SQL Server 查找統(tǒng)計信息的采樣時間與采樣比例 原文:SQL Server 查找統(tǒng)計信息的采樣時間與采樣比例

有時候我們會遇到，由于統(tǒng)計信息不準(zhǔn)確導(dǎo)致優(yōu)化器生成了一個錯誤的執(zhí)行計劃（或者這樣表達(dá)：一個較差的執(zhí)行計劃），從而引起了系統(tǒng)性能問題。那么如果我們懷疑這個錯誤的執(zhí)行計劃是由于統(tǒng)計信息不準(zhǔn)確引起的。那么我們?nèi)绾闻袛嘟y(tǒng)計信息不準(zhǔn)確呢？當(dāng)然首先得去查看實際執(zhí)行計劃中，統(tǒng)計信息的相關(guān)數(shù)據(jù)是否與實際情況有較大的出入，下面我們拋開這個大命題，僅僅從統(tǒng)計信息層面去查看統(tǒng)計信息的更新時間，統(tǒng)計信息的采樣行數(shù)、采樣比例等情況。

?

1：首先，我們要查查統(tǒng)計信息是什么時候更新的。

?

2：其次，我們查看統(tǒng)計信息的采樣的百分比以及采樣信息：采樣選取的行數(shù)、自上次更新統(tǒng)計信息以來前導(dǎo)統(tǒng)計信息列（構(gòu)建直方圖的列）的總修改次數(shù)。。。

?

?

查看統(tǒng)計信息的最后更新時間。

?

?

方法1：

?

--查看統(tǒng)計信息的更新時間DECLARE @TableName NVARCHAR(128);SET @TableName = '[Maint].[JobHistoryDetails]';SELECT? @TableName? AS Table_Name,??????? name AS Stats_Name ,??????? STATS_DATE(object_id, stats_id) AS Last_Stats_UpdateFROM??? sys.statsWHERE?? object_id = OBJECT_ID(@TableName)ORDER?BY 2 DESC;

?

?

?

?

如上所示，我們通過這個腳本查看某個表所有的統(tǒng)計信息的最后一次更新時間。如果你需要查看某個具體的統(tǒng)計信息的最后更新時間，那么在這個SQL的基礎(chǔ)上修改相關(guān)查詢條件即可。

?

?

方法2：

?

?

?

--查看統(tǒng)計信息的更新時間

EXEC sp_autostats '[Maint].[JobHistoryDetails]';

?

?

方法3：

?

還有一種方法可以通過 sys.dm_db_stats_properties 返回統(tǒng)計信息的更新時間，不過這個DMF只有SQL Server 2008 R2 SP2這個版本之后的才有。

?

列名	數(shù)據(jù)類型	Description
object_id	int	要返回統(tǒng)計信息對象屬性的對象（表或索引視圖）的 ID。
stats_id	int	統(tǒng)計信息對象的 ID。?在表或索引視圖中是唯一的。?有關(guān)詳細(xì)信息，請參閱?sys.stats (Transact-SQL)。
last_updated	datetime2	上次更新統(tǒng)計信息對象的日期和時間。?有關(guān)詳細(xì)信息，請參閱此頁中的備注部分。
rows	bigint	上次更新統(tǒng)計信息時表或索引視圖中的總行數(shù)。?如果篩選統(tǒng)計信息或者統(tǒng)計信息與篩選索引對應(yīng)，該行數(shù)可能小于表中的行數(shù)。
rows_sampled	bigint	用于統(tǒng)計信息計算的抽樣總行數(shù)。
Step	int	直方圖中的值范圍數(shù)（步長）（Number of steps in the histogram）。?有關(guān)詳細(xì)信息，請參閱?DBCC SHOW_STATISTICS (Transact-SQL)。
unfiltered_rows	bigint	應(yīng)用篩選表達(dá)式（用于篩選的統(tǒng)計信息）之前表中的總行數(shù)。?如果未篩選統(tǒng)計信息，則 unfiltered_rows 等于行列中返回的值。
modification_counter	bigint	自上次更新統(tǒng)計信息以來前導(dǎo)統(tǒng)計信息列（構(gòu)建直方圖的列）的總修改次數(shù)。 內(nèi)存優(yōu)化表： 正在啟動SQL Server 2016 (13.x)并在Azure SQL Database此列包含： 修改因為最后一個時間統(tǒng)計信息已更新或重新啟動數(shù)據(jù)庫的表的總次數(shù)。
persisted_sample_percent	float	持久樣本百分比用于未顯式指定采樣百分比的統(tǒng)計信息更新。?如果值為零，則不為此統(tǒng)計信息設(shè)置持久樣本百分比。 適用范圍：SQL Server 2016 (13.x)?SP1 CU4

?

?

SELECT sch.name + '.' + so.name AS table_name????? , so.object_id????? , ss.name? AS stat_name????? , ds.stats_id????? , ds.last_updated????? , ds.rows????? , ds.rows_sampled????? , ds.rows_sampled*1.0/ds.rows *100 AS sample_rate????? , ds.steps????? , ds.unfiltered_rows????? --, ds.persisted_sample_percent????? , ds.modification_counter FROM sys.stats ssJOIN sys.objects so ON ss.object_id = so.object_idJOIN sys.schemas sch ON so.schema_id = sch.schema_idCROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) dsWHERE? so.is_ms_shipped = 0 ??????? AND so.object_id NOT?IN (??????? SELECT? major_id??????? FROM??? sys.extended_properties (NOLOCK)??????? WHERE?? name = N'microsoft_database_tools_support' );

?

?

?

查看統(tǒng)計信息采樣的百分比

?

?

SELECT sch.name + '.' + so.name AS table_name????? , so.object_id????? , ss.name? AS stat_name????? , ds.stats_id????? , ds.last_updated????? , ds.rows????? , ds.rows_sampled????? , ds.steps????? , ds.unfiltered_rows????? , ds.modification_counter FROM sys.stats ssJOIN sys.objects so ON ss.object_id = so.object_idJOIN sys.schemas sch ON so.schema_id = sch.schema_idCROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) dsWHERE so.name =? N'pbCutClothCost'??? AND?? LEFT(ss.name, 4) != '_WA_';

?

?

?

?

如上截圖，索引IX_CutClothCost的統(tǒng)計信息有更新，是因為在執(zhí)行上面腳本前，我更新了這個統(tǒng)計信息。通過rows與實際記錄數(shù)對比、 modification_counter信息，我們從而有個大概的判斷，這些統(tǒng)計信息是否過時。是否采樣的比例太小。如果查看統(tǒng)計信息的采樣百分比，那么可以使用下面腳本。

?

SELECT sch.name + '.' + so.name AS table_name????? , so.object_id????? , ss.name? AS stat_name????? , ds.stats_id????? , ds.last_updated????? , ds.rows????? , ds.rows_sampled????? , ds.rows_sampled*1.0/ds.rows *100 AS sample_rate????? , ds.steps????? , ds.unfiltered_rows????? , ds.modification_counter FROM sys.stats ssJOIN sys.objects so ON ss.object_id = so.object_idJOIN sys.schemas sch ON so.schema_id = sch.schema_idCROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) dsWHERE so.name =? N'pbCutClothCost'??? AND?? LEFT(ss.name, 4) != '_WA_';

?

?

查看整個數(shù)據(jù)庫的所有用戶表的采樣比例，可以使用下面腳本

?--適應(yīng)于SQL Server 2016 (13.x) SP1 CU4之前的版本SELECT sch.name + '.' + so.name AS table_name????? , so.object_id????? , ss.name? AS stat_name????? , ds.stats_id????? , ds.last_updated????? , ds.rows????? , ds.rows_sampled????? , ds.rows_sampled*1.0/ds.rows *100 AS sample_rate????? , ds.steps????? , ds.unfiltered_rows??? --, ds.persisted_sample_percent????? , ds.modification_counter FROM sys.stats ssJOIN sys.objects so ON ss.object_id = so.object_idJOIN sys.schemas sch ON so.schema_id = sch.schema_idCROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) dsWHERE? so.is_ms_shipped = 0 ??????? AND so.object_id NOT?IN (??????? SELECT? major_id??????? FROM??? sys.extended_properties (NOLOCK)??????? WHERE?? name = N'microsoft_database_tools_support' );

?

當(dāng)然也可以使用DBCC SHOW_STATISTICS來查看統(tǒng)計信息的詳細(xì)信息。

?

DBCC SHOW_STATISTICS ('dbo.pbCutClothCost', IX_pbCutClothCost_N1)

?

?

?

查看統(tǒng)計信息是否需要更新

?

判斷統(tǒng)計信息是否過時的一個維度：統(tǒng)計信息最后更新的時間，通過時間維度（最后一次統(tǒng)計信息更新距今的時間）。這個對于下面的維度（修改的數(shù)據(jù)數(shù)量）而言，往往不是特別準(zhǔn)確，但是也有參考意義。

?

SELECT??? sch.name + '.' + so.name AS "Table",??? ss.name???????????????????? AS"Statistic",??? CASE??????? WHEN ss.auto_Created = 0 AND ss.user_created = 0 THEN? 'Index Statistic'??????? WHEN ss.auto_created = 0 AND ss.user_created = 1 THEN? 'USER Created'??????? WHEN ss.auto_created = 1 AND ss.user_created = 0 THEN? 'Auto Created'??????? WHEN ss.AUTO_created = 1 AND ss.user_created = 1 THEN? 'Not Possible'??? END?AS??? "Statistic Type",??? CASE??????????? WHEN ss.has_filter = 1 THEN? 'Filtered INDEX'??????????? WHEN ss.has_filter = 0 THEN? 'No Filter'????? END?AS "Filtered",??? CASE??????????? WHEN ss.filter_definition IS?NULL?THEN?''??????????? WHEN ss.filter_definition IS?NOT?NULL?THEN ss.filter_definition???? END?AS "Filter Definition",??? sp.last_updated AS "Stats Last Updated",??? sp.rows?AS "Rows",??? sp.rows_sampled AS "Rows Sampled",??? sp.unfiltered_rows AS "Unfiltered Rows",??? sp.modification_counter AS "Row Modifications",??? sp.steps AS "Histogram Steps"FROM sys.stats ssJOIN sys.objects so ON ss.object_id = so.object_idJOIN sys.schemas sch ON so.schema_id = sch.schema_idOUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS spWHERE so.TYPE = 'U'??? AND sp.last_updated < GETDATE() - 7ORDER?BY sp.last_updated DESC;

?

以前收集過一個查詢過時的統(tǒng)計信息（忘記出自哪里了），自己對腳本做過調(diào)整、修改，這個是通過自上次統(tǒng)計信息更新以來，變化的行數(shù)超過某個閥值來判斷統(tǒng)計信息是否過時。如下所示

?

Max(ApproximateRows) > 500 AND Max(RowModCtr) > (Max(ApproximateRows)*0.2 + 500 )

?

1：如果是SQL Server 2008 R2 SP2以上的版本，使用sys.dm_db_stats_properties的modification_counter字段值：自上次更新統(tǒng)計信息以來前導(dǎo)統(tǒng)計信息列（構(gòu)建直方圖的列）的總修改次數(shù)

?

2：如果是SQL Server 2008 R2 SP2之前的版本，使用sysindexes的rowmodctr字段值：對自上次更新表的統(tǒng)計信息后插入、刪除或更新行的總數(shù)進(jìn)行計數(shù)。

?

?

SET?TRAN?ISOLATION?LEVEL?READ UNCOMMITTED;?DECLARE @product_version NVARCHAR(128),??????? @db_version NVARCHAR(32) ,??????? @edition INT,??????? @small_edition INT,??????? @sql_script_index INT;???SET @product_version = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));???--版本為10.50.4000或高于這個版本使用sys.dm_db_stats_properties這個DMV，否則使用sysindexes中的rowmodctr字段?SELECT @db_version=? CASE????? WHEN @product_version like?'8%'??? THEN?'SQL2000'???? WHEN @product_version like?'9%'??? THEN?'SQL2005'???? WHEN @product_version like?'10.0%'?THEN?'SQL2008'???? WHEN @product_version like?'10.5%'?THEN?'SQL2008 R2'???? WHEN @product_version like?'11%'??? THEN?'SQL2012'???? WHEN @product_version like?'12%'?? THEN?'SQL2014'???? WHEN @product_version like?'13%'??? THEN?'SQL2016'???? ???? WHEN @product_version like?'14%'?? THEN?'SQL2017'????? ELSE?'unknown'? END?SET @edition= SUBSTRING(@db_version, 4, 4)?????IF @edition <=2005??? SET @sql_script_index=0;ELSE?IF @edition = 2008??? ??? IF @db_version ='SQL2008 R2'AND?CAST(SUBSTRING(@product_version,7, 4) AS?INT) >= 4000??????? SET @sql_script_index =1;??? ELSE???????? SET @sql_script_index =0;ELSE???? SET @sql_script_index=1;??IF @sql_script_index = 0??? BEGIN??????? PRINT?'0'??????? EXEC sp_executesql N';WITH StatTables AS(???????????? SELECT???? obj.schema_id??????? AS ''schema_id''??? ????????????????????? ,obj.name??????????????? AS ''table_name''????????????????????? ,obj.object_id??????? AS ''object_id''????????????????????? ,CASE INDEXPROPERTY(obj.object_id, dmv.name, ''IsStatistics'')??????????????????????????????? WHEN 0 THEN dmv.rows??????????????????????????????? ELSE (SELECT TOP 1 row_count FROM sys.dm_db_partition_stats ps (NOLOCK) WHERE ps.object_id=obj.object_id AND ps.index_id in (1,0))?????????????????????? END AS ''approximate_rows''????????????????????? ,dmv.rowmodctr AS ''row_mod_ctr''??????????? FROM sys.objects obj (NOLOCK)??????????????? INNER JOIN sysindexes dmv (NOLOCK) ON obj.object_id = dmv.id??????????????? LEFT JOIN sys.indexes ind (NOLOCK) ON obj.object_id = ind.object_id AND obj.type in (''U'',''V'') AND ind.index_id? = dmv.indid??????????? WHERE obj.is_ms_shipped = 0? --object is not created by an internal sql server component??????????????? AND dmv.indid<>0??????????????? AND obj.object_id NOT IN (SELECT major_id FROM sys.extended_properties (NOLOCK) WHERE name = N''microsoft_database_tools_support'')??????? ),???????? StatTableGrouped AS??????? (??????? SELECT ????????????? ROW_NUMBER() OVER(ORDER BY table_name)??????? AS seq1???????????? ,ROW_NUMBER() OVER(ORDER BY table_name DESC)??? AS seq2???????????? ,table_name??????????????????????????????????? AS table_name???????????? ,CAST(MAX(approximate_rows) AS BIGINT)??????????? AS approximate_rows???????????? ,CAST(MAX(row_mod_ctr) AS BIGINT)??????????????? AS row_mod_ctr???????????? ,schema_id???????????? ,object_id??????? FROM StatTables st??????????? GROUP BY schema_id,object_id,table_name??????????? HAVING (MAX(approximate_rows) > 500 ??????????????? AND MAX(row_mod_ctr) > (MAX(approximate_rows)*0.2 + 500 ))???????? )???????? SELECT??????????? @@SERVERNAME??????????????? AS instance_name?????????? ,seq1 + seq2 - 1??????????????? AS occurences_num?????????? ,SCHEMA_NAME(stg.schema_id)??? AS ''schema_name''?????????? ,stg.table_name?????????? ,CASE OBJECTPROPERTY(stg.object_id, ''TableHasClustIndex'')?????????????????????????? WHEN 1 THEN ''Clustered''?????????????????????????? WHEN 0 THEN ''Heap''?????????????????????????? ELSE ''Indexed View''???????????? END AS clustered_or_heap?????????? ,CASE OBJECTPROPERTY(stg.object_id, ''TableHasClustIndex'')???????????????? WHEN 0 THEN (SELECT COUNT(*) FROM sys.indexes i (NOLOCK) WHERE i.object_id= stg.object_id) - 1??????????????????????? ELSE (SELECT COUNT(*) FROM sys.indexes i (NOLOCK) WHERE i.object_id= stg.object_id)??????????? END AS IndexCount?????????? ,(SELECT COUNT(*) FROM sys.columns c (NOLOCK) WHERE c.object_id = stg.object_id ) AS columns_count ?????????? ,(SELECT COUNT(*) FROM sys.stats s (NOLOCK) WHERE s.object_id = stg.object_id)???? AS stats_count ?????????? ,stg.approximate_rows?????????? ,stg.row_mod_ctr?????????? ,stg.schema_id?????????? ,stg.object_id??????? FROM StatTableGrouped stg';???? END;?ELSE??? BEGIN??????? ??????? EXEC sp_executesql? N'??????????? ;WITH StatTables AS(??????????? SELECT??? obj.schema_id??????????????????????? AS schema_id?? ?????????????????? ,obj.name??????????????????????????? AS table_name?????????????????? ,obj.object_id??????????????????????? AS object_id?????????????????? ,ISNULL(sp.rows,0)??????????????????? AS approximate_rows?????????????????? ,ISNULL(sp.modification_counter,0)??? AS row_mod_ctr??????????? FROM sys.objects obj (NOLOCK)??????????????? JOIN sys.stats st (NOLOCK) ON obj.object_id=st.object_id??????????????? CROSS APPLY sys.dm_db_stats_properties(obj.object_id, st.stats_id) AS sp??????????? WHERE obj.is_ms_shipped = 0??????????????? AND st.stats_id<>0??????????????? AND obj.object_id NOT IN (??????????????????????? SELECT major_id FROM sys.extended_properties WITH(NOLOCK) ??????????????????????????? WHERE name = N''microsoft_database_tools_support'')????????????? ),??????? StatTableGrouped AS??????? (??????????? SELECT ??????????????? ROW_NUMBER() OVER(ORDER BY table_name)??????????? AS seq1,??????????????? ROW_NUMBER() OVER(ORDER BY table_name DESC)??????? AS seq2,??????????????? table_name??????????????????????????????????????? AS table_name,??????????????? CAST(MAX(approximate_rows) AS BIGINT)??????????? AS approximate_rows,??????????????? CAST(MAX(row_mod_ctr) AS BIGINT)??????????????? AS row_mod_ctr,??????????????? COUNT(*)??????????????????????????????????????? AS stats_count,??????????????? schema_id??????????????????????????????????????? AS schema_id,??????????????? object_id??????????????????????????????????????? AS object_id??????????? FROM StatTables st??????????? GROUP BY schema_id,object_id,table_name??????????? HAVING (MAX(approximate_rows) > 500 AND Max(row_mod_ctr) > (Max(approximate_rows)*0.2 + 500 ))??????? )???????? SELECT??????????? @@SERVERNAME??????????????????????? AS instance_name?????????? ,seq1 + seq2 - 1??????????????????????? AS occurences_num?????????? ,SCHEMA_NAME(stg.schema_id)??????????? AS schema_name?????????? ,stg.table_name?????????? ,CASE OBJECTPROPERTY(stg.object_id, ''TableHasClustIndex'')???????????????? WHEN 1 THEN ''Clustered''???????????????? WHEN 0 THEN ''Heap''???????????????? ELSE ''Indexed View''??????????? END AS clustered_or_heap?????????? ,CASE OBJECTPROPERTY(stg.object_id, ''TableHasClustIndex'')????????????????? WHEN 0 THEN (SELECT COUNT(*) FROM sys.indexes i WITH(NOLOCK) WHERE i.object_id= stg.object_id) - 1??????????????????????? ELSE (SELECT COUNT(*) FROM sys.indexes i WITH(NOLOCK) WHERE i.object_id= stg.object_id)???????????? END AS IndexCount??????????? ,(SELECT COUNT(*) FROM sys.columns c (NOLOCK) WHERE c.object_id = stg.object_id ) AS columns_count ??????????? ,stg.stats_count??????????? ,stg.approximate_rows??????????? ,stg.row_mod_ctr??????????? ,stg.schema_id??????????? ,stg.object_id??????? FROM StatTableGrouped stg';???? END;

?

?

參考資料：

?

https://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/

posted on 2019-03-31 23:49 NET未來之路 閱讀(...) 評論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/lonelyxmas/p/10634085.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server 查找统计信息的采样时间与采样比例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。