Post reply

DBCC INDEXDEFRAG

  • August 1, 2011 at 9:15 pm

    #241560

    Comments posted to this topic are about the item DBCC INDEXDEFRAG

    Thanks

  • August 1, 2011 at 9:28 pm

    #1361949

    Thanks for the question.

    M&M

  • August 1, 2011 at 9:30 pm

    #1361950

    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 ScannedPages MovedPages Removed

    18 8 3

    Using 2008

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

    Pages ScannedPages MovedPages 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]

  • August 2, 2011 at 1:12 am

    #1361964

    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 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 2, 2011 at 1:55 am

    #1361974

    nice question

  • August 2, 2011 at 1:57 am

    #1361976

    What is spatial index?

  • August 2, 2011 at 2:23 am

    #1361983

    ningaraju.n (8/2/2011)

    What is spatial index?

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

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 2, 2011 at 2:38 am

    #1361988

    Interesting question.

    Thanks.

  • August 2, 2011 at 3:54 am

    #1362015

    Nice question, thanks.

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

  • August 2, 2011 at 5:08 am

    #1362035

    Nice question thanks.

    http://brittcluff.blogspot.com/

  • August 2, 2011 at 5:22 am

    #1362048

    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!

  • August 2, 2011 at 5:48 am

    #1362062

    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

  • August 2, 2011 at 6:35 am

    #1362096

    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

  • August 2, 2011 at 6:40 am

    #1362102

    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.

  • August 2, 2011 at 6:55 am

    #1362115

    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 31 total)

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