CODEWITHSUNDEEP
c#azure.net-coreazure-sql-databasesqlbulkcopy
user610217
user610217

Reputation:

SqlBulkCopy to Azure Timing out when Timeouts are disabled

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.

Upvotes: 2

Views: 590

Answers (3)

user610217
user610217

Reputation:

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.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

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");
    }


}

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15648

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

Upvotes: 1

Related Questions