|
|
Posted by Erland Sommarskog on 01/22/08 22:15
Uri Dimant (urid@iscar.co.il) writes:
> Copy -Paste from Tony articles
>
> In SQL 2005 there is, sort of. This is query lists the last execution
> time for all SQL modules in a database:
>
> SELECT object_name(m.object_id), MAX(qs.last_execution_time)
> FROM sys.sql_modules m
> LEFT JOIN (sys.dm_exec_query_stats qs
> CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
> ON m.object_id = st.objectid
> AND st.dbid = db_id()
> GROUP BY object_name(m.object_id)
>
> But there are tons of caveats. The starting point of this query is
> the dynamic management view dm_exec_query_stats, and the contents is
> per *query plan*. If a stored procedure contains several queries,
> there are more than one entry for the procedure in dm_exec_query_stats.
And very importantly: it only lists what's in the cache. A procedure could
fall out of the cahce for several reasons. For instance, adding an index
on a table, flushes all plans related to that table. And a server restart
empites the cache entirely.
I would be very careful to use information of that kind to draw
conclusion of whether an object is in use.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|