5

My SQL Server databases are hosted on a highly spec'd converged platform with All Flash Storage Arrays, 40G network etc.

However, we're still unable to get more than 50MB/s Redo Rate - I need this to be in the GB/sec range.

The supporting platform can handle this - what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it? Even with a single-threaded process 50MB/s is poor - and totally useless given the amount of data we process.

Updates: When I'm on the server I can copy files between drives and get speeds > 1000MB/s. After reboots I see Redo Rates spike as high as 300MB/s, so I know its possible in my environment, however it quickly reduces to its norm of 50MB/s even when the Recovery queue is sitting at 400G. That takes 8 hours to drain - leading to very unhappy Clients...

closed as too broad by Max Vernon, Colin 't Hart, Tom V, hot2use, Marco May 15 '18 at 6:41

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • This is likely too broad to make a good question on Database Administrators, unless you can really narrow this down to a particular problem area. Put it this way, there is no "bottleneck" in SQL Server itself. – Max Vernon May 11 '18 at 19:18
  • Take the tour and check the help center for more details about what makes a good question. We'd need a much greater level of detail about the server, such as is the storage on a SAN, or is locally attached? How much RAM is in the machine. What is the page-life-expectancy. How many databases reside on the replica. Are both servers one the same LAN segment or are they thousands of miles apart? – Max Vernon May 11 '18 at 19:21
  • You've touched on one of the items: It's single-threaded. SQL Server 2016 introduced multithreaded redo on Availability Groups. Additionally, I recall from a session at the (2015?) PASS Summit in which there was a suboptimal encryption/compression setting in the AG transfer codebase. Those may contribute to what you're seeing.The panelist who mentioned this noted that it was fixed in SQL Server 2014 (it was a "What's New In SQL Server 2014 Availability Groups" session). – swasheck May 11 '18 at 19:24
  • 1
    What kind of speeds do you see when you copy a file across the same path? – Erik Darling May 11 '18 at 19:25
  • 1
    Also make sure no one decided to QoS your AG network. – Erik Darling May 11 '18 at 19:29
4

what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it?

That was a major work item in SQL 2016.

The result is a design for SQL Server 2016 that provides high availability for the most demanding workloads on the latest hardware with minimal impact and scalable for the future. Our design for SQL Server 2012 and SQL Server 2014 is still proven and meets the demands for many of our customers. However, if you are looking to accelerate your hardware, our Always On Availability Group design for SQL Server 2016 can keep pace.

See SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

Not the answer you're looking for? Browse other questions tagged or ask your own question.