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?

| improve this question | | | | |
  • Usual shot in the dark would be different execution plan on each environment. For starters we need eyeballs on a) the query and b) execution plans for the query from each machine. – Mark Storey-Smith Feb 16 '12 at 15:07
  • I understand performance differences among computers, but not that much - 2 secs VS 26 secs, same query, same data, and even faster computer. btw, i can't see much difference in execution plans. – jiangyan Feb 16 '12 at 15:13
  • Save the query plan XML from the two computers and upload both of them somewhere. A minor difference in a query plan can make a massive difference in performance. – Simon Righarts Feb 17 '12 at 9:18
  • 2
    -1 : No need for cursing. – Hogan Feb 17 '12 at 13:02

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.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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