Calculating Some Max Mirror Stats
sql-server
This turned out to be quite a challenge. I couldn’t find anything that made this very clean and straight forward to calculate, and in my case I was trying to gauge how many mirroring databases I could run on a server.In my scenario, I wasn’t running Expensive Edition (@BrentO coined this wonderful phrase), so was looking for the best way to assess numbers by doing mirroring on a large number of databases, in my case > 300 eventually. The documentation was… well… a bit confusing. I felt like my notes were from the movie “A Beautiful Mind” as I tried to calculate just how many mirrors were too many! This is my code snippet for calculating some basic numbers as I walked through the process. Seems much easier after I finished breaking down the steps. And yes, Expensive Edition had additional thread impact due to multi-threading after I asked about this. Feedback is welcome if you notice a logical error. Note that this is “theoretical”. As I’ve discovered, thread count gets reduced with increase activity so the number mirrored database that can be mirrored with serious performance issues gets decreased with more activity on the server.
Calculator_Mirroring.sql
/*******************************************************
THIS IS FOR 64 BIT OPERATING SYSTEMS : I didn't run analysis on 32bit
For 64 bit operating system:
- Total available logical CPU’s <= 4 : max worker threads = 512
- Total available logical CPU’s > 4 : max worker threads = 512 + ((logical CPUS’s - 4) * 16)
Database mirroring has the following requirements for worker threads
- Principal server: 1 global thread and 2 threads per each of the mirrored databases.
Mirror server:
- 64 bit architectures: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.
*******************************************************/
------------------------- set variables -------------------------
declare @MirrorDbCount int = 50
,@LogicalCPU int = 4
,@UsingWitness int = 1 -- ENTER: 1/0
,@IsEnterprise bit = 0 -- ENTER: 1/0
,@AdditionalThreadPer4 int = 0
,@TotalAdditionalThreadsToAdd int = 0;
print '@MirrorDbCount = ' + cast(@MirrorDbCount as varchar(100))
print '@LogicalCPU = ' + convert(varchar(max), @LogicalCPU)
print '@UsingWitness = ' + convert(varchar(max), @UsingWitness)
------------------------- calc variables -------------------------
print char(10) + char(10) + '-- Calculated Variables --'
declare @MaxWorkerThreads int = case
when @LogicalCPU <= 4 then 512
else ((@LogicalCPU - 4) * 16)
end;
if @IsEnterprise = 1
begin
set @AdditionalThreadPer4 = floor((@LogicalCPU + 3) / 4) -- My very overcomplicated way till i read the forumla in technet .... ceiling((((@LogicalCPU / 4.00) * 4) / 4) % 4) -- THIS MIGHT BE APPLICABLE ONLY TO ENTERPRISE (ONE ARTICLE SAID THIS)
set @TotalAdditionalThreadsToAdd = @AdditionalThreadPer4 * @MirrorDbCount
end
print '@MaxWorkerThreads = ' + cast(@MaxWorkerThreads as varchar(100))
print '@AdditionalThreadPer4 = ' + cast(@AdditionalThreadPer4 as varchar(100))
print '@TotalAdditionalThreadsToAdd = ' + cast(@TotalAdditionalThreadsToAdd as varchar(100))
print char(10) + ' --------> NOTE: Add 1 thread for each mirrored database for every 4 processor cores ( FORUMULA: ceiling((((@' + cast(@LogicalCPU as varchar(100)) + ' / 4.00) * 4) / 4) % 4)'
print ' --------> RESULTS: @TotalAdditionalThreadsToAdd = ' + cast(@TotalAdditionalThreadsToAdd as varchar(100)) + ' ' + ' (@AdditionalThreadPer4 ' + cast(@AdditionalThreadPer4 as varchar(100)) + ' * @MirrorDbCount ' + cast(@MirrorDbCount as varchar(100)) + ')'
/*******************************************************
CALC: principal server details
*******************************************************/
print char(10) + char(10) + '------------------------- CALC RESULTS ------------------------- '
declare @TotalCountPrincipal int = 1 + @UsingWitness; -- starts with single global thread
set @TotalCountPrincipal += (@MirrorDbCount * 2)
print '@TotalCountPrincipal = ' + cast(@TotalCountPrincipal as varchar(100))
/*******************************************************
CALC: mirror server
- In SQL Server Standard, one redo thread per mirror database, or in SQL Server Enterprise, one redo thread per mirror database for every four CPUs. These threads perform the actual log redo.
*******************************************************/
declare @TotalCountMirror int = 1 + @UsingWitness; -- starts with single global thread
set @TotalCountMirror += (@MirrorDbCount * 3)
print '@TotalCountMirror = ' + cast(@TotalCountMirror as varchar(100))
/*******************************************************
provide details on the estimated mirroring threads consumed by setup
- using the lowest common denominator (the mirror server)
- I WENT WITH THE CONSERVATIVE CALC THAT ASSUMES THIS ADDITIONAL COST EVEN IF NOT ENTERPRISE
*******************************************************/
print 'Total Threads Consumed by Current Setup: ' + cast((@TotalAdditionalThreadsToAdd + @TotalCountMirror) as varchar(100))
print 'Remaining threads: ' + cast(@MaxWorkerThreads - (@TotalAdditionalThreadsToAdd + @TotalCountMirror) as varchar(100))
/*******************************************************
CALC: DETERMINE THE MAX THEORATICAL DB SUPPORTED
*******************************************************/
print char(10) + '------------------------- MAX SUPPORTED DB ------------------------- '
declare @MaxSupportedDbs int = 0;
set @MaxSupportedDbs = (@MaxWorkerThreads - @UsingWitness) / (@AdditionalThreadPer4 + 3)
print '@MaxSupportedDbs = (@MaxWorkerThreads- @UsingWitness) / (@AdditionalThreadPer4 + 3)' + ' --------> ((' + cast(@MaxWorkerThreads as varchar(100)) + ') - ' + cast(@UsingWitness as varchar(100)) + ') / (' + cast(@AdditionalThreadPer4 as varchar(100)) + ' + 3))';
print '@MaxSupportedDbs = ' + cast(@MaxSupportedDbs as varchar(100))
print 'This is with no other activity, so its not a realistic real world calculation'