Solved

SQL Searches randomly failing

Posted on 2015-01-22
11
75 Views
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.
0
Comment
Question by:rwheeler23
11 Comments
 
LVL 17

Expert Comment

by:OriNetworks
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?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 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.
0
 

Author Comment

by:rwheeler23
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.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 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?
0
 

Author Comment

by:rwheeler23
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:OriNetworks
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.
0
 

Author Comment

by:rwheeler23
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.
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 400 total points
ID: 40567657
Here is the official Microsoft documentation for this feature in SQL 2014
https://msdn.microsoft.com/en-us/library/bb933864.aspx

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
https://technet.microsoft.com/en-us/magazine/2007.03.sqlindex.aspx
0
 

Author Comment

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

Author Comment

by:rwheeler23
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.
0
 

Author Closing Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now