May 27, 2010 at 1:20 am
Hi everbody
I have a problem about my sql server 2005 I got a trace and ı find a lot of deadlocks.Can somebody analyse this trace for me and say somethink.
http://rapidshare.com/files/392015163/TraceAllDeadLocks.rar.html
May 27, 2010 at 1:45 am
check why following SPID creating dealock use sp_who2 for it
SELECT distinct spid
FROM ::fn_trace_gettable('C:\Documents and Settings\bkumar\Desktop\TraceAllDeadLocks\TraceAllDeadLocks.trc',0)
where textdata like '%deadlock%'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 27, 2010 at 1:58 am
Trace is here
May 27, 2010 at 2:03 am
I found reason but i want your comments about that db sql server performance
May 27, 2010 at 2:23 am
What exactly are you looking for here? A general analysis of the 15MB trace file?
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
May 27, 2010 at 6:20 am
Yes i need some analyis about this trace what can i do avoid this deadlocks or anythink.....
May 27, 2010 at 11:50 am
How much are you willing to pay per hour for a good analysis of the trace file?
We are volunteers here, and many of us have our own jobs to do as well, and if you want something like this done, you should be willing to pay for it if you want it done well and in a short period of time.
May 27, 2010 at 10:47 pm
How much do you want for it?
May 27, 2010 at 11:07 pm
I usually charge around $1250-$1500 per month for something like this (one analysis and report a month, minimum duration 6 months). If you're interested, PM me and I'll send you a formal proposal
It really is a fair bit of work and, to do a proper job, we'd need the definition of all the tables referenced by any of the queries in the trace as well as a rough estimate of the rows.
I'll give you some free advice.
Get rid of all of those index hints first. I'm willing to bet at least some are hindering performance, not helping. Get rid of all the nolock statements. They could be causing incorrect results (See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx)
If you prefer to do the analysis yourself, these may help as a starting point.
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
May 28, 2010 at 1:17 am
I read that articiles thanks for your advice.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply