localhost:5432 admin@test=# set track_functions to 'all';
SET
localhost:5432 admin@test=# SELECT pg_reload_conf();
+----------------+
| pg_reload_conf |
+----------------+
| t              |
+----------------+
(1 row)

localhost:5432 admin@test=# \c -
psql (15beta1 (Ubuntu 15~~devel~20220516.2230-1~803.git05a5a17.pgdg20.04+1), server 14.3 (Ubuntu 14.3-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "test" as user "admin".
localhost:5432 admin@test=# show track_functions ;
+-----------------+
| track_functions |
+-----------------+
| none            |
+-----------------+
(1 row)

After I reconnect, the track_function return to none. How to config set it to all all the time?

I am trying to change the MAX DOP value for my Azure Managed SQL which currently is set to ZERO. Here is the script I am running:

USE [my-sql-db] 
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =5 ; 

the script works fine and returns success, but then when I run

SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism'

I see the number is set to ZERO

and when I run select * from sys.dm_exec_query_memory_Grants I can see the value is set to 1.

but then when I check the DB settings in SSMS i can see the correct value of MAXDOP

I am wondering why do I get conflicting numbers when I check the DB Settings in SSMS vs when I checksys.configurations or sys.dm_exec_query_memory_Grants ?

Lets say I want to implement basic compression for my table. I know it can be done in two steps, for example:

1. alter table MYTABLE compress;
2. alter table MYTABLE move;

Is there a way to check that both steps were implemented? How do I know that data was compressed?

I'm using Oracle 19c if that matters.

Missing one or more rows that were not the target of update

When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits. ( https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide )

I want to get a better understanding of the above.

1. Missing one or more rows that were not the target of update - how does it matter if rows that were not the target of update are missed?

2. you might miss rows if another transaction is causing a page split - assuming another transaction that can cause page split is either an INSERT or UPDATE statement?

3. This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits. - can you explain the marked line in simpler words please.

4. Although not mentioned in the above paragraph, does page splits not result in reading the same row multiple times? For example - say a row is read and then a page split resulted in the row to go into the next page.

DBCC CHECKDB terminated abnormally due to error state 6

The error state 6 means process is killed or terminated by external process at least as far as my experience goes, this is not documented though. I have witnessed this and in my case this was due to a monitoring query running killing long running job. Please double, triple check is any user process/agent job/third part software (in some case) is killing the process.

Something like:

select extract(year from c.dt) as yr
     , extract(month from c.dt) as mt
     , count(1) as cnt
from subscriptions s
join calendar c
    on c.dt between coalesce(s.open_date, '1900-01-01') 
                and coalesce(s.close_date,'2099-01-01')
group by extract(year from c.dt)
       , extract(month from c.dt)
order by 1,2

We are interested in calendar dates that lies between open and close for each subscription. Since the interval is open in both ends (can both be null) we add a sufficiently small/large date that acts as beginning/end of time as a replacement.

From this new relation, we can apply an aggregate such as COUNT(1). In your example, you use COUNT(distinct client_id) so I assume that a client can have multiple subscriptions during a period of time.

https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=b2717fe4e369bdfe7102accb0c001fb1

Members of db_datareader can in fact read any table/view in any schema. Which is why it's often advised not to use this role if you need finegrained access control.

You could create a new 'common_read' role, and just assign your users/groups to that one - and make a more restrictive grant (ie. don't grant access to 'secret_schema')

To copy role-assignments:

-- execute this, copy the output to query window and execute
select concat('exec sp_addrole_member ''common_read'', ',rp.name,''';')
from sys.database_role_members rm
join sys.database_principals dp on (rm.role_principal_id = dp.principal_id)
join sys.database_principals rp ON (rm.member_principal_id = rp.principal_id)
where dp.name='db_datareader'

In a pinch, you can always DENY access to a schema. This will trump any GRANT, so create a role, assign the members from db_datareader and DENY access to 'secret_schema'. But it's sort of a backwards way of doing it as it will require managing role memberships twice.

What is the formula for calculating the maximum size of the buffer pool (data cache) for SQL Server (when there is only one instance of the machine).

Say the machine has 1 GB of physical memory, what is the maximum size of the buffer pool in such a scenario?

Assume max server memory is 0.

The question came about when reading about memory grants and I read that the resource semaphore can use 75% of the buffer pool for query memory grants. Then the natural question is well how is the buffer pool calculated.

Thanks

You need to cast from a string, not from a number: '1'::jsonb. This is because using '1' means that the result is actually a constant, whereas 1 is a runtime conversion.

to_jsonb(1) also works, but I think this uses a runtime conversion.

SELECT pg_typeof('1'::jsonb), jsonb_typeof('1'::jsonb);

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e60de76843bd07277dcbe651ab835411

I think you want something like this:

select                        -- show 
    c.Client_ID, 
    c.Effective_Date, 
    c.Term_Date
from                          -- all contracts
    contracts as c
where                         -- from clients
    not exists                -- without
    ( select 1 
      from contracts as ac    -- active contracts
      where ac.Term_Date is null
        and ac.Client_ID = c.Client_ID
    ) ;