Paul and Kimberly public class in Boston April 2010

The first of our public classes (what we call Immersion Events) in 2010 is now officially open for registrations!

We've teamed up with our good friend Adam Machanic to bring a week-long custom class to the Boston area. In this class Kimberly and I take turns teaching modules and we're both on hand to answer questions, do research, and try things out (and banter too!). The tag-team approach works *really well* and make the class very enjoyable and relaxed for those attending (and for us!).

The class will be April 12-16 2010 in the Le Meridien hotel in Cambridge, MA and is $3100 for the week. If you use the discount code EARLYBIRD you'll get $600 off for registering before February 1st.

The class will cover:

  • On-disk structures: how the data is stored
  • Index internals: how the data is organized
  • Logging and recovery: how the data is protected
  • Choosing the RIGHT Data Type
  • Table & Index Partitioning Strategies
  • Data Access
  • Indexing Strategies
  • Data and log file provisioning and management
  • Tempdb
  • Index and statistics maintenance
  • Using backup and restore (plus internals)
  • Consistency checking and disaster recovery

You can see a much more detailed course outline here and full details including how to register at Adam's Boston SQL Training website.

We hope to see you there!

Where do the Books Online index fragmentation thresholds come from?

I made them up. Yup.

I’m talking about the guidance which is:

  • if an index has less than 1000 pages and is in memory, don’t bother removing fragmentation
  • if the index has:
    • less than 5% logical fragmentation, don’t do anything
    • between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE)
    • more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX … REBUILD)

These numbers are made up. They can and will vary for you, but they’re a good starting point to work from.

There’s been some discussion since PASS, when I confessed publicly on Twitter (during Grant Fritchey‘s session) to making them up, about whether I really said that etc etc. Yes – I really did make them up.

Back in 1999/2000 when I wrote DBCC INDEXDEFRAG and DBCC SHOWCONTIG for SQL Server 2000, customers wanted *some* guidance on what the thresholds should be where they should care about fragmentation or not, and how to remove it. We had to put *something* into Books Online (my favorite “it depends!” wouldn’t have been too helpful), so I talked to some customers, inside and outside Microsoft, did a bunch of experimentation, and chose these numbers as most appropriate at the time. So they’re not *really* made up – they were chosen carefully.

They’re not set in stone – they’re a big generalization, and there are a ton of other factors that may affect your choice of threshold and fragmentation removal method (e.g. recovery model, high-availability technologies in use, log backup schedule, query workload, disk space, buffer pool memory, and so on). I wish Microsoft would update the old whitepaper on fragmentation – they keep promising me they’ll get around to it.

In the meantime, take those numbers with a pinch of salt and don’t treat them as absolute.

Interesting 2008 partitioned view perf bug fixed in SP1 CU4

This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent.

The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes a TOP (1) operator which uses the right index, but in 2008 the optimizer rule was broken and the plan turned into a stream aggregate, much more expensive in this case.

Here's the 2005 query plan:

 

and here's the 2008 query plan (before the bug fix):

 

You can get the fix in CU4 for 2008 SP1 (or later) and read a bit more about it in KB 973255.

Note that you have to turn on trace flag 4199 to enable the fix – that requirement will be removed in SQL11.