January 23, 2015 at 2:51 pm
Our group's primary employee data table has a LOT of indexes.
I noticed that, in some cases, more than 1 index exists on the same column, and all have the same settings (fillfactor, etc...)
The only difference I can see is the set of included columns.
This seems like it would have several drawbacks:
- Increased processing time when updating the table (it only happens 1x per day as an update to the entire table from multiple sources)
- Wasted HD space
- Potential Query Analyzer issues when a query needs to return columns that are included in separate indexes
It seems like a slam dunk to combine the indexes into one with all the included columns together.
Am I correct? Or are there ANY potential benefits to keeping them separate?
Thanks,
CG
P.S. - My guess is that this happened over time because different devs were doing query tuning and simply clicked "Missing Index Details" in the Missing Index in an Execution Plan, and created it blindly
Conceptual Example
Combine:
- EmpIndex1 (EmployeeID) Included (EmployeeName, StatusID)
- EmpIndex2 (EmployeeID) Included (Location, PhoneNumber)
Into:
- EmpIndex (EmployeeID) Included (EmployeeName, StatusID, Location, PhoneNumber)
January 23, 2015 at 6:08 pm
I would say you are on the right track. The two index examples would eat up space unnecessarily and would cause additional work for modification queries involving the Key column. Having too many indexes on an OLTP system is hard on the CPU because it ends up reading a lot of pages from disk due to not having the right index to use and you burn resources maintaining indexes that might have far more modifications to them than reads.
If you're interested in going deep into indexing you could check out the index related videos from the SQLSkills link below.
You might also check out Bart Duncan's post that has a query on index usefulness.
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
https://www.sqlskills.com/sql-server-resources/sql-server-mcm-training-videos/
January 26, 2015 at 1:24 am
cherrygalaxy (1/23/2015)
It seems like a slam dunk to combine the indexes into one with all the included columns together.Am I correct?
Absolutely correct
P.S. - My guess is that this happened over time because different devs were doing query tuning and simply clicked "Missing Index Details" in the Missing Index in an Execution Plan, and created it blindly
Very likely
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply