The short answer is, of course, none of them, but testing is the only way to be sure.

I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different).

Let’s start with the simplest:

SELECT    *
  FROM    Production.ProductListPriceHistory AS plph;

This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index:

CREATE NONCLUSTERED INDEX TestIndex
  ON Production.ProductListPriceHistory
(ProductID,StartDate,EndDate,ListPrice,ModifiedDate);

When I rerun the query it results in an index scan, non-clustered, with 5 logical reads. Granted, this is stupid. Instead, let’s do this. We’ll create a meaningful non-clustered index and then force it’s use:

CREATE NONCLUSTERED INDEX TestIndex
  ON Production.ProductListPriceHistory
(ListPrice);

Then run this:

SELECT    *
  FROM    Production.ProductListPriceHistory AS plph
WITH (INDEX(TestIndex));

Now that’s using a non-clustered index in a ‘SELECT *’ situation. It results in 794 logical reads and this execution plan:

Clearly, this is not an improvement. Finally, let’s get rid of the non-clustered index and put this columnstore index in place:

CREATE NONCLUSTERED columnstore INDEX xtest
  ON production.ProductListPriceHistory
(productid,startdate,enddate,listprice,modifieddate);
GO

Now when I run the ‘SELECT *’ query I have 37 reads and this execution plan:

This means the columnstore index is being used, but, if you look at the properties, you’ll see that the execution mode on this one is Rows, which is not the preferred use you’ll want out of a columnstore index. You want to see the execution mode be Batch.

If we stopped here, the answer is simple, a clustered index scan is better. But what if we add filtering? Let’s modify the query to look like this:

SELECT    *
  FROM    Production.ProductListPriceHistory AS plph
WHERE    plph.ListPrice = 23.5481;

Now, I’ll go back and set up the table so that it has just the clustered index, a non-clustered index, or the columnstore index. The results for the clustered index are identical. Since the column, ListPrice, is not part of the clustered key, a scan is necessary and the results are 5 reads and an execution time of about 1ms (the data is cached). The non-clustered index resulted in the same execution plan as before, but only 8 reads. But, the execution time was 42ms, so the added processing of getting the data put together from the key lookup was a little costly. Finally, the columnstore index results in 42 reads and an execution time of 4ms. The execution mode of the columnstore index was still Row.

What’s all this mean? Not much since you shouldn’t be using ‘SELECT *’ anyway, but the main takeaway I’d suggest is that columnstore indexes are not magic. They don’t replace traditional indexing. Further, if you’re going to use them, be sure that you’re really using them correctly. Just because you see the columnstore operator in the execution plan doesn’t mean you’re taking advantage of all the fantastic benefits they offer. Dive down into the properties and check the execution mode to ensure you’re getting a Batch execution. Then you’ll know that you’re benefiting from the columnstore index.

There’s a very old saying, “When you find yourself in a hole, stop digging.”

And my evidence today is:

That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard.

If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows.

Please, stop digging.

The question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6:

1. Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s a timeout, I know I can’t count on this plan being good. Also I get the parameter compile time & run time values to help determine parameter sniffing issues in the properties.
2. Warnings. If you see no join predicate warnings, that should jump up and poke you in the eye like some jumping eye-poking little monster. Same goes with missing statistics. The new warnings in plans in 2012 are equally important to know about. These are quick pieces of information that should immediately point you in a direction of inquiry within the plan.
3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them. They’re accurate more often than not and quickly lead you to the possible source of the problem.
4. Fat pipes. Now really, these are usually just an indication of volume and knowing that you’re moving lots of rows helps you read a plan (umpty-million rows joining umpty-million rows through a Loop might be an issue). But the real alarm bells go off when you see big fat pipes going to little skinny ones or skinny ones to big fat ones or even skinny-fat-skinny. That’s a huge indicator of something
5. Extra operators. This is like that old statement about pornography “I can’t give you a precise definition, but I know it when I see it.” It’s looking for stuff that doesn’t belong. For example, you don’t have a single ORDER BY statement, but there sits a Sort operation. Why? That’s my “extra operator” indicator telling me to dig deeper.
6. Scans. Scans are not necessarily bad and Seeks are not necessarily good. In general terms, with smaller data sets, you usually would expect to see a Seek over a Scan. Scans can be the right, good, and best choice, especially for very large data sets and in other situations, but they are an indicator of potential issues.

After that, you have a whole slew of things you can get worked up about. Table Spools in SELECT statements are usually not good. Look for indications of multi-statement UDF’s (Scan’s with zero cost). Loop joins when a Merge makes more sense, Merges where you ought to see a Hash, missing index information, mismatch between estimated & actual, blah, blah, blah… You get the point. There’s just tons & tons of information within execution plans. But that list of six are usually the first things I look for.

I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge.

Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are. Which is why most anyone who wants to learn about extended events should plan on starting at one place, Jonathan Kehayias’ blog. Yeah, the Books Online help get you started, but Jonathan really makes it all take off.

But why is this huge? I’m not going into a low-level discussion here, because frankly, I’m incapable, for that go back to Jonathan. What I will say is that you have a mechanism that captures events at their source instead of after the fact. That makes it faster and causes less impact on your system. Further, you can put filters in place at the event level that make it so that you only capture the information you want, when you want it. In short, it’s about control, reduced impact and increased knowledge.

So why write about it now? Because Denali has finally provided a GUI that simplifies the process of setting up extended events. There’s a wizard for building extended events sessions, and a session management GUI that you can use to manage them on your own. Basically, you have a way to get past the confusing and odd TSQL implementation and start using these things.

Check it out:

It really does provide everything you need to get going with extended events… or does it? There’s still the issue of knowing how these things work and what they do. The GUI above enables you, but it doesn’t provide much guidance. For example, there’s a very enticing little action (stuff collected from events are called actions or fields) called sql_text. When setting up my first, simple, session (the one you see above), I saw that action and thought, ah-ha, there’s my collection point for getting the sql batch and the procedure call. But then, on testing, it didn’t work. After a plaintive call for help on Twitter (hash tag #sqlhelp for those who don’t know), Jonathan rode to my rescue and informed me that despite the enticing name, that’s not what I wanted.

Instead, each event has event fields that are simply a part of the event. Global Fields (actions, whatever, told you extended events were confusing), have an added overhead, so you have to think twice about using them. Plus, sql_text didn’t return any data for sql batch calls. No, instead, for the rpc_completed, I needed to read the field, statement. Excellent! All set. Well, no. There’s still another wrinkle. For sql_batch_completed there is no statement field. Instead, I need to collect sql_batch_text. Ah, good…. but wait, now I have two fields that get returned and I have to figure out which of these I use instead of just going to a single source like I used to with trace…grrr… Why does Microsoft hate me? And you?

Anyway, I’ll be weaving extended event sessions throughout the book. You can use these things for looking at waits, deadlocks, ooh, all kinds of stuff. I did say powerful and huge right. Keep an eye out for a few more blog posts.

Also, if you want to talk to me about gathering knowledge for your performance tuning efforts, then you should make your way to SQL In the City: Los Angeles. I’m going to be there with a slew of other MVPs on October 28th. It’s free. Click here now to register. My session is called Performance Tuning With Knowledge, and it’s all about gathering the information you need to make your performance tuning decisions.

I was presenting on execution plans when another question came up that I didn’t know the answer to immediately. Yes, I know you’ve seen that phrase before on this blog. I love presenting because you get exactly the kinds of questions that make you think and make you learn. I’m presenting, in part, to learn, just as much as I am to teach. It was the same with kenpo. The more I taught, the better I learned the art. Wait, this isn’t supposed to be a blog post about learning. This one is about statistics.

The question was, does the execution plan have the statistics that were used by the optimizer to decide on the execution plan. And no, what was meant, was not does it show the estimated rows, which come from the statistics, but specifically does it show that it used a set of statistics named X? The answer to that is yes & no. Or, to be more DBAish about it, it depends.

Let’s take a really simple query run against a freshly installed copy of AdventureWorks2008R2:

SELECT p.BusinessEntityID,
p.FirstName
FROM Person.Person AS p
WHERE p.FirstName LIKE 'Toni%';

This query generates this execution plan:

Yes, a very sophisticated and hard to understand execution plan. Now, here’s the deal, there were two sets of statistics used to make this plan, but only one of them can be seen in the plan. See any statistics there? Sure you do. IX_Person_LastName_FirstName_MiddleName. That is one of the two sets of statistics that were used to make this execution plan. Where’s the second set? Not in the execution plan.

[sourcecode language=”sql”]sp_helpstats N’Person.Person’, ‘ALL’;[/sourcecode]

The results are here:

There is the second set of statistics used for this query, right at the top. Because I was searching the FirstName column, the optimizer found that it did not have the statistics it needed, so they were created, on the fly, and then, were not a part of the execution plan. Further, I probably looked at other statistics such as the PK_Person_BusinessEntityID because that’s the clustered index for the table. It could have scanned that to get the list of values just as easily as the other index. But, that other index is probably smaller, which means fewer pages scanned.

So, back to the question, can you see the statistics used by the optimizer inside the execution plan? Some of them, yes, but not all of them.

Please, if I’m presenting, ask questions. I’ll know a few of the answers, right off the top of my head. Others will make me go and learn so that I can answer the next person who asks the same question. I don’t mind losing at a game of Stump the Chump, so let’s play.

I have to say, I only recently noticed this on a tool tip:

and this in the property sheet:

The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn’t remember having seen one before.

Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter:

SELECT  soh.PurchaseOrderNumber,
soh.AccountNumber,
p.Name,
sod.OrderQty,
sod.LineTotal,
cc.CardNumber
FROM    Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
JOIN Sales.CreditCard AS cc
ON soh.CreditCardID = cc.CreditCardID;

The entire execution plan looks like this:

We’re focused on the Hash Match join at the top. For logical processing it would be the first operation. For physical operations it would be the last.

The way a Hash Match works is by creating a hash table (in tempdb by the way) and making a hash value. Then, it makes a hash value of the stuff being compared and tries to find matches in the hash table. That’s it.

The residual is if there are additional predicates that also must be matched in order to fully satisfy the query. That’s all. It’s actually quite simple.

What are the implications? Well, this is where it gets fun. You see, the first match, in the hash has to take place, and then, it also has to do the residual probe. The first match is part of the process. The second match is additional work. That’s not good. You can see it in this example plan because the Hash Match operation is estimated as the most costly and that’s probably true.

The key is, drill down to understand what your execution plans are up to.

I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out?

This sounds like a job for XQuery!

There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code.

WITH XMLNAMESPACES(DEFAULT N'<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan')">http://schemas.microsoft.com/sqlserver/2004/07/showplan')</a>,  QueryPlans
AS  ( 
SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,
        RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,
        --dest.text,
        SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
                  (deqs.statement_end_offset - deqs.statement_start_offset)
                  / 2 + 1) AS StatementText,
        deqp.query_plan,
        deqp.dbid,
        deqs.execution_count,
        deqs.total_elapsed_time,
        deqs.total_logical_reads,
        deqs.total_logical_writes
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
        CROSS APPLY deqp.query_plan.nodes(N'//StmtSimple') RelOp (pln)
WHERE   deqs.statement_end_offset > -1        
)   
SELECT  DB_NAME(qp.dbid),
        *
FROM    QueryPlans AS qp
WHERE   (qp.dbid = 13 OR qp.dbid IS NULL)
        AND qp.optimizationlevel = 'Full'
ORDER BY qp.execution_count DESC ;

The mandatory warning now, XQuery like this can be somewhat processor intensive. I wouldn’t suggest running this on a production system unless you were to put more filters in place to trim the data down a bit.

The Rally is next week. It’s not too late to register. This is going to be a very solid event with excellent opportunities for learning and networking. If you’re on the fence about going, don’t be. You should attend.

I even have a few (somewhere near 10) slots open in my pre-conference seminar, Query Performance Tuning: Start to Finish. I’ve been working on hard on this presentation and showing pieces of it to various user groups around the country. The reception so far has been very good.

If you want a seven hour brain dump on the ins and outs of performance tuning your queries, I strongly recommend it. Since there are still openings, you can get in, but I’d act quickly. Not because they’re all likely to fill, but because, it takes place in one week’s time. You need to plan for the trip.

Once you’re in town, you should stay of course for the next two days. There are going to be tons of fun things to do, people to learn from, other to talk to, sessions to attend. You might not want to missing the lightening talks either. I’m going to do one called “Testing Your Backups: A Rant.” You won’t want to miss it. There are going to be a bunch of evening events too, so we’re really stretching out the cost of registration. There will be only one reason to go back to your hotel, and that’s to pass out in preparation for the next day.

Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go.

Recompiles

I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here is “any given set of statistics.” My edge case was a query against 86 tables. Any of the statistics on any of the 86 tables changed, and the query went into it’s three minute recompile. The problems come in because while it’s recompiling, it’s locked and everyone has to wait. And waiting is the performance problem.

Parallelism

The default cost threshold for parallelism is 5. This means that any query plan that has an estimated cost greater than 5 could be marked as a parallel query plan. Parallel execution is a good thing, when the costs of breaking the code into disparate streams and gathering them back together is offset by the use of multiple processors. The problem is that cost is generally much higher for the break apart and gathering than the benefits of bringing multiple processors into play.

Deadlocks

Yeah, deadlocks are a performance problem. You have to deal with the fact that at least one process goes through some part of the steps it was doing and then has to roll back. The rollback process is costly and then it has to be resubmitted for processing again. These things can seriously kill performance.

ORM

These things are great. These things are evil. I think of ORM tools like a firearm. In the proper hands, used the proper way, they’re an excellent tool. In the wrong hands and used the wrong way, mayhem ensues. Same thing goes with most of the ORM tools. Further, many of the ORM tools, very well built pieces of software, are advertised and pushed in a way that makes people use them incorrectly. It would be like someone showing off hand gun twirling with loaded pistols as an example of safe gun handling. More education and more appropriate expectations are needed here.

Dynamic Queries

To a degree these are an offshoot of ORM tools, but they’ve been around a lot longer. You’ve all seen them and I think most of us have built them at one point or another; the catch-all query, the report with infinite flexibility, etc. They fill memory up and flush things out of cache and cause untold grief.

Database Engine Tuning Advisor

If Microsoft is going to put something out there that advises people on how to improve performance, you would expect it to work. It doesn’t. Oh, it can. I’ve seen it catch some of the simplest queries and fix them. I’ve also seen it miss the simplest queries and what it suggests for complex queries can be really, really scary. It will have you adding indexes and individual sets of statistics all over the place. You’ll duplicate existing indexes. All sorts of stuff. And, because it’s from Microsoft, people take what it tells them as gospel and implement it, with no testing or evaluation at all.

TSQL

I don’t think the language is all that hard. But, evidently it is. And it’s easy to muck it up and write code that is truly not good.

Backups

No, not the act of backing up databases, but the fact that people can’t seem to take backups. Don’t think that’s a performance issue? How fast are queries running when everything is offline? How good is performance when the database is offline because it’s corrupt?

Query Optimizer

And no, it’s not that I think the optimizer itself is a performance problem, but that so many people rely on it to perform miracles that it just can’t perform. I have an 86 table join, why isn’t everything running fast? I’ve got 36 deep nested views, how much the query is bad? It’s the expectations that the optimizer can and will figure out anything for them quickly, efficiently, and accurately, so that they don’t have do the things that they should that becomes a problem. And I understand it. The optimizer does incredible amounts of processing in very short periods of time. It’s truly an amazing piece of software. But, because it does all this stuff so well, I think it’s taught people to rely on it for too much and it just can’t deliver everything. It doesn’t have a “run faster” button that many people seem to think it has.

The very first ever SQL Rally is taking place in a little less than four weeks in Orlando Florida. It’s going to be quite the event. There will be two full days of sessions on any number of topics. But before that all starts, there’s going to be a set of full day pre-conference seminars. These too are on a number of topics, but I’m hoping to draw your attention to just one, mine.

I’ve put together a seven hour session on query performance tuning. I’ve tried to make it as complete as I possibly can. I’m going to cover the whole process from collecting data on your machines to identify where problems may be, to understanding the optimizer so you know how things work, to reading execution plans so you can identify issues, to various methods of fixing all sorts of different performance problems. In short, performance tuning, start to finish. Here are the specific things that I hope to communicate to you with this seminar:

1. The ability to collect performance metrics on their servers as part of an overall query tuning methodology

2. The ability to generate execution plans from multiple sources in support of troubleshooting poorly performing queries

3. An understanding of how the optimizer works in support of writing better TSQL code as well as troubleshooting poorly performing queries

4. A working knowledge of DMVs that will help them identify and fix performance issues on their servers

5. The ability to address common query performance problems

That’s it. If you get all or part of these five topics, it’s a win. This is probably an low to intermediate level class. It’s not high level. If you’ve been teaching performance tuning to experts for years, you’re probably not going to get much out of this class. If you do want to prep for the class, I’d recommend getting a copy of my book, “SQL Server 2008 Query Performance Tuning Distilled.”

There’s a good chance this seminar will sell out. If you’re interested, please click here to register soon in order to ensure that you have a spot.

I hope to see you there.