Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 24 '12 at 18:19

This question came from our site for professional and enthusiast programmers.

6  
Maybe you have an open transaction that has a lock on those rows –  Lamak Feb 24 '12 at 18:04
    
Good idea, some of coworkers just suggested that. Let's see ... –  Jan Zich Feb 24 '12 at 18:06
4  
Take an execution plan for a fast query and a slow query, and compare them. The free SQL Sentry Plan Explorer will reduce the amount of time you need digging through Management Studio's representation of a plan (download: sqlsentry.net/plan-explorer/sql-server-query-view.asp) Disclaimer: I work for SQL Sentry. –  Aaron Bertrand Feb 24 '12 at 18:21
1  
And what is different about the query that never finishes? Surely you could take an estimated execution plan, or simplify the two queries until they both come back in a reasonable time (e.g. remove joins, output columns or calls to UDFs). –  Aaron Bertrand Feb 24 '12 at 19:20
3  
@JanZich: Also, if the blocking session has a negative SPID, check for distributed transactions that got interrupted in action and were never rolled back on your server. –  Marian Feb 24 '12 at 19:51

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
Normally, the index seek in that query is used million times a day and it takes an unmeasurable fraction of time. Now, it seems that it works for most of the records in the table, but for some it simply waits forever. Hmm ... yes, it looks like a locking issue. –  Jan Zich Feb 24 '12 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.