Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


True or False


True or False

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

arup chakraborty
arup chakraborty
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 365
igsri (11/26/2010)
Good Question !!..


We can see the Execution Plan of Encrypted Procedure.
I have tested it..


Hi,

Could u tell us how?
igsri
igsri
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 58
Ii have created the procedure :
CREATE PROC Test
WITH ENCRYPTION
AS
SELECT *from tbl_Test


Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.

I have attached a screen shot also
Attachments
Screen.png (15 views, 103.00 KB)
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
igsri (11/27/2010)

Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.


Yes, you see an execution plan that says you are executing a stored procedure.
But do you see the execution plan of what is inside the stored procedure? The actual steps that the sp performs?

No you don't, and that's because the sp is encrypted. If it wasn't, you'd see a lot more.
Try and create the same sp without encryption and then ask for the execution plan, you'll see.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
igsri (11/27/2010)
Ii have created the procedure :
CREATE PROC Test
WITH ENCRYPTION
AS
SELECT *from tbl_Test


Now this procedure is encrypted, but in SQL 2008 we can see it's execution plan too.

I have attached a screen shot also

The screen shot doesn't show an execution for the stored procedure: it shows the execution plan for a batch which calls the stored procedure, with the execution of teh stored procedure itself omitted (because the SP was encrypted).

Tom

Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
We are not able to see the execution plan of encrypted stored procedure, if that is the case then there is the need of encryption keyword :-)
If we use SQL Profiler and add performance -> "Showplan XML" or "Showplan text", then profiler displays "Encrypted text" when it is executing actual procedure.

Thanks
Yousaf Khan
Yousaf Khan
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 113
Referencing to the link bellow
http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

if we create procedure like
CREATE PROCEDURE #RegularSP
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Create SP with Encryption */
CREATE PROCEDURE #EncryptSP
WITH ENCRYPTION
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Execute SP - Execution Plan Tab shows up */
EXEC #RegularSP
GO

and execute the #RegularSP then it will show the execution plan
and if we execute the #EncryptSP then the execution will be disappear.
Yousaf Khan
Yousaf Khan
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 113
Referencing to the bellow link

http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

here the example

CREATE PROCEDURE #RegularSP
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Create SP with Encryption */
CREATE PROCEDURE #EncryptSP
WITH ENCRYPTION
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Execute SP - Execution Plan Tab shows up */
EXEC #RegularSP
GO

if create the stored procedure like above and execute the #RegularSP then it will show the executio plan and if we execute the #EncryptSP then the execution plan will be disappear.

Yousaf Khan
igsri
igsri
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 58
Many thanks to all of you..
I was wrong, but now i understand..
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the question, it made me think about it some and learn something. (Though I still don't think encrypted procedures are worthwhile.)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search