Once indexes were applied, the execution plan results were:
And the statistics after the implementation of the indexes were:
(170259 row(s) affected)
Table ‘ CMPASOCIADOS’. Scan count 1, logical reads 2162, physical reads 0, read-ahead reads 0.
Table ‘ TRANSAC’. Scan count 1, logical reads 1889, physical reads 0, read-ahead reads 24.
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.
As we can observe, the improvement is evident. We have passed from 31004 logical reads to 1889 in the TRANSAC table.
The Same Analysis Using the SQL Server 2005 Database Tuning Advisor (DTA)
Now, I wanted to see how the DTA would analyze the same query. First, I removed the indexes added in the previous step, then analyzed the same query and data using the DTA. In this case, the execution plan looked like this:
The statistics corresponding to the execution of the query were:
Table ‘ TRANSAC’. Scan count 1, logical reads 31004, physical reads 0, read-ahead reads 31008.
Table ‘ CMPASOCIADOS’. Scan count 1, logical reads 8482, physical reads 19, read-ahead reads 4482.
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.
(170259 row(s) affected)
It’s possible to observe that the differences between the statistics of the query executed without optimizing are minimal, which was expected. Later, I executed the DTA and it suggested the following indexes with an expected improvement of 78%.
CREATE NONCLUSTERED INDEX [ IXC2005_TRANSAC_6_98099390__K10_K30_K81_K1_K2_K105_K3_K55 ] ON [ dbo ].[ TRANSAC ]
(
[ NROTRANSELIM ] ASC,
[ CODSUC ] ASC,
[ CODEMP ] ASC,
[ NROTRANS ] ASC,
[ DATE ] ASC,
[ NROTRANSCTRL ] ASC,
[ CODCMP ] ASC,
[ NROTRANSAUT ] ASC
)
CREATE NONCLUSTERED INDEX [ IXC2005_CMPASOCIADOS_6_437576597__K3_K1_K2_K8_K7 ] ON [ dbo ].[ CMPASOCIADOS ]
(
[ CHECKBOOK ] ASC,
[ NROTRANS ] ASC,
[ CODCMP ] ASC,
[ NUMEROEXT ] ASC,
[ PREFIJOEXT ] ASC
)