MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

Optimization tips for MS SQL 6.5: storage Nullable fields

Alexander Chigrik
chigrik@mssqlcity.com


Introduction

In this article, I want to show why it is important to know the data pages and the data rows structure and how you can use this knowledge for optimization purpose in SQL Server 6.5.

You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for the particular number):

DBCC tab (dbname, objname, printopt={ 0 | 1 | 2 })

where dbname - is the database name,
      objname - is the table name,
      printopt - is the type of the output:

      0 - minimum information (only the pages headers, the total
          number of data pages in this table and the total number
          of data rows in this table)

      1 - more information (plus full rows structure)

      2 - as printopt = 1, but without rows separation (full dump)

by default printopt = 0

Optimization tips

1. If you create default constraint for some fields, you should define these fields as NOT NULL.

2. Place all nullable fields to the end of the field's list (after fields with fixed length), so than more often this field will contain NULL value, the closer to the end of the record it should be placed.

Because one byte is placed into the Offset table for each nullable field, (to the exclusion of the field that situated at the end of the row and contained the Null value, or if only fields that contains the Null values are situated after this nullable field) you can save storage space if you will use these tips. So, you can save from several percents up to two times (look at the examples below).

Examples

1. Incorrect example:

USE pubs
GO
if object_id('dbo.tbTest') is not null drop table tbTest
GO
CREATE TABLE tbTest (
  id int identity primary key,
  field1 char(255) not null,
  field2 char(255) not null,
  field3 char(255) not null,
  field4 char(226) not null,
  field5 char(2) null,
  field6 char(1) null default ('1')
)
GO
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('1', '1', '1', '1')
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('2', '2', '2', '2')
GO
DBCC TRACEON (3604)
GO
DBCC TAB(pubs, tbTest)
GO
This is the result from my computer:

...

DATABASE:5   OBJECT:117575457   PAGE:425 (0x1a9)

Page header for page 0x12fe000
pageno=425 nextpg=0 prevpg=424 objid=117575457 timestamp=0001
00004979
nextrno=1 level=0 indid=0  freeoff=1039 minlen=997
page status bits: 0x100,0x1
The total number of data pages in this table is 2.
Table has 2 data rows.
You can see, that in this example two pages were created to store two data rows. This is because one byte was placed into the Offset table for field5 column at the end of each data row.

2. Correct example:

USE pubs
GO
if object_id('dbo.tbTest') is not null drop table tbTest
GO
CREATE TABLE tbTest (
  id int identity primary key,
  field1 char(255) not null,
  field2 char(255) not null,
  field3 char(255) not null,
  field4 char(226) not null,
  field6 char(1) null default ('1'),
  field5 char(2) null
)
GO
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('1', '1', '1', '1')
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('2', '2', '2', '2')
GO
DBCC TRACEON (3604)
GO
DBCC TAB(pubs, tbTest)
GO
This is the result from my computer:

...

DATABASE:5   OBJECT:181575685   PAGE:424 (0x1a8)

Page header for page 0x1302000
pageno=424 nextpg=0 prevpg=0 objid=181575685 timestamp=0001 000049e6
nextrno=2 level=0 indid=0  freeoff=2044 minlen=997
page status bits: 0x100,0x1
The total number of data pages in this table is 1.
Table has 2 data rows.
You can see, that in this example only one page was created to store two data rows. This is because one byte was not placed into the Offset table for field5 column at the end of each data row.

By the way, if you will create field6 as

field6 char(1) not null default ('1')

then you will save one byte in the Offset table too.

SQL Server 6.5 reads data by entire pages. So, for this query:

SELECT * FROM pubs..tbTest

SQL Server 6.5 will read one page only, and your query will be two times faster than in the first example.

So, the proper fields order in the table definition is very important thing for storage and query optimization.


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc