Foglight for SQL Azure 5.5.8.6

Unlike other panels in the Activity drilldown, which provide summarized information for all sessions, the Sessions table allows viewing a breakdown of a single session’s activity. As a result, the Sessions table allows carrying out the following tasks:

•

The table can be sorted to find the single most resource-intensive session in terms of CPU consumption, current wait time, amount of I/O performed, and so on.

•

Clicking a session on the table displays the selected session in the Session Details page, which provides detailed information about the session in several panes. For details, see Viewing the Session Details page on page 32.

These details can be displayed for all sessions (the default display). Alternatively, the table can be filtered via the following check boxes:

•

Active only—to display only sessions that are actively processing in SQL Azure, or are blocked or blocking

•

Foreground only—to display only foreground sessions, namely: sessions whose SPID number is higher than 50.

Table 2. The Sessions Table
Column	Description
SPID	The session unique identifier (Session Process ID). The unique number the Server has assigned for identifying the selected session.
Status	Indicates whether the session is active.
DB User	The SQL Azure 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.
Total I/O	The total amount of I/O operations carried out by the session since it connected to the SQL Azure.
Memory	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 Azure session.
Host Name	The name of the Client computer that established the SQL Azure 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.
Last Wait Type	The type of the last wait. The data for this parameter is reported directly from SQL Azure.
Last Wait Resource	The resource on which the current session is waiting. The data for this parameter is reported directly from SQL Azure and, for performance reasons, is not resolved to actual resource names during normal data collection.
Cache Hit Ratio	The percentage, for this session, of buffer cache hit ratio, namely: 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 Azure 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.
Last. Tran. Start Time	The exact time when the last transaction started.

Viewing the Session Details page

The various sections of the Session Details page provide detailed information about the selected session.

The Session Details page displays the following sections:

•

SQL Azure Session Identification on page 32

•

Session Summary table on page 32

SQL Azure Session Identification

The Session Identification section displays several parameters that provide general information about the selected session, as follows:

Table 3.
Parameter	Description
Session ID	The session unique identifier (Session Process ID). The unique number that SQL Azure has assigned to identify the selected session.
Logon Time	The time when the user logged on to the session.
Status	Indicates whether the session is active or inactive.
DB User	The SQL Azure login name for this session.
Program	The name of the program the user is running to access SQL Azure.
Waiting for:	The resource on which the session is waiting.

Session Summary table

The Session Summary table contains the panes described in the following sections:

•

Running SQL on page 32

•

SQL Summary on page 33

•

Locks on page 33

Running SQL

The Running SQL pane provides details about the statement that is currently being executed by the selected session.

This pane contains a table with the following columns:

Table 4.
Column	Description
SQL Batch	The full text of the SQL execution batch.
Active	Indicates whether the statement is active or inactive.
Database	The database where the statement is executing.
CPU Time	Total CPU time consumed by the statement.
Physical IO	The total amount of physical IO consumed by the statement.
Active Time	Summary of all Sum of active wait events incurred by the SQL statement.
Rows	Total number of rows retrieved by the statement.
SQL Batch	The full text of the SQL execution batch.
Buffer Hit Rate	The percentage of database page I/O requests that were satisfied from the buffer cache and therefore did not have to perform disk reads. This ratio measures how efficiently SQL Azure is using the memory allocated to its buffer cache. A low buffer cache hit rate indicates that SQL Azure is finding fewer pages already in memory, and therefore has to perform more disk reads. This is often caused by either lack of SQL Azure memory or use of inefficient SQL queries, which are accessing a very large number of pages in a non-sequential manner. The best figure varies from one application to another, but ideally this ratio should be above 90%.
NOTE: The columns detailed below are not displayed by default, and need to be selected for display via the Customizer button. For details, see Components Shared by all Foglight for SQL Azure Screens on page 13.
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.
Start Time	The time when the last execution started.
Statement Up Time	The time, in seconds, since the statement started.
Procedure Memory Usage	The total amount, in MB, of physical memory consumed by the statement so far.

SQL Summary

The SQL Summary pane displays summarized data about each of the SQL Statements that were executed by the session, via a table that is identical to the table displayed in the Running SQL pane.

Locks

The Locks pane displays all locks held or requested by the selected session.

The Status column indicates whether the locks have been granted to the session, or are currently blocked and waiting on another session.

The Locks table displays the following columns:

Table 5.
Column	Description
Lock Type	The type of the currently locked resource (Database, Table, Page, Row, Extent, and so on)
Mode	The kind of lock being applied to the resource (Shared, Exclusive, Update, and so on)
Status	The status of the lock: Granted, Convert, or Wait
Locked Object	The name of the currently locked object
Database	The database being used by the current process
Count	The number of times locks of the same type were applied to the same resource during the specified time range
Index	The name of the index that is being used by the current process
Object	The name of the currently locked object