• mah_j (11/18/2012)


    Hi every body

    I have a large table (270 G) and i only have insert and select statement over this table.

    I have a lot of time out but i don't know what can i do for this problem and what to check.

    The table has a clustered index(primary key) and 6 other non clustered indexes.

    I have checked the unused indexes by monitoring over dm_db_index_usage_stats during a week ,hence there isn't any unused index.

    Any help would be appreciated.

    If it's timing out on inserts, there's a very high probability that at least one of your indexes has a leading column with very low cardinality (for lack of a better term) and suffers from massive page (clustered index) or extent (non-clustered indexes) splits. Find those indexes, disable them (except for the clustered index) and see if that fixes the problem. If it doesn't, then you may have picked the wrong columns for the clustered index (should be narrow, unique, and ever increasing). If it does, then you'll need to come up with a better index than the one(s) you disabled.

    That's not the end of the road for this problem but that's where I'd likely start with the caveat understanding that when you re-enable the index, it's going to rebuild the index and THAT could take quite a while on such a large table.

    If it's timing out on SELECTs, then consider redactinng the queries to use "Divide'n'Conquer" methods instead of "all-in-one" queries and make sure that all criteria is capable of doing index seeks.

    As a sidebar to that, consider partioning the table to reduce the impact that index maintenance will have on such a large table. You might also want to consider developing a data-archive plan to keep the size of the table in check.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)