Post #196,150
2/23/05 8:45:50 PM
|
MS Sql Server Slowness
Unfortunately from time to time I have to work with / tolerate Sql Server. And of course, it's gone weird.
I'm inserting records via ODBC (on an other box) and today it's writing at 2 - 3 rows per second, instead of the usual several hunderd rows/sec.
The box feeding the data is running merrily (so it's not slow to feed the records), and the SqlServer box is only at 5% cpu usage, if that. Yet, the data is being inserted slower than I could type it.
The SqlServer process is using around 400MB Ram, no idea if that is normal or not. The server box itself has 512MB. It says it's running SqlServer version 8.00.
I've just truncated the log and shrunk the database (heck, at least it looked like I was doing something :) but that made no difference, apart from freeing up some disk space.
My current guess is that the box (which I don't have physical access so [after all, I'm a programmer :) ]) is sitting there disk-thrashing itself into oblivion. I'm almost inclined to let it. BUt I have people drumming fingers impatiently upon tables, waiting for a result. Additionally, the sooner I get this out of the way, the sooner I get back to a real database :)
So. (a) Do you SqlServer experts recognise this as some common thing, that is fixable?
(b) Or will I just cut my losses, reboot the box, and see what happens?
Thanks muchly in advance,
John.
Two out of three people wonder where the other one is.
|
Post #196,152
2/23/05 8:55:59 PM
|
If this is abnormal...
then look for what changed.
Rebooting might solve it. Network trouble could potentially explain it (dropped packets and all that).
My first guess would have been that certain coding techniques were used (eg using recordsets in VB without realizing that that sends an update once per field per row, which really adds up), though your insistance that normally you go much faster suggests that that isn't your problem.
Also note that Sybase (and therefore likely SQL Server) will slow down drastically once a row of data no longer fits in a single packet. But IIRC, drastically is a factor of 4-5, not several hundred.
Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #196,154
2/23/05 9:06:08 PM
|
It's a daily process
And I can say 'nothing has changed' with all the credibility that a programmer can summon when s/he says that :)
I since found a query someone else was running (emphasis on the 'was' part), and it's now picked up the pace to a blinding 4 rows per second.
Darn.
/me wanders off to find the 'please reboot this box' paperwork :(
If only there was a 'convert to Oracle so it might actually work' form :)
Hi, my name's John, and I'll be your database bigot this evening...
|
Post #196,153
2/23/05 9:03:41 PM
|
I agree with your diagnosis.
NOTE: I know nothing about SQLServer.
Anything eating 400 MB on a 512 MB box running Windows is probably going to cause much disk thrashing. Windows likes to keep a big hunk of RAM free to make loading new programs seem fast. (E.g. this 768 MB box running Win2k, Thunderbird (18 MB), Mozilla (94 MB), BOINC (10 MB), NAV (NOPDB=50 MB), Acrobat Reader (35 MB), Copernic Desktop Search (33 MB), and a bunch of the usual utilities has 294 MB RAM free and 84 MB paged kernel memory). My guess is that it's throwing stuff it needs out to the pagefile and thrashing itself.
More RAM would probably help a lot.
Rebooting it would probably help, at least until the SQL process eats 400 MB again.
My vote is for rebooting it (once everything is put away properly). If it just started acting this way, I'd probably make sure the virus and spyware protection was up-to-date too... :-(
Luck!
Cheers,
Scott.
|
Post #196,156
2/23/05 9:27:01 PM
|
Is there any way this can be done with DTS instead of VB?
Are there a lot of indexes on the table(s) you are doing the inserts into? IOW, could it be that your data is causing a lot of page splits for the indexes? If Sql Server is having to re-org its index trees because of a patindex setting during your inserts a lot, I've seen that hit performance drastically (although v8/Sql 2000 is much better, so this might not be your problem).
You might not be able to do this, but if you can drop the indexes prior to the insert and rebuild them post bulk insert that might help (of course if you - as you should - have a clustered index on the table(s) you are inserting into you should not drop those indexes, and yes, I realize what I'm suggesting).
I used to write a lot of VB6 "insert" programs, but found that using a DTS package performed much better.
HTH.
bcnu,
Mikem
Eine Leute. Eine Welt. Ein F\ufffdhrer.
God Bless America.
|
Post #196,158
2/23/05 9:45:12 PM
|
Not a VB program.
The program itself is fine - it's in DataStage, which is what DTS aspires to be when if grows up.
I confess I don't have the time / interest (ok, primarily the interest) to research it - I'm getting the box rebooted. If that fixes it, I'll just moan about Windows/Sql Server some more, and move on...
Thanks heaps for your ideas though - if I end up having to look into the problem closely, at least I'll now know where to start :)
Two out of three people wonder where the other one is.
|
Post #196,157
2/23/05 9:35:49 PM
|
is it a winders box?
512k, win2k is going to camp on about 180megs ram, the app is using 400 so the disk is swapping heavily but it should be quicker than that. What happens if you gracefully bring the server down and up, if that doesnt help reboot. If that doesnt help, move the table locally to see if it thrashes just as hard. After that you should have enough experience to get a better job elsewhere :-)
regards,
daemon
may you go in beauty
|
Post #196,164
2/23/05 10:53:37 PM
|
ICLRPD (new thread)
Created as new thread #196163 titled [link|/forums/render/content/show?contentid=196163|ICLRPD]
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #196,166
2/23/05 11:52:04 PM
|
The reboot fixed it. Thanks all for you help!
Two out of three people wonder where the other one is.
|
