Encryption and the Performance DMOs

Home / SQLServerPedia Syndication / Encryption and the Performance DMOs

Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment.

I’m going to create a simple stored procedure:

CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT)
SELECT soh.AccountNumber,
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @SalesOrderID

When I create this procedure and run it, you can see the general performance of the query being run by pulling data from the sys.dm_exec_procedure_stats DMO like this:

SELECT deps.type_desc,
dest.encrypted AS EncryptedText,
deqp.encrypted AS EncryptedPlan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) as deqp
WHERE dest.text LIKE 'CREATE PROCEDURE dbo.GetSalesDetails%'

Now, to modify the procedure so that it’s encrypted I’m going to recreate it with a slight modifcation:

CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT)

Now, if I execute the procedure and rerun the select statement against the DMO, I won’t get any data. Why? Because of the WHERE clause. The text of the procedure is no longer available in the sys.dm_exec_procedures_stats DMO. Encryption has worked. I can’t see the SQL and I can’t see the execution plan. I will however, see values in the EncryptedText and EncryptePlan columns, showing that despite the encryption, rows for the procedure in question do exist in the appropriate DMOs.

There’s the answer to the question.

EDIT: Fixed the spelling of Johan’s name. Sorry Johan!


  • Johan Bijnens - ALZDBA

    The Answer I gave was that there are encryption indicators in those DMO. I assumed it would be trivial the text isn’t published in the DMO.
    So you cannot filter on the text itself.
    However, you can get some info based on …

    SELECT deps.type_desc
    , deqp.encrypted AS EncryptedPlan
    , dest.encrypted AS EncryptedText
    , deps.*
    , dest.*
    , deqp.*
    , OBJECT_SCHEMA_NAME(deps.object_id, deps.database_id) as ObjSchema
    , OBJECT_NAME(deps.object_id, deps.database_id) as ObjName

    FROM sys.dm_exec_procedure_stats AS deps
    CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
    CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) as deqp
    where OBJECT_NAME(deps.object_id, deps.database_id) = ‘GetSalesDetails’

  • Johan Bijnens - ALZDBA

    I’m glad I could assist.
    BTW i used deqp.* to get to see all usage data. min, max and averages for worker time (cpu), elaps, reads, …

    Nice info to figure out if encrypted stuff blows up your sql instance.

  • Johan Bijnens - ALZDBA

    I just noticed dm_exec_procedure_stats is new with SQL2008.
    Here’s a version you can use with SQL2005 ( to start with ):

    SQL2005 !

    select DB_NAME(dest.[dbid]) as DbName
    , OBJECT_SCHEMA_NAME(dest.objectid, dest.[dbid]) as ObjSchema
    , OBJECT_NAME(dest.objectid, dest.[dbid]) as ObjName
    , (eqs.total_worker_time/eqs.execution_count) as total_worker_time
    , deqp.encrypted AS EncryptedPlan
    , dest.encrypted AS EncryptedText
    , eqs.*
    , dest.*
    , deqp.*

    from sys.dm_exec_query_stats eqs
    CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS dest
    CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) as deqp
    Where dest.dbid > 4
    and dest.dbid 32767

    and eqs.execution_count > 1

    Happy Tuning 😉

OK, fine, but what do you think?