Best Online Tutorials

SQL Tutorial

Free SQL Tutorial

SQL Interview Questions

SQL Practice Questions

SQL Tips & Tricks

SQL Errors

SQL Books

Angular Tutorial

Free Angular Tutorial

Angular Interview Questions

Angular Practice Questions

Angular Tips & Tricks

Angular Errors

Angular Books

Node.js Tutorial

Free Node.js Tutorial

Node.js Interview Questions

Node.js Practice Questions

Node.js Tips & Tricks

Node.js Errors

Node.js Books

React.js Tutorial

Free React.js Tutorial

React.js Interview Questions

React.js Practice Questions

React.js Tips & Tricks

React.js Errors

React.js Books

SSIS Tutorial

Free SSIS Tutorial

SSIS Interview Questions

SSIS Practice Questions

SSIS Tips & Tricks

SSIS Errors

SSIS Books

SEO Tutorial

Free SEO Tutorial

SEO Interview Questions

SEO Practice Questions

SEO Tips & Tricks

SEO Errors

SEO Books

Javascript Tutorial

Free Javascript Tutorial

Javascript Interview Questions

Javascript Practice Questions

Javascript Tips & Tricks

Javascript Errors

Javascript Books

MongoDB Tutorial

Free MongoDB Tutorial

MongoDB Interview Questions

MongoDB Practice Questions

MongoDB Tips & Tricks

MongoDB Errors

MongoDB Books

Next.js Tutorial

Free Next.js Tutorial

Next.js Interview Questions

Next.js Practice Questions

Next.js Tips & Tricks

Next.js Errors

Next.js Books

Ways to get Execution Plan of Running SQL Query 

Pooja Goel  |   Dec 4, 2019  |  Views: 655
pictures

Description:

In this article we will discuss on the Execution Plan of Running SQL Query 


But there are different ways for questioning the same topic, which I have listed below, all having the same answer which we are going to discuss.


  • Ways to get Execution Plan of Running SQL Query
  • How to see execution plan in SQL server management studio
  • How to optimize query using execution plan in SQL  server
  • SQL server get execution plan for stored procedure
  • Execution plan in SQL server step by step
  • SQL server execution plan analysis
  • How to check execution plan in SQL server management studio
  • How to find bad execution plan in SQL server
  • How to get query execution plan


There are many ways to get execution plan of Running SQL Query.


1)  Using SQL Server Management studio (SSMS) 
2)  Using Dynamic Management Views (DMVs) 


We are going to discuss these two ways here with the example and screenshots.


 

Using SQL Server Management studio (SSMS) :



In SSMS There are two ways to get the execution plan:


  1. Estimated Execution Plan 
  2. Actual Execution Plan

1. Estimated Execution Plan :

This contains an approximate execution plan.

Steps to Create Estimated Execution Plan:

 

Step 1:   Open SSMS and connect Database.


Example


There is a connected database “TESTDB”


 




Step 2:  Open Query window to write SQL query in SSMS.


Example :


 

Step3:  Click on “Estimated Execution Plan” Symbol (Red marked in below screen) or press CTRL+L.





Step 4:  Click on Table Scan / Index Scan to view the details



 



2.  Actual Execution Plan :


This contains actual query execution plan


Steps to Create Actual Execution Plan:


 

Step 1:  Open SSMS and connect Database.


Example : 

There is a connected database “TESTDB".


 



Step 2:  Open Query window to write SQL query in SSMS.


Example :


 

Step 3:  Click on Actual Execution Plan Symbol (Red marked in below screen) or press CTRL+M.


Step 4:  Click on Table Scan / Index Scan to view the details



 



Using Dynamic Management Views (DMVs) :

 

By using DMVs, You can view all execution plans of running query.Below query is used to find execution plan:

 

SELECT 
cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
QP.query_plan as [Query Plan], 
ST.text AS [Query Text]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) ST

 


Once You clicked on link , Execution plan will be appear.

 

 


Comments:
Content Contributor
Pooja Goel

Pooja Goel

Sr. Software developer at Damco Solutions Pvt. Ltd.  |   Noida, Uttar Pradesh, India

I am a sr. software developer working on technologies like SQL, SSIS, SSRS, DB2,Tableau, ASP.NET having 7 years of experience.


Read related articles

SQL Sever-Disk Usage Reports

Pooja Goel  |   Jun 20, 2020  |  Views: 517

How to optimize the Query in SQL SERVER

Pooja Goel  |   Jun 16, 2020  |  Views: 410

SQL Server - ORDER by using @Parameter

Pooja Goel  |   Jun 13, 2020  |  Views: 321

What is ACID fundamental

Prashant Surya  |   Mar 18, 2020  |  Views: 163

What is SQL Injection

Prashant Surya  |   Mar 18, 2020  |  Views: 263

Best website to learn SQL for free

Prashant Surya  |   Jan 24, 2020  |  Views: 188

SQL Tutorials Online

Prashant Surya  |   Jan 24, 2020  |  Views: 234

Top 10 SQL tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 229

SQL Tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 197

SQL Tutorials

Prashant Surya  |   Jan 24, 2020  |  Views: 223

Best SQL Tutorial

Prashant Surya  |   Jan 24, 2020  |  Views: 243

Advanced SQL Tutorial Topics - DritalConnect

Prashant Surya  |   Dec 21, 2019  |  Views: 345

Ways to get Execution Plan of Running SQL Query

Pooja Goel  |   Dec 4, 2019  |  Views: 656

Best SQL book for beginners - SQL Tutorial

Pooja Goel  |   Nov 23, 2019  |  Views: 319

Top 30 SQL Interview practice questions

Prashant Surya  |   Nov 23, 2019  |  Views: 431

Best website to learn sql tutorial online

Prashant Surya  |   Nov 22, 2019  |  Views: 230

SQL tutorials - DritalConnect

Prashant Surya  |   Nov 20, 2019  |  Views: 289

Performance tuning in SQL Server

Pooja Goel  |   Oct 19, 2019  |  Views: 1241

Ways to call SSIS Package in CA Workload

Abhishek Roy  |   Oct 9, 2019  |  Views: 569

Ways to Improve SQL Query Performance

Prashant Surya  |   Oct 7, 2019  |  Views: 1188

Want to sell your software ?-

If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.

  • Sell ERP
  • Sell Website Clones
  • Sell App Clones
  • Sell Readymade Portals
  • Sell Readymade Websites
  • Sell Mobile Apps
  • Sell Window Apps

Clients will find it using our advanced search filter and will contact you directly.

No any charge for the product lsiting.

Do you own a Company ?+

Property.sale

Buy or Sell Commercial Property+

Are you a freelancer ?+

Looking for a job ?+

Are you a Service Provider ?+

Are you a blogger ?+

Do you own an IT Institute ?+



Property.sale