First of all, the question is nothing to do with SQL performance optimization.
I have a .BAK SQL Server backup, and have restored it on my computer.
My computer: i7 2600 3.4GHz, 240GB SSD, 8GB RAM, Win 7 Ultimate x64, SQL Server 08 Ent x64.
When I ran a particular stored procedure the execution time is about 22 ~ 28 sec.
I restored the same .BAK file to my several of my colleague's computers, and run the same stored procedure, execution time is about 2 ~ 4 seconds.
*My colleague's computers: Various Intel/AMD CPUs, SATA HDD, Windows XP or Server 2003 or Windows 7 (x86/x64), with SQL Server 2005 or 2008; one colleague even has the same OS and SQL Server as mine.
I also have a server, XEON 5600 CPU, Windows Server 2003, SQL Server 2005, restore and run in about 46 seconds.
I've tried every method I could think of however I have no idea why my computer is running the stored procedure so slowly.
Why would my computer, when spec-wise it is so much faster, perform so much slower?
Best Answer
Is Instant File Initialisation on on your colleagues computer?
Basically, this hugely decreased the time to initialise the MDF file. Without it, the entire file needs written with zeros. The log (LDF) file is always zero-initialized.