Questions tagged [database-internals]
For technical questions about the internal workings of the database engine.
225
questions
-1
votes
1
answer
36
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 [...
- 15.4k
0
votes
0
answers
30
views
Nonclustered Columnstore Tuple Mover Merge Threshold
I'm learning about columnstore indexes in SQL Server and do some testing to better understand it. For the tests I'm using the StackOverlfow2013 database on SQL Server 2022.
I've created a copy of the ...
- 1,984
10
votes
1
answer
1k
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 ...
- 1,729
2
votes
2
answers
218
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
0
votes
0
answers
37
views
How does ghost cleanup process work internally? [duplicate]
I wanted to understand when a record is updated/deleted in 8-kb page, what happens to old record?
I know it gets cleaned up by ghost cleanup process - but what method does it use for this?
Some ...
- 133
3
votes
2
answers
494
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 ...
- 602
9
votes
1
answer
582
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?
- 78.6k
4
votes
1
answer
171
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 ...
- 1,729
2
votes
1
answer
201
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 ...
- 1,984
2
votes
1
answer
128
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 ...
- 1,729
3
votes
1
answer
88
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 ...
- 249
1
vote
2
answers
38
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 ...
- 486
0
votes
1
answer
224
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,213
6
votes
1
answer
1k
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....
- 1,729
16
votes
1
answer
488
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 ...
- 31.6k
0
votes
2
answers
60
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
210
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 ...
- 743
2
votes
1
answer
99
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 ...
- 135
4
votes
1
answer
408
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 ...
- 247
1
vote
2
answers
189
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
167
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
883
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
163
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
312
views
How memory limit for backup process is calculated?
How "memory limit" for backup process is actually calculated?
- 1,213
2
votes
2
answers
217
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 ...
- 1,984
12
votes
1
answer
560
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 ...
- 78.6k
1
vote
1
answer
166
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
16
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
- 31
-1
votes
0
answers
33
views
BIT VARYING internals
I Have a table called IP_Block in which I created two columns
ID of type Bit Varying(32) as PK
IP Bloc of type CIDR
the construction of the ID is the following :
For example for the block 1.0.0.0/8 ...
- 31
1
vote
2
answers
4k
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)?
- 31
14
votes
2
answers
1k
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
3
votes
1
answer
247
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 ...
- 1,984
1
vote
1
answer
383
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 ...
- 21
1
vote
0
answers
72
views
My question is in a select statement how do you return the RID along with the table data?
I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table.
In the event that a developer does not use a clustered ...
- 11
9
votes
2
answers
571
views
EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan
The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT.
What is the purpose of the projection? e.g. Is there a different EXCEPT ...
- 869
2
votes
1
answer
255
views
SQLite Internals - Records
Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (...
- 121
0
votes
2
answers
556
views
What triggers the SSRS to generate a log file or modify a current one?
When running a trace on my new SSRS Reporting Services 2016, after troubleshooting some problems related to migrating from SSRS 2008, I find the following pattern running continuously:
Here is the ...
- 15.4k
4
votes
2
answers
3k
views
Difference between Physical Reads and Read-Ahead Reads
I am trying to understand read-ahead reads, but it seems a bit complicated to me. I searched on the web and got the following:
From Reading Pages (Microsoft documentation):
Read-ahead anticipates the ...
- 1,213
0
votes
0
answers
633
views
What means execute S_2, S_3 and S_4: COMMIT in Postgresql logs?
I'd noticed different commit types in Postgres log:
2019-12-20 12:00:00 [99999]: [12-1] host=...,user=...,db=... LOG: duration: 1000.000 ms execute S_2: COMMIT
2019-12-20 12:00:00 [99999]: [12-1] ...
- 421
4
votes
1
answer
275
views
SQL Server chooses unselective index
I was testing SQL Server indexes and found very strange behavior. Here is my code:
DROP TABLE IF EXISTS dbo._Test
DROP TABLE IF EXISTS dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL,
...
- 1,393
0
votes
1
answer
264
views
does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server
when an automatic checpoint runs in background in sql server, i know it flushes all the dirty pages to data file.
does it also flushes dirty pages which are related to un-committed transactions into ...
0
votes
0
answers
232
views
Trimming copy/delete mysql table rows using Trigger
I am using Freeradius AAA server on Ubuntu 18 server which uses mySQL ver 5.7.27 as DB to store entries. INNODB is being used. It have a table called radacct which stores users accounting /session ...
- 111
17
votes
1
answer
503
views
Constant Scan applying
This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.
Using CROSS APPLY to alias an ...
- 2,334
1
vote
1
answer
94
views
Physical co-clustering of two or more logical relations/tables in modern RDBMS
One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join ...
- 243
5
votes
1
answer
476
views
Constant Scan joining
In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me.
Setup is simple
...
- 2,334
15
votes
1
answer
538
views
Cardinality estimate outside the histogram
Setup
I'm having some trouble understanding a cardinality estimate. Here's my test setup:
the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new ...
- 28.5k
1
vote
0
answers
106
views
Character set issue
Error 'Character set '#255' is not a compiled character set and is not specified in the '/user/share/mysql'.
Facing error in replication channel from mysql 8.0.16 to mysql 5.7.25.Is there any ...
- 11
7
votes
1
answer
539
views
"Invalid Protection Option"
In the following error message, the reason given is "Invalid Protection Option". What does that indicate?
SQL Server detected a logical consistency-based I/O error: invalid protection option
...
- 68.5k
10
votes
1
answer
435
views
Hash aggregate bailout
A question that arose in a chat discussion:
I know hash join bailout switches internally to a sort of nested loops thing.
What does SQL Server do for a hash aggregate bailout (if it can happen at ...
- 78.6k
14
votes
4
answers
2k
views
Index Uniqueness Overhead
I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
- 243