Tag Archives: SQL Server

Статистические функции STDEV, STDEVP, VAR, VARP

Среди функций агрегации в SQL Server присутствуют STDEV, STDEVP, VAR и VARP, которые могут вызвать вопросы, что это такое, как и когда их применять. Для начала я приведу формулы, по которым идем расчет указанных функций. Функции взяты из математической статистики.

STDEV возвращает статистическое среднеквадратическое отклонение всех значений в указанном выражении.

 — дисперсия;  — i-й элемент выборки;  — объём выборки;  — среднее арифметическое выборки:

STDEVP возвращает статистическое среднеквадратичное отклонение совокупности всех значений в указанном выражении.

VAR возвращает статистическую дисперсию всех значений в указанном выражении. Значение равно (квадрат значения, возвращаемого функцией STDEV).

VARP Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении (квадрат значения, возвращаемого функцией STDEVP).

Т.к. функции VAR и VARP фактически дублируют STDEV и STDEVP, то сосредоточимся только на последних. Перепишем формулы их вычисления в чуть более понятный на мой взгляд вид.

Stdevp = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/n )

Stdev = sqrt( ((x1-xmean)^2 + (x2-xmean)^2 + … + (xn-xmean)^2)/(n-1) )

По сути эти функции показывают насколько в среднем значения отличаются от арифметического среднего. Теперь давайте рассмотрим небольшой пример их применения. Возьмем простую таблицу со столбцом типа int, 3мя записями и рассчитаем для них значения функций.

USE [tempdb];
GO

IF OBJECT_ID('tempdb.dbo.test', 'U') IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (3);
GO

SELECT
    AVG([d]) AS [avg]
    ,STDEV([d]) AS [stdev]
    ,STDEVP([d]) AS [stdevp]
FROM [dbo].[test];
GO


Значение STDEV на небольших выборках, как мы видим может быть более точным, нежели STDEVP (по сути исключается из расчетов одно значение, которое либо само является средним, либо близко к среднему). Но на больших выборках они могут быть очень близки.

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

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

USE [tempdb];
GO

IF OBJECT_ID(‘tempdb.dbo.test’, ‘U’) IS NOT NULL
    DROP TABLE [dbo].[test];
GO

CREATE TABLE [dbo].[test] (
    [d] [int] NOT NULL
);
GO

INSERT INTO [dbo].[test] ([d]) VALUES (1), (2), (1), (1), (0), (1), (1), (2), (1), (1), (15);
GO

SELECT
    [d]
    ,CASE WHEN ABS([d] - AVG([d]) OVER() ) > 3 * STDEV([d]) OVER() THEN 1 ELSE 0 END IsLargeThan3Sigma
FROM [dbo].[test];
GO

Russian SQL Server User Group возобновляет свою работу

После долгого перерыва Russian SQL Server User Group возобновляет свою работу. Очередные встречи пройдут 20 декабря и 24 января в новом месте в Microsoft Technology Center м. Белорусская. Это практически в центре Москвы и добраться будет значительно проще. Описание встреч вы можете посмотреть у меня на сайте в разделе Встречи.

Предварительная регистрация на встречи обязательна. Зарегистрироваться можно по адресу http://ineta.ru/sqlrus.

Использование CONTEXT_INFO

SQL Server обладает широким набором интересных функций. Одной из них является возможность реализовать передачу определенной информации в пределах одной сессии. Когда это может быть нужно? Например, с одной таблицей в базе данных работает несколько приложений и каждое вводит или изменяет данные с помощью своей хранимой процедуры, а триггер в таблице на изменение данных должен это учитывать и вести себя по-разному для всех приложений. В этом случае как раз можно использовать следующий сценарий: хранимая процедура сохраняет определенное значение в переменной сессии, а триггер считывает это значение и выполняет нужной действие в зависимости от контекста. Также эту технику можно применять при использовании вложенных триггеров (nested triggers).

Переменная сессии CONTEXT_INFO позволяет сохранять данные переменной длины размером до 128 байт. Это немного, но для большинства задач должно хватить. Чтобы установить значение переменной сессии нужно воспользоваться командой SET CONTEXT_INFO, а чтобы получить текущее значение – функцией CONTEXT_INFO(). Ну и сразу приведу небольшой пример.

DECLARE @context_info varbinary(128)
SET @context_info = CAST('MyApplicationID1' AS varbinary(128))
SET CONTEXT_INFO @context_info
GO

SELECT CONTEXT_INFO()
SELECT CAST(CONTEXT_INFO() AS varchar(128))
GO

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

Также переменную сессии можно посмотреть в следующих системных представлениях в столбце context_info.

  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.sysprocesses

Также можно использовать технику, когда в CONTEXT_INFO мы будем хранить не одно, а несколько значений, но в разных местах. Например, мы договариваемся, что в позиции с 1 по 20 мы храним идентификатор процесса, а 21 по 28 – дату и время. Тогда процесс формирования нашей строки будет выглядеть примерно следующим образом.

DECLARE @process_id char(20) = 'MyProcessID2'
DECLARE @date_time datetime = GETDATE()
DECLARE @context_info varbinary(128)

SET @context_info = CAST(@process_id AS binary(20)) + CAST(@date_time AS binary(8))

SET CONTEXT_INFO @context_info

SELECT CAST(SUBSTRING(CONTEXT_INFO(), 1, 20) AS char(20)) AS process_id, CAST(SUBSTRING(CONTEXT_INFO(), 21, 8) AS datetime) AS date_time

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

DECLARE @context_info varbinary(128)

SET @context_info = CAST('Value before transaction' AS varbinary(128))
SET CONTEXT_INFO @context_info
SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value before transaction]

BEGIN TRAN

	SET @context_info = CAST('Value inside transaction' AS varbinary(128))
	SET CONTEXT_INFO @context_info
	SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value inside transaction]

ROLLBACK

SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value after transaction]

Финальным результатом вернется значение, которое мы установили внутри транзакции, несмотря на то, что транзакцию мы откатили. Это нужно учитывать или можно использовать.

Что такое Central Management Server

Central Management Server (CMS) является хранилищем организованного списка управляемых серверов и служит администратору баз данных хорошим подспорьем при управлении несколькими серверами. В качестве CMS подойдет SQL Server версии 2008 и выше и любой редакции, в т.ч. Express. Для того чтобы создать его, нужно в SQL Server Management Studio в окне Registered Servers щелкнуть правой клавишей мыши и выбрать пункт Register Central Management Server.

В появившемся окне нужно всего-лишь вбить имя сервера и сохранить.

Все, теперь CMS создан и готов к использованию.

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

Управление списком серверов в Central Management Server

Теперь мы можем добавлять в CMS наши сервера. Этот процесс такой же, как и добавление серверов в окне Registered Servers. Напоминаю, что в качестве CMS должна использоваться версия не ниже SQL Server 2008, а вот в список серверов для администрирования вы можете добавлять даже 2000 и 2005 версии и, насколько мне известно, хотя я сам лично не проверял, даже версию 7.0.

Например так выглядит добавление группы:

А так добавление сервера:

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

Недостатки Central Management Server

На текущий момент существует два недостатка, которые необходимо учитывать при внедрении CMS.

Central Management Server поддерживает только Windows аутентификацию. Соответственно, если в вашей среде есть удаленные SQL сервера, доступ к которым вы имеете только через SQL аутентификацию или SQL Azure сервера, вы не сможете добавить их в общий список. Соответственно, если у вас несколько изолированных доменов, в каждом из которых вас есть экземпляры SQL Server, то ваш единственный выход – использовать несколько CMS, хотя это и не очень удобно.

Также нельзя добавить в список серверов сам Central Management Server. Поэтому обычно Central Management Server развертывают на отдельном экземпляре.

Способы применения Central Management Server

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

Выполнение запроса одновременно на группе серверов

С помощью CMS становится очень просто выполнить определенную команду одновременно сразу на нескольких серверах. Для этого вы просто щелкаете правой клавишей мыши на нужной вам группе серверов и выбираете пункт New Query.

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

Выполнение административных действий со службами

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

CMS как хранилище политик для Policy Based Management

Central Management Server играет ключевую роль, если вы применяете Policy Based Management для контроля и управления несколькими экземплярами SQL Server. Его удобно использовать в качестве центрального хранилища для ваших политик. Подробнее о Policy Based Management вы можете прочитать здесь: Administer Servers by Using Policy-Based Management.

Центральное хранилище списка экземпляров SQL Server

Об этом я уже в кратце говорил, но выделю еще раз. Если в вашей организации несколько человек занимаются администрированием, то очень полезно иметь центральное хранилище списка экземпляров SQL Server. На самом CMS весь список серверов храниться в БД msdb в следующих системных таблицах:

dbo.sysmanagement_shared_registered_servers_internal
dbo.sysmanagement_shared_server_groups_internal

Также имеются 2 системных представления:

dbo.sysmanagement_shared_registered_servers
dbo.sysmanagement_shared_server_groups

Для выдачи прав к CMS существует 2 роли в БД msdb: ServerGroupReaderRole и ServerGroupAdministratorRole. Первая дает доступ на чтение к вышеуказанным представлениям со списком серверов, а вторая дает право на запуск следующих системных процедур, которые служат для управления списком серверов:

dbo.sp_sysmanagement_add_shared_registered_server
dbo.sp_sysmanagement_add_shared_server_group
dbo.sp_sysmanagement_delete_shared_registered_server
dbo.sp_sysmanagement_delete_shared_server_group
dbo.sp_sysmanagement_move_shared_registered_server
dbo.sp_sysmanagement_move_shared_server_group
dbo.sp_sysmanagement_rename_shared_registered_server
dbo.sp_sysmanagement_rename_shared_server_group
dbo.sp_sysmanagement_update_shared_registered_server
dbo.sp_sysmanagement_update_shared_server_group
dbo.sp_sysmanagement_verify_shared_server_type

Соответственно, если вы хотите предоставить членам вашей команды доступ к вашему CMS, вам нужно включать их в соответствующие группы.

Функция IS_MEMBER

Очень часто требуется написать хранимую процедуру или функцию, которая будет выдавать разные результаты для разных групп пользователей. Например, директор предприятия хочет видеть информацию по всем сотрудникам его фирмы, а начальники отделов только по своим сотрудникам. Реализовать это можно, например, включив пользователей в разные группы AD, а в запросе проверять вхождение и в зависимости от результата выдавать разные данные. В SQL Server существует функция IS_MEMBER способная осуществлять вышеуказанную проверку, причем она работает даже с вложенными группами. Функция принимает единственный параметр – название AD группы и возвращает 1, если текущий пользователь входит в указанную группу; 0 – если не входит и NULL, если такой группы в AD не найдено. Естественно, пользователи должны использовать Windows аутентификацию, чтобы воспользоваться этим функционалом. И привожу пример, как может выглядеть в хранимой процедуре вывод результатов в зависимости от вхождения в группу.

IF IS_MEMBER('TEST\CEO')
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
ELSE IF IS_MEMBER('TEST\DepartmentHead')
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
	WHERE [Department] = (SELECT [Department] FROM [HumanResources].[Employee] WHERE [LoginName] = SUSER_SNAME())
ELSE
	SELECT [FirstName], [LastName], [Department], [Salary]
	FROM [HumanResources].[Employee]
	WHERE [LoginName] = SUSER_SNAME()

Удаление из процедурного кэша определенных планов запросов

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

Вариант первый, можно изменить хранимую процедуру саму на себя. Например в Object Explorer выбрать хранимую процедуру, сделать Script Store Procedure as -> ALTER to -> New Query Window и выполнить то, что получилось.

Script Stored Procedure

Script Stored Procedure

Это приведет к тому, что при следующем запуске хранимой процедуры произойдет ее перекомпиляция. Метод очень простой и быстрый, но имеет один недостаток: это ведет к изменению поля modify_date в системном представлении sys.objects, хотя по сути дела никаких изменений мы не вносили.

Вариант второй, более сложный, сделать запрос к системному преставлению sys.dm_exec_cached_plans, получить нужный хэндл (или хэндлы) неоптимальных планов и удалить их из кэша с помощью DBCC FREEPROCCACHE. Например, в своей тестовой базе я сделаю такой запрос и получу значение поля plan_handle.

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%uspGetBillOfMaterials%'
	AND t.dbid = DB_ID('AdventureWorks2008R2')
Результаты запроса

Результаты запроса

Проверяем, что в поле text у нас действительно нужная хранимая процедура, и после этого запускаем DBCC FREEPROCCACHE. В качестве параметра передаем значение поля plan_handle.

DBCC FREEPROCCACHE(0x050005005C31690140016C9A000000000000000000000000)

Все, ненужный нам план из кэша удален и при следующем запуске процедуры произойдет перекомпиляция.

Как узнать процент выполнения запроса

Начиная с SQL Server 2005 для команд BACKUP, RESTORE и некоторых команд DBCC (например, DBCC CHECKDB) появилась возможность узнать процент выполнения запроса в процессе выполнения команды. При запуске команд BACKUP и RESTORE можно указать параметр STATS, но этим можно пользоваться только в интерактивном режиме. А у команд DBCC аналогичного параметра вообще нет. Но очень часто бывает полезным узнать процент выполнения данных команд и приблизительное время окончания. На помощь нам приходит системное представление sys.dm_exec_requests. Для примера я запущу резервное копирование, восстановление и проверку целостности на тестовом сервере (каждую из этих команд я запускаю в отдельном окне):

DBCC CHECKDB('TEST');
BACKUP DATABASE [TEST2] TO DISK = 'C:\TEMP\TEST2.bak';
RESTORE DATABASE [TEST3] FROM DISK = 'C:\TEMP\TEST3.bak';

И давайте следующим запросом посмотрим процесс выполнения этих команд:

SELECT
	[command]
	,[start_time]
	,[percent_complete]
	,[estimated_completion_time] / 60000. AS [estimated_completion_time_min]
FROM sys.dm_exec_requests
WHERE [command] = 'BACKUP DATABASE'
	OR [command] = 'RESTORE DATABASE'
	OR [command] LIKE '%DBCC%'

Результаты получаются примерно такие:

Результаты запроса

Результаты запроса

 

Запуск T-SQL команды на нескольких экземплярах без использования CMS

С появлением SQL Server 2008 R2 стало возможным использовать Central Management Servers для запуска T-SQL команды сразу на нескольких экземплярах. Но, однако, такой трюк можно проделать и без установки и настройки CMS, если вы используете Registered Servers в SQL Server Management Studio. Для этого в SSMS нужно кликнуть правой клавишей мыши на нужной группе серверов или же на всей категории Local Servers Group и выбрать пункт New Query.

New Query on Local Server Group

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

Multiply Servers Query Result

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

Query Windows Connection

Таким образом только с помощью SQL Server Management Studio можно с легкостью выполнить T-SQL команду сразу на нескольких серверах, что порой так необходимо при администрировании.