Category Archives: SQL Server

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

Leave a reply

Выражение 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.

Про скорость поиска по строкам и таблицу с настройками

Leave a reply

Довольно широко распространена практика, хранить настройки приложений в базе данных в отдельной таблице примерно следующей структуры.

use [tempdb];
go


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

create table dbo.settings (
	[name] varchar(128) not null,
	[value] sql_variant null,

	constraint [pk_settings] primary key clustered ([name])
);
go

Настройки могут быть самые разные: какие-то процессы могут постоянно обновлять свой статус, хранить дату последней обработки данных; также это могут быть какие-то параметры для внешних сервисов или ETL процессов. В общем, способов применения очень много, и такой подход встречается очень часто. Но я, конкретно, хотел бы остановиться на одной довольно распространенной ошибке, которая встречается в этом случае, да и в некоторых других. Очень часто названия параметров содержат довольно большой одинаковый префикс, например: Process1_config_param1, Process1_last_active_time, Process2_config_param1, Process2_last_active_time и т.п. Я надеюсь, вы уловили смысл. У нас в базе данных могут быть десятки, сотни или даже тысячи строк, у которых 5-30 первых символов совпадает. И это может повлиять на производительность, т.к. при поиске по названию придется проверять все эти строки. Давайте устроим небольшой тест на производительность и посмотрим, как себя ведет SQL Server.

В первом тесте я вставлю 1000 значений в таблицу, у которых первые 10 символов будут одинаковые, а последние 4 уникальные, и запущу подряд 3 миллиона выборок по одному из значений.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select replicate('A', 10) + replicate('0', 4 - ceiling(log(rn + 1, 10))) + cast(rn as varchar(10)), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;

declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = 'AAAAAAAAAA0100'

	set @i += 1

end

В следующем тесте я сделаю первые 4 символа уникальные, а остальные совпадающие. Т.е. я убрал все повторяющиеся куски в конец строки, а уникально идентифицирующие в начало.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000') + replicate('A', 10), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100AAAAAAAAAA'

	set @i += 1

end

Ну и в финальном тесте я вообще убрал повторяющиеся значения, а оставил только короткие уникальные наименования.

truncate table dbo.settings;
alter table dbo.settings rebuild;

insert into dbo.settings (name, value)
select format(rn, '0000'), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;



declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin

	select @test = value
	from dbo.settings
	where name = '0100'

	set @i += 1

end

Вы можете запустит все 3 теста у себя и сравнить результаты. Я же хочу поделиться своими. Тесты запускались несколько раз, результаты были достаточно стабильны. Точное время выполнения приводить не имеет смысла, поделюсь лишь сравнительными итогами. Если взять за основу первое решение, то второй вариант относительно него выполняется на 10% быстрее, что очень существенно. Если взять третий тест, то он будет приблизительно на 15% быстрее чем первый и чуть больше, чем на 5% быстрее, чем второй.

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

SQL Server 2016: увеличен максимальный размер ключа в некластерном индексе

Leave a reply

В SQL Server 2016 и в Azure SQL Database увеличен максимальный размер ключа в некластерном индексе с 900 до 1700 байт. Максимальный размер ключа для кластерного индекса по-прежнему остается 900 байт.

Ниже приведен пример, когда создается таблица со строковым полем фиксированной длины 1700 байт и индексом на этом поле.

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

create table dbo.test_table (
	c1 varchar(1700) not null
);
go

create index ix_test_c1 on dbo.test_table (c1)
go

insert into dbo.test_table values (replicate('A', 1700))
go

Скрипт успешно отрабатывает на SQL Server 2016. На ранних версиях SQL Server команда CREATE INDEX выдает предупреждение.

Warning! The maximum key length is 900 bytes. The index ‘ix_test_c1’ has maximum length of 1700 bytes. For some combination of large values, the insert/update operation will fail.

А при попытке вставить какие данные в таблицу – ошибку.

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length 1700 bytes for the index ‘ix_test_c1’ exceeds the maximum length of 900 bytes.

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

Для некластерных индексов в in-memory таблицах ограничение на размер ключа составляет 2500 байт, для hash индексов ограничений нет.

О том, что всегда нужно использовать псевдонимы

Leave a reply

Сегодня я бы хотел остановится подробно на том, как можно обезопасить себя от непредсказуемых ошибок в T-SQL коде путем его правильного оформления. При написании запросов на выборку данных можно указывать псевдонимы для таблиц. Во-первых, это позволяет в дальнейшем обращаться к ним по более коротким именам, например:

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

create table #t1 (
	id int not null primary key,
	some_value char(10) not null
);

insert into #t1 (id, some_value)
values
	(1, 'A'), (2, 'B');

-- Пример запроса без использования псевдонимов.
select id, some_value
from #t1;

-- Пример запроса с использованием псевдонимов.
select t1.id, t1.some_value
from #t1 as t1;

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

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

if object_id('tempdb..#t2', N'U') is not null
	drop table #t2;

create table #t1 (
	id int not null primary key,
	some_value char(10) not null
);

create table #t2 (
	id int not null primary key,
	some_value char(10) not null
);

insert into #t1 (id, some_value)
values
	(1, 'A'), (2, 'B');

insert into #t2 (id, some_value)
values
	(1, 'C'), (2, 'D');

-- Пример корректно написанного запроса.
select t1.id, t2.some_value
from #t1 as t1
inner join #t2 as t2 on
	t2.id = t1.id;

-- Пример запроса, который вызовет ошибку.
select id, some_value
from #t1 as t1
inner join #t2 as t2 on
	t2.id = t1.id;

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

Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ‘t2’.

Однако это еще не все. Иногда игнорирование использования псевдонимов в запросе может приводить к трудно отлавливаемым ошибкам и некорректным результатам.

use tempdb;
go

if object_id('tempdb..#t1', N'U') is not null
	drop table #t1;

if object_id('tempdb..#t2', N'U') is not null
	drop table #t2;

create table #t1 (
	id int not null,
	some_id int not null
);

create table #t2 (
	no_id int not null
);

insert into #t1 (id, some_id)
values
	(1, 1),
	(2, 3);

insert into #t2 (no_id) values (5);

-- В таблице #t2 отсутствует поле id, но запрос при этом выполняется без ошибок.
select *
from #t1 as t1
where
	some_id in (select id from #t2);

В данном случае мы предполагали, что поле id присутствует во временной таблице #t2, хотя его там нет. Запрос при этом отработал без ошибок и вернул данные, т.к. написан он корректно: вернул те записи, у которых поля id и some_id совпадают, если в таблице #t2 имеется хоть одна запись. Как мы видим, интерпретация запроса получилась совсем другая, которую мы имели ввиду, когда его составляли. Поэтому во избежание подобных случайных ошибок в своих запросах рекомендуется всегда использовать псевдонимы и точно указывать, к каким таблицам и столбцам вы обращаетесь.

Как посмотреть процент отката транзакции в SQL Server

Leave a reply

Предположим такую ситуацию: у вас происходит откат (rollback) какого-либо запроса или транзакции, а вы хотите посмотреть, когда он закончится. Одним из самых простых способов для этого является, как ни странно, команда kill с опцией statusonly. Выглядеть это будет примерно следующим образом.

kill 64 with statusonly

На выходе вы получите примерно следующий результат.

SPID 64: transaction rollback in progress. Estimated rollback completion: 60%. Estimated time remaining: 13508 seconds.

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

SQL Server 2016: DROP IF EXISTS

Leave a reply

Я более чем уверен, что многие из вас писали следующий или очень похожий на него код:

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

create table dbo.test_table (
    id int not null,
    name varchar(100) null
);
go

Объекты из базы иногда необходимо удалять, например, в скриптах развертывания новой версии базы данных, либо при тестировании, когда скрипт, создающий новые объекты запускается подряд несколько раз. А если просто попытаться удалить несуществующий объект, то возникнет ошибка и выполнение скрипта прервется. И если вышеуказанный код проверки и удаления таблицы еще выглядит более-менее читабельно, но проверка на существование триггеров и пользователей представляет из себя уже довольно длинное выражение, например:

if exists (select top (1) 1 from sys.triggers as tr where tr.name = 'tr_test')
    drop trigger tr_test;
go

create trigger tr_test
on dbo.test_table
after insert
as
begin
    print 'test';
end;
go

В SQL Server 2016 наконец-то появилась возможность в T-SQL удалять объект с проверкой на его существование. Выглядит эта команда вот так: DROP <тип  объекта> IF EXISTS <название объекта>. Давайте посмотрим, как вышеуказанные команды будут выглядеть в SQL Server 2016.

drop table if exists dbo.test_table;
go

drop trigger if exists tr_test;
go

Как видим, теперь это делается гораздо проще и выглядит более понятно. Сейчас новый синтаксис распространяется на следующие типы объектов:

VIEW FUNCTION SEQUENCE INDEX
PROCEDURE TRIGGER DATABASE SECURITY POLICY
TABLE VIEW SCHEMA SYNONYM
ASSEMBLY RULE USER
ROLE TYPE DEFAULT

 

Казалось бы, небольшое изменение, но оно очень упрощает работу, т.к. все, что облегчает рутинные операции и минимизирует количество написанного для них кода, в конечном счете увеличивает нашу производительность. Ну и напоследок хочу добавить от себя ложку дегтя. Очень хотелось бы получить команду CREATE OR REPLACE, как в Oracle, т.к. тогда вообще отпадала бы необходимость писать команду удаления. Возможно, в будущих версиях нас все-таки порадуют.

SQL Server 2016: Dynamic Data Masking

Leave a reply

В SQL Server 2016 появляется новая возможность Dynamic Data Masking, которая позволяет ограничить видимость важных данных для непривилегированных пользователей путем ограничения области видимости. Вы сами сможете указывать, какую часть из этих данных можно отображать. При этом данные в самой базе остаются неизменными, а также не потребуются доработки со стороны существующих приложений.

Давайте рассмотрим это на примере. Допустим у нас в базе данных хранятся номера кредитных карт пользователей, и у нас есть оператор, которому необходимо видеть только 4 последних цифры этого номера (вы должны были довольно часто сталкиваться с этим, при общении с представителями банков). С помощью Dynamic Data Masking мы можем на уровне базы данных задать маску, которая скроет от оператора все цифры кредитных карт, кроме последних четырех.

Существует 4 вида функций, которые можно применять для сокрытия данных.

default – Для строковых полей отображает XXXX, для числовых типов – 0, для дат – 1 января 1900г., а для бинарных – 0x30 (это не что иное, как бинарное представление ASCII кода от символа 0). Null значения не скрываются. Давайте посмотрим на небольшой пример, как это будет работать. В примере я создаю таблицу с колонками различных типов данных, вставляю туда несколько разных значений и проверяю, как отображение работает из-под владельца базы данных и пользователя с правом только на выбор данных.

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	c1_varchar varchar(100) masked with (function = 'default()') null,
	c2_varchar_max varchar(max) masked with (function = 'default()') null,
	c3_nvarchar varchar(100) masked with (function = 'default()') null,
	c4_nvarchar_max varchar(max) masked with (function = 'default()') null,
	c5_int int masked with (function = 'default()') null,
	c6_bit bit masked with (function = 'default()') null,
	c7_uniqueidentifier uniqueidentifier masked with (function = 'default()') null,
	c8_datetime datetime masked with (function = 'default()') null,
	c9_varbinary varbinary(100) masked with (function = 'default()') null,
	c10_varbinary_max varbinary(max) masked with (function = 'default()') null,

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

insert into dbo.[test] (
	c1_varchar,
	c2_varchar_max,
	c3_nvarchar,
	c4_nvarchar_max,
	c5_int,
	c6_bit,
	c7_uniqueidentifier,
	c8_datetime,
	c9_varbinary,
	c10_varbinary_max
)
values
	('q', 'q', 'qwerty_asdfg', 'qwerty_asdfg', 1, 1, newid(), getutcdate(), 0x01, 0x000102030405),
	('qw', 'qw', 'qwer', 'qwer', 2, 0, newid(), getutcdate(), 0x02, 0x00),
	('qwe', 'qwe', 'qwe', 'qwe', 3, 1, newid(), getutcdate(), 0x03, 0x02),
	('qwer', 'qwer', 'qw', 'qw', 4, 0, newid(), getutcdate(), 0x04, 0x01),
	('qwerty_asdfg', 'qwerty_asdfg', 'q', 'q', 5, 1, '00000000-0000-0000-0000-000000000000', '19000101', 0x30, 0x30),
	(null, null, null, null, null, null, null, null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

email – данная функция, как легко догадаться, предназначена для сокрытия адресов электронной почты и работает по следующему алгоритму: отображает первый символ адреса, а после него показывает XXX@XXXX.com, независимо от того, в каком домене у вас адрес, на конце всегда .com. Соответствующий пример для функции email будет выглядеть так:

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	email varchar(200) masked with (function = 'email()') null,

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

insert into dbo.[test] (
	email
)
values
	('qwe@qwe.com'),
	('zxc@asdzxc.ru'),
	(null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

partial – позволяет более гибко управлять отображением строковых значений. Вы указываете эту функцию в формате partial(N1, “XXXXXXX”, N2), где N1 – количество символов с начала строки, которые можно показать, N2 – с конца, а между ними указываете произвольную маску, которая отобразится вместо остального текста. Если вдруг длина строки не будет превышать указанного количества открытых символов, то вместо нее просто отбразится маска. Давайте посмотрим, как, например, с помощью этой функции можно скрыть номера телефонов, кредитных карт и просто произвольной строки разной длины.

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	phone_number varchar(20) masked with (function = 'partial(3, "-XXX-XX-", 2)') null,
	credit_card char(19) masked with (function = 'partial(0, "XXXX-XXXX-XXXX-", 4)') null,
	custom_string varchar(100) masked with (function = 'partial(5, "XXXXX", 5)') null,

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

insert into dbo.[test] (
	phone_number,
	credit_card,
	custom_string
)
values
	('99112345671', '1111-1111-1111-1111', 'qwerty'),
	('99212345672', '1111-1111-1111-2222', 'qwe'),
	('99312345673', '1111-1111-1111-3333', '123456789A'),
	('99412345674', '1111-1111-1111-3333', '123456789AB'),
	('99512345675', '1111-1111-1111-4444', '123456789ABCDEF'),
	(null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

random – четвертая функция предназначена для сокрытия числовых типов данных, но в отличии от функции default она позволяет не просто отображать нули, а некое случайно число в заданном диапазоне. Например, для колонки, где у нас указан возраст клиентов, можно генерировать некое случайно число от 18 до 100. Используется в формате random(<начало диапазона>, <конец диапазона>). И давайте посмотрим на соответствующий пример для этой функции:

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	age tinyint masked with (function = 'random(18, 100)') null,
	month tinyint masked with (function = 'random(1, 12)') null,

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

insert into dbo.[test] (
	age,
	month
)
values
	(18, 1),
	(19, 2),
	(20, 3),
	(30, 4),
	(55, 5),
	(null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

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

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

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	tmp varchar(100) masked with (function = 'default()') null,

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

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * into #tmp from dbo.test;
select * from #tmp;
revert;
go

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

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	tmp1 varchar(100) null,
	tmp2 varchar(100) masked with (function = 'default()') null,
	tmp3 varchar(100) masked with (function = 'default()') null,

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

insert into dbo.[test] (
	tmp1,
	tmp2,
	tmp3
)
values
	('qwe1@qwe.com', 'qwe2@qwe.com', 'qwe3@qwe.com');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

alter table [dbo].[test]
alter column tmp1 add masked with (function = 'default()');
go

alter table [dbo].[test]
alter column tmp2 drop masked;
go

alter table [dbo].[test]
alter column tmp3 varchar(100) masked with (function = 'email()');
go

execute as user = 'test_user';
select * from dbo.test;
revert;

grant unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;

revoke unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;
go

Есть также некоторые ограничения, которые действуют на замаскированные колонки: они не могут быть зашифрованы с помощью Always Encrypted, не поддерживается FILESTREAM, COLUMN_SET, вычисляемые столбцы, а также они не могут быть ключом для FULLTEXT индекса. Как видим, ограничения довольно небольшие и вполне ожидаемые, поэтому я не считаю их критичными.

Хотя Dynamic Data Masking предназначен для скрытия данных от того, кто их не должен видеть, он не спасет вас, если пользователь будет напрямую подключаться к баз данных и запускать запросы, которые будут вычислять скрытые данные. Эта возможность является лишь дополнением к остальным функциям, обеспечивающим безопасность данных (распределение прав доступа, шифрование, аудит и т.п.). Вы должны четко понимать модель угроз для ваших данных, чтобы правильно выбрать необходимые технологии для их защиты. В конце я хочу привести пример, который может вам позволить вычислять, какие же символы стоят в строке на определенных позициях, даже если строка от вас скрыта.

use [tempdb];
go

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

create table test (
	id int not null identity(1, 1),
	tmp text masked with (function = 'default()') null,

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

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select *, datalength(tmp), substring(tmp, 1, 1) from dbo.test;
select * from dbo.test where substring(tmp, 1, 1) = 'q';
select * from dbo.test where substring(tmp, 1, 1) = 'w';
revert;
go

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

На этом я бы хотел закончить рассказ о Dynamic Data Masking в SQL Server 2016. Получилось довольно объемно, но, на мой взгляд, удалось рассказать все самое важное об этом функционале с примерами.

SQL Server 2016: конфигурация tempdb во время установки

6 Replies

Несомненно, правильная или, наоборот, неправильная настройка tempdb может очень сильно повлиять на производительность SQL Server. Эта системная база данных является глобальным ресурсом и доступна всем пользователям, которые подключаются к SQL Server, и предназначена, чтобы хранить следующие данные:

  • Временные объекты, созданные пользователями: временные локальные или глобальные таблицы, табличные переменные, временные процедуры и курсоры.
  • Внутренние объекты, которые создаются движком SQL Server, например, рабочие таблицы (worktable), которые используются для хранения временных результатов сортировки, а также скрытые буферные таблицы (spool).
  • Версии строк, которые генерируются при модификации данных в базе, где используются оптимистичные уровни изоляции.
  • Версии строк, которые генерируются при онлайн перестроении индексов, AFTER триггеров или MARS (Multiple Active Result Sets).

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

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

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

Ядер CPU Количество файлов в tempdb
2 2
4 4
8 8
32 8

Возможно, и даже скорее всего, что 8 файлов для многопроцессорных нагруженных серверов будет мало (нет универсальной формулы, которая бы ответила на этот вопрос, нужно смотреть на характер нагрузки на ваш сервер), но по умолчанию это вполне разумные настройки. Создавать файлов больше, чем у вас ядер в системе, не будет иметь практического смыла, ввиду того, что одновременно с tempdb не будет работать больше сессий, чем у вас ядер в системе.

Далее, что еще бросается в глаза. По умолчанию размер файлов составляет 8 Мб и прирост 64 Мб. Опять же, в большинстве случаев эти цифры необходимо будет увеличить в зависимости от ваших потребностей. Хорошо, на мой взгляд, что в инсталляторе отсутствуют параметры прироста в процентах, что практически всегда является плохой практикой.

Еще стоит отметить, что вы можете указать несколько директорий, в которых будут созданы файлы данных. Файлы будут распределены по этим директориям по алгоритму round-robin. Например, вы указали создать 8 файлов данных и разместить их в 3х директориях. В этом случае установщик расположит их следующим образом:

Файл данных Директория
tempdb.mdf 1
tempdb_mssql_2.ndf 2
tempdb_mssql_3.ndf 3
tempdb_mssql_4.ndf 1
tempdb_mssql_5.ndf 2
tempdb_mssql_6.ndf 3
tempdb_mssql_7.ndf 1
tempdb_mssql_8.ndf 2

Улучшения в производительности при работе с tempdb

Также в работу tempdb были внесены следующие изменения, направленные на оптимизацию и ускорение выполнения запросов:

  • Кэширование временных объектов позволяет запросам, которые постоянно удаляют и создают временные объекты работать быстрее и уменьшают конкуренцию за системные ресурсы. В последних версиях SQL Server можно было регулярно видеть изменения и улучшения этого механизма.
  • Уменьшена нагрузка на журнал транзакций в tempdb, снижено количество требуемых I\O операций.
  • Доработан алгоритм накладывания latch’ей при выделении страниц, уменьшено их количество.
  • При приращении tempdb теперь одновременно будет увеличен размер всех файлов (отпадает необходимость включать флаг трассировки 1117). Опция AUTOGROW_ALL_FILES включена по умолчанию и не может быть изменена. Это поможет избежать разбалансирования размеров файлов при постоянно приросте tempdb.
  • Для временных объектов идет выделение только экстентами (блоками по 8 страниц, 64 кб). Отпадает необходимость включать флаг трассировки 1118. Это также поможет в большей части случаев.

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

Различия между операторами SET и SELECT при присваивании значений переменным

Leave a reply

В SQL Server в языке T-SQL имеется два оператора SET и SELECT, и они оба могут использоваться для присваивания значений переменным. В некоторых ситуациях использование того или иного оператора может привести к неожиданным и непредсказуемым результатам. В этой статье я бы хотел подробно рассмотреть различия между ними и рассказать о различных ловушках, в которые вы можете попасть.

В первую очередь, оба оператора могут равнозначно использоваться для присваивания фиксированных значений переменных. Однако, оператор SET является стандартом для языка SQL, в то время как SELECT является особенностью только T-SQL диалекта в SQL Server.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 1;
set @dt_example = getdate();
set @str_example = 'qwe';

select @int_example, @dt_example, @str_example;

select @int_example = 1;
select @dt_example = getdate();
select @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

Однако, если мы хотим за одну операцию инициализировать сразу несколько переменных, то необходимо использовать оператор SELECT.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select
    @int_example = 1,
    @dt_example = getdate(),
    @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

Если же мы попытаемся использовать в данной ситуации SET, то получим ошибку, т.к. он просто не поддерживает такую операцию.

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set
    @int_example = 1,
    @dt_example = getdate(),
    @str_example = 'qwe';

select @int_example, @dt_example, @str_example;
go

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘,’.

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = (select int_example from #tmp where id = 1);
select @int_example;

select @int_example = int_example from #tmp where id = 2;
select @int_example;
go

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = (select int_example from #tmp where id = 3);
select @int_example;
go

Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

В случае же использования оператора SELECT ошибки не возникнет, но в общем случае мы не можем точно предугадать, какое из удовлетворяющих условию выборки значений будет присвоено переменной. Рассмотрим первый случай, в котором запрос вернет 32.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select @int_example = int_example from #tmp where id = 3;
select @int_example;
go

В следующем случае запрос возвращает 31.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 32, '19030202', 'qwe32'),
    (3, 31, '19030101', 'qwe31');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

select @int_example = int_example from #tmp where id = 3;
select @int_example;
go

Как мы сумели убедиться, недетерминированность выборки может приводить к неожиданным результатам. Поэтому в данном случае я бы рекомендовал использовать оператор SET, чтобы как минимум получить ошибку, а также следить за условиями выборки, чтобы не допускать подобных ситуаций.
И еще один момент, на котором я бы хотел остановиться, когда под условия выборки не попадает ни одной записи и при этом значение переменной уже инициализировано каким-либо значением.

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 5;
set @int_example = (select int_example from #tmp where id = 4);
select @int_example;

set @int_example = 5;
select @int_example = int_example from #tmp where id = 4;
select @int_example;
go

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

create table #tmp (
    id int not null,
    int_example int not null,
    dt_example datetime not null,
    str_example varchar(255) not null
);

insert into #tmp (
    id,
    int_example,
    dt_example,
    str_example
)
values
    (1, 1, '19000101', 'qwe'),
    (2, 2, '19020101', 'qwe2'),
    (3, 31, '19030101', 'qwe31'),
    (3, 32, '19030202', 'qwe32');

declare
    @int_example int,
    @dt_example datetime,
    @str_example varchar(255);

set @int_example = 5;
set @int_example = (select int_example from #tmp where id = 4);
select @@rowcount;
select @int_example;

set @int_example = 5;
select @int_example = int_example from #tmp where id = 4;
select @@rowcount;
select @int_example;
go

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

  • Если вы присваиваете фиксированные значения переменным без использования запросов и хотите следовать стандартам.
  • Ожидаете, что переменной будет присвоено значение NULL, если запрос не вернул никаких результатов.
  • Запрос может вернуть несколько значений, и вы хотите отслеживать такие ситуации.

И используйте оператор SELECT, если:

  • Хотите за одну инструкцию присвоить значения сразу нескольким переменным.
  • В случае выборки значения из таблицы готовы контролировать, действительно ли произошла инициализация, например, с помощью @@rowcount.

Что такое RPO и RTO

1 Reply

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

Итак, RPO (recovery point objective) это максимальный период времени, за который могут быть потеряны данные в результате инцидента. Например, у нас имеется информационная система и RPO для нее мы определили в 1 час. Это значит, что, если вдруг происходит авария, мы готовы к тому, что систему удастся восстановить, но в ней будут потеряны данные не более, чем за последний час. Количество потерянных данных может быть меньше, если нам повезет, но не более 1 часа. Этот показатель говорит нам о том, как часто мы должны делать резервные копии нашей системы, и какие технологии применять, чтобы удержать этот показатель. Может ли он быть ноль? Теоретически да, но на практике организовать это очень сложно. Это можно организовать, только, если запись идет как минимум в 2 разных хранилища. Использование AlwaysOn Availability Groups или Database Mirroring вам не поможет, т.к. эти технологии не спасут от случайного удаления таблицы.

RTO (recovery time objective) это промежуток времени, в течение которого система может оставаться недоступной в случае аварии. Например, в центре обработки данных произошел пожар, но мы хотим, чтобы система была снова доступна для работы через 2 часа. Это и есть наш RTO. Мы должны планировать так, чтобы за этот промежуток восстановить работоспособность информационной системы на резервном оборудовании или площадке. Это можно реализовать с помощью различных технологий отказоустойчивости или же простым восстановлением из резервных копий на другой сервер. В любом случае мы должны обеспечить этот показатель при инциденте.

Кто определяет эти числа? В идеале, их вам должен сказать владелец сервисов. Обычно это представитель бизнеса, который редко знает, что это за показатели. Ваша задача состоит в том, чтобы совместно с ним прийти к итоговому решению, т.к. чем меньше эти показатели, тем больше ресурсов потребуется, чтобы их обеспечить. Например, для тестовой базы данных не нужен показатель RPO в 1 минуту, т.к. они, скорее всего, готовы потерять все данные целиком и просто повторно их сгенерировать. А вот RTO для них может быть критичен, т.к. в случае недоступности, несколько человек не смогут работать какое-то время. В то же время не надо переусердствовать, обеспечивая очень маленький показатель, т.к. затраты на его поддержку могут легко превысить оплату труда простаивающих специалистов. В то же время для основных баз данных эти показатели будут играть большую роль, т.к. от них будет зависеть доход и репутация компании.