August 2011 - Posts

• Can a SELECT query cause page splits?

  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 .  Read More... Posted Tuesday, August 30, 2011 7:46 AM by Paul White | 5 Comments Filed under: Query Plans, Undocumented, Performance, Page Splits

• SQL Server, Seeks, and Binary Search

  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 Read More... Posted Tuesday, August 09, 2011 10:10 AM by Paul White | 9 Comments Filed under: Internals, Undocumented, Indexes, Seeks, Performance

• Avoiding Uniqueness for Performance

  In my last post , I showed how using a index unique could speed up equality seeks by around 40%. For today’s entry, I’m going to use the same tables as last time (single BIGINT column, one table with a non-unique clustered index, and one table with a Read More... Posted Thursday, August 04, 2011 2:34 AM by Paul White | 14 Comments