Hardware and Network

As a first step in troubleshooting, it is beneficial to look at the SQL Server hardware and network configuration. There are several aspects of this involved. First, it makes sense to analyze if the server is powerful enough to handle the load. Obviously, this is a very subjective question, which often cannot be answered based solely on the server specifications. However, in some cases, you will see that the hardware is clearly underpowered.

One example when this happens is with systems developed by Independent Software Vendors (ISV) and deployed in an Enterprise environment. Such deployments usually happen in stages. Decision makers evaluate system functionality under a light load during the trial/pilot stage. It is entirely possible that the database has been placed into second-grade hardware or an under-provisioned virtual machine during trials and stayed there even after full deployment.

SQL Server is a very I/O intensive application, and a slow or misconfigured I/O subsystem often becomes a performance bottleneck. One very important setting that is often overlooked is partition alignment. Old versions of Windows created partitions right after 63 hidden sectors on a disk, which striped the disk allocation unit across multiple stripe units in RAID arrays. With such configurations, a single I/O request to a disk controller leads to multiple I/O operations to access data from the different RAID stripes.

Fortunately, partitions created in Windows Server 2008 and above are aligned by default. However, Windows does not re-align existing partitions created in older versions of Windows when you upgrade operating systems or attach disks to servers. It is possible to achieve a 20-40 percent I/O performance improvement by fixing an incorrect partition alignment without making any other changes to the system.

Windows allocation unit size also comes into play. Most SQL Server instances would benefit from 64KB units, however you should take the RAID stripe size into account. Use the RAID stripe size recommended by the manufacturer; however, make sure that the Windows allocation unit resides on the single RAID stripe. For example, a 1MB RAID stripe size works fine with 64KB windows allocation units hosting 16 allocation units per stripe when disk partitions are aligned.

Finally, you need to analyze network throughput. Network performance depends on the slowest link in the topology. This is especially important in cases of network-based storage when every physical I/O operation utilizes the network. For example, if one of the network switches in the path between SQL Server and a SAN has 2-gigabit uplink, the network throughput would be limited to 2 gigabits, even when all other network components in the topology are faster than that. Moreover, always remember to factor in the distance information travels over a network. Accessing remote data adds extra latency and slows down communications.

Operating System Configuration

You should look at the operating system configuration in the next step. It is especially important in the case of a 32-bit OS where the amount of user memory available to processes is limited. It is crucial that you check that SQL Server can use extended memory and that the “Use AWE Memory” setting is enabled.

You should check what software is installed and what processes are running on the server. Non-essential processes use memory and contribute to server CPU load. Think about anti-virus software as an example. It is better to protect the server from viruses by restricting user access and revoking administrator permissions, rather than to have anti-virus software constantly running on the server. If company policy requires that you have anti-virus up and running, make sure that the system and user databases are excluded from the scan.

Using development and troubleshooting tools locally on the server is another commonly encountered mistake. Developers and database administrators often run Management Studio, SQL Profiler, and other tools on a server during deployment and troubleshooting. Those tools reduce the amount of memory available to SQL Server and contribute to unnecessary load. It is always better to access SQL Server remotely whenever possible.

Also check if SQL Server is virtualized. Virtualization helps reduce IT costs, improves the availability of the system, and simplifies management. However, virtualization adds another layer of complexity during performance troubleshooting. Work with system administrators, or use third-party tools, to make sure that the host is not overloaded, even when performance metrics in a guest virtual machine appear normal.

Another common problem related to virtualization is resource over-allocation. As an example, it is possible to configure a host in such a way that the total amount of memory allocated for all guest virtual machines exceeds the amount of physical memory installed on the host. That configuration leads to artificial memory pressure and introduces performance issues for a virtualized SQL Server. Again, you should work with system administrators to address such situations.

SQL Server Configuration

It is typical to have multiple databases hosted on a SQL Server instance. Database consolidation helps lower IT costs by reducing the number of servers that you must license and maintain. All those databases, however, use the same pool of SQL Server resources, contribute to its load, and affect each other. Heavy SQL Server workload from one system can negatively impact the performance of other systems.

You can analyze such conditions by examining resource-intensive and frequently executed queries on the server scope. If you detect a large number of such queries coming from different databases, you may consider optimizing all of them or to separate the databases among different servers. We will discuss how to detect such queries later in this chapter.

You should also check if multiple SQL Server instances are running on the same server and how they affect the performance of each other. This condition is a bit trickier to detect and requires you to analyze various performance counters and DMOs from multiple instances. One of the most common problems in this situation happens when multiple SQL Server instances compete for memory, introducing memory pressure on each other. It might be beneficial to set and fine-tune the minimum and maximum memory settings for each instance based on requirements and load.

It is also worth noting that various Microsoft and third-party products often install separate SQL Server instances without your knowledge. Always check to see if this is the case on non-dedicated servers.

Finally, check the tempdb configuration and make sure that it is optimal, as we have already discussed in Chapter 12, “Temporary Tables.”

Database Options

Every database should have the Auto Shrink option disabled. As we have already discussed, the Auto Shrink periodically triggers the database shrink process, which introduces unnecessary I/O load and heavy index fragmentation. Moreover, this operation is practically useless because further data modifications and index maintenance make database files grow yet again.

The Auto Close option forces SQL Server to remove any database-related objects from memory when the database does not have any connected users. As you can guess, it leads to extra physical I/O and query compilations as users reconnect to the database afterwards. With the rare exception of very infrequently accessed databases, the Auto Close setting should be disabled.

It is better to have multiple data files in filegroups with volatile data. This helps avoid allocation map contention, similar to what happens in the case of tempdb. We will discuss the symptoms of such contention later in this chapter.

I/O Subsystem and Non-Optimized Queries

The most common root-cause of issues related to a slow and/or overloaded I/O subsystem is non-optimized queries, which require SQL Server to scan a large amount of data. When SQL Server does not have enough physical memory to cache all of the required data in the buffer pool, which is typically the case for large systems, physical I/O occurs and constantly replaces data in the buffer pool.

Figure 27-6 illustrates the situation with non-optimized queries, and it shows the metrics and tools that can be used to diagnose and fix these problems.

PAGEIOLATCH_* wait types occur when SQL Server is waiting for an I/O subsystem to bring a data page from disk to the buffer pool. A large percentage of those waits indicate heavy physical I/O activity in the system. Other I/O wait types, such as IO_COMPLETION, ASYNC_IO_COMPLETION, BACKUPIO, WRITELOG, and LOGBUFFER relate to non-data pages I/O. Those wait types may occur for various reasons. IO_COMPLETION often indicates slow tempdb I/O performance during sort and hash operators. BACKUPIO is a sign of slow performance of a backup disk drive, and it often occurs with an ASYNC_IO_COMPLETION wait type. WRITELOG and LOGBUFFER waits are a sign of bad transaction log I/O throughput.

When all of those wait types are present together, it is easier to focus on reducing PAGEIOLATCH waits and data-related I/O. This will reduce the load on the I/O subsystem and, in turn, it can improve the performance of non-data–related I/O operations. Otherwise, when PAGEIOLATCH waits are not very significant, you need to look at I/O subsystem performance and configuration and correlate the data from the various metrics we are about to discuss.

The sys.dm_io_virtual_file_stats function provides you with I/O statistics for data and log files including information about a number of I/O operations, the amount of data processed, and I/O stalls, which is the time that SQL Server waited for I/O operations to complete. This can help you detect most I/O intensive databases and data files, which is especially useful when a SQL Server instance hosts a large number of databases.

Listing 27-3 shows you the query that obtains information about I/O statistics for all of the databases on a server. Figure 27-7 illustrates the partial output of the query from one of the production servers.

Listing 27-3.  Using sys.dm_io_virtual_file_stats

select
        fs.database_id as [DB ID]
        ,fs.file_id as [File Id]
        ,mf.name as [File Name]
        ,mf.physical_name as [File Path]
        ,mf.type_desc as [Type]
        ,fs.sample_ms as [Time]
        ,fs.num_of_reads as [Reads]
        ,fs.num_of_bytes_read as [Read Bytes]
        ,fs.num_of_writes as [Writes]
        ,fs.num_of_bytes_written as [Written Bytes]
        ,fs.num_of_reads + fs.num_of_writes as [IO Count]
        ,convert(decimal(5,2),100.0 * fs.num_of_bytes_read /
                (fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Read %]
        ,convert(decimal(5,2),100.0 * fs.num_of_bytes_written /
                (fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Write %]
        ,fs.io_stall_read_ms as [Read Stall]
        ,fs.io_stall_write_ms as [Write Stall]
        ,case when fs.num_of_reads = 0
                then 0.000
                else convert(decimal(12,3),1.0 *
                        fs.io_stall_read_ms / fs.num_of_reads)
        end as [Avg Read Stall]
        ,case when fs.num_of_writes = 0
                then 0.000
                else convert(decimal(12,3),1.0 *
                        fs.io_stall_write_ms / fs.num_of_writes)
        end as [Avg Write Stall]
from
        sys.dm_io_virtual_file_stats(null,null) fs join
                sys.master_files mf with (nolock) on
                        fs.database_id = mf.database_id and
                        fs.file_id = mf.file_id
        join sys.databases d with (nolock) on
                d.database_id = fs.database_id
where
        fs.num_of_reads + fs.num_of_writes > 0
option (recompile)

Unfortunately, sys.dm_io_virtual_file_stats provides cumulative statistics as of the time of a SQL Server restart without any way to clear it. If you need to get a snapshot of the current load in the system, you should run this function several times and compare how the results changed between calls. Listing 27-4 shows the code that allows you to do that.

Listing 27-4.  Using sys.dm_io_virtual_file_stats to obtain statistics about the current I/O load

create table #Snapshot
(
        database_id smallint not null,
        file_id smallint not null,
        num_of_reads bigint not null,
        num_of_bytes_read bigint not null,
        io_stall_read_ms bigint not null,
        num_of_writes bigint not null,
        num_of_bytes_written bigint not null,
        io_stall_write_ms bigint not null
);
 
insert into #Snapshot(database_id,file_id,num_of_reads,num_of_bytes_read
        ,io_stall_read_ms,num_of_writes,num_of_bytes_written
        ,io_stall_write_ms)
        select database_id,file_id,num_of_reads,num_of_bytes_read
                ,io_stall_read_ms,num_of_writes,num_of_bytes_written
                ,io_stall_write_ms
        from sys.dm_io_virtual_file_stats(NULL,NULL)
option (recompile);
 
-- Set test interval (1 hour)
waitfor delay '00:01:00.000';
 
;with Stats(db_id, file_id, Reads, ReadBytes, Writes
        ,WrittenBytes, ReadStall, WriteStall)
as
(
        select
                s.database_id, s.file_id
                ,fs.num_of_reads - s.num_of_reads
                ,fs.num_of_bytes_read - s.num_of_bytes_read
                ,fs.num_of_writes - s.num_of_writes
                ,fs.num_of_bytes_written - s.num_of_bytes_written
                ,fs.io_stall_read_ms - s.io_stall_read_ms
                ,fs.io_stall_write_ms - s.io_stall_write_ms
        from
                #Snapshot s cross apply
                        sys.dm_io_virtual_file_stats(s.database_id, s.file_id) fs
)
select
        s.db_id as [DB ID], d.name as [Database]
        ,mf.name as [File Name], mf.physical_name as [File Path]
        ,mf.type_desc as [Type], s.Reads
        ,convert(decimal(12,3), s.ReadBytes / 1048576.) as [Read MB]
        ,convert(decimal(12,3), s.WrittenBytes / 1048576.) as [Written MB]
        ,s.Writes, s.Reads + s.Writes as [IO Count]
        ,convert(decimal(5,2),100.0 * s.ReadBytes /
                        (s.ReadBytes + s.WrittenBytes)) as [Read %]
        ,convert(decimal(5,2),100.0 * s.WrittenBytes /
                        (s.ReadBytes + s.WrittenBytes)) as [Write %]
        ,s.ReadStall as [Read Stall]
        ,s.WriteStall as [Write Stall]
        ,case when s.Reads = 0
                then 0.000
                else convert(decimal(12,3),1.0 * s.ReadStall / s.Reads)
        end as [Avg Read Stall]
        ,case when s.Writes = 0
                then 0.000
                else convert(decimal(12,3),1.0 * s.WriteStall / s.Writes)
        end as [Avg Write Stall]
from
        Stats s join sys.master_files mf with (nolock) on
                s.db_id = mf.database_id and
                s.file_id = mf.file_id
        join sys.databases d with (nolock) on
                s.db_id = d.database_id
where
        s.Reads + s.Writes > 0
order by
        s.db_id, s.file_id
option (recompile)

You can analyze various system performance counters from the PhysicalDisk object to obtain information about current I/O activity, such as the number of requests and the amount of data being read and written. Those counters, however, are the most useful when compared against the baseline, which we will discuss later in this chapter.

Performance counters from the Buffer Manager Object provide various metrics related to the buffer pool and data page I/O. One of the most useful counters is Page Life Expectancy, which indicates the average time a data page stays in the buffer pool. Historically, Microsoft suggested that values above 300 seconds are acceptable and good enough, however this is hardly the case with modern servers that use large amounts of memory. One approach to defining the lowest-acceptable value for the counter is by multiplying 300 seconds for every 4GB of buffer pool memory. For example, a server that uses 56GB of memory for the buffer pool should have a Page Life Expectancy greater than 4,200 seconds (56/4*300). However, as with other counters, it is better to compare the current value against a baseline, rather than relying on a statically defined threshold.

Page Read/Secand Page Write/Sec counters show the number of physical data pages that were read and written respectively. Checkpoint Pages/Sec and Lazy Writer/Sec indicate the activity of the checkpoint and lazy writer processes that save dirty pages to disks. High numbers in those counters and a low value for Page Life Expectancy could be a sign of memory pressure. However, a high number of checkpoints could transpire due to a large number of transactions in the system, and you should include the Transactions/Sec counter in the analysis.

The Buffer Cache Hit Ratioindicates the percentage of pages that are found in the buffer pool without the requirement of performing a physical read operation. A low value in this counter indicates a constant buffer pool flush and is a sign of a large amount of physical I/O. However, a high value in the counter is meaningless. Read-ahead reads often bring data pages to memory, increasing the Buffer Cache Hit Ratio value and masking the problem. In the end, Page Life Expectancy is a more reliable counter for this analysis.

Full Scans/Sec and Range Scan/Sec performance counters from the Access Methods Object provide you with information about the scan activity in the system. Their values, however, can be misleading. While scanning a large amount of data negatively affects performance, small range scans or full scans of small temporary tables are completely acceptable. As with other performance counters, it is better to compare counter values against a baseline rather than relying on absolute values.

There are several ways to detect I/O intensive queries using standard SQL Server tools. One of the most common approaches is by capturing system activity using SQL Trace or Extended Events, filtering the data by the number of reads and/or writes or duration.

This approach, however, requires you to perform additional analysis after the data is collected. You should check how frequently queries are executed when determining targets for optimization.

Another very simple and powerful method of detecting resource-intensive queries is the sys.dm_exec_query_statsdata management view. SQL Server tracks various statistics including the number of executions and I/O operations, elapsed and CPU times, and exposes them through that view. Furthermore, you can join it with other data management objects and obtain the SQL Text and execution plans for those queries. This simplifies the analysis, and it can be helpful during the troubleshooting of various performance and plan cache issues in the system.

Listing 27-5 shows a query that returns the 50 most I/O intensive queries, which have plan cached at the moment of execution.

Listing 27-5.  Using sys.dm_exec_query_stats

select top 50
        substring(qt.text, (qs.statement_start_offset/2)+1,
                ((
                        case qs.statement_end_offset
                                when -1 then datalength(qt.text)
                                else qs.statement_end_offset
                        end - qs.statement_start_offset)/2)+1) as SQL
        ,qp.query_plan as [Query Plan]
        ,qs.execution_count as [Exec Cnt]
        ,(qs.total_logical_reads + qs.total_logical_writes) /
                qs.execution_count as [Avg IO]
        ,qs.total_logical_reads as [Total Reads]
        ,qs.last_logical_reads as [Last Reads]
        ,qs.total_logical_writes as [Total Writes]
        ,qs.last_logical_writes as [Last Writes]
        ,qs.total_worker_time as [Total Worker Time]
        ,qs.last_worker_time as [Last Worker Time]
        ,qs.total_elapsed_time / 1000 as [Total Elapsed Time]
        ,qs.last_elapsed_time / 1000 as [Last Elapsed Time]
        ,qs.last_execution_time as [Last Exec Time]
        ,qs.total_rows as [Total Rows]
        ,qs.last_rows as [Last Rows]
        ,qs.min_rows as [Min Rows]
        ,qs.max_rows as [Max Rows]
from
        sys.dm_exec_query_stats qs with (nolock)
                cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
                cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
order by
        [Avg IO] desc
option (recompile)

As you can see in Figure 27-8, it allows you to define optimization targets easily based on resource usage and the number of executions. For example, the second query in the result set is the best candidate for optimization due to how frequently it runs.

Unfortunately, sys.dm_exec_query_stats does not return any information for the queries, which do not have compiled plans cached. Usually, this is not an issue because our optimization targets are not only resource intensive, but they also frequently executed queries. Plans of those queries usually stay in cache due to their frequent re-use. However, SQL Server does not cache plans in the case of a statement-level recompile, therefore sys.dm_exec_query_stats misses them. You should use SQL Trace and/or Extended events to capture them. I usually start with queries from the sys.dm_exec_query_stats function output and crosscheck the optimization targets with Extended Events later.

SQL Server 2008 and above provides stored procedure-level execution statistics with the sys.dm_exec_procedure_stats view. It provides similar metrics with sys.dm_exec_query_stats, and it can be used to determine the most resource-intensive stored procedures in the system. Listing 27-6 shows a query that returns the 50 most I/O intensive stored procedures, which have plan cached at the moment of execution.

Listing 27-6.  Using sys.dm_exec_procedure_stats

select top 50
        db_name(ps.database_id) as [DB]
        ,object_name(ps.object_id, ps.database_id) as [Proc Name]
        ,ps.type_desc as [Type]
        ,qp.query_plan as  [Plan]
        ,ps.execution_count as [Exec Count]
        ,(ps.total_logical_reads + ps.total_logical_writes) /
                ps.execution_count as [Avg IO]
        ,ps.total_logical_reads as [Total Reads]
        ,ps.last_logical_reads as [Last Reads]
        ,ps.total_logical_writes as [Total Writes]
        ,ps.last_logical_writes as [Last Writes]
        ,ps.total_worker_time as [Total Worker Time]
        ,ps.last_worker_time as [Last Worker Time]
        ,ps.total_elapsed_time / 1000 as [Total Elapsed Time]
        ,ps.last_elapsed_time / 1000 as [Last Elapsed Time]
        ,ps.last_execution_time as [Last Exec Time]
from
        sys.dm_exec_procedure_stats ps with (nolock)
                cross apply sys.dm_exec_query_plan(ps.plan_handle) qp
order by
        [Avg IO] desc
option (recompile)

There are plenty of tools available on the market to help you automate the data collection and analysis process including the SQL Server Management Data Warehouse. All of them help you to achieve the same goal and find optimization targets in the system.

Finally, it is worth mentioning that the Data Warehouse and Decision Support Systems usually play under different rules. In those systems, it is typical to have I/O intensive queries that scan large amounts of data. Performance tuning of such systems can require different approaches than those found in OLTP environments, and they often lead to database schema changes rather than index tuning.

Memory-Related Wait Types

The RESOURCE_SEMAPHORE wait type indicates the wait for the query memory grant. As already discussed, every query in SQL Server requires some memory to execute. When there is no memory available, SQL Server places a query in one of three queues, based on the memory grant size, where it waits until memory becomes available. A high percentage of RESOURCE_SEMAPHORE waits indicate that SQL Server does not have enough memory to fulfill all memory grant requests.

You can confirm the problem by looking at the Memory Grants Pending performance counter in the Memory Manager Object. This counter shows the number of queries waiting for memory grants. Ideally, the counter value should be zero all the time. You can also look at the sys.dm_exec_query_memory_grants view, which provides information about memory grant requests, both pending and outstanding.

Obviously, one of the ways to address this issue is to reduce the memory grant size for the queries. You can optimize or simplify the queries in a way that removes memory-intensive operators, hashes, and sorts, for example, from the execution plan. You can obtain the query plan and text from the sys.dm_exec_query_memory_grants view directly, as shown in Listing 27-7. It is also possible, however, to take a general approach and focus on non-optimized queries. General query optimization reduces the load on the system, which leaves more server resources available.

Listing 27-7.  Obtaining query information from the sys.dm_exec_query_memory_grants view

select
        mg.session_id
        ,t.text as [SQL]
        ,qp.query_plan as [Plan]
        ,mg.is_small
        ,mg.dop
        ,mg.query_cost
        ,mg.request_time
        ,mg.required_memory_kb
        ,mg.requested_memory_kb
        ,mg.wait_time_ms
        ,mg.grant_time
        ,mg.granted_memory_kb
        ,mg.used_memory_kb
        ,mg.max_used_memory_kb
from
        sys.dm_exec_query_memory_grants mg with (nolock)
                cross apply sys.dm_exec_sql_text(mg.sql_handle) t
                cross apply sys.dm_exec_query_plan(mg.plan_handle) as qp
option (recompile)

CXMEMTHREADis another memory-related wait type that you can encounter in systems. These waits occur when multiple threads are trying to allocate memory from HEAP simultaneously. You can often observe a high percentage of these waits in systems with a large number of ad-hoc queries where SQL Server constantly allocates and de-allocates plan cache memory. Enabling the Optimize for Ad-hoc Workloads configuration setting can help address this problem if plan cache memory allocation is the root-cause.

SQL Server has three types of memory objects that use HEAP memory. Some of them are created globally on the server scope. Others are partitioned on a per-NUMA Node or per-CPU basis. You can use startup trace flag T8048 to switch per-NUMA Node to per-CPU partitioning, which can help reduce CXMEMTHREAD waits at cost of extra memory usage.

Listing 27-8 shows you how to analyze memory allocations of memory objects. You may consider applying the T8048 trace flag if top memory consumers are per-NUMA Node partitioned, and you can see a large percentage of CXMEMTHREAD waits in the system. This is especially important in the case of servers with more than eight CPUs per-NUMA Node, where SQL Server 2008 and above have known issues of per-NUMA Node memory object scalability.

Listing 27-8.  Analyzing memory object partitioning and memory usage

select
        type
        ,pages_in_bytes
        , case
                when (creation_options & 0x20 = 0x20)
                        then 'Global PMO. Cannot be partitioned by CPU/NUMA Node. T8048 not applicable.'
                when (creation_options & 0x40 = 0x40)
                        then 'Partitioned by CPU. T8048 not applicable.'
                when (creation_options & 0x80 = 0x80)
                        then 'Partitioned by Node. Use T8048 to further partition by CPU.'
                else
                        'Unknown'
        end as [Partitioning Type]
from
        sys.dm_os_memory_objects
order by
        pages_in_bytes desc

High CPU Load

As strange as it sounds, low CPU load on a server is not necessarily a good sign. It indicates that the server is under-utilized. Even though under-utilization leaves systems with room to grow, it increases the IT infrastructure and operational costs—there are more servers to host and maintain. Obviously, high CPU load is not good either. Constant CPU pressure on SQL Server makes systems unresponsive and slow.

There are several indicators that can help you detect that a server is working under CPU pressure. These include a high percentage of SOS_SCHEDULER_YIELD waits, which occur when a worker is waiting in a runnable state. You can analyze the % Processor Time and Processor Queue Length performance counters and compare the signal and resource wait times in the sys.dm_os_wait_stats view, as shown in Listing 27-9. Signal waits indicate the waiting times for the CPU, while resource waits indicate the waiting times for resources, such as for pages from disk. Although Microsoft recommends that the signal wait type should not exceed 25 percent, I believe that 15-20 percent is a better target on busy systems.

Listing 27-9.  Comparing signal and resource waits

select
        sum(signal_wait_time_ms) as [Signal Wait Time (ms)]
        ,convert(decimal(7,4), 100.0 * sum(signal_wait_time_ms) /
                sum (wait_time_ms)) as [% Signal waits]
        ,sum(wait_time_ms - signal_wait_time_ms) as [Resource Wait Time (ms)]
        ,convert(decimal(7,4), 100.0 * sum(wait_time_ms - signal_wait_time_ms) /
                sum (wait_time_ms)) as [% Resource waits]
from
        sys.dm_os_wait_stats with (nolock)
option (recompile)

Plenty of factors can contribute to CPU load in a system, and bad T-SQL code is at the top of the list. Imperative processing, cursors, XQuery, multi-statement user-defined functions and complex calculations are especially CPU-intensive.

The process of detecting the most CPU-intensive queries is very similar to that for detecting non-optimized queries. You can use the sys.dm_exec_query_stats view, as was shown in Listing 27-5. You can sort the data by the total_worker_time column, which detects the most CPU-intensive queries with plans currently cached. Alternatively, you can use SQL Trace and Extended Events, filtering data by CPU time rather than by I/O metrics.

Constant recompilation is another source of CPU load. You can check the Batch Requests/Sec, SQL Compilations/Sec, and SQL Recompilations/Sec performance counters and calculate plan reuse with the following formula:

Plan Reuse = (Batch Requests/Sec - (SQL Compilations/Sec - SQL Recompilations/Sec)) / Batch Requests/Sec

Low plan reuse in OLTP systems indicates heavy Ad-Hoc activity and often requires code refactoring and parameterization of queries. However, non-optimized queries are still the major contributor to CPU load. With non-optimized queries, SQL Server processes a large amount of data, which burns CPU cycles regardless of other factors. In most cases, query optimization reduces the CPU load in the system.

Obviously, the same is true for bad T-SQL code. You should reduce the amount of imperative data processing, avoid multi-statement functions, and move calculations and XML processing to the application side if at all possible.

Parallelism

Parallelism is perhaps one of the most confusing aspects of troubleshooting. It exposes itself with the CXPACKET wait type, which often can be seen in the list of top waits in the system. The CXPACKET wait type, which stands for Class eXchange, occurs when parallel threads are waiting for other threads to complete their execution.

Let’s consider a simple example and assume that we have a parallel plan with two threads followed by the Exchange/Repartition Streams operator. When one parallel thread finishes its work, it waits for another thread to complete. The waiting thread does not consume any CPU resources; it just waits, generating the CXPACKET wait type.

The CXPACKET wait type merely indicates that there is parallelism in the system and, as usual, it fits into the “It Depends” category. It is beneficial when large and complex queries utilize parallelism, because it can dramatically reduce their response time. However, there is always overhead associated with parallelism management and Exchange operators. For example, if a serial plan finishes in 1 second on a single CPU, the execution time of the parallel plan that uses two CPUs would always exceed 0.5 seconds. There is always extra time required for parallelism management. Even though the response (elapsed) time of the parallel plan would be smaller, the CPU time will always be greater than in the case of the serial plan. You want to avoid such overhead when a large number of OLTP queries are waiting for the available CPU to execute. A high percent of SOS_SCHEDULER_YIELD and CXPACKET waits is a sign of such a situation.

One common misconception suggests that you completely disable parallelism in the case of a large percentage of CXPACKET waits in OLTP systems and set the server-level MAXDOP setting to 1. However, this is not the right way to deal with parallelism waits. You need to investigate the root-cause of parallelism in the OLTP system and analyze why SQL Server generates parallel execution plans. In most cases, it occurs due to complex and/or non-optimized queries. Query optimization simplifies execution plans and removes parallelism.

Moreover, any OLTP system has some legitimate complex queries that would benefit from parallelism. It is better to increase the Cost Threshold for Parallelism configuration option rather than to disable parallelism by setting the MAXDOP setting to 1. This would allow you to utilize parallelism with complex and expensive queries while keeping low-cost OLTP queries running serially.

There is no generic advice for how the Cost Threshold for Parallelism value needs to be set. By default, it is set to five, which is very low nowadays. You should analyze the activity and cost of the queries in your system to find the optimal value for this setting. Check the cost of the queries that you want to run serially and in parallel, and adjust the threshold value accordingly.

Speaking of the MAXDOP setting, as general advice, it should not exceed the number of logical CPUs per hardware NUMA node. However, in some Data Warehouse/Decision Support Systems, you can consider using a MAXDOP setting that exceeds this number. Again, you should analyze and test your workload to find the most optimal value for this setting.

Locking and Blocking

Excessive locking and blocking issues in a system presents various LCK_M_* wait types. Each lock type has its own corresponding wait type. For example, LCK_M_U indicates update (U) lock waits, which can be a sign of non-optimized data modification queries.

We have already covered how to troubleshoot locking and blocking issues in a system. You need to detect what processes participated in the blocking chain with the Blocked Process Report, Deadlock Graph events, and sys.dm_tran_locks view and find the root-cause of the blocking. In most cases, it happens due to non-optimized queries.

Worker Thread Starvation

In rare cases, SQL Server can experience worker thread starvation, a situation where there are no available workers to assign to new tasks. One scenario when this can happen is when a task acquires and holds a lock on a critical resource that is blocking a large number of other tasks/workers, which stays in a suspended state. When the number of workers in the system reaches the Maximum Worker Thread configuration setting, SQL Server is not able to create new workers, and new tasks remain unassigned, generating THREADPOOL waits.

Blocking is not the only reason why this situation could occur. It is also possible to reach the limit of worker threads in systems with heavy concurrent workload from a large number of users.

As usual, you need to find the root-cause of the problem. While it is possible to increase the Maximum Worker Thread number in the SQL Server configuration, this may or may not help. For example, in the blocking scenario described above, there is a good chance that newly created workers will be blocked in the same way as existing ones. It is better to investigate the root-cause of the blocking problem and address it instead.

You can check a blocking condition and locate the blocking session by analyzing the results of the sys.dm_os_waiting_tasks or sys.dm_exec_requests views. Listing 27-10 demonstrates the first approach. Keep in mind that the sys.dm_exec_requests view does not show tasks that do not have workers assigned waiting with the THREADPOOL wait type.

Listing 27-10.  Using sys.dm_os_waiting_tasks

select
        wt.session_id
        ,wt.wait_type
        ,wt.wait_duration_ms
        ,wt.blocking_session_id
        ,wt.resource_description
from
        sys.dm_os_waiting_tasks wt with (nolock)
order by
        wt.wait_duration_ms desc
option (recompile)

As you can see in Figure 27-9, the ID of the blocking session is 51.

For the next step, you can use the sys.dm_exec_sessions and sys.dm_exec_connections views to get information about the blocking session, as shown in Listing 27-11. You can troubleshoot why the lock is held and/or terminate the session with the KILL command if needed.

Listing 27-11.  Getting information about a blocking session

select
        ec.session_id
        ,s.login_time
        ,s.host_name
        ,s.program_name
        ,s.login_name
        ,s.original_login_name
        ,ec.connect_time
        ,qt.text as [SQL]
from
        sys.dm_exec_connections ec with (nolock)
                join sys.dm_exec_sessions s with (nolock) on
                        ec.session_id = s.session_id
                cross apply
                        sys.dm_exec_sql_text(ec.most_recent_sql_handle) qt
where
        ec.session_id = 51 -- session id of the blocking session
option (recompile)

It is worth mentioning that even though increasing the Maximum Worker Thread setting does not necessarily solve the problem, it is always worth upgrading to a 64-bit version of Windows and SQL Server. A 64-bit version of SQL Server has more worker threads available by default, and it can utilize more memory for query grants and other components. It reduces memory grant waits, makes SQL Server more efficient and, therefore, allows tasks to complete execution and frees up workers faster.

Workers, however, consume memory, which reduces the amount of memory available to other SQL Server components. This is not usually an issue unless SQL Server is running on a server with very little physical memory available. You should consider adding more memory to the server if this is the case. After all, it is a cheap solution nowadays.

ASYNC_NETWORK_IO Waits

The ASYNC_NETWORK_IO wait type occurs when SQL Server generates data faster than the client application consumes it. While this could be a sign of non-sufficient network throughput, in a large number of cases ASYNC_NETWORK_IO waits are accumulated due to incorrect or inefficient client code.

One such example is reading an excessive amount of data from the server. The client application reads unnecessary data or, perhaps, performs client-side filtering, which adds extra load and exceeds network throughput.

Another pattern includes reading and simultaneous processing of the data, as shown in Listing 27-12. The client application consumes and processes rows one-by-one, keeping SqlDataReader open. Therefore, the worker waits for the client to consume all rows generating the ASYNC_NETWORK_IO wait type.

Listing 27-12.  Reading and processing of the data: Incorrect implementation

using (SqlConnection connection = new SqlConnection(connectionString))
{
        SqlCommand command = new SqlCommand(cmdText, connection);
        connection.Open();
 
        using (SqlDataReader reader = command.ExecuteReader())
        {
                while (reader.Read())
                {
                        ProcessRow((IDataRecord)reader);
                }
        }
}

The correct way of handling such a situation is by reading all rows first as fast as possible and processing them after all rows have been read. Listing 27-13 illustrates this approach.

Listing 27-13.  Reading and processing of the data: Correct implementation

List<Orders> orderRows = new List<Orders>();
 
using (SqlConnection connection = new SqlConnection(connectionString))
{
        SqlCommand command = new SqlCommand(cmdText, connection);
        connection.Open();
 
        using (SqlDataReader reader = command.ExecuteReader())
        {
                while (reader.Read())
                {
                        orderRows.Add(ReadOrderRow((IDataRecord)reader));
                }
        }
}
 
ProcessAllOrderRows(orderRows);

You should check network performance and analyze the client code if you see a large percentage of ASYNC_NETWORK_IO waits in the system.

Allocation Map Contention and Tempdb load

Allocation map pagescontention exposes itself with PAGELATCH_* wait types. These wait types indicate contention on in-memory pages as opposed to PAGEIOLATCH wait types, which are I/O subsystem related.

Allocation map pages contention rarely happens in user databases unless the data is highly volatile. One example is a system that collects data from external sources with very high inserts and, therefore, pages and extents allocations rates. However, as we have already discussed, allocation map pages contention could become a major performance bottleneck in the case of tempdb.

When you see a large percentage of PAGELATCH waits, you should locate the resources where contention occurs. You can monitor the wait_resource column in the sys.dm_exec_requests or resource_description columns in the sys.dm_os_waiting_tasks view for corresponding wait types. The information in those columns includes the database id, file id, and page number. You can reduce allocation map contention in the corresponding database by moving objects that lead to the contention to another filegroup with a larger number of data files.

In the case of allocation map contention in tempdb (database id is 2), you can prevent mixed extents allocation with T1118 trace flag and create temporary objects in a way that allows their caching. We have already discussed this in detail in Chapter 12, “Temporary Tables.”

Other tempdb related performance counters, which can help you monitor its load, include Version Store Generation Rate (KB/S), Version Store Size (KB) in Transactions Object and Temp Table Creation Rate, and Active Temp Tables in a General Statistics Object. Those counters are most useful with the baseline, where they can show the trends of tempdb load.

Wrapping Up

Table 27-1 shows symptoms of the most common problems you will encounter in systems, and it illustrates the steps you can take to address these problems.

This list is by no means complete, however it should serve as a good starting point.