Disabling Non-Clustered Indexes For Fun and Profit

One of the frequently quoted ETL best practices is to disable your non-clustered indexes during a data load.  Basically the conventional wisdom is that you disable the non-clustered indexes, do your load, and then enable the non-clustered indexes, which functionally rebuilds them with the newly loaded data (a step you should be performing after a load of any size even if you *don't* disable your indexes - rebuild those newly fragmented indexes!)

So why doesn't anybody seem to do this?

https://memegenerator.net/img/instances/53039992/ponder-we-must.jpg

--

IMPORTANT NOTE <cue scary music> - do NOT disable the clustered index when doing data loads or at any other time you want the table to be accessible.  Disabling the clustered index makes the object (table or view) inaccessible.

If you disable the clustered index you are functionally disabling the object, and any attempts to access that object will result in an Error 8655:

Msg 8655, Level 16, State 1, Line 23

The query processor is unable to produce a plan because the index ‘PK_Person_BusinessEntityID’ on table or view ‘Person’ is disabled.

https://memegenerator.net/img/instances/62333367/no-please-dont-do-that.jpg

-- 

I recently had a situation with a client where they have a load process that altogether runs over 24 hours - not one day-long query but rather a string of processes that usually takes 24+ hours to complete.  The situation has worsened to the point where other things are impacted because the process is taking so long, and they asked me to check it out.

I started with all of the basics, and right away Page Life Expectancy during the relevant period jumped out:

The server has 128GB of RAM...so a PLE consistently below 1,000 is pretty low.

I used XEvents to gather queries with large memory grants, and it brought to light an issue that the client has previously considered but not handled - disabling indexes during the load.

** SIDEBAR**

The code for the XEvents session to pull large memory grant queries I used is as follows - it collects any memory grant over 8MB (which you can modify in the WHERE clause) and its calling query:

CREATE EVENT SESSION [Ntirety_MemoryGrantUsage] ON SERVER 

ADD EVENT sqlserver.query_memory_grant_usage

(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id

,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text)

WHERE ([package0].[greater_than_uint64]([granted_memory_kb],(8192))))

ADD TARGET package0.event_file(SET filename=N'Ntirety_MemoryGrantUsage',max_file_size=(256)),

ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(1048576))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON

)

The query to then retrieve the data is this - it dynamically pulls your default error log path and then queries the XEL output file from the event_file target in the session at that path:

USE master

GO

DECLARE @ErrorLogPath nvarchar(400), @XELPath nvarchar(500)

SET @ErrorLogPath = (

SELECT LEFT(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)),LEN(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)))-8)

)

SET @XELPath = @ErrorLogPath+'Ntirety_MemoryGrantUsage*.xel'

SELECT DISTINCT *

FROM 

(

SELECT 

  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value(N'(event/@timestamp)[1]', N'datetime'))as Event_time

, CAST(n.value('(data[@name="granted_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS granted_memory_mb

, CAST(n.value('(data[@name="used_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS used_memory_mb

, n.value('(data[@name="usage_percent"]/value)[1]', 'int') AS usage_percent

, n.value ('(action[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name

, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name

, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS client_hostname

, n.value ('(action[@name="server_principal_name"]/value)[1]','nvarchar(500)') AS [server_principal_name]

, n.value('(@name)[1]', 'varchar(50)') AS event_type

, n.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS sql_text

FROM 

(

SELECT CAST(event_data AS XML) AS event_data

FROM sys.fn_xe_file_target_read_file(

@XELPath

, NULL

, NULL

, NULL)

) 

AS Event_Data_Table

CROSS APPLY event_data.nodes('event') AS q(n)

) MemoryGrantUsage

ORDER BY event_time desc

** END SIDEBAR **

Looking at the queries that were the top memory consumers over the time period led to some pretty basic queries:

INSERT INTO "dbo"."Junk" 

SELECT "UID"

, "ContainerId"

, "CategoryCode"

, "CategoryName"

, "CategoryDataValue"

, "CharacteristicCode"

, "CharacteristicName"

, "CharacteristicDataValue"

, "DimensionCode"

, "DimensionName"

, "DimensionDataValue"

, "OptionId"

, "NumberOfItems"

, "IsStandard"

, "LanguageId"

, "Market"

, "VehicleType"

, "YearMonth"

, "BatchRefreshDate"

, "CreatedBy"

, "CreatedTimestamp"

, "ModifiedBy"

, "ModifiedTimestamp"

, "BatchId" 

FROM "dbo"."Junk_Stg" 

where "Market" = 'XYZ'       

  

https://media.makeameme.org/created/so-simple-right.jpg

The immediate problem to me was that memory grants usually come from SORT and HASH operations (check out Erik Darling's great description at https://www.erikdarlingdata.com/starting-sql/starting-sql-memory-grants-in-execution-plans/) and this query doesn't obviously do any of that - it is a very straightforward INSERT...SELECT from one table into another, but the query plan looks like this:

 

Pretty crazy right?  With all of those SORT's, no wonder SQL Server wants to give this query a giant memory grant!

But why are there six different SORT operations on this one basic INSERT...SELECT?

Then I realized this is why:

Six SORT's...for SIX non-clustered indexes!

Inserting rows into a table with enabled non-clustered indexes requires a SORT to add rows to that index - the INSERT can't simply dump the rows on to the end of the index, but has to sort the input to match the indexes sort.

As a test I created a copy of the target table that I could tweak the indexes on, and a second copy without the indexes in place, and my INSERT...SELECT query plan against the No Index copy looked a little bit better:

As I test I copy-pasted the two INSERT...SELECT's into a single query window and pulled the plans for each so that I could compare them:

The Query Cost numbers in Management Studio always have to be taken with a grain of salt, but for comparison's sake consider - the six-way insert costs almost four times as much as the no-index query!

To test further I disabled three of the indexes on my copy table and ran the plan again:

Bingo - six streams now down to three for the three remaining enabled indexes.

I disabled the rest of the non-clustered indexes and:

https://memegenerator.net/img/instances/63334945/booyah.jpg

For one final comparison I ran the double query against  my "Indexes All Newly Disabled" copy and my "No Index" copy together:

Spot on!

--

As I mentioned above the cost to this is that at the end of the process you have to have time to enable the indexes again, which does a rebuild of that index.

As always Your Mileage May Vary, as the direct impact will relate to the size of the table, the number of non-clustered indexes in place, and multiple other items - but check it out, especially if your process runs longer than you want - this may be the problem!

Hope this helps!

Toolbox - Why is My Database So Big????

I have written previously (here) about how to tell which database files have free space in them when your drive starts to fill.

What if all of your database files are full and you are still running out of space?

DRIVE	DATABASE NAME	FILENAME	FILETYPE	FILESIZE	SPACEFREE	PHYSICAL_NAME
F	DB03	DB03_MDF	DATA	35.47 GB	225.63 MB	F:\MSSQL\Data\DB03_Data.mdf
F	DB02	DB02Data	DATA	110.25 MB	92.38 MB	F:\MSSQL\Data\DB02.mdf
F	DB01	DB01Data	DATA	142.06 MB	72.69 MB	F:\MSSQL\Data\DB01.mdf
F	DB05	DB05_Data	DATA	35.72 GB	71.44 MB	F:\MSSQL\Data\DB05_Data.mdf
F	DB06	DB06_MDF1	DATA	36.47 GB	58.50 MB	F:\MSSQL\Data\DB06_Data.mdf
F	DB04	DB04Data	DATA	36.47 GB	38.00 MB	F:\MSSQL\Data\DB04_Data.mdf

http://www.joemartinfitness.com/wp-content/uploads/2013/11/Post-holiday-bloat.jpg

The next step is to see what is taking up the space in the individual databases.  Maybe there's an audit table that never gets purged...or a Sales History that can be archived away, it only there were an archive process...

https://s3.amazonaws.com/lowres.cartoonstock.com/business-commerce-work-workers-employee-employer-staff-ear0117_low.jpg

**ALWAYS CREATE AN ARCHIVE/PURGE PROCESS ** #ThisIsNotAnItDepends

--

There is an easy query to return the free space available in the various tables in your database - I found it in an StackOverflow at https://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server and then modified it somewhat to make the result set cleaner (to me anyway):

--

/*
Object Sizes

Modified from http://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server
*/

SELECT 
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, ISNULL(s.name+'.'+t.NAME, '**TOTAL**')  AS TableName
, SUM(p.rows) AS RowCounts
--, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.total_pages) * 8/1024.0 AS TotalSpaceMB
, SUM(a.total_pages) * 8/1024.0/1024.0 AS TotalSpaceGB
, SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.0 AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.schemas s 
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i 
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p 
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY s.name+'.'+t.Name
WITH ROLLUP
ORDER BY TotalSpaceMB DESC

--

Running the query against an individual database will return all of the tables in the database and their sizes, as well as the total size of the database (from the ROLLUP):

--

InstanceName	DatabaseName	TableName	RowCounts	TotalSpaceMB	UsedSpaceMB	UnusedSpaceMB
Instance01	Database99	**TOTAL**	148409590	36,042.063	32,890.922	3151.141
Instance01	Database99	dbo.BusinessRulesAuditing	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	dbo.BusinessRulesRuleSet	18840	3,895.766	877.945	3,017.820
Instance01	Database99	dbo.ModelWorkbookVersionExt	4383	1,818.453	1,806.445	12.008
Instance01	Database99	dbo.EquityOutputVersions	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	dbo.NominalOutputs	3592710	258.305	251.047	7.258
Instance01	Database99	dbo.Auditing	173494	33.391	27.586	5.805
Instance01	Database99	dbo.EquityOverrides	13105402	515.859	511.188	4.672
Instance01	Database99	dbo.ContingentOutputs	1371465	364.641	361.719	2.922
Instance01	Database99	dbo.ContingentWSStaging	292907	333.320	329.359	3.961
Instance01	Database99	dbo.TransformedAdminContingent	585848	76.070	73.000	3.070

--

There are two different useful cases I normally find with this result set.

The first (highlighted in aqua) is the very large table.  In this sample my 35GB database has one large 17GB table.  This is a situation where you can investigate the table and see *why* it is so large.  In many cases, this is just the way it is - sometimes one large "mother" table is a fact of life (You take the good, you take the bad, you take them both, and there you have..."

https://memegenerator.net/img/instances/11140850/god-im-old.jpg

Often though you will find that this table is an anomaly.  As mentioned above, maybe there is a missing purge or archive process - with a very large table look at the table definitions to see if their are date/datetime columns, and then select the top 10 order by those columns one by one to see how old the oldest records are.  You may find that you are storing years of data when you only need months and can purge the oldest rows.  You may also find that even though you do need years of data, you may not need them live in production, which allows you to periodically archive them away to another database (maybe even another instance) where they can be accessed only when needed.

https://blog.parse.ly/wp-content/uploads/2015/05/say_big_data.jpg

--

The second case (highlighted in yellow) is a table with significant free space contained in the table itself.  In this example a 3.7GB table has 3.0GB of free space in it!

How do you account for this?  There are a few ways - when you delete large numbers of rows from the table, the space usually isn't released until the next time the related indexes are rebuilt.  Another possibility is index fill factor - the amount of free space that is included in indexes when they are rebuilt.  I have run into several instances where a client DBA misunderstood the meaning of the fill factor and did a reverse to themselves, setting the fill factor to 10 thinking it would leave 10% free space when in fact it left 90% free space, resulting not only in exceeding large indexes but also in very poor performance as the SQL Server needs to scan across many more pages to retrieve your data.

To help determine which index(es) may contribute to the problem, you can add the indexname to the query to break the data out that one more level:

--


/*
Object Sizes With Indexes

Modified from http://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server
*/

SELECT 
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, ISNULL(s.name+'.'+t.NAME, '**TOTAL**')  AS TableName
, ISNULL(i.Name, '**TOTAL**')  AS IndexName
, SUM(p.rows) AS RowCounts
--, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.total_pages) * 8/1024.0 AS TotalSpaceMB
, SUM(a.total_pages) * 8/1024.0/1024.0 AS TotalSpaceGB
, SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.0 AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.schemas s 
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i 
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p 
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY s.name+'.'+t.Name
, i.Name
WITH ROLLUP

--

InstanceName	DatabaseName	TableName	IndexName	RowCounts	TotalSpaceMB	UsedSpaceMB	UnusedSpaceMB
Instance01	Database99	**TOTAL**	**TOTAL**	148409590	36,042.063	35,890.922	151.141
Instance01	Database99	BusinessRulesAuditing	PK__BusinessRules	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	BusinessRulesAuditing	**TOTAL**	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	BusinessRulesRuleSet	IX_RuleSet1	18840	2,100.000	137.000	1,963.000
Instance01	Database99	BusinessRulesRuleSet	IX_RuleSet2	18840	190.000	180.000	10.000
Instance01	Database99	BusinessRulesRuleSet	PK_RuleSet	18840	1,600.000	560.000	1,040.000
Instance01	Database99	BusinessRulesRuleSet	**TOTAL**	56520	3,895.766	877.945	3,017.820
Instance01	Database99	EquityOutputVersions	PK_EquityOutputVersions	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	EquityOutputVersions	**TOTAL**	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	EquityOverrides	PK_EquityOverrides	13105402	515.859	511.188	4.672
Instance01	Database99	EquityOverrides	**TOTAL**	13105402	515.859	511.188	4.672
Instance01	Database99	Auditing	PK_Auditing	173494	33.391	27.586	5.805
Instance01	Database99	Auditing	**TOTAL**	173494	33.391	27.586	5.805

<result set snipped for space>

--

In this case you could look at the fill factor of the IX_RuleSet1 and PK_RuleSet indexes to see why there is so much free space.  Failing that it is possible that these indexes need to be rebuilt to release that space, possibly after a large delete from the table cleared that space.


--

I find I use this query more than I ever would have thought with space issues - I start with the Database Free File query (from the previous post) and then move next to this query.

--

Hope this helps!

Embrace The Missing Index DMVs - But Proceed with Caution!

One of the performance tools I use all of the time is the set of Missing Index DMVs: 

·         sys.dm_db_missing_index_details – Detailed specifics on the missing indexes, including column lists 

·         sys.dm_db_missing_index_groups – relates individual missing indexes to index groups

·         sys.dm_db_missing_index_group_stats – information on potential cost and benefit of the missing indexes

·         sys.dm_db_missing_index_columns – (not regularly used but included for completeness) – included information on individual columns in the missing indexes – this information is readily retrieved from sys.dm_db_missing_index_details as groups of columns 

As I always tell you, the easiest way to start is to borrow from someone else.  The most commonly used query is from Glenn Berry’s (blog/@GlennAlanBerry) Diagnostic Information Queries.  As of the February 2017 release it is Query #31:

-- Missing Indexes for all databases by Index Advantage  (Query 31) (Missing Indexes All Databases)

SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS index_advantage,

migs.last_user_seek, mid.statement AS Database.Schema.Table,

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);

------

-- Getting missing index information for all of the databases on the instance is very useful

-- Look at last user seek time, number of user seeks to help determine source and importance

-- Also look at avg_user_impact and avg_total_user_cost to help determine importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

The results look like this:

Index advantage	last_user seek	Database.Schema. Table	Equality columns	Inequality columns	Included columns	Unique compiles	User seeks	avg_total user_cost	avg_user impact
219108325.6	02/16/2017 22:28:13	database1.schema2.table1	employee_id	NULL	id, as_of_effective_date	5	36411704	6.07278699	99.09
9319171.13	02/17/2017 10:09:54	database4.dbo.table3	cmpcode, year_max, period_max	NULL	rundatetime	581	3482	2679.33185	99.89
7881068.93	02/17/2017 10:05:19	database2.dbo.table99	code	grpcode	cmpcode	341	3451	2285.305576	99.93
7037526.5	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	NULL	rundatetime	453	2588	2720.924091	99.94
5861313.35	02/17/2017 10:05:19	database2.dbo.table99	NULL	grpcode	cmpcode, code	341	3451	2285.305576	74.32
3440880.84	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	rundatetime	NULL	227	1294	2661.233188	99.92
3362884.24	02/17/2017 10:05:43	database2.dbo.table12	elmlevel, deldate	NULL	cmpcode, code, name, sname	278	629	5353.357209	99.87
1646616.36	02/17/2017 10:10:39	database7.schema33.table2	NULL	doc_status	cmpcode, doccode, docnum	724	234099	10.3851265	67.73
1592595.34	02/17/2017 10:09:51	database2.dbo.table99	code	NULL	cmpcode, name, sname	140	310	5137.918128	99.99
877818.52	02/16/2017 15:57:09	database2.dbo.table99	elmlevel, grpcode	NULL	cmpcode, code	184	378	3185.120325	72.91

What this tells me is that the potentially (**potentially**) most useful index is on schema2.table1 in database1 on the employee_id column, with the id and as_of_effective_date columns along for the ride as INCLUDEs.  Since the MSSQLServer service was last restarted, the index would have been compiled 5 times (low cost) and would have been used a whopping 36 million times (huge benefit)!

…but wait!

At this point we all need to pause and consider the collective wisdom...

https://cdn.meme.am/instances/44649369.jpg

You can see that Glenn warns us in the last comment of his script “Do not just blindly add indexes that show up from this query!!!”

One of the most common reasons people quote for this is the impact such an index can have.  It is possible that adding an index can cause other queries to create/choose a query plan that is less favorable than its current plan because of the index of the new index.  Maybe Query1 was using Plan1 and running smoothly but now that there is a new index it may start using Plan2 which take milliseconds longer but is at a lower “cost.”  (Yes, milliseconds definitely matter!)

This is very uncommon but it can happen.  Always test missing indexes in a DEV/TEST environment before you roll them out into production!

Because you all have DEV/TEST environments for every single PROD environment that matches the hardware/software specs of PROD, right?

Right?  

RIGHT?

http://24.media.tumblr.com/tumblr_m8lvn0pSSH1qbsjydo1_500.jpg

Well….test if you can – I would never recommend “test in PROD” from an academic sense, but we all know in the real world we often have no choice – which is just another reason to be even more cautious of blindly adding new indexes – “missing” or otherwise.

--

One of the top reasons I say to be cautious of the Missing Index DMVs that I want to discuss has to do with duplicative suggestions.

Let’s look at the results from above again:

Index advantage	last_user seek	Database.Schema. Table	Equality columns	Inequality columns	Included columns	Unique compiles	User seeks	avg_total user_cost	avg_user impact
219108325.6	02/16/2017 22:28:13	database1.schema2.table1	employee_id	NULL	id, as_of_effective_date	5	36411704	6.07278699	99.09
9319171.13	02/17/2017 10:09:54	database4.dbo.table3	cmpcode, year_max, period_max	NULL	rundatetime	581	3482	2679.33185	99.89
7881068.93	02/17/2017 10:05:19	database2.dbo.table99	code	grpcode	cmpcode	341	3451	2285.305576	99.93
7037526.5	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	NULL	rundatetime	453	2588	2720.924091	99.94
5861313.35	02/17/2017 10:05:19	database2.dbo.table99	NULL	grpcode	cmpcode, code	341	3451	2285.305576	74.32
3440880.84	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	rundatetime	NULL	227	1294	2661.233188	99.92
3362884.24	02/17/2017 10:05:43	database2.dbo.table12	elmlevel, deldate	NULL	cmpcode, code, name, sname	278	629	5353.357209	99.87
1646616.36	02/17/2017 10:10:39	database7.schema33.table2	NULL	doc_status	cmpcode, doccode, docnum	724	234099	10.3851265	67.73
1592595.34	02/17/2017 10:09:51	database2.dbo.table99	code	NULL	cmpcode, name, sname	140	310	5137.918128	99.99
877818.52	02/16/2017 15:57:09	database2.dbo.table99	elmlevel, grpcode	NULL	cmpcode, code	184	378	3185.120325	72.91

Highlighted rows 4 and 6 are an example of what I call duplicative recommendations.  The CREATE INDEX statement for the two recommendations (generated using Bart Duncan’s Missing Index script) shows this even more clearly:

CREATE INDEX missing_index_2044_2043_table3 ON database4.dbo.table3 (cmpcode, usercode) INCLUDE (rundatetime)

CREATE INDEX missing_index_2046_2045_table3 ON database4.dbo.table3 (cmpcode, usercode,rundatetime)

The first index is only two columns with an INCLUDE of a third column, while the second index is only all three columns.  The second index will not only satisfy any situations needing that index, but will also satisfy any situations needing the first index.

Note that the Index Advantage (weighted average of cost and benefit) of the second index, the index we really want, is only half that of the first index.  When I report recommendations like this to the client I edit the output to match the highest Index Advantage of the duplicative indexes with the most correct recommendation – in this case I would use the second index definition (the index on all three columns with no INCLUDE) with the first Index Advantage (7037526.5).

--

Another situation similar to that of the duplicative recommendation is that of the “left-hand-equivalent” recommendation.  Consider the two highlighted rows here:

Index advantage	last_user seek	Database.Schema. Table	Equality columns	Inequality columns	Included columns	Unique compiles	User seeks	avg_total user_cost	avg_user impact
219108325.6	02/16/2017 22:28:13	database1.schema2.table1	employee_id	NULL	id, as_of_effective_date	5	36411704	6.07278699	99.09
9319171.13	02/17/2017 10:09:54	database4.dbo.table3	cmpcode, year_max, period_max	NULL	rundatetime	581	3482	2679.33185	99.89
7881068.93	02/17/2017 10:05:19	database2.dbo.table99	code	grpcode	cmpcode	341	3451	2285.305576	99.93
7037526.5	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	NULL	rundatetime	453	2588	2720.924091	99.94
5861313.35	02/17/2017 10:05:19	database2.dbo.table99	NULL	grpcode	cmpcode, code	341	3451	2285.305576	74.32
3440880.84	02/17/2017 10:09:39	database4.dbo.table3	cmpcode, usercode	rundatetime	NULL	227	1294	2661.233188	99.92
3362884.24	02/17/2017 10:05:43	database2.dbo.table12	elmlevel, deldate	NULL	cmpcode, code, name, sname	278	629	5353.357209	99.87
1646616.36	02/17/2017 10:10:39	database7.schema33.table2	NULL	doc_status	cmpcode, doccode, docnum	724	234099	10.3851265	67.73
1592595.34	02/17/2017 10:09:51	database2.dbo.table99	code	NULL	cmpcode, name, sname	140	310	5137.918128	99.99
877818.52	02/16/2017 15:57:09	database2.dbo.table99	elmlevel, grpcode	NULL	cmpcode, code	184	378	3185.120325	72.91

As above, here are the scripted CREATE INDEX statements for those two rows:

CREATE INDEX missing_index_35_34_table99 ON database2.dbo.table99 (code,grpcode) INCLUDE (cmpcode)

CREATE INDEX missing_index_250_249_table99 ON database2.dbo.table99 (code) INCLUDE (cmpcode, name, sname)

These two indexes are not as obviously related but they are.

http://i.imgur.com/iQYuWno.jpg

They are not only on different fields, but also have different INCLUDE columns.  If you look closely though, the actual index columns are what I call “left-hand equivalent” – they both start with code and then the first index adds grpcode, so an index on code, grpcode would cover both situations for the searchable index fields.

The second piece that would truly make an index cover both situations is for it to include the sum of the INCLUDE’d columns – hence:

CREATE INDEX missing_index_250_249_table99 ON database2.dbo.table99 (code,grpcode) INCLUDE (cmpcode, name, sname)

This index on two columns with three included columns covers both situations – instead of choosing one index over the other we need to do a little work and combine them, but the effect can be very beneficial, and once you understand how it works it doesn’t take that much time.

--

Here is another (completely contrived) situation:

CREATE INDEX missing_index_44_45_table23 ON database3.dbo.table23 (name,address1) INCLUDE (address2)

CREATE INDEX missing_index_32_33_table23 ON database3.dbo.table23 (name) INCLUDE (address1, address2, state)

CREATE INDEX missing_index_55_56_table23 ON database3.dbo.table23 (name, city) INCLUDE (address1)

CREATE INDEX missing_index_48_49_table23 ON database3.dbo.table23 (name, address1,city)

So we need to start at the beginning – are the indexes all on the same database and table?  Check!  (You may chuckle but especially when looking across an instance you may find you have very similar looking databases/tables!)

Next, let’s look at left-hand equivalence.  All four indexes start with name field – so far so good.  Index_32_33 ends there, so it is a likely candidate to be consolidated with something else.

This is where it gets a little trickier – both index_44_45 and index_48_49 have address1 as their second column, which means they could be duplicative and could also be related (left-hand-equivalent) to index_32_33 upon further investigation.

Index_55_56 however does not continue with address1 – instead it has city in its second position.  This means index_55_56 is *not* duplicative of index_44_45 or index_48_49 although it can still be related to the narrowest index, index _32_33.

This demonstrates again how important the order in the index is – indexes are searched from left-to-right, so "name, city" <> "name, address".

Consider index_55_56 and index_48_49 – even though index_48_49 *does* have the city column in its index list, it is not in the same-left-to-right order (with address1 in the way) so it isn’t left-hand-equivalent and therefore not combinable.

This leaves us with two options, either of which can be optimal: 

Combine index_32_33, index_44_45, and index_48_49, and just create index_55_56 as is: 

CREATE INDEX missing_index_98_99_table23 ON database3.dbo.table23 (name,address1,city) INCLUDE (address2,state) 

CREATE INDEX missing_index_55_56_table23 ON database3.dbo.table23 (name, city) INCLUDE (address1)

 Combine index_44_45 with index_48_49 (both containing name,address1) and index_32_33 and index_55_56: 

CREATE INDEX missing_index_77_78_table23 ON database3.dbo.table23 (name,address1,city) INCLUDE (address2) 

CREATE INDEX missing_index_88_89_table23 ON database3.dbo.table23 (name, city) INCLUDE (address1, address2, state) 

As stated above either of these options work – they both cover all four situations.  One thing to consider is the size of the fields contained in the indexes – in option 1 we are storing eight fields (name twice, address1 twice, city twice, address2 once, and state once) whereas in option 2 we are storing *nine* fields as we have address2 in the INCLUDE of both indexes.  This may make Option 1 at least slightly “better” although depending on the datatype of address2 and the number of rows in table23, that advantage may be negligible.

--

Missing indexes are an oft-avoided subject but they really can make a difference to performance, and the algorithms inside SQL Server to help determine and weight the recommendations has become much better with each version of SQL Server.  One thing these improved algorithms still don’t watch for are the duplicative/related situations we have discussed here, so you still need to watch for them yourselves.

http://www.cindyvallar.com/crowsnest.jpg

Again, do *not* just blindly create new indexes – consider, test if possible, and weigh the advantages against the possible disadvantages such as the amount of space the index will consume.

--

Hope this helps!