Sp_BlitzCache gives a “divide by zero error encountered.”

sp-blitzcachesp-blitzfirst

After updating to First Responders Kit 20180901, I have a job that runs every 15 minutes and calls sp_BlitzFirst:

EXEC [DBA Maintenance].[dbo].[sp_BlitzFirst]
    @OutputDatabaseName = 'DBA Maintenance', 
    @OutputSchemaName = 'dbo', 
    @OutputTableName = 'BlitzFirst',
    @OutputTableNameFileStats = 'BlitzFirst_FileStats',
    @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
    @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
    @OutputTableNameBlitzCache = 'BlitzCache',
    @OutputTableRetentionDays = 10;

After updating the SQL Scripts to First Responders Kit 20180901, the Job keeps failing and when I run the Step SQL manually I get below messages:

. . .
Populating Warnings column
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 3972
Divide by zero error encountered.
The statement has been terminated.
Populating Warnings column for stored procedures
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 4047
Divide by zero error encountered.
. . .
Writing results to table.
sp_BlitzCache Finished

I think the error is happening for the sp_BlitzCache CASE lines

CASE 
    WHEN   CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. 
    THEN    ', High Compile Memory' 
    ELSE    '' 
END

When I SELECT the values in the ##bou_BlitzCacheProcs table, MaxCompileMemory is either NULL or 0 (Zero).

Anybody else having this issue?

Is there a SQL Setting I need to look at?

Best Answer

Clearly, without a doubt, that's a bug. You'll want to post it there.

I'm guessing MaxCompileMemory is 0 for you? This is the only division I see,

CASE WHEN CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. THEN ', High Compile Memory' ELSE '' END, 3, 200000) 

Update

Filed here and fixed with this patch.