Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gregory Alford]

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
——————————

[Brad Prendergast]

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
——————————
——————————————-

[Heather Walters]

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.

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

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

[Gregory Alford]

Thanks Heather, but that doesn’t allow me to export to Excel.

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

[Ram Mohan]

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
——————————
——————————————-