This following sample code used to find Most used Stored procedure.
SELECT TOP 10 qt.TEXT AS 'StoredProcedure', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_physical_reads AS 'PhysicalReads', qs.creation_time 'CreationTime', qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = ( SELECT dbid FROM sys.sysdatabases WHERE name = 'NorthWind') -- U can change the name of ur Database from NorthWind to Ur's ORDER BY qs.total_physical_reads DESC
|
| Author: Kapil Dhawan 18 Jun 2008 | Member Level: Gold Points : 2 |
Hello Nice piece of code Thanks for sharing your knowledge with us. I hope to see more good code from your side This code will help lots of guys Thanks to you Regards, Kapil
|