SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time out in insert statement


Time out in insert statement

Author
Message
mah_j
mah_j
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 1262
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216748 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mah_j
mah_j
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 1262
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?
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13390 Visits: 4077
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;-)
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13390 Visits: 4077
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;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216748 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mah_j
mah_j
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 1262
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.
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13390 Visits: 4077
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;-)
mah_j
mah_j
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 1262
Yes ,excuse me

I am going to read the links
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216748 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search