SQL: requêtes et scripts

J’utilise souvent les mêmes scripts pour les certaines opérations SQL. Au lieu de partager les scripts sur ce blogue, j’ai décidé de faire un billet, que je vais mettre à jour au fil du temps, qui contient la liste de tous les articles et scripts SQL que je trouve utile. 

Indexes et Statistics


Performance

D365 Finance and Operations : les ports nécessaires pour déployer un environnement Cloud-Hosted

J'ai reçu l'erreur suivante lors du déploiement d'un environnement Cloud-Hosted dans mon infrastructure Azure:

Attempted to remotely execute a powershell script but was unable to reach the Windows Remote Management service. Please make sure the virtual machine in resource group is accessible.

Afin de corriger le problème, j'ai ouvert les ports pour accèder WinRM et tenter un nouveau deployment. Cette fois, j'ai reçu le message suivant:

Script [Run-CommandAOSServiceResetAosAdminUser] failed execution against VM. Last Result: 0xFFFFFFF ().

Le problème est encore le pare-feu. En fait, ceci est dû au fait que je sélectionne un VNET et un subnet qui existe déjà dans mon infrastructure Azure. Le subnet est attaché à un Network Security Group (NSG) qui restreint les ports accessibles de l'extérieur. Étant donné que j'ai été incapable de trouver les pré-requies en terme de port pour le déploiement d'un environnment Cloud-Hosted, j'ai fait un déploiement et j'ai laissé LCS créer le VNET et le subnet. Ensuite, le déploiement fut un succès et j'ai été regarder la configuration par défaut du NSG.

Ensuite, il suffit de modifier les permissions du NSG existant donc je veux utiliser pour les futures déploiement.


D365 Finance and Operations: Utilisation DTU pour les base de données Azure SQL

Les environnements D365 F&O Tier 2+ utilisent Azure SQL pour les bases de données AX, Financial Reporter et Data Warehouse. Étant donnée que les environnements son hébergé par Microsoft, nous n’avons pas accès aux serveurs Azure SQL via le portail Azure. Ainsi, nous ne pouvons pas utiliser les outils de monitoring d'Azure.

Toutefois, nous pouvons utiliser l’outil Environnement Monitoring de LCS pour voir le pourcentage  d’utilisation SQL pour la base de données AxDB.


Toutefois, aucune information pour les bases de données AxDW et MrDB. Afin de savoir l’utilisation SQL pour ses deux bases de données, il faut se connecter sur le serveur Azure SQL et exécuter la requête suivante:

WITH workload_group_resource_stats_cte (end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent) 
AS (SELECT end_time, 
    Sum(avg_cpu_percent)       AS AVG_CPU_PERCENT, 
    Sum(avg_data_io_percent)   AS AVG_DATA_IO_PERCENT, 
    Sum(avg_log_write_percent) AS AVG_LOG_WRITE_PERCENT 
FROM   sys.dm_db_workload_group_resource_stats 
GROUP  BY end_time)
SELECT END_TIME,
 (SELECT Max(v) 
  FROM (VALUES (avg_cpu_percent), 
    (avg_data_io_percent), 
    (avg_log_write_percent)) AS VALUE(v)) AS DTU_CONSUMPTION_PERCENT,
avg_cpu_percent,avg_data_io_percent,avg_log_write_percent
FROM workload_group_resource_stats_cte
ORDER BY END_TIME DESC


SQL Server: FETCH_API consommes beaucoup de resources SQL

Lors d’une investigation d’un problème de performance avec D365 for Finance and Operations, j’ai exécuté le script Find Most Expensive Queries Using DMV de Pinal Dave et j’ai trouvé ceci:


Étant donné que le processus était toujours en cours d’exécution dans l’application, j'ai utilisé mon script qui me retourne toute les requêtes en cours d’exécution et j’ai vu le résultat ci-dessous. Il faut dire que ce fut par chance parce que comme vous pouvez le voir, c’est une petite requête qui ne consomme pas de CPU, reads, writes et logicial reads, donc elle passe vite. J’ai dû exécuter mon script plusieurs fois avant avant de la voir.


Qu’est-ce que FETCH_API ? C’est bien expliquer dans l’article suivant: Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch. En résumé, le curseur est défini au début de la transaction et SQL travaille a l’intérieur de ce curseur.

Afin de voir la requête derrière le curseur, l’article mentionner plus haut propose la requête suivante (il faut remplacer le chiffre 53 par le SPID):

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (53) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

Celle-ci fonctionne bien si le SPID reste le même pour une longue durée. Pour ma part, j’ai fait une modification au script afin de voir tous les curseurs ouverts en ordre de worker_time, read and writes afin de facilement identifier le curseur qui consomme le plus de ressource.

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text, worker_time, reads, writes
FROM sys.dm_exec_cursors (0) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
ORDER BY 
worker_time DESC
reads DESC
writes DESC


Voilà, je peux maintenant voir les requêtes en question.

D365 Finance and Operations: The request channel timed out while waiting a reply after 00:59:59.

Récemment, j’écrivais un article sur comment trouver les messages d’erreur lors de l’exécution d’une job export entity to database qui échoue: Export failed while copying data from target to staging.

J’ai diagnostiqué un autre cas ou l’export échouait avec l’erreur suivante:

The request channel timed out while waiting a reply after 00:59:59. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding.

Dans une architecture WCF, la valeur du SendTimeout est établie par le client. Dans ce cas-ci, le client est le service AOS et le serveur est Data Import Export Framework service. Afin de modifier la valeur du SendTimeout du côté client, il faut modifier le fichier suivant:

Fichier: Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.dll.config

Microsoft-Hosted
  • G:\AosService\WebRoot\bin\
Cloud-Hosted
  • K:\AosService\WebRoot\bin

Ensuite il faut redemarrer IIS.