SQL Server execution plan and index optimization basics

First, how to open the execution plan?

Open "Include Actual execution Plan" (shortcut key: ctrl+m) in Microsoft SQL Server Management Studio, execute SQL statement to see actual execution plan (graphic format, very intuitive), this is undoubtedly the most friendly way to consult.

Second, how to read the actual implementation plan?

After the execution of the complex script, see the execution plan, many people are directly ignorant of the circle, what is the meaning of these texts, and how to optimize it?

The following is a list of common execution plan actions:

Table Scan (full table Scan): If your table data volume is very small, the tables scan is OK, and the performance may be better than other scanning methods, if it is a large table, then you have to optimize the index. Large data volume, full table scan, performance consumption is very obvious.

Clustered Index Scan (clustered index Scan): in general, query statements and indexes should also be optimized at this time. A clustered index scan may occur because there are too many rows or columns to return data, or there is no explicit where condition to trigger the index. For such scans, you should consider creating appropriate indexes, adding stricter return column qualifications, and narrowing the data range of the query to control the number of rows returned.

Index Seek: performance is better at this time, indicating that nonclustered index lookups are used.

Clustered Index Seek (clustered index lookup): use a clustered index (primary key) lookup, which is actually the fastest type of index lookup that SQL Server can do.

Bookmark Lookup : You should also consider optimization. A nonclustered index cannot be used in a select, JOIN, where field to satisfy a lookup, and the query optimizer has to use an additional clustered index to find a field that satisfies the query's requirements, which may cause a bookmark lookup, but the query optimizer may also use the clustered index Scan to replace the bookmark lookup. Another possible reason for bookmark lookups is the SELECT * from ..., so it is not recommended to use SELECT * in any case.

Stream Aggregate (Stream aggregation): when an aggregate function of SQL Server is used, a stream aggregation operation is generated. such as: Count,max,min,avg,distinct,sum and so on.

Compute scalar (computed scalar): This action is caused when the count is counted;

Sort (sort): When you sort by using a field that is not indexed, you might get a sort operation, and you should consider whether you want the sort operation or sort the fields with other indexes that you create. If this does not change, we recommend that you add an index to the sort field.

Iii. Common Index Optimization rules

1, All index optimization depends on the size of the data, the amount of data is very small, can not consider the creation of indexes, the creation of indexes caused performance degradation;

2, frequently query, sort or use the condition field, we recommend using index optimization, such as: when there is a self-increment primary key, if the use of the creation time sorting, optimize to use the primary key to sort;

3, The expression in the where condition is placed as far as possible after the operator. Common conditional operators are: =, >, <,! =, between and etc.;

Example: DATEPART (year,createtime) >=2017 should be optimized for:createtime> ' 2017-01-01 00:00:00 '; LEN (paramvalue) =0 should be optimized to: 0=len ( Paramvalue);

4, the existence of a higher value repetition of the field, is not recommended to create an index, such as: status identification column;

5, often combined with multiple criteria fields, a composite index should be created;

6, reduce the use of like, such as the beginning of the use of wildcards (such as the "%a%") will not use the index query;

7, large characters prohibit the creation of indexes;

8, cannot misuse index, must be created and maintained according to actual need. Indexes can cause disk and data maintenance overhead, knowing that the server's hard disk is very valuable.

Attached: Fun system Query Script

1, querying a database object referencing a character, suitable for finding stored procedures, views, etc. that use a field;

SELECT So.name, Sc.text from Dbo.syscomments SC, dbo.sysobjects so

WHERE sc.id = so.id and sc.text like '%platvalue% '

--and so.xtype = ' P '--xtype represents the object type, p is the stored procedure

By name

2. Query the table structure of a table

SELECT Sc.name, st.name as [DataType], Sc.prec

From Syscolumns SC

INNER JOIN systypes st on st.xusertype = Sc.xusertype

WHERE sc.id = object_id (' Platparam ')

SQL Server execution plan and index optimization basics