--Execute in SSMS Window
--E&OE - Tested in SQL 2008 and Above. Other variations of this script might be existing too.
Mehernosh,
2015-05-01 (first published: 2013-08-13)
--Execute in SSMS Window
--E&OE - Tested in SQL 2008 and Above. Other variations of this script might be existing too.
/*########################################### Find the most executed stored procedure(s). ############################################*/ SELECT 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 --E&OE - Other variations of this script might be existing too.
Here's a general overview for creating and editing stored procedures. Head over to the discussion forum for this article and present your creative ideas for using this powerful feature of SQL Server to the SSC community.
2002-05-27
19,483 reads
Sp_readerrorlog can read any file on the hard disk, not just error logs. Read on to learn how!
The third article in a four part series, this week Andy shows how to use the command object to work with stored procedure parameters.
2007-02-09 (first published: 2001-12-18)
39,653 reads
Designing your application so that Stored Procedures are the only database objects that are accessed is generally the best practice. Agree? Disagree? Read this article by guest columnist Mindy Curnutt to see what she thinks!
2001-08-01
12,462 reads
My ASP file doesn’t access my database." "I can’t connect to my database from my code." "I’m having problems calling and debugging stored procedures." These are some of the problems I hear every day as a Microsoft® developer support engineer.
2001-07-20
2,008 reads