We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
14
votes
1answer
5k 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 ...
34
votes
2answers
13k 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)[...
35
votes
1answer
21k 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 ...
8
votes
1answer
1k 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 ...
35
votes
4answers
15k 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 ...
6
votes
2answers
547 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 ...
3
votes
1answer
1k 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); ...
25
votes
2answers
33k 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 ...
26
votes
1answer
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 ...
27
votes
1answer
4k 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 ...
13
votes
1answer
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 ...
12
votes
3answers
1k 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 ...
11
votes
2answers
11k 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 ...
6
votes
3answers
2k 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....
2
votes
1answer
381 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 ...
21
votes
1answer
3k 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 ...
13
votes
1answer
1k 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 (...
11
votes
2answers
2k 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 ...
2
votes
1answer
486 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 ...
14
votes
2answers
20k 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 ...
9
votes
1answer
465 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
1answer
236 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 ...
3
votes
1answer
2k 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 ...
11
votes
4answers
2k 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
1answer
1k 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 ...
7
votes
1answer
4k 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 ...
6
votes
3answers
7k 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....
5
votes
1answer
178 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 ...
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 ...
13
votes
1answer
235 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 [...
11
votes
2answers
3k 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)) ...
10
votes
1answer
3k views

Get minimal logging when loading data into temporary tables

Even after reading The Data Loading Performance Guide , I am still unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get ...
8
votes
2answers
878 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 ...
8
votes
1answer
412 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
1answer
752 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 ...
2
votes
1answer
250 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 ...
2
votes
1answer
156 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 ...
1
vote
1answer
460 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
2answers
201 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 ...
1
vote
1answer
498 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 ...
-2
votes
2answers
544 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, ...