August 15, 2024 at 9:52 pm
SELECT
DB_Name() As CurrentDatabase,
objects.name AS Table_name,
indexes.name AS Index_name,
SUM(dm_db_index_usage_stats.user_seeks) as UserSeeks,
SUM(dm_db_index_usage_stats.user_scans) as UserScans,
SUM(dm_db_index_usage_stats.user_updates) as UserUpdates,
GETDATE() as Createdttm
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE dm_db_index_usage_stats.user_lookups = 0
ANDdm_db_index_usage_stats.user_seeks < 1
ANDdm_db_index_usage_stats.user_scans < 1
AND indexes.name IS NOT NULL
GROUP BY
objects.name,
indexes.name
Running the above query gives you unused indexes within a specific database. By msdn -
Running the above query gives you unused indexes within a specific database. By msdn -
user_updates - bigint - Number of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1
User updates should happen when you have data and code is doing some CRUD. I am showing index updates many that do not have any data, and have not had data ever. Should I be using this query and also be looking at the tables to make sure they have data? Why would it show and update when the table has never had data?
August 15, 2024 at 10:32 pm
If you issued an UPDATE against an empty table, the UPDATE counter would still increment.
Maybe that's what happened?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 16, 2024 at 3:55 pm
That is possible. Our stored procedures are not called systematically and the parameters are probably not checked for no data. I guess just ignore those empty tables.
August 19, 2024 at 4:31 am
This won't tell you if a table is "empty" or not but it will tell you if it has been used by users since the last restart or not. I wouldn't use this to drop a table, though... you could have quarterly or annual tables or reference tables that only the system uses. Also, empty tables are sometimes the target of INSTEAD OF triggers.
As a bit of a side bar, you might want to bone up on some of the "OBJECT" related functions, start using aliases for improved readability and simplicity, and spend a bit more time aligning your code so that when you come back to it a year later, you don't have to spend so much time trying to read what it does.
WITH cteGetUsage AS
(
SELECT CurrentDatabase = DB_NAME()
,SchemaName = OBJECT_SCHEMA_NAME(idx.object_id)
,TableName = OBJECT_NAME(idx.object_id)
,IndexName = idx.name
,IndexType = idx.type_desc
,UserSeeks = ISNULL(sta.user_seeks ,-1) --The "-1" means not used since last restart
,UserScans = ISNULL(sta.user_scans ,-1) --The "-1" means not used since last restart
,UserUpdates = ISNULL(sta.user_updates,-1) --The "-1" means not used since last restart
,Createdttm = GETDATE()
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats sta ON idx.object_id = sta.object_id
AND idx.index_id = sta.index_id
)
SELECT usg.*
FROM cteGetUsage usg
--WHERE usg.UserSeeks < 1 --Uncomment the WHERE/ANDs to see only user unused Indexes/Tables/Heaps.
-- AND usg.UserScans < 1
-- AND usg.UserUpdates < 1
ORDER BY SchemaName, TableName, IndexName
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply