SQL Performance Queries (DMV Queries)

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 1st 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

Monitor current SQL Server processes

Monitor current SQL Server processes

How many times does somebody come to you and say 'Is something running on the server right now?'   Or, 'Why is it so slow?'  Time and time again, you're in there running sp_who2, trying to figure out where the problem is -- what is sucking your server resources? 

Activity Monitor is pretty good.  It's like SQL Server's version of Perf Mon.  But, like most GUI-based tools, it's a memory pig all by itself!  Here is a quick piece that I use to go behind the Activity Monitor GUI, and still get the goods.  I've wrapped it into a view, which I target in a job that I use for monitoring, and collecting stats from the server.  You could just use the SELECT statement, or even put it into a procedure.

Let me know what you think.

   USE master
   GO
   CREATE VIEW dbo.vwCurrentSQLProcess
   AS
   /*
   View utilized to help automate the monitoring of SQL Server processes.
   It is available at all times on demand, but also used by the scheduled job which
   collects the data and dumps into dbo.CurrentSQLProcess. Useful to automate 
   monitoring, alerts, and server administration.

   SELECT * FROM dbo.vwCurrentSQLProcess

   Auth:  ME
   Date: 2/27/2014
   */
   SELECT
         TOP 100 PERCENT
         s.session_id [SessionID],
         s.login_name [Login],
         COALESCE(s.host_name, c.client_net_address) [Host],
         s.program_name [Application],
         r.command [Process],
         t.task_state [State],
         r.start_time [StartTime],
         r.[status] [Status],
         r.wait_type [WaitType],
         TSQL.[text] [tSQL],
         (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
         (tsu.internal_objects_alloc_page_count -  
          tsu.internal_objects_dealloc_page_count ) [#PagesAllocated]
   FROM        
        sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c
           ON s.session_id = c.session_id LEFT JOIN sys.dm_db_task_space_usage tsu
             ON s.session_id = tsu.session_id LEFT JOIN sys.dm_os_tasks t
               ON tsu.session_id = t.session_id
               AND tsu.request_id = t.request_id LEFT JOIN sys.dm_exec_requests r
                 ON tsu.session_id = r.session_id
                 AND tsu.request_id = r.request_id
            OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
     WHERE
         (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
         (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) > 0
     ORDER BY
        [#PagesAllocated] DESC;


These are the results... from my pretty idle, inactive laptop:

SessionID Login Host App. Process State StartTime Status WaitType tSQL #PagesAllocated 5 sa NULL NULL SIGNAL HANDLER SUSPENDED 2014-02-26 08:10:39.807 background KSOURCE_WAKEUP NULL 4

Active SQL Server connections

Active SQL Server connections

SP_WHO2

These are just a few helpful DMV requests, returning details regarding the active SQL Server connections.  Keep in mind, user sessions are >= session_id 51.


Report all the connections to SQL Server, returning one row for each:

  SELECT

    connection_id,

    session_id,

    client_net_address,

    auth_scheme

 FROM

    sys.dm_exec_connections


Report each session connected to SQL Server, similar to sp_who2:

 SELECT
    session_id,login_name,
    last_request_end_time,cpu_time
 FROM
    sys.dm_exec_sessions
 WHERE
    session_id >= 51
 ORDER BY
    last_request_end_time DESC


Report details for what each connection is actually doing:
  SELECT
    session_id,
    status,
    command,
    sql_handle,
    database_id
 FROM
    sys.dm_exec_requests
 WHERE
    session_id >= 51

SQL Server: Performance Tuning (Understanding Set Statistics Time output)

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

Let us start with a example.

USE AdventureWorks2008
GO
            DBCC dropcleanbuffers
            DBCC freeproccache

GO
SET STATISTICS TIME ON
GO
SELECT * 
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID 
    WHERE ProductID BETWEEN 700 
        AND 800
GO
SELECT * 
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID 
    WHERE ProductID BETWEEN 700 
        AND 800




There aretwo select statement in the example .The first one is executed after clearing the buffer. Let us look into the output.

SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.


SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.