azureazure-sql-databaseazure-sql-serversql-server-profilerazure-data-studio

Why is Azure Data Studio Profiler not capturing stored procedures with large input data?

I am analysing the performance of stored procedures in an Azure SQL database, but when using the profiler in Azure Data Studio, I cannot see some of the stored procedures, specifically those that have a large dataset input.

After investigation in a test environment, if I spread the input dataset across multiple stored procedure executions, I am able to see the execution in the profiler like this:

enter image description here

Why is the Profiler hiding the stored procedure executions with large input datasets?

Is it possible to increase the character limit in the TextData for example?

Solution

  • Large TVPs are not captured by extended events to avoid impact on overall database performance. ADS Profiler uses an extended event ring buffer. KB4468102, which applies to Azure SQL Database as well, mentions a limit for TVPs but the threshold is not documented AFAIK.

    I found through experimentation the limit depends on not just the number for rows passed in the TVP but the schema of the table type as well.