The Microsoft Premier - Dynamics team has created and compiled a set of scripts and tools for helping analyze and troubleshoot SQL Server performance issues on the Dynamics products. These are the same tools that we use on a daily basis for collecting SQL performance data and troubleshooting SQL performance issues on all our Dynamics products* and we want to make this available to our partners and customers. These tools rely heavily on the SQL Server DMVs so it is only available for SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
This tool can aid in the troubleshooting of blocking issues, index utilization, long running queries, and SQL configuration issues. Instructions for installing and using the tool are included in the download package. One nice feature of this tool is that it creates a database called DynamicsPerf and imports all the performance data collected into the DynamicsPerf DB, which can be backed up and restored on any SQL Server (2005, 2008) for later analysis making it "portable." The collection of performance data can also be automated via a SQL job for which the scripts are provided.
Performance Analyzer 1.0 for Microsoft Dynamics can be downloaded via the following MSDN link. This tool is updated on a fairly consistent basis with bug fixes and new functionality so please check often for new versions.
This tool and associated scripts are released "AS IS" and are not supported.
*There is added functionality for Dynamics AX
-Michael De Voe
I have captured information from a large NAV database and when running queries on the "QUERY_STATS_CURR_VW" view I get the following error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
I figure I need to add a CAST/CONVERT statement so the view uses a different (larger) datatype. But not sure where.
Found my problem. In views "QUERY_STATS_HASH_VW" and "QUERY_STATS_CURR_VW" where TOTAL_ELAPSED_TIME and TOTAL_WORKER_TIME are returned, I changed DECIMAL(12, 3) to DECIMAL(38, 20)
The specified Link doesn't work. Any Idea?
This looks like a very good tool for capturing the blocking, i have followed all the steps have tested this to capture deadlock by posting Journal Batch (>100 plus lines) from two separate RTC clients but now i have got to a point where i do see some data in the Trace or more specifically when i run 'Query Blocks - Investigate Blocks.sql' query but i actually don't know how and what to analyze, can you please guide me further or point me in right direction.
I ran following query from 'Query Blocks - Investigate Blocks.sql'
SELECT E.NAME, F.*
FROM fn_trace_gettable('C:\SQLTRACE\DYNAMICS_DEFAULT.trc', DEFAULT) F,
WHERE EventClass = trace_event_id
I could see some stuff in TextData column but again i don't know the next step(s).
Any help on the subject would be appreciated.