sql locking problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » sql locking problem

sql locking problem

Rate Topic

Display Mode

Topic Options

Author

Message

SqlUser-369680

Posted Saturday, October 20, 2012 11:26 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 02, 2013 5:35 AM
Points: 18, Visits: 51

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

Post #1375143

GilaMonster

Posted Sunday, October 21, 2012 4:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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 2008, MVP
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

Post #1375157

SqlUser-369680

Posted Sunday, October 21, 2012 4:59 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 02, 2013 5:35 AM
Points: 18, Visits: 51

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

Post #1375158

GilaMonster

Posted Sunday, October 21, 2012 5:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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 2008, MVP
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

Post #1375160

SQLWorks

Posted Sunday, October 21, 2012 5:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:43 AM
Points: 33, Visits: 183

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

Post #1375161

SqlUser-369680

Posted Sunday, October 21, 2012 6:23 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, February 02, 2013 5:35 AM
Points: 18, Visits: 51

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

Post #1375164

GilaMonster

Posted Sunday, October 21, 2012 6:30 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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 2008, MVP
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

Post #1375165

GilaMonster

Posted Sunday, October 21, 2012 6:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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 2008, MVP
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

Post #1375168

« Prev Topic | Next Topic »

Permissions