Questions tagged [database-internals]
For technical questions about the internal workings of the database engine.
200
questions
11
votes
1answer
386 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 ...
1
vote
1answer
37 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
9 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 ...
0
votes
2answers
58 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
788 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] ...
2
votes
1answer
61 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
0answers
57 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 ...
9
votes
2answers
512 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 ...
1
vote
1answer
57 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 (...
0
votes
0answers
96 views
How does MySQL index a datetime column?
My assumption is datetime values, storing time to at least the second (YYYY-MM-DD HH:MM:SS), would be nearly unique across records in a database table.
For example, if there were three records in ...
0
votes
2answers
37 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 ...
1
vote
2answers
308 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
0answers
106 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
247 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,
...
0
votes
1answer
37 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
0answers
55 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 ...
8
votes
0answers
257 views
Constant Scan applying
This question is continuation of optimizer behavior exploration in regard to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.
Using CROSS APPLY to alias an ...
1
vote
1answer
52 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 ...
5
votes
1answer
312 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
...
15
votes
1answer
429 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 ...
1
vote
0answers
60 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 ...
7
votes
1answer
222 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
...
10
votes
1answer
293 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 ...
14
votes
4answers
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 ...
1
vote
0answers
23 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 ...
7
votes
2answers
175 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
...
0
votes
1answer
31 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 ...
-2
votes
1answer
66 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 ...
0
votes
0answers
104 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 ...
6
votes
0answers
3k 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 ...
1
vote
1answer
309 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 ...
2
votes
3answers
912 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 ...
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 ...
4
votes
1answer
187 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 ...
4
votes
2answers
765 views
Why does an index rebuild requires a Sch-M lock?
I don't see why an index rebuild would require a Sch-M lock on the given index.
2
votes
2answers
238 views
When does MySQL begin to start sending data to a client?
I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
1answer
79 views
Will Postgresql statements execute synchronously if autocommit=true?
If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server?
I read that on the postgres server side, "there ...
2
votes
1answer
94 views
How to find function definition
How can I find the definitions of below system functions in SQL Server:
has_access
permission_name
I tried with DAC connection but getting below error:
Msg 15009, Level 16, State 1, Procedure ...
12
votes
1answer
586 views
Why is this stream aggregate necessary?
Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script):
SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);
...
0
votes
2answers
768 views
How to understand Composite Primary Key when it comes to storage (in Mysql)?
I can image how rows will be arranged in a single database page as primary key order (example an auto-increment key) after reading this post. But I wonder how it will in the case of composite primary ...
7
votes
0answers
200 views
dm_os_memory_cache_clock_hands understanding
As I mentioned in this question I'm trying to understand the working of clock hands. Simply put clock hands move when there is memory pressure so that cache entries can be removed or the cost can be ...
3
votes
1answer
235 views
sql server cache clock hands
I'm doing some research about how to troubleshoot local memory pressure. Correct me if I'm wrong but as I understand it is that SQL Server uses a CLOCK algorithm for the cache- and userstores. Each ...
0
votes
2answers
146 views
No 'Copying to tmp table' state in profiling a mysql query
I have a table with 100k rows
CREATE TABLE `small_table` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varc` varchar(255) DEFAULT NULL,
`txt` text,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB ...
9
votes
1answer
621 views
Measuring plan eviction
We have a SQL Server 2016 SP1 with max memory set to 24GB.
This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored ...
1
vote
1answer
121 views
How are page splits determined internally in MySQL?
Going through this answer for a stack-overflow question, I found a fact that
In The physical structure of InnoDB index pages I describe the Last Insert Position, Page Direction, and Number of ...
3
votes
1answer
166 views
leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk
I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain.
I've a clustered index on a table of about 66 million rows and rows are added throughout ...
31
votes
1answer
2k views
Why is a scan faster than seek for this predicate?
I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals.
On my machine, the following query does a clustered index ...
1
vote
2answers
341 views
Extent Allocation Mechanism in SQL Server 2017
I was looking at the SQL Server 2017 Documentation for page and extent allocations.
What I got to know from online resources and sessions in SQL conferences: Starting with SQL Server 2016, Uniform ...
11
votes
1answer
175 views
List ROW_OVERFLOW_DATA pages for a specific table
I'm attempting to get a list of pages for a table that has rows with ROW_OVERFLOW_DATA. I can get the list of allocated pages from the undocumented DMV, sys.db_db_database_page_allocations, however, ...
