Monday, January 23, 2012

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.

10 comments:

  1. I would recommend the local security policy "perform volume maintenance tasks" as well. This file initialization setting increases the performance of the datafile growth quite well.

    ReplyDelete
  2. Wonderful post! Youve made some very astute observations and I am thankful for the the effort you have put into your writing. Its clear that you know what you are talking about. I am looking forward to reading more of your sites content.
    Latest jobs

    ReplyDelete
  3. This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of trackbacks??
    Dynamics crm training

    ReplyDelete
  4. Hi there,I enjoy reading through your article post, I wanted to write a little comment

    to support you and wish you a good continuationAll the best for all your blogging

    efforts.Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    Awesome post
    Hadoop training

    ReplyDelete
  5. My uncle has needed to find a program for his new business. I am one of his helpers to find out what is out there. I'm liking some things said about this one though.
    http://www.ascentinnov.com/microsoft-dynamics-ax.html

    ReplyDelete
  6. FYI: In AX2012 and above there's a site/service called Lifecycle Services: https://lcs.dynamics.com.

    This includes a diagnostics feature which can analyse your configuration and report on issues; including warnings & recommendations when autogrowth is not configured per best practices.

    More info: https://technet.microsoft.com/en-us/library/dn268619.aspx

    ReplyDelete
  7. Hello! Thank you for your advice and recommendations, this article was very useful. I completely agree that this often forgotten feature must necessary be installed. I have already set the growth of my Ax SQL database, so I hope it will ensure stability in the future and eliminate the performance issue.

    ReplyDelete
  8. what a fantastic post! This is so chock full of users information and the resources you provided was helpful to me. There i was reading someones's blog where they discussed briefly about database autogrowth, setting autogrowth in new database and its benifits:
    http://www.sqlmvp.org/database-autogrowth-in-sql-server/

    ReplyDelete
  9. hello! thank you :)Awesome article to nice sharing. Training and certification on courses helps you to study this technology in future. I am looking forward to reading more of your sites content.

    ReplyDelete
  10. Very helpful suggestions that help in the optimizing topic,Thanks for your sharing.

    ดูหนังผี

    ReplyDelete