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 12»»

How to improve insert performance Expand / Collapse
Author
Message
Posted Sunday, August 31, 2008 11:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 11:14 PM
Points: 19, Visits: 255
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



Post #561850
Posted Monday, September 1, 2008 7:27 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(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 #562148
Posted Monday, September 1, 2008 9:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 12:21 PM
Points: 58, Visits: 199
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.





_______________________________
[size="5"]Jody Claggett
SQL Server Reporting Analyst
[/size]
Post #562170
Posted Tuesday, September 2, 2008 1:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 4,410, Visits: 6,282
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 at GMail
Post #562233
Posted Tuesday, September 2, 2008 4:06 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 5:31 AM
Points: 159, Visits: 432
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
Post #562287
Posted Tuesday, September 2, 2008 7:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
Check for an insert trigger.

I've seen ridiculously inefficient triggers resulting in high CPU and IO.



Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #562388
Posted Tuesday, September 2, 2008 7:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 4,410, Visits: 6,282
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 at GMail
Post #562402
Posted Tuesday, September 2, 2008 7:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(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 #562788
Posted Wednesday, September 3, 2008 2:07 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 5:31 AM
Points: 159, Visits: 432
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
Post #562861
Posted Wednesday, September 3, 2008 9:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:57 AM
Points: 343, Visits: 1,520
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.


Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #563130
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse