Sco's Blog

Performance Tuning SQL Queries against Xml Data Types

Mon, 10 Jun 2013 11:38:51 GMT

Had an interesting, yet all too common situation last week whilst looking to tune an integration solution built on BizTalk Server. The LOB system we were integrating with had used SQL Server as it’s backend as was performing poorly…real poorly. We were seeing up to 15 sec to grab a single customer from the database!

Upon looking at the DB schema I found all the tables defined something like this:

With just an internal ID as the PK and a single column to store segments of xml data. What made this design “interesting” was the lack of any indexing on the DB. Capturing the queries being issued from the LOB system we had something like:

SELECT * FROM [dbo].[Customers] WHERE XmlData.exist
(' declare namespace ns0="http://BizTalkService.Customer"; /ns0:Customer[CustomerID=''F001998A-E367-4B34-B630-3A70A91CA0BD'']
') = 1

These were taking anywhere from 1 sec up to 15 seconds. The execution plan was confirming what I’m sure you are already thinking: table scans! Simulating this query against our Customers table above with 100K rows gave us a baseline of around 5 sec to extract a single row.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time =
16 ms. (1 row(s) affected) Table 'Customers'.
Scan count 1, logical reads 33459,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0. SQL Server
Execution Times: CPU time = 4914 ms, elapsed time =
4981 ms.

Instead of xml indexing, which brings substantial storage and maintenance overhead, promoted columns were looked at. We were told by the LOB vendor that the client application optimised the queries if it found indexes defined on the table. Indexed columns would be used instead of xpath queries when they existed. But how do we get indexed columns from the table structure we had?

Here is the gist of the approach:

First, Identify the common xpath expressions being used which look appropriate to optimise. In our example this might be the CustomerID lookup. Then we create a UDF to grab this value from the xml segment stored in the row. A computed or promoted column is created using the PERSISTED keyword and an index created.

CREATE FUNCTION udf_Get_CustomerID(@xmldata
XML) RETURNS nvarchar(50) WITH SCHEMABINDING BEGIN RETURN @xmldata.value ( 'declare
namespace ns0="http://BizTalkService.Customer"; (/ns0:Customer/CustomerID)[1]', 'nvarchar(50)' ) END GO ALTER TABLE Customers ADD CustomerID AS dbo.udf_Get_CustomerID(XmlData) PERSISTED GO CREATE INDEX ix_CustomerID ON Customers(CustomerID); GO

Now we can modify our query, as the LOB client did, to use the new indexed column and dramatically increase he performance of the types of queries.

SELECT * FROM [dbo].[Customers] WHERE CustomerID='F001998A-E367-4B34-B630-3A70A91CA0BD';

Giving us stats of:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time =
4 ms.  (1 row(s) affected) Table 'Customers'.
Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0. SQL Server
Execution Times: CPU time = 15 ms, elapsed time =
1 ms.

So we went from 4981 ms to 1 ms!

Obviously we need to consider the overhead this creates for write operations as we should when considering any indexing design. Only optimise the most costly queries that get issued most frequently. Overkill can lead to poorer performance as more resources are required to maintain the indexes. During our subsequent testing we didn't measure any noticeable impact. Further testing will quantify this for us in the coming weeks.

Of course not all scenarios can rely on the client app changing its behaviour and issuing an optimised query once it see’s proper indexing. If this describes your situation you maybe interested in Selective Xml Indexes. In this article Seth Delconte writes about the new SQL Server 2012 SP1 feature that doesn't require the client to change. An option should the vendor migrate to SQL 2012 SP1 in the future:
https://www.simple-talk.com/sql/learn-sql-server/precision-indexing--basics-of-selective-xml-indexes-in-sql-server-2012/

Get PowerShell Commands

Thu, 28 Mar 2013 23:14:22 GMT

Just noticed the following option at the end of a configuration wizard:

How good is that!!! – clicking on this option generates me a ps1 script to perform the same configuration using the options I just selected in the wizard. Sure we have been able to write PS scripts for most install and configuration tasks but we always had to write these from scratch. I hope this pops up more and more in configuration tooling.
Golf clap to the product team

BizTalk Server Mapper Output Different Between Environments

Wed, 27 Mar 2013 12:33:22 GMT

Had one of those head banging issues the other day while working on a BizTalk Server 2010 integration solution. To set the scene, we had multiple development environments and we were seeing different behaviour at runtime depending on which environment built the assembly. In particular, the mapper XSLT was different and we saw different runtime behaviour depending on which environment deployed to the integration server.

The specific issue was the order of the imported namespaces and corresponding prefixes assign to them.

In simple scenarios this wouldn't cause much grief, these are generated during building the map and the tooling does the work for us. However it is a show stopper when you use Inline XSLT in your map and have to reference these namespace prefixes. We found that one of the development environments was outputting these in a different order. So for example, prefix ns3, referenced a different schema and the resulting xml output became invalid (…”it works on my machine” )

After much digging and mind numbing process of elimination, I found the problem to be the installation of .NET 4.5 on one of the environments

Removing .NET 4.5 on that environment fixed this and now we have consistent builds and unit tests once again.

Although the .NET frameworks are designed to be installed side-by-side, I guess something was changed that produced this behaviour. What that was I don't know and I didn't have the time to investigate/reproduce/etc.…. “Integration waits for no man” they say.

Microsoft Integration Alliance Announced

Tue, 11 Dec 2012 21:35:50 GMT

Breeze is proud to announce a brand new alliance with two integration partners, offering you a global team of over 100 passionate integration experts at your fingertips. "Impack" is the combination of three award winning, Microsoft certified integration partners. It is the collaboration between Breeze (Australia), Codit (Europe) and Matricis (North America) enabling an elite group of Microsoft qualified integration specialists to share knowledge, innovation and provide exceptional service for our customers.

Read all about it on the Impack website

Breeze Job Opportunities

Fri, 20 Jul 2012 02:11:33 GMT

Great opportunity for .Net Developers to join fast growing company

Breeze was recently awarded Microsoft World partner of the Year for 2012 and is looking for .Net developers x2 to work with the latest cloud technologies in Sydney. Please contact me or visit us at www.breeze.net

Windows Azure Service Bus and Certificate Revocation Checks

Thu, 28 Jun 2012 12:40:30 GMT

It wasn't so long ago that fellow Breezer, Micko, and I felt the pain trying to configure firewall rules for Service Bus relay endpoints in a locked down environment. Having jumped over those hurdles we quickly ran into another when the client API performed its online CRL checks. What hurt more was the fact that we could not turn these off

…that was soooo last year (well Nov 2011 to be precise).

As part of the Windows Azure Service Bus June 2012 release, one of the changes addresses the CRL check issue:

The .NET Framework enables you to configure the check for X509 certificate revocation. The default behavior for the Service Bus client library is to perform this check in the Online mode. Specifying an online check can result in a long delay while the certificate authority is contacted. To modify this behavior, you can add the following to the Web.config or App.config files for the project using the Service Bus in the appsettings section:

<add key="Microsoft.ServiceBus.X509RevocationMode" value="NoCheck" />

A very welcome addition.

Breeze Winner of Microsoft Worldwide Partner of the Year

Tue, 26 Jun 2012 06:07:44 GMT

Today, Breeze proudly announces the company has won the 2012 Worldwide Award for Microsoft Application Integration Partner of the Year and Finalist for Microsoft Cloud Partner of the Year Award.

Breeze was selected from a global field of top Microsoft partners, over 3000 entrants worldwide, for demonstrating excellence in innovation and implementation of customer solutions based on Microsoft technology. Breeze was recognised for providing outstanding solutions in Application Integration and Hybrid on-premise and public cloud solutions for Centrebet

Well done team!!!

Browsing Service Bus Resources with the Visual Studio Server Explorer

Tue, 26 Jun 2012 04:57:09 GMT

In the latest drop (v1.7) of the Windows Azure Tools for Microsoft Visual Studio a new Server Explorer plugin has been added to manage our Windows Azure Service Bus entities. Previously our options were to use the portal, namespace management API’s or 3rd party tools. One of the best tools is Service Bus Explorer that I use in various projects, webcasts and posts.

Now we have added functionality within Visual Studio Server Explorer to manage our Service Bus Queues, Topics and Subscriptions. A simple test harness is included as well.

This is a nice, but I’ll continue to use Paolo Salvatori’s excellent tool as it still has a richer feature set. Stay tuned to the Visual Studio Tool releases though, as new stuff is always being added.

Find out more here

ESB Add Namespace Pipeline Component and the no XML start tag open error

Tue, 19 Jun 2012 11:33:26 GMT

I had an interesting error crop up this week that cost me a few hours I wont get back.

The Scenario

A typical BizTalk Server integration project required the incoming message to have an xml namespace added to it before processing. No worries, lets leverage the ESB Toolkit and use the Microsoft.Practices.ESB.Namespace.PipelineComponents.AddNamespace pipeline component in a custom pipeline.

All was well with the solution and both unit tests and local integration tests worked a treat.

The Issue

When we ran end-to-end testing in the TEST environment we started getting the following exceptions being thrown by the ESB pipeline component:

Description: There was a failure executing the receive pipeline...blah blah blah...

Source: "ESB
Add Namespace"...blah blah blah...

Reason: There was no XML start tag open 

I noticed the source system was producing the incoming message slightly differently to the messages used in testing. Namely, the source system was appending additional CR+LF to the end of the xml message.

For example:

Mock Test Message	Mock Source System

So I'm thinking, this can’t be the issue. Surely the ESB Toolkit component is more resilient than this?…nope!

The Solution

The solution was to set the ExtractionNodeXPath setting to the root element as follows:

This gave me what I was after (same as before *but* without the xml parse error).

One other solution is to include a NamespacePrefix in the pipeline instance configuration:

Note: we must use a prefix outside of [ns0-ns6] as these are reserved by the component.

Both solutions will work in the case of trailing CR+LF and no trailing CR+LF in the incoming messages.

Show me the frameworks installed on this machine – quick’n’easy

Mon, 04 Jun 2012 12:48:06 GMT

Might be an oldie, but I only found this out now…

Open up IE and paste in the following address > javascript:alert(navigator.userAgent)

Sure, there is always a trip down to C:\Windows\Microsoft.NET\Framework to be certain, but I like this one too.

MSEVENTS LIVE Meeting: Managing Windows Azure Applications

Thu, 03 May 2012 11:19:20 GMT

Breeze and Microsoft are continuing the Windows Azure Webcast series. Two in the same week, yea ha!

LIVE Meeting: Managing Windows Azure Applications

Event ID: 1032500972

Language(s): English.

Product(s): Microsoft BizTalk Server and Windows Azure.

So you just made your first Windows Azure deployment. Now what? Is it healthy? How many instances do you need? What will my bill be? When do I need to scale up? Was that a DoS attack? Will auto-patching break me? Getting an application into Windows Azure is the first step, now you have to run the application for the next three years. Come to this session and see how to manage and operate your Windows Azure applications.

Register for Event

Starts: Friday, 4 May 2012 2:00 PM
Time zone: (GMT+10:00) Australia (East)
Duration: 2 hour(s)

Register

SQL Developer – 2-3 Week Contract – Start Yesterday!

Thu, 03 May 2012 02:05:38 GMT

Hey guys – Breeze needs a SQL 2005/2008/R2 developer for a 2-3 week short term contract.
Start immediately. Contact Breeze if you've got some bandwidth to take this on.