Sql-Server

日誌傳送備用伺服器上的慢速選擇查詢

  • October 21, 2019

我們在主 DC 中有一個大約 15 TB 的數據庫，其中最大的一個分區表大約 7 TB。

為了避免使用者報告 LS 設置已在不同的 DC 中完成，其中 LS 還原作業每晚執行一次。

在 Primay-update stats 和 update stats AYNC 都設置為 True ，因此在次要上反映相同。我不確定為什麼有人同時啟用了這兩個功能，應該是這樣嗎？

現在在輔助上，我看到當從使用者執行 SELECT 查詢時會發生很多阻塞，導致 select statman 語句用於該只讀數據庫的自動更新統計資訊。

我不明白為什麼啟用自動更新非同步時會有一個？

此外，在 LS 恢復期間是否會恢復在主伺服器上完成的更新統計資訊（每週一次）？如果不是，那麼在 LS 待機模式中涉及的輔助數據庫上執行更新統計資訊的更好方法是每天執行以獲得更好的選擇性能嗎？

請指教

只讀數據庫中的統計資訊。

TL; 博士

在只讀數據庫中，可以利用正常的永久統計資訊來滿足查詢計劃。如果 sql server 需要只讀數據庫的新統計資訊，或者數據庫中的這些永久統計資訊已經過時，則可以創建/更新**臨時統計資訊。*這些統計資訊駐留在TempDBSQL Server 中並由其管理（您只能刪除它們）*。

有兩種臨時統計資訊，一種是由於缺少統計資訊而創建的，另一種是“更新”的。

可以通過在主數據庫上手動添加統計資訊或生成報告查詢的估計執行計劃*（也在主實例/數據庫上）*來刪除臨時統計資訊的創建。在這個答案中進一步了解這一點。

永久統計資訊可以更新並“轉換”為只讀數據庫上的臨時統計資訊。

臨時統計更新可以通過更頻繁地更新主數據庫上的統計資訊或在日誌恢復完成後禁用只讀數據庫上的自動統計更新來解決。

要禁用自動更新統計資訊，您可以在只讀數據庫上執行此操作：ALTER DATABASE [Database] SET AUTO_UPDATE_STATISTICS OFF;這將停止此只讀數據庫上臨時統計資訊的更新。

關於恢復到備用數據庫和臨時統計資訊的另一個重要部分是，當應用日誌備份時，臨時統計資訊會再次更新，即使它們仍然存在於 sys.stats 中。

Statman在您的情況下，解釋查詢的日常問題*（如果需要創建/更新，則每天重新計算臨時統計資訊）。*

Norecovery –> Standby –> Norecovery … 刪除臨時統計資訊

關於臨時統計的另一個有趣的部分是，當數據庫狀態更改為正在恢復時，它們將消失RESTORE DATABASE ... WITH NORECOVERY。

use MASTER
GO
RESTORE DATABASE [ReadOnly2] with NORECOVERY
RESTORE DATABASE [ReadOnly2] WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

有效刷新所有對象的臨時統計資訊

SELECT * From sys.stats where is_temporary = 1;

並在每個狀態更改 + 測試查詢執行之間重新計算相同的 2 個統計資訊。

在此處輸入圖像描述

這兩個永久統計資訊都顯示在我們執行計劃的 xml 中

  <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[IX_Bla_indexedval]" ModificationCount="12000000" SamplingPercent="15.8812" LastUpdate="2019-06-12T10:52:32.25" />
     <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[PK__Bla__3214EC075017BD54]" ModificationCount="12000000" SamplingPercent="15.2345" LastUpdate="2019-06-12T10:52:35.34" />

在使用臨時統計資訊“更新”再次執行查詢後modificationcount，samplingpercent&發生了更改。lastupdate

ModificationCount="0" SamplingPercent="5.71018" LastUpdate="2019-06-13T11:32:36.5"

創建臨時統計

正常的、非臨時的統計資訊不會更新/您不能更新（甚至是臨時的）只讀數據庫的統計資訊。

您在報告實例上看到的是臨時統計數據的創建/“更新”。

這些統計資訊駐留在 TempDB & SQL Server 創建和更新它們。

複製行為

我能夠在只讀數據庫中複製具有 100M 行的表上創建臨時統計資訊的行為*（附錄 #1）* 在此處輸入圖像描述

使用您提到的麻煩的 StatMan 查詢。

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [NonIndexedVal] AS [SC0] FROM [dbo].[Bla] TABLESAMPLE SYSTEM (7.707678e-001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Maxdop 16（最大核心數），因為我在我的測試機器 YMMV 上將 MAXDOP 設置為 0

重啟實例

當我重新啟動實例時，我看到了相同的行為，表明這些實際上是臨時統計資訊。一些關於臨時統計的問答可以在這裡找到。

現在我們看到的是臨時統計資訊的創建，它們是在查詢執行之前創建的。

對於大表，他們確實注意到採樣率仍適用於臨時統計資訊。

需要注意的另一點是，作為自動統計的一部分創建的統計資訊使用數據採樣，因此這些統計資訊的創建速度很快，並且不依賴於表的大小

來源

當 sql server 重新啟動/數據庫恢復時，您如何解決統計資訊的創建？

如果可以這樣做，您可以編寫腳本並在主“主”數據庫上創建它們。

查找臨時統計資訊

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

編寫統計腳本

沒有 T-SQL 或使用關於如何在 Sql Server 中編寫統計資訊的問題的 T-SQL 答案？（使用 T-SQL）作者：Martin Smith

這可以解決您一次又一次創建統計數據的主要問題。

如果問題是由於創建了臨時統計資訊而您無法正確編寫腳本，則另一個想法可能是為主數據庫上的報告查詢創建估計的執行計劃。這應該創建自動創建統計資訊 = on 時所需的統計資訊。

臨時統計更新

可能出現的另一個問題是陳舊的永久統計數據。正如前面提到的部落格中所指出的，陳舊的永久統計數據可以更新並設置為is_temporary=1.

這意味著只讀數據庫上的永久統計資訊可以成為臨時統計資訊，直到實例重新啟動。當您更新主節點上的統計資訊時，它應該在應用日誌時轉移到輔助節點。

非同步臨時統計更新

我們看到非同步統計更新也適用於這些臨時統計！

執行addendum #1後，我們執行下一個片段：

  USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF 

USE [ReadOnly]
GO
INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal) 
SELECT TOP(10000000) --10M
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON 

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;
SELECT Indexedval 
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

以上SELECT立即執行。

執行查詢後，將顯示後果。

在此處輸入圖像描述

臨時統計資訊更新在查詢執行後執行。

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [Indexedval] AS [SC0] FROM [dbo].[Bla] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

禁用只讀數據庫的自動更新統計資訊

您可以通過執行下一條語句來禁用將永久統計資訊更新為臨時統計資訊，您可以在只讀數據庫上更改此設置，它仍然可以工作。

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF;

臨時統計升級的解決方案。

此外，在 LS 恢復期間是否會恢復在主伺服器上完成的更新統計資訊（每週一次）？

更新您的統計數據將被帶到輔助數據，盡可能多地更新它們將導致更少的陳舊統計數據和更少陳舊的永久統計數據被轉換為臨時統計數據。

研究這兩個將是我解決這個問題的方法。

使用跟踪標誌禁用臨時統計資訊

當大規模啟用 traceflags 以幫助同事並深入探勘 2 到 3000 之間時，似乎可以使用traceflag 2362禁用臨時統計資訊。

您可以像這樣啟用它們：

DBCC TRACEON(2362,-1);

並且不會創建所有新的臨時統計資訊。現有的臨時統計資訊將一直保留，直到它們被刪除。例如，通過將數據庫設置為離線並再次聯機。

將日誌備份應用到備用數據庫和臨時統計資訊

附錄 2

當執行附錄 #2中的查詢並將日誌備份應用到備用數據庫時，每次還原後都會更新統計資訊。

即使在應用“空”日誌備份之後。

在附錄 2 中，在每個日誌備份還原之間執行以下查詢：

SELECT Indexedval 
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

這些每次都會觸發臨時統計更新。

證明

在此處輸入圖像描述 在此處輸入圖像描述

所有這一切意味著在晚上應用日誌將使臨時統計更新每天執行，而無需重新啟動實例。

解決這個問題

  • 應用日誌後在備用數據庫上設置自動更新統計資訊。更改數據庫$$ ReadOnly2 $$設置 AUTO_UPDATE_STATISTICS 關閉；
  • 恢復完成後執行您的報告查詢以創建臨時統計資訊
  • 在應用日誌之前更頻繁地更新主數據庫的統計資訊。
  • 啟用跟踪標誌 2362

恢復日誌備份時，它們仍然存在：

SELECT name, is_temporary From sys.stats where is_temporary = 1;

name    is_temporary
PK__Bla__3214EC075017BD54   1
IX_Bla_indexedval   1

但是在重新執行查詢時，它們確實會重新計算。 在此處輸入圖像描述

附錄 #1（只讀數據庫中包含 100M 記錄的表。）

CREATE DATABASE [ReadOnly]
CONTAINMENT = NONE
ON  PRIMARY 
( NAME = N'ReadOnly', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON 
( NAME = N'ReadOnly_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [ReadOnly] SET COMPATIBILITY_LEVEL = 140
ALTER DATABASE [ReadOnly] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS_ASYNC ON 
ALTER DATABASE [ReadOnly] SET  READ_WRITE 
ALTER DATABASE [ReadOnly] SET RECOVERY SIMPLE 
ALTER DATABASE [ReadOnly] SET  MULTI_USER 
ALTER DATABASE [ReadOnly] SET PAGE_VERIFY CHECKSUM  

USE [ReadOnly]
GO
CREATE TABLE dbo.Bla(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Indexedval INT,NonIndexedVal INT);
CREATE INDEX IX_Bla_indexedval on dbo.Bla(Indexedval);

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal) 
SELECT TOP(10000000) --10M
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;
GO 10

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;

USE [ReadOnly]
GO
SELECT NonIndexedVal 
FROM dbo.Bla
WHERE  NonIndexedVal = 999999;

#附錄2

ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET RECOVERY FULL

BACKUP DATABASE [ReadOnly] to disk = 'D:\temp\ReadOnly.bak'
WITH COMPRESSION, STATS=5


RESTORE FILELISTONLY FROM DISK = 'D:\temp\ReadOnly.bak'

RESTORE DATABASE [ReadOnly2] FROM disk = 'D:\temp\ReadOnly.bak'
WITH MOVE  'ReadOnly' to 'D:\temp\ReadOnly2.mdf'
,MOVE 'ReadOnly_log' to 'F:\temp\ReadOnly_log2.ldf'
, STANDBY = 'D:\temp\ReadOnly_Standby.bak'





USE [ReadOnly2]
GO
ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS ON

SELECT Indexedval 
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1); 


USE [ReadOnly]

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal) 
SELECT TOP(2000000) --2M
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;


USE MASTER
GO
BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog.trn'
WITH COMPRESSION, STATS=5


RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval 
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1); 

BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog2.trn'
WITH COMPRESSION, STATS=5


RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog2.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval 
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1); 
SELECT * From sys.stats where is_temporary = 1

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

相關問答

Sql-Server

重新索引是否更新統計資訊？

  • March 17, 2021
檢視問答
Sql-Server

查找所有採樣的統計資訊

  • May 13, 2020
檢視問答
Sql-Server

日誌傳送和每日備份

  • August 23, 2019
檢視問答
Sql-Server

恢復日誌投遞到備伺服器後，第一個儲存過程執行慢

  • May 14, 2019
檢視問答
Sql-Server

SQL Server LSCopy 作業

  • October 31, 2018
檢視問答
Sql-Server

更正已創建索引和統計資訊的數據庫

  • September 6, 2018
檢視問答