SQL Server 2005 has so many new features that in my opinion if you read only BOL for a year you'd find something new every day. One of those is Multiple Active Result Sets or MARS. Multiple Active Result Sets is a new SQL Server 2005 feature that, putting it simply, allows the user to run more than one SQL batch on an open connection at the same time.
quote:Also the important thing to realize is that multiplexed execution DOES NOT mean parallel execution.
This doesn't jive with what I understand about MARS. Or maybe it is just worded ambiguously. I thought that named save points were disabled on MARS connections explicitly because the parallel execution through MARS does not guarantee the order with which the commands are executed.
I certainly agree that MARS doesn't mean parallel connections (Higher bandwidth) It has just one, time sliced connection.
But, my understanding was that MARS most certainly does use time-slicing to produce parallel execution of multiple batches. Am I wrong on this?
yes it has a time sliced connection. but time slicing isn't equal to parallel execution.
it uses time slicing on statements i mentioned. so if for example you have 2 select statements that are MARS-ed then both will be time sliced: slice 1: run select 1 part 1 slice 2: run select 1 part 2 slice 3: run select 2 part 1 slice 4: run select 1 part 3 slice 5: run select 2 part 2 ... etc
until both statements complete. i haven't seen anything that would give accurate results on how the sql server prioritizes things if both statement transactions are equal. but i'm still playing with that. however if you have a select and insert then when the insert comes in while the select is being procesesd it will get executed in full before select can proceed onward.
real parallel execution only comes with more than 1 processor core.
time slicing is only make belief parrallel execution.
But (in my understanding, I could be wrong) only the connection is time-sliced, not the processing. (Although, it is true that if you only have a single CPU that the OS with time-slice it) SQL treats the multiple commands coming through MARS the same as multiple commands from different connections and processes them in parallel. (With the standard locking stuff that has always been there to prevent corruption)
Of course, the whole issue is almost moot because there are extremely few cases where, even with MARS, parallel processing would ever occur, and fewer still where it would matter one way or another.