Stored Procedures

You properly know how a database environment are changing all the time, reports and databases comes and go. A part of your daily tasks is to write stored procedures to support all queries against your server. As time goes the volume of unused stored procedures grow, making cleaning up quite at task.

I came across the same problem some time ago, and I had some thoughts on how to handle the task, and came up with the following solution.

I will create a table in some “management database” and then query sys.dm_exec_procedure_stats, getting last execution time, but the DMV’s only hold statistics from last restart, then they are deleted, and often not as long, it depends, when the plan are dropped from the plancache, to handle this I wrote my query as a stored procedure and then run that procedure every 5 minutes, updating the values in my table, then I will get a table of StoredProcedure names and when they were last run, after a month I would have a idea of witch StoredProcedures were used but of cause some stored procedures are only used once every year.

The upside to this solution is the data is stored after server restart, making it easy to query not used stored procedures.
First I will create a “Management Database” (if you have one use it) and the table to hold the data:

USE ManagmentDB
GO

DROP TABLE IF EXISTS StoredProcedureUsed

CREATE TABLE dbo.StoredProcedureUsed(
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    ProcedureName SYSNAME,
    DatabaseName SYSNAME,
    DatabaseId INT,
    Last_Execution_Time DATETIME2,
    Execution_Count BIGINT
)

And then the SQL Query to get the data:

SET NOCOUNT ON;
DROP TABLE IF EXISTS #spStats;

SELECT DISTINCT
       OBJECT_NAME(P.object_id) AS 'ProcedureName',
       DB_NAME(database_id) AS 'DatabaseName',
       DB_ID() AS 'DatabaseId',
       last_execution_time,
       execution_count
INTO #spStats
FROM sys.dm_exec_procedure_stats AS P
WHERE OBJECT_NAME(P.object_id) IS NOT NULL
      AND database_id = DB_ID()
ORDER BY ProcedureName;

--MERGE Data in table in master database
MERGE ManagmentDB.dbo.StoredProcedureUsed AS TARGET
USING #spStats AS SOURCE
ON (
       SOURCE.ProcedureName = TARGET.ProcedureName COLLATE Danish_Norwegian_CI_AS
       AND SOURCE.DatabaseId = TARGET.DatabaseId
   )
WHEN MATCHED THEN
    UPDATE SET TARGET.Last_Execution_Time = SOURCE.last_execution_time,
               TARGET.Execution_Count = SOURCE.execution_count
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
        ProcedureName,
        DatabaseName,
        DatabaseId,
        Last_Execution_Time
    )
    VALUES
    (SOURCE.ProcedureName, SOURCE.DatabaseName, DB_ID(), SOURCE.last_execution_time);

DROP TABLE #spStats;

Please note i my setup i have a COLLATION mismatch to solve this i have the “COLLATE Danish_Norwegian_CI_AS” in my MERGE statement!

To run the query on every database I will use a cursor, only running the query, on the databases I want to monitor, the final StoredProcedure looks like this:

CREATE PROCEDURE dbo.sp_StoredProcedureUsage
AS
BEGIN
  /*Using a cursor,lo eleminate system databases and other databases i dont want to monitor*/
  DECLARE @dbName SYSNAME,
    @dbId INT

  DECLARE cursor_Databases CURSOR FAST_FORWARD READ_ONLY
  FOR
  SELECT name,
    dbid
  FROM master.dbo.sysdatabases
  WHERE dbid > 4 AND name NOT IN (
      'DQS_PROJECTS',
      'SSISDB',
      'DataMigrationAssistant',
      'DQS_MAIN'
      )

  OPEN cursor_Databases

  FETCH NEXT
  FROM cursor_Databases
  INTO @dbName,
    @dbId

  WHILE (@@fetch_status <> - 1)
  BEGIN
    DECLARE @sqlCommand NVARCHAR(MAX) = '
    USE ' + @dbName + 
      ' SET NOCOUNT ON;
        SET NOCOUNT ON;
    DROP TABLE IF EXISTS #spStats  
  
     SELECT DISTINCT
        OBJECT_NAME(P.object_id) AS ''ProcedureName'',
        DB_NAME(database_id) AS ''DatabaseName'',
        DB_ID() AS ''DatabaseId'',
        Last_Execution_Time,
        execution_count  
    INTO #spStats
    FROM sys.dm_exec_procedure_stats AS P
    WHERE   OBJECT_NAME(P.object_id) IS NOT NULL AND 
           database_id = DB_ID()
    Order by ProcedureName

    --MERGE Data in table in master database
    MERGE ManagmentDB.dbo.StoredProcedureUsed AS TARGET
    USING #spStats  AS SOURCE
    ON (SOURCE.ProcedureName = TARGET.ProcedureName  COLLATE Danish_Norwegian_CI_AS AND SOURCE.DatabaseId = TARGET.DatabaseId)
    WHEN Matched THEN
        UPDATE
        SET TARGET.Last_Execution_Time = SOURCE.Last_Execution_Time,
            TARGET.Execution_Count = SOURCE.Execution_count
    WHEN NOT MATCHED BY TARGET THEN
        INSERT ( ProcedureName, DatabaseName, DatabaseId, Last_Execution_Time)
        VALUES ( SOURCE.ProcedureName, SOURCE.DatabaseName,DB_ID(), SOURCE.Last_Execution_Time)      
    ;

    DROP TABLE #spStats  '

    BEGIN TRY
      EXEC sp_executesql @sqlCommand
    END TRY

    BEGIN CATCH
      PRINT ERROR_MESSAGE()
    END CATCH

    FETCH NEXT
    FROM cursor_Databases
    INTO @dbName,
      @dbId
  END

  CLOSE cursor_Databases

  DEALLOCATE cursor_Databases
END

And then the attentive reader would notice the execution count, if data is cleared in the DMV then after SQL Server restart then the count would be less than the saved value in the table, and that is correct, but that is for you to tweak in your query.

Now you can create a SQL Server job running every 5 minute to collect data. When you then cleanup your StoredProcedures you should truncate this table and start over, to get the StoredProcedures who is no longer in use but could be stored in your table.

With this data you could query all stored procedures and show only these not in the StoredProcedureUsed table.

If you have any problems with your Microsoft SQL Server, or SQL Query please don’t hesitate to call me or one of my colleagues at Unit IT phone: 88 333 333