Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
21 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 ...
7
votes
0answers
134 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
39 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
193 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 ...
14
votes
1answer
369 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 ...
0
votes
0answers
27 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 ...
6
votes
1answer
118 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
146 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
19 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 ...
0
votes
0answers
19 views

Mysql 8.0.16 crash due to informatiin_schema.Keywords table

How to resolve issue of mysql 8.0.16 crash?mysql 8.0.16 crash many times and shown server logs due to invalid argument.and in error log query is select WORD from information_schema.KEYWORDS. how to ...
7
votes
2answers
144 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
25 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
56 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
63 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
754 views

Alwayson AG lease timeout and healthcheck timeout

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
145 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
255 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
1k 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
89 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
578 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
127 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
46 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
79 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
506 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
293 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 ...
6
votes
0answers
136 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 ...
2
votes
1answer
157 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
112 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
467 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
59 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
118 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 ...
30
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
205 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
125 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, ...
3
votes
2answers
64 views

PostgreSQL GiST compress skeleton when no compression required

I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
4
votes
1answer
293 views

How large is SQLites minimum data storage overhead and what generates them?

Motivation: In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
2
votes
1answer
145 views

Performance difference in accessing differrent columns in a Postgres Table

Created a table with 200 bigint column, 200 varchar column. (Postgres 10.4) create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint,....., int200 bigint, char1 varchar(255),......, ...
9
votes
1answer
398 views

Index Seek Operator Cost

For the AdventureWorks sample database query below: SELECT P.ProductID, CA.TransactionID FROM Production.Product AS P CROSS APPLY ( SELECT TOP (1) TH.TransactionID FROM ...
4
votes
2answers
1k views

In what cases are BLOB and TEXT stored in-line on InnoDB?

I've googled this and can't find it, you can see Rick James mention it here. ... BLOB and TEXT are not always stored separately The answer on What is the difference between MySQL VARCHAR and TEXT ...
4
votes
2answers
251 views

Advantages to column constraints over table constraints

Is there any advantages whatsoever, other than style to writing this, CREATE TABLE foo ( a int PRIMARY KEY, b int ); And then, CREATE TABLE bar ( a int REFERENCES foo, c int ); Over, ...
1
vote
1answer
110 views

Does a backup include dropped, hidden columns still in a table's physical structure?

If I have a table with an NVARCHAR(MAX) column and I drop it, the standard way to reclaim the space it used is to either: Rebuild the clustered index or Run DBCC CLEANTABLE If I didn't perform ...
3
votes
1answer
155 views

PostgreSQL priority between CTE and real table

Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which ...
2
votes
0answers
58 views

How is page data rearranged after clustered index is created?

Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?
8
votes
1answer
343 views

Why does a Set Returning Function (SRF) run slower in a FROM clause?

This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for ...
0
votes
1answer
60 views

Is file a basic form of database? [duplicate]

I’m having an interesting conversation with my friend who’s a developer. I’m a database admin. I say file is a basic form of database. I believe whatever that stores data can be called a database. He ...
1
vote
1answer
53 views

How can we foresee (and configure) B-Tree and R-Tree indexes width and depth in PostgreSQL? [closed]

The B-Tree and the R-Tree are two similar index structures that PostgreSQL offers in its implementation. Both structures can have at most m children per node and as other tree structures, children ...
7
votes
1answer
1k views

Internally, how does OpenRowset(TABLE …) work?

I see a lot of internal views, such as sys.syscomments making calls to CROSS APPLY OpenRowset(TABLE, oid). I am wondering how this function operates and what it's reading from. Let's look at one ...
3
votes
1answer
2k views

What is the significance of the principal_id in sys.schemas?

What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id? 1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas; 2> GO ...
2
votes
2answers
412 views

What is an “Obfuscation key” in SQL Server?

Jokes aside, what is an "Obfuscation key"? I'm just going off the docs. sys.sysclsobjs Exists in every database. Contains a row for each classified entity that shares the same common properties ...