C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Offshore Development    
Silverlight Tutorials | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !






How to find Most used Stored procedure


Posted Date: 07 Jun 2008    Resource Type: Code Snippets    Category: SQL Schema
Author: Jins PhilipMember Level: Gold    
Rating: Points: 10



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




Responses

Author: Kapil Dhawan    18 Jun 2008Member 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



Feedbacks      
Popular Tags   What are tags ?   Search Tags  
(No tags found.)

Post Feedback


This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.
Next Resource: How to identify all the columns with datatype
Previous Resource: How to find all User Defined Funtions From a Database
Return to Discussion Resource Index
Post New Resource
Category: SQL Schema


Post resources and earn money!
 
Related Resources



dotNet Slackers   BizTalk Adaptors    Web Design


Contact Us    Privacy Policy    Terms Of Use