| Author |
Topic |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2009-07-09 : 05:22:40
|
| Hi,whether forcing of index name is advisableeg:non clustered1 index (column 1,column2,column3)non clustered2 index (column3)clustered index on other columnexecution plan refers non clustered1 index scanselect column name from table name with (index = nci,nolock)where index column1 = 1and index column3 = 1it shows seek if i force index name |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 05:47:35
|
select column name from table name with (index (nonclustered1), nolock)
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2009-07-09 : 06:13:22
|
| Thanks peso,I want to know forcing of index name is advisable for update\delete |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 06:56:10
|
No, it's not.And it's not advisable for SELECT either, just as a last resort when all other measures have been taken and failed.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2009-07-09 : 08:11:53
|
| Any specific technical reason please explain in detail \any reference sites |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-09 : 10:09:55
|
| It's because the DB engine knows what it is going to do and more or less how how much CPU/IO it costs. If it has accurate information then it can 99.99% make the right choice. With very few exceptions you should never need to tell it how to do stuff, just the "what". If you find yourself giving index hints then more likely you have out of date statistics or have missed off vital info that you know but you have not told the database, such as not null, uniqueness constraints, PKs/FKs etc.That said, sometimes it does go pearshaped, but try everything else first.Oh, and drop the NOLOCK. It's extremely rare that it's what you want. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 12:06:31
|
Well, extremely rare is relative term, isn't it? ;-)Not long ago (a year actually) I came across a Data Warehouse which was updated once per night.During the day there were no activity to mention (maybe about 10 inserts, compared to the millions of inserts nightly).And since there were 4,000+ simultaneous users, we went for the NOLOCK hint to avoid most blocking situations.It went very well. There were reported some issues (about 2 per day) where the count for some queries were wrong, but according to their current SLA that was acceptable.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-11 : 09:23:26
|
quote:
Originally posted by Peso
Well, extremely rare is relative term, isn't it? ;-)Not long ago (a year actually) I came across a Data Warehouse which was updated once per night.During the day there were no activity to mention (maybe about 10 inserts, compared to the millions of inserts nightly).And since there were 4,000+ simultaneous users, we went for the NOLOCK hint to avoid most blocking situations.It went very well. There were reported some issues (about 2 per day) where the count for some queries were wrong, but according to their current SLA that was acceptable.
N 56°04'39.26"E 12°55'05.63"
Yeah DW is different. Since 2005 though I would go the row versioning stuff rather than nolock. At least you get a result that was true at some point in time. As you know, with nolock it can be a bit of a lottery and you can get results that were never true if updates are occurring. |
|
|
|
|
Forcing of index name