Saturday, December 9, 2017

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
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
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
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
s.max_elapsed_time DESC

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.

No comments:

Popular Posts