Workload Tuning Training

I recently booked my flight to the U.K. for SQLBits, and I realized I probably should write a post about my full day training session on workload tuning! It’s on Wednesday, February 27, 2019, and you can read the abstract here: Stop Tuning Your Queries and Start Tuning Your Workload.

I included a fair bit of information about what I’ll cover, so I thought it might be interesting to anyone considering this session to hear the backstory about how it came to be.
First, this is a new full day session, I haven’t presented it anywhere else previously. It does have pieces of content I’ve discussed elsewhere, but this really represents a different approach for tuning and troubleshooting. Namely: let’s not focus on one or two or five specific queries that are causing problems, let’s focus on the workload as a whole to identify patterns and then address them in specific queries.

What I have noticed over the past couple years is that I see similar patterns when I’m looking at a given system. Very often, within a solution there are patterns in terms of how queries are written, typical performance problems, and then typical ways they are addressed or fixed internally.

For example, we engage with a customer and discover they use table variables in a lot of their code, and this causes poor cardinality estimates and then a host of subsequent issues. We optimize a couple queries using temporary tables instead, and they then replace all table variables with temporary tables, and then start seeing issues related to temp table contention. Both options have a time and a place, the key is to understand when and why.

The ultimate goal of my session is to share my experience working with a wide range of customers and solutions over the years, the patterns I have seen, and options for addressing them. I’ll tell you now that there is no “easy button” in terms of fixes. But you already knew that 😊 A big key is figuring out how to convince your team of the changes that need to be made, and understanding how and why is critical. That’s what we’ll discuss, because I want you to walk away with applicable information that you can use immediately in your environment.

Still have questions? Add a comment or send me an email. I’d love to hear from you. I hope to see you in Manchester in Feburary!

p.s. I should also mention that next week (Jan 15 – Jan 17) I’m presenting my Query Store course online, and it’s the only delivery of it scheduled for the first part of the year. There is still space if you’re interested! Three afternoons next week, each day with two 1.5 hour sessions. I really like this format, it gives people good breaks to absorb information and think of/ask questions.

Finding the Slowest Query in a Stored Procedure

This post, Finding the Slowest Query in a Stored Procedure, originally appeared on the PASS website.  It has been re-posted here with permission.

Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights.  It’s not uncommon to see procedures with hundreds, even thousands of lines of code.  You may have been told which stored procedure runs slow by a user or manager, or you might have found it by looking in SQL Server DMVs.  Either way, once you have detected the offending procedure, where do you start?

If you’re running SQL Server 2016, one option is Query Store.  Query Store captures individual queries, but it also captures the object_id, so you can find all the queries that are associated with an object to determine which ones are problematic.

Setup

We’ll work with a restored copy of the WideWorldImporters database, which you can download from GitHub.

First, since this is a demo database, we will enable Query Store and clear out any historical information:

ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE (
     OPERATION_MODE=READ_WRITE,
     INTERVAL_LENGTH_MINUTES = 10
     );
GO

/*
Do not run in a Production database unless you want
to remove all Query Store data
*/

ALTER DATABASE [WideWorldImporters]
    SET QUERY_STORE CLEAR;
GO

Next, we will create a very simple stored procedure that has multiple queries within.

USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Sales].[usp_GetCustomerDetail];
GO

CREATE PROCEDURE [Sales].[usp_GetCustomerDetail]
     @CustomerName NVARCHAR(100)
AS

CREATE TABLE #CustomerList (
     [RowID] INT IDENTITY (1,1),
     [CustomerID] INT,
     [CustomerName] NVARCHAR (100)
     );

INSERT INTO #CustomerList (
     [CustomerID],
     [CustomerName]
     )
SELECT
     [CustomerID],
     [CustomerName]
FROM [Sales].[Customers]
WHERE [CustomerName] LIKE @CustomerName
UNION
SELECT
     [CustomerID],
     [CustomerName]
FROM [Sales].[Customers_Archive]
WHERE [CustomerName] LIKE @CustomerName;

SELECT
     [o].[CustomerID],
     [o].[OrderID],
     [il].[InvoiceLineID],
     [o].[OrderDate],
     [i].[InvoiceDate],
     [ol].[StockItemID],
     [ol].[Quantity],
     [ol].[UnitPrice],
     [il].[LineProfit]
INTO #CustomerOrders
FROM [Sales].[Orders] [o]
INNER JOIN [Sales].[OrderLines] [ol]
     ON [o].[OrderID] = [ol].[OrderID]
INNER JOIN [Sales].[Invoices] [i]
     ON [o].[OrderID] = [i].[OrderID]
INNER JOIN [Sales].[InvoiceLines] [il]
     ON [i].[InvoiceID] =  [il].[InvoiceID]
     AND [il].[StockItemID] = [ol].[StockItemID]
     AND [il].[Quantity] = [ol].[Quantity]
     AND [il].[UnitPrice] = [ol].[UnitPrice]
WHERE [o].[CustomerID] IN (SELECT [CustomerID] FROM #CustomerList);

SELECT
     [cl].[CustomerName],
     [si].[StockItemName],
     SUM([co].[Quantity]) AS [QtyPurchased],
     SUM([co].[Quantity]*[co].[UnitPrice]) AS [TotalCost],
     [co].[LineProfit],
     [co].[OrderDate],
     DATEDIFF(DAY,[co].[OrderDate],[co].[InvoiceDate]) AS [DaystoInvoice]
FROM #CustomerOrders [co]
INNER JOIN #CustomerList [cl]
     ON [co].[CustomerID] = [cl].[CustomerID]
INNER JOIN [Warehouse].[StockItems] [si]
     ON [co].[StockItemID] = [si].[StockItemID]
GROUPBY [cl].[CustomerName], [si].[StockItemName],[co].[InvoiceLineID],
     [co].[LineProfit], [co].[OrderDate], DATEDIFF(DAY,[co].[OrderDate],[co].[InvoiceDate])
ORDER BY [co].[OrderDate];
GO

Viewing the Data

With the stored procedure created, we can now execute it with different input parameters:

EXEC [Sales].[usp_GetCustomerDetail] N'Alvin Bollinger';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Tami Braggs';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Logan Dixon';
GO 10
EXEC [Sales].[usp_GetCustomerDetail] N'Tara Kotadia';
GO 10

If we use the Query Store Top Resource Consuming Queries report within Management Studio, we can see what individual queries have been executed:

Top Resource Consuming Queries

Top Resource Consuming Queries Report

In this example, the queries for our stored procedure are quite evident – but we don’t have much of a workload.  In a production system, all the queries from a stored procedure probably won’t appear in this view. It will likely be necessary to use T-SQL to find all the queries for a stored procedure, which currently has no built-in report to find all queries for a given stored procedure.  Using the query below, we can list all queries captured by Query Store for the usp_GetCustomerDetail procedure:

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     [qsq].[object_id],
     [qst].[query_sql_text],
     ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
     ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
     ON [qsq].[query_id] = [qsp].[query_id]
WHERE [qsq].[object_id] = OBJECT_ID(N'Sales.usp_GetCustomerDetail');
GO
All queries for usp_GetCustomerDetail

All queries for usp_GetCustomerDetail

This confirms which queries are part of the procedure – but if we are troubleshooting performance, we are really interested in execution statistics.  We can expand this query slightly to get the data we want:

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     [qsq].[object_id],
     [rs].[runtime_stats_interval_id],
     [rsi].[start_time],
     [rsi].[end_time],
     [rs].[count_executions],
     [rs].[avg_duration],
     [rs].[avg_cpu_time],
     [rs].[avg_logical_io_reads],
     [rs].[avg_rowcount],
     [qst].[query_sql_text],
     ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
     ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
     ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
     ON [qsp].[plan_id] = [rs].[plan_id]
JOIN [sys].[query_store_runtime_stats_interval] [rsi]
     ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]
WHERE [qsq].[object_id] = OBJECT_ID(N'Sales.usp_GetCustomerDetail')
     AND [rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())
     AND [rs].[execution_type] = 0
ORDER BY [qsq].[query_id], [qsp].[plan_id], [rs].[runtime_stats_interval_id];
GO
Runtime statistics for usp_GetCustomerDetail

Runtime statistics for usp_GetCustomerDetail

We’ve only retrieved CPU, duration, and IO metrics in this query – much more information is available in Query Store depending on what you’re interested in seeing, or the problem you are trying to solve.  A quick review of the duration and CPU columns helps us identify the second query in the stored procedure as the one that takes the longest to execute.

There are a few important items worth noting about the query we ran, and its output:

1. We restricted the runtime statistics to only those from the last hour with this predicate:
[rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())

2. We restricted the output to show only successful executions with this predicate:
[rs].[execution_type] = 0

3.  Runtime statistics are aggregated across the collection interval, which is set with the INTERVAL_LENGTH_MINUTES option.  For this demo, the interval was set to 10 minutes, a value that’s low for a production environment, and we’re only seeing data for one 10-minute interval (19:30 to 19:40 on 2018-11-28) because we’ve only run our set of queries one time.

*If you’re not familiar with Query Store settings and considerations around them, check out my Query Store Settings post.

4.  The statistics represent the average CPU, duration, logical I/O, etc. for a query’s plan, based on how many times the query was executed in that interval (count_executions).  Minimum, maximum, and standard deviation numbers for each counter are also calculated and can be useful in finding plans with high variability.

From here, we can drill further into this specific query using the Query Store Tracked Queries report and the query_id (2).

Tracked Queries Report for query_id 2

Tracked Queries Report for query_id 2

Again, we’ve only executed the stored procedure 40 times in total, so while it’s great to see this query’s plan and see that we have a missing index recommendation, this data isn’t that interesting compared to what we might see in a production environment.  We can use a simple loop to run the stored procedure repeatedly with different Customer Names, and let that run for a bit to generate some data:

DECLARE @CustomerID INT = 801
DECLARE @CustomerName NVARCHAR(100)

WHILE 1=1
BEGIN

     SELECT @CustomerName = SUBSTRING([CustomerName], 1, 10) + '%'
     FROM [Sales].[Customers]
     WHERE [CustomerID] = @CustomerID;

     EXEC [Sales].[usp_GetCustomerDetail] @CustomerName;

     IF @CustomerID < 1092
     BEGIN
          SET @CustomerID = @CustomerID + 1
     END
     ELSE
     BEGIN
          SET @CustomerID = 801
     END

END

Now if we look at our execution statistics, you will see we have more intervals of data (noted by the start_time and end_time, and the respective runtime_stats_interval_id).  In addition, you’ll note that query_id 3 has three plans (plan_ids 3, 16, and 18):

Runtime Statistics for usp_GetCustomerDetail after additional executions

Runtime Statistics for usp_GetCustomerDetail after additional executions

Looking at query performance across time is not that easy in the grid output.  If we look at any individual query again in the Tracked Queries report for a 30-minute window, we get a much better view of the data:

Tracked Queries Report for query_id 3

Tracked Queries Report for query_id 3

Important points about this output:

1. This is query_id 3, which has three different plans, which are represented by the different plan_id values on the right, and different colors on the graph.

2.  The circles represent the average values for successful execution.  The squares represent canceled executions (which we excluded in our query).  If you see a triangle, that means an error in the execution.

3.  Clicking on the different plan_id values displays the corresponding query plan in the bottom window.  Hold down the SHIFT key to select two plan_id values, then use the ComparePlans button above to open a new window and see both plans side-by-side.

Even though this view allows us to trend individual query performance over time, we don’t get a good sense of overall query performance relative to other queries in the stored procedure.  We have to go back to T-SQL for that, and for this example, we’ll aggregate our metrics based on the query_id and the plan­_id, as a query can have multiple plans and they may have wildly different performance characteristics.

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     OBJECT_NAME([qsq].[object_id])AS [ObjectName],
     SUM([rs].[count_executions]) AS [TotalExecutions],
     AVG([rs].[avg_duration]) AS [Avg_Duration],
     AVG([rs].[avg_cpu_time]) AS [Avg_CPU],
     AVG([rs].[avg_logical_io_reads]) AS [Avg_LogicalReads],
     MIN([qst].[query_sql_text]) AS[Query]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
     ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
     ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
     ON [qsp].[plan_id] = [rs].[plan_id]
JOIN [sys].[query_store_runtime_stats_interval] [rsi]
     ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]
WHERE [qsq].[object_id] = OBJECT_ID(N'Sales.usp_GetCustomerDetail')
     AND [rs].[last_execution_time] &gt; DATEADD(HOUR, -1, GETUTCDATE())
     AND [rs].[execution_type] = 0
GROUP BY [qsq].[query_id], [qsp].[plan_id], OBJECT_NAME([qsq].[object_id])
ORDER BY AVG([rs].[avg_cpu_time]) DESC;
GO

The data is ordered by avg_cpu_time descending, so the worst performing query in terms of CPU is listed first.  You can change the order based on what is of interest to you, but you’ll notice that query_id 2 is still our worst offender.

Averaged runtime statistics by query for usp_GetCustomerDetail

Averaged runtime statistics by query for usp_GetCustomerDetail

Conclusion

There are multiple sources of query performance data within SQL Server, and the DMVs and Extended Events (or Trace for those of you running SQL Server 2008R2 and below) are traditional tools used to understand how long it takes for a query to execute.  Query Store is a new option for capturing query metrics, and it shines over both in that it’s persisted in the user database (the DMVs only represent what’s currently in cache, and are cleared on a restart).  In addition, Query Store doesn’t require any setup other than enabling it one time – you need to have an event session defined and running to capture query performance with Extended Events, and the same with Trace.  Lastly, Query Store allows you to look at all the queries for a stored procedure and drill into what is not performing well either at this moment, or over time.

T-SQL code used here can be found in its entirety on GitHub.

Do you need to update statistics after an upgrade?

This post originally went live on May 11, 2018, but modifications were made on May 14, 2018 after some additional internal discussions with Microsoft.  Changes made on May 14, 2018 are in blue. 

There are a variety of methods we use for helping customers upgrade to a new SQL Server version, and one question we often get asked is whether or not statistics need to be updated as part of the upgrade process.

tl;dr

Yes.  Update statistics after an upgrade. Further, if you’re upgrading to 2012 or higher from an earlier version, you should rebuild your indexes (which will update index statistics, so then you just need to update column statistics).

History

Some of you may remember that the stats blob changed between SQL Server 2000 and SQL Server 2005, and Microsoft specifically recommended updating statistics after upgrading from SQL Server 2000.  Official Microsoft documentation about the stats blog change in SQL Server 2005 is difficult to find, but this article includes the following paragraph:

After you upgrade from SQL Server 2000, update statistics on all databases. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases. This step is not necessary for upgrading from SQL Server 2005.

Current Microsoft documentation related to upgrading does not state anything specific about updating statistics, but people continue to ask and if you peruse forums, blog posts, and other social media options, you’ll see recommendations to update statistics. Further, the documentation that Microsoft provides about when to update statistics does not mention anything about upgrades.

Side bar: I don’t recommend using sp_updatestats, and here’s why: Understanding What sp_updatestats Really Updates.

Today

The statistics blob has not changed since SQL Server 2000 to my knowledge, but I thought I would ask someone from Microsoft for an official recommendation to share publicly.  Here you go:

Microsoft suggests that customers test the need for a full update of statistics after a major version change and/or a database compatibility level change.

Further items to note:

  1. If Microsoft updates the format of statistics (e.g. the stats blog), customers will be expected to update statistics after an upgrade.
    1. Microsoft does not always upgrade the statistics format as part of a major version upgrade.
  2. There are occasions where Microsoft does not change the format of statistics, but they do change the algorithm for creating statistics as part of a major version upgrade or database compatibility level change.

In addition, there was a change in the nonclustered leaf level internals in SQL Server 2012, so if you are upgrading to 2012 or higher from an earlier version (e.g. 2008, 2008R2), rebuild your nonclustered indexes.  And remember, rebuilding indexes updates the statistics for those indexes with a fullscan, so you do not need to update them again.

Conclusion

As part of your upgrade methodology, it is recommended (by me, based on experience with a lot of customer upgrades) to build in time to update statistics.  I’ve gotten some pushback from customers who don’t want to update statistics after upgrade because it takes too long.  Some kind reminders:

  • Updating statistics is an online operation, therefore, the database and related applications are accessible and usable. A statistics update does take a schema modification lock so you’re not allowed to make any changes to a table while its stats are updating.  Therefore, if you decide to change your schema after upgrading your SQL Server version (not something I would typically recommend), do that before you update stats.
  • You need to update statistics regularly to provide the optimizer with current information about your data, so at some point it needs to be done. Immediately after an upgrade is a really good time, considering the aforementioned items.

If you’re not comfortable upgrading to a newer version of SQL Server, we can help!  I’m in the process of helping a customer migrate from SQL Server 2012 to SQL Server 2017, and I’m so excited to get them up to the latest version so they can start using some new features…like Query Store 😉