I’ve just lost 2 days going completely bananas over a performance issue that I could not explain.
I’ve got this [Dell R300][1] rack server that runs Windows Server 2008 that I dedicate to running IIS and SQL Server 2008, mostly for development purposes.
![Dell PowerEdge R300 Rack servers][dellr300]
In my [previous blog entry][2], I was trying some benchmark to compare the performance of Access and SQL Server using INT and GUID and getting some strange results.
Here are the results I was getting from inserting large amounts of data in SQL Server:
| Machine | Operating System | Test without Transaction | Test with Transaction |
|---|---|---|---|
| MacbookPro | Windows Server 2008 x64 | 324 ms | 22 ms |
| Desktop | Windows XP | 172 ms | 47 ms |
| Server | Windows Server 2008 x64 | 8635 ms!! | 27 ms |
On the server, not using transactions makes the query run more than 8 seconds, __at least an order of magnitude slower than it should!__
I initially thought there was something wrong with my server setup but since I couldn’t find anything, I just spend the day re-installing the OS and SQL server, applying all patches and updates so the server is basically brand new, nothing else on the box, no other services, basically all the power is left for SQL Server…
### Despair
When I saw the results for the first time after spending my Easter Sunday rebuilding the machine I felt dread and despair.
The gods were being unfair, it had to be a hardware issue and it had to be related to either memory or hard disk, although I couldn’t understand really why but these were the only things that I could see have such an impact on performance.
I started to look in the hardware settings:
![Device Manager][screen1]
And then I noticed this in the Policies tab of the _Disk Device Properties_ :
![DISK Device Properties][screen2]
Just for the [lulz][3] of it, I ticked the box, close the properties
![Enable advanced performance][screen3]
And then tried my query again:
| Machine | Operating System | Test without Transaction | Test with Transaction | Server | Windows Server 2008 x64 | 254 ms!! | 27 ms |
|---|
A __nearly 35 fold increase in performance!__
### Moral of the story
If you are getting strange and inconsistent performance results from SQL Server, make sure you check that _Enable advanced performance_ option.
Even if you’re not getting strange results, you may not be aware of the issue, only that some operations may be much slower than they should.
Before taking your machine apart and re-installing everything on it, check your hardware settings, there may be options made available by the manufacturer or the OS that you’re not aware of…
Lesson learnt.
[1]: http://www.dell.com/content/products/productdetails.aspx/pedge_r300?c=us&cs=555&l=en&s=biz
[2]: /2009/access-vs-sql-server-some-stats-part-1/
[3]: http://encyclopediadramatica.com/Lulz
[screen1]: /wp-content/uploads/2009/04/screen1.png
[screen2]: /wp-content/uploads/2009/04/screen2.png
[screen3]: /wp-content/uploads/2009/04/screen3.png
[dellr300]: /wp-content/uploads/2009/04/pedge_r300_overview1.jpg