SQL Server – Logical Disk Read Bytes/sec and disk io

In the SAN environment , the Logical Disk Performance objects counters monitor the logical partitions.

 I use the Logical Disk Read Bytes/sec  as a starting point when I troubleshoot Disk IO issues. If I can isolate the activity , this gives me a good guideline as to Read rates from the SAN as viewed from the OS.

 The Logical Disk Read Bytes/sec  and the Logical Disk Writes Bytes/sec  display rates of transfer for read\write .

Check SQL Server – Backup to NUL trick - read throughput as an example of how to use the counter to troubleshoot disk IO .

Use Perfmon to initiate a monitor. Isolating the activity onto one disk and stopping all other activity is a good starting point to define the rates.

See Also

Window Perfmon scripting, SQL Server perfmon and how to perfmon

IO_completion on disk solved with Wait Stats and Perfmon

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – Backup to NUL trick - read throughput

A neat test for read speed backup throughput is to use the Backup to NUL .

NUL is a special “file”. It's as a nul device , anything  “written” to it is discarded.Don’t confuse with the SQL reserved word NULL.

Before you run this statement , note that even as the file is not going anywhere , SQL Server still considers it to be valid BACKUP – therefore any subsequent LOG backups would be invalid , until you commit another FULL BACKUP.

Use the COPY_ONLY  option to avoid this problem

   1:  backup database MY_DB to disk = 'NUL:' with stats = 10
Sample output:
Processed 1706648 pages for database 'MY_DB', file 'MY_DB' on file 1.
100 percent processed.
Processed 38 pages for database 'MY_DB', file 'MY_DB_log' on file 1.
BACKUP DATABASE successfully processed 1706686 pages in 28.221 seconds 
(495.417 MB/sec).

 

Accompany the statement with  : Logical Disk Read Bytes/sec on relevant disk  to obtain read throughput

 See Also

Copy only backup in SQL server 2005 and not break the backup chain

Disk IO performance , disk block size tuning and SQL Servers

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server –WRITELOG and how to reduce it

SQL Server Books online defines the wait type WRITELOG as “Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. “

A checkpoint writes all SQL dirty pages , currently in the buffer,  onto disk. Transaction commits make  data modifications permanent in the database.

A few things to note:

1) Top priority – check for disk io susbsystem performance.

2) The sys.dm_io_virtual_file_stats DMV  assists in identifying slow performance by returning statistics on IO for the database files.

io_stall on sys.dm_io_virtual_file_stats is  a good indicator of IO issues. There is no definite number , but the lower the better.

Benchmark some systems on relevant workload – and use as a guide.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'SQLSERVERDB'), 2);

3) If transaction logs are sharing the same drives as data then separate out onto different drive \ mapped to a different IO channel.

4)It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause

See Also

Calculate disk IO throughput and MB per second

SQL Server IO patterns and RAID levels

Disk IO performance , disk block size tuning and SQL Servers

SQL Server Latency

Latency is the time delay measure in the system.  SQL Server  can only be as fast as the slowest component in the system.

Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads.

Latency is about benchmarking certain limitations , such as disk speed  , and acknowledging that relative to the speed of light – delays occur. Use Production data or SQL Server test data generation testing tools to benchmark systems.

Database server latency is also about finding the acceptable level of delay. Yes, the IO request may not be 3 ms – but 6 ms may be acceptable to the users.

In a large database server ecosystem – multiple requirements are considered. Balancing budgets, business requirements  and system management  are factors in finding the acceptable level of latency.

SQL Server since 2005 has offered some useful methods of starting the analysis.

Use these two DMVs to develop a quick overview of where some delays are occurring. Use the information to focus on bottlenecks. There are other ways of measuring Disk IO performance such as  SQLIO

sys.dm_io_pending_io_requests  - View pending I/O request(s) in SQL Server.

sys.dm_io_virtual_file_stats  Returns I/O statistics for Database  data and log files. Measure IO transaction rates and sys.dm_io_virtual_file_stats

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk.  The purpose of  asynchronous IO is to release resources and  for other tasks to progress.

i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur.

 When this occurs , you’ll see an error message IO Requests taking longer than 15 seconds to complete

IO  writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server memory region

When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.

Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging

The write-ahead logging method ensure the transaction log is written to disk  and is fundamental to the Recovery Manager.

How is the dirty page written to disk?

Eager writing   - Nonlogged bcp,  SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.

Checkpoint  writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.

Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

a)       net stop mssqlserver

b)       SHUTDOWN

c)       ALTER DATABASE adding a file

 

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint

Author: Jack Vamvas (http://www.sqlserver-dba.com)