Mudanças em DMVs no SQL Server 2008 R2 SP1

Pessoal há poucas semanas atrás a Microsoft liberou o SP1 do SQL Server 2008 R2. O que muita gente não sabe é que além de correções de bugs foram introduzidas novas DMVs ao produto e algumas outras foram extendidas! Segue abaixo a listagem dessas DMVs:

1) sys.dm_exec_query_stats – teve 4 colunas adicionadas

Essa DMV é muito utilizada para análise de performance e para facilitar nesse trabalho foram adicionadas colunas para informar total/min/max/last “row counts” (número de linhas). Assim você pode separar as consultas que apenas retornam muitos registros das que realmente estão causando problemas no servidor.

Você pode usar seus scripts para analisar a média de input/output por comando, mas agora adicionando essas novas informações para ter uma visão mais completa:

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2,
	(CASE
		WHEN qs.statement_end_offset = -1
	 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
		ELSE qs.statement_end_offset
	 END - qs.statement_start_offset)/2) AS [Query Text],
qs.total_rows, qs.min_rows, qs.max_rows, qs.last_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

2) sys.dm_os_volume_stats(f.database_id, f.file_id) – foi adicionada

Essa nova DMF permite verificar o espaço livre nas partições onde os arquivos do SQL Server estão! Com ela podemos montar scripts bem interessantes em conjunto com a sys.database_files, por exemplo. Agora não há mais necessidade de utilizar a xp_fixeddrives!

Por exemplo, com o comando abaixo podemos listar todos os arquivos de todos os bancos de dados, espaço total e livre da unidade onde o arquivo se encontra, além do nome do volume e o tipo de file system que utiliza.

SELECT DB_NAME(f.database_id) AS [DatabaseName], f.file_id,
vs.volume_mount_point, vs.total_bytes, vs.available_bytes,
vs.file_system_type, vs.logical_volume_name
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
ORDER BY f.database_id;

3) sys.dm_os_windows_info – foi adicionada

Essa DMV retorna informações sobre o SO na máquina onde o SQL Server está executando. Isso ajuda você a gerar a documentação do seu servidor, por exemplo.

SELECT windows_release, windows_service_pack_level,
       windows_sku, os_language_version
FROM sys.dm_os_windows_info;

4) sys.dm_server_registry – também adicionada!

Essa DMV retorna informações do registro do windows relacionadas a configuração e instalação do SQL Server.

SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry;

Seguem exemplos de algumas informações que podem ser analisadas com essa DMV.

-Descobrir as instâncias disponíveis na máquina:

To help find out what SQL Server services are available on the host machine

  • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ObjectName
  • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath
  • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\Start

-Analisar problemas de conectividade que podem ter sido causados por má configuração:

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ AdminConnection\TCP
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Np
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Sm
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ TCP\ IP1… IPAll
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Via

5) sys.dm_os_sys_info – foi extendida.

Não encontrei informações consolidadas sobre essa, mas pelo que pude analisar aqui, foram adicionadas 8 novas colunas nessa DMV, sendo elas:

affinity_type,
affinity_type_desc,
process_kernel_time_ms,
process_user_time_ms,
time_source,
time_source_desc,
virtual_machine_type,
virtual_machine_type_desc

Veja que ai temos informações muito interessantes, como a virtual_machine_type, que informa se o SQL Server está em um ambiente físico, virtualizado com hypervisor (virtualização suportada por hardware) ou virtualizado sem hypervisor, como no virtual PC.

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
sqlserver_start_time , affinity_type_desc, virtual_machine_type_desc
FROM sys.dm_os_sys_info;

6) sys.dm_server_services – adicionada

Retorna informações sobre os serviços do SQL Server, SQL Agent e Fulltext, como a hora de inicialização do serviço, conta de serviço, se é clusterizado, o nó do cluster onde se encontra, entre outras. Pode também ajudar na documentação de seu servidor, além de análise de problemas.

SELECT servicename, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services;

7) sys.dm_server_memory_dumps – adicionada

Essa DMV retorna informações sobre memory dumps gerados por travamentos recentes.

SELECT filename, creation_time, size_in_bytes
FROM sys.dm_server_memory_dumps;

Caso queira pesquisar mais, você pode encontrar outras informações interessantes nos seguintes blogs:

Esse post foi publicado em Artigos, DMV/DMF, Virtual PASS BR. Bookmark o link permanente.

Uma resposta para Mudanças em DMVs no SQL Server 2008 R2 SP1

  1. Legal o post.. vale lembrar também que para quem utilza o SCOM esse serviçe pack ainda nao esta homologada. Entao cuidado se alguem for utilizar em instancias que possuem as databases do SCOM.

    Att.
    Marcos Freccia

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s