What you should know if you are using Logical IOs to measure query plan quality

One of the presenters at recent SQL Server Open World conference in Denmark (http://www.miracleas.dk/index.asp?page=168&page2=323)  suggested tuning SQL queries using logical IOs to measure quality of query plan. The IO count is returned for each object accessed in your query after you issue “set statistics IO on”. Here is an example of returned message: Table ‘t1’…. Read more

Previously committed rows might be missed if NOLOCK hint is used

I received a question from one of our customers about using the NOLOCK hint: can it cause missing rows in scans even if the rows were committed well before my SELECT with NOLOCK starts?   The NOLOCK hint is employed by many users to avoid contention on tables where updates are performed concurrently with selects…. Read more

Partition Elimination in SQL Server 2005

Lubor Kollar   Partition elimination is very important when SQL Server executes queries against partitioned tables or partitioned views. In general, SQL Server is doing an excellent job of not scanning the partitions that are excluded by some predicates. Recently we have discovered one scenario where the partition elimination does not work against partitioned tables… Read more

Achieving Selective Uniqueness in SQL Server Tables

SQL Server development group is repeatedly getting questions, suggestions and requests related to supporting unique indexes allowing multiple NULLs in SQL Server.   The SQL Standard requires that a column—or a set of columns—which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional… Read more