I have been running some bcp out statements, something like this:
I have noticed that for some days I am getting slower output, then for another days.
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.
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
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 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