Steps to Optimize SQL Server

infonote · 2008-01-17

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 · 2008-01-17

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 · 2008-01-18

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 · 2008-01-22

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 · 2008-01-22

I'm looking forward reading about your research.

kriki · 2008-01-22

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 · 2008-02-05

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 · 2008-02-05

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

Waldo · 2008-02-05

me 2

infonote · 2008-02-05

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 · 2008-02-06

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

Steps to Optimize SQL Server

Comments

Categories