locked
Get data from Statistics (Histogram) RRS feed

  • Question

  • How can I get the number of values that match each distinct column value from Statistics in SQL server??

    I want to calculate the most common values (popular values)??

    Also, how can I use DBCC SHOW_STATISTICS   values in queries??

    Thanks in advanced.. I appreciate any help ..






    Wednesday, June 25, 2014 4:53 AM

Answers

  • Exporting DBCC SHOW_STATISTICS results into a table.

    Try:

    use AdventureWorks2012;
    go
    create table #histogram (
        [RANGE_HI_KEY] sql_variant
    ,   [RANGE_ROWS] sql_variant
    ,   [EQ_ROWS] sql_variant
    ,   [DISTINCT_RANGE_ROWS]  sql_variant
    ,   [AVG_RANGE_ROWS] sql_variant
    );
    go
    
    insert into #histogram exec ('dbcc show_statistics ( "Sales.SalesOrderHeader", "PK_SalesOrderHeader_SalesOrderID") with  HISTOGRAM');
    go
    
    select * from #histogram;
    /*
    RANGE_HI_KEY	RANGE_ROWS	EQ_ROWS	DISTINCT_RANGE_ROWS	AVG_RANGE_ROWS
    43659	0	1	0	1
    75123	31463	1	31463	1
    */


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:37 AM
    Wednesday, June 25, 2014 5:45 AM
  • Hi Dareen Fadol,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps?If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Dareen Fadol Tuesday, October 21, 2014 12:40 AM
    Tuesday, July 15, 2014 2:38 AM

All replies

  • Hi Dareen,

                     You can use the histogram value for query tuning just for analysis, but it is not advisable to make use of that value in query, because it can update statistics when there is more the 20% + sample data changes in base table

    please elaborate the need so that we can help you.

    yes you can see the statistics histogram using DBCC SHOW_STATISTICS

    http://msdn.microsoft.com/en-IN/library/ms174384.aspx

    try below link to get inside

    http://www.patrickkeisler.com/2013/02/what-is-dbcc-showstatistics-telling-me.html

    Thanks

    Saravana Kumar C



    • Edited by SaravanaC Wednesday, June 25, 2014 5:14 AM
    • Proposed as answer by Aalamjeet Rangi Wednesday, June 25, 2014 5:10 PM
    Wednesday, June 25, 2014 5:11 AM
  • Exporting DBCC SHOW_STATISTICS results into a table.

    Try:

    use AdventureWorks2012;
    go
    create table #histogram (
        [RANGE_HI_KEY] sql_variant
    ,   [RANGE_ROWS] sql_variant
    ,   [EQ_ROWS] sql_variant
    ,   [DISTINCT_RANGE_ROWS]  sql_variant
    ,   [AVG_RANGE_ROWS] sql_variant
    );
    go
    
    insert into #histogram exec ('dbcc show_statistics ( "Sales.SalesOrderHeader", "PK_SalesOrderHeader_SalesOrderID") with  HISTOGRAM');
    go
    
    select * from #histogram;
    /*
    RANGE_HI_KEY	RANGE_ROWS	EQ_ROWS	DISTINCT_RANGE_ROWS	AVG_RANGE_ROWS
    43659	0	1	0	1
    75123	31463	1	31463	1
    */


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:37 AM
    Wednesday, June 25, 2014 5:45 AM
  • This doesn't make a lot of sense. Statistics might be sampled not actually counted. And statistics might not be up-to-date. Surely it would be better to count the rows in the table, something like this:

    USE AdventureWorks2008R2;
    GO
    SELECT COUNT(AccountNumber) AS CountOfOrders, AccountNumber
    FROM [Sales].[SalesOrderHeader]
    GROUP BY AccountNumber
    ORDER BY COUNT(AccountNumber) DESC;
    GO


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Sofiya Li Thursday, June 26, 2014 1:45 AM
    Wednesday, June 25, 2014 4:41 PM
  • @SaravanaC

    Thanks for your reply, and here is what I'm doing. I'm working on a project with my friend. She could make some changes in Postgres (PG) optimizer  to do specific operations. We want to apply these operations to any DBMS without migration to PG. So, we wrote a code that takes the values we need from different types of DBMS, and then send these values to PG to do the needed calculations. After that the program gets the results from postgres optimizer.

    The optimizer in PG needs the most common values to do the calculations. We could find these values in Oracle's histogram and MySQL's histogram, but we could not find them in SQL Server histogram. It's not practical to calculate them using regular query specially if are dealing with big amount of data.

    Thanks, 

      
    Friday, June 27, 2014 5:37 AM
  • @Kalman Toth

    Thanks, I'll try it and let you know if  it works with me

    Friday, June 27, 2014 5:41 AM
  • @Kalman Toth

    Thank for your reply, but it's not practical to do it this way if I'm working with big amount of data. That's why asked if I can get these values from the histogram.


    Friday, June 27, 2014 5:44 AM
  • Hi Dareen Fadol,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps?If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Dareen Fadol Tuesday, October 21, 2014 12:40 AM
    Tuesday, July 15, 2014 2:38 AM