Rastreando o uso do procedimento armazenado

Você pode olhar no cache do plano para ter uma boa ideia do uso do Stored Procedure. Veja esta consulta, por exemplo:

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc

Isso fornecerá a você os usecountsprocedimentos armazenados armazenados em cache referentes ao SomeDB.

Nota: o cache do plano contém os planos de execução. Essa retenção desses planos tem muitos fatores envolvidos. Considerando que isso lhe dará uma boa ideia do que está sendo usado e com que frequência, definitivamente não é o total de procedimentos armazenados em execução e com que frequência/quando eles foram executados.

Referência BOL sobre o Cache do Plano

Você pode dar uma olhada nisso, bem como contém informações last_execution_timede cada procedimento armazenado.

    SELECT DB_NAME(database_id)
    ,OBJECT_NAME(object_id,database_id)
    ,cached_time
    ,last_execution_time
    ,execution_count
FROM sys.dm_exec_procedure_stats