Systems Engineering and RDBMS

SYS.DM_EXEC_TEXT_QUERY_PLAN

Posted by decipherinfosys on October 16, 2007

In our previous blog posts, we have covered about some of the new features introduced in service pack 2 (SP2) of MS SQLServer 2005, namely VARDECIMAL (new data type) and log-on trigger. In this blog post, we will cover yet another enhancement available in SP2.

In SP2 Microsoft introduced new table valued function SYS.DM_EXEC_TEST_QUERY_PLAN. This table valued function returns query plan in text format for a given batch or for a specific SQL in batch. It is pretty much similar to table valued function SYS.DM_EXEC_QUERY_PLAN but has two major differences.

• It returns output in the text format.
• One can specify an individual query statement within the batch.

Function takes three arguments:

• Plan handle is the first argument, which can be obtained either from SYS.DM_EXEC_REQUESTS or from SYS.DM_EXEC_CACHED_PLANS dynamic management view.
• Statement_start_offset: this could be either starting poistion in bytes for the specific query within the batch or 0 (Default). We can even define it as a DEFAULT (0). When 0 is defined, it indicates starting of the batch.
• Statement_end_offset: Similar to start position, this could be either end position of the query within the batch or -1 (Defaule value) or DEFAULT (-1). -1 Indicated end of the batch.

So if function is invoked with 0 and -1 as a start and end position then, it will return query plan in text format for entire batch. If starting position is non zero, and ending position is more than -1, then it will be for a specific query within the batch. Using cross apply operator, we can even return the text plan for each row returned by dynamic management view. Following is the simple example.

SELECT query_plan
FROM sys.dm_exec_requests AS cp
CROSS APPLY SYS.DM_EXEC_TEXT_QUERY_PLAN(cp.plan_handle,DEFAULT,DEFAULT)
WHERE query_plan is not null;
GO

OR

SELECT objtype, query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY SYS.DM_EXEC_TEXT_QUERY_PLAN(cp.plan_handle,DEFAULT,DEFAULT)
WHERE query_plan is not null;
GO

User must be member of the sysadmin fixed server role or he/she should have the ‘view server state’ permission to execute the SYS.DM_EXEC_TEXT_QUERY_PLAN table valued function.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: