Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP
.
About This Blog
Thursday 3 May 2012
Parallel Execution Plans Suck
Tuesday 1 May 2012
Query Optimizer Deep Dive - Part 4
This is the final part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group, and SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.
Links to other parts of this series: Part 1 Part 2 Part 3
Beating the Optimizer
Our AdventureWorks test query produces an optimized physical execution plan that is quite different from the logical form of the query.
The estimated cost of the execution plan shown below is 0.0295 units.
Since we know the database schema very well, we might wonder why the optimizer did not choose to use the unique nonclustered index on Name
in the Product
table to filter rows based on the LIKE
predicate.
Sunday 29 April 2012
Query Optimizer Deep Dive – Part 3
This is the third in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group, and SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.
Links to other parts of this series: Part 1 Part 2 Part 4
Storage of Alternative Plans
We saw in part 2 how optimizer rules are used to explore logical alternatives for parts of the query tree, and how implementation rules are used to find physical operations to perform each logical steps.
To keep track of all these options, the cost-based part of the SQL Server query optimizer uses a structure called the Memo. This structure is part of the Cascades general optimization framework developed by Goetz Graefe.
Saturday 28 April 2012
Query Optimizer Deep Dive – Part 2
This is the second in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group, and SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.
Links to other parts of this series: Part 1 Part 3 Part 4
Cost-Based Optimization Overview
The input to cost-based optimization is a tree of logical operations produced by the previous optimization stages discussed in part one.
Cost-based optimization takes this logical tree, explores logical alternatives (different logical tree shapes that will always produce the same results), generates physical implementations, assigns an estimated cost to each, and finally chooses the cheapest physical option overall.
The goal of cost-based optimization is not to find the best possible physical execution plan by exploring every possible alternative. Rather, the goal is to find a good plan quickly.
Query Optimizer Deep Dive - Part 1
This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group, and SQL Saturday events in Wellington, New Zealand and Adelaide, Australia.
Links to other parts of this series: Part 2 Part 3 Part 4
Introduction
The motivation behind writing these sessions is finding that relatively few people have a good intuition for the way the optimizer works. This is partly because the official documentation is rather sparse, and partly because what information is available is dispersed across many books and blog posts.
The content presented here is very much geared to my preferred way of learning. It shows the concepts in what seems to me to be a reasonably logical sequence, and then provides tools to enable the interested reader to explore further, if desired.
Monday 12 March 2012
Fun with Scalar and Vector Aggregates
There are interesting things to be learned from even the simplest queries.
For example, imagine you are asked to write a query that lists AdventureWorks product names, where the product has at least one entry in the transaction history table, but fewer than ten.
Wednesday 18 January 2012
Dynamic Seeks and Hidden Implicit Conversions
A LIKE
predicate with only a trailing wildcard can usually use an index seek, as the following AdventureWorks sample database query shows:
SELECT
P.[Name]
FROM Production.Product AS P
WHERE
P.[Name] LIKE N'D%';
Friday 23 December 2011
Forcing a Parallel Query Execution Plan
This article is for SQL Server developers who have experienced the special kind of frustration that only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan.
This situation often occurs when making an apparently innocuous change to the text of a moderately complex query — a change which somehow manages to turn a parallel plan that executes in ten seconds, into a five-minute serially-executing monster.
Tuesday 6 December 2011
SQL Server Optimizer Bug with JOIN and GROUP BY
I came across a SQL Server optimizer bug recently that made me wonder how on earth I never noticed it before.
As the title of this post suggests, the bug occurs in common JOIN
and GROUP BY
queries. While it does not cause incorrect results to be returned, it will often cause a poor query plan to be selected by the optimizer.
If you are just interested in the bug itself, you will find a description in the section headed “the bug revealed”. It relates to cardinality estimation for serial partial aggregates.
As the regular reader will be expecting though, I am going to work up to it with a bit of background. The lasting value of this post (once the bug is fixed) is in the background details anyway.
Sunday 4 December 2011
Is Distinct Aggregation Still Considered Harmful?
Back in 2008, Marc Friedman of the SQL Server Query Processor Team wrote a blog entry entitled “Distinct Aggregation Considered Harmful”.
Marc shows a way to work around the poor performance that often results simply from adding the keyword DISTINCT
to an otherwise perfectly reasonable aggregate function in a query.
This post is an update to that work, presenting a query optimizer enhancement in SQL Server 2012 that reduces the need to perform the suggested rewrite manually.
Wednesday 21 September 2011
Finding the Statistics Used to Compile an Execution Plan
In this post, I show you how to determine the statistics objects used by the query optimizer in producing an execution plan.
Note: This technique only applies to queries compiled using the original (70) cardinality estimation model.
Tuesday 30 August 2011
Can a SELECT query cause page splits?
The SQL Server documentation has this to say about page splits:
When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations.
Given that, how can a SELECT
statement be responsible for page splits?
Well, I suppose we could SELECT
from a function that adds rows to a table variable as part of its internal implementation, but that would clearly be cheating, and no fun at all from a blogging point of view.
Tuesday 9 August 2011
SQL Server, Seeks, and Binary Search
The following table summarizes the results from my last two articles, Enforcing Uniqueness for Performance and Avoiding Uniqueness for Performance. It shows the CPU time used when performing 5 million clustered index seeks into a unique or non-unique index:
In test 1, making the clustered index unique improved performance by around 40%.
In test 2, making the same change reduced performance by around 70% (on 64-bit systems – more on that later).
Thursday 4 August 2011
Avoiding Uniqueness for Performance
In my last post, Enforcing Uniqueness for Performance, I showed how using a unique index could speed up equality seeks by around 40%.
Friday 29 July 2011
Enforcing Uniqueness for Performance
A little while back, I posted a short series on seeks and scans:
One of the things I highlighted in the middle post was the difference between a singleton seek and a range scan:
-
A singleton equality seek always retrieves exactly one row, and is guaranteed to do so because a unique index exists to enforce it.
-
A range scan seeks down the B-tree to a starting (or ending) point, and scans forward (or backward) from that point using the next or previous page pointers.
Today’s short post shows how much faster a singleton seek is, compared with a range scan, even when both return exactly the same number of records.
Tuesday 19 July 2011
Join Performance, Implicit Conversions, and Residuals
Introduction
You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.
Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT
elements, and others routinely inspect plans for that type of thing when tuning.
Now, that’s all good, as far as it goes. It may surprise you to learn that not all implicit conversions are visible in query plans, and there are other important factors to consider too.
Thursday 7 July 2011
Bitmap Magic (or… how SQL Server uses bitmap filters)
Question
Can a parallel query use less CPU than the same serial query, while executing faster?
The answer is yes! To demonstrate, I’ll use the following two (heap) tables, each containing a single column typed as integer
:
Saturday 2 July 2011
Undocumented Query Plans: The ANY Aggregate
As usual, here’s a sample table:
CREATE TABLE #Example
(
pk numeric IDENTITY PRIMARY KEY NONCLUSTERED,
col1 sql_variant NULL,
col2 sql_variant NULL,
thing sql_variant NOT NULL,
);
Some sample data:
And an index that will be useful shortly:
CREATE INDEX nc1
ON #Example
(col1, col2, thing);
There’s a complete script to create the table and add the data at the end of this post. There’s nothing special about the table or the data (except that I wanted to have some fun with values and data types).
Wednesday 22 June 2011
Undocumented Query Plans: Equality Comparisons
The diagram below shows two data sets, with differences highlighted:
To find changed rows using T-SQL, we might write a query like this:
The logic is clear: Join rows from the two sets together on the primary key column, and return rows where a change has occurred in one or more data columns.
Unfortunately, this query only finds one of the expected four rows:
The problem is that our query does not correctly handle NULLs.
Saturday 5 March 2011
How Parallelism Works in SQL Server
You might have noticed that January was a quiet blogging month for me.
Part of the reason was that I was working on an article for Simple Talk, looking at how parallel query execution really works. The first part is published today at:
Understanding and Using Parallelism in SQL Server.
This introductory piece is not quite as technical as normal, but I hope there be enough interesting material there to make it worth a read.
© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi
Sunday 27 February 2011
SQL Server Bug: Slow T-SQL Sums and Averages
It’s a curious thing about SQL that the SUM
or AVG
of no items (an empty set) is not zero, it’s NULL
.
In this post, you’ll see how this means your SUM
and AVG
calculations might run at half speed, or worse. As usual though, this entry is not so much about the result, but the journey we take to get there.
Wednesday 23 February 2011
Advanced TSQL Tuning: Why Internals Knowledge Matters
There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes. Query tuning is not complete as soon as the query returns results quickly in the development or test environments.
In production, your query will compete for memory, CPU, locks, I/O, and other resources on the server. Today’s post looks at some tuning considerations that are often overlooked, and shows how deep internals knowledge can help you write better T-SQL.
Saturday 19 February 2011
I see no LOBs!
Is it possible to see LOB (large object) logical reads from
STATISTICS IO
output on a table with no LOB columns?
I was asked this question today by someone who had spent a good fraction of their afternoon trying to work out why this was occurring — even going so far as to re-run DBCC CHECKDB
to see if corruption was the cause.
The table in question wasn’t particularly pretty. It had grown somewhat organically over time, with new columns being added every so often as the need arose.
Nevertheless, it remained a simple structure with no LOB columns — no text
or image,
no xml
, no max
types — nothing aside from ordinary integer
, money
, varchar
, and datetime
types.
To add to the air of mystery, not every query that ran against the table would report LOB logical reads — just sometimes — but when it did, the query often took much longer to execute.
Thursday 17 February 2011
Seeking Without Indexes
A seek can contain one or more seek predicates, each of which can either identify (at most) one row in a unique index (a singleton lookup) or a range of values (a range scan).
When looking at an execution plan, we often need to look at the details of the seek operator in the Properties window to see how many operations it is performing, and what type of operation each one is.
As seen in the first post of this mini-series, When is a Seek not a Seek? the number of hidden seeking operations can have an appreciable impact on performance.
So…is it a Seek or a Scan?
You might be most familiar with the terms ‘Seek’ and ‘Scan’ from the graphical plans produced by SQL Server Management Studio (SSMS). You might look to the SSMS tool-tip descriptions to explain the differences between them:
Both mention scans and ranges (nothing about seeks) and the Index Seek description maybe implies that it will not scan the index entirely (which isn’t necessarily true). Not massively helpful.
Wednesday 16 February 2011
When is a Seek not a Seek?
The following script creates a single-column clustered table containing the integers from 1 to 1,000 inclusive.
IF OBJECT_ID(N'tempdb..#Test', N'U') IS NOT NULL
BEGIN
DROP TABLE #Test
END;
GO
CREATE TABLE #Test
(
id integer PRIMARY KEY CLUSTERED
);
INSERT #Test
(id)
SELECT
V.number
FROM master.dbo.spt_values AS V
WHERE
V.[type] = N'P'
AND V.number BETWEEN 1 AND 1000;
Let’s say we are given the following task:
Find the rows with values from 100 to 170, excluding any values that divide exactly by 10.
Tuesday 14 December 2010
Beware Sneaky Reads with Unique Indexes
I saw a question asked recently on the #sqlhelp hash tag:
Might SQL Server retrieve (out-of-row) LOB data from a table, even if the column isn’t referenced in the query?
Leaving aside trivial cases like selecting a computed column that does reference the LOB data, one might be tempted to say that no, SQL Server does not read data you haven’t asked for.
In general, that is correct; however, there are cases where SQL Server might sneakily read a LOB column.
Friday 10 December 2010
Heaps of Trouble?
Brad Schulz recently wrote about optimizing a query run against tables with no indexes at all. The problem was, predictably, that performance was not very good. The catch was that we are not allowed to create any indexes (or even new statistics) as part of our optimization efforts.
In this post, I’m going to look at the problem from a different angle, and present an alternative solution to the one Brad found.
Thursday 4 November 2010
Myth: SQL Server Caches a Serial Plan with every Parallel Plan
Many people believe that whenever SQL Server creates an execution plan that uses parallelism, an alternative serial plan is also cached.
The idea seems to be that the execution engine then decides between the parallel and serial alternatives at runtime. I’ve seen this on forums, in blogs, and even in books.
In fairness, a lot of the official documentation is not as clear as it might be on the subject. In this post I will show that only a single (parallel) plan is cached. I will also show that SQL Server can execute a parallel plan on a single thread.
Monday 1 November 2010
The Case of the Missing Shared Locks
This post covers a little-known locking optimization that provides a surprising answer to the question:
If I hold an exclusive lock on a row, can another transaction running at the default read committed isolation level read it?
Most people would answer ‘no’, on the basis that the read would block when it tried to acquire a shared lock. Others might respond that it depends on whether the READ_COMMITTED_SNAPSHOT
database option was in effect, but let’s assume that is not the case, and we are dealing simply with the default (locking) read committed isolation level.
Tuesday 19 October 2010
Sequence Tables
It is frequently useful to generate sequences of values within SQL Server, perhaps for use as surrogate keys. Using the IDENTITY
property on a column is the easiest way to automatically generate such sequences:
CREATE TABLE dbo.SomeTable
(
row_id integer IDENTITY PRIMARY KEY,
[data] sql_variant NOT NULL,
);
Sometimes though, the database designer needs a more flexible scheme than is provided by the IDENTITY
property. One alternative is to use a Sequence Table.
Thursday 23 September 2010
A Tale of Two Index Hints
If you look up Table Hints in the official documentation, you’ll find the following statements:
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.
If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
The interesting thing there is that both hints can result in a scan. If that is the case, you might wonder if there is any effective difference between the two.
This blog entry explores that question, and highlights an optimizer quirk that can result in a much less efficient query plan when using INDEX(0)
. I’ll also cover some stuff about ordering guarantees.
Wednesday 1 September 2010
Inside the Optimizer: Plan Costing
A detailed look at costing, and more undocumented optimizer fun.
The SQL Server query optimizer generates a number of physical plan alternatives from a logical requirement expressed in T-SQL. If full cost-based optimization is required, a cost is assigned to each iterator in each alternative plan, and the plan with the lowest overall cost is ultimately selected for execution.
Friday 27 August 2010
Sorting, Row Goals, and the TOP 100 Problem
When you write a query to return the first few rows from a potential result set, you’ll often use the TOP
clause.
To give a precise meaning to the TOP
operation, it will normally be accompanied by an ORDER BY
clause. Together, the TOP…ORDER BY
construction can be used to precisely identify which top ‘n’ rows should be returned.
Sunday 22 August 2010
Row Goals and Grouping
You might recall from Inside the Optimizer: Row Goals In Depth that query plans containing a row goal tend to favour nested loops or sort-free merge join over hashing.
This is because a hash join has to fully process its build input (to populate its hash table) before it can start probing for matches on its other input. Hash join therefore has a high start-up cost, balanced by a lower per-row cost once probing begins.
In this post, we will take a look at how row goals affect grouping operations.
Wednesday 18 August 2010
Inside the Optimizer: Row Goals In Depth
Background
One of the core assumptions made by the SQL Server query optimizer cost model is that clients will eventually consume all the rows produced by a query.
This results in plans that favour the overall execution cost, though it may take longer to begin producing rows.
Saturday 14 August 2010
Viewing Another Session’s Temporary Table
Is it possible to view the contents of a local temporary table, created on a different connection?
Say we run this code on connection 1:
CREATE TABLE #Private
(
[data] nvarchar(30) NOT NULL
);
GO
INSERT #Private
(
[data]
)
VALUES
(
N'Only I can see this'
);
Is there a way to see the contents of the #Private
table from connection 2?
It isn’t particularly easy, but a user with sysadmin
permissions, a bit of internal knowledge (and some patience) can do it.
Wednesday 11 August 2010
The Impact of Non-Updating Updates
From time to time, I encounter a system design that always issues an UPDATE
against the database after a user has finished working with a record — without checking to see if any of the data was in fact altered.
The prevailing wisdom seems to be “the database will sort it out”. This raises an interesting question: How smart is SQL Server in these circumstances?
In this post, I’ll look at a generalisation of this problem: What is the impact of updating a column to the value it already contains?
The specific questions I want to answer are:
- Does this kind of
UPDATE
generate any log activity? - Do data pages get marked as dirty (and so eventually get written out to disk)?
- Does SQL Server bother doing the update at all?
Thursday 5 August 2010
Iterators, Query Plans, and Why They Run Backwards
Iterators
SQL Server uses an extensible architecture for query optimization and execution, using iterators as the basic building blocks.
Iterators are probably most familiar in their graphical showplan representation, where each icon represents a single iterator. They also show up in XML query plan output as RelOp
nodes:
Each iterator performs a single simple function, such as applying a filtering condition, or performing an aggregation. It can represent a logical operation, a physical operation, or (most often) both.
Wednesday 4 August 2010
An Interesting MERGE Bug
Investigating an optimizer transformation that exposes a bug in SQL Server’s MERGE
implementation.
Saturday 31 July 2010
Inside the Optimizer: Constructing a Plan – Part 4
More undocumented ways to explore how the query optimizer works.
Inside the Optimizer: Constructing a Plan – Part 3
Presenting an undocumented Dynamic Management View we can use to identify the optimization rules involved in producing an executable plan.
Thursday 29 July 2010
Inside the Optimizer: Constructing a Plan - Part 2
Continuing the series of posts looking at how the optimizer matches and applies internal rules to refine a query plan.
The last post ended with this query plan:
The optimizer has pushed the predicate ProductNumber LIKE 'T%'
down from a Filter to the Index Scan on the Product
table, but it remains as a residual predicate.
Inside the Optimizer: Constructing a Plan - Part 1
For today’s entry, I thought we might take a look at how the optimizer builds an executable plan using rules. To illustrate the process performed by the optimizer, we will configure it to produce incrementally better plans by progressively applying the necessary rules.
Wednesday 28 July 2010
Ranking Function Optimizer Transformations
In my last post I showed how SQL Server 2005 and later can use a Segment Spool to implement aggregate window functions and the NTILE
ranking function.
The query optimizer is also smart enough to recognise that some queries are logically equivalent to a window function, even if they are written using different syntax.
Partitioning and the Common Subexpression Spool
SQL Server 2005 introduced the OVER
clause to enable partitioning of rowsets before applying a window function. This post looks at how this feature may require a query plan containing a ‘common subexpression spool’. This query plan construction is required whenever an aggregate window function or the NTILE
ranking window function is used.
The Segment and Sequence Project Iterators
In my last post I promised to cover the Segment iterator in more detail, so here we go.
Segment
The Segment iterator partitions rows into groups as they flow through a query plan, checking whether the current row belongs in the same group as the previous row. For this to work, the incoming rows must be presented in an order which guarantees that all members of a group are received sequentially.
Segment has a “Group By” argument to specify how it should partition its input. The iterator adds an additional column to the rows that flow through it. This new column is used to communicate with its parent iterator, and is named something like [Segment1003]
.
The new column is visible in the graphical query plan properties window, or by hovering your mouse cursor over the Segment operator. The attribute name in both cases is Segment Column
).
The “Segment Top” Query Optimization
A question that often comes up on the forums is how to get the first or last row from each group of records in a table. This post describes a clever query plan optimisation that SQL Server can use for these types of query.