October 28, 2014 at 5:07 am
Hi ,
In past we have SQL Server 2008 R2 stand alone server which is running very fast and no performance issues. Currently we installed SQL Server 2012 SP1 with alwayson (synchronous mode). now performance was dropped comparing to previous version.
Can any one let me know where i can do change and improve performance in 2012.
Thanks,
Venki
October 28, 2014 at 7:05 am
There are so many variables to this that it's hard to know where to start.
Different server hardware? Differences in Max Degree of Parallelism/Cost Threshold for Parallelism? Are your statistics being updated properly?
October 28, 2014 at 7:06 am
If it's a synchronous replica, there's always the latency between the nodes too.
October 28, 2014 at 7:30 am
I agree, the primary factor here (given the limited information we have) is AGs in synchronous. If you don't require automatic failover or zero potential data loss I would evaluate whether asynchronous would be "good enough". Or make certain that you have absolutely minimal latency between your two nodes, because in synchronous everything has to be written to secondary before being fully committed on primary, like synchronous mirroring. Not something to be doing across subnets, etc.
October 28, 2014 at 7:41 am
While it certainly could be AG, and that's really the only evidence we have, I'd suggest capturing a bunch of metrics in order to understand where the problem lies rather than starting to poke at the first thing that comes to mind. Tune through knowledge, not guesses. Get the wait statistics for the server in order to understand what's causing it to slow down. Gather query metrics in order to understand how your queries are behaving (there are a lot of updates in the optimizer between 2008 & 2012, you could be hitting a regression). Understand what is running slow and why it's running slow before you attempt to fix a general concept of "slow."
"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 28, 2014 at 7:59 am
1) use sp_whoisactive to detect right-now activity and metrics
2) use differential wait stats for say 3 minutes to detect waits that occur during that time
3) use differential file IO stalls for say 3 minutes to detect IO stalls that occur during that time
One or more of those will point you to the issue(s) you are having. As other's have said, there are a BAJILLION things that could be at play here. Initial leading candidate is synchronous mirroring (AG). That is easy to observe (and easy to fix if you can improve the bottleneck, which is almost always IO/RAM on secondary or perhaps network latency/throughput if not in same data center).
If you need scripts to do 2 and 3 above reply here. I have a Tune Like a Guru session I have done at a number of SQL Saturday events I can point you to. sp_whoisactive can be downloaded from sqlblog.com. v11.11 is latest AFAIK.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply