Performance issue setting NOCOUNT to on in a stored procedure - Synapse SQL pool

Question

Hi,

I'm monitoring the execution of a stored procedure implemented in a Synapse dedicated SQL pool with DW100c service level.

I've observed a run time of 15-30 milliseconds for the

SET NOCOUNT ON;

statement.
For me, the registered time is high, really high!
Any suggests to me, please?

Answer 1

@pmscorca

Thanks for reaching out to Microsoft Q&A.

I understand your concern. The SET NOCOUNT ON; statement is a very lightweight operation and it’s unusual for it to take 15-30 milliseconds to execute. This statement simply turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed, which can reduce network traffic.

Here are a few things you might consider:

Network latency: If your application is not in the same data center as your database, network latency could be a factor. You might want to check the network latency between your application and the database.

Server load: If the server is under heavy load, it might take longer to process even simple statements. You could check the server’s resource usage to see if this is the case.

Blocking: If other processes are accessing the same resources, they could be blocking your statement. You could use SQL Server’s built-in tools to check for blocking.

Query execution plan: Although this is a simple statement, SQL Server still generates an execution plan for it. You could examine the execution plan to see if there are any unexpected operations.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

SET NOCOUNT ON simply surpress InfoMessages back to client, it has absolutely no effect on query ececution/query runtime.