We have a system which uses MARS to enable it to maintain multiple open queries (DataReaders) against a SQL Server 2005 (std edition) database within a single-threaded .NET process.[1]
What we find is that when we make requests on the MARS-enabled connection, we sometimes get response times measured at around 90-100ms, rather than the 10ms or so which is normal for our queries and hardware environment.
It seems that these longer times occur only when we:
a) go over 10 logical connections on the MARS-enabled physical connection
and
b) are not using a local shared memory connection between the application and SQL Server.
Even in that situation, we don't ALWAYS get the slower 90ms response times. Immediately after a SQL Server instance restart we get fast response times even beyond 10 logical connections. Then, sometime during the second run of the report, we suddenly start getting the longer response times on ~30% of the requests, and that effect continues on subsequent run. We don't get all requests being slowed -- there is a strong "bipolar" distribution, with around 70% still take <10ms
and ~30% take 90-100ms, with around 1% spread evenly between 10-90ms.
Restarting the SQL Server instance recovers the performance (until the 2nd run after the restart, of course!).
We have a couple of questions as a result:
1) Is this expected behaviour from MARS? Or is there something wrong with our environment?
2) Is making more than 10 logical connections via a single MARS connection supported at all by SQL Server 2005? Is the limit/recommendation to keep below 10 logical connections a hard limit, and does it apply equally to all connection types (tcp/ip, remote named pipes, local shared memory).
3) Is it reasonable for us to run the job from the SQL Server box, using a shared memory connection, which seems to avoid/sidestep this problem? What risk is there that the performance or stability of SQL Server may suffer now or in the future as a result of going over 10 logical connections on a single MARS-enabled physical connection?
Any help / advice gratefully received!
Misha
[1] The system has been converted from a COBOL-style cursor-oriented platform, so the structure of a typical job/report is:
for each record in table A
for each record in table B
for each record in table C
...
do something with C, using information from B and A
next C
next B
next A
Each of the for loops is implemented by opening a datareader on a common MARS-enabled connection, so in the middle there are 3 logical connections on that single physical connection. Some of the most complex reports have a nesting level of up to 13/14 levels!