SQL Server: Melhorando a performance do Banco de Dados
por Wagner Aparecido Miranda - W2R Consultoria e Sistemas
Atualmente quem é DBA e procura sempre está antenado na parte de performance do seu ambiente de MS SQL Server. Abaixo segue algumas dicas para tratar de índices, update statistics, nada de muita novidade no nosso mundo, mas que são comandos que geralmente não temos assim de cabeça, mas conseguimos copiar e colar tranquilamente e ter um efeito esperado.
Vamos ver algumas coisas sobre desfragmentação de índices que hoje é um grande impacto na operação em seu ambiente.
Geralmente é tranquilo analisar se seu banco de dados está com alguns índices desfragmentados, porém visualizar todos os índices de todos os bancos de dados seria uma opção melhor ainda do que visualizar um a um não é mesmo?
Então vamos a 1º dica de hoje:
–=======================================
–VERIFICAR TODOS OS ÍNDICES DO
–BANCO DE DADOS QUE ESTÃO DESFRAGMENTADOS
–=======================================
–=======================================
–VERIFICAR TODOS OS ÍNDICES DO
–BANCO DE DADOS QUE ESTÃO DESFRAGMENTADOS
–=======================================
USE master
GO
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC SHOWCONTIG(‘?’)”
GO
GO
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC SHOWCONTIG(‘?’)”
GO
Esse comando você conseguirá visualizar de uma forma bem simples todo os índices que estão desfragmentados em todos seus databases. Veja quais índices você deve dar manutenção antes que ele prejudique seu ambiente produtivo!!!
2º Dica
Você identificou que seu banco de dados está com a maior parte de seus índices com problemas, todos desfragmentados, então como vamos fazer para poder corrigir sem que isso nos leve tempo para realizar a atividade um a um? Vamos utilizar o comando abaixo juntamente com o updatestats para termos uma solução completa de correção:
–=======================================
–ATUALIZAR INDICES E UPDATE STATISTICS
(SQL Server 2000 E SQL Server 2005)
–===================================
USE Banco_de_Dados_de_Usuario
GO
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
GO
EXEC sp_updatestats
GO
GO
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
GO
EXEC sp_updatestats
GO
3º Dica
Para realizar o update statistics no banco de dados seleciona, vamos utilizar o comando abaixo. Copie o resultado deste comando e cole em uma New Query para executar o comando e termos o efeito desejado
–=======================================
–ATUALIZACAO DE UPDATE STATISTICS
–===================================
USE Banco_de_Dados_do_Usuario
GO
SELECT ‘UPDATE STATISTICS’, NAME FROM SYSOBJECTS WHERE XTYPE = ‘U’
GO
–ATUALIZACAO DE UPDATE STATISTICS
–===================================
USE Banco_de_Dados_do_Usuario
GO
SELECT ‘UPDATE STATISTICS’, NAME FROM SYSOBJECTS WHERE XTYPE = ‘U’
GO
4º Dica
Quais são as top 50 querys que mais tem utilização de CPU no seu banco de dados? Com essa query conseguimos ter essa visão e conseguimos tratar e realizar um tunning desta query.
–=========================================
–TOP 50 querys que mais utilizam CPU (MS SQL Server 2005
–=========================================
SELECT TOP 50
SUM(QS.TOTAL_WORKER_TIME) AS TOTAL_CPU_TIME,
SUM(QS.EXECUTION_COUNT) AS TOTAL_EXECUTION_COUNT,
COUNT(*) AS NUMBER_OF_STATEMENTS,
QS.PLAN_HANDLE
FROM
SYS.DM_EXEC_QUERY_STATS QS
GROUP BY
QS.PLAN_HANDLE
ORDER BY
SUM(QS.TOTAL_WORKER_TIME) DESC
–=========================================
–TOP 50 querys que mais utilizam CPU (MS SQL Server 2005
–=========================================
SELECT TOP 50
SUM(QS.TOTAL_WORKER_TIME) AS TOTAL_CPU_TIME,
SUM(QS.EXECUTION_COUNT) AS TOTAL_EXECUTION_COUNT,
COUNT(*) AS NUMBER_OF_STATEMENTS,
QS.PLAN_HANDLE
FROM
SYS.DM_EXEC_QUERY_STATS QS
GROUP BY
QS.PLAN_HANDLE
ORDER BY
SUM(QS.TOTAL_WORKER_TIME) DESC
5º Dica
Com essa query você consegue verificar quais são as 50 querys que mais utilizam memória em seu servidor de SQL Server.
–=======================================
–Top 50 querys que mais utilizam Memoria
–=======================================
SELECT TOP 50
(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,
QT.DBID,
QT.OBJECTID
FROM
SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
ORDER BY [AVG IO] DESC
–Top 50 querys que mais utilizam Memoria
–=======================================
SELECT TOP 50
(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,
QT.DBID,
QT.OBJECTID
FROM
SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
ORDER BY [AVG IO] DESC
Com essas 5 dias listadas acima, acredito que conseguimos identificar e corrigir diversos erros relacionados a performance de MS SQL Server.
