Query finishes in SSMS not in PE

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)

Plan.pesession (318.7 kB)
pecsv.txt (7.0 kB)
avatar image By gbritton 93 asked Sep 16, 2016 at 07:27 PM
more ▼
(comments are locked)
avatar image Vlady Oselsky Sep 19, 2016 at 01:17 PM

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.

avatar image gbritton Sep 19, 2016 at 07:23 PM

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%

avatar image gbritton Sep 19, 2016 at 07:34 PM

Ummm how do I attach a CSV file on this forum?

avatar image Vlady Oselsky Sep 19, 2016 at 07:37 PM

Just edit your question and click paper clip.

avatar image gbritton Sep 19, 2016 at 01:20 PM

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

avatar image SQLSaurus ♦♦ Sep 19, 2016 at 03:26 PM

It is designed to handle those things. It does take some time to deal with very complicated call stacks though.

avatar image gbritton Sep 19, 2016 at 03:29 PM

I see, I did wait quite a while however (hours actually)

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x39

asked: Sep 16, 2016 at 07:27 PM

Seen: 325 times

Last Updated: Sep 19, 2016 at 07:43 PM