We're experiencing performance degradation on one of our SQL Servers to the point where every couple of days it needs to be rebooted.
Environment: 4 processor pentium pro 200, NT 4.0 SP6a, SQL Server 7.0 SP3, 785MB RAM
SQL Server memory is DYNAMIC, allthough right now I have a MAX set to
650MB because I thought that might help the situation, but it acts the same
either way ( with or without the max setting ). Nothing else runs on the box
( except Norton Antivirus ).
I've monitored via Task Manager, and have also run some Performance Monitor log files with long intervals ( 5 to 10 minutes ) to try and capture anything that steadily increases or decreases. Here's a summary of what seem to be "key" counters:
Task Manager: Available memory goes below 4MB, even below 1MB --> Initially I felt that this indicated the box needed more memory so we bumped it from 256MB to 512MB and then to 785MB. It actually seems to have gotten worse since doing that, which makes no sense at all...in fact that's when I decided to set the MAX dynamic memory for SQL, which again, doesn't appear to have helped.
Performance Montior Counters: THe only "symptom" at this point that seems to stand out to me is that SQLServer:Memory Manger:Total Server memory seems to steadily DECREASE, and Process:sqlservr.exe:Thread Count seems to steadily INCREASE. BUT I DON'T KNOW IF THAT TELLS ME ANYTHING OR NOT? Does it? Does that indicate a memory leak associated with a THREAD, and if so, how can I isolate?
Other PerfMon counters:
-buffer cach hit ratio, and all cache hit ratios are consistently 95% or better
-SQLServer:Memory Manager:Total Server memory: initially climbs to 630, steadily decreases to last of 166
-Process:sqlservr.exe:Thread Count goes to 54 initially then steadily climbs to 194
rivate Bytes 393 min climbs to 730 stays about there ... 787 max, 740 avg.
ool Paged Bytes fluctuates in range of 46394 to 85540
ool NonPaged Bytes fluctuates in range or 4040392 to 4877532
-paging activity does seem to be up when the performance gets real bad...pages/sec for sqlservr.exe may be 30 to 80, and %use on the paging file
will be 20-30%, where as normally those values are both almost 0.