November 9, 2009 at 2:21 pm
how can i use sql profiler to monitor or track stored procedures that are running slowly in a production environment
November 9, 2009 at 2:28 pm
Hi
That's too less information. Do you have some special procedures to be traced? Do you have some general performance issues? Which kind of performance issues do you have?
Greets
Flo
November 9, 2009 at 2:43 pm
i want to track stored procedures on the production server that are hampering performance by running slowly
November 9, 2009 at 2:47 pm
Generally..
Start profiler, remove Audit Login/Log-off and Existing Connections, remove SQL:Batch Started and Completed and keep "RPC:Completed". Use Column Filter to specify a CPU-time or a duration which means "slow" in your case and start the trace.
As you see, it's quiet hard (impossible in my opinion) to help with more information.
Greets
Flo
November 9, 2009 at 4:12 pm
I'd do as Flo has recommended although with the caveat that you may want to look at executions in addition to duration. You may get a bigger boost out of tuning a query that takes 2 seconds but runs 100 times an hour versus one that takes 20 seconds but it only run twice a day.
You should also look at the sys.dm_exec_query_stats DMV. Something like this (from BOL):
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
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) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2011 at 3:00 pm
I would say copy the database to development and test with the following events:
-RPC:Completed
-SP:Completed
This blog discusses the topic well.
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/0c922a7f-f782-4179-a42c-3b7310652463
Also this is mentioned in the sample video from this site
http://sqlserver2008tutorial.com/sql-tutorials.html
Later
Kash
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply