Blog

You know how to write TSQL queries, but do you know how to make them faster? In this free 30 minute talk, Kendra Little will cover the basics of clustered and non-clustered indexes in SQL Server. She’ll show how you can tell which indexes your queries are using and show warning signs that mean perhaps your query needs a re-write. If you’ve ever wanted to learn how you can use indexes to make your queries run faster, this session is for you.

Want the scripts from the webcast? Scroll on down, the code is below the video.

Sample Databases from Microsoft

The sample code below uses the Contoso Retail DW database, but you can also play around with AdventureWorks. Here are where you can grab ‘em:

Demo Code

--------------------------------------------------
-- Demo script copyright Brent Ozar PLF, 2012
-- This script is ***NOT*** suitable for production use!
-- The script contains commands that could significantly
-- harm/change performance, so use on a dedicated test
-- instance only, and always research any commands
-- you aren't familiar with.
--------------------------------------------------

--Set up the script;
SET NOCOUNT OFF;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

--Restore the db if needed
--This is restoring to my rotational drive
--Time estimate: 30 seconds
USE [master]
GO
IF DB_ID('ContosoRetailDW') IS NOT NULL
	BEGIN
		ALTER DATABASE ContosoRetailDW SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	END
RESTORE DATABASE [ContosoRetailDW] FROM  DISK = N'S:_INSTALLContosoRetailDW.bak'
	WITH
		MOVE N'ContosoRetailDW2.0' TO N'T:MSSQLDataContosoRetailDW.mdf',
		MOVE N'ContosoRetailDW2.0_log' TO N'T:MSSQLDataContosoRetailDW.ldf',
		REPLACE, NOUNLOAD,  STATS = 5
GO

-- Run single threaded. This is just to simplify the demo--
-- We don't have time in 30 minutes to cover the index examples
-- AND also all the impacts of parallelism on query cost and execution.
-- This is not a production configuration recommendation!
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE
GO

--- Let's clear data out of our SQL Server's memory.
--- This is NOT a safe command for production servers!
--- We use this in the test so it causes a "fair playing field"
--- Otherwise we'd read from disk the first time, then read
--- From memory on subsequent runs-- which wouldn't be fair.
DBCC DROPCLEANBUFFERS;
GO

-- Get into our test database.
USE ContosoRetailDW;
GO

--- Let's say we're running this query.
--- We need to know total sales by day
--- Just for one store
--- For seven days
--- And this is just a sample of our data!
SELECT	CAST(datekey AS DATE) AS [Date of Sales],
		SUM(salesQuantity) AS [Sum of SalesQuantity],
		SUM(SalesAmount) AS [Sum of SalesAmount]
FROM	dbo.FactOnlineSales AS fs
WHERE	StoreKey = 306
		AND datekey BETWEEN '2009-11-01' AND '2009-11-07'
GROUP BY CAST(datekey AS DATE)
ORDER BY [Date of Sales] DESC;
GO

----------------------------------
--Question 1:
--Why did that take so long?
----------------------------------
--- Let's clear data out of our SQL Server's memory.
--- This is NOT a safe command for production servers!
DBCC DROPCLEANBUFFERS;
GO

--These commands tell SQL Server to report back on how my query runs.
--Results will be on the 'Messages' tab.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Also turn on "actual execution plans"
-- The execution plan will show up in its own tab.
-- This can be done with ctrl+m

SELECT	CAST(datekey AS DATE) AS [Date of Sales],
		SUM(salesQuantity) AS [Sum of SalesQuantity],
		SUM(SalesAmount) AS [Sum of SalesAmount]
FROM	dbo.FactOnlineSales AS fs
WHERE	StoreKey = 306
		AND datekey BETWEEN '2009-11-01' AND '2009-11-07'
GROUP BY CAST(datekey AS DATE)
ORDER BY [Date of Sales] DESC;
GO

--- How much did we read?
-- What does the execution plan tell us?

-- SQL Server says this is the ideal index for THIS query:
USE [ContosoRetailDW]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[FactOnlineSales] ([StoreKey],[DateKey])
INCLUDE ([SalesQuantity],[SalesAmount])
GO

-- But do we have to have that exact index?
-- First, let's look at all the indexes on the table:

EXEC sp_helpindex 'dbo.factOnlineSales'

-- OK, well we're starting fresh!
-- I talk my DBA into creating ONE very narrow index.
-- In the real world, we won't always have the perfect index for *every* query--
-- And there is very good reason for that!

-- My DBA wants the index to start with DateKey, and they won't agree to all my includes.

CREATE NONCLUSTERED INDEX [ix_FactOnlineSales_DateKeyStoreKey]
ON [dbo].[FactOnlineSales] ([DateKey],[StoreKey]) WITH (MAXDOP=2);
GO

-- What happens to our query?
-- Will it use the not-so-perfect index?
-- Turn on actual execution plans: CTRL+M

--- This is NOT a safe command for production servers!
DBCC DROPCLEANBUFFERS;
GO

SELECT	CAST(datekey AS DATE) AS [Date of Sales],
		SUM(salesQuantity) AS [Sum of SalesQuantity],
		SUM(SalesAmount) AS [Sum of SalesAmount]
FROM	dbo.FactOnlineSales AS fs
WHERE	StoreKey = 306
		AND datekey BETWEEN '2009-11-01' AND '2009-11-07'
GROUP BY CAST(datekey AS DATE)
ORDER BY [Date of Sales] DESC;
GO

--- Hmm. Well, what if I forced it? I can use an INDEX HINT!
--- (is this a good idea???)
--- This is NOT a safe command for production servers!
DBCC DROPCLEANBUFFERS;
GO

SELECT	CAST(datekey AS DATE) AS [Date of Sales],
		SUM(salesQuantity) AS [Sum of SalesQuantity],
		SUM(SalesAmount) AS [Sum of SalesAmount]
FROM	dbo.FactOnlineSales AS fs WITH ( INDEX=ix_FactOnlineSales_DateKeyStoreKey )
WHERE	StoreKey = 306
		AND datekey BETWEEN '2009-11-01' AND '2009-11-07'
GROUP BY CAST(datekey AS DATE)
ORDER BY [Date of Sales] DESC;
GO

-- Bad news: Index hints are risky business over time.
-- Is there a better way?
-- Let's look at those indexes again.

EXEC sp_helpindex 'dbo.factOnlineSales';
GO

-- There's a secret about the OnlineSalesKey column
-- It is the clustering key, so it's automatically in my nonclustered index.
-- I can use the knowledge to rewrite my query.

--- This is NOT a safe command for production servers!
DBCC DROPCLEANBUFFERS;
GO

WITH	mykeys
		  AS ( SELECT	OnlineSalesKey
			   FROM		dbo.FactOnlineSales AS fs
			   WHERE	datekey BETWEEN '2009-11-01' AND '2009-11-07'
						AND StoreKey = 306)
	SELECT	CAST(datekey AS DATE) AS [Date of Sales],
			SUM(salesQuantity) AS [Sum of SalesQuantity],
			SUM(SalesAmount) AS [Sum of SalesAmount]
	FROM	dbo.FactOnlineSales AS fs
			JOIN mykeys ON fs.OnlineSalesKey = mykeys.OnlineSalesKey
	GROUP BY CAST(datekey AS DATE);
GO

-- What if I used a temp table instead?

--- This is NOT a safe command for production servers!
DBCC DROPCLEANBUFFERS;
GO

IF OBJECT_ID('tempdb..#mykeys') IS NOT NULL
	DROP TABLE #myKeys;
CREATE TABLE #myKeys
	(
	  onlinesaleskey INT
	);

INSERT	#myKeys ( onlinesaleskey )
		SELECT	OnlineSalesKey
		FROM	dbo.FactOnlineSales AS fs
		WHERE	datekey BETWEEN '2009-11-01' AND '2009-11-07'
				AND StoreKey = 306;

SELECT	CAST(DateKey AS DATE) AS [Date of Sales],
		SUM(salesQuantity) AS [Sum of SalesQuantity],
		SUM(SalesAmount) AS [Sum of SalesAmount]
FROM	dbo.FactOnlineSales AS fs
		JOIN #mykeys ON fs.OnlineSalesKey = #mykeys.OnlineSalesKey
GROUP BY CAST(DateKey AS DATE)
ORDER BY [Date of Sales] DESC;
GO

-- Which of these is better?
-- I have a definite preference: watch the webcast to find out why.