Export Query Plans

I know it is very easy to export a single query plan. My question is, when I create a query which returns multiple plans, such as looking at the most costly queries on the server, is there a way to bulk export those plans for later analysis or do I need to export them one at a time? Simply saving the query results will allow me to maintain the XML, but it is still a one by one review, plus I am missing something in the process of being able to view it later.

I am 99% certain there is something simple I am missing. Could you help me find that?

I know it is very easy to export a single query plan. My question is, when I create a query which returns multiple plans, such as looking at the most costly queries on the server, is there a way to bulk export those plans for later analysis or do I need to export them one at a time?

I would suggest you to create a utility database e.g. dba_repository (or whatever name you like the database to be called) and store the information in that utility database along with the query plan XMLs. Overtime, it would server as a warehouse for doing performance tuning and benchmarking.

Create physical tables and store relevant information in them. This way you can backup the database along with all the info you gathered.

IMHO, it would become ugly if you go on the route of exporting plan XMLs into folders.

Basically, you need to explore the plan cache from your server instance that will allow you to tune your workload and give you below information :

• Plans with Missing Indexes

• Plans with Warnings

• Plans with Implicit Conversions

• Plans with Index Scans

• Plans with Lookups

• Finding index usage

• Plans with Parameterization

• Cost of Parallel Plans

• Cost of Parallel Plans with detail per Operator

• Parallel plans where Avg. Worker Time > Avg. Elapsed Time

• Parallel plans where Avg. Worker Time > Avg. Elapsed Time with detail per Operator

There is a wealth of information and scripts on blog written by Pedro Azevedo Lopes - Exploring the plan cache – Part 1 & Part 2.

Also, Jonathan Kehayias has written a lot about exploring plan cache.

A gentle note: Dont do knee jerk performance tuning e.g. dont just blindly create missing indexes when you find them, etc. Instead follow a more methodological approach by baselining your server instance and then take it from there.

StackExchange Database Administrators Q#114679, answer score: 4