SQL Server Tour--fifth station did have to say the DBCC command

Original: SQL Server Tour--fifth station did have to say the DBCC command

Today's research and Development Center for the annual meeting, the evening is the respective department dinner, I personally like to drink dry red, in the dry red you can appreciate the kind of ups and downs ... Life is not so??? Just

CEO from the United States brought dry red back, and then I was happy to go through the addiction .... A word ... Cool.... Drink a bit more ... Now the head still hurts .... Back on the subway and think about it.

The weekend busy looking for a house, the book did not see, and did not go on the blog, think of the blog has a friend said the popularity of the DBCC, although a bit vaguely ... See if we can write something new.

Pattern.... No matter what language you learn, or the database, you have to look deeper than others ... The saddest thing is to stay on the surface, so that after each version upgrade, there are some tricks, and then you

I think it's too damn magical, but is that really the case? A very real example is the syntax sugar in each version of C # ..... When you ildasm, you'll find that it's all about the basics.

The basic thing, C # has a benefit, is that you can see the source code, you can also ildasm it ... Basically you can let it go naked to see you, but SQL Server is a bit special, it's not a programming language,

You will not learn through anti-compilation, it is only a deeper layer of things can only be the bottom of the data page. 。 So this is the basic skills of learning SQL Server, a little bit more ...

One: DBCC

1: What is DBCC

I am not a teaching teacher, and I cannot say that there is no impeccable definition, full name: Database Console Commands. As the name implies "Database Console command", speaking of "console",

My first reaction is Chrome's developer tool, and I don't know what your first reaction will be. In the developer tools, as long as JavaScript can recognize the syntax, you can type in the console ... With

The same thing that SQL Server can recognize.

How many orders did 2:DBCC have?

You should know that most of the consoles will provide a help command, such as a CMD interface, or a mongodbのconsole,

And see what help is in DBCC???

From the top to the bottom, I have 32 orders, but interestingly enough, the 32 commands that Microsoft provided are actually open to the public, and I mean there are still some open orders

Is Microsoft's own use .... I can make you see the truth.

From the above we can probably see that the public and the unpublished DBCC commands add up to about 100, but you have to ask me how these DBCC commands are used ... I can't tell you, after all, my brain.

Not so well-developed to remember these boring things, and also do not want to spend time dedicated to remember these things, like English remember words ... But there are ways, and most of them can't help it.

Rotten Dozen, to the girl also like this, either entangled in her like you, or entangled her to the police to arrest you.

3: How to remember DBCC commands

<1> Books Online

Books Online can be said to be the mother of SQL Server, in this world you will not find a more authoritative than it, but also all the information, you can click here to see its divinity, and then you can

Type DBCC to find what you need to look for, and also see that DBCC is probably divided into 4 categories, but unfortunately, the unpublished DBCC commands are not found on Books Online.

<2> help (' xxx ')

If you have a general understanding of how commands are used, but you forget how to assign arguments, you can use Help (' xxx ') to save your development time, such as buffer and IND commands.

Second: The practice of common commands

1:DBCC TRACEON

In Books Online, this is to enable the specified trace flag, since said the designation, this is the connotation of ... I used to study only two:

<1> TRACEON (2588)

As you know, you can see the non-public DBCC command by specifying the 2588 tag, and you can also see the hints for the various command parameters.

<2> TRACEON (3604)

The specified tag is the result of the DBCC page can be displayed on the client, otherwise it will not appear, do not know you in the previous chapters have noticed???

2:DBCC IND

This command is used very frequently in this series, because it is used to view the data page information of "Heap table" or "index", without it, I have no research, or the usual, first look at

Its parameter information, such as:

Top two parameters I want you to understand, I also said in the previous section, one is dbname, one is tablename or viewname or procname, etc., the third parameter is the most

Interesting, what is the meaning of 1,0,-1,-2 in this???

<1> 1: Displays clustered index data page information and IAM trace data page information.

<2> 0: Displays heap table data page information and IAM trace data page information.

<3>-1: Displays all data page information, such as (IAM, index data page, heap table data page).

<4>-2: Displays information for IAM data pages.

<5>nonclustered indid: From this parameter of the list, you can probably see is a "positive infinity" to 1,0,-1,-2 this mode, think about this meaning I think you also understand, such as

Say 2 represents the first nonclustered index, 3 represents the second clustered index, and so on .....

I really do not want to give an example, because the next thing to say, it is endless ... Well, let's give an example:

Through the above diagram, I think you should understand what I am doing??? You can see that the data for the current nonclustered index is distributed in (Pagefid:pagepid) (1:110), (1:115) .... Wait 4 numbers

According to the page, you can see (1:114) just their data tracking page. One might say, how do I see it as an IAM tracking page? You just have to look at Iamfid and iampid as null and you can think of it as

IAM tracking page, and when you see INDEXID>0, it's the index page.

3:DBCC PAGE

This command is also frequently involved in this series, because it is really useful, when I use the IND Export Data page, the next step is to look at the data page in the end of what information, people

are so greedy, temptation ah ~ ~ ~ The usual, first look at the parameters.

As you can see, the second and third parameters do not mean anything, because I have used IND to find out which data pages (Fileid:pageid) are indexed on, and below we carefully

Take a look at the fourth parameter.

<1> 0: Output data page header data in readable form, for this reason, in a data page, there are 96 bytes of space to represent a data page header, the content can be enriched ....

<2> 1: Output the data page header data in readable form, and also the hexadecimal contents of the slot corresponding to the record.

<2> 2: Output hex data for the entire data page header, including (header, content, and slot), which is my most commonly used command.

<3> 3: Output data page header data in readable form, and includes a readable form of each field in the record.

The above command looks a bit iffy, I'll just give one example, the others are left for everyone to try ~ ~ ~

DBCC TRACEON (3604)DBCC PAGE (Ctrip,1,2)

1 DBCCExecution is complete. IfDBCCThe error message is output, please contact your system administrator. 2 3PAGE: (1: the)4 5 6 BUFFER:7 8 9BUF@0x0000000085f8ed00Ten  OneBpage= 0x000000008519a000Bhash= 0x0000000000000000Bpageno=(1: the) ABdbid= 8Breferences= 0BUse1= 8576 -Bstat= 0x3c00009Blog= 0x32159Bnext= 0x0000000000000000 -  the PAGE HEADER: -  -  -Page@0x000000008519a000 +  -M_pageid=(1: the) m_headerversion= 1M_type= 2 +M_typeflagbits= 0x0M_level= 0M_flagbits= 0x204 AM_objid (Allocunitid.idobj)=  -M_indexid (Allocunitid.idind)=  the   atMetadata:allocunitid= 72057594041729024                                  -Metadata:partitionid= 72057594040877056Metadata:indexid= 2 -Metadata:objectid= 245575913M_prevpage=(0:0) M_nextpage=(1: the) -Pminlen= 909M_slotcnt= 8M_freecnt= 784 -M_freedata= 7392M_reservedcnt= 0M_lsn=(141:194: the) -M_xactreserved= 0M_xdesid=(0:0) m_ghostreccnt= 0 inM_tornbits= -788728362               -  to Allocation Status +  -GAM (1:2)=Allocated SGAM (1:3)=Allocated thePFS (1:1)= 0x60Mixed_ext Allocated 0_pct_full DIFF (1:6)=CHANGED *ML (1:7)=  notmin_logged $ Panax Notoginseng DATA: -  the  +MemoryDump @0x0000000010cea000 A  the0000000010cea000:01020000 04020001 0000000000008d03† .......... +0000000010CEA010:73000000 010008003a000000 1003e01c†s ......... -0000000010cea020:6e000000010000008d000000 c2000000†n ......... $0000000010cea030:aa00000000000000 00000000d6f5fcd0† .......... $0000000010cea040:00000000 00000000 00000000 00000000† ... .... ..... -  - ........... the  -0000000010CEBFE0:21212121 21212121 21212121 21212121†!!!!!!!!!!!!!!!!Wuyi0000000010cebff0:5019c015 3012a00e 100b8007 f0036000†p ...0.........`.  the  -OFFSETTABLE: Wu  -Row-Offset About 7(0x7)- 6480(0x1950)               $ 6(0x6)- 5568(0x15c0)               - 5(0x5)- 4656(0x1230)               - 4(0x4)- 3744(0xea0)                - 3(0x3)- 2832(0XB10)                A 2(0x2)- 1920x1080(0x780)                + 1(0x1)- 1008(0x3f0)                the 0(0x0)-  the(0x60)                   -  $  the DBCCExecution is complete. IfDBCCThe error message is output, please contact your system administrator.

~~~~~~~~~~~~~~~~~~~~~~ finally is the research and Development Center of the annual meeting of those beautiful scene dare not alone, give everyone welfare ~~~~~~~~~~~~~~~~~~~~~~

SQL Server Tour--fifth station did have to say the DBCC command