Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
87 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 ...
2
votes
1answer
56 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 ...
6
votes
1answer
2k views

SQL Server Internal Memory Pressure

I am trying to identify the cause of internal memory pressure. From what I have learned, Resource Monitor Ring Buffer returns an indicator for internal pressure. For example by using this query, ...
2
votes
1answer
125 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 (...
1
vote
1answer
840 views

What are these SFP files that SQL Server distributes?

What are the SFP files that come with SQL Server 2017? /opt/mssql/lib/sqlservr.sfp /opt/mssql/lib/sqldkxplat.sfp /opt/mssql/lib/secforwarderxplat.sfp /opt/mssql/lib/system.common.sfp /opt/mssql/lib/...
1
vote
1answer
139 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 ...
2
votes
4answers
50k views

Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle

The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list? How do we find all sql id, sql text, ...
3
votes
1answer
141 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 ...
1
vote
2answers
34 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 ...
0
votes
0answers
35 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"...
5
votes
1answer
471 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
6
votes
3answers
787 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(...
10
votes
1answer
4k views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide, I am still unsure if it's necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index, in order to get minimal ...
3
votes
1answer
67 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.
2
votes
2answers
99 views

How memory limit for backup process is calculated?

How "memory limit" for backup process is actually calculated?
14
votes
0answers
370 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
votes
2answers
104 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 ...
0
votes
1answer
87 views

LOAD DATA statement returns a syntax error a fraction of the time

MySQL 5.5.32, MyISAM A LOAD DATA INFILE statement usually executes and returns normally, succeeding at loading the data. On rare occasion it fails, returning a generic syntax error, always indicating ...
0
votes
0answers
186 views

How to delete specific range of data from Service Broker > System Queues data table?

Due to sudden increase of my database .mdf file we found there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table. we try this ...
12
votes
1answer
456 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 ...
3
votes
1answer
135 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
vote
1answer
56 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') ...
0
votes
0answers
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
0
votes
0answers
11 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 ...
1
vote
2answers
678 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)?
13
votes
2answers
965 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] ...
1
vote
0answers
58 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 ...
28
votes
1answer
5k views

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by ...
19
votes
1answer
10k views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
3
votes
1answer
90 views

What are system columns used for in PostgreSQL?

I was following a PostgreSQL tutorial and the author just mentioned that there are system columns in PostgreSQL but did not mention when and why to use them. Also, in PostgreSQL Documentation it ...
9
votes
2answers
534 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 ...
2
votes
2answers
1k 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 ...
0
votes
2answers
195 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 ...
4
votes
1answer
2k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
0
votes
0answers
343 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] ...
4
votes
1answer
251 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, ...
5
votes
3answers
5k views

Why does the optimizer choose nested loops over merge joins here?

I have 3 tables. #a is main one and two secondary tables, #b and #c. create table #a (a int not null, primary key (a asc)) ; create table #b (b int not null, primary key (b asc)) ; create table #c (c ...
8
votes
1answer
474 views

Why more (and varying number of) logical reads with read ahead (prefetch)?

After creating tpch database in my SQL Server, I tried below query: set statistics io on DBCC DROPCLEANBUFFERS; select top 100 * from dbo.lineitem order by l_partkey; The table ...
0
votes
1answer
107 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 ...
1
vote
1answer
136 views

Is database record access sequential after retrieving the indexed page from disk? [closed]

This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer. What I understand about databases that if a queried or requested record is found in the ...
3
votes
1answer
8k views

Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?
14
votes
2answers
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 ...
10
votes
1answer
2k views

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem. SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) AA ( A ) CROSS JOIN ...
36
votes
1answer
22k views

DELETE vs TRUNCATE

I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of: DELETE -> the database ...
39
votes
2answers
15k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[...
0
votes
0answers
109 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 ...
5
votes
1answer
386 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 ...
19
votes
1answer
1k views

Nonclustered index storage on clustered columnstore

In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is ...
1
vote
1answer
67 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 ...
2
votes
3answers
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 ...

1
2 3 4 5