DBADon's questions

Versão curta

Depois de habilitar AUTO_CREATE_STATISTICS& AUTO_UPDATE_STATISTICS_ASYNCem um banco de dados, qual métrica de desempenho exibirá melhor as alterações?

Versão longa

Acabamos de perceber que o banco de dados de um sistema legado nunca teve o AUTO_CREATE_STATISTICS& AUTO_UPDATE_STATISTICS_ASYNCativado ( AUTO_UPDATE_STATISTICSfoi ativado).

Como seria de esperar, depois de ativá-los, nossos aplicativos tiveram um desempenho muito melhor. Os tempos de resposta são significativamente melhores e os usuários não esperam que a tela seja atualizada ou as páginas sejam carregadas.

O banco de dados está bem indexado e já havíamos criado estatísticas manualmente onde achamos que eram necessárias. Imediatamente após a ativação, verifiquei sys.stats(juntei-me ao sys.stats_columns& sys.objects) e só precisava criar estatísticas em três objetos. (Eu tenho isso registrado caso a quantificação dessas mudanças esteja com eles.)

Capturo muitos dados de desempenho neste servidor e examinei esses dados antes e depois da alteração, mas ainda não identifiquei onde estão os ganhos reais.

Eu tenho outros sistemas semelhantes em que as estatísticas automáticas ainda estão desativadas para esse banco de dados. Esses sistemas também se beneficiariam de habilitá-los, mas estou aguardando caso haja alguma métrica que ainda não tenha, posso adicioná-la antes de fazer as alterações.

Minha verdadeira pergunta

Além da experiência do usuário, como quantifico os aumentos feitos alterando as configurações de Auto-Stats em um sistema?

SQL Server 2008R2 no Windows Server 2008.

Desabilitar o Agent XPs está impedindo que sp_add_job seja executado com êxito.

Durante a manutenção, nosso software está desabilitando os Agent XPs. Isso precisa ser corrigido, mas TODOS os nossos outros sites continuam sem problemas.

Fizemos alguns testes entre sistemas, o teste consistindo em: desabilitar o Agent XPs, executar EXEC sp_add_job 'hello there';(Isso cria um trabalho com o nome especificado).

Descobrimos que sp_add_jobfalha neste site solitário, mas em todos os outros lugares o trabalho é adicionado muito bem.

Comparamos o código sp_add_jobentre sites comparáveis ​​e o código é idêntico. As permissões também parecem ser as mesmas.

Tentamos depurar o procedimento executando-o como um script, mas curiosamente isso produziu o mesmo resultado quebrado em todos os sistemas (mesmo aqueles que estão funcionando).

Algo está permitindo sp_add_jobfuncionar bem em qualquer outro lugar, mas impedindo que funcione em um site.

use msdb
go

declare @jobname sysname
if exists (select * from sysjobs where name = 'test_with_procedure_12345')
    EXEC msdb.dbo.sp_delete_job @job_name=N'test_with_procedure_12345', @delete_unused_schedule=1

if exists (select * from sysjobs where name = 'test_without_procedure_12345')
    EXEC msdb.dbo.sp_delete_job @job_name=N'test_without_procedure_12345', @delete_unused_schedule=1

exec sp_configure 'agent xps', 0
reconfigure

print '          1. running with procedure' -- Only one site fails here, the rest all work

exec sp_add_job 'test_with_procedure_12345'
select @jobname = name from sysjobs where name = 'test_with_procedure_12345'
print '             job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
print '          2. after running with procedure'

exec sp_configure 'agent xps', 1
reconfigure

print '          3. state reset'

exec sp_configure 'agent xps', 0
reconfigure

print '          4. running without procedure' 


--CREATE PROCEDURE sp_add_job  
DECLARE
  @job_name                     sysname = 'test_without_procedure_12345',  
  @enabled                      TINYINT          = 1,        -- 0 = Disabled, 1 = Enabled  
  @description                  NVARCHAR(512)    = NULL,  
  @start_step_id                INT              = 1,  
  @category_name                sysname          = NULL,  
  @category_id                  INT              = NULL,     -- A language-independent way to specify which category to use  
  @owner_login_name             sysname          = NULL,     -- The procedure assigns a default  
  @notify_level_eventlog        INT              = 2,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_email           INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_netsend         INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_level_page            INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @notify_email_operator_name   sysname          = NULL,  
  @notify_netsend_operator_name sysname          = NULL,  
  @notify_page_operator_name    sysname          = NULL,  
  @delete_level                 INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always  
  @job_id                       UNIQUEIDENTIFIER = NULL ,--OUTPUT,  
  @originating_server           sysname           = NULL      -- For SQLAgent use only  
--AS  
BEGIN  
  DECLARE @retval                     INT  
  DECLARE @notify_email_operator_id   INT  
  DECLARE @notify_netsend_operator_id INT  
  DECLARE @notify_page_operator_id    INT  
  DECLARE @owner_sid                  VARBINARY(85)  
  DECLARE @originating_server_id      INT  

  SET NOCOUNT ON  

  -- Remove any leading/trailing spaces from parameters (except @owner_login_name)  
  SELECT @originating_server           = UPPER(LTRIM(RTRIM(@originating_server)))  
  SELECT @job_name                     = LTRIM(RTRIM(@job_name))  
  SELECT @description                  = LTRIM(RTRIM(@description))  
  SELECT @category_name                = LTRIM(RTRIM(@category_name))  
  SELECT @notify_email_operator_name   = LTRIM(RTRIM(@notify_email_operator_name))  
  SELECT @notify_netsend_operator_name = LTRIM(RTRIM(@notify_netsend_operator_name))  
  SELECT @notify_page_operator_name    = LTRIM(RTRIM(@notify_page_operator_name))  
  SELECT @originating_server_id        = NULL  

  -- Turn [nullable] empty string parameters into NULLs  
  IF (@originating_server           = N'') SELECT @originating_server           = NULL  
  IF (@description                  = N'') SELECT @description                  = NULL  
  IF (@category_name                = N'') SELECT @category_name                = NULL  
  IF (@notify_email_operator_name   = N'') SELECT @notify_email_operator_name   = NULL  
  IF (@notify_netsend_operator_name = N'') SELECT @notify_netsend_operator_name = NULL  
  IF (@notify_page_operator_name    = N'') SELECT @notify_page_operator_name    = NULL  

  IF (@originating_server IS NULL) OR (@originating_server = '(LOCAL)')  
    SELECT @originating_server= UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  

  --only members of sysadmins role can set the owner  
  IF (@owner_login_name IS NOT NULL AND ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME())  
  BEGIN  
    RAISERROR(14515, -1, -1)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1
  END  

  -- Default the owner (if not supplied or if a non-sa is [illegally] trying to create a job for another user)  
  -- allow special account only when caller is sysadmin  
  IF (@owner_login_name = N'$(SQLAgentAccount)')  AND   
     (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)  
  BEGIN  
    SELECT @owner_sid = 0xFFFFFFFF     
  END  
  ELSE   
  IF (@owner_login_name IS NULL) OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME()))  
  BEGIN  
    SELECT @owner_sid = SUSER_SID()  
  END  
  ELSE  
  BEGIN      --force case insensitive comparation for NT users  
    SELECT @owner_sid = SUSER_SID(@owner_login_name, 0) -- If @owner_login_name is invalid then SUSER_SID() will return NULL  
  END  

  -- Default the description (if not supplied)  
  IF (@description IS NULL)  
    SELECT @description = FORMATMESSAGE(14571)  

  -- If a category ID is provided this overrides any supplied category name  
  EXECUTE @retval = sp_verify_category_identifiers '@category_name',  
                                                   '@category_id',  
                                                    @category_name OUTPUT,  
                                                    @category_id   OUTPUT  
  IF (@retval <> 0)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1

  -- Check parameters  
  EXECUTE @retval = sp_verify_job NULL,  --  The job id is null since this is a new job  
                                  @job_name,  
                                  @enabled,  
                                  @start_step_id,  
                                  @category_name,  
                                  @owner_sid                  OUTPUT,  
                                  @notify_level_eventlog,  
                                  @notify_level_email         OUTPUT,  
                                  @notify_level_netsend       OUTPUT,  
                                  @notify_level_page          OUTPUT,  
                                  @notify_email_operator_name,  
                                  @notify_netsend_operator_name,  
                                  @notify_page_operator_name,  
                                  @delete_level,  
                                  @category_id                OUTPUT,  
                                  @notify_email_operator_id   OUTPUT,  
                                  @notify_netsend_operator_id OUTPUT,  
                                  @notify_page_operator_id    OUTPUT,  
                                  @originating_server         OUTPUT  
  IF (@retval <> 0)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1


  SELECT @originating_server_id = originating_server_id   
  FROM msdb.dbo.sysoriginatingservers_view   
  WHERE (originating_server = @originating_server)  
  IF (@originating_server_id IS NULL)  
  BEGIN  
    RAISERROR(14370, -1, -1)  
    --RETURN(1) -- Failure  
    --NOTE: replaced with select to run outside of original sp_add_job procedure
    SELECT 1
  END  


  IF (@job_id IS NULL)  
  BEGIN  
    -- Assign the GUID  
    SELECT @job_id = NEWID()  
  END  
  ELSE  
  BEGIN  
    -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)  
    IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')  
    BEGIN  
      RAISERROR(14274, -1, -1)  
      --RETURN(1) -- Failure  
      --NOTE: replaced with select to run outside of original sp_add_job procedure
      SELECT 1
    END  
  END  

  INSERT INTO msdb.dbo.sysjobs  
         (job_id,  
          originating_server_id,  
          name,  
          enabled,  
          description,  
          start_step_id,  
          category_id,  
          owner_sid,  
          notify_level_eventlog,  
          notify_level_email,  
          notify_level_netsend,  
          notify_level_page,  
          notify_email_operator_id,  
          notify_netsend_operator_id,  
          notify_page_operator_id,  
          delete_level,  
          date_created,  
          date_modified,  
          version_number)  
  VALUES  (@job_id,  
          @originating_server_id,  
          @job_name,  
          @enabled,  
          @description,  
          @start_step_id,  
          @category_id,  
          @owner_sid,  
          @notify_level_eventlog,  
          @notify_level_email,  
          @notify_level_netsend,  
          @notify_level_page,  
          @notify_email_operator_id,  
          @notify_netsend_operator_id,  
          @notify_page_operator_id,  
          @delete_level,  
          GETDATE(),  
          GETDATE(),  
          1) -- Version number 1  
  SELECT @retval = @@error  

  -- NOTE: We don't notify SQLServerAgent to update it's cache (we'll do this in sp_add_jobserver)  

  --RETURN(@retval) -- 0 means success  
  --NOTE: replaced with select to run outside of original sp_add_job procedure
  SELECT @retval as 'errorcode result'
END  

print '          5. after running without procedure'
set @jobname = null
select @jobname = name from sysjobs where name = 'test_without_procedure_12345'
print '             job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
exec sp_configure 'agent xps', 1
reconfigure

Se você executar o código anexado, o erro da etapa 4:

Msg 15281, Level 16, State 1, Procedure sp_verify_job, Line 2
SQL Server bloqueou o acesso ao procedimento 'dbo.sp_verify_job' do componente 'Agent XPs' porque este componente está desativado como parte da configuração de segurança para este servidor. Um administrador de sistema pode habilitar o uso de 'Agent XPs' usando sp_configure. Para obter mais informações sobre como habilitar 'Agent XPs', consulte "Configuração da área de superfície" nos Manuais Online do SQL Server.

Msg 515, Level 16, State 2, Line 178
Não é possível inserir o valor NULL na coluna 'category_id', tabela 'msdb.dbo.sysjobs'; coluna não permite nulos. INSERIR falha.

É o mesmo erro que estamos recebendo na etapa #2.