October 7, 2010 at 3:02 am
Hi,
I have always monitored our server and looked at performance stats. The page life has always been around 300 and sometimes going below this as we have restrictions on our hardware.
I have recently set up mirroring from this server to another server at another location. Since setting this up the PLE has flat lined and so has the Buffer Cache hit ratio!
I monitor the Sprocs via the profiler and non take excessive times to run.
Indexes are reorganised every night on the server - We have SQL 2005 std edition.
Any ideas or thoughts would be appreciated.
Thanks
October 7, 2010 at 4:05 am
I suspect this is due to memory pressure, but I find it hard to relate to db mirroring.
Have you checked your memory usage stats?
-- Gianluca Sartori
October 7, 2010 at 4:09 am
what was the PLE before the mirroring was setup?
October 7, 2010 at 4:43 am
Hi,
The PLE was never flat lining. Although it was low around 50-80 at times.
October 7, 2010 at 4:51 am
We do have trouble with the memory we have. It's only 4GB but we have at least 1GB available on the monitor. I don't think it will be down to memory though.
The PLE was giving at least a value all the time we were running the server prior to stopping it and setting up mirroring. Nothing else has changed on the system apart from this mirror being set up. The system is coping as normal (just).
Are there any specific memory stats that you would advise to look into?
Thanks for your reply.
October 7, 2010 at 5:42 am
markganley (10/7/2010)
The PLE was giving at least a value all the time we were running the server prior to stopping it and setting up mirroring. Nothing else has changed on the system apart from this mirror being set up.
What are the chances of confirming your suspicions by temporarily breaking the mirroring and see if it gets back to what it used to be?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 7, 2010 at 5:44 am
markganley (10/7/2010)
The system is coping as normal.
If the system is performing well meaning, as it used to do - I wouldn't be really worried about the metric. I'll probably open a ticket with Microsoft to check if they have an explanation; after all this is the beauty of Microsoft - they support both the O/S and the RDBMS 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 7, 2010 at 5:46 am
markganley (10/7/2010)
Hi,The PLE was never flat lining. Although it was low around 50-80 at times.
Yeah, that looks like memory pressure... You release that the PLE counter will grow until the SQL cache is cleared and then the couter is reset... so a value 50-80 is not exactly good! Ideally this counter should be constantly monitored to see how many times the cached is dumped in a day.
Are you using 32bit or 64bit?
Have you got an upper memory limit set within SQL Server?
October 7, 2010 at 5:50 am
Hi,
We are using 32-bit non AWE. The server has 4GB memory. It's mirror is the same spec with 32-Bit SQL server.
October 7, 2010 at 5:54 am
That's something I have in mind. Unfortunately we are a small business and the location of the mirror is somewhat 50 miles away in unmanned offices. I can't risk doing this unless somebody is on site when I drop mirroring. I must ensure that the mirroring is restored as our operations run 24 hours a day 365 days a year. Even rebooting the server via RDP can sometimes cause a problem.
October 7, 2010 at 6:01 am
another thought - have you done a profiler trace alongside your perfmon stats collection? Thats probably what Microsoft support would ask you to do anyway.. this smells like the footprint of a large table scan (or otherwise very large query) that comes along and blows away the buffer cache. Is there any evidence of that that you can find? Could be mirroring is a decoy.
October 7, 2010 at 6:11 am
Thanks. Will look into this.
October 7, 2010 at 6:16 am
Hello,
I have carried out continuous profiler traces. Nothing is exceeding 10 seconds. Everything is executing as should be and there are no hangups. I may try pausing/stopping the mirroring when I can do so.
October 7, 2010 at 6:26 am
markganley (10/7/2010)
Hello,I have carried out continuous profiler traces. Nothing is exceeding 10 seconds. Everything is executing as should be and there are no hangups. I may try pausing/stopping the mirroring when I can do so.
I don't know if I'd pay as much attention to the duration of the queries as the amout of IO it performs. But, its likely that if there were such a large query as I suspected, it probably wouldn't execute in longer than 10 seconds... Unless you had a batch of many small queries that caused the same blow-away-the-cache problem, but in small queries. That's why I'd pay more attention to how much IO queries are using around the time of the flat-lining.
October 7, 2010 at 6:40 am
Regularly running at 50-80ms for page life isn't good. I'd look to see if you're getting good plan reuse, are you getting lots of recompiles? I'd also take a look at the most frequently called procedures and the longest running procs and see what the execution plans look like. Are you getting lots of scans, lots of temp tables or work tables? All of that can lead to excessive memory use. Also, if you're just moving lots of data around all the time, you may not be able to keep things in memory.
I tend to doubt it's mirroring at the root, but that could be adding to the issue.
"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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply