|
|
| |
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.
|
|
|
