SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql locking problem


sql locking problem

Author
Message
SqlUser-369680
SqlUser-369680
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 52
Hi,

I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result.
The problem is when more users are trying to generate the report, it seems that it takes more time.

My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.

update table
set status = 1
where keyId = 6


I don't have any locking on my select nor update.
What is the best practice to resolve it?
I have 100 users nationwide aside from report users, please need help.

Thanks in advance
Dabuskol
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86625 Visits: 45250
You always have locking on updates, you will have locking on the select unless in one of the snapshot isolation levels.

Are you seeing blocking, or is the update just slow?
What's the entire process that the update is part of?
Got any triggers on that table?

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


SqlUser-369680
SqlUser-369680
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 52
No Triggers.

But I have a lot of Select where it exists on many different reports.
What I did for now is to add from my select statement on all reports
"WITH (NOLOCK)", dirty read is no problem at all.


Regards
Dabuskol
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86625 Visits: 45250
Bad, bad, bad idea. Unless your users are happy with their reports being potentially inaccurate (have you asked them it 'mostly correct' is acceptable? Have you asked your manager if making the reports 'mostly accurate' is an acceptable fix?).
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

I think you need to have a read through chapter 6 (at least) of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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


SQLWorks
SQLWorks
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 195
He did say "dirty read is no problem at all"...so what you are asking for help with is the performance of the reports, is that correct?
If you are trying to determine who is blocking who, click the link in my sig I just posted a blocking script that could help you, if you're looking for something else please give us some more context. How often are these tables updated? Why are you running that update during the production day, can it be run off hours?



SQL Tips and Scripts
SQLWorks Blog
SqlUser-369680
SqlUser-369680
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 52
Thanks for the infos.

My insert and update is a common transaction every seconds considering the number of users I have.
The application is a tracking system where approval and confirmation is often needed in order to approve and implement a certain request.
My problem is that I often ecnounter nowadays "time out expired" only to found out that one my many report is running slow and after some investigation found out that this one is causing the time out.

I need to solve this issue becuase It even happens in the night wherein users are stock and can't work.

That is the very reason I used NOLOCK just to overcome the locking issue but as per best practice it is not recommended.
What is the best approach to this problem? Our administrator is not that experience to explain to me in details what is written in the recommendations.

Solving this problem for more than 1 month though it happen Isolated but it affects the integrity and performance of the application.

Thanks in advance
Dabuskol
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86625 Visits: 45250
SQLWorks (10/21/2012)
He did say "dirty read is no problem at all"...


Yes, but in my experience most people who say that don't realise what that means. It's not just a case where the report might show old values for a row that's just been updated, that is the least of the anomalies that nolock can cause.

I had an architect who maintained that position and insisted on nolock on all reports, until I showed him a report that had 3 of a particular client's transactions duplicated on the report, resulting in the total being out by around 40%.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86625 Visits: 45250
SqlUser-369680 (10/21/2012)
What is the best approach to this problem?


Please read the book chapter that I referenced.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search