Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [data-pages]

60 questions
26
votes
1 answer

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; select ctid , id from t; ctid | id :---- |…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
17
votes
2 answers

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: Demystifying TempDb and recommendations). But…
Rauf Asadov
  • 1,313
  • 14
  • 36
15
votes
1 answer

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 past a certain fragmentation threshold) need to…
Paul-Sebastian Manole
  • 682
  • 6
  • 12
13
votes
1 answer

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 heard wrong and data page are always stored…
Peanut
  • 233
  • 2
  • 5
13
votes
1 answer

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 (8192 - 96 - 8060)B = 36B. Ok, this is logical…
ooutwire
  • 1,437
  • 10
  • 18
13
votes
3 answers

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 CLUSTERED INDEX idx_cl_id ON…
Alphas Supremum
  • 233
  • 1
  • 6
12
votes
4 answers

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); and then delete the file: ALTER DATABASE tempdb…
AdamL
  • 415
  • 1
  • 5
  • 12
11
votes
2 answers

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)) + SUM(DATALENGTH(Field2)) +…
Chris Woods
  • 1,791
  • 1
  • 16
  • 22
10
votes
1 answer

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, State 1Index 'PK_Mytable' row length exceeds the…
Henrik Staun Poulsen
  • 2,249
  • 2
  • 23
  • 41
6
votes
1 answer

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.
jrara
  • 5,393
  • 20
  • 58
  • 65
5
votes
1 answer

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 seems clear to me from the context that there would…
Michael Green
  • 25,255
  • 13
  • 54
  • 100
5
votes
1 answer

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 to the Low Level Cache, when these pages are…
Jigao
  • 167
  • 1
  • 8
5
votes
3 answers

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 deletion and it's a multi-tenancy application, I…
Diego Jancic
  • 451
  • 5
  • 15
4
votes
1 answer

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. At a high level you could think: I can just put…
Dave Sims
  • 333
  • 1
  • 4
  • 12
4
votes
2 answers

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 at all. After ~30 seconds, the server resumes…
Marcus
  • 390
  • 1
  • 4
  • 15
1
2 3 4