Sql Profiler

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

  • 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

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

  • 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

  • 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;

  • 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