Best practices: Autogrowth of the database, what not to forget

Some system administrators are happy already when Dynamics Ax is fully installed and up-and-running.  But there is more.  Ask yourself: Is everything set at its best?  Did you do everything to ensure stability in the future as well?
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.

Different ways of getting the current date: Which one should you pick?

There are a number of ways in Dynamics Ax to get the current date.
Maybe you've tried some of them, and most likely they all returned the same result.  Albeit that's actually depending on your installation and setup.

What's the difference?  Which method should you use when writing code to get the current date?
A short overview:

today

The today function uses the date of the machine.
This method is deprecated, for the simple reason that there's no support for different time zones.


systemDateGet

This function returns the session date.  If there is no specific session date set, it will return the system date.
Remember what the session date is?
It's a feature which allows you to change the date used within your specific Ax session, fe to back-date transactions.  Changing the posting date (all modules), changing the invoice date (and subsequent due date).  Note that for system fields of a table like CreatedDateTime and ModifiedDateTime, the computer date is used and not the Ax system date.

You can set the session date under Tools - Session:

DateTimeUtil::getSystemDateTime

This method gets you the session date as well, if not specified the system date.
The method returns a utcdatetime value.  You can use DateTimeUtil::Date to extract the date part.
Only DateTimeUtil::getSystemDateTime compensates for the time zone of the user.
Remember there can be a difference in date and time between server and client.
From application point of view, when establishing business logic, this method is the natural choice.

This last method is considered best practice in Ax.

In code, the different options:

static void WhatsTheDate(Args _args)
{  ;
   info(date2str(today(),123,2,2,2,2,4));
   info(date2str(systemdateget(),123,2,2,2,2,4));
   info(date2str(DateTimeUtil::date(DateTimeUtil::getSystemDateTime()),123,2,2,2,2,4));
   info(date2str(DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone())),123,2,2,2,2,4));
}

Conclusion: Use DateTimeUtil::getSystemDateTime, unless you have very good reasons not to.

In a following blog post, I'll dive deeper into the functions available with DateTimeUtil, with different timezone support and user-related timezone settings.