Requires Spotlight Cloud Professional

How to open the I/O by File drilldown for a SQL Server connection

  1. Direct your web browser to http://www.spotlightcloud.io. Sign in an click the Monitoring tab. See also Welcome to Spotlight Cloud Monitoring.
  2. Select a SQL Server connection. Select the I/O by File drilldown.

Open the IO by File drilldown for a SQL Server connection

I/O by File page

Show I/O statistics for database files.

By default this table is sorted in descending order of Total I/O. The Total I/O column values are overlaid on the table in the form of a bar chart. You can select a different metric to sort on from the drop down menu.

By default the file with the most Total I/O is selected. Select any file on the left pane (click on the row) to show statistics for the selected file in greater detail on the right pane.

Select a SQL Server database file

Click Maximize in the top right corner of the left pane to maximize the pane on the screen. Show more column metrics and show the statistics for the selected metric and file plotted over time.

Select multiple files to plot them over time.

Plot the selected statistic over time

Definitions to the metrics on the SQL Server IO by File page

Avg Read Wait Time

The average time (in milliseconds) SQL Server spent waiting for each read operation on the file during the given time frame.

Avg I/O Rate

The average rate (milliseconds per second) at which SQL Server performed both physical read and write operations for the file (reads rate plus writes rate).

Avg I/O Wait

Avg Wait Per I/O

The average wait time (in milliseconds) per I/O for the file.

Avg Write Wait Time

The average time (in milliseconds) SQL Server spent waiting for each write operation on this file in the given time frame.

DB Name

The file resides in this SQL Server database.

Disk

The file is stored on this disk.

Logical File Name

The name of the file.

Read

The number of megabytes read from this file in the given time frame.

Reads/s

The rate at which SQL Server performed physical read operations from this file.

Size

The number of megabytes on disk used to store the file.

Total I/O

The number of megabytes read from and written to this file since SQL Server started (megabytes read plus megabytes written).

Total I/O Wait Time

The total number of seconds SQL Server has spent waiting for I/O operations on this file since SQL Server started.

Total Reads Wait Time

The total number of seconds SQL Server has spent waiting for physical read operations on the file since SQL Server started. [SQL Server 2005 and above]

Total Writes Wait Time

The total number of seconds SQL Server has spent waiting for write operations on the file since SQL Server started. [SQL Server 2005 and above]

Type

The type of file. For example: log, rows. [SQL Server 2005 and above]

Writes/s

The rate at which SQL Server performed physical write operations to this file.

Written

The number of megabytes written to this file in the given time frame.