Collect all used SQL Server objects

Question

We have a large database with around 5000 objects (tables, views, sql functions, stored procedures etc.). A large portion of those objects are not in use anymore - but no-one exactly knows which ones. So we keep maintaining those old objects without knowing if someone still uses them.

We wanted to use Extended Events to trace the objects actually in use. Essentially we use the following query to collect an event whenever a database object is accessed. the events are stored in *.xel files.

CREATE EVENT SESSION {mySession} ON SERVER
            ADD EVENT sqlserver.lock_acquired (
                SET collect_database_name = (0)
                    ,collect_resource_description = (1)
                ACTION(sqlserver.client_hostname, sqlserver.client_app_name, collect_system_time, database_id)
                WHERE (
                    [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
                    AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
                    AND [package0].[equal_uint64]([database_id], {dbId}) -- user database
                    AND [package0].[greater_than_equal_int64]([object_id], ({minimalUserObjectId})) -- user object
                    AND ([mode] = (1)-- SCH - S
                         OR [mode] = (6)-- IS
                         OR [mode] = (8)-- IX
                         OR [mode] = (3)-- S
                         OR [mode] = (5)-- X
                         )

                )
            )
            ADD TARGET [package0].event_file
            (
                SET filename='{xelFile}',
                    max_file_size=25,
                    max_rollover_files=5000
            )
            WITH (
                 MAX_MEMORY = 25 MB
                ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
                ,MAX_DISPATCH_LATENCY = {MaxDispatchLatency} SECONDS
                ,MEMORY_PARTITION_MODE = PER_NODE
                ,TRACK_CAUSALITY = OFF
                ,STARTUP_STATE = OFF
            );

We then wrote a separate program which periodically reads those *.xel files, aggretates the values and stores them in a *.xlsx file.

This prinicpally works. However, on the production database, there is an enormous amount of data collected (the *.xel files will grow to about 300 GB in size - per day!). Also, SQL server seems to use up a lot of resources for collecting the events - frequently, users cannot connect to the database or cannot run very simple queries because they get a timeout.

Is there any other - less resource intensive - way to collect usage data of db objects?

Regardless of the solution you choose here I want to caution you. This type of thing often proceeds dropping the objects that "are no longer used". You need to keep in mind that there are some processes that only get run once a year or sometimes even longer in between. It also seems that your current should be workable. You just need to add some filtering to start eliminating the objects you know are valid already. No point in continuing to track objects you know are used. — Sean Lange, Sep 22 '17 at 18:05
Drop the objects one at a time and see if anybody calls to complain that something is broken. — Tab Alleman, Sep 22 '17 at 18:17
@SeanLange yes I'm aware of this. we planned to let the event capture run for at least one year. — Urs Meili, Sep 22 '17 at 20:09
@TabAlleman that's a sure way to be in the newspapers the next day. This isn't just a small company with 2 employees... — Urs Meili, Sep 22 '17 at 20:15
@UrsMeili just curious, for this, what did you wind up doing? Thanks! — mbourgon, Feb 10 '22 at 18:01

score 0 · Answer 1 · answered Sep 22 '17 at 18:11

0

In case if you are looking if table definition is changed, then use

SELECT * FROM sys.tables order by modify_date desc

In case of if any data is updated in a table

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')

-- Finding unused stored procedures

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT s.name, s.type_desc
FROM sys.procedures s
LEFT OUTER JOIN sys.dm_exec_procedure_stats d
        ON s.object_id = d.object_id
WHERE d.object_id IS NULL
ORDER BY s.name

If SQL server 2016 then use for UDF

SELECT * FROM sys.dm_exec_function_stats

answered Sep 22 '17 at 18:11

Rex

521
3
8

doesn't dm_db_index_usage_stats only update when data in the table is updated that is covered by at least one index? – Urs Meili Sep 22 '17 at 20:11
The documentation for dm_exec_procedure_stats (https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql) says that the stats are cleared as soon as the procedure is no longer in the query cache. That of course dosn't mean the procedure hasn't been used at all, just that it is not used right at this moment. – Urs Meili Sep 22 '17 at 20:20

score 0 · Answer 2 · answered Sep 22 '17 at 19:29

0

Try this

SELECT 
  [schema] = s.name, 
  [object] = o.name,
  o.type_desc
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ('P','U');

answered Sep 22 '17 at 19:29

nitin.sharma0180

471
3
6

this just gives me the names of some objects, not their usage – Urs Meili Sep 22 '17 at 20:12

Collect all used SQL Server objects

2 Answers2