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

Posted on 2014-04-01
Medium Priority
Last Modified: 2014-04-03
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.
Question by:TheMetalicOne
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 40

Accepted Solution

lcohan earned 800 total points
ID: 39970268
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


Author Comment

ID: 39970342
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.

LVL 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 800 total points
ID: 39970418
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?
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


Author Comment

ID: 39970463
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

LVL 30

Expert Comment

by:Rich Weissler
ID: 39970522
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.

Author Comment

ID: 39970576
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.
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 39970672
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.

Author Comment

ID: 39972099
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.
LVL 49

Assisted Solution

PortletPaul earned 200 total points
ID: 39974019
Perhaps the existing clustered index is less efficient than it could be.

It may be useful to review all the indexes on this table.

Author Closing Comment

ID: 39976370
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!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question