I haven’t been blogging enough recently—I’ve been busy writing, traveling, and speaking all around the world, and trying to get ready for my PASS PreCon on Power BI. It’s been a busy year—it’s barely September, and I’ve already requalified for my frequent flier status. Anyway, you don’t care about me. Let’s talk about SQL 2016—there are a myriad of new features and enhancements that are truly awesome. And for those of you who are all “old man yells at cloud” most of the new features and enhancements will help you in your on-premises environment. Anyway—there’s more time for that between now and whenever 2016 gets released. Let’s talk about the Query Store.
I got an email from one of my clients (these guys are brave and already live on 2016—they along with Microsoft have that much confidence in the code) about a query that was running poorly over the weekend (worse than in the older environment). A little bit about their environment—it’s largely a data warehouse type solution, with the goal of delivering data sets to their clients. In the upgrade to 2016, we did a rearchitecture that heavily leveraged clustered columnstore indexes, and took advantage of Availability Groups for scale out reads. So remember when one of your customers would email you about something that was slow over the weekend, and you would desperately scour the plan cache, possibly writing xQuery to look for the needle in the haystack of a query that was performing poorly?
Figure 1 Query Tuning in SQL 2014
Enter the Query Store. I opened up the database, and find the Query Store in Object Explorer.
I can see “Top Resource Consuming Queries”—if I click on that, a report will launch. (It will default to the last hour—click configure in the top right to change—the time interval and the resource you are curious about.)
Since I wanted to see queries with heavy logical reads from last weekend, I changed the resource type to logical reads, and the duration to “Last Week”. And then, I get this:
Well that was easy—I see the clear outlier of a query, and I see the index recommendation. I also get the execution plan—where I note the biggest expense is that SQL Server is doing a full scan of a fact table. Writing this post took me way longer than it did to isolate that query and propose a couple of options for tuning it to the client. The Query Store is awesome.
2 Responses