Tag Archives: SQL Optimization

Script to find out most CPU bound Queries

Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage

SELECT highest_cpu_queries.plan_handle, 
(highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, 
highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,
q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time,
qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads,
qs.min_physical_reads FROM sys.dm_exec_query_stats qs 
ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 

Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.

SELECT SPID, CPU, s2.text, open_tran, status, program_name,
net_library, loginame FROM sys.sysprocesses 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
where cpu > 5000 and status = 'runnable' 

Performance Tuning – Wait Statistics

Many times we got a call from the business team regarding the performance issue on the database server. As a first step you can check for the blocking, if the blocking is not there. We have to check for the waits, Query is internally waiting for the resources to complete its process.

By identifying the correct wait type will give you the directions to troubleshooting issue further. You can execute below query to get the 10 wait statistics.

        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
                                    AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
                                    AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
ORDER BY wait_time_ms DESC

You have to checkout for the below kind of wait statistics and troubleshoot as per the stats.

CXPACKET :Most of the time it indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, it may be the symptom of another problem, associated with one of the other high value wait types in the instance.

SOS_SCHEDULER_YIELD :The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure.

THREADPOOL :A task had to wait to have a worker bound to it, in order to execute.

LCK_* :These wait types indicate that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using, for example, the information in the sys.dm_db_index_operational_stats.

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG :These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server.

PAGELATCH_* :Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.

LATCH_* :These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO :This wait is often incorrectly attributed to a network bottleneck.

Performance Tuning Series

Lock Pages in Memory

You can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service.

Model Database Whenever we create a new database, it will use model as template. Configure model DB for the Auto Shrink OFF, Auto Update/Create Statistics on

Maximum Worker Threads: Based on the load increase the maximum work thread.

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond 4 GB memory limit. The AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32-bit and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which might have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be granted for the account that runs SQL Server.



Please note:

  • Turn auto-shrink off.
  • Make sure auto-update of statistics is turned on.
  • If a database is read only, set it to read only.
  • Use triggers very judiciously. They mostly operate in the background making them difficult to monitor and troubleshoot.
  • Be very careful of auto growth settings on the database. 10% auto growth will be fine when the database is 500mb. It makes a huge difference when the system is 50gb. For larger databases, change the setting to grow by a fixed amount rather than a percentage of total database size.
  • Files and FileGroups
  • Other factors that can affect the performance of your system include the way the files and file groups are laid out. You should be creating multiple files for your databases to optimize performance. A baseline for this would be to create one file for the logs, another for the data (defined by the clustered index), and another for non-clustered indexes. Additional files may be necessary to separate out BLOB data or XML data or unusually active tables, each onto its own file, and where possible, onto its own disk. This has been found to be true even on SAN systems because distributing the load takes further advantage of the architecture of the SAN.
  • Data Types
  • Define the data types that you need, not what you think you might need someday. A phone number is a string, not a number. Define the length of field that you need and enforce that length.


Common cause of the performance issues

Below are the most common reasons for the performance issues.

  • SQL Server Configuration Issues

  • Database/table/schema Design and Configuration Issues

  • CPU/IO/Memory Bottleneck

  • Blocking

  • Network Bottleneck

  • Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)

  • Out-of-date/missing statistics

  • T-SQL Code

  • Application Code

Script to find out Heap Table/Table without clustered Index

Heap Table: Table without a clustered index is called Heap Table. You can find out the Heap Table by querying the sys.indexes or sys.partitions against the index_id column.

select OBJECT_NAME(object_id),* 
from sys.indexes where index_id = 0

Select OBJECT_NAME(object_id),*  
from sys.partitions where index_id = 0