Time out in insert statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Time out in insert statement

Time out in insert statement

Rate Topic

Display Mode

Topic Options

Author

Message

mah_j

Posted Sunday, November 18, 2012 12:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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.

Post #1386031

Jeff Moden

Posted Sunday, November 18, 2012 12:48 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

mah_j (11/18/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1386089

mah_j

Posted Sunday, November 18, 2012 11:35 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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?

Post #1386166

Bhuvnesh

Posted Monday, November 19, 2012 2:11 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1386225

Bhuvnesh

Posted Monday, November 19, 2012 2:14 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1386229

Jeff Moden

Posted Monday, November 19, 2012 6:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

mah_j (11/18/2012)

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

Post #1386351

mah_j

Posted Wednesday, November 21, 2012 1:27 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

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.

Post #1387268

Bhuvnesh

Posted Wednesday, November 21, 2012 2:35 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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 Smile

. 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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1387304

mah_j

Posted Wednesday, November 21, 2012 2:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

Yes ,excuse me

I am going to read the links

Post #1387309

Jeff Moden

Posted Wednesday, November 21, 2012 6:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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

Post #1387390

« Prev Topic | Next Topic »

Permissions