SQL Server Performance

Create Statistics in other filegroup

Discussion in 'General DBA Questions' started by Luis Martin, Nov 20, 2003.

  1. Luis Martin Moderator

    Any way to create statistics in new filegroup with index only and no data?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  2. Luis Martin Moderator

    No way?




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. satya Moderator

    If no resonse means.... either nobody knows... or its not possible [8D]

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. Luis Martin Moderator

    Well, I love impossibles questions[<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br />Luis Martin<br /><br />...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell
  5. satya Moderator

  6. FrankKalis Moderator

  7. satya Moderator

    Yes, since longback.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] But not very active, right?<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  9. satya Moderator

  10. FrankKalis Moderator

  11. satya Moderator

    [^].. don't be [<img src='/community/emoticons/emotion-11.gif' alt='8)' />] be my guest.<br /><br />_________<br />Satya SKJ<br />Moderator<br />SQL-Server-Performance.Com<br />
  12. Luis Martin Moderator

    Weel, nice chat.

    I read link and apparently statistics belongs to filegroup where table was created.
    No way to change to another filegroup. Except changing table too.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  13. FrankKalis Moderator

    Yes, Luis, it's not all about our profession [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  14. jasper_smith New Member

    Statistics are simply rows in sysindexes. System objects always live in the primary filegroup. Thus if you create a database with 3 filegroups e.g.

    PRIMARY
    DATA
    INDEX

    and place tables on DATA and nonclustered indexes on INDEX, your statistics will always be in a different filegroup than your data and indexes. If you put your indexes in the primary filegroup then indeed your statistics and indexes would exist on a filegroup with no user data (but would be on the same filegroup as the system objects)


    HTH

    Jasper Smith
  15. Luis Martin Moderator

    So you said: Statistics allways go to primary filegroup. No mather where Tables and Index are?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  16. jasper_smith New Member

    Correct, system objects have to live in the primary filegroup, so this is where the sysindexes table will be and thus where your statistics will be.

    HTH

    Jasper Smith
  17. Luis Martin Moderator

    Ok. Thanks.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  18. Luis Martin Moderator

    That is what I found. (Working in test)
    Statistics belongs to filegroup where Cluster Index belong.
    If no Cluster Index, then go to Primary.
    So if table is in Filegroup A, and Cluster Index is in Filegroup B, then statisics go to B.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  19. jasper_smith New Member

    A clustered index is the table - they cannot exist on different filegroups because they are the same object [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Only nonclustered indexes can exist on a separate filegroup than their base table. As I mentioned earlier, statistics are simply rows in sysindexes and thus will always exist only in the primary filegroup, this is regardless of anything else, system objects (such as sysindexes) can only exist in the primary filegroup. The placement of the table/indexes and the existence of a clustered index have no bearing on that. I'm not sure how you reached your conclusions ?<br /><br /><br />HTH<br /><br />Jasper Smith
  20. Luis Martin Moderator

    Well you are write, and I was wrong, in one thing. When I create a cluster in other filegroup table was move to that filegropu. My mistake.

    But statistics made by me, via ITW, go to where cluster belongs not Primary. When I said primary I'm tooking about default filegroup. When I said other filegroup I mean made by me.

    Regards my conclusions, well, like I said 1rst part my mistake.
    Second part looking where statistics are.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  21. jasper_smith New Member

    quote:But statistics made by me, via ITW, go to where cluster belongs not Primary. When I said primary I'm tooking about default filegroup. When I said other filegroup I mean made by me

    The "statistics" created by the ITW (name starts 'hind') are actually hypothetical indexes and NOT true statistics (and not a true index either) . They are created using the undocumented "with statistics_only" index option and cannot be used directly as a data access path. You can confirm this using INDEXPROPERTY and the IsHypothetical and IsStatistics properties. They are used by the ITW as part of its optimisation process but should actually be dropped when it finishes (there is a bug that if you cancel out of ITW at certain stages it doesn't drop these so you have to clean them up manually). However, these hypothetical indexes don't have any storage as such, they are much more like statistics than an index. I'm confused by your comment above and how you are determining the placement of these hypothetical indexes (which again are just rows in sysindexes and thus exist in the primary filegroup only) ?


    HTH

    Jasper Smith
  22. Luis Martin Moderator

    Microsoft documentation said about Hypothetical Index: May be.

    Here is an example of statistics (hint if you want).

    CREATE STATISTICS [hind03.10.10_Statistic_Debe] ON [dbo].[LINASIENTOS] ([DEBE])

    The following scrip is what I use to find Hyphotetical Index:

    ELECT SO.Name as 'Tabla', SI.name as 'Indice'
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    where INDEXPROPERTY(OBJECT_ID(SO.Name), SI.name , 'IsHypothetical')= 1

    And the followint script is what I use to find where index and statistics (hind) are:

    select si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as 'Filegroup'
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    join sysfilegroups as SFG
    on SI.GroupId = SFG.GroupId
    where left(SI.Name,4) not in ('_WA_') and left(si.Name,3) like ('ix%') or left(si.Name,3) in ('hin')
    order by si.rows desc, SO.Name , SI.name, SFG.GroupName

    And the next is one row just as example:

    Table Index/Stat Filegruop
    LINASIENTOShind03.10.10_Statistic_DebeTercero




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  23. jasper_smith New Member

    Hypothetical indexes and statistics do not have separate physical storage, they do not "exist" on the filegroup indicated in sysindexes, they only exist as rows in sysindexes. I think the ITW is confusing matters. The important point is that statistics and hypothetical indexes exist only as rows in sysindexes and by definition only in the primary filegroup. The groupid is irrelavent. Also, you should drop all the hind stuff (especially remnant hypothetical indexes). It's not useful and can cause problems.


    HTH

    Jasper Smith
  24. Luis Martin Moderator

    Agree with you.
    I allways delete hypothetical index, just I said I use scrip to find then and delete.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell

Share This Page