October 29, 2009 at 10:18 am
Got an odd one here, that I hoped one of you very helpful people reading this, may be able to clear up for me.
I execute a backup script to backup a database to C:\Database Backups on the local server.
The backup returned for following information....
Processed 24616 pages for database 'myDatabase', file 'myDatabase_Data' on file 1.
Processed 1 pages for database 'myDatabase', file 'myDatabase_Log' on file 1.
BACKUP DATABASE successfully processed 24617 pages in 18.313 seconds (11.011 MB/sec).
The problem is the execution actually only took 3 seconds!! So why is it telling me 18 seconds??????
I run the same backup, only this time to a file share location using a \\UNC path and returned details said the backup took 5 seconds which it did. So I'm a little confused as to why SQL server thinks it took 18 seconds to backup to the local drive!
Any ideas peeps?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 30, 2009 at 4:07 am
Hey Dave,
Is it repeatable? If you back up the database to the local drive, does it always take less time than indicated in the message? How about to the UNC path?
Most often, these timing things are related to either:
(1) Automatic clock synchronization
(2) Variable-speed processors
In the first case, the server has its clock synchronized regularly. When the backup starts, the server hosting SQL Server is 13 seconds behind the 'true clock'. Clock synchronization occurs while the backup is running. The elapsed time is therefore over-stated by 13 seconds when the operation completes.
Second case: the server contains hardware to speed up or slow down the processor based on demand, or power consumption targets (some blade servers do this). Since SQL Server uses clock frequency for timings, changes in processor speed can result in significant clock skew, and other timing-related oddities.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 30, 2009 at 4:31 am
Hi,
Yeah, I done it about 4 times. But backups to a UNC are accurate and repeatable
We do have 2 processors on the server - both the same spec, so is it potentially that the work is being split between the processors, generating the clock skew?
What would I need to do to check the clock synchronisation?
Ultimately, I'm more concerned the database is backed up, but if performance becomes an issue, it could be a red herring and cause a few headaches, so it'd be good to fix.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 30, 2009 at 4:49 am
For blade servers, there is a BIOS option to determine whether the server varies the speed of the CPUs according to load or not. I believe this is a 'green' feature. It also means we have to resynchronize our server's clocks every few minutes to minimize the skew. Turning the option off requires a reboot - and we only have scheduled down time twice a year 🙂
If you are lucky enough to have dedicated platform staff, check with them. Otherwise, I guess you'll need to check the manuals/server BIOS yourself 😉
BTW this is just an issue that has affected me, and just a guess at what might cause the behaviour you describe. You could check the server's event logs to see how often the time is being corrected and by how much.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply