Search

Advertisements

Showing posts with label SQL Server internals. Show all posts
Showing posts with label SQL Server internals. Show all posts

SQL Server - DBCC PAGE and DBCC IND



DBCC IND – used to know the page details of tables & indexes.

syntax:
DBCC IND ( {dbname}, {table_name},{index_id} )
Description of columns displayed from DBCC IND

PageFID
File ID of Page

PagePID
Page ID

IAMFID,IAMPID
File ID&Page ID -> IAM mapping


ObjectID
 object ID(Table)

IndexID
Clustered index (IndexID = 1),Non clustered index (IndexID = 2)

PartitionNumber
 Number of Partition which holds data & index pages

PartitionID
ID of Partition which holds data & index pages

iam_chain_type
In-row data,Row-Overflow data

PageType
1=Data page,2=Index page,3&4=Text page,10=IAM

IndexLevel
0=leaf level

NextPageFID,NextPagePID,PrevPageFID,PrevPagePID
Next & Previous Page ID 's & File ID 's of a page




 

DBCC PAGE – used to see the contents of  a Page.

syntax:
DBCC  PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )



DBCC PAGE & DBCC IND both are undocumented commands.

Few examples for DBCC PAGE & DBCC IND:

SQL Server and Numbers



Few informations about maximum limits & sizes in SQL Server.

Though practically we might not be crossing these maximum limits & sizes in SQL Server in most cases ,it is always better to be aware of it.



page size  (128 pages/MB)
8 KB
Extents (16 extents/MB)
64 KB


Databases per instance of SQL Server
32,767
Files per database
32,767
User connections
32,767
Common table expression MAX recurrsion count
32,767
DB_ID of resource database
32,767


Columns per primary key
16
Columns per foreign key
16


Columns per SELECT statement
4,096
Columns per INSERT statement
4,096
Columns per UPDATE statement
4,096


Nested stored procedure levels
32
Nested subqueries
32
Nested trigger levels
32
Parameters per user-defined function
2100
Parameters per stored procedure
2100


Clustered indexes per table
1
Non Clustered indexes per table
In SQL Server 2005 = 249,In SQL Server 2008 = 999
Max number of included columns in covering index
1023
 


For more information , refer below link :

Advertisements