Greetings. Our Organization has a VM in Azure running SQL 2019 (Not Azure SQL). We have been trying to pin down performance issues and one item I'd like to clarify is an entry in the Activity Monitor regarding SQL ATP for Queries which is suspended and has a wait state of XE_Live_Target_TVF with 0 memory usage.
I understand that this involves monitoring extended events and is usually benign. It seems to be part of the Azure Advanced Threat Protection. I see lots of info regarding this feature with Azure SQL but am not sure how to disable it with a hosted VM. I'd like to stop the Azure ATP and stop this application and see if our performance gets any better. Any suggestions would be appreciated.
Not much data to go on, but that looks like a pretty benign thing. It's had 3 seconds of waits over however long it's been connected (can't see that in the image). Assuming it's been connected as long as your server has been online (which is 100% likely), that's absolutely not a problem (unless your server has been online for only 3 seconds, in which case, woof, it's a huge problem, but I'll bet that's not the case).
This should allow you to disable the recurring scans, but I wouldn't. The problem is likely elsewhere. Are you using either Query Store or Extended Events (or a 3rd party tool) to look at query performance over time? If not, that's where I would focus my efforts first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2022 at 5:33 pm
Thanks for getting back to me Grant. We are not using Query Store but there are some extended events, including the built in Health and Telemetry Events. Are there any considerations re stopping the ATP and Telemetry sessions. I know the Telemetry session involves sending traffic to Microsoft and we would lose the monitoring benefits the Advanced Threat Protection provides. We are using an Azure VM with SQL but not an Azure SQL Database. Thanks for your assistance.
September 13, 2022 at 5:48 pm
Nah, you can turn off whatever you want. You'll lose some pretty sophisticated SQL Injection protection and a few other things.
I was referencing Query Store and ExEvents as a way to monitor query behaviors. That's how I'd go about identifying the route cause of the performance issues. I've no doubt that there is some load there caused by Telemetry & ATP, but I'll bet it's an extremely minor part of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2022 at 5:55 pm
I stopped the SQL Advanced threat protection traffic extended event session and killed the SQL ATP for Queries process in the Activity Monitor but it immediately started up and was in a suspended state again. I'm not sure what I need to shut down to stop this process from appearing.
September 13, 2022 at 5:59 pm
The suspended state just means it's a connection waiting for a query to run. It's not actively using resources (well, a tiny amount of memory maintaining the connection). Did you follow the link and shut it from the Portal?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2022 at 6:53 pm
l looked through the Portal and the link but it seemed more designed for Azure SQL whereas we have an Azure VM with SQL on it. The screenshots and menu options didn't exist. I looked at the properties of the VM and the Microsoft Defender for Cloud Page and didn't see anything referencing the Advanced Threat Protection. I also looked through the Windows Services and the installed Apps and didn't see anything that I could identify as related to the Advanced Threat Protection
This task is suspended but the wait time is constantly changing. I suspect this is not related to our performance but shutting it down was a vendor recommendation.
September 14, 2022 at 4:04 am
This was removed by the editor as SPAM
November 20, 2022 at 5:21 am
Hi Paul, I am also facing the same issue.
Did you solved it ? or it is still in figuring out the soltuion ??
If you know anything to solve it, please suggest me.
November 23, 2022 at 7:56 pm
The problem seems to have gone away on its own as of last week. No indication why as we didn't make any changes in SQL or Azure
April 12, 2023 at 6:43 am
Hi,
we have a similar constellation, Azure VM with SQL2019. There is also the problem with the Process SQL ATP for Queries which is suspended and has a wait state of XE_Live_Target_TVF. We had it last year and then it just went away. Are there any new insights into this?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply