Search results matching tags 'Performance' and 'SQLCLR'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Performance,SQLCLR&orTags=0Search results matching tags 'Performance' and 'SQLCLR'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Re-Inventing the Recursive CTEhttp://sqlblog.com/blogs/adam_machanic/archive/2015/04/07/re-inventing-the-recursive-cte.aspxTue, 07 Apr 2015 13:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58352Adam Machanic<p>Working with hierarchies in SQL Server never fails to be absolutely fascinating.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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.</p> <blockquote> <p><b>Important notes:</b>&nbsp;</p> <ul> <li>First of all, you might want to review my <a href="https://www.youtube.com/watch?v=CTB7LrQVu5c" mce_href="https://www.youtube.com/watch?v=CTB7LrQVu5c">PASS Summit 2014 presentation</a> on parallelism techniques prior to continuing with this post. The content below builds heavily on many of the ideas I introduced in that session.</li> <li>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.</li> <li>Finally, please be aware that these techniques have been heavily tested, but not <i>rigorously</i> 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.</li> </ul> </blockquote> <p>&nbsp;</p> <p><b><u>Why are we here?</u></b></p> <p>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.</p> <p>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?</p> <p>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…</p> <p>&nbsp;</p> <p><b><u>Hierarchical Modeling, the Adjacency List, and Recursive CTEs</u></b></p> <p>“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.”</p> <p>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. </p> <p>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.</p> <p>So why, then, do we play games and <a href="http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/swinging-from-tree-to-tree-using-ctes-part-1-adjacency-to-nested-sets.aspx" mce_href="http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/swinging-from-tree-to-tree-using-ctes-part-1-adjacency-to-nested-sets.aspx">attempt to use</a> 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.)</p> <p>More than likely—if you’re current with your T-SQL skills—you’ll write a query like this one:</p> <blockquote> <p><font face="Consolas">WITH <br>paths AS <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL</font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.')) <br>&nbsp;&nbsp;&nbsp; FROM paths AS p <br>&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw ON <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = p.EmployeeID <br>) <br>SELECT <br>&nbsp;&nbsp;&nbsp; * <br>FROM paths</font></p> </blockquote> <p>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 <a href="https://msdn.microsoft.com/en-us/library/ms190078.aspx" mce_href="https://msdn.microsoft.com/en-us/library/ms190078.aspx">Discard Results After Execution</a> option set.</p> <p>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.)</p> <p>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.</p> <p>Just like other queries, understanding the performance characteristics of this one requires a peek at the execution plan:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image_43C446A9.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image_43C446A9.png"><img src="http://sqlblog.com/blogs/adam_machanic/image_thumb_4CB3D8E8.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="219" width="1026"></a> </p> <p>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:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image4_4EF061A4.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image4_4EF061A4.png"><img src="http://sqlblog.com/blogs/adam_machanic/image4_thumb_079B0BB2.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="122" width="252"></a> </p> <p>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):</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image8_351C2B75.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image8_351C2B75.png"><img src="http://sqlblog.com/blogs/adam_machanic/image8_thumb_257C2064.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="338" width="232"></a> </p> <p>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.</p> <p>While these internals details are somewhat interesting, there are a few key facts that explain both performance (or lack thereof) and some implementation hints:</p> <ul> <li>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. </li> <li>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 <a href="http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx" mce_href="http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx">make_parallel()</a> function will fail to yield any kind of parallelism for this query. This greatly limits the ability for this plan to scale. </li> <li>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?</li> <li>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. </li> </ul> <p>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.</p> <p>&nbsp;</p> <p><b><u>hierarchy_navigator: The SQLCLR Recursive CTE</u></b></p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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 <a href="http://azure.microsoft.com/blog/2014/12/11/preview-available-for-next-generation-of-azure-sql-database/" mce_href="http://azure.microsoft.com/blog/2014/12/11/preview-available-for-next-generation-of-azure-sql-database/">SAFE CLR assemblies are supported in Azure SQL Database</a>. </p> <p>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:</p> <blockquote> <p><font face="Consolas">SELECT&nbsp; <br>&nbsp;&nbsp;&nbsp; i.EmployeeID,&nbsp; <br>&nbsp;&nbsp;&nbsp; i.FullPath <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(@@SPID) AS hi <br>&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw ON&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>) AS i <br>CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho</font></p> </blockquote> <p>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. </p> <p>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.</p> <p>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:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image_6BF9106C.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image_6BF9106C.png"><img src="http://sqlblog.com/blogs/adam_machanic/image_thumb_476782E8.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="456" width="704"></a> </p> <p>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.</p> <p>Once coded and deployed, at first blush everything seemed great. The query optimizer produced a perfect serial plan:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image15_34B28931.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image15_34B28931.png"><img src="http://sqlblog.com/blogs/adam_machanic/image15_thumb_1020FBAD.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="309" width="1026"></a> </p> <p>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.</p> <p>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…</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image24_048B3E6E.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image24_048B3E6E.png"><img src="http://sqlblog.com/blogs/adam_machanic/image24_thumb_5FF9B0E9.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="250" width="1026"></a> </p> <p>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.</p> <p>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.)</p> <p>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.)</p> <p>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.</p> <p>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:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) AS v <br>OUTER APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw ON&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho <br>) AS p <br>WHERE <br>&nbsp;&nbsp;&nbsp; p.EmployeeID IS NOT NULL</font></p> </blockquote> <p>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. </p> <p>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…</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image8_2676A0F2.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image8_2676A0F2.png"><img src="http://sqlblog.com/blogs/adam_machanic/image8_thumb_53F7C0B5.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="160" width="1026"></a> </p> <p>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.</p> <p>My solution for this was to create yet another function, this one to operate as part of the anchor:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) AS v <br>OUTER APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS JOIN EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeId, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw ON&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho <br>) AS p <br>WHERE <br>&nbsp;&nbsp;&nbsp; p.EmployeeID IS NOT NULL</font></p> </blockquote> <p>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.</p> <p>It almost worked!</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image17_64068F6C.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image17_64068F6C.png"><img src="http://sqlblog.com/blogs/adam_machanic/image17_thumb_5870D22D.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="348" width="1026"></a> </p> <p>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.</p> <p>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. </p> <p>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. </p> <p>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:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) AS v <br>OUTER APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS JOIN EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY (SELECT SUM(id) FROM (VALUES(hi.EmployeeID)) AS p0 (id)) AS p(y) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY (SELECT SUM(id) FROM (VALUES(hi.EmployeeID)) AS p0 (id)) AS q(y) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw ON <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; hi.EmployeeID = p.y <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND hi.EmployeeID = q.y <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND hi.EmployeeID = CHECKSUM(hi.EmployeeID) <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho <br>) AS p <br>WHERE <br>&nbsp;&nbsp;&nbsp; p.EmployeeID IS NOT NULL</font></p> </blockquote> <p>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:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image22_77B37900.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image22_77B37900.png"><img src="http://sqlblog.com/blogs/adam_machanic/image22_thumb_68136DEF.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="318" width="1027"></a> </p> <p>Unfortunately, zooming out a bit revealed that other parts of the plan were also impacted:</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image28_3543677B.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image28_3543677B.png"><img src="http://sqlblog.com/blogs/adam_machanic/image28_thumb_7BC05783.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="504" width="1026"></a> </p> <p>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! </p> <p>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.)</p> <p>After much gnashing of teeth and further refactoring of the query, I managed to bring things into a working form:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) AS v <br>CROSS APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; anchor.* <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT TOP(1) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha0 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS anchor </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; recursive.* <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUTER APPLY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide AS ehw <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS recursive <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT TOP(1) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ho0.* <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_outer(@@SPID, i.EmployeeID, i.FullPath) AS ho0 <br>&nbsp;&nbsp;&nbsp; ) AS ho <br>&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID IS NOT NULL <br>) AS p</font></p> <p>&nbsp;</p> </blockquote> <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.</p> <p>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.)</p> <p>The end result query plan (note that this used TF 8649):</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image_65010FFA.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image_65010FFA.png"><img src="http://sqlblog.com/blogs/adam_machanic/image_thumb_406F8276.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="331" width="1026"></a> </p> <p>&nbsp;</p> <p><b><u>Final Enhancements</u></b></p> <p>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.</p> <p>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:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) AS v <br>CROSS APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; anchor.* <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT TOP(1) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(v.x, @@SPID) AS ha0 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ManagerID IS NULL <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS anchor </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; recursive.* <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(v.x, @@SPID) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OUTER APPLY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM EmployeeHierarchyWide AS ehw <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS recursive <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES(dbo.hierarchy_outer_scalar(@@SPID, i.EmployeeID, i.FullPath)) <br>&nbsp;&nbsp;&nbsp; ) AS ho (value) <br>&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ho.value IS NOT NULL <br>) AS p</font></p> </blockquote> <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. </p> <p>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.</p> <p><a href="http://sqlblog.com/blogs/adam_machanic/image_34D9C537.png" mce_href="http://sqlblog.com/blogs/adam_machanic/image_34D9C537.png"><img src="http://sqlblog.com/blogs/adam_machanic/image_thumb_2224CB80.png" title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" alt="image" border="0" height="270" width="1026"></a> </p> <p>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:</p> <blockquote> <p><font face="Consolas">CREATE FUNCTION dbo.hierarchy_init_t() <br>RETURNS TABLE <br>AS <br>RETURN <br>( <br>&nbsp;&nbsp;&nbsp; SELECT DISTINCT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_simple_init(@@SPID) <br>) <br>GO </font></p> <p><font face="Consolas">CREATE FUNCTION dbo.hierarchy_anchor_t <br>( <br>&nbsp;&nbsp;&nbsp; @init_token INT <br>) <br>RETURNS TABLE <br>AS <br>RETURN <br>( <br>&nbsp;&nbsp;&nbsp; SELECT TOP(1) <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(@init_token, @@SPID) <br>) <br>GO </font></p> <p><font face="Consolas">CREATE FUNCTION dbo.hierarchy_inner_t <br>( <br>&nbsp;&nbsp;&nbsp; @init_token INT <br>) <br>RETURNS TABLE <br>AS <br>RETURN <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner(@init_token, @@SPID) <br>) <br>GO </font></p> <p><font face="Consolas">CREATE FUNCTION dbo.hierarchy_outer_t <br>( <br>&nbsp;&nbsp;&nbsp; @EmployeeID INT, <br>&nbsp;&nbsp;&nbsp; @FullPath NVARCHAR(900) <br>) <br>RETURNS TABLE <br>AS <br>RETURN <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 AS value <br>&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbo.hierarchy_outer_scalar(@@SPID, @EmployeeID, @FullPath) IS NOT NULL <br>) <br>GO</font></p> </blockquote> <p>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:</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; p.* <br>FROM dbo.hierarchy_init_t() AS v <br>CROSS APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor_t(v.x) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS JOIN EmployeeHierarchyWide AS ehw <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hi.FullPath, ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_inner_t(v.x) AS hi <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw WITH (FORCESEEK) ON <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hi.EmployeeID <br>&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp; CROSS APPLY dbo.hierarchy_outer_t(i.EmployeeID, i.FullPath) AS ho <br>) AS p</font></p> </blockquote> <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.</p> <p>The final refactoring step was to rename the functions and their outputs to be more general. </p> <p>I arrived at the following four T-SQL functions, which are what you will find in the archive attached to this post:</p> <ul> <li><b>hierarchy_init()</b> – This is the initializer function, and returns a column called “initialization_token.” It is backed by a CLR function called hierarchy_init_inner(). </li> <li><b>hierarchy_anchor(@initialization_token)</b> – The anchor function, this returns a column called “value.” It is backed by hierarchy_anchor_inner(). </li> <li><b>hierarchy_recursive(@initialization_token)</b> – 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(). </li> <li><b>hierarchy_enqueue(@id, @payload)</b> – This is the outer function. It returns a column called “value” and is backed by the scalar hierarchy_enqueue_inner(). </li> </ul> <p>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.</p> <blockquote> <p><font face="Consolas">SELECT <br>&nbsp;&nbsp;&nbsp; x.* <br>FROM dbo.make_parallel() <br>CROSS APPLY <br>( <br>&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p.* <br>&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_init() AS hi <br>&nbsp;&nbsp;&nbsp; CROSS APPLY <br>&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i.FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT('.', ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_anchor(hi.initialization_token) AS ha <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS JOIN EmployeeHierarchyWide AS ehw <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID IS NULL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL </font></p> <p><font face="Consolas">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.EmployeeID, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONVERT(VARCHAR(900), CONCAT(hr.payload, ehw.EmployeeID, '.')) AS FullPath <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM dbo.hierarchy_recursive(hi.initialization_token) AS hr <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN EmployeeHierarchyWide AS ehw WITH (FORCESEEK) ON <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ehw.ManagerID = hr.id <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS i <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY dbo.hierarchy_enqueue(i.EmployeeID, i.FullPath) AS he <br>&nbsp;&nbsp;&nbsp; ) AS p <br>) AS x</font></p> </blockquote> <p>&nbsp;</p> <p><b><u>Final Notes and Script Attachments</u></b></p> <p><b><u></u></b></p> <p><b><u></u></b></p> <p>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.</p> <p>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. </p> <p>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. </p> <p>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. </p> <p>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.</p> <p>Enjoy, thanks for reading, and as always let me know if you have any questions!</p> <p>&nbsp;</p> <p><b><u>Acknowledgement</u></b></p> <p>Huge thanks to <a href="http://blog.boyet.com/" mce_href="http://blog.boyet.com/">Julian Bucknall</a>, who kindly fielded my questions as I was writing my lock-free queue implementation.</p><p>&nbsp;</p><p><u><b>Update, 2015-10-04</b></u></p><p>I found and fixed a minor bug in the assembly. The attachment has been updated with the change. <br></p>SQLRally Nordic and SQLRally Amsterdam: Wrap Up and Demoshttp://sqlblog.com/blogs/adam_machanic/archive/2013/11/11/sqlrally-nordic-and-sqlrally-amsterdam-wrap-up-and-demos.aspxTue, 12 Nov 2013 00:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51688Adam Machanic<p><b>First and foremost</b>: Huge thanks, and huge apologies, to everyone who attended my sessions at these events. I promised to post materials last week, and there is no good excuse for tardiness. My dog did not eat my computer. I don't have a dog. And if I did, she would far prefer a nice rib eye to a hard chunk of plastic.</p><p>Now, on to the purpose of this post...</p><p><b>Last week I was lucky enough</b> to have a first visit to each of two amazing cities, Stockholm and Amsterdam. Both cities, <a href="http://sqlblog.com/blogs/adam_machanic/archive/2013/10/04/query-performance-sessions-in-stockholm-and-amsterdam.aspx" mce_href="http://sqlblog.com/blogs/adam_machanic/archive/2013/10/04/query-performance-sessions-in-stockholm-and-amsterdam.aspx">as mentioned previously on my blog</a>, hosted SQLRally events, and I did a precon plus two talks at each event.</p><p>The events in both cities were well done and the audiences seemed quite sharp. Very nice events at which to be a speaker! <b>I hope to return to both areas very, very soon. </b>(Hint, hint, event organizers!)<br></p><p>Precon attendees: You should already know where to get your slides and demos.</p><p><b>Breakout session attendees</b>: Find the demos for both of my talks attached here.</p><p>Thanks again to everyone who I met and/or taught. It was really a fantastic week. Skål and/or proost -- depending on your disposition!<br></p>Query Performance Sessions in Stockholm and Amsterdamhttp://sqlblog.com/blogs/adam_machanic/archive/2013/10/04/query-performance-sessions-in-stockholm-and-amsterdam.aspxFri, 04 Oct 2013 13:15:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51210Adam Machanic<p>As <a href="http://sqlblog.com/blogs/adam_machanic/archive/2013/09/20/hey-amsterdam-and-stockholm-just-say-no-to-guessing-about-performance.aspx" mce_href="http://sqlblog.com/blogs/adam_machanic/archive/2013/09/20/hey-amsterdam-and-stockholm-just-say-no-to-guessing-about-performance.aspx">previously mentioned</a>, I'll be delivering my full-day "No More Guessing" performance troubleshooting seminar at both the <a href="http://www.sqlpass.org/sqlrally/2013/nordic/" mce_href="http://www.sqlpass.org/sqlrally/2013/nordic/">Stockholm</a> and <a href="http://www.sqlpass.org/sqlrally/2013/amsterdam/" mce_href="http://www.sqlpass.org/sqlrally/2013/amsterdam/">Amsterdam</a> SQLRally events. In addition to the seminar, I'll be doing two breakout sessions at each event. <br></p><p>It's going to be a busy week! But luckily for me, I'm doing the same things in each city:</p><p>&nbsp;</p><p><b>Using Your Brain to Beat SQL Server</b></p><p>This session is a joint effort with one of the world's greatest SQL superheroes, <a href="http://blog.kejser.org/" mce_href="http://blog.kejser.org/">Thomas Kejser</a>. The session is all about query optimizer deficiencies and how to win the battle for excellent performance by applying your own intelligence instead of blindly trusting that SQL Server will do the right thing. I think this is going to be a really fun and useful session.</p><p>&nbsp;</p><p><b>Data, Faster: SQL Server Performance Techniques with SQLCLR</b> </p><p>Remember when SQLCLR was first put into SQL Server and everyone was wondering what they should use it for? I found my answer: raw performance. In this session I'll teach you a SQLCLR pattern that can help reduce the run time of your heaviest and most logic-filled queries, often by an order of magnitude or more.</p><p>&nbsp;</p><p><b>Looking forward to seeing you in Europe next month!</b> <br></p>TechEd 2013: Demos for &quot;Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR&quot;http://sqlblog.com/blogs/adam_machanic/archive/2013/06/04/teched-2013-demos-for-data-faster-microsoft-sql-server-performance-techniques-with-sqlclr.aspxTue, 04 Jun 2013 16:23:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49344Adam Machanic<p>Today at TechEd in New Orleans I delivered a breakout session entitled "<a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B404">Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR</a>." </p><p>This session covered a number of techniques for using SQLCLR as a query tuning tool, especially for big, ugly, and heavily analytical queries.</p><p><b>Thank you so much for all who attended and took the time to evaluate the session.</b> For those of you who weren't there, the video will (apparently) be posted soon on the link above.</p><p>The full set of demo material is attached to this post. Leave a comment if you have any questions for me.</p><p><b>Enjoy, and may your queries be ultra-speedy!</b><br></p>SQLCLR Performance Session at TechEd UShttp://sqlblog.com/blogs/adam_machanic/archive/2013/05/08/sqlclr-performance-session-at-teched-us.aspxWed, 08 May 2013 16:14:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48998Adam Machanic<p>I am super-excited to visit New Orleans next month for Microsoft TechEd; it will be my sixth time speaking at the show.</p><p><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B404">My session</a> takes an in-depth look at some of the techniques I've developed for using SQLCLR modules -- and some of the great performance gains I've been able to achieve.</p><p>Hope to see you in NOLA! If you're not attending the show, the video will be available on demand a few days after I give the talk.<br></p>Performance impact: Try/Catch blocks in SQLCLR – a follow uphttp://sqlblog.com/blogs/linchi_shea/archive/2011/07/07/performance-impact-try-catch-blocks-in-sqlclr-a-follow-up.aspxFri, 08 Jul 2011 03:48:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36715Linchi Shea<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>My </FONT><A href="http://sqlblog.com/blogs/linchi_shea/archive/2011/07/06/performance-impact-too-many-try-catch-blocks-may-be-harmful-in-sqlclr.aspx"><FONT color=#606420 size=3>previous post</FONT></A><FONT size=3> showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. To see if I would run into similar behavior, I wrote a simple C# program for a quick test. To simulate&nbsp;the concurrent exception-handling load, the test program spawns 10 background threads, each calling the following method nonstop (the complete program is listed at the end of this post):<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>static void TryCatch()<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>int c = 0;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 200000; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>try { c= i/c; } catch (Exception) { c = 1;}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>} <o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>Note that variable c is assigned value 0, thus forcing a divide-by-zero exception, and this exception is handled 200,000 times in a loop.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>The elapsed time of the method is measured both when there is no additional background threads and when 10 additional threads are running.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>On my old two-core 2GB PC workstation, the following output from the program is typical among many runs:<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">C:\junk&gt;test2.exe<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() without any background thread = 15617968 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() without any background thread = 15559616 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() without any background thread = 15566064 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() without any background thread = 17472496 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() without any background thread = 15782952 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 0 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 1 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 2 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 3 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 4 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 5 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 6 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 7 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 8 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">Thread 9 Called TryCatch() 1000 times.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17498336 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17535984 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17664424 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17515200 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17465312 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17498432 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17508656 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">TryCatch() with 10 background threads = 17710856 ticks<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">^C<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>At least for this test, the adverse concurrency impact that we saw with SQLCLR--and reported in </FONT><A href="http://sqlblog.com/blogs/linchi_shea/archive/2011/07/06/performance-impact-too-many-try-catch-blocks-may-be-harmful-in-sqlclr.aspx"><FONT color=#606420 size=3>the previous post</FONT></A><FONT size=3>--is <U>not</U> observed. <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>Although it’s not strictly an apple-to-apple comparison between this test without SQLCLR and that described in the previous post with SQLCLR, the end user experience is so different that it calls into question why SQLCLR does not seem to handle many concurrent exceptions as gracefully. I don’t have an answer. <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>I have absolutely no knowledge of how SQLCLR works internally, and can’t explain the concurrency behavior observed in the previous post.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>By the way, when I set variable c to 1 in the TryCatch() method, thus avoiding the exception,&nbsp;its concurrency impact (or the lack of)&nbsp;did not change much, if at all.</FONT></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3></FONT></o:p></SPAN>&nbsp;</P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><FONT size=3>Anyway, here is the test program. For the output presented above, the program was compiled with .NET Framework 3.5.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">using System;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">using System.Diagnostics;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">using System.Threading;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">public partial class Test<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>public static void Main()<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Stopwatch stop_watch = new Stopwatch();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>// warming up a bit<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 5; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>TryCatch();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp; </SPAN>// measure the elaped time without any additional background threads<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 5; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Reset();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Start();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>TryCatch();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Stop();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Console.WriteLine("TryCatch() without any background thread = {0} ticks",<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="mso-spacerun:yes;">&nbsp;</SPAN>stop_watch.ElapsedTicks);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread.Sleep(2000);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread[] user_threads = new Thread[10];<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 10; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>user_threads[i] = new Thread(new ThreadStart(StartTryCatch));<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>user_threads[i].Name = i.ToString();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>user_threads[i].IsBackground = true;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>user_threads[i].Start();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread.Sleep(10);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread.Sleep(5000);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>// now measure the elaped time again with 10 additiona threads running<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 20; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Reset();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Start();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>TryCatch();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.Stop();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Console.WriteLine("TryCatch() with 10 background threads = {0} ticks",<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>stop_watch.ElapsedTicks);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread.Sleep(2000);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>// this will never be reached. You have to Cltr-C to stop the program<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for (int i = 0; i &lt; 10; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>if (user_threads[i] != null)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>user_threads[i].Join();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>static void StartTryCatch()<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>int i = 0;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>while (true)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>if (i % 1000 == 0)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Console.WriteLine("Thread {0} Called TryCatch() 1000 times.", Thread.CurrentThread.Name);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>TryCatch();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Thread.Sleep(10);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>i++;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>static void TryCatch()<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>int c = 0;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>for(int i = 0; i &lt; 200000; i++)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>try { c= i/c; } catch (Exception) { c = 1;}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="BACKGROUND:#e6e6e6;MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';">};<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-FAMILY:Arial;"><o:p><FONT size=3>&nbsp;</FONT></o:p></SPAN></P>Performance impact: too many try/catch blocks may be harmful in SQLCLRhttp://sqlblog.com/blogs/linchi_shea/archive/2011/07/06/performance-impact-too-many-try-catch-blocks-may-be-harmful-in-sqlclr.aspxWed, 06 Jul 2011 04:40:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36663Linchi Shea<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">What is surprising is the extent to which you may experience severe performance degradation when multiple users are executing a piece of SQLCLR code (e.g. calling a SQLCLR table valued function) that contains many try/catch blocks. In other words, there may significant and adverse concurrency implication if you use too many try/catch blocks in your SQLCLR code.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">Here is an example to prove it!<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">I have implemented two SQLCLR table-valued functions: tvf_GetDataWithTryCatch() and tvf_GetDataWithoutTryCatch(). These two functions return exactly the same resultset: 2000 rows with 100 columns of integers. The functions simulate the case where the integer values are out of bound (i.e. greater than the maximum value allowed for the integer datatype, which is SqlInt32.MaxValue in the attached C# code), and when that happens, the functions return NULL instead.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">There are many ways to check whether a value is equal to or greater than SqlInt32.MaxValue. As the name implies, tvf_GetDataWithTryCatch() implements the check in a try block and returns NULL in the corresponding catch block. The tvf_GetDataWithoutTryCatch() function, on the other hand, implements the check with a C# conditional operator (?:).<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><B style="mso-bidi-font-weight:normal;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">When there is no background concurrent load<o:p></o:p></SPAN></B></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><B style="mso-bidi-font-weight:normal;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></B></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">The tvf_GetDataWith TryCatch() function is expected to be slower than the tvf_GetDataWithoutTryCatch() function because of the inherent overhead with a try/catch block, and tests show that is indeed the case. On my rather antiquated PC with 2GB of physical memory, the following query<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;COLOR:blue;FONT-FAMILY:Arial;mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> dbo<SPAN style="COLOR:gray;">.</SPAN>[tvf_GetDataWithTryCatch]<SPAN style="COLOR:gray;">()</SPAN></SPAN><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">returns all the 2000 rows in about 8 seconds, whereas this query<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;COLOR:blue;FONT-FAMILY:Arial;mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> [dbo]<SPAN style="COLOR:gray;">.</SPAN>[tvf_GetDataWithoutTryCatch]<SPAN style="COLOR:gray;">()</SPAN></SPAN><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">returns in about<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>1 second. Consistently, the function doing 200,000 try/catch’es is about eight times slower than the function doing 200,000 conditional operations. So far so good! No surprises!<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><B style="mso-bidi-font-weight:normal;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">When there is background concurrent load<o:p></o:p></SPAN></B></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">When I have 10 users running tvf_GetDataWithoutTryCatch() nonstop against the same SQL Server 2008 instance in the background, the tvf_GetDataWithoutTryCatch() returns in about 2 seconds consistently. So running additional load of tvf_GetDataWithoutTryCatch() does slow it down a bit, but not a lot.</SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">Now, when I have 10 users running tvf_GetDataWithTryCatch() nonstop in the background against the same SQL Server 2008 instance, the performance of tvf_GetDataWithTryCatch() degrades dramatically. This time it takes about 76 seconds to return all the 2000 rows. That is about <STRONG>10 times</STRONG> slower than it is when there is no concurrent load. And it is about <STRONG>38 times</STRONG> slower than tvf_GetDataWithoutTryCatch() under the same 10-user background concurrent load. Note that tvf_GetDataWithoutTryCatch() returns in about 2 seconds regardless whether the 10-user background load is calling tvf_GetDataWithTryCatch() or calling tvf_GetDataWithoutTryCatch().<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">If you think 38 times slower is bad, in real production environments I have seen far worse&nbsp;concrrency&nbsp;impact&nbsp;as a result of too many try/catch blocks.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;">With the attached C# code, you can run the tests yourself to see the behavior. Your results may differ in some details, but you should see a similar pattern. If not, post back a comment and let us know.<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;"><SPAN style="FONT-SIZE:11pt;FONT-FAMILY:Arial;"><o:p>&nbsp;</o:p></SPAN></P>Performance, Discounts, and an Excuse to Visit New York Cityhttp://sqlblog.com/blogs/adam_machanic/archive/2011/06/02/performance-discounts-and-an-excuse-to-visit-new-york-city.aspxThu, 02 Jun 2011 14:21:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36040Adam Machanic<p>A couple of weeks ago <a href="http://sqlblog.com/blogs/adam_machanic/archive/2011/05/18/two-day-of-advanced-performance-techniques-july-14-15-new-york-city.aspx">I announced</a> a two-day <a href="http://dataeducation.com/taking-it-to-the-extreme-sqlclr-and-parallelism-course-outline/">advanced performance seminar</a> in New York City, which will be delivered in July. This seminar will cover SQLCLR and parallelism techniques to help you <b>take performance well beyond the levels that typical tuning exercises yield</b>. Check out the links for more details, including a full outline.<br></p><p>Thanks to the great response so far, we have decided to <b>extend the early registration discount</b> for a few more days. You have <b>until the end of the day tomorrow, June 3</b>, to take advantage of the <b>$100 savings</b> off of the $1050 course fee. To get the discount, use the code EARLYBIRD on the <a href="http://www.eventbrite.com/event/1595370797?ref=ebtn">registration page</a>.</p><p>Finally, I would like to point out that one of the main reasons that I chose New York City for this course is that <b>it is by far my favorite city to visit</b>. I just noticed yesterday that <a href="http://www.nycgo.com/thirdnight">a bunch of hotels are offering a free third night this summer</a>--so I hope that you'll be able to&nbsp;come for the course and then stay an extra night or two to enjoy the city. Note that <b>we also have a hotel discount available to course attendees</b>, unrelated to the third night offer. Let me know if you would like more information on that.</p><p>Hope to see you in New York next month! <br></p>Two Days of Advanced Performance Techniques - July 14-15, New York Cityhttp://sqlblog.com/blogs/adam_machanic/archive/2011/05/18/two-day-of-advanced-performance-techniques-july-14-15-new-york-city.aspxWed, 18 May 2011 16:19:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35709Adam Machanic<p>I am pleased to announce that I will be delivering two days of training in New York City, July 14 and 15.</p><p><b>This seminar focuses on achieving "next-level" performance--going beyond that which you can gain via normal tuning methodologies</b>. The vehicles for this performance improvement are two technologies that I've been pushing on this blog and in other venues for a long time: SQLCLR and parallelism. The seminar will be based on the in-depth materials that I used for my full-day sessions PASS conference in 2009 and 2010. These seminars were both quite well-received, but <b>I have tweaked and tuned the content</b> to make it even better and more focused on the bottom line goal of achieving maximum performance.</p><p><a href="http://dataeducation.com/taking-it-to-the-extreme-sqlclr-and-parallelism-course-outline/"><b>Full information on the seminar is available on the Data Education web site</b></a>. There is also an early bird discount currently in effect. Use the discount code "EARLYBIRD" to save $100 on the $1050 registration fee.</p><p>&nbsp; <br></p><p>I would like to take this opportunity to mention that <a href="http://dataeducation.com/">Data Education</a> is a new training venture that I've recently launched. This will be the company's second public training event (our first featured Kalen Delaney in the Boston area). The company is an evolution of Boston SQL Training, a company that I started a couple of years ago with the goal of bringing extremely high-quality SQL Server training events to the Boston area. <b>The new name, Data Education, reflects our desire to focus beyond Boston and on a broader technology spectrum</b>. We plan to eventually move into training on data-related programming (Entity Framework and similar), other DBMS platforms, NoSQL technologies, and wherever else the database industry moves.</p><p>Currently, aside from my course in New York we've announced an <a href="http://dataeducation.com/applied-ssas-2008-and-powerpivot-course-outline/"><b>Analysis Services and PowerPivot course featuring Teo Lachev</b></a>, which will take place in the Boston area September 19-23. Several other courses will be announced shortly, so stay tuned and consider following us on Twitter (<a href="http://twitter.com/#!/dataeducation">@DataEducation</a>).</p><p>If you've read this far, <b>I would greatly appreciate your taking part in a quick and informal poll</b>: in the comments section below, please let me know what geographic location would be interesting to you for an advanced SQL Server course, and what topic areas you're not seeing enough of. </p><p>&nbsp; <br></p><p><b>Thanks, everyone, and I'm looking forward to seeing you in New York!</b><br></p>T-SQL Tuesday #001: Exploring &quot;Fuzzy&quot; Interval Islands Using SQLCLRhttp://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspxTue, 08 Dec 2009 19:13:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:19621Adam Machanic<p>When working with time intervals, we often want to ask a couple of basic questions: </p>
<ul>
<li>Which time periods are not covered by our intervals? These are known as "gaps".</li>
<li>What are the time ranges that we are fully covering? These are known as "islands".</li>
</ul>
<p>If you're unfamiliar with "gaps" and "islands" I highly recommend reading some of Itzik Ben-Gan's recent work in SQL Server Magazine. He's had a great series going on the topic. But one thing that he hasn't found a good T-SQL solution for is a problem that I call "fuzzy islands." </p>
<p><b>When is an island fuzzy?</b> When it doesn't necessarily have a fixed end time. For an example of this, consider a store, selling a number of products. Management might want to see a report showing when each product was selling. This is, effectively, an island question. The goal is to find all of the covered ranges during which sales occurred. But running such a report, you might find that way too much data is returned. A given product may have sold units on Monday, Tuesday, and Thursday, but for some reason no one bought one on Wednesday. Creating a new island every time there is a small gap will create a 300-page report where a 1-page dashboard might suffice--not a good user experience, nor a good way of representing the data. The solution? Introduce a bit of fuzziness--a rule that says, for instance, that a gap is only a gap if it's longer than 7 days.<br></p>
<p><b>Answering the fuzzy islands question </b>is not a very difficult thing to do in T-SQL. The basic algorithm follows:</p>
<ol>
<li>Find all of the "start" dates or times. These are simply those dates or times for which a previous date or time in the fuzzy interval does not exist. So if a row is dated 2009-12-08 and our fuzzy granularity is 7 days, we know we have a start date if there is no other covered data from the end of November.</li>
<li>For each start date identified, find the minimum date greater than the start date. This is done by looking ahead rather than behind, so if our date is 2009-12-08 and we have a granularity of 7 days, we'll look forward until December 15th.</li>
<li>Optionally, add the fuzzy factor to the end date. This is something that I think is a good idea, as it introduces the concept of an "active" interval--a period over which, for example, a product is considered to have been selling. The interval shouldn't necessarily terminate the day that the last sale occurred. But of course this depends on the situation in question.</li>
</ol>
<p>The following query produces a fuzzy islands report for each product in the AdventureWorks (or AdventureWorks2008) Production.TransactionHistory table. You can modify the @active_interval variable to tweak the fuzziness and change the output. <br></p><blockquote>
<pre>--Find all "active" product time ranges, meaning that the product<br>--has sold within the previous 7 days<br>DECLARE @active_interval INT = 7<br><br>SELECT DISTINCT<br> t_s.ProductID,<br> t_s.TransactionDate AS StartDate,<br> DATEADD<br> (<br> dd,<br> @active_interval,<br> (<br> SELECT<br> MIN(t_e.TransactionDate)<br> FROM Production.TransactionHistory AS t_e<br> WHERE<br> t_e.ProductID = t_s.ProductID<br> AND t_e.TransactionDate &gt;= t_s.TransactionDate<br> AND NOT EXISTS<br> (<br> SELECT *<br> FROM Production.TransactionHistory AS t_ae<br> WHERE<br> t_ae.ProductID = t_s.ProductID<br> AND t_ae.TransactionDate BETWEEN <br> DATEADD(dd, 1, t_e.TransactionDate) <br> AND DATEADD(dd, @active_interval, t_e.TransactionDate)<br> )<br> )<br> ) AS EndDate<br>FROM <br>(<br> SELECT DISTINCT<br> ProductID,<br> TransactionDate<br> FROM Production.TransactionHistory<br>) AS t_s<br>WHERE<br> NOT EXISTS<br> (<br> SELECT *<br> FROM Production.TransactionHistory AS t_ps<br> WHERE<br> t_ps.ProductID = t_s.ProductID<br> AND t_ps.TransactionDate BETWEEN <br> DATEADD(dd, -@active_interval, t_s.TransactionDate) <br> AND DATEADD(dd, -1, t_s.TransactionDate)<br> )<br>ORDER BY<br> ProductID,<br> StartDate<br>GO</pre>
</blockquote>
<p>Running this query you'll find that it works... But the results are returned a bit more slowly than we might desire--15 to 16 seconds on my end. Looking at the query plan, the reason for this becomes quite obvious: Lots and lots of table scans. How can we eliminate all of the overhead?</p>
<p><b>SQLCLR to the rescue.</b> The best way to solve this problem--at least until the SQL Server team adds proper OVER clause support (LAG and LEAD, specifically)--is to use a cursor algorithm. We could do this in a T-SQL cursor, but why bother? Cursor logic in SQLCLR is much, much faster. </p>
<p>To solve the problem, I implemented an enumerator, called active_products_enumerator. The enumerator is initialized using a SqlDataReader and an "active interval" -- the number of days we're allowing for fuzziness. The DataReader is expected to return rows ordered by ProductID and TransactionDate. The enumeration process uses the following algorithm:</p>
<ol>
<li>If the current ProductID is not the same as the previous ProductID, return an end date for the previous interval and start a new one</li>
<li>If the current period date is greater than the previous period date plus the active interval, return an end date for the previous interval and start a new one</li>
<li>Otherwise, continue</li>
</ol>
<p>Following is the MoveNext method for the enumerator (the complete code is attached to this post so that you can run it on your end without my bombarding you with a gigantic code-filled post):</p>
<blockquote><pre><p>public bool MoveNext()<br>{<br>&nbsp;&nbsp;&nbsp; try<br>&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_results = null;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; while (r.Read())<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new_ProductID = r.GetSqlInt32(0);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new_period_date = r.GetDateTime(1);<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (new_ProductID != ProductID)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (ProductID != 0)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_results = new results(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductID,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period_plus_interval);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductID = new_ProductID;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate = new_period_date;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period_plus_interval = new_period_date.AddDays(activeInterval);<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (current_results != null)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return (true);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (period_plus_interval &lt; new_period_date)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_results = new results(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductID,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period_plus_interval);<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate = new_period_date;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period_plus_interval = new_period_date.AddDays(activeInterval);<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (current_results != null)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return (true);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //return the last row of data<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (ProductID != 0)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_results = new results(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductID,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period_plus_interval);<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //set this to 0 so we don't return another row<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductID = 0;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (current_results != null)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return (true);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.Dispose();<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return (false);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp; catch<br>&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.Dispose();<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; throw;<br>&nbsp;&nbsp;&nbsp; }<br>}</p></pre></blockquote>
<p>Put into a table-valued function, as the attached code does, this algorithm will return the same data as the T-SQL query in under a third of a second on my end--<span style="font-weight:bold;">around 45 times faster than the T-SQL version</span>.</p><p>Note that I've played some games with a loopback connection to get this whole thing to work. That's a topic for a future blog post, so stay tuned. In the meantime, please realize that you'll have to catalog the assembly with EXTERNAL_ACCESS permission to make this happen.</p><p>This post was created for T-SQL Tuesday, the revolving SQL Server blog party, hosted this month by... <a href="http://sqlblog.com/blogs/adam_machanic/archive/2009/11/30/invitation-to-participate-in-t-sql-tuesday-001-date-time-tricks.aspx">me</a>. Enjoy! <br></p>