Need some help

  • 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

  • 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)

  • Is this Sp reorganizing indexes?

  • 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.

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

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