Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL Searches randomly failing

Posted on 2015-01-22
Medium Priority
Last Modified: 2015-02-06
We are running SQL Server 2014. We have a very large inventory table and a corresponding item site table. We have a very small routine that help the user during date entry. What it does it goes to the first record in the item site table and returns that location. Over the past few months we have noticed that this search will randomly fail. They can type the same part number in a dozen times it works fine and then on the 13th try it fails and then do it another 12 times and it works. I am thinking perhaps a corrupt index or worse yet, the item site table may be becoming physically damaged. What would you suggest be the steps to rectify this issue? We can start with the simplest tasks and then move onto more complex solutions, if needed.
Question by:rwheeler23
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 17

Expert Comment

ID: 40565388
If you suspect the database may be corrupted you can run DBCC to check the database, which should be a routine part of maintenance anyway. However this doesn't sound like corruption to me at first.

Since you mention it is a very large table my first guess is that the query is taking took long and a timeout error is returned. For example, if this is a .net application I believe the default may be 30 seconds. You can try increasing the connection timeout in the applications connection string or if this is a vendor supplied software you can report it to them for their recommendation. Also related you may investigate possibly adding an index or changing the query itself for better performance.

If the query returns an error quickly within a few seconds then there may be something else going on. How long does it run until you get an error and what error is displayed? What type of application is this?
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 40565460
Would need to see that routine's actual code as well.  Who knows, maybe there is an obscure bug/issue in the code itself.

Author Comment

ID: 40565639
This application has about 160,000 installations around the globe. I have checked with others plus I have been using this software for 20 years and no one else has ever reported this issue. This is why I think it is something internal. DBCC showed no errors last month. We do not have access to the code and since it happens only randomly it is very difficult to catch using SQL Profiler. Personally I think it is something these users are doing. They all type about 200 words per minute and are constantly cutting and pasting.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 50

Assisted Solution

Lowfatspread earned 200 total points
ID: 40565928
please be more specific ...

what do you mean by fail?

is an error message returned...
wrong results....
no output...

your original question says "date entry"  ... is this a typo for data entry?

the "small" routine to obtain location ... is that your own? or part of a larger package?
ie what/which application is installed in 160K locations?

why are you concerned about cut/paste   ... do you suspect buffer overflows, or unexpected whitespace problems with the pasted data?

Author Comment

ID: 40566220
I am concerned that since these three people type so fast that it is possible they might add some extra character to the end of the item number and then of course the search will fail. There is no error message it just does not find the item as entered. It behaves as if it is a new item. Right now we are playing the finger pointing game. They blame the application and I blame something they are doing. I will try to rebuild the indexes and run dbcc again and update statistics and all the SQL tasks you are supposed to do.

By the way, the application is MS Dynamics GP. It has been sold for 20+ years and there are hundreds of thousands installations.
LVL 17

Expert Comment

ID: 40567278
As I stated the sudden failure could be due to the table suddenly becoming large enough to reach the timeout limit when running the queries.
If you are using sql 2008 as you tagged, you can turn on SQL Data Collection.
From SQL Management Studio, under management, right-click Data Collection and configure the data warehouse. This will routinely setup jobs to collect data that you can report off of. These reports can list wait types that may be blocking queries or simply queries that don't perform well. After collecting this information for a while, have a user report the next time the timeout occurs and you can review the reports to find what was going on at that time.

You could also try running database engine tuning advisor. If you happen to find the exact query that is too slow, you could try pasting it into a new query window and getting the execution plan. This may sometimes suggest additional indexes to add to the table for enhancing performance.

Author Comment

ID: 40567567
There is no tag for SQL 2014 so is Data Collection available for SQL 2014? With this tool will I be able to search by table? It is only this one table and there is an index on the two fields involved in the query. Actually, that just made me think. This database has about 100 table and data collected since 1995. Some tables have in excess of 500,000 records and the entire database is over 14GB in size. SO why are we having trouble with just this one table? I was thinking perhaps the pages of the index may be getting corrupt.
LVL 17

Accepted Solution

OriNetworks earned 1600 total points
ID: 40567657
Here is the official Microsoft documentation for this feature in SQL 2014

You will not search by table in these results but it can tell you any wait that may be locking up the queries and if some of the worst performing queries are working on that table.

If you would like to check you index health you can review the following documentation

Author Comment

ID: 40567709
Thank you very much for these articles. I will review these tomorrow and then decide how to proceed from there.

Author Comment

ID: 40594812
These tools proved very useful and I will use them in the future. It turns out in this case the issue is caused by an external application. I had the users send me a list of all orders where this was happening. As I examined the list I noticed all the order numbers began with WEB which tells me they are imported from their website. I am talking to the developers right now and have alerted them to the fact that this issue only occurs with orders that are imported through their application.

Author Closing Comment

ID: 40594819
Thanks to everyone for taking the time to respond to this query.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

610 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