SQL Server / Index Usage Report Project / Most Expensive Query Plans

I created this ETL to fetch and save expensive execution plans daily. When ETL process runs again firstly it creates directory and copy all plans from previous day to that directory, and only after that fetch new execution plans. This way I can look old plan later if I like.

SSIS Package Description.

Control Flow Screen

I am using File System Task to create new directory using the name of  day before, to store my yesterday’s execution plans.

As you can see I am using variable FolderPath to create folder name with day before date.

This is Expression I used

From the valuated value you can see the logic of the path.

Next step is to move all execution plans to the newly created directory.

I am using Foreach Loop container to accomplish that

I am searching for all the files with sqlpan extension

And mapping those values with variable FilesToMove

After that I am adding new file system task within loop container and adding those two variables, previously created FolderPath as an destination and source variable called FolderPathSource as an name of the file I want  to move to the created directory.

FolderPathSource expression

First part is over, now we have moved our old execution plans to the newly created directory. Now we need to choose new most expensive plans.

I created Execution SQL Task and joining two WH tables to fetch query hashes of expensive plans

SELECT
distinct CONVERT(varchar(max), epc.[query_hash], 1) as query_plan_varchar
FROM [WH_Index_Stats].[dbo].[Cloud02_Expensive_Queries] eq
inner join [WH_Index_Stats].[dbo].[Cloud02_Execution_Plan_Cache] epc on epc.[plan_handle]=eq.[planhandle]

You will get something similar

You can see here how I created ETL packages for those WH tables
Expensive_Queries
Execution_Plan_Chache

Finally, I have a list of query hashes and I want to fetch plans for them.

I am adding another loop to be able to create execution plans.

And map with new VarilabeFileName

Now I am adding data flow task to that loop and passing variable VariableFileName to the OLE DB Source component

And adding following SQL command to my db source component

SELECT
distinct CONVERT(varchar(max), epc.[query_hash], 1) as [query_hash],
CONVERT(varchar(max), epc.[plan_handle], 1) as [plan_handle],
CONVERT(varchar(max), epc.[query_plan], 1) as query_plan_varchar
FROM [WH_Index_Stats].[dbo].[Cloud02_Expensive_Queries] eq
inner join [WH_Index_Stats].[dbo].[Cloud02_Execution_Plan_Cache]
epc on epc.[plan_handle]=eq.[planhandle]
where CONVERT(varchar(max), epc.[query_hash], 1) =?

So if you try to test this query and instead using question mark you put there actual query hash you will have something similar to this

And the last part is to pass that xml to Flat File Destination tool

End results are similar to this

Similar Posts:

• SQL Server / Index Usage Report Project / Most Expensive Queries
• SQL Server / Index Usage Report Project
• SQL Server / Long Running Queries / Email Alert
• SQL Server / Missing Indexes Query / Cached Plans / Email Alert
• SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell