SQL Monitor - 3.1
Step 1. Define metric
The sections in Step 1. help you complete the custom metric definition.
Enter custom metric details
Adding a metric name is compulsory, because it is the unique identifier for that metric in SQL Monitor. The name can contain up to 255 characters.
The metric description is optional, but recommended. The text you enter is displayed in the Description tab on the Analysis page to help users understand the metric values. If you want to follow the format of descriptions for existing metrics, include:
- an initial overview of what it being measured.
- the equivalent PerfMon counter (if relevant).
- a more detailed explanation of why the metric is useful, how values are calculated, and what exceptions exist.
- optimal values and dependencies, including examples of baseline values.
- related metrics that may affect or be affected by this metric.
- references to useful TechNet articles or similar sites
Enter the T-SQL query used to collect data
The T-SQL on which the custom metric is based must return a single, numeric scalar or NULL value. It can call a stored procedure, as long as the returned value meets the same criteria. It must not collect multiple values, results containing alphabetic or special characters, or recordsets.
What cannot be included in the T-SQL query?
You must exclude:
- statements that make a change to the database, for example, INSERT; UPDATE; DELETE; DROP; CREATE; ALTER.
- statements that will affect the performance of normal database operations and may place a burden on the server. For example, do not query against the fragmentation level of indexes.
You should also avoid USE statements that can collect a value from one database and store it against another, potentially incorrect database. There's another section for specifying databases to collect from, so you don't need to do this in the T-SQL.
Warning: To prevent causing damage to your servers or data, we recommend that you validate and run the query against a non-production database in your SQL Server management software before adding it to SQL Monitor.
Select instances to collect from
Every instance currently being monitored is displayed in this section. Use the check boxes to select specific instances from which to collect data, or use the Select all and Select none buttons to make selection easier. Selecting a single instance automatically selects the machine that hosts it, and similarly, selecting the machine automatically selects the instance.
Choose databases to collect from
The query will run once against the databases you select from the following options:
- All databases - every database stored in the selected instances.
- All user databases - every non-system database.
- Specify database - every database name that you enter in the associated text box. Enter each name on a separate line:
Database names are not case sensitive. If you have databases with names differentiated by the use of upper or lower case characters (for example, ProductionXYZ and productionxyz) on two different servers, values will be collected from both databases.
If you are collecting server-level metrics, you should select Specify database and enter
master in the text box.
Test metric collection
We recommend testing the T-SQL query in SQL Monitor to make sure that data can be successfully collected from monitored objects within a reasonable duration.
- Click the Test metric collection button. In the displayed dialog, the databases previously specified are selected by default. If you don't want to test every object, change the selection by unchecking the boxes or using the selection buttons below.
- Click the Test metric collection button. SQL Monitor tests the query once against each selected database and displays its progress at the bottom of the dialog.
If an object fails the test, or the duration is significantly longer than expected:
- Check the exception or error messages displayed in the Return value column.
- Make sure the T-SQL query complies with the details listed in the section above called What cannot be included in the T-SQL query?.
- Check for other problems with the T-SQL, such as invalid syntax or objects referenced in the query that don't exist in the database.
- Check the Monitored servers page (Configuration > Monitored servers) and the Alert Inbox for status errors or alerts affecting servers hosting selected instances.
Once you've fixed any connection or T-SQL problems, retry the test until they complete successfully.
Set collection frequency
This section determines how often the query will run against selected databases to collect data. The collection frequency options available are:
- every minute
- every 5 minutes
- every 10 minutes
- every 30 minutes
- every hour
- every day
The first collection will take place within 10 seconds of the metric being created. For metrics with a daily frequency set, subsequent collections will take place at the same time of day as the initial collection.
Note: The more frequently data is collected, the greater the storage impact on the Data Repository. By default, data older than one week is purged on an hourly basis. To ensure that the Data Repository doesn't use up too much hard disk space, check the purge settings for custom metric data. See Purging SQL Monitor data.
Use collected or calculated values
Custom metric values are displayed in the analysis graphs as data points. They also form the basis of alert thresholds should you create an alert based on this metric.
By default, SQL Monitor uses the actual values collected after running the T-SQL query. For example, if you're measuring database size in GBs, you want to see the actual GB size at each data point in the Analysis graphs. In most cases, this is the value you're interested in.
If a metric is measuring a property that changes over time, you want to see the rate, not the actual collected value. For example, if you measure the database's daily growth rate in GBs, you're no longer interested in finding out the actual GB size at each data point. Turn on the Use a calculated rate of change between collections check box. SQL Monitor calculates the rate for you by finding the difference between each pair of consecutive values and dividing it by the number of seconds between each collection.
Metric collection is Enabled/Disabled
By default, metric collection is enabled, so data collection can start after all three steps of the wizard have been completed. If you want to delay collection, click Disabled. You can enable the metric later using the wizard or the Custom metric page (Configuration > Custom metric).
Once you've completed this page, click Next to go to Step 2 - Add an alert.
Was this article helpful?
Thanks for your feedback!
- Example configuration of SQL Monitor using virtualization software
- Retrieving alert settings using SQL Data Compare
- Licensing issues with SQL Monitor
- Log files in SQL Monitor
- Upgrading to a later version of SQL Monitor
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs