The developers just released some code and added a bunch of indexes and now I am seeing all my DATA drives become low on space. How can I find out the cause of this growth, any advice would be great? I used this query but not sure if I am on the right path: SELECT t.name, au.* FROM sys.allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.partition_id INNER JOIN sys.tables t ON p.object_id = t.object_id ORDER BY au.total_pages desc
That's as good a start as any. I have used this in the past: SELECT OBJECT_NAME(p.object_id) AS object_name , i.name AS index_name , ps.in_row_used_page_count FROM sys.dm_db_partition_stats ps JOIN sys.partitions p ON ps.partition_id = p.partition_id JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id Indexes are subsets of your data. They add space and overhead. Ask the developers what indexes they added and then look at the data types involved, then count the rows in the table. You may be surprised at A) how much data needed to be duplicated to support the indexes, B) How much data is now entered in multiple storage structures on each insert. Indexing is a science all to itself - they speed up queries but come at a price.