2

I recently asked a question about exporting query plans (here Export Query Plans) and got some great answers.

However, I did run into one issue, during the process of interrogating SQL Server to obtain the plans, the process fails if there are any offline databases. Simple question, to which I suspect the answer is complex. How do I have the interrogation ignore the offline databases?

Thank you in advance for your assistance!

Community
  • 1
pshore73
  • 427
  • 3
  • 9
  • 1
    Why would you care about db status when interrogating the query cache? – Remus Rusanu Sep 15 '15 at 13:38
  • One query can touch multiple databases. In order to know if any of the objects are offline, you would need to parse the plan/query/etc to check all of the databases in the plan. – Brent Ozar Sep 15 '15 at 13:56
  • Remus, because the interrogation fails if a DB is offline. – pshore73 Sep 15 '15 at 13:58
  • Brent, I keep forgetting that is a truism in SQL, since so much of what I am dealing with are databases that are not "cross queried". Creating the process to exam the plan seems like a large task to take on sometime after now. :) – pshore73 Sep 15 '15 at 13:59
  • @pshore73 that does not answer my question. The interrogation fails because is incorrect. – Remus Rusanu Sep 15 '15 at 14:09
  • @Remus = when a database goes offline, the cache is not immediately cleared of those plans. I blogged about this behavior in relation to a database mirroring failover: http://www.sqlsoldier.com/wp/sqlserver/doesamirroringfailovercleartheprocedurecache – Robert L Davis Sep 15 '15 at 14:32
  • The answer is you filter sys.dm_exec_query_plan on the dbid column to only databases that are online. Join to sys.databases if you need to. – Robert L Davis Sep 15 '15 at 14:33

1 Answers1

1

Did you try:

get-sqldatabase -ServerInstance yourserver | Where-Object status -eq 'Normal'
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
ALZDBA
  • 26
  • 1
  • Thank you, I did not know that option existed in PowerShell. However, as Brent noted, with the potential of queries hitting multiple DBs, the status check needs be accompanied by some parsing of the query plan itself. – pshore73 Sep 15 '15 at 14:01