SQL Database Performance

mcurrie · 2007-04-17

We recently upgraded from Nav 2.6 to 4.01 and converted to SQL from a native Nav database. Over the past few months we have noticed some performance issues. We do not have any SQL DB admins onsite so I have been asked to do the investigation and make a recommendation on how to increase the DB performance.

Here is our current hardware/software configuration:

- We have a dedicated server for SQL running SQL Server 2000 on Windows 2003 Server SP1 Enterprise edition.
- 16 GB (mirror set housing the OS)
- 72 GB (5 disk RAID 5 - housing the SQL DB)
- 4GB RAM.
- Dual 3.0 Ghz processors

We have 45 users maximum on a 30GB DB. At this point in time we do not have any maintenance routines setup in SQL. We do daily backups and shrink the DB weekly.

After investigating this is what I plan to recommend.

- leave cpu's and RAM as is
- 16GB (mirror set for OS)
- Setup 2 disk RAID 1 for SQL log files (size to be determined)
- Setup 6 or 8 disk RAID 10 for SQL data files (size to be determined)
- disable any processes that are not needed
- setup weekly maintenance\reindexing routines

I'm a programmer and not real handy when it comes to hardware configuration so can anybody comment on whether this would be a good configuration or offer suggestions on a better course of action?

Thanks.

DenSter · 2007-04-17

Make sure you dedicate the log drives to the log. Don't get tempted to use available space for backup storage or anything like that. Use more smaller drives rather then fewer bigger drives. It's not capacity you are looking for, but read/write speed.

Consider using the SQL Perform tools to create the maintenance plans, and further analyze any remaining performance issues. I like those tools because they are SQL Server tools that you can use to fix issues inside NAV. It doesn't make any changes to the SQL Server database that have an impact on the NAV objects, so you can keep developing the database without having to redo any SQL Server routines.

nunomaia · 2007-04-17

A few more tips

Upgrade client from 4.0 SP1 to 4.0 SP3. You will have a performance increase in SQL Server.

I would put SQL data in RAID 1 instead of RAID 10.

Analyse Indexes if you need to rebuild indexes more than weekly.

DenSter · 2007-04-17

You'll want to mirror and stripe when you span the data across multiple drives.

mcurrie · 2007-04-17

nunomaia wrote:

A few more tips

Upgrade client from 4.0 SP1 to 4.0 SP3. You will have a performance increase in SQL Server.

I would put SQL data in RAID 1 instead of RAID 10.

Analyse Indexes if you need to rebuild indexes more than weekly.

Thanks for the tips everyone.

In addition to what I mentioned above, we are planning on upgrading to 5.0 once the tools become available. (end of April I believe)

I have a question about SQL data on RAID 1 as opposed to RAID 10. From what I have been told by our network admins as well as read on different forums is that read and write speeds to a RAID 10 is much quicker then RAID 1 due to the ability to stripe across multiple drives. Is this not true?

nunomaia · 2007-04-17

Forget what I have said about RAID 10 and 1 #-o

http://www.microsoft.com/technet/prodte ... op-10.mspx

I native I always got better performance with RAID 1 (don't ask me why)

Marije_Brummel · 2007-04-17

Please do not put all your hope of good performance in hardware. This only solves 10 or 20% of the issues.

80% is in application design and indexes. Seek profesional help to solve that first.

DenSter · 2007-04-17

That's right it doesn't, although getting rid of a RAID 5 configuration is a very good first step. Fixing your hardware will only be the first step, and the biggest gains are made in the application.

nunomaia · 2007-04-17

DenSter wrote:

That's right it doesn't, although getting rid of a RAID 5 configuration is a very good first step. Fixing your hardware will only be the first step, and the biggest gains are made in the application.

Application can be a bottleneck. Don't forget end users.

Training end users is also important. Some users make filters that can kill a db.

DenSter · 2007-04-17

nunomaia wrote:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

VERY nice link \:D/

David_Singleton · 2007-04-17

mcurrie wrote:

We recently upgraded from Nav 2.6 to 4.01 and converted to SQL from a native Nav database. Over the past few months we have noticed some performance issues. We do not have any SQL DB admins onsite so I have been asked to do the investigation and make a recommendation on how to increase the DB performance.

Here is our current hardware/software configuration:

- We have a dedicated server for SQL running SQL Server 2000 on Windows 2003 Server SP1 Enterprise edition.
- 16 GB (mirror set housing the OS)
- 72 GB (5 disk RAID 5 - housing the SQL DB)
- 4GB RAM.
- Dual 3.0 Ghz processors

We have 45 users maximum on a 30GB DB. At this point in time we do not have any maintenance routines setup in SQL. We do daily backups and shrink the DB weekly.

After investigating this is what I plan to recommend.

- leave cpu's and RAM as is
- 16GB (mirror set for OS)
- Setup 2 disk RAID 1 for SQL log files (size to be determined)
- Setup 6 or 8 disk RAID 10 for SQL data files (size to be determined)
- disable any processes that are not needed
- setup weekly maintenance\reindexing routines

I'm a programmer and not real handy when it comes to hardware configuration so can anybody comment on whether this would be a good configuration or offer suggestions on a better course of action?

Thanks.

More RAM, and 64 bit so you can use it all.

David_Singleton · 2007-04-17

nunomaia wrote:

...

I native I always got better performance with RAID 1 (don't ask me why)

For two reasons,
1/ Navision is good at managing its own striping. It "understands" Navision tables, and how to best stripe them.
2/ And most importantly Navision is able to dedicate a separate Commit cache for each individual spindle set, ONLY if it knows that they are separate, i.e. separate drives, thus you will have a separate slave.exe for each.

Putting all this on one drive (even if RAID 10) in Native will never be as good as individual drives.

davmac1 · 2007-04-17

You could also look at moving to SQL Server 2005 64 bit (and Windows Server 2003 64 bit) and bumping up the RAM. You may only need SQL Server 2005 standard edition.
SQL Server 2000 Enterprise does not fully utilize RAM above 2GB.

Navision allows you to specify which Navision indexes and SIFTs not to maintain in SQL Server - which can bump up performance.

You can also look at doing a technical upgrade to Nav 5 (replaces executables only) - and should work better with SQL Server 2005 64 bit.

Waldo · 2007-04-18

mcurrie wrote:

We recently upgraded from Nav 2.6 to 4.01 and converted to SQL from a native Nav database. Over the past few months we have noticed some performance issues. We do not have any SQL DB admins onsite so I have been asked to do the investigation and make a recommendation on how to increase the DB performance.

Here is our current hardware/software configuration:

- We have a dedicated server for SQL running SQL Server 2000 on Windows 2003 Server SP1 Enterprise edition.
- 16 GB (mirror set housing the OS)
- 72 GB (5 disk RAID 5 - housing the SQL DB)
- 4GB RAM.
- Dual 3.0 Ghz processors

We have 45 users maximum on a 30GB DB. At this point in time we do not have any maintenance routines setup in SQL. We do daily backups and shrink the DB weekly.

After investigating this is what I plan to recommend.

- leave cpu's and RAM as is
- 16GB (mirror set for OS)
- Setup 2 disk RAID 1 for SQL log files (size to be determined)
- Setup 6 or 8 disk RAID 10 for SQL data files (size to be determined)
- disable any processes that are not needed
- setup weekly maintenance\reindexing routines

I'm a programmer and not real handy when it comes to hardware configuration so can anybody comment on whether this would be a good configuration or offer suggestions on a better course of action?

Thanks.

Let me have a go ...

45 users, 30Gb DB: I would go for minimum 2CPU's (Dual Core) and 4Gb RAM. This is also what SQLPerform recommends.

It is indeed very important that the Transaction Log is on a dedicated RAID 1. Why? Because it writes everything directly to the log ... and then it's important the the "HD-needle" is always at the right place to start writing. Putting the DB files on a RAID10 is the best option, but only if you have a good RAID controller, or a very good SAN configuration ... don't use NAS!

You could go for a dedicated drive for tempdb, allthough this is peanuts, because NAV doesn't really use tempdb a lot.

What hasn't been discussed yet (I think ... only diagonally read the thread :oops: ) ... are the auto-option. Don't auto-maintain the statistics, don't auto-maintain the indexes. I would do this on a daily basis (if possible). Create a daily job to do this maintenance.

Also use a maximum degree of paralellism = 1. For NAV, this should be the best option.

Off course, if you have the option, go for the 64 bit version (OS and SQL)

.

Marije_Brummel · 2007-04-18

Waldo wrote:

What hasn't been discussed yet (I think ... only diagonally read the thread :oops: ) ... are the auto-option. Don't auto-maintain the statistics, don't auto-maintain the indexes. I would do this on a daily basis (if possible). Create a daily job to do this maintenance.

Also use a maximum degree of paralellism = 1. For NAV, this should be the best option.

Be careful with those settings if you do not have a proper maintenance plan. Otherwise you wil kill your server. Always make sure your statistics are up to date, if not via maintenance then use the autoopions since it is better than nothing at all.

DOP (Degree of Parallelism) needs to be swiched sometimes, because sometimes you do want it. Also when you run tools like Crystal reports you might need it.

A sugested maintenance plan can be that of SQL Perform.

Administrator · 2007-04-18

[Topic moved from Navision to SQL Performance forum]

mcurrie · 2007-04-18

I can't thank everyone enough. This is a huge help!

Sorcerer · 2007-04-23

are there any whitepapers or explanations from MS about setting DOP to 1 using Navision?

Marije_Brummel · 2007-04-24

It is on the SQL presentation from TechEd. Do you have that presentation?

kriki · 2007-04-24

Waldo wrote:

You could go for a dedicated drive for tempdb, allthough this is peanuts, because NAV doesn't really use tempdb a lot.

I read that the tempdb is used when rebuilding the indexes. So this process can be speed up by a tempdb on a dedicated drive

David_Singleton · 2007-04-24

kriki wrote:

Waldo wrote:

You could go for a dedicated drive for tempdb, allthough this is peanuts, because NAV doesn't really use tempdb a lot.

I read that the tempdb is used when rebuilding the indexes. So this process can be speed up by a tempdb on a dedicated drive

Sure, but you only do that at night when everyone is off the system and you are importing new objects, so it wouldn't really help the live system.

Considering that the biggest cost these days is rack space for drives, I would say the space and slots woul dbe better used for Database.

kriki · 2007-04-24

David Singleton wrote:

kriki wrote:

Waldo wrote:

You could go for a dedicated drive for tempdb, allthough this is peanuts, because NAV doesn't really use tempdb a lot.

I read that the tempdb is used when rebuilding the indexes. So this process can be speed up by a tempdb on a dedicated drive

Sure, but you only do that at night when everyone is off the system and you are importing new objects, so it wouldn't really help the live system.

Considering that the biggest cost these days is rack space for drives, I would say the space and slots woul dbe better used for Database.

Some companies work 24/24,7/7....

DenSter · 2007-04-24

A dedicated drive for TempDB for a NAV system should be at the bottom of the list, as NAV hardly uses TempDB (as Waldo suggests). If you're going to get dedicated drives, use them for the transaction logs. Remember that many small drives is better than fewer big drives. Don't worry about 'lost' disk capacity, it's not the capacity you should think about, it's performance.

David_Singleton · 2007-04-24

kriki wrote:

Some companies work 24/24,7/7....

Yes true, so I guess there will be special cases where it makes sense to have a separate TempDB, but I think that for the vast majority, that disk would be better spend on DB.

Waldo · 2007-04-24

Indeed, Kriki. I've done one implementation with a dedicated tempdb drive, which is a manufacturing environment.
A dedicated LOG is indeed much more important. I notice it again now, as I'm tuning a big database with Mark ... .

kriki · 2007-04-24

Waldo wrote:

Indeed, Kriki. I've done one implementation with a dedicated tempdb drive, which is a manufacturing environment.
A dedicated LOG is indeed much more important. I notice it again now, as I'm tuning a big database with Mark ... .

I know. With some things I read on Mibuso, the SQL help, some SQL-forums I know that in general it is not a performance issue (especially for Navision).
But it can become one if the DB has no window (or very limited) in which you can do an indexrebuild.
In this case it CAN be usefull to have it's own drive just for the indexrebuild.
In general on the same drive of the system is already good enough because the system-drive is not used a lot.

Sorcerer · 2007-04-24

Mark Brummel wrote:

It is on the SQL presentation from TechEd. Do you have that presentation?

No, could you please post a link?

Thanks.

DenSter · 2007-04-24

kriki wrote:

But it can become one if the DB has no window (or very limited) in which you can do an indexrebuild.

So by having TempDB on a separate drive, you can do a reindex during production hours without interfering much with performance?

kriki · 2007-04-26

DenSter wrote:

kriki wrote:

But it can become one if the DB has no window (or very limited) in which you can do an indexrebuild.

So by having TempDB on a separate drive, you can do a reindex during production hours without interfering much with performance?

A reindex always blocks the table, so having the tempdb on a separate drive is not a clearance to do it during production hours.
But the window you need to do it will probably be smaller.

Heltborg · 2007-06-12

Since Navision tend to use Cursors and Cursors use TempDB it is always a good idea to put it on a separat drive.

atm im having 6 trans/sec in tempdb vs 43 trans/sec in the Navidb on a dedicated Navi SQL server

But i do agreed with that it should be one of the less important areas. Keeping those data and indexes updated and sorted is main priority.
[/quote]

colin_leversuch-roberts · 2007-06-12

striped mirrors ( raid 10 ) will always leave any multiple of other raids standing ( assuming using a good hardware controller ). Note there are significant differences between sql2000 and sql2005 and there are significant differences using 64bit. You can totally offset the disk sub system with memory, so putting 32gb of ram on your server will effectively put your database in cache. Be very wary of turning off auto stats - uptodate stats are what the optimiser uses for it's queries - the system I'm looking at has these options turned off and I'm not convinced that this is a positive move. Note that sql2005 had rebuild stats asynchronously.
I'd also be very very careful of involking the maxdop 1 option - doing this stops your backups, index rebuilds and other maint options running in parallel ( talking ent edition here )
If you don't have a dba then leave sql options "out of the box" unless you really really know a good reason not too.
I'd advise checking out the SQL server whitepapers on best practices

http://technet.microsoft.com/en-us/sqls ... 31794.aspx

SQL Database Performance

Comments

Categories