High reads when Inserting single value

Question

High reads when Inserting single value

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

freeman.e.l SSC Veteran Points: 249 More actions · Answer 1

Ok so we are back to indexes being the likely cause issue. IMO it sounds like a page split. Make sure the fillfactor and pad_index are not set too high. You would likely need to rebuild the indexes.

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 2

freeman.e.l (12/23/2009)
this is probably caused by a large number of indexes.

Absolutely spot on, Freeman.

--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)

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 3

TheSQLGuru (12/24/2009)
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.

Heh... I agree... That would also do it. The other "not uncommon mistake" is that people just keep adding indexes until they hit the "sweet spot" (actually, the "sweat" spot because that's when they stop sweating the load :-P) and they often don't go back and clean up the indexing mess they've made.

Of course, the suggestion of checking for triggers was also a good one. It's normally the first thing I check for when I get a lot of reads on INSERTs... in fact, here's the order of things that I check and I don't stop just because I found one to be true...

1. Triggers

2. Over-indexed

3. Foreign keys

4. Indexed views and the like

The only reason why I don't check for FK's first is that a lot of the folks I know just don't understand the value of FK's and so they don't use them. Their normal excuse is, and I quote, "They get in the way during development or when we want to insert/delete/modify data". Yes... they all love pork chops, too. 😀

--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 SSC Guru Points: 134017 More actions · Answer 4

Yeah - I think I can probably count on one hand the number of clients I have had in over 10 years of consulting that used Foreign Keys! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service