I'm dealing with some performance issues on SQL Server 2012. The client application indicates that the SQL time to load a page is over 2.5 seconds with a total loading time of more than 3 seconds. For this page it executes more than a few 1000 queries. In the trace I can see that it does the following a lot.

IF @@TRANCOUNT > 0 COMMIT TRAN (see screenshot of default tace).

What is the impact of this constant trancount check on the database? Can this be the cause of the issue? The queries itself take a very short time (microseconds) and other indicators show that the SQL server is healthy so I'm looking at overhead caused by these type of things. I've eliminated the network too.

SQL default trace

  • The SQL time comes to about 800 per second based on the 2.5 seconds you mentioned if the 1000 queries needed to load a page include both the parameterized query itself and the @@TRANCOUNT check. That seems reasonable for a single thread since it's actually 2000 queries being run. – Dan Guzman May 23 '15 at 13:00
  • 1
    If 2.5 seconds is unacceptable, the application can execute queries in parallel to reduce response time. Better yet, execute a fewer queries that return more rows each. – Dan Guzman May 23 '15 at 13:07
  • The problem is I'm not able to change how the client application does its requests. – J. Maes May 26 '15 at 12:30
  • There is nothing that can be done on the database server side to improve the response time, noting both the query and IF @@TRANCOUNT execute in less than a millisecond. With 1ms network latency, it will take 2 seconds to execute 2000 queries. Without application changes, your only recourse is to upgrade to a 10Gbit network. – Dan Guzman May 26 '15 at 13:23
  • Another option is to run the application on the same machine as the database server. Not that I'm recommending that as it may eliminate the latency issue only to introduce a more serious resource issue on the database server. – Dan Guzman May 26 '15 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.