3

Is there a query I can run to see the top disk space consuming tables for a given file in a file group?

Need to free up some disk space for a given SAN drive and need to identify which tables are using the most space for a couple of data files on that drive.

2 Answers 2

2

The tricky part is to get the info per file and object. Add the fact that the distribution might not be equal over the files. I.e., the traditional DMVs won't be useful.

But what if you crack the IAM pages? This will for each object tell you what pages the object uses, and on what file the page is. I.e., just throw in a WHERE clause where you specify the file you are interested in (or add the file to GROUP BY, depending what you want) and Bob's your uncle!

Here's something I threw together and it seems to do the trick. Try on a smaller database first, it can take a while to crack all IAM's for all objects.

(You can make it fancy by using a cursor and loop over only the objects you are interested in (the object_id is the second parameter), but since you are after the largest tables anyhow, I doubt that you'll find that worthwhile. )

SELECT OBJECT_NAME(a.object_id), COUNT_BIG(*) * 8192/(1024*1024) AS size_MB
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, null, null, 'LIMITED') AS a
WHERE a.extent_file_id = 1
GROUP BY OBJECT_NAME(a.object_id)
ORDER BY size_MB DESC

I suggest you also test it in a database where you don't have more than one file, just to verify that is seems to do the right thing. My tests gave me the right info, but those tests weren't exhaustive. They did however include both LOB pages and columnstore index (which are LOB pages in the end).

1
4

You may go to the report --> standard report and select Disk Usage by Top Table. This will be give you the table name in descending order which is used from maximum on disk to minimum on disk.

Alternatively, you may use the SQL below to get details:

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

Or this query:

SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                    ) AS data,
                SUM (ps.used_page_count) AS used
                FROM sys.dm_db_partition_stats ps
                GROUP BY ps.object_id
            ) AS a1

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'   
    order by a1.data desc         

Note: I have not tested these queries myself, confirming back whether it works or not would be appreciated.

This has been asked on multiple forums and I have used answers from here.

As far as files are concerned, you may need to list filegroups, if none are defined, all the allocations will take place in primary filegroup.

Below query could help you finding physical location:

SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid 
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Hope this helps.

6
  • @Verace - Apologies for editing again, I had to add details about files and filesgroup. Jan 1, 2021 at 17:25
  • Thanks but I don't see any info returned about the file group file in either of those queries. Jan 1, 2021 at 17:36
  • You shall need to check how many file groups are defined, first check which is the heaviest table and then check whuch file group does that table belong to. Your first priority should be to find list of heavy tables. Jan 1, 2021 at 17:53
  • 1 file group [Primary] with 8 different data files spread across 4 SAN drives. Jan 1, 2021 at 18:16
  • 1
    Edited my answer. Please check. Jan 1, 2021 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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