** Restricted Text ** when Reviewing Execution Plan in SQL Server Management Studio -

i'm reviewing execution plan see why stored procedure running slowly. in execution plan window instead of useful missing indexes text ** restricted text ** instead.

this has perked curiosity i've not seen before , can't find reference googling or indeed searching so.

could please explain telling me and, if possible, how un-restrict text - i'm guessing ssms trying tell me whatever trying tell me quite verbose , being replaced text instead.

?

there few cases client tools obfuscate query text, in different tools, depending on version:

1. the use of sp_password
2. the creation of login with password
3. the with encryption option
4. certain encryption / decryption functions

with credit @lamak, see this , try this:

create login lamak password = 'w0w, l@m@k $m@rt!'; 

depending on version, ssms either give plan ** restricted text ** in place of actual command wrote, or not give plan @ all. think modern versions don't bother exposing plans ddl because, well, why? going information? optimize create login? add index sys.server_principals? can't of these things optimize ddl.

however, when query text replaced obfuscation, shouldn't block missing indexes showing up. it's query text getting blocked out, not missing index suggestion. here proof, using favorite copy of adventureworks:

use adventureworks; go create symmetric key symkey1 algorithm = aes_256 encryption password = 'abcd1234$%^&';  open symmetric key symkey1 decryption password = 'abcd1234$%^&'; 

now, turn actual execution plan on:

select top (10) decryptbykey(creditcardapprovalcode), salesorderid, purchaseordernumber, creditcardapprovalcode sales.salesorderheader purchaseordernumber 'po147%'; 

results:

at first thought ssms obscuring text, no, in plan xml, it's sql server prevents actual text getting out. feels necessary obscure text part of batch reveals sensitive password isn't transmitted later other users (which may happen if save showplan , pass along). can inspect xml see this:

<batch>   <statements>     <stmtsimple ... statementtext="** restricted text **" ... 

you can see inputbuffer null. turn off actual execution plan, run query again, , window run:

dbcc inputbuffer(<spid first window>); 

the event info column null. see query plan not stored:

select * sys.dm_exec_query_stats s   cross apply sys.dm_exec_sql_text(s.sql_handle) t   t.text '%decrypt'+'bykey(%'; 

now, run query again, time comment out decryptbykey bit:

select top (10) --decryptbykey(creditcardapprovalcode),   salesorderid, purchaseordernumber, creditcardapprovalcode   sales.salesorderheader   purchaseordernumber 'po147%'; 

now, query against dmvs above return 1 row: 1 commented-out reference. also, dbcc inputbuffer show query ran instead of null.

---

don't forget clean up:

close symmetric key symkey1; drop symmetric key symkey1;