|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Index Tuning Wizardam applying additional non clustered indexes were I feel it should benefit the query. After running the Index Tuning Wizard - the recommendation is to remove the indexes that I have applied. Looking at the Statistics io it seems that these indexes enhance performance. Why does the Index Tuning Wizard recommend removing these indexes? Below is the statistics io output before and after my indexes are applied. Statistics io before indexes are applied: -- Table 'Table1'. Scan count 1, logical reads 168 -- Table 'Table2'. Scan count 1, logical reads 97 -- Table 'Table3'. Scan count 1, logical reads 17 -- Table 'Table4'. Scan count 1, logical reads 406 -- Table 'Table5'. Scan count 1, logical reads 19 -- Table 'Table6'. Scan count 1, logical reads 2 -- Table 'Table7'. Scan count 1, logical reads 2 -- Table 'Table8'. Scan count 1, logical reads 2 -- Table 'Table9'. Scan count 1, logical reads 2 -- Table 'Table10'. Scan count 537, logical reads 1731 -- Table 'Table11'. Scan count 3, logical reads 11 After Indexes are applied: -- Table 'Table1'. Scan count 1, logical reads 168 -- Table 'Table2'. Scan count 1, logical reads 97 -- Table 'Table3'. Scan count 1, logical reads 17 -- Table 'Table4'. Scan count 1, logical reads 408 -- Table 'Table5'. Scan count 1, logical reads 5 -- Table 'Table6'. Scan count 1, logical reads 1 -- Table 'Table7'. Scan count 1, logical reads 1 -- Table 'Table8'. Scan count 1, logical reads 1 -- Table 'Table9'. Scan count 1, logical reads 2 -- Table 'Table10'. Scan count 3, logical reads 429 -- Table 'Table11'. Scan count 3, logical reads 11 * For all stats - physical reads 0, read-ahead reads 0. The improvement is quite obvious, don't you think? Or am I missing
something...? ML The first set of statistics listed is after using the Query Tuning Wizard
(QTW). The indexes have been removed. It shows the current state of the db. The second list of statistics is before using the Tuning Wizard and after applying additional indexes on my own. It seems to me that the recommendation (by the QTW) to remove these indexes is a poor one. Show quote "Wes" wrote: > I am running the index tuning wizard to evaluate a query for optimization. I > am applying additional non clustered indexes were I feel it should benefit > the query. After running the Index Tuning Wizard - the recommendation is to > remove the indexes that I have applied. Looking at the Statistics io it > seems that these indexes enhance performance. Why does the Index Tuning > Wizard recommend removing these indexes? Below is the statistics io output > before and after my indexes are applied. > > Statistics io before indexes are applied: > -- Table 'Table1'. Scan count 1, logical reads 168 > -- Table 'Table2'. Scan count 1, logical reads 97 > -- Table 'Table3'. Scan count 1, logical reads 17 > -- Table 'Table4'. Scan count 1, logical reads 406 > -- Table 'Table5'. Scan count 1, logical reads 19 > -- Table 'Table6'. Scan count 1, logical reads 2 > -- Table 'Table7'. Scan count 1, logical reads 2 > -- Table 'Table8'. Scan count 1, logical reads 2 > -- Table 'Table9'. Scan count 1, logical reads 2 > -- Table 'Table10'. Scan count 537, logical reads 1731 > -- Table 'Table11'. Scan count 3, logical reads 11 > > After Indexes are applied: > -- Table 'Table1'. Scan count 1, logical reads 168 > -- Table 'Table2'. Scan count 1, logical reads 97 > -- Table 'Table3'. Scan count 1, logical reads 17 > -- Table 'Table4'. Scan count 1, logical reads 408 > -- Table 'Table5'. Scan count 1, logical reads 5 > -- Table 'Table6'. Scan count 1, logical reads 1 > -- Table 'Table7'. Scan count 1, logical reads 1 > -- Table 'Table8'. Scan count 1, logical reads 1 > -- Table 'Table9'. Scan count 1, logical reads 2 > -- Table 'Table10'. Scan count 3, logical reads 429 > -- Table 'Table11'. Scan count 3, logical reads 11 > > * For all stats - physical reads 0, read-ahead reads 0. > On Thu, 29 Sep 2005 06:36:05 -0700, Wes wrote:
>The first set of statistics listed is after using the Query Tuning Wizard Hi Wes,>(QTW). The indexes have been removed. It shows the current state of the db. > The second list of statistics is before using the Tuning Wizard and after >applying additional indexes on my own. It seems to me that the >recommendation (by the QTW) to remove these indexes is a poor one. The Index Tuning Wizard is a tool - useful, but with limitations. It's recommendations can give you a head start for increasing performance, but you should always test, test, and test to verify that they do indeed give the expected results. In this case, I'd think twice about the ITW's suggestion. Sure, it reduces the physical reads when run with a cold cache. But it also increases the number of logical reads. How often will this query be run without any of the data in the cache? And how often will the query find (almost) everything it needs in cache? That's the question it all boils down to. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Did you compare the executions plans before and after to be sure that sql
server is using the indexes applied? AMB Show quote "Wes" wrote: > I am running the index tuning wizard to evaluate a query for optimization. I > am applying additional non clustered indexes were I feel it should benefit > the query. After running the Index Tuning Wizard - the recommendation is to > remove the indexes that I have applied. Looking at the Statistics io it > seems that these indexes enhance performance. Why does the Index Tuning > Wizard recommend removing these indexes? Below is the statistics io output > before and after my indexes are applied. > > Statistics io before indexes are applied: > -- Table 'Table1'. Scan count 1, logical reads 168 > -- Table 'Table2'. Scan count 1, logical reads 97 > -- Table 'Table3'. Scan count 1, logical reads 17 > -- Table 'Table4'. Scan count 1, logical reads 406 > -- Table 'Table5'. Scan count 1, logical reads 19 > -- Table 'Table6'. Scan count 1, logical reads 2 > -- Table 'Table7'. Scan count 1, logical reads 2 > -- Table 'Table8'. Scan count 1, logical reads 2 > -- Table 'Table9'. Scan count 1, logical reads 2 > -- Table 'Table10'. Scan count 537, logical reads 1731 > -- Table 'Table11'. Scan count 3, logical reads 11 > > After Indexes are applied: > -- Table 'Table1'. Scan count 1, logical reads 168 > -- Table 'Table2'. Scan count 1, logical reads 97 > -- Table 'Table3'. Scan count 1, logical reads 17 > -- Table 'Table4'. Scan count 1, logical reads 408 > -- Table 'Table5'. Scan count 1, logical reads 5 > -- Table 'Table6'. Scan count 1, logical reads 1 > -- Table 'Table7'. Scan count 1, logical reads 1 > -- Table 'Table8'. Scan count 1, logical reads 1 > -- Table 'Table9'. Scan count 1, logical reads 2 > -- Table 'Table10'. Scan count 3, logical reads 429 > -- Table 'Table11'. Scan count 3, logical reads 11 > > * For all stats - physical reads 0, read-ahead reads 0. > Yes. You can also see that the indexes are being used by looking at the stats-
(*note that I have listed the stats in after/before order of implementing the recommended changes. Sorry – I know this is confusing.) Looking at rows for Table10: After applying recommendation to remove index - Table 'Table10'. Scan count 537, logical reads 1731 Before applying recommendation to remove index - Table 'Table10'. Scan count 3, logical reads 429 Show quote "Alejandro Mesa" wrote: > Did you compare the executions plans before and after to be sure that sql > server is using the indexes applied? > > > AMB > > "Wes" wrote: > > > I am running the index tuning wizard to evaluate a query for optimization. I > > am applying additional non clustered indexes were I feel it should benefit > > the query. After running the Index Tuning Wizard - the recommendation is to > > remove the indexes that I have applied. Looking at the Statistics io it > > seems that these indexes enhance performance. Why does the Index Tuning > > Wizard recommend removing these indexes? Below is the statistics io output > > before and after my indexes are applied. > > > > Statistics io before indexes are applied: > > -- Table 'Table1'. Scan count 1, logical reads 168 > > -- Table 'Table2'. Scan count 1, logical reads 97 > > -- Table 'Table3'. Scan count 1, logical reads 17 > > -- Table 'Table4'. Scan count 1, logical reads 406 > > -- Table 'Table5'. Scan count 1, logical reads 19 > > -- Table 'Table6'. Scan count 1, logical reads 2 > > -- Table 'Table7'. Scan count 1, logical reads 2 > > -- Table 'Table8'. Scan count 1, logical reads 2 > > -- Table 'Table9'. Scan count 1, logical reads 2 > > -- Table 'Table10'. Scan count 537, logical reads 1731 > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > After Indexes are applied: > > -- Table 'Table1'. Scan count 1, logical reads 168 > > -- Table 'Table2'. Scan count 1, logical reads 97 > > -- Table 'Table3'. Scan count 1, logical reads 17 > > -- Table 'Table4'. Scan count 1, logical reads 408 > > -- Table 'Table5'. Scan count 1, logical reads 5 > > -- Table 'Table6'. Scan count 1, logical reads 1 > > -- Table 'Table7'. Scan count 1, logical reads 1 > > -- Table 'Table8'. Scan count 1, logical reads 1 > > -- Table 'Table9'. Scan count 1, logical reads 2 > > -- Table 'Table10'. Scan count 3, logical reads 429 > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > * For all stats - physical reads 0, read-ahead reads 0. > > Wes,
Did you clean the cache before executing both statements? DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS Do not execute this in a production server. AMB Show quote "Wes" wrote: > Yes. You can also see that the indexes are being used by looking at the stats- > (*note that I have listed the stats in after/before order of implementing > the recommended changes. Sorry – I know this is confusing.) > Looking at rows for Table10: > After applying recommendation to remove index - > Table 'Table10'. Scan count 537, logical reads 1731 > Before applying recommendation to remove index - > Table 'Table10'. Scan count 3, logical reads 429 > > > > > "Alejandro Mesa" wrote: > > > Did you compare the executions plans before and after to be sure that sql > > server is using the indexes applied? > > > > > > AMB > > > > "Wes" wrote: > > > > > I am running the index tuning wizard to evaluate a query for optimization. I > > > am applying additional non clustered indexes were I feel it should benefit > > > the query. After running the Index Tuning Wizard - the recommendation is to > > > remove the indexes that I have applied. Looking at the Statistics io it > > > seems that these indexes enhance performance. Why does the Index Tuning > > > Wizard recommend removing these indexes? Below is the statistics io output > > > before and after my indexes are applied. > > > > > > Statistics io before indexes are applied: > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > -- Table 'Table4'. Scan count 1, logical reads 406 > > > -- Table 'Table5'. Scan count 1, logical reads 19 > > > -- Table 'Table6'. Scan count 1, logical reads 2 > > > -- Table 'Table7'. Scan count 1, logical reads 2 > > > -- Table 'Table8'. Scan count 1, logical reads 2 > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > -- Table 'Table10'. Scan count 537, logical reads 1731 > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > After Indexes are applied: > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > -- Table 'Table4'. Scan count 1, logical reads 408 > > > -- Table 'Table5'. Scan count 1, logical reads 5 > > > -- Table 'Table6'. Scan count 1, logical reads 1 > > > -- Table 'Table7'. Scan count 1, logical reads 1 > > > -- Table 'Table8'. Scan count 1, logical reads 1 > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > -- Table 'Table10'. Scan count 3, logical reads 429 > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > * For all stats - physical reads 0, read-ahead reads 0. > > > Now you remove the indexes and comapare it again. You will know why? coz
statistics are updated. Regards R.D Show quote "Wes" wrote: > Yes. You can also see that the indexes are being used by looking at the stats- > (*note that I have listed the stats in after/before order of implementing > the recommended changes. Sorry – I know this is confusing.) > Looking at rows for Table10: > After applying recommendation to remove index - > Table 'Table10'. Scan count 537, logical reads 1731 > Before applying recommendation to remove index - > Table 'Table10'. Scan count 3, logical reads 429 > > > > > "Alejandro Mesa" wrote: > > > Did you compare the executions plans before and after to be sure that sql > > server is using the indexes applied? > > > > > > AMB > > > > "Wes" wrote: > > > > > I am running the index tuning wizard to evaluate a query for optimization. I > > > am applying additional non clustered indexes were I feel it should benefit > > > the query. After running the Index Tuning Wizard - the recommendation is to > > > remove the indexes that I have applied. Looking at the Statistics io it > > > seems that these indexes enhance performance. Why does the Index Tuning > > > Wizard recommend removing these indexes? Below is the statistics io output > > > before and after my indexes are applied. > > > > > > Statistics io before indexes are applied: > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > -- Table 'Table4'. Scan count 1, logical reads 406 > > > -- Table 'Table5'. Scan count 1, logical reads 19 > > > -- Table 'Table6'. Scan count 1, logical reads 2 > > > -- Table 'Table7'. Scan count 1, logical reads 2 > > > -- Table 'Table8'. Scan count 1, logical reads 2 > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > -- Table 'Table10'. Scan count 537, logical reads 1731 > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > After Indexes are applied: > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > -- Table 'Table4'. Scan count 1, logical reads 408 > > > -- Table 'Table5'. Scan count 1, logical reads 5 > > > -- Table 'Table6'. Scan count 1, logical reads 1 > > > -- Table 'Table7'. Scan count 1, logical reads 1 > > > -- Table 'Table8'. Scan count 1, logical reads 1 > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > -- Table 'Table10'. Scan count 3, logical reads 429 > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > * For all stats - physical reads 0, read-ahead reads 0. > > > I cleared the cache. I now see that there are differences in the physical
reads and read-ahead reads. Thanks for all the help. Here are the stats (with indexes)(before applying recommendations): -- Table 'Table1'. Scan count 1, logical reads 168, physical reads 2, read-ahead reads 159. -- Table ‘Table2’. Scan count 1, logical reads 97, physical reads 1, read-ahead reads 88. -- Table 'Table3'. Scan count 1, logical reads 17, physical reads 1, read-ahead reads 15. -- Table 'Table4'. Scan count 1, logical reads 408, physical reads 5, read-ahead reads 387. -- Table 'Table5'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 0. -- Table 'Table6'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0. -- Table 'Table7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0. -- Table 'Table8'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0. -- Table 'Table9'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0. -- Table 'Table10'. Scan count 3, logical reads 429, physical reads 1, read-ahead reads 135. -- Table 'Table11'. Scan count 3, logical reads 11, physical reads 1, read-ahead reads 8. Here are the stats (with indexes removed)(after applying recommendations): -- Table 'Table1'. Scan count 1, logical reads 168, physical reads 2, read-ahead reads 159. -- Table ‘Table2’. Scan count 1, logical reads 97, physical reads 1, read-ahead reads 87. -- Table 'Table3'. Scan count 1, logical reads 17, physical reads 1, read-ahead reads 14. -- Table 'Table4'. Scan count 1, logical reads 406, physical reads 0, read-ahead reads 409. -- Table 'Table5'. Scan count 1, logical reads 19, physical reads 1, read-ahead reads 14. -- Table 'Table6'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0. -- Table 'Table7'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0. -- Table 'Table8'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0. -- Table 'Table9'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0. -- Table 'Table10'. Scan count 537, logical reads 1998, physical reads 0, read-ahead reads 6. -- Table 'Table11'. Scan count 3, logical reads 11, physical reads 1, read-ahead reads 8. Show quote "R.D" wrote: > Now you remove the indexes and comapare it again. You will know why? coz > statistics are updated. > Regards > R.D > > "Wes" wrote: > > > Yes. You can also see that the indexes are being used by looking at the stats- > > (*note that I have listed the stats in after/before order of implementing > > the recommended changes. Sorry – I know this is confusing.) > > Looking at rows for Table10: > > After applying recommendation to remove index - > > Table 'Table10'. Scan count 537, logical reads 1731 > > Before applying recommendation to remove index - > > Table 'Table10'. Scan count 3, logical reads 429 > > > > > > > > > > "Alejandro Mesa" wrote: > > > > > Did you compare the executions plans before and after to be sure that sql > > > server is using the indexes applied? > > > > > > > > > AMB > > > > > > "Wes" wrote: > > > > > > > I am running the index tuning wizard to evaluate a query for optimization. I > > > > am applying additional non clustered indexes were I feel it should benefit > > > > the query. After running the Index Tuning Wizard - the recommendation is to > > > > remove the indexes that I have applied. Looking at the Statistics io it > > > > seems that these indexes enhance performance. Why does the Index Tuning > > > > Wizard recommend removing these indexes? Below is the statistics io output > > > > before and after my indexes are applied. > > > > > > > > Statistics io before indexes are applied: > > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > > -- Table 'Table4'. Scan count 1, logical reads 406 > > > > -- Table 'Table5'. Scan count 1, logical reads 19 > > > > -- Table 'Table6'. Scan count 1, logical reads 2 > > > > -- Table 'Table7'. Scan count 1, logical reads 2 > > > > -- Table 'Table8'. Scan count 1, logical reads 2 > > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > > -- Table 'Table10'. Scan count 537, logical reads 1731 > > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > > > After Indexes are applied: > > > > -- Table 'Table1'. Scan count 1, logical reads 168 > > > > -- Table 'Table2'. Scan count 1, logical reads 97 > > > > -- Table 'Table3'. Scan count 1, logical reads 17 > > > > -- Table 'Table4'. Scan count 1, logical reads 408 > > > > -- Table 'Table5'. Scan count 1, logical reads 5 > > > > -- Table 'Table6'. Scan count 1, logical reads 1 > > > > -- Table 'Table7'. Scan count 1, logical reads 1 > > > > -- Table 'Table8'. Scan count 1, logical reads 1 > > > > -- Table 'Table9'. Scan count 1, logical reads 2 > > > > -- Table 'Table10'. Scan count 3, logical reads 429 > > > > -- Table 'Table11'. Scan count 3, logical reads 11 > > > > > > > > * For all stats - physical reads 0, read-ahead reads 0. > > > >  |
|||||||||||||||||||||||
Other interesting topics