Sql-server – Random performance of stored procedure across databases

Option 1 is the worst of all.. would not use that..

Option 2 a lot of administrative effort..

Option 3 (see last sentece :) )

Option 4 recently I had to think about how to secure databases.. My solution was this one. there is great article on security here. Even thought it was a bit complicated to use Certificates at beginning (never done with them anything before) I am very happy of this choice. I grant permissions to Certificate Login/User. I sign procedures by that certificate and everything works. I use the same system to grant permissions for dynamic SQL (so if there is sp_executesql I grant minimum rights to necessary tables to Certificate User, so caller of proc can not see tables and i do not have to make Execute as Owner or smthng). And the same to grant rights to use Asymmetric Keys. And I always know WHO was the caller of procedure (no Execute As), even though its not very important in my case..

Its a bit cumbersome to sign procedures during development time (trying to find good way around that). Currently I am searching procedure text (DDL trigger) for use of other database and if I find it, I print out warning, that procedure possibly has to be signed..

Another thing I miss is that its not possible to sign functions or views (there must be a good reason) so you cant use them to access other DB..

Last thing I would like to say- I would prefer to solve as many problems I possibly can with minimum variety of patterns.. So if you extensively using "Execute As" and proxy users then maybe Trustworthy option is better choice.

This will get you the top 100 with the highest average duration since the last service restart. This DMV is reset when SQL Server restarts, so if you need to go beyond that, you'll need to trace, use extended events, use auditing of some kind (built-in or manual), or invest in a 3rd party monitoring tool.

You can adjust that obviously, or go after different metrics such as physical or logical reads...

USE [Your Database Name];
GO

SELECT TOP (100)
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    type_desc,
    cached_time,
    last_execution_time,
    execution_count,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
    total_logical_reads,
    last_logical_reads,
    min_logical_reads,
    max_logical_reads,
    total_elapsed_time,
    last_elapsed_time,
    min_elapsed_time,
    max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = N'Your Database Name'
ORDER BY total_elapsed_time / execution_count DESC;