September 12, 2018 at 5:34 am
Dear Experts,
I generated a list of unused indexes from my database by equating User_scans, user_seeks , and user_lookups to zero. Though I got the data , I could see that some of them have Index Name as NULL . Can anybody share the reason for Index names not appearing for some . Any similar experiences ? Thank you in advance.
Best Regards
Arshad
September 12, 2018 at 7:10 am
Arsh - Wednesday, September 12, 2018 5:34 AMDear Experts,
I generated a list of unused indexes from my database by equating User_scans, user_seeks , and user_lookups to zero. Though I got the data , I could see that some of them have Index Name as NULL . Can anybody share the reason for Index names not appearing for some . Any similar experiences ? Thank you in advance.Best Regards
Arshad
I would guess your query pulls the name from sys.Indexes and name will appear as Null when indid = 0. The table is a heap, there is no index.
You could add type or type_desc to your query if you want to differentiate those.
Sue
September 12, 2018 at 8:23 am
In addition to identifying the heaps via the NULL index name, if you're using sys.dm_db_index_usage_stats it won't include information for anything that hasn't been touched at all, which can be confusing if you don't see tables there when you expect to.
If you're not already accounting for it, the below script returns info for everything:SELECT * from (
SELECT
u.object_id
,[SchemaName] = OBJECT_SCHEMA_NAME(u.object_id)
,[TableName] = OBJECT_NAME(u.object_id)
,[IndexName] = CASE WHEN u.index_id = 0 THEN 'HEAP' ELSE i.name END
,[Reads] = SUM(user_seeks + user_scans + user_lookups)
,[Writes] = SUM(user_updates)
FROM sys.dm_db_index_usage_stats u
JOIN sys.indexes i
on u.index_id = i.index_id
and u.object_id = i.object_id
WHERE u.database_id = DB_ID()
AND u.object_id > 100
group by database_id, u.object_id, u.index_id, i.name
UNION ALL
SELECT
o.object_id
,OBJECT_SCHEMA_NAME(o.object_id)
,object_name(o.object_id)
,CASE WHEN i.index_id = 0 THEN 'HEAP' ELSE i.name END
,0
,0
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type_desc in ('user_table')
and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id
and i.index_id=s.index_id and database_id = db_id(db_name()) )
) as tableUsage
order by reads, writes
September 16, 2018 at 4:31 am
Best Regards
Arshad
September 16, 2018 at 9:54 am
Arsh - Sunday, September 16, 2018 4:31 AMThank you Sue . Thank you to you too SQLPirate ; and Yes I am usage dm_db_usage_stats . Your query gets everything and that's useful as well.Best Regards
Arshad
The trouble is that view resets to being empty when the SQL Server Service is restarted. Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.
There are also indexes associated with certain constraints that may never see user usage. For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned. If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2018 at 1:53 am
Jeff Moden - Sunday, September 16, 2018 9:54 AMThe trouble is that view resets to being empty when the SQL Server Service is restarted. Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.There are also indexes associated with certain constraints that may never see user usage. For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned. If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.
Yes Jeff , that's a really good point . I'll have an exception list probably based on such important points and a full cycle end to end testing would provide a better picture. Nonetheless , the duplicate indexes are definitely potential candidates to do away with . Thank you for the point put forth.
regards..Arshad
September 17, 2018 at 9:17 am
Arsh - Monday, September 17, 2018 1:53 AMJeff Moden - Sunday, September 16, 2018 9:54 AMThe trouble is that view resets to being empty when the SQL Server Service is restarted. Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.There are also indexes associated with certain constraints that may never see user usage. For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned. If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.
Yes Jeff , that's a really good point . I'll have an exception list probably based on such important points and a full cycle end to end testing would provide a better picture. Nonetheless , the duplicate indexes are definitely potential candidates to do away with . Thank you for the point put forth.
regards..Arshad
Be careful even with that, Arsh. I've seen people drop NCIs that were an exact duplicate of the Clustered Index key(s) and it crushed performance in many areas because the NCI was a whole lot more narrow at the leaf level than the CI. I even have a presentation with code that demonstrates the huge performance increase thanks to an NCI that duplicated the CI key.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply