[Webinar] Streamline your web hosting managementRegister Today


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
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 31

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?
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


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 31

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

607 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