Really , it is a daily question for any DBA + DB Analyst ….
How I could achieve 0 sec ( few msec) for most of production queries to get an outstanding performance level for end users….
It needs exceptionally and voluntarily effort to Capture + Analyze + Enhance any Expensive queries Daily and Keep doing like that fast + easily.
Therefore, we could start now a new series of articles to cover sufficiently this regard
First : How to capture any expensive queries…
They could be audited automatically through variety of options:
1- Using SQL Server 2008 Activity Monitor where you could find cached expensive queries.
But not sufficient for a comprehensive auditing since a recycling of expensive queries is there and you mightn’t be able to track old ones.
2- Use SQL Profiler >>> Performance Event >> Performance Statistics >> select all columns
But this is also not sufficient from 2 perspectives:
· Profilers almost post more overload on production DB servers + Master DB + yield to a performance degradation which might range from 50 % -100% or perhaps might be more.
· Not easily tracked particularly for huge OLTP transactions
3- DMVs which is the best practice and to utilize it easily + operationally to audit long history of expensive queries , you could follow the below scripts by the same order :
CREATE TABLE [dbo].[Exp_table](
[EXP_Query] [nvarchar](max) NULL,
[Time] [datetime] NULL,
[Elapsed_Time] [int] NULL
) ON [PRIMARY]
DELETE FROM EXP_TABLE WHERE DATEDIFF (D,TIME, GETDATE())>n
--n = no of days needed to keep auditng results
insert into msdb.dbo.Exp_Table SELECT
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text,qs.last_execution_time, qs.last_elapsed_time/1000000
FROM sys.dm_exec_query_stats AS qs
WHERE last_elapsed_time >3000000
and qs.last_execution_time not in (select time from [msdb].[dbo].[Exp_Table])
order by last_elapsed_time desc
And then you could schedule the last 2 scripts in one scheduled job to run by any polling interval like 5 minutes.
Advantage of DMV solution:
· Precise auditing of all possible expensive queries every millisecond.
· Long retention period of auditing which might be for months or years.
· The least consume of resources since no CPU overload or Storage capacity needed at all for it.
· More accessible by first class objects ( SQL commands ) to be filtered easily for any time interval needed