April 11, 2019 at 2:28 pm
As I understand it, SQL has various query execution steps, and as result become available in the result set it queues them up for network transfer back to the requesting client.
In this case, I have an ETL operation that is running.
SQL Server finishes servicing the query after 12 minutes but for another 15 minutes the client is still receiving data.
I don't see any bottlenecks being encountered, network, IO, memory, CPU.
I'm not quite able to understand why this is happening or if I can do anything to speed this up?
Any suggestions for how I should approach this? Trace I should run or what to look for exactly? Earlier in the query it was sending 500 MB /s to the client which was great, then at the end it slows to a trickle of 20 MB/s so it does not appear to be an issue where a obvious bottleneck exists. *Neither the SQL server nor the client are maxing out CPU or network or drive IO from what I'm seeing for those last 15 minutes.
Thank you for your help
April 11, 2019 at 3:55 pm
An ETL operation? What does that look like - simple query with linked servers, an SSIS package, a custom application, something else? Is it possible that some non-SQL component is caching the data?
John
April 11, 2019 at 4:51 pm
Good point, poor clarity on my part.
In this case it executes raw TSQL queries against the server. The client receiving the data is Microsoft SSAS Tabular engine.
It pulls the data out for use in Tabular data models.
I don't think there are any non-SQL components involved. Windows servers each on the network with access to each other.
They are in Azure as IaaS VMs. However, I would not anticipate any interference in that respect. The two servers ate able to transfer raw data at 5Gbs or faster in network testing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply