question

sqlLearner 1 avatar image
sqlLearner 1 asked

MDF File Growing

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
tsqlindexesmdf
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Blackhawk-17 avatar image
Blackhawk-17 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.