Why did my indexes drop in size?

Question

I've got the following SP which I wrote to calculate the size of all the tables on my database

SELECT 
    name,dbo.nice(rows) as [rows],
    dbo.nice(CAST(total-indexes as DECIMAL)/1024) as [data (MB)],
    dbo.nice(CAST(indexes as DECIMAL)/1024) as [indexes (MB)],
    dbo.nice(CAST(total as DECIMAL)/1024) as [total (MB)]
FROM (
   Select A.name,rows
   ,SUM(CASE B.indid WHEN 1 THEN B.used ELSE 0 END)*8 as [total]
   ,SUM(CASE B.indid WHEN 1 THEN 0 ELSE B.used END)*8 as [indexes]
   from sysobjects A (nolock), sysindexes B (nolock) 
   where A.id = b.id and A.type = 'u' and rows > 0
   group by A.name,rows 
) as [d]
order by total desc

I can't recall where I got this exactly. I wrote the outer part and found the inner part online somewhere.

Anyway, it does the trick. It calculates the size of my data by subtracting the indexes. I got a shock today when I ran the SP and compared it to a result from a few days ago

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 4 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // today

My data had jumped in size! After inspecting the SP source code and remember how it works I realise of course the data size did not change (much). What happened was the indexes drastically reduced in size.

What causes this? I didn't make an edits to the indexes. I didn't shrink the DB or run anything on it that should affect the indexes. Why did my indexes decrease so dramatically? And why didn't it affect the overall size of the database?

UPDATE:

I looked into my indexes and found one was seriously fragmented, at over 80%. I rebuilt it and now the data has jumped again. Here are the new stats.

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 5 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // yesterday
MyBigTable   241,705,941      35,581      32,538         68,119       // today

Answer 1

Three possibilities come to mind immediately:

• Maybe someone dropped an index? You should create a query that also breaks it down by index. A row missing from those results would be much more obvious than numbers changing.

• A filtered index, and a data change that substantially reduced the number of rows contained in the index.

• Fragmentation - was a defrag or rebuild run recently against this table or any of its indexes? This could drastically reduce the number of pages used by an index, even if the number of rows and the index itself have not changed.

Also, why didn't it affect the size of the database? Because the database is just a container, and it doesn't auto-shrink just because you delete data or shift data around (unless you have auto-shrink set, which you absolutely should not do!). Think about your house: if you clean up the clutter in your basement, your house doesn't shrink! You don't want your database to do this either. All this does is cause the data file to grow again as soon as you add more data. What could be more wasteful than that? You freed up some space, temporarily, only to make user transactions wait while you use it up again. Yay?

In any case, your overall data size seems to have remained the same, so I postulate that two things happened: you added a million rows that contributed significantly to data size, and some index maintenance happened with reduced the index size substantially. In the end, we can't tell you what caused this, because we don't have access to your system. We can only throw wild guesses into the air, and you have to figure out which ones stick.