T-SQL.RU


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 просмотры: 485, комментарии: 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 просмотры: 608, комментарии: 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 просмотры: 482, комментарии: 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 просмотры: 443, комментарии: 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 просмотры: 563, комментарии: 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 просмотры: 490, комментарии: 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 просмотры: 561, комментарии: 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 просмотры: 821, комментарии: 1



Секционированные представления и магические 64 таблицы

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

Ситуация воспроизводится на разных версиях SQL Server, в том числе и на версии SQL Server 2014.

Но прежде нам необходимо создать несколько объектов (100 таблиц и 1 представление).

use tempdb;
go
set nocount on;
go
------------
--В динамике создаём 100 таблиц с чеками, в каждую таблицу вставляю по 100 записей
------------
declare @cmd varchar(4096)
      , @i int = 1
      , @dt datetime = '20140101';

while @i <= 100
begin
set @cmd = ' create table dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' ( id uniqueidentifier, dt datetime '
         + ' , constraint ck_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' check ( dt >= ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' and dt < ' + quotename ( convert( varchar, @dt + 1, 126 ),'''' ) + ' )'
         + ' , constraint pk_' + right( '00' + cast( @i as varchar ), 3 )
         + ' primary key( dt, id )' 
         + ' );';
exec ( @cmd );
 
set @cmd = ' insert into dbo.tbl_' + right( '00' + cast( @i as varchar ), 3 ) 
         + ' select top 100 newid(), dateadd( mi, t1.number, ' + quotename ( convert( varchar, @dt, 126 ),'''' ) 
         + ' ) from master....
читать дальше...
добавлено: 19 фев 15 просмотры: 1167, комментарии: 1