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
