DMV's are one of the most usefull features in SQL Server 2005.
There are 2 that I like more than others because they can give you information about
executed SQL statements without the use of SQL Profiler tool.
Of course they aren't a substitute for SQL profiler but rather an another tool you can use
in situations when you can't run SQL Profiler.
Those 2 DMV's are:
- sys.dm_exec_query_stats
- sys.dm_exec_sql_text
They provide much info about executed statements but i used them in this procedure for searching through
executed sql statemnts.
Since dm_exec_query_stats is based on cached query plans the row in it lasts until the plan is removed from cache.
So for better and more usefull data i put the results of each run into a separate log table.
this is the stored procedure:
create table StatementExecLog
(
StatementText nvarchar(max),
LastExecuted datetime,
ElapsedTimeInSeconds int,
ExecCountFromLastCompile int
)
go
create proc GetExecutedQueries
@Filter varchar(8000),
@DateFrom datetime = '19000101',
@DateTo datetime = '99990101'
as
select * from
(
SELECT 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 StatementText,
last_execution_time as LastExecuted,
last_elapsed_time/1000000.0 as ElapsedTimeInSeconds,
execution_count as ExecCountFromLastCompile
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where last_execution_time between @DateFrom and @DateTo
) t
where StatementText like '%' + @Filter + '%'
order by LastExecuted desc
go
select * from person.address
go
-- for simple searches
exec GetExecutedQueries '.address'
-- for long term logging
insert into StatementExecLog
exec GetExecutedQueries '.address'