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

Откуда: Киев
Сообщений: 57
Здравствуйте!
Досталось мне некое детище в виде приложения и базы к нему, выглядит, честно скажу, вполне ужасно.
Однако работает уже лет 7, так что глобально поменять в нем что то сложно.

Есть некий отвратительный запрос, который зашит в коде, переписать его не получится, так что борюсь разве что созданием индексов, помогаю оптимизатору.

вот часть запроса, которая работает довольно странным образом:

SELECT 
		s.WORKFLOWINSTANCE,
		s.ID STEP,
		wd.DESCRIPTION INSTANCEDESCRIPTION,
		s.STATUS STEPSTATUS,
		s.TYPE STEPTYPE,
		s.STARTTIME,
		u.NAME SENTBY,
		wi.WORKFLOWVERSION,
		s.STEPCODE,
		wi.WORKFLOWSTATUS,
		ws.DESCRIPTION WORKFLOWSTATUSDESCRIPTION,
		wv.WORKFLOW,
		wv.WORKFLOWDESCRIPTION,
		wv.STEPDESCRIPTION,
		ISNULL(wi.PRIORITY, 0) PRIORITY
FROM   NFWDTT_CURRENTUSERPOSITION CUP
JOIN NFWDTT_DELEGATION D ON  D.ID = CUP.DELEGATION
JOIN NFWDTT_POOL P ON  P.[USER] = D.OWNER
JOIN NFWDTT_STEP S ON  S.ID = P.STEP
LEFT JOIN NFWDTT_WORKFLOWDESCRIPTION wd(NOLOCK) ON  s.WORKFLOWINSTANCE = wd.WORKFLOWINSTANCE AND wd.LANGUAGE = 'ru'
LEFT  JOIN NFWDFT_USER u(NOLOCK) ON  s.SENTBY = u.CODE 
inner  JOIN NFWDTT_WORKFLOWINSTANCE wi(NOLOCK) ON  s.WORKFLOWINSTANCE = wi.ID
LEFT JOIN NFWDFT_WORKFLOWSTATUS ws(NOLOCK) ON  wi.WORKFLOWSTATUS = ws.STATUS AND ws.LANGUAGE = 'ru'
JOIN NFWVW_WORKFLOWVERSION wv(NOEXPAND) ON  wi.WORKFLOWVERSION = wv.WORKFLOWVERSION AND wv.LANGUAGE = 'ru'
WHERE  CUP.[USER] = 'User'
AND CUP.DELEGATION IS NOT NULL
AND S.STATUS = 'P'
AND wi.STATUS = 'C'
AND wv.STEPCODE = s.STEPCODE
AND (CUP.WORKFLOW IS NULL OR CUP.WORKFLOW = wv.WORKFLOW)


51	1	  |--Nested Loops(Left Outer Join, WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWSTATUS] as [wi].[WORKFLOWSTATUS]=[NFWDFT_WORKFLOWSTATUS].[STATUS] as [ws].[STATUS]))	1	2	1	Nested Loops	Left Outer Join	WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWSTATUS] as [wi].[WORKFLOWSTATUS]=[NFWDFT_WORKFLOWSTATUS].[STATUS] as [ws].[STATUS])
51	1	       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([S].[SENTBY]))	1	3	2	Nested Loops	Left Outer Join	OUTER REFERENCES:([S].[SENTBY])
51	1	       |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([S].[WORKFLOWINSTANCE]))	1	4	3	Nested Loops	Left Outer Join	OUTER REFERENCES:([S].[WORKFLOWINSTANCE])
0	0	       |    |    |--Compute Scalar(DEFINE:([Expr1017]=[NFWDTT_WORKFLOWINSTANCE].[PRIORITY] as [wi].[PRIORITY]))	1	5	4	Compute Scalar	Compute Scalar	DEFINE:([Expr1017]=[NFWDTT_WORKFLOWINSTANCE].[PRIORITY] as [wi].[PRIORITY])
51	1	       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [wv].[WORKFLOWVERSION], [Expr1022]) OPTIMIZED WITH UNORDERED PREFETCH)	1	6	5	Nested Loops	Inner Join	OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [wv].[WORKFLOWVERSION], [Expr1022]) OPTIMIZED WITH UNORDERED PREFETCH
165455	1	       |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([CUP].[WORKFLOW], [S].[STEPCODE], [Expr1021]) WITH UNORDERED PREFETCH)	1	9	6	Nested Loops	Inner Join	OUTER REFERENCES:([CUP].[WORKFLOW], [S].[STEPCODE], [Expr1021]) WITH UNORDERED PREFETCH
1027	1	       |    |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([D].[OWNER]) OPTIMIZED)	1	11	9	Nested Loops	Inner Join	OUTER REFERENCES:([D].[OWNER]) OPTIMIZED
26	1	       |    |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CUP].[DELEGATION]))	1	13	11	Nested Loops	Inner Join	OUTER REFERENCES:([CUP].[DELEGATION])
29	1	       |    |    |         |    |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_CURRENTUSERPOSITION].[Idx_NFWDTT_CURRENTUSERPOSITION] AS [CUP]), SEEK:([CUP].[USER]='User') ORDERED FORWARD)	1	14	13	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_CURRENTUSERPOSITION].[Idx_NFWDTT_CURRENTUSERPOSITION] AS [CUP]), SEEK:([CUP].[USER]='User') ORDERED FORWARD
26	29	       |    |    |         |    |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_DELEGATION].[PK_NFWDTT_DELEGATION] AS [D]), SEEK:([D].[ID]=[NFWDTT_CURRENTUSERPOSITION].[DELEGATION] as [CUP].[DELEGATION]) ORDERED FORWARD)	1	15	13	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_DELEGATION].[PK_NFWDTT_DELEGATION] AS [D]), SEEK:([D].[ID]=[NFWDTT_CURRENTUSERPOSITION].[DELEGATION] as [CUP].[DELEGATION]) ORDERED FORWARD
1027	26	       |    |    |         |    |    |--Index Seek(OBJECT:([NFWDTT_STEP].[Idx_NFWDTT_STEP_USER_STATUS] AS [S]), SEEK:([S].[USER]=[NFWDTT_DELEGATION].[OWNER] as [D].[OWNER]),  WHERE:([NFWDTT_STEP].[STATUS] as [S].[STATUS]='U') ORDERED FORWARD)	1	16	11	Index Seek	Index Seek	OBJECT:([NFWDTT_STEP].[Idx_NFWDTT_STEP_USER_STATUS] AS [S]), SEEK:([S].[USER]=[NFWDTT_DELEGATION].[OWNER] as [D].[OWNER]),  WHERE:([NFWDTT_STEP].[STATUS] as [S].[STATUS]='U') ORDERED FORWARD
165455	1027	       |    |    |         |    |--Clustered Index Seek(OBJECT:([NFWVW_WORKFLOWVERSION].[IX_WORKFLOWVERS] AS [wv]), SEEK:([wv].[LANGUAGE]='ru' AND [wv].[STEPCODE]=[NFWDTT_STEP].[STEPCODE] as [S].[STEPCODE]),  WHERE:([NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW] IS NULL OR [NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW]=[NFWVW_WORKFLOWVERSION].[WORKFLOW] as [wv].[WORKFLOW]) ORDERED FORWARD)	1	17	9	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWVW_WORKFLOWVERSION].[IX_WORKFLOWVERS] AS [wv]), SEEK:([wv].[LANGUAGE]='ru' AND [wv].[STEPCODE]=[NFWDTT_STEP].[STEPCODE] as [S].[STEPCODE]),  WHERE:([NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW] IS NULL OR [NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW]=[NFWVW_WORKFLOWVERSION].[WORKFLOW] as [wv].[WORKFLOW]) ORDERED FORWARD
51	165455	       |    |    |         |--Clustered Index Seek(OBJECT:([NFWDTT_WORKFLOWINSTANCE].[PK_NFWDTT_WORKFLOWINSTANCE] AS [wi]), SEEK:([wi].[ID]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWVERSION] as [wi].[WORKFLOWVERSION]=[NFWVW_WORKFLOWVERSION].[WORKFLOWVERSION] as [wv].[WORKFLOWVERSION] AND [NFWDTT_WORKFLOWINSTANCE].[STATUS] as [wi].[STATUS]='C') ORDERED FORWARD)	1	18	6	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_WORKFLOWINSTANCE].[PK_NFWDTT_WORKFLOWINSTANCE] AS [wi]), SEEK:([wi].[ID]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWVERSION] as [wi].[WORKFLOWVERSION]=[NFWVW_WORKFLOWVERSION].[WORKFLOWVERSION] as [wv].[WORKFLOWVERSION] AND [NFWDTT_WORKFLOWINSTANCE].[STATUS] as [wi].[STATUS]='C') ORDERED FORWARD
51	51	       |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_WORKFLOWDESCRIPTION].[PK_NFWDTT_WORKFLOWDESCRIPTION] AS [wd]), SEEK:([wd].[WORKFLOWINSTANCE]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE] AND [wd].[LANGUAGE]='ru') ORDERED FORWARD)	1	22	4	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_WORKFLOWDESCRIPTION].[PK_NFWDTT_WORKFLOWDESCRIPTION] AS [wd]), SEEK:([wd].[WORKFLOWINSTANCE]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE] AND [wd].[LANGUAGE]='ru') ORDERED FORWARD
35	51	       |    |--Clustered Index Seek(OBJECT:([NFWDFT_USER].[PK_NFWDFT_USER] AS [u]), SEEK:([u].[code]=[NFWDTT_STEP].[SENTBY] as [S].[SENTBY]) ORDERED FORWARD)	1	23	3	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDFT_USER].[PK_NFWDFT_USER] AS [u]), SEEK:([u].[code]=[NFWDTT_STEP].[SENTBY] as [S].[SENTBY]) ORDERED FORWARD
204	51	       |--Clustered Index Scan(OBJECT:([NFWDFT_WORKFLOWSTATUS].[PK_NFWDFT_WORKFLOWSTATUS_NFW_ID] AS [ws]), WHERE:([NFWDFT_WORKFLOWSTATUS].[LANGUAGE] as [ws].[LANGUAGE]=N'ru'))	1	24	2	Clustered Index Scan	Clustered Index Scan	OBJECT:([NFWDFT_WORKFLOWSTATUS].[PK_NFWDFT_WORKFLOWSTATUS_NFW_ID] AS [ws]), WHERE:([NFWDFT_WORKFLOWSTATUS].[LANGUAGE] as [ws].[LANGUAGE]=N'ru')



при этом Estimated Rows в тысячи раз меньше, чем Actual Rows, а статистика I/O показывает огромное число логических чтений таблицы NFWDTT_WORKFLOWINSTANCE.

(51 row(s) affected)
Table 'NFWDFT_WORKFLOWSTATUS'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDFT_USER'. Scan count 0, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_WORKFLOWDESCRIPTION'. Scan count 0, logical reads 153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_WORKFLOWINSTANCE'. Scan count 0, logical reads 506714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWVW_WORKFLOWVERSION'. Scan count 1027, logical reads 8208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_STEP'. Scan count 26, logical reads 1652, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_DELEGATION'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_CURRENTUSERPOSITION'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

В план показывает использование исключительно Nested Loops для соединений таблиц.

если добавить всего один хинт в строку:

inner  JOIN NFWDTT_WORKFLOWINSTANCE wi(NOLOCK) ON  s.WORKFLOWINSTANCE = wi.ID


и поменять её на

[src]inner loop JOIN NFWDTT_WORKFLOWINSTANCE wi(NOLOCK) ON  s.WORKFLOWINSTANCE = wi.ID


По логике, это не должно ничего поменять, так как в запросе и так используется везде Nested Loops, но план меняется:


Executes	Rows	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument

1	51	  |--Nested Loops(Inner Join, OUTER REFERENCES:([CUP].[WORKFLOW], [S].[STEPCODE], [wi].[WORKFLOWVERSION], [Expr1021]) WITH UNORDERED PREFETCH)	1	2	1	Nested Loops	Inner Join	OUTER REFERENCES:([CUP].[WORKFLOW], [S].[STEPCODE], [wi].[WORKFLOWVERSION], [Expr1021]) WITH UNORDERED PREFETCH
1	51	       |--Nested Loops(Left Outer Join, WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWSTATUS] as [wi].[WORKFLOWSTATUS]=[NFWDFT_WORKFLOWSTATUS].[STATUS] as [ws].[STATUS]))	1	4	2	Nested Loops	Left Outer Join	WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWSTATUS] as [wi].[WORKFLOWSTATUS]=[NFWDFT_WORKFLOWSTATUS].[STATUS] as [ws].[STATUS])
0	0	       |    |--Compute Scalar(DEFINE:([Expr1017]=[NFWDTT_WORKFLOWINSTANCE].[PRIORITY] as [wi].[PRIORITY]))	1	5	4	Compute Scalar	Compute Scalar	DEFINE:([Expr1017]=[NFWDTT_WORKFLOWINSTANCE].[PRIORITY] as [wi].[PRIORITY])
1	51	       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [Expr1020]) OPTIMIZED WITH UNORDERED PREFETCH)	1	6	5	Nested Loops	Inner Join	OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [Expr1020]) OPTIMIZED WITH UNORDERED PREFETCH
1	1027	       |    |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([S].[SENTBY], [Expr1019]) WITH UNORDERED PREFETCH)	1	9	6	Nested Loops	Left Outer Join	OUTER REFERENCES:([S].[SENTBY], [Expr1019]) WITH UNORDERED PREFETCH
1	1027	       |    |         |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [Expr1018]) WITH UNORDERED PREFETCH)	1	11	9	Nested Loops	Left Outer Join	OUTER REFERENCES:([S].[WORKFLOWINSTANCE], [Expr1018]) WITH UNORDERED PREFETCH
1	1027	       |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([D].[OWNER]) OPTIMIZED)	1	13	11	Nested Loops	Inner Join	OUTER REFERENCES:([D].[OWNER]) OPTIMIZED
1	26	       |    |         |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([CUP].[DELEGATION]))	1	15	13	Nested Loops	Inner Join	OUTER REFERENCES:([CUP].[DELEGATION])
1	29	       |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_CURRENTUSERPOSITION].[Idx_NFWDTT_CURRENTUSERPOSITION] AS [CUP]), SEEK:([CUP].[USER]='User') ORDERED FORWARD)	1	16	15	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_CURRENTUSERPOSITION].[Idx_NFWDTT_CURRENTUSERPOSITION] AS [CUP]), SEEK:([CUP].[USER]='User') ORDERED FORWARD
29	26	       |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_DELEGATION].[PK_NFWDTT_DELEGATION] AS [D]), SEEK:([D].[ID]=[NFWDTT_CURRENTUSERPOSITION].[DELEGATION] as [CUP].[DELEGATION]) ORDERED FORWARD)	1	17	15	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_DELEGATION].[PK_NFWDTT_DELEGATION] AS [D]), SEEK:([D].[ID]=[NFWDTT_CURRENTUSERPOSITION].[DELEGATION] as [CUP].[DELEGATION]) ORDERED FORWARD
26	1027	       |    |         |    |    |    |--Index Seek(OBJECT:([NFWDTT_STEP].[Idx_NFWDTT_STEP_USER_STATUS] AS [S]), SEEK:([S].[USER]=[NFWDTT_DELEGATION].[OWNER] as [D].[OWNER]),  WHERE:([NFWDTT_STEP].[STATUS] as [S].[STATUS]='U') ORDERED FORWARD)	1	18	13	Index Seek	Index Seek	OBJECT:([NFWDTT_STEP].[Idx_NFWDTT_STEP_USER_STATUS] AS [S]), SEEK:([S].[USER]=[NFWDTT_DELEGATION].[OWNER] as [D].[OWNER]),  WHERE:([NFWDTT_STEP].[STATUS] as [S].[STATUS]='U') ORDERED FORWARD
1027	1027	       |    |         |    |    |--Clustered Index Seek(OBJECT:([NFWDTT_WORKFLOWDESCRIPTION].[PK_NFWDTT_WORKFLOWDESCRIPTION] AS [wd]), SEEK:([wd].[WORKFLOWINSTANCE]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE] AND [wd].[LANGUAGE]='ru') ORDERED FORWARD)	1	19	11	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_WORKFLOWDESCRIPTION].[PK_NFWDTT_WORKFLOWDESCRIPTION] AS [wd]), SEEK:([wd].[WORKFLOWINSTANCE]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE] AND [wd].[LANGUAGE]='ru') ORDERED FORWARD
1027	979	       |    |         |    |--Clustered Index Seek(OBJECT:([NFWDFT_USER].[PK_NFWDFT_USER] AS [u]), SEEK:([u].[code]=[NFWDTT_STEP].[SENTBY] as [S].[SENTBY]) ORDERED FORWARD)	1	20	9	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDFT_USER].[PK_NFWDFT_USER] AS [u]), SEEK:([u].[code]=[NFWDTT_STEP].[SENTBY] as [S].[SENTBY]) ORDERED FORWARD
1027	51	       |    |         |--Clustered Index Seek(OBJECT:([NFWDTT_WORKFLOWINSTANCE].[PK_NFWDTT_WORKFLOWINSTANCE] AS [wi]), SEEK:([wi].[ID]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[STATUS] as [wi].[STATUS]='C') ORDERED FORWARD)	1	21	6	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWDTT_WORKFLOWINSTANCE].[PK_NFWDTT_WORKFLOWINSTANCE] AS [wi]), SEEK:([wi].[ID]=[NFWDTT_STEP].[WORKFLOWINSTANCE] as [S].[WORKFLOWINSTANCE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[STATUS] as [wi].[STATUS]='C') ORDERED FORWARD
51	204	       |    |--Clustered Index Scan(OBJECT:([NFWDFT_WORKFLOWSTATUS].[PK_NFWDFT_WORKFLOWSTATUS_NFW_ID] AS [ws]), WHERE:([NFWDFT_WORKFLOWSTATUS].[LANGUAGE] as [ws].[LANGUAGE]=N'ru'))	1	25	4	Clustered Index Scan	Clustered Index Scan	OBJECT:([NFWDFT_WORKFLOWSTATUS].[PK_NFWDFT_WORKFLOWSTATUS_NFW_ID] AS [ws]), WHERE:([NFWDFT_WORKFLOWSTATUS].[LANGUAGE] as [ws].[LANGUAGE]=N'ru')
51	51	       |--Clustered Index Seek(OBJECT:([NFWVW_WORKFLOWVERSION].[IX_WORKFLOWVERS] AS [wv]), SEEK:([wv].[LANGUAGE]='ru' AND [wv].[STEPCODE]=[NFWDTT_STEP].[STEPCODE] as [S].[STEPCODE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWVERSION] as [wi].[WORKFLOWVERSION]=[NFWVW_WORKFLOWVERSION].[WORKFLOWVERSION] as [wv].[WORKFLOWVERSION] AND ([NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW] IS NULL OR [NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW]=[NFWVW_WORKFLOWVERSION].[WORKFLOW] as [wv].[WORKFLOW])) ORDERED FORWARD)	1	27	2	Clustered Index Seek	Clustered Index Seek	OBJECT:([NFWVW_WORKFLOWVERSION].[IX_WORKFLOWVERS] AS [wv]), SEEK:([wv].[LANGUAGE]='ru' AND [wv].[STEPCODE]=[NFWDTT_STEP].[STEPCODE] as [S].[STEPCODE]),  WHERE:([NFWDTT_WORKFLOWINSTANCE].[WORKFLOWVERSION] as [wi].[WORKFLOWVERSION]=[NFWVW_WORKFLOWVERSION].[WORKFLOWVERSION] as [wv].[WORKFLOWVERSION] AND ([NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW] IS NULL OR [NFWDTT_CURRENTUSERPOSITION].[WORKFLOW] as [CUP].[WORKFLOW]=[NFWVW_WORKFLOWVERSION].[WORKFLOW] as [wv].[WORKFLOW])) ORDERED FORWARD



Статистика I/O улучшается:

(51 row(s) affected)
Table 'NFWVW_WORKFLOWVERSION'. Scan count 51, logical reads 283, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDFT_WORKFLOWSTATUS'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_WORKFLOWINSTANCE'. Scan count 0, logical reads 3154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDFT_USER'. Scan count 0, logical reads 2029, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_WORKFLOWDESCRIPTION'. Scan count 0, logical reads 3154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_STEP'. Scan count 26, logical reads 1652, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_DELEGATION'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NFWDTT_CURRENTUSERPOSITION'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



Как видно, количество логических чтений уменьшается с 506714 до 3154, т.е. в 160 раз.
Значения Estimated Rows начинают, хоть и не полностью соответствовать, но хотя бы приближаться к Actual Rows.

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

@@Version

Microsoft SQL Server 2005 - 9.00.3175.00 (X64) 
	Jun 14 2007 11:45:39 
	Copyright (c) 1988-2005 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)



Может ли кто подсказать причину такого поведения сервера ?
Я не приверженец хинтов и считаю, что оптимизатор может быть достаточно умным, чтоб вычислить оптимальный план запроса.
Однако такие резкие изменения в планах и несоответствие Estimated и Actual Rows откровенно сбивают с толку.
25 июл 12, 01:51    [12911392]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
step_ks
Member

Откуда:
Сообщений: 936
На обновленном до SP4 сервере не тестировали?
У вас Enterprise, можно попробовать создать индексированное представление - может, подхватит. Если это стоит того.
25 июл 12, 08:38    [12911693]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
Обновлять до SP4 сейчас нет возможности (админ вопрос)
Можно создать, да только есть несколько НО:
1) я точно не уверен, сколько еще запросов используют эту таблицу.
2) Исходя из п.1 вьюха должна будет повторять структуру таблицы, а тогда идея лишена смысла.
25 июл 12, 10:16    [12912099]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
step_ks,
немного соврал, тестировал на этой же базе только на SQL 2012.
Та же проблема.
Видимо дело в запросе и данных, а не в оптимизаторе.
25 июл 12, 10:18    [12912113]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
step_ks
Member

Откуда:
Сообщений: 936
rector
Можно создать, да только есть несколько НО:
1) я точно не уверен, сколько еще запросов используют эту таблицу.
2) Исходя из п.1 вьюха должна будет повторять структуру таблицы, а тогда идея лишена смысла.

чойта повторять? вьюху сделать по inner join-ам (всем или не всем).
25 июл 12, 10:43    [12912241]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
step_ks
чойта повторять? вьюху сделать по inner join-ам (всем или не всем).


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

Где в этом случае я могу получить выигрыш ?
25 июл 12, 10:55    [12912350]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
step_ks
Member

Откуда:
Сообщений: 936
Improving Performance with SQL Server 2008 Indexed Views
bol
In the Developer and Enterprise editions of SQL Server 2000 and SQL Server 2005, the query processor can use an indexed view to solve queries that structurally match the view, even if they don't refer to the view by name.
25 июл 12, 11:01    [12912417]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
step_ks
Member

Откуда:
Сообщений: 936
там же
bol
In SQL Server Enterprise, the SQL Server query optimizer automatically determines when an indexed view can be used for a given query execution. The view does not need to be referenced directly in the query for the optimizer to use it in the query execution plan. Therefore, existing applications can take advantage of the indexed views without any changes to the application itself; only the indexed views have to be created.
25 июл 12, 11:02    [12912434]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
SomewhereSomehow
Member

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

Вы добавьте хинт не inner loop join, а option(loop join), тогда по-идее ничего не должно поменяться. А когда вы указываете тип соединения непосредственно в join, то еще и форсируется порядок таблиц в соединении, т.е. они соединяются в том порядке как вы указали (что видно в плане, если учесть что таблица NFWDTT_POOL видимо исключается из плана вообще еще на этапе упрощения).
Join Hints (Transact-SQL)
If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords.


В запросе без хинта оптимизатор сам выбирает порядок, основываясь на оценках. Видимо, его выбор оказывается хуже (я так полагаю запрос работает медленнее чем без хинта?). Значит надо бороться с неправильными оценками. Почему они не правильные - если учесть что вы говорите про актуальность статистики и т.д. - сказать сразу затрудняюсь.

Вы лучше кстати выкладывайте планы в формате xml, чтоб их можно было просмотреть нормальными графическими тулзами, а то в этом тексте нет желания копаться.
25 июл 12, 11:11    [12912527]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
step_ks
там же
bol
In SQL Server Enterprise, the SQL Server query optimizer automatically determines when an indexed view can be used for a given query execution. The view does not need to be referenced directly in the query for the optimizer to use it in the query execution plan. Therefore, existing applications can take advantage of the indexed views without any changes to the application itself; only the indexed views have to be created.


Опа! Не знал такого, спасибо, это действительно вариант.
Отпишусь о результатах :)
25 июл 12, 11:12    [12912532]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
Эх, к сожалению, не наверняка не подойдёт мне вариант с вьюхой.
запрос, который я показал выше является частью адского запроса с CTE в котором заключено 5 подзапросов, соединенных через UNION.
Так как фактически это является одним запросом, то оптимизатор строит план для него целиком, так что вьюху не получится подставить.
25 июл 12, 17:17    [12915325]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
rector
Member

Откуда: Киев
Сообщений: 57
SomewhereSomehow,

да, именно за счет изменения порядка таблиц и получается более лучший план.

Возможно ли, что при актуальной статистике оптимизатор просто не доходит до оптимально плана при переборе ?

нет ли возможности, аналогичной Plan Guide, только чтоб можно было подменить один запрос другим ?

У меня это наиболее часто исполняющийся и, наверное, самый кривой запрос в системе :(
25 июл 12, 17:27    [12915405]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
SomewhereSomehow
Member

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

Да, возможно что и не доходит, а возможно что и просто ошибается, т.к. ошибки в оценках не только из-за неактуальной статистики могут быть.
А почему вас не устраивают Plan Guide, они как раз ведь для случаев, когда на код запроса повлиять нельзя?
25 июл 12, 18:03    [12915672]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация недоступного запроса. Старнный план.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rector
SomewhereSomehow,

да, именно за счет изменения порядка таблиц и получается более лучший план.

Возможно ли, что при актуальной статистике оптимизатор просто не доходит до оптимально плана при переборе ?

нет ли возможности, аналогичной Plan Guide, только чтоб можно было подменить один запрос другим ?

У меня это наиболее часто исполняющийся и, наверное, самый кривой запрос в системе :(
Выложите планы в нормальном виде, тогда можно будет определить где лажает статистика, а так, парсить текст и ломать глаза нет никакого желания.
26 июл 12, 01:08    [12917161]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить