Friday, April 4, 2014

BCP is also influenced by health of indexes and valid statistics

I have been running some bcp out statements, something like this:

bcp "select * from DatabaseName..TableName where Timestamp>='2013-12-23 00:00:00' and Timestamp<'2013-12-24 00:00:00'" queryout
C:\temp\TableName_2013-12-23.dat -S SQLServerMachineName\DatabaseInstanceName -T -n

I have noticed that for some days I am getting slower output, then for another days.

Since the TableName is partitioned by Timestamp, I did the index rebuild on partitions where "slow" dates exists. But it did not solve my problem. BCP was still slow.


Then I asked myself - why BCP is still slow? 

The answer came to my mind when I noticed that bcp is using SQL statement. Then I knew that the problem must be in that statement. Then I took that SQL statement, generate a query execution plan for a day when bcp is ok, and for a day when bcp is slow, and I noticed that they have different execution plans. 

The reason why they have the different execution plans is that statistics for indexes on TableName were wrong for the dates which have slow bcp. I thought that partitioned index rebuild will automatically update statistics, but it did not update it. So, I run update statistics manually, for all the indexes on that table.

After that, bcp was fast for all the days.

No comments:

Post a Comment