June 2, 2009 at 2:40 am
I am maintaining a data mart and came across a table in that is having 3 non-clustered indexes on the same columns. The difference between the 3 indexes is that one of the index uses 2 columns in the include clause and the second once uses 1 column in the include clause and the third one doesn't include any columns.
Following is the code:
IF object_id('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[SK_ID] [bigint] IDENTITY(1,1) NOT NULL,
[ACCOUNT_NO] [varchar](20) NULL,
[CARD_NUMBER] [numeric](19, 0) NULL,
[TRANS_TYPE] [numeric](3, 0) NULL,
[TRANS_COUNT] [numeric](7, 0) NULL,
[TRANS_VALUE] [numeric](15, 2) NULL,
[CURRENT_DATE] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [NIX_WITHINCLUDE] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
)
INCLUDE ( [TRANS_COUNT],
[CURRENT_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_WITHINCLUDE_1] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
)
INCLUDE ( [CURRENT_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_WITHOUTINCLUDE] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
) ON [PRIMARY]
I have read BOL and other articles about INCLUDE clause and my conclusion is that NIX_WITHINCLUDE is good enough to cover everything that will be covered by all 3 indexes.
Do I actually need NIX_WITHINCLUDE_1 and NIX_WITHOUTINCLUDE indexes?
Please clarify my doubt and let me know if I am wrong.
These indexes and slowing down my load process.
-Vikas Bindra
June 2, 2009 at 3:05 am
It is impossible to give you a recommendation about the indexes without knowing what queries you have and the selectivity of the queries. An index that is created with include columns is created in order to create a covering index for a specific query. If the query returns few records, then the included columns won't help much because it has to do small amount of lookup. If the query returns lots of records, then the included columns can be very beneficial. If I'll assume that in your case both indexes (with the include) are very beneficial, most chances are that I'd have only one index based on Trans_type and account_no columns that also have the include columns Trans_type and current_date.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 2, 2009 at 3:48 am
Thanks Adi for your reply.
I am very new to the system. Still need to dig out what all queries are getting fired on this table. I have got around 12 million records in this table. And daily load is slowing down with more and more records inserted into this table. I have archived old data from this table, still not much gain.
I agree with you that I can not drop/change any index before i find out the queries,
But in general, if all 3 are very beneficial, should i keep only the one that includes Trans_type and current_date?
-Vikas Bindra
June 2, 2009 at 3:57 am
vikas bindra (6/2/2009)
Do I actually need NIX_WITHINCLUDE_1 and NIX_WITHOUTINCLUDE indexes?
I can't see any reason why you would. It is possible that SQL would use the smaller ones occationally, but the one index is a super-set of the other two.
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
June 2, 2009 at 6:57 am
Thanks Gail for the feedback!
The current system is really messed up; apart from the above example there are lot many others.
They also have loops and cursors all over the load process:crying:
-Vikas Bindra
June 2, 2009 at 7:37 am
vikas bindra (6/2/2009)
Thanks Adi for your reply.I agree with you that I can not drop/change any index before i find out the queries,
But in general, if all 3 are very beneficial, should i keep only the one that includes Trans_type and current_date?
If I wouldn't have time to check the queries, then this is what I'd do. If I'd have time to check it and see if I really need to the included columns, I'll might use a different index I wouldn't have more then one index (from the indexes that you wrote about).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 2, 2009 at 7:47 am
Thanks Adi!
Time is the problem...its always is:-)
-Vikas Bindra
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply