query hints best practice.

Question

Post reply

query hints best practice.

David McKinney

SSChampion

Points: 10472
More actions
April 24, 2007 at 1:02 am

#172092

The following path is taken from a technical specification for an application to be developed in our company.
"Consider query hints to avoid locking data (or to prevent lock escalation) where appropriate. Each query should contain a query hint or a comment explicitly stating that no hint is required."
I would like to have peoples opinions on this statement. (I'll keep my own opinion to myself for now)

Thanks,
David McKinney.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Just a thought at this time ....

Like with all hints, try to avoid them !

And if you must use a hint, use it wisely.

In this case, why don't you develop using the connectionproperties "read uncommitted". IMO that's should have the same result a when using

the "with (nolock)" hint.

Also when connecting using "read uncommitted" you serve the vaster majority of your queries, avoiding the code-everywhere.

To be able to read uncommitted data may have it's consequences !!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

David McKinney SSChampion Points: 10472 More actions · Answer 2

Confession....I hadn't read what I was posting. I'd sort of ignored the first line, and just focused on "Each query should contain a query hint or a comment explicitly stating that no hint is required."

I hadn't digested that the doc was only talking about locking hints (and not index links.) So thanks ALZDBA for reading my post - I should have done the same.

BTW, developing using Read Uncommitted as default sounds pretty bold! Not for the faint-hearted!

Thanks,

Dave.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

HTH

... BTW, developing using Read Uncommitted as default sounds pretty bold! Not for the faint-hearted! ...

Indeed, but the same goes for coding a "with (nolock)" everywhere.

Doublecheck you have a manager's signature for that project-decision !

I guess you want to emulate the Oracle-versioning approach, In that case you may want to consider using the "new" sql2005 snapshot isolation level.

Check BOL for more info.