Grant Showplan to <user>

Grant Showplan to <user>

• Segua

  Yes. Those features run one of the SET SHOWPLAN command underneath the covers. See the BOL link below for more details on the permissions:

   

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c18657e-2992-4ee7-ab07-7af3141ec658.htm

   

  The online version of BOL links is:

   

  http://msdn2.microsoft.com/en-us/ms189602(SQL.90).aspx

   

  
  
  

• lingling

  Does it have anything to do with "Display estimated execution plan" or "Include actual execution plan" in query analyzer

  Thanks

  
  

• SergeiU

  Thanks for your prompt reply.

  Just one last question.

  If i have a query that takes about 60 minutes to run and i have the "Show actual estimation plan" selected...that would not effect the server perrformance or clog it up as compare to if i just run the query

  Thanks

  
  

• U?ur Gumu?han

  The SHOWPLAN permission only governs who can run the various SET SHOWPLAN statements. It doesn't impact performance of the server per se. And with some of the SHOWPLAN statement in effect, the statement(s) is not executed and goes through compilation phase only. There is no other alternative though other than granting the required permission to the user.
  
  

• MissBook

  The actual estimation plan uses "SET STATISTICS XML ON" in SQL Server 2005 and "SET STATISTICS PROFILE ON" in older versions of SQL Server. It will not affect the query performance drastically. You may want to just use SET STATISTICS TIME ON first to see the compilation and execution times. That will give an idea as to where the chunk of query execution time is being spent. You can then use the actual plan output to look for missing indexes, scans etc.
  
  
• Grant Showplan to <user>