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.

Tracy Boggiano
Follow me

Tracy Boggiano

Database Administrator at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Platform. She has worked on SQL Server 6.5 and up including currently SQL 2017 CTP 2.0. She enjoys monitoring, performance tuning, and disaster recovery technologies.

She also tinkered with databases in middle school to keep her baseball card collection organized.

Her passion outside of SQL Server is volunteering with foster children as their advocate in court through http://www.casaforchildren.org.
Tracy Boggiano
Follow me