Trying to find all databases not in using sys.dm_db_index_usage_stats

Question

Trying to find all databases not in using sys.dm_db_index_usage_stats

Summer90

SSC-Dedicated

Points: 32891
More actions
July 30, 2020 at 7:53 pm

#3776111

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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, 3 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!

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

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.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 8

Thanks Jeff but that is above my SQL coding abilities.

Lowell SSC Guru Points: 323518 More actions · Answer 9

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, 3 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!

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

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)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 12

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

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)