I spend quite a bit of time writing about query tuning on this blog. I’ve written (re-written and am actively re-writing) books on query tuning. But what I like most is talking about query tuning. I love giving sessions at various events on different aspects of query tuning, but, what I like the most is spending a whole day, trying to do a complete brain dump to get as much information out there as possible. Sound attractive? Then I’ve got a great deal for you. Come to Louisville on June 20th, 2014. We will talk query tuning at length. You have a specific question? Let’s get it answered. Then, the next day, we can all go to SQL Saturday 286 there in Louisville to get more learning and some serious networking. What’s not to like?
Let’s have some fun.
This Friday, November 1, 2013, I’m putting on an all day seminar on query tuning. It’s set up as a pre-conference event for SQL Saturday 255 in Dallas. It’s a 200 level course on understanding how the query optimizer works, the importance of statistics, constraints and indexes, how to read execution plans, and how to take all that knowledge and go to work on tuning your queries.
Here’s the fun. Sign up for the seminar, and bring a nasty query you’ve been trying to tune or a query you don’t understand or an execution plan that’s making you crazy. Depending on the time available near the end of the day, we’ll walk through a few of them. I’ve slightly restructured the seminar so I have some flexibility near the end to do this. It should be a blast. Don’t bring anything that contains sensitive data because I’m going to put it up on the big board in front of your peers. Also, don’t worry about blame. We all know Timmy wrote that query, not you.
There are only a couple of days left to sign up. If you are in the Dallas area and you want to learn query tuning, and maybe have a giggle along the way, please, click the link and register now.
I was presenting a session on how to read execution plans when I received a question: Do you have a specific example of how you can use the query hash to identify similar query plans. I do, but I couldn’t show it right then, so the person asking requested this blog post.
If you’re dealing with lots of application generated, dynamic or ad hoc T-SQL queries, then attempting to determine tuning opportunities, missing indexes, incorrect structures, etc., becomes much more difficult because you don’t have a single place to go to see what’s happening. Each ad hoc query looks different… or do they. Introduced in SQL Server 2008 and available in the standard Dynamic Management Objects (DMO), we have a mechanism to identify ad hoc queries that are similar in structure through the query hash.
Query hash values are available in the following DMOs: sys.dm_exec_requests and sys.dm_exec_query_stats. Those two cover pretty much everything you need, what’s executing right now, what has recently executed (well, what is still in cache that was recently executed, if a query isn’t in cache, you won’t see it). The query hash value itself is nothing other than the output from a hash mechanism. A hash is a formula that outputs a value based on input. For the same, or similar, input, you get the same value. There’s also a query_plan_hash value that’s a hash of the execution plan.
Let’s see this in action. Here is a query:
SELECT soh.AccountNumber , soh.DueDate , sod.OrderQty , p.Name FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID JOIN Production.Product AS p ON p.ProductID = sod.ProductID WHERE p.Name LIKE 'Flat%';
And if I modify it just a little, like you might with dynamically generated code:
SELECT soh.AccountNumber , soh.DueDate , sod.OrderQty , p.Name FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID JOIN Production.Product AS p ON p.ProductID = sod.ProductID WHERE p.Name LIKE 'HL%';
What I’ve got is essentially the same query. Yes, if I were to parameterize that WHERE clause by creating a stored procedure or a parameterized query it would be identical, but in this case it is not. In the strictest terms, those are two different strings. But, they both hash to the same value: 0x5A5A6D8B2DA72E25. But, here’s where it gets fun. The first query returns no rows at all, but the second returns 8,534. They have identical query hash values, but utterly different execution plans:
Now, how to use this? First, let’s say you’re looking at the second execution plan. You note the scan of the SalesOrderHeader clustered index and decide you might want to add an index here, but you’re unsure of how many other queries behave like this one. You first look at the properties of the SELECT operator. That actually contains the query hash value. You get that value and plug it into a query something like this:
SELECT deqs.query_hash , deqs.query_plan_hash , deqp.query_plan , dest.text FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE deqs.query_hash = 0x5A5A6D8B2DA72E25;
This resulted in eight rows. Yeah, eight. Because I had run this same query different times in different ways, in combinations, so that the query hash, which is generated on each statement, is common, but there were all different sorts of plans and issues. But, a common thread running through them all, a scan on the clustered index as the most expensive operator. So, now that I can identify lots of different common access paths, all of which have a common problem, I can propose a solution that will help out in multiple locations.
The problem with this is, what if I modify the query like this:
SELECT soh.AccountNumber , soh.DueDate , sod.OrderQty , p.Name, p.Color FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID JOIN Production.Product AS p ON p.ProductID = sod.ProductID WHERE p.Name LIKE 'HL%';
Despite the fact that this query has an identical query plan to the one above, the hash value, because of the added p.Color column, is now 0xABB5AFDC5A4A6988. But, worth noting, the query_plan_hash values for both these queries are the same, so you can do the same search for common plans with different queries to identify potential tuning opportunities.
So, the ability to pull this information is not a magic bullet that will help you solve all your ad hoc and dynamic T-SQL issues. It’s just another tool in the tool box.
UPDATE: In the text I had incorrectly said this was introduced in 2005. It was 2008. I’ve updated it and added this little footnote. Sorry for any confusion.
First thing, there are no bad operators, just bad parents, uh, I mean query writers, or database designers, or ORM tools. Why do I say this? Because all the operators within a query execution plan serve a purpose. They are there to fulfill a task. Depending on where and when you see them, they’re doing exactly what you ask of them. The issues come up because you’re asking them to do a task that they may not be well suited for. This comes from inappropriate structures and inappropriate code. Lazy spools are not really bad (that was just link bait). In fact, depending on the query, what’s being done, how you’re retrieving data, what data is being retrieved, the lazy spool is actually awesome. But, it’s good to know what these things are doing because, like magic, the lazy spool comes with a price.
The spool operators within SQL Server are indications of the need to store data for reuse. That’s it. A spool is just gathering up information, putting it into a temp table so that it can use that data again. Key word and trick phrase in that last sentence, temp table. Again, depending on the needs of the query, temporary storage is a great solution. BUT, you are creating more stuff into tempdb, additional overhead to your disks, all that’s associated with this. So if your system is already under load in this area, depending on the query in question, you may need to adjust in order to eliminate the spool. The use of tempdb is the price you pay for the lazy spool.
A lazy spool is a non-blocking operator. That means that it doesn’t stop the flow of data while it gets loaded. Instead, it acts more or less as a pass through, letting data flow through it as needed by the downstream operations. That makes a lot sense when you consider that the lazy spool is loaded in a “lazy” fashion, or, as requested. The eager spool for example stops all operations until it completes loading it’s temporary object, loading in an “eager” fashion. Once data is loaded into the lazy spool, it can be used by other operators within the plan.
Let’s see this in action. I’m going to call a recursive CTE query from AdventureWorks2012
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 9
This query results in the following execution plan (click on it to expand it).
Reading plans that involve spools, is where you really see the need to understand both the logical and physical processing order within an plan. The reason I say that is that if you just read this from right to left, the physical order, you’re likely to think that the spool is being loaded all the way over there on the right. Instead, it’s being loaded by Node ID 5, which I show below.
This shows how the logical processing order, left to right, like reading a book, has to be taken into account to deal with query plans. Now, in this case, the spool is how the optimizer is dealing with the recursive nature of the query. It loads data into a spool operator in a lazy fashion and that data is there when needed later within the operations of the query. In short, the lazy spool in this case is doing a good thing and helping you out.
So, why the lazy spool is bad? Well, it isn’t. It’s just not free.
When I first saw this question I thought to myself, “Self. Don’t you think that’s comparing apples to hammers? Yes, Self, I’m pretty sure it is. Good, I thought so too, self. Yeah, me too.” After rebooting because of the runaway iterations on that thought, I had another, “Well… hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer.” I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes?
As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you’re dealing with functional objects within SQL Server, it’s a bad habit to start to equate one to the other, especially when they have such radically different functions. I, myself, know that an index is meant to be a mechanism to speed the retrieval of data. While a foreign key is meant to constrain data in a relational fashion between two tables enforcing that the data in one table can only contain data from the primary key (or a unique constraint) from the other table. But, my other self points out, indexes also work as constraints. As mentioned, you have a primary key, which is realized as an index on the table (clustered by default, but they don’t have to be). And, in addition to the primary key, you can have a unique constraint, also realized as an index on the table. So these constructs are similar to foreign keys. But, they’re very different. A foreign key simply validates, prior to allowing the data to get added to the table in question, that the values being added (or modified) match values in the key. That’s it. Indexes, unique or not, are a secondary storage mechanism within SQL Server. Clustered indexes actually are the table. They have the structure of an index, the balanced tree, or b-tree AND, at the leaf level, they have all the data that defines the table. Non-clustered indexes are sort of the same. They have a b-tree, just the same, and they have leaf levels that might have columns that are INCLUDEd in their storage.
Within the optimizer, assuming your foreign key is enforced, meaning it is created WITH CHECK or a check is run after it is created, then the optimizer can take advantage of the knowledge that data within the foreign key must match data within the parent table. This allows for better optimization of joins, even elimination of joins (see an example of this here). But, that’s not the same as what the optimizer does with indexes. Again, this is where the data is, so the optimizer is going to make a great deal of use of indexes in retrieving data in an optimal fashion, simply because that’s where the data is located (or, can be found if we’re talking key lookups and stuff like that).
So, while comparing apples & hammers, uh, I mean, foreign keys and indexes, you do hit the concept of constraints for both, the mechanisms associated with indexes are very different from those associated with the foreign key because they have storage while the foreign key does not.
It’s been emphasized over and over that the costs of operations within an execution plan, and the estimated costs of the plan themselves are, in fact, estimates. But it goes further than that. The estimated values are based on statistics, or the lack thereof. Statistics themselves are also estimates. This means that the costs you’re seeing are extrapolations based on extrapolations. So, you should just ignore those values and move on, right? Wrong.
In order to understand how the optimizer is choosing to put together an execution plan for your query so that you can use that understanding to then make intelligent choices as to modifying the query or the structure of your database, you must use the values you have at hand. However, you must also understand where and how those values were derived in order to make a determination on how much faith you can put into them (because you are simply placing faith in those numbers). Above all else, this means you must understand your data as presented by the statistics and what these statistics represent in terms of defining your data.
For a more detailed discussion of what exactly statistics represent, see my article in Simple-Talk.
Next, you need to understand what the operator itself is doing. In the majority of cases, this means just reading the description. They’re usually pretty clear. But sometimes, it might not be that clear. Further, while the descriptions of an operator may be clear, you need to further understand why or what it is doing, not simply what it is. This means further drill down to reading through the properties of the operator (NOTE: not the tool tip) in an attempt to understand what’s going on. Boogle or Ging will be a friend here. You can search up descriptions of what operators are to assist with your understanding. You need to know when something doesn’t even have statistics, such as a table variable, and therefore the optimizer assumes it only has 1 row, because that’s going to radically affect the cost estimates displayed for you, even in an actual execution plan. That’s right, the actual plan costs are still just estimates.
I wish I could tell you to rely on these numbers, but you can’t. I wish I could tell you to ignore these numbers, but you can’t. These are the only numbers you get that show you what’s happening internally within the query within the optimizer, so you must use them. Just use them with the full knowledge that they are calculations based on other calculations based on extrapolations. In short, a bit of a guess.
I am excited to be able to tell you about an all day seminar that I’ll be putting on prior to the Dallas SQL Saturday #255. The seminar will be on November 1, 2013. It’s called Query Performance Tuning in SQL Server. We’re going to cover the topic from an understanding of the optimizer to collecting data using extended events to reading execution plans and then on to lots of standard problems and their solutions. If you sign up before September 21st you can get a substantial early-bird discount, so I’d jump on it. Also, seats are limited, so don’t wait too long. Let’s get together and talk query tuning.
It may seem obvious, but I’ve heard more than one person suggest to me that statistics on a clustered index just don’t matter. That if the clustered index can satisfy a given query, it’s going to get selected. That just didn’t make any sense to me, but I haven’t seen anyone set up a test that shows how it might work one way or the other. Here you go.
First, I’m going to create a table and load it up with data. I’m intentionally using strings because I don’t want to confuse the ease of management of integers within indexes. I also went for one column that would have a very attractive set of statistics and one that would have a very ugly set. Also, because we’re only dealing with two columns at any given juncture, either a clustered or a non-clustered index would be a covering index. Finally, I didn’t mark the clustered index as unique because I wanted the non-selective clustered index and the highly selective clustered index to both have to deal with that extra bit of processing. Here’s how I set up the table and the data:
CREATE TABLE dbo.IndexTest ( SelectiveString VARCHAR(50), NonSelectiveString VARCHAR(2)) WITH Nums AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) AS n FROM master.sys.all_columns AS ac CROSS JOIN master.sys.all_columns AS ac2 ) INSERT INTO dbo.IndexTest (SelectiveString, NonSelectiveString ) SELECT n, CASE WHEN n % 3 = 0 THEN 'ab' WHEN n % 5 = 0 THEN 'ac' WHEN n % 7 = 0 THEN 'bd' ELSE 'aa' END FROM Nums;
From there I created the first two indexes:
CREATE CLUSTERED INDEX ClusteredSelective ON dbo.IndexTest (SelectiveString); CREATE NONCLUSTERED INDEX NonClusteredNonSelective ON dbo.IndexTest (NonSelectiveString);
Then I ran each of these queries, both of which are actually going after fairly selective bits of data, although largely relatively speaking in terms of the second query:
SELECT * FROM dbo.IndexTest AS it WHERE SelectiveString = '2323'; SELECT * FROM dbo.IndexTest AS it WHERE NonSelectiveString = 'aa';
This resulted in the following two execution plans:
As you can see, the clustered index was used in the first query. It makes sense because we’re querying against the clustered key and it’s a very highly selective key. The second query, despite being against a fairly non-selective key, 48,000 rows out of 100,000, used the non-clustered index. If I drop the non-clustered index and use just the cluster for the second query, the number of reads goes from 110 to 299 despite the fact that the same data is being returned. Clearly there’s a huge advantage to how data is ordered. Also, clearly, the fact that the statistics suggest that the cluster can’t immediately satisfy the query makes the optimizer choose other options. But, what happens if we change the indexes like this:
CREATE NONCLUSTERED INDEX NonClusteredSelective ON dbo.IndexTest (SelectiveString); CREATE CLUSTERED INDEX ClusteredNonSelective ON dbo.IndexTest (NonSelectiveString);
Then, when I rerun my queries, I get these execution plans:
At least to my mind, it’s pretty clear. The statistics for the cluster clearly help the optimizer decide if that index is useful. Yeah, if I drop the nonclustered indexes and then run the queries the clustered index is always used, but that’s not because the cluster is selective or not, it’s because the cluster is the table.
I’m not sure where this concept that the statistics of a clustered do not matter, but, from these tests, it seems that they do.
And remember, in just a couple of weeks I’ll be doing 7 hours of query performance tuning instruction at the PASS Summit. You can sign up, as far as I know, right up to the day of the event. The name of the session is Query Performance Tuning: Start to Finish. I cover gathering metrics, understanding the optimizer, reading execution plans, and tuning queries. It’s a beginner’s level to intermediate course. It should be a lot of fun. Go here to register. I hope to see you there.
I’m working through some code that I haven’t touched recently and I’m running it for the first time on a SQL Server 2012 server. The code is a way to load information into the RML utilities and I started hitting errors. First, I hit an error that my server couldn’t be connected to, but thanks to Erin Stellato (blog|twitter), I was able to quickly fix that. Then I hit this:
Number of processors: 2 Active proc mask: 0x00000003 Architecture: 9 Page size: 4096 Highest node: 0 Package mask: 0x00000001 Processor(s): 0x00000001 Function units: Separated Package mask: 0x00000002 Processor(s): 0x00000002 Function units: Separated Processors: 0x00000003 assigned to Numa node: 0 -Ic:\performancetuning\rml.trc -oc:\bu -SDOJO\RANDORI Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x00060101 and Defined: 0x00060101 Attempting to cleanup existing RML files from previous execution Using extended RowsetFastload synchronization Establishing initial database connection: Server: DOJO\RANDORI Database: PerfAnalysis Authentication: Windows Using SQL Client version 10 Creating or clearing the performance database The major version number (11) in the trace file header is not a supported file version. At this time only Microsoft SQL Server 2000, 2005 and 2008 trace files are supported. The current trace version (11) is not supported. ERROR: Read of file header for file c:\performancetuning\rml.trc failed with operating system error 0x8007000D (The data is invalid) *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x8007000D (The data is invalid) Reads completed - Global Error Status 0xfffffffe Shutting down the worker thread message queues. ...
That’s right, the trace header shows the version it was captured with and the 2012 version doesn’t work with RML Utilities. I’ve tried several different ways of defining the trace collection, but the header doesn’t seem to be something you can control. I’ve also tried opening it in an editor and finding the right bit of code to change (it’s just a trace file after all) but no luck there either.
Firing up some older hardware now in order to get at a 2008R2 or earlier server. I’ve already upgraded all my servers & virtuals.
Just so we’re clear, I use SQL Server. I like SQL Server. But, this doesn’t mean I have anything against Oracle. It’s fine. It’s good. But, I know very little about it. However, throughout my career I’ve found myself needing to understand it better. Either because I’m trying to train Oracle people to better use SQL Server and I need to be able to speak a little of their language to facilitate translation. Or, because I’m defending SQL Server on some technical point that the Oracle people don’t completely understand. Or, because I’ve said something stupid about Oracle in my ignorance.
Now, you know how busy you are, and I know how busy I am, so I doubt either of us has the time we really need to learn Oracle much. So, what do you do? Well, Red Gate Software, who straddles the worlds between Oracle & SQL Server like the Bifrost between Midgard & Asgard, has started a series of conversations between two people who know something about each platform, Jonathan Lewis (blog) and me.
We had our first conversation talking about clustered indexes. We covered how they work in both platforms (not that differently) and they’re used and abused. Interestingly enough, according to Jonathan, clustered indexes just aren’t used that much within Oracle, despite the fact that they really do behave mostly the same way as they do within SQL Server, where we use them on most every table (or at least so I maintain you should). It was a great discussion (NOTE: not a fight, no one was nasty or mean, we talked).
We’re going to have another discussion. We’re going to be talking about temporary tables. Again, I don’t know much about Oracle, so please, this is not an attack, but apparently they don’t have the same concept of temporary tables as we do in SQL Server. We’re going to cover a lot of the myths and misperceptions surrounding temp tables on both Oracle and SQL Server, how they work and how they affect performance. I learned a lot during the last conversation and I don’t doubt I’ll learn a lot during this one. If you’re interested, please go to this web page and register.
And, I’d be remiss if I didn’t mention again, if you like learning about performance in SQL Server that you should consider attending the PASS Summit 2012. If you register now, you save $500, which is just enough to pay for my pre-conference seminar, Query Performance Tuning: Start to Finish. I’ll be covering all aspects of performance tuning from gathering metrics to understand which queries are running slow, to reading execution plans to understand why, to addressing the issues to fix the performance and make your queries hum. Please consider taking part. It’ll be a lot of fun and I’ll try like crazy to make it useful.