About This Blog

SQL Server internals information including my content from SQLBlog.com and SQLPerformance.com.

Thursday, 7 July 2011

Bitmap Magic (or… how SQL Server uses bitmap filters)

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:

Tables #BuiltInt and #Probe

Saturday, 2 July 2011

Undocumented Query Plans: The ANY Aggregate

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:

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

Undocumented Query Plans: Equality Comparisons

The diagram below shows two data sets, with differences highlighted:

Two data sets with differences highlighted

To find changed rows using T-SQL, we might write a query like this:

Query to find changed rows

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:

Only one changed row found

The problem is that our query does not correctly handle NULLs.

Saturday, 5 March 2011

How Parallelism Works in SQL Server

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

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

Advanced T-SQL 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!

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

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?

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:

Scan and Seek tooltips

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?

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.