Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
23 votes
1 answer
14k 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 ...
  • 333
39 votes
2 answers
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)[...
  • 78.6k
37 votes
2 answers
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 ...
10 votes
1 answer
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 ...
  • 354
6 votes
2 answers
798 views

Can a plan "age" reach zero?

I am reading SQL Server Execution plans by Grant Fritchey, He mentions: SQL Server does not keep execution plans in memory forever. They are slowly aged out of the system using an "age" formula ...
  • 1,487
40 votes
4 answers
20k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
  • 14.4k
13 votes
1 answer
1k views

how to simulate a low-memory state when running a query

I am trying to better understand how SQL Server's execution engine behaves in a low-memory state. Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be ...
4 votes
1 answer
2k views

Why does a JSON element return text when it's not text?

When I try to access a JSON element using ->>'elementName' I get a texttype. SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2) FROM ( VALUES ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2); ...
  • 57.2k
26 votes
2 answers
39k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
  • 1,962
26 votes
1 answer
1k views

Logical reads different when accessing the same LOB data

Here are three simple tests that read the same data, yet report very different logical reads: Setup The following script creates a test table with 100 identical rows, each containing an xml column ...
  • 73.8k
28 votes
1 answer
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 ...
  • 2,303
6 votes
3 answers
7k views

What is the significance of the trailing dollar sign `$` on identifiers in SQL Server?

Dumping some internal views, such as sys.system_objects as done in this question I see that some identifiers end with a dollar-sign $ CREATE VIEW sys.system_objects AS -- cutout FROM sys....
  • 57.2k
3 votes
1 answer
3k views

How does Postgres make its B-tree index?

I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size. PostgreSQL manual about the index here ...
14 votes
3 answers
2k views

Concatenation Physical Operation: Does it guarantee order of execution?

In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like: select 'A' as c union all select 'B' Could return two rows in any order (although, in practice ...
13 votes
2 answers
4k views

Database for efficient range aggregate queries?

As a simplified example, suppose I have a table like this: seq | value ----+------ 102 | 11954 211 | 43292 278 | 19222 499 | 3843 The table may contain hundreds of millions of records, and I need ...
  • 231
11 votes
2 answers
13k views

Why disabling a clustered index makes the table inaccessible?

When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
2 votes
1 answer
511 views

What differences are there with SQL Server 2017's packaging of `Binn` on Linux and Windows?

Knowing that the Binn is packaged differently on Windows and Linux, I'm wondering what differences there are between the two? Do they come with different utilities? On Linux Binn is distributed in a ...
  • 57.2k
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)?
  • 35
21 votes
1 answer
5k views

Hash keys probe and residual

Say, we have a query like this: select a.*,b.* from a join b on a.col1=b.col1 and len(a.col1)=10 Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the ...
  • 2,899
13 votes
1 answer
2k views

Slot Array and Total Page Size

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B This leaves (...
  • 1,437
2 votes
1 answer
709 views

Why is intarray's push `+` so much faster than array-to-element concatenation `||`?

When it comes to using arrays of ints, I always default to using intarray. But, now I'm wondering why is the default concatenation so much slower. This answer probably requires some understanding of ...
  • 57.2k
13 votes
2 answers
25k views

Why does MySQL allow HAVING to use SELECT aliases?

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way: FROM WHERE GROUP BY HAVING SELECT ORDER BY ...
  • 353
20 votes
1 answer
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 ...
  • 73.8k
7 votes
2 answers
4k 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 ...
  • 57.2k
9 votes
1 answer
3k 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 ...
  • 57.2k
9 votes
1 answer
842 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 ...
5 votes
1 answer
360 views

On SQL Server 2017 (Linux), where is the Resource Database persisted (located on disk)?

In my default install of SQL Server 2017 on Linux, under /var/opt/mssql/data I see the following databases, master.mdf - master Database - Records all the system-level information for an instance of ...
  • 57.2k
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 ...
  • 30.9k
11 votes
3 answers
16k views

Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

In this question I was writing a query using sys.sysobjects. However, one of the answers mentioned sys.system_objects. I'm just wondering what is the difference between these tables? sys.objects sys....
  • 57.2k
11 votes
4 answers
3k views

What is the maximum number of local variables that can participate in SET operation?

I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of ...
11 votes
1 answer
2k views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
  • 1,437
7 votes
2 answers
7k views

Why can a SQL Server table not have more than one IDENTITY column?

Why can a SQL Server table not have more than one identity column? CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY) Msg 2744, Level 16, State 2, Line 5 Multiple identity columns specified for ...
5 votes
1 answer
444 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 ...
  • 2,314
15 votes
1 answer
454 views

What allows SQL Server to trade an object name for a string passed to a system procedure

What causes it to be legal to pass an object name to the system stored procedure sp_helptext? What mechanism converts the object name to a string? e.g. -- works sp_helptext myproc sp_helptext [...
  • 651
11 votes
2 answers
4k views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
  • 1,731
10 votes
1 answer
5k 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 ...
  • 1,437
8 votes
2 answers
2k views

SQL Server 2014 compression and maximum row size

I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero). I know about 8060 bytes per row limitation, so i ...
  • 155
8 votes
1 answer
515 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 ...
3 votes
1 answer
2k views

How does SQL Server reuse reserved pages after deleting a table

I just want to know: How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1) How SQL Server reuses reserved space after truncating a table? (call this table T2) Both are ...
  • 1,728
3 votes
1 answer
209 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 ...
3 votes
1 answer
503 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 ...
2 votes
1 answer
408 views

How does the sys.objects Catalog View pull from itself? [duplicate]

I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this ...
  • 57.2k
1 vote
1 answer
1k 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/...
  • 57.2k
1 vote
1 answer
659 views

SQL Server Query Plan Hash Collision

I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query. The hash is a 16 digit Hex that sp_help ...
  • 303
1 vote
2 answers
512 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 ...
0 votes
1 answer
426 views

What determines if a bytea has 1 or 4 extra bytes?

When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length. The question that I can't seem to find the answer to is what ...
  • 335
-1 votes
2 answers
1k views

What internal functions are undocumented in SQL Server?

I just read about fn_dblog, and fn_dump_dblog. I was wondering how do I know what functions like this exist, I'm not the only one either, the comments are scattered with people wanting to know more, ...
  • 57.2k