Mysqld draining memory, using way more then assigned and not giving any back

Below is an analysis of what you have.  Nothing points to how it could be over-filling memory.  Please provide the evidence (top / htop / innodb_top / whatever) that shows mysqld larger than, say, 10GB.  Maybe there are some clues there.
Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.3.27-MariaDB-0+deb10u1
32 GB of RAM
Uptime = 13:59:47; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.  (I don't know if Aria is consuming a lot of space.)

The More Important Issues:
To free up some RAM:  key_buffer_size = 20M
The dataset seems to be quite small; innodb_buffer_pool_size (now 7,516,192,768) is probably bigger than necessary.
If you are using a SSD drive and are having I/O issues, some settings can be adjusted.
autocommit was on when you captured the GLOBAL STATUS.  If that is your normal mode of running things, do not forget to perform COMMIT.
You are using ENGINE=Aria a lot?  (My analysis does not cover much of that.)
Several clues indicate that some queries may need optimization.  Suggest turning the slowlog on with a small value for long_query_time to see which queries need the most help.
You are doing some things with InnoDB's FULLTEXT?  I see that the stopwords are turned off an the word length has been shrunk.  How is that going?
Details and other observations:
( innodb_buffer_pool_size ) = 7,168 / 32768M = 21.9% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 7 = 0.571 -- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 7)
(Beginning to go away in 10.5)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky.
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 100,551 / 458752 = 21.9% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 7516192768) is bigger than necessary?
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.
( innodb_log_files_in_group ) = 4 -- Number of InnoDB log files
-- 2 is probably the only reasonable value.
A large number may cause performance problems.
( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( Innodb_row_lock_waits ) = 10,785 / 50387 = 0.21 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.
( Innodb_row_lock_waits/Innodb_rows_inserted ) = 10,785/8926 = 120.8% -- Frequency of having to wait for a row.
( innodb_table_locks ) = innodb_table_locks = OFF -- Something to do with autocommit=0.
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Created_tmp_disk_tables / Created_tmp_tables ) = 9,073 / 14054 = 64.6% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456); improve indexes; avoid blobs, etc.
( Sort_merge_passes ) = 1,196,767 / 50387 = 24 /sec -- Heafty sorts
-- Increase sort_buffer_size (now 2097152) and/or optimize complex queries.
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.
( Connections ) = 1,584,168 / 50387 = 31 /sec -- Connections
-- use pooling?
( thread_cache_size / Max_used_connections ) = 151 / 101 = 149.5%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Abnormally small:
Acl_users = 4
Aria_pagecache_blocks_unused = 14,017
Handler_write = 0.19 /sec
Innodb_dblwr_pages_written = 0
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
eq_range_index_dive_limit = 0
innodb_buffer_pool_chunk_size = 128MB
innodb_ft_min_token_size = 2
innodb_max_undo_log_size = 10MB
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
innodb_spin_wait_delay = 4

Abnormally large:
Aria_pagecache_blocks_not_flushed = 1,688
Aria_pagecache_read_requests = 14019 /sec
Aria_pagecache_write_requests = 435 /sec
Aria_pagecache_writes = 435 /sec
Aria_transaction_log_syncs = 1,618
Com_show_slave_hosts = 0.21 /HR
Created_tmp_files = 3.8 /sec
Feature_fulltext = 22 /sec
Feature_subquery = 32 /sec
Handler_discover = 3.7 /HR
Handler_icp_attempts = 62394 /sec
Handler_icp_match = 62386 /sec
Handler_read_key = 56640 /sec
Handler_read_last = 3.4 /sec
Handler_read_next = 1378189 /sec
Handler_read_prev = 32923 /sec
Handler_read_rnd = 7044 /sec
Handler_tmp_write = 13198 /sec
Innodb_buffer_pool_read_requests = 2259173 /sec
Innodb_rows_read = 1870004 /sec
Innodb_secondary_index_triggered_cluster_reads = 210238 /sec
Rows_read = 1790121 /sec
Sort_priority_queue_sorts = 12 /sec
Sort_rows = 32610 /sec
Tc_log_page_size = 4,096
aria_sort_buffer_size = 256.0MB
innodb_commit_concurrency = 2
innodb_thread_concurrency = 24
max_heap_table_size = 256MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 256MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
tmp_memory_table_size = 256MB

Abnormal strings:
Innodb_have_snappy = ON
aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
ft_stopword_file =
innodb_data_home_dir = /var/lib/mysql
innodb_fast_shutdown = 1
innodb_ft_enable_stopword = OFF
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT


Checking space availability before a VACUUM FULL (postgres)

Firstly, I would suggest using pgstattuple to obtain tuple-level statistics.

  pgstattuple returns a relation's physical length, percentage of “dead”
  tuples, and other info. This may help users to determine whether
  vacuum is necessary or not.

For example:

create extension pgstattuple ;
create table my_table ( id int , name text);
insert into my_table select a, md5(a::text) from generate_series(1, 1e7)a;

-- size of my_table
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | my_table | table | postgres | 651 MB |

-- dead_tuple_percent = 0, pgstattuple will not lock your table
SELECT tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple('my_table');

 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         89.35 |                0 |                  0 |     338776 |         0.05

-- let update 50% rows
update my_table set name = name || id where id < 5000000;

-- now, dead_tuple_percent = 28.63%
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         60.43 |          4999999 |              28.63 |    1834236 |         0.17

-- size of my_table has increased

 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | my_table | table | postgres | 1016 MB |

-- try to vacuum full
vacuum full my_table;

-- after that, dead_tuple_percent = 0 and size of my_table has reduced
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         88.92 |                0 |                  0 |    1664780 |         0.23
 Schema |   Name   | Type  |  Owner   |    Size    | Description
--------+----------+-------+----------+------------+-------------
 public | my_table | table | postgres | 691 MB     |

Secondly, if you are in production environment, I would suggest using pg_repack to reclaim disk without locking your table.

  pg_repack is a PostgreSQL extension which lets you remove bloat from
  tables and indexes, and optionally restore the physical order of
  clustered indexes. Unlike CLUSTER and VACUUM FULL it works online,
  without holding an exclusive lock on the processed tables during
  processing. pg_repack is efficient to boot, with performance
  comparable to using CLUSTER directly.

For instance:

/usr/pgsql-11/bin/pg_repack -d postgres -U postgres -n -t my_table  &


There are few command line options you need to consider.

  pg_restore -x --no-owner --dbname=mynewdb --exit-on-error -Fc database.bak

-Fc: restore from a binary back up as its more flexible than plain text SQL dump
--dbname: restore under this database name (must be created beforehand using createdb)
-x: do not try to restore access priviledges (e.g. the superuser name may change between databases)
--no-owner: the database owner user is different on the system where restore is performed
--exit-on-error: If bad stuff happens, abort and don't flood your terminal with useless errors


How can I generate a consistent entity graph (say from 10 tables) from a database fetch

Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?

It depends on the RDBMS.
In SQL Server you can use a transaction with SNAPSHOT transaction isolation level to get a consistent view of the database across multiple queries.
The isolation level Oracle calls SERIALIZABLE does the same thing.

My concern is one of performance of joining a large number of entities together in a single query

This is a valid concern because most RDBMS engines only return tabular results from queries. The strategies to generate a single tabular result for a complex object graph can lead to large resultsets and complex queries.  This has always been a problem for ORMs.


Reasons for incremental backup on Progress OpenEdge

Time for the backup to run is a reason for doing incremental backup on Progress. My backup is running fast enough that I did not need to use this function and I'm still doing only full backups.

It also depends on your requirements. For example, if you have heavy financial transactions and you want to keep a backup every hour or something like that, you would need to do incremental (or real time).

But unless you have something forcing you to do incremental, I would do full backup, I find that easier to restore.

In terms of performance impact, if you have a fast disk array, I haven't seen much impact of doing a backup even during moderately heavy usage. Obviously it depends also on the size of your system. I'm talking about a 37Gb DB.


Can SQL Server Bulk Copy cause timeouts when querying the table

It definitely could.

It requires locks just like any other insert operation. If enough locks are taken it will escalate to a full table lock (assuming the table allows it). Any insert operation like this would block anything else that was trying to read the data, unless NOLOCK was specified for those queries (which I am not recommending here).

You can actually set up a trace to see what types of locks are being taken if you are interested. Just set up a Profiler trace that gets the "Locks: Acquired" and "Locks: Escalation", and then do a bulk insert.

Word of warning, Profiler can cause a hit to performance, so I would either run the trace on a dev server, or if you have to run it in production, export the script and execute it as a server-side trace.


Searching between dates on a CHAR type field

You can cast your date to perform where clause on it just like this.

WHERE CONVERT(DATETIME, MyStringDateField, 120) BETWEEN @DateParam1 AND @DateParam2

The last parameter in CONVERT is the date format. You must choose the one that match your text data of your date. You can find the list here

You must be aware that if a string does not match a date for a particular record, your query will fail with a message saying that the data XYZ cannot be converted to DATETIME type.


Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message

  From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

What is output of this query.

select serverproperty('IsIntegratedSecurityOnly')

It seems like authentication mode is Windows only. If above query returns 1. It means authentication mode is windows go to server . Right click on SQL Server select property and then click on security. Change authentication it to mixed mode by selecting radio button SQL Server and windows authentication mode and restart SQL Server service again.


Tempdb change settings and recommendation

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed.

  As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Now also make sure that the tempdb files are created such that you don't need auto growth and that they are all the same size with the same growth parameters not in percentiles.

Your tempdb is now ~140 GB so that is about the size you need to provision for. Create 8x15-20 GB datafiles and a single log file (as transaction logs are used sequentially) set all the files with exactly the same growth parameters, something that makes sense on your storage 512mb is fine if you have instant file allocation. As your transaction log is just about 5GB create a single 6-8GB file for the transaction log and set autogrowth to be in MB. Log growth is not affected by instant file allocations so make that parameter smaller than the one on the database files.

If you still see contention then go for 16x 8-10 GB files and dont change the transaction log parameters


replace user defined functions (UDF) and tabled value functions(TVF)

No easy way unless you are on SQL2017 for table valued functions and not UDFs.
However on SQL 2019 scalar UDFs May perform little better but there are still lots of catches involved
By default parallelism is inhibited while using the UDFs within code, mind you the function part itself can go parallel though.
Your best part if not on latest sql server version would be to inline the function. That is simply copying the work of function inside your code rather than calling function from the code.


SQL Server 2012 Always On, during a massive DB change

If the database has a long scheduled maintenance you don't need high availability or do you?
Keep in mind that every change is written to the transaction log and than transmitted to the secondaries. I wouldn't use the full recovery model for such a maintenance. If you don't do such a thing each week/month, I would turn off HADR for these databases, change the recovery model and set up the AG afterwards again. Transfering the complete backup after the changes is a lot faster than transfering each change. And the primary server has less work to do.


How to compress a non-shrinkable table with nvarchar(max) fields?

We added compression for LOBs in CCIs exactly for this scenario in SQL 2016+.  If you can't upgrade, then likely you should get more disk space for the server as the easiest fix.


Once a database is migrated to Hyperscale, it cannot be migrated
directly to a non-Hyperscale service tier. At present, the only way to
migrate a database from Hyperscale to non-Hyperscale is to
export/import using a BACPAC file or other data movement technologies
(Bulk Copy, Azure Data Factory, Azure Databricks, SSIS, etc.)

Source is here.
Database copy for Hyperscale is now in Public preview as announced here on Sep 22, 2020.


Online backups with VENDOR library: what should be set for ACTIVE LOG PATH?

The active log path (database configuration parameter LOGPATH) for a database MUST be a locally accessible directory on the database server.  Preferably one with very good read/write performance.  (I'd say it must be a local directory, but technically you can specify the path on an NFS mount).

When DB2 fills a transaction log file (and all transactions contained within have been committed), the DB2 log archiver process will copy the file according to what you've set in the LOGARCHMETH1 and LOGARCHMETH2 configuration parameters.


How to clone a user in SQL Server 2008 R2?

Note : The below script doesn't actually set the permissions on anything, it just creates the script that can be copied and pasted to a new query, which can then be edited before being executed.

Below script will help you copy/clone permissions of one user to another:

--- To copy permissions of one user/role to another user/role.

USE database_name -- Use the database from which you want to extract the permissions
GO

SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname

SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from
SET @NewUser = 'userNEW'  --The user or role to which to copy the permissions to

SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT  '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC

SELECT  CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE   usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT  CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE   usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC


Try resetting the local agent:

> emctl stop agent

> emctl clearstate agent

> emctl start agent


Looking for a set-based solution for a rolling 30-day period

I tried to put together an approach that would work on 2008 and is more set-based.  Here is what I came up with.

It did end up being more complex than I had hoped, but it might be an interesting approach for you to benchmark against your current approach on larger data sets.  For what it's worth, this script runs in about 15ms for the provided data set on my machine (vs. 75ms for the original script).

As others have mentioned, there are probably other better approaches if you were able to use window functions in 2012+ or maybe a natively compiled procedure in 2014.  But it can be fun to think about how to do things without the newer features sometimes!

http://sqlfiddle.com/#!3/ad2be/7

-- Assign each point a sequential rank within each agent's history
SELECT p.internalID, ROW_NUMBER() OVER (PARTITION BY internalID ORDER BY date) AS recordRank, date
INTO #orderedPoints
FROM points p
-- Sort the data for efficient lookup of potential incentives
ALTER TABLE #orderedPoints
ADD UNIQUE CLUSTERED (internalId, recordRank)

-- Identify a potential incentive for any point that has 9+ points in the following 30 days
SELECT s.internalId, s.recordRank, ROW_NUMBER() OVER (PARTITION BY s.internalId ORDER BY s.recordRank) AS potentialIncentiveRank
INTO #potentialIncentives
FROM #orderedPoints s
JOIN #orderedPoints e
    ON e.internalId = s.internalId
    AND e.recordRank = s.recordRank + 9
    AND e.date < DATEADD(dd, 30, s.date)
-- Sort the data to allow for efficient retrieval of subsequent incentives
ALTER TABLE #potentialIncentives
ADD UNIQUE CLUSTERED (internalId, recordRank)

-- A table to hold the incentives achieved
CREATE TABLE #incentives (internalId INT NOT NULL, recordRank INT NOT NULL)
-- A couple transient tables to hold the current "fringe" of incentives that were just inserted
CREATE TABLE #newlyProcessedIncentives (internalId INT NOT NULL, recordRank INT NOT NULL)
CREATE TABLE #newlyProcessedIncentives_forFromClause (internalId INT NOT NULL, recordRank INT NOT NULL)

-- Identify the first incentive for each agent
-- Note that TOP clauses and aggregate functions are not allowed in the recursive portion of a CTE
-- If that were allowed, this could serve as the anchor of a recursive CTE and the loop below would be the recursive portion
INSERT INTO #incentives (internalId, recordRank)
OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
SELECT internalId, recordRank
FROM #potentialIncentives
WHERE potentialIncentiveRank = 1

-- Identify the next incentive for each agent, stopping when no further incentives are identified
WHILE EXISTS (SELECT TOP 1 * FROM #newlyProcessedIncentives)
BEGIN
    -- Transfer the most recently identified incentives, so that we can truncate the table to capture the next iteration of incentives
    TRUNCATE TABLE #newlyProcessedIncentives_forFromClause
    INSERT INTO #newlyProcessedIncentives_forFromClause (internalId, recordRank) SELECT internalId, recordRank FROM #newlyProcessedIncentives
    TRUNCATE TABLE #newlyProcessedIncentives

    -- Identify the next incentive for each agent
    INSERT INTO #incentives (internalId, recordRank)
    OUTPUT inserted.internalId, inserted.recordRank INTO #newlyProcessedIncentives (internalId, recordRank)
    SELECT nextIncentive.internalId, nextIncentive.recordRank
    FROM #newlyProcessedIncentives_forFromClause f
    CROSS APPLY (
        SELECT TOP 1 p.*
        FROM #potentialIncentives p
        WHERE p.internalId = f.internalId
            AND p.recordRank >= f.recordRank + 10 -- A new incentive can only start after all 10 points from the previous incentive
        ORDER BY p.recordRank
    ) nextIncentive
END

-- Present the final results in the same format as the original implementation
SELECT a.internalId, a.points, i.incentives
FROM (  -- Tabulate points
    SELECT internalId, MAX(recordRank) AS points
    FROM #orderedPoints
    GROUP BY internalId
) a
JOIN (  -- Tabulate incentives achieved
    SELECT internalId, COUNT(*) AS incentives
    FROM #incentives
    GROUP BY internalId
) i
    ON i.internalId = a.internalId


How are the system health extended events files rolling over?

How is the mechanism working in SQL Server of rolling over the files

The files will roll over when any of the following occurs:

the file reaches its max size (in this case, 5 MB)
the XE session is stopped and restarted for any reason

This could be someone actually running the command:
ALTER EVENT SESSION [session_name]
ON SERVER
STATE = STOP;

Or it could be that the server or SQL Server instance was restarted (this seems unlikely given you mentioned this is a critical production server).

and is there a way I can check when and how this is happening?

If all of your files are at or around 5 MB, your server is just generating a lot of events.  I have a production server that's not very busy, and it still takes less than 24 hours to fill up one of the 4 XE files with 5 MB of events.  Here's a screenshot, check out the modified dates on those files:

To see what events are included in the event session in SSMS, you can go to Management → Extended Events → Sessions → right-click "system_health" and choose "Script Session as" → "CREATE to" → "New Query Editor Window".
However, note this from the Microsoft docs article Use the system_health Session:

We recommend that you do not stop, alter, or delete the system health session.

So your best bet would be to create a new XE session with just the subset of events you want, and the specific Event File Target settings that work for your system.
If the files are less than 5 MB, then the server or event session is being restarted.  You can check the SQL Server error log for when restarts are happening, and you'll have to check with those who have CONTROL SERVER permissions to see who is stopping the XE session if that's the case.

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

It depends.  Querying (reading) from a bunch of different files can be inconvenient.  So I'd lean towards a lower total number of files, and just increasing the size of each one.
That being said, I've never tried parsing through a really large XE file, so there might be issues with that.  You'll have to test and see which one is better for your system.


What is the most compact way to calculate and then Update a maximum value for each day's data?

I am not entirely sure but if the aggregated results are supposed to be per date and the date is production.scheduled_pull_date, then perhaps this:

UPDATE
  pOut
SET
  pOut.lead_ppm = (
    SELECT
      MAX(p1.lead_ppm)
    FROM
      dbo.production AS p1
    WHERE
       (p1.tank = 'B' or p1.tank = 'C')
       AND p1.scheduled_pull_date = pOut.scheduled_pull_date
  ),
  pOut.sulfur_ppm = (
    SELECT
      MAX(p2.sulfur_ppm)
    FROM
      dbo.production AS p2
    WHERE
      (p2.tank = 'B' or p2.tank = 'C')
      AND p2.scheduled_pull_date = pOut.scheduled_pull_date
  )
FROM
  dbo.production AS pOut,
  dbo.assay_temp AS tOut
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1
  AND pOut.scheduled_pull_date = tOut.formatted_date
;

Although I would also rewrite the join using the contemporary syntax:

FROM
  dbo.production AS pOut
  INNER JOIN dbo.assay_temp AS tOut ON pOut.scheduled_pull_date = tOut.formatted_date
WHERE
  pOut.tank = 'S'
  AND pOut.cell_num = 1

The assay_temp doesn't seem needed in the subqueries at all, and instead of matching scheduled_pull_date against formatted_date the subqueries should correlate with the outer query. Since you want aggregations per date, it seemed to me the subqueries should match against pOut.scheduled_pull_date.


The main difference seems to be how each approach finds the row to be updated. The STATIC Cursor copies the full result set to a hidden temporary table first (hence why it is read-only), so it would seem to be less efficient to then have to re-query the main table for each UPDATE. However, the Positioned Update seems to have quite a bit more in Logical Reads and operations. One advantage of the Positioned Update, however, is noted in the MSDN page for UPDATE:

  CURRENT OF

  Specifies that the update is performed at the current position of the specified cursor.

  A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE  clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.

Test Setup

SET NOCOUNT ON;
-- DROP TABLE ##CursorTest;
CREATE TABLE ##CursorTest ([ID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
                           [Val] INT NOT NULL);
INSERT INTO ##CursorTest ([Val]) VALUES (1), (1), (1), (1);

Updateable CURSOR and WHERE CURRENT OF

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  KEYSET -- removing only reduces logical reads by 4
  SCROLL_LOCKS
  --OPTIMISTIC
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] < 5
  FOR UPDATE OF [Val];

DECLARE @ID INT;
OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE CURRENT OF curTest;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;

Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 1, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 1, logical reads 2

Removing the KEYSET option did reduce the logical reads by 4 (I believe), but that might not be a savings on a more complicated query, possibly with JOINs.

Also, switching SCROLL_LOCKS to be OPTIMISTIC increased the Logical Reads.

STATIC Cursor and standard UPDATE

UPDATE ##CursorTest SET [Val] = 1;
SELECT * FROM ##CursorTest;

SET STATISTICS IO ON;
DECLARE curTest CURSOR TYPE_WARNING
  LOCAL
  FORWARD_ONLY
  STATIC
  OPTIMISTIC
FOR
  SELECT [ID] FROM ##CursorTest WHERE [Val] < 5;

DECLARE @ID INT;

OPEN curTest;

FETCH NEXT
FROM  curTest
INTO  @ID;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE tmp
  SET    tmp.[Val] = tmp.[Val] + 2
  FROM   ##CursorTest tmp
  WHERE  tmp.[ID] = @ID;

  FETCH NEXT
  FROM  curTest
  INTO  @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
SET STATISTICS IO OFF;

SELECT * FROM ##CursorTest;

Results:

Table 'Worktable'. Scan count 0, logical reads 8
Table '##CursorTest'. Scan count 1, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2
Table '##CursorTest'. Scan count 0, logical reads 2
Table 'Worktable'. Scan count 0, logical reads 2

These simple tests seem to show the STATIC Cursor and regular UPDATE being the better option, and a more complicated query for the Cursor might be an even bigger difference (assuming you are able to update based on the Clustered Key of the target table).

But, if you have a situation where you can't narrow down to a individual row / have no Key value to use, then the Positioned Update would be quite handy.


Setting location of temp files for Postgresql backend (9.3.10)

There is a big problem with a_horse_with_no_name's solution. Not all instances of PostgreSQL even have a $PGDATA/base/pgsql_tmp because that entire path is actually created as needed in those instances. It doesn't even exist until needed, and it is destroyed when it is no longer required, so you can't create a symlink on an entire path that doesn't exist and that gets dynamically created then destroyed. You need to configue the temp tablespace location in the postgresql.conf file using the temp_tablespaces parameter (https://www.postgresql.org/docs/9.3/static/runtime-config-client.html#GUC-TEMP-TABLESPACES). This is because the temp_tablespace parameter not only designates the location of all temporary tables created by the CREATE command that do not have an explicit tablespace named at the time they are created, but also designates the location of all temporary files used for joins, sorts, and other overhead processing (https://www.postgresql.org/message-id/490ABE1D.3060700%40deriva.de).


Getting Max for partition column in clustered columnstore index

To address your second question first, it looks like SQL Server may not have access to that optimization or may not always apply it as we would like it to. I created a yearly partitioned heap with 9 million rows and ran the same query as you. It would be nice if SQL Server looped backwards through the partition function and stopped when it found data in a partition. However, the query optimizer did a full table scan instead. I think that it's much more common to put an index on a partitioned column, so it would be natural to attribute a fast SELECT MAX(PARTITIONED_COLUMN) query to a partitioning scheme instead of an underlying index. If it would help I can post my code.
To address the first question, is 7 seconds really that slow to process 800 million rows? Your query runs in parallel so you should get batch mode processing. However, because you are on SQL Server 2014 you will not get aggregate pushdown. Sunil has a blog post here which works through a similar example.

The picture below shows and aggregate query that processes 10 million rows and computes a single aggregate SUM of Quantity sold from the SALES table
SELECT SUM (Quantity) FROM  SALES
SQL Server 2014 scans these 10 million rows in batches (e.g. 900 rows) and sends these batches to Aggregate operator to compute the aggregate.  The picture below shows 10 million rows moving from SCAN node to the Aggregate node.
In SQL Server 2016, the aggregate operator itself is pushed to the SCAN node (i.e. closer to the source of the data) and the aggregate is computed there for compressed rowgroups. The picture below shows that 0 rows moved from SCAN node to the AGGREGATE node. This is because the aggregate was computed at the SCAN node.

For the COUNT(*) query, you are explicitly filtering on a single partition. I would expect SQL Server to use partition pruning to only get the relevant rowgroups. Overall it should be faster and require less resources than the MAX() query does because that query will hit all partitions.
I suspect that you asked about the MAX() query mostly out of curiosity. The easiest way to make that query go faster is to upgrade to SQL Server 2016. For something more immediate, you could take a look at your rowgroups and make sure that they're of a good size. If you have fewer rowgroups I would expect the query to go somewhat faster. Alternatively, you could write a function to find the maximum value in a way that takes advantage of partitioning on the table.


Clean up all but current backupset details

MSDB retains history of backups purely for informational purposes.  This history does not affect the ability to perform restores in any way.

To see backup history, I use a query like this:

DECLARE @DBName SYSNAME;
DECLARE @HistoryDays INT;
SET @HistoryDays = 7; --limit the display to backups taken in the past 7 days.
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history
--SET @DBName = 'some_db_name';
;WITH src AS
(
SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type
            WHEN 'D' THEN 'Database'
            WHEN 'L' THEN 'Log'
            ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name
                               ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.type = 'D'
    AND bs.backup_finish_date >= DATEADD(DAY, 1 - @HistoryDays, GETDATE())
)
SELECT src.DatabaseName
    , src.BackupStartDate
    , CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
    , src.ExpirationDate
    , src.BackupSetName
    , src.RecoveryModel
    , src.ServerName
    , src.BackupType
    , src.LogicalDeviceName
    , src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
     AND src.DatabaseName NOT IN (
          'master'
        , 'model'
        , 'msdb'
        , 'tempdb'
     )
ORDER BY src.BackupStartDate;

As you can see, I'm ordering the results by msdb.dbo.backup_set.backup_start_date -> the last row will be the most recently taken backup, which might or might not be the one I will restore in a disaster recovery effort.  As the old saying goes, "you can never have too many backups".  I may very well need to restore a backup from prior to the last backup for any number of reasons, including perhaps database corruption, user error, malicious intent, etc.  The concept of "current" backups does not really exist since the backup file may or may not exist at the time of restore.  If I take a backup now, to a disk that fails two minutes after the backup completes, what is the "current" backup?

You can use sp_delete_backuphistory to remove backup history from msdb:

DECLARE @oldestDate datetime;
SET @oldestDate = CONVERT(datetime, '2016-12-01T00:00:00');
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldestDate;

Modify @oldestDate to whatever time period you like.


In short, the answer to your questions is yes. However, I think you should bear in mind...

Try to get a version of Oracle XE that is the same as your company's main database.

You'll need to remember where your applications are configured to connect to and be careful to not inadvertently connect to a database you shouldn't, like the live or production database. Hopefully there will already be security in place to prevent you from doing this.

Also, keep in mind the limitations of XE:

  only use one CPU on a system
  use up to 1GB of RAM
  store up to 4GB of data
  11GB of data on 11.2

  The following database features are not supported:

  partitioned objects
  internal Java virtual machine (JVM)

I also wondered about possible licensing issues, but a quick Googling threw up these interesting posts...

Clarification on Oracle XE licensing - really free for any usage?

As well as the forum links in the above blog, I also found these:

oracle 10g XE in commercial enviroment

Oracle XE, is it really free for commercial use?


how to add key and UNIQUE KEY to existing table without check to speed up performance?

If I understand correctly, you want to achieve improved performance for the restore process of an SQL dump file. In that case, you can use do SET UNIQUE_CHECKS=0; before inserting the data and then SET UNIQUE_CHECKS=1; afterwards.

(mysqldump already does this automatically, at least for MariaDB.)

See also the documentation for bulk loading data.


Confusion with Tuple Relation Calculus Operations

∃s (student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)

For some s, s is a student & s is female & s is in d's department.

~∃s (student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)

For no s, s is a student & s is female & s is in d's department.

~∃x P is ∀x ~P. So ~∃x (Q ∧ R) is ∀x (~Q V ~R) is ∀x (Q → ~R). We get:

∀s (student(s) → (s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId))

For all s, if s is a student then either they are not female or their department is not d's.

For all students, either they are not female or their department is not d's.

For some reason you changed notation (but not meaning) & wrote that as:

∀s ∈ student(s) (s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId))

You don't explain what you think this means so it's not clear why you think it couldn't mean the same as the negated existential. But they mean the same thing.

When all the students are either non-female or not in d's department, the forall is true. But the negated existential is also true. Otherwise some student is female & in d's department, and the forall is false. But the negated existential is also false. (That "otherwise" clearly is the existential.)

  Now if it fails to find one, it returns FALSE and the negation will make it TRUE

You don't explain your notion of universal quantification failure & success. Your "fails to find one returns false" is for the existential. (Ie "finds one returns true".) But "fails to find all returns false" is for the universal. (Ie "finds all returns true".) (Not "fails to find one returns true" or "finds one returns false".)

When the existential fails to find a student that is female & in d's department--false, negated to true--the universal finds all students are either non-female or not in d's department--true. Otherwise, both expressions return false. They always return the same result.


How to use insert delay with the InnoDB engine and use less connection for insert statements?

Since you have more writes then reads, I would like to recommend the following
Decent Tuning of InnoDB would be the key
Buffer Pool (Sized by innodb_buffer_pool_size)
Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the InnoDB Buffer Pool. Transactional information for the Writes are written immediately to the Redo Logs (ib_logfile0, ib_logfile1). The writes that are posted in the Buffer Pool are periodically flushed from memory to disk via ibdata1 (InsertBuffer for Secondary Indexes, Double Write Buffer). The larger the Buffer Pool, the large the amount of INSERTs can be cached. In a system with 8GB or more of RAM, use 75-80% of the RAM as the innodb_buffer_pool_size. In a system with very little RAM, 25% (to accommodate the OS).
CAVEAT : You can set innodb_doublewrite to 0 to speed up writes even more, but at the risk of data integrity. You can also speed things up with setting innodb_flush_method to O_DIRECT to prevent caching InnoDB to the OS.
Redo Logs (Sized by innodb_log_file_size)
By default, the redo logs are named ib_logfile0 and ib_logfile1 and would be 5MB each. The size should be 25% of the innodb_buffer_pool_size. If the redo logs already exist, add the new setting in my.cnf, shutdown mysql, delete them, and restart mysql.
Log Buffer (Sized by innodb_log_buffer_size)
The log buffer holds changes in RAM before flushing them into the redo logs. The default is 8M. The larger the log buffer, the less the Disk I/O. Be careful with very large transactions, as this may slow down COMMITs by milliseconds.
Accessing Multiple CPUs
MySQL 5.5 and the MySQL 5.1 InnoDB Plugin have settings to have InnoDB Storage Engine access multiple CPUs. Here are the options you need to set:

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. It is usually recommended to set for this is (2 X Number of CPUs) + Number of Disks. Last year, I learned firsthand from the Percona NYC Conference that you should set this to 0 in order to alert the InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads (set this to 3000) and innodb_write_io_threads (set this to 7000) (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. Set these to 64. Also, set the innodb_io_capacity to 10000.

Upgrade to MySQL 5.5
If you have MySQL 5.0, upgrade to MySQL 5.5. If you have MySQL 5.1.37 or prior, upgrade to MySQL 5.5. If you have MySQL 5.1.38 or above and want to remain in MySQL 5.1, install the InnoDB Plugin. That way, you can take advantage of all the CPUs for InnoDB.


SQL Server: How to call stored procedure without schema name?

Best practice is always qualify schema for all object references. See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

But you can run this if you choose to ignore best practice:

ALTER SCHEMA dbo TRANSFER John.mySP_Name;


Percona Xtrabackup (Partial + incremental)

Why do you import the tablespaces? Strickly speaking you could use the data from step II, move it in the datadir, adjust the permissions and start MySQL. The procedure you using would make sense only if you want to restore db1 out of many other databases.  What is the error message?  Anything specific regarding the table that is failing?


There are options to help you in this:

  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.

Keep this paradigm in mind

mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using --replace (or --insert-ignore)
and --no-create-info into DUMP2
load DUMP2 into DB3


How to find which tables and views a user has access to?

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions...

 USE Your_Database;
 GO
 EXECUTE AS USER = N'the_user_name';
 GO
 SELECT
    s.name,
    o.name,
    p.[permission_name]
 FROM sys.objects AS o
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name)
   + N'.' + QUOTENAME(o.name), N'OBJECT') AS p
   WHERE o.[type] IN (N'U', N'V') -- tables and views
   AND p.subentity_name = N''; -- ignore column permissions
 GO
 REVERT;

Only tested on compat level 100+; you may need to structure that slightly differently in older compatibility modes (see the first example here).

Since your actual role has been granted a bunch of implicit access to tables through authorization, perhaps try:

-- all the tables and views in the system
SELECT
  schemaName = s.name,
  objectName = o.name,
  o.[object_id]
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE type IN (N'U',N'V')
AND NOT EXISTS
(
  -- except those that have been *explicitly* denied
  SELECT 1
  FROM sys.database_permissions AS p
  WHERE p.class = 1
  AND p.state_desc = N'DENY'
  AND p.major_id = o.[object_id]
  AND p.grantee_principal_id IN (USER_ID(N'db_finrep_deny'),USER_ID(N'finrep'))
);


You have to configure  using SSMS GUI :

Make sure you have error logs set to manageable size.

Check MSDN : Configure SQL Server Error Logs and kb 2199578 for managing SQL Server error log.

EDIT:

Below is what we do in our PROD environments:

Recycle error logs using master.sys.sp_cycle_errorlog with a SQL Agent job.
If you need more than 99 days of log (since you mentioned in your comment - The maximum is 99 which will be reached very soon), you can use PowerShell to move the files to a safe location. I would doubt that you would need error logs past 99 days ! Just make sure that you keep them in a manageable size, else you it would be difficult for log viewer to open very large files.


RMAN & Archivelog Deletion Policy confusion

Am a bit confused about what exactly is your question(s). Let me see if I can answer (at least) some part:

  Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.

I believe you are talking about DELETE INPUT part of the syntax of BACKUP command. This part tells oracle to delete the archived logs that exist at the time BACKUP command is run (as well as those generated during the execution of BACKUP command) ONLY AFTER BACKUP has successfully backed up the archived logs. Without this part, the archived logs are not deleted.

  Deleting input, or deleting input all, seems counterintuitive to me when the deletion policy is set to 7 times to match an overall backup policy of 7 days.

A deletion policy is used by Oracle to manage the space in flash recovery area. Oracle can delete archived logs in flash recovery area when the flash recovery area is full and there exist archived logs that satisfy deletion policy criteria i.e. if deletion policy criteria is set to 2 days and the flash recovery area gets full, say, on Thursday then oracle will automatically delete archived logs older than 2 days (i.e. before Wednesday) to free up the space.

  And what should the Archivelog deletion policy be if the goal is to have one week’s worth of backups available at all times?

That depends upon
a) How much data loss is the business/company is prepared to tolerate? &
b) What is the SLA (Service Level Agreement) time for recovery of database &
c) your backup strategy (hot/cold, full/incremental, image copy/backupset etc.)

Hope this helps.


Should numeric identifiers like serial numbers be stored as integers?

One of the biggest advantages to using an INT over say a CHAR or VARCHAR(10) is size.  The INT value will be 4 bytes and the CHAR/VARCHAR value will be 10 bytes.  NCHAR/NVARCHAR would be even larger at 20 bytes.  Because of this indexes on INT values are smaller and thus faster, sometimes considerably so, than a corrisponding CHAR/VARCHAR index.  Also your row sizes are smaller requiring less reads per row.  This is all aside from actual space storage of course.  Thomas LaRock has written several blogs on "right sizing" your data types.  Does this datatype make my column look fat and How to: Right sizing the datatypes currently in the buffer pool for example.

All of this of course assumes that you in fact will not be using characters in your
tracking codes.  If you are then of course you have no choice but to use CHAR\VARCHAR.


How to organise and understand a terribly modelled database

To add on to the first two comments on your post, yes unfortunately this is the case in a lot of Vendor systems. Reverse engineering, and normalizing their less than optimal schemas has been a fairly common task I've dealt with for multiple Vendor systems at multiple different companies I've worked for. There's a couple things I recommend to make it slightly easier on yourself either now or at least in the future:

Documenting your progression is important. As you find out the relations between entities, I'd recommend building out a database diagram for them, and then expanding on that diagram as you learn about other relevant entity relations. Of course in a large scale system, a single database diagram can become unwieldly, so you might want to break it up into multiple diagrams for each subject matter in the system. E.g. ERP systems typically have a multitude of modules that span different subject matters between Finance, Sales, Production, etc and in such a case I'd recommend at least a separate database diagram for each module.

Familiarize yourself with a schema search tool, such as the Find DB Object feature of the Oracle SQL Developer or the search features of PL SQL Developer as mentioned in this StackOverflow answer. (Microsoft SQL Server has the luxury of RedGate's SQL Search tool, but unfortunately I don't think RedGate offers an equivalent for Oracle in their tool belt.) This will be helpful in understanding all the places an entity is referenced and how it's used.

As much of a pain as it can be working with the Vendor and their documentation, if you have a technical contact who can at least answer the question if their application itself (or if they have a tool or database dictionary) that exposes the entity names of the fields behind the UI then that could make your job with reverse engineering their system a lot easier. Surprisingly this is fairly common practice that I've seen among many Vendor systems I've encountered. In some cases it was a dedicated shortcut key while focused on the field in the UI, sometimes it was a matter of right clicking the field and it was an option in the context menu, other times it was a separate tool bundled with their software (which was also used for customizations), and I've even seen it purely in the database in a dedicated schema / database dictionary. Also you may need to be a super user of sorts to be able to access that information. Either way, the Vendor should be able to answer that question for you.

Best of luck!


How can I design a query to pause / sleep / wait for a specified amount of time?

If you are looking for a function to block the execution batch for a specified amount of time, you can use WAITFOR in SQL Server 2000. This function is similar to SLEEP function in Oracle. Usage examples:

WAITFOR DELAY '00:00:30';     -- wait for 30 seconds
WAITFOR TIME '13:30';         -- wait until server time is 13:30


How to estimate/predict data size and index size of a table in MySQL

Given the table description, I see

66 bytes per row of data
4 bytes per row for the primary key
7 bytes per row for country code index

3 bytes for the country
4 bytes for Clustered Key attached to the country code

Total of 77 bytes of data and keys
This does not factoring housekeeping for BTREEs or Tablespace Fragmentation

For a million rows, that would 77,000,000 bytes (73.43 MB)

As for measuring the table, for a given table mydb.mytable, you can run this query

SELECT
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

To measure all tables grouped by Database and Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

Run these queries and you can track changes in database/engine disk usage.

Give it a Try !!!


Probably the error 1205 is not set up to be logged in ERRORLOG. Look it up in sys.messages:

SELECT *
FROM sys.messages
WHERE message_id = 1205;

If the column is_event_logged is set to 0, change it this way:

EXEC master.sys.sp_altermessage
    @message_id = 1205,
    @parameter = 'WITH_LOG',
    @parameter_value = 'true';

The documentation for sp_add_alert describes under which conditions an alert is raised:

  Remarks

  sp_add_alert must be run from the msdb database.

  These are the circumstances under which errors/messages generated by SQL Server and SQL Server applications are sent to the Windows application log and can therefore raise alerts:

  Severity 19 or higher sys.messages errors
  Any RAISERROR statement invoked with WITH LOG syntax
  Any sys.messages error modified or created using sp_altermessage
  Any event logged using xp_logevent


Bind variable vs. string-concatenated variable

You ask, referring to the variable l_field_name:

  Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing?

One way to look at this is to consider when and how the value of l_field_name is used.

The first (in terms of time) use is when you compose the string value that represents the INSERT statement. At that time the value of l_field_name is concatenated, along with other string literals and variable values, into the resultant string and becomes a part of it. The complete statement string is then passed to EXECUTE IMMEDIATE for parsing, compilation, and eventual execution.

The second use is when the value of l_field_name is assigned to the host (bind) variable :bv2 just prior to the statement execution. In this case that value is not concatenated or "injected" into the statement text: by this time the statement is already compiled into a plan, and :bv2 behaves like a variable it is -- it holds its value (that is now equal to the value of l_field_name) and passes it on to be inserted into the corresponding column.


1) InnoDB will go faster with indexes, too.

2) InnoDB with appropriate indexing is the best solution.

3) MyISAM has been slower than InnoDB for most workloads for well over a decade. There are fundamental difference in how memory and caching work between the two.

In this case InnoDB was picking the first row by primary key. Since InnoDB tables are clustered by primary key, this was very fast, and PK was likely already in memory from when you created the table.

Create an index in BidHigh and it'll go even faster.

Unless you have an overwhelmingly good reason to use MyISAM - you shouldn't. And if you think you have an overwhelmingly good reason, you should re-scrutinize it because they are vanishingly few in 2020.


Is the order of records in a VALUES list well-defined (postgres)?

VALUES is treated like SELECT. The SELECT documentation says:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Your version of Postgres happens to return the three rows in the order you have written them. While unlikely, this could change in the future.


Get all the users that are managed by a targeted manager

No simple select exists because MySQL does not support Recursive CTE queries like SQL Server.

This being the case, I wrote up some Stored Procedures to run iterative techniques for gathering children of a parent out of a hierarchical data design.

Here is my old post from Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs

I have three Stored Procedures

GetParentIDByID
GetAncestry
GetFamilyTree

In your particular case, you need to do two things

Change GetFamilyTree to gather all users from the user table under the target user
LEFT JOIN the result from GetFamilyTree with the customer table (I suggest LEFT JOIN rather than INNER JOIN in the event a user does not have customers, has not been fired, and it still in this hierarchy).


Restored Database is different from the original

You've used NOINIT in your backup, and then FILE = 1 in your restore. So it seems like you have multiple backups in the .bak file, and you aren't pulling the right one for the restore.  In fact you can see that in your RESTORE VERIFYONLY script you specify the FILE = @backupSetId but in the actual restore you just hard-code FILE = 1.

This is one of many reasons I don't like reusing a single file for multiple backups. What does it gain you? Not a whole lot, unless you are creating a gazillion backups every day and exceeding the capacity of file count by NTFS. I would much rather have a single .bak file with a timestamp in the name or something, then it is very easy to identify oldest or most current backups both visually and programmatically, restores are simpler, and it is also easier to purge expired backups.

For now, try a much simpler command with a unique filename and WITH INIT (I don't think you really need all of those other options)...

BACKUP DATABASE [BI_Staging]
  TO DISK = N'G:\BI_Staging_This_Time_For_Sure.bak' WITH INIT;

...and then restore that file.


Finding tables & columns at risk of hitting max value of int

As you mention auto-incrementing, it sounds like you want to check for sequence exhaustion - check_postgres has a sequence check which will warn about this.
Sequence metadata is stored separately from the associated tables, so is relatively easy to access without having to check each table individually. If you're using PostgreSQL 10 or later, the pg_sequences view provides a useful overview of the current state of all sequences. The default sequence naming makes it obvious which table/column the sequence is associated with (though it's also possible to create a query which returns the actual table/column name as well).


No other clean up should be required.  If you are really worried, you can watch your database size with:

select pg_database_size('your_database');

To make sure it shrinks by about the amount you expected.  It could take until the end of the next checkpoint to fully clean up.

If you were dropping tens of thousands of small tables is where you have you to worry about catalog bloat.  One large table is not an issue.


Why do I need DAC if I am able to connect to the server via sqlcmd?

Referencing Diagnostic Connection for Database Administrators with highlighting mine:

  SQL Server provides a special diagnostic connection for administrators
  when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to
  execute diagnostic queries and troubleshoot problems even when SQL
  Server is not responding to standard connection requests.


Is it possible: MySQL semisync and async replication on same master-slave pair?

Semisync Replication will switch to async on the fly if needed.

I mentioned this before in my old post MySQL Replication : 1 Slave / Multiple Masters

In that post I said

  Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

In another old post of mine, With MySQL Replication, what level of resilience is possible?, I mentioned the option that shows that semisynch can switch to asynch and back:

  rpl_semi_sync_master_wait_no_slave : With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.

Therefore, you cannot setup semisync and async replication on the same instance of MySQL. Then again, you don't need to since it is designed to toggle between them depending on response time or lack of response time.

BTW to setup Semisync Replication, the semisync plugin activated on Master and Slave. See my post Is MySQL Replication Affected by a High-Latency Interconnect? on how to install Semisync Replication.


Does MariaDB 5.5.56 have INFORMATION_SCHEMA.INNODB_METRICS?

Your version string indicate your client version is MariaDB. Making the assumption that your server is too and looking at the MariaDB kb INFORMATION_SCHEMA.INNODB_METRICS was added in 10.0.


Database design of Room Booking system in a Hotel

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question.

Each Customer can request for one or more Room.
Each Room can be requested by one Customer
Each Admin can assign one or many Room.
Each Room must be assigned by one Admin.
Each Room must be belongs to one Room Type.
Each Room Type can contain one or many Room.
Each Booking must be belongs to one Date.
Each Date can contain one or more Booking.
Each Room Type can contain one or many Rent.
Each Rent must belongs to one Room Type.
Each Booking must include one or many Room.
Each Room can be included in one Booking.
Each Customer can hold one or many Booking.
Each Booking must belongs to one Customer.
Each Rent can be included in one or many Bill.
Each Bill must include one Rent.
Each Bill Payment must include one Payment Type.
Each Payment Type can contain one or many Bill Payment.
Each Date can contain one or many Bill Payment.
Each Bill Payment must belongs to one Date.
Each Room can be rated one or many Room Rating.
Each Room Rating must belongs to one Room.
Each Rating can contain one or many Room Rating.
Each Room Rating must belongs to one Rating.

Based on the above rules we have designed the following data model.

This model is a subset of Hotel Booking system. After the normalization process (check my blog), you will have more detailed and complete model.
In the above model Room rent is calculated based on the room type such as Single Room, Doubles Room, Family Room or Meeting Hall rather than number of people in the room.
And Room rent is changed from time to time, so we have included the from date and to date to have history of room rents. Also customer can use many payment types such as credit card, debit card, cash payment, so we are included payment type entity.
This model is based on assumption. I hope this model will help you in someway.
Thank you.


How to select an item from a column only if none of the value in corresponding column is NULL

you can use NOT IN and a subselect

CREATE TABLE modelitem
    ([ltemNumber] varchar(7), [Cornponent] varchar(6), [Quantity] varchar(4))
;

INSERT INTO modelitem
    ([ltemNumber], [Cornponent], [Quantity])
VALUES
    ('Modell', 'Wheel', '19'),
    ('Modell', 'Bolt', '19'),
    ('Modell', 'Engine', NULL)
    ,('Modell', 'Engine', NULL),
    ('Model 2', 'Wheel', '19'),
    ('Model 2', 'Bolt', '19'),
    ('Model 2', 'Engine', '6'),
    ('Model 3', 'Wheel', '19'),
    ('Model 3', 'Bolt', '19'),
    ('Model 3', 'Engine', '6'),
    ('Mode14', 'Wheel', '19'),
    ('Mode14', 'Bolt', '19'),
    ('Mode14', 'Engine', '6')
;
GO

SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [ltemNumber] NOT IN (SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [Quantity] IS NULL)
GO

| ltemNumber |
| :--------- |
| Mode14     |
| Model 2    |
| Model 3    |

db<>fiddle here


How do you test for race conditions in a database?

I do it all the time with my T-SQL modules.

Essentially, all you need to do is run your modules from two or more connections in a loop for a couple of minutes. Typically, all potential problems are exposed in a few minutes, assuming you have a SQL Server box with decent CPUs.

I wrote a few examples here and here.


Efficiently storing query-affecting settings in a database

It needs mentioning that the Admin_Configuration table contains only
18 rows, hence it shouldn't affect performance.

Why shouldn't an 18 row table affect performance? Take a look at the estimated query plan for the original query that takes 4 seconds. The query optimizer is saying that the loop join has roughly the same cost as the scan. The scan is only 59% of the total cost. The query with just the scan finishes in two seconds. It just so happens that the costs line up well here, but SQL Server is telling you that there is some overhead to sending all of those rows through the join. It would be more typical to write the query like this:
SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY,
  (SELECT -1 * MAX(NumValue)
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )
  , getdate());

However, that won't help you. You'll still have the overhead of the join. You're also unlikely to get good cardinality estimates. I assume that some of your views have more complex logic than just joining to a single table. It could be important to get cardinality estimates based on the actual setting value instead of a magic number guess or an average of all of the values in the table. By that I mean that if you change your setting value from 30 to 3000 you probably want some of the query plans to change. With your current strategy that may not happen, unless you have a filtered statistics on the table for each row.
Use a view or table-valued function
If you would need to create an object per row to get good estimates, you might as well put your data into views or table-valued functions. They seem to have mostly the same performance characteristics for this problem so I'll just cover views. Let's say that you create a view that holds your settings instead of your table:
CREATE OR ALTER VIEW SETTINGS_VIEW AS
SELECT 'DaysBack' SettingName , 85 SettingValue
UNION ALL
SELECT 'Setting2', 0;

SQL Server won't create statistics on the view. Instead it'll look directly at the values. However, your cardinality estimates can still be off. For the data that I mocked up:

However, if I create a view for each setting:
CREATE OR ALTER VIEW DAYSBACK_SETTING_VIEW AS
SELECT 85 SettingValue;

And run this query:
SELECT OrderId
FROM SalesOrder as S
WHERE S.[OrderDate] > DATEADD(DAY
, (
    SELECT -1 * MAX(SettingValue)
    FROM DAYSBACK_SETTING_VIEW
)
, GETDATE());

I get a better estimate that's directly based on the value in the view:

The overhead of the join, which you think will be a problem, is still present.
Use a one-row view
Or, write a one row view with one column per setting as described in SQL Server: Variables, Parameters or Literals? Or… Constants? by Jared Ko:
CREATE OR ALTER VIEW dbo.Constants AS
SELECT
    [DaysBack] = CONVERT(integer, 85),
    [Setting2] = CONVERT(integer, 0),
    [Setting3] = CONVERT(char(6), 'Banana')
    ...

The query becomes:
SELECT
    SO.OrderId
FROM dbo.SalesOrder AS SO
JOIN dbo.Constants AS C
    ON SO.[OrderDate] > DATEADD(DAY, -C.DaysBack, GETDATE());

This gives a good estimate using the value in the view, without a join.
Use a scalar function
I almost never recommend this as an option, but you could consider using a scalar UDF in the query. Scalar UDFs are bad because they will force any query using the view to be fully run in serial, they can be executed per row instead of per query which can lead to poor performance, and the cardinality estimates from filtering against them are often based on magic number guesses and don't change with the input variables. However, your current query already runs serially and your current approach doesn't give good cardinality estimates. Consider the following function:
CREATE OR ALTER FUNCTION SETTINGS_UDF (@SettingName VARCHAR(100)) RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE @SettingName
        WHEN 'DaysBack' THEN 85
        WHEN 'Setting2' THEN 0
        ELSE NULL
    END;
END;

If I use that in the query there's no longer a join in the plan:

The brief testing that I did suggests that a simple function like this can be executed once per query instead of once per row, if you're careful. The numbers vary a bit, but here's what I see when I execute the three queries:
╔═══════════╦═══════════════╦═══════════════════╗
║   Test    ║ CPU Time (ms) ║ Elapsed Time (ms) ║
╠═══════════╬═══════════════╬═══════════════════╣
║ Hardcoded ║           422 ║               502 ║
║ UDF       ║           422 ║               476 ║
║ View      ║           906 ║               990 ║
╚═══════════╩═══════════════╩═══════════════════╝

In my experience, if your development framework is causing scalar UDFs to appear to be a good option then you probably need to change your framework if possible. I would start by validating that the two second performance degradation that you observed will actually be a problem. If it is, consider other solutions that don't require you to hardcode values into a set of views. That isn't really a typical thing to do.


Joining three tables with intermediate nullable option and filter condition

Theoretically this should work according your conditions:

SELECT
    *
FROM
    A
LEFT JOIN
    B
    ON B.id = A.b_id
LEFT JOIN
    C
    ON C.b_id = B.id
WHERE
    (B.id IS NULL) OR (C.active = 1);


Get most recent 100 records from the database

Based on the comments above, what you will need is something like this:

create table #topdates (ID int not null, TableName nvarchar(128), systimestamp smalldatetime)
exec sp_MSforeachtable @command1 = 'insert into #topdates select top 100 ID, ''?'' as TableName, systimestamp from ? order by systimestamp desc'
select top 100 * from #topdates
order by systimestamp
drop table #topdates

This will collate the top 100 results from each table into a temporary table, and then return the top 100 rows to you.

EDIT: Based on further information provided in the comments, it was established that the ID field might actually have a different name on every table.  The following SQL will list the top 100 last edited records in the entire database, across all tables, showing the Primary key value, the table name, and the systimestamp:

create table #keys (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128))
insert into #keys
SELECT cu.TABLE_NAME, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
create table #topdates (Primary_Key nvarchar(128), TableName nvarchar(128), systimestamp smalldatetime)
declare @table nvarchar(128), @Column nvarchar(128), @sql nvarchar(max)
DECLARE cur CURSOR FOR select * from #keys
OPEN cur
FETCH NEXT FROM cur INTO @table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
    select @sql = 'insert into #topdates select top 100 [' + @Column + '], ''' + @table + ''', systimestamp from [' + @table + '] order by 3 desc '
    exec sp_executesql @sql
    FETCH NEXT FROM cur INTO @table, @Column
 END
CLOSE cur
DEALLOCATE cur
select top 100 * from #topdates order by systimestamp desc, TableName, Primary_Key
drop table #keys
drop table #topdates

You can easily extend this to show the column name of the Primary Key column too.


Select Query takes more than a minute to retrieve data

Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on?
Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is occurring in your query in regards to the questions above:
1) The actual rows are way off from the estimated rows (because the sql optimizer has no good estimates on what is actually in your tables)
2) The IO from your query listed above is at least a magnitude higher than if you just inserted those same 10 fields into another table and ran the same query (if those 10 fields are no larger than your average across all those 99 fields)
Even if you don't use those other 89 fields (since your query looks to only select 10 fields), SQL can only read what is on the page -- all 99 fields. Think of it like this: You have a word doc all your data printed out on paper. All your records are striped in one long string; at the end of the each record (99 fields), you have some delimiter. Then it starts over again with the next record; again all in one long string printed across all these physical piece of paper. Now if you are wanting to read just 10 of those fields, you still have to look at that whole piece of paper and pull out just those 10 fields. This is exactly how SQL Server works. If those 10 fields make up 10% of the actual data length, you are wasting 90% of the paper in his massive book you have printed out. If you create a non-clustered index on just these 10 fields you will save a lot of reading (because now these sheets of paper you have printed out will be completely crammed full of good data and the overall size will be 10% the original book. You will of course want to index on something that limits the most (based on the criteria of your query) and then just throw the other fields into an includes. Also, if those 7 rows you mention in your other query are static values, I would recommend to even go further and do a filtered index on those 7 values OR a filtered index on one of your where criteria. That will shrink your index down even further.


Preserving existing logins during database refresh

Not sure how you're doing your DB refresh. A DB restore of a user database doesn't change the logins; that should only be an issue if you are replacing the master DB, which I wouldn't generally recommend.

However, if the SID of a login in prod doesn't match the SID of that same login in the source environment, then the users may not match up correctly; that's when you'd have to deal with "orphaned" users in your databases.

As you note, scripting out the users in one environment, and loading them to another, should ensure that the SIDs in both environments line up correctly. That said, unless you're doing this when first setting up a server, I wouldn't try this.

Microsoft has an article that helps you troubleshoot orphaned users for SQL Server 2014.

To find orphaned users, run the following:

USE <database_name>;
GO
sp_change_users_login @Action='Report';
GO

This will identify users tied to a login SID that doesn't exist.

You can fix these with the following statement:

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

This updates the login SID for the user, so it matches that of the login you specify.

NOTE: the above works (as best I recall) going back at least as far as SQL Server 2008. However, the process has changed for SQL Server 2016 & 2017. Here's a link to the SQL 2016 version of the page I linked to above.


CTEs don't allow you to add indexes, they behave similarly to views but they aren't a persistent object in the database so they can't have indexes (unlike views).

If the bottle neck is the JOIN operation you should resort to a temp table and add an index on the column you will be using to speed up the JOIN operation.


Returning true or false in a custom function

I found the error to have been my varchar

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar

which i changed to

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.


'Connection not found' error thrown at end of task, after the package has successfully used the connection it refers to as missing

I figured this out and documenting here in case anyone comes across this question with the same issue.

Within the Execute Package Task Editor I had chosen the Reference Type as External Reference and was linking to the .dtsx file within the file system. It seems that this option, when run directly from Visual Studio at least, wasn't cleaning up properly.

To fix, I changed the Reference Type to Project Reference and choose the required package from the list of package within the project. This ran 100%.

I have gone as far as setting this up on management studio, but to run from within Visual Studio, this fixed the issue.


If the slave server is offline, the rsync in the archive_command should fail (exit with a non-zero exit value), which means the master will keep retrying it until it succeeds.  It will not remove a pg_xlog file until it is successfully archived.  So it will still be there when the slave comes back on online.

Also, the || exit 0 at the end of your restore command is just wrong.  PostgreSQL will ask for files which do not exist, and has to be told they don't exist (by returning a non-zero exit code)


What is it called when you search the middle of a string instead of the beginning?

It's called an "un-anchored search pattern", and it looks like this in SQL.

foo LIKE '%bar%'

If you lack a % on either side, it is said that the search pattern anchors to the start or end of the string respectively. This lingo comes from the regex world.

foo LIKE 'bar%'

You would say, "the search pattern bar% anchored to the start of the string".

For comparison, a PCRE is anchored with ^ or $ tokens and it looks like ^bar or bar$. PCREs require explicit anchoring with tokens, whereas SQL LIKE statements are implicitly anchored and require explicit % to create an "un-anchored search pattern".

As a side note, you can index these types of expressions with trigrams using something like pg_trgm in PostgreSQL


Oracle 12 Startup upgrade throws error 443

We got to the bottom of this eventually!

It was an oracle group write (g+w) permission missing in the parent folder of the diagnostic_dest structure.  Why it gives an error 443 and not a simple write permission error, only the oracle engineers who created this enigmatic diagnostic_dest structure will know!


In SQL Server 2008R2 Will currently deployed applications still work if I turn on Mirroring without changing connection strings?

Yes, it'll be just fine and you'll fail over just fine, provided that the server that was the single server is online.  If that server is totally down then without the connection string change the apps won't know how to find the other instance.

In short yes it'll work fine until you can get the connection strings updated.


Unable to show binary logs how to remove logs from mysql?

When the folder with binary logs is full, mysqld will freeze everything that does a write to a MyISAM table (even temporary MyISAM tables). See my post "Site Offline" MySQL server failing to start and stop as to why. Anyway, mysqld will wait for free diskspace before continuing whatever operation it was doing. In this particular instance, you must go to the OS and delete the binary logs, but not all of them.

For example, if you defined

[mysqld]
datadir=/var/lib/mysql
log-bin=mysql-bin
log-error=error-log.log

go to the OS and run this

cd /var/lib/mysql
ls -l mysql-bin.*

You will see all the binary logs. Delete just the first 2 or 3 binary logs.

Log back into mysql and run SHOW BINARY LOGS;

It will eventually kick in when the free space is acknowledged by mysqld.

Then, you can run PURGE BINARY TO 'mysql-bin.xxxxxx';

You may also have to look into the error log or slow log. If they are big, truncate them like this:

cd /var/lib/mysql
echo -n > error-log.log

GIVE IT A TRY !!!


And the answer is, operator ineptitude :-)

Apparently, when the change is applied to the MV, the log entry gets purged. The update shows up in the MV. The reason I was looking in the log at all is that this MV is used to update another MV, and the change was not showing up there. That view has it's own issues which I will have to work out.  Thanks for looking!


Index for inequality on multiple columns (col1 >= 'const_1' AND col2 <= 'const_2')

Whether PostgreSQL will use one index or not depends (a lot!) on the actual values of your tables, the conditions on your queries, whether statistics are up-to-date for the values in the table, and your configuration settings. So... whatever comes next depends a lot on your actual data. If you can provide a better description of your actual values, I guess we'll be able to better help you.

Anyhow, a first guess:

I assume your table has a structure similar to this:

CREATE TABLE plane_tracks
(
    plane_track_id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITHOUT TIME ZONE NULL,
    some_pay_load TEXT
) ;

I populate the table with some simulated data:

-- Generate data for a few days (60.475 rows).
INSERT INTO
    plane_tracks (created_at, some_pay_load)
SELECT
    generate_series(timestamp '2016-12-25 00:00:00',
        timestamp '2016-12-31 23:59:00', interval '10 seconds'), 'payload' ;

I simulate that (about) half your rows are updated, and that changes "udpated_at":

-- Update randomly some of these data (just simulate updated_at)
UPDATE
    plane_tracks
SET
    updated_at = created_at + ((random()*10) || ' minutes')::interval
WHERE
    plane_track_id IN
    (SELECT plane_track_id FROM plane_tracks ORDER BY random() LIMIT 30000) ;

I make sure PostgreSQL has good statistics:

ANALYZE VERBOSE plane_tracks ;

If I use under these conditions an index on (created_at, updated_at) with your query:

CREATE INDEX
    plane_tracks_on_created_at_updated_at
    ON plane_tracks(created_at,updated_at);

The index IS really used:

explain analyze SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-29 08:00:00'
      AND updated_at <= '2016-12-30 00:00:00';

Bitmap Heap Scan on plane_tracks  (cost=890.34..1679.47 rows=12209 width=28) (actual time=4.611..5.194 rows=2834 loops=1)
  Recheck Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=22
  ->  Bitmap Index Scan on plane_tracks_on_created_at_updated_at  (cost=0.00..887.29 rows=12209 width=0) (actual time=4.596..4.596 rows=2834 loops=1)
        Index Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
Planning time: 0.147 ms
Execution time: 5.443 ms

The reason why the index is used: PostgreSQL thinks it will help it select 12.209 out of the 60.475, and this will take less time than a sequential scan. This is giving good selectivity.

If you just change your query, and you make the condition broader, see what happens:

explain analyze SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-26 08:00:00'   /* 26 instead of 29 */
      AND updated_at <= '2016-12-31 00:00:00';  /* 31 instead of 30 */

Seq Scan on plane_tracks  (cost=0.00..1963.12 rows=31066 width=28) (actual time=12.890..21.236 rows=19993 loops=1)
  Filter: ((created_at >= '2016-12-26 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-31 00:00:00'::timestamp without time zone))
  Rows Removed by Filter: 40482
Planning time: 0.086 ms
Execution time: 22.441 ms

The reason: PostgreSQL has decided that, if the number of rows returned is going to be much higher than before, it makes more sense to scan the whole table than to use the index. Using an index means: first check the index, then gather the data from the main table. The savings on data gathering (SELECT *) would not be compensated by the expense of also checking the index.

In practice: I'd probably have one ("reasonable") index, and let PostgreSQL decide whether it is worth using it or not. The way you use your index (one comparison >=, the other <=), I think the best suited should be:

CREATE INDEX
    plane_tracks_on_created_at_updated_at
    ON plane_tracks (created_at, updated_at DESC);

Anyhow, it's normally good to check a few variants. You could also try to use a GIST index:

CREATE INDEX
    plane_tracks_gist_idx
    ON plane_tracks USING   gist   (created_at, updated_at);

... and see if that helps. GIST indexes tend to be good when you're going to use ranges, but you need to try which combination fits best your use-case.

NOTEs:

My test were done on 9.6.1 on Mac OS X 10.12, with "out of the box" settings. If you perform the same tests with other settings, results might change.
The choice of sequential versus index scans depends not only on your data, but also on a number of PostgreSQL settings. You can actually force PostgreSQL to not use index scans, or set some cost parameters for random access so high that, in practice, the usage of indexes will be heavily restricted. Check the Query Planning documentation.
When you're not sure about which indices to use, you can start by creating (a few) more than you actually need, analyze the index usage statistics after the system has been running in practice for a period that you think is long enough, and remove the seldom used ones.


How to create a table in a database on a different server

You can easily achieve this using OPENROWSET.

Connect on SQL Server B;

SELECT *
INTO TABLE_B
FROM OPENROWSET('SQLOLEDB', 'server=ServerA;uid=Login;pwd=Password;database=Database_A',
'set nocount on
SELECT * FROM TABLE_A;
')


Is the concept of a clustered index in a DB design sensical when using SSDs?

Ask yourself another question: If the entire database is in memory and I never have to touch the disk, do I want to store my data in an ordered B-tree or do I want to store my data in an unordered heap?

The answer to this question will depend on your access pattern. On most cases your access requires single row look-up (ie. seeks) and range scans. These access patterns require a B-Tree, otherwise they are inefficient. Some other access patterns, common in DW and OLAP, are always doing aggregates over the entire table end-to-end always and they do no benefit from range scans. As you drill further other requirements come to light, like the speed of insert and allocation into a heap vs.  B-Tree may play a role for huge ETL transfer jobs. But most times the answer really boils down to one question: do you seek or range-scan? The overwhelming number of times the answer is YES. And therefore the overwhelming number of times the design requires a clustered index.

In other words: just because is cheap to read it from disk in random order does not imply that you can trash your TLBs and L2 lines in a 64Gb RAM scan bonanza...


Disadvantages of reorganizing all tables nightly

There are better ways - just reorganizing all indexes nightly can be quite wasteful. Why even bother reorganizing an index that is 12% fragmented? Why reorganize a 10GB index every night if it takes 30 minutes and you only reduce fragmentation by 2% or 3%? How much effort should you spend reorganizing an index that is largely or completely in memory anyway - sure you save some disk space, but you have to pull the index out of buffer to do so - is the space savings worth the performance hit that will cause?

There are free scripts out there that help make some of these decisions for you, and it's easy to override the defaults:

Ola Hallengren: http://ola.hallengren.com/
Michelle Ufford (@sqlfool): http://sqlfool.com/2011/06/index-defrag-script-v4-1/

SentryOne also has a tool (not free) called SQL Sentry which goes quite a bit further than these by offering full historical reporting, broad to granular rules and schedules on which indexes to reorganize/rebuild and when (from server-wide to individual index and even partition), support for concurrent operations (should your maintenance window be tight), drag-and-drop calendar for scheduling, and the ability to assess exactly how the work you're doing affects performance.


BM25 (full-text search) implementation in SQL Server

After a few days of fiddling around, I finally managed to get this done. Here is the code I ended up with, packed into a stored procedure. For this to work, I added another table, which just holds a document ID and its length in words.

ALTER PROCEDURE [dbo].[BuildIndexBM25]
-- default parameters K1=> [1.2 - 2.0], B => [0.0 - 1.0], Weight => 3 (e.g. titles only)
    @K1 FLOAT = 1.2,
    @B FLOAT = 0.75,
    @Weight FLOAT = 3
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @N FLOAT;
    DECLARE @AVGDL FLOAT;

    -- number of all documents
    SELECT @N = COUNT(DISTINCT DocumentID) FROM TF;

    -- average document length (in words)
    SELECT @AVGDL = AVG(Length) FROM DocumentLength;

    -- BM25 implementation
    -- result set: | DocumentA | DocumentB | BM25 |
    SELECT
        DL.ID AS DocumentA,
        BM.DocumentID AS DocumentB,
        BM.BM25 AS BM25
    FROM (
        SELECT ID FROM DocumentLength
    ) DL
    CROSS APPLY (
        SELECT
                Q.DocumentID,
                SUM(LOG((@N - X.Count + 0.5) / (X.Count + 0.5)) * (Q.Count * (@K1 + 1)) / (Q.Count + @K1 * (1 - @B + (@B * L.Length / @AVGDL)))) AS BM25
        FROM    TF D, TF Q, DF X, DocumentLength L
        WHERE   D.TermID = Q.TermID
            AND D.DocumentID = DL.ID
            AND Q.TermID = X.ID
            AND D.DocumentID = L.ID
            AND Q.Weight = @Weight
            AND D.Weight = @Weight
        GROUP BY Q.DocumentID
    ) BM
    WHERE   DL.ID != BM.DocumentID
END

I hope this answer helps anyone who wanted to implement a full-text search algorithm by themselves. The default algorithm in SQL Server 2008 R2 has only minor differences to this implementation.


Multi-thread Informatica connections to use different processors on target database server

When the "Max Degree of Parallelism" configuration in SQL Server (server properties > Advanced) to 0, the server will split up the connections to use as much of the server as it can.  With the way I set up the commands in Informatica, there are four separate processes.  When I tested on a 2 core server, it used two cores.  When I tested on a 4 core server, it used 4 cores.  Everything was running in parallel, or asynchronously.  It made the ETL process fly!


Postgresql 9.4: How to find out the stack level of nested function

There's a different, simpler, solution for this. Add an argument to function fA with a default value, for instance: called_from_b BOOLEAN DEFAULT FALSE. Then, provide that argument when calling fA from fB.

I understand that this doesn't directly answer your question, but it does provide a solution for the example usecase that you provide.