August 21, 2003 at 2:21 pm
running sql2k sp2. 2gb ram
dedicated box; only one db with 1gb size
2 processors almost 100%processor time.
min serv memoroy 512
max serv memory 1.7gb
%Disk Time almost 0
Cache hit ratio 99%
memory available 40mb
min query memory 2mb
normal select,update, insert queries
Sudden spike from last friday. Every day night rebooting the box; looks ok for an hour and cpu spikes up to 95 to 100.
sql buffer manager:
stolen pages, total pages, free pages high.
What else to monitor? PLESE HELP
August 21, 2003 at 2:32 pm
From task manager --> processes to see which processes are eating the CPUs. If it is SQL Server process, You can run dbcc dbreindex and statistics update to your database to see any improvement. If not, you need to run Profiler to trace which stored procedures, queries are consuming the CPUs. Once indetify those queries, verify the execution plan to see whether they are using the correct indexes.
August 21, 2003 at 2:51 pm
I do dbcc reindex regularly. Even I did dbcc checkdb came out 0 errors. In process, only sqlserver is eating up the memory and cpu. We did not add anything new; but from friday it is terrible. Ran profiler, to see usual queries coming. What is stolen pages?
I suspect, sqlserver is not releasing the memory.
August 21, 2003 at 2:58 pm
Any errors in SQL Server errorlog? Check serevr's application and system log too.
August 21, 2003 at 3:32 pm
spid 124 Mail Session Started
Spid 124 Error: 1073759778 Severity 1
August 21, 2003 at 4:49 pm
Anything new implemented on the server last Thursday/Friday? Or, any config change at all?
Cheers,
- Mark
Cheers,
- Mark
August 21, 2003 at 5:19 pm
Think that is a nothing error. If you can stop the sql service for a while, that should let you find out if you have a virus since it would still spike the processor activity level.
Andy
August 22, 2003 at 4:49 pm
Nothing new installed. We put patches for virus. Page file was defined for 4gb in C&E drive. I had to reboot and cleanup and make room for it. dbcc sqlperf showed that transaction log for master,model & msdb are high than allocated. so, I increased them; still performance hit. Customer logs in thru Jrun servers and places orders. About 80 user connections; The queries are bad; but they were bad forever. Could not answer this sudden spike. Planning to move to another database to see its performance.
August 25, 2003 at 12:02 pm
Thank you all for your suggestions.
We found out, why cpu was 100%. It was nothing wrong with the sql server. From the java frontend application, one process was going in an infinite loop, with a select statement which does not bring anything.
Stupid Development team was blaming me; and we were blaming them for poor code, for months. I am glad that they are wrong. 🙂
August 25, 2003 at 2:51 pm
Well done. Did you use Profiler to track it down?
Cheers,
- Mark
Cheers,
- Mark
August 25, 2003 at 4:18 pm
Yes. Profiler and Perfmon side by side.
We had to stop the client from placing orders. Log in with a single user from the front end. Tracked all sql; as soon as they click on some link, it goes in an infinite loop of select statement, as it could not find a specific result from database. All these days, they have been blaming us for this, saying it is a old machine.
August 26, 2003 at 8:27 am
Haven't you seen the Programmer's Drinking Song?
quote:
100 little bugs in the code,100 bugs in the code,
fix one bug, compile it again,
101 little bugs in the code.
101 little bugs in the code.....
Repeat until BUGS = 0
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply