17

Can I use T-SQL to show me the number of 8K pages that a table is using to store its rows?

Also, can I see the number of 8K pages that a database is using?

PingPing
  • 899
  • 6
  • 15
  • 28

2 Answers2

29

Try something like this:

-- Total # of pages, used_pages, and data_pages for a given heap/clustered index
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalPages, 
    SUM(a.used_pages) AS UsedPages, 
    (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

That shows you the pages used by the tables - used, unused and total.

For the whole database - just sum up the pages for each table and repeat for each database .

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Does this mean that we can use this query to increase the fill factor or indexes for tables that have higher un utilized space. ? – singhswat Jan 17 '17 at 12:09
  • 1
    @singhswat your fill factor should almost ALWAYS be 100... there are many misconceptions on leaving some room for page splits, etc but all you are doing is making things worse with internal fragmentation. – S3S Oct 09 '18 at 13:11
0

One more way from SQLSERVER2012 with out showing count of rows..My search for some question lead me to here and i updated the answer with the one i used

select 
object_name(object_id) as 'tablename',
count(*) as 'totalpages',
sum(Case when is_allocated=0 then 1 else 0 end) as 'unusedPages',
sum(Case when is_allocated=1 then 1 else 0 end) as 'usedPages'
from sys.dm_db_database_page_allocations(db_id(),null,null,null,'DETAILED')
group by
object_name(object_id)
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94