Buffer Cache Page doesn't load in SQL Server agent. (4245003)

When trying to access the DBSS agent "Buffer Cache" dashboard the following error occur:

"An error occurred while trying to retrieve data to this screen. If this issue persists, contact Quest Support."

In the agent log files an execution timeout can be found.

Error in getBufferCacheList: RemoteException occurred in server thread; nested exception is: com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run Buffer_Cache_List collection. Failed to execute query on instance [usdc-HOSTNAME-INSTANCENAME], user account [USERNAME]. Reason : [The query has timed out.- Profile:MSSQLProfile{host='HOSTNAME', instance= 'DEFAULT_INSTANCE', username='.', authType= 'WINDOWS_DEFAULT', port= '0' useNTLMv2= 'true', socketTimeout= '900', database= 'master', secureConnection= 'OFF', packetSize= '0', ApplicationIntent= 'NONE', enforceSSLVersion= 'NONE', lockTimeout= '10000', codePageOverride = '', selectMethod='' } Query: "-- SQL Server 2005/8...

The Buffer Cache collection query is timing out; it may take longer if the number of Database Pages is high.

The following SQL queries can be used to review the size of the Buffer Cache:

SELECT COUNT(*) AS TotalPagesInBufferCache FROM sys.dm_os_buffer_descriptors with (NOLOCK)

Select cntr_value as pages from sys.dm_os_performance_counters where object_name like '%Buffer Manager%' and counter_name='Database Pages'

SELECT COUNT(DISTINCT database_id) AS NumberOfDatabasesInBufferCache FROM sys.dm_os_buffer_descriptors with (NOLOCK)

SELECT databases.name AS DatabaseName, COUNT(*) * 8 / 1024 AS mb_used FROM sys.dm_os_buffer_descriptors INNER JOIN sys.databases ON databases.database_id=dm_os_buffer_descriptors.database_id GROUP BY databases.name

NOTE: The failure of the on-demand collection for Buffer Cache page is usually accompanied by the failing of the regular agent collection of the buffer cache page in the database agent log files.

Increasing the query timeout for the Buffer Cache list collection

This affects the timed collection run by the agent. By default the Buffer Cache collection query timeout should be 300 seconds.

To increase the buffer cache collection query timeout

1. Navigate to Administration | Agents | Agent Status
2. Place a checkbox next to the SQL Server agent that has the issue 
3. Click on Edit and then Edit Properties in the menu
4. Click on the link to modify the private properties for the agent
5. Scroll down the Collection Details area
6. Click the Edit button next to On demand collection details
7. Add the BUFFER_CACHE_LIST collection if it already present
8. Increase the query timeout to 600 (or more) seconds
9. Click the Save button
10. Navigate back to Agent Status (Administration | Agents | Agent Status or by clicking the Agent Status link in the top right corner of the page)
11. Click on the Deactivate button
12. Click on the Activate button
13. Validate the buffer Cache results in the database agent dashboard

Increasing the On-Demand timeout for all collections

To increase the on-demand timeout for all collections including the Buffer Cache List collection review knowledgebase article 4308516 to increase on demand timeout.

Options for when there are a huge number of pages in the buffer cache to scan

In situations where there is a huge number (e.g. 100 million) of pages in the buffer cache to scan, there is little that can be done to improve the query time. The options in this circumstance are

A). Schedule a report that will periodically query the Buffer Cache and prepare a canned report and save it to a given location. This way, customer can get a full list of DB objects in the Buffer Cache at periodic times. This would be a customization

B). Look at Memory > Summary. In the top right corner, look at the Buffer Cache chart under memory areas, it shows the use in percent of the Buffer Cache pool.

Click "Memory" in SQL PI in the top workload areas. Then in the bottom "memory related metrics" chart, there is "Buffer Cache Hit Ratio" as well.

C). In SQL PI Memory area - Instance view -> SQL Statements -> sort by granted Memory to check which of the SQL statements is using the most memory 

D). Decrease the number of databases and cache pages used by the Buffer Cache list collection. By default this is set to 10 databases and 200000 cache pages.

The following script can be used to adjust these hidden agent status property values

• Replace AGENTNAME with the SQL Server agent name
• Adjust the value next to bfcMaxPages to increase or decrease the setting (this script will lower the value to 50000 max pages)
• Adjust the value next to bfcMaxDBs to increase or decrease the setting (this script will lower the value to 5 databases)
• Knowledgebase article 4309796 details how to run a groovy script in the script console

configService = server.get("ConfigService");
agentService = server.get("AgentService");

def modifyAgentPropertiesPrimary(agent) {		
	def primary = configService.getAgentInstancePrimaryAsp("DB_SQL_Server", "DB_SQL_Server", agent.getId());
	primary.setValueByString("bfcMaxPages", "50000");
  primary.setValueByString("bfcMaxDBs", "5");
  configService.saveConfig(primary);		
}


def getAgentByName(agent_name) {  
  agents = agentService.findByName(agent_name);

  if ((agents != null) && (agents.size() > 0)) {
    return agents.get(0);
  } else {
    return null;
  }
}

AGENTNAME = "MYAGENTNAME";

agent = getAgentByName(AGENTNAME);
buff = new StringBuffer();

if (agent != null){
	modifyAgentPropertiesPrimary(agent);	
	buff.append( "Agent '" + AGENTNAME + "' ASP has been changed; \n" );	
} else {
   buff.append( "Agent '" + AGENTNAME + "' not found ; \n" );	
}

return buff.toString();

Enhancement ID FGSS-I-209 has been logged to unhide these properties. This will be reviewed by Product Management for consideration in a future release of the SQL Server cartridge.

Enhancement ID FGSS-I-210 has been logged to exclude named databases from the collection. This will be reviewed by Product Management for consideration in a future release of the SQL Server cartridge.