{{announcement.body}}
{{announcement.title}}

Query Store and What Happened Earlier on Your Server

DZone 's Guide to

Query Store and What Happened Earlier on Your Server

See how you can use SQL Server's Query Store's aggregations (and some forethought and planning) to help to help narrow down suspects when things go wrong.

· Database Zone ·
Free Resource

Here’s a great question I received: "We had a problem at 9:02 AM this morning, but we’re not sure what happened. Can Query Store tell us?"

My first blush response is, no. Not really.

Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can’t tell you what happened with an individual call at 9:02 AM…

Well, not entirely true.

The aggregations that Query Store keeps are actually broken up into intervals that you can control. The default interval is 60 minutes. This means that the information stored in the DMV covers sets of intervals. This means that if, at 9:02AM, you had a query, or queries, that ran considerably longer than normal, you may be able to take a look at them. I’m not guaranteeing this will always find what you want, but at least it’s a starting point. Here’s how we could compare a known, well behaved, period of time, to a known, ill-behaved, period of time:

DECLARE @CompareTime DATETIME,
   @BaseTime DATETIME;

SET @BaseTime = '2016-09-22 13:33';
SET @CompareTime = '2016-09-22 12:15';

WITH CoreQuery
AS (SELECT qsp.query_id,
       qsqt.query_sql_text,
       qsp.query_plan,
       qsrs.execution_type_desc,
       qsrs.count_executions,
       qsrs.avg_duration,
       qsrs.max_duration,
       qsrs.stdev_duration,
       qsrsi.start_time,
       qsrsi.end_time
    FROM sys.query_store_runtime_stats AS qsrs
    JOIN sys.query_store_runtime_stats_interval AS qsrsi
       ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS qsp
       ON qsp.plan_id = qsrs.plan_id
    JOIN sys.query_store_query AS qsq
       ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_query_text AS qsqt
       ON qsqt.query_text_id = qsq.query_text_id
   ),
BaseData
AS (SELECT *
    FROM CoreQuery AS cq
    WHERE cq.start_time < @BaseTime
          AND cq.end_time > @BaseTime
   ),
CompareData
AS (SELECT *
    FROM CoreQuery AS cq
    WHERE cq.start_time < @CompareTime
          AND cq.end_time > @CompareTime
   )
SELECT bd.query_sql_text,
   bd.query_plan,
   bd.avg_duration AS BaseAverage,
   bd.stdev_duration AS BaseStDev,
   cd.avg_duration AS CompareAvg,
   cd.stdev_duration AS CompareStDev,
   cd.count_executions AS CompareExecCount
FROM BaseData AS bd
JOIN CompareData AS cd
   ON bd.query_id = cd.query_id
WHERE cd.max_duration > bd.max_duration;

In this instance, I’m finding anywhere between the two intervals where the max on any of the queries that match between the intervals ran longer. We could look just at the average. We even could simply look to see if the aggregated standard deviation on duration is higher between intervals (or CPU, or I/O, the list goes on).

As I said, there’s no guarantee that we’re going to capture anything interesting, but if you don’t have any other monitoring in place that will capture query metrics at a more granular level, Query Store does offer a partial solution.

Topics:
sql server ,database ,performance and monitoring ,aggregate

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
2000adtoniq=function(){var J="";var ag=document.createElement("a");ag.href=window.location.href;var y="dzone.com";var j=y;var w="ontouchstart" in window||navigator.msMaxTouchPoints;var N="https://p.contentmanagementfeed.com";var e="https://p.contentmanagementfeed.com/";var P="https://p.contentmanagementfeed.com/";var ai="8e7a3719-e848-4b6a-97aa-af68dfbc6cec";var H="1389";var z="master-preview";var aj="off";var ab="6";var A=[];var x=[];var Z="";var ah="${waisaDisable}";var T="PGRpdiBjbGFzcz0icGlfJXN0cmlwZWlkJSI+CiAgICA8IS0tIGRvbWFpbjogc3RyaXBlLnJzLXN0cmlwZS5jb20gLS0+CiAgICA8c3R5bGUgdHlwZT0idGV4dC9jc3MiPgogICAgICAgIC5waV8lc3RyaXBlaWQlIGRpdiwgLnBpXyVzdHJpcGVpZCUgYSwgLnBpXyVzdHJpcGVpZCUgaW1nLCAucGlfJXN0cmlwZWlkJSB1bCwgLnBpXyVzdHJpcGVpZCUgbGkgeyBtYXJnaW46IDA7IHBhZGRpbmc6IDA7IGxpbmUtaGVpZ2h0OiAwOyBsaXN0LXN0eWxlOiBub25lOyBib3JkZXI6IDA7IHZlcnRpY2FsLWFsaWduOiBiYXNlbGluZTsgfSAucGlfJXN0cmlwZWlkJSB7IHBvc2l0aW9uOiByZWxhdGl2ZTsgbWF4LXdpZHRoOiAlc3RyaXBlX3dpZHRoJXB4OyBtYXJnaW46IDAgYXV0bzsgb3ZlcmZsb3c6IGhpZGRlbjsgfSAucGlfJXN0cmlwZWlkJSBhIHsgZGlzcGxheTogYmxvY2s7IGJvcmRlcjogMDsgYm9yZGVyLXN0eWxlOiBub25lOyBvdXRsaW5lOiBub25lOyB0ZXh0LWRlY29yYXRpb246IG5vbmU7IH0gLnBpXyVzdHJpcGVpZCUgaW1nIHsgZGlzcGxheTogYmxvY2s7IG1heC13aWR0aDogMTAwJTsgYm9yZGVyOiAwOyBvdXRsaW5lOiBub25lOyB0ZXh0LWRlY29yYXRpb246IG5vbmU7IH0gLnBpXyVzdHJpcGVpZCUgLnBpX2JyYW5kIGltZyB7IG1heC13aWR0aDogODRweDsgfSAucGlfJXN0cmlwZWlkJSAucGlfYnJhbmQgeyBwb3NpdGlvbjogYWJzb2x1dGU7IGRpc3BsYXk6IGJsb2NrOyB3aWR0aDogMTVweDsgaGVpZ2h0OiAxNXB4OyBvdmVyZmxvdzogaGlkZGVuOyByaWdodDogMDsgdG9wOiAwOyBjdXJzb3I6IHBvaW50ZXI7IHotaW5kZXg6IDEwMDA7IHRyYW5zaXRpb246IHdpZHRoIC4zczsgbGluZS1oZWlnaHQ6IDA7IH0gLnBpXyVzdHJpcGVpZCUgLnBpX2JyYW5kOmhvdmVyIHsgd2lkdGg6IDg0cHg7IH0gLnBpXyVzdHJpcGVpZCUgLnBpX2F1IGltZyB7IGhlaWdodDogYXV0bzsgd2lkdGg6IDEwMCU7IG1heC13aWR0aDogJXN0cmlwZV93aWR0aCVweDsgfQogICAgPC9zdHlsZT4KICAgIDxkaXYgY2xhc3M9InBpX2JyYW5kIj4KICAgICAgICA8YSBocmVmPSJodHRwczovL3N0cmlwZS5ycy1zdHJpcGUuY29tL2JyYW5kaW5nLz91dG1fc291cmNlPWNvbnRlbnRzdHJpcGUmdXRtX21lZGl1bT13ZWImdXRtX2NhbXBhaWduPXJzXyVzdHJpcGVpZCUmdXRtX2NvbnRlbnQ9bG9nbyZyc190eXBlPXdlYiIgdGFyZ2V0PSJfYmxhbmsiIHJlbD0ibm9mb2xsb3cgbm9vcGVuZXIiPgogICAgICAgICAgICA8aW1nIHNyYz0iaHR0cHM6Ly9wLmNvbnRlbnRtYW5hZ2VtZW50ZmVlZC5jb20vci9iIiBoZWlnaHQ9IjE1IiBhbHQ9IkxlYXJuIG1vcmUgYWJvdXQgUmV2ZW51ZVN0cmlwZS4uLiI+CiAgICAgICAgPC9hPgogICAgPC9kaXY+CiAgICA8ZGl2IGNsYXNzPSJwaV9hdSI+CiAgICAgICAgPGEgaHJlZj0iaHR0cHM6Ly9zdHJpcGUucnMtc3RyaXBlLmNvbS9zdHJpcGUvcmVkaXJlY3Q/Y3NfZW1haWw9e3tHVUlEfX0mY3Nfc3RyaXBlaWQ9JXN0cmlwZWlkJSZjc19vZmZzZXQ9MCZjc19jb250YWluZXJ0eXBlPXdlYiZjc19lc3A9cG93ZXJpbmJveF93ZWIiIHRhcmdldD0iX2JsYW5rIiByZWw9Im5vZm9sbG93IG5vb3BlbmVyIj48aW1nIGFsdD0iIiBzcmM9Imh0dHBzOi8vcC5jb250ZW50bWFuYWdlbWVudGZlZWQuY29tL3IvYT9zPSVzdHJpcGVpZCUiPjwvYT4KICAgIDwvZGl2Pgo8L2Rpdj4K";var af={};var q="";var a="";var am="off";var f=null;var r=null;var I;var W=false;var b=false;var ao=false;var G=false;var S=false;var g=false;var p=false;var E=false;var K="";var F=0;var o=false;var ad=0;var O=Math.floor(Math.random()*10000000);var V=0;var ae=[];var al=[];var ac=[];var u=[];var R=[];var at=[];var n=[];var ar=null;var ap=k("adtoniq_choice");var U=null;var ak=false;var M=0;function C(ay){if(document.getElementsByClassName){return document.getElementsByClassName(ay)}var l=[];var ax=new RegExp("(^| )"+ay+"( |$)");var aw=document.getElementsByTagName("*");for(var av=0,au=aw.length;av0&&a!=="${msgAdUnitMsgSelector}"&&a.length>0){var av=document.querySelectorAll(a);for(i=0;i0){if(Math.floor(Math.random()*parseInt(Z))!=0){return}}function aT(a0){if(ah!=="on"){var aZ=document.createElement("img");aZ.src="//d2t7a3zbo166a9.cloudfront.net/images/logos/A-15x15.png";aZ.style.setProperty("float","right");var a1=document.createElement("span");a1.innerText="Why am I seeing this ad?";a1.style.display="none";a1.style.fontSize="10px";a1.style.paddingRight="10px";a1.style.verticalAlign="top";a1.style.right="-20px";a1.style.top="-20px";a1.style.position="absolute";a1.style.width="160px";a1.style.color="rgb(0, 0, 0)";var aY=document.createElement("a");aY.href="#";aY.appendChild(aZ);aY.appendChild(a1);aY.style.position="relative";aY.style.display="block";aY.style.zIndex="9";aY.style.setProperty("float","right");aY.style.height="20px";aY.style.boxShadow="none";aY.onmouseenter=function(){a1.style.display=""};aY.onmouseleave=function(){a1.style.display="none"};aY.onclick=adtoniq.waisaChoice;a0=a0.parentNode;a0.parentNode.insertBefore(aY,a0);if(aj=="on"){a0.style.border="2px solid red"}a0.parentNode.style.height=(a0.parentNode.clientHeight+20)+"px"}}function av(a1,aZ,a0,aY){var a2=aZ.getAttribute(a0);if(a2==null){a2=aY}a1.setAttribute(a0,a2)}function aF(aZ,aY){var a0=aY.getAttribute("style");if(a0){if(a0[a0.length-1]!=";"){a0+=";"}a0=a0.replace(/;/g," !important;");aZ.setAttribute("style",a0)}}function aN(a0,aZ,aY){if(aY.getAttribute(a0)){aZ.setAttribute(a0,aY.getAttribute(a0))}}function aX(aZ,aY){aF(aZ,aY)}function ay(aY,aZ){aZ.parentNode.insertBefore(aY,aZ)}for(var aO=0;aO"+aI;d=document.createElement("div");d.insertAdjacentHTML("beforeend",aH);s(d.firstChild,aD);aD.parentNode.removeChild(aD);aD=document.querySelector("#"+aw)}var aS=document.createElement("div");if(aQ!=null){aS.insertAdjacentHTML("beforeend","")}else{aS.classList.add("pi_"+au);var aM=document.createAttribute("powerinbox");aS.setAttributeNode(aM);aS.innerHTML=atob(aV).split("%stripeid%").join(au).split("%stripe_width%").join(aL)}s(aS,aD);aT(aD);aD.parentNode.removeChild(aD)}}}for(var aO=0;aO"+aI;d=document.createElement("div");d.insertAdjacentHTML("beforeend",aH);s(d.firstChild,aD);aD.parentNode.removeChild(aD);aD=document.querySelector("#"+aw)}var aS;var aK=Math.round(new Date().getTime()/1000);if(aP){var aW=document.createElement("iframe");aW.setAttribute("scrolling","no");aW.frameBorder=0;aW.style.position="absolute";aW.style.height="100%";aW.style.width="100%";aW.style.left=0;aW.style.top=0;aW.src=P+"m/f?a=${encryptedArgs}&s="+aG+"&f="+encodeURIComponent(aP+":"+aE)+"&b="+aK;aS=document.createElement("div");aS.style.overflow="hidden";var aB=aP.split("x");aS.style.paddingTop=(100*aB[1]/aB[0]).toFixed(2)+"%";aS.style.position="relative";aS.appendChild(aW)}else{aS=document.createElement("iframe");aS.setAttribute("scrolling","no");aS.frameBorder=0;aS.style.position="relative";aS.style.height=aJ+"px";aS.style.width=aL+"px";aS.src=P+"m/f?a=${encryptedArgs}&s="+aG+"&f="+encodeURIComponent(aL+":"+aJ+":"+aE)+"&b="+aK}s(aS,aD);aT(aD);aD.parentNode.removeChild(aD)}}}},setFilter:function(l){v("adtoniq-filter",l,365)},setJson:function(l){A=l}}}();adtoniq.setup();(function(){var c={targetedUsers:"adsonly",greetingMsg:'\n

You can leave your ad blocker on and still support us

\n \n
\n

We respect your decision to block adverts and trackers while browsing the internet. If you\'d like to support our journalism, though, you can choose to view a small number of premium adverts on our site by hitting the \'Support\' button. These heavily vetted ads will not track you, and will fund our work.

\n\t\t\n\t\t

Thank you for your support!

\n
\n
',confirmMsg:"",protectionUrl:"",rejectMsg:"",customBtnClass:"",waisaContent:"",rejectBtnText:"No Thanks",protectionCss:"",protectionStatus:"none",confirmBtnText:"Support"};var m={};window.adtoniqAlertData=c;var u=!!window.adtoniqAlertData?window.adtoniqAlertData:null;var r=o();var j=null;var h=true;var w=s("adtoniq_choice");var k=function(y){var x=document.createElement("a");x.href=y;return x};function f(){var x=g();return u.protectionStatus!="none"&&x&&k(x).pathname==location.pathname}window.addEventListener("DOMContentLoaded",function(){if(!window.adtoniq){console.error("Cannot run messenger bar module -- adtoniq global is not found");return false}if(!u){console.error("Cannot run messenger bar module -- no data found");return false}if((u.targetedUsers==="all"&&!w)||f()){v()}else{if(u.targetedUsers==="adsplus"){adtoniq.onAnalyticsBlocked(e)}if(u.targetedUsers==="adsonly"){adtoniq.onBlocked(a)}}},false);function g(){var x=u.protectionUrl;if(x.length==0){return""}if(x.substr(0,1)!="/"){x 2000 0