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 ).
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 .
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):
There are some general guidelines what you need:
Keep in mind, these are rough guidelines. The choice also depends on the implementation.
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.
The CPU is tupically not a bottleneck, but here are some guidelines:
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... .
Some very quice recommendations:
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:
There are many infrastructure software setup things to think about. Amongst them (didn't catch them all):
Then, finally, the things you can do at application level.
First of all, he briefly explained the Tools you can use to monitor performance:
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:
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.
Hm... you're not going to find a discussion here, I'm afraid. But feel free to discuss this on mibuso to get another one's opinion.
You could go for RAID5 for data (mainly used for reading), but I would never do that. I don't want a write queue ... not even (and definitely not) for the data disks.
But again, feel free for opening a thread on mibuso or dynamicsuser... .
I'm not agree with you about RAID 5. The best practices on SQL Server 2005, for max performance, (source MCS France) are RAID 5 for data, RAID 0+1 for log, RAID 1 for tempdb.
All I can say is that I do my best in being as clear as possible :|.
When you make tables please tell the contents refers to what and please be clear when writing the stuff as some people are not up to your level
Almost got it right ... changed the "low changing fruit" to "low hanging fruit" .
Well, I as a competitor of SQL Perform ;c) found it somewhat annoying that permanently the SQL Perform Tools were mentioned ;c)) ignoring the fact that other magnificent optimization software is availabe, too ;c)))
But seriously: even from a more objective perspective I think it was too much advertisement and less content. I prefer last year's session.
Only in Holland and Belgium :-)
Part? I think you run that community ...
Last year it was all new but it seems that the basic knowledge is generaly available know.
It is up to methodolgy and partner community now.
I love being part of the SQL Perform community.
I agree ...
I was really happy with the new format of this presentation. Last year it was very technical, and in one hour you just can't do that. This years presentation was a great overview, and the sort of thing that lets you know all the areas you need to look at, and some starting points of where to go next. Think of it as "Hello World" for people implementing NAV on SQL.