28 de agosto de 2012

Avaliando a performance de queries no SQL Server

Muito da aceitação de um programa de computador por seus usuários pode ser atribuído à praticidade de sua interface gráfica e até mesmo a aparência que ela tenha. No entanto, a aceitação rapidamente se reverte se o programa apresentar problemas de performance. Com bastante frequência, a degradação de performance num programa está associado a queries mal projetadas submetidas a um banco de dados pelo programa.

Quando digo "mal projetada", eu me refiro a certas características das queries adotadas pelo programa, tais como não utilizar os índices apropriados das tabelas ou manter tabelas sem índice, fazer ligações ineficientes com outras tabelas, trazer registros demais que não serão utilizados ou ainda executar uma mesma query mais vezes do que o necessário.

A partir da sua versão 2005, o SQL Server mantém em tabelas virtuais uma série de informações a respeito das queries que foram executadas pelo banco de dados. Essas informações ficam armazenadas em memória e são descartadas quando o serviço do banco é desligado. No entanto, são informações bastante úteis para auxiliar na localização de problemas de performance gerados pelas queries mal planejadas.

Um usuário que queira consultar as informações disponibilizadas nessas tabelas virutais precisa ter atribuida a permissão VIEW SERVER STATE. Isso pode ser feito visualmente, através das propriedades do servidor no Management Studio ou via comando SQL, utilizando-se o GRANT.

Uma das views mais interessantes nesse contexto é a sys.dm_exec_query_stats, responsável pelas estatísticas de execução de queries. Ela traz, dentre outras informações, a quantidade de vezes que uma query foi executada, o tempo gasto nessas execuções, quantas linhas foram retornadas, a quantidade de leituras e gravações feitas durante a execução. Ela dá acesso também ao texto da query e ao plano de execução montado pelo SQL. O exemplo no quadro abaixo lista em ordem decrescente as queries que mais consumiram CPU:
SELECT qs.total_worker_time / qs.execution_count As 'Uso Médio da CPU',
qs.total_worker_time As 'Uso Total da CPU',
qs.execution_count As 'Qtde de Execuções',
qt.text As 'Query',
DB_NAME(qt.dbid) As 'Nome do Banco'
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) As qt
ORDER BY 1 DESC

Veja que a quantidade de vezes que uma query executou não é determinante para que ela tenha consumido mais CPU na média. Consulta semelhante pode ser montada para obter as queries que mais foram executadas (pra avaliar se todas as execuções são mesmo necessárias) ou a quantidade de linhas retornada (pra avaliar se não é possível restringir mais os registros, acrescentando outras comparações à cláusula WHERE). Os tempos listados estão em microsegundos; para convertê-lo em segundos, divida o valor por 1 milhão. É possível ainda levantar a quantidade de gravações feitas por uma query e avaliar se tantas alterações são mesmo necessárias

Outro detalhe a ser destacado no quadro anterior é que o texto da query não está na mesma view. Para obtê-lo, é necessário pegar o código contido no campo sql_handle e repassá-lo à função sys.dm_exec_sql_text.

Outra view bastante útil é a sys.dm_db_missing_index_details. Como o próprio nome diz, ela armazena detalhes sobre o uso de queries cujas execuções não estão otimizadas por que não foi possível encontrar na tabela um índice apropriado. Baseado nas informações retornadas por ela, podemos introduzir novos índices e melhorar a performance das consultas envolvendo as tabelas em questão. O quadro abaixo mostra um exemplo de consulta a essa view, listando as colunas encontradas na cláusula WHERE da query com problema, a tabela envolvida e a quantidade de vezes que a query problemática foi disparada:
SELECT equality_columns, inequality_columns, statement, count(*) As 'Qtde Exec'
FROM sys.dm_db_missing_index_details
WHERE DB_NAME(database_id) = 'ABC71DS94'
GROUP BY equality_columns, inequality_columns, statement
ORDER BY statement

A coluna statement traz o nome da tabela que está sem índice apropriado. A equality_columns lista as colunas usadas em comparações de igualdade no WHERE - algo como NOME_DO_CAMPO = VALOR. Em inequality_columns aparecem as colunas usadas em outros tipos de comparação do WHERE. Veja que é possível filtrar as informações por banco de dados instalado, permitindo avaliar os índices para cada banco isoladamente.

A lista de informações que podemos obter no DMV (Dynamic Management Views) inclui ainda os motivos de espera para uma query ser executada, dados de espelhamento e replicação, tamanho do banco de dados e suas partições, etc. A lista completa das informações disponíveis por esse meio pode ser encontrada em Dynamic Management Views and Functions no MSDN online.

Nenhum comentário :

Postar um comentário

OBS: Os comentários enviados a este Blog são submetidos a moderação. Por isso, eles serão publicados somente após aprovação.

Observação: somente um membro deste blog pode postar um comentário.