Queries for SQL Server Performance Tuning

SQL Server Performance Tuning Queries .sql

/* Get top 100 executed Stored Procedures 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
-----------------------------------------------------------------------------------------------------


/* Get top 20 executed Stored Procedures 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
-----------------------------------------------------------------------------------------------------


/* Get top 20 executed Stored Procedures 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
-----------------------------------------------------------------------------------------------------


/* Get top 20 executed Stored Procedures 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
-----------------------------------------------------------------------------------------------------


/* Get top 20 executed Stored Procedures 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
-----------------------------------------------------------------------------------------------------


/* Select top 10 most frequent Stored Procedures in SQL Server */
-----------------------------------------------------------------------------------------------------
    SELECT TOP 10 
    qt.TEXT AS 'SP Name',
    SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) 
        THEN LEN(qt.text) ELSE (qs.statement_end_offset - qs.statement_start_offset)/2 END) 
        AS actual_query,
    qs.execution_count AS 'Execution Count',
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_physical_reads AS 'PhysicalReads',
    qs.creation_time 'CreationTime',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) 
        AS 'Calls/Second'
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = (SELECT dbid
                    FROM sys.sysdatabases
                   WHERE name = '<db-name>')
    ORDER BY qs.total_physical_reads DESC
-----------------------------------------------------------------------------------------------------


/* Get all procedures without nolock hint */
-----------------------------------------------------------------------------------------------------
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION  NOT LIKE '%nolock%' and ROUTINE_TYPE='PROCEDURE' 
-----------------------------------------------------------------------------------------------------


/* Select top N most executing procedures */
-----------------------------------------------------------------------------------------------------
    SELECT top 20
        DatabaseName       = DB_NAME(st.dbid), 
        SchemaName         = OBJECT_SCHEMA_NAME(st.objectid,dbid),
        StoredProcedure    = OBJECT_NAME(st.objectid,dbid),
        ExecutionCount     = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    WHERE DB_NAME(st.dbid) IS NOT NULL 
    AND cp.objtype = 'proc'
    GROUP BY
        cp.plan_handle,
        DB_NAME(st.dbid),
        OBJECT_SCHEMA_NAME(objectid,st.dbid),
        OBJECT_NAME(objectid,st.dbid) 
    ORDER BY MAX(cp.usecounts) DESC
-----------------------------------------------------------------------------------------------------