• Replies 16 replies
  • Subscribers 336 subscribers
  • Views 142 views
  • Users 0 members are here
Related

Maintenance Plan Reorganise index

Ben Gu
$organization

Hi All,

Good day.

our Reorganise index Maintenance paln always failed for a while.

I never figure out what is wrong before.

today I used the SSIS package "Reorganize Index Task" to run the task, finally find out what cause the job failure.

the error message is :

 

Msg 2552, Level 16, State 1, Line 1
The index "IND_TW_SLI_DETAIL_BYPASS_spid" (partition 1) on table "TW_SLI_DETAIL_BYPASS" cannot be reorganized because page level locking is disabled.

This is v9.0 release Notes:

985

Indexes on the following tables have been reorganized to improve performance: TX_PERF_PMAP, TX_APPEAL_MEDIA_TYPE, T_MEMB_ORG, T_EADDRESS, T_ROLE, T_PRODUCTION, T_CUST_ACTIVITY, T_ISSUE_ACTION, T_ACCOUNT_DATA and T_LINEITEM.  In addition the index on the work table TW_SLI_DETAIL_BYPASS has been changed to disallow page_locks to improve concurrency.  Indexes were added to T_BATCH and T_SEAT to cover some frequently occurring queries.  Two indexes on T_LINEITEM were removed, as they were used very infrequently.

 

 

 Final solution:

USE [impresario]
GO
ALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS]  SET (ALLOW_PAGE_LOCKS = ON)
GO

run my Reorganise index Job


USE [impresario]
GO
ALTER INDEX [IND_TW_SLI_DETAIL_BYPASS_spid] ON [dbo].[TW_SLI_DETAIL_BYPASS]  SET (ALLOW_PAGE_LOCKS = OFF)
GO

 

have fun

Ben

Parents
  • Former Member
    Former Member $organization

    Hi Ben

    There's almost certainly no point in reorganising indexen on TW_SLI_DETAIL_BYPASS anyway - it's a work table that only holds small amounts of data (and only holds it while an order is open, I think, so the indexen only exist momentarily as well), so there's no chance for them to get fragmented, so they won't ever need to be re-organised....

    In general, I think you're better off using a structured approach to rebuilding/re-organising indexes, rather than the one-size-fits-all approach used by the SSIS package task or the Tess utility, so that you only reorganise/rebuild indexes that need to be re-organised/rebuilt - certainly saves processing time and performance (and log generation).

    Ken

  • $organization in reply to Former Member

    Hi Ken,

    Good day.

    Thank you for your great advice.  I should think about some of local tables too.

    What I fixed here is a standard SQL task. I think most of us maybe have the same issue as mine.

    Sometime I opened the job history, all jobs were green, except this Reorganise index was red.

    Now they all can be green again. someone suggests this on-off error is a MS bug.

    Your advice should be my next step.

    Thank you very much.

    have fun.

    Ben

  • Former Member
    Former Member $organization in reply to Ben Gu

    Hi Ben,

     

    I had the same issue when we moved to 9.0.0.2. I recreated my plans and have been ok since then. I had a standard SQL task go and “fix” all my indexes as needed as well.

     

    Thank you have a good day!

    Naomi Williams

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben Gu
    Sent: Monday, March 22, 2010 2:12 AM
    To: Naomi Williams
    Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Ken,

    Good day.

    Thank you for your great advice.  I should think about some of local tables too.

    What I fixed here is a standard SQL task. I think most of us maybe have the same issue as mine.

    Sometime I opened the job history, all jobs were green, except this Reorganise index was red.

    Now they all can be green again. someone suggests this on-off error is a MS bug.

    Your advice should be my next step.

    Thank you very much.

    have fun.

    Ben

    From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com>
    Sent: 3/22/2010 1:41:42 AM

    Hi Ben

    There's almost certainly no point in reorganising indexen on TW_SLI_DETAIL_BYPASS anyway - it's a work table that only holds small amounts of data (and only holds it while an order is open, I think, so the indexen only exist momentarily as well), so there's no chance for them to get fragmented, so they won't ever need to be re-organised....

    In general, I think you're better off using a structured approach to rebuilding/re-organising indexes, rather than the one-size-fits-all approach used by the SSIS package task or the Tess utility, so that you only reorganise/rebuild indexes that need to be re-organised/rebuilt - certainly saves processing time and performance (and log generation).

    Ken




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • Former Member
    Former Member $organization in reply to Ben Gu

    Hi Ben,

     

    I had the same issue when we moved to 9.0.0.2. I recreated my plans and have been ok since then. I had a standard SQL task go and “fix” all my indexes as needed as well.

     

    Thank you have a good day!

    Naomi Williams

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ben Gu
    Sent: Monday, March 22, 2010 2:12 AM
    To: Naomi Williams
    Subject: Re: [Tessitura Technical Forum] Maintenance Plan Reorganise index

     

    Hi Ken,

    Good day.

    Thank you for your great advice.  I should think about some of local tables too.

    What I fixed here is a standard SQL task. I think most of us maybe have the same issue as mine.

    Sometime I opened the job history, all jobs were green, except this Reorganise index was red.

    Now they all can be green again. someone suggests this on-off error is a MS bug.

    Your advice should be my next step.

    Thank you very much.

    have fun.

    Ben

    From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com>
    Sent: 3/22/2010 1:41:42 AM

    Hi Ben

    There's almost certainly no point in reorganising indexen on TW_SLI_DETAIL_BYPASS anyway - it's a work table that only holds small amounts of data (and only holds it while an order is open, I think, so the indexen only exist momentarily as well), so there's no chance for them to get fragmented, so they won't ever need to be re-organised....

    In general, I think you're better off using a structured approach to rebuilding/re-organising indexes, rather than the one-size-fits-all approach used by the SSIS package task or the Tess utility, so that you only reorganise/rebuild indexes that need to be re-organised/rebuilt - certainly saves processing time and performance (and log generation).

    Ken




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data