February 13, 2012 at 8:21 am
We have a report that runs on an audit table.
The audit table has a description/notes/comments field which the report has to parse using a LIKE statement 'ActionCode = 7%' for example. (We know what the description field starts with, but the rest is variable after that)
Granted, designing the table with additional lookups would have been preferable, but this was done years ago and we aren't going to normalize it today 🙂
Anyway, the SELECT statement (for the purposes of this discussion) is "optimally tuned".
I am wondering about SELECT blocking.
I could use NO LOCK or perhaps better (is it better?) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
A few questions:
What are the downsides of this (other than dirty reads which we are not concerned about, this table only has inserts no updates/deletes)
I assume doing dirty reads will help reduce the risk of blocking due to the SELECT statement working through a large set of reads/long running query?
Any reason that I should use NO LOCK instead of setting the transaction isolation level? If I am using a sproc, then doing this is fine, as that isolation level resets once the sproc completes.
If I were to set that inside the query window, then it would remain at that isolation level until... the connection closes and /or I set it to a different level specifically?
February 13, 2012 at 8:30 am
Did you investigate to see if the queries are actually being blocked?
Inserts can still cause problems with read uncommitted isolation, so you would be better off staying away from it.
If you do have blocking, you would probably be better of going with the one of the row version isolation levels, either snapshot isolation or read committed shapshot.
February 13, 2012 at 8:37 am
Read this article about NO LOCK and dirty reads. It is more than just non-committed transactions and it is by no means a magic "go fast" pill.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
A few questions:
What are the downsides of this (other than dirty reads which we are not concerned about, this table only has inserts no updates/deletes)
Do you care about duplicate or missing data?
Any reason that I should use NO LOCK instead of setting the transaction isolation level? If I am using a sproc, then doing this is fine, as that isolation level resets once the sproc completes.
If you still going to use dirty reads using snapshot isolation is preferred.
_______________________________________________________________
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/
February 13, 2012 at 8:42 am
Assuming you have severe blocking (and not just slow queries), have you considered snapshot or read committed snapshot isolation levels?
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
February 13, 2012 at 8:56 am
I was wondering about that.
From what I understand it will basically just take a snapshot/temp-copy of the data in a differential sense. So other operations can be performed against the table while the SELECT statement runs?
Also, thank you to the other posts.
I am going to read through the linked articles.
February 13, 2012 at 9:06 am
Err, not exactly. Data modifications put the older versions into TempDB so that selects can read them if necessary.
First thing though is to see if you really do have blocking, or just a rather slow query.
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
February 13, 2012 at 10:17 am
I don't think it will hurt for you to have nolock on.
Any stored procedures that I write I add that in, regardless of how long something takes to run. A lock and error can occur in a query that only runs for a second, technically. So Nolock won't hurt.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 13, 2012 at 10:21 am
Well, No Lock could really screw up your results though if dirty reads are not acceptable (exact counts, financial related work, multiple queries that rely on related results to tally/total/sum up properly, etc...).
Unless you just mean sprocs that don't "care" if they get exact results/ clean reads?
February 13, 2012 at 10:22 am
kschwid (2/13/2012)
I don't think it will hurt for you to have nolock on.Any stored procedures that I write I add that in, regardless of how long something takes to run. A lock and error can occur in a query that only runs for a second, technically. So Nolock won't hurt.
So you don't care if the data has extra rows, missing rows, or if you get an error because one of those rows was deleted?
Jared
CE - Microsoft
February 13, 2012 at 10:30 am
Well in my case Stored Procedures are used for Reporting Purposes and no I don't care because you report on the day, week, month prior. So the data shouldn't be changing.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 13, 2012 at 10:34 am
kschwid (2/13/2012)
Well in my case Stored Procedures are used for Reporting Purposes and no I don't care because you report on the day, week, month prior. So the data shouldn't be changing.
It doesn't have anything to with when the row was added. It has to do with pages. If a page is being reallocated during your select it has no bearing on when the row was created or edited.
Read the article I posted. It explains what NO LOCK really does and the implications of using it.
You should use isolation instead of NO LOCK for your reports.
_______________________________________________________________
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/
February 13, 2012 at 10:35 am
kschwid (2/13/2012)
Well in my case Stored Procedures are used for Reporting Purposes and no I don't care because you report on the day, week, month prior. So the data shouldn't be changing.
Well, clearly you know what you are talking about. Nevermind the fact that it does not matter if the data from last millenium is not changing... The page could be getting split which would make your report results wrong and inconsistent. If my boss ran a report that he found out had the potential for bad data, especially sales figures, billing, or any other critical stats that are used for Board discussions... I would be fired. Please don't give advice to people when you don't understand what is really going on.
Jared
CE - Microsoft
February 13, 2012 at 11:25 am
kschwid (2/13/2012)
Well in my case Stored Procedures are used for Reporting Purposes and no I don't care because you report on the day, week, month prior. So the data shouldn't be changing.
And if the data you are querying won't be getting changed, why use nolock at all? It's not a 'go faster' switch, if there are no locks on the data you're querying, why risk the side effects (which can occur even if other rows in the table are getting modified depending on your indexes) for no gain?
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
February 13, 2012 at 11:41 am
Couple of things:
Firstly Read Isolation Uncommitted is basically the same as NoLock.
Secondly you can use NoLock on only a table or two in your query rather than all tables in your query like isolation.
Thirdly unless you use Serializable for your isolation level its still has potential to give you phantom data.
This link might be of some interest (or not)
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#READ_UNCOMMITTED_.28dirty_reads.29
I do understand that NoLock is not a fix all but there are purposes for it, or it wouldn't exist.
I also stated before that I only use them for reporting, so there are no inserts or updates involved, using NoLock on some of the tables in my query can optimize performance and still give little chance for corrupt/phantom data.
I am sorry if it isn't useful for you or your company but others can find a reason for it and just because you don't understand something doesn't mean you should get rude with other people who do understand/have a use for it.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 13, 2012 at 11:53 am
kschwid (2/13/2012)
I also stated before that I only use them for reporting, so there are no inserts or updates involved, using NoLock on some of the tables in my query can optimize performance and still give little chance for corrupt/phantom data.
Of course there is a use for it, but in extremely rare cases and never as a performance tool. You told the OP that you saw no problem in this case, which was hardly detailed enough for you to make a sound judgement on that.
What you mentioned above means to me that the data is read only (no updates or inserts). In which case NOLOCK does absolutely nothing. If it is not read only, how are you saying that there are no inserts or updates?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply