1

This one's a bit strange so bear with me:

I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection was not closed properly).

Pre incident, insert performance was roughly 100m rows/hour with 500k batches (about 20 seconds/batch).

Post incident, batch inserts with more than ~80k rows will never complete (left it running for more than a day) whereas an insert with 20k rows completes in ~95 seconds but that results in an insert performance of only around 18m rows/hour. The machine the SQL server is installed on does not experience any load. As far as I can tell the server is idling.

What trips me up about this is that dropping the database or even reinstalling the server instance does not resolve the issue. I simply can't reproduce the performance I was seeing pre incident. On top of that, I had observed this exact phenomenon with Azure SQL instances, except that dropping the database "solved" the issue there.

The way this issue presents itself indicates that the problem is not actually with the SQL server but rather on the insert side / the driver. However I've tested basically all possible ways to connect to a MS SQL instance from python with the same result.

The SQL Server 2017 instance is updated and running on a fairly powerful dedicated machine with NVMe drives located in the same datacenter as the client machine.

The inserts are executed using:

  • ubuntu 16.04
  • Python 2.7
  • pyodbc 4.0.23 (also tested pymssql+freetds and pypyodbc)
  • Microsoft ODBC driver 17.2.0.1
Improve this question
4
  • 1
    Check the Query Store and the per-query wait stats in SQL 2017. See: learn.microsoft.com/en-us/sql/relational-databases/performance/… And also read the Columnstore data loading guide: learn.microsoft.com/en-us/sql/relational-databases/indexes/… You are using a Columnstore table, right?
    – David Browne - Microsoft
    Oct 20, 2018 at 22:03
  • 1
    did you check for orphan spid or spid with open transaction being left out ? We ran into this issue wherein the only resort was to restart the service ... we eventually put in timeout in the connection string and resolved it.
    – Kin Shah
    Oct 21, 2018 at 1:03
  • @DavidBrowne-Microsoft looking at an insert that has stalled for over 20 hours now, query_store_wait_stats reports 130ms wait time, exec count 214356 and total duration 5,5 sec. There are no other operations running on this server instance. I'm not using a columnstore table as of now.
    – MoorzTech
    Oct 21, 2018 at 10:42
  • 1
    @Kin that was one of my first suspicions, but restarting the service doesn't help either. sp_who reports only one connection from the client server and "AWAITING COMMAND" on that connection. To me this all points at pyodbc just not doing anything. I'll pop over to the pyodbc git repo and post an issue there as well.
    – MoorzTech
    Oct 21, 2018 at 10:43

1 Answer 1

Reset to default
2

Okay, so it is a very specific issue with PyODBC afterall:

I create tables with IGNORE_DUP_KEY = ON as ensuring no duplicate insertions would be much more expensive in our application and previously inserted IDs will get updated shortly afterwards anyways. The incident described in the original question (in combination with an unrelated bug) left the client application in a somewhat erroneous state, trying to re-insert previously inserted IDs. But even this should not be an issue normally.

I'm not sure yet why but PyODBC seems to take offense to duplicate primary key insertions. It becomes completely unresponsive (ignoring configured timeouts and ctrl+c) and using 100% CPU. SQL Server logs show it repeatedly kills its connection to the server and reconnects. I'll post this issue over on their github.

Improve this answer
2
  • 1
    An insert with an ingored dup key will send a message to the client, so I would suspect a client bug in handling that message. Also consider MERGE as a workaround, as you can implement the same behavior as IGNORE DUP KEY. Although you first need to get the data to the server using a temp table or JSON document.
    – David Browne - Microsoft
    Oct 21, 2018 at 13:11
  • Thanks for the advice! Our implementation is prototypical at best right now and we'll likely switch to columnstore tables at some point, making workarounds unnecessary. Github issue is here btw: github.com/mkleehammer/pyodbc/issues/476
    – MoorzTech
    Oct 21, 2018 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.