May 28, 2013 at 10:08 am
we have a high activity transactional database and we have put NOLOCK hints on all SELECT statements...
for sometime now we are experiencing timeouts inspite of NOLOCK hint
I thought with NOLOCK hint we should not get timeouts ?
The same queries run OK from SSMS and we dont see issue with optimization..the problem occurs when WCF
makes hundreds of hits per min....
[It is OK for us to to have dirty reads because of NOLOCK hints but we cant have timeouts...]
May 28, 2013 at 10:18 am
Nolock is NOT an optimisation technique. If you're getting timeouts, tune your queries, tune your indexes. If blocking is a problem, tune your queries, tune your indexes and consider one of the snapshot isolation levels
Nolock is a way of telling SQL Server that you don't mind if your data is slightly incorrect, so it should go and ignore the usual isolation rules for queries.
You do know about the other data anomalies with nolock, the duplicate rows, the missing rows, correct?
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
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
May 28, 2013 at 10:22 am
WangcChiKaBastar (5/28/2013)
we have a high activity transactional database and we have put NOLOCK hints on all SELECT statements...for sometime now we are experiencing timeouts inspite of NOLOCK hint
I thought with NOLOCK hint we should not get timeouts ?
The same queries run OK from SSMS and we dont see issue with optimization..the problem occurs when WCF
makes hundreds of hits per min....
[It is OK for us to to have dirty reads because of NOLOCK hints but we cant have timeouts...]
Looks like very bad design to me. Does your database has anything to do with financial?
One way or another you are going to have some issues.
NOLOCK does not guarantee no timeouts. Timeout may easily happen when you use NOLOCK hint, it can be to do with your JOIN or WHERE clauses.
NOLOCK will reduce blocking but it has nothing to do with resource contention, plus you will get all sort of extra "nice" features such as missing uncommitted and committed (even long ago) records, double counting, returning something which does not exist and so on...
May 28, 2013 at 10:29 am
Nolock is a way of telling SQL Server that you don't mind if your data is slightly incorrect
...
Yeah, it can for example double count records:
Slightly incorrect in this case would mean returning the same record twice. Let say you are running profit report on pre-aggregated data and you are really lucky to double count largest figure, profit or loss will be "slightly" incorrect...
Or, NOLOCK may miss some existing records
Slightly incorrect in this case would mean not returning existing record at all. Let say you are running commission report on pre-aggregated sale data and you are really lucky enough to miss the monthly sales data for the best broker. I guess he will personally congratulate you (and most likely your development manager) for loosing your jobs...
Think twice, or even better three time, before using NOLOCK in "all SELECT statements";-)
May 28, 2013 at 11:21 am
Eugene Elutin (5/28/2013)
Nolock is a way of telling SQL Server that you don't mind if your data is slightly incorrect
...
Yeah, it can for example double count records:
Slightly incorrect in this case would mean returning the same record twice. Let say you are running profit report on pre-aggregated data and you are really lucky to double count largest figure, profit or loss will be "slightly" incorrect...
I think the best I've seen was a report (for upper management) whose total was around 30% above what the total should have been because a chunk of rows was read twice and that chunk included the highest transaction of the month.
That was the event I needed at that particular company to push for the removal of NOLOCK from every single place it was used.
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
May 28, 2013 at 11:36 am
To add something, this serie of articles can help you to understand about locks and some limitations of nolock to prevent them.
http://aboutsqlserver.com/2011/09/28/locking-in-microsoft-sql-server-table-of-content/
May 28, 2013 at 11:54 am
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).
Thanks for all pointers..I will revisit the select Queries and data model in general to see why timeouts are happening.
May 28, 2013 at 12:04 pm
Nolocks only reduce contention caused by locking. They don't reduce any other kind of contention. So if your queries are using too much CPU, nolock doesn't help. If your queries are waiting on access to the disk, nolock doesn't help. If you have lots of data updates occurring, nolock can't help you at all because data modification must do locking no matter what.
From the sounds of things, I think everyone else is right on. Instead of a traditional evaluation of what was running slow and why, you attempted to solve performance problems by addressing a symptom, not the cause of the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2013 at 3:44 pm
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).
Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
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
May 30, 2013 at 9:17 am
GilaMonster (5/28/2013)
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
Gail,
I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.
May 30, 2013 at 9:28 am
Luis Cazares (5/30/2013)
GilaMonster (5/28/2013)
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
Gail,
I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.
Where are your DBAs located (country)?
May 30, 2013 at 9:31 am
Eugene Elutin (5/30/2013)
Luis Cazares (5/30/2013)
GilaMonster (5/28/2013)
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
Gail,
I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.
Where are your DBAs located (country)?
Mexico
May 30, 2013 at 9:56 am
Luis Cazares (5/30/2013)
Eugene Elutin (5/30/2013)
Luis Cazares (5/30/2013)
GilaMonster (5/28/2013)
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
Gail,
I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.
Where are your DBAs located (country)?
Mexico
Is it one of the country the company operates within? 😉
If not, then why would they really care about data quality.
It's simple! Until business is really suffer financially, data quality issue will not be taken seriously...
May 30, 2013 at 10:06 am
It's simple! Until business is really suffer financially, data quality issue will not be taken seriously...
Agreed. I used to do some consulting for a company that administered health insurance. At one point their DBAs mandated that ALL queries use the NOLOCK hint to "speed things up and minimize resource contention". It did both of those things. I warned them profusely about how bad this was. Eventually, they started seeing very strange anomalies from time to time that nobody could explain. We authorized debit card transactions when the accounts did not have enough funds, or denied them when the accounts had plenty of funds. I kept telling them it was because of those hints. They continued to ignore me. Eventually they ran into enough issues that they authorized a full scale investigation into the cause. They lost thousands and thousands of dollars in approved transactions when there were not enough funds. Finally after months of investigation they "discovered" that the NOLOCK hints were causing the issue. At that point they repealed the mandate and went back through the 2-3 thousand procs and removed that hint. This little "go fast at the cost of all else" trick cost the company lots and lots of money in countless weeks of multiple developers and QA time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 10:07 am
Luis Cazares (5/30/2013)
GilaMonster (5/28/2013)
WangcChiKaBastar (5/28/2013)
our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.
If you're getting timeouts, tune the queries, don't address a symptom.
Gail,
I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.
Using one of the snapshop isolation levels is a far better solution for elimination of blocks and contention.
The "DBAs" saying the most important thing is not having blocks or contentions, may mean it's the most important thing to them, not the business users. The "DBAs" may be more concerned about lack of complaints from users than the accuracy of the data that the users are getting.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply