Architecture question.
I have ServerA.TableA (5 million rows) and ServerB.TableB (26 million rows)
I want to:
Select
ServerA..TableA.Column1, ServerB..TableB.Column2
From
ServerA..TableA, ServerB.dbo.TableB
Where
ServerA..TableA.Column1 = ServerB..TableB.Column1
AND
ServerB..TableB.DateColumn
between '2007-09-19' and getdate()
The query works and takes 5 minutes. Too long
Issues:
There is no index on the relevant date column from the Where clause
Join is done in the WHERE instead of the FROM
I inherited this
The question:
How does SQL Server process these rows in memory? Does it pull all 26 million rows into ServerA's memory and then join them? What part of memory (mem-to-leave, BPool)? Does it table scan for the date on Server B and then bring those rows back to Server A for joining?
I'm not looking for query optimization so much as I am looking for how SQL Server processes the rows in a linked server situation where the LS is also SQL Server. I know it does strage things with non-SQL Server data in mem-to-leave.
If you can find some documentation on this that you can link to, that's would be really helpful as well.
Start Free Trial
