Post reply

Time out in insert statement

  • November 18, 2012 at 12:30 am

    #271029

    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.

  • November 18, 2012 at 12:48 pm

    #1560117

    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)

  • November 18, 2012 at 11:35 pm

    #1560170

    Thanks Jeff

    The time out is on inserts.But i can not test any thing on main server.I even could not run DBCC SHOWCONTIG ('dbo.Table_Name' ) to see if it is necessary to rebuild the indexes.

    (it takes a lot of time and causes many time out)

    Is there any other way to find the hot index?

    What do you mean by partitioning?Do you mean using partitioning with an appropriate range of data in that table to separate the files?

  • November 19, 2012 at 2:11 am

    #1560213

    mah_j (11/18/2012)

    But i can not test any thing on main server.Is there any other way to find the hot index?

    this can help you here http://msdn.microsoft.com/en-us/library/ms188917(v=sql.90).aspx

    mah_j (11/18/2012)

    What do you mean by partitioning?Do you mean using partitioning with an appropriate range of data in that table to separate the files?

    yes , in this way you can shift the IO oerhead to other data files (disks/drives)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 19, 2012 at 2:14 am

    #1560216

    mah_j (11/18/2012)

    What do you mean by partitioning?Do you mean using partitioning with an appropriate range of data in that table to separate the files?

    see this for advantages http://sqlserverpedia.com/wiki/Partitioning_Query_Performance_Benefits

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 19, 2012 at 6:28 am

    #1560326

    mah_j (11/18/2012)

    Thanks Jeff

    The time out is on inserts.But i can not test any thing on main server.I even could not run DBCC SHOWCONTIG ('dbo.Table_Name' ) to see if it is necessary to rebuild the indexes.

    (it takes a lot of time and causes many time out)

    Is there any other way to find the hot index?

    Yes... I haven't tried the code therein because I've always just been able to look at the first column of the indexes and test the cardinality of the column using a distinct on the column but I'vee not had to do that with a table quite as large. See the following link for alternate methods that actually detect page splits as recorded in the log file.

    http://www.sqlservercentral.com/blogs/sqlballs/2012/08/06/how-to-find-bad-page-splits/

    What do you mean by partitioning?Do you mean using partitioning with an appropriate range of data in that table to separate the files?

    Yes although they can remain on the same file. I don't know about the claims of performance cited in the article (because I've not worked anywhere that has the luxury of split disk space) that Bhuvnesh posted but I do know partitions that are setup in a temporal fashion (by age of mostly static rows) can really take a load off of index maintenance. If you have the Enterprise Edition, lookup "Table Partitioning" in Books Online (press the {f1} key to get there. If you have the Standard Edition, lookup "Partitioned Views".

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

  • November 21, 2012 at 1:27 am

    #1561114

    Thank you both for your comments.

    Does it have any effect,If i implement partitioning only on one drive ?

    I will read more,and also focus on partitioning.

  • November 21, 2012 at 2:35 am

    #1561144

    mah_j (11/21/2012)

    Thank you both for your comments.

    Does it have any effect,If i implement partitioning only on one drive ?

    I will read more,and also focus on partitioning.

    your question depicts that you havent read the link 🙂 . actually partitioninig gives you edge when u distribute/segregate the data on diferent disk to divide the IO load . see the link for more details

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 21, 2012 at 2:53 am

    #1561149

    Yes ,excuse me

    I am going to read the links

  • November 21, 2012 at 6:01 am

    #1561219

    mah_j (11/21/2012)

    Thank you both for your comments.

    Does it have any effect,If i implement partitioning only on one drive ?

    I will read more,and also focus on partitioning.

    Paritiioning really isn't a performance tool. Even if you write queries that work against only one partition, that partition will still be on a single file space and will act no more differently than if all the data was on a single partition. Depending on the query, it might not even help if you always end up with scans instead of seeks. Sure, you can get some benefit by doing things like putting indexes on a different physical drive space than the data but nothing will ever make up for not having a good database design and writing good queries. The horizotal partitioning that we're talking about is to ease maintenance time by making it so that you don't have to reindex the whole of a table.

    Partioning of data is not a performance panacea.

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

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

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