Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Deadlock_и, помогите разобраться.  [new]
AlexanderVS
Member

Откуда: Krasnoyarsk
Сообщений: 433
Переодически на сервере присутствуют дедлоки, естественно что хочется избавиться от них.
Имеем следующее:
Date,Source,Severity,Message
10/30/2009 14:41:45,spid17s,Unknown,waiter id=processc5ec58 mode=S requestType=wait
10/30/2009 14:41:45,spid17s,Unknown,waiter-list
10/30/2009 14:41:45,spid17s,Unknown,owner id=processf3c718 mode=X
10/30/2009 14:41:45,spid17s,Unknown,owner-list
10/30/2009 14:41:45,spid17s,Unknown,ridlock fileid=1 pageid=14067 dbid=5 objectname=diasoft.dbo.pCntParamValue id=lock4889b5080 mode=X associatedObjectId=72057595657912320
10/30/2009 14:41:45,spid17s,Unknown,waiter id=processf3c718 mode=S requestType=wait
10/30/2009 14:41:45,spid17s,Unknown,waiter-list
10/30/2009 14:41:45,spid17s,Unknown,owner id=processc5ec58 mode=X
10/30/2009 14:41:45,spid17s,Unknown,owner-list
10/30/2009 14:41:45,spid17s,Unknown,ridlock fileid=1 pageid=3232554 dbid=5 objectname=diasoft.dbo.pCntParamValue id=lock3e42c4d80 mode=X associatedObjectId=72057595657912320
10/30/2009 14:41:45,spid17s,Unknown,resource-list
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed
10/30/2009 14:41:45,spid17s,Unknown,select @cnt<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Comment      = ''
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID = 1736<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed    = @cntFailed out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cnt          = @cnt out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Date         = '20091030'<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID    = 40006044<c/>
10/30/2009 14:41:45,spid17s,Unknown,@MarkType     = 24<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number       = 5<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec DealProtocolChecks_Insert
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed  int
10/30/2009 14:41:45,spid17s,Unknown,declare @cnt int<c/>
10/30/2009 14:41:45,spid17s,Unknown,inputbuf
10/30/2009 14:41:45,spid17s,Unknown,@Comment      = ''
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID = 1736<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed    = @cntFailed out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cnt          = @cnt out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Date         = '20091030'<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID    = 40006044<c/>
10/30/2009 14:41:45,spid17s,Unknown,@MarkType     = 24<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number       = 5<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec DealProtocolChecks_Insert
10/30/2009 14:41:45,spid17s,Unknown,frame procname=adhoc line=4 stmtstart=92 stmtend=672 sqlhandle=0x01000500d886a92b207f0833020000000000000000000000
10/30/2009 14:41:45,spid17s,Unknown,@Comment        = @Comment
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID   = @InstrumentID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Action         = @Action<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DateSet        = @Date<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID      = @OperSetID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number         = @Number<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DealID         = @ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DealProtocolID = @DealProtocolID<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec @RetVal = DealProtocol_Add2
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.DealProtocolChecks_Insert line=537 stmtstart=51170 stmtend=52056 sqlhandle=0x030005000664a36732ecc7000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,@ID   = @DealProtocolID
10/30/2009 14:41:45,spid17s,Unknown,@Type = 0<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec @RetVal = CntPrmV_Adjust
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.DealProtocol_Add2 line=2479 stmtstart=214920 stmtend=215140 sqlhandle=0x030005003dfc6c5704d8c7000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,and ID   = @Zero
10/30/2009 14:41:45,spid17s,Unknown,and Type = @Type
10/30/2009 14:41:45,spid17s,Unknown,where SPID = @@spid
10/30/2009 14:41:45,spid17s,Unknown,from pCntParamValue
10/30/2009 14:41:45,spid17s,Unknown,ObjectID
10/30/2009 14:41:45,spid17s,Unknown,ParamType<c/>
10/30/2009 14:41:45,spid17s,Unknown,Value<c/>
10/30/2009 14:41:45,spid17s,Unknown,ParamName<c/>
10/30/2009 14:41:45,spid17s,Unknown,@ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,select @Type<c/>
10/30/2009 14:41:45,spid17s,Unknown,ObjectID)
10/30/2009 14:41:45,spid17s,Unknown,ParamType<c/>
10/30/2009 14:41:45,spid17s,Unknown,Value<c/>
10/30/2009 14:41:45,spid17s,Unknown,ParamName<c/>
10/30/2009 14:41:45,spid17s,Unknown,ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,(Type<c/>
10/30/2009 14:41:45,spid17s,Unknown,into tCntParamValue
10/30/2009 14:41:45,spid17s,Unknown,insert
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.CntPrmV_Adjust line=171 stmtstart=15772 stmtend=16472 sqlhandle=0x03000500bb8c5440bdeac8000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,executionStack
10/30/2009 14:41:45,spid17s,Unknown,process id=processf3c718 taskpriority=0 logused=11500 waitresource=RID: 5:1:3232554:0 waittime=281 ownerId=4072650684 transactionname=user_transaction lasttranstarted=2009-10-30T14:41:44.703 XDES=0x239d9ea40 lockMode=S schedulerid=6 kpid=484 status=suspended spid=150 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2009-10-30T14:41:44.703 lastbatchcompleted=2009-10-30T14:41:44.703 lastattention=2009-10-30T14:41:44.703 clientapp=5NT(e) hostname=ICA hostpid=13960 loginname=sycheva isolationlevel=read committed (2) xactid=4072650684 currentdb=5 lockTimeout=4294967295 clientoption1=2097184 clientoption2=16384
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed
10/30/2009 14:41:45,spid17s,Unknown,select @cnt<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Comment      = ''
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID = 1558<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed    = @cntFailed out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cnt          = @cnt out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Date         = '20091030'<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID    = 40006021<c/>
10/30/2009 14:41:45,spid17s,Unknown,@MarkType     = 24<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number       = 5<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec DealProtocolChecks_Insert
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed  int
10/30/2009 14:41:45,spid17s,Unknown,declare @cnt int<c/>
10/30/2009 14:41:45,spid17s,Unknown,inputbuf
10/30/2009 14:41:45,spid17s,Unknown,@Comment      = ''
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID = 1558<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cntFailed    = @cntFailed out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@cnt          = @cnt out<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Date         = '20091030'<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID    = 40006021<c/>
10/30/2009 14:41:45,spid17s,Unknown,@MarkType     = 24<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number       = 5<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec DealProtocolChecks_Insert
10/30/2009 14:41:45,spid17s,Unknown,frame procname=adhoc line=4 stmtstart=92 stmtend=672 sqlhandle=0x010005002a14311be01d8cba030000000000000000000000
10/30/2009 14:41:45,spid17s,Unknown,@Comment        = @Comment
10/30/2009 14:41:45,spid17s,Unknown,@InstrumentID   = @InstrumentID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Action         = @Action<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DateSet        = @Date<c/>
10/30/2009 14:41:45,spid17s,Unknown,@OperSetID      = @OperSetID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@Number         = @Number<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DealID         = @ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,@DealProtocolID = @DealProtocolID<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec @RetVal = DealProtocol_Add2
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.DealProtocolChecks_Insert line=537 stmtstart=51170 stmtend=52056 sqlhandle=0x030005000664a36732ecc7000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,@ID   = @DealProtocolID
10/30/2009 14:41:45,spid17s,Unknown,@Type = 0<c/>
10/30/2009 14:41:45,spid17s,Unknown,exec @RetVal = CntPrmV_Adjust
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.DealProtocol_Add2 line=2479 stmtstart=214920 stmtend=215140 sqlhandle=0x030005003dfc6c5704d8c7000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,and ID   = @Zero
10/30/2009 14:41:45,spid17s,Unknown,and Type = @Type
10/30/2009 14:41:45,spid17s,Unknown,where SPID = @@spid
10/30/2009 14:41:45,spid17s,Unknown,from pCntParamValue
10/30/2009 14:41:45,spid17s,Unknown,ObjectID
10/30/2009 14:41:45,spid17s,Unknown,ParamType<c/>
10/30/2009 14:41:45,spid17s,Unknown,Value<c/>
10/30/2009 14:41:45,spid17s,Unknown,ParamName<c/>
10/30/2009 14:41:45,spid17s,Unknown,@ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,select @Type<c/>
10/30/2009 14:41:45,spid17s,Unknown,ObjectID)
10/30/2009 14:41:45,spid17s,Unknown,ParamType<c/>
10/30/2009 14:41:45,spid17s,Unknown,Value<c/>
10/30/2009 14:41:45,spid17s,Unknown,ParamName<c/>
10/30/2009 14:41:45,spid17s,Unknown,ID<c/>
10/30/2009 14:41:45,spid17s,Unknown,(Type<c/>
10/30/2009 14:41:45,spid17s,Unknown,into tCntParamValue
10/30/2009 14:41:45,spid17s,Unknown,insert
10/30/2009 14:41:45,spid17s,Unknown,frame procname=diasoft.dbo.CntPrmV_Adjust line=171 stmtstart=15772 stmtend=16472 sqlhandle=0x03000500bb8c5440bdeac8000c9c00000100000000000000
10/30/2009 14:41:45,spid17s,Unknown,executionStack
10/30/2009 14:41:45,spid17s,Unknown,process id=processc5ec58 taskpriority=0 logused=10280 waitresource=RID: 5:1:14067:0 waittime=296 ownerId=4072650686 transactionname=user_transaction lasttranstarted=2009-10-30T14:41:44.703 XDES=0xd4f4b930 lockMode=S schedulerid=1 kpid=2332 status=suspended spid=216 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2009-10-30T14:41:44.703 lastbatchcompleted=2009-10-30T14:41:44.703 lastattention=2009-10-30T14:41:44.703 clientapp=5NT(e) hostname=ICA hostpid=9352 loginname=lyalina isolationlevel=read committed (2) xactid=4072650686 currentdb=5 lockTimeout=4294967295 clientoption1=2097184 clientoption2=16384
10/30/2009 14:41:45,spid17s,Unknown,process-list
10/30/2009 14:41:45,spid17s,Unknown,deadlock victim=processc5ec58
10/30/2009 14:41:45,spid17s,Unknown,deadlock-list
30 окт 09, 13:29    [7861969]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
AlexanderVS
Member

Откуда: Krasnoyarsk
Сообщений: 433
Насколько я понимаю, это информация о блокировке ресурса:

ridlock fileid=1 pageid=3232554 dbid=5 objectname=diasoft.dbo.pCntParamValue id=lock3e42c4d80 mode=X associatedObjectId=72057595657912320
Таблица pCntParamValue имеет след структуру:
CREATE TABLE [dbo].[pCntParamValue](
	[SPID] [dbo].[DSSPID] NOT NULL,
	[Type] [dbo].[DSIDENTIFIER] NOT NULL,
	[ID] [dbo].[DSIDENTIFIER] NOT NULL,
	[ObjectID] [dbo].[DSIDENTIFIER] NOT NULL,
	[ParamName] [dbo].[DSIDENTNAME] NOT NULL,
	[Value] [dbo].[DSCOMMENT] NOT NULL,
	[ParamType] [dbo].[DSIDENTIFIER] NOT NULL
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [XPKpCntParamValue] ON [dbo].[pCntParamValue] 
(
	[SPID] ASC,
	[Type] ASC,
	[ID] ASC,
	[ObjectID] ASC,
	[ParamName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
Помогите советом, как можно избавиться, в этом случае, от блокировок.
30 окт 09, 13:36    [7862053]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Судя по тому, что у вас драка идет за RID-ы (не за KEY), скорее всего, ИМХО, у вас банально не хватает индексов.

Сообщение было отредактировано: 30 окт 09, 13:42
30 окт 09, 13:41    [7862118]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
AlexanderVS
Member

Откуда: Krasnoyarsk
Сообщений: 433
Кластерный индекс отсутствует, так что все равно происходит обращение к RID.
Таблица это псевдовременная, так что даные там не живут.
2 ноя 09, 11:03    [7869504]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
AlexanderVS
Кластерный индекс отсутствует, так что все равно происходит обращение к RID.
Причем здесь кластерный индекс? Оптимизатор может и не лазить в таблицу-кучу, если ему достаточно данных (столбцов) в некластерном индексе.
2 ноя 09, 11:48    [7869806]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
AlexanderVS
Member

Откуда: Krasnoyarsk
Сообщений: 433
Понятно.
2 ноя 09, 12:32    [7870247]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock_и, помогите разобраться.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Вот скрипт от Александр Гладченко (ссылку сейчас не найду), генерирующий скрипты "недостающих" индексов (чуток мной подрихтованный):
SELECT 	[Рекомендуемый индекс]= 
		'-- CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' +
		replace(replace(replace(ISNULL(mid.equality_columns,'')
		+ ISNULL(', ' + mid.inequality_columns,''), '[', ''), ']', ''), ', ', '_')
--		+ CAST(mid.index_handle AS nvarchar)
		+ '] ON '
		+ mid.statement
		+ ' (' + ISNULL(mid.equality_columns,'')
		+ case when mid.equality_columns is not null
					and mid.inequality_columns is not null then ', '
				else ''
				end
		+ ISNULL(mid.inequality_columns,'') +
		+ case when mid.included_columns is null then ');'
				else ') INCLUDE (' + ISNULL(mid.included_columns,'') + ');'
				end
	, [Число компиляций] = migs.unique_compiles
	, [Количество операций поиска] = migs.user_seeks
	, [Количество операций просмотра] = migs.user_scans
	, [Средняя стоимость] = CAST(migs.avg_total_user_cost AS int)
	, [Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)
FROM	sys.dm_db_missing_index_groups mig
JOIN	sys.dm_db_missing_index_group_stats migs
ON	migs.group_handle = mig.index_group_handle
JOIN	sys.dm_db_missing_index_details mid 
ON	mig.index_handle = mid.index_handle
AND	mid.database_id = DB_ID()
order by migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)DESC;
Попробуйте запусть его в контексте своей базы и поищите в результирующем наборе свою таблицу...
Может достроеных индексов и хватит.
2 ноя 09, 12:38    [7870308]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить