unfilled Jul 5 2020 at 19:37

SQL Server Plan Guide и другие не самые лучшие практики

11 min

12K

Microsoft SQL Server*Database Administration*

+17

Comments 9

frrrost Jul 5 2020 at 20:59

Хорошая статья, не знал, что для Plan Guide есть свои хранимки.

Я бы ещё добавил, что если уж очень хочется помахать молотком с гвоздями — в SQL Server 2016 появился Query Store, который позволяет явно назначить запросу один из недавних планов (предполагается, что среди нескольких последних планов был один, самый эффективный). Ссылка на BOL и best practice

unfilled Jul 5 2020 at 22:00

Да, query store — это очень крутая штука. Но она и правда может помочь только в том случае, если у запроса с определёнными параметрами (или с "определённой" статистикой) попадается удачный план. Запросы с которыми боролся я, к сожалению, лишены возможности отработать так, как мне бы хотелось, самостоятельно (по крайней мере, не вижу как этого добиться без хинтов и буду рад ошибиться).

gleb_l Jul 6 2020 at 00:16

Я бы попробовал построить индекс (title, id DESC), а не (id DESC, title), оставил бы в условии сортировки просто order by id DESC, без оконной функции, сделал только SELECT id, без звёздочки, чтобы убрать key lookup (id можно будет взять прямо из индекса), и далее уже набор полученных id использовал как подзапрос с inner join с основной таблицей, из которой уже берётся звездочка

unfilled Jul 6 2020 at 06:03

Попробую сегодня, результат выложу. Но практически уверен, что такой индекс (title, id DESC) не будет отличаться поведением от индекса только по title, поскольку всё равно придётся его сканировать целиком и пересортировывать по убыванию id.

unfilled Jul 6 2020 at 07:18

CREATE INDEX ix_Title_id ON dbo.Posts (Title, Id DESC);
SET STATISTICS IO, TIME, XML ON;

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2
    WHERE CHARINDEX (N'Aptana', Title) > 1
    ORDER BY Id DESC
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2 WITH (INDEX(ix_Title_Id))
    WHERE CHARINDEX (N'Aptana', Title) > 1
    ORDER BY Id DESC
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2 WITH (INDEX(ix_Title))
    WHERE CHARINDEX (N'Aptana', Title) > 1
    ORDER BY Id DESC
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

Запрос без хинта:

С хинтами:

Статистика выполнения без хинта:

Table 'Posts'. Scan count 1, logical reads 252564, 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 = 2390 ms

С хинтом на ix_Title_Id:

Table 'Posts'. Scan count 5, logical reads 102315, physical reads 0, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 57391 ms

С хинтом на ix_Title:

Table 'Posts'. Scan count 5, logical reads 105417, physical reads 0, read-ahead reads 3550, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 57311 ms

TokminD Jul 6 2020 at 12:49

Решил попробовать на своем примере. ORM генерирует код записи в БД в таком виде

 INSERT INTO "test".."Sample" ("id", ....)

Хочу добавить принудительно блокировку таблицы, но при записи hint в виде

N'OPTION (TABLE HINT("test".."Sample", TABLOCKX))'

получаю ошибку

Cannot execute query. Table-valued or OPENROWSET function 'test..Sample' cannot be specified in the TABLE HINT clause.

Уже разные варианты перепробовал — натыкаюсь на эту ошибку.

unfilled Jul 6 2020 at 12:57

Попробуйте использовать «лайфхак» — найти в кэше планов нужный план и создать из него гайд, а потом уже созданный гайд править.
Хотя, честно говоря, никогда не задумывался можно ли их вообще использовать для INSERT/UPDATE/DELETE.

Потыкался на StackOverflow и dba.stackexchange — достаточно частая проблема, по крайней мере с UPDATE'ами — требуется явное указание FROM. Если дело действительно в этом, возможно, получится выполнить руками запрос с этим хинтом, сохранить его XML-план и в явном виде подсунуть в план гайд. Других идей, к сожалению, нет.

TokminD Jul 6 2020 at 13:30

Создал через хэш плана подправив выражение. Судя по названию плана в XML структуре актуального плана он используется, но табличные указания блокировок не применяются. И при сравнении XML актуальных планов для запросов с указанием

WITH (TABLOCKX)

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

unfilled Jul 6 2020 at 20:36

1 и 2 — схожие вопросы на Stack Overflow. Сам попробовал и не смог указать для обычного DELETE / UPDATE / INSERT хинт блокировки через OPTION (TABLE HINT ...), только через WITH (hint), но plan guide так его повесить не может, поэтому, видимо, ещё одно ограничение в копилку ограничений plan guide'ов.

Show the best of all time