Several common factors affecting application performance of SQL Server database

This article transferred from: http://blogs.msdn.com/b/apgcdsd/archive/2012/01/18/sql-server-2012-1-18.aspx

Several common factors affecting application performance of SQL Server database

Performance issues are one of the common problems that plague database users. Often someone is challenged by the ability of SQL Server to handle large data applications because of performance issues. In fact, as a business in the market for more than 20 years, out of several generations of database products, SQL Server as an enterprise-level database capabilities, is beyond doubt. In real-world applications, the volume of data reached hundreds of GB, or even the upper TB level, more than 1, 2000 concurrent connections, more than 1000 requests per second of SQL Server, is now a lot of. We also see more and more of these SQL servers at some of our major domestic customers.

So why do some SQL Servers run so strong, and many users have a database of just dozens of GB, and feel like they're not moving? Before we talk about the common causes of performance problems, let's talk about some of the important performance-related features of SQL Server. Understanding these features is essential to designing an efficient database application.

1. The data to be accessed by SQL Server must be buffered in memory

Whether it is the data to be queried or the data to be modified, SQL Server checks that the data it accesses is in memory before running the statement that the client has sent over to process the data. If it is not in memory, SQL Server first puts the page that stores the data from the disk into memory and then does the real data processing.

When the data is processed, SQL Server does not discard the data cache immediately. As long as SQL Server is not short of memory, previously accessed data pages will always be cached in SQL
The address space of the server process. This way, if the next time another user wants to access the same data record, the SQL
The server can be processed in memory immediately, without having to go to disk.

This design maximizes the reuse of memory and improves the processing speed of SQL Server. It also determines that SQL Server is a very big memory-loving application.

2. If SQL Server does not have free memory and the user needs access to the new data, SQL will select a portion of the previously cached data to be purged from memory, freeing up space to cache the new data

SQL Server does not request memory indefinitely. It calculates its maximum amount of memory based on the user's settings, as well as the number of memory in the system. If the current memory size and this limit are reached, SQL will no longer request more memory from Windows. This mechanism ensures that SQL Server and Windows, as well as other applications running on the same machine, can coexist peacefully.

When the memory of SQL Server has reached the maximum, the memory space has been cached full of various data pages, and the user has to access new, not yet cached in the memory of the data, SQL
Based on the frequency of existing data access, the server clears the oldest and least frequently accessed data from memory, freeing up space to cache new data that customers now want to access.

So the ideal state is that the data that the user wants to access is always in memory, and SQL Server never needs to go to disk to find it. This is also the best case for database performance. At this point SQL Server almost never needs to do disk reading.

If the data that the user wants to access is not in memory frequently, SQL Server will be forced to daoteng the data between memory and disk continuously, and the performance will be severely affected. At this point, you will see SQL doing disk reads frequently.

3. The organization and access of data in tables is closely related to the selection of clustered indexes

A notable feature of SQL Server is the storage of data in tables, sorted by the value of the field in which the clustered index resides. Instead, nonclustered indexes are built on top of the clustered index structure. If a table does not have a clustered index, the data is stored as a heap, in no order.

For the same amount of data, SQL Server manages a table with a clustered index that is far more effective than a table without a clustered index. In most cases, a large table must have a suitable clustered index if it wants to have good performance. There is no clustered index, only nonclustered indexes, and optimized performance is not achieved.

This is a very important feature of SQL Server.

4. Under the default transaction isolation level, read and write operations on the same record are mutually exclusive

SQL Server implements the four isolation levels of the ANSI standard. At the default isolation level of Read Committed, the read operation will request an S lock, and the modification operation will request an X lock, an S lock and an X lock mutex. So the same record cannot be read and write at the same time.

Some other database products in the industry, by default, use row versioning to implement transaction isolation, if one user modifies a record, but does not commit a transaction, and the other user reads the same record, it will let the second user read the value of the record before the first person modifies it. So read and write operations can be done at the same time.

This version-controlled isolation level, of course, is higher than SQL Server, and is less likely to block between read and write operations. However, its transaction isolation effect is different from SQL. For example, for a bank account query, assuming that the original account has 10,000 yuan, user A to start a transaction, the account of 10,000 yuan to be transferred out. When the move out of this action is not completed, User B will check the account balance. With version control, User B can get the result right away: 10,000 yuan, but the result is probably out of date. With SQL Server, User B must wait until user a transfers is complete to find the balance, but what he gets must be an up-to-date value.

These two isolation levels actually reflect the two kinds of user needs, can not say which kind of good, which kind of bad. Users who like version control this isolation level, if you want to
The same concurrency in the server, you can choose the snapshot isolation level in SQL (this function is introduced in SQL 2005). In this way, the read and write operations in SQL are not mutually exclusive.

The above features determine many of the behavioral characteristics of SQL Server. If you run on SQL Server and the application does not design well according to the characteristics described above, it is easy to encounter a variety of performance issues.

In real-world applications, there are a few common causes of SQL Server performance problems.

1. The design of the index is not optimized enough to force statements to use the execution plan of the full table scan frequently

Without good index help, SQL Server queries any one record may have to scan the entire table again. This has little impact when the database is relatively small, because SQL can keep all the data in memory, even if the full table scan is not too slow. As the amount of data increases, memory will not fit. The burden of full-scale scanning is getting heavier, and eventually it can seriously affect SQL
The overall performance of the server.

This is a very common reason why SQL Server runs more slowly.

2. Large tables do not have a clustered index, or the clustered index is based on an inappropriate data column

For tables that do not have a clustered index, management and querying can add a lot of overhead if there are tens or more records inside. One experience is that for a large table to be used frequently, build a clustered index. The data column where the index is located should be one or several repeating records that are not many columns of data.

Some other databases in the industry may not emphasize the concept of clustered indexes or use other methods to manage tables. So if you put a non-SQL
Server's database is migrated to SQL Server, be sure to recheck the index structure and adjust the indexing settings according to the characteristics of SQL Server. Otherwise, it is normal that you will not get good performance on SQL Server after migration.

3. The amount of data that users need to access frequently, much larger than the number of machines in memory

As we have said earlier, SQL
The data that the server wants to manipulate is required to be cached in memory. If the sum of the amount of data that all users of the terminal wants to access frequently is much larger than the amount of memory that SQL Server has, then SQL will have to re-page between memory and disk. This time the SQL performance is down, it may not be one or two orders of magnitude.

The size of the amount of data that users need to access frequently is usually related to several factors.

A. User-sent statement definitions

If there are good conditions in the statement, then the amount of data can be controlled. If the constraints are not strong, the amount of data increases as the number of records in the table grows. An example of this is that users are always querying this year's XXX data. At the beginning of the year, the query must be quick. The amount of data by the end of the year was 12 times times that of January. The amount of data accessed at that time is likely to be 12 times times.

B. SQL Server whether the index can be used, use seek the way to find the data instead of scanning the full table

Without a good index, even if the data the user wants to access is only a small part of the table, SQL may need to traverse the entire table. In this case, the larger the table, the more slowly the SQL runs. If the index is designed to be optimized, the amount of data access for SQL Server should not have an absolute relationship with the size of the table.

c. the nature of the application

Database applications can be divided into two main categories: OLTP type, and Data warehouse type. The former will deal with a large number of small business, such as patient registration, supermarket checkout, warehouses and other goods. Requests from users of this application are not very complex, and the amount of data to be processed is smaller each time, but the required response is fast. It is possible that 0.5 seconds of waiting will cause performance problems. So for this application, the data it accesses should be guaranteed to always be cached in memory.

Data Warehouse type of application is mainly to do the analysis and collation, often in order to produce some reports. This kind of application mainly queries mainly, will certainly access a large number of data, will appear the larger the database, the more data accessed. But the client can tolerate a certain amount of waiting time. For this application, the data it accesses is not in memory.

For OLTP-type applications, you should try to ensure that the data that users frequently want to access is cached in memory for long periods of time. Therefore, in the design of application logic and database, it is necessary to consider, control the amount of data accessed by users, establish an effective index, avoid full table scan. In the management, also have the historical data archiving mechanism, control the whole database size. If the amount of data that the user needs to access is larger than the memory, upgrading the memory is also a scenario to consider.

For application of report type, because the amount of data accessed is very large, disk paging is unavoidable. Of course, good index design can also be helpful for performance.

The situation to avoid is that the same SQL Server is running an OLTP type of application and is running the report type application from time to time. They can have a great deal of mutual interference, which can have a devastating effect on the responsiveness of OLTP applications, and it is difficult to easily resolve them by adapting the database design, or by upgrading the hardware.

4. Database applications are migrated directly from other non-SQL Server architectures

As discussed earlier, SQL Server's default transaction isolation level is read Committed, which may not be the same as other databases. In addition, many applications access the database by invoking the interface of the database control rather than invoking the SQL statement or stored procedure directly. To achieve the same goal, there are many ways to implement the program. Some methods may perform well on other databases, but to SQL
It is possible to change the approach on the server or change the driver. In addition, different database systems may have different suggestions for how the instructions are written, and the index requirements on the table are not the same. So in SQL
For good performance on the server, you must optimize the migrated applications on the SQL Server platform. Applications that are not tested, optimized, and migrated directly, often fail to perform the advantages of SQL Server and perform poorly.

Several common factors affecting application performance of SQL Server database