ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 续:有关SQL SERVER分布统计的问题

续:有关SQL SERVER分布统计的问题

原创 Linux操作系统 作者:bigholy 时间:2012-03-21 13:36:44 0 删除 编辑

查询统计

现在我们来粗略地看下直方图的工作方式,重要的是要了解如何查询表中的统计个数,幸运的是可以使用以下命令来查询:

   1: SELECT Schema_name(sys.objects.schema_id) + '.' + Object_Name(sys.stats.object_id) AS Table_Name,
   2:        sys.columns.name AS Column_Name,
   3:        sys.stats.Name AS Stats_Name
   4:   FROM sys.stats
   5:  INNER JOIN sys.stats_columns
   6:     ON stats.object_id = stats_columns.object_id
   7:    AND stats.stats_id = stats_columns.stats_id
   8:  INNER JOIN sys.columns
   9:    ON stats_columns.object_id = columns.object_id
  10:   AND stats_columns.column_id = columns.column_id
  11:  INNER JOIN sys.objects
  12:     ON stats.object_id = objects.object_id
  13:   LEFT OUTER JOIN sys.indexes
  14:     ON sys.stats.Name = sys.indexes.Name
  15:  WHERE sys.objects.type = 'U'
  16:    --AND sys.objects.name = 'Tab1'
  17:  ORDER BY Table_Name
  18: GO

查询结果:

image

创建统计

在对你的数据库运行上面的查询时看到的统计对象是自动创建的,当然,你也可以通过运行以下命令来手动创建:

   1: CREATE STATISTICS Stats_MyStatOnCol1 ON t1(i) WITH FULLSCAN

上面的这条命令将对表t1的i列上创建一个统计对象,同时使用WITH FULLSCAN参数来扫描整个表,而不是采样部分行,主要是为了得到更为精确的统计直方图。

不过,FULL SCAN的开销也是比较大的,但是要比采样扫描更为精确。

采样扫描

默认,SQL SERVER会根据现有的统计对象是否过期来进行创建或更新统计;当检测到与当前数据不匹配时,SQL SERVER会采样表中的数据进行重建统计,默认的采样频率是根据表的大小进行缓慢增加。

当使用采样创建统计时,SQL SERVER会从IAM链中随机选取一些页面,一旦某个页面选定后,页面中的这些数据就作为采样的数据源,这偶尔对导致一些不正确的数据统计,为此,你可能使用FULLSCAN来重建统计。

注意:有关IAM链的信息可以从这个链接http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx获取详细的内容,另外,使用下面的信息来检测标记过期统计的方法,当统计的列发生更新时,SQL SERVER会根据实例中设置的“自动更新统计”来保持数据最新,其工作的方法如下:

  • 当表的行数小于6条,并且该表存储在TEMPDB数据库中,每发生6次修改会触发自动更新
  • 当表的行数大于6条,并且小于等于500,每发生500次修改会触发自动更新
  • 当表的行数大于500条,表中的(500+20%)的数据发生变化会触发自动列新
  • 对于表变量来说,并不会触发自动更新

 

全扫描

在有些表中,经常看到由于某些列发生变化,与这些列相关的统计也会自动更新,但是若发现创建的统计是基于采样的数据,那你可能需要手动运行

UPDATE STATISTICS WITH FULLSCAN来更新统计,如下命令:

   1: UPDATE STATISTICS Tab1 Stats_MyStatOnCol1 WITH FULLSCAN

 

说到这里,你可能会问:有必要关注数据库中统计所占用的空间吗?

答案取决于多种因素,通常不用过多关注统计所占用的空间,但是也不总是这样;如果你遇到一个含有非常长的列的表,或许你应该调查一下服务器是否因花费过多的时间和资源来更新这些统计的必要性。

在维护期间重建索引和更新统计,对于一个系统中数据量比较大的表来说,可能删除那些未使用的统计,这样有助于提高重建和统计更新的速度,不过并没有一种方法来检查统计对象是否使用,很难找出哪些统计对象未被使用。

 

测试

下面来通过一个示例来介绍,脚本创建一个示例表Tab1(26列),然后插入1万行:

   1: CREATE TABLE Tab1 (ID Int IDENTITY(1,1) PRIMARY KEY, 
   2:                    Col1 VarChar(200) DEFAULT NEWID(),
   3:                    Col2 VarChar(200) DEFAULT NEWID(),
   4:                    Col3 VarChar(200) DEFAULT NEWID(),
   5:                    Col4 VarChar(200) DEFAULT NEWID(),
   6:                    Col5 VarChar(200) DEFAULT NEWID(),
   7:                    Col6 VarChar(200) DEFAULT NEWID(),
   8:                    Col7 VarChar(200) DEFAULT NEWID(),
   9:                    Col8 VarChar(200) DEFAULT NEWID(),
  10:                    Col9 VarChar(200) DEFAULT NEWID(),
  11:                    Col10 VarChar(200) DEFAULT NEWID(),
  12:                    Col11 VarChar(200) DEFAULT NEWID(),
  13:                    Col12 VarChar(200) DEFAULT NEWID(),
  14:                    Col13 VarChar(200) DEFAULT NEWID(),
  15:                    Col14 VarChar(200) DEFAULT NEWID(),
  16:                    Col15 VarChar(200) DEFAULT NEWID(),
  17:                    Col16 VarChar(200) DEFAULT NEWID(),
  18:                    Col17 VarChar(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
image

现在我们假设你在表上为每一列创建一个统计,这意味着重建会触发统计的更新,通常你不必太在意,但是要清楚如何管理就行。

要为每一列创建统计对象,可以使用sp_createstats存储过程。

EXEC sp_createstats;
GO

执行上面的存储过程后,将为表Tab1的26个字段分别创建一个统计,然后再执行重建操作。

DBCC DBREINDEX (Tab1)
GO

image

从上面的输出可以看出,有大量的更新统计(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, 
  55:          Column_Name = @Column_Name, 
  56:          Stats_Name = @Stats_Name
  57:   SELECT TOP 1 @i = ROWID, 
  58:          @Table_Name = Table_Name,
  59:          @Column_Name = Column_Name,
  60:          @Stats_Name = Stats_Name
  61:     FROM @Tab
  62:    WHERE ROWID > @I
  63: END
  64: GO
  65: SELECT SUM(DATALENGTH(ColStats_Stream) / 1024.) AS [Size KB]
  66:   FROM #TMP
  67: GO
  68: SELECT Table_Name,
  69:        Column_Name,
  70:        Stats_Name,
  71:        ColStats_Stream,
  72:        DATALENGTH(ColStats_Stream) / 1024. AS [Size KB]
  73:   FROM #TMP
  74:  ORDER BY [Size KB] DESC

 

image 

从Size KB列可以知道每一个统计对象使用的字节数。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-719177/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    82609