December 12, 2013 at 10:59 am
I have a process that does 15-20 Inserts & updates of various tables. pretty simple code, no variables or #temp tables. Sometimes the job can take 4 -5 times longer than normal, for no apparent reason.
When I look at the trace file of that time period, it shows the whole process as 1 entry.
I was thinking I could put GO statements between each step, then in my trace files, I should get an entry for each Insert & Update, right ? That would help me identify which parts sometimes take much longer than normal.
Any downside to that ?
Another option I could do is write to a log table after every step, with a DateTime column which will also show me which steps took the longest.
EDIT: I think I will write to a log table between steps. Then it will be easier to look back at later.
December 13, 2013 at 3:37 am
But if you're hitting slow performance on inserts/updates, adding a log table to the whole thing is going to slow it down further.
I'd suggest capturing statement level events with extended events. You can filter it so it only captures those from the proc that's making the calls, so it shouldn't be a heavy impact on your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply