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

Question

I'm reviewing an execution plan to see why a stored procedure is running slowly. However in the execution plan window instead of the useful Missing Indexes text I get ** Restricted Text ** instead.

This has perked my curiosity as I've not seen it before and can't find a reference to it googling or indeed searching SO.

Could someone please explain what this is telling me and, if possible, how to un-restrict the text - I'm guessing that SSMS is trying to tell me something but whatever its trying to tell me is quite verbose and being replaced with this text instead.

?

Answer 1

There are a few cases where the client tools will obfuscate the query text, in different tools, depending on version:

1. the use of sp_password
2. the creation of a login WITH PASSWORD
3. the WITH ENCRYPTION option
4. certain encryption / decryption functions

With credit to @Lamak, see this and try this:

CREATE LOGIN Lamak WITH PASSWORD = 'W0W, L@m@k is $m@rt!';

Depending on version, SSMS will either give you a plan with ** Restricted Text ** in place of the actual command you wrote, or not give you a plan at all. I think modern versions simply don't bother exposing plans for DDL because, well, why? What are you going to do with this information? Optimize CREATE LOGIN? Add an index to sys.server_principals? You can't do any of these things to optimize DDL.

However, even when the query text is replaced with this obfuscation, it shouldn't block any missing indexes from showing up. It's the query text that is getting blocked out, not the missing index suggestion. Here is proof, using your favorite copy of AdventureWorks:

USE AdventureWorks;
GO
CREATE SYMMETRIC KEY SymKey1 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'abcd1234$%^&';

OPEN SYMMETRIC KEY symKey1 DECRYPTION BY PASSWORD = 'abcd1234$%^&';

Now, turn actual execution plan on:

SELECT TOP (10) DecryptByKey(CreditCardApprovalCode),
SalesOrderID, PurchaseOrderNumber, CreditCardApprovalCode
FROM Sales.SalesOrderHeader
WHERE PurchaseOrderNumber LIKE 'PO147%';

Results:

At first I thought SSMS was obscuring the text, but no, this is in the plan XML, so it's SQL Server itself which prevents the actual text from getting out. Likely it feels it necessary to obscure the text so that the part of the batch that reveals a probably sensitive password isn't transmitted later to other users (which may happen if you save the showplan and pass it along). You can inspect the XML yourself to see this:

<Batch>
  <Statements>
    <StmtSimple ... StatementText="** Restricted Text **" ...

You can also see that INPUTBUFFER is NULL. Turn off actual execution plan, run the query again, and from another window run:

DBCC INPUTBUFFER(<spid from first window>);

The Event Info column will be NULL. You will also see that the query plan does not get stored:

SELECT * FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
  WHERE t.text LIKE '%Decrypt'+'ByKey(%';

Now, run the query again, but this time comment out the DecryptByKey bit:

SELECT TOP (10) --DecryptByKey(CreditCardApprovalCode),
  SalesOrderID, PurchaseOrderNumber, CreditCardApprovalCode
  FROM Sales.SalesOrderHeader
  WHERE PurchaseOrderNumber LIKE 'PO147%';

Now, the query against the DMVs above will return one row: the one with the commented-out reference. Also, DBCC INPUTBUFFER will show the query you ran instead of NULL.

---

Don't forget to clean up:

CLOSE SYMMETRIC KEY symKey1;
DROP SYMMETRIC KEY symKey1;