Index Maintenance
-
I need a query to find the unused indexes and missing indexes on all tables in a database.also for creation of index recommendations on tables.
-
Please give Yabingooducklehoo a shot. 😉
--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) -
Have a look at Database Health Monitor. It will give you that info and a whole bunch more. Plus it is free!
That being said, using an automated "build" or "drop" indexes script is risky. What if the "missing indexes" tells you that you are missing 100 distinct indexes on a 100 million row table which has a lot of inserts, updates and deletes with very few selects on it? Are you going to create all 100 indexes simply because the script you ran told you to? It will cause performance issues and waste a lot of disk space.
As for dropping unused indexes, it could be that the index was created so a yearly process would complete in a reasonable time. So it may seem unused, but it is actually just used infrequently. In this case, it may be more beneficial to create the index prior to the yearly process being run rather than leave it there all the time, but you dropping the index can cause unintended performance issues.
A better approach is to address your slow running queries which may mean create an index, or drop an index, or it may mean you remove a cursor or change a WHERE clause or add/remove a temp table or any number of things...
The above is all just my opinion and experience. I am not saying never drop unused indexes or never create indexes, but make sure you understand the risks and benefits and make sure you understand what you are trying to fix so you will know if the index changes actually fixed your problem.
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. -
My take on this is that if you have to ask for scripts to do this, you're probably not qualified to use the scripts. I don't mean that as a slam. It's just that way too many people get themselves into a heap (no pun intended) of trouble using scripts to do things they know absolutely nothing about.
I'll also add that you have to remember that there are a whole lot of people that think they know enough about doing such a thing that actually don't know enough.
To wit, remember that half of all that is written is untrue and the other half is usually written in such a fashion that you can't actually tell... especially on the huge subject of indexes. For example, a lot of people will tell you that you should never use Random GUIDs for index keys because of how quickly they do page splits and rapidly become fragmented. Now, I agree that Random GUIDs suck as index keys for a whole lot of reasons but I'm here to tell you that you can go literally for MONTHS with absolutely NO PAGE SPLITS (not even the supposedly "good" ones, which are still quite bad) and the reason why most people have problems with them is because they don't actually know how to do the correct index maintenance on them. And, no.... using supposed "Best Practice" index maintenance being done on Random GUID keyed indexes is actually worse than doing no index maintenance at all... and I can prove it all... just not in a forum post. 😀
--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) -
To add to Jeff's reply, best practice is not always how you should set things up either. Best practice is to use Windows Authentication and not use mixed mode. I am not saying that is wrong, but some applications with database backends will not allow you to use windows authentication so you must use mixed mode.
And to add to what I was saying, I'm NOT suggesting to never drop an unused index OR never create new indexes from the missing index suggestions from SQL, I am just saying make sure you need those indexes before you create them and understand the implications of creating or removing an index. If creating those indexes and dropping unused indexes would improve things most of the time, Microsoft would create and drop those indexes without telling you about it (likely with a setting similar to the auto update statistics). Since there is no built in automated way to do these things, I would not recommend blindly removing and adding indexes.
I am one of those people that Jeff is talking about too who think they know enough about adding or removing indexes and I don't actually know enough. There are times I am sure an index will help and when I try it out, it makes no difference or makes things worse. Or the worse scenario, my one little thing runs faster, but everything else runs slower.
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. -
Sorry, Brian... I don't know if that came out the wrong way but, to be sure, I didn't mean to make it sound like I was talking about you specifically. Even folks that know a whole lot more about indexing than you or I combined have no way of doing it in a rote manner that is always 100% foolproof.
With that, I'll also state that I'll generally don't drop indexes that I deem "useless" right away. Instead, I'll disable the index end won't actually delete it until after the end of a year. It's a bit of a chore to keep track of such things because SQL Server doesn't actually provide a "Disabled_On" date but I learned my lesson very quickly (it only took once) about just up and dropping indexes.
I'll also state that I need some seriously strong evidence or a really good reason to ever drop a Clustered Index (unique or not) or a Unique Index even if neither is the PK.
--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) -
Oh, it didn't come out wrong, I was just admitting that someone on here (ie me) with a lot of posts and been a DBA for nearly 10 years and I still make mistakes building indexes! There are some indexing experts out there who may disagree with me but I think indexing is HARD to get right. You can tune one query with an index and make 10 others take twice as long. In an isolated sandbox environment, tuning a single query, making indexes is EASY. In a real-world scenario, making indexes is complicated.
And yes I agree about that disable vs drop. That is my rule for every database object actually. Disable or deny on any object before dropping it. Far to easy to think the stored procedure/table/schema/function you wrote 5 years ago for application XYZ is ONLY used by XYZ and drop it only to take down 2 other applications you didn't know were using it and then scramble to recreate it. Disable or Deny and bringing it back online is a simple task. Downside is I know I have ended up with objects that nobody can do anything with on the database for longer than I care to admit...
I think one piece of strong evidence to drop a unique index is if it is a duplicate of another unique index. Or if you have some weird table that should be a heap. We actually have a set of databases that I refer to as the "data graveyard". Automated tools write data into the database and the data WAS used when the product was new, but the product is a good 10 years old using the same code. Since the automated test never fail anymore (except with hardware failure, but the test can detect that and will guess which part failed). So the data goes into the database and is never looked at or modified. This is a good example to have a heap and thus no need for the clustered index. But for 90% of the tables out there (if not more), they should have a clustered index.
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. -
well i remember a time where we used to provide scripts, and then an explanation of what not to do....
try my flavor of unused indexes. don't blindly execute them, evaluate which ones are unused, or only used a little bit.
the WHERE statement is important: current database context only, only non clustered, ignoring PK's and unique's. there is an optional filter for a single table.
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
AND dm_ius.database_id = Db_id()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND(@TableToValidate ='' OR o.NAME = @TableToValidate)there are three columns that are important:
the ServerRestartedDate column. unused indexes since the last time the SQL service restarted. if you just restarted the server, EVERY index is unused. you need a long stretch of uptime for a proper analysis.
UserSeeks and UserScans columns: if they are zero, or a ridiculously low number, they are unused since the last server restart. note the UserUpdates is misleading. if the data changes, every index must also be updated, but it tells you how often the data changes.
DECLARE @TableToValidate VARCHAR(128) =''
DECLARE @LastRestarted datetime;
SELECT @LastRestarted = CREATE_DATE FROM sys.databases where name='tempdb';
SELECT TOP (100) @LastRestarted AS ServerRestartedDate,
schema_name(o.schema_id) As SchemaName,
o.NAME AS ObjectName,
o.type_desc As ObjectType,
i.NAME AS IndexName,
i.index_id AS IndexID,
dm_ius.user_seeks AS UserSeek,
dm_ius.user_scans AS UserScans,
dm_ius.user_lookups AS UserLookups,
dm_ius.user_updates AS UserUpdates,
p.TableRows,
'DROP INDEX ' + Quotename(i.NAME) + ' ON '
+ quotename(schema_name(o.schema_id)) + '.' + Quotename(s.NAME) + '.'
+ Quotename(Object_name(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i
ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o
ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
INNER JOIN (SELECT Sum(p.rows) TableRows,
p.index_id,
p.OBJECT_ID
FROM sys.partitions p
GROUP BY p.index_id,
p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
AND dm_ius.database_id = Db_id()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND(@TableToValidate ='' OR o.NAME = @TableToValidate)
ORDER BY ( dm_ius.user_seeks + dm_ius.user_scans
+ dm_ius.user_lookups ) ASCLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - Lowell wrote:
well i remember a time where we used to provide scripts, and then an explanation of what not to do....
Ah, lordy... be careful, Lowell...
It's not that I don't want to help the OP but, based on the nature of the question, I'm thinking that the OP wouldn't know a good script from a bad one and wouldn't know the right things to look at if he had a good one. There are a ton of bad scripts out there. I also ask that you consider looking at the previous posts for this OP. There's no indication that the OP will or even can actually do "due diligence" on this fairly dangerous subject and I'm concerned with the databases he may be charged with.
Shifting gears a bit, I have to tell you that even a "ridiculously low" number of seeks or scans does NOT mean that the index is "unused". It means they not used often and may actually be absolutely critical to month end or quarter end or year end reports (whatever) actually finishing sometime in the same week. In fact, some authors have proven even dropping indexes that have no sign of usage can be a bad thing because they can actually provide the optimizer with the hint(s) it needs to do a better job.
I also notice that the script you provided does the right thing by checking for unique constraints BUT you can have unique indexes without them being a unique constraint and dropping those can be as devastating as dropping a unique constraint. The code you provided has no such check and can cause an unknowing OP to blow themselves out of the water.
Here's the code that demonstrates a little of what I'm talking about...
CREATE TABLE dbo.UniqueTest
(
SomeInt INT
)
;
CREATE UNIQUE INDEX AK_UniqueTest ON dbo.UniqueTest (SomeInt)
;
SELECT ObjectName = OBJECT_NAME(object_id)
,IndexName = name
,is_primary_key
,is_unique_constraint
,is_unique
,is_ignored_in_optimization
FROM sys.Indexes
WHERE object_id = OBJECT_ID('dbo.UniqueTest')
AND name = 'AK_UniqueTest'
;And here are the results... unique with no unique constraint...
Like I said... it's not that I don't want to help the OP. Instead, he's asking for a suite of scripts that he wouldn't be asking for if he actually knew how to use such a thing and it IS a dangerous thing. Even you made the mistake of not checking for a unique index. That's not meant as a slam... it's just a simple fact that could cost someone some serious pain.
--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) -
Thanks Jeff, I think my script is fine though, I don't think a unique index that is not part of a constraint requires any additional logic or exceptions.
a unique index can help the engine make a better execution plan, sure, but we are talking literally about unused indexes here.
sqlshack has a reference to what you are talking about, how the index statistics can be used to assist other plans:
https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/
If you had a unique, unused index, that was on some column, it would still be without any value, I think. practically, if someone put a unique index, it's most likely on a valuable column that is queried, but if it the columns used in that unique never appears in a join or WHERE statement, and you have a decent set of statistics on the column after the index removal, I think the removal would be justified.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hey Lowell!!!!
Great to see you out here in the wild.
Be a little cautious only on unique indexes when defining "used". The optimizer can actually reference a unique index in it's compile process to identify that there is unique values in a table and then make decisions based on that. When it does this, if the index itself isn't in the plan, it's not in any way marked in sys.dm_db_index_usage_stats. This is actually one reason, of many, that I understand that Azure's automatic index dropping won't drop a unique index.
Otherwise, whole hearted agreement.
An additional caveat is that the sys.dm_db_index_usage_stats DMV does get reset by a bunch of different operations. So not all index usage data is necessarily there. Just a good thing to know if you're going to use this type of query (which I have done and will do).
Thanks for sharing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply