SQL Server Database - One Table has read issues - A mystery to solve!

We have a 2008 R2 SQL Server with a number of minor databases on them.  One database, called ProdData had a situation last night that I struggle to find the root cause (or even a theory).

On our production floor, we have about 120 stations running a VB App which communicates with the SQL Database to update production, downtime, etc.

Within the ProdData database, there is a table called downtime, which simply writes a single record everytime downtime is recorded on one of the production stations.  It records the ID, Down Start, Down End, StationName, and the duration.  There are probably no more than 250-300 appends to this table every 8 hours.

Around 10PM last night during a shift change, the stations were filing their production numbers.  To do the final filing, they have to access 4 separate tables to assemble the data needed to file into our main system.

What we noticed was, when the stations attempted to collect the data from the downtime table, they were timing out and shutting down, thus not completing the filing process. When we linked into the network to troubleshoot, we found that we could not access the downtime table.  All other tables responded as they usually do.  The Downtime table is actually the smallest table, at only 190,000 records.  We purged 70,000 records, and there was no immediate improvement.  

5 hours later, everything is back to normal.  Like nothing was ever wrong.


- All stations could continue to write to the downtime table without issue all night. We could not find a noticable pattern to suggest which station may have triggered the issue, or even what time the issue may have started.
- No stations could query the table in a reasonable amount of time, thus the timeouts.
- When we attempted to access the table, a select * would take 9 minutes
- On neither the workstations, nor the server did we observe:
    - Any event log items both Windows or SQL
    - No CPU, Memory or Network abnormalities.  No spikes at all.
- Now reviewing the data in the downtime table, we can find no data abnormalities in the last 2 weeks of data.  No special characters, missing fields, incomplete dates, nothing unexpected at all.

We have already made code changes to the VB App to accomodate a timeout and just omit downtime information if necessary, however the root cause of the issue is a complete and unexplainable mystery here.

So, anyone have any ideas?

It is  the worst kind of problem to me that has no explaination or theory to fall back on to fix.
Who is Participating?
lcohanDatabase AnalystCommented:
WITH (NOLOCK) SQL hint is your answer in my opinion and I know there are a lot of PROs and CONs against it but I'm using it with success since SQL re-write to 2000 version.
Assuming you use default READ COMMITTED isolation level I would add it to ALL tables participating in select and joins regardless to avoid locking/blocking.
I would also install and use SQL own Performance Dashboard Reports to see what missing indexes you can add to help your long running queries.

Performance Dashboard

TheMetalicOneAuthor Commented:
Thank you very much!  I will take your advice and see if it helps track down the issue.  I will close out the question tomorrow just in case there are some more ideas that may float by.

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Mostly questions.

You didn't have a grow operation occur against the database file (not log file) at the same time as your issue, did you?

Is that downtime table, really a table or is it just a *shiver* heap?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TheMetalicOneAuthor Commented:
I will check for a grow op.

Sadly (I think) I have no idea what a shiver heap is. It is definitely a table in the db

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Sorry... I was trying to be funny with the *shiver* part.  (It was a mental shake.)

A 'table' without a clustered index is properly referred to as a heap.  One of the problems with a heap, as I recall, is that every read against the table has to perform a complete table scan... it has to read ALL the records in the table.  Writes can be faster, because there shouldn't be page splits.
TheMetalicOneAuthor Commented:
Lol. OK, that makes sense. I would suspect the table is a heap.   We are installing the performance dashboard as suggested to see where we can improve. Any suggestions on resources for best practice for indexing?

Thank you both for your contributions.
Scott PletcherSenior DBACommented:
I too think that the [downtime] table being a heap could be your problem.

You can run this command to check, and to fix the problem if it is at the same time:

--Note: If you get an error about online rebuild not being available,
--remove "ONLINE = ON," from the command and re-run.

If the table is not a heap, i.e. it already has a clustered index, you will get a message stating that.
TheMetalicOneAuthor Commented:
The Table is indeed a clustered index already.  I suppose perhaps that is a default?

Thank you for all your contributions.  I am going to hold onto this post for the remainder of the day so I can post any actions.
Perhaps the existing clustered index is less efficient than it could be.

It may be useful to review all the indexes on this table.
TheMetalicOneAuthor Commented:
Thank you all for your feedback.  We are monitoring the system with the performance dashboard, the table is a cluster, but we will be reviewing all tables in the databases for index improvement opportunities.  

The app has been coded to detect a table problem like this moving forward, and we revised the code to cut the number of reads down by 60% just by improving code efficiency.

Problem has not re-occured, hopefully it will stay away.  

Thank you again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.