Табличные параметры с оценочным числом строк 1

Я часами искал в Интернете, пытаясь понять, как повысить производительность моего запроса с помощью табличных параметров (TVP).

После нескольких часов поиска я, наконец, определил, что, по моему мнению, является корнем проблемы. Изучив план предполагаемого выполнения моего запроса, я обнаружил, что предполагаемое количество строк для моего запроса равно 1 каждый раз, когда я использую TVP. Если я заменю TVP на запрос, который выбирает интересующие меня данные, то расчетное количество строк будет намного точнее и составит около 7400. Это значительно увеличивает производительность.

Однако в реальном сценарии я не могу использовать запрос, я должен использовать TVP. Есть ли способ заставить SQL Server более точно прогнозировать количество строк при использовании TVP, чтобы использовать более подходящий план?


sql sql-server-2008 table-valued-parameters sql-execution-plan
person Chris Tremblay    schedule 16.04.2014    source источник
comment
Можете ли вы использовать статистику обновления для табличной переменной.   -  person Dannyg9090    schedule 17.04.2014
comment
@Dannyg9090 Dannyg9090 Нет, временные переменные не хранят статистику.   -  person Chris Tremblay    schedule 17.04.2014

Ответы (1)


arrow_upward
4
arrow_downward

TVP — это табличные переменные, которые не ведут статистику и, следовательно, в отчете есть только 1 строка. Есть два способа улучшить статистику по TVP:

  1. Если вам не нужно изменять какие-либо значения в TVP или добавлять в него столбцы для отслеживания рабочих данных, вы можете выполнить простую операцию OPTION (RECOMPILE) на уровне оператора для любого запроса, в котором используется табличная переменная (TVP или локально созданная) и делает с этой табличной переменной больше, чем простой SELECT (т. е. выполнение INSERT INTO RealTable (columns) SELECT (columns) FROM @TVP; не требует перекомпиляции на уровне оператора). Выполните следующий тест в SSMS, чтобы увидеть это поведение в действии:

    DECLARE @TableVariable TABLE (Col1 INT NOT NULL);

INSERT INTO @TableVariable (Col1)
  SELECT so.[object_id]
  FROM   [master].[sys].[objects] so;

-- Control-M to turn on "Include Actual Execution Plan"

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (incorrect)

SELECT * FROM @TableVariable
OPTION (RECOMPILE); -- Estimated Number of Rows = 91 (correct)

SELECT * FROM @TableVariable; -- Estimated Number of Rows = 1 (back to incorrect)

  2. Создайте локальную временную таблицу (одна #) и скопируйте в нее данные TVP. Хотя это дублирует данные в tempdb, преимущества заключаются в следующем:

    • better statistics for a temp table as opposed to table variable (i.e. no need for statement-level recompiles)
    • возможность добавления столбцов
    • возможность изменять значения
person Solomon Rutzky    schedule 24.02.2015
comment
Действительно ли RECOMPILE решает проблему с оценкой? Статистики для TVP нет. - person usr; 24.02.2015
comment
@usr Да, RECOMPILE уровня оператора действительно получает правильное количество строк для этого запроса. Поскольку статистика для табличных переменных не ведется, количество строк, к сожалению, не переносится в последующие запросы, которые ссылаются на эту табличную переменную. Я обновил свой ответ тестом, который показывает, что он работает. - person Solomon Rutzky; 24.02.2015
comment
@usr да, вот отличная статья Аарона Бертрана sqlperformance .com/2014/06/t-sql-запросы/ - person JJS; 04.05.2016