Last time a stored procedure executed?

Question

Last time a stored procedure executed?

becky.mcdermott

SSC Journeyman

Points: 85
More actions
April 11, 2013 at 7:17 am

#276436

I would like to list all of the stored procedures in a database and know the last time they executed. I realize that this type of information can be somewhat reliable as it only reports on SPs that are cached but I would at least like to look at what's in the cache and when it last ran.
I have found several scripts/queries on the internet but each one I try gives a syntax error.
My server is SQL Server 2005 / Product Version 9.00.5069.00 / SP4 / Enterprise Edition (64-bit)
I tried the following scripts (which I found on this site) but I get the error
(Msg 102, Level 15, State 1, Line 19 / Incorrect syntax near '.'.):
Use MyDB
Go
SELECT a.execution_count ,
OBJECT_NAME(objectid) Name,
query_text = SUBSTRING(
b.text,
a.statement_start_offset/2,
(CASE WHEN a.statement_end_offset = -1
THEN len(convert(nvarchar(max), b.text)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset)/2
) ,
b.dbid ,
dbname = db_name(b.dbid) ,
b.objectid ,
a.creation_time,
a.last_execution_time,
a.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) like 'sp%'
ORDER BY a.last_execution_time DESC
Any advice? I have tried other queries/scripts too that use different methods but they all yield similar syntax errors.
Thank you

Viewing 11 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

the system function sys.dm_exec_sql_text(a.sql_handle) is for SQL2008 and above only.

SQL2005 will throw an error on syntax, because it's not valid. no cross applying functions back then but the function does exist if you change it to a sub select or join

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Lowell (4/11/2013)
the system function sys.dm_exec_sql_text(a.sql_handle) is for SQL2008 and above only.
SQL2005 will throw an error on syntax, because it's not valid. no cross applying functions back then but the function does exist if you change it to a sub select or join

Cross apply and the sys.dm_exec_sql_text DMF were both added in SQL 2005. You can't use that function in a join because the parameter for the function is a column from another DMV and that requires CROSS/OUTER apply. The only way to use it in a join would be to specify a hardcoded plan/sql handle.

Most likely the problem here is that the database being used is in compat mode 80. In compat mode 80, cross apply does not work because it's SQL 2000 compatibility. Try running the query from the master database (or any other database that is in compat mode 90)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

becky.mcdermott SSC Journeyman Points: 85 More actions · Answer 3

I am able run it on the Master database and your explanation makes sense.

I am sure that the database I want to run it in has some 2000 compatibility issues.

Is there any way to get this type of information (last executed time for stored procedures) from my database (SQL 2005 with 2000 compatibility issues)?

I'm guessing not but just thought I would ask.

Thank you

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

becky.mcdermott (4/11/2013)
Is there any way to get this type of information (last executed time for stored procedures) from my database (SQL 2005 with 2000 compatibility issues)?

The plan cache DMVs are not database-specific, so you can run it in master and get data for all databases. If you want to join to a specific database's catalog views to get object names, then just join with a 3-part name instead of 2-part.

p.s. You do realise that the plan cache query only returns data for queries whose plans are still in cache, a reboot will clear cache and there are many things that can throw plans out of cache. The 'last execution' from that is a best guess at most, its correct for the procs it returns but for any that aren't returned you can draw no conclusions at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

becky.mcdermott SSC Journeyman Points: 85 More actions · Answer 5

Yes, I do realize that only those SPs that are cached will be returned (and the cache is flushed each time the server is re-started). I know the information will not be 100% reliable but it does give us some insight.

Can you help me with the join portion of the query? These system level catalogs are a bit above my SQL knowlege.

My database is "Priya". I'm not sure where to add this when building a 3 part name. The current FROM clause is:

FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

How would I incporate my database (Priya) to build the 3 part name? I've tried a couple of things but keep getting "invalid object name".

Thank you

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

...

FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

INNER JOIN Priya.sys.procedures p on b.objectid = p.object_id

WHERE p.name LIKE 'sp%'

btw, starting a procedure name with sp_ is a bad practice, that prefix is for system procedures

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

becky.mcdermott SSC Journeyman Points: 85 More actions · Answer 7

Thank you! Worked like a charm.

Yes, I did comment out the where clause.

Is there any way to get the actual name of the stored procedure (as opposed to the "query_text")?

all of my OBJECT_NAME(objectid)'s come back as NULL. Ideally, I would like the name of the stored procedure as it is saved in the "Programmability" folder.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

becky.mcdermott (4/11/2013)
Thank you! Worked like a charm.
Yes, I did comment out the where clause.
Is there any way to get the actual name of the stored procedure (as opposed to the "query_text")?

The 'name' column from sys.procedures (already joined in). Object_Name references the current database, which is why I used the sys.procedures view instead for the filter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

becky.mcdermott SSC Journeyman Points: 85 More actions · Answer 9

Thank you so much for your help.

There are a lot of SPs that I would expect to see (I know they are run frequently) but they don't appear to be in the cache. I guess that is why it not really trustworthy information.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

Old blog post, but still valid

http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass