How to Avoid SQL Query Timeout

How to avoid SQL query timeout error when open transaction is there?

Few things..

1.First of all your update is not sargable,you can try to rewrite it as below

UPDATE CUSTOMER 
SET Status = 'L' 
WHERE CustId = @BorrowerCode AND Borrower = 'Y' 

2.Time out has pretty huge limit,25 times query cost.so even with this limit,if you are getting time out ,then there must be some thing wrong and we are trying to apply bandage with nolock.setting isolation level to snapshot ,will not result in blockings of select ,but it comes with a cost of tempDB usage,scan issues(see link below for more..).Further isolation level wont apply to DDL/DML statements ,they are just for select statements and in your case ,an open transaction may mean some DDL/DML running for so long.

In summary,i wont use nolock,but i would rather try to see why timeout happens and also changing isolation level requires some testing as well

References:
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

How to avoid SQL server time out issue while handling huge amount of data

As per comments, selecting at most 20 records should not take a huge amount of time.

Some things to verify

• does each foreign key have an index
• is there a covering index on SaveMode and companyname

Sidenote: It seems to me your SQL Statement could be simplified to the following. Only change in output should be that there are always 20 records selected whereas that might not have been the case in your original query.

SQL Statement

SELECT  TOP 20 DISTINCT COALESCE(Pp.CompanyName, p.CompanyName)
FROM    Periodicals P
        LEFT JOIN Periodicals Pp ON Pp.PID = P.ParentID
        LEFT JOIN Companies C ON P.CompanyID = C.CompanyID
        LEFT JOIN UserContacts UC ON P.CreatedUserID = UC.UserID
        LEFT JOIN Contacts CT ON UC.ContactID = CT.ContactID
        LEFT JOIN UserContacts URC ON P.CustomerID = URC.UserID
        LEFT JOIN Contacts RCT ON URC.ContactID = RCT.ContactID
        LEFT JOIN UserRepresentCompanies UCP ON UCP.UserID = URC.UserID
        LEFT JOIN Contacts CC ON CC.ContactID = UCP.CompanyContactID
WHERE   ( @CompanyID IS NULL OR @CompanyID = C.CompanyID )
        AND ( @CustomerID = 0 OR @CustomerID = URC.UserID )
        AND ( P.SaveMode IS NULL )

How to avoid Sql Query Timeout

Although there is clearly some kind of network instability or something interfering with your connection (15 minutes is possible that you could be crossing a NAT boundary or something in your network is dropping the session), I would think you want such a simple?) query to return well within any anticipated timeoue (like 1s).

I would talk to your DBA and get an index created on the underlying tables on MemberType, Status. If there isn't a single underlying table or these are more complex and created by the view or UDF, and you are running SQL Server 2005 or above, have him consider indexing the view (basically materializing the view in an indexed fashion).

How can I debug this sql query to avoid timeout?

The syntax is outdated, legacy code.
I think I found the reason. The original (legady code) query statement is - or uses? - CTE (common table expression) which doesn't work well with indexing. That's what I read somewhere. I don't know if that's really the reason, however, here is my version, which works:

SELECT
  some,
  some,
  some,
  some,
  some,
  some,
  some,
  some / some * some
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          a.somelot,
          b.someqty vq
        FROM
          (
            (
              SELECT
                view2.some_id somelot,
                view2.someother_id --added this
              FROM
                someview view2
              WHERE
                view2.some_prefix = 'ABCD'
                AND view2.some_prefix = 'EFGH'
            ) a
            JOIN (
              SELECT
                view1.lot,
                SUM(view1.qty) qty
              FROM
                someview view1
              GROUP BY
                lot,
                qty
            ) b ON a.someother_id = b.lot
          )
      ) s2
      JOIN (
        SELECT
          lot,
          SUM(trans_qty) pq
        FROM
          someview
        WHERE
          side = 'TO'
        GROUP BY
          lot,
          trans_qty
      ) s1 ON s1.lot = s2.lot
      JOIN (
        SELECT
          lot,
          some,
          some,
          trans_qty trans_qty,
          some
        FROM
          someview
        WHERE
          some = 'ZZZZ'
          AND some = 'XXX'
          AND some_prefix = 'WXYZ'
      ) t2 ON t2.lot = s1.lot
      AND t2.lot = s2.lot
  )

How to prevent port timeouts for large remote queries from SQL Server?

You are right to note that you need to change the remote query timeout setting to 0.

This link give pretty neat answer to your question. There are ways for doing it via both the SQL Server management Studio, and the command line. However, you need to first login with a user that has the required permissions.

How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31')

Turns out that the post (or rather the whole table) was locked by the very same connection that I tried to update the post with.

I had a opened record set of the post that was created by:

Set RecSet = Conn.Execute()

This type of recordset is supposed to be read-only and when I was using MS Access as database it did not lock anything. But apparently this type of record set did lock something on MS SQL Server 2012 because when I added these lines of code before executing the UPDATE SQL statement...

RecSet.Close
Set RecSet = Nothing

...everything worked just fine.

So bottom line is to be careful with opened record sets - even if they are read-only they could lock your table from updates.

How do I prevent a timeout error when executing a store procedure using a SqlCommand?

The timeout on the connection is for connecting to the database only.

There is a separate CommandTimeout property of the SqlCommand class, use this property to specify the execution timeout.

Ie.

using (SqlCommand cmd = new SqlCommand())
{
  cmd.Connection = connection1;
  cmd.CommandTimeout = 240; //in seconds
  //etc...
}

SQL Query TimeOut

A connection has a timeout, but so does the command running against the connection. That timeout is for how long to wait just trying to establish the connection. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

So assuming you're using a SqlCommand then set the CommandTimeout property of the command.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

---

Related Topics