October 10, 2011 at 8:04 pm
Hi Guys,
We have SQL SERVER 2008 R2, and also we have an old transactional Stored procedure that is being called frequently(it inludes couple of insert and updates).I noticed that sometimes this stored procedure takes long time to run (1-2 Seconds)(generally it runs in less than 10 mili-second).when I checked that Stored Procedure I noticed that were lots of table locking hints(rowlock,updlock) in it.Should I remove these table hints?Because this stored procedure has been developed long time ago in SQL SERVER 2000.As far as I know ,SQL SERVER 2008 generally choose the best locking hint and we should not interfer on SQL SERVER tasks.
I would appreciate if someone help me on that.
October 10, 2011 at 8:56 pm
since it interrupt SQL Server Engine and enforce some kind of escalation level + index usage which might be bad one or insufficient for data entity size exist there .
So if needed ,you could either :
•Use just with (nolock) hints to avoid any locks or deadlocks resulted by select statements
•Or use the with (index) hints under hih cautions and for specific workloads
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 10, 2011 at 9:28 pm
Performace Guard (Shehap) (10/10/2011)
So if needed ,you could either :•Use just with (nolock) hints to avoid any locks or deadlocks resulted by select statements
Assuming that the process is such that the reduction of data integrity rules that NoLock causes doesn't cause unacceptable errors in the query results.
Nolock != free lunch. It is not a 'go faster' switch. It tells SQL that some degree of inaccuracy in the returned data is acceptable.
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
October 10, 2011 at 9:46 pm
To get more details about this subject of with (nolock):
Using with (nolock) could be used with pure select statements not in DML statements
If to use with (nolock), you should have use to know first if business wise could accept that or now since it reads dirty data or uncomitted data...
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply