SQL Server database Performance tuning tips

Original: SQL Server database Performance tuning tips

The reasons for the slow query are many, and the following are common:

1. No index or index is not used;

2, I/O throughput is small, forming a bottleneck effect;

3, insufficient memory;

4, the network speed is slow;

5, the amount of data queried is too large;

6, lock or deadlock;

7, the return of unnecessary rows and columns;

8, query statement is not good, no optimization.

You can refine your query by:

Hardware / Network Aspects

1, upgrade the hardware.

2, improve the speed.

3, expand the memory of the server.

4. Increase the number of server CPUs.

5. Put data, logs, indexes on different I/O devices.

6. Separation of DB server and app server.

7, apply distributed partitioned view.

Index aspects

8, index according to the query criteria, optimize the index.

9, the index should be as small as possible, using a small number of bytes Lie Jian index good.

10. Do not Jianjian a single index (such as a gender field) on a limited number of characters.

11. The full-text index is long for the value of the query field.

12, to pay attention to the maintenance of the index, periodically rebuild the index, recompile the stored procedure.

13. If you use in or OR and so on to find that the query does not walk the index, use the display declaration to specify the index.

14. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system.

15. When using an indexed field as a condition, if the index is a federated index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used.

16, if the data volume of the temporary table needs to be indexed, then the process of creating the temporary table and indexing should be placed in a single sub-stored procedure, in order to ensure that the system can use the index of the temporary table well.

17. If a column has a null value, it will not improve performance even if the column is indexed.

SQL Statement Aspects

18, if you use like to query, simple to use the index is not, like ' a% ' using the index, like '%a ' do not use the index, like '%a% ' query, the query time and the total length of the field value is proportional, so can not be used char type, but varchar.

19. Do not return rows or columns that are not required when querying.

20, be sure to separate the function and column names. If you must use a function, create a computed column and then create an index instead.

21, not in will scan the table multiple times, using EXISTS, not exists,in,left OUTER join to replace, especially the left connection, and EXISTS than in faster, the slowest is not operation.

22, between at some point faster than in, between can quickly find the range based on the index.

23. Clauses with or can be decomposed into multiple queries, and multiple queries are concatenated through union. Their speed is only related to the use of indexes. The clauses of multiple or are not used in the index, and the form of Union is then tried to match the index.

24, in the face value of the list, will appear the most frequent values on the front, the least appears on the last side, reduce the number of judgments.

25, generally in the group BY, have the clause before you can eliminate the redundant lines, so try not to use them to do the culling line of work. Their order of execution should be optimal: the WHERE clause selects all the appropriate rows, group by is used to group statistical rows, and the HAVING clause is used to exclude redundant groupings. So group BY, have a small cost, query fast. For large data rows, group by and having are very resource-intensive. If the purpose of group by is not to include calculations, just groups, then use distinct faster.

26, try to use batch processing.

27, do not use the same function repeatedly in a sentence, wasting resources, put the results in a variable and then call faster.

28. Analysis Select Emp_name form employee where salary > 3000 If salary is a float type in this statement, the optimizer optimizes it to convert (float,3000). Since 3000 is an integer, we should use 3000.0 in programming instead of waiting for the DBMS to be transformed by the runtime. The same is true for other types.

29, note the WHERE clause, you must consider the order of the sentence, should be based on the index order, the range size to determine the order of the condition clauses, as far as possible to make the field order and index order consistent, ranging from large to small.

30, try to use exists instead of select COUNT (1) to determine if there is a record, the Count function is only used for all rows in the statistics, and count (1) is more efficient than count (*).

31, try to use ">=", Do not use ">".

32. Note The data types connected between tables to avoid connections between different types of data.

33, try to avoid the use of distinct, ORDER by, GROUP by, having, JOIN, Cumpute.

34, in the vast number of queries as far as possible to use the format conversion.

35. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

Other aspects

36. Vertical and horizontal partition table, reduce the size of the table.

37. Set automatic shrink log.

38, Periodic cleanup log.

39, for large databases do not set the database autogrow, it will reduce the performance of the server.

40, avoid the table scan.

41. If possible, do not use cursors.

42, use Profiler to track the query, get the time required to query, find out the problem of SQL. Optimizes the index with the index optimizer.

43. Use Query Analyzer to view the SQL statement's query plan and evaluate whether the analysis is an optimized SQL.

44, the calculation results are pre-calculated to put in the table, query time and then select.

45, if you want to insert a large binary value into the image column, use stored procedures, do not use the application embedded insert to insert. Because the application first converts the binary value to a string (twice times its size), the server receives the character and converts it to a binary value. Stored procedures do not have these actions.

46, minimize the use of views, its low efficiency. View operations are slower than direct table operations.

47, try to put the data processing work on the database, reduce the network overhead, such as the use of stored procedures. Stored procedures are compiled, optimized, and organized into an execution plan, and stored in a database of SQL statements, is a collection of control flow language, the speed of course fast.

48, the return value of the function is not too large, this overhead is very large. A user-defined function that executes as a cursor consumes a large amount of resources if a large result is returned with a stored procedure.

49, as far as possible to avoid repeated access to the same or several tables, especially the large number of data tables, you can consider the criteria to extract the data into the temporary table, and then do the connection.

50, to try to avoid the cursor in the loop of the table to join the operation.

51. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

52. In some cases where cursors must be used, consider moving the qualifying data rows into a temporary table, and then manipulate the temporary table definition cursors, which can significantly improve performance.

Experience shows that the biggest improvement in SQL Server performance is due to logical database design, index design, and query design. Conversely, the biggest performance problems are often caused by deficiencies in these same areas. In fact, the essence of SQL optimization is that the result is correct, with the optimizer can recognize the statement, fully use the index, reduce the number of I/O table scan, as far as possible to avoid the occurrence of table search.

SQL Server database Performance tuning tips