Find Execution Plan For a Function

Question

Post reply

Find Execution Plan For a Function

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
December 6, 2010 at 12:32 pm

#242164

This is so strange, if i run a procedure along with execution plan enabled i can see details for each batch of script inside the procedure but why can't i look at the details when i run a function. Does execution plan show details for a function? If not how can i know which part is taking most time in my function?

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

To get the exec plan you'll need profiler. I believe it's the statistics XML event.

http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

GilaMonster (12/6/2010)
To get the exec plan you'll need profiler. I believe it's the statistics XML event.
http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

Thanks. I am able to see now but the plan or trace is not showing how much time that specific piece of code took to run. ?

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

Which events are you tracing?

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

GilaMonster (12/6/2010)
Which events are you tracing?

I have attached the script. Gail this is so frustrating, i have my trace and then i want to run index tuning advisor against the trace so that i can create some indexes( i am sure there should be few because i see scans on big tables) but the tuning advisor comes up with records saying "Statement does not reference any tables". I can see the tables in the trace, what else does DTA needs? Does it have the capability to analyze a trace file? Please advice.

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

sqldba_icon (12/6/2010)
GilaMonster (12/6/2010)
Which events are you tracing?
I have attached the script.

I'm not spending an hour or so looking up the event classes in Books online and comparing them with your script. Surely you know what events you're tracing

I can see the tables in the trace, what else does DTA needs? Does it have the capability to analyze a trace file? Please advice.

No idea, I don't use DTA cause it's a pile of junk. There's a tuning template in profiler for if you really want to use DTA. Use that template.

As an aside, if you have table-accessing scalar functions those are going to kill performance far faster than any lack of indexes

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

curious_sqldba

SSC-Dedicated

Points: 36494

December 7, 2010 at 8:41 am

#1259583

Thanks