September 19, 2012 at 1:19 pm
I am trying to debug some really old code written by someone and I'm not about to rewrite it for now.
Here's the situation - there's a job that's running that used to take about 15 minutes that all of a sudden jumped to 24 hours in the last week. Big jump.
I've isolated the issue to a linked server issue. Here's a snippet of what is happening:
Situation:
client SQL Server (we'll call it C1)
where the linked server is pointing to (we'll call the server and the linked server S1)
so on c1, there's a temp table created called #t1 on the client machine that has 1000 IDs, we'll call it Series ID
on c2, there exists one table (we'll call it Series) that is billions of rows, indexed basically only on this ID, SeriesID. Each Series ID can have hundreds of thousands of rows, each with a timestamp
so there's a query that looks something like this, when run from c1 :
select blah blah blah
from #t1 t
join S1.dbo.Series s
on t.SeriesID = s.SeriesID
and timestamp > getdate() - 1
from what I can tell from Profiler at the prepexec, it looks like it is reading through the whole S1.dbo.Series table and knows nothing about the join to the #t1 table. As such, it's reading billions of rows from the Series table.
I don't know how it worked before last week, but I guarantee you that it was not reading billions of rows. Since this is such a drastic change, it had to have been a setting or configuration change, since the code hasn't changed in a couple of years.
Any ideas?
September 19, 2012 at 1:21 pm
when I transfer the temp #t1 table to the S1 server and run everything locally on S1, the query takes about 1 minute and the number of reads is about 800K, which is what it's supposed to be.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply