Sql-server – Similar indices in MS SQL Server

indexsql server

I currently have a table which has 2 indices on the same table that are being used to speed up slightly different sets of queries. They both have 2 index columns and 1 included column and all 3 columns are the same in both. The table stores sales ranks at particular points in time. The 3 fields are the ID of the product that the rank applies to (int), the date and time it was checked (smalldatetime) and the rank (int). In one index I have the rank and checked date/time as indexed cols and the product ID included, in the other I have the product ID and the checked date/time as indexed cols and the rank included.

Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

Create statements are: –

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank] 
ON [dbo].[SalesRankCheck]
(
    [ChannelSKUID] ASC,
    [Checked] ASC
)
INCLUDE ([SalesRank]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID] 
ON [dbo].[SalesRankCheck]
(
    [SalesRank] ASC,
    [Checked] ASC
)
INCLUDE ([ChannelSKUID]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE 2: These are the stats on the indices: –

OBJECT NAME INDEX NAME  USER_SEEKS  USER_SCANS  USER_LOOKUPS    USER_UPDATES
SalesRankCheck  ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank    1140    18  0   4091
SalesRankCheck  ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID    36  0   0   4091

Best Answer

The indexes are similar but not identical. The order of the columns in an index matters - a lot - and affects what queries might benefit from them.

We don't know your queries but for example a query like the following would benefit most from index A:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE ChannelSKUID = 'XXX'
ORDER BY Checked ;

while this one would benefit from index B:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE SalesRank = 1 
ORDER BY Checked ;

If only one of the two indexes were available, the query plans might still use it but the plan would not be optimal, due to the difference in column order in the index and the mismatch with the optimal column order needed for the query. The index would still be "covering" the query but the plan would probably be a full index scan instead of an index seek and would have additional (sort) operations as the rows provided by the index would not be in the order the query wants.

For the question now:

Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

No, it is a sensible way and I wouldn't expect a single index to cover queries that might choose either index otherwise.

But it depends on what queries run against the table. Any analysis should be based on the workload you have and the performance you expect. Analyze the queries (all of them or the ones most often run) and what indexes they use and what performance you get, before you decide dropping or adding an index.

Run tests in test installations with different indexing, if performance is not good. Or experiment (say with dropping an index), if you think that the index is not used.

For a quick and dirty advice, if the number of indexes on the table is small and the performance is good, don't bother changing anything. The extra load that indexes impose on write operations is usually small, unless you have a table with 10+ indexes or a table that is under extreme write load (thousands of writes per second).

Related Question