Para otimizar a performance o importante é detectar os gargalos, é só com DMV é possível enxergar tudo o que está acontecendo no SQL em tempo real.

Dynamic Management Views (DMV e DMF)

Principais rotinas para visualizar as Consultas Gerenciais

• select * FROM sys.dm_exec_connections
• select * FROM sys.dm_exec_sessions
• select * FROM sys.dm_exec_requests
• select * FROM sys.dm_os_wait_stats
• select * FROM sys.dm_os_waiting_tasks

Extended Store Procedures serão descontinuadas
Veja mais: http://blogs.technet.com/b/comunidadesql/