May 20, 2010 at 8:51 am
I'm debuggin a prod issue. There is a job that runs the below sql. I don't know who wrote it and there are some serious issues i have w/ the way it was implimented. Specifically the loading of the top 5,000,000 into a table variable. Regardless i changed it to top 5000 for testing purposes and i still get an error. Proc Desc: Looks like this is an archiving process, peeling recs who are 90 days old and pushing them to an archive db. Can you guys take a cursory look and let me know if there is something obvious i'm missing here. I've never seen that sort of error.
Script:
--begin proc============================================================
set ansi_warnings off
set nocount on
declare @rc int,
@ptr int,
@batch int
declare @temp table (rid int identity(1,1) primary key,
visitorid int not null)
insert @temp (visitorid)
Select top 5000000 a.visitorid
From (
Select top 5000000 visitorid
From dbo.Visitors with (nolock)
Where StartVisitDateTime < dateadd(dd,-90,convert(char(8),getdate(),112))
order by Visitorid
) a
Order by a.Visitorid
set @rc = @@ROWCOUNT
set @ptr = 1
set @batch = 400
print getdate()
--select *
--from @temp
While @ptr <= @rc
begin
Insert mgm_archive.dbo.VisitorSources_archive with (tablock)
select vs.VisitorID,vs.SourceID,vs.VisitDate,vs.MarketingMaterialID,vs.SubIDID,vs.FirstSource,vs.ImageID,vs.CampaignID
from @temp t
inner join dbo.VisitorSources vs with (nolock)
on vs.visitorid = t.visitorid and
t.rid between @ptr and @ptr + @batch - 1
left outer join mgm_archive.dbo.VisitorSources_archive a with (nolock)
on a.visitorid = vs.visitorid and
a.sourceid = vs.sourceid and
a.visitdate = vs.visitdate
where a.visitorid is null
If @@ERROR <> 0
begin
Raiserror('Error inserting VisitorSources',16,1)
Return
end
Delete vs with (rowlock)
From @temp t
inner join dbo.VisitorSources vs
on vs.visitorid = t.visitorid and
t.rid between @ptr and @ptr + @batch - 1
If @@ERROR <> 0
begin
Raiserror('Error deleting VisitorSources',16,1)
Return
end
Insert mgm_archive.dbo.Visitors_archive with (tablock)
select vs.VisitorID,vs.MasterID,vs.MerchantID,vs.ReturnVisitor,vs.PagesViewed,vs.SourceCount,vs.SourceID,vs.IPAddress,
vs.BrowserID,vs.ColorDepth,vs.OSID,vs.ScreenHeight,vs.ScreenWidth,vs.MarketingMaterialID,vs.ImageID,vs.SubIDID,vs.CampaignID,
vs.StartVisitDateTime,vs.SourceVisitTime,vs.RefPageID,vs.FirstPageID,vs.LastPageID,vs.LastPageVisitDateTime,
vs.LastVisitorPageID,vs.HasCookies,vs.CookieCount
from @temp t
inner join dbo.Visitors vs with (nolock)
on vs.visitorid = t.visitorid and
t.rid between @ptr and @ptr + @batch - 1
left outer join mgm_archive.dbo.Visitors_archive a with (nolock)
on a.visitorid = vs.visitorid
where a.visitorid is null
If @@ERROR <> 0
begin
Raiserror('Error inserting Visitors',16,1)
Return
end
Delete vs with (rowlock)
From @temp t
inner join dbo.Visitors vs
on vs.visitorid = t.visitorid and
t.rid between @ptr and @ptr + @batch - 1
If @@ERROR <> 0
begin
Raiserror('Error deleting Visitors',16,1)
Return
end
set @ptr = @ptr + @batch
waitfor delay '00:00:00.010'
end
print getdate()
print @ptr
--end proc============================================================
ERROR: when i change it to top 5000 i get the below error
May 20 2010 10:47AM
Location: scanrid.cpp:359
Expression: m_len != 0
SPID: 141
Process ID: 3448
Msg 3624, Level 20, State 1, Line 77
ERROR: When i let it run as is i get the below error
May 20 2010 10:49AM
Location: scanrid.cpp:359
Expression: m_len != 0
SPID: 141
Process ID: 3448
Msg 3624, Level 20, State 1, Line 77
May 20, 2010 at 9:02 am
equally as odd is the fact that my qury anal conn shows disconnected when it errors.
May 20, 2010 at 10:07 am
BaldingLoopMan (5/20/2010)
equally as odd is the fact that my qury anal conn shows disconnected when it errors.
There is nothing odd about the connection. Sev 20 errors do exactly that. From what I understand, the problem might be caused by the log file size getting close to run the disk on which it is located out of space. Please check how much log growth the batch causes.
Oleg
May 20, 2010 at 10:33 am
I had to debug a painfully similar script about a year ago. The problem we had was limited to the enormous log file growth and ridiculously slow execution times. The remedy I found was very simple and consisted of 2 simple tweaks:
1. Use the real temp table instead of the table variable. The problem with table variable is that you cannot create a non-clustered index on it (unless you create non-clustered PK, which is not the case in your batch), and it appears that your batch might benefit from the non-clustered index on the visitorid column in the temp table (depending on cardinality of course, but you can make this decision because you see the data).
2. Definitely try to play with the value of the batch size variable. Currently, it appears to be too small. At 400 batch size and 5,000,000 records in the temp table it will do 12,500 iterations in the while loop, each inserting, deleting and inserting data. In my case I had to crank it up to 50,000 to see a huge boost in performance and so it was adjusted to that value with no problems since.
Oleg
May 21, 2010 at 5:04 am
This is SQL Server 2000, right? What is the exact version please? Is the VisitorSources table a heap with non-clustered indexes? The reason I ask is that the code assertion failure you are seeing (line 359 of scanrid.cpp) can happen during deletion of a non-clustered index entry on a heap.
Before doing anything else, check you have full verified backups and run DBCC CHECKDB on that database.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2010 at 5:07 am
Oleg Netchaev (5/20/2010)
...The problem with table variable is that you cannot create a non-clustered index on it...
Well you can, it just has to be unique and created at declaration time:
DECLARE @temp
TABLE (
rid INTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
visitorid INTEGER NOT NULL UNIQUE NONCLUSTERED
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2010 at 8:45 am
Paul White NZ (5/21/2010)
Oleg Netchaev (5/20/2010)
...The problem with table variable is that you cannot create a non-clustered index on it...Well you can, it just has to be unique and created at declaration time:
DECLARE @tempTABLE (
rid INTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
visitorid INTEGER NOT NULL UNIQUE NONCLUSTERED
);
I was just trying to point out that nonclustered index might be not an option in this specific case if table variable is used. This is because I was not sure that visitorid is unique and not unique nonclustered index cannot be created/added.
Paul, do you remember if the table variables existed in SQL Server 2000? If not then he has to have higher than 2000 version right?
I still believe that whatever I suggested in my last post might make sense. I had to work on the problem really similar to the one described.
Oleg
May 21, 2010 at 8:51 am
Oleg Netchaev (5/21/2010)
I was just trying to point out that nonclustered index might be not an option in this specific case if table variable is used. This is because I was not sure that visitorid is unique and not unique nonclustered index cannot be created/added.
I see. One can always make an index unique by including the PK of course!
Paul, do you remember if the table variables existed in SQL Server 2000? If not then he has to have higher than 2000 version right?
Introduced in SQL Server 2000, yes.
I still believe that whatever I suggested in my last post might make sense. I had to work on the problem really similar to the one described.
You might well be right, Oleg. I just wanted to add that the system assertion has a specific cause.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2010 at 8:58 am
I saw on other posts yesterday that line 359 hints that the database is near corruption, I just hope that this is not the case.
Oleg
May 21, 2010 at 9:06 am
Oleg Netchaev (5/21/2010)
I saw on other posts yesterday that line 359 hints that the database is near corruption, I just hope that this is not the case.
Agreed. Definitely time for a DBCC CHECKDB (though I'm sure BaldingLoopMan runs them regularly ;-))
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply