2

I'm trying to bulk insert a couple of million rows to a dev instance Azure SQL database with SqlBulkCopy in .NET Core.

I have disabled the connection string timeout, and the BulkCopyTimeout (set them both to 0), but I'm still timing out.

Now this is not a high-tier machine (it's a development environment), and this process maxes out the DTUs pretty easily... but my understanding of how the DTU max thing is supposed to work is that it's a throttling mechanism, not an aborting mechanism. With unlimited timeouts, I would expect the process to take a while but eventually finish. Instead, what I am seeing is the process starting... uploading a bunch of rows... and then timing out, at odd times: 2:38, 4:20... no rhyme or reason.

This makes me think it's a transport error of some kind, but I am clearly getting a TimeoutException.

Per the suggestions in Bulk insert is not working properly in Azure SQL Server, I've tried to make the batches very small as well, but this doesn't seem to do anything either.

Can anyone explain what is going on here, and how to address it? This is blocking development on a high-vis project, and I hate to tell people I can get it to work on my laptop's SQL Server Express, but not on an Azure DB.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
  • The default timeout is 30 seconds. Don't make zero. Make larger than 30 seconds. – jdweng Feb 04 '19 at 13:35
  • I will, eventually... right now, I'm just trying to localize the problem. I'm scratching my head over timing out when it shouldn't be possible to time out. – Jeremy Holovacs Feb 04 '19 at 13:38
  • I'm not sure if setting to zero really makes timeout infinite. Why not try a very large timeout and see if it works. – jdweng Feb 04 '19 at 13:46
  • Per MS documentation, that's the way to make the timeout infinite. I did try setting the timeout to be an hour on both, but it still timed out after a few minutes. – Jeremy Holovacs Feb 04 '19 at 13:47
  • Post the full exception ToString. It will tell us what component is causing the timeout. – usr Feb 04 '19 at 13:55
  • Check the server log files to see if there are any error messages. I suspect the connection is breaking due to an exception. Also watch the Task Manager while running to see if you are running out of memory. – jdweng Feb 04 '19 at 15:19
  • 1
    #%$#^%# I suddenly can no longer reproduce this. This problem has existed since last Thursday and now it's just... working. No code change. Sigh. I wonder if something in our SAP instance was bad. – Jeremy Holovacs Feb 04 '19 at 15:58
  • 1
    It's a Heisenbug. I fixed it for you by looking! – usr Feb 04 '19 at 19:12

3 Answers3

1

Please run the following query and let’s try to find evidence about the Azure SQL Database being throttled.

SELECT *
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

If you see avg_log_write_percent is close or equal 100% then throttling is occurring, and you need to scale up the tier of the database. Non-premium tiers are not good for I/O intensive workloads, and batching is recommended.

When throttling occurs on an Azure SQL Database you will not only see slow response times but you also you start to see unsuccessful connections attempt and timeouts.

select * 
from sys.event_log 
where event_type <> 'connection_successful' and
start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
order by start_time desc

select *
from sys.database_connection_stats_ex
where start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
order by start_time desc
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
1

I just tried to repro and couldn't. The SqlBulkCopy ran for over 30min before I canceled it.

Try this from outside Azure against a low-DTU Azure SQL Database:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

class Program
{
    static void Main(string[] args)
    {
        var constr = "Server=tcp:xxxxxx.database.windows.net,1433;Initial Catalog=xxxxxx;User ID=xxxxxx;Password=xxxxxx";


        using (var con = new SqlConnection(constr))
        {
            con.Open();

            var cmd = con.CreateCommand();
            cmd.CommandText = "create table #test(id int, data varbinary(max))";
            cmd.ExecuteNonQuery();

            var bc = new SqlBulkCopy(con);
            bc.DestinationTableName = "#test";
            bc.BulkCopyTimeout = 0;

            var dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("data", typeof(byte[]));
            var buf = Enumerable.Range(1, 1000 * 1000).Select(i => (byte)(i % 256)).ToArray();
            dt.BeginLoadData();
            for (int i = 0; i < 1000*1000*10; i++)
            {
                var r = dt.NewRow();
                r[0] = 1;
                r[1] = buf;
                dt.Rows.Add(r);
            }
            dt.EndLoadData();

            foreach (DataColumn col in dt.Columns)
            {
                bc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            bc.NotifyAfter = 100;
            bc.SqlRowsCopied += (s, a) =>
            {
                Console.WriteLine($"{a.RowsCopied} rows copied");
            };


            Console.WriteLine($"Starting {DateTime.Now}");
            bc.WriteToServer(dt);
            Console.WriteLine($"Finished {DateTime.Now}");

        }
        Console.WriteLine("done");
    }


}
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

Both of the answers are good, but it seems like my problem was not code-related. There was some "glitch in the matrix" that seemed to be causing the symptoms I was seeing, and after a certain point I was no longer able to reproduce them.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254