THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

  • SQL Server 2016 “Hidden Gems” Resources

    Today I was honored to present my SQL Server 2016 “Hidden Gems” talk for the GroupBy online conference.

     

    The slide deck and demos are attached to this post.

     

    Following are some links to help you in further exploration of the various features I discussed:

     

    STRING_SPLIT

    https://msdn.microsoft.com/en-us/library/mt684588.aspx

    Aaron Bertrand’s performance tests: https://sqlperformance.com/2016/03/sql-server-2016/string-split

     

    Session Context

    https://msdn.microsoft.com/en-us/library/mt605113.aspx

     

    Time Zone Support

    sys.time_zone_info: https://msdn.microsoft.com/en-us/library/mt612790.aspx

    AT TIME ZONE: https://msdn.microsoft.com/en-us/library/mt612795.aspx

     

    HASHBYTES

    https://msdn.microsoft.com/en-us/library/ms174415.aspx

     

    Session Wait Stats

    https://msdn.microsoft.com/en-us/library/mt282433.aspx

     

    Live Query Statistics

    https://msdn.microsoft.com/en-us/library/dn831878.aspx

    Ability to attach to other sessions' Live Queries!  https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/

     

    Actual Query Plan Data Enhancements

    https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

     

    INPUTBUFFER DMV

    https://msdn.microsoft.com/en-us/library/mt652096.aspx

     

    Database Scoped Configuration

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/02/database-scoped-configuration/

     

    Increased Number of Index Keys

    https://msdn.microsoft.com/en-us/library/ms143432.aspx#Engine

     

    Increased Index Key Size

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/

     

    COMPRESS

    https://msdn.microsoft.com/en-us/library/mt622775.aspx

     

    Columnstore

    Too much stuff to list! Go google it.

  • The SQL Genie (T-SQL Tuesday #086)

    At some point a long time ago, when I was first getting serious about SQL Server, I became aware of an open secret.

    You see, there was this special Microsoft alias called “sqlwish.” Those in the know could send an e-mail and pass along suggestions for improving the product. And they wouldn’t just vanish into thin air; some actual member of the product team on the other end would not only read the suggestions, but also add them to the internal Official List of Stuff to Get Done.

    How exciting! A chance to make my mark on the product I worked with day in and day out. I immediately put together a list of my wishes and sent an e-mail. And over time, I sent a few more. I never heard back from anyone, so I have no clue if my missives were actually read. And while I can’t remember all of the things I asked for, I’m pretty sure none of them were ever implemented. (I can clearly remember only one request from that very first e-mail. I’ve asked for it again numerous times over the years and still want it today. No luck.)

    Fast-forward a few years and Microsoft introduced the Product Feedback Lab, a web-based solution for user feedback collection. The sqlwish alias was retired and now we could submit feedback in an open, transparent way. We were told by Microsoft that not only would someone read these items, but now we would actually get status updates and replies! An amazing idea. And it went well for a while. Users loved the Lab, populated it with lots of items, and Microsoft soon promoted it to a permanent home in a place called Connect.

    Alas, the powers that be at Microsoft seem to have severely under-estimated the amount of work and attention this endeavor would require. Members of the product group got busy or moved on to other things and the replies stopped coming as often. Users lost enthusiasm. And time simply passed, as it does. After a while Connect fell into a state of abandonment. The SQL Server items have occasionally seen periods of promising forward movement, as well as at least one horrible stage during which the product group indiscriminately closed virtually every open item. But generally speaking the site is mostly considered to be a joke by the SQL Server professionals I’m friendly with. It’s insanely slow, has an unbelievably awful UI, and most of the submitted items are eventually “resolved”—if they ever are—with the infamous “Won’t Fix” status. (Said resolutions are almost never accompanied by a comment from Microsoft or anyone else.)

    All of which doesn’t mean that Connect is completely useless. As desperate as we often are to see change in certain areas, Connect has become an amazing catalog of user stories. It’s one of the only mechanisms for most people to voice concerns about the direction the product is taking, and it turns out that DBAs have quite loud voices. Some tickets actually do go through the system and end up in that exalted state of having been “Fixed.” One such case that I will never forget occurred early on, when Hugo Kornelis demanded the release of SQL Server 2005 SP3. Over 800 people joined him and the service pack was released. A true win for and by the SQL Server community.

    But today’s post isn’t about victory. Today’s post is about a dream.

    This month’s T-SQL Tuesday challenge, issued by Brent Ozar, is simple, vague, and frustrating: find an interesting item on Connect and write about it. The problem for me is that I can’t find just one interesting item; there are simply too many from which to choose. But sometimes when I’m both especially bored and feeling a bit depressed about SQL Server I’ll peruse Connect by using one of its only good features, the ability to sort the items by “most voted.” (Note that even this feature doesn’t even work properly; the sort order is only kinda-sorta accurate.)

    What if, I wonder, Microsoft took a step back and actually listened to the voice of the masses? What if Microsoft actually committed to implementing the top voted items on the list?

    That’s exactly what would happen if Microsoft put me in charge. (Imagine that!) Looking over the most voted—but still open—items, and sorting the wheat from the chaff just a bit (I only want legit developer features), I could come up with a pretty amazing release.  Here’s the session I would present at PASS just before Microsoft fired me for not filling the bits with enough marketing fluff:

    SQL Server v.Next.Next++: Developer Enhancements That Will Completely Change Your Life

    Hyperbole? Maybe just a little bit. But check out some of these amazing features…

    First and foremost, I would eliminate the biggest debugging time waster in the history of the product. (Bad error messages!) That sounds nice. Nine years and 1300 votes, for those keeping score.

    I would so, so love to see this virtual table of errors that would completely revolutionize relational ETL. I’ve even written about it before. (Three and a half years ago. Sigh.) 600 votes over four years.

    Regular expressions. I have to admit that I absolutely hate them and even when I work in C# I often find some other way to solve my problems. But the built in LIKE/PATINDEX patterns just don’t cut it. And yeah, you can implement some SQLCLR solution for this, but it should just be built in by default. Only 475 votes in the past 10 years, but I guarantee this would be an amazingly popular feature among the hardcore developer set.

    Scalar functions are awful. It bears repeating. Scalar functions are awful. Just for good measure, maybe once more. Scalar functions are awful. But they’re so incredibly tempting! Encapsulation and re-use simply make development better, more scalable, more manageable. If only scalar functions weren’t so very awful. Fix them? Yes. Please. Now! 560 people over the past 10 years have agreed with this sentiment. I bet that number would be much higher if the item was not somewhat strangely worded.

    I use a lot of derived tables in my T-SQL. I bet you do, too. I spend so much time writing so very many derived tables. And they bloat my statements to ginormous proportions. But I have no choice! I need to be able to use expressions as filter qualifications. Oh wait, there’s an amazing solution for this problem, suggested by Itzik Ben-Gan. 340 people over the past seven years have ticked the upvote button. Maybe Microsoft would have implemented it were it suggested by someone who actually knows T-SQL pretty well. The world may never know.

    Table-valued parameters are one of those things that I was so excited about when they were released. And I told myself that typical Microsoft lie. “It’s a v1 thing. It will be improved over time.” Well, that hasn’t happened. And this is a feature so very ripe for improvement. Removing that READONLY restriction would mean the difference between today’s “meh” and an absolute developer productivity masterpiece. Nine years. 320 votes.

    How many times have you been asked by end users to change the order of columns in a table? “But tables are sets, and sets have no explicit order.” Guess what? Users don’t care. We waste so much time on this and SQL Server could so easily support changing the order. And this item asking for a feature to do it has been in the system so long (almost 12 years) that when you click on it you don’t even see the item, you get an error. (When Microsoft migrated the items from Product Feedback Lab to Connect, something got messed up and a large number of items went into this limbo state. Perhaps someone got a column out of order? It’s been 10 years since that transition and I’m not expecting a fix anytime soon. But you can still see the number of votes on the main list: 270. I’m sure making these items almost completely unavailable did them no favors in terms of votes.)

    NULLs. What’s the first thing you think of? 3VL? You’re a geek. Annoyance over subtle bugs? You’re quite correct. And while there are some pretty brilliant workarounds to the various NULL issues, the fact is that the ANSI standard includes a clause specifically dedicated to eliminating a huge subset of the problems. Why, after nine years and almost 300 votes, do we not have this?

    Magic numbers! That sounds cool. Except it’s not. It’s a horrible drain on readability and a pox on the manageability of your code. But we need them, because every database out there has “lookup tables” with specific values that we need to predicate on throughout the code base. SQL Server could solve this problem once and for all by allowing users to promote these values to named enumerators. How cool would that be?!? 220 votes, nine years.

    And finally, one more from Mr. Ben-Gan, a clever idea for improving TOP with the OVER clause. I love the OVER clause. I love TOP. Their offspring would be beautiful indeed… 180 voters over the past nine years have shared in this sentiment.

    Quite a list, isn’t it? I think it would in fact change the entire developer experience for the better. But these items are old, and these numbers aren’t too impressive. 180 votes, really? There are probably a hundred thousand SQL Server DBAs and developers out there. The community, it seems, has no faith in Connect. People have neither time nor inclination to navigate its painful interface just to click the vote button on an item that will sit untouched for a decade. It’s a sad state of affairs.

    Eternal optimist that I am, I still hit Connect from time to time. I still vote, and I still submit my own items. I remain ever hopeful. Microsoft, if you need a new product head and fall guy, just for one release, I’m waiting by the phone...

    And just for the record, the number one item in my very first sqlwish e-mail, and something I’d still love to see today? Implementation of DOMAINs, an ANSI-standard feature that would bring the type system out of the Dark Ages and into the 21st Century. Here it is on Connect. “Won’t Fix.” The genie clearly slept through that one.

  • T-SQL Tuesday Rules of Engagement

    Just over seven years ago (wow, does time fly!) I launched T-SQL Tuesday. The core idea is simple: Each month, one blogger acts as “host.” He or she is responsible for choosing an interesting topic in or related to the SQL Server realm. Then a bunch of other bloggers all write articles on that topic. Finally, the hosting blogger puts together a round up of all of the participating posts so that anyone who’s interested in reading can find all of the content in one convenient place.

    We’ve now gone through 85 months of this experiment (#86 was announced just today, by Brent Ozar). And I’m happy to report that at least from my perspective, it has mostly been a huge success. We’re nearing a big milestone, just a bit more than a year away, of hitting triple digits. And I don’t see this slowing down anytime in the foreseeable future. There is still plenty of sharing and learning to be done.

    In speaking with Brent, he mentioned that he wasn’t sure exactly what the correct rules are or where to find them, and he asked me to put things together in one clear place. It kind of makes sense that there is no central place: Although I control the hosts and help dictate the general rules, T-SQL Tuesday is an entirely community-based thing and has grown and spread quite organically. Each month, each host is free to bend the rules just a bit to fit his or her unique blog style. But there are some central guidelines and best practices, and I’ll document and describe each of them below.

    Before I present the rules, I’d like to take a moment to describe the purpose of all of this: T-SQL Tuesday is designed to strengthen the SQL Server blog community in two different ways. First and most obvious, it generates blog posts, and depending on the topic these tend to be educational in nature. More education means a stronger community, and that’s what we’re all about. Second, and a bit more subtly, T-SQL Tuesday was conceived to strengthen search engine reputation by creating lots of links between blogs. Search engines like pages that get linked to a lot. So we generate lots of links to lots of great content, and then that great content floats up to the top as people search for stuff. That’s a win-win in my book and something to keep in mind if you either host or participate in a T-SQL Tuesday.

    (Aside: Does driving up your blog traffic sound good to you? Write to me about hosting! See below for the rules. I am literally always looking for hosts, especially new blogger hosts.)

    Now that that’s out of the way, without further ado, I present…

    The Official T-SQL Tuesday Rules, as of January 2017

    1. Each month, a host blogger will be assigned to create a T-SQL Tuesday event on his or her blog.
    2. Creating an event entails several steps:
      1. First the host blogger must be assigned to a month.
        1. Getting a month assigned means writing to me (amachanic [at] gmail [dot] com) and asking to host.
        2. Prior to writing to me you must have participated in at least two prior T-SQL Tuesday events as a blogger.
        3. Prior to writing to me your blog must show decent on-going activity. This means, for established blogs, at least 6-8 technical posts a year. For newer blogs, I expect to see at least one technical post a month sustained for at least six months.
        4. Assuming that everything checks out, I will give you the next available month. Usually things are booked out four to six months in advance.
        5. I try not to repeat hosts more often than once every 24 months.
      2. The host blogger must first choose a topic.
        1. Despite the name of the event, this topic does not need to be about T-SQL, and in fact ideally should not be. Any topic in or related to the SQL Server realm can be covered.
        2. It is strongly suggested that topics be technical in nature, and also strongly suggested that topics be broad enough to cover a variety of different interests and job functions. A T-SQL specific topic would therefore not be recommended, as it would eliminate people who want to write about MDX, DAX, R, PowerShell, C#, or some other related toolset. Remember that not everyone is you, and that’s what makes this fun; we want to hear from all sorts of different perspectives.
        3. A list of all prior topics is available here: https://voiceofthedba.com/t-sql-tuesday-topic-list/. This list is maintained by Steve Jones, to whom I owe a huge debt of gratitude.
      3. The host blogger must create an invitation blog post. The invitation post is the means by which the host blogger will advertise that month’s event.
        1. The invitation post must include T-SQL Tuesday and the event number in its title.
        2. The invitation post must include the T-SQL Tuesday logo.
        3. The invitation post should usually go live either on the Tuesday one week prior to the second Tuesday of the event month, or on the Monday before that Tuesday. Note that some hosts have posted a bit earlier than that, in order to give participants more time. That’s allowed, but it usually backfires in the form of low participation as participants forget to write their posts. Posting later than a week out can backfire as well, due to people not having enough time.
          1. If your post does not go live by close of business on the US East Coast on the Tuesday one week prior to the second Tuesday of the event month, and you haven’t been in contact with me, you will lose your month and you may be banned from ever hosting again. I luckily have not had to exercise this clause very often! Thank you, hosts, for fulfilling your duty.
        4. The invitation post should describe the topic and outline what kinds of blog posts participants should write in order to participate in the event.
        5. The invitation post must mention that participant posts should go live on the second Tuesday of the event month. The recommendation is to limit this to 0:00 UTC to 23:59 UTC on that day, but hosts are free to change the rule to local time zones or however they see fit to enforce it.
        6. The invitation post should describe how participants should make the host aware of participating posts – via trackbacks, comments on the invitation blog post, etc.
        7. I appreciate it if invitation posts tell people to contact me so that they can host. But that’s not required.
      4. The host blogger should advertise his or her invitation through social media channels. Advertising can be done on the official T-SQL Tuesday hashtag, #tsql2sday.
      5. The host is not required to participate in the month’s event by creating his or her own on-topic post, but it is recommended.
      6. After the second Tuesday of the month, ideally by the end of the week, the host is expected to post a “round up.” The round up is a blog post that reiterates the month’s topic and links to all of the participant blog posts. Ideally this round up post should include some overall commentary on the content that was produced, and maybe some individual comments on each blog post.
    3. Participating in a T-SQL Tuesday simply means writing a blog post
      1. Any blogger can (and should!) participate. It does not matter how long your blog has been active or where you blog or even what language you post in. WE WANT YOU.
      2. The blog post should be on topic for the month as described in the invitation blog.
      3. The blog post should go live at the appropriate time as specified in the invitation post.
      4. Participant blog posts need not mention the text “T-SQL Tuesday” in either the subject or the body.
      5. Participant blog posts must include the T-SQL Tuesday logo. The logo must link back to the invitation post. This is the primary hard and fast rule that drives T-SQL Tuesday and the host reserves the right to not link back to you if you don’t follow it. If you don’t link, you’re not acting in the spirit of the event and you are in fact being very rude.
      6. That’s it! Have fun, write great content, and submit it.
    4. All other questions or concerns
      1. Figure it out for yourself and do what you think is best
      2. Failing number one, write to me and I’ll tell you what I think is best

    Amazing how 85 months turned my initial single paragraph description into this big set of rules. But it’s really just a simple exercise in spreading our collective knowledge. So go forth, write, learn, and enjoy. See you next Tuesday!

  • Query Tuning Training in Early 2017: Boston and Cleveland!

    A couple of days ago in Washington DC I presented the final 2016 delivery of my Tuning Your Biggest Queries seminar. The class went really well (at least from my perspective!) and it was a great end to a busy 2016 training schedule.

    The seminar has been an absolute joy to teach and I think it is some of the best work I've ever put together. Advanced query tuning is my favorite topic, by far, and there are so many interesting routes through the material that it never seems to get old or boring. At every class attendees have been asking unique and challenging questions that really make things feel dynamic. Plus, thanks to Microsoft's increased release cadence and focus on performance, there are a lot of new and cool things on the horizon that I plan to add to the content in the coming months. Exciting times to be a query tuner!

    So all that said, now that this year is closing down, I'm happy to report that I'm already working on more dates for next year! Here are the first two:

     

    Boston area, January 27, 2017 - Information Here

    This will be a standalone delivery, which I'm working on in conjunction with (and in support of) the local New England SQL Server users group.

     

    Cleveland, OH, February 3, 2017 - Information Here

    Just a week after the Boston class I'll do it again, this time as a pre-con for SQL Saturday 595. And hopefully I can squeeze in a visit to the Rock and Roll Hall of Fame!

     

    Even more dates forthcoming - stay tuned, and I hope to see you in 2017! 

  • Learn Advanced Query Tuning in Washington DC

    Just about every database professional on Earth has the same goal: Make things faster.

    There are numerous ways to achieve that goal, but for many people it comes down to a series of hunches, guesses, and trying whatever seemed to work last time.

    What if there was a more efficient way to get the job done? What if you could understand what's going on under the covers, know your options, and make the right moves for your actual situation?

    That's what my full-day Tuning Your Biggest Queries seminar is all about. In this unique class you'll learn about how the query processor works, how the query optimizer thinks, and how you can leverage an understanding of these internals to make your queries faster. What really sets this content apart is that there is almost no focus on indexing -- only a very brief discussion at the end of the day, and only after you've already learned how to improve query performance by orders of magnitude through rewrites.

    The idea is not to make you an index champion. The idea is to set you on the path to advanced query tuning mastery.

    I am pleased to announce the final 2016 public delivery of the seminar -- Friday, December 2, in Washington DC. And you can get early bird pricing through the end of this week!

    This delivery will act as a pre-conference seminar for SQL Saturday DC, but you are free to attend either or both.

    An updated course outline follows. Looking forward to seeing you there!


    Tuning Your Biggest Queries - December 2 - Washington DC 

    Module 1: Query Plans

    • How Query Plans Really Work
    • Data Acquisition Iterators
    • Join Iterators
    • Aggregation and Analysis Iterators
    • Parallel Processing 

    Module 2: Plan Shaping

    • T-SQL Rewrites That Waste Your Time
    • T-SQL Rewrites That Work
    • Query Hints
    • Leveraging Row Goals For Plan Control
    • Understanding Ideal Plan Shape
    • Performance Debugging Complex Plans 

    Module 3: Index Optimization 

    • The Levels of Index Understanding 
    • How B*Tree Indexes Really Work
    • How to Design Optimal B*Tree Indexes
    • How to Choose Among Indexing Options
    • Columnstores vs. B*Trees 

     

  • sp_whoisactive: The Big Fixes!

    Four and a half years have flown by since I released sp_whoisactive version 11.11.

    It's been a pretty solid and stable release, but a few bug reports and requests have trickled in. I've been thinking about sp_whoisactive v.Next -- a version that will take advantage of some newer SQL Server DMVs and maybe programmability features, but in the meantime I decided to clear out the backlog on the current version.

    And so, with that, I present sp_whoisactive version 11.16. Hosted on the brand new home for sp_whoisactive. (Yes, it's a bit minimalist for the moment.)

    Enjoy! 

  • Temporal Tables: Connect Item Round Up

    I've been thinking a lot about SQL Server 2016 temporal tables of late. I think it's possibly the most compelling feature in the release, with broad applications across a number of different use cases. However, just like any v.1 feature, it's not without its faults.

    I created a couple of new Connect items and decided to see what other things people had submitted. I combed the list and came up with a bunch of interesting items, all of which I think have great merit. Following is a summary of what I found. I hope you'll consider voting these items up and hopefully we can push Microsoft to improve the feature in forthcoming releases.

     

    Better, More Automatic Logging, Especially to Support Audit Scenarios

    A big theme I saw across many items, and something I've also heard from attendees of my sessions when I've presented on temporal tables, is the question of audit support. History is never just about the the data itself. Metadata around who made the change, how the change was made, and sometimes even why the change was made, can be vital for a number of scenarios. Temporal tables do not improve this experience today, but I think they very easily could.

    Permit additional hidden columns in temporal tables - by DBAIntrepid - https://connect.microsoft.com/SQLServer/Feedback/Details/1707429

    Storing audit columns and don't want your users to see them all the time? Wouldn't it be nice if they could be hidden by default? I certainly think so...


    Provide a mechanism for columns to automatically update themselves - by Adam Machanic - https://connect.microsoft.com/sql/Feedback/Details/3105516 

    UpdatedBy, UpdatedFrom, UpdatedSessionContext, and many other versions on the same theme. Every single database I see has these columns. Why can't SQL Server help us by automatically populating them on our behalf?

     

    Temporal Tables: Improve History Retention of Dropped Columns - by Adam Machanic - https://connect.microsoft.com/sql/Feedback/Details/3105517 

    One of the really annoying things about temporal tables is that the idea of what is and is not history can change. If I drop a column from my base table, the history of the column disappears. That, it seems to me, is unnecessary. Why not leave it (optionally?) and make it nullable?

     

    Temporal Tables (system versioned) Enhancment - by Guy Twena - https://connect.microsoft.com/SQLServer/Feedback/Details/1691517

    Not the best named item; this one is requests a column update mask, similar to that provided by CDC. I agree that this would be a very useful feature.

     

    Easier Querying 

    The first time you use SQL Server temporal tables, the query experience seems amazing. It's so much better and more integrated than any other form of "history" table we've ever had in SQL Server. But the experience very quickly breaks down as soon as you try to do something a bit more complex. The issue? The temporal predicates support only literals and variables. This means that, for example, you can't encapsulate a significantly complex temporal query in a view or inline UDF. I found lots of items around these theme but I decided to include only the following one in this post, as I thought it had the best phrasing and customer impact story.

    Temporal for FOR SYSTEM_TIME AS OF cannot use datetime functions. - by Eric A. Peterson - https://connect.microsoft.com/SQLServer/Feedback/Details/2582201 

     

    Better Administrative Support 

    Sometimes you need to change history. Maybe you need to trim some rows to keep table sizes in check. Maybe you need to backfill some history when merging data sets. Or maybe you need to fix an error. In any case, you can do this with temporal tables by briefly turning off the feature, making your changes, and then turning it back on. This isn't something end users should be doing, but is absolutely a valid administrative concern. Unfortunately, it's tougher than it should be to encapsulate in a stored procedure.

    Cannot catalog stored procedures for updating temporal columns in advance - by Greg Low - Australia - https://connect.microsoft.com/SQLServer/Feedback/Details/2500716

     

    That's it for this round up. Any other interesting items I missed? Please leave a note in the comments section if so! 

  • Upcoming Performance Seminars: Charlotte and Vegas!

    I am happy to report two more scheduled public deliveries of my new-for-2016 seminar, Tuning Your Biggest Queries! This seminar is all about how to approach difficult query tuning tasks by understanding exactly what the query optimizer is (and is not) doing with your data, how it internally works and thinks, and how you can take complete control. Thanks to great audience feedback I've made some tweaks and I think the content is getting better and better every time. 

    The upcoming dates are:

    Charlotte, NC - September 16 - Pre-conference seminar for SQL Saturday Charlotte. Note that this is almost sold out.

    Las Vegas, NV - October 10 - Pre-conference seminar for IT/Dev Connections. An early bird rate is still in effect for this one.

     

    Hope to see you there!

  • Performance Tuning Seminars in Philadelphia and Indianapolis

    Pretty much every environment I've ever seen follows the same patterns with regard to query size distribution: lots of typical run-of-the-mill queries (whatever is typical in that environment), followed by a diminishing number of larger queries (say, an order of magnitude bigger than typical), and a handful of gigantic queries that are several orders of magnitude beyond that.

    When tuning a slow SQL Server, what's the main thing I look at improving? I focus, first and foremost, on that final set, containing the biggest, most complex, most long-running things I can find. I believe this makes a lot of sense on several levels: Not only are the biggest queries the "lowest hanging fruit" -- i.e. things I tend to be able to markedly improve quickly, but they're also usually the ones dragging down the rest of the system. Bring down the monsters and all of the little guys can breathe easier.

    Unfortunately -- or fortunately if you enjoy these kinds of things as much as I do -- tuning the biggest and most complex queries can be quite a challenge. I often need to try to figure out what the query optimizer is "thinking," figure out which query hints I can or cannot leverage, and come up with interesting ways to re-write things to improve speed while maintaining the same logic. Dealing with big queries tends to be equal parts art (creativity) and science (raw application of logic). And the really interesting thing I've noted after tuning dozens of systems is that it doesn't matter whether my "big" query is bigger than your "big" query: It's the relative size of these beasts, as compared to whatever is standard, that makes them equally challenging and rewarding to tackle.

    Recently I was looking for a new topic on which to write a seminar, and I decided that this topic would be perfect. Query tuning is one of my favorite pastimes in the SQL Server world, virtually every environment can use some tuning, and virtually everyone has to deal with these kinds of big queries.

    So all that said, I'm proud to announce the first two deliveries of Tuning Your Biggest Queries, a full-day seminar I've put together for SQL Saturdays and conferences.

    The two dates are:


    Click through for abstract, pricing, and registration details. 
     
    If you can't make those dates, don't worry; more are forthcoming. Stay tuned. 

    Questions? Comments? Leave me a message below if you need clarification regarding the content, deliveries, or future dates.
     
    Hope to see you there!
  • sp_whoisactive for Azure SQL Database - Attempt #2

    Over a year ago now I announced a rough port of sp_whoisactive to Azure SQL Database. This worked thanks to Microsoft finally aligning the Azure DMVs to the box product DMVs...until Microsoft decided to rip out one of the key DMVs, sys.dm_os_sys_info and its ms_ticks column. This column is the key to unlocking various metrics in some of the other SQLOS DMVs.

    I tried to get my contacts at Microsoft to listen to me about restoring it, but my efforts were met with nothing but silence and lots of wasted time.

    So I set about looking for a workaround. This week I think I've finally managed to get something working that approximates the number I need from that view, ms_ticks. 

    Attached is sp_whoisactive v11.112 -- Azure Special Edition v2. Please give it a shot, and I am especially interested in feedback if you use the @get_task_info = 2 option when running sp_whoisactive. That is the main use case that's impacted by the lack of ms_ticks information and my attempt at a workaround.

    Thanks for your patience as I worked this out! Enjoy. 

  • SQLQueryStress: The Source Code

    Back in 2006, I created a very simple tool called SQLQueryStress. The goal of this tool was to enable the user to test a single query under some form of load -- up to 200 simultaneous threads.

    I wrote this tool primarily for a book I was working on at the time, and used it for most of the examples therein. And after that, truth be told, I decided that the tool was not very useful. I use it for demos in almost every presentation I give -- it's great for showing simple contention scenarios -- but I can't say that I've used it even a single time in an actual work project.

    Interestingly, although I personally haven't found much use for it, many of you out there seem to have. The tool has become incredibly popular over time, and I've received numerous e-mails over the years requesting features, as well as many from people requesting and even offering to pay for the source code.

    I've never said yes to any of these source code requests. For a long time I said no because I thought I might add more features and turn SQLQueryStress into something I could sell. Later, as my C# skills improved, I realized that the project was not very well done, and I really didn't want to release garbage code with my name on it. Still later, I thought I'd lost the source code forever in a hard drive crash. Meanwhile, SQLQueryStress has limped along unchanged, even as the wish list has continued to grow.

    Things changed last week, when I allowed one of my domains to expire; the one hosting SQLQueryStress. The domain was hosted by a very shady company that I'd long since decided to stop doing business with, and I found myself faced with the task of transferring things to a new provider. That is of course not a big deal, but I decided that the world would be better off without the current version of SQLQueryStress being quite so available. Almost 10 years after its creation -- most of the work done over a long weekend -- the world needs a better tool. I think it's time for someone to step up and deliver. And that someone is not going to be me.

    After the domain expired I went searching through my archives and found the source code. I took a quick look and confirmed what I thought all along: It's pretty bad. But today I'm releasing it for free, and the old adage stands: You get what you pay for!

    So here's the official word: The attached source code is hereby released to the world, copyright and royalty free. You may use it, if you like, for whatever you want. Enjoy! If you use it for a public project, I would appreciate a mention in the acknowledgements section, but even that is not required. This source code is yours, warts and all. I was tempted to do some cleanup work, but at this point it's just not something I'm ever going to touch again. I upgraded the project from Visual Studio 2005 to Visual Studio 2013, confirmed that it builds and seems to work, and that's that.

    I would very much like for someone to release a quality and modern stress tool. I would personally trash this source code and start with something brand new, but if you do use my code or even just my ideas as the base, here are the current top to-do items:

    • Fix bug where SQLQueryStress tends to crash after the Stop button is hit. Some variable, I guess, is not being properly reset.
    • Implement a better query editor. The control I used is bare bones to the max and does no one any favors.
    • Apparently there are some bugs around the statistics creation options not getting properly turned off when you turn them off.
    • Enable more than 200 simultaneous threads. This was just an arbitrary number and can be changed by simply modifying the appropriate control. But perhaps there is a smarter way to come up with a maximum?
    • Implement support for saving of statistics and collected information, and reporting on that data, especially when the same test is run more than once.
    • Implement support for multiple test queries.

    If you have any questions about the source code... you're out of luck. I haven't looked at it in at least 8 years. So fire up your debuggers!

    And finally, kindly let me know of any public projects you create, so that I can become an end user.

  • sp_whoisactive and Azure SQL Database

    I've received a number of requests over the past couple of years for an Azure-specific version of sp_whoisactive. Given the combination of a lack of interesting DMVs available in Azure and my general lack of interest in all things Azure related, creating a modified version of the proc has not exactly been a top priority.

    Luckily, now I don't have to. Microsoft made a number of enhancements to the newest version of Azure SQL Database--v12--including adding all of the DMVs. Even the deprecated ones!

    I just tested sp_whoisactive in a new instance, and it appears to work perfectly!

    The only caveat: The proc has a reference to a couple of views in MSDB, which are not available in Azure. (Makes sense, since you don't have access to Agent and other msdb-type features in Azure.) To get it to compile I had to comment out those lines: All the code between lines 4601 and 4635 of the sp_whoisactive v11.11 script.

    I've attached a version of the script to this blog post that has the lines commented out. I'm not sure if there are other issues hiding somewhere, but so far (very thorough testing for all of 5 minutes in an almost completely empty database) everything seems to be fine.

    In other words, there are probably some issues--if you test and see anything awry, please post a comment here and I'll work on it.

    Enjoy!

  • Re-Inventing the Recursive CTE

    Working with hierarchies in SQL Server never fails to be absolutely fascinating.

    On the face of things they’re super-easy to model and seem to fit perfectly well: parent_key, child_key, self-referencing FK, and you’re done. But then you go to query the thing and there is a complete breakdown.

    Prior to SQL Server 2005 the product included zero language support for hierarchical navigation. That release gave us the recursive CTE, a feature which perhaps could have solved all of our issues. But alas, its implementation is notoriously heavy and sluggish.

    As a result of the language gap, various smart and creative people have come up with lots of interesting and alternative ways of modeling hierarchies. These include materialized (enumerated) path models, nested sets, nested intervals, and of course SQL Server’s own HierarchyId data type. Each of these techniques involves deep understanding, often some interesting math, and lots of work to get right. Just the kind of thing that usually keeps me fully engaged.

    But this post isn’t about any of these alternative models. This post is about standard, run-of-the-mill adjacency lists. I’m a big fan of the adjacency list, if only for the fact that I can put one together without breaking out the espresso machine. If only the performance problems could be fixed, perhaps we could leverage them in a wider variety of scenarios.

    Important notes: 

    • First of all, you might want to review my PASS Summit 2014 presentation on parallelism techniques prior to continuing with this post. The content below builds heavily on many of the ideas I introduced in that session.
    • Second, you might (rightfully) decide that my reasoning is flawed, and that’s fine: I’m advocating for adjacency lists because of simplicity, and some of the queries below are somewhat less than simple. We all need to pick our own favorite poisons.
    • Finally, please be aware that these techniques have been heavily tested, but not rigorously tested. This is more of a research topic for me than something I would recommend as a “best practice,” and there very well may be bugs and/or caveats that I have yet to uncover.

     

    Why are we here?

    Several months ago on a mailing list I subscribe to, there was a long thread on hierarchies and hierarchical modeling. The thread was initially about HierarchyId but eventually morphed into a discussion on various other hierarchy techniques. Much of the thread was devoted to how annoying HierarchyId is to work with, and how annoying it is that adjacency lists can’t perform well.

    This thread sparked a set of questions in my mind: What’s wrong with a simple adjacency list? Why are recursive CTEs not better at doing their job? And is there any way to improve the situation?

    After giving the issue some thought I decided to try to apply some of the techniques I’ve been developing for optimizing parallel query plans. The result? Well, read on…

     

    Hierarchical Modeling, the Adjacency List, and Recursive CTEs

    “Every employee has a manager. So every employee’s row references his manager’s row. Except the CEO; that row has a NULL manager since the CEO doesn’t report to any of our employees.”

    Say that sentence to any technology person, any business person, any pointy-haired boss type, and right away they’ll get exactly what you mean. They’ll understand the model. And if your company hasn’t adopted some weird matrix management approach, it will fit. It’s a simple and absolutely perfect way to model simple reality. It’s the adjacency list.

    And not only is modeling the data easy, data management is just as simple. Need to insert a new middle manager somewhere along the line? Just insert a row and update a few other rows. Need to remove someone? Reverse that process.

    So why, then, do we play games and attempt to use other models? Fire up your favorite hierarchy and ask for a report, with full materialized paths, of everyone who reports up to the CEO. (In the code attached to this post you’ll find two test hierarchies, one wide and one deep. The wide hierarchy has 1,111,111 nodes in only 7 levels. The deep hierarchy has slightly fewer nodes—1,048,575—but it’s 20 levels deep.)

    More than likely—if you’re current with your T-SQL skills—you’ll write a query like this one:

    WITH
    paths AS
    (
        SELECT
            EmployeeID,
            CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
        FROM EmployeeHierarchyWide
        WHERE
            ManagerID IS NULL

        UNION ALL

        SELECT
            ehw.EmployeeID,
            CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.'))
        FROM paths AS p
        INNER JOIN EmployeeHierarchyWide AS ehw ON
            ehw.ManagerID = p.EmployeeID
    )
    SELECT
        *
    FROM paths

    This query materializes the path, separating node (employee) IDs using periods, by leveraging a recursive CTE. It returns the desired results, but at a cost: This version, which operates on the wide test hierarchy, takes just under 10 seconds on this end, run in Management Studio with the Discard Results After Execution option set.

    Depending on your typical database style—transaction processing vs. analytical—10 seconds is either a lifetime or doesn’t sound too bad. (I once interviewed a career OLTP developer who told me that no query, in any database, ever, should run for longer than 40ms. I told the company to not hire her for the data warehouse developer position she was interviewing for. I believe her head would have quite literally exploded, right in the middle of her second heart attack, about an hour before lunch on her first day.)

    Once you reset your outlook on query times to something a bit more realistic, you might notice that this isn’t a gigantic amount of data. A million rows is nothing these days, and although the rows are artificially widened—the table includes a string column called “employeedata” that contains between 75 and 299 bytes per row—only 8 bytes per row are brought into the query processor on behalf of this query. 10 seconds, while quite brief for a big analytical query, should be sufficient time to answer much more complex questions than that which I’ve posed here. So based purely on the metric of Adam’s Instinct and Gut Feel, I hereby proclaim that this query feels significantly too slow.

    Just like other queries, understanding the performance characteristics of this one requires a peek at the execution plan:

    image

    In this plan, the anchor part of the CTE is evaluated on the upper subtree under the Concatenation operator, and the recursive part on the lower subtree. The “magic” that makes recursive CTEs work is contained within the Index Spool seen at the upper left part of the image. This spool is, in fact, a special version that allows rows to be dropped in and re-read in a different part of the plan (the Table Spool operator which feeds the Nested Loop in the recursive subtree). This fact is revealed with a glance at the Properties pane:

    image

    The spool in question operates as a stack—a last in, first out data structure—which explains the somewhat peculiar output ordering we see when navigating a hierarchy using a recursive CTE (and not leveraging an ORDER BY clause):

    image

    The anchor part returns EmployeeID 1, and the row for that employee is pushed (i.e. written) into the spool. Next, on the recursive side, the row is popped (i.e. read) from the spool, and that employee’s subordinates—EmployeeIDs 2 through 11—are read from the EmployeeHierarchyWide table. Due to the index on the table, these are read in order. And because of the stack behavior, the next EmployeeID that’s processed on the recursive side is 11, the last one that was pushed.

    While these internals details are somewhat interesting, there are a few key facts that explain both performance (or lack thereof) and some implementation hints:

    • Like most spools in SQL Server, this one is a hidden table in tempdb. This one is not getting spilled to disk when I run it on my laptop, but it’s still a heavy data structure. Every row in the query is effectively read from one table and then re-written into another table. That can’t possibly be a good thing from a speed perspective.
    • Recursive CTEs cannot be processed in parallel. (A plan containing a recursive CTE and other elements may be able to use parallelism for the other elements—but never for the CTE itself.) Even applying trace flag 8649 or using my make_parallel() function will fail to yield any kind of parallelism for this query. This greatly limits the ability for this plan to scale.
    • The parallelism limitation is especially interesting because it is not necessary. Why, for example, can’t one thread process the subordinates of EmployeeID 11, while a second thread processes the subordinates of EmployeeID 10?
    • The stack behavior, likewise, doesn’t really matter. Do you care whether EmployeeID 11 is processed prior to EmployeeID 10? Would this change the output in any meaningful way? Of course not. The stack was no doubt chosen because it’s a simple data structure for solving the problem at hand, but from an implementation standpoint a queue would have been equally effective.

    I like the idea of adjacency lists, and I like the idea of recursive CTEs. They’re easy to understand and easy to maintain. To go faster we must both eliminate tempdb from the equation and remove the limitations on parallel query plans. And using the built-in functionality, that’s just not going to happen. Solution? Roll our own.

     

    hierarchy_navigator: The SQLCLR Recursive CTE

    After consideration of the four facts outlined in the previous section, I realized that it would be fairly simple to create a “recursive CTE” library in C#. By doing so I would be able to eliminate many of the issues with T-SQL recursive CTEs and take full control over processing.

    My core thought was that by creating my own data structures I could eliminate much of the tempdb overhead inherent with T-SQL recursive CTEs. There would naturally be transfer and allocation overhead as part of moving the data into the CLR space, but I felt that I could optimize things to the point where that cost would still be far less than what the query processor has to do in order to maintain a spool. The tradeoff is of course memory, and that’s something I feel is worth sacrificing for better performance. As always, you should make your own decisions on those kinds of issues based on your applications and workloads.

    The data structure I decided to work with was a lock-free queue. Why a queue instead of a stack? Because I had already written the queue for something else. As mentioned above, this is merely an implementation detail. It should not matter to you in what order the data is processed, nor in what order it is output, in the absence of an ORDER BY clause.

    The main benefit of a lock-free queue? Since it’s naturally thread safe it inherently supports parallelism. And since it’s lock-free my CLR assembly can be cataloged as SAFE, a nice benefit especially now that SAFE CLR assemblies are supported in Azure SQL Database.

    Writing the queue in C# is one thing; getting the query processor to use it is a slightly more arduous task. My initial thought was to create a system that would support queries of a form similar to the following:

    SELECT 
        i.EmployeeID, 
        i.FullPath
    FROM
    (
        SELECT
            EmployeeID,
            CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
        FROM EmployeeHierarchyWide
        WHERE
            ManagerID IS NULL

        UNION ALL

        SELECT
            ehw.EmployeeID,
            CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.'))
        FROM dbo.hierarchy_inner(@@SPID) AS hi
        INNER JOIN EmployeeHierarchyWide AS ehw ON 
            ehw.ManagerID = hi.EmployeeID
    ) AS i
    CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho

    The idea here was to make the query feel more or less “recursive CTE-esque.” Each row from the derived table [i] (which is really just a modified recursive CTE) would push values into the hierarchy_outer() TVF. This TVF would then internally enqueue the EmployeeID and path and subsequently output a row. (Any row would be fine—the presence of a row is what would matter, not its content, so the idea was for the function to output an integer column called “x” that always had a value of 0.) On the “recursive” part of the query, the hierarchy_inner() function could dequeue an EmployeeID and path, driving a seek into EmployeeHierarchyWide, which would then feed the hierarchy_outer() function, and so on and so forth.

    Each of the functions in this design takes a SQL Server session_id as an argument, in order to appropriately scope the memory allocation required to handle passing of values around on the lock-free queue.

    Part of this design required setting up some internal synchronization logic in the functions. It is to be expected that at various points the internal queue may not have enough of a backlog to keep all of the worker threads busy, and we wouldn’t want the threads without enough work to do to shut down until the entire process is actually complete. In order to facilitate this, I implemented logic as explained by the following flowchart:

    image

    In this scheme each thread sets a central flag (actually a counter) upon entering the function. If a thread begins outputting rows from the queue (and, therefore, reading from the recursive side and potentially pushing more rows onto the queue), that flag will stay set, indicating that any other threads should not shut down even if they have no work to do. The threads that are waiting will yield until either some work appears on the queue, or all of the other threads have set the reader flag off, thereby indicating that no work is forthcoming.

    Once coded and deployed, at first blush everything seemed great. The query optimizer produced a perfect serial plan:

    image

    This plan had exactly the correct shape, with exactly the correct behaviors. And reducing the tempdb overhead helped tremendously: this plan ran in only 6.5 seconds, 45% faster than the recursive CTE.

    Alas, making this into a parallel query was not nearly as easy as simply applying TF 8649. As soon as the query went parallel myriad problems cropped up. The query optimizer, having no clue what I was up to, or the fact that there was a lock-free data structure in the mix, started trying to “help” in various ways…

    image

    This plan might look perfectly decent to the casual observer. Almost the same shape as before, except for that Distribute Streams iterator, whose job it is to parallelize the rows coming from the hierarchy_inner() function. This would have been perfectly fine if hierarchy_inner() were a normal function that didn’t need to retrieve values from downstream in the plan via an internal queue, but that latter condition creates quite a wrinkle.

    The reason this didn’t work? In this plan the values from hierarchy_inner() must be used to drive a seek on EmployeeHierarchyWide so that more rows can be pushed into the queue and used for latter seeks on EmployeeHierarchyWide. But none of that can happen until the first row makes its way down the pipe. This means that there can be no blocking iterators on the critical path. If anything blocks that critical first output row from being used for the seek, or those latter rows from driving more seeks, the internal queue will empty and the entire process will shut down. And unfortunately, that’s exactly what happened here. Distribute Streams is a “semi-blocking” iterator, meaning that it only outputs rows once it amasses a collection of them. (That collection, for parallelism iterators, is known as an Exchange Packet.)

    Phrased another way, the semi-blocking behavior created a chicken-and-egg problem: The plan’s worker threads had nothing to do because they couldn’t get any data, and no data could be sent down the pipe until the threads had something to do. I considered modifying the hierarchy_inner() function to output specially marked junk data in these kinds of situations, in order to saturate the Exchange Packets with enough bytes to get things moving, but that seemed like a dicey proposition. I was unable to come up with a simple algorithm that would pump out only enough data to kick off the process, and only fire at appropriate times. (Such a solution would have to kick in for this initial state problem, but should not kick in at the end of processing, when there is truly no more work left to be done.)

    The only solution, I decided, was to eliminate all blocking iterators from the main parts of the flow—and that’s where things got just a bit more interesting.

    The Parallel APPLY pattern that I have been speaking about at conferences for the past few years works well partly because it eliminates all exchange iterators under the driver loop, so was is a natural choice here. Combined with the initializer TVF method that I discussed in my PASS 2014 session, I thought this would make for a relatively easy solution:

    SELECT
        p.*
    FROM
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    ) AS v
    OUTER APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                EmployeeID,
                CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
            FROM EmployeeHierarchyWide
            WHERE
                ManagerID IS NULL

            UNION ALL

            SELECT
                ehw.EmployeeID,
                CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.'))
            FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi
            INNER JOIN EmployeeHierarchyWide AS ehw ON 
                ehw.ManagerID = hi.EmployeeID
        ) AS i
        CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho
    ) AS p
    WHERE
        p.EmployeeID IS NOT NULL

    To force the execution order I modified the hierarchy_inner function to take the “x” value from the initializer function (“hierarchy_simple_init”). Just as in the example shown in the PASS session, this version of the function output 256 rows of integers in order to fully saturate a Distribute Streams operator on top of a Nested Loop.

    Once applying TF 8649 I discovered that the initializer worked quite well—perhaps too well. Upon running this query rows started streaming back, and kept going, and going, and going…

    image

    The issue? The anchor part must be evaluated only once, even if there are numerous threads involved in the query. In this case I built no protection against it being evaluated multiple times, so the query processor did exactly as I asked and evaluated it 256 times—once per initializer row. As a result, my query produced 256 times more rows than were needed; definitely not the desired output.

    My solution for this was to create yet another function, this one to operate as part of the anchor:

    SELECT
        p.*
    FROM
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    ) AS v
    OUTER APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                EmployeeID,
                CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
            FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha
            CROSS JOIN EmployeeHierarchyWide
            WHERE
                ManagerID IS NULL

            UNION ALL

            SELECT
                ehw.EmployeeId,
                CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.'))
            FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi
            INNER JOIN EmployeeHierarchyWide AS ehw ON 
                ehw.ManagerID = hi.EmployeeID
        ) AS i
        CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho
    ) AS p
    WHERE
        p.EmployeeID IS NOT NULL

    The function hierarchy_anchor() shown in this version of the query was designed to use the exact same signature as the hierarchy_inner() function, but without the need to touch the queue or anything else internal except a counter to ensure that it would return one, and only one row, per session.

    It almost worked!

    image

    The optimizer decided to push the hierarchy_anchor() function call under the anchor EmployeeHierarchyWide seek, which means that that seek would be evaluated 255 more times than necessary. That could have been considered a flaw, but at this point I was okay with it because each of those 255 seeks were comparatively inexpensive. The anchor part still returned only one set of actual output rows, by virtue of the function filtering things out. So far so good.

    Unfortunately, changing the characteristics of the anchor part also had an impact on the recursive part. The optimizer introduced a sort after the call to hierarchy_inner(), which was a real problem.

    The idea to sort the rows before doing the seek is a sound and obvious one: By sorting the rows by the same key that will be used to seek into a table, the random nature of a set of seeks can be made more sequential. In addition, subsequent seeks on the same key will be able to take better advantage of caching. Unfortunately, for this query these assumptions are wrong in two ways. First of all, this optimization should be most effective when the outer keys are nonunique, and in this case that is not true; there should only be one row per EmployeeID. Second, Sort is yet another blocking operator, and we’ve already been down that path.

    Once again the issue was that the optimizer doesn’t know what’s actually going on with this query, and there was no great way to communicate. Getting rid of a sort that has been introduced due to this type of optimization requires either a guarantee of distinctness or a one-row estimate, either of which tell the optimizer that it’s best not to bother. The uniqueness guarantee is impossible with a CLR TVF without a blocking operator (sort/stream aggregate or hash aggregate), so that was out. One way to achieve a single-row estimate is to use the (admittedly ridiculous) pattern I showed in my PASS 2014 session:

    SELECT
        p.*
    FROM
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    ) AS v
    OUTER APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                EmployeeID,
                CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
            FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha
            CROSS JOIN EmployeeHierarchyWide
            WHERE
                ManagerID IS NULL

            UNION ALL

            SELECT
                ehw.EmployeeID,
                CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.'))
            FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi
            CROSS APPLY (SELECT SUM(id) FROM (VALUES(hi.EmployeeID)) AS p0 (id)) AS p(y)
            CROSS APPLY (SELECT SUM(id) FROM (VALUES(hi.EmployeeID)) AS p0 (id)) AS q(y)
            INNER JOIN EmployeeHierarchyWide AS ehw ON
                ehw.ManagerID = hi.EmployeeID
            WHERE
                hi.EmployeeID = p.y
                AND hi.EmployeeID = q.y
                AND hi.EmployeeID = CHECKSUM(hi.EmployeeID)
        ) AS i
        CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho
    ) AS p
    WHERE
        p.EmployeeID IS NOT NULL

    The nonsense (and no-op) CROSS APPLYs combined with the nonsense (and once again no-op) predicates in the WHERE clause rendered the desired estimate and eliminated the sort in question:

    image

    Unfortunately, zooming out a bit revealed that other parts of the plan were also impacted:

    image

    The Concatenation operator between the anchor and recursive parts was converted into a Merge Join, and of course merge requires sorted inputs—so the Sort had not been eliminated at all. It had merely been moved further downstream!

    To add insult to injury, the query optimizer decided to put a Row Count Spool on top of the hierarchy_outer() function. Since the input values were unique the presence of this spool would not pose a logical problem, but I saw it as a useless waste of resources in this particular case, as it would never be rewound. (And the reason for both the Merge Join and the Row Count Spool? The same exact issue as the prior one: lack of a distinctness guarantee and an assumption on the optimizer’s part that batching things would improve performance.)

    After much gnashing of teeth and further refactoring of the query, I managed to bring things into a working form:

    SELECT
        p.*
    FROM
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    ) AS v
    CROSS APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                anchor.*
            FROM
            (
                SELECT TOP(1)
                    *
                FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha0
            ) AS ha
            CROSS APPLY
            (
                SELECT
                    EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
                FROM EmployeeHierarchyWide
                WHERE
                    ManagerID IS NULL
            ) AS anchor

            UNION ALL

            SELECT
                recursive.*
            FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi
            OUTER APPLY
            (
                SELECT
                    ehw.EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath
                FROM EmployeeHierarchyWide AS ehw
                WHERE
                    ehw.ManagerID = hi.EmployeeID
            ) AS recursive
        ) AS i
        CROSS APPLY
        (
            SELECT TOP(1)
                ho0.*
            FROM dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho0
        ) AS ho
        WHERE
            i.EmployeeID IS NOT NULL
    ) AS p

     

    Use of OUTER APPLY between the hierarchy_inner() function and the base table query eliminated the need to play games with the estimates with that function’s output. In experimenting with the hierarchy_outer() function call I discovered that telling the optimizer that it would return only one row eliminated the need to work with the outer estimate in order to remove the Merge Join and Row Count Spool. This was done by using a TOP(1), as is shown in the table expression [ho] in the above query. A similar TOP(1) was used to control the estimate coming off of the hierarchy_anchor() function, which helped the optimizer to eliminate the extra anchor seeks into EmployeeHierarchyWide that earlier versions of the query suffered from.

    Use of OUTER APPLY on the recursive part of the query created an interesting twist: nonmatching rows started cycling back in via the hierarchy_outer() call, creating an endless loop—and an endless query. To solve that issue I migrated the NOT NULL check on EmployeeID inside of derived table [p], and converted the outermost APPLY to a CROSS APPLY—which, luckily, did not change the plan shape. (Had it done so, two NOT NULL checks would have been required in this form of the query.)

    The end result query plan (note that this used TF 8649):

    image

     

    Final Enhancements

    The above query returned the correct results, properly supported parallelism, and ran fairly quickly: around 2.2 seconds at DOP 6 on my laptop. However, the work was not completely done.

    While attempting to further reduce the run time I began experimenting with different options and ended up playing with a scalar version of the hierarchy_outer() function. Instead of returning a row, it returned 0 when it successfully enqueued a row. If a NULL EmployeeID was passed in it would return NULL, thereby signaling an invalid row. This changed the query to the following form:

    SELECT
        p.*
    FROM
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    ) AS v
    CROSS APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                anchor.*
            FROM
            (
                SELECT TOP(1)
                    *
                FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha0
            ) AS ha
            CROSS APPLY
            (
                SELECT
                    EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
                FROM EmployeeHierarchyWide
                WHERE
                    ManagerID IS NULL
            ) AS anchor

            UNION ALL

            SELECT
                recursive.*
            FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi
            OUTER APPLY
            (
                SELECT
                    ehw.EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath
                FROM EmployeeHierarchyWide AS ehw
                WHERE
                    ehw.ManagerID = hi.EmployeeID
            ) AS recursive
        ) AS i
        CROSS APPLY
        (
            VALUES(dbo.hierarchy_outer_scalar(@@SPID, i.EmployeeID, i.FullPath))
        ) AS ho (value)
        WHERE
            ho.value IS NOT NULL
    ) AS p

    I didn’t expect much from this and tried it only on a whim—so I was pleasantly surprised when the query finished in only 1.4 seconds, a rather large improvement as compared to the 2.2 second run times I’d been seeing with the prior version.

    The reason this is faster? The scalar function interface requires only a single underlying call, whereas the table-valued interface requires at least two, plus CLR object conversion overhead, plus CLR iterator overhead. The query plan below shows that the scalar version removed one of the plan’s subtrees, which probably also contributed a small performance gain.

    image

    The next step was to hide (i.e. encapsulate) as much complexity as possible. I realized that the various TOP and DISTINCT clauses, as well as the @@SPID calls, could all be handled in a layer of inline T-SQL table valued functions. I created each of the following:

    CREATE FUNCTION dbo.hierarchy_init_t()
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT DISTINCT
            x
        FROM dbo.hierarchy_simple_init(@@SPID)
    )
    GO

    CREATE FUNCTION dbo.hierarchy_anchor_t
    (
        @init_token INT
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT TOP(1)
            *
        FROM dbo.hierarchy_anchor(@init_token, @@SPID)
    )
    GO

    CREATE FUNCTION dbo.hierarchy_inner_t
    (
        @init_token INT
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT
            *
        FROM dbo.hierarchy_inner(@init_token, @@SPID)
    )
    GO

    CREATE FUNCTION dbo.hierarchy_outer_t
    (
        @EmployeeID INT,
        @FullPath NVARCHAR(900)
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT
            0 AS value
        WHERE
            dbo.hierarchy_outer_scalar(@@SPID, @EmployeeID, @FullPath) IS NOT NULL
    )
    GO

    Creating these functions allowed me to create a query that came very close to my original goal, and with the same fast query plan I’d managed to achieve in the prior iteration:

    SELECT
        p.*
    FROM dbo.hierarchy_init_t() AS v
    CROSS APPLY
    (
        SELECT
            i.EmployeeID,
            i.FullPath
        FROM
        (
            SELECT
                ehw.EmployeeID,
                CONVERT(VARCHAR(900), CONCAT('.', ehw.EmployeeID, '.')) AS FullPath
            FROM dbo.hierarchy_anchor_t(v.x) AS ha
            CROSS JOIN EmployeeHierarchyWide AS ehw
            WHERE
                ehw.ManagerID IS NULL

            UNION ALL

            SELECT
                ehw.EmployeeID,
                CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath
            FROM dbo.hierarchy_inner_t(v.x) AS hi
            INNER JOIN EmployeeHierarchyWide AS ehw WITH (FORCESEEK) ON
                ehw.ManagerID = hi.EmployeeID
        ) AS i
        CROSS APPLY dbo.hierarchy_outer_t(i.EmployeeID, i.FullPath) AS ho
    ) AS p

    The only slight annoyance here was the FORCESEEK hint I was forced to put on the recursive side. Without it, the query optimizer decided to use a Hash Join for that part of the plan, which obviously didn’t fit at all. As compared to many of the games played on the way to this query, I found FORCESEEK to be very minor, so I wasn’t too upset by its being there. Plus I was able to convert the odd-looking OUTER APPLY into an INNER JOIN, so I decided that it was a net win.

    The final refactoring step was to rename the functions and their outputs to be more general.

    I arrived at the following four T-SQL functions, which are what you will find in the archive attached to this post:

    • hierarchy_init() – This is the initializer function, and returns a column called “initialization_token.” It is backed by a CLR function called hierarchy_init_inner().
    • hierarchy_anchor(@initialization_token) – The anchor function, this returns a column called “value.” It is backed by hierarchy_anchor_inner().
    • hierarchy_recursive(@initialization_token) – The recursive function, this returns two columns, “id” (an integer) and “payload” (nvarchar(4000)). The idea is that most hierarchical navigation will be done using some form of integer parent/child ID scheme. The payload, as a string column, can be packed with anything you’d like to carry along for the ride. This function is backed by hierarchy_recursive_inner().
    • hierarchy_enqueue(@id, @payload) – This is the outer function. It returns a column called “value” and is backed by the scalar hierarchy_enqueue_inner().

    The final version of the query, which you can replicate using the attached scripts, is as follows. Note that I’ve utilized make_parallel(), rather than TF 8649, in order to guarantee a parallel plan.

    SELECT
        x.*
    FROM dbo.make_parallel()
    CROSS APPLY
    (
        SELECT
            p.*
        FROM dbo.hierarchy_init() AS hi
        CROSS APPLY
        (
            SELECT
                i.EmployeeID,
                i.FullPath
            FROM
            (
                SELECT
                    ehw.EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT('.', ehw.EmployeeID, '.')) AS FullPath
                FROM dbo.hierarchy_anchor(hi.initialization_token) AS ha
                CROSS JOIN EmployeeHierarchyWide AS ehw
                WHERE
                    ehw.ManagerID IS NULL

                UNION ALL

                SELECT
                    ehw.EmployeeID,
                    CONVERT(VARCHAR(900), CONCAT(hr.payload, ehw.EmployeeID, '.')) AS FullPath
                FROM dbo.hierarchy_recursive(hi.initialization_token) AS hr
                INNER JOIN EmployeeHierarchyWide AS ehw WITH (FORCESEEK) ON
                    ehw.ManagerID = hr.id
            ) AS i
            CROSS APPLY dbo.hierarchy_enqueue(i.EmployeeID, i.FullPath) AS he
        ) AS p
    ) AS x

     

    Final Notes and Script Attachments

    As mentioned at the top of this post, this was more of a research project than something intended as a solid and proven solution. The techniques presented here have been heavily tested on my laptop and were briefly tested on one real-world project. In both cases I have been extremely happy with the results and have noticed no issues on recent builds of the functions. That said, multithreaded code is always tricky to absolutely guarantee, and I do not know whether some race condition or bug still exists in my code.

    Outside of my code there is also a big issue, and it’s called the query optimizer. While I’ve done everything in my power to control it, use of these components in the real world should only be done by advanced practitioners who are very comfortable reading and manipulating query plans. Divergence from ideal plans may result in wrong results, exceptions, or endless loops.

    The archive attached to this post includes create scripts for two hierarchy test tables (I wanted to test behavioral differences between deep and wide hierarchies. It turns out there aren’t really any, but since I already did the work you can try yourself.); the CLR functions in a binary format (the C# code will not be published at this time); the T-SQL outer functions; and the final query.

    You are licensed to use the code and binaries in any project internal to your company. You are not allowed to re-distribute the code, binaries, or a reverse-engineered version of either as part of another project without my written consent.

    If you do use this code I would love to know how it goes; please drop me a line via e-mail or in the comments here.

    Enjoy, thanks for reading, and as always let me know if you have any questions!

     

    Acknowledgement

    Huge thanks to Julian Bucknall, who kindly fielded my questions as I was writing my lock-free queue implementation.

     

    Update, 2015-10-04

    I found and fixed a minor bug in the assembly. The attachment has been updated with the change.

  • SQLBits 2015 - Query Tuning Mastery: Clash of the Row Goals - Demos

    Thanks to everyone who attended my talk yesterday! The demos are attached to this post. Let me know if you have any questions.

    Until next time...

  • New Book! T-SQL Querying with Itzik Ben-Gan, Dejan Sarka, Kevin Farlee

    I'm excited to announce that after a year of work, T-SQL Querying is now available in e-book form (dead trees will ship next month)! 

    As you might guess from the author list, this book goes deep into various aspects of T-SQL -- both from querying and query processing perspectives. The book includes Itzik's signature T-SQL coverage, data warehousing information from Dejan, and in-memory OLTP from Kevin. The sections I contributed focus on my favorite topics: parallelism and SQLCLR programming.

    I'm quite proud of what we've accomplished and I hope you'll enjoy the fruits of our labor. 

More Posts Next page »

This Blog

Syndication

Privacy Statement