Performance Tuning in SQL Server: Top 4 Ways to Find Slow Queries - Part1

SQL performance tuning is a never ending battle. I’m not a DBA, but I am a developer who has pretended to be one for 15 years.  I have worked with SQL Server databases with terrabytes of RAM all the way down to Stackify’s massive fleet of little SQL Azure databases. I have seen a little bit of everything over the years.

In this article, I’m going to provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.

4 Ways to Find Slow SQL Queries  

1. Find Slow Queries With SQL DMVs

One of the great features of SQL Server is all of the dynamic management views (DMVs) that are built into it. There are dozens of them and they can provide a wealth of information about a wide range of topics.

There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.

For example, this query below can be used to find the queries that use the most reads, writes, worker time (CPU), etc.

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

SELECT DISTINCT TOP 10 t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS nMaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time ASnLogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY

s.max_elapsed_time DESC

GO

The result of the query will look something like this below. The image below is from a marketing app I made. You can see that one particular query (the top one) takes up all the resources.

By looking at this, I can copy that SQL query and see if there is some way to improve it, add an index, etc.