Questions tagged [database-internals]
For technical questions about the internal workings of the database engine.
242 questions
3
votes
1
answer
78
views
What is the maintenance on the index caused by insert, update, or delete operations?
Follow up question for this answer regarding the excerpt of the sys.dm_db_index_usage_stats doc:
The user_updates column is a counter of maintenance on the index
caused by insert, update, or delete ...
- 6,511
14
votes
2
answers
1k
views
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 - ...
- 88.7k
2
votes
1
answer
164
views
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
2
votes
1
answer
401
views
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
...
- 167
0
votes
1
answer
385
views
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 ...
5
votes
1
answer
294
views
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 ...
- 19k
3
votes
1
answer
508
views
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
11
votes
3
answers
384
views
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 ...
- 2,122
0
votes
1
answer
140
views
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, ...
- 17.3k
6
votes
1
answer
346
views
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:
...
- 95.8k
0
votes
1
answer
88
views
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 ...
- 103
3
votes
0
answers
98
views
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
0
votes
1
answer
235
views
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 ...
- 2,122
2
votes
3
answers
490
views
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 ...
- 19k
4
votes
2
answers
206
views
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
0
votes
2
answers
841
views
Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint
I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.
Here is a simplified version of my T-SQL code:
IF OBJECT_ID('dbo.dept') IS NOT NULL ...
- 1,239
4
votes
1
answer
953
views
How does the XML_COMPRESSION option work?
XML_COMPRESSION has recently gone GA in Azure SQL Database
I've been trying to find some details about how it works to understand the pros and cons and so far not found any specifics.
Trying the below ...
- 88.7k
2
votes
1
answer
84
views
How do you make sure a data page can only have 3 rows
For a table with a setting of (fillfactor = 75) I am trying to make sure that each data page can only have 3 rows.
select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012.
First 24 is page header ...
- 487
5
votes
1
answer
851
views
What page type is page 516855552?
SQL Server tracks page allocation in various internal bitmaps. Among these are the Global Allocation Map (GAM) and Page Free Space (PFS) pages. We know that GAM pages occur at set intervals of 511232 ...
- 25.3k
7
votes
3
answers
854
views
What is the stride of a GAM interval
For interest, I was reading about the internal structures of a Microsoft SQL Server file. It's fairly obvious how IAM pages are connected. However, it is not clear to me where later GAM pages are to ...
- 25.3k
-1
votes
1
answer
614
views
how to get database settings such as LEGACY_CARDINALITY_ESTIMATION and INTERLEAVED_EXECUTION_TVF
I am moving some database finally to compatibility level 150 using the script below
USE [master]
GO
ALTER DATABASE [DB1] SET COMPATIBILITY_LEVEL = 150
GO
USE [...
- 17.3k
10
votes
1
answer
4k
views
Is it possible for SQL Server to grant more memory to a query than is available to the instance
I was asked the other day what would happen if SQL Server wanted to run a single query that was granted more memory than is available to the instance. My initial thoughts were that I may see ...
- 2,256
2
votes
2
answers
2k
views
Is space reserved for a field when data doesn't use the full length?
A column is marked as nvarchar(50), but the value stored is only two characters. Do the remaining 48 characters take up space?
- 29
3
votes
2
answers
2k
views
Logical reads and LOB logical reads
I have a query app scanning a whole table with a text field.
The query is doing this many reads:
Scan count 1, logical reads 170586, physical reads 3, read-ahead reads
174716, lob logical reads ...
- 512
10
votes
1
answer
896
views
SQL Server LOB variables and memory usage
When I use a variable of a large object (LOB) data type in SQL Server, is the whole thing kept in memory at all times? Even if it is 2GB in size?
- 95.8k
4
votes
1
answer
321
views
How does SQL estimate the number of rows in a less than < Predicate
I have been doing some testing to try to better understand how SQL Server uses a histogram to estimate the number of rows that will match an equality predicate and also a < or > predicate
Given ...
- 2,256
2
votes
1
answer
373
views
Diff backup fails with error 3035 but log backup succeeds
We have a SQL Server 2014 Enterprise where the DIFF backups fail.
This is the error message we get:
Msg 3035, Level 16, State 1, Server sqltest, Line 1
Cannot perform a differential backup for ...
- 2,122
3
votes
1
answer
257
views
Why Does SQL Server not have 200 buckets in the statistics histogram when there are >100k distinct values in the table
Given I am using the AdventureWorks2016 OLTP database why does the statistics histogram for the index PK_TransactionHistory_TransactionID on table Production.TransactionHistory only contain 3 ...
- 2,256
3
votes
1
answer
154
views
Change the definition of an Index with Expression without dropping/recreating the index
I'm in a peculiar situation:
I have multiple indexes with expressions that use a function. I want to switch them to a different function, where I can guarantee the function behaves the same (so the ...
- 279
1
vote
2
answers
60
views
Does any RDBMS "inline" a 1:0-1 relationship's dependent table?
In the case of a 1:0-1 relationship between a principal table p and dependent table d, when commonly-used queries will read from both p and d, do any RDMBs inline d to eliminate the need to store d ...
- 692
1
vote
1
answer
792
views
What is difference between checkpoint_timeout and checkpoint_completion_target in PostgreSQL?
I am MSSQL guy and I find it a bit difficult to understand the main purpose of checkpoint_completion_target. I cannot find a comprehensive resource that would explain more clearly the difference ...
- 1,293
6
votes
1
answer
4k
views
Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan
Given the following simple query on the StackOverflow2010 database:
SELECT u.DisplayName,
u.Reputation
FROM Users u
JOIN Posts p
ON u.id = p.OwnerUserId
WHERE u....
- 2,256
16
votes
1
answer
527
views
Does SQL Server 2019 have task scheduling differences between enterprise and standard editions?
In How It Works: SQL Server 2012 Database Engine Task Scheduling, Bob Dorr explains some of the changes for worker scheduler assignment in SQL Server 2012. He mentions that some of the improvements ...
- 33.3k
0
votes
2
answers
83
views
how do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources? [closed]
So for many days, I had a question in mind.
How do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources?
e.g. Kafka/Kinesis => DW(Snowflake, Teradata, ...
0
votes
1
answer
392
views
Reading of fixed-length data types
Say I have a fixed length column and I am SELECTing from it, say 100 rows. When reading different rows of the fixed length column, does SQL Server check the length of the column for every row or does ...
- 827
2
votes
1
answer
178
views
Why does SQL Server Trace Flag 715 behave differently from the TABLOCK query hint?
It's my understanding, based on numerous sources (including this one: https://techcommunity.microsoft.com/t5/sql-server/migrating-sap-workloads-to-sql-server-just-got-2-5x-faster/ba-p/384910) that SQL ...
- 177
4
votes
1
answer
852
views
Suggestion for nearly gap-less sequences in postgres
In PostgreSQL sequences are designed to have gaps for reasons mentioned in this post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences. My question is: could sequences not be ...
- 257
1
vote
2
answers
401
views
Tracing Postgres frontend and backend messages
As per my understanding when we execute a command or query, the Postgres client sends a frontend message, and in return gets the response in the backend message format.
How do I capture and check ...
- 135
0
votes
0
answers
294
views
B-tree indexing example
I am trying to understand "how" Postgres creates the indexes using b-trees with an example.
To be specific, I am looking to understand how the b-tree will look like when the ("Name"...
- 109
6
votes
3
answers
1k
views
Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size?
Is it possible to overwrite the same disk address in a T-SQL update statement? A use case would be preventing recovery of the original text on disk for security or anonymity reasons.
CREATE TABLE Test(...
3
votes
1
answer
285
views
Why is log reserve negative in the SQL Server compensation log when using fn_dblog command
I was using the fn_dblog command and was not able to get why the log reserve was negative.
- 39
2
votes
2
answers
659
views
How memory limit for backup process is calculated?
How "memory limit" for backup process is actually calculated?
- 1,293
2
votes
2
answers
494
views
Difference between avg Disk sec/write and io_stall_write_ms
We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks.
So we have data,log, tempdb and system db's have each their own drive. Date and log ...
- 2,122
13
votes
1
answer
632
views
Prevent THREADPOOL waits due to idle worker thread trimming
After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:
The idea is that once SQL Server has created ...
- 95.8k
1
vote
1
answer
743
views
Storage space requirements for a varchar(40) column to store 'abcd' with charset 'ucs2' and 'utf8mb4'?
I understand that if the length is > 255 it will require 2 bytes to store the length prefix. I just need to verify my assumptions. Please tell me the length prefix and the length of string('abcd') ...
- 33
0
votes
0
answers
17
views
Array of chars? [duplicate]
Can we create an array of chars in postgresql i.e (Column_name char[5]) and does it take only 5 bytes in disk ? since type char takes only one byte
- 55
3
votes
2
answers
9k
views
varchar(n) size?
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
- 55
14
votes
2
answers
2k
views
Unclear update conflict
I have two questions:
1. Why do I get update conflict in this situation instead of just blocking:
-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] ...
- 183
4
votes
1
answer
472
views
Badly-formed histogram causes bad estimates on Nested Loop
We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
- 2,122
1
vote
1
answer
772
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 ...
- 13