Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state.

The below query, without any additional WHERE clauses is failing with the following SQL error:

Msg 954, Level 14, State 1, Line 1 The database "DatabaseName" cannot be opened. It is acting as a mirror database.

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs 
CROSS APPLY     sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

I suspect what happens is the sys.dm_exec_query_plan function is first trying to parse all the plan handles from the cache, but fails on cached objects for a mirrored database.

Does anyone know if there are any ways to get around this, T-SQL wise?

Off course I could execute DBCC FREEPROCCACHE to clear the cache, however I'm hoping for other solutions. I'm a bit amazed this function is not discarding any objects from mirrored databases while trying to parse the plans.

share|improve this question
    
Does it fail if you only run SELECT * FROM sys.dm_exec_query_stats AS deqs ? –  Max Vernon Mar 21 '13 at 14:36
add comment

migrated from stackoverflow.com Mar 21 '13 at 2:20

This question came from our site for professional and enthusiast programmers.

2 Answers

The sys.dm_exec_query_plan has a column returned for dbid, you could just filter on that for the database that is being mirrored.

Or, you could get fancy and nest a sub-query that filters for any dbid that is in a mirrored state by going out to the sys.database_mirroring DMV.

share|improve this answer
add comment

Building on SQLRockstar's suggestion to add the mirroring dmv, you get the following:

SELECT  d.name, dbm.mirroring_role_desc, deqs.*, CAST(deqp.query_plan as xml) AS QueryPlan
FROM    sys.dm_exec_query_stats AS deqs 
CROSS APPLY     sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
INNER JOIN sys.databases d on deqp.dbid = d.database_id
INNER JOIN sys.database_mirroring dbm ON d.database_id = dbm.database_id
WHERE dbm.mirroring_role_desc = 'PRINCIPAL';

This takes the same amount of time on my mirror machine with and without the WHERE clause, leading me to suspect the dm_exec_query_plan DMV is getting the query plans for everything prior to joining on database_id, which means your problem won't be solved by this query. Unfortunately, my mirror server has been restarted since the last failover so I don't see the issue on my server.

CAST(deqp.query_plan as xml) allows you to simply click the plan in question to see it in SSMS's graphical plan view.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.