Fix Slow Queries in SQL Server Using a Nonclustered Index
In this tutorial, I demonstrate how to optimize slow queries in SQL Server by creating the right index. Slow queries are one of the most common performance issues DBAs encounter, and indexing is one of the most effective ways to improve query performance.
In this hands-on demo using SQL Server 2022, we build a small indexing lab from scratch. You'll see how a query performs without an index (table scan) and how performance improves after creating a nonclustered index (index seek).
This step-by-step walkthrough shows a real workflow used by SQL Server DBAs when troubleshooting performance problems.
In this video you will learn:
• Why slow queries happen in SQL Server
• How SQL Server performs table scans vs index seeks
• How to create a nonclustered index
• How indexes reduce logical reads and improve performance
• How DBAs analyze query performance using STATISTICS IO
If you work with SQL Server as a DBA, database developer, or data engineer, understanding indexing is a fundamental skill for optimizing query performance.
Follow Along – SQL Scripts Used in This Tutorial
You can copy and run the scripts below to follow along with the tutorial.
--Step 1 — Create the Test Database
CREATE DATABASE IndexTuningLab; -- Create a new database for the indexing demo
GO
USE IndexTuningLab; -- Switch context to the new database
GO
--Step 2 — Create the Test Table
USE IndexTuningLab; -- Ensure we are in the IndexTuningLab database
GO
CREATE TABLE dbo.CustomerOrders -- Create a table that simulates customer order data
(
OrderID INT IDENTITY(1,1) NOT NULL, -- Unique order identifier that auto-increments
CustomerID INT NOT NULL, -- Customer identifier
LastName NVARCHAR(50) NOT NULL, -- Customer last name (used later in the WHERE clause)
FirstName NVARCHAR(50) NOT NULL, -- Customer first name
City NVARCHAR(50) NOT NULL, -- Customer city
OrderDate DATE NOT NULL, -- Date the order was placed
OrderAmount DECIMAL(12,2) NOT NULL, -- Total order amount
OrderStatus NVARCHAR(20) NOT NULL, -- Current order status
Notes NVARCHAR(200) NULL, -- Optional notes column
CONSTRAINT PK_CustomerOrders PRIMARY KEY CLUSTERED (OrderID) -- Create clustered index on OrderID
);
GO
--Step 3 — Insert Test Data
USE IndexTuningLab; -- Ensure we are in the lab database
GO
;WITH N1 AS
(
SELECT 1 AS n
UNION ALL
SELECT 1
),
N2 AS (SELECT 1 AS n FROM N1 a CROSS JOIN N1 b), -- 4 rows
N4 AS (SELECT 1 AS n FROM N2 a CROSS JOIN N2 b), -- 16 rows
N8 AS (SELECT 1 AS n FROM N4 a CROSS JOIN N4 b), -- 256 rows
N16 AS (SELECT 1 AS n FROM N8 a CROSS JOIN N8 b), -- 65,536 rows
N32 AS (SELECT 1 AS n FROM N16 a CROSS JOIN N1 b), -- 131,072 rows
Nums AS
(
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM N32
)
INSERT INTO dbo.CustomerOrders
(
CustomerID,
LastName,
FirstName,
City,
OrderDate,
OrderAmount,
OrderStatus,
Notes
)
SELECT
ABS(CHECKSUM(NEWID())) % 10000 + 1,
CASE
WHEN n % 20 = 0 THEN 'Smith'
WHEN n % 20 = 1 THEN 'Johnson'
WHEN n % 20 = 2 THEN 'Williams'
WHEN n % 20 = 3 THEN 'Brown'
WHEN n % 20 = 4 THEN 'Jones'
ELSE CONCAT('LastName', n % 500)
END,
CONCAT('FirstName', n % 1000),
CONCAT('City', n % 200),
DATEADD(DAY, -(n % 3650), CAST(GETDATE() AS DATE)),
CAST((ABS(CHECKSUM(NEWID())) % 50000) / 10.0 + 10 AS DECIMAL(12,2)),
CASE
WHEN n % 3 = 0 THEN 'Shipped'
WHEN n % 3 = 1 THEN 'Pending'
ELSE 'Completed'
END,
CONCAT('Test order row ', n)
FROM Nums;
GO
--Step 4 — Confirm Row Count
SELECT COUNT(*) AS TotalRows
FROM dbo.CustomerOrders;
GO
--Step 5 — Enable Performance Metrics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
--Step 6 — Run the Query Without an Index
SELECT
CustomerID,
LastName,
FirstName,
OrderDate,
OrderAmount
FROM dbo.CustomerOrders
WHERE LastName = 'Smith';
GO
--Step 7 — Create the Nonclustered Index
CREATE NONCLUSTERED INDEX IX_CustomerOrders_LastName
ON dbo.CustomerOrders (LastName)
INCLUDE (CustomerID, FirstName, OrderDate, OrderAmount);
GO
Step 8 — Run the Query Again
Run the same query again to see the performance improvement.
Tools Used
• SQL Server 2022
• SQL Server Management Studio (SSMS)
#SQLServer
#SQLDBA
#SQLServerPerformance
#DatabaseAdministration
#SQLServerTutorial