Developing Matt

My Technical Journal

Don’t stop the showplan

leave a comment »

I’m reading chapter 2 of “Inside Microsoft SQL server 2005: T-SQL querying” by itzik ben gan. 

Setting the different types of showplans on before you run your query gives you a little bit of guidance in showing you how well formed your query and/or structure.  Here are a few types (and don’t forget to turn it off when through):

SET SHOWPLAN_TEXT ON is text only and gives you just the main operations.

SET SHOWPLAN_ALL ON is also text only and gives you more.  I like that it returns you a table of information with row estimates, cpu estimates, total cost. 

SET SHOWPLAN_XML ON contains an xml link to a graphical estimated plan as a single batch.

SET STASTICS XML ON is produced at run-time which is a bonus, but it also separates the batches into different documents.  It is also an xml link to a graphical plan.

Using the xml (versus text) has a few advantages

  1. Shows the size of the plan (cachedPlanSize)
  2. Shows parameter values for how the plan was optimized (ParameterList)
  3. Shows the number of rows processed (actualRows)
  4. Shows the parallelism (degreeOfParallelism attribute)
  5. Using the xml link is nice because you can just copy the link into notepad, save it as a sqlplan file and send it over email, etc. (no need to connect as long as they have ssms or some other application that can read xml files)

SET STATISTICS POFILE ON is similar to SET SHOPLAN_ALL ON except that it is active during the execution and is produced after the execution is complete.  This gives you  two new outputs that give you thread execution information: Rows (the sum of the rowcount attribute from the RunTimeCountersPerThread)…and Executes (the sum of actual executions from the RunTimeCountersPerThread attribute).  Here is where we can find a potential problem: by examining the difference between the estimates….the real number of executes you see here…and the actual number of returned rows.  (the problem with this is that the estimates is based upon per estimated execution, so we have to multiply the EstimateRows by EstimateExecutions and compare the results with the number in the Rows column – see page 55 to clear any confusion up on this)


Written by matt

December 29, 2008 at 1:40 pm

Posted in Sql Server, T-Sql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: