I've been looking at analysing sql server query usage and have used SQL profiler to ascertain long running queries and generally log interaction with the DB.
Here is an interesting URL on the subject
http://www.developer.com/db/article.php/10920_3482216_1
in addition I was looking for a way to do this using a query this only works on SQL Server 2005, i found this query (i forget where) but i modded it so that the actual debug query is excluded from the results.
/*Debug query */
SELECT TOP 100
qt.text as QUERY,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
/* Filter this query out */
and qt.text not like '/*Debug query */%'
/* Filter out any system level calls made by the GUI */
and qt.text not like '%FROM sys.%'
/* Reduce analysis window to today */
and year(last_execution_time) = year(getdate()) and month(last_execution_time) = month(getdate()) and day(last_execution_time)=day(getdate())
ORDER BY qs.total_logical_reads DESC
SELECT TOP 100
qt.text as QUERY,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
/* Filter this query out */
and qt.text not like '/*Debug query */%'
/* Filter out any system level calls made by the GUI */
and qt.text not like '%FROM sys.%'
/* Reduce analysis window to today */
and year(last_execution_time) = year(getdate()) and month(last_execution_time) = month(getdate()) and day(last_execution_time)=day(getdate())
ORDER BY qs.total_logical_reads DESC
Copyright © 2011 Pixl8 Interactive, 3 Tun Yard, Peardon Street, London, SW8 3HT