Эффективность плана выполнения запроса

← →
Bless © (2005-04-25 16:59) [0]

Проблема вот в чем.
В пятницу, после жалоб на жуткие тормоза, я посмотрел на план выполнения одного запроса (выборка)
(который эти тормоза провоцировал).
Оказалось, что этот план (сгенерированный самим MS SQL, там нет никаких хинтов) - на редкость неудачный.
Потанцевав немного с бубном, решил перегрузить сервер. После перезагрузки сделал заодно полный BACKUP базы.
Это (перезагрузка) не помогло. Но тут к счастью вскоре закончился рабочий день. :)
В понедельник же тормозов - как и не бывало. Посмотрел на план выполнения - он стал совсем другим!
Я из сделанного накануне backup-а сделал себе тестовую копию базы данных.
И выполнил в ней этот же запрос. В результате - те же тормоза и тот же фиговый план выполнения, что и в пятницу.
Чем это можно объяснить?

← →
Desdechado © (2005-04-25 17:29) [1]

1. сборку статистики индексов сделай
2. поиграйся с настройками оптимизатора

← →
Nikolay M. © (2005-04-25 21:31) [2]

> Чем это можно объяснить?

Необновленной статистикой.

← →
Johnmen © (2005-04-25 22:02) [3]

>Чем это можно объяснить?

Глобальным потеплением.

← →
MOA © (2005-04-25 22:48) [4]

DBCC FREEPROCCACHE

← →
Bless © (2005-04-26 10:01) [5]

>Необновленной статистикой.

Я тоже так подумал. Но вызов sp_updatestats (этого ведь достаточно чтобы обновить статистику? Или нет?) ничего не изменил.

MOA [4]>

Не помогло. Тем более, что я выполняю этот запрос не в процедуре а просто в Query Analizer-е.

Запрос выглядит так:
SELECT nzz INTO #p FROM pozib WHERE nzs=@nzs --вернет 91 запись. /*ниже источник тормозов*/ SELECT * FROM pozib_m pm INNER JOIN shapb_m_cache s ON pm.nzm=s.nzm WHERE s.nzs=@nzs AND s.comp_name=@comp AND s.indi<>1 AND pm.nzp IN (SELECT nzz FROM #p) AND s.del<>1

Неэффективный план запроса выглядит так:
1)table scan
OBJECT: ([v_ao].[dbo].[Pozib_m] AS [pm])
На выходе - 613 444 записи (то есть все записи в этой таблице)

2)table scan
OBJECT: ([v_ao].[dbo].[shapb_m_cache] AS [s])
WHERE: ((([s].[nzs]=[@nzs] AND [s].[indi]<>1) AND [s].[comp_name]=[@comp_name]) AND Convert([s].[del])<>1)
На выходе 21 запись.

3)Hash Match/Inner join первого и второго пунктов
HASH : ([pm].[nzm]=[s].[nzm])
На выходе 25 685 записей

4)table scan
OBJECT : [tembdb][dbo][ #p_________________000000000830]
На выходе 91 запись

5)Hash Match/Inner join
HASH : [pm].[nzp]=[ #p].[nzz]

На выходе 113 результирующих записей.

А эффективный начинается так :
1) table scan
OBJECT : [tembdb][dbo][ #p_________________000000000830]
На выходе 91 запись

2)Sort/ Distinct Sort
DISTINCT ORDER BY : ([ #p].[nzz] ASC)

3) Index Seek
OBJECT : ([ao].[dbo].[pozib_m].[pozib_m_nzp_index] AS [pm]),
SEEK : ([pm].[nzp]=[ #p].[nzz]) ORDERERD FORWARD
На выходе 168 записей.

Потом эти 168 объединяются с 21 записью из shapb_m_cache и т.д.
То есть в дальнейшем работа ведется уже с маленькими наборами данных.

Если вручную слегка изменить запрос на
SELECT * FROM pozib_m pm WITH (INDEX pozib_m_nzp_index) INNER JOIN shapb_m_cache s ON pm.nzm=s.nzm WHERE s.nzs=@nzs AND s.comp_name=@comp AND s.indi<>1 AND pm.nzp IN (SELECT nzz FROM #p) AND s.del<>1

то это приводит лишь к тому, что в пункте 1) заменяется table scan на index seek, что без толку, поскольку возвращаются все те же 613 444 записей.

Как мне сделать из первого плана второй?

Если так:
SELECT * FROM #p INNER JOIN pozib_m pm ON #p.nzz=pm.nzp INNER JOIN shapb_m_cache s ON pm.nzm=s.nzm WHERE s.nzs=@nzs AND s.comp_name=@comp AND s.indi<>1 --stoimost trudozatrat (indi=1) schitaetsia, a ne vvoditsia polzovatelem AND s.del<>1 OPTION (FORCE ORDER)

то получается почти так же эффективно, как и в случае второго плана выполнения (по времени), хотя немножко все же хуже. Да и план выполнения все ж отличается от эффективного.
План начинается так:

1)table scan
OBJECT : [tembdb][dbo][ #p_________________000000000830]
На выходе 91 запись

2)table scan
OBJECT: ([v_ao].[dbo].[Pozib_m] AS [pm])
На выходе - все те же613 444 записи.

3)Hash Match/Inner join
HASH : ([ #p].[nzz]=[pm].[nzp])
На выходе 168 записей. Как и в эффективном плане после 3) пункта. Хотя и пришли к этим 168 записям по-разному.

Но что-то мне очень не охота хинты в запрос вставлять.

← →
Nikolay M. © (2005-04-26 10:17) [6]

> >Необновленной статистикой.
>
> Я тоже так подумал. Но вызов sp_updatestats (этого ведь
> достаточно чтобы обновить статистику? Или нет?) ничего не
> изменил.

Сделай для нужного индекса
DBCC SHOW_STATISTICS
и посмотри, сколько записей (Rows Sampled) от общего числа записей (Rows) на данный момент учтено последним обновлением статистики.
sp_updatestats собирает статистику по дефолтному кол-ву записей, чему оно равно в твоем случае - хз, но, скорее всего, не 100%.

← →
Bless © (2005-04-26 12:54) [7]

Nikolay M.[6]>

Ну нифига себе! Сделал, как ты сказал. Оказалось, что статистики по индексам таблицы pozib_m в "плохой" базе вообще нет!
То есть поле sysindexes.statblob = NULL.

Почему sp_updatestats не обновила статистику для этой таблицы?
Как сделать, чтоб статистика по индексам этой таблицы обновилась?
И почему она могла пропасть? Это я что-то где-то "ровными" руками потрогал?
Киньте, кто знает, толковым урлом в меня на эту тему, а то я с понятием "статистика" только в пятницу после этой проблемы познакомился и мое представление о ней пока очень смутное.

← →
Nikolay M. © (2005-04-26 13:31) [8]

> Bless © (26.04.05 12:54) [7]

Для начала в БОЛ. Ключевые слова для поиска ты уже знаешь.
Потом за нюансами сюда:
http://www.sql.ru/forum/actualtopics.aspx?search=%F1%F2%E0%F2%E8%F1%F2%E8%EA%E0&bid=1

← →
Bless © (2005-04-26 16:52) [9]

Статистика наконец-то появилась
После вызова DBCC DBREINDEX (pozib_m).
Это не странно?

Или sp_updatestats работает только для как бы это сказать "настоящих статистик", у которых есть свое имя, а для статистик, положенных индексу, не работает?

← →
Bless © (2005-04-26 16:55) [10]

Неужели MSSQL, когда автоматически обновляет статистику индексов, тоже вызвает DBCC DBREINDEX?

← →
Bless © (2005-04-26 17:20) [11]

Кстати, статистика хоть и появилось, но план выполнения остался прежним :(

← →
Nikolay M. © (2005-04-26 17:41) [12]

Кстати, попробуй ради эксперимента заменить переменные @nzs и @comp на собственно их значения. Думаю, план должен поменяться.

← →
Bless © (2005-04-26 18:00) [13]

Nikolay M.[12]>
Попробовал. План ничуть не изменился :(

← →
Nikolay M. © (2005-04-26 18:40) [14]

> Попробовал. План ничуть не изменился :(

Ну, ладно, все равно дело не в этом. В любом случае у тебя встречается в плане как минимум один скан таблицы, что не есть гуд для достаточно больших таблиц. Проверь наличие индексов по связываемым полям. IN замени на EXISTS или вообще на JOIN.

← →
Bless © (2005-04-27 09:50) [15]

Nikolay M. © (26.04.05 18:40) [14]>
Что касается индексов, то все есть. Ведь в этой же базе, ничего не меняя, в понедельник все заработало. И на JOIN я тоже уже менял.
Безрезультатно.

← →
Nikolay M. © (2005-04-28 09:36) [16]

> Bless © (27.04.05 09:50) [15]

Приведи скрипты на создание всех таблиц, включая индексы.

← →
Bless © (2005-04-28 14:17) [17]

CREATE TABLE [dbo].[shapb_m_cache] ( [nzz] [int] IDENTITY (1, 1) NOT NULL , [comp_name] [nchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL , [nzs] [int] NOT NULL , [nzm] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL , [indi] [smallint] NOT NULL , [ob] [real_high] NULL , [st] [float] NOT NULL , [ozen] [float] NULL , [tzen] [float] NULL , [zsr] [float] NULL , [modi] [smallint] NOT NULL , [del] [bit] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Pozib_M] ( [nzz] [int] IDENTITY (1, 1) NOT NULL , [nzp] [int] NOT NULL , [indi] [smallint] NOT NULL , [znak] [varchar] (2) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL , [nzm] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL , [norma] [float] NOT NULL , [st] [real_low] NULL , [ozen] [real_low] NULL , [tzen] [real_low] NULL , [zsr] [real_low] NULL ) ON [PRIMARY] ALTER TABLE [dbo].[shapb_m_cache] WITH NOCHECK ADD CONSTRAINT [DF_shapb_m_cache_comp_name] DEFAULT (host_name()) FOR [comp_name], CONSTRAINT [DF_shapb_m_cache_st] DEFAULT (0) FOR [st], CONSTRAINT [DF_shapb_m_cache_state] DEFAULT (0) FOR [modi], CONSTRAINT [DF_shapb_m_cache_del] DEFAULT (0) FOR [del], CONSTRAINT [PK_shapb_m_cache] PRIMARY KEY NONCLUSTERED ( [nzz] ) ON [PRIMARY] ALTER TABLE [dbo].[Pozib_M] WITH NOCHECK ADD CONSTRAINT [DF_Pozib_M_st] DEFAULT (0) FOR [st], CONSTRAINT [PK_Pozib_M] PRIMARY KEY NONCLUSTERED ( [nzz] ) ON [PRIMARY] CREATE INDEX [pozib_m_nzp_index] ON [dbo].[Pozib_M]([nzp]) ON [PRIMARY] CREATE INDEX [pozib_m_nzm] ON [dbo].[Pozib_M]([nzm]) ON [PRIMARY]

← →
Fay © (2005-04-28 15:47) [18]

real_low, real_high ?

← →
Nikolay M. © (2005-04-28 15:56) [19]

> Fay © (28.04.05 15:47) [18]
> real_low, real_high ?

Я просто int поставил :)

> SELECT * FROM pozib_m pm
> INNER JOIN shapb_m_cache s ON pm.nzm=s.nzm
> WHERE s.nzs=@nzs AND s.comp_name=@comp AND s.indi<>1
> AND pm.nzp IN (SELECT nzz FROM #p) AND s.del<>1

А на shapb_m_cache вообще индексов нет? И какова структура #p?

← →
Nikolay M. © (2005-04-28 16:04) [20]

> ALTER TABLE [dbo].[shapb_m_cache] WITH NOCHECK ADD
> CONSTRAINT [DF_shapb_m_cache_comp_name] DEFAULT (host_name())
> FOR [comp_name],
> CONSTRAINT [DF_shapb_m_cache_st] DEFAULT (0) FOR [st],
> CONSTRAINT [DF_shapb_m_cache_state] DEFAULT (0) FOR [modi],
> CONSTRAINT [DF_shapb_m_cache_del] DEFAULT (0) FOR [del],
> CONSTRAINT [PK_shapb_m_cache] PRIMARY KEY NONCLUSTERED
>
> (
> [nzz]
> ) ON [PRIMARY

А почему бы не сделать ПК кластерным?

← →
Fay © (2005-04-28 16:10) [21]

Nikolay M. © (28.04.05 16:04) [20]
Зачем!?

← →
Nikolay M. © (2005-04-28 17:06) [22]

> Fay © (28.04.05 16:10) [21]
> Nikolay M. © (28.04.05 16:04) [20]
> Зачем!?

Чтобы, во-первых, связка с другими таблицами, ссылающимися на данную происходила по кластерному индексу, а, во-вторых, чтобы все прочие индексы в таблице также строились на основе кластерного индекса.

← →
Bless © (2005-04-28 17:19) [23]

>А почему бы не сделать ПК кластерным?

Вопрос как раз вовремя :)
Я после того, как сюда запостил, сделал первичный ключ в pozib_m кластерным и все заработало. Хотя пусть меня покрасят, если я понимаю почему.
Может ты знаешь? :)

В [5] же написано:
SELECT nzz INTO #p FROM pozib WHERE nzs=@nzs
nzz- целочисленное поле. И полей всего 91

>А на shapb_m_cache вообще индексов нет?

В нее часто вставляют/удаляют. Кроме того она небольшая, в средней 100-300 записей.

Fay [18]>
Эт моё творчество :)

← →
Nikolay M. © (2005-04-28 17:41) [24]

> В [5] же написано:
>
> SELECT nzz INTO #p
> FROM pozib WHERE nzs=@nzs

Проглядел. 91 - видимо, записей, а не полей. Да, на такой таблице индексы, скорее всего, будут лишними.

> Я после того, как сюда запостил, сделал первичный ключ в
> pozib_m кластерным и все заработало. Хотя пусть меня покрасят,
> если я понимаю почему.
> Может ты знаешь? :)

Сначала немного личного опыта: я почти во всех таблицах делаю ПК Identity+Clustered, исключение составляют таблицы, на которые не ссылаются другие таблицы или элементарные справочники типа (ID, WorkDate). Что касается твоего случая, могу предположить, что таблица активно участвует а апдейтах-инсертах-дилитах, а поскольку кластерный индекс у тебя отсутствовал, то индексы были основаны на RowID, а как он себя ведет при активной работе с таблицей - мне лично неведомо. Создание кластерного индекса и, как следствие, перестройка всех остальных индексов расставила все по местам. Полистай на досуге:
http://blogs.gotdotnet.ru/personal/Bezzus/PermaLink.aspx?guid=2ec662e4-9179-4663-8034-3f54bf198db9
http://blogs.gotdotnet.ru/personal/AlexGaas/PermaLink.aspx?guid=1c2fcc75-f9ad-4bd1-afe0-3ddc3209bcf9

Да, вот еще очень полезная статья, не сразу ее нашел:
http://www.sql.ru/articles/mssql/03013101Indexes.shtml

2 Nikolay M. © (28.04.05 17:48) [25]
>Да, вот еще очень полезная статья, не сразу ее нашел:
> http://www.sql.ru/articles/mssql/03013101Indexes.shtml

>> В SQL Server кластерный индекс является уникальным индексом по определению.
Бред

> Fay © (28.04.05 18:26) [26]
> >> В SQL Server кластерный индекс является уникальным индексом
> по определению.
> Бред

Может лучше сказать "я поленился внимательно прочитать текст до конца"? Эту статью прочитала не одна сотня человек, вряд-ли в ней может быть допущена настолько грубая ошибка. Неоднозначность такой формулировки - да, возможно, но дальше подробно описывается, что конкретно имеется ввиду.

Если существуют записи с одинаковыми значениями, SQL Server делает их уникальными, добавляя номера из внутреннего (невидимого снаружи) счетчика.

Nikolay M. © (28.04.05 19:37) [27]
> Если существуют записи с одинаковыми значениями, SQL
> Server делает их уникальными, добавляя номера из
> внутреннего (невидимого снаружи) счетчика.

Может лучше сказать "автор поленился подумать над значением слов "уникальный индекс""?

P.S.
"Телега ..." намного интереснее.

> автор поленился подумать над значением слов "уникальный
> индекс"

Тем не менее полезность статьи от этого не уменьшилась. К тому же автор прав: кластерный индекс действительно имеет дело с уникальными значениями, просто они не всегда в точности равны значениям индексируемого поля из таблицы.

>http://blogs.gotdotnet.ru/personal/Bezzus/PermaLink.aspx?guid=
>2ec662e4-9179-4663- 8034-3f54bf198db9

Полезная статья.
Правда, что-то я в BOL ничего не нашел по поводу
dbcc traceon(3604)
dbcc tab(@db_id, @tbl_id)

> dbcc traceon(3604)

Имхо, это рудимент, оставшийся от Sybase и потому недокументированный в SQL Server 2000.
http://www.cybersecurity.ru/manuals/data/mssql/1924.html
Флаг трассировки 3604 заставляет сервер передавать отладочную информацию в текущее соединение непосредственно клиенту

> dbcc tab(@db_id, @tbl_id)

http://www.sql.ru/subscribe/005.shtml

аааааааааа знаю .... это БУБЕН ПОМОГ :)

автор просто сказачник такого увлекательного сабжа еще не читал :)

жаль только помочь ничем не могу такой проблемы не возникало :) и как с ней бороться соответственно не знаю

Nikolay M.[32]>

Спасибо за инфу. И за предыдущие тоже. :)
Вопросов больше нет. На досуге поразбираюсь, что же это все-таки было (ведь в рабочей базе все само прошло к понедельнику, так что дело все-таки не в ключе). Благо теперь есть куда думать. Спасибо.

Lord Zmiy[33]>
>автор просто сказачник

Думаешь, я все придумал? А я ж еще не все говорил, чтоб меня совсем идиотом не посчитали. :)
1)
В [5], в неэффективном плане в пункте 4) на выходе действительно была 91 запись. Вот только через раз. А через другой раз - более 2 000 000 записей. Хотя и на конечный результат это никак не влияло, и последующий select * from #p давал 91 запись, как и положено.
2)
В тестовой базе тоже все прошло само собой (по-моему в среду) и мне пришлось еще раз восстановиться из BACKUP-а, чтоб мочь исследовать этот неприятный эффект.

>такого увлекательного сабжа еще не читал :)

Можешь выслать мне гонорар :)

> Спасибо за инфу. И за предыдущие тоже. :)

Да на здоровье. Тема неисчерпаема, на самом деле :)

> Lord Zmiy © (29.04.05 12:44) [33]

Сказал, что газы пустил... Купи себе жвачку.

Эффективность плана выполнения запроса Найти похожие ветки