SQL SERVER – Tomorrow 2 Sessions on Performance Tuning at TechEd India 2011 – March 25, 2011

Tomorrow is the third day of the TechED India 2011 at Bangalore. I will be speaking on two very interesting sessions. If you are developer, database administrator or just want to learn something new and interesting, I suggest you attend my two sessions tomorrow. Here is the details of the session.

Sessions Date: March 25, 2011

Here is the abstract of the session:

Understanding SQL Server Behavioral Pattern – SQL Server Extended Events
Date and Time: March 25, 2011 12:00 PM to 01:00 PM

History repeats itself! SQL Server 2008 has introduced a very powerful, yet very minimal reoccurring feature called Extended Events. This advanced session will teach experienced administrators’ capabilities that were not possible before. From T-SQL error to CPU bottleneck, error login to deadlocks –Extended Event can detect it for you. Understanding the pattern of events can prevent future mistakes.

SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting
Date and Time: March 25, 2011 04:15 PM to 05:15 PM

Just like a horoscope, SQL Server Waits and Queues can reveal your past, explain your present and predict your future. SQL Server Performance Tuning uses the Waits and Queues as a proven method to identify the best opportunities to improve performance. A glance at Wait Types can tell where there is a bottleneck. Learn how to identify bottlenecks and potential resolutions in this fast paced, advanced performance tuning session.

Here is the video which my wife shot while I was preparing for the sessions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQLAuthority News – I am Presenting 2 Sessions at TechEd India

TechED is the event which I am always excited about. It is one of the largest technology in India. Microsoft Tech Ed India 2011 is the premier technical education and networking event for tech professionals interested in learning, connecting and exploring a broad set of current and soon-to-be released Microsoft technologies, tools, platforms and services.

I am going to speak at the TechED on two very interesting and advanced subjects.

Venue:
The LaLiT Ashok
Kumara Krupa High Grounds
Bangalore – 560001, Karnataka, India

Sessions Date: March 25, 2011

Understanding SQL Server Behavioral Pattern – SQL Server Extended Events

Date and Time: March 25, 2011 12:00 PM to 01:00 PM
History repeats itself! SQL Server 2008 has introduced a very powerful, yet very minimal reoccurring feature called Extended Events. This advanced session will teach experienced administrators’ capabilities that were not possible before. From T-SQL error to CPU bottleneck, error login to deadlocks –Extended Event can detect it for you. Understanding the pattern of events can prevent future mistakes.

SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting

Date and Time: March 25, 2011 04:15 PM to 05:15 PM
Just like a horoscope, SQL Server Waits and Queues can reveal your past, explain your present and predict your future. SQL Server Performance Tuning uses the Waits and Queues as a proven method to identify the best opportunities to improve performance. A glance at Wait Types can tell where there is a bottleneck. Learn how to identify bottlenecks and potential resolutions in this fast paced, advanced performance tuning session.

My session will be on the third day of the event and I am very sure that everybody will be in groove to learn new interesting subjects.

I will have few give-away during and at the end of the session. I will not tell you what I will have but it will be for sure something you will love to have. Please make a point and reserve above time slots to attend my session.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Summary of Month – Wait Type – Day 28 of 28

I am glad to announce that the month of Wait Types and Queues is very successful. I am glad that it was very well received and there was great amount of participation from the community.

I am fortunate to have some of the excellent comments throughout the series. I want to dedicate this series to all the guest bloggers – Jonathan, Jacob, Glenn, and Feodor for their kindness to take participation in this series.

Here is the complete list of the blog posts in this series.

I enjoyed writing the series and I plan to continue writing similar series. Please offer your opinion.

SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28

SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28

SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28

SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28

SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28

SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28

SQL SERVER – SOS_SCHEDULER_YIELD – Wait Type – Day 8 of 28

SQL SERVER – PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_SH, PAGEIOLATCH_UP – Wait Type – Day 9 of 28

SQL SERVER – IO_COMPLETION – Wait Type – Day 10 of 28

SQL SERVER – ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28

SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28

SQL SERVER – FT_IFTS_SCHEDULER_IDLE_WAIT – Full Text – Wait Type – Day 13 of 28

SQL SERVER – BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28

SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28

SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28

SQL SERVER – WRITELOG – Wait Type – Day 17 of 28

SQL SERVER – LOGBUFFER – Wait Type – Day 18 of 28

SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28

SQL SERVER – MSQL_XP – Wait Type – Day 20 of 28

SQL SERVER – Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28

SQL SERVER – Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28

SQL SERVER – OLEDB – Link Server – Wait Type – Day 23 of 28

SQL SERVER – 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28

SQL SERVER – 2011 – Wait Type – Day 25 of 28

SQL SERVER – Guest Post – Glenn Berry – Wait Type – Day 26 of 28

SQL SERVER – Best Reference – Wait Type – Day 27 of 28

SQL SERVER – Summary of Month – Wait Type – Day 28 of 28

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Best Reference – Wait Type – Day 27 of 28

I have great learning experience to write my article series on Extended Event. This was truly learning experience where I have learned way more than I would have learned otherwise. Besides my blog series there was excellent quality reference available on internet which one can use to learn this subject further.

Here is the list of resources (in no particular order):

I have listed all the reference I have found in no particular order. If I have missed any good reference, please leave a comment and I will add the reference in the list.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Tracking Session and Statement Level Waits

SQL SERVER – Guest Post – Glenn Berry – Wait Type – Day 26 of 28

Glenn Berry works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS. He is also an Adjunct Faculty member at University College – University of Denver, where he has been teaching since 2000. He is one wonderful blogger and often blogs at here.

I am big fan of the Dynamic Management Views (DMV) scripts of Glenn. His script are extremely popular and the reality is that he has inspired me to start this series with his famous DMV which I have mentioned in very first  wait stats blog post (I had forgot to request his permission to re-use the script but when asked later on his whole hearty approved it).

Here is is his excellent blog post on this subject of wait stats:

Analyzing cumulative wait stats in SQL Server 2005 and above has become a popular and effective technique for diagnosing performance issues and further focusing your troubleshooting and diagnostic  efforts.  Rather than just guessing about what resource(s) that SQL Server is waiting on, you can actually find out by running a relatively simple DMV query. Once you know what resources that SQL Server is spending the most time waiting on, you can run more specific queries that focus on that resource to get a better idea what is causing the problem.

I do want to throw out a few caveats about using wait stats as a diagnostic tool. First, they are most useful when your SQL Server instance is experiencing performance problems. If your instance is running well, with no indication of any resource pressure from other sources, then you should not worry that much about what the top wait types are. SQL Server will always be waiting on some resource, but many wait types are quite benign, and can be safely ignored. In spite of this, I quite often see experienced DBAs obsessing over the top wait type, even when their SQL Server instance is running extremely well.

Second, I often see DBAs jump to the wrong conclusion based on seeing a particular well-known wait type. A good example is CXPACKET waits. People typically jump to the conclusion that high CXPACKET waits means that they should immediately change their instance-level MADOP setting to 1. This is not always the best solution. You need to consider your workload type, and look carefully for any important “missing” indexes that might be causing the query optimizer to use a parallel plan to compensate for the missing index. In this case, correcting the index problem is usually a better solution than changing MAXDOP, since you are curing the disease rather than just treating the symptom.

Finally, you should get in the habit of clearing out your cumulative wait stats with the  DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR); command. This is especially important if you have made an configuration or index changes, or if your workload has changed recently. Otherwise, your cumulative wait stats will be polluted with the old stats from weeks or months ago (since the last time SQL Server was started or the stats were cleared).  If you make a change to your SQL Server instance, or add an index, you should clear out your wait stats, and then wait a while to see what your new top wait stats are.

At any rate, enjoy Pinal Dave’s series on Wait Stats.

This blog post has been written by Glenn Berry (Twitter | Blog)

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2011 – Wait Type – Day 25 of 28

Since the beginning of the series, I have been getting the following question again and again:

“What are the changes in SQL Server 2011 – Denali with respect to Wait Types?”

SQL Server 2011 – Denali is yet to be released, and making statements on the subject will be inappropriate. Denali CTP1 has been released so I suggest that all of you download the same and experiment on it. I quickly compared the wait stats of SQL Server 2008 R2 and Denali (CTP1) and found the following changes:

Wait Types Exists in SQL Server 2008 R2 and Not Exists in SQL Server 2011 “Denali”

SOS_RESERVEDMEMBLOCKLIST
SOS_LOCALALLOCATORLIST
QUERY_WAIT_ERRHDL_SERVICE
QUERY_ERRHDL_SERVICE_DONE
XE_PACKAGE_LOCK_BACKOFF

Wait Types Exists in SQL Server 2011 and Not Exists in SQL Server 2008

SLEEP_MASTERMDREADY
SOS_MEMORY_TOPLEVELBLOCKALLOCATOR
SOS_PHYS_PAGE_CACHE
FILESTREAM_WORKITEM_QUEUE
FILESTREAM_FILE_OBJECT
FILESTREAM_FCB
FILESTREAM_CACHE
XE_CALLBACK_LIST
PWAIT_MD_RELATION_CACHE
PWAIT_MD_SERVER_CACHE
PWAIT_MD_LOGIN_STATS
DISPATCHER_PRIORITY_QUEUE_SEMAPHORE
FT_PROPERTYLIST_CACHE
SECURITY_KEYRING_RWLOCK
BROKER_TRANSMISSION_WORK
BROKER_TRANSMISSION_OBJECT
BROKER_TRANSMISSION_TABLE
BROKER_DISPATCHER
BROKER_FORWARDER
UCS_MANAGER
UCS_TRANSPORT
UCS_MEMORY_NOTIFICATION
UCS_ENDPOINT_CHANGE
UCS_TRANSPORT_STREAM_CHANGE
QUERY_TASK_ENQUEUE_MUTEX
DBCC_SCALE_OUT_EXPR_CACHE
PWAIT_ALL_COMPONENTS_INITIALIZED
PREEMPTIVE_SP_SERVER_DIAGNOSTICS
SP_SERVER_DIAGNOSTICS_SLEEP
SP_SERVER_DIAGNOSTICS_INIT_MUTEX
AM_INDBUILD_ALLOCATION
QRY_PARALLEL_THREAD_MUTEX
FT_MASTER_MERGE_COORDINATOR
PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
REDO_THREAD_PENDING_WORK
REDO_THREAD_SYNC
COUNTRECOVERYMGR
HADR_DB_COMMAND
HADR_TRANSPORT_SESSION
HADR_CLUSAPI_CALL
PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC
PWAIT_HADR_ACTION_COMPLETED
PWAIT_HADR_OFFLINE_COMPLETED
PWAIT_HADR_ONLINE_COMPLETED
PWAIT_HADR_FORCEFAILOVER_COMPLETED
PWAIT_HADR_WORKITEM_COMPLETED
HADR_WORK_POOL
HADR_WORK_QUEUE
HADR_LOGCAPTURE_SYNC
LOGPOOL_CACHESIZE
LOGPOOL_FREEPOOLS
LOGPOOL_REPLACEMENTSET
LOGPOOL_CONSUMERSET
LOGPOOL_MGRSET
LOGPOOL_CONSUMER
LOGPOOLREFCOUNTEDOBJECT_REFDONE
HADR_SYNC_COMMIT
HADR_AG_MUTEX
PWAIT_SECURITY_CACHE_INVALIDATION
PWAIT_HADR_SERVER_READY_CONNECTIONS
HADR_FILESTREAM_MANAGER
HADR_FILESTREAM_BLOCK_FLUSH
HADR_FILESTREAM_IOMGR
XDES_HISTORY
XDES_SNAPSHOT
HADR_FILESTREAM_IOMGR_IOCOMPLETION
UCS_SESSION_REGISTRATION
ENABLE_EMPTY_VERSIONING
HADR_DB_OP_START_SYNC
HADR_DB_OP_COMPLETION_SYNC
HADR_LOGPROGRESS_SYNC
HADR_TRANSPORT_DBRLIST
HADR_FAILOVER_PARTNER
XDESTSVERMGR
GHOSTCLEANUPSYNCMGR
HADR_AR_UNLOAD_COMPLETED
HADR_PARTNER_SYNC
HADR_DBSTATECHANGE_SYNC

We already know that Wait Types and Wait Stats are going to be the next big thing in the next version of SQL Server. So now I am eagerly waiting to dig deeper in the wait stats.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28

I have received many comments, email, suggestions and motivations for my current series of wait types and wait statistics. One of the questions which I keep on receiving almost every other day is whether all of the discussions I have presented so far are also applicable to SQL Server 2000. Additionally, I receive another question asking me if wait statistics matters in SQL Server 2000. If it is, then the asker wants to know how to measure wait types for SQL Server 2000.

In SQL Server, you can run the following command to get a list of all the wait types:

DBCC SQLPERF(waitstats)

The query above will work in SQL Server 2005/2008/R2  because of backup compatibility. As you might have noticed, I have been discussing everything keeping SQL Server 2005+ in mind, but I have given little consideration on SQL Server 2000. However, I am pretty sure that most of the suggestions I have provided are applicable to SQL Server 2000. The wait types I have been discussing mostly exist in SQL Server 2000 as well. But the difference of the 2000 version is that it gets late recent releases, but it is worth it.

Wait types are very essential to measure performance bottleneck. Because of this, I do not have to state that I am big fan of them just so I could identify performance bottleneck.

Please read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)