Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

The tag has no usage guidance.

0
votes
1answer
18 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 ...
1
vote
1answer
59 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 ...
0
votes
1answer
25 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. ...
0
votes
0answers
9 views

Handling Many Text Fields in a Mysql Table

Specification : Mysql (5.7.23) , Baracuda file format, DYNAMIC row format. I am working in bootstrapping a product. I have a scenario where the user may require 1 - 500 Text Fields for a particular ...
1
vote
0answers
70 views

Which columns are off-paged (ROW_FORMAT=DYNAMIC)?

When going through the MySQL 5.7 manual, I found some contradictory facts. Fact 1: When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length ...
3
votes
2answers
49 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
0answers
52 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?
0
votes
1answer
25 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 ...
1
vote
0answers
96 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), ...
2
votes
1answer
57 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?
5
votes
3answers
1k 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 ...
10
votes
1answer
759 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, ...
0
votes
1answer
286 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?
3
votes
1answer
204 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: +--------------------+------+...
0
votes
2answers
75 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 ...
3
votes
1answer
103 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 ...
3
votes
1answer
47 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?
3
votes
1answer
35 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?
1
vote
0answers
253 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 ...
5
votes
1answer
109 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. ...
13
votes
3answers
836 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 ...
11
votes
2answers
2k 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)) ...
0
votes
1answer
465 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. ...
2
votes
1answer
99 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.
4
votes
2answers
745 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 ...
3
votes
1answer
71 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 ...
10
votes
2answers
13k 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); ...
3
votes
1answer
207 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
337 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 ...
10
votes
1answer
9k 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; ...
3
votes
1answer
152 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 ...
0
votes
2answers
660 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 ...
1
vote
0answers
154 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 ...
3
votes
1answer
5k 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.
2
votes
1answer
821 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 object like xml. And xml size could be different. Some are about 2kb average. And some are 7kb average. So does it make ...
1
vote
1answer
425 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 ...
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: ...
12
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 (...
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.
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 ...