Couple of weeks ago, I blogged about how I tried to use AI (Claude) to teach me performance tuning. You can read it here.
Starting from this blog I'm going to share those exercises and what I have learned from them.
I'm modifying what Claude has outputted to looks like it is actual exercise in here.
Setting Up Data
Below assume you have already downloaded and setup some version of AdventureWorks sample database by Microsoft.
The scenario runs on a table modelled after AdventureWorks sales data, but we need to build an inflated copy, so the performance difference is actually felt rather than just theoretical — the stock SalesOrderHeader is only ~31K rows, which is too small to show anything meaningful.
Step 1: Create "SalesOrders" table.
IF OBJECT_ID('dbo.SalesOrders') IS NOT NULL DROP TABLE dbo.SalesOrders;
GO
CREATE TABLE dbo.SalesOrders
(
SalesOrderID INT IDENTITY(1,1) NOT NULL,
OrderDate DATETIME NOT NULL,
CustomerID INT NOT NULL,
TotalDue MONEY NOT NULL,
OrderStatus TINYINT NOT NULL,
OnlineOrderFlag BIT NOT NULL,
CONSTRAINT PK_SalesOrders PRIMARY KEY CLUSTERED (SalesOrderID)
);
GO
Step 2: Insert lot of dummy data, by cross joining sys.all_objects.
INSERT INTO dbo.SalesOrders (OrderDate, CustomerID, TotalDue, OrderStatus, OnlineOrderFlag)
SELECT TOP (1500000)
DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 2920, '2019-01-01'), -- ~8 years of dates
ABS(CHECKSUM(NEWID())) % 20000 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS MONEY),
ABS(CHECKSUM(NEWID())) % 8 + 1,
ABS(CHECKSUM(NEWID())) % 2
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO
Step 3: Create a index which simulate real world scenario where users already have some indexes.
CREATE NONCLUSTERED INDEX IX_SalesOrders_OrderDate
ON dbo.SalesOrders (OrderDate)
INCLUDE (CustomerID, TotalDue);
GO
Step 4: Create the stored procedure which we will be tuning.
CREATE OR ALTER PROCEDURE dbo.GetOrdersByYear
@Year INT
AS
BEGIN
SET NOCOUNT ON;
SELECT SalesOrderID, OrderDate, CustomerID, TotalDue
FROM dbo.SalesOrders
WHERE YEAR(OrderDate) = @Year
ORDER BY OrderDate;
END
GO
Scenario
There's a "Sales by Year" report page in the app that calls dbo.GetOrdersByYear with a single year, e.g. EXEC dbo.GetOrdersByYear @Year = 2022;.
Users are complaining that this report is sluggish and keeps getting slower the longer the system's been live — it used to feel snappy when the table was small, now it's a few seconds and climbing.
The DBA is confused because there's a perfectly good index sitting on OrderDate that includes the exact columns the query returns, yet it doesn't seem to be helping. CPU also ticks up noticeably whenever the report runs, even though each call only returns one year's worth of rows.
Exercise
Run the proc with the actual execution plan on and SET STATISTICS IO, TIME ON, then come back with three things:
- The root cause (why that index isn't being used the way you'd hope)
- Your fix (the rewritten proc)
- The evidence that it worked (before/after logical reads, plan operator change, and duration)
Keep the proc's signature the same; the caller shouldn't have to change how it invokes it.
The thing to watch for: the fix here shouldn't require adding any new index. The right index already exists — the query just isn't letting SQL Server use it properly.
-----------------------------------------
I suggest you give a shot at above exercise. Then look for my solution below.
-----------------------------------------
First thing I did was, as per instructions execute and see how it works currently.
SET STATISTICS IO, TIME ON
GO
exec dbo.[GetOrdersByYear] '2022'
I got following execution plan:
As you can see it is using the index, but doing an index scan operation.
Stats as follows:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'SalesOrders'. Scan count 1, logical reads 5597, physical reads 1, page server reads 0, read-ahead reads 5603, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 3593 ms.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 3595 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
It was not bad, just 4 seconds for report query? I was wondering. But as mentioned on the exercise text, this is increasingly taking longer. So it seems like, more data on the table it gets more time to execute.
Looking at the execution plan it was obvious why that is happening. More data, more time to scan.
So why does it scan, when you have a index on date column and it is a covering index (i.e. provide all columns query need)?
So, I opened up the stored procedure to check. That's when I realized the issue.
Query is not SARGable. Input parameter was integer (year), so SQL server had to find the year using a "YEAR" function, which made query not SARGable. This is why index scan was used. Because SQL server couldn't predict which rows to seek into.
Now that I have understood issue, fix was to make the query more SARGable. Simlest technique is converting the incoming parameter into a date range and query the date rage. When using date rage, SQL server will be able to seek into the index correctly.
Here is my solution
ALTER PROCEDURE [dbo].[GetOrdersByYear_MDA]
@Year INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @startDate datetime = DATEFROMPARTS(@Year, 1, 1)
DECLARE @endDate datetime = DATEFROMPARTS(@Year + 1, 1, 1)
SELECT SalesOrderID, OrderDate, CustomerID, TotalDue
FROM dbo.SalesOrders
WHERE OrderDate >= @startDate AND OrderDate < @endDate
ORDER BY OrderDate;
END
New execution plan:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'SalesOrders'. Scan count 1, logical reads 705, physical reads 2, page server reads 0, read-ahead reads 709, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 3366 ms.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 3373 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Note that logical reads are down from 5597 to 705 and CPU time is 32 ms against 219 ms in previous round. Although network IO time was bit same and query executed about same time.
To be honest in the first round I used "Date" variables rather than "DateTime" variable in the new stored procedure. But claude has pointed me that data type conversion will consume bit of CPU time and we can optimize more if we use "DateTime". And that is a very good suggestion.
That was not a bad first exercise. Hope to get more complex one next.
