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
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
|
Type
|
Name
|
Tables
|
Schedule (default)
|
Store Proc
|
DYNPERF_COLLECT_SSRS_EXECUTIONLOG
|
dbo.WRK_TZ_SQL_INFO
dbo.SSRS_EXECUTIONLOG
|
Every 5 minutes
|