ManageEngine® Applications Manager


MS SQL DB Servers

<< Prev

Home

Next >>

MS SQL DB Servers

Supported Versions

Applications Manager supports monitoring of MS SQL 2000, 2005, 2008, 2012 and 2014 versions.

 

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:

Note:
Minimum User Privileges : User should be permitted to access MASTER and MSDB database.
Roles : public + db_datareader should be selected in MASTER and MSDB database

For MS SQL 2005, 2008 and 2012 user role,
Database Accessed: Master
Permit in Database Role : db_datareader and requires VIEW SERVER STATE permission on the server

To grant VIEW SERVER STATE you can use any of the following methods :

1) Execute the following query,
GRANT VIEW SERVER STATE TO username;

2) In SQL management studio for user choose Properties -> Securables -> Click Add ( under securables ) -> choose "All objects of the Types..." -> choose Servers -> choose Grant for "View server state" permission.

Monitor Information

Parameters

Description

Name

Specifies the name of MS SQL server monitor.

Health

Specifies the health (Clear, Warning, Critical) of the MS SQL server.

Type

Specifies the type you are monitoring.

Version

Specifies the version of the database server.

Port

Specifies the port number at which the database server is running.

ODBC Driver Version

Specifies the ODBC driver version used.

Host Name

Specifies the host at which the database server is running.

Host OS

Specifies the OS of the host where the database server is running.

Last Alarm

Specifies the last alarm that was generated for the database server.

Last Polled at

Specifies the time at which the last poll was performed.

Next Poll at

Specifies the time at which the next poll is scheduled.

Availability

Shows the current status of the server - available or not available.

Memory Usage

Parameters

Description

Total Memory

Total amount of dynamic memory the server is currently consuming.

SQL Cache Memory

Total amount of dynamic memory the server is using for the dynamic SQL cache.

Lock Memory

Total amount of dynamic memory the server is using for locks.

Optimizer Memory

Total amount of dynamic memory the server is using for query optimization.

Connection Memory

Total amount of dynamic memory the server is using for maintaining connections.

Granted WorkSpace Memory

Total amount of memory granted to executing processes. This memory is used for hash, sort and create index operations.

Memory Grants Pending

Current number of processes waiting for a workspace memory grant.

Memory Grants Success

Current number of processes that have successfully acquired a workspace memory grant.

Buffer Manager Statistics

Parameters

Description

Buffer Hit Ratio

Percentage of pages that were found in the buffer pool without having to incur a read from disk.

Page LookUps/Min

Number of requests to find a page in the buffer pool.

Page Reads/Min

Number of physical database page reads issued.

Page Writes/Min

Number of physical database page writes issued.

Total Pages

Number of pages in the buffer pool (includes database, free, and stolen).

Database Pages

Number of pages in the buffer pool with database content.

Free Pages

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.

Connection Statistics

Parameters

Description

Connection Time

Time taken to get connected to the MS SQL database 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.

Cache Details

Parameters

Description

Cache Hit Ratio

Ratio between cache hits and lookups

Cache Used/Min

Times each type of cache object has been used

Cache Count

Number of cache objects in the cache

Cache Pages

Number of 8k pages used by cache objects

Lock Details

Parameters

Description

Lock Requests/Min

Number of new locks and lock conversions requested from the lock manager.

Lock Waits/Min

Total wait time for locks in the last minute.

Lock Timeouts/Min

Number of lock requests that timed out. This includes internal requests for NOWAIT locks.

Deadlocks/Min

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.

SQL Statistics

Parameters

Description

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.

Latch Details

Parameters

Description

Latch Waits/Min

Number of latch requests that could not be granted immediately and had to wait before being granted.

Average Latch Wait Time

Average latch wait time for latch requests that had to wait.

Access Method Details

Parameters

Description

Full Scans/Min

Number of unrestricted full scans. These can either be base table or full index scans.

Range Scans/Min

Number of qualified range scans through indexes.

Probe Scans/Min

Number of probe scans. A probe scan is used to directly look up rows in an index or base table.

Database Details

Parameters

Description

DataFile Details

Gives the DataFile size.

Log File Size

Gives the Size of the Log File, used size of the Log File.

Transaction Details

Gives the number of transaction per minute, replication transaction per minute, and the active transactions.

Log Flush Details Gives the number of Log Flush/minute, Log Flush waits/minute, and the Log Flush wait time.

In addition, Applications Manager displays more in-depth performance metrics such as System Tables, Table Relationship, User Tables etc., of the selected database details page.

 

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 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.

Scheduled Jobs

Parameters

Description

Job Status

Gives the Status of the job

Run date & time

Gives the date & time for which the jobs are scheduled to run.

Job Time

Gives the time taken by the job.

Retries Attempted

Gives the number of times the scheduled jobs attempted to run.

SQL Performance

Applications Manager presents a host of performance metrics for the SQL Server. They are:

Top Queries by CPU

Parameters

Description

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 Slow Running Queries

Displays information of Top 10 costly queries as measured by the Average Execution Time.

Parameters

Description

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.

Last Execution Time

The last time at which the query started executing.

Top Queries by Lowest Plan Reuse

Displays information of 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.

Parameters

Description

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.

Top Queries by I/O

Displays information of the Top 10 costly queries as measured by Average I/O operation (logical read and logical write operations).

Parameters

Description

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 Most Frequently Executed

Parameters

Description

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.

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.

Parameters

Description

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 Queries by CLR

Displays information of the Top 10 queries having high CLR time (Common Language Runtime inside Microsoft .NET Framework).

Parameters

Description

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 Queries by most frequently blocked

Parameters

Description

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 Components by Memory Usage

Displays information about the various Memory clerks. Memory clerks allocates resource / memory to SQL memory object during times of need.

Parameters

Description

Component Type

Displays the type of memory clerk.

Single Pages(KB)

Displays 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)

Displays the total amount of multipage memory allocated in (KB) from outside the buffer pool.

Virtual Memory Reserved(KB)

Displays the total amount of virtual memory reserved directly by the component that uses this clerk.

Virtual Memory Committed(KB)

Displays 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)

Displays total amount of memory that is allocated by the memory clerk by using Address Windowing Extensions (AWE).

Shared Memory Allocated(KB)

Displays 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)

Displays the total amount of shared memory that is committed by the memory clerk.

Top Waits by Waiting Tasks

Parameters

Description

Wait Type

Indicates name of the wait type.

Waiting Tasks

Displays the number of waits on that particular wait type.

Wait Time

Total time in milliseconds for that particular type of wait.

Average Wait Time

Average time in milliseconds for that particular type of wait.

Signal Time

Displays the difference between the time the waiting thread was signaled and when it started running.

Backup/Restore Details

Backup Details

Displays the performance metric about various SQL backup set present in the SQL Server.

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.

Restore Details

Displays the performance metrics about various Restore operation in the SQL Server.

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 Details

 

Log Shipping Status

Parameters

Description

Database Name

Displays the name of the database.

Agent Type

Displays the type of agent - Backup, Copy or Restore.

Status

Displays the status of the database log shipping - Starting, Running, Success, Error, Warning.

Time Elapsed (in min)

Displays the total time taken to run the log shipping operation.

Log Time

Displays the time and date last log shipping operation.

Error Log Time

Displays the time of the last error log shipping operation ran.

Error Message

Displays the error messages received while running the log shipping operation if any.

Replications Monitor Details

 

Publications

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

Parameters

Description

Status

The status of each publication.

Publication

Name of the publication

Type

Type of the publication

No. of Subscriptions

Number of subscriptions associated with the corresponding publication.

Synchronizing

The number of distribution agents running for 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.

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.

 

See Also

Creating New Monitor - MS SQL Database Server

 

<< Prev

Home

Next >>

Oracle Coherence DB Servers

IBM DB2 DB Servers