October 2, 2007 at 2:38 pm
Hi All,
one of my server seems to be real slow. I Have checked all possible causes for the slow performance : RAM, DISK SPACE, SQL processes, error logs etc.
All end users are complaining about the application freezing every time there is data to be saved into a table or updated to a table.
Here are few things I Observed:
1.) Error shows just one error - ERROR 1205, severity: 13 State: 61.
Transaction(Process ID #) was deadlocked on lock resources with another process.
2.) in the performance monitor there are 217 full scans being run on an average.
Everything else seems to be normal.
Please suggest possible solutions for these issues.
Thanks,
Sathya.
October 2, 2007 at 2:53 pm
Checked the network?
Even with only 1 deadloack, you might have heavy locking, you may also have auto-close or auto-shrink on which may cause these types of problems (when the db opens, but mostly when the files grow back up).
Have you ran the application from the user's pc to see just how slow it is?
Did you check for bad exec plans?
Did you check for missing/out of date stats?
Index framentation?
File fragmentation on disk?
Disk contention for tempdb?
Have you considered that some users might use that as an excuse to not do their work properly... or on time (much rarer, but apparently that exists).
October 2, 2007 at 2:55 pm
Also, if all the user PCs are slow for a particular application or even form, you might have a crappy application in place. Might be a good place to watch next (run the profiler and see what really goes on under the hood when "nothing" is happening >> you might have 1 000 000 small queries that don't seem all that bad and don't stand out in a trace).
October 2, 2007 at 3:10 pm
Hi Ninja_RGR'us,
- I have checked the auto-close and auto-shrink options and they are disabled.
- there is also no disk contention for TEMPDB.
- the slowness of the application : it takes the application to update or save data anywhere between
30 secs to 1 minute.
- as far as file fragmentation on disk and index fragmentation are concerned, I have not taken a look.
could you suggest few procedures of how to check the index fragmentation and file fragmentation?
also, the deadlocks errors : error:1205, severity 13 occurs atleast 200 times a day in the sql error log.
Thanks,
Sathya
October 2, 2007 at 3:22 pm
I'd go with profiler next. That will rule out the poor app design option and will even point you to the dead lock possibility.
I've not met a file or index fragmented enough to cause deadlocking (but it might explain the slow part, I'm still assuming that the slow and dead lock may be 2 different ploblems altogether).
October 2, 2007 at 3:53 pm
could you suggest few procedures of how to check the index fragmentation and file fragmentation?
also, the deadlocks errors : error:1205, severity 13 occurs atleast 200 times a day in the sql error log.
Use dbcc showcontig with tableresults,fast
and check logical fragmaentation > 15% for tables that have large number of rows.
run sp_updatestats just to make sure that stats have at least a minimum quality
in terms of deadlocking you should use enable trace flag 1204 and and 3605 look at the culprits and "fix" the issues. 200 a day sounds like a lot to me.
Cheers,
* Noel
October 2, 2007 at 3:56 pm
Sounds a lot to me too... but I have no baseline for his server, and I have never had a deadlock happen (unless I tried to make one), so I'm out of my area of expertise there :).
October 3, 2007 at 7:24 am
Deadlocks is a 'bad design' issue, not a performance issue per se.
It's 'fixed' by re-designing the process that is found to be the cause.
What about disk-configuration?
Does the box have a poorly designed i/o subsystem, and thus exceedingly long disk queues?
For example, is it a 3-drive RAID5 with everything on it, or something else?
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply