For technical questions about the internal workings of the database engine.
3
votes
1answer
49 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 ...
27
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 ...
0
votes
2answers
60 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
82 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
44 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 ...
3
votes
1answer
60 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 ...
0
votes
0answers
41 views
SQL Server locking question
Could you please clarify how SQL Server locking works internally during an index seek?
Lets look into such case. We have a table TEST that consists of three columns Col1, Col2, Col3. Col1 has a ...
2
votes
1answer
88 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),......, ...
8
votes
1answer
100 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 ...
2
votes
2answers
238 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
237 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
90 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
56 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
52 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
253 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
59 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
48 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
482 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
468 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
341 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 ...
2
votes
1answer
77 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 ...
5
votes
3answers
609 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
2k 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....
-3
votes
2answers
278 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
312 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
262 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 ...
-5
votes
2answers
52 views
Creating my own database [closed]
I need to create my own database. Which language is comfortable for it? C or Java or any other language? Please tell me step by step process in creating data base.
1
vote
2answers
135 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
246 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
215 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
160 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
151 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
531 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
166 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
129 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(-...
4
votes
3answers
1k 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
337 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
79 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
17 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
647 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
57 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
146 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
256 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 ...
17
votes
1answer
1k 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
579 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 ...
13
votes
2answers
516 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
55 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?
1
vote
2answers
250 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
458 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 ...
