Create Statistics in other filegroup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Create Statistics in other filegroup

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
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
If no resonse means…. either nobody knows… or its not possible [8D] _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
Don’t know whether this linkhttp://www.sqlservercentral.com/columnists/cmiller/gettingridofexcessfilesandfilegroups.asp is related or any help, but may check. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Interesting!
Are you member of SQL Server Central, satya? Frank
http://www.insidesql.de
http://www.familienzirkus.de
Yes, since longback. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

[<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>
Ofcourse, my active part of play will be on this site andhttp://www.sqlmag.com/forums and few times onhttp://www.dbforums too. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

<big grin>, fortunately that leaves some posts there for me to answer. Frank
http://www.insidesql.de
http://www.familienzirkus.de
[^].. 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 />
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
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>
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
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
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
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
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
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
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
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
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
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
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
]]>