October 17, 2014 at 4:32 am
H, folks,
I have sql2012 for my web application which has heavy load. I run tuning advisor and it offers me that index:
CREATE CLUSTERED INDEX [_dta_index_TransactionsTable_c_6_1549248574__K29] ON [dbo].[TransactionsTable]
(
[BankResult] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
but I already have this one:
CREATE NONCLUSTERED INDEX [BankResult] ON [dbo].[TransactionsTable]
(
[BankResult] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
what are the differences ? I read that setting both ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS to OFF causes deadlocks under heavy load as they lock whole table, so for example for web scenarios (like mine) its better to set them ON. but I really don't understand the difference between mine and advisor's index. what should I do ?
interesting thing is advisor says that will give me %65 performance gain.
As I see advisor sets this index as PRIMARY, which I currently use ID as my primary index and I cannot change it as main access method to my records is using ID. but I'm concerned about other parameters.
any suggestions please ?
best.
October 17, 2014 at 4:48 am
The first one is clustered index, the second is a nonclustered index.
The difference is huge. A clustered index sorts the data according to its key, hence the clustered index IS the table.
A nonclustered index is an index on top of the existing table.
Basically this means the first index will sort your data according to BankResult.
Is this a unique column?
Do you often use BankResult in the WHERE clause or in the ON clause of a join?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 17, 2014 at 5:35 am
BankResult is not unique. and I use it in every query.
select ... where BankResult <> '-' or ... BankResult='APPROVED' or ....BankResult='DECLINED'.... there are 6 or more BankResult types which are varchar and cannot be changed to int.
only ID column is unique.
October 17, 2014 at 5:49 am
A clustered index doesn't need to be of data type int. Typically you would create your clustered index key as small as possible though.
I would just ignore the advisor (as many people do) and keep on working with the nonclustered index.
Maybe add some included columns to make the index covering.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 17, 2014 at 5:58 am
great, thanks.
have a great day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply