24

This next year, I am helping an effort to clean several SQL Server environments.

We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.

Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called. On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).

One approach to this is to use the SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not. From then, we could move the procedures to a different schema in case a department comes screaming.

Is using the Profiler the most effective approach here? And/Or have any of you done something similar and found another way/better way to do this?

32

You can use server side trace (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis.

Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the data is flushed).

You can even schedule a job to capture DMV data to a table to keep it persisted.

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database 
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

Refer to :

  • 1
    Also see stackoverflow.com/questions/10421439/… and stackoverflow.com/questions/7150900/… (ignoring that on the latter the link to SQLServerPedia is now dead). – Aaron Bertrand Jul 25 '13 at 21:58
  • 2
    Make sure you check the DMV periodically over the course of weeks or even months as there may be SPs that only get run on a monthly or even quarterly basis. DMVs are cleared out when the instance is restarted, cleared manually, or even just over time. – Kenneth Fisher Jul 25 '13 at 22:22
  • 1
    @KennethFisher Thats why I recommended to schedule a job to capture DMV data to a table. Thanks for mentioning though ! – Kin Shah Jul 25 '13 at 23:56
4

As a follow on to Kin's script. Here is a simple script to create a table to track the uses over time & a script to update it periodically.

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  Create the use table 
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE [dbo].[_ProcedureUseLog](
    [ObjectName] [nvarchar](255) NOT NULL,
    [UseCount] [int] NULL,
    [LastUse] [datetime] NULL,
    [LastCache] [datetime] NULL,
 CONSTRAINT [PK___PROCEDURE_USE] PRIMARY KEY CLUSTERED 
(
    [ObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_ProcedureUseLog] ADD  CONSTRAINT [DF_Table_1_References]  DEFAULT ((0)) FOR [UseCount]
GO

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  Run this periodically to update the usage stats
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @UsesTable TABLE
(
    ObjectName nvarchar(255),
    Executions int,
    LastUse datetime,
    LastCache datetime
)

INSERT INTO @UsesTable       
SELECT p.name, qs.execution_count, qs.last_execution_time, qs.cached_time
FROM    sys.procedures AS p LEFT OUTER JOIN
        sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id
WHERE        (p.is_ms_shipped = 0)

MERGE [dbo].[_ProcedureUseLog]      AS [Target]
USING @UsesTable                    AS [Source]
    ON Target.ObjectName = Source.ObjectName
WHEN MATCHED AND 
        ( Target.LastCache <> Source.LastCache)
    THEN UPDATE SET
        Target.UseCount = Target.UseCount + Source.Executions,
        Target.LastCache = Source.LastCache,
        Target.LastUse = Source.LastUse
WHEN NOT MATCHED
    THEN INSERT (ObjectName, UseCount, LastUse, LastCache) 
    VALUES      (ObjectName, Executions, LastUse, LastCache);

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  This just shows what you've logged so far
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM [_ProcedureUseLog] ORDER BY UseCount DESC
0

This post also provides a script to find unused ojects: Find the unused database tables in SQL Server Below is the script from the article, I changed table type "U" into stored procedure type "P":

   USE DBName;
   SELECT 

       ao.[name] [Table],
       s.[name] [Schema],
       [create_date] [Created],
        [modify_date] [LastModified]
    FROM
         sys.all_objects ao JOIN sys.schemas s
           ON ao.schema_id = s.schema_id
    WHERE
         OBJECT_ID NOT IN (
              SELECT OBJECT_ID
              FROM sys.dm_db_index_usage_stats
        )
        AND [type] = 'P'
    ORDER BY
        [modify_date] DESC
  • This will always return all procedures as procedures don't get entries made in the index usage stats DMV... – Martin Smith Jun 1 '14 at 16:32
11

You can find this question useful, it applies to tables and columns but suggests using a third party tool ApexSQL Clean which can also find unused stored procedures as well as all objects that are not referenced by any other object in database, or in external databases

Disclaimer: I work for ApexSQL as a Support Engineer

  • 3
    The OP does not want to find unreferenced stored procedures, instead OP want to find unused SP. Your answer does not serve as an answer for this question. – Kin Shah Jan 23 '14 at 13:09
  • Kin i'll update. ApexSQL Clean marks unused objects as unreferenced so I understand that that caused the confusion – Milica Medic Jan 23 '14 at 13:52
10

If you are on SQL Server 2008+ you can also use extended events with a histogram target. Possibly this would be more light weight than a trace.

AFAIK you would need to create a different session for each database of interest though as I couldn't see any indication that bucketizing on multiple columns was possible. The quick example below filters on database_id=10

CREATE EVENT SESSION [count_module_start_database_10]
ON SERVER
ADD EVENT sqlserver.module_start
(  
        WHERE (source_database_id=10) 
)
ADD TARGET package0.asynchronous_bucketizer
(     SET  filtering_event_name='sqlserver.module_start', 
            source_type=0, 
            source='object_id',
            slots = 10000
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO
ALTER EVENT SESSION [count_module_start_database_10]
ON SERVER
STATE=START

And then after running some stored procedures in that DB a few times and retrieving the data with

SELECT CAST(target_data as XML) target_data
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
WHERE s.name = 'count_module_start_database_10'

The output is

<HistogramTarget truncated="0" buckets="16384">
  <Slot count="36">
    <value>1287675635</value>
  </Slot>
  <Slot count="3">
    <value>1271675578</value>
  </Slot>
  <Slot count="2">
    <value>1255675521</value>
  </Slot>
</HistogramTarget>

Showing that the procedure with object_id of 1287675635 was executed 36 times for example. The asynchronous_bucketizer is memory only so it would be best to set up something that polls this every so often and saves to persistent storage.

  • 1
    It is true, you need one session per database. Would be great to say WHERE (source_database_id IN (10,15,20)) but alas this is not supported. – Aaron Bertrand Jul 26 '13 at 0:10
  • @AaronBertrand - And even if it was supported you would still need to count procedure calls for objects with the same object_id (or same object_name) in different databases separately and I don't think that is possible either. – Martin Smith Jul 26 '13 at 7:14
  • Correct me if im wrong but extended events where added in 2012 not 2008? – Peter Feb 7 '17 at 8:48
  • 1
    @Peter yep you're wrong. :-) technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx – Martin Smith Feb 7 '17 at 20:27
  • 1
    The extended events UI wasn't introduced until SSMS 2012 and I don't think they made it backwards compatible. On 2008 the only way to create sessions out the box was through TSQL though there was a community project for similar functionality extendedeventmanager.codeplex.com – Martin Smith Feb 8 '17 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.