Tag Archives: Query Optimizer

Что нового в SQL Server 2012 SP1

Сегодня компания Microsoft на основном докладе конференции PASS анонсировала первый пакет обновления для SQL Server 2012 (Service Pack 1). Он уже доступен для скачивания по указанной ссылке. Я постараюсь кратко описать, что же нового в нем появилось и показать пару примеров.

Межкластерная миграция для AlwaysOn Availability Groups

В SQL Server 2012 SP1 появляется возможность миграции одной или нескольких AlwaysOn Availability Groups на новый экземпляр кластера Windows Server Failover Clustering (WSFC). Это значительно облегчит обновление существующего кластера на Windows Server 2012 благодаря уменьшению времени простоя.

Selective XML Index

Появляется новый тип индексов, так называемый Selective XML Index. Он позволяет индексировать не все содержимое столбца типа XML, а только необходимые его части, что уменьшает размер XML индексов и положительно сказывается на скорости запросов. Я думаю, что до конца этой недели опубликую отдельную статью, посвященную этой функции.

DBCC SHOW_STATISTICS работает с правами SELECT

В предыдущих версиях SQL Server необходимо было обладать правами администратора или владельца объекта, чтобы просматривать результаты с помощью DBCC SHOW_STATISTICS. Данное ограничение очень сильно влияло, если мы использовали распределенные запросы, т.к. пользователи не имеют административных прав на удаленные объекты. Соответственно отсутствие информации о статистиках на удаленных объектах приводит к построению оптимизатором менее оптимальных планов запросов. В SQL Server 2012 SP1 теперь достаточно прав SELECT на таблицу целиком или на отдельные столбцы, чтобы посмотреть статистики для них. Для тех, кто использует распределенные запросы и может столкнуться с какими-либо проблемами введен флаг трассировки (trace flag) 9485, чтобы вернуть поведение новой функциональности до увроня SQL Server 2012 RTM. Хочу привести сразу пример результатов. Слева план запроса, который использует в качестве удаленного источника SQL Server 2012 RTM, а справа SQL Server 2012 SP1. Как мы видим – статистка начала использоваться.

Новая функция для просмотра статистики

Новая динамическая функция sys.dm_db_stats_properties возвращает информацию о статистиках для указанной таблицы или индексированного представления. Вы можете использовать эту функцию для просмотра тако информации как время последнего обновления статистики, количество строк, которое было использовано для построения статистики, а также количество изменений произошедших в столбце, что раньше было доступно только через недокументированные системные таблицы. Кстати, функция стала также доступна и в SQL Server 2008 R2 SP2. Привожу пример результатов работы данной функции:

Полная версия SQL Server Management Studio в редакциях Express

Теперь с редакциями Express поставляется полнофункциональная версия SQL Server Management Studio.

Поддержка SQL Server Management Objects для управления Resource Governor

В SQL Server 2012 SP1, SQL Server Management Objects обновлены и поддерживают синтаксис команды CREATE RESOURCE POOL (Transact-SQL):

  • CAP_CPU_PERCENT
  • AFFINITY SCHEDULER
  • AFFINITY NUMANODE

Следующие объекты SMO можно использовать для ограничения использования CPUи привязки ресурсных пулов к scheduler’ам и NUMA нодам:

Также появилось несколько улучшений в инструментах бизнес анализа, но так как я к бизнес анализу имею достаточно поверхностное отношение, то не буду о них рассказывать. Я думаю, вы при желании без труда найдете о них информацию. Как мы видим SQL Server 2012 SP1 это не просто пакет обновлений и исправлений, он вносит новый достаточно интересный функционал в существующую платформу.

Влияет ли PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке

Как мы знаем, статистика не создается на табличных переменных и оптимизатор SQL Server считает, что ожидаемое количество строк при выборке из табличной переменной будет равно единице. На прошедшей конференции SQL Rally 2012 после моего доклада прозвучал очень интересный вопрос: будет ли влиять PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке. Я решил проверить это на практике, а так как результат, на мой взгляд, получился очень интересным, я решил им поделиться.

Для нашего эксперимента я создам 2 временных таблицы, одну с PRIMARY KEY, а вторую без, и 2 аналогичных табличных переменных. И вставлю в них значения от 1 до 100. И давайте попробуем сделать несколько тестовых выборок из этих таблиц с разными предикатами, а потом проанализируем результаты. Перед запуском скрипта необходимо включить  отображение реального плана выполнения запроса (можно сделать несколькими способами: сочетанием клавиш Ctrl+M, либо через меню Query -> Include Actual Execution Plan, либо нажать соответствующую кнопку на панели инструментов).

-- Создаем временную таблицу с числами от 1 до 1000
IF OBJECT_ID('tempdb.dbo.#nums', 'U') IS NOT NULL
	DROP TABLE #nums;

CREATE TABLE #nums (
	[n] int PRIMARY KEY
);

DECLARE @max AS INT, @rc AS INT;
SET @max = 800;
SET @rc = 1;

INSERT INTO #nums ([n]) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
	INSERT INTO #nums ([n]) SELECT [n] + @rc FROM #nums;
	SET @rc = @rc * 2;
END

INSERT INTO #nums ([n])
SELECT [n] + @rc FROM #nums WHERE [n] + @rc <= @max;

/*
Создаем 2 временные таблицы: одну с PRIMARY KEY, вторую без.
И 2 аналогичные табличные переменные. Вставляем в них числа от 1 до 1000.
*/
IF OBJECT_ID('tempdb.dbo.#temp_table', 'U') IS NOT NULL
	DROP TABLE #temp_table;

IF OBJECT_ID('tempdb.dbo.#temp_table_pk', 'U') IS NOT NULL
	DROP TABLE #temp_table_pk;

CREATE TABLE #temp_table (
	[i] int
);

CREATE TABLE #temp_table_pk (
	[i] int PRIMARY KEY
);

DECLARE @table_var TABLE (
	[i] int
);

DECLARE @table_var_pk TABLE (
	[i] int PRIMARY KEY
);

INSERT INTO #temp_table ([i])
SELECT [n] FROM #nums;

INSERT INTO #temp_table_pk ([i])
SELECT [n] FROM #nums;

INSERT INTO @table_var ([i])
SELECT [n] FROM #nums;

INSERT INTO @table_var_pk ([i])
SELECT [n] FROM #nums;

-- Делаем несколько тестовых запросов
SELECT [i] FROM #temp_table WHERE [i] < 145;
SELECT [i] FROM #temp_table_pk WHERE [i] < 145;

SELECT [i] FROM #temp_table WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM #temp_table_pk WHERE [i] > 378 AND [i] < 634;

SELECT [i] FROM @table_var WHERE [i] < 145;
SELECT [i] FROM @table_var WHERE [i] < 145 OPTION (RECOMPILE);

SELECT [i] FROM @table_var_pk WHERE [i] < 145;
SELECT [i] FROM @table_var_pk WHERE [i] < 145 OPTION (RECOMPILE);

SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);

SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);

А теперь давайте подробно проанализируем планы наших запросов.

1. Выборка и временной таблицы с фильтром по верхней границе.

Результаты запроса

Результаты запроса

Здесь в принципе нет ничего необычного, оптимизатор построил статистику на временной таблице и использовал ее при выборке. При выборке из временной таблицы с PRIMARY KEY ситуация будет аналогичная, поэтому план запроса не привожу.

2. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы.

Результаты запроса

Результаты запроса

Опять нет ничего необычного, оптимизатор построил статистику на временной таблице и использовал ее при выборке. При выборке из временной таблицы с PRIMARY KEY ситуация будет аналогичная, поэтому план запроса не привожу.

3. Выборка из табличной переменной с фильтром по верхней границе.

Привожу сразу планы запросов для обычной табличной переменной и табличной переменной с PRIMARY KEY.

Результаты запроса

Результаты запроса

Результаты запроса

Результаты запроса

Как мы видим и в том и в другом случае количество ожидаемых строк равно единице. Таким образом, PRIMARY KEY никак не влияет на ожидаемое количество строк при выборке из табличной переменной.

4. Выборка из табличной переменной с опцией RECOMPILE. Привожу только план выборки из обычной табличной переменной, т.к. для табличной переменной с PRIMARY KEY будет аналогично.

Результаты запроса

Результаты запроса

Результаты оказались несколько неожиданными: оптимизатор ожидает на выходе 300 строк вместо одной. 300 строк – это 30%, обычная константа, которая зашита в оптимизатор.

5. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы. Сначала приведу обычный план запроса, потом план запроса с опцией RECOMPILE.

Результаты запроса

Результаты запроса

Результаты запроса

Результаты запроса

В первом плане запросов ожидаемое количество строк равно единице. А вот для запроса с опцией RECOMPILE количество ожидаемых строк равно 90 или 9% от общего числа. Я поэкспериментировал с различным количеством строк в таблице и разными границами предиката, но результата один – 9%. Для запросов к табличным переменным с PRIMARY KEY результаты будут такие же, поэтому планы запросов не привожу.

Вывод.

Пришла пора подвести итог небольшого практического исследования и ответить на главный вопрос статьи: наличие PRIMARY KEY на табличной переменной никак не влияет на ожидаемое количество строк при выборке.

Но в процессе исследования случайно столкнулся с тем, что если использовать опцию RECOMPILE, то количество ожидаемых строк будет 30% от общего числа строк, если использовать в предикате ограничение только по одной границе интервала. А если использовать ограничение с двух сторон, то количество ожидаемых строк будет 9% от общего числа строк в табличной переменной.