up vote 2 down vote favorite
share [g+] share [fb]

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?

link|improve this question
feedback

1 Answer

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.

link|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 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. – syneticon-dj Jan 5 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 at 12:50
feedback

Your Answer

 
or
required, but never shown
discard

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