续：有关SQL SERVER分布统计的问题(二)
(200) DEFAULT NEWID(),
19: Col18 VarChar(200) DEFAULT NEWID(),
20: Col19 VarChar(200) DEFAULT NEWID(),
21: Col20 VarChar(200) DEFAULT NEWID(),
22: Col21 VarChar(200) DEFAULT NEWID(),
23: Col22 VarChar(200) DEFAULT NEWID(),
24: Col23 VarChar(200) DEFAULT NEWID(),
25: Col24 VarChar(200) DEFAULT NEWID(),
26: Col25 VarChar(200) DEFAULT NEWID())
27: GO
28: INSERT INTO Tab1 DEFAULT VALUES
29: GO 10000
接着，我们来运行DBCC来执行索引重建，注意该表没有任何统计，你可以通过PROFILER来检查。
1: DBCC DBREINDEX (Tab1)
2: GO
现在我们假设你在表上为每一列创建一个统计，这意味着重建会触发统计的更新，通常你不必太在意，但是要清楚如何管理就行。
要为每一列创建统计对象，可以使用sp_createstats存储过程。
EXEC sp_createstats;
GO
执行上面的存储过程后，将为表Tab1的26个字段分别创建一个统计，然后再执行重建操作。
DBCC DBREINDEX (Tab1)
GO
从上面的输出可以看出，有大量的更新统计（SELECT StatsMan…）执行。
接下来我们来看一下统计对象占用多少的容量。
统计对象所占用的空间容量实际上很小，不会对系统性能造成影响，如果你持怀疑态度，想调查统计对象实际占用了多少容量，运行以下查询:
1: IF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL
2: DROP TABLE #TMP
3: GO
4: CREATE TABLE #TMP (ID Int Identity(1,1) PRIMARY KEY,
5: Table_Name VarChar(200),
6: Column_Name VarChar(200),
7: Stats_Name VarChar(200),
8: ColStats_Stream VarBinary(MAX),
9: ColRows BigInt,
10: ColData_Pages BigInt)
11: GO
12: DECLARE @Tab TABLE (ROWID Int IDENTITY(1,1) PRIMARY KEY,
13: Table_Name VarChar(200),
14: Column_Name VarChar(200),
15: Stats_Name VarChar(200))
16:
17: DECLARE @i Int = 0,
18: @Table_Name VarChar(200) = '',
19: @Column_Name VarChar(200) = '',
20: @Stats_Name VarChar(200) = ''
21:
22: INSERT INTO @Tab (Table_Name, Column_Name, Stats_Name)
23: SELECT Schema_name(sys.objects.schema_id) + '.' + Object_Name(sys.stats.object_id) AS Table_Name,
24: sys.columns.name AS Column_Name,
25: sys.stats.Name AS Stats_Name
26: FROM sys.stats
27: INNER JOIN sys.stats_columns
28: ON stats.object_id = stats_columns.object_id
29: AND stats.stats_id = stats_columns.stats_id
30: INNER JOIN sys.columns
31: ON stats_columns.object_id = columns.object_id
32: AND stats_columns.column_id = columns.column_id
33: INNER JOIN sys.objects
34: ON stats.object_id = objects.object_id
35: LEFT OUTER JOIN sys.indexes
36: ON sys.stats.Name = sys.indexes.Name
37: WHERE sys.objects.type = 'U'
38: ORDER BY Table_Name
39:
40: SELECT TOP 1 @i = ROWID,
41: @Table_Name = Table_Name,
42: @Column_Name = Column_Name,
43: @Stats_Name = Stats_Name
44: FROM @Tab
45: WHERE ROWID > @I
46: WHILE @@RowCount > 0
47: BEGIN
48: --PRINT 'UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN'
49: --EXEC ('UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN')
50: INSERT INTO #TMP(ColStats_Stream, ColRows, ColData_Pages)
51: EXEC ('DBCC SHOW_STATISTICS ("' + @Table_Name + '", "'+@Stats_Name+'") WITH STATS_STREAM')
52: ;WITH CTE_Temp AS (SELECT TOP (@@RowCount) * FROM #TMP ORDER BY ID DESC)
53: UPDATE CTE_Temp
54: SET Table_Name = @Table_Name,
5