Determine whether index/table spans multiple files
- Hello!I was wondering if there is an easy way to determine whether index/table spans multiple file within the same filegroup.Thanks,Igor
-
With DBCC IND you can list the pages that belongs to an index.
DBCC TRACEON(3604)
DBCC IND(database_id, object_id, -1)
The last parameter can be 0, -1, -2 or an index id number. 0 lists page numbers for all IAM and data pages for the object, -1 lists all IAM, data and index page numbers and -2 lists all IAM page numbers. Any other number lists the page numbers for all IAM and index pages for that specific index, plus the data pages if that index is the clustered index (indid 1).
The column PageFID in the output tells you the file id for that page.
-
Chris,
Thanks for you advice, as always! I am trying to accomplish this task programmatically, that is retrieving all indexes from database and determining filegroup(s) index belongs to. Is it possible to save this output into SQL Server table for further analysis while looping through all indexes?
Thanks,
Igor
-
CREATE TABLE #ind (
PageFID INT
, PagePID INT
, IAMFID INT
, IAMPID INT
, ObjectID INT
, IndexID INT
, PageType INT
, IndexLevel INT
, NextPageFID INT
, NextPagePID INT
, PrevPageFID INT
, PrevPagePID INT
)
GO
INSERT INTO #ind
EXEC ('DBCC IND(dbid, objectid, 0)')
-
Can anybody help me..
I can not found DBCC IND in Books onlone.Where I can locate it?
Thanks in advance
-
DBCC IND is undocumented.
Btw, would this help?
SELECT
CAST(OBJECT_NAME(sysind.id) AS CHAR(20)) AS TableName
, CAST(sysind.name AS CHAR(30)) AS IdxName
, FILENAME
, CAST(sysfg.groupname AS CHAR(10)) AS GroupName
FROM
sysindexes sysind
INNER JOIN
sysfilegroups sysfg
ON
sysind.groupid = sysfg.groupid
INNER JOIN
sysfiles sfiles
ON
sysfg.groupid = sfiles.groupid
INNER JOIN
sysobjects sysobj
ON
sysind.id = sysobj.id
WHERE
sysobj.xtype <> 'S'
AND
sysind.name NOT LIKE '_WA%'
ORDER BY
sysind.TableName
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Doh, much better solution of course. Sometimes one should not know too much.
-
Your posting made me aware of DBCC IND. Found it now in Inside SQL Server. Very, very useful at times
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Kindly tell me where is DBCC IND in sql server
-
Copy Chris' script into Query Analyzer, replace dbid and objectid with valid values, add a
SELECT * FROM #ind
DROP TABLE #ind
There you go.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
binu john,
For more about the command, just search the internet for "DBCC IND". Here's one link I got from google:
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
Also, try this as an example:
CREATE TABLE #ind
(
PageFID INT
, PagePID INT
, IAMFID INT
, IAMPID INT
, ObjectID INT
, IndexID INT
, PageType INT
, IndexLevel INT
, NextPageFID INT
, NextPagePID INT
, PrevPageFID INT
, PrevPagePID INT
)
DECLARE @dbID int, @objID int, @sql varchar(500)
SET @dbID = db_id()
SET @objID = 1
SET @sql = 'DBCC IND( ' + CONVERT(varchar(15), @dbID) + ' , ' + CONVERT(varchar(15), @objID) + ', 0)'
INSERT INTO #ind EXEC ( @sql )
SELECT * FROM #ind
DROP TABLE #ind
-
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply