I have an S3 SQL Server database in Azure, and I’m having an issue with a specific Entity Framework generated query and how often it is being run.
As the numbers below show, I’m getting 160K+ executions per hour, and this number is unexpected (for the time periods illustrated I would have expected less than 10K executions per hour).
The number of executions also doesn’t correlate with the number of requests to the API methods that issue this query (they were called about 50 times in the corresponding period).
So my question is: using Azure analytics is there any way I can trace the query to determine who/what made the call? Can I somehow get an IP address? This is clearly a code problem, is there any helpful way to determine where the calls are coming from?
Use SQL Auditing to track from where those statements are coming.
Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup" -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT" -StorageAccountName "storageAccount" -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]" -RetentionInDays 60
A sample audit record for the BATCH_COMPLETED_GROUP will show the following information:
As you can see it gives you the IP address, the principal name and the application name that generated the SQL statement.