Vast difference in runtime between SSMS and Notebook query runtime when including XML data

[ConstantineK]

After reading through the issues, this may be resolved with further movement on #4123 or #4124.

• Azure Data Studio Version:
  Version: 1.5.1-insider
  Commit: 7344b41
  Date: 2019-03-07T03:38:43.893Z
  VS Code 1.30.1
  Electron: 2.0.12
  Chrome: 61.0.3163.100
  Node.js: 8.9.3
  V8: 6.1.534.41
  OS: Windows_NT x64 10.0.17134

Steps to Reproduce:

1. Run the following query in both SSMS and ADS (against a local SQL Server instance)

;with f as 
(
	SELECT 
		name, 
		target_name, 
		CAST(xet.target_data AS xml) as target_data
	FROM sys.dm_xe_session_targets AS xet
	JOIN sys.dm_xe_sessions AS xe
	   ON (xe.address = xet.event_session_address)
) 
select *
from f as f1
CROSS JOIN f AS f2

2. Note the large gap in the runtimes.

In my personal testing, the following query returned 25 rows (5x5) - took 2 seconds in SSMS, and 17 seconds in the Notebook.

When monitoring the behavior via sp_whoisactive, I noted a wait type of ASYNC_NETWORK_IO - this generally indicates the client is having an issue chewing through the result set that the SQL Server is throwing at it, and is having to wait for the client.

When adding a third cross join (CROSS JOIN f AS f3), SSMS returns 125 rows in 10s, and the Notebook took 2 minutes 25s (x12).

[chlafreniere]

Thanks so much for the detailed analysis here! We'll be looking into this soon, as notebook query perf is quite important for us 😄.

[chlafreniere]

@ConstantineK Could you please try running the same query in the query editor in ADS and let us know what the perf numbers look like?

[ConstantineK]

Sure thing @chlafreniere.

[ConstantineK]

Ran the query in the normal query editor, it ran in 17s first try and then became quite unresponsive for a significant time before rendering the results.

Subsequent runs return in "8 seconds" but make the client go nuts with regards to CPU usage, even to redraw the screen after resizing.

During this time you can see my CPU usage increase even while the client results are collapsed and really struggles to render the rows when I scroll through them.

[ConstantineK]

Watching query execution in sp_whoisactive during the ADS query window results also show ASYNC_NETWORK_IO as the wait type in question, though the unresponsive client "lagginess" was anytime dealing with the results set or redrawing the window, not just the initial query time.

[yualan]

Hi @corivera, this issue is being actively discussed in this thread: https://twitter.com/vickyharp/status/1216783392487792640

Was this issue closed because it is being tracked elsewhere?

[corivera]

We bulk closed a lot of older issues. We can revisit this one.