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

Related posts about Performance

Related posts about sql-server-2008