Database unusable whist doing a database restore (SQL Server 2008 R2)

Question

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?

Answer 1

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.