Index decision on using INCLUDED Columns and Filter Expression

batchakamal
batchakamal used Ask the Experts™
on
Hi,

I have a table called MailBox with 45 columns in my heavy OLTP database with around 3 million records (300GB in size). Users access this table very frequent with different search conditions. Now my problem is, our DBA has created too many indexes in this table. I found lot of indexes are duplicated as below,

1. Two indexes are same by Index Columns but different INCLUDED columns. I think this can be combined as one by collectively creating all INCLUDED column in the first index. Please suggest (with some reference)

2. Two indexes are same by Index Columns, INCLUDED columns but different Filter expression. Please suggest.

Also let me know, how indexes (if too many) causing DEADLOCKS.

Note: I want answers for the above questions, not alternative solutions.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
For both questions you can use DMV's to see which indexes are used (or missing) http://msdn.microsoft.com/en-us/library/ms187974.aspx
So not being used is already the most deciding element to drop it.

1. Combining the two in one index can be usefull to avoid duplicate storage and maintenance. But when both sets of columns are not used together your index will become so big it can become slower and difficult for maintenance.  But when shared columns are update a lot then ofcourse the update won't be necessary in 2 indexes.
.... so depends on how they are used and if columns are update

2. A filter keeps an index small and especially with included columns it won't be updated when rows are updated that don't fall into the filter.
But the 2 filters must differ enough (and match the query) to be worth and to be selected.
batchakamalAdvisor

Author

Commented:
As I found only this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial