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
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 ...
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....
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....
-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, ...
2
votes
1answer
732 views

What is a SQL Server “lop”?

The tool ldfreader.exe has an option called -lopFilter -lopFilter Dump only records with these LOP. (short form: -l) But, I don't see anything explaining what a LOP is. What is known about "...
4
votes
1answer
391 views

Can I extend SQL Server with C++ or C?

So, I created a PE32+ DLL, in an attempt to load it into SQL Server, $ file example_dll.dll example_dll.dll: PE32+ executable (DLL) (console) x86-64, for MS Windows but when I try to compile it as ...
1
vote
2answers
271 views

How do I use SQL Server's “Hekaton”?

The SQL Server 2017 version on Linux comes packaged with a utility called mtlogreader.exe that is absent from the Windows distribution, MtLog Reader - Dump Hekaton MtLog files Usage: \\VBOXSVR\mssql\...
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 ...
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/...
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 ...
1
vote
2answers
214 views

Where can I find documentation on SQL Server internals?

I know, well I've been told, Microsoft has a per-dev doc system under MSDN. Specifically, I'm looking for docs on the internal format of the WAL log. Is this documented anywhere? Certainly third ...
1
vote
2answers
1k views

Where does SQL Server store the SA password and SQL Server password policy?

On Linux, where does SQL Server store the "SQL Server password policy" and the SA user's password? I get it, "password" isn't secure. ERROR: Unable to set system administrator password: Password ...
1
vote
2answers
462 views

Hash function and custom C type for PostgreSQL

I am creating a custom type for PostgreSQL. The type is as follow: typedef struct { unsigned long prefix; unsigned long long id; } CustomType; I have build all the in and out functions. ...
1
vote
1answer
412 views

What is the 131072 in the call to lo_open()

ByteA to BLOB conversions typically make a call to lo_open(), CREATE OR REPLACE FUNCTION make_lo(bytea) RETURNS oid AS $$ DECLARE loid oid; fd integer; bytes integer; BEGIN loid := lo_creat(-...
5
votes
3answers
3k views

Why does the optimizer choose nested loops over merge joins here?

I have 3 tables. #a is main one and two secondary tables, #b and #c. create table #a (a int not null, primary key (a asc)) ; create table #b (b int not null, primary key (b asc)) ; create table #c (c ...
3
votes
1answer
751 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
0answers
126 views

Hash Match Probe Residual [duplicate]

Experts, I am trying to learn more about the CONVERT_IMPLCIT and Probe Residual. Can someone explain to me why the following test script cause Probe Residual? My understanding is if both join ...
1
vote
0answers
19 views

What kind of object is being checked against the cache when a new query arrives? [duplicate]

After reading several resources, it's not clear to me what kind of object is being checked against SQL Server Plan Cache when a new query arrives. I see 3 possible options: The pure query text. The ...
6
votes
2answers
701 views

Contradiction detection

I am trying to learn Table Partitioning. However, I struggling to understand why SQL Server behaving stage for a simple query. Test dataset /* -------------------------------------------------- --...
3
votes
1answer
158 views

Why does NULL ordering impact a btree scan in PostgreSQL?

In this answer jjanes says that NULL ORDERING contrary to the index declaration ordering can force a quick scan. Turns out he's right. I want to know why that is though. If you a btree with nulls last,...
1
vote
1answer
377 views

SSRS Encryption Key backup location

I would like to know if there is any metadata table/registry entry where we can find the backup location of SSRS Encryption Key. I am working on a power shell report to display the Encryption Key ...
8
votes
1answer
413 views

Understanding IAM Pages: extent intervals

I'm reading Itzik's book "Querying Microsoft SQL Server 2012" as well as reading/watching different educational material on the internet. My intention is to have a useful understanding of how the ...
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 ...
7
votes
1answer
812 views

Bug in Rebuild on SQL Server 2016 and above?

Question Summary A fragmented Clustered Index is not performing well, even after a REBUILD of the index. If the index is REORGANIZED then performance increases for the given table/index. I am seeing ...
14
votes
2answers
952 views

In Search of FILESTREAM Insider Information

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows: Sharename FILESTREAM_SHARE ...
2
votes
1answer
84 views

If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?

If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?
4
votes
2answers
1k views

How does MySQL manage its memory with regards to indexes?

To start off, the reason I'm asking this, is because I feel I have a database that - according to my own estimates - should have been killing the disks with massive I/O, because of indexes not fitting ...
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 ...
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 ...
2
votes
1answer
126 views

Specificness of Tablespace

I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace. Lets say I have a system where a user will input a schedule of tasks that need to be performed....
2
votes
2answers
111 views

How does Postgres handle integer data of different sizes in the same column?

If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk? Will Postgres have an easier time doing queries and ...
5
votes
1answer
860 views

Does partitioning help with lookups and inserts/updates by primary key?

I'm using MariaDB 10.1 and the default InnoDB storage storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few million per month, and it's mostly ...
0
votes
1answer
58 views

When does a table get an oid?

I'm wondering when a table acquires an oid. Is it only when the transaction commits? Or does it have a hidden oid prior to commit? Especially in the context of a CREATE TABLE AS SELECT.
2
votes
1answer
175 views

Where can I find the algorithm used in Oracle Range Partitioning

I am new to database and would like to know where can I find the resource to study and understand about different algorithms used in table partitioning For example, what algorithm is used between ...
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 ...
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
2
votes
1answer
35 views

Retrieving page contents from SQL, Postgresql?

Data is stored over pages and not necessarily one row or one column or one attribute maps to one page. Is it possible to use some query to extract data from a specific page in a database environment?
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); ...
0
votes
1answer
47 views

In what file does SQL Server Store the configuration data?

Where does SQL Server 2014 store the configuration data? Such as the certificate references for SSL and the size limits and allocations?
2
votes
1answer
115 views

Cannot get LOB data from SQL Server transaction logs

I am trying to extract all information (Insert, Update and Delete Operations) from the transaction log file of SQL Server manually. For this work, I am using the undocumented functions fn_dblog and ...
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 ...
3
votes
1answer
455 views

How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys?

First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint. Is there any intelligent way that databases ...
5
votes
3answers
1k views

Union implemented with Hash Match operator

I was reviewing the SQL Server physical operators listed on TechNet (don't judge, you know you've done it) and read that the Hash Match physical operator is sometimes used to implement the UNION ...
4
votes
1answer
65 views

How much does an ordered query of an attribute costs in terms of operations when the attribute is a key or an attribute?

How much does the following query costs in terms of operations when the attribute is a key or an attribute ? SELECT Attribute FROM R WHERE Attribute = 50 ORDER BY Attribute We assume we ...
2
votes
1answer
1k views

Find Index size in sql server [duplicate]

The following two queries return different values for index size. SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, i.index_id AS IndexID, 8 * SUM(a.used_pages) AS 'Indexsize(KB)' ...
3
votes
1answer
227 views

Confused about maximum connections in PostgreSQL

I am building a web application and I using PostgreSQL as the database management system. The following page details the maximum connections setting: https://www.postgresql.org/docs/9.5/static/...
10
votes
3answers
3k views

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache than there is space available? This query would contain multiple joins, so the result set ...
11
votes
2answers
1k views

Why does nested loops join only support left joins?

In Craig Freedman's blog, Nested Loops Join, he explains why the nested loops join cannot support a right outer join: The problem is that we scan the inner table multiple times – once for each ...
0
votes
1answer
49 views

dbcc checktable will write in my dbcc page (x,9,3) for the field dbi_dbccLastKnownGood

If I run DBCC CHECKALLOC,DBCC CHECKCATALOG and DBCC CHECKTABLE in all objects it will update my internal page with the actual date in dbi_dbccLastKnownGood field? The main reason of the question is ...