axinthefield: TempDb blocking with Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

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 challenges in managing tempdb is that there is no way to partition its resources based on user databases, applications, or user sessions. You cannot even partition 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 2 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:

<ol> 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