MS SQL Monitoring

Cavisson enables you to keep an eye on the performance of your database servers. Using the MS SQL Monitoring tool, you can optimize the performance of your applications and ensure the availability of your SQL servers. It provides an end-to-end monitoring of MS SQL queries with insights into various aspects, such as SQL Activity, Waits Statistics, Databases, and Support Services.

Benefits

Implementing effective MSSQL monitoring with NetStorm offers the following benefits:

  • Improved application availability
  • Increased database performance
  • Quick detection of database outages, failures, and table corruption
  • Prognostic analysis of storage requirements and index performance

Features

  • Provides all information on blocked / blocking queries.
  • Provides all types of Wait Stats.
  • Provides Query Execution Statistics and Query Plan.
  • Provide status for MSSQL services.
  • Ability to view data file growth and log growth for databases.

MSSQL DB Monitor Pre-Requisites

  1. Communication from machine on which the agent is installed to DB Server.
  2. Supported MSSQL Versions: 2005, 2008, 2012, 2014, 2016, and 2017.

In Outbound mode, in </home/cavisson/Controller/webapps/sys/msSqlConfig.properties> file, the following keywords should be added:

Note: These keywords are mandatory for DB Monitoring when running test in Outbound mode, but this dependency is removed in 4.3.0 release.

Provide the following mandatory options while configuring monitor through Monitoring UI.

  1. MSSQL Host – Server IP or server name where MS SQL DB server is installed.
  2. MSSQL Port – Provide MSSQL service running port (default port 1433).
  3. Username and Password – Provide accessible username and password.
  4. Authentication – Select either SQL authentication or Windows authentication (depends on client).
  5. Instance Name – Provide any name. It is needed to differentiate if multiple monitors are applied on same server.

In addition to the above-mentioned options, refer to the following image for monitor type.

  1. Monitor Type – Provide Ring Buffer option for two monitors (Deadlock and Actual Execution Plan).

MSSQL Server Authentication

  • SQL Server Authentication: If machine agent is installed in Linux platform then SQL Server Authentication should be enabled for provided user credentials.
  • Windows Authentication: If machine agent is installed in Windows platform then SQL Server Authentication or Window Authentication works for monitoring.

Required Permissions to see MSSQL Stats and Execution Plans

User account used for mapping should have the following permissions to see MSSQL Stats and Execution plans:

  1. Map the user to the “master” DB and default schema to “sys”.
  2. For current database server, provide the explicit grant for “Connect SQL”.
  3. User should be granted with “VIEW” permission for “server state”. This can be done by running the following query:

GRANT VIEW server state to <user_name>;

For more details, please refer the “Appendix – Assigning Permissions to User” section.

Metric Description

There are system related metrics and application related metrics supported by Cavisson. All system related metrics would also be available for MSSQL server. All System, Process, and Network related metrics are standard for Windows environment. For details, please refer Cavisson Product Suite – Datasheet. Here, we are describing only MSSQL server metrics.

Following are some key metrics that captures the health status of MS SQL Server.

  • MSSQL File Stats
  • MSSQL IO Activity Stats V2
  • MSSQL Performance Stats V2
  • MSSQL Server Stats V2
  • MSSQL Service Stats
  • MSSQL Temp DB Stats
  • MSSQL Connection Stats
  • MSSQL Memory Utilization Stats

MSSQL File Stats

Graph Description Table

SR No.

Metric Name

Description

1

MSSQL File State

File states : 0 means ONLINE, 1 means RESTORING, 2 means RECOVERING, 3 means RECOVERY_PENDING, 4 means SUSPECT, 5 means EMERGENCY 6 means OFFLINE, 7 means DEFUNCT.

2

MSSQL File Size (MB)

File size in Megabytes.

3

MSSQL File Growth Status

File growth status either in percentage or in pages. 1 means Growth of the file is a percentage, and 0 means Absolute growth size in pages.

4

MSSQL File Used (MB)

File space used in Megabytes.

5

MSSQL File Free (MB)

File free space in Megabytes.

6

MSSQL File Used (%)

File space used in percentage.

7

MSSQL File Growth

If file growth status = 0, auto growth increment is in units of 8-KB pages, rounded to the nearest 64 KB, If file growth status = 1, auto growth increment is expressed as a whole number percentage.

8

MSSQL File Recovery Model

File recovery model selected. 1 means FULL, 2 means BULK_LOGGED, 3 means SIMPLE

Graphical View of Monitoring

MSSQL IO Activity Stats V2

Graph Description Table

SR No.

Metric Name

Description

1

MSSQL Data File Avg Read Size (KB)

Data file average read size in Kilobytes.

2

MSSQL Data File Avg Write Size (KB)

Data file average write size in Kilobytes.

3

MSSQL Data File Bytes Read/Sec

Data file bytes read per second.

4

MSSQL Data File Bytes Write/Sec

Data file bytes write per second.

5

MSSQL Data File IO Stall (ms)/Sec

Time, users waited for I/O to be completed on the file per second.

6

MSSQL Data File Read/Sec

Number of read per second from data file.

7

MSSQL Data File Write/Sec

Number of write per second into data file.

8

MSSQL Log File Avg Read Size (KB)

Log file average read size in Kilobytes.

9

MSSQL Log File Avg Write Size (KB)

Log file average write size in Kilobytes..

10

MSSQL Log File Bytes Read/Sec

Log file bytes read per second.

11

MSSQL Log File Bytes Write/Sec

Log file bytes write per second.

12

MSSQL Log File IO Stalls

Log file io stalls.

13

MSSQL Log File Read/Sec

Number of read per second from log file.

14

MSSQL Log File Write/Sec

Number of write per second into log file.

Graphical View of Monitoring

MSSQL Performance Stats V2

Graph Description Table

SR No.

Metric Name

Description

1

MSSQL Query/Min

Number of query executing per minute.

2

MSSQL Avg Query CPU Time (ms)

Average query cpu time in milliseconds.

3

MSSQL Avg Query Elapsed Time (ms)

Average query elapsed time in milliseconds.

Graphical View of Monitoring

MSSQL Server Stats V2

Graph Description Table

S. No.

Metric

Description

1.         

MSSQL Active Cursors

Number of active cursors.

2.         

MSSQL Active Temp Tables

Number of temporary tables.

3.         

MSSQL Active Transactions

Number of active transactions.

4.         

MSSQL AU Cleanup Batches/Sec

The number of batches per second that were completed successfully by the background task that cleans up deferred dropped allocation units.

5.         

MSSQL AU Cleanups/Sec

The number of allocation units per second that were successfully dropped the background task that cleans up deferred dropped allocation units. Each allocation unit drop requires multiple batches.

6.         

MSSQL Auto-Param Attempts/Sec

Number of auto param attempts per second.

7.         

MSSQL Latch Wait Time (ms)/Sec

Latch wait time (in milliseconds) per second for latch requests that had to wait.

8.         

MSSQL Wait Time (ms)/Sec

Time(in millisecond ) for the selected type of wait per second.

9.         

MSSQL Backup and Restore Throughput/Sec

Read/write throughput for backup and restore operations of a database per second..

10.      

MSSQL Batch Requests/Sec

Number of Transact-SQL command batches received per second.

11.      

MSSQL Buffer Cache Hit Ratio (Pct)

Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.

12.      

MSSQL Bulk Copy Rows/Sec

Number of bulk copy rows per second.

13.      

MSSQL Bulk Copy Throughput (KB)/Sec

Amount of data bulk copied (in kilobytes) per second.

14.      

MSSQL By-Reference Lob Create/Sec

Number of large object (lob) values per second that were passed by reference. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by value.

15.      

MSSQL By-Reference Lob Used/Sec

Number of by-reference lob values per second that were used. By-reference lobs are used in certain bulk operations to avoid the cost of passing them by-value.

16.      

MSSQL Cache Hit Ratio

Ratio between cache hits and lookups.

17.      

MSSQL Cache Objects

Number of cache objects in the cache.

18.      

MSSQL Cache Objects In Use

Number of cache object in use.

19.      

MSSQL Cache Pages

Number of 8-kilobyte (KB) pages used by cache objects.

20.      

MSSQL Cached Cursors

Number of cursors of a given type in the cache.

21.      

MSSQL Checkpoint Pages/Sec

Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

22.      

MSSQL CLR Execution Time (mus)

Total execution time in CLR (microseconds).

23.      

MSSQL Connection Memory (KB)

Connection memory in killobytes.

24.      

MSSQL Lob Readahead/Sec

Number of lob pages per second on which readahead was issued.

25.      

MSSQL Pull In Row/Sec

Number of column values per second that were pulled in-row from off-row.

26.      

MSSQL Push Off Row/Sec

Number of column values per second that were pushed from in-row to off-row.

27.      

MSSQL Cursor Memory Usage (KB)

Amount of memory consumed by cursors in kilobytes (KB).

28.      

MSSQL Cursor Requests/Sec

Number of SQL cursor requests received by server per second.

29.      

MSSQL Cursor Worktable Usage

Number of worktables used by cursors.

30.      

MSSQL Data File Size (KB)

Cumulative size (in kilobytes) of all the data files in the database.

31.      

MSSQL Database Pages

Number of database pages.

32.      

MSSQL DBCC Logical Scan Bytes/Sec

Number of logical read scan bytes per second for database console commands (DBCC).

33.      

MSSQL Deferred Dropped Rowsets/Sec

Number of rowsets created per second as a result of aborted online index build operations that are waiting to be dropped by the background task that cleans up deferred dropped rowsets.

34.      

MSSQL Dropped Rowset Cleanups/Sec

Number of rowsets per second created as a result of aborted online index build operations that were successfully dropped by the background task that cleans up deferred dropped rowsets.

35.      

MSSQL Dropped Rowset Skipped/Sec

Number of rowsets per second created as a result of aborted online index build operations that were skipped by the background task that cleans up deferred dropped rowsets created.

36.      

MSSQL Errors/Sec

Number of errors per second.

37.      

MSSQL Event Notifications Delayed Drop

Number of event notifications waiting to be dropped by a system thread.

38.      

MSSQL Extent Deallocations/Sec

Number of extents deallocated per second in all databases in this instance of SQL Server.

39.      

MSSQL Extent Allocated/Sec

Number of extents allocated per second in all databases in this instance of SQL Server.

40.      

MSSQL Failed AU Cleanup Batches/Sec

The number of batches per second that failed and required retry, by the background task that cleans up deferred dropped allocation units. Failure could be due to lack of memory or disk space, hardware failure and other reasons.

41.      

MSSQL Failed Auto-Params/Sec

Number of failed auto params per second.

42.      

MSSQL Failed Leaf Page Cookie/Sec

Number of times per second that a leaf page cookie could not be used during an index search since changes happened on the leaf page. The cookie is used to speed up index search.

43.      

MSSQL Failed Tree Page Cookie/Sec

Number of times per second that a tree page cookie could not be used during an index search since changes happened on the parent pages of those tree pages. The cookie is used to speed up index search.

44.      

MSSQL Forced Parameterizations/Sec

Number of forced parameterizations per second.

45.      

MSSQL Forwarded Records/Sec

Number of records per second fetched through forwarded record pointers.

46.      

MSSQL Free List Stalls/Sec

Number of free list stalls per second.

47.      

MSSQL Free Space In Tempdb (KB)

The amount of space (in kilobytes) available in tempdb. There must be enough free space to hold both the snapshot isolation level version store and all new temporary objects created in this instance of the Database Engine.

48.      

MSSQL FreeSpace Page Fetches/Sec

Number of pages fetched per second by free space scans. These scans search for free space within pages already allocated to an allocation unit, to satisfy requests to insert or modify record fragments.

49.      

MSSQL FreeSpace Scans/Sec

Number of scans per second that were initiated to search for free space within pages already allocated to an allocation unit to insert or modify record fragment. Each scan may find multiple pages.

50.      

MSSQL Full Scans/Sec

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

51.      

MSSQL Granted Workspace Memory (KB)

Total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations in kilobytes.

52.      

MSSQL HTTP Authenticated Requests/Sec

Number of http authenticated requests per second.

53.      

MSSQL Index Searches/Sec

Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row.

54.      

MSSQL Latch Waits/Sec

Number of latch requests per second that could not be granted immediately.

55.      

MSSQL Lazy Writes/Sec

number of buffers written per second by the buffer manager’s lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.

56.      

MSSQL LobHandle Created/Sec

Number of temporary lobs handle created per second.

57.      

MSSQL LobHandle Destroyed/Sec

Number of temporary lobs handle destroyed per second.

58.      

MSSQL LobSS Provider Create/Sec

Number of lob Storage Service Providers (LobSSP) created per second. One worktable created per LobSSP.

59.      

MSSQL LobSS Provider Destroy/Sec

Number of lob SS provider destroyed per second.

60.      

MSSQL LobSS Provider Truncation/Sec

Number of lob ss provider truncates per second.

61.      

MSSQL Lock Blocks

current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.

62.      

MSSQL Lock Blocks Allocated

Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.

63.      

MSSQL Lock Memory (KB)

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

64.      

MSSQL Lock Owner Blocks

Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there are three lock owner blocks.

65.      

MSSQL Lock Owner Blocks Allocated

Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.

66.      

MSSQL Lock Requests/Sec

Number of lock requests per second.

67.      

MSSQL Lock Timeouts (Without NOWAIT)/Sec

Number of lock requests per second that timed out, but excluding requests for NOWAIT locks.

68.      

MSSQL Lock Timeouts/Sec

Number of lock requests per second that timed out, including requests for NOWAIT locks.

69.      

MSSQL Lock Wait Time (ms)

Total wait time (in milliseconds) for locks in the last second.

70.      

MSSQL Lock Waits/Sec

Number of lock requests per second that required the caller to wait.

71.      

MSSQL Log Bytes Flushed/Sec

Number of log bytes flushed per second.

72.      

MSSQL Log Cache Hit Ratio (Pct)

Percentage of log cache reads satisfied from the log cache.

73.      

MSSQL Log Cache Reads/Sec

Number of log cache reads per second.

74.      

MSSQL Log File Size (KB)

Cumulative size (in kilobytes) of all the transaction log files in the database.

75.      

MSSQL Log File Used Size (KB)

Cumulative used size of all the log files in the database.

76.      

MSSQL Log Flush Wait Time (ms)/Sec

Log flush wait time (in milliseconds) per second to flush the log. On an Always On secondary database, this value indicates the wait time for log records to be hardened to disk.

77.      

MSSQL Log Flush Waits/Sec

Number of commits per second waiting for the log flush.

78.      

MSSQL Log Flushes/Sec

Number of log flushes per second.

79.      

MSSQL Log Growths/Sec

Number of times per second the transaction log for the database has been expanded..

80.      

MSSQL Log Shrinks/Sec

Number of times per second log shrinks for this database.

81.      

MSSQL Log Truncations/Sec

Number of times per second the transaction log has been shrunk.

82.      

MSSQL Logical Connections

Number of logical connections to the system.

83.      

MSSQL Logins/Sec

Total number of logins started per second. This does not include pooled connections.

84.      

MSSQL Logouts/Sec

Total number of logout operations started per second.

85.      

MSSQL Longest Transaction Running Time (ms)

Transaction longest running time in miliseconds.

86.      

MSSQL Mars Deadlocks/Sec

Number of MARS deadlocks per second.

87.      

MSSQL Maximum Workspace Memory (KB)

Maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations in kilobytes.

88.      

MSSQL Memory Grants Outstanding/Sec

Total number of processes per second that have successfully acquired a workspace memory grant.

89.      

MSSQL Memory Grants Pending

Total number of processes waiting for a workspace memory grant.

90.      

MSSQL Mixed Page Allocations/Sec

Number of pages allocated per second from mixed extents. These could be used for storing the IAM pages and the first eight pages that are allocated to an allocation unit.

91.      

MSSQL Non-Atomic Yields/Sec

Number of non-atomic yields per second.

92.      

MSSQL NonSnapshot Version Transactions

Number of currently active transactions that are not using snapshot isolation level and have made data modifications that have generated row versions in the tempdb version store.

93.      

MSSQL Active Cursor Plans

Number of active cursor plans.

94.      

MSSQL Deadlocks/Sec

Number of lock requests per second that resulted in a deadlock.

95.      

MSSQL Current SuperLatches

Number of latches that are currently SuperLatches.

96.      

MSSQL Optimizer Memory (KB)

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

97.      

MSSQL Page Compression Attempts/Sec

Number of pages evaluated for page-level compression. Includes pages that were not compressed because significant savings could be achieved. Includes all objects in the instance of SQL Server.

98.      

MSSQL Page Deallocations/Sec

Number of pages deallocated per second in all databases in this instance of SQL Server. These include pages from mixed extents and uniform extents.

99.      

MSSQL Page Life Expectancy (Sec)

Number of seconds a page stays in the buffer pool without references.

100.   

MSSQL Page Lookups/Sec

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

101.   

MSSQL Page Reads/Sec

Number of physical database page reads that are issued per second.

102.   

MSSQL Page Splits/Sec

Number of page splits per second that occur as the result of overflowing index pages.

103.   

MSSQL Page Writes/Sec

Number of physical database page writes that are issued per second.

104.   

MSSQL Pages Allocated/Sec

Number of pages allocated per second in all databases in this instance of SQL Server. These include pages allocations from both mixed extents and uniform extents.

105.   

MSSQL Pages Compressed/Sec

Number of data pages that are compressed by using PAGE compression. Includes all objects in the instance of SQL Server.

106.   

MSSQL Log Used (Pct)

Percentage of space in the log that is in use.

107.   

MSSQL Probe Scans/Sec

Number of probe scans per second that are used to find at most one single qualified row in an index or base table directly.

108.   

MSSQL Processes Blocked

Number of currently blocked processes.

109.   

MSSQL Range Scans/Sec

Number of qualified range scans through indexes per second.

110.   

MSSQL Readahead Pages/Sec

Number of read ahead pages per second.

111.   

MSSQL Replication Transactions/Sec

Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.

112.   

MSSQL Safe Auto-Params/Sec

Number of safe auto params per second.

113.   

MSSQL Scan Point Revalidations/Sec

Number of times per second that the scan point had to be revalidated to continue the scan.

114.   

MSSQL Skipped Ghosted Records/Sec

Number of ghosted records per second skipped during scans.

115.   

MSSQL Snapshot Transactions

Number of currently active transactions using the snapshot isolation level.

116.   

MSSQL SOAP Empty Requests/Sec

Number of empty SOAP requests started per second.

117.   

MSSQL SOAP Method Invocations/Sec

Number of SOAP method invocations per second.

118.   

MSSQL SOAP Session Initiate Requests/Sec

Number of SOAP session initiate requests per second.

119.   

MSSQL SOAP Session Terminate Requests/Sec

Number of SOAP session terminate requests per second.

120.   

MSSQL SOAP SQL Requests/Sec

Number of SOAP SQL requests started per second.

121.   

MSSQL SOAP WSDL Requests/Sec

Number of SOAP Web Service Description Language requests started per second.

122.   

MSSQL SQL Attentions/Sec

Number of attentions per second. An attention is a request by the client to end the currently running request

123.   

MSSQL SQL Cache Memory (KB)

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

124.   

MSSQL SQL Compilations/Sec

Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state.

125.   

MSSQL SQL Re-Compilations/Sec

Number of statement recompiles per second.

126.   

MSSQL Stored Procedures Invoked/Sec

Number of stored procedures invoked per second.

127.   

MSSQL SuperLatch Demotions/Sec

Number of SuperLatches per second that have been demoted to regular latches in the last second.

128.   

MSSQL SuperLatch Promotions/Sec

Number of latches per second that have been promoted to SuperLatches in the last second.

129.   

MSSQL Table Lock Escalations/Sec

Number of times locks on a table were escalated to the TABLE or HoBT granularity.

130.   

MSSQL Target Pages

Ideal number of pages in the buffer pool.

131.   

MSSQL Target Server Memory (KB)

Ideal amount of memory the server can consume in kilobytes.

132.   

MSSQL Task Limit Reached

Total number of times that a queue monitor would have started a new task, but did not because the maximum number of tasks for the queue is already running.

133.   

MSSQL Task Limit Reached/Sec

Number of times per second that a queue monitor would have started a new task, but did not because the maximum number of tasks for the queue is already running.

134.   

MSSQL Tasks Aborted/Sec

Number of activation stored procedure tasks per second that end with an error, or are aborted by a queue monitor for failing to receive messages.

135.   

MSSQL Tasks Running

Number of activation stored procedures that are currently running.

136.   

MSSQL Tasks Started/Sec

Number of activation stored procedures started per second by all queue monitors in the instance.

137.   

MSSQL Temp Tables Created/Sec

Number of temporary tables/table variables created per second.

138.   

MSSQL Temp Tables For Destruction

Number of temporary tables/table variables waiting to be destroyed by the cleanup system thread.

139.   

MSSQL Total Latch Wait Time (ms)

Total latch wait time in milliseconds.

140.   

MSSQL Total Server Memory (KB)

Amount of memory the server has committed using the memory manager in kilobytes.

141.   

MSSQL Total Transactions

total number of transactions.

142.   

MSSQL Transactions/Sec

Number of transaction per second.

143.   

MSSQL Unsafe Auto-Params/Sec

Number of unsafe auto-params per second.

144.   

MSSQL Update Conflict Ratio (Pct)

Percentage of those transactions using the snapshot isolation level that have encountered update conflicts within the last second. An update conflict occurs when a snapshot isolation level transaction attempts to modify a row that last was modified by another transaction that was not committed when the snapshot isolation level transaction started.

145.   

MSSQL Update Snapshot Transactions

Number of currently active transactions using the snapshot isolation level and have modified data.

146.   

MSSQL Used Leaf Page Cookie/Sec

Number of times per second a leaf page cookie is used successfully during an index search since no change happened on the leaf page. The cookie is used to speed up index search.

147.   

MSSQL Used Tree Page Cookie/Sec

Number of times per second a tree page cookie is used successfully during an index search since no change happened on the parent page of the tree page. The cookie is used to speed up index search.

148.   

MSSQL User Connections

Number of user connections.

149.   

MSSQL Version Cleanup Rate (KB/Sec)

The rate (in kilobytes per second) at which row versions are removed from the snapshot isolation version store in tempdb.

150.   

MSSQL Version Store Size (KB)

The amount of space (in kilobytes) in tempdb being used to store snapshot isolation level row versions.

151.   

MSSQL Version Generation Rate (KB/Sec)

The rate (in kilobytes per second) at which new row versions are added to the snapshot isolation version store in tempdb.

152.   

MSSQL Version Store Units

The number of active allocation units in the snapshot isolation version store in tempdb.

153.   

MSSQL Version Store Unit Creation/Sec

Number of allocation units that have been created per second in the snapshot isolation store since the instance of the Database Engine was started.

154.   

MSSQL Version Store Unit Truncation/Sec

Number of allocation units that have been removed per second from the snapshot isolation store since the instance of the Database Engine was started.

155.   

MSSQL Workfiles Created/Sec

Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates.

156.   

MSSQL Worktables Created/Sec

Number of work tables created per second. For example, work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

157.   

MSSQL Worktables From Cache Ratio

Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache. (When a work table is dropped, two pages may remain allocated and they are returned to the work table cache. This increases performance.)

158.   

MSSQL Write Transactions/Sec

Number of write transactions per second.

Graphical View of Monitoring

MSSQL Service Stats

Graph Description Table

S. No.

Metric

Description

1

MSSQL Service Status

Service status.

1 means Stopped,

2: means Other (start pending),

3 means Other (stop pending),

4 means Running,

5 means Other (continue pending),

6 means Other (pause pending),

7 means Paused.

2

MSSQL Service Pid

PID of the service. PID is empty for stopped services

Graphical Representation of Monitoring

MSSQL Temp DB Stats

Graph Description Table

S. No.

Metric

Description

1

MSSQL Free Space (MB)

Total unallocated file space in Megabytes.

2

MSSQL Internal Objects Space (MB)

Total allocated space for internal objects in the file in Megabytes.

3

MSSQL User Objects Space (MB)

Total allocated space for user objects in the file in Megabytes.

4

MSSQL Version Store Space (MB)

Total allocated space for version store in the file in Megabytes.

Graphical Representation of Monitoring

MSSQL Connection Stats

Graph Description Table

S. No.

Metric

Description

1

MSSQL Connections

Number of connections established per database.

Graphical Representation of Monitoring

MSSQL Memory Utilization Stats

Graph Description Table

S. No.

Metric

Description

1

MSSQL Total Server Memory (MB

Total memory of database server.

2

MSSQL Used Server Memory (MB)

Total used memory of database server.

3

MSSQL Free Server Memory (MB)

Total free memory of database server.

4

MSSQL Used Memory (MB)

Total server memory used by MSSQL database.

5

MSSQL Used Memory (PCT)

Total server memory used by MSSQL database in percentage.

6

MSSQL Total Server Memory (PCT)

Total used memory of database server in percentage.

Graphical Representation of Monitoring

 

Query wise Performance Measurements

This is a powerful feature to measure the performance of each query contributing in a session execution. It provides complete monitoring of MSSQL queries with insights into SQL activity, wait statistics, databases, support services, Temp DB, and high availability. It enables a user to detect such queries, which are consuming more resources as compare to normal ones. Once identified, user can diagnose the issue and perform corrective measures.

Accessing MSSQL Monitoring Window

To view the MSSQL monitoring window, follow the below mentioned steps:

  1. Login to Product UI. This displays the Home page / Web Dashboard with various components. If Home page is displayed, go to Web Dashboard by clicking the Test Run icon from the left menu on Home Then, click a Test Run number / Session number from the list to perform the monitoring. This displays the Web dashboard for that particular test run / session.

2. Go to View > DB Monitoring > MS SQL Monitoring menu item.

This displays MSSQL monitoring window with various tabs, such as SQL Activity, Wait Statistics, Databases, and Support Services.

Note:

  • Each tab is further categorized into various sections. Details of each tab and its corresponding sections is provided in the subsequent topics.
  • User can perform some operations within tabs / sections. Details are provided within the “Operations to View Data” section.

Operations to View Data

User can perform some operations to customize the viewing of data, such as:

  • Selection of Database server
  • Configuration for viewing Real Time data samples
  • Configure Preset option for data samples
  • Configure View by based on the Preset selection
  • Rewind data samples to view historical data based on last sample time
  • Forward data samples which were rewind to view historical data
  • Filter or Search for records at column level
  • Show / Hide Columns based on the relevancy or requirements
  • Changing Chart types for representation of data differently
  • Download the records in CSV format for reference or analysis

Selection of Database server

User can select which database is to monitor. To do this, click the beside the Database icon . A drop-down list is displayed with the database names. Select the required data base for monitoring.

Viewing Real Time Data / Sample Data

User has an option to view either real time data or offline data. Real time data can be viewed for online tests only. To view real time data, click the Switch to Real Time  icon at the top-right corner of the window. This prompts the time (in seconds) for the interval. Select the interval, such as 15 sec, 30 sec, or 45 sec for viewing the data.

Default interval is 30 sec. User can change the interval from 30 sec to either 15 sec or 45 sec by clicking

The data is displayed based upon the interval selection. Post that, user further has two options:

  • Pause the real time data for a certain duration by clicking the icon.
  • Move out from the real time viewing of data by clicking the icon. 

Preset

It is the time for which the data is to be displayed for monitoring. For example – Last 10 minutes, last 30 minutes, last 1 hour, and so on.

Upon selecting the Custom option, user needs to specify the Start date / time and End date / time.

View by

In the View by option, user can specify the format of aggregated data (such as hours, minutes, and seconds) based on the selection of Preset option. For example, if user selects ‘Last 10 minutes’ in preset option, then ‘View by’ option contains ‘Minutes’ only. If user selects ‘Last 4 hours’ in preset option, then ‘View by’ contains ‘Minutes’ and ‘Hours’.

View Last Sample Time

User can view the last sample time on the header of each table and graphical representation. This sample time is based on configured progress interval.

Rewind

This feature is used to view the data of last samples. User can view the historical data based on the last samples by clicking the Rewind  icon.

Upon clicking this icon, data of previous sample is displayed and last sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:50:14 To 07/30/18 11:51:14, after clicking the Rewind icon, the data sample time becomes 07/30/18 11:49:14 To 07/30/18 11:50:14 and data is displayed accordingly. User can further click the Rewind icon to view the corresponding historical data. Each click displays the previous data based on sample interval.

To resume with the current time, click the  icon.

Forward

User can view the next to previous data (which was accessed by clicking the rewind icon) by clicking the  icon.

Upon clicking this icon, data of next sample is displayed and sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:49:14 To 07/30/18 11:50:14 after clicking the Forward icon, the time for sample data becomes 07/30/18 11:50:14 To 07/30/18 11:51:14 and data is displayed accordingly. User can further click the Forward icon to view the corresponding historical data. Each click displays the next data based on sample interval.

To resume with the current time, click the  icon.

Filter

User can filter the data or search for specific records based on the value provide in the text boxes that are displayed upon clicking the  icon.

For example: To view the records with data type as “Data”, user needs to specify “Data” within the “Data Type” column. This filtered out the records containing data type as “Data” and displayed accordingly.

Changing Chart Types

User can also change the chart type by clicking the  icon. There are various chart types available, such as Line, Pie, Stack Area, Bar, and Area.

Download as CSV

User also has an option to download the records in CSV format. To do this, click the  button at the top-right corner of the window. This download the records in CSV format.

These were some operations for viewing data in customized way and downloading the records in CSV format. In the subsequent topics, we are providing how we can analyze the query by monitoring various statistics.

SQL Activity

This section provides details of DB query for monitoring, such as DB Query Stats, Blocking Sessions, Locks, Sessions, I/O by File, Summary, and Deadlock. Details of these sub-sections is provided in the subsequent topics. 

DB Query Stats

In this sub-section, details of DB query is displayed along with SQL statement and execution plan. Upon doing mouse hover at query, complete query is displayed.

Upon clicking the DB Name, complete details of the query (with all columns) is displayed.

To know about all the details displayed for query, please refer the subsequent topic DB Query Stats (Tabular). The same details (as in above image) is displayed in tabular format there.

DB Query Stats (Tabular)

User can identify the slow DB queries and other details from this section. This displays various stats for DB query. Each one is described in the below table.

Column Name

Description

DB Name

Database name associated with the query.

SQL Handle

A token that refers to the batch / stored procedure that the query is part of.

Query

Query that is executed.

CLR Time

Average Time (in microseconds) consumed inside Microsoft .NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.

Total CLR Time

Total Time (in microseconds) consumed inside Microsoft .NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.

Elapsed Time

Average elapsed time, reported in microseconds, for completed executions of this plan.

Total Elapsed Time

Total elapsed time, reported in microseconds, for completed executions of this plan.

Weight (%)

Weightage of query execution based on elapsed time.

Logical Reads

Logical read indicates total number of data pages needed to be accessed from data cache to process query. Logical read = page access in memory.

Logical Writes

Average Number of logical writes performed by executions of this plan since it was compiled.

Physical Reads

Physical read indicates total number of data pages that are read from disk.

Physical read = page access from disk

Total Logical Reads

Total Number of logical reads performed by executions of this plan since it was compiled.

Total Logical Writes

Total Number of logical writes performed by executions of this plan since it was compiled.

Total Physical Reads

Total Number of physical reads performed by executions of this plan since it was compiled.

Wait Time

Avg Time difference between the query being executed and before getting CPU.

For example: Waiting Time=(elapsed time – worker time) / execution count

Last Wait Time

Last Time difference between the query being executed and before getting CPU.

For example: Last Wait Time= (last elapsed time – Last worker time)

Worker Time

Amount of CPU time (in microseconds), that was consumed by executions of this plan since it was compiled.

Creation Time

Time at which the plan was compiled.

End Offset

Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object.

Maximum Wait Time

Maximum Time difference between the query being executed and before getting CPU.

For example: Maximum Wait Time = (max elapsed time – max worker time)

Minimum Wait Time

Minimum Time difference between the query being executed and before getting CPU.

For example: Minimum Wait Time= (min elapsed time – min worker time)

Plan Handle

A token that refers to the compiled plan that the query is part of.

DB Resource

DB resource used in query execution plan.

Start Offset

Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.

Last Execution Time

Last time at which the plan started executing.

Total Execution Count

Total number of times that the plan has been executed.

Total Wait Time

Total Time difference between the query being executed and before getting    CPU.

For example: Total Wait Time= (total elapsed time – total worker time)

Total Worker Time

Total amount of CPU time, reported in microseconds, that was consumed by executions of this plan since it was compiled.

SQL Statement

This section displays SQL statement of the selected query.

Execution Plan

This section provides an interactive and intuitive way to view the query execution plan and statement. The focus is on the SQL Statement selected on the Query Execution Statistics table. This section displays the sequence of execution for the selected query plan.

By default, top 20 slow queries are visible base on IO. The user can view slow queries based on CPU or IO. The user can also increment or decrement the number of queries to view.

In case, the user needs to view all the queries (and not the slow queries), then need to select “All” option.

Upon clicking a node, user can view the node details.

To download the execution plan XML, click the  icon.

To download the execution plan as diagrammatically, click the  icon.

Actual Execution Plan

A new monitor named “MSSQLActualExecutionPlan” is provided to fetch actual execution plan from Ring Buffer. This is used to fetch only actual execution plan. Actual Execution Plan is fetched according to the Last Execution time of the query and the Event Time corresponding to the Actual plan fetched from Ring Buffer. Actual Plan corresponding to the Event Time that is closest to the Last Execution Time of the query and less than the Last Execution Time is displayed.

Blocking Sessions

Block (or blocking lock) occurs when two processes need access to same piece of data concurrently so one process locks the data and the other one needs to wait for the other one to complete and release the lock. As soon as the first process is complete, the blocked process resumes operation.

This section shows all connections that are currently either waiting on locks held by others or are causing others to wait. A list of sessions (parent sessions) that are blocking other sessions (child sessions) is displayed. Upon expanding such sessions, by clicking the  icon, it displays a list of those sessions (child sessions), which are blocked due to inappropriate execution of queries in the parent sessions.

       

It displays the details of blocked and blocking sessions in tabular format and number of blocked processes in graphical format. To view the blocking / blocked query details, click the query within the table. A pop-up is displayed with query details.

Upon clicking the Session ID, user is navigated to the Sessions tab where user can see the session details, such as SPID, SQL User, Batch Text, status, and so on. This is covered in detail in the Sessions section.

Blocking Sessions

The columns in Blocking Sessions table and its description is provided below:

Column Name

Description

Session ID

The unique session number for the session owning or requesting the lock.

Query

The query, which is blocking other queries or the blocked ones.

Wait Time

The time a query is waiting for a resource.

Waiting Resource

The resource that is in conflict. This often identifies a database and table.

Command

The current or previous command executed.

Login Name

User associated with session ID.

Program Name

The application program that the user is using (for example, Microsoft Word).

CPU Time

The total amount of CPU consumed by the session so far. Can be useful when deciding which sessions to kill.

Physical IO

The total amount of physical I/O consumed by the session so far. Can be useful when deciding which sessions to kill

Host Name

The name of the client computer.

Host Process

Process ID for processes that are running on an instance of SQL Server. These processes can be client processes or system processes

Status

The status of the session (Blocked, Blocking or both). For sessions at the head of the blocking chain (those that are not blocked), this also indicates if the session is Runnable or Sleeping.

Blocking Session ID

Session ID of the parent query

Database Name

Name of the database.

Server Name

Name of the server.

Number of Blocked Processes

This provides a graphical representation that shows number of blocked processes vs lead processes based on the sample interval.

User can zoom-out a section to find out the issue in a particular time by stretching it. To resume back to the previous state, click the Reset Zoom button.

By default, graphs of both Lead Process and Blocked process is displayed. To view a particular process graph from them, click either Lead Process or Blocked Process at the right side of the graph.

Lead Process

Blocked Process

To view the details at a certain time, mouse over to the graph.

Locks

Lock is acquired when any process accesses a piece of data where there is a chance that another concurrent process needs this piece of data as well at the same time. By locking the piece of data, we ensure that we are able to action on that data the way we expect. It displays information for SQL locks, resource types, and latches.

SQL Locks

The columns in SQL locks table and its description is provided below:

Field

Description

SPID

A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

Database Name

Name of the SQL server database

Request Count

Number of times, the requester has requested for the resource

Resource Type

This tells us what resource in the database the locks are being taken on.

It can be one of these values: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT, TABLE, ROW

Request Mode

The kind of lock  being applied to the resource, such as Shared, Exclusive, Update, IntentShared, IntentExclusive

Request Status

The status of the lock, such as Blocked, Blocking, or Granted

Login Name

MSSQL server login name under which the session is currently executing

Resource Types

This section displays details for resource types in graphical format. The details includes:

  • Resource Type – Wait / Sec
  • Resource Type – Request / Sec
  • Resource Type – Timeout / Sec

Latches

SQL Server latches are an internal SQL Server mechanism that serves to protect shared memory resources, like pages and memory data structures inside the buffer pool, in order to coordinate access to those resources and protect them from corruption.

Sessions

This section displays details of all the sessions. Upon clicking of SPID from any other section, user is navigated to this window.

On clicking SPID, wait statistics for the session is displayed. This provides information about the wait type and wait time acquired by the session.

On clicking Session Stats, it provides the aggregated data of session stats for selected SPID for the time duration applied in .

On clicking Sql Query and Sql Plan, it provides the complete query and plan.

Sql Query

Sql Plan

SQL Session Locks


SQL Session Batch Jobs


SQL Session Temp DB

SQL Session Blocking

The description of the columns in SQL Session table is provided below:

Field

Description

SPID

ID of the session associated with the request

SQL User

SQL Server Login name for this session

Batch Text

Full batch SQL for this session

Status

Status of the session, such as runnable, sleeping, blocked

RUNNING: This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.

SUSPENDED: It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, a waiting for communication on the network, or it is waiting for lock or a latch.

RUNNABLE: The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

PENDING: The request is waiting for a worker to pick it up. This means the request is ready to run but there are no worker threads available to execute the requests in CPU.

BACKGROUND: The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING: There is no work to be done.

Blocked By

Which SPID (if any) holds locks that this session is waiting on

Last Command

Current or previous command executed

DB Name

Name of the database this session belongs to

Mem (No. of Pages)

Number of pages in the procedure cache that are currently allocated to this process

Logical Reads

Number of logical reads performed for each request

Reads

Number of physical reads performed for each request

Writes

Number of physical writes performed for each request

Total CPU

CPU time consumed by the session since SQL server restarts. It is updated every time when execution is finished.

Total I/O

The sum of total reads and total writes

Total Logical Reads

Number of logical reads performed for the session

Total Reads

Number of physical reads performed for the session

Total Writes

Number of physical writes performed for the session

Current Wait Time

Amount of time this session has been waiting. It shows 0 if the session is currently not waiting.

Last Wait Type

Describes the type of wait that this session last waited on (or is currently waiting on)

Last Wait Resource

Describes the resource that this session last waited for (or is currently waiting for). It shows no data if the session is currently not waiting.

Last Batch Time

Time the last batch started execution

Tran Count

Number of open transactions which corresponds to the session’s trancount value

Program

Program that user is running to access SQL server

Host Name

Name of the client computer

Host Process

Workstation process ID number

Net Address

This is the IP address of the client’s computer network card

Net Library

Network protocol being used to establish communication between SQL server and the application

Login Time

Session creation time

Request ID

ID of the current request within the current session

Plan Handle

Identifier for the query plan. Plan handle is available only when the session is executing

Win Domain

Name of the windows domain that the specified user belongs to

Win User

Name of the windows account under which the user is logged on

I/O by File

Database servers internally uses file system for some operations. This window displays the stats for the same. The details are displayed in tabular and graphical format.

The description of the columns in I/O by File table is provided below:

Field

Description

Database Name

The name of the SQL Server database that contains the file

File Name

The name of the file whose I/O statistics are on display

File Type

The type of the file whose I/O statistics are on display. Data for SL server 2005 and above.

Total IO

The current rate (per second) at which SQL server is performing both physical read and write operations for this file (Addition of Reads rate and writer rate)

KB Read

The number of kilobytes read from this file since SQL server started

KB Written

The number of kilobytes written to this file since SQL server started

KB on Disk

Physical file size on disk in kilobyte.

Total Wait Time

Total number of milliseconds SQL server has spent waiting for I/O operations on this file since SQL server started

Reads Wait Time

Total number of milliseconds SQL server has spent waiting for physical read operations on the file since SQL server started

Writes Wait Time

Total number of milliseconds SQL server has spent waiting for write operations on the file since SQL server started

Current Reads Wait Time

The number of milliseconds SQL server has spent waiting for physical read operation on this file since the last time data was collected for this page.

Current Write Wait Time

MSSQL server login name under which the session is currently executing

Current Wait Time

The number of milliseconds SQL server has spent waiting for write operations on this file since the last time data was collected for this page.

Summary

This section provides a summarized view of various metrics.

User can change the graph type by clicking the  icon.

Deadlock

Deadlock occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked and waiting for the first process to release the lock.

Wait Statistics

This section provides insight into the resources waiting statistics, such as what is the wait type, for how long it is waiting, and so on. It provides such details in tabular format along with graphical format.

Wait Statistics (Tabular)

This section provides information about all the waits encountered by threads that are executed for each session. User can use this view to diagnose performance issues with the SQL Server session and with specific queries and batches. The columns in Wait Statistics table and its description is provided below:

SR No.

Column Name

Description

1

Category

Categories currently contributing to wait time.

2

Wait Type

Name of the wait type.

3

Waiting Task Total

Number of waits on this wait type. This counter is incremented at the start of each wait.

4

Total Wait Time

Total wait time for this wait type in milliseconds. This time is inclusive of Signal Wait time

5

Wait Time Rate

The rate at which waits on this wait type are occurring.

Calculated as, (current wait time – previous wait time) / interval

6

Max Wait Time

Maximum wait time on this wait type.

7

Signal Wait Time Total

Difference between the time that the waiting thread was signaled and when it started running.

8

Server Name

Name of the server.

Wait Statistics (Graphical)

This section shows wait statistics in graphical format where categories are listed at X – axis (along with sample interval) and Wait Time Rate (ms/s) at Y- axis.

Drilldown from Wait Stats to Session Stats

This section displays the drilldown from wait category to session screen. Need to first select the wait category in graph. For the selected graph, different wait type is displayed in table. Now, need to select a wait type to view additional information in session screen corresponding to the selected wait type.

On selecting the ‘Network’ category, all wait type for Network category is displayed in table.

On selecting the wait type, it re-directs to session screen showing all the sessions’ id. The data displayed is for the wait selected in the wait stats screen.

Databases

This section provides insight into SQL database that includes various details in tabular format, rows used (%), and rows memory (MB).

SQL Database Logs

The columns in SQL Database table and its description is provided below:

SR No.

Column Name

Description

1

DB Name

Name of the database

2

Data Type

It can be either log or data.

3

Status

Status can be ONLINE,  RESTORING,  RECOVERING, RECOVERY_PENDING, SUSPECT,  EMERGENCY  OFFLINE, DEFUNC

4

Total MB

Total space allocated in MB

5

Used MB

Total space used in MB

6

Free MB

Total free space out of allocated.

7

Growth

The percentage or MB at which the memory can be increased.

8

Recovery

Recovery model of the database. Possible values include:

SIMPLE

BULK_LOGGED

FULL

File Size Used (%)

This section displays the memory used (in percentage) by DB for a specific datatype at different interval.

User can change the chart type by clicking the  icon.

File Size (MB)

This section displays the memory used and memory free (in MB) in DB for a specific datatype.

Support Services

This section provides insight into SQL support service along with various statistics in tabular and graphical format. This section is further categorized into Support Status and Batch Jobs.

Support Status

SQL Support Service

The columns in SQL Support Service table and its description is provided below:

SR No.

Column Name

Description

1

Service Name

Name of the SQL Server Database Engine, Full-text, or SQL Server Agent service. Cannot be null

2

Start Type

Indicates the start mode of the service. The following are the possible values and their corresponding descriptions.

0: Other

1: Other

2: Automatic

3: Manual

4: Disabled

3

Status

Indicates the status of the service. The following are the possible values and their corresponding descriptions.

1: Stopped

2: Other (start pending)

3: Other (stop pending)

4: Running

5: Other (continue pending)

6: Other (pause pending)

7: Paused

4

Last Startup Time

The date and time the service was last started

5

Service Account

The account authorized to control the service. This account can start or stop the service, or modify service properties.

6

Server Name

Name of the server.

7

Process ID

The process ID of the service. Cannot be null.

Service Status History

This section displays the running and stopped services. The currently running services are displayed with ‘Green’ color and stopped ones are displayed with ‘Red’ color.

Batch Jobs

A batch job signifies execution of queries in batches. Here, user can have the consolidated view of all the jobs ran in the past along with their schedule for future executions. This section is further categorized into various other sub- sections, such as SQL Batch Jobs, Job History, and SQL Batch Job Status. 

The details of each sub-section is provided in the subsequent sections:

SQL Batch Jobs

Below table provides description of each column in the SQL Batch Job section,

Field

Description

Job Name

The name of the job

Job Category

The SQL agent job category assigned to the job

Enabled

Indicates whether the job is enabled to be executed

Last Run Outcome

The outcome of the last run (succeed of failed)

Current Status

The current status of the job (succeeded of failed)

SPID

The unique session number for the SQL agent job

Last Run Date

The date / time on which the job last ran

Last Run Finish

The time when the job was finished

Current Duration

Time duration between start time of the batch job (which is currently running) and current time

Last Run Duration

Time duration between start time and end time of the batch job(which has been completed)

Next Run Time

The date/ time for the next scheduled run

Description

A brief description of the job

Job ID

Unique ID for the job

Job History

User can view the stats of a particular job by clicking the Job Name.

Field

Description

Run Date

Date at which the step started to execute

Job Name

Name of the job for which the user is looking the stats

Step ID

ID of the step in the job. [In a batch job, multiple steps are defined, for each step, a unique ID is assigned]

Step Name

Name of the step. [This value is defined by the user who configures the batch job]

Message

Message associated with the step

Run Status

Outcome of the step, such as succeeded, failed, retry, and so on

Run Duration

Elapsed time in the execution of the step

Retries Attempted

Number of retries attempted when the step was last executed

SQL Batch Job Status

This section displays status of the batch job like where it is succeeded, failed, cancel, corresponding to the duration of the job.

Temp DB

The tempdb is a special system database used to store temporary objects and data like tables, views table variables, tables returned in functions, temporary objects and indexes.

Following are the description of the columns in the Temp Db table.

Field

Description

Session ID

The ID of the session generating Temp DB usage

Total Space

Total space in the Temp DB consumed by the database

Application

The application generating temp DB usage

Host

The name of the windows server hosting the SQL server instance

User

The SQL server login name for this session

Current SQL

The SQL statement last completed

Internal Object Space

The space consumed by internal objects by all tasks in the execution

User Object Space

The total space consumed by user objects in this session

Custom Queries

To quickly find out what queries and stored procedures are running slow, and to know details about current users, sessions, and processes, a user can execute some predefined functions, such as sp_WhoisActive, sp_who, and sp_who2. Apart from this, the user can also execute custom queries to analyze stats of the DB server, such as number of connected users, number of active users and so on. A new common tab is added named ‘Query Execution’. Within this, there are two sections – one is Query Execution (where user can select either the predefined functions or can specify the custom query) and another is Query Output (where the user can see the result).

The description of predefined functions are as below:

sp_whoIsActive: Procedure is used for activity monitoring. Provides performance, workload data and slow running quieries and procedures on SQL Server.

sp_who: Provides information about current users, sessions, and processes.

  • System process ID.
  • Status of the process.
  • Login name of the user.
  • Name of the user.
  • If the process is blocked, the SPID of the blocking process.
  • Database the process is using.
  • Command currently being executed.

sp_who2: Along with the data provided by sp_who, sp_who2 provides additional information.

  • Total CPU time of each process.
  • Total amount of disk reads for each process.
  • Last time a client called a procedure or executed a query.
  • Application connected.

On selecting Custom Query option, the user can execute any query and see the output in Query Output section.

Server Stats

Memory Stats:

Memory stats graph provides details about free memory and usage of total memory by database. The values of memory space displayed in the graph in MB.

  • Total memory of database server
  • Total used memory of database server
  • Total free memory of database server
  • Total server memory used by database

This graph displays data for time interval according to preset as in other screens and refreshes accordingly.

DB Configuration: 

Database Configuration table provides detail of different configuration options and settings related to the database performance.

Connection Stats:

Database connection stats provide details of number of connections created in the databases on that server, based on different users logged-in.

Connection Detail:

Connection Detail table provides detail like wait time, CPU, Status, SQL Handle etc. for a database selected from the graph above.

Configuration Setting

The user can configure logs within the ‘Configuration & Logs’ section. This can be viewed only if user has read-write permission. The user needs to click the  icon.

On selecting  icon, a pop up appears. Here user can change the values as per requirement.

The user needs to provide the following configurations:

  1. Sample Progress Interval: The interval at which data is updated in UI.
  2. Batch Job History Duration: To view job history for batch job based on the specified duration.
  3. Timeout for Custom Query: To terminate the custom query execution in case data from query is not received.
  4. Threshold for SQL Plan: To set the min & max value (threshold) for SQL Plan.

Appendix – Assigning Permissions to User

  1. Select “User Mapping” tab and map “master” DB to the user and map default schema to “sys”.

2. Select “Securables” tab from left, click search button, add the mssql server, and click OK.

3. Select the added server and grant explicit permission of “Connect SQL”.

4. Once the login is created, provide the following privileges to the user. Open query tab and assign privileges by running below query: 

GRANT VIEW server state to <user_name>;

For example, GRANT VIEW server state to cavisson:

5. Click OK.