April 25, 2008 at 12:54 pm
We are facing a siginificant performance problem with our production application..
The scenario...
1- 50 million row table that stores testing info
2- the table has a trigger that fires after every insert into this table
does something like this ... ((this is actually coded within a cursor and i am including the non-cursor version)
Update tblTest Set Status = 1 from
tbltest inner join inserted i on tblTest.ID<>i.id AND tblTest.serial_fk=i.serial_fk and tblTest.test_fk=i.Test_fk and tblTest.sequence=i.Sequence
where i.status = 0
3- checked the graphical execution plan and its using a NC index seek on serial_fk with a bookmark lookup. id is the clustered primary key and the table has several NC indexes on various cols
4- the performance problem is mainly being caused by stored procedures that update and insert the test table
5- checked sp_lock output indicates several page-level locks and a table-level X locks on the test table
6- the sql error logs report a lot of deadlocks between conflicting processes /stored procs accessing the test table
7- the table and DB also shows fragmentation
my qns are?
I am not sure that just removing the cursor and defraging the table is going to solve the problem enitrely.
Are there any other steps we can take to in this scenario
mainly to avoid the excessive deadlocks and locks , esp the X table lock and what could be causing this? the number of rows updated or selected does not seem large enough to generate so many locks?
thanks !
April 25, 2008 at 1:05 pm
- Defragging your table will help ! No doubt about it ! Less IO = better performance.
- less IO = less time needed to hold locks
- create an index that covers your join (and where if needed) clause !
That would be an index on table tbltest wich has following columns
serial_fk, test_fk,sequence, ID
The order of these columns is important !
So put the most filtering fk-column first !
put your ID column last in this couvering index because that's the one that has to be different (<>) in the join.
If you organise the room, you'll be able to find your stuff with less effort compared to the "post-it-note" references to where you've moved the stuff. (and maybe find another post-it note overthere)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2008 at 1:35 pm
Thanks. That helps ..
I know.. the <> !!! 🙁
and it would be okay to add the clustered key to a NC index?
also is there an easy and sure way to find the most selective/filtered key ?
thanks again ..
April 25, 2008 at 3:14 pm
and, after the dust has settled, and the crisis is over, make sure that an important task is to reprogram this process to eliminate the RBAR aspect of it. That will give you lasting performance improvements.
April 25, 2008 at 5:35 pm
Every row in the cursor ends up delaying the transaction. Each of those delays can add up, especially if you update lots of stuff and end up escalating from row->page->table locks.
Remove the cursor, index better, retest.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply