Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Всем добрый вечер
Пытаюсь разобраться в плане выполнения запросов.
Есть запрос к тестовой базе AdventureWorks, Таблице Person.Addresses
Текст запроса:
select AddressID from Person.Address where City='Snohomish'
 and 
SYSTEM_USER='sa'



Хотел посмотреть как будет строиться план выполнения
если добавить "внешнее условие" не связанное с таблицей
получил вот такой план выполнения :

  |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks].[Person].[Address].[AddressID]))
       |--Filter(WHERE:(STARTUP EXPR(suser_sname()=CONVERT_IMPLICIT(nvarchar(4000),[@2],0))))
       |    |--Index Scan(OBJECT:([AdventureWorks].[Person].[Address].[IX_Address_StateProvinceID]))
       |--Clustered Index Seek(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID]), SEEK:([AdventureWorks].[Person].[Address].[AddressID]=[AdventureWorks].[Person].[Address].[AddressID]),  WHERE:([AdventureWorks].[Person].[Address].[City]=N'Snohomish') LOOKUP ORDERED FORWARD)


В связи с этим возник вопрос: зачем производится сканирование некластерного индекса
почему не используется кластерный индекс

Сообщение было отредактировано: 13 апр 15, 19:48
13 апр 15, 19:42    [17509620]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Виноват: почему не используется только кластерный индекс.
13 апр 15, 19:59    [17509672]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31437
Алексей
Виноват: почему не используется только кластерный индекс.
Кластерный индекс пришлось бы сканировать. Если таблица широкая, то это дороже сканирование узкого индекса.
А если при этом предполагаемое количество записей для 'Snohomish' маленькое, то выгоднее просканировать узкий инедкс, и потом лукапами выбрать сами записи.

Мне дрругое интересно - почему сервер не использует значение AddressID из самого некластерного индекса, ведь оно там неявно есть?
13 апр 15, 21:25    [17509896]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
alexeyvg,

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

может, поэтому позволяется рисовать не отражающую действительность картинку
(я только в кач-ве предположения, в свете последних событий,
когда мне явно дали почитать, что лучше смотреть SET STATISTICS PROFILE ON, меньше бурды покажет)

вот 2 плана, сравните сами

К сообщению приложен файл. Размер - 35Kb
13 апр 15, 21:55    [17509996]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
вот ссылка, к-ой со мной поделились:
Conversion and Arithmetic Errors
там вообще немного про другое,
но суть такая: включаем SET STATISTICS PROFILE ON
и смотрим, если реальное выполнение итератора 0 раз, то сами понимаете ...
13 апр 15, 22:01    [17510016]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Алексей,

Все исходя из решений стоимости.
Индекса с лидирующим столбцом нет - так что остается сканирование.
Исходя из оценочного числа строк, сервер решает что выгоднее всего просканировать самый узкий индекс содержащий AddresID, это IX_Address_StateProvinceID. В кластерный нужно залезть, чтобы достать для просканированных записей поле City, чтобы проверить предикат: WHERE:([AdventureWorks].[Person].[Address].[City]=N'Snohomish').

Если убрать из предиката условие and SYSTEM_USER='sa', то это повлияет на оценку строк и выгоднее станет другая стратегия доступа. (тоже будет в СЕ 2014, т.к. там поменялась логика вычисления комплексных предикатов)

Убедиться в том, что дешевле всего серверу показался именно такой вариант доступа можно сравнив стоимости всех прочих:
use AdventureWorks2008R2
go
-- Defult (Narrow Nonclustered Scan + Lookup): 0.05 units
select AddressID from Person.Address where City='Snohomish' and SYSTEM_USER='sa'
-- Nonclustereed Scan: 0.18 units
select AddressID from Person.Address with(index(IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode)) where City='Snohomish' and SYSTEM_USER='sa'
-- CLustered Scan: 0.27 units
select AddressID from Person.Address with(index(PK_Address_AddressID)) where City='Snohomish' and SYSTEM_USER='sa'
13 апр 15, 22:04    [17510025]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
o-o
вот 2 плана, сравните сами

Прослушивание параметров (автопараметризация, обратите внимание, что ваш запрос параметризован) + Кэширование (разные комментарии вынуждают перекомпилировать батч и заново строить план для каждого из них). Так что логины ни при чем.

Кол-во выполнений - за это отвечает Start Up фильтр (-Filter(WHERE:(STARTUP EXPR(suser_sname()=CONVERT_IMPLICIT(nvarchar(4000),[@2],0))))) Там в условии написан предикат, который определяет будет ли реально выполняться часть кода под фильтром или нет.
13 апр 15, 22:36    [17510110]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
invm
Member

Откуда: Москва
Сообщений: 9404
SomewhereSomehow,

Почему тогда получаем разные планы тут:
use AdventureWorks2008R2;
go

declare @l sysname = 'sa';

set statistics profile on;

select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' option (recompile);
/*
select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' option (recompile);
  |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks2008R2].[Person].[Address].[AddressID]))
       |--Filter(WHERE:(STARTUP EXPR(suser_sname()=N'sa')))
       |    |--Index Scan(OBJECT:([AdventureWorks2008R2].[Person].[Address].[IX_Address_StateProvinceID]))
       |--Clustered Index Seek(OBJECT:([AdventureWorks2008R2].[Person].[Address].[PK_Address_AddressID]), SEEK:([AdventureWorks2008R2].[Person].[Address].[AddressID]=[AdventureWorks2008R2].[Person].[Address].[AddressID]),  WHERE:([AdventureWorks2008R2].[Person].[Address].[City]=N'Snohomish') LOOKUP ORDERED FORWARD)
*/

execute as login = N'sa';
select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' option (recompile);
revert;
/*
select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' option (recompile);
  |--Filter(WHERE:(STARTUP EXPR(suser_sname()=N'sa')))
       |--Index Scan(OBJECT:([AdventureWorks2008R2].[Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]),  WHERE:([AdventureWorks2008R2].[Person].[Address].[City]=N'Snohomish'))
*/

select AddressID from Person.Address where City='Snohomish' and system_user = @l;
/*
select AddressID from Person.Address where City='Snohomish' and system_user = @l;
  |--Filter(WHERE:(STARTUP EXPR(suser_sname()=[@l])))
       |--Index Scan(OBJECT:([AdventureWorks2008R2].[Person].[Address].[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]),  WHERE:([AdventureWorks2008R2].[Person].[Address].[City]=N'Snohomish'))
*/

set statistics profile off;
?
13 апр 15, 22:50    [17510154]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
invm
Member

Откуда: Москва
Сообщений: 9404
Отвечу сам себе:
select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' /*false*/ option (recompile);
/*0.0529 parrots*/
execute as login = 'sa';
select AddressID from Person.Address where City='Snohomish' and system_user = N'sa' /*true*/ option (recompile);
/*0.19375 parrots*/
select AddressID from Person.Address with (index = IX_Address_StateProvinceID) where City='Snohomish' and system_user = N'sa' /*true*/ option (recompile);
/*4.31541 parrots*/
revert;

Значит на оценку влияет Filter с Startup Expression Predicate.
13 апр 15, 23:09    [17510202]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
SomewhereSomehow,
Да,но если условие system_user=Sa то будет сканирование кластерного индекса. Почему во втором случае не будет использоваться такая же оценка стоимости?
14 апр 15, 06:36    [17510645]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
invm
Почему тогда получаем разные планы тут

Ну да, тут понятно почему. Я же говорил про эксперимент o-o. Если в нем взять, например, запрос слева, выполнить под одним логином, и его же выполнить под другим логином – план будет тот же самый. План - в зависимости от того, какая была оценка для предиката «and SYSTEM_USER='sa'» (точнее даже SYSTEM_USER=@2) в момент построения плана. Этот же план закэшируется и будет использоваться, в том числе и для другого логина. В этом смысле логин не влияет на то, будет ли меняться план, вот что имелось ввиду. Иронично, что в примере из-за разных комментариев – получаются разные батчи, каждый из которых сервер оптимизирует заново, поэтому разные планы. То же самое вы и показали при помощи option(recompile).

Start Up фильтр влияет, но в более общем смысле, влияет вообще, что там в предикате (ну и версия, в 2014 картина другая):
-- Under Win\Login:
select AddressID from Person.Address where City='Snohomish' and SYSTEM_USER='sa' -- Scan + Lookup + StartUp
select AddressID from Person.Address where City='Snohomish' and (SYSTEM_USER='sa' or SYSTEM_USER='1') -- Scan + StartUp
select AddressID from Person.Address where City='Snohomish' and (SYSTEM_USER='sa' or SYSTEM_USER='1') and StateProvinceID+1 = 1+1 -- Scan + Lookup + StartUp
select AddressID from Person.Address where City='Snohomish' or SYSTEM_USER='sa' -- Scan + no StartUp


Алексей,

Оценка стоимости в двух разных планах отличается, т.к. отличается число строк. Число строк отличается, т.к. отличается значение, которое принимает функция SYSTEM_USER в зависимости от контекста.

Когда план компилируется, значение в момент компиляции неизвестно, берется текущее значение. Оно используется в качестве константы времени выполнения (Runtime Constant).
Условно можно было бы сказать, что оптимизатор строит план для вот такого выражения:
select AddressID from Person.Address where City='Snohomish' and RunTimeConstant('Domain\Login')='sa'


Понятно, что Domain\Login != sa и будет возвращено 0 строк, но мы не знаем в каком контексте это будет выполняться и не можем полностью исключить условие, по этому – оцениваем минимумом 1 строка (плюс на деле влияние оказывают и другие предикаты, как они скомбинированы и т.д.).

Разумеется, если бы план строился для такого значения:
select AddressID from Person.Address where City='Snohomish' and RunTimeConstant('sa')='sa'

То sa=sa, это потенциально все строки, так что никакой оценки в 1 строку.

Вот пример (будьте аккуратны, чистится кэш не выполняйте на рабочем сервере, select 1 – чтобы убрать простую параметризацию, чтобы не усложнять).
-- Warning FREE cache!
dbcc freeproccache;
go
-- Query
select AddressID, (select 1) from Person.Address where City='Snohomish' and SYSTEM_USER='sa'
go
execute as login = 'sa';
go
-- Query
select AddressID, (select 1) from Person.Address where City='Snohomish' and SYSTEM_USER='sa'
go
revert;
go

Запустим профайлер.
Выберем события:
- Showplan All For Query Compile – показывает план, когда он в первый раз был скомпилирован для запроса;
- Showplan Statistics Profile – действительный план, который был использован для выполнения;
- SQL:BatchCompleted – завершение обработки пакета инструкций.

Запустим пример из под логина не равного sa, и посмотрим последовательность действий.
Картинка с другого сайта.

1. Очищаем кэш.
2. Хотим выполнить запрос, проверяем кэш, там пусто, плана нет, строим план запроса. При этом используется текущее значение, которое возвращает SYSTEM_USER. Это, допустим ‘Domain\Login’. Оценка 1 строка, получаем соотв. план.
3. Используем построенный план для выполнения запроса.
4. Завершили выполнение пакета.
5. Переключаем контекст на другой логин.
6. Смотрим в кэш. План в кэше есть (логин не является ключом плана и его смена не влечет ре-компиляции) построен на шаге 2.
Используем тот же план, что был построен на шаге 2, но тут функция возвращает другое значение, отличное от того, для которого был скомпилирован запрос. Видно что возвращаются все строки. Этакий эффект parameter sniffing.
7. Завершили выполнение пакета.

Если на шаге 6, сделать рекомпиляцию (например, другой батч из-за комментариев - вариант о-о или option(recompile) - вариант invm), то значение константы времени выполнения SYSTEM_USER будет «прослушано» заново. И соответственно мы получим другое число строк, стоимость и план.
14 апр 15, 09:54    [17511213]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
вроде сегодня без фокусов и ему действительно наплевать на исполняющий логин.
вот у меня выполняется одно и то же под двумя разными логинами,
в одном окне исполнитель совпадает с логином первого запроса,
во втором -- с логином второго, никаких комментариев и планы одинаковые для обоих исполнителей,
зависят только от логина в запросе.

вроде теперь сошлось :)

а к ТС:
у меня в плане скана кластерного нет вообще никогда, хоть все условия с логином поубирать вообще.
может, вы плохо посмотрели?

К сообщению приложен файл. Размер - 110Kb
14 апр 15, 10:19    [17511311]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Добрый вечер

Да, Вы правы, идет сканирование некластерного индекса IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Сканирования кластерного индекса нет.
14 апр 15, 23:50    [17515786]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
в общих чертах я понял...
спасибо за развернутые объяснения.
15 апр 15, 00:04    [17515849]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Добрый день

При повторном прочтении возник вопрос
А что такое лидирующий индекс. Не могу найти по нему инфы.

заранее спасибо
15 апр 15, 11:26    [17517223]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Пардон лидирующий столбец индекса
15 апр 15, 11:26    [17517227]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
вариант для ленивых (игра "угадайте логин"),
как убедиться в описанном выше про прослушивание (без профайлера).
вот все, что понадобится:
--dbcc freeproccache;

select AddressID from Person.Address where City='Snohomish'
 and 
SYSTEM_USER='sa' -- существующий логин(1)

select AddressID from Person.Address where City='Snohomish'
 and 
SYSTEM_USER='hd03\ee25989' -- существующий логин(2)

select AddressID from Person.Address where City='Snohomish'
 and 
SYSTEM_USER='RRRR' -- несуществующий логин

select AddressID from Person.Address where City='Snohomish'
 and 
SYSTEM_USER='KKKK' -- несуществующий логин

выполняем dbcc freeproccache (выделив его без комментария. текст в окне вообще не меняем в ходе всего эксперимента)
жмем на Estimated Execution Plan.
убеждаемся, что он "прослушал" логин:
Nested Loops (условное название "бурда", т.к. это для случая, когда строк не будет вообще) появится
в плане всех запросов, кроме того, где подставлен ваш текущий логин.

теперь в этом же окне меняем соединение, делаем его под другим логином (Connection --> Change connection)
снова жмем Estimated Execution Plan -- не отражает ничего. картинка та же.
ок, снова делаем dbcc freeproccache
и снова просим Estimated Execution Plan.
на этот раз картинка сменится, ваш текущий логин тот, для чьего запроса в плане нет Nested Loops

P.S. вместо dbcc freeproccache можно вписывать комментарии,
перепрослушает и снова "угадает логин" :)
15 апр 15, 11:37    [17517308]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
А про лидирующий столбец не подскажете?
15 апр 15, 11:56    [17517428]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
Алексей,

да я даже поиском по этой теме не нахожу "лидирующий столбец",
кроме как в ваших же вопросах про него.
покажите, где вы его взяли, может, это так перевели тот столбец, к-ый указан первым в create index?
15 апр 15, 12:09    [17517485]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
ответ от SomewhereSomehow
автор
Алексей,

Все исходя из решений стоимости.
Индекса с лидирующим столбцом нет - так что остается сканирование.
15 апр 15, 12:17    [17517538]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
Алексей,

а, ну точно, мой поиск был на "точное совпадение" :)
да, имелось в виду, что нет подходящего индекса,
т.е. чтобы интересующий нас City был упомянут первым в
create index .. on .. (field1,..., fieldN)
15 апр 15, 12:25    [17517581]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Для составного индекса нашел вот такое определение
http://studopedia.ru/3_177808_ispolzovanie-indeksov.html
Обращение к составному индексу возможно только в том случае, если в условиях выбора участвуют столбцы, представляющие собой лидирующую часть составного индекса. Если индекс, например, включает поля (X, Y, Z), то обращение к индексу будет происходить в тех случаях, когда в условии запроса участвуют поля XYZ, XY или X, причём именно в таком порядке.

Совсем запутался..... Во втором случае когда логин совпадает сканируется индекс IX_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode, при этом индекс не должен использоваться, поскольку столбец City не является лидирующим. Наверное это имелось ввиду.....
15 апр 15, 12:37    [17517661]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Тогда интересно что повлияло выбор индекса в случае когда логин не совпадает. Почему он не взял тот же индекс а использовал другой.
15 апр 15, 12:43    [17517723]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
Алексей,

да, в составном индексе он так и называется:
composite index leading column,
я иногда на русском торможу :)

про сканирование можно сказать так:
когда нет подходящего индекса (т.е. где столбец из where -- лидирующий),
то остается сканировать.
и тут уже неважно, индекс, не индекс, лишь бы там было все, что надо.
разумеется, выберет тот индекс, к-ый меньше всего места занимает.
им оказался IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
т.к. там есть City и AddressID тоже, т.к. это ключ кластерного

я скорее больше не понимаю, зачем в "неисполняемый" план попал Nested Loops.
т.е. я конечно вижу доводы по этому поводу:
SomewhereSomehow

Убедиться в том, что дешевле всего серверу показался именно такой вариант доступа можно сравнив стоимости всех прочих:
use AdventureWorks2008R2
go
-- Defult (Narrow Nonclustered Scan + Lookup): 0.05 units
select AddressID from Person.Address where City='Snohomish' and SYSTEM_USER='sa'
-- Nonclustereed Scan: 0.18 units
select AddressID from Person.Address with(index(IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode)) where City='Snohomish' and SYSTEM_USER='sa'
-- CLustered Scan: 0.27 units
select AddressID from Person.Address with(index(PK_Address_AddressID)) where City='Snohomish' and SYSTEM_USER='sa'

но считаю(IMHO!!!), что план с Nested Loops -- туфта.

потому что делать двойную работу еще и по лукапу
(да-да, по лукапу, к-ый все равно что скан всей таблицы,
т.к. что вообще есть в этом IX_Address_StateProvinceID? ключ кластерного?
а условие where на него есть? а раз нету, то сканировать кластерный, а уж шире его нет ничего), -- это безобразие,
т.е. мое мнение "идеального плана" для этого случая такое, что надо выбирать скан IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode.
а тот план -- для отвода глаз. отговорка типа: зная, что не будет исполяться, напишу "что угодно".
еще раз -- это только мое мнение.
15 апр 15, 13:02    [17517934]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по плану выполнения  [new]
o-o
Guest
не знаю, сойдет ли как доказательство "туфтовости" плана NL + IX_Address_StateProvinceID,
но предлагаю такой эксперимент:
выбираем ВСЕ поля Person.Address без всяких условий.
что в плане? очевидный скан всей таблицы, что же еще.
а теперь добавим проверку логина.
и уж тут тоже всего 2 варианта: все или ничего.

а теперь объявляю конкурс на лучшее оправдание появления в плане NL.
какой еще NL, если мне все равно нужна вся таблица (ну или не нужна)???
но как же, снова в плане NL + IX_Address_StateProvinceID.

предлагаю даже первое правдоподобное предположение:
в заведомо невыполняемом плане всегда будет красоваться NL + самый узкий индекс.
этакая видимость проделанной работы: "я честно поискал самый узкий индекс"
(предположение не мое, автор может забрать себе приз, если других предложений не будет)

К сообщению приложен файл. Размер - 69Kb
15 апр 15, 13:32    [17518181]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить