Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was asked to create a database from a backup for reporting and whilst transferring the file from backup to a disk on the server the database became unusable.

Then again whist running the restore script, it was again running incredibly slowly. Not sure if it is a configuration issue...

  • SQL server is running on an ESXi host (4.0 Build 398348)
  • Win 2008 R2
  • 8GB Ram (SQL has most of this)
  • Single CPU (due to licencing)
  • Storage is locally attached rather than SAN/NAS
  • SAS disks 10k (I think)

Here is the backup script (fairly standard)

RESTORE FILELISTONLY
FROM DISK = 'E:\Backup\[DatabaseName].bak'

RESTORE DATABASE [DatabaseName]
FROM DISK = 'E:\Backup\[DatabaseName].bak'
WITH 
    MOVE '[DatabaseName]_Data' TO 'E:\DatabaseFiles\[DatabaseName].mdf',
    MOVE '[DatabaseName]_Log' TO 'E:\LogFiles\[DatabaseName]_log.ldf'

ALTER DATABASE [DatabaseName] SET MULTI_USER

Folders DatabaseFiles and LogFiles are mounted NDFS partitions.

Are there any tests I could perform to identify the issue?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Since the server runs virtualized, there is a good chance you are running into an I/O bottleneck. Check your disk performance counters in the Windows guest - specifically idle time and queue length - and take a look at ESXi's "Datastore" and "Storage adapter" latency performance counters - a decently performing storage would show values in the magnitude of 10 ms there. If you see more than 100 ms, the storage is probably overloaded.

share|improve this answer
    
As I thought.. I followed the guidance on the link you provided - although it peaked and then levelled off (although a smaller file) Any recommendations? Would moving it to a different raid in the server (which currently has nothing on it) make any difference? Or is it the way the disk is configured? –  Matt Jan 5 '12 at 9:32
    
Usually write I/O is the problem - ESXi does a lot of synchronous writes - if the server does not have a RAID controller with write cache enabled (which it should only be if there is a BBU or another construct to prevent cached data loss on power loss for consistency reasons) you are going to see terrible performance. –  the-wabbit Jan 5 '12 at 10:50
    
It has a raid controller but I don't know if write cache is enabled. I believe the raid controller does have a BBU and UPS. Thanks for your help - I think you may have found the problem! –  Matt Jan 5 '12 at 12:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.