Zane's questions

好吧，我一直在从一些小文件中压缩文件，并将它们移动到我为这些杂项表创建的 FG 上。我一直在通过使用WITH (DROP_EXISTING = ON)选项重新创建索引来移动这些表，并且效果很好。然而，这最后一次它给了我一个错误。这是我正在运行的脚本

CREATE Unique CLUSTERED INDEX PK_MyTable 
    ON dbo.MyTable (MyTableID, RowStatus)  
    WITH (DROP_EXISTING = ON)  
    ON FG_Misc; 

当我运行它时，我收到以下错误消息。

消息 1902，级别 16，状态 3，第 1 行

不能在表 'dbo.MyTable' 上创建多个聚集索引。在创建另一个之前删除现有的聚簇索引 PK_MyTable'。

我看不出有任何原因会在这个特定的表上抛出这个错误，但我一直在处理的任何其他表上都没有。

所以我有一个基本的 SQL 代理作业，它运行一个 Robocopy 脚本来将所有文件从一个文件夹移动到另一个文件夹。

Job 是一个非常基本的设置。

有一个非常基本的时间表。

然而它还没有运行。我也不是说成功运行，我的意思是完全运行。有什么理由可能是这种情况吗？

有关其他信息，我还将编写工作脚本。

USE [msdb]
GO

/****** Object:  Job [MoveMantisFilesToArchive]    Script Date: 12/23/2015 10:21:52 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/23/2015 10:21:52 AM ******/
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'MoveMantisFilesToArchive', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Moves Mantis files to archive. It''s a very descriptive title.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_email_operator_name=N'MyEmailGroup', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Move the files in the afformentioned title.]    Script Date: 12/23/2015 10:21:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Move the files in the afformentioned title.', 
        @step_id=1, 
        @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'CmdExec', 
        @command=N'robocopy MySoruce MyDestination /mov', 
        @flags=0, 
        @proxy_name=N'RunsAs'
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_jobschedule @job_id=@jobId, @name=N'M-F', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=62, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20151218, 
        @active_end_date=99991231, 
        @active_start_time=170000, 
        @active_end_time=235959, 
        @schedule_uid=N'bcb83273-19e8-49fb-a456-8517642370e3'
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

所以我有这个查询，我正在尝试调整它，但遇到了一个我无法理解的问题。首先是我正在使用的查询。

SELECT  
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

当我运行此查询时，我得到的执行计划如下所示。

所以查询正在对这个返回 3000 万行的大表进行非常大的扫描，只是为了将其减少到 3K。它使用的索引看起来像这样。

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

我改变了查询方式，通过将谓词的日期部分移动到初始查询之外，从而提供更好的性能。

select
    *
from (
    SELECT  
        si.LoanNbr AS [LoanNumber],
        fi.[SvcClientNbr] AS ClientID,
        si.LoanMasterID,
        si.LoanSrcCode AS [LoanSourceCode],
        fi.LoanPurpCode,
        fi.[PropState] AS [Property State],
        im.ImagedocumentID AS [Image Document ID],
        CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
        im.[PageCount],
        im.[SignatureInd]  
    FROM dbo.NotMybaseTable Si
    INNER JOIN dbo.NotMyTableName fi
      ON si.LoanMasterID = fi.LoanMasterID
    INNER JOIN [dbo].[ImagedDocument] im
      ON si.loanmasterid = im.loanmasterid
      AND im.[DocTypeCode] = '10112'
    WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
            WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
        ELSE 0 END = 1
        ) as F
WHERE [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

这带来了更好的性能和明显更优的查询计划。

它正在扫描的索引现在看起来像这样。

CREATE NONCLUSTERED INDEX [IX_ImageLoanDoc_SystemName_ICMPDocTypeCode] ON [dbo].[ImageDoc]
(
    [SystemName] ASC,
    [ICMPDocTypeCode] ASC,
    [LoanMasterID] ASC,
    [ImageDate] ASC
)
INCLUDE (   [ImageDocumentID],
    [ImageDocType],
    [BatchName],
    [SignatureInd],
    [ICMPDocCategoryCode],
    [ICMPDocSubTypeCode])

所以这是我的问题。由于 ImageDate 在此索引中，为什么在我的查询中包含它会严重损害性能？包含该谓词难道不应该更容易消除行吗？我在做什么/想错了什么？

我有一个 SSIS 包，它正在执行一个简单的SELECT语句，然后加载到一个表中。我已经匿名化了 sql 语句，当您查看表名时，这应该是显而易见的。为了这个演示的目的，我还简化了包以只计算行数。

我遇到的这个问题是查询在 SQL Server 中生成行但在 SSIS 中不生成任何行。查询如下。

DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT 
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)

请忽略 *，因为它仅用于演示目的。实际查询有同样的问题，即使它没有 *。在 SQL Server 中执行时，这会按预期从日历表中返回 1 行。但是，当在 SSIS 中针对相同环境执行时，我没有返回任何行。

如果我将查询转换为存储过程并在 SSIS 中执行存储过程，我得到的行数为 1。

如果我将查询更改为此。

SELECT 
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' )) AS DATE)

然后我再次收到 1 行。

有什么理由让我看到这种行为吗？

更新：在我收到大量批评之前，我对 NOLOCK 废话别无选择。

好的，所以我有一份报告进行了本周与上周的比较，我们的客户注意到他们的数据“时髦”。经过进一步调查，我们发现它没有按照 ISO 标准正确运行数周。我将此脚本作为测试用例运行。

SET DATEFIRST 1
SELECT DATEPART(WEEK, '3/26/13')
    , DATEPART(WEEK, '3/27/12')
    , DATEPART(WEEK, '3/20/12')
    , DATEPART(WEEK, '1/2/12')
SELECT DATEPART(ISO_WEEK, '3/26/13')
    , DATEPART(ISO_WEEK, '3/27/12')
    , DATEPART(ISO_WEEK, '3/20/12')
    , DATEPART(ISO_WEEK, '1/2/12')

运行时我得到了这些结果。

我认为这很奇怪，所以我进行了更多挖掘，发现 SQL Server 将 1 月 1 日计为一年中的第一周，而 ISO 将 1 月的第一个星期日计为一年中的第一周。

然后问题最终变成了两个问题。问题1 为什么会这样？问题 2 有没有办法改变它，所以我不必修改我的所有代码以ISO_Week在任何地方使用？

我有四台相同的服务器 2 用于生产，另外两台是演示机/紧急故障转移盒。更强大的机器是 16 核 127 gigs 的 Ram，它同时托管我们的 Datawarehouse 和我们的 OLTP 数据库。功能较弱的盒子是一个 8 核 98 gigs 的 ram 盒子，我们用于 ETL 和我们的数据存储。我遇到的问题是生产服务器集成服务需要大量时间来执行包验证。对于在 6 秒内返回的查询，完成包验证需要 40 秒。如您所见，这是一个相当简单的包。

请记住，这只是我作为示例使用的包，我的所有包都有相同的问题，尽管程度较轻。当我运行它时，验证部分平均需要 40 秒。

为了更好地测试这一点，我在姐妹盒子上运行了完全相同的包，它运行与此盒子相同的所有 SQL 作业。仅让执行前验证需要 0 秒，查询需要 4 到 6 秒。

为了进一步测试这一点，我对 SQL 服务器的两个实例运行了查询。

SELECT '{' + CAST(Store_Id AS CHAR(36)) + '}' AS Store_Id
      ,'{' + CAST(ItemGroupDetail_Id AS CHAR(36)) + '}' AS ItemGroupDetail_Id
      ,ItemGroupType_Id
      ,'{' + CAST(Concept_Id AS CHAR(36)) + '}' AS Concept_Id
      ,'{' + CAST(Company_Id AS CHAR(36)) + '}' AS Company_Id
      ,'{' + CAST(ItemGroup_Id AS CHAR(36)) + '}' AS ItemGroup_Id
      ,GroupName
      ,Sort
      ,NameOverride
      ,'{' + CAST(ParentGroup_Id AS CHAR(36)) + '}' AS ParentGroup_Id
      ,Active
      ,'{' + CAST(UserCreated AS CHAR(36)) + '}'  AS UserCreated
      ,'{' + CAST(UserModified AS CHAR(36)) + '}'  AS UserModified
  FROM bos_vAllItemGroups

在具有最新日期的主服务器上执行平均在 5 秒内返回 63326 行，在 3 秒内返回 50527 行。这是可以预料的，这台服务器的活动要少得多，但我希望它会稍微快一些。我没想到的是包验证时间比实际查询运行时间增加得更多。

经过昨天的大量挖掘，我想我可能已经发现了这个问题，但不知道如何证明它。生产 OLTP 数据库管理全国各地的数字菜单板的内容，这些数字菜单板经常打电话回家以检查新内容。大多数情况下，没有什么可改变的，要找出的查询会在几毫秒内完成。但是，他们确实发送了有关此过程的信息，最终平均每秒运行 4500 个查询。这可能是我的问题的原因吗？来自我们的网络服务器的大量交易。如果是，我该如何证明？我一直在尝试让这些数据库在不同的机器上运行，因为这个 OLTP 数据库和我们的数据仓库一样增长得非常快，并且从我所有的阅读来看，将 OLAP 和 OLTP 数据库保持在同一台服务器上被认为是不好的做法。

我有一个查询用于填充聚合表以进行报告。该查询来自我工作的公司的另一位开发人员，但我的工作是让它快速运行。到目前为止，我所有的早期尝试都失败了。我已经用这个查询尝试了几件事，这就是我目前所处的位置。我已经缩短了大约半小时的加载时间，但我被卡住了，我想我可能只需要重新做整个事情。我希望这里有人可以看到如果我遗漏了什么，并给我一些关于如何修复此查询的指示。

SELECT P.CompanyID,
       P.CompanyName,
       P.StoreID,
       P.StoreName,
       P.ReportDate,
       Isnull((SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS PullNet
               FROM   FactSalesTransaction AS FT
                      LEFT JOIN (SELECT TransactionID,
                                        DimStoreID,
                                        DimBusinessDateID,
                                        Sum(PaymentAmount) AS PaymentAmount
                                 FROM   FactSalesPayment
                                 WHERE  DimPaymentTypeID <> 2
                                        AND ModStatusFlg <> 'D'
                                 GROUP  BY TransactionID,
                                           DimStoreID,
                                           DimBusinessDateID) AS FP
                        ON FP.TransactionID = FT.TransactionID
                           AND FP.DimStoreID = FT.DimStoreID
                      INNER JOIN DimCalendar AS C
                        ON FT.DimBusinessDateID = C.DimCalendarID
                           AND FP.DimBusinessDateID = C.DimCalendarID  AND C.CalendarDate >= '12/4/2012'
               WHERE  FT.DimStoreID = P.DimStoreID
                      AND FT.DimBusinessDateID = P.DimBusinessDateID

                      AND FT.ModStatusFlg <> 'D'), 0) AS StoreCash,
       SR.CashDeposit AS StoreResp,
       SN.StoreNet,
       P.DimEmployeeID AS EmpID,
       P.EmpName,
       P.RegisterID,
       P.PullNumber,
       Isnull((SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS PullNet
               FROM   FactSalesTransaction AS FT
                      LEFT JOIN (SELECT TransactionID,
                                        DimStoreID,
                                        DimBusinessDateID,
                                        Sum(PaymentAmount) AS PaymentAmount
                                 FROM   FactSalesPayment
                                 WHERE  DimPaymentTypeID <> 2
                                        AND ModStatusFlg <> 'D'
                                 GROUP  BY TransactionID,
                                           DimStoreID,
                                           DimBusinessDateID) AS FP
                        ON FP.TransactionID = FT.TransactionID
                           AND FP.DimStoreID = FT.DimStoreID
                      INNER JOIN DimCalendar AS C
                        ON FT.DimBusinessDateID = C.DimCalendarID
                           AND FP.DimBusinessDateID = C.DimCalendarID AND C.CalendarDate >= '12/4/2012'
               WHERE  FT.DimStoreID = P.DimStoreID
                      AND FT.DimRegisterID = P.DimRegisterID

                      AND FT.TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime
                      AND FT.ModStatusFlg <> 'D'), 0) AS PullCash,
       P.PullResp + Isnull((SELECT Sum(SkimAmount)
                            FROM   FactSkims
                            WHERE  DimStoreID = P.DimStoreID
                                   AND DimRegisterID = P.DimRegisterID
                                   AND SkimDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime), 0) AS PullResp,
       Isnull((SELECT Sum(NetSales) AS PullNet
               FROM   FactSalesTransaction AS FT
                      INNER JOIN DimCalendar AS C
                        ON FT.DimBusinessDateID = C.DimCalendarID AND C.CalendarDate >= '12/4/2012'
               WHERE  DimStoreID = P.DimStoreID
                      AND DimRegisterID = P.DimRegisterID
                      AND TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime

                      AND ModStatusFlg <> 'D'), 0)                                                             AS PullNet
FROM   (SELECT C.CompanyID,
               C.CompanyName,
               S.StoreID,
               S.StoreName,
               F.DimEmployeeID,
               E.FirstName + ' ' + E.LastName                           AS EmpName,
               CASE
                 WHEN F.PullDrawerStartTime <> '1900-01-01' THEN F.PullDrawerStartTime
                 ELSE Isnull(Cast((SELECT TOP 1 Dateadd(SECOND, 1, PullDrawerEndTime)
                                   FROM   FactPullDrawer
                                   WHERE  PullDrawerEndTime < F.PullDrawerEndTime
                                          AND DimStoreID = F.DimStoreID
                                          AND DimRegisterID = F.DimRegisterID
                                          AND DimBusinessDateID = F.DimBusinessDateID
                                   ORDER  BY PullDrawerEndTime DESC) AS DATETIME), BD.CalendarDate + Isnull(Cast(Cast(ST.SiteSettingValue AS TIME) AS DATETIME), Cast('4:00:00 AM' AS DATETIME)))
               END AS PullDrawerStartTime,
               F.PullDrawerEndTime,
               BD.CalendarDate AS ReportDate,
               R.RegisterID,
               R.DimRegisterID,
               (SELECT Count(PullDrawerEndTime)
                FROM   FactPullDrawer
                WHERE  PullDrawerEndTime < F.PullDrawerEndTime
                       AND DimStoreID = F.DimStoreID
                       AND DimRegisterID = F.DimRegisterID
                       AND DimBusinessDateID = F.DimBusinessDateID) + 1 AS PullNumber,
               Isnull(F.Amount, 0)                                      AS PullResp,
               F.DimStoreID,
               F.DimBusinessDateID
        FROM   FactPullDrawer AS F
               INNER JOIN DimCompany AS C
                 ON C.DimCompanyID = F.DimCompanyID
               INNER JOIN DimStore AS S
                 ON S.DimStoreID = F.DimStoreID
               INNER JOIN DimCalendar AS BD
                 ON BD.DimCalendarID = F.DimBusinessDateID
                    AND BD.CalendarDate >= '12/4/2012'
               INNER JOIN DimEmployee AS E
                 ON F.DimEmployeeID = E.DimEmployeeID
               INNER JOIN DimRegister AS R
                 ON R.DimRegisterID = F.DimRegisterID
               LEFT JOIN DimSiteSettings AS ST
                 ON S.StoreID = ST.StoreID
                    AND C.CompanyID = ST.CompanyID
                    AND ST.SiteSettingFieldID = 1412) AS P
       INNER JOIN (SELECT DimStoreID,
                          DimBusinessDateID,
                          Sum(NetSales) AS StoreNet
                   FROM   FactSalesTransaction
                   WHERE  ModStatusFlg <> 'D'
                   GROUP  BY DimStoreID,
                             DimBusinessDateID) AS SN
         ON SN.DimStoreID = P.DimStoreID
            AND SN.DimBusinessDateID = P.DimBusinessDateID
       INNER JOIN (SELECT CompanyID,
                          StoreID,
                          ReportDate,
                          Sum(ValTotal) AS CashDeposit
                   FROM   AgtAccountingReport
                   WHERE  ReportCatOrder = 7
                          AND ReportElementOrder < 100
                          AND ReportElementOrder NOT IN ( 7, 9, 10, 16,17, 18, 19, 20, 21 )
                          AND ReportDate >= '10/28/2012'
                   GROUP  BY CompanyID,
                             StoreID,
                             ReportDate) AS SR
         ON SR.CompanyID = P.CompanyID
            AND SR.StoreID = P.StoreID
            AND SR.ReportDate = P.ReportDate 

我在想所有的嵌套SELECT的，这就是为什么我想我会从头开始。任何帮助，将不胜感激。

因此，有一个通过集成服务运行的 ETL 作业，我从 SQL 作业名称 StartStageStore 开始。该作业的最后一步是开始另一个作业并报告成功。此调用的作业称为 LoopStageStore。LoopStageStore 所做的只是执行一个存储过程，重新启动 StartStageStore 作业，这样我们就不断地将源数据拉入我们的系统。这项工作已经运行了几个星期，没有失败。直到最后两个晚上，我开始收到此错误“以用户身份执行：AgentUser。SQLServerAgent 错误：运行作业阶段/存储 AgentUser 的请求被拒绝，因为该作业已根据用户 AgentUser 的请求运行。[SQLSTATE 42000]（错误 22022） . 步骤失败。

这应该是不可能的，因为运行 StartStageStore 作业完成并且在 LoopStageStore 启动时不再运行。

在这里我们可以看到在 12:19:14 执行的最后一步。

并且下一个作业在 12:19:15 失败，说 StartStageStore 当前正在运行。我已经有一个解决方法，我只是在第二份工作上抛出延迟。我只是想知道是否有人知道为什么会发生这种情况。

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO