Questions tagged [database-internals]
For technical questions about the internal workings of the database engine.
242 questions
10votes
2answers
768views
How does SQL Server maintain rowcount metadata?
For an example rowstore table... CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL); There are a variety of different methods of retrieving table row counts from metadata in SQL Server - ...
- 87.4k
2votes
1answer
84views
Why does innodb next-key locks lock more range than the condition?
Assume the following table: DROP TABLE IF EXISTS person; CREATE TABLE person ( id int unsigned auto_increment primary key, name varchar(255) not null, age tinyint unsigned, key (age)...
- 155
2votes
1answer
383views
Are there two levels of constant folding?
Are there two levels of constant folding, one in the conversion tree and one in the simplification phase? If I run the following query SELECT P.[ProductID] FROM Production.Product AS P WHERE ...
0votes
1answer
158views
Query running successfully but much longer due to wait_event MessageQueueSend
I have a long running bug where some larger queries, sometimes run much much longer due to being stuck on wait_even MessageQueueSend. The difference can be anything from <100% to 1000s percent when ...
5votes
1answer
277views
When looking at the first DCM page, where is the next DCM page documented?
TL/DR; When looking at the first DCM page in a database (which documents which extents have been modified, so that the DIFF backup process doesn't have to scan the whole database for changes, but can ...
- 18.5k
3votes
1answer
464views
How do nonclustered columnstore indexes in SQL Server handle linked updates and deletes with rowstore tables(either heap or clustered index tables)?
I have read several articles and blog posts describing that columnstore indexes store all fields redundantly, including the row IDs from the underlying rowstore table (either RID for heaps or ...
- 31
11votes
3answers
326views
Huge log backups due to enabling querystore
We have a SQL Server 2019 CU18 where we discovered a strange issue with querystore. Normally the average size of the hourly logbackup is 40MB but as soon as we enable querystore the average size of ...
0votes
1answer
90views
sql server backups - what is physical_block_size?
we have recently changed the file server that is the repository (the place where the backups are stored) server . I noticed the hardware specially IO is much better backup take shorter to finish, ...
0votes
0answers
72views
How to Determine the Low High Water Mark (LHWM) in Oracle DB?
How could one determine the position of the Low High Water Mark (LHWM) for a non-partitioned heap table segment in Oracle? I am concerned, that if we only do direct path inserts and never update, ...
6votes
1answer
296views
Sampled Statistics Percentage Internal Calculation
When SQL Server builds sampled statistics, how is the sampling percentage calculated? For example, updating statistics on the PostId column of the dbo.Votes table in the Stack Overflow 2013 database: ...
- 93k
0votes
1answer
69views
Why does PostgreSQL not add padding for column alignment at the end of tuple?
In the example here: https://www.percona.com/blog/postgresql-column-alignment-and-padding-how-to-improve-performance-with-smarter-table-design/ Why doesn't PostgreSQL add 2 bytes padding after the ...
3votes
0answers
81views
Are Innodb redo logs written atomically?
When flushing dirty pages from buffer pool to disk pages, it uses double write buffering to avoid half written pages. But redo logs do not have such mechanism. So, how does it prevent redo logs from ...
- 155
0votes
1answer
181views
High free memory and Lazy Writer triggered by IndicatorsPool
I'm experiencing a strange issue on one of our SQL Server (SQL Server 2019 Enterprise CU18). The server has 12 cores and 96GB physical memory and is also part of a failover cluster. Max memory has ...
2votes
3answers
473views
Why does SQL Server require a LEFT JOIN to retrieve a status that doesn't exist?
I was recently querying our internal database inventory tool for a list of servers, instances and databases, and was adding the corresponding status to each server, instance and database. Relationship ...
- 18.5k
4votes
2answers
178views
Table size increased after changing datatype from nchar to varchar
I am facing a strange behavior when changing datatype from nchar(100) to varchar(100) for one column. I understand if I change datatype of a column where data is present then it can increase size but ...
- 113
