Unused indexes

Question

Post reply

Unused indexes

christi1711

SSC-Addicted

Points: 441

January 8, 2021 at 3:54 pm

#3830033

Hi all,

I used such script for finding unused indexes in my DB:

Hi all,
I used such script for finding unused indexes in my DB:

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
,IX.name AS Index_Name
--,IX.type_desc Index_Type
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,IXUS.user_seeks AS NumOfSeeks
,IXUS.user_scans AS NumOfScans
,IXUS.user_lookups AS NumOfLookups
,IXUS.user_updates AS NumOfUpdates
,IXUS.last_system_update
,IXUS.last_user_update AS LastUpdate
,IXUS.last_user_seek AS LastSeek
,IXUS.last_user_scan AS LastScan
,IXUS.last_user_lookup AS LastLookup 
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
AND IXUS.user_lookups = 0
AND IXUS.user_seeks = 0
AND IXUS.user_scans = 0
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_system_update ,IXUS.last_user_update, IXUS.last_user_seek
,IXUS.last_user_scan
,IXUS.last_user_lookup
ORDER BY IXUS.user_updates DESC

It was interesting for which period I received such information.

I read that this information updated each time when SQL Server is restarting.

I used such a script for identifying when my SQL Server was last time restart:

SELECT sqlserver_start_time 
FROM sys.dm_os_sys_info

I saw that my SQL Server was restarted in November 2020 (last time).

But when I execute the first script each day I received different indexes. Who can explain to me why?

For what period is the information in the view sys.dm_db_index_usage_stats relevant?

This topic was modified 3 years, 10 months ago by christi1711.
This topic was modified 3 years, 10 months ago by christi1711.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Quick google brought me to Microsoft's website on that view and to quote them:

Returns counts of different types of index operations and the time each type of operation was last performed.

(source - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver15)

So, dm_db_index_usage_stats gives you statistics about the last time that index was used.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Each restart, yes, but also failovers, detach/attach, all sorts of variation on that data. It is the one place to try to determine if indexes are in use, but, it's also possibly wrong. So if you do remove indexes based on the information there, be sure you have them backed up in source control so you can reapply a given index as needed, if needed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Grant Fritchey wrote:

Each restart, yes, but also failovers, detach/attach, all sorts of variation on that data. It is the one place to try to determine if indexes are in use, but, it's also possibly wrong. So if you do remove indexes based on the information there, be sure you have them backed up in source control so you can reapply a given index as needed, if needed.

e

I'll add to that great idea... don't initially delete and index. Instead, simply disable it. That, way, a quick rebuild will enable it if the proverbial poo hits the fan. Keep a record of when indexes were disabled (a DDL trigger would automate that process) and only drop them after some comfortable fixed amount of time has passed. Even that can still be an error so I very much agree with Grant... the index should be in easily findable source control.

--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)

christi1711 SSC-Addicted Points: 441 More actions · Answer 4

@Mr. Brian Gale, @Grant Fritchey, @jeff Moden

thanks a lot.

1. Can I conclude about the appropriateness of the index based on scans, seeks and lookups in this table?

2. Do I always need to have a clustered index in the table? For example, I have a table with very frequent inserts and there is high fragmentation. Is it advisable to create an index on such a table?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Generally, yes. If the index is in active use, it's probably needed.
The rule I follow is pretty simple. Unless I can absolutely prove otherwise, through very thorough testing, every table ought to have an appropriately chosen clustered index. Tons of work has been put into ensuring the performance of clustered indexes. It is the preferred storage mechanism within SQL Server (except, of course, for Clustered Columnstore, but different discussion).

One other point, it is possible to have a unique constraint (which will be a unique index, it's just how that works), that is used by the optimizer, but, never used by your queries and therefore not visible in the DMV. This is because if the optimizer knows a given table has unique values, even though it may not choose that index, that index affects other choices, such as join method, etc.. This is rare, and unique to unique indexes. Still, I'd always be more cautious when it comes to those.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

christi1711 wrote:

Hi all, I used such script for finding unused indexes in my DB:
It was interesting for which period I received such information. I read that this information updated each time when SQL Server is restarting.
I used such a script for identifying when my SQL Server was last time restart:
I saw that my SQL Server was restarted in November 2020 (last time). But when I execute the first script each day I received different indexes. Who can explain to me why?
For what period is the information in the view sys.dm_db_index_usage_stats relevant?

You could run it regularly and save the results into a table, with an additional column containing GETDATE() if you want a history to refer to later

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

christi1711 wrote:

For example, I have a table with very frequent inserts and there is high fragmentation. Is it advisable to create an index on such a table?

Can you post the CREATE TABLE statement along with the CREATE INDEX statements for any indexes created on the that table?

--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)

christi1711 SSC-Addicted Points: 441 More actions · Answer 8

Sure.

I have separate scripts for creating table and for creating indexes.

SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [IX_User_LastName] ON [dbo].[User]
(
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

christi1711 SSC-Addicted Points: 441 More actions · Answer 9

Does "index disabling" blocks a table?

Do we have any constraints regarding it?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 10

christi1711 wrote:

Sure. I have separate scripts for creating table and for creating indexes.

SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [IX_User_LastName] ON [dbo].[User]
(
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The CREATE TABLE code appears to be missing from that. If what you say is true, the PK and the Clustered Index (not necessarily the same) appears to be missing from the mix.

--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: 1004343 More actions · Answer 11

christi1711 wrote:

Does "index disabling" blocks a table? Do we have any constraints regarding it?

No... disabling the index doesn't substantially block the table except for the time it takes to disable the index, which is quite short for most places where I've seen it done.

Yes, there are some possibly serious constraints when it comes to either dropping or disabling an index.

First, you should never drop a Clustered Index. It will cause a table to revert to a heap and any Non-Clustered Indexes will automatically be rebuilt whether you want them to be or not. Rebuilding the Clustered Index from a heap will cause the same issue. The Clustered Index is frequently the target of any FKs, as well.

Speaking of FKs, you probably shouldn't drop or disable any Non-Clustered index that is unique unless you've checked to see if any FKs are pointing to it and have made arrangements to handle that interaction. It may mean rebuilding FKs if you go that route.

When you REBUILD any index, it will paralyze the underlying table during the REBUILD unless you use the WITH (ONLINE = ON) option, which has it's own set of caveats including but not limited to some seriously massive log file entries and not doing the rebuild quite as tightly as doing an OFFLINE rebuild, which is also quite a bit faster (~350ms for a half-Gig index on my laptop for OFFLINE, almost 9 seconds for ONLINE). It may be worth it, though. "It Depends". One good tradeoff is to do OFFLINE rebuilds for such a small index, wait a couple of seconds, and then do the next one if in a 24/7 environment but you do need to check the effect to make sure it's not going to kill your online customers especially if the table has a high hit ratio.

For me, REORGANIZE simply isn't an option because, although it follows the Fill Factor, it's not capable of creating new pages to clear the area above the Fill Factor, which can be essential to preventing page-splits that cause blocking and the fragmentation caused by the page splits.

A vivid example for the problems associated with REORGANIZE can be found in Random GUID keyed indexes. Almost everyone regards them as a major fragmentation problem when, in reality, they're they the epitome of how most people envision how an index should actually behave. The use of REORGANIZE on such indexes is the actual cause of the fragmentation because it packs partially full pages to the Fill Factor and does nothing to clear the area above the Fill Factor. If you only use REBUILDs (whether online of offline) you can go literally for weeks and even months with <1% fragmentation because page-splits are seriously minimized.

Of course, all bets are off for fragmented indexes that you rebuild that have the default Fill Factor of "0" or indexes that have a super high Fill Factor because they will also not have any place (or very little of it) to absorb mid-index inserts and "ExpAnsive Updates". It's actually better to not bother doing index maintenance on such indexes than it is to do index maintenance wrong. Of course, that can be the wrong thing to do, as well.

--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)