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

Toggle sidebar Toggle sidebar
I

igor_kavinski

Lifer
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.
 
Sort by date Sort by votes
sdifox

sdifox

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

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.

Retrieve Actively Running T-SQL Statements from SQL Server

Learn about the SQL Server 2016 replacement for DBCC INPUTBUFFER with the sys.dm_exec_input_buffer DMF that enables you to see the input buffers for all spids.
www.mssqltips.com www.mssqltips.com

thing is I am not sure you are the right person to muck with this :p
 
Last edited:
Upvote 0 Downvote
I

igor_kavinski

Lifer
Jul 27, 2020
26,002
17,946
146
sdifox said:
thing is I am not sure you are the right person to muck with this :p
Click to expand...
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.
 
Upvote 0 Downvote
sdifox

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.

docs.microsoft.com

Recompile a Stored Procedure - SQL Server

Learn details about how to recompile a stored procedure by using Transact-SQL.
docs.microsoft.com
 
Last edited:
Upvote 0 Downvote
I

igor_kavinski

Lifer
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?
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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?
Click to expand...


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:
Upvote 0 Downvote
I

igor_kavinski

Lifer
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.
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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?
Click to expand...


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
 
Upvote 0 Downvote
I

igor_kavinski

Lifer
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?
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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?
Click to expand...


Query store
azure.microsoft.com

Query Store: A flight data recorder for your database | Microsoft Azure Blog

This post will help you get started using Query Store for query performance analysis.
azure.microsoft.com azure.microsoft.com

Db was restored from what?
 
Upvote 0 Downvote
I

igor_kavinski

Lifer
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.
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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.
Click to expand...


Sounds like fun xd
 
Upvote 0 Downvote
I

igor_kavinski

Lifer
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?
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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?
Click to expand...

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.
 
Upvote 0 Downvote
I

igor_kavinski

Lifer
Jul 27, 2020
26,002
17,946
146
sdifox said:
How familar is the dev with the db? Queries should not take minutes.
Click to expand...
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.
 
Upvote 0 Downvote
sdifox

sdifox

No Lifer
Sep 30, 2005
99,345
17,545
126
igor_kavinski said:
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.
Click to expand...


Knowing sql is not the same thing as understanding the data and how best to query.
 
Upvote 0 Downvote
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom