Need some help

Question

Need some help

clarification

Mr or Mrs. 500

Points: 558
More actions
July 31, 2011 at 5:12 pm

#255020

please check and reply what does this SP do?
This SP is scheduled to execute daily on one important database which users are performing lot of operations daily.
My doubt is if this job runs daily, is there necessary
to do dbcheck integrity and rebuild indexes weekly on this database?Its sql server 2008R2 enterprise.
create procedure [dbo].[Reindex] as
begin
set nocount on
declare @indexid uniqueidentifier
declare @indexname sysname
declare @entityid uniqueidentifier
declare @baseTableName sysname
declare @extensionTableName sysname
declare @fillfactor int
declare @isClustered bit
declare @isPrimary bit
declare indexCursor cursor FORWARD_ONLY READ_ONLY for
select IndexId, Name, EntityId, IsClustered, IsPrimaryKey, SqlFillFactor
from EntityIndex
order by EntityId, IsClustered desc
open indexCursor
fetch next from indexCursor into @indexid, @indexname, @entityid, @isClustered, @isPrimary, @fillfactor
while (@@fetch_status = 0)
begin
declare @hasBaseIndex int
declare @hasExtensionIndex int
select @hasBaseIndex = count(*)
from AttributeView
where IsCustomField = 0 and
AttributeId in (select AttributeId from IndexAttributes where IndexId = @indexid)
select @hasExtensionIndex = count(*)
from AttributeView
where IsCustomField <> 0 and
AttributeId in (select AttributeId from IndexAttributes where IndexId = @indexid)
declare @sqlstr nvarchar(2048)
select @baseTableName = BaseTableName, @extensionTableName = ExtensionTableName from EntityView where EntityId = @entityid
if (@isClustered <> 0 and @isPrimary <> 0)
begin
select @sqlstr = 'DBCC DBREINDEX(' + @baseTableName + ', PK_' + @baseTableName + ', 80)'
print @sqlstr
fetch next from indexCursor into @indexid, @indexname, @entityid, @isClustered, @isPrimary, @fillfactor
continue
end
if (@hasBaseIndex <> 0)
begin
select @sqlstr = 'DBCC DBREINDEX(' + @baseTableName + ', ' + @indexname + ', ' + convert(nvarchar(128), @fillfactor) + ')'
print @sqlstr
exec sp_executesql @sqlstr
select @sqlstr = null
end
if (@hasExtensionIndex <> 0)
begin
select @sqlstr = 'DBCC DBREINDEX(' + @extensionTableName + ', ' + @indexname + ', ' + convert(nvarchar(128), @fillfactor) + ')'
print @sqlstr
exec sp_executesql @sqlstr
select @sqlstr = null
end
--
-- remove empty indexes
--
if (@hasExtensionIndex = 0 and @hasBaseIndex = 0)
begin
delete from EntityIndex where IndexId = @indexid
end
fetch next from indexCursor into @indexid, @indexname, @entityid, @isClustered, @isPrimary, @fillfactor
end -- indexCursor
close indexCursor
deallocate indexCursor
--
-- special case extension table PKs
--
declare entityCursor cursor FORWARD_ONLY READ_ONLY for
select ExtensionTableName from EntityView where IsCustomizable <> 0
open entityCursor
fetch next from entityCursor into @extensionTableName
while (@@fetch_status = 0)
begin
select @sqlstr = 'DBCC DBREINDEX(' + @extensionTableName + ', PK_' + @extensionTableName + ', 80)'
print @sqlstr
exec sp_executesql @sqlstr
fetch next from entityCursor into @extensionTableName
end
close entityCursor
deallocate entityCursor
end

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

clarification (7/31/2011)
please check and reply what does this SP do?
This SP is scheduled to execute daily on one important database which users are performing lot of operations daily.
My doubt is if this job runs daily, is there necessary
to do dbcheck integrity and rebuild indexes weekly on this database?Its sql server 2008R2 enterprise.

From a cursory (NPI :-D) glance, it's a backup routine and it's based off of some properties stored in one of your tables.

Since this is on an "important database", I'd suggest you sit down, go hand-over-hand on it, and document it with some thoughtful comments because you're the person who will need to maintain it. I could do it for you, but you'd learn nothing of your own system that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clarification Mr or Mrs. 500 Points: 558 More actions · Answer 2

clarification

Mr or Mrs. 500

Points: 558

August 1, 2011 at 11:35 am

#1361733

Is this Sp reorganizing indexes?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

clarification (8/1/2011)
Is this Sp reorganizing indexes?

Ya but I'd use this instead.

http://sqlfool.com/2010/04/index-defrag-script-v4-0

That doesn't update the stats, but if the db is small enough donig fullscan updates on all index will run in a few minutes so I wouldn't bother getting too cute with it.