I can't believe I haven't told this story from SQLPASS yet… and by the way, here's Paul Randal's blog on this same event/topic.
One of the few sessions I managed to attend was a really cool Grant Fritchey (blog, twitter) session on DMVs. He was talking about the DMV that includes index fragmentation data (sys.dm_db_index_usage_stats) and mentioned briefly that below a certain page level, he doesn't bother defragmenting. During the QnA (and Tweeting all the while) I asked Grant, "For you, how many pages does a table need to have before you start worrying about the fragmentation level?"
He answered that his lower cutoff is in the single digits, because defragmenting a table that small will make no difference at all. And "my [upper] cutoff is at about 100 pages, but Microsoft says 1,000 pages. But even a 30 page table can wreak havoc on a query if highly fragmented."
Paul Randal (blog, twitter) was very active on Twitter during the summit, and saw the tweets. He replied immediately: "#sqlpass 1000 is a number I made up when back at MS, but it only applies if the table is in memory. Otherwise, sure, defrag." I got to read that out while we were still in Grant's session, and it got a big laugh.
-Jen McCown