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: Mark Townsend <markbtownsend_at_comcast.net>
Date: Sun, 26 Feb 2006 15:44:09 -0800
Message-ID: <hoCdnWNP0dPVoJ_Z4p2dnA@comcast.com>


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 ? Received on Sun Feb 26 2006 - 17:44:09 CST

Original text of this message

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