| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2012-01-02 : 18:16:33
|
| If I have a table that has 20 fields and I have a query that queries based on 3 of those fields. I have an index that contains 6 fields including the 3 that the query uses. If I check the execution plan the query is using that index (seek). If I create a second index that only contains the 3 fields that the query specifically uses then the execution plan will switch to use my new index.My question is - is the performance gain of the tailored index worth the extra drive space it consumes? The query is executed about 8000 times per minute so it is heavily used. And I presume that SQL Server thinks the tailored index is better since it decided to start using it. Your thoughts? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-02 : 18:51:19
|
On your index with 6 columns (not fields  ), are the 3 columns that your query uses the first 3 in this index? I don't see why adding the 3-column index is a big deal, but whether or not it's worth the disk space is up to you. We could care less how much disk space our databases use, what we care about is performance. Disks are relatively cheap, losing customers due to slow performance is not. Have you looked at statistics io and time for this query with the 6-column index in use and with the 3-column index in use? Can you post those results for us?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-02 : 22:32:42
|
| What is your read write ratio for the table where you have those indexes ? What is the cardinality of the table ? Is the execution plan being reused ?Compare the performance gain to that to the old index and if there is and performance gain and the execution engine is using the newly created index then it is well and good.Just to be on the safer side be sure to add an index hint which uses the new index in the query.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-02 : 22:46:58
|
| I have seen instances where the query optmizer has gone haywire and picked up wrong indexes and performance degraded cause statistics not being updated,huge data adds or deletes ,inefficient cardinality estimations etc etc.I can show you a couple of examples if you want to..After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-02 : 23:42:46
|
quote:
Originally posted by tkizer
I don't need to see examples. I've seen plenty. You deal with those on a case by case basis.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I to agree it should be done on a rare basis.Also I did hint that OP should compare the benefits of using 6 column index to a 3 column index and if the performance is good it would be better off to add the index hint so that the optimiser is forced to use the new index.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-02 : 23:59:54
|
| I understand what you are saying, what I am disagreeing with is adding the index hint even after that analysis is done. If the query optimizer is using the correct index, then you should not add an index hint. You may add an even better index in the future which you won't be able to use with the hint in place. Add index hints only when the optimizer is not selecting the right index and only after it has been thoroughly analyzed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-03 : 00:26:52
|
| What I am trying to say here is that though the index is being used at this stage there are lot of circumstances where optimizer will incorrectly tend not to use the index in the near future.Since the execution count of the query is quite high about 8000 times per minute and can have serious implications if the optimiser thinks otherwire and does not use the index.I am still not getting the point you are trying to make about the implications of using an index hint.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-01-04 : 17:33:05
|
quote:
We could care less how much disk space our databases use, what we care about is performance. Disks are relatively cheap, losing customers due to slow performance is not.
Disk space is less of a concern. The concern is rebuilding the indexes. The more we have the longer it takes and we don't have enterprise edition so it effectively takes the application down for 45 minutes. Our Australian clients are less than happy about it. Moving to Enterprise is the obvious solution but a costly one. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-01-04 : 19:26:54
|
| BTW..."After Monday and Tuesday even the calendar says W T F ...."Hilarious!=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-04 : 23:23:18
|
quote:
Originally posted by Bustaz Kool
BTW..."After Monday and Tuesday even the calendar says W T F ...."Hilarious!=================================================Men shout to avoid listening to one another. -Miguel de Unamuno
I meant Wednesday Thursday Friday..What did you thought ?  After Monday and Tuesday even the calendar says W T F .... |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 02:58:46
|
| We rebuild if < 100 pages and Reorg otherwise. Takes too much log space (and probably MDF extension risk too) to rebuild big indexes, plus we are cheapskates and only have Web version so cannot rebuild ONLINE. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-01-05 : 13:36:46
|
quote:
Originally posted by Kristen
We rebuild if < 100 pages and Reorg otherwise. Takes too much log space (and probably MDF extension risk too) to rebuild big indexes, plus we are cheapskates and only have Web version so cannot rebuild ONLINE.
So you only defrag small tables? Aren't the big tables the ones that really benefit from defrag? I have a table with 14 indexes and each one is 110,000 pages. That is the main problem table. Should I exclude it from defrag and only reorg them? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-05 : 13:43:28
|
| It's not something we can answer. You have to test whether or not your system degrades by only running a reorg. We rebuild above 50%. On one of our VLDBs, we only do reorgs even though we have enterprise edition and the tables qualify for online rebuilding. The online rebuilding still impacts that system. No performance degradation has been noticed by only doing reorgs, so we've kept it that way.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Next Page
|
index performance question