Performance Finally: Cost Limit - SQL SERVER

        Concluímos aqui nossa primeira etapa de configurações de performance no SQL SERVER. Alterar as configurações do SQL pode ser uma ótima maneira de otimizar desempenho, porém requer um considerável conhecimento da estrutura e funcionamento do banco de dados. Isto é bom quando conhecemos estas configurações e podemos utilizá-las para adaptar o nosso banco a cada situação, mas também pode ser perigoso, pois uma série de recursos que foram automaticamente configurados para uma boa performance podem ser alterados de forma errada, gerando gravíssimos problemas no banco de dados. O ideal é realizar as configurações em um servidor de teste, e caso sucesso, implementá-las no banco em produção.


Performance Final: Query Governor Cost Limit 

Configura o tempo limite de custo de uma consulta. Esta opção é uma das poucas que normalmente sofrem alterações por parte dos DBAs, pois permite que seja gerenciado o tempo em segundos para uma consulta ser disparada. O valor padrão para essa configuração é 0, o que significa que não há limites para quanto tempo uma consulta pode ser executada. Deixar o banco de dados com esta configuração pode ser perigoso para consultas lentas, principalmente quando há diversos usuários simultâneos acessando o banco de dados.


EXEC SP_CONFIGURE 'query governor cost limit', 50 --segundos
GO
RECONFIGURE WITH OVERRIDE
GO

Performance: NESTED TRIGGERS - SQL SERVER

Hoje falaremos um pouco sobre performance:

Em especifico melhoras no modulo de NESTED TRIGGERS DO SQL SERVER


Configuração permite o uso ou não de triggers aninhadas. Por padrão é configurada a opção 1, que significa que o uso de triggers aninhadas está ativo. Sabemos que eles consomem uma boa parte do processamento de uma transação, então caso deseje que os desenvolvedores não usem este tipos de triggers, basta setar esta configuração para o valor 0.


EXEC SP_CONFIGURE 'nested triggers', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Lição De Casa: https://msdn.microsoft.com/pt-br/library/ms190739

Se gatilhos aninhados forem permitidos e um gatilho na cadeia iniciar um loop infinito, o nível de aninhamento será excedido e o gatilho será encerrado. Podemos usar gatilhos aninhados para executar funções de manutenção úteis, como armazenar uma cópia de backup de linhas afetadas por um gatilho anterior.Por exemplo, você pode criar um gatilho em PurchaseOrderDetail que salva uma cópia de backup das linhas de PurchaseOrderDetail que o gatilho delcascadetrig excluiu.Com o gatilho delcascadetrig em vigor, excluirPurchaseOrderID 1965 de PurchaseOrderHeader exclui a linha correspondente dePurchaseOrderDetail.Para salvar os dados, você pode criar um gatilho DELETE emPurchaseOrderDetail que salva os dados excluídos em outra tabela criada separadamente,del_save. Por exemplo:

CREATE TRIGGER Purchasing.savedel
   ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
   INSERT del_save;
   SELECT * FROM deleted;

Performance: INDEX Memory - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de INDEX Create Memory Do SQL SERVER

Determinante para alocaçao de memória utilizado na criação dos índices. O valor default 0 permite que o servidor SQL Server determinar automaticamente o valor ideal a ser utilizado. Em praticamente todos os casos, o SQL Server irá configurar a quantidade de memória otimizada, por exemplo, em índices cluster. Em outros casos, pode acontecer de um índice ocupar espaço demais em uma tabela e tornar as consultas lentas. Somente se essa ocorrência for verificada, é que se torna importante definir um espaço em memória para os índices.


EXEC SP_CONFIGURE 'index create memory', 2048
GO
RECONFIGURE WITH OVERRIDE
GO

Performance: Server Memory- SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de  Memoria (Server Memory)  Do SQL SERVER

Max Server Memory e Min Server Memory: configuram o valor máximo e mínimo de memória para um servidor SQL, respectivamente. Esta opção pode ser interessante quando, no servidor onde reside o SQL Server, existirem aplicações que em determinados momentos consomem muita memória, deixando pouca para o SQL Server. Caso o seu banco de dados seja a única aplicação no servidor, aconselha-se não alterar esta configuração, porque o uso da memória já estará otimizado.

Exemplos práticos:

EXEC SP_CONFIGURE 'min server memory (MB)', 2
GO
RECONFIGURE WITH OVERRIDE
GO 


EXEC SP_CONFIGURE 'max server memory (MB)', 3
GO
RECONFIGURE WITH OVERRIDE
GO 

Performance: Priority Boost - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de Priority Boost Do SQL SERVER

Esta configuração é inteiramente responsável entre alternar as prioridade dos processamentos referentes ao SQL Server na CPU Do Servidor. Aqui temos uma configuração muito interessante: os processos oriundos do SQL server terão uma prioridade maior de execução que de outros aplicativos no servidor, podendo ser utilizado quando temos a certeza de que existem no servidor outras aplicações rodando além do SQL Server, e que sejam aplicações com baixo grau de importância quanto à performance.

Nota:
O valor padrão 0 = sem prioridade
1 = Maior prioridade.


EXEC SP_CONFIGURE 'priority boost', 1
GO
RECONFIGURE WITH OVERRIDE
GO 

Performance: Parallelism - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de  Max Degree Of Parallelism Do SQL SERVER

Configuração responsável pelo grau máximo de paralelismo. Por padrão (embora pareça contraditório um falso negativo rs), o valor 0 indica que o paralelismo está ativado e pode usar todas CPU´s existentes no servidor. Se você alterar esta definição para 1, o paralelismo é desativado para todas as CPU´s. Devemos desativar o paralelismo somente quando for desnecessário o uso de múltiplas CPU´s, por exemplo, aplicações que executam consultas que retornam poucos registros e fazem pouco uso de processamento. Para estas, aconselha-se utilizar somente uma CPU, poupando as outras CPU´s para serviços com maiores prioridades.

EXEC SP_CONFIGURE 'max degree of parallelism', 0
GO
RECONFIGURE WITH OVERRIDE
GO


Nota: Paralelismo é a capacidade do Query Optimizer de usar múltiplas CPUS para executar uma única consulta.

Performance: Memory Per Query - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de  Min Memory Per Query Do SQL SERVER

Responsável pelo valor mínimo da memória para cada query. O valor mínimo padrão é 1024Kb, podendo ser aumentado somente quando o servidor possui muita memória RAM disponível e se existe a certeza de que as queries disparadas ocupam um valor bem maior que o padrão. Ou seja, as chamadas consultas “pesadas”. Como exemplo, considere uma consulta que traga milhares de registros de várias tabelas, e estes registros precisem ser ordenados pelo usuário. Como o espaço em memória requerido para esta tarefa é grande e sempre irá ultrapassar o tamanho padrão de 1024Kb, pode-se configurar um tamanho mínimo padrão de memória maior.

EXEC SP_CONFIGURE 'min memory per query', 3072
GO
RECONFIGURE WITH OVERRIDE
GO 

Performance: Network Packet - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de Network Packet Size Do SQL SERVER

Responsavel pelo tamanho padrão de pacotes enviados pelas transações do SGBD. O tamanho padrão é 4096 bytes para cada pacote. Esta configuração pode ser alterada para um valor maior quando a sua aplicação armazena e trafega com freqüência dados com um grande número de bytes por requisição, por exemplo, imagens e longos trechos de texto. Aumentar o número de bytes, temos menos pacotes trafegando e assim aceleramos o tráfego na rede. Por outro lado, se o banco de dados trafega dados em pouca quantidade, aumentar o tamanho dos pacotes pode ser prejudicial.

EXEC SP_CONFIGURE 'network packet size', 6000
GO
RECONFIGURE WITH OVERRIDE
GO 

Performance: Recovery Internal - SQL SERVER

Hoje falaremos um pouco sobre performance:
Em especifico melhoras no modulo de  Recovery Internal Do SQL SERVER

Recovery Internal é responsavel pelo intervalo de recuperação dos objetos que compõem o banco de dados quando alguma falha ocorre. Se o seu Servidor SQL Server é muito ativo, recebendo uma carga enorme de requisicoes e mantém o monitor de performance em 100% na maior parte do tempo, esta é uma boa configuração a ser alterada. Por padrão, o SQL Server tem o valor 0 nesta configuração, o que significa que ele terá até 1 minuto para realizar uma recuperação após reiniciar, o que pode não ser suficiente.

Para esses casos, podemos aumentar este intervalo para 5 minutos, como no exemplo abaixo:

EXEC SP_CONFIGURE ‘recovery interval’, 5
GO
RECONFIGURE WITH OVERRIDE
GO

Configurações De Performance - SQL SERVER

Para obter o máximo de performance , DBAs configuram o SQL Server para atender às suas necessidades de negócio e muitas vezes acabam alterando configurações de forma equivocada.

Conhecendo as configurações:

A maneira mais fácil de obter a lista das configurações que o banco de dados possui é executando uma stored procedure de servidor chamada SP_CONFIGURE. Esta stored procedure irá listar as configurações que estão sendo utilizadas atualmente no banco de dados, e seu resultado pode variar dependendo da versão do SQL Server

EXEC SP_CONFIGURE

Repare que para cada configuração, temos cinco colunas de informações:

•Name: nome da configuração;
•Minimun: valor mínimo permitido para a configuração listada;
•Maximun: valor máximo permitido para a configuração listada;
•Config_value: valor configurado;
•Run_value: valor que está sendo executado. Pode ser que o valor que está sendo executado não seja o valor configurado, isto acontece porque determinadas configurações, quando alteradas, só entrarão em vigor após o servidor ser reiniciado.


Alterando uma configuração:
 A stored procedure de servidor SP_CONFIGURE, além de exibir as configurações, pode ser utilizada também para alterá-las. A sintaxe para alterar uma configuração é a seguinte:

SP_CONFIGURE [nome da configuração], [novo valor a ser configurado]
GO
RECONFIGURE
WITH OVERRIDE GO

Observe que mesmo utilizando o comando RECONFIGURE WITH OVERRIDE que serve para alterar uma configuração em tempo de execução, algumas configurações só serão realmente executadas ao reiniciar o servidor, como é o caso do 'recovery interval' (intervalo de recuperação do banco de dados).

Como exemplo, vamos alterar a configuração clr enabled para desabilitar o suporte ao CLR (common langauge runtime) no banco de dados, alterando o valor de 1 para 0.

SP_CONFIGURE 'clr enabled', 0
GO

RECONFIGURE WITH OVERRIDE
GO

Para confirmar alteração: SP_CONFIGURE 'clr enabled'

Configurações que influenciam a performance

Dentre as configurações listadas através da stored procedure de servidor SP_CONFIGURE, vale destacar algumas configurações importantes para o conhecimento e manuteção do banco de dados e, com isso, obter um ganho considerável de desempenho:

•Recovery Interval, 
•Network Packet Size, 
•Min Memory Per Query, 
•Max Degree Of Parallelism, 
•Priority Boost, 
•Max Server Memory, 
•Min Server Memory, 
•Index Create Memory, 
•Nested Triggers, 
•Query Governor Cost Limit.

Nos próximos post iremos detalhar individualmente cada uma das configurações;

Microsoft Press libera livros gratuitos.

Olá pessoal, é isso mesmo que vocês leram, não é de hoje que a Microsoft libera todo conteúdo dos seus livros de forma on-line e melhor gratuito.

Clique nas imagens abaixo para baixar o seu livro ou clique aqui para ir a página oficial.

Free ebooks: Great content from Microsoft Press that won’t cost you a penny

★★★★★

★★★★

★★★

★★

May 4, 2012By KimSpilker

Windows

Microsoft SQL Server

Microsoft Azure

Developer Topics

Grande abraços

Scripts: Últimos Backup efetuados - SQL Server

--LISTAR OS ÚLTIMOS BACKUPS REALIZADOS COM SUCESSO

SELECT
                 SD.NAME,
                 CASE BS.TYPE
                        WHEN 'D' THEN 'DADOS'
                        WHEN 'L' THEN 'LOG'
               END AS TYPE,
                 BS.DATABASE_NAME,
                 MAX(BS.BACKUP_START_DATE) AS LAST_BACKUP
FROM    
MASTER..SYSDATABASES SD
                LEFT OUTER JOIN MSDB..BACKUPSET BS ON 
                RTRIM(BS.DATABASE_NAME) = RTRIM(SD.NAME)
             
               LEFT OUTER JOIN MSDB..BACKUPMEDIAFAMILY BMF ON
               BS.MEDIA_SET_ID = BMF.MEDIA_SET_ID
GROUP BY
SD.NAME,
                BS.TYPE,
                BS.DATABASE_NAME
ORDER BY
SD.NAME,LAST_BACKUP

Scripts: Procurar campos (Colunas) Em Procedures - SQL SERVER

Imaginamos o seguinte cenário, em uma alteração de collumn name ..
todas as procedures apresentariam erros ao ser executadas por manter o nome da antiga coluna.

Neste scripts procuramos identificar todas as procedures
em que esta sendo utilizado determinada coluna, para possível alteração/correções ..

--
SELECT DISTINCT
  SO.NAME
FROM
   syscomments SC
  INNER JOIN sysobjects SO ON SC.ID = SO.ID
WHERE
  SC.TEXT LIKE '%CLIENTES%'   -- NOME DO CAMPO
  AND SO.Type = 'P'                          -- TYPE P = PROCEDURE

Scripts: Maiores tabelas da base de dados (SQL SERVER)

Compartilhamos abaixo um script para facilitar o dia dia do desenvolvedor,
a identificar as maiores tabelas ou mais preenchidas por ordem em toda a base de dados.


SELECT
              'SELECT * FROM ' + OBJECT_NAME(ID) AS NOME_TABELA,
              ROWCNT                                                  AS QUANTIDADE_DE_REGRITOS,
              DPAGES * 8                                                    AS TAMANHO
FROM
              SYSINDEXES
WHERE
              INDID IN (1,0)
              AND OBJECTPROPERTY(ID,'ISUSERTABLE') = 1
ORDER BY
              ROWCNT DESC

Scripts: Procurar campos (Colunas e Tabelas) SQL SERVER

Compartilhamos abaixo um script para facilitar o dia dia do desenvolvedor,
a identificar campos ou tabelas por nome em toda a base de dados

Obs: Especifico para SQL SERVER

--
DECLARE @Busca VARCHAR(8000)
SET @Busca = 'NOME PESQUISADO'

SELECT
        Colunas.COLUMN_NAME,
        Tabelas.TABLE_NAME
FROM
        INFORMATION_SCHEMA.TABLES Tabelas
  INNER JOIN INFORMATION_SCHEMA.COLUMNS Colunas
        ON Tabelas.TABLE_NAME = Colunas.TABLE_NAME

WHERE
         (Tabelas.TABLE_TYPE='BASE TABLE') AND
         (
                 (Tabelas.TABLE_NAME LIKE '%' + @Busca + '%') OR
                  (Colunas.COLUMN_NAME LIKE '%' + @Busca + '%')
         )

ORDER BY 
Tabelas.TABLE_NAME ASC

about

.. Este blog tem como objetivo compartilhar assuntos relacionados a banco de dados,
de diversas plataformas e SGBD' S

Expondo meus scripts cotidianos dia a dia e scripts de outros desenvolvedores.