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.
If you generate an XML query plan through SSMS or Profiler you can get some additional information. The XML plan includes this snippet:
<QueryPlan CachedPlanSize="196" CompileTime="53" CompileCPU="53" CompileMemory="1896">
If you review the schema for the XML query plan you can find a little bit about these values.
- CachedPlanSize is in kilobytes.
- CompileTime is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
- CompileCPU is in milliseconds (1/1000th of a second) and was introduced in SQL Server 2005 SP2.
- CompileMemory is in kilobytes and was introduced in SQL Server 2005 SP2.
There are also entries for MemoryGrant (KB) and DegreeOfParallelism but I haven’t used those as much.