Sql Profiler

Question

Sql Profiler

klineandking

SSCrazy

Points: 2570
More actions
November 9, 2009 at 2:21 pm

#217449

how can i use sql profiler to monitor or track stored procedures that are running slowly in a production environment

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

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

klineandking SSCrazy Points: 2570 More actions · Answer 2

i want to track stored procedures on the production server that are hampering performance by running slowly

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

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

Kash SSC Journeyman Points: 79 More actions · Answer 5

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