Adam Machanic

The Hidden Costs of INSERT EXEC

Adam Machanic — Thu, 25 Jun 2009 20:19:00 GMT

INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post...(read more)

SQL PFE Team: New Must-Read Blog?

Adam Machanic — Thu, 18 Jun 2009 20:01:00 GMT

Several months ago I asked Joe Sack why his team, the SQL Server Premier Field Engineers, wasn't blogging and sharing the insights that result from their work with a number of high-end SQL Server customers. He replied that he thought it was a great idea,...(read more)

Demos From Cape Cod .NET: T-SQL Power! Presentation

Adam Machanic — Thu, 18 Jun 2009 16:05:00 GMT

On Tuesday night I was honored to present a new talk to the Cape Cod .NET Users Group that meets in Plymouth, MA. The talk is called "T-SQL Power! Learning to Harness the Under-Used OVER Clause". Following is the abstract for the talk: First introduced...(read more)

Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Sun, 31 May 2009 20:07:00 GMT

After weeks of putting it off, I finally found the time and spent the last day and a half judging the Grouped String Concatenation Challenge. I would like to congratulate the winner, Peter Larsson, who submitted a great query and walks away with a shiny new MSDN Premium subscription.

For those who are interested, following is a breakdown of the judging process, along with some commentary:

Submission Process

To begin with, e-mails. As I mentioned in the first post, I ignored all e-mails that didn't follow the directions. Luckily this was only a few submissions. I felt it rather odd that people would spend a not insignificant amount of time working up a solution only to not bother to read the guidelines thoroughly. But that's human nature, I suppose.

Round 1

Once I collected all of the queries that followed the e-mail rules (all of which are included in the attached ZIP file), I began testing against an expanded version of AdventureWorks (the script for that is also included). I decided to eliminate any queries that did not produce the correct output data based on my sample set, or which took longer than 30 seconds to complete. The majority of queries did complete in a reasonable amount of time, and many were eliminated because the output simply wasn't correct. The biggest issue was ordering of the elements in the comma-delimited sets. I also deducted points from one person's entry because of invalid column names, but I decided to let the entry ride to the next round.

An important side note is that I created this competition with the sole intention of discovering new and different ways to do grouped string concatenation, and my hope was that someone would come up with a clever, fast solution. Unfortunately, that didn't happen, and every submission that used any technique except FOR XML PATH was eliminated in the first round of testing. I received some extremely creative solutions from a couple of people and I would like to mention them here:

Alejandro Mesa's submissions made use of various XQuery techniques, and are very interesting to look at, although fairly slow
Dean Cochrane's submission used an interesting idea of doing a MAX(CASE ...) pivot for the lists. Alas, the product names lists were not correct, so the submission didn't make it to the stress testing phase
Scott Coleman tried a similar technique, actually using the PIVOT keyword. Unfortunately, this ran for over 200 seconds, so it was eliminated

Also interesting to note is that a few people tried recursive CTE solutions. These were all cancelled at the 300 second mark. Recursive CTEs, as mentioned before on here on SQLblog, simply do not scale in their current implementation.

Round 2

After tabulating the Round 1 results I was left with 18 queries, and some obvious contenders. I ran each query through a SQLQueryStress session with 10 threads running 5 iterations each. In this phase the queries were separated into fairly distinct groups: Those that ran for around 5 minutes, those that ran for around 7-8 minutes, and those that ran longer. These groups were based, not surprisingly, on how much attention was paid by the query writers to the little details. For example, Peter Larsson's winning query cut down on logical reads dramatically by doing some of the grouping in a derived table, rather than in the outermost query as some of the other submissions did.

Lesson learned: When doing aggregations, especially when joining a lot of tables, think about what you're really aggregating, and do the aggregation as early as possible. For example, if you need to aggregate sales per customer and get customer names, do the aggregation of the sales numbers first, then join out to get the customer names. Otherwise the query processor is forced to do more work than it has to do, and your query won't be as fast. Peter and a few other contestants understood this distinction and wrote queries that were much faster as a result.

Round 3

Round 2 eliminated 4 queries, leaving me with 14 to judge based on query style. In order to judge consistently, I came up with 10 factors. A query was allotted 500 points to start, and failure to meet each factor resulted in a 50 point penalty. These factors were:

Consistent Indentation

Does the query use the same rules for indentation in all parts? This is huge for readability and helps people understand where each section of the query starts and ends.

Consistent Capitalization

Does the query use the same rules for capitalization throughout? For example, keywords should be either all capitalized, or all lowercase.

Capitalize Keywords

I like to see keywords capitalized.

Use AS for Alias Names

AS is optional, and I've left it out in many queries I've written. But the more of other peoples' code I read, the more I realize that it really does help on the readability front. Use it. Always.

Follow Capitalization of Base Tables/Columns

If the base table is called OrderHeader, I want to see it used as OrderHeader when referenced in your query, rather than orderheader. A trainer I know found this out the hard way, when he reinstalled SQL Server on his laptop shortly before a training session, and used a case-sensitive collation rather than his previously-installed case-insensitive collation. He had been careless in adhering to capitalization for his training materials, and discovered the issue in front of the class. Oops.

Long Horizontal Lists

I don't like horizontal scrolling, and I find long lists difficult to read.

Consistent Vertical Lists

Put either a comma after each element or before each element, not both. Indent your lists the same way throughout. If you indent some items below the SELECT, don't put other items on the same line as the SELECT (or GROUP BY, or ORDER BY, etc)

Alignment of Delimiters

I follow a .NET-inspired style where I put delimiters on their own lines, and line them up vertically. This gives my code what I feel is an airy, easy-to-read feel. When reading others' code I look for some kind of alignment. Failure to align delimiters makes it very difficult to understand, again, where one section begins and another ends. By the way, common delimiters for this challenge included both parens and CASE...END.

Comments

Does the query have comments? Are the comments useful in understanding the logic?

Aesthetics

This is perhaps the most subjective. My general feeling on how I enjoyed reading the code.

All in all, the queries were pretty good. I would like to call out Rick Halliday, who had the highest score in this round with some very well formatted and highly readable code.

Round 4

After judging Round 3 I tallied all of the scores and was left with a tie for top 3:

Rick Halliday
Leonid Koyfman
Peter Larsson's query #4

All three of these queries were well thought out, but only one could win, so I took another pass through each. Rick's query, though extremely well written and readable, was eliminetad first due to the fact that it performed worse than the other two. This left Leonid and Peter. It was a tough choice, but I had to give the prize to Peter for taking the time to really think through the problem and figure out exactly how best to do the aggregations. Leonid was a very, very close second, and I really wish I had a consolation prize for him.

The End

And that's that. Thank you to everyone who participated in the challenge. I hope it was as much a learning experience for you as it was for me. Congratulations again to Peter. All of the materials are attached in the ZIP file; please let me know if you have any questions, comments, etc.

Peter DeBetta and I Rock .NET

Adam Machanic — Tue, 19 May 2009 14:10:00 GMT

... or something like that.

We're featured on this week's .NET Rocks! show, discussing some of the intricacies of database design with Richard and Carl. Enjoy!

Demos - TechEd DAT402 - Microsoft SQL Server 2008: Performance Profiling and Troubleshooting with Extended Events

Adam Machanic — Thu, 14 May 2009 18:52:00 GMT

Whew! Another TechEd session completed, and now I'm done for the week. Thanks to everyone who joined me today; I had a fantastic time talking about the ins and outs of Extended Events. Demos are attached. I realize how complex some of these topics are, so please feel free to drop me a note in the comments here with any questions or concerns. Also stay tuned--I'm continuing to work with and improve these samples and will be publishing updates from time to time in the coming months.

Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

Adam Machanic — Wed, 13 May 2009 23:46:00 GMT

A huge thank you to everyone who attended today's session on exception handling! I had a great time and got some very good, on-point questions from the audience. As promised, the demos are attached to this blog post. Please let me know in the comments if you have any questions (whether or not you attended--everyone is allowed to join the party).

I Swallowed the Twitter-Colored Pill

Adam Machanic — Thu, 07 May 2009 01:14:00 GMT

I'm not entirely certain what color that is, but follow me here (if you dare).

In keeping with the Twitter mentality I'll keep this post ultra-short.

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.

Do You Change the Results to Grid Font in SSMS?

Adam Machanic — Fri, 24 Apr 2009 20:28:00 GMT

Update: Please respond here instead. Thanks for the great idea, Alex!

Please respond and let me know:

A) Whether you change it at all

and

B) If so, whether you use a fixed width font (the default font is non-fixed width)

Background, in case you're curious: I modified a newer version of Who is Active?, based on some feedback I received from Aaron Bertrand asking for right-padding of the numbers in the result. I did this modification based on my assumption that most people won't change the default grid font, and even if they do change it they'll still use a non-fixed width font. I tested my modification on my end and sent it over to Aaron to see what he thought, and low and behold he has changed his own SSMS settings to use a fixed width font. Oops! I would be very interested in finding out how many of you do the same. If the vast majority of people do indeed leave the font unchanged, or at least in the same font family, perhaps I'll leave the right-padding as-is (I happen to like it, personally -- sorry, Aaron!). If I'm mistaken in my assumption, I'll rip it out.

Thanks for letting me know, either way.

TechEd Coming Up - Recommended Background for My Extended Events Talk

Adam Machanic — Thu, 23 Apr 2009 01:46:00 GMT

TechEd North America 2009 is just a few short weeks away, and I'm really looking forward not only to the show, but also the opportunity to visit Los Angeles for the first time in several years. I'm busy putting the finishing touches on my Extended Events talk, DAT402, "Microsoft SQL Server 2008: Performance Profiling and Troubleshooting with Extended Events", and I thought I should write a quick note to anyone out there who is thinking of attending.

What's noteworthy about this talk is that the TechEd team modified the title a bit and removed the word "Advanced", but left the talk at 400-level and also left the original abstract untouched:

Imagine tracking and aggregating wait statistics at the session level rather than the server level. Imagine seeing exactly how long each step in your query took and figuring out the real cost of that index scan. Imagine capturing an exception with an associated callstack--no more guessing about exactly what component failed and why. When you're done imagining, open your eyes and attend this session to learn all of these techniques and more, all thanks to Extended Events (XEvents)--the powerful new tracing infrastructure in SQL Server 2008. Designed for DBAs and developers who already understand the basics of XEvents, this session goes from 0 to 60 in the first few minutes. See a number of code examples and gain an understanding of how to maximize XEvents for performance profiling and troubleshooting purposes. If you're serious about making your SQL Servers fly, this is one session not to be missed.

When I gave this same talk earlier this year at SQL Server Connections in Orlando, some attendees apparently didn't understand that it really is an advanced talk, and showed up without quite enough background to follow along as well as they could have. I'm hoping to minimize that phenomenon at TechEd; so please keep in mind that although I do a quick (five-minute) refresher, that's it, and Extended Events is a much bigger topic than can be fully introduced in five minutes. Once the refresher part is over I jump into four fairly intense, demo-heavy deep-dive sections, so please, if you want to get your money's worth, make sure that you read one or more of the following in the next few weeks:

My chapter in the SQL Server 2008 Internals book
The Extended Events section in the recently-released Troubleshooting Performance Problems in SQL Server 2008 white paper
Paul Randal's Advanced Troubleshooting with Extended Events article in TechNet Magazine

Reading through these sources will give you the background so that you can focus on the performance troubleshooting aspects of the talk, rather than sitting there bogged down in the unfamiliar Extended Events syntax and terminology.

I'm looking forward to meeting some SQLblog readers at the event; if you're attending, even if you decide to skip my Extended Events talk, stop by the Database Practices TLC and say hi. Looking forward to seeing you in LA!

Connect Item: Globally-Scoped Metadata Views

Adam Machanic — Thu, 16 Apr 2009 15:09:00 GMT

Ever tried writing a more or less database-agnostic administrative script?

If the experience didn't make your head spin, you either have a stronger constitution than most DBAs or your script doesn't actually work properly. The fact is, due to the way most of the metadata views are currently scoped in SQL Server, pulling data from multiple databases requires a rather annoying set of techniques, usually involving temporary tables, dynamic SQL, and cursors. For a real-world example, just take a look at my Who is Active? script.

This could all be so much easier if the product simply exposed global views, available from anywhere, and keyed with a database_id column. The views could be internally partitioned to ensure great performance if you only need data from a single database, yet still flexible enough to provide data across the entire server. I think this feature would make the overall administrative experience much nicer, so I've created a Connect item, and I hope that you will vote for it (5, of course!):

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432689

Thanks for your support!

Who is Active? v8.40 - Now With Delta Power!

Adam Machanic — Mon, 30 Mar 2009 16:41:00 GMT

It has been only a month and a half since I posted the last version of Who is Active? (v7.30), but in that time I've made a huge number of changes, fixes, and enhancements. The new version, v8.40, is faster, more robust, and includes a few exciting features.

Click here to download Who is Active? v8.40

Following is a list of some of the things I've done with it, in no particular order, along with descriptions where applicable.

Added a collection interval option, @DELTA_INTERVAL. This option causes the script to collect numeric metrics -- such as reads, writes, and context switches -- twice. The script will wait between collections, based on the duration passed into the parameter (in seconds). The script will then calculate the differences (deltas), between the first and second collections, for any requests that were active for each collection. These deltas will be output in a new set of columns, reads_delta, writes_delta, context_switches_delta, etc.

For example, to use the default options and wait two seconds between collections, you would use the following:

EXEC sp_WhoIsActive @DELTA_INTERVAL = 2;

If you played with Who is Active? in the past you know that the script outputs a lot of data in a lot of different columns. I started feeling that the column list was getting unwieldy, so I implemented dynamic column lists as a fix. There are two changes here. First of all, the default column list will only contain columns associated with those features you have turned on. So for example, if you don't have @GET_LOCKS enabled, the locks column won't appear.

The second part of this fix is a new feature to make things even more dynamic: custom column lists. I sent a slightly earlier test version to Jimmy May, and his response was something along the lines of, "it's pretty good, but I really don't like the column order." To make Jimmy happy, I implemented the @OUTPUT_COLUMN_LIST option. To use this option, simply pass in whatever columns you would like to see, in whatever order you would like to see them, and the script will do the rest. Note that the output will be the intersection of whatever options are enabled and whatever columns are found in the list.

Here's an example I've been using in my own work recently:

EXEC sp_WhoIsActive
    @OUTPUT_COLUMN_LIST =
        '[login_name],[dd hh:mm:ss.mss],[sql_text],[reads],[writes],
        [context_switches],[physical_io],[wait_info],[blocking_session_id],
        [tempdb_writes],[tran_log_writes],[query_plan],[session_id]';

Another feature I added that makes things a bit more dynamic and customizable is the ability to sort the output based on whatever column and direction you would like. Interested in seeing the requests that are currently blocked? No need to scroll, just ask for them to sort high:

EXEC sp_WhoIsActive
@SORT_COLUMN = '[blocking_session_id]',
@SORT_COLUMN_DIRECTION = 'DESC';

While viewing the results on the screen in SSMS is great, sometimes I like to capture the results to a table for later analysis. So I added a few features in this most recent version to support automated collection.

To begin with, I realized that the default, nicely-formatted output of the script, while great for on-screen viewing, isn't good if you want to do your own analysis later. So I added a new option, @FORMAT_OUTPUT, that lets you control whether the output is or is not formatted. The default value for the parameter is 1, keeping with the previous behavior.

I also figured that if you want to do some later analysis it might be nice to know what time period you're working with. So I added a column called collection_time, which outputs a datetime instance representing the time that the script finished running.

Taking things one step further, I decided that with all of the dynamic column options it would be a pain to put together a table matching the output schema if you start playing with different settings. So I made things easy; no need to figure out what the output looks like; the script will write a CREATE TABLE statement for you. Simply enable the @RETURN_SCHEMA option and collect the output value from the @SCHEMA parameter.

Finally, due to nested INSERT/EXEC limitations, it's impossible to insert the result into a table when calling the stored procedure. No worries, the script will insert its output into a table for you. Just tell it where to send the data, via the @DESTINATION_TABLE option.

Here's an example of asking for the unformatted output schema, creating a destination table based on the current day, and collecting data 10 times, waiting 15 seconds between each collection:

DECLARE @destination_table VARCHAR(4000);
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112);

DECLARE @schema VARCHAR(4000);
EXEC sp_WhoIsActive
    @FORMAT_OUTPUT = 0,
    @RETURN_SCHEMA = 1,
    @SCHEMA = @schema OUTPUT;

SET @schema =
    REPLACE
    (
        @schema,
        '<table_name>',
        @destination_table
    );

EXEC(@schema);

DECLARE @i INT;
SET @i = 0;

WHILE @i < 10
BEGIN;
    EXEC sp_WhoIsActive
        @FORMAT_OUTPUT = 0,
        @DESTINATION_TABLE = @destination_table;

    SET @i = @i + 1;

    WAITFOR DELAY '00:00:15'
END;
GO

One of the key reasons I created the original version of Who is Active? was to help identify blocked and blocking SPIDs. And I thought it worked pretty well for that purpose until recently, when I discovered that sys.dm_exec_requests fails to properly identify blocking when queries go parallel. Oops. Fixed in the new version; it now uses the waiting tasks DMV for this purpose, instead of the requests DMV.

As part of this process I discovered that the joins to the tasks DMVs were slightly flawed in v7.30, so I fixed them up. And I was able to play some games to improve performance--enough that I decided to eliminate the @TASK_INFO option, making this the default behavior. This means that you'll always get correct blocker identification from Who is Active? v.8.40, along with a few bonus task-based metrics including physical I/O stats and the number of context switches.

Interested in information about transactions? So are most DBAs, which is why I promoted the transaction start time, which was previously embedded in the tran_log_writes column, to a top-level column of its own. This will still populate only with transactions that have actually done a write, but from what my testers tell me this is okay behavior. Let me know if you disagree. While I was in there I played even more performance games, and greatly improved the speed of the query when the @GET_TRANSACTION_INFO option is enabled.

A few smaller fixes are also worth mentioning here. First of all, I tested the script against a case-sensitive instance, and fixed a few issues there. So it should work for you no matter what collation you're using. Second, thanks to a discussion I had with Roman Nowak, I was able to finally solve the entitization problem that plagued the "clickable" query output produced by the script. Third, I put on my security hat and added some code to verify input arguments, as well as did a review of all of the dynamic SQL used by the script to make sure that it is not injectable.

Thank you to everyone who sent me feedback and/or feature requests! If you have an idea for th script, please make sure to send it my way. I have quite a bit of momentum at the moment and want to keep going and see just how far I can take this thing. I can only do this with your help.

Enjoy!

Click here to download Who is Active? v8.40

About the SQLblog Roller Outage

Adam Machanic — Sat, 21 Mar 2009 19:24:00 GMT

Over the past few days you might have noticed that the SQLblog external feeds roller had mysteriously vanished. We had some technical difficulties on Tuesday, things appeared to have fixed themselves by Wednesday morning, and then the problems returned Wednesday evening. So I decided to disable the feature. Honestly, I didn't think anyone would even notice, but I received some e-mails from some concerned parties; apparently there are people out there who actually use the roller.

The bad news is that we have no idea why this issue cropped up, but the good news is that I've been watching it now for three days and it seems fine. I've just turned it back on, and as part of the process of cleaning up the mess I even added a few new blogs to the list. As always, I'm looking for more great blogs to add, so if you have any ideas leave a comment here. Enjoy, and please let me know if you see any further issues.