Skip to main content
Dynamics 365

Analysis scripts for Performance Analyzer v2.0

Following on from my previous blog post regarding analysis scripts for the tool Performance Analyzer v1.20*, I am now publishing the scripts that come with the latest version, 2.0.

(*Previous post: https://blogs.msdn.microsoft.com/axsupport/2014/08/31/microsoft-dynamics-ax-general-performance-analysis-scripts/)

Download link: https://dynamicsperf.codeplex.com/

After downloading and extracting the tool from the above link, you can find these scripts in the following sub-folders:

  • ..\DynamicsPerf\DynamicsPerf – Analysis Scripts
  • ..\DynamicsPerf\Scripts – Dynamics AX

There is also a solution file for them which you can open from SQL Server Management Studio (..\DynamicsPerf\Performance Analyzer 2.00 Analyze Data.ssmssln).

I have updated my previous performance troubleshooting checklist blog posts linking into these scripts, to assist with interpreting and acting on the results. You can find these here:

https://blogs.msdn.microsoft.com/axsupport/2014/09/05/ax-performance-troubleshooting-checklist-part-1a-introduction-and-sql-configuration/

https://blogs.msdn.microsoft.com/axsupport/2014/09/05/ax-performance-troubleshooting-checklist-part-1b-application-and-aos-configuration/

https://blogs.msdn.microsoft.com/axsupport/2014/09/08/ax-performance-troubleshooting-checklist-part-2/

This is page 1 of 10. Please use the links in the table below to navigate between pages.

– General analysis
Analyse SQL Configuration Page 1
Analyse SQL Indexes Page 2
Analyse SQL Queries Page 3
Analyse Blocking Page 4
Baseline – benchmark queries Page 5
SSRS Report Analysis Page 6
Query Alerts Page 7
– AX Specific
Analyse AX Configuration Page 8
Analyse AX Indexes Page 9
Analyse AX Queries Page 10

SQLSERVER_INFO

WINDOWS_VERSION

SQL_SERVICES

DISK_VOLUMES

SQL_REGISTRY

SQL_CONFIGURATION

DATA_BUFFER_CACHE

SQL_DATABASES

SQL_DATABASEFILES

MAX_TEMPDB_SIZE

LOGFILE_BIGGER_THAN_DATABASE

SQL_VLFS

SQL_JOBS

SQL_LOGS

SQL_TRACE_FLAGS

SQL_TRIGGERS

SQL_RECORD_SIZES

CHANGE_DATA_TRACKING

CHANGE_DATA_CONTROL

SQL_REPLICATION

SQL_PLAN_GUIDES

— ————————————————————–

—            SQLSERVER_INFO

— SQL Server version, start time, cpu, memory, etc

—————————————————————-

USE DynamicsPerf

SELECT *

FROM SERVERINFO_CURR_VW

ORDER BY STATS_TIME DESC

— ————————————————————–

—                WINDOWS_VERSION

— Windows version information for this SQL Server instance

—        Current Service Pack?

— NOTE: This will be blank if not SQL Server 2008R2 SP1 or later

—————————————————————-

SELECT *

FROM SERVER_OS_VERSION_VW

— ————————————————————–

—        SQL_SERVICES

— SQL Server Services

—        What account are the services running under?

— NOTE: This will be blank if not SQL Server 2008R2 SP1 or later

—————————————————————-

SELECT servicename,

startup_type_desc,

status_desc,

process_id,

last_startup_time,

service_account,

is_clustered

FROM sys.dm_server_services;

— ————————————————————–

—            DISK_VOLUMES

— SQL Server Disk Volumes information for all drives that

— has a database located on it.

— Is free disk space low?

— NOTE: This will be blank if not SQL Server 2008R2 SP1 or later

—————————————————————-

SELECT *

FROM SERVER_DISKVOLUMES

— ————————————————————–

—        SQL_REGISTRY

— SQL Server Registry values

—        What Trace flags are set?

— NOTE: This will be blank if not SQL Server 2008R2 SP1 or later

—————————————————————-

SELECT *

FROM SERVER_REGISTRY

— ————————————————————–

—            SQL_CONFIGURATION

— SQL configuation issues

— 1. Max Degree of Parallelism set to 1 ?

— 2- Is %fillfactor 0 ?

— 3- Is Max Server Memory set to something less than total server memory?

—————————————————————–

SELECT *

FROM SQL_CONFIGURATION_CURR_VW

— ————————————————————–

—        DATA_BUFFER_CACHE

— Data Buffer Cache

— 1. Which database is consuming the largest amount of data cache ?

— a-are we capturing perf data on that database?

—————————————————————-

— By Database

SELECT *

FROM BUFFER_DETAIL_CURR_VW

ORDER BY SIZE_MB DESC

/*********************************************************************

–RUN this code in any database to get DB specific data cache information

SELECT

db.name, OBJ.NAME ,index_id ,

COUNT(*)AS CACHED_PAGES_COUNT

FROM sys.dm_os_buffer_descriptors AS BD

INNER JOIN

(

SELECT obj.name AS NAME

,index_id ,ALLOCATION_UNIT_ID

FROM sys.allocation_units AS AU

INNER JOIN sys.partitions AS P

ON AU.CONTAINER_ID = P.HOBT_ID

AND (AU.type = 1 OR AU.type = 3)

INNER JOIN sys.sysobjects AS obj

on obj.id = P.object_id

UNION ALL

SELECT obj.name AS NAME

,index_id, ALLOCATION_UNIT_ID

FROM sys.allocation_units as AU

INNER JOIN sys.partitions AS P

ON AU.CONTAINER_ID = P.PARTITION_ID

AND AU.type = 2

INNER JOIN sys.sysobjects AS obj

on obj.id = P.object_id

) AS OBJ

ON BD.allocation_unit_id = OBJ.ALLOCATION_UNIT_ID

INNER JOIN sys.databases db ON BD.database_id = db.database_id

WHERE db.name = DB_NAME() and db.state_desc = ‘ONLINE’

GROUP BY db.database_id,db.name, OBJ.NAME, index_id

ORDER BY 4 DESC,db.database_id,db.name, OBJ.NAME, index_id

*********************************************************************/

— ————————————————————–

—            SQL_DATABASES

— Investigate databases on this SQL instance

— Are there multiple Dynamics production databases AX and CRM as an example

— Is development or test databases on this SQL instance

—————————————————————-

SELECT *

FROM SQL_DATABASES_CURR_VW

— ————————————————————–

—        SQL_DATABASEFILES

— Investigate database files

— Are the data and log files on the same drive

— Is the database set to auto-grow

— Is TempDb on a dedicated drive

— Is there 1 TempDb file per CPU core

—————————————————————-

SELECT *

FROM SQL_DATABASEFILES_CURR_VW

— ————————————————————–

—        MAX_TEMPDB_SIZE

— initial size for Tempdb should be close to this value or

— a minimum of 25% of the sum of all database sizes on the instance

—————————————————————-

SELECT SERVER_NAME,FILE_NAME,

MAX([DB_SIZE(MB)]) AS MAX_SIZE

FROM SQL_DATABASEFILES_CURR_VW

WHERE DATABASE_NAME = ‘tempdb’

GROUP BY SERVER_NAME, FILE_NAME

— ————————————————————–

—        LOGFILE_BIGGER_THAN_DATABASE

— Transaction log file is 50% or more of actual database size

— Are Transaction Log backups setup?

—————————————————————-

SELECT SERVER_NAME,DATABASE_NAME,

SUM([DB_SIZE(MB)])

FROM SQL_DATABASEFILES_CURR_VW V1

WHERE FILE_TYPE = ‘Data’

GROUP BY SERVER_NAME,DATABASE_NAME

HAVING SUM([DB_SIZE(MB)]) / (SELECT DBSIZE

FROM (SELECT DATABASE_NAME,

SUM([DB_SIZE(MB)]) AS DBSIZE

FROM SQL_DATABASEFILES_CURR_VW V2

WHERE FILE_TYPE = ‘Log’

AND V1.DATABASE_NAME = V2.DATABASE_NAME

AND V1.SERVER_NAME = V2.SERVER_NAME

GROUP BY DATABASE_NAME)AS ACTIVITY_MONITOR_VW) < 2

— ————————————————————–

—            SQL_VLFS

— Investigate Virtual Log files for each database LOG file

— VLF_Count > 10k requires attention

—————————————————————-

SELECT SERVER_NAME,

DATABASE_NAME,

FILEID,

VLF_COUNT,

FREE,

INUSE

FROM LOGINFO LI

WHERE LI.STATS_TIME = (SELECT MAX(STATS_TIME)

FROM LOGINFO L2

WHERE L2.DATABASE_NAME = LI.DATABASE_NAME

AND L2.FILEID = LI.FILEID

AND L2.SERVER_NAME = LI.SERVER_NAME)

ORDER BY SERVER_NAME,

DATABASE_NAME,

FILEID

— ————————————————————–

—            SQL_JOBS

— Investigate SQL Jobs

— Is there a database backup job

— Is there a database maintenance job to update statistics daily

— Is there a database maintenance job to rebuild indexes weekly

— Are there jobs that could stress the server

—————————————————————-

SELECT *

FROM SQL_JOBS_CURR_VW

— ————————————————————–

—        SQL_LOGS

— Investigate SQL Error LOG

— Are there any failed entries?

— NOTE: If no data in this table, you need to install latest

— SQL Server cumulative update

—————————————————————-

SELECT *

FROM SQLErrorLog

WHERE LOGTEXT LIKE ‘%error%’

— AND SERVER_NAME = ‘XXXXXXXX’

— ————————————————————–

—            SQL_TRACE_FLAGS

— Investigate SQL Trace Flags that are configured

— Recommended Trace Flags

—        1117 – Evenly grow database files

—        1224 – Override lock escalation, only enable on large memory systems

—        2371 – SQL 2008 R2 SP1 and later, auto-update statistics occurs more frequently

—        4139 – SQL 2012 SP1 CU1 and later, moves last data point in index_histogram

—        4199 – Enable all optimizer changes implmented since RTM, should almost always have this on

— Informational Trace flags

—        1118 – Eliminate Mixed Extents (can increase performance at expense of disk space)

—        7646 – Trace Flag to reduce contention on Fulltext indexes

— NEVER turn on Trace Flags

—        4136 – Causes SQL Optimizer to use Density Vector instead of Histogram

—————————————————————-

;WITH MAX_STATS_CTE (SERVER_NAME, STATS_TIME)

AS (SELECT SERVER_NAME,

MAX(STATS_TIME)

FROM TRACEFLAGS

GROUP BY SERVER_NAME)

SELECT TF.*

FROM TRACEFLAGS TF

INNER JOIN MAX_STATS_CTE CTE

ON TF.SERVER_NAME = CTE.SERVER_NAME

AND TF.STATS_TIME = CTE.STATS_TIME

— ——————————————————————-

—            SQL_TRIGGERS

— Investigate Database Triggers

— Are there any custom triggers that could cause performance issues

———————————————————————

SELECT * FROM TRIGGER_TABLE

— ——————————————————————-

—            SQL_RECORD_SIZES

— Investigate Database Record legnth sizes

— Are there any tables too wide?

———————————————————————

SELECT DSO.RUN_NAME, DSO.DATABASE_NAME,

DSO.NAME AS TABLE_NAME,

DSI.NAME AS INDEX_NAME,

DSI.MAX_ROW_SIZE,

DSI.MAX_LOB_SIZE

FROM DYNSYSINDEXES DSI

INNER JOIN DYNSYSOBJECTS DSO

ON DSI.OBJECT_ID = DSO.OBJECT_ID

–WHERE DATABASE_NAME = ‘XXXXXXXXXX’

ORDER BY 1,2,5 DESC

— ——————————————————————-

—            CHANGE_DATA_TRACKING

— Investigate Database with Change Data Tracking enabled

———————————————————————

SELECT *

FROM SQL_CHANGETRACKING_DBS

ORDER BY SERVER_NAME

SELECT *

FROM SQL_CHANGETRACKING_TABLES

ORDER BY SERVER_NAME,

DATABASE_NAME

— ——————————————————————-

—            CHANGE_DATA_CONTROL

— Investigate Database with Change Data Control enabled

———————————————————————

SELECT *

FROM CDC

ORDER BY SERVER_NAME,

DATABASE_NAME,

SOURCE_TABLE

— ——————————————————————-

—            SQL_REPLICATION

— Investigate any SQL Replication

— Are we replicating high transaction volume tables?

———————————————————————

SELECT * FROM SQL_REPLICATION

— ——————————————————————-

—            SQL_PLAN_GUIDES

— Investigate any SQL PLAN GUIDES

———————————————————————

SELECT * FROM SQL_PLAN_GUIDES

–WHERE SERVER_NAME = ‘XXXXXX’ AND DATABASE_NAME = ‘XXXXXXX’

— ————————————————————–

— Run the following from a command line on the SQL Server

—        Bytes per Cluster should be 64k

—        fsutil fsinfo ntfsinfo f:

— Run the following from a command line on the SQL Server

— WMIC /OUTPUT:C:\SQLTRACE\PARTALIGN.html PARTITION GET DeviceID, StartingOffset /FORMAT:htable

—————————————————————-