Executing Plan in SQL Server

Observing performance tuning, reducing or boosting the cost are displayed by executing plan.It allows us to see big picture how system works in background. Primarily it is needed to understand how a query is executed by server.Steps:

1. Parse - parsing whole statement whether it has mistake in it or not.
2. Resolve - getting info about tables,views so on.
3. Optimize - deciding how to execute the query more efficiently
4. Compile - creating executing plan about whole process
5. Execution - final point to realize the execution plan

All implemented execution plans are stored in Plan Cache, to prevent next queries' execution plans to reduce the performance by reading related plans throughout disc.

Big data or complex queries might be much more difficult or more costly to execute,so that SQL Server gives us Estimated Execution Plan command to get informed about the query's plan in advance.Just click on the button and see result.Besides, Actual Execution plan is working after executing a query.Click on the button and see result after displaying the graphical view.

1.Estimated EP button; 2.Actual EP button

Management studio presents the result by 2 formats: XML and Graphics related. Before SQ: Server 2005 Text related format was exist, however after deprecating stated format Microsoft carried its contents to XML.Most used format is graphics related one, it shows results as diagram and easy to understand.Next one is called by SET statements.And includes also estimated and actual plans.

Estimated plans calculating according to statistical values and needed to be updated.Does not mean estimated plans' result should be same with actual one, changes according to filters,complexity of queries,join and index and so on. SET statements to get execution plans:

In result set some elements occur and each one impresses system's cost.Common execution plan elements:

• Table scans - looking for whole table, so it has much more cost on server
• Clustered index scans - scans the B-tree throughout root and leaf
• Clustered index seeks - just identifying required value's location and seeking for it,has fewer cost on server in comparison with index scans
• Nested loops - appears while join processes mainly
• RID Lookups - applying on heap structure, that has not any index,or non-clustered.Scan is realizing by row related. Row IDentified Lookup is more costly.
• Key Lookup - Another row-related lookup, not as costly as RID, implementing on clustered indexes
• Merge joins & Hash Matches - Both merge joins and hash matches are seen in join operations, however in merge joins simple and accurate, in latter one more complex and inaccurate operations are executed
• Aggregations - Like merge joins and hash matches it has two forms:stream and hash. For accurate and simple aggregation operations stream,in complex and inaccurate operations hash one is displayed
• Filter - is displaying while WHERE and HAVING clauses are presented
• Sort - is displaying while ORDER BY clause is presented.
• Data Manipulation Statements - is displaying while INSERT,DELETE and UPDATE clauses are presented

Some Dynamic Management Views are helpful in getting informed about execution plans:

DMVs Related to Execution Plans

Broad explanation about Execution Plans is in my YouTube video (Turkish only).