Think Spindles. Not Space!

  • Article

Here is one question my customers ask me every time I do a Dynamics architecture assessment:

"How much storage space do I allocate for the database files?"

Well my answer - "Think Spindles*. Not Space!"

Reason -"PERFORMANCE"

A Spindle here refers to one physical disk.

Spindle

With the new age disks available in storage sizes ranging from 73 GB to 500 GB and more, customers tend to think that they could live with just two or three disks for their ERP or CRM databases.

While that can take care of your database growth, it does not necessarily ensure performance all the time!

More the Spindles, better the Input/output (I/O) performance

With ERP/CRM Implementations, what is more important than storage space is I/O performance.    Chances are, owing to the multiple transactions being triggered against the database, you will run into disk contention issues wherein your pending operations get queued up.  So, even if a particular user transaction needs just 1 byte of data from the disk, it will have to wait in the queue for other operations to complete.

Therefore, it makes a lot of sense to have multiple spindles for a single data or log file with the SQL Database.  That way, you are increasing the I/O capacity at the Disk level.  Multiple spindles operating on a data file means you will have a smaller queue.  This ensures your transactions get the data they need faster with less wait times resulting in enhanced performance.  The same concept applies to write transactions as well.

How many Spindles or Disks should I allocate?

Well, determining exactly how many spindles you would need for your data and log files is an inexact science.   It highly depends on the transaction volumes expected.   There are various methods out there to arrive at this number.  Our Dynamics Product teams have released benchmark hardware test results from where we can take guidelines on sizing the number of spindles for typical deployments. 

Following are some findings from our product team testing:

CRM 4.0 Spindle Count

 Source: CRM 4.0 Suggested Hardware for Deployments up to 500 concurrent users

AX 4.0 Spindle Count

 Source: AX 4.0 Hardware Requirements for Deployments up to 250 concurrent users

NAV 5.0 Spindle Count

 Source: NAV 5.0 Hardware Requirements for Deployments up to 250 concurrent users

How do I know if there is Disk Contention?

There are a couple of PerfMon counters that you could monitor to identify disk contention issues and increase spindles as required.

  • PhysicalDisk: % Disk Time
  • PhysicalDisk: Avg. Disk Queue Length

"PhysicalDisk: % Disk Time" counter monitors the percentage of time that the disk is busy with read/write activity. If the "PhysicalDisk: % Disk Time" counter is high (more than 90 percent), check the "PhysicalDisk: Current Disk Queue Length" counter to see how many system requests are waiting for disk access. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles that make up the physical disk.

  • Less than 2 plus the number of spindles is an excellent value.
  • Less than double the number of spindles is a good value.
  • This requires further investigation of the disk transfer time in order to see whether disk queue length would actually impact the system.
  • Less than triple the number of spindles is a fair value.
  • Generally not an issue if seen for period of 5-10 seconds

The suggested resolution when you see this kind disk contention issues would be to:

  • Use a faster disk drive (Please note that the usual recommendations we make in our assessments is to use 10K or 15K RPM disks)
  • Add more spindles

With Dynamics deployments, Good I/O performance can be considered to be directly proportional to the number of spindles being dedicated for SQL Database and Log files.  So, "Think Spindles. Not Space!"