Grant Showplan to <user>

I was trying to review some query statistics and received the following message:

SHOWPLAN permission denied in database Test

I gave the user permission by the following command:

Grant showplan to user.

I am curious as to how much perfomance does this effect? Is there an alternative?

regards

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.|||

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

Thanks

|||

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

|||

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

|||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.