Оптимизация с использованием SQL Server 2000 Index Tuning Wizard и SQL Server 2005 Database Tuning Advisor — до 30 мин

Помимо ручной настройки запросов и индексов, SQL Server 2000 и 2005 предоставляют автоматизированные средства настройки индексов. Запрос, который мы проанализируем с помощью SQL Server 2000 Index Tuning Wizard:

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 (' CA', ' CC', ' CB', ' CE',' LR',

' LO', ' LP', ' CZ',' VA', ' VB', ' VC', ' YOU',' 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

Этот запрос, скопированный в Query Analyzer, имел план исполнения, показанный на рисунке 11.1. Статистика выполнения запроса показала следующие значения:

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.

После запуска ITW, были получены следующие рекомендации по индексам:

Рисунок 11.1 — Исходный план выполнения запроса на SQL Server 2000

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)

Ожидаемое улучшение при использовании этих индексов составляло 52 %. После применения индексов, получился следующий план исполнения (см. рисунок 11.2):

Рисунок 11.2 — План выполнения запроса после выполнения рекомендаций ITW

Cтатистика данных после создания индексов выглядела так:

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

Как можно видеть, улучшения очевидны. Число логических чтений в таблице TRANSAC было сокращено с 31004 до 1889.

Повторяем анализ, используя SQL Server 2005 Database Tuning Advisor (DTA)

Сначала удалим созданные ранее индексы, а затем проанализируем те же самые запрос и данные, используя DTA. В этом случае, план исполнения выглядел так (см. рисунок 11.3):

Рисунок 11.3 — Исходный план выполнения запроса на SQL Server 2005

Статистика данных, соответствующая исполняемому запросу, была следующей:

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)

Как видно, различия между статистиками запроса, исполняемого до оптимизации, минимальны, что и ожидалось. После этого DTA предложил создать следующие ниже индексы, ожидая улучшения на 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)

Уже здесь мы наблюдаем некоторые различия. С одной стороны, процент улучшения у ITW был 52 %, а у DTA - 78 %. С другой стороны, стоит обратить внимание на то, что предложенные индексы имеют различия.

Используем таблицу TRANSAC, и рассмотрим первые три поля. ITW предлагает следующий порядок полей в индексе: NroTrans, Date и Codcmp. В то же время, DTA предлагает другой порядок: NroTranselim, CodSuc и CodEmp.

То же самое и с таблицей CMPASOCIADOS. У ITW три первых поля: NroTrans, Codcmp и Checkbook, тогда как DTA предлагает: Checkbook, NroTrans и CodCmp.

После создания предложенных индексов получаем следующий план исполнения (см. рисунок 11.4):

Рисунок 11.4 — План выполнения запроса после выполнения рекомендаций DTA

Статистика, полученная после выполнения запроса с новыми индексами, следующая:

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)

Здесь, мы видим очень схожие цифры ITW и DTA для таблицы TRANSAC, но большие различия статистики для таблицы CMPASOCIADOS.

В то время, как оптимизация с помощью ITW дала 2162 логических чтений, после оптимизации DTA их осталось только 619.

Для полноты картины, проведём ещё одно испытание, чтобы проверить, какое из предложений индексов стоит выбрать. Для этого, нужно проверить индексы, рекомендованные DTA и ITW.

После перезапуска сервера и выполнения в Query Analyzer запрос, была получена следующую статистика:

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

Как можно видеть, статистика фактически та же самая, поэтому можно сделать заключение, что предлагаемые DTA индексы лучше. Это подтверждает и то, что после создания предложенных DTA индексов, ITW больше не предлагал для них изменений.

Дата добавления: 2014-01-11