Обнаружение параметров на табличных параметрах

Question

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

(INT_LIST - это определенный пользователем тип таблицы, представляющий собой один столбец типа INT)

CREATE PROCEDURE [dbo].[TVPSniffTest](
    @param1 varchar(50),
    @idList INT_LIST readonly
)
AS
BEGIN 
   DECLARE @param1_sniff VARCHAR(50) = @param1 --this is worth doing

   DECLARE @idList_sniff INT_LIST
   INSERT INTO @idList_sniff SELECT value FROM @idList --will this help?

   --query code here
END

Answer 1

Это никак не повлияет - фактически, это отрицательно сказывается на производительности, потому что вы сначала копируете всю таблицу.

Оптимизатор не ведет статистики ни для табличных параметров, ни для табличных переменных. Это может легко привести к плохим планам запросов с несоответствиями количества элементов; Решением для этого обычно является промежуточная временная таблица. В любом случае, анализ параметров не будет проблемой - содержимое таблицы никогда не используется для оптимизации плана запроса.

Кстати, в то время как вы можете назначить параметр локальной переменной для обхода сниффинга, более гибким вариантом является использование OPTIMIZE FOR или жеRECOMPILE подсказки в запросах, которые особенно затронуты (или WITH RECOMPILE в целом хранимая процедура, но это чуть более радикально). Это предотвращает засорение процедуры копиями всего.

Answer 2

Как уже упоминал Jeroen, нет никакой проблемы с анализом параметров с TVP. А также, что одним из способов смягчения недостатка статистики является копирование TVP в локальную временную таблицу (которая поддерживает статистику).

Но другой вариант, который иногда более эффективен, - это выполнить перекомпиляцию на уровне операторов для любых запросов, используя переменную таблицы (т. Е. TVP). Статистика не будет поддерживаться по всем запросам, поэтому ее необходимо выполнять для любого запроса, который включает переменную таблицы, которая не похожа на простой SELECT.

Следующее иллюстрирует это поведение:

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".
-- For each of the 3 following queries, hover over the "Table Scan"
--   operator to see the "Estimated Number of Rows".

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)