//
you're reading...
SQL Server

Waits and Queues: How Do They Help Me?

As an application, SQL Server requests system resources just like any other application to fullfil its primary objectives. For example it may request system resources as it executes a user query and waits for its request to be completed.

Waits in SQL Server are represented by wait statistics. SQL Server tracks wait information any time that a user connection or session_id is waiting. This wait information is summarized and categorized across all connections creating a performance profile for a given work load. SQL Server wait types identify and categorize user\threads waits from an application workload or user perspective.

Queues in SQL Server measure system resources and utilization.  Queues are represented by Performance Monitor objects and counters and other sources of information. Performance Monitor counters measure various aspects of performance such as transfer rates for disks or the processor time consumed, etc.  SQL Server object counters are exposed using the dynamic management view (DMV) sys.dm_os_performance_counters. Performance Monitor counters show performance from a resource point of view.

The correlation of wait types to performance counters round out the picture.  The association of waits and queues allows one to identify potential problem areas and performance bottlenecks and narrow the focus effectively on the problem area.  Comparisons of one counter to another provide perspective in order to draw the right conclusion.  For example, say you encounter 4500 lock waits during a production workload.  Does this represent a serious performance problem? maybe.  To determine what role the lock waits play in overall performance you would also need to consider the total number of lock requests, the wait time or duration of the lock waits, and the time span of the workload.  If the total number of lock requests was 10 million, perhaps 4500 waits is insignificant. Additionally If the associated lock wait time is 50 seconds overall, and the workload is over 8 hours, this would probabaly be insignificant.  However, if you average a 50 second wait for EACH of the 4500 lock waits, obviously this would be significant.

Application performance can be easily explained by looking at SQL Server waits and System\Resource queues. In SQL Server, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. For the system or resource perspective use Performance Monitor counters (and other data sources) to provide a breakdown of system resource utilization according to resource queues. Taken together, examination of the waits and queues enables one to rapidly narrow in on potential performance bottlenecks.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: