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 ()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName AS SYSNAME ;
IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
SET @GroupName = 'MyWorkLoad' ;
RETURN @GroupName ;
END
GO
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:
CREATE TABLE dbo.MyTable
(Col1 INT NOT NULL,
Col2 NVARCHAR(50) NULL
)
WITH (
DATA_COMPRESSION = PAGE) ;
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):
CREATE NONCLUSTERED INDEX ix_MyTable1
ON dbo.MyTable (Col2)
WITH ( DATA_COMPRESSION = ROW ) ;
Using fewer resources for storage can
and will result in performance improvements, but you will need to
monitor the CPU on systems using compression.
