READ UNCOMMITTED - SNAPSHOT

Hi All,
I am having to run a "batch update" on a LARGE table that is used by a repor
t.
i.e.
BEGIN TRANSACTION
EXEC 5_minute_Procedure_On_Large_Table
COMMIT TRANSACTION
This batch update takes 5 minutes to run and during this time the LARGE
table is locked and prevents anyone from accessing the report since it uses
the LARGE table that is being updated in 5_minute_Procedure_On_Large_Table.
- this is not good
My "Sorta" Solution is:
READ UNCOMMITTED is "almost" the right solution - however it does not return
a SNAPSHOT of the table data as it was before the begining of the
5_minute_Procedure_On_Large_Table. Instead it shows the records as they are
being updated. - This gives distorted results
My question is - is there something like READ ONLYCOMMITED records?
If not - any suggestions?
Best Regards,
Mekim
P.S. For those interested in SQL 2005 - this would seem to resolve my issue
- but that's obviously not an option
http://msdn.microsoft.com/sql/defau...hotisola_topic6Mekim
In SQL 2000, there is no way to get a shapshot of the data before your proc
began. It doesn't exist anymore. So you have 3 choices:
1. Wait for the transaction modifying the data to finish. This is the
default behavior.
2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
new values of data that has changed. As you noticed, it does not guarantee
transactional consistency.
3. Use the READPAST hint. This will only show you data that is not locked.
It causes SQL Server to READ PAST locked rows. So it will show you only
COMMITTED data, as you asked for, but it won't show you ALL the data.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mekim" <mekim@.discussions.microsoft.com> wrote in message
news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
> Hi All,
> I am having to run a "batch update" on a LARGE table that is used by a
> report.
> i.e.
> BEGIN TRANSACTION
> EXEC 5_minute_Procedure_On_Large_Table
> COMMIT TRANSACTION
> This batch update takes 5 minutes to run and during this time the LARGE
> table is locked and prevents anyone from accessing the report since it
> uses
> the LARGE table that is being updated in
> 5_minute_Procedure_On_Large_Table.
> - this is not good
> My "Sorta" Solution is:
> READ UNCOMMITTED is "almost" the right solution - however it does not
> return
> a SNAPSHOT of the table data as it was before the begining of the
> 5_minute_Procedure_On_Large_Table. Instead it shows the records as they
> are
> being updated. - This gives distorted results
> My question is - is there something like READ ONLYCOMMITED records?
> If not - any suggestions?
> Best Regards,
> Mekim
> P.S. For those interested in SQL 2005 - this would seem to resolve my
> issue
> - but that's obviously not an option
> http://msdn.microsoft.com/sql/defau...hotisola_topic6
>|||Hi Kalen,
Thank you for your suggestions and it therefore looks like ReadUncommited
(or Read Past) are the best (and only) options
Best Regards,
Mekim
"Kalen Delaney" wrote:

> Mekim
> In SQL 2000, there is no way to get a shapshot of the data before your pro
c
> began. It doesn't exist anymore. So you have 3 choices:
> 1. Wait for the transaction modifying the data to finish. This is the
> default behavior.
> 2. Use the READUNCOMMITTED hint or isolation level. This wil show you the
> new values of data that has changed. As you noticed, it does not guarantee
> transactional consistency.
> 3. Use the READPAST hint. This will only show you data that is not locked.
> It causes SQL Server to READ PAST locked rows. So it will show you only
> COMMITTED data, as you asked for, but it won't show you ALL the data.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "mekim" <mekim@.discussions.microsoft.com> wrote in message
> news:0CB93CE7-A7E3-474E-BD82-CC981BB04A82@.microsoft.com...
>
>

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis