PLE and plan cache hit ratio are low

Last Post 22 Oct 2011 07:16 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Clint Spann
New Member
New Member

--
20 Oct 2011 08:21 AM
First of all, I'm a new member, and this is my first post, so good to be here ...

We have a SQL Server (2008 Standard 64 bit on Windows 2008 R2) that is kind of a data warehouse - let's call it SQLReport. It contains replicated data from an OLTP environment. That data has SSRS reports run off of it, and it also is used to build cubes, which exist on a different server.

There are two issues I'm concerned with:

A) SQLReport consistently has a 60-70% plan cache hit ratio. From my understanding, this is fairly common on a data warehouse, since a lot of the queries run are adhoc and/or being built by reports with many different parameters, so plans aren't reused as much. However, I wonder if it also has something to do with my next issue...

B) The Page Life Expectancy (PLE) is regularly low (i.e. below 100-200). I've only ever seen low PLE being caused by poor buffer caching (i.e. not enough memory allotted to SQL), so the buffer cache is flushed regularly. However, this machine has 24576 MB of RAM and SQL's max memory is set to 21500 MB, and SQL is only using around 7 GB! Therefore, it seems to me that SQL doesn't need more memory - if it did, it would grab more than 7 GB. However, since PLE is consistently low, it looks like the buffer cache is getting flushed?!

There are also SSIS packages running on this server, but only 4, and they run on a schedule. There are other system processes, but not anything major. I tried to add up all the memory used by all processes, and unless my calculations are wrong, it doesn't even come close to the amount available on the machine. Also, I haven't been able to determine that the PLE drop corresponds with any particular event...

Any help/thoughts/ideas would be greatly appreciated. Thanks!

gunneyk
New Member
New Member

--
20 Oct 2011 10:54 AM
If the queriesw are adhoc in nature the plan cache hit ratio will generally be low. If the queries are close in structure but only differ in the values of the SARG's you might try changing the parameterization option of that database to Forced and see if that helps any. Also I would recommend turning on the "Optimize for Adhoc Workloads" option of the SQL Instance. As for the memory that does seem strange. Do I read that correct in that you have ~24GB of memory in total? I would double check to ensure the max memory setting is correct. It's easy to miss a digit. How large are the dbs on that server and have you queried more than 7GB of data since the last restart? Do you have LOCK PAGES in MEMORY set for the account that SQL Server is running under? I think you need SP1 of 2008 for it to work with Std edition. One last ? Does the PLE just drop down to 200 or does it stay close to it all the time?
Clint Spann
New Member
New Member

--
21 Oct 2011 03:11 PM
Thanks for the responses. To answer your questions, PLE would stay between 10 to 1000, but hovered mostly around 200.  Yes, you read correct that we have 24GB of memory on the server, and SQL is given roughly 21GB of that.

We don't have ADHoc optimization turned on, but after researching, I believe it will help with our low cahce hit ration problem.

Also, what I didn't mention is that we are an entirely virtualized shop, using VMWare with an EMC SAN backend. With that said, the problem actually ended up being that even though we allotted the memory to the server through VMWare, Windows wasn't "seeing it" properly. Windows showed that 24GB was installed, but it also showed 98% usage, which was totally wrong. We rebooted Windows, then one of our infrastructure gurus worked some VMWare magic, and now the server is happy. Current PLE is above 15k.

Thanks all!
gunneyk
New Member
New Member

--
22 Oct 2011 07:16 AM
Good old VMWare . Glad to hear you got it fixed.
You are not authorized to post a reply.

Acceptable Use Policy