Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wrightgj
Starting Member
8 Posts |
 Posted - 2011-04-12 : 05:29:43
|
| Hi all,First post here so Hi to everyone.I have an issue with a SQL 2008 database whereby an INSERT statement that is being committed is taking approximately 2-3 seconds to Commit rather than the usual milliseconds.I have run profiler and notice that the INSERT SQL is executed as normal, but the COMMIT TRAN is the batch that is taking the time.I believe this can be due to the transaction log, but I have checked the log and it is only 22% full.The INSERT also causes a trigger to run that will insert the same row into a mirror image 'copy' backup table.Any help on where to start looking would be much appreciated. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-04-13 : 13:12:40
|
| could you try getting rid of triggers and use inserted.field_name into a temp table or table variable and then insert into your audit table from temp table or table vardeclare @audit_table_name table(action nvarchar(12), other_fields_data_type)insert into mytableOUTPUT $action, inserted.id, inserted.first_name,frominsert into auditfrom @audit_table_name table variables can hurt you if your data is huge.If you don't have the passion to help people, you have no passion |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-04-26 : 06:48:48
|
| Thanks for the replies. I will try perfmon and see what I get |
|
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-05-03 : 11:48:04
|
| Hi guys,I have an update.It looks like my wait is due to an excessive PAGEIOLATCH_EX wait (around 2500 ms).I ran the SQL Nexus trace and analysed it in the utility.As well as this Latch wait, I get a WRITELOG wait of approximately 600ms at the end of the COMMIT.I've googled around and people suggest I have a disk problem. If this is the case, why does it only happen on certain types of transactions? The ones I can reproduce the problem on are not very large and do not contain much data.The only thing I can think of is that between SQL 2000 and 2008, there have been major optimisation changes thast have ruined performance on the process we are performing.Currently we have 2 HDD. On Hdd 1, there is a db1.table1 that has an insert trigger on it. When this trigger is fired, the record is also inserted to a copy of the database db2.table1 which is on a different HDD.Could this be causing the excessive delays?regards, |
|
|
|
wrightgj
Starting Member
8 Posts |
Posted - 2011-05-11 : 06:59:39
|
| Ok finally got to the bottom of this and it may help others in future...The problem was that the 'copy' database and log file were located on a slave disk that, by manufacturer default, would go into standby after 30 seconds inactivity. This meant when the log was updated for the first time after each standby, it took approximately 2.5 seconds to 'wake up' again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Issues with slow COMMIT times on new SQL2008 db