SQL Server 2008 R2 中的領先/滯後實施：超出最大記憶體

December 17, 2015

背景
我正在嘗試建立一個“訪問”序列，其中如果在基本相同的地方General_Location（因此，如果在所有 A(n) 個位置都屬於“A”的Location序列中檢測到動物，則前 6 次檢測將作為訪問 1 (@A)，接下來作為訪問 2 (@B)，然後作為訪問 3 (@D)，然後訪問 4 (返回 @A)。
A1, A2, A3, A3, A3, A1, B2, D4, A2
General_Location
由於LAG並且在 SQL Server 2008R2 中不可用（在ing 子句LEAD中也不可用），我正在嘗試按照SQL Authority 部落格條目中所述的解決方法。UNBOUNDED PRECEDING``PARTITION
我遇到了以下記憶體問題（更不用說計算時間了）：
WITH s AS (
   SELECT
       RANK() OVER (PARTITION BY det.Technology, det.XmitID ORDER BY DetectDate ASC, ReceiverID ASC) as DetID,
       COALESCE(TA.AnimalID, det.Technology+'-'+cast(da.XmitID AS nvarchar), 'BSVALUE999') as AnimalID,
       det.Technology, det.XmitID, DetectDate, det.location as Location, RL.General_Location as GLocation, ReceiverID
   FROM
       Detections_with_Location as det JOIN
       Receiver_Locations as RL
           ON det.Location=RL.Location LEFT OUTER JOIN
       Tagged_Animal as TA
           ON det.AnimalID=TA.AnimalID
)
INSERT INTO ##ttOrder_det (AnimalID, Technology, XmitID, DD, Location, GLocation, ReceiverID, DetID, PrevDD, BinInc)
   SELECT 
       s1.AnimalID, --was a coalesce
       s1.Technology, s1.XmitID, s1.DetectDate, s1.Location, s1.GLocation, s1.ReceiverID,
       s1.DetID, 
       sLAG.DetectDate,
       CASE WHEN sLAG.DetectDate IS NULL
           THEN 1 
           ELSE CASE WHEN sLAG.GLocation = s1.GLocation
               THEN 0
               ELSE 1
           END
       END AS BinInc
   FROM s as s1
   LEFT OUTER JOIN s AS sLAG ON
       s1.DetID = sLAG.DetID + 1 AND
       s1.AnimalID= sLAG.AnimalID --and s.Technology=sLAG.Technology and s.XmitID=sLAG.XmitID;
正如各種使用者（@MartinSmith、@Frisbee）已經提到或暗示的那樣，使用AnimalID不是的完整主鍵Tagged_Animal，也不是在唯一約束中定義的。但是，表中的行數A.AnimalID=B.AnimalID AND A.TagSN<B.TagSN（目前）為零。為了使這個查詢健壯，我必須強制它是唯一的（或者只是從 PK 中刪除 TagSN）。
表和索引定義
## ttOrder_det（臨時表）
目前在填充表格之前創建了索引；我正在進行測試，在填充臨時表後將NONCLUSTERED非索引創建轉移到一個位置。UNIQUE
CREATE TABLE ##ttOrder_det (
   AnimalID nvarchar(50) not null,
   Technology varchar(25) not null,
   XmitID int not null,
   DD DateTime not null,
   [Location] [nvarchar](255) NULL,
   [GLocation] nvarchar(255) NULL,
   PrevDD DateTime NULL,
   ReceiverID int not null,
   DetID int NOT NULL,
   BinInc int NULL,
   BinNum int NULL,
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
   ([AnimalID] ASC, [DD] ASC, ReceiverID ASC)
   WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON ##ttOrder_det (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON ##ttOrder_det (AnimalID ASC, DD ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
CREATE NONCLUSTERED INDEX NIX_A ON ##ttOrder_det (GLocation ASC);
CREATE NONCLUSTERED INDEX NIX_DD ON ##ttOrder_det (DD, PrevDD);
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON ##ttOrder_det (BinInc ASC);
CREATE NONCLUSTERED INDEX NIX_CT ON ##ttOrder_det (XmitID ASC, Technology ASC);
標記_動物
CREATE TABLE [dbo].[Tagged_Animal](
   [DateTagged] [datetime] NULL,
   [AnimalID] [nvarchar](50) NOT NULL,
   [TagSN] [nvarchar](50) NOT NULL,
   [XmitID] [int] NULL,
   [Technology] [varchar](25) NULL,
   [Animal_SubType] [nvarchar](50) NULL,
   [Species] [nvarchar](30) NULL,
   [StudyID] [nvarchar](50) NULL,
   [Weight] [float] NULL,
   [Length] [int] NULL,
   [Length_Type] [nvarchar](50) NULL,
   [Date_Released] [datetime] NULL,
   [Release_Location] [nvarchar](50) NULL,
   [Lat] [float] NULL,
   [Lon] [float] NULL,
   [Course_Dist_km] [float] NULL,
   [Sex] [nvarchar](255) NULL,
   [Life_Stage] [nvarchar](255) NULL,
   [Marking_Method] [nvarchar](255) NULL,
   [Tag_Type] [varchar](30) NULL,
   [Notes] [nvarchar](255) NULL,
CONSTRAINT [PK_tbl_Tagged_Animal] PRIMARY KEY CLUSTERED 
(
   [AnimalID] ASC,
   [TagSN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_TF_TagCode] ON [dbo].[Tagged_Animal] 
(
   [XmitID] ASC,
   [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
Detections_with_Location
CREATE TABLE [dbo].[Detections_with_Location](
   [AnimalID] [nvarchar](50) NOT NULL,
   [XmitID] [int] NOT NULL,
   [Technology] [varchar](25) NOT NULL,
   [DetectDate] [datetime] NOT NULL,
   [ReceiverID] [int] NOT NULL,
   [Data] [float] NULL,
   [Units] [varchar](50) NULL,
   [Location] [nvarchar](255) NULL,
   [StartD] [datetime] NULL,
   [StopD] [datetime] NULL,
   [fname] [nvarchar](255) NULL,
   [notes] [nvarchar](max) NULL,
CONSTRAINT [PK_dlwk] PRIMARY KEY CLUSTERED 
(
   [ReceiverID] ASC,
   [Technology] ASC,
   [XmitID] ASC,
   [DetectDate] ASC,
   [AnimalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_VTC] ON [dbo].[Detections_with_Location] 
(
   [ReceiverID] ASC,
   [AnimalID] ASC,
   [XmitID] ASC,
   [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCpi] ON [dbo].[Detections_with_Location] 
(
   [XmitID] ASC,
   [Technology] ASC
)
INCLUDE ( [DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCD] ON [dbo].[Detections_with_Location] 
(
   [AnimalID] ASC,
   [XmitID] ASC,
   [Technology] ASC,
   [DetectDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_F] ON [dbo].[Detections_with_Location] 
(
   [AnimalID] ASC
)
INCLUDE ( [XmitID],
[Technology],
[DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_DSS] ON [dbo].[Detections_with_Location] 
(
   [ReceiverID] ASC,
   [Location] ASC,
   [StartD] ASC,
   [StopD] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
Receiver_Locations
CREATE TABLE [dbo].[Receiver_Locations](
   [Region] [nvarchar](50) NULL,
   [Location_Long] [nvarchar](50) NULL,
   [Location] [nvarchar](50) NOT NULL,
   [Lat] [float] NULL,
   [Lon] [float] NULL,
   [Altitude] [float] NULL,
   [Elevation] [float] NULL,
   [RiverKm] [float] NULL,
   [LocationType] [nvarchar](50) NULL,
   [General_Location] [nvarchar](50) NULL,
   [Nearest_Access] [nvarchar](50) NULL,
   [Responsible_Agent] [nvarchar](50) NULL,
   [Agent_Phone] [nvarchar](255) NULL,
   [Agent_Email] [nvarchar](255) NULL,
CONSTRAINT [PK_tbl_Receiver_Locations] PRIMARY KEY CLUSTERED 
(
   [Location] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
桌子尺寸
Tagged_Animals:
10,000 檢測_with_Location: 46+ 百萬條目
Receiver_Locations: 800
收到的特定錯誤
無法為數據庫“tempdb”中的對象“dbo.SORT 臨時執行儲存：140737631617024”分配空間，因為“PRIMARY”文件組已滿。通過刪除不需要的文件、刪除文件組中的對象、向文件組添加其他文件或為文件組中的現有文件設置自動增長來創建磁碟空間。
數據庫“tempdb”的事務日誌已滿。要找出日誌中的空間不能被重用的原因，請參閱 sys.databases (tempdb ACTIVE_TRANSACTION) 中的 log_reuse_wait_desc 列
執行批處理時出錯。錯誤消息是：拋出了“System.OutOfMemoryException”類型的異常——（如果s在大約 3300 萬條記錄後直接選擇）。
（估計）初始程式碼的執行計劃摘要
INSERT成本 0%
SEQUENCE成本 0% 但從 9 個不同的子步驟中提取。這些子步驟（具有典型成本）是Index Insert（2%），其次是Sort（8%），然後是Table Spool（2%）。NIX_A有 9% 的成本，Index Insert既沒有NIX_TCD也NIX_F沒有Sort步驟；Table SpoolforNIX_F是免費的。費用為 10%
。分發流還有2% 的成本和 1% 的成本。對於成本，它似乎增加了 95%，其他步驟花費了 13%，所以很明顯某處存在一些舍入“錯誤”，可能主要在 14% 的序列中。Clustered Index Insert
Sort``Parallelism
SEQUENCE``Index Insert``Sort``Table Spool
註釋/參考
基於SQL 權威部落格條目
的 LAG/LEAD 實現另見此 Stackexchange 執行緒
我的問題
有什麼改進的建議嗎？
當我加入副本時，我也可以分區s嗎？
如果我製作s一個謹慎的臨時表並適當地索引它，情況會有所改善嗎？
在執行所有插入之後UNIQUE在臨時表中創建非索引會更有效嗎？我假設必須預先創建（因此）索引以防止違反鍵約束。UNIQUE``PRIMARY KEY
回答我自己的問題之一
是的，是的。進一步優化後
21 分鐘用數據填充臨時表
1分鐘索引
這個過程之前至少進行了 1.5 小時，出錯並且沒有生成結果表。在我開始擺弄查詢邏輯之前，實際上需要 4 個多小時才能出錯。
伺服器規格：
處理器：Xeon E3-1240 V2 @ 3.4 GHz（4 核/8 執行緒）
記憶體：16 GB
分頁文件：16 GB on 111 GB SSD（52 GB free）
tempdb + my database on 223 GB SSD（119 GB free）
目前狀態
請參閱我發布的解決方案/答案。

我將此作為答案送出，因為我目前正在避免記憶體不足類型錯誤以及顯著減少執行時間（4 小時以上，以失敗告終；現在是 1.25 小時，以成功告終）。但是，我確信在大約 1000 萬條記錄之後，這個問題可能會再次發生，所以我將不勝感激任何其他旨在提高未來記憶體效率的評論或答案。

對此的“解決方案”是從臨時表的設計中刪除不需要的欄位，尤其是索引。此外，非約束鍵的索引創建被推遲到表被填滿之後。

為了解決分區不匹配的問題（@MartinSmith 首先指出）JOIN，我在子查詢中創建了一個欄位，然後我將其用於PARTITIONing 和 for JOINing。

Q的程式碼

set nocount on;
SET XACT_ABORT ON;
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'started' as reason; --last run 2015-12-16 18:22:02
GO

CREATE TABLE ##ttOrder_det (
   AnimalID nvarchar(50) not null,
   DD DateTime not null,
   ReceiverID int NOT NULL,
   Location nvarchar(255) NOT NULL,
   GLocation nvarchar(255) NULL,
   DetID int NOT NULL,
   BinIncORNum int NULL, -- first pass=Inc, second pass=Num
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
   ([AnimalID] ASC, [DD] ASC, ReceiverID ASC)
   WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
  'created first temporary table' as reason;  --last run 2015-12-16 18:22:02
GO
WITH s AS (
   SELECT 
       AnimalID, DetectDate,
       ReceiverID, Location, GLocation,
       ROW_NUMBER() OVER (
           PARTITION BY AnimalID ORDER BY DetectDate ASC, ReceiverID ASC
       ) as DetID
   FROM (
       SELECT 
           COALESCE (
               TF.AnimalID,
               Det.Technology+'-'+cast(Det.XmitID AS nvarchar(10)),
               Det.AnimalID,
               N'BSVALUE999'
           ) as AnimalID,
           DetectDate,
           ReceiverID,
           COALESCE (
               Det.location,
               N'Unknown Location'
           ) as Location,
           COALESCE (
               ML.General_Location,
               N'Invalid General Location - Orphaned Receiver'
           ) as GLocation
       FROM
           Detections_with_Location as Det LEFT OUTER JOIN
           Receiver_Locations as ML ON Det.Location=ML.Location LEFT OUTER JOIN
           Tagged_Animal as TF ON Det.AnimalID=TF.AnimalID
   ) AS T
)
INSERT INTO ##ttOrder_det (AnimalID, DD, ReceiverID, Location, GLocation, DetID, BinIncORNum)
   SELECT 
       s1.AnimalID,
       s1.DetectDate, s1.ReceiverID, s1.Location, s1.GLocation,
       s1.DetID, 
       CASE WHEN sLg.DetectDate IS NULL
           THEN 1 
           ELSE CASE WHEN sLg.GLocation = s1.GLocation
               THEN 0
               ELSE 1
           END
       END AS BinInc
   FROM s as s1
   LEFT OUTER JOIN s AS sLg ON
       s1.AnimalID= sLg.AnimalID AND
       s1.DetID = sLg.DetID + 1
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
  'filled first temp table' as reason,
  COUNT(*) as SizeOfFirstTemp FROM ##ttOrder_det; --2015-12-16 18:43:03, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
--dropped several additional indices: `NIX_`s VTC, TCD, A, DD, Bi
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'indexed first temp table' as reason; --2015-12-16 18:44:12
GO

快速評論

我改用 usingROW_NUMBER而不是RANK. 這是不確定的，但至少會導致沒有“聯繫”，這將導致LAG圍繞該點的實施中斷。聯繫不應該存在，但這只是針對General_Location那些非常接近並共同檢測相同傳輸的 s 的未來證明。

同樣，正如兩位使用者在上面的評論中指出的那樣，我沒有使用Tagged_Animal表的完整 PK，因此有可能存在JOIN帶有模棱兩可的AnimalID. 然而，目前，兩者AnimalID和TagSN都是UNIQUE，儘管不受約束。

我計劃放棄該ReceiverID領域以支持使用Location，但我有一段時間在同一位置部署了兩個接收器（一個接收器被假定失去但後來被發現）確實同時檢測到同一隻動物

完成任務的後續程式碼

CREATE TABLE ##ttOrder_det2 (
   AnimalID nvarchar(50) not null,
   DetID int NOT NULL,
   BinNum int NULL,
CONSTRAINT [PK_ttRDA2] PRIMARY KEY CLUSTERED 
   ([AnimalID] ASC, [DetID] ASC)
   WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'second temp table created' as reason; --2015-12-16 18:44:15
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs. 
BEGIN TRANSACTION
 BEGIN TRY
  DECLARE @AnimalID as nvarchar(50);
   DECLARE @DetID as int;
   DECLARE @BinInc as int;
   DECLARE @BinNum as int;
   DECLARE @AnimalVisit as CURSOR;
   SET @AnimalVisit = CURSOR FOR SELECT AnimalID, DetID, BinIncORNum FROM ##ttOrder_det ORDER BY AnimalID, DetID;
   OPEN @AnimalVisit;
   FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
   WHILE @@FETCH_STATUS = 0
     BEGIN
       IF (@DetID &lt;= 1) SET @BinNum = 0;
       SET @BinNum += @BinInc;
       INSERT INTO ##ttOrder_det2 (AnimalID, DetID, BinNum) VALUES (@AnimalID, @DetID, @BinNum);
       FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
     END
   CLOSE @AnimalVisit;
   DEALLOCATE @AnimalVisit;
 END TRY
 BEGIN CATCH
   exec sp_lock; -- added to display the open locks after the timeout
--    exec sp_who2; -- shows the active processes
   EXECUTE usp_GetErrorInfo;
   --RETURN -- ignoring this error for brevity
       -- Test XACT_STATE:
       -- If 1, the transaction is committable.
       -- If -1, the transaction is uncommittable and should 
       --     be rolled back.
       -- XACT_STATE = 0 means that there is no transaction and
       --     a commit or rollback operation would generate an error.

   -- Test whether the transaction is uncommittable.
   IF (XACT_STATE()) = -1
   BEGIN
       SELECT
           cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
           'The transaction is in an uncommittable state. Rolling back transaction.' as reason
           SET XACT_ABORT off
           RETURN -- 1 --error
       ROLLBACK TRANSACTION;
   END;
   -- Test whether the transaction is committable.
   IF (XACT_STATE()) = 1
   BEGIN
       SELECT
           cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
           'The transaction is committable. Committing transaction.' as reason
       COMMIT TRANSACTION;   
   END;
 END CATCH;
 IF @@TRANCOUNT &gt; 0
   COMMIT TRANSACTION;
GO
SELECT 
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'filled second temp table' as reason,
   COUNT(*) as Table2Size from ##ttOrder_det2; --2015-12-16 19:11:17, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_CT2 ON ##ttOrder_det2 (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R2 ON ##ttOrder_det2 (DetID ASC);
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'indexed second temp table' as reason;
--EXEC tempdb.dbo.usp_PruneTemp;
GO
BEGIN TRANSACTION
   BEGIN TRY
       UPDATE a
           SET a.BinIncORNum=b.BinNum
       FROM ##ttOrder_det AS a
       INNER JOIN ##ttOrder_det2 AS b ON 
           a.AnimalID=b.AnimalID AND a.DetID=b.DetID;
       SELECT
           cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
           'backfilled first temp table with info from second table' as reason,
           @@ROWCOUNT as EntriesAffected;  --2015-12-16 19:19:54, 46627879
       DROP TABLE ##ttOrder_det2;
       COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
       SELECT name, log_reuse_wait_desc FROM sys.databases
       SELECT
         cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
         'ERROR: backfilling first temp table, see sys.databases info' as reason;
       EXECUTE usp_GetErrorInfo;
       IF @@TRANCOUNT &gt; 0 ROLLBACK TRANSACTION;
       SET XACT_ABORT off
       RETURN --1 --error
   END CATCH
--IF @@TRANCOUNT &gt; 0 COMMIT TRANSACTION
GO
CREATE TABLE derived__AnimalVisits (
   AnimalID nvarchar(50) not null,
   DetectDate DateTime not null,
   ReceiverID int NOT NULL,
   Location nvarchar(255) NOT NULL,
   GeneralLocation nvarchar(255) NULL,
   DetOrder int NOT NULL,
   VisitNum int NOT NULL,
CONSTRAINT [PK_dFV] PRIMARY KEY CLUSTERED 
   (AnimalID ASC, DetectDate ASC, ReceiverID ASC)
   WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE UNIQUE INDEX UX_CTR ON derived__AnimalVisits (AnimalID ASC, DetOrder ASC);
SELECT 
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'created persistent derived table' as reason; --2015-12-16 19:20:53
GO

--DECLARE @FVError as int = 0;
BEGIN TRY
   INSERT INTO derived__AnimalVisits (AnimalID, DetectDate, ReceiverID, Location, GeneralLocation, DetOrder, VisitNum)
       SELECT AnimalID, DD, ReceiverID, Location, GLocation, COALESCE(DetID,0), COALESCE(BinIncORNum,0) FROM ##ttOrder_det;
END TRY
BEGIN CATCH
   SELECT
       cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
       'ERROR: filling persistent derived table' as reason;
   EXECUTE usp_GetErrorInfo;
   SET XACT_ABORT off
   RETURN --1 --error
END CATCH
CREATE NONCLUSTERED INDEX NIX_CT ON derived__AnimalVisits (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON derived__AnimalVisits (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON derived__AnimalVisits (AnimalID ASC, DetectDate ASC);
CREATE NONCLUSTERED INDEX NIX_R ON derived__AnimalVisits (DetOrder ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON derived__AnimalVisits (VisitNum ASC);
SELECT
   cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'indexed / now cleaning up' as reason; --2015-12-16 19:31:18
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det2]') AND xtype=N'U')
   DROP TABLE tempdb.[dbo].[##ttOrder_det2];
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det]') AND xtype=N'U')
   DROP TABLE tempdb.[dbo].[##ttOrder_det];
SET XACT_ABORT off
--cleaning up of transaction logs, etc done at 2015-12-16 19:39:07

引用自：https://dba.stackexchange.com/questions/123481

SQL Server 2008 R2 中的領先/滯後實施：超出最大記憶體

背景

表和索引定義

## ttOrder_det（臨時表）

標記_動物

Detections_with_Location

Receiver_Locations

桌子尺寸

收到的特定錯誤

（估計）初始程式碼的執行計劃摘要

註釋/參考

我的問題

伺服器規格：

目前狀態

Q的程式碼

快速評論

完成任務的後續程式碼

相關問答

為什麼添加 TOP 1 會顯著降低性能？

SQL Server 如何知道謂詞是相關的？

為什麼本屆會議特別暫停？請看詳情

更改 SQL Server 2008 R2 數據庫後性能不佳

在不重寫此查詢的情況下，有沒有辦法強制查詢優化器更好地估計所需的資源量，例如使用查詢提示？

防止單個查詢佔用所有資源