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)

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

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact