Questions tagged [data-pages]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
18
votes
1answer
13k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
16
votes
2answers
2k views

What happens when there is no available physical memory left for SQL Server?

While googling I found some conflicting information. Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server:...
14
votes
1answer
3k views

Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts ...
13
votes
3answers
896 views

512 Bytes are not being used from SQL Server's 8 KByte data page

I have create the following table: CREATE TABLE dbo.TestStructure ( id INT NOT NULL, filler1 CHAR(36) NOT NULL, filler2 CHAR(216) NOT NULL ); and then created a clustered index: CREATE ...
13
votes
1answer
1k views

SQL Server - How are data pages stored when using a clustered index

I recently heard that the data pages in a clustered index aren't stored contiguously. Is this true? Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I ...
13
votes
1answer
1k views

Slot Array and Total Page Size

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B This leaves (...
11
votes
2answers
3k views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
10
votes
1answer
1k views

What is the row overhead when using Page Compression?

I have created a table with 650 Numeric(19,4) columns. When I switch on Page Compression, by running ALTER TABLE fct.MyTable REBUILD WITH (DATA_COMPRESSION = PAGE); I get Msg 1975, Level 16, ...
10
votes
3answers
15k views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
6
votes
1answer
2k views

SQL Server: How to create a table that fills one 8 KB page?

Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.
5
votes
3answers
3k views

SQL Server: Clustered index, sorting and pagination

In my application, several times I have to show results that are paginated and sorted by some field. For example a simple user list sorted by last name. Because of that and because I also have logical ...
4
votes
2answers
1k views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
4
votes
1answer
117 views

What happens at the data page level when the record length changes

I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly. ...
4
votes
1answer
257 views

Logical read count varies

In this post the writer runs a query several times. I notice the logical reads vary a little across executions. There is a difference of about 2 pages in a total of a few thousand pages read. It ...
3
votes
1answer
308 views

Does the term “page” have the same meaning in DBMS and OS jargons?

I am learning the data format of DBMS and found we have a term: page. Pages are the fetched data in main memory from external storage (eg. a disk, SSD) and several pages would be "cached" firstly ...
3
votes
1answer
6k views

DB2: Is it good to create the index table space to use 32 KB data page

I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.
3
votes
1answer
474 views

Why only 8kb per page?

I just want to know how they came to 8kb when striking a balance between: smaller page = more page splits bigger page = longer to look through to find value I expect there are many more pros and cons ...
3
votes
1answer
214 views

What is an effective way to wipe sensitive data on a data pages after dropping a table's column?

After dropping a column with sensitive data from a SQL Server table, the data still exists on the data and index pages. To ensure the protection of this data, it needs to be removed from the pages as ...
3
votes
1answer
443 views

How can you have more used_pages than total_pages when looking at sys.allocation_units?

When looking at a database, this query: SELECT * FROM sys.allocation_units a WHERE (a.total_pages - a.used_pages) < 0 surprisingly returns a row. This is the result: +--------------------+------+...
3
votes
1answer
72 views

In SQL Server would misc pages be pushed out when an object grows within a mixed extent?

In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than ...
2
votes
1answer
93 views

If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?

If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?
2
votes
1answer
9k views

How do I fix this corrupted page?

My database is in simple recovery mode. One page got corrupted and I only have backups created using simple backup. When I run this: DBCC PAGE ('myDB', 1, 952663, 3) I get: DBCC PAGE error: ...
2
votes
1answer
87 views

Big Amount of <free_space_in_bytes> in Buffer Pool's Database Pages

We were viewing sys.dm_os_buffer_descriptors using below query select d.[name] [Database_Name], (count(file_id) * 8) / 1024 [...
2
votes
1answer
239 views

Page Number in SQL Server database is not starting from zero

I have a question on SQL Server page, I have created a new database with only one new table as below Here is the Code: CREATE TABLE PAGETEST ( ID int primary key ) When i see my staring page ...
2
votes
1answer
102 views

Find the date and time that a data page was modified

Is there a method (documented or undocumented) to determine the last time a data page was changed in a SQL Server 2012 database data file.
2
votes
2answers
55 views

How does a Page in SQL Server distinguishes between the data of different columns of a table?

I know that row offset array at the end of page stores information about the starting index of any row. Does it also store information about column data? And where does it store? If not, how does it ...
2
votes
1answer
56 views

Is it possible to get a “pages touched” statistic from PostgreSQL?

With PostgreSQL one can measure disk reads and cache hits. Is it possible to have a clearer statistic of how many index pages were "touched" to run a query? If so, how?
2
votes
1answer
38 views

Retrieving page contents from SQL, Postgresql?

Data is stored over pages and not necessarily one row or one column or one attribute maps to one page. Is it possible to use some query to extract data from a specific page in a database environment?
2
votes
1answer
961 views

DB2: Is it good to create the LONG table space to use 32 KB data page

Our DB2 database (V9.7) has only defined one table space to store all the big objects like XML. And xml size could be different. Some are about 2kb average. And some are 7kb average. So does it make ...
2
votes
0answers
63 views

How is page data rearranged after clustered index is created?

Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?
1
vote
2answers
375 views

What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log. The definition itself is simple: InnoDB tries to flush data from the buffer pool so that the percentage ...
1
vote
2answers
61 views

What is the difference between PAGEIOLATCH_* and IO_COMPLETION?

according to this blog: SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O ...
1
vote
1answer
857 views

How to find percentage data change after last full backup?

I am trying to find the data change rate for all databases on a server. In the past, I used Paul Randal's script How much of the database has changed since the last full backup which worked great. ...
1
vote
1answer
48 views

Pages Input per Second Spiking Daily

I'm still learning a lot about SQL Server and pretty green, but doing the best I can as an accidental DBA. We have recently purchased a SQL monitoring software package, and after adjusting the ...
1
vote
1answer
64 views

MSSQL Server Pages

Looking online I have found a lot of information describing how pages work, what they are used for, and how they tie into the architecture of MSSQL Server. I understand they are a part of the mdf file ...
1
vote
1answer
438 views

Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2. I was wondering if my PK ...
1
vote
0answers
188 views

Is the idea of 'pages' and 'extents' unique to SQL Server or shared by other database management systems?

I know that a database management system like MySQL can make use of multiple storage engines - i.e. InnoDB, MyISAM, etc. And reading into how SQL Server stores data (Understanding Pages and Extents), ...
1
vote
0answers
429 views

Extreme high index fragmentation in MS SQL Server

I have a problem with CLUSTERED INDEX, every one of my table have extemely high total fragmentation, around 92% with having page fullness in the same time at around 99%. So in my understanding pages ...
1
vote
0answers
175 views

Page Header DB Frag ID Property

I am using SQL Server version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64). I created a single table containing one and only one integer-valued column. I then added one and only one row to the ...
0
votes
2answers
549 views

What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL? Database pages are smallest read write unit ...
0
votes
1answer
689 views

what is an InnoDB Page checksum?

What is an InnoDB Page checksum exactly? Does a Page checksum only detect issues with underlying storage when writing or reading to a page/block?
0
votes
2answers
793 views

How to understand Composite Primary Key when it comes to storage (in Mysql)?

I can image how rows will be arranged in a single database page as primary key order (example an auto-increment key) after reading this post. But I wonder how it will in the case of composite primary ...
0
votes
1answer
26 views

Can an InnoDB page contain data from different indexes?

(In this question, I assume that the data of a table is acťually an index (PRIMARY), so I don't differentiate between table data and column indexes) I've compacted an "old" InnoDB table (via null ...
0
votes
2answers
743 views

How Can I Determine Object Distribution in a Multiple-File Filegroup?

I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the ...
0
votes
1answer
48 views

Need some insights on 'index_page' variables in Innodb_Metrics Table

I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb. For sequencial Inserts I got: index_page_splits: 3199 index_page_merge_attempts: 0 ...
0
votes
2answers
108 views

How does a integer data record store in SQL Server page

I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused. In Below table i have create ...
0
votes
1answer
49 views

Least recently used replacement strategy - where is pin count stored?

I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies. The two popular replacement startegies mentioned are least recently used - LRU and ...
0
votes
1answer
72 views

How does DYNAMIC Row format handle NULL storage MYSQL 5.7?

As a newbie to Mysql I just can't understand how NULL values are stored in each row formats. As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. ...
-1
votes
1answer
45 views

Is the index created as soon as data is inserted into a table? [closed]

For example I have a table and I created an index on ID column which generates numbers every time I insert a row. If I only insert 5 rows does index tree start to be created or is there a threshold of ...