�ݺ�ߣ

RESOURCE GOVERNOR SQL SERVER 2008 Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified [email_address] 050-4477117

WHY A RESOURCE GOVERNOR? Every DBA wants more control over resource usage on their SQL Server instances. Pressure comes from: IT : reducing data center footprint, hosting more instances on less hardware Other departments : sales, marketing, finance who want better performance Customers : my web report timed out Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT IS THE RESOURCE GOVERNOR? A technology that enables you to manage SQL Server workload and resources by specifying limits on resource consumption … or… The new way to prevent your peons and pointy- haired bosses from bringing down your server Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

BEFORE RESOURCE GOVERNOR… What we did before to control resource usage was inflexible and largely reactive: Kill SPIDs with “runaway” queries utilize SET QUERY_GOVERNOR_COST_LIMIT per query (or per instance with sp_configure) use separate instances with affinity to separate workloads juggle scheduled jobs to avoid peak activity times schedule creation / destruction of procedures or even changing passwords to block certain groups during peak load times Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT ARE THE GOALS? To classify and prioritize workloads To make resource usage more balanced and predictable To help prevent, or at least to minimize, the “run away query” To monitor and adapt the above tactics to further smooth resource usage Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

THREE COMPONENTS OF RESOURCE GOV. Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Classifier. There are internal rules that classify incoming requests and route them to a workload group. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

HOW DO THEY WORK TOGETHER? Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT ARE THE BASIC STEPS? Create resource pools Create workload groups Create classifier function Enable resource governor Monitor and adapt Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT IS A RESOURCE POOL? Provides a “slice” of a SQL Server instance’s resources (min/max CPU, memory, or both) Pools can overlap or be isolated % of resources based on amount “left over” – not being used by internal processes Allows for aggregate monitoring of all requests utilizing the pool RESOURCE POOL SYNTAX CREATE RESOURCE POOL pool_name [ WITH ( [ MIN_CPU_PERCENT =value ][[,] MAX_CPU_PERCENT =value ][[,] MIN_MEMORY_PERCENT =value ][[,] MAX_MEMORY_PERCENT =value ] )]; Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT IS A WORKLOAD GROUP? This acts as a bucket for requests of a similar type (as defined by the “classifier function”) and to place constraints on those requests Allows for aggregate monitoring of all requests from all the members of the group WORKLOAD GROUP SYNTAX CREATE WORKLOAD GROUP group_name [ WITH ([ IMPORTANCE ={ LOW|MEDIUM|HIGH } ][[,] REQUEST_MAX_MEMORY_GRANT_PERCENT =value][[,] REQUEST_MAX_CPU_TIME_SEC =value][[,] REQUEST_MEMORY_GRANT_TIMEOUT_SEC =value][[,] MAX_DOP =value][[,] GROUP_MAX_REQUESTS =value] )][ USING { pool_name| "default"} ]; Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT IS A CLASSIFIER FUNCTION? User-defined scalar function that allows you to customize how incoming requests are routed Function returns a workload group name, which tells Resource Governor which pool to associate the request with Needs to be very efficient Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

CLASSIFICATION PROCESS In the context of Resource Governor, the login process for a session consists of the following steps: Login authentication LOGON trigger execution Classification Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT ARE SOME CLASSIFICATION EXAMPLES? You can segregate incoming requests using a whole slew of criteria: LOGINPROPERTY (DefaultLanguage, DefaultDatabase) ORIGINAL_DB_NAME() HOST_NAME(), APP_NAME() * CONNECTIONPROPERTY() –IP address, protocol, etc. [S]USER_[S]NAME() IS_SRVROLEMEMBER(), IS_MEMBER() Also intrinsic functions, DATEPART, GETDATE(), etc. .Examples… Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

CLASSIFIER FUNCTION EXAMPLE #1 .Give Aaron priority, and non-sa low priority CREATE FUNCTION dbo.Classifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN RETURN (SELECT CASE SUSER_SNAME() WHEN ‘sa’ THEN ‘HighPriorityGroup’ ELSE ‘LowPriorityGroup’ END ); END GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

CLASSIFIER FUNCTION EXAMPLE #2 .Give ad hoc Management Studio queries low priority during business hours, and high priority otherwise CREATE FUNCTION dbo.Classifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN RETURN (SELECT CASE WHEN APP_NAME() LIKE ‘%Management Studio%’ AND DATEPART(HOUR, GETDATE()) BETWEEN 9 AND 17 THEN ‘LowPriorityGroup’ ELSE ‘HighPriorityGroup’ END ); END GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

CLASSIFIER FUNCTION EXAMPLE #3 .Get the DallasAtlanta office back for that April Fool’s joke they played on the DBA CREATE FUNCTION dbo.Classifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN RETURN (SELECT CASE WHEN CONNECTIONPROPERTY(‘Local_Net_Address’) LIKE ‘192.168.2.%’ THEN ‘Group_With_Max_CPU_1_Percent’ ELSE ‘HighPriorityGroup’ END ); END GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

USER-DEFINED FUNCTION CHARACTERISTICS : The user-defined function is evaluated for every new session, even when connection pooling is enabled. The user-defined function gives workload group context for the session. After group membership is determined, the session is bound to the workload group for the lifetime of the session. If the user-defined function returns NULL, default, or the name of non-existent group . the session is given the default workload group context. The session is also given the default context if the function fails for any reason. Only one user-defined function can be designated as a classifier at a time. The classifier user-defined function cannot be dropped or altered unless its classifier status is removed. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

THE DEFAULT WORKLOAD GROUP Requests are classified into the default group when the following conditions exist: There are no criteria to classify a request. There is an attempt to classify the request into a non-existent group. There is a general classification failure. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

HOW DO I MONITOR? New Perfmonobjects with lots of counters: SQLServer: Resource Pool Stats SQLServer: Workload Group Stats New trace events (e.g. CPU Threshold Exceeded) There are also new DMVs: sys.dm_resource_governor_workload_groups sys.dm_resource_governor_resource_pools sys.dm_resource_governor_configuration Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

DEMO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 1: Initial Demo Setup On a newly installed server (i.e. no prior Resource Governor configuration) demo on a dual core laptop and for the sake of simplicity I'm using a single CPU for SQL Server. To do so, I adjust CPU affinity mask as follows: -- first enable advanced options in sp_configure �� Using 1 CPU for SQL Server on a dual proc machine has an interesting side-effect: we normalize "CPU usage %" counter to number of CPUs on the box and thus, the values will hover around 50% as maximum and not 100% as you might expect. I will illustrate this below. sp_configure 'show advanced', 1 GO �� RECONFIGURE GO �� -- use only 1 CPU on demo machine sp_configure 'affinity mask', 1 GO �� RECONFIGURE GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

-- 512 Mb is suitable for laptop demo sp_configure 'min server', 512 GO sp_configure 'max server', 512 GO �� RECONFIGURE GO In addition to that for demo purposes I will set min/max server memory to a fixed value, since it will improve predictability of the demo on the laptop. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 2: Workload groups and Resource Pools Now we will be setting up the following hierarchy of workload groups and resource pools: Each corresponding workload group contains queries of the corresponding class or department (i.e. Marketing, Adhoc, and VP). Note that, Marketing and Adhoc queries share the same resource pool, while workload group VP has its own similarly named pool. The reason of such separation will become clear as we see how we adjust Resource Governor controls. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

You will start building the above configuration in a bottom up manner (i.e. starting from pools and going up) To do so, we execute the following T-SQL: -- create user pools -- note that we are using all default parameters CREATE RESOURCE POOL PoolMarketingAdhoc CREATE RESOURCE POOL PoolVP �� -- create user groups -- also note that all groups created with default parameters -- only pointing to the corresponding pools (and not 'default') CREATE WORKLOAD GROUP GroupMarketing USING PoolMarketingAdhoc �� CREATE WORKLOAD GROUP GroupAdhoc USING PoolMarketingAdhoc �� CREATE WORKLOAD GROUP GroupVP USING PoolVP GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 3: Classification Now, what you have just done is created hierarchy of the groups and pools, however, how does the server know about which query goes where? This is where classification comes in. The above picture becomes: Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

There is a couple of things: To do the classification you will need to create a user-defined function that will be executed for every new connection and it will place these new connections in the corresponding workload groups. How will we separate different connections? For demo purposes we will use 3 separate login names which we will check and use inside of the function To implement the above 2 steps we will run the following: -- classifier function should be created in master database -- switch to master unless you are there already USE master GO �� -- create logins to separate users into different groups -- note that we disabled strong password checking for demo purposes -- but this is against any best practice CREATE LOGIN UserMarketing WITH PASSWORD = 'UserMarketingPwd', CHECK_POLICY = OFF CREATE LOGIN UserAdhoc WITH PASSWORD = 'UserAdhocPwd', CHECK_POLICY = OFF CREATE LOGIN UserVP WITH PASSWORD = 'UserVPPwd', CHECK_POLICY = OFF GO �� -- note that this is just a regular function CREATE FUNCTION CLASSIFIER_V1 () RETURNS SYSNAME WITH SCHEMABINDING BEGIN DECLARE @val varchar(32) SET @val = 'default'; if 'UserVP' = SUSER_SNAME() SET @val = 'GroupVP'; else if 'UserMarketing' = SUSER_SNAME() SET @val = 'GroupMarketing'; else if 'UserAdhoc' = SUSER_SNAME() SET @val = 'GroupAdhoc'; return @val; END GO Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 4: Are we there yet? After all this work, can we start workloads and see what happens? The answer, as you have guessed by the question is - no. What's left? Again, a couple of steps: We need to tell Resource Governor to use the function that we just created Make all the changes effective First step is done by -- make function known to the Resource Governor ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1) GO For the second step, let's compare output of catalog views with in-memory information (note difference in names of catalog views and dynamic management views (DMVs) which are prefixed with dm_: -- metadata information SELECT * FROM sys.resource_governor_workload_groups SELECT * FROM sys.resource_governor_resource_pools SELECT * FROM sys.resource_governor_configuration �� -- in-memory information SELECT * FROM sys.dm_resource_governor_workload_groups SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_configuration Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Now transfer changes from metadata to memory by running the following statement. Also, do not confuse it with already existing RECONFIGURE command: -- make the changes effective ALTER RESOURCE GOVERNOR RECONFIGURE GO And rerun the above query on metadata and DMVs and you should see that new groups, pools and classifier function ID are present in corresponding DMVs. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 5: Running the workloads The easiest way to simulate a CPU intensive workload is to run the following in a loop: set nocount on declare @i int declare @s varchar(100) �� set @i = 100000000 �� while @i > 0 begin select @s = @@version; set @i = @i - 1; end Also, instead of running this query from the Management Studio, consider saving it in a file and running from a command prompt by using a script similar to the below. Note that we are using 3 different user names to connect to the server. echo "Press any key to start Marketing workload" pausestart sqlcmd -S <your_server_name> -U UserMarketing -P UserMarketingPwd -i "CPU intensive loop.sql" echo "Press any key to start VP workload" pause start sqlcmd -S <your_server_name> -U UserVP -P UserVPPwd -i "CPU intensive loop.sql" echo "Press any key to start Adhoc workload" pause start sqlcmd -S <your_server_name>" -U UserAdhoc -P UserAdhocPwd -i "CPU intensive loop.sql" Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

To observe the effects of the load, add the following performance counters in the perfmon: We will monitor CPU usage per group in the 1 st instance of perfmon; add "SQLServer:Workload Group Stats object", "CPU usage %" counter for "GroupMarketing", "GroupAdhoc" and "GroupVP" instances We will monitor CPU usage per pool 2 nd instance of perfmon, add "SQLServer:Resource Pool Stats object", "CPU usage %" counter for "PoolMarketingAdhoc" and "GroupVP" instances Before you start the next workload, observe the counters for pools and groups for a number of seconds, you should see approximately the following: Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

G R O U P S Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

P O O L S Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Few things to note: Remember I set affinity mask to 1? This is why the maximum on the figures is only 50% (it is for a single CPU while usage is normalized to all CPUs on the machine, 2 in my case) At point A, I started Marketing workload, CPU usage went to the maximum on the CPU for a single workload, 50% in this case At point B, I started VP workload, which, as you remember, belongs to a separate resource pool and it made Marketing group share half of the CPU with VP group. The same is true for the pools (because we have 1 to 1 match of active requests in groups to pools at this point) At point C, I started Adhoc workload, which has its own group but shares the pool with Marketing workload. What happened here on the groups is that all 3 of them are now sharing the CPU getting approximately 1/3 of it or roughly 17%. On the pool side, however, we can see that Marketing Adhoc pool which shows aggregate resource usage by all groups inside of it, has 2/3 or the CPU while Pool VP has only 1/3. This behavior is exactly what you get on SQL Server 2005. Resources are distributed as they are being requested and whoever needs more CPU simply gets it. This was one of the goals: to make behavior as close as possible to SQL Server 2005 when you do not use the Resource Governor or adjust any parameters of it. Note, however, by creating the groups and pools you are already slightly altering the behavior of SQL Server 2008, but more on this in the next few posts. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Step 6: Management Actions Now we came to the point where we want to apply action to change the above picture. Specifically, we want our VP workload to proceed faster and thus, limit CPU usage by Marketing and Adhoc workloads to 50% of the CPU. To do this, we alter the PoolMarketingAdhoc using the following syntax (remember, we created the pool using all default parameters): -- adjust PoolMarketingAdhoc to not consume more than 50% of CPU ALTER RESOURCE POOL PoolMarketingAdhoc WITH (MAX_CPU_PERCENT = 50) Remember to make changes effective: -- make the changes effective ALTER RESOURCE GOVERNOR RECONFIGURE Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

G R O U P S G R O U P S Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

WHAT ARE THE LIMITATIONS? CPU / Memory only (no I/O yet) 2008 Database Engine only (no SSAS, SSRS, SSIS) Single instance only Short OLTP operations are immune to constraints Lack of contention can also prevent enforcement Cannot constrain “internal” processes Must disable classification to modify classifier function(you can’t apply these changes to existing sessions) Pool / group names are case sensitive Enterprise Edition only! Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

COMMON MISTAKES Expecting importance to mean priority; importance applies weights to resources, does not form a “queue” Expecting classifier function to be case insensitive; MiS-CAsEdgroup names will end up in default group Creating classifier function in wrong database; function must be in master Cluster / multi-instance failover scenarios; need to be prepared to have instances share resources Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

Conclusion The Resource Governor Feature of SQL Server 2008 helps DBA’s to monitor and control the CPU and memory utilization with respect to different workload groups. Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified

THANK YOU! Aaron Shilo , Database Consultant Oracle & MS Sql Server Certified [email_address] 050-4477117

�ݺ�ߣ

resource governor

More Related Content

What's hot (20)

Similar to resource governor (20)

resource governor