SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Kahn

Asked: 2013-11-06 02:38:35 +0800 CST2013-11-06 02:38:35 +0800 CST 2013-11-06 02:38:35 +0800 CST

SQL Server Agent: QUOTED_IDENTIFIER

772

O cenário é bastante simples: Um Job falha na produção, dando um erro "ALTER INDEX falhou porque as seguintes opções SET têm configurações incorretas: 'QUOTED_IDENTIFIER' ...".

A questão é: por que esse problema aparece na produção e não em nenhum de nossos ambientes de teste? Verificando DBCC USEROPTIONS, os resultados são exatamente os mesmos em todos os bancos de dados. Além disso, o script em si não contém aspas. Ele simplesmente reconstrói alguns índices e atualiza algumas estatísticas com fullscan.

Usando exatamente o mesmo trabalho em qualquer outro banco de dados, posso definir QUOTED_IDENTIFIER ON ou OFF, ou simplesmente não configurá-lo e deixá-lo executar nos padrões de conexão. E sempre funcionará independentemente. Mas na produção, parece que deve ser definido como ON ou OFF.

Estou perguntando isso porque, em termos de tempo, não posso testar isso uma vez por dia, preciso saber por que esse erro está acontecendo e como prever qual configuração é necessária e quando. A única diferença visível entre os bancos de dados de teste e de produção é o agrupamento, mas mesmo criando um banco de dados de teste para corresponder ao agrupamento na produção, o trabalho ainda funciona independentemente da configuração QUOTED_IDENTIFIER.

Obrigado!

3 respostas

Voted

Henry Lee · Answer 1 · 2013-11-06T05:27:51+08:00

Best Answer

Henry Lee

2013-11-06T05:27:51+08:002013-11-06T05:27:51+08:00

Acabei de me deparar com esse mesmo problema. Por padrão, a primeira instrução em uma etapa de trabalho é SET QUOTED_IDENTIFIER OFF- você pode ver isso em um rastreamento do Profiler. A solução é adicionar uma linha no início do seu código para SET QUOTED_IDENTIFIER ON.

Quanto ao seu script que não contém aspas - acredito que, ao criar ou reconstruir certos tipos de índices, a configuração necessária deve estar ativada.

Aqui estão alguns links:

https://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off

2

user36161 · Answer 2 · 2014-03-26T09:07:55+08:00

user36161

2014-03-26T09:07:55+08:002014-03-26T09:07:55+08:00

Descobri que o trabalho do SQL Agent define as opções do banco de dados com base no nível de compatibilidade do banco de dados e isso pode substituir as opções do banco de dados que foram definidas.

ou seja, o banco de dados tem opções de banco de dados SET QUOTED_IDENTIFIER ON, no entanto, o nível de compatibilidade do banco de dados é definido como SQL 2000 para que o SQL Job falhe (ou seja, ao tentar ATUALIZAR linhas na tabela que tem índice filtrado).

Mude o nível de compatibilidade para SQL 2008 e, em seguida, o trabalho SQL será bem-sucedido.

2

Marcello Miorelli · Answer 3 · 2015-05-30T08:52:30+08:00

Eu me deparo com o mesmo problema ao agendar um trabalho para criar índices. o que eu fiz é

1) - Eu executo este script para identificar qual é o valor atual das opções

SELECT  *
FROM
(
    SELECT  name,
        [Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN 'OFF' ELSE 'ON' END
    FROM    master.dbo.spt_values 
    WHERE       type='SOP'
        AND number > 0
) AS X

2) Modifiquei meu trabalho de criação de índice, adicionando a configuração de QUOTED_IDENTIFIER conforme necessário. Você pode ver isso no código de criação de trabalho abaixo. isso está funcionando atualmente.

--------------------------------------------------
-- server is SQLBOCSSLON1

-- this script creates an AD HOC job each step is a missing index in improving performance of 
--'spCustomerSearchBySurnameRaw'
--'spCustomerSearchByFirstNameAndSurnameRaw'

-- this file will be located at:
--B:\Projects\2015\SQLBOCSSLON1\SP Tuning\TEST\spCustomerSearchBySurnameRaw\20150529

--Marcelo Miorelli
--29-May-2015
--------------------------------------------------

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Ad Hoc - Create new Indexes - Bocss2', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]
ON [dbo].[tblBAddress] 
([blnIsCurrent], [strDeliveryPointSuffix], [strCountryCode], [strPostalCodeLookup]) 
INCLUDE (lngAddressID) WITH (ONLINE = ON)', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode] 
ON [dbo].[tblBAddress] ([blnIsCurrent]) 
INCLUDE (lngAddressID,strAccountCode) WITH (ONLINE = ON)', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames] 
ON [dbo].[tblBColdList] 
([sintMarketID], [strAddressLine3], [sdtmCopiedToAccount], [blnUsable], [strSurname], [strPostalCodeLookup]) 
INCLUDE (strAccountCode,strTitle,strFirstNames) WITH (ONLINE = ON)

', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]', 
        @step_id=4, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable], [strSurname]) INCLUDE (strAccountCode,sintMarketID,strTitle,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]', 
        @step_id=5, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable]) INCLUDE (strAccountCode,sintMarketID,strTitle,strSurname,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]', 
        @step_id=6, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode] 
ON [dbo].[tblBColdList] ([strFirstNames], [strPostalCodeLookup]) INCLUDE (strAccountCode) WITH (ONLINE = ON)

', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

SQL Server Agent: QUOTED_IDENTIFIER

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Conceder acesso a todas as tabelas para um usuário

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

SQL Server Agent: QUOTED_IDENTIFIER

3 respostas

relate perguntas