Microsoft Dynamics AX general performance analysis scripts

Did you know there is a set of analysis scripts that come with the tool Performance Analyzer 1.20 and can be used to assess the health of your AX system and identify potential causes of performance issues?

After downloading the tool (see above link), you can find these scripts in the following folders:

• ..\DynamicsPerf1.20 RC0.zip\DynamicsPerf\DynamicsPerf – Analysis Scripts
• ..\DynamicsPerf1.20 RC0.zip\DynamicsPerf\Scripts – Dynamics AX

This series of blog posts also includes these scripts and a general performance troubleshooting checklist will follow in another series (linking into these) to assist with interpreting and acting on the results.

This is page 1 of 8. 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
– AX Specific
Analyse AX Configuration	Page 6
Analyse AX Indexes	Page 7
Analyse AX Queries	Page 8

Analyse SQL Configuration

SQLSERVER_INFO
WINDOWS_VERSION
SQL_SERVICES
DISK_VOLUMES
SQL_REGISTRY
SQL_CONFIGURATION
DATA_BUFFER_CACHE
SQL_DATABASES
SQL_DATABASEFILES
SQL_VLFS
SQL_JOBS
SQL_LOGS
SQL_TRACE_FLAGS
SQL_TRIGGERS
FILE_ALLOCATION_UNIT_SIZE
DISK_SECTOR_ALIGNMENT

—————————————————————- —
— SQLSERVER_INFO
— SQL Server Installation
—————————————————————-

USE DynamicsPerf

SELECT *
FROM   SERVERINFO
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 the SQL Build the current build ?
— 3- AWE enabled on large memory 32bit systems?
— 4- 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 buffer cache ?
—      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 buffer 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
— 
— Is there more then 1 production database
— 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 there 1 tempdb file per CPU core
—————————————————————-

SELECT *
FROM   SQL_DATABASEFILES_CURR_VW

—————————————————————-
—
—   SQL_VLFS
—
— Investigate Virtual Log files for each database LOG file
—  VLF_Count > 10k requires attention
—————————————————————-

SELECT DATABASE_NAME,FILEID,
COUNT(*) AS VLF_COUNT,
SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END) AS FREE,
SUM(CASE WHEN STATUS != 0 THEN 1 ELSE 0 END) AS INUSE
FROM LOGINFO
GROUP BY DATABASE_NAME,FILEID
ORDER BY DATABASE_NAME,FILEID

—————————————————————-
—
—   SQL_JOBS
—
— Investigate SQL Jobs
— 
— Is there a database backup job
— Is there a database maintenance job to rebuild indexes and update statistics
— Are there jobs that could stress the server
—————————————————————-

SELECT *
FROM   SQL_JOBS_CURR_VW

—————————————————————-
—
—  SQL_LOGS
—
— Investigate SQL Error LOG
— 
— Is 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%’

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

—
—   SQL_TRACE_FLAGS
—
— Investigate SQL Trace Flags that are configured

— 1117 – Evenly grow database files
— 1118 – Eliminate Mixed Extents (can increase performance at expense of disk space)
— 1224 – Override lock escalation, only enable on large memory systems
— 2371 – SQL 2008 R2 SP1 and later, auto-update statistics occurs more frequently
— 4136 – Causes SQL Optimizer to use Density Vector instead of Histogram [not recommended – alternative solution for parameter sniffing here].
— 4199 – Enable all optimizer changes implmented since RTM, should almost always have this on
— 7646 – Trace Flag to reduce contention on Fulltext indexes

 

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

SELECT *
FROM   TRACEFLAGS

—————————————————————-
—
—   SQL_TRIGGERS
—
— Investigate Database Triggers

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

SELECT * FROM TRIGGER_TABLE

 

—————————————————————-
—
— 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
—
—————————————————————-