Hi,
Lately I’m working on some projects who experience a bad performance in Dynamics Ax. This can have multiple causes but one of the most important is maintaining the SQL Server. In my humble opinion every Dynamics Ax developer should have a basic knowledge on how SQL Server works and is maintained because it is the backbone of our beloved ERP software. :-).
This said if terms like rebuilding and reorganizing indexes, updating statistics or recovery model don’t ring a bell these links will be very interesting for you ;-).
- http://www.mikefal.net/2011/05/31/database-oil-changes/
A very good post on how a database must be maintained. (make sure you read part 2 & 3 too.) - http://www.opsvault.com/how-to-set-up-the-performance-analyzer-for-microsoft-dynamics-ax/
A tutorial to set up the Performance Analyzer toolkit, this toolkit packs a number of SQL scripts that can find missing indexes, expensive indexes and combined with the AOS tracing long running queries. - http://www.mssqltips.com/sqlservertip/1708/index-fragmentation-report-in-sql-server-2005-and-2008/
A good index fragmentation report script. - http://www.itdevspace.com/2009/07/how-to-filter-spwho2-information.html
The SP_WHO2 statement is very useful to find locking and to diagnose system slowdowns. This script imports the data into a temp table so you apply queries on it. - http://www.extremeexperts.com/sql/articles/sqlcounters.aspx or http://sugeshkr.blogspot.com/2008/07/performance-counter-for-sql-server.html
On overview of the most common SQL Server Performance counters. - http://msdn.microsoft.com/en-us/library/dd672789%28v=sql.100%29.aspx
A long paper by Microsoft on troubleshooting performance issues in SQL Server 2008.