Navigation: Tools >> Utilities >> Manufacturing >> MFG Data Inquiry
MFG Data Inquiry provides a set of database health and volume analysis tools to help you monitor the status of the manufacturing system.
Report: Data Size / View: Tables
This report (shown above) lists all of the manufacturing tables covered by MFG Data Archive. It shows the number of rows in each table, and the amount of space required for data storage and for the indexes.
A SQL Index can be thought of like a table of contents. It summarizes the details, and provides pointers (page numbers) to get you quickly to the information you need. A database table can have multiple indexes which provide different methods of summarizing the data, and each index takes up additional space in the database (just like additional tables of contents would require additional pages in a book).
Removing manufacturing orders reduces both the data size and the index size.
The window is sortable by clicking on the column headers.
Report: Data Size / View: Statistics
The Statistics report provides several different measurements to help you track and monitor the size and growth rate of the manufacturing data. This information is helpful for planning archiving frequency.
|Total MOs||Total count of manufacturing orders in the Work Order Master (WO010032).|
|Total Data Rows||Total row count from all tables covered by the archive. There are manufacturing tables which are not archived (such as QA Test setup, Item Engineering, and Routings) that are not included.|
|Data||The total amount of disk space required for the manufacturing data.|
|Indexes||Total amount of disk space required for manufacturing table indexes. See the previous section for a discussion of Indexes.|
|Total on Disk||Total of Data plus Indexes.|
|Est. Per MO||Total on Disk / Total MOs.|
|Date First MO||Earliest Start Date on an MO in the Work Order Master (WO010032).|
|MO/month Last Mo.||Total MO’s with a Start Date in the prior month.|
|MO/month YTD||Total MO’s with a Start Date in the current year, divided by the number of months up to the current date.|
|MO/month R12||Total MO’s during the last 12-months, divided by 12.|
|MO/month All||Total MO’s in system, divided by number of months between earliest MO and the current date.|
|Growth per month||(Est. Per MO) * (MO/month R12)|
|Next GB in Days||Uses the Growth per month to estimate how long (in days) it will take to add a gigabyte of manufacturing data.|
The small listview window showing Years and kBData uses the Est. Per MO to gauge how much manufacturing data was added during each year.
Index fragmentation can have a significant adverse effect on performance. An index can be thought of like a table of contents in a book. It is a summary of the data in the book, with pointers (page numbers) to help you quickly locate what you are looking for. An index, like a table of contents, takes up space.
An index, like a table of contents, can use different methods to order the list, such as by Chapter or by Topic (such as a Glossary in a book). Each different method of summarizing and sorting the data is a different index and takes up additional disk space.
A fragmented index means that the data is not located contiguously on the hard-drive, so when SQL Server needs to locate information it needs to skip around on the drive to find it. The extra time required by this skipping can dramatically slow the time it takes Lookups to fill or reports to run.
The Fragmentation report shows the percent of fragmentation in each index for the tables covered by the archive. Under 20% is good, over 40% is bad. The Red/Yellow/Green dots correlated to those percentages.
If you have a high amount of fragmentation, consult with you Dynamics GP reseller regarding database maintenance to rebuild the indexes. Rebuilding indexes should be part of a scheduled maintenance routine on the SQL server.
Article is closed for comments.