dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted April 2, 2018

WEBINAR:
On-Demand

The California Consumer Privacy Act: What You Need to Know


How to Show the Execution Plan of a Running Query

By Greg Larsen

When you are trying to tune a query, you can look at the query execution plan associated with the TSQL code.  By looking at the execution plan you can  determine which steps are used to resolve the query.  But what if you just want to find out what the query plans are for only the queries that are currently running?

SQL Server first compiles the TSQL that needs to be executed and stores the query execution plan in the procedure cache.  Therefore, by looking in the procedure cache one can identify the execution plan of a given query.  To see all the execution plans for executing queries I can run the following TSQL code:

SELECT QP.query_plan as [Query Plan], 
       ST.text AS [Query Text]
FROM sys.dm_exec_requests AS R
   CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(R.plan_handle) ST;

The above code identified the commands that are running by using the sys.dm_exec_requests DMV.  I then use the CROSS APPLY operator to join the plan_handle of the executing queries with query plans in the procedure cache by using the sys.dm_exec_query_plan DMF.  Finally I use another CROSS APPLY operator to find the sql text using  the sys.dm_exec_sql_text DMF .   When I run the code above on my instance of SQL Server it displays the “Query Plan” and “Query Text” as shown below.

 

Query Plan and Query Text
Query Plan and Query Text

By double clicking on the XML shown in the Query Plan column it will bring up the execution plan for the query that is running.

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.