Export (0) Print
Expand All

DBCC CONCURRENCYVIOLATION

SQL Server 2000

  Topic last updated -- January 2004

Displays statistics on how many times more than eight operations were run concurrently on SQL Server 2000 Desktop Engine (MSDE 2000) 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 eight operations were run 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 eight concurrent operations.

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 (MSDE 2000) 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 Engine and SQL Server 2000 Personal Edition have a concurrent workload governor that limits performance when more than eight operations are run concurrently. Counts of the number of times more than eight operations are run 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 eight operations running 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 eight operations running concurrently. The counters are not written to the error log whenever there are seven or less concurrent operations.

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 eight-operation limit each violation was. The 1 represents the count of the number of times nine operations (eight operation limit + 1 violation) were executing concurrently, the 2 represents the count of the number of times ten operations (eight + two) were running concurrently, and so on. The heading 10-100 represents the count of the number of times the system was between 10 and 100 operations over the limit, and the heading >100 indicates the number of times the system was more than 100 operations over the limit.

  • The third line reports how many times the indicated number of operations were executing concurrently. In the example line above, there were five times when the system was one operation over the limit, three times it was two operations over the limit, and one time it was three operations 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 five operations 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.

Note  The message refers to five concurrent queries instead of eight operations, the governor limit is not defined in terms of queries.

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

See Also

DBCC

The SQL Server 2000 Workload Governor

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft