Query Store: Exploring new features in SQL Server – vNext   1 comment


Troubleshooting performance problem is one of the challenging and most often faced situation in DBA’s life. Following diagram shows three major scenarios where DBA’s get involved and spend nights troubleshooting performance problems.

Note: This is with community technical preview 2 of SQL Server 2016, things may change with the RTM release

18

So you may have issues like

  • Application is completely down because of performance problem
  • Users are facing performance problem intermittently.
  • You have recently upgraded your databases and now performance is very slow.

These are common problems which can take hours of your time in troubleshooting and the major reason is change in query plan choice, which means plan has either been flushed out of cache or have changed due to various reason e.g. data change, memory pressure etc.

Hence during such troubleshooting we spend most of the time in finding the answers of following questions

  • Which are the slow queries?
  • Why is it slow (plan change? blocking?)
  • What was the old stats of query?
  • What was the old plan?

Without the ability to see all the changes in the query plans it’s difficult to figure out why regressions happened and what can you do to prevent them in the future.  Sometimes even if we know what query plan supposed to be but still it’s not easy to modify the query using query hint or figure out how to make a plan guide.

So how good it would be if this data is collected on regular basis and stored into databases so that it can be used for further performance troubleshooting, possible?

Yes, SQL Server 2016 CTP2 brings you a new feature which can help us solving plan choice change problem called “Query Store”

Today I am going to explain what is query store and how can it be helpful in troubleshooting performance issues faster with the help of an example along with a demo.

So before we start demo let me give you some background on querystore.

Query Store works similar to an airplane’s flight data recorder, it collects and presents detailed historic information about all queries in simplified way and reduce time to diagnose and resolve issues faster. Collected data is separated by time windows, allowing you to observe changes in query performance over time. If a query generates different plans, you can use Query Store to analyze the plan changes, identify possible performance degradation, and even force the query processor to use a particular plan for your query. Most importantly!!!  This information is stored across server restarts, upgrades and query recompiles.

Here is the Query Store work flow.

19

Now, I am sure following questions must be coming across your mind.

  • Where does it stores all this information?
  • What’s the overhead on SQL Server?

So here are the answers. All the information is stored in user database for which you have enabled the Query Store and also you have choice to control data volume and its retention along with flush interval. It works similar to “lazy writer” where data is first collected into memory and later it will be flushed to the disk on regular interval by a background thread. In case of memory pressure on the server it flushes the data at faster rate. You can see all the stats using TVF “Query_Store_Runtime_Stats”.

21

Below diagram shows the settings for Query Store and how to enable it.

Right click on database- > properties and you will find a separate page called “Query Store”

16

 

As you can see there is a separate page created under database properties which has all the details about Query Store. These options are self-explanatory so I am not going in to the details of each. Along with that a folder called “Query Store” also gets created inside that database (you can see in left side of above figure).

Query Store settings can also be defined using T-SQL Command. As shown in following example.

alter database current set query_store(interval_length_minutes=1)

alter database current set query_store=on

go

So now we know how to enable query store, let us understand how Query Store can help us in solving slow query performance problems.

Let’s create a slow query performance problem using a parameter sniffing scenario in which when the query plan is compiled a parameter is sniffed. This plan may be optimal for few values however it might not be suitable for other values resulting multiple plan.

We will see how Query Store can help us in analyzing this problem and solve it by forcing an optimal plan resulting improved query performance.

In the below script I am going to following things

  • Create a database and enable query store with 1 minute interval
  • Load 10000 unique records starting from 0
  • Create sque data load by loading 100000 records with value “1″
create database querystoredemo
go
use querystoredemo
alter database current set query_store(interval_length_minutes=1)
alter database current set query_store=on
go

create table mytable (col1 int,col2 int, col3 binary(2000));
declare @i int =0
while @i <10000
begin
insert into mytable(col1,col2) values (@i,@i)
set @i+=1
end
--commit transaction
go

insert into mytable (col1,col2) values (1,1)
go 100000

create index i1 on mytable(col1)
create index i2 on mytable(col2)

Now it’s time to fetch the data. I have written small powershell script which will fetch the data in random order by using a random number generator and then frequently we will flush the plan so that it will re-compiled and new parameter will get sniff will cause plan change.

 

<#################################################################################

Script Name: QueryStoreDemo.ps1                       

Author     : Manish Upadhyay                           

Date       : June 29th, 2015

Note:
This Sample Code is provided for the purpose of illustration only and is not intended to be used
in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS”
WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

##################################################################################>

$conn = new-object System.Data.SqlClient.SqlConnection "Data Source=SQLACTIONSVM\SQL2016CTP2;Initial Catalog=querystoredemo;Integrated Security=SSPI;"

$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.commandtext
$cmd.CommandText ="select * from Mytable where col1=@p1 and col2 = @p2"
$cmd.Parameters.Add("@p1",[system.data.SqlDbType]::Int) | Out-Null
$cmd.Parameters.Add("@p2",[system.data.SqlDbType]::Int) | Out-Null

For( $i = 0; $i -le 30000; $i ++ )
{
    $p1= Get-Random -Maximum 100
    $p2=$p1
    $cmd.Parameters["@p1"].value = $p1
    $cmd.Parameters["@p2"].value = $p2
    $StopWatch = New-Object system.Diagnostics.Stopwatch
    $stopWatch.Start()
    $sqlReader=$cmd.ExecuteReader()
    while ($sqlReader.Read())
        {
        }

    $stopWatch.Stop()
    $sqlReader.Close()
    $ts = $StopWatch.Elapsed
    $ElapsedTime = [system.String]::Format("{0:00}:{1:00}:{2:00}.{3:00}",$ts.Hours, $ts.Minutes, $ts.Seconds, $ts.Milliseconds / 10);
    Write-Host "Execution completed time: $elapsedTime p1=$p1 p2=$p2"
    $a=Get-Random -Maximum 100
    if($a -le 2)
        {
            $cmd1 = New-Object System.Data.SqlClient.SqlCommand
            $cmd1.connection = $conn
            $cmd1.CommandText ="dbcc freeproccache"
            $cmd1.ExecuteNonQuery()
            Write-Host "freeproccache"
        }
}
$conn.Close()


When we run it is going to generate the output like below.

1

Now while the script is running let us see how the query store helps us in analyzing this problem. If you go to Query Store folder and select “Top resource consuming queries” it will show all the queries running with duration (there are other options e.g cpu, read, write) and all the plans with plan id’s graphical plan.

11

In this case I see there are 2 plans got generated with 2 different plan id’s (25,26) and while the screen shot above shows you the stats of plan id 25 where avg duration is ~3 sec in the below plan avg duration is ~6 secs with different query plan..

12

This feature also provides the facility to compare both the plans and find out what’s the actual issue. You can click on “Compare plan” option in plan summary above.

13

As we can clearly see one of the plan is going for seek and other one is doing table scan where it’s very slow also asking for a missing index.   Now let’s go to the beauty of this feather where we are going to force a plan and see if that helps. Once you click on force plan it is going to force this plan you can see the icon which indicates this plan id has been forced for this query.

14

As you can see I have force one plan and restarted my query and performance has improved, though we see one more new plan has come in but the duration of this plan is also ~3 sec.

15

This is how you can improve the query performance by using this feature. I hope you now got the idea about how query store works and how it can help us in troubleshooting performance problems. On a closing note as you know every new feature comes with few dmv’s similarly Query Store also has Introduced few DMV/DMF which can be used while troubleshooting.

20

Hope you like this new feature in SQL Server 2016. Stay tuned for some other good features!!

One response to “Query Store: Exploring new features in SQL Server – vNext

Subscribe to comments with RSS.

  1. Very nice blog.

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: