SQL Server – Why Can’t We Access When a Long Query is Running?

performancequery-performancesql serversql-server-2012

We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load.

This work fine normally, however when we run a long query from Management Studio, the web page fails to load. It seems database does not respond when the long query is running in Management Studio. Is this a normal behavior?

Web page gives the following error –

Timeout expired.  The timeout period elapsed prior to completion of the operation or
the server is not responding.
Description: An unhandled exception occurred during the execution of the current web 
request. Please review the stack trace for more information about the error and where 
it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout 
period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. 
Information regarding the origin and location of the exception can be identified using 
the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to 
completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean 
    breakConnection) +404
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412

Best Answer

It would be impossible to say for sure without examining your web application and the SSMS query. My guess, however, is that the long query is locking tables which the web page wants to read, so the page has to wait for the query to finish. The page's timeout occurs before this happens and the page fails to load.

The concepts you are dealing with are locking and concurrency.

There are several ways to get the number of rows in a table. Some are more accurate than others; some are faster. This post on Stack Overflow may give you some ideas to work with.

Response to comment: a select count(*).. query will read every page of the table. Since the SSMS statements are performing updates they will have exclusive locks on some pages and prevent the count(*) from proceeding. This is exactly what I guessed was happening.

Some options are:

  1. Read the post from Stack Overflow and decide if any of those options would be appropriate for you. There are ways of getting the row count without touching the data tables.
  2. Batch the updates so you only update, say, 1000 rows before you commit the transaction. The commit will release locks and allow the counts to proceed.
  3. Add WITH (NOLOCK) to the counting queries, described here.
  4. Explicitly declare an appropriate isolation level for the web page's queries.
  5. Change the default isolation level for the SQL Server instance. NOTE WELL - this will have MAJOR implications for ALL activity on the instance and should only be done after CAREFUL, DETAILED discussion with an EXPERIENCED SQL Server DBA.
Related Question