Books Online has this to say about page splits: When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row.  This reorganization is known as a page split.  A page split makes room for new records, but can take time to perform and is a resource ...

The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the same change reduced performance by around 70% (on 64-bit systems – more on that later).  ...

You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when ...

If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

Summary: A detailed look at costing, and more undocumented optimizer fun. The SQL Server query optimizer generates a number of physical plan alternatives from a logical requirement expressed in T-SQL.  If full cost-based optimization is required, a cost is assigned to each iterator in each alternative plan, and the plan with the lowest ...

When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.  To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause.  Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned. The ...