An often overlooked setting here is the growth of your Ax SQL database, a forgotten setup which may come back to haunt you later as a performance issue.
When creating a database in MS SQL server, you receive the default database settings. For autogrowth settings, these include an autogrowth setting of 1 Mb.
There are 2 major reasons on why you want to review this setting:
- Letting the database grow with small portions gives an almost constant extra overhead for your transactions, slowing you down.
- Physical fragmentation can have a severe effect on the overall performance of your database.
So what should you do then? What's the best practice here?
Don't rely on the autogrowth parameters. Size your database, based on the expectations. Like expected transactions for sales, ledger accounts, production etc. That may mean to set an initial size big enough to start working and an evaluation after 1 or 3 months. To be repeated each half year for instance. So the set file size would cover the requirements, without the need to grow automatically.
You may leave the autogrow on, no problem. But it should be considered merely part of a contingency plan for unexpected growth. Set an autogrowth rate of for example 200 or 500 Mb.
Be sure to check on your database size from time to time. Or even better, setup some proper monitoring right from the start. Being proactive is key here. It's always better to do your SQL maintenance (=resizing of the database) in non-peak hours.
For those not knowing where to look for or how change these settings:
From the Microsoft SQL Server Management Studio, select the database in question.
Right click the database and choose Properties. Under Files, you find the database file information, including the Autogrowth settings.
Note: You might wanna check the Auto Shrink feature as well, make sure it's switched off.