In case you missed the announcement SQL Server 2016 CTP2 preview was publicly announced today. You can read about it in the following post:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx
There are already some training materials up on https://technet.microsoft.com/en-us/virtuallabs
The first new feature I noticed was a new type of actual query plan to include, Live Query Statistics.
When enabled the actual query plan is displayed as the query is executed, however it now also displays the real time progress as each stage of the query is executed. You can see this in the following screen shot where the Sort is at 65% (and subsequently all tasks after it are also at 65%). I wasn't fast enough to capture it before it reached the sort but it really does visually show you real time query execution. I'm going to have to find a really large workload to try it out on ;)
One of the new feature Query Store is one I have been "geeking out" on since earlier in the year and I am so glad I can now talk about it publicly and here is my first look at the feature first hand.
First off when you view the properties of a database there is a new configuration section of "Query Store" as seen in the following screen shot.
Before you can access this feature you need to enable it on each database. Like all data collection there is a small resource overhead but it's minimal due to the layer of integration so just enable it.... and given the product is in preview status should there be performance impact file a Connect Item and let the product team know.
So now you have enabled it you will have the ability to customise the data frequency, buffer hardening (flush), data retention/grooming, etc. Here are the defaults.
In my lab I have adjusted these values to be more granular, for the purpose of this blog, but you will need the find the sweet spot for your environment ;)
In the earlier screen shot of the database properties you can also see that there are graphical representations of the Query Store storage usage and a button to flush the storage if you need to.
Now if you refresh the database in Object Explorer you will see a new Query Store branch which contains some useful views.
These views offer some interesting data that previously we would have had to use extra tools to obtain.
The following shows the Overall Resource Consumption view.
Each of the views has the ability to be configured to adjust the metrics, date range, etc
The most interesting of the views in my opinion is the Top Resource Consuming Queries as this shows the top X queries on the top left box, and the query plans which have been used to return the results in the right top box.
When you select a "plan id" it displays the cached plan for that id in the lower pane.
Why is the so interesting, well like you can see in this screen shot I have had two plans for the same query. One with Parallelism and one without..... and from the bubble chart I can see which one has executed at times with the highest resource consumption and which has executed with the least resource consumption (based on my charts configuration). So from this data you can then make a choice if you should "force" a plan, or if you have forced the plan previously then you can 'unforce' it.
This makes all that effort of trying to figure out if a query plan has changed and is causing a degradation in performance, and then having to extract that plan and create the guide for it. Or many other changes in patterns of performance behavior.
Behind the scenes there is a great deal of data being captured about query execution to which we can access from a number of system tables.
For example on the table sys.query_store_query you can see the data around the internals of query times such as compilation time, parse time, etc.
I am definitely looking forward to exploring this feature further and all the time I am going to get back when looking into performance issue.
Obviously this is CTP2 and features/functionality will change before RTM.
Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx
There are already some training materials up on https://technet.microsoft.com/en-us/virtuallabs
The first new feature I noticed was a new type of actual query plan to include, Live Query Statistics.
When enabled the actual query plan is displayed as the query is executed, however it now also displays the real time progress as each stage of the query is executed. You can see this in the following screen shot where the Sort is at 65% (and subsequently all tasks after it are also at 65%). I wasn't fast enough to capture it before it reached the sort but it really does visually show you real time query execution. I'm going to have to find a really large workload to try it out on ;)
One of the new feature Query Store is one I have been "geeking out" on since earlier in the year and I am so glad I can now talk about it publicly and here is my first look at the feature first hand.
First off when you view the properties of a database there is a new configuration section of "Query Store" as seen in the following screen shot.
Before you can access this feature you need to enable it on each database. Like all data collection there is a small resource overhead but it's minimal due to the layer of integration so just enable it.... and given the product is in preview status should there be performance impact file a Connect Item and let the product team know.
So now you have enabled it you will have the ability to customise the data frequency, buffer hardening (flush), data retention/grooming, etc. Here are the defaults.
In the earlier screen shot of the database properties you can also see that there are graphical representations of the Query Store storage usage and a button to flush the storage if you need to.
Now if you refresh the database in Object Explorer you will see a new Query Store branch which contains some useful views.
These views offer some interesting data that previously we would have had to use extra tools to obtain.
The following shows the Overall Resource Consumption view.
Each of the views has the ability to be configured to adjust the metrics, date range, etc
The most interesting of the views in my opinion is the Top Resource Consuming Queries as this shows the top X queries on the top left box, and the query plans which have been used to return the results in the right top box.
When you select a "plan id" it displays the cached plan for that id in the lower pane.
Why is the so interesting, well like you can see in this screen shot I have had two plans for the same query. One with Parallelism and one without..... and from the bubble chart I can see which one has executed at times with the highest resource consumption and which has executed with the least resource consumption (based on my charts configuration). So from this data you can then make a choice if you should "force" a plan, or if you have forced the plan previously then you can 'unforce' it.
This makes all that effort of trying to figure out if a query plan has changed and is causing a degradation in performance, and then having to extract that plan and create the guide for it. Or many other changes in patterns of performance behavior.
Behind the scenes there is a great deal of data being captured about query execution to which we can access from a number of system tables.
I am definitely looking forward to exploring this feature further and all the time I am going to get back when looking into performance issue.
Obviously this is CTP2 and features/functionality will change before RTM.
Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
