Common methods for SQL Server tuning

Speaking of the tuning of SQL Server, I think everyone would like to know this knowledge. I am also exploring the way, we have any good ideas, welcome to discuss and study. Win the people's long, can avoid weaknesses.

This article is mainly from the programmer's SQL code, if you want to read, you can download the Internet to read (of course, the best way to buy the author's book).

On the tuning of the program, there are hardware-related knowledge, but also related to software knowledge. But I am just a software aspect of it man, so just record software content.

In fact, in terms of SQL Server or other databases, some of the optimization methods are consistent. For example, the conventional approach has the following several ways:

Create the necessary indexes

University Reading has heard of the index inside the database, has not been to delve into, but also in the ignorance of the fearless in more than four years of development career, would like to be ashamed of very, today is fortunate to understand, a sense of life wide open and cheerful general. Index, not only in the database, as we write code does not touch the array also has an index. The index is already sorted in a certain way, and then we go through the index position to locate it. When it comes to SQL Server indexing, it's important to talk about two concepts. are clustered index and nonclustered index respectively.

1, clustered index: The way the index is stored in the same way as the physical storage of the content

2. Non-clustered index: The way the index is stored is inconsistent with the order of the physical storage of the content

Here's a brief description of the difference. The simplest example is the Chinese dictionary of the way, for a Chinese dictionary, its physical storage order is already fixed, is through the phonetic order of the good, which means that the Chinese dictionary is the physical storage of the way to store by pinyin. For example, the index established by pinyin is the clustered index. WHY? Because the storage of indexes is consistent with the physical storage of Chinese dictionaries, which is the classification mentioned above. So what is a nonclustered index, such as a Chinese dictionary where the Radicals are an index, then the index of the radicals is nonclustered. WHY? Because it is inconsistent with the original physical storage methods of Chinese dictionaries. When you create a clustered index, you need each table to have only one clustered index, because the data in the table can have only one physical order, but not a clustered index to create multiple.

Note: Because indexes need to occupy a certain amount of storage space, and indexes can reduce the speed of data inserts, updates, and deletes, you should create only the necessary indexes, typically in the retrieved fields. For deletions, indexing can cause fragmentation problems. Because when we delete a record, the corresponding index is not deleted. Creates an index that is idle, and once the unused index is fragmented, it can affect the efficiency of the query. The system's garbage fragments are the same, and need to be cleared regularly. For indexes, discovering that using index searches is slow, you need to rebuild the index periodically, rebuilding the index deletes the previously created index, and then creates a new index, and the mainstream database management system provides the ability to rebuild the index.

Using precompiled queries

Many people use stored procedures to compile SQL statements beforehand to achieve optimization. Some projects execute SQL statements dynamically based on user input, either way, it is performed in a parameterized manner, not only to avoid SQL injection exploits, but to precompile the most important databases for these parameterized SQL, so that the DBMS will check for the SQL statement the first time it is executed. Ask for optimizations and perform precompilation so that you can use the precompiled results directly when you execute the SQL later, greatly increasing the speed of execution.

Adjust the order of joins in the WHERE clause (does this not quite understand?) ）

The DBMS generally uses a bottom-up sequence to parse the WHERE clause, according to which the table joins are best written before other where conditions, which can filter out the maximum number of records.

For example, the following SQL statement has poor performance:

SELECT * from
t_person
WHERE fsalary > 50000 and
fposition= ' MANAGER '
< (select COUNT (*) from T_manager
WHERE fmanagerid=2);

We put the criteria of the subquery to the front, and the following SQL statement performs better:

SELECT * from
t_person
where
< (select COUNT (*) from T_manager
where fmanagerid=2) and
Fsalary & Gt 50000 and
fposition= ' MANAGER ';

Avoid using ' * ' in SELECT statements

For the SELECT * from table, I think a lot of people are going to query. WHY? On the one hand because the system's data level is still relatively low, and secondly also easy to map. But as the project's data volume is soaring and the performance of the system drops rapidly, every way of optimization needs to be valued. As in the query, if you don't need to retrieve all the columns, try to specify the columns of the query. This can effectively reduce the network load and server resource consumption. Do not use SELECT * even if you do need to retrieve all the columns, because this is a very inefficient method, and the DBMS will convert the * to all column names in the process of parsing, which means more time will be spent. In the SQL Server Management Studio tool query diagram easy, the amount of data is small I think it is OK:, but at least in the code is not recommended to do so.

Multiple SQL statements are compressed into one SQL to execute

For execution of SQL statements, each time to establish a network connection, check the permissions, the query optimization of SQL statements, send execution results, this process is very time-consuming, so should try to avoid too much execution of SQL statements, can be compressed to a sentence of SQL execution of the statement should not be executed with multiple.

Replace a HAVING clause with a WHERE clause

Avoid using the HAVING clause because having only filters the result set after retrieving all records. If you can limit the number of records through the WHERE clause, you can reduce the overhead. The conditions in the having are generally used to filter the aggregate functions, and in addition, the conditions should be written in the WHERE clause.