ZzSQL Server performance optimization

Database Design

To optimize SQL Server performance, you must first have a good database design solution. In practice, many SQL Server solutions tend to suffer poor performance due to poor database design. To achieve a good database design, you must consider these issues:

1. Logic database standardization

In general, the logical database design meets the first three standards of standardization:

1st specification: No repeated group or multi-value columns;

2nd specification: each non-Keyword segment must depend on the primary keyword and cannot depend on some components of a combined primary keyword;

3rd specification: one non-Keyword segment cannot depend on another non-Keyword segment.

Databases that comply with these rules are designed to generate fewer columns and more tables, which reduces data redundancy and data storage pages.

2. Generate a physical database

To correctly select basic physical implementation policies, you must understand and make good use of the Database Access format and operating features of hardware resources, especially memory and disk subsystem I/o. The following are some common tips:

The data types related to each table column should reflect the minimum storage space required for data, especially for indexed columns. For example, if the smallint type can be used, the integer type is not used, so that the index field can be read more quickly and more data rows can be placed on a data page, therefore, I/o operations are reduced.

Put a table on a physical device, and then put its clustered index on a different physical device through the SQL Server segment, which improves performance. Especially when the system uses multiple smart disk controllers and data separation technologies, the benefits of doing so are even more obvious.

Use the SQL Server segment to split a frequently used large table and place it on multiple database devices with separate smart disk controllers. This can also improve performance. Because multiple heads are searching, data separation can also improve performance.

Using SQL Server segments to store text or image column data on a separate physical device improves performance. A dedicated intelligent controller can further improve performance.

Application System Design

In the design of application systems, the following points should be taken into account:

1. Use indexes reasonably

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 SQL Server performance 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.

2. Avoid or simplify sorting

Duplicate sorting of large tables should be simplified or avoided as much as possible. When indexes can be used to automatically generate outputs in the appropriate order, the optimizer avoids the sorting step. In order to avoid unnecessary sorting, We need to correctly add indexes and reasonably merge database tables (although it may affect table standardization sometimes, it is worthwhile to Improve the efficiency ). If sorting is unavoidable, you should try to simplify it, such as narrowing the column range of sorting.

3. Eliminates sequential access to data in large table rows

In nested queries, sequential table access may have a fatal impact on query efficiency. Sometimes we can use Union sets to avoid sequential access. Although indexes may exist on all check columns, some forms of where clauses force the optimizer to use sequential access. This should also be noted.

4. Avoid related subqueries

If a column appears in both the primary query and the where clause, it is likely that after the column value in the primary query changes, the subquery must be re-queried. The more nested query layers, the lower the efficiency. Therefore, avoid subqueries as much as possible. If the subquery is unavoidable, filter as many rows as possible in the subquery.

5. Avoid difficult Regular Expressions

Mathes and like keywords support wildcard matching, but such matching is particularly time-consuming. For example, select * from customer where zipcode like "98 _", even if an index has been created on the zipcode field, sequential scanning is also used in this case. If you change the statement to select * from customer where zipcode> "98000", the query will be executed using the index, which will obviously increase the speed.

6. Use temporary tables to accelerate queries

Sort a subset of a table and create a temporary table, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. The temporary table has fewer rows than the primary table, and the physical order is the required order, which reduces disk I/o, so the query workload can be greatly reduced. Note that the modification to the master table is not reflected after the temporary table is created. Do not lose data when the data in the master table is frequently modified.

7. Try not to use the select into statement.
The select inot statement will lock the table and prevent other users from accessing the table.
8. Force the query optimizer to use an index if necessary

SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8, 32, 45)

Changed:

SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8, 32, 45)

The query optimizer forcibly uses the index IX_ProcessID to execute the query.

 

Performance Optimization for SQL Server related to operating systems

The performance of the operating system directly affects the database usage performance. If the operating system has problems, such as cpu overload, over-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 the 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 driver is overloaded, I/O operations on these disk drives will be queued up, 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.

Of course, there are many factors that affect performance, and the applications are different. It is very difficult to find a general SQL server performance optimization solution, it can only be adjusted according to the running conditions during system development and maintenance.