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

Authentication and Authorization in Web API -Part1

Image
The Authentication technique used mainly by token based with OWIN, Step was to register a user , We created a appliction form to submit the user credentials to the WEB API Post Mehtod and saved in the database . We have built  a repository class which interacts with the database and gives the required result-set when requested for , We have also introduced a Business Layer which handles the business logics and acts as  model for the controller . The implementations of the Authentication and Authorization is done from this site . http://bitoftech.net/2014/06/01/token-based-authentication-asp-net-web-api-2-owin-asp-net-identity/ The Controller Class using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Net.Http; using System.Web.Http; using WebApiDemo.Models; namespace WebApiDemo.Controllers {     public class EmployeeController : ApiController     {   [Authorize]        ...
Read more

Can a derived class reference contain base class object.

1)Can a derived class reference contain base class object. using System; public class Base         {         public Base()         {         Console.WriteLine("I am from the Base Class Constructor");         }         public void  Invert()         {         Console.WriteLine("I am an invert function and I belong to Base");         }         } public class Derived:Base         {         public Derived()         {         Console.WriteLine("I am from the derived class constructor");   ...
Read more

Mvc and WebApi Videos

Persisting data between different elements in MVC with View Bag, ViewData,TempData,Session Sending data betweeb controller,View to another view  we use ViewData and ViewBag, Data sending from one ActionResult to another we use TempData, Data sending between view to controller we use query string,Hidden fields,    Session variables of ASP.NET can also be used to get the data through out all the entities https://www.youtube.com/watch?v=yq1N7zfZTBo
Read more