Category Archives: SQL Wait Stats

by

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
)

About these ads
by

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

by

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
)

by

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
)

by

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
)

by

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

When I decided to start writing about this wait type, the very first question that came to my mind was, “What does ‘OLEDB’ stand for?” A quick search on Wikipedia tells me that OLEDB means Object Linking and Embedding Database. (How many of you knew this?) Anyway, I found it very interesting that this wait type was in one of the top 10 wait types in many of the systems I have come across in my performance tuning experience.

Books On-Line:

OLEDB occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.

OLEDB Explanation:

This wait type primarily happens when Link Server or Remove Query has been executed. The most common case wherein this wait type is visible is during the execution of Linked Server. When SQL Server is retrieving data from the remote server, it uses OLEDB API to retrieve the data. It is possible that the remote system is not quick enough or the connection between them is not fast enough, leading SQL Server to wait for the result’s return from the remote (or external) server. This is the time OLEDB wait type occurs.

Reducing OLEDB wait:

  • Check the Link Server configuration.
  • Checking Disk-Related Perfmon Counters
    • Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

At this point in time, I am not able to think of any more ways on reducing this wait type. Do you have any opinion about this subject? Please share it here and I will share your comment with the rest of the Community, and of course, with due credit unto you.

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
)

by

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

Jacob Sebastian is a SQL Server MVP, Author, Speaker and Trainer. Jacob is one of the top rated expert community. Jacob wrote the book The Art of XSD – SQL Server XML Schema Collections and wrote the XML Chapter in SQL Server 2008 Bible. See his Blog | Profile. He is currently researching on the subject of Filestream and have submitted this interesting article on the very subject.

What is FILESTREAM?

FILESTREAM is a new feature introduced in SQL Server 2008 which provides an efficient storage and management option for BLOB data.

Many applications that deal with BLOB data today stores them in the file system and stores the path to the file in the relational tables. Storing BLOB data in the file system is more efficient that storing them in the database. However, this brings up a few disadvantages as well. When the BLOB data is stored in the file system, it is hard to ensure transactional consistency between the file system data and relational data.

Some applications store the BLOB data within the database to overcome the limitations mentioned earlier. This approach ensures transactional consistency between the relational data and BLOB data, but is very bad in terms of performance.

FILESTREAM combines the benefits of both approaches mentioned above without the disadvantages we examined. FILESTREAM stores the BLOB data in the file system (thus takes advantage of the IO Streaming capabilities of NTFS) and ensures transactional consistency between the BLOB data in the file system and the relational data in the database.

For more information on the FILESTREAM feature, visit:
http://beyondrelational.com/filestream/default.aspx

FILESTREAM Wait Types

Since this series is on the different SQL Server wait types, let us take a look at the various wait types that are related to the FILESTREAM feature.

FS_FC_RWLOCK

This wait type is generated by FILESTREAM Garbage Collector. This occurs when Garbage collection is disabled prior to a backup/restore operation or when a garbage collection cycle is being executed.

FS_GARBAGE_COLLECTOR_SHUTDOWN

This wait type occurs when during the cleanup process of a garbage collection cycle. It indicates that that garbage collector is waiting for the cleanup tasks to be completed.

FS_HEADER_RWLOCK

This wait type indicates that the process is waiting for obtaining access to the FILESTREAM header file for read or write operation. The FILESTREAM header is a disk file located in the FILESTREAM data container and is named “filestream.hdr”.

FS_LOGTRUNC_RWLOCK

This wait type indicates that the process is trying to perform a FILESTREAM log truncation related operation. It can be either a log truncate operation or to disable log truncation prior to a backup or restore operation.

FSA_FORCE_OWN_XACT

This wait type occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.

FSAGENT

This wait type occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.

FSTR_CONFIG_MUTEX

This wait type occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.

FSTR_CONFIG_RWLOCK

This wait type occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.

Waits and Performance

System waits has got a direct relationship with the overall performance. In most cases, when waits increase the performance degrades. SQL Server documentation does not say much about how we can reduce these waits. However, following the FILESTREAM best practices will help you to improve the overall performance and reduce the wait types to a good extend.

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

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