Embarcadero Performance Analyst

I/O Page Statistics

One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations.This explains why I/O analysis is important from a DBAs viewpoint.If there are large number of I/O operations in SQL Server,it can be minimised by maximising the size of the buffer pool.

The I/O page includes the following sections and statistics:

1. Bottleneck Analysis:-This section identifies the top I/O related waits that might be currently a cause of lessened performance. It also includes the current hottest database/log files and a summary of I/O activity.
2. I/O Access Pattern:-There are many different routes that SQL Server can take to access various objects contained in a database. The I/O Access Patterns display summarizes the methods SQL Server is using to satisfy end-user requests for data.
3. Key System Analysis:- Some of the key I/O values are reported in this section:
   1. Lazy Writes:-The lazy writer is a SQL Server system process that flushes out batches of dirty and aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The Lazy Writes statistic represents the number of buffers written by the buffer manager’s lazy writer process.
   2. Log Cache Reads:-The Log Cache Reads statistic represents the reads performed per second through the log manager cache.
   3. Log Flushes:-The Log Flushes statistic represents the total number of log pages for all databases written to disk by the log writer process. A log flush occurs when SQL Server writes all changes from the database’s log cache out to the database’s log files on disk.
4. SQL Analysis:-The SQL Analysis section provides the user with a quick overview of the percentage of Reads, Writes, CPU, and Elapsed Time the Top SQL statements have used.Detailed analysis of the Top expensive SQL statements can be done using the Top SQL view
5. Workload Analysis – Top I/O Hogs:-The Top I/O Hogs display shows the top sessions that have caused the most physical I/O on the system (both reads and writes).

I/O Detail

The I/O Detail includes the following tabbed pages:

1. Database I/O:-The Database I/O tab of the I/O Detail includes the following sections:
   
   1. Database Read/Write Summary:-The Reads statistic represents that number of physical database page reads that are issued per second by SQL Server. The Writes statistic represents the number of physical database page writes issued by SQL Server.
   2. Database I/O Detail:-This gives details about the number of reads and writes issued against the database,The total amount of time that processes have waited for I/O operations to complete, in milliseconds.
   3. Database Bytes Read/Write Summary:-The Database Bytes Read/Write Summary statistics represent the number of bytes read and written by SQL Server.
2. File:-File tab is very similar to the Database I/O.The File tab of I/O Detail includes the following sections:
   
   1. File Bytes Read/Write Summary
   2. File I/O Detail
   3. File Read/Write Summary
3. System I/O:-The System I/O tab of the I/O Detail includes the following sections:
   
   1. AWE I/O:-AWE is Address Windowing Extensions.It is used by SQL Server for supporting large amounts of RAM. Some of the information available in this section are:-
      1. Lookup Maps:-The number of times that a database page was requested by the server, found in the buffer pool, and mapped. When it is mapped, it is made a part of the server’s virtual address space.
      2. Unmap Pages :-The number of SQL Server buffers that are unmapped.
   2. I/O Activity:-This section gives information about the number of transactions that have occured in the database,number of log flushes for the server etc.
   3. Physical I/O:-This section gives information about page reads,page writes,lazy writes etc.
   4. Space I/O:-This section gives information about the extents allocated,page splits,extents allocated,page allocated etc.
4. User I/O:- The User I/O tab of the I/O Detail includes the following sections:
   
   1. Lock Activity:-The Lock Activity section details performance statistics that reflect how SQL Server is handling lock operations. It shows the number of deadlocks detected by SQL Server,total number of locks acquired by the server,total number of table lock escalations and the number of blocked users.
   2. SQL Activity:-It displays statistics about the number of SQL compilations,SQL recompilations etc.
   3. Temporary Object Activity:-This section displays statistics that reflect how SQL Server is handling the creation of temporary objects, such as temporary tables used in stored procedures.
   4. User Object Activity:-It displays statistics how SQL Server is performing object access operations.It displays the number of Full scans,Probe scans(It is used to directly find rows in an index or base table),Index searches etc.