3

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
  • Any exception you are getting? Can you add that? – vijayp Sep 10 '14 at 10:29
  • Timeout error here IMO is related to poor query running on the system. You should focus first finding out problematic queries and then tuning it by taking the help of execution plan – Shanky Sep 10 '14 at 13:30
4

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.
  • The Management Studio query is used to update tables and hence expected to locking table. The query from the web page is a simple select count..query. IS there a way out? – user47179 Sep 10 '14 at 10:25
  • 1
    Maybe you can count the rows via the index. SELECT rowcnt FROM sys.sysindexes WHERE id = OBJECT_ID(<tablename>) AND indid in (0,1) – Lothar Kraner Sep 10 '14 at 11:18
  • 1
    There are many ways out @user47179. You could chunk your updates. You could query without locking. You could create a 2nd read only instance of the DB. It depends on your requirements in regard to your data query. – Paul Sep 10 '14 at 13:36
2

It's a Remote Connection Query Timeout

You would need to plan something on saving the number of rows on your table for use or having efficient way to get the number of rows count from tables, You can try below query

SELECT 
  Total_Rows= SUM(st.row_count)
FROM 
  sys.dm_db_partition_stats st
WHERE
  object_name(object_id) = 'TableName' AND (index_id < 2);

or:

SELECT 
  rowcnt 
FROM 
  sys.sysindexes 
WHERE 
  id = OBJECT_ID('TableName') AND indid in (0,1);

Check this fastest-way-to-count-rows

Although the default SQL Server Configuration for Remote Connection Query Timeout is 600 seconds, for testing purpose you can increase that value and check the result.check msdn

  • First query is 1000000 times better than the second one. I would never recommend sysindexes for any purpose unless you know the user is on SQL Server 2000 and can't use anything better. – Aaron Bertrand Sep 10 '14 at 12:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.