Queries to check CPU and Memory consumption for MS SQL database routines.

Hi All,

Yesterday i was getting frequent time outs on accessing my website and when i checked the server from task manager it was hitting the maximum values for both RAM and CPU usage and most of that was begin taken by SQL processes, the easiest solution for this problem is to increase the Memory and Cores for processor but frankly speaking i am not a Hardware guy and had to do every thing with in my software skills premisses (at least till the point i can.).

So i found some interesting articles and that tells us which queries are taking time which are caught in deadlocks and which requires optimization the simplest way to check that is Activity monitor provided in Microsoft SQL server Management Studio, to do connect to server with admin rights and then RIGHT CLICK on databases to select activity monitor which will give a good graphical representation of queries taking time.

But i know most of us needs some more information than what is being provided by Activity monitor so there are some queries which allows us to check the names and execution time of procedures and queries.

The first one mentioned below returns the Query which is taking maximum amount of memory so optimization can be done in the manner to take care of query taking the maximum time.

 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  

the second variant of query returns the procedures that are taking maximum memory

 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  

Both the above queries can be modified to get the CPU cycles in order to optimize the CPU usage, finally i am going to share a query which will be giving the CPU and Memory consumption per Execution.

 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  

Hope this will help you the same way in optimization as they helped me.

Happy coding..