Sql timeout Exception although max pool size is set to 1000000

Question

I'm working with an asp.net MVC4 project and using SQL Server 2008. My database table contains 100000 rows. Also max pool size of connection string property is set to 1000000 and pooling is set to true.

My table structure is as follows:

CREATE TABLE tblNews
(
ID int IDENTITY(1,1) NOT NULL,
Url nvarchar(300) UNIQUE NOT NULL,
PubDate datetime NOT NULL,
Active bit NOT NULL,
Hit int NOT NULL,
...
)

and there's an index as follows:

CREATE NONCLUSTERED INDEX indexTblNews_Url
ON tblNews(Url)

My select query is:

CREATE PROC spNewsGet
   @Url nvarchar(300)
AS
   UPDATE tblNews
   SET Hit = Hit + 1
   WHERE Url = @Url
     AND PubDate > GETDATE()
     AND Active = 1;

   SELECT
      *
   FROM tblNews
   WHERE Url = @Url
     AND PubDate > GETDATE()
     AND Active = 1
   ORDER BY PubDate DESC

In low rated sites there is no problem and this works perfectly. But in database such big as 100000 rows and website having 2000000 single users / day, it crashes. It throws SqlTimeout exception at one of three pages. When I click a page consequently nearly all of them gives above exception.

I've checked hardware performance and processor consumption is at %70 of I7 3.6 GHZ, ram consumption is 1.5 gb. But there is more empty ram memory. How can I overcome this problem?

Any helps would be very appreciated.

Are you sure you are closing off your connections once you have used them? Have you checked your execution plan in SQL server to see if you are utilizing your index properly? — Paddy, Oct 23 '13 at 15:15
Well, you are searching and ordering by PubDate, why not index that too? Also, I guess `url` is not a unique column? — MikeSmithDev, Oct 23 '13 at 15:17
@Paddy I close off all connection also I checked with .net profiler. There is no open connections. — Mesut, Oct 23 '13 at 15:19
@MikeSmithDev I forgot including unique key. Url is also unique. And when I include PubDate within index, it slows down. — Mesut, Oct 23 '13 at 15:21
@Mesut then why do you have an `ORDER BY`... if you are only going to ever get back one row since you are querying on the unique column `URL`? — MikeSmithDev, Oct 23 '13 at 15:23
Yes, you are right. I musn't use order by clause. But this didn't solve problem. — Mesut, Oct 23 '13 at 15:26
@Mesut I didn't say that was a solution... it's just one of many odd things with your query. — MikeSmithDev, Oct 23 '13 at 15:26
Have you looked at the execution plan for the query or run the tuning advisor? These might provide some insight as to the cause of the problem. — Chris Dunaway, Oct 23 '13 at 15:41
**(1)** is there also a **clustering key** on that table? If not: there should be! (on the `ID` column) **(2)** do you **really** need **all** columns in your `SELECT`? Try to limit yourself to what is **really** needed. **(3)** based on your `WHERE` clause, I'd try to use an index on `(Url, PubDate, IsActive)` (instead of just `Url`) — marc_s, Oct 23 '13 at 16:17
CLUSTERED INDEX of primary key conflicts with nonclustered index(Url). So I removed primary key. — Mesut, Oct 23 '13 at 16:22
Shouldn't the Identity column `ID` be either Unique or the Primary Key? Try fixing this, before you do anything else. — RBarryYoung, Oct 23 '13 at 16:47

score 1 · Answer 1 · answered Oct 23 '13 at 15:09

1

Most likely you are using SqlConnection. Try setting the ConnectionTimeout property of the connection to 0 to indicate an infinite timeout. If you're not using SqlConnection, you may have a similar property you can set.

answered Oct 23 '13 at 15:09

Michael J. Gray

9,784
6
38
67

This is not the proper solution, my expectation is database to get fast. – Mesut Oct 23 '13 at 15:11
1

@Mesut Since you did not specify this in your question, I suspected you wanted a quick and dirty resolution to the issue. I am glad to hear that you understand this is a workaround and not a real fix. I up-voted your question, so lets hope someone can help you out. – Michael J. Gray Oct 23 '13 at 15:15

score 1 · Answer 2 · edited May 23 '17 at 10:31

A likely reason why it works quickly for some sites, but not for others, is that for high volume sites, the URL is no longer selective enough for the existing index to work. As a result, the UPDATE will likely escalate beyond row locks, causing contention for the clustered / non-clustered indexes.

I doubt that connection starvation is an issue - setting the connection pool max size beyond 32k is pointless, and concurrent SQL connections will also depend on factors relating to .Net threads.

I don't really understand why PubDate is future dated, but if PubDate > GETDATE(), and / or Active = 1 significantly reduce the amount of records in the update query, then I would also add one or both of these fields to the index (as applicable), i.e.:

CREATE NONCLUSTERED INDEX indexTblNews_Url
ON tblNews(Url, PubDate);

You are also duplicating the query - once to update, once to select. You can reduce the redundancy through a temp table and join back into it.

CREATE PROC spNewsGet
@Url nvarchar(300)
AS
  SELECT ID
  INTO #tmp
  FROM tblNews
  WHERE Url = @Url
  AND PubDate > GETDATE()
  AND Active = 1;

  UPDATE tblNews
  SET Hit = Hit + 1
  FROM tblNews INNER JOIN #tmp on #tmp.ID = tblNews.ID;

  -- Consider also using SET TRANSACTION ISOLATION SNAPSHOT or READ UNCOMMITTED here.
  SELECT tblNews.*
  FROM tblNews INNER JOIN #tmp on #tmp.ID = tblNews.ID
  ORDER BY PubDate DESC;
GO

And finally, as per the comment, you can consider dropping the isolation level on the final select, provided that the result set isn't used for 'transaction critical' usage.

He could probably just use a variable and not a tmp table since it is just an ID. — MikeSmithDev, Oct 23 '13 at 15:25
Using #TempTable comsumes more performance. I must use a variable. But there is smore columns at select result. How can I Both select and set a table and get all column of results back? — Mesut, Oct 23 '13 at 15:30
And finally, as per the comment, you can consider dropping the isolation level on the final select, provided that the result set isn't used for 'transaction critical' usage. How can I do this? — Mesut, Oct 23 '13 at 15:36
@StuartLC I see. I just meant like `DECLARE @id INT`. No table at all. — MikeSmithDev, Oct 23 '13 at 15:39
Re : More columns in the select - simply join back into the underlying tables. Re : `SET TRANSACTION ISOLATION LEVEL SNAPSHOT;` or `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;` Just be aware of the [consequences of the latter](http://dba.stackexchange.com/questions/10655/is-nolock-always-bad). @MikeSmith is correct - I missed that URL is unique - there is no need for a temp or table variable at all - you can store a simple INT variable for `ID`. — StuartLC, Oct 23 '13 at 15:42

Sql timeout Exception although max pool size is set to 1000000

2 Answers2