January 8, 2021 at 3:54 pm
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?
January 8, 2021 at 5:30 pm
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.
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.
January 11, 2021 at 12:46 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 11, 2021 at 5:26 pm
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
January 12, 2021 at 1:23 pm
@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?
January 12, 2021 at 2:04 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 12, 2021 at 7:57 pm
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
January 13, 2021 at 2:25 am
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
January 13, 2021 at 2:27 pm
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
January 13, 2021 at 2:31 pm
Does "index disabling" blocks a table?
Do we have any constraints regarding it?
January 13, 2021 at 6:13 pm
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
January 13, 2021 at 6:36 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply