June 29, 2009 at 10:33 pm
hi all,
I interact with maximum of DBA's all said "NO" for following
Just for the conformation..
Is it possible we can tranfer data in mdf of database D1 to newly created ndf or mdf file in D1????
Even if i add new filegroup how can we transfer data?
thanks,
Neerav
June 29, 2009 at 11:01 pm
your co workers are under informed, I think.
when you move the primary key/clustered index of a table to a new file group, you move it's data as well.
example:
CREATE CLUSTERED INDEX ix_tblname_name ON tblname( name )
WITH DROP_EXISTING
ON [FG_CRIT]
here's a modified script I've kept handy that moved non clustered indexes to a new file group...I've tweaked it to make it do the PK's instead.
Create view V_IXCOL
as
select SIK.* , C.name as ColName
from dbo.sysindexkeys SIK
inner join dbo.syscolumns C
on SIK.id = C.id
and SIK.colid = C.colid
go
Declare @NewFG varchar(128)
set @NewFG = 'YourNewFilegroup'
print '-- Move NCI Indexes to new FG'
print '-- keep column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create ' + case when (SIX.status & 2) 0 then 'CLUSTERED ' else '' end + 'index [' + SIX.name + '] on [' + U.name + '].[' + O.name + '] ( '+
IX1.ColName
+ case when IX2.ColName is null then '' else ', ' + IX2.ColName end
+ case when IX3.ColName is null then '' else ', ' + IX3.ColName end
+ case when IX4.ColName is null then '' else ', ' + IX4.ColName end
+ case when IX5.ColName is null then '' else ', ' + IX5.ColName end
+ case when IX6.ColName is null then '' else ', ' + IX6.ColName end
+ case when IX7.ColName is null then '' else ', ' + IX7.ColName end
+ case when IX8.ColName is null then '' else ', ' + IX8.ColName end
+ case when IX9.ColName is null then '' else ', ' + IX9.ColName end
+ case when IX10.ColName is null then '' else ', ' + IX10.ColName end
+ case when IX11.ColName is null then '' else ', ' + IX11.ColName end
+ case when IX12.ColName is null then '' else ', ' + IX12.ColName end
+ case when IX13.ColName is null then '' else ', ' + IX13.ColName end
+ case when IX14.ColName is null then '' else ', ' + IX14.ColName end
+ case when IX15.ColName is null then '' else ', ' + IX15.ColName end
+ case when IX16.ColName is null then '' else ', ' + IX16.ColName end
+ case when IX17.ColName is null then '' else ', ' + IX17.ColName end
+ case when IX18.ColName is null then '' else ', ' + IX18.ColName end
+ ' ) WITH DROP_EXISTING on [' + @NewFG + '] ' + char(10) + 'GO '
from V_IXCOL IX1
inner join dbo.sysobjects O
on IX1.id = O.id
and o.xtype = 'U'
inner join dbo.sysUsers U
on O.Uid = U.Uid
inner join dbo.sysindexes SIX -- no INFORMATION_SCHEMA available for this info
on IX1.id = SIX.id and IX1.indid = SIX.indid
and SIX.indid between 1 and 254 -- Select only NCI
and SIX.name not like '[_]WA[_]%'
left join V_IXCOL IX2
on IX1.id = IX2.id and IX1.keyno = 1 and IX2.keyno = 2
left join V_IXCOL IX3
on IX1.id = IX3.id and IX1.keyno = 1 and IX3.keyno = 3
left join V_IXCOL IX4
on IX1.id = IX4.id and IX1.keyno = 1 and IX4.keyno = 4
left join V_IXCOL IX5
on IX1.id = IX5.id and IX1.keyno = 1 and IX5.keyno = 5
left join V_IXCOL IX6
on IX1.id = IX6.id and IX1.keyno = 1 and IX6.keyno = 6
left join V_IXCOL IX7
on IX1.id = IX7.id and IX1.keyno = 1 and IX7.keyno = 7
left join V_IXCOL IX8
on IX1.id = IX8.id and IX1.keyno = 1 and IX8.keyno = 8
left join V_IXCOL IX9
on IX1.id = IX9.id and IX1.keyno = 1 and IX9.keyno = 9
left join V_IXCOL IX10
on IX1.id = IX10.id and IX1.keyno = 1 and IX10.keyno = 10
left join V_IXCOL IX11
on IX1.id = IX11.id and IX1.keyno = 1 and IX11.keyno = 11
left join V_IXCOL IX12
on IX1.id = IX12.id and IX1.keyno = 1 and IX12.keyno = 12
left join V_IXCOL IX13
on IX1.id = IX13.id and IX1.keyno = 1 and IX13.keyno = 13
left join V_IXCOL IX14
on IX1.id = IX14.id and IX1.keyno = 1 and IX14.keyno = 14
left join V_IXCOL IX15
on IX1.id = IX15.id and IX1.keyno = 1 and IX15.keyno = 15
left join V_IXCOL IX16
on IX1.id = IX16.id and IX1.keyno = 1 and IX16.keyno = 16
left join V_IXCOL IX17
on IX1.id = IX17.id and IX1.keyno = 1 and IX17.keyno = 17
left join V_IXCOL IX18
on IX1.id = IX18.id and IX1.keyno = 1 and IX18.keyno = 18
where IX1.keyno = 1
and exists (select *
from sysobjects PK
where xtype = 'PK'
and PK.parent_obj = O.id
and PK.name = SIX.name )
order by U.name , O.name, SIX.name
go
drop view V_IXCOL
Lowell
June 30, 2009 at 5:18 am
hi lowell,
At least someone is agree with me..Thanks Bro!!!!
We have OLTP system.can you send me some Performance tuning tips because at certain point our db slow down.
we dont have indexes how can we transfer data?
June 30, 2009 at 5:30 am
Neerav (6/30/2009)
hi lowell,At least someone is agree with me..Thanks Bro!!!!
We have OLTP system.can you send me some Performance tuning tips because at certain point our db slow down.
we dont have indexes how can we transfer data?
There are two parts to your question.
1. performance - I suggest you create server side traces and check on poor performing queries.
These two links should guide you to find out real culprits for slowness of ur db.
2. you want to move data to another filegroup? If you dont have clustered indexes on your tables, create one in the target filegroup, this will move the entire table to the secondary filegroup and then drop the clustered index.
EDIT - Fixed hyperlinks.
June 30, 2009 at 5:33 am
Neerav: Performance tuning is a massive subject entire books can be written on the subject (and in fact have)
Have a look for "SQL 2008 performance tuning Distilled" and "Inside SQL Server 2005: Query Tuning and Optimisation"
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
June 30, 2009 at 5:51 am
I second Gail's recommendations on those books. They're worth reading.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 30, 2009 at 10:14 pm
hi all,
Thanks for your support
i have successfully created cluster index on target filegroup.
I have one mdf,one ndf and one ldf.i also deleted cluster index as said by you.
How i am gonna test that my data has been transfered?
Thanks,
Neerav
July 1, 2009 at 12:59 am
You can query sys.partitions and sys.data_spaces to see what filegroups objects are on. I don't have the query handy, but it's an easy one to write.
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
July 1, 2009 at 2:45 pm
Try this query:
select object_name(si.object_id) As TableName,fg.name,si.type_desc
from sys.indexes si join sys.data_spaces fg
on si.data_space_id=fg.data_space_id
join sys.objects so on so.object_id=si.object_id
where so.type='U'
MJ
July 1, 2009 at 4:35 pm
Having recently done the same thing (and still in progress)
Here are some useful scripts:
Script 1 - determine LOBs per filegroup
select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.partition_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type = 2
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
Where filegroup_name(a.data_space_id) = 'Primary'
union all
select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.hobt_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type in (1, 3)
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
Where filegroup_name(a.data_space_id) = 'Primary' --Can query specific filegroup names or ommit this in the --where clause
and object_name(p.object_id) not like '%queue_%'
and object_name(p.object_id) not like 'sysd%'
and object_name(p.object_id) not like 'dt%'
Script 2 - FileGroup Sizes
CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
"Name" nvarchar( 128 ) , "FileName" nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
--Data File Details
INSERT INTO #FileDetails (
FileId , FileGroupId , TotalExtents , UsedExtents , "Name" , "Filename"
)
EXECUTE( 'dbcc showfilestats with tableresults' )
SELECT FILEGROUP_NAME( FileGroupID ) AS FileGroupName ,
FileId ,
"Name" ,
"FileName" ,
(CONVERT(decimal(38,2),TotalSize)) AS FileSizeMB ,
(CONVERT(decimal(38,2),UsedSize)) AS CurrentSizeMB ,
(CONVERT(decimal(38,2),((UsedExtents*1.)/TotalExtents)*100)) AS "%Usage"
FROM #FileDetails
Select sum(CONVERT(decimal(38,2),UsedSize)) as CurrentDBSize from #FileDetails
DROP TABLE #FileDetails
Script 3 - Tables per filegroup with Size of table
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
Some pieces of these scripts (97% of script 1 for instance) were found on the net. Script 2 is mine.
These should be helpful to you. You may also want to look into some scripts out there to help automate your table moves (drop constraints, move clustered indexes, recreate constraints, and also recreate any other indexes)
Nearing completion of my project, and I have seen an avg of 5 - 10% improvement in resource utilization.
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
July 1, 2009 at 4:37 pm
Forgot to add to the post that DB optimization is not found in the filegroup solution alone - but can be a piece of the puzzle. In short, I second Gails suggestion.
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
July 2, 2009 at 12:07 am
THANKS TO ALL FOR HELP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply