Specific Tables on Specific Drives

matttrax · 2010-01-12

Please bare with me as I am not a SQL or hardware expert by any stretch. Hoping to learn as we go through this, though. We are currently on SQL2000 and Navision 3.7 but will be moving to SQL2008 and NAV2009SP1 very shortly. We have 125+ users and a 100GB+ database. Currently the data all resides on a single disk.

We have identified our most heavily hit tables. They are just as you would expect: G/L Entry, Vendor Ledger, Customer Ledger, Value Entry, etc.

We would like to split these tables over multiple drives in order to increase performance. We will have a RAID10 with four disks dedicated to NAV data. From reading other posts it seems as though this can be very beneficial: more drives = more spindles = more concurrent reads/writes.

Our DBA is worried that because of NAV's unique internal ID number for tables, copying the tables using a SQL script will mess with the mappings between the NAV code and the tables. I've seen some other posts about partitioning data (specifically by year) across drives, and I assume it's the same for tables. And I know it has to be done from the SQL side.

Anything we should watch out for? Any posts you can point me to that I might have missed or not understood? NAV is pretty particular when it comes to doing things, especially outside of the client window.

Thanks for your advice.

DenSter · 2010-01-12

I would not put specific tables on specific disks, at least not as your first attempt at improving performance. The thing about a RAID array is that the hardware will take care of spreading files across disks, you don't have to think about that. You should get a huge performance boost by separating data from log files on their own dedicated disk arrays. You still have to make sure your hardware is good, RAM, CPU's, SQL Server settings, DOP, memory setup, things like that. Then you have to do index tuning, code review, and if all of that fails, maybe, just maybe then, you can start thinking about assigning tables to particular disks.

matttrax · 2010-01-12

Sorry, should have said this as well.

The log files are on their own separate drive. We have also done index tuning in the past. Granted this is on SQL2000.

New hardware is a 16 core active-active cluster with 32GB of memory. So the hardware should not be a problem.

Because of years of past slowness the company want to do everything in its power to make sure this thing flies. And will continue to fly without intervention for years and years to come.

DenSter · 2010-01-12

matttrax wrote:

16 core active-active cluster with 32GB of memory. So the hardware should not be a problem.

You realize though that those specs don't draw the whole performance picture.

matttrax · 2010-01-12

Absolutely. I know SQL has to be setup to properly utilize the hardware that it is on. That knowledge / skill set is just currently outside of my realm. Of course when it comes to SQL Server setup anything past setting the trace flag and importing the xp_ndo dlls is outside what I usually do.

stryk · 2010-01-12

Also have in mind, that currently - with NAV 3.70 - one of the most severe problems is the SIFT management ; or have you done intensive SIFT optimization e.g. "Bucket" reduction?
Well, with a NAV 2009 Client this would be replaced with VSIFT, thus getting rid of the SIFT issues (maybe replaced by - minor - VSIFT problems

)

IF you want to move tables to separate drives (and I agree absolutely with DenSter that this should be at the rear end of optimization) HEN you could proceed like this (SQL 2005 or higher):

1. Create new Filegroup for segregating Tables (e.g. Data Filegroup 2)
2. Create new file on separate drive; assigned to filegroup from step 1 (e.g. DFG1).
3. Drop all non-clustered indexes (NCI) of the to-be-moved tables
4. Drop the clustered index (CI) using the MOVE TO clause; moving the data to the new FG
5. Re-create all NCI in new FG

Have in mind that index changes in NAV might undo these changes, thus you'll have to re-run this fuss ... ](*,)

Good Luck!
Jörg

davmac1 · 2010-01-12

32 GB RAM does not sound very much.
Why not use Windows 64 Enterprise edition and have more RAM?
You SQL Server can still be the standard edition as long as it is 64 bit too (which SQL Server 2008 for standard should be) - it can use all the memory available to the Windows Server.

DenSter · 2010-01-12

matttrax wrote:

when it comes to SQL Server setup anything past setting the trace flag and importing the xp_ndo dlls is outside what I usually do.

This is the biggest reason why you should not even start down the road of partitioning your tables.

ara3n · 2010-01-12

He is not doing it.

He I believe the DBA is involved as well.

DenSter · 2010-01-13

Regardless, putting tables in specific files or filegroups is not a very good way to speed up NAV databases.

ara3n · 2010-01-13

DenSter wrote:

Regardless, putting tables in specific files or filegroups is not a very good way to speed up NAV databases.

I'm sure it's not the first thing you would do either.

I would move to 64 bit SQL/OS and double the RAM and implement 2009 SP1 instead of 2009. And do code/index improvements.

DenSter · 2010-01-13

64bit - yes, double the RAM - yes (although 32GB is not bad), but also, 4 spindles in RAID10 for a 125 user implementation is probably not nearly enough, I'd at least double, if not triple that (remember, you're looking for throughput not disc capacity, all that excess disc space is NOT a waste of money because you are using more discs for the throughput, NOT the capacity). Put the log on a dedicated RAID0 (dedicated for the log file for the NAV database only, do not share it with any other database's log files), put TempDB on a dedicated RAID0 and see where that leaves you.

Oh and whatever you do, do NOT use any of the data discs (data, log or tempdb) for file sharing. These spindles need to be dedicated for the NAV production database only.

kriki · 2010-01-13

DenSter wrote:

64bit - yes, double the RAM - yes (although 32GB is not bad), but also, 4 spindles in RAID10 for a 125 user implementation is probably not nearly enough, I'd at least double, if not triple that (remember, you're looking for throughput not disc capacity, all that excess disc space is NOT a waste of money because you are using more discs for the throughput, NOT the capacity). Put the log on a dedicated RAID0 (dedicated for the log file for the NAV database only, do not share it with any other database's log files), put TempDB on a dedicated RAID0 and see where that leaves you.

Oh and whatever you do, do NOT use any of the data discs (data, log or tempdb) for file sharing. These spindles need to be dedicated for the NAV production database only.

Small typo, Denster : log and temp on a RAID10 or RAID1 (I would put log on separate RAID10 and temp on separate RAID1) and not RAID0.

matttrax · 2010-01-13

Thanks everyone for your input.

Yes, I am definitely not doing this myself. We have an experienced DBA who does these things all the time, just not with NAV. I'm just getting information about whether or not it is a good idea and to find out information that should be discussed more before we do / do not make changes like this.

It will be running on SQL 2008 Enterprise, Windows Server 2008 64bit. It's 32GB because that's what they got, honestly. We sell refurbished computer hardware, so if it's not enough we just go grab some from the warehouse.

So to sum it up, I KNOW the speed will be faster, and I also know that every install can be different. But if you were the average customer / my company, do you think the move between the following would be enough for you?

We started with Navision 3.7, SQL 2000, 8 cores, 8GB of RAM, 1 data drive with additional drives for logs and backups, and some index tuning.

Moving to NAV 2009 SP1, SQL 2008 Enterprise 64 bit, 16 cores, 32 GB of RAM, 4 data drives with additional drives for logs and backups, and index tuning later.

Again, thank you everyone for your help. SQL Server is definitely an area I need to learn more about.

DenSter · 2010-01-13

kriki wrote:

Small typo, Denster : log and temp on a RAID10 or RAID1 (I would put log on separate RAID10 and temp on separate RAID1) and not RAID0.

You're right, a typo. Typically you don't need more than a 2 disc RAID1 array for the logs and TempDB.

DenSter · 2010-01-13

matttrax wrote:

Moving to NAV 2009 SP1, SQL 2008 Enterprise 64 bit, 16 cores, 32 GB of RAM, 4 data drives with additional drives for logs and backups, and index tuning later.

It's a start, but you are definately going to benefit a LOT from using way more spindles for the data.

ara3n · 2010-01-13

I would rather get SSD instead.

Marije_Brummel · 2010-01-13

Comment 1:

Just put the database on the RAID10 drive and don't try to move tables or indexes.

Comment 2:

About SSD: The last I heard it was either extremely expensive (RAMSAN) or unreliable for the number of random I/O packages. They would simply break to often.

matttrax · 2010-01-13

Thanks for your input everyone. I will definitely pass along the information to our DBA. I've probably learned more about SQL and hardware sizing in 15 or so comments than in the entire Microsoft guide.

Specific Tables on Specific Drives

Comments

Categories