T-SQL.RU


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


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

читать дальше...
добавлено: 16 апр 16 просмотры: 181, комментарии: 0



Compress и Decompress

Рассмотрим ещё одну новую возможность, которая появилась в 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.na...
читать дальше...
добавлено: 29 дек 15 просмотры: 863, комментарии: 1



SQL Server 2016: Row-Level Security

Одна из интересных новинок в 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-х пользователей и дадим им права на чт...

читать дальше...
добавлено: 22 ноя 15 просмотры: 751, комментарии: 0



Live Query Statistics

В родном клиенте 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


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

читать дальше...
добавлено: 20 ноя 15 просмотры: 563, комментарии: 0



DROP IF EXISTS

С выходом 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

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

 

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

читать дальше...
добавлено: 19 ноя 15 просмотры: 550, комментарии: 0



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

С выходом каждой новой версии 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', replic...
читать дальше...
добавлено: 02 ноя 15 просмотры: 669, комментарии: 0



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

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

"Как узнать статус служб 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):

... читать дальше...
добавлено: 14 окт 15 просмотры: 578, комментарии: 0





ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table [XXX] a

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

Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table [XXX] allows values that are not allowed by check constraints or partition function on target table [YYY].


Сейчас я покажу одну из самых частых причин подобных ошибок!

Для начала создадим тестовую функцию и схему секционирования, в которой будет всего 5 "отрезков" с данными, которые мы будем хранить:

create partition function pf_dt ( datetime )
as range left for values ( '20140101', '20140102', '20140103' );
go

create partition scheme ps_dt 
as partition pf_dt all to ([primary]);
go

Т.к. у нас ранжирование LEFT, то схематически наше секционирование можно представить в виде рисунка ниже:

Точки, по которым идёт разбиение на секции буду принадлежать левому отрезку (опять же, т.к. у нас range left), кроме того, все строки, которым соответствуют значения NULL столбца секционирования, располагаются в самой левой секции, кроме случая, когда задано пустое граничное значение и параметр RIGHT. В данном случае самая левая секция является пустой, и в нее помещаются значения NULL.

Теперь создадим тестовую таблицу на нашей схеме секционирования:

create table tbl_test( id int, dt datetime, val varchar(50) ) on ps_dt(dt);
go

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

читать дальше...
добавлено: 04 апр 15 просмотры: 697, комментарии: 1



Индексы в табличных переменных

Сегодня я немного расскажу о том, как создать индексы на табличных переменных (это которые @table) в SQL Server 2014 и в ранних версиях. При этом я не буду останавливаться на том, зачем нужны индексы и не буду сравнивать табличные переменные с временными таблицами, т.к. на эту тему достаточно статей, например: http://www.sql-server-performance.com/2007/temp-tables-vs-variables/ или http://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/. При этом сразу оговорюсь, что одним из недостатков табличных переменных было именно отсутствие возможности явно создать индекс. Но давайте по порядку.

В SQL Server 2012 и ниже мы действительно не можем явно создать индекс на нужном нам поле (полях), но существует несколько приёмов с созданием индексов через ограничения (PRIMARY KEY и UNIQUE). Пример ниже:

declare @table table ( id int primary key
                     , a  varchar(10)
                     , b  datetime
                     , c  money
                     , unique (a,c)
                     );

select * from @table where id > 0;

select a, c from @table where a is not null;

И всё бы хорошо, но как создать неуникальный индекс? И вот теперь, начиная с SQL Server 2014 это стало возможно и в синтаксис добавили создание индекса на табличных переменных.

declare @table table ( id int primary key 
                     , a  varchar(10)
                     , b  datetime  
                     , c  money
  ...
читать дальше...
добавлено: 19 мар 15 просмотры: 932, комментарии: 1