July 25, 2012 at 2:32 pm
Hello,
I have a question on the nonclustered index consolidation topic. There 2 tables called Records and RecordTypes. There is a Foreign Key from Records to RecordTypes on the RecordType column.
There are 2 nonclustered indexes on the Records table -- IX_Records_RecordType (FK index) and IX_Records_RecordType_DateCreated (a web page uses it).
From 06/03/12 to today, IX_Records_RecordType has 24,193 seeks and the last seek dated today; IX_Records_RecordType_DateCreated has 179 seeks and it's not heavily used page, sometimes is not being called for few days.
Does it makes sense (a good practice!) to consolidate these to indexes together? I figured that the RecordType key is the first column in both indexes. I understand that the index record for the 2 column index is wider but will it really be a big disadvantage comparing to the narrower index.
Thank you in advance!
Denis
July 25, 2012 at 4:32 pm
What are the exact definitions of those two indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2012 at 6:49 am
Hello Gail,
Thank you for your reply. Is this what you were looking for?
CREATE NONCLUSTERED INDEX [IX_Records_RecordType_DateCreated] ON [dbo].[Records]
(
[RecordType] ASC,
[DateCreated] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Records_RecordType] ON [dbo].[Records]
(
[RecordType] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Denis
July 26, 2012 at 7:12 am
Yup.
You can drop the index only on RecordType. The chances of that slowing any queries down as a result are incredibly slim.
Do test this before doing it in production. If there are any queries that have index hints, dropping an index could cause errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2012 at 7:16 am
Good point! Thank you, Gail.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply