Need Tips On SQL DB performance issues

ajaybabuCh

Hi

Can anybody give the list of points to increase the performance with
Navision SQL DB.

1) Do I need to click the optimize button for each table OR is there nay facility to Optimize all the tables.

2)I am already using the SQL commands like FINDSET,FINDFIRST etc.,
is there anything i need to optimize my code

3)I got one download in this forum , that will give list of unused keys in
Navision DB.
Can I deactivate all unused keys through Navision code.

kriki

Search the forum for SQL performance. There are a lot of posts on the topic.
There are also 2 downloads that are quite good to explain what to do.

Waldo

I think you're asking something that takes too much of an effort to go into enough detail... . Indeed, if you search the forum, you can glue together the bits and pieces to do a succesful tuning effort, but even then, I doubt if the information will be complete.

I advice to do your first tuning-effort together with an export, and then go for it yourself. May be use SQLPerform for it the first time.

To (try to) give a quick answer to your question:
1) Yes, you can do that, but what I would do also is:
- a daily statistics rebuild
- a daily index rebuild
2) You should watch the webcast that you can download on this forum:
http://www.mibuso.com/dlinfo.asp?FileID=808. Keep in mind to first tune your indexes, then go after your code!
3) Never tried that download. I won't do it.

May some other tips:
- see that your infrastructure corresponds with the recommendations (also a webcast: http://www.mibuso.com/dlinfo.asp?FileID=809)
- see that you're working on the latest NAV runtime version
- Get familiar with the SQL Server Profiler and try to base your tuning on its output
- concentrate on the HOT tables
- Use the SQLPerform Analysis tool to identify the HOT tables. If this is not possible, try to identify them by using the profiler
- first tune the indexes
- only maintain SIFT when necessary (for processes, not for reporting)
- only maintain SQL indexes when necessary

Watch the webcast a few times.
Try to get as much as possible out of this forum.

Savatage

Just as a FYI

It's not posted up yet - But I received my copy of MS Technet Magazine and for March 2007 it's covering "SQL Server Tune-Up"

A Real World Guide to:
-Index Health
-Server Clusters
-High Availability
-Table Partitions

http://www.microsoft.com/technet/technetmag/

Administrator

[Topic moved from Navision to SQL Performance forum]

Marije_Brummel

1. Yes, because this will also deletes empty sift buckets. (There are also tools who do this nightly).

2. It is not these commands that will give you a "general" performance boost. These commands can help you when a certain process is slow. I have seen them used wrongly and then create even more issues. If you are not sure what to use, then use the old FIND.

3. You can go ahead and disable all SQL indexes for keys that are not used, but you might spend time on issues that are non-issues.

Try to focus on hot areas. Find you what process is causing the most problems.

With performance there are always the vicktims that complain and the person causing the issues keeps his mouth shut.

Waldo

Mark Brummel wrote:

With performance there are always the vicktims that complain and the person causing the issues keeps his mouth shut.

This statement is more important than it looks.
People always tent to solve the victims problem, while you should be looking at the process that is causing the problem (not so easy) ... which could be in a whole different area, in a whole different departement/granule/ ... .

DenSter

It might not even be NAV code or SQL Server indexes, maybe it's the dude in the corner office way out in the warehouse that found the IT guys' wireless connection, and is too lazy to plug his laptop into the network. Now he's running sales reporting through the weak wireless connection and slowing everyone else down. Good luck finding those type of problems.