January 26, 2010 at 9:55 am
I have a very large database. Let me explain more about the database. It is SQL Server 2005 database. Size is around 600 GB. the database consist of 3 mdf files and 1 ldf file. Now since the database is very hugely used we are planning to move some tables to a different file group( to a different mdf group.)
But I heard that one cannot move table, but one has to move the indexes and that will move the table. So was wandering what are the indexes that can be moved to a new filegroup are they cluster index or non-cluster index. And besides moving them by the below method what is the other way?
1.In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine and then expand that instance.
2.Expand Databases, expand the database that contains the table with the specific index, and then expand Tables.
3.Expand the table in which the index belongs and then expand Indexes.
4.Right-click the index to be moved and then select Properties.
5.On the Index Properties dialog box, select the Storage page.
6.Select the filegroup in which to move the index.
7. click OK.
January 26, 2010 at 11:21 am
any idea friends?????????
January 26, 2010 at 11:34 am
espanolanthony (1/26/2010)
...But I heard that one cannot move table, but one has to move the indexes and that will move the table.
A clustered index is the data for the table. If you move that, then you will move the table. The exception to this rule is if BLOB data exists in the table. If there is blob data, then you would have to recreate the table on the new filegroup and then transfer the data. Another exception is if the table does not have a clustered index. To move the table in that scenario, you must create a clustered index on the new filegroup which would move the table.
So was wandering what are the indexes that can be moved to a new filegroup are they cluster index or non-cluster index. And besides moving them by the below method what is the other way?
Both Clustered and Non-clustered can be moved. Non-clustered is easier, Clustered requires PK drop and creates in the scenario that your Clustered index is on the PK.
1.In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine and then expand that instance.
2.Expand Databases, expand the database that contains the table with the specific index, and then expand Tables.
3.Expand the table in which the index belongs and then expand Indexes.
4.Right-click the index to be moved and then select Properties.
5.On the Index Properties dialog box, select the Storage page.
6.Select the filegroup in which to move the index.
7. click OK.
For Non-clustered indexes, you could use the above method. For clustered indexes, you should probably script it out.
There are scripts available at SSC that discuss this.
Here is an article that also discusses moving en masse your indexes and tables.
http://qa.sqlservercentral.com/articles/Files+and+Filegroups/65538/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 12:02 pm
ok so i used the SSMS way and changed the filegroup. but now i run the below query to see the index description, size etc and still in the index description column I see the old filegroup name location, instead of the new one now. any idea?
-------------------------------------------
Set NOCOUNT on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Table_Name varchar(100)
CREATE TABLE #Temp (Table_Name varchar(200), INdex_Name varchar(200),
Index_Description varchar(200), IndexKeys varchar(200), Index_Size_MB int)
DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name from sysobjects WHERE xtype = 'U'
OPEN TCursor
FETCH NEXT FROM TCursor INTO @Table_Name
WHILE @@FETCH_STATUS = 0
begin
INSERT INTO #Temp (INdex_Name, Index_Description, IndexKeys)
EXEC sp_helpindex @Table_Name
UPDATE #Temp SET Table_Name = @Table_Name WHERE Table_Name IS NULL
FETCH NEXT FROM TCursor INTO @Table_Name
END
close TCursor
DEALLOCATE TCursor
GO
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
UPDATE #Temp SET Index_Size_MB =
((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #Temp T ON T.Table_Name = sysobjects.name AND T.INdex_Name = sysindexes.name
select * from #Temp
order by Index_Size_MB desc
GO
DROP TABLE #Tempemp
GO
January 26, 2010 at 12:12 pm
Here is script that I prefer to use than what you are using.
Run this and let me know if the filegroups show differently in it.
with tablesize as (
select so.Name as TableName
,TableSizeMB = convert(decimal(15,2),si.dpages *8 / 1024)
,IndexSizeMB = convert(decimal(15,2),sum(isnull(si2.used,0))*8 / 1024)
,TotalSizeMB = convert(decimal(15,2),(si.dpages * 8 /1024) + (sum(isnull(si2.used,0))*8 / 1024))
,TableFreeMB = convert(decimal(15,2),(si.reserved * 8 /1024) -(si.used * 8/1024))
,TableSizeKB = convert(decimal(15,2),si.dpages *8)
,IndexSizeKB = convert(decimal(15,2),sum(isnull(si2.used,0))*8)
,f.Name as FileGroupName
,d.physical_name as FGFileName
,(select convert(decimal(15,2),sum(reserved)* 8 /1024) from sysindexes where indid in (0,1)) as DBSize
from sysindexes si
Inner join sys.objects so
on so.object_id = si.id
and so.is_ms_shipped = 0
-- and so.type = 'U'
and si.indid in (0,1)
and so.name <> 'sysdiagrams'
Inner join sysindexes si2
on so.object_id = si2.id
and si2.indid > 1
and si2.indid < 255
Inner Join sys.filegroups f
on f.data_space_id = si.groupid
Inner Join sys.database_files d
on f.data_space_id = d.data_space_id
group by so.Name,si.dpages,f.Name,d.physical_name,si.reserved,si.used
)
Select TableName,TableSizeMB,IndexSizeMB,TotalSizeMB,TableFreeMB,TableSizeKB,IndexSizeKB,FileGroupName,FGFileName,DBSize
,convert(decimal(15,12),(TableSizeMB + IndexSizeMB)/DBSize) * 100 as TablePercentofDB
from TableSize
Order by FileGroupName asc,TableSizeMB desc
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 12:32 pm
ok now i see they are there thanks a lot for the script. appreciate it
January 26, 2010 at 12:35 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 1:08 pm
one question how can i see which table has clustered index?
January 26, 2010 at 1:19 pm
Try the following:
http://jasonbrimhall.info/?p=97
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 1:45 pm
thanks again friend.
January 26, 2010 at 3:22 pm
No Problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply