alexander.suprun (6/10/2011)
So after 2 years this management script is still has a logic error which makes using it poitless if not saying harmful.Major issues:
'IndexUsage' column calculated as (user_seeks+user_scans+user_lookups+user_updates) is used to decide whether we need to rebuild or drop the index.
So in case there is a totally useless index on highly active table where user_seeks+user_scans+user_lookups = 0 but user_updates is big enough then such an index would be rebuilt each and every time we run the script consuming cpu and creating extra load on I/O system. When it's obvious that such an index should be dropped as useless one.
And what is so called "Unused Indexes" are not affecting performance at all, if there are no updates on these indexes then there are no maintenance costs. The only reason you may want to drop them is because of the disk space.
Usually to find good/bad indexes one should use some kind of difference between (user_updates) and (user_seeks+user_scans+user_lookups)
Minor issues:
Limitition of Uf_GetIndexSize to only non-partitioned tables.
Reorganizing heavily used indexes every time even if they don't have fragmentation (@avg_frag<=20)
REDUILD index without ONLINE option (for enterprise edition) may lock the whole table and it is very critical for 24/7 systems
There is no deletes from tblUnusedIndexes, so I'm assuming it's a history table? If so then there is no timestamp in it. Analyzing such a mess can become a challenging task for a DBA.
Dropping index by some fake ID - @UnusedIndID is not a wise decision as well.
Why are people using this script? Just use Ola Hallengren's IndexOptimize stored procedure to take care of your existing indices, and set-up a table to keep track of your indices data. The script in this article just has too many dependencies.
This script is bad for many reasons, including the fact that it does not automatically break down fragmentation into external and internal fragmentation. It also divides by zero. Small tables will constantly be rebuilt with this script, which is pointless. SSMS's Index Physical Statistics standard report has the same epic failure.
As a brief aside, although I love Ola Hallengren's scripts, I really hate the lack of higher-order functions in TSQL stored procedures. Ola's scripts are not that only TSQL scripts that emulate the usefulness of passing higher-order functions as parameters via passing in strings and parsing the string during execution. The problem with parsing strings is that if Ola or whoever wrote the string parser didn't think of a scenario you had in mind, then you either have to edit the script or do some complex logic before invoking the script.
Please also see Limitations on using Missing Indexes feature on MSDN.