|
|
Posted by David Portas on 04/25/06 17:52
drawnai@hotmail.com wrote:
> When the day comes that Microsoft runs parallel query against a
> sequential update, then it will be spotted in UAT (if I haven't already
> seen it,) and replaced with a cursor or some other cunning tactic.
>
> I don't imagine I'll ever see that however, because I don't suppose
> that Microsoft will rollback the row_number() (Over col1, col2, col3)
> function in SQL 2010, just to allow it to introduce its new "parallel
> sequential (tm)" technology - a process that allocates an unknown in
> advance amount sequential numbers of in parallel.
>
> Out of interest, how does one allocate an index in sequence in parallel
> against a table whose size is unknown at the start of the update
> without wasting huge resources calculating the tree sizes? Does one
> mark the entire index as "untouched", then excute massive parallel jobs
> against parts of it, ensuring never to touch the same record twice?
> Perhaps, it asks god how big each table leaf root is, and then assigns
> ranges of numbers of exact size, (taking into account any inserts or
> deletes that are going to happen, after it starts and before it ends)
> to ensure that when it distributes the job to it's SMP array, that each
> row gets a number that is exactly unique, and contiguous. Perhaps you
> could start at one end of the table, with the length of it (including
> any intermediate insert/deletes as extrapolated from a small piece of
> fairy cake), and start at the other end of the same, and then work
> inwards.
>
> My solution would be to simply use magic. This is guaranteed to work.
Even sequential scans don't always start at the same place in the index
due to enhanced read-ahead (AKA merry-go-round scan). AFAIK that
doesn't apply to index updates today but it could perhaps do so in
future. That's a good example of something you may be unlikely to spot
in a unit test (unless you simulate load). It will likely show up if
you test to peak production workload but do you always regression test
on that scale for every service pack? Engine changes have gone in SPs
before.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|