Sql-server – SQL 2005 Unused procedures

sql serversql-server-2005stored-procedurest-sql

I'm trying to get a list of unused procedures across the whole SQL server. I've accomplished getting a list of ALL procedures and now I'm using the following script to identify which procedures have been used.

DECLARE @command varchar(1000) 
SELECT @command = 

'USE [?] SELECT

qt.text AS "SP Name", 
qs.execution_count AS "Execution Count", 
qs.max_logical_reads, 
qs.max_logical_writes, 
qs.creation_time,
DB_NAME() as DatabaseName,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS "Age in Cache"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qt.text DESC'

EXEC sp_MSforeachdb @command

I'm aware that this will only show procedures which have been used post restart. The data seems to work somewhat – however; the issue i'm having is that it seems to show the WHOLE procedures text IE:

CREATE PROCEDURE dbo.proc_MSS_PropagationGetQueryServers AS
DECLARE @Temp table( ServerID int, SearchServerName
nvarchar(31), IndexDirectory nvarchar(260),
MachineStatus int, LastPropagationTime datetime,
PropagationStatus int ) INSERT INTO @Temp
SELECT SearchServerID, SearchServerName,
IndexDirectory, MachineStatus, LastPropagationTime,
1 AS PropagationStatus FROM
dbo.MSSPropagationSearchServerTable UPDATE @Temp
SET PropagationStatus = 0 WHERE NOT EXISTS
(SELECT * FROM dbo.MSSPropagationSearchServerReady AS R WHERE ServerID
= R.SearchServerID) AND (1 < DATEDIFF(m, LastPropagationTime, GETUTCDATE()) OR 30 < DATEDIFF(s, LastPropagationTime,
GETUTCDATE())) CREATE TABLE #UnresponsiveQueryServers (
ServerID int ) INSERT #UnresponsiveQueryServers EXEC
dbo.proc_MSS_PropagationGetUnresponsiveQueryServers UPDATE
@Temp SET PropagationStatus = 3 WHERE
ServerID IN ( SELECT ServerID FROM #UnresponsiveQueryServers )
UPDATE @Temp SET PropagationStatus = 2
WHERE MachineStatus = 0 OR MachineStatus = 1 OR
MachineStatus = 2 SELECT SearchServerName,
IndexDirectory, MachineStatus, PropagationStatus
FROM @Temp

I just want the name of the procedure itself. So in this case proc_MSS_PropagationGetQueryServers

Anyone able to help on this?

Best Answer

What you are looking for is not really possible, at least not in the spirit of what you are ultimately trying to accomplish. As I explained in my answer on what is pretty much a duplicate question -- Query last time a view, funcion or sp was last used/read -- this approach doesn't take into account infrequently accessed code paths in the app code and "support" code that is executed manually, usually in SSMS. It sucks, but you really just need to do an exhaustive review of all code that could be executing Stored Procedures, as well as support tickets that include instructions on what to run in case of X, or even internal wiki pages that might have instructions indicating to execute Stored Procedures (or use UDFs, etc).

Also, you can create a table to log usage, and for those Stored Procedures that you believe are "unused", add a line at the top to insert into this log table. Check that log table periodically, and set a cut-off time with your team / department where everyone feels comfortable getting rid of that code if it hasn't been accessed within that amount of time.

P.S. I have gone through this process before. And I have had support people call me (or my manager) shortly after a release because I had dropped some code that was not referenced in any other code or internal wiki page, etc (and believe you me, I was quite thorough in my research!). BUT, it was being used in a Microsoft Access app that a support engineer created years prior to make certain support tasks / requests take less time (i.e. not needing to wait 2 weeks for a DBA to get around to running the Stored Proc so that a customer could do what they needed to do). And since none of us database engineers was aware of this app even existing (it was only on a few desktops in the support area, and certainly not in SVN), it made it a bit difficult to take into account.

P.P.S. regarding this statement:

I'm aware that this will only show procedures which have been used post restart.

That is the most that can be shown. But, a restart is not the only way to clear cached system data. Either of the following commands will also clear that data:

DBCC FREEPROCCACHE;

DBCC FREESYSTEMCACHE('ALL');

AND, SQL Server will periodically dump the cached data when there is memory pressure (i.e. low available physical memory).

Hence, you cannot use the output of any of the DMVs / DMFs for this purpose.

P.P.P.S. The sys.dm_exec_query_stats DMV works at the server / instance level, not the database level. Meaning, not only do you not need to wrap such queries in sp_MSforeachdb, but you don't want to do that anyway since the same result set will get repeated per each of your databases.

P.P.P.P.S. The sys.dm_exec_sql_text DMF that you are already using conveniently returns columns for objectid and dbid. The dbid column can be used along with the DB_NAME() built-in function that you are already using to get the "current" database name for that query. And, both the dbid and objectid columns can be used along with the OBJECT_NAME() built-in function to get the name of the stored procedure, relative to that "current" database (as opposed to the database that you are running this query in, which can return erroneous values since the objects are potentially not in that database). And then both of those functions can be used again in the ORDER BY clause.

Taking all of that info into account, the re-written query is:

SELECT  DB_NAME(qt.[dbid]) AS [DatabaseName],
        OBJECT_NAME(qt.[objectid], qt.[dbid]) AS [SP Name],
        qs.execution_count AS [Execution Count],
        qs.max_logical_reads,
        qs.max_logical_writes,
        qs.creation_time,
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
ORDER BY DB_NAME(qt.[dbid]) DESC,
         OBJECT_NAME(qt.[objectid], qt.[dbid]) DESC;

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Create Temp Table From Stored Procedure Result

See following question from stack overflow. According to this question you can use

CREATE PROCEDURE getDatabaseList
AS
BEGIN
    SELECT * FROM sys.databases
END

 SELECT * INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table

Best Answer

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Sql-server – Create Temp Table From Stored Procedure Result

Related Question