Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Time out in insert statement Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 12:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:50 AM
Points: 108, Visits: 1,086
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
Posted Sunday, November 18, 2012 12:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386089
Posted Sunday, November 18, 2012 11:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:50 AM
Points: 108, Visits: 1,086
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
Posted Monday, November 19, 2012 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1386225
Posted Monday, November 19, 2012 2:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1386229
Posted Monday, November 19, 2012 6:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386351
Posted Wednesday, November 21, 2012 1:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:50 AM
Points: 108, Visits: 1,086
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
Posted Wednesday, November 21, 2012 2:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1387304
Posted Wednesday, November 21, 2012 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:50 AM
Points: 108, Visits: 1,086
Yes ,excuse me

I am going to read the links
Post #1387309
Posted Wednesday, November 21, 2012 6:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1387390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse