You can view the memory usage, database usage, instance activity, and services of the SQL database.
This section covers the following key areas:
The Memory dashboard provides access to graphs that display details of memory usage for the currently monitored server.
The Summary dashboard allows monitoring how the SQL Server instance is using its physical memory. This dashboard features charts that allow you to perform a full investigation of memory-related issues on the server. Use this dashboard to review any possible memory-related performance issues, and then go to the Buffer Cache or Plan Cache dashboards for an in-depth investigation of such issues.
The Summary dashboard allows you to perform the following tasks:
The Status chart displays the main areas used by SQL Server for carrying out database operations within memory. Use these charts to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
Memory pressure is defined as the (100-100*Total Server Memory/Target Server Memory) or in other words, 1-accepted memory/requested memory by the SQL server. The percentage division shows the current memory pressure and the amount of memory used or free by SQL Server or entire server memory. Click on either the Machine RAM or the SQL Server metrics to investigate how these values change over time.
Page Life expectancy is the length of time that a database page will stay in the buffer cache without references. A value too low for your system indicates that pages are being flushed from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better. The Memory Used metric indicates the total percentage of dynamic memory, within the host’s physical RAM, that the server is currently consuming.
Alerts regarding memory pressure may indicates that:
The chart at the bottom of this section displays the metrics over time. Selecting the Configuration Parameters link allows you to view the memory related options on the server.
The Resource Pools (RP) metrics are supported only from SQL Server 2008 onwards. The middle division of the Memory Summary dashboard provides metrics which may help you to prevent resource pool memory related errors. For example:
The first chart in the Resource Pool section displays the resource pools available for application and server usage and alerts you if memory utilization occurs in any resource pool. The values are grayed out when the resource governor is disabled.
The list contains the following values:
Two resource pools “internal” and “default” are created when SQL Server is installed. Clicking on the resource pool displays how the usages changes over time. Further drilling into the databases related to this resource pool is also available starting with SQL Server 2014, for databases that have the In-Memory feature enabled. When using In-Memory databases, it is highly recommended to define a dedicated resource pool. The lower part of the Resource Pool section provides metrics on up to eight resource pools over time. Click the Properties tab to view the definition parameters of the resource pools on the server.
The Memory Plan metrics provide data on cache-related issues.
Slow running processes and Hit ratio issues Plan cache is a component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer attempts to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan. Low hit ratio may indicate a lack of memory resources, a new plan might also be compiled even if a similar plan exists in cache when:
Plan compilations are expensive though; if plans are flushed from cache only to be recompiled afterwards because of size constraints even more memory resources are consumed. Selecting the Plan Cache hit ratio allows you to investigate changes over time. Further drilling down leads to the Plan Cache dashboard.
Buffer cache (Also called Buffer Pool) is the place in system memory that holds data and index pages read from disk. This has two purposes:
Buffer Cache size is determined among other things by server memory and the target server memory specified in the Max Server Memory parameter. When that threshold is reached and SQL Server needs to read more pages, previously cached pages are discarded.
A low hit ratio may indicate lack of memory. It also indicates that the Buffer Cache cannot improve performance by reducing IO operations. As such, the Buffer Cache size can correlate to improved performance during a heavy workload.
Improving Buffer Cache hit ration in SQL Server 2014 is also possible by setting a Buffer Pool Extension on an SSD drive.
The hit rate for this cache should normally be above 90%. Selecting the Buffer cache hit ratio bar allows you to investigate these metrics over time. Further drilling down leads to the Buffer Cache dashboard.
Investigating In-Memory OLTP (XTP) Performance
New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance.
The Memory Usage of In-Memory (XTP) indicates the amount of data used by memory optimized tables. A large percentage indicates a server that heavily uses In-Memory OLTP features. It is recommended that you dedicate a specific resource pool to databases containing In-Memory OLTP objects hence ensuring proper allocation of memory resources among applications on the SQL Server.
The Oldest XTP Transaction currently active on the server indicates issues regarding long running XTP transactions. Any value over 20 seconds is regarded a warning to an un-healthy behavior on the server. Long running transactions should be avoided with memory-optimized tables. Such transactions increase the likelihood of conflicts and subsequent transaction terminations. A long-running transaction also defers garbage collection.
The longer a transaction runs, the longer In-Memory OLTP keeps recently deleted row versions, which can decrease lookup performance for new transactions.
Selecting the Oldest XTP transaction displays general information regarding the transaction and the session that had initiated it.
Investigating Major and Minor Memory Areas
The Major Memory Areas chart displays the amount of memory allocated to the Buffer cache, Plan cache, free memory and XTP. Use this chart to ensure that:
The Minor Memory Areas chart displays the main areas SQL Server uses for carrying out database operations within memory.
The display is broken down into each of the main cache areas:
Use this chart to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
The Buffer Cache dashboard investigates the utilization of the buffer cache of an instance, by reviewing the top-consuming objects, the page allocation, and the buffer cache hit rate of it.
The Buffer Cache table displays the objects that currently occupy the most space in the buffer cache (the Top N buffer cache objects). To improve performance, Foglight for SQL Server limits the number of records that can be displayed in this table to 300 rows, but by default, a maximum of 20 records are displayed.
To define global settings about data retrieval to the Buffer Cache table, use the Buffer Cache view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings.
The Buffer Cache table contains the following columns:
Column | Description |
---|---|
Database | The name of the database where the object resides. |
Owner | The user name of the object’s owner. |
Table | The table name (if the object is a table). |
Cached Size | The amount of cache used by the object. |
Index ID | The index identifier (if the object is an index). This metric can have one of the following values: - 0 — indicates that the object is a heap and not an index. - 1 — indicates that the object is a clustered index. - > 1 — indicates that the object is a non-clustered index |
File Group | The file group where the object resides. |
% of Object | The percentage of object in the cache. |
% of Cache | The percentage of buffer cache used by the object. |
Because the process of determining the largest objects in the cache is highly CPU-intensive, this table can take some time to populate.
The Page Allocations chart displays the amount of memory allocated to database pages, free pages, and stolen pages over the selected time range (by default, last 60 minutes). High volume of database pages requested by SQL Server can sometimes indicate that data is not properly indexed or that the query optimizer is not using the most efficient index. Free pages values that are consistently close to zero may indicate use of inefficient queries, or shortage of SQL Server cache memory. The value of the Free Pages metric is calculated as follows: <free pages> * 8K / 1024. Constantly high value of stolen pages (pages that were taken from the buffer cache to satisfy other memory requests) may indicate an overall system memory shortage.
The Buffer Cache Hit Rates chart displays the buffer cache hit rate over the specified time range. The buffer cache hit rate is the rate of logical reads being satisfied from the buffer cache, saving the need to carry out physical reads. The buffer cache contains database, free, and stolen pages, and its size is calculated as follows: <pages> * 8 K /1024. The buffer cache hit rate should normally exceed 90%; hit rate values that are constantly lower than 90% may result from extensive data operations, due to inefficient query planning.
The Plan Cache dashboard displays information about the SQL Server’s plan cache, which holds execution plans for stored procedures, triggers, ad hoc SQL, and so on. This dashboard can be used for tracking the plan cache hit rate, and, if the rate it too low, investigate which object plans are used frequently.
The various panes of the Plan Cache dashboard allow carrying out the tasks described in the following sections:
SQL Server 2005 and later objects In SQL Server 2005 and later versions, the following object types can appear in the chart:
The Plan Cache table displays objects that are currently stored in the plan cache. The plan cache contains executable plans for Transact-SQL, including objects such as stored procedures, triggers, views and defaults, as well as ad hoc and prepared SQL. To improve performance, Foglight for SQL Server limits the number of records that can be displayed in this table to 300 rows, but by default, a maximum of 20 records are displayed.
To define global settings about data retrieval to the Plan Cache table, use the Plan Cache view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings.
Foglight for SQL Server retrieves the largest plan cache entries first, so if any entries are not displayed, they are the smallest ones. The Plan Cache table contains the following columns:
Metric | Description |
---|---|
Database | The database where the object resides. |
Owner | The object’s owner. |
Object | The object name. |
Type | The object type. An object can have one of the following types: - Stored procedure - Prepared statement - Ad hoc query - Replication procedure - Trigger - View - Default - User table - System table - Check - Rule |
Use Count | The number of times this cache object has been used since the plan was created. |
Reference Count | The number of objects that reference this cache object. |
Used Size | The amount of space in the plan cache, in megabytes, which is allocated to this object. |
% Cache | The percentage of plan cache used by this plan. |
SQL Query Text | The name of the procedure, or the first 128 bytes of the batch submitted. |
Language | The language of the connection that created the cache object. Each connection can individually set an SQL Server language to be used for theconnection. Note: Running identical plans that use several, incompatible language formats decreases the plan cache hit rate. To avoid language issues, ensure that all identical plans use the same language. |
Date Format | The date format used by the connection that created the cache object. Note: Running identical plans that use several, incompatible date formats decreases the plan cache hit rate. To avoid date issues, ensure using the same date format for all applications. |
The Cache Size by Object Type chart displays the amount of memory allocated to the plan cache over time, and breaks it down by object type. This display allows viewing trends of plan usage, which can be used for improving the queries used for retrieving the plans.
The Object Types chart shows various statistics for each type of plan cache object. The Object Types list can be used for selecting the metric to be displayed in this graph. Each metric is displayed for each type of plan cache object.
The Object Types chart displays the following metrics.
Metric | Description |
---|---|
Hit Rate | The rate of logical reads that were satisfied from plans existing in the plan cache, divided by plan types. |
Number of Objects | The number of objects of each type that are currently stored in the plan cache. |
Use Rate | The rate at which each type of plan cache objects is being run (used). |
The Activity menu provides access to graphs that display both current and recent activity details for the currently monitored SQL Server instance. The first section contains instructions regarding the use of the various dashboards of the Activity menu for carrying out a root-cause analysis of possible performance issues. The following sections provide in-depth data about each dashboard.
The Activity menu provides two levels of information about the currently diagnosed SQL Server instance:
The Activity menu allows you to carry out the following tasks:
The SQL Instance Summary dashboard displays a breakdown for the selected time range (by default: last 60 minutes) of system and SQL Server activity metrics, such as CPU utilization and response time. The display also includes the inner division within the metrics (for example, total CPU utilization compared with CPU utilization by the SQL Server). All metrics are displayed in the Foglight for SQL Server real-time summary; however, the overview displays only the last snapshot (by default: 20 seconds). This dashboard allows viewing the source of a performance issue, by displaying the most immediate causes for this issue. Because the SQL Instance Summary dashboard displays the immediate performance indicators, most in-depth analyses can be carried out in other dashboards of the Activity menu.
The SQL Instance Summary dashboard allows carrying out the tasks detailed in the following sections:
The Connected Sessions chart displays SQL Server session information. This chart contains a plot graph, which displays the number of SQL Server sessions over time.
Sessions are broken down into the following categories:
A high number of active user sessions, that is, non-system sessions that are actively processing in SQL Server or that are waiting on locks, may indicate wait events that prevent such sessions from completing their activity and moving to idle state. For example, a CPU-intensive operation may result in a CPU wait event. A high number of idle user sessions can complicate their management and lead to significant resource consumption, as a result of the update and management operations required for such sessions.
The CPU Utilization chart displays the amount of CPU being used by SQL Server compared with the total being used by all processes in Windows. The top-consuming sessions in the system can be reviewed in the Sessions dashboard, by sorting the Sessions table by the CPU Usage column.
The SQL Server IO chart displays the following types of I/O activity:
Under optimal work conditions, the SQL Server uses logical page reads to read pages from the buffer cache. However, required pages that do not yet reside in the cache are being read from disk using physical I/O operations. A high value for the logical page reads indicates that SQL Server efficiently uses the memory allocated to its buffer cache. A high value for physical page reads, on the other hand, indicates that SQL Server is finding fewer pages already in memory, resulting in the need to perform more disk reads.
The SQL Server IO chart, which displays I/O activity for all sessions and for all SQL Server database files, is actually a summary of two different charts, found in the SQL I/O Activity dashboard. A more focused display can be obtained using the following steps. To view a breakdown of logical and physical reads per session:
To view current SQL Server I/O statistics for each SQL Server database file:
Response time is the full time (in milliseconds) it has taken a dummy query (by default: select 1) to get from the application to SQL Server and back.
Every time a real-time sampling interval starts (by default: 5 minutes), a query is sent and its response time value is displayed. Any value higher than 20 ms may indicate a performance issue. The Response Time chart displays the response over the defined time range.
The Cache Hit Rates chart displays the hit rates for the main SQL Server cache resources.
A hit rate indicates the rate at which SQL Server finds pages already in the cache memory, saving the need to carry out physical reads. Hit rates are shown for the following cache types:
A buffer cache hit ratio value that continually goes below 90% may indicate the need for reviewing the memory settings. Low procedure cache hit rates may indicate inefficient use of SQL cache memory, which increases the need for compilation.
To review how the cache hit rate affects the number of compiles and recompiles, review the Call Rates chart.
The Call Rates chart displays the rate at which various events are occurring in SQL Server. The following metrics are charted:
When many recompiles take place, the SQL Server’s CPU can become overloaded, slowing down everything running on that computer. When a predefined threshold is exceeded, the Recompiles alarm is invoked. The ReCompiles metric of this chart allows viewing whether the recompiling issue is a persistent one.
The SQL I/O Activity dashboard provides information, represented graphically by charts, about the physical and logical activity of all SQL Server’s sessions. In addition, this dashboard includes a chart that displays various statistics relating to how SQL Server data is being accessed and updated (for example, page splits and page allocations). An extra chart featured in this dashboard, Disk Queue Length, displays the disk activity for each logical disk, including activity generated by non-SQL Server processes. The charts displayed in this dashboard provide a summary of physical and logical I/O activity for all sessions. To view a breakdown of these activities per session, go to the Sessions dashboard and click the requested session.
The SQL I/O Activity dashboard allows carrying out the following tasks:
The following table identifies each of the main elements of the Foglight for SQL Server real-time summary page, and provides a link to display more information, corresponding to the logical dataflow within the Foglight for SQL Server Instance Overview page.
Main elements of the Real-time Summary Page:
View Name | Description |
---|---|
Instance properties | Identifies the Instance |
Sessions pane | Monitors General Session Statistics |
Background Processes pane | Monitors Background Processes |
Workload | The workload (average active sessions) graph for the SQL Server instance, plotted over the specified time range (by default: last 60 minutes). |
Process activity | Data flows. Monitors total activity |
Memory Activity pane | Monitors the SQL memory management |
Disk Storage pane | Monitors the disk storage |
Physical I/O operations | Tracks the physical I/O activity |
The main groups (panes) of gathered icons and gauges, which represent the main activity areas in the SQL connection process, are as follows:
Identifying the Instance The Instance identification indicators allow you to identify the currently monitored instance, its type and its operation period. These indicators are as follows:
The pane’s initial view only shows the date; to display the time as well, hover the mouse on the instance status icon.
The pane’s initial view only shows the OS name; to display the build and service packs as well, hover the mouse or click the OS name’s text.
The virtualization overhead metric holds value only for virtual hosts running on VMware ESX servers. Clicking the virtualization overhead indicator displays the Virtualization Resource Utilization pop-up.
The following components represent the data flow in the SQL Server operation, from the session to the physical disk storage:
The Sessions pane monitors all session types, that is, system, user and SQL Server Agent sessions. Using this pane allows viewing the response time compared with the number of sessions and the instance’s level of activity. A high response time value may result from a long queue, that is, an overly high percentage of active users.
Long queues can indicate one of the following issues:
Flow | Description |
---|---|
Batches | The rate at which batches of SQL statements are being submitted to SQL Server for execution. |
Received packets | The rate at which the SQL Server receives network packets from client applications. When this icon is yellow, clicking it displays text that describes the deviations that triggered this display, along with a representation of the rate at which SQL Server is encountering network packet errors. |
Sent packets | The rate at which network packets are being sent from SQL Server to client applications. When the color of the icon is yellow, clicking it displays text that describes the deviations that triggered this display, along with a graphic representation of the rate at which SQL Server is encountering network packet errors. |
The Process Activity pane displays the following parameters:
Parameter | Description |
---|---|
Total Processes | The total number of SQL Server processes, including both user and system processes. |
Blocked Processes | Number of processes that are waiting for another process to release a resource that the process is currently locking. Blocked processes can sometimes lead to bottlenecks. The Blocked Process indicator changes its color when one or more processes become blocked. |
Active transactions (No.) | The average number of active transactions for each database of the monitored instance. |
Error Log | The SQL Server and SQL Agent error logs. When scanning is enabled, Foglight for SQL Server scans the SQL Server logs and raises alarms upon finding error messages that contain any of the error log alert rules. These rules can be specified for all connections, or for the current connection, using the Log Scanning view in the Databases Administration dashboard. Hovering over this icon displays the number of errors that were recorded in the SQL Server error log during the selected time range (by default, last 60 minutes). |
The flow from the SQL Memory pane to the SQL Processes pane indicates the rate at which pages in the buffer cache are being referenced by SQL connections (logical page reads). Normally, the majority of logical reads is satisfied from the cache, but if the required page is not yet in the cache, it is read from disk.
The Compiles flow displays the rate of SQL compilations and recompilations per second. Preferably, the most frequently used executable query plans should be retrieved from the procedure cache, thereby saving the need to compile, significantly reducing the utilization of CPU resources, and speeding up the response time of SQL Server queries. Recompiling, which is a CPU-intensive process that may degrade performance, can in certain cases be avoided through sound coding practices. When many recompiles take place, the SQL Server’s CPU can become overloaded, thereby slowing down everything running on that computer. As a result, the Recompiles alarm is invoked. The Call Rates chart, displayed in the SQL Instance Summary dashboard of the SQL Activity dashboard, details the use of compiles and re-compiles during the last 60 minutes.
The Memory Activity pane allows monitoring the SQL Server’s dynamic memory management and ensuring its proper handling of the buffer pool. Each page of memory used by SQL Server is assigned to one of several cache types. Each cache grows and shrinks in size as required. The main caches are the Buffer Cache, which stores a copy of the SQL Server’s most recently used database pages, and the Procedure Cache, which holds recently compiled query execution plans. Both these cache types should satisfy I/O requests and save the need for physical reads from the disk. The efficiency of the Buffer cache also affects the Page Life Expectancy, another indicator displayed in the SQL Memory pane.
The SQL Memory pane displays the following parameters:
Parameter | Description |
---|---|
Summary section | |
Total (MB) | The total amount of memory, either fixed or dynamically allocated, which the SQL Server is currently using. When the amount of SQL Server memory available for immediate reuse drops below a threshold, the Free Buffers alarm is invoked. |
Memory Used | Displays the total amount of memory, either fixed or dynamically allocated, that the SQL server is currently consuming, scaled against the maximum amount of memory it can use. By default, SQL Server manages its total memory automatically, adjusting it to the varying memory requests of both SQL Server processes and Windows processes. Clicking this gauge allows accessing the Memory > Buffer Cache dasbhoard, which provides an in-detail view of SQL Server’s memory management. |
Plan Cache Section | |
Total | Displays the amount of memory currently allocated to the plan cache (formerly known as procedure cache), a memory area used by SQL Server to hold recently compiled query execution plans stored procedures, triggers, ad hoc SQL, and so on. The value of this metric is calculated as follows: <pages> * 8K / 1024 |
Hit Rate | Displays the percentage of plan cache lookups that found the required plan already in the cache. If a matching plan is found, SQL Server does not need to compile the query/stored procedure. This can save a significant amount of CPU resources, and can speed up SQL Server queries. A low Plan Cache hit rate may lead to performing extra compilations, thereby degrading SQL Server performance by causing extra CPU load. In a such situation, the Procedure Cache Hit Ratio Alarm is invoked. The plan cache hit rate is the average hit rate for all procedure cache object types, except for ad hoc SQL. This rate is calculated using a differential sampling method, which gauges only the last few sampled periods. |
Buffer Cache Section | |
Total | Displays the amount of memory currently allocated to the buffer cache, including database, free, and stolen pages. The buffer cache, which is an in-memory copy of recently used database pages, is normally the largest memory cache used by SQL Server. If an SQL process needs to access a database page, finding this page in the buffer cache spares the SQL Server the need to read the page from disk, thereby significantly reducing the amount of disk I/O and speeding up queries. The value of this metric is calculated as follows:<pages> * 8K / 1024 |
Summary section | |
Hit Rate | Displays the percentage of database page I/O requests that were satisfied from the buffer cache. High buffer cache hit rate indicates that SQL Server efficiently uses the memory allocated to its buffer cache. Low buffer cache hit rate, on the other hand, indicates that SQL Server is finding fewer pages already in memory, resulting in the need to perform more disk reads. Low buffer cache hit rate invokes the Buffer Cache Hit Ratio alarm. |
Page Life Expectancy | Displays the current Page Life Expectancy, that is, the length of time in seconds that a database page stays in the buffer cache before it is flushed out. Small values indicate that the buffer cache retains pages for short periods, as a result of high data cycling, and that the buffer cache is not being effective. Microsoft recommends 300 seconds as the minimum value for this metric; any less is indicative of a shortage of memory. |
The physical I/O Operations section of the Foglight for SQL Server Instance Overview page displays graphical representations of the number of pages physically read from and written to disk by SQL Server. In addition, this section allows viewing the SQL Server processes Checkpoint and Lazy Writes.
Parameter | Description |
---|---|
Physical Writes | A flow that represents data/index pages written to disk per second. Normally, SQL users do not have to wait for database write operations to complete, as most modifications to database pages are made in the buffer cache. |
Physical Reads | A flow that represents page reads from the disk per second. Physical reads are used when a connection requests a page that is not already in the buffer cache. Physical read operations are necessary when accessing new table or index pages. Nevertheless, these operations should be avoided when possible, as requested pages should reside in the SQL Server buffer cache. |
Log Flushes | A flow that represents the number of log pages per second being written to disk by the Log Writer process. |
The Disk Storage pane displays the status of the storage devices in the SQL Server system, that is, databases and their backup, data files, and log files. Using this pane allows identifying storage issues such as a database that has not been backed up or a data file that has almost exhausted its growth potential and is going to fill completely. In addition, the Disk Storage pane displays the Log Flush wait time alarm, which may indicate excessive I/O operation.
The Disk Storage pane displays the following parameters:
Parameter | Description |
---|---|
Databases | The total number of available databases in the SQL Server instance. This icon displays an alarm when one or more of the databases becomes unavailable, or if any database has not been backed up in the last few days. |
Data Space | The total number of data files in all databases of the SQL Server instance. The disk icon represents the total percentage of currently used space within the entire capacity of all data files that reside on all databases. The total percentage is also displayed in text at the bottom of this section, below the text that displays the physical size of data files space used. |
Log Space | The log files in all databases of the SQL Server instance.The total percentage is also displayed in text. |
Disk Activity section | |
Disk Utilization | The percentage of time the busiest disk spent serving system-wide I/O requests. The Disk Utilization metric serves as a measure for the system I/O load. High values may indicate a device bottleneck, due to either disk fragmentation or I/O resource contention of multiple processes that try to write or read from the disk. |
Disk Queue Length | The average number of I/O requests that are queued and waiting for an available disk during the sample interval. This figure may include I/O activity generated by both SQL Server and non-SQL Server processes. |
The Background Processes pane displays the status of optional SQL Server components, such as the Replication Agent, which is implemented as a job of SQL Server Agent service, and several of the SQL Server services. Some of the supporting services significantly enhance the database efficiency and accuracy, and therefore should be active at all times; for example, DTC, which ensures successful and complete transactions.
The Background Processes pane contains the following indicators:
Indicator | Description |
---|---|
Replication | Indicates whether SQL Server Replication is configured. If replication is configured, indicates whether there are replication errors. Replication is an optional component of SQL Server, which synchronizes data between different SQL Server databases. |
Services | Status of the SQL Server services that are currently monitored. These services are supplementary to SQL Server and provide capabilities such as mail and full text search. This icon invokes an alarm when any of the SQL Server services are installed, but not active. |
The SQL Server Physical I/O chart displays the rate at which pages are physically read from and written to disk by SQL Server. The read and write operations are carried out by either SQL Server processes or system processes.
Physical read operations are necessary when creating a table or an index page for the first time. Nevertheless, these operations should be avoided when possible, as requested pages should reside in the SQL Server buffer cache. High rates of these metrics indicate extensive data operations.
The following list presents the main physical I/O types shown in the chart:
The SQL Server Logical I/O chart displays the number of Logical reads (getpage requests) issued by SQL Server. SQL Server keeps a copy of its most recently used database pages in the buffer cache. When a connection needs to reference a database page, SQL Server performs a Logical I/O operation by checking the buffer cache to see if the requested page is already in memory. If the page is found in the buffer cache, a Logical I/O read is carried out; otherwise, the page is read from disk, using a Physical I/O operation.
The Disk Queue Length chart displays the disk activity for each logical disk. Disk Queue length tracks the average number of I/O requests that are queued and waiting for an available disk during the sample interval. This figure may include I/O activity generated by processes other than SQL Server. Values that exceed the threshold set in this metric may indicate a system bottleneck.
Storage Area Network (SAN) storage array may indicate high Disk Queues for sustained periods, even though no disk I/O bottleneck takes place. Therefore, it is advisable to check related information about read, write, and transfer operations, and determine whether there are pending SQL Server I/O operations, before assuming the occurrence of I/O bottlenecks.
The Access Methods chart allows viewing various statistics used for monitoring the methods used for accessing and updating SQL Server data. These counters are all collected from the SQL Server Access Methods Manager.
The Access Methods list allows you to choose one of the following counters:
Counter | Description |
---|---|
Page Splits | Displays the rate at which index database pages are being split into two because the page does not contain enough free space to accommodate an inserted or updated record. |
Page Allocations | Displays the rate at which pages are being allocated to tables or indexes. Indicates how fast tables are expanding. |
Page Deallocations | The rate at which pages are being de-allocated from tables or indexes, thereby indicating how fast tables are shrinking. |
Freespace Scans | The number of scans initiated to search for free space to insert a new record. A high rate of freespace scans can indicate disk fragmentation. |
Forwarded Records | The rate at which SQL Server is retrieving forwarded records. When a row in a table that does not have a clustering index is updated - and if the modified row no longer fits on the old page - SQL Server moves the row to a new page and leaves a forwarding pointer in the old page. Using this method saves the need to modify the non-clustered indexes in order to reflect the new row location; however, when this method is implemented, subsequent retrieval of this row using non-clustered indexes requires extra I/O resources. A high Forwarded Records rate can indicate a need to reorganize the existing tables (unload/reload) or define clustered indexes. |
Ghosted Records | The rate at which SQL Server encounters ghosted records during scans. When a record is deleted from a table, SQL Server improves concurrency by not physically removing the row from index Leaf levels, but marking it instead as deleted (ghosted). At some later point, a housekeeping process asynchronously removes these rows from the leaf level. Until the records are removed, SQL Server must skip the ghosted records during leaf-level scans. |
Table Lock Escalation | The rate at which locks are being escalated to the table level. A high table lock escalations rate may indicate a need to tune queries, in order to avoid designing multiple page locks that continually force SQL Server to carry out such escalations. |
The Sessions dashboard allows you to trace the activity of all currently opened sessions, as well as their resource consumption.
The Sessions dashboard is divided into 3 views:
The resource consumption section is divided into 2 time frames, Current and Last Hour. The Current section allows viewing the consumption of various resources of all the sessions currently opened to the instance. The resource list is as follows:
By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events
The current sessions view allows you to monitor the currently running sessions and their individual resource consumption. The Session List section contains a table that lists all currently running SQL Server sessions, displaying each session in a separate row. The Sessions view in the table can be filtered by the Active only and Foreground Only check boxes:
The current Sessions table contains the following information:
Column | Description |
---|---|
Kill | Allows termination of the selected session, using the Kill Session dialog boxthat is displayed upon clicking this column. |
SPID | The session unique identifier (Session Process ID). |
Status | Indicates whether the session is active, blocked or suspended. |
DB User | The SQL Server login name for this session. |
Database | The name of the database the user is accessing. |
CPU Usage | Time spent by the various sessions consuming CPU cycles. The CPU usage value is read directly from the operating system. |
Memory Usage | The portion, in megabytes, of the procedure cache allocated to this process. A negative number indicates that the process is freeing memory allocated by another process. |
Active Time | Sum of all the active wait events, equal to the session total activity within the current interval. |
Transaction Count | The number of open transactions. This parameter’s value corresponds to the value of the session’s @@trancount — a global variable that reflects the level of nested transactions. |
Last batch | The latest batch ran by this process. |
Login Time | The time when the session was created. |
Program | The program that is the owner of this SQL Server session. |
Host Name | The name of the client computer that established the SQL Server connection. |
Last Batch | Time The time when the last batch started execution. |
Blocked by | Which SPID (if any) holds locks on a resource on which this session is waiting. |
Cache Hit Ratio | The percentage, for this session, of buffer cache hit ratio, that is, file read operations that were satisfied by the file system cache without requiring any physical I/O. The value of this metric should be as high as possible. |
Physical Writes | The total number of data/index pages written to disk by the selected session. Normally SQL users do not have to wait for database write operations to complete. Most modifications to database pages are made in the buffer cache. |
Logical Reads | The total number of logical reads carried out by the selected session. |
Physical Reads | The total number of physical reads carried out by the selected session. |
Activity | Indicates whether the session is active. |
Context Info | The context information of the current session. |
Last Command | The currently executed or most recently executed command. |
Lock Timeout | The amount of time that this session will wait for lock requests to be satisfied; corresponds to the @@Lock_Timeout global variable for this session. |
Session Up Time | The amount of time since the session is started (dd:hh:mm:ss). |
Resource Pool name | The name of the resource pool attached to the session |
XTP Transactions | The number of In-Memory OLTP (XTP) open transactions by the session |
Oldest Transaction Duration | The duration (in seconds) of the oldest transaction opened by the session |
To accelerate the retrieval time of a session, only the first 60 characters of the SQL text of each query are being retrieved.
The refresh rate of the data displayed on the Sessions dashboard can be set by selecting a value from the Refresh interval drop-down list, which appears on the upper right side of the dashboard.
The various sections of the Session Details page provide detailed information about the selected session upon selecting a specific session in the table. Configuring SQL PI presents in-depth analysis of the session details.
Session Identification Section This section displays several parameters that provide general information about the selected session, as follows:
Parameter | Description |
---|---|
Session ID | Session Process ID; the unique number that SQL Server has assigned to identify the selected session. Note: Clicking on the Session ID enables you to switch sessions from a pop-up list of current sessions. |
Login Time | The time when the user logged on to the session. |
Status | Indicates whether the session is active or inactive. |
DB User | The SQL Server login name for this session. |
Program | The name of the program the user is running to access SQL Server. |
Machine | The name of the host the session is running from. |
Waiting for | The resource on which the session is waiting. |
Viewing Workload details: The Workload page displays the selected session workload (Seconds/Sec) during the specified time range (default 1 Hr.). The breakdown displays the workload, distributed by the various wait event categories. The colors of the categories match the colors of the various resources displayed in the Resource Breakdown section. By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events.
CPU Section: Displays the sum amount f CPU (in seconds) that the selected session is consuming.
IO Section: Displays the sum amount of IO (in pages) that the selected session is consuming.
Session Details Section: The Session Details section contains several tabs of information.
Viewing the Most Recent SQL details: This tab provides details about the statement that is currently being executed by the selected session. This pane contains a table with the following columns:
Column | Description |
---|---|
SQL Batch | The full text of the SQL execution batch. |
CPU Time | Total CPU time consumed by the statement |
Physical IO Reads | The total number of physical I/O reads performed by the selected SQL statement. |
Physical IO Writes | The total number of physical I/O writes performed by the selected SQL statement. |
Active Time | Summary of all Sum of active wait events incurred by the SQL statement. |
Logical Reads | The total number of logical reads carried out by the selected SQL statement. |
Waiting On | The resource on which the session is waiting. |
Viewing Metrics: Additional columns are displayed in the Metrics table when SQL PI is configured. The Metrics pane contains the sum of activity for the selected session in a table that displays following metrics:
Column | Description |
---|---|
CPU Usage | Time spent using CPU. Calculated in seconds. |
Transaction Count | Number of open transactions for the session. |
Physical Reads | Total number of physical reads operations performed by all requests running in the instance. |
Physical Writes | Total number of logical write operations performed by all requests running on the instance. |
Logical Reads | Total number of logical read operations performed by all requests running on the instance. |
Cache Hit Ratio | The ratio of logical reads to physical reads. It indicates the percentage of database page I/O requests that were satisfied from the Buffer Cache and therefore did not have to perform disk reads |
Memory Usage | Amount of memory |
Active Time | Sum of all the active waits and CPU usage, equal to the session total activity within the current interval. |
Viewing Session Trace details: The Session Trace pane uses SQL Server trace functionality to display the SQL events activity generated by the selected session. The retrieval of the data displayed in the Session Trace pane starts upon entering the pane, and retrieval continues throughout the session investigation run by Foglight for SQL Server. Two minutes after exiting the session pane, data retrieval for the specific session is automatically disabled.
The Session Trace pane allows you to trace the following events:
Event | Description |
---|---|
RPC: Completed | Occurs when a remote procedure call (RPC) has completed |
SQL: Batch Completed | Occurs when a Transact-SQL batch has completed. |
Event | The type of class that was traced. |
Start Time | The time when the class event started. |
End Time | The time when the class event ended. |
Duration | The duration of the class event in milliseconds. |
Text Data | The query’s text. This column displays data only if the event class or classes being captured by the trace contain text. |
Reads | The number of logical disk reads performed by the server on behalf of the event. |
Writes | The number of physical disk writes performed by the server on behalf of the event. |
CPU Usage | The amount of CPU time (in milliseconds) used by the event. |
Database ID | ID of the database currently used by the session. |
Object Name | System-assigned ID of the object. |
Index ID | ID for the index on the object affected by the event. |
Viewing Session Blocks details The Session Blocks pane displays all blocks held or requested by the selected session. The Lock Status column indicates whether the locks have been granted to the session, or are currently blocked and waiting on another session. The Session Blocks table displays the following columns:
Column | Description |
---|---|
Session ID | The session unique identifier (Session Process ID). The unique number that SQL Server has assigned to identify the selected session. |
SQL User | The SQL Server login name for this session. |
Type | The type of the currently locked resource (Database, table, page, row, extent, and more). |
Status | The status of the lock, for example, running or blocked. |
Wait time | The time the session is waiting on lock. |
Resource | The name of the currently locked object. |
Command | The currently executed command. |
Program | The name of the program the user is running to access SQL Server. |
Win user | The Windows login name for this session. |
CPU | The amount of CPU time, in milliseconds, used by the event. |
IO | The total number of I/O operations performed by the selected SQL statement. |
Host Name | The name of the host the session is running from. |
DB Name | The database on which the lock is taking place. |
Session SQL | The full text of the SQL execution batch. |
Viewing Session Locks details The Locks table displays information about all locks currently held or requested. The Locks table displays the following columns:
Column | Description |
---|---|
SPID | The server process ID of the current user process. |
Database | The database being used by the current process. |
Count | The number of locks of the type specified in the Lock Type column against the database specified in the Database column, from the SPID specified in the SPID column. |
Index Name | The index being used by the current process, if any. |
Object Name | The currently locked object. |
Lock Type | The type of the resource that is currently locked. For example, database, table page, row, or extent. |
Mode | The kind of lock being applied to the resource. For example, shared, exclusive, update, IntentShared, or IntentExclusive. |
Status | The status of the lock: Blocked, Blocking, or blank (Granted) |
Configuring SQL PI enhances the Current session details by adding the next enhancements:
Viewing SQL Summary details The SQL Summary pane displays summarized data about each of the SQL Statements that were executed by the session. The SQL statements are sorted into a table that represents the activity of each SQL statement that ran or still running on the selected session.
The SQL Summary table displays the following columns:
Column | Description |
---|---|
SQL Statement | The SQL statement’s text. |
CPU Usage | Total CPU usage consumed by the statement. |
CPU Wait | Time spent by waiting in the systems run queue for CPU cycles. This reading is calculated from the operating system readings, rather than the SQL Server wait states. |
I/O Wait | Time spent waiting for disk input/output operations to complete. |
Memory Wait | Time spent waiting by the various processes waiting for the completion of a log operation. |
Network Wait | Network wait events occur when a session spends time waiting for messages to be sent or received over the network interface. |
Lock Wait | Time spent waiting for a blocking lock, held by another session, to be released. |
Log Wait | Time spent waiting for a Log operation to complete. |
Current Wait Time | The number of seconds SQL Server has spent waiting for I/O operations on this file since the last time data was collected. |
Latch Wait | Time spent waiting for internal locks (latches) to be released. |
CLR Wait | Time spent waiting for CLR code execution to complete. |
Remote Provide Wait | The time waiting for a remote OLEDB call to complete or DTS synchronization. |
Other Wait | Time waiting for miscellaneous log waits. |
SQL Batch | The full text of the SQL execution batch. |
Elapsed Time | The amount of time, in seconds, that the SQL statement’s execution lasted. |
Physical IO Reads | The total number of physical I/O reads performed by the selected SQL statement |
Physical IO Writes | The total number of physical I/O writes performed by the selected SQL statement. |
Logical Reads | The total number of logical reads carried out by the selected SQL statement. |
Command Type | The type of the command that was carried out by the selected SQL statement. For example, insert, select. |
The dashboard displays the SQL Batch that is executed for the selected SQL statement in the table and a pie chart which display the SQL statement workload activity.
Selecting a metric row from the metric list present this metric activity in a chart. The data displayed in the chart represents the metric activity over the selected time frame. The following columns are added the metrics table when SQL PI is configured:
Column | Description |
---|---|
Backup Recovery | Time spent waiting for backup or recovery tasks to complete. |
Cursor Synchronization | The time spent synchronizing information flow within cursors |
Database Replication | Time spent waiting for replication synchronization events to complete. |
Deferred Task Worker | Time spent waiting for I/O requests. |
Distributed Transaction | Time spent waiting for various distributed transaction events to complete. |
External Procedure | Time spent waiting for external procedures to end. |
Full Text Search | Average CPU consumption by the Full Text Search service. |
Hosted Component | Time spent waiting for hosted components such as, but not exclusively, CLR. |
I/O Bulk Load | Time spent waiting for the completion of I/O operations required to carry out a bulk load I/O. |
I/O Completion | Time spent waiting for I/O operations to complete. |
I/O Data Page | Time spent waiting to latch a buffer for an I/O request. |
Internal Cache Latch | Time spent waiting for latches that are not buffer latches or savepoint latches. |
Latch Buffer | Time spent waiting to latch a buffer that is not an I/O request. |
Latch Savepoint | Time spent waiting to synchronize commits to marked transactions. |
Lock Bulk Update | Time spent waiting to acquire bulk update locks. |
Lock Exclusive | Time spent waiting to acquire exclusive locks. |
Lock Intent | Time spent to acquire intent locks. |
Lock Schema | Time spent waiting to acquire schema locks |
Lock Shared | Time spent waiting to acquire shared locks. |
Lock Update | Time spent waiting to acquire update locks. |
Log Buffer | Time spent waiting for space in the log buffer or otherwise waiting for memory to be made available to write log records. |
Log Other | Time spent waiting for miscellaneous log. |
Log Synchronization | Time spent waiting to see whether log truncation frees log space. |
Log Write | Time spent waiting for outstanding I/O to finish, or waiting for log flushes to complete. |
Network HTTP | Time spent waiting for outstanding HTTP connections to complete and exit. |
Network I/O | Time spent waiting for network packets. |
Network IPC | Time spent waiting for sub-tasks to generate data; long waits are indicative of unexpected blockages. |
Network Mirror | Time spent waiting for sub-tasks to generate data; long waits are indicative of unexpected blockages. |
OLEDB Provider Full Text | Time spent waiting for the Microsoft Full Text Engine for SQL Server. |
Other Miscellaneous | Time spent waiting for miscellaneous database operations. |
Parallel Coordination | Time spent waiting for parallel coordination tasks to complete. |
Service Broker | Time spent waiting for Service Broker event handlers and endpoints |
Synchronous Task | Time spent waiting for tasks started synchronously. Most SQL Server processes are started Asynchronously. |
The Session Blocks and Session Locks tabs are not presented when drilling to the session’s details from the Last 1 Hour view.
The Locks dashboard displays information about all locks and latches in the currently monitored SQL Server instance.The information is provided by the panes described in the following topics:
The Locks table displays information about all locks currently held or requested. To configure the default retrieval settings for this dashboard, use the Lock view in the Databases Administration dashboard. The Locks table displays the following columns:
Column | Description |
---|---|
SPID | The server process ID of the current user process. |
Database | The database being used by the current process. |
Count | The number of locks of the type specified in the Lock Type column against the database specified in the Database column, from the SPID specified in the SPID column. |
Index Name | The index being used by the current process (if any). |
Object Name | The currently locked object. |
Lock Type | The type of the resource that is currently locked (Database, Table, Page, Row, Extent, and so on). |
Mode | The kind of lock being applied to the resource (Shared, Exclusive, Update, IntentShared, IntenExclusive, and so on). |
Status | The status of the lock: Blocked, Blocking, or blank (Granted). |
Login Name | SQL Server login name for this session. |
The Lock Types chart shows lock statistics broken down by the various types of locks available in SQL Server. This chart displays one line for each of the following lock types:
Use the list on the chart title to select which of the following lock types to display:
By default, SQL Server never times out locks. However, many applications issue a SET LOCK_TIMEOUT statement to cause SQL Server to time out their locks after the specified interval. This metric shows how often these time-outs are being exceeded. The Lock Timeout (@@LOCK_TIMEOUT) values for each connection are displayed in the Session Details pane, in the Sessions dashboard.
The Latches chart displays statistics on latch requests. This chart shows the following series of data:
Latches are file system locks, used for synchronizing data within SQL Server. Latches are enforced when a data element is being accessed physically, in order to ensure that the data page on which the data element resides is readable and writable. Enforcement of latches is also carried out before the modified data page is written to disk, to prevent modifications by other users during the physical write operation. After the page is successfully written to disk, the latch is released. A high rate of latch wait events per second may indicate a slow disk I/O subsystem.
The Blocking (Current) dashboard provides details for all current lock conflicts. This dashboard allows carrying out the tasks detailed in the following topics:
The Blocking table displays all connections that are either currently waiting on locks held by others, or are causing others to wait, highlighting who is waiting on whom, and the resources involved. To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side.
The hierarchy in this tree diagram represents the blocking chains. It shows who is blocking whom, by displaying one entry for each session that is blocked, and one for each session that is blocking another but is not blocked itself. Sessions at the top of the tree (those that do not have a parent in the tree) are at the head of the blocking chain, and are therefore the root cause of all blocking. Such sessions appear as Lead Blockers in the Number of Blocked Processes chart.
The Blocking table displays the following parameters:
Parameter | Description |
---|---|
SPID | The unique number the Server has assigned for identifying the selected session. |
Wait Time | How long this session has been waiting for the lock (measured in seconds). If the value displayed is 0, the session is not waiting. |
Type | The type of the lock request that is waiting (Database, Table, Page, and so on). |
Resource | The resource that is in conflict. This value often identifies a database and table. The data in the Resource column is reported directly from SQL Server. |
Command | The current or previous command executed. This information can be useful when deciding which sessions to kill. |
SQL User | Identifies the user associated with the SPID. |
Program | The application program that the user is using (for example, Microsoft Access). |
Win User | Name of the Windows account with which the user is logged in to SQL Server. |
CPU | The total amount of CPU consumed by the session so far. |
I/O | The total amount of I/O resources consumed by the session so far. This information can be useful when deciding which sessions to kill. |
Host Name | The name of the client computer. |
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 will indicates if the session is Runnable or Sleeping. |
DB Name | The name of the database where the session is active. |
Session SQL | Displays the SQL belonging to the session that is blocked and/or blocking |
Monitoring blocked processes for the sampled interval
The Number of Blocked Processes chart displays the number of SQL Server sessions that were involved in blocks over time. Use this chart to review the frequency and duration of lock conflicts in SQL Server. This chart displays the following indicators:
The Deadlocks dashboard displays all deadlocks that took place within the selected time range. Deadlock situations are highly time and resource-consuming, and result in unresponsive applications and rollback, because they involve at least two transactions that lock one another and can be resolved only by terminating one of the transactions, making it a “deadlock victim”. Resolving a deadlock can become even more complicated when the deadlock involves more than two sessions (chained deadlocks). Using the Foglight for SQL Server agent for collecting Deadlock Graph trace data does not require turning on any SQL Server trace flags. Foglight for SQL Server collects this data implicitly for any SQL Server user who has been granted the ALTER TRACE permission through the automatic discovery wizard; the user does not need to intervene in the process.
To retrieve Deadlock Graph data, Foglight for SQL Server uses minimal trace over deadlocks, collecting only the deadlock-related data, unlike SQL Server Profiler. The only difference is in the refresh rate; while SQL Server Profiler constantly refreshes its data, Foglight for SQL Server agent collects the data every 1 minute (by default) when focusing the screen (online mode), and every 5 minutes when off screen (offline mode). As a result, when watching the deadlock dashboard the data refresh occurs every 1 minute (by default). The Deadlocks dashboard contains an overview pane, which provides significant performance-related details about each of the deadlocks, such as the amount of time the “deadlock victim” ran before being terminated, the amount of time each of the transactions involved in the deadlock had to wait, and the ratio of regular to chained deadlocks during the specified time range. The other panes of the dashboard focus on specific types of components (databases/objects/applications), allowing to view which of the application’s components were most adversely affected by deadlocks, and are therefore the most vulnerable to deadlock situations.
This dashboard includes the panes described in the following sections:
The Deadlocks pane contains the following components, which provide information over all of the deadlocks that took place during the specified time range:
Deadlock details include:
Column | Description |
---|---|
Time | The exact time when the deadlock took place. |
Type | The deadlock type: - Chained — indicated by a lock sign - Regular — the column is empty |
SPID | The SQL Server’s internal process ID |
SQL Text | The short text of the selected SQL statement. |
Database | Database name |
Host Name | The name of the client computer that established the SQL Server connection. |
Login Name | The SQL Server login name for this session. |
Client Application | The client application that established the SQL Server connection. |
Lost Time | The time, in seconds, since the last transaction ran until the deadlock occurred. |
Wait Time | The amount of time the process is waiting for the resource. |
Log Used | The amount of log used through the session process. |
Lock Mode | The lock mode set for the Owner. |
Trans. Count | The number of opened transactions. |
Owned Object | The object that is owned by the selected session and waited by the other session involved in the deadlock. |
Waited Object | The object that is owned by the other session involved in the deadlock, and for which the selected session waits. |
Wait Resource | The resource for which the process is waiting. |
Deadlock Priority | The deadlock priority specified for the SQL Server session. |
The Databases pane displays all of the databases that were involved in deadlock situations during the specified time range. This pane contains the following sections:
The Objects pane displays all of the objects that were involved in deadlock situations during the specified time range. This pane contains the following sections:
The Applications pane displays all of the applications that were involved in deadlock situations during the specified time range. This pane contains the following sections:
The I/O by File dashboard displays current I/O statistics for each SQL Server file. The I/O statistics’ display can also be grouped by other grouping criteria, such as database files or disk.
This dashboard includes the following components:
The I/O by File table displays current SQL Server I/O statistics for each SQL Server database file. To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side.
The I/O by File table displays by default the following columns:
Column | Description |
---|---|
DBName | 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. |
Disk | The name of the physical disk on which the database resides. |
MB on Disk | Physical file size on disk in megabytes. |
MB Read | The number of megabytes read from this file since SQL Server started. |
MB Written | The number of megabytes written to this file since SQL Server started. |
MB Total | The total number of megabytes written to and read from this file since SQL Server started. |
Reads Wait Time | The total number of seconds SQL Server has spent waiting for physical read operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions. |
Writes Wait Time | The total number of seconds SQL Server has spent waiting for write operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions. |
Total Wait Time | The total number of seconds SQL Server has spent waiting for physical read and write operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions. |
Read Operations | The total number of read operations carried out by this file during the selected time period. |
Write Operations | The total number of write operations carried out by this file during the selected time period. |
Total Operations | The combined total number of read and write operations carried out by this file during the selected time period. |
The I/O by File chart provides a graphical representation of SQL Server I/O statistics for each SQL Server database file. This chart represents several indicators from the table, as presented in the following table. To display only requested files, click the first requested file and then hold down either the Shift key, for choosing a block of multiple files, or the Ctrl key, for choosing individual files.
The I/O by File chart displays the following:
Indicator | Description |
---|---|
Current IO Rate | The current rate, in megabytes, at which SQL Server is performing physical read and write operations to this file (Reads rate plus Writes rate). |
Current Read Rate | The current rate, in megabytes, at which SQL Server is performing physical read operations from this file. |
Current Write Rate | The current rate, in megabytes, at which SQL Server is performing physical write operations to this file. |
Current Wait Time (Average Active Sessions) | The number of seconds that SQL Server has spent waiting for I/O operations on this file since the last time data was collected. |
The Databases dashboard displays storage information about the selected SQL Server instance, including: databases, file groups, files, tables, indexes, disks, and log files. The Databases dashboard is divided into the following areas:
The Databases table provides details about all of the selected SQL Server databases within the selected instance. The information displayed in the table can be changed by selecting, from the View list, one of the options detailed in the following sections:
The Overview view displays general information about each of the monitored databases.
Parameter | Description |
---|---|
dbid | ID of the database, unique within an instance of SQL Server. |
Database Name | Name of the database, unique within an instance of SQL Server. |
Status | The database’s current status (for example, ONLINE, OFFLINE, RESTORING, or SUSPECT). |
Database Space | Provides a visual representation of the database size and space usage within each database. |
Data Size | The total size allocated to data files. |
Data Used Size | The total size actually used by data files in the database. |
Data Free Size | The total available data size (allocated to data files but not used). |
Log Size | The total size allocated to log files. |
Log Used Size | The total size actually used by log files in the database. |
Log Free Size | The total available log size (allocated to log files but not used). |
# Tables | The number of tables, where table type=user, which exist in the database. |
# Indexes | The number of indexes that exist in the database. |
# File Groups | The number of file groups that exist in the database. |
Last Backup Full Time | The date and time when the most recent full database backup of any type took place. |
Last Backup Differential Time | The date and time when the most recent differential database backup of any type took place. |
Last Backup Log Time | The date and time when the most recent database log backup of any type took place. |
Recovery Model | The recovery model for the database; for example, Simple, Bulk Logged, or Full. |
The Last Backup Full Time, Last Backup Full Differential Time, and Last Backup Log Time columns are not displayed by default; to view these columns, click the Customizer button at the right side of the table.
The Transactions view displays details about the oldest active transaction in each of the monitored databases. A transaction open for a long time may result in degraded performance. The Transactions view displays the following parameters:
Parameter | Description |
---|---|
dbid | ID of the database, unique within an instance of SQL Server. |
Database Name | Name of the database, unique within an instance of SQL Server. |
Oldest Tran Start Time | The date and time at which the oldest active transaction in the database began. |
Oldest Tran SPID | The system process ID of the session that owns the oldest active transaction in the database. |
To view the session details of a transaction:
The Backup Status view displays backup details for the database, such as the data size and the date and time of the most recent backup of any type for the database.
The summarized value of the data size and the log size indicates the anticipated size of the backup file.
The Backup Status view displays the following parameters:
Parameter | Description |
---|---|
dbid | ID of the database, unique within an instance of SQL Server |
Database Name | Name of the database, unique within an instance of SQL Server. |
Status | The database’s current status (for example, ONLINE, OFFLINE, RESTORING, or SUSPECT). |
Data Size | The total size allocated to data files. |
Log Size | The total size allocated to log files. |
Last Backup Full Time | The date and time when the most recent full database backup of any type took place. |
Last Backup Differential Time | The date and time when the most recent differential database backup of any type took place. |
Last Backup Log Time | The date and time when the most recent database log backup of any type took place. |
The Last Backup Full, Differential, and Log Time columns are not displayed by default; to view these columns, click the Customizer button at the right side of the table.
The Properties view displays database properties that correspond to the data returned by the SQL Server DatabasePropertyEx function. For more information about these properties, see the Transact-SQL Reference section in Microsoft® SQL Server Books Online.
The In Memory view displays metrics on space usage by objects in memory. To view details for a specific database, click on the database row. Detailed metrics will be displayed below the table.
The summarized value of the data size and the log size indicates the anticipated size of the backup file.
Parameter | Description |
---|---|
dbid | The database ID number. |
Database Name | The name of the database. |
XTP Enabled | Whether or not the database is XTP enabled. |
Resource Pool Name | The name of the resource pool allocated to the database. |
Percent of Pool | Total memory usage of the objects within the database. |
Tables Allocation | Total amount of memory (in MB) allocated for the tables. |
Indexes Allocation | Total amount of memory (in MB) allocated for the indices. |
System Allocation | Total amount of memory (in MB) allocated for the system. |
Table Used | Total amount of memory (in MB) used by the tables, including row versions. |
Indexes Used | Total amount of memory (in MB) used by the indices, including row versions. |
System Used | Total amount of memory (in MB) used by the system, including row versions. |
You can display detailed data for a database by selecting the database row in any one of the tables displayed on the Overview, Transactions, Backup Status, Properties or In Memory tabs. The Database Details dashboard allows carrying out the following tasks:
The panes from File Groups to Tables and Indexes display tabular and graphical information about other areas down the storage level hierarchy, currently selected in the Databases table. The charts on each of these panes display the amount of space allocated to the area for each selected database (Space Chart), and the amount by which each of the areas is growing over time (Growth Chart).
The Summary pane displays three separate charts showing various statistics relating to recent I/O activity on each database. You can select which data each graph should display.
The graphs in the chart are available for each database.
The following graphs are available for each database:
Graph | Description |
---|---|
% Log Used | The amount of file space allocated to the transaction log of each currently used database. Log space can be freed up by backing up the log, or truncating it, using the truncate option (backup log Note: All log records that exist after the oldest open transaction cannot be freed. |
Data File Size | The amount of disk space that the data files are using. This graph indicates how the size of data files has changed over time. |
Log File Size | The amount of disk space that the log files are using. This graph shows how the size of log files has changed over time. |
Active Transactions | The number of open transactions in each database. |
Transactions Rate | The rate of transactions for each database. |
Log Cache Hit Rate | Percentage of log cache reads satisfied from the log cache. This metric reflects the amount of physical log I/O that is being avoided by caching log data. The log caches normally have a non-zero hit rate only if Rollback activity is taking place. |
BCP Throughput Rate | The rate (measured in kilobytes per second) at which data is being loaded into the database using BCP (Bulk Copy Program) or BULK INSERT. Despite the speed at which BCP and BULK INSERT can import data into SQL Server, system I/O performance may degrade while BCP operations are underway. |
Backup Throughput Rate | The rate (measured in kilobytes per second) at which backup or restore operations are reading or writing to the database. Throughput of a database backup or restore operation allows determining the progress and performance of these operations; for example, measuring how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. |
DBCC Scan Bytes Rates | The rate (measured in kilobytes per second) at which Database Console Commands (DBCC) are processing data. This rate represents the number of logical read scan kilobytes per second for database command console (DBCC) statements. |
Log Flushes Rate | The rate at which the log cache for each database is being flushed to disk, which is necessary to guarantee that transactions can be recovered in the event of a system failure. |
Log Flush Wait Time | The amount of time spent, in milliseconds, waiting for log flushes in each database. High log flush wait time can be caused by a slow or overworked disk subsystem. If a database has a consistently high Log Flush Wait Time that never changes, run the SQL command CHECKPOINT on that database to force another log flush and re-check the value in Foglight for SQL Server. |
Log Growths Rate | The number of times the log has been expanded for each database. |
Log Shrinks Rate | The number of times the log has been reduced for each database. |
Log Truncations Rate | The frequency of log truncations for each database. |
The File Groups pane displays detailed information about all file groups in the databases that are currently selected in the Databases table. This pane includes the following areas:
The File Groups table shows information about all file groups in the selected database. File stream types (used by memory optimized objects and file-stream objects) are now supported. This table displays the following parameters:
Column | Description |
---|---|
File Group | The name of the file group in the database. |
File Count | The number of files in each file group. |
Type | The type of file group; for example, data or log file. |
Total Size | The total size of all files in the group. |
Used Size | The total size of used space of all files in the group. |
Used Pct | The percentage of used space of all files in the group. |
Free Size | The total size of free space of all files in the group. |
Free Pct | The percentage of free space of all files in the group. |
Can Grow | Indicates whether the files in the file group (if any) can grow automatically as they fill. |
The File Groups Space chart displays the amount of space allocated to each file group. Each file group consists of used space and free space. The chart values can be displayed in megabytes or as a percentage of disk space. Use the File Groups Space list to change the values displayed on the chart.
The Growth chart displays the amount by which each file group is growing over time. Use the Growth list to change the values displayed on the chart. This list can be displayed by the total amount of space (File total size), used space (File used size), or unused space (File free size).
The Data Files pane displays detailed information about all data files in the database currently selected in the Databases table. This pane includes the following areas:
To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side.
The Data Files table displays the following indicators for all database data files:
Column | Description |
---|---|
Files | The name of the data file. |
File Group | The name of the file group to which the file belongs. |
Type | The type of file group; for example, data or log. |
Total Size | The size of the file. |
Used Size | The total size of the file’s used space. |
Free Size | The total size of the file’s free space. |
Used Pct | The percentage of the file’s used space. |
Free Pct | The percentage of the file’s free space. |
Auto Grow | Indicates whether the file can grow automatically. |
Growths Remaining | The number of times the file can grow, considering its currently configured autogrow increment, before exceeding its space limits. |
Max Size | The maximum size to which the file can grow. |
Growth Inc. | Growth increment. Indicates the amount by which the file grows every time, if the autogrow option is enabled. for example, if the data file’s initial size is 1 megabyte and it has to grow to a final size of 11 megabytes, setting the Growth Inc. parameter to 2 megabytes causes the file to grow five times, each time by a 2 megabyte increment. |
Path | The path of the data file. |
Db and File | The database name, concatenated with the database file name. |
The File Space chart displays the amount of space allocated to the data file for each selected database. Each data file consists of used and free space. The chart values can be displayed in megabytes or as a percentage of disk space. Use the File Space list to change the values displayed on the chart.
The Data Files Growth chart displays, in megabytes, the amount by which each file is growing over time. Use the Growth list to change the values displayed on the chart. This list can be displayed by the total amount of space (File Total Size), used space (File Used Size) or unused space (File Free Size).
The Transaction Logs pane displays detailed information about all transaction logs in the database currently selected in the Databases table. This pane includes the following areas:
The Transaction Logs table displays information about all transaction logs in the selected databases, as detailed in the following table. The Transaction Logs table details.
Column | Description |
---|---|
File Group | The name of the file group to which the file belongs. |
File Count | The number of transaction logs in the database. |
Type | The type of file; for example, log. |
Total Size | The total size of all files in the transaction log. |
Used Size | The amount of used space. |
Used Pct | The percentage of used space within the log file. |
Free Size | The amount of free space. |
Free Pct | The percentage of space available for use within the log file. |
Can Grow | Indicates whether any of the files in the transaction log can grow. |
The Transaction Log Space chart displays used space and free space for each of the selected databases’ transaction logs. The chart values can be displayed in megabytes or as a percentage of disk space. Use the Transaction Log Space list to change the values displayed on the chart.
Transaction Log Growth chart The Transaction Log Growth chart shows the amount by which each transaction log is growing over time. Use the Growth list to change the values displayed on the chart. This list can be displayed by the total amount of either all transaction logs (File Total Size), used logs database (File Used Size), or unused transaction logs (File Free Size).
The Log Files pane displays detailed information about all log files in the database currently selected in the Databases table. This pane includes the following areas:
The Log Files table displays information about all log files in the selected database.
Column | Description |
---|---|
File | The log file name. |
File Group | The name of the file group to which the file belongs. |
Type | The log file type. |
Total Size | The size of the log file. |
Used Size | The amount of the used space in the log file. |
Free Size | The size of the currently unused space in the log file. |
Used Pct | The percentage of used space within the log file. |
Free Pct | The percentage of space available for use within the log file. |
AutoGrow | Determines whether the log file can grow automatically.Growths remaining The number of times the file can AutoGrow before it can grow no more. |
Max Size | The maximum size to which the log file can grow. |
Growth Inc. | Growth increment. The amount by which the log file can AutoGrow at any one time. |
Can Grow | Determines whether the log file can grow. |
Path | The log file’s path. |
Db and File | The database name, concatenated with the log file name. |
The Log Files Space chart displays the total space allocated to each log file. The chart values can be displayed in megabytes or as a percentage of disk space. Use the Log File Space list to change the values displayed on the chart.
The Log File Growth chart shows the amount by which each log file is growing over time. Use the Growth list to change the values displayed on the chart. This list can be displayed by the total amount of space (Total), used space (Used), or unused space (Free space).
The Tables and Indexes pane, which displays storage information for database tables and indexes, contains the following sections:
The Table list displays all tables in the selected database, including data such as their size, number of rows, free space, and owner name. To set a user-defined display of the Table list:
To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side. The value displayed in the Table Size column indicates only the amount of space used by data, while the value displayed in the Used Size column indicates the amount of space used by both data and indexes, and the value displayed in the Reserved Size column indicates the entire amount of space reserved for data and indexes.
The parameters displayed in the table are as follows:
Column | Description |
---|---|
Owner Name | The name of the table owner. |
Table Name | The name of the table. |
File Group Name | The name of the file group where the table is stored. |
Table Size (MB) | The actual size of the table, in megabytes. |
Reserved Size (MB) | The amount of space reserved for the table, in megabytes. |
Used Size (MB) | The amount of used space in the table, in megabytes. |
Free Size (MB) | The amount of free space in the table. |
Percent of DB | The percentage of space that the table occupies in the database. |
Rows | The number of rows in the table. |
Reserved Memory | The amount of memory reserved for this table. |
Used Memory | The amount of memory used by this table. |
Number of Partitions | The number of partitions, if any. |
Compression Type | What type of compression the table uses, if any. |
Table Type | The type of table. Memory optimized, user tables and File tables are now included. |
The Indexes table shows the index statistics for all indexes in the selected tables. SQL Server stores statistics about the distribution of the key values in each index, and uses these statistics to determine which indexes to use in query processing. Selecting a single row in the Indexes table displays detailed index statistics for that index in the lower section of the dashboard. To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side.
The columns displayed in the Indexes table are as follows:
Column | Description |
---|---|
Owner Name | The owner of the index. |
Table Name | The table that the index is associated with. |
Index Name | The name of the index. |
Index ID | The ID of the index. |
File Group Name | The name of the File Group where the index resides. |
Type | The type of the index. |
No. of Keys | The number of keys in the index. |
Index Size (MB) | The size of the index at the time of its last statistics update, measured in megabytes. |
Used Size (MB) | The amount of used space in the index, in megabytes. |
Free Size (MB) | The amount of free space in the index, in megabytes. |
Rows | The number of rows in the table. |
Row Mod Ctr | The number of row modifications made since statistics were last updated for this index. |
Original Fill Factor | The fill factor used when creating an index, in order to reserve a percentage of free space on each data (leaf level) page. |
This table allows selecting one or more indexes. When only one index is selected, its distribution statistics are displayed at the bottom part of the dashboard. When multiple indexes are selected, statistics are displayed only for the index with cursor focus (indicated by a black box around the index row).
Clicking a single index in the Indexes table displays the Index Properties pop-up. The Index Properties pop-up contains the tables described in the following sections:
Index Density table
The Index Density table shows the density values for each combination of columns in the index.
The Index Density table contains the following columns:
Statistic | Description |
---|---|
Columns | The name of the columns in the index. |
Density | Density is the term used by SQL Server to represent the selectivity of the index columns. The more selective an index is, the more useful it is in searches. Density is calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values. Note: Starting from SQL Server 2008, the density values is not used by the query optimizer, and is displayed only for backward compatibility with earlier versions. |
Average Key Length | Average number of bytes per value for all of the key columns in the statistics object. |
Fragmentation table
The Fragmentation table displays all indexes in the selected tables and shows the latest fragmentation information collected for each index. Fragmentation information is collected by running the DBCC SHOWCONTIG command on each selected index.
Running DBCC SHOWCONTIG on large tables can take a long time and put significant load on the server.
How fragmented are my tables? The following values can be used for determining the overall fragmentation in tables:
A value of -1 is displayed for heaps (tables without indexes) and for text structures.
Removing fragmentation
Several options are available for eliminating (or at least reducing) fragmentation. In general, fragmented indexes should be rebuilt by SQL Server. For details, see Microsoft® SQL Server Books Online.
When a single index is selected in the Indexes table, the Index Distribution chart displays the index distribution histogram for the selected index. When SQL Server collects statistics on an index for use in determining the indexes usefulness for resolving queries, it samples the data in the index and produces a histogram of the key values found. This chart shows that histogram for the selected index. The values shown in the Index Distribution chart derive from the latest statistics for the selected index. If the index statistics are out of date, these values are also outdated. Check the Last Updated statistic on the Statistics page for the date when statistics were last collected. Use the Update Statistics SQL command to re-collect statistics. Each series in the chart corresponds to one sample value from the first column in the index. The chart shows the number of rows in the table that have that value.
SQL Server does not inspect every key value, but uses only sampling. The histogram displays the percentage of the table that falls in a sample range. Not all of the records counted have the exact sample value shown.
Example: If the selected index is on a Name column, the chart may display the following values:
The Disk Space pane displays storage information about disks that reside on the currently monitored server. The term disk in this tab is used on the assumption that each physical disk contains only one partition. If the disk is divided to several partitions (logical drives), the tab displays disk space utilization per partition, and not a summary of the utilization of the entire physical drive. This pane is designed to answer the following questions:
The Disk Space chart displays several parameters:
Parameter | Description |
---|---|
Data Used | The amount of space on the disk used by data files in the currently monitored SQL Server databases. |
Data Free | The amount of space on the disk allocated to data files in the currently monitored SQL Server databases but not used. |
Log Used | The amount of space on the disk allocated to SQL Server log files for the currently monitored databases. |
Log Free | The amount of space on the disk allocated to log files in the currently monitored SQL Server databases but not used. |
Disk Free | The amount of space on the disk not used by any files. |
Non SQL Files | The amount of space on the disk used by files not associated with the currently monitored SQL Server databases. |
To include the Disk Free and the Non SQL files parameters on the chart, select the Include Non-SQL files in the chart checkbox. To restrict the data and log-related figures to display only the space used by specific databases, select one or more of the databases in the Databases table. To choose whether to display the chart in megabytes (MB) or percentage, use the Disk Space Utilization dropdown list box.
The space shown for SQL data and log files refers only to files in this instance of SQL Server. If multiple SQL Server instances are running on this server, the data/log files for other instances are included in the Non-SQL Files figure.
If the Windows server being monitored does not have Logical Disk performance counters enabled, the value of the Non-SQL Files component of this chart is always zero. If disk counters are not enabled, the Disk Counters Disabled alarm is displayed on the overview page.
The TempDB system database is a global resource that exists in any SQL Server instance and is used for various functions within it. There is one such database in every instance. It is used to hold the following:
Given its wide range of usage and the fact that there is only one such database in any instance, it may become a bottleneck. The TempDB dashboard aim is to help analyze TempDB activity and diagnose potential problems.
The upper pane of the dashboard indicates the amount of space currently allocated across all data files of the TempDB database as well as its log files. The usage profile graph indicates how space is used within TempDB so it is possible to see which component is allocated the majority of the space: user objects, internal objects or the version store. The usage profile tab on the lower pane of the screen, provides more information about the usage trends of TempDB. The left side indicates how space is allocated between the various consumers of TempDB both at the database level and at the file level. To find out which tables are currently allocated within TempDB use the Tables & Indexes tab.
The Right side provides more information about the version store usage.If you see the version store consume a considerable amount of space in the TempDB, check the generation rate compared to the cleanup rate. A high generation rate that is not matched by the cleanup rate may indicate data modification transactions are not committing frequently enough. The “Current Transaction Running Time” indicates the running time of the longest transaction.
To find out more about sessions that are currently allocating space in TempDB, use the Sessions tab. The TempDB Current Allocated Size metric indicate the amount of space (MB) currently allocated by the session and not yet deallocated. The TempDB Total Allocated Size metric indicates the amount of space (MB) that the session has used since it started. More details about the session can be found by drilling down to the Session dashboard using the link on the SPID column.
The Create Objects Rate graph, on the upper pane of the dashboard, can be used to determine the trend of user objects creation rate. If this number is high or deviates from its normal behavior it may indicate misuse of temporary objects. The Summary tab, on the lower pane, can be used to examine rate of transactions against TempDB. Wait for IO on TempDB files, as well as contention for allocation pages, can be examined in the SQL Performance dashboard if SQL PI is installed.
The Services dashboard provides detailed information, represented by graphs and tables, of the state of the various SQL Server support services.
The Services Status dashboard includes panes that allow carrying out the following tasks:
The Services Status table, which displays the current status of each SQL Server support service, contains several parameters, as presented in the following table. To create a custom filter for this table, use the options accessible by clicking Advanced.
The Services Status table parameters.
Column | Description |
---|---|
State | The state of the SQL Server support service. The possible values are: Stopped, Paused, Running, Not Installed, Not Configured, and Configured. |
Service | The SQL Server service name. The support services monitored by Foglight for SQL Server are as follows: - SQL Server Agent - DTC: This is only supported for SQL Servers running on Windows plaftorm. - Full Text Search - OLAP Services - SQL Server Mail - Integration Service - Report Service - Browser Services - Writer Services - ADH Service |
The Service Status History chart displays the status of each SQL Server support service over time. This table allows viewing whether a service has been installed, and the exact times when each support service’s state was one of the following:
SQL Server Mail and SQL Agent Mail are not services and cannot be started or stopped. The Service Status History chart displays only their current configuration status.
The SQL Agent Jobs dashboard lists all of the currently defined SQL Agent jobs, highlighting their current status (running, succeeded, or failed). This dashboard also allows viewing the execution messages from the last run of each job. In addition, the SQL Agent Jobs dashboard displays the status of each job over time, thereby providing the exact information about when a job ran and when it succeeded or failed.
This dashboard allows carrying out tasks described in the following topics:
The SQL Agent Jobs table lists all SQL Server Agent Jobs defined in this server. This table highlights the current status of each job (running, succeeded, or failed). This data is updated on demand.
To create a custom filter for this table, use the options accessible by clicking Advanced.
The job state is indicated to the left of the job name.
Indication | Description |
---|---|
![]() |
The job was never run. |
The following indicators refer to jobs that were run: | |
![]() |
The job run was cancelled. |
![]() |
The job run failed. |
![]() |
The job run completed successfully. |
The job state depends on the last run outcome. For example, if the current status is Completed, but the last run outcome is Fail, the job is indicated by a red circle as a failed job.
The SQL Agent Jobs table contains the following columns:
Column | Description |
---|---|
Job Name | The name of the job. |
Job Category | The SQL Agent job category assigned to this job. |
Enabled | Indicates whether the job is enabled. |
Last Run | Outcome The result of the last run (Fail, Success, Retry, Cancel, or In progress). |
Current Status | The current status of the job (Running, Completed, or Never Ran). |
Curr Step # | If the job is currently running, displays the current step number. |
Last Run Time | The date/time on which the job last ran. |
Last Run Finish | The time the job was completed. |
Last Run Duration | How long the last run took (displayed in the d hh:mm:ss format). |
Next Run Time | The date/time of the next scheduled run. |
Description | A brief description of the job. |
The Job Messages table displays the execution messages for each step in the current job. The data in the table is refreshed on demand. Historical data is not stored in a repository. To create a custom filter for this table, use the options accessible by clicking Advanced.
The Job Messages table displays the following parameters:
Column | Description |
---|---|
Time | The time the job or step started execution. For an In Progress history, this is the time the history was written. |
Message | Displays an error message, if the current step in the job resulted in an error. |
Step Name | The name of the step. |
Run Status | The job execution’s status (Failed, Success, Retry, Cancelled, or In progress). |
Run Duration | Elapsed time in the execution of the job or step in seconds format. |
The SQL Agent Jobs History chart displays the status of jobs over time, thereby providing the exact information about when a job ran and when it succeeded or failed. The possible statuses are as follows:
When a job executes, Foglight for SQL Server adds it to this chart (Running), identifying the time it started. When the job finishes, Foglight for SQL Server checks the completion status (Success or Failed) and changes the color of the job accordingly, to indicate when exactly a job started and ended.
The SQL Agent Alerts dashboard lists all currently defined SQL Server Agent Alerts, highlighting the alert type and when it last occurred.
This SQL Agent Alerts dashboard is only supported for monitored SQL Servers running on Windows platform.
This dashboard also features a chart that allows tracking the occurrences of each alert over time. The sections of this dashboard allow carrying out the tasks detailed in the following topics:
The SQL Agent Alerts table lists all SQL Server Agent alerts defined in this SQL Server instance. This table highlights the type of alert (either Event Alert or Performance Alert), as well as how often the event has occurred, and the date and time of its last occurrence. To create a custom filter for this table, use the options accessible by clicking Advanced. The SQL Agent Alerts table displays the following parameters:
Column | Description |
---|---|
Alert Name | The name of the alert that was raised. |
Type | The type of alert. |
Enabled | Indicates whether the alert is enabled. |
Last Occurred | The time when the alert was last raised. |
Count | The number of occurrences of the alert. |
DBName | The name of the database where the alert was raised. |
Description | The full text of the specified alert’s error message. This text is taken either from SQL Server or, for user-defined alerts, from the user-created message. |
The Alert Occurrences chart shows when recent SQL agent alerts have occurred, and how many have occurred during the selected time range. The legend on the right indicates the types of alerts currently displayed in the chart.
The DTC dashboard provides additional data on SQL Server transactions. DTC (Distributed Transaction Coordinator) service is an integral component of Microsoft Windows (Windows 2003 and higher versions) and is also available as part of the SQL Server installation, if a previous version of Windows is used. If the server to which Foglight for SQL Server is currently connected has more than one instance running, this dashboard provides data regarding all instances of SQL Server on the current computer, not only the instance currently under analysis.
The DTC Details chart displays data on the following aspects of DTC performance:
Data Series | Description |
---|---|
Aborted Transactions | The number of distributed transactions that were rolled back. |
Active Transactions | The number of currently active distributed transactions. |
Committed Transactions | The number of distributed transactions committed per second. |
In Doubt Transactions | Transactions that have passed phase 1 of the two-phase commit operation (have committed the local transaction), and are awaiting a response from the DTC to either commit or roll back (phase 2). |
Response Time Average | The average of the full time (in milliseconds) it has taken a query (select 1, by default) to get from the application to SQL Server and back. |
Transactions/sec | The number of distributed transactions performed per second. |
In full-text indexing, a separate catalog is maintained that indexes each word in a database field as a separate index entry. The Full Text Search dashboard displays performance details for all full-text indexes on the server. To create a custom filter for this table, use the options accessible by clicking Advanced.
The Full Text Search Service table contains the following data.
Data Series | Description |
---|---|
Database Name | The name of the database where the tables in the Full Text Catalog reside. Provides a unique identification of the database within a SQL Server instance. |
ID | ID of the full-text catalog. Provides a unique identification across the full-text catalogs in the database. |
FTC Name | The name of the full-text catalog. Provides a unique identification within the database. |
Status | The status of the full-text catalog. Can have one of the following values: - 0 — Idle - 1 — Full population in progress - 2 — Paused - 3 — Throttled - 4 — Recovering - 5 — Shutdown - 6 — Incremental population in progress - 7 — Building index - 8 — Disk is full. Paused. - 9 — Change tracking |
Index Size MB | The size of the full-text index, in megabytes, rounded to the nearest value. |
Item Count | The number of items currently found in the full-text catalog. |
Path | The name of the catalog directory in the file system. |
Last Populated | Displays the time when the full text index was last populated; returns 0 if no population has occurred. |
Table Count | The number of tables in the database that take part in the full text indexing process. |