?
Solved

SQL Searches randomly failing

Posted on 2015-01-22
11
Medium Priority
?
84 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
[X]
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
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: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.
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 50

Assisted Solution

by:Lowfatspread
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?
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
 
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 1600 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

800 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