October 14, 2020 at 4:21 pm
I found a pair of two identical indexes, containing the same column - one is clustered primary key and another is non-clustered non-unique.
When I suggested to remove the 2nd index, developers explained that we need both of them. First - for queries with wide column listing in Select clause, and the 2nd one - only for a single column. Is that true?
Thanks
October 14, 2020 at 4:30 pm
Maybe, if queries are only using columns included in the non clustered index SQL Server might use that one over the clustered index, whether it's actually a significant improvement or whether it's worth maintaining both indexes is a different question.
October 14, 2020 at 5:20 pm
I would check the index usage stats to confirm if its getting any use and if it is scrape the query plan cache for anything which uses that index to see where its used and try to figure out why.
Make sure to replace the <yourdatabasenamehere> and <yourtablenamehere> and <yourindexnamehere> with the right values
select
OBJECT_SCHEMA_NAME(i.object_id)+'.'+object_name(i.object_id) AS ObjectName,
ISNULL(i.name,'**HEAP**') AS IndexName,
i.type_desc AS IndexType,
isnull(ddius.user_seeks,0) AS UserSeeks,
isnull(ddius.user_scans,0) AS UserScans,
isnull(ddius.user_lookups,0) AS UserLookups,
ISNULL(ddius.user_seeks,0) + ISNULL(ddius.user_scans,0) + ISNULL(ddius.user_lookups,0) AS TotalUserLookupOperations,
ISNULL(ddius.user_updates,0) As UserUpdates,
ISNULL(ddius.user_updates,0) - ISNULL(ddius.user_seeks,0) + ISNULL(ddius.user_scans,0) + ISNULL(ddius.user_lookups,0) AS UpdateDifference
,CASE i.is_disabled WHEN 0 THEN 'No' WHEN 1 THEN 'YES' END AS IndexDisabled
,case
when ISNULL(ddius.user_updates,0) > ISNULL(ddius.user_seeks,0) + ISNULL(ddius.user_scans,0) + ISNULL(ddius.user_lookups,0) THEN 'MoreUpdatesThanLookups'
when ISNULL(ddius.user_updates,0) = ISNULL(ddius.user_seeks,0) + ISNULL(ddius.user_scans,0) + ISNULL(ddius.user_lookups,0) THEN 'NoActivity'
else 'MoreLookupsThanUpdates' end as IndexUsageStatus
from
sys.objects o
inner join
sys.indexes i
on o.object_id = i.object_id
left join sys.dm_db_index_usage_stats ddius
on i.object_id = ddius.object_id and i.index_id = ddius.index_id
where is_ms_shipped = 0
and database_id = db_id('<yourdbnamehere>') or database_id is null
and o.name = '<yourtablenamehere>'
and o.schema_id <> schema_id('sys')
and o.type = 'u'
order by 1,i.index_id
USE <yourdatabasenamehere>
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = '<yourindexnamehere>';
--— Make sure the name passed is appropriately quoted
IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName);
--–Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName;
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';
--— Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
where dbid = db_id()
OPTION(MAXDOP 1, RECOMPILE)
October 14, 2020 at 6:18 pm
If I have a clustered index on orderdate, and a nonclustered index on orderdate, I might use both of them. If I'm joining the table on orderdate, and not retrieving other columns, for example, some sort of aggregation in a different table, the NCI might perform much better, especially if the table is wide. The CI contains all the data, so while I could have 10 rows of data on a page in the CI, I might have 500 in the NCI.
I might even have an include in the NCI that allows me to satisfy queries easily without using the CI. However, if my simple queries on the NCI end up with lookups into the CI, SQL will tend to use the CI.
It can be helpful to examine your workload and the stats, as Anthony noted above.
October 14, 2020 at 6:22 pm
It is very possible you need both indexes. However, developers typically don't really know. You need to check the index usage stats, as Anthony stated, to know for sure.
While you're doing that, you might as well look at the missing index stats too. It might help you decide to add another column(s) to that existing NCI and/or to create another NCI that would be really valuable to have.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2020 at 7:03 pm
Thanks all for replies. Now I am convinced, will leave the 2nd index in place.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply