Post reply

Rebuiliding index on a particular table causes the CPU at 100% to dip

  • August 29, 2011 at 10:37 pm

    #256446

    I have a SQL 2005 ent 64 edition on my prod envirronment.I have a table called Assigments.We were exepriencing a high ~CPU 100% throughout the day last few days.When i redindexed the clustered index of the above mentioned table i found the CPU dipping to 30-40%.

    Does this means that the CPU is taking lot many cycles to access the Assignments table.I am not using online reindexing.

    thanks

  • August 30, 2011 at 2:38 pm

    #1375241

    It can be Statistics related issue (as rebuild index updates statistics)

  • August 30, 2011 at 3:43 pm

    #1375266

    I agree with Yuri. It could be a statistics issue. How did you reindex the clustered index? If you told the table to rebuild all then you rebuilt any non-clustered indexes you had as well. So if you had non-clustered indexes with a lot of fragmentation you may have fixed performance issues as well.

    Joie Andrew
    "Since 1982"

  • August 31, 2011 at 12:09 am

    #1375306

    Guys i forgot to mention a important thing...the CPU shoots back at 100% after the rebuiliding completes and i am only rebuilding the clustered index.

    thanks

  • August 31, 2011 at 7:05 am

    #1375441

    I believe the CPU dipped when you rebuilt the clustered index because a) that operation kicks the shit out of your IO and probably causes CPUs to wait for data and b) that operation locks the table completely for DML access thus your inserts/updates/deletes are all blocked.

    I HIGHLY recommend you find out why your CPUs are running 100% steady state and start some SERIOUS tuning efforts. Consider engaging a professional to help out if you aren't up to the task.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • September 3, 2011 at 2:13 am

    #1376741

    Can you tell what step you are performing for Rebuild Index?

  • September 5, 2011 at 1:26 am

    #1376960

    a standard dbcc dbreindex statement would do the same on the particilau table.Infact any index on the table is rebuilt it causes the cpu to dive and come back again after the rebuild is complete

    thanks

  • September 6, 2011 at 5:35 am

    #1377307

    If the statistics are not up to dat due to massive data skew in your large indexes, the rebuild index will not parallelize properly.

    I would recommand you to update the stats first and try rebuild.

  • September 6, 2011 at 7:18 am

    #1377392

    HI ,

    Can you run the profiler and find out from which hostname the long running quries are hitting .

  • September 6, 2011 at 7:19 am

    #1377395

    I agree with SQLGuru. You need to identify which of your stored procs that references the table is eating up your CPU. Check your Wait types. You most probably see high CXPacket waits. This could mean high amount of parallelism.

    Is this an OLTP server? If it is just one Query that is eating up your CPU, you could consider a MAXDOP hint for just that query.

    Just my 2 cents

    -Roy

Viewing 10 posts - 1 through 9 (of 9 total)

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