Post reply

Index related problems?? Whats happening here?

  • September 21, 2005 at 1:13 pm

    #166081

    All queries for a particular table seems to be slow. It has one clustered index on the primary key column which of data type INT and has identity insert ON. This table has < 10000 rows and is fast with response in all other circumstances. The clustered index is at a fill factor of 90% and I have toyed upto 70% fillfactor. When it is slow I ran DBCC SHOWCONTIG and there were signs of fragmentation which didn't look very serious. The BOL says it is not reliable for smaller tables. I run DBCC INDEXDEFRAG on a particular database. The results suggest that there were 72 pages and 72 pages were moved and 0 deleted. Still no improvement in performance. I run DBCC DBREINDEX and viola query runs fast... I am happy but what is happening here?
    All help is welcome and appreciated...

    Thanks
    JackofAllMasterofNone

  • September 21, 2005 at 1:31 pm

    #591989

    Do you have the before and after execution plans?

    Did you try to simply update the stats (maybe with a deeper scan).

  • September 21, 2005 at 1:41 pm

    #591991

    I will try to post it as soon as I get the problem again. And also I will try to just update the stats although I am sure its not the stats beacuse I hev set the autoupdate statistics to yes and I did not see any changes for the indexes starting with _WASYS_ which are supposed to be stats..

    A

  • September 21, 2005 at 1:45 pm

    #591992

    What changes did you look at to affirm that??

    Also yes those are the stats I'm reffering to.

  • September 21, 2005 at 1:57 pm

    #591997

    There were no changes to the pages moved when I ran DBCC INDEXDEFRAG

  • September 21, 2005 at 1:58 pm

    #591998

    How does that [not] affect the statistics??

  • September 21, 2005 at 2:02 pm

    #592000

    I don't know I am trying to make an [un]intelligent guess no offence to you... I will get back with more info when I encounter the problem. This is a ongoing saga and I am not able to recreate the problem. It just happens....

  • September 21, 2005 at 2:13 pm

    #592003

    Start with this part >>

    Do you have the before and after execution plans?

    It could become usefull.

  • September 22, 2005 at 2:09 am

    #592106

    ok - so youve got a c/index on a pk that is an integer - thats great except - is that the field you are using when retriveing data - i suspect not if its a counter - try adding some useful indices.

  • September 22, 2005 at 12:23 pm

    #592353

    One point not mentioned in relation to UPDATE STATISTICS. When you execute this command on a table you then need to execute the following code:

     

    exec sp_recompile table_name --> the table_name is the one the stats were updated for

     

    This allows the optimizer to taker advantage of updated key/index distribution statistics for all stored procedures that use this table (a plan recompilation on the next execution).

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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