BlogmyQuery – BMQ

A question we are frequently asked is what happens when an update statement assigns a column to its same current value. For example,

use tempdb

go

create table t(i int, cc as i + 1)

create index t_cc on t(cc)

go

insert into t values(1)

go

update t set i = 1

go

update t set i = i

go

SQL is a declarative language that returns multi-sets (sets allowing duplicates) of rows. The operations exposed in SQL, such as join, filter, group by, and project, do not inherently have any ordering guarantees. ANSI SQL does expose an ORDER BY clause for the top-most SELECT scope in a query, and this can be used to return rows in a presentation order to the user (or to a cursor so that you can iterate over the results of query). This is the only operation in ANSI SQL that actually guarantees row order.

Microsoft SQL Server provides additional ordering guarantees beyond ANSI, mostly for backwards-compatibility with previous releases of the product. For example, variable assignment in the top-most SELECT list when an ORDER BY is specified is done in the presentation order of the query.

Example:

SELECT @a = @a + col FROM Table ORDER BY col2;

The Optimizer model makes several assumptions when making plan choice decisions. These decisions can be false for particular queries and data sets, and sometimes this can cause plan issues. One such problem is called “The Sorted Seek Problem” by the Optimizer Team, and it affects plan selection, usually between clustered index or heap scan plans and bookmark lookup plans.

When costing a series of bookmark lookup operations (for a heap) or index seek operations (for an index), the costing code will effectively treat these as a series of random I/Os. These are much more expensive than sequential I/Os because the disk arm needs to be moved and the disk needs to spin around to find the right sector(s). When the correct page is found, it is loaded into the buffer pool and the engine will cache it for future references. The costing code also understands, on average, how often a page is likely to be found on subsequent operations in the cache, and it costs these accesses much more cheaply than the original access.

(continue reading...)

I saw a post in one of the newsgroups today that referenced a piece of information Microsoft published on how the Optimizer makes decisions about remoting certain operations.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6oxf.asp

It's slightly out-of-date, and I'll work on trying to get it updated.  Specifically, SQL 2005 will remote uniqueidentifiers (and it will support statistics over them as well).  We'll remote queries from SQL 2005 to SQL 2000 that contain filters comparing columns to constant uniqueidentifier values as well.

We published a research paper last year on how the Distributed Query feature works in more detail.  While it does not cover every practical detail of the implementation, you may find it as an interesting reference if you use distributed queries.

http://citeseer.ist.psu.edu/732761.html

If you have other remoting questions/problems, please post comments on them and we'll see if we can get them answered for you.

Thanks,

Conor

Source : http://blogs.msdn.com/queryoptteam/default.aspx