Friday, March 23, 2012

how many times a DML was executed ?

how can I find out , how many times a SQL statment was executed ?
can it be done using enterprise Manager , T-sql scripts ?
(same as V$sql.EXECUTIONS in oracle)
10x in advanceLook in Books-On-Line at system table syscacheobjects and column usecounts. I believe that once the object is swapped out of cache and brought back in the count will be initiated to zero.

Example script
SELECT SUBSTRING(master..syscacheobjects.sql,1,35) AS SQL,
master..syscacheobjects.cacheobjtype AS CacheObjectType,
master..syscacheobjects.objtype AS ObjectType,
SUBSTRING(master..sysdatabases.name,1,25) AS DatabaseName,
master..syscacheobjects.pagesused,
master..syscacheobjects.usecounts AS [Number of times used since inception],
master..syscacheobjects.sqlbytes AS [Length of name or batch submitted (in bytes)],
SUBSTRING(master..sysusers.name,1,15) AS Creator
From master..syscacheobjects
INNER JOIN
master..sysdatabases
ON master..syscacheobjects.dbid = master..sysdatabases.dbid
INNER JOIN
master..sysusers
ON master..syscacheobjects.uid = master..sysusers.uid
ORDER BY
master..sysdatabases.name

No comments:

Post a Comment