Foglight
Cartridge for SQLServer
User Guide
This document contains proprietary information, which is protected by copyright. The software described in this document is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser's personal use without the written permission of Quest Software, Inc.
Warranty
The information contained in this document is subject to change without notice. Quest Software makes no warranty of any kind with respect to this information. QUEST SOFTWARE SPECIFICALLY DISCLAIMS THE IMPLIED WARRANTY OF THE
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Quest Software shall not be liable for any direct, indirect, incidental, consequential, or other damage alleged in connection with the furnishing or use of this information.
Trademarks
Foglight® is a registered trademark of Quest Software, Inc.
Foglight software includes 1996 Expect© software (freeware). Expect and its documentation are copyrights and trademarks of Don Libes, Associates.
This product includes software developed by the OpenSSL Project Copyright (c) 1998-2003 The OpenSSL Project. All rights reserved.
Portions of the code in this package were distributed by Carnegie Mellon University. 1989, 1991, 1992, Carnegie Mellon University. All rights reserved.
Portions of this product were obtained from the ucd-snmp package written by Wes Hardaker at the University of California, Davis copyright © 1996, 1998-2000. Copyright © 2001-2002, Networks Associates Technology, Inc. All rights reserved. Portions of this code are also copyright © 2001-2002, Cambridge Broadband Ltd. All rights reserved.
Portions of this software are derived from the RSA Data Security, Inc. MD5 Message-Digest Algorithm.
Foglight software includes Info-ZIP. Info-ZIP is provided "as is" without warranty of any kind, express or implied. In no event shall Info-ZIP or it's contributors be held liable for any direct, indirect, incidental, special or consequential damages arising out of the use of or inability to use this software.
This product includes software developed by The Apache Software Foundation (http://www.apache.org/).
All other trademarks and registered trademarks used in this document are property of their respective owners.
World Headquarters 8001 Irvine Center Drive Irvine, CA 92618
www.quest.com
email: [email protected]
U.S. and Canada: 949.754.8000
Please refer to our Web site for regional and international office information. Foglight® Cartridge for SQLServerTM User Guide
Updated - April 2005 Software Version - 3.2.3 Foglight Version - 4.2
C
ONTENTS
SQLSERVER AGENT . . . 1
ABOUT QUEST SOFTWARE, INC. . . 1
Contacting Customer Support . . . 1
Contacting Quest Software . . . 1
ABOUTTHE SQLSERVER AGENT . . . 2
USING SQLSERVER ASPS . . . 3
Setting the Connection Details . . . 3
Setting the Data Management Parameters. . . 6
Setting the Service Checks . . . 8
Setting the Collection Parameters. . . 9
SELECTINGA COLLECTION MODEL. . . .11
MANAGING COLLECTION DATA . . . .14
DEBUGGINGAND TROUBLESHOOTING . . . .16
CLUSTER HANDLING . . . .21
COLLECTION ERROR RETURN CODES . . . .23
EDITINGTHE ERRLOG SEARCH STRINGSLIST . . . .24
EDITINGTHE ERRLOG EXCLUSIONLIST. . . .26
EXCLUDINGDATABASES FROMBEINGMONITORED. . . .27
EDITINGTHE TABLE ID LIST . . . .29
EXCLUDINGJOBS FROMTHE JOBTABLE. . . .30
OVERRIDING DEFAULTSAMPLE AND PURGINGVALUES . . . .31
SQLSERVER AGENT TABLES . . . .32
AMPerfInfo Table . . . 34
Availability Table . . . 35
Backup Table . . . 37
Blocking Table (renamed Blocking_323) . . . 37
BMPerfInfo Table . . . 39
Cache Table . . . 39
CollectionStatus Table. . . 40
Connection Table . . . 41
Database Table (renamed Database_323) . . . 42
DBMonCount Table . . . 42
DBStatus Table . . . 44
ErrorLog Table (renamed ErrorLog_323) . . . 45
File Table (renamed File_323) . . . 46
Filegroup Table (renamed Filegroup_323) . . . 47
General Table (renamed General_323) . . . 47
Jobs Table (renamed Jobs_323) . . . 48
Lock Table . . . 49 LockRate Table. . . 49 LogShipping Table . . . 50 Memory Table . . . 50 MMPerfInfo Table . . . 51 Replication Table . . . 52 Response Table . . . 53 Service Table . . . 53 SQLConfig Table . . . 54 Statistic Table . . . 55 System Table . . . 55 TableSize Table . . . 56
TopUsers Table (renamed TopUsers_323) . . . 56
Trace Table . . . 57
INVESTIGATIONS . . . .59
Investigating Data . . . 59
Investigating Locks and Blockers . . . 59
Investigating Memory Usage . . . 60
Investigating Performance . . . 60
Investigating Physical Space . . . 61
Investigating Processes and Jobs . . . 62
VIEWS . . . .63
SQLServer Buffer Cache Free Pages Graph . . . 63
SQLServer Cache Hit Ratios Graph . . . 63
SQLServer Compiler Statistics Graph . . . 64
SQLServer Connections Activity Graph . . . 65
SQLServer Connections License Summary Graph . . . 65
SQLServer Connections Login Rate Graph . . . 66
SQLServer Connections Maximum Blocking Time Graph . 66 SQLServer Connections Type Graph . . . 67
SQLServer Database Space Overview Graph . . . 67
SQLServer Graph Database User . . . 68
SQLServer File Space Overview Graph . . . 68
SQLServer Lock Rate Graph. . . 70
SQLServer Memory Areas Graph . . . 70
SQLServer Rates Statistics Graph . . . 71
SQLServer Response Time Graph . . . 72
SQLServer Table Row Count Graph. . . 72
SQLServer Table Size Graph . . . 73
RULES. . . .74
Changing Rule Threshold Values. . . 74
Agent Status Rule. . . 75
Agent Mail Status Rule . . . 75
Buffer Cache Free Pages Rule. . . 76
Buffer Cache Hit Ratio Rule . . . 77
Cluster Failover Rule . . . 78
Cluster Node Status Rule. . . 78
SQLServer Rule Collection Error . . . 78
SQLServer Rule Collection Timeout . . . 79
Database Status Rule . . . 79
Days Since Last Backup Rule . . . 79
Deadlock Rate Rule. . . 80
DTC Status Rule . . . 81
Error Log Rule . . . 81
FTS Status Rule . . . 82
Last Run Outcome Rule . . . 82
License Limit Rule. . . 83
Log Shipping Failures Rule. . . 84
Login Rate Rule . . . 84
Maximum Block Time MS Rule . . . 85
Max Server Memory Rule. . . 85
Monitored Databases Rule . . . 86
OLAP Status Rule . . . 87
Potential Growth MB Rule . . . 87
Procedure Cache Hit Ratio Rule . . . 88
Recompile Rate Rule . . . 89
Replication Agent Failed Rule . . . 89
Replication Agents Retrying Rule . . . 90
Replication Conflicts Rule. . . 90
Response Time Rule . . . 90
Server Mail Status Rule . . . 91
SQLServer Running Rule . . . 91
Trace Poor Performer Rule . . . 92 Worker Threads Rule . . . 93
SQLServer Agent
About Quest Software, Inc.
Quest Software, Inc. delivers innovative products that help organizations get more performance and productivity from their applications, databases and infrastructure. Through a deep expertise in IT operations and a continued focus on what works best, Quest helps more than 18,000 customers worldwide meet higher expectations for enterprise IT. Quest Software, headquartered in Irvine, Calif., can be found in offices around the globe and at www.quest.com.
Contacting Customer Support
Quest Software’s world-class support team is dedicated to ensuring successful product installation and use for all Quest Software solutions.
SupportLink: www.quest.com/support Email: [email protected]
You can use SupportLink to do the following: • Create, update, or view support requests • Search the knowledge base
• Access FAQs • Download patches
Contacting Quest Software
Phone: 949.754.8000 (United States and Canada) Email: [email protected]
Mail: Quest Software, Inc. World Headquarters 8001 Irvine Center Drive Irvine, CA 92618
USA
Web site: www.quest.com
Please refer to our Web site for regional and international office information. This User Guide is a printable version of the Foglight online help. In instances where there is conflicting information, the online help supercedes content in this guide .
About the SQLServer Agent
This documentation is for the Foglight SQLServer agent release v3.2.3.
Use the SQLServer agent to monitor the performance of your SQLServer. The SQLServer Agent uses a default set of rules to trigger alerts when specific conditions on your SQLServer occur. You can modify these rules to narrow or broaden the conditions that trigger an alert.
The SQLServer agent monitors SQL Servers running in a Windows environment and a clustered Windows environment. To monitor SQL Servers in a clustered Windows environment you must follow specific installation instructions. For more information, see SQLServer Agent Cluster Handling.
ASP and Procedures
Using SQLServer ASPs
Managing SQLServer Agent Collection Data Selecting a SQLServer Agent Collection Model Overriding default sampling and purging values Excluding databases from being monitored Excluding jobs from the Job table
Editing the Errlog Search Strings list Editing the Errlog Exclusion list Editing the Table ID list
SQLServer Agent Debugging and Troubleshooting SQLServer Agent Cluster Handling
SQLServer Agent Collection Error Return Codes
Investigating Views and Tables
Investigating Data
Investigating Processes and Jobs Investigating Memory Usage Investigating Physical Space Investigating Performance
Rules
Buffer Cache Free Pages Rule Buffer Cache Hit Ratio Rule Cluster Failover Rule Cluster Node Status Rule SQLServer Rule Collection Error SQLServer Rule Collection Timeout Days Since Last Backup Rule Database Status Rule Deadlock Rate Rule Error Log Rule
Last Run Outcome Rule License Limit Rule Login Rate Rule
Log Shipping Failures Rule Maximum Block Time MS Rule Max Server Memory Rule Monitored Databases Rule Procedure Cache Hit Ratio Rule Recompile Rate Rule
Replication Agent Failed Rule Replication Agents Retrying Rule Replication Conflicts Rule
Response Time Rule SQL Long Running Job Rule SQLServer Running Rule SQL Service Checks Trace Poor Performer Rule Worker Threads Rule
Tables
SQLServer Agent Tables
Using SQLServer ASPs
The SQLServer agent is shipped with startup parameters that dictate how the agent will behave. You can change these parameters to suit your particular system requirements. The parameters are grouped into the four topics below..
Use the options on the Connection Details tab to set the SQLServer agent connection parameters. You can specify the following:
• Instance Name
• Use Windows Authentication
• SQLServer user name and password • Work Database Name
• Work Database Location • Cluster Group Name • SQL Connection Timeout
To set the connection details
1. Right-click the SQLServer agent icon and choose Edit, ASPs.
Setting the Connection Details
2. Click the Connection Details tab.
3. Complete the fields:
a) In the Instance Name field, enter the fully qualified name of the instance that the SQLServer agent is to monitor. The format of this entry is the same as that used by the Microsoft SQLServer tools, that is HOST[\INSTANCE].
• HOST is the name of the machine on which the SQLServer instance resides, and is mandatory.
• INSTANCE is optional, and if not specified the agent monitors the default SQLServer instance.
Examples for local and remote monitored instances are shown below. In these examples MYBOX is the local machine where the agent is running, on which an instance named INST2 resides, and OTHERBOX is a remote machine on which an instance named INST4 resides.
• To monitor the default instance on the local MYBOX, enter:MYBOX
• To monitor instance INST2 on MYBOX, enter MYBOX\INST2 • To monitor the default instance on the remote OTHERBOX,
enter OTHERBOX
• To monitor instance INST4 on OTHERBOX, enter OTHERBOX\INST4
Note: You can enter just a period (".") instead of the host name to specify the default instance on the local machine
Note: When working in a clustered SQL Server environment, the Instance Name is the SQL Server cluster's instance name. See Cluster Handling for more information.
b) Select Use Windows Authentication to log in to the SQLServer instance using the same Windows credentials as used by the Foglight Host Service. When using Windows authentication the following two fields (SQLServer User Name and Password) are ignored.
Note: The installation default for the credentials as used by the Foglight Host Service is the BUILTIN\administrator account. Otherwise, clear the Use Windows Authentication flag and then enter an existing SQLServer User Name and SQLServer Password into the following two fields.
Note: This SQLServer user will typically be a DBA user. This user requires:
• create/read/write access for the work and temp databases • read access to master, msdb and all monitored databases • execute permission for xp_cmdshell..
c) Enter the SQLServer User Name if not using Windows authentication.
d) Enter the SQLServer Password if not using Windows authentication.
e) In the Work Database Name field, type the name of the database within the instance that the SQLServer agent is to use as its work area.
Note: The database name must not contain any spaces.
Note: If you have Quest Software's I/Watch product installed, you should not use the same database due to compatibility issues. f) In the Work Database Data Location field, type the directory
path name to be used to store the work database data files. Leave the field blank to use the location of the SQLServer master database.
g) In the Work Database Log Location field, type the directory path name to be used to store the work database log files.Leave the field blank to use the Work Database Data Location specified in the previous field.
h) In the Cluster Group Name field, type the name of the cluster resource group. Leave the field blank if you are not using clusters. Note: The Cluster Group Name is the cluster resource group name as seen in the Windows Cluster Administrator for this cluster. This
is the group name for the group to which the target host belongs, and hence can be seen in the Windows Cluster Administrator under the host node "Active Groups".See Cluster Handling for more details.
i) In the SQL Connection Timeout (s) field, type the timeout value between 30 and 3600 seconds. This is the number of seconds that you are prepared to wait for a connection to the instance. The supplied value is 60.
4. Click OK
Setting the Data Management Parameters
Use the options on the Data Management tab to set the SQLServer agent data managment parameters. Here you can :
• set the sampling frequency for collections
• set the period for purging collected data from tables in the Foglight database
The agent collects data for each table periodically. This period is called the Sample Frequency. The Sample Frequency value might more accurately be described as a sample interval, that is, the elapsed period between collections.
The Foglight server periodically (typically overnight) purges old table data. Data in a table that is older than the Purge Frequency will be purged. The Purge Frequency value might more accurately be described as a retention period, that is, the elapsed period for which data is retained before being purged.
Modifying these parameters will affect the amount of data collected and retained in the Foglight tables, and will also have load implications for your SQLServer instance. For more information see Managing Data.
The sample and purge values in this dialog are 'agent wide' values, that is, they will apply to all collections that do not have overrides. You can set override values for specific collections using the SQL Collection Override list.
To set the data management parameters
2. Click the Data Management tab.
3. Complete the fields:
a) In the Global Sample Frequency field, type how often you want the SQLServer agent to collect data (in seconds). This must be between 30 and 3600 seconds.
b) In the Global Purge Period field, type the purging period (in days). This is how frequently you want data purged from the Foglight tables. Type 9999.if you do not want data purged. c) Click the down-arrow and choose an override table name from the
list.
The agent comes shipped with a number of collection models (override tables). These are based on various hypothetical site requirements and provide you with different reporting and alerting scenarios. They can and should be modified to suit your specific needs. For more information, see Selecting a SQLServer Agent Collection Model.
Alternatively you can create a new override table. In this case simply type the new table name. Then a new table will be provided for you to edit as required. This new table will be based on the contents of the default table SQL_Collection_Overrides_Standard. d) Click the Edit button located next to the Overrides field to edit
the override table. This allows you to set sampling or purging values for specific collections. See Overriding default sampling and purging values.
Setting the Service Checks
Use the options on the Service Checks tab to check for the availability of the following services . • Server Mail • Agent • Agent Mail • DTC • FTS • OLAP
To set the service checks
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Service Checks tab.
3. Enable the services you want checked. The status of checked services is displayed in the Service table. By default, the Check Server Mail option is not selected.
If any of the services are not installed, the SQLServer agent will generate errors to warn you of this. To stop these errors being generated, clear the services that are not installed.
Setting the Collection Parameters
Use the Collection Parameters tab in the SQLServer ASP dialog to set various collection-specific parameters.
To set the collection parameters
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
3. Complete the fields:
a) Select Log agent to debug file if you want additional debugging information about the Foglight SQLServer agent logged.
Information is logged to a file called <agentname>.log. For more information see Debugging and Troubleshooting.
b) In the SQL Statement Timeout (s) field, type the timeout value between 30 and 3600 seconds. This is the number of seconds that you are prepared to wait for any one collection to complete. The default value is 120.
c) In SQL Command To Be Timed field, type the text of the SQL command that you want timed. The time taken by the database to complete this SQL command is displayed in the Response table.
Note • You should not enter an sql statement here that is going to timeout (see SQL Statement Timeout above), otherwise the SQL Response Time rule will not fire as intended.
d) In the Maximum Trace Rows field, type the maximum number of rows to be displayed in the Trace collection table. Reducing the value in this field limits the amount of data collected into the Trace collection table.
Note • The Trace collection is disabled by default. For
information about enabling a collection, see Overriding default sampling and purging values.
e) Set the following four parameters to identify poorly performing (PP) SQL statements reported by the Trace collection. For an SQL statement to be reported as a poor performer, all four parameters must be exceeded.
• In the Trace PP Duration (ms) field, type the number of milliseconds.
• In the Trace PP CPU Usage (ms) field, type the number of milliseconds of CPU usage.
• In the Trace PP Logical Disk Reads field, type the number of reads.
• In the Trace PP Physical Disk Writes field, type the number of writes.
f) Select Trace system objects if you want the Trace collection to include all system objects in the collection. By default these rows are filtered out.
g) Select Trace Quest objects if you want the Trace collection to include all Quest Software's Foglight and Spotlight objects in the sample. By default these rows are filtered out.
h) In the Maximum Blocking Rows field, type the maximum number of rows to be displayed in the Blocking collection table. Reducing the value in this field limits the amount of data collected into the Blocking collection table.
i) Click the Edit button located next to the Errlog Search Strings field to add, modify or remove error strings from the search list. This list specifies the SQLServer error log entries that will be collected by this agent. A comprehensive list of search strings are supplied by default.
j) Click the Edit button located next to the Errlog Exclusion List to
add or remove errorlog search strings from this list. This list specifies the errorlog search strings you want discarded from those collected (see above).
k) Click the Edit button located next to the Database Exclusion List to add or remove databases from this list. Use this to limit the number of databases monitored by the SQLServer agent. By default, the following databases are excluded: tempdb, pubs, Northwind, model. The database exclusion list affects the Database, Filegroup, File and Backup collection tables.
If the Monitored Databases Rule raises an alert, you may use the Database Exclusion List to reduce the number of databases being monitored.
l) Click the Edit button located next to the Table ID List to add or remove table IDs. This list specifies which tables are to be monitored by the TableSize collection table. By default this list is empty.
m) Click the Edit button located next to the Job Exclusion List to add or remove job names. This list specifies the job names to be excluded from the Jobs collection table. By default this list is empty.
Note • You can create your own personalized Secondary ASP lists to suit your requirements. Simply type the name you want to call your list in the relevant secondary ASP
field and click Edit. A new list based on the default list will open, ready for you to edit as required.
4. Click OK.
Selecting a SQLServer Agent Collection
Model
This section describes four collection models shipped with the Foglight SQLServer agent. These models are based on various hypothetical site requirements, and are supplied as override tables (see Overriding default sampling and purging values).
These frequencies can and should be modified as needed to meet the specific alerting and reporting requirements for your particular site, balancing the need for timely alerts with system and Foglight server loads.
SQLServer agent Collection Models
There are four collection models shipped with the SQLServer agent and available for selection. These are:
• Standard: Standard values balancing all general requirements. This is the default model used.
• DBDetail: Concentrating just on real-time DB statistics and performance.
• ProcDetail: Concentrating just on jobs and processes.
• Availability: Concentrating just on the availability of core resources.
To select a Collection Model
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Data Management tab.
3. From the drop-down list next to Overrides select the collection model you want to use.
4. Click OK.
When you elect a model to use, you can vary individual collection intervals within that model to suit your needs (see Overriding default sampling and purging values). You can view some statistical
information about each individual collection in the Managing SQLServer Agent Collection Data topic.
Collection Categories
Each collection is loosely associated with or related to one of the following categories. This is purely an informational grouping only and does not constitute any physical grouping.
• Intrinsic: These collections are concerned with data that is static, essentially static or else derived from the run state of the agent. The customer should not normally adjust the frequencies for these collections.
• Availability: These collections are concerned with availability or core components.
• Database: These collections are concerned with database specifics. • Process: These collections are concerned with job and process
specifics.
• Response: These collections are concerned with user response specifics.
• Statistic: These collections are concerned with various SQL Server statistics that do not fit into other categories. The Cache collection is usually disabled by default due to its high cost.
• Perfinfo:These collections are concerned with raw SQLServer perfinfo counters. These break down into Statistic (AMPerfInfo, BMPerfInfo, MMPerfInfo) and Database (DBPerfInfo) categories. • Diagnosis: These collections are concerned with troubleshooting
specific bottlenecks. Both collections (TableSize and Trace) are disabled by default due to their high cost.
"As-Shipped" Sample Frequencies for Collection Models
CATEGORY (INFO ONLY) COLLECTION TABLE NAME STANDARD MODEL FREQ AVAIL MODEL FREQ DBDETAIL MODEL FREQ PROCDETAIL MODE L FREQ Intrinsic System 86400 86400 86400 86400 SQLConfig 14400 14400 14400 14400
CollectionStatus n/a n/a n/a n/a
Availability Availability 60 60 60 60 DBStatus 300 300 300 0 Service 300 300 0 300 Database DBMonCount 300 300 300 0 Database 300 0 300 0 Filegroup 300 0 300 0 File 300 0 300 0 Backup 3600 0 3600 0 General 300 300 300 0 Process Jobs 300 0 0 300 Errorlog 300 300 0 300 Replication 300 0 0 300 LogShipping 300 0 0 300 Blocking 300 0 0 300 Response Response 300 300 300 300 Connection 300 0 300 300 Statistic Statistic 300 0 300 0 LockRate 300 0 300 300 Memory 300 0 300 0 TopUsers 300 0 300 300 Lock 300 0 300 300 Cache 0 0 0 0
Managing SQLServer Agent Collection Data
The Foglight SQLServer agent is installed with 'global' collection sample frequencies and purging periods as well as specific collection override values.
You can change these sample frequencies and purging periods to suit your specific monitoring and reporting requirements. These may be on long term reporting, monitoring of service availability, general day to day performance monitoring, or some other specific requirements (see more notes about this below).
The agent is shipped with a number of "collection models" which provide a variety of reporting options using combinations of collections and sample intervals. These models can be modified as required. For more information see
Selecting a SQLServer Agent Collection Model.
The following table shows the standard installed default values for Sample Frequency and Purge Period, along with additional information about the volume of data collected by each collection.
Perfinfo AMPerfInfo 300 0 0 0 BMPerfInfo 300 0 0 0 DBPerfInfo 300 0 300 0 MMPerfinfo 300 0 0 0 Diagnosis TableSize 0 0 0 0 Trace 0 0 0 0 COLLECTION TABLE NAME STANDARD SAMPLE FREQUENCY PURGE
PERIOD (D) PER ROWBYTES
ROWS PER COLLECTION AMPerfInfo 300 30 152 1 Availability <=60 30 112 1 Backup 3600 30 248 Varies Blocking 300 30 376 Varies BMPerfInfo 300 30 68 1 Cache 0 30 164 <=20 CollectionStatus * 2 68 1 Connection 300 30 52 1 DBMonCount 300 30 4 1 Database 300 30 180 Varies DBPerfInfo 300 30 148 1 per db
DBStatus 300 30 96 No. of accessible
dbs
Errorlog 300 30 220 Varies
File 300 30 296 Varies
Filegroup 300 30 148 Varies
When changing any of these values you should consider the following factors:
• the amount of space consumed in the Foglight server database by the collections
• how often the collected data is likely to change
• how often you want the associated rules to be evaluated • how far back in time you wish to graph the data
• how fine-grained you wish the graphs to appear • the CPU load (relative expense) of the collection.
For example, if you double a particular sample interval then you will reduce the data volume over time by half, but any rule associated with that collection will not be evaluated until the interval has elapsed, and any graph of that collection data will have double the previous granularity.
Sample intervals should be chosen with these tradeoffs in mind, along with consideration of how often the collected data is likely to change in your environment.
Purging periods should be chosen with regard to your specific reporting requirements, and with consideration of the storage space required by the historical data.
Note • The CollectionStatus collection can help you to determine the relative expense of collections (DurationMS field), and also shows you the actual number of rows stored (RowsStored field).
Note • An overall limit of 4096 rows per collection is enforced to protect against data flooding. This would be a very rare occurrence. The 4096 row limit is really applicable only to the Errorlog, Backup, TableSize and Jobs collections, as all other collections have either a fixed number of
Jobs 300 30 272 Varies Lock 300 30 52 <=6 per db LockRate 300 30 24 1 LogShipping 300 30 4 1 Memory 300 30 84 1 MMPerfinfo 300 30 120 1 Replication 300 30 12 1 Response 300 30 4 1 SQLConfig 14400 30 64 <=43 Service 300 30 96 1 Statistic 300 30 64 1 System 86400 30 100 1 TableSize 0 30 140 Varies TopUsers 300 30 224 10 Trace 0 30 320 Varies
returned rows or have a lower limit. To ensure that this limit is not reached for those four collections, you should ensure that your choice of sample interval is appropriate.
Note • In addition to the volume of data collected per row, note that there is also a 72 byte one-off overhead per table
SQLServer Agent Debugging and
Troubleshooting
There may be times when the Foglight support staff require supplementary information about the SQL Server agent in order to resolve issues that arise. This topic describes how the support staff can obtain such information and is not generally applicable to normal Foglight users unless directed here by the support staff.
ASPs for debugging • Debugging
Set this ASP to true to enable logging to the agent debug log file. You do this by selecting Log to agent debug file on the Collection
Parameters ASP tab.
The agent debug log file provides developers and support engineers with a trace of the execution of the agent executable. The log file resides in the Foglight Client bin directory and is named
<AgentInstanceName>.log, where <AgentInstanceName> is the agent name specified when an instance of the agent is created (that is, with the New->Agent menu option). This agent instance name defaults to SQLServer, so the name of log file is usually
SQLServer.log.
Note1: The agent debug log file will be appended to if it already exists, and is never truncated. Beware of disk space usage!
Note2: This agent uses the existing ErrWorld and agent.log logging paradigms for warning, critical and fatal messages; and (if debugging is enabled) any messages that are sent via those paradigms are also duplicated in the agent debug log file, that is, the agent debug log will contain all debug, warning, critical and fatal messages.
• EncryptStoredProcs
Set this (hidden) ASP to false to be able to inspect/modify the stored procedures within the work database. By default the stored
• DecryptSPFile
Set this (hidden) ASP to false to be able to load an unencrypted databaseobjects.sql file. By default this file is expected to require decrypting at load time. There is also a utility executable called crypt available (contact us) for encrypting or decrypting the
databaseobjects.sql file.
To modify hidden ASPs
Modifying hidden ASPs should be done with care and only on the specific instructions of a Quest support engineer.
1. Stop the existing agent and delete it.
2. From the Foglight console menu bar, select Tools and then Agent Browser.
3. Expand the Database node and select SQLServer. 4. Expand the ASP node and select SQLServer.
5. Select the ASP to be modified and change the default value appropriately.
6. Save the changed ASP.
Any new agent will now get the changed ASPs.
Agent performance issues
• Memory: 6.7 Mb (approximately) • Handles: 150 (approximately)
• CPU: Most of the time the agent is actually idle and waiting. When it is performing a particular collection it will then use some more CPU for the duration of the collection. Therefore the CPU usage is seen as a series of spikes. The amount of work that the agent has to do will depend mostly on:
• the collection sample frequencies
• the number of databases (within the instance) being monitored
• the size of (that is, the number of objects contained in) each DB
• the frequency of events that cause alerts to be raised. Performance testing on a heavily loaded 4 CPU system running SQL Server 2000 showed that the majority of collections were finished within 5 seconds. On a lightly loaded system, the figures are much lower.
Note • The Cache and Trace collections are turned off by default (that is, their sample frequency is set to zero) due to their large relative processing expense. For the same reason the TableSize collection is scheduled to only run once every 4 hours.
Work database issues
• You can force the stored procedures to be reloaded by deleting the work database (using the SQLServer Enterprise Manager), and then (re)starting the agent.
• Be aware that if Quest Software's I/Watch product is installed, the SQLServer agent must not use the same work database due to compatibility issues.
• The transaction log file for the work database will eventually fill up, causing collections to fail with the MS SQLServer 9002 error, unless you perform periodic backups, or periodically drop the database. • The collation of the work database must have the same collation as
that of the master database. A work database that is created by the agent will have the correct collation. However, if you specify the name of an existing database to use as the work database, you must ensure that its collation matches that of the master database. Collation conflicts cause collections to fail with the MS SQLServer 446 error. • To find out what collations are being used for each database, use the
MS SQLServer Query Manager (or similar query execution tool) to execute sp_helpdb. You will see the database's collation in the (very wide) column called status.'
• It is important that you perform database backups regularly to reduce the size of the database transaction logs. The transaction log for the Foglight work database will grow over time. If you also have Spotlight on SQLServer installed this transaction log may grow very quickly. • Periodic maintenance of the work database (assuming it is called
QuestSoftware) should include the following, after first stopping the agent and ensuring no users are accessing QuestSoftware.
• USE QuestSoftware; CHECKPOINT
• BACKUP LOG QuestSoftware WITH TRUNCATE_ONLY • DBCC SHRINKDATABASE( QuestSoftware, TRUNCATEONLY)
Other useful information for debugging and tuning
• The agent log contains user-oriented messages about the status of the agent. This is not to be confused with the agent debug log file described earlier. An agent may have a status of Running, Stopped, or Broken. Viewing the agent log can help you understand why the
agent is in a certain state. To view the agent log, right-click on the agent and select Show Log.
• The CollectionStatus Table provides some useful agent debugging and tuning information.
• Individual collections can be disabled by setting their sample frequency to zero. This may help you to isolate a problematic collection. See Overriding default sampling and purging values. • The Connection ASP tab (see page 3) includes an SQL Connection
Timeout ASP that you can set anywhere between 30 and 3600 seconds. The default setting is 60 seconds.
• The Collection Parameters ASP tab (see page 9) includes an SQL Statement Timeout ASP that you can set anywhere between 30 and 3600 seconds. The default setting is 120 seconds. Note that in high SQL load situations, a collection may fail due to contention for SQL resources. Although this is not exactly the same as an SQL statement timeout, it will be interpreted and acted upon exactly the same as if it were an SQL statement timeout.
• The DBCC UPDATEUSAGE utility is usually run by the DBA after operations such as backups or index reorgs. This ensures that the internal statistics are reinitialized. It corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes (this size information is not maintained for nonclustered indexes). If this utility is not run then these statistics can be incorrect and this may falsely trigger alerts. For information about the DBCC UPDATEUSAGE utility, refer to the SQLServer
Books Online help system.
Faulty performance counters
For a variety of reasons outside of the Foglight agent's control, the SQL Server performance counters (in the master.dbo.sysperfinfo table) may be
inaccessible, incomplete or defective.
During the System collection (performed at agent startup), the
master.dbo.sysperfinfo table is checked and if found to be faulty, a warning is sent to the agent log.
Faulty performance counters will result in problems for the following collections:
• AMPerfInfo (access method): No rows are returned. • BMPerfInfo (buffer manager): No rows are returned. • DBPerfInfo (database): No rows are returned.
• LockRat: No rows are returned.
• Memory All columns except BufferCacheHitRatio, ProcCacheActiveMB and ProcCacheHitRatio will be zero.
• Statistic All columns will be zero.
Usually the missing performance counters can be restored on SQLServer by performing the following steps:
• Open a command prompt in the SQLServer Binn directory for example, C:\Program Files\Microsoft SQL Server\MSSQL\Binn • Determine the drivername from the sqlctr.ini file by running:
indstr drivername sqlctr.ini
Typically the drivername will be 'MSSQLServer' for unnamed instances.
• Unload the performance counters by running: unlodctr MSSQLServer
Substitute your drivername here as appropriate • Reload the performance counters by running:
lodctr sqlctr.ini
• Restart the SQLServer instance.
Note • These steps are for non-clustered SQL Servers only.
You can check for successful unloading and loading messages in the application log within the Windows Event Viewer. If you get an unexpected error message, look it up at msdn.microsoft.com.
In the event that the above process does not remedy the missing performance counters, see article 227662 in the Microsoft Knowledge Base for an alternative possible solution.
Log shipping failure alerts
Log shipping creates jobs that are to be run on both the source and destination SQLServer. The agent residing on the SQLServer Log Shipping Monitor Server raises an alert if it detects that log shipping has failed. Agents residing on the source or destination server also raise alerts for any particular log shipping job that may have failed on that server.
Multiple repeating entries may also be written to the SQLServer Error Log by the SQLServer Log Shipping Monitor Server, and so the agent may raise multiple alerts for the one situation. You may therefore wish to filter out this particular class of error log message from the ErrorLog collection. For filtering instructions see Editing the Errlog Search Strings list.
SQLServer Agent Cluster Handling
The Foglight SQLServer Agent monitors the involvement of the host node within a cluster, and also monitors the role of the SQLServer instance within the cluster.
Installation
In a clustered scenario, all the SQLServer instances that are accessible from the cluster nodes should be monitored. To achieve this, install the following on each node in the cluster:
• The Foglight Host Services (RAPSD).
• A Foglight SQLServer Agent for every SQLServer instance that is installed within the cluster.
The Foglight Host Services and the Foglight SQLServer Agent must be installed on the node's local disk, rather than on the cluster disk array where the SQLServer instance is typically installed.
We recommend that you configure the Cluster Service to start before the Foglight Host Service. If the Foglight Host Service is configured to start automatically at boot time (which is the default) then it is possible that it may start up before the Clustering Service, causing the agent to report the
SQLServer instance status incorrectly.
Before starting each agent for the first time, do the following:
1. Select the Connection Details ASP tab.
2. In the Instance Name field, enter the clustered SQLServer's instance name.
3. Note: This must be a local instance name as the cluster state cannot be monitored remotely.
In the Cluster Group Name field, enter the name of the cluster resource group that the SQLServer is in.
Note • The Cluster Group Name is the cluster resource group name as seen in the Windows Cluster Administrator for this cluster. This is the group name for the group to which the target host belongs, and hence can be seen in the Windows Cluster Administrator under the host node "Active Groups".
Some common cluster scenarios and IP Map examples Active/Passive:
• One SQLServer instance is installed within the cluster.
• The instance on Box1 is active and the instance on Box2 is passive. • In the event of Box1 failing, Box2 will become active.
• The agent running on Box1 is collecting data from the active SQLServer instance on Box1.
• The agent running on Box2 is in quiescent mode, only monitoring the status of the passive SQLServer instance on Box2.
Active/Active:
• Two separate SQLServer instances are installed within the cluster so that each node has one active SQLServer instance. This is commonly done to share workloads between the two nodes.
• Both nodes and both SQLServer instances are normally active. • If one node fails then the SQLServer instance on it is ‘failed over’ to
the other node.
• The SQLServer_1 agent running on Box1 is collecting data from the active SQLServer instance on Box1.
• The SQLServer_2 agent running on Box1 is in quiescent mode, only monitoring the status of the passive SQLServer instance on Box1. • The SQLServer_1 agent running on Box2 is in quiescent mode, only
monitoring the status of the passive SQLServer instance on Box2. • The SQLServer_2 agent running on Box2 is collecting data from the
active SQLServer instance on Box2.
Operational notes
• If an SQLServer instance is active on a clustered node, the agent (residing on that same node) performs normal monitoring of the instance.
• If an SQLServer instance is inactive on a clustered node, the agent (residing on that same node) only monitors the cluster status of the instance, and does not perform any SQL dependent collections. The agent is in ‘quiescent mode’.
• In the event of a failover, the agent on the failed node issues an alert and then enters quiescent mode, whilst the agent on the newly activated node takes over the normal monitoring of the (now active) SQLServer instance.
• As described above, in the event of a failover, the agent does not shutdown and then restart on another node in the cluster.
Associated tables and rules
• The Availability collection table shows whether the SQLServer instance is running, and its role within a cluster.
• The Cluster Failover rule issues a fatal alert when the cluster resource group becomes inactive within the cluster. The alert raised shows the name of the newly active node and issues an email advice.
• The Cluster Node Status rule issues a warning alert when the cluster resource group becomes inactive within the cluster. The alert raised shows the name of the newly active node.
• When clustered, the SQL Server Running rule issues a fatal alert if the cluster resource group's role is Active but the cluster resource group is not fully online (as seen by the Windows Cluster Manager).
SQLServer Agent Collection Error Return
Codes
These are the possible error return codes from an SQLServer agent collection.
Note • The error codes -2, -3, -5 and -7 may sometimes be returned when a timeout or contention has occurred during the execution of the query. However the agent is unable to discriminate between this event and other possible causes, so should these error codes persist in
subsequent collections please contact Quest Support at [email protected]
Editing the Errlog Search Strings list
Use this list to specify which SQLServer errorlog entries are to be alerted on. By default, this list contains all the anticipated strings, and the standard severities 1 through 25. You can add, delete or edit strings and assign the relevant severity so that Foglight raises an alert when these errors are logged by SQLServer.
The Errorlog Exclusion List secondary ASP allows you to specify errorlog strings that you want discarded from the returned list.
To edit the error log search strings list
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
CODE NOTES
0 Successful execution and collection.
-1 Internal software error. The SQLServer agent will shut down. In the rare event that this should occur, contact Quest Support at [email protected] -2 No rowset returned. The agent will retry on the next scheduled collection. -3 invalid rowset returned. The agent will retry on the next scheduled collection. -4 A serious sql error suggesting that the connection has failed. The agent will
then attempt to re-establish a connection to the SQLServer.
-5 An ordinary sql error. The agent will retry on the next scheduled collection. -6 A timeout. The agent will retry on the next scheduled collection.
-7 An unexpected number of rows returned. The agent will retry on the next scheduled collection.
3. Click the Edit button located next to the Errlog Search Strings field.
To add an errorlog string:
a) Click New.
b) The Add dialog opens.
c) In the String field, type the search string.
Note • Error log entries that match the search string anywhere in the entry will be alerted on.
Note • The search string must not contain any single quote or pipe characters, and wildcards are not accepted.
d) From the Severity list, select a severity. e) Click OK.
f) If you want to add another error string, click Apply and then repeat steps c to e. If you are finished, click OK.
To edit a string
a) Select a string from the list. b) Click Edit.
d) Click OK.
To delete a string:
a) Select a string from the list. b) Click Delete.
c) Confirm the deletion by clicking Yes. d) Click OK.
Editing the Errlog Exclusion list
You can specify a list of errorlog search strings to be excluded from the Errorlog collection table using the Errlog Exclusion List on the Collection Parameters tab.
Each string in the list is searched for and located wherever it appears in the error log entry. These strings are then removed from the strings returned by the Errlog Search String collection.
To edit the error log string exclusion list
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
3. Click the Edit button located next to the ErrLog Exclusion List.
To add a string to the exclusion list:
a) Click New.
b) The Add dialog opens.
c) In the Errorlog Exclusion String field, type the string you want to exclude.
Note • When typing a string to be excluded, you must use the correct case if the SQLServer instance has been installed as case-sensitive.
d) If you want to exclude another string, click Apply and then repeat step b. If you have listed all the strings you want to exclude, click OK.
To delete a string from the list:
a) Select the name of the string you want to delete. b) Click Delete.
c) Click Yes to confirm deletion. d) Click OK.
To edit a string in the list:
a) Select the name of the string you want to edit. b) Click Edit.
c) In the Errorlog Exclusion String field, modify the string. d) Click OK.
Excluding databases from being monitored
You can stop the SQLServer agent to from monitoring specific databases by adding the database name to the Database Exclusion list. By default, the following databases are excluded: Tempdb, Pubs, Northwind, Model.
The Database Exclusion List is utilized by the DBMonCount, Database, Filegroup, File, General and Backup collection tables.
If the Monitored Databases Rule is activated, you can use the Database Exclusion List (see page 27) to reduce the number of databases being monitored.
To edit the Database Exclusion list
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
To add a database to the list:
a) Click New.
b) The Add dialog box opens.
c) In the Database Name field type the name of the database you want to exclude.
Note • When typing a database name to be excluded, you must use the correct case if the SQLServer instance is case-sensitive.
Note • Names entered in the DB Exclusion List may contain wildcards. The names are used in a 'LIKE' comparison within the stored procedure, so the can contain wildcards as documented in Microsoft® SQL Server™. Transact-SQL reference help for 'LIKE'. For example, '%test' will match any database name ending with 'test'.
d) If you want to add another database, click Apply and then repeat step c. When you have added all the databases you want to exclude, click OK.
To delete a database from the list:
a) Select the name of the database you want to delete. b) Click Delete.
c) Click Yes to confirm deletion. d) Click OK.
Editing the Table ID list
Use this option to specify which tables are to be monitored by the SQL Server agent. Information about tables is displayed in the TableSize table, Table Size graph, and Table Row Count graph.
Note • Important: By default, no tables are monitored for tablesize data due to the high SQLServer load cost. You must specify those tables to be monitored for tablesize via this Table ID List. before any data is collected.
To edit the Table ID list
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
3. Click the Edit button located next to the Table ID List field.
To add a table ID:
a) Click New.
b) The Add dialog opens.
c) In the Table ID field, type the table ID. You must enter a fully qualified table name in the format:
databasename.databaseowner.tablename.
d) If you want to add another table ID, click Apply and then repeat step b. If you are finished, click OK.
To edit a table ID:
a) Select a table ID from the list. b) Click Edit.
c) Make your changes. d) Click OK.
To delete a table ID:
a) Select a table ID from the list. b) Click Delete.
c) Confirm the deletion by clicking Yes. d) Click OK.
Excluding jobs from the Job table
You can specify a list of job names to be excluded from the Jobs collection table using the Job Exclusion List on the Collection Parameters tab (see page 9).
To edit the Job Exclusion list
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Collection Parameters tab.
3. Click the Edit button located next to the Job Exclusion List.
To add a job to the exclusion list:
a) Click New.
b) The Add dialog opens.
c) In the Job Name field, type the name of the job you want to exclude.
Note • When typing a job name to be excluded, you must use the correct case if the SQLServer instance has been installed as case-sensitive
Note • A trailing asterisk (*) wildcard is allowed in the Job Names. For example 'myjob*' will match any job name starting with 'myjob'.
d) If you want to exclude another job, click Apply and then repeat step b. If you have listed all the job names you want to exclude, click OK.
To delete a job from the list:
a) Select the name of the job you want to delete. b) Click Delete.
c) Click Yes to confirm deletion. d) Click OK.
To edit a job in the list:
a) Select the name of the job you want to edit. b) Click Edit.
c) In the Job Name field, modify the job name. d) Click OK.
Overriding default sampling and purging
values
Default (global) values for sampling and purging are set in the Data
Management ASP (see page 6) . The global values apply to all collections except where overridden for individual collections as described below. Frequencies should only be modified after due consideration of the impact on the agent's performance (see Managing SQLServer Agent Collection Data). Note that some collections are disabled out-of-the-box for performance reasons.
To override the global sampling and purging values for a particular collection
1. Right-click the SQLServer agent icon and choose Edit, ASPs. 2. Click the Data Management tab.
To set values for a collection:
a) Click New.
b) The Add dialog opens.
c) In the Collection Name field, type the name of the collection you want to set sampling and purging values for.
d) In the Sample Frequency field, type a sampling interval (in seconds). Setting the sample frequency to zero will disable the collection.
e) In the Purge Period field, type a data retention period (in days). f) If you want to add another collection, click Apply and then repeat
steps c-e. If you are finished, click OK.
To edit values for a collection:
a) Select a collection from the list. b) Click Edit.
c) Edit the values. d) Click OK.
To delete values for a collection:
a) Select a collection from the list. b) Click Delete.
c) Confirm the deletion by clicking Yes. d) Click OK.
SQLServer Agent Tables
The SQLServer agent collects and stores data in the following tables in the Foglight database. Click the hyperlinks to view the field descriptions for the tables. See Managing SQLServer Agent Collection Data for the installed sample frequency and purge frequency values.
AMPerfInfo Table Availability Table
Backup Table Blocking Table (renamed Blocking_323) BMPerfInfo Table Cache Table
CollectionStatus Table Connection Table Database Table (renamed Database_323) DBMonCount Table DBPerfInfo Table DBStatus Table
Note • In this agent release some collection tables have been superseded. The new versions of these tables are listed below, and it is these new tables that are populated and used in rules and views by this agent. Although this documentation may refer to the old table names, the
documentation actually applies to the new tables.
The older versions of these tables (that is, without the _323 suffix), are retained in this agent solely for compatibility with earlier agent versions. The old tables are not populated or otherwise used by this agent.
Filegroup Table (renamed Filegroup_323) General Table (renamed General_323) Jobs Table (renamed Jobs_323) Lock Table
LockRate Table LogShipping Table Memory Table MMPerfInfo Table Replication Table Response Table Service Table SQLConfig Table Statistic Table System Table
TableSize Table TopUsers Table (renamed TopUsers_323) Trace Table
Blocking renamed Blocking_323 Database renamed Database_323 ErrorLog renamed Errorlog_323 File renamed File_323 FileGroup renamed Filegroup_323
General renamed General_323
Jobs renamed Jobs_323 TopUsers renamed TopUsers_323
AMPerfInfo Table
This table stores access method performance metrics collected from the sysperfinfo table.
The following describes the data returned by the AMPerfInfo collection.
FIELD DESCRIPTION
ExtentDeallocationsPS Number of extents deallocated from database objects per second.
ExtentsAllocatedPS Number of extents allocated to database objects for storing index or data records per second.
ForwardedRecordsPS Number of records fetched through forwarded record pointers per second.
FreeSpacePageFetchesPS Number of pages returned by free space scans to satisfy requests to insert record fragments per second.
FreeSpaceScansPS Number of scans initiated to search for free space to insert a new record fragment per second.
FullScansPS Number of unrestricted full scans per second. These can either be base table or full index scans.
IndexSearchesPS Number of index searches per second. Index searches are used to start range scans, single index record fetches, and to reposition within an index.
MixedPageAllocationsPS Number of pages allocated from mixed extents per second. Used for storing the first eight pages allocated to an index or table.
PageDeallocationsPS Number of pages deallocated per second.
PageSplitsPS Number of page splits occurring per second as the result of index pages overflowing.
PagesAllocatedPS Number of pages allocated to database objects per second for storing index or data records.
ProbeScansPS Number of probe scans per second. A probe scan is used to directly look up rows in an index or base table.
RangeScansPS Number of qualified range scans through indexes per second.
ScanPointRevalidationsPS Number of times per second the scan point had to be revalidated to continue the scan.
For more information, see the SQLServer Performance Counters section in the
SQLServer Agent Debugging and Troubleshooting topic.
Availability Table
This collection determines if the SQLServer is running, and its role within a cluster.
The default sampling frequency is 1 minute. However this table is also collected whenever the state changes, so the collection may occur more frequently than this.
The following describes the data returned by the Availability collection. SkippedGhostedRecordsPS Number of ghosted records skipped during scan per
second.
TableLockEscalationsPS The number of times locks on a table were escalated per second.
WorkfilesCreatedPS Number of workfiles created in the last second. WorktablesCreatedPS Number of worktables created in the last second. WorktablesFromCacheRatio Percent of worktables created where the initial pages
were available in the worktable cache.
FIELD DESCRIPTION
InstanceName The fully qualified name of the instance being monitored.
IsClustered Is set to true if the SQLServer part of a clustered environment.
SQLServerActive If IsClustered then this will be set to true if the Windows Cluster Manager considers this cluster resource group to be the currently active cluster resource group.
If not IsClustered, this is irrelevant and set to true.
Note • In a clustered environment, a 'Passive' SQLServer instance may be offline (depending on site architecture). In this situation, the agent will just poll for changes in the cluster state (ie, perform the Availability collection), and will not perform rest of the collections (ie, those other collections that require the execution of TRANSACT-SQL commands). For more information see SQLServer Agent Cluster Handling.
SQLServerRunning If IsClustered then this will be set to true if the Windows Cluster Manager considers that this cluster resouce group is fully online (and so the SQLServer process is running).
If not IsClustered, this simply indicates whether the SQLServer process is running
WindowsClusterName If IsClustered then this is the name of the Windows Cluster on which the clustered instance is running
If not IsClustered, this is irrelevant and set to blank.
ActiveNodeName The name of the physical Windows machine (node) that is currently active within the cluster.
If not IsClustered, this is irrelevant and set to blank.
SQLServerFailover If IsClustered then this is set to true if
SQLServerActive transitions from true
to false.
Therefore this will not be set to true when an agent starts up on an inactive node in a cluster.
If not IsClustered, this is irrelevant and set to false.
Backup Table
This collection returns backup history for each database being monitored. You can limit the databases being monitored by using the Database Exclusion List..It utilizes database information stored in the DBMonCount collection and is also intimately related to the Database, Filegroup, File, and General collections.
The first collection scans from the "current" position within the backup history, that is, the current time minus the backup collection sample frequency. Subsequent collections scan onward from the last record scanned, that is, only records that have been added are returned in subsequent collections.
The following describes the data returned by the Backup collection.
Blocking Table (renamed Blocking_323)
This collection returns information regarding blocking and blocked processes.
The rows are sorted into groups, with each group consisting of a blocking process row followed by the blocked process row(s). Blocked processes are those that are contending for a resource (lock) that is already held by the blocker. Below is an example of such a group.
You can limit the amount of data collected by using the Maximum Blocking Rows limit as set in the Collection Parameters, however this is rarely needed as in most systems the number of blocking rows collected will be zero or small. Note that a partial group may be collected if the Maximum Blocking Rows limit is reached mid-group.
FIELD DESCRIPTION
DBName The name of the database. Device The name of the backup device. FinishTime The time that the backup finished. SizeMB The size of the backup in megabytes. StartTime The time that the backup started.
The following describes the data returned by the Blocking collection.
Note • For further details regarding processes and lock resources, refer to the sysprocesses and syslockinfo system table and related topics in the Microsoft® SQL Server" Books Online.
FIELD DESCRIPTION
SPID The ID of the blocking or blocked process.
Type The lock type. For example, Database, Table, Page, Row, Key, Extent, etc.
Status The lock status (Granted, Converting or Waiting). Mode The lock mode, for example, Shared, Exclusive, Update,
IntentShared, IntentExclusive, etc.
Resource A textual representation of the resource. This usually shows the database and table name. If the resource cannot be determined, this will be blank.
BlockerSPID The ID of the blocking process. For a blocking process this is shown as zero.
UserName The user name associated with this process. Program The name of the application program. For example,
Microsoft Access.
SQLCommand The SQL command being executed. If the SQL command cannot be identified, this will be blank.
CPU The CPU time (in ms) consumed. PhysicalIO The IO (in pages) consumed.
WaitTime(ms) The time (ms) that this process has been waiting for the resource. For a blocking process this is shown as zero.
BMPerfInfo Table
This table stores buffer manager performance metrics collected from the sysperfinfo table.
The following describes the data returned by the BMPerfInfo collection..
For more information, see the SQLServer Performance Counters section in the
SQLServer Agent Debugging and Troubleshooting topic.
Cache Table
The Cache collection returns the Top 20 tables in the Buffer Cache, that is, the tables that are using most of the Buffer Cache.
By default, this collection is disabled (with a sample frequency of 0) due to the high cost of collecting this data. To find out how to set a sample frequency for the cache collection, see Overriding default sampling and purging values.
FIELD DESCRIPTION
BufferCacheHitRatio Percentage of pages that were found in the buffer pool without having to incur a read from disk. CheckpointPagesPS Number of pages flushed by checkpoint or other
operations that require all dirty pages to be flushed. LazyWritesPS Number of buffers written by buffer manager's lazy
writer.
PageReadsPS Number of physical database page reads issued per second.
PageRequestsPS Number of requests per second to find a page in the buffer pool.
PageWritesPS Number of physical database page writes issued per second.
ProcedureCachePages Number of pages used to store compiled queries. ReadAheadPagesPS Number of pages read in anticipation of use. ReservedPages Number of buffer pool reserved pages.
StolenPages Number of pages used for miscellaneous server purposes (including procedure cache).
The following describes the data returned by the Cache collection.
CollectionStatus Table
This table collection contains a summary of all the collections for agent tuning and debugging purposes. This table is updated after every individual collection.
See SQLServer Agent Collection Error Return Codes for descriptions of the possible error values.
The following describes the data returned by the CollectionStatus collection.
Note • In high SQL load situations, a collection may fail due to contention for SQL resources. Although this is not exactly the same as an SQL statement timeout, the Error field will be shown as an SQL statement timeout. The DurationMS field may be less than the SQLStmtTimeout value in these cases.
FIELD DESCRIPTION
DBName The name of the database. TBName The name of the cached table. IXName The name of the index. PercentageofCache The percentage of cache used. PercentageofObject The percentage of object in cache. Pinned Is the table pinned in memory?
PinnedSizeKB The size of the table (Kb) that is pinned in memory. SizeInCacheKB The size of the object (Kb).
TBOwner The name of the table owner.
FIELD DESCRIPTION
CollectionName The name of the collection.
DurationMS The duration of the last run in milliseconds. RowsStored The number of rows stored in the last run. Error The error reported in the last run.
Connection Table
This collection returns summary information of the current SQLServer connections.
The following describes the data returned by the Connection collection.
FIELD DESCRIPTION
ActiveUserConnectionCnt The total number of active user (non-system) connections.
ClientMachineCnt The total number of hosts connected to the SQLServer.
ConnectionCnt The total number of connections.
InactiveConnectionCnt The total number of inactive connections. LicenseConcurrentLimit If the LicenseMode is 1 then this is the
number of client connections allowed. If the LicenseMode is 0, this will always be 0. (See LicenseMode below.) LicenseCurrentCount The number of clients currently
connected.
If LicenseMode is 0, this will always be 0. (See LicenseMode below.)
LicenseMode The license mode of the SQLServer installation.
1 = licensing per server 0 = licensing per seat
Note: The 'per server' mode is available
for SQLServer 7 only.
LoginRate The number of logins per second. MaxBlockTimeMS The longest wait time of all currently
blocked processes.
MaxWorkerThreads The configured maximum worker threads.
SystemConnectionCnt The total number of system connections. UserConnectionCnt The total number of users (non-system)
Database Table (renamed Database_323)
This collection returns details about the database size, status and other summary information. You can limit the databases being monitored by using the Database Exclusion List. It utilizes database information stored in the
DBMonCount collection and is also intimately related to the Filegroup, File, General and Backup collections.
The following describes the data returned by the Database collection.
DBMonCount Table
This collection returns information about the number of databases that the agent is currently monitoring. As this number grows, then the amount of data that is collected by the agent increases.
It builds an internal list of all databases being monitored by the agent but ignores any databases on the agent's Database Exclusion List and any that are
unreadable or inaccessible. This list is also used by the Database, Filegroup, File, Backup and General collections.
