SQL Server Query Optimization

SQL Server Query Optimization ,Internal Storage, Indexes


Internale Storage  - Data Pages


Create table tab1 in msdb

create table tab1
(id int,
 item varchar(20))

 insert into tab1 values(1,'AAA')
 insert into tab1 values(1,'BBB')

 select id,item from tab1
 select * from tab1

 DBCC IND ('msdb',tab1,-1);
 DBCC TRACEON(3604)
 DBCC PAGE('msdb',1,1510,1)


The Internal Page Structure is shown below


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:1510)


BUFFER:


BUF @0x07DA7290

bpage = 0x1228E000                  bhash = 0x00000000                  bpageno = (1:1510)
bdbid = 4                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 4547                        bstat = 0x10b
blog = 0x5adb21cc                   bnext = 0x00000000                 

PAGE HEADER:


Page @0x1228E000

m_pageId = (1:1510)                 m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 412   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594064928768                               
Metadata: PartitionId = 72057594055491584                                Metadata: IndexId = 0
Metadata: ObjectId = 1305771709     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 2                       m_freeCnt = 8028
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (97:256:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = 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, Length 32, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 32                   
Memory Dump @0x0F79A060

00000000:   70000800 01000000 02000001 00120041 41410000  p..............AAA..
00000014:   00000000 00002f15 00000000                    ....../.....  

Slot 1, Offset 0x80, Length 32, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 32                   
Memory Dump @0x0F79A080

00000000:   70000800 01000000 02000001 00120042 42420000  p..............BBB..
00000014:   00000000 00003215 00000000                    ......2.....  

OFFSET TABLE:

Row - Offset                       
1 (0x1) - 128 (0x80)               
0 (0x0) - 96 (0x60)                


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Comments

Post a Comment

Popular posts from this blog

Understanding CallBack function on Thread.

Understading Callback method wher a function is executed not on the main thread but on a child thread that it created .  Similary there are different ways of executing function created on the child thread. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; namespace Memory_understanding {     public delegate void sumofnumberscallback(int sumofnumber);     class A     {         int target;         public A(int num)         {             this.target= num;         }         public void printNumber(int n)         {              ...
Read more

POC on gridView with Button element as Column value

To develop a code for the below requirement: 1)Create a GridView to display Employee table (first_name ,Last_name, Id) 2)For each row in the grid ,place a button "Exclude"inside the grid 3)On click of  the button "Exclude" put include the Emplyee name in a excluded list. 4)Write a function which can exclude and include the name of the employees from the Grid. 5)On submit button click show the number of Employess included and excluded. ASPX Code <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Employee_Example.aspx.cs" Inherits="WebApplication1.Employee_Example" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <form id="form1" runat="server">     <div>      <asp:GridView ID="employeeGrid" runat="server" CellPadding...
Read more

Multicast Delegate

Multicast Delegate ///-------------------understading Delegate //mutlicastDelegate -----------------------///     class multicastdelegate     {         delegate string function1(string display);         static function1 fun = getMessage;         static string getMessage(string display)         {             return display + " this is Multicast delegate from getMessage";         }         static string getMessage2(string display)         {             return display + " this is Multicast delegate from getMessage2";         }         stati...
Read more