Chat now with support
Chat with Support

Foglight for SQL Server (Cartridge) 5.7.5.30 - User Guide

Managing SQL Server Database Systems Introduction to this Guide Using Foglight for SQL Server Monitoring Business Intelligence Services Administering Foglight for SQL Server Glossary Reference

Setting Options for Displaying Data in the Plan Cache

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.
 
Table 8. Click Edit in the Plan Cache Settings section to edit the following parameters.
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.
After carrying out all of the requested changes, save the changes before switching to another view. For details, see Reviewing the Administration Settings.

Setting Options for Displaying Data in the Locks Panel

The Locks view allows setting parameters for displaying data in the SQL Server's Locks panel, under the SQL Activity drilldown, as well as configuring the minimal duration, in seconds, which a block should reach or exceed in order to be collected and displayed in the Blocking (History) panel.
 
Table 9. The parameters that can when clicking Edit on this view are as follows:
Parameter
Description
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%.
Filter Object Name
Defines whether to display only certain objects.
When setting this parameter, the character % can be used as a wild card.
Show System Objects
Defines whether to display SQL Server system objects.
Show Database Shared Locks
Defines whether to display shared locks.
Show Intent Locks
Defines whether to display Intent locks.
Show TempDB Locks
Defines whether to display locks on temp DB objects.
Blocking History section
The minimal duration (seconds) for collecting a block in the Blocking (History) panel is:
Defines the minimal duration, in seconds, which a block should reach or exceed in order to be collected. All blocks that meet this criterion are displayed in the lower section of the SQL Activity > Blocking (History) panel.
After carrying out all of the requested changes, save the changes before switching to another view. For details, see Reviewing the Administration Settings.

Defining Retention Policies

The Retention Policies view allows defining the requested time range for which each of the metric collections, which are defined in the Collection Frequencies view, are to be kept.
Data can be retained for brief, moderate, or long periods, by selecting one of the following options:
•
Short — retains data up to one month
•
Medium — retains data up to three months
•
Long — retains data up to one year
To modify the retention policy:
1
Click the text that displays the current retention policy.
The dialog box Edit the Retention Policy appears.
2
Select the requested retention policy scheme from the list.
3
Click Set to apply the selected setting or Cancel to reject the setting.
4
After carrying out all of the requested changes, save the changes before switching to another view. For details, see Reviewing the Administration Settings.

Defining the Collection and Display of Top SQL Statements

The Top SQL Statements pane allows configuring the requested settings for top SQL statements, that is, statements that generated the maximal workload, as defined by the criterion by which the selected SQL statements are sorted. Use this pane to define the criterion of top SQL statements, as well as the maximum number of such statements to be retrieved and to be displayed. Top SQL statements are displayed on the SQL Activity > Top SQL Statements panel.
To define the settings for collecting and displaying SQL statements:
1
Click Edit.
The Edit Top SQL Statements Setting s dialog box appears.
2
Use the Maximum number of displayed SQL statements field to enter the maximum number of SQL statements that are to be displayed in the Top SQL Statements table.
3
Use the Maximum number of retrieved SQL statements field to enter the number of SQL statements that generated the maximal workload and are to be saved to the Top SQL statements collection.
4
Use the Sort the collected SQL statements by list to select the field by which the list is to be sorted.
The possible values are as follows:
•
CPU Time — the total CPU time consumed for carrying out the SQL statement executions
•
Elapsed Time — the total time consumed for carrying out the SQL statement executions
•
Executions — the number of times the SQL script executed for a unique SQL handle
5
Use the Maximum size of short SQL statement field to type the maximum number of characters for the short SQL text.
6
Click Set to save these settings.
7
After carrying out all of the requested changes, save the changes before switching to another view. For details, see Reviewing the Administration Settings.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating