Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 27 Feb 2006 09:19:04 -0800
Message-ID: <1141060739.994866@jetspin.drizzle.com>


Mark Townsend wrote:
> Tony Rogerson wrote:
>

>>
>> Yes, sometimes it is funny; however, that demo did not go wrong !
>>
>> The example SQL is here: 
>> http://www.sqlserverfaq.com/controls/kbase/store/KB_31_CONCURRENCY.zip
>>

>
> Apologies - as I said, I only had the sound track. But it did sound like
> when you went to see the old version in temp in the SI part of the
> demo, it was no longer there ?
>
> Anyhow, I do have some questions coming out of your demonstration
> (sometimes demos are much more revealing than the doc).
>
> I think you mentioned that SQL Server copies the entire row being
> modifided into a version store in temp db to provide the read consistent
> view ? It does this for every row being updated at the same time ? So
> how do you size what is required - presumably the more users you have
> doing more updates, the larger the version store needs to be ?
>
> You mentioned that you didn't think that versioning added any additional
> overhead to a transcation or performance in the Q&A section. Is this
> because all the version rows are stored in memory (i.e temp is cached) ?
> Otherwise there would be additional IO overhead, right ? What happens in
> usage spikes - does memory dynamically allocate to increase the size of
> the version store ? What happens if you hit a hard memory limit ?
>
> How does SQLServer handle updates to a LOB ? Does it copy the entire LOB
> into temp as well ?
>
> How are the rows in the version store indexed ? If a system has a large
> number of updates going on on a heavily used table, then presumably
> there could be many 10,000's of rows in the version store at any one
> time. Some of the big tables under many of the packaged applications,
> for instance. How does the optimizer go looking for these ?
>
> I didn't quite understand what you were saying about triggers - the same
> version store is used to provide both the old and new versions of the
> data ? So it's possible that every row being updated has two copies in
> the version store ? Did I get this wrong ?
>
> You mentioned in an earlier post that the same version store was used
> for online index builds. I presume that this means that all updates to a
> table made after the index build starts are versioned ? Is it the same
> version copy used to satisfy user queries as well, or is this an
> additional version copy ?
>
> I also didn't quite understand what you meant about being an optimistic
> locking strategy. If user A is modifying a row, then a query by user B
> sees the old row, correct ? What is user B now makes an update to the
> row they got out of the version store ? Can they save this update ? What
> happens if user A now saves the same row back, with a different update ?
> Do both work, or does one fail ? Which one fails ? Can you mitigate this
> in SQL Server by doing something like a SELECT FOR UPDATE ?
>
> Last but not least, how does SQLServer do rollback ? Is it using the
> version store, or is there some other data structure being created to
> allow a transcation to be undone ?

And last but not least, what does this do to the required size of the log files? ;-)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Feb 27 2006 - 11:19:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US