Tables and Indexes in DataFiles

Question

Post reply

Tables and Indexes in DataFiles

Jeff W

SSCertifiable

Points: 5207
More actions
October 3, 2002 at 11:56 am

#58376

I wasn't sure if I should post this in Performance and Tuning or here. My end result will be for performance and tuning, but the question is general enough..

I need to find in which datafiles the table and index data are stored. I know there is a pointer in sysindexes, but am not sure on how to interpret it.

For example, a single file group has five files and I want to know in which datafile(s) the pages of the 'Employee' table are located.

How does one find this info 🙂

Thanks

"Keep Your Stick On the Ice" ..Red Green

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20891 More actions · Answer 1

Here is a query that should get you started:

select i.id,o.name,i.name, filename from sysobjects o

join sysindexes i on o.id = i.id

join sysfilegroups g on i.groupid = g.groupid

join sysfiles f on f.groupid = g.groupid

where xtype ='U'

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20891 More actions · Answer 2

Maybe I should have read you post a little closer. I believe you can only identify the filegroup in which the table belongs, and not the individual files within the filegroup. I'm assuming that when you place a table on a filegroup that contains multiple files then most likely your table will be spread across all the files in the filegroup, expecially if your table is large. I'm assuming a small table might not be spread across all tables.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Jeff W SSCertifiable Points: 5207 More actions · Answer 3

Your second post has a lot to do with where I am at. I have 8 10GB datafiles in one filegroup. The first 2 datafiles are at about 95% capacity and the capacity levels taper down to about 10% on the last datafile. I always thought SQL Server spread the data across the files in a group somewhat equally. It does not appear to be doing this on my SQL Server 7.0 SP3 (SAP) database.

I want to find where the FirstIAM is pointing too(what file?), and would be really happy to be able to track the chain of IAM's to discover what datafile the pages are located in.

Thanks for your input 🙂

Jeff

"Keep Your Stick On the Ice" ..Red Green