If your running SQL Server 2008 Enterprise
and have the need to restrict certain users or groups from executing runaway
queries, then the Resource Governor might be what you are looking for. By
creating the right mix of workload groups and resource pools, a DBA can create
an environment that is more predictable and stable, while maintaining the
availability of resources for all users.
In this example, I'll create a Workload
Group that only Windows logins will use. I used the distinction of Windows
logins vs SQL logins because all the produciton applications hitting this
server use SQL authentication while any users (aside from the sys admins) doing
ad hoc analytics use Windows authentication. Many times when users are doing ad
hoc analytics, they are digging through the data and looking for trends, which
lends itself to someone using "Select *". That kind of query on very wide and deep
tables can easily lock a table needed for the production applications. This is where
the resource governor can help. By limiting the amount of resources a query or
a session can have, a DBA can make other sessions on the server execute more
predicitbly.
There is quite a bit of setup involved with
the Resource Governor and there are many different ways to do it, so that steps
I take below will obviously have to be tailored to your enviornment.
There are 3 main parts to setting up the
Resource Governor:
1.
Resource Pool: A pool of resources
that Workload Groups will access.
2.
Workload Group: Group that logins belong to based on the Classifier Function.
3.
Classifier Function: Function that
assigns logins to Workload Groups.
First , let's do the setup for the
classifier. Since we are classififing on login type (SQL or Windows) our
Classifier Function should be quite simple, but the Classifier Function
has to be schema bound, which means we can't reference
sys.server_principals. So since we can't use the system tables in the
Classifier Function, we need to create our own table in the MASTER database to
keep SQL login information.
CREATE TABLE [SQLLoginsList]
(SQL_LoginName sysname)
Now we need to populate the table with all
the existing SQL Logins.
Insert Into SQLLoginsList
Select Name From sys.Server_Principals Where [Type] = N'S'
Once the table is in place, a server
trigger needs to be created to insert into the SQLLoginsList table any time a
new SQL Login is created.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [srv_trg_SQLLoginList] ON
ALL SERVER
WITH EXECUTE AS 'sa'
FOR CREATE_LOGIN
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
IF (SELECT
EVENTDATA().value('(//LoginType)[1]', 'VARCHAR(255)')) = 'SQL Login'
BEGIN
IF NOT EXISTS ( SELECT *
FROM SQLLoginsList
WHERE SQL_LoginName =
EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)') )
BEGIN
INSERT
dbo.SQLLoginsList(SQL_LoginName)
SELECT
EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)')
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [srv_trg_SQLLoginList] ON
ALL SERVER
GO
Now that we have a place to query SQL
Logins and a mechanism to catch newly created ones, we can start creating our
Resource Pool and Workload Group.
When creating a Resource Pool, there are 4
properties that can be set.
1.
MIN_CPU_PERCENT: The minimum CPU
bandwidth for the pool when there is CPU contention.
2.
MAX_CPU_PERCENT: The max CPU
bandwidth for the pool when there is CPU contention.
3.
MIN_MEMORY_PERCENT: minimum amount of memory reserved for this pool.
4.
MAX_MEMORY_PERCENT: maximum amount
of memory requests in this pool can consume.
The "when there is CPU
contention" because CPU contention is a soft limit, meaning when there
is no CPU contention the pool will consume as much CPU as it needs.
CREATE RESOURCE POOL pAdhocProcessing
WITH
(MIN_CPU_PERCENT = 0 --no min cpu bandwidth
for pool WHEN THERE IS CONTENTION
,MAX_CPU_PERCENT = 25 --max cpu bandwidth for
the pool WHEN THERE IS CONTENTION
,MIN_MEMORY_PERCENT = 0 --no memory
reservation for this pool
,MAX_MEMORY_PERCENT
= 25 --max server memory this pool can take
)
Next comes the creation of the Workload
Group. By default there is already a group called "default" that all
users are placed into if they aren't placed into another Workload Group by the
Classifier Function. Think of the Workload Groups as buckets of users that can
dip water (resources) from an assigned "pool" (Resource Pool). Each
Workload Group can only have one Resource Pool, but a Resouce Pool can have
many Workload Groups assigned to it.
When creating a Workload Group, there are 6
properties that can be set.
1.
IMPORTANCE: Gives a precedence or
"importance" to CPU resources available in the Resource Pool.
2.
REQUEST_MAX_MEMORY_GRNT_PERCENT:
Amount of memory one request can take from Resource Pool.
3.
REQUEST_MAX_CPU_TIME_SEC: Amount of
total CPU time a request can have.
4.
REQUEST_MEMORY_GRANT_TIMEOUT_SEC:
Maximum amount of time a request will wait for resource to free up.
5.
MAX_DOP: Max Degree of Parallelism a
query can execute with. This option takes precedence over any query hint or
server setting.
6.
GROUP_MAX_REQUESTS: Amount of
requests this group can simultaneously issue.
The following script will create the
Workload Group and assign it to a Resource Pool
CREATE WORKLOAD GROUP gAdhocProcessing
WITH
(IMPORTANCE = LOW --Low importance meaning the scheduler will execute
medium (default) session 3 times more often
,REQUEST_MAX_MEMORY_GRANT_PERCENT = 25 --one person can only take 25
percent of the memory afforded to the pool
,REQUEST_MAX_CPU_TIME_SEC = 60 --can only take a TOTAL of 60 seconds of
CPU time (this is not total query time)
,REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60 --max amount of time a query will
wait for resource to become available
,MAX_DOP = 1 --overrides all other DOP hints and server settings
,GROUP_MAX_REQUESTS = 0 --unlimited requests (default) in this group
)
Using pAdhocProcessing
At this point, we should see the following
under Management --> Resource Governor
The Resource Governor will stay in "Reconfigure
Pending" status until we create the Classifier Function and issue a
Reconfigure Command to turn the Resource Governor on.
Finally, we'll create our Classifier
Function that will place all incoming requests into either the gAdhocProcessing
group or the default group. This function looks a lot like any other scalar
function with the excepting that is had to be created using "With
SchemaBinding". Based on the login type and whether the login is a
sysadmin, the function will return what Resource Group the request should fall
into.
CREATE FUNCTION fnLoginClassifier()
RETURNS sysname
WITH SCHEMABINDING
as
Begin
Declare @Login sysname
set
@Login = SUser_Name()
if
(IS_SRVROLEMEMBER('sysadmin') = 1)
--Server Admins
Return N'default'
else if (Select Count(*) from dbo.[SQLLoginsList] Where SQL_LoginName =
@Login) > 0 --SQL Logins
Return N'default'
else if @Login like '<domain>%' --Windows Logins
Return N'gAdhocProcessing'
--If nothing is returned, put it in the default group just in case
Return N'default'
End
GO
Now that the Classifier Function has been
created we have to tell the Resource Governor to use that function as it's
classifier.
ALTER RESOURCE GOVERNOR with
(CLASSIFIER_FUNCTION = dbo.fnLoginClassifier)
At this point, if we right click the Resource Governor in the Object Explorer and
choose properties we will should see the following.
The Resource Pool pAdhocProcessing has been
created and the Workload Group gAdhocProcessing has been assigned to it. Also,
the fnLoginClassifier function shows as the Classifier function name and the
message at the top signals us that the Resource Governor has pending changes
and that we need to issue a Reconfigure command to enable the governor.
Now all that's left to do is turn it on. We
can do that by issuing the following command.
ALTER RESOURCE GOVERNOR RECONFIGURE
Once the Resource Governor is turned on, we
can monitor the amount of sessions each login has open and what Workload Group
they have been assigned to.
SELECT
WG.name
,COUNT(*)
,ES.login_name
,es.program_name
FROM
sys.dm_exec_sessions ES
INNER JOIN sys.dm_resource_governor_workload_groups WG
ON ES.group_id = WG.group_id
GROUP BY
WG.name,es.login_name,es.program_name
ORDER BY login_name
If for some reason the Resource Governor is
not working as it should, issue the following command to disable it.
ALTER RESOURCE GOVERNOR DISABLE
Since it is possible on a busy system to
lock yourself out by configuring the Resource Governor incorrectly, you may
have to sign in with the Dedicated Administrator Connection (DAC). That
connection uses the internal Workload Group and cannot have it's resources
altered. Once you have established a connection using the DAC, you will have
the ability to either disable the Resouce Governor or remove the Classifier
Function. If you remove the Classifier Function, all incoming connections will
fall to the default group. To remove the Classifier Function, issue the
following commands.
ALTER RESOURCE GOVERNOR with
(CLASSIFIER_FUNCTION = NULL)
ALTER RESOURCE GOVERNOR RECONFIGURE
