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/