| Author |
Topic |
|
shaharru
Yak Posting Veteran
72 Posts |
 Posted - 2010-12-19 : 09:27:04
|
I'm using SQL SERVER 2008 and .NET.I'm using a sql table as a queue , i constantly add new items to the table ,in another thread at the same time i select and fill a .net queue with items from that table (keeping the queue always full with 100 items).The following UPDATE query causes timeout errors.the "Pages" table has 5,000,000 records.Error:System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Query:UPDATE PagesSET Checked = 1 , LastChecked = GETDATE()OUTPUT Inserted.ID, Inserted.Title INTO @UpdatedWHERE ID in (SELECT TOP (@Count) ID FROM Pages WHERE Checked = 0 and TypeID = @TypeID ORDER BY ID)SELECT * FROM @Updated END I have an index on Checked , TypeID columns.On a different thread I Insert records to Pages table, this runs continually (a page is added every ~1 sec or less).INSERT INTO Pages (Title ,.......)VALUES (@Title , .......) Why do i get the timeout error and how can I solve it?thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-19 : 11:37:49
|
| Also, looks like checked is a bit column, making that index useless. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-19 : 11:45:48
|
| Thanks Tara. Of course, now I have to play around with that, but if Kimberly says so, then I'm sure it's so.Tara, question: did she specify that proceeding columns should be selective? I ran a couple of quick tests and couldn't get anything but scans. But, index (bitColumn, selectiveColumn) and use them both in the WHERE clause, I get a seek.Sorry for the hijack. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-19 : 12:09:52
|
| We had a discussion about the Missing Indexes DMV report suggesting a very non-selective composite index on one of my production systems. The Impact column showed 3 billion, so I asked her about it. It was a 3-column composite index. All 3 columns had low selectivity. And no matter what order I put them in, they would still have low selectivity. She said that was fine since the query the DMV was targetting was range-based.Her response was that selectivity is irrelevant for range-based queries. From the OP's UPDATE query, it appears the part where the index would be used is a range-based queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-19 : 12:13:45
|
| Selectivity is irrelevant when the index is covering. It's only when the index is not covering and lookups to the cluster/heap are needed that SQL will only use a very selective query.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-19 : 12:25:59
|
| Thanks Tara and Gail. |
|
|
|
shaharru
Yak Posting Veteran
72 Posts |
|
|
shaharru
Yak Posting Veteran
72 Posts |
Posted - 2010-12-19 : 12:38:43
|
quote:
Originally posted by russell
Also, looks like checked is a bit column, making that index useless.
"Checked" column is a bit column , used to identify which rows have already been de-queued. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shaharru
Yak Posting Veteran
72 Posts |
Posted - 2010-12-19 : 13:32:20
|
quote:
Oh yes forgot "covering" in my reply. Kimberly specifically mentioned covering as well to me.
Sorry but i really have no idea what you guys are talking about. Can somebody please help me figure how to get rid of the timeout error? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shaharru
Yak Posting Veteran
72 Posts |
Posted - 2010-12-19 : 15:35:47
|
quote:
Originally posted by tkizer
Sorry shaharru, it's a side-bar conversation about your index.Have you checked blocking? I don't really see anything wrong with your code or indexes, so I'm suspecting it's something else. Check blocking and wait stats.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
@tkizer , Thank you for the answerI believe the problem is blocking , probably the rapid insert operation is blocking the update/select.but i dont really know the right tools to find out if its true and how to fix it.I've looked on Sql "Activity Monitor" - "Resource Waits" , but that only gives me real time info and i cant really catch the error when it happens.ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shaharru
Yak Posting Veteran
72 Posts |
Posted - 2010-12-19 : 16:03:04
|
quote:
Originally posted by tkizer
Are you able to implement read_committed_snapshot isolation level? If you aren't familiar with that, then I'd highly recommend looking into it as it will likely solve your blocking problem. With the default isolation level, reads block writes. In RCSI, reads don't block writes. But you have to be aware of what RCSI does in order to know if it's okay.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I'm not familiar with read_committed_snapshot , ill read about it.Will the INSERT statement i wrote above block the UPDATE Query , and do you think READ_COMMITTED_SNAPSHOT can help? |
|
|
|
shaharru
Yak Posting Veteran
72 Posts |
Posted - 2010-12-21 : 07:57:20
|
| anyone? |
|
|
|
|
Table as a Queue ?