Buffer cache & paging problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Buffer cache & paging problem

Good morning all, We are running on a W2k SP4 server with 2 CPU Xeon 2.4Ghz, 3GB memory.
SQL server 2000 SP3a DISK 1 (raid 1+0)
– partition C: W2k SP4
– Partition E: Pagefile 4Gb (only the page file on the drive) DISK 2 (raid 1+0)
– partition D: SQL server 2000, transaction log, 2 SQL DB (6GB & 1.5Gb). the SQL server is configured to use (dynamic) a minimum of 800Mb memory and a maximum of 1.5Gb The server have problem with paging and buffer cache
during the maximum use of the server (14 computer connected): Paging : 134 pages/sec.SQL server processing 78 login/sec.
SQL server is escalating locks to table level at the rate of 0.13/sec.
buffer cache 696Mb active 65% with hit rates 84%
Procedure cache : 444Mb active at 52% with hit rates at 92.7% What should we do to have better perfs ?
the day before the serve have the same performance with 2gb memory. we have added 1GB and the perf are the same. Thanks, Olivier Schils
IS Specialist Operator
Few links to study and investigate:
http://support.microsoft.com/kb/835864 HTH Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
SQL Server should not be paging, are there other applications that use memory,
what is system free memory, track down the source of paging no way should you be at 78 logins/sec, look into connection pooling use stored procedures correctly, you are wasting 444M on procedure cache, basically,
i take it you compile rate is high, memory is not affecting performance probably because your cpu is spent in logins & compiles
Thanks for your Answer, the CPU is very quite… 30 % used
Memory usage : 1.8Gb / 3Gb (1.5Gb for SQL). I don’t understang why we have paging. Joe :what do you mean by "look into connection pooling" Olivier Schils
IS Specialist Operator
the client application opens several connections to SQL Server and keeps them open,
when the client makes SQL calls, it uses the already open connection, rather than making new logins. on pages, try Perfmon, Process, Page Faults/sec for All instances, see which ones are generating faults,
but this includes both soft and hard, and we are just looking for the hard faults. try also the IO Data Operations /sec
for the page activity: Hard fault is 14/sec
Soft Faults /sec: average is 500 an max is 7000 (at this time the activity of the server is low) I have also 154 connected sessions idle. Olivier Schils
IS Specialist Operator
you only care about hard faults, on a properly configured dedicated SQL Server system, it really should be zero, except during maintenance ops,<br /><br />anyways, your more serious problems are the high SQL Logins/sec – 78<br />and i am guessing you do not use stored procedures, hence the very large procedure cache, which also waste cpu cycles on compiles<br /><br />continue monitoring Logins/sec, proc cache size<br /><br />start monitoring SQLServer<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics-&gt;<br /> Batch Requeest/sec<br /> SQL Compilations/sec<br /> SQL Re-Compilations/sec<br /><br />get your client app people to start fixing their code.<br /><br />you could also increase max sql server memory to 2GB, if this is a dedicated SQL Server
I find something new:<br /><br />The procedure cache memory is growing.<br />after a start of the server it take 12Mb<br />after 1 day work it take 800Mb.<br />If i remove all inactive Procedure Cache entries from SQL server.<br />the memory for procedure cache go back to 10Mb.<br /><br />Whath does it mean ?<br />Is it normal that proc cache growing like the (for inactive proc) ?<br /><br />The supplier of SQL server (app) and client-server applications say we have problem with our hardware or w2k server environment (but I think it’s not true).<br /><br />Thanks for your help Joe <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><br /><br /><br /><br />Olivier Schils<br />IS Specialist Operator<br />TNT
this is why i said your problem was from not using stored procs,
you end up with a huge procedure cache of entries that will not get used again (unless the user clicks the same action twice) without a more detailed analysis, you do not have definitive proof,
but if the vendor cannot provide a precise explanation of what in the HW or OS is the problem and how it can be fixed, chances are they have no clue, and just want to point the finger elsewhere, but if an ISV sold an app that generates 78 logins/sec, i would ditch this app as soon as i can,
it really is not difficult to build an app from the ground up with VS .NET these days,
enough so that this warrants serious consideration over the app that was so badly designed, and from an uncooperative vendor