SQL Server 2008 BULK INSERT causes more reads than writes. Why?
Posted
by
sh1ng
on Stack Overflow
See other posts from Stack Overflow
or by sh1ng
Published on 2011-11-27T07:14:21Z
Indexed on
2011/11/27
9:50 UTC
Read the original article
Hit count: 280
I've huge a table (a few billion rows) with a clustered index and two non-clustered indices.
A BULK INSERT operation produces 112000 reads and only 383 writes (duration 19948ms).
It's very confusing to me. Why do reads exceed writes? How can I reduce it?
update query
insert bulk DenormalizedPrice4 ([DP_ID] BigInt, [DP_CountryID] Int, [DP_OperatorID] SmallInt, [DP_OperatorPriceID] BigInt, [DP_SpoID] Int, [DP_TourTypeID] Int, [DP_CheckinDate] Date, [DP_CurrencyID] SmallInt, [DP_Cost] Decimal(9,2), [DP_FirstCityID] Int, [DP_FirstHotelID] Int, [DP_FirstBuildingID] Int, [DP_FirstHotelGlobalStarID] Int, [DP_FirstHotelGlobalMealID] Int, [DP_FirstHotelAccommodationTypeID] Int, [DP_FirstHotelRoomCategoryID] Int, [DP_FirstHotelRoomTypeID] Int, [DP_Days] TinyInt, [DP_Nights] TinyInt, [DP_ChildrenCount] TinyInt, [DP_AdultsCount] TinyInt, [DP_TariffID] Int, [DP_DepartureCityID] Int, [DP_DateCreated] SmallDateTime, [DP_DateDenormalized] SmallDateTime, [DP_IsHide] Bit, [DP_FirstHotelAccommodationID] Int) with (CHECK_CONSTRAINTS)
No triggers & foreign keys Cluster Index by DP_ID and two non-unique indexes(with fillfactor=90%)
And one more thing DB stored on RAID50 with stripe size 256K
© Stack Overflow or respective owner