posts - 218, comments - 410, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Cost to Compile a Query

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.

Print | posted on Wednesday, June 24, 2009 6:49 AM | Filed Under [ SQL Server Stuff ]

Feedback

Gravatar

# re: Cost to Compile a Query

Where are you seeing DegreeOfParallelism? I can't find it in any compiled plans, whether parallel or not. (SQL Server 2008)
6/24/2009 12:36 PM | Adam Machanic
Gravatar

# re: Cost to Compile a Query

which record should be referred to know the times taken for query

I had run query first and then I run this command

Select * from sys.dm_exec_cached_plans

would it be in the same way to know or let me know how to check?
7/1/2009 12:23 AM | Rajkumar Rajput
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET