Article ID: 929728
Article Last Modified on 11/21/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition
SYMPTOMS
Consider the following scenario:
- You have a computer that is running an instance of Microsoft SQL Server 2000 or of Microsoft SQL Server 2005.
- You use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server.
- You run SQL Profiler on the computer that is running the instance of SQL Server, or you run SQL Profiler on a different computer.
In this scenario, the instance of SQL Server performs slowly.
When this problem occurs, you notice the following symptoms if you query the master.dbo.sysprocesses system table in the instance of SQL Server:
- Several active server process IDs (SPIDs) display the 0x404 value in the waittype column.
- One or more SPIDs display the SQL Profiler value in the program_name column.
Additionally, you see one of the following values in the waitresource column if you query the master.dbo.sysprocesses system table:
- TRACE_CONTROLLER
- TRACE_IO_SYNC
- TRACE
You see one of these values if the following conditions are true:
- You use SQL Server 2000 Service Pack 4 (SP4) or SQL Server 2005.
- A SPID is waiting to perform synchronization trace operations in SQL Profiler.
CAUSE
When you run SQL Profiler against an instance of SQL Server 2000 or of SQL Server 2005, each active SPID of the instance requires an exclusive latch (LATCH_EX) to write the event to SQL Profiler. The SPID writes the event to the local memory buffer first. Then, the event is transferred to SQL Profiler. If the local memory buffer is full, the SPID discards the event. After the events in the local memory buffer are transferred to SQL Profiler, the SPID can write new events to the local memory buffer. If other SPIDs are waiting to write events to SQL Profiler, these SPIDs wait in a queue.
Because SQL Profiler is a process that is separate from the SQL Server process, transferring each event to SQL Profiler takes some time. If you run SQL Profiler on a different computer, this process takes longer. This process takes longer because of network latency and because of the performance of the computer that is running SQL Profiler. Therefore, the instance of SQL Server performs slowly.
WORKAROUND
To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.
You can use the following system stored procedures:
- sp_trace_create
- sp_trace_setevent
- sp_trace_setfilter
- sp_trace_setstatus
For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
You can use the PSSDIAG data collection utility to collect the trace data files. For more information about the PSSDIAG data collection utility, click the following article number to view the article in the Microsoft Knowledge Base:
830232 PSSDIAG data collection utility
For more information about the waittype column in SQL Server 2000 and in SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
822101 Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
Keywords: kbsql2005engine kbexpertiseadvanced kbtshoot kbprb KB929728
