August 5, 2011 at 10:34 am
Hi Guys,
I have a strange situation here.
I have two jobs on the same server.
One job runs the following code:
declare @table varchar(256)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
exec('DBCC DBREINDEX ('+@table+', " ", 0)')
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
declare @table2 varchar(256)
declare tables2 cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables2
fetch next from tables2 into @table2
while @@fetch_status = 0
begin
exec('UPDATE STATISTICS '+@table2+' WITH FULLSCAN')
fetch next from tables2 into @table2
end
CLOSE tables2
DEALLOCATE tables2
The second job only runs:
declare @table2 varchar(256)
declare tables2 cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables2
fetch next from tables2 into @table2
while @@fetch_status = 0
begin
exec('UPDATE STATISTICS '+@table2+' WITH FULLSCAN')
fetch next from tables2 into @table2
end
CLOSE tables2
DEALLOCATE tables2
They run against the same two databases.
The first one , wich first rebuild the indexs and then updates the statistics is faster then the second that only updates the statistics.
I even tried to run the first job first and then the second job but still the second job takes much more time to complete.
How can this happen , can someone help?
Thanks
August 5, 2011 at 10:55 am
First, you don't need to update stats after rebuilding indexes. The rebuild does that automatically as part of it. That's probably also why it's faster, they're already up to date by the time it gets to that step.
Second, I'd consider using the Alter Index All option for rebuilding, instead of the DBCC method.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2011 at 10:57 am
Sure, use this instead. It'll beat the crap out of either of those jobs for speed and it won't be deprecated in future releases...
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
As for your original question, not I don't see any reason. Are you sure the list of tables is the same in both jobs?
P.P.S. REINDEX + UPDATE FULLSCAN is redundant (partly). Only the stats outside of the index need rework so you're doing the same work twice (actually 3 times over if you run both jobs).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply