Category Archives: Dynamics AX 2009

Show SQL Plans in Plan Cache for a table name or matching partial query text

Ever wondered what queries are calling what tables or do you have a query and want to see how many times it’s been called (since last plan cache flush) and view the query plan for it.

These queries are a good to use when performance tuning indexes and should be a part of your toolbox.  You need to know what queries are using your table before you can start to plan the best indexing strategy for your table.

Note: Query#2 will only work in SQL Server Management Studio 2008 onwards.

Here is a quick and easy way to do this:


This query will show you the plan handle value you need to extract the query plan from the plan cache, it will also show you how many times the query has been executed using that plan handle.

-- Shows SQL plan handles in plan cache for a specific piece of SQL text
SELECT plan_handle, usecounts AS ExecutionCount, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%<Enter Table name or partial Query here>%'
ORDER BY usecounts DESC


Using the plan_handle value from Query#1 insert it into the where clause and this will give you XML result which you can click on to view the graphical query plan.

-- Show XML query plan ** Only works with SSMS2008 **
sys.dm_exec_query_plan(<Paste Query "plan_handle" value from previous query here>)



Query#1 Results of ‘%EVENTINBOX%’ 

Query#2 – XML result of plan_handle (0x06001100A23AD31D40031579060000000000000000000000) from highest exection count of Query#1


Query#2 – Visual Query Plan after clicking on XML result show above

Dynamics AX 2009 components are installed into an incorrect directory and the install fails

I recently encountered an issue when installing a 2nd AOS on a server that it failed to install correctly.  The components were attempting to install into an incorrect directory, therefore the install failed as it was unable to register the service correctly.

You may see the following error message within the “DynamicsSetupLog.txt” install log.

AOS.1: Regasm C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe   "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll"
RegAsm : error RA0000 : Unable to locate input assembly 'C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll' or one of its dependencies.

An error occurred during setup of Application Object Server (AOS).
Reason: Error unregistering  Microsoft.Dynamics.IntegrationFramework.dll. Program run: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe.  Parms: "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll" /unregister

As you can see it’s attempting to install the AOS into an incorrect directory namely:

C:\Program Files\Microsoft Dynamics AX\50\Server\Server\

The correct folder is:

C:\Program Files\Microsoft Dynamics AX\50\Server\

A hotfix exists to resolve this issue it can be download from Customer Source here:;en-us;959494

View the knowledge base article on the MS Support site here: