2

sp_Blitz (20160715) is reporting "Too Much Free Memory" on a seldom-used 2012 SP2 CU12 test instance (Developer Edition) that only contains the databases created and used by SSRS. The Details are:

0GB of free memory inside SQL Server's buffer pool, which is 0GB. You would think lots of free memory would be good, but check out the URL for more information.

I modified the query to return the raw values and got Free = 587,896 and Total = 1,052,256. My altered query:

SELECT Free = cFree.cntr_value, Total = cTotal.cntr_value
FROM sys.dm_os_performance_counters cFree
INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
    AND cTotal.counter_name = N'Total Server Memory (KB)                                                                                                        '
WHERE cFree.object_name LIKE N'%Memory Manager%'
    AND cFree.counter_name = N'Free Memory (KB)                                                                                                                '
    AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
     AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'

dbo.sp_BlitzCache (@BringThePain = 1) displays 316 rows, all apparently built-in SSRS housekeeping statements. Is it more likely that:

  1. I have a problem as described at https://www.brentozar.com/blitz/too-much-free-memory/, or
  2. The WHERE clause of the sp_Blitz query needs tweaking to avoid a false positive in this circumstance
2
  • It also seems odd that the cntr_value columns are being CAST to BIGINT in the WHERE clause when they are already BIGINTs, but this probably doesn't hurt anything. Aug 5, 2016 at 20:17
  • #2, I reckon, and it looks like Tara has logged a bug with them. Also, these diagnostic scripts from vendors require interpretation, and should not be taken at face value. Good on you for questioning the results. That's a good sign. Aug 5, 2016 at 21:30

1 Answer 1

4

I logged a bug for it over on the SQL Server First Responder Kit repository on Github. You can check it for status and also add to it as needed.

1
  • 1
    FYI - this has been fixed in the dev branch, and we'll merge it to master in August.
    – Brent Ozar
    Aug 6, 2016 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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