SQL Query to find number of table rows stored in different partitions

Sometime if we are analysing production applications, we may need to check why some queries are taking more time. There are some case where all the data may be going to same database file which is against the partition strategy of the database. Below is the query which gets the details about how the table rows are distributed in partitions.

---

DECLARE @TableName sysname = 'Users';

SELECT p.partition_number, fg.name as FileGroup, p.rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID(@TableName)

---

We can directly evaluate the partition function as below to know what will be value of partition for a given value.This will also help us to determine which rows went to which partition. But the above is more handy to use.

---

$Partition.<partition name>(value)

---

Happy debugging.

Note: The credits for this query goes to my team mate who don't have a blog as of now. May be this will inspire him to start his blog :)