Problem solve Get help with specific problems with your technologies, process and projects.

Scaling SQL Server 2008 performance and efficiency

Some features were added to SQL Server 2008 that not only maximize scalability and performance of SQL instances, but also save on licensing costs.

Two goals of SQL Server 2008 were to increase scalability and deliver predictable performance. To squeeze maximum scalability from SQL Server 2008, Microsoft added the following features:

  • Resource Governor
  • Management Data Warehouse
  • Policy-based management
  • Increased hardware support

Resource Governor

Resource Governor is a feature introduced in SQL Server 2008 Enterprise and Developer Editions. It was designed to deliver predictable performance across a SQL Server instance. It allows you to scale the number of databases, applications and users that SQL supports. This capability prevents one query from consuming SQL Server resources and degrading all other processes running on that server.

Resource Governer allows you to scale the number of databases, applications and users that SQL supports.

You can also throttle the amount of CPU and memory that a group of processes will consume. For example, if you are using backup compression in SQL Server 2008, you may want to limit the amount of CPU consumed during the backup process so that other jobs running during that time don't experience CPU contention or performance degradation.

Resource Governor is configured by identifying processes at login using a login name, host name or application name. The processes then run in a resource group. More than one resource group runs within a resource pool. You can limit a resource pool's minimum and maximum CPU and memory, which limits the processes when the SQL Server is low on CPU or memory.

Other high-priority tasks can be directed to a resource pool with larger amounts of CPU and memory dedicated to it. Consequently, when running in conjunction with workloads in different resource pools with fewer resources, the tasks won't suffer from performance degradation.

What does this mean for a DBA? Overall, it means predictable performance—essential tasks can be configured with the necessary resources to complete a task with little resource contention. DBAs can prioritize workloads, and nonessential reporting tasks will then operate in the background without contending essential processes. SQL Server 2008 also supports more workloads simultaneously and allows more databases to be consolidated on a single SQL Server That way, DBAs can scale up to support more users without having to invest in additional hardware or machines.

Management Data Warehouse

SQL Server 2008's Management Data Warehouse feature lets you collect performance statistics on one or more SQL servers. You can then store and aggregate the information for reporting and analysis to quickly obtain metrics from SQL Servers to understand why they're underperforming or how performance patterns are changing over time.

DBAs can perform forensics on transient performance problems or determine why a query executed incorrectly one day and worked fine the next. They can also see which servers are underutilized and which ones are underpowered. With the Management Data Warehouse feature, DBAs can understand why a machine is underperforming and know how to fix it.

The Management Data Warehouse feature is only available in SQL Server 2008 Enterprise Edition.

Policy-based management

Not following best practices is one of the biggest factors limiting SQL Server scalability.

Not following best practices is one of the biggest factors limiting SQL Server scalability. Storing database log files and data files on the same drive will cause I/O contention and limit the scalability of all applications that access that SQL Server.

With policy-based management, you can configure a set of policies to ensure that all SQL Server instances adhere to a set of best practices. You can configure policy-based management to deny any change to a stored procedure, table, function, database and so on that would make it fall out of compliance. You can also configure policy-based management to report on SQL servers that no longer follow best practices or company standards.

Increased hardware support

SQL Server 2008 now supports Hot Add Memory and CPU, Non Uniform Memory Architecture (NUMA) as well as the ability to host 50 SQL Server instances on a single machine. By hosting 50 SQL Server instances on a single machine you only need one license for Windows and one SQL Server 2008 Enterprise license. This will pay dividends compared to virtualization, where you would need to purchase licenses for the hypervisor, SQL Server and Windows for each virtual instance.

Continue to part four

Justifying an upgrade
The best of SQL Server 2008

Upgrading and installing SQL Server 2008

Part 1: Part 2: Part 3: Scaling SQL Server 2008 Part 4:

Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. He is the author of A Guide to SQL Server 2000 Transactional and Snapshot Replication, published by Not While The Surf's Up Press, 2004.

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.