Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [database-internals]

For technical questions about the internal workings of the database engine.

Filter by
Sorted by
Tagged with
0 votes
0 answers
32 views

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, ...
Alex Bartsmon's user avatar
6 votes
1 answer
155 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: ...
Paul White's user avatar
  • 87.1k
0 votes
1 answer
40 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 ...
Amr Elmohamady's user avatar
3 votes
0 answers
72 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 ...
William's user avatar
  • 135
0 votes
1 answer
73 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 ...
Frederik Vanderhaegen's user avatar
2 votes
3 answers
467 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 ...
John K. N.'s user avatar
4 votes
2 answers
156 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 ...
saadkaul's user avatar
  • 113
0 votes
2 answers
335 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 ...
Fajela Tajkiya's user avatar
3 votes
1 answer
429 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 ...
Martin Smith's user avatar
  • 85.4k
2 votes
1 answer
48 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 ...
jian's user avatar
  • 447
5 votes
1 answer
828 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 ...
Michael Green's user avatar
7 votes
3 answers
759 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 ...
Michael Green's user avatar
-1 votes
1 answer
403 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 [...
Marcello Miorelli's user avatar
10 votes
1 answer
3k 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 ...
SE1986's user avatar
  • 2,000
2 votes
2 answers
1k 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?
rome's user avatar
  • 29
3 votes
2 answers
1k 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 ...
Mattia Nocerino's user avatar
9 votes
1 answer
757 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?
Paul White's user avatar
  • 87.1k
4 votes
1 answer
281 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 ...
SE1986's user avatar
  • 2,000
2 votes
1 answer
330 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 ...
Frederik Vanderhaegen's user avatar
3 votes
1 answer
209 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 ...
SE1986's user avatar
  • 2,000
3 votes
1 answer
128 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 ...
Joe's user avatar
  • 269
1 vote
2 answers
48 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 ...
Dai's user avatar
  • 593
1 vote
1 answer
691 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 ...
Rauf Asadov's user avatar
  • 1,293
6 votes
1 answer
3k 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....
SE1986's user avatar
  • 2,000
16 votes
1 answer
501 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 ...
Joe Obbish's user avatar
  • 32.5k
0 votes
2 answers
67 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, ...
Libertarian's user avatar
0 votes
1 answer
313 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 ...
xhr489's user avatar
  • 819
2 votes
1 answer
154 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 ...
Mike S's user avatar
  • 135
4 votes
1 answer
698 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 ...
sev's user avatar
  • 247
1 vote
2 answers
358 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 ...
Govinda Sakhare's user avatar
0 votes
0 answers
261 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"...
Rebooting's user avatar
  • 109
6 votes
3 answers
987 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(...
Francisco d'Anconia's user avatar
3 votes
1 answer
213 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.
java code's user avatar
2 votes
2 answers
524 views

How memory limit for backup process is calculated?

How "memory limit" for backup process is actually calculated?
Rauf Asadov's user avatar
  • 1,293
2 votes
2 answers
338 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 ...
Frederik Vanderhaegen's user avatar
13 votes
1 answer
611 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 ...
Paul White's user avatar
  • 87.1k
1 vote
1 answer
532 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') ...
Great Greek's user avatar
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
d4bbi's user avatar
  • 45
2 votes
2 answers
8k 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)?
d4bbi's user avatar
  • 45
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] ...
Pavel Zv's user avatar
  • 183
4 votes
1 answer
401 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 ...
Frederik Vanderhaegen's user avatar
1 vote
1 answer
631 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 ...
abhishek's user avatar
1 vote
0 answers
129 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 ...
Andy Eggers's user avatar
9 votes
2 answers
592 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 ...
Paul Holmes's user avatar
2 votes
1 answer
370 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 (...
Matis's user avatar
  • 121
0 votes
2 answers
903 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 ...
Marcello Miorelli's user avatar
6 votes
2 answers
5k 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 ...
Rauf Asadov's user avatar
  • 1,293
0 votes
0 answers
939 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] ...
Rogerlr's user avatar
  • 451
4 votes
1 answer
284 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, ...
Artashes  Khachatryan's user avatar
0 votes
1 answer
439 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 ...
sachin-SQLServernewbiee's user avatar

1
2 3 4 5