SQL Server Performance Tuning Excersises - 3

This is the 3rd part of the blog series where we discuss about SQL server performance tuning exercises generated by Claude.

This time I asked for medium level challenge like the last time. I feel shouldn't go to expert level too soon.

Claude has chosen Stack Overflow database this time also.

Steup

Interesting setup script given by Claude:

USE StackOverflow2013;   -- or StackOverflow2010, whatever you're using
GO

-- Record this first so you can put it back later
SELECT name, compatibility_level 
FROM sys.databases 
WHERE database_id = DB_ID();
GO

-- This shop did a lift-and-shift onto SQL 2019/2022 hardware
-- but never touched the compat level. Very common.
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO

As you can see above script set the compatibility level to 140, i.e. 2017, but has a comment saying they (shop in the example) did a lift and shift to 2019/2022. Then we have these two helper functions written by a developer sometimes back.

CREATE OR ALTER FUNCTION dbo.fn_GetUserAnswerCount (@UserId int)
RETURNS int
AS
BEGIN
    DECLARE @AnswerCount int;

    SELECT @AnswerCount = COUNT(*)
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @UserId
      AND p.PostTypeId = 2;      -- 2 = Answer

    RETURN ISNULL(@AnswerCount, 0);
END
GO

CREATE OR ALTER FUNCTION dbo.fn_GetUserTier (@Reputation int)
RETURNS varchar(20)
AS
BEGIN
    DECLARE @Tier varchar(20);

    IF @Reputation >= 100000
        SET @Tier = 'Legendary';
    ELSE IF @Reputation >= 25000
        SET @Tier = 'Elite';
    ELSE IF @Reputation >= 5000
        SET @Tier = 'Trusted';
    ELSE IF @Reputation >= 1000
        SET @Tier = 'Established';
    ELSE
        SET @Tier = 'Newcomer';

    RETURN @Tier;
END
GO

Then index added by previous DBA

IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Posts_OwnerUserId_PostTypeId' 
                 AND object_id = OBJECT_ID('dbo.Posts'))
    CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId_PostTypeId
        ON dbo.Posts (OwnerUserId, PostTypeId);
GO
-- Heads up: on the 2013 dataset this build takes a few minutes.

Finally, we have stored procedure use for troublesome report.

CREATE OR ALTER PROCEDURE dbo.usp_GetActiveContributorReport
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            dbo.fn_GetUserAnswerCount(u.Id)  AS AnswerCount,
            dbo.fn_GetUserTier(u.Reputation) AS ReputationTier
    FROM    dbo.Users AS u
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END
GO

Scenario

The "Active Contributors" page in the internal admin tool takes between 40 and 90 seconds to load. It used to be quicker when the site was smaller, and it's been getting steadily worse. Nobody changed the code. CommandTimeout is set to 120 in the .NET app, so it doesn't actually time out — it just makes everyone hate the page.

From the DBA. "I checked the plan and the estimated cost is about 2.5. That's nothing. This query doesn't even show up in our 'top 20 most expensive queries by cost' report. But it's sitting at number one in Query Store for total CPU time, by a mile." She also mentions the plan is completely serial no matter what — she tried slapping OPTION (MAXDOP 8) on it and the plan shape didn't budge. MAXDOP on the server is 8, cost threshold is 50.

From the sysadmin. During the report run, one CPU core pins at 100% and the other seven are idle. Disk queue length is flat. No blocking, no lock waits.

From the developer who wrote it. "I ran SET STATISTICS IO ON and it barely shows anything — some reads on Users and that's basically it. The Posts table hardly registers, which is weird because the report is obviously counting posts." He also points out that if he runs the inner count for a single user by hand:

SELECT COUNT(*) FROM dbo.Posts WHERE OwnerUserId = 22656 AND PostTypeId = 2;

...it returns instantly, single-digit milliseconds. So in his mind the function can't possibly be the problem.

What Claude expected from me

Get the procedure returning the same result set in a couple of seconds on warm cache, and — more importantly - be able to explain why every cost-based diagnostic in the box lied to you about this query.

A few things I want you to actually measure rather than assume: total worker time before and after (sys.dm_exec_procedure_stats or Query Store, not wall clock), whether the plan goes parallel after your fix, and what the real logical read count on Posts was all along.

Why did the plan never go parallel, and what specifically about one of those two functions caused it? (Only one of them is guilty of this.) Why did the plan never go parallel, and what specifically about one of those two functions caused it? (Only one of them is guilty of this.)

And finally: once the function problem is gone, what's the next bottleneck in this query? There's one more thing in there worth fixing.

My Solution

Here is my version of the stored procedure:

ALTER   PROCEDURE [dbo].[usp_GetActiveContributorReport_MDA]
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #tempPosts (OwnerUserId int PRIMARY KEY, AnswerCount int)
    INSERT INTO #tempPosts
    SELECT  OwnerUserId, COUNT(*)
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 2
    GROUP BY OwnerUserId

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            tp.AnswerCount,
            CASE 
                WHEN u.Reputation >= 100000 THEN 'Legendary'
                WHEN u.Reputation >= 25000 THEN 'Elite'
                WHEN u.Reputation >= 5000 THEN 'Trusted'
                WHEN u.Reputation >= 1000 THEN 'Established'
                ELSE 'Newcomer'
            END As ss
    FROM    dbo.Users AS u INNER JOIN #tempPosts tp ON u.Id = tp.OwnerUserId
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END

I told Claude following:

• Plan didn't go parallel even after the modification. Probably due to lack of data volume in 2010 version of the stack overflow database.
• The reason original plan has not gone parallel was because there were UDF function (both functions are causing issues not one).
• Further improvements can be gain by adding more indexes to Users table.
• The reason why actual stats were hidden, was because they were behind UDF function.
• My stats:

Claude's Answer

ALTER PROCEDURE dbo.usp_GetActiveContributorReport_MDA
    @MinReputation    int,
    @LastAccessSince  datetime
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #tempPosts
    (
        OwnerUserId int NOT NULL PRIMARY KEY,
        AnswerCount int NOT NULL
    );

    INSERT INTO #tempPosts (OwnerUserId, AnswerCount)
    SELECT p.OwnerUserId, COUNT_BIG(*)
    FROM   dbo.Posts AS p
    WHERE  p.PostTypeId = 2
      AND  p.OwnerUserId IS NOT NULL
    GROUP BY p.OwnerUserId;

    SELECT  u.Id,
            u.DisplayName,
            u.Reputation,
            u.LastAccessDate,
            ISNULL(tp.AnswerCount, 0) AS AnswerCount,
            CASE
                WHEN u.Reputation >= 100000 THEN 'Legendary'
                WHEN u.Reputation >=  25000 THEN 'Elite'
                WHEN u.Reputation >=   5000 THEN 'Trusted'
                WHEN u.Reputation >=   1000 THEN 'Established'
                ELSE 'Newcomer'
            END AS ReputationTier
    FROM    dbo.Users AS u
    LEFT JOIN #tempPosts AS tp ON tp.OwnerUserId = u.Id
    WHERE   u.Reputation     >= @MinReputation
      AND   u.LastAccessDate >= @LastAccessSince
    ORDER BY u.Reputation DESC;
END

Note that Claude has put p.OwnerUserId IS NOT NULL into the where clause of the select statement where we fetch posts. Its argument was, since OwnerUserId is primary key and if there are records with no OwnerUserId this will fail. I think fair enough argument.

Second different was, LEFT JOIN was used with temp table instead of INNER JOIN. This something I missed, in a hurry. Since I used INNER join, some of the records (where there were no answers) has missed. In my result set I had 102084 records, where Claude has 168634 records. That's a good catch.

Third different was, ISNULL(tp.AnswerCount, 0) As AnswerCount. This is go hand in hand with above LEFT outer join, because if answer count is null, it will need to appear as 0.

In it's answer, it admitted, it got wrong about just one function being the cause of procedure not going parallel. Following code shows, both functions could be in-lined in higher compatibility levels.

SELECT OBJECT_NAME(object_id), is_inlineable
FROM   sys.sql_modules
WHERE  object_id IN (OBJECT_ID('dbo.fn_GetUserAnswerCount'),
                     OBJECT_ID('dbo.fn_GetUserTier'));

So changing compatibility level 150 will auto improve the stored procedure without a single line of code change. But there are lot of counter arguments against function in-lining feature, so I would still go for hand made stored procedure.

Claude in it's solution, talk about OUTTER APPLY vs pre-aggregation (which we used). But OUTTER apply is subject to parameter sniffing (in this case), so I would careful about it.

Conclusion

It was a good solution from Claude and good explanation. But notice LLMs are still making some mistakes (function in-lining scenario). Note that I have used Opus 5 (High) in this scenario, which one of the most capable models. So, we need to use them carefully. I would use AI in query tuning any day, but I will be careful and analyse the solutions it gives before apply to production.

SQL Server Performance Tuning Excersises - 2

This is the 2nd part to of the series of blogs where we discuss Performance Tuning Excersises generate by Claude.

Setup

Require StackOverflow database 2010 (10GB) or 2013 (50GB) version.

You can find the download instructions for StackOverflow database in this Brent Ozar page.

Following script create indexes required.

-- Start from a known state
DROP INDEX IF EXISTS IX_Users_Reputation ON dbo.Users;
DROP INDEX IF EXISTS IX_Posts_OwnerUserId ON dbo.Posts;
GO

CREATE INDEX IX_Users_Reputation
    ON dbo.Users (Reputation)
    INCLUDE (DisplayName);

CREATE INDEX IX_Posts_OwnerUserId
    ON dbo.Posts (OwnerUserId)
    INCLUDE (Score);
GO

-- Fresh, fullscan stats so you can't blame stale statistics.
-- This matters: I want to take that explanation off the table up front.
UPDATE STATISTICS dbo.Users WITH FULLSCAN;
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;
GO

Then we create the stored procedure:

CREATE OR ALTER PROC dbo.rpt_TopContributors
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM dbo.Users AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    WHERE u.Reputation >= @MinReputation
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC;
END
GO

Note that in here we are filtering based on reputation points. So, we need two end of reputation points to check the stored procedure. In here Claude has picked 100000 as high reputation point, which means only few users are returned and 10 has the low reputation threshold, which return most of the users in Users table.

So, our test execution script will be:

SET STATISTICS IO, TIME ON;

EXEC sp_recompile 'dbo.rpt_TopContributors';  -- safer than FREEPROCCACHE
EXEC dbo.rpt_TopContributors @MinReputation = 100000;
EXEC dbo.rpt_TopContributors @MinReputation = 10;

In round 1 of testing, we run high reputation threshold first and then lower reputation (as per above).

Result Set 1: @MinReputation = 100000 (compiled with @MinReputation = 10000): 

Table 'Posts'. Scan count 453, logical reads 2622, physical reads 0, page server reads 0, read-ahead reads 0, 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.
Table 'Users'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, 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 = 110 ms,  elapsed time = 240 ms.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 245 ms.

Result Set 2: @MinReputation = 10 (Compiled with @MinReputation = 100000): 

Table 'Posts'. Scan count 234232, logical reads 755528, physical reads 0, page server reads 0, read-ahead reads 0, 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.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, 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.
Table 'Users'. Scan count 1, logical reads 1010, physical reads 0, page server reads 0, read-ahead reads 0, 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 = 1593 ms,  elapsed time = 1796 ms.

 SQL Server Execution Times:
   CPU time = 1593 ms,  elapsed time = 1796 ms.

In round 2 of testing, we run min reputation threshold first and then higher reputation second.

Result Set 3: @MinReputation = 10 (compiled with @MinReputation = 10): 

Table 'Users'. Scan count 0, logical reads 318, physical reads 0, page server reads 0, read-ahead reads 0, 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.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, 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.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, 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 = 750 ms,  elapsed time = 971 ms.

 SQL Server Execution Times:
   CPU time = 765 ms,  elapsed time = 1034 ms.

Result Set 4: @MinReputation = 100000 (compiled with @MinReputation = 10): 

Table 'Users'. Scan count 0, logical reads 342, physical reads 0, page server reads 0, read-ahead reads 0, 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.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, 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.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, 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 = 735 ms,  elapsed time = 875 ms.

 SQL Server Execution Times:
   CPU time = 735 ms,  elapsed time = 875 ms.

My thoughts:

In above I have used StackOverflow 2010 database.

Looking at the results, we can see result set 2, i.e. executing with @MinReputation = 10 when stored procedure compiled with @MinReputation = 100000 is the worst scenario. Although time wise it is not much visible in 2010 version of the stack overflow database, it has significantly more logical reads (about 755000 in my case). 

If we look at the execution plan for first two result sets, it starts with seeking into reputation index. It is because SQL server thought there will be very small number of users with reputation higher than 100000 (because plan is compiled with @MinReputation = 100000). So, it only found few with reputation higher than 100000. But when we ran with @MinReputation = 10, reputation index returned lot more users (234232 user in my case). In the second part of plan SQL server index seek on each of those users return. This index seek was ok when there were less users (less number of seeks), but for large number of users it is too much. This is why you see performance degrade.

If plan was compiled with @MinReputation = 10 (result set 3 and 4), we can see different shape of plan. In this case SQL server, choose to scan the entire post owners index and sort it on post count. Then for each group (group by OwnerUserId), it seeked into the users table, until it finds 100 users (to full fill TOP 100 users).

For result set 3 and 4, logical reads and cpu time is mostly similar.

My Solution

Knowing that result set 3 and 4 behaved fairly equally not depending on parameter, I suggest we optimize the plan for @MinReputation = 10. So, no matter which parameter we use, it will use the plan compiled for @MinReputation = 10.

ALTER   PROC [dbo].[rpt_TopContributors]
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM dbo.Users AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    WHERE u.Reputation >= @MinReputation
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC
    OPTION (OPTIMIZE FOR (@MinReputation = 10));
END

Above is my solution, note the use of OPTIMIZE FOR hint.

Claude Solution:

Well Claude has accepted my solution, but told me to OPTIMIZE for UNKNOWN. But ideal solution it suggested is using "temp" table to fetch users first and then query posts. To do this query will be split to two. See below:

ALTER   PROC [dbo].[rpt_TopContributors]
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT u.Id,
           u.DisplayName,
           u.Reputation
    INTO #tempUsers
    FROM dbo.Users u
    WHERE u.Reputation >= @MinReputation

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM #tempUsers AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC
END

Running above shows fairly low number of logical reads and cpu time for all parameter combinations.

Also Claude has mentioned with temp table usage, I get automatic recompilation, based on the number of rows it retrieved into the temp table. This was something I didn't thought of.

SQL Server Performance Tuning Excersises - 1

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: 

1. The root cause (why that index isn't being used the way you'd hope)
2. Your fix (the rewritten proc)
3. 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:

New stats:

 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.