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
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)This is only a preview. Your comment has not yet been posted.
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.
Posted by: |