Steps to Optimize SQL Server

infonote

Hi,

I have a :?:

What steps/guidelines do you follow when you optimize your sql server database besides code improvement and indexes.

And one last question, what tools do you use to measure performance of the database.

After all, you need to measure running processes, system resources etc before and after a change to verify that an improvement has been made.

Thanks in advance

Waldo

This is (in my honest opinion) a very good question. Unfortunately, there are not many people that can give you one answer. I guess we all have our own methodology which has been succesful in the past, so which we try to use in all projects... .

For me, it comes down to:
1. Hardware check
2. Index tuning
3. Code tuning
4. Locks

I use SQLPerform toolswith MS SQL Profiler, MS SQL Server Management Studio, Performance Monitor, OneNote (),... (I probably forgot some).

There are workshops you can follow. I attended the SQLPerform workshop, which was very valuable for me.

stryk

This would be my list (in order of priority):

1) Platform: Hardware, Installation & Configuration (OS, SQL Server, etc.)
2) SIFT Optimization
3) Index Optimization
4) Block- & Deadlock Detection and Resolution
5) C/AL Code Optimization
6) Business-Process Desing, Workflow, Userhandling

Proceedings (and usage of the tools below) described in the NAV/SQL Performance Field Guide (and of course plenty of other documents, webpages, blogs, etc.)

I use

1) NAV/SQL Performance Toolbox
2) Windows Performance Monitor
3) SQL Server Profiler
4) SQL Server Management Studio
5) NAV Client Monitor/Code Coverage

And I also perform workshops, trainings, etc.

infonote

Thanks a lot for the replies.

I am currently researching blogs/forums a bit. I will provide a summary of my findings with sources and quotes 9for further detail).

It is informal. Obviously what works in 1 system will not work on another. However, some recommendations apply to all.

I will post it when I feel I have researched a bit.

However, the topic is vast and there are a lot of variables involved.

Waldo

I'm looking forward reading about your research.

kriki

infonote wrote:

Thanks a lot for the replies.

I am currently researching blogs/forums a bit. I will provide a summary of my findings with sources and quotes 9for further detail).

It is informal. Obviously what works in 1 system will not work on another. However, some recommendations apply to all.

I will post it when I feel I have researched a bit.

However, the topic is vast and there are a lot of variables involved.

And why not blogging on Mibuso?

infonote

Here is the link. It is just a summary of what I found with links to the respective person/thread.

http://www.kaizenlog.com/2008/02/05/nav ... endations/

You can either comment or continue posting here.

Did not know that Mibuso has blogs also. Will sign up.

DenSter

What's wrong with that website? I see lots of jumbled text.

Waldo

me 2

infonote

Hi,

Yes it is a but jumbled. I will have to check the theme.

In the meantime, go to the bottom of the post and click on the print button.

kriki

infonote wrote:

Here is the link. It is just a summary of what I found with links to the respective person/thread.

http://www.kaizenlog.com/2008/02/05/nav ... endations/

You can either comment or continue posting here.

Did not know that Mibuso has blogs also. Will sign up.

Since short we have: http://www.mibuso.com/forum/viewtopic.php?t=23135