Skip to content

PowerShell Function to Get Query Store Data Across Multiple Databases

In SQL Server 2016, Microsoft introduced to us the Query Store.  This has been proven to be a very beneficial feature especially at my job, saved the day on Christmas Eve. One of the limitations if see is when you have server with several databases on it trying to find the one that has the problem. So until Microsoft gives use a better tool or someone builds one I wrote a quick PowerShell function to query across all the databases and return data to a grid so you can sort and find that troublesome query.

To call it simply provide the server, which metric you want, the top number of queries to return, who many hours to look back, and the minimum execution count.

Here is the function. Enjoy! Be warned if you have a lot of databases say like 300 on the same server it will take a long time to run. So you may want to adjust the QueryTimeout parameter near the bottom or add a WHERE clause to the sys.databases table.

One thought on “PowerShell Function to Get Query Store Data Across Multiple Databases

  1. Pingback: Multi-Database Query Store Data Retrieval – Curated SQL

Leave a Comment