SQL Server Performance

Buffer cache & paging problem

Discussion in 'Performance Tuning for Hardware Configurations' started by olivierschils, Mar 2, 2006.

  1. olivierschils New Member

    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
    TNT
  2. satya Moderator

  3. joechang New Member

    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
  4. olivierschils New Member

    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
    TNT
  5. joechang New Member

    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
  6. olivierschils New Member

    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
    TNT
  7. joechang New Member

    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
  8. olivierschils New Member

    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
  9. joechang New Member

    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

Share This Page