Posts Tagged ‘performance’

SQLskills’ Blogs

June 22, 2011 Leave a comment

Just added Kimberly and Paul’s blogs to my blogroll (see sidebar for links).

They provide fantastic tips in SQL Server Magazine.

For example in the May 2011 issue they touch on items like:

Follow them!



Performance of in-line SQL vs. stored procedures

May 27, 2011 Leave a comment

Just been doing some load tests with .NET code against SQL Server 2008 R2 Developer edition via Data Access Application Block in Enterprise Library.

I insert 5,000 records into a table with a primary key on an Identity column and a clustered index on the same.  I’m setting 3 nvarchar fields and one smallint one, and then returning the Identity value of the newly added record.  The in-line SQL method calls SELECT SCOPE_IDENTITY() as the second statement in the batch and utilizes the ExecuteScalar() method while the stored proc path merely returns the same value and is called via the ExecuteNonQuery method.  Using caching as detailed below, I almost achieve 1,000 records per second on my developer machine.

I’m afraid I can’t help the stored procedure vs. in-line SQL debate as sometimes the in-line SQL is quicker and other times the stored proc is quicker!

However to get the greatest throughput (on both in-line and stored procs) be sure to cache (or reuse) the following:

  • Database object (from DatabaseFactory.CreateDatabase) – although caching this didn’t make a huge difference
  • DbCommand (needed for next item!)
  • ALL DbParameters – merely overwrite the values on subsequent calls instead of calling Database.AddInParameter


BizTalk Server Tuning Tips

May 22, 2011 Leave a comment

Here are a number of things to pay attention to in order to get the most out of your BizTalk system (many of these are for SQL Server in general):

  • Place data and log files on separate disks
  • Create one tempdb data file per CPU (including cores); ensure each is exactly the same size; do not exceed 8 (see Optimizing tempdb Performance for more details)
  • Use dedicated SQL Servers
  • Be careful of constant database auto-growth – set to sensible values
  • Ensure all databases are getting backed up (which reduces transaction log size) – use the generated SQL Agent job to backup the BizTalk databases – it’s the only supported means of BizTalk database backups
  • Ensure all BizTalk Agent jobs are running successfully (there is one that runs in a loop and never terminates so be aware of that) (and another does backups so if doing your own, skip that one)
  • Microsoft does not want you changing any of their customized settings on the databases made during the BizTalk install – this includes any schema changes to the MsgBox database including indexes (See What you can and can’t do with the MsgBox database)
  • Separate the MsgBox and Tracking database and log files onto separate disks (so 4 disks – so I hope you’re reading this before you’ve committed to your kit!)
  • Change the DTA Purge and Archive job to just purge if you don’t need archiving (see documentation below)
  • Place sending, receiving, processing (i.e. orchestrations), and tracking into separate BizTalk hosts (so 4 are needed at a minimum) (and note again, a separate host for tracking)
  • If on a 32-bit system and getting out of memory (host restarted) error messages, try the /3GB OS startup switch – this gives each host 3GB instead of 2 (I’ve used it with great success)

Finally a couple of great resources to check out.

First I highly recommend the BizTalk Server Operations Guide (from which many of the above suggestions were derived).  It’s chock full of goodness.  There’s one for each version but the 2010 may be sufficient:

Also an absolutely fantastic tool to perform an automatic check of the health and configuration of your system and provide tuning tips is MsgBoxViewer.  I think it’s completely misnamed as it does a lot more than just look at the MsgBox database.