November 13, 2009 at 4:50 am
We are experiencing some dead locks. During testing, I have captured the trace. After some testing, I have analysed the trace in DTA and its suggesting me to create some indexes.
Should I go ahead and create all of them or some other things I need to consider?
Could any one suggest?
cheers
November 13, 2009 at 5:10 am
siddartha pal (11/13/2009)
Should I go ahead and create all of them or some other things I need to consider?
Not without testing them to make sure they really do help.
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
November 13, 2009 at 2:28 pm
I would certainly agree with Gail, in that you need to test these indexes/stats before implementing them blindly into your production environment. However, you may be looking at the wrong tool to help diagnose your deadlocking issue, examining the trace you created will certainly be a good starting point, then determine what part of your code is triggering the deadlock to occur, and work your way back from there.
November 17, 2009 at 4:05 am
thanks for the info.
the traffic is quite high on live and I dont have access to live. I can only test the same on dev. so now for me its quite difficulat to know how the new index will help. Any pointers, what one can do in such situations.
cheers
November 17, 2009 at 4:14 am
You shouldn't be testing on live anyway. That's what a test server is there for.
Run the queries against the DB. Time them. Create one of the indexes. Run the queries again. Have any improved? Is so, by how much. Drop that index and repeat with a different one.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply