I've faced a problem with SQL Server generating poor execution plans. I have 3 databases with the same structure and the same set of procedures.
My query looks like this:
select d.Id as DirectoryId, d.MeetingId as MeetingId, --... from dbo.MeetingsDirectories d left join dbo.Meetings m ON m.Id = d.MeetingId left join (select * from dbo.fnLatestDirectoryData()) dat on d.Id = dat.MeetingDirectoryId ...
The difference is
The first db cached following execution plan. Which is terrible because basically it executes
fnLatestDirectoryDataas many times as there are rows after I joined
dbo.MeetingsDirectories. My evidence is that it number of executions corresponds with the number of actual rows that come into final nested loop in upper left corner. But execution of
fnLatestDirectoryDatais way too expensive and if number of rows is ~3k it starts to lag (about 32sec)
I just recompiled procedure by altering it. And instead of doing clustered index scan it inserted table spool right before going to nested loops. I think it just multiplied results of fnLatestDirectoryData and cached them. But it's size is about 1000x1000 which worries me. Nevertheless the execution time dropped to 8sec.
In the the third case I added hash hint to left join and observed that my function called once and execution time dropped even further. I also tried adding merge hint but they both work relatively the same time.
left hash join (select * from dbo.fnLatestDirectoryData())
My questions are:
Can I prevent degradation of execution plans from second stage to first, so I always have fixed execution plan for each stored procedure? I don't have enough evidences, but I feel like sometimes SQL Server decides to recompile execution plans, and if they are bad my app performance drops significantly.
Is the third option is a right way to grantee that I would have only one execution of
Can I reliably fix the order of joins? I see joins happen in different order, so if I do something like group them in pair (
select (select from tableA join tableB) join tableC) it will actually help.