I do come across a lot of times when the beginners
omit on checking the table size before making an alteration at table level. It is
obvious that the size of the table is completely linked with the performance of
your query. Additionally, in production servers, checking the table structure should
be considered a mandated step. For instance, applying changes like the addition of an
index can impact the server performance drastically.
Let’s look at the two ways to find the biggest table in a database:
1. By using a query to gather the table size information
SELECT
t.name AS
TableName,
i.name as
indexName,
sum(p.rows) as
RowCounts,
sum(a.total_pages) as
TotalPages,
sum(a.used_pages) as
UsedPages,
sum(a.data_pages) as
DataPages,
(sum(a.total_pages) * 8) / 1024
as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024
as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024
as DataSpaceMB
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
i.object_id > 255 AND
i.index_id <= 1
--and t.name like '%your table
name%' --if desired to take just for few tables
GROUP BY
t.name, i.object_id, i.index_id, i.name
ORDER BY
object_name(i.object_id)
2. By using GUI to gather the table
size information
Please follow the below steps in
your SSMS
1. Right-click on the desired “database
name” and hover on the “Reports” option.
2. From the “Report” option, hover on “Standard
Reports” and finally select “Disk Usage by Top Tables”.
Once the “Disk Usage by Top Tables” option has been selected, you
will find a report something as below.
This report can be imported to a table and use as a weekly monitoring
report with respect to the table size.
0 comments:
Post a Comment