SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/17/2014 3:56:04 AM

Another way to control performance is to just prevent the processes from using as much of the CPU, memory, or disk space as possible. If any one process can’t access very much memory, more memory is available to all the other resources. If you use less space on the disks, you will have more room for growth. SQL Server 2012 supplies tools that let you manage resources. The Resource Governor lets you put hard stops on the amount of memory or CPU that requests coming in to the server can consume. Table and index compression is now built into SQL Server.

Limiting Resource Use

The Resource Governor helps you solve common problems such as queries that just run and run, consuming as many resources as they can; uneven work loads where the server is sometimes completely overwhelmed and other times practically asleep; and the ability to say that one particular process has a higher, or lower, priority than other processes. The Resource Governor will identify that a process matches a particular pattern as it arrives at the server, and it will then enforce the rules you’ve defined as appropriate for that particular pattern. Prior to setting up the Resource Governor, you will need to have a good understanding of the behavior of your application using tools already outlined such as Performance Monitor, the Data Collector, and profile traces.

When you’ve gathered at least a few days’ worth of performance data so that you know what limits you can safely impose on the system, you’ll want to configure the Resource Governor. You can control the Resource Governor through SQL Server Management Studio or T-SQL statements. T-SQL statements are going to give you more granular control and the ability to program behaviors, and they are required for some functions, but using SSMS for most functions will be easier until you have a full understanding of the concepts.

The Resource Governor is not enabled by default. To enable it, navigate to the Management folder in the Object Explorer in SSMS. Right-click the Resource Governor icon, and select Enable from the context menu. The Resource Governor is now running.

The Resource Governor works by managing the resources available in pools. A resource pool describes how much memory and CPU will be available to processes that run within that pool. To create a new pool, you first have to enable the Resource Governor by right-clicking the icon and selecting Enable Resource Governor. Next, right-click the Resource Governor icon, and choose New Resource Pool from the context menu. A window similar to Figure 1 will open.


Figure 1. Resource Governor Properties window

We’ve created a new resource pool called MyResourcePool and placed limits on the Maximum CPU % of 60 percent and on the Maximum Memory % of 50 percent. This means that processes that run within the pool will be able to use only 60 percent of the processor power on the server and no more than 50 percent of the memory.

You define the processes that run within a pool by creating a workload. In the workload, you can further control the behavior of processes. These are the metrics you can control:

  • Importance: This sets the priority of the particular workload within the resource pool. It doesn’t affect behavior outside that pool. This is useful for specifying which workload gets more of the resources within the pool.
  • Maximum Requests: This limits the number of simultaneous requests within the pool.
  • CPU Time (sec): Use this to put a limit on how long a process within the workload can wait for resources to be freed. Setting this limit gets processes out of the way if there’s a lot of stress on the server.
  • Memory Grant %: This one limits how much memory can be granted from the pool to any individual process within the workload.
  • Grant Time-out(sec): This is like the CPU time limit, but it limits how long the process can wait for memory.
  • Degree of Parallelism: In systems with multiple processors, this limits how many processors can be used by processes within the workload.

Finally, to set up the Resource Governor to get it to recognize that processes belong to this pool and this workload, you need to define a function called a classifier. The classifier is a user-defined function that uses some sort of logic to decide whether the processes coming into the system need to be passed on to the Resource Governor. An example might be to limit the amount of resources available to queries run from Management Studio on your server. Inside the database you want to govern, you would create a function that looks something like this (governorclassifier.sql in the download):

CREATE FUNCTION GovernorClassifier ()
        SET @GroupName = 'MyWorkLoad' ;
    RETURN @GroupName ;

You have to assign this to the workload only through the drop-down available in the Resource Governor Properties window, as shown in Figure 13-34. Now, when queries come in from SQL Server Management Studio, they will be limited, as defined by the workgroup and the pool, to the available resources. This will leave more resources for all other processes, thereby preventing their performance from degrading, which works out to be the same as improving it.

Leveraging Data Compression

Index and data compression are available only in the Enterprise and Developer editions of SQL Server. Data in SQL Server is stored on a construct referred to as a page. When you read data off the disk, you have to read a whole page. Frequently, especially when running queries that return large sets of data, you have to read a number of pages. Reading data from disk, as well as writing it there, are among the most expensive processes that SQL Server performs. Compression forces more data onto a page. This means that when the data is retrieved, fewer reads against the disk are necessary with more data returned. This can radically increase performance. Compression does not come without a cost, however. The process of compressing and uncompressing the data must be taken up the CPU. On systems that are already under significant stress in and around the CPU, introducing compression could be a disaster. The good news is that CPU speed keeps increasing, and it’s one of the easiest ways to increase performance. No application or code changes are required to deal with compressed data or indexes.

You can compress a table or indexes for the table separately or together. It’s worth noting that you can’t count on the compression on a table or a clustered index to automatically get transmitted to the nonclustered indexes for that table. They must be created with compression enabled individually. Compression can be implemented at the row or page level. Page compression uses row compression, and it compresses the mechanisms that describe the storage of the page. Actually, implementing either of these types of compression is simply a matter of definition when you create the table. The following script (createcompressedtable.sql in the download) shows how it works:

    (Col1 INT NOT NULL,
     Col2 NVARCHAR(50) NULL
    WITH (

This will create the table dbo.MyTable with page-level compression. To create the table with row-level compression instead, just substitute ROW for PAGE in the syntax. To create an index with compression, the following syntax will work (createcompressedindex.sql in the download):

ON dbo.MyTable (Col2)

Using fewer resources for storage can and will result in performance improvements, but you will need to monitor the CPU on systems using compression.

  •  SQL Server 2012 : Tuning Queries (part 3) - Using the Database Engine Tuning Advisor
  •  SQL Server 2012 : Tuning Queries (part 2) - Gathering Query Information with Extended Events
  •  SQL Server 2012 : Tuning Queries (part 1) - Understanding Execution Plans
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 2) - Setting and Verifying Permissions to the Stored Procedures
  •  SQL Server :ONE-WAY ENCRYPTION - Creating the Interface (part 1) - Creating the View, Creating the Stored Procedures
  •  SQL Server : Implementing One-Way Encryption (part 1) - Populate the Hash Columns, Verify the Implementation, Drop the Unencrypted Column
  •  SQL Server : Implementing One-Way Encryption (part 1) - Create the Primary Hash Column,Create a Secondary Hash Column for Searching
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Hierarchical Table Indexing Strategies
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 3) - The GetAncestor Method
  •  Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 2) - The ToString Method
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us