Wow, optimized a #query which was slow only the first time! (in MS SQL Server 2008+)
After lots of searching I found at least a query to find #slow #compile queries
(with high cpu and memory usage):
http://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/

Together with a "clear all cache" sql I could reproduce and optimize the slow query:
DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; DBCC FREESESSIONCACHE; DBCC FREESYSTEMCACHE ('ALL');

It turns out that you should not use (many) "view in view in view in view" :)
After combining all views into one big sql (with sub select in sub select in sub join etc) I got from "10s + 81Mb" to "1.2s + 17Mb" compile time and memory usage! 
Wow, is Sql Server so in-efficient in compiling subsubsubsub views? 
Shared publiclyView activity