find out unused index

Dear all,

Any tools/script you all use for a while which can find out unused index "correctly" and it will generate drop index script correctly so that we can simple execute the output by that script easily ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
AND s.index_id is null
OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC

Open in new window


Try this one

Or you can also find


SELECT TOP 25
o.name AS ObjectName
, 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(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
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry that the first one show me 2 x entries but that second one return nothing, what will be the problem ? any use database need to be type for the second one ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
did script 1 check all user database one by one ?

and how about the second one ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dropping "unused" indexes is a dangerous thing, as some indexes may only be used once per month (or even once per year) ...
while the costs of maintaining a index, per insert/update/delete is small, recreating the full index (on a large table) can be extremely costy.

I would, if at all, only look at tables with 5+ indexes, and see why there are so many, and what could be done to eventually have less indexes on that table (or split up the table ...) etc ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikas GargBusiness Intelligence DeveloperCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
"dropping "unused" indexes is a dangerous thing, as some indexes may only be used once per month (or even once per year) ..."

yes, but the DMV which store this information also shows if they have been used during the past 1-6 months, right?

"while the costs of maintaining a index, per insert/update/delete is small, recreating the full index (on a large table) can be extremely costy."

yes, but dropping index can also be part of disk capacity management, right? dropping unused index can save a lot of space !

"I would, if at all, only look at tables with 5+ indexes, and see why there are so many, and what could be done to eventually have less indexes on that table (or split up the table ...) etc ..."

usually around 20 index is normal, right?

you mean combine index .. ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
disks are cheap. so trying to win disk space from an index is not the right argumentation for a DBA

>usually around 20 index is normal, right?
20 indexes for a single table? that is "not normal" for OLTP tables, but could be just fine for a DW fact table

and yes, I mean that some indexes could be combined ... but this might require also some application sql changes...
0
marrowyungSenior Technical architecture (Data)Author Commented:
disks are cheap. so trying to win disk space from an index is not the right argumentation for a DBA,

but if disk usage growth by unused index also not a good practice for DBA too as DBA responsible for the capacity management of the SQL server, right?

"but this might require also some application sql changes..."

please give an example.

"20 indexes for a single table? that is "not normal" for OLTP tables, but could be just fine for a DW fact table"

so you still count on 5 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
example of application sql change...

I modified today an application that used a plain view to check if some subscriptions of accounts needed additional services based on account level services, or if those services are to be removed (for a double-checking process);

so far, that task took 3 minutes, and was scheduled every 15 minutes.

the change now was to take some data into staging tables, and proceed from there.
now the task takes 10 seconds.

though no index was added, a staging table was added, to improve an application task.
the actual application was running the task via a stored procedure, so does not see the change.

the dba does see the change.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and yes, 5 indexes can be perfectly normal for a OLTP table.
the primary key is the first one, and normally you have an index on the human "key" (typically the name/description) field
often you have some date fields, which are also indexed
also, often, you have some "category" or similar foreign key field(s), which "require" indexes. each fk field in the table is a candidate for such an index, except for those for very small lookup value (read up about cardinality).
for example, for car models, the foreign key for the brand is a good candidate.
for persons, the gender key is not a good candidate for a index.
a ticket status (open, closed, submitted, waiting) etc is usally not a good candidate for a index, but might be one for partitioning.
0
Scott PletcherSenior DBACommented:
View sys.dm_db_index_usage_stats is extraordinarily useful, but you need to be aware that it gets cleared every time SQL Server (re)starts.  Therefore, if SQL has been up only a day or two, you don't want to rely on the usage stats to drop indexes.

I've removed thousands of indexes in production.  There often are too many because people don't know how to tune them and they "listen" to DTA when it tells them to create an index.  But SQL always wants an index, even if it isn't a good use of resources overall.

What is most critical to overall performance is to first get the best clustered index on every table.  Until you do that, other index "tuning" is actually counter productive.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ScottPletcher,

"There often are too many because people don't know how to tune them and they "listen" to DTA when it tells them to create an index"

you mean DTA is not that good at all ? from my point of view, that tools will give you the same index which already created. can't see why it still suggest !

"But SQL always wants an index, even if it isn't a good use of resources overall."

if an wrong index is here, it will do an index scan instead of table scan and it is much better and lower cost, right?

"What is most critical to overall performance is to first get the best clustered index on every table"

small int or number is a godo candidates for clustered index right? someone use GUID for clustered index, performance very poor,,

but why you said that? basically once the primary key defined SQL server will use it as the clustered index key, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Guy Hengel [angelIII / a3],

"the change now was to take some data into staging tables, and proceed from there.
now the task takes 10 seconds.
"
ok, adding staging table does help ! should it implies that if that staging table on other dedicated HDD, then it will be more faster ?

"for persons, the gender key is not a good candidate for a index
a ticket status (open, closed, submitted, waiting) etc is usally not a good candidate for a index,
"

why ? because we usually don't need to read this value ?

as Vikas Garg's script on finding unused index is not very good result, any other suggestion ? which expect to ALSO generate the drop statement for me on the right DB ?

I found these script for adding and finding unused index quite bad, both return me nothing:

1) http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/
2) http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

but this one return me nothing too:

http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/

which is from Vikas Garg

but the first one from Vikas Garg return me 2 x index.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>ok, adding staging table does help ! should it implies that if that staging table on other dedicated HDD, then it will be more faster ?
yes, having the staging table on another HDD that the source tables will help, because the READ (if needed) and the WRITE (insert) can be parallelized. eventually


>why ? because we usually don't need to read this value ?
we need to read the value, but consider this query with the table having roughly 50% man and 50% woman:
SELECT * FROM PERSON WHERE gender = 'male';

trying to use a index on gender column will return still 50% of the table, so you are just wasting time by trying to build the list of rows (from the index) of the table to actually return. just reading all the rows of the table and just skipping those which are not matching will be faster/more efficient (to explain that in detail would take a bit, please just take it like this at this point)




> basically once the primary key defined SQL server will use it as the clustered index key, right?
please understanding that by default, the SQL Server GUI will make the primary key the clustered index;
however, in most cases, this is NOT the best choice.

usually you want another column to be the one for the clustered index (for persons, you could take the one where most often you perform range queries on)
explanation:  the primary key is a unique index. normally, when using the primary key value, it's to get a single row from the table. having that index as clustered (meaning the table sorted by that value) does not bring any additional value.
however, person.lastname or person.birthdate could be a possible candidate (depending on the application) for such a clustered index, as you may have often queries like:
FROM PERSON WHERE lastname like 'Smi%'
FROM PERSON WHERE lastname >=  'Smith' and lastname <'T'
FROM PERSON WHERE birthdate >=  convert(date, '1970-01-01', 120) and birthdate <  convert(date, '1970-02-01', 120)
0
Scott PletcherSenior DBACommented:
you mean DTA is not that good at all ? from my point of view, that tools will give you the same index which already created. can't see why it still suggest !

No automated tool is actually good at determining best indexing [hallelujah!, since that's a big part of my job!! :-)].  As one important example, DTA will recommend multiple columns for an index, but it does not determine which of them should be first.  And the order of keys is absolutely critical to good performance.

Therefore, carefully review DTA's recommendations before creating, dropping or modifying indexes). For genuinely good results, you need to review the index info yourself so that you can properly adjust what DTA recommends.  But this is a complex process, thus no one can give up a quickie "cheat sheet" or 5-minute primer that will make you really good at index tuning in no time at all.  That simply doesn't exist.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ScottPletcher,

"Therefore, carefully review DTA's recommendations before creating, dropping or modifying indexes)."

what I found out before is the DTA only give recommendation on sth already been done.

"But this is a complex process, thus no one can give up a quickie "cheat sheet" or 5-minute primer that will make you really good at index tuning in no time at all.  That simply doesn't exist."

yeah, but one thing is good to do and a bit time consuming is to combine index and then create new index, right?

" And the order of keys is absolutely critical to good performance."

any suggest read on this so that we can create the right key ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
combining indexes MAY be an option, it really depends on the application SQL.
that's the main issue , and also why MS renamed the "Tuning Wizard" into a "Tuning Advisor" ...
0
marrowyungSenior Technical architecture (Data)Author Commented:
" it really depends on the application SQL.
that's the main issue ,"

like how it call the SQL server? like set based T-SQL update or combine SP so that they call SQL at once and let SP return all value ? as well as the staging DB/table you talked about ?

", and also why MS renamed the "Tuning Wizard" into a "Tuning Advisor" ..."

so it just advisor no matter it is accurate for not ?

"ok, adding staging table does help "

but I think that staging table should be on other dedicated hardware for parallel access, what I am wondering if we create  staging table, additional workload introduced and therefore balance the benefit to add another hard disk for staging table/DB, is that right ?

So application SQL also means architecture SQL, right?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>so it just advisor no matter it is accurate for not ?
trying to, but without the full knowledge of how the application is working logically, checking only the SQL is only 50% of the work, so it can only be an advisor in regards to that part .


>like how it call the SQL server? like set based T-SQL update or combine SP so that they call SQL at once and let SP return all value ? as well as the staging DB/table you talked about ?
yes.
 I will not continue the discussion on the staging tables "hows" and "impacts" as this goes beyond the question
0
marrowyungSenior Technical architecture (Data)Author Commented:
Guy Hengel,

">why ? because we usually don't need to read this value ?
we need to read the value, but consider this query with the table having roughly 50% man and 50% woman:
SELECT * FROM PERSON WHERE gender = 'male';"

yeah, but the point is we should not not the distribution of this value in advance and we might already create the index when we create the table, also when data growth large, we may not check this kind of pattern at all.

"> basically once the primary key defined SQL server will use it as the clustered index key, right?
please understanding that by default, the SQL Server GUI will make the primary key the clustered index;
however, in most cases, this is NOT the best choice."

yeah, specially when clustered index define on non int column and some GUID column, right? clustered index good only on int or small int column,

"usually you want another column to be the one for the clustered index (for persons, you could take the one where most often you perform range queries on)"

yeah, as clustered index has the data in order !
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I will not continue the discussion on the staging tables "hows" and "impacts" as this goes beyond the question"

yes, no needed.

but at this moment I focus on index first for this topic.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.