November 26, 2022 at 2:31 pm
I'm using this query to check if I have any unused indexes.. One of the tables it display's has lots of user_updates but the indexname field is NULL...
There is a non-clustered index on the table No fill factor and page fullness about 50% and 98% fragmented. Min row size 42 and max 74 avg 53.
Why did it come back with Null Index Name?
Thx.
SELECT
objects.name AS Table_name,
indexes.name AS Index_name,
dm_db_index_usage_stats.user_seeks,
dm_db_index_usage_stats.user_scans,
dm_db_index_usage_stats.user_updates
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
AND
dm_db_index_usage_stats.user_seeks = 0
AND
dm_db_index_usage_stats.user_scans = 0
ORDER BY
dm_db_index_usage_stats.user_updates DESC
November 26, 2022 at 3:02 pm
if you bother to look at the results of the index definition you will discover why its null.
and get used to use alias on the tables - makes your life easier typing as well as reading the sql statements.
November 27, 2022 at 4:08 am
"Learning by example" opportunity here... spend some time looking things up on the web, as suggested in the code below.
And, like Frederico suggests, learn how to use "Object Aliases" and use them properly. They make code much easier to read and understand. They're also essential, in some areas. As a rule of thumb for me, I only use 2 part naming conventions. Using 3 part naming, like what would be required for columns from "sys" objects, has been deprecated as of SQL Server 2016. Search for "Three-part and four-part column references" at the following link (and you should search for such an article for each version of SQL Server that comes out and study it)...
The following code will also let you know why some "indexes" have no names, like Frederico said.
--===== Please snuggle up with the documentation!
-- Do a search for things like sys.inexes, sys.objects, the names of the functions, etc, etc.
-- Notice the use of the idx, obj, and usg "Object Aliases". THAT'S WHAT FREDERICO WAS TALKING ABOUT.
--===== Warning... sys.dm_db_index_usage_stats is totally cleared out on startup.
-- Warning... Indexes not used by users might still be evaluated by the optimizer. Don't just up and delete them.
-- Warning... Never delete indexes until you know for sure. Instead, disable them, which will NOT drop their definition.
-- Warning... It's your funeral if you don't read'n'heed the warnings above and below! ;)
WITH cteFindAll AS
(--==== This finds all indexes, even those not used since startup, which will NOT appear in sys.dm_db_index_usage_stats.
SELECT Schema_Name = SCHEMA_NAME(obj.schema_id) --I added this... you're going to need it.
,Table_Name = obj.name
,Index_Name = idx.name
,Index_Type = idx.type_desc
,User_Seeks = ISNULL(usg.user_seeks ,-1) --ISNULL() to find objects/identify not used since startup.
,User_Scans = ISNULL(usg.user_scans ,-1) --ISNULL() to find objects/identify not used since startup.
,User_Updates = ISNULL(usg.user_updates,-1) --ISNULL() to find objects/identify not used since startup.
,Is_Unique = idx.is_unique --Warning! I would NEVER disable/drop a unique keyed index unless you could absolutely prove it's not being used.
,Is_Disabled = idx.is_disabled
,Object_ID = idx.object_id
,Index_ID = idx.index_id
FROM sys.indexes idx
JOIN sys.objects obj ON obj.object_id = idx.object_id
LEFT JOIN sys.dm_db_index_usage_stats usg ON usg.OBJECT_ID = idx.OBJECT_ID --LEFT JOIN finds indexes not used since startup
AND usg.index_id = idx.index_id
WHERE idx.is_hypothetical = 0 --Warning! These indexes are special system "indexes". Don't worry about them!
AND obj.schema_id <> SCHEMA_ID('sys') --Comment this out to test... you might find some sys objects not used.
)--==== Report our findings even for indexes that haven't been used since startup.
SELECT *
FROM cteFindAll
WHERE User_seeks < 1 --Warning... Just because an index isn't used by users, it could be used by the system.
AND User_scans < 1 --We look for items < 1 so both 0 and -1 objects from the CTE will be found.
AND User_updates < 1 --Warning... Remember that "Reference" tables might NEVER be updated and only be used on occasion.
ORDER BY SIGN(Index_ID), Index_Type, Schema_Name, Index_Name
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2022 at 7:45 am
Jeff has pointed out some things and posted much the same query as I have used.
You definitely need a left join on sys.dm_db_index_usage_stats if you want to find all unused indexes. You only get a row in this view if the index has been accessed (for any reason) since the last SQL restart. Therefore you will find totally unused indexes have null values in the fields derived from sys.dm_...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 7, 2022 at 9:15 pm
Thanks for post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply