3

the syntax for creating statistics is:

-- Syntax for SQL Server and Azure SQL Database  

UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ] 
    ] ;  

<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]

and here there is an excellent script by Martin Smith that scripts the stats with STATS_STREAM

when I run that script in one of my test databases I get the following:

DECLARE @Schema             sysname,
        @Table              sysname,
        @StatsName          sysname,
        @IncludeStatsStream BIT,
        @StatsStream        VARCHAR(MAX),
        @TwoPartQuotedName  NVARCHAR(500);

select  @Schema           = 'dbo',
        @Table            = 'tbl_Application_Medical',
        @StatsName        = '_WA_Sys_00000002_7039AB57',
        @IncludeStatsStream = 1;

select @TwoPartQuotedName = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);


IF @IncludeStatsStream = 1 AND @@MICROSOFTVERSION/ POWER(2,24) > 9
  BEGIN
      DECLARE @StatsResults TABLE
        (
           StatsStream VARBINARY(MAX),
           Rows        BIGINT,
           DataPages   BIGINT
        );

      INSERT INTO @StatsResults
      EXEC sys.sp_executesql 
        N'DBCC SHOW_STATISTICS(@TwoPartQuotedName, @StatsName) WITH STATS_STREAM;',
        N'@TwoPartQuotedName NVARCHAR(500), @StatsName sysname',
        @TwoPartQuotedName = @TwoPartQuotedName,
        @StatsName = @StatsName;

      --Would need some other method on 2005 hence just skipping this
      SELECT @StatsStream = CONVERT(VARCHAR(MAX), StatsStream, 1)
      FROM   @StatsResults;
  END;

WITH stats AS
(
/* 
Support earlier versions without these columns using trick from http://dba.stackexchange.com/a/66755/3690 */
SELECT x.*
FROM (SELECT NULL AS filter_definition, NULL AS is_incremental) AS dummy
CROSS APPLY
(
  SELECT object_id, stats_id, name, no_recompute, filter_definition, is_incremental
  FROM sys.stats
) AS x
)
SELECT '
CREATE STATISTICS ' + QUOTENAME(name) + '   
ON ' + @TwoPartQuotedName + ' (' + SUBSTRING(cols, 2, 10000000) +')
'  + 
ISNULL(' WHERE ' + filter_definition,'') +
ISNULL(STUFF ( 
    ISNULL(',STATS_STREAM = ' + @StatsStream, '') +
    CASE WHEN no_recompute = 1 THEN ',NORECOMPUTE' ELSE '' END + 
    CASE WHEN is_incremental = 1 THEN ',INCREMENTAL=ON' ELSE '' END
 , 1 , 1 ,  ' WITH '  ) , '') AS [processing-instruction(x)]
FROM   stats s
       CROSS APPLY (SELECT ',' + QUOTENAME(c.name)
                    FROM   sys.stats_columns sc
                           JOIN sys.columns c
                             ON c.object_id = sc.object_id
                                AND c.column_id = sc.column_id
                    WHERE  sc.object_id = s.object_id
                           AND sc.stats_id = s.stats_id
                    ORDER  BY sc.stats_column_id
                    FOR XML PATH(''))CA(cols)
WHERE  s.object_id = OBJECT_ID(@TwoPartQuotedName)
       AND s.name = @StatsName
FOR XML PATH('');

select * from @StatsResults

a xml file:

<?x CREATE STATISTICS [_WA_Sys_00000002_7039AB57] ON [dbo].[tbl_Application_Medical] ([Smoker]) WITH STATS_STREAM = lot of binary chars that would not fit here?>

enter image description here

when Update statistics for all databases all tables in a server would I benefit from generating the create statistics script with stats_scream, instead of WITH FULLSCAN?

what is the difference between these 2?

3

The other answer is correct so just in case an example makes things clearer...

CREATE TABLE T1
(
C1 VARCHAR(50),
INDEX CIX CLUSTERED(C1)
);

INSERT INTO T1
VALUES ('orange'), ('kiwi'), ('strawberry');


UPDATE STATISTICS [dbo].[T1]([CIX]) 
WITH STATS_STREAM = 0x01000000010000000000000000000000368684C40000000051020000000000001102000000000000A7020000A7000000320000000000000008D000000000000007000000009BA10039AA000003000000000000000300000000000000000000000000003F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000020000000100000010000000ABAAAA400000404000000000ABAAAA4000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001304000000000000000000000000000049000000000000008D0000000000000095000000000000009D0000000000000010000000000000002C000000000000003000100000000040000000000000803F04000001001C006170706C65300010000000803F000000000000803F04000001001D0062616E616E61FF01000000000000000300000002000000280000002800000000000000000000000B0000006170706C6562616E616E61030000004000000000820500000001060500000003000000000000000000000000000000, 
ROWCOUNT = 3, PAGECOUNT = 1

DBCC SHOW_STATISTICS ( T1 , CIX )  WITH HISTOGRAM;   

The statistics are entirely fictitious and bear no relation to table contents. It just uses the histogram encoded in the STATS_STREAM

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| apple        |          0 |       2 |                   0 |              1 |
| banana       |          0 |       1 |                   0 |              1 |
+--------------+------------+---------+---------------------+----------------+

But

UPDATE STATISTICS [dbo].[T1]([CIX]) 
WITH FULLSCAN 

DBCC SHOW_STATISTICS ( T1 , CIX )    WITH HISTOGRAM; 

Scans all the base table rows and creates a new histogram with correct values

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| kiwi         |          0 |       1 |                   0 |              1 |
| orange       |          0 |       1 |                   0 |              1 |
| strawberry   |          0 |       1 |                   0 |              1 |
+--------------+------------+---------+---------------------+----------------+

The STATS_STREAM option is only useful for debugging. It allows you to copy histograms from production to a development instance without needing to copy any data. This can assist when trying to evaluate query plan issues.

4

Using commands with STATS_STREAM you are able to create exact copy of some statistics. It really useful for troubleshooting or for testing purposes when you want to repeat the query optimizer behavior on another table or even in another database.

A great article on the option: Custom Built Statistics

Using a command 'WITH FULLSCAN' you just update query optimization statistics by scanning all rows on a table.

From BOL:

FULLSCAN

Compute statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.