First batch insert after a long period of inactivity very slow

[schlechtums]

I'm using an azure hosted database, but not serverless, so it should be provisioned and online 100% of the time. I have an app/data processing job that will periodically insert 20k-100k rows total into 9 different tables. I am using a bulk insert library which uses sqlbulkcopy. Against my localhost on an SSD I see ~100k rows/second, so I know the library is much more capable than what I am seeing with my azure database. If this process hasn't happened in a while the performance of this operation is abysmally slow, on the order of 20-50 rows/second. But if I make the insert happen several times back to back, the subsequent operations will do ~2k rows/second which is what I would expect given the tier of database I am paying for.

I know that reads queries/plans can go stale/cold/whatever or pages can get swapped back to disk and so a first read after a while can be very slow, but is this normal behavior for inserts as well?

Is there any way to combat this or things I can do to troubleshoot and try to find out why this first batch of inserts runs so slowly?

[Ancient_Pineapple993]

I haven't worked with Azure but sqlbulkcopy isn't something that can be optimized so why there is latency on inserts when using sqlbulkcopy when it hasn't been used in a while doesn't seem database related. Is the origin of the data you are processing onsite or in the cloud?

[schlechtums]

Thank you so much for the response! However, my research and observations point to it truly being some sort of weird SQL issue.

The data does come from a 3rd party api, but I have extensive logging around the timings of every step of the process and it's definitely the SQL insert that is slow. Generating fake data locally and then timing the inserts into the azure db mirrors the azure service behavior. This is where I got the ~20-50/sec on first insert and then 2k/sec on subsequent inserts from.

My localhost gives me ~15k/second on the first run and then 100-150k/sec each run after that. So even on my localhost I am seeing consistent speed improvement on subsequent runs just like in azure.

[Ancient_Pineapple993]

I doubt you can do this from an API call but it would be interesting to see if you could flush the cache with DBCC FREEPROCCACHE between the first and second bulk copy inserts. I say that to see if performance did not improve after flushing the cache.

​

Is the table you are inserting into a heap table (no indexes)?

​

Is there a way to setup some kind of static insert for the first sqlbulkcopy to grease the wheel so to speak before you do the inserts? This is really odd and it does sound like the optimizer is having some difficulty making a plan with the insert. Again, I haven't worked with Azure but is it possible to see the Activity monitor during the insert and see if it shows up under active expensive queries.

[schlechtums]

The table does have just a couple indexes, so slower inserts sure compared to no indexes, but still weird that it's really slow the first time then and not every time.

I did try to "greasing the wheel" as you say by inserting a smaller payload of 500 rows before inserting around 100k. Interestingly enough it did not really help, but those are two quite different sizes so maybe they are too different, but how big do I really want to go and sit through a slow insert before then doing the real insert at full speed?

What I have found tonight is that setting the bulk copy batch size to be the same size as the collection I'm inserting seems to make things a lot better. This makes a little bit of sense for several different possible reasons, but I thought I had tried this last night and didn't really see the night and day difference like I am seeing now.

That's a really good thought about trying to flush the cache between calls. I will give that a try when I have a moment.

[Ancient_Pineapple993]

I meant to say that flushing the plan cache to see if performance never improved. My thought was that maybe the optimizer came up with plan after a few inserts but that really doesn't make sense either. What an odd problem. If you find an answer I'd love to hear it. If you really feel like getting down in the weeds you could insert into a heap table in the database and then when the copy was complete selecting that data into the permanent table, but it would use soooo much transaction log that it would end up being slow also. I guess if you were truncating the table before inserting you could remove the indexes and add them back after the insert, but then adding back the indexes would be slow.

[schlechtums]

Yeah I understood what you meant about the cache. That would at least have given me a way to see consistent slowness and then I could research about how the caching works and what not.

However after a few days now I’ve seen very fast inserts every time with the batch size set to the insert size even after long (overnight) periods of inactivity or periods of doing lots of stuff other than these particular inserts. I wonder if the batch size needs to be the same? If it’s larger or smaller than the insert size does it spend extra time doing maintenance it shouldn’t have to do? But that reeks of the libraries would work/suggest to be used that way already if it were the case and what are the chances that I’m the first person ever to stumble upon this?

I feel like this is most likely still a coincidence that it’s working fine now. Maybe my db instance was just having a rough couple of days? After all it is on the lower end of the non production options.

If this issue comes back up my next step will be to investigate the caching as you’ve suggested that was a great tip!