February 4, 2009 at 2:38 pm
Hi,
Is there a way to find out the time spent by SQL server 2005 to build execution plan for a particular query ?
THanks
February 4, 2009 at 2:45 pm
Kind of difficult, but you can try using this trick. (I am not sure if it is accurate or not)
Use the profiler to check for Compile/recompile for the SPID from which you are running the SQL Query. Just out of curiosity, why are you trying to find the time take for creating a execution plan?
-Roy
February 4, 2009 at 2:58 pm
If you use "set statistics time on", it will give you the parse and compile time. That's pretty much the time spent reading the query and building an execution plan for it.
- 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
February 4, 2009 at 2:59 pm
The reason for this is we have very complex query with huge Where clause. What I suspect is that SQL Server spends a lot more time to build execution plan rather than execution time itself. When I comment out whole Where clause the execution time is much faster (and of course, execution plan is much simpler).
In order to prove it I need some metrics for this. I will try profiler, but I thought that there is some way to do it in DMV's or DBCC's.
February 4, 2009 at 3:00 pm
Thanks, GSquare, I will try this too.
February 4, 2009 at 3:17 pm
The stats thing will show parse and compile time vs execution time. Should give you what you need.
Most likely, it's not the compile time, it's IO differences or computation time because of the Where clause.
Is it possible for you to post the table definitions, sample inserts, and the query and its execution plan? Might be able to help speed it up.
- 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
February 5, 2009 at 5:55 am
It's possible for the compile time to be outrageous. We had an 80 table JOIN, plus an extensive WHERE clause that took 45 seconds to compile but only about 1.5 seconds to run. The nature of the data was such that changes in statistics caused a recompile about every 12-18 minutes which blocked all access for about 45 seconds while the proc recompiled. That was a fun one to troubleshoot.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2009 at 6:52 am
Grant Fritchey (2/5/2009)
It's possible for the compile time to be outrageous. We had an 80 table JOIN, plus an extensive WHERE clause that took 45 seconds to compile but only about 1.5 seconds to run. The nature of the data was such that changes in statistics caused a recompile about every 12-18 minutes which blocked all access for about 45 seconds while the proc recompiled. That was a fun one to troubleshoot.
It can definitely happen. I had one where I went "I'm going to make this into a single, set-based command," and ended up with about a dozen derived tables nested five or six layers deep, and so on. Took f-o-r-e-v-e-r to compile, and even longer to run. Broke it down into temp tables with one step in each one and one final join, went from about a 10-minute compile and run down to sub-second. Still set-based in each step, but broke it all down to make it easier on the server.
But most of the time, it's something that can be handled by some simple re-writes.
- 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
February 6, 2009 at 9:42 am
Grant Fritchey (2/5/2009)
It's possible for the compile time to be outrageous. We had an 80 table JOIN, plus an extensive WHERE clause that took 45 seconds to compile but only about 1.5 seconds to run. The nature of the data was such that changes in statistics caused a recompile about every 12-18 minutes which blocked all access for about 45 seconds while the proc recompiled. That was a fun one to troubleshoot.
I have seen this for massively partitioned tables as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2009 at 11:20 am
TheSQLGuru (2/6/2009)
I have seen this for massively partitioned tables as well.
Really. When did it occur? When the query had to hit lots of the partitions or is it just a danger in general as you scale out partitioning?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 6, 2009 at 11:44 am
Grant Fritchey (2/6/2009)
TheSQLGuru (2/6/2009)
I have seen this for massively partitioned tables as well.Really. When did it occur? When the query had to hit lots of the partitions or is it just a danger in general as you scale out partitioning?
Just the compile for queries involving variables for the where clause on the partitioning column. Hundreds of partitions were involved, and it took a long time to chew up a plan and spit it out. Hard-coded values (aka dynamic sql) seemed to speed up plan creation and got the right partition eliminations as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply