locked
Incorrect Statistics Column Order on a Composite Clustered PK RRS feed

  • Question

  • Hello All,

      I've encountered a bit of strange behavior with statistics in a database I am working on. Basically I have a partitioned table that has a composite clustered PK consisting of two columns (arenaId, id) where arenaId is the partitioning column. When looking at statistics created for the PK the order of the columns appears to be inverted (id, arenaId). It appears that the statistics are created using the ordering of the index_column_id and not key_ordinal, which is how the actual index is organized. Below is a screengrab of the relevant system views illustrating the problem:

    I suspect that this is causing suboptimal query plans, since the statistics and historgram is probably not correct. The only way I was able to get statistics column order to match the PK column order is to actually create the table with column order matching the PK order like so:

    This behavior is the same across SQL Server 2008 R2 SP1 and 2012 RTM. My question is - is this the way it supposed to work or is this a bug that I should file with MS? Also is statistics order not matching PK order is in fact a problem that could affect selection of the best query plan?

    Thanks in advance,
    Aleksandr Krymskiy

    • Moved by Maggie Luo Friday, August 31, 2012 9:26 AM (From:SQL Server Database Engine)
    Tuesday, August 28, 2012 2:58 PM

Answers

  • I see what you mean.  Hrm. looking at BOL for stats_column_id  it mentions that it is supposed to be a one based ordinal, which should mean it should show up same as the index_column table.  

    Running the DBCC SHOW_STATS command shows the columns in proper order.. it appears that the stats_columns and SSMS is incorrect.  

    If I had a vote, I'd say bug, but in display only, not the actual statistic. I have a couple of partitioned tables and they perform as expected, and SHOW_STATS is correct.  (I've never looked at them through SSMS before, but they are in column order, not actual KEY order)

    -JohnV


    • Edited by JohnVal Tuesday, August 28, 2012 7:03 PM clarified
    • Marked as answer by Aleksandr Krymskiy Friday, August 31, 2012 5:24 PM
    Tuesday, August 28, 2012 6:55 PM
  •   Yeah, I just ran the SHOW_STATS as well and the density vector appears to be in the correct order (the clustered index column order) for both table arrangment I tried. The histogram is on the arenaId column as well (as described in the documentation - only the first column is used).

     I think you are right - this is probably a bug with the sys.stats_column view and SSMS UI is probably grabbing the information from there.

    Thanks for the SHOW_STATs reminder - I guess in relying on the correctness of the views and SSMS UI, I did not explore the other options..

    Alex

    Tuesday, August 28, 2012 7:06 PM

All replies

  • if you look at your first set of queries, the INDEX_COLUMN_ID is 1,2 but the KEY_ORDINAL is 2, 1.  Even the partition ordinal looks correct in your first set.

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

    key_ordinal - Ordinal (1-based) within set of key-columns.

    hth

    -John V

    P.S.

    if you run ShowStats on the index, you should see the correct order as well.

    DBCC show_statistics ('dbo.ArenaLeaderBoard',PK__ArenaLeaderBoard) 

    • Edited by JohnVal Tuesday, August 28, 2012 6:36 PM more info
    Tuesday, August 28, 2012 6:21 PM
  • Hi John,

      Thanks for the response. Yes, the index meta-data is correct - my issue is with the statistics. If you look at the sys.stats_columns query - the order is 1, 2 unlike the key_ordinal of the index which is (2, 1). This is also reflected in SSMS UI where the index properties dialog column order shows:

         arenaId
         id

    while statistics properties dialog shows:

         id
         arenaId

    Thanks,
    Alex

    Tuesday, August 28, 2012 6:43 PM
  • I see what you mean.  Hrm. looking at BOL for stats_column_id  it mentions that it is supposed to be a one based ordinal, which should mean it should show up same as the index_column table.  

    Running the DBCC SHOW_STATS command shows the columns in proper order.. it appears that the stats_columns and SSMS is incorrect.  

    If I had a vote, I'd say bug, but in display only, not the actual statistic. I have a couple of partitioned tables and they perform as expected, and SHOW_STATS is correct.  (I've never looked at them through SSMS before, but they are in column order, not actual KEY order)

    -JohnV


    • Edited by JohnVal Tuesday, August 28, 2012 7:03 PM clarified
    • Marked as answer by Aleksandr Krymskiy Friday, August 31, 2012 5:24 PM
    Tuesday, August 28, 2012 6:55 PM
  •   Yeah, I just ran the SHOW_STATS as well and the density vector appears to be in the correct order (the clustered index column order) for both table arrangment I tried. The histogram is on the arenaId column as well (as described in the documentation - only the first column is used).

     I think you are right - this is probably a bug with the sys.stats_column view and SSMS UI is probably grabbing the information from there.

    Thanks for the SHOW_STATs reminder - I guess in relying on the correctness of the views and SSMS UI, I did not explore the other options..

    Alex

    Tuesday, August 28, 2012 7:06 PM
  • that's funny, I was thinking the same thing about your method ;-)  I don't use the UI tools very often so I forget what's possible via the UI.

    Best of luck,

    -JohnV

    Wednesday, August 29, 2012 5:53 PM
  • SELECT TOP 1 s.name,c.name, ISNULL(ic.key_ordinal,sc.stats_column_id) as [Final Order!!!!!!!!]
        FROM sys.stats s (nolock)
        JOIN sys.stats_columns sc (nolock) ON s.stats_id=sc.stats_id AND s.object_id=sc.object_id
        JOIN sys.columns c (nolock) ON c.object_id=s.object_id AND c.column_id=sc.column_id
        LEFT JOIN sys.indexes i (nolock) ON i.index_id=s.stats_id AND i.object_id=s.object_id
        LEFT JOIN sys.index_columns ic (nolock) ON i.index_id=ic.index_id AND ic.object_id=i.object_id AND c.column_id=ic.index_column_id

    Wednesday, February 11, 2015 1:05 PM