Transact-SQL Reference

DBCC CONCURRENCYVIOLATION

Displays statistics on how many times more than five batches were executed concurrently on SQL Server 2000 Desktop Engine or SQL Server 2000 Personal Edition. Also Controls whether these statistics are also recorded in the SQL Server error log.

Syntax

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

Arguments

DISPLAY

Displays the current values of the concurrency violation counters. The counters record how many times more than 5 batches were executed concurrently since logging was started or the counters were last reset. DISPLAY is the default if no option is specified.

RESET

Sets all the concurrency violation counters to zero.

STARTLOG

Enables logging the concurrency violation counters in the SQL Server event log once a minute whenever there are more than 5 concurrent batches.

STOPLOG

Stops the periodic logging of the concurrency violation counters in the SQL Server event log.

Remarks

DBCC CONCURRENCYVIOLATION can be executed on any Edition of SQL Server 2000, but is only effective on the SQL Server 2000 editions that have a concurrent workload governor: SQL Server 2000 Desktop Engine and SQL Server 2000 Personal Edition. On all other editions, it has no effect other than returning the message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server 2000 Desktop Edition and SQL Server 2000 Personal Edition have a concurrent workload governor that limits performance when more than 5 batches are executed concurrently. As the number of batches executing concurrently increases, the governor lowers the performance of the system by increasing amounts. Counts of the number of times more than 5 batches are executed concurrently are maintained in internal counters. You can display the contents of these counters using the DBCC CONCURRENCYVIOLATION statement with either the DISPLAY parameter or no parameter. You should consider upgrading to another edition of SQL Server 2000 if performance on a well-tuned system is slow, and DBCC CONCURRENCYVIOLATIONS shows that the database engine has often had significantly more than 5 batches executing concurrently.

You can enable periodic logging of the concurrency violation counters in the SQL Server event log using the DBCC CONCURRENCYVIOLATION(STARTLOG) statement. When logging is enabled, the concurrency violation counters are logged in the event log once a minute if there are more than 5 concurrent batches being executed. The counters are not written to the error log whenever there are 4 or less concurrent batches.

The primary output of the DBCC CONCURRENCYVIOLATION statement is in these lines:

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

The first line indicates how long the counters have been accumulating statistics.
The second line is built of headings that indicate which counter is being reported in that field of the message. Each heading indicates how far over the 5-batch limit each violation was. The 1 represents the count of the number of times 6 batches (5 batch limit + 1 violation) were executing concurrently, the 2 represents the count of the number of times 7 batches (5 + 2) were executing concurrently, and so on. The heading 10-100 represents the count of the number of times the system was between 10 and 100 batches over the limit, and the heading >100 indicates the number of times the system was more than 100 batches over the limit.
The third line reports how many times the indicated number of batches were executing concurrently. In the example line above, there were 5 times when the system was 1 batch over the limit, 3 times it was 2 batches over the limit, and 1 time it was 3 batches over the limit.

When periodic logging is enabled, a message in this format is placed in the SQL Server error log once a minute whenever more than 5 batches are executing concurrently:

2000-02-02 11:03:17.20 spid 12  This SQL Server has been opimized for 5 concurrent queries. This limit has been exceeded by 2 queries and performance may be adversely affected.

Result Sets

If periodic logging of the concurrency violation counters is enabled, DBCC CONCURRENCYVIOLATION returns this result set (message):

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
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If periodic logging of the concurrency violation counters is not enabled, DBCC CONCURRENCYVIOLATION returns this result set (message):

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
Concurrency violations will not be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC CONCURRENCYVIOLATION permissions default to members of the sysadmin fixed server role and are not transferable.

Examples

This example displays the current counter values, and then resets the counters.

-- Display the current counter values.
DBCC CONCURRENCYVIOLATION
GO
-- Reset the counter values to 0.
DBCC CONCURRENCYVIOLATION(RESET)
GO