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/
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/
I've seen a similar issue with 2008 and 2008 r2, but 2012 appears to be better at nested ad-hoc queries. Haven't tried with nested views.
ReplyDeleteSimon Stuart it has nothing to do with indexes (yet) because the query itself is very fast AFTER compiling the query plan. However there is not much info about slow compiling (or preparing) querys, only about slow execution of queries
ReplyDelete