Configuring Microsoft SQL Server monitoring profile - Documentation for BMC PATROL for Microsoft SQL Server 9.6

FieldDescriptionEnvironment nameEnter a unique logical name for the environment. A container will be created with the environment name. This environment will hold all the SQL Server databases. BMC recommends you to provide only alphanumeric characters in environment name.Windows user

Windows user credentials are used for discovery and to collect host related data, such as disk size or MS SQL Server service status.

UserName should contain the domain name (DOMAIN_NAME\USER_NAME).

User Name

Enter a unique, user-defined alias name, host name.

User Name should contain a domain name (DOMAIN_NAME\USER_NAME).

PasswordEnter the password for the specified user.Confirm PasswordRe-enter the password for confirmation.MS SQL Server loginUser Name

Enter a unique username. This username is the MS SQL Server login that will be used for monitoring. If this field is left blank then, the Windows User credentials will be used to connect to the MS SQL Server (Integrated Security).

PasswordEnter the password for the specified user.Confirm PasswordRe-enter the password for confirmation.Hosts configurationHost name/IP address

Enter the FQDN or IP Address where the SQL Server is installed.

$HOSTNAME refers to the host on which Patrol Agent is installed. (Default value is $HOSTNAME)

MS SQL server instance configurationMS SQL Server instance name (regex)

Enter the name of the MS SQL server instance. Enter .* to discover all the available instances.

Monitor databases only

Enable this option to monitor only database related metrics. Monitoring of any other SQL Server general metrics will be disabled.

Note

This feature is disabled if Monitor availability only feature is enabled. 

Monitor availability only

Enable this option to monitor only availability related metrics. Monitoring of any other SQL Server general metrics will be disabled.

Databases

Filtering mode

Specify the filtering type for filtering the database.

• None - Selects all databases for filtering
• Include - Includes only the databases entered in the SQL Server databases field for monitoring
• Exclude - Excludes the databases entered in the SQL Server databases field from monitoring

Include/Exclude from monitoring (regex)

Enter a regular expression of the relevant database names to either include or exclude from filtering.

Monitor files and file groups

Enable monitoring of DB files and file groups.

Ignore file 'Autogrowth'

Configuration for Space Used Percentage Metrics

MS SQL AgentJobs considered as failed as long as its last run was failed

Select this check box if you want to consider a job as failed as long as its last run was failed. Such values are stored in the SQL Agent Job Failures parameter. For example, when one job fails in a collection interval, the value of the SQL Agent Job Failures parameter is set to 1 and the value remains so until next run. If the check box is clear, the value of the SQL Agent Job Failures parameter is reset to 0 in the new collection interval if the job did not run during the new collection interval. If you want to retain the number of jobs that failed until the same job runs successfully during any collection interval, select this check box.

Jobs filtering optionsJobs by logins filtering optionsFiltering mode

Specify the filtering type for the filtering the MS SQL Agent jobs by logins.

• None - Selects all available MS SQL Agent jobs by logins for filtering
• Include - Includes only the MS SQL Agent jobs by logins entered in the Include/Exclude from Monitoring field
• Exclude - Excludes the MS SQL Agent jobs by logins entered in the Include/Exclude from Monitoring field

Include/Exclude from monitoring (regex)

Enter a regular expression of the relevant MS SQL Agent jobs by logins to either include or exclude from filtering.

Jobs by categories filtering optionsFiltering mode

Specify the filtering type for the filtering the MS SQL Agent jobs by categories.

• None - Selects all available MS SQL Agent jobs by categories for filtering
• Include - Includes only MS SQL Agent jobs by categories entered in the Include/Exclude from Monitoring field
• Exclude - Excludes the MS SQL Agent jobs by categories entered in the Include/Exclude from Monitoring field

Include/Exclude from monitoring (regex)

Enter a regular expression of the relevant MS SQL Agent jobs by categories to either include or exclude from filtering.

Jobs by names filtering optionsFiltering mode

Specify the filtering type for filtering the MS SQL Agent jobs by names.

• None - Selects all available MS SQL Agent jobs by names for filtering
• Include - Includes only the MS SQL Agent jobs by names entered in the Include/Exclude from Monitoring field
• Exclude - Excludes the MS SQL Agent jobs by names entered in the Include/Exclude from Monitoring field

Include/Exclude from monitoring (regex)

Specify a regular expression of the relevant MS SQL Agent jobs by names to either include or exclude from filtering

Perfmon objects filteringPerfmon monitoring level

• None - Disables the monitoring of all perfmon objects

• Most Important - (Recommended) Monitors all important perfmon objects

   Click here to see the list of most important perfmon objects

  Availability Replica

  Columnstore

  Databases

  Database Mirroring

  Database Replica

  General Statistics

  Locks

  Memory Manager

  Plan Cache

  Replication Dist

  Replication Logreader

  Replication Snapshot

  Resource Pool Stats

  SQL Errors

  SQL Statistics

  Transactions

  XTP Storage

  XTP Transactions

  
  Note: The Most Important perfmon objects are specified in the %PATROL_HOME%mssql\configuration\PerfmonMapping.xml file. The most important perfmon objects are marked as is_important. You can change these perfmons in the PerfmonMapping.xml file. After you update the file, restart PATROL Agent.

• Full - Enables the monitoring of all perfmon objects
• Custom - Choose manually the perfmon objects to monitor

Click Custom filtering settings to filter perfmon objectsFiltering mode

Specify the filtering type for filtering the perfmon objects.

• Include - Will include only the perfmon objects entered in the List - Custom perfmon filtering list for filtering
• Exclude - Will exclude the perfmon objects entered in the List - Custom perfmon filtering list from filtering

Perfmon objects

Select the list of perfmon objects to add to the List - Custom perfmon filtering list to Include/Exclude from filtering.

Access Methods                               Availability Replica Backup Device Batch Resp Statistics Broker Activation Broker/DBM Transport Broker Statistics Broker To Statistics Buffer Manager Buffer Node Buffer Partition Catalog Metadata CLR Columnstore Cursor Manager By Type Cursor Manager Total Databases Database Mirroring

Database Replica Deprecated Features Exec Statistics External Scripts File Table General Statistics HTTP Storage Latches Locks LogPool FreePool Memory Broker Clerks Memory Manager Memory Node Plan Cache Query Execution Query Store Replication Agents Replication Dist Replication Logreader

Replication Merge Replication Snapshot Resource Pool Stats SQL Errors SQL Statistics Transactions User Settable Wait Statistics Workload Group Stats XTP Cursors XTP Garbage Collection XTP IO Governor XTP Phantom Processor XTP Storage XTP Transaction Log XTP Transactions

Custom SQL queriesName

Enter the logical name for the SQL query.

SQL query

Enter the SQL query.

Collection interval (min)Specify the collection interval in minutesTime based parameters configurationLong Running Transactions criteria (min)

Specify the amount of time the transaction has been running before it is considered as a long running transaction. All long running transactions are reported by the Long Running Transactions parameter.

SQL Agent Hung Jobs criteria (min)

Specify the amount of time the job has been running before it is considered as a hung job. All the hung jobs are reported by the SQL Agent Hung Jobs parameter.

Idle Procs criteria (min)

Specify the amount of time the process has not been consuming any resources before it is considered as an idle process. All the idle processes are reported by the Idle Procs parameter.

Number of Blocking Processes criteria (min)

Specify the amount of time the process has been blocked by the blocking process before it is considered as a blocking process. All the blocker processes are reported by the Number of Blocking Processes parameter

Advanced ConfigurationUnique port number (per instance)

Enter a unique port number.

Note

Leave this field blank. System-defined ports are used by default in an empty field. If the System DBA has overwritten the system definitions, enter a unique port number for each instance.

KM administration

Availability collection interval (min)

Enter the availability collection interval in minutes.

Data collection interval (min)Enter the data collection interval in minutes.Device mappingSelect to enable device mapping to consolidate the configured devices at the device level.LoggingSelect to enable debug logging for the environment.Generate detailed events (requires MRL)

Select the metrics to generate events containing the detailed annotation:

• Failed Jobs
• Hung Jobs
• Long Running Transactions

Note

To support this feature, the required MRL needs to be applied on TrueSight cell side. For more details, see Applying the mrl script.

To enable showing these events in BMC Helix Operations Management, create the HelixMonitorEvents configuration variable (/AgentSetup/integration/HelixMonitorEvents) and assign its value as 1.

 Steps to create a configuration variable

To add a configuration variable

1. Click Configuration and select Monitor Policies.
2. Locate the policy and click Edit.
3. Click the Configuration Variables tab and then click Add Configuration Variable.
4. On the Add Configuration Variable page, in the Variable field, enter the variable path and name.
5. From the Operation list, select REPLACE. 
   You create a new variable by using the REPLACE operation.
6. In the Value field, enter the value of the variable.
   If you do not enter a value, default value of the variable is used.

Query timeout (sec)Specify the number of seconds allowed for a query to complete before going into timeout. This time affects all queries executed by the KM to collect data.Global administration settingsLoggingSelect to enable debug logging only at a global level