SQL-Ex blog

Чтобы решить эту проблему, мы собираемся использовать Resource Governor.

Погодите. Вернемся назад.

Когда Resource Governor впервые появился, он не получил широкого распространения. Он был доступен только в Enterprise Edition, и его главной работой было замедлять запросы. Это довольно прискорбно, т.к. ко мне не приходило много людей со словами: "Эй, Брент, не можешь сделать мои запросы медленнее?". Им не нужно было нанимать меня для этого - они имели свой собственный отдел бизнес-аналитики, который заботился об этом.

Мы собираемся использовать только часть функциональности Resource Governor:

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


2. Создадим функцию классификатора, чтобы при их авторизации мы могли разместить их в разных пулах.


3. Установить ограничения на ЦП для каждого пула рабочей нагрузки.


4. Используем динамические представления для отчета Resource Governor, чтобы сделать запрос о тех, кто напрягает наши процессоры.

Конфигурируем Resource Governor, чтобы разместить людей по группам

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

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* Создать пулы для групп пользователей, которых вы хотите отслеживать: */

CREATE RESOURCE POOL pool_WebSite;

CREATE RESOURCE POOL pool_Accounting;

CREATE RESOURCE POOL pool_ReportingUsers;

GO

CREATE WORKLOAD GROUP wg_WebSite;

CREATE WORKLOAD GROUP wg_Accounting;

CREATE WORKLOAD GROUP wg_ReportingUsers;

GO

CREATE WORKLOAD GROUP wg_WebSite USING [pool_WebSite];

CREATE WORKLOAD GROUP wg_Accounting USING [pool_Accounting];

CREATE WORKLOAD GROUP wg_ReportingUsers USING [pool_ReportingUsers];

GO

/* Для этого примера я собираюсь создать 

несколько логинов SQL, которые я буду классифицировать

в группах. Вам не нужно этого делать, поскольку ваш сервер

их уже имеет. */

CREATE LOGIN [WebSiteApp] WITH PASSWORD=N'Passw0rd!', 

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [WebSiteApp]

GO

CREATE LOGIN [AccountingApp] WITH PASSWORD=N'Passw0rd!', 

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [AccountingApp]

GO

CREATE LOGIN [IPFreely] WITH PASSWORD=N'Passw0rd!', 

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [IPFreely]

GO

/* При входе в систему эта функция запустится и поместит людей

в разные группы в зависимости от того, кем они являются. */

CREATE FUNCTION [dbo].[ResourceGovernorClassifier]() 

RETURNS sysname 

WITH SCHEMABINDING

AS

BEGIN

	-- Определяем возвращаемую функцией переменную типа sysname

	DECLARE @grp_name AS sysname;

	SELECT @grp_name = CASE SUSER_NAME()

		WHEN 'WebSiteApp' THEN 'wg_WebSite'

		WHEN 'AccountingApp' THEN 'wg_Accounting'

		WHEN 'IPFreely' THEN 'wg_ReportingUsers'

		ELSE 'default' END;

	RETURN @grp_name;

END

GO

/* Сообщаем Resource Governor, какую функцию использовать: */

ALTER RESOURCE GOVERNOR 

WITH ( CLASSIFIER_FUNCTION = dbo.[ResourceGovernorClassifier])

GO

/* Зафиксируйте изменения

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

*/

Вы заметили, что я закомментировал RECONFIGURE в конце. Resource Governor может быть довольно опасным: вы можете загнать себя в ситуацию, когда возникают проблемы с выполнением запросов из-за ошибок в функции классификатора. Сначала выполняйте этот последний RECONFIGURE только в среде разработки и убедитесь, что вы получаете ожидаемые результаты.

Измеряем, какие группы пользователей больше всего используют ЦП

Чтобы сделать это, воспользуемся DMV sys.dm_resource_governor_resource_pools:

SELECT *

FROM sys.dm_resource_governor_resource_pools;

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



Когда вы начнете изучать эти данные, то можете обнаружить, что ваш первый проход по созданию пулов и групп завершился не очень детализированными данными. Возможно, 80% времени ЦП сжигается единственной группой - что не очень показательно. Тут вы захотите продолжить создание все более мелких групп, пока не сможете точно выяснить, кто или какое приложение потребляет большую часть мощности ЦП.

Содержимое этого DMV сбрасывается после рестарта SQL Server. У вас возникнет соблазн накапливать эти данные, записывая их каждый день в таблицу, и я думаю, что это плохая идея. Каждый раз, когда запускается экземпляр SQL Server, думайте об этом как о новом дне. Люди, пишущие ужасные запросы, возможно, осознали свои ошибки и перевернули новую страницу, прочитали хорошую книгу Ицика Бен Гана по T-SQL и с тех пор стали писать волшебно настроенные запросы.

Хотя, наверное, нет.