October 30, 2014 at 8:34 am
I'm in the process of trying to optimize a stored procedure with many queries. The execution plan provides a missing non-clustered index on nearly every query, and they're all fairly similar. The only real difference between them are what's in the INCLUDE statement. The two key columns are listed in every missing index. Let's say each query is approximately 5% of the total batch and 90% of the queries all fall into the category I listed above. How should I go about creating the missing indexes? Create all of the missing indexes or create one generic one that has all the INCLUDE columns? Create a minimal index with just a few of the common INCLUDE columns?
Here's an example of what I'm talking about with the missing indexes:
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9044%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ARTX],[C4ASTX],[C4ADNB],[C4CZST])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.5418%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
Thanks,
Rory
October 30, 2014 at 8:44 am
The nice thing about INCLUDE is that it doesn't matter what order the included columns are in. You could spend a long time tweaking your index based on logical reads... but it's probably just about as effective to just make one NCI with all the requested columns included (assuming the resulting index isn't MASSIVE due to VARCHAR(MAX) or LOB columns, etc.)
October 30, 2014 at 9:06 am
Thanks for the reply - I've only been a SQL Server DBA for about a month. I took your advice and created a non-clustered index with all of the INCLUDE columns. The stored procedure runs nearly 50% faster now.
October 30, 2014 at 10:39 am
One generic index should be best. If you had created multiple indexes, all with the same key values, you might not see them used even in the queries that suggested the missing index.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply