October 3, 2002 at 11:56 am
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
October 3, 2002 at 12:37 pm
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
October 3, 2002 at 12:52 pm
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
October 3, 2002 at 1:12 pm
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
June 21, 2005 at 7:51 am
You can find a script that returns all this data for you at
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
-------------------------
Omri Bahat
SQL Farms Solutions
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply