Is your Microsoft Dynamics system fast enough?

If you run Microsoft Dynamics on premise then all but the smallest user counts will one day come across performance issues. As data volumes and user counts grow this becomes something we have to pay more attention to if the snappy instant speed users become used to initially is to be maintained. This is not restricted to Dynamics applications, but is true for any database system. The principles I outline for keeping the system optimal apply to any such application, especially if based on Microsoft SQL Server database platform.

So the Dynamics family are primarily database applications used to store lots of data about all aspects of your business. The primary constraint for speed is going to be how fast they can get the data you need now off the disk it stored on and how fast they can write new data to the correct place. The most important part of your system for this is your SQL server – so that's what we will concentrate on.

So by far the best option are the newish SSD (solid state disks) that are made up of electronic chips. The trouble is that these chips only support a number of writes to the same place before they burn out, and while this will be years on a normal laptop or PC, on a database server with lots of people feeding revised data all the time – it can happen very quickly. For this reason the SSD’s suitable for database servers are expensive basically because they have to have lots of redundancy, so when an area burns out it can move the information to that spare area.

The older type still use mechanical spinning disks to encode magnetic data. These have read/write heads that go across the disk to the correct track as the sector spins round. That means you have what we call latency, as the disk gets itself into the right position to read or write the data you need.

This latency means that performance is much better, having lots of smaller disks rather than a few large ones. The read/write heads cannot be in multiple places at the same time, so having two on two disks rather than one big one gives twice as much chance of it being feted to get the data you need. Similarly buying faster spinning disks that spin 15,000 times a minute means that data comes round under the heads quicker than the 7,200rpm slow ones.

What those disks are connected to is also critically important. There will be times when the communication channel to the disks will be saturated so the faster and wider it is the better. If the disks are the carparks then the disk subsystem are the roads from the car parks to the motorway. A road system that allows all the carparks to both park and leave without getting is each other's way and at motorway speed is what you need. That means either a fast SCSI or better still a saturate area network based system.

Best of all though is that the system doesn’t have to get it off the disks at all. This happens when your SQL Server has enough memory to be able to store the data there is what we call cache and so it doesn't have to get it for disk. No SQL Server can have too much memory, buy as much as you can afford, you'll not regret it.

The final bit of the SQL Server performance jigsaw are the processors. These however on a SQL server are not the priority. Don't get me wrong they need adequate power but it's not critical to have cutting edge. The money is better spent on memory and the disk subsystem.

I'm often asked we are about to buy this hardware, is it suitable? The trouble with that question is suitable for what? What you need now for the peak twenty minutes a week? What you might need in the future because it's got to last at least four year? Why take what will be a gamble?

Outsource the lot to the cloud and specifically Microsoft Azure. That way if performance is slow you can turn it up without having wasted money on hardware you don't need any more. If the business need change the platform can change. You only pay for what you need today. If your communications aren’t good enough and reliable enough don't you need to change that anyway?

Having a fast Dynamics system won't be much good if you phones and email are down for long will it? How much longer will your business prosper without that fibre connection? How attractive will your premises be if it's doesn't have fast reliable comes? Spend the money, dig up the road, put the fibre in and never regret it.

Author: James Crowter

I’m passionate about how businesses can improve their efficiency by getting process optimal more of the time. For the last twenty five years I’ve worked to help organisations of all sizes and types implement the ERP & CRM software that typically they decide they need when things are going wrong. I’ve seen that work unbelievably well and enabled those organisations to rapidly grow but I’ve also had some hard projects over that time where it’s felt more like warfare at times. Since 1996 (and version 1.01) I’ve been working with a small Danish product called Navision that’s now become Microsoft’s Dynamics NAV and I’ve also been using and consulting around Microsoft CRM since 2005. As managing Director of one of the longest established first Navision and now Microsoft Dynamics partners I’ve been involved in the complete history including numerous product councils and system design reviews. It’s my privilege to know many of the key Microsoft executives and product designers and have insight into both where the products are now and their future direction. So colleagues & clients have asked me to start this blog to share some of the insight that both this knowledge (obviously where not restricted by NDA’s or client confidentiality) and experience can help. Specifically I want to concentrate not on the specifics of how (there are some great blogs already for that) but why. If any user helps their business make better decisions or consultant can give better advice then that will be objective achieved. I founded Technology Management in 1992 and have led from the front ever since. Helping clients use technology to grow their business is my passion through explaining technology in terms that everyone can understand. My interest in computing began at the age of eight, long before my school had the equipment to cope. Throughout school and university I developed software commercially. I hold many IT certifications, such as Microsoft Dynamics NAV (for over 17 years), Microsoft Dynamics CRM (for over 10 years), as well as Microsoft Windows Server, Exchange and SQL. In October 2015, I was awarded the title of Most Valuable Professional (MVP), a title given to a select few individuals (31 currently) across the world specifically for Dynamics NAV. After years of working with a range of distribution and manufacturing software for hundreds of organisations, I focus on understanding the business requirements of an organisation, what it will take to deliver the systems required to maximise their potential. Follow me online via my other social channels: - Twitter: @jamescrowter - LinkedIn: linkedin.com/in/jamescrowter Or email me directly at james[.]crowter[@]tecman.co.uk.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *