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.