Trying to find the compile time for a plan

Question

Trying to find the compile time for a plan

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
June 7, 2012 at 4:11 pm

#275222

I'm trying to find how long it takes for a plan to compile. Does anyone know a DMV where the information is stored?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's stored within the plans themselves. The properties of the top-most operator (the SELECT, UPDATE, INSERT, DELETE, etc) has a property CompileTime. That's the time in ms the query took to compile

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

I hate to ask, but I just don't know enough XML yet. Do you have a query that will pull the compile time out of the plan XML?

If for example I have

select *

from sys.dm_exec_cached_plans

cross apply sys.dm_exec_query_plan(plan_handle)

How do I get *,compile_time ??

Thanks

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Off hand, no. Google might turn one up. Otherwise I'll see if I can run one up later or over the weekend

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Great, I appreciate it. I'll see if I can find/create one today and post it if I can. XML is one of those things I plan on learning, just havn't gotten there yet.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It's not that bad, but I do not recommend learning on a query plan. The schema is weird to say the least.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

The compile time is a specific work problem. I'm trying to prove to my boss that turning on OPTIMIZE FOR ADHOC WORKLOADS is a good idea.

I have the query:

SELECT cacheobjtype, CASE WHEN usecounts = 1 THEN 1 ELSE 2 END AS uses,

sum(size_in_bytes)/1024.0/1024.0 AS Size_In_MG, count(1) AS Plans_in_Cache

FROM sys.dm_exec_cached_plans

GROUP BY CASE WHEN usecounts = 1 THEN 1 ELSE 2 END, cacheobjtype

ORDER BY cacheobjtype, CASE WHEN usecounts = 1 THEN 1 ELSE 2 END

to show how many queries are adhoc, and how many aren't. I need the compile time to prove that the extra compile time for the ones that aren't adhoc is worth it.

Learning XML is going to be for my MCTS in development. I'll be doing that on my simpler fare 🙂

Thanks again

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

Something like this:

;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT * ,

query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileTime)[1]','int')

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_query_plan(plan_handle) ;

You might want to Cross Apply query_plan.nodes() down to the Statements level, if you have multiple statements in your batch. Depends on the queries you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 8

I found this link usefull when building a report against a Sharepoint list, which is xml.

Outlines keywords for select, which are a bit different.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

Ok, so now I'm having a really bazaar error. I'm trying to use the CROSS APPLY to get the query plan and for some reason I'm getting an error about query hints. This is only happening on my 2008 servers. My 2005 servers seems to be doing working just fine.

I've simplified the code to make sure I'm not doing something wrong and I'm using the same code in each case.

SELECT *

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_query_plan(plan_handle) ;

I absolutly HATE these kinds of errors.

Thanks

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You're running it in a database with compat mode set to 80.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 11

Thanks again for all of the help. Here is my final query. There are probably better ways to do it but this appears to get what I need.

;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT cacheobjtype, CASE WHEN usecounts = 1 THEN 1 ELSE 2 END AS uses,

sum(size_in_bytes)/1024.0/1024.0 AS Size_In_MG,

count(1) AS Plans_in_Cache,

SUM(x.Compile_Time)/1000.0 AS CompileTime_In_Sec

FROM sys.dm_exec_cached_plans

CROSS APPLY (SELECT SUM(n.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileTime)[1]','int')) AS Compile_Time

FROM sys.dm_exec_query_plan(plan_handle)

CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)

) x

GROUP BY CASE WHEN usecounts = 1 THEN 1 ELSE 2 END, cacheobjtype

ORDER BY cacheobjtype, CASE WHEN usecounts = 1 THEN 1 ELSE 2 END

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Just one nitpick... Lose the ;. CTEs don't start with statement terminators.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass