Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a TVF that when called takes 10 secs to run.

However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables.

In the Execution Plan of the TVF it is performing a table scan. How do I find out why it is doing this?

Thanks.

Query...

select Username, Identifier 
from [dbo].Unmapped(@CustID, @StartDate, @EndDate)

enter image description here

Table Scan properties...

enter image description here

share|improve this question
3  
You have a multi statement table valued function and SQL Server can not look into that and show the execution plan for the function. The scan you see here is a scan of the rows returned by the function and since you have no where clause, a scan is the best thing to do. –  Mikael Eriksson Nov 28 '13 at 10:50
1  
Here is a query you can use to see what query plan you got for your function. Just replace FunctionName with whatever your function is called. –  Mikael Eriksson Nov 28 '13 at 10:57
    
Thanks Mikael! I updated to an inline TVF and much better performance! –  k09 Nov 28 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.