March 2, 2012 at 5:55 am
Hello!
How can i logging or monitoring the slow queries on the SQL Server 2005 and SQL Server 2008?
Thanks
March 2, 2012 at 6:07 am
you would want to build a query up which looks at sys.dm_exec_requests and cross applies it to sys.dm_exec_sql_text where the sys.dm_exec_requests.total_elapsed_time is greater than the number of seconds what you qualify as a slow query and also doesnt show any system spids
March 5, 2012 at 9:10 am
Thanks! Now I know monitoring the queries.
But how to know I logging the slow queries (Examlpe the time of query bigger then 5 seconds)?
March 5, 2012 at 9:28 am
WHERE sys.dm_exec_requests.total_elapsed_time > 5
Jared
CE - Microsoft
March 5, 2012 at 9:51 am
you would also want to exclude any spid below 50 so that no system processes are picked up as well which could take longer than X seconds
March 5, 2012 at 1:27 pm
Here's what I use (includes everything that has already been mentioned), I adapted it from an article I read here on SSCCREATE PROC [dbo].[utl_SeeWhatsExecuting]
@Duration int = NULL
AS
/*
exec utl_SeeWhatsExecuting 5000
*/
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @Duration IS NULL
SET @Duration = 0
SELECT
session_id [Spid],
total_elapsed_time [Duration],
DB_NAME(sp.dbid) [DB],
nt_username [User],
er.status [Status],
wait_type [Wait],
SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2) [Currently Executing],
qt.text [Calling Code],
program_name [Program],
Hostname [Host],
nt_domain [Domain],
start_time [Started]
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
AND total_elapsed_time > @Duration
ORDER BY 1, 2
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply