Strange Log Entries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Strange Log Entries

Author

Topic

wynng
Starting Member

8 Posts

Posted - 2006-04-21 : 09:01:36

I'm wondering if anyone can shed any light on this.

We have a performance problem problem on a SQL Server 2000 database. Users complain of a slow down at two points in the day. We think we've tracked this down to a query that is running in a job during these times and is taking a share lock on a number of tables. We've improved the query so that the locking problem is minimized but while having this problem the following log entries appear in the SQL Server log:

ResType:ExchangeId Stype:'AND' SPID:89 ECID:30 Ec:(0x74D10098) Value:0x5afdc8a4 Cost:(0/2710)

We have been investigating a deadlock issue and so we've enabled the trace flag to show deadlocking info however I don't believe this relates to deadlocks. Does anyone know what this message actually means?

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-21 : 14:06:52

If the entry is coming from a deadlock trace there would be a lot more information than the 1 line above. It would give you explicit information as to what commands were running in the two processes that caused the problem.

One of the things that you can do to avoid blocking issues is use NOLOCK if you don't need latest/greatest data. Another is to create indexes that provide what is needed for the commands. If an index completely covers a query (not sure if it would apply in your situation) then the query never has to touch the base table so if data is just being read two queries might get the same data from two different indexes (if appropriate.)

Not sure if the following will help you or not but it is a script that you can put into a job and have the job run every minute. It will capture any blocking activity that is occuring at that minute in time and record the processes that were blocked and the commands that were being used. Blocking will naturally occur, but I built this to find trends where things were always blocked by a particular command or commands. You can paste the script into a job and run, but you need to run the Create table script first to construct the "Blocking Processes" table that is used to retain the information. Whenever the script uncovers blocking processes it will send you an email.

[CODE]-- Script needs to run in the MASTER database or be modified appropriately
declare @BlockingProcesses TABLE (spid smallint, kpid smallint, blocked smallint, dbid smallint, hostname nchar(256))

insert into @BlockingProcesses
select spid, kpid, blocked, dbid, hostname
from sysprocesses where spid in (select distinct blocked from sysprocesses where blocked > 0)

declare @count as int
select @count = count(*) from @BlockingProcesses
if @count > 0
begin
exec master..xp_sendmail @recipients = 'Your.email@here', @subject = '[FYI] Blocked Processes were found on the server'

declare @WhenHappened as datetime
declare @spid as smallint
declare @kpid as smallint
declare @blocked as smallint
declare @dbid as smallint
declare @hostname as nchar(128)
declare @commandtype as nvarchar(30)
declare @command as nvarchar(255)

declare curBlocking cursor for
select * from @BlockingProcesses

open curBlocking
FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname

WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #InputBuffer(EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))

DECLARE @ExecStr as nvarchar(255)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@spid) + ')'
INSERT INTO #InputBuffer
EXEC (@ExecStr)
SELECT @CommandType = EventType, @Command = EventInfo FROM #InputBuffer

DROP TABLE #InputBuffer

insert into BlockingProcesses
values (GetDate(), @spid, @kpid, @blocked, @dbid, @hostname, @CommandType, @Command)

FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname
END

CLOSE curBlocking
DEALLOCATE curBlocking
end[/CODE]
-- This script must be run 1 time before you begin tracing to construct the table to hold the blocking processes. This table goes in the Master database[CODE]CREATE TABLE [dbo].[BlockingProcesses] (
[WhenHappened] [datetime] NULL ,
[SPID] [smallint] NULL ,
[KPID] [smallint] NULL ,
[Blocked] [smallint] NULL ,
[DBID] [smallint] NULL ,
[HostName] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommandType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Command] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) [/CODE]

Hope it helps,
Dalton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources