Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2

When you compress an object (index or table or partition), there are two components of space savings that you get. First component is fragmentaton (i.e. the original object might have been fragmented). The object gets degragmented as part of doing compression so you get some space savings. Second component is actual data compression savings. Many customers have asked the… Read more

Example: Index fragmentation with insert/updates, measuring it and fixing it

This blog shows a very simple example to illustrate data fragmentation, measuring it, seeing behind the scene data and steps to address it. I recommend reading the overall series on index fragementation at     — TSQL Script create database indextest go     use indextest go     — create the index after loading… Read more

How can you tell if an index is being used?

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.   If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a… Read more

More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

(Final blog post of the year for me. Its been a bit of a wild ride the last 6 months – 7 TechEds on 3 continents, 46 blog posts and some major life changes – but now things have calmed down and I should be back to more regular posting in 2007. Tomorrow I fly out to… Read more

When can allocation order scans be used?

I know this is jumping the gun a little as I haven’t made it this far in my series on fragmentation, but this came up in a chalk-talk I did yesterday at TechEd Developers in Barcelona and is worth blogging about. You’d expect a select * from mytable query on a table with a clustered… Read more

Fragmentation (part 4): what are heaps?

Ok – really catching up with the various blog post series I started back in June/July – time to bang out the next few posts in the series on index fragmentation. Remember I’m starting from first principles and covering What are records? What are pages? What are extents? What is a heap? What is a… Read more

Fragmentation (part 3): What are extents?

(Ok – another flight – another blog post. This time its Boston back to Seattle. The three of us who’d come over for the training course upgraded to 1st Class on Alaska for the flight back (great value at $100 for a 6-hour flight) and so there’s oodles of room for laptops, newspapers, and long… Read more

Fragmentation (part 2): What are pages?

(Boston continues its reputation – with me at least – for great seafood – calamari and pan-seared fresh halibut this evening. Yum! I was tempted to get on the T – Boston’s subway system – and see what’s happening downtown but with no jacket I’d get wet. So, instead I’ll be boring and geeky and… Read more

Fragmentation (part 1): What are records?

This blogging thing sucks you in, doesn’t it? Not content with having an ongoing series on disaster recovery and CHECKDB (with another 6 and 25 more posts planned respectively), I’m starting a new series on fragmentation. This will begin from first principles and work up, in approximately 18 posts over the next few months. The… Read more