DBADon's questions

精简版

在数据库上启用AUTO_CREATE_STATISTICS&后AUTO_UPDATE_STATISTICS_ASYNC，什么性能指标最能显示更改？

长版

我们刚刚意识到遗留系统的数据库从未打开AUTO_CREATE_STATISTICS& AUTO_UPDATE_STATISTICS_ASYNC（AUTO_UPDATE_STATISTICS已打开）。

如您所料，打开这些后，我们的应用程序的性能要好得多。响应时间明显更好，用户无需等待屏幕刷新或页面加载。

数据库索引良好，我们已经在我们认为需要的地方手动创建了统计信息。启用后我立即检查sys.stats（加入sys.stats_columns& sys.objects），它只需要在三个对象上创建统计信息。（我已经记录了这些，以防量化这些变化取决于他们。）

我在这台服务器上捕获了很多性能数据，并查看了更改前后的数据，但我还没有确定实际收益在哪里。

我还有其他类似的系统，其中该数据库的自动统计仍然关闭。这些系统也将从启用它们中受益，但我正在等待以防有一些我还没有的指标，我可以在进行更改之前添加它。

我真正的问题

除了用户体验之外，我如何量化通过更改系统上的 Auto-Stats 设置所带来的增长？

Windows Server 2008 上的 SQL Server 2008R2。

禁用 Agent XPs 会阻止 sp_add_job 成功运行。

在维护期间运行时，我们的软件会禁用 Agent XP。这需要修复，但我们所有其他站点都可以继续正常运行，没有任何问题。

我们在系统之间做了一些测试，测试包括：禁用 Agent XP，运行EXEC sp_add_job 'hello there';（这会创建一个具有指定名称的作业）。

我们发现sp_add_job在这个单独的站点失败了，但在其他任何地方都可以很好地添加作业。

我们比较了sp_add_job可比较站点之间的代码，代码是相同的。权限似乎也相同。

我们试图通过将其作为脚本运行来调试该过程，但有趣的是，这在所有系统（即使是那些正在运行的系统）上都产生了相同的错误结果。

有些东西允许sp_add_job在其他地方正常运行，但阻止它在一个站点上工作。

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

如果执行附加的代码，步骤 #4 中的错误：

消息 15281，级别 16，状态 1，过程 sp_verify_job，第 2 行
SQL Server 阻止访问组件“Agent XPs”的过程“dbo.sp_verify_job”，因为此组件作为此服务器安全配置的一部分已关闭。系统管理员可以通过使用 sp_configure 启用“代理 XP”。有关启用“代理 XP”的详细信息，请参阅 SQL Server 联机丛书中的“外围应用配置”。

消息 515，级别 16，状态 2，第 178 行
无法将值 NULL 插入列“category_id”，表“msdb.dbo.sysjobs”；列不允许空值。插入失败。

与我们在第 2 步收到的错误相同。