Friday, July 08, 2011

Weekly Health check for SQL Server 2005 using DMV

Every Week, you can set up a benchmark by running following DMV, it can help to provide high level view to show whether your DB is healthy or not currently without any monitoring tools.

While, Some limitations you may have to pay attention to when you are using following DMV Queries:
(Thanks for a good reference provided by Vance: http://www.mssqltips.com/tip.asp?tip=1843)

1. Limitation with DMV queries: Keep this in mind when you are using the DMVs for query usage and performance stats. If you are using inline T-SQL and sp_executesql you may not be capturing all of the data that you need.
—Suggestion : think about using stored procedures for all data related operations instead of using inline T-SQL or sp_executesql in your application code.
2. Limitation with dbid column: there is a problem that it is limiting the result data to queries with a database id. The reason for this is that the dbid column is NULL for ad hoc and prepared SQL statements, So you can comment out the where condition which having dbid in (...);
—Suggestion : you may just comment out the dbid constrain or using “dbid = null” instead of assign a dbid in “where clause”


-- DMV FOR CHECKING CPU USAGE:
 
 SELECT TOP 50   
      DB_Name(dbid) AS [DB_Name],  
      total_worker_time/execution_count AS [Avg_CPU_Time],  
      total_elapsed_time/execution_count AS [Avg_Duration],  
      total_elapsed_time AS [Total_Duration],  
      total_worker_time AS [Total_CPU_Time],  
      execution_count,  
   SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
     ((CASE qs.statement_end_offset  
      WHEN -1 THEN DATALENGTH(st.text)  
      ELSE qs.statement_end_offset  
      END - qs.statement_start_offset)/2) + 1) AS statement_text  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
 WHERE dbid in (  
           SELECT DB_ID('yourtablename') AS [Database ID]  
      )  
 ORDER BY Avg_CPU_Time DESC;  

-- DMV FOR CHECKING I/O USAGE
 SELECT TOP 50  
      DB_Name(dbid) AS [DB_Name],  
      Execution_Count,  
      (total_logical_reads/Cast(execution_count as Decimal(38,16))) as avg_logical_reads,  
      (total_logical_writes/Cast(execution_count as Decimal(38,16))) as avg_logical_writes,  
      (total_physical_reads/Cast(execution_count as Decimal(38,16))) as avg_physical_reads,  
      max_logical_reads,  
      max_logical_writes,  
      max_physical_reads,  
   SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
     ((CASE qs.statement_end_offset  
      WHEN -1 THEN DATALENGTH(st.text)  
      ELSE qs.statement_end_offset  
      END - qs.statement_start_offset)/2) + 1) AS statement_text  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
 WHERE dbid in (  
           SELECT DB_ID('yourtablename') AS [Database ID]  
      )  
 ORDER BY avg_logical_reads DESC;  

-- DMV FOR CHECKING INDEX USAGE
 SELECT     top 50   
           idx.name as Index_name  
           ,obj.name   
           ,dmv.object_id  
           ,sampledatetime=Getdate()  
           ,dmv.index_id  
           ,user_seeks  
           ,user_scans  
           ,user_lookups   
 FROM sys.dm_db_index_usage_stats dmv  
 INNER JOIN sys.indexes idx on dmv.object_id = idx.object_id and dmv.index_id = idx.index_id  
 Cross Apply sys.objects obj  
 WHERE dmv.object_id = obj.object_id and database_id in (  
 SELECT DB_ID('yourtablename') AS [Database ID]  
 )  
 ORDER BY user_scans desc  

-- DMV FOR CHECKING OBJECT BLOCKING/WAITING
 SELECT TOP 50  
      DB_NAME(qt.dbid),  
      [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,  
      [Total Time Blocked] = total_elapsed_time - total_worker_time,  
      [Execution count] = qs.execution_count,  
      SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,   
     ((CASE qs.statement_end_offset  
      WHEN -1 THEN DATALENGTH(qt.text)  
      ELSE qs.statement_end_offset  
      END - qs.statement_start_offset)/2) + 1) AS statement_text,
      [Parent Query] = qt.text
 FROM sys.dm_exec_query_stats qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt  
 WHERE DB_NAME(qt.dbid) = 'yourtablename'  
 ORDER BY [Average Time Blocked] DESC;  

-- DMV FOR CHECKING TEMPDB USAGE
 SELECT getdate(),   
      SUM(user_object_reserved_page_count) * 8 as user_objects_kb,  
      SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,  
      SUM(version_store_reserved_page_count) * 8 as version_store_kb,  
      SUM(unallocated_extent_page_count) * 8 as freespace_kb  
 FROM sys.dm_db_file_Space_Usage  
 where database_id = 2