November 29, 2011 at 1:49 am
how to find long running queries in sql server 2008...?
November 29, 2011 at 2:10 am
You can find long running queries by executing below SQL code. You can find it on Pinal Dave's blog here:
http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
&
http://blog.sqlauthority.com/2009/01/23/sql-server-2008-2005-find-longest-running-query-tsql-part-2/
I strongly suggest that you read both the articles before running the script & run this script on some test server to know that it meets your requirements.
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC,
GO
November 29, 2011 at 3:53 am
If you want to see whats running now, just run [sp_who2 'Active'] and look at the STATUS and CPU etc. To see whats been taking a while to run previously, use the above (or one of the many scripts you can find on here). Bear in mind that the data underneath the DMVs etc is lost when a service is restarted.
November 29, 2011 at 4:43 am
If you want to find out what is currently running for a long time use sys.dm_exec_requests. You can combine that with other DMOs such as sys.dm_exec_sql_text and sys.dm_exec_query_plan to get the query and the execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2011 at 10:02 am
If you want use the profiler then the below blog might be helpful too.
http://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply