Server is idle and busy at the same time

Question

Server is idle and busy at the same time

Anurag Thakur

SSC Rookie

Points: 33
More actions
May 25, 2006 at 8:10 am

#113200

Hi, we have the problem that our SQL Server 2000 is sometimes 80 times slower than usual. How do we best go about finding the problem?
During a database upgrade a single user ran an upgrade script that during test runs executed in a couple of minutes. The same script with the same data would suddenly not finish in two hours. The script basically copies rows from the old to the new database.
While it ran slowly, we checked perfomance monitor: CPU utilisation was only 1% and Disk Queue Length stayed at 0 with regular bursts of 12 or so. From this we conclude that neither CPU nor the RAID disks were restricting the execution speed. What is there left that does so? Memory usage was at 4GB (out of 8)
Next time we manage to reproduce, we will lock at EM to see if there are any locks (although there is only one SQL process executing). Apart from that, what can we check to track down the cause of the immense slow-down? The causes we could think of (too little SQL Server RAM, bad indexes/statistics) would result in high disk activity, which we did not observe.
Thanks for your help -- Cornelius
Environment:
Dual core dual processor Opteron
8GB RAM
Windows 2003 Server
Sql Server 2000 SP4
AWE enabled

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

does your script include inline functions as calculations in the sql ?

If so I've seen this type of activity caused by functions. Why it does this on high performance systems I don't know - but I have seen similar.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Anurag Thakur SSC Rookie Points: 33 More actions · Answer 2

There are no inline functions. We even had the problem with a plain "insert into newdb.dbo.table(...) select ... from olddb.dbo.table"

dmanju Say Hey Kid Points: 685 More actions · Answer 3

you might want to check

select * from master..sysprocesses where blocked > 0 to see if there is any blocking happening. Also, reindexing and updating the stats might help. (unless you have already done that in which case I can only think of blocking!!) or when the problem happens, see if thats when the log file is growing. Often times, the log file or the data file is set to autogrow and when the file starts to grow, all transactions are pending till it is finished.

Anurag Thakur SSC Rookie Points: 33 More actions · Answer 4

Thanks, we will look at the locks when next it happens. The other causes (reindexing, stats, growing files) would entail disk activity though, wouldn't they, which is a missing symptom.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Are these databases on the same server for production? I mean, old and new database? I had cases where the development upgrade with both databases ran on the same machine and the actual production upgrade moved data from one machine in one Data Center to another machine in another Data Center. The difference was a couple of hours.

Another difference may be in the Recovery Model. Something related to bulked operations being logged or not. I assume that the disc activity may increase with additional logging so it may or may not be relevant

Output: there is a difference in how you monitor your process, if a process have to report the progress with every inserted line or not.

Did you check your RAID? Is everything OK with your RAID in production? It is a major producer of slow downs.

What is the RAID level anyway in Production comparing to Development?

Are you running Anti-Virus SW or Indexing Service?

Regards,Yelena Varsha

Anurag Thakur SSC Rookie Points: 33 More actions · Answer 6

The two databases (old and new) are on the same server.

The recovery model is always simple (and any problem here would result in high disk activity).

There is no output (we had the problem with a simple "insert into newdb.dbo.table(...) select ... from olddb.dbo.table").

The RAID hardware is fine (and new).

There is no AV but the indexing is on, but again, if that interfered there would be disk activity.

We have been trying to reproduce for days now and cannot. We will have another go at the live update and hope for the best.

Thanks for your help.

Lowell SSC Guru Points: 323518 More actions · Answer 7

does the new table have a lot of constraints on it? are the number of rows being moved in the millions?

could tempdb be expanding in size in 10mb increments when it needs to jump to a gig to handle all the work? that operation would not be resource intensive, and the problem might go away/not be reproducible until the server is rebooted, and tempdb goes back to a default of 10 meg or something.

I had an issue where all the check/default/foregn key constaraints added to a table really slowed down inserts done via an application; for the duration of that process, whenever we ran it we disabled constraints, let the application insert, then re-enabled constraints. maybe the combination of one huge transaction to grow the tempdb, plus all the constraints might be slowing it down.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!