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.
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.
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.
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.
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')ISNOTNULLBEGINDROPTABLE#TestEND;
GO
CREATETABLE#Test(
id integerPRIMARYKEYCLUSTERED);INSERT#Test(id)SELECT
V.number
FROM master.dbo.spt_values AS V
WHERE
V.[type]= N'P'AND V.number BETWEEN1AND1000;
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.
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.
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.
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.
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.