Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Profiler Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160
how can i use sql profiler to monitor or track stored procedures that are running slowly in a production environment
Post #816134
Posted Monday, November 9, 2009 2:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #816137
Posted Monday, November 9, 2009 2:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:21 PM
Points: 116, Visits: 160
i want to track stored procedures on the production server that are hampering performance by running slowly
Post #816149
Posted Monday, November 9, 2009 2:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #816153
Posted Monday, November 9, 2009 4:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 11,148, Visits: 12,888
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #816231
Posted Saturday, January 22, 2011 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:30 PM
Points: 7, Visits: 167
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


[url][/url]
Post #1052000
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse