Finding most used store procedures, given database and that touch a particular table.

• sql-lover

  SSCoach

  Points: 18530

  More actions

  September 4, 2015 at 1:37 pm

  #321000

  Not my TSQL snippet, but I modified it a bit...

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  SELECT TOP 10 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,

  execution_count,

  s2.objectid,

  (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,

  ( (CASE WHEN statement_end_offset = -1

  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)

  ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,

  last_execution_time

  FROM sys.dm_exec_query_stats AS s1

  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x

  WHERE (sql_statement like '%MyTable%' OR sql_statement LIKE '%dbo.MyTable%') AND sql_statement<>'Ad-Hoc'

  and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1

  and exists (SELECT 1 FROM sys.procedures s

  WHERE s.is_ms_shipped = 0

  and s.name = x.ProcName )

  ORDER BY execution_count DESC, ProcName

  The idea, as indicated in the thread's title, Is to be able to find most used store procedures that also touch or read a particular table. Above DMV seems to accomplish the 1st, not is showing sql statements with that table that do not look are part of the store procedure.

  Am I missing something?

  :ermm: