March 14, 2012 at 2:39 am
Hi,
We have 2 sql server machines in an automatic failover configuration. (With 3rd witness).
After a week or so everything gets slower.
We fail it over to the mirror and instantly it is fast again for another week.
I suspect that maybe it takes a week for the cache to fill up and then the performance takes a hit.
My question is:
Is it ok to run DBCC DROPCLEANBUFFERS on the principle?
Or will it some how crash the mirror group/ruin the database/cause everything to stop working etc?!
This is a production setup and I don't have a test setup to try it on. I could set one up but if someone has experience of clearing the cashe on a mirrored setup It would be good to know. I'm sure it wouldn't cause any problems however would like a second opinion on this before trying it.
If it works then we can schedule the command for once a week which would resolve this issue.
Many Thanks
Simon
Edit: It's sql 2005 x64 btw.
March 14, 2012 at 2:45 am
simon4132-806507 (3/14/2012)
Is it ok to run DBCC DROPCLEANBUFFERS on the principle?
No, it's recommended that not be run on production
Or will it some how crash the mirror group/ruin the database/cause everything to stop working etc?!
It won't crash anything, but it will cause things to slow down while SQL repopulates the data cache. Cached data = good. Cached data means that SQL doesn't have to wait for the slower disks to return data.
If it works then we can schedule the command for once a week which would resolve this issue.
Have you 100%, absolutely, definitely identified the size of the data cache as the problem, or are you guessing that it's the data cache (failing over doesn't clear the data cache, the mirror tries to keep data in cache to support a failover)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2012 at 3:39 am
Hi,
The cache must empty when failover occurs as queries take ages to run for a couple of minutes until the cache is repopulated.
Seeing as the cache is mostly in memory, I can't see how that would be transferred to the mirror anyway.
If you have any other ideas as to why failing over resolves the issue then It would be great to hear them.
Of course it would be fantastic to really iron out what the problem is. I would think the 'lock pages in memory' is high on the list, but we can't risk disruption of the production system to upgrade on the offchance that it will improve.
(From past experience, upgrades can be bad as well as good).
Thanks
Simon
March 14, 2012 at 6:40 am
simon4132-806507 (3/14/2012)
The cache must empty when failover occurs as queries take ages to run for a couple of minutes until the cache is repopulated.
The data cache shouldn't be completely empty. The plan cache likely will be. Still, if it takes only a few minutes until the cache is repopulated then, if the problem was related to a full cache, I'd expect the problems to reoccur within a few minutes
Seeing as the cache is mostly in memory, I can't see how that would be transferred to the mirror anyway.
It's not transferred, the data cache on the mirror will be populated as SQL replays the copied transactions. It won't be identical to the principal (selects aren't mirrored so any data pages that are only read but never changed won't be in the data cache on the mirror)
If you have any other ideas as to why failing over resolves the issue then It would be great to hear them.
Without a lot more details on the problem, not really. If I was at the server I'd look at wait stats (overall and per query) when the server is slow, memory and disk perfmon counters comparing when it's fast vs when it's slow, execution plans when things are fast vs when they're slow, etc.
What's the exact version of SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2012 at 4:16 am
Please let us know the backup strategy for your primary mirror database. It seems like your transaction log keep growing each day. I am not very sure what will be the impact if there you do not have transaction log backup policy in mirroring.
--- babu
May 24, 2012 at 1:30 am
Hi,
I think I have found the problem but need further advice.
After a lot of research I came to the conclusion that because all the queries are ad-hoc queries, it is likely that the proc cache is being filled up with non-resuseable plans. When failing over to the mirror the proc cache is flushed but not the buffer cache.
I ran dbcc freeproccache and the database immediately sped up. FYI the db is about 40gb and the command took about 3 seconds to run.
I believe the permanant solution should be to set forced paramaterization.
With this in mind I have an important question.
If I set forced paramaterization in the GUI on the master database how will it affect mirroring?
Will the 'forced paramaterization' setting be automatically set on the mirror?
Will the 'forced paramaterization' setting need to be manually set on the mirror?
Or - worse - will the mirroring fail after setting this as it will detect that the setting is different on both machines? (I do not have a test mirror to check this).
Thanks for any advice on this,
Simon
p.s. answer to previous post - we have full db backup and hourly translog backup. I do not believe this to be causing any problems.
May 24, 2012 at 2:34 am
I honestly wouldn't recommend turning forced parameterisation on without a lot of analysis of the workload to see if it causes adverse effects. Optimise for ad-hoc workloads is safe and I've never seen anyone mention bad outcome, but forced parameterisation can degrade the performance of your workload through reuse of sub-optimal plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2012 at 2:38 am
Hi,
Thanks for your fast reply and I understand your response.
I have 2 problems with this...
1. It is Sql 2005 which does not have the 'optimize for ad-hoc workloads'
2. Our production server has about 1000 ad-hoc queries a second going through it and takes a week to slow down. It is impossible to re-create this in a test environment accurately. I'm not sure how we can test the setting without switching it on.
The only other option is to schedule dbcc freeproccache every week, but i'm sure you will agree that that is not the answer.
Thoughts?
Thanks
Simon
May 24, 2012 at 2:44 am
It's not the slowdown of the mirroring that you need to test, it's that your workload does not suffer a degradation in performance due to the forced paramerisation.
I would honestly look at changing the app in the long term to fix the ad-hoc queries issue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2012 at 3:00 am
Hi,
I take the point, however we are currently experiencing a slowdown at around a week, which if we do not intervene will grind to a halt.
It really can't be much worse than this.
If it doesn't work out we can always switch it back.
If it does work out, we will hopefully not need to worry about this again.
We cannot simply re-write the application which has millions of lines of code and sql. It would be much better to change the database in such a way so that it works properly. (Maybe upgrading to 2008 or higher but that will likely introduce other issues as nothing is ever that simple!)
That aside - Do you know the answer to my earlier question? i.e. how it would affect the 2 machines settings/mirroring.
Thanks
Simon
May 24, 2012 at 3:27 am
It can be worse. It can cause erratic or terrible performance of some queries all the time. Hence the reason I cannot recommend that you use that without doing testing first.
It's a database property and like any database property if it's changed the change will be logged and hence transfered to any mirror or log shipping secondary.
First check that you are at least on SP2 of SQL 2005, preferably SP4 as there were changes to the plan cache size rules in SP2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2012 at 4:18 am
Hi,
Thanks for the reply.
We are on sp2 + a few hot fixes.
Presumably it must work for some people, otherwise what would be the point of having the option? I have read many good reviews of the option and lots of cautionary notes, but I can't find anything where it has not worked out - (maybe I havn't looked hard enough or maybe people don't post that!)
Either way - it is impossible to test.
The only way to test this would be to fire every possible permutation of each query at the database in every possible order to see if a 'bad' plan - if there is one for the query gets cached before a good plan - therefore affecting all queries using that plan. That is simply not possible. Even if this was done, it might never happen in that order in the real world.
Another option would be to write something which monitors db performance and clears the cache if it is slow. Not a great solution either.
With regard to the alter database command, i thought that mirroring was set up using 'alter database' which has to be used differently on both databases so presumed they don't copy accross. I can't remember totally though so maybe i'm wrong.
Many Thanks
Simon
May 24, 2012 at 4:20 am
simon4132-806507 (5/24/2012)
With regard to the alter database command, i thought that mirroring was set up using 'alter database' which has to be used differently on both databases so presumed they don't copy accross.
Mirroring is set up using alter database statements, but that has no effect on other database alterations which, as they are logged operations, are transferred to mirror or log shipping secondary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply