• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

SQL Searches randomly failing

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
rwheeler23
Asked:
rwheeler23
3 Solutions
 
OriNetworksCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
rwheeler23Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LowfatspreadCommented:
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
 
rwheeler23Author Commented:
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
 
OriNetworksCommented:
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
 
rwheeler23Author Commented:
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
 
OriNetworksCommented:
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
 
rwheeler23Author Commented:
Thank you very much for these articles. I will review these tomorrow and then decide how to proceed from there.
0
 
rwheeler23Author Commented:
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
 
rwheeler23Author Commented:
Thanks to everyone for taking the time to respond to this query.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now