Nov
6
2012

XML Schema Collections: the XML accelerator pedal

While some of us may disagree, I think XML is a great method of moving small-to-medium sized data sets into SQL Server.

Because of XML's generality, it can be used by pretty much any client application and technology you can think of, and this can be a big advantage to getting the job done quickly and flexibly. I personally like it most for passing in sets of parameters (i.e., multiple ids) into stored procedures, and sending in full sets of data values where it's much easier to consolidate them on the SQL side rather than using complicated application logic.

The downside is that the XML has to be shredded (i.e. parsed) on the SQL Server side, which uses CPU. Out of all the tiers in our application stack, SQL Server is the most expensive to scale up, so it's important to be mindful when putting operations with large processing overhead into production. Ideally, we'd like to use XML with as little overhead as possible.

When we tell SQL Server to shred an XML document into a rowset, it can't make assumptions about what the incoming XML is going to look like. In fact, by default, SQL Server assumes there could be multiple root elements in the document (i.e., it's an XML fragment)!

By using XML Schema Collections, we tell SQL Server what to expect, and the optimizer is able to tailor the query plan to only include the necessary physical operations. A great side-effect of this is that the incoming XML is automatically validated against the schema, essentially making the input "strongly typed" like we would with regular variables.

Let's walk through an example to demonstrate just how much of a difference this can make. (Note: XML Schema Collections are available in 2005+, but this example code is written for 2008+.)

First, I'll create a table into which I'll dump the shredded data:

CREATE TABLE [dbo].[Table1]
(
	Id int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Value1 int NOT NULL,
	Value2 nvarchar(50) NOT NULL
);

 

Next, I'll create the XML Schema Collection for comparison testing. You can see all the XML Schema Collections in a database through Management Studio in the Programmability | Types | XML Schema Collections folder.

CREATE XML SCHEMA COLLECTION [dbo].[Table1Rows] AS
N'<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="Table1Rows">
		<xs:complexType>
			<xs:sequence>
				<xs:element maxOccurs="unbounded" name="Table1Row">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="Value1" type="xs:integer" minOccurs="1" maxOccurs="1" />
							<xs:element name="Value2" type="xs:string" minOccurs="1" maxOccurs="1" />
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>';

 

Finally, here is the meat of the test script, which builds up an XML document, and then shred/INSERTs it into the table. Comparison test by uncommenting the (Table1Rows) part of the XML variable declaration on line 15.

DECLARE @sourceString nvarchar(MAX) =
	CONVERT
	(
		nvarchar(MAX),
		(
			SELECT TOP 100
				CAST(v.number AS nvarchar(4)) AS Value1,
				REPLICATE('a', CAST((v.number / 50) AS int)) AS Value2
				FROM master..spt_values v
				WHERE v.type = 'P'
				FOR XML PATH('Table1Row'), ROOT('Table1Rows')
		)
	);

DECLARE @sourceXml xml/*(Table1Rows)*/ = @sourceString;

DECLARE @i int = 100;
DECLARE @startTime datetime2;
DECLARE @endTime datetime2;

SET NOCOUNT ON;

TRUNCATE TABLE [dbo].[Table1];

SET @startTime = SYSDATETIME();

WHILE @i >= 0
BEGIN

	INSERT INTO [dbo].[Table1](Value1, Value2)
		SELECT
			n.x.value(N'Value1[1]', 'int'),
			n.x.value(N'Value2[1]', 'nvarchar(50)')
			FROM @sourceXml.nodes(N'Table1Rows[1]/Table1Row') n(x);
			
	SET @i -= 1;
	
END

SET @endTime = SYSDATETIME();

SELECT DATEDIFF(MILLISECOND, @startTime, @endTime);

 

While the results on your machine will no doubt vary versus mine, my test on a 2008 R2 instance took about 3,100 milliseconds without using the XML Schema Collection, and about 280 ms with. This is a huge improvement! And we can see the difference in the query plans.

Before:

Query Plan Before

 

After:

Query Plan After

 

When I started using XML Schema Collections, I was only using them to strongly-type my XML, but it turns out that shredding XML can see a significant performance improvement as a side-effect. I read that strongly-typing XML slows down writes to XML variables, but I have yet to encounter a situation where that was necessary, or indeed, a good idea.

If you do start using XML Schema Collections in your own applications, I have a slight caution: you may want to avoid strongly typing parameters that are exposed in the database API (i.e., stored procedure parameters), even though it's technically correct to construct the parameters that way. The reason why is that XML Schema Collections, once created, cannot be altered in-place (there is an ALTER XML SCHEMA COLLECTION, but it doesn't do what you might expect). To change the definition, a collection must be dropped and recreated, which means that all objects with strong dependencies must be dropped and recreated as well. I think this is a royal pain -- feel free to vote up the Connect feature request to add the expected functionality.

A workaround is to weakly type the API parameters, but immediately cast the parameter to a strongly-typed local variable in the procedure body, like so:

CREATE PROCEDURE [dbo].[Proc1](@p1 xml)
AS
BEGIN

	DECLARE @realP1 xml(MySchemaCollection) = @p1;
	
	...

 

From an API point of view, that's not the best solution as the strong type isn't exposed to the outside, but IMO, it's a good enough tradeoff from a maintenance point of view, particularly if the Schema Collection is reused in many places.

Oct
31
2012

Managing the size of the transaction log

When we manage databases in either the FULL or BULK_LOGGED recovery models, we need to pay particular attention to the size of the transaction log files. If our processes aren't optimal, we can see log files grow either out of control, or beyond what we think is a reasonable size.

 

Virtual Log Files

As I mentioned in a previous post, the transaction log contains an ordered sequence of the physical operations that occur on a database.

What I didn't mention is that the physical transaction log file also contains logical sections, called virtual log files (VLFs). It's the VLFs which actually contain the physical operations I mentioned. The purpose of VLFs is to allow SQL Server to more efficiently manage the log records; specifically, to know which portions of the transaction log are used, and which aren't.

Knowing the portions of the log that are used is important when we go to take a transaction log backup, which creates a copy of all the transaction log records since the last transaction log backup. After a transaction log backup, as long as all the log records within a VLF are not part of an active transaction (or are required for some other feature, such as replication), the VLF can be marked as unused/inactive. This allows SQL Server to reuse that portion of the log file. This process is called many names, including clearing the log and log truncation. It does not affect the physical size of the log file.

Problems only start happening if all the VLFs end up being used, and none are available1. This means the log file (the physical file) has to grow to accommodate more transactions. A physical log file growth automatically creates new unused VLFs, and so the transactions can continue.

 

What is causing the log to grow after I shrank it?

Any write operation in the database can potentially cause the log file to grow. The real question is: why are all the VLFs used up when that's not what is expected?

Here are some of the problems you might encounter:

  • Not taking transaction log backups frequently enough, or not taking them at all. The only way to mark VLFs as inactive is to take a transaction log backup (again, only for FULL and BULK_LOGGED databases). If transaction log backups are only taken once/day, besides exposing yourself to a full day of data loss, you also need a transaction log file large enough to hold all the transactions that occur for a full day. That could be quite a bit of space! The solution is obvious, and it's a win for recoverability, too: take transaction log backups more frequently.
  • Overly aggressive and/or too frequent index maintenance. I'm certainly a proponent of index maintenance, but it's very easy to get carried away. Maintenance plans that rebuild shiny new indexes for you every night are both ridiculously easy to set up, and also ridiculously bad for the size of the transaction log. Typically, rebuilding all the indexes of a database takes more log space than the data files actually take up. If you're dealing with databases of even small-to-moderate sizes (say, 1 GB and larger), this can add up to a tremendous amount of wasted storage space, because all that transaction log has to be backed up at some point, and the log file will likely end up larger than the data files. What I strongly recommend doing is putting in place a much more selective index maintenance process, such as Ola Hallengren's index maintenance scripts, which I use in production.
  • The database is in FULL or BULK_LOGGED when it actually should be in SIMPLE. Before you go flipping the settings around (which can potentially cause data loss), go read about choosing the right recovery model for the database. Typically, I see this happen when a production backup is restored to a development environment, and a backup gets taken for some reason. And then the transaction log ends up growing, and growing, and growing as developers test and roll back their work, right up until things screech to a halt when the drive that contains the log file has filled up.
  • A SQL Server feature, such as replication or mirroring, is enabled. Until the transactions have been copied to the secondary server, the records cannot be cleared from the transaction log. If the secondary is slow applying changes, or if you don't even have a secondary any more and things weren't cleaned up correctly, this could lead to infinite transaction log growth. To solve this really depends on where the specific problem is -- solve that first, and then see if there is still an issue with the transaction log.

 

If none of those cover your scenario, you can run the following query, which will tell you why the transaction log is still in use.

SELECT
    name,
    log_reuse_wait_desc,
    recovery_model_desc
    FROM sys.databases

 

1 Extra empty log space is pre-allocated such that there is always enough space for active transactions to be able to roll back. Regardless, that space is "used" in the sense that it's been spoken for already.

Oct
25
2012

Consuming a secure web service in SSIS

If you need to use SSIS to consume methods of a web service that require a client certificate, the first thing you need to know is this: the Web Service Task will not get you there. (Regardless of its... other issues.)

The Properties GUI is misleading in that you can specify a certificate to test the connection and evaluate the methods, but that's as far as it goes -- the certificate information isn't passed along to the underlying HTTP Connection Manager at runtime, and you end up with "403 forbidden" errors for no apparent reason.

The HTTP Connection Manager does have a very tantalizing Certificate property... which can't be set using an Expression. (Or at least I haven't figured out how.)

We would have to resort to using a Script Task (or Script Component) to set the Certificate property, but going that route, it's actually easier to take a different approach entirely within the task.

First, though, let's take a step back, because we still need a way to get the certificate so it can be used with the secure service.

Below is a Script Task function that will return a reference to a certificate based on a certificate store location and a certificate serial number (run certmgr.msc to view the local certificate store).

Note that this is not a complete solution! You'll probably want to keep this code in its own script task which sets a package variable, so the certificate is available for all the web service calls you need to make. Also, it would be a good idea to externalize the input parameters so your package is configurable. I'm showing it this way here for simplicity.

 

using System.Security.Cryptography.X509Certificates;

private X509Certificate2 _GetClientCertificate(
	StoreLocation storeLocation, string serialNumber)
{
	X509Store store = new X509Store(storeLocation);
            
	store.Open(OpenFlags.ReadOnly);

	try
	{
		foreach (X509Certificate2 cert in store.Certificates)
		{
			if (cert.SerialNumber.CompareTo(serialNumber) == 0)
				return cert;
		}

		// No match
		throw new ArgumentException();
	}
	finally
	{
		store.Close();
	}
}

 

The next step is to configure a Script Task to actually call the web service. First, create the new task or component and go into the Visual Studio code editor. Right-click on the project file, and use Add Web Reference to generate proxy classes for your web service.

Now, here is where I've had a bit of frustration. Sometimes exiting out of Visual Studio at this point does not correctly save the project file, and you end up with the web reference files in the file system, but not actually in the project. There's no way to "add" them back to the project the way they were. Sadly, the easiest way I've found to clean it up... is to start again with a new Script Task. So what I've tried to do is use the Save All function to basically hope and pray that it sticks, then exit out, and go back in to make sure the folder still appears in the project. If it's still there, we're good to proceed.

At this point, try to build the project by using the Build | Build st_<guid> menu item. If you get an error "Task failed because "sgen.exe" was not found, or the correct Microsoft Windows SDK is not installed. ..." open the project properties, go into the Build tab, and change the Generate serialization assembly option to Off. The project should build successfully now.

So after all this leadup, here is the code to actually consume the web service in the Script Task (or Script Component):

using Services = st_<guid>.csproj.MyService;

Services.MyService svc = new Services.MyService();
svc.Url = "https://www.mydomain.com/MyService.svc";
svc.ClientCertificates.Add(
	_GetClientCertificate(StoreLocation.CurrentUser, "123456"));

svc.MyServiceMethod();

 

If you need to make many calls to the same web service, it's possible to add a reference to an external assembly in the Script Task project file, instead of generating the proxy classes directly inside the project. While the steps needed to do this are beyond the scope of this post, a common assembly is a great way to centralize the logic and service references in a larger project.

Oct
24
2012

Fix for Web Service Task "Cannot find definition for .... Service Description with namespace ... is missing. Parameter name: name"

The problem is that the SSIS Web Service Task does not handle namespaces other than the default.

While the web service WSDL file may validate just fine, SSIS still balks at it. Here's how to fix up the WSDL file.

Before:

<wsdl:definitions
	...
	targetNamespace="http://tempuri.org"
	xmlns:tns="http://tempuri.org"
	xmlns:ns1="http://www.MyServiceTargetNameSpace.com">
	...
	<wsdl:service name="MyServiceName">
		<wsdl:port name="MyPortName" binding="ns1:MyBindingName">
			...
		</wsdl:port>
	</wsdl:service>
</wsdl:definitions>

 

After:

<wsdl:definitions
	...
	targetNamespace="http://www.MyServiceTargetNameSpace.com"
	xmlns:tns="http://www.MyServiceTargetNameSpace.com">
	...
	<wsdl:service name="MyServiceName">
		<wsdl:port name="MyPortName" binding="tns:MyBindingName">
			...
		</wsdl:port>
	</wsdl:service>
</wsdl:definitions>

You could also just overwrite the targetNamespace and xmlns:tns attribute values with your service target namespace. Essentially, they all have to end up being the same namespace for it to work.

Oct
18
2012

Creating a Backup Maintenance Plan (video)

Maintenance plans are a great way of getting started with a backup solution for your environment.

While maintenance plans don't offer great flexibility, particularly when it comes to managing backups across many different servers, they're often a good starting point because they're easy to create and simple to understand.

In this demo video, I walk through creating a skeleton maintenance plan that contains 3 subplans to perform full, differential, and transaction log backups. The only thing left to do is schedule each of the automatically-generated SQL Agent jobs to meet your backup and recovery needs.