locked
Maximum Number of Mirrored Databases Decreases as Number of Available Cores Increases? RRS feed

  • Question

  • Hi,

    I wonder if you can help me. We're planning a large in-house mirroring deployment and are concerned we may (at some point) run into the limits described in the following KB article: http://support.microsoft.com/kb/2001270/en-gb.

    Based on the formulas contained within the above article, I knocked up a quick spreadsheet to see how increasing the number of cores might allow us to increase the number of mirrored databases. However, to my surprise, my calculations imply that as you increase the number of cores, the number of databases you can potentially mirror decreases?

    No. of Logical Cores Max Worker Threads Threads Required per Mirrored Database Max. No. of Databases that can be Mirrored
    4 512 3 170.6666667
    8 576 4 144
    12 640 5 128
    16 704 6 117.3333333
    20 768 7 109.7142857
    24 832 8 104
    28 896 9 99.55555556
    32 960 10 96
    64 1472 18 81.77777778

    Am I missing something here? Have a miscalculated somewhere? This seems counterintuitive.

    Regards

    Andy

    Thursday, August 28, 2014 2:20 PM

Answers

  • Hi Andy,

    I've just done the calculation according to the given KB article, and it seems you are bloody right. I get the same results as yours in your table.

    However, this is theory only. Practically I have a few customers where we needed to increase the number of worker threads manually (not due mirroring) and they have not had any problems since. Also this calculation applies to the mirror server and maybe increasing the number of worker threads can increase the theoretical limits. But other factors such as network bandwidth and IO should not be forgotten as they can even lower the limits.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by rushandy Monday, September 1, 2014 10:30 AM
    Monday, September 1, 2014 10:04 AM

All replies

  • Hi  rushandy,

    As the KB post, we need to calculate the max worker threads, which is default no. of worker thread SQL Server will spawn. about database mirroring requirements for worker threads, we need to calculate the number of threads required per mirrored database. At end,

    maximum number of databases that can be mirrored = the max worker threads/ the number of threads required per mirrored database.

    As your post, for 64 bit architectures, if the number of logical CPU cores is 16 on the mirror server, after calculating , maximum number of databases that can be mirrored is 117. However, in actual scenario, the mirroring databases is less than 117, or else, it will cause some errors, such as error 17884 and so on.

    Usually, not all worker threads would be available to perform mirroring activities as some threads are assigned to core database activity on the server. For more information, seehttp://sqlknowledgeshare.blogspot.com/2012/02/do-you-know-maximum-number-of-databases.html

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Friday, August 29, 2014 9:07 AM
  • In addition to what Sofiya has posted:

    It is possibly to manually set the max worker threads higher, but I would hesitate to do this unless I knew the system very well. I could actually cause more issues than it could potentially fix.

    Having said that, I have had to increase this value manually when using a large number of databases. If it would be possible to swap to 2012 or better yet, 2014, I would look at availability groups. It makes better use of shared threads instead of dedicated threads for certain operations.


    Sean Gallardy | Blog | Microsoft Certified Master

    Friday, August 29, 2014 4:25 PM
    Answerer
  • Hi Both,

    Thank you for your responses.

    Sofia - Yes, I'm aware that some threads will be used by both internal SQL Server processes and there will also be a number of threads used by user generated activity.

    Sean - Yes, I'm also hesitant to suggest changing the default max worker threads setting. Moving to SQL 2012 is also an option that I've already suggested to management, specifically to make use of the more efficient thread handling properties of AGs.

    However, I've explicitly been asked whether or not beefing up the hardware - by adding more processors - will allow us to mirror a larger number of databases. Based on my calculations, it appears as though more processors actually means less databases can be mirrored. If someone could confirm whether or not this specific point is correct, I'd be very greatful.

    Regards

    Andy

    Monday, September 1, 2014 8:33 AM
  • Sean, Bodo,

    Forgive me, but I can't see where Sofiya has answered my questions. I'm just looking for confirmation that increasing the number of available cores actually decreases (in real terms) the number of databases that can be mirrored.

    She doesn't seem to have addressed this point. If I'm missing something here, I'd be very greatful if you could point out where.

    Regards

    Andy

    Monday, September 1, 2014 9:51 AM
  • Hi Andy,

    I've just done the calculation according to the given KB article, and it seems you are bloody right. I get the same results as yours in your table.

    However, this is theory only. Practically I have a few customers where we needed to increase the number of worker threads manually (not due mirroring) and they have not had any problems since. Also this calculation applies to the mirror server and maybe increasing the number of worker threads can increase the theoretical limits. But other factors such as network bandwidth and IO should not be forgotten as they can even lower the limits.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by rushandy Monday, September 1, 2014 10:30 AM
    Monday, September 1, 2014 10:04 AM
  • Thanks Bodo,

    One other person coming to the same conclusion is (in this instance) good enough for me, so I'll mark this as answered. 

    I may consider manually increasing the max worker threads limit but this would have to be a last resort given the guidance from MS stating that this isn't generally advisable. I'd prefer to migrate this system to 2012 / 2014 to make use of AGs, as I believe this would be a better solution, but that costs of course, so we'll see. Thanks for checking my figures.

    Thanks to all of you for your responses.

    Regards

    Andy

    Monday, September 1, 2014 10:34 AM
  • If I were you, I'd also prefer AGs in 2013/14. Another great advantage is the listeners that make it possible to automatically fail over applications that don't work with the native client such as some ERP systems.

    Maybe another solution to your problem could be using a combination of failover cluster and async mirroring - provided you are running enterprise editions. This could reduce the impact on production significantly.

    Either way, I am getting a bad stomach thinking of so many mirrored databases even with enough threads for mirroring and core. There's much potential for trouble....


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de


    Monday, September 1, 2014 10:45 AM