Archive for April, 2006

Sorted Seeks

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...)

Optimizing Distributed Queries

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.

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.

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



Source :

  • Copyright © 1996-2010 BlogmyQuery - BMQ. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress