Performance Tuning SQL Server 2008 basics

Performance Tuning SQL Server 2008 basics

SQL Server performance tuning has always been thought of as one of the dark arts. While there have been many tools out there, not many database administrators have known how to use them or how to interpret the results. Because of this, performance tuning has always taken a back seat to project deadlines and unless there were noticeable performance issues with a piece of code or an install of SQL Server, it was not a high priority.


Because performance tuning has traditionally taken a back seat to delivery, database administrator and developers have generally had to be prodded to try to figure out how to trap the performance characteristics of their serves. Generally, this occurred only when a major performance issue cropped up, forcing admins and developers to troubleshooting ongoing issues and spot new issues before they became a major problem.


SQL Server 2008 can be thought of as the release for the database administrator. Many of its features are geared towards the administration of SQL Server and making the database administrator’s job easier to accomplish. With this delivery of administration tools in SQL Server 2008, several features that will help in the performance monitoring and tuning of many SQL Server installations around the world have been included in this next release.


While there are many performance improvements and smaller features found in SQL Server 2008, the main performance tuning features are: Data Collector, Resource Governor, and Policy Management. Utilizing one or more of these new features will help a database administrator to monitor, detect, troubleshoot, and resolve the vast majority of their performance tuning issues in SQL Server 2008.


Data Collector


The Data Collector actually began as the Performance Studio. Despite the name change, its role is to collect data from one or more SQL Server installations into a centralized management warehouse in order to provide a single place the database administrator can go in order to determine the performance characteristics of their database servers and installations.


The Data Collector works by the creation of data collection sets. Data collection sets are a collection of data collectors that roll up a variety of data from many different sources. The sources can be internal to the database, Dynamic Management Views and Functions, or they can be external components such as SQL Server Profiler and Windows System Monitor. The data collectors then store the data in a relational database called the management warehouse so it can be used in performance reports.


Once the data has been collected, a series of predefined and custom reports are built of the stored performance data in order to analyze the data for performance issues. The use of these reports will form the basis of most data performance monitoring solutions. But, since the underlying data is stored in a relational database, additional methods of analyzing and viewing the performance data are possible.


Resource Governor


While the Data Collector serves to collect and analyze performance data, it is a reactive system, as the performance issue must show up before it can be addressed. To combat performance issues as they happen, SQL Server 2008 has made a Resource Governor available to the database administrator. The Resource Governor can be used to categorize incoming connections into different pre-defined workloads and then specify the resources that are available to each of those workloads. This allows the database administrator to prevent certain types of workloads, such as reporting, from utilizing the majority of system resources. This often causes performance issues for the other uses of the database.


The Resource Governor helps to balance the use of the system resources among different types of workloads. It also helps the database administrator make better use of the limited resources that are often found on many database servers.


Policy Management

While not strictly a performance feature, the new Policy Management feature in SQL Server 2008 can be used to help the performance in database environments.


Policy Management enables database administrators to configure different policies that help to manage the instance and database configurations across many different servers and databases. Having a pre-defined policy that covers a set of configuration options helps ensure a consistent set of configurations across all servers and databases. This, in turn, helps database administrators resolve many of the performance issues that often crop up because of incorrect configuration settings.


Summary


While there are many different features of SQL Server 2008, database administrators trying to understand and solve performance tuning issues will find that SQL Server 2008 has included many different features and functionalities to help them in their quest. Database administrators already utilizing performance tools such as Dynamic Management Views and Functions, SQL Profiler, SQL Trace, and Database Tuning Advisor will find that they still exist in SQL Server 2008 and in many cases have been improved in this release. These older tools, along with the new performance features of SQL Server 2008, should help to make this release of SQL Server a favorite among database administrators trying to resolve performance issues.