In the previous blog post we have explored how to tell how many records do we have per company account using a T-SQL Query. But AX does have a concept which SQL cannot cope with, and makes it a bit harder to tell the active record count in ValidTimeState tables.
As part of our Data Upgrade from AX 2012 to Dynamics 365 Finance and Operations we had to ensure a way of telling how many records do we have per data area. Data import via entities are company-based, so we had to find out a reliable way to tell if everything has been successfully transferred. The following script can tell exactly that, show the SQL record count by company:
SET NOCOUNT ON
DECLARE @tableName NVARCHAR(255);
DECLARE @statement NVARCHAR(MAX);
-- Temporary table for storing record counts
CREATE TABLE #jdm_count (TableName NVARCHAR(255), Company NVARCHAR(4), RecordCount int)
-- Cursor for getting list of User-created tables
DECLARE cur_name CURSOR FOR
SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name)
FROM sys.objects as sOBJ
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name;
OPEN cur_name
-- Loop tables
FETCH NEXT FROM cur_name INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Construct SQL Statement for getting company-specific record count
SELECT @statement = 'SELECT ''' + @tableName + ''' AS [TableName]'
+ IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ', DATAAREAID AS [Company]', ', '''' AS [COMPANY]')
+ ',COUNT(*) AS [RowCount] FROM ' + @tableName + ' WITH (NOLOCK)'
+ IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ' GROUP BY [DATAAREAID]', '')
+ ' HAVING COUNT(*) > 0';
-- Insert statement results in temporary table
INSERT INTO #jdm_count (TableName, Company, RecordCount)
EXEC sp_executeSQL @statement;
FETCH NEXT FROM cur_name INTO @tableName
END
CLOSE cur_name
DEALLOCATE cur_name
-- Display results
SELECT * FROM #jdm_count
ORDER BY RecordCount DESC, TableName, Company
DROP TABLE #jdm_count
SET NOCOUNT OFF
The Document Attachments were stored in our AX 2012 database, and were occupying several hundred GBs. As part of the migration to D365FO we had to export these files in bulk. The most efficient way is to run the batch task in parallel. But this data is tilted in a way that if you would group the tasks based on their creation date, it the first tasks would barely have any records to process while the last ones would go on forever. The solution for this is called data histogram equalization.
It would be a big challenge to code this in X++, but SQL Server has a function for doing this exactly: NTILE.
The following direct query is able to process the data histogram, and then return 10 buckets of Record Identifier ranges of roughly equal size:
WITH documents (Bucket, RecId)
AS (
SELECT NTILE(10) OVER( ORDER BY DocuRef.RecId) AS Bucket
,DocuRef.RECID
FROM docuRef
INNER JOIN docuType
ON (docuType.dataAreaId = docuRef.RefCompanyId OR DOCUREF.REFCOMPANYID = '')
AND docuType.TypeId = docuRef.TypeId
INNER JOIN docuValue
ON docuValue.RecId = docuRef.ValueRecId
WHERE docuType.TypeGroup = 1 -- DocuTypeGroup::File
AND docuType.FilePlace = 0 -- DocuFilePlace::Archive
AND docuType.ArchivePath <> ''
AND docuValue.Path = ''
)
SELECT Bucket
,count(*) AS Count
,(SELECT MIN(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_From
,(SELECT MAX(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_To
FROM documents
GROUP BY Bucket
Here are the results for spawning 10 batch job tasks to do parallel execution based on the RecId surrogate key index, with ~57230 rows in each bucket. This allows you to evenly distribute the load for data processing in parallel.
If we would export our document attachments sequentially, it would take roughly 40 hours total. By utilizing the data histogram equalization we could get it down to be under 3 hours. That is amazing!
It is a great way to split ranges of data for parallel processing, to keep in mind for the future.
If you have ever encountered SQL Server Jobs which looks like a GUID value, you might have wondered what they are used for.
Our AX 2012 deployment is using a SQL Server Reporting Services instance, which is used for two purposes:
AX 2012 SSRS reports
Custom reporting from our Data warehouse
These jobs are actually periodically generated SSRS reports scheduled by our staff. Each job belongs to a different person and report.
By just having a look at the job itself does not reveal what is actually being executed, but the following T-SQL script can extract the details when you run it against the ReportServer database:
SELECT
c.Name AS ReportName,
S.ExtensionSettings,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(100)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(100)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="BCC"])[1]','nvarchar(100)') as [BCC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
/*,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]*/
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
Now we have the details of:
what reports were scheduled
the report format
execution status
schedule and last successful run time.
This should give you a better overview about what are the SQL Server Jobs which looks like a GUID value in your database instance.
Once a company has been live for a while and they are doing Business As Usual, they often forget about maintaining one critical area. The system is using Number Sequences as identifiers, for which we have fixed, allocated range of values. Typical example is a general financial journal with a format like JNL18-###### with values between 000001 and 999999. Number Sequence consumption monitoring is essential!
If the sequence reaches the maximum value, you are no longer able to create new financial journals. Sometimes it is obvious when a number is about to run out, but in many cases AX has it hidden on a transactional level that is not necessarily visible for the users. When the sequence runs out, it can cause serious issues:
database locking
error messages
rolled back transactions.
Proactive monitoring is key to a healthy ERP system on many levels. Number sequence consumption monitoring is no exception. We are running a Transact-SQL script that keeps tracking of the number sequence utilization, and sends out an e-mail with entries reaching a set threshold. We are running the job based on a weekly schedule. We include any sequences that have used up at least 70% of their available range.
