Multi-Database Query Store Data Retrieval

Tracy Boggiano wants to get Query Store data for a large number of databases on the same instance and has written a Powershell script to this effect:

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.

This is one of two issues I have with Query Store, the other being that its data gets saved to the primary filegroup without any recourse.  Click through for the script.

Related Posts

Basic Powershell Regex

Adam Bertram shows how to use regular expressions for pattern matching in Powershell: However, regex has traditionally been a topic that most IT pros shy away from when they first see how it works. Admittedly, regex does take a bit of getting used to, and you’re still probably going to have to do some Googling […]

Read More

Substrings: Powershell Versus T-SQL

Shane O’Neill contrasts the SUBSTRING function in T-SQL with Powershell’s Substring method: The main difference that I can see when using SUBSTRING() in SQL Server versus in PowerShell is that SQL Server is very forgiving. If you have a string that is 20 characters longs and you ask for everything from the 5th character to the 100th […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930