Using Performance Analyzer for Microsoft Dynamics in a Development, Test, or QA environment

USING THIS PROCESS WILL KEEP SOME PERFORMANCE ISSUES OUT OF PRODUCTION !!!

BE PROACTIVE NOT REACTIVE WITH YOUR DYNAMICS PERFORMANCE !!

Using Performance Analyzer for Microsoft Dynamics in your production environment I think is pretty well understood the benefits of doing that activity.  All of us are constantly trying to tune our production systems.  One of the big opportunities that gets missed with our toolset is implementing it in your Development, Test or QA environments.

The real benefit of using Performance Analyzer for Microsoft Dynamics is not finding issues in production but PREVENTING issues before they get to production.  It’s one of the most common problems that we see in implementations is that performance tuning/analysis isn’t given enough time and effort or $$$ to succeed.  Thus why we tend to fight fires in our production systems.  It really is a pay me now or pay me later type situation. 

In version 1.20, we have added a new script that walks you through the process of analyzing data in an environment other then your production system. The steps are:

1. Delete old data from DynamicsPerf
2. Free procedure cache
3. Run your test
4. Collect Data
5. Analyze Data

The script is called Capturestats in Test.sql:

--------------------------------------------------------------
-- For testing in a non-production system do the following:
--
--STEP1
        USE DynamicsPerf

        GO

        EXEC SP_PURGESTATS
          @PURGE_DAYS = -1,
          @DATABASE_NAME = 'XXXXXXXX' --Use this option to delete all data for 1 database

        GO

--        --    Be sure all users are out of the test database
--        --    Get yourself to the point in the application where you are ready
--        --    to push the button for the code    you want to review.
--
--STEP2

        DBCC FREEPROCCACHE

-- OR the follwing for a specific database
--
        DECLARE @intDBID INTEGER
        SET @intDBID = (SELECT dbid
                        FROM   master.dbo.sysdatabases
                        WHERE  name = 'database_name')
        DBCC FLUSHPROCINDB (@intDBID)
--
--
-- Capture data enabling delta's to be calculated
--
-- STEP3  (optional)
--  USE DynamicsPerf
EXEC SP_CAPTURESTATS    @DATABASE_NAME = 'XXXXXXXXX'
--
--

-- STEP4
--        --NOW RUN YOUR TESTS

-- STEP5 capture data

EXEC SP_CAPTURESTATS    @DATABASE_NAME = 'XXXXXXXXXX'

-- STEP 6  Review your data

That’s all there is too it !!  Granted the hardest part of this is analyzing the data. 

Core installation instructions can be found here:

http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx

Dynamics AX customers can go here for the Dynamics AX components after installing the core components

http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation.aspx

Also checkout these blogs on DynamicsPerf 1.20 :

Baselines with DynamicsPerf 1.20 http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/baselines-with-performance-analyzer-for-microsoft-dynamics-dynamicsperf-are-here.aspx

Documenting with comments in DynamicsPerf 1.20  http://blogs.msdn.com/b/axinthefield/archive/2014/06/05/commenting-in-performance-analyzer-for-microsoft-dynamics-dynamicsperf.aspx

Rod “Hotrod” Hansen