SQL Server Performance Forum – Threads Archive

2 identical apps running is more than 5x slower

We use a 4GL language with SQL 2000 SP3/ODBC. As an example we have a report program which uses a FOREACH cursor to process records and output them to a report. If one instance of this report is running it takes [say] 1 minute to run. If I set off 2 at the same time, I would expect them to take roughly twice as long, but they actually run something like 5-10 times slower. We are using READ UNCOMMITTED and there is no blocking, and this is on a development server with no other apps running. I’ve posted this on another board, but just got some lectures about some fairly obvious stuff which I’ve already covered, as well as being told not to use cursors (which isn’t an option with our 4GL system). On a purely SQL level, does anyone know why two cursors fetching the same data at the same time might be this much slower?

Why isn’t it an option with your 4GL system? When this happens, what do the following counters in Profiler look like? Processor Disk write queue length Disk read queue length –List these two per disk and describe the disk (tempdb, data, log, etc) buffer cache hit ratio cache hit ratio –Do you see these two drop during this time? Look at the release list for SP4. About 15% of the issues addressed are dealing with issues. It’s possible you are experiencing one of the issues addressed in that service pack. We have had cursors in the past that accessed the same data simultaneously with no issues at all. We’ve also seen where that type of usage can bring down an entire system. I don’t know all the details of why though. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You might want to open a ticket with Microsoft. As a last note, you might not be able to eliminate cursors; however, I seriously doubt it’s not optional just because your systems is a 4GL system. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

To run a report in our 4GL system you must fire individual records/rows to a report function, usually using a FOREACH or WHILE loop and a cursor, eg:- START REPORT myrep … DECLARE mycurs CURSOR FOR
SELECT * FROM …. FOREACH mycurs INTO myrec.* OUTPUT TO REPORT myrep(myrec.*) END FOREACH FINISH REPORT myrep … With something like Crystal, a report can be based on an SQL statement, but this isn’t possible in our 4GL system. Most of our other 4GL apps use similar methods to process data too, not just the reports. Processor is 100% throughout with either 1 or 2 apps running.
Disk queue lengths almost zero throughout.
Buffer cache hit ratio – almost static at 88%.
Cache hit ratio – almost static at 99.8%. So far I’ve not been able to find a counter which increases dramatically when a second instance or the report is started. I’ll check with SP4. I have this installed on a test system. We can’t put this into production yet because we have AWE with 6GB RAM. Interestingly, if I fire off 2 different reports looking at different data, this doesn’t happen. The first one slows down a bit as you might expect, but nothing like this.

Look into the FAST_FORWARD options etc on cursor level.

]]>