INDEX HINT or NO INDEX HINT

Question

INDEX HINT or NO INDEX HINT

sql-lover

SSCoach

Points: 18530
More actions
May 22, 2015 at 10:29 am

#318450

Got a deadlock problem between a SELECT and a DELETE statement. Modified an existing Index on my Test environment and it seems that the new Index fixes the deadlock. The issue is, MSSQL is not picking the new index unless I force it.
I can post the plan but it's Friday, I bit lazy today, lol ... but the actual execution plan is exactly the same except a key lookup against the clustered index, which is the one that generates extra locks and produces the deadlock.
I'm usually against hints, but "running out of bullets" here. If I use an Index HINT, then it picks mine, and the key lookup disappears.
The table is big, by the way, 32 million rows.
So ... hint or no hint, that's the dilemma? Any suggestions? ....

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If SQL's not picking your index, it doesn't think that it's optimal. Can't say much more without seeing the execution plan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 2

GilaMonster (5/22/2015)
If SQL's not picking your index, it doesn't think that it's optimal. Can't say much more without seeing the execution plan.

Knew that type of answer was coming.

But it's NOT optimal for my environment. The query finishes in 1 second, with either Index. But there is a key lookup on one, not the other. With existing Index there is a recurring deadlock, due extra pages being locked. With new one, there is not.

Let me try to upload the execution plan...

sql-lover SSCoach Points: 18530 More actions · Answer 3

sql-lover

SSCoach

Points: 18530

May 22, 2015 at 10:42 am

#1800089

Ok Gail,

Here is the execution plan...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sql-lover (5/22/2015)
But it's NOT optimal for my environment.

Well then no, the optimiser won't chose it. It's going to look for the most efficient index, if you're creating an index that's intentionally sub-optimal then the query shouldn't use it, whether by choice or forced..

And please post the execution plan, not a picture of the execution plan. The picture's missing 90% of the information that anyone trying to help needs. You can do a find/replace through the XML if you want to obfuscate the table names.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 5

GilaMonster (5/22/2015)
sql-lover (5/22/2015)
But it's NOT optimal for my environment.
Well then no, the optimiser won't chose it. It's going to look for the most efficient index, if you're creating an index that's intentionally sub-optimal then the query shouldn't use it, whether by choice or forced..
And please post the execution plan, not a picture of the execution plan. The picture's missing 90% of the information that anyone trying to help needs. You can do a find/replace through the XML if you want to obfuscate the table names.

The new Index is the same, with 2 new columns in the INCLUDED part, that's it. That alone, eliminates the key lookup.

I am not creating a suboptimal Index. I am troubleshooting the problem. And I discovered that the existing Index needs two more columns to make it cover.

Can you please provide the link so I can obfuscate the table names?

sql-lover SSCoach Points: 18530 More actions · Answer 6

sql-lover

SSCoach

Points: 18530

May 22, 2015 at 11:24 am

#1800098

Attached the execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sql-lover (5/22/2015)
And I discovered that the existing Index needs two more columns to make it cover.

And you added two columns to the existing index? Or created a new one with two additional columns?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 8

GilaMonster (5/22/2015)
sql-lover (5/22/2015)
And I discovered that the existing Index needs two more columns to make it cover.
And you added two columns to the existing index? Or created a new one with two additional columns?

Gail,

I Added two more columns to the INCLUDED column list. That was my only change.

Not at my PC right now but I think we have 3 NCIs. After that change, it picked another NCI but not the modified one. The modified one does a seek without the key lookup.

I should also say that I refresh stats on the lab just to check the optimizer but keeps picking a different NCI.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Can you post the table definition and CREATE INDEX for all three indexes please? I may only get around to looking at this in detail tomorrow.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 10

GilaMonster (5/22/2015)
Can you post the table definition and CREATE INDEX for all three indexes please? I may only get around to looking at this in detail tomorrow.

Thanks! I will ...

sql-lover SSCoach Points: 18530 More actions · Answer 11

GilaMonster (5/22/2015)
Can you post the table definition and CREATE INDEX for all three indexes please? I may only get around to looking at this in detail tomorrow.

Had to manually obfuscate the name of the columns, so hopefully I did not mess up. But here's the Index definition:

CREATE CLUSTERED INDEX [CI] ON [dbo].[MyTable]

(

[col2] 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, FILLFACTOR = 90) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI1] ON [dbo].[MyTable]

(

[EmployeeID] ASC

)

INCLUDE ( col2,

col14,

col10,

col11,

col12) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI2] ON [dbo].[MyTable]

(

col14 ASC

)

INCLUDE ( col1,

col13,

col3,

col100,

col4,

col5,

col6,

col7,

col10,

col11,

col200,

col300,

col400,

col500,

col8,

col12,

col600,

col700,

col9,

col15) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED

(

[col15] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

Notice col9 and col15 on NCI2. Those are the two new columns I added.

Here is the obfuscated query:

SELECT Column1, Column2, Column3, Column4, Column5, Column6,

Column7, Column8, Column9, Column10,

Column11, Column12, Column13, Column14, Column15

FROM Schema1.Object1 Object2

--Without below HINT, this modified Index is not chosen anymore.

--WITH (INDEX(Column16))

WHERE Column1 = ?

AND Column2 between ? and ?

AND Object2.Column14 =?

As you can see... NCI2 is a covered Index. Before, it was not. NC2 basically has all the columns on the INCLUDED list.

After adding those two extra columns in the INCLUDED list of NC2, the optimizer now picks NCI1 and continue doing a Seek plus the Key lookup. Before, with original Indexes, there was also an Index seek plus a CI lookup, but using NC2 (old version)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

The obsfucation has lost data.

You said NCI2 is used, but the key column (Col4) is not filtered on in the query and hence SQL can't seek on it. However the forced plan shows a seek, so one of them is wrong

I'm not in the mood for trying to figure out what got lost where, so an optimal, covering index for that query (as specified in your last post) will be:

Key columns - (Col1, Col14, Col2) -- in that order, or maybe (Col14, Col1, Col2)

Include (Column3, Column4, Column5, Column6,

Column7, Column8, Column9, Column10,

Column11, Column12, Column13, Column15)

Optimiser should use one of those automatically

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass