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

Откуда: Н.Новгород -> Москва
Сообщений: 192
Помогите разобраться!

автор
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Дело было так:
дорабатывал процедуру в которой выполняется запрос
SELECT 
              CurR.ID
            , Cur.CurrenID
            , Cur.Kod
            , CurR.Rate
            , CurR.Koef
            , CurR.Data
        FROM 
            CurRate CurR    
            JOIN Curren Cur ON CurR.CurenID = Cur.CurrenID
        WHERE 
                Cur.CurrenID = @CurrencyID
            AND CurR.BankID = @BankID

построил индекс
CREATE INDEX IX_CurRate_BankID_CurenID  ON [dbo].[CurRate] ([BankID],[CurenID]) INCLUDE ([ID],[Data],[Koef],[Rate])

прогоняю процедуру и вижу план запроса: (TotalSubtreeCost = 0.0346387)

Rows         Executes     StmtText                                                                                                                                                                     StmtId       NodeId       Parent       PhysicalOp   LogicalOp    Argument                                                                                                                                                    DefinedValues                                            EstimateRows EstimateIO   EstimateCPU  AvgRowSize   TotalSubtreeCost OutputList                                                                              Warnings     Type         Parallel     EstimateExecutions 
----         --------     --------                                                                                                                                                                     ------       ------       ------       ----------   ---------    --------                                                                                                                                                    -------------                                            ------------ ----------   -----------  ----------   ---------------- ----------                                                                              --------     ----         --------     ------------------ 
3144         1            Nested Loops(Inner Join)                                                                                                                                                     0            0                         Nested Loops Inner Join                                                                                                                                                                                                                        3144         0            0.0131419    36           0.0346387        [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate], [Cur].[CurrenID], [Cur].[Kod]              PLAN_ROW     0            1                  
1            1              |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002]))                                                                                                                  0            1            0            Nested Loops Inner Join   OUTER REFERENCES:([Bmk1002])                                                                                                                                                                                         1            0            4.18E-006    16           0.00657038       [Cur].[CurrenID], [Cur].[Kod]                                                                        PLAN_ROW     0            1                  
1            1              |    |--Index Seek(OBJECT:([LMK02].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD)                                            0            2            1            Index Seek   Index Seek   OBJECT:([LMK02].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD                                                 [Bmk1002], [Cur].[CurrenID]                              1            0.003125     0.0001581    19           0.0032831        [Bmk1002], [Cur].[CurrenID]                                                                          PLAN_ROW     0            1                  
1            1              |    |--RID Lookup(OBJECT:([LMK02].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD)                                                            0            4            1            RID Lookup   RID Lookup   OBJECT:([LMK02].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD                                                                 [Cur].[Kod]                                              1            0.003125     0.0001581    12           0.0032831        [Cur].[Kod]                                                                                          PLAN_ROW     0            1                  
3144         1              |--Index Seek(OBJECT:([LMK02].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD) 0            8            0            Index Seek   Index Seek   OBJECT:([LMK02].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate] 3144         0.011311     0.0036154    27           0.0149264        [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]                                             PLAN_ROW     0            1                  

продолжаю дорабатывать процедуру, несколько раз пересоздаю, прогняю
вижу тот же план. НО! (TotalSubtreeCost = 0.00985766) !!! И совсем другое распределение Costs по таскам... И странная картина EstimateRows = 1 везде... несколько раз выполняю процедуру - картина та же.
EstimateIO, EstimateCPU, TotalSubtreeCost показывают совсем другие значения...
Rows         Executes     StmtText                                                                                                                                                                     StmtId       NodeId       Parent       PhysicalOp   LogicalOp    Argument                                                                                                                                                    DefinedValues                                            EstimateRows EstimateIO   EstimateCPU  AvgRowSize   TotalSubtreeCost OutputList                                                                              Warnings     Type         Parallel     EstimateExecutions 
----         --------     --------                                                                                                                                                                     ------       ------       ------       ----------   ---------    --------                                                                                                                                                    -------------                                            ------------ ----------   -----------  ----------   ---------------- ----------                                                                              --------     ----         --------     ------------------ 
3144         1            Nested Loops(Inner Join)                                                                                                                                                     0            0                         Nested Loops Inner Join                                                                                                                                                                                                                        1            0            4.18E-006    36           0.00985766       [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate], [Cur].[CurrenID], [Cur].[Kod]              PLAN_ROW     0            1                  
1            1              |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002]))                                                                                                                  0            1            0            Nested Loops Inner Join   OUTER REFERENCES:([Bmk1002])                                                                                                                                                                                         1            0            4.18E-006    16           0.00657038       [Cur].[CurrenID], [Cur].[Kod]                                                                        PLAN_ROW     0            1                  
1            1              |    |--Index Seek(OBJECT:([LMK02].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD)                                            0            2            1            Index Seek   Index Seek   OBJECT:([LMK02].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD                                                 [Bmk1002], [Cur].[CurrenID]                              1            0.003125     0.0001581    19           0.0032831        [Bmk1002], [Cur].[CurrenID]                                                                          PLAN_ROW     0            1                  
1            1              |    |--RID Lookup(OBJECT:([LMK02].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD)                                                            0            4            1            RID Lookup   RID Lookup   OBJECT:([LMK02].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD                                                                 [Cur].[Kod]                                              1            0.003125     0.0001581    12           0.0032831        [Cur].[Kod]                                                                                          PLAN_ROW     0            1                  
3144         1              |--Index Seek(OBJECT:([LMK02].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD) 0            8            0            Index Seek   Index Seek   OBJECT:([LMK02].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate] 1            0.003125     0.0001581    27           0.0032831        [CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]                                             PLAN_ROW     0            1                  
Пересоздаю индекс. И вижу первоначальное распределение по костам и первоначальный суммарный кост!!!

Пересоздаю процедуру - вижу второй вариант плана...

Разница костов планов аж в 3,5 раза. Второй вариант кажется скорее глюком. Данные в таблицах статичные, изменений и добавлений не было. Значения параметров одни и те же.

Никто не сталкивался с подобным? Это глюк?
29 май 09, 11:39    [7243305]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Змей,

в dbms это выглядит так:

К сообщению приложен файл. Размер - 0Kb
29 май 09, 11:49    [7243383]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Похоже у вас PK_Curren неклстерный и, вообще, в таблице Curren нет кластерного индекса о чем и говорит RID Lookup...
Включите (INCLUDE) в PK_Curren столбец Kod - RID Lookup и уйдет.
Потом можно и с разными планами поразбираться...
29 май 09, 12:20    [7243595]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
tpg,

да, в принципе , можно так сделать , но сильно это вряд ли повлияет на производительность - и так уже точное попадание в одну запись:) меня больше другое интересует почему пересоздание процедуры так влияет на план? вообще насколько этому плану (или тому как он показывается) можно верить?

Получается что возвращение 1-ой записи из Curren (Index Seeк + RID Lookup) в 2(два)! раза по костам больше 3144 раз Index Seek в CurRate ?? а join так и вообще не требует ресурсов? EstimateIO = 0 ; EstimateCPU = 4.18E-006. Странно это.. В DBMS и Profiler планы показываются одинаково..
29 май 09, 12:44    [7243754]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Glory
Member

Откуда:
Сообщений: 104760
Змей


Получается что возвращение 1-ой записи из Curren (Index Seeк + RID Lookup) в 2(два)! раза по костам больше 3144 раз Index Seek в CurRate ?? а join так и вообще не требует ресурсов? EstimateIO = 0 ; EstimateCPU = 4.18E-006. Странно это.. В DBMS и Profiler планы показываются одинаково..

Это же EstimateCost !! А не реальные затраты
29 май 09, 13:01    [7243880]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Получается что возвращение 1-ой записи из Curren (Index Seeк + RID
> Lookup) в 2(два)! раза по костам больше 3144 раз Index Seek в CurRate ??
> а join так и вообще не требует ресурсов? EstimateIO = 0 ; EstimateCPU =
> 4.18E-006. Странно это.. В DBMS и Profiler планы показываются одинаково..

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

Posted via ActualForum NNTP Server 1.4

29 май 09, 13:01    [7243882]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Glory
Змей


Получается что возвращение 1-ой записи из Curren (Index Seeк + RID Lookup) в 2(два)! раза по костам больше 3144 раз Index Seek в CurRate ?? а join так и вообще не требует ресурсов? EstimateIO = 0 ; EstimateCPU = 4.18E-006. Странно это.. В DBMS и Profiler планы показываются одинаково..

Это же EstimateCost !! А не реальные затраты


Действительно... Estimate с Execution попутал. Но скрины я привел именно Actual Execution Plan. а на них по 33% везде...

И все-таки главный вопрос - что за бифуркация такая происходит при пересоздании хранимой процедуры??

Ситуация у меня 100% воспроизводимая:
-убил индекс
-создал индекс
-выполнил процедуру , получил TotalSubtreeCost = 0.0346387 по стэйтменту
-пересоздал процедуру
-выполнил процедуру , получил TotalSubtreeCost = 0.00985766 по стэйтменту

этот запрос маленький, но если такое же с более тяжелыми запросами происходит?
29 май 09, 13:15    [7244006]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Glory
Member

Откуда:
Сообщений: 104760
Змей

этот запрос маленький, но если такое же с более тяжелыми запросами происходит?

Я лично не вижу никакой разницы в этих планах
Количесто и типы шагов одинаковы. Rows и Executes тоже одинаковы.
Если совпадут IO statistics и Reads/Writes/CPU из Профайлера, то отличий вообще не будет
29 май 09, 13:20    [7244042]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Glory,

Помониторил все с set statistics io on. В плане производительности все одинаково.
пробовал и на 2005-м и на 2008-м сервере, студию использовал как 2005, так и 2008. результат одинаковый.


И это удручает.... получается, что как мне сервер покажет косты в актульном плане зависит от того в какой последовательности я делаю create index & create procedure :(
и веса, которые я увижу могут ввести в заблуждение в том, какая именно часть запроса нуждается в оптимизации. Когда Index Seeк + RID Lookup по одной записи дают 66% запроса а Index Seeк по 3000 строк - остальные 33% это... мягко говоря лажа...
29 май 09, 14:32    [7244667]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
iljy
Guest
Змей
Glory,

Помониторил все с set statistics io on. В плане производительности все одинаково.
пробовал и на 2005-м и на 2008-м сервере, студию использовал как 2005, так и 2008. результат одинаковый.


И это удручает.... получается, что как мне сервер покажет косты в актульном плане зависит от того в какой последовательности я делаю create index & create procedure :(

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

и веса, которые я увижу могут ввести в заблуждение в том, какая именно часть запроса нуждается в оптимизации. Когда Index Seeк + RID Lookup по одной записи дают 66% запроса а Index Seeк по 3000 строк - остальные 33% это... мягко говоря лажа...

а что вас собственно смущает? существует такая вещь - read ahead. возможно вам везет, и при чтении страниц для первой выборки вы получаете страницы, подходящие для второй, соответственно для первой учитывается время физического чтения, а для второй - нет.
29 май 09, 14:39    [7244729]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
iljy
Guest
Змей,

а еще - при пересоздании индекса обновляется статистика и устраняется фрагментация, и основываясь на обновленных данных планировщик получает лучшие ожидаемые значения.
29 май 09, 14:45    [7244781]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
iljy,

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

К сообщению приложен файл (TEST.zip - 64Kb) cкачать
29 май 09, 18:52    [7246290]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
iljy
в общем случае непременно зависит, потому что план для процедуры обычно создается при компиляции, соответственно планировщик никак не может учесть индексы, которых нет.


т.е. если я создаю новый индекс, то в процедуре он не будет использоваться без перекомпиляции?
правильно я понимаю, что процедура будет принудительно перекомпилена при первом запуске после создания индекса на объектах , которые в процедуре используются?
29 май 09, 19:00    [7246313]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
iljy
Змей,

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


Но если данные статичные и индекс несколько раз пересоздавался, то статистика самая обновленная получается, а план все равно меняется (точнее сказать структура плана не меняется, меняются косты). И меняется только после пересоздания (точнее и после ALTER и после DROP/CREATE PROC)
29 май 09, 19:06    [7246329]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
iljy
Guest
Змей,

вот что получилось у меня:
первый exec:
Table 'Curren'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CurRate'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Curren'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 150 ms.
1	1	SELECT @CurrencyID = CurrenID           FROM Curren          WHERE Kod = @CurrencyCode	1	1	0	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	0,00657038	NULL	NULL	SELECT	0	NULL
1	1	  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))	1	2	1	Nested Loops	Inner Join	OUTER REFERENCES:([Bmk1000])	NULL	1	0	4,18E-06	11	0,00657038	[tempdb].[dbo].[Curren].[CurrenID]	NULL	PLAN_ROW	0	1
1	1	       |--Index Seek(OBJECT:([tempdb].[dbo].[Curren].[IX_Curren_Kod]), SEEK:([tempdb].[dbo].[Curren].[Kod]=[@CurrencyCode]) ORDERED FORWARD)	1	3	2	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[Curren].[IX_Curren_Kod]), SEEK:([tempdb].[dbo].[Curren].[Kod]=[@CurrencyCode]) ORDERED FORWARD	[Bmk1000]	1	0,003125	0,0001581	15	0,0032831	[Bmk1000]	NULL	PLAN_ROW	0	1
1	1	       |--RID Lookup(OBJECT:([tempdb].[dbo].[Curren]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)	1	5	2	RID Lookup	RID Lookup	OBJECT:([tempdb].[dbo].[Curren]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD	[tempdb].[dbo].[Curren].[CurrenID]	1	0,003125	0,0001581	11	0,0032831	[tempdb].[dbo].[Curren].[CurrenID]	NULL	PLAN_ROW	0	1
3144	1	SELECT                 CurR.ID              ,Cur.CurrenID              ,Cur.Kod              ,CurR.Rate              ,CurR.Koef              ,CurR.Data          FROM               CurRate CurR                  JOIN Curren Cur ON CurR.CurenID = Cur.CurrenID          WHERE                   Cur.CurrenID = @CurrencyID              AND CurR.BankID  = @BankID        --set statistics io off      --set statistics time off	2	1	0	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	0,00985766	NULL	NULL	SELECT	0	NULL
3144	1	  |--Nested Loops(Inner Join)	2	2	1	Nested Loops	Inner Join	NULL	NULL	1	0	4,18E-06	40	0,00985766	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate], [Cur].[CurrenID], [Cur].[Kod]	NULL	PLAN_ROW	0	1
1	1	       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002]))	2	3	2	Nested Loops	Inner Join	OUTER REFERENCES:([Bmk1002])	NULL	1	0	4,18E-06	16	0,00657038	[Cur].[CurrenID], [Cur].[Kod]	NULL	PLAN_ROW	0	1
1	1	       |    |--Index Seek(OBJECT:([tempdb].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD)	2	4	3	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD	[Bmk1002], [Cur].[CurrenID]	1	0,003125	0,0001581	19	0,0032831	[Bmk1002], [Cur].[CurrenID]	NULL	PLAN_ROW	0	1
1	1	       |    |--RID Lookup(OBJECT:([tempdb].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD)	2	6	3	RID Lookup	RID Lookup	OBJECT:([tempdb].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD	[Cur].[Kod]	1	0,003125	0,0001581	12	0,0032831	[Cur].[Kod]	NULL	PLAN_ROW	0	1
3144	1	       |--Index Seek(OBJECT:([tempdb].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD)	2	10	2	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]	1	0,003125	0,0001581	31	0,0032831	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]	NULL	PLAN_ROW	0	1
второй exec:
Table 'Curren'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CurRate'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Curren'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 31 ms.
1	1	SELECT @CurrencyID = CurrenID           FROM Curren          WHERE Kod = @CurrencyCode	1	1	0	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	0,00657038	NULL	NULL	SELECT	0	NULL
1	1	  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))	1	2	1	Nested Loops	Inner Join	OUTER REFERENCES:([Bmk1000])	NULL	1	0	4,18E-06	11	0,00657038	[tempdb].[dbo].[Curren].[CurrenID]	NULL	PLAN_ROW	0	1
1	1	       |--Index Seek(OBJECT:([tempdb].[dbo].[Curren].[IX_Curren_Kod]), SEEK:([tempdb].[dbo].[Curren].[Kod]=[@CurrencyCode]) ORDERED FORWARD)	1	3	2	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[Curren].[IX_Curren_Kod]), SEEK:([tempdb].[dbo].[Curren].[Kod]=[@CurrencyCode]) ORDERED FORWARD	[Bmk1000]	1	0,003125	0,0001581	15	0,0032831	[Bmk1000]	NULL	PLAN_ROW	0	1
1	1	       |--RID Lookup(OBJECT:([tempdb].[dbo].[Curren]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)	1	5	2	RID Lookup	RID Lookup	OBJECT:([tempdb].[dbo].[Curren]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD	[tempdb].[dbo].[Curren].[CurrenID]	1	0,003125	0,0001581	11	0,0032831	[tempdb].[dbo].[Curren].[CurrenID]	NULL	PLAN_ROW	0	1
3144	1	SELECT                 CurR.ID              , Cur.CurrenID              , Cur.Kod              , CurR.Rate              , CurR.Koef              , CurR.Data          FROM               CurRate CurR                  JOIN Curren Cur ON CurR.CurenID = Cur.CurrenID          WHERE                   Cur.CurrenID = @CurrencyID              AND CurR.BankID  = @BankID        --set statistics io off      --set statistics time off	2	1	0	NULL	NULL	NULL	NULL	3144	NULL	NULL	NULL	0,03613809	NULL	NULL	SELECT	0	NULL
3144	1	  |--Nested Loops(Inner Join)	2	2	1	Nested Loops	Inner Join	NULL	NULL	3144	0	0,01314192	40	0,03613809	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate], [Cur].[CurrenID], [Cur].[Kod]	NULL	PLAN_ROW	0	1
1	1	       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1002]))	2	3	2	Nested Loops	Inner Join	OUTER REFERENCES:([Bmk1002])	NULL	1	0	4,18E-06	16	0,00657038	[Cur].[CurrenID], [Cur].[Kod]	NULL	PLAN_ROW	0	1
1	1	       |    |--Index Seek(OBJECT:([tempdb].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD)	2	4	3	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[Curren].[PK_Curren] AS [Cur]), SEEK:([Cur].[CurrenID]=[@CurrencyID]) ORDERED FORWARD	[Bmk1002], [Cur].[CurrenID]	1	0,003125	0,0001581	19	0,0032831	[Bmk1002], [Cur].[CurrenID]	NULL	PLAN_ROW	0	1
1	1	       |    |--RID Lookup(OBJECT:([tempdb].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD)	2	6	3	RID Lookup	RID Lookup	OBJECT:([tempdb].[dbo].[Curren] AS [Cur]), SEEK:([Bmk1002]=[Bmk1002]) LOOKUP ORDERED FORWARD	[Cur].[Kod]	1	0,003125	0,0001581	12	0,0032831	[Cur].[Kod]	NULL	PLAN_ROW	0	1
3144	1	       |--Index Seek(OBJECT:([tempdb].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD)	2	10	2	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[CurRate].[IX_CurRate_BankID_CurenID] AS [CurR]), SEEK:([CurR].[BankID]=[@BankID] AND [CurR].[CurenID]=[@CurrencyID]) ORDERED FORWARD	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]	3144	0,01281039	0,0036154	31	0,01642579	[CurR].[ID], [CurR].[Data], [CurR].[Koef], [CurR].[Rate]	NULL	PLAN_ROW	0	1
обсудим чуть позже, я двигаю домой
29 май 09, 19:11    [7246346]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
iljy
Guest
Змей
iljy
Змей,

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


Но если данные статичные и индекс несколько раз пересоздавался, то статистика самая обновленная получается, а план все равно меняется (точнее сказать структура плана не меняется, меняются косты). И меняется только после пересоздания (точнее и после ALTER и после DROP/CREATE PROC)

это как раз понятно - обычно план для процедуры создается при создании либо первом вызове, и пересоздается соответственно после drop/create.

кстати на планах видно, что различаются ожидаемые количества строк, и на первом оно не соответствует реальному,- по-моему это явно указывает на несоответствие статистики индекса. И по первому плану ожидаемое время меньше, а реальное - больше. А то, что второй план быстрее - может объясняться уменьшением фрагментации. Кстати попробуйте пересоздать индекс и не пересоздавая выполнить процедуру - думаю что реальное время выполнение будет таким же, как и после пересоздания.
29 май 09, 22:50    [7246930]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
iljy
Змей
iljy
Змей,

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


Но если данные статичные и индекс несколько раз пересоздавался, то статистика самая обновленная получается, а план все равно меняется (точнее сказать структура плана не меняется, меняются косты). И меняется только после пересоздания (точнее и после ALTER и после DROP/CREATE PROC)

это как раз понятно - обычно план для процедуры создается при создании либо первом вызове, и пересоздается соответственно после drop/create.

если просто дропнуть индекс IX_CurRate_BankID_CurenID, а процедуру не трогать, план то заметно изменится:). т.е. перекомпиляция процедуры все-таки происходит.
iljy

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

В том то и дело что быстрее он только "на картинке" :) посмотрел , как советовал Glory
Glory
Если совпадут IO statistics и Reads/Writes/CPU из Профайлера, то отличий вообще не будет

У меня никаких отличий, тем более таких радикальных как 3 раза нет. Хотя глядя на разницу в TotalSubtreeCost можно было бы ожидать.
30 май 09, 10:28    [7247314]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
iljy
Guest
Змей

если просто дропнуть индекс IX_CurRate_BankID_CurenID, а процедуру не трогать, план то заметно изменится:). т.е. перекомпиляция процедуры все-таки происходит.

конечно происходит! у вас план начинает ссылаться на несуществующий объект! я имел ввиду "не происходит при отсутствии изменений структуры". хотя не готов сказать - воспримет она пересоздание индекса как изменение структуры или нет... видимо нет:)
Змей

В том то и дело что быстрее он только "на картинке" :)

?
для первого exec
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 150 ms.

для второго exec
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 31 ms.

это не картинка, это set statistics time on ;)

Змей

посмотрел , как советовал Glory
Glory
Если совпадут IO statistics и Reads/Writes/CPU из Профайлера, то отличий вообще не будет

У меня никаких отличий, тем более таких радикальных как 3 раза нет. Хотя глядя на разницу в TotalSubtreeCost можно было бы ожидать.


у меня статистика IO совпадает добуквенно. а TotalSubtreeCost она для первого плана считает неверно, потому что исходит из неверного предпологаемого числа строк. Сравните эти столбцы для первого и второго планов. Разницу во времени могу объяснить либо фрагметацией индекса, либо банальным кешированием его страниц. Либо чудом:)
30 май 09, 13:16    [7247503]     Ответить | Цитировать Сообщить модератору
 Re: Странные планы выполнения  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
iljy,

iljy
для первого exec
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 150 ms.

для второго exec
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 31 ms.


"Не верю!" (с) Станиславский :)

Я раз по 10 выполнял до и после пересоздания процедуры и у меня в обоих случаях CPU time от 0 до 16ms, elapsed time = от 300 до 600 ms гуляет в обоих случаях.
Кроме этого Read/Wrire Duration у меня ничем не отличаются. 3-х раз точно нет!

iljy
Разницу во времени могу объяснить либо фрагметацией индекса


use tempdb
go
declare 
    @database_id smallint
   ,@object_id int
   ,@object_id2 int
   ,@index_id smallint
   ,@index_id2 smallint
   ,@partition_number int
   ,@mode sysname 
   
select
    @database_id = db_id()
   ,@object_id = object_id('CurRate')
   ,@index_id = (select indid from sysindexes where id = object_id('CurRate') and name = 'IX_CurRate_BankID_CurenID')
   ,@object_id2 = object_id('Curren')
   ,@index_id2 = (select indid from sysindexes where id = object_id('Curren') and name = 'PK_Curren')
   ,@partition_number = NULL
   ,@mode = NULL
   
   
    select 
      fragment_count
    , avg_fragment_size_in_pages
    , avg_fragmentation_in_percent
    --, * 
    from 
        sys.dm_db_index_physical_stats(@database_id, @object_id, @index_id, @partition_number, @mode)
union all
    select 
      fragment_count
    , avg_fragment_size_in_pages
    , avg_fragmentation_in_percent
    --, * 
    from 
        sys.dm_db_index_physical_stats(@database_id, @object_id2, @index_id2, @partition_number, @mode)

avg_fragmentation_in_percent = 0 !

iljy
либо банальным кешированием его страниц

ну судя по тому что физически ничего не читается в обоих случаях, то да...
автор
Table 'CurRate'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Curren'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

iljy
Либо чудом:)


Чудо сложно повторить, а тут
Змей
Ситуация у меня 100% воспроизводимая:
-убил индекс
-создал индекс
-выполнил процедуру , получил TotalSubtreeCost = 0.0346387 по стэйтменту
-пересоздал процедуру
-выполнил процедуру , получил TotalSubtreeCost = 0.00985766 по стэйтменту


iljy
TotalSubtreeCost она для первого плана считает неверно, потому что исходит из неверного предпологаемого числа строк.
.

Вот и хочется понять - как это убрать. И почему пересоздание процедуры так влияет на расчет Costs.
1 июн 09, 11:27    [7250410]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить