Situatie
Mai jos este o procedura ce returneaza top 10 cele mai utilizate proceduri sql din baza de date pe care este rulata.
Solutie
create procedure ListTop10MostUsedSp_prc
as
SELECT top 10 DB_NAME(SQTX.DBID) AS [DBNAME] ,
OBJECT_SCHEMA_NAME(SQTX.OBJECTID,DBID)
AS [SCHEMA], OBJECT_NAME(SQTX.OBJECTID,DBID)
AS [STORED PROC] , MAX(CPLAN.USECOUNTS) [EXEC COUNT]
FROM SYS.DM_EXEC_CACHED_PLANS CPLAN
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CPLAN.PLAN_HANDLE) SQTX
WHERE DB_NAME(SQTX.DBID) IS NOT NULL AND CPLAN.OBJTYPE = ‘PROC’
GROUP BY CPLAN.PLAN_HANDLE ,DB_NAME(SQTX.DBID) ,OBJECT_SCHEMA_NAME(OBJECTID,SQTX.DBID) ,OBJECT_NAME(OBJECTID,SQTX.DBID)
ORDER BY MAX(CPLAN.USECOUNTS) DESC
Leave A Comment?