Questions tagged [database-internals]

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

Filter by
Sorted by
Tagged with
8
votes
2answers
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 ...
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
159 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
138 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
1k 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
63 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
247 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 ...
13
votes
2answers
3k 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
624 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
2k 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
40 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?
4
votes
1answer
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); ...
0
votes
1answer
52 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
171 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
601 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
731 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
2k 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
67 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
3k 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
433 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
2k 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
50 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 ...
0
votes
1answer
86 views

LOAD DATA statement returns a syntax error a fraction of the time

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 ...
19
votes
1answer
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 ...
6
votes
1answer
942 views

Index Pages (page type 2)

I trying to understand page splits in SQL Server, reading WHAT IS A PAGE SPLIT? WHAT HAPPENS? WHY DOES IT HAPPEN? WHY WORRY? by Tony Rogerson CREATE TABLE mytest ( something_to_see_in_data ...
1
vote
1answer
703 views

How does Oracle close and unmount a database?

I was reading the Oracle 12c concepts manual. Then I realised that something is not "technically" explained. Below is an excerpt of paragraph 13.2.2.2: When a database is closed as part of a ...
3
votes
1answer
2k views

How do I convert between the transaction ID formats in fn_dblog() and sys.dm_tran_database_transactions?

fn_dblog() shows transaction IDs in this format: 0000:00049d43 And sys.dm_tran_database_transactions shows transaction IDs in this format: 9811233 I believe the above examples show the same ...
11
votes
4answers
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 ...
4
votes
1answer
119 views

What happens at the data page level when the record length changes

I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly. ...
14
votes
1answer
358 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 [...
4
votes
1answer
145 views

Boot page flag for cleanly shut down database

I have been watching the SQL Server: Advanced Corruption Recovery Techniques by Paul Randal on Pluralsights. During module 4, he discusses the fact that if you have a missing log file it is important ...
15
votes
4answers
2k views

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method: Introduce 2 new columns as C and D2....
7
votes
1answer
416 views

STATISTICS IO for parallel index scan

Assume that there is a table with clustered index create table [a_table] ([key] binary(900) unique clustered); and some data insert into [a_table] ([key]) select top (1000000) row_number() over (...
3
votes
1answer
90 views

What are system columns used for in PostgreSQL?

I was following a PostgreSQL tutorial and the author just mentioned that there are system columns in PostgreSQL but did not mention when and why to use them. Also, in PostgreSQL Documentation it ...
6
votes
2answers
720 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 ...
2
votes
2answers
92 views

PostgreSQL GiST for compressed entry type

I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have ...
4
votes
1answer
84 views

Where are filtered-index dependencies listed?

Given a table of the form... create table dbo.FilterIDXTest ( id int not null identity primary key clustered, _bigint bigint, _varchar10 varchar(10), _varchar20 varchar(20), ...
5
votes
1answer
459 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
8
votes
1answer
473 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 ...
2
votes
1answer
224 views

Plan cache behaviour

Can someone please explain me why the following is no getting cached use AdventureWorks2014 go DECLARE @id INT=43865 SELECT sp.[BusinessEntityID] , so.salesorderid FROM sales.salesorderheader so JOIN ...
7
votes
1answer
629 views

Is there a way to see how SQL Server 2012 defines new identities?

On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO? SAMPLE DATA -- ...
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 ...
4
votes
1answer
2k views

TempDB uses a lot of space in buffer pool

I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used: SELECT COUNT(*) AS ...
7
votes
1answer
6k 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 ...
12
votes
1answer
1k views

Where does SQL Server physically store the IDENTITY VALUE for a table?

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far. SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
3
votes
1answer
354 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
8
votes
1answer
956 views

Set statistics I/O for nested loops

Consider the below query: CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key @pagesize AS BIGINT = 25 AS SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM ...
11
votes
2answers
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)) ...
2
votes
1answer
472 views

Allocation Unit ID calculation is incorrect?

I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and m_objID....