September 9, 2010 at 3:12 am
Hi,
I couldn't find a SQL 2000 forum and so have posted here instead. If there is a more suitable forum then please let me know and I'll move the thread.
I have a SQL 2000 SP4 server that generally works well. At the moment one of the databases on it appears to be struggling when attempting to read/write to a specific table. The other tables appear to be fine. The table is heavily used and many write requests appear to be resulting in a deadlock situation which is annoying my users somewhat. The table has historically thrown this type of error (due to it's usage) but seems to have got a lot worse in the past few days.
The datable is a vendor database and so I can't redesign it in its entirety, but I do have some control over it.
Is there a tool I can use to diagnose the problem with the table. I suspect the answer will be SQL Profiler, in which case I would appreciate some tips on how to configure the trace to be relevant for my task.
Could other factors such as disk space (there is more than 50% free on the disk), backups, or other services effect just one table? How could I check and rule these out as potential trouble makers?
Any (relevant!) suggestions are welcomed.
Mike
September 9, 2010 at 3:59 am
check the update statement, are they 'with lock' hint which making the table not be available for other use.
Also what is the isolation level of the database/server?
----------
Ashish
September 9, 2010 at 4:27 am
ashish.kuriyal (9/9/2010)
Also what is the isolation level of the database/server?
??
Isolation level is a connection-level property. Servers and databases don't have isolation levels, connections do.
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
September 9, 2010 at 4:30 am
Are there specific queries that are slow? Check and see if they can be optimised.
Try updating statistics (with fullscan) see if it makes any difference.
p.s. There are SQL 2000 forums here. Further down the main page iirc
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
September 9, 2010 at 4:30 am
ok, so you mean if the isolation level is not the read commited then he will not have any performance issue
----------
Ashish
September 9, 2010 at 4:32 am
ashish.kuriyal (9/9/2010)
ok, so you mean if the isolation level is not the read commited then he will not have any performance issue
That's not what I said at all.
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
September 9, 2010 at 4:35 am
ok, but thats what I mean to ask in my original question.
----------
Ashish
September 9, 2010 at 2:25 pm
Is the table fragmented? If so, how heavily? Please post the results of:
DBCC SHOWCONTIG( tablename )
That will also show whether the table has a clus index or not, which dramatically affects table performance as well.
Scott Pletcher, SQL Server MVP 2008-2010
September 10, 2010 at 7:47 am
In addition to Gail's advice, try some of the classic deadlock resolutions. Check the code to ensure that everything is accessing the tables involved in the same order, stuff like that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply