Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can I really drop that index?

By Tony Davis,

You've interrogated the sys.dm_db_index_usage_stats Dynamic Management View (DMV), and indentified an index that appears never to have been used. In theory, it's being maintained and so eating up valuable resources, but is offering no benefit in terms of query performance.

And yet, your finger hovers nervously over the metaphorical "delete" button. You can't quite bring yourself to do it. Is it really safe to drop this index? Perhaps there are queries that rely on it but aren't represented in the DMV because they only run periodically, and have not been executed since the metadata was refreshed.

During my recent visit to Oracle World, I was interested to hear about "invisible indexes" in an Oracle RDBMS. The concept is simple and rather akin to being able to "comment out" an index, by altering it to be invisible. In SQL Server, it is possible to disable an index during troubleshooting, and then rebuild it. However, invisible indexes work differently; the index is still fully maintained, but the optimizer cannot see it, and will not use it in an execution plan, and it can be re-enabled without rebuilding.

In Oracle, it's possible that if an index hasn't been used in an execution plan, it will be reported as unused by the index's monitoring usage attribute, even if the optimizer has actually used the statistics associated with the index, in order to derive an optimal execution path. This means that dropping an index that hasn't been "used" might cause the optimizer to misjudge the data cardinality, and result in sub-optimal execution plans, subsequently.

The same does not appear to be true for DMV-based monitoring of index usage in SQL Server but, nevertheless, invisible indexes struck me as an interesting idea, and one I'd like to make its way "across the border". In cases where we believe an index could be removed, but some doubt remains, we could make such the index invisible for a trial period, allowing us to investigate any potential repercussions from removing it, before actually dropping it. Likewise, if for some reason the optimizer begins to use an index in a highly inefficient manner, we can make it invisible, and investigate alternative execution paths.

I'd like to hear the thoughts of the DBW community. Are invisible indexes a good idea, or do we already have too many indexes to worry about? What sort of tests do you currently perform, and how, before dropping any apparently-unused indexes?

Cheers,

Tony.

Total article views: 331 | Views in the last 30 days: 3
 
Related Articles
FORUM

How to execute Index?

How to execute Index?

FORUM

query optimizer

OPTIMIZATION

FORUM

Performance optimization on huge data

Query execution optimization by implementing Clustered and non-clustered index

FORUM

Optimizer?

how Optimizer selects the index?

FORUM

Invisible stored procedure

SP executes correctly but its invisible in Management Studio

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones