Table Split - FileGroup

Angelo · 2008-01-26

Hi Master,

How to make table split and assign in filegroup? Please guide me step by step?

thx,
Angelo

Marije_Brummel · 2008-01-26

Please do not go that way.

Why are you thinking this is a solution?

What you will end up with is transactions waiting for filegroups to be ready. I've seen this many many times and always put it back. This is a very painfull process.

How big is your database? How many end users do you have? What kind of transactions do they make? What modules do you use?

Have you looked at transaction posting speed? How much overhead do unused indexes cause you? Have to removed them already?

Anyway. Good luck.

Angelo · 2008-01-28

Hi Mark,

I have read "tuning navision for better performance" and I have seen the webcast video from Hynek. I compare with my server system because the performance is very bad. All area run slowly especially in Warehouse activities and Sales activities. So, my first step is from HW configuration and the second step is SW(application) side.

My System :
Windows 2003 Enterprise Manager
SQL 2000 Standard Edition
Dual core 3.2Ghz(4 CPU)

HDD
SCSI, 15K RPM
RAID 0 ( 2 disk) for OS and SQL, Drive C only. Total 73GB
RAID 10 (3 disk) for LOG and Database (5 log file, 1 mdf file, 1 ndf file), Drive D only,Total 205GB

Memory 6GB
Citrix Metaframe
Navision 4.0 SP1

My Recommendation :
***CPU :
No Recommendataion

*** Memory :
using /3GB /PAE swicth and enable AWE

*** RAID I
C : for O/S, Swap File, SQL
D : for Temp DB
E - F : for LOG File

*** RAID 1+0
H-J : for mdf and ndf file

My Questions :
1. For Memory, how much "max server memory" that I have to use for enabling AWE?My memory only 6GB

2. For HDD,
How much GB that I have to assign to TempDB? put it in one disk with O/S and SQL?
How much GB that I have to assign to each LOG File?
Should I make each disk for each Drive?(I mean E-F drive use 3 disks) or
I just using 1 disk and make 3 partition (E-F).which one is better?
If using 3 disks, How much GB that I have to use for each disk? one log file for one disk?
How many ndf (filegroup) that I have to make?

I hope my questions is for knowledge to another people who have same problem with me. Please advise....[-o<

regards,
Angelo

kine · 2008-01-28

SQL 2000 Standard Edition

This is the main source of your problem. This version of SQL can use just 2GB of RAM - NO MORE! It means that /AWE and /3GB switches will not help you!

As first step you need to think about using another version of SQL. e.g. SQL 2005 Standard 64bit or higher. But it depends on count of users and size of DB, which you didn't mentioned.

Angelo · 2008-01-28

session until now is 80 sessions and Licensed session is 101.
Ok, let say I have change the SQL version, would you mind answering my questions? [-o<

rgds,
Angelo

kine · 2008-01-28

2-4GB RAM - /3GB in OS
>4GB RAM - /PAE /3GB in OS, /AWE in SQL
>16GB RAM - /PAE in OS, /AWE in SQL

TEMP db is better to keep on separate disk (mainly in SQL 2005)
Log file size depends on your backup policy
Do not use "logical disc". It is better to have one physical disc as a one logical disc.
NDF has nothing with filegroups. Use just the filegroups created by default, you can just add more files to cover all available discs (each DB file on separate physical disc/volume)

Angelo · 2008-01-28

I have enabled /PAE and /3GB then

when I enable AWE using this SP :

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'min server memory', 0
RECONFIGURE
sp_configure 'max server memory', 2147483647( I'm doubt of this value)
RECONFIGURE

How much 'max server memory' that I have to set? any suggestion?

rgds,
Angelo

Waldo · 2008-01-28

I always reserve a fixed number of GB for my server(s). So, minimum and maximum the same.

In your case (if it's a dedicated server), I would go for 5Gb. The other 1Gb is for OS and stuff.

YOu should monitor your server to see whether you can assign more or you should assign less... .

Marije_Brummel · 2008-01-28

Move to SQL2005 x64.

This is a difference compared to SQL2000 like day and night.

You might run into some issues where SQL2005 takes the wrong index but that is solvable.

I have done many customers on SQL, some with 100+ user up until 600.

16GB usually is enough to hold transaction data in memory.

Consider a DAS storrage with 16 disks like you can buy at HP's these days on the 3x and 5x series.

Waldo · 2008-01-28

Please elaborate more on the DAS storage...

.

Marije_Brummel · 2008-01-28

DAS means Direct attached storage.

Since SAS disks are much smaller, they can fit up to 16 disks in a server. This means you don't need an external storage anymore to get more reading speed on your system.

You can use 16 15k 73GB disks and configure it like this:

* 1x RAID 1 for OS + BACKUP (Make 2 partitions)
* 1x RAID 1 for Log
* 1x RAID 1 for TempDB
* 1x RAID 10 (10 Disks!) for Data

This is a resonably cheap solution compared to external storages.

16GB of RAM will cost you about 1600 euro's.

For failover you can use log shipping to the test machine. I recommend always using a separate test box.

I have customers with over 150 users on this config.

The 300+ user systems are generally using EMC/NetApp or EVA sans.

bbrown · 2008-01-28

Angelo wrote:

Hi Mark,

HDD
SCSI, 15K RPM
RAID 0 ( 2 disk) for OS and SQL, Drive C only. Total 73GB
RAID 10 (3 disk) for LOG and Database (5 log file, 1 mdf file, 1 ndf file), Drive D only,Total 205GB

RAID 0 on the OS (no protection for drive failures)?

A 3 disk RAID 10? How's that?

Angelo · 2008-01-29

bbrown,
Yes, that is not so good configuration. That's why I need suggestion from this forum to make it better.

Mark,
you said 10 disk for database file. Thats mean I have to make many filegroups(ndf file)? for example,
nav.ndf is assigned in disk1
nav.ndf1 assigned in disk 2
nav.ndf2 assigned in disk 3,so forth.

for log file is it OK if I put only in 1 disk ?

Pls advice....

Marije_Brummel · 2008-01-29

No, the 10 disks are in RAID 10 so it is one logical drive.

Just put the datafile on that RAID.

I am not sure if you can configure 10 disks on 1 controler. I have to dig into that.

Remember, it is not the drivespace you need, but the # of spindles.

Angelo · 2008-01-29

Mark,

ok,you said just only one logical data file.
I ever heard that making more that one ndf file will improve improvement?is it right?
we do that also with our log file.

rdgs,
Angelo

Marije_Brummel · 2008-01-29

I only have one customer with that and that is because their database is to large.

The wins in those things, if any, make your system so complex that it's not worth it.

There is so much more to win in the application.

bbrown · 2008-01-29

Angelo wrote:

Mark,

ok,you said just only one logical data file.
I ever heard that making more that one ndf file will improve improvement?is it right?
we do that also with our log file.

rdgs,
Angelo

Mutliple data files will only show an improvement if they are each on their own physical drive. You would probably find no advantage to having 5 - 10 GB files (each on it's own RAID 1) vs. 1 50 GB file on a single 10 disk RAID 10. In both cases the same number of spindles is servicing the data.

There are 2 areas where filesplitting may be considered. 1) When the disk requirements exceed the physical capacity of the hardware. In other words, you want to use more disk then 1 array will support. 2) The I/O demand is greater then 1 array can support. I would not consider either one of these to be my first performance tuning move when it comes to Navision.

bbrown · 2008-01-29

Question: (I haven't had my coffe yet)

Are we talking about multiple datafiles in a single filegroup, or each in its own filegroup? These are very different and have different applications.

kriki · 2008-02-13

[Topic moved from Navision forum to SQL Performance forum]

Table Split - FileGroup

Comments

Categories