Great intro, Carlos. Very much looking forward to the rest of this series especially since we've not used Query Store on our systems, yet.
Shifting gears a bit, a lot of people make somewhat of a mistake by concentrating on the queries that have the longest duration, which are usually NOT the biggest problem in systems that have a combination of batch processing and OLTP (and most systems do have both in one form or another). Just to express an interest, will Query Store make it easier to find the following?
- Ad hoc queries and stored procedures that cause a recompile virtually every time they're used. My interest here is that we had a query from the front end that would call a stored procedure. The run time was "only" 100ms (which I thought was way too long but that's a different story) but the query would cause a recompile about 96% if the time. The really bad part was that the recompile would take between 2 and 22 SECONDS each time. Although Jonathan Kehayias has a great script to help find those, it does take a long time to run and leaves a bit to be desired because it can't actually aggregate by the query being executed because it's dynamic in nature and so requires a fair bit of analysis after capturing the output.
- Very short queries that are called tens of thousands and sometimes millions of times per hour. While most people aren't interested in "very short queries", the number of times a query is executed in an hour can really cause a huge build up of resource usage. For example, we identified one "very short" query that "consumed" nearly 34 TERABYTES of memory I/O in the form of logical reads in just 8 hours. We also identified several others that were nearly as bad and rewrote them all. They ran even quicker (and CPU dropped as well) but, possibly even more importantly, the memory I/O dropped to "just" several hundred mega bytes in the same time period. This also helped other queries that didn't have such a problem to also run more quickly.
That being said, will your series cover such things?