SQL Server Performance Forum – Threads Archive

Continue: How to speed up SQL Server performance?

Thanks for your help. here is some example of our db updating log. 1. I try to delete 1 shift, it involves some updating of 3 tables (Sometimes the user could delete thousands of shifts ,and it will all be included in one large transaction) <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRequest – Items = 52, Thread = 2064 <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 5, Size = 5 <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tartTrans() <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 57, Size = 100 <2064 04-02-20 08:36:48:049> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:049> CTableBuffer::Create() <2064 04-02-20 08:36:48:049> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘delete from schedule_depot_stats where schedule_depot_stats_id = ? and schedule_set_id = ? ‘ <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 103, Size = 200 <2064 04-02-20 08:36:48:049> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:48:049> CTableBuffer::Flush. Number of rows = 1 <2064 04-02-20 08:36:48:049> CTableBuffer::Create() <2064 04-02-20 08:36:48:049> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘update schedule_set set schedule_set_name = ? ,days_of_operation = ? ,timetable_id = ? ,crew_type_id = ? ,trip_category = ? ,is_read_only = ? ,is_actual = ? ,is_deleted = ? ,reference_count = ? ,date_mod = ? ,user_mod = ? ,date_created = ? ,single_continuous_period = ? where schedule_set_id = ? ‘ <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 29, Size = 100 <2064 04-02-20 08:36:48:049> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:049> CTableBuffer::Flush. Number of rows = 1 <2064 04-02-20 08:36:48:049> CTableBuffer::Create() <2064 04-02-20 08:36:48:049> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘delete from schedule_depot where schedule_depot_id = ? and schedule_set_id = ? ‘ <2064 04-02-20 08:36:48:049> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:48:049> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:48:049> CTableBuffer::Flush. Number of rows = 1 <2064 04-02-20 08:36:48:065> CTableBuffer::Create() <2064 04-02-20 08:36:48:065> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘update schedule_activity set schedule_id = ? ,activity_type_id = ? ,days_of_operation = ? ,start_time = ? ,end_time = ? ,start_node_id = ? ,end_node_id = ? ,non_timetabled_work_id = ? ,trip_id = ? ,trip_number = ? ,flags = ? ,direction = ? ,other_schedule_id = ? ,railway_line_id = ? ,number_of_cars = ? ,comment_ = ? ,alternative_index = ? ,other_trip_id = ? ,other_trip_number = ? where schedule_activity_id = ? and schedule_set_id = ? ‘ <2064 04-02-20 08:36:48:065> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:48:065> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:48:065> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 124, Size = 200 <2064 04-02-20 08:36:48:065> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:065> CTableBuffer::Flush. Number of rows = 3 <2064 04-02-20 08:36:48:065> CTableBuffer::Create() <2064 04-02-20 08:36:48:065> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘delete from schedule_activity where schedule_activity_id = ? and schedule_set_id = ? ‘ <2064 04-02-20 08:36:48:065> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:48:065> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:065> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:48:065> CDatabaseWr iter::ApplyRecord (D) <2064 04-02-20 08:36:48:065> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:48:080> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:080> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 90, Size = 100 <2064 04-02-20 08:36:48:080> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:080> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 100, Size = 100 <2064 04-02-20 08:36:48:080> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:080> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:48:080> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:080> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 41, Size = 100 <2064 04-02-20 08:36:48:080> CDatabaseWriter::ApplyRecord (D) <2064 04-02-20 08:36:48:080> CTableBuffer::Flush. Number of rows = 17 ========================================================================================= 2. I did undo my change, so it will insert all the data back.<2064 04-02-20 08:36:58:235> CDatabaseWriter:<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tartTrans() <2064 04-02-20 08:36:58:235> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 41, Size = 100 <2064 04-02-20 08:36:58:235> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:235> CTableBuffer::Create() <2064 04-02-20 08:36:58:235> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘insert into schedule(schedule_id,schedule_set_id,shift_type_id,schedule_number,days_of_operation,is_dummy,amendment_number,previous_amendment,is_frozen,crew_number,modified) values (?,?,?,?,?,?,?,?,?,?,?)' <2064 04-02-20 08:36:58:251> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:58:251> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:251> CTableBuffer::Flush. Number of rows = 1 <2064 04-02-20 08:36:58:266> CTableBuffer::Create() <2064 04-02-20 08:36:58:266> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘insert into schedule_activity(schedule_activity_id,schedule_set_id,schedule_id,activity_type_id,days_of_operation,start_time,end_time,start_node_id,end_node_id,non_timetabled_work_id,trip_id,trip_number,flags,direction,other_schedule_id,railway_line_id,number_of_cars,comment_,alternative_index,other_trip_id,other_trip_number) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' <2064 04-02-20 08:36:58:282> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:58:282> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:282> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:58:282> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:282> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:58:282> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:282& gt; CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 90, Size = 100 <2064 04-02-20 08:36:58:282> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:282> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:58:298> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:298> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 91, Size = 100 <2064 04-02-20 08:36:58:298> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:298> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:58:298> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:58:298> CTableBuffer::Flush. Number of rows = 17 <2064 04-02-20 08:36:58:345> CTableBuffer::Create() <2064 04-02-20 08:36:58:345> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘update schedule_activity set schedule_id = ? ,activity_type_id = ? ,days_of_operation = ? ,start_time = ? ,end_time = ? ,start_node_id = ? ,end_node_id = ? ,non_timetabled_work_id = ? ,trip_id = ? ,trip_number = ? ,flags = ? ,direction = ? ,other_schedule_id = ? ,railway_line_id = ? ,number_of_cars = ? ,comment_ = ? ,alternative_index = ? ,other_trip_id = ? ,other_trip_number = ? where schedule_activity_id = ? and schedule_set_id = ? ‘ <2064 04-02-20 08:36:58:345> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:58:345> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:58:345> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 83, Size = 100 <2064 04-02-20 08:36:58:345> CDatabaseWriter::ApplyRecord (U) <2064 04-02-20 08:36:58:345> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 29, Size = 100 <2064 04-02-20 08:36:58:345> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:345> CTableBuffer::Flush. Number of rows = 6 <2064 04-02-20 08:36:58:345> CTableBuffer::Create() <2064 04-02-20 08:36:58:345> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘insert into schedule_depot(schedule_depot_id,schedule_set_id,schedule_id,days_of_operation,crew_depot_id,is_preferred) values (?,?,?,?,?,?)' <2064 04-02-20 08:36:58:360> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 57, Size = 100 <2064 04-02-20 08:36:58:360> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:360> CTableBuffer::Flush. Number of rows = 1 CTableBuffer::Create() <2064 04-02-20 08:36:58:376> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘insert into schedule_depot_stats(schedule_depot_stats_id,schedule_set_id,schedule_id,schedule_depot_id,days_of_operation,alternative_index,paid_time,work_time,over_mileage_shift_limit_time,kilometreage,dust_allowance,is_kilometreage,sign_on_time,sign_off_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)' <2064 04-02-20 08:36:58:376> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 41, Size = 100 <2064 04-02-20 08:36:58:376> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:391> CTableBuffer::Flush. Number of rows = 1 <2064 04-02-20 08:36:58:438> CTableBuffer::Create() <2064 04-02-20 08:36:58:438> CTableBuffer:<img src=’/community/emoticons/emotion-41.gif’ alt=’:Q’ />uery = ‘insert into schedule(schedule_id,schedule_set_id,shift_type_id,schedule_number,days_of_operation,is_dummy,amendment_number,previous_amendment,is_frozen,crew_number,modified) values (?,?,?,?,?,?,?,?,?,?,?)' <2064 04-02-20 08:36:58:438> CDatabaseWriter:<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />rocessRecord – Length = 79, Size = 100 <2064 04-02-20 08:36:58:438> CDatabaseWriter::ApplyRecord (I) <2064 04-02-20 08:36:58:438> CTableBuffer::Flush. Number of rows = 1 ********************************************************************************* If I split the deleting transaction up, like begin tran begin tran 1 … commit tran 1 begin tran 2 … commit tran 2 …. commit tran If currently it running on transaction 2, it is still holding the locks from tran 1 until the last commit happens.

Have you ever checked execution plan for these update statements and how about indexes involved? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya Have you ever checked execution plan for these update statements and how about indexes involved? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for your advice. Most time the updats are done through primary key, and all the tables we set the primary keys as clustered index. We also have a scheduled task that will back up the database and check the database and index nightly. Due to our functionality, sometimes we have really large update transactions. so the locks will be hold for quite a long time. Do you know there is some way that we can split the large transaction but won’t involve in a lot of extra work for us to keep the data consistency?

Due to the clustered index on the inserted data, the performance is slow as compared.
Follow thru these links in this website for tips and tricks :
http://www.sql-server-performance.com/indexes_not_equal.asp
http://www.sql-server-performance.com/q&a116.asp HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>