SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


True or False


True or False

Author
Message
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32727 Visits: 18559
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
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 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 (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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 (20 views, 103.00 KB)
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27625 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14406 Visits: 12214
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1596 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 (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 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 (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 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 (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

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

Group: General Forum Members
Points: 2708 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