Database Research Center of the Harbin University of Technology ZHAOKAI@hit.edu.cn How to display the query plan in SQL Server 2000. Use SQL Query Analyzer displays the execution plan SHOWPLAN_TEXT and SHOWPLAN_ALL The two SET options SHOWPLAN_TEXT and SHOWPLAN_ALL let you see the estimated query plan without actually executing the query in text mode. Both options also automatically enable the SET NOEXEC option, so you do not see any results from your query-you see only the way that SQL Server has determined is the best method for processing the query. Turning NOEXEC ON can be a good thing while tuning a query. for example, if you have a query that takes 20 minutes to execute, you might try to create an index that will allow it to run faster. However, immediately after creating a new index, you might just want to know whether the query optimizer will even choose to use that index. If you were actually executing the query every time you looked at its plan, it would take you 20 minutes for every tuning attempt. Setting NOEXEC ON along with the show plan option will allow you to see the plan without actually executing all the statements. WARNING Since turning on SHOWPLAN_TEXT or SHOWPLAN_ALL implies that NOEXEC is also on, you must set the SHOWPLAN option to OFF before you do anything else. For example, you must set SHOWPLAN_TEXT to OFF before setting SHOWPLAN_ALL to ON. Use SQL Query Analyzer to graphically display execution Plan How to Use DBCC Page Commands to view data page format: Use test go select * from Stu Go Results: First look at the content of the sysindexes relationship. Each index in the database and tables each occupied in the table. This table is stored in each database. Column Data Type Description ID INT Table ID (if IndID = 0 or 255). Otherwise, the ID of the table is indexed. STATUS INT internal system status information. First binary (6) points to the first page or the pointer of the root page.
Indid Smallint Index ID: 0 = This table is a new table, the corresponding table is a heap 1 = aggregate index> 1 = non-aggregation 255 = Table entries with text or image data Root binary (6) if Indid> = 1 and < 255, root is a pointer to the root page. If Indid = 0 or Indid = 255, root is a pointer to the last page. MINLEN Smallint minimum line size. The number of Keycnt Smallint keys. GroupID Smallint creates the file group ID of the object. DPAGES INT If Indid = 0 or Indid = 1, DPAGES is a count of used data pages. If IndID = 255, it is set to 0. Otherwise it is a count of the index page. Reserved Int If Indid = 0 or Indid = 1, reserved is a page count assigned to all indexes and table data. If Indid = 255, reserved is a page count assigned to Text or Image data. Otherwise it is the page count assigned to the index. Used int If Indid = 0 or Indid = 1, the subs is the total number of pages for all indexes and table data. If Indid = 255, the used is a page count for Text or Image data. Otherwise, it is the page count for indexing. Rowcnt Bigint counts based on indeid = 0 and Indid = 1. If Indid = 255, the RowCnt is set to 0. RowModCtr Int counts the total number of rows, delete, or update lines from the last updated table. XMaxlen Smallint maximum line size. Maxirow Smallint maximum non-leaf index line size. OrigfillFactor Tinyint Creates the starting fill factor value used by the index. This value is not retained; however, if you need to recreate the index but can't remember the original use of the fill factor, this value may be helpful. Reserved1 tinyint reserved. Reserved2 Int reserves. Firstiam binary (6) is retained. Impid Smallint is reserved. Index implementation logo. LockFlags Smallint is used to constrain the indecoded index lock size. For example, for a read-only lookup table, it can be set to only a table-level lock to minimize the lock cost. PGModCtr Int is reserved. Keys Varbinary (816) Compositions the list ID list of the index key. Name sysname table name (if IndID = 0 or 255). Otherwise, the name of the index. Statblob Image Statistics Blob. Maxlen Int is reserved. ROWS INT is based on the number of data-level rows of Indid = 0 and Indid = 1, which is repeated for Indid> 1. If IndID = 255, ROWS is set to 0. This column is provided is backward compatible.
SELECT ID, FIRST, Indid from sysindexes where id = Object_id ('stu') And Indid IN (0, 1) DBCC format is: DBCC Page ({DBID | DBNAME}, filenum, pagenum [, printopt]) To get filenum , filenum, execute the following statement: select id, first, indid from sysindexes where id = object_id ( 'stu') and indid in (0,1) id first indid 453576654 0x190000000100 0 then run DBCC dbcc traceon (3604) go dbcc page ('Test', 1, 25, 1) Go DBCC is executed. If DBCC outputs an error message, contact your system administrator.
Page: (1:25) ------------ Buffer: ------- BUF @ 0x18ed5a00 --------------- bhash = 0x19620000 bhash = 0x00000000 BPAGENO = (1:25) bdbid = 7 BREFERENCES = 24 bstat = 0x9 bspin = 0 bnext = 0x00000000 Page Header: ------------ Page @ 0x19620000 ------------ -------- m_pageId = (1:25) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8008 m_objId = 453576654 m_indexId = 0 m_prevPage = (0: 0) m_nextPage = (0: 0) pminlen = 8 m_slotCnt = 8 m_freeCnt = 7938 m_freeData = 1316 m_reservedCnt = 0 m_lsn = (6: 166: 2) m_xactReserved = 0 m_xdesId = (0: 0) m_ghostRecCnt = 0 m_tornBits = 2 Allocation Status -------- --------- Gam (1: 2) = allocated SGAM (1: 3) = not allocated PFS (1: 1) = 0x61 Mixed_ext allocated 50_pct_full diff (1: 6) = Changed ML (1: 7 ) = Not min_logged data: ----- Slot 0, Offset 0x60 ------------------ Record type = primary_record record attributes = null_bitmap variable_columns 19620060: 01000002 7A001300 0 ............ z 19620070: 6F6168 Hao Slot 1, Offset 0x73 ------------------ Record Type = primary_record record attributes = null_bitmap variable_columns 19620073 01000002 6B001200 0 ........... .K 19620083: 6961 ai slot 2, offset 0x85 ------------------ Record type = primary_record record attributes = null_bitmap variable_columns 1962002d 01000002 77001300 0 ...- ........ w 19620095: 676E61 Ang slot 3, offset 0x98 ------------------- Record type = primary_record record attributes =
Null_bitmap variable_columns 19620098: 00080030 000000F 01000002 66001300 0 ........... F 196200A8: 676E65 ENG SLOT 4, OFFSET 0XAB ----------------- - Record type = primary_record record attributes = null_bitmap variable_columns 196200ab: 00080030 000004c 01000002 78001100 0 ... l ........ x 196200bb: 78 x slot 5, offset 0xbc -------- ----------- Record Type = primary_record record attributes = null_bitmap variable_columns 196200bc: 00080030 0000005A 01000002 79001100 0 ... z ........ Y 196200CC: 79 y slot 6, offset 0xcd ------------------- Record type = primary_record record attributes = null_bitmap 196200cd: 00080010 00000c8 020002 ........... Slot 7, Offset 0x50E - ------------------- Record type = primary_record record attributes = null_bitmap variable_columns 1962050e: 00080030 0004d2 01000002 7A001600 0 ............ z 1962051e: 6B6F6168 6961 Haokai Offset Table: ------------- ROW - Offset 7 (0x7) - 1294 (0x50E) - 205 (0xCD) 5 (0x5) - 188 (0xBC 4 (0x4) - 171 (0xAb) 3 (0x3 ) - 152 (0x98) 2 (0x2) - 133 (0x85) 1 (0x7) - 115 (0x73) 0 (0x0) - 96 (0x60) DBCC is executed. If DBCC outputs an error message, contact your system administrator. Analysis: Data Row Format 19620060: 00080030 0000000C 01000002 7A001300 0 ............ z 19620070: 6F6168 hao id = 12 Name = zhao 30 00 0800 0C000000 0200 00 0100 1300 7A68616F 30 00 0800 0C000000 0200 00 0100 1300 7A68616F Status Bit Not Using Location The 8th byte of the first column begins the value of the first column of c = 12 The total number of columns is 2 NULL flags can be a non-air changing number of columns 1 column of the first gear end at 0x13 = 19 End First Various column zhao Object_id Returns the database object identification number. Syntax Object_ID ('Object') Parameter 'Object' The object to use. Object's data type is char or Nchar. If the data type of Object is char, it is hidden to convert it to nchar. Return Type INT DBCC ExtentInfo takes a list of all panels belonging to an object using DBCC Extentinfo.
Its syntax is: DBCC EXTENTINFO (DBNAME, TABLENAME, INDEXID) SQL will connect the data pages of the aggregated index to the linked list, but do not need it for the stack. For example, as well as its data lines (selected): 19918060: 00080030 0A0A0001 01000004 4E002B00 0 ............ . N 19918070: 4820765 20657269 6F4A202D 6F6E2062 EW Hire - Job no 19918080: 70732074 66696365 646569 t specified job_id = 1 job_desc = New Hire - Job not specified min_lvl = 10 max_lvl = position Small 10 00080030 0a0a0001 01000004 4e002b00 48207765 20657269 6f4a202d 6f6e2062 70732074 66696365 646569 30 00 0800 0100 0a columns starting from the first column of data int int third Tiny Column Data 0A 0400 00 01 002B Tiny INT The fourth column number of data columns is 4 units of vacanile long column number 1 The first changed length column ends remaining a string replicate to repeat character expressions . The syntax replicate (character_expression, integer_expression) parameter character_expression is a letter digital expression consisting of character data. The Character_Expression can be a constant or variable, or a character column or binary data column. Integer_expression is a positive integer. Returns an empty string if integer_expression is negative. Return Type VARCHAR Character_EXPRESSION must be a validity to convert to VARCHAR data types. Otherwise, use the CAST function to explicitly convert the character_expression. For Example: Replicate (CAST (1 as varchar (1)), 250) or replicate ('1', 250) and the isy it is Same. If it is Cast (1 as char), the type of type 1 is transformed The space is 32 bytes if Cast (1 as varchar (1)), then the space occupied by 1 byte. Primary Key Unique is the same in the processing of Primary Key and UNIQUE in SQL. The aggregation index will be automatically established on the columns constrained by Primary Key and Unique. The query optimizer makes decisions based on facts located in the index rather than a fact that is declared as the principal code. For example, after establishing an IT table, the system automatically establishes an aggregated index, which simultaneously shows the relationship between SysObjects, Syscolumns, and SysIndexes. After two lines to be inserted, the results are as follows: When we insert Insert IT Values (3, NULL) analysis file format 1990408D: 00080010 00000003 020002 ......... ID = 3 name = [null] 10 00 0800 0300000000 0200 02 10 00 0800 03000000 0200 02 The first column data is 3 column data at the eighth byte. There are two columns null = 10 second columns are empty, and the establishment index is, if not, the name is Clustered or NonClustered, The system defaults to non-cluster set, ie Indid> 1 Syslogins, which preserves all login users' information sp_dboption display or change the database option.
