How to improve insert performance
-
Dear all,
We are facing performance problem in inserting into table.
Detail
Table Size: 2 GB
Index: cluster index on identity column, three non cluster index
index size: 3 GB
cluster index insert
row count:38968909
the insert statement is part of the procedure and the proc is executed by multiple users simultaneously. And CPU utilization is reaching 100% and SOS_Scheduler_yield wait type is occurring.
Particularly the insert statement in the procedure is costing 203%
Please provide your valuable suggestion and it would be greate if any body explain how insert will work againts the table which is having no of cluster and noncluster index
Thanks
kokila k
-
When is the last time you did any maintenance on the indexes of the table?
--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) -
1. Make sure your stored procedure is not recompiling every time it is run. (RECOMPILE = ON).
2. Check the index fragmentation using sys.dm_db_index_physical_stats.
3. Check out this post on that wait type http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790196&SiteID=1.
4. I assume you know what the stored proc is doing, but just in case run a Profiler trace to get an idea of the processes being executed.
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size] -
Table Size: 2 GB
Index: cluster index on identity column, three non cluster index
index size: 3 GB
cluster index insert
row count:38968909
Soooo, you have just 2GB of data yet 3 NC indexes total up to 3GB? Did you perchance use DTA and now you have 3 indexes which each have 50-75% of the columns in the table included in them??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
In addition to the other posts you will always have a "hotspot" on the last data page of a clustered index on and identity column - this is because the data pages and last clustered index pages of a table with a clustered index are the same. Because the ID's increment the next row inserted will always end up being inserted to the last data page of the index - so there is a lot of contention for that data page.
It may be better to have a different index as the clustered index - obviously this might hurt performance elsewhere so it would require testing and a lot more info re data - but e.g. if you could cluster on say a user identifier column in the table then the contention for the clustered index page would only be against the same user's inserts.
Also remember that though indexes are good for lookup they will always hurt insert performance - so carefully examine the other indexes on the table to see if they are optimal
James Horsley
Workflow Consulting Limited -
Check for an insert trigger.
I've seen ridiculously inefficient triggers resulting in high CPU and IO.
-
Richard Fryar (9/2/2008)
Check for an insert trigger.I've seen ridiculously inefficient triggers resulting in high CPU and IO.
That is indeed a good one, Richard, and one that is often simply overlooked!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
James Horsley (9/2/2008)
It may be better to have a different index as the clustered index - obviously this might hurt performance elsewhere so it would require testing and a lot more info re data - but e.g. if you could cluster on say a user identifier column in the table then the contention for the clustered index page would only be against the same user's inserts.If you decide to do that, pick a nice low FILL FACTOR or the page splits will be even more painfull than the hot spot.
--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) -
If you decide to do that, pick a nice low FILL FACTOR or the page splits will be even more painfull than the hot spot.
Indeed.
James Horsley
Workflow Consulting Limited -
You could also check the growth rate that's set for your data and log files. If it's too small (e.g. in 10 MB increments), the insert will take longer than necessay due to the need to constantly grow the files.
Also, here's another view on using identity columns for the clustered index.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Ray Mond (9/3/2008)
You could also check the growth rate that's set for your data and log files. If it's too small (e.g. in 10 MB increments), the insert will take longer than necessay due to the need to constantly grow the files.Also, here's another view on using identity columns for the clustered index.
10MB growth could also be the source for some pretty massive physical file fragementation on the hard-disk, as well.
Growth should never happen automatically... it should always be a planned event... say, once per month.
--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) -
TheSQLGuru (9/2/2008)
Richard Fryar (9/2/2008)
Check for an insert trigger.I've seen ridiculously inefficient triggers resulting in high CPU and IO.
That is indeed a good one, Richard, and one that is often simply overlooked!
I'm going through that sh*t right now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply