Post reply

Index question

  • November 22, 2013 at 9:44 am

    #282835

    Is there a way to script out the ONLINE option for an index? I want to list out the indexes and whether they were created with ON or OFF.

    I can find pretty much everything else about the index except that.

    Thank you,

    Josh

  • November 22, 2013 at 10:12 am

    #1668481

    whether the command was run with ONLINE or not is not saved anywhere, I'm pretty sure.

    peek at this scripting example, it essentially the same way i do it:

    http://www.sqlservercentral.com/scripts/Indexing/63620/

    generate the script with ONLINE if the server version is Enterprise or Developer, and the table does not contain any columns of datetype ('image','text','ntext','xml')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 22, 2013 at 10:50 am

    #1668487

    Thanks Lowell!

  • November 22, 2013 at 1:06 pm

    #1668521

    You have an option "Keep index online while reindexing" in the Rebuild Index Task element in SSIS. Pressing button "View T-SQL" will script the indexes with the ONLINE=ON.

    You can also play with the FillFactor and Sort in Tempdb settings.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • November 23, 2013 at 2:22 am

    #1668587

    Lowell (11/22/2013)

    whether the command was run with ONLINE or not is not saved anywhere, I'm pretty sure.

    It's not, because the option only affects the process of rebuilding and has no effect whatsoever on the index once created/rebuilt.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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