SQL Server 2016: DROP IF EXISTS

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

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

В 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 во время установки

Несомненно, правильная или, наоборот, неправильная настройка 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 при присваивании значений переменным

В 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

В этой статье я бы хотел подробно остановиться на двух магических показателях, с которыми необходимо определиться до того, как планировать резервное копирование и восстановление не только баз данных 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 для них может быть критичен, т.к. в случае недоступности, несколько человек не смогут работать какое-то время. В то же время не надо переусердствовать, обеспечивая очень маленький показатель, т.к. затраты на его поддержку могут легко превысить оплату труда простаивающих специалистов. В то же время для основных баз данных эти показатели будут играть большую роль, т.к. от них будет зависеть доход и репутация компании.

MAXDOP и Resource Governor в MS SQL Server

В этой небольшой заметке я бы хотел немного рассказать о тонкостях в настройках параллелизма в Microsoft SQL Server. Очень многим из вас давно известна опция Max Degree od Parallelism, которая присутствует в SQL Server уже очень давно. По умолчанию она выставлена в 0, что значит, что SQL Server будет сам выбирать оптимальную степень параллелизма, то есть количество процессоров\потоков, задействованных для выполнения одной инструкции. Я сейчас не буду останавливаться и рассуждать, в какое же именно значении лучше выставлять эту опцию – это тема для отдельное заметки. Я лишь рассмотрю, как значение этой опции влияет на выполнение запросов. Например, ниже на рисунке, эта опция выставлена в 1, что означет, что параллельные планы для всех запросов по умолчанию отключены.

Также данная опция доступна для просмотра с помощью следующей команды T-SQL:

И действительно, любой план запроса по умолчанию будет последовательным. Например:

Однако, у разработчика и любого пользователя по-прежнему остается возможность повлиять на это путем использования подсказок (hints). Для этого всего лишь нужно указать нужную степень параллелизма, и генерируется нужный план запроса, например:

И если мы понаблюдаем на этот запрос через представление sys.dm_exec_query_profiles, то увидим, что он действительно выполняется в 10 потоков.

Таким образом, в системе остается потайной лаз, который могут использовать разработчики и пользователи, чтобы «ускорить» (тут я специально поставил в кавычки, т.к. не всегда большая степень параллелизма ведет к уменьшению времени выполнения запроса) свои запросы путем увеличения степени параллелизма. Но, таким образом, они могут просто «убить» сервер, запуская множество неконтролируемых параллельных запросов одновременно. Что же мы можем с этим сделать? Вот тут нам на помощь приходит Resource Governor, очень мощная и совершенно недооцененная система, которая позволяет очень гибко распределить ресурсы между разными группами пользователей. Опять же, я сейчас не буду останавливаться на том, как он устроен, и какими возможностями обладает. Я лишь остановлюсь подробно, как влияют его настройки ограничения параллелизма. Давайте для начала взглянем в настройки по умолчанию:

Опять мы видим, что по умолчанию опция выставлена в 0 и решение о выборе максимальной степени отдано на откуп SQL Server. Теперь посмотрим, что будет, если я поменяю это значение в 5. Внимание, ни в коем случае не делайте такие настройки на реальной системе, т.к. я даже не определил функцию классификации для Resource Governor и меняю default группу. Но для теста и понимания, как все работает конкретно сейчас на моем примере, этого хватит. Таким образом, я ограничиваю для всех максимальную степень параллелизма 5 потоками. Напомню, что опция Max Degree of Parallelism, которую мы рассматривали ранее выставлена по-прежнему в значение 1. Если мы теперь посмотрим на план выполнения нашего изначального запроса, то по умолчанию он будет последовательный, а с опцией maxdop 10 – параллельный. Но, если мы запустим параллельный план, то увидим кое-что интересное.

Теперь наш запрос выполняется только в 5 потоков, несмотря на то, что опция maxdop для него имеет значение 10. И, если вы укажете для запроса опцию maxdop 4, он будет выполняться в 4 потока (опция в Resource Governor установлена в 5). В этом случае подсказка maxdop меньше настройки Resource Governor, поэтому дополнительного ограничения не накладывается. Пример этого я уже не привожу.

Таким образом, Resource Governor является более мощным средством, который уже реально ограничивает максимальную степень параллелизма для запросов, и эту степень можно задать разную для разных групп пользователей. При этом опция Max Degree of Parallelism по-прежнему продолжает работать и вносит свою лепту (или слегка запутывает администраторов, разработчиков и пользователей, когда работает в купе с Resource Governor). Далее, лишь только вашей фантазией ограничены варианты выставления значений этих 2х параметров, но важно помнить лишь две вещи: Max Degree of Parallelism и подсказка (hint) maxdop для запроса влияет на то, какой план будет сгенерирован, сколько максимальное количество потоков будет возможно для этого запроса, а Resource Governor еще дополнительно сверху ограничивает запрос уже во время выполнения.

Выступление на IT Campus 26 июля 2014 г.

В последнюю пятницу июля традиционно отмечают День системного администратора. И именно в этот день у многих IT специалистов Москвы и всей России есть шанс посетить конференцию IT Campus. 3 дня участники будут проживать в палаточном городке, который они возведут собственными силами примерно в 30 километрах от г. Калуга. Организаторам удалось привлечь большое количество спонсоров и составить, на мой взгляд, очень интересную программу, а также большое количество дополнительных мероприятий, включая различные спортивные соревнования, турниры по Quake 3, настольные игры, выступления музыкальных коллективов. Цена за участие составляет всего 200 рублей, а все подробности вы можете узнать на сайте конференции. По-моему это шанс отлично провести выходные да еще и с пользой.

Одно из центральных мест на конференции будет занимать Дом Microsoft, где также пройдет большое количество активностей, например, виртуальные соревнования по скоростному спуску на коньках Red Bull Crashed Ice Kinect, где все желающие смогут попытаться пройти на Xbox одну из пяти сложнейших трасс, на которых в разное время бились за победу спортсмены Red Bull Crashed Ice. Также вы сможете пообщаться с представителями компании и узнать больше о продуктах и сервисах компании.

Я очень рад, что меня пригласили с докладом. Я буду рассказывать о практиках и методах построения отказоустойчивых решений для MS SQL Server 26 июля 2014 г. в субботу в 12:30. Буду делиться своим практических опытом, с чего начать, какие технологии доступны. Поэтому приглашаю всех желающих послушать. Также в этот день практически с самого утра и до позднего вечера вы сможете найти меня в Доме Microsoft, где я буду дежурить как эксперт и готов ответить на практически любые ваши вопросы об MS SQL Server.

Буду рад встречи со всеми и, надеюсь, погода не подведет и всех ждут отличные выходные!

Повреждение данных в SQL Server 2012 и 2014 при перестроении индексов в режиме online

Всегда, когда заходит речь о причинах повреждения данных в SQL Server, я называю программные ошибки в операционной системе и самом продукте. К счастью, это крайне редкий случай, но всем людям свойственно ошибаться, а SQL Server тоже пишут люди. Один их таких случаев произошел совсем недавно и затрагивает новые версии продуктов: SQL Server 2012 и 2014. Если кратко, то при онлайн перестроении индексов в вышеуказанных продуктов может возникнуть повреждение индексов или потеря данных, если при этом параллельно выполняются запросы на изменение большого количества строк и в определенном порядке возникает ошибка взаимоблокировки и фатальная ошибка, такая как «lock timeout». Проблема довольно серьезная, поэтому стоит обратить на нее очень пристальное внимание и установить вышедшие обновления. Дополнительно описание и ссылку на скачивание исправлений можно получить по нижеуказанной ссылке. Исправление доступно только для SQL Server 2012 SP1 и SP2, а также SQL Server 2014. Для SQL Server 2012 RTM его нет и не предвидится. FIX: Data corruption occurs in clustered index when you run online index rebuild in SQL Server 2012 or SQL Server 2014 Стоит обратить особое внимание на то, что недавно вышедший SP2 для SQL Server 2012 не содержит указанного исправления. Поэтому, если вы придерживаетесь политики ставить только сервис паки и игнорируете кумулятивные обновления или установку отдельных исправлений, то у вас могут возникнуть большие проблемы. На мой взгляд, ситуация, когда организации переходят на новые версии продукта тогда, когда становится доступен только первый или второй сервис пак не оправдана. Да, я согласен с тем, что не стоит бросаться сломя голову и обновлять ваш сервер, как только вышла новая версия продукта, но и ждать так долго тоже не имеет смысла. Сейчас мир меняется очень быстро, и релиз циклы начинают уменьшаться, что мы все отчетливо можем наблюдать эту тенденцию на примере версий 2012 и 2014. Второй сервис пак для SQL Server 2012 вышел уже после официального выхода SQL Server 2014, а третий, я боюсь, уже не будет выпущен. Но немного вернемся к основной проблеме. Как проверить, что на ваш SQL Server установлены все необходимые исправления? Для этого выполните команду select @@version на вашем сервере и результат сверьте с нижеприведенной таблицей:

SQL Server 2012 RTM Для указанной версии обновлений недоступно. Рекомендуется установить первый или второй сервис пак, а потом исправление KB #2969896.
SQL Server 2012 SP1 В случае, если версия ниже 11.0.3437, то установите исправление KB #2969896.
SQL Server 2012 SP2 В случае, если версия ниже 11.0.5522, то установите исправление KB #2969896.
SQL Server 2014 RTM В случае, если версия ниже 12.0.2370, то установите исправление KB #2969896или второй накопительный пакет исправлений KB # 2967546.

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

  • Вы можете временно отключить перестроение индексов совсем.
  • Вы можете установить опцию max degree of parallelism на уровне сервера в значение 1, но учтите, что это может негативно сказаться на производительности остальных запросов.
  • Вы можете добавить опцию WITH (MAXDOP = 1) ко всем командам перестроения индексов. В случае, если вы используете стандартные Maintenance Plans, то в них нет возможности указать, что перестроение индексов необходимо делать в однопоточном режиме. Если вы используете какие-либо еще утилиты для перестроения индексов, то уточняйте возможность перестроения в однопоточном режиме в их документации.

Как это было: февральский семинар Russian SQL Server User Group

В феврале мы открыли новый сезон встреч Russian SQL Server User Group в Москве. И место встречи тоже было новым: в настоящий момент Microsoft Technology Center, наше привычное место встреч, переезжает в соседнее здание и поэтому пришлось поискать другой вариант. В нашей группе на Facebook был проведен опрос и большинство проголосовало за встречу в новом офисе Лаборатории Касперского. Эта компания любезно предоставила нам большой зал с двумя экранами, куда легко влезли все желающие прийти. Зарегистрировалось порядка 60 человек, а пришло около 30. В очередной раз статистика посещения показывает, что приходит от 40 до 60 процентов зарегистрировавшихся. В программе было 3 доклада, подробнее с ней можно ознакомиться по указанной ссылке.

Также публикую несколько фотографий с мероприятия. Качество не очень, но пусть будут на память. Ждите в ближайшее время анонса следующей встречи. J

Февральский семинар Russian SQL Server User Group в Москве

В свзяи с тем, что привычное всем место встречи Microsoft Technology Center на Белорусской находится в процессе переезда (в соседнее здание) и недоступен для встреч до середины марта, решено было провести встречу в необычном месте. Путем голосования в группе на Facebook большинство голосов было отдано в пользу офиса Лаборатории Касперского. Встреча состоится 11 февраля в 18:00 по адресу Москва, Ленинградское шоссе, д.39А, стр.2, БЦ «Олимпия Парк» (средний корпус из трех), м. Водный стадион (последний вагон из центра). Те, кто планирует добраться до места на машине, учитывайте, что парковку придется искать самостоятельно, на территорию бизнес центра машины пропускать не будут. Регистрация на мероприятие обязательна по указанной ссылке, т.к. списки будут подаваться на охрану. При входе необходимо будет предъявить паспорт и получить временный пропуск. Ознакомиться с программой мероприятия вы можете ниже.

Рассказ о группе Whitelisting в Лаборатории Касперского
Лаборатория Касперского предпринимает все возможные усилия для защиты пользователей своих продуктов от вредоносного кода. Один из процессов – поиск и анализ «хорошего» ПО – файлов, которые заведомо не несут угрозы пользователям. Расскажем о том, как и какие объемы данных обрабатываются в Лаборатории для предотвращения ложных срабатываний антивирусных продуктов.
Докладчик: Дмитрий Костылев. Начал работать с Microsoft SQL Server в 2000 году. Основная профессиональная деятельность – разработка и поддержка как самих баз данных, так и больших проектов, интенсивно использующих реляционные хранилища. В данный момент под его контролем работает и развивается одна из баз знаний о файлах в «Лаборатории Касперского».
Продолжительность доклада: 20 мин.

Новые возможности для объединения объектно-ориентированного подхода и реляционной модели данных.
Возможности для простого создания эффективных и гибких решений всегда была интересны для разработчиков информационных систем. Одной из причин, затрудняющей достижение этой цели, является традиционная архитектура, разделяющая хранение и обработку данных, что, в свою очередь, вызвано отсутствием в современных СУБД простых средств для создания выразительных бизнес моделей, допускающих сложную обработку данных. Считается, что это невозможно из-за комплекса проблем, который принято называть «Object-Relational ImpedanceMismatch». Но так ли это?
В докладе будет представлен новый, оригинальный подход к решению этого вопроса, соединяющий ОО подход к описанию данных с реляционной моделью данных. Действующий прототип продемонстрирует эволюционный способ развития реляционных СУБД по направлению к независимым системам управления гибкими выразительными моделями предметной области, позволяющим эффективно обрабатывать данные на стороне сервера.
Докладчик: Евгений Григорьев, тех. директор компании “PxO проект”.
Продолжительность доклада: 1 час 40 мин. (презентация RxO технологии – 1 час 20 мин., вопросы, обсуждение – 20 мин.).

Планирование архитектуры SQL Server для обработки больших данных. Анализ и управление статистикой.
Доклад предназначен для внедряющих СЭДД, архитекторов, специалистов тех. поддержки. Ключевые моменты, который будут обсуждаться:

  • типовая архитектура СЭД и основные ключевые требования к ним
  • личный опыт использования/внедрения SQL Server в системах СЭДД до 4000 пользователей
  • успешные реализованные проекты на базе SQL Server
  • планирование и проектирование систем поддержки и управления SQL Server
  • планирование и проектирование систем повышения доступности и обеспечения отказоустойчивости для СУБД SQL Server
  • рекомендации из личного опыта – обмен опытом/вопросы
  • анализ данных и управление статистикой

Докладчик: Максим Лемешко, более 3 лет работает с SQL Server в качестве архитектора высоконагруженных систем. Использует собственные наработки в области проектирования хранилищ данных, методов оптимизации и управления статистикой. На данный момент занимается развитием бизнеса в области облачных сервисов.
Продолжительность доклада: 1 час.