0

Can Someone explain what these codes are and how they might explain why my ETL Duration times have doubled in the last few weeks?

select collationname(0x0904D00034)
sys.sp_describe_first_result_set;1

Same for these messages, but they are from the logs

The Service Broker endpoint is in disabled or stopped state

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [<Data File Name.ndf> in database id 18.  The OS file handle is 0x0000000000002380.  The offset of the latest long I/O is: 0x0000083de64000.  The duration of the long I/O is: 29818 ms.

I have researched each, but I cant find any sensible connection between these and longer data population times

user8675309
  • 49
  • 6
  • Are you getting a lot of the "I/O requests taking longer than 15 seconds" messages? That would obviously explain perf issues. The rest of the stuff doesn't look relevant - why have you picked out those SQL statements? – Martin Smith Jan 10 '23 at 21:10
  • Yes I have been getting many of the I/O statements. They seem to cover a large span of time during the moments when jobs hang, but when I first looked at the logs these same statements from a month or more ago I saw similar messages, so I disregarded them at First. Im not sure what SQL statement you are referring to. These logs are from the the primary population jobs that run throughout the evening. These messages will show up during the etl for each of our databases. I'll start digging into this issue, but can you explain briefly what this message is communicating?. FWIW, no network outage – user8675309 Jan 10 '23 at 22:20
  • Your ETL duration times have doubled. Start digging. Is it one particular package? One particular task? In a package? What does that task do? Does it have a SQL Statement? It may simply be that your ETL process is inefficient and you have reached a data volume where it is becoming obvious. To solve this you need to find one task in your SSIS package that is contributing to the performance issue and describe it. – Nick.Mc Jan 11 '23 at 00:00
  • It is not one task, step, or package. There are 3 databases that populate at three different times a day. Once those complete, 3 more databases and 2 multidimensional cubes run/process once at the end of the day. Every database doubled in population time virtually overnight. While I won't discount the possibility of inefficient queries as contributors, I don't think it is that specifically. I also looked for inefficient queries in several packages and none stood out. We were on a code freeze for a week before this started so I also don't think it is due to that. – user8675309 Jan 11 '23 at 13:42
  • You are saying that every individual task in the SSIS packages doubled in execution time, and not one individual task stood out as the outlier. Are all of your tasks data flows and sql statements? are there other types of tasks? – Nick.Mc Jan 18 '23 at 22:46
  • Also you posted an identical question here https://stackoverflow.com/questions/75030339/sql-server-ssisdb-jobs-suddenly-taking-longer-to-complete . Can you add the info about Oracle to this question, then go through one of your database load processes and compare the timings for each _task_ before and after and see if there is an outlier – Nick.Mc Jan 18 '23 at 22:48
  • @Nick.McDermaid Essentially yes, all the jobs across multiple databases including Multidimensional cube processing nearly doubled overnight. The only step that appears to have taken longer in Various DFT steps was a "pre execution phase" and sometimes a validation phase. These steps seem to take several minutes while other steps take milliseconds. Not sure if this is normal. For Oracle I have read only privilege's, and the OLTP connection we have to Oracle hasn't changed. Even duration times puling from Oracle has increased. – user8675309 Jan 20 '23 at 14:04

0 Answers0