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

x
?
Solved

Query timeout on SQL server suddenly appearing

Posted on 2011-09-08
6
Medium Priority
?
508 Views
Last Modified: 2012-05-12
I have an installation with two Dbs which should be identical in structure on the same SQL Server running two different companies. One of these is significantly larger than the other.

On the smaller of the two timeout errers have started to appear on queries which return results instantly to the users of the  other Db which has about 4 times the number of rows in each table.

Something is wrong but I don't know where to begin. I have tried copying the query to a file and running the tuning advisor but it comes up with Error Unable to open input file on the Consuming Workload section.

Any ideas?
0
Comment
Question by:grwallace
[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
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:celazkon
ID: 36501687
Don't forget to create an index on the tables involved in the timing-out query. This will greatly enhance performance of the sql query
0
 

Accepted Solution

by:
grwallace earned 0 total points
ID: 36501728
An index already exists for the query - I a concerned that there is data corruption or something of that nature
0
 

Author Closing Comment

by:grwallace
ID: 36527877
No relevant answers
Grad C as nothing satisfactory
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 40

Expert Comment

by:lcohan
ID: 36505682
indexes and statistics are not self maintained so sometimes you need to do a rebuild index and see SQL BOL for that or simply run:

update statistics table_name;
exec sp_recompile table_name;

to refresh stats and flush out of cache all old plans tied to the table_name.
0
 

Author Comment

by:grwallace
ID: 36506121
I had to raise a call to Microsoft to fix this one at a cost of £199. It is now solved with their help.The problem was due to due to a process locking out my query. Unfortunately there were no comments which were helpful of pointed me in that direction from Experts Exchange
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36506196
that was costly and for the future keep in mind - SQL Activity Monitor and SP_WHO2  or SP_WHO stored proicedures can reveal that at no cost to you.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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