The following query is a great way to get a look into how your stored procedure driven application is performing. I wouldn’t recommend running this during your peak hours. What I have done is scheduled it to run in the off hours and store into a table in my dummy DB. I then query that table as it is less intensive then querying the DMVs. What this query shows me is area’s a I need to tune within my application. If a query is executed many times and has a high number of logical reads perhaps my index needs to be tuned. Same goes if I look into area’s where physical data processing is happening… If your data isn’t being found in the cache you may have a bottleneck or perhaps you are just pulling to much data. It’s also great to see which queries are executed most frequently as there is always room for improvement and it’s important to understand how your data is being accessed.
SELECT
SUBSTRING(text,PATINDEX(‘%create %’,text),50),
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END
– qs.statement_start_offset)/2) + 1) as statement_text,
db_name(dbid) DB,
creation_time,
last_execution_time,
execution_count,
(total_elapsed_time/execution_count)/1000000 as time
,total_physical_reads
,last_physical_reads
,min_physical_reads
,max_physical_reads
,total_logical_writes
,last_logical_writes
,min_logical_writes
,max_logical_writes
,total_logical_reads
,last_logical_reads
,min_logical_reads
,max_logical_reads
,getdate()
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
where db_name(dbid) =’YourDB’
