2

I wonder if ColdFusion can get the execution plan from Microsoft SQL Server? Or even the Estimated execution plan. It would be nice to get a query back of all the costs.

Phillip Senn
  • 46,771
  • 90
  • 257
  • 373

2 Answers2

3

As mentioned in the link @James posted, there are several methods you can use to retrieve the information about the execution plan using a simple cfquery. A couple things to keep in mind:

  • SET SHOWPLAN options are usually applied to the session connection meaning they may persist beyond the current request if you are using connection pooling (which is undesirable). Be sure to always disable the setting at the end of the query - even if an error occurs.

  • Some SET SHOWPLAN options return multiple resultsets. CFQuery only returns a single resultset. So it may not capture all of the data returned.

  • Most of the system views and procedures (sys.dm_exec_sql_text, etcetera ...) require elevated permissions. The datasource user must be granted special access to use these objects. So keep in mind the security implications.

...To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

UPDATE: As @Travis mentioned in the comments, SET SHOWPLAN options must be run separately. So you need separate cfquery's to toggle the setting ON|OFF before and after the main query. Here is a quick and dirty example. Note, the transaction is to ensure the same connection is used throughout. It may be overkill, but should not hurt anything.

<cftransaction>
    <cftry>
        <!--- note, SHOWPLAN_ALL does NOT execute the SQL --->
        <cfquery name="toggleStats" datasource="someDSN">
            SET SHOWPLAN_ALL ON
        </cfquery>
        <cfquery name="getQueryStats" datasource="someDSN">
            --- some sql query here 
        </cfquery>
        <cfcatch>
            <!--- rethrow and rollback automatically --->
            <cfrethrow>
        </cfcatch>
        <cffinally>
            <!--- always disable the setting --->
            <cfquery name="toggleStats" datasource="someDSN">
                SET SHOWPLAN_ALL OFF
            </cfquery>
        </cffinally>
    </cftry>
</cftransaction>

<cfif structKeyExists(variables, "getQueryStats")>
    <cfdump var="#getQueryStats#" label="Query Statistics">
</cfif>
Leigh
  • 28,765
  • 10
  • 55
  • 103
  • commands like `set showplan_all`, `set showplan_text`, `set showplan_xml` cannot be run inside a stored procedure and also run as a single command so I understand that to mean you'll also need multiple cfquery tags to set it, send the query, then another to set the setting again. It may be possible to use showplan_xml to get xml then use cf to parse it. My sql server connection isn't connecting so I can't test anything so I'm not confident enough to make that a possible answer. – genericHCU Dec 07 '12 at 18:32
  • @Travis - Yes, that is my understanding as well. (Granted, I do not use those commands outside of the Query Analyzer much.) You run separate queries to toggle the setting `on\off` before and after the main query. I would probably wrap all three in a transaction to ensure it keeps the same dsn connection. The latter may be overkill, but should not hurt anything. – Leigh Dec 07 '12 at 19:03
  • That would also be assuming that your SQL Server is on your Web server which probably isn't the case so would be a nonstarter anyway – genericHCU Dec 07 '12 at 19:33
  • *would be a nonstarter anyway* Not sure I follow. With which part? – Leigh Dec 07 '12 at 19:47
  • Getting the XML output generated by showplan_xml. I supposed it could be put in a shared folder that the CF server has access to. – genericHCU Dec 07 '12 at 22:32
  • Well it is just a string so there are plenty of options there. Though you do not have to return xml. You could use `SHOWPLAN_ALL` and dump the query text. – Leigh Dec 07 '12 at 22:39
  • This is very interesting. It has columns TOTALSUBTREECOST, LOGICALOP and PHYSICALOP. Thank you very much Leigh! – Phillip Senn Dec 08 '12 at 03:16
  • regarding the first comment by @Travis, given that this is sql server, you might be able to run all three commands inside a single cfquery block as long as you terminate the first two commands with semi colons. I've not tried it with these commands but I've run run multiple sql server commands before when testing for sql injection vulnerability. – Dan Bracuk Dec 08 '12 at 18:51
  • @Dan - Normally yes, but not with `showplan`. It must be executed separately and cannot be used inside a stored procedure. See the BOL for more details. – Leigh Dec 08 '12 at 19:25
  • @Leigh i interpreted the following as file output. Is this not the case? SET SHOWPLAN_XML returns information as a set of XML documents. Each batch after the SET SHOWPLAN_XML ON statement is reflected in the output by a single document. Each document contains the text of the statements in the batch, followed by the details of the execution steps. – genericHCU Dec 09 '12 at 01:21
  • 1
    @Travis - No, the result is just a string. [Per the docs](http://msdn.microsoft.com/en-us/library/ms187757.aspx), "return(s) output as nvarchar(max) for applications ... where the XML output is subsequently used by other tools.". It is possible SSMS incorporates some temporary files when displaying the results. But from cfquery it is just `getQueryStats["MICROSOFT SQL SERVER 2005 XML SHOWPLAN"][rowNumber]`. – Leigh Dec 09 '12 at 04:19
  • @Travis - Yeah, I have done the same thing :) BOL have a ton of information... after a while my brain starts skimming. – Leigh Dec 09 '12 at 04:40
2

This seems to cover what you are looking for

How do I obtain a Query Execution Plan?

Then just wrap it in <cfquery>

Community
  • 1
  • 1
James A Mohler
  • 11,060
  • 15
  • 46
  • 72