MS SQL DB Servers Monitoring

MS SQL Server - An Overview

Applications Manager's SQL Server monitoring feature helps database administrators monitor the performance and availability of production databases. It is an agentless monitoring solution that provides out-of-the-box performance metrics making sure that the SQL server runs efficiently.

Creating a new MS SQL database monitor

Supported Versions - Applications Manager supports monitoring of MS SQL 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019 and 2022 versions.

Prerequisites to create a new monitor: Click here

To create a MS SQL database server Monitor, follow the given steps:

  1. Click on New Monitor link.
  2. Select MS SQL DB Server.
  3. Enter the IP Address or Hostname of the host. To add MS SQL as a cluster type monitor, enter the Virtual IP Address or the Listener IP Address of the cluster.
  4. Enter the subnetmask of the network.
  5. Enter the Port number in which the MS SQL is running.
  6. Enter the polling interval time in minutes.
  7. If you are adding a new monitor from an Admin Server, select a Managed Server.
  8. Provide the User Name and Password of user who has permission to access the MS SQL database. The user name specified for collecting the data from MS SQL Server should have either System Administrator role or the user should be the DB owner for master database. Alternatively, you can provide the Windows Authentication details (give the User Name like domainname\username) also.
  9. Select the Enable Kerberos Authentication checkbox, if you want to monitor MS SQL server through Kerberos authentication.
  10. If you want to connect using a Named Instance, check the Connect using Named Instance checkbox and specify the instance name.
  11. Choose Force Encryption option if the server is configured for encrypted connections.
  12. Choose jTDS JDBC Driver or Microsoft JDBC Driver option in Driver for SQL Server Connection field to choose the required driver for connecting to the database for data collection.
  13. Choose the Monitor Group from the combo box with which you want to associate MS SQL database server Monitor (optional). You can choose multiple groups to associate your monitor.
  14. Click Add Monitor(s). This discovers MS SQL database server from the network and starts monitoring them.

Note: Applications Manager's SQL Server Performance monitoring allows you to enable/disable data collection of critical SQL metrics like Jobs, Backup and Replications. Once enabled, you can also customize the polling interval of these metrics.

Monitored Parameters

Go to the Monitors Category View by clicking the Monitors tab. Click on MS SQL under the Databases Table. Displayed is the MS SQL bulk configuration view distributed into three tabs:

  • Availability tab gives the Availability history for the past 24 hours or 30 days.
  • Performance tab gives the Health Status and events for the past 24 hours or 30 days.
  • List view enables you to perform bulk admin configurations.

Visit our page on SQL performance tuning where you can get detailed information on the parameters used to optimize your SQL database and how to monitor them using Applications Manager.

Provided below is the list of different metrics monitored with SQL monitoring. Click on the monitor name to see all the MS SQL Database metrics listed under the following tabs:

Overview

Parameters Description
Name The name of MS SQL server monitor.
Health The health (Clear, Warning, Critical) of the MS SQL server.
Type The type you are monitoring.
Version The version of the database server.
ODBC Driver Version The ODBC driver version used.
Instance Name Name of the SQL instance
Port The port number at which the database server is running.
Host Name The host at which the database server is running.
Host OS The OS of the host where the database server is running.
Last Alarm The last alarm that was generated for the database server.
Last Polled at The time at which the last poll was performed.
Next Poll at The time at which the next poll is scheduled.
Availability Shows the current status of the server - available or not available.
Buffer Cache Hit Ratio Percentage of pages found in the buffer cache without having to read from disk. If Buffer Cache Hit Ratio value is “high”, then SQL Server is efficiently caching the data pages in memory, reads from disk are relatively low, and so there is no memory bottleneck. It should be > 90% for a well performing server.
Plan Cache Hit Ratio This metric measures how much the plan cache is being used. A high percentage here means that your SQL Server is not building a new plan for every query it is executing so is working effectively and efficiently. A low percentage here means that for some reason, the SQL Server is doing more work than it needs to.
CPU AND MEMORY USAGE
Total Memory The total amount of dynamic memory the server is currently consuming.
SQL Cache Memory The total amount of dynamic memory the server is using for the dynamic SQL cache.
Lock Memory The total amount of dynamic memory the server is using for locks.
Optimizer Memory The total amount of dynamic memory the server is using for query optimization.
Connection Memory The total amount of dynamic memory the server is using for maintaining connections.
Granted WorkSpace Memory The total amount of memory granted to executing processes. This memory is used for hash, sort and create index operations.
Memory Grants Pending The current number of processes waiting for a workspace memory grant.
Memory Grants Success The current number of processes that have successfully acquired a workspace memory grant.
CPU Utilization The percentage of CPU used by the SQL server instance.
BUFFER MANAGER STATISTICS
Buffer Hit Ratio The percentage of pages that were found in the buffer pool without having to incur a read from disk.
Page LookUps/Min The number of requests to find a page in the buffer pool.
Page Reads/Min The number of physical database page reads issued.
Page Writes/Min The number of physical database page writes issued.
Total Pages The number of pages in the buffer pool (includes database, free, and stolen).
Database Pages The number of pages in the buffer pool with database content.
Free Pages The total number of pages on all free lists.
Page Life Expectancy The number of seconds a page will stay in the buffer pool without references.
ACCESS METHOD DETAILS
Full Scans/Min The number of unrestricted full scans. These can either be base table or full index scans.
Range Scans/Min The number of qualified range scans through indexes.
Probe Scans/Min The number of probe scans. A probe scan is used to directly look up rows in an index or base table.
Work Files Created/Min Displays the number of work files created per minute. These work files could be used to store temporary results for hash joins and hash aggregates.
Work Tables Created/Min Displays the number of work tables created per minute. These work tables could be used to store temporary results for query spool, LOB variables, XML variables, and cursors.
Free Space Scans/Min Displays the number of scans per minute that were initiated to search for free space within pages already allocated to an allocation unit to insert or modify record fragments.
Lazy Writes/Min Displays the number of buffers written by buffer manager's lazy writer per minute.
Scan point Revalidation/Min Displays the number of times the scan point had to be revalidated to continue the scan.
Index Searches/Min Displays the number of index searches per minute. These index searches are used to start range scans, single index record fetches, and to reposition within an index.
CACHE DETAILS
Plan Cache Hit Ratio This metric measures how much the plan cache is being used. A high percentage here means that your SQL Server is not building a new plan for every query it is executing so is working effectively and efficiently. A low percentage here means that for some reason, the SQL Server is doing more work than it needs to.
Total Log Cache Hit Ratio Displays the overall total percentage of log cache reads that were satisfied from the log cache.
Cache Used/Min Displays the number of times each type of cache object has been used.
Cache Count Displays the number of cache objects in the cache.
Cache Pages Displays the number of 8k pages used by cache objects.
LOCK DETAILS
Lock Requests/Min The number of new locks and lock conversions requested from the lock manager.
Lock Waits/Min The total wait time for locks in the last minute.
Lock Timeouts/Min The number of lock requests that timed out. This includes internal requests for NOWAIT locks.
Deadlocks/Min The number of lock requests that resulted in a deadlock.
Average Lock Wait Time The average amount of wait time for each lock request that resulted in a wait.
LATCH DETAILS
Latch Waits/Min The number of latch requests that could not be granted immediately and had to wait before being granted.
Average Latch Wait Time The average latch wait time for latch requests that had to wait.

Performance

Parameters Description
TOP QUERIES BY CPU
Avg. CPU Time Average CPU Time taken to execute the query.
Total CPU Time The cumulative total amount of CPU time that has been spent running this query since the plan was compiled.
Query The SQL Query Text.
DB Name The database against which this query has been executed.
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
TOP QUERIES BY I/0
Avg. Logical I/O Average number of logical reads and logical writes performed by executions of this query.
Total I/O The cumulative total number of logical reads and logical writes performed by executions of this query since the plan was compiled.
Query The SQL query text.
DB Name The Database against which this query has been executed (will be empty for ad hoc and prepared batches).
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
TOP QUERIES BY CLR (Top 10 queries that have high CLR time (Common Language Runtime) inside the Microsoft .NET Framework)
Average CLR Average Time consumed inside Microsoft .NET Framework Common Language Runtime (CLR) for executions of this query.
Total CLR The cumulative Total Time consumed inside Microsoft .NET Framework Common Language Runtime (CLR) objects by executions of this query since the plan was compiled.
DB Name The Database against which this query has been executed.
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
Top Waits by Waiting Tasks
Wait Type Indicates the name of the wait type.
Waiting Tasks Displays the number of waits in a particular wait type.
Wait Time The total wait time for a particular wait type (ms).
Average Wait Time The average wait time for a particular wait type (ms).
Signal Time Displays the difference between the time the waiting thread was signaled and the time it started running.
Top Waits by waiting tasks → Click any Wait Type → A popup with the Query Details will open
Query The SQL Query that is waiting for the resource.
Status The current status of the query execution (Running, Suspended, Sleeping, etc).
Execution Count The number of times the query has been executed since the last time the SQL Server instance was started or since the statistics were last reset.
Total Physical Reads The total number of physical disk reads performed since the statistics were last reset.
Total Logical Reads The total number of logical disk reads performed since the statistics were last reset.
Total Physical Writes The total number of physical disk reads performed since the statistics were last reset.
Top Slow Running Queries
Avg. Execution Time Average time taken for complete execution of this query.
Maximum Execution Time Maximum elapsed time for any complete execution of this query.
Minimum Execution Time Minimum elapsed time for any complete execution of this query.
No of Executions The number of times that the query has been executed since the plan was last compiled.
Query The SQL query text.
DB Name The Database against which this query has been executed (will be empty for ad hoc and prepared batches).
Object Name Returns the database object name for schema-scoped objects.
Last Execution Time The last time at which the query started executing.
Most Frequently Executed Queries
Execution Count The number of times that the query has been executed since the plan was last compiled.
Query The SQL query text.
DB Name The Database against which this query has been executed (will be empty for ad hoc and prepared batches).
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
Most Blocked Queries
Average Time Blocked The average amount of time the query was blocked.
Total Time Blocked The cumulative total amount of time for which the query was blocked.
Query The SQL query text.
DB Name The Database against which this query has been executed.
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
Top Queries by Lowest Plan Reuse (Top 10 queries whose query plan are not cached by the SQL Server. Query plan will be cached by SQL Server for faster query execution.)
Plan Usage Number of times this cache object has been used since its inception.
Cache Object Type Type of object in the cache. (Possible Values : Compiled Plan / Compiled Plan Stub / Parse Tree / Extended Proc / CLR Compiled Func / CLR Compiled Proc).
Query The SQL Query Text.
DB Name The Database against which this query has been executed (will be empty for ad hoc and prepared batches).
Last Execution Time The last time at which the query started executing.
Average Execution Time Average time taken to execute an individual query.
Cost of Missing Indexes (Displays information of the Top 10 missing indexes in the SQL Server. These indexes will have varying levels of impact on query performance. From this table, you can find out the most costly missing indexes across all the databases on the server and thus help finding out which missing indexes are likely to have the most significant impact on performance.)
Total Cost Overall cost of the queries executing without having the recommended indexes. It simply is a unit, the SQL Server Query Optimizer does its calculations with and is not associated with any type of known measurement unit like seconds or CPU time. Bigger the total cost number, more the resources could be saved after creating the recommended index. You need to check the query execution plan before creating the index for these particular columns and you need not check any other details. Also note that every index has a negative impact on the inserts/updates/deletes on that table and this impact can not be measured.
Avg. User Impact Displays the average percentage of benefit that the user queries could experience if this missing index group was implemented. This value means that the query cost would on average drop by this percentage if this missing index group was implemented.
Table Name Displays the name of table (along with Database Name and Schema Name) in which the index is missing.
Equality Usage Displays the column names for which Index is missing. These column names are used in equality predicates of the form: table.column = constant_value
Inequality Usage Displays the column names for which Index is missing. These column names are used in inequality predicates ( any comparison operator other than "=" ) of the form: table.column > constant_value.
Include Columns Displays the column names for which Index is missing. These column names are Comma-separated list of columns needed as covering columns for the query.
Top Components by Memory Usage (Displays information about the various Memory clerks. Memory clerks allocates resource / memory to SQL memory object during times of need.)
Component Type The type of memory clerk.
Single Pages(KB) The total amount of single page memory allocated in kilobytes (KB) directly from the buffer pool. Its is the memory consumed by this component from the Buffer pool.
Multi Pages(KB) The total amount of multipage memory allocated in (KB) from outside the buffer pool.
Virtual Memory Reserved(KB) The total amount of virtual memory reserved directly by the component that uses this clerk.
Virtual Memory Committed(KB) The total amount of virtual memory that is committed by the memory clerk. This should be always less than the amount of reserved memory.
AWE Memory Allocated(KB) The total amount of memory that is allocated by the memory clerk by using Address Windowing Extensions (AWE).
Shared Memory Allocated(KB) The total amount of shared memory that is reserved by a memory clerk memory reserved for use by shared memory and file mapping.
Shared Memory Committed(KB) The total amount of shared memory that is committed by the memory clerk.

Database

Database Management Actions

Applications Manager provides the ability to perform the following actions on your database(s).

Parameters Description
Online Changes the offline database(s) to online state.
Offline Changes the online database(s) to offline state. If there is any open connection in the database, this action waits for the connection to be closed and then brings the database offline.
Offline with no wait Checks for open connections and changes the online database(s) to offline state only if all connections are closed. This action returns error without waiting for existing connections to be closed.
Offline with immediate rollback Changes the database(s) to offline state immediately and all incomplete transactions are rolled back.
Offline with rollback after given time Waits for the specified period of time for the open connections to close and then brings the database offline. If there is any incomplete transactions even after specified period of time, the transactions will be rolled back.
Delete Deletes the selected database(s) permanently from the server.

Database Connection Detail

Parameters Description
Database Name Displays the name of the database.
Login Name Displays the SQL user name connected to the database.
Database Connections Displays the number of connections currently open in the database.
Parameters Description
Database Details
Name The Name of the database of the SQL server.
Data File (MB) The data file size of the database of the SQL server.
Used The total used space of the data file/log file of the SQL server.
Free The total free space available in the data file/log file of the SQL server.
Total The total alloted size of the data file/log file of the SQL server.
Max size The maximum file size of the data file/log file of the SQL server upto which it can grow.
Log File (MB) The log file size of the database of the SQL server
Used The total used space of the data file/log file of the SQL server.
Free The total free space available in the data file/log file of the SQL server.
Total The total alloted size of the data file/log file of the SQL server.
Max size The maximum file size of the data file/log file of the SQL server upto which it can grow.
Total size (MB) The total file size of the entire database of the SQL server.
Percentage Log Used The percentage of Log used of the database of the SQL Server.
Availability The current availability of the database in the SQL Server.
Health The current health of the database in the SQL Server.
Status The current status of the database of the SQL Server.:
  • ONLINE: Database is available for query.
  • INACTIVE: Database is inactive as it is no longer accessed by any application for some time now.
  • OFFLINE: Database was explicitly taken offline.
  • RESTORING: Database is being restored.
  • RECOVERING: Database is recovering and not yet ready for queries.
  • SUSPECT: Database did not recover.
  • EMERGENCY: Database is in an emergency, read-only state. Access is restricted to sysadmin member.

Click on the database name to see further details:

Parameters Description
System Tables
  • System Table Name - Name of the System Table.
  • Total Rows - Total number of rows present in that table.
  • Total Space Used in MB - Size of the table (MB).
Table Relationship
  • Parent Table – Table which contains the primary / parent column for the foreign key.
  • Parent Column – The primary column for which a foreign exists.
  • Foreign Table – Table name of foreign key column.
  • Foreign Column – The foreign key column name.
User Tables
  • System Table Name - Name of the User Table.
  • Total Rows - Total number of rows present in that table.
  • Total Space Used in MB - Size of the table (MB).
View Details
  • View Name - Name of the View.
  • Schema Name - Schema Name to which this view is associated.
  • IsIndexed - Shows if there is an index for the view ( 1 = Index Present, 0 = No Index ).
  • IsIndexable - Shows whether an index can be created ( 1 = Index can be created, 0 = No Index can be created ).
  • Create Date - Date on which the view was created.
  • Modify Date - Date on which the view was last modified.
Index Details
  • Table Name - Name of the Table.
  • Index Name - Name of Index present in the table.
  • Maximum Size Row - Maximum size for a row in that table (in bytes).
  • Max Noleafindex Row - Maximum size of a nonleaf index row (in bytes).
  • File Group Name - Name of the filegroup (Primary or User-defined).
  • Column Name - Name of the column for which index is present.
  • Type - Data type of the Column.
  • Clustered Index - Shows if the index is a Clustered Index or not (Yes or No). A clustered index determines the physical order of data in a table.
Indexes that have not been used
  • Object Name – Name of the table in which the unused index is present.
  • Index Name – Name of the index which is not used.
  • Type – Type of the index.
Fragmentation Details
  • Table Name - Name of the table in which the index is present
  • Index Name - Name of Index present in the table
  • Fragmentation Percent - Percentage of Logical fragmentation present in the Index. This is the percentage of out-of-order pages in the leaf pages of an index. The Percentage of Fragmentation should be as close to zero as possible for maximum performance. However, values from 0% through 10% may be acceptable
Indexes that have been used
  • Object Name – Name of the table in which the index is present.
  • Schema Name – Schema Name to which this table is associated.
  • Index Name – Name of the index which is not used.
  • Type – Type of the Index.
  • User Seeks - The number of seeks in this index by user queries.
  • User Scans - The number of scans in this index by user queries.
  • User Lookups - The number of bookmark lookups in this index by user queries.
  • User Updates - The number of updates in this index by user queries.

Note: The data file details of the database are updated once in an hour. You can also manually update each data file of the database by clicking on the 'Update Data/ Log File'.

Parameters Description
Database Disk Utilization
Total Size The total disk space of all available data files(in MB).
Used % The percentage of used disk space.
Used MB The total disk space used by all data files (in MB).
Free % The percentage of free/unused disk space.
Free MB The free/unused disk space (in MB).
Growth % The percentage at which all data files grow.
Data File Details
File Name Name of the data file.
Total Size (MB) The total disk space of all available data files(in MB).
Used (%) The percentage of used disk space.
Used (MB) The total disk space used by all data files (in MB).
Free (%) The percentage of free/unused disk space.
Free (MB) The free/unused disk space (in MB).
Growth (%) The percentage at which all data files grow.
Growth Rate The rate at which the data files grow.
Maximum size The maximum size (in MB) up to which the data file can grow.
DataFile Total Disk Space (MB) * Total disk space for specific data file in that drive
DataFile Available Disk Space (MB) * Available disk space for specific data file
Disk Free (%) * Disk free percentage for specific file
Disk Used (%) * Disk used percentage for specific file
Log File Details
File Name Name of the log file
Total Size (MB) The total disk space of all available log files(in MB).
Used (%) The percentage of used disk space.
Used (MB) The total disk space used by all log files (in MB).
Free (%) The percentage of free/unused disk space.
Free (MB) The free/unused disk space (in MB).
Growth (%) The percentage at which all log files grow.
Growth Rate The rate at which the log files grow.
Maximum size The maximum size (in MB) up to which the log file can grow.
LogFile Total Disk Space (MB) * Total disk space for specific log file in that drive
LogFile Available Disk Space (MB) * Available disk space for specific log file
Disk Free (%) * Disk free percentage for specific file
Disk Used (%) * Disk used percentage for specific file
Transaction Details
Transactions/Min Number of transactions started for the database per minute.
Active Transactions Number of transactions that's currently executing.
Replication Transactions/Min Number of replication transactions per minute.
Bulk Copy Rows/Min Number of rows bulk copied.
Bulk Copy Throughput/Min Amount of KiloBytes bulk copied.
Backup/Restore Throughput/Min Read/write throughput for backup/restore of a database.
Log Cache Reads/Min Reads performed through the log manager cache.
Log Flush Details
Log Flush/Min Number of log flushes
Log Flush Waits/Min Number of commits waiting on log flush
Log Flush Wait Time Total wait time (milliseconds)
Log Cache Hit Ratio The log cache hit ratio file size.
VLF Details
Number of VLFs The number of VLFs (Virtual Log Files) created. A transaction log is split internally into smaller chunks called virtual log files. A large number of VLFs can cause degradation in performance when transaction log backups occur or in any transaction log related activities.
FileID Physical log file identifier from sysfiles
FileSize Virtual log file size ( in bytes )
StartOffset Beginning point of the virtual log file
FSeqNo Virtual log file's sequence number
Status Whether the virtual file contains the active part of the transaction log. 0 means that virtual file does not contain the active portion of the log and it is available for log use ; 2 means that VLF is active
Parity Parity information for virtual log file
CreateLSN Log sequence number that began the virtual log file.
Database Properties
Creation Date Date in which the Database was created.
Database Mode The recovery model for the database which shows how transactions are logged.
Page Verify The option used to discover and report incomplete I/O transactions caused by disk I/O errors.
Compatibility Level The latest version of SQL Server that the database supports. The values are SQL Server 2014 (120), SQL Server 2012 (110), and SQL Server 2008 (100).
Auto Shrink Indicates whether the database files are available for periodic shrinking.
Auto Create Statistics Indicates whether the database automatically creates missing optimization statistics.
Auto Update Statistics Indicates whether the database automatically updates out of date optimization statistics.
DBCC Information
DBCC CHECKDB Shows when the DBCC CHECKDB command was issued last.
DBCC INDEXDEFRAG Shows when the DBCC INDEXDEFRAG command was issued last.

* Not supported for SQL Server versions 2008 and below.

Database Mirroring Details

Applications Manager provides the option to monitor mirroring details of SQL databases. You can monitor attributes like Mirroring role, Status, Mirroring partner details,Witness details, Unsent log available in Send Queue, Current send rate, Time to restore log and more.

Parameters Description
Mirroring Details
  • Mirroring Role- Displays the role of the mirroring database. The possible values are:
    • Principal
    • Mirror
  • Mirroring Status - Displays the status of mirroring process. The possible values are Synchronized, Synchronizing, Suspended, Pending failover, Disconnected.
  • Transaction Safety - Displays whether the mirroring operates in synchronous or asynchronous mode.
  • Mirroring Partner - Displays the server name of the mirroring partner.
  • Mirroring Partner Connection - Displays the connection string of the mirroring partner server.
  • Witness Status - Displays the status of witness connection. The possible values are Connected, Unknown, Disconnected.
  • Witness Connection - Displays the connection string of the witness server.
  • Witness Address - Displays the name of the witness server
Principal Log
  • Unsent Log (in KB) - Displays the unsent logs that are in the Send Queue.
  • Oldest Unsent Transaction (DD:HH:mm:ss) - Displays the estimated time of transaction pending in the Send Queue.
  • Time to send log (DD:HH:mm:ss) - Displays the estimated time that takes to send the log to the mirror server.
  • Current Send Rate - Displays the current send rate in KB/Sec.
  • Current Rate of new transactions - Displays the rate of new transactions entered in the principal server in KB/Sec.
Mirror Log
  • Unrestored log - Displays the amount of log waiting in the redo queue in KB/Sec.
  • Time to restore log (DD:HH:mm:ss) - Displays the estimated amount of time that takes to restore the log to the mirroring database.
  • Current restore rate - Displays the current restore rate in KB/Sec
Mirroring session
  • Mirror Commit Overhead - Displays the amount of overhead incurred in milliseconds while the principal server instance waits for the mirror server instance to write the transaction's log record into the redo queue.
  • Time to send and restore log (DD:HH:mm:ss) - Displays the time needed to send and restore the transactions committed in the principal database.
Mirroring Performance Counters For Principal database :
  • Logs Sent KB/Min - The number of logs sent per minute.
  • Log Send Queue KB - Total kilobytes of log that have not yet been sent to the mirror server.
  • Average Delay/Transaction (ms) - The delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This reports the approximate delay for a single transaction in process at that time.

For Mirror database :

  • Logs Redo KB/Min - The amount of transaction log applied on the mirror database per minute.
  • Redo Queue KB - Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.

Sessions

Parameters Description
Connection Statistics
Connection Time The time taken to connect to the Microsoft SQL database server from Applications Manager server.
Active Connections Number of users connected to the system.
Logins/Min Total number of logins started per minute.
Logouts/Min Total number of logouts started per minute.
Blocked Sessions Number of sessions that are blocked.
SQL Statistics
Batch Requests/Min Number of SQL batch requests received by server.
SQL Compilations/Min Number of SQL compilations.
SQL Recompilations/Min Number of SQL re-compiles.
AutoParams/Min Number of auto-parameterization attempts.
Failed AutoParams/Min Number of failed auto-parameterizations.
Host Details
Host Connected to the SQL Displays the hosts name connected to the SQL server.
Database Connections Displays the number of connections each host has to the SQL server.
Memory Usage Displays the total memory usage by each host connected to the SQL server.
CPU Time (ms) Displays the total CPU time (in milliseconds) of each host connected to the SQL server.
I/O The cumulative Disk reads and writes for the process running in the SQL Server.
Database Connection Detail
Database Name Displays the name of the database.
Login Name Displays the SQL user name connected to the database.
Database Connections Displays the number of connections currently open in the database.

You can find the list of sessions, lock and block running for the selected database.

Parameters Description
Database Name The name of the database.
Agent Type The type of agent - Backup, Copy or Restore.
Status The status of the database log shipping - Starting, Running, Success, Error, Warning.
Time Elapsed (in min) The total time taken to run the log shipping operation.
Log Time The time and date last log shipping operation.
Error Log Time The time of the last error log shipping operation ran.
Error Message The error messages received while running the log shipping operation if any.

Session Details

Parameters Description
PID Displays the SQL server session ID running in SQL Server.
Status Displays the status of the process in SQL Server. The possible values are:
  • Dormant : SQL Server session that hasn't done any work in more than an hour.
  • Running : SQL Server session running one or more batches. A session can run multiple batches when Multiple Active Result Sets (MARS) is enabled.
  • Background : SQL Server session running a background task, such as deadlock detection.
  • Rollback : SQL Server session which has a transaction rollback in process.
  • Pending : SQL Server session waiting for a worker thread to become available.
  • Runnable : The task in SQL Server session which is in the runnable queue of a scheduler while waiting to get a time quantum.
  • Spinloop : The task in SQL Server session waiting for a spinlock (Spinlock is a lock where the thread waits in a loop, repeatedly checking until the lock becomes available) to become free.
  • Suspended : SQL Server session is waiting for an event, such as I/O, to complete.
User Name The login name used for executing the process.
Host The host machine name from where the session has been established from.
DB Name Displays the name of the database currently being used by the process.
Program The application in SQL Server which has established the session.
Memory Usage The number of pages in the procedure cache that are currently allocated to this process (in KB).
CPU Time The cumulative CPU time for the process running in the SQL Server (in ms).
I/O The cumulative Disk reads and writes for the process running in the SQL Server.
Session Blocked Time Amount of time elapsed after the session was blocked (in seconds).
Blocking Session Displays the ID of the session that is blocking the request.
  • If this column is 0, then the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
  • If value is -2, then the blocking resource is owned by an orphaned distributed transaction.
  • If value is-3, then the blocking resource is owned by a deferred recovery transaction.
  • If value is -4, then the Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
Kill Allows you to kill the session of the selected database.

SQL Session Blocked Lock Details

On clicking 'View More', following details will be displayed:

Parameters Description
Holding ID The Blocked sessions ID.
Status The status of the process in SQL Server. The possible values are:
  • Dormant : SQL Server session that hasn't done any work in more than an hour.
  • Running : SQL Server session running one or more batches. A session can run multiple batches when Multiple Active Result Sets (MARS) is enabled.
  • Background : SQL Server session running a background task, such as deadlock detection.
  • Rollback : SQL Server session which has a transaction rollback in process.
  • Pending : SQL Server session waiting for a worker thread to become available.
  • Runnable : The task in SQL Server session which is in the runnable queue of a scheduler while waiting to get a time quantum.
  • Spinloop : The task in SQL Server session waiting for a spinlock (Spinlock is a lock where the thread waits in a loop, repeatedly checking until the lock becomes available) to become free.
  • Suspended : SQL Server session is waiting for an event, such as I/O, to complete.
Lock Type The type of Lock.
Command Name Displays the command that is being currently executed.
Query Displays the query for the selected database session.
Hold User The user the Lock.
Wait User The blocked user waiting for the Lock.
DB Name The Name of the database being used by the process.
Object ID The Object ID associated with the resource.
Hold Host The host machine holding the Lock.
Wait Host The Blocked Host waiting the Lock.
Host Command The Command being executed by the process holding the Lock.
Wait Command The Command to be executed by the process waiting for the Lock.
Memory Usage The number of pages in the procedure cache that are currently allocated to this process.
Wait Time (ms) The time, in milliseconds, in which this task is waiting for a resource. When the task is not waiting, the wait time is 0.
Last Wait Type The name of the last wait type.
Wait Resource The name of the resource that is needed.

Jobs

Applications Manager also allows you to easily manage these Jobs and Backup processes in bulk. Just select the Job(s) you wish to manage and then select any one of the option ( 'Start Job, Stop Job, Delete Job, Enable Job, Disable Job, Manage, Unmanage, Unmanage and Reset') from the pull-down menu.

Create a new Job

You can also create a job right from Applications Manager console. To do so, follow the steps given below:

  1. Click on 'Create job' link.
  2. Provide Job Name, select the Owner from the pull-down menu, select the Category and the Description.
  3. You can also set the status as either 'Enable' or 'Disable' if you choose to run the Job later.
  4. Provide the Step Name, Type, Run As, Database on which it should run, enter the SQL Command you wish to execute. You can repeat this process to add 'n' number of steps. You can also edit them at a later stage or even delete a step if found not required.
  5. Set the action On Success Action either as 'Go to the next step' or 'Quit the job reporting success' or 'Quit the job reporting failure'.
  6. Set the Retry attempts and Retry interval (minutes).
  7. Set the action On Failure Action either as 'Quit the job reporting failure' or 'Go to the next step' or 'Quit the job reporting success'.
  8. If you like to schedule the job, click on Schedule and provide the following information.
    • Provide a Name for the schedule job.
    • Select the Frequency at which you want the job to be executed : Daily / Weekly / Monthly.
    • If its Daily, then choose the occurrence at which you want this job to run daily: Occurs once / Multiple Times. If it Occurs once, provide the time (hh:mm). Also select the date from which the job needs to run.
    • If the frequency is Weekly, choose the days on which you want this job to run and provide whether it recurring every 'nth' weeks.
    • If the frequency is Monthly, choose the nth day of every nth month or select whether it should run on 'nth' day of every 'nth' week of every nth month.
  9. If you like to receive/send notifications of these jobs, click on Notifications, and select Email notification when the job either succeeds, fails or finishes to the appropriate operator. If the operator name is not specified, then select 'New Operator' and provide the Name and Email ID of the same.
  10. You can also set notification and allow jobs to be written into Windows Event Log files when it succeeds, fails or finishes.

The Jobs tab displays the performance metrics of various Jobs history running in SQL Server. You will be able to view SQL Job's history when you click on the corresponding SQL Job from Job History table. This will show the step by step job execution history for that corresponding job in a pop-up window.

Note: Applications Manager allows you to enable/disable data collection of the SQL Server Performance several metrics like Jobs, Backup and Replications . Once enabled, you can also customize the polling interval of these metrics.

Parameters Description
Job Name Name of the SQL Job
Current Execution Status The status of the job execution in each step.
Last Run Status Gives the last run status of the job
Run Date & Time Gives the date & time for which the jobs are scheduled to run.
Job Time (in seconds) Elapsed time in seconds for the execution of the job or step.
Retries Attempted Gives the number of times the scheduled jobs attempted to run.

Backup/Restore Details

Backup Details

Parameters Description
DB Name Displays the name of the database involved in the backup operation.
Start Displays the date and time of the back up operation started.
End Displays the date and time of the back up operation ended.
Expiration Displays the date and time the back up operation is supposed to expire.
Damaged If the value is 1, then it means that the damage to database was detected while this back up was being created. However the back up operation continued despite the errors.
Size Displays the size of the back up set (in bytes).
Physical Name Displays the physical name of the back up device.
Backup Age (Hours) Displays the number of hours passed since the back up operation was completed.

Note: Applications Manager allows you to enable/disable data collection of the SQL Server Performance several metrics like Jobs, Backup and Replications . Once enabled, you can also customize the polling interval of these metrics.

Restore Details

Parameters Description
DB Name Displays the name of the destination database for which the restore operation operate upon.
Restore by Displays the name of the user who performed the restore operation on the database.
Restore Type Displays the type of restore operation performed which could be either Database / File / Filegroup / Log / Verifyonly / Differential / Revert.
Started Displays the date and time of the restore operation.
Restore From Displays the physical name of the backup file created.
Restore To Displays the name of the file where the backup was restored to.

Replication

Log Shipping Status

Parameters Description
Database
Primary Name of the primary database.
Secondary Name of the secondary database.
Status
Backup Status of the backup session. (Starting, Running, Success, Error, or Warning)
Copy Status of the copy session. (Starting, Running, Success, Error, or Warning)
Restore Status of the restore session. (Starting, Running, Success, Error, or Warning)
Time since Backup Amount of time elapsed since the last log backup (in minutes).
Time since Copy Amount of time elapsed since the last log backup was copied (in minutes).
Time since Restore Amount of time elapsed since the last log backup was restored (in minutes).
Restore Latency Amount of time that elapsed between when the log backup was created on the primary database and when it was restored on the secondary databases (in minutes).

Log Shipping Role

Parameters Description
Primary Server
Agent Type Type of the log shipping job. (Backup)
Secondary Server(s) Name of the secondary instance(s) of MS SQL in the log shipping configuration.
Backup File The filename of the last backup file.
Backup Time The time and date of the last backup operation ran.
Backup Error Log Time Date and time at which the error occurred during backup operation.
Backup Error Message Displays the error messages received while running the backup operation if any.
Secondary Server
Agent Type Type of the log shipping job. (Copy or Restore)
Copied File The filename of the last backup file that was copied to the secondary server.
Copied Time The time and date of the last copy operation to the secondary server.
Copy Error Log Time Date and time at which the error occurred during copy operation.
Copy Error Message Displays the error messages received while running the copy operation if any.
Restored File The filename of the last backup file that was restored to the secondary server.
Restored Time The time and date of the last restore operation on the secondary database.
Restore Error Log Time Date and time at which the error occurred during restore operation.
Restore Error Message Displays the error messages received while running the restore operation if any.

Replication Status

Note:
  • The Distributor is the same SQL Server as the Publisher, or can be a separate SQL Server from the Publisher.
  • As Applications Manager monitors publication and subscription metrics from the distributor server, add the distributor SQL server as a monitor.
  • The SQL user used for monitoring must be given read access to the distribution database present in the distributor.

Publications

This table displays status information for all publications available in the corresponding SQL monitor.

Parameters Description
Publication Name of the publication
Type Type of the publication
Recent Status Indicates the current status of the publication based on the most recently executed subscription in the publication.
Overall Status Indicates the overall status of the publication based on highest priority/severity status among all the subscriptions in the publication.
Current Average Performance Current average performance of the replication based on the latency.
Current Worst Performance Current worst performance of the replication based on the latency.
No. of Subscriptions Number of subscriptions associated with the corresponding publication.
Synchronizing The number of distribution agents running for the publication.

Subscriptions

Applications Manager provides information about subscriptions available in the corresponding monitor.

Parameters Description
Running status Status of each subscription.
Subscription Name of the subscription
Publication Name of the publication running the corresponding subscription.
Performance Performance of the subscription.
Latency Latency value
Expiration Status Shows expiring status of the subscription which can be one of the following
  • Expired
  • Expiring soon
  • Critical

Replication Agents

Replication Agents tab displays information about each replication agent running in the corresponding monitor.

Parameters Description
Agent Name Name of the agent involved in the replication
Agent Type Type of the agent
Current Status Current status of the agent
[Database].[Publication] Name of the publisher
Last Start Time Last start time of the agent
Duration (HH:mm:ss) Duration of the agent session.
Delivary Rate (cmds/sec) Ratio of delivered commands to the duration of the agent.
Latency The current amount of time, in milliseconds, elapsed from when transactions are applied at the Publisher to when they are delivered to the distributor.
#Trans The number of transactions delivered to the distributor.
#Cmds The number of commands delivered to the distributor.
Avg #Cmds The average number of commands per transaction delivered to the distributor for the session.

Replication Agent History Table

Displays the performance metrics of various agents history running in SQL server. You will be able to view replication agent's history when you click on the corresponding agent from Agent Details table. This will show the step by step agent execution history for that corresponding agent in a pop-up window.

Parameters Description
Current status Current status of the agent
Last start time Last start time of the agent
Duration (HH:mm:ss) Duration of the agent session.
Last Action Displays the last action message of the agent.

Users

ManageEngine Applications Manager User Details section displays the entire list of active users of SQL servers. It displays the Username, Status, Login Type, Default DB and Creation Date. The DBA can also, quite easily, perform actions to the selected list of users such as Enable User, Disable User and Delete User.

Parameters Description
User Details
User Name Name of the user.
Status Status of the user.
Login Type Type of login made by the user.
Default DB Default database name accessed by the user.
Creation Date Timestamp at which the user was created.

Configuration

You can manage and optimize SQL Server resources using configuration options in Applications Manager. Optimizing these resources can have a significant effect on SQL Server's behavior and performance.

Applications Manager lists all available configuration options, the range (minimum and maximum value) of possible settings, and configuration values.

Parameters Description
Configuration Details
Name Name of the configuration option
Minimum Minimum range of configuration value
Maximum Maximum range of configuration value
Configured value The value currently configured to the server. For few options, this value takes effect for use only after restarting the SQL instance
Value in use The value currently in use for the server

Advanced options will be available or changed only when the 'show advanced option' is set to 1. You can enable these options in the following two ways:

  • Click on the link 'Enable Advanced options' (or)
  • Change the value of show advanced options to 1.

For some of the configuration options, the changes take effect only after restarting the SQL instance.The values used for the parameters Configured value and Value in use columns should match for a dynamically updated option. Options that require SQL Server to restart will initially show the changed value only in the Configured value column. After restart, the new value will appear in both the Configured value column and the Value in use column.

For example, the minimum and maximum server memory options are updated dynamically in the Database Engine; therefore, you can change them without restarting the server.

Note: Only users with Administrator / Operator with permission can edit the SQL Server Configuration Settings.

Parameters Description
access check cache bucket count* The Access check cache bucket count option controls the number of hash buckets used for access check result cache. The default value - 0 indicates that SQL Server is managing these options.
access check cache quota* The access check cache quota option controls the number of entries used for access check result cache. The default value - 0 indicates that SQL Server is managing these options.
Ad Hoc Distributed Queries* By default, SQL Server does not allow ad hoc distributed queries. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.
affinity I/O mask* The value for affinity I/O mask specifies which CPUs in a computer are eligible to process SQL Server disk I/O operations. The values for affinity I/O maskare as follows:
  • A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.
  • A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.
  • A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.
  • A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.
  • To cover more than 32 CPUs, configure a four-byte affinity I/O mask for the first 32 CPUs and up to a four-byte affinity64 I/O mask for the remaining CPUs.
affinity mask* The affinity mask option dynamically controls CPU affinity. The values for affinity maskare as follows:
  • A one-byte affinity mask covers up to 8 CPUs in a multiprocessor computer.
  • A two-byte affinity mask covers up to 16 CPUs in a multiprocessor computer.
  • A three-byte affinity mask covers up to 24 CPUs in a multiprocessor computer.
  • A four-byte affinity mask covers up to 32 CPUs in a multiprocessor computer.
  • To cover more than 32 CPUs, configure a four-byte affinity mask for the first 32 CPUs and up to a four-byte affinity64 mask for the remaining CPUs.
affinity64 I/O mask* The affinity64 I/O mask binds SQL Server disk I/O to a specified subset of CPUs. This option is only visible on the 64-bit version of SQL Server.
affinity64 mask* The affinity64 mask binds processors to specific threads. This option is only visible on the 64-bit version of SQL Server.
Agent XPs* Use the Agent XPsoption to enable the SQL Server Agent extended stored procedures on this server. The possible values are:
  • 0 - indicating that SQL Server Agent extended stored procedures are not available (the default).
  • 1 - indicating that SQL Server Agent extended stored procedures are available.
The setting takes effect immediately without a server stop and restart.
allow updates This option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server.
awe enabled* You can use the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory.
backup compression default Used by the BACKUP Transact-SQL statement to select the backup compression setting if a user does not specify WITH COMPRESSION or WITH NO_COMPRESSION. Used in SQL Server 2008 Enterprise and later only. Default backup compression is set to 0
blocked process threshold (s)* Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from values 0 to 86,400.
c2 audit mode* This option will configure the server to record both failed and successful attempts to access statements and objects.
clr enabled Use the clr enabled option to specify whether user assemblies can be run by SQL Server. Possible values are as follows:
  • 0 - Assembly execution not allowed on SQL Server
  • 1 - Assembly execution allowed on SQL Server
cost threshold for parallelism* The cost threshold for parallelism option specifies the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. The cost threshold for parallelism option can be set to any value from 0 through 32767.
cross db ownership chaining Use the cross db ownership chainingoption to configure cross-database ownership chaining for an instance of Microsoft SQL Server. Possible Values are :
  • 0 - cross db ownership chaining is off for the instance, cross-database ownership chaining is disabled for all databases.
  • 1 - cross db ownership chaining is on for the instance, cross-database ownership chaining is on for all databases.
cursor threshold* The cursor threshold optionspecifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. Possible Values are:
  • 1 - all keysets are generated synchronously.
  • 0 - all cursor keysets are generated asynchronously.
Database Mail XPs* The Database Mail XPs option enables Database Mail on this server. Possible values are:
  • 0 - Database Mail is not available (default).
  • 1 - Database Mail is available.
default full-text language* The default full-text language option specifies a default language value for full-text indexed columns. The default value of this option is the language of the server and requires an LCID value.
default language The default language option to specify the default language for all newly created logins.
default trace enabled* The default trace enabled option enables or disables the default trace log files.
disallow results from triggers* This option controls whether triggers return result sets.
  • When set to 1 (ON) any attempt by a trigger to return a result set fails, and the user receives an error message
  • The default setting for this option is 0 (OFF).
filestream access level Use the filestream access leveloption to change the FILESTREAM access level for this instance of SQL Server. Possible values are:
  • 0 - Disables FILESTREAM support for this instance.
  • 1 - Enables FILESTREAM for Transact-SQL access.
  • 2 - Enables FILESTREAM for Transact-SQL and Win32 streaming access.
fill factor (%)* The fill factor option specifies how full Microsoft SQL Server should make each page when it creates a new index using existing data.
ft crawl bandwidth (max)* The ft crawl bandwidth option specifies the size to which the pool of large memory buffers can grow. The max parameter value specifies the maximum number of buffers that the full-text memory manager should maintain in a large buffer pool. If the max value is zero, then there is no upper limit to the number of buffers that can be in a large buffer pool.
ft crawl bandwidth (min)* The ft crawl bandwidth option specifies the size to which the pool of large memory buffers can grow. The min parameter specifies the minimum number of memory buffers that must be maintained in the pool of large memory buffers. Upon request from the Microsoft SQL Server memory manager, all extra buffer pools will be released but this minimum number of buffers will be maintained. If, however, the min value specified is zero, then all memory buffers are released.
ft notify bandwidth (max)* The ft notify bandwidth option specifies the size to which the pool of small memory buffers can grow. The max parameter value specifies the maximum number of buffers that the full-text memory manager should maintain in a small buffer pool. If the max value is zero, then there is no upper limit to the number of buffers that can be in a small buffer pool.
in-doubt xact resolution* The in-doubt xact resolutionoption controls the default outcome of transactions that the Microsoft Distributed Transaction Coordinator (MS DTC) is unable to resolve. Inability to resolve transactions may be related to the MS DTC down time or an unknown transaction outcome at the time of recovery. Possible Values are :
  • 0 - No presumption. Recovery fails if MS DTC cannot resolve any in-doubt transactions.
  • 1 - Presume commit. Any MS DTC in-doubt transactions are presumed to have committed.
  • 2 - Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
index create memory (KB)* The index create memory option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated. The default value for this option is 0 (self-configuring).
lightweight pooling* The lightweight pooling option provides a means of reducing the system overhead associated with the excessive context switching seen in symmetric multiprocessing (SMP) environments. Possible Values:1 - causes SQL Server to switch to fiber mode scheduling. The default value for this option is 0.
locks* The locks option to set the maximum number of available locks, thereby limiting the amount of memory used. Possible Values: 0 (Default) - allows the Database Engine to allocate and deallocate lock structures dynamically, based on changing system requirements. When locks is not set to 0, lock escalation occurs when the number of locks reaches 40 percent of the value specified for locks.
max degree of parallelism* You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. Possible Values:
  • 0 default value - allows SQL Server to use all the available processors up to 64 processors.
  • 1 - To suppress parallel plan generation
  • greater than 1 - to restrict the maximum number of processors used by a single query execution.
max full-text crawl range* The max full-text crawl range option optimizes CPU utilization; you can specify the number of partitions that Microsoft SQL Server should use during a full index crawl. The default value of this option is 4; the minimum value is 1, and the maximum value is 256.
max server memory (MB)* Use the max server memory option to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server.
max text repl size (B) The max text repl size option to specify the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default is 65536. A value of -1 indicates no limit, other than the limit imposed by the data type.
max worker threads* The max worker threads option is used to configure the number of worker threads available to Microsoft SQL Server processes. 0(default value)- allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems.
media retention* The media retention option provides a system-wide default for the length of time to retain each backup set. This option helps protect backups from being overwritten until the specified number of days has elapsed. The default is 0 days.
min memory per query (KB)* The min memory per query option specifies the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query.
min server memory (MB)* The min server memory option is used to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server. The default setting for min server memory is 0.
nested triggers Use the nested triggersoption to control whether a trigger can cascade (perform an action that initiates another trigger that initiates another trigger, and so on). Possible values:
  • 0 - triggers cannot cascade.
  • 1 - triggers can cascade to as many as 32 levels.
network packet size (B)* The network packet size option to set the packet size (in bytes) used across the entire network. If an application sends and receives small amounts of information, the packet size can be set to 512 bytes, which is sufficient for data transfers.
Ole Automation Procedures* Use the Ole Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. Possible Values:
  • 0 - OLE Automation Procedures are disabled. Default for new instances of SQL Server.
  • 1 - OLE Automation Procedures are enabled.
open objects* This option is still present in sp_configure, although its functionality has been disabled in Microsoft SQL Server. (The setting has no effect.) In SQL Server, the number of open database objects is managed dynamically and is limited only by the available memory. The open objects option available in sp_configure for backward compatibility with existing scripts.
optimize for ad hoc workloads* The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan.
PH timeout (s)* The PH timeout option to specify the time, in seconds, that the full-text protocol handler should wait to connect to a database before timing out. The default value is 60 seconds. Increase the ph timeout value when connection attempts are timing out due to temporary network issues.
precompute rank* The precompute rank server-level advanced option to improve the performance of FREETEXTTABLE queries that specify the top_n_by_rank parameter. When the option is set to 1, FREETEXTTABLE queries specified with top_n_by_rank use precomputed rank data stored in the full-text catalogs. When you use precomputed rank data, the result set that is returned by top_n_by_rank may not contain the same results as those that are returned when the precompute rank option is set to 0.
priority boost* Use the priority boostoption to specify whether Microsoft SQL Server should run at a higher Microsoft Windows 2000 or Windows 2003 scheduling priority than other processes on the same computer. Possible Values:
  • 1 - SQL Server runs at a priority base of 13 in the Windows 2000 or Windows Server 2003 scheduler.
  • 0 (default) - A priority base of 7.
query governor cost limit* Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.
query wait (s)* The query wait option specifies the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
recovery interval (min)* Use the recovery interval option to set the maximum number of minutes per database that Microsoft® SQL Server™ needs to recover databases. Keep recovery interval set at 0 (self-configuring) unless you notice that checkpoints are impairing performance because they are occurring too frequently. If this is the case, try increasing the value in small increments.
remote access Use the remote accessoption to control the execution of stored procedures from local or remote servers on which instances of Microsoft SQL Server are running. Possible Values:
  • 0 - to prevent local stored procedures from being run from a remote server or remote stored procedures from being run on the local server.
  • 1 (default) - to grant permission to run local stored procedures from remote servers or remote stored procedures from the local server
remote admin connections SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. Possible Values:
  • 0 - Indicates only local connections are allowed by using the DAC.
  • 1 - Indicates remote connections are allowed by using the DAC.
remote login timeout (s) Use the remote login timeout option to specify the number of seconds to wait before returning from a failed attempt to log in to a remote server. The default setting for remote login timeout is 20 seconds. A value of 0 allows for an infinite wait.
remote proc trans The remote proc trans option protects the actions of a server-to-server procedure through a Microsoft® Distributed Transaction Coordinator (MS DTC) transaction.
remote query timeout (s) Use the remote query timeout option to specify how long (in seconds) a remote operation can take before Microsoft SQL Server times out. The default value is 600, which allows a 10-minute wait.
Replication XPs* This option is for internal use only.
scan for startup procs* The scan for startup procsoption to scan for automatic execution of stored procedures at Microsoft SQL Server startup time. Possible Values:
  • 1 - SQL Server scans for and runs all stored procedures defined on the server.
  • 0 (default value) - do not scan
server trigger recursion The server trigger recursionoption specifies whether to allow server-level triggers to fire recursively. Possible values:
  • 1 (ON) - server-level triggers will be allowed to fire recursively.
  • 0 (OFF)- server-level triggers cannot be fired recursively.
set working set size* The set working set size option to reserve physical memory space for SQL Server that is equal to the server memory setting. Before setting set working set size to 1, set both min server memory and max server memory to the same value, the amount of memory you want SQL Server to use.
show advanced options The show advanced options option to display the sp_configure system stored procedure advanced options. When you set show advanced options to 1, you can list the advanced options by using sp_configure. The default is 0.
SMO and DMO XPs* Use the SMO and DMO XPs option to enable SQL Server Management Object (SMO) extended stored procedures on this server. Possible Values are:
  • 0 - SMO XPs are not available.
  • 1 - SMO XPs are available. This is the default.
SQL Mail XPs* Use the SQL Mail XPs option to enable SQL Mail on this server. The possible values are:
  • 0 indicating SQL Mail is not available (default)
  • 1 indicating SQL Mail is available
transform noise words* Use the transform noise wordsserver configuration option to suppress an error message if noise words/stopwords, cause a Boolean operation on a full-text query to return zero rows. Possible Values:
  • 0-Noise words (or stopwords) are not transformed.
  • 1-Noise words (or stopwords) are transformed. They are ignored, and the rest of the query is evaluated
two digit year cutoff* The two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.
user connections* The user connections option specifies the maximum number of simultaneous user connections allowed.
user instance timeout* The User Instance Timeout option that you can access through sp_configure is not supported in Microsoft SQL Server 2008. This option works only with SQL Server 2008 Express (SQL Server Express).
user instances enabled The user instance enabled option that you can access through sp_configure is not supported in Microsoft SQL Server 2008. This option works only with SQL Server 2008 Express (SQL Server Express).
user options The user options option specifies global defaults for all users. The user optionsoption allows you to change the default values of the SET options (if the server's default settings are not appropriate). Possible Values:
  • 1- DISABLE_DEF_CNST_CHK - Controls interim or deferred constraint checking.
  • 2- IMPLICIT_TRANSACTIONS - For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
  • 4 - CURSOR_CLOSE_ON_COMMIT - Controls behavior of cursors after a commit operation has been performed.
  • 8 - ANSI_WARNINGS - Controls truncation and NULL in aggregate warnings.
  • 16 - ANSI_PADDING - Controls padding of fixed-length variables.
  • 32 - ANSI_NULLS - Controls NULL handling when using equality operators.
  • 64 - ARITHABORT - Terminates a query when an overflow or divide-by-zero error occurs during query execution.
  • 128 - ARITHIGNORE - Returns NULL when an overflow or divide-by-zero error occurs during a query.
  • 256 - QUOTED_IDENTIFIER - Differentiates between single and double quotation marks when evaluating an expression.
  • 512 - NOCOUNT - Turns off the message returned at the end of each statement that states how many rows were affected.
  • 1024 - ANSI_NULL_DFLT_ON - Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
  • 2048- ANSI_NULL_DFLT_OFF - Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
  • 4096 - CONCAT_NULL_YIELDS_NULL- Returns NULL when concatenating a NULL value with a string.
  • 8192- NUMERIC_ROUNDABORT- Generates an error when a loss of precision occurs in an expression.
  • 16384 - XACT_ABORT - Rolls back a transaction if a Transact-SQL statement raises a run-time error.
xp_cmdshell* The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled.

AlwaysOn Availability Groups

Applications Manager lets you monitor the status of your SQL Server AlwaysOn resources by displaying all the Availability Groups running in your SQL server. Click on the links below to view all the required details listed under the following tables:

Availability Groups

Parameters Description
Name The name of an availability group for which the connected server instance hosts a replica.
Primary Replica Name of the server instance that is hosting the primary replica of the availability group.

Availability Replicas

On clicking the required availability group, you can view detailed information on various replicas available under that group.

Parameters Description
Replica The name of the server instance that hosts the availability replica.
Role Indicates the current role of the availability replica. Possible values are Primary, Secondary and Resolving.
Availability Mode Indicates the replica property that you set separately for each availability replica. Possible values are Synchronous and Asynchronous.
Failover Mode Displays the failover mode for which the replica is configured. Possible failover mode values are Automatic and Manual.
Synchronization Health Indicates the synchronization health of the replica. Possible values are Healthy, Partially Healthy and Not Healthy.
Connection Status Indicates whether a secondary replica is currently connected to the primary replica. If the replica is primary, It indicates whether it connected with all the secondary replicas. Possible values are either Connected or Disconnected.
State Indicates the current operational state of the secondary replica. Possible values are: Pending failover, Pending, Online, Offline, Failed, Failed, No Quorum and Not Local.
Bytes Received from Primary * Rate at which data is received from the Primary Replica by the Secondary Replica (in MB/Sec).
Bytes Sent to Primary * Rate at which data is sent to the Primary Replica by the Secondary Replica (in MB/Sec).

* - Obtained after clicking over the required replica under Replica Stats.

Availability Databases

On clicking the required replica, you can view detailed information of various databases available under that replica.

Primary Databases:

Database Status Information:

Parameters Description
Synchronization Health Indicates the Synchronization health of the database. Possible values are Healthy, Partially Healthy and Not Healthy.
Synchronization State Indicates whether the availability database is currently synchronized with other replicas. Possible values are Not Synchronizing, Synchronizing, Synchronized, Reverting and Initializing.
Failover Readiness Indicates which availability database can be failed over with or without potential data loss. Possible values are either Data Loss or No Data Loss.
State Indicates whether the state at which the availability database is currently in. Possible values are either Suspended or Resumed.

Performance Statistics:

Parameters Description
Redone Bytes The rate at which log records are redone on the primary database (in MB/Sec).
Log Bytes Received The rate at which log records are received by the primary database (in MB/Sec).
Log Flushed Amount of data written from the log cache to the physical transaction log file on the disk (in MB).

Secondary Databases:

Database Status Information:

Parameters Description
Synchronization Health Indicates the Synchronization health of the database. Possible values are Not Healthy, Partially Healthy and Healthy.
Synchronization State Indicates whether the availability database is currently synchronized with other replicas. Possible values are Not Synchronizing, Synchronizing, Synchronized, Reverting and Initializing.
Failover Readiness Indicates which availability database can be failed over with or without potential data loss. Possible values are either Data Loss or No Data Loss.
State Indicates whether the state at which the availability database is currently in. Possible values are either Suspended or Resumed.

Transaction Details:

Parameters Description
Log Send Queue Indicates the amount of log records in the log files of the primary database that have not been sent to the secondary replica (in MB).
Redo Queue Indicates the amount of log records in the log files of the secondary database that have not yet been redone (in MB).
Log Flushed Indicates the amount of data written from the log cache to the physical transaction log file on the disk (in MB).
Log Send Rate Indicates the rate at which log records are being sent to the secondary database (in MB/Sec).
Redo Rate Indicates the rate at which the log records are being redone (in MB/Sec).

Performance Statistics:

Parameters Description
Send Latency Indicates the amount of time taken by the Primary Replica to send all the log records to the Secondary Replica (in Sec).
Redo Lag Indicates the amount of time taken to redo the catch-up time (in Min). The catch-up time is the time taken for the secondary replica to catch up with the primary replica.
Sync Lag Indicates the amount of time delay observed in the last transaction log records between the primary and the secondary replica (in Min). If the primary replica fails, all the transaction log records within that time lag will be lost.
Redone Bytes Indicates the amount of log records that are redone on the secondary database in the last second (in MB/Sec).
Log Bytes Received Indicates the amount of log records received by the secondary replica for the database in the last second (in MB/Sec).

SQL Server - Cluster Details

Applications Manager lets you monitor the status of your SQL Server Clusters and Cluster resources. Click on the monitor name to see all the Cluster details listed under the following links:

Note: 'Cluster Details' tab can be viewed only in Windows installations of Applications Manager.

Cluster Details

Parameters Description
Cluster Name/IP Address The name/IP Address of the cluster.
Quorum Owner Node The node in which the sql cluster configuration data is currently accessible.
Quorum Path The path to the quorum files.
Quorum Type The current quorum type. The following are the possible values:
  • Majority
  • No Majority
Number of Nodes The total number of nodes in a cluster.
Max Nodes The maximum number of nodes that can participate in a cluster.
Number of Networks The number of networks used by the server cluster for communication.
Resources Online The SQL server cluster resources that are currently online.
Resources Offline The SQL server cluster resources that are currently offline.
Resource Groups Online The SQL server cluster resource groups that are currently online.
Resource Groups Offline The SQL server cluster resource groups that are currently offline.
Disks in Use The number of disks currently in use in the cluster.

Disk Utilization

You can monitor the total Disk Utilization of a Cluster.

Parameters Description
Used Percentage The total percentage of used disk space in a Cluster.
Free Percentage The total percentage of free disk space in a Cluster.
Size The total size of disk memory, in megabytes.
Used The total used space in the disk, in megabytes.
Free The total free space available in the disk, in megabytes.

Storage

Parameters Description
Path The path (including the drive letter if present) of the clustered disk partition.
Volume Label Specifies access to the VolumeLabel property, which is the volume label of the partition.
Size The total size for the partition, in megabytes.
Used The total used space in the partition, in megabytes.
Free The total free space available for the partition, in megabytes.
Used Percentage The percentage of used space in the partition.
Free Percentage The percentage of free space in the partition.

Nodes

Parameters Description
Node Name Specifies the label by which the node is known.
State Specifies the current state of a node. Node states can be:
  • Up - The node is physically plugged in, turned on, booted, and capable of executing programs.
  • Down - The node is turned off or not operational.
  • Joining - The node is in the process of joining a cluster.
  • Paused - The node is running but not participating in cluster operations.
  • Unknown - The operation was not successful.

Networks

Parameters Description
Name Specifies the name of the network.
Role Provides access to the network's Role property i.e, the role of the network in the cluster. The following are the possible values:
  • 0 - None - The network is not used by the cluster.
  • 1 - Cluster - The network is used to carry internal cluster communication.
  • 2 - Client - Not supported - Windows Server 2003: The network is used to connect client systems to the cluster.
  • 3 - Both - The network is used to connect client systems and to carry internal cluster communication.
State Specifies the current state of the network. The following are the possible values:
  • -1 - Unknown - The operation was not successful.
  • 0 - Unavailable - All of the network interfaces on the network are unavailable, which means the nodes that own the network interfaces are down.
  • 1 - Down - The network is not operational; none of the nodes on the network can communicate.
  • 2 - Partitioned - The network is operational, but two or more nodes on the network cannot communicate. Typically a path-specific problem has occurred.
  • 3 - Up - The network is operational; all of the nodes in the cluster can communicate.
Address Specifies the address for the entire network or subnet.

Resource Groups

Parameters Description
Name The name of the Resource group.
Current Node The node in which the sql server is currently running.
Preferred Node The node that is primarily responsible for hosting a cluster resource.
State The current state of the resource group. The following are the possible values.
  • Unknown (-1)
  • Online (0)
  • Offline (1)
  • Failed (2)
  • PartialOnline (3)
  • Pending (4)