Return
-
Title
Setting options for displaying data in the SQL Server Plan Cache and Buffer Cache -
Description
The Plan Cache view allows configuring the default display settings for the SQL Server's plan cache panel, accessible using the Memory drilldown.Use this view to filter the Plan Cache panel's display, by setting criteria such as which object type is displayed, as well as the default number of records displayed and the sorting method.The Buffer Cache view allows configuring the default retrieval settings for Buffer Cache panel, accessed through the Memory drilldown.A lower buffer cache hit rate can be resolved by investigating the objects that consume the largest amount of cache size.Use this view to configure the number of objects to be displayed in the table and their sorting properties. -
Resolution
- Navigate to the Databases Dashboard
- Select one or more SQL Server database agents
- Click Settings | Administration
- Click the Collection Settings item
- Click Edit in the Plan Cache Settings section to edit the following parameters.
- Save Changes
Parameter Description Show Adhoc objects Defines whether to display Adhoc SQL plans. Show system objects Defines whether to display SQL Server system objects. Filter Database Name Defines whether to display only objects that reside on certain databases. When setting this parameter, the character % can be used as a wild card. For example, to display objects from all of the databases that begin with Quest (Questdatabase, QuestWorkDatabase, and so on), enter Quest%.Top N Records Defines the maximum number of objects to be displayed in the Plan Cache panel (default: 20). Filter Object Name Defines whether to retrieve only certain objects. When setting this parameter, the character % can be used as a wild card.Order by Defines the criterion for determining the data display order. The available criteria are as follows:- Database Name (Default) — the name of the database
- Schema Name — the name of the schema
- Object Name — the name of the object
- Object Type — the object type
- Use Count (Default) — the number of times this cache object has been used since inception
- Ref Count — the number of other cache objects that reference this cache object
- Cache Object Type — the type of the cache object
- SQL Bytes — the size of the text
- SQL Text — the SQL text
- Used Date Format — the date format used by the object
- Used Language — the language format used by the object
- MB — the amount of space in the plan cache that is allocated to this object
- % from Cache — the percentage of cache used by the object
- Used MB — the size, in megabytes, used by the object type
Order direction Defines whether the display would be carried out in ascending or descending order. - Navigate to the Databases Dashboard
- Select one or more SQL Server database agents
- Click Settings | Administration
- Click the Collection Settings item
- Click Edit in the Buffer Cache Settings section to edit the following parameters.
- Save Changes
Parameter Description Top N Buffer Cache Objects by Cached MB Defines the maximum number of objects to be displayed in the Buffer Cache panel (default: 20). Order direction Defines whether the order of the items for retrieval is ascending or descending. Order by Defines the parameter by which the display is to be ordered. After carrying out all of the requested changes, save the changes before switching to another view.