Tip: Tracking Concurrency Violations

DBCC CONCURRENCYVIOLATION is a powerful tool for checking whether users of SQL Server Personal Edition or SQL Server Desktop Edition (formerly Microsoft Data Engine—MSDE) are reaching the threshold of concurrent batches, as the internal concurrent workload governor determines. This governor artificially limits the performance of the database engine in these two editions. If your users are consistently hitting the concurrency governor's limit, you should probably upgrade to the SQL Server Standard Edition to achieve maximum performance.

Microsoft doesn't publish the exact algorithms that the governor uses, but the company says that you might start to hit the concurrent batch limit when users execute more than five concurrent batches. The more users execute concurrent batches—and the more data each batch retrieves—the more the governor slows down individual batches. Microsoft probably implemented the governor to limit the throughput on these SQL Server editions, which the company designed to serve only a few users. But with MSDE, which includes the governor, this limit poses a potentially serious problem. Although the governor might artificially limit query throughput, it doesn't raise an alert. Thus, some users, for example, could test an application against MSDE during the artificial slowdown and receive horrible performance but never know why—they had hit the governor limit. This scenario is especially likely when independent software vendors (ISVs) ship the freely distributable Desktop Edition as an integrated component of their applications.

The DBCC CONCURRENCYVIOLATION command, however, helps administrators of small workgroups that are using a SQL Server Desktop Edition recognize when the application would benefit from an upgrade to the Standard Edition. This command supports four options, as the following syntax shows:

DBCC CONCURRENCYVIOLATION [ ( DISPLAY | RESET | STARTLOG | STOPLOG ) ]

DISPLAY, the default option, displays the concurrency violation counters' current values. The counters record how many more than five batches that users have executed concurrently since the command started logging or you last reset the counters. RESET sets all the concurrency violation counters to zero. STARTLOG lets you log the concurrency violation counters in the SQL Server event log once a minute whenever more than five concurrent batches are in execution. STOPLOG stops the periodic logging of the concurrency violation counters in the SQL Server event log.

The command's output isn't intuitive, as the following example shows:

Concurrency violations since 2000-02-02 11:03:17.20 1   2   3    4    5   6    7   8   9   10-100   >100 5   3   1    0    0   0    0   0   0      0       0

Here's how to decipher the results. The first line tells you that the command has tracked concurrency violations since 2000-02-02 11:03:17.20. The second line contains header information, with each number representing how many batches more than five (the governor limit) that the system executed. For example, 1 means the system executed six (five plus one) concurrent batches. The third line tells you how many times your users reached the batch threshold since the command began capturing statistics. In this example, users executed six batches concurrently five times, seven batches three times, and eight batches one time.