如何更新數據庫系統表的統計資訊

我們最近將許多實例升級到 2016。因此，來自sqlpackage.exe的 SELECT 語句在某些實例上超時。

經過一些測試，我發現通過更新執行計劃中顯示的數據庫系統表的統計資訊，SELECT 停止了超時。

update statistics sys.[sysclsobjs] with fullscan
update statistics sys.[syscolpars] with fullscan
update statistics sys.[sysidxstats] with fullscan
update statistics sys.[sysiscols] with fullscan
update statistics sys.[sysobjvalues] with fullscan

無論如何，是否有通過標準維護包、Ola Hallengren 的腳本或其他一些過程來僅更新系統表統計資訊？

更新 08/01

這是我升級後採取的步驟

關於 4199 跟踪標誌KB974006

-- for the instance
/* 
Turn on traceflag 4199 (my understanding of this traceflag is that it disables
optimizer hotfixes in 2016
*/

-- disable automatic numa
sp_configure 'automatic soft-NUMA disabled', 1
GO

-- For each database
-- Turn on Query optimizer hotfixes
-- Turn off Legacy cardinality estimation

exec sp_MSforeachDB 'ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 130;
USE [?]; 
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

-- update statistics for all tables, system tables are ignored
EXEC sp_MSforeachtable ''UPDATE STATISTICS [?] WITH FULLSCAN''
'

更新 08/02

這是 sqlpackage.exe 中的 SELECT 導致 TFS 超時

SELECT * FROM (
SELECT 
SCHEMA_NAME([o].[schema_id]) AS [SchemaName],
[si].[object_id] AS [ColumnSourceId],
[o].[name] AS [ColumnSourceName],
[o].[type] AS [ColumnSourceType],
[ic].[column_id] AS [ColumnId],
[c].[name] AS [ColumnName],
[si].[index_id] AS [IndexId],
[si].[name] AS [IndexName],
[ds].[type] AS [DataspaceType],
[ds].[data_space_id] AS [DataspaceId],
[ds].[name] AS [DataspaceName],
[si].[fill_factor] AS [FillFactor],
[si].[is_padded] AS [IsPadded],
[si].[is_disabled] AS [IsDisabled],
[si].[allow_page_locks] AS [DoAllowPageLocks],
[si].[allow_row_locks] AS [DoAllowRowLocks],
[sit].[cells_per_object] AS [CellsPerObject],
[sit].[bounding_box_xmin] AS [XMin],
[sit].[bounding_box_xmax] AS [XMax],
[sit].[bounding_box_ymin] AS [YMin],
[sit].[bounding_box_ymax] AS [YMax],
[sit].[level_1_grid] AS [Level1Grid],
[sit].[level_2_grid] AS [Level2Grid],
[sit].[level_3_grid] AS [Level3Grid],
[sit].[level_4_grid] AS [Level4Grid],
[sit].[tessellation_scheme] AS [TessellationScheme],
[s].[no_recompute] AS [NoRecomputeStatistics],
[p].[data_compression] AS [DataCompressionId],
CONVERT(bit, CASE WHEN [ti].[data_space_id] = [ds].[data_space_id] THEN 1 ELSE 0 END)
AS [EqualsParentDataSpace]
FROM
[sys].[spatial_indexes] AS [si] WITH (NOLOCK)
INNER JOIN [sys].[objects] AS [o] WITH (NOLOCK) ON [si].[object_id] = [o].[object_id]
INNER JOIN [sys].[spatial_index_tessellations] [sit] WITH (NOLOCK) ON [si].[object_id] = [sit].[object_id] AND [si].[index_id] = [sit].[index_id]
INNER JOIN [sys].[data_spaces] AS [ds] WITH (NOLOCK) ON [ds].[data_space_id] = [si].[data_space_id] 
INNER JOIN [sys].[index_columns] AS [ic] WITH (NOLOCK) ON [si].[object_id] = [ic].[object_id] AND [si].[index_id] = [ic].[index_id]
INNER JOIN [sys].[columns] AS [c] WITH (NOLOCK) ON [si].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id]
INNER JOIN [sys].[objects] AS [o2] WITH (NOLOCK) ON [o2].[parent_object_id] = [si].[object_id]
INNER JOIN [sys].[stats] AS [s] WITH (NOLOCK) ON [o2].[object_id] = [s].[object_id] AND [s].[name] = [si].[name]
INNER JOIN [sys].[partitions] AS [p] WITH (NOLOCK) ON [p].[object_id] = [o2].[object_id] AND [p].[partition_number] = 1
LEFT JOIN [sys].[indexes] AS [ti] WITH (NOLOCK) ON [o].[object_id] = [ti].[object_id]
LEFT JOIN [sys].[tables] AS [t] WITH (NOLOCK) ON [t].[object_id] = [si].[object_id]
WHERE [si].[is_hypothetical] = 0
AND [ti].[index_id] < 2
AND OBJECTPROPERTY([o].[object_id], N'IsSystemTable') = 0
AND ([t].[is_filetable] = 0 OR [t].[is_filetable] IS NULL)
AND ([o].[is_ms_shipped] = 0 AND NOT EXISTS (SELECT *
FROM [sys].[extended_properties]
WHERE [major_id] = [o].[object_id]
AND [minor_id] = 0
AND [class] = 1
AND [name] = N'microsoft_database_tools_support'
))
) AS [_results] 

如果我不更新數據庫的系統表統計資訊，那麼在通過 sqlpackage.exe 部署時，此 SELECT 可以並且將會超時

您可以在不使用 DAC 連接的情況下“查看”​​系統表：

SELECT *
FROM sys.objects o
WHERE o.type = 'S'
ORDER BY o.name

從那裡，您可以建構一個 TSQL 命令字元串並執行它。（我sys為模式硬編碼。大概這永遠不會改變。）：

DECLARE @TSql NVARCHAR(MAX) = ''
SELECT @TSql = @TSql +  'UPDATE STATISTICS sys.' + o.name + ' WITH FULLSCAN;' + CHAR(13) + CHAR(10)
FROM sys.objects o
WHERE o.type = 'S'
ORDER BY o.name

--Verify/test commands.
PRINT @TSql

--Uncomment and re-run when ready to proceed.
--EXEC sp_executesql @TSql

這可以很容易地放入 SQL 代理作業的作業步驟中。我在 SQL Server 2012 w/SP3 上對此進行了測試。我還在 SQL 2008 R2 和 SQL 2014 中執行了類似的程式碼。我不確定它是否在 SQL 2016 中有效——如果無效，請告訴我們。

其他注意事項當我以使用者身份登錄時，

這些命令有效UPDATE STATISTICS

$$ sysadmin $$. 如果登錄名不是$$ sysadmin $$，但是是數據庫所有者。會員資格$$ db_owner $$單獨是不夠的。（同樣，這是在 SQL 2012 w/SP3 上。YMMV。）

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