The Basics - Level 1 of the Stairway to Query Store

• carlos10robles

  SSC Veteran

  Points: 200

  More actions

  May 13, 2019 at 12:00 am

  #3533007

  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

  w: www.dbamastery.com

  e: crobles@dbamastery.com

   

• Jeff Moden

  SSC Guru

  Points: 1004556

  More actions

  May 13, 2019 at 1:27 pm

  #3641461

  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.
  

  ---

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