December 23, 2009 at 1:34 am
Hi I have a situation where i am receiving the high reads when i am using a simple insert statement.
for e.g insert into table 1 values(v1, v2, v3........, v10)
Your suggestions are appreciated.
Abhijit - http://abhijitmore.wordpress.com
December 23, 2009 at 3:19 am
This was removed by the editor as SPAM
December 23, 2009 at 3:24 am
My initial guess would be that you have a Trigger on the table.
December 23, 2009 at 4:51 am
Nope I dn't have any trigger's on the tabl. The table structure is simple.
1. Primary Key
2. 7-8 Foreign Keys
Abhijit - http://abhijitmore.wordpress.com
December 23, 2009 at 4:58 am
F-Keys might be the reason.
BTW how did you find out there were large number of reads?
"Keep Trying"
December 23, 2009 at 6:52 am
Foreign key maybe the reason. Please take a backup of the db, remove all the foreign keys and try inserting a value then you will know what would be the exact problem.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 23, 2009 at 7:43 am
I checked the profiler.
Abhijit - http://abhijitmore.wordpress.com
December 23, 2009 at 6:08 pm
this is probably caused by a large number of indexes.
December 24, 2009 at 1:11 am
any suggestions how can i reduce it.
Abhijit - http://abhijitmore.wordpress.com
December 24, 2009 at 1:21 am
This was removed by the editor as SPAM
December 24, 2009 at 1:27 am
you can find what indexes are on the table by using sp_helpindex 'tablename'
If indexes are the issue you can use profiler to monitor queries to the table and drop indexes that are not used. Is the insert performance bad enough that a change like this is even needed?
December 24, 2009 at 8:58 am
It is almost certainly the foreign keys.
run an insert from SSMS with set statistics on and show actual execution plan. My best guess is that one or more of the referenced fields are not indexed, thus requiring a table scan for every insert to validate the FK relationship. Not an uncommon mistake - I have seen it at a number of clients.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 24, 2009 at 9:35 am
That sounds like a good explanation and would likely have a much greater impact on the insert performance.
December 24, 2009 at 10:21 am
TheSQLGuru (12/24/2009)
My best guess is that one or more of the referenced fields are not indexed, thus requiring a table scan for every insert to validate the FK relationship.
The foreign key has to refer to the Primary Key or a Unique Constraint on the referenced table, so how can it not be indexed... or am I missing something?
Deletion of a parent is where the lack of indexes on a child table usually hurts.
December 24, 2009 at 10:35 am
Ian Scarlett (12/24/2009)
TheSQLGuru (12/24/2009)
My best guess is that one or more of the referenced fields are not indexed, thus requiring a table scan for every insert to validate the FK relationship.The foreign key has to refer to the Primary Key or a Unique Constraint on the referenced table, so how can it not be indexed... or am I missing something?
Deletion of a parent is where the lack of indexes on a child table usually hurts.
DOH! Right you are - I plead Holiday Brain! 🙂
Looks like it is over to trigger's for most likely cause then. I have seen over 60 indexes (most with MANY included fields - way to go DTA!) that did not cause a significant increase in the number of IOs. Sure did cause lots of locking/blocking issues though!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply