Figure 1 shows the output that I get back from this command. Of course, your actual page numbers will likely be different when you run this command.
Using DBCC PGLINKAGE is a quick way to get the list of all pages belonging to a table, in sorted order. However, the page numbers all come back as messages, so you can't easily save them and examine them using SQL queries. For this reason, I prefer to use DBCC IND to get all the page numbers for a table. One benefit of DBCC IND is that you don't need to know any page numbers before you use this command; all you need to know is the table name. Also, with just one command, DBCC IND can tell you all the pages that belong to the table for all indexes and returns the results in a tabular format that you can save in a table for analysis and reporting. DBCC IND also tells you all the pages for the table, for IAMs, and for pages containing text or image data.
DBCC IND has three parameters. The first parameter is the database name or the database ID. The second parameter is the object name or object ID within the database. The third parameter is a specific index ID or one of the values 0, -1, or −2, which Table 1 explains. Here's the syntax:
DBCC IND ({'dbname' | dbid }, {
'objname' | objid }, { indid | 0 | -1 | -2 })
To get all the pages for all the indexes for the Order Details table, I'd execute the following command:
DBCC IND ('Northwind', 'Order Details', -1)
Note that, unlike with the DBCC PAGE command, I don't need to enable trace flag 3604 before running DBCC IND. Table 2 lists this command's output columns and the meaning of each.
Although we usually talk about the leaf level of any index as being level 0 and the leaf level of a clustered index as being the data pages, in the DBCC IND output, the level above the data in a clustered index is considered to be level 0. To find the first page in any index level, you can look for the page with a PrevPagePID and a PrevPageFID of 0. However, if you have lots of rows in the output, it can be hard to scroll the output to find the row representing that first page. If you could capture this output into a table, you could then use T-SQL queries to find the rows you're interested in. Fortunately, you can capture the output of any command that gives results in a tabular form.
The first step in this case is to run the code that Listing 1 shows to create a table that will hold the tabular results of the DBCC command. Because the table name starts with sp_, if you create this table in the master database, you'll be able to access it from any database without having to qualify the table name with the database name.
To populate this table with index information from the Order Details table, run the following INSERT statement:
TRUNCATE TABLE sp_index_info
INSERT INTO sp_index_info
EXEC ('DBCC IND
('Northwind', 'Order
Details', -1) ')
Since the table can be populated from any database, you might want to truncate the table before you insert more data into it, so the TRUNCATE TABLE statement is optional. To find the row for the first data page, you need to find a row that has a page type of 1 and no previous page. You can use the following SELECT statement:
SELECT * FROM sp_index_info
WHERE pagetype = 1 AND
prevpagePID = 0 AND prevpageFID = 0
After you have the page numbers for a table, you can use the DBCC PAGE command to examine the data rows. And, now that you have the data in a table, you can organize it in many different ways and ask questions about the indexes on this table. For example, if you want to know how many pages of each type you have, you can run the query that Listing 2 shows.
If you want to know how many pages are in the DBCC IND output for each index, you can run the code that Listing 3 shows. This query joins the new table to the sysindexes table in Northwind to get the name of each index. You can probably come up with many more questions about the indexes on a table, so I'll leave it up to you to come up with the queries to extract that information.
In future columns, I'll tell you about more interesting information you can find in the sysindexes table. I'll also start examining some of the index information that will be available in SQL Server 2005.
End of Article
Prev. page
1
[2]
next page -->
