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


You will get something similar


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


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


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:

Leave a Reply

Your email address will not be published. Required fields are marked *