Been away for some time finishing up on the Upgrade and Application Compatibility testing content and tools. We’re about 98% done with just a few more tweaks before final RTM. If you’re interested in the release candidate versions, check out SQL Server 2008 Upgrade Assistant.

Managed to find some time for an extended lunch so I figured I’d come back and share some other learnings - an easy one this time: backup compression. This is not the same as data compression so don’t confuse the two. I’ll blog on the latter later when I find some time. That’s pretty cool too.

I’ve worked with a lot of customers who use 3rd party backup software primarily for the compression capabilities followed by improved speed, especially during restore. With one of the more popular 3rd party backup solutions, I’ve restored a 1.2 TB database in under 30 minutes on a beefy HP Integrity rx8640 server with very a large EMC Dx3 SAN so there was no IO or CPU bottlenecks at any point. No official hard data on Katmai’s backup compression or performance right now but I know the folks in building 35 will be working hard to at least match the popular 3rd party solutions.

From some of the tests we’ve done both internally and with a couple of customers with tens and hundreds of GB databases, both compression ratio and performance seem to map closely to the popular 3rd party software we used. At least for default compressions on the 3rd party software. You can change the level of compression with some of these 3rd parties but SQL Server 2008 only has 1 level so I didn’t bother testing against different settings. I’d imagine the delta would not be significant percentage wise but if you have tens of Terabytes to deal with, even 5% can be a noteworthy difference.
 

How it’s done

Taking advantage of backup compression once you’ve upgraded to Katmai is just a matter of adding an extra option (in bold below) in your backup scripts 

– My Backup Job with compression 

BACKUP DATABASE MyBigDatabase
TO  DISK = ‘N:\Tier1Backups\MyBigDatabase.bak’
WITH
   NOFORMAT,
   NOINIT, 
   NAME = ‘Full DB Backup - MyBigDatabase’,
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION;
GO

or selecting the compression option if you are using Management Studio GUI.  Note that you can change the global setting at the instance level with

SP_CONFIGURE ‘backup compression default’, 1;

so that all backup commands will always compress by default. This can be changed at the command level also when you execute your backup so you can backup with compression for specific databases only and not for everything in the instance. This is too easy.

Note that the default setting is ‘no compression’ so your DBA team does not get alarmed by the sudden reduction in the backup file sizes; just in case not everyone is keeping close tabs Katmai enhancements. Last thing you want is the graveyard shift operator re-running backup all night and finally paging you at 3am because “the backup file looks too small so it can’t be good…”. Don’t laugh (unless it’s someone else’s turn to be on-call).
 

Sample results

Simple tests with the AdventureWorks OLTP database yielded the following:

Path	Backup time	Restore time	File size
Original database	n/a	n/a	174,080 MB
NO COMPRESSION backup	23s	23s	171,093 MB
WITH COMPRESSION backup	11s	14s	40,097 MB

This was done on a VPC with 1.5GB of dedicated RAM and its own dedicated 7200rpm EIDE disk (8MB cache) on a USB 2 interface. The host machine is a notebook with an Intel Core 2 Duo 2.2GHz CPU (T7500), 4GB RAM and a 7200rpm SATA disk. The times are based on averages from 3 runs - all were with a couple of seconds. Other tests with real customer databases ranging from 5GB to 800GB in size yielded similar levels of compression and percentage elapsed time reduction. Sorry, can’t publish any of the customer data but those tests were run on x64 workstations with local SATA drives and IA64 servers with enterprise class SANs.

Not bad at all. Note the benefits from a performance perspective since you have much less to send to disk or retrieve when restoring. There was some impact on CPU utilization but it’s hard to say what the real impact would be when doing hot backups in a production environment since I don’t have other workloads running. You can expect some CPU load but the level will really depend on your database. Looks like it’ll be low to moderate. So long as you’re not already facing CPU pressure in your current system, I expect this to work fine but don’t take my word for it; test (no really, test). This is important because while few environments do full DB backups during peak hours, many companies have other system jobs or reports run during maintenance windows so make sure you don’t saturate the CPU. All that aside, the numbers are very decent especially since it’s still in beta.

Of course, with any compression technology, database or file system, your mileage can vary significantly. Character data types typically compress very well while binary types not as well (or very poorly in some cases). However, most traditional OLTP systems (e.g. accounting, sales, HR, etc…) will probably benefit from this since the bulk of the data is of character types.I expect this will be a widely used feature both to improve backup/restore performance and to save disk space (enterprise storage is NOT cheap).

One thing to remember is that smaller backup files also benefit some disaster recovery scenarios (basically those that don’t have good DR plans). Companies that do not have a SAN replication solution or some kind of Log Shipping/Database Mirroring solution in place, often transfer backup files to their warm DR site. Smaller files means faster transfer time which means better chance of recovery (you have a smaller window of exposure).

Password lock is available as with regular backups but there is no encryption capability with this release as far as I know. Now if you’re thinking, “hey, I’ve got Transparent Data Encryption, I can mix and match!!“. Well, don’t. Backup compression and TDE, at least based on the current release, do not compliment each other. You get next to nothing in space savings with backup compression on a TDE enabled database but you will burn the extra CPU cycles trying. As far as I can tell, this is true regardless of whether you use SQL Server’s backup compression or some 3rd party backup software with compression. However, some 3rd party solutions do offer backup compression with encryption but that’s a whole different deal. It’s a compressed backup that’s stored encrypted on disk with a non-TDE database.

Chalk this up as one of the “little to no effort” benefits when you move to SQL Server 2008. Just be aware that while it does great at the intended task, it doesn’t have all the bells & whistles that popular common 3rd party solutions do. For example, having different levels of compression and encrypting backups of non-TDE database are the most common I hear from our customers. Got something you want the SQL Server product group to add something for the next release? Go to SQL Server Connect

joe.

If I had to pick my favorite feature in Katmai, it would have to be the Resource Governor. This is something I had wanted for a long time for various reasons and was sorely disappointed when it was cut from Yukon. I’m sure Richard & Euan remember the amount of grief I gave them though it wasn’t really them who made the final cut decision (but they did own the feature back then).

Prior to SQL Server 2008 Resource Governor, there really was very little you could do to manage resources within an instance. The Windows Server Resource Manager controls Windows processes so all you’re doing is restricting what the sqlservr.exe process could consume. Max/min memory settings locked things down at the instance level and so did CPU and I/O affinity. There really wasn’t much that you could do to specific users or groups. Sure, there was the query governor but all you can really do is set an “execution time threshold” for queries. I.E. If the estimated cost of a query exceeds the threshold, it won’t be allowed to run. Not bad for preventing very heavy queries from running but not really controlling resources either and the side effect is users might start opening support tickets thinking something’s wrong with the system because they can’t run their reports. Also, if your statistics are out of date and not automatically updated, queries may be incorrectly allowed or disallowed to run.

With Resource Governor, Admins can now set controls on how resources are consumed in SQL Server. At this time, the RG can control CPU and memory. Disk I/O and network I/O are not covered in this release. Still, this is already a giant step forward.

Let’s try a few things. First, the old way using the Query Governor cost limit. You can set this at the server instance (sp_configure) or on a per connection basis (set query_governor_cost_limit). If your goal is really to prevent long running queries during peak hours, you probably set this at the instance level. After all, how many users do you know will voluntarily not run their heavy queries? If you’re wondering what self-respecting DBA would allow reporting, DSS and other BI type queries run against a production OLTP system during business hours, well, DBAs don’t write the checks or run the business. It is reality today that the business users want/need to analyze data rapidly, not after a day, week or month. While you can create separate reporting servers, there are some genuine needs where a reporting server’s latency is beyond what the business users require. It’s the new world my fellow traditional DBAs - embrace and control it!

Now, let’s try this example. The following script (written by my colleague Suresh) scans the order tables in the Sales schema in the Adventureworks database and offers a discount for purchase quantities greater than 3. A cursor is used to scroll through and evaluate each row, taking action where appropriate. This query takes anywhere from 20-25 minutes to run on a workstation consuming a notable amount of CPU and memory.

USE AdventureWorks;
GO

DECLARE @l_SalesOrderID INT
SET NOCOUNT ON
DECLARE update_cursor SCROLL CURSOR
FOR
 SELECT SalesOrderID FROM Sales.SalesOrderHeader

OPEN update_cursor

FETCH NEXT FROM update_cursor INTO @l_SalesOrderID

WHILE (@@fetch_status = 0)
 BEGIN
  UPDATE Sales.SalesOrderDetail
  SET UnitPriceDiscount = 0.01
  WHERE SalesOrderID = @l_SalesOrderID
  AND OrderQty >= 3
  FETCH NEXT FROM update_cursor INTO @l_SalesOrderID
 END

CLOSE update_cursor
DEALLOCATE update_cursor
SET NOCOUNT OFF
GO

SQL Server 2005 (and earlier)
With SQL Server 2005 and earlier, you would do one of the following:

Instance wide: sp_configure ‘query governor cost limit’, 300

Per connection: SET QUERY_GOVERNOR_COST_LIMIT 300

Either of these will evaluate queries and disallow execution if they exceed 300 seconds. If you tried to execute the sample query from above, you will get a message that looks like:

Error 8649, Severity 17, State 1, Procedure iduSalesOrderDetail, Line 33
The query has been canceled because the estimated cost of this query (1157) exceeds the configured threshold of 300. Contact the system administrator.
Msg 3609, Level 16, State 1, Line 14
The transaction ended in the trigger. The batch has been aborted.

So that accomplishes your goal of not letting heavy queries execute. These settings can be changed at any time so you can setup SQL Agent to set the appropriate value at different times of the day. However, the big downside here is that your “heavy” queries do not get executed at all. This probably won’t go down very well with your end users. How about letting the query run but not allowing it to dominate server resources?

With SQL Server 2008, you have MUCH better control and can keep both regular OLTP and batch/reporting users happy.

There are a few terms we need to be familiar with before diving into Resource Governor. The following are brief descriptions of key terms. BOL has the gory details which I encourage you to check out.

• Workload group: a logical way to classify different types of workloads you want to govern
• Internal group: a “special” group created by and for SQL Server for system processes. This is not user configurable.
• Default group: another special group created by SQL Server to be the catch-all bucket for any workload that doesn’t get allocated into a user defined group for whatever reason.
• Classification function: a UDF that determines with group a submitted workload belongs to
• Resource pools: a logical container for the amount of CPU and memory that members of the pool can consume
• Internal pool: a “special” resource pool created by and for SQL Server to be consumed by system processes (in the internal group). This is not user configurable and will always have higher priority than all other pools.
• Default pool: another special resource pool created by SQL Server to be the catch-all bucket for any group (stars with the default group) that doesn’t get allocated into other user defined pools. While it cannot be dropped, its configuration can be altered.

Getting started with Resource Governor.
Before we can start bossing workloads around with RG, there are a few things we need to consider.

Rules and environment prep:

1. How many logical workgroups do you have in your database? E.g. Regular OLTP users, reports users, batch processors, administrators, security team, etc…. Can you standardize across your organization?
2. What is the typical CPU and RAM utilization of your server? While limits can be adjusted easily, knowing the typical utilization will help avoid setting initial limits that are too low and causing performance problems.
3. Is there a order of priority among your users?
4. Is there an SLA (implicit or written) on query response time?

Once you figure out what you need from a user and administration perspective, you can then start implementing the controls.

Basic setup:

1. Create workload groups in your environment that you want to have specific resource allocations for (others will fall into the default group)
2. Create a classification function to place workloads in appropriate groups
3. Register the classification function with Resource Governor
4. Enable Resource Governor

The syntax itself is easy enough so I won’t elaborate here. The example below uses explicit transactions but it does not have to be that way. However, this is a recommended practice;  in case you make a mistake, it is easily undone (rolled back).

First, let’s create the buckets (known as groups) that we want to place our users into:

BEGIN TRAN
CREATE WORKLOAD GROUP ResGroupAdhoc
CREATE WORKLOAD GROUP ResGroupReport
CREATE WORKLOAD GROUP ResGroupAdmin
CREATE WORKLOAD GROUP ResGroupExecs

Next we need to create a classification function. This is the “filter” that decides which group each query submitted to SQL Server belongs to. Note that any workload that does not get classified by your function goes into the ‘DEFAULT’ group

CREATE FUNCTION ClassifierFn_Basic() RETURNS SYSNAME WITH SCHEMABINDING AS
 BEGIN
   DECLARE @ResGroup AS SYSNAME
   IF (SUSER_NAME() = ’sa’) OR (SUSER_NAME() = ‘REDMOND\v-jyong’)
      SET @ResGroup = ‘ResGroupAdmin’
   IF (SUSER_NAME() = ‘BillG’) OR (SUSER_NAME() = ‘CORP\DaBoss’)
      SET @ResGroup =  ‘ResGroupExecs’
   IF (APP_NAME() LIKE ‘%Management Studio%’) OR (APP_NAME() LIKE ‘%SQL Query Analyzer%’) OR
   (APP_NAME() LIKE ‘SQLCMD’)
      SET @ResGroup = ‘ResGroupAdhoc’
   IF (APP_NAME() LIKE ‘%REPORT SERVER%’) OR (APP_NAME() LIKE ‘%Microsoft Office%’)
      SET @ResGroup = ‘ResGroupReport’
   RETURN @ResGroup
END

COMMIT TRAN
GO

If you aren’t sure what App_Name to filter with, run Profiler for a while just to capture the ApplicationName column.

You can set classification conditions by a number of different connection properties including user name (both SQL Server and domain users), application name, hostname (source machine name), server role, etc… Check out BOL for details.

This function is just a sample of what you can do to place each user connection or application into specific groups. This is how you classify/categorize your users and it is with these groups that you will set resource utilization controls on later.

Next, we need to register the function with Resource Governor and enable RG.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.ClassifierFn_Basic)
GO

Now that we have the function registered and RG enabled, we can set resource usage allocations for each group that we created.

First thing we want to do is to limit the amount of CPU resources ad hoc queries can consume. Let’s say we do not want ad hoc queries to consume more than 20% of CPU cycles  and no more than 15% of available memory if there are other workloads running. To do this, we need to create a resource pool, place the ad hoc group in that resource pool and set limits on that pool.

CREATE RESOURCE POOL ResPoolAdhoc WITH (MAX_CPU_PERCENT = 20,
     MAX_MEMORY_PERCENT = 15)

Next, we will assign the Adhoc group to the resource pool we just created so it will be governed by the policies we set for that pool. We then need to notify Resource Governor (kinda like what we already do when making a change with sp_configure).

ALTER WORKLOAD GROUP ResGroupAdhoc USING ResPoolAdhoc
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Now all queries that are submitted via SQL Server Management Studio, Query Analyzer or SQLCMD (from a command prompt) will not be allowed to consume more than 20% CPU time or 15% of available memory if there are other workloads running. If the server isn’t very busy, RG is smart enough to let the queries use free cycles. For more details on how it works, check out BOL.

You can now repeat this process for other groups to set the max and/or min values for each group by creating and assigning resource pools for the groups. Try it out by submitting the long running query at the start of this article but use different UserIDs that belong to different resource groups. Run several instances of them and you’ll see the effect of the Resource Governor. It becomes very obvious when you run a lot of instances of that query.

Note that if you forget to assign a particular group to a resource pool, it will remain in the default pool. New applications or applications that are not defined in your function will fall in the default group which also falls into the default pool. (For example: another team may decide to use Toad as their query tool instead of Query Analyzer). That’s why it is always a good idea to set the default pool limits to values that you are comfortable with so that queries that are not caught by your classifier function will not hog server resources.

A few other things you should probably be aware of:

• Dedicated Admin Connection runs in the “internal group”. That means you do not need to place it in a group to ensure it will always be able to run. SQL Server already takes care of that for you.
• Don’t create vastly complicated classifier functions. All connections will be filtered through the function so if you have some convoluted code that performs 27000 checks, the connection may timeout before getting clasffied.
• Multiple groups can belong to the same pool (e.g. default pool) but you can still impose some priority for allocation within that pool using the “IMPORTANCE” setting (LOW, MED, HIGH).
• You can do all this in Management Studio, it doesn’t have to be via TSQL

Ok, so this is quite a bit more involved than the Query Governor Cost Limit in SQL Server 2005 and earlier but it also provides a lot more control and flexibility. This is one of those features that requires some work to take advantage of but it really isn’t much work plus the returns are huge. We covered the basics here which should enough to get you started. There are quite a few more things you can do with RG including setting up filters that just log events when queries run for a specific period (want an easy way to track offenders with long running queries?). BOL documents this pretty well and look for whitepapers (to come) on www.microsoft.com/sql/

So what should you do after upgrading to 2008 if you currently use Query Governor Cost Limit? I suggest creating the appropriate rules in Resource Governor (sorry, no such thing as an upgrade/migrate for this) starting with a close to 1-1 match for what you had before. Get used to its behavior then expand with more rules. This will work for probably >95% of the users of the Query Governor Cost Limit. There are some scenarios where you truly do not want a query to run if it is expected to last longer than x minutes. In which case, you can keep both; they are compatible.

Btw, if you’re wondering about how to monitor activity and how the allocations are actually being used, monitoring data is exposed via System Monitor (aka perfmon) and DMVs. BOL documents these well or you could just do what most DBAs do; fire it up and look around.

Beyond workload governance
Performance management and runaway queries aren’t the only things the Resource Governor is good for. With the ability to set resource utilization limits within a database, it becomes harder for malicious acts against your database such as a DDOS attack to cause widespread problems. It also provides greater flexibility and resource usage efficiency in database consolidation. There’s a good reason why this one feature is cited across multiple scenarios in the 2008 release. It really is super handy. Now as soon as we get network and disk IO added to the resources we can govern, DBAs will rule the world (more than before).

joe yong

With every new release, you can generally classify new features and changes to existing features into 3 group:

• Things that require rearchitecture/major code change (including new code) to take advantage of
• Things that require some/minimal changes to take advantage of
• Things that you benefit from just by upgrading

While I was at the SQLPASS summit a couple of weeks back, most of the coversations on SQL Server 2008 were around the big and shiny features like Resource Governor (which really rocks btw), MOLAP enabled write-back, LINQ, DMF, backup compression, etc…. There are other features that didn’t get quite as much mention though they will likely help just about any deployment though the medium and large ones will likely benefit more.

I’ll be looking into some of these features, picking out the ones that require little or no change to an existing system to take advantage of - things you get just by upgrading.

One such feature is hot-add CPU. With SQL Server 2008, you now have the ability to add CPUs to the server without needing to bring the server down and restarting. This is takes advantage of some of the work done in 2005 where you can dynamically allocate/de-allocate existing CPUs (via affinity setting) without needing to restart the instance. With 2008, you can do this with nett new CPUs on the server. It doesn’t allow hot-remove but the need for removal is far lower than adding.

Customers who struggle with trying to balance between provisioniong a server that will serve them for as long as possible without taking it offline for upgrades vs. increasing resource utilization will love this feature. With hot-add memory already supported in SQL Server 2005, users can now provision a lightly populated server and increase RAM and/or CPU when they need to without incurring downtime. One clever default behaviour is that new CPUs added to the server will not automatically get used by SQL Server. You will need to run RECONFIGURE before SQL Server will start assigning work to the new CPU. This is a good thing because you might have added the new CPU

• for another instance in a multi-instance server - very common in consolidated environments (and this is a great feature for it)
• for a non-SQL Server workload on your machine - backup agents, management agents, etc…

Before you run out there and start plugging things into your server, do note that you need to have supported hardware and operating system else, you’ll be hitting that speed-dial for customer support in no time. Also, as of this time, this feature is only available on the Enterprise Edition of SQL Server 2008.

joe.