P&R do SQLAfunilamentos de CPU, restauração, movimentação de bancos de dados e muito mais

Editado por Nancy Michell

P O uso de CPU do meu SQL Server™ começou a apresentar picos repentinamente – embora nada tenha sido alterado. Nenhum usuário foi adicionado, nenhum hardware foi desativado, nenhuma tabela foi criada. O que pode estar acontecendo?

Dica: acesso durante a Criação do Índice

Há momentos em que você deseja criar índices em tabelas grandes (o que pode demorar muito tempo), embora queira que os dados permaneçam disponíveis durante esse processo. Como você consegue cumprir ambos os objetivos?

Sempre que um índice cluster é criado, ignorado ou reconstruído, o SQL Server coloca um bloqueio SCH-M (Schema modification) na tabela, que impede todo o acesso do usuário aos dados subjacentes durante a operação. É esse o caso quando você cria um índice cluster em uma tabela. Por outro lado, quando você cria um índice não-cluster em uma coluna, o SQL Server coloca um bloqueio S (Shared) na tabela e isso, ao mesmo tempo em que impede a atualização dos dados na tabela subjacente, pelo menos permite que você execute instruções SELECT e, assim, possa ler os dados.

Caso a disponibilidade de leitura na tabela seja importante durante a criação do índice cluster, é possível criar um índice em uma tabela e torná-lo uma operação online. Este é o comando:

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

Ao usar operações de índice online nas tabelas, o SQL Server continuará colocando um bloqueio SCH-M para um índice cluster ou um bloqueio S para um índice não-cluster na tabela subjacente, mas apenas por um período muito curto – durante as fases inicial e final da operação de índice. Por isso, essa opção oferece um acesso melhor à consulta e à atualização da tabela subjacente durante o processo de criação do índice. Apenas observe que a criação do índice/operações online só estão disponíveis com o SQL Server 2005 Enterprise Edition.

R Um afunilamento de CPU que acontece repentina e inesperadamente sem que haja nenhuma alteração ou carga adicional pode ter várias razões em sua origem, mas algumas das mais comuns incluem um plano de consulta não ideal, uma má configuração do SQL Server, o projeto incorreto do aplicativo/banco de dados e os recursos de hardware insuficientes.

A primeira coisa a ser feita em uma situação como essa é identificar se o servidor está vinculado à CPU e, caso esteja, identificar as instruções que mais consomem a CPU no seu sistema de SQL Server local. É possível usar o Monitor de Desempenho para determinar se o servidor está vinculado à CPU olhando o contador PROCESSADOR:% DO TEMPO DO PROCESSADOR. Caso o valor referente ao tempo usado por CPU seja igual ou maior do que 75 por cento, você tem um afunilamento de CPU.

Você também deve monitorar os agendadores do SQL Server consultando a DMV (exibição de gerenciamento dinâmico) chamada SYS.DM_OS_SCHEDULERS para consultar o valor das tarefas em execução. Um valor não nulo indica que as tarefas devem aguardar sua fração de tempo para serem executadas; valores altos desse contador também são um sintoma de um afunilamento de CPU.

É possível usar a seguinte consulta para listar todos os agendadores e observar o número de tarefas em execução:

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Para obter as 50 instruções SQL de maior uso da CPU, use a consulta na Figura 1.

Figure 1 50 usos principais da CPU

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

P Eu tive que recuperar um banco de dados do SQL Server 2005 a partir da fita. Depois da restauração, a maioria das permissões que os usuários tinham no banco de dados foi perdida. Você pode me dizer o que eu fiz de errado durante a restauração? Os dados estavam certos, mas as permissões estavam uma bagunça.

R O mais provável é que você não tenha restaurado o banco de dados mestre no mesmo ponto e as identificações de logon no banco de dados do usuário não correspondam às do banco de dados mestre atual. Você precisa sincronizá-los. A barra lateral "Onde obter ajuda ao mover um banco de dados" lista os recursos mais úteis para se chegar à origem dos problemas enfrentados por você ao restaurar ou mover um banco de dados.

P Eu desenvolvi um aplicativo em que uso um procedimento armazenado que distribui os dados XML em tabelas relacionais usando o OpenXML no SQL Server 2005 SP1. O documento XML tem, no máximo, 5KB (a média é de 2,5KB). O procedimento armazenado é chamado muitas vezes em paralelo (até 50 vezes).

Estou enfrentando sérios problemas de contenção do bloqueio e acho que eles se devam ao OpenXML. O que vocês acham?

R Embora o OpenXML possa ser mais rápido na distribuição ou na conversão de dados em um único segmento do que o método baseado em nós, este normalmente dimensiona melhor, especialmente se usado em paralelo. No entanto, caso vá usar o OpenXML mesmo assim, você deve usar as seguintes diretrizes para melhorar o desempenho geral do OpenXML.

Em vez de chamar o OpenXML cinco vezes com o mesmo padrão de linha (ao observar que estava fazendo isso em sua solução), você deve extrair todos os dados com o mesmo padrão de linha para uma tabela temporária e, em seguida, executar as seleções nessa tabela temporária. Tente liberar a memória usando sp_xml_removedocument assim que possível. Além disso, é uma boa idéia evitar usar curingas como, por exemplo, * e //, sempre que possível; fornecer o caminho explícito resultará em uma consulta de execução melhor.

P DBCC SHRINKFILE está sendo executado muito lentamente no meu servidor. Eu teria um desempenho melhor em um computador multiprocessador? O que posso fazer para melhorar a situação?

R DBCC SHRINKFILE é uma operação de segmento único, o que significa que ele não usa várias CPUs. Ele move páginas do back-end do arquivo para o front-end, uma página por vez. E a redução, como costuma ser chamada, não executa a desfragmentação; na verdade, em muitas situações, a redução aumenta a fragmentação lógica.

Entre algumas das formas de melhorar o desempenho da redução está a movimentação das páginas em índices cluster. Se você tivesse heaps e eles tivessem muitos índices não-cluster, a velocidade seria notadamente menor (em comparação a uma situação de índice cluster).

Também observe que a movimentação das páginas de dados BLOB (grande objeto binário) é baixa, porque os dados em linha devem ser lidos para que a raiz dos dados LOB seja localizada.

Caso a maior parte do conteúdo de um índice/tabela resida no final do arquivo, é possível reconstruir os índices e movê-los para o front-end do arquivo. A reconstrução dos índices aproveita várias CPUs e poderia usar menos espaço de log no modo bulk_logged. Quando você executar a redução, ela funcionará mais rapidamente.

Para obter mais informações sobre operações de redução, comece com as entradas em blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspxe blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Onde obter ajuda ao mover um banco de dados

• Como mover bancos de dados entre computadores que estão executando o SQL Server
• Como mover os bancos de dados do SQL Server para um novo local usando as funções Desanexar e Anexar no SQL Server
• INF: Opção com a instrução RESTORE usando o WITH MOVE
• Como resolver problemas de permissão ao mover um banco de dados entre servidores que estão executando o SQL Server
• INF: Como transferir logons e senhas entre instâncias do SQL Server
• INF: Noções básicas sobre e solução de problemas Assistente de Banco de Dados copiar na SQL Server 2000
• Logons de usuário e permissões em um banco de dados podem estar incorretos depois que o banco de dados for restaurado
• Não é possível restaurar backups do banco de dados de sistema para uma compilação diferente do SQL Server
• Como recuperar uma tabela específica ou linhas de backups do banco de dados ou backups do log de transação no SQL Server

Dica: alterando portas

Qualquer pessoa que conheça o SQL Server sabe que o número de porta padrão que o SQL Server escuta é 1433. Embora as instâncias nomeadas do SQL Server sejam configuradas para usar as Portas Dinâmicas (o que significa que elas poderão selecionar qualquer porta disponível sempre que uma instância do SQL Server for iniciada), a instância padrão do SQL Server sempre escuta a porta 1433. Portanto, caso esteja escutando a porta padrão e não esteja bem protegido, o servidor está em risco. Mas é possível impedir ataques alterando a porta padrão. Assim:

Abra o SQL Server Configuration Manager e expanda Configuração de Rede do SQL Server 2005, Protocolos. Em seguida, clique duas vezes em TCP/IP. A lista das propriedades de TCP/IP e suas funções são mostradas no gráfico abaixo; defina-as corretamente.

Observe que, como o SQL Server Database Engine pode escutar várias portas no mesmo endereço IP, você deve listar as portas que deseja usar separadas por uma vírgula no formato 1433,1500,1501. Caso queira configurar um único endereço IP para escutar várias portas, você também deve definir o parâmetro Escutar Tudo para não na guia Protocolos da caixa de diálogo Propriedades de TCP/IP.

Agora, clique com o botão direito do mouse em cada endereço e clique em Propriedades para identificar o endereço IP que deseja configurar. Caso a caixa de diálogo Portas TCP Dinâmicas contenha 0, o que indica que o mecanismo do banco de dados está escutando portas dinâmicas, exclua o 0. Na área Propriedades de IP, dentro da caixa Porta TCP, digite o número da porta que o endereço IP deve escutar e clique em OK. No painel do console, clique em Serviços do SQL Server 2005 e, no painel de detalhes, clique com o botão direito do mouse em SQL Server (<nome da instância>) e clique em Reiniciar para parar e reiniciar o SQL Server.

Depois que você configura o SQL Server para escutar uma porta específica, há várias formas para que um cliente se conecte à porta. É possível executar o serviço Navegador do SQL Server no servidor para se conectar à instância Database Engine por nome; você pode criar um alias no cliente, especificando o número da porta, ou pode programar o cliente para se conectar usando uma cadeia de conexão personalizada.

Meus agradecimentos aos seguintes profissionais de TI da Microsoft por suas respostas às perguntas do mês: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal e Wayne Yu.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..