Slow SQL Server
-
We're running SQL2K on a Win2K server, dual processor, 2Gb memory, blah blah
The database serves it's data to IIS running ColdFusion and ASP on a different server.
Usually this runs fine, but recently we've seen the server gridning to a halt with 100% processor usage on both processors and the memory very slowly being eaten away. So slowly that you really have to watch to see it.
On each occassion we've had our users call complaining that the system appears to run slowly when deliveing data driven intranet pages.
Each time we have run the query analyser and run benchmark queries to judge the time delay and we see no slow down. The performance monitors show that the cpus are maxing out and that the Memory.pages/sec is going through the roof.
Each time, we have also tracked the problem down to a connection or two from IIS asp pages (supplied by an external vendor) that have been running for 4-5 hours, still marked as RUNNABLE in the connections, not apparently doing anything, devouring huge amounts of CPU time, and listing nothing more than sp_cursoropen;1
Like I said the connections don't appear to be doing anything, but the moment you kill them, the processor immediately drops to more usual levels.
Has anyone else experienced this or know what causes it as it is driving me crackers.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion. -
Try running SQL Profiler and then access the problem pages. It would be best to do this in a test environment so you can limit the activity of the server. I expect you will see a pattern emerge.
-
Yup, Profiler is your best bet here. One thing I'd add is to have Performance Monitor counter logging going at the same time watching memory, processors, disk usage, paging, etc. at the same time.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
From the fact that you're seeing "sp_cursoropen", I say that these pages are doing a lot of recordset processing.
ie:
While recordset1 not EOF
open recordset2 with recordset1 parameter
while recordset2 not EOF
do some calcs and stuff
maybe insert or delete some data
next record in recordset2
close recordset2
next record in recordset1
etc...
I've had a very similiar situation where the recordsets where nested 4 levels deep! The timeout value had to be set to 30 mins before the web page was displayed.
After I kindly pointed out the advantages of stored procedures and using a set based approach to the developer, with a brick to the head
, things returned to some resemblance of normality.Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Whilst you are experiencing the 100% datablocking try running sp_who, then running DBCC INPUTBUFFER(x) where x is one of the SPID's returned by sp_who.
-
We've already headed down these routes and are still stumped.
The performance monitors are showing that there is a problem in the fact that both CPUs are at 100% and the memory is paging heavily.
The enterprise manager becomes sluggish, even on the server console everything is slowing down.
We've run the profiler against the databases and have monitored the impact on the throughput of data, expecting to see queries taking longer to run and return data, but we're not ??? , the throughput of data and the time to run the queries remains within acceptable limits.
With regards to the sp_cursoropen, we've checked the pages and they don't seem to be recordset intensive, only one query to get one record of data, suitably indexed from a table with maybe a couple of hundred records and the profiler has confirmed that only one record is being returned, but for some reason, every now and again, it fails to complete and starts to devour the processor and memory. This page will happily run exactly the same query at other times ?!?!
We have no blocks in the data with one process waiting upon another, and we've also started to see the slow down occur when the sp_cursoropen is not present and the suspect website is down or not in use.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion. -
Could this be the 'infinite loop' problem inside the procedure. Opening a cursor but not fetching the next record might not impact performance of other queries too heavily, but it certainly will run your CPU to 100%...
Just try compiling and running this in C :
void main void{int i=0;for(;;)i=i;} -
I've seen problems similar if they are running on the same server, but not on different ones. Had to do with explicit destruction of the connection in ASP. Running the IIS in shared memory (for scripts) kept the connection open after the object went out of scope. Simple changes to ASP fixed it.
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC. -
Recent performance changes to the negative can sometimes be teh result of out of date indexes and/or statistics.
Try updating both and see what happens.
Laura
-
Many thanks to NPeeters who set me on the right track. This is one of our problems. We finally tracked down a faulty stored procedure which could lock itself in a WHILE loop reading the same record over and over again.
One question though just to see if I may be on the right track with this. One of the regular tasks relies on a stored procedure summoning a DTS package. When this task kicks off, we are seeing a huge prolonged spike as the paging file is being hit. The DTS is responsible for processing data and outputting it as a csv file across the network. Is it possible that as SQL is running dedicated on the server, when the DTS kicks in, it is competing for memory forcing the server to start paging? If so, would adding memory and throttling it back to leave memory free for the OS resolve the issue? Or am I baking up completely the wrong forest?
PS Many thanks for all the replies to this problem so far - you are all wonderful people
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
, things returned to some resemblance of normality.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply