In my last post, Enforcing Uniqueness for Performance, I showed how using a unique index could speed up equality seeks by around 40%.
About This Blog
Thursday 4 August 2011
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.