This is a continuation post from last weeks’ SQL Server Storage: Pages and Extents explanation. Since the description was a bit longer than I originally expected it to be, I decided to split the scripts into more posts.
First, I’m going to quickly cover how you can see information about a specific page using T-SQL. Now, normally this won’t be of much use to you, but it’s fun to play around with a bit just to see how things are actually stored. You might have need to read a page during some heavy troubleshooting at some point in the future too. This procedure is undocumented though, so information is scarce and the feature could disappear without notice in future versions.
To read a page, you’ll need to utilize
DBCC PAGE which I’ve listed the basic layout for below.
DBCC PAGE ( 'DbName' OR DbId -- Database name or Database ID, either one! ,FileNumber -- File Number of the Page ,PageNumber -- Page Number in the File ,PrintOption -- Display option ranging from 0-3 with different info from each );
Now we need to know what FileNumber and PageNumber to supply to
DBCC PAGE though. Random numbers might work, but if you are actually trying to do anything halfway useful, they won’t get you far. To solve this problem, we have to utilize another procedure…
DBCC IND ( 'DbName' OR DbId -- Database name or Database ID, either one! ,TableName -- Table Name...I don't think this really needs a comment ,IndexId -- index_id from sys.indexes; -1 = indexes and IAMs, -2 = IAMs );
DBCC IND we can get some relevant data to pass into
DBCC PAGE. The problem is, you still need a relevant
Index_ID for the selected table. The following query can help with that. Just supply the desired
TableName in the
--Get the index_id relating to your desired TableName to pass into DBCC IND SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = 'TableName'
index_id and a
TableName in mind, we can get some results from DBCC IND.
Awesome, now we have meaningful ids to use with
DBCC PAGE. You’ll need to do one more thing before you run it though.
Trace flag 3604 has to be set for SQL to provide output. Without it, you won’t get any results at all.
DBCC TRACEON(3604) DBCC PAGE('DbName',1,100,3) WITH TABLERESULTS; GO
With the Trace Flag on, you’ll get more data than you probably know what to do with.
Now you know how to get page information about your tables, so long as these undocumented procedures are available. Since this post again got longer than I originally expected, I’ll cover reading and setting block sizes on your disks next week. That will involve some fun PowerShell too!