Viewing the SQL Database Performance

You can view the memory usage, database usage, instance activity, and services of the SQL database.

This section covers the following key areas:

Viewing the Memory Usage

The Memory dashboard provides access to graphs that display details of memory usage for the currently monitored server.

Viewing the Memory Summary

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:

Monitoring Status

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:

  • More RAM Memory is required for the server.
  • The processes running on the SQL server may consume more memory than possible for a healthy server.

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.

Investigating Resource Pool Memory Issues

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:

  • There is insufficient system memory in resource pool <RP Name> to run this query.
  • There is insufficient memory available in the buffer pool.

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:

  • An alert icon for the RP utilization.
  • The name of the RP.
  • A bar showing the usage within the RP.
  • The total memory potentially available for the RP.

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.

Investigating Cached Memory Issues

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:

  • There are changes in the schema.
  • A query is running parallel that may have run serially before.
  • Parameters have changed.

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:

  • To modify pages according to INSERT and UPDATE statements. Those pages are marked as “dirty” and are flushed to disk when a checkpoint is performed.
  • To increase response time when retrieving the same data.

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 buffer cache has appropriate size of memory, which is sufficient to hold the most frequently used data pages.
  • The free memory value is not consistently close to zero, which may indicate use of inefficient queries or shortage of SQL Server cache memory.
  • The plan cache has appropriate size of memory, which is sufficient to hold the most frequently used execution plans.
  • The XTP memory has no significant peaks that might mean there are memory utilization issues during the day.

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:

  • Lock Area — memory allocated to keeping track of locks.
  • Optimizer Code — a work area for the SQL Server optimizer.
  • Sort, Hash, Index — memory used for each of these operations.
  • User Connections — memory allocated to keeping track of each connection’s attributes.

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.

Viewing the Top-consuming Objects of the Buffer Cache

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.

Tracking the Page Allocations

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.

Monitoring the Buffer Cache Hit Rates

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:

  • Monitoring the Plan Cache
  • Viewing Statistics about Plan Cache Objects

Viewing Object Types

SQL Server 2005 and later objects In SQL Server 2005 and later versions, the following object types can appear in the chart:

  • Bound Trees — normalized trees for views, rules, computed columns, and check constraints.
  • Extended Stored Procedures — a SQL Server object that dynamically loads and runs a function within a dynamic-link library (DLL) in a manner similar to a stored procedure.
  • Object Plans — query plans generated by creating a stored procedure, function, or trigger.
  • Replication Procedure Plans — query plans of a replication system stored procedure.
  • SQL Plans — query plans corresponding to statements prepared using sp_prepare, sp_cursorprepare, or using auto-parameterization.
  • Temporary Tables and Table Variables — temporary tables are session-specific tables, that is, the tables are automatically dropped when the session is closed. Table variables, on the other hand, are created in the memory and exist there until the running of a single Transact-SQL (T-SQL) batch is completed

Monitoring the Plan Cache

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.

Tracking Memory Allocation to the Plan Cache by Object Type

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.

Viewing Statistics about Plan Cache Objects

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

Viewing the Instance Activity

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.

Viewing In-depth Data about the Instance

The Activity menu provides two levels of information about the currently diagnosed SQL Server instance:

  • An overview of the activity in the currently monitored database, using the SQL Instance Summary dashboard. This dashboard can be used for indicating specific performance issues, such as high response time and low cache rates. Most of the possible reasons for a slow response time can be initially traced by using the charts in this dashboard.
  • More detailed information, using the other dashboards. Each of the other dashboards allows carrying out a root-cause analysis of possible performance issues indicated in the SQL Instance Summary dashboard.

The Activity menu allows you to carry out the following tasks:

  • Viewing SQL I/O activity data — using the SQL I/O Activity dashboard, which provides graphical representations of various I/O activities of the currently diagnosed SQL Server instance.
  • Viewing session data — using the Sessions dashboard, which lists all current SQL sessions and allows viewing session details and locks.
  • Viewing detailed statistics about locks — using the Locks dashboard, which displays information about all locks, latches and requests on the currently diagnosed SQL Server instance.
  • Viewing statistics about current lock conflicts — using the Blocking (Current) dashboard, which displays information about all current blocked sessions and lock conflicts, including the connections and resources involved in these conflicts.
  • Tracking deadlocks and their implications — using the Deadlocks dashboard, which displays all of the deadlocks that took place within the selected time range, as well as the databases and objects that were involved in the deadlock situations.
  • Viewing I/O Statistics by Database Files — using the I/O by File dashboard, which displays current I/O statistics for each SQL Server file.
  • Viewing the Real Activity data — represent the main activity areas in the SQL connection process.

Viewing the SQL Server Instance Activity

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:

  • Monitoring connected sessions
  • Monitoring CPU utilization
  • Viewing the SQL Server I/O activity
  • Tracking the response time
  • Monitoring cache hit rates
  • Monitoring the call rates

Monitoring connected sessions

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:

  • Active user sessions
  • Idle user sessions
  • Internal SQL Server System sessions

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.

Monitoring CPU utilization

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.

Viewing the SQL Server I/O activity

The SQL Server IO chart displays the following types of I/O activity:

  • Physical I/O activity — indicated by the rate at which pages are physically read from and written to disk by SQL Server.
  • Logical I/O activity — indicated by the rate at which pages in the buffer cache, a memory area used by SQL Server to hold recently accessed database pages, are being referenced by SQL connections (logical page reads).

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:

  1. Click Activity > Sessions and navigate to the Sessions dashboard.
  2. Click the requested session in the Sessions table.
  3. Review the details displayed in the Session Details pane.

To view current SQL Server I/O statistics for each SQL Server database file:

  1. Click Activity > IO by File and navigate to the IO by File dashboard.
  2. View the details displayed in the IO by File table.

Tracking the response time

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.

Monitoring cache hit rates

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:

  • Buffer Cache — the hit rate for this cache should normally be over 90%.
  • Procedure Cache — the hit rate for this cache varies widely, depending on how well the application is written.

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.

Monitoring the call rates

The Call Rates chart displays the rate at which various events are occurring in SQL Server. The following metrics are charted:

  • Batches — a batch is one or more transact SQL statements sent at one time from an application to the SQL Server instance. High rates of both batch, and compiles and re-compiles can sometime indicate a bottleneck, as a result of batches that are not using compiled plans, or identical batches that use a different plan or create a new one. To troubleshoot this issue, review the use of execution plans, date and language formats used.
  • Compiles — the rate at which an SQL stored procedure is being compiled into the procedure cache. The Compiles figure includes Re-Compiles.
  • Re-Compiles — the rate at which SQL Server is recompiling an SQL stored procedure in the procedure cache.

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.

Viewing SQL I/O Activity Data

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:

  • Viewing the Real-time Summary Page
  • Monitoring the SQL Server physical I/O activity
  • Monitoring the SQL Server logical I/O activity
  • Viewing how SQL Server’s logical data is accessed and updated
  • Viewing Session Details

Viewing the Real-time Summary Page

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:

  • Instance identification — used for identifying the instance, its type, and its properties.
  • Components representing instance data flow — the main activity area in the Foglight for SQL Server Instance Overview page includes several panes and flows that represent the data flow in the SQL Server operation. The dataflow is described as a top-down design, that is: from the session to the physical disk storage.

Overview Dashboard

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:

  • Instance name — identifies the currently monitored instance.
  • Specified time range — indicates the period for which data is being displayed (by default: last 60 minutes).
  • Instance pane — contains the following indicators:
    • DB Type — identifies whether the monitored database’s type is SQL Server, Sybase, DB2, or Oracle.
    • DB Version — identifies the SQL Server version number, along with the most recently installed service packs.
    • Up Since — identifies the date and time when the instance was last started

      The pane’s initial view only shows the date; to display the time as well, hover the mouse on the instance status icon.

    • OS Version — identifies the operating system’s version number, along with the most recently installed builds and service packs.

      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.

    • Response Time — the time (in milliseconds) that elapses from the moment a query, which is supposed to represent the general workload, is submitted, until the application indicates that the query was run. Because the response time is usually the starting point for investigation, the Response parameter leads to the SQL Instance Summary dashboard in the SQL Activity dashboard.
  • CPU (%) and Memory (%) — the average CPU load and memory consumption (percentage), during the specified time range, of all CPU units that host the SQL Server instance. This indicator displays the share of SQL Server-incurred CPU load and memory consumption within the total figure. Clicking the number on both icons displays a pop-up that shows the total CPU usage or memory consumption on the currently monitored host, plotted over time.
  • No. of CPUs — the number of CPU units on the instance’s host.
  • Total RAM — the total amount (in megabytes) of the host’s physical memory.
  • V. Overhead — the percentage of CPU that is unavailable to this virtual machine because it is utilized either by other virtual machines or by VMware itself.

    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.

  • Free RAM — the total amount (in megabytes) of physical memory available to the applications.

Tracking the Instance Data Flow

The following components represent the data flow in the SQL Server operation, from the session to the physical disk storage:

  • The Sessions dashboard allows examining all sessions running on the instance, distributed between the following session types:
    • Active vs. inactive sessions
    • System (background) vs. User (foreground) sessions This pane displays the activity on the client side, including the flow of data (packets and batches of SQL Statements) from the client to the Server and (in the case of packets) back.
  • The Process Activity pane — displays several indicators about the instance’s sessions, such as the total number of processes and the number of blocked processes. In addition, this pane allows you to track the rate of the data flow used by SQL connections — compiles and logical page reads, that is, referencing pages in the buffer cache.
  • The Background Processes pane — used for tracking the Replication and Services background processes.
  • Memory Activity pane — displays the size of the total SQL memory (both physical RAM and buffer pool), and allows ensuring that the SQL Server appropriately uses its dynamic memory management. The SQL Memory pane’s indicators may point out inefficient memory management, which leads to unnecessary use of physical I/O operations.
  • Physical I/O operations — displays representations of physical I/O activity. While such activity is necessary to carry out certain operations (for example, accessing a new table), a high level of physical I/O activity may indicate poorly coded SQL or inadequate indexes, which prevent logical reads.
  • Disk Storage pane — displays the state of the physical disk storage, that is, databases and their backup, data files, and log files. This pane features indicators that refer either to lack of disk space (when a data file is close to reaching its predefined storage ceiling), or performance issues such as high disk queue length. In addition, the Disk Storage pane displays the Log Flush wait time alarm, which may indicate an issue with the logical disk activity.

Monitoring General Session Statistics

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:

  • A massive workload — the system handles more users than it was initially designed to do.
  • A bottleneck — lack of system resources prevents users from carrying out their transactions, resulting in wait events and an increasing number of sessions that remain active for prolonged periods. The Sessions pane displays the total number of sessions, distributed according to the following distinctions:
  • Active vs. inactive sessions
  • System (background) vs. User (foreground) sessions The parameters in this pane lead to the SQL Activity > Sessions dashboard. The client applications represented graphically in the Sessions pane communicate with the SQL Server by sending and receiving network packets and by submitting SQL statements for execution by SQL Server. The flows, detailed in the following table, help indicate performance issues if their values are too low.
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 allows monitoring the processes, both system and user sessions, run by the SQL Server. This pane also features other performance indicators, such as total number of lock requests per second and CPU usage, and allows accessing the error log.

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

To communicate with the SQL memory, the SQL processes use logical reads and compiles, graphically represented as flows in the overview page.

Tracking Logical Reads

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.

Tracking Compiles

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.

Monitoring the SQL memory management

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.

Tracking physical I/O activity

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.

Monitoring the disk storage

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.

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

Monitoring the SQL Server physical I/O activity

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:

  • Checkpoint Writes are the most common type of write activity under normal circumstances. The checkpoint process periodically scans the buffer cache for modified pages and flushes all modified pages out to disk, thereby minimizing the amount of work SQL Server is required to do on restart. Checkpoint writes are carried out at intervals, which are defined by the recovery interval parameter. If this parameter’s value is too high, the checkpoint process may run infrequently, thereby overloading the Lazy Writer process. As a result, the Lazy Writer does not efficiently maintain the Free Pages list and, when a certain threshold has been exceeded, the Free Buffers alarm is invoked.
  • Lazy Writer Writes are carried out when the Lazy Writer process needs to free up buffer pages that have been modified in the buffer cache. Freeing up the buffers requires the Lazy Writer process to write first the changed pages to disk. High value of lazy writes may indicate that SQL Server is running out of available space in the buffer pool cache. Use this chart to view whether the high lazy writes value is a consistent issue. When the amount of SQL Server memory available for immediate reuse drops below a certain threshold, the Free Buffers alarm is invoked..
  • Physical Page Reads are carried out when a user connection requests a page that is not already in the buffer cache. The connection requesting the page awaits until the I/O operation completes.
  • Physical Page Writes are write operations where the user connection has to wait for the I/O to complete before continuing. These are most often caused by operations such as create index, bulk insert, or restore.
  • Read Ahead occurs when SQL Server forecasts the need for data that currently resides on the disk. In this case, the pages are pre-fetched into the buffer cache before being requested by the user, using Read Ahead processing.

Monitoring the SQL Server logical I/O activity

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.

Viewing the disk queue length

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.

Viewing how SQL Server’s logical data is accessed and updated

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.

Viewing Session Details

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:

  • Resource consumption - displays the current and last 1 hour resource consumption.
  • Current - displays all the current opened sessions.
  • Last 1 Hr. - Enabled only when SQL PI is configured. This view provides several significant features, which highly enhance data retrieval and display capabilities, viewing the top sessions that were executed in the instance during the last 1 hour and filtering them based on the resource consumption.

Viewing Resource Consumption

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:

  • Sessions — Displays the distribution of the currently opened SQL Server sessions between active (sessions that are actively processing in SQL Server) and inactive sessions during the current sampling period.
  • Throughput — Divided to 3 metrics which represent the throughput of the monitored instance:
    • Sessions/Sec — The rate per second of sessions that were initiated during the current sampling period.
    • Transaction — The average number of active transactions on the monitored instance during the current sampling period.
    • Batches/Sec — The rate per second of statement executions during the current sampling period.
  • Blocked — Displays the total number of SQL Server sessions that are blocked during the current sampling period.
  • CPU — The Host CPU Usage the total percentage of CPU resources being used on the monitored host. The displayed value represents the total CPU that is consumed by all Windows processes (SQL Server and non-SQL Server processes). The Last 1 hour trend presents the activity on the instance during the last 1 Hour by presenting:
  • Workload — displays the general workload (average active sessions) during the specified time range.
  • 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

Viewing Current session details

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:

  • Active only — selected by default, presenting only the active sessions that are currently running, by clearing the check box, all inactive sessions will be presented as well.
  • Foreground only — selected by default, presenting only the sessions whose SPID number is higher than 50, by clearing the check box, the background sessions will be presented as well which holds the instance sessions ID’s 1 through 50.

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.

Viewing Session Details

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)

Viewing Session details with SQL PI

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.

Viewing Metrics details with SQL PI

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.

Monitoring Locks and Latches

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:

  • Locks table
  • Lock Types chart
  • Latches chart

Locks table

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.

Lock Types chart

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:

  • AllocUnit — a lock on an allocation unit
  • Application — a lock on an application-specified resource
  • Database — a lock on a database, including all of the database’s objects
  • Extent — a lock on a contiguous group of eight pages
  • File — a lock on a database file
  • HoBT — a lock on a heap of data pages, or on the BTree structure of an index
  • Key — a lock on a row in an index
  • Metadata — a lock on a piece of catalog information, also called metadata
  • Object — lock on table, stored procedure, view, and so on, including all data and indexes. The object can be anything that has an entry in sys.all_objects.
  • Page — a lock on an 8-kilobyte (KB) page in a database
  • RID (Rows) — Row ID; a lock on a single row in a heap

Use the list on the chart title to select which of the following lock types to display:

  • Waits — the rate of lock request wait events. Such wait events take place when lock requests cannot be satisfied immediately and require the caller to wait before being granted the lock.
  • Average Wait Time — the average time (in seconds) that elapses before a lock request wait is cleared.
  • Lock Requests — the number of lock requests and lock conversion requests per second.
  • Timeouts — the number of lock time-outs per second.

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.

  • Deadlocks — the number of lock requests per second that resulted in a deadlock. A deadlock occurs when multiple SQL Server sessions request conflicting locks in such a way that two locks are blocked by each other.

Latches chart

The Latches chart displays statistics on latch requests. This chart shows the following series of data:

  • Latch Waits — how many wait events for latches occurred in the specified time range
  • Total Wait Time — the total amount of time (in milliseconds) that latch requests spent waiting during the specified time range

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.

Tracking Current Lock Conflicts

The Blocking (Current) dashboard provides details for all current lock conflicts. This dashboard allows carrying out the tasks detailed in the following topics:

  • Handling Blocking Sessions, using the Blocking table.
  • Monitoring the processes blocked during the reporting period, using the Number of Blocked Processes chart.

Handling blocking sessions

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:

  • Blocked Processes — number of sessions that were waiting on locks held by others.
  • Lead Blockers — number of sessions that were not blocked, but were blocking others. Lead Blockers correspond to sessions in the Blocking table that do not have a parent in the Blocking chain (at level 1 in the tree).

Tracking Deadlocks and their Affected Objects

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:

  • Deadlocks
  • Databases
  • Objects
  • Applications

Deadlocks

The Deadlocks pane contains the following components, which provide information over all of the deadlocks that took place during the specified time range:

  • Summary — provides summarized data regarding the following aspects:
    • Overall lost time — the total amount of time that the terminated session (“deadlock victim”) was running before its termination.
    • Number of deadlocks — the total number of deadlock situations that took place during the specified time range.
  • Chained and Regular Deadlocks — contains the following components:
    • Chart — displaying the distribution of locks, with different color codes for chained and regular deadlocks.
    • Graph — displaying the number of regular and chained deadlocks
  • Table — displaying detailed data about each of the deadlocks, as listed below.

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.

Databases

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:

  • Involved Databases table — contains the following columns
    • Name — the name of the involved database.
    • Overall lost time — the total amount of time that the terminated sessions (“deadlock victims”) were running before they were terminated and rolled back.
    • Deadlocks — the total number of deadlock situations in which the database was involved during the specified time range.
    • Chained — indicates how many of the deadlock situations were chained deadlocks, that is, deadlocks that involve more than two sessions.
    • Regular — indicates how many of the deadlock situations involved only two sessions.
  • Deadlocks related to a database — clicking a specific database in the Involved Databases table displays in this section data about the deadlocks in which the selected database was involved, using the following components:
    • Chart — displaying the distribution over the specified time range of deadlocks in which the selected database was involved.
    • Table — displaying detailed data about each of the deadlocks in which the selected database was involved. This table is identical to the one displayed on the Deadlocks pane.

Objects

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:

  • Involved Objects table
  • Deadlocks related to an object — clicking a specific object in the Involved Objects table displays in this section data about the deadlocks in which the selected object was involved. The components of this pane (tables and a chart) are identical to the ones found in the Databases pane.

Applications

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:

  • Involved Applications table
  • Deadlocks related to an application — clicking a specific application in the Involved Applications table displays in this section data about the deadlocks in which the selected application was involved. The components of this pane (tables and a chart) are identical to the ones in the Databases pane.

Viewing I/O Statistics by Database Files

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:

  • A table that displays current SQL Server I/O statistics for each SQL Server database file.
  • A chart that provides a graphic representation of some of the I/O statistics for each row selected in the table.

Viewing the I/O by File table

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.

Viewing the I/O by File chart

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.

Viewing Database Usage

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:

  • Database Details — contains the Summary pane, which provides a graphical representation of the database space and history (either for a single database or for multiple databases), as well as several panes that display lower levels of the storage hierarchy.
  • TempDB — helps analyze TempDB activity and diagnose potential problems.

Monitoring SQL Server Databases

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:

  • Overview — general information about each database.
  • Transactions — details about the oldest active transaction in each database.
  • Backup Status — details on database backups, including date and total file sizes.
  • Properties — the properties of the various SQL Server databases.
  • In Memory

Overview

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.

Transactions

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:

  1. Select the SQL Activity > Sessions > Sessions table.
  2. In the Sessions table, browse for the requested SPID.
  3. Click the SPID number to display the Session Details page. Use this page to review the information relevant for handling the transaction, such as the transaction count and the text of the most recent SQL batch.

Backup Status

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.

Properties

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.

In Memory

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.

The In Memory view displays metrics on space usage by object in memory.

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.

Monitoring Database Details

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:

  • Monitoring database space and history — using the Summary pane, which displays a graphical representation of the disk space usage for databases, as well as various statistics for each database.
  • Monitoring file groups — using the File Groups pane.
  • Monitoring data files — using the Data Files pane, which displays all files (excluding the Transaction Log) in the selected databases.
  • Monitoring transaction logs — using the Transaction Logs pane.
  • Monitoring log files using the Log Files pane.
  • Monitoring tables — using the Tables & Indexes pane, which displays statistics for all tables and indexes in the selected databases (by default, 50 rows are displayed)

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

  • Monitoring disk space — using the Disk Space pane, which displays each logical disk on the server, with a detailed graphical representation of the disk space usage for all disk.

Monitoring Database History

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.

To view a particular graph, select it from the Database History list.

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 with truncate_only).
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.

Monitoring File Groups in the Selected Databases

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:

  • File Groups table
  • File Group Space chart
  • File Groups Growth chart

File Groups table

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.

File Group Space chart

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.

File Groups Growth 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).

Monitoring data files

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:

  • Data Files table
  • Data Files Space chart
  • Data Files Growth chart

Data Files table

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.

Data Files Space chart

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.

Data Files Growth 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).

Monitoring Transaction Logs in the Selected Database

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:

  • Transaction Logs table
  • Transaction Log Space chart
  • Transaction Log Growth chart

Transaction Logs table

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.

Transaction Log Space chart

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

Monitoring Log Files

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:

  • Log Files table
  • Log File Space chart
  • Log File Growth chart

Log Files table

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.

Log File Space chart

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.

Log File Growth 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).

Monitoring Tables and Indexes

The Tables and Indexes pane, which displays storage information for database tables and indexes, contains the following sections:

  • Table list — provides detailed information about the disk space usage by the database’s largest tables.
  • Indexes list — displays all indexes created for the table selected on the Table list.

Viewing space usage of a table

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:

  1. Set the criterion for a large table — either size or number of rows — by selecting a value from the Order By list.
  2. Set the number of tables to be displayed, by selecting a value from the Top list. Optionally — To filter the display, proceed to Step 4. To display the entire list according to the parameters set now, go to Step 5.
  3. Use the File Group Name field to display only tables belonging to a specific file group.
  4. Use the Table Name field to display either a specific table, by entering its exact name, or all tables that share a character string (for example, product), by entering that string.
  5. Click Submit.

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.

Viewing Indexes for the selected table

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
  • Fragmentation table
  • Index Distribution chart

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.

For information about the Fragmentation statistics displayed on the Fragmentation page, see the DBCC SHOWCONTIG topic in the Transact-SQL Reference section in the Microsoft® SQL Server Books Online.

How fragmented are my tables? The following values can be used for determining the overall fragmentation in tables:

  • Extent Scan Fragmentation and Logical Scan Fragmentation are expressed as percentage values.

    A value of -1 is displayed for heaps (tables without indexes) and for text structures.

  • Average Page Density shows how full each page is. This is affected by the FILL FACTOR setting used when creating the index; for example, immediately after creating an index with a FILL FACTOR of 30, the Average Page Density for that index is 30. This value is then change as data in the table is modified. Average Page Density can be thought of as the “real” fill factor at the time the data was collected, as opposed to the “original” fill factor that was specified when the index was created. The Original Fill Factor is used when an index is created to leave free space in index pages to allow for the insertion of new records without having to split the page.
  • Fill Factor Variance is a computed metric, used for comparing the Average Page Density with the original fill factor used when creating the index. The value of this metric is calculated as the absolute difference between the average page density (AvgPage Density) and Original Fill Factor. The value of this variance shows how much the fill factor of the index has changed since the index was created.
  • Scan Density is a value computed by dividing the optimal number of extent switches by the actual number of extent switches. It is expressed as a percentage value, where higher values indicate less fragmentation. Scan density is displayed with a value of -1 for tables that reside on more than one file.

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.

Index Distribution chart

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:

  • ABRAHAM: 5
  • GEORGE: 20
  • PETER: 25
  • STEVEN: 30
  • ZACH: 20 This means that five of the people have a name that is alphabetically before or equal to ABRAHAM, 20 people are between ABRAHAM and GEORGE, 25 people are between GEORGE and PETER, and so on. This chart can be used for identifying skewed indexes, that is, indexes that have a very uneven distribution of data. For example, if 95% of the table has the same value for a key, it is difficult for SQL Server to determine if it is useful index. Searches on values that fall in the 5% might find the index very useful, but searching on the value that occurs 95% of the time are not so useful.

Monitoring Disk Space

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:

  • How much space are the SQL Server database files using on each disk?
  • What file space is available? This pane features the Disk Space chart, which displays each disk on the server. The display is divided into SQL Data files, SQL Log files, Non-SQL files or Free Space. This chart allows to easily view how much of each disk is used by SQL databases, and how much is free.

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.

Monitoring TempDB Status

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:

  • User objects — Temporary objects explicitly created by users such as global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects — Created by the SQL Server Database Engine. For example, work tables to store intermediate results for spools or sorting.
  • Version store — Row versions that are either generated by data modification transactions in a database that uses read-committed using row versioning isolation, or snapshot isolation transactions, or Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

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.

Monitoring TempDB Space Usage

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.

Monitor TempDB Activity

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.

Viewing the Services

The Services dashboard provides detailed information, represented by graphs and tables, of the state of the various SQL Server support services.

Viewing the Service Status

The Services Status dashboard includes panes that allow carrying out the following tasks:

  • Monitoring the Status of the Current Services — using the Services Status table, which allows viewing the current status of each SQL Server support service, as well as the exact time when this status was last changed.
  • Tracking the Service Status History — using the Service Status History chart, which displays the status of the support services over the last hour.

Monitoring the Status of the Current Services

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

Tracking the Service Status History

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:

  • Stopped
  • Paused
  • Running
  • Not installed
  • Not configured
  • Configured

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.

Viewing SQL Agent Jobs

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:

  • Tracking the SQL Agent Jobs
  • Tracking Job Execution Messages
  • Viewing the SQL Agent Jobs History

Tracking the SQL Agent Jobs

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:
Blue The job run was cancelled.
Red The job run failed.
Green 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.

Tracking Job Execution Messages

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.

Viewing the SQL Agent Jobs History

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:

  • Never Run
  • Retrying
  • Running
  • Failed
  • Cancelled
  • Success

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.

Viewing SQL Agent Alerts

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:

  • Tracking SQL Agent Alerts
  • Tracking Recent Alert Occurrences

Tracking SQL Agent Alerts

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.

Tracking Recent Alert Occurrences

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.

Monitoring SQL Server Transactions using the DTC Dashboard

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.

Monitoring Full-text Indexes using the Full Text Search Dashboard

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.