articles >> performance tuning >> Comparing the SQL Server 2000 Index Tuning ...

Comparing the SQL Server 2000 Index Tuning Wizard and the SQL Server 2005 Database Tuning Adviser

By : Luis Martin Jun 29, 2005

Page 3 / 3



At this point we began to observe some differences. On one hand the percentage of improvement with the ITW was 52%, and on the other with the DTA was 78%. One thing to notice is that the suggested indexes are clearly different.

Let’s use the TRANSAC table, taking a close look at the three first fields. ITW suggests the ordering of the index by the following fields: NroTrans, Date, Codcmp, whereas the DTA suggests for the same table the following order: NroTranselim, CodSuc, CodEmp.

The same happens with table CMPASOCIADOS. With the ITW, the three first fields are: NroTrans, Codcmp, Checkbook, whereas the DTA suggests: Checkbook, NroTrans, CodCmp.

Since researching consists of testing and analyzing, we apply the suggested indexes and obtain the following execution plan:

The statistics obtained after the execution of the query with the new indexes is as follows:

Table ' CMPASOCIADOS'. Scan count 1, logical reads 619, physical reads 0, read-ahead reads 0.
Table ' TRANSAC'. Scan count 1, logical reads 1757, physical reads 0, read-ahead reads 16.
Table ' TIPOSCMP'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table ' TALONARIOS'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
(170259 row(s) affected)

Here, we find similar data between ITW and DTA for the table TRANSAC, but a great difference for the table CMPASOCIADOS.

While optimization with the ITW shows 2162 logical reads, the DTA shows 619.

We needed one more test in order to verify which of the suggestions on indexes we should to choose. We needed to verify the indexes generated by DTA with ITW.

Once I re-initialized the server, I executed the query using Query Analyzer to obtain the following statistics:

(170259 row(s) affected)
Table ' CMPASOCIADOS'. Scan count 1, logical reads 619, physical reads 0, read-ahead reads 0.
Table ' TRANSAC'. Scan count 1, logical reads 1757, physical reads 0, read-ahead reads 0.
Table ' TIPOSCMP'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
Table ' TALONARIOS'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

As you can see, the statistics are practically the same, therefore we can infer that the indexes generated with the use of the DTA are better than the ones used by SQL 2000’s Query Analyzer.

In order to verify it, I executed the ITW with the previous modifications. As expected, we do not obtain suggestions, and it uses the new indexes. This can be verified analyzing the report generated by the ITW.

[ dbo ].[ CMPASOCIADOS] [ IXC2005_CMPASOCIADOS_6_437576597__K3_K1_K2_K8_K7] 100,0 17280
[ dbo ].[ TRANSAC] [ IXC2005_TRANSAC_6_98099390__K10_K30_K81_K1_K2_K105_K3_K55] 100,0 15096

 

Conclusions

In the same way that the ITW of SQL 2000 improved the strength in the analysis and suggesting of indexes compared to the SQL 7.0, the DTA of SQL 2005 Beta 3 has improved on the previous ones.

I believe that the big question is: should we continue optimizing data bases of SQL 2000 with the ITW or should we do it with the DTA?

In my opinion, there still a long way to go before we can answer this question with certainty.

 

Copyright 2005 by the author.