June 2, 2017 at 6:19 am
We're working to upgrade SQL Servers that are used by a bunch of web apps from SQL 2012 to SQL 2016. We have a monthly batch that runs from a web app, uses SQLBulkCopy to transfer a few million rows. It transfers in batches of around 750K rows, and the transfers are consistently a few seconds slower in 2016 than in 2012, to the point of sometimes hitting the 30 second timeout when running against 2016.
I've compared the SQL instance settings like Max DOP, max memory settings, etc.
The database that's being loaded is restored directly from the 2012 instance. I've tried keeping the same compatibility level, I've tried upping the compatibility level in 2016 to 130.
Both servers have 8 GB of RAM, the 2012 server has 2 cores, Intel Xeon 2.7 GHz. The 2016 server has 4 cores, Intel Xeon 2.9 GHz.
There is little to no load running on either SQL instance when testing the batch.
Any help or suggestions on other things to look into is much appreciated. If there's more info that I should provide I'm glad to do so!
Thanks!
Bill
June 2, 2017 at 7:14 am
I'm not aware of something inherent in 2016 that would make that run slower. You are going to be dealing with the new cardinality estimation engine (at lease when the compatibility level is changed to 130) however it sounds like you already tested for that.
I think you'll just have to go to fundamentals. Capture the wait statistics before and after the data load to see where things are slowing down. Capture query metrics and execution plans to see how the queries are behaving. That sort of thing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 2, 2017 at 9:42 am
The MSDN site has some guidelines for things to try.
https://msdn.microsoft.com/en-us/library/ms177445(v=sql.105).aspx
If it were me, the first thing I would do is lower the batch size. If the issue is with timeouts, then a smaller batch size should get you in under the threshold. The other thing you can do is increase your timeout.
As far as it being a lot slower than 2012, it's really hard to guess. As Grant said, capture your wait stats to see if the server is bottlenecking on anything. Also, you can turn on code profiling in Visual Studio to see if it is having any bottlenecks.
This is one place where storage is going to be a big factor, since all that data has to make it to disk. Is there any difference between how the storage is set up between the two servers? Or can you get disk waits?
Windows performance monitor is another place to turn to see if there's anything going on with the server. The PAL tool is a good place to start. https://github.com/clinthuffman/PAL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply