Jul 29, 2014

How to analyse SQL Server performance

Being able to quickly analyze the workload and possible bottlenecks on a busy, mission-critical SQL Server is an extremely valuable skill, especially in a crisis. As I have blogged about before, both SQL Server 2005 and 2008 expose a wealth of very useful metrics that were difficult if not impossible to be aware of with previous versions of SQL Server.
In order to get the best results from these queries, you should run DBCC FREEPROCCACHE on your server a few minutes before you run them. Otherwise, the Age in Cache values will not be the same, which will skew the results of these queries. Of course, you should be aware for the effect of running DBCC FREEPROCCACHE on a production server before you do it.
The first query below tells you which stored procedures are being called the most often, which is good to know for baseline and troubleshooting purposes. Don’t be fooled into assuming that the SP that is called the most often is the most costly though. It may well be that you have other stored procedures that are not called as much, which are much more costly (in different ways) than the most frequently called SPs.
Query 2 shows the top 20 stored procedures sorted by total worker time (which equates to CPU pressure). This will tell you the most expensive stored procedures from a CPU perspective.
Query 3 shows the top 20 stored procedures sorted by total logical reads(which equates to memory pressure). This will tell you the most expensive stored procedures from a memory perspective, and indirectly from a read I/O perspective.
Query 4 shows the top 20 stored procedures sorted by total physical reads(which equates to read I/O pressure). This will tell you the most expensive stored procedures from a read I/O perspective.
Query 5 shows the top 20 stored procedures sorted by total logical writes(which equates to write I/O pressure). This will tell you the most expensive stored procedures from a write I/O perspective.
In an upcoming post, I will explain how to interpret the results of these queries, and more importantly, some steps to improve the queries that show up at the top of your lists.

Query 1
    -- Get Top 100 executed SP's ordered by execution count
    SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.execution_count DESC

Query 2
    -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)
    SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.execution_count AS 'Execution Count', 
    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
    qs.max_logical_reads, qs.max_logical_writes, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_worker_time DESC
    
Query 3
    -- Get Top 20 executed SP's ordered by logical reads (memory pressure)
    SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads, 
    qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.total_logical_writes,
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY total_logical_reads DESC

Query 4

    -- Get Top 20 executed SP's ordered by physical reads (read I/O pressure)
    SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',
    qs.execution_count AS 'Execution Count',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,  
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_physical_reads DESC

Query 5

    -- Get Top 20 executed SP's ordered by logical writes/minute
    SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',  
    qs.execution_count AS 'Execution Count', 
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_logical_writes DESC
 
Query 1 looks at cumulative waits for the entire database instance since the last time SQL Server was started (or the last time the wait stats were cleared with the command shown below).
-- Clear Wait Stats for entire instance DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); Query 1 tells you what resources that SQL Server is spending the most time waiting for. This can help you focus your subsequent diagnostic and troubleshooting efforts, so it is very valuable. I do want to caution people not to obsess over the results though. What I mean by that is that I frequently see people get very worried about what ever shows up as the top wait type, even if their SQL Server instance is running just fine, with no performance or scalability problems. 
SQL Server is always waiting on something, but many wait types are pretty benign (which is why I try to filter many of them out). If your instance is running well, with no other indicators of trouble, relax a little! This query is much more valuable when you are experiencing performance problems, or you see other symptoms of stress.
Query 2 lets you look at memory clerk usage, which will help you find issues with procedure cache bloating with ad-hoc query plans. Back in SQL Server 2005 days, the SQL Server Agent was a major culprit here. I also see many ad-hoc plans coming from SharePoint 2007 and 2010. The worst offender may be your own developers, if they like to write ad-hoc SQL by concatenating a T-SQL query in the client or middle-tier.
You can use the “Optimize for ad-hoc workloads” instance setting (which was new in SQL Server 2008) to help minimize this problem. You may need to use the old band-aid approach of periodically running the command shown below.
-- Clear ad-hoc SQL plans for entire SQL instance DBCC FREESYSTEMCACHE('SQL Plans'); Query 3 will find the most expensive cached stored procedures in the current database, from a CPU perspective. You would definitely want to run this if you saw any signs of CPU pressure. You can easily change the sort order and column selection of this query to focus on other pain areas, such as logical reads, logical writes, etc.
Queries 4 and 5 will help you tune your relational index usage and selection. I usually call those two queries, “the dynamic duo”, since they are so useful for getting the right number of useful indexes for your workload. This is critical for getting the best performance and scalability for your databases.
I talked about this subject in much more detail recently in a post called “Index Tuning for Mere Mortals”.
These five queries should give you plenty of opportunities to “save the day”, and impress your boss with your awesome SQL Server tuning skills, so have fun!

related to : http://sqlserverperformance.wordpress.com/2011/02/04/five-dmv-queries-that-will-make-you-a-superhero-in-2011/
 

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib