BizTalk Server Performance Tips: #2 – Auto Growth

What’s so important about “Auto Growth”?

Firstly, let’s explore what it is and why it happens.

Auto-growth is an operation by which SQL Server expands the size of a database file (could be either the data file or the transaction log) when it runs out of space. How much depends on the settings that you have chosen; or if you’ve done no changes (which many don’t do), the default values of 1MB for data files and 10% for the log file. The growth settings available to choose for the files from are:-

  1. They can grow by a specific size
  2. Grow based on a percentage of the current size
  3. Not grow at all
  4. Unrestricted growth
  5. Set the growth of a database file to grow no larger than a specified size.

SQL Server, during the auto-growth operation, will block all current transactions until it completes the operation. If SQL Server is asking for a 1MB extension, that won’t take long. But imagine how long it will take if SQL Server was requesting for 2GB disk space?

On top of that, you need to understand that this disk space will not be contiguous (i.e., it won’t be physically right next to the existing database space), but instead will be somewhere else on the disk. You’re introducing disk fragmentation which will slow down SQL Server due to extra number of disk reads and writes that will now be necessary to support BizTalk. The more auto-growth operations you have, the more disk fragmentation!

That’s the theory, now let’s move onto what I reckon should be done and why.

1) Pre-size your BizTalk databases

You need to pre-size your databases so auto-growth is a contingency, not an expected behaviour.

Tim Form, a SQL Server MVP, believes that three years worth of transactions would be the basis of sizing your databases files. He even believes that auto growth is #10 of the Top 13 SQL Server Mistakes and Missteps

2) Pre-define the auto-growth setting for your data and transaction log files

Database auto-growth for both data and transaction log files should be set to 100MB instead of 1MB and 10% (respectively).

So, putting these together is very well summed up in the Microsoft BizTalk Performance Optimisation Guide. The guide states:

  • BizTalk DTADB (BizTalk Tracking database files): Data file having a file size of 2048 MB with 100 MB growth and a log file of 1024 MB with 100 MB growth.
  • BizTalkMgmtdb (BizTalk Management database files): Data file having a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
  • SSODB: Data file having a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
  • BizTalkMsgBoxDb (BizTalk MessageBox databases): 8 data files, each having a file size of 2 GB with 100 MB growth and a log file of 20 GB with 100 MB growth.

AutoGrowthSizeExample

Here’s a screenshot of the database properties of the BizTalkDTADb with the guidelines in place.

Until next tip… be good!

BizTalk Server Performance Tips: #1 – Enable Text in Row

Happy New Year everyone !

I’ve had for the last 2 years, and still having, a deep dive into Microsoft BizTalk Server especially around performance optimisation. So, I’d like to share some of my experiences and exposures.

My first tip – Enable Text in Row – comes from considering the message size that I was dealing with; the messages were predominately small in size where the most important and time-critical message request and response pair were 1Kb and 3Kb respectively and suit this tip.

By taking advantage of a SQL Server option called “text in row”, we can tell SQL Server that the contents of the fields of type text, ntext, or image data whose dimensions are smaller than those of a data page (8Kb) to store the contents in the data row rather than storing a “pointer” to where that data is actually stored (which means SQL Server needs to perform another read operation to retrieve the actual data!!).

The “text in row” option is detailed in TechNet at http://technet.microsoft.com/en-us/library/ms173530(v=sql.105).aspx. (Though I have to note that Microsoft have been saying that they’ll be deprecating this option since SQL 2005 …).

Here’s the SQL that I used for enabling this option, which sets the maximum size to be stored in the row to 7000 bytes:-

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’Spool’, ‘text in row’, ‘7000’

EXEC sp_tableoption N’Parts’, ‘text in row’, ‘7000’

Also, when your orchestrations have a small serialised/persistance footprint, the same tip can be applied to the host’s DynamicStateInfo table. (Of course, this assume you’ve created new and separated your hosts and host instances from those that come out-of-the-box… I think I’ll tackle that one as another tip 🙂 ) . Here’s the SQL that I used for enabling this; please replace OrchestrationHost with the real name for your host!

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’DynamicStateInfo_OrchestrationHost‘, ‘text in row’, ‘7000’

Furthermore, if you wish to ‘undo’ this tip, then the following SQL will be handy.

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’Spool’, ‘text in row’, ‘OFF’

EXEC sp_tableoption N’Parts’, ‘text in row’, ‘OFF’

EXEC sp_tableoption N’DynamicStateInfo_OrchestrationHost‘, ‘text in row’, ‘OFF’

Until next tip… be good!

Microsoft SecondShot offer

Microsoft are still offering for you to get a second shot at your Microsoft Certification for free. The Second Shot offer is available from September 1, 2013, to May 31, 2014.

Second Shot is available for the regular-priced individual technical exams with a prefix of 070 or 071, and for the Microsoft Technology Associate (MTA) certification exams administered through Prometric.

Go to http://www.microsoft.com/learning/secondshot for the details