 |
|
ARTICLES To receive example SQL
Scripts used in webcasts and articles:
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.
About the articles:
I explain Performance Monitoring and Tuning concepts in the
workshop with examples - as you can see it in the below
feedbacks. I try to do the best while writing articles, but it
is not the same!!
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
If you find this article helpful, give me feedback, this
motivates me to write some more articles. I also encourage you to attend the
3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshop.
Register for the upcoming 3 Day Level 400 Microsoft SQL Server 2012 and 2008 Performance Monitoring & Tuning Hands-on Workshop, for workshop
schedule click
here.
These are hands-on workshops with a maximum of 12 participants
and not lectures. For consulting
engagements 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).
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.
In Austria,
R Meyyappan delivered workshops during
June 16-18, 2010,
October 12-14, 2009,
May 11-13, 2009,
April 14-16, 2009,
March 23-25, 2009,
March 16-18, 2009,
January 12-14, 2009,
May 06-08, 2008,
March10-12, 2008,
December 04-06, 2007,
September 05-07, 2007,
February 21-23, 2007,
October 03,05, 2006,
September 27-28, 2006 and
December 12-13, 2005,
participants were from various companies including
ace Neue Informationstechnologien GmbH, addIT
Dienstleistungs GesmbH, AgrarData,
Allgemeines
Rechenzentrum GmbH, bwin,
C07 IT Operations, FTC Capital GmbH, GrECo
International, Hyperwave,
Kapsch AG,
Kapsch Business Com,
Kapsch Traffic Com,
Konnex Dienstleistungen / BBRZ Gruppe,
Land OÖ Abteilung Informationstechnologie,
Logis IT Service, MAYER & CO Beschläge GmBH,
mii,
Österreichische Nationalbank, Österreichisches Verkehrsbüro, Racon West
Software, Rudolf Leiner Ges.m.b.H.,
S&T Austria GmbH,
Salzburg AG, SAP Österreich GmbH,
sDIES Dezentrale
IT-Infrastruktur Services GmbH, Siemens AG,
Softwaresysteme GTLDATA GmbH,
Solutions Factory,
SWS EDV-Software Service,
TIWAG-Tiroler Wasserkraft AG and TUPACK
Verpackungen.
In United
Kingdom,
R Meyyappan delivered workshops during
June 22-24, 2010,
November 14-16, 2007,
November 29 - December 01, 2006 and
June 19-21, 2006,
participants were from various companies including
Atos Origins, Attenda,
Capgemini, Centiq,
Centrica, Ciber UK, Digica,
EMC, Hewlett-Packard,
Holman Fenwick & Willan, Lloyds TSB,
Logica CMG, Serco,
Smiths News, Trafford Council,
Transco, T-Systems,
Unilever and Yorkshire Water.
In
Switzerland,
R Meyyappan delivered workshops during
April 21-23, 2010,
October 19-21, 2009,
February 25-27, 2009,
January 30 - February 01, 2008,
April 11-13, 2007 and
September 11-15, 2006, participants were from various companies including
Altria ITSC Europe, Bedag
Informatik AG, Bühler AG,
Credit Suisse, Die Schweizerische Post,
ETA SA Manufacture Horlogère Suisse,
JTI, Lombard Odier Darier Hentsch,
Manor AG, National Suisse,
Novartis AG, Schindler Management
Ltd, Swiss Life, Swisscom,
Trivadis AG and T-Systems Schweiz
AG.
In Finland,
R Meyyappan delivered workshops during
February 16-18, 2010,
November 02, 2009,
August 24-25, 2006,
September 18-20, 2006 and
November 28-30, 2005 participants were from various companies including
FD Finanssidata Oy / OP Bank Group,
Fujitsu Services Oy, Kemira Oyj,
Siemens, Solteq Oyj,
TietoEnator Oyj and WM-data.
In Denmark,
R Meyyappan delivered workshops during
November 11-13, 2009,
October 08-10, 2007,
April 23-25, 2007,
March 26-28, 2007,
February 12-14, 2007 and
February 07-09, 2007, participants were from various companies including
bankdata, Danske Bank,
Egmont, LEGO, NNIT
A/S, Siemens, Solar
Danmark A/S, Sydbank, TDC
Services and Vestas Wind Systems A/S.
In Norway,
R Meyyappan delivered workshops during
January 27, 2009,
November 19-21, 2007
and
November 08-10, 2006, participants
were from various companies including Borregaard,
Bouvet, CIBER Norge AS,
Det Norske Veritas, EDB Business Partner ASA,
ErgoGroup AS, Hafslund ASA,
Helse Vest IKT AS, Microsoft, Nord-Odal
Veggmontasje A/S, Odfjell Seachem AS,
Pearl Consulting AS, Spring
Consulting AS, Ullevål Universitetssykehus
and VetcoGray Scandinavia AS.
In
Netherlands, R Meyyappan delivered workshops
during
April 07-09, 2008,
October 01-03, 2007,
September 26-28, 2007,
July 23-25,
2007, February 26-28, 2007,
January 16-18, 2007 and
October
18-20, 2006,
participants were from various companies including
Achmea, Agis, Atos Origin,
BKR, Bloemenveiling Aalsmeer,
Capgemini, DAF Trucks,
De Brauw Blackstone & Westbroek,
De Nederlandsche Bank, Deloitte,
Equens, Getronics,
IND, ING, ING Bank, ING
Real Estate, Interpolis,
KLM – Royal Dutch Airlines, KPMG,
KPN, Leids Universitair Medisch
Centrum, Logica CMG,
Loyens & Loeff N.V, Menzis,
Ministerie van justitie, Ministerie
van Sociale Zaken en Werkgelegenheid,
Ministerie Verkeer en Waterstaat, NV
Nederlandse Spoorwegen,
PGGM,
Philips, PricewaterhouseCoopers,
Simac ICT,
Sylis, TNO Delft,
UMC St Radboud, UMC Utrecht,
Unisys, Universiteit van Amsterdam,
University Medical Center Groningen and
VGZ-IZA-TRIAS.
In Sweden,
R Meyyappan delivered workshops during
June 11-13, 2007,
May 21-23, 2007 and
March 05-07, 2007,
participants were from various companies including
Banverket, Cambio Healthcare Systems,
Försäkringskassan, Hogia Public
Transport Systems AB, ICA AB,
Östergötland County Council,
Sandvik IT, SCB, Sirius
International, Skandia IT,
Sony Ericsson Mobile Communications AB,
Svenska Cellulosa Aktiebolaget, Sveriges
riksbank, Swedbank AB,
TietoEnator AB and WM-data.
In Belgium,
R Meyyappan delivered workshops during
May 07-09, 2007 and
March 12-14, 2007,
participants were from various companies including
Avanade, Capgemini, Cegeka,
Dolmen, EZOS, Ferranti Computer Systems,
Getronics, Hewlett-Packard Belgium
bvba/sprl, Icorda, IKAN
Consulting n.v., ORBID,
Ordina Belgium, PeopleWare,
Progu BVBA, Securewave,
Systemat, Wavenet and
Xylos.
In Germany,
R Meyyappan delivered workshops during
May 29-31, 2006,
Microsoft Employees (including senior members of Microsoft
Consulting Services and Microsoft Support)
have participated in the workshop. For
SAP AG
he delivered workshops
during May 15-19, 2006,
entire SAP IT department DBAs participated
in the workshop in 2 batches, in addition members of
SAP Hosting team also participated in the workshop.
Let's get to the bottom line:
This level 400 workshop requires participants to have basic prior SQL
Server experience, specifically knowledge about Cluster,
Heap and Non-cluster index, Index Tuning Wizard and SQL
Profiler - to avoid repetition. The workshop covers specifically SQL Server Performance Monitoring & Tuning
topic - doesn't cover Disaster Recovery topics on the side –
to keep the focus.
Have you been to a workshop where the instructor explains the
difference between Clustered and Heap index (or demonstrates SQL
Profiler) and you felt like having an another coffee break ?
Have you been to a workshop where the instructor explains deeply
about DBCC CHECKDB and then 2 years later you felt like you
could have spent that time on a vacation because it didn’t make
a dent in your career ?
Well this workshop will be an entirely different
experience. This truly level 400 workshop gives you an opportunity to really
gain practical knowledge and use that knowledge to advance
your career.
Microsoft Employees (including senior members of Microsoft
Consulting Services, Support and SQL Server Product
Development team) have participated in the 3 Day Level 400 Microsoft SQL Server Performance Monitoring & Tuning Hands-on Workshops delivered by R Meyyappan. SAP AG’s IT Department
DBAs in Walldorf, Germany have participated in Level 300 Microsoft SQL Server Administration & Tuning for SAP
Customers Hands-on Workshops delivered by R Meyyappan.
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.
COPYRIGHT AND TRADEMARK NOTICES:
The copyright in all material provided on this site is held
by SQLWorkshops.com or by the original creator of the material.
Except as stated herein, none of the material may be copied,
reproduced, distributed, republished, downloaded, displayed,
posted or transmitted in any form or by any means, including,
but not limited to, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of
SQLWorkshops.com or the copyright owner. Permission is granted
to download the webcast on this Site for personal,
non-commercial use only, provided you do not modify the webcast
and that you retain all copyright and other proprietary notices
contained in the webcast. You also may not, without
SQLWorkshops.com permission, "mirror" any webcast contained on
this Site on any other server. This permission terminates
automatically if you breach any of these terms or conditions.
Upon termination, you must immediately destroy any downloaded
webcast. Any unauthorized use of any webcast contained on this
Site may violate copyright laws, trademark laws, the laws of
privacy and publicity, and communications regulations and
statutes.
Microsoft is a registered trademark of Microsoft Corporation in
the United States and/or other countries. SQL Server is a
trademark of Microsoft Corporation in the United States and/or
other countries. This webcast may refer to organizations and
products that may be the trademarks or registered trademarks of
their various owners.
You may freely use the ideas and concepts discussed in this
webcast, but you may not claim any of it as your own work. You
may quote freely, with acknowledgements to SQLWorkshops.com.
This article is for informational purposes only; you use any of
the suggestions given here entirely at your own risk.
|
