Question What's stalling this query in Azure SQL Server Managed Instance?

[igor_kavinski]

The query never finishes. The moment it is executed, it gets put into suspended mode. What is going on?

I don't have much idea about this stuff. The query is listed as "Create proc etc." and it's an INSERT command.

 

[igor_kavinski]

• Change the DB Compatibility to 150.
• Reindexing done for all the databases.
• updatestatistics run for all the databases.
• Created Index for Expensive query’s.
• Removed freespace from database.

That's what the development team did before I had to jump in to try to improve the reports performance. When I changed the compatibility level back to 100, I immediately recorded an improvement, meaning the queries are not optimized for the newer version. Told them that they have to follow the DB Upgrade Wizard's recommendations to tune their queries.

Also, their stored procedures are not amenable to natively compiled execution plans so they have to change those too. I'm also recommending a mirror DB specifically for the reports as too many indexes get created which then need to be updated during transaction processing, bogging down the transaction throughput. Am I on the right track?

 

[sdifox]

• Change the DB Compatibility to 150.
• Reindexing done for all the databases.
• updatestatistics run for all the databases.
• Created Index for Expensive query’s.
• Removed freespace from database.

That's what the development team did before I had to jump in to try to improve the reports performance. When I changed the compatibility level back to 100, I immediately recorded an improvement, meaning the queries are not optimized for the newer version. Told them that they have to follow the DB Upgrade Wizard's recommendations to tune their queries.

Also, their stored procedures are not amenable to natively compiled execution plans so they have to change those too. I'm also recommending a mirror DB specifically for the reports as too many indexes get created which then need to be updated during transaction processing, bogging down the transaction throughput. Am I on the right track?

Sounds like your devs don't know what they are doing with the queries. You are really hurting for a DBA. I have never heard of db being lower performing when you update the compatibility level.

Generally speaking if your reports take a while to run you benefit from creating views to serve the reports.

You could schedule stat update weekly if your data changes that much in a week.

 

[igor_kavinski]

Generally speaking if your reports take a while to run you benefit from creating views to serve the reports.

Thanks! Something more to research

 

[sdifox]

Thanks! Something more to research

Staring at execution plans does give you ideas on what is eating cycles.

 

[igor_kavinski]

Generally speaking if your reports take a while to run you benefit from creating views to serve the reports.

Found something very interesting. We have two different applications residing on the same DB server. One of them (Let's call it Tex) has views and DB Tuning Advisor was able to speed it up further with indexing recommendations.

The DB for the other application (Mex) has no views and DBTA has not been able to offer any recommendations for it after analyzing the workload. So going to ask the developer why that is so since past data for Mex never gets changed. It seems to be the perfect candidate for using views.

Again, thank you so much! Without your help, I would have had to study databases from the ground up!

By the way, we moved away from Azure SQL Managed Instance to a VM with SQL Server. The MI was Broadwell and the VM is Cascade Lake. Here's the performance difference:



Cascade Lake is beating Broadwell by more than 7X!

Broadwell is 4-core (changing to 8-core didn't provide any improvement) while Cascade Lake VM is 8-core.