2

I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code.

The stored procedure has all of a sudden started to run slow.

The first thing I would look to do is look at the execution plan to try to work out where the problem is, however, there doesn't seem to be a way to show the plan (sp_Whoisactive is NULL in the query_plan field)

I might also try to update statistics but I have no way of seeing what tables the stored procedure uses.

The wait stats for the query do not show anything obvious

Is it realistically possible to do any sort of troubleshooting on this query without having an un-encrypted version?

  • 2
    RE: "I have no way of seeing what tables the stored procedure uses." - can you run it and look at STATISTICS IO output to see this? Untested suggestion – Martin Smith May 24 at 15:25
  • 1
    ...or through lock info, using Extended Events or sys.dm_tran_locks... – Aaron Bertrand May 24 at 15:30
5

Notwithstanding that your question is about how to troubleshoot without decrypting the stored procedure, I would suggest that you do decyrpt the stored procedure so you can do troubleshooting as required, without guessing what's going on.

"Encrypted" stored procedures in SQL Server are actually not encrypted AT ALL. They are simply obfuscated using a pretty simple method. This means you can very easily de-obfuscate them and see the source code. Having said that you might violate the terms of the End-User-License-Agreement included with the vendor's software.

Use the details found in this question about how to de-obfuscate the "encrypted" stored procedure.

If the EULA forbids decrypting the stored proc, the best course of action would be to contact the vendor to have them do the performance troubleshooting. You do have a maintenance contract, right?

  • Thanks for this. It looks like the answer to "Is it realistically possible to do any sort of troubleshooting on this query without having an un-encrypted version?" is "not really"! Which is what I thought. I was aware about decryption but since that's not strictly "by the book" it would seem the correct way is to expect the vendor to do it. Thanks for your help – SEarle1986 May 28 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.