Questions tagged [data-pages]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
60 views

Why bulk of pages/extents are unallocated in sys.dm_db_file_page_usage

Could someone help shed some light on a strange sys.dm_db_file_page_usage result of a large database? sys.dm_db_file_space_usage result: |total_page_count | allocated_extent_page_count | ...
Mary W's user avatar
  • 11
1 vote
1 answer
115 views

InnoDB - how MySQL InnoDB pages works with underlying OS file system

I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage. Would you mind to confirm that my understanding is correct? InnoDB store ...
obanadingyo's user avatar
1 vote
1 answer
377 views

Is there a way to see which tables contribute most to WAL size?

I'm maintaining a postgresql database and am considering the use of clustering so that update queries have to touch fewer pages. With clustering based on indices I'm seeing a significant speedup for ...
saolof's user avatar
  • 111
0 votes
1 answer
294 views

pg_class returns 0 as relpages

I have this table: Table "public.region" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- r_regionkey | ...
tail's user avatar
  • 235
3 votes
1 answer
2k views

SQL Server :: How many 8K pages are taken by this record?

I would like to see how many 8K pages are taken by a specific record. If you run this query on AdventureWorks2019: SELECT ProductID ,Name ,DATALENGTH(Name) AS SizeInBytes ,LEN(Name) AS ...
Francesco Mantovani's user avatar
0 votes
2 answers
168 views

Sum of pages in the data file does not equal the file size

I am not trying to solve a specific problem, but just trying to understand. I have read several places that the data file (let us just assume one mdf file) is split into 8 KB pages. But when I sum ...
xhr489's user avatar
  • 805
2 votes
4 answers
674 views

What is the impact of power failure on recovery assuming checkpoint has not occurred?

When an INSERT query is fired, SQL server records this in its log and sends the confirmation to user that query is complete. Meanwhile it also updates the data page. Both of these (log and data page) ...
variable's user avatar
  • 3,593
0 votes
1 answer
393 views

Get number of deleted pages in PostgreSQL table/index?

How can I get the number of deleted pages in a table or index? My purpose in asking is to modify an index bloat query that otherwise doesn't take into account which pages are deleted (and therefore ...
Paul Draper's user avatar
-1 votes
2 answers
57 views

Does SQL server share data pages?

I am trying to understand more about how data is allocated to the 8KB data page: Can the same page be shared with rows from other databases? Can the same page be shared with rows from other tables?
variable's user avatar
  • 3,593
0 votes
0 answers
2k views

What are the differences between Heap File Organization and Sequential File Organization (Pile Method)

I was learning about File Organization in DBMS, but i found that the Heap File Organization and Sequential File Organization (Pile Method) have the same definition (or maybe i couldn't find any ...
Malek Alhourani's user avatar
1 vote
1 answer
580 views

what is difference between uniform extents and mixed extents in Database file storage structure?

I just went to some resources saying: uniform extents are owned by single object and mixed extents can be shared by maximum 8 objects. But I'm unable to grasp their insight meaning, like what is the ...
abhishek's user avatar
1 vote
2 answers
664 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 ...
Rauf Asadov's user avatar
  • 1,253
1 vote
2 answers
3k 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 ...
Marcus's user avatar
  • 378
17 votes
2 answers
4k 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:...
Rauf Asadov's user avatar
  • 1,253
2 votes
1 answer
237 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 [...
Aleksey Vitsko's user avatar
0 votes
2 answers
457 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 ...
sanjihan's user avatar
  • 253
15 votes
1 answer
7k 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 ...
Paul-Sebastian Manole's user avatar
5 votes
1 answer
2k 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 ...
Jigao's user avatar
  • 147
0 votes
2 answers
2k 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 ...
John P's user avatar
  • 169
1 vote
2 answers
2k 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 ...
Dinesh Kumar's user avatar
1 vote
1 answer
59 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 ...
Isaac A.'s user avatar
0 votes
1 answer
105 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 ...
Dinesh Kumar's user avatar
1 vote
1 answer
76 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 ...
Rumham's user avatar
  • 23
0 votes
1 answer
307 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. ...
Dinesh Kumar's user avatar
2 votes
2 answers
66 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 ...
Dhiren's user avatar
  • 141
2 votes
0 answers
71 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?
Dhiren's user avatar
  • 141
0 votes
1 answer
31 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 ...
Marcus's user avatar
  • 378
1 vote
0 answers
269 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), ...
Zach Smith's user avatar
  • 2,360
2 votes
1 answer
182 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?
Marcus's user avatar
  • 378
5 votes
3 answers
5k 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 ...
Diego Jancic's user avatar
10 votes
1 answer
2k 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, ...
Henrik Staun Poulsen's user avatar
1 vote
1 answer
1k 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?
Sybil's user avatar
  • 2,488
3 votes
1 answer
845 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: +--------------------+------+...
Zach Smith's user avatar
  • 2,360
0 votes
2 answers
255 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 ...
info.sqldba's user avatar
2 votes
1 answer
422 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 ...
info.sqldba's user avatar
2 votes
1 answer
129 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?
audlift-bit's user avatar
2 votes
1 answer
79 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?
audlift-bit's user avatar
1 vote
0 answers
652 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 ...
Svisstack's user avatar
  • 145
4 votes
1 answer
129 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. ...
Dave Sims's user avatar
  • 323
13 votes
3 answers
1k 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 ...
Alphas Supremum's user avatar
11 votes
2 answers
5k 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)) ...
Chris Woods's user avatar
  • 1,771
1 vote
1 answer
1k 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. ...
sql_handle's user avatar
2 votes
1 answer
114 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.
EngineeringSQL's user avatar
4 votes
2 answers
2k 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 ...
Marcus's user avatar
  • 378
3 votes
1 answer
85 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 ...
LearnByReading's user avatar
11 votes
4 answers
21k 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); ...
AdamL's user avatar
  • 405
5 votes
1 answer
345 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 ...
Michael Green's user avatar
3 votes
1 answer
683 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 ...
James Anderson's user avatar
24 votes
1 answer
19k 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; ...
Jack Douglas's user avatar
  • 39.8k
3 votes
1 answer
370 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 ...
BrianCooksey's user avatar