Track your Procedure Execution Time

Posted: August 27, 2010 in SQLServerPedia Syndication

Do you know how long your procs run on average? What about a trend on how long they have been executing?

Ok so a while ago I posted a DMV that Glenn Berry used in a presentation. It was a great piece of code that I am using a lot, but I found myself wanting to know over an extended period of time how the performance on the procs was changing, were things getting better or worse? There is a number of developers where I am working right now and each of them are working on new aspects of the applications we have, so how does what they are doing now, going to impact what we already have in the database?

A note about Glenn, I have had a couple discussions with Glenn and I can tell you each time I contact him he has been an awesome help. Tonight when I was grabbing the link for is blog I noticed even another set of DMV’s there that I didn’t have a chance to look at. If you have not seen his blog I highly recommend it. You can find his blog here, Thanks Glenn

I made a few changes and here is what I did.

 

First I created a table in my DBStats database that I keep out there:

 

CREATE
TABLE [dbo].[PROC_Exe_Times](

    [dbname] [varchar](200)
NOT
NULL,

    [spname] [varchar](2000)
NULL,

    [ExeCount] [bigint] NULL,

    [ExePerSec] [bigint] NULL,

    [AvgWorkerTime] [bigint] NULL,

    [TotalWorkerTime] [bigint] NULL,

    [AvgElapsedTime] [bigint] NULL,

    [MaxLogicalReads] [bigint] NULL,

    [MaxLogicalWrites] [bigint] NULL,

    [TotalPhysicalReads] [bigint] NULL,

    [DateRecorded] [datetime] NULL

)
ON [PRIMARY]

 

GO

 

Then I create a proc that will cursor through each of the databases, other than the ones I don’t care about for this effort and record that information in the Table. This will give me a good idea how the performance trends over time.

 

CREATE
PROCEDURE [dbo].[GetProcExe]

AS

DECLARE @DBNAME VARCHAR(20)

DECLARE db_Cursor CURSOR

    FOR
SELECT NAME FROM
master..sysdatabases

        WHERE name NOT
IN
(‘Master’,‘Model’, ‘MSDB’, ‘TempDB’, ‘distribution’, ‘LiteSpeedLocal’)

OPEN db_cursor

FETCH
NEXT
FROM db_cursor INTO @DBNAME

WHILE
@@FETCH_STATUS
= 0

    BEGIN

    –DECLARE @DBname VARCHAR(10)

    INSERT
INTO DBSTats..PROC_Exe_Times

    (dbname,SPname, ExeCount, ExePerSec, AvgWorkerTime, TotalWorkerTime, AvgElapsedTime, MaxLogicalReads, MaxLogicalWrites, TotalPhysicalReads, DateRecorded)

        SELECT

            @dbname AS dbname,

            OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid)
+
‘.’
+
object_name(qt.objectid, qt.dbid) AS spname,

            qs.execution_count AS
‘Execution Count’,

            qs.execution_count/DATEDIFF(Second, qs.creation_time,
GetDate())
AS
‘Calls/Second’,

            qs.total_worker_time/qs.execution_count AS
‘AvgWorkerTime’,

            qs.total_worker_time AS
‘TotalWorkerTime’,

            qs.total_elapsed_time/qs.execution_count AS
‘AvgElapsedTime’,

            qs.max_logical_reads,

            qs.max_logical_writes,

            qs.total_physical_reads,

            GETDATE()
AS RecordedDate

        FROM
sys.dm_exec_query_stats
AS qs

            CROSS
APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt

            WHERE qt.dbid =
db_id(@dbname)
— Filter by current database

        ORDER
BY qs.execution_count DESC

    FETCH
NEXT
FROM db_cursor INTO @DBNAME    

    END

WHILE
@@FETCH_STATUS
= 0

CLOSE db_Cursor

DEALLOCATE db_cursor

DBCC FreeProcCache

 

 

GO

 

I want to make sure that I thank Glenn for sharing this DMV with me, this is going to give me a lot of information that I have not had in the past.

Comments
  1. Ian Stirk says:

    Hi,

    Nice article.

    You can discover a lot more about DMVs in this forthcoming book http://www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:

    A simple monitor
    Finding your slowest queries
    Find your missing indexes
    Identifying what SQL is running now
    Quickly find a cached plan

    Thanks
    Ian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s