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

Jul 27, 2020
26,002
17,946
146
1644410049285.png
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.
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
Newbie question: how do I get the complete query for session ID 144?

err you need access to the actual query. you said it is an insert command, which most case would be a select into, where is the rest of the query? you also need to understand the tables involved, to see if it is an issue with constraints.


thing is I am not sure you are the right person to muck with this :p
 
Last edited:
Jul 27, 2020
26,002
17,946
146
thing is I am not sure you are the right person to muck with this :p
You are right. However, this query (I think it's a stored procedure) was running great with 4 vCores. Then I tried it with 24 vCores and it started getting suspended and hasn't worked even after going back to 4 vCores. I think this is some crap specific to Managed Instance because nothing like this happened when SQL Server was installed on a Comet Lake 8-core VM. This SQL MI is General Purpose Broadwell.
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
Do you know if that stored proc has the with recompile option set? Any time you mess with vm config like vCores you should recompile.

Typically this option is set on all stored proc and they will automatically recompile on reboot. But who knows. You should examine the execution plan to figure out the issue. Also check licences since you may not be entitled to more than 4 vCores.

 
Last edited:
Jul 27, 2020
26,002
17,946
146
Awesome! This could be the reason. I will try sp_recompile to see if it does the trick. Any command to force a recompile globally for all stored procedures?
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
Awesome! This could be the reason. I will try sp_recompile to see if it does the trick. Any command to force a recompile globally for all stored procedures?


Although you really should check with the DBA in charge of this instance before doing anything. It's really their job xd. If nothing else you need to know how much it will cost to run.
 
Last edited:
Jul 27, 2020
26,002
17,946
146
It's a Pay-As-You-Go instance so I think the license thing doesn't matter here? There is NO DBA. The application developer just knows T-SQL but has no idea about SQL Server administration. So I have to try to fix this.
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
EXEC sp_updatestats;

This probably fixed it last time I ran this command and seems the easier way to do it, although it takes around 30 minutes or so. Do you think the other solutions will be quicker?


couldn't tell ya how long it will take, too many variables. but that will trigger stored proc recompile if needed. you should try forcing recompile on that one stored proc to see if problem goes away.

sp_updatestats can get expensive xd
 
Jul 27, 2020
26,002
17,946
146
I will try the recompile when I get to work. In the meantime, two more questions:

1) I can't enable query store on the databases. I ran the relevant commands but when I use Database Engine Tuning Advisor with query store, it says query store not enabled.

2) Database was restored and it has a compatibility level of 100. To upgrade it to version 150, I have to use the Database Upgrade wizard that will run for specified time period analyzing the queries and then it will upgrade the database structure to benefit from the newer version's optimizations?
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
I will try the recompile when I get to work. In the meantime, two more questions:

1) I can't enable query store on the databases. I ran the relevant commands but when I use Database Engine Tuning Advisor with query store, it says query store not enabled.

2) Database was restored and it has a compatibility level of 100. To upgrade it to version 150, I have to use the Database Upgrade wizard that will run for specified time period analyzing the queries and then it will upgrade the database structure to benefit from the newer version's optimizations?


Query store

Db was restored from what?
 
Jul 27, 2020
26,002
17,946
146
Recompiling the procedure didn't work. sp_updatestats took less than 90 seconds and also didn't work. So that was likely not the issue (I was really hoping it would be!). Found a procedure called sp_whoisactive on Github. It shows that there is a constant PAGEIOLATCH wait when doing an INSERT on a particular table. Google told me that it could be due to a corrupted index so rebuilt all the indexes on the table. Didn't work! Finally, I've given the query to the application developer and asked him to investigate.
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
Recompiling the procedure didn't work. sp_updatestats took less than 90 seconds and also didn't work. So that was likely not the issue (I was really hoping it would be!). Found a procedure called sp_whoisactive on Github. It shows that there is a constant PAGEIOLATCH wait when doing an INSERT on a particular table. Google told me that it could be due to a corrupted index so rebuilt all the indexes on the table. Didn't work! Finally, I've given the query to the application developer and asked him to investigate.


Sounds like fun xd
 
Jul 27, 2020
26,002
17,946
146
It was fun. The annoying, time-consuming, frustrating kind. The dev told me a simple SELECT query was taking "huge" time (his words). I ran that query and then ran DB Tuning Advisor and applied its recommendations. Time reduced from 3 minutes to less than a second!

Also, the latest DB engine can't handle MAX DOP = 0. Or at least, SS2014 works fine with this setting. Setting it to 4 on Azure SQL MI fixed the report. Then DB Tuning Advisor helped to reduce the report's running time from 125 seconds to 55 seconds. So I think I can turn the ignition key and drive this truck a little bit now, yeah?

What I understand now is, every few weeks, run sp_updatestats and DB Tuning Advisor and apply recommendations and things should work fine, correct?
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
It was fun. The annoying, time-consuming, frustrating kind. The dev told me a simple SELECT query was taking "huge" time (his words). I ran that query and then ran DB Tuning Advisor and applied its recommendations. Time reduced from 3 minutes to less than a second!

Also, the latest DB engine can't handle MAX DOP = 0. Or at least, SS2014 works fine with this setting. Setting it to 4 on Azure SQL MI fixed the report. Then DB Tuning Advisor helped to reduce the report's running time from 125 seconds to 55 seconds. So I think I can turn the ignition key and drive this truck a little bit now, yeah?

What I understand now is, every few weeks, run sp_updatestats and DB Tuning Advisor and apply recommendations and things should work fine, correct?

Well it won't hurt. There are too many variables to give a definitive answer. But most dbs don't change that much, you tend to just add more data to it.

How familar is the dev with the db? Queries should not take minutes.
 
Jul 27, 2020
26,002
17,946
146
How familar is the dev with the db? Queries should not take minutes.
As I said, he only knows T-SQL and not much else. I sent an email to him telling him of my progress. I'm guessing it will be something new for him too. DB Tuning Advisor said there were some syntax errors while it was analyzing workloads but didn't give any details. I've asked the dev to look into that but don't really expect any miracles from him.
 

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
As I said, he only knows T-SQL and not much else. I sent an email to him telling him of my progress. I'm guessing it will be something new for him too. DB Tuning Advisor said there were some syntax errors while it was analyzing workloads but didn't give any details. I've asked the dev to look into that but don't really expect any miracles from him.


Knowing sql is not the same thing as understanding the data and how best to query.