Simple SQL queries not finishing

Question

I don't quite know how to formulate the question because we don't know much yet, but I would like to ask sooner than later because this looks like something that should not be neglected.

This week we started having issues with our database server. It appears to be a data consistency problem and it manifests by timeouts even on very simple queries and small tables. We "fixed" the problem by restarting the server earlier this week and it went away, but now it seems that it is coming back and this time on more crucial tables. For instance, I just did some investigation and I'm looking at a query like this:

SELECT * FROM table WHERE id = 1234

for a particular ID. The table has about 30+ million rows. But it seems that it happens only for a small fraction of records. I bet when I restart the server or backup and restore the database on another server, it will all be fine. But I will try.

At this point, I'm running:

DBCC CHECKTABLE ('table', NOINDEX)

but it seems it's going to run forever. When we hit the issues the first time, I checked the offending table and it was fine. This new table is much bigger.

Some background technical information:

• SQL Server 2008 R2, Windows Server 2008 R2
• AWS/EC2, m2.2xlarge, 32 GB RAM, 4 x 1TB RAID 0
• almost no disk IO, it seems that most of the db is in memory
• total db size: 100GB

The ELB volumes are "brand" new. We created them this week.

Edit: I just used the following command:

SELECT
    sqltext.TEXT,
    req.session_id,
    req.blocking_session_id,
    req.wait_type,
    req.wait_time,
    req.last_wait_type,
    req.wait_resource,
    req.open_transaction_count,
    req.transaction_id,
    req.total_elapsed_time
FROM
    sys.dm_exec_requests req
CROSS APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

and found that my query it waiting for a shared lock (LCK_M_S) where the blocking session is waiting for another shared lock blocked by a session that does not exist.

Edit 2: OK, the session exists (I found it using sys.dm_exec_sessions), but it does not appear to do anything now.

Edit 3: I can't find anything interesting about the session. I see what web server it comes from, but not much else.

Edit 4: Edit 4: We found a possible bug in our code: a function that was not making sure a database connection is closed. On the other hand, it looked like the transaction the function was using used to be correctly disposed in which case all locks should have been cleared. It's still not very clear to me, but it seems to be the likely reason. We are going to fix the bug and and keep an eye on it.

Answer 1

What's the wait type for the query when it is hung? This will tell you exactly what it is waiting for. Go find and install sp_whoisactive on the server and run the stored procedure when you have the problem. This will show you the spid and give you the wait type.

Odds are you are being blocked by someone writing to that row, or another row on that page or you are waiting for the disk to respond.

Answer 2

Try to run DBCC CHECKDB on the problem DB and wait until it finishes. If there is a physical data inconsistency which produces such a strange behavior, then it is too dangerous to work with this DB as you may lose all your data.

1. Do the backup as soon as possible.
2. Check the DB.

BUT

If the table has BLOB columns with relatively large amounts of data, it is absolutely normal that checks against this table should take a long amount of time.

Answer 3

not really sure what you expect as an answer but if the table has 30+ million rows, its expected that DBCC comand would run for a long time.

When you say:

But it seems that it happens only for a small fraction of records

you mean that you are worried that the whole table are not beeing scanned? That's normal if you have a index on ID, the index would be scanned which would result in fewer reads.