Navision - DB files placement

David_Singleton

rixrix wrote:

1. We have about 250000 GL Entries per week (average of 3 years)
2. MS Experts and people from implementing company said the problem is there...

:shock:

Did I read correctly 250,000 per week? did you miss a zero? If not, there is no way you need this hardware for a system this small.

I was just working last week on a system where we tried to post a peak of 260,000 GL transactions PER HOUR! But we could only achieve 160,000. And we have nothing even close to the hardware you are planning here.

Sorry this thread goes on a bit and I have not read it entirely, but in general I think you need to spend more money on getting the system tuned than just throwing more hardware at it.

David_Singleton

rixrix wrote:

Besides that we tried to optimize indexes (also with mr.Stryk and MS help) , we optimized the code (i m sure here can be a lot of done yet), etc...

OK just read this bit.

Joerg is one of the very best Navision performance experts in the world. Engaging him was probably the best thing you did =D>

But if he is your consultant, why are you double guessing him and asking on a forum?

Not only that, but if he is working with you he will have a lot more information from analysis of your system than the dribs and drabs you are posting here for us to read. If you have engaged him, you should be working from his advice.

rixrix

With Mr. Stryk, it was not "real consultation" , he just tried to tune some indexes for us based on profiler info.
We were upgrading to v5 that time, so there wa not time to bigger consultation ... so he couldnt do his best .....
And we are very glad he did that for us, even it didn't solve the problems.

rixrix

Your post is missing so much useful information, you will get only very generic responses.
What version of SQL Server?
What version of NAV executables and objects
32 bit or 64 bit SQL Server
RAM on the SQL Server
All direct connect clients or are you using remote desktop
Locking problems.
Navision locks tables when you post to the G/L Entry table - how much posting is going on? Has it been optimized? Any customizations to streamline posting?

I already mentioned all ....


MSSQL 2005 Std
NAV V5.01 clients, DB technically upgraded to v5
64 bit SQL Server
32GB RAM
70 users on LAN, 100 remote users through Citrix ( 2 servers)

I am not able to say how much is the posting modified... I didn't programm it ...but we have quite big modifications in whole system.

The blockings shows up mostly at the end of month when everybody is posting invoices .... sometimes they have 100-3000 lines (we are invoicing also between our companies 1 invoice-whole month).

rixrix

I was just working last week on a system where we tried to post a peak of 260,000 GL transactions PER HOUR! But we could only achieve 160,000. And we have nothing even close to the hardware you are planning here.

Isn't it different when it posts 1 user compared if 100 users are posting at the same time?

We have alse implemented batch posting to post things at night... but it's not possible to use it for everything and everytime

davmac1

You could do batch posting or implement a posting queue that does all the posting through the job queue. That way only one process is locking the tables.
The other thing to look at is your Citrix boxes and make sure they have enough RAM to prevent paging. If a user is posting and then gets paged out of RAM, their locks stay in force until their process is finished.

rixrix

Hi,
So here it is, new server has been delivered
But now I am more confused then I was before.
I red a lot of things but ones were just negating other ones.

Is here someone with real experiences with NAV who can give me the answers?


Intro: We Have NAV 5.01 (tech upgraded from v4), 200 concurent users, 500GB (3 years) database, 4 companies,
lot of customization, MSSQL 2005 Standard
New server is HP 380G7, 2xXeon5660 (6core each) 2,8Ghz, 72GB RAM, Win2008Ent,
16 internal HDD on internal controller (1GB cache) + 2xD2700 (50 HDDs together) connected to p812 controller
(1GB cache)

The DB_DATA, DB_LOG, TEMP_DATA, TEMP_LOG, Import/export_DATA, BACKUP will be on it's separate Raid arrays
(mostly Raid10)

Questions:

1. How many files use for DB ? Som of people say 1 per core, some of them say 0,25/core maximally or just ONE totally .
Everyone of tham has some purposes why to do so ... And I just don't know ...

2. Same as Q1 but for TEMP DB

3. Filegroups
a) Does it worth to separate Non Clustered Indexes to another filegroup (which will reside on separate Raid array) ?
Some of people say yes, som of them say absolutely NO.

b) As Q3 + should I separate some tables with heavy i/o to another filegroup?

c) Or just make one big Raid 10 and put it there in just one filegroup (multiple files)?

4. Is there any simple benchmarkung utility which wil do some i/o tests and will tell me answers to my questions?

Thank you

David_Singleton

Hi rixrix,

the problem is that these are not "forum" questions. Forums are good for general knowledge and best practice type solutions, but you clearly have issues that need resolving, and the issues are specific to you system. You can't get specific answers without actually seeing your system, and analyzing it directly. Without this analysis, any answers you get will be just generic solutions. You will end out wasting a lot of time and money on experimenting rather than doing the ob properly.

Why don;t you spend some money to have an expert look directly at your system. In the long run it will save you a lot of money.

Anyway to answer some of your questions:

rixrix wrote:

1. How many files use for DB ? Som of people say 1 per core, some of them say 0,25/core maximally or just ONE totally .
Everyone of tham has some purposes why to do so ... And I just don't know ...

No idea without a review of the read/write statistics of the live database. Cores have nothing to do with it.

rixrix wrote:

2. Same as Q1 but for TEMP DB

What is the current performance? Why is the TempDB being used so heavily? Could this be the wrong EXE version of NAV.

rixrix wrote:

3. Filegroups
a) Does it worth to separate Non Clustered Indexes to another filegroup (which will reside on separate Raid array) ?
Some of people say yes, som of them say absolutely NO.

b) As Q3 + should I separate some tables with heavy i/o to another filegroup?

c) Or just make one big Raid 10 and put it there in just one filegroup (multiple files)?

No
No
Why?

rixrix wrote:

4. Is there any simple benchmarkung utility which wil do some i/o tests and will tell me answers to my questions?

Yes SQL Perform and Jorg both have tool kits that can do this. I use SQL Perform Analysis Tool Kit. I have not used Jorg's tool kit, but it looks to copy most of the SQL Perform functionality so it should do the same thing.

kriki

My 2 cents:

1) For most NAV DB's, it is best a big RAID10 on which there is the db-file.
2) for temp DB's: I read in a lot of blogs that most SQL DBA's go for 1 file for each 2 cores. And IMPORTANT: the files must have the same size.
3) C) : 1 big file with a big RAID10 is the basic and best 'standard' configuration. Before playing with filegroups, put tables/indexes on other filegroups, you really have to know what you are doing. And considering that a NAV DB of 1 TB is huge. It is quite small for SQL server.
4) for benchmarking, a lot SQL DBA's (non NAV) use SQLIOSim (http://support.microsoft.com/kb/231619/ ... &spid=2855). And for the rest; SQL Perform or Jorg Stryck.

rixrix

Thank you kriki, David

We definitely have Disk bottleneck here now (old story)
New disks will hopely solve this.
Maybe everything will work OK even with no special settings.
But I would like to setup all I can right now now.

I will do tunning, benchmarking, tests .... but I would like to start configuring it using some theoretical suggestions = best practices ... because it is much difficult to do changes when DB is depoloyed, raids are set, .....


Kriki, So u suggest just 1 file for producion DB ?
1 file per core was suggested on many blogs because of using paralellism (each core can work on each file simultaneously) .
Even when all files are on the same Raid ....


thank you

rixrix

Hello there,

just 1 question

Would u put TEMPDB on it's own Raid10 (6xHDD) or on
2 x Raid10 (each of 16 HDD's) together with production DB data files ?

Thank u

Marije_Brummel

The years that NAV and SQL needed massive I/O on the disks are IMHO in the past.

With current technology (NAV & SQL) most of those issues are solved, but the NAV channel and community does not seem to realise that and/or there are still quite a lot of old installations and people that write bad code. I'm affraid the latter will always remain.

TempDB should not be used in a good setup scenario and can be anywhere.

Most important nowadays are memory and network thoughput.

Invest your hardware money on getting on the latest binaries and software redesign.

David_Singleton

Mark Brummel wrote:

The years that NAV and SQL needed massive I/O on the disks are IMHO in the past.

With current technology (NAV & SQL) most of those issues are solved, but the NAV channel and community does not seem to realise that and/or there are still quite a lot of old installations and people that write bad code. I'm affraid the latter will always remain.

TempDB should not be used in a good setup scenario and can be anywhere.

Most important nowadays are memory and network thoughput.

Invest your hardware money on getting on the latest binaries and software redesign.

=D> =D> =D> =D> =D>