April 7, 2010 at 12:24 am
Hello Guys,
I have table that give extremely slow performance when inserting data. I have executed command check dbcc and found there are more than 8 billion rows!
There are 7901047676 rows in 227847395 pages for object "db_owner.scr_fct_exact_access".
Retention period is 13 month, and we have not hit the retention period yet!
Can any one please help me point in right direction.
Thank you
Kena
April 7, 2010 at 12:54 am
I would look at partitioning the table. Check that out in BOL. It could be a performance saver for you.
Also, you should check the query being used for the insert and verify that it is optimized.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 2:21 pm
In addition to what Jason has shared I'd also investigate indexes and triggers. Too many indexes can slow down inserts and triggers can definitely be a killer.
I'd also look at the clustered index (if there is one), because inserting into the middle of a clustered index can slow things down as well (Page Splits).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 7, 2010 at 2:23 pm
Jack Corbett (4/7/2010)
In addition to what Jason has shared I'd also investigate indexes and triggers. Too many indexes can slow down inserts and triggers can definitely be a killer.I'd also look at the clustered index (if there is one), because inserting into the middle of a clustered index can slow things down as well (Page Splits).
Sound advice and certainly worth a look at doing before partitioning the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2010 at 3:47 pm
Thank you guys, these are all great advice!!!! Thank you again!
April 9, 2010 at 6:59 am
ah0996 (4/8/2010)
Thank you guys, these are all great advice!!!! Thank you again!
You should also share the solution you used to fix above issue.So that other people can also learn from it
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 9, 2010 at 8:22 am
You sound very surprised about the number of rows - are you sure it's actually true? I'd be inclined to run some DBCC to make sure it's not got its knickers in a twist - DBCC CHECKTABLE with PHYSICAL_ONLY would be a good move, but be warned, it'll use a lot of TempDB space and slow the server right down - so do it out of hours!
April 9, 2010 at 9:01 am
On the other hand a large heap (no clustered index) would INSERT very slowly (I have come across some prize examples of same). You may also want to check if there is a trigger on the table, where the trigger is the culprit rather than the table itself
April 10, 2010 at 11:58 pm
tony.turner (4/9/2010)
On the other hand a large heap (no clustered index) would INSERT very slowly (I have come across some prize examples of same).
This is a bit new/strange for me . i always heard or experience that table without index always play good with insert. but can you explain your above statement.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 11, 2010 at 2:03 am
The base MS KB article is 297861. There are also blog posts by Tibor Karaszi http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx and Kimberly Tripp http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx
In summary, it is my understanding that it is not necessarily straight forward, but the performance penalty is associated with searching for free space in which to place the insertion point. In a multi-user environment on a large table there appears to be considerable contention in that search
In practice, it is in some circles common to use logging tables written from a trigger, where the logging table is a heap (no indexes at all, let alone clustered indexes). When they get really large (because there is no maintenance function) they perform increasingly poorly until either truncated or re-created with a clustered index
April 11, 2010 at 10:53 am
Bhuvnesh (4/10/2010)
tony.turner (4/9/2010)
On the other hand a large heap (no clustered index) would INSERT very slowly (I have come across some prize examples of same).This is a bit new/strange for me . i always heard or experience that table without index always play good with insert. but can you explain your above statement.
I think one thing you will learn very quickly is that there are few guarantees of this nature. To say that SQL always does something or that it never does something is a bit too overstated. The answer is usually "It Depends."
Performance can be better under certain conditions when using an insert without any indexes. One must test and evaluate for themselves.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 11, 2010 at 11:48 am
tony.turner (4/9/2010)
On the other hand a large heap (no clustered index) would INSERT very slowly (I have come across some prize examples of same). You may also want to check if there is a trigger on the table, where the trigger is the culprit rather than the table itself
A heap (with no indexes at all) generally exhibits somewhat slower INSERT performance than a table with an optimal clustered index. UPDATEs tend to be faster on heaps, though.
A more realistic comparison, where the heap uses the non-clustered index equivalent to the clustered index, can be found in the following Microsoft Best Practices Article:
http://msdn.microsoft.com/en-us/library/cc917672.aspx
Reference for my statement about INSERTs generally being slower on heaps, and UPDATEs being faster:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 11:49 am
To be clear about my own personal position on this: most tables benefit from having a clustered index, though there are cases where a heap may be 'better'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 3:07 am
Performance can be better under certain conditions when using an insert without any indexes. One must test and evaluate for themselves.
Don't want to provoke any religious wars. However, going back to the original question, Kena has a table to which he is adding maybe 1 billion rows per month, and he has poor performance (which is why he raised the question in the first place). Looking at the clustered index as one of the options seems fair to me
April 12, 2010 at 3:17 am
tony.turner (4/12/2010)
Don't want to provoke any religious wars. However, going back to the original question, Kena has a table to which he is adding maybe 1 billion rows per month, and he has poor performance (which is why he raised the question in the first place). Looking at the clustered index as one of the options seems fair to me
Oh I agree - I was just responding to the general point about heaps and stuff.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply