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

    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)]
   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?


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
  • CAST as DECIMAL with no precision/scale? Old-style joins? Old backward compatibility views? What version of SQL Server is this? What indexes are on MyBigTable and are any of them filtered? Also I hope you don't use dbo.nice() in production queries... – Aaron Bertrand Dec 6 '13 at 19:41
  • Thanks for the criticism. I only run this privately for admin queries. As I said I found the inner part online. No, I don't use dbo.nice() in production. All that function does is add thousand separators to the numbers so I don't see what your problem with it is. – roryok Dec 6 '13 at 19:46
  • For ad hoc stuff like this, I don't have a problem with dbo.nice(). Notice how I said in production queries - running that against the select list for every row in a query, just to add thousands separators, is terribly wasteful to perform in T-SQL, because it essentially forces SQL Server to go call the function for every row times however many times it's referenced in the select list. Do that "nice" formatting on the client, which has much more powerful formatting options, and has to loop through every row anyway. – Aaron Bertrand Dec 6 '13 at 19:58
  • Sorry I got a bit defensive. I'd never do anything like that in production of course! – roryok Dec 6 '13 at 20:05
  • You might consider looking at sp_spaceused. It will return you the size of the data, the size of the indexes, and the space allocated to the object but not currently used. That last may be part of what happened to you. – Kenneth Fisher Dec 7 '13 at 3:58

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.

  • I'm the only person with access to this DB at the moment, so nobody else could have dropped an index. In fact, I was thinking about dropping an index from exactly this table but haven't done it yet! It wasn't defragged or rebuilt (unless this happened automatically). A large number of rows are being removed by a background agent job, although they're being added at a higher rate (hence the increase in row count). I guess the second one makes the most sense – roryok Dec 6 '13 at 19:48
  • @roryok ... do you have any maintenance plans? what about filtered indexes? – swasheck Dec 6 '13 at 19:58
  • @roryok can you define "a large number"? And what is the criteria for deletion? Is it based on calendar months or something? If you provide more details, we can pull fewer teeth... – Aaron Bertrand Dec 6 '13 at 19:59
  • A large number in this case is about 20-40 million rows in the last 2-3 months. These are being removed based on an extracted list of pkids, which seems to be the most efficient way to delete from this table. This index drop is rather sudden though, coming in just 4 days as it has – roryok Dec 6 '13 at 20:08
  • You may have just happen to have deleted the last few rows on a large number of pages, and they've finally been deallocated, which may take a long time based on how the list of pk values is derived, and especially if you are doing no index maintenance. Why aren't you doing any index maintenance, particularly on a table with this much churn? – Aaron Bertrand Dec 6 '13 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.