Michael-Thomas
asked on
Identifying and Monitoring long running Stored Procedures & Queries in SQL 2005
I would like to create a stored procedure that can report back on queries and stored procedures that run longer than X time (15s in my case). I am looking for all the regular performance details for the long running query/sp such as logical/physical read/writes along with the running times and the idea is to have the results inserted into a table and have daily reports emailed later.
Thanks in advance
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
make sure to run DBCC FREEPROCCACHE to flush the cached SP query stats if you want to start with fresh data.
That could be a big hit on performance on a prod box.
Insteaad you might want to consider capturing the results of a system view query, then running the query again in a <fixed amount of time>. You can then subtract the two values to get the activity that occurred during the <fixed amount of time>.
Insteaad you might want to consider capturing the results of a system view query, then running the query again in a <fixed amount of time>. You can then subtract the two values to get the activity that occurred during the <fixed amount of time>.
Indeed do NOT run FREEPROCCACE or DROPCLEANBUFFERS on prod dbs and you could use the code below for your purpose but SQL 2005 has pretty good Standard reports for that - just right click the server in SSMS go to Reports and select Standard. You could also download performance_dashboard report from Microsoft which I recommend:
http://www.microsoft.com/downloads/en/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
--CREATE PROC Get_ActiveProcesses @Delay smallint= 5
--AS
----
---- Description Returns list of active processes and their buffer contents (what they execute)
---- A process is considered as active if it has some changes of cpu time consumed or number
---- of io operation in specified period.
---- Input (optional) @Delay - Time interval to catch activity
---- Output Result set with active processes
----
SET nocount on
DECLARE @Delay smallint
SET @Delay = 120
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
PRINT @Delay
DECLARE @DelayClock CHAR(8), @Internal_Value int
SET @DelayClock = '00:00:' + LTRIM(STR(@Delay))
CREATE TABLE #tmpsysprocesses (
EventTime DATETIME,
DBName sysname NULL,
ObjectName sysname NULL,
spid smallint,
[Host_Name] sysname NULL,
[Program_Name] sysname NULL,
[Login_Name] sysname NULL,
[Start_Time] DATETIME,
TotalReads int,
TotalWrites int,
TotalCPU int,
Writes_in_TempDB int,
CmdType VARCHAR(16),
CmdStatus VARCHAR(30),
SQL_Text xml,
Blocking_spid smallint,
Blocking_Text xml)
INSERT INTO #tmpsysprocesses
SELECT GETDATE(), (SELECT DB_NAME(dbid) FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle)) AS dbname,
(SELECT OBJECT_NAME(objectid) FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle)) AS objectname,
x.session_id AS spid,
x.host_name,
x.program_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
x.command AS cmdtype,
x.status,
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle) FOR XML PATH(''), TYPE) AS sql_text,
COALESCE(x.blocking_sessio n_id, 0) AS blocking_spid,
(SELECT p.text FROM (SELECT MIN(sql_handle) AS sql_handle FROM master.sys.dm_exec_request s r2 WHERE r2.session_id = x.blocking_session_id) AS r_blocking
CROSS APPLY
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(r_blocki ng.sql_han dle) FOR XML PATH(''), TYPE) p (text)) AS blocking_text
FROM
(SELECT r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id,r.co mmand,r.st atus,
SUM(r.reads) AS totalReads, SUM(r.writes) AS totalWrites, SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc _page_coun t + tsu.internal_objects_alloc _page_coun t) AS writes_in_tempdb, s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id, r.command, r.status, s.program_name) x
WAITFOR delay @DelayClock
CREATE TABLE #tmpsysprocesses2 (
EventTime DATETIME,
DBName sysname NULL,
ObjectName sysname NULL,
spid smallint,
[Host_Name] sysname NULL,
[Program_Name] sysname NULL,
[Login_Name] sysname NULL,
[Start_Time] DATETIME,
TotalReads int,
TotalWrites int,
TotalCPU int,
Writes_in_TempDB int,
CmdType VARCHAR(16),
CmdStatus VARCHAR(30),
SQL_Text xml,
Blocking_spid smallint,
Blocking_Text xml)
INSERT INTO #tmpsysprocesses2
SELECT GETDATE(), (SELECT DB_NAME(dbid) FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle)) AS dbname,
(SELECT OBJECT_NAME(objectid) FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle)) AS objectname,
x.session_id AS spid,
x.host_name,
x.program_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
x.command AS cmdtype,
x.status,
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(x.sql_ha ndle) FOR XML PATH(''), TYPE) AS sql_text,
COALESCE(x.blocking_sessio n_id, 0) AS blocking_spid,
(SELECT p.text FROM (SELECT MIN(sql_handle) AS sql_handle FROM master.sys.dm_exec_request s r2 WHERE r2.session_id = x.blocking_session_id) AS r_blocking
CROSS APPLY
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(r_blocki ng.sql_han dle) FOR XML PATH(''), TYPE) p (text)) AS blocking_text
FROM
(SELECT r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id,r.co mmand,r.st atus,
SUM(r.reads) AS totalReads, SUM(r.writes) AS totalWrites, SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc _page_coun t + tsu.internal_objects_alloc _page_coun t) AS writes_in_tempdb, s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id, r.command, r.status, s.program_name) x
CREATE TABLE #xp_msver (
[index] int,
[name]VARCHAR(1000) NULL,
internal_value INT NULL,
character_value VARCHAR(1000) NULL)
INSERT INTO #xp_msver
EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = internal_value
FROM #xp_msver
WHERE [name] = 'ProcessorCount'
SELECT t2.DBName,t2.ObjectName,t2 .spid 'ProcessId',t2.TotalReads, t2.TotalWr ites,t2.To talReads+t 2.TotalWri tes 'TotalPhysical_IO',(t2.Tot alReads+t2 .TotalWrit es)-(t.Tot alReads+t. TotalWrite s) 'Physical_IO_InTheTimeFrag ment',t2.T otalCPU,t2 .TotalCPU- t.TotalCPU 'CPU_ConsumedInTheTimeFrag ment',t2.W rites_in_T empDB,UPPE R(t2.CmdSt atus) 'CmdStatus',t2.CmdType,t2. SQL_Text,t 2.[Program _Name] 'Application_Name',t2.[Hos t_Name],t2 .[Login_Na me],t2.[St art_Time], t2.Blockin g_spid,t2. Blocking_T ext
FROM #tmpsysprocesses2 t2 INNER JOIN #tmpsysprocesses t
ON t2.spid = t.spid AND t2.start_time = t.start_time --AND t2.DBName IS NOT NULL
SELECT MAX(DATEDIFF(ms,t.EventTim e,t2.Event Time)) 'Fragment_Duration', @Internal_Value 'Number_Of_CPUs', SUM(t2.TotalCPU-t.TotalCPU )'SUM CPU_ConsumedInTheTimeFragm ent', SUM((t2.TotalReads+t2.Tota lWrites)-( t.TotalRea ds+t.Total Writes))'S UM Physical_IO_InTheTimeFragm ent'
FROM #tmpsysprocesses2 t2 INNER JOIN #tmpsysprocesses t ON t2.spid = t.spid
SELECT TOP 5 'Top 5 Queries by CPU' AS Comment, total_worker_time/executio n_count AS [Avg_CPU_Time], execution_count AS [Execution_Count],
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(qs.sql_h andle) FOR XML PATH(''), TYPE) AS Statement_Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq l_handle) AS st
ORDER BY total_worker_time/executio n_count DESC;
SELECT TOP 5 'Top 5 Queries by IO' AS Comment, (total_logical_writes + total_logical_reads)/execu tion_count AS [Avg_IO_per_Exec], execution_count AS [Execution_Count],
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex t(qs.sql_h andle) FOR XML PATH(''), TYPE) AS Statement_Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq l_handle) AS st
ORDER BY (total_logical_writes + total_logical_reads)/execu tion_count DESC;
DROP TABLE #tmpsysprocesses
DROP TABLE #tmpsysprocesses2
DROP TABLE #xp_msver
GO
http://www.microsoft.com/downloads/en/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
--CREATE PROC Get_ActiveProcesses @Delay smallint= 5
--AS
----
---- Description Returns list of active processes and their buffer contents (what they execute)
---- A process is considered as active if it has some changes of cpu time consumed or number
---- of io operation in specified period.
---- Input (optional) @Delay - Time interval to catch activity
---- Output Result set with active processes
----
SET nocount on
DECLARE @Delay smallint
SET @Delay = 120
IF @Delay > 59
SET @Delay = 59
IF @Delay < 1
SET @Delay = 1
PRINT @Delay
DECLARE @DelayClock CHAR(8), @Internal_Value int
SET @DelayClock = '00:00:' + LTRIM(STR(@Delay))
CREATE TABLE #tmpsysprocesses (
EventTime DATETIME,
DBName sysname NULL,
ObjectName sysname NULL,
spid smallint,
[Host_Name] sysname NULL,
[Program_Name] sysname NULL,
[Login_Name] sysname NULL,
[Start_Time] DATETIME,
TotalReads int,
TotalWrites int,
TotalCPU int,
Writes_in_TempDB int,
CmdType VARCHAR(16),
CmdStatus VARCHAR(30),
SQL_Text xml,
Blocking_spid smallint,
Blocking_Text xml)
INSERT INTO #tmpsysprocesses
SELECT GETDATE(), (SELECT DB_NAME(dbid) FROM master.sys.dm_exec_sql_tex
(SELECT OBJECT_NAME(objectid) FROM master.sys.dm_exec_sql_tex
x.session_id AS spid,
x.host_name,
x.program_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
x.command AS cmdtype,
x.status,
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
COALESCE(x.blocking_sessio
(SELECT p.text FROM (SELECT MIN(sql_handle) AS sql_handle FROM master.sys.dm_exec_request
CROSS APPLY
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
FROM
(SELECT r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id,r.co
SUM(r.reads) AS totalReads, SUM(r.writes) AS totalWrites, SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id, r.command, r.status, s.program_name) x
WAITFOR delay @DelayClock
CREATE TABLE #tmpsysprocesses2 (
EventTime DATETIME,
DBName sysname NULL,
ObjectName sysname NULL,
spid smallint,
[Host_Name] sysname NULL,
[Program_Name] sysname NULL,
[Login_Name] sysname NULL,
[Start_Time] DATETIME,
TotalReads int,
TotalWrites int,
TotalCPU int,
Writes_in_TempDB int,
CmdType VARCHAR(16),
CmdStatus VARCHAR(30),
SQL_Text xml,
Blocking_spid smallint,
Blocking_Text xml)
INSERT INTO #tmpsysprocesses2
SELECT GETDATE(), (SELECT DB_NAME(dbid) FROM master.sys.dm_exec_sql_tex
(SELECT OBJECT_NAME(objectid) FROM master.sys.dm_exec_sql_tex
x.session_id AS spid,
x.host_name,
x.program_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
x.command AS cmdtype,
x.status,
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
COALESCE(x.blocking_sessio
(SELECT p.text FROM (SELECT MIN(sql_handle) AS sql_handle FROM master.sys.dm_exec_request
CROSS APPLY
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
FROM
(SELECT r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id,r.co
SUM(r.reads) AS totalReads, SUM(r.writes) AS totalWrites, SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY r.session_id, s.host_name, s.login_name, r.start_time, r.sql_handle, r.blocking_session_id, r.command, r.status, s.program_name) x
CREATE TABLE #xp_msver (
[index] int,
[name]VARCHAR(1000) NULL,
internal_value INT NULL,
character_value VARCHAR(1000) NULL)
INSERT INTO #xp_msver
EXEC master..xp_msver 'ProcessorCount'
SELECT @Internal_Value = internal_value
FROM #xp_msver
WHERE [name] = 'ProcessorCount'
SELECT t2.DBName,t2.ObjectName,t2
FROM #tmpsysprocesses2 t2 INNER JOIN #tmpsysprocesses t
ON t2.spid = t.spid AND t2.start_time = t.start_time --AND t2.DBName IS NOT NULL
SELECT MAX(DATEDIFF(ms,t.EventTim
FROM #tmpsysprocesses2 t2 INNER JOIN #tmpsysprocesses t ON t2.spid = t.spid
SELECT TOP 5 'Top 5 Queries by CPU' AS Comment, total_worker_time/executio
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq
ORDER BY total_worker_time/executio
SELECT TOP 5 'Top 5 Queries by IO' AS Comment, (total_logical_writes + total_logical_reads)/execu
(SELECT text AS [text()] FROM master.sys.dm_exec_sql_tex
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sq
ORDER BY (total_logical_writes + total_logical_reads)/execu
DROP TABLE #tmpsysprocesses
DROP TABLE #tmpsysprocesses2
DROP TABLE #xp_msver
GO
ASKER
Thanks, definitely don't want to run FREEPROCCACE or DROPCLEANBUFFERS on my production DBs.
I am going to try both Racimo's server side trace (too bad there is no option to load into a table by default) and lcohan's query and I'll get an update later this afternoon.
I am going to try both Racimo's server side trace (too bad there is no option to load into a table by default) and lcohan's query and I'll get an update later this afternoon.
If you run SQL Profiler, after you put your credentials on the "Trace Properties" window check box "Save to table" and follow the steps - this way trace data is saved into a table. Hope this is what you wanted.
<<If you run SQL Profiler, after you put your credentials on the "Trace Properties" window check box "Save to table" and follow the steps - this way trace data is saved into a table. Hope this is what you wanted.>>
A few words of caution on that...
I would not recommend running client side tracing on a production since it can be very dangerous to the servers that are under heavy load. Using profiler can create an overhead that can bring a server down under heavy concurrency situations. On the other hand, server side scripting on the other hand is very safe even under heavy conditions but can only be set up through scripting.
A few words of caution on that...
I would not recommend running client side tracing on a production since it can be very dangerous to the servers that are under heavy load. Using profiler can create an overhead that can bring a server down under heavy concurrency situations. On the other hand, server side scripting on the other hand is very safe even under heavy conditions but can only be set up through scripting.
That is all true indeed: if you you are not familiar with the tool itself you could crash your SQL by only running a default trace however....it is the SQL built in tool that if used wisely I believe many other databases would like to have it. For instance we use it ocasionaly with success against a web site SQL backend database to identify speciffic security and/or performance issues.
Just don't run on the same computer as your SQL server and make sure your trace data itself is light.
Just don't run on the same computer as your SQL server and make sure your trace data itself is light.
ASKER
Thanks, definitely not planning on running profiler on the server itself. I did learn that one the hard way.
<<Just don't run on the same computer as your SQL server and make sure your trace data itself is light.>>
Actually, client side tracing is dangerous whether you run the client locally or remotely. I have seen profiler sessions bringing down servers *in minutes*, even though the profiler was running remotely.
The cause is that client side overhead is *not* only related to the RAM consumed by the client, which is fixed, but mainly to the memory overhead caused by the buffer size increase to stream data to the client in real time. To ellaborate,under high transactionnal throughput, client side tracing creates memory pressure, because the streaming buffer can not purge itself fast enough to make room for new data to be streamed.
The only real safe solution is to use server side tracing which does not have that kind of overhead. But unfortunately it can not be configured in a GUI.
HTH
Actually, client side tracing is dangerous whether you run the client locally or remotely. I have seen profiler sessions bringing down servers *in minutes*, even though the profiler was running remotely.
The cause is that client side overhead is *not* only related to the RAM consumed by the client, which is fixed, but mainly to the memory overhead caused by the buffer size increase to stream data to the client in real time. To ellaborate,under high transactionnal throughput, client side tracing creates memory pressure, because the streaming buffer can not purge itself fast enough to make room for new data to be streamed.
The only real safe solution is to use server side tracing which does not have that kind of overhead. But unfortunately it can not be configured in a GUI.
HTH
<<Thanks, definitely not planning on running profiler on the server itself. I did learn that one the hard way.>>
Do NOT use Profiler on a production if you have high transactional load. The only safe way to do that is to use server side tracing that establish a trace without a GUI.
In my company, it is strictly forbidden to run profile on some servers that have been brought down.
From MS Support
http://support.microsoft.com/kb/929728/en-us
".... do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events...."
meaning the problem is known and identified...
More links on how to do this right...
http://www.mssqltips.com/tip.asp?tip=1035
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Hope this helps
Do NOT use Profiler on a production if you have high transactional load. The only safe way to do that is to use server side tracing that establish a trace without a GUI.
In my company, it is strictly forbidden to run profile on some servers that have been brought down.
From MS Support
http://support.microsoft.com/kb/929728/en-us
".... do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events...."
meaning the problem is known and identified...
More links on how to do this right...
http://www.mssqltips.com/tip.asp?tip=1035
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Hope this helps
To clarify further...
When you run SQL Profiler against an instance of SQL Server 2000 or of SQL Server 2005, each active SPID of the instance requires an exclusive latch (LATCH_EX) to write the event to SQL Profiler. The SPID writes the event to the local memory buffer first. Then, the event is transferred to SQL Profiler. If the local memory buffer is full, the SPID discards the event. After the events in the local memory buffer are transferred to SQL Profiler, the SPID can write new events to the local memory buffer. If other SPIDs are waiting to write events to SQL Profiler, these SPIDs wait in a queue.
When you run SQL Profiler against an instance of SQL Server 2000 or of SQL Server 2005, each active SPID of the instance requires an exclusive latch (LATCH_EX) to write the event to SQL Profiler. The SPID writes the event to the local memory buffer first. Then, the event is transferred to SQL Profiler. If the local memory buffer is full, the SPID discards the event. After the events in the local memory buffer are transferred to SQL Profiler, the SPID can write new events to the local memory buffer. If other SPIDs are waiting to write events to SQL Profiler, these SPIDs wait in a queue.
>> (too bad there is no option to load into a table by default) <<
There actually is, but it's too much overhead, so I strongly recommend you *not* do it.
You can load the trace file(s) into table(s) later if you need to.
There actually is, but it's too much overhead, so I strongly recommend you *not* do it.
You can load the trace file(s) into table(s) later if you need to.
ASKER
Thanks everyone.
In the end I chose to use a server side trace and actually used the scripts from http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm to make a bit easier for reuse. I also found a site that can really help if you're not sure of the details for creating what you want (http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-create-a-server-side-trace-with-sql-profiler/).
In the end I chose to use a server side trace and actually used the scripts from http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm to make a bit easier for reuse. I also found a site that can really help if you're not sure of the details for creating what you want (http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-create-a-server-side-trace-with-sql-profiler/).
ASKER
Following examples on the provided url http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm helped alot on setting up the server side trace
<<Following examples on the provided url http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm helped alot on setting up the server side trace>>
Glad it helped. In the end, server side traces are safe and highly reusable since they can be programmed to run for specific period of time, each time a problem arises, with almost neglectable impact on servers's performance.
Good luck...
Glad it helped. In the end, server side traces are safe and highly reusable since they can be programmed to run for specific period of time, each time a problem arises, with almost neglectable impact on servers's performance.
Good luck...
to get more info on sys.dm_exec_query_stats, check out http://msdn.microsoft.com/en-us/library/ms189741%28SQL.90%29.aspx
Open in new window