March 4, 2010 at 12:18 pm
Is there an easy way to move statistics from one filegroup to another?
I can script out the indexes and alter the filegroup specified but I can't seem to see where to script out the statistics (in batch, not one at a time). When I go to Tools > Scripting and turn on the Script Statistics option it doesn't work.
Thanks,
John
March 4, 2010 at 12:45 pm
If moving an index to a new file group, you have to recreate the index on the new location with drop option, and the corresponding statistics will be created and stored accordingly.
March 4, 2010 at 1:32 pm
The stats blobs are stored within the system tables, hence they will always be on PRIMARY and cannot be moved.
Why do you want to move them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 3:25 pm
That's strange... when I do a select from sysobjects where i specify the groupid all it's showing now are textimage indexes and statistics. Do you think these could be tied to the textimage?
I am going to try to use the GUI to relocate the textimage (sql 2000). I'm hoping it'll either move or recreate the statistics when I do that.
Any thoughts?
John
March 4, 2010 at 3:57 pm
JohnnyDBA (3/4/2010)
That's strange... when I do a select from sysobjects where i specify the groupid all it's showing now are textimage indexes and statistics. Do you think these could be tied to the textimage?
What query exactly? SQL 2000 or 2005?
Neither textimage 'indexes' nor statistics show in sysobjects, they're both in sysindexes.
I am going to try to use the GUI to relocate the textimage (sql 2000). I'm hoping it'll either move or recreate the statistics when I do that.
The textimage (an 'index' that represents the text, ntext, image columns in a table) cannot be moved. There's no syntax to do so. Only way is to create a new table and specify, for that new table TEXTIMAGE ON [AnotherFileGroup] then moev the data across to the new table.
It's tedious to say the least.
Statistics, as I said above, are stored in the system tables (the column STATSBLOB on SQL 2000) and hence cannot be moved.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 4:00 pm
Oops i meant sysindexes... sql 2000... sorry that was a type-o
As far as using the GUI, it says online that if I update the location in the designer it'll recreate / swap the tables under the hood for me. I'm thinking that might be easiest.
And if the statistics are in the system tables... should I just drop them then and let the system recreate them?
John
March 4, 2010 at 4:04 pm
See "Datadude and the Text Filegroup"
Well, it turns out that if a table used to have a LOB in it, then the Text Filegroup is preserved. Deleting the LOB column does not zero the entry out of the lob_dataspaces_id column in sys.table. Imagine that.
http://blogs.msdn.com/duncand/archive/2007/01/27/datadude-and-the-text-filegroup.aspx
SQL = Scarcely Qualifies as a Language
March 4, 2010 at 4:07 pm
JohnnyDBA (3/4/2010)
As far as using the GUI, it says online that if I update the location in the designer it'll recreate / swap the tables under the hood for me. I'm thinking that might be easiest.
Just watch out for the time that takes if the table's large
And if the statistics are in the system tables... should I just drop them then and let the system recreate them?
Why?
p.s. Please post SQL 2000 questions in the SQL 2000 forums in future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 4:42 pm
Sorry I should have posted the "why" at the beginning... Basically we are trying to empty a file group and get rid of the file in that file group. I currently cannot because I cannot move the image / text index and i cannot move the statistics.
The tables are relatively small so I'm not worried about recreating the table so much, but I am just trying to get the statistics issue worked out now.
Thanks,
John
PS - I didn't realize this was 2000 until after I posted (we have a lot of servers 😀 )
March 4, 2010 at 4:50 pm
Statistics are stored in the system tables, not on a file group. They're stored in sysindexes which is always in PRIMARY, so groupid = 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 7:02 pm
I don't think that's right... because when I do SELECT * FROM sysindexes WHERE indid = 255 and groupid = 3 I'm getting results back
John
March 4, 2010 at 11:11 pm
indid = 255 are text images, not statistics.
Statistics have indid anywhere between 2 and 249 and are identified either using the INDEXPROPERTY function ("IsStatistics" property) or by noting that columns such as root and firstIAM are null (since they have no actual storage)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply