When running 12.0.1.3298 on an Intel Core i7 Q720 with Task Manager showing 8 CPUs, it is possible to run a CPU-intensive query that starts 8 separate "INT: Exchange" connections that report enormous CONNECTION_PROPERTY ( 'ApproximateCPUTime' ) values while the originating client connection reports zero:

SELECT COUNT(*) 
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSCOLUMN AS C;

On a busy server, how do I identify the culprit?

I.e., how do I trace the "INT: Exchange" connections back to the originating client connection?

...and are there any other "INT: ..." connections that behave similarly?

asked 18 Mar '12, 10:33

Breck%20Carter's gravatar image

Breck Carter
27.4k425586838
accept rate: 21%

edited 18 Mar '12, 10:34


permanent link

answered 18 Mar '12, 10:47

Breck%20Carter's gravatar image

Breck Carter
27.4k425586838
accept rate: 21%

FWIW, doing validation with v12.0.1.3554 shows a similar behaviour, i.e. running several short-living internal "INT: Exchange" connections with increasing "ApproximateCPUTime" whereas the triggering connection does not increment that value.

However, as soon as the internal connections are dropped, then their CPU times seems to get summed up for the parent connection. - During validation, this happens multiple times, as there are multiple cycles of creating, running and dropping internal "Exchange" connections.

Checked on a different connection via:

select connection_property('ApproximateCPUTime', sci.Number), *
from sa_conn_info() sci
permanent link

answered 19 Mar '12, 04:41

Volker%20Barth's gravatar image

Volker Barth
30.0k295449654
accept rate: 32%

edited 19 Mar '12, 04:42

AFAIK it's intra-query parallelism. Have you noticed that the individual numbers for INT:Exchange ApproximateCPUTime are much larger than they should be; e.g., if there are 8 of them, each number is 8 times what it should be?

(19 Mar '12, 08:06) Breck Carter
Replies hidden

Sorry, no, I haven't checked nor stored the numbers - and was just using a box with 2 CPUs, so the effect would not have been that obvious...

(19 Mar '12, 08:39) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×241

question asked: 18 Mar '12, 10:33

question was seen: 1,683 times

last updated: 19 Mar '12, 08:39