Convergence 2007 Diary: Optimizing Performance on SQL Server

Sorry for writing this entry just now, but my battery was empty, so I had to write this down on paper first ... and after a 10-hour drive from Copenhagen to Beerse (Belgium) ... my head wasn't feeling that fresh to write decent stuff... (not that this is decent, but anyway Indifferent).

This was the third time that I attended Hynek's session. The first two were exactly the same, but Hynek told me he changed the contents, so I decided to go again, and it was worth it. Not that I learned something new, but SQL Server Performance Tuning on Dynamics NAV is quite a complex subject, and you know what they say: "repetitio mater studiorum est".

He handled both infrastructure and application in one hour. You can imagine it went fast Indifferent.

Only 10 to 20% of the problems are due to infrastructural problems. This is not much, but if it's set up wrong, it could really mess up for the application as well ... .

When we think of "infrastructure", these items are important (in order of importance):

  • RAM
  • DISK
  • CPU
  • NETWORK

 RAM:

There are some general guidelines what you need:

DB Size/Users

0-50

51-100

101-150

151-200

>200

<=25 Gb

4

8

8

12

12

<=50 Gb

8

8

12

12

16

>50 Gb

8

8

12

12

16

Keep in mind, these are rough guidelines. The choice also depends on the implementation.

DISK:

The disks are the slowest components, thus very important to choose the right DISK configuration.

First of all: Don't use RAID 5. Use as many disks as you can afford, with a minimum of 3 times RAID 1 (=6 disks). Why? To split all OS files, all Transaction Log files and All Data files. If you have more than 6 disks, scale the array of the data files up to RAID 10.

CPU:

The CPU is tupically not a bottleneck, but here are some guidelines:

DB Size/Users

0-50

51-100

101-150

151-200

>200

<=25 Gb

2

4

4

6

6

<=50 Gb

4

4

6

6

8

>50 Gb

4

4

6

6

8

Now, Hynek wasn't that a fan of duo or quad cores, because it actually just performance 60% of the performance if you compare it with full CPU's... .

NETWORK:

Some very quice recommendations:

  • 100Mb minimum
  • The network should be switched (as switched as possible)
  • On server side, best you use a 1Gb network

Now, for planning your hardware, you shouldn't only use these guidelines. Also take for instance in account other factors like the annual business growth, seasonality, ... .

On software side, it is best to keep everything up-to-date, but be aware:

  • 2000 is not 2005: it does not behave the same
  • 2005 uses tempdb more ... And it's may be a good idea to put it on a seperate spindle.
  • 4.00 update 6 is a good release:
    • It fixes some SIFT issues
    • It does not use OPTION (FAST xx) any more
    • Is uses index hinting by default ... Bewar of that!

There are many infrastructure software setup things to think about. Amongst them (didn't catch them all):

  • Degree of Parallellism should be 1
  • Split the TL, Data Files and Tempdb (if necessary)
  • Maintenance:
    • Update statistics
    • Rebuild indexes

Then, finally, the things you can do at application level.

First of all, he briefly explained the Tools you can use to monitor performance:

  • SQL Profiler
    Mainly used for analyzing queries that come from the Dynamics NAV client. Also for analyzing deadlocking and timeouts
  • Client Monitor
    To record the server calls from within NAV. It links certain SQL queries to pieces of code in C/SIDE.
  • SQL Server Mgt Views
    Keep in mine: only SQL Server 2005 has got these. Interesting dm views are:
    • Dm_db_index_usages_stats
    • Dm_exec_query_stats
    • Dw_os_wait_stats
    • ...
  • PerformTools

Demotime

Hynek showed us how you can use profiler, client monitor, code monitor and some of his tools. The Benchmark Toolkit was used during the demo for creating load on the database.

These tools can be used to do index tuning. In this stage, you are going to get your write transaction down by deleting/disabling sift (not the ones that are frequently run) and disabling/creating indexesen doen we - or just should do when you're tuning that hell out of you:

  • Eliminate Head
  • Optimize your code (you know... Findinstructions
  • Optimize the process: break Llong transaction
  • App Setup: e.g. Auto cost posting / Find as you type
  • Data Archiving
  • ...

Focus:
You should focus on "low hanging fruit". That means, where it's fairly easy to gain performance with. Usually this means using the HOT tables. Which are the hot tables? There are the tables that causes most of the probles. Using the SQLPerformTools, this is quite easy to find out... . 

Hynek also warned us for the fact that 4.0 SP3 update 6 does index hinting by default.  Just know this, and try it out what works the best: disabling index hinting or not).

That was all that I wrote down. I hope it's still useful.

Enjoy!

Comment List
Related
Recommended