We are migrating our application from DB2/LUW v11, to SQL Server 2019.
We have built our SQL Server database with the same tables, indexes, views, etc. as our DB2 database and have updated the SQL in our programs to use SQL Server syntax. We have loaded the SS database with a “production” size amount of data and are now doing some initial performance testing. We are finding that SQL Server will not perform as is. The access paths it is creating are horrible. I am trying to find out if anyone has any experience with this and can recommend any database scope configuration settings or any other configurations, to force SQL Server to better mimic DB2 as far as the access paths it uses. I’m sure we’ll have some “one off’s” to deal with, but at this point, I would estimate 80% of the SQL performs very poorly.
We have run index reorgs and updated statistics on our SQL database. That gave no noticeable improvement. Our SQL Server Database team can only send us tons of index recommendations from QueryStore. My thought is that we basically have almost all the indexes needed for the database to perform, with (I’m sure), some room for improvement. But before we go down that path, I am expecting/hoping to find some upfront tuning that can be done.
My background has always been in DB2.. DB2/ZOS mostly, with some later DB2/LUW. I’m basically a nube when it comes to SQL Server.
Tony Moore is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1