The Basics - Level 1 of the Stairway to Query Store

  • carlos10robles

    SSC Enthusiast

    Points: 180

    Comments posted to this topic are about the item The Basics - Level 1 of the Stairway to Query Store

    Carlos Robles  

    DBA Mastery
    Data Platform MVP | MCSE, MCSA, MCTS, MCP | ITIL v3




  • Jeff Moden

    SSC Guru

    Points: 996651

    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?

    1. 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.
    2. 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?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply