Tag Archives: Performance Optimization

SQL Server 2016: Database Scoped Configuration

Одним из наиболее интересных нововведений в SQL Server 2016 (а также в Azure SQL Database V12) является возможность контролировать некоторые параметры, напрямую связанные с производительностью, на уровне базы данных, а не сервера или запроса, как было раньше. Несомненно, это огромный плюс, т.к. обычно с разными базами данных работают разные приложения, которые могут требовать своих требований к настройкам. Также это позволяет более гибко управлять этими настройками, т.к. определенные пользователи с правами на указанные базы уже могут менять эти параметры, нет необходимости расширять им права до уровня сервера. И еще один интересный момент, если ваши базы данных участвуют в AlwaysOn Availability Groups или в GeoReplication для Azure, то вы можете даже выставить разные настройки для главной (PRIMARY) базы и для вторичных реплик (SECONDARY), т.к. опять же, у вторичных реплик может быть совсем другой сценарий использования и требования, чем у первичной базы данных.

Эта новая опция называется Database Scoped Configuration. На текущий релиз в нее входят следующие параметры:

  • Возможность очистить процедурный кэш только для конкретной базы данных. Раньше это можно было сделать либо для всего сервера, либо для конкретного запроса по его plan handle.
  • MAXDOP – уровень параллелизма по умолчанию. 0 – будут использоваться настройки сервера. Эта настройка перебивает соответствующую на уровне сервера, однако, подсказки на уровне запросов обладают высшим приоритетом. При этом не забываем, что Resource Governor все равно всех ограничивает сверху. Подробнее о его работе вы можете прочитать тут.
  • Parameter Sniffing – включить или выключить. Выключение аналогично trace flag 4136. На уровне запроса этим можно было управлять с помощью подсказки OPTIMIZE FOR UNKNOWN.
  • Управление, какую версию Cardinality Estimator использовать (новую, появившуюся в SQL Server 2014, либо предыдущую).
  • Оптимизатор запросов со всеми последними обновлениями. Аналог trace flag 4199.

Для того, чтобы внести изменения, необходимо обладать правами ALTER ANY DATABASE SCOPED CONFIGURATION на уровне базы данных. Такие права может выдать любой, у кого есть право CONTROL на базу данных.

grant alter any database scoped configuration to [MyUser];

Если вы изменяете настройки в Database Scoped Configuration, то они автоматически транслируются на вторичные реплики, если база данных участвует в AlwaysOn AG или Geo Replication. Если же вы хотите для вторичных реплик выставить свои отдельные значения, то необходимо будет использовать ключевое слово FOR SECONDARY. Например, ниже вы увидите пример изменения настройки MAXDOP для главной базы данных, а во втором случае для вторичных реплик. Все вторичные реплики получают одинаковые настройки. Нет возможности выставить свои отдельные параметры для разных вторичных реплик.

alter database scoped configuration set maxdop = 4;
alter database scoped configuration for secondary set maxdop = 2;

Также параметры с опцией FOR SECONDARY могут принимать значение PRIMARY. Это будет означать, что настройки будут идентичными, что и на основной базе данных.

alter database scoped configuration for secondary set maxdop = primary;

Примеры выключения parameter sniffing для основной базы данных и вторичных реплик:

alter database scoped configuration set parameter_sniffing = off;
alter database scoped configuration for secondary set parameter_sniffing = off;

Возможность вернуть старую версию Cardinality Estimator:

alter database scoped configuration set legacy_cardinality_estimation = on;

Активация нового оптимизатора запросов со всеми исправлениями:

alter database scoped configuration set query_optimizer_hotfixes = on;

Очистка процедурного кэша для отдельной базы данных (доступно только для основной БД):

alter database scoped configuration clear procedure_cache;

Также вам доступен просмотр всех текущи значений в Database Scoped Configuration через системное представление sys.database_scoped_configurations.

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

SQLSaturday в Краснодаре 4 июня 2016 г.

sqlsat503_webОсталось чуть меньше двух недель до, пожалуй, главного события на юге России, посвященного SQL Server – это, уже ставшая традиционной, конференция SQLSaturday. Если честно, я уже сбился со счета, какой год подряд она уже проходит в Краснодаре, но факт, что уже точно больше, чем в Москве.

Вы еще можете успеть зарегистрироваться, купить билеты в Краснодар, посетить эту замечательную бесплатную конференцию, а также провести выходные в этом довольно милом городе.

Официальный сайт конференции доступен по следующей ссылке. Там же вы сможете зарегистрироваться. Ознакомиться с полным списком докладов и докладчиков вы можете на этой странице. Докладов немного, но они достаточно жесткие и интересные, на мой взгляд.

Сам я выступлю с двумя докладами: по языку R в SQL Server 2016, а также с новым, улучшенным докладом про нетрадиционные и провокационные методы оптимизации запросов. Надо отметить, что этот доклад я в ближайшем будущем больше нигде читать не планирую.

Буду рад всех увидеть на своих докладах в Краснодаре. Удачи вам и приятного посещения конференции!

SQL Server 2016: Live Query Statistics

В SQL Server Management Studio появилась возможность наблюдать за ходом выполнения запроса. По мере выполнения плана запроса отображается взаимодействие между операторами и потоки данных. Этот процесс получил название Live Query Plan и показывает время работы запроса, прогресс на каждом операторе и количество данных. С помощью данного функционала можно более детально изучать работу запросов для их последующей оптимизации. Стоит отметить, что возможность появляется именно в новой SQL Server Management Studio, а работать будет как с SQL Server 2014, так и с SQL Server 2016.

Для того, чтобы увидеть Live Query Plan необходимо на панели инструментов нажать кнопку Include Live Query Statistics.

Также вы можете нажать правой кнопкой мыши на запросе и выбрать необходимый пункт из выпавшего меню.

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

Зеленым выделен оператор, который уже завершился, стрелки, обозначающие потоки данных, прямые линии. Синим выделен оператор, который еще в процессе выполнения, потоки данных обозначены пунктиром, процент выполнения растет и количество строк тоже. Для каждого оператора отображается соответственно его время выполнения, оценочный процент выполнения, количество прошедших через него строк и ожидаемое количество строк. Если вдруг оптимизатор оценит количество строк неправильно, то у вас будет отображаться 99% выполнения, а количество строк будет постоянно расти. В этом случае предсказать время выполнения становится сложнее. Выглядеть это будет примерно так.

Также существует возможность посмотреть на Live Query Plan прямо из Activity Monitor путем нажатия правой клавишей мыши на интересующий нас запрос и выбора пункта Show Live Execution Plan.

Но для того, чтобы это сработало необходимо, чтобы в сессии, которая выполняет запрос был включен сбор статистики. Это можно сделать с помощью команд SET STATISTICS XML ON или SET STATISTICS PROFILE ON в нужной сессии, а также включив сбор расширенного события query_post_execution_showplan для нужных сессий. Будьте аккуратны, не активируете это для всех сессий на сервере, т.к. сбор статистики замедляет выполнение запросов.

Функциональность требует наличия прав SHOWPLAN и VIEW SERVER STATE, но эти права обычно всегда выдают тем, кто занимается диагностикой и оптимизацией запросов. Существуют также некоторые ограничения. Live Query Plan недоступен для Natively Compiled хранимых процедур.

Подводные камни INSERT EXEC

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

Для начала мы создадим пару таблиц: источник и приемник, добавим в источник около 100 тысяч записей, а также процедуру, которая возвращает данные из источника.

use [tempdb];
go

if object_id('dbo.test_table_src', 'U') is not null
	drop table dbo.test_table_src;
go

if object_id('dbo.test_table_tgt', 'U') is not null
	drop table dbo.test_table_tgt;
go

if object_id('dbo.get_test_table_src', 'P') is not null
	drop procedure dbo.get_test_table_src;
go

create table dbo.test_table_src (
	[id] int not null identity(1, 1),
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,

	constraint [pk_test_table_src] primary key clustered ([id])
);
go

create procedure [dbo].[get_test_table_src]
as
begin
	set nocount on;

	select
		[id],
		[c1],
		[c2],
		[c3]
	from [dbo].[test_table_src];
end;
go

create table dbo.test_table_tgt (
	[id] int not null,
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,

	constraint [pk_test_table_tgt] primary key clustered ([id])
);
go

insert into [dbo].[test_table_src] (
	[c1],
	[c2],
	[c3]
)
select top (100000)
	cast(rand(checksum(newid())) * 1000000 as int),
	cast(newid() as varchar(100)) + '_' + cast(newid() as varchar(100)),
	dateadd(second, cast(rand(checksum(newid())) * 1000000 as int), '19000101')
from sys.all_columns as t1
cross join sys.all_columns as t2
go

Для начала давайте включим статистику времени выполнения и IO операций и просто сделаем вставку в таблицу приемник из источника.

set statistics io on;
set statistics time on;
go
insert into [dbo].[test_table_tgt] (
	[id],
	[c1],
	[c2],
	[c3]
)
select
	[id],
	[c1],
	[c2],
	[c3]
from [dbo].[test_table_src];
go

Ниже я приведу статистику, которая получилась на моей машине.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘test_table_src’. Scan count 1, logical reads 1272, 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 = 188 ms,  elapsed time = 226 ms.

(100000 row(s) affected)

А теперь посмотрим, за сколько отработает запрос INSERT EXEC в таблицу приемник с помощью хранимой процедуры. Перед запуском я предварительно очищаю таблицу от результатов предыдущего теста.

truncate table [dbo].[test_table_tgt];
alter table [dbo].[test_table_tgt] rebuild;
go
insert into [dbo].[test_table_tgt]
exec [dbo].[get_test_table_src];
go

И статистика для этого запроса.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Table ‘test_table_src’. Scan count 1, logical reads 1271, 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 = 671 ms,  elapsed time = 669 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 1, logical reads 306169, 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 = 921 ms,  elapsed time = 927 ms.

(100000 row(s) affected)

Как мы видим в статистике, появляется Worktable (временная таблица в tempdb), с которой происходит очень большое количество операций. Это получается из-за того, что перед тем, как вставить данные в таблицу приемник, они сначала временно материализуются в tempdb, а уже только потом вставляются. В итоге на моей машине, да и на нескольких других, где я проводил подобный тест, INSERT EXEC работает примерно в 4 раза медленнее, чем обычный INSERT. Да, мы лишаемся удобного интерфейса для получения данных, но если для нас важнее скорость, то лучше всеми способами избегать использовать INSERT EXEC.

Материалы с конференции DevCon 2013

2 недели назад в подмосковных Яхонтах прошла очередная конференция DevCon 2013. Выкладываю материалы моих докладов.

Как читать план запроса в SQL Server и на что обращать внимание

В докладе идет речь о том, что такое план запроса; о различных операторах, с которыми можно встретиться, с чего начать анализ плана, на что обращать внимание и как можно выявить “узкое место” в запросе.

Презентация: https://olontsev.ru/download/DevCon2013_Query_Plans.pdf

Скрипты с демонстрациями: https://olontsev.ru/download/Query_Plans_Demo_Scripts.zip

Особенности использования хранимых процедур и функций в SQL Server

Эта сессия об особенностях, с которыми можно столкнуться при использовании хранимых процедур и функций в SQL Server, и как это может повлиять на производительность ваших запросов.

Презентация: https://olontsev.ru/download/DevCon_2013_UDFs_and_SPs.pdf

Скрипты с демонстрациями: https://olontsev.ru/download/UDFs_and_SPs_Demo_Scripts.zip

Записи докладов можно посмотреть по указанной ссылке (в скором времени должны выложить на http://www.techdays.ru/): http://www.msdevcon.ru/online

Ну и напоследок ответ на самый главный вопрос конференции: в чем секрет белки? J

SQL Server Enterprise Edition Advanced Scanning

Мы знаем, что Enterprise редакция SQL Server содержит некоторые улучшения, которые при определенных условиях позволяют выполнять операции более оптимально, чем в Standard редакции. Одной из таких вещей является Advanced Scanning, которая позволяет нескольким запросам на сканирование делить одну операцию физического чтения с диска. Также можно встретить другое название этой особенности: Merry-go-Round Scan.

Например, у нас есть большая таблица Table1 состоящая из 1 000 000 страниц. Пользователь UserA начинает выполнять инструкцию T-SQL, которая требует сканирования таблицы. В тот момент, когда выполнилось сканирование 200 000 страниц пользователь UserB подключается и начинает другой запрос, который также требует сканирование Table1. Для сканирования после 200 001-ой страницы компонент Database Engine запланирует всего одно физическое чтение и будет возвращать полученные строки обоим планам выполнения. После сканирования 500 000 страниц пользователь UserC подключается и также запрашивает все данные из таблицы Table1. Теперь операция физического сканирования уже делится между 3мя запросами. Как только сканирование закончится для пользователя UserA, оно продолжится для пользователей UserB и UserC, но уже с 1-ой страницы и соответственно до 200 000 для UserB и до 500 000 для UserC.

В чем смысл этой оптимизации. Если у вас имеются очень большие таблицы, которые не вмещаются целиком в оперативную память и их сканирование приводит к «вымыванию» буферного пула и такие сканирования идут часто и параллельно, то такая оптимизация поможет снизить давление на буферный пул и не делать сканирование несколько раз.

Для демонстрации давайте создадим достаточно большую таблицу с кластеризованным индексом в БД AdventureWorks2012.

use
[AdventureWorks2012];
go

select
    [DatabaseLogID],
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
into [dbo].[BigDatabaseLog]
from [dbo].[DatabaseLog];
go

insert into [dbo].[BigDatabaseLog]
(
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
)
select
    [PostTime],
    [DatabaseUser],
    [Event],
    [Schema],
    [Object],
    [TSQL],
    [XmlEvent]
from [dbo].[DatabaseLog];
go 200

create clustered index [IX_CL_DatabaseLogID]
on [dbo].[BigDatabaseLog]
(
    [DatabaseLogID] asc
) on [PRIMARY];
go

Также стоит отметить, что Advanced Scanning работает только в том случае, если нет требования, чтобы поток данных был отсортирован. Например, как в следующем плане запроса:

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

Дополнительно стоит отметить, что у меня получилось провести указанный эксперимент только для сканирования кластеризованного индекса, который целиком не помещается в буферный пул. Я ограничивал размер максимальной памяти в SQL Server 400 мегабайтами. В двух следующих ситуациях вышеуказанное НЕ работало:

  1. При сканировании heap таблицы (без кластеризованного индекса) данные всегда возвращались в одном порядке.
  2. Также, если таблица целиком закэширована в буферном пуле, результаты также возвращались в одном порядке, но это ничему не противоречит, т.к. тяжелых операций чтения с диска при этом нет, а только логические чтения из буферного пула.