SQL Server Performance Forum – Threads Archive
Serious performance problem on SQL Server
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
First question, Why you have nto installed SP4. were there any issues —————————————-
http://spaces.msn.com/members/dineshasanka
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=’;-)’ />
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 />
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 />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
Do you mean dbcc inputBuffer?
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.
guys, 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??? AKTHAR
http://www.sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
http://support.microsoft.com/kb/835864 too for your joy. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
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
Thanks,
Ram
% 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
]]>