Low PLE, but all other mem parameters are good.

Posted on 2009-12-31
Last Modified: 2012-05-08
The SQL Server Ent 2005 Server is on a cluster in the Windows 2003 Ent OS environment. Memory is 8GB, with AWE enabled and memory locked, with a min of 0 and max of 6GB.

Most of the mem usage is for Adhoc(reporting). VM committed is 61MB. PLE is mostly in single or double digits when under load. When there is no load, it rises above 1000.

Other numbers from recent tests are:
700MB available
Page Faults/sec - 889
Memory\Pages/sec - 22
AWE lookup maps/sec - 230
AWE stolen maps/sec - 642
AWE unmap calls/sec - 14
AWE unmap pages/sec - 882
AWE write maps/sec - 11
Buffer cache hit ratio - 99.63
Cache Hit Ratio -82.4
Cache Object Counts -2993
Cache Objects in use-35
Cache Pages-52906
Checkpoint pages/sec - 4.1
Database pages - 695187
Free list stalls/sec - 0.11
Lazy writes/sec - 0.60
Page lookups/sec - 6000
Page reads/sec - 770
Page writes/sec - 61
Readahead pages/sec-732
Reserved pages - 1474
Stolen pages - 64789
Target pages - 771072
Total pages - 771072
Target Server memory = Total Server memory = 6168576
Connection Memory (KB)-7718
Optimizer Memory (KB)-1490
Granted Workspace Memory (KB) - 24321
Memory Grants Outstanding - 0.16
Memory Grants Pending - 0
Lock Blocks-227
Lock Blocks Allocated-24733
Process(_Total)\Working Set 1386200320

Do you see any other major concerns, other than PLE for memory.

Question by:anushahanna
    LVL 51

    Expert Comment

    by:Mark Wills
    Hi anushahanna,

    Would like to discuss but with New Years, might be a slight pause before any serious discussions... Back soon :)

    LVL 51

    Accepted Solution

    *laughing* Champagne and numbers dont seem to mix all that well :)  So, will try to give you a few things to get ready....

    Do you mean that the database is mainly used for reporting, or, just that any adhoc queries are (and the business use is primarily prepared - like SP's)
    If for reporting, what reporting tool ? How have the datasources been set up - straight queries or SP ?

    How big is the database ? What kind of recovery model - scheduled backups - maintenance jobs (like update statistics) - no autoshrink

    Have you checked for any missing indexes / checked index use ?

    Page faults and Stolen pages could be interesting... Though stolen pages is about 10% of target pages, and normally a high percent (say > 70%) is seen if there are internal memory pressures (which are considerably harder to much with).

    Any Errors being logged ?  - while you are there, double check the SQL Server error log for : "Address Windowing Extensions enabled."

    Are there multiple instances of SQL server on the box ? Is there a large number of databases within your Server ?

    Good to see enterprise versions :) What about boot.ini and sql startup parameters ? any /PAE switch ? any -g switch ?

    Have a quick read of "managing Memory" : and and because you are running AWE, also check :

    And then there is the SQL 2005 performance "must read" :

    OK, by the time you have digested the above, think we will be ready to chat some more...
    LVL 6

    Author Comment

    Hope you had a great NY beginning and celebrations..

    sorry today i had another major crisis.. hence the delay..

    The server, has only one instance, and is dedicated for SQL and has the live database, and also the reporting DB, which is a mirror of the live one, 126GB each; Full recovery for the live database. Every day, the database is backedup and restored into the reporting one, which is done through maintenance job. Every night, reindexing and statistics are done once a week which is a long job.

    Other than this, there are 2 small databases in the server, each less than 1 GB.

    The reporting server is where all the heavy reads are happening. The reporting happens through Business Objects with ODBC connectivity.

    we check for missing indexes and apply it weekly.

    the only msg related to AWE in logs is
    "Address Windowing Extensions is enabled. This is an informational message only; no user action is required."

    In the boot.ini, we have the /PAE switch. (no -g; what is it for?)

    thanks for the links. The 'must read' one has lot of good help on memory.

    I looked at the numbers again for today(this was run for 3 hours).. Even though PLE is still in single/double digits, others seem ok. what do you think?

    AvailableMem is 900MB,
    Page Faults/Sec 418
    Pages/Sec 15
    Page reads/sec 6299.96
    Readahead pages/sec 6280.69
    Page writes/sec 11.11
    AWE lookup maps/sec 2777
    AWE stolen maps/sec 5743
    AWE unmap calls/sec 155
    AWE unmap pages/sec 8411
    AWE write maps/sec 2.44
    Buffer cache hit ratio 99.61
    Plan Cache(_Total)\Cache Hit Ratio 73.33
    Checkpoint pages/sec .46
    Database pages 682992
    Free list stalls/sec .03
    Free Pages 2140
    Reserved pages 753
    Stolen pages 85940
    Lazy Writes/Sec 1.05
    PLE 28
    Page lookups/sec 13407
    Target pages=Total pages = 771072
    Target Server  Mem = Total Server Mem = 6168576
    Connection Memory (KB) 2648
    Granted Workspace Memory (KB) = 6545
    Optimizer Memory (KB) 990
    SQL Cache Memory (KB) = 12012
    Lock Blocks 304
    Lock Blocks Allocated 52800
    Memory Grants Outstanding 0.24
    Memory Grants Pending 0
    Cache Object Counts 4535
    Cache Objects in use 155
    Cache pages 75968
    Process(_Total)\Working Set 877610508
    Memory\Cache Bytes 223596544 (213MB)

    Here are some results from your prev queries..
    *[Non BufferPool Memory] 29MB
    *[BufferPool -with AWE] 6087MB
    *[BufferPool -Single Pages] 445MB
    *[BufferPool -Multi Pages] 21MB
    *VAS available memory in all free regions - 226MB
    *Average Available VAS since restart 281MB
    *Among cache counters, CACHESTORE_SQLCP has the most count (3300 entry counts with 115 in use) and holds a total of 350MB reserved, and only 4MB used. Nothing else comes close
    *virtual_memory_committed from BufferPool is 61MB
    *in the cached plans, 1900 are adhoc, 1500 are prepared, 200 are views, 60 are procs.

    if you see anything obvious, kindly share..

    LVL 6

    Author Comment

    If you get to this, I will follow up with you.

    thanks so much.
    LVL 51

    Expert Comment

    by:Mark Wills
    Sorry for not getting back sooner...

    By the sounds of it your reporting database is the likely culprit for clearing out pages.

    They are likely ad-hoc style calls to the database. Assuming it is Crystal Reports ? Or are you also running BI tools over the top as well ?

    You might be able to improve the situation by looking at the most often run reports and slightly redeveloping them to use Stored Procedures instead. The query plan is captured at the time of the Stored Procedures, you might also look at converting some of the queries to straight views (though that can largely depend on both the query, and any sub reporting within Crystal). That would also improve plan cache hot ratio (again likely due to the adhoc queries).

    What is always hard to tell with two databases is where the "cause and effect" is really happening - I am guessing that it became two to avoid lock contention and such like ? Which of course really doubles the size of the datasets being handled (depending on what you app is doing). Is there a real need to seperate the databases ? It is equally possible that the applications are generating ad hoc queries on the database as well - except it can be harder to do anything with Applications unless they are your own.

    If you ever get the chance, it would be worthwhile looking at the load without reporting database, or, move that reporting database off to another server...

    Reporting can be an ugly thing - for a start most accesses are largely sequential in nature via one index or another - and it really becomes a matter of looking at how data is being retrieved via the user request. Now if they have several parameters that they are free to choose from, and do choose freely, then it does get a lot more involved learning how the business is really accessing that data...

    We can chat some more...

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now