What is the usage of Execution Plan in SQL Server?

Question

What is the usage of Execution Plan in SQL Server? When can these plans help me?

Answer 1

When you need to perform performance profiling on a specific query.

Have a look at SQL Server Query Execution Plan Analysis

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.

Answer 2

It's helpful in identifying where bottlenecks are occurring in long running queries. You can make some quite impressive performance improvements simply by knowing how the server executes your complex query.

If I remember correctly it also identifies good candidates for indexing which is another way to increase performance.

Answer 3

These plans describe how SQL Server goes about executing your query. It's the result of a cost-based algorithm by the SQL Server query optimiser, which comes up with a plan for how to get to the end result in the expected best possible way.

It's useful because it will show you where time is being spent in the query, whether indexes are being used or not, what type of process is being done on those indexes (scan, seek) etc.

So if you have a poorly performing query, the execution plan will highlight what the costliest parts are and allow you to see what needs optimising (e.g. may be a missing index, may be an inefficiently written query resulting in an index scan instead of a seek).

Answer 4

When your queries all run fast, all is good in the world and execution plans don't really matter that much. However, when something is running slow, they are very important. They are primarily used to help tune (speed up) slow SQL. Without execution plans, you'd just be guessing at what to change to make your SQL go faster.

Here is the simplest way they can help you. Take a slow query and do the following in a SQL Server Management Studio query window:

1) run the command:

SET SHOWPLAN_ALL ON 

2) run your slow query

3) your query will not run, but the execution plan will be returned.

4) look through the PhysicalOp column output for the word SCAN within any text in this column, this is usually the part of the query that is causing the slowdown. Analyze your joins and index usage in regards to this row of the output and if you can eliminate the scan, you will usually improve the query speed.

There are may useful columns (TotalSubTreeCost, etc) in the output, you will become familiar with them as you learn how to read execution plans and tune your slow queries.