SQL Server 2005 - Tuning a Database

SQL Server 2005 - Tuning a Database

In this tutorial you will learn about Database Tuning Advisor overview, Database Engine Tuning Advisor Features, Improved Workload Parsing, Enhanced Scalability, Integrated Tuning, Multiple Database Tuning, Offload of Tuning Overhead to Test Server, Drop-Only Tuningm, XML Support, User-specified Configuration and "What-if" Analysis Support, Analysis Reports, Tuning Sessions, Database Engine Tuning Advisor Tuning Capabilities and Tuning SQL Server 2000 and 2005 databases with DTA.

Database Tuning Advisor overview

In SQL Server 2005 the Database Tuning Advisor has been introduced in place of the Index tuning wizards of the earlier SQL Server versions. It contains a graphical user interface and a command line utility called the dta.exe. The Database Tuning Advisor provides better quality recommendations, enhanced the type of events evaluated and the types of physical design structures considered.

Database Engine Tuning Advisor Features

Improved Workload Parsing

The new Database Tuning Advisor handles batches that reference transient tables. It does not terminate tuning due to an inability to parse an event. Instead it logs the event into the tuning log and proceeds with the task on hand. It parses and tunes queries that reference user defined functions and handles all the USE statement in a trace when multiple databases are being tuned. Events are correctly tuned using the LoginName column in the workload and also tunes statements in triggers.

Enhanced Scalability

The Database Tuning Advisor uses workload compression to reduce the amount of time spent in tuning and uses an algorithm to avoid redundant statistics creation and reduces the number of I/Os incurred during tuning.

Integrated Tuning

Database Tuning Advisor has the capability of harnessing the integrated configuration of multiple design structures to provide high quality recommendations. This reduces the cost of the execution for any given query.

Multiple Database Tuning

The Database Tuning Advisor can tune multiple databases simultaneously. The set of databases to be tuned can be specified and the DTA makes recommendations for all the databases.

Offload of Tuning Overhead to Test Server

When large workloads are large significant overheads on the server being tuned are created. The DTA then needs to make several calls to the query optimizer during a tuning process. It is advisable to use a test server in addition to the production server to eliminate this problem. The production server environment is duplicated in the test server to the extent that metadata, statistics and hardware parameters are copied from the production server for simulating the required environment. The recommendation then received from the test server can be implemented in the production server.

Drop-Only Tuning

DBA’s need to drop structures which accumulate in databases over a period of time. The drop only tuning option helps him identify existing physical design structures that are derelict. This drop only option can be used with the-fx argument in the dta.exe utility. In the GUI utility the Evaluate utilization of existing PDS only option can be selected from the Tuning options tab. New structures are never proposed by the DTA and the Evaluate utilization of Existing PDS only cannot be used with options that add indexes, indexed views or partitions.

XML Support

DTA operations can be defined in an xml schema input file format defined in the installation directory of Microsoft SQL Server 2005. This feature enables the DBA to have more flexibility.

User-specified Configuration and "What-if" Analysis Support

It is possible to provide hypothetical configuration of physical design structures as a tuning input to the Database Engine Tuning Advisor. These databases can then be implemented as if they were real inputs. In other words, a what if analysis can be done for a given physical design structure even before it is actually designed and implemented. This feature is fully supported by the XML schema that comes with the DTA.

Analysis Reports

Once the tuning process is complete, DTA generates XML reports and recommendations. The reports detail the cost of queries in a workload, or relationships between queries and the indexes they reference. The GUI can be used to view the reports or the xml editor can be used.

Tuning Sessions

Each tuning session can be given a unique identity in the DTA. The tuning session information is saved once the process is complete and it is associated with the identifier. The identifier, then, can be used to reload the tuning session as and when required. These sessions make comparison of reports of different sessions possible using the Session Monitor.

Database Engine Tuning Advisor Tuning Capabilities

The DTA recommends the best mix of indexes for databases by using the query optimizer and analyzing queries in a workload. It can recommend aligned or non aligned partitions for databases references in the workload. It recommends indexed views for databases in the workload and analyzes the effects of the proposed changes including index usage, query distribution among tables and query performance in a workload. It recommends ways to tune the database for small set of problem queries or permits customization of the recommendation by specification of advanced options such as disk space constraints. It provides the DBA with report summaries of the impact of implementing the recommendation of a given workload.

Tuning SQL Server 2000 and 2005 databases with DTA.

The SQL Server 2005 Database Engine Advisor (DTA) can be used to tune SQL Server 2000 and SQL Server 2005 databases.

SQL Server 2000 databases can be tuned using the following features of the SQL Server 2005 Database Tuning Wizard.

1. Time bound tuning which controls the amount of time taken by the Database Engine Tuning Advisor to analyze a workload. The quality of recommendations is directly in proportion to the amount of time taken. The more the time taken the better the quality of the recommendation made.

2. Workloads spread over multiple databases can be tuned. The DTA recommends indexes, indexed views or partitioning of databases in the workload.

3. The DTA helps tune broader classes of events and triggers. Workloads can include User-defined functions, batches that reference temporary tables, statements in triggers etc.

4. A tuning log is maintained by the DTA for all the events that cannot be tuned and the reason why the tuning could not be performed. This log can be viewed during the tuning session.

5. The DTA supports What-if analysis. The configuration of existing and hypothetical physical designs can be specified in an XML input file. The DTA then evaluates the impact of the physical design structures without expensive overheads relating to implementing the configuration before the tuning is performed.

6. The DTA allows the DBA to specify a greater variety of tuning options such as adding nonclustered indexes or keeping existing clustered indexes etc.

7. Finally DTA provides XML support. It accepts xml files as input and generates the recommendations in XML.

SQL Server 2005 databases can be tuned using the following features of the DTA in addition to the ones already mentioned in the context of SQL Server 2000 databases:

1. Partitioning is recommended by the DTA when performance can be improved on large tables.

2. A test server is supported when the production server tuning load has to be reduced. Most of the load is offloaded into the test server by using the production server hardware configuration information, but does not copy the data from the production server to the test server. However the metadata is copied along with the required statistics.

3. The database tuning advisor can be used only by members of the sysadmin fixed server role or db_owners fixed database role and hence security can be maintained.