CODEWITHSUNDEEP
sql-serversql-server-2019
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

Is there a way to find the pages density on the page level in SQL Server (not avg page density)?

I can page density by using following query:

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_id,
    ips.index_type_desc,
    ips.page_count,
    ips.avg_page_space_used_in_percent AS AvgPageDensity
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.table'), NULL, NULL, 'DETAILED') AS ips
JOIN 
    sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
ORDER BY 
    ips.page_count DESC;

However it's showing the AVG page density and I would like to check how much each specific page is filled. Is it possible?

Upvotes: 0

Views: 69

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

While waiting for the answer, I've found pretty much what I needed. Prior SQL Server 2019 it can be checked by using DBCC PAGE command. However you can't use it in query and the output is PRINTed and it has a lot of info as well as the actual data in the page.

Starting from the SQL Server 2019 you can use sys.dm_db_page_info function. However it can be very resource intensive on the large tables, so use it very carefully in production systems.

Upvotes: 0

Related Questions