Query finishes in SSMS not in PE

gbritton 2016-09-16 19:27:33

link textI have a query (which callas a proc) that finishes in about 6 min in SSMS but doesn't finish in PE. I'm running Profiler at the same time. The last thing I see in Profiler from PE is:

DECLARE
@PortfoliofeedCode varchar(50) = 'CADemandWealth'
, @JobID UNIQUEIDENTIFIER = '612E9169-6FAE-4DF2-BC37-1C5F27B58182'
, @ProcessDate DATE = '2016-06-30'
, @UseEventsToDetermineChanges BIT = 0
, @Debug BIT = 1
, @NoExecute BIT = 0
exec [Cert].[UpdateFromStaging] @ProcessDate = '2016-06-30', @PortfolioFeedCode = @PortfoliofeedCode, @UseEventsToDetermineChanges = 0, @JobID = @JobID

By alredy twice as much time has gone by. The row counts are steadily increasing (beyond believable numbers) as I write this.

I just uploaded a csv file as pe.csv.txt. The forum won't let me upload a csv (invalid file type message)

/storage/attachments/1722-Plan.pesession
/storage/attachments/1731-pecsv.txt
Vlady Oselsky 2016-09-19 13:17:31
Do you have scalar functions or nested sp calls? Something similar happened to my friend. His PC showed high RAM and CPU usage, while PE was painting all the rows. A long time ago, I ran into the same issue in Pro version but it worked fine in the Free version. Check for scalar calls and if you by a chance have an old Free version, try it there.
gbritton 2016-09-19 19:23:24
More info on this. I started Profiler and ran the query in SSMS. Run time about 3 min. IOs counts 7854598+ (but less than 8M). With profiler still running, ran it again in PE (just copy/paste from SSMS in a new tab and click get actual results). At 3 min, I/O counts > 10 M. This never finishes. Profiler does not record the completed batch either.

This is consistently reproducible. I can even run the batch to the end minus the last query. When it hits the last query — trouble

I ran sp_whoisactive with things like this. I'm attaching a CSV file with the relevant info. Note that none of the four queries shown is from the query I'm trying to test.

OH, btw while this is going on my NIC is pinned at 100%

gbritton 2016-09-19 19:34:42
Ummm how do I attach a CSV file on this forum?
Vlady Oselsky 2016-09-19 19:37:37
Just edit your question and click paper clip.
gbritton 2016-09-19 13:20:56
Sure do, the code is a monster. Lots of nested calls. lots of scalar functions. Are you saying that PE isn't designed to support this kind of thing? Pity.

PS I don't have an old Free version. This is my first time using PE

SQLSaurus 2016-09-19 15:26:19
It is designed to handle those things. It does take some time to deal with very complicated call stacks though.
gbritton 2016-09-19 15:29:57
I see, I did wait quite a while however (hours actually)
September 16, 2016
No Comments