SQL Server 2008 : Common performance problems (part 2)

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
5/9/2013 9:52:35 PM

2. CPU pressure

How do you measure CPU pressure for a SQL Server system? While classic Performance Monitor counters such as Processor:% Processor Time and System:Processor Queue Length provide a general overview, they are insufficient on their own to use in forming the correct conclusion. For that, we need to look a little further, with signal waits a critical consideration.

Signal waits

We looked at how SQLOS uses schedulers in allocating CPU time with processes (SPIDs) moving between three states: running, suspended, and runnable. There can be only a single SPID in the running status of a given scheduler at any one time, with the runnable queue containing SPIDs that are ready to run. The classic analogy used when discussing this model is the supermarket checkout line; that is, SPIDs in the runnable queue can be considered in the same manner as people lining up in the checkout queue: they have their groceries and are ready to leave, pending the availability of the checkout operator.

As we saw earlier, the sys.dm_os_wait_stats DMV includes a signal_wait_time_ms column, which indicates the amount of time, in total, processes spent in the runnable status for each wait type. Calculating the sum total of the signal wait time for all wait types as a percentage of the overall wait time gives a good indication of the depth of the runnable queue and therefore an indication of CPU pressure, from a SQL Server perspective.

When calculating the signal wait percentage, you should consider excluding certain wait types, LAZYWRITER_SLEEP, for example.

Generally speaking, a signal wait percentage of more than 25 percent may indicate CPU pressure, particularly in combination with a sustained high value for Processor:% Processor Time (> 80 percent). However, in some cases, CPU percentage may be well below 100 percent even though there is still significant CPU pressure. In such cases, the SOS_SCHEDULER_YIELD wait type is more than likely in the mix.


As we covered earlier, a single SQLOS scheduler is created for each CPU core that a SQL Server instance has access to. When a request is sent to SQL Server for execution, it's assigned to a scheduler for execution and remains on that scheduler until complete. Despite SQL Server using various mechanisms for balancing load across the available schedulers, various situations may lead to a disproportionate load being assigned to a single scheduler.

Consider the following example: at a given point, all schedulers are equally busy, and two large CPU bound queries are submitted for execution. If they land on the same scheduler, they must remain on that scheduler until complete, even if load drops off the others. When multiple CPU bound tasks are executing on one scheduler, they yield to each other in order to ensure each task receives equal amounts of CPU time. In our example, the two large CPU bound tasks would be yielding to each other, despite the availability of a number of idle schedulers.[] In this case, the total CPU percentage (as reported by Performance Monitor) may be well below 100 percent, even though there is significant contention on some CPU cores.

[] In this case restarting one of the processes would more than likely position it on an idle scheduler.

Of course, if a system is completely CPU bound (all CPU cores), then the yielding process, exposed with the SOS_SCHEDULER_YIELD wait type, would be occurring on all schedulers. The important point is that the yielding process may be occurring on only some schedulers, even when overall CPU usage appears low. This makes the SOS_SCHEDULER_YIELD wait type an important consideration in an overall assessment of CPU pressure.


Scheduler details can be inspected with the sys.dm_os_schedulers DMV. Querying this DMV will reveal one scheduler per CPU core that the instance has access to, a number of system schedulers, and one for the dedicated administrator connection (DAC). Included columns reveal a number of scheduler-level details such as the number of tasks and yield count.

In closing this section on CPU pressure, let's look at some DMV queries that can be used in supplementing information from the waits and queues analysis.

DMV queries

The two DMV queries in this section identify signal wait percentage and top CPU consumers.

Listing 5 can be used to detect signal waits and resource waits as a percentage of the total wait time. As discussed previously, a high signal wait time usually indicates CPU pressure.

Example 5. Signal wait time
-- Wait Analysis; Signal vs. Resource Waits
   SUM(wait_time_ms - signal_wait_time_ms) as [ResourceWaitTotal]
   , CAST(100.0 * sum(wait_time_ms - signal_wait_time_ms)
      / SUM(wait_time_ms) as numeric(20, 2)) AS [ResourceWait%]
   , SUM(signal_wait_time_ms) AS [SignalWaitTotal]
   , CAST (100.0 * sum(signal_wait_time_ms)
      / SUM (wait_time_ms) AS numeric(20, 2)) AS [SignalWait%]
FROM sys.dm_os_wait_stats
    wait_type not in (
        , 'RESOURCE_QUEUE'
        , 'SLEEP_TASK'

        , 'WAITFOR'

Notice that the script excludes a number of wait types not relevant to a performance-tuning process. The get/track_waitstats stored procedures we covered earlier perform the same exclusions.

Listing 6 lists the top 50 queries ordered by CPU time. It includes the execution_count column to indicate how many times this query has been executed. Frequently executed queries with large CPU consumption are targets for optimization.

Example 6. Top 50 queries by CPU consumption
-- Top 50 Queries by CPU Consumption
   queryStats.total_worker_time/queryStats.execution_count AS [Avg CPU
Time] , queryStats.execution_count , SUBSTRING(queryText.text,queryStats.statement_start_offset/2, (CASE WHEN queryStats.statement_end_offset = -1 THEN len(convert(nvarchar(max), queryText.text)) * 2 ELSE queryStats.statement_end_offset end - queryStats.statement_start_offset) / 2) AS query_text , dbname=db_name(queryText.dbid) FROM sys.dm_exec_query_stats queryStats CROSS APPLY sys.dm_exec_sql_text(queryStats.sql_handle) AS queryText ORDER BY [Avg CPU Time] DESC

One of the things that will become obvious as we move throughout this section is that common design problems affect a number of resources. For example, poor index selection and maintenance can have a dramatic impact on both memory and disk I/O.

3. Index-related memory pressure

The correct selection and maintenance of indexes are crucial from a query-performance perspective. Numerous unused indexes have a large maintenance overhead, and missing or poorly maintained indexes have a double impact on resources: additional disk I/O and a reduction in the available buffer cache.

From a performance-monitoring perspective, the following performance counters are of interest in assessing the impact of poor index design and maintenance:

  • SQL Server:Buffer Manager - Page Life Expectancy—This counter indicates the average time (in seconds) that data pages remain in memory. A common occurrence is for this value to drop suddenly in response to a large query that requires a lot of disk access, flushing data pages from memory to make way for the required data from disk. Missing indexes are a common contributor to this type of event. A system with adequate memory and good indexing should see this value in excess of 500 seconds, without frequent sudden drops during normal activity.

  • SQL Server:Buffer Manager - Buffer Cache Hit Ratio—This counter indicates the percentage of time required pages are found in the buffer cache. The higher the value, the better, as memory access is obviously much faster than disk access. Once a SQL instance has been up and running for a period of time covering typical activity, values lower than 95 percent indicate memory pressure, one cause of which may be additional disk I/O required to fulfill queries without the appropriate indexes.

  • SQL Server:Access Methods - Full Scans/Sec—This counter represents the number of full table (or index) scans per second. There are no benchmark numbers to compare this value against. In some cases, a table (or index) scan is actually preferred over an index lookup; however, one thing to look out for here is a sudden increase in this value, possibly indicating that an index is no longer being used. As with many other counters, baseline analysis is critical in being able to accurately detect a significant increase/decrease.

  • SQL Server:Access Methods - Index Searches/Sec—Similar to Full Scans/Sec (but in the opposite direction), sudden decreases in this value may indicate an index is no longer being used.

  • SQL Server:Access Methods - Page Splits/Sec—When a record is inserted into an index, it must be inserted in order. If the data page is full, the page splits in order to maintain the appropriate order. A high value for this counter may warrant the consideration of a lower fill factor.

In addition to increased memory pressure, poor index selection and maintenance have a direct and measurable impact on disk I/O.

4. Disk bottlenecks

Throughout this book we've covered a number of best practices pertaining to the layout of data and transaction log files, tempdb configuration, and sizing files to avoid autogrow operations. We've also covered the importance of striping data across multiple disks (spindles) and using RAID volumes for both performance and redundancy. With these things in mind, let's explore the waits and queues of significance in a disk bottleneck.


A classic performance-tuning dictum is there will always be a bottleneck somewhere, the idea being to address/reduce each bottleneck until performance is acceptable. As the slowest component, the bottleneck is usually on disk, on both high- and low-performing systems. The following wait types usually occupy the top two wait-list positions (after excluding system background waits) on systems experiencing disk bottlenecks:

  • PAGEIOLATCH—As pages are read into the buffer cache from disk, SQL Server uses a series of latches (lightweight locks) on the buffer pages as they are filled with data and released to the requesting process. Both PAGEIOLATCH_SH and PAGEIOLATCH_EX are used as part of this process, and the appearance of these wait types in the top wait positions may be an indication of a disk I/O bottleneck, particularly when seen in combination with high disk sec/transfer counters, which we'll cover shortly.

  • ASYNC/IO_COMPLETION—Both ASYNC_IO_COMPLETION and IO_COMPLETION indicate waits on disk I/O, with the async version typically associated with operations such as backups and restores.

  • WRITELOG—This wait type is associated with writes to the transaction log. As covered throughout this book, locating transaction logs on dedicated disk volumes, preferably with a large battery-backed write cache, is essential in any high-volume database solution.

A top ranking of these waits necessitates the inspection of a number of related queues.


The classic disk-related Performance Monitor counters are PhysicalDisk:Avg. Disk Sec/Read and PhysicalDisk:Avg. Disk Sec/Write, with the commonly accepted ranges for performance as follows:

  • < 10ms—Good

  • 10-20ms—Average/typical performance

  • 20-50ms—Slow

  • > 50ms—Very slow, needs immediate attention

These counters measure the time in milliseconds for a read or write operation to disk and should be measured for each of the applicable disk volumes. For high-throughput applications, you need to pay particular attention to these counters for the transaction log disk, which should be well under 10ms.

As we covered earlier in the book, you should use the SQLIO and SQLIOSIM tools before commissioning any SQL Server system for production use to verify both the throughput and validity of the I/O system and compare the results to published vendor performance expectations. In addition to these counters, additional counters of interest are as follows:

  • Physical Disk:% Disk Time—This counter measures the percentage of time the disk was busy servicing reads and writes. The generally accepted idea is that more than 50 percent may represent a bottleneck for the measured disk.

  • Physical Disk:Avg./Current Disk Queue Length—A sustained value of more than 2 indicates the disk is struggling to service its queue. When measuring these counters, you need to consider the number of disks in the array. For example, a volume with 10 disks could reasonably service a queue of up to 20.

  • Physical Disk:Avg. Disk Reads & Writes /Sec—As with the disk queue-length counters, you need to measure these counters in awareness of the disk volume's underlying disk count. Values approaching 90 percent of the disk's published read/writes per second capacity may indicate an approaching bottleneck.

Bear in mind that during disk-bound operations such as backups and restores, it's perfectly normal and reasonable to see sustained disk activity, with cause for concern centered on the speed or duration of the operation. For example, backing up a terabyte database to disk will obviously bottleneck on disk; however, the performance can be maximized by using dedicated backup disks, multiple backup files, and so forth.

In closing our brief look at disk I/O, let's examine a couple of DMV queries.

DMV queries

The two DMV queries in this section identify queries with the largest I/O usage and database files with the highest stall rates.

Listing  7 lists the top 50 queries ordered by I/O usage.

Example 7. Top 50 queries by I/O usage
-- Top 50 Queries by I/O Consumption
   (total_logical_reads + total_logical_writes) / execution_count AS [Avg
IO] , substring (qt.text,qs.statement_start_offset/2, ( CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end - qs.statement_start_offset)/2 ) AS query_text , qt.dbid , qt.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt ORDER BY [Avg IO] DESC

Listing 8 uses the sys.dm_io_virtual_file_stats function to inspect the stall rate of disk I/O per file. A stall occurs when a process waits for I/O to complete. By determining which files are stalling the most, opportunities arise for rebalancing I/O. A good example of this is multiple high-transaction-rate databases using the same physical disk(s). By segregating their database files on separate disks, you should be able to improve the throughput (and reduce the stall rate).

Example 8. Database file I/O stalls
-- Identify database files with the highest stall rate
    , file_id
    , io_stall_read_ms
    , num_of_reads
    , cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as
    , io_stall_write_ms
    , num_of_writes
    , cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as
    , io_stall_read_ms + io_stall_write_ms as io_stalls

, num_of_reads + num_of_writes as total_io
    , cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads +
      num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
FROM sys.dm_io_virtual_file_stats(null,null)
WHERE database_id > 4
     database_id, avg_io_stall_ms DESC

In closing this section on common performance problems, let's turn our attention to blocking.

5. Blocking

A block occurs when one query wants to access data that is locked by another. Despite blocks and locks being normal, fundamental components of any relational database management system, they present a significant problem in poorly designed databases and transactions.

In this section, we'll cover blocking from a waits perspective.

LCK_* waits

SQL Server Books Online lists all of the wait types that may be encountered, including an impressive collection beginning with LCK_. All of these represent a wait on a particular lock being released. For example, a transaction with a shared lock on a row will block a separate transaction that requires an exclusive lock on the same row. In this case, the registered wait type will be LCK_M_X.

When the volume of locks and subsequent blocks increases, overall transaction throughput decreases, often accompanied by a reduction in Performance Monitor counters such as SQL Server SQL Statistics:Batch Requests/Sec. To the untrained eye, a severe blocking problem is often met with confusion; users complain of poor performance, but a quick check of the classic Performance Monitor counters (CPU, disk, and so forth) reveals little in the way of server load. When the blocking transaction completes, activity returns to normal.

A fundamental component of a good database design is short transaction length with the appropriate isolation level (and usually with an optimistic locking mode). Such topics are beyond the scope of this book: however, should you identify blocking as a top wait type, the sys.dm_db_index_operational_stats function can assist you in investigating the problem further.


One of the purposes of the sys.dm_db_index_operational_stats function is to determine the tables and indexes with the highest occurrence of row lock waits, as shown in listing 9.

Example 9. Tables/indexes with high lock waits
— Identify tables and indexes with the highest number of row lock waits
   , object_name(s.object_id) as objectname
   , as indexname
   , row_lock_count
   , row_lock_wait_count
   , cast (100.0 * row_lock_wait_count /
     (1 + row_lock_count) as numeric(15,2)) as [block %]
   , row_lock_wait_in_ms
   , cast (1.0 * row_lock_wait_in_ms /
     (1 + row_lock_wait_count) as numeric(15,2)) as [avg row lock waits in
ms] FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s INNER JOIN sys.indexes i on s.object_id = i.object_id AND s.index_id = i.index_id WHERE objectproperty(s.object_id,'IsUserTable') = 1 ORDER BY row_lock_wait_count desc

In combination with the SQL Profiler blocked process report event, this script can be used to identify the source of common blocking problems. The avg row lock waits in ms column, as the name suggests, returns the average lock wait time. This value can be used in estimating which value to set for the sp_configure 'blocked process threshold' value, although it should be noted that the sp_configure value is set in seconds, whereas the value returned from the above script is in milliseconds.

Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us