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
more ▼

asked Mar 19, 2014 at 08:58 PM in Default

avatar image

sqlLearner 1
972 43 51 57

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 20, 2014 at 04:05 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 19, 2014 at 08:58 PM

Seen: 516 times

Last Updated: Mar 20, 2014 at 04:05 AM

Copyright 2018 Redgate Software. Privacy Policy