Severe SQL Server 2005 database performance issues

srfrsean

We are having severe performance issues on Nav 4.0 running against SQL Server 2005. The disk performance is clearly a problem and is being addressed with a hardware upgrade. After that is anyone having major problems with concurrent user activity when running against SQL Server?

DenSter

It is quite common to have performance issues with NAV on SQL Server. This is such a complicated area though that each implementation has its own set of unique problems.

In general:

• Make sure your hardware is up to standards. At the very least it is recommended to separate storage of the data files from the operating system spindles, with a dedicated physical drive for the transaction log. Do NOT use RAID 5, no matter what your hardware vendor tells you.
• Put in place maintenance plans for your SQL Server database. Unlike the native C/SIDE database server, which can be installed and forgotten, SQL Server needs to be maintained. There are many posts about this, so you might want to search the forum. You can purchase tools for this, and personally I have my preference about which tools to use
• Your tables will probably need to be tuned. The tools I just spoke about also has analysis capabilities to help quickly identify which tables to tune. You can also analyze the system manually, but that will take much more time.
• Code review. This you should not do until you have done all other steps, as it gives the least 'bang for the buck'

Of course these are general guidelines. Your situation might be completely different. If the code is bad enough, it will be a much bigger problem than usual. Hardware upgrades, as long as the initial hardware was set up properly, usually doesn't provide nearly as much improvement as expected. I have to say though that I have also seen hardware upgrades make a HUGE difference, even though at first I was not sure if that would make much of a difference.

You would probably benefit from having an experienced specialist involved to help you. Start with your partner, and if they don't have the right resources I'd recomend getting someone externally involved.

Alex_Chow

srfrsean wrote:

We are having severe performance issues on Nav 4.0 running against SQL Server 2005. The disk performance is clearly a problem and is being addressed with a hardware upgrade. After that is anyone having major problems with concurrent user activity when running against SQL Server?

SQL Performance problems are always touchy subjects and is unique to every installation because every business is ran differently.

There's a person out in Michigan who does it pretty well. His website is: www.risplus.com

srfrsean

Thanks, we're moving the locally stored datafiles onto SAN RAID 1 + 0 disk hopefully tonight. We have already spec'd out a new server runningi Windows 2003 Enterprise so we can go to 8GB or more of RAM.

When you say tune the tables are you referring to new indexes/redesign indexes or actual table changes?

I appreciate the help. I just took this application on recently and it needs some help fast.

srfrsean

DenSter wrote:

It is quite common to have performance issues with NAV on SQL Server. This is such a complicated area though that each implementation has its own set of unique problems.

In general:

• Make sure your hardware is up to standards. At the very least it is recommended to separate storage of the data files from the operating system spindles, with a dedicated physical drive for the transaction log. Do NOT use RAID 5, no matter what your hardware vendor tells you.
• Put in place maintenance plans for your SQL Server database. Unlike the native C/SIDE database server, which can be installed and forgotten, SQL Server needs to be maintained. There are many posts about this, so you might want to search the forum. You can purchase tools for this, and personally I have my preference about which tools to use
• Your tables will probably need to be tuned. The tools I just spoke about also has analysis capabilities to help quickly identify which tables to tune. You can also analyze the system manually, but that will take much more time.
• Code review. This you should not do until you have done all other steps, as it gives the least 'bang for the buck'

Of course these are general guidelines. Your situation might be completely different. If the code is bad enough, it will be a much bigger problem than usual. Hardware upgrades, as long as the initial hardware was set up properly, usually doesn't provide nearly as much improvement as expected. I have to say though that I have also seen hardware upgrades make a HUGE difference, even though at first I was not sure if that would make much of a difference.

You would probably benefit from having an experienced specialist involved to help you. Start with your partner, and if they don't have the right resources I'd recomend getting someone externally involved.

DenSter

Make sure that the TL is put on its own, separate, dedicated, physical disk array. Most commonly, NAV databases need a 2 spindle RAID 1 for the TL. The more spindles you use in a 1+0 for the data files, the better. It's throughput capacity you are looking for, not storage capacity.

Tuning tables in NAV means removing redundant indexes, and eliminating redundant SIFT levels. Having too many indexes and SIFT levels is by far the biggest cause of performance problems on NAV.

bbrown

DenSter wrote:

...a 2 spindle RAID 1...

Aren't all RAID 1 arrays 2 spindle?

Or do you mean span the log over 2 RAID 1 arrays?

bbrown

How large is the database and how many users?

Have you considered moving to 64 bit Windows Server and SQL Standard 64? If the only reason you are moving to Enterprise is larger memory support this may be a more economical move. Of course depending on other factors there may be reasons to use Enterprise. The need to support 8 GB is not one of them.

DenSter

bbrown wrote:

DenSter wrote:

...a 2 spindle RAID 1...

Aren't all RAID 1 arrays 2 spindle?

Or do you mean span the log over 2 RAID 1 arrays?

Yes all RAID 1 arrays are 2 spindles, but not all 2 spindle arrays are RAID 1 now are they

kriki

[Topic moved from Navision forum to SQL Performance forum]