Processor Performance issue. could it be virus

  • 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

  • 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.

  • 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.

  • Any errors in SQL Server errorlog? Check serevr's application and system log too.

  • spid 124 Mail Session Started

    Spid 124 Error: 1073759778 Severity 1

  • Anything new implemented on the server last Thursday/Friday? Or, any config change at all?

    Cheers,

    - Mark


    Cheers,
    - Mark

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • 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.

  • 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. 🙂

  • Well done. Did you use Profiler to track it down?

    Cheers,

    - Mark


    Cheers,
    - Mark

  • 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.

  • 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

Viewing 12 posts - 1 through 11 (of 11 total)

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