{"id":172,"date":"2024-02-13T09:46:53","date_gmt":"2024-02-13T07:46:53","guid":{"rendered":"http:\/\/130.61.57.200\/?p=172"},"modified":"2024-10-02T10:54:18","modified_gmt":"2024-10-02T08:54:18","slug":"rows-and-space-per-partition","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2024\/02\/13\/rows-and-space-per-partition\/","title":{"rendered":"Rows and space per partition"},"content":{"rendered":"\n

SELECT o.name as table_name, rv.value as partition_range,<br>fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,<br>sum(au.total_pages) *<em>8\/1024.0 [Total MB],sum(au.used_pages)<\/em>*8\/1024.0 [Used MB]<br>FROM sys.partitions p<br>INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id<br>INNER JOIN sys.objects o ON p.object_id = o.object_id<br>INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id<br>INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id<br>INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id<br>INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number<br>INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id<br>LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id<br>group by o.name, rv.value,fg.name, p.partition_number;<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

SELECT o.name as table_name, rv.value as partition_range,<br>fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,<br>sum(au.total_pages) *<em>8\/1024.0 [Total MB],sum(au.used_pages)<\/em>*8\/1024.0 [Used MB]<br>FROM sys.partitions p<br>INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id<br>INNER JOIN sys.objects o ON p.object_id = o.object_id<br>INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id<br>INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id<br>INNER JOIN sys.partition_functions f ON […]<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[50],"class_list":["post-172","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql","tag-partitions"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=172"}],"version-history":[{"count":3,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/172\/revisions"}],"predecessor-version":[{"id":228,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/172\/revisions\/228"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}