mibuso.com

Microsoft Business Solutions online community
It is currently Thu Dec 18, 2014 5:57 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 22 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Fri Jul 13, 2012 2:23 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
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).

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Fri Jul 13, 2012 2:36 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 3:49 pm
Posts: 4187
Location: Olst
Country: Netherlands (nl)
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.

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Read my new book: Microsoft Dynamics NAV 2013 Application Design

MY BLOG : http://markbrummel.wordpress.com


Top
 Profile E-mail WWW  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Fri Jul 13, 2012 3:12 pm 
Offline

Joined: Tue Jun 07, 2005 4:24 pm
Posts: 2883
Location: MA
Country: United States (us)
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?

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Sun Jul 15, 2012 10:56 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 11:00 am
Posts: 640
Location: Nürnberg
Country: Germany (de)
Hi Frank,

FDickschat wrote:
During these 4 weeks (MO-FR, 8am - 7pm) NAV read and wrote the following data:
Code: Select all
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:
Code: Select all
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" :wink: which I do not recommend ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Mon Jul 16, 2012 1:46 pm 
Offline

Joined: Mon Mar 12, 2007 2:07 pm
Posts: 368
Location: Hamburg
Country: Germany (de)
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.

_________________
Frank Dickschat
FD Consulting


Top
 Profile E-mail WWW  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Tue Jul 17, 2012 1:33 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 9:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
David Singleton wrote:
With Enterprise he can do online indexing. :wink:

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.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2015 - 19 & 20 November 2015 (!) ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: Optimal SQL Data files setup
PostPosted: Wed Aug 01, 2012 6:54 pm 
Offline

Joined: Wed Aug 01, 2012 3:26 am
Posts: 14
Country: United States (us)
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

_________________
MacUxPC
Dynamics-NAV.org


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page Previous  1, 2

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: