She can dig it!
D Sharon Pruitt

The last post in the plan cache series discussed the children for the Statements element.  As I noted in that post, there are five possible children.  This post will focus on StmtUseDb.  Or as little children sometimes say “Statement Use Database”

As the name implies, this element in the SHOWPLAN XML details when the database context has changed.  In all reality, I haven’t found much use for this element but I’m going to dig into it some as part of the build up to better content.

If you are curious about this element – then read on.  If not, come back tomorrow for other elements.  This will talk about the element and the little value it has and it’s not very exciting.

StmtUseDb Element

The StmtUseDb element will appear in an execution plan whenever a USE <Database> statement appears.  Take the following statements for instance:

USE AdventureWorks

SELECT * FROM Sales.SalesOrderHeader
GO

Execute the statement above and there be some rows that are returned, provided you have the AdventureWorks database.  We don’t care about that, though.  Jump ahead and run this next statement:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.query_plan.exist('//StmtUseDb') =1

In this query, we are using the exist() method to return all plans that have the StmtUseDb element.  The output of the second query provides a list of all of the plans in the cache that have a USE database statement.  Clicking on the execution plan link results in the following execution plan:

StmtUseDb Details

In the last demo, we went through finding plans that have this element.  There are a few details in this element that can be extracted.  For instance, if you wanted to know the database that the USE statement was switching to you could use the following query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qp.query_plan
,c.value('@StatementText', 'varchar(255)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,c.value('@Database', 'varchar(255)') AS [Database]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtUseDb') t(c)
WHERE qp.query_plan.exist('//StmtUseDb') =1

The useful attributes in the StmtUseDb element are:

• StatementText: The T-SQL statement that was executed
• StatementType: The type of T-SQL statement.  This will be USE DATABASE every time.
• Database: The name of that database that the batch switched to.

Not much there, but a little information on what’s inside.

The StmtUseDb Rub

While it might seem that this element could be used to see what databases that the execution plan is running against, it only appears when the USE <database> statement is in the batch.  So there’s one ding against querying for it.

Next, a GO statement between the USE <database> statement and the rest of the queries will place the USE statement in a separate batch that will not go into the plan cache.  Try it – I’ll skip a demo on that for brevity.

Lastly, if you happen to have a source of execution plans outside of the plan cache.  For instance if you have a few actual execution plans from Management Studio or SQ Profiler – then the USE statement also will not appear.  Even if they are in the same batch.

Below is the plan you would see.  As it shows, now USE statement.

So like the Joes, you now know about StmtUseDb.