File Size Metrics

File Size Metrics
Follow
Gregory Alford
Gregory AlfordJul 29, 2022 05:39 PM
Our CFO would like a pie chart showing what the data storage usage is by table. For example, I can go …

1. File Size Metrics

TOP CONTRIBUTOR
Gregory Alford
Posted Jul 29, 2022 05:39 PM
Our CFO would like a pie chart showing what the data storage usage is by table. For example, I can go into SQL Server and the table properties will tell me that I am using 400 MB of disk space for the Item Ledger Entries and another 623 MB for the Item Ledger Entries Indexes. However, I do not want to go through each table one at time to get these metrics. Does anyone know of a tool that will go through all of the tables and give us this information so that it can be dumped into a spreadsheet? I know there is a standard report, but I need it in Excel format.

——————————
Gregory Alford
Director of Technical Resources
Tri Star Metals LLC
Aurora IL
——————————

2. RE: File Size Metrics
Brad Prendergast
Posted Aug 01, 2022 07:42 AM
Hi,
If you can run a query against the SQL Server you can access this information with a query:
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name

If you do not have access to the SQL Server, you can review the data with a page that has the Table Information table as a source.

——————————
Brad Prendergast
——————————

3. RE: File Size Metrics
SILVER CONTRIBUTOR
Heather Walters
Posted Aug 01, 2022 09:36 AM
Adding onto Brad’s reply, this data can be accessed in SQL Server Management Studio by right-clicking the database name > Reports > Standard Reports > Disk Usage by Table.

SSMS Report Screenshot

(I am on SQL 2014, the menus might be a bit different in later versions)

——————————
Heather Walters
Apothecary Products
Burnsville MN
——————————

4. RE: File Size Metrics
TOP CONTRIBUTOR
Gregory Alford
Posted Aug 01, 2022 09:42 AM
Thanks Heather, but that doesn’t allow me to export to Excel.

——————————
Gregory Alford
Director of Technical Resources
Tri Star Metals LLC
Aurora IL
——————————

5. RE: File Size Metrics
Ram Mohan
Posted Aug 02, 2022 10:29 AM | view attached (2)
One can use SQL Mantra Tool to find the tables size and more.

By running [Analyse_DynamicsNAV_Table_Size] SQL Procedure from SQL Mantra Tool one can see table size, number of records, SIFT size, number of index, number of SIFT, NAV/BC table number, Extension it belongs to, number of updates, inserts, deletes, along with tables size by Company, etc…

Further this is the only tool you would need to effectively troubleshoot performance issues such as Blocking, Deadlocks, Slow running process / screen as well as maintain the system free from performance issues. It will give CAL and AL code, SQL code causing the above performance issues as well as the NAV/BC user causing these performance issues. Their maintenance module will maintain a busy and big NAV/BC database in a healthy state by proactively maintaining the indexes in a healthy state.

Visit the website (see below) and download the user manual and the brochure for more detail.

SQL Mantra Tools – The SQL Performance Tuning Experts
SQL Mantra Tools remove preview

SQL Mantra Tools – The SQL Performance Tuning Experts
SQL Mantra Tools is your one stop shop for all the tools you need for better performance. Why waste money on upgrading your application, while you can tune it to improve it’s speed, scalability, concurrency and extend it’s life for a fraction of the cost ?.
View this on SQL Mantra Tools >
)

#Implementation,#Performance,#Architecture and Development
​

——————————
Ram Mohan
Grays
——————————

Attachment(s)
pdf
SQL Mantra tools flyer.pdf
pdf
SQL Mantra Tool User Manual.pdf