Low PLE, but all other mem parameters are good.
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mark
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..
thanks
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..
thanks
ASKER
Mark
If you get to this, I will follow up with you.
thanks so much.
If you get to this, I will follow up with you.
thanks so much.
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...
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...
Would like to discuss but with New Years, might be a slight pause before any serious discussions... Back soon :)