Clicky

We have a (physical) Server 2003 r2 server that is hosting several SQL 2005 instances.  One of the databases was recently moved from another server running SQL 2000, to this server running SQL 2005.

The application side is an internal website.  We have several error catches setup to catalog any issues that are reported to users.  Since moving it to the new server we have noticed that it will go on a string of time outs, all of the timeouts appear related to write (update) commands.  Then it will run fine for a few hours, then a few more will timeout, etc...

I am trying to figure out how we can find out why they are timing out and how we can prevent them.

This instance never seems to use very much RAM, I have it configured to use a maximum of 1GB and it never seems to break the 250MB mark.  And the server is using less RAM than it actually has.  The instance appears to use a lot more CPU than our other SQL instances normally do.

I have kept a few counts up and have been looking at the 'Avg Disk Queue' thinking perhaps it is a Disk Queue issue - while I have seen a few spikes upwards of 8, normally it is below 2.  This server has 6xSAS drives on it.

Is there anything else I should be looking for?

Also, the website is currently setup using classic ASP.

asked 12/08/2011 06:12

americaneldercare's gravatar image

americaneldercare ♦♦


7 Answers:
Look at long running queries holding locks on the records being updated.
link

answered

sweetfa2's gravatar image

sweetfa2

Have you done maintenance on your database?  I have seen issues like this where the indexes are causing problems.  You may need to go and re-index your tables.  I have seen corrupted indexes after a restore.

Re-create your indexes and maybe even recompile your stored procedures.
link

answered 2011-12-08 at 15:14:05

Randymar's gravatar image

Randymar

Run SQL Server Profiler and get the Lock and time out details, capture those queries and get its execution plan and analyze it makes sure that you have proper indexes and they are not fragmented. Also update its statistics if they are outdated. Its important that your tables are heavily indexed as this slows down the the Inserts\Updates\delete.
link

answered 2011-12-08 at 15:29:49

anujnb's gravatar image

anujnb

going to look into those a bit however it appears it may be a Disk IO issue.  I have had the Perf Mon logging now [to file] and matched it up with when the errors are appearing and we have had spikes upwards of 70-80 coinciding with those errors.

Is there any way to see which SQL instance is using the Disk IO?  I know that there are Counters in PerfMon for SQL but not sure if any of those will help in figuring out what is causing the IO requests (even if it is just narrowing it down to an instance).
link

answered 2011-12-09 at 00:55:42

americaneldercare's gravatar image

americaneldercare

Probably at the moment that or a transaction log backup is taken or that there is a problem with the allocated space.
With the dynamic managment views you can have some more info on which sort of problem it can be
http://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/
http://ssysadmin.com/sys-admin/dbms/identify-io-bottlenecks-ms-sql-server.html

You just moved instance so it can be everything: hardware, db-files on same/or wrong type of discs , interaction with other instances (on same volumes), no preventive index-reorganisation, ....
link

answered 2011-12-09 at 07:03:56

jogos's gravatar image

jogos

apparently the maintenance plans were having an issue due to a missing '\' in microsofts wizard driven task creation.  While the database itself was only 3GB, the transaction log had grown to a massive 60GB.  Upon getting the maintenance tasks running, the transaction log went back down to a normal size and the server has begun operating normally again.
link

answered 2011-12-10 at 08:04:35

americaneldercare's gravatar image

americaneldercare

the maintenance of the indexes wasn't the issue so much as the maintenance of the database itself which is why I gave 'partially' to the Complete/Accurate parts.  But otherwise it appears that resolving the maintenance task not running has resolved the issue.

link

answered 2011-12-13 at 10:39:50

americaneldercare's gravatar image

americaneldercare

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×30
×132
×3

Asked: 12/08/2011 06:12

Seen: 366 times

Last updated: 12/17/2011 05:20