Adam Machanic : SQLCLR

SQLCLR Performance Session at TechEd US

Adam Machanic — Wed, 08 May 2013 16:14:00 GMT

I am super-excited to visit New Orleans next month for Microsoft TechEd; it will be my sixth time speaking at the show. My session takes an in-depth look at some of the techniques I've developed for using SQLCLR modules -- and some of the great performance...(read more)

CloudSeeder: CLR Stored Procedures For Creating CPU Pressure

Adam Machanic — Tue, 23 Oct 2012 19:49:00 GMT

Sometimes, in the interest of testing various scenarios that your server might encounter, it's useful to be able to quickly simulate some condition or another. I/O, memory, CPU pressure, and so on. This latter one is something I've been playing with a...(read more)

Performance, Discounts, and an Excuse to Visit New York City

Adam Machanic — Thu, 02 Jun 2011 14:21:00 GMT

A couple of weeks ago I announced a two-day advanced performance seminar in New York City, which will be delivered in July. This seminar will cover SQLCLR and parallelism techniques to help you take performance well beyond the levels that typical tuning...(read more)

Two Days of Advanced Performance Techniques - July 14-15, New York City

Adam Machanic — Wed, 18 May 2011 16:19:00 GMT

I am pleased to announce that I will be delivering two days of training in New York City, July 14 and 15. This seminar focuses on achieving "next-level" performance--going beyond that which you can gain via normal tuning methodologies . The vehicles for...(read more)

Webcast - September 28, 2010 - Next-Level SQLCLR: Parallel Processing and Bulk Load

Adam Machanic — Mon, 27 Sep 2010 18:24:00 GMT

Tomorrow at 12:00 p.m. EDT (4:00 p.m. GMT) I'll be doing a webcast for the PASS Application Development Virtual Chapter . Next-Level SQLCLR: Parallel Processing and Bulk Load The power of SQLCLR as a performance tool has been well-documented at this point;...(read more)

TechEd 2010 Thanks and Demos

Adam Machanic — Thu, 10 Jun 2010 17:00:00 GMT

Thank you to everyone who attended my three sessions at this year's TechEd show in New Orleans. I had a great time presenting and answering the really great questions posed by attendees. My sessions were: DAT317 T-SQL Power! The OVER Clause: Your Key...(read more)

Visual Studio 2010 and SQLCLR: Some Good, Some Bad

Adam Machanic — Sun, 02 May 2010 21:40:00 GMT

This past week I've been trying out Visual Studio 2010 for SQLCLR development. Verdict: A couple of nice things, a couple not so nice. In the interest of keeping things somewhat positive around here, we'll start with the good stuff : Pre-deployment and...(read more)

T-SQL Tuesday #001: Exploring "Fuzzy" Interval Islands Using SQLCLR

Adam Machanic — Tue, 08 Dec 2009 19:13:00 GMT

When working with time intervals, we often want to ask a couple of basic questions: Which time periods are not covered by our intervals? These are known as "gaps". What are the time ranges that we are fully covering? These are known as "islands". If you're...(read more)

Full Outline for my PASS Pre-Con: SQLCLR From Beginner to Expert

Adam Machanic — Mon, 17 Aug 2009 14:00:00 GMT

Since the announcement of my SQLCLR pre-conference seminar at this November's PASS conference I have received a few e-mails asking for more detail about what I will be covering. In addition to the Q&A I did with PASS , I thought it might be helpful...(read more)

SQLCLR String Splitting Part 2: Even Faster, Even More Scalable

Adam Machanic — Tue, 28 Apr 2009 18:54:00 GMT

Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method, I received a comment telling me about a big thread on SQLServerCentral dedicated to the question of how best to split strings. So I jumped in, and went back and forth, and back and forth, and back... and forth...

Many, many messages and several revisions and re-revisions later, I am happy to present the newer, better, more bug-free SQLCLR string splitting function. Not only is it faster than the previous one I posted, but it also handles multi-character delimiters, ignores consecutive delimiters as well as pre and post delimiters, and best of all, it won't get itself stuck in an infinite loop in certain cases (always a really great stability feature).

This version walks the SqlChars character array rather than using the IndexOf method on the string, which we found to be a somewhat faster technique--and it's certainly the most scalable and memory efficient method I can imagine.

Special thanks to SQLServerCentral member Florian Reischl, who was the main person keeping the thread going with me during the last couple of days, and who managed to re-write my versions and eke out even better performance by modifying the algorithms. Great stuff--it was definitely the most fun I've had on a technical forum in quite a long time.

But without further ado, the code:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
       )
    ]
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_Multi(object obj, out SqlString item)
    {
        item = new SqlString((string)obj);
    }

    public class SplitStringMulti : IEnumerator
    {
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                return new string(theString, lastPos, nextPos - lastPos);
            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i < stringLen; i++)
                {
                    bool matches = true;

                    //Optimize for single-character delimiters
                    if (isSingleCharDelim)
                    {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else
                    {
                        for (byte j = 0; j < delimiterLen; j++)
                        {
                            if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j]))
                            {
                                matches = false;
                                break;
                            }
                        }
                    }

                    if (matches)
                    {
                        nextPos = i;

                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

Enjoy!

Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Sun, 26 Apr 2009 23:59:00 GMT

It seems like every couple of months we see yet another post on SQLCLR string splitting routines. Many bloggers, I suppose, are still struggling, even three years later, to find that "perfect" use case for SQLCLR. Is string splitting it? Probably not. And with SQL Server 2008 table-valued parameters now available, SQLCLR string splitting has become an even less interesting exercise than it was before. None the less, a recent post on the Less Than Dot site has inspired me to counter with my own best SQLCLR string splitting method, for those of you who are still interested in solving this problem.

I've noticed that almost invariably, the methods posted online stress how very easy it is to do string splitting in .NET, thanks to the String.Split method. And while this easy method tends to work pretty well for small strings and on workloads that don't need to scale, it quickly breaks down when any amount of load is introduced (something that, unfortunately, most writers don't bother considering). The Less Than Dot writer, "onpnt" did do some testing, and discovered that--surprise, surprise--String.Split isn't all that great.

The issue? Well, it all comes down to large memory allocations and the art of scalable .NET programming--an area about which many SQL Server developers can (and do) remain blissfully naïve. In .NET, reduction of memory utilization--especially large allocation done en masse--is king, and String.Split does exactly the wrong thing. It takes the input string, breaks it into N chunks, and allocates all of the memory needed to store those chunks and pointers to those chunks, in one big huge operation. This can't possibly scale, and indeed it doesn't. I did a quick SQLQueryStress test of a TVF based on String.Split and saw fairly good performance when the input sentences were small (in the 40-400 byte range--see below), but after a certain point the AppDomains began recycling and performance became abysmal. Protections put in place for stability of the CLR host include memory leak detection, and this kicks in quite readily when we force allocation of so much memory in one shot--a good thing for the SQL Server instance as a whole, but not great when we're trying to really split a huge string.

Students of .NET who are concerned with scale (and really, everyone should be) are urged to look at the way problems are handled in LINQ. Here the vast majority of requests are internally handled using streaming iterator patterns, rather than moving around huge chunks of memory. This turns out to a much more scalable option for several reasons: Lower in-flight memory utilization, fewer large object heap allocations, and better access by the garbage collector to collect intermediate data that is no longer needed.

So how can we apply streaming to the string splitting problem? Rather than break the string up into all of its component parts upfront, we can walk the string step-by-step, only finding the next piece as required. In order to handle this, I created the following worker class:

    public class splitIt : IEnumerator
    {
        public splitIt(string theString, char delimiter)
        {
            this.theString = theString;
            this.delimiter = delimiter;
            this.lastPos = -1;
            this.nextPos = -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get { return theString.Substring(lastPos, nextPos - lastPos).Trim(); }
        }

        public bool MoveNext()
        {
            if (nextPos >= theString.Length)
                return false;
            else
            {
                lastPos = nextPos + 1;

                if (lastPos == theString.Length)
                    return false;

                nextPos = theString.IndexOf(delimiter, lastPos);
                if (nextPos == -1)
                    nextPos = theString.Length;

                return true;
            }
        }

        public void Reset()
        {
            this.lastPos = -1;
            this.nextPos = -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;
        private string theString;
        private char delimiter;
    }

This class is a simple enumerator implementation that looks for the next delimiter on each iteration, only when requested. Splitting strings in this way, rather than using String.Split, means that no huge upfront allocation takes place. Aside from the sentence itself, only one "chunk" is in play at any given time, and any chunks that have already been used can be garbage collected as needed when memory is tight.

Wiring this class up in a SQLCR TVF is just about as simple as when using String.Split:

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillIt", TableDefinition = "output nvarchar(4000)")]
    public static IEnumerator faster_split
        (
            SqlChars instr, 
            [SqlFacet(IsFixedLength=true, MaxSize=1)]
            SqlString delimiter
        )
    {
        return (
            (instr.IsNull || delimiter.IsNull) ? 
            new splitIt("", ',') : 
            new splitIt(instr.ToSqlString().Value, Convert.ToChar(delimiter.Value)));
    }

    public static void FillIt(object obj, out SqlString output)
    {
        output = (new SqlString((string)obj));
    }

I've enhanced this example slightly compared with most of the usual suspects: A SqlFacet attribute is used to make sure that the delimiter is only a single character, and I've added a bit of additional code in the main method to deal with NULL inputs.

The scalability difference between this method and the String.Split method is staggering in the simple tests I ran today on my SQL Server 2008 test server. With small sentences, even under moderate load (100 concurrent threads), each method performs more or less equivalently. But as sentence size increases to 50KB, the String.Split method begins slowing, taking almost 2 seconds per iteration, and the occasional AppDomain recycle is seen in the SQL Server log. The streaming method, on the other hand, continues to complete its job in just over 1/10th of a second, and causes no AppDomain recycles. Increasing to 500KB sentences, String.Split causes numerous AppDomain recycles and time per iteration increases to over 30 seconds, while the streaming method averages just 16 seconds per iteration. Jumping to 5MB sentences, String.Split causes almost continuous AppDomain recycles, and each iteration takes almost 6 minutes to complete. Yet with the streaming method, even with sentences of this size I am still unable to cause an AppDomain recycle to occur, and iterations complete in around 55 seconds.

The test I did was quite simple, and I won't post too many details here as I prefer that you test with your own workloads and draw your own conclusion about how this method fares when compared with T-SQL versions or the naïve String.Split method. I hope that if you do test you'll post back here with your results so that we can all learn the best way to handle these problems--whether or not string splitting really is all that interesting in the post-SQL Server 2005 world.

Speaking Season About to Begin: MCM, Roadshow, PASS, and SQLCLR Precon at DevTeach

Adam Machanic — Tue, 21 Oct 2008 20:20:00 GMT

Fall is now in full swing, and after a relatively quiet several months my speaking schedule is about to jump into fairly-packed mode. Following is a list of where I'll be headed over the next couple of months:

Next week I'm really excited to be teaching a day of the Microsoft Certified Master: Microsoft SQL server 2008 program's beta rotation. I'll be teaching developer topics including ADO.NET, XML, and SQLCLR. Should be a good time!

November 12, the SQL Server 2008 Roadshow hits Boston, and I'll be presenting a half day on all sorts of SQL Server 2008 topics. This one is free, so if you're in the Boston area you have nothing to lose by checking it out.

November 19-21 is the PASS Summit, and I'm excited to be involved in three presentations. I'm doing a spotlight presentation on programmatic/application concurrency topics, a duo with Peter DeBetta called "[Anti]patterns and [Mal]practices", and a duo with James Luetkehoelter on performance metrics.

December 1 and 2 I'll be in Montreal, speaking at the always-fun DevTeach/SQLTeach conference. Aside from speaking in the regular conference program, on December 1 I will be doing a full-day preconference on SQLCLR development. In this preconference I will start with SQLCLR beginner topics and take you all the way through to some really advanced use cases. So this one should be of interest even if you've been using SQLCLR for some time. Best practices will be stressed, and you might just learn about some of the dusty corners you've not yet had a chance to touch. I'm also upgrading the content to cover what changes SQL Server 2008 brings to the table, so this will be a complete session on the topic. I think that SQLCLR, while not used much in SQL Server 2005, will really pick up adoption in SQL Server 2008 and beyond. So now is the time to pick up what will be a key skill going forward.

[OT] SQLTeach: Almost Here

Adam Machanic — Fri, 25 Apr 2008 00:15:00 GMT

I was just reviewing my calendar for the next several weeks and noticed that the Toronto SQLTeach conference is now only a few weeks away. This conference includes quite a few interesting SQL Server-related sessions, on topics ranging from best practices, to performance, to some of the new SQL Server 2008 features. I fully expect this to be a great show.

I am doing two breakout sessions during the main conference:

SQL Server 2005: Authorization, Privilege, and Access Control. In this talk I cover SQL Server 2005’s enhancements around granting permissions via stored modules (i.e., stored procedures, views, functions)
Designing Highly Concurrent Database Applications. In this talk I get into the business requirements behind supporting concurrent processes, and the areas where SQL Server (and every other database product) falls short. I then go on to show how to solve the problems in the database programmatically.

I am also doing a full-day post-conference session on SQLCLR programming. This will be the first time that I will be presenting all of my SQLCLR material in a single day; should be fun. I will take attendees from the basics all the way through some advanced applications and techniques, so if you’re interested in becoming a SQLCLR expert I highly recommend attending.

The conference starts in just three weeks, but it is not too late to register.

Running sums yet again: SQLCLR saves the day!

Adam Machanic — Thu, 13 Jul 2006 01:52:00 GMT

Back again! Fourth post for the month of February, making this my best posting month in, well, months. Expect this trend to continue.

After yesterday's post on running sums and the evils of cursors, Jamie Thompson came up with a faster solution than the curser I posted. Alas, Jamie's solution uses an undocumented form of UPDATE syntax, and I am really not comfortable using it. So I set out to find still another solution. As promised at the end of my last post, SQLCLR is where I looked. And my instinct proved correct.

Jamie's solution runs in 4 seconds on my laptop. Compared to 14 seconds for the cursor I posted, that's a great enhancement. But I knew that we could do better still, and without undocumented syntax and temp tables.

The answer? A SQLCLR stored procedure. Same logic as the cursor: Pull back the data in order, then loop over the rows and maintain the running sum in a variable. But thanks to the SqlPipe's SendResults methods, we don't need a temporary table for this one -- the results can be sent back one row at a time, and will still show up on the client as a single result set.

Here's how I did it:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TransactionHistoryRunningSum()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true;"))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = @"" +
                "SELECT TransactionID, ActualCost " +
                "FROM Production.TransactionHistory " +
                "ORDER BY TransactionID";

            SqlMetaData[] columns = new SqlMetaData[3];
            columns[0] = new SqlMetaData("TransactionID", SqlDbType.Int);
            columns[1] = new SqlMetaData("ActualCost", SqlDbType.Money);
            columns[2] = new SqlMetaData("RunningTotal", SqlDbType.Money);

            decimal RunningSum = 0;

            SqlDataRecord record = new SqlDataRecord(columns);

            SqlContext.Pipe.SendResultsStart(record);

            conn.Open();

            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                decimal ActualCost = (decimal)reader[1];
                RunningSum += ActualCost;

                record.SetInt32(0, (int)reader[0]);
                record.SetDecimal(1, ActualCost);
                record.SetDecimal(2, RunningSum);

                SqlContext.Pipe.SendResultsRow(record);
            }

            SqlContext.Pipe.SendResultsEnd();
        }
    }
};

Results? 3 seconds on my laptop. 25% better than the previous best solution. Not bad!

And, I even did better than that. By creating a CLR table-valued user-defined function that uses a custom class wrapping a SqlDataReader, I was able to get this operation down to 2 seconds on my laptop. However, due to restrictions related to passing around context connections, that solution can not use a context connection and is therefore highly suboptimal. Until I figure out how to pass around a context connection (or if it's even possible), I'll keep that one off the blog.

So to recap: We certainly have not gotten rid of the cursor. This SQLCLR solution is really just a cursor in disguise. But we've built a better cursor, because this one doesn't require temporary tables. And that I can live with--for now.

Thanks to Jamie Thompson for prompting me to not wait several weeks before following up as I usually do with these posts!

Using static properties in SQLCLR UDTs

Adam Machanic — Thu, 13 Jul 2006 01:48:00 GMT

I spoke at the Beantown .NET user group meeting tonight, on the topic of SQLCLR in SQL Server 2005.

One of the questions that came up during the UDT part of the talk was whether static properties are supported. Unfortunately, I had no answer at the time--it's not something I'd yet thought to try.

The answer, as it turns out, is yes: they are supported. But they must be defined as readonly, e.g.:

public static readonly int foo;

As it turns out, this means that they can also only be initialized from a static constructor:

static myType()
{
foo = 1;
}

... which means that value will stick around from the time the type is first used, until the AppDomain is reset (for example, if SQL Server is restarted).

In my opinion, this greatly limits many use cases. One might, I suppose, have some expensive, yet rarely-modified data to initialize the member with, and get that data on the first pass only. However, if the data does chang, I'm not sure that it would be easy to reset the AppDomain. Do you really want to restart SQL Server in production environments to update static members?

Another use case I can think of is logging. Perhaps there are situations in which you'd want to log the first time a type is used. But that doesn't seem incredibly interesting.

If someone else reading has a more compelling use case, I'd be interested in hearing it!