March 13, 2010 at 11:34 am
I was just tryign to analyse what would be the dis advantages of creating too many indexes for improving peroformance.
In the process of improving peroformance my indexes size id double the size of data, does it make any sense ? actually all my queries are performing very fast after creating so many indexes.
March 13, 2010 at 7:17 pm
It depends on the queries you run, Indexes are very good for select statement, Indexes are not good for DML statements if you are expecting to many DML changes don't create too many indexes, as they get fragmented after DML changes and result in poor performance.
March 13, 2010 at 9:22 pm
Tara-1044200
First be happy that your system is responding rapidly to the users needs, and you have the disc space available to continue what you are doing..
If response time slows, or before that to keep things running as you want, look at establishing a method of keeping your indexes up to date.
I would suggest reading:
http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
a quote from the above:
It is important that you experiment to determine the best threshold for your environment.
March 15, 2010 at 6:13 am
Just to reinforce what the others have said, it's a balancing act. You want to add enough indexes, in the right places, to make any query that uses a WHERE clause (as some DML do) run appropriately fast. But, you want to avoid index duplication or adding unnecessary indexes, because as you insert data (or delete or update), the indexes will have to be maintained, meaning, the key values (and any include columns) have to be added, updated, or removed, which can lead to page splits and data rearrangement, all possibly costly operations, which can lead to blocks and slower performance overall.
So in short, you want to index enough, but just enough. Too many indexes can be as painful as too few.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2010 at 8:47 am
As a point of reference, I spent several hundred hours a few years ago ELIMINATING/improving hundreds of indexes a company created by going hog-wild with Database Tuning Advisor. The combined overhead of DML activity updating all those indexes crushed insert/update/delete activity and brought the app to it's knees. It wasn't pretty. But after I eliminated over 2/3rds of their indexes read performance had dropped by a tiny fraction but concurrency was up through the roof.
It takes EFFORT to develop an appropriate and optimal indexing strategy. I haven't yet, in almost 12 years of consulting (many of it purely perf-tuning related), come across a single client that had it right.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 16, 2010 at 8:21 am
I would also, in your experimenting, be cautious; certainly with SQL Server 2000 I've seen the database engine choose to use absolutely the wrong index, even with up to date statistics.
I also believe that the database engine spends only so long in trying to come up with a query plan. If that is so, then there must, therefore, be a threshold after which indexes are evaluated in less detail and/or some indexes are not evaluated at all.
Definitely use whatever tools you can to run your entire production workload and determine which indexes are not used.
Definitely understand reads vs. writes in your production workload.
Perhaps, cautiously, use index hints where required; re-evaluate these on a regular basis (perhaps annually), as not only patch levels but also size makes a difference on SQL Server execution plan generation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply