INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'

Hoje ao criar ima VIEW indexada para ajustar um relatório com problemas de performance, um outro processo que referenciava uma das tabelas incluídas na VIEW, apresentou as seguintes mensagens de erro:

“Msg 1934, Level 16, State 1, Line 1

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.”

Depois de pesquisar um pouco, descobri uma referência a este tipo de problema através do seguinte link.

Basicamente, para inserir uma linha com êxito em uma tabela que é referenciada em um modo de exibição indexado, a configuração ARITHABORT SQL deve ser definida como ON. Além disso, a instrução que se aplica essa configuração deve ser executada em seu próprio lote, ou seja, junto com o comando de criação da view. Como os procedimentos contêm apenas um lote, adicionando a instrução para o procedimento não funciona.

Resolvi o problema, executando um ALTER VIEW nas views indexadas, com os seguintes comandos:

SET ARITHABORT ON

 

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

 

GO

 

alter view dbo.vw_folha_ramo

with schemabinding

as

.

.

Mais um usuário feliz!

Bom final de semana a todos!

 

 

Você sabia que...

Você sabia que o Sql Server permite a ordenação das colunas através do seu índice, e não apenas através dos nomes dos campos? Observe o código abaixo:

SELECT

      [codigo]

      ,[nome]

      ,[endereco]

FROM

      [Desenvolvimento].[Clientes]

ORDER BY

      1 ASC,

      3 DESC

Concedendo permissões com grant, usando os Database Schemas

Podemos usar o comando grant para conceder permissões a um usuário, utilizando os Schemas criados. Por este motivo, os Database Schemas se tornam extremamente atraentes, sob o ponto de vista da segurança, pois podemos conceder, através de um comando, permissões de acesso a uma série de tabelas associadas a um Schema. Observe o exemplo abaixo:

 

 

--Criando um banco de testes

create database TesteSchema

go

 

use TesteSchema

go

 

--Criando um novo Database Schema

Create Schema Desenvolvimento

go

 

--Criando tabelas associadas ao Schema

create Table Desenvolvimento.Clientes(

  codigo int,

  nome varchar(100),

  endereco varchar(100)

)

 

create Table Desenvolvimento.Fornecedores(

  codigo int,

  nome varchar(100),

  endereco varchar(100)

)

 

--Adicionando um novo usuário

sp_adduser 'teste','teste'

go

 

--Concedendo permissões ao Schema para o usuário "teste"

grant select on schema::Desenvolvimento to [teste]

go

 

 

Querys com sys.dm_exec_query_stats para medição de performance

Com base nos Scripts apresentados no ótimo livro Inside MS-SQL 2005 Query tuning and otimization, realizei algumas modificações nas querys que utilizam a view sys.dm_exec_query_stats para acompanhamento da performance do banco de dados.

Usando o dm_exec_query_stats para retornar as 10 querys com o maior tempo de trabalho.

SELECT TOP 10

    total_worker_time/execution_count AS [Média de custo de CPU],

    execution_count [Número de execuções do plano],

    last_execution_time [Hora da última execução],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

      (CASE WHEN statement_end_offset = -1

            THEN LEN(CONVERT(nvarchar(max), text)) * 2

            ELSE statement_end_offset

       END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY [Média de custo de CPU] DESC;

Query que retorna as 10 querys que tomam o maior volume de tempo de CPU.

SELECT TOP 10

    total_worker_time/execution_count AS [Média de custo de CPU],

    execution_count [Número de execuções do plano],

    last_execution_time [Hora da última execução],

    total_physical_reads [Total de leituras físicas],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

       (CASE WHEN statement_end_offset = -1

               THEN LEN(CONVERT(nvarchar(max), text)) * 2

               ELSE statement_end_offset

        END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY [Média de custo de CPU] DESC;

Query para retornar as querys (top 10) mais frequentemente executadas

SELECT TOP 10

      total_worker_time [Tempo total de trabalho],

      execution_count [Número de execuções do plano],

      (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

          (CASE WHEN statement_end_offset = -1

                  THEN LEN(CONVERT(nvarchar(max),text)) * 2

                  ELSE statement_end_offset

           END - statement_start_offset)/2)

       FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY execution_count DESC;

Use a query abaixo para acompanhar as querys que estão sendo recompiladas com frequência.

SELECT TOP 10

      plan_generation_num as [Número de execução do plano],

      execution_count as [Número de execuções],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

      (CASE WHEN statement_end_offset = -1

            THEN LEN(CONVERT(nvarchar(max),text)) * 2

            ELSE statement_end_offset

       END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

WHERE plan_generation_num >1

ORDER BY 1 DESC;

A query abaixo lista as 10 primeiras querys que geram o maior volume de I/O.

SELECT TOP 10

 (total_logical_reads/execution_count) AS [Média de leituras lógicas],

 (total_logical_writes/execution_count) AS [Média de escritas lógicas],

 (total_physical_reads/execution_count) AS [Média de leituras físicas],

  execution_count,

  plan_handle,

  (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

          THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

          ELSE statement_end_offset

     END - statement_start_offset)/2)

   FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY (total_logical_reads + total_logical_writes) DESC;

Lembrando que os scripts acima funcioname apenas no SQL SERVER 2005 ou superior.

Semana que vem, falaremos sobre as diferenças entre atribuições com SET e SELECT. Não perca!

Problema com JOB de agendamento de pacote DTSX

Consultado por um colaborador em relação a um problema em um JOB do Sql Server 2008, verifiquei o JOB History e verifiquei a seguinte mensagem de erro:

"Executed as user: USR The package execution failed.  The step failed."

Adicionei o role Sysadmin ao server roles do usuário relacionado a execução do pacote e o JOB funcionou.

Até a próxima!

Recursos do Sql Server - Parte 2

A tabela Sys.Columns, retorna uma linha para cada coluna relacionada a objetos que tem colunas como Views, Tabelas e funções que retornam tipos table. Sys.Columns apresenta diversas colunas com diferentes informações sobre as colunas.

Object_Id: Id do objeto ao qual a coluna pertence. Um jeito rápido de listar todas as colunas de uma tabela, sabendo apenas o nome da tabela, é fazer select em Sys.Columns usando a função Object_id.

Select * from Sys.Columns

Where Object_id=Object_Id('NomeDaTabela')

Esta coluna é do tipo int.

Name: Nome da coluna. Coluna do tipo Sys.Name

Column_Id: Id da coluna. É único no escopo do objeto ao qual a coluna está relacionada, porém pode não ser seqüencial. Coluna do tipo int.

System_type_id: Id do type da coluna definido pelo sistema. Coluna do tipo TinyInt.

User_type_id: Id do type da coluna definido pelo usuário. Para ver a descrição da coluna, é possível fazer um join com a tabela Sys.types.  Coluna do tipo int.

Max_length: Comprimento máximo, em bytes, da coluna. Para colunas com do tipo varchar(max), nvarchar(max), varbinary(max), ou XML esta coluna tem o valor -1. Para coluna do tipo Text, este campo tem o valor 16, ou o valor definido pela configuração ‘text in row’, definida pela procedure sp_tableoption.  Coluna do tipo Smallint.

Precision:  Precisão da coluna se esta for do tipo numérico com ponto flutuante, caso contrário é zero. Se definirmos uma coluna em uma tabela do tipo Numeric(10,2), precision será igual a 10.

Scale:  Scale da coluna se esta for do tipo numérico com ponto flutuante, caso contrário é zero. Se definirmos uma coluna em uma tabela do tipo Numeric(8,3), scale será igual a 3.

Collation_name: Nome do collate ao qual a coluna está associada, caso seja do tipo caracter (varchar por exemplo). Caso não seja do tipo caracter, esta coluna apresenta null.

Is_nullable: Campo do tipo bit, apresenta valor 1 se a coluna aceitar valores nulos, e zero caso contrário.

Is_ansi_padded:  Campo do tipo bit, apresenta valor 1 se a coluna tem o comportamento ANSI PADDING, e zero caso contrário.

Is_rowguidcol: Campo do tipo bit, apresenta valor 1 se a coluna está definida como ROWGUIDCOL, e zero caso contrário.

Is_identity: Campo do tipo bit, apresenta valor 1 se a coluna está definida como Identity, e zero caso contrário.

Is_Computed: Campo do tipo bit, apresenta valor 1 se a coluna é um campo calculado, e zero caso contrário.

Is_FileStream: Esta coluna está reservada pela Microsoft para futuras implementações.

Is_Replicated: Campo do tipo bit, apresenta valor 1 se a coluna é definida como Replicated, e zero caso contrário.

Is_non_sql_subscribed: Campo do tipo bit, apresenta valor 1 se a coluna tem um assinante que não é o Sql Server, e zero caso contrário.

Is_merge_published: Campo do tipo bit, apresenta valor 1 se a coluna é publicada por mesclagem, e zero caso contrário.

Is_dts_replicated: Campo do tipo bit, apresenta valor 1 se a coluna é replicada através do Integration Services, e zero caso contrário.

Is_xml_document: Campo do tipo bit, apresenta valor 1 se a coluna contém um documento XML completo, e zero caso contrário.

Xml_Collection_id: Campo do tipo int, será diferente de zero caso o type da coluna seja definido como XML e se este XML for tipado. Zero caso contrário.

Default_object_id: Caso a coluna tenham algum tipo de valor padrão definido por um Default, esta coluna apresentará valor diferente de 0.

Rule_object_id: Id da regra associada a coluna.  

Continuaremos falando sobre mais tabelas e recusos do sistema.

Até a próxima!

Estudo de Caso da Nasdaq

Segue o link para o estudo de caso da Nasdaq, que migrou de antigos mainfram para o Sql Server 2005 com servidores Dell Power EDGE.

A aplicação suporta 5000 transações por segundo e 100.000 querys diárias.

Recursos do Sql Server - Parte 1

Hoje vamos analisar a view de sistema, chamada Sys.Objects. Esta view lista todos os objetos criados na instância onde a query é executada.

A estrutura da view compreende os seguintes campos:

Name: Nome do objeto. Se criarmos uma tabela no banco de dados, e nomeá-lo como "Clientes", esta descrição aparecerá na view quando esta for acessada. Do tipo "Sysname".

Object_id: Código de identificação do objeto. É único dentro de um banco de dados. Dica: Para obter

o Id de um objeto com rápidez, podemos usar a função Object_ID, passando como parâmetro o nome do objeto (com o seu respectivo Schema). Do tipo INT.

Principal_Id: Id do owner do objeto, caso este seja diferente do owner do Schema. Por padrão, objetos

que podem ser contidos em um Schema são criados no Schema padrão, porém um owner diferente pode ser designado usando o comando ALTER AUTHORIZATION. Do tipo INT.

Principal_id é sempre nulo para os seguintes objetos:

C = CHECK constraint

D = DEFAULT

F = FOREIGN KEY (Chave estrangeira)

PK = PRIMARY KEY (Chave primária)

R = Rule (Regra)

TA = Assembly (CLR-integration) trigger

TR = SQL trigger

UQ = UNIQUE constraint (Constraint única)

Schema_id: É o Id do Schema ao qual o objeto está vinculado. Podemos visualizar os schemas criados no Database através de uma outra view, Sys.Schemas. Do tipo INT.

parent_object_id: é o Id do objeto, ao qual o objeto em questão pertence. É o caso de constraints de chave primária e estrangeira por exemplo. Quando o objeto não está vinculado a outro, o valor deste campo é zero. Do tipo INT.

Dica: Podemos listar todas as constrains de uma tabela com a seguinte query:

      select * from sys.objects

      where parent_object_id = object_id(N'MonitoramentoColeta.tb_janela_agendamento')

Type: Trata do tipo de objeto classificado:

AF = Função de agregação camada CLR

C  = CHECK constraint

D  = DEFAULT

F  = Constraint de chave estrangeira

PK = Constraint de chave primária

P  = Stored Procedure

PC = Assembly stored procedure da camada CLR

FN = Função Scalar do SQL

FS = Função Scalar da CLR

FT = Função do tipo table da CLR

R  = Regra (Rule)

RF = Replication-filter-procedure

S  = Tabela do sistema

SN = (Sinônimo) Synonym

SQ = Serviço da fila

TA = Trigger da CLR

TR = Trigger

IF = Função inline do SQL Server

TF = Função do tipo table do SQL Server

U  = Tabela definida pelo usuário

UQ = Constraint do tipo unique

V  = View

X  = stored procedure do tipo Extended

IT = Tabela interna do Sql Server

Este campo é um Char com 2 caracteres.

type_desc: Descrição do tipo do objeto. Este campo é do tipo nvarchar com tamanho máximo de 60 caracteres.

create_date: Data de criação do objeto

modify_date: Data da última modificação

is_ms_shipped: Objeto é criado por um componente interno do Sql Server (geralmente TRUE quando se trata de tabela interna do SQL SERVER). Campo do tipo BIT.

is_published: Objeto é publicado. Campo do tipo BIT.

is_schema_published: True quando o SCHEMA owner do objeto é publicado. Campo do tipo BIT.

Semana que vêm vamos analisa a view Sys.Columns detalhadamente.

Blog do Alexandre Lopez

O blog do Alexandre Lopez está muito bom, com notícias, informações sobre administração do Sql Server e alguns códigos em T-SQL.

Vale a pena!

Microsoft lança ferramenta para migração do MySql para o Sql Server

A Microsoft apresentou esta semana, uma ferramenta que permite aos usuários do MySql, migrar as suas aplicações para o Sql Server. O "SQL Server Migration Assistant (SSMA) for MySQL" surge exatamente com a intenção de minimizar o esforço e o risco de migração entre os dois SGBDs.

http://www.microsoft.com/downloads/details.aspx?FamilyID=0e6168b0-2d0c-4076-96c2-60bd25294a8e&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=c6f14640-da22-4604-aaaa-a45de4a0cd4a&displaylang=en

Repetindo uma query com o comando GO

Você sabia que é possível repetir um comando Sql utilizando o comando GO e informando o número de execuções desejadas? O comando abaixo:

Select top 2 * from sys.objetcs
GO 5

Produz o seguinte resultado:

Scripts do Livro Inside Query Tuning and Otimization

O Livro Inside Query Tuning, disponibiliza on-line alguns scripts que são vistos no livro.

Clique aqui para seguir link.

Resolvendo problemas de paralelismo no Sql Server 2000

A partir da versão 2000, o Sql Server pode fazer uso de mais de um processador para encontrar o melhor execution plan para as querys. Porém, em determinadas situações, o optmizer pode se perder e os paralelismos podem ocorrer, gerando diversos problemas, inclusive de performance.

Foi o que ocorreu com um colega de trabalho, recém contratado pela empresa:

Nem sempre a solução para este problema é a mesma, porém resolvemos o problema, adicionando  ao final da query, o hint MAXDOP 1.

Como se trata de um query hint, a sintaxe correta é:

SELECT

  a.codigo,

  b.nome

FROM

  Tabela1 a

INNER JOIN Tabela2 b ON (a.codigo=b.codigo)

OPTION (MAXDOP 1)

 

Até a próxima!

Resolvendo conflitos de COLLATE

Já passei por diversos problemas com COLLATE de banco de dados, mas somente hoje decidi apresentar uma solução para o problema.

Ao executar uma query similar a apresentada abaixo:

SELECT            

    F.ID_codigo

   ,sum(F.vl_devido)            

    ,Max(F.qt_dia_atraso)           

    ,Max(F.nu_behavior_score) 

FROM

    ##tb_tabela_global F (nolock)  

WHERE

    AND F.nu_digito_cpf IN (select Final from #FinaisCPF)           

    AND F.cd_tipo_pessoa IN (select TP_PESSOA from #TipoPesssoaFisicaJuridica (Nolock))  

GROUP BY

    F.ID_codigo 

HAVING

    SUM(F.vl_devido) BETWEEN 0.00 AND 999999999.99

Debugando o código, descobri que o problema estava no segundo IN:

    AND F.cd_tipo_pessoa IN (select TP_PESSOA from #TipoPesssoaFisicaJuridica (Nolock))  

O código resulta o seguinte erro:

"Cannot resolve collation conflict for equal to operation"

Resolvi o problema, alterando o COLLATE dos campos da query:

AND F.cd_tipo_pessoa COLLATE DATABASE_DEFAULT IN (select TP_PESSOA COLLATE DATABASE_DEFAULT from #TipoPesssoaFisicaJuridica (Nolock))  

Até a próxima!