Saturday, August 11, 2012

Sql server how to find currently running query's

We will face an issue like a sql server memory will keep on increase, in that situation we can easily find the issue using following query. Following query will give how many time query executed and total elapsed time .

SELECT qs.execution_count, DatabaseName = DB_NAME(qp.dbid), ObjectName = OBJECT_NAME(qp.objectid,qp.dbid), StatementDefinition = 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 ), query_plan, st.text, total_elapsed_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp WHERE st.encrypted = 0 ORDER BY qs.execution_count DESC