Dynamics AX: SQL Server, Heart of Dynamics AX

[Blog bot]

Источник: http://dynamics-ax.blogspot.com/2007...namics-ax.html
==============
When you think of Dynamics AX, you have to think about SQL Server, currently 2005. This is the heart of Any Dynamics AX implementation, and therefore must be thought through for what hardware and optimization options that will need to be setup for your given installed base.

A SQL Server Admins best friend is the System Monitor [aka Performance Montior] and the different information you can gather from the Currenty Activity section, as well as the SQL Server Profiler. These three tools will aid anyone whom knows how to use them in adjusting SQL Server performance to reach peak levels of optimization for the given instance. In the coming weeks I will start into a good many post on the different aspects of SQL Server optimization that is geared towards Dynamics AX. See with Dynamics AX, the management of SQL server is different than normal custom applications. The reason for this is the meta layer that Dynamics AX uses to manage the database, and the objects within it.

So to start let's talk about something that can be one of the first things that will bring improvement to most Any SQL Server install. One of the first things one can do is use the System Montior [aka Performance Monitor] and track the Page Life Expectancy for a given SQL Server instance. Now the reference to the instance of SQL Server here is the OLTP instance that host the transactional Dynamics AX database. So with this messaurement, one is looking at the average life of a SQL Page in RAM, over a timeframe messaured in Miliseconds. The higher the number, the better. A rule of thumb is that if the Page Life Expectancy stays on average below 3000 mark then you need to look at improvements for this area. How would your improve the life of a SQL page in RAM? One would think right off the bat, add more RAM. This thought is half way right, in that you need more RAM. Just adding more RAM though in a 32 bit system will not do the trick. No you have to enable AWE, or Address Windows Extensions. AWE enables a 32-bit program, via the OS, to allocate more than the default max of 2 GB for a given process in memory. So adding more RAM, and then enabling AWE, will increase your page life expectancy, and in turn your entire system will run faster because SQL server is not having to recompute / recompile the query plans, instead for the entire page life it can re-use those query plans.

To enable AWE in 2000 took a little bit of tricky work, but in 2005 it's pretty straight forward. Basically you have to apply the proper switch to the boot.ini of Windows [/3GB] and then tell SQL server to make use of AWE and set the max to all but 1.5 GB of what is there. [1 GB is you must, but that is a min. for the OS to have]

I have a couple links for you here that will be of useful help that talks about AWE:

1.) SQL Server Performance.com AWE Post for SQL Server 2000

2.) Microsoft Page about enabling AWE for SQL Server 2005

Check back soon as we look into other area's of interest for SQL Server performance, and also more focused towards Dynamics AX!

Find a job at: www.DynamicsAXJobs.comFind a job at: DynamicsAXJobs.com, also post a job for only $99.00!


==============
Источник: http://dynamics-ax.blogspot.com/2007...namics-ax.html