[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Low PLE, but all other mem parameters are good.

Posted on 2009-12-31
Medium Priority
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
  • 3
  • 2
LVL 51

Expert Comment

by:Mark Wills
ID: 26156347
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

Mark Wills earned 2000 total points
ID: 26156565
*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 ? http://msdn.microsoft.com/en-us/library/dd206996.aspx  - 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" : http://msdn.microsoft.com/en-us/library/ms176018(SQL.90).aspx and http://msdn.microsoft.com/en-us/library/ms178067.aspx and because you are running AWE, also check : http://msdn.microsoft.com/en-us/library/ms190673(SQL.90).aspx

And then there is the SQL 2005 performance "must read" : http://technet.microsoft.com/en-us/library/cc966540.aspx

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

Author Comment

ID: 26174531
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..


Author Comment

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

thanks so much.
LVL 51

Expert Comment

by:Mark Wills
ID: 26299405
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard 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.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

834 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