I am trying to understand how the data in sql server is stored and
also regarding fill factor and page splitting.
1) My first question what is the difference between Index pages and
Data pages.
and how are they different for clustered and non clustered indexes and
heap tables.
2) What is the relation between index and data pages.
2) BOL says that fill factor is used to define the amount of free
space on each page of index. I am confused here what does index pages
and data pages contain
for clustered/non clustered/heap tables.
3) Why does page splits occur and do they occur due to the lack of
space in index or data pages?
Thanks
skura 2 10353
"skura" <th********@com cast.net> wrote in message
news:cc******** *************** ***@posting.goo gle.com... I am trying to understand how the data in sql server is stored and also regarding fill factor and page splitting.
I recommend you get a copy of Inside SQL Server 2000. It contains a lot of
detailed information on the low-level implementation of data and index
pages. I've given some short answers below (which will certainly be
oversimplified, but hopefully accurate enough), but there's enough
information in Books Online to get a good understanding. 1) My first question what is the difference between Index pages and Data pages. and how are they different for clustered and non clustered indexes and heap tables.
Data pages have data only; clustered index pages have index information and
data (because the leaf level is made up of data pages); non-clustered index
pages have index information only.
2) What is the relation between index and data pages.
If the index is clustered, the leaf nodes are data pages. If the index is
non-clustered, but there is already another clustered index on the table,
the leaf nodes point to keys in the clustered index. If the table is a heap,
the index leaf nodes point to rows in data pages.
2) BOL says that fill factor is used to define the amount of free space on each page of index. I am confused here what does index pages and data pages contain for clustered/non clustered/heap tables.
As above, with a clustered index, the index includes data pages; with a heap
table, the indexes have only index pages.
3) Why does page splits occur and do they occur due to the lack of space in index or data pages?
If the table has a clustered index, and the data pages in the leaf node fill
up with data, a split occurs because SQL Server has to 'make room' for the
new data. The same applies to non-clustered indexes - as data is added,
sooner or later the index pages will get full, whatever other indexes may be
on the table.
Think of putting new books in the middle of a shelf which already has lots
of books, sorted in alphabetical order. If all the old books are side to
side with no gaps, you'll have to move some of them every time you add a new
book to the shelf, in order to keep them all in alphabetical order. But if
you leave a number of spaces between the books, then you'll usually be able
to add many more new books before you have to move any of the old ones.
Thanks
skura
I hope that helps.
Simon
Simon, That helps and thanks for the info. I will get the book and read it.
"Simon Hayes" <sq*@hayes.ch > wrote in message news:<3f******* ***@news.bluewi n.ch>... "skura" <th********@com cast.net> wrote in message news:cc******** *************** ***@posting.goo gle.com... I am trying to understand how the data in sql server is stored and also regarding fill factor and page splitting.
I recommend you get a copy of Inside SQL Server 2000. It contains a lot of detailed information on the low-level implementation of data and index pages. I've given some short answers below (which will certainly be oversimplified, but hopefully accurate enough), but there's enough information in Books Online to get a good understanding.
1) My first question what is the difference between Index pages and Data pages. and how are they different for clustered and non clustered indexes and heap tables.
Data pages have data only; clustered index pages have index information and data (because the leaf level is made up of data pages); non-clustered index pages have index information only.
2) What is the relation between index and data pages.
If the index is clustered, the leaf nodes are data pages. If the index is non-clustered, but there is already another clustered index on the table, the leaf nodes point to keys in the clustered index. If the table is a heap, the index leaf nodes point to rows in data pages.
2) BOL says that fill factor is used to define the amount of free space on each page of index. I am confused here what does index pages and data pages contain for clustered/non clustered/heap tables.
As above, with a clustered index, the index includes data pages; with a heap table, the indexes have only index pages.
3) Why does page splits occur and do they occur due to the lack of space in index or data pages?
If the table has a clustered index, and the data pages in the leaf node fill up with data, a split occurs because SQL Server has to 'make room' for the new data. The same applies to non-clustered indexes - as data is added, sooner or later the index pages will get full, whatever other indexes may be on the table.
Think of putting new books in the middle of a shelf which already has lots of books, sorted in alphabetical order. If all the old books are side to side with no gaps, you'll have to move some of them every time you add a new book to the shelf, in order to keep them all in alphabetical order. But if you leave a number of spaces between the books, then you'll usually be able to add many more new books before you have to move any of the old ones.
Thanks
skura
I hope that helps.
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Andrew Dalke |
last post by:
Is there an author index for the new version of the
Python cookbook? As a contributor I got my comp version
delivered today and my ego wanted some gratification.
I couldn't find my entries.
Andrew
dalke@dalkescientific.com
|
by: stef |
last post by:
Hi,
I have a range partitioned (one partition = one month) table. On this
table I have a local unique index and a local domain index (Oracle
Text) on a CLOB column.
I'm running Oracle 9.2.0.1.0
CREATE TABLE PAGES_4M (
LOADDATE NUMBER(8) NOT NULL,
|
by: Bill |
last post by:
In an effort to improve the speed of queries against my main table,
I'll be indexing a column whose data type is varchar(50).
Would I be better off (better performance) if I changed the column's
data type to some numeric type? I would have to update the column's
data to accomodate this, but I would do it if this offers a
performance gain.
-- Bill
|
by: Raziq Shekha |
last post by:
Hello all,
SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?
Thanks,
|
by: Andr? Queiroz |
last post by:
Hi,
I have a table with 10M records and col A has a index created on it.
The data on that table has the same value for col A on all 10M
records. After that I insert diferent values for that column but my
queries do not use the index I created for that column.
Is there any way I can force the usage of the index or to ommit a
value on the index creation, like 0 (zeroes) or spaces?
Thanks in advance,
André Queiroz
| |
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
|
by: Gustaf Liljegren |
last post by:
Hi,
I've been struggling with this for days now. Hope to get some help here,
but some knowlegde in XML is required. I'm trying to create a back-of-
book index in XML, following this DTD:
<!ELEMENT index (entry | group)*>
<!ELEMENT entry (text, pages)>
<!ELEMENT text (#PCDATA)>
<!ELEMENT pages (#PCDATA)>
|
by: Jeffrey W. Baker |
last post by:
Greetings,
I have a 23GB data table upon which I am building a primary key of three
columns. The data is mounted in a 137GB device and pg_xlog is mounted
on a separate 3.5GB device. I have configured 24 checkpoint segments,
which I expect gives me a worst-case usage in pg_xlog of 384MB.
Unfortunately, during the CREATE INDEX, pg_xlog becomes full!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |