Memory - Buffer Manager Free Pages

This script will display the current value of the number of free pages available in the buffer pool for a SQL Server instance.

Having a sustained value below 640 could mean that new memory requests may stall and performance will suffer. Looking at Buffer Manager Free Pages over a period of time can give a general indication of possible memory pressure.  You will also want to look at Page Life Expectancy and Buffer Catch Hit Ratio.

Buffer Manager Free Pages in SQL Server 2012 is not applicable.

SELECT  cntr_value ,
        GETDATE()  AS [CollectionDt]
FROM    sys.dm_os_performance_counters WITH ( NOLOCK )
WHERE   OBJECT_NAME = N'SQLServer:Buffer Manager'
        AND counter_name = N'Free pages'
OPTION  (RECOMPILE);

To learn more about the Buffer Manager and Free Pages and memory monitoring, please take a look at these links:

• http://msdn.microsoft.com/en-us/library/ms189628(v=sql.105).aspx
• http://msdn.microsoft.com/en-us/library/ms187743(v=sql.110).aspx
• http://blogs.msdn.com/b/vsanil/archive/2012/11/21/did-some-performance-counters-like-free-pages-disappear-in-sql-2012.aspx

Operation is not valid due to the current state of the object

I have discovered how to fix this, but there was not a whole lot of information out there yet, so I thought I would add a post with all the information I found. Hopefully you can add to the discussion with anything useful to add.

Solution

Before getting into the details, the quick solution is to add key="aspnet:MaxHttpCollectionKeys" value="?" to the appSettings in Web.config. The default value is 1000, which appears to have changed in a recent security update. Set the value to something higher than 1000, but understand the security implications before doing this. There is a reason why Microsoft made the change. I recommend reading the KBs mentioned in this post.

Here is the key I added to <appSettings> in Web.config for Reporting Services. On the Report Server Web.config you may have to add <appSettings>.

<add key="aspnet:MaxHttpCollectionKeys" value="1500" />

Symptoms

After adding several security updates from Microsoft Update on one of our Reporting Services instances, we started receiving complaints that some reports would not run and would display the following message.

Operation is not valid due to the current state of the object

Our Symptoms

• Reports with large number of parameters, or form fields, would not return the report and display “Operation is not valid due to the current state of the object” in Reporting Services and Report Server.
• Applications that called the Report Server for a report with a large number of parameters, or form fields, would display a generic “rsInternalError” message.
• We installed Security Update for Microsoft .NET Framework 4 on XP, Server 2003, Vista, Windows 7, Server 2008 x86 (KB2656351). http://support.microsoft.com/kb/2656351
• Event ID 1309 in the Application Event Viewer from source ASP.NET with: Event code: 3005, Event message: An unhandled exception has occurred.

Others’ Symptoms

• Source Error: System.InvalidOperationException
• Stack Trace: InvalidOperationException: Operation is not valid due to the current state of the object.

Cause

This appears to be caused from Microsofts security bulletin MS11-100 (http://technet.microsoft.com/en-us/security/bulletin/ms11-100) for the .Net framework. In short, the update resolves vulnerabilities in how .Net handles requests, authenticates users, and cached content.

It looks like this has impacted ASP.Net environments that have installed KB 2656351. Any of these security updates described in security bulletin MS11-100 may also cause this issue, although I have only researched 2656351.

• KB 2656351
• KB 2656356
• KB 2657424
• KB 2656352
• KB 2656362
• KB 2656355
• KB 2656358
• KB 2656353

Here are some links I used to bring this information together.

• http://technet.microsoft.com/en-us/security/bulletin/ms11-100
• http://support.microsoft.com/kb/2656351
• http://forums.asp.net/t/1755767.aspx/1
• http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cb6ede72-6ed1-4379-9d3c-847c11b75b32
• http://www.c-sharpcorner.com/Blogs/8006/operation-is-not-valid-due-to-the-current-state-of-the-objec.aspx
• http://kreelbits.blogspot.com/2011/12/maxhttpcollectionkeys.html
• http://www.sqlservercentral.com/Forums/Topic692785-147-1.aspx

Hope this helps. Any contribution is appreciated.

Regards,
Jon