Posts

Today I heard a great analogy, and it inspired this post. Execution plans are like flight plans, there are multiple flight paths that can be taken to get from where you are to your ultimate destination. Some plans are more efficient than others, and we want to use those whenever possible. How can I make sure I am using the plan I want to use? Let’s talk about air traffic control, the Query Store.

What is the Query Store?

Starting in SQL Server 2016, Query Store is a native tool that tracks query execution data including execution statistics and execution plans. Query store gives you insight to SQL Server performance issues. It provides graphical views in SSMS and dynamic management views to assist in identifying problem queries.

Reasons to give it a try?

  • Knowing what queries are the most expensive
  • Seeing all query executions and whether or not they have regressed based on code changes
  • Ability to force specific plans for problem queries

Turning it on is a no brainer for many environments, but if your workload is mostly ad hoc you may not see the same benefit as environments that focus more on stored procedure execution

Getting Started with Query Store

Installation

Since this is a database level setting and not a server level setting, you will have to perform these actions on each database to enable the Query Store. After the initial work to get this enabled, you do have the option to enable it on the model system database and every new database that gets created will also get this setting enabled so you don’t have to worry about doing it later. There are two ways to get Query Store enabled in your environment.

The fastest way to get this enabled on a long list of databases would be to use T-SQL or PowerShell. For PowerShell, I highly recommend utilizing dbatools.io. It’s free and full of amazing documentation.

ALTER DATABASE [StackOverflow2010] SET QUERY_STORE = ON; 
ALTER DATABASE [StackOverflow2013] SET QUERY_STORE = ON; 
Enable Query Store with T-SQL
T-SQL
Set-DbaDbQueryStoreOption -SqlInstance ServerA -Database StackOverflow2010, StackOverflow2013 -State ReadWrite
Enabling Query Store with DBATools.io and PowerShell.
PowerShell using dbatools.io/Set-DbaDbQueryStoreOption

You can also use the user interface to enable this feature. Right click the database and then open the properties. From here, change the operation mode from off to read write. We will go over additional settings to consider in the section below that can also be updated in this view.

Enabling Query Store with SSMS Graphical interface.
UI

Settings to Consider

To implement any of the below settings, you can use the UI like the screenshot above, dbatools for PowerShell, or T-SQL (which I will include below)

Max Size is exactly what it sounds like, a limit to how large you want your query store to grow. Most of the settings below will affect how much space is required, but a good default to work from would be 1GB, adjusting based on the below settings. If the query store runs out of space, it will go from read write to read only and stop gathering query metrics.

Data Flush Interval is used to control how often Query Store goes from memory and gets persisted to disk. When considering this setting, think about how much data you are willing to lose from query store if you have a DR situation arise.

Statistics Collection Interval decides how often to collect metrics. The more often you collect affects how much space you need, but also provides greater levels of granularity when looking into issues in the environment.

Stale Query Threshold controls how often to clean up old data and inactive queries. This setting directly impacts how much space is required for query store.

Query Store Capture Mode determines what queries are captured in query store. Typically this is set to All if you want to capture every query or to Auto if you are ok with SQL ignoring insignificant queries based on execution counts, compile and run times.

Well it’s on, now what?

Stay tuned for additional blogs in my Query Store series to cover basic built in reporting, using query store to assist in tuning, and more advanced features!

The post Query Store 101 appeared first on dbWonderKid.

While not all implicit conversions are equal implicit conversions matter. For example, just this week I ran into a trivial code change that caused some severe regression in performance. One simple filter change caused a query to consume 227 million reads compared to 212 reads. What did the filter do? Well, it forced an implicit conversion.

In this blog post you will learn what is an implicit conversion, how to identify it, and also how do you find the implicit conversion leading to the highest amount of reads in the query store.

What is a SQL Server Implicit Conversion?

This is a great question. The answer is when SQL Server optimizer has to convert data in a column to match the type you are using in your query. For example, if you had a column that was an Integer and you decided to filter off of a string “varchar” variable. ORM’s like Entity Framework is notoriously known for this. You would end up forcing an implicit conversion. This will force the optimizer to read every value in the column and cast it so the column can then be filtered based off your variable or another column that doesn’t match the same data type.

Let’s look at an example. Yes, this is exactly similar to the example mentioned in the first paragraph that consumed 227 million reads. We are just going to simplify it down for you with a copy of the AdventureWorks modified database.

SalesOrderID column is an INT, not VARCHAR. We are going to filter with a VARCHAR data type using the LIKE operator as shown below.

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[SalesOrderDetailEnlarged]
  WHERE SalesOrderID LIKE '43659'

This will produce the following execution plan and statistics which is not ideal at all. We will get a warning for the implicit conversion and also go parallel scanning the whole table due to this conversion.

Implicit Conversion Forces Table Scan
A SQL Server Implicit Conversion Forces a Table Scan
Implicit Conversions hurt performance
SQL Server Implicit Conversions causing the whole table to be read.
statistics for our implicit conversion
The following is statistics for our implicit conversion

How to fix Implicit Conversions in SQL Server

Now that we know how to identify an implicit conversion I bet you might be wondering how do you resolve them? They usually come in two flavors. First, the columns do not match data types on a join. Second, the parameter or literals data type does not match with the column. Today, we will focus on the second time in the code below.

The only thing we change in our code is the type of filter.

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[SalesOrderDetailEnlarged]
  WHERE SalesOrderID = 43659
Parameter matches column data type
The literal parameter matches with the column data type.
Gone from 76k reads to 3!

How do I get Implicit Conversions from the Query Store?

Now that you understand implicit conversions you might wonder how do I find them? Well, hopefully, you are on SQL Server 2016 or higher and can query the execution plans in query store. If not, check out Jonathan Kehayias example to find implicit conversions in the plan cache.

Disclaimer: Plan cache will only have execution plans that are still in memory. Query store will only have execution plans that are in the query store. Also, I added variables for the start date, end date, and top offenders. Performance milage will vary based on how your query store is configured and used. run the query below at your own risk!

DROP TABLE IF EXISTS #plans
DROP TABLE IF EXISTS #tmp1
DROP TABLE IF EXISTS #tmp2
DROP TABLE IF EXISTS #finalplan
GO

DECLARE @StartDate datetime = dateadd(d,-1, getdate()),
 @EndDate datetime = getdate(), 
 @TopStmts INT = 1000

SELECT TOP (@TopStmts)
	SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
   qsp.plan_id
INTO #tmp1
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
WHERE qrs.first_execution_time >= @StartDate 
AND qrs.last_execution_time <= @EndDate
group by qsp.plan_id
ORDER BY est_logical_reads DESC 


SELECT cte.*, TRY_CAST (qsp.query_plan AS XML) AS xml_query_plan 
INTO #tmp2
FROM #tmp1 cte
JOIN sys.query_store_plan qsp ON cte.plan_id = qsp.plan_id


SELECT #tmp2.* 
INTO #plans
FROM #tmp2;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME());

;WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT p.est_logical_reads, p.sum_executions, p.avg_logical_io_reads, p.plan_id
,   stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQLStmt, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName, 
 ic.DATA_TYPE AS ConvertFrom, 
 ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   p.xml_query_plan
   INTO #finalplan
   FROM #plans p
CROSS APPLY xml_query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
CROSS APPLY stmt.nodes('.//PlanAffectingConvert[@ConvertIssue="Cardinality Estimate"]') AS ce(pt) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1


SELECT fp.est_logical_reads, fp.sum_executions, fp.avg_logical_io_reads, fp.plan_id, 
qsq.query_id, qsq.query_hash, fp.SQLStmt, fp.SchemaName, fp.TableName, 
fp.ColumnName, fp.ConvertFrom, fp.ConvertTo, fp.ConvertFromLength, fp.ConvertToLength, fp.xml_query_plan
FROM #finalplan fp
JOIN sys.query_store_plan qsp ON fp.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
ORDER BY fp.est_logical_reads DESC	

How do I fix this?

If you found this page you most likely either found yourself in the middle of a SQL Server parameter sniffing and/or data skew issue and have no idea how to get yourself out of this hole. In the eight-minute video below we go over exactly what parameter sniffing and data skew is and how you can identify it on your own.

SPOILER ALERT: Parameter sniffing is mostly an amazing thing that saves tons of CPU cycles. Like many DBA tasks, this usually only gets attention when expectations are not met. Most likely you have these issues in your database without even knowing it. See a real-world case where data skew was resolved with a customer using SQL Server 2017 without changing any code.

Learn how to identify parameter sniffing and data skew and fix it on your own.

How do we fix SQL Server parameter sniffing?

Now that you know what parameter sniffing and data skew is our next blog post will go over using Query Store and Automatic Tuning to enhance your performance automatically or manually. This will resolve queries that regressed due to parameter sniffing.

What Are Others Say about Parameter Sniffing?

Here are some classic posts from Microsoft on Parameter Sniffing (PFE blog at Microsoft. What is Parameter Sniffing )

Here is a great article from our partners at SentryOne.

Have you ever had a query that runs frequently start to randomly go slower? We all have and without the proper tools and knowledge, this can be hard to figure out because there can be so many different reasons. In this blog post, our goal is to make your query optimization in SQL Server a lot better and easier for you.

How does a SQL Server query get proccessed?

This is a great question. All queries get put into a queue if they are not available to run. If there are any available schedulers then the query will start processed. We call this the RUNNING state. If the RUNNING query needs to pause to obtain any required resource like pages from disk or is blocked this query will have SUSPENDED state. Once the required pause is no longer needed, the query status will go back into the queue and its status will now be “RUNNABLE”. This queue is first in first out (FIFO) and once the query moves up the queue and a schedule is free the query is back in motion and has the “RUNNING” status again. This circle can be repeated multiple times until the query execution completes.

Hopefully, our flow process of the status of a query will make sense below.

The cycle of states for a running query.

How do we see the waits for my query?

Now that you understand how a query waits. Lets go ahead and look at how we recommend reviewing this data. Before SQL Server 2017 you had to a bit of work with extended events and dynamic management views. Now we can use our best friend, the query store.

In this video below you will learn how to figure out what waits occurred when your query was executed inside of SSMS without writing a single line of code.

Lets look and see why your query is running slow…

What are your thoughts about getting quick access to waits going on for your queries? Got aditional questions? Let us know in the comments section down blow.

Can I get the demo code?

We know some of you love to replay demos and we like to give you the code to do so when you have free time. If you have any questions with the demo below fill free to throw your questions our way.

USE [master];
GO
ALTER DATABASE [WideWorldImporters] 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
					DATA_FLUSH_INTERVAL_SECONDS = 60, 
					QUERY_CAPTURE_MODE = ALL, /* For demo purposes.
					most likely don't want to capture all in prod */
					INTERVAL_LENGTH_MINUTES = 1);
GO

USE [WideWorldImporters];
GO
DBCC DROPCLEANBUFFERS; -- For demo to get PAGEIOLATCH waits
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;


/* Verify Clean Starting Point */
select * from sys.query_store_plan
select * from sys.query_store_query
select * from sys.query_store_runtime_stats
select * from sys.query_store_wait_stats /* New in SQL 2017 */
GO


USE [WideWorldImporters];
GO

/* Load all records from disk into memoery 
	because we dropped clean buffers */
SELECT * FROM Sales.Invoices

/* 102 records */
SELECT * FROM Sales.Invoices
WHERE CustomerID = 832


/* Create locking example.
Begin explicit transaction 
but don't commit or rollback yet */
BEGIN TRANSACTION

UPDATE Sales.Invoices
SET DeliveryInstructions = 'Data Error'
WHERE CustomerID = 832

-- Hold off on rolling back until after the block


/* Now back in another window, 
wait a few seconds and then 
execute the following statement */
USE [WideWorldImporters];
GO
SELECT COUNT(DeliveryInstructions)
FROM Sales.Invoices
WHERE CustomerID = 832


-- After a few seconds - roll back the other transaction
/* Now Rollback and show other window has data */
ROLLBACK TRANSACTION
/* Flushes the in-memory portion of the Query Store data to disk. 
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-flush-db-transact-sql?view=sql-server-2017
*/
USE [WideWorldImporters];
GO
 
EXECUTE sp_query_store_flush_db;
GO

/* 
Look at all queries in QS using Sales.Invoices.
Notice our UPDATE is missing??
We Rolled it BACK
*/
SELECT [q].[query_id], *
FROM sys.query_store_query_text AS [t]
INNER JOIN sys.query_store_query AS [q]
	ON [t].query_text_id = [q].query_text_id
WHERE query_sql_text LIKE '%Sales.Invoices%';

/* get the query_id from statement above
use it to get the plan_id so we can look at its waits */
declare @queryID bigint = 7 
SELECT *
FROM sys.query_store_plan
WHERE query_id = @queryID;

/*Get all waits for that plan. */
begin
declare @planid int = 7
select * from sys.query_store_runtime_stats_interval

SELECT * --wait_category_desc, avg_query_wait_time_ms
FROM sys.query_store_wait_stats
WHERE	plan_id = @planid AND
		wait_category_desc NOT IN ('Unknown')
ORDER BY avg_query_wait_time_ms DESC;
end
GO

/* Make sure you filter for time intervals needed */
declare @planid int = 7
SELECT wait_category_desc, avg_query_wait_time_ms
FROM sys.query_store_wait_stats
WHERE	plan_id = @planid AND
		wait_category_desc NOT IN ('Unknown')
ORDER BY avg_query_wait_time_ms DESC;
GO

/* Look at all waits in system */
select * from sys.query_store_wait_stats

In SQL Server 2016 we saw Query Store.  Query Store was a game changer to help database administrators identify troublesome queries. Query Store helps DBAs make those queries run faster.  Microsoft’s marketing team even jumped on to help coin the phrase, “SQL Server It Just Runs Faster.” With SQL Server 2017, this started to get even better with automatic tuning. Don’t worry database administrators.  Automatic Tuning will just enhance your career and not replace it.

SQL Server 2017 Automatic Tuning looks for queries where execution plans change and performance regresses. This feature depends on Query Store being enabled. Note, even if you don’t turn on Automatic Tuning you still get the benefits of having access to the data. That is right. Automatic Tuning would tell you what it would do if it was enabled.  Think of this as free performance tuning training.  Go look at the DMVs and try to understand why the optimizer would want to lock in an execution plan. We will actually go through a real-world example:

Automatic Tuning with SQL Server 2017

First, let’s take a quick look at the output of the data. You can find the query and results we will focus on below.

SELECT reason, score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
planForceDetails.*,
estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)
*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
[current plan_id] int '$.regressedPlanId',
[recommended plan_id] int '$.recommendedPlanId',

regressedPlanErrorCount int,
recommendedPlanErrorCount int,

regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float

) as planForceDetails;

I will break the results down into two photos to make them fit well in this blog post.

Free Tuning Recommendations with Automatic Tuning in SQL Server 2017

Free Tuning Recommendations with SQL Server 2017 (1/2)

Automatic Tuning Results in SQL Server 2017

Free Tuning Recommendations with SQL Server 2017 (2/2)

Now we know in the query store query_id 2271 has CPU time changing from 7,235ms to 26ms. That’s a big difference. Let’s take that query and look at its findings by using the tracked query report inside SSMS.

Query Store find history of a query

Find my Changed Query. Did the plans change?

Here we can see the major difference between the two execution plans. One is averaging over 14 seconds in duration while the other is under a second.

Reviewing Query performance in Query Store

Query Store showing the performance difference between the two plans

Now we can select both plans on the screen above and look at the execution plans side by side inside of SSMS. When doing so, we see the common example of the optimizer determining if it is better to scan an index vs a seek with a key lookup.

Side by Side Execution Plan Review in SSMS.

Using SSMS to compare auto tuning recommended query.

To complete the example I want to point out that automatic tuning would lock in the index seek plan (Plan 2392). In SQL Server 2016 you can do this as well manually inside Query Store. With SQL Server 2017 it can be done automatically for you with Automatic Tuning. If you have ever woken up to slow performance due to an execution plan changing and performance going through the drain this might be a life saver.

If you would like to learn about performance tuning changes in SQL Server 2016 and 2017 sign up for our newsletter or catch me talking about these features at SQL Saturday Denver and SQL Saturday Pittsburgh this month.  If you need any help with tuning or upgrading contact us. We would love to chat with you!

 

The post SQL Server Automatic Tuning in the Real-World appeared first on SQL Server Consulting & Remote DBA Service.

Last weekend I had a blast speaking at the SQL Saturday in Chicago. It was awesome to share my knowledge and also catch up with some good friends.  My talk was on Automating the Pain Away with Query Store and Automated Tuning.  I hope this session helped people understand Query Store and Automated Tuning as these two new features can help resolve parameter sniffing problems.

John Sterrett Teaching Performance Tuning

John Sterrett teaching Performance Tuning at SQL Saturday Chicago

John Sterrett teaching performance tuning

SQL Saturday Chicago training class

The post SQL Saturday Chicago appeared first on SQL Server Consulting & Remote DBA Service.