Post reply

How to find (or create) the stats_stream, rowcount, and pagecount used in Update Statistics

  • January 15, 2013 at 3:06 pm

    #272776

    I have been asked to script out the creation of specific tables in the database including primary key, nonclustered indexes, all constraints, and statistics. I am almost done with it except for the update statistics.

    I can't find any information on how to find (or create) the hex stats_stream on a specific statistic. Anyone know how that is created?

    I need the stats_stream, rowcount, and pagecount.

    Example below:

    UPDATE STATISTICS [dbo].[StgVendItem]([XIE1StgVendItem]) WITH STATS_STREAM = 0x01000000040000000000000000000000B98268400000000088000000000000000000000000000000380100003800000004000A00000000000000000000000000A7020000A70000000C0000000000000008D0003400000000A7020000A70000001E0000000000000008D0003400000000380100003800000004000A00000000000000000000000000, ROWCOUNT = 0, PAGECOUNT = 0

  • January 15, 2013 at 6:35 pm

    #1577577

    From BOL: "Not supported. Future compatibility is not guaranteed."

    http://msdn.microsoft.com/en-us/library/ms187348.aspx

    Why don't you just create and/or refresh statistics to get realistic data ?

    You could update system tables using dedicated admin connection (put "admin:" in front of server name) and starting the sql instance in single-user mode ("-m;" startup parameter), but why doing such invasive stuff when you can probably go with normal, supported methods?

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • January 16, 2013 at 3:15 am

    #1577728

    DBCC SHOW_STATISTICS(N'[dbo].[StgVendItem]', N'XIE1StgVendItem')

    WITH STATS_STREAM

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • January 16, 2013 at 5:06 am

    #1577758

    Nice article here: http://blog.kejser.org/2011/07/07/the-ascending-column-problem-in-fact-tables-part-two-stat-job/[/url]

    Still - unsupported, so you use it on your own risk.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply