Luc and kriki asked me to post the convergence sessions about sql performance in this forum...
What to start looking at ... .
Why should you tune your database?
Loss of user productivity
Cust of new hardware
Harware may only temporarily resolve
Understanding load being placed on your server
...
Seems logical, doesn't it, but try to explain to your customes that the application should be 'tuned' towards the usage of the application... .
The hardware restricting performance, is usually: Disk I/O, CPU, Memory and Network.
Most important: The Disk I/O. Some general recommendations:
- Use Physical disk instead of logical disk
- Sec/trasfer should be less than 15ms
- Transfers/sec should be less than 120 per disk
- Current disk queue length should be less than (2* # disks)
- Disk bytes/sec should be less than (10MB/sec per disk)
You should use the profiler, together with these counters, and see what it gives... .
Now, he began explaining about the RAID volumes. The main message: RAID 5 has got a poor write performance ... RAID 10 has got a high read performance. So: the .mdf is best but on RAID 10. The .log can go on RAID 1, but if shown pressure, RAID 10. Don't put the log on RAID 5. The tempdb is best put on a RAID 1, but if shown pressure, RAID 10. The Master should be put on RAID 1.
The causes of high I/O could be:
Memory pressure / Excessive paging / Severe blocking caused backlog of I/Os / Poorly designed queries (missing key indexes) (important for NAV!) / RAID 5 .
Demo time:
Just showing a "busy" machine with the performance counters above. Random performance issues: probably blocking.
Second thing to be looking into is CPU. Interesting counters are:
% processor time < 80%
% privileged time < 10%
Processor queue length < 2
Context switches/sec < 1000
SQL re-compilations/sec (lower is better)
The causes of CPU bottlenecks could be:
Compiles/recompiles for exec. Plans / Hash joins / Aggregate functions / Data sorting / Disk I/O activity / Other apps/services / Screen savers
Demo time:
He showed creating tables and dropping inside a loop and outside a loop. The recompiles in the second scenerio was (as expected) better.
Third: Memory
These are the counters:
Available MB > 10Mb
Pages/Sec < 50-300 (depending on disk perf)
Buffer cache hit ratio >= 90%
Free pages > 640
Memory grants pending = 0 (quite important!)
Target server memory - physical memory
Total server memory - target memory
Things you should take in count to resolve the memory bottleneck:
- Set to dynamically allocate or raise max
- Increase physical RAM
- Evaluate high read count queries
- /3GB switch in boot.ini (!)
- /PAE switch in boot.ini + AWE enable in SQL (!)
He didn't go into the network, because there should be no problem these days thanks to the current technology.
To conclude, there are also a number of SQL Specific counters. To investigate missing indexes: use the "Full Scan/sec". To investigat blocking, you can use:
- Total latch wait time (ms)
- Lock timeouts/sec
- Lock ait time (ms)
- Number of deadlocks/sec
It was an interesting session, and you should download the presentation if it comes available on Mibuso ... .
Comments
RIS Plus, LLC
MVP - Business Apps
Of course, before you reach this level of performance tuning you first need to add a maintenance plan and tune SIFT and Indexes.
If you find a customer with RAID 5 on a 10 Mbit line using VPN to connect to the database directly, there are more obvious things to do than tuning indexes. Our approach is to verify that the hardware is ok, and tackle that first if it is problematic. Once there are still performance problems on 'good hardware' then you start the other things.
Obviously you can do some of the obvious checks while you're reviewing the hardware, such as the checking the largest tables, take a quick scan of the (SIFT) indexes. If the largest tables are the ledger entry dimension entry tables and the analysis view tables for instance, you have a pretty good candidate for quick wins.
RIS Plus, LLC
MVP - Business Apps
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps