Dynamics AX et SQL Resource Governor

Un de mes clients éprouvait des problèmes de performance avec leur serveur SQL. Après avoir utilisé le script de Glen Barry afin d'effectuer un diagnostique, il était évident que SQL souffrait de congestion au niveau de la mémoire et des disques. De plus, j’ai constaté que la majorité des ressources du serveur était utilisée par des  bases de données autre que MicrosoftDynamicsAX et MicrosoftDynamicsAX_model. Les instructions du client étaient claires: les bases de données de Microsoft Dynamics AX doivent avoir priorité sur les ressources.

Le client possède une architecture SQL AlwaysOn Availability groups avec 3 serveurs de réplication, dont un dans un leur site de recouvrement (DR). Les serveurs SQL sont hébergés sur la plateforme Azure dans un mode de déploiement Classic.  La taille des serveurs est déjà au maximum 8vCPU, 56GB et il est impossible pour le moment de migrer les serveurs dans le mode Azure Resource Manager (ARM). Impossible aussi pour l'instant de déplacer les autres bases de données.

C’est un excellent cas pour Resource Governor! C'est la première fois que j'utilisais Resource Governor parce que lors de toutes mes implantations précédentes, les clients ont décidé d'y aller avec une architecture SQL dédiée pour Dynamics AX.

Je ne vais pas entrer dans les détails de Resource Governor, mais en résumé c’est un outil qui permet de limiter la consommation de CPU, mémoire et IOPS. Bref, un outil parfait dans mon cas afin de limiter les ressources utilisées par les autres bases de données sur le serveur et laisser plus de performance pour Dynamics AX.

Tout d’abord, j’ai créé un nouveau Resource Pool nommé DynamicsAX. Mon intention est de rediriger toutes les sessions provenant des serveurs AOS vers ce Resource Pool. Je lui assigne un minimum de 50% du CPU et un minimum de 70% de la mémoire.  Prenez note que je ne peux pas assigner un minimum et maximum de IOPS via l’interface graphique, l'option est disponible seulement via T-SQL.

Ensuite, j’ai créé 3 différents workload groups. Un pour le serveur AOS qui dessert les utilisateurs, un pour le serveur AOS qui exécute les batch et un pour le serveur AOS qui dessert les utilisateurs Enterprise Portal.

Ensuite, j'ai créer un classifier. Le classifier permet de rediriger les sessions dans un workload group. Le workload group appartient à un Resource Pool. Dans ce cas-ci, j'ai décidé d'utiliser HOST_NAME() pour déterminer la source de la session en sachant que mes serveurs AOS se connectent à la base de données Dynamics AX uniquement via le service AOS. Il est aussi possible d’utiliser d’autre variable comme APP_NAME(), HOST_NAME(), ORIGINAL_DB_NAME(), etc.

CREATE FUNCTION dbo.DynamicsAXClassifier()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @WorkloadGroup AS SYSNAME

IF(HOST_NAME() = 'AOSSERVERNAME_USERS')

SET @WorkloadGroup = 'AOS_Users'

ELSE IF (HOST_NAME() = 'AOSSERVERNAME_BATCH')

SET @WorkloadGroup = 'AOS_Batch'

ELSE IF (HOST_NAME() = 'AOSSERVERNAME_EP')

SET @WorkloadGroup = 'AOS_EP'

ELSE

SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup

END

GO

Toutes les sessions qui ne proviennent pas des serveurs AOS iront dans le resource pool default. Celui -ci a un accès maximum de 100% du CPU et de la mémoire, mais étant donné que le Resource Pool DynamicsAX a une réservation de 70% (CPU) et 70% (mémoire), ceci veut dire que le nouveau maximum est 30% et 30%.

Je voulais ajouter des limitations supplémentaires au Resource Pool default. Dans mon cas, la configuration global de MAXDOP est 4. Mais, il est possible de limiter MAXPOP à 1 pour toute requête autre que Dynamics AX:

Pour finir, j’ai modifié la limite de IOPS a 1000 pour le Resource Pool default:

ALTER RESOURCE POOL [default] WITH(

                min_cpu_percent=0, 

max_cpu_percent=100, 

min_memory_percent=0, 

max_memory_percent=100, 

cap_cpu_percent=100, 

AFFINITY SCHEDULER = AUTO, 

min_iops_per_volume=0, 

max_iops_per_volume=1000)

GO

Après que le tout est en place, il faut redemarrer les service AOS afin d'initier de nouvelles sessions. Ensuite, j’utilise un rapport SRRS SQL Server Resource Governor Monitoring reports pour surveiller l’utilisation des ressources dans les Ressources Pools.

Finalement, vous avez surment remarqué qu’il y a plusieurs options dont je n’ai pas mentionné. Je suggère la lecture de Resource Governor sur simple-talk afin de connaitre tous les détails.

DynamicsPerf : D'où viennent les données ?

J'aime beaucoup l'outil DynamicsPerf afin d'analyser les performances de mes environnements Dynamics AX. Toutefois, ce n'est pas un outil très user friendly. Il arrive souvent que l'outil collecte partiellement les données à l'insu du DBA ! Je vais mettre ici l'information et les requêtes TSQL qui me permettent de troubleshooter l'installation de DynamicsPerf.

DATABASES_2_COLLECT

Une requête qui me donne les bases de données à collecter

SELECT * FROM DATABASES_2_COLLECT

CAPTURE_LOG

Une requête qui me retourne les logs

SELECT * FROM CAPTURE_LOG ORDER BY STATS_TIME DESC

Une requête qui me retourne les logs qui contiennent le mot FAILED

SELECT * FROM CAPTURE_LOG
WHERE TEXT LIKE '%FAILED%'
ORDER BY STATS_TIME DESC

DYNPERF_TASK_HISTORY

Une requête qui me retourne l'historique des taches exécutées

SELECT DTS.TASK_DESCRIPTION,
                DTH.*,
                DTS.*
FROM   DYNPERF_TASK_HISTORY DTH
               INNER JOIN DYNPERF_TASK_SCHEDULER DTS
               ON DTH.TASK_ID = DTS.TASK_ID
ORDER  BY DTH.TASK_ID 


SQL JOBS

Cette requête me retourne l'information sur les SQL Jobs de Dynamics Perf

SELECT 
sJOB.name AS [Job Name],

CASE
WHEN sSCH.schedule_uid IS NULL THEN 'No'
ELSE 'Yes'
END AS [Schedule Enabled],

CASE sJOB.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [Job Enabled],

CASE sJSTP.last_run_outcome
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 5 THEN 'Unknown'
END AS [Last Run Outcome],

CASE 
WHEN [sJSTP].[last_run_date] IS NULL OR [sJSTP].[last_run_time] IS NULL OR [sJSTP].[last_run_date] = 0 OR [sJSTP].[last_run_time] = 0 THEN NULL
ELSE CAST(CAST([sJSTP].[last_run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':')  AS SMALLDATETIME)
END AS [Last Run Date Time],

STUFF(STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6) , 3, 0, ':') , 6, 0, ':') AS [Last Run Duration (HH:MM:SS)],

CASE
WHEN [sJOBSCH].[next_run_date] IS NULL OR [sJOBSCH].[next_run_time] IS NULL OR [sJOBSCH].[next_run_date] = 0 OR [sJOBSCH].[next_run_time] =0  THEN NULL
ELSE CAST(CAST([sJOBSCH].[next_run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([sJOBSCH].[next_run_time] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') AS SMALLDATETIME)
END AS [Next Run Date Time],

CASE sSCH.freq_type
WHEN 1 THEN 'One time only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END AS [Frequency],
 
sJOB.date_created AS [Creation Date]
FROM
msdb.dbo.sysjobs AS sJOB
LEFT JOIN msdb.dbo.syscategories AS sCAT ON sJOB.category_id = sCAT.category_id
LEFT JOIN msdb.dbo.sysjobsteps AS sJSTP ON sJOB.job_id = sJSTP.job_id AND sJOB.start_step_id = sJSTP.step_id
LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH ON sJOB.job_id = sJOBSCH.job_id
LEFT JOIN msdb.dbo.sysschedules AS sSCH ON sJOBSCH.schedule_id = sSCH.schedule_id
WHERE sJOB.name IN ('DYNPERF_PROCESS_TASKS_LOW_PRIORITY','DYNPERF_PURGE_QUERYPLANS','DYNPERF_COLLECT_AOS_CONFIG','DYNPERF_PROCESS_TASKS','DYNPERF_CAPTURE_STATS','DYNPERF_CAPTURE_SSRS')

TABLES

Je me sers de la requête suivante pour savoir le nombre de lignes par table. Je suis ainsi capable d'identifier plus facilement les données qui manquent.

SELECT  t.NAME AS TableName,
                 p.[Rows]
FROM  sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
WHERE t.NAME NOT LIKE '%CRM%' AND index_id < 2
ORDER BY ROWS ASC, TableName ASC

Il arrive que je trouve certaines tables avec aucune donnée. Il est difficile de savoir la raison. Afin de m'aider à trouver le problème, j'ai identifié la source de données pour chaque table de DynamicsPerf. Par exemple, je sais maintenant que les données dans QUERY_STATS proviennent d'une Store procédure nommée DYNPERF_COLLECT_QUERY_STATS qui est exécutée par une SQL Job nommée DYNPERF_CAPTURE_STATS

Type	Name	Tables	Schedule
AX Batch Job	AOTExport	dbo.AX_BATCHJOB_DETAIL dbo.AX_BATCHSERVERGROUP_CONFIG dbo.AX_CONFIGURATIONKEY_DETAIL dbo.AX_INDEX_DETAIL dbo.AX_SERVER_CONFIG dbo.AX_TABLE_DETAIL
SQL Job	DYNPERF_COLLECT_AOS_CONFIG	dbo.AOS_EVENTLOG dbo.AOS_REGISTRY	Every day at 6AM
SQL Query	4-ConfigureDBs to Collect.sql	dbo.DATABASES_2_COLLECT	Manual
SQL Query	5-Setup SSRS Data Collection.sql	dbo.SSRS_CONFIG	Manual

SQL JOB - DYNPERF_CAPTURE_STATS

Type	Name	Tables	Schedule (default)
Store Proc	DYNPERF_COLLECT_QUERY_STATS	dbo.QUERY_STATS	Every 5 minutes
Store Proc	DYNPERF_COLLECT_INDEXSTATS	dbo.INDEX_DETAIL	Every 1 hour
Store Proc	DYNPERF_COLLECT_SQL_TEXT	dbo.QUERY_TEXT	Every 5 minutes
Store Proc	DYNPERF_COLLECT_QUERY_PLANS	dbo.QUERY_PLANS_PARSED	Every 5 minutes
Store Proc	DYNPERF_COLLECT_SYSOBJECTS	dbo.DYNSYSOBJECTS dbo.DYNSYSCOLUMNS dbo.DYNSYSINDEXES dbo.DYNSYSCOLUMNS	Every 1 day
Store Proc	DYNPERF_COLLECT_WAITSTATS	dbo.WAIT_STATS	Every 1 hour
Store Proc	DYNPERF_COLLECT_VIRTIALIO_DISKSTATS	dbo.DISKSTATS	Every 1 hour
Store Proc	DYNPERF_COLLECT_CHANGE_DATA_CONTROL	dbo.CDC	Every 1 day
Store Proc	DYNPERF_COLLECT_CHANGE_TRACKING	dbo.SQL_CHANGETRACKING_DBS dbo.SQL_CHANGETRACKING_TABLES	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_DATA_BUFFER_CACHE	dbo.BUFFER_DETAIL	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_DATABASES	dbo.SQL_DATABASES	Every 1 day
Store Proc	DYNPERF_COLLECT_DATABASE_REPLICATION_INFO	dbo.SQL_REPLICATION	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_CONFIGURATION	dbo.SQL_CONFIGURATION	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_DATABASE_FILES	dbo.SQL_DATABASEFILES	Every 1 day
Store Proc	DYNPERF_COLLECT_DATABASE_VLFS	dbo.LOGINFO	Every 1 day
Store Proc	DYNPERF_COLLECT_INDEX_USAGE_STATS	dbo.INDEX_USAGE_STATS	Every 1 hour
Store Proc	DYNPERF_COLLECT_INDEX_OPERATIONAL_STATS	dbo.INDEX_OPERATIONAL_STATS	Every 1 hour
Store Proc	DYNPERF_COLLECT_SQL_JOBS	dbo.SQL_JOBS	Every 1 day
Store Proc	DYNPERF_COLLECT_SERVERINFO	dbo.SERVERINFO	Every 1 day
Store Proc	DYNPERF_COLLECT_SERVER_REGISTRY	dbo.SERVER_REGISTRY	Every 1 day
Store Proc	DYNPERF_COLLECT_SERVER_DISKVOLUMES	dbo.SERVER_DISKVOLUMES	Every 1 week
Store Proc	DYNPERF_COLLECT_SERVER_OS_INFO	dbo.SERVER_OS_VERSION	Every 1 week
Store Proc	DYNPERF_COLLECT_TRIGGER_INFO	dbo.TRIGGER_TABLE	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_TRACEFLAGS_RUNNING	dbo.TRACEFLAGS	Every 1 day
Store Proc	DYNPERF_COLLECT_SQL_ERRORLOG	dbo.SQLERRORLOG	Every 5 minutes
Store Proc	DYNPERF_COLLECT_DATABASE_STATISTICS	dbo.INDEX_STAT_HEADER dbo.INDEX_DENSITY_VECTOR dbo.INDEX_HISTOGRAM	Every 1 week
Store Proc	DYNPERF_COLLECT_SQL_PLAN_GUIDES	dbo.SQL_PLAN_GUIDES	Every 1 day
Store Proc	DYNPERF_COLLECT_PERF_COUNTERS	dbo.PERF_COUNTER_DATA	Every 5 minutes
Store Proc	DYNPERF_COLLECT_PERF_COUNTERS_AZURE	dbo.PERF_COUNTER_DATA	Every 5 minutes
Store Proc	DYNPERF_COLLECT_AZURE_EVENTLOG	dbo.AZURE_EVENTS	Every 5 minutes
Store Proc	DYNPERF_COLLECT_AX_SQLTRACE	dbo.AX_SQLTRACE	Every 5 minutes
Store Proc	DYNPERF_COLLECT_AX_SQLSTORAGE	dbo.AX_SQLSTORAGE	Every 1 week
Store Proc	DYNPERF_COLLECT_AX_USERINFO	dbo.AX_USERINFO	Every 1 day
Store Proc	DYNPERF_COLLECT_AX_NUMBERSEQUENCE	dbo.AX_NUM_SEQUENCES	Every 1 hour
Store Proc	DYNPERF_COLLECT_AX_SYSGLOBALCONFIG	dbo.AX_SYSGLOBALCONFIGURATION	Every 1 day
Store Proc	DYNPERF_COLLECT_AX_USERINFO	dbo.AX_USERINFO	Every 1 day

SQL JOB - DYNPERF_CAPTURE_SSRS

Type	Name	Tables	Schedule (default)
Store Proc	DYNPERF_COLLECT_SSRS_EXECUTIONLOG	dbo.WRK_TZ_SQL_INFO dbo.SSRS_EXECUTIONLOG	Every 5 minutes

Dynamics AX : Modifier le propriétaire d'une batch

Voici les deux requêtes nécessaires afin de changer le propriétaire d'une batch job dans AX:

UPDATE BATCHJOB SET CREATEDBY ='NewUserID' WHERE STATUS=1 AND CREATEDBY = 'OldUserID'
UPDATE BATCH SET CREATEDBY ='NewUserID' WHERE STATUS=1 AND CREATEDBY = 'OldUserID'

Dynamics AX : Crash and Hang Analysis

Je voulais créer un billet pour mettre en lien deux articles au sujet des crashs d’un serveur AOS. Le premier article est au sujet de l’outil Crash and Hangs Analysis qui se trouve sur le portail LCS. L'outil est bien expliqué dans le blog suivant: Crash and Hang analysis on LCS.

Le deuxième article explique comment collecter le dump: AOS crash, hang or memory leak debugging tools.

Il y a plusieurs exemples selon le problème que vous avez: AOS crashes, AOS Hangs et AOS Memory Leaks.

Prenez note que l’auteur utilise procdump.exe, il est fort probable que vous devez utiliser procdump64.exe si vous utilisez AX 2009 ou 2012.

Le dernier article explique comment collecter les dumps automatiquement: Possibilities to create Memory Dumps from crashing processes

Dynamics AX : Business Intelligence for Visual Studio 2013 (SSDT)

Je reçois le message d'erreur suivant lorsque je tente de visualiser un rapport SSRS dans Visual Studio: Unable To preview the report, install the Microsoft SQL Serveur Data Tools - Business Intelligence for Visual Studio 2013.

SQL Server Data Tool n'est plus inclus avec SQL Serveur 2014, voici le lien pour télécharger l'outil: https://www.microsoft.com/en-ca/download/details.aspx?id=42313

SSRS : The request failed with HTTP status 401: Unauthorized

Lors de la configuration de SSRS Scale-Out déploiement avec deux serveurs, le message d'erreur suivant s'affichait lorsque je tentais de me connecter aux services.

The request failed with HTTP status 401: Unauthorized

Une recherche Google vous mène à différentes solutions. Tout d'abord, si vous obtenez ce message d'erreur à partir du serveur SSRS lui-même, vous devez utiliser la fonctionnalité Disable the Loopback Check ou configurer Back Connexion Host Names sur le serveur.

Dans mon cas, j'obtenais le message d'erreur à partir de mon client, peu importe l'URL:

http://node1/Reports

http://node2/Reports

http://virtualname/Reports

Toutefois, si l'erreur ne se produit pas lorsque je stoppe le service SSRS sur un serveur, peu importe lequel. 

La cause du problème est la suivante: l'authentification est envoyée au serveur SSRS. Ensuite, ce même serveur SSRS initie une connexion au service en utilisant le nom virtuel, cette requête est redirigée au deuxième serveur SSRS.

Pour résoudre le problème, il faut modifier le fichier hosts sur les serveurs SSRS comme indiquer dans cet article: 

Intermitent HTTP 401 error message in a SSRS Load Balancing Deployment

De cette façon, le serveur SSRS va résoudre le nom virtuel avec son adresse IP locale et ainsi la deuxième requête va toujours être retournée au même serveur.

Dynamics AX : Online Users & Connexion Actives (Rapport)

Un de mes clients m’a demandé un rapport sur l’utilisation de ses AOS. Une donnée intéressante de ce rapport est le nombre d’utilisateur actif sur chaque AOS et le nombre de connexion active.

Nous pouvons trouver l’information via System Administration --> Common --> Users --> Online Users. Sous l’onglet Client Sessions, on y trouve le nombre d’utilisateur actif

Sous l’onglet Server Instances, on y trouve le nombre de connexion actif (inclus les sessions de type Workers)

J’ai développé une requête afin d’obtenir toute l’information dans un même tableau.

SET NOCOUNT ON
DECLARE @name VARCHAR(256)

DECLARE @date VARCHAR(256)

DECLARE @time VARCHAR(256)

SET @date = (SELECT CONVERT(VARCHAR, GETDATE(), 23))

SET @time = (SELECT substring(CONVERT(VARCHAR, GETDATE(), 108),0,6))

CREATE TABLE #dbinfo

(

Date varchar (25),

Time varchar (25),

ServerName varchar(25),

RichClient bigint,

BusinessConnector bigint,

Workers bigint,

WebUser bigint

)

DECLARE db_cursor CURSOR FOR

SELECT aosid FROM SYSSERVERSESSIONS

WHERE status = 1

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #dbinfo

  SELECT DISTINCT @date, @time, @name,

  (SELECT COUNT (*)

FROM SYSSERVERSESSIONS s, SYSCLIENTSESSIONS c

WHERE c.clienttype = 0

AND  AOSID = @name

AND c.STATUS =1

AND s.serverID = c.serverID

GROUP BY CLIENTTYPE) AS RichClient,

  (SELECT COUNT (*)

FROM SYSSERVERSESSIONS s, SYSCLIENTSESSIONS c

WHERE c.clienttype = 1

AND  AOSID = @name

AND c.STATUS =1

AND s.serverID = c.serverID

GROUP BY CLIENTTYPE) AS BusinessConnector,

  (SELECT COUNT (*)

FROM SYSSERVERSESSIONS s, SYSCLIENTSESSIONS c

WHERE c.clienttype = 3

AND  AOSID = @name

AND c.STATUS =1

AND s.serverID = c.serverID

GROUP BY CLIENTTYPE) AS Worker,

  (SELECT COUNT (*)

FROM SYSSERVERSESSIONS s, SYSCLIENTSESSIONS c

WHERE c.clienttype = 5

AND  AOSID = @name

AND c.STATUS =1

AND s.serverID = c.serverID

GROUP BY CLIENTTYPE) AS WebClient

  FROM SYSSERVERSESSIONS s, SYSCLIENTSESSIONS c

  WHERE AOSID = @name

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

SELECT Date

, Time

, ServerName

, ISNULL (RichClient,0) AS RichClient

, ISNULL (BusinessConnector,0) AS BusinessConnector

, ISNULL (Workers,0) AS Workers

, ISNULL (WebUser,0) AS WebUser

, ISNULL (RichClient,0) + ISNULL (BusinessConnector,0) + ISNULL(Workers,0) + ISNULL(WebUser,0) AS ActiveConnections

FROM #dbinfo

ORDER BY ServerName

DROP TABLE #dbinfo

Le résultat est le suivant :

Si vous voulez un rapport sur une plus longue durée. Vous pouvez créer une tâche céduler qui exécute le requête et exporte les donnée dans un fichier .CSV.

@ECHO OFF

REM **************************

REM **SET YOUR VARIABLE**

REM **************************

set databaseserver=.

set databasename=MicrosoftDynamicsAX

set scriptpath=C:\Script\AXOnlineUsers

set OnlineUsersQuery=OnlineUsers.sql

set Outputfile=OnlineUsers.csv

ECHO ON

SQLCMD -S %databaseserver% -d %databasename% -i %scriptpath%\%OnlineUsersQuery% -s "," -h -1 >> %scriptpath%\%Outputfile%

Le résultat ressemble à ceci :