Dissecting SQL Server execution plans

1. Common analysis statements
DBCC freeproccache -- clear Cache
DBCC dropcleanbuffers -- clear the Buffer Zone

-- Get performance information
Set statistics profile on

Set statistics Io on

Set statistics time on

Use the set statistics XML on option to enable the XML display plan Function

-- View the cached SQL
Select [Cp]. [refcounts]
, [Cp]. [usecounts]
, [Cp]. [objtype]
, [St]. [dbid]
, [St]. [objectid]
, [St]. [text]
, [QP]. [query_plan]
From SYS. dm_exec_cached_plans CP
Cross apply SYS. dm_exec_ SQL _text (CP. plan_handle) ST
Cross apply SYS. dm_exec_query_plan (CP. plan_handle) QP;

2. When is the actual plan different from the execution plan?
1. Statistical expiration
2. Concurrency required

3. The aggregate index contains all column data. Therefore, even if all columns are returned, no [nested loop] or [key search] exists]

Sort: sqlserver uses memory for sorting first. If memory is insufficient, tempdb is enabled for sorting. A warning is reported. If a warning is reported, improve the memory size orProgramItself.

 

4. CTE public table expressions can be used as Recursion

 

5. Materialized View/index view (view storage data, tempdb, automatically updated upon insertion)
1. When creating an index view, you must specify the structure of the table.
2. When creating the SELECT statement for the index view, * is not used. The specific column name must be specified.
3. Duplicate column names cannot exist in the SELECT statement for creating the index view. This is not an example.
4. Only unique clustered indexes can be created for the index view.