Você já deve ter ouvido falar nas DMV, mas voce já utilizou alguma?
Primeiramente, DMV é o nome mais comum para views (Dynamic Management Views) e functions (Dynamic Management Functions). DMVs são metadados que ficam armazenadas no schema sys, iniciam com sys.dm_* e permitem explorar o comportamento interno do SQL Server com comando SELECT.
As DMVs foram implementadas no SQL Server 2005 e hoje já são popularmente conhecidas, mas eu ainda vejo algumas pessoas lutarem contra elas e continuarem utilizando formas antigas de recuperar informações internas do SQL Server, por exemplo: Utilizar a stored procedure de sistema SP_WHO ou SP_WHO2 para trazer informações do que esta acontecendo e quais processos estão sendo executados na instancia.
Essas procedures de sistemas são uteis e trazem algumas informações que você com certeza pode identificar o que está acontecendo em seu ambiente porem, na minha visão, elas tem limitações extremas e trazem poucas informações.
Com as DMVs você pode ser mais especifico em procurar um problema ou informação, não dependendo de um conjunto de resultado pré-estabelecido que não pode ser modificado.
As DMVs contem muitas informações adicionais que não fazem parte de procedures de sistemas e com detalhes riquíssimos onde podemos cruzar informações entre varias DMVs e obter um diagnostico preciso sobre o que você procura.
Em uma breve conversa com alguns DBAs da minha equipe, percebi que o fato de que alguns deles não usarem as DMVs é por falta de conhecimento e por “preguiça”… Sim a comodidade de escrever poucas palavras e não escrever um SELECT é o principal ponto que encontrei…
Mas vamos lá… Por que eu devo utilizar as DMVs?
Na minha opinião é pelo fato delas conterem muitas informações relevantes para N tipos de situações, seja uma simples consulta de propriedades até um diagnostico completo de problemas. Com as DMVs a facilidade de informação está ao nosso lado, então porque desperdiçar?
Conhecer todas as DMVs é um desafio, mas basta começar à conhece-las que você irá se acostumar facilmente e isso acontecerá porque as DMVs estão categorizadas em grupos específicos baseados nas áreas que a informação é exposta.
Algumas dessas áreas são:
dm_exec_*
Nessa sessão você tem informação relacionada a execução de código e conexão associada. Por exemplo: sys.dm_exec_session voce tem informação sobre as sessões existentes na instancia.
Aqui vale a pena um atenção especial! Na minha opinião essa sessão deve ser bem explorada pois tudo o que esta sendo executado na sua instancia pode ser encontrado aqui.
dm_db_*
Nessa sessão você tem informação sobre detalhes do database e seus objetos. Por exemplo: sys.dm_db_missing_index_details você tem informações detalhadas sobre índices ausentes
dm_io_*
Nessa sessão voce tem informação sobre atividade e I/O. Por exemplo: sys.dm_io_pending_io_requests retorna uma linha para cada solicitação de I/O pendente.
dm_os_*
Nessa sessão você tem informação em baixo nível do sistema. Por exemplo: sys.dm_os_schedulers que retorna uma linha para cada scheduler.
dm_tran_*
Nesse sessão você tem informação sobre transações corrente. Por exemplo: sys.dm_tran_active_transactions você tem informação sobre transações da instância.
E assim por diante!! Para obter mais informações sobre as DMVs acesse o link: http://technet.microsoft.com/en-us/library/ms188754.aspx
Complemento:
A Microsoft SQL Server DMV/DMF Cheat-Sheet
SQL Server 2008 System Views poster is now available for download!
O link abaixo achei super interessante, pois monstra uma forma diferente de visualizar as DMVs!!!
The SQL Server 2012 Periodic Table of Dynamic Management Objects