December 2, 2016 at 9:12 am
What are the best practices when we have more than 350+ databases on a single AlwaysOn Group. I see issues like:
1. When connecting the to the instance, it takes 30 seconds to 90 seconds.
2. CPU pegging during backups ??
3. any suggestions for maintenance job (re indexing / taking stats on week end)?
SQL Version: 2014
OS: Windows 2012 R2
I am interested in worker threads / Max DOP settings / any suggested process...
thanks
December 2, 2016 at 2:55 pm
nuth (12/2/2016)
What are the best practices when we have more than 350+ databases on a single AlwaysOn Group. I see issues like:1. When connecting the to the instance, it takes 30 seconds to 90 seconds.
2. CPU pegging during backups ??
3. any suggestions for maintenance job (re indexing / taking stats on week end)?
SQL Version: 2014
OS: Windows 2012 R2
I am interested in worker threads / Max DOP settings / any suggested process...
thanks
max worker threads are what will kill you with this scenario. If you do some research you will find that threads are set aside per AG database to handle tlog movement and replay.
Use sp_whoisactive and also differential fileIO and wait stats analysis to see why resources such as CPU or others are getting saturated and causing pain.
Maintenance often needs to be done on a revolving basis. Never forget that there are 60 seconds per minute, 60 minutes per hour and 24 hours per day. Schedule things around the clock as best you can to avoid pushing various resources past the "exponential breakover point" where things just fall off a cliff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply