Trying to find all databases not in using sys.dm_db_index_usage_stats

  • I am at a new company that has many SQL Servers with hundreds of databases on them. It is suspected that many of these databases are no longer used.    I found the below query to run but don't know how to code it to loop through all of the databases with a print command to tell me which databases this applies to.   Does anyone have any idea how to show which db the output applies to or know of another SQL method of finding which databases may not have any last read or update stats for?

     

     

    DECLARE @command varchar(1000)

    SELECT @command =

    '

    ;WITH agg AS

    (

    SELECT

    last_user_seek,

    last_user_scan,

    last_user_lookup,

    last_user_update

    FROM

    sys.dm_db_index_usage_stats

    WHERE

    database_id = DB_ID()

    )

    SELECT

    last_read = MAX(last_read),

    last_write = MAX(last_write)

    FROM

    (

    SELECT last_user_seek, NULL FROM agg

    UNION ALL

    SELECT last_user_scan, NULL FROM agg

    UNION ALL

    SELECT last_user_lookup, NULL FROM agg

    UNION ALL

    SELECT NULL, last_user_update FROM agg

    ) AS x (last_read, last_write);'

    EXEC sp_MSforeachdb @command

  • sys.dm_db_index_usage_stats already has all the index usage for all databases and all tables inside it, so you can get all the Databases all at once. no need for a loop or cursor in this case.

    Since index stats get reset each time the SQL service is stopped and started, it's important to know how long it has been, so you don't think you scan delete a database that is not used much.

    here's a simple version:

    SELECT
    ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
    DB_NAME([statz].[database_id]) AS DatabaseName,
    MAX([statz].[last_user_seek]) AS MaxSeek,
    MAX([statz].[last_user_scan]) AS MaxScan,
    MAX([statz].[last_user_lookup]) AS maxLookup,
    MAX([statz].[last_user_update]) AS MaxUpdate
    FROM
    [sys].[dm_db_index_usage_stats] [statz]

    GROUP BY DB_NAME([statz].[database_id])

    • This reply was modified 4 years, 4 months ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Great query.  Question though.   What is the difference between the date/timestamp for a database of the server restart versus NULL though?

     

    2020-07-17 20:47:46.113 DB_AX_20190724 2020-07-17 20:47:55.650 NULL 2020-07-17 20:47:55.550 NULL

    2020-07-17 20:47:46.113 DB_AX2 2020-07-17 20:47:59.580 NULL NULL NULL

  • Lowell wrote:

    sys.dm_db_index_usage_stats already has all the index usage for all databases and all tables inside it..

    Careful now.  If, for example, any database is totally unused, it my not show up in the DMV at all.  And, for sure, if a table had not been used since the last restart, it will also not show up in the DMV.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... serendipity strikes.  There's a pretty good article on all of this that was in an article in yesterday's MSSQLTips email.  The link to the article I'm talking about is https://www.mssqltips.com/sqlservertip/1545/deeper-insight-into-used-and-unused-indexes-for-sql-server/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • That is all fine.  But with some of our servers having 500+ databases on them that is going to be way too hard to interpret the output.  I simply need a query that loops through each db and gives me a list of dbs NOT accessed.

  • Yea, that is the problem with this one.  It only lists the dbs since last server restart that have been accessed, I want an opposite list.  In a SQL Server with over 500 databases attempting to figure out which ones were not accessed is the issue I am attempting to resolve.

  • Summer90 wrote:

    Yea, that is the problem with this one.  It only lists the dbs since last server restart that have been accessed, I want an opposite list.  In a SQL Server with over 500 databases attempting to figure out which ones were not accessed is the issue I am attempting to resolve.

    You can solve that problem by generating some dynamic SQL that will scan all databases to create a view that will list (from sys.indexes on each database) all indexes and heaps.  Then left join the usage DMV to that and search for where NULLs appear in the usage DMV.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff but that is above my SQL coding abilities.

  • well, for items that are not in index stats at all, you could use something like this:

    SELECT name 
    FROM sys.[databases]
    WHERE [database_id] NOT IN(
    SELECT database_id FROM [sys].[dm_db_index_usage_stats]
    )

    and combine both my ideas together for a fuller picture:

     

    SELECT   ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
    DB_NAME([database_id]) AS DatabaseName,
    CONVERT(DATETIME,NULL) AS MaxSeek,
    CONVERT(DATETIME,NULL) AS MaxScan,
    CONVERT(DATETIME,NULL) AS maxLookup,
    CONVERT(DATETIME,NULL) AS MaxUpdate
    FROM sys.[databases]
    WHERE [database_id] NOT IN(
    SELECT database_id FROM [sys].[dm_db_index_usage_stats]
    )
    UNION ALL
    SELECT
    ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
    DB_NAME([statz].[database_id]) AS DatabaseName,
    MAX([statz].[last_user_seek]) AS MaxSeek,
    MAX([statz].[last_user_scan]) AS MaxScan,
    MAX([statz].[last_user_lookup]) AS maxLookup,
    MAX([statz].[last_user_update]) AS MaxUpdate
    FROM
    [sys].[dm_db_index_usage_stats] [statz]

    GROUP BY DB_NAME([statz].[database_id])

     

    • This reply was modified 4 years, 4 months ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell.   THANK YOU.   I was overthinking it.  Why didn't I think of your first one for the NOT IN.  But thanks for the second one as well.  That shows the entire picture.

  • Summer90 wrote:

    Lowell.   THANK YOU.   I was overthinking it.  Why didn't I think of your first one for the NOT IN.  But thanks for the second one as well.  That shows the entire picture.

    Just a couple of words of caution... if someone bounces the SQL Server service or the whole box, the sys.dm_db_index_usage_stats clears all counts.  If you have a database that is infrequently used, you may be looking at dropping a database that's still used.

    On the other hand, if the system uses an index in a database, it may show in sys.dm_db_index_stats where no one and no other thing has used the database and you could still end up keeping databases that aren't actually used by apps or people or jobs, etc (what I believe you're calling "Unused").

    On the first where you believe no one is using a particular database, I'd take a full backup and then immediately take a "Tail Log" backup to take it offline while preserving the last instant of any and all activity.  I wouldn't drop the database for a good 3 months just in case it's one of those rare databases only used once per quarter.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh yes that is the plan.  Identify, communicate to everyone the lists of databases, back them up to a non expiring place, OFFLINE them for 2-3 months then drop them.  Keep backups for a year.

  • Sounds like an excellent plan.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply