How to provide statement id in the serverless SQL pool support ticket?

Published Jul 05 2022 08:00 AM 1,329 Views
Microsoft

Serverless SQL pool is a query service that enables you to analyze data in Azure Data Lake storage, Cosmos DB, or Data verse without a need to manage the resources, tune your queries, or do any other administration task to enable your data analyst to query data.

 

There is a set of best practices that you should apply in your solution that should ensure optimal performance. By following the best practices, you will ensure that in most cases your queries will be executed without any issue.

 

However, in some cases you might experience some errors of slower queries even if you have properly configured everything. In this case, you can file a support ticket, and synapse support team will analyze your query and handle the issues.

 

It is especially important to include all the information that will help the support team to resolve the issues, for example:

  • Workspace name
  • Region
  • Statement ID
  • Error text, query text, Approximate time of the query execution/error, results (if applicable and not contain confidential information)

The {Statement ID} is very important information that you should include whenever you report an issue. {Statement ID} enables the support team to find most of the information about your query (execution time, duration, error message, etc.), so in most cases it is more valuable than any other information.

 

To avoid delays in the resolution of your support case and going back and forward while trying to get additional information, make sure that you include this statement id in your support ticket.

 

In the following sections, I will explain how to find this information in serverless SQL pool.

 

Where is my {Statement ID}?

A statement id is information that is returned to the client application whenever you execute a query. Let’s look at a query that is executed using Synapse Studio that returns the following results:

 

JovanPop_0-1655813109578.png

 

Note that the results are displayed in the “Results” tab, but there is another tab called “Messages”. Instead of looking at the results, you should switch to the “Messages” tab by clicking on the “Messages” label:

 

JovanPop_1-1655813109593.png

 

In the “Messages” tab, you will be able to see the GUID representing the ID of the statement.

 

If you are using other tools for querying, you can use a similar approach to find the {Statement ID} in the messages. The following figure shows how to find this information when you execute a query in SQL Server Management Studio:

 

JovanPop_2-1655813109608.png

 

If you can execute a query manually via tool, always find the {Statement Id} and send it as a part of support request.

 

The {Statement ID} is sent as an info message using the standard TDS protocol. Therefore, you can get this information even if you are writing code that is accessing serverless pool. You can use SqlConnection.InfoMessage to get this info in your .Net code. Other libraries and language have similar API for fetching messages.

 

It might be more difficult to find the {Statement ID} if you cannot directly execute the query. For example, Power BI or Analysis services might run the queries in the background to refresh the reports, and you might not be able to see the messages.

 

In that case you should try to find the query and its statement id in the history on serverless pool.

 

First, make sure that you set up the QPI T-SQL library in your serverless database. This library is a set of useful T-SQL scripts that enable you to find some information in the databases on the serverless SQL pool. Once you execute this script, you will be able to query qpi.query_history view, that returns the recent query executions, their statement ids, duration, data processed etc.

The results that this view might return are shown in the following picture:

 

JovanPop_3-1655813109646.png

 

The value in the request_id column is the value that you should report in a support ticket. This is the easiest way to find the {Statement Id} for the queries that you did not execute directly. Note that at the time of writing this post, the statement id is not shown in the Synapse SQL requests report.

 

Conclusion

One of the most important pieces of information that you should provide to Synapse support team that is working on the support ticket is the {Statement ID}. This information will enable the support team to quickly find all information about your query and speed up the support case resolution.

 

Make sure that you use this post to find the statement id and include it in the support ticket that you are reporting to ensure that your support case will be more efficiently resolved.

Co-Authors
Version history
Last update:
‎Jul 01 2022 09:44 AM
Updated by: