T-SQL.RU

Массовая замена текста в объектах SQL Server

a.knyazev@t-sql.ru — Tue, 27 Sep 2016 23:57:00 +0800

Если у вас возникнет потребность внести массовые изменения во все объекты БД (заменить одно значение на другое), то следующий скрипт сможет облегчить вам эту задачу. Кроме того, что он заменять указанный текст на новый, он ещё сохраняет на диск старую и новую версию файла, а это поможет вам в последствии легко заменить файлы в системе контроля версий, например, в TFS.
Скрипт написан на PowerShell и в текущей реализации работает с процедурами, функциями, триггерами и представлениями, но может быть легко дополнен. Для выгрузки скриптов в файлы используются два параметра: $backupFolder и $changeFolder. Кроме того, если вам не нужно заменять объекты на сервере, а только выгрузить файлы для последующей замены, то достаточно поменять параметр $alter с $true на $false.

$ServerName=Read-Host "Укажите имя сервера БД"
# Определяем имя БД
$DatabaseName=Read-Host "Введите имя БД"
# Определяем текст для поиска
$OldText=Read-Host "Введите текст для поиска" 
# Определяем текст для замены
$NewText=Read-Host "Введите текст для замены" 

$backupFolder = "c:\temp\old\";  
$changeFolder = "c:\temp\new\";
$alter = $true;  

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$server=new-object("Microsoft.SqlServer.Management.Smo.Server") $ServerName
 
#Имя БД, с которой работаем
$db = $server.Databases[$DatabaseName]

 
# Создаём соединение
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$ServerName;Integrated Security=SSPI;Initial Catalog=$DatabaseName") 
 
# Создаем SQL команду, используя наш запрос и созданное соединение
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand("
select object_schema_name(m.object_id) as sch
     , object_name(m.object_id) as obj
     , o.type   
     , o.type_desc
     , object_schema_name(o.parent_object_id) as parent_sch
     , object_name(o.parent_object_id) as parent_obj
    from sys.sql_modules m
        inner join sys.objects o
          on m.object_id = o.object_id
    where m.definition like '%$OldText%'
    order by 2
", $SQLConnection) 
 
# Открываем соединение
$SQLConnection.Open()
 
# Выполняем….
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)
$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
 
$DataTable.DefaultView | Out-GridView

foreach ($Row in $DataTable.Rows)
{
   $type = $($Row[2])
   
   #StoredProcedures
   if ( $type -match "P" )
   {
    $proc = $db.StoredProcedures | where {$_.Schema -eq $($Row[0]) -and $_.Name -eq $($Row[1])}
    $proc.Script() | Out-File ($backupFolder + "StoredProcedures\" + [string]$proc.name + ".sql");
    $proc.Script() -replace($OldText, $NewText) | Out-File ($changeFolder + "StoredProcedures\" + [string]$proc.name + ".sql");
        if($alter)
        {
        $proc.TextBody = $proc.TextBody -replace($OldText, $NewText);
        $proc.Alter();
        Write-Host "Altered " $proc.Name;
        }    
   }
   
   #Views
   if ( $type -match "V" )
   {
    $view = $db.Views | where {$_.Schema -eq $($Row[0]) -and $_.Name -eq $($Row[1])}
    $view.Script() | Out-File ($backupFolder + "Views\" + [string]$view.name + ".sql");
    $view.Script() -replace($OldText, $NewText) | Out-File ($changeFolder + "Views\" + [string]$view.name + ".sql");
        if($alter)
        {
        $view.TextBody = $view.TextBody -replace($OldText, $NewText);
        $view.Alter();
        Write-Host "Altered " $view.Name;
        }    
   }
   
   #Triggers
   if ( $type -match "TR" )
   {
    $table = $db.Tables | where {$_.Schema -eq $($Row[4]) -and $_.Name -eq $($Row[5])}
    $trigger = $table.Triggers | where {$_.Name -eq $($Row[1])}
    $trigger.Script() | Out-File ($backupFolder + "Triggers\" + [string]$trigger.name + ".sql");
    $trigger.Script() -replace($OldText, $NewText) | Out-File ($changeFolder + "Triggers\" + [string]$trigger.name + ".sql");
        if($alter)
        {
        $trigger.TextBody = $trigger.TextBody -replace($OldText, $NewText);
        $trigger.Alter();
        Write-Host "Altered " $trigger.Name;
        }    
   }
   
   #UserDefinedFunctions (SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_TABLE_VALUED_FUNCTION)
   if ( ( $type -match "FN" ) -or ( $type -match "IF" ) -or ( $type -match "TF" ) )
   {
    $func = $db.UserDefinedFunctions | where {$_.Schema -eq $($Row[0]) -and $_.Name -eq $($Row[1])}
    $func.Script() | Out-File ($backupFolder + "UserDefinedFunctions\" + [string]$func.name + ".sql");
    $func.Script() -replace($OldText, $NewText) | Out-File ($changeFolder + "UserDefinedFunctions\" + [string]$func.name + ".sql");
        if($alter)
        {
        $func.TextBody = $func.TextBody -replace($OldText, $NewText);
        $func.Alter();
        Write-Host "Altered " $func.Name;
        }    
   }
     
}

# Закрываем соединение
$SQLConnection.Close()

Минимальное протоколирование (MIN_LOGGED) и полная (FULL) модель восстановления

a.knyazev@t-sql.ru — Sat, 02 Apr 2016 19:14:00 +0800

SQL Server позволяет повысить производительность ряда операций за счёт минимального протоколирование изменений в журнал транзакций.
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени.
Список таких операций впечатляет (https://msdn.microsoft.com/ru-ru/library/ms190925.aspx):

Операции массового импорта (bcp, BULK INSERT и INSERT... SELECT)
Операции SELECT INTO
Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text, ntext и image
Частичные обновления типов данных с большими значениями с помощью предложений. WRITE инструкции UPDATE при вставке или добавлении новых данных
Операции с индексами (CREATE INDEX [включая индексированные представления], ALTER INDEX REBUILD или DBCC DBREINDEX, DROP INDEX), тесты над индексами я проводил ранее - http://t-sql.ru/post/index_minimally_logged.aspx

За счёт минимального протоколирования можно в разы сократить время выполнения привычных запросов, но это возможно, только если у вас используется модель восстановления – простая (SIMPLE) или модель восстановления с неполным протоколированием (BULK LOGGED).
Как же быть, если мы используем полную модель восстановления (FULL)? В BOL по этому поводу написано:
В модели полного восстановления все массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, временно переключив базу данных на модель восстановления с неполным протоколированием во время массовых операций. Минимальное протоколирование более эффективно, чем полное, и снижает вероятность того, что во время массовой операции большого объема будет заполнено все доступное пространство журнала транзакций. Однако, если при включенном минимальном протоколировании база данных будет повреждена или потеряна, ее нельзя будет восстановить до точки сбоя.
Но всё ли так однозначно? Давайте проведём небольшое тестирование одной из операций с минимальным протоколированием (операции массового импорта) в полной модели восстановления.

use master;
go

--Удалим БД, если она уже существует
if db_id( 'MIN_LOGGED' ) is not null
begin
  alter database MIN_LOGGED set restricted_user with rollback immediate;
  drop database MIN_LOGGED;
end

--Создадим БД для тестов
create database MIN_LOGGED;
go
--Переключим модель в режим полного протоколирования
alter database MIN_LOGGED set recovery full;
go
--Переключение на модель полного восстановления или модель восстановления с неполным протоколированием 
--вступает в силу только после создания первой резервной копии данных
backup database MIN_LOGGED to disk = 'c:\temp\MIN_LOGGED_01.bak';
go

use MIN_LOGGED;
go

--Создадим 2 таблицы для тестов
create table dbo.test_table_01 (id int identity, val varchar(255));
go
create table dbo.test_table_02 (id int identity, val varchar(255));
go
set statistics time on;
go
--Вставка с хинтом TABLOCK 
--(позволяет использовать минимальное протоколирование для массовой загрузки)
--https://msdn.microsoft.com/ru-ru/library/ms190422.aspx
insert into dbo.test_table_01 with (tablock)
select replicate('A', 255) from sys.all_objects t1, sys.all_objects t2;
go
insert into dbo.test_table_02
select replicate('A', 255) from sys.all_objects t1, sys.all_objects t2;
go
set statistics time off;
go

/*
SQL Server Execution Times:
  CPU time = 5445 ms,  elapsed time = 211902 ms.
SQL Server Execution Times:
  CPU time = 16333 ms,  elapsed time = 280117 ms.
*/

Разница во времени выполнения запросов существенна 211 vs 280 секунд (или 25%). Обратимся к журналу транзакций:

--Посмотрим сколько строк у нас записалось в журнал транзакций
select count(*) cnt 
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.test_table_01%';
go

select count(*) cnt 
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.test_table_02%';
go

Кроме того, в ряде случаев страница BCM - Bulk Change Map (Схема массовых изменений), она отслеживает экстенты, измененные операциями с неполным протоколированием, показывает нам что операция выполнялась с минимальным протоколированием.

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


--Запомним, сколько записей в dbo.test_table_01
exec sp_spaceused 'dbo.test_table_01'; --4 647 336                          
go 
begin transaction flag with mark 'flag';
delete top (1000) from dbo.test_table_01;
commit transaction
go
--Убедимся, что удаление произошло
exec sp_spaceused 'dbo.test_table_01'; --4 646 336                                       
go 
--Очистим таблицу
truncate table dbo.test_table_01;
go
--Сделаем резервную копию журнала транзакций
backup log MIN_LOGGED to disk = 'c:\temp\MIN_LOGGED_ldf_01.bak';
go

--Восстановим БД на момент начала транзакции "flag"
use master;
go
restore database MIN_LOGGED from disk = 'c:\temp\MIN_LOGGED_01.bak' with norecovery, replace;
go
restore database MIN_LOGGED from disk = 'c:\temp\MIN_LOGGED_ldf_01.bak' with stopatmark = 'flag', recovery;
go

use MIN_LOGGED;
go
exec sp_spaceused 'dbo.test_table_01'; --4 646 336                                       
go

Вуаля!!!, мы смогли восстановиться на нужную нам «точку». Но почему же у нас операция была помечена, как операция с минимальным протоколированием и почему операция выполнилась быстрее?
Во-первых, страница Bulk Change Map актуальна только для модели восстановления с неполным протоколированием (BULK LOGGED).
Во-вторых, ряд операций минимально протоколируется и при полной модели восстановления, например, TRUNCATE (в журнал транзакций записывает только данные об освобождении страниц).
В-третьих, при анализе журнала транзакций, необходимо смотреть не только кол-во операций, которые попали в лог, но и их размер [Log Record Length], тогда разница будет уже не такая существенная.

Основное отличие в том, что SQL Server для операций массовой вставки выделяет сразу целые страницы и экстенты, а не по строкам, тем самым количество записей существенно меньше, но их размер больше.
Но основное преимущество от минимального протоколирования мы сможем получить только при режиме восстановления BULK LOGGED, либо SIMPLE. И если проделать все операции, описанные выше, уже при одной из этих моделей, то мы получим следующие цифры (что уже на порядок лучше):

SQL Server Execution Times:
  CPU time = 15476 ms,  elapsed time = 82727 ms.

SQL Server Execution Times:
  CPU time = 16801 ms,  elapsed time = 202738 ms.

А при попытке восстановить БД на определённый момент времени мы получим ошибку:

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

Ссылка по теме: http://www.enabledbusinesssolutions.com/blog/?p=1222

24 Hours of PASS, 2016

a.knyazev@t-sql.ru — Wed, 30 Mar 2016 19:55:00 +0800

16 - 17 марта 2016 года прошла 5-ая конференция 24 Hours of PASS
Записи докладов: https://www.youtube.com/user/russianvc.

Мой доклад SQL Server 2016. Temporal Tables:

Temporal Tables - это один из новых функционалов, который появится в SQL Server 2016.
В докладе мы подробно рассмотрим эту технологию, поговорим о преимуществах от её использования на примере реальных задач. Сравним с другими способами отслеживания изменений, такими, как логирование с помощью DML-триггеров, Change Tracking (CT) и Change Data Capture (CDC).

[youtube:-6tr7Mo5C3c]
Презентация и архив с примерами.

Temporal_Tables.pptx (2,90 mb)

Demo.zip (6,44 kb)

Error 5161: An unexpected file id was encountered. File id %d was expected but %d was read from

a.knyazev@t-sql.ru — Mon, 08 Feb 2016 20:38:00 +0800

Ошибка 5161 - An unexpected file id was encountered. File id %d was expected but %d was read from "%.*ls". Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
может возникнуть, когда вы пытаетесь подменить файлы БД.

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

Создать новую БД с аналогичной структурой
С помощью операции ALTER DATABASE ... MODIFY FILE указать расположение файлов, которые нам нужно "подцепить"
Перезапуск SQL Server

После этого, вместо того, чтобы получить доступ к данным, ваша БД переходит в статус Recovery Pending.
Если посмотреть журнал ошибок SQL Server, то там могут присутствовать ошибки:

Причина этой ошибки в том, что файлы вашей исходной БД имели другие идентификаторы, например:

А когда вы создаёте пустую БД, то файлы идут по порядку:

CREATE DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'db', FILENAME = N'c:\temp\db\db.MDF'  ),
( NAME = N'FG_01', FILENAME = N'c:\temp\db\01.ndf'  ),
( NAME = N'FG_02', FILENAME = N'c:\temp\db\02.ndf'  ),
( NAME = N'FG_03', FILENAME = N'c:\temp\db\03.ndf'  ),
( NAME = N'FG_04', FILENAME = N'c:\temp\db\04.ndf'  ),
( NAME = N'FG_05', FILENAME = N'c:\temp\db\05.ndf'  ),
( NAME = N'FG_06', FILENAME = N'c:\temp\db\06.ndf'  )
 LOG ON
( NAME = N'db_log', FILENAME = N'c:\temp\db\db_Log.LDF' )
go

Если попытаться "подсунуть" файлы нужной БД на место текущих, то мы получим ошибку, озвученную выше. Для того, чтобы её избежать необходимо убедиться, что порядок файлов в sys.master_files соответствует, тому, что указан внутри системной таблицы sys.sysprufiles (object_id = 24), которая хранится в mdf-файле.
Самый простой сценарий для подключения файлов в такой БД:

--Создание пустой БД
create DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'db', FILENAME = N'c:\temp\db\db.MDF'  ),
--2 файла для подмены
( NAME = N'FG_xx1', FILENAME = N'c:\temp\db\xx1.ndf'  ),
( NAME = N'FG_xx2', FILENAME = N'c:\temp\db\xx2.ndf'  ),
---------------------
( NAME = N'FG_01', FILENAME = N'c:\temp\db\01.ndf'  ),
( NAME = N'FG_02', FILENAME = N'c:\temp\db\02.ndf'  ),
( NAME = N'FG_03', FILENAME = N'c:\temp\db\03.ndf'  ),
( NAME = N'FG_04', FILENAME = N'c:\temp\db\04.ndf'  ),
( NAME = N'FG_05', FILENAME = N'c:\temp\db\05.ndf'  ),
( NAME = N'FG_06', FILENAME = N'c:\temp\db\06.ndf'  )
 LOG ON
( NAME = N'db_log', FILENAME = N'c:\temp\db\db_Log.LDF' );
go

--теперь можно эти файлы удалить
alter database [MyDB] remove file FG_xx1;
go
alter database [MyDB] remove file FG_xx2;
go

--Указываем новое расположение файлов (файлы, которые необходимо подключить)

ALTER DATABASE MyDB
MODIFY FILE (NAME = db, FILENAME = N'c:\temp\db\new\db.MDF');
go
ALTER DATABASE MyDB
MODIFY FILE (NAME = db_log, FILENAME = N'c:\temp\db\new\db_log.LDF');
GO
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_01, FILENAME = N'c:\temp\db\new\01.ndf');
GO
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_02, FILENAME = N'c:\temp\db\new\02.ndf');
GO
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_03, FILENAME = N'c:\temp\db\new\03.ndf');
go
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_04, FILENAME = N'c:\temp\db\new\04.ndf');
go
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_05, FILENAME = N'c:\temp\db\new\05.ndf');
go
ALTER DATABASE MyDB
MODIFY FILE (NAME = FG_06, FILENAME = N'c:\temp\db\new\06.ndf');
go

Compress и Decompress

a.knyazev@t-sql.ru — Mon, 28 Dec 2015 02:16:00 +0800

Рассмотрим ещё одну новую возможность, которая появилась в SQL Server 2016 CTP 3.1. Это встроенная поддержка сжатия GZIP: COMPRESS и DECOMPRESS.

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

Для начала убедитесь, что вы используете SQL Server 2016 CTP 3.1 и выше. Я использую Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.73 (X64) Dec 10 2015 18:49:31 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 10586: ).

Создадим 2 таблицы:

--Простая таблица с текстовым полем
create table SimpleTable ( val varchar(max) );
go

--Таблица с GZIP
create table SimpleTableGZIP ( val varbinary(max) );
go

Наполним данными:

--SIMPLE
insert into SimpleTable
select replicate('A', 10000);
go 1000

--GZIP
insert into SimpleTableGZIP
select compress ( replicate('A', 10000) );
go 1000

Теперь сравним их объём:

select object_name( object_id ) table_name
     , reserved_page_count
     , ( reserved_page_count ) * 8. sizeKb
     , row_count
  from sys.dm_db_partition_stats
  where object_id in ( object_id( 'SimpleTable' )
                     , object_id( 'SimpleTableGZIP' )
                     );

А теперь небольшой тест на скорость вставки:

set statistics time on;
go
insert into SimpleTable
select t1.name + t2.name from sys.all_objects t1, sys.all_objects t2;
go
insert into SimpleTableGZIP
select compress ( t1.name + t2.name ) from sys.all_objects t1, sys.all_objects t2;
go
set statistics time off;
go

И тест на извлечение данных:

set statistics time on;
go
select * into #t1 
  from SimpleTable
go
select decompress(val) val into #t2
  from SimpleTableGZIP;
go
set statistics time off;
go

DROP IF EXISTS

a.knyazev@t-sql.ru — Thu, 05 Nov 2015 02:52:00 +0800

С выходом SQL Server 2016 CTP3 нас ожидает приятное дополнение в синтаксисе.

Изменения касаются DDL операций DROP и ALTER TABLE.

Теперь любую операцию по удалению объекта (таблицы, процедуры, функции, триггеры, индексы и т.д.), или столбца таблицы можно выполнить проще с помощью инструкции DROP IF EXISTS.

Для проверки существования объекта, перед его созданием использовался скрипт на подобии того, что ниже:

--1)
if object_id ( N'dbo.TestTable', N'U' ) is not null
drop table dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

--2)
if exists ( select * from sys.tables t
              inner join sys.schemas s
                on t.schema_id = s.schema_id
              where t.name = N'TestTable' 
                and s.name = N'dbo'
          )
drop table dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

Теперь есть ещё один способ (только начиная с CTP3 SQL Server 2016):

--3)
drop table if exists dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

Синтаксис поддерживается для следующих объектов:

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

Как на t-sql узнать запущена ли служба SQL Server, а если запущена, то под кем?

a.knyazev@t-sql.ru — Tue, 29 Sep 2015 00:00:00 +0800

Вопросы, которые периодически задают многие:

"Как узнать статус служб SQL сервер?"

"Как узнать под кем стартует сервис?"

"Как всё это сделать с помощью запроса T-SQL?"

Параметры служб можно легко получить через стандартную оснаску Windows "Службы (Services)" - services.msc

Либо через стандартную утилиту SQL Server Configuration Manager - SQLServerManagerXX.msc, где XX - номер версии SQL Server.

Но получить эту информацию на удалённом сервере можно и через запрос на T-SQL. Вот несколько способов:

1) Недокументированная процедура master..xp_servicecontrol у которой 2 входных параметра - команда (start/stop/pause/continue/querystate) и имя службы. Эта процедура позволяет перезапускать службы и просматривать текущий статус.

exec master.. xp_servicecontrol @Action = 'querystate', @ServiceName = N'MSSQLSERVER';

2) Используем недокументированную процедуру master..xp_regread, которая позволяет считывать настройки из реестра.

declare @ServiceName sysname;
exec master..xp_regread
      'HKEY_LOCAL_MACHINE'
    , 'SYSTEM\CurrentControlSet\services\MSSQLSERVER'
    , 'ObjectName' 
    , @ServiceName OUTPUT;
select @ServiceName as 'Log On As';

3) Через WMI (Windows Management Instrumentation)

exec master..xp_cmdshell 'wmic service where "Name like ''%sql%''" get Caption, StartName, Started';

4) Через утилиту командной строки SC.exe

exec master..xp_cmdshell 'SC query MSSQLSERVER';

5) С помощью запроса на PoSh (PowerShell):

exec master..xp_cmdshell '""C:\WINDOWS\system32\windowspowershell\v1.0\powershell" "get-service MSSQLSERVER | Format-List *""';

6) В SQL Server 2008R2 +SP1 появилось новое Динамическое административное представление sys.dm_server_services

select servicename, service_account, status_desc from sys.dm_server_services;

7) CLR-сборка

Live Query Statistics

a.knyazev@t-sql.ru — Wed, 19 Aug 2015 03:40:00 +0800

В родном клиенте SQL Server Management Studio 2016 появилась возможность в режиме реального времени просматривать, как выполняется запрос. Отслеживать построение плана и оперативно оптимизировать его.

Функционал поддерживается, как для новой версии SQL Server 2016, так и для SQL Server 2014 SP1.

В графическом виде можно видеть, как выполняется запрос:

Просмотреть live query statistics можно несколькими способами:

Кроме того, можно включить настройки SET STATISTICS XML ON или SET STATISTICS PROFILE ON, прежде чем запустить выполнение запроса. А в другой сессии выполнить запрос к системному представлению sys.dm_exec_query_profiles:

select st.text
, eqp.physical_operator_name
, eqp.row_count
, eqp.estimate_row_count
, 100 * eqp.row_count /eqp.estimate_row_count as [PercentComplete]
  from sys.dm_exec_query_profiles as eqp
    cross apply sys.dm_exec_sql_text (eqp.sql_handle) as st

Либо использовать расширенное событие (Extended Events) - query_post_execution_showplan

Ссылки по теме:

SQL Server 2016: Новые ON-Line операции

a.knyazev@t-sql.ru — Sun, 02 Aug 2015 03:10:00 +0800

С выходом каждой новой версии SQL Server появляется всё больше операций, которые могут быть проведены ON-Line, т.е. без длительных блокировок и ожиданий. В SQL Server 2014, например, мы увидели ON-Line операции над отдельными секциями секционированных объектов.

Новая версия SQL Server 2016 так же добавит несколько операций ON-Line.

Во первых появится возможность совершать операцию TRUNCATE над отдельными секциями, а не только над всей таблицей. О преимуществах операции TRUNCATE над операцией DELETE я не буду останавливать в этой статье, просто скажу, что за счёт минимального количества операций протоколирования, операция TRUCATE выполняется в разы быстрее, чем операция DELETE. При этом мы можем указывать не только отдельную секцию, но и целый диапазон.

truncate table dbo.test_table with ( partitions ( 1, 3 to 4 ) ); 
go

А вторая операция - это возможность выполнять изменение колонки в таблице в режиме ON-Line

alter table dbo.test_table 
alter column val varchar(50) not null 
with (online = on );
go

Для демонстрации TRUNCATE над отдельными секция создадим секционированную таблицу и наполним её данными:

create partition function pf_dt ( datetime )
as range right for values ( '20150801', '20150802', '20150803', '20150804', '20150805' );
go
 
create partition scheme ps_dt
as partition pf_dt all to ( [primary] );
go
 
create table dbo.test_table ( dt datetime, val varchar(50) ) on ps_dt (dt);
go
 
insert into dbo.test_table
select '20150801', replicate( 'A', 50 );
go 10
insert into dbo.test_table
select '20150802', replicate( 'A', 50 );
go 10
insert into dbo.test_table
select '20150803', replicate( 'A', 50 );
go 10
insert into dbo.test_table
select '20150804', replicate( 'A', 50 );
go 10
insert into dbo.test_table
select '20150805', replicate( 'A', 50 );
go 10

Убедимся, что в секциях появились данные:

select partition_number, rows 
from sys.partitions
where object_id = object_id ( 'dbo.test_table' ); 
go

Теперь выполним TRUNCATE над секциями 2 и 4-5, и убедимся, что данные удалились:

truncate table dbo.test_table with ( partitions ( 2, 4 to 5 ) );
go
select partition_number, rows 
from sys.partitions
where object_id = object_id ( 'dbo.test_table' ); 
go

А теперь протестируем, как работает On-Line операция изменения колонки. Создадим таблицу с большой колонкой, чтобы каждая запись занимала отдельную страницу и наполним её данными:

create table dbo.test_table ( id int identity primary key, val varchar(8000) );
go
insert into dbo.test_table
select replicate( 'A', 8000 ) from sys.all_columns;
go 100

Теперь попробуем изменить колонку val на NOT NULL, а в другом окне будем просматривать данные в таблице. Можно убедиться, что, когда мы делаем ALTER COLUMN в режиме ON-Line, то данные остаются доступны на чтение, а блокировка схемы Sch-M накладывается лишь в самом конце операции ALTER, когда происходит переключение на новые страницы с данными. При этом доступны не только сами данные, но и статистики, которые были созданы по этому полю и лишь после того, как данные колонки переключаются на новые страницы, статистика удаляется и её необходимо создать по новой. Для того, чтобы убедиться, что после ON-Line операции ALTER COLUMN изменились страницы выполним следующий скрипт:

drop table dbo.test_table;
go
create table dbo.test_table ( id int identity primary key, val varchar(8000) );
go
insert into dbo.test_table
select replicate( 'A', 8000 );
go
select l.page_id from dbo.test_table as t
  outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l;
go
alter table dbo.test_table 
alter column val varchar(8000) not null 
--with (online = on );
go
select l.page_id from dbo.test_table as t
  outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l;
go
-------------------------------------
drop table dbo.test_table;
go
create table dbo.test_table ( id int identity primary key, val varchar(8000) );
go
insert into dbo.test_table
select replicate( 'A', 8000 );
go
select l.page_id from dbo.test_table as t
  outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l;
go
alter table dbo.test_table 
alter column val varchar(8000) not null 
with (online = on );
go
select l.page_id from dbo.test_table as t
  outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l;
go

Результат ниже:

Подробнее про On-Line операцию ALTER COLUMN

SQL Server 2016: Row-Level Security

a.knyazev@t-sql.ru — Fri, 03 Jul 2015 02:48:00 +0800

Одна из интересных новинок в SQL Server 2016 - это Row-Level Security (RLS). Технология RLS (row-level security или безопасность на уровне строк) предоставляет возможность создания политик безопасности, которые ограничивают доступ пользователям к информации в БД.

Данная технология поддерживается во многих современных СУБД, но, к сожалению, в предыдущих версиях SQL Server реализовать RLS было не самой простой задачей. На сайте Microsoft есть целый документ, как это реализовать - http://download.microsoft.com/download/8/8/0/880F282A-AA4E-4351-83C0-DFFA3B56A19E/SQL_Server_RLS-CLS_White_paper.docx

Теперь рассмотрим, как это реализовано в SQL Server 2016

Создадим тестовую таблицу и наполним её данными:

if object_id ( N'dbo.TestRLS', N'U' ) is not null
drop table dbo.TestRLS;
go
create table dbo.TestRLS ( id int identity
                         , UserName sysname
                         , Val int
                         );
go
insert dbo.TestRLS 
values  ( 'User1', 11 ) 
      , ( 'User1', 12 )
      , ( 'User1', 13 )
      , ( 'User2', 21 )
      , ( 'User2', 22 )
      ;
go
select * from dbo.TestRLS;
go

Технология RLS состоит из 3-х компонентов:
Predicate function - Пользовательская функция описывает логику безопасности
Security predicate - Применяет предикат к конкретной таблице. Два типа: filter predicates and blocking predicates
Security policy - Коллекция предикатов для нескольких таблиц

Теперь создадим 2-х пользователей и дадим им права на чтение нашей таблицы

create user User1 without login;
create user User2 without login;
go
grant select on dbo.TestRLS to User1;
grant select on dbo.TestRLS to User2;
go

Создадим функцию (Predicate function), которая будет определять права

create function dbo.fn_securitypredicate(@user_name as sysname)
    returns table
with schemabinding
as
    return select 1 as fn_securitypredicate_result 
      where @user_name = user_name();
go

И теперь по имени пользователя будем определять, какие данные нам доступны:

create security policy rls_plicy
add filter predicate dbo.fn_securitypredicate(UserName) 
on dbo.TestRLS
with (state = on);
go

Посмотрим, какие данные видны в контексте каждого пользователя:

--Текущий пользователь
select user_name() as [user_name];
go
select * from dbo.TestRLS;
go

--Перевлючаем в контекст пользователя User1
execute as user = 'User1';
go
select user_name() as [user_name];
go
select * from dbo.TestRLS;
go
revert;
go

--Перевлючаем в контекст пользователя User2
execute as user = 'User2';
go
select user_name() as [user_name];
go
select * from dbo.TestRLS;
go
revert;
go

Всё достаточно легко и просто!