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).
In my last post, Enforcing Uniqueness for Performance, I showed how using a unique index could speed up equality seeks by around 40%.
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.
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.
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:
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).
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.
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
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.
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.