SQL Server: Minimizar a E/S no disco
O ajuste e a indexação de consulta é uma forma eficiente de reduzir E/S física e lógica no disco.
Extraído de "SQL Server DMV Starter Pack" publicado pela Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Há uma persistente necessidade de minimizar/s lógico e físico. A coleção de objetos de gerenciamento de banco de dados relacionados a I/O (DMOs) ajuda a investigar, especificamente, física I/O, tendo lugar no seu sistema, quando dados são gravados e de leitura do disco.
DMOs nesta categoria fornecem uma imagem explícita de e/s de disco do ponto de vista do subsistema de disco. Eles nos mostram, por exemplo, como o i / o é distribuída entre vários arquivos no disco, lugares onde a e/s está tornando-se um gargalo e resultando em barracas de I/O e assim por diante. Você pode usar essas informações para otimizar a arquitetura de subsistema de disco. Você também pode coletar dados e usá-lo para oferecer suporte a solicitações para líderes da unidade de negócios de mais capacidade de armazenamento.
I/naturalmente, alguns físicos o é inevitável. SQL Server deve gravar dados do aplicativo ao disco. Ele também tem de gravar o log de transação para cada insert, update e delete e até mesmo para operações em massa. No entanto, antes de saltar para a conclusão de que você simplesmente precisa de mais poder de disco, lembra que há que muito você pode fazer em termos de consulta ajuste e indexação para minimizar/s lógico e físico desnecessário.
Você deve considerar as informações de I/O derivadas da DMOs coberto aqui (todos que começam com "sys.dm_io_"), bem como dados de outras exibições de gerenciamento dinâmico (DMVs) que fazem referência o desempenho de e/s de alguma maneira, incluindo:
- DM exec_query_stats – i/o que uma determinada consulta tem o custo sobre as vezes que tiver sido executada
- DM exec_connections – i/o que tem ocorrido nessa conexão
- DM exec_sessions – i/o que tomou lugar durante a sessão
- DM os_workers-i/o pendentes para um thread de trabalho determinado
Todas as consultas nesta seção trabalham com SQL Server 2005, 2008 e 2008 R2, e todos exigem a permissão View Server State.
Investigar os afunilamentos de disco através de bancas de e/s
O DMV que usaremos aqui é DM io_virtual_file_stats, que descreve Manuais Online do SQL Server, como: "Retorna estatísticas de I/O de dados e arquivos de log. Essa exibição de gerenciamento dinâmico substitui a função fn_virtualfilestats."
Esta DMV aceita dois argumentos: database_id e file_id. Você pode especificar NULL para qualquer um. Nesse caso, ele retornará informações sobre todos os bancos de dados ou todos os arquivos.
Observe que essa DMV é acumulativa. Em outras palavras, os valores nas colunas de dados continuamente incrementam do ponto quando o servidor foi reiniciado última. Isso significa que você precisa tomar uma medida de linha de base, seguida-se a medida real. Em seguida, Subtrai os dois, para que você possa ver onde está acumulando I/O.
Este script permite que você veja o número de leituras e escreve em cada arquivo de dados e de log para cada banco de dados executando em uma instância do SQL Server. É classificada por tempo médio de tenda I/O, em milissegundos:
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
Esta consulta irá mostrar os arquivos que espera a mais longa de e/s de disco. Pode ajudá-lo a decidir onde localizar arquivos individuais com base em seus recursos de disco disponível. Você também pode usar para ajudar a convencer alguém como um SAN engenheiro que SQL Server está vendo disco gargalo para determinados arquivos.
Investigar os afunilamentos de disco através de e/s pendentes
Isso leva a uma abordagem ligeiramente diferente para investigar os afunilamentos de disco i/o. Use o sys.dm_io_pending_io_requests DMV, que descreve Manuais Online do SQL Server, como: "Retorna uma linha para cada solicitação de e/s pendente no SQL Server."
Os dados no DMV fornecem um instantâneo de "momento" de solicitações de e/s pendentes em seu sistema, logo no momento em que você executar o script:
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
Porque esses dados representam um instantâneo no momento da atividade, você vai querer executar esta consulta várias vezes para ver se os mesmos arquivos (e as mesmas letras de unidade) mostram-se consistentemente no topo da lista. Se isso acontecer, é evidência de estrangulamentos de I/O para essa letra de unidade ou arquivo particular. Você pode usar isso para ajudar a convencer a SAN engenharia do sistema estava com problemas de I/O para um determinado LUN.
As duas últimas colunas na consulta de retorno o número cumulativo de leituras e gravações para o arquivo desde que o SQL Server foi iniciado (ou desde que o arquivo foi criado — que era mais curto). Esta informação é útil ao tentar decidir qual nível RAID para uma letra de unidade específica. Por exemplo, arquivos com mais atividade de gravação terá geralmente melhor desempenho em um RAID 10 LUN que eles vão em um RAID 5 LUNs.
Conhecendo a proporção relativa de leitura/gravação para cada arquivo pode ajudá-lo a colocar seus arquivos de banco de dados em um LUN apropriado. Este, por sua vez, irá ajudá-lo ajustar suas consultas para uma maior eficiência.
.png)
.png)
.png)
Glenn Berry trabalha como arquiteto de banco de dados NewsGator Technologies em Denver, Colorado Ele é um MVP do SQL Server e tem um conjunto de certificações da Microsoft, incluindo o MCITP, MCDBA e MCSE, MCSD, MCAD, MCTS, que prova que ele gosta de fazer testes.
Louis Davidson tem sido no setor de TI há 16 anos como arquiteto e desenvolvedor de banco de dados corporativo. Ele tem sido um MVP do Microsoft SQL Server para seis anos e já escreveu quatro livros sobre design de banco de dados. Atualmente ele é o arquiteto de dados e às vezes DBA para Christian Broadcasting Network, escritórios de apoio em Virginia Beach, Virgínia e em Nashville, Tennessee.
Timothy Ford Eus um MVP do SQL Server e tem trabalhado com o SQL Server para mais de 10 anos. Ele é o principal DBA e especialista do assunto para a plataforma do SQL Server para a saúde do espectro. Ele tem sido escrito sobre tecnologia desde 2007 para uma variedade de Web sites e mantém seu próprio blog em thesqlagentman.com, cobrindo o SQL como tópicos de desenvolvimento bem como teletrabalho e profissional.**
Saiba mais sobre "SQL Server DMV Starter Pack" em red-gate.com/our-company/about/book-store.