October 30, 2010 at 11:29 am
Hello everyone,
Right i've spent 5 days trying to figure out why this problem is happening without success so any help here is much appreciated.
The Database / Server / Network
We are running a fairly small database which is about 400mb in size. The server is SBS2008 with SQL Server 2008 and exchange (6 mailboxes) on it. The server is an I7 920 with 16GB of DDR3 Ram which is ultilised at only 70% during busy times while the CPU is being use at 20% at busy times. 1.5TB of storage space in a 4 Drive 500gb RAID5 setup. All clients are running on Gigabit LAN (with Gigabit switch, ect) and are running AMD 9650 Quad Cores with 2GB DDR2 Ram.
The Problem
Everything on the server works perfectly, the hardware is a little over a year old and has been running great. In-fact everything works and has been working great up until 6 days ago. Basically, when the user on the database application prints an invoice, and the query is run to fetch the invoice information, the machine will Hang for 30 seconds and have the Error [Microsoft][ODBC SQL Server Driver]Timeout expired. This is intermitent however as sometimes it can print within 20 seconds and print the invoice fine. 9/10 attempts however will see the error and a blank invoice printed.
Everything else works with the application including some more complex and demanding queries regarding a delivery scheduler. I can access the server directly and log in fine and when i test the query in the query browser it runs in 0.329 seconds. So why the timeout / problem?!
What have i tried
-Checked Event Viewer for any issues. None found.
-Increasing SQL priority
-Checking the RAID5 Integrity
-Upgrading the RAM
-Restarting the services / server / router / switch
-Trying with only one connected user (and not the busy 12 connected users during the day).
-Increasing Remote query timeout value (its now set to 0, hence unlimited).
-Increasing the Remote Login Timeout to 60 seconds.
-Increasing the Connection-string timeout for designer to 60 seconds.
-Restarting the server / sql server again
Any ideas? This is giving me a massive headake not only from users pestering me but from trying to find a solution.
Thanks,
Rob1988
October 30, 2010 at 11:36 am
rob1988 (10/30/2010)
-Increasing SQL priority
Please go and set that back to default. It is not recommended that the priority be increased.
-Increasing Remote query timeout value (its now set to 0, hence unlimited)
That's only of use if the query uses remote servers (openquery, openrowset, 4-part naming)
Is there blocking?
Any deadlocks?
What's the wait type when the invoice takes longer than usual?
Can we see table defintion, index definitions and execution plan?
p.s. It is generally recommended that SQL and Exchange NEVER share the same server due to resource contention.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2010 at 11:51 am
To be honest the software was created by a thid party which chagres a fortune for support. I reluctantly called them in 2 days ago and they said "hmm, we don't know why this is happening. Its your server not our software." So i cant really tell you must about how it works.
All i can say is 6 days ago invoices printed instantly, now its this issue. Nothing has changed on the server with no new software installed but as i said i did double the RAM as it was ultilising 97% of it. Now its only using 70% but this was done after the problem started in an attempt to fix the issue.
I am unsure if i am allowed under copyright to paste the query here; but the query had no issues up until 6 days ago and now its over 100 times longer to execute and on client computers (not the sql browser) can timeout. The query does run in less than 0.4 seconds on the server every time i test it with no errors.
N.B. I note about exchange but due to the low number of mailboxes i can limit exchanges grab on rscources without any issues.
October 30, 2010 at 12:11 pm
Without details it's going to be very hard (read near impossible) to do anything other than guess. Really need lock/wait info to have much of a chance if it's not something trivial.
I will tell you that it's unlikely to be hardware-related. Possibly some tipping point for data volumes or the like.
Try running this for every table in the database, see if it makes any difference
UPDATE STATISTICS <TableName> WITH FULLSCAN
Are you doing regular index maintenance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply