how see parameter value on xml_deadlock_report

You cannot see that information in the deadlock report, because it is a parameterized query. However, maybe it helps you to use Brent Ozar's sp_blitzLock to troubleshoot your deadlocks. https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/

Edit:

You can only see what parameters are used to compile the query plan. However, that probably is not what you are looking for, because the query that caused the deadlock has most likely different parameters then the one used to compile the plan. If you want to, refer to this article:

https://www.mssqltips.com/sqlservertip/4992/how-to-find-compiled-parameter-values-for-sql-server-cached-plans/

In order to see the compiled values you probably can use Extended Events. I looked it up and found this article that explains how to do that:

https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/

Be aware that running Extended Events can have an impact on your server in terms of performance.

The issue with performance you are running into is probably parameter sniffing. Suddenly your Stored Procedure runs slow. There is already tons of stuff written about it. Refer to this article:

Why is my query suddenly slower than it was yesterday?


can i shrink log file in full recovery model

Shrinking a log file isn't generally a good idea unless you've had a large amount of unexpected log file growth - otherwise it'll only grow again, and a log file that grows can cause performance problems.

You can shrink a log file that's in any of the recovery models, but you won't always see the effect of it, as less of the file will be able to be shrunk - log records have to hang around until they have been backed up, transferred to other machines, and so on.

If you can put up with your log file being large, then that could be okay. Or try to shrink it to an okay size until it shrinks - maybe try every half hour until then? Once it can shrink, it will, and you can stop trying.


Remove both Special Characters and String Characters from output SQL Server

Try this code that uses a combination of SUBSTRING, PATINDEX, LEN, and REVERSE, to account for the possibility of 10-13 digit ISBN variability to return the exact ISBN with no trailing extras:

declare @myvar varchar(50)='C9781501132957 : (hardcover)'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar)))

...or perhaps differentiating between other numbers in the string from ones that are at least 10 digits in size, like maybe a date of 1987 included in the description (if this is even needed):

declare @myvar varchar(50)='C9781501132957 : (hardcover) 1987'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',reverse(@myvar)))

i.e.

Select DISTINCT t.processed 'Title',
substring((Select max(reconst) from isbn where bib# = t.bib#),2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar))) 'ISBN',    t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc


This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.

Auditing has been built into Oracle for many releases.  There is an article here which goes into some detail.
Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.

Check the system parameter AUDIT TRAIL, it sounds like you want the fine grained auditing available from 9i onwards so you can access the SQL_BIND and SQL_TEXT.

There is an SCN number so you can flashback to the state of the data when the change was made.

Keep in mind that there is a performance hit for all this. You must have the hardware and resources available if this is a production system.


I would expect the OS to be cognizant of already installed MySQL packages, but not the files under /var/lib/mysql. Sure, rpm and yum repository info gets reset with the OS. The best thing that I know of a mysql uninstall is the renaming of /etc/my.cnf.

What you could have have done was something like this:

STEP 01

service mysql stop
rpm -qa | grep i mysql

You might see the following output

MySQL-shared-compat-5.6.12-1.rhel5.x86_64
MySQL-devel-5.6.12-1.rhel5.x86_64
MySQL-client-5.6.12-1.rhel5.x86_64
MySQL-shared-5.6.12-1.rhel5.x86_64
MySQL-server-5.6.12-1.rhel5.x86_64
MySQL-python-1.2.3-0.3.c1.1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64

STEP 02

You would then rpm -e or yum uninstall all these packages.

That still won't nix the files for MySQL. What next ?

STEP 03

Move the old datadir aside:

chown -R root:root /var/lib/mysql
mv /var/lib/mysql /var/lib/mysql_old

Now, the old mysql data is out of the way.

If you did not do these things before installing MySQL, a new install of MySQL will not overwrite the previous /var/lib/mysql. In light of this, I would not expect any OS installations to do this either.

After the OS reinstall, you probably tried to rpm or yum install MySQL again. This won't shove aside /var/lib/mysql. If you don't want the old /var/lib/mysql, no worries. Just redo Steps 1-3,


Write differences between varchar and nvarchar

You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is NVARCHAR:

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';
INSERT dbo.t(c) SELECT 'នរៀ';
INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';
SELECT c FROM dbo.t WHERE c = N'រៀន';

Results:

c
----
??? -- not stored correctly
??? -- not stored correctly
រៀន -- stored correctly!

c
----
???
??? -- probably not expected, however all Unicode characters have been changed to ?

c
----
រៀន

For those on mobile devices or decrepit browsers that show box characters instead of actual Unicode characters, this is what it looks like:


Transactional replication with updates on the subscriber?

After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)

There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator!
This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).

You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication:
- When adding articles to the "reverse" publication:
@pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated
- When setting up the "reverse" subscription you should set:
@sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber
@loopback_detection = 'true' (which is set by default)

These are the steps that need special attention. Other than that, it is a straight forward process.

When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open.
You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.

PS. I will provide a detailed description of the setup process, shortly.


If all you need is to speed up your query, you can create the following indexed view:

Select ParentDetailID ,
       SUM(Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail
Group By ParentDetailID

You can join it you Detail table, and your query might run faster.


How to do a configuration on RMAN with run block without save this configuration on rman

Instead of CONFIGURE, just use ALLOCATE to allocate a channel with the desired parameters. You will also have to give a name for the channel, which is C1 in the example here:

run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 50M;
BACKUP DATABASE;
}


I want to know if there is a problem to occasionally start a new incremental backup when the previous one is still running

For SQL Server 2000

You would not be allowed to start new differential backup unless the previous one finishes.

For SQL Server 2005 and above

You can start new differential backup while old one is still running but new one would be blocked by already running differential backup. The new diff backup will only start after old one has finished.

This Blogs.msdn article and this SE thread would help you in understanding similar behavior


How to split XML array into separate rows (while upholding consistency)

Does something like this satisfy the resultset?

Table & Data

CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)

INSERT INTO #TestingIdea(PostId,Tag)
VALUES(1,'<mysql><innodb><myisam>')

GO

Query

SELECT PostId, RIGHT(value,len(value)-1) as SplitTag
FROM #TestingIdea
CROSS APPLY string_split(tag,'>')
WHERE value != ''

Result

PostId  SplitTag
1   mysql
1   innodb
1   myisam


You have to add To_Char(Order.Date, 'IW') in the GROUP BY too.


How to verify the password generated by ENCRYPT?

When you store a password you have to store encrypt(password,salt) and salt for this user. To check if input from user is the right password you comapre encrypt(input,salt) with the stored encrypted string. Chanck the section about 8.6.2 What Is Salt? in
Practical UNIX & Internet Security, by Simson Garfinkel & Gene Spafford from O'Reilly.


Does PL/SQL support pointers, aliases, or references?

Since it is a database you can create a table with key and value. You can create a package that has a CONSTANT which points to a key. You can create a function that takes a key and returns a value from your key/value table. Then you would just look up and use that values from the key/value table and rather than changing code you change data.


Your best bet is probably to solve this at the application level.  This looks like a query you run as part of a data-cleaning exercise.  If that is so, why do you care if it takes 6 seconds to run, and why do you restrict it to 100 rows rather than reading all of them in one shot?  Perhaps you could use a materialized view or some other caching mechanism.  If you reject that option, read on for some "second best" options.

  I should also mention that the users_email_subscribers_idx is showing an Index Scan and not Index Only Scan likely because the index is being updated regularly.

That isn't why. You need columns out of the Users table that are not contained in the index, such as firstName and id. If you created an index with all those column at the end of the column list, you would get an index only scan. That might make the query 20% faster, but isn't going to make it 99% faster.

               Heap Fetches: 1153034

You need to vacuum UserEmails more aggressively.  Again, it won't be a 99% improvement, but it should help some.  Autovacuum doesn't do a good job at keeping tables sufficiently vacuumed to optimize index-only scans.  You can do manual vacuums.  Or you can try to force autovacuum to do a better job by lowering the per-table setting of "autovacuum_vacuum_scale_factor" to zero, and then setting the per table "autovacuum_vacuum_threshold" to be in control of the vacuuming.  If the table is updated randomly throughout the table, I'd set "autovacuum_vacuum_threshold" to about 1/20 of the number of blocks in the table.

How does the query perform if you experimentally set enable_nestedloop to off?  This will probably give you hash anti joins, and if your version is new enough you might get parallel versions of them.


Can additional indices ever aggravate lock contention?

Speaking for Postgres.

In default READ COMMITTED transaction isolation, indexes don't introduce the possibility for deadlocks where there wouldn't be without them - AFAIC. It's still possible to experience deadlocks only after creating a new index. Changed query plans can make lurking design problem surface as side effect, but not as immediate fault of the index.

Creating or deleting indexes is special, quoting the manual:

  An exclusive lock on the index as a whole will be taken only during
  index creation, destruction, or REINDEX.

And there are corner cases with SERIALIZABLE transaction isolation. See the last paragraph of chapter "Index Locking Considerations" in the manual. (Or read it all for details.)

But indexes can certainly aggravate lock contention in various ways. Obviously, additional indexes incur additional cost for write operations (indexes have to be maintained). There are other corner cases that might impair performance with a similar effect. Longer transactions mean more lock contention, since locks are only released at the end of transactions.

For large inserts or updates it can pay to delete unrelated indexes and recreate afterwards (in the same transaction) for better performance - but at the cost of an exclusive lock on the table. Example:

Best way to populate a new column in a large table?

Typically, though, if you only create indexes you actually need, the opposite is the case: shorter transactions, less lock contention.

Related:

Deadlock best practices


Benefits of not having a clustered index on tables (heaps)

I'm only addressing the question in the title. The question body has too many different questions to be answered in a stack exchange answer. Heaps have the following advantages over tables with clustered indexes:

Tables with clustered indexes physically organize the data in order of those indexes. The SQL Server query optimizer may add a sort to a query plan for large enough inserts if the data to be inserted isn't already sorted in clustered key order. The additional work performed by the sort is not free and the query can run slower as a result. Inserts into non-partitioned heaps do not need sorted source data.

The rules for minimal logging work differently between heaps and clustered indexes. In some scenarios, an insert into a heap will write fewer bytes to the transaction log than a comparable insert into a table with a clustered index. Reference the Data Loading Performance Guide for some examples.

A clustered index prevents parallel insert into a table. The insert part of the query plan will always be located in a serial zone (DOP = 1). Starting with SQL Server 2016, an insert into a heap with no nonclustered indexes can qualify for parallel insert.

A clustered index will add lock and latch contention when many separate processes are inserting into the same table. Heaps with no nonclustered indexes can be loaded by many processes concurrently with less contention, especially when those inserts are sourced outside of SQL Server with Bulk Update locks.


Static service not working when using SCAN?

Static registration is no longer required starting with 12.1.0.2, as the DG broker uses Grid Infrastructure to restart instances. Of course, if you have a single instance database without Grid Infrastructure, there it is still required.
Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Also in the above document:

From 11.2 on, never register the entries with the SCAN listener as it
is not associated with a specific host.

I have never seen anyone using static registration in SCAN listeners, so I had to try this myself. On a 2-node cluster with 3 SCAN listeners, I added the below to listener.ora, first node:
SID_LIST_LISTENER_SCAN1,SID_LIST_LISTENER_SCAN2,SID_LIST_LISTENER_SCAN3=
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TEST_SERVICE)
   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
   (SID_NAME = TEST1)
  )
 )

Second node:
SID_LIST_LISTENER_SCAN1,SID_LIST_LISTENER_SCAN2,SID_LIST_LISTENER_SCAN3=
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TEST_SERVICE)
   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
   (SID_NAME = TEST2)
  )
 )

Yes, it worked, but I would still not do it because of the above reasons.


Try an index on (tag, time).  With B-tree indexes, you generally want the equality-constrained columns to be in the index before the inequality-constrained columns.


You need to be a little more clear about your requirements:

Do you want to keep all 12 databases intact (12 instances versus all 12 in a single instance)?
Are you intending to serve any queries from the backup servers?
Do you want multiple snapshots?

You can approach this from many different ways, but I will cover the most common approaches:

Put secondaries in US-West

This approach means you have a full, complete copy of your databases on the west coast.  You can make them hidden and priority 0 to make sure they don't serve slaveOK reads or become primary by accident.  To make them primary (in a disaster recovery scenario) you will need to re-configure the set manually if they are hidden and priority 0.

This is quite an easy approach, though it could be expensive.  You can run multiple mongod processes on a single host but they will be competing for resources and it will still have to be fast enough to keep up with the primaries on the east coast.

Use EBS snapshots

Assuming you are running with journaling enabled, you can just take an EBS snapshot of your data folder (dbpath) and you can back that up to S3 or use it to "seed" hosts on the west coast.

Mongodump

mongodump can be used to dump out all of your data from the Eastern hosts to the Western hosts.  However, the output (BSON files) will need to be restored before it can be used.

Generally I would recommend a combination of the first two in order to combine the best resilience options with the most flexibility.


"Data truncated" vs "Incorrect string value"

It is an attempt to store a 2-byte string into one byte

Since the maximum length of TEXT is 65535, it can safely hold 32767 (65536/2 - 1) 2-byte characters without an error message.

Any attempt to add 32768 2-byte characters will result in Incorrect string value: '\xC3\xA9' for column 'mycol' at row 1 because the 32768th character does not have the room to be inserted into mycol.

In reality, you do not get a malformed character. You really can a TEXT field whose 2-byte character count and length is 32767. The last character is simply lost and not considered .

To make sure, run this

select length(mycol) from txttest;

The last INSERT will not be length 65536 by 65534.


Backup Database (SQL 2000 Compatibility) on SQL Server 2005 with CHECKSUM, but Page Verification TORN_PAGES Detection

Check the file permissions on the backup on the second server.  It's possible that's your problem, and it's easily fixed.

If that doesn't fix it, you should run a dbcc checkdb on the original database, which will tell you if the database itself is corrupt.

If DBCC returns okay, run a RESTORE VERIFYONLY FROM DISK = 'path to backup' on the backup on the original server.

If all of that returns okay, go back to the second server and double-check the file permissions.


Using UNION (ALL) in a WHILE loop with SELECTto merge the result set for each iteration

Depending on your real requirements, something like this might work:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out varchar(255);
    SET @Out ='';
    WHILE (@start<@end)
    BEGIN
        SELECT @Out = @Out + CHAR(@start);
        SET @start = @start + 1;
    END
    SELECT @Out AS Character;
END;

This will append each char to a variable, then output the content of the variable.  If you need each character on  a separate line, you could add CHAR(13) + CHAR(10) to the end of the SELECT @Out line.

if you require each character to be returned in its own row, you could do:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out TABLE (Character varchar(255), RowNum IDENTITY(1,1) );
    WHILE (@start<@end)
    BEGIN
        INSERT INTO @Out (Character) VALUES ( CHAR(@start) );
        SET @start = @start + 1;
    END
    SELECT * FROM @Out ORDER BY RowNum;
END

This stores the character data in a table variable during each iteration of the while loop, then selects the contents of the table variable.  The ORDER BY clause ensures the character data is outputted in the correct order since the RowNum field is an automatically incrementing IDENTITY column.


MySQL "Statement may not be safe to log in statement format" warning

This means that your binary log format binlog_format is STATEMENT, which is the default binary log format.

STATEMENT-based format logs the statement issued, while ROW-based format logs how individual tables were changed. The problem with STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the slave.

An example:

INSERT INTO audit (CURRENT_USER());

You can get a good idea of different types of these non-deterministic statements by looking at how the MIXED format handles switching to ROW-based format. ROW-based format writes a lot more data to the binary log, but is much safer.


Does 2 in the DBCC LOGINFO column "Status" mean it cannot be truncated?

According the the information in this MSDN forums answer by Sankar Reddy (and other posts when searching for DBCC loginfo status 2):

  Status = 2 means that VLF can't be reused (overwritten) at this time
  and it doesn't necessarily mean that VLF is still active and writing
  transactions to that VLF. It means that the VLF is waiting for
  backup/REPL/Mirroring etc...

Database Checkpoints (SQL Server)

  CHECKPOINT only truncates the transaction log (marks the VLF for
  reuse) only in simple recovery model. In Full recovery, you have to
  take log backup.

Understanding Logging and Recovery in SQL Server by Paul S. Randal


Change MySQL backup script to use on PostgreSQL

have a look at pg_dump it's very similar to the dump utility by MySQL
See: pg_dump documentation

I think most of the change is just changing the flags to match the new utility and replacing the call for mysql with a similar call to psql -l to get the db list (to filter out dbs you want to ignore)


How deterministic is execution plan creation in SQL Server?

Given these constants, will SQL Server always produce the same plan for a given query? If not, are there other considerations? Is there also an element of nondeterminism to consider as well?

Query compilation is deterministic as far as I am aware. One of the original QO design goals was that it should be possible to reproduce execution plans on a different system using a statistics-only copy of the database. There are a couple of subtleties to that, around configuration parameters like the amount of memory available, and the number of logical processors, but these are covered by your list of things to synchronize.

Caveat: That is true provided the word 'same' in your list is taken to mean exactly the same in all respects. For example, the 'same' statistics might exist on the two systems, but they are only exactly the same if the histogram steps and density information are identical.

That said, the optimization process is also extremely complex, meaning it can be difficult to ensure that all inputs to this deterministic process are identical, and that all internal states are similar enough to ensure the same code path is taken through the optimizer for a particular compilation. If the query contains access outside the database (to another database or instance), those environments must be identical too.

One thing I would add to your list is to check that any plan guides also exist in the second database.

The use of non-deterministic functions like GETDATE() in queries might mean you get a different plan too. While the main optimizer does not use the value directly, cardinality estimation can (see Constant Folding and Expression Evaluation During Cardinality Estimation). I am unsure if this class of difference falls within the scope of the question though, because both systems would produce the same plan if executed at the same time (or, more generally, with the same input variables, parameters, and function values).


After clarification in the fiddle you provided, the only other way I can interpret your issue is you're saying you're not seeing the columns for the users table even though you joined to it. That's because you didn't list any columns from the users table in the SELECT list.
You can add any or all columns from your users subquery you want to the SELECT list like so:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.*
FROM reservations
LEFT JOIN (
    SELECT users.id AS userID, users.name AS name, users.surname AS surname FROM users
) AS users ON reservations.user_id = users.userID
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;

Note you shouldn't use * in your SELECT list as it's bad practice and can lead to dependency issues later on, and rather you should explicitly list the column names you want.
Furthermore it seems like you might've gotten a little confused because you used a subquery to join in the users table but that's not necessary, rather you can re-write your query simpler like this:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.id AS userID, users.name AS name, users.surname AS surname
FROM reservations
LEFT JOIN users
    ON reservations.user_id = users.id
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;


How do you tell which host is close to being blocked in MySQL?

Actually, you should raise the max_connect_errors way beyond the default of 10.

The range is 1-18446744073709547520. I would set it to 1000 for now.

However, you have a bigger problem. mysqladmin requires you connect to mysqld before it does anything. If max_connect_errors is reached too quickly, you cannot make mysqladmin do anything at that point. Even a cron job will fail in this respect.

Since you are using MySQL 5.5, your best option is create a scheduled event within the MySQL Instance to go off every 5 minutes that will only do one thing: run FLUSH HOSTS.

I just did this in MySQL 5.5.12 on my PC

mysql> use test
Database changed
mysql> CREATE EVENT ev ON SCHEDULE
    ->       EVERY 5 MINUTE
    -> STARTS (NOW() + INTERVAL 1 MINUTE)
    -> DO FLUSH HOSTS;
Query OK, 0 rows affected (0.02 sec)

mysql>

Great, no complaints from mysqld. Of course, I arbitrarily chose 5 minutes. You can choose whatever schedule you prefer.

Give it a Try !!!

UPDATE 2011-08-25 11:22 EDT

For everybody using release of MySQL prior to 5.1, I have a worthwhile suggestion:

Write a perl script that keeps an open connection, making it heartbeat itself by retrieving something (i.e., uptime, aborted_connects, etc) and every 10 minutes kicking out FLUSH HOSTS. Whatever user you connect as, make sure it has RELOAD privilege.

For example: run this command:

GRANT RELOAD on *.* to 'whateveruser'@'localhost' identified by 'whateverpassword';

Here is a perl script I call heartbeat.pl to do this using that user and password:

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

In the OS, call the perl script to heartbeat every 6 seconds. When it heartbeats 100 times, that 600 seconds (10 minutes), make it run FLUSH HOSTS. Call it like this:

nohup perl heartbeat.pl 6 100 &

Here are other schedules:

nohup perl heartbeat.pl  6  50 & (heartbeat every  6 sec,FLUSH HOSTS every 5 minutes)
nohup perl heartbeat.pl 10 360 & (heartbeat every 10 sec,FLUSH HOSTS every 1 hour)
nohup perl heartbeat.pl 60 180 & (heartbeat every 60 sec,FLUSH HOSTS every 3 hours)

To stop the program, run this:

perl heartbeat.pl


Is a Non-Clustered index needed when referencing a small table with a Foreign Key

Short answer
No.
Long answer
No, and adding the index could be detrimental to performance.
Something that factors heavily into whether or not a secondary/non-clustered index is even used is how selective it is (and the searches you are trying to perform are).  Y/N has two values - it's ability to be selective is going to depend on the proportion of Y to N.  If they are evenly split, and there is no rhyme/reason to Y/N with relation to the clustered index, they will not be selective and the query optimizer will almost always ignore that index.
The potential slowdown occurs if you issue a delete on the larger table or an update against that Y/N column.  There's no efficient way to update the secondary index as it is organized by Y/N - so the only option is to basically scan the entire index looking for the record to update/delete.  Depending on the number of rows in the main table and the size of the clustered index, this could be an issue.
Caveats
If there are few Ys or few Ns, and you need to locate those records quickly, you can use a filtered index to create a very small index that contains only records for the sparsely populated value.  This can be beneficial and has a much lower footprint than a traditional non-clustered index would in that situation.
If you're just trying to enforce Y/N (or some other easily understood code set), that can be handled easily through a check constraint, which will have a lower overhead than the FK.
ADD CONSTRAINT CK_<ColumnName>_Is_YesNo CHECK (<ColumnName> IN ('Y','N'))


Are your links supposed to show anything? Currently none works.

TT is right, that setting is just for the Buffer Pool and some memory clerks. See docs. There are other areas and even services part of SQL Server package that use memory.

  max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

Suggestion: You've most likely allocated too much memory to SQL. Dial it down - suggest going down to 40 GB to let also the OS breathe. If you have any signs of memory pressure then you might need to turn the knobs for more memory.

If you don't have any problems or memory pressure signs, then SQL Server it's doing its job.

Articles on the subject:

https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
https://www.brentozar.com/blitz/max-memory/
https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
http://www.sqlpassion.at/archive/2015/02/23/the-3-configuration-options-that-you-always-have-to-change-in-sql-server/
https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/
https://straightpathsql.com/archives/2017/02/sql-server-max-memory-best-practices/
https://voluntarydba.com/post/2013/03/19/Is-my-SQL-Servers-memory-over-committed.aspx

Later edit, after your reply:

You need first to establish what's the problem. Is it the report itself that there's no available memory? Do your applications/queries really suffer due to low memory? Is it just a training exercise?

You playing with database settings might just have caused the buffer pool to be flushed and now the reports look cool, but apps may suffer.

See what's in that server's memory by running:

https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-october-2017/

And after you find out how your server's memory is populated, only then you'll find out where's it spent and if you have a problem.


You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.

It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.

This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.

One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.

And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.


Is it possible to log mysql queries from other hosts in general log?

You may use the mcaffee audit plugin https://github.com/mcafee/mysql-audit/wiki/Installation there you can filter hosts in logging.

You could also refuse all hosts specifying a mysql User with an appropriate Host. ('user'@'allowedHostIpRange').


How to merge two databases (each with different updates) on the same server

(Disclaimer: I'm not familiar with WordPress and I haven't used phpmyadmin very much.)

If there is new data in each database since the "split", i.e. each database has some unique new data not found in the other one, then I don't think there is an easy path for merging them just using standard MySQL commands or tools. There will be records in one database with the same primary key values as in the other database, but with different data in the other columns in those records. If there aren't a lot of such records, then you may be able to merge this "manually", record by record, but it will likely be labour intensive and you need to know what you're doing. E.g. when you give a record a new primary key value to avoid a collision with the other database, you also need to make sure to update every foreign key that refers to it.

That said, there are special WordPress plugins for exporting and importing WP databases, and I'd be surprised if these didn't allow you to merge two databases. See e.g. wp-exporter and wordpress-importer. But that is probably outside of the scope for this site. See also this question on stackoverflow: How do I merge two Wordpress SQL databases in phpmyadmin?


Postgresql: does effective_cache_size includes shared_buffers?

Quote from the manual

When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files

(emphasis mine)
So the answer is: yes, it includes the shared buffers.
If you set shared buffer to e.g. 8GB and see that the operating system uses approx 16GB for caching, effective_cache_size should be set to 24GB
Note that this parameter does not allocate any memory. It's just a hint to the optimizer how likely it is that a block needs to physically read from disk.


DBCC DBINFO incorrect dbi_dbccLastKnownGood

READ ONLY REPLICA (Asynchronous AlwaysOn HA AG)

Like Erik pointed out, AG Replicas are currently read only so it can't update the values here. Additionally, if it did update the values it would just be overwritten the next time anything was updated on the primary and the log block sent to the secondary.

I decided to check the PRIMARY REPLICA where DBCC CHECKDB is also running
•Subplan ran at 02/05/2018 00:00:00 AM successfully, with a duration of 02:14:40
•When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113 which is the same as the RoR but it should be 02/05/2018 02:15 AM

The job started 2:14:40 ago, which is the time of the last known good checkdb - that's true. You started it then, it ran for 2 hours and 14 minutes +/- some agent stuffs. The times look fine to me, it's not like the job ran for 5 seconds and shows a last good of 6 hours ago.
I believe the issue is you expect the CHECKDB to show the good value at the end of the run versus being updated when it was executed. That shouldn't be a problem.


I cannot write into SQL Server 2008 R2 express edition from C#

Are you using the User Instances and AttachDbFileName options in your connection string? If so I will bet that you are actually connected to two different copies of your database. You need to make sure you are connecting to the same copy - I would suggest manually attaching the database on your Express instance, and always connecting to that without the use of User Instances and AttachDbFileName.

If this is not the case, please show the connection string you are using in C# and how you are validating that the data is not updated (e.g. you are probably using a different application and/or connection string for that).

The User Instances feature is actually deprecated for future development work. In SQL Server 2012 this is replaced with SqlLocalDb.


JDBC Exception JZ0BE: BatchUpdateException: A user with System Administrator (SA) role must reconfigure the system to enable Java

Start by trying:

sp_configure "enable java",1

...and restarting the service if necessary. There may be more to it than that, please provide details of your server product, version, and anything else that might be helpful.


Reference implementation of a scalable storage system with strong consistency?

The image in this article is used quite a bit around the internet in various different forms but it highlights which systems fall in each category of CAP

So your are basically asking which horizontally scalable systems don't rely on eventual consistency. To which MongoDB, HBase and many more would be a good answer.


Do I still need to disable or drop column store index before insert and/or update on big tables?

Correct, you don't have to.

For inserts be sure that ETL is saving data directly into compressed rowgroups, not the delta store.

If you want to update or delete data you need to be aware of the internal implementation of the updates on the column store. During DML operations data in a compressed column store rowgroup are never deleted or modified but just flagged as deleted. Updates are implemented as Insert/Delete.
Rows are physically deleted only during index reorg or rebuild.


Percona Server - binlog not automatically purge with mysqladmin flush-logs

Please note Paragraphs 2,3 in the MySQL Documentation for binlog_expire_logs_seconds:

  The default binary log expiration period is 2592000 seconds, which equals 30 days (30*24*60*60 seconds). The default applies if neither binlog_expire_logs_seconds nor the deprecated system variable expire_logs_days has a value set at startup. If a non-zero value for one of the variables binlog_expire_logs_seconds or expire_logs_days is set at startup, this value is used as the binary log expiration period. If a non-zero value for both of those variables is set at startup, the value for binlog_expire_logs_seconds is used as the binary log expiration period, and the value for expire_logs_days is ignored with a warning message.

  To disable automatic purging of the binary log, specify a value of 0 explicitly for binlog_expire_logs_seconds, and do not specify a value for expire_logs_days. For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly for expire_logs_days and do not specify a value for binlog_expire_logs_seconds. In that case, the default for binlog_expire_logs_seconds is not applied.

Please review your my.cnf

If you have this line in the my.cnf

expire_logs_days = 0

change it to

expire_logs_days = 1

Then go execute

mysql> SET GLOBAL expire_logs_days = 1;
mysql> FLUSH BINARY LOGS;

This will cause a new thread to be opened with the new setting.

It's OK to have

expire_logs_days = 1
binlog_expire_logs_seconds = 86400

because the documentation says when both are set, binlog_expire_logs_seconds will take precedence and a simple warning is posted for expire_logs_days.

As a last resort, please restart mysql

service mysql restart

If you still have a problem after doing this, then I would suspect a bug.


Calculate new percentage when new record is added

Why don't you sum the result for each group and use that sum to calculate the overall percentage?
E.g. You have 4 groups with result 34,23,56,78. The total percent can be calculated by:
 total = sum(34,23,56,78)/4 * 100;

After running the aforementioned php code on insertion, use the value of the $result variable to perform the calculation.

Wanted to put as a comment but I can't... I will try to put the above in code. Does this help?

This is a sample:

$conn = mysqli_connect("localhost","root","","test"); # replace the details as it becomes necessary
if(!$conn){
die("Connection failed: " . mysqli_connect_error());
}
else{
    $initRows = 0;
    $result = 0;
    $sql = "SELECT SUM(num1 + num2 + num3) AS SumNumbers  FROM numbers GROUP BY numid";
    $res = $conn->query($sql);
    $initRows = 0;
    $rowNum = 0;
    $result = 0;
    $total = 0;
    if ($res->num_rows > 0) {
       while ($row = $res->fetch_assoc())
        {
            $initRows = $row['SumNumbers'];
            $rowNum = $res->num_rows;     #this will give the number of rows
            $columncount = mysqli_num_fields($res); # this will give the total number of columns
            $result = floor(($initRows/$columncount)*100); #this will give the percentage
            $total += $result;     #this will give a total of all results but not the percentage. if you need the persentage of the total, you can now derive it using the $total variable
            echo $result ."
";
        }
        echo "Total is " . $total . "
";
        $totalres = floor(($total/$rowNum)*100);
        echo "Total result percentage is " . $totalres;
    }
}


Summary

For large tables, it is nearly always preferable to move the data instead of altering the table. To get maximum speed, use one of the following patterns. Let us call the table you want to change S (for source).

Create an empty copy of S. Let us call that T
ALTER TABLE on T. This is very fast because T has no rows
If T has a cluster index, remove all other indexes but the cluster
If T is a heap, remove all indexes
Put the database in SIMPLE recovery mode (if you can, but be aware of the backup implications)
Use one of the two options provided in the Copy Data section below
Rebuild the indexes on T
Drop S and rename to T

Copy Data

In step 6 above, you will need to move data from S to T at highest possible speed. The following are two options:

Use INSERT ... SELECT
Use SSIS

Of these two, SSIS is the fastest. With INSERT ... SELECT you are restricted to a single thread. However, INSERT ... SELECT is much easier. See below for more details.

Option 1: INSERT ... SELECT

If T is a heap, do this:

INSERT INTO T WITH (TABLOCK) SELECT ... FROM S

This is minimally logged.

If T has a clustered index, do this:

DBCC TRACEON (610)
INSERT INTO T SELECT ... FROM S

This is also be minimally logged. The problem with this option is that you can only run one INSERT statement in parallel. Because SQL Server does not do parallel INSERT ... SELECT, this restricts you to around 40-80MB/sec - which is quite slow.

Option 2: Use SSIS

This option is a lot more trouble, but also faster. The idea is this:

Drop all indexes on T
Create an SSIS package that takes a parameter that selects a subset of the rows in S (using some filter that is supported by an index). The SSIS package then moves the rows to T using the TABLOCK hint in bulk mode
Execute multiple copies of the SSIS package, each operating on their own, distinct subset of S (so you don't duplicate rows in T)

This is MUCH faster because the packages can run in parallel. With this technique, I can typically move tables at GB/sec.


MongoDB document delete and fragmentation

Reasons for unexpected data growth of data files.

"Data fragmentation" and data file preallocation

When a document is deleted, it's space is used right away if the new document fits into that space. Let's say you delete a document which takes 1kb of disk space and a new document requiring 0.9 Kb of disk space is synced to disk, then the first free space (the deleted document's in our example) will be used. Now let's assume the new document would need 1.1k. In a worst case scenario, a new datafile of 2GB had to be provisioned although only 0.1kb of space was missing. The reasons for datafiles being preallocated are rather good ones, btw: it would simply take too long during a disk sync.

Padding

When a document is written, some space is added to allow the document grow in size without triggering a rather expensive document migration each time. Documents are migrated when they do not fit into their position in the data file any more since

  Documents are never fragmented

So if your documents grow, they have to be migrated and a new padding is applied, it might well be that millions of places in the data files would provide enough space for a 1k document, but still a new data file has to be preallocated.

Another "problem" is the way padding is calculated. As of MongoDB 2.6, documents are by default by using power of 2 sizes. So let's assume your document is 513 bytes in size. However, since the next power of 2 would be 1kb, almost half of the space allocated for the document would not be in use until it grows in size. So in a worst case scenario, half of the space allocated for your data files -1 byte might be "wasted".

Increased usage

Your application might well be getting momentum and there simply is more data stored than you expect. Congratulations!

What to do

Usually, one of three ways of dealing with data file growth is suggested.

the compact command
the repair command
Forcing a resync from replica set members

I'll go over them with their Pros and Cons from my point of view and explain why I think all of them are improper ways of dealing with that data file growth.

The compact command

How it works

The compact command defragments the data files of a collection. It does it by creating a new data file of 2GB and moves the documents back and forth until there are no gaps between the documents any more.

Pros

The compact command is relatively fast when compared to the other solutions. The defragmentation helps a bit to prevent unnecessary data file preallocation.

Cons

The database containing the target collection is locked during the execution.
No disk space is reclaimed
You really should have a backup of the target collection before using the compact command. So in order to have said backup, you need to over provision your disks with 2Gb (the additional data file) plus the size of your largest collection (for the backup). But with over provisioned disks, space will not be a problem in the first place.
It doesn't help at all when space really is a problem: if you are in a critical situation, the problems detailed above prevent you from using the compact command.

Why I don't think it is a proper solution

Well, it's kind of obvious - you lock your database, which means downtime. For really large databases, this means a lot of downtime, and all this for the relatively small gain of potentially preventing one or two data files to be created (which means 4Gb disk space at max).

The repairDatabase command

How it works

Simplified, the repairDatabase command creates a second instance of your database, iterates over the documents in the original database, verifies them and writes them into the new database in consecutive order. In the last step, the old database is deleted and the new database is renamed.

Pros

With a proper planning, you can reclaim disk space with very little downtime, since the repairDatabase command can be run against secondaries. So you can do the following

Run the repairDatabase command against all secondaries
Have the primary step down. This might lead to 3-5 seconds of downtime during the election of the new primary.
Run the repairDatabase command against the recently stepped down primary

Sounds nice, right? However, there a huge

Cons

You need to massively over provision your disks, since basically a copy of your database is made. So now let's assume you run this command against a database which is in an optimal state. So to make sure the command is successfully executed you need at least the same amount of free disk space as your database uses when you issue the repair command. Since the repair command is potentially even more critical than the compact command, you should make a backup beforehand or use the backupOriginalFiles option.

Why I don't think it is a proper solution

The cons detailed above show that you have to over provision your disks by at least 200% of your payload data. With that massive amount of disk space, you would not have a problem in the first place.

Forcing a resync from replica set members

How it works

You shut down a secondary, delete its data files and restart it. The node notices that it is basically a new member added to a replica set and forces an initial sync with the replica set. Since the initial resync is document oriented, only necessary datafiles are allocated, potentially freeing formerly used disk space.

Like with the repair command, you do this for all secondaries (of course one after another), have the primary step down and delete its datafiles and let it resync.

Pros

You do not need to over provision the disks of an individual node
There is just very little downtime
It is a relatively straightforward process

Cons

This process takes a while, may well have some impact on performance and reduces your planned level of redundancy. Let me explain this in a bit more detail: When planning a replica set, you choose how many replicas you want to have, ranging from one (two data bearing nodes plus an arbiter) to 50 as the time of this writing. You have a good reason for this redundancy, whatever it may be. When arbitrarily shutting down replica set members in order to reclaim disk space, you effectively reduce or even eliminate failover capabilities. So it is safe to say that in order to keep your desired level of redundancy during the resync, you need one additional node to maintain it.

Why I don't think it is a proper solution

Put plainly: putting half the money you spend for the additional node into additional disk space should solve any space problem in the first place. However, this might not be in your case (although that might well be through under dimensioned hardware) and thus the resync might be a viable solution in some cases

Ok, smarty pants: What to do?

Frankly, from my experience, the need of reclaiming disk space is a sure sign of a badly planned cluster.

Granted, MongoDB is not the most efficient when it comes to disk space consumption, but after a while, it levels out. So when MongoDB constantly adds new datafiles, you can be sure that you simply need more disk space.

This can be either achieved through vertical or horizontal scaling. If you still can scale vertically and get an adequate bang for your buck, your hardware was underprovisioned until now. Go for it, problem solved!

If you already get the most bang for the buck and the size of your data (not only the number of your data files) constantly grows, it is time to scale horizontally, read to shard your cluster.

As a rule of thumb: when more than 80% of your disk space is used and the size of your data didn't show a massive spike but is constantly growing, I'd add a shard or start sharding. It requires some experience and knowledge to determine the exact threshold and how to do it exactly is out of scope even for this long answer.

With this approach, the decision when to shard is based on emprirical information, is started early enough to prevent serious problems, it reduces maintenance effort and risks and it enables you to scale properly.

One last word: Often people say that adding a shard is too expensive or they are not up to paying three config servers in addition to the data bearing nodes and start to shard their data manually. The reason for that is plainly wrong calculation of their own prices and a wrong understanding of how to do things sustainably. In the long run, it's going to bite you in the neck to reinvent the wheel.


I think you can, using a "diamond" relationship diagram:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;


CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

The deciding factor for Postgres is that the function concat() is defined stable and not immutable in the system catalog pg_proc:

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc
FROM pg_proc
WHERE proname = 'concat';

proname | provolatile | proargtypes| argtype | prosrc
--------+-------------+------------+---------+-----------
concat  | s           | 2276       | "any"   | text_concat

The manual on pg_proc.provolatile:

  provolatile tells whether the function's result depends only on its
  input arguments, or is affected by outside factors. It is i for
  "immutable" functions, which always deliver the same result for the
  same inputs. It is s for "stable" functions, whose results (for fixed
  inputs) do not change within a scan.

I also added the argument types of the function ("any") to connect to the answers of @dezso and @jjanes, which deliver the rationale behind the decision to make this function only stable. And the name of the internal function (text_concat).

Here is a related question to illustrate why immutability of index expressions is a sine qua non condition:

Intentionally corrupting an index in PostgreSQL

As for using the operator ||:

    SELECT o.oprname, o.oprleft::regtype, o.oprright::regtype, o.oprcode, p.provolatile
    FROM   pg_operator o
    JOIN   pg_proc     p ON p.oid = o.oprcode
    WHERE  oprname = '||';

 oprname |   oprleft   |  oprright   |     oprcode     | provolatile
---------+-------------+-------------+-----------------+-------------
 ||      | anyarray    | anyelement  | array_append    | i
 ||      | anyelement  | anyarray    | array_prepend   | i
 ||      | anyarray    | anyarray    | array_cat       | i
 ||      | text        | text        | textcat         | i
 ||      | bit varying | bit varying | bitcat          | i
 ||      | bytea       | bytea       | byteacat        | i
 ||      | text        | anynonarray | textanycat      | s
 ||      | anynonarray | text        | anytextcat      | s
 ||      | tsvector    | tsvector    | tsvector_concat | i
 ||      | tsquery     | tsquery     | tsquery_or      | i
 ||      | jsonb       | jsonb       | jsonb_concat    | i

The function to use internally depends on actual data type of operands. The definition of an operator includes the operands' data types in Postgres.
All of the functions are different and also different from text_concat above. || is just stable as well, when one of the operators is anynonarray. Things are not so trivial behind the curtains.

character varying(256) (like any varchar variant) is binary-coercible to text so function type resolution defaults to text.


Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.


MS SQL Linked Server Security failing using current security context

You mention a SQL Agent Job. I usually make sure the SQL Server Agent is configured with a domain account as well, and I usually use the same account.


DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc

How about DBMS_LOB.SUBSTR, introduced in Db2 for LUW 9.7 as part of the Oracle compatibility feature?
If you are migrating from Oracle consider reading about that feature and how to use it, it might save you some effort.
Plain old SUBSTR will work as well.


Database restore on new server and upgrade to SQL Server 2012

First you need to run upgrade advisor to be able to confirm that there won't be any errors during the upgrade from 2008 to 2012.

If you want to minimize downtime, then setup mirroring from SQL 2008 to 2012 (yes mirroring is still supported but declared deprecated). Then during the day of cutover (migration), change the mode of mirroring to SYNCHRONOUS and failover. This will require couple of seconds to bring the mirror server DB online.

this way, you can prepare in advance and minimize the downtime.

once the 2012 server db is online, you can get that database in AG. Make sure to remove mirroring once you failover.

I have put in detailed steps for migration at : Upgrade SQL Server 2000 database to 2008 R2 and enable new features

Don't go for detach/attach as it is risky as if something goes wrong, you will end up without backup. Backup restore is the best method.


What is the minimum privilege required to create a full text index?

Someone can create indexes if they have been granted db_ddladmin or if their user is the owner for that table, database, or has been granted db_owner.


Locate multiple duplicate columns in PostgreSQL table

Look for the model - does you need something like this?

fiddle

create table test (id int, value1 int, value2 int)

✓

insert into test values
(1,11,21),
(2,12,22),
(3,13,23),
(4,14,24),
(5,12,24),
(6,16,26),
(7,17,24),
(8,18,28)

8 rows affected

select t1.id id,
       t2.id dup_id,
       case when t1.value1 = t2.value1 then 'value 1'
            when t1.value2 = t2.value2 then 'value 2'
            else 'some error'
            end dup_field,
       case when t1.value1 = t2.value1 then t1.value1 :: text
            when t1.value2 = t2.value2 then t1.value2 :: text
            else 'some error'
            end dup_value
from test t1, test t2
where t1.id < t2.id
and ( t1.value1 = t2.value1
      or
      t1.value2 = t2.value2 )

id | dup_id | dup_field | dup_value
-: | -----: | :-------- | :--------
 2 |      5 | value 1   | 12
 4 |      5 | value 2   | 24
 4 |      7 | value 2   | 24
 5 |      7 | value 2   | 24


There's a few different questions in here:

Q: "Looking at perfmon I can see very high Avg. Disk Write Queue."

That Perfmon counter isn't relevant for SQL Server anymore. SQL Server batches IO operations together, and it's normal to see big jumps here. Instead, check out the counters for Avg Disk Sec/Read and sec/Write. This tells you how fast the storage is responding to your requests. The downside is that it's only at the drive (volume, mount point) level. To get IO stats for specific files, query sys.dm_io_virtual_file_stats. David Pless has a great query here:

http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

Q: "Looking at SQL ASYNC_IO_COMPLETION and OLEDB are featuring as the more prominent waittypes. These suggest to me that SQL isn't the defining factor in the poor performance."

ASYNC_IO_COMPLETION is typically data file writes that happen in the background, asynchronously. When you insert/update/delete stuff, SQL Server has to get the data into the log file immediately - that's WRITELOG waits. It changes the data pages in memory, and then those get cached until later. ASYNC_IO_COMPLETION waits aren't holding up end users. It can indicate slow drive performance for data file writes, but that's a system bottleneck, not an end-user-facing bottleneck.

In my experience, OLEDB is usually caused by performance monitoring tools like Spotlight, SQL Sentry, Idera SQL DM, etc that are running traces and grabbing performance data over the wire.

Q: What do others think? What other steps can I take to prove (or disprove) my theory that the disk is slow.

I've got a video on how to do that here:

http://www.brentozar.com/archive/2011/08/how-prove-its-san-problem-webcast-video/

Short story - IO is probably not your biggest problem based on what you described here.


That's because you also need to grant the CREATE TYPE permission on the database to the particular principle(s).

Please see this BOL reference on CREATE TYPE:

  Requires CREATE TYPE permission in the current database and ALTER permission on schema_name.

grant create type
to [U];
go


Index on primary key not used in simple join

Many people have heard guidance that "sequential scans are bad" and seek to eliminate them from their plans, but it isn't so simple. If a query is going to cover every row in a table, a sequential scan is the fastest way to get those rows. This is why your original join query used seq scan, because all rows in both tables were required.

When planning a query, Postgres's planner estimates the costs of various operations (computation, sequential, and random IO) under different possible schemes, and picks the plan it estimates as having the lowest cost.  When doing IO from rotating storage (disks), random IO is usually substantially slower than sequential IO, the default pg configuration for random_page_cost and seq_page_cost estimate a 4:1 difference in cost.

These considerations come into play when considering a join or filter method which uses an index vs one which sequentially scans a table.  When using an index, the plan may find a row quickly via the index, then have to account for a random block read to resolve the row data. In the case of your second query which added a filtering predicate WHERE NOT lezarva, you can see how this effected the planning estimates in the EXPLAIN ANALYZE results. The planner estimates 1006 rows resulting from the join (which pretty closely matches the actual result set of 964). Given that the larger table munkalap_lepes contains about 38K rows, the planner sees that the join is going to have to access about 1006/38046 or 1/38 of the rows in the table.  It also knows the avg row width is 214 bytes and a block is 8K, so there's about 38 rows/block.

With these statistics, the planner considers it likely that the join will have to read all or most of the table's data blocks. Since the index lookups aren't free either, and the computation to scan a block evaluating a filter condition is very cheap relative to IO, the planner has chosen to sequentially scan the table and avoid index overhead and random reads as it calculates the seq scan will be faster.

In the real world, data is often available in memory via the OS page cache, and so not every block read requires IO.  It can be quite hard to predict how effective a cache will be for a given query, but the Pg planner does use some simple heuristics. The configuration value effective_cache_size informs the planners estimates of the likelyhood of incurring actual IO costs.  A larger value will cause it to estimate a lower cost to random IO and may thus bias it towards an index driven method over a sequential scan.


SQL Server 2012 performance v SQL Server 2008 R2

There are some underlying performance enhancements in filestream and full-text search. I don't have the details handy. I am also sure there are other underlying enhancements that aren't publicized, as well as fixes from 2008/R2 that made it into service packs and cumulative updates on those platforms that were ported forward into SQL Server 2012. That would be a bit more quiet because while 2012 was in development there wasn't a need for a public KB article for every change.

Some of the new windowing functionality will make calculations like running totals perform way better. And OFFSET can be used to make child's play out of things like calculating a median. FORCESEEK also allows you to now actually specify the index you want used, instead of taking whatever SQL Server thought you meant. But those require code changes.

There is also going to be more predictable performance with regard to memory use - the memory manager now handles more than just single-page allocations, so "Max server memory" has a much more accurate meaning than it has in prior versions. Resource Governor memory settings also take advantage of this, and can be used for scheduler/NUMA affinity as well.

Troubleshooting performance is also a lot easier. For example there is a lot more information included in execution plans, and extended events has almost full parity with trace (and adds much more powerful causality tracking).

I was surprised that they didn't publish TPC stuff well in advance of the release date (not that those tests really represent real-world stuff, but it's fun to watch the "my dad can beat up your dad, at least today" arguments unfold between the vendors). Glenn Berry blogged about one of the SQL Server 2012 TPC-E results. I apologize but I don't have time to investigate whether there is an easy way to compare this apples to apples against similar benchmarks using 2008 or 2008 R2. The problem with aged benchmarks is that they're never on the same hardware, so while the new benchmarks are always faster (or else they won't get released), it's impossible to tell how much of the improvement was based on the hardware and how much on the software.

Your best bet is to install SQL Server 2012, restore a copy of your database, update compat level, update statistics, and run your worklaod on your hardware using your data.


Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Both references are valid and will resolve correctly, but the #temp table is created under the dbo schema.

Same answer (on your system, some number I couldn't possibly guess):

SELECT OBJECT_ID('dbo.#MyTempTable');
SELECT OBJECT_ID('SomeSchema.#MyTempTable');

Same answer (both 1, which is dbo):

SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.#MyTempTable');
SELECT schema_id FROM sys.tables WHERE [object_id] = OBJECT_ID('SomeSchema.#MyTempTable');

Being able to specify a schema doesn't buy you anything because you're not going to have collisions (two same-named #temp tables under different schemas) within a session, right?

This is expected behavior. A #temp table is tied to a session, but not to a specific schema. And it works the same all the way up to 2016 CTP 3.2. The parser is probably forgiving, allowing the meaningless schema name in much the same way it allows this errant trailing comma:

CREATE TABLE dbo.foo
(
        bar INT
        ,
);


'cmd' Field Definitions sysprocesses MS SQL Server

As suggested in comment sys.sysprocesses is deprecated and Microsoft does not recommends it to use further. Its just there for backward compatibility

The above view has been replaced by sys.dm_exec_requests. You would see cmd column from sys.sysprocesses is now command column in sys.dm_exec_requests and this column tells you

  Identifies the current type of command that is being processed. Common
  command types include the following:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • BACKUP LOG

  • BACKUP DATABASE

  • DBCC

  • FOR

  The text of the request can be retrieved by using sys.dm_exec_sql_text
  with the corresponding sql_handle for the request. Internal system
  processes set the command based on the type of task they perform.
  Tasks can include the following: • LOCK MONITOR

  • CHECKPOINTLAZY

  • WRITER

  Is not nullable.

Instead I suggest you to focus on column sql_handle which is present in sys.dm_exec_requests. Below code would give you query behind sql_handle

select er.session_id,
er.command,
t.text --gives query behind sql_handle
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) t


Is it possible to pass a built in function as an argument to a trigger?

The CREATE TRIGGER documentation says:

  arguments:
  An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings.

You cannot do any processing before the function is called; you have to put all the logic into the trigger function.


Entity ID field that can refer to multiple tables?

One idea comes from extending the analogy of interfaces in object-oriented programming. You can have an IGeneratesNotification "interface" entity that the Posts and Photos entities "implement", as well as an IAuditable "interface" entity that the Posts and Users entities "implement". It would be possible for an entity to implement multiple interface entities, such as in the case of Posts. Here's a diagram of how this can be done.

Note that the fields containing references to the interface entities would be unique.

Advantages:

You can enforce referential integrity to the interface entity that can be implemented by one of several entities.
If there are fields common to all implementing entities, they can be abstracted out to the interface entity.

Disadvantages:

It is possible for the id of an interface entity to be present in 0, or more than one, implementing entities.
The service layer would be required to determine which entity the notification or audit action applies to before knowing which table to query.


Syntax shorthand for updating only changed rows in UPSERT

There is no provision in SQL to say "all columns except this one". (You can do something like that with jsonb or hstore.) Related answer with details:

Select columns inside json_agg

On second thought, you don't need this. You might as well update all columns. Postgres writes a new row version anyway, nothing lost.
INSERT INTO test_upsert AS t (some_id, a, b, note)  -- list is optional
VALUES ('c', 5, true, 'asdf')
ON     CONFLICT (some_id)
DO     UPDATE
SET    (some_id, a, b, note) = ROW (excluded.*)  -- ROW syntax
WHERE  (t.*) IS DISTINCT FROM (excluded.*)       -- again, compare whole row
RETURNING *;

You still have to spell out the column list once for the target list of SET. That's required. The rest can be shortened.
You might even omit the target list for the INSERT while targeting all columns anyway, but in most cases it's good form to spell it out, more robust against future changes.
The table alias is an optional additional syntax shorthand. Note that the AS key word is required for a table alias for the target of an INSERT - unlike most places for a table alias.

The manual on INSERT.

Or even shorter, yet:
...
WHERE  t IS DISTINCT FROM excluded
...

We can as well just use the table name (or alias) for the (well-known!) composite type. The only corner-case disadvantage of this: if there is also a column of the same name "t" (or "excluded"), it takes precedence due to Postgres syntax rules. (t.*) IS DISTINCT FROM (excluded.*) cannot be mistaken and is the slightly safer form. Related answer:

Convert a row to a nested JSON document containing all relations


why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.


InnoDB
You need to run this query:
SELECT
    CONCAT(IB_BB/POWER(1024,1),'K') IB_KB,
    CONCAT(IB_BB/POWER(1024,2),'M') IB_MB,
    CONCAT(IB_BB/POWER(1024,3),'G') IB_GB
FROM (SELECT SUM(data_length+index_length) IB_BB
FROM information_schema.tables WHERE engine='InnoDB') A;

This will tell you what to set for innodb_buffer_pool_size or 75% of the DB Server's total memory, whichever is smaller.
See also

Jan 13, 2012 : What to set innodb_buffer_pool and why..?

MyISAM
You need to run this query:
SELECT
    CONCAT(KeyBuffer_BB/POWER(1024,1),'K') KeyBuffer_KB,
    CONCAT(KeyBuffer_BB/POWER(1024,2),'M') KeyBuffer_MB,
    CONCAT(KeyBuffer_BB/POWER(1024,3),'G') KeyBuffer_GB
FROM (SELECT SUM(index_length) KeyBuffer_BB FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will tell you what to set for key_buffer_size.
CAVEAT #1
Use common sense about using both buffers in relation to the total installed RAM (See my answer to What are the main differences between InnoDB and MyISAM?)
CAVEAT #2
Once you set these values and start mysql, please download mysqltuner.pl and run it. It will forecast how much RAM is need for all buffers and for connections (it terms of buffers joins, sort, reads)
CAVEAT #3
As for InnoDB, there are other settings you need to set for getting InnoDB to use
Multiple CPUs/cores:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

InnoDB Tablespaces for Individual Tables

Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Feb 22, 2012 : Ibdata usage and Recommendations?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Apr 19, 2012 : MySql - Clean ibdata1
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

and so much more


Use both together.

A common design strategy in screen-scraping is to separate the task of fetching data from the task of processing data.

Have one thread focused on just fetching the data. Store that data in rather raw form on one "scrape" table. You can let these accumulate as they may be useful in debugging problems. Occasionally truncate the table when old rows are no longer of interest.

Use another thread or another app to process those data samples. As you process each sample, mark the "scrape" row as processed, parse the raw scraped data, and normalize the data to put the repeating product info (description, etc) in a parent "product" table. Put the changing data (price, date-time sample taken) in a child "price" table.

Use the product code as the key fields linking the two tables if you are certain that is reliable: the code is truly unique and always will be. In my experience such conditions are never true, screw-ups happen and such codes are recycled or mistakenly re-assigned or eventually change when companies are re-orged or merged. So I always use a surrogate key of type UUID, but you may decide to stick with natural keys. Reasonable people disagree on this.

If you are truly concerned about scaling, specify your expected volume of data. A serious enterprise database such as Postgres can easily handle millions of rows, even on low-end computer hardware such as a Mac mini. (Though, for critical data you would of course be using a better machine with error-correcting memory and RAID storage.)


LEN() in most SQL distributions do not include spaces.  You would want to use something like DATALENGTH() for that.
https://stackoverflow.com/questions/1151693/len-vs-datalength-in-sql-server-2005
As for where the '.' and spaces came from, we would need more information like how is that field being populated?  What app, directly via SQL, etc.
Also, it would be helpful to know what distribution you are using.  I assumed SQL Server.


Optimisation select with large IN and millions of records in result

You might get bitmap index scans and better performance if you increase work_mem to something like 200MB or more, so that a bitmap for your table fits into it.
Other than that, the only remedy I can see is using more parallel workers by raising max_parallel_workers_per_gather.
But all these optimizations are questionable if you plan to run more than a single concurrent query on this tiny machine.
UPDATE:
Experiments with enable_seqscan = off suggest that PostgreSQL overestimates the cost of an index scan.
So if you lower random_page_cost to something closer to 1, PostgreSQL should choose the better plan automatically.


What should be the relationship between an adminstrators table and users table

From what I can tell from your image (please post ddl instead), there is no relationship between adminstrateur and technicien_sav. If you add an attribute created_by or administered_by in technicien_sav, there would be a relationship.  Since this attribute most likely is not part of the key for tech... , it would be referred to as a non-identifying relationship. Example:

CREATE TABLE adminstrateur
( adminstrateur_id ... not null primary key
, fullname ...
, ...
);

CREATE TABLE technicien_sav
( technicien_sav_id ... not null primary key
, ...
, adminstrateur_id ... not null
      references adminstrateur (adminstrateur_id)
);

I prefer using the same attribute name throughout the model, names like id, name, etc are IMO to vague. Even better is to use names from the domain of discourse, but I have no idea what the might be in your case.


You could use the LEFT() and RIGHT() functions to do this.

DECLARE @Folder varchar(20) = '/folder1/folder2/';

IF LEFT(@folder,1) = '/'
SET @Folder = RIGHT(@Folder ,LEN(@Folder)-1);

IF RIGHT(@folder,1) = '/'
SET @Folder = LEFT(@Folder ,LEN(@Folder)-1);

SELECT @Folder;

Result

(No column name)
folder1/folder2


Do you have 4 or more cores/processors machine? Current MySQL query cache implementation does not scale well and there is a mutex contention problem on systems with more thread (the query might spend more time waiting to check the query cache than it would need to be executed fresh if the relevant data are already in other buffers which allow parallel access).

Do you have cacheable queries? If most queries do not repeat (they contain some random or time-dependent constants etc. - imagine a query getting "active articles" with 1 second precision - such query would be cached and maybe returned for one second if it were fired multiple times, but next second a new version is queried with a different timestamp so it does not match and is executed anew - you get your QC full of these queries, each only effective for 1 second). Then there are those containing rand(), now() and similar functions which cannot be cached at all.

Transactions may collide with QC - when one query inside a transaction invalidates some table in the query cache, later selects to that table inside the same transaction cannot use query cache at all (because of the repeatable-read consistency - other transactions might fill the QC with data which would be stale from the viewpoint of this transaction).

Thousands of databases and millions of tables sounds like some kind of hosting - you probably have no way to fix the queries or guarantee they will be cacheable at all? It seems only small percent is and giving it more memory will only slow down invalidations, not help with hit ratio.


Does mysql "lock" (no other process can take from it) the memory allocated to it?

If you check kernel log, you should see OoM Killed messages.. Kernel will usually kill the largest offender which is MySQL in ourcase.


The problem has been solved with help from Neo4j support. Hopefully they will fix the bug so you will not face this issue. For some reason one line was not in the template used to install the Neo4j VM.
The problem was there was not a listener for all the Neo4j ports in the neo4j.conf file. Make sure they are there for all three ports (7473, 7474 and 7687). The format is:
dbms.connector.bolt.listen_address=0.0.0.0:7687

You might also need a default listener:
dbms.default_listen_address=0.0.0.0

Other insights learned in debugging this:

When you restart the VM, it may take several minutes for the Neo4j
Browser and python code capabilities to recycle and come up.
In Putty, the command "neo4j status" will return "neo4j is not running"
even when it is.


MySQL duplicate entry error opon import to new server

Per this response: mysqldump with INSERT ... ON DUPLICATE

It would be best to take a new backup with the --replace option, if you are certain that this will not destroy relational integrity of your dataset. This will drop the row that is creating conflicts and instead insert the row that is signalling the error and stopping the restore.

The issue is that you, somehow, have ended up with multiple rows with the same value in a column that enforces uniqueness. How this happened in the Galera server is unclear.

It may be useful to post the results of the following:

mysql> select max(t.count) from (select count(user_id) as count from <db>.<table> group by user) t;

If any row returns a value other than 1, then there's a relational problem on the originating server. If the value is equal to 1, there's more to explore on the restore-server in why it believes there is an issue. It could be that the backup was taken hot, and there are missing transactions which do not yield a consistent dataset.


Can we migrate Azure SQL Database's objects to In-Memory OLTP?

I cannot see how a write-heavy workload would be a good candidate for In-Memory OLTP on SQL Azure Database. That's because transaction log throughput is limited, no matter how many CPUs you have, and even in Business Critical:

https://www.brentozar.com/archive/2019/02/theres-a-bottleneck-in-azure-sql-db-storage-throughput/


Get all rows in table A even if no rows in table B in CROSS JOIN

You can use an outer apply instead of a cross join.

Using this simplified table structure.

create table T1(ID1 int);
create table T2(ID2 int);

Add three rows to T1

insert into T1 values(1);
insert into T1 values(2);
insert into T1 values(3);

Query with outer apply.

select T1.ID1,
       T2.ID2
from T1
  outer apply (
              select T2.ID2
              from T2
              ) as T2;

Result:

ID1         ID2
----------- -----------
1           NULL
2           NULL
3           NULL

Add one row to T2

insert into T2 values(1);

Result:

ID1         ID2
----------- -----------
1           1
2           1
3           1


Wrong estimate on a query on partitioned tables

The estimates (with the new cardinality estimator) are fine for a normal join, but are less accurate when the optimizer considers the option of a colocated join.
A colocated join (aka per-partition join) is available when joining two tables that are partitioned in the same way. The idea is to join one partition at a time, using nested loops apply driven by partition ids provided by a constant scan (in-memory table of values).
Regular join
Since the colocated join involves a nested loops apply, you can force the optimizer to avoid this by specifying OPTION (HASH JOIN) for example:

The two seeks in that plan are:
Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator((3)), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator((3)), Scalar Operator((20140802))

The optimizer has applied static partition elimination in both cases, giving accurate estimates for both seeks, and the following join.
Colocated join
When the optimizer considers a colocated join (as shown in the question), the seeks are:

Seek Keys[1]: Prefix:
    PtnId1000, [dbo].[A].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))
Seek Keys[1]: Prefix:
    PtnId1003, [dbo].[B].DateKey = Scalar Operator([Expr1006]), Scalar Operator((20140802))

...where [Expr1006] is the value returned by the Constant Scan operator.
The cardinality estimator now cannot see that the DateKey value and the partition id are interdependent, as it could when literal constants were used. In other words, it is not apparent to the estimator that the value inside [Expr1006] specifies the same partition as DateKey = 20140802.
As a consequence, the CE chooses (by default) to estimate the selectivity of the two (apparently independent) predicates using the normal exponential backoff method.
This explains the reduced cardinality estimates feeding the join. The lower apparent cost of this option (due to the misestimate) means the optimizer chooses a colocated join instead of a regular join, even though it is obvious (to humans) that it offers no value.
There are several ways to work around this gap in the logic, including using the query hint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'), but this will affect the whole query, not just the problematic colocated join alternative. As Erik notes in his answer, you could also hint the use of the legacy CE.
For more information about colocated joins, see my article Improving Partitioned Table Join Performance


SQL Server 2008 - How did I solve the need to check the column definitions quickly?

Run the following script to create the function:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'getColDef')
    DROP FUNCTION dbo.getColDef;
GO

CREATE FUNCTION getColDef (@TAB_AND_COL VARCHAR(256))
RETURNS @RESULT TABLE
   ([Table] varchar(256),
    [Column] varchar(256),
    [Type] varchar(64),
    [Length] int,
    [IsNullable] varchar(5),
    [FK_Name] varchar(256),
    [Index_Name] varchar(256),
    [Trigger_Name] varchar(256))
AS
BEGIN
   DECLARE @TNAME VARCHAR(256), @COL VARCHAR(256), @CHARIX INT

    SET @CHARIX = CHARINDEX('.', @TAB_AND_COL)
    SET @TNAME = LEFT(@TAB_AND_COL, CHARINDEX('.',@TAB_AND_COL)-1)
    SET @COL = SUBSTRING(@TAB_AND_COL, @CHARIX + 1, LEN(@TAB_AND_COL) - LEN(@TNAME))

    INSERT INTO @RESULT
        SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) [Table],c.name [Column],t.name AS [Type],c.max_length AS [Length]
            , CASE C.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable]
            , FK.FK_NAME as FK_Name
            , IDX.IdxName as Index_Name
            , trig.name as Trigger_Name
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id=t.user_type_id
        LEFT JOIN
            (SELECT f.name AS FK_NAME
                , OBJECT_NAME(f.parent_object_id) AS FK_TABNAME
            FROM sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
            WHERE OBJECT_NAME(f.parent_object_id) = @TNAME
            AND COL_NAME(fc.parent_object_id,fc.parent_column_id) = @COL) FK
                ON FK.FK_TABNAME = OBJECT_NAME(c.OBJECT_ID)
        LEFT JOIN
            (SELECT i.Name AS IdxName, OBJECT_NAME(i.object_ID) AS TName, c.Name AS CName
            FROM  sys.indexes i
            JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
            JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
            WHERE c.name = @COL
            AND OBJECT_NAME(c.object_id) = @TNAME) IDX
                ON IDX.TName = OBJECT_NAME(c.object_id)
        LEFT JOIN sys.triggers trig ON trig.parent_id = c.object_id
        WHERE C.object_id = object_id(N''+ @TNAME +'') AND c.name = @COL

    RETURN
END

To execute the function, simply run the following script:

SELECT * FROM getColDef('TABLENAME.COLUMN_NAME')

Similarly, you can get the column definitions for an entire table by executing the following script:

DECLARE @TABLENAME VARCHAR(256)
SET @TABLENAME = 'TABLENAME' -- Put the name of the table here

DECLARE @RESULTS TABLE([Table] varchar(256),[Column] varchar(256),[Type] varchar(64),[Length] int,[IsNullable] varchar(5)
    ,[FK_Name] varchar(256),[Index_Name] varchar(256),[Trigger_Name] varchar(256))
DECLARE @ROW_NOW INT, @ROW_MAX INT, @COLNAME VARCHAR(256)

SELECT @ROW_NOW = MIN(ROWNUM), @ROW_MAX = MAX(ROWNUM)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC

WHILE @ROW_NOW <= @ROW_MAX
BEGIN
    SELECT @COLNAME = name FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC
    WHERE ROWNUM = @ROW_NOW

    INSERT INTO @RESULTS
    SELECT * FROM getColDef(@TABLENAME+'.'+@COLNAME)

    SET @ROW_NOW = @ROW_NOW + 1
END

SELECT * FROM @RESULTS ORDER BY [Column]


Breaking down your example, we have:

IFNULL(A,B)

This means: Evaluate A, IF A <> Null return A Else Evaluate and return B

CASE WHEN A THEN A
    WHEN B THEN B
END

Which means: Evaluate A if true then evaluate and return A (since we are using A as condition and return expression above). If B then evaluate and return B.

As such, it is best to use IFNULL if the condition we are testing for null is also the return value. Using case is better if you have a condition to test and a different return value.

Having said all that, your first example seems to be using nested IFNULLs. However, just doing IFNULL(**Should send message query**, **Should check inbox**) may be be enough since it just needs to get one or the other or null.

Finally, Are you running this statement for multiple records? If so, that may be the cause of your high CPU as putting sub-selects in the select clause will cause correlated sub-selects to run for each record (i.e if you are processing 1000 entries with your query, each sub select in the query will run 1000 times). At that point I would say to try to re-write the query to first get all the values for inbox and message (maybe two subqueries in the from clause or put them in a temp table) and then do the ifnull at the end. In the end, as usual check the execution plan of your query and see what's going on .


Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table

I think you can solve this with two UNIQUE partial indexes:

CREATE UNIQUE INDEX concept_status_accepted_upx
ON temp (concept_id)
WHERE status = 'accepted' ;

CREATE UNIQUE INDEX concept_status_basionym_upx
ON temp (concept_id)
WHERE status = 'basionym' ;

This will enforce that there is only one 'accepted' per concept and only one 'basionym' per conecpt.

Not sure if you want the extra but tt will NOT enforce that every concept has an 'accepted' taxonomy. This is much harder to do with DRI and the restriction of a single table.


How to capture client host name in postgresql?

You need to set log_hostname to on in postgresql.conf