**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 the same character as the problem spot. ERROR 1064 (42000) at line 36: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ull, @field_name)' **I have no reason to believe there is a syntax error** because the large majority of the time it succeeds, and it always succeeds on MySQL 5.5.19 and other versions. So all of these details might be red herrings. But, for what it's worth, that line in the statement is field_name = if(@field_name='None', null, @field_name); The question is whether this is a known bug, or a theoretically explainable behavior, and **not** what the syntax error is. Are there conditions in a database, or perhaps a file or filesystem, that could generate the same generic syntax error inconsistently but persistently like this?

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 maybe_new_rows.ID FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows WHERE NOT EXISTS ( SELECT 1 FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween WHERE maybe_new_rows.ID = halloween.ID ) OPTION (MAXDOP 1, QUERYTRACEON 7470); One possible plan shape includes a merge join and an eager spool. The eager spool operator is present to solve the Halloween Problem : On my machine, the above code executes in about 6900 ms. Repro code to create the tables is included at the bottom of the question. If I'm dissatisfied with performance I might try to load the rows to be inserted into a temp table instead of relying on the eager spool. Here's one possible implementation: DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE; CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE ( ID BIGINT, PRIMARY KEY (ID) ); INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK) SELECT maybe_new_rows.ID FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows WHERE NOT EXISTS ( SELECT 1 FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween WHERE maybe_new_rows.ID = halloween.ID ) OPTION (MAXDOP 1, QUERYTRACEON 7470); INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK) SELECT new_rows.ID FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows OPTION (MAXDOP 1); The new code executes in about 4400 ms. I can get actual plans and use Actual Time Statistics™ to examine where time is spent at the operator level. Note that asking for an actual plan adds significant overhead for these queries so totals will not match the previous results. ╔═════════════╦═════════════╦══════════════╗ ║ operator ║ first query ║ second query ║ ╠═════════════╬═════════════╬══════════════╣ ║ big scan ║ 1771 ║ 1744 ║ ║ little scan ║ 163 ║ 166 ║ ║ sort ║ 531 ║ 530 ║ ║ merge join ║ 709 ║ 669 ║ ║ spool ║ 3202 ║ N/A ║ ║ temp insert ║ N/A ║ 422 ║ ║ temp scan ║ N/A ║ 187 ║ ║ insert ║ 3122 ║ 1545 ║ ╚═════════════╩═════════════╩══════════════╝ The query plan with the eager spool seems to spend significantly more time on the insert and spool operators compared to the plan that uses the temp table. Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway? I believe I am looking for answers that focus on internals. I'm able to see how the call stacks are different but can't figure out the big picture. I am on SQL Server 2017 CU 11 in case someone wants to know. Here is code to populate the tables used in the above queries: DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR; CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR ( ID BIGINT NOT NULL, PRIMARY KEY (ID) ); INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK) SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2 CROSS JOIN master..spt_values t3 OPTION (MAXDOP 1); DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS; CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS ( ID BIGINT NOT NULL ); INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK) SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2;

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](https://link.springer.com/book/10.1007/978-1-4302-3226-1) , where the author explains internal record format (Figure 9-5, page 351): Given table:

sqlite> SELECT * FROM episodes ORDER BY id LIMIT 1;
id   season  name
---  ------  --------------------
0    1       Good News Bad News

Its internal record format is:

| 04 | 01 | 01 | 49 |   | 00 | 01 | Good News Bad News |

> "The header is 4 bytes long. The header size reflects this and itself is encoded as a single byte. The first type, corresponding to the id field, is a 1-byte signed integer. The second type, corresponding to the season field, is as well. The name type entry is an odd number, meaning it is a text value. Its size is therefore given by (49-13)/2=18 bytes." Specifically I'm curious about TEXT attribute, in the example above we have a string of length 18 characters. And the rule for TEXT in SQLite is as follows:

Type Value     Meaning   Length of Data
----------     -------   --------------------
N>13 and odd   TEXT      (N-13)/2

What hapenns though when the string is longer ? It'll get out of range of that one byte.

For an example rowstore table... CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL); There are a variety of different methods of retrieving table row counts from metadata in SQL Server - such as the below

SELECT SUM(rows) 
FROM sys.partitions
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(row_count) 
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('dbo.T') AND index_id <= 1;

SELECT SUM(rows)
FROM sys.sysindexes
WHERE id = object_id('dbo.T') AND indid <= 1;

SELECT OBJECTPROPERTYEX(object_id('dbo.T'), 'Cardinality')

The execution plans apparently show a variety of different objects being used - such as the below. * sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT * sysidxstats CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS * sysidxstats i CROSS APPLY OpenRowSet(TABLE INDEXPROP What is going on here? Does SQL Server really maintain this metadata in multiple places? If so which is the most reliable method?

Assume the following table:

DROP TABLE IF EXISTS person;
CREATE TABLE person (
    id   int unsigned auto_increment primary key,
    name varchar(255) not null,
    age  tinyint unsigned,
    key (age)
);

INSERT INTO person (name, age)
VALUES ('bob', 10), ('alice', 16), ('jack', 19), ('william', 20);

And the following query:

begin;
select * from person where age < 12 for update;
rollback;

Why does innodb lock range [12, 16] as reported by:

select LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA 
from performance_schema.data_locks;

TABLE,IX,GRANTED,
RECORD,X,GRANTED,"10, 1"
RECORD,X,GRANTED,"16, 2"
RECORD,"X,REC_NOT_GAP",GRANTED,1

Even there're new records inserted or modified between [12, 16], it will not cause phantom reads, right?

Are there two levels of constant folding, one in the conversion tree and one in the simplification phase? If I run the following query

SELECT 
    P.[ProductID]
FROM 
    Production.Product AS P
WHERE
    P.[ProductID] = 1 + 2 * 3
    OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);

In the conversion tree, I find one constant, so I would call that basic constant folding? like [Paul Holmes](https://github.com/paulholmes-london/SQL-Server-Examples/blob/master/Logical%20Trees%20Step-By-Step/Logical%20Trees%20Part%202%20-%20Overview%20of%20Trees%20and%20Operators.sql) says in this example but if I run this query from [Paul White](https://www.sql.kiwi/2012/04/query-optimizer-deep-dive-part-1/)

use AdventureWorks2016
begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2)
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran

and

begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        'd%'
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran

I don't see much difference between both in terms of conversion trees so the constant folding happened here and their simplified trees has the same operators? Are there different stages in constant folding? Where are they? what did I do wrong here, so I did not see it in the simplification phase?

We have a SQL Server 2019 CU18 where we discovered a strange issue with querystore. Normally the average size of the hourly logbackup is 40MB but as soon as we enable querystore the average size of the logbackup is 2.5GB. There are (according to querystore) 140.000 queries executed/hour. This is about 40 executions/second. This is the config of our querystore: ALTER DATABASE [db_name] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE ,CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45) ,DATA_FLUSH_INTERVAL_SECONDS = 900 ,MAX_STORAGE_SIZE_MB = 2048 ,INTERVAL_LENGTH_MINUTES = 30 ,SIZE_BASED_CLEANUP_MODE = AUTO ,QUERY_CAPTURE_MODE = AUTO ); When I open such a big logbackup file with fn_dump_dblog I see that multiple transactions happen in the same second. The transactions all have the name 'SwapPage'. |Operation |CONTEXT |AllocUnitId |Page ID |Transaction Name | |-----------------|-----------------------|-----------------|-------------|-----------------| |LOP_BEGIN_XACT |LCX_NULL |NULL |NULL |SwapPage | |LOP_INSYSXACT |LCX_INDEX_INTERIOR |72057594047692800|0001:00056321|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:000a871c|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:0000041b|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:0000041c|NULL | |LOP_FORMAT_PAGE |LCX_UNLINKED_REORG_PAGE|72057594047692800|0001:000a8715|NULL | |LOP_MODIFY_HEADER|LCX_UNLINKED_REORG_PAGE|72057594047692800|0001:000a8715|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:000a8715|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:000a871c|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:0000041c|NULL | |LOP_INSERT_ROWS |LCX_CLUSTERED |72057594047692800|0001:000a8715|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:000a8715|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:000a8715|NULL | |LOP_MODIFY_ROW |LCX_INDEX_INTERIOR |72057594047692800|0001:00056321|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:0000041b|NULL | |LOP_MODIFY_HEADER|LCX_HEAP |72057594047692800|0001:0000041b|NULL | |LOP_MIGRATE_LOCKS|LCX_NULL |NULL |0001:000a8715|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:000a8715|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:0000041c|NULL | |LOP_INSYSXACT |LCX_UNLINKED_REORG_PAGE|72057594047692800|0001:0000041b|NULL | |LOP_INSYSXACT |LCX_CLUSTERED |72057594047692800|0001:000a871c|NULL | |LOP_INSYSXACT |LCX_INDEX_INTERIOR |72057594047692800|0001:00056321|NULL | |LOP_COMMIT_XACT |LCX_NULL |NULL |NULL |NULL | The allocation unit points to plan_persist_runtime_stats. After a comment of Paul White I setup an Extended Event to capture query_store_index_rebuild_started and query_store_index_rebuild_finished. To my suprise querystore was doing index rebuilds. This are the results of this trace: |event |timestamp |current_size_kb| |----------------------------------|-----------------------|---------------| |query_store_index_rebuild_started |2024-12-05 07:51:10.353|874208 | |query_store_index_rebuild_finished|2024-12-05 07:52:29.073|868832 | |query_store_index_rebuild_started |2024-12-05 08:20:58.497|873504 | |query_store_index_rebuild_finished|2024-12-05 08:22:18.320|869152 | |query_store_index_rebuild_started |2024-12-05 08:36:03.147|874528 | |query_store_index_rebuild_finished|2024-12-05 08:37:19.670|869664 | |query_store_index_rebuild_started |2024-12-05 09:06:00.943|874336 | |query_store_index_rebuild_finished|2024-12-05 09:07:12.750|870304 | It looks like the index rebuild is started around 874MB, the max size of querystore is set to 2048. I also included the stacktrace of the query_store_index_rebuild_started event in the Extended Event. > sqllang!XeSqlPkg::CollectClientHostnameActionInvoke > sqllang!XeSqlPkg::CollectDatabaseIdActionInvoke > sqllang!XeSqlPkg::CollectDatabaseNameActionInvoke > sqllang!XeSqlPkg::CollectNtUsernameActionInvoke
> sqllang!XeSqlPkg::CollectSessionIdActionInvoke > sqllang!XeSqlPkg::CollectTSqlStack > sqllang!XeSqlPkg::CollectTSqlStackActionInvoke > qds!XeQdsPkg::query_store_index_rebuild_started::Publish
> qds!CDBQDS::ReclaimFreePages
qds!CDBQDS::DoSizeRetention
> qds!CDBQDS::ProcessQdsBackgroundTask
> qds!CQDSManager::AcquireGenericQdsDbAndProcess>
> qds!CDBQDS::ProcessQdsBackgroundTask
> sqldk!SOS_Task::Param::Execute
sqldk!SOS_Scheduler::RunTask
> sqldk!SOS_Scheduler::ProcessTasks
> sqldk!SchedulerManager::WorkerEntryPoint
> sqldk!SystemThreadDispatcher::ProcessWorker
> sqldk!SchedulerManager::ThreadEntryPoint
KERNEL32+0x17AC4
> ntdll+0x5A8C1 I had hoped to find what is triggering the index rebuild but no such luck. After some pointers of Zikato I added some extra querystore related events to my trace. This shows that the index rebuild only is triggered if a query_store_size_retention_cleanup_started event has occurred. No rebuild:
Rebuild:

Everytime the cleanup runs 0KB has been deleted but apparently a rebuild is needed.
What confuses me is the appearance of the cleanup event. I thought this would only be triggered when querystore reaches 90% of the max storage size.

Increasing the max size of the querystore doesn't make any difference. Did anybody experienced the same issue or can somebody explain what is happening? Other databases on the instance don't have this problem.

I have a long running bug where some larger queries, sometimes run much much longer due to being stuck on wait_even MessageQueueSend. The difference can be anything from <100% to 1000s percent when compared to optimal run time, which sometimes also happens, so this is a benchmark. The problem seems to not be related to query performance/optimization in itself. Earlier the query had several CTEs which I then removed. This decreased the happy-path processing from ~43s to ~23s, but it did not get rid of this problem. When it gets stuck it can be there for several minutes or more. The same query can run sometimes in 23s and sometimes in 15mins. I've digged into this topic a little, it is seems to be something related to IPC and shared memory of processes comprising the query - screenshot attached. MessageQueueSend wait event signifies that the parallel worker processes are waiting to send bytes to a shared message queue. But I'm lost when it comes to why that may happen - shared message queue is full? main process is busy and cannot collect data from queue? - and what can be done about it. I've tried adjusting postgresql.conf, and the common values related to performance are as follows: - max_connections = 20 - shared_buffers = 2GB - effective_cache_size = 6GB - maintenance_work_mem = 1GB - checkpoint_completion_target = 0.9 - wal_buffers = 16MB - default_statistics_target = 500 - random_page_cost = 1.1 - effective_io_concurrency = 200 - work_mem = 26214kB - huge_pages = off - min_wal_size = 4GB - max_wal_size = 16GB - max_worker_processes = 4 - max_parallel_workers_per_gather = 2 - max_parallel_workers = 4 - max_parallel_maintenance_workers = 2 **Tech stack:** - postgres v.16 on linux VM, 8gb of ram, 4 CPUs from cluster - queries are triggered on database by dbt v1.9 - db purpose: data warehouse, running daily several larger queries P.S. I've searched stack exchange posts, stack overflow posts, google, asked multiple LLMs and spent many hours trying to figure it out, although I'm a junior, so that's that.

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, SELECT Top (1) * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' The XML results (within the resource monitor tag): RESOURCE_MEMPHYSICAL_LOW 2 0 0 0 100789 0 In this article by Bob Dorr, shrinking signal from Memory Broker could create internal memory pressure and reflect in resource monitor notification with IndicatorsProcess = 2 (IDX_MEMPHYSICAL_LOW) as shown above. This article by Slava Oks shows another way of checking internal memory pressure with RING_BUFFER_SINGLE_PAGE_ALLOCATOR. I don't know much about this particular ring buffer because I never seen it in my environment. SELECT Top (1) * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SINGLE_PAGE_ALLOCATOR' The XML result this time: 477 477 31553 31553 ... My question is, which internal processes may be contributing to internal physical memory pressure that will turn on this indicator = 2 (IDX_MEMPHYSICAL_LOW)? What I see from resource monitor indication of internal physical memory pressure, these are among them: 1. Memory broker shrink operation (e.g. cache cleanup) which can be identified through memory broker ring buffer, 2. Max Server Memory setting change and, 3. Buffer pool stolen page > 75% (as I am not seeing it reflected from single page allocator ring buffer) But are there others, too? And is there any record/log to identify them through DMV or other mean?

### TL/DR; When looking at the first DCM page in a database (which documents which extents have been modified, so that the DIFF backup process doesn't have to scan the whole database for changes, but can hop to the extents to scan the pages which have been changed) where is the location of the next DCM page documented? I would have thought that it would be documented in the m_nextPage attribute of the page header. But upon checking this doesn't seem to be the case. ### Finding the DCM pages I was instructing our apprentice on what the differential backups contain and how the DBMS documents the changes in the individual pages and then in the consolidated DCM page. ***Reference:** Pages and extents architecture guide (Microsoft Learn | SQL)* I then started playing around with a very large database and set off the following commands:

DBCC TRACEON (3604);
DBCC PAGE ( 6, 1, 0, 3 );		-- File Header

This produced the following output:

PAGE: (1:0)


BUFFER:


BUF @0x000002CC7AC80780

bpage = 0x000002CC2D11E000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48700                       bstat = 0x9
blog = 0x15a                        bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D11E000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7019                    m_freeData = 3321
m_reservedCnt = 0                   m_lsn = (2501578:318516:1)          m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 1668847350
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED

After reading the outptut I noticed the information near the bottom and assumed (correctly) that the DCM page would be located in page number 6 (well, 7 actually, but...). Let's take a look:

DBCC PAGE ( 6, 1, 6, 0 );       -- 1. DCM Page

Yes, this seems to be the first DCM pages:

PAGE: (1:6)


BUFFER:


BUF @0x000002CC7AC80300

bpage = 0x000002CC2D112000          bhash = 0x0000000000000000          bpageno = (1:6)
bdbid = 6                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48800                       bstat = 0x9
blog = 0x7a7a7a7a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D112000

m_pageId = (1:6)                    m_headerVersion = 1                 m_type = 16
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (2501605:3132423:42)        m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -431908416
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DIFF_MAP: Header @0x000000B60C7FA064 Slot 0, Offset 96

status = 0x0                        

DIFF_MAP: Extent Alloc Status @0x000000B60C7FA0C2

(1:0)        - (1:24)       =     CHANGED                                
(1:32)       - (1:40)       = NOT CHANGED                                
(1:48)       -              =     CHANGED                                
(1:56)       - (1:80)       = NOT CHANGED                                
(1:88)       -              =     CHANGED                                
(1:96)       - (1:104)      = NOT CHANGED                                
(1:112)      - (1:128)      =     CHANGED                                
(1:136)      - (1:152)      = NOT CHANGED                                
(1:160)      - (1:168)      =     CHANGED                                
(1:176)      -              = NOT CHANGED                                
(1:184)      - (1:192)      =     CHANGED                                
(1:200)      - (1:208)      = NOT CHANGED   
...

However, finding the next DCM page was a trial and error. I added 4'096'000 pages to the 6 I already had (which is wrong, it should be only 512'000 pages. Thanks Martin Smith for pointing that out), but received a page number slightly higher than the next DCM page. The next DCM page could be found in the header information.

GAM (1:4089856) = ALLOCATED         SGAM (1:4089857) = NOT ALLOCATED    PFS (1:4092528) = 0x0   0_PCT_FULL
DIFF (1:4089862) = NOT CHANGED      ML (1:4089863) = NOT MIN_LOGGED

The next DCM page is not documented in the m_nextPage header information, so I am assuming it should be in the last bit of the first DCM page. ### Questions 1. Is my assumption correct that SQL Server detects the next DCM page as the last bit of the first DCM page? Or does SQL Server have other means of finding the second, third, fourth, etc. DCM pages? 2. Wouldn't it be more efficient to document the next DCM page in the m_nextPage header information of the first DCM page?

I have read several articles and blog posts describing that columnstore indexes store all fields redundantly, including the row IDs from the underlying rowstore table (either RID for heaps or clustered index keys for clustered tables). Some articles mention that within a columnstore, each data row gets its own unique identifier internal to the columnstore index, and a delete bitmap or delete buffer uses this identifier (rowgroup ID and row number within the rowgroup) to mark specific rows as deleted. However, these sources do not provide detailed explanations of how the columnstore index correlates an updated or deleted row in the rowstore table to the corresponding row in the columnstore index. If the process truly relies on the rowstore-based identifiers (like RID or clustered key), it seems impractical given that the columnstore index stores its own structure of data. References Reviewed: * https://aboutsqlserver.com/2019/10/10/sql-server-2016-2017-critical-columnstore-index-bug-has-been-fixed/ * https://aboutsqlserver.com/2017/11/28/splitting-and-merging-non-empty-partitions-in-columnstore-indexes/ * https://sqlserverfast.com/epr/table-update/ * https://aboutsqlserver.com/2013/10/15/sql-server-storage-engine-data-pages-and-data-rows/ * https://www.mssqltips.com/sqlservertip/7759/sql-server-columnstore-indexes-tuple-mover-background-process/ * https://www.sqlshack.com/sql-server-2017-columnstore-in-place-updates/ * http://www.nikoport.com/columnstore/ Could someone explain in detail the actual mechanism SQL Server uses to tie a row in the columnstore index to the corresponding row in the rowstore table for the purpose of correct and consistent updates and deletes, and how these operations are performed behind the scenes? I’ve tried using the DBCC PAGE command to inspect the data pages, but the only concrete information I’ve found is that the delete bitmap stores a unique 9-byte identifier for each row. I still don’t understand how these identifiers map back to the corresponding row in the original rowstore table or how the internal linking mechanism works for updates and deletes. Could someone provide a detailed explanation of this process?

we have recently changed the file server that is the repository (the place where the backups are stored) server . I noticed the hardware specially IO is much better backup take shorter to finish, depending on many things, however, restores still take the same time as before. this is not a complex question, I have only one question here: what is physical_block_size? when you run the following query on the msdb database: SELECT * FROM msdb.dbo.backupmediafamily BMF WITH(NOLOCK) order by 1 desc this is the result of above query and please notice how the physical_block_size changed from 4096 to 512 exactly when I decided to use our new server mentioned above for the destination of the backups:

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/system.certificates.sfp /opt/mssql/lib/system.netfx.sfp /opt/mssql/lib/system.sfp

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 DROP TABLE dbo.dept; CREATE TABLE dept(id INT, name NVARCHAR(20), address NVARCHAR(50)); GO INSERT INTO dept(id, name, address) VALUES (1, 'Dept 1', 'Sarjapur Road'); INSERT INTO dept(id, name, address) VALUES (2, 'Dept 2', 'Whitefield'); INSERT INTO dept(id, name, address) VALUES (3, 'Dept 3', 'Electronic City'); INSERT INTO dept(id, name, address) VALUES (4, 'Dept 4', 'Koramangala'); GO CREATE CLUSTERED INDEX cl ON dbo.dept(id); CREATE INDEX ncl ON dbo.dept(address); GO SELECT *, %%lockres%% lock, %%physloc%% physloc, sys.fn_PhysLocFormatter(%%physloc%%) formatted FROM dbo.dept WITH (NOLOCK, INDEX (cl)) SELECT *, %%lockres%% lock, %%physloc%% physloc, sys.fn_PhysLocFormatter(%%physloc%%) formatted FROM dbo.dept WITH (NOLOCK, INDEX (ncl)) And here is the result: +----+--------+-----------------+----------------+--------------------+------------+ | id | name | address | lock | physloc | formatted | +----+--------+-----------------+----------------+--------------------+------------+ | 1 | Dept 1 | Sarjapur Road | (de42f79bc795) | 0xB01F000004000000 | (4:8112:0) | | 2 | Dept 2 | Whitefield | (9d6bf8154a2a) | 0xB01F000004000100 | (4:8112:1) | | 3 | Dept 3 | Electronic City | (052c8c7d9727) | 0xB01F000004000200 | (4:8112:2) | | 4 | Dept 4 | Koramangala | (1a39e6095155) | 0xB01F000004000300 | (4:8112:3) | +----+--------+-----------------+----------------+--------------------+------------+ +----+--------+-----------------+----------------+--------------------+-----------+ | id | name | address | lock | physloc | formatted | +----+--------+-----------------+----------------+--------------------+-----------+ | 3 | Dept 3 | Electronic City | (b64f1cd4ff4f) | 0x1800000003000000 | (3:24:0) | | 4 | Dept 4 | Koramangala | (4471456166ef) | 0x1800000003000100 | (3:24:1) | | 1 | Dept 1 | Sarjapur Road | (7948805432b9) | 0x1800000003000200 | (3:24:2) | | 2 | Dept 2 | Whitefield | (584262fe5906) | 0x1800000003000300 | (3:24:3) | +----+--------+-----------------+----------------+--------------------+-----------+ As you can see, the lock and physloc for the two resultsets are very different. In the past, I always believed that these pseudo columns are revealing information about the dbo.dept table (the clustered index or the heap), since I'm selecting from it. But this testing seems proved that they are showing information about how the data is accessed. Is my understanding correct?

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:

UPDATE STATISTICS dbo.Votes
(
    _WA_Sys_00000002_0AD2A005
) 
WITH MAXDOP = 1;

SQL Server builds an internal statement:

SELECT 
    StatMan
    (
        [SC0], 
        [SB0000]
    ) 
    FROM 
    (
        SELECT TOP 100 PERCENT 
            [SC0], 
            step_direction([SC0]) over (order by NULL) AS [SB0000]  
        FROM 
        (
            SELECT [PostId] AS [SC0] 
            FROM [dbo].[Votes] 
                TABLESAMPLE SYSTEM (9.234204e-01 PERCENT) 
                WITH (READUNCOMMITTED) 
        ) AS _MS_UPDSTATS_TBL_HELPER 
        ORDER BY 
            [SC0], 
            [SB0000] 
    ) AS _MS_UPDSTATS_TBL  
OPTION (MAXDOP 1)

Where does the 9.234204e-01 PERCENT come from?

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 been set to 92GB (min memory is 0). The SQL Server service process has allocated all the 92GB.
In our monitoring we see that SQL Server always has approx 40GB of free memory (Memory Manager\Free Memory). In the error log I see that all 12 cores are used: > SQL Server detected 1 sockets with 12 cores per socket and 12 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing. All databases have their recovery interval set to 60 seconds. This are the results of running EXEC sp_PressureDetector @what_to_check = 'memory'; As soons as Memory Manager\Free Memory drops below 40GB the lazy writer kicks in to evict pages from the bufferpool so that the free memory goes back to 40GB. After rebooting the server or failover, the same happens again. Because there are 12 cores in one socket, SOFT-NUMA splits the 12 cores into 2 nodes of each 6 cores. I have a feeling that SQL Server is only using half of the memory due to the SOFT-NUMA but I can't prove this. As a test I also lowered max memory with 20GB, expecting that Memory Manager\Free Memory would drop with the same amount but it only dropped with 7GB. Some further digging around in the ring buffers showed me that there is 'pool level pressure'
The timestamps of these entries match the start of the lazywriter.
Does this mean that there is pressure on the bufferpool or how do I need to interpret this? Can somebody explain this strange behavior or point me in the right direction to troubleshoot this issue further?

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 int2 in t2 to align the word correctly?

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 corrupting? Are Innodb redo logs written atomically? If so, how is it implemented? AFAIK, neither

nor writing in sector size guarantees atomicity.

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 Diagram

Server ˂-- 1 : n --˃ Instance ˂-- 1 : n --˃ Database
   ˄                    ˄                      ˄
   |                    |                      |
   |                  1 : 1                    |
   |                    |                      |
   |                    ˅                      |
   +-- 1 : 1 --˃     Status        ˂-- 1 : 1 --+

Read as: ...a server can have multiple instances ...an instance can have multiple databases ...a server, an instance and a database can have a status ## Setup ### Status Table

CREATE TABLE [Status]
(
  StatusID int,
  StatusName char(20),
  );

### Status Data

INSERT INTO [Status] (StatusID, StatusName)
VALUES
(1,'Productive'),
(2,'Prod ACC'),
(3,'Prod APP'),
(4,'Test'),
(5,'Test ACC'),
(6,'Test APP'),
(7,'OFFLINE'),
(8,'Reserved'),
(9,'Decommisioned');

### Server Table

CREATE TABLE [Server]
(
  ServerID int,
  ServerName char(20),
  ServerStatusID int
  );

### Server Data

INSERT INTO [Server] (ServerID, ServerName, ServerStatusID)
VALUES
(1,'FirstServer',1),
(2,'SecondServer',2),
(3,'ThirdServer',5),
(4,'FourthServer',8),
(5,'FifthServer',8);

### Instance Table

CREATE TABLE [Instance]
(
  InstanceID int,
  ServerID int,
  InstanceName char(30),
  InstanceStatusID int
  );

### Instance Data

INSERT INTO [Instance] 
  (InstanceID, ServerID, InstanceName, InstanceStatusID)
VALUES
(1,1,'GENERAL',1),
(2,1,'TAXES',1),
(3,2,'GENERAL',9),
(4,2,'SOCIAL',2),
(5,3,'GENERAL',5),
(6,3,'FBI',8),
(7,5,'COMINGSOON',8);

### Database Table

CREATE TABLE [Database]
(
  DatabaseID int,
  InstanceID int,
  DatabaseName char(30),
  DatabaseStatusID int
  );

### Database Data

INSERT INTO [Database]
(DatabaseID, InstanceID, DatabaseName, DatabaseStatusID)
VALUES
(1,1,'master',1),
(2,1,'model',1),
(3,1,'msdb',1),
(4,1,'UserDB1',1),
(5,2,'master',1),
(6,2,'model',1),
(7,2,'msdb',1),
(8,2,'TaxesDB',1),
(9,4,'master',2),
(10,4,'model',2),
(11,4,'msdb',2),
(12,4,'HealthCareDB',2),
(13,5,'master',5),
(14,5,'model',5),
(15,5,'msdb',5),
(16,5,'GeneralUserDB',5),
(17,6,'master',8),
(18,6,'model',8),
(19,6,'msdb',8),
(20,6,'CriminalDB',8);

## SELECT Statement without Status table involved The initial SELECT statement involved simply joining the three tables: server, instance, database and was as follows:

-- Simple SELECT to get all information on Servers, Instances and Databases
-- The status of the server, instance or database is not returned
SELECT 
  ServerName, 
  InstanceName,
  DatabaseName 
  FROM [Server] as srv
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID;

### Results of 1. Statement **PLEASE OBSERVE THAT...** - there is a server without an instance and database - there is an instances without a database | ServerName | InstanceName | DatabaseName | | :----------|:------------|:------------| | FirstServer | GENERAL | master | | FirstServer | GENERAL | model | | FirstServer | GENERAL | msdb | | FirstServer | GENERAL | UserDB1 | | FirstServer | TAXES | master | | FirstServer | TAXES | model | | FirstServer | TAXES | msdb | | FirstServer | TAXES | TaxesDB | | SecondServer | GENERAL | *null* | | SecondServer | SOCIAL | master | | SecondServer | SOCIAL | model | | SecondServer | SOCIAL | msdb | | SecondServer | SOCIAL | HealthCareDB | | ThirdServer | GENERAL | master | | ThirdServer | GENERAL | model | | ThirdServer | GENERAL | msdb | | ThirdServer | GENERAL | GeneralUserDB | | ThirdServer | FBI | master | | ThirdServer | FBI | model | | ThirdServer | FBI | msdb | | ThirdServer | FBI | CriminalDB | | FourthServer | *null* | *null* | | FifthServer | COMINGSOON | *null* | ## SELECT Statement involving Status table In the next statement I decide to add the status to each element (server, instance, database) and JOINed each table with the Status table as follows:

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID
  ;

### Results of 2. Statement To my surprise the **server without an instance and database** and the **server with an instance but without a database** were no longer listed: | ServerName | StatusName | InstanceName | StatusName | DatabaseName | StatusName | | :----------|:----------|:------------|:----------|:------------|:----------| | FirstServer | Productive | GENERAL | Productive | master | Productive | | FirstServer | Productive | GENERAL | Productive | model | Productive | | FirstServer | Productive | GENERAL | Productive | msdb | Productive | | FirstServer | Productive | GENERAL | Productive | UserDB1 | Productive | | FirstServer | Productive | TAXES | Productive | master | Productive | | FirstServer | Productive | TAXES | Productive | model | Productive | | FirstServer | Productive | TAXES | Productive | msdb | Productive | | FirstServer | Productive | TAXES | Productive | TaxesDB | Productive | | SecondServer | Prod ACC | SOCIAL | Prod ACC | master | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | model | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | msdb | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | HealthCareDB | Prod ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | master | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | model | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | msdb | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | GeneralUserDB | Test ACC | | ThirdServer | Test ACC | FBI | Reserved | master | Reserved | | ThirdServer | Test ACC | FBI | Reserved | model | Reserved | | ThirdServer | Test ACC | FBI | Reserved | msdb | Reserved | | ThirdServer | Test ACC | FBI | Reserved | CriminalDB | Reserved | ## Findings / Solution After checking various options with a trial and error approach I found out that the JOIN on the Status table had to be changed to a LEFT JOIN to allow for the statement to display the **server without an instance or a database**, and to display **the instance without a database**:

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    LEFT JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID;

### Results of 3. Statement | ServerName | StatusName | InstanceName | StatusName | DatabaseName | StatusName | | :----------|:----------|:------------|:----------|:------------|:----------| | FirstServer | Productive | GENERAL | Productive | master | Productive | | FirstServer | Productive | GENERAL | Productive | model | Productive | | FirstServer | Productive | GENERAL | Productive | msdb | Productive | | FirstServer | Productive | GENERAL | Productive | UserDB1 | Productive | | FirstServer | Productive | TAXES | Productive | master | Productive | | FirstServer | Productive | TAXES | Productive | model | Productive | | FirstServer | Productive | TAXES | Productive | msdb | Productive | | FirstServer | Productive | TAXES | Productive | TaxesDB | Productive | | SecondServer | Prod ACC | GENERAL | Decommisioned | *null* | *null* | | SecondServer | Prod ACC | SOCIAL | Prod ACC | master | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | model | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | msdb | Prod ACC | | SecondServer | Prod ACC | SOCIAL | Prod ACC | HealthCareDB | Prod ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | master | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | model | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | msdb | Test ACC | | ThirdServer | Test ACC | GENERAL | Test ACC | GeneralUserDB | Test ACC | | ThirdServer | Test ACC | FBI | Reserved | master | Reserved | | ThirdServer | Test ACC | FBI | Reserved | model | Reserved | | ThirdServer | Test ACC | FBI | Reserved | msdb | Reserved | | ThirdServer | Test ACC | FBI | Reserved | CriminalDB | Reserved | | FourthServer | Reserved | *null* | *null* | *null* | *null* | | FifthServer | Reserved | COMINGSOON | Reserved | *null* | *null* | ## Reference Material Here a link to the dbfiddle to reproduce my findings. ## Question Why does SQL Server require a LEFT JOIN on the Status table for child items that do not exist and for the query to display these items?

Suppose database system inserts a row in table, will it use write() system call of host file system to write that particular row to table?