sql - View Clustered Index Seek over 0.5 million rows takes 7 minutes -

take @ execution plan: http://sdrv.ms/1aglg7k
it’s not estimated, it’s actual. actual execution took 30 minutes.

select second statement (takes 47.8% of total execution time – 15 minutes).
@ top operation in statement – view clustered index seek on _security_tuple4. operation costs 51.2% of statement – 7 minutes.

the view contains 0.5m rows (for reference, log2(0.5m) ~= 19 – mere 19 steps given index tree node size two, in reality higher).
result of operator 0 rows (doesn’t match estimate, never mind now).
actual executions – zero.

so question is: how bleep take 7 minutes?! (and of course, how fix it?)

---

edit: some clarification on i'm asking here.
not interested in general performance-related advice, such "look @ indexes", "look @ sizes", "parameter sniffing", "different execution plans different data", etc.
know already, can kind of analysis myself.

what need know what cause 1 particular clustered index seek slow, , what speed up.

not whole query.
not part of query.
1 particular index seek.
end edit

---

also note how second , third expensive operations seeks on _security_tuple3 , _security_tuple2 respectively, , take 7.5% , 3.7% of time. meanwhile, _security_tuple3 contains 2.8m rows, 6 times of _security_tuple4.

also, background:

1. this database project misbehaves. there couple dozen other databases of same schema, none of them exhibit problem.
2. the first time problem discovered, turned out indexes 99% fragmented. rebuilding indexes did speed up, not significantly: whole query took 45 minutes before rebuild , 30 minutes after.
3. while playing database, have noticed simple queries “select count(*) _security_tuple4” take several minutes. wtf?!
4. however, took several minutes on first run, , after instant.
5. the problem not connected particular server, neither particular sql server instance: if database , restore on computer, behavior remains same.

first i'd point out little misconception here: although delete statement said take 48% of entire execution, not have mean takes 48% of time needed; in fact, 51% assigned inside part of query plan should not interpreted taking 'half of time' of entire operation!

anyway, going remark takes couple of minutes count(*) of table 'the first time' i'm inclined have io issue related said table/view. don't materialized views have no real experience them , how behave internally suggest fragmentation causing toll on underlying storage system. reason works fast second time because it's faster access pages cache when fetching them disk, when all on place. (are there (max) fields in view ?)

anyway, find out taking long i'd suggest rather take code out of trigger it's in, 'fake' inserted , deleted table , try running queries again adding times-stamps and/or using program sql sentry plan explorer see how long each part takes (it has duration column when run script within program). might you're looking @ wrong part; experience shows cost , actual execution times not related we'd think.