DBCC INDEXDEFRAG

  • Comments posted to this topic are about the item DBCC INDEXDEFRAG

    Thanks

  • Thanks for the question.

    M&M

  • IGNORE THIS ENTRY SEE CORRECTION BY SQLkiwi Posted Today @ 4:23 AM

    Here we go again

    Try it as:

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)

    DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in the AdventureWorks_regular DB

    Your reference states

    DBCC INDEXDEFRAG cannot be used to defragment the following indexes:

    A disabled index.

    An index with page locking set to OFF.

    A spatial index.

    DBCC INDEXDEFRAG is not supported for use on system tables.

    It may be depreciated in the future and it may not be supported on system tables ...BUT IT WORKS NOW in both the MASTER DB and a system table in the AdventureWorks_regular data bases using SQL 2008 or SQL 2005

    My testing:

    Using 2005

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)--Original system table

    Pages Scanned Pages Moved Pages Removed

    14 6 4

    Using 2008

    DBCC INDEXDEFRAG (Master,"dbo.spt_values",spt_valuesclust)--System table

    Pages Scanned Pages Moved Pages Removed

    18 8 3

    Using 2008

    DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in AdventureWorks_regular

    Pages Scanned Pages Moved Pages Removed

    14 6 4

    IGNORE THIS ENTRY SEE CORRECTION BY SQLkiwi Posted Today @ 4:23 AM

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (8/1/2011)


    DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)

    DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in the AdventureWorks_regular DB

    Neither of those are system tables, they are ordinary tables that happen to be used by the system 🙂

  • nice question

  • What is spatial index?

  • ningaraju.n (8/2/2011)


    What is spatial index?

    See this link: http://msdn.microsoft.com/en-us/library/bb895265.aspx

  • Interesting question.

    Thanks.

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nice question thanks.

    http://brittcluff.blogspot.com/

  • good question!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Thanks for the straightforward question that I made tricky by seeing the first three bullets in the reference, eliminating those answers and deducing that the fourth choice must be correct. Which of course is covered in the very next line. Time to slow down this morning. 🙂

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Good question, but the answer does not match the question. The question does not ask about whether the command is being depricated. The answer is actually in the link posted, and the correct answer is wrong.:(

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • sjimmo (8/2/2011)


    Good question, but the answer does not match the question. The question does not ask about whether the command is being depricated. The answer is actually in the link posted, and the correct answer is wrong.:(

    I'm not sure what things looked like when you answered but I'm seeing this in the answer:

    However INDEXDEFRAG does not work with disabled indexes, spatial indexes, indexes that do not allow page locking, or system tables.

    And the correct answer is actually marked correct unless I'm missing something.

  • Happy to have gotten one right based on my gut feeling about it. 😉

    Means I'm learning something when I do read help files.... just sometimes it's filed wrong.

    DBCC CHECKDB('Marks_Brain')



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 1 through 15 (of 32 total)

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