Sql-server – Indications from obscenely high Operator Costs in Query Plan

If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:

WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)

WHERE dateColumn >= '2012-01-01' 
  AND dateColumn < '2013-01-01'

it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto incremented integer column.

For InnoDB tables, the above choice means that this index will be the clustered index of the table and any range (on the date) queries on the table will use it and will only scan the relevant part of the table.

If the HasExpired condition is more complex, e.g. some rows should be marked expired after a month, some others after a year, some after a few days, I would consider splitting the table(s) into 2 parts. One having only expired data and one having all data.

So, when some data expires, you'll simply delete them from the "not_expired" table. The data will already be on the other. When you search for not expired data, you'll be searching only one table (the smallest one).
You also have the benefit of adding different indexes on the tables (for example only the above suggestion for the CI in the "all-data" table and many indexes on the not-expired table, depending on your queries.) Without the split, you would need to have an HasExpired column and attach it to all your indexes (as first column), to have equally good indexes.

If the (not-expired) data is updated many times, it might be beneficial to have the split as "not-expired" and "expired" data. So you won't need to do any updates on the "expired" table. But when some data expires, you'll simply move them (instead of deleting) from the "not_expired" table to the "expired" one.

I don't think this query is about TVPs so much as complexity. The plan has an optimizer timeout. You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml. These are not always a disaster, it just means the optimizer has run out of time (more iterations) to come up with a plan and picks the lowest cost one it has at that stage. Optimizer timeouts are often a sign of over-complexity in a query and the common recommendation is to simplify, eg break it up, remove unnecessary parameters and joins etc

Looking at your query, there are 13 tables. Looking in the plan, many of them just do a "SELECT all records" from the lookup table ( eg all records from dbo.Station for the @from and @to tables, all ticketFilter and ticketClass records ), so actually you don't need these tables and removing them does not change the result. I understand this is a user-driven query, so you might look at approach where you only join to the tables if the user has actually applied a filter.

I can reproduce the timeout, and remove it by commenting out 4 of the tables, eg:

SELECT d.Month_Year[PeriodName], qg.Title [Service_Area], AVG((CAST(sa.Answer AS smallmoney) * 10.00)) [Average]
FROM [dbo].StaticDataView d WITH (NOEXPAND)
    INNER JOIN [dbo].[Survey] s  ON (s.CustomerJourney = d.Journey_Id)
        INNER JOIN [dbo].[SurveyAnswer] sa   ON (sa.Survey = s.ID)
            INNER JOIN [dbo].[Question] q  ON (q.ID = sa.Question)
                INNER JOIN [dbo].[QuestionGroup] qg  ON (qg.ID = q.QuestionGroupID)

    --INNER JOIN @StationFrom sfl ON (sfl.ID = d.[Source])
    --INNER JOIN @StationTo stl ON (stl.ID = d.[Dest])
    INNER JOIN @InitialScores sc ON (s.Score = sc.ID)
    INNER JOIN @RouteList rl ON (rl.ID = d.Route_ID) -- route list
    --INNER JOIN @TicketClass tc ON (tc.ID = d.Class_ID)
    --INNER JOIN @TicketTypeFilter ttl ON (ttl.ID = d.Ticket_Filter_Type_ID)
    INNER JOIN @TicketDiscount td ON (td.ID = d.Discount_Type_ID)
    INNER JOIN @Days dy ON (dy.ID = d.[DayOfWeek])

WHERE s.DateCompleted IS NOT NULL AND sa.Answer != -1
AND (d.RSID = COALESCE(@RSID, d.RSID))
AND (d.[Date] BETWEEN @Start AND @End) -- between dates
AND (d.[Time] BETWEEN @StartTime AND @EndTime) -- between times
AND ((@Direction IS NULL AND d.Direction IN (0,1)) -- Both
        OR (@Direction = 1 AND d.Direction = 1) -- North
        OR (@Direction = 0 AND d.Direction = 0)) -- South
AND ((@PassengerJourney = 0 AND d.Journey_Type_ID IN (1,2,3)) -- ALL
    OR (@PassengerJourney IN (1,2) AND @PassengerJourney = d.Journey_Type_ID) -- Single or Return
    OR (@PassengerJourney = 3 AND NULLIF(d.RSID_LEG_2,'') IS NOT NULL)) -- multi part
GROUP BY d.Month_Year, qg.Title
OPTION ( RECOMPILE )

Does this make for a better plan? Hard to say, as in my simple rig, I can't get this query to run in more than half a second, with or without the timeout. Even SQLFiddle doesn't seem to struggle. Can you see much difference between the SQLFiddle repro and your setup?