Showing posts with label Slow Running queries. Show all posts
Showing posts with label Slow Running queries. Show all posts

Monday, 29 May 2017

Top 10 expensive queries in SQL

Top 10 expensive queries in SQL


Often, DBA gets this message from the application developers and clients that their system or applications are running deadly slow. There could be several reasons for a Query or System is running slow. A few common reasons can include a temporary blocking, a network slowness or sometimes even an issue from the application end. To verify if it is a temporary blocking we can simply use this system stored-procedure- sp_who2. Upon execution of this system stored procedure we are presented with all the granular level details of the system and user processes which are running behind the scene in SQL Server. I have pasted a sample output of the execution below to get a fair idea of how the output will look like-
SPID  Status Login BlkBy DBName Command CPUTime DiskIO
1 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    0 0
2 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    0 0
3 BACKGROUND                     sa   . NULL UNKNOWN TOKEN    328 0
4 BACKGROUND                     sa   . NULL LOG WRITER       125 0
5 BACKGROUND                     sa   . NULL RECOVERY WRITER  78 0
6 BACKGROUND                     sa   . NULL LAZY WRITER      9765 0
7 BACKGROUND                     sa   . NULL LOCK MONITOR     15 0
8 BACKGROUND                     sa   . master SIGNAL HANDLER   0 0
9 BACKGROUND                     sa   . master BRKR TASK        0 0
10 BACKGROUND                     sa   . NULL XE DISPATCHER    31 0
11 BACKGROUND                     sa   . NULL RESOURCE MONITOR 503875 0
12 BACKGROUND                     sa   . NULL XE TIMER         1562 0
13 sleeping                       sa   . master TASK MANAGER     0 0
14 BACKGROUND                     sa   . master TRACE QUEUE TASK 31 0

The process ids which have their SPID lesser than 50 are system processes and others are user processes. If there is a block then you can see the same in blk by column of the above output. Blocking is intermittent and generally resolves in sometime once the resource is set free by the process but if it does not then we have to identify the cause and try to resolve it.


While there could be a blocking issue sometimes but most of the times the culprit will be poorly created queries. Such queries can eat up all the resources and make the system severely slow.In order to tune up those queries we first need to identify them.

Below is the Query that can help you find out the TOP 10 expensive queries in SQL Server by CPU-





SELECT TOP 10 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),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
 --ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time

Once you extract the Top 10 expensive queries in SQL Server you can then work on them and tune them which in turn will boost the overall performance of your SQL Server System.