SQL Server Performance

Serious performance problem on SQL Server

Discussion in 'Performance Tuning for Hardware Configurations' started by Akthar, Jan 11, 2006.

  1. Akthar New Member

    Hi all,<br /><br />i am running an SQL Server 2000 Sp3a on Windows 2003 Server Sp1 with 2gb of RAM.<br />The server everyday uses around 1gb of RAM for all the queries and processes and the next day timeout for the same queriers.<br /><br /><br />Anybody can give me a clue.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br /><br /><br /><br />AKTHAR
  2. dineshasanka Moderator

  3. Twan New Member

    HI ya,<br /><br />it sounds like there are locks taken out by some processes causing blocking.<br /><br />use sp_who2 or Enterprise manager at a time when there are problems to see if there are blocked processes<br /><br />also run sql profiler to see which statements may be causing performance slow downs<br /><br />Cheers<br />Twan<br /><br />PS could a moderator of the 2005 area please move this prost to the sql2000 area <img src='/community/emoticons/emotion-5.gif' alt=';-)' />
  4. FrankKalis Moderator

    Done. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  5. ghemant Moderator

    Hi,<br />and run dbcc bufferinput(spid) also to find out which procedure / t-sql is is causing blocking and run profiler trace also <br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  6. mmarovic Active Member

    Do you mean dbcc inputBuffer?
  7. joechang New Member

    if performance is degrading over time, i would look for events that would cause address space fragmentation, ie, calls to sp_cursor, sp_prepare, other extended stored procedures.
  8. Akthar New Member


    i have also notice that at some specific time my CPU usage goes 100% and remain there.
    i noticed that Sqlserver process is taking up the Cpu resource. But no specific process runs at this time.
    How can this be explain???

  9. satya Moderator

  10. ramkumar.mu New Member

    did you try performance counters? you can watch the performance in its peak hours. you need not add all the counters. you can go for the following counters and check for the average values...

    Counters to be added...

    SQL Server:Buffer Manager - Buffer cache hit ratio
    SQL Server:Memory Manager - Connection Memory
    Processor - %Processor Time
    Processor - Context Switches/sec
    PhysicalDisk - %Disk Read Time
    PhysicalDisk - %Disk Write Time
    Memory - Page Faults/sec
    Memory - Pages/sec

  11. joechang New Member

    % disk time is a totally worthless counter with regard to disk issue,
    follow the counter list i mention (in the qdpma section)
    memory page faults includes soft faults, which are not serious, so page/sec is the only one that is important of those two

    for the problem described, i would actually watch Process->SqlServr-> Virtual & Private Bytes

Share This Page