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.
Question What's stalling this query in Azure SQL Server Managed Instance?
- Thread starter igor_kavinski
- Start date
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,339
- 17,544
- 126
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
thing is I am not sure you are the right person to muck with this
Last edited:
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.thing is I am not sure you are the right person to muck with this
sdifox
No Lifer
- Sep 30, 2005
- 99,339
- 17,544
- 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.
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.
Recompile a Stored Procedure - SQL Server
Learn details about how to recompile a stored procedure by using Transact-SQL.
docs.microsoft.com
Last edited:
sdifox
No Lifer
- Sep 30, 2005
- 99,339
- 17,544
- 126
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:
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?
sdifox
No Lifer
- Sep 30, 2005
- 99,339
- 17,544
- 126
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
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?
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,339
- 17,544
- 126
Query store
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
Db was restored from what?
sdifox
No Lifer
- Sep 30, 2005
- 99,339
- 17,544
- 126
No, as in restored from an earlier version of sql? Was the db originally running on an earlier version of sqlserver? How big is it?
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,339
- 17,544
- 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?
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,339
- 17,544
- 126
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.
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,339
- 17,544
- 126
Knowing sql is not the same thing as understanding the data and how best to query.
TRENDING THREADS
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 25K
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes Discussion Threads
- Started by Tigerick
- Replies: 21K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
-
-
Facebook
Twitter