SQL Workshops - Index - Truly Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshops & Webcasts

Workshops Instructor Schedule Feedbacks Webcasts Contacts


Workshops

Instructor

Schedule

Feedbacks

Webcasts

Contacts
Sign up for Email Updates

Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop in Vienna, Austria during November 26-28, 2013, click here to register. These are hands-on workshops with a maximum of 12 participants and not lectures. For consulting engagements click here.

To read some of our articles I wrote click here.

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 1 Part 1: SQLWebcast1Part1of3.wmv(29.1MB), Part 2: SQLWebcast1Part2of3.wmv(47.5MB) & Part 3: SQLWebcast1Part3of3.wmv(37.4MB) - FREE Download. Webcast 1 released on November 21, 2009 (Box.net download counters as of February 14, 2012: 36,079 downloads, sum of all 3 Parts).

To receive example SQL Scripts used in webcasts: Sign up for Email Updates. Please take a few minutes and pass on the webcast link to your friends and local user community. Feedback from a Microsoft Employee: Your webcast rocked. Your free webcast was very good and informative.

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 2 Part 1: SQLWebcast2Part1of3.wmv(35.1MB), Part 2: SQLWebcast2Part2of3.wmv(34.9MB) & Part 3: SQLWebcast2Part3of3.wmv(30.6MB) - FREE Download. Webcast 2 released on January 20, 2010 (Box.net download counters as of February 14, 2012: 23,953 downloads, sum of all 3 Parts).

Download Truly Level 400 SQL Server Performance Monitoring and Tuning Webcast 3 Part 1: SQLWebcast3Part1of3.wmv(103MB), Part 2: SQLWebcast3Part2of3.wmv(116MB) & Part 3: SQLWebcast3Part3of3.wmv(69.1MB) - FREE Download. Webcast 3 released on February 10, 2010 (Box.net download counters as of February 14, 2012: 39,568 downloads, sum of all 3 Parts). Firewall might block downloads, if you have issues downloading let us know, we will provide you with alternate download links.

Below is a short intro webcast, different webcast but same topic, if you are not still convinced to download the webcast, you can view the introduction to decide for yourself if it is useful for you / worth downloading. More info on Webcasts agenda available here.

Christian from Microsoft Switzerland received 9 evaluation forms from the attendees on June 24, 2011 and the feedback summary is below:
Score on Instructor Quality: R Meyyappan - Scale of 1 (Poor) to 9 (Excellent):
The instructor was prepared for the class: 9 out of 9
The The instructor was knowledgeable in the subject matter: 9 out of 9
The instructor provided a valuable learning experience: 8.8 out of 9
The instructor was able to answer questions effectively: 8.9 out of 9
The instructor checked for understanding of concepts: 8.8 out of 9
The instructor was available for assistance for hands-on exercises: 9 out of 9

Lubor Kollar, Customer Advisory Team, SQL Server Development, Microsoft Corporation:
I have attended the first two days of Ramesh’s Performance Monitoring and Tuning Hands-on Workshop in Prague on Oct. 15 and 16.
My main goal was to learn in detail the contents of Ramesh’s class so that I can suggest his class to other Microsoft customers I’m working with in the role of SQL Server Customer Advisory Team manager. I was very pleased both with the content and delivery of Ramesh’s class. He is providing useful, deep and very accurate information that will help our customers when developing, testing, tuning and deploying their applications using SQL Server or upgrading existing applications from previous SQL Server releases. Ramesh showed extraordinary attention to detail and he explained in depth the internals of the engine.

For R Meyyappan's SQLBits.com session recording click here. To download a copy of this video, right click and choose "Save As" with this link: http://go.microsoft.com/?linkid=9712350. For additional recorded sessions use this link: http://www.sqlbits.com/information/event6/PublicSessions.aspx.

R Meyyappan primarily offers 2 types of workshops, 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop and 3 day Level 300 SQL Server Administration & Tuning for SAP Customers Hands-on Workshop.

R Meyyappan also offers onsite and offsite consulting services on the following areas:
Performance Monitoring, Tuning & Troubleshooting for SQL Server Customers
Optimizing SQL Server Implementation for SAP Customers

3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop.

Target Group: Database Administrators, Developers and Database Architects. This workshop is not for SQL Server Trainers or Instructors.

Goal of the Workshop: To provide participants with knowledge on how to monitor and tune performance of Microsoft SQL Server 2012 and 2008. The course addresses CPU, Memory, Disk, Query and Lock monitoring and tuning techniques for optimal performance and to maximize the utilization of existing hardware. Best practice guidelines for configuration and maintenance are covered. After the workshop, the attendees should be able to identify and tune performance issues. Past workshop feedbacks are available here.

Prerequisite: This is an advanced workshop; basic SQL Server concepts will not be covered. Prior basic SQL Server experience (knowledge about Cluster, Heap and Non-cluster indexes, Database Engine Tuning Advisor, SQL Profiler) is necessary.

Instructor: R Meyyappan with more than 15 years of SQL Server expertise including working in the past as a Program Manager in the SQL Server Development Team at Microsoft Corporation. He is specialized in SQL Server Performance Monitoring, Tuning & Troubleshooting.

Charges: € 2100 (VAT excl.). For Workshop schedule and registration information click here.

Maximum Attendees per workshop: 12.

AGENDA:

Analyzing current activity and performance bottlenecks of SQL Server in a Production environment:
This module introduces a comprehensive approach for analyzing current activity and identifying performance bottlenecks of SQL Server in a Production environment. Implementing alert mechanism for proactive monitoring is explained.

Monitoring and Tuning CPU Utilization:
SQL Server architecture relating to CPU usage is explained. Efficient methods for monitoring and tuning CPU bottlenecks are described. Identifying optimal number of CPUs and configuration parameters for maximizing CPU utilization is explained. Best practice guidelines on using Resource Governor for allocating CPU resource are covered.

Monitoring and Tuning Disk I/O and Database Files:
SQL Server architecture relating to Disk I/O and Database File usage are explained. Various methods including Wait Stats and Extended Events for monitoring Disk I/O and Database File usage are described out of which few important methods with reference data are provided for optimal monitoring. Best practice guidelines on implementing Storage subsystems with DAS, SAN and SSDs are explained. Configuring and maintaining Database Files for user database and tempdb with Index Rebuild and Reorganization, Data Compression, Database Snapshots, and Bulk Inserts for optimal performance are covered.

Monitoring and Tuning Memory Utilization:
SQL Server architecture relating to Memory allocation and usage is explained. Efficient methods for monitoring and tuning memory usage are described. Tempdb usage due to Memory pressure and suboptimal execution plans are explained along with effective workarounds to address these performance issues. Best practice guidelines on using Resource Governor for optimizing Memory usage are covered.

Monitoring and Tuning Query Optimization and Query Execution:
SQL Server architecture relating to Query Optimization and Query Execution is explained. Various methods for monitoring and tuning Plan Caching and Recompilation using Parameterization and Plan Guides are described. Analyzing Execution Plans including joins and parallel plans and isolating performance issues are described. Monitoring and tuning Query Execution with optimal Indexes, Statistics, Query Hints, Computed Columns, Indexed Views, Filtered Indexes, Filtered Statistics, Indexed Views, Partitioned Tables and Partitioned Views are covered. Best practice guidelines on using Temporary Tables, Table Variables, Table Value Parameters, Stored Procedures, User Defined Functions and in general writing SQL Server Query Optimizer friendly queries are covered.

Monitoring and Tuning Locks, Deadlocks and other Concurrency issues:
SQL Server architecture relating to resource Locking is explained. Various methods including Extended Events for monitoring and tuning Locking, Blocking and Deadlocks are described. Best practice guidelines on using Snapshot Isolation level, disabling Table Lock Escalation, and Locking Hints and query rewrite procedures to reduce locking and deadlocks issues are covered.

Monitoring and Tuning AlwaysOn Availability Groups, Database Mirroring, Database Replication, Columnstore Indexes, Backup Compression and Database Encryption:
Monitoring and tuning the performance impact of Availability Replicas, Database Mirroring is explained. Best practice guidelines on using Database Replication, Columnstore Indexes, Backup Compression, and Database Encryption from performance perspective are covered.

Useful Performance data to collect on a regular interval and on ad-hoc basis are discussed. Using Extended Events and Management Data Warehouse for performance data collection is explained. Ways to further deepen the SQL Server performance monitoring and tuning knowledge is discussed.

Articles related to the Workshops.

Workspace Memory / Query Memory Tuning – RESOURCE_SEMAPHORE / IO_COMPLETION / SLEEP_TASK Waits

Prefetch – Querying at the speed of SAN

Plan and Tuning AlwaysOn Availability Groups, Database Mirroring, Database Replication, Columnstore Indexes, Backup Compression and Database EncryptionCaching and Query Memory - When not to use stored procedure or other plan caching mechanism like sp_executesql or prepared statement

Parallel Sort and Merge Join – Watch out for unpredictability in performance

Parallel Query Memory - MAXDOP and query memory distribution in spotlight

Flying with Plan Freezing – Mostly you experience thrust or stay afloat, but rarely this add-on can be a drag

Finding optimal number of CPUs for a given CPU intensive workload

When to place tempdb in RAM to improve ORDER BY performance

3 Day Level 300 SQL Server Administration & Tuning for SAP Customers Hands-on Workshop.

AGENDA:

SAP / SQL Server Architecture:
SID & Tempdb Database, Data & Log Files, Auto Growth, Proportional Fill, Multiple Instances, Tables & Indexes, Statistics, Fragmentation. SAP Sessions & Connections, Schema, Statement Execution in SQL Server. SQL Server Configuration Parameters, Auto Create, Update, Asynchronous Statistics.

Database Maintenance:
File Reorganization, Index Reorganization, Statistics Update, Database Consistency Check, ErrorLog, Service Packs, Cumulative Updates, SQL Server Versions, Moving Data/Log File, Expanding Data/Log Files, System Copy, Shrinking Data/Log Files.

Disaster Recovery & High Availability Planning:
Master, Msdb, SID , Backup & Restore, Point in Time, Log Mark, Backup Compression, DBA Planning Calendar, Log Shipping, Database Mirroring, Database Snapshots.

Performance Monitoring and Tuning:
Requests & DBA Cockpit Database Processes, Wait Stats. CPU, Disk & Memory. Query Tuning and Locking. Bitmap filtering, Latency, Throughput, Read Ahead, Partition Alignment, Instant File Initialization, SQLIO, Maximum & Minimum Memory, Data Cache Hit Ratio, Hash & Sort Warning, Lock Pages in Memory.
DBA Cockpit, SQL Statements, ST05, SQL Profiler, Analyzing Execution Plan,
Plan Caching, Identifying Non Optimal Plans, Query Tuning, Missing Indexes, Index & Column Statistics, Hints, Plan Guides, Extended Events.
Locking, Blocking, Escalation, Deadlock, Statistics.

Database Compression:
Concepts, Tools, Compression scenarios for SAP Customers upgrading from SQL Server 2005 and 2008 to SQL Server 2012.

New features for SAP Customers in SQL Server 2012 & SQL Server 2008:
Transparent Data Encryption, SQL Server Audit, Change Data Capture, Change Tracking, Resource Governor, Policy-Based Management, Data Collection, SQL Server Utility.

For additional details refer to: 3 day Level 300 SQL Server Administration & Tuning for SAP Customers Hands-on Workshop.

Webcast Agenda:

Webcast 1: Explains Memory allocation issues with sort. Demonstrates ways to identify sort spills to tempdb. Provides query rewrite procedure to avoid sort spills to tempdb. Demonstrates cases where 1 tempdb date file per core might not be ideal for all implementations.

Webcast 2: Explains high CXPACKET waits are NOT a direct result of delays associated with inefficiencies of parallel processing. Provides example to scale queries over many CPU cores without reducing MAXDOP settings.

Webcast 3: Recommends not using stored procedure or other plan caching mechanism like using sp_executesql and Prepared Statement using ADO.NET or OLEDB based executions for memory allocating queries. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.

With examples provides ways to identify queries performing Hash match operations that spill to tempdb. Using SQL Profiler: Hash Warnings (Hash Recursion and Hash Bailout). Using sys.dm_exec_query_memory_grants: Granted Memory, Used Memory and Maximum Used memory. Explains how SLEEP_TASK wait type is associated with Hash Warnings.

Explains with examples how sp_recompile can block and bring an application to a standstill and recommends using DBCC FREEPROCCACHE (plan_handle) instead.

Explains with examples how stored procedure or other plan caching mechanism affects queries that sort (perform order by). Explains how IO_COMPLETION wait type is associated with Sort Warnings.

Demonstrates why rollbacks and database restore could wait on IO_COMPLETION wait type and if needed how this wait can be reduced.

Explains how parallel query performance is significantly affected by a CPU intensive query executing on one of the CPU cores. Explains the reason for the performance issue and how to identify the issue with SLEEP_TASK wait type. With example explains the reason for the observed query execution time when the child thread executes on the CPU core where the CPU intensive query executes and also when the coordinator executes on the CPU core where the CPU intensive query executes. Recommends ways to avoid this parallel sort query performance issue and also demonstrates that only certain queries will be affected by this and not all parallel queries.

Explains sys.dm_os_waits_stats.signal_wait_time_ms does not indicate system wide CPU pressure, just at a CPU core level.

Explains the prefetch mechanism and how it can affect query performance. Provides ways to force prefecth. Demonstrates cases where Avg. Disk Sec / read, the disk latency, PAGEIOLATCH_SH, Avg. disk queue length is very high and the query executes fast.

Explains why Plan Guides (Plan Freezing!) cannot help with prefetch or in few other cases.

Ramesh's consulting customers include Austria: Allgemeines Krankenhaus Linz, BMD Systemhaus, Bundesrechenzentrum, bwin, C07 IT Operations, CMC Markets, derStandard, ERGO Insurance Service, Fabasoft Austria, Greentube, IAEA (UN), Interwetten, INTERSPORT, Kapsch, Linz AG, Mayr-Melnhof Karton, NÖM, ÖBB, OMV, Österreichische Post, RACON West Software, Raiffeisen Bankengruppe, Raiffeisen Informatik, S.Spitz, Salzburg AG, Siemens, TIWAG, UC4, Vinzenz Gruppe, Wiener Zeitung, Wirtschaftskammer Österreich; Bulgaria: MobilTel; Czech Republic: Česká pojišťovna, ČSOB Bank, GTS Novera, Telefónica O2, Vodafone; Denmark: Danfoss, Egmont, NNIT; Finland: Cap Gemini, Kemira, Pohjola; Germany: JAM Software, SAP AG; Netherlands: De Nederlandsche Bank; Norway: Acta, Aker Solutions, Det Norske Veritas(DNV), Komplett; Russia: Mediatel; Serbia: Tarkett; Slovakia: Adastra Corporation, VÚB Banka; Slovenia: Bankart, Iskratel, KDD, Krka, Mobitel, Pošta Slovenije, Sava, SRC.SI; South Africa: De Beers, Kumba, Sasol; Spain: CIE Automotive, Gas Natural, ITP; Sweden: Sony Ericsson; Switzerland: F. Hoffmann-La Roche, Philip Morris International; UAE: Dubai Customs. For past consulting engagement feedbacks click here.