Default table lock hint on SQL Server 2005/2008

How do you look up a default global table locking hin开发者_开发知识库t?

-- Questions

1. Are there any DMV/DMF (Dynamic Management View/Function) that return such information?

2. And also, is there a way to change the default lock hint?

Currently I am adding nolock hint almost everywhere to prevent locks.

I'd like to avoid doing so by changing the default lock hint to nolock so that existing stored procedures do not need to change.

There is no global setting. The default is always READ COMMITTED

It can be changed at

• session, batch using [SET TRANSACTION ISOLATION LEVEL][]
• table using table hints
• database level for snapshot types using ALTER DATABASE ..xxSNAPSHOTxx

NOLOCK everywhere is utterly misguided And here too:

• Is the NOLOCK (Sql Server hint) bad practice?
• When is it appropriate to use NOLOCK?
• Get rid of those NOLOCK hints…
• Why using NOLOCK is bad..

Edit: After comment about query timeout...

A query with NOLOCK can still consume massive CPU and IO resources. Locking isn't that big an issue. If it is, then another query is taking too long, probably consuming massive CPU and IO resources...

I am not aware of any such global setting. IMHO even should that exist there can be little justification for using it.

You can however set the isolation levels to control whether individual transactions are able to read changes to data made by other transactions. This is done via

SET TRANSACTION ISOLATION LEVEL