[sql] How do I obtain a Query Execution Plan in SQL Server?

Beside the methods described in previous answers, you can also use a free execution plan viewer and query optimization tool ApexSQL Plan (which I’ve recently bumped into).

You can install and integrate ApexSQL Plan into SQL Server Management Studio, so execution plans can be viewed from SSMS directly.

Viewing Estimated execution plans in ApexSQL Plan

  1. Click the New Query button in SSMS and paste the query text in the query text window. Right click and select the “Display Estimated Execution Plan” option from the context menu.

New Query button in SSMS

  1. The execution plan diagrams will be shown the Execution Plan tab in the results section. Next right-click the execution plan and in the context menu select the “Open in ApexSQL Plan” option.

Execution Plan

  1. The Estimated execution plan will be opened in ApexSQL Plan and it can be analyzed for query optimization.

Estimated execution plan

Viewing Actual execution plans in ApexSQL Plan

To view the Actual execution plan of a query, continue from the 2nd step mentioned previously, but now, once the Estimated plan is shown, click the “Actual” button from the main ribbon bar in ApexSQL Plan.

click the “Actual” button from the main ribbon bar

Once the “Actual” button is clicked, the Actual execution plan will be shown with detailed preview of the cost parameters along with other execution plan data.

Actual execution plan

More information about viewing execution plans can be found by following this link.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to performance

Why is 2 * (i * i) faster than 2 * i * i in Java? What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism? How to check if a key exists in Json Object and get its value Why does C++ code for testing the Collatz conjecture run faster than hand-written assembly? Most efficient way to map function over numpy array The most efficient way to remove first N elements in a list? Fastest way to get the first n elements of a List into an Array Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? pandas loc vs. iloc vs. at vs. iat? Android Recyclerview vs ListView with Viewholder

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to sql-execution-plan

How do I view the Explain Plan in Oracle Sql developer? How do I obtain a Query Execution Plan in SQL Server? Understanding the results of Execute Explain Plan in Oracle SQL Developer Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"