Blog

When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex™.

When you look at missing index requests, it’s always important to remember one of the biggest things: these missing index requests won’t ever ask for or recommend a specific clustered index.

Let’s take a look at what this might mean against a slightly modified version of the AdventureWorks2012 sample database.

Hey, we have a high Value missing index!

Let’s say we run sp_BlitzIndex™ and it diagnoses that we have a high value missing index.

--To diagnose a database, we would run:
EXEC dbo.sp_BlitzIndex
    @database_name='AdventureWorks'

We scroll to the right on this line to get more info and see that the index request has an overall “benefit” over over one million. That benefit is a made up number– it’s a combination of the number of times the index could have been used, the percentage by which SQL Server thought it would help the queries generating the request, and the estimated “cost” of the requests. These factors are all multiplied together to help bubble up the biggest potentially useful requests.

We also see that the index request is really quite narrow. It only wants an index with a single key column on the “City” column! That seems really reasonable.

Scrolling over farther, we can see that this could have potentially been used about 49 thousand times. The queries that could have used it (it’s quite possibly more than one), were rather cheap– their costs were less than one on average– and it would have improved those queries a whole lot (around 93% overall). We currently don’t have ANY nonclustered indexes on the table in question, so we don’t have to worry about creating a duplicate nonclustered index.

This index seems really reasonable. It would be used a lot, and it would help those queries out.

If we keep going to the right, we see that there’s some sample TSQL to create the suggested index at the far right:

CREATE INDEX [ix_Address_City] ON [AdventureWorks].[Person].[Address]

Sure enough, that syntax will create a nonclustered index. That seems really good, but hold on a second!

Always look at the whole table

Just to the left of the “Create TSQL” column is a really important helper– the “More Info” column.

Copy the command from the appropriate row out. It will look something like this:

EXEC dbo.sp_BlitzIndex
    @database_name='AdventureWorks',
    @schema_name='Person',
    @table_name='Address'

This command will help you look at the whole table itself and assess if there might be something you’re missing.

When you run this command, you’ll see a big picture view of the indexes on the table, the missing index requests for the table, and the number and types of the columns in the table:

In this table, note that we don’t even have a clustered index. This table is a heap! Heaps have all sorts of wacky problems in SQL Server.

In this case, we have an OLTP database and we definitely want to avoid heaps to keep our sanity.

Our queries are requesting an index on the City column, but it looks an awful lot like our table was modeled with another clustered index in mind (AddressID). Don’t just assume that the missing index request itself will always make the best clustered index. You need to take a look at your overall workload and the queries which use the table. You need to decide based on the whole workload and overall schema what should be the clustered index, if you have a primary key, and if the primary key is the same thing or different than the clustered index. After you have that design, then make decisions for your nonclustered indexes.

The clustered index is special

The clustered index in any table has special uses and significance. This index is the data itself, and it will be used in every nonclustered index in the table. If you are defining a new clustered index or changing a clustered index, SQL Server will need to do IO on every nonclustered index on the table as well. Always make sure to test your indexing changes and choose your index definitions carefully.

Want to try this out on your own?

Feel free! A copy of the scripts I used to set up AdventureWorks for this very simple demo are below. Note that this is only suitable for test environments, and the script will mess up your Person.Address table (so make sure you can re-restore AdventureWorks2012 afterward). Get sp_BlitzIndex™ here

RAISERROR('Careful there! Run in sections.',20,10) WITH LOG;
GO

--******************
-- (C) 2013, Brent Ozar Unlimited.
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--This query may have a very long runtime on some systems.
--******************

--Modify this restore to fit your file system and preferences
IF DB_ID('AdventureWorks') IS NOT NULL
BEGIN
	USE master;
	ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE AdventureWorks FROM DISK=N'C:MSSQL11.SQL2012CSMSSQLBackupAdventureWorks2012-Full Database Backup.bak'
	WITH MOVE N'AdventureWorks2012_Data' TO N'C:MSSQL11.SQL2012CSMSSQLDATAAdventureWorks2012_Data.mdf',
	MOVE N'AdventureWorks2012_Log' TO N'C:MSSQL11.SQL2012CSMSSQLDATAAdventureWorks2012_log.ldf',
	REPLACE,
	RECOVERY;
GO

USE [AdventureWorks];
GO

---Remove all the indexes from Person.Address

ALTER TABLE Person.BusinessEntityAddress  DROP CONSTRAINT FK_BusinessEntityAddress_Address_AddressID;
GO
ALTER TABLE Sales.SalesOrderHeader DROP CONSTRAINT FK_SalesOrderHeader_Address_BillToAddressID;
GO
ALTER TABLE Sales.SalesOrderHeader DROP CONSTRAINT FK_SalesOrderHeader_Address_ShipToAddressID;
GO

DROP INDEX [AK_Address_rowguid] ON [Person].[Address] ;
GO
DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] ;
GO
DROP INDEX [IX_Address_StateProvinceID] ON [Person].[Address] ;
GO
ALTER TABLE [Person].[Address] DROP CONSTRAINT [PK_Address_AddressID]
GO

--Run a query a bunch of times
set nocount on;
set statistics time, io off;
GO

declare @i int =0;
while @i < 10000
begin
	declare @AddressLine1 nvarchar (60), @AddressLine2 nvarchar (60),
		@City nvarchar (30), @StateProvinceCode nchar(3), @PostalCode nvarchar (15)
	SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2,
		@City=City, @StateProvinceCode=[StateProvinceCode],
		@PostalCode=PostalCode
	FROM [Person].[Address] AS [pa]
	JOIN [Person].[StateProvince] AS 1 on pa.StateProvinceID=ps.StateProvinceID
	WHERE City = 'Kenosha';

	set @i=@i+1;
end
GO

declare @i int =0;
while @i < 39291
begin
	declare @countme BIGINT
	SELECT @countme = count(*)
	FROM [Person].[Address] AS [pa]
	JOIN [Person].[StateProvince] AS 1 on pa.StateProvinceID=ps.StateProvinceID
	WHERE City = 'San Diego'
	and ps.StateProvinceCode='CA';

	set @i=@i+1;
end
GO

--Diagnose!
EXEC dbo.sp_BlitzIndex
    @database_name='AdventureWorks'

--Look at the Person.Address table specifically
exec sp_BlitzIndex @database_name='AdventureWorks', @schema_name='Person', @table_name='Address'
GO

Fast – When developers ask how quickly a piece of code needs to run, don’t say fast. Give them a finish line so that they can know when it’s time to move on. “This query currently does over 15mm logical reads. We don’t allow production OLTP queries that do over 100k logical reads – anything higher than that needs to hit the reporting server instead.” Developers don’t want to write slow queries, but if you don’t show them how to measure their queries, they don’t know what’s slow versus fast. Show them how to measure what makes a query successful, and they’ll start measuring it long before they bring it to you.

Sometimes - When code works unpredictably, don’t say it sometimes works. Look for things it has in common when it fails. Does it bomb every Sunday, or when it handles over ten customers, or when it’s only being called once at a time? Keep digging for environmental coincidences until we can give the developers or QA staff a lead. Sometimes (see what I did there?) I find that developers want access to the production server just because they can’t get enough specific troubleshooting help from the DBAs. “Your code fails sometimes” isn’t going to cut it.

Never - When the developer tries to deploy a trigger in production, don’t say, “We never allow that.” The business, not our emotional desire, dictates the technical solutions we use. We’re here to advise the business, and sometimes the business won’t go along with our advice. Our job is to lay out our concerns clearly and concisely, preferably with risk assessments and real-life stories, and then listen. I’d love to build every box and application perfectly, but we gotta ship if we’re going to keep paying salaries.

Fine - When you’re asked how the server is performing, don’t say it’s fine. Quantify it in terms of batch requests per second, average duration for a stored procedure, or some other metric that you can measure precisely. Bonus points if we correlate this number over time, like if we can say, “We normally average 1,400 to 1,500 batch requests per second during peak weekday hours, and we’re doing 1,475 right now.”

Large – When developers asks how big a table or database or index is, don’t say large. What’s large to you is small to someone else, and vice versa. Give exact numbers: number of terabytes in the largest database, number of databases, number of rows in the largest table, or the number of times you’ve updated your resume in terror because the backup failed.

It Depends – DBAs love giving this answer as a cop-out for tough questions, and if you’re not careful, it comes off as a condescending know-it-all. For best results, immediately follow this phrase with the word “on”, as in, “It depends on the table’s access pattern – in read-focused systems like our data warehouse, we can have up to 10-15 indexes on our dimension tables, but in OLTP databases like our web site, we need to aim for 5 indexes or less per table.” The faster you can help someone to the answer they’re looking for, the more they’ll respect you as a partner, not an enemy combatant.