- Jul 27, 2020
- 26,002
- 17,946
- 146
Newbie question: how do I get the complete query for session ID 144?
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![]()
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?
EXEC sp_updatestats;
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.
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?
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?
Backup file.
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.
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?
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.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.
Yeah. At some point we need to find an expert for that.Knowing sql is not the same thing as understanding the data and how best to query.