{"id":1878,"date":"2016-03-29T16:30:21","date_gmt":"2016-03-29T14:30:21","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1878"},"modified":"2016-04-14T23:49:54","modified_gmt":"2016-04-14T21:49:54","slug":"sql-server-2016-features-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/03\/29\/sql-server-2016-features-query-store\/","title":{"rendered":"SQL Server 2016 features: Query Store"},"content":{"rendered":"

Given that SQL Server 2016 is coming \u2018real soon now\u2019, it\u2019s probably well past time that I write up some thoughts on new features.<\/p>\n

The first one I want to look at is a feature that I\u2019m so looking forward to getting to use, the Query Store.<\/p>\n

Query Store is essentially a flight recorder for a SQL database. It tracks queries, their execution characteristics and their execution plans. The best part is that this information is persisted into the database and hence is not lost on restart, as the current performance-related DMV contents are.<\/p>\n

The data is aggregated over a defined period of time, by default an hour. This will probably be fine for most cases, if there are performance problems that come and go in fractions of an hour (like a case I had last year), then that interval may need to be reduced, depending on the type of problem.<\/p>\n

There are lots and lots of blog posts on how to enable it and how to query it and the like, so I\u2019m not going to repeat that info here. See http:\/\/sqlperformance.com\/2015\/02\/sql-plan\/the-sql-server-query-store<\/a> for all of that kind of info.<\/p>\n

There\u2019s two main aspects that I want to discuss about this feature. Let\u2019s start with a screenshot of one of the Query Store\u2019s built-in reports, the top resource-consuming queries.<\/p>\n

\"QueryStore\"<\/a><\/p>\n

The longest-running (on aggregate) query in this database is this nice parameterised query against the Stock and Issues tables. In the pane at the bottom of that report I can see its query plans.<\/p>\n

Nothing really fancy there, except for one thing.<\/p>\n

The server that I pulled those reports from has never had that query run against it. I pulled that report from an Azure VM that I set up last week. The workload all dates from 3 weeks ago.<\/p>\n

Query Store data is persisted into the database, which means it\u2019s included in a backup. I ran the workload against one server, backed up the database, restored it elsewhere and then queried the query store data. And there\u2019s an even better part.<\/p>\n

\"QueryStoreNoTables\"<\/a><\/p>\n

Before running those Query Store reports, I dropped every table in the database.<\/p>\n

Before this, to get the aggregated performance characteristics of a workload, I\u2019d have to run a server-side trace or an extended events session, write the results out to a file, copy the file to my analysis server, load them into a table and aggregate the results. It\u2019s a process that could take a day or two.<\/p>\n

Now (or at least once SQL 2016 becomes widespread) I can just ask the client for their latest backup as all the query performance data I want is in there. If they\u2019re uncomfortable with me having access to the data, they can restore the DB somewhere, drop all the tables then back up the \u2018empty\u2019 database and send me that. Much faster, and far fewer worries about having potentially missed something that ran at a time the trace wasn\u2019t running.<\/p>\n

The second thing isn\u2019t so much about Query Store, as it is about the message that\u2019s coming from Microsoft about it. Over and over and over they keep talking about how Query Store can be used to force a good query plan. It\u2019s not hard to do.<\/p>\n

Let\u2019s take an example of a query with a parameter sniffing problem.<\/p>\n

CREATE PROCEDURE GetOrders (@StartDate DATETIME)\r\nAS\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 OrderDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IssueID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 QtyOrdered,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Total\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Orders\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 OrderDate > @StartDate;\r\nGO<\/pre>\n
The index on OrderDate isn\u2019t covering, so a seek and key lookup is optimal for small numbers of rows and a clustered index scan is better for large numbers of rows<\/p>\n
\"BadQueryLogicalReads\"<\/a><\/p>\n
The query in question is by far the worst query in terms of logical reads on the server.<\/p>\n
\"QueryStoreMultiplePlans\"<\/a><\/p>\n
And it has two plans associated with it. The one at the top (plan 453) is the seek and key lookup. The one at the bottom (plan 452) is the clustered index scan.<\/p>\n
Select the desired plan. It appears in the bottom section, then<\/p>\n
\"ForcePlan\"<\/a><\/p>\n
And problem solved.<\/p>\n
Well\u2026<\/p>\n
I\u2019d argue that\u2019s less fixing the problem than hiding it. Sure, you won\u2019t get a different plan, or at least that\u2019s the idea. (While testing I did manage to get a different plan to the forced one, I need to investigate further why.) But is that forced plan the best solution? In this example, widening the index would have been a better solution. In other cases you might rather want to split the procedure into two, one for some date values one for others, or add the recompile hint, or even change the query.<\/p>\n
Forcing a plan is great for stopping a problem that\u2019s currently bringing production down, but it\u2019s far from the only thing that will ever be done now. For starters forcing a good plan requires that there is a good plan, and if the query is written so it can\u2019t use indexes or there are no suitable indexes, there won\u2019t be a good plan to force. It\u2019s a nice tool, but that\u2019s all it is, another tool in the performance tuning box. It\u2019s not a replacement for all other tuning work that\u2019s ever been done<\/p>\n
Now, I wonder how long it\u2019s going to take to get my clients to upgrade.<\/p>\n","protected":false},"excerpt":{"rendered":"
Given that SQL Server 2016 is coming \u2018real soon now\u2019, it\u2019s probably well past time that I write up some thoughts on new features. The first one I want to look at is a feature that I\u2019m so looking forward… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: SQL Server 2016 features: Query Store","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16],"tags":[],"class_list":["post-1878","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-ui","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1878"}],"version-history":[{"count":6,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1878\/revisions"}],"predecessor-version":[{"id":1890,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1878\/revisions\/1890"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}