October 24, 2012 at 10:31 pm
Comments posted to this topic are about the item Last Accesses Stored Procedure
October 25, 2012 at 6:17 am
What does the "query text" have to do with the stored procedure, and why is it so different between different runs?
October 25, 2012 at 7:55 am
join sysobjects o on b.objectid=o.id
that way you can filter on xtype = 'P' and you aren't restricted to procs named sp%.
October 25, 2012 at 7:56 am
gregory.anderson (10/25/2012)
What does the "query text" have to do with the stored procedure, and why is it so different between different runs?
The DMV dm_exec_query_stats returns cached query plans, and in SQL Server 2005+, plans are cached at the statement level. Joining sys.dm_exec_sql_text and filtering on query text is needed to return stored procedure calls, because it conatins object name, but it's not really needed to include query text in the resultset, of all we want is a list of stored procedures with metrics. It seems that aggregating metrics on distinct object_name is what we want to do. Here is an alternate version that does just that.
SELECT
dbname = db_name(b.dbid) ,
OBJECT_NAME(objectid) Name,
min(a.creation_time)creation_time,
max(a.last_execution_time)last_execution_time,
sum(a.execution_count)execution_count
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) like 'usp%'
group by db_name(b.dbid), OBJECT_NAME(objectid)
ORDER BY max(a.last_execution_time) DESC
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 25, 2012 at 8:35 am
jamie.chicago (10/25/2012)
join sysobjects o on b.objectid=o.idthat way you can filter on xtype = 'P' and you aren't restricted to procs named sp%.
or to avoid the performance hit of the join you can alternatively use the objectpropertyex function
objectpropertyex(objectid,'BaseType') = 'P'
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 objectpropertyex(objectid,'BaseType') = 'P'
ORDER BY a.last_execution_time DESC
-
October 25, 2012 at 1:26 pm
When I run this, the Name of the sp doesn't even exist in the db its matching to. The query text doesn't match the name either. All I was after is a query that gives the db, name and when it last run. looks to be harder if not impossible!:crazy:
October 25, 2012 at 1:38 pm
nevhancock (10/25/2012)
When I run this, the Name of the sp doesn't even exist in the db its matching to. The query text doesn't match the name either. All I was after is a query that gives the db, name and when it last run. looks to be harder if not impossible!:crazy:
If you're looking for statistics at the procedure level, then the query needs to work from dm_exec_procedure_stats instead of dm_exec_query_stats.
For example, the following returns the top 10 procedures by average worker time.
select top 10
d.object_id, d.database_id,
object_name(object_id, database_id) proc_name,
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time / d.execution_count as avg_elapsed_time,
d.last_elapsed_time, d.execution_count
from sys.dm_exec_procedure_stats as d
order by total_worker_time desc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 25, 2012 at 2:03 pm
the query plan cache is server scoped not database scoped.
you can filter for a specific database on dm_exec_sql_text.dbid by database id or use the db_name(dbid) to filter by database name.
b.dbid = @dbid
--OR
db_name(b.dbid) = 'MySpecifiedDatabase'
Also the plan cache only contains data for active plans. If your procedure hasn't been executed in a long long time (definition of long time varies by instance since plan cache size depends on the server memory, number of queries and others), since the last service restart, or the last execution of DBCC FREEPROCCACHE etc.
This usage of these dmv's is new to me as of today and is the first time I've seen a solution that will return this kind of data (btw: thank you JasonClements).
If you have to know the last execution time of a procedure regardless of the plan cache activity, you'll have to add a run history table and code in the procs to insert history entries each time they are called.
(There may be other solutions and hopefully someone here will share, but this is the only other alternative I know of)
EDIT:
Also, thank you Eric for clarifying the DMV using the proc dmv simplifies this alot.
(I have now learned two new things today. Does that mean I can have tomorrow off :hehe:)
-
October 25, 2012 at 2:18 pm
Any idea why, when I try to use it, I receive a message:
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '.'.
Line 18 is:
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
:ermm:
I'm using:
SQL Server 2008 R2 x86 (32 bit)
Microsoft SQL Server Management Studio10.50.1617.0
Microsoft Analysis Services Client Tools10.50.1617.0
Microsoft Data Access Components (MDAC)3.85.1132
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3634
Operating System 5.1.2600 (XP SP3)
October 25, 2012 at 2:32 pm
rf44 (10/25/2012)
Any idea why, when I try to use it, I receive a message:Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '.'.
Line 18 is:
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b:ermm:
I'm using:
SQL Server 2008 R2 x86 (32 bit)
Microsoft SQL Server Management Studio10.50.1617.0
Microsoft Analysis Services Client Tools10.50.1617.0
Microsoft Data Access Components (MDAC)3.85.1132
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3634
Operating System 5.1.2600 (XP SP3)
Copy the text into an advanced text editor like NotePad++, and confirm that all lines are terminated with [CR][LF] and there are no special characters where you wouldn't expect them.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 25, 2012 at 4:30 pm
Eric M Russell (10/25/2012)
rf44 (10/25/2012)
Any idea why, when I try to use it, I receive a message:Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '.'.
Line 18 is:
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b:ermm:
I'm using:
SQL Server 2008 R2 x86 (32 bit)
Microsoft SQL Server Management Studio10.50.1617.0
Microsoft Analysis Services Client Tools10.50.1617.0
Microsoft Data Access Components (MDAC)3.85.1132
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3634
Operating System 5.1.2600 (XP SP3)
Copy the text into an advanced text editor like NotePad++, and confirm that all lines are terminated with [CR][LF] and there are no special characters where you wouldn't expect them.
Thanks for your answer. I checked with a Hex-Editor to be sure and the text is OK, no weird characters and correct [CR][LF] line endings.
October 26, 2012 at 7:31 am
rf44 (10/25/2012)
Eric M Russell (10/25/2012)
rf44 (10/25/2012)
Any idea why, when I try to use it, I receive a message:Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '.'.
Line 18 is:
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b:ermm:
I'm using:
SQL Server 2008 R2 x86 (32 bit)
Microsoft SQL Server Management Studio10.50.1617.0
Microsoft Analysis Services Client Tools10.50.1617.0
Microsoft Data Access Components (MDAC)3.85.1132
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3634
Operating System 5.1.2600 (XP SP3)
Copy the text into an advanced text editor like NotePad++, and confirm that all lines are terminated with [CR][LF] and there are no special characters where you wouldn't expect them.
Thanks for your answer. I checked with a Hex-Editor to be sure and the text is OK, no weird characters and correct [CR][LF] line endings.
Confirm the 'Compatability Mode' setting on the database that has context when you're running the script. Also, try running it from MASTER database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2012 at 2:30 pm
Agreed that this is better but it requires SQL 2008.
Lee
October 26, 2012 at 2:33 pm
Forgive the sparse post. I was referring to the use of [sys].[dm_exec_procedure_stats] as a better approach but it does require SQL Server 2008.
October 26, 2012 at 2:40 pm
If you decide you do NOT want to restict this to the current database you could change
OBJECT_NAME(objectid,) Name to:
OBJECT_NAME(b.objectid,b.dbid) Name
Lee
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply