Does Encryption Affect Seeing Statements in Deadlock Graphs?

SQL Server 2012
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: [crayon-5a6cbc1483058997511343/] Then I’ll execute things in the following order. From one connection this query: [crayon-5a6cbc1483063269885795/] From a second connection, my stored procedure: [crayon-5a6cbc1483067373542957/] Then, back on the first connection, this query: [crayon-5a6cbc148306b220491506/] That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: [crayon-5a6cbc148307a116859485/] Note, the statement offset is crazy, but that’s because it’s coming from an encrypted stored procedure. But the short answer is, yes you can (at least in this test).
Read More

Encryption and the Performance DMOs

SQLServerPedia Syndication, TSQL
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) AS SELECT soh.AccountNumber, sod.LineTotal 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…
Read More