February 6, 2009 at 11:40 pm
Hi All,
I am facing deadlock during concurrent execution of stored procedure.
I have an sp which accept the table name as input and divide its data in sub tables month and year wise. In sp, first I create temp tables for distict month and year records. Then loop through them and create sub tables if they don't exist, alter these tables to create proper indexes and statistics. Finally transfer records and delete related records from Main table.
When multiple user execute the sp at same time then I am facing deadlock problem during this process. Here is trace output for the deadlock:
deadlock-list
deadlock victim=process92cf28
process-list
process id=process738c58 taskpriority=0 logused=13592 waitresource=KEY: 17:281474978938880 (d2007789cc39) waittime=4421 ownerId=1284036 transactionname=CREATE INDEX lasttranstarted=2009-02-05T15:02:21.423 XDES=0x3b17800 lockMode=X schedulerid=1 kpid=744 status=suspended spid=65 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-02-05T15:00:38.423 lastbatchcompleted=2009-02-05T15:00:38.423 clientapp=.Net SqlClient Data Provider hostname=ANKUR01 hostpid=3976 loginname=UseMe4DBOwner isolationlevel=read committed (2) xactid=1284036 currentdb=17 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x0100110020846c2e086f6615000000000000000000000000
CREATE CLUSTERED INDEX [PK_BAT_Charges_May06_Chg] ON BAT_Charges_May06 ([InvoiceNumber], [InvoiceDate], [TrackingNo], [ChargeClassCode]) 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]
frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000
sp_executesql
frame procname=UIRP-ArchiveTest.dbo.MHP_UIB line=80 stmtstart=11882 stmtend=11972 sqlhandle=0x030011003ec4c60bcf41f600a79b00000100000000000000
exec sp_executesql @CreateIndex1Script
frame procname=adhoc line=1 sqlhandle=0x010011003cc4431558579e15000000000000000000000000
Execute MHP_UIB 'BAT_Charges','b0cfa3c0-5a9d-4f57-a8db-ce277bdb8334'
inputbuf
Execute MHP_UIB 'BAT_Charges','b0cfa3c0-5a9d-4f57-a8db-ce277bdb8334'
process id=process92cf28 taskpriority=0 logused=0 waitresource=OBJECT: 17:1029578706:0 waittime=4531 ownerId=1284043 transactionname=I4ObjPropI4Str lasttranstarted=2009-02-05T15:02:21.423 XDES=0x22e7b8e0 lockMode=Sch-S schedulerid=2 kpid=752 status=suspended spid=71 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-02-05T15:00:14.707 lastbatchcompleted=2009-02-05T15:00:14.707 clientapp=.Net SqlClient Data Provider hostname=ANKUR01 hostpid=3056 loginname=UseMe4DBOwner isolationlevel=read committed (2) xactid=1284021 currentdb=17 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=UIRP-ArchiveTest.dbo.MHP_UIB line=49 stmtstart=4352 stmtend=4628 sqlhandle=0x030011003ec4c60bcf41f600a79b00000100000000000000
if NOT exists (select * from dbo.sysobjects where id = object_id(N'[' + @SplitTblName + ']') and OBJECTPROPERTY(id, N'IsTable') = 1)
frame procname=adhoc line=1 sqlhandle=0x01001100aebdbe2a883e4512000000000000000000000000
Execute MHP_UIB 'BAT_Charges','17dda9a5-c4e4-4656-b833-96a9b1fffbf5'
inputbuf
Execute MHP_UIB 'BAT_Charges','17dda9a5-c4e4-4656-b833-96a9b1fffbf5'
resource-list
keylock hobtid=281474978938880 dbid=17 objectname=UIRP-ArchiveTest.sys.sysschobjs indexname=clst id=lock13a0e340 mode=U associatedObjectId=281474978938880
owner-list
owner id=process92cf28 mode=S
waiter-list
waiter id=process738c58 mode=X requestType=convert
objectlock lockPartition=0 objid=1029578706 subresource=FULL dbid=17 objectname=UIRP-ArchiveTest.dbo.BAT_Charges_May06 id=lock16569400 mode=Sch-M associatedObjectId=1029578706
owner-list
owner id=process738c58 mode=Sch-M
waiter-list
waiter id=process92cf28 mode=Sch-S requestType=wait
can anyone help me in solving out this problem.
Thanks
Ankur Bhargava
February 7, 2009 at 7:03 am
That's tough to look at.
Next time try capturing a deadlock graph in your trace and it will become more obvious.
Jonathan Kehayias wrote a good article on this just last week or so
http://www.sqlservercentral.com/articles/deadlocks/65614/[/url]
One object has object_id(1029578706) Schema- M locked UIRP-ArchiveTest.dbo.BAT_Charges_May06
the other process has this object_id(281474978938880) S locked locked- UIRP-ArchiveTest.sys.sysschobjs
They each are looking for access to the other one to write to sys.sysschobjs and the other to perform an operation that requires schema stability (schema - S lock) on UIRP-ArchiveTest.dbo.BAT_Charges_May06
Looking at the code snippets and hearing what you're trying to accomplish... you may be able to use table partitioning to accomplish what you're after.
Either that or allow dirty reads UIRP-ArchiveTest.sys.sysschobjs may fix the immediate problem.
~BOT
Craig Outcalt
February 7, 2009 at 8:15 am
It may be fixes with the latest SP. (sp3)
Can you post sqlserver version info ?
Select @@version
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2009 at 8:25 am
Sure is hard agrees..but i see create indexes ...is this a index rebuild script?
February 9, 2009 at 12:20 am
Hi All,
Thanks for your replies. Below is the stored procedure which I am running.
Also my sql version is: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
CREATE PROCEDURE [dbo].[PartitionData]
@ArchiveTableName nvarchar(500) ,
@SessionId varchar(100)=''
AS
DECLARE @month_name varchar(3),
@year char(2),
@SplitTblName Varchar(25)
DECLARE @DropSplitTblScript char(16),
@CreateSplitTblScript nvarchar(200)
DECLARE @ConstraintString varchar(400),
@CreateConstraintScript nvarchar(400)
DECLARE @CreateIndex1Script nvarchar(500),
@CreateIndex2Script nvarchar(500),
@CreateIndex3Script nvarchar(500)
DECLARE @InsertSplitTblScript nvarchar(200),
@CreateDeleteScript nvarchar(500),
@CreateSelectScript nvarchar(4000),
@GrantPermissionsScript nvarchar(4000) ,
@Statement nvarchar(4000)
Create Table #temp (fMonth varchar(3),fyear char(2))
set @CreateSelectScript='Insert Into #temp select distinct left(datename(mm, InvoiceDate), 3) As fMonth, right(year(InvoiceDate), 2) As fYear from '+@ArchiveTableName
exec (@CreateSelectScript)
DECLARE curTableName CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR Select fMonth,fYear From #temp
SET NOCOUNT ON
OPEN curTableName
FETCH NEXT FROM curTableName into @month_name, @year
WHILE @@FETCH_STATUS = 0
begin
set @SplitTblName = @ArchiveTableName + '_' +@month_name + @year
set @ConstraintString = ' ( (datepart(month,InvoiceDate) = ' + cast(month(left(@month_name + @year, 3) + ' 01, 01') as char(2)) + ') and (datepart(year,InvoiceDate) = ' + cast(year('Jan 01, ' + right(@month_name + @year, 2)) as char(4)) + ')) '--' ArchiveDate >= ''' + Cast(@TimeStamp as Varchar(10))+ ''''
if NOT exists (select * from dbo.sysobjects where id = object_id(N'[' + @SplitTblName + ']') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
set @CreateSplitTblScript='select * into ' + @SplitTblName + ' from '+@ArchiveTableName+' where ' + CASE when @ArchiveTableName Like 'Bat_Archive' THEN 'RowNumber<0' ELSE ' InvoiceDate IS Null' END
exec sp_executesql @CreateSplitTblScript
set @CreateConstraintScript = 'ALTER TABLE ' + @SplitTblName + ' WITH NOCHECK ADD CONSTRAINT [CK_' + @SplitTblName + '] CHECK ' + @ConstraintString
exec sp_executesql @CreateConstraintScript
if (@ArchiveTableName='BAT_Addresses')
begin
set @CreateIndex1Script = 'CREATE UNIQUE CLUSTERED INDEX [PK_' + @SplitTblName + '_Addr] ON ' + @SplitTblName + ' ([InvoiceNumber], [InvoiceDate], [TrackingNo], [AddrType]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
set @CreateIndex2Script = ''
set @CreateIndex3Script='ALTER TABLE '+@SplitTblName+' WITH CHECK ADD CONSTRAINT [FK_' + @SplitTblName + '_2_Inv] FOREIGN KEY([InvoiceDate], [InvoiceNumber]) REFERENCES [BAT_Invoice] ([InvoiceDate], [InvoiceNumber]) ON UPDATE CASCADE ON DELETE CASCADE'
end
Else if (@ArchiveTableName='BAT_Charges')
BEGIN
set @CreateIndex1Script = 'CREATE CLUSTERED INDEX [PK_' + @SplitTblName + '_Chg] ON ' + @SplitTblName + ' ([InvoiceNumber], [InvoiceDate], [TrackingNo], [ChargeClassCode]) 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]'
set @CreateIndex2Script = 'CREATE UNIQUE NONCLUSTERED INDEX [' + @SplitTblName + '_2K_Shpmt] ON [' + @SplitTblName + '] ([InvoiceDate] ASC,[InvoiceNumber] ASC,[RowNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
set @CreateIndex3Script = 'ALTER TABLE '+@SplitTblName+' WITH CHECK ADD CONSTRAINT [FK_' + @SplitTblName + '_2_Inv] FOREIGN KEY([InvoiceDate], [InvoiceNumber]) REFERENCES [BAT_Invoice] ([InvoiceDate], [InvoiceNumber]) ON UPDATE CASCADE ON DELETE CASCADE'
END
Else if (@ArchiveTableName='BAT_Package')
BEGIN
set @CreateIndex1Script = 'CREATE UNIQUE CLUSTERED INDEX [PK_' + @SplitTblName + '_Pkg] ON ' + @SplitTblName + ' ([InvoiceNumber], [InvoiceDate], [TrackingNo]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
set @CreateIndex2Script = 'CREATE NONCLUSTERED INDEX [' + @SplitTblName + '_TkNo] ON [' + @SplitTblName + '] ([TrackingNo] 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]'
set @CreateIndex3Script = 'ALTER TABLE '+@SplitTblName+' WITH CHECK ADD CONSTRAINT [FK_' + @SplitTblName + '_2_Inv] FOREIGN KEY([InvoiceDate], [InvoiceNumber]) REFERENCES [BAT_Invoice] ([InvoiceDate], [InvoiceNumber]) ON UPDATE CASCADE ON DELETE CASCADE'
END
else
BEGIN
set @CreateIndex1Script = 'CREATE UNIQUE CLUSTERED INDEX [PK_' + @SplitTblName + '_RowNoInvNoDate] ON ' + @SplitTblName + ' ([RowNumber], [InvoiceNumber], [InvoiceDate]) WITH IGNORE_DUP_KEY ON [PRIMARY]'
set @CreateIndex2Script = ''
set @CreateIndex3Script = ''
END
exec sp_executesql @CreateIndex1Script
if (@CreateIndex2Script<>'')
BEGIN
exec sp_executesql @CreateIndex2Script
END
if (@CreateIndex3Script<>'')
BEGIN
exec sp_executesql @CreateIndex3Script
END
End
--Bat Archive Indexex Start
BEGIN TRY
if (@ArchiveTableName='BAT_Archive')
BEGIN
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1a')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1a] ON [dbo].['+@SplitTblName+']
(
[ChargeClassCode] ASC,[ChargeCatCode] ASC,[InvoiceDate] ASC,[RowNumber] ASC,[InvoiceNumber] ASC,[NetAmount] ASC,[TrackingNo] ASC,[RecipientNumber] ASC,[BilledWeight] ASC,[ChargeCatDtlCode] ASC,[ChargeDescCode] ASC,[ReceiverPostalCode] ASC,[TransactionDate] ASC,[Zone] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY];
'
EXEC dbo.sp_executesql @Statement;
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1b')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1b] ON [dbo].['+@SplitTblName+']
(
[TrackingNo] ASC,[ChargeClassCode] ASC,[ChargeCatCode] ASC,[InvoiceDate] ASC,[NetAmount] ASC,[RecipientNumber] ASC,[RowNumber] ASC,[InvoiceNumber] ASC,[BilledWeight] ASC,[ChargeCatDtlCode] ASC,[ChargeDescCode] ASC,[ReceiverPostalCode] ASC,[TransactionDate] ASC,[Zone] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY];'
EXEC dbo.sp_executesql @Statement;
END
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_36_54_6'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_36_54_6] ON [dbo].['+@SplitTblName+']([ChargeCatCode], [NetAmount], [InvoiceDate])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_1_7_6_45'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_1_7_6_45] ON [dbo].['+@SplitTblName+']([RowNumber], [InvoiceNumber], [InvoiceDate], [ChargeClassCode])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_1_7_6_54_45'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_1_7_6_54_45] ON [dbo].['+@SplitTblName+']([RowNumber], [InvoiceNumber], [InvoiceDate], [NetAmount], [ChargeClassCode])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_1_54_6_45_36_22'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_1_54_6_45_36_22] ON [dbo].['+@SplitTblName+']([RowNumber], [NetAmount], [InvoiceDate], [ChargeClassCode], [ChargeCatCode], [TrackingNo])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_7_54_6_45_36_22_3'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_7_54_6_45_36_22_3] ON [dbo].['+@SplitTblName+']([InvoiceNumber], [NetAmount], [InvoiceDate], [ChargeClassCode], [ChargeCatCode], [TrackingNo], [RecipientNumber])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_3_54_6_45_36_22_1'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_3_54_6_45_36_22_1] ON [dbo].['+@SplitTblName+']([RecipientNumber], [NetAmount], [InvoiceDate], [ChargeClassCode], [ChargeCatCode], [TrackingNo], [RowNumber])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_30_37_46_82_6_7_13_35_36_45'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_30_37_46_82_6_7_13_35_36_45] ON [dbo].['+@SplitTblName+']([BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [InvoiceDate], [InvoiceNumber], [TransactionDate], [Zone], [ChargeCatCode], [ChargeClassCode])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_22_30_37_46_54_82_6_36_45_1'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_22_30_37_46_54_82_6_36_45_1] ON [dbo].['+@SplitTblName+']([TrackingNo], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [NetAmount], [ReceiverPostalCode], [InvoiceDate], [ChargeCatCode], [ChargeClassCode], [RowNumber])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_6_45_36_1_7_30_37_46_82_13_35'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_6_45_36_1_7_30_37_46_82_13_35] ON [dbo].['+@SplitTblName+']([InvoiceDate], [ChargeClassCode], [ChargeCatCode], [RowNumber], [InvoiceNumber], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [TransactionDate], [Zone])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_22_54_6_7_45_36_30_37_46_82_13'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_22_54_6_7_45_36_30_37_46_82_13] ON [dbo].['+@SplitTblName+']([TrackingNo], [NetAmount], [InvoiceDate], [InvoiceNumber], [ChargeClassCode], [ChargeCatCode], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [TransactionDate])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_54_6_45_36_22_3_30_37_46_82_1'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_54_6_45_36_22_3_30_37_46_82_1] ON [dbo].['+@SplitTblName+']([NetAmount], [InvoiceDate], [ChargeClassCode], [ChargeCatCode], [TrackingNo], [RecipientNumber], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [RowNumber])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_54_6_22_1_7_45_36_30_37_46_82_13_35'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_54_6_22_1_7_45_36_30_37_46_82_13_35] ON [dbo].['+@SplitTblName+']([NetAmount], [InvoiceDate], [TrackingNo], [RowNumber], [InvoiceNumber], [ChargeClassCode], [ChargeCatCode], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [TransactionDate], [Zone])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_22_30_37_46_54_82_6_7_13_35_36_45_3'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_22_30_37_46_54_82_6_7_13_35_36_45_3] ON [dbo].['+@SplitTblName+']([TrackingNo], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [NetAmount], [ReceiverPostalCode], [InvoiceDate], [InvoiceNumber], [TransactionDate], [Zone], [ChargeCatCode], [ChargeClassCode], [RecipientNumber])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_22_6_1_54_7_45_36_3_30_37_46_82_13_35'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_22_6_1_54_7_45_36_3_30_37_46_82_13_35] ON [dbo].['+@SplitTblName+']([TrackingNo], [InvoiceDate], [RowNumber], [NetAmount], [InvoiceNumber], [ChargeClassCode], [ChargeCatCode], [RecipientNumber], [BilledWeight], [ChargeCatDtlCode], [ChargeDescCode], [ReceiverPostalCode], [TransactionDate], [Zone])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
END TRY
BEGIN CATCH
END CATCH
--Bat Archive Indexex END
--BAT_CHARGES Indexex Start
BEGIN TRY
IF (@ArchiveTableName='BAT_Charges')
BEGIN
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1a')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1a] ON [dbo].['+@SplitTblName+']
(
[InvoiceNumber] ASC,[InvoiceDate] ASC,[TrackingNo] ASC,[NetAmount] ASC,[IncentiveAmount] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1b')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1b] ON [dbo].['+@SplitTblName+']
(
[TrackingNo] ASC,[NetAmount] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1c')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1c] ON [dbo].['+@SplitTblName+']
(
[ChargeCatDtlCode] ASC,[InvoiceDate] ASC,[InvoiceNumber] ASC,[ChargeClassCode] ASC,[TrackingNo] ASC,[ChargeDescCode] ASC,[ChargeCatCode] ASC,[ChargeDesc] ASC,[NetAmount] ASC,[BillOptionCode] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_1_4'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_1_4] ON [dbo].['+@SplitTblName+']([InvoiceDate], [TrackingNo])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_5_6_1_4'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_5_6_1_4] ON [dbo].['+@SplitTblName+']([NetAmount], [IncentiveAmount], [InvoiceDate], [TrackingNo])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
SET @Statement = 'IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N''[dbo].['+@SplitTblName+']'') AND name = N''_dta_stat_'+@SplitTblName+'_2_1_4_5_6'')
CREATE STATISTICS [_dta_stat_'+@SplitTblName+'_2_1_4_5_6] ON [dbo].['+@SplitTblName+']([InvoiceNumber], [InvoiceDate], [TrackingNo], [NetAmount], [IncentiveAmount])'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
END TRY
BEGIN CATCH
END CATCH
--BAT_CHARGES Indexex End
--BAT_Packages Indexex Start
BEGIN TRY
IF (@ArchiveTableName='BAT_Package')
BEGIN
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].['+@SplitTblName+']') AND name = N''+@SplitTblName+'_New1a_K1_K2_K3_K4_K9_K11')
BEGIN
SET @Statement = 'CREATE NONCLUSTERED INDEX ['+@SplitTblName+'_New1a_K1_K2_K3_K4_K9_K11] ON [dbo].['+@SplitTblName+']
(
[InvoiceDate] ASC,[InvoiceNumber] ASC,[TrackingNo] ASC,[LeadShipmentNo] ASC,[EnteredWeight] ASC,[BilledWeight] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
--PRINT @Statement
EXEC dbo.sp_executesql @Statement;
END
END
END TRY
BEGIN CATCH
END CATCH
--BAT_Packages Indexex End
set @InsertSplitTblScript = 'Insert Into ' + @SplitTblName + ' select * from '+@ArchiveTableName+' where ' + @ConstraintString
if (@SessionId<>'')
BEGIN
SET @InsertSplitTblScript=@InsertSplitTblScript + ' AND SessionId=''' + @SessionId +''''
END
exec sp_executesql @InsertSplitTblScript
if exists (select * from dbo.sysusers where name = N'MSM_Admin')
Begin
set @GrantPermissionsScript='GRANT SELECT, INSERT, DELETE ON ' + @SplitTblName + ' TO MSM_Admin '
exec sp_executesql @GrantPermissionsScript
End
set @CreateDeleteScript='Delete From '+@ArchiveTableName+' Where ' + @ConstraintString
if (@SessionId<>'')
BEGIN
SET @CreateDeleteScript=@CreateDeleteScript + ' AND SessionId=''' + @SessionId +''''
END
exec sp_executesql @CreateDeleteScript
FETCH NEXT FROM curTableName
into @month_name, @year
End
Close curTableName
DEALLOCATE curTableName
exec MHP_CREATE_VIEW_UIB @ArchiveTableName
Thanks
Ankur Bhargava
February 9, 2009 at 12:35 am
ehm .... Microsoft SQL Server 2005 - 9.00.1399.06 ... thats RTM ! (+- 3 years without maintenance ??)
There are a number of fixes available and solved with SP3 (=current) !
It is worth the upgrade because IMO the product works way better now!
Keep in mind things will shift places in SSMS when you implement sp3 (they did it in sp2), so don't worry if you see that.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2009 at 10:06 am
ankur.bhargava (2/9/2009)
Hi All,Thanks for your replies. Below is the stored procedure which I am running.
What, at a high level, is it supposed to do?
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Developer edition? On a production server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2009 at 10:17 am
synce you run the sproc in parallel
and you create objects,
I'm not surprised you run into conflicts.
At least at catalog level (creating the objects, indexes,..).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply