SQL Server Performance Optimization

Source code files

1, what is a performance issue?

If the existing resources do not reach the maximum throughput, the system cannot meet the reasonable expected performance, then it can be defined as a performance problem. Performance metrics include: response time, throughput, scalability.

2, a preliminary study of optimization

2.1 Optimization theory

2 performance issues are commonly encountered:

1), a function is slow, or suddenly slow, such as a stored procedure, query and so on.

2), the whole system is very slow.

In the first case, the object is relatively clear, so it is easier to handle. In most cases, only the implementation plan needs to be studied to solve most of the problems. By changing the query, adjusting the table structure (index, etc.). Can play a significant effect.

In the second case, the object is ambiguous and the bottleneck needs to be positioned first. You can perform a summary analysis by using performance counters, SQL Profiler/sql Trace, extendedevents (SQL Server 2008). Then locate the bottleneck.

2.2 Defining the problem

2.2.2 By classifying performance data. After the processing is completed, the deep analysis and summary are summarized.

1), analyze the wait at the database instance level. Use performance counters, SQL Profiler for monitoring.

6), optimize index/query: A, improve the code: use a CTE instead of a cursor query.

Key words:
Sarg:sarg (searchable arguments operation, because it usually refers to a particular match, a worthy range of matches, or a connection of more than two conditions.

1, wildcard% opening of the string causes the index to be unusable. Name like '% Zhang ', does not belong to Sarg.

2,or will cause a full table scan. Name= ' Zhang San ' or price >5000 is not in accordance with SARG.

3,in equivalent to or

4, it is usually more efficient to use union than to use or.

5, be sure to avoid the Select useless field.

6, by paging, etc.

Sarg:http://baike.baidu.com/link?url=7zxaydrpukcbltpbt45lmprjymf5ls89cdodskxa08xbt3sppuivuf_ 0caqevucbzupjqkqtxpmseekg7kgf3q

SQL Server Performance Optimization