isolating tempdb vs. six-disk-setup raid10

Fragment

Hi Community,

in my company we use NAV V4 SP2 on a Windows 2003 Standard Server 32-bit with SQL Server 2005 SP2 Standard Edition 32-bit and the following hardware specs:

DISK| C:\ RAID1; 2x 73GB (SCSI, 10k) -> OS, pagefile, SQL Server Installation with masterdb, modeldb, msdb and tempdb (mdf & ldf)
\ RAID10; 4x 73GB (SCSI, 15k) -> productive databases ldf
E:\ RAID10; 4x 300GB (SCSI, 15k) -> productive databases mdf & ndf

CPU| 4x Dualcore Intel Xeon MP 3 GHz

RAM| 16GB

NIC| 2x Broadcom NetXtreme Gigabit Ethernet

We have a NAV database file size of ~90-100GB of which ~80-90GB are used. Also we have 2 further databases on the server which are relatively (...) small -> Perbit (human ressources database) and a small DWH where NAV financial data is transferred to and processed (scheduled at night mostly) by our IBM Cognos TM1 Server.

We have been experiencing different kinds of performance problems (long lasting locks for users, temporary lag/slow response when working with NAV) the last months and did already start to perform certain countermeasures that are:

- weekly "ALTER INDE REORGANIZE ... WITH LOB_COMPACTION
- weekly "DBCC CHECKDB ... REPAIR_REBUILD" & DATA PURITY in Single User Mode
- weekly shrink DB
- two times per week "EXEC sp_updatestats" & "EXEC sp_createstats 'indexonly'"
- upgrade of RAM from 8 GB to 16 GB (besides from the reservation of the SQL server before about 500MB were showed as completely free/available, now 2 GB are showed as completely free/available)
- disable "allow find as you type"
- IndexHint = No
- max degree of parallelism = 1

Furthermore we are planning to:

- reduce autogrowth fom "10%, unrestricted growth" to a fixed ~250MB value (gain?)
- change the Security Model to standard (gain?)
- install SP4 of SQL Server (gain?)
- perform a technical upgrade to V6 (clients only)


Finally the last thing we want to do is add two additional hard disks because we want to

1) isolate tempdb on a separate RAID (1 or 10, is 10 really neccessary for the tempdb outsourcing?)
2) separate the tempdb file to 1 file per physical/virtual (???) processor

At the moment we have 10 disks and we have only space for 2 more in the server which would allow a RAID 1 for the separated tempdb.

Now (besides the little hidden questions in the above text =] ) what I want to know is: Do you think it is better to use
two disks for the separate storing of tempdb

or

does it make more sense to upgrade the 4-disk 300 GB RAID10 with the ndfs and mdfs of the productive databases to a 6-disk RAID10 instead ... performance of ~240% vs RAID1 (=100%) ... performance of ~180% vs (4-disk RAID10)


I would gladly appreciate your thoughts/experiences on this view.


Thanks in advance!
Fragment


Edit: Also some further important information may be that we worked through the NAV Field Guide of Joerg Stryk
and most of the actions that have been taken result from his advice. One main performance indicator "Avg. Disk Queue Length" is still very high at some time. Should be around 0-10 ... grows up to 100...
The recommended action is to optimize C/AL code which we did not yet manage to do. But I hope that one of the two choices (tempdb on seperate physical RAID array / upgrade to six-disk-RAID10 for database file array) will at least provide a minor improvement, as the indicator is a hardware-reference-value.
Although I know that you can't fix bad code equally well by upgrading the hard disk setup rather than just fixing the bad code ;-)

kriki

The 2 other DB's. How big are they? How are they used? If they are used very little or only during the night for updating and the rest of the day for reading, I would put their transactionlogs on another disk to make sure the transactionlog of the NAV db is DEDICATED TO ONLY THAT!

Before putting the tempDB on a separate disk, check first if it is used a lot or not. If it is used a lot, you might put the tempDB and the .ldf of the other 2 DB on the new drive.

You also need to check fragmentation of the disks. And also internal fragmentation of the log file http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/

Another thing would be to upgrade all to 64bit. 64bit is a lot better to handle memory. With 32bit, it is best you keep "Always Rowlock" to FALSE.

Upgrade to NAV2009 R2 (preferably with SQL2008R2).

- weekly shrink DB
=> KILL!! KILL!! KILL!! KILL IT NOW!!!
This can create a lot of fragmentation on the disk and also internal fragmentation of the log file. Probably this is the biggest factor of your problems.
Keep your DB at a size that you keep at least 20% of free space. Don't count on autoincrement, but keep it as a failsafe. Incrementing size of the DB should be done manually.


- weekly "ALTER INDE REORGANIZE ... WITH LOB_COMPACTION
- two times per week "EXEC sp_updatestats" & "EXEC sp_createstats 'indexonly'"
=>No need for these. An IndexRebuild (statistics are created/updated automatically) is better. If you can do this each night, it is better. Otherwise do it once a week and for the other days, you do a reorganize+statistics create/update.

- disable "allow find as you type"
=> Very good!

- max degree of parallelism = 1
=> Very good!

- reduce autogrowth fom "10%, unrestricted growth" to a fixed ~250MB value (gain?)
=> I use 50MB for DB and my main TL is fixed size and big enough to contain the indexrebuild. Then I create an 'overflow'-log file of size 10MB. This has autogrowth 500MB. This trick makes sure that my main TL is fixed on disk and is never fragmented. The overflow-log I create to avoid that the system comes to a halt when the TL grows too much. When it is too big, I check why it happened. Fix the problem and shrink the overflow log again. This is one of the exceptions when shrinking is allowed.

- change the Security Model to standard (gain?)
=> no need for synchronizing anymore. And in case you never have done it, it means that all users are (at least) dbowner and can delete your DB if they want to.

- install SP4 of SQL Server (gain?)
=> In general, all SP's have some extra's for performance. So this is a good idea

- perform a technical upgrade to V6 (clients only)
=> NAV2009 R2. Each new version of NAV gains performance on SQL!

1) isolate tempdb on a separate RAID (1 or 10, is 10 really neccessary for the tempdb outsourcing?)
2) separate the tempdb file to 1 file per physical/virtual (???) processor
Before deciding on how to use extra disks, first you need to find out where they most serve. Which disks have most I/O problems? But before measuring this, fix other possible problems like fragmentation.

Edit: Also some further important information may be that we worked through the NAV Field Guide of Joerg Stryk
=> If after all the things I wrote, you still have problems, it might be a good idea to hire Joerg for a few days to do an in-depth check.

Fragment

Hello kriki,

thanks for the nice and informative welcome.
I will try to stick to your recommendations and keep you informed about the success.

Thanks again!
Fragment

David_Singleton

Fragment wrote:

Hi Community,

in my company we use NAV V4 SP2 on a Windows 2003 Standard Server 32-bit with SQL Server 2005 SP2 Standard Edition 32-bit and the following hardware specs:

DISK| C:\ RAID1; 2x 73GB (SCSI, 10k) -> OS, pagefile, SQL Server Installation with masterdb, modeldb, msdb and tempdb (mdf & ldf)
\ RAID10; 4x 73GB (SCSI, 15k) -> productive databases ldf
E:\ RAID10; 4x 300GB (SCSI, 15k) -> productive databases mdf & ndf

CPU| 4x Dualcore Intel Xeon MP 3 GHz

RAM| 16GB

NIC| 2x Broadcom NetXtreme Gigabit Ethernet

We have a NAV database file size of ~90-100GB of which ~80-90GB are used. Also we have 2 further databases on the server which are relatively (...) small -> Perbit (human ressources database) and a small DWH where NAV financial data is transferred to and processed (scheduled at night mostly) by our IBM Cognos TM1 Server.

We have been experiencing different kinds of performance problems (long lasting locks for users, temporary lag/slow response when working with NAV) the last months and did already start to perform certain countermeasures that are:

- weekly "ALTER INDE REORGANIZE ... WITH LOB_COMPACTION
- weekly "DBCC CHECKDB ... REPAIR_REBUILD" & DATA PURITY in Single User Mode
- weekly shrink DB
- two times per week "EXEC sp_updatestats" & "EXEC sp_createstats 'indexonly'"
- upgrade of RAM from 8 GB to 16 GB (besides from the reservation of the SQL server before about 500MB were showed as completely free/available, now 2 GB are showed as completely free/available)
- disable "allow find as you type"
- IndexHint = No
- max degree of parallelism = 1

Furthermore we are planning to:

- reduce autogrowth fom "10%, unrestricted growth" to a fixed ~250MB value (gain?)
- change the Security Model to standard (gain?)
- install SP4 of SQL Server (gain?)
- perform a technical upgrade to V6 (clients only)


Finally the last thing we want to do is add two additional hard disks because we want to

1) isolate tempdb on a separate RAID (1 or 10, is 10 really neccessary for the tempdb outsourcing?)
2) separate the tempdb file to 1 file per physical/virtual (???) processor

At the moment we have 10 disks and we have only space for 2 more in the server which would allow a RAID 1 for the separated tempdb.

Now (besides the little hidden questions in the above text =] ) what I want to know is: Do you think it is better to use
two disks for the separate storing of tempdb

or

does it make more sense to upgrade the 4-disk 300 GB RAID10 with the ndfs and mdfs of the productive databases to a 6-disk RAID10 instead ... performance of ~240% vs RAID1 (=100%) ... performance of ~180% vs (4-disk RAID10)


I would gladly appreciate your thoughts/experiences on this view.


Thanks in advance!
Fragment


Edit: Also some further important information may be that we worked through the NAV Field Guide of Joerg Stryk
and most of the actions that have been taken result from his advice. One main performance indicator "Avg. Disk Queue Length" is still very high at some time. Should be around 0-10 ... grows up to 100...
The recommended action is to optimize C/AL code which we did not yet manage to do. But I hope that one of the two choices (tempdb on seperate physical RAID array / upgrade to six-disk-RAID10 for database file array) will at least provide a minor improvement, as the indicator is a hardware-reference-value.
Although I know that you can't fix bad code equally well by upgrading the hard disk setup rather than just fixing the bad code ;-)

I think step one should be t increase the RAM. The 2-3 gig you currently have is not really enough.

David_Singleton

kriki wrote:

- perform a technical upgrade to V6 (clients only)
=> NAV2009 R2. Each new version of NAV gains performance on SQL!

That's simply not true. There are many upgrades that will significantly slow down the system.

Fragment

David Singleton wrote:

Fragment wrote:

Hi Community,

in my company we use NAV V4 SP2 on a Windows 2003 Standard Server 32-bit with SQL Server 2005 SP2 Standard Edition 32-bit and the following hardware specs:

...

RAM| 16GB

...and did already start to perform certain countermeasures that are:

...

- upgrade of RAM from 8 GB to 16 GB (besides from the reservation of the SQL server before about 500MB were showed as completely free/available, now 2 GB are showed as completely free/available)

...

...

Thanks in advance!
Fragment

...

I think step one should be t increase the RAM. The 2-3 gig you currently have is not really enough.

Hi David,

thanks for your answers.
We did an upgrade from 8GB to 16 GB two weeks ago and it did not lead to any mentionable performance gain (in regards to client locks), nevertheless maybe there is an overall performance gain respectively a more stable state of the server itself. But I don't think that RAM is the main issue here.
But if you have some more information or experience why more than 16 GB could be really needed then I am open to any further clarification of this approach!

Thanks again,
Fragment

davmac1

David - where do you see 2 to 3GB RAM? He has 16GB on his list.
The challenge is utilizing 16 GB RAM efficiently in a 32 bit environment.
There are various tools available to analyze performance - many included in SQL Server and NAV.

There are also external factors that can hamper performance like slow clients that cannot process a transaction quickly.

Fragment

Also my main question is still what do you guys really think is more important/better.
We have 10 disks in the server. We have space for 2 more disks. We don't want to change the disk subsystem.

Now when we receive 2 more disks (size doesn't matter ;-) 2x300 or 2x73, depending on the solution) shall we

1) Use those for the separation of the tempdb file from the other systemdbs

or

2) Upgrade the 4-disk RAID10 mdf/ndf-Partition of the main databases to a 6-disk RAID10

Does anybody know what is - in general - more preferable?

Thanks,
Fragment

stryk

Hi there,

well, isolating the "tempdb" should be a general thing, but I guess in your case this will not make much of a differene ...
So on which drives do you have the "pressure"? Is it the "tempdb" volume or the database volume?

Having just 4 HDD in a RAID10 volume for 90GB database is not that many ... e.g. the MS "Hardware Sizing Guide for NAV" is already recommending 12 to 14 HDD, so my vote would be to add those spindles to that mdf/ndf array ...

With NAV 4.0 it is crucial to optimize the SIFT structures; see http://dynamicsuser.net/blogs/stryk/archive/2010/05/29/optimizing-sift-and-vsift.aspx as the standard SIFT are causing a huge workload and are responsible for decreased read- and write-performance; implying also pressure on the disk-subsystem.
According to this, the SIFT tables need to be maintained frequently (caution: check for corrupt db version, see article).

And as David already pointed out: just an upgrade is not necessarily improving performance - there are many things to regards and tuning might be necessary. It also should be mentioned, that MS explicitly advises against a scenario "old objects with NAV 2009 clients" because some Automation Servers do not exist anymore and supposedly the behaviour of Form-Triggers was changed!
(Just to have that in mind. Actually you need to test this. I have customers running such an "unsupported scenario" without any problems.)

So if you are considering any upgrade scenarios, you should not just think about the NAV client site; I'd rather look into a new Server, too: 64bit environment, Win/SQL 2008 R2 (?), 32(+?)GB RAM, state-of-the-art CPU, FC/SAS disk-subsystem, etc. and maybe upgrade the NAV client to 5.0 SP1 U2+ or NAV 2009 SP1+ ... to be discussed in detail ...

David_Singleton

Fragment wrote:

David Singleton wrote:

Fragment wrote:

Hi Community,

in my company we use NAV V4 SP2 on a Windows 2003 Standard Server 32-bit with SQL Server 2005 SP2 Standard Edition 32-bit and the following hardware specs:

...

RAM| 16GB

...and did already start to perform certain countermeasures that are:

...

- upgrade of RAM from 8 GB to 16 GB (besides from the reservation of the SQL server before about 500MB were showed as completely free/available, now 2 GB are showed as completely free/available)

...

...

Thanks in advance!
Fragment

...

I think step one should be t increase the RAM. The 2-3 gig you currently have is not really enough.

Hi David,

thanks for your answers.
We did an upgrade from 8GB to 16 GB two weeks ago and it did not lead to any mentionable performance gain (in regards to client locks), nevertheless maybe there is an overall performance gain respectively a more stable state of the server itself. But I don't think that RAM is the main issue here.
But if you have some more information or experience why more than 16 GB could be really needed then I am open to any further clarification of this approach!

Thanks again,
Fragment

You need to move to 64 bit. Why are you running on 32 bit?