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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?



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

enter image description here

Table Scan properties...

enter image description here

share|improve this question
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
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


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

Browse other questions tagged or ask your own question.