As Thom said, if you could give us sample data that recreates the issue that would be ideal. 

Also, what is the exact query you ran?

Cheers!

https://www.sqlservercentral.com/Forums/1889627/Ranking-for-each-group is the duplicate. I'd post any replies there instead of here.

Cheers!

Here's one way (I have a nagging feeling this can be simplified, but it's getting a bit late in the day so I'm not sure how much more thought I'll...

Something like this should do the trick to create the missing #inv rows, if I understand your request correctly: 

SELECT  i.inv_id,p.name,p.prod
FROM  #prod p INNER JOIN #inv...

I'll have to actually run through your example to give exact numbers for this exact data, but I can make a quick general point.

With a plan that uses...

The statistics for a user object are physically stored in the relevant user database's files. See https://dba.stackexchange.com/questions/27151/where-are-statistics-physically-stored-in-sql-server, for example.

Cheers!

Ah, nice catch, Jason. 

Yeah, there are a couple different functions in that query (in addition to the one you pointed out, there's a reference to dbo.ufn_IsWithSLA). Since the...

That's not surprising. 

The sort is for the results of the merge join between tbSPT and tbTechnical_Details, which is on spt_ref_no=[Opportunity Reference]. That output will be ordered by the...

Again, all of these attempted changes are blind guesses until you know why the query takes the time it does.

You need to collect information about that session when...

Ok, this is what I was looking for.

A few questions.

Still no real detail. 🙂 Unfortunately, to us "quits working correctly" is no better than just saying there's "odd behavior".

What exactly do you mean?Is the problem...

This sort of thing occurs because the memory granted was not enough, typically because the estimated rows are drastically inaccurate.

This exactly happens in your query plan. The sort...

One quick note: you're using TOP 1 without an ORDER BY, so if you have multiple values for rn that meet your criteria, there's no guarantee you'll get the same...

PAGEIOLATCH_* waits occur when a page has to be fetched from disk into the buffer pool.

PAGELATCH_* waits occur for data file pages in memory, as Perry said.