Do local Hyperion User Groups (or a national group) exist?
Christopher W. Pritchard (CWP)
SQL 7.0, Visual C++ MTS Object with ADO:
We have a "Logging" MTS object which basically executes an insert
command. With a tester that we built, we make a large volume of calls
to this object. One scenario included 24 DOS sessions each making
100,000 calls to insert a record into SQL 7.0. It works fine, but what
we see is that after some period of time, all sessions stop executing
for a while, and then they all start again.
We originally had our ADO execute command timeout set to it's default
value of 30 seconds. When this "freeze" happened, many of our commands
contiually timed out.
We then increased the ADO execute command timeout value to 120
seconds. What we saw was that whenever we hit this "freeze", since our
timeout was now longer, it would eventually start up again if (what
ever happened on the SQL Server side) completed before 120 seconds.
Sometimes it would "freeze" longer than 120 seconds and this would
cause ADO to give the timeout error.
We have now set the ADO command timeout value to 0 which means wait
indefinitely until the commands completes. We have seen this "freeze"
happen for 4 minutes or less several times throughout the completion of
the 100,000 inserts by 24 sessions.
I didn't monitor the actual completion time, but in a span of 2 to 2
1/2 hours (maybe less) it completed all 2,400,000 inserts.
What we are trying to figure out is what goes on during this "freeze"
period on the SQL Server Side. Also, If during this "freeze" we try to
manually execute a query through the query analyzer...it does not
respond until this "freeze" is over.
Because of the large volume, does SQL Server stop or slow down command
execution to do some sort of tune-up?
Any ideas or suggestions would be appreciated. Thanks in advance for
Sent via Deja.com http://www.deja.com/
Before you buy.