October 20, 2011 at 9:09 am
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!
October 20, 2011 at 11:25 am
If it's not turned on already, turn on optimizer for ad hoc workload.
Next, news to me, but worth mentioning, NUMA (which most systems have these days) makes the normal page life expectancy counter useless. You need to look at the Nodes page life expectancy to get a better idea if there are issues. Also, if your page life expectancy is constant, if low, it might not be an issue. But the fact that you have a low buffer cache hit rate suggests that everything is going to the disk, a lot. I wouldn't worry so much about memory as I would, what are your queries doing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2011 at 11:28 am
Sounds a little odd.
Have you traced the memory usage over time to see if it changes?
Do you have any logships in addition to replication?
How frequently does the replicated data change? (what type of replication?)
A punt could be to search sys.sql_modules for DBCC DROPCLEANBUFFERS too. I've know reporting developers use this and it could get left behind in a proc.
October 21, 2011 at 2:38 pm
Thanks for the responses. First, to answer both your questions:
1) We do not have the AdHoc optimization turned on, and actually, I'd forgotten about it. I've been researching, and I believe this will solve the low plan cache hit problem - a large percentage of our cached plans are only used once.
My only question is, does it have to be classified as an AdHoc cache type to benefit, or will other types (i.e. PROC, etc.) benefit from this if they are only used once also?
2) We are on a VMWare environment with an ESX SAN on the back-end. The SAN has NUMA enabled, but it is not being utilized by this particular server.
3) Memory usage did not seem to change much at all over the past week or so, based on the monitoring we have in place.
4) No log shipping is being done
5) This server serves as a subscriber for one transactional replication, which involves data being mostly being changed, but added to often also. We add about 1GB per day, and probably have 2-3GB of changes. This server also serves as a distributor for 4 other publications (Snapshot, merge, and transactional
So, with all 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!
October 22, 2011 at 5:25 am
Clint-525719 (10/21/2011)
Thanks for the responses. First, to answer both your questions:1) We do not have the AdHoc optimization turned on, and actually, I'd forgotten about it. I've been researching, and I believe this will solve the low plan cache hit problem - a large percentage of our cached plans are only used once.
My only question is, does it have to be classified as an AdHoc cache type to benefit, or will other types (i.e. PROC, etc.) benefit from this if they are only used once also?
2) We are on a VMWare environment with an ESX SAN on the back-end. The SAN has NUMA enabled, but it is not being utilized by this particular server.
3) Memory usage did not seem to change much at all over the past week or so, based on the monitoring we have in place.
4) No log shipping is being done
5) This server serves as a subscriber for one transactional replication, which involves data being mostly being changed, but added to often also. We add about 1GB per day, and probably have 2-3GB of changes. This server also serves as a distributor for 4 other publications (Snapshot, merge, and transactional
So, with all 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!
I used to think it helped procs too, but it's only for ad hoc queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply