Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Request for sample SQL disk I/O statistics

    I'm trying to get a handle on an issue that involves Disk I/O speed and I need some representative samples of what people have in the field today. I'd like anyone willing to run this script to post the results for me.

    Only run this if you are positive that you are NOT using differential backups!!!
    Code:
    --  ptp  20080318  Backup database to NUL: device to get I/O stats
    
    DECLARE @c		NVARCHAR(200)
    
    SET @c = 'BACKUP DATABASE [' + Db_Name() 
    +  '] TO DISK = ''NUL:'' WITH STATS = 25'
    
    EXECUTE (@c)
    All this code does is build a string with a backup command that discards the backup file (sends it to the NUL: device), then executes that command. This causes SQL Server to "go through the motions" of doing a backup, without actually writing a backup file to your disk, but it displays the I/O statistics when it finishes and those are what I really want.

    I'm specifically interested in databases from 5 to 500 gigabytes in size, although larger ones would help too. It would really help me if you could include at least a little bit of information about your disk hardware (local disk, SAN, NAS, other) and RAID configuration (0, 1, 5, 0 + 1).

    This kind of information changes rapidly, so the 24 month old data that I've got on hand is nearing historical value, but it has little practical value! Thanks for anyone willing to take a few minutes to help me out on this!

    -PatP

  2. #2
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Basics:
    - 8 gig DB on local SCSI drives
    - Single Filegroup
    - .mdf file does not reside on same drive as swap file
    - SQL Server 2000 SP4
    - DELL Server P2600 (Intel Xeon @3.06GHz)
    - 2 gigs RAM
    - SBS 2003 SP2
    - RAID 5


    Results:
    25 percent backed up.
    50 percent backed up.
    75 percent backed up.
    Processed 144664 pages for database 'MyDatabase', file 'MyFile' on file 1.
    100 percent backed up.
    Processed 1 pages for database 'MyDatabase', file 'MyFile_log' on file 1.
    BACKUP DATABASE successfully processed 144665 pages in 11.022 seconds (107.520 MB/sec).

  3. #3
    Join Date
    Mar 2008
    Posts
    1
    Basics:
    - 200 gig DB on DAS SAS drives (24 15k)
    - multiple Filegroups
    - .mdf file does not reside on same drive as swap file
    - SQL Server 2005 SP2
    - IBM x3650 (Intel 1 quad core @3.0GHz)
    - 12 gigs RAM
    - win 2003 ent r2
    - RAID 10

    25 percent processed.
    50 percent processed.
    75 percent processed.
    Processed 13528 pages for database 'd7', file 'a' on file 1.
    Processed 12934208 pages for database 'd7', file 'a_1' on file 1.
    Processed 8510440 pages for database 'd7', file 'x_1' on file 1.
    Processed 157280 pages for database 'd7', file 'b_1' on file 1.
    100 percent processed.
    Processed 6 pages for database 'd7', file '_Log' on file 1.
    BACKUP DATABASE successfully processed 21615462 pages in 279.916 seconds (632.596 MB/sec).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •