List all Stored Procs which are taking >=2 secs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

List all Stored Procs which are taking >=2 secs

Author

Topic

prasanth_sqlbeginner
Starting Member

3 Posts

Posted - 2012-06-07 : 21:50:09

Without using SQL Profiler is there any other way to identify all Stored Procs which are taking more than 2 secs to execute? Will any of the DMVs be of help here?

Regards
Prasanth

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-08 : 11:04:09

You can customized the below query as per your requirement and not sure abt DMVs.

DECLARE @i int = 0, @TestLimit int = 10

DECLARE @Timing TABLE ( Timing int)

WHILE (@i < @TestLimit)

BEGIN

DECLARE @RC int, @ManagerID int =1

DBCC DROPCLEANBUFFERS

DECLARE @Start datetime = getdate()

EXECUTE @RC = [AdventureWorks2008].[dbo].[uspGetManagerEmployees] @ManagerID

INSERT @Timing

SELECT ExecutionMsec = datediff (millisecond, @Start, getdate())

SET @i=@i+1

END

SELECT ExecutionMsec = avg(Timing) FROM @Timing

Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources