Sysadmin: SQL server performance madness

Sunday, April 12, 2009

Technology I’ve just lost 2 days going completely bananas over a performance issue that I could not explain.

I’ve got this Dell R300 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

In my previous blog entry, 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:

MachineOperating SystemTest without TransactionTest with Transaction
MacbookProWindows Server 2008 x64324 ms22 ms
DesktopWindows XP172 ms47 ms
ServerWindows Server 2008 x648635 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

And then I noticed this in the Policies tab of the Disk Device Properties :

DISK Device Properties

Just for the lulz of it, I ticked the box, close the properties

Enable advanced performance

And then tried my query again:

MachineOperating SystemTest without TransactionTest with Transaction
ServerWindows Server 2008 x64254 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.

Entry Filed under  :  Database,Hardware,SQL Server,sysadmin

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links