July 23, 2009 at 9:07 am
Hello!
In the case where a table's pages are located in two seperate physical locations within a file group, I would like to report how many pages for the table are being stored on location a and how many pages for that same table are being stored on location b.
I would like to do that for all objects, but I'm trying to find out how to even achieve this goal at it's most basic level. I've been searching through the system tables to find the information I need, but so far i've had no luck.
any ideas?
Thanks,
Kimberly
July 23, 2009 at 9:16 am
This isn't stored in the system tables, AFAIK. There are likely ways to read the IAM, SGAM, pages and relate this back to an object, but I'm not sure. The pages are organized into extents, which are contiguous pages on disk. If you run showcontig, or use the DMVs for fragmentation, then you can see how many extent switches there are, which means that you have consecutive pages in different locations. That probably matters more than knowing which pages are in which location. For example, if I have two extents:
Extent 1
Page 1 Page 2 Page 3
1 5 9
2 7 11
Extent 2
Page 1 Page 2 Page 3
3 10 4
6 8
I have page numbers listed in scan order. Even though I have 2 "locations" I have 7 switches to read these, which could be a performance issue.
Reporting on page, IMHO, doesn't make sense unless you have fragmentation under control.
July 23, 2009 at 9:22 am
Thank you for the information 🙂
This project started as a report to see how much space a database was consuming on specific storage locations. Then a request came in to see the space consumption at the object level. That's why I need to see how the object is being split between the locations.
July 23, 2009 at 9:54 am
Are you talking filegroups?
The objects are all in one location, in the filegroup on disk. Not sure it matters other than trivia. AFAIK, an object cannot cross filegroups, so an object is located in a filegroup.
July 23, 2009 at 10:27 am
Right, the filegroup contains logical names, and those locigal names can be split amoung multiple physical locations.
That's being said, an object, such as a table can be in that one filegroup, however, the object pages can be stored in seperate physcial locations (still within the same file group).
I just dont know if it's possible to track. I read information on IAM and SGAM as you suggested. So far I see it mapped to the database, but not the physical file location.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply