Search results matching tags 'performance' and 'Query Tuning'

Query Tuning Mastery at PASS Summit 2012: The Video

Adam Machanic — Tue, 13 Nov 2012 14:49:00 GMT

An especially clever community member was kind enough to reverse-engineer the video stream for me, and came up with a direct link to the PASS TV video stream for my Query Tuning Mastery: The Art and Science of Manhandling Parallelism talk, delivered at the PASS Summit last Thursday. I'm not sure how long this link will work, but I'd like to share it for my readers who were unable to see it in person or live on the stream.

Start here.

Skip past the keynote, to the 149 minute mark.

Enjoy!

Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Mon, 12 Nov 2012 04:00:00 GMT

For the second year in a row, I was asked to deliver a 500-level "Query Tuning Mastery" talk in room 6E of the Washington State Convention Center, for the PASS Summit. (Here's some information about last year's talk, on workspace memory.) And for the second year in a row, I had to deliver said talk at 10:15 in the morning, in a room used as overflow for the keynote, following a keynote speaker that didn't stop speaking on time. Frustrating!

Last Thursday, after very, very quickly setting up and getting sound and video checks, the rest of the talk went surprisingly smoothly. My deck--a brand new version created specifically for PASS--helped me get across the message I wanted to communicate, my demos ran without any failure, and my jokes didn't drive too many people out of the room before the end of the talk. I even received a round of applause when I managed to take a 26 minute query plan and, using a few query rewrites, deliver the same exact data in 9 seconds. That, I have to say, was pretty cool.

Here's the abstract for the session:

Query Tuning Mastery: The Art and Science of Manhandling Parallelism
As a database developer, your job boils down to one word: performance. In today's multi-core-driven world, query performance is very much determined by how well you're taking advantage of the processing power at your disposal. Are your big queries using every clock tick, or are they lagging behind? And if your queries are already parallel, can they be rewritten for even greater speed?

In this session, you'll learn to take full advantage of SQL Server query parallelism. After a terminology review and technology refresher, the session will go deep, covering T-SQL patterns that allow certain queries to scale almost linearly across your multi-core CPUs. You'll see when and why the optimizer makes a parallel plan choice and how to impact the decision. Along the way, you’ll manipulate costs and row goals, challenge generally accepted tuning practices, and take complete control of your parallel queries.

Since the talk was being broadcast live on "PASS TV," I had Paul White join me at the front of the room to moderate questions delivered via Twitter. This worked out reasonably well and I hope to do something similar in the future. Huge thanks to Paul for helping out -- and for giving me a really ugly scowl when one of my jokes fell totally flat.

Demos for the talk are attached. Let me know if you have any questions.

Thanks again to everyone who watched, either in person or at home. I had a blast. Hope you enjoyed it even half as much as I did!

PASS Summit 2011 - Zen and the Art of Workspace Memory - Demos

Adam Machanic — Mon, 17 Oct 2011 00:23:00 GMT

What a rush. Standing on the stage in an almost-full 1,000-person room, I (very) momentarily wondered what I'd been thinking when I submitted a 500-level talk for the biggest SQL Server conference in the world. But despite a rough start--my laptop crashed and I had to reboot it two minutes into the talk--I found my rhythm and the entire 90 minutes went by in a flash. I wish I'd been able to take 90 more!

The scene? PASS Summit 2011. Friday, October 14, 10:15 a.m. (Room 6E, to be exact.) The last day of one of the best PASS Summits I've had the pleasure of attending.

The topic? A fairly obscure area of SQL Server, called workspace memory. Here's the abstract for the talk:

Query Tuning Mastery: Zen and the Art of Workspace Memory
As SQL Server professionals, we often think of memory in vague, instance-level terms: buffer pool, procedure cache, Virtual Address Space, and so on. But certain tasks require a more in-depth focus, and query tuning is one of them. Large, complex queries need memory in which to work--workspace memory--and understanding the how's, when's, and why's of this memory can help you create queries that run in seconds rather than minutes. This session will teach you how to guide the query processor to grant enough memory for top performance, while also keeping things balanced for the sake of concurrency. You will learn advanced monitoring techniques, expert-level application of specialized query hints, and the memory internals needed to put it all together. If you work with large queries and are serious about achieving scalability and consistently great performance, you owe it to yourself to attend this session.

If you were in the audience, I thank you for choosing my session over the many others that were running concurrently. I had a great time, and I hope you did too.

The demos for the talk are attached to this post. Apologies, but I am not sharing the deck at this time as I'm going to be integrating it into a larger course that I hope to start delivering next year. (Through Data Education, naturally!)

Enjoy! And as always, let me know in the comments if you have any questions.

Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

jchang — Mon, 22 Jun 2009 04:25:00 GMT

Everyone should know by now how really useful the DMVs dm_exec_query_stats, and dm_db_index_usage_stats and the associated DMFs for obtaining the SQL and XML plan: dm_exec_sql_text, dm_exec_query_plan, and dm_exec_text_query_plan. Of course it has been explained that dm_exec_query_stats is not a replacement for SQL Server Profiler and SQL Trace. The DMV is only reliable if execution plans are not frequently evicted from the procedure cache and if SQL is not frequently recompiled.

Still the DMV dm_exec_query_stats is popular because the results are available with a simple query. The complexity of setting up a trace, making sure it is not adversely impacting server performance, making sure space is available, and most of all, the effort of parsing the trace are all avoided.

So what do we do next? From the top query statistics we can start looking at the SQL and the execution plans. One minor annoyance is that if we use CROSS APPLY to dm_exec_query_plan, each XML plan has to be saved individually. This can be annoying if one has to remote into to a PC in the server room and this connection is slow.
There is much information in the XML plan that would be helpful if we could see it in a grid along with the execution statistics.

From the index usage statistics, we can eliminate unused indexes. There might also be indexes that are infrequently used. Sometimes it is obvious that certain indexes can be consolidated. Other times, it is necessary to examine the execution plan to determine if another index is sufficient. The XML plan analysis cross-references in which plans each index is used. All of this is tedious work which can be automated. So this is the purpose of the SQLExecStats tool.

A few people have tried it on production systems. There are still bugs, and feature requests, but hopefully I can turn this into something useful over time.

lets use this url for the latest build rather than changing the url for each build

http://www.qdpma.com/tools/ExecStats.zip

The above build is now 20090628. The (poor excuse for) documention is also online http://www.qdpma.com/SQLExecStats.html

Feedback is welcome, in particular, send me the output files so I can try to figure out if this is collecting the right information. Support will be based on availability.

Note: SQL Server 2008 sp1 fix now correctly reports parallel execution plan total_worker_time (CPU). It is not fixed on SQL Server 2005 sp3, latest cu?

Limitations:

European Localization The XML plan is stored as a string, with numbers in US-en format. My program extracts plan cost and estimated row numbers from the XML plan. The previous version did not specify any localization, but I think the default is the host environment. Hence the code double.Format(value) is interpreted as whatever the local environment is instead of US-en. The builds since 20090623 specifies the US-en format in most places.

If some one with European number formats could run this program and send me the output, I would appreciate it.

Bug fixes, features etc

2009-06-22: The password now should display *, I don't do security work, and it shows. Some formatting was changed to right align, comma thousands separator, Very large numbers in Excel cannot be changed, 4 bytes integers are represented as numbers, larger numbers are represented as text.
I will do more formatting changes later

2009-06-28

The error described by Zen occurs with case sensitive collations. Apparently one SQL query I issued from the executable was not a case correct with the columns I declared on the receiving side, and this is sufficient to cause the data table load to fail.

The current build is really an intermediate build. I am adding the ability to collect table and index information for multiple databases and not everything is consistent yet.

Be careful on trying to collect for too many databases, and too many execution plans, there are checks for over size yet.

2009-07-09

I am still in the middle of changing the tool to have the option of collecting table and index info from all databases. So some of the UI is not entirely consistent. However, all bug fixes are in the new version.

Previously there were SQL queries that were not case correct with client-side table definitions, and when the default collation is case sensitive, an error occurred. This should be corrected.

Also, previously I issued DBCC SHOW_STATISTICS([schema.table],[stat])

which can generate an error if there is a hyphen '-' in the table or schema. I changed this to

('schema.table',[stat])
I am not sure if this is the most safe format, should it be: ('schema.table','stat')?
anyways, go to the main url, http://www.qdpma.com/

look for SQL Exec Stats Build 2009-07-09

http://www.qdpma.com/tools/ExecStats_20090709.zip

Why Logical IO is a poor performance metric

jchang — Wed, 10 Sep 2008 16:08:00 GMT

Many years ago, I attended a conference where a very highly respected Oracle performance expert advocated the use of logical IO count as a performance metric. The argument was that a logical IO is a fairly substantial operation relative to others. I suppose that detailed logical IO was already collected supported this. So why would logical IO be advocated over CPU? On the Windows side, many people have probably noticed that Profiler frequently shows 0 CPU for low cost queries.

(The resolution of Profiler in SQL Server 2000 was some approximate integer multiple of 10 ms for the single processor kernel and 15.625 ms for multi-processor kernel. It does turn out that averaging many calls yields a reasonably accurate CPU measure, possibly to the range of 1 msec.)

I am not sure what the situation is for Oracle, which runs on many platforms, UNIX, Windows and other OS. It cannot be easy collecting performance counters whose meaning is uniform across all the different platforms.

Anyways, the argument was convincing enough that I thought it warranted investigation on the SQL Server side. By comparing logical IO and average CPU (measured in full saturation load tests where SQL Server is driven to near 100% CPU), it was quickly apparent that Logical IO and true cost had no meaningful relation.

Consider a table for which the indexes have depth 4, so that there is a root level, 2 intermediate levels and the leaf level. The index seek for a single index key (that is, no key lookup) would generate 4 logical IO (LIO). Now consider if a key lookup is required. If the table has a clustered index also of depth 4, each key lookup generates 4 LIO. If the table were a heap, each key lookup generates 1 LIO. In actuality, the key lookup to a heap is about 20-30% less expensive than a key lookup to a clustered index, not anywhere close to the 4:1 LIO ratio. Now consider a loop join between two tables. The index seek to outer source generates 4-5 LIO, 4 for the index depth, and possibly one or more IO for additional rows that do not fit in the same leaf level page. Each index seek to the inner source generates 4 LIO as the index is traversed. For a loop join involving 100 rows in a one-to-one join, (no key lookups) there are 404 or so LIO. Now consider a hash or merge join for the same number of rows (for which there is an explicit SARG on each source). There are 4-5 LIO to each table, for a total of 8-10 LIO. In actuality, the cost between the 3 join types at this row count is not far different (the hash join has a higher startup cost than a loop join, but lower cost per incremental row), but the LIO ratio is 404:10!

The SQL Server Cost Based Optimizer itself clearly does not use LIO for its cost estimation. Just compare an index seek for tables and indexes with different index depth. The cost is the same, which is approximately true. After assembling a very deep collection of SQL Server performance measurements, I went back to the Oracle expert to ask about the LIO argument. He said that in the intervening time, a huge amount of evidence on the Oracle side also indicated LIO is a poor metric and this had been abandoned.

The SQL Server 2005 DMV dm_exec_query_stats keep milli-sec resolution CPU stats, and micro-sec in SQL Server 2008. I strongly suggest people abandon LIO as a performance metric. Use CPU (total_worker_time) for non-parallel execution plans. For parallel plans, the DMV reports 1000. So I suppose one must revert to Profiler traces for parallel execution plans. (If any one know how to get parallel plan CPU from DMV, please advise.)

Note also, the other post indicating physical IO has a completely different cost structure than logical IO.

SQL Server Query Processing Puzzle: LIKE vs ?

Adam Machanic — Tue, 22 Apr 2008 14:18:00 GMT

How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<do_not_mail> @ do_not_mail.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.

Run the following T-SQL to create two tables in TempDB:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Now consider the following query:

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) -- the challenge is to tune this by doing nothing more than re-writing the query.

Good luck! I'll leave the contest open for submissions until May 1.

Who's On First? Solving the Top per Group Problem (Part 1: Technique)

Adam Machanic — Fri, 08 Feb 2008 23:09:00 GMT

Relative comparison is a simple matter of human nature. From early childhood we compare and contrast what we see in the world around us, building a means by which to rate what we experience. And as it turns out, this desire to discover top and bottom, rightmost and leftmost, or best and worst happens to extend quite naturally into business scenarios. Which product is the top seller? How about the one that's simply not moving off the shelves? Which of our customers has placed the most expensive order? What are the most recent orders placed at each of our outlets?

In the world of common business questions, the edge cases are generally of most interest. What's in the middle is unimportant; it's often too difficult for the mind to compare and comprehend when there are hundreds, thousands, or even millions of items, transactions, or facts that are all within a similar range. Instead, we focus on those that stick out in some extraordinary way.

Those of us who work with SQL products on a regular basis are faced with solving this same problem time and again as we work through various business requirements. Over time, I have noticed four basic query patterns that can be used to solve the problem; each are logically equivalent (within certain restrictions -- more on that later), but can have surprisingly different performance characteristics depending on the data being queried. In this first post, I will outline the available patterns/methods. In the following posts, I will show the results of testing each pattern against a variety of scenarios in an attempt to discover where and when each should be used.

The four basic patterns are outlined below. Each of the methods is illustrated using a query to show all customers' names, plus their most recent order date, and the amount of that order. I've included notes that indicate where logic differences can arise among the various methods.

Method 1: Join to full group and use correlated subquery with a MIN/MAX aggregate to filter

In this method we use an inner join to get all required columns, then filter the resultant set using a correlated subquery in the WHERE clause.

SELECT
    c.FirstName,
    c.LastName,
    o.OrderDate,
    o.OrderAmount
FROM Customers c
JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE
    o.OrderDate =
    (
        SELECT MAX(o1.OrderDate)
        FROM Orders o1
        WHERE
            o1.CustomerId = o.CustomerId
    )

Logic notes: With this method ties are automatically included in the output, unless a tiebreaker is specified (which can be tricky given that you only have one column to work with). This method does not allow you to pull back an arbitrary number of rows, such as top 10 per customer; you are limited to the edge and any ties that might exist.

Method 1a: Join to full group and use correlated subquery with TOP(n) and ORDER BY to filter

This method is almost identical to Method 1 (which is why it is classified here as 1a), but the TOP and ORDER BY allow for a bit more flexibility than the aggregates.

SELECT
    c.FirstName,
    c.LastName,
    o.OrderDate,
    o.OrderAmount
FROM Customers c
JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE
    o.OrderDate =
    (
        SELECT TOP(1)
            o1.OrderDate
        FROM Orders o1
        WHERE
            o1.CustomerId = o.CustomerId
        ORDER BY
            o1.OrderDate DESC
    )

Logic notes: With this method you can more easily integrate a tiebreaker than with Method 1; the comparison column can be anything, including a primary key, and you can still order on whatever column makes most sense. In addition, you can take more rows than with Method 1 by using IN instead of = in the WHERE clause, and increasing the argument value to TOP.

Method 2: CROSS APPLY to ordered TOP(n)

In this method, SQL Server 2005's CROSS APPLY operator is used. This operator allows us to essentially create a table-valued correlated subquery -- something that impossible in previous versions of SQL Server. By using TOP in conjunction with ORDER BY we can get as many rows per group as needed.

SELECT
    c.FirstName,
    c.LastName,
    x.OrderDate,
    x.OrderAmount
FROM Customers c
CROSS APPLY
(
    SELECT TOP(1)
        o.OrderDate,
        o.OrderAmount
    FROM Orders o
    WHERE
        o.CustomerId = c.CustomerId
    ORDER BY
        o.OrderDate DESC
) x

Logic notes: This method is almost identical, from a logic point of view, with Method 1a modified to use IN on a primary key column. With both methods WITH TIES can be added to the TOP in order to get ties.

Method 3: Join to derived table that uses a partitioned, ordered windowing function, and filter in the outer query based on the row number

In this method a derived table or CTE is used, in conjunction with a windowing function partitioned based on the required grain of the final query. So for the "most recent order per customer" query, the row number is partitioned based on the customer. This gives us a count starting at 1 for each customer, which can be filtered in the outer query.

SELECT
    c.FirstName,
    c.LastName,
    x.OrderDate,
    x.OrderAmount
FROM Customers c
INNER JOIN
(
    SELECT
        o.OrderDate,
        o.OrderAmount,
        o.CustomerId,
        ROW_NUMBER() OVER
        (
            PARTITION BY o.CustomerId
            ORDER BY o.OrderDate DESC
        ) AS r
    FROM Orders o
) x ON
    x.CustomerId = c.CustomerId
    AND x.r = 1

Logic notes: If ties are important, use DENSE_RANK instead of ROW_NUMBER. ROW_NUMBER is good for arbitrary TOP(n), similar to Method 2. Unlike the previously described methods, in conjunction with DENSE_RANK this method can return an arbitrary TOP(n) rows, all of which can include ties. So if you would like to see the three most recent order dates and each happens to have multiple orders, this method will be able to return them all by simply filtering on x.r = 3. This would not be directly possible with any of the other methods described here.

Method 4: "Carry-along sort"

This is the only "tricky" method, and not one that I recommend using, except as a last resort. I'm including it here only for completeness and comparison because it happens to be a very high performance method in some cases. This method involves converting each of the required inner columns into a string, concatenating them, then applying an aggregate to the string as a whole. By putting the "sort" column first, the other data is "carried along" -- thus the name for the method. The concatenated data is then "unpacked" in the outer query. This can be surprisingly efficient from an I/O standpoint, but the resultant code is a maintenance nightmare and it is quite easy to introduce errors. In addition, this method can only return the top 1 per group -- no ties or multiple return items are supported.

SELECT
    c.FirstName,
    c.LastName,
    CONVERT(DATETIME, SUBSTRING(x.OrderInfo, 1, 8)) AS OrderDate,
    CONVERT(MONEY, SUBSTRING(x.OrderInfo, 9, 15)) AS OrderAmount
FROM Customers c
INNER JOIN
(
    SELECT
        o.CustomerId,
        MAX
        (
            CONVERT(CHAR(8), OrderDate, 112) +
            CONVERT(CHAR(15), SubTotal)
        ) OrderInfo
    FROM Orders o
    GROUP BY
        o.CustomerId
) x ON
    x.CustomerId = c.CustomerId

This post is just the beginning; watch this space in the coming days for a series of performance tests and analysis of these methods, and some results that I personally found to be quite surprising.

Medians, ROW_NUMBERs, and performance

Adam Machanic — Mon, 18 Dec 2006 19:52:00 GMT

A couple of days ago, Aaron Bertrand posted about a method for calculating medians in SQL Server 2005 using the ROW_NUMBER function in conjunction with the COUNT aggregate. This method (credited to Itzik Ben-Gan) is interesting, but I discovered an even better way to attack the problem in Joe Celko's Analytics and OLAP in SQL.

Rather than using a COUNT aggregate in conjunction with the ROW_NUMBER function, Celko's method uses ROW_NUMBER twice: Once with an ascending sort, and again with a descending sort. The output rows can then be matched based on the ascending row number being within +/- 1 of the descending row number. This becomes clearer with a couple of small examples:

A	1	4
B	2	3
C	3	2
D	4	1

A	1	5
B	2	4
C	3	3
D	4	2
E	5	1

In the first table (even number of rows), the median rows are B and C. These can be matched based on [Ascending Column] IN ([Descending Column] + 1, [Descending Column] - 1). In the second table (odd number of rows), the median row is C, which is matched where [Ascending Column] = [Descending Column]. Note that in the second table, the match criteria for the first table does not apply -- so the generic expression to match either case is the combination of the two: [Ascending Column] IN ([Descending Column], [Descending Column] + 1, [Descending Column] - 1).

We can apply this logic within the AdventureWorks database to find the median of the "TotalDue" amount in the Sales.SalesOrderHeader table, for each customer:

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
   FROM Sales.SalesOrderHeader SOH
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;

The equivalent logic using Itzik Ben-Gan's method follows:

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue) AS RowNum,
       COUNT(*) OVER (
          PARTITION BY CustomerId) AS RowCnt
   FROM Sales.SalesOrderHeader
) x
WHERE
   RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY CustomerId
ORDER BY CustomerId;

Taking a look at the estimated execution plans for these two queries, we might believe that Ben-Gan's method is superior: Celko's algorithm requires an expensive intermediate sort operation and has an estimated cost of 4.96, compared to 3.96 for Ben-Gan's.

Remember that these are merely estimates. And as it turns out, this is one of those times that the Query Optimizer's cost estimates are are totally out of line with the reality of what happens when you actually run the queries. Although the performance difference is not especially noticeable on a set of data as small as that in Sales.SalesOrderHeader, check out the STATISTICS IO output. Celko's version does 703 logical reads; Ben-Gan's does an astonishing 140110!

There is a good lesson to be learned from this: Cost-based optimization is far from perfect! Never completely trust what estimates tell you; they've come a long way, but clearly there is still some work to do in this area. The only way to actually determine that one query is better than another is to run it against a realistic set of data and look at how much IO and CPU time is actually used.

In this case, Ben-Gan's query probably should perform better than Celko's. It seems odd that the Query Processor can't collect the row counts at the same time it processes the row numbers. Regardless, as of today this is the best way to solve this problem... Not that I've ever needed a median in any production application I've worked on. But I suppose that's beside the point!

Announcing SQLQueryStress: A simple query load tool

Adam Machanic — Sat, 21 Oct 2006 04:29:00 GMT

On October 20, 2004 -- two years ago -- I announced that I was considering writing my own query load testing tool.

Clearly, my follow through on this promise has been incredibly delayed. But all is not lost, and I'm happy to report that I am true to my word. As of today, beta-1 of my new SQLQueryStress tool is available for download.

SQLQueryStress is a free tool that provides a simple means of load testing individual queries. It provides some support for query parameterization and options for collection of I/O and CPU metrics. It is not intended to replace tools such as Visual Studio Team System's load tests, but rather to be a simple and easy-to-use tool in the DBA or database developer's kit. I think it lives up to that goal, and initial feedback I've gotten from a few people I've shown it to has been positive. I've been working on this project on and off for around the last eight months, so I'm quite excited to finally release it to the public!

Please check it out and provide me with any feedback you have: What works? What doesn't work? What other features would you like to see? I'm planning to do quite a bit more with this tool, so I'm definitely interested in what users think would be useful. And of course, please let me know if you find any bugs (but given the rigor with which I've tested this project, there couldn't possibly be any!)

Thanks, and enjoy!

Exploring the secrets of intermediate materialization

Adam Machanic — Wed, 04 Oct 2006 02:18:00 GMT

When working with SQL Server 2000, I used to have this little trick I'd pull out after exhausting all other ideas for tuning a query. And I thought that my little trick was dead in SQL Server 2005, but thanks to fellow SQL Server MVP Rob Farley, I am officially reviving my trick from the dead here and now, in this blog post.

... But first, let's start with an example query. Here's the scenario: You work for AdventureWorks, and management has asked you to create a report to find out how many peers each employee in the company has. You see, AdventureWorks management seems to believe that if two employees are managed by the same person, they must have exactly the same job function, and they can do each others' jobs equally well. So what they want to do is find out which employees have too many peers (might as well downsize some of that extraneous fluff), and at the same time find out which employees, should they be hit by a bus tomorrow, could be immediately substituted for by a colleague. Now, whether or not management's belief is utterly moronic or not is beyond the scope of this post, so dash any such thoughts from your head until you've read to the end, and then resume pondering along those lines, which I'm sure will end up putting a smile on your face.

But smiles are for later. At this point I've managed to go off on a horribly involved tangent, so let's get back to the query at hand:

SELECT 
    x.EmployeeId,
    COUNT(*) AS TheCount
FROM HumanResources.Employee x
JOIN HumanResources.Employee y on y.ManagerId = x.ManagerId
GROUP BY x.EmployeeId

What we're doing here is finding all employees managed by the same manager, and then taking a count of those employees. Yes, it would have made more sense to simply find out how many employees are managed by each manager, but that's not what management asked for, and management clearly thinks better than you do. So go run the report!

But what does any of this have to do with query tuning tricks, you ask (while tidying up your resume a bit)? To answer that question, let's take a quick peek at the I/Os our query is using, in addition to the query plan. First, a baseline for the I/Os:

SELECT *
FROM HumanResources.Employee

Result, obtained via Profiler: 20 logical reads. OK, and now the test query in question: 827 logical reads. Quite a jump for a query that only uses the one table -- we're clearly wasting a lot of resources. And looking at the query plan, it's obvious we can do better. An outer table scan, looped to find the count for each employee -- that's a lot of index operations.

A common way to start tuning this kind of query is to move the aggregation into a derived table. After peering at this query for a while, one might come to the conclusion that there's no reason to aggregate on ManagerId more than once per manager. Why do it once per employee?

SELECT
    x.EmployeeId,
    y.theCount
FROM HumanResources.Employee x
JOIN
(
    SELECT
        p.ManagerId, 
        COUNT(*)
    FROM HumanResources.Employee p
    GROUP BY p.ManagerId
) y (ManagerId, theCount) ON y.ManagerId = x.ManagerId

Seems better, but upon running it, we see that it produces 819 logical reads and almost exactly the same query plan. Not much of an improvement. And alas, there's not much more we can do here. There just aren't too many ways to skin this query, and each of them requires some kind of loop to get the count, either implied or otherwise... Right?

And now we're almost to "dirty trick" territory. But let's first try a not-so-dirty trick. A temp table might eliminate some of the overhead, right? Then we'll only have to query the base tables once...

SELECT
    p.ManagerId, 
    COUNT(*) AS theCount
INTO #y
FROM HumanResources.Employee p
GROUP BY p.ManagerId

SELECT
    x.EmployeeId,
    y.theCount
FROM HumanResources.Employee x
JOIN #y y ON y.ManagerId = x.ManagerId

207 logical reads. Quite an improvement! But the temp table is still using a nested loop, and a merge would be so much nicer, wouldn't it? A MERGE JOIN hint drops the number of reads to 115, but I still feel that we can do even better.

Now in SQL Server 2000 at about this point in my query tuning excercise, I might try forcing intermediate materialization of the derived table, sans the temp table, by using TOP 100 PERCENT in conjunction with ORDER BY. Unfortunately, the SQL Server query optimizer team decided that this wasn't a good idea, and the optimizer now ignores such attempts.

And until earlier today, I thought the game was over. Until I was reminded by Rob that TOP takes a number of rows in addition to a percent. The trick, then? Use a bigger number of rows than you'll ever actually get back... Say, the maximum value for SQL Server's INTEGER type (2147483647)?

By applying TOP and ORDER BY within the derived table, we can force SQL Server to perform intermediate materialization of the results. And by playing with the ORDER BY properly, we can even prompt the optimizer to choose a merge...

SELECT
    x.EmployeeId,
    y.theCount
FROM HumanResources.Employee x
JOIN
(
    SELECT TOP (2147483647)
        p.ManagerId, 
        COUNT(*)
    FROM HumanResources.Employee p
    GROUP BY p.ManagerId
    ORDER BY p.ManagerId
) y (ManagerId, theCount) on y.ManagerId = x.ManagerId

And the result of all of this hard labor? 10 logical reads (1000% improvement over the next best method), a merge operation, and if I do say so myself, a very good topic for a blog post.

The usual caveats apply. Do not try this at home. Do not rely on this undocumented behavior. Do not pass Go. Do not fail to hire me to tune your databases if this trick doesn't fix all of your problems. And, lest I forget, do not waste time reading this blog when management needs that report yesterday!

Anyway, until next time, enjoy!