Troubleshooting a slow performing encrypted stored procedure

Question

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?

Answer 1

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?