 |
|
Truly Level 400 SQL Server Performance Monitoring and Tuning Hands-on Workshops and Webcasts
Register for the upcoming 3
Day Level 400 Microsoft SQL Server 2008
Performance Monitoring & Tuning Hands-on Workshop
in London, United Kingdom
during
June 12-14, 2012, 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 December 02, 2011: 31,678
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 December 02, 2011: 22,910
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 December 02, 2011: 37,313 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 2005 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 2008 Performance Monitoring &
Tuning Hands-on Workshop and
3 day Level 300 SQL Server 2008
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 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 the participants with knowledge on how to
monitor and tune performance of Microsoft SQL Server 2008. The
course addresses CPU, Memory, Disk, Query and Lock monitoring
and tuning techniques. 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 performance bottlenecks of a SQL Server in a
Production environment:
This module introduces a comprehensive approach for
analyzing current performance bottlenecks of SQL Server in a
Production environment.
Monitoring and Tuning CPU Utilization:
SQL Server architecture relating to CPU usage is
explained. Optimal methods to monitor and tune CPU bottlenecks
are described. Best practice guidelines on using Resource
Governor are covered.
Monitoring and Tuning Disk I/O and Database Files:
SQL Server architecture relating to Disk I/O and
Database File usage is explained. Various methods including
Extended Events to monitor Disk I/O and Database File usage is
described. Best practice guidelines on implementation and
maintenance of Disk subsystem and Database Files, 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 to monitor and tune
Memory usage are described. Best practice guidelines on using
Resource Governor are covered.
Monitoring and Tuning Query Optimization and Query Execution:
SQL Server architecture relating to Query Optimization
is explained. Various methods to monitor and tune Plan Caching
and Recompilation are described. Analyzing Execution Plans and
isolating performance issues are described. Monitoring and
tuning Query Execution with optimal Indexes, Filtered Indexes,
Indexed Views and Partitioned Tables 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 to monitor
and tune Locking, Blocking and Deadlocks are described. Best
practice guidelines on using Snapshot Isolation level, disabling
Table Lock Escalation and Locking Hints are covered.
Monitoring and Tuning Database Mirroring, Backup Compression and
Database Encryption:
Monitoring and tuning the performance impact of
Database Mirroring is explained. Best practice guidelines on
using Backup Compression and Database Encryption are covered.
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 Caching 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 2008 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 to SQL Server 2008 to
SQL Server 2008 R2.
New features for SAP Customers in SQL Server 2008 & SQL Server
2008 R2:
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 2008
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.
|
