Best Performance? SELECT for a 'Snapshot' (Current Data)
-
We have a 3rd party ERP System. I develop solutions for information using Access Reports and exporting query results to Excel. More and more I am using SSMS to develop the queries. Likewise, more and more I end up creating an Access Pass Through Query rather than use JET (and now I'm learning to review both solutions to determine which one is more efficient).
Always one to tweak things to strive for better performance (and not diminish someone else's because of what I am doing) I had discovered a while back to use the following in my query windows:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
It also removed the issue of occasionally having executed a SELECT and then leaving the results 'intact' - locking someone.
The solutions referred to are 'snapshots', i.e. Open Orders status, Current Inventory, YTD Sales, even Current Labor (Manufacturing) Activity.
No one is concerned that if they had run the report "a second later" one last piece of information might be different.
I just came across a tip that sounds like I can do better than READ UNCOMMITTED by using SNAPSHOT.
The article included a list of SQL Server Isolation Levels.
The web page is:
MSSQLTIPS - http://www.mssqltips.com/sqlservertip/1646/execute-sql-server-select-command-without-locking/%5B/url%5D
I will leave the default to READ COMMITTED for the ERP System.
However, when I am developing and the solutions delivered to the users for retrieving the "current information":
Is the SNAPSHOT more efficient than READ UNCOMMITTED? It sounds like some locks overhead exists using READ UNCOMMITTED. I read: "Equal to a NOLOCK hint for an individual SELECT statement which means that shared locks are issued and no exclusive locks are honored."
It sounds like the SNAPSHOT does not include any locks overhead. The trade off being that if the 'dirty read' is rolled back, then the information is incorrect. In our situation, most of the time, virtually all of the time, there is COMMIT, not a ROLLBACK.
Is my understanding correct?
As I mentioned, we use Access as a Front End for reporting and moving data to Excel and, more and more, I am using Pass Through Queries. It sounds like I can add the NOLOCK hint to the SELECT statement and achieve better performance* Is this correct?
*Subjective analysis of performance - most SELECTS take less than 2 seconds to execute, so, the performance is not significant in and of itself. But, trimming a little off - especially those rare moments when a number of people are 'hitting on the server' - there can be a perceived benefit.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
-
Hello,
The SNAPSHOT isolation level would have to be enabled at the database level before you could use it, so you will want to check with the DBA, if that's you, decide if that is feasible for your server.
From your description you have simple queries running very quickly already (sub 2 sec), so I doubt you will ever notice the difference.
-
The SNAPSHOT isolation level would have to be enabled at the database level before you could use it, so you will want to check with the DBA, if that's you, decide if that is feasible for your server.
From your description you have simple queries running very quickly already (sub 2 sec), so I doubt you will ever notice the difference.
I'm the DBA - albeit, I "don't know much beyond some basics". Our ERP Vendor has someone who handles configuring SQL Server and installing the ERP DB when we upgrade. I was going to try SNAPSHOT vs READ UNCOMMITTED in my little stopwatch utility and SQL Server informed me about having to ALTER DATABASE...
Thanks for the rough analysis - probably insignificant difference.
I appreciate your taking the time to respond. It saves me digging through google hits trying to decipher various information.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
-
The options you're weighing are not as relevant for performance as they are for data integrity.
Three isolation levels were mentioned:
1. READ UNCOMMITTED (same as using targetted NOLOCK hints on all tables) - this is a VERY bad thing to do in almost every conceivable scenario. This iso level impairs the database engine's ability to maintain data integrity. NOLOCK might be alright for getting a quick peak at some data but if you're using that data for anything important then you are risking incorrect results, i.e. looking at data that is never actually committed to the database so never really exists anywhere except according to your query. I cannot stress highly enough how detrimental using this iso level can be for applications or analysis where data integrity is important, and when isn't data integrity important?
2. SNAPSHOT - requires code to set this iso level before starting transacitons so probably will not work with your ERP system unless it says it supports it as a config option.
3. READ COMMITTED - this is the default for SQL Server and generally does not need to be overridden with a hint or by explicitly setting the isloation level.
A common way to increase concurrency in databases where there are lots of concurrent reads and writes, and as a consequence blocking may occur, is to enable the READ_COMMITTED_SNAPSHOT.
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;* exclusive access to the database is required to change the setting
From Isolation Levels in the Database Engine:
When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation behaves as it did in earlier versions of SQL Server. Both implementations meet the ANSI definition of read committed isolation.
The change to enable READ_COMMITTED_SNAPSHOT is transparent to application code, i.e. the database engine manages the change to the iso level which makes it a very nice option, especially for databases supporting third-party software. Enabling this is a MUCH safer option than ever using the NOLOCK hint or the READ UNCOMMITTED isolation level to reduce blocking. Again, this says nothing about query performance, this is about data integrity and avoiding blocking. Now, databases with heavy blocking can appear to be slow so the net result to your users may be "wow, the app is much faster", but we know why and it has nothing to do with the query actually running faster it's just that it's now running unimpeded by other queries that were previously likely to block it. Note that additional stress is added to the tempdb when READ_COMMITTED_SNAPSHOT is enabled (lookup Version Store for more info) but not so much that it has become a problem on any system where I have enabled it, just watch your tempdb usage.
More info:
Understanding Row Versioning-Based Isolation Levels
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
NOLOCK is not a performance tool and when used as such is dangerous.
-
Thank you both, Artoo22 & opc.three for your replies. I read the links.
The following is what I got out of the articles. Let me know if I'm on the right track or not...
"Understanding Row Versioning-Based Isolation Levels" was difficult to digest. I think what's significant for me to take away from this is that there is still overhead.
NOLOCK is not a performance tool and when used as such is dangerous.
http://eohmicrosoft.blogspot.com/2012/08/nolock.html%5B/quote%5D
Excellent article, for me, to understand what is going on under the hood. This covers activity not explicit in the other article. Both are important.
Bottom line - It sounds like there is still overhead activity when using the various isolation levels. Hence, what I thought would be a performance gain isn't at all.
It looks like I leave the default setting intact, READ COMMITTED.
Thanks again. I appreciate your helping me learn all this.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply