Microsoft Dynamics since version 4.0 has utilized SQL Servers feature Read Committed Snapshot Isolation to enable better database concurrency. This feature adds much more tempdb activity then database administrators are used to with other applications. One of the challenges in managing tempdb is that there is no way to partition the resources based on user databases, applications, user sessions,or resources based on the category of objects (such as version stores) that are stored. It seems useful to partition tempdb resources to isolate applications from one another but then the burden is on the applications or on the administrators to partition these resources in such a way so as to minimize waste. For example, if you put an upper bound on the tempdb resources that can be consumed by an application, you may be forced to abort one or more transactions even if those resources were not being used by any other applications or queries.
The biggest issue that can arise is database blocking that can occur in tempdb. A common question that comes up is how can two users running different queries on different resources get blocked in tempdb? The answer is how space is allocated in tempdb. The following article from our SQL Server team describes how pages are allocated inside a database and what is an allocation bottleneck.
For Dynamics AX we recommend the following:
- Create at least as many files of equal size as there are Cores/CPUs for SQL Server process. The idea is that at a given time, the number of concurrent threads is <= number of Cores/CPUs.
- Enable Trace Flag -1118 if you are having bottlenecks in SGAM structures
To determine if you are having this type of issue with tempdb run the following DMV query:
Select session_id, wait_duration_ms, resource_description
where wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'
Since the database id of TempDB is 2, the search argument ‘2.%’ represents any page in TempDB across any file. If this page happens to be GAM, SGAM or PFS, it will represent allocation bottleneck. Note, in Dynamics AX, some blocking is expected so you will need to baseline the allocation waits when your system is performing normally. Only when the waits exceed the baseline significantly, it signals that you are incurring allocation bottleneck.