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

By : Luis Martin Jun 29, 2005

Printer friendly

Among the regular tasks of any DBA, there’s one thing that we all must find time for: research. In my case, I concentrate my investigation into new tools that streamline the tasks that I already do.

With that idea in mind, I started taking a close look at the SQL 2005 Beta, wondering: could tools like the new Database Tuning Advisor (DTA) improve the choice of indexes for SQL Server 2000 databases?

In order to answer this question, I used a real client database. I thought it wasn’t adequate to use a test database, so instead I chose one that I was optimizing at the moment.

To begin with, I used SQL 2000 Profiler in order to capture a query from the real client application.

Analysis Using the SQL Server 2000 Index Tuning Wizard (ITW)

The query I was analyzing was:

SELECT DISTINCT t.date AS c0,
c.prefijoext AS c1,
c.numeroext AS c2,
c.checkbook AS c3
FROM Transac t (nolock)
join cmpasociados c (nolock)
on t.nrotrans = c.nrotrans
join tiposcmp you (nolock)
on c.codcmp = you.codcmp
join checkbooks so (nolock)
on c.checkbook = so.checkbook
and t.codemp = so.codemp
WHERE T.Nrotranselim is null
AND (CASE WHEN T.Codcmp in (' CÁ, ' CC', ' CB', ' CÉ, ' LR', ' LÓ, ' LP', ' CZ', ' VÁ, ' VB', ' VC', ' YOU GO, ' VZ') THEN T.Nrotransaut
WHEN T.Codcmp in (' IÉ, ' EÉ, ' RD') THEN T.Nrotransctrl
ELSE T.Nrotrans END) is not null
AND (t.CodEmp is null
or t.codemp = 1)
AND c.checkbook = 25
AND t.codsuc = 1
ORDER BY C2 DESC

This query, copied into Query Analyzer, generates the following execution plan:

The statistics of the query execution showed the following values:

170259 row(s) affected)
Table ' TRANSAC'. Scan count 1, logical reads 31004, physical reads 1065, read-ahead reads 29512.
Table ' CMPASOCIADOS'. Scan count 1, logical reads 8482, physical reads 0, read-ahead reads 4393.
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.

After observing the results, I executed the ITW, which generated the following index recommendations:

CREATE NONCLUSTERED INDEX [ IXC2000_TRANSAC27 ] ON [ dbo ].[ TRANSAC ] ([ NROTRANS ] ASC, [ DATE ] ASC, [ CODCMP ] ASC, [ NROTRANSELIM ] ASC, [ CODSUC ] ASC, [ NROTRANSAUT ] ASC, [ CODEMP ] ASC, [ NROTRANSCTRL ] ASC)

CREATE NONCLUSTERED INDEX [ IXC2000_CMPASOCIADOS28 ] ON [ dbo ].[ CMPASOCIADOS ] ([ NROTRANS ] ASC, [ CODCMP ] ASC, [ CHECKBOOK ] ASC, [ PREFIJOEXT ] ASC, [ NUMEROEXT ] ASC)

The expected percentage of improvement using these indexes was 52%.