It’s pretty easy to determine the CPU and disk resources that a given query or stored procedure will use. It’s more difficult to determine the resources that were used to compile that query plan. You can start by looking at sys.dm_exec_cached_plans. It has a column called “size_in_bytes” that will tell you how much memory the query plan is using.
This is a companion discussion topic for the original entry at http://billg.sqlteam.com/2009/06/24/cost-to-compile-a-query/