Wow, optimized a #query which was slow only the first time! (in MS SQL Server 2008+)
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? http://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/