Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
3 votes
1 answer
67 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 ...
user avatar
  • 197
1 vote
2 answers
24 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 ...
user avatar
  • 400
0 votes
1 answer
39 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 ...
user avatar
  • 1,163
6 votes
1 answer
252 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....
user avatar
  • 1,450
16 votes
1 answer
472 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 ...
user avatar
  • 30.3k
0 votes
2 answers
49 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, ...
user avatar
0 votes
1 answer
106 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 ...
user avatar
  • 587
2 votes
1 answer
71 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 ...
user avatar
  • 135
4 votes
1 answer
208 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 ...
user avatar
  • 205
1 vote
2 answers
89 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 ...
user avatar
0 votes
0 answers
36 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"...
user avatar
  • 109
6 votes
3 answers
822 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(...
user avatar
3 votes
1 answer
90 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.
user avatar
2 votes
2 answers
152 views

How memory limit for backup process is calculated?

How "memory limit" for backup process is actually calculated?
user avatar
  • 1,163
2 votes
2 answers
153 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 ...
user avatar
12 votes
1 answer
503 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 ...
user avatar
  • 69.2k
1 vote
1 answer
74 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') ...
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
user avatar
  • 35
0 votes
0 answers
15 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 ...
user avatar
  • 35
1 vote
2 answers
2k 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)?
user avatar
  • 35
13 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] ...
user avatar
  • 173
3 votes
1 answer
184 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 ...
user avatar
1 vote
1 answer
208 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 ...
user avatar
1 vote
0 answers
60 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 ...
user avatar
9 votes
2 answers
559 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 ...
user avatar
2 votes
1 answer
158 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 (...
user avatar
  • 121
0 votes
2 answers
315 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 ...
user avatar
3 votes
2 answers
2k 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 ...
user avatar
  • 1,163
0 votes
0 answers
471 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] ...
user avatar
  • 401
4 votes
1 answer
263 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, ...
user avatar
0 votes
1 answer
154 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 ...
user avatar
0 votes
0 answers
160 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 ...
user avatar
  • 111
15 votes
1 answer
457 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 ...
user avatar
  • 2,294
1 vote
1 answer
77 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 ...
user avatar
  • 233
5 votes
1 answer
427 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 ...
user avatar
  • 2,294
15 votes
1 answer
498 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 ...
user avatar
  • 27.1k
1 vote
0 answers
89 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 ...
user avatar
7 votes
1 answer
431 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 ...
user avatar
  • 67.1k
10 votes
1 answer
400 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 ...
user avatar
  • 69.2k
14 votes
4 answers
1k 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 ...
user avatar
  • 143
1 vote
0 answers
29 views

DCM page in Filestream_Data_Filegroup

I'm doing some research about the DCM (Differential Change Map) page in SQL Server 2016. This is what I discovered so far: page type: 16 each DCM page covers a GAM interval each data file has a DCM ...
user avatar
7 votes
2 answers
205 views

What is the purpose of this Uniq1002 column in this index scan?

Take the following repro: USE tempdb; IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL DROP TABLE dbo.t GO CREATE TABLE dbo.t ( id int NOT NULL PRIMARY KEY NONCLUSTERED ...
user avatar
  • 67.1k
0 votes
1 answer
490 views

View images as query results [closed]

I've done lots of research on the topic but I'm drawing a blank. Are their any database engines that can return rendered images in the query response? For example: Select images from fruit where ...
user avatar
  • 55
-2 votes
1 answer
110 views

SSMS Application Security idle session locking window [closed]

We are running multiple versions of SQL Server Management Studio in our network, I believe from 2000 through 2017. For our security review we need to find out how to set an inactivity timeout in the ...
user avatar
0 votes
0 answers
137 views

Is there a way to make postgres "COUNT WHERE" query log(N)?

Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this: find an item with the value of rating 50(or greater) in our b-tree index, pretty fast. Iterate though all items ...
user avatar
  • 113
6 votes
0 answers
6k views

Alwayson AG lease timeout and healthcheck timeout [closed]

We have a physical SQL Server 2016 Enterprise SP2 with two instances and each instance contains one AG. Sometimes it happens that one AG goes to resolving (without failover), the cluster resource is ...
user avatar
1 vote
1 answer
619 views

Calculating disk usage with INFORMATION_SCHEMA.TABLE versus file system?

MySQL's database size can be calculated with SELECT table_schema AS db_name, SUM(data_length + index_length) AS size FROM INFORMATION_SCHEMA.TABLES WHERE table_schema != 'INFORMATION_SCHEMA' GROUP BY ...
user avatar
  • 55.7k
3 votes
3 answers
2k views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
user avatar
  • 43
14 votes
2 answers
2k views

Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?

Consider the following query that inserts rows from a source table only if they aren't already in the target table: INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK) SELECT ...
user avatar
  • 30.3k
4 votes
1 answer
365 views

SQL Server 2016 still uses mixed_extent_allocation in TempDB

Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace. I expected ...
user avatar

1
2 3 4 5