Frankly, when I want to unit test a stored procedure in an environment where the data, system specs, and workload as as close as possible to actual production, there have been (some) occasions where I tested it in... production. Well it's sort of in production. OK, I don't unit test this way with procedures which modify data, and I'm not talking about deploying a development version of a procedure on top of an existing production procedure. I'm simply talking about deploying a development version of a stored procedure to production in a special schema called UnitTest, and then intentionally execute it during normal business hours to confirm it's performing as expected and doesn't cause blocking issues. I'll also run the unit test under the context of an account with permissions limited only to executing procedures within the UnitTest schema. I'm not saying (when) or (where) I've done this, only that I've done it in the past.

This is more secure than copying data from production to a development or test environment, and performance tuning a stored procedure in an isolated environment is not a true test of how it will perform in production.