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

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%.