October 26, 2006 at 8:37 am
We have a database that is using three logical drives with one Primary filegroup. How can I tell what database objects are in each file?
October 26, 2006 at 2:31 pm
hard to track down; good question;
if a table is assigned to a filegroup, it stays within that file group.
if the filegroup is comprised of more than one physical file, when inserts occur, the data is round robin'ed into the multiple files, so no single file would contain a specific table.
so if you create multiple file groups, with a 1:1 relationship to the file it uses, only then can you know which file contains a table.
an undocumented proc that sp_help uses was the key: sp_objectfilegroup
select sysobjects.name as TableName,
s.groupname as Data_located_on_filegroup
from sysobjects, sysfilegroups s, sysindexes i
where
sysobjects.id = i.id
and i.indid < 2
and i.groupid = s.groupid
here's an indepth link about how it gets round robin'ed:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx
Lowell
October 26, 2006 at 3:34 pm
Lowell,
Thank you for your response. I found an undocmented DBCC command that showed me just what you're saying. I ran DBCC TAB for some of the objects and see that the objects are evenly distributed across all three drives. The application group would like additional filegroups created and to move some objects around to improve IO performance. Since we are on a RAID 5 array would that even make sense?
Thanks,
Michelle
October 26, 2006 at 3:53 pm
multiple file groups are always recommended; I'm pasting a script to help you out with a tiny slice of it: after you've created multiple file groups, the script below can help you move indexes to a specific file group to help with performance.
Hopefully others will pitch in, but my recommendation is one filegroup for the most heavily pounded tables as well as what tables they usually join against, one for less hammered tables,one for logs, one for temp, one for indexes? sound right?
hope this helps;
| typical output: |
| Create UNIQUE index [UQCNTTYPE] on [dbo].[CMTYPE] ( CONTACTTBLKEY, CONTACTTYPETBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO |
| Create UNIQUE index [UQ__PROJ_REQ] on [dbo].[COMPASSOCREQ] ( COMPPROJECTTBLKEY, COMPREQUIREMENTTBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO |
| Create UNIQUE index [UQ__COMPCOMMENTS] on [dbo].[COMPCOMMENTS] ( COMPPROJECTTBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO |
| Create UNIQUE index [UQ__COMPENVIRONRELATE] on [dbo].[COMPENVIRONRELATE] ( COMPPROJECTTBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO |
| Create UNIQUE index [UQ__COMPFAIR] on [dbo].[COMPFAIR] ( COMPPROJECTTBLKEY ) WITH DROP_EXISTING on [YourNewFilegroup] GO |
| ... |
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 'UNIQUE ' 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 2 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 not 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
go
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply