Optimal SQL Data files setup

KYDutchie · 2012-07-12

Hi All,

I am currently setting up a new SQL server for the customer and I want it to fly.
This is the configuration we are working with:
1. 64 Gigabyte of RAM
2. 2 * 8 core intel XEON.
3. OS is Server 2008R2 Enterprise 64 Bit,
4. SQL is SQL 2008R2 Enterprise 64 Bit,
5. Disk arrays are build like this:
a. OS and program files, RAID1
b. Data Raid 10, 8 disks,
c. Temp database, Raid 1
d. Transaction Log, Raid 1
And all are hardware raids, alligned, Direct attached (Optical) and internal
6. Dynamics NAV 2009SP1, 80 Gigabyte

This server is dedicated to SQL.

My customers inventory is totally lot controlled, so there is a lot of data in the Item Ledger Entry, Item Application Entry, Reservation entry and Value entry tables.

I have been researching my question online before coming here for advice, but have not been able to find a definite answer.
My question is should I split the SQL Data over multiple data files or use a single data file?
If the best performance can be achieved using multiple data files, how many should I use? Should the number of data files be related to the number of CPU cores, individual data file size or the number of physical hard drives?

Thanks in advance,

Willy

Marije_Brummel · 2012-07-12

Single data file.

KYDutchie · 2012-07-12

Thank you for the extremely quick answer.

davmac1 · 2012-07-12

One thing I read years ago was one tempdb file per core.
See this link for current discussion on this topic.

http://www.sqlskills.com/BLOGS/PAUL/pos ... -core.aspx

Sounds like you have a pretty good server.
How many users?
How many databases?
If you have more than one database, then don't share the same drives for the log file.

Marije_Brummel · 2012-07-12

A well tuned NAV database with enough RAM does not or hardly use TempDB.

Placing on a seperate lun is the best option. A file per CPU is IMHO over-design for NAV.

An "exuse" might be an extremely tight schedule for the reindex plan.

FDickschat · 2012-07-12

Mark Brummel wrote:

A well tuned NAV database with enough RAM does not or hardly use TempDB.

Well, this really depends on the amount of RAM in comparison to the DB size. With 64GB RAM for an 80GB DB this should be pretty fine. Still Temp DB could be used a lot depending on the indexes in NAV.

Mark Brummel wrote:

Placing on a seperate lun is the best option. A file per CPU is IMHO over-design for NAV.

Definitely on a separate LUN. I think a file per CPU is ok, a file per core is too much for NAV.

I had the opportunity to do some tests at a customer with a FusionIO Card. I did some measurements over 4 weeks: 2 with SAN and 2 with FusionIO. The DB is currently at 300GB, the server has 64GB of RAM.

During these 4 weeks (MO-FR, 8am - 7pm) NAV read and wrote the following data:

DB      Data read  Data written
NAV DB    488 GB      143 GB
Temp DB    27 GB       84 GB

In this example TempDB is heavily used, especially for writes.

DB Maintenance is a completely different story:

DB      Data read  Data written
NAV DB   1964 GB     1127 GB
Temp DB    <1 GB       15 GB

So especially reindexing does not use TempDB so much.

We could now probably start discussing about the state of proper tuning of that DB. The server is handling the workload pretty well so currently there is no need for additional tuning.

PS: Hamburg is cold and rainy. We are going to Spain next week

bbrown · 2012-07-13

The real advantages of multiple data files don't come unless you can place each on a separate array. Even then, the typical NAV database does not reach the size where features like this present a performance advantage that justifies the cost. I've done is a few time mainly due to physical system limits. From what I have seen, multiple data files isn't going to really help a NAV system.

KYDutchie · 2012-07-13

Thank you all for these answers.

I have read the same thing online about splitting the Temp database and even the datafiles.
I can understand that when you have multiple un-raided drives that splitting a database can improve performance, but I just wanted to be sure that it still would be helpful at all on a Raid 10. But having multiple un-raided drives does not make to much sense to me either, especially on a live database.

I think the customer will perform good and with 20 concurrent users, doing about 10-30 production orders and about 15 large sales orders a day we should be more than ok. Still I will have some performance tuning to do on older customized reports that were upgraded from previous versions but that is on a one to one basis.

Thanks again,

Willy

Marije_Brummel · 2012-07-13

Another option might be to switch to SSD. I know may people are reluctant to do this, but my experiences are great.

Using TempDB for reindexing is optional.

David_Singleton · 2012-07-13

KYDutchie wrote:

I think the customer will perform good and with 20 concurrent users, doing about 10-30 production orders and about 15 large sales orders a day we should be more than ok.

That volume of transactions and users you could manage with my laptop as a server

David_Singleton · 2012-07-13

Mark Brummel wrote:

An "exuse" might be an extremely tight schedule for the reindex plan.

KYDutchie wrote:

4. SQL is SQL 2008R2 Enterprise 64 Bit,

Marije_Brummel · 2012-07-13

David Singleton wrote:

Mark Brummel wrote:

An "exuse" might be an extremely tight schedule for the reindex plan.

KYDutchie wrote:

4. SQL is SQL 2008R2 Enterprise 64 Bit,

???

David_Singleton · 2012-07-13

With Enterprise he can do online indexing.

bbrown · 2012-07-13

Mark Brummel wrote:

Another option might be to switch to SSD. I know may people are reluctant to do this, but my experiences are great.

Using TempDB for reindexing is optional.

Could you expand a little on your SSD experience? Or maybe start a new thread (so we don't hijack this one). I've considered SSD at times but don't currentkly have any NAV systems usign them. I do have one client with their BI database on Fusion I/O.

Marije_Brummel · 2012-07-13

David Singleton wrote:

With Enterprise he can do online indexing.

I know, but I thought Online Reindex was not 100% completely the same as real reindexing. I might be wrong since it has been a while...

David_Singleton · 2012-07-13

The key to using SSDs is the controller. Once you have SSDs most of the drive configuration becomes irrelevant, and you move the bottle neck from the drives to the controller. If the controller is configured incorrectly then the SSDs can be slower than normal drives, so just make sure you have someone that understands SSDs controllers and Navision.

The real advantage of SSDs is not data through put, as for the same dollars you can get more through put on physical drives. The advantages are in the simplicity, where fragmentation, spindles RAID type etc become less important and thus the system is much easier to manage.

For a small implementations like this, you could use two SSDs in RAID 1 and put everything on one drive which will make it very easy to manage. (Obviously keep the OS on a separate physical drive).

Marije_Brummel · 2012-07-13

The thing with the amount of disks is that you need the place to put them. Most servers can handle 4 or 8 disks. With 16 you need extra shelves. This becomes expensive to.

So if you can have good performance with 2 SSD disks compared to 8 SAS disks you need less phisical space.

bbrown · 2012-07-13

Here's some of my random thoughts/questions on SSD:

1. SSD eliminates the physical limitations of regular drives which is the driving factor behind separating different file types such as data vs. logs. This can be an advantage in smaller systems where clients balk at dedicating a pair of 146 GB SAS drives for a log file that will never see 10 GB in its life.

2. SSD is best suited for the small random I/O and to a lesser extend sequential I/O. This would likely make them a great choice for data but not so much for logs. On a small system it probably makes sense to put it all on the SSD. But does a larger system justify the SSD for logs? Would you be better to just have the log on a regular RAID 1?

3. Have you seen any issues in terms of write life with SSD? The inforamtion I've seen recommends not usign SSD on systems where >20% of I/O are writes. Most NAV systems I've seen fall under that 20% threshhold so would be valid SSD candidates.

4. MLC vs SLC vs Enterprise Class SSD. Thoughts?

5. I might lean more toward RAID 5 instead of RAID 1 for SSD. Better usable space vs. cost. Thoughts?

stryk · 2012-07-15

Hi Frank,

FDickschat wrote:
During these 4 weeks (MO-FR, 8am - 7pm) NAV read and wrote the following data:
DB      Data read  Data written
NAV DB    488 GB      143 GB
Temp DB    27 GB       84 GB
In this example TempDB is heavily used, especially for writes.

Are you sure that I/O on "tempdb" was caused by NAV? I often see that some BI stuff is putting such heavy load on the "tempdb" as those application create temp-tables/worktables there ... ?!

FDickschat wrote:
DB Maintenance is a completely different story:
DB      Data read  Data written
NAV DB   1964 GB     1127 GB
Temp DB    <1 GB       15 GB
So especially reindexing does not use TempDB so much.

This changes if users flag the Index Rebuild with "sort in tempdb"

which I do not recommend ...

FDickschat · 2012-07-16

stryk wrote:

Are you sure that I/O on "tempdb" was caused by NAV? I often see that some BI stuff is putting such heavy load on the "tempdb" as those application create temp-tables/worktables there ... ?!

All tasks related to BI are scheduled to run in the night starting at 1:00am. Customer is running the PerformTools though. So some of the traffic on TempDB could be coming from the PerformTools.

kriki · 2012-07-17

David Singleton wrote:

With Enterprise he can do online indexing.

Online indexing is useful if you have a very small window for index rebuilding (you can use index defragging but it is not as good as an index rebuild, but it locks the table/index a lot less).
Online indexing is the same as offline indexing but locks the table/index a lot less (it locks at the start of a rebuild and at the end of the rebuild).

David Singleton wrote:

The key to using SSDs is the controller. Once you have SSDs most of the drive configuration becomes irrelevant, and you move the bottle neck from the drives to the controller.

And also memory. I read a blog lately (did save the link though...) that states that you must have 2GB of memory for each 80GB of SSD space. I didn't test it out myself but I did read some other blogs that stated you SSD's use more memory than normal disks. I don't know the technical reason for it (did find any info on this).

bbrown wrote:

3. Have you seen any issues in terms of write life with SSD? The inforamtion I've seen recommends not usign SSD on systems where >20% of I/O are writes. Most NAV systems I've seen fall under that 20% threshhold so would be valid SSD candidates.

The older SSD's had problems with it (Q:How do I know this if I have no experience with SSD's? A:I follow a lot of blogs about SQL and there is a lot of blogs about performance and SSD's). Current SSD's are a lot better.

macuxpc · 2012-08-01

KYDutchie wrote:

Hi All,

4. SQL is SQL 2008R2 Enterprise 64 Bit,

Being on Enterprise Edition, you can also use Index Compression, which helps a lot with overall I/O. It reduces I/O because data is transferred compressed, and more data can be cached in RAM as well.

You will see more CPU utilization, but a typical NAV SQL Server usually has plenty of CPU time available.

Implementing Index Compression requires good analysis though - you have to find the proper candidate indexes (read/write ratio).

A link to more info from Stryk: http://dynamicsuser.net/blogs/stryk/archive/2011/02/15/data-compression.aspx

Optimal SQL Data files setup

Answers

Categories