Optimization of SQL Server common performance problems

SQL Server's common performance problems can be considered in three aspects: operating system and database configuration, table/index/storage settings, and SQL statements. For delivered systems, it is often difficult to optimize SQL statements due to actual conditions. This article summarizes the practical methods for SQL Server performance optimization.

Operating System-Related Optimization

The operating system performance directly affects the database usage performance. If the operating system has problems, such as CPU overload, excessive memory switching, and disk I/O bottlenecks, simply adjusting the internal performance of the database will not improve the system performance. We can monitor various devices through the system monitor of Windows NT to find performance bottlenecks.

A common performance problem of CPU is the lack of processing capability. The system's processing capability is determined by the number, type, and speed of the system's CPU. If the system does not have sufficient CPU processing capabilities, it cannot process transactions quickly enough to meet the needs. We can use system monitor to determine the CPU usage. If we run at a rate of 75% or higher for a long time, we may encounter a CPU bottleneck problem. At this time, we should upgrade the CPU. However, you must monitor other features of the system before the upgrade. If the SQL statement efficiency is very low, the optimization statement will help solve the problem of low CPU utilization. However, you can add a CPU or replace it with a faster CPU when it is determined that a stronger processing capability is required.

Memory usage of SQL Server is one of the most critical factors in SQL Server performance. The relationship between memory and the I/O subsystem is also an important factor. For example, in systems with frequent I/O operations, the more memory available for SQL Server to cache data, the less physical I/O that must be executed. This is because the data will be read from the data cache rather than from the disk. Similarly, insufficient memory may cause obvious Disk Read and Write bottlenecks, because insufficient system cache capacity will lead to more physical disk I/O.

You can use system monitor to check the buffer cache hit ratio counter of SQL Server. If the hit rate is often lower than 90%, you should add more memory.

The bottleneck problem that occurs in the I/O subsystem is the most common hardware-related problem in the database system. Poorly configured I/O subsystems cause performance problems only when poorly compiled SQL statements are used. The I/O subsystem problem occurs in this way. I/O operations can be performed on a disk drive only limited. Generally, a general disk drive can only process 85 I/O operations per second, if the disk drive is overloaded, the I/O operations to these disk drives will be queued, and the SQL I/O latency will be very long. This may cause the lock to last longer or keep the thread idle while waiting for resources. The result is that the performance of the entire system is affected.

It may be the easiest to solve problems related to the I/O subsystem. In most cases, adding a disk drive can solve this performance problem.

Precisely locate the root cause of database performance bottlenecks

Performance problems are often related to database design and SQL statements. During performance optimization, the goal is not to allow us to propose upgrade suggestions for hardware facilities. We need to find the real root cause of performance, rather than a plausible excuse.

Although performance problems vary, most problems can be diagnosed and analyzed using general methods. If you have a resource bottleneck to some extent, try to find out all the causes. For example, if the throughput is significantly reduced due to excessive CPU usage (that is, there is a CPU bottleneck), we recommend that you first determine the five CPU users on the server. If the throughput is significantly reduced due to I/O saturation (with an I/O bottleneck), we recommend that you first determine the five largest I/O users on the server.

Is there lock blocking (Lock competition )? We recommend that you analyze the resources (tables and indexes) that are being used and determine the five SQL statements involved in the competition. Although it is not stated whether the problem is from SQL Server 2000 or SQL Server 2005, the methods for determining the causes of different bottlenecks are only slightly different between platforms. These general rules of thumb should be helpful to you. On any platform, you can restrict the investigation scope to a specific database.

Reduce lock usage in SQL Server databases

The use of locks is an important factor that leads to performance. Too many locks may cause serious performance degradation and even deadlock. At the same time, the lock is inevitable in any database application with multiple users. But on the other hand, if the application has a large number of locks, you can take appropriate measures.

In addition to re-designing the system, the preferred measure is to evaluate the Indexing policy. If your application environment has clustered indexes, make sure that they store data on the 8 K page and stay away from each other. Even make sure that the fill factor (and pad index) is set to a non-default value, which may be 75% to 80%, so that more space can be inserted between data pages. As an auxiliary measure, make sure that your transaction gets the minimum number of required locks. At the same time, try to restrict the use of wildcards (such as select * from...), especially the time when the insert, update, and delete transactions remain open and uncommitted.

Second, you can use the set deadlock_priority command to set that a transaction or a group of transactions always have a low priority (or always have a high priority ).

Finally, you can change the default locking behavior of a given connection by using the SET transaction isolation level command, or use a query prompt such as nolock to modify the behavior of a single query to change by query. If you do not carefully consider the consequences of changing the inherent behaviors of the SQL Server database, we recommend that you do not perform this operation.

Rational use of Indexes

An index is an important data structure in a database. Its fundamental goal is to improve query efficiency. The index should be used properly. The usage principles are as follows:

The optimizer automatically generates indexes for fields that are frequently connected but not specified as foreign keys; indexes are created on columns that are frequently sorted or grouped (that is, group by or order by operations). indexes are created on columns with many different values that are frequently used in condition expressions, do not create indexes on columns with less values. For example, in the "gender" column of the employee table, there are only two different values: "male" and "female", so there is no need to create an index. If an index is created, the query efficiency is not improved, but the update speed is greatly reduced. If multiple columns are to be sorted, you can create a composite index on these columns.

Common SQL statement skills

1. If there is a unique index, the where clause with the "=" operator has the best performance, followed by closed intervals (ranges) and then open intervals.

2. From the perspective of database access, where clauses containing discontinuous connectors (or and in) generally do not have good performance. Therefore, the Optimizer may adopt the r policy, which will generate a worksheet containing each identifier that may match the execution. The optimizer will mark these row operators (page numbers and row numbers) it is regarded as a "Dynamic Index" pointing to the matched rows in one table ". The optimizer only needs to scan the worksheet, retrieve each row identifier, and then obtain the corresponding row from the data table. Therefore, the cost of the r policy is to generate a worksheet.

3. Contains not, <>, or! The where clause = is of no use for the optimizer's index selection. Because such clauses are exclusive rather than inclusive, the selectivity of clauses cannot be determined before scanning the original data table.

4. Restrict data conversion and string operations. The optimizer generally does not generate index selection based on the expressions and data conversion types in the WHERE clause. For example:

Paycheck * 12> 36000 or substring (lastname,) = "L"

If the table has an index for paycheck and lastname, the index cannot be used for optimization. You can rewrite the above conditional expression:

Paycheck <36000/12 or lastname like "l %"

5. If there is no Index containing the merge clause, the optimizer constructs a worksheet to store rows in the smallest table in the merge. Then, create a clustered index on the table to complete an efficient merge. The cost of this approach is the worksheet generation and the generation of the next sub-family index. This process is called reformatting. Therefore, pay attention to the size of the database tempdb in Ram or on the disk (except the select into Statement ). In addition, if these types of operations are common, putting tempdb in Ram is very good for improving performance.