Wednesday, October 1, 2014

SQL Server Query to find out high memory and cpu usages

Taken from: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9802159d-518c-4e7d-bef9-ae6debdd57f7/high-memory-usage-by-sql-server-and-very-slow

-- Find queries that take the most CPU overall  
SELECT TOP 50  
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,TextData           = qt.text  
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads  
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads  
    ,Executions         = qs.execution_count  
    ,TotalCPUTime       = qs.total_worker_time  
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count  
    ,DiskWaitAndCPUTime = qs.total_elapsed_time  
    ,MemoryWrites       = qs.max_logical_writes  
    ,DateCached         = qs.creation_time  
    ,DatabaseName       = DB_Name(qt.dbid)  
    ,LastExecutionTime  = qs.last_execution_time  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
 ORDER BY qs.total_worker_time DESC 
   
-- Find queries that have the highest average CPU usage  
SELECT TOP 50  
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,TextData           = qt.text  
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads  
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads  
    ,Executions         = qs.execution_count  
    ,TotalCPUTime       = qs.total_worker_time  
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count  
    ,DiskWaitAndCPUTime = qs.total_elapsed_time  
    ,MemoryWrites       = qs.max_logical_writes  
    ,DateCached         = qs.creation_time  
    ,DatabaseName       = DB_Name(qt.dbid)  
    ,LastExecutionTime  = qs.last_execution_time  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
 ORDER BY qs.total_worker_time/qs.execution_count DESC 
/**********************************************************  
*   top procedures memory consumption per execution  
*   (this will show mostly reports & jobs)  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
   
) T  
ORDER BY IO_Per_Execution DESC 
   
/**********************************************************  
*   top procedures memory consumption total  
*   (this will show more operational procedures)  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY Total_IO_Reads DESC 
   
/**********************************************************  
*   top adhoc queries memory consumption total  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,QueryText          = qt.text  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
    GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY Total_IO_Reads DESC 
   
/**********************************************************  
*   top adhoc queries memory consumption per execution  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,QueryText          = qt.text  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
    GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY IO_Per_Execution DESC