Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

True or False Expand / Collapse
Author
Message
Posted Friday, November 26, 2010 8:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:18 PM
Points: 17,710, Visits: 15,578
Thanks for the question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1026796
Posted Saturday, November 27, 2010 3:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 3, 2012 5:49 AM
Points: 418, 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?
Post #1026954
Posted Saturday, November 27, 2010 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 5:06 AM
Points: 10, Visits: 57
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


  Post Attachments 
Screen.png (15 views, 103.83 KB)
Post #1026962
Posted Saturday, November 27, 2010 5:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1026964
Posted Sunday, November 28, 2010 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 PM
Points: 7,738, Visits: 9,487
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
Post #1027042
Posted Sunday, November 28, 2010 9:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
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
Post #1027120
Posted Sunday, November 28, 2010 10:45 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, August 9, 2014 11:50 PM
Points: 407, Visits: 78
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.
Post #1027140
Posted Sunday, November 28, 2010 10:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, August 9, 2014 11:50 PM
Points: 407, Visits: 78
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

Post #1027145
Posted Monday, November 29, 2010 3:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 5:06 AM
Points: 10, Visits: 57
Many thanks to all of you..
I was wrong, but now i understand..
Post #1027210
Posted Friday, December 3, 2010 1:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thanks for the question, it made me think about it some and learn something. (Though I still don't think encrypted procedures are worthwhile.)
Post #1030140
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse