SQL Server database optimization method (1)

 

I often encounter some questions about database optimization. I think this is the most basic skill requirement, especially the use of SQLServer2005. for SQL Server optimization, full-time or part-time DBAs are easier to work and more efficient, but many new users may not know much about them, I will write some basic introductions here.

　I. First, I will introduce the two tools used to optimize data in SQL Server 2005.

1. The first tool, SQL ServerProfileThis tool is also available in SQL Server 2000. When using SQL 2000 databases, I often use this tool to capture SQL statements with performance problems.

SQL Server Profiler is a tool used by the server to capture SQL Server 2005 events. An event can be saved in a trace file, analyzed in the future, or used to replay a series of steps when trying to diagnose a problem.

SQL Server Profiler can be used for the following activities:

1) analyze problematic queries to find out the cause of the problem.

2) search for and diagnose slow queries

3) capture a series of T-SQL statements that cause a problem, then copy the problem on a test server with the saved trace, and then diagnose the problem on the test server.

4) monitor SQL server performance to optimize workload

5) Associate performance counters with diagnostic problems

When using SQL Server Profiler, You can group the trace or trace files in the "duration", CPU, "read", or "write" column to eliminate data faults. For example, you can troubleshoot data faults for queries with poor performance or queries with extremely high logic read operations.

By saving a trail to a table and usingTransact-SQLQuery event data to find other information.

Tool interface

　　

2. The second tool is SQLServer2005 newly added tool-database engine optimization Consultant

The database engine optimization advisor is used to analyze the performance of workloads running on one or more databases. After analyzing the workload of the database, the database engine optimization consultant will provide suggestions for adding, deleting, or modifying the physical design structure in the Microsoft SQL Server database. These physical performance structures include clustered indexes, non-clustered indexes, index views, and partitions. After these structures are implemented, the database engine optimization advisor enables the query processor to execute workload tasks in the shortest time.

Database administrators can use database engine optimization consultants for exploratory analysis. Exploratory Analysis requires comprehensive use of Manual Optimization and tool-assisted optimization. To use the database engine optimization consultant for exploratory analysis, you must use the configuration function specified by the user. You can use the configuration function specified by the user to specify and optimize the configurations of the existing and hypothetical physical design structures (such as indexes, index views, and partitions. The advantage of specifying a hypothetical structure is that you can evaluate their impact on the database without first implementing these structures.

Although both the graphic user interface (GUI) of the database engine optimization Advisor and the DTA command line utility support exploratory analysis, DTA is more flexible because it can use XML input files. The XML input file uses the XML architecture of the database engine optimization advisor. This is a published architecture that can be downloaded by accessing the database engine optimization advisor architecture.

　Two modes of Exploratory Analysis

You can use one of the following two modes to perform exploratory analysis:

1) Evaluation Mode

In the evaluation mode, the database engine optimization consultant compares the current configuration cost (c) with the user-specified configuration cost (u) under the same workload. Because C is composed of the existing physical design structure in the database, C is always the actual configuration. In comparison, U is a configuration composed of the actual and hypothetical physical design structures. If the database engine optimization consultant reports that the cost of U is lower than the cost of C, the physical design performance of u may be better than that of C.

For example, the evaluation mode is useful in the following situations:

The database administrator must determine the impact of adding non-clustered indexes to the table on performance.

The database administrator just completed using the database engine optimization advisor to optimize the database and accepted the suggestion (R ). After viewing the R, the Administrator may fine-tune it by modifying the R.

For example,

The database administrator wants to add two non-clustered indexes and delete one non-clustered index in R. After the R is modified, the Administrator sends the modified R as the input to the database engine optimization consultant and optimizes it again to measure the performance impact of the modified R.

2) optimization mode

In the optimization mode, the database administrator knows that a part of the physical design of the database should be modified. However, the database engine optimization consultant is expected to provide suggestions on the optimal physical design structure for other configurations.

For example, the optimization mode is useful in the following situations:

The Database Administrator understands that the fact that a data table is too large must be partitioned. The Administrator must select monthly or quarterly. You can use either of the following methods to partition a table, but the administrator wants to select a partitioning method that provides the best performance under a given workload. To determine the best partitioning method, the administrator can use the database engine optimization consultant for two chemical engineering workloads.

First, the Administrator optimizes the workload through the user-specified configuration and the table with monthly partitions.

Then, use a quarterly partitioned table to optimize the workload.

After two assumptions are used to optimize the workload, the administrator can determine the partition method that can provide the best performance by comparing the increased percentage.

For example:

The orders table must contain the clustered index of the ship_date column. The database administrator wants to determine a group of optimal non-clustered indexes for the orders table. By specifying the configuration specified by the user (this configuration includes the clustered index of the ship_date column in the orders table), the database administrator can partially modify the physical database design. Then, you can use the database engine optimization consultant in the optimization mode to determine the impact of the user-specified configuration on performance.

The most common causes of unoptimized database engine optimization events include:

1) The workload references tables that the user has not selected for optimization.

2) The table referenced by the workload is too small. For example, the table contains less than 10 data pages.

3) The database engine optimization consultant cannot optimize the workload within the specified time range.

(Workload is the database engine optimization consultant analysis object, it by one or more of the database to optimize the execution of a group of T-SQL statements.