THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Can a SELECT query cause page splits?

Published Tuesday, August 30, 2011 7:46 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Thiago Dantas said:

Thanks for the article Paul. Really interesting material. Will surely check out XEvents on CTP3 now

August 29, 2011 2:22 PM
 

Fabiano Amorim said:

I was wondering if we could do something to force a sort to avoid these page splits on index spool. What do you think?

As always, very nice post. indeed, It's much better to use XE on CTP3 :-).

August 29, 2011 6:56 PM
 

Paul White said:

Hi Fabiano,

Yes we can often rewrite the query to achieve an explicit sort before the index spool by using unsupported tricks like a subquery with TOP (BIGINT.Max) ... ORDER BY <spool index columns>, but should we have to?

We could also manually edit the XML show plan and use a plan guide with a 'real' plan obtained by using that as a USE PLAN hint, but this is even more work and forces a plan shape that might not always be ideal.

The other main option is to remove the need for the spool by providing a better access path (e.g. by improving the indexes on the base tables), but this isn't always possible.

Paul

August 30, 2011 2:46 AM
 

Hiriyo said:

Hi Paul,

First of all I would like to thank you for sharing your knowledge and ideas to the Index based stuffs. I am regular reader of your Blogs. If possible could you please provide me a doc file for your all blogs related to Index performance posted after July 2010. I know its a laborious task but we all will definitely appreciate it.

Thanks again..

September 9, 2011 2:08 AM
 

Rob Volk said:

Jonathan has struck again and found a way to identify mid-page splits, if anyone's interested:

http://sqlskills.com/blogs/jonathan/post/Tracking-Problematic-Pages-Splits-in-SQL-Server-2012-Extended-Events-e28093-No-Really-This-Time!.aspx

December 15, 2011 12:06 PM
 

Paul White: Page Free Space said:

This is a post for T-SQL Tuesday #43 hosted by my good friend Rob Farley . The topic this month is Plan

June 11, 2013 5:00 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement