I see this on my SQL Server 2016 instance as well (same settings as yours: 15 min data flush, and 1 hour stats collection).  I noticed that the plans with missing information correlated with AG failovers and maintenance-related reboots (which I found by looking in the SQL Server error log).
If you are following the best practices related to "mission critical servers" like I am, you may have taken these steps:

Use trace flags on mission critical servers
The global trace flags 7745 and 7752 can be used to improve availability of databases using Query Store. For more information, refer to Trace Flags.

Trace flag 7745 will prevent the default behavior where Query Store writes data to disk before SQL Server can be shut down. This means that Query Store data that has been collected but not been yet persisted to disk will be lost.

Trace flag 7752 enables asynchronous load of Query Store. This allows a database to become online and queries to be executed before the Query Store has been fully recovered. The default behavior is to do synchoronous load of Query Store. The default behavior prevents queries from executing before the Query Store has been recovered but also prevents any queries from being missed in the data collection.

This explains all of the missing runtime stats in my setup.  While it's not explicitly called out in this article, it appears that the queries and plans might get into the query store without aggregated runtime stats.  This might vary based on your "Data Flush Interval" and "Statistics Collection Interval."


How can I speed up this 2m5s query that has indices?

Your query is equal to

select /* urls.id */ s1.url_id as urlId,
    count(case when s1.hit_type = 0 then 1 end) as aCount,
    count(case when s1.hit_type = 1 then 1 end) as bCount,
    count(case when s1.hit_type = 2 then 1 end) as cCount,
    count(distinct s1.source_id) as sourcesCount
from /* urls join */ stats s1 /* on urls.id = s1.url_id */
where s1.hit_date >= '2017-12-12'
group by /* urls.id */ s1.url_id
order by aCount desc
limit 0,100;

except in your query output there are only records which "pairs" exists in urls table.

But the constraint

CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`)

do not allow those records.

So my query is absolutely equal to your one, and you can use it instead.

To increase this query speed you may create covering index

ALTER TABLE stats ADD INDEX idx (url_id, hit_date, hit_type, source_id)

And the best way is to move url_id to a separate table and replace it with a reference of numeric type (grouping by VARCHAR field is expensive).

Additionally - count(case when s1.hit_type = N then 1 end) can be replaced with short SUM(s1.hit_type = N).

To speed up the whole query I'd recommend try to divide it to 4 separate queries:

SELECT urlId,
       MAX(aCount) aCount,
       MAX(bCount) bCount,
       MAX(cCount) cCount,
       MAX(sourcesCount) sourcesCount
FROM (  select  s1.url_id as urlId,
                COUNT(*) as aCount,
                0 as bCount,
                0 as cCount,
                0 as sourcesCount
        from stats s1
        where s1.hit_date >= '2017-12-12' AND s1.hit_type = 0
        group by s1.url_id
      UNION ALL
        select  s1.url_id, 0, COUNT(*), 0, 0
        from stats s1
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 1
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, COUNT(*), 0
        from stats s1
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 2
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, 0, count(distinct s1.source_id)
        from stats s1
        where s1.hit_date >= '2017-12-12'
        group by s1.url_id
    ) x
GROUP BY urlId
order by aCount desc
limit 0,100;

The index by (url_id, hit_type, hit_date) will speed up first 3 subqueries, and by (url_id, hit_date, source_id) will speed up the last subquery.


MySQL query to create a Matrix view using pivot

Example pivot tracking table

CREATE TABLE IF NOT EXISTS `mail_track` (
`ID` int(10) NOT NULL auto_increment,
 MemberID mediumint(7) default NULL,
`MessageTitle` varchar(20) default NULL,
`Type` varchar(6) default NULL,
`URLID` varchar(15) default NULL,
`DateTime` datetime default NULL,
`IP` varchar(15) default NULL,
PRIMARY KEY  (`ID`),
KEY `DateTime` (`DateTime`,`Type`,`MessageTitle`,`MemberID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=199952834 ;

pivot query

SELECT
SUM( IF( mail_track.Type = 'Open', 1, 0 ) ) AS Opens,
SUM( IF( mail_track.Type = 'Click', 1, 0 ) ) AS Clicks,
SUM( IF( mail_track.Type = 'Sent', 1, 0 ) ) AS Sent,
DATE(DateTime)
FROM mail_track GROUP BY DATE(DateTime)


My initial playing around with various queries suggested no pattern at all but on paying closer attention it appears to be predictable for serial plans. I ended up at KB314648 which @AustinZellner mentions:

  Each SQL Server connection has an associated process status structure
  (PSS) that maintains connection-specific state information. Each
  unique server process ID (SPID) in the sysprocesses system table
  represents a different PSS, and the information in the sysprocesses
  virtual table is a "view" into this status information.

And the section relevant to your question:

  If STATISTICS IO is enabled for a connection, SQL Server allocates an
  array during query execution to track IO information on a per-table
  basis. As SQL Server processes the query, it records each logical
  request for a page in the appropriate table's entry in this array,
  along with whether that logical IO request resulted in a physical IO.
  SQL Server returns the information, at the end of the query, in error
  message 3615.

The observed behaviour suggests that entries are made to the array in the order that IO is generated, essentially the result of GetNext() on a physical operator. The last entry in the statistics output is the first table that resulted in an IO being recorded, the first entry is the last table. I’d speculate that the order for parallel plans is not predictable (or less so) as there is no guarantee as to which parallel task will be scheduled first.


First, stay off enums for things like this. Enum values can never be removed, so use them only if you are sure that that will never be necessary, which doesn't seem the case here.

Anyway, I would say that your design is too complicated, and still lacks the crucial feature of referential integrity.

Use a junction table for each pair of objects that can be related. This way, you

make clear which objects can be related
can have referential integrity

Having many tables is something that a database is good at. If you contend that you have 1000 tables and each object can be related to each other, that would be too many tables. But in that case you should probably go for a model where you have not a table per object type anyway.


pgBouncer works great but occasionally becomes unavailable

The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)

The range of socket ports can be output by cat /proc/sys/net/ipv4/ip_local_port_range. The default value on a stock Linux kernel is generally 32768 61000.

You may check the result of netstat -ton|grep WAIT on the client(s) and on the pgBouncer's host when the system is busy. The -o flag will show the timeout
counters related to wait states.

If the total number of TCP sockets is close to 61000-32768=28232 then exhaustion of this range is likely your problem. Since a closed socket spends 60 seconds in TIME_WAIT state in normal condition, if  a client host connects more than 28232 times in one minute, new connections will fail with the mentioned error until ports are freed.

As a first workaround, the TCP ports range may be extended:

 # echo "1025 65535" >/proc/sys/net/ipv4/ip_local_port_range

If it's not satisfactory, check the tcp_tw_recycle and tcp_tw_reuse flags, also tunable through /proc/sys/net/ipv4 and sysctl.

They're defined as (from man tcp):

       tcp_tw_recycle (Boolean; default: disabled; since Linux 2.4)
              Enable  fast  recycling  of  TIME_WAIT  sockets.   Enabling this
              option is not recommended since this causes problems when  work‐
              ing with NAT (Network Address Translation).

       tcp_tw_reuse (Boolean; default: disabled; since Linux 2.4.19/2.6)
              Allow  to reuse TIME_WAIT sockets for new connections when it is
              safe from protocol viewpoint.  It should not be changed  without
              advice/request of technical experts.

Personally I had success with tcp_tw_recycle when faced with this problem with a MySQL client app, but don't take this as a recommendation, my understanding of TCP being superficial at best.


SQL View for Items not sold last six months

Is this what you're looking for?

DECLARE @ItemMaster TABLE (ItemNo INT)
DECLARE @SalesDataMart TABLE (ItemId INT,TranDate DATE)

insert into @ItemMaster (ItemNO) values (1)
insert into @ItemMaster (ItemNO) values (2)

insert into @SalesDataMart (ItemId, TranDate) values (1,'2017-02-17')
insert into @SalesDataMart (ItemId, TranDate) values (2,'2016-02-17')
SELECT *
FROM @ItemMaster itmmst
WHERE NOT EXISTS (
        SELECT *
        FROM @SalesDataMart
        WHERE itemid = itmmst.itemno
            AND Trandate >= Dateadd(month, - 6, sysdatetime())
        )


SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables

Your table structure looks like this?:

Table1
id (identity)
data1
data2
data3

Table 2
id (identity)
t1id (fk to table1)
data1
data2
...

-- Main Table insert
INSERT INTO table1 (data1,data2,data3)
VALUES('dummy1','dummy2','dummy3');

--get last inserted id
var=SELECT SCOPE_IDENTITY()

-- dependent insert into table2
INSERT INTO table2 (t1id,data1,data2,...)
VALUES(var,'dummy1','dummy2','...');

Look at this answer.
If you return the id from the newly inserted row of Table1 you could use that to insert the dependent row into Table2 without querying Table1 for every row.
If the select for the newly generated id is your bottleneck this should help. If you're full scanning your main table for every row, indexing the id column, if not already done, could also decrease the time needed for the lookup.


SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance?

Just check the properties of the model database - everything else being equal a new database is created using the model database as a template.
If you want to change the default size, resize your model database - job done.
Note however if the database creation script supplies a specific value that will take precedence.
re-read the question including your title and I think you want the results of running
exec sp_helpdb

or try if you want to see the space available
exec sp_msforeachdb "use [?]; exec sp_spaceused"


Ahh, I had a typo in the name of my database... There was nothing wrong with the credentials!


The documentation you have already read says it pretty well. To explain further:

  The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views.

Normally an update acts on a single table. To avoid tortuous subqueries in the filter, Oracle allows you to update a view (or subquery) as long as it is still able to easily map the changes you are making onto real underlying rows in a table. This is possible if the set clause only modifies columns in a 'key preserved' table:

  A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

For example:

create table foo( foo_id integer primary key, foo_val integer not null );
create table bar( bar_id integer primary key, bar_val integer not null,
                  foo_id integer not null references foo );

update (select * from foo join bar using(foo_id)) set foo_val=1;
ORA-01779: cannot modify a column which maps to a non key-preserved table

update (select * from foo join bar using(foo_id)) set bar_val=1;
0 rows updated.

the first update fails because Oracle has no way of 1:1 mapping foo_val in the query to foo_val in foo - conversely the second update succeeds because Oracle can 1:1 map each bar_val to  bar_val in bar. The important thing is that foo_id is unique in foo - so for each row in bar, there can only be at most one corresponding row in foo (actually exactly 1 in this example, but the same applies for a nullable foreign key - the point is that there is never more than one row).


Windows Server 2019 non-owner node cannot see Cluster Name or IP

I managed to fix this by enabling HA ports in the Azure Load Balancer (standard Load Balancer). That seems to have fixed the issue.
By the way I have SQL Server 2019 Enterprise on Windows Server 2019.
Set the following Load Balancing rule in the Azure Load Balancer for WFCS.

The source of this information for this article : https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial#add-the-cluster-core-ip-address-for-the-windows-server-failover-cluster-wsfc
The article does not talk about HA ports because it is a Standard Azure Load Balancer feature and the article was written using a Basic  Azure Load Balancer.


Why can't I insert this value using timestamp but can with datetime?

If you want to catch every hour that exists, have a sequence of integers and do

x + INTERVAL y HOUR

Where x is some constant base time and y is the sequence of hours.  The result of the sum is a valid hour, suitably converted according to DST hours, leap years, etc.


SQL Server Instance How to recover memory used

Is this Normal?

Yes.

my boss and colleagues state that this is not normal behaviour for the database

SQL Server will grow its memory usage up to your configured max server memory and only reduce its memory usage if the system runs low on physical memory.
If the system has plenty of available RAM, SQL Server will use it to cache data and execution plans, and to have memory on-hand to run queries.
If Windows sends out a global "low memory" signal SQL Server responds by triming its caches and de-allocating memory.
This is explained in detail in the docs:

The default memory management behavior of the SQL Server Database
Engine is to acquire as much memory as it needs without creating a
memory shortage on the system. The SQL Server Database Engine does
this by using the Memory Notification APIs in Microsoft Windows.
When SQL Server is using memory dynamically, it queries the system
periodically to determine the amount of free memory. Maintaining this
free memory prevents the operating system (OS) from paging. If less
memory is free, SQL Server releases memory to the OS. If more memory
is free, SQL Server may allocate more memory. SQL Server adds memory
only when its workload requires more memory; a server at rest does not
increase the size of its virtual address space.
Max server memory controls the SQL Server memory allocation, compile
memory, all caches (including the buffer pool), query execution memory
grants, lock manager memory, and CLR1 memory (essentially any memory
clerk found in sys.dm_os_memory_clerks).

Memory Management Architecture Guide - Dynamic Memory Management

Is there a way to release memory without rebooting the instance?

You can reduce the max server memory and SQL Server will trim its caches and reduce its memory usage online.  After it drops, you can increase max server memory again and SQL Server will re-grow the caches over time.
But this procedure is operationally pointless.  The memory isn't needed for anything else, and if it were, SQL Server would respond to the system low memory signal by reducing its memory utilization.


Migrating PostgreSQL DB from AWS to Alibaba Cloud

You can downgrade postgresql database from the command line.

Simply extract the database with a

pg_dump --host=... --port=... --dbname=.... --user=.... >dump

command. Then you will have your database in the file dump in your local directory.

Then insert the dump into your new database with the command

psql --host=... --port=... --dbname=.... --user=.... <dump

It won't work at first. Typically, you will have syntax errors or similar. But get a good text editor, and edit it all out, until it won't be clean.


innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

  As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two ways:

Upgrade to MySQL 5.5 (All InnoDB Plugin enhancements are part of MySQL 5.5)
Upgrade the InnoDB Plugin

Once you have done so, you can enhance InnoDB to

access more CPUs and Cores
increase read and write I/O threads
scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian


PostgreSQL - Create table as select with distinct on specific columns

"VAL_X" and "VAL_Y" chosen through some aggregate function

You should consider using GROUP BY for the columns whose values you consider that should be "distinct" (as a group), and, for the rest of columns, choose an appropriate aggregate function (for instance, MIN):

CREATE TABLE my_result AS
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  tableA
WHERE
  true /* your condition goes here */
GROUP BY
  city, street, streetnum

If you need to put together values from several tables, UNION ALL of them before you GROUP BY:

CREATE TABLE my_result AS
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  (
  SELECT city, street, streetnum, val_x, val_y FROM tableA
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableB
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableC
  ) AS s0
WHERE
  true /* your condition goes here */
GROUP BY
  city, street, streetnum ;

Using always "VAL_X" and "VAL_Y" from same row, using a WINDOW

If you need to make sure your values are always from the same row, the best way is to use a WINDOW in your query: PARTITION BY "CITY", "STREET", "STREET_NUM" and ORDER BY "VAL_X", "VAL_Y", and choose the first row of every partition.

You can do this with two steps:

1) Add the row_num() to every partition:

SELECT
  *,
  (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
FROM
  table_a

  |  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y | rn |
  |-------|------------|------------|-------|-------|----|
  | CityA | Street abc |          5 |  11.5 |   0.5 |  1 |
  | CityA | Street abc |          5 |  12.4 |   2.8 |  2 |
  | CityA | Street abc |          5 |  15.4 |   1.8 |  3 |
  | CityB | Street xyz |         18 |   5.4 |   1.9 |  1 |
  | CityB | Street xyz |         18 |   8.4 |   1.1 |  2 |
  | CityC | Street klm |         55 |   9.6 |   0.8 |  1 |

2) At this point, choose only the rows WHERE rn=1 (and ORDER them, if necessary):

SELECT
   "CITY", "STREET", "STREET_NUM", "VAL_X", "VAL_Y"
FROM
  (
  SELECT
    *,
    (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
  FROM
    table_a
  ) AS table_a_grouped
WHERE
  rn = 1
ORDER BY
  "CITY", "STREET", "STREET_NUM"

The result is:

|  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y |
|-------|------------|------------|-------|-------|
| CityA | Street abc |          5 |  11.5 |   0.5 |
| CityB | Street xyz |         18 |   5.4 |   1.9 |
| CityC | Street klm |         55 |   9.6 |   0.8 |

You can see the example at SQLFiddle


For starter there is no such thing as the 'current count' to store in a variable. A query like SELECT COUNT(*) FROM ... is subject to the current isolation level and all concurrent pending transactions. Depending on the isolation level, the query can see or not see rows inserted or deleted by pending uncommitted transactions. The only way to answer is to count the rows that are visible to the current transaction.

Note that I did not even touch the even more thorny subject of concurrent transactions that start or end during the count. Not to mention rollbacks...


Getting the row before a row with a certain value in SQL

The steps to do this are actually pretty simple in any SQL variant that supports LAG:

Pull out the previous action_name using LAG, in a derived table or CTE.
Filter on the outside to only rows which contain 'reference_action', these rows will also have the previous action from the first step. You want to do this on the outside, otherwise you won't get the correct LAG values.
Group by the previous action, and count.

SELECT
    prevAction AS action,
    COUNT(*) AS [count]
FROM (
    SELECT *,
        LAG(action_name) OVER (ORDER BY timestamp) AS prevAction -- you may want a PARTITION also
    FROM YourTable
) AS t
WHERE action_name = 'reference_action'
GROUP BY prevAction;


Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

The possible reason for RAM consumption can be here:
[!!] Temporary tables created on disk: 85% (249K on disk / 290K total)
When automatic temporary table is created it is always created with ENGINE=MEMORY first. If it doesn't fit the max_heap_table_size = 134217728 limit, it will be transferred on disk for further proceeding. Temptables are associated with session/connection so each thread can create a number of 128MB large temptables in the RAM - in addition to the buffers allocated for reads/joins/sorts.
I suggest to decrease max_heap_table_size to some reasonable value like 16M as far as in 85% cases temptables don't fit 128M anyway.
Another suggestion is to optimize your queries having longest execution times (due to the slow on-disk temptables operations). Slow queries log is a good starting point.


What's the difference between data definition language and data description language?

DDL (Data Definition Language): Used for defining data structures and schema like CREATE and ALTER commands.

DML (Data Manipulation Language): Used for managing data with schema objects like SELECT commands.

DCL (Data Control Language): Used to control data like REVOKE and GRANT commands.

TCL (Transaction Control Language): Used to manage the changes made by DML statements like COMMIT.

Reference Link


How can I join msdb.dbo.sysalerts to msdb.dbo.sysoperators?

You can use this query:

SELECT *
FROM msdb.dbo.sysalerts AS Ale
INNER JOIN msdb.dbo.sysnotifications AS Noti ON Ale.id = Noti.alert_id
INNER JOIN msdb.dbo.sysoperators AS Ope ON Noti.operator_id = ope.id


MySQL DNS issue: skip-name-resolve connection fast but cannot log into email

Workaround:

Keeping skip-name-resolve in place, I changed shunned users having non-IP number hostnames in the users table as follows:

UPDATE mysql.user SET Host='127.0.0.1' WHERE Host='localhost' AND User='username';
UPDATE mysql.db SET Host='127.0.0.1' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;

I will delve into the DNS problem when I have a spare weekend.

(Changed % to 127.0.0.1 as requested in a comment, as with % everyone would be able to access MySQL with username, with no IP checks. localhost is resolved as 127.0.0.1 in most cases (see your /etc/hosts). - [ed.])


Yes, create a new record for every reading.

MongoDB and other document stores are designed for efficient retrieval of de-normalized or irregularly structured data. A MongoDB collection is essentially a set of key-value pairs with a rich value type. Updating the value is an atomic operation, which means that constantly appending to a list within the value is not going to be efficient and, as you mentioned, you will eventually hit the document size limit.


Postgresql numeric and decimal is automatically rounding off

It seems to be the default behaviour of SQL Workbench, have a look at this article:

How do I change the resolution or scale of decimal data type on SQL Workbench.

Quoted from the article:

  Normally, SQL Workbench doesn't display the decimal data with the full
  scale. By default the scale is 2. We can change the scale by the
  setting.

  Solution

  Open Data formatting settings. SQLWorkBench -> Preferences ->Data formatting
  Change Decimal digits The default is 2. In this case, it should be at least 11.

And according to SQL Workbench manual about Data formating:

  Decimal digits

  Define the maximum number of digits which will be displayed for
  numeric columns. This only affects the display of the number, not the
  storage or retrieval. Internally they are still stored as the DBMS
  returned them. To see the internal value, leave the mouse cursor over
  the cell. The tool tip which is displayed will contain the number as
  it was returned by the JDBC driver. When exporting data or copying it
  to the clipboard, the real value will be used.

  If this value is set to 0 (zero) values will be display with as many digits as available.

(Bold is mine)


will table partitioning and compression help heavily used OLTP database?

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc.

  Due to heavy blocking, we can't purge data while the database is
  online.

Table partitioning is exceptionally helpful in many environments, especially those with large data sets. Not only can you avoid locking issues and improve query performance, but you can reduce log impact by using Truncate after swapping out your partitions when purging old records.

See this free training by Kendra Little with Brent Ozar Unlimited for a detailed guide about table partitioning in SQL Server which, incidentally, is focused on OLTP environments.

As for data compression, see this SQL Server Customer Advisory Team white paper for data compression implementation. You will save disk space, and reduce I/O at the cost of CPU usage... This trade-off is something that you need to plan and test for before blindly implementing.


Free space of tablespace deleting table content (rows)

Without understanding the nature of the data contained within this object contains, it is difficult to offer solid advice.

If this were a regular table, then yes; you would delete rows from it and thereby release space within the Tablespace.
The fact that a delete statement results in more data being written to this object makes me think it is non-standard; perhaps an "Auditing" feature of some sort that records everything - including deletions.  That means your only option is to give the Tablespace more room to work with, by adding more datafiles to it or by extending the underlying disk space.

Thinking about performing either operation without your DBA?

My recommendation: Don't.

Very little about Oracle database is as straightforward as we would like it to be, so you need someone who knows what they're doing (and knows what to do when it goes wrong).

Furthermore, you shouldn't even be able to!

If your DBA has given you sufficient privileges to even attempt this operation, then they need their head examining, having little or no concept about database Security (or, most generously, a highly over-developed sense of Trust).


Here the more clear and powerful model for you domain


How can I fake inet_client_addr() for unit tests in PostgreSQL?

inet_client_addr() is a system information function.
It is located in the schema pg_catalog like other built-in functions (except for additional modules).

pg_catalog is automatically part of the search_path. Per documentation:

  In addition to public and user-created schemas, each database contains
  a pg_catalog schema, which contains the system tables and all the
  built-in data types, functions, and operators. pg_catalog is always
  effectively part of the search path. If it is not named explicitly in
  the path then it is implicitly searched before searching the path's
  schemas. This ensures that built-in names will always be findable.
  However, you can explicitly place pg_catalog at the end of your search
  path if you prefer to have user-defined names override built-in names.

Bold emphasis mine.
So we create a dedicated schema and place it before pg_catalog in the search_path:

CREATE SCHEMA override;

CREATE OR REPLACE FUNCTION override.inet_client_addr()
  RETURNS inet AS
$func$
SELECT '127.0.0.1'::inet
$func$ language sql STABLE;

SET search_path = override, pg_catalog, public;

Then your call finds your custom override-function first:

SELECT inet_client_addr();

SQL Fiddle

Make sure, unprivileged users cannot create objects in the override schema, or they can play all kinds of tricks on you. That's not the case by default. Per documentation:

  No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces.

Bold emphasis mine.

Care is needed if the same user should be allowed to create objects in the database.
Per documentation:

  The first schema named in the search path is called the current
  schema. Aside from being the first schema searched, it is also the
  schema in which new tables will be created if the CREATE TABLE command
  does not specify a schema name.

Always specify a schema name for CREATE commands, and disallow crating object to all by default to rule out mistakes.


How to avoid primary key violation while doing inserts

use not exists

declare @batchSize int = 1000
INSERT INTO [dbo].[Destinationtable]
   ([PacketId]
   ,[DeviceId]
   ,[PacketDate]
   ,[PacketNumber]
   ,[Processed]
   ,[CreateDate]
   ,[NewReadingId])
SELECT top (@batchSize)
   [PacketId]
  ,[DeviceId]
  ,[PacketDate]
  ,[PacketNumber]
  ,[Processed]
  ,[CreateDate]
  ,newid()
FROM [dbo].[SourceTable]
where Processed is null
  and not exists (select 1 from [Destinationtable]
                  where [PacketId] = [SourceTable].[PacketId])
order by CreateDate


What happens when two separate processes perform a full backup against the same database?

Will these applications interfere with each other if they are both processing the same database?

Yes they would affect each other, in terms of blocking, when running "concurrently", the one would block the other. But if they are not running concurrently like you schedule copy_only backup after full backup or before full backup, copy_only full backup is not going to affect full backup and you recovery planning. Basically copy_only full backup just backups up the data without changing the differential base and is not going to affect the restore procedure you have planned.

As a fact a lot people use copy_only backups to get replica of database when its required urgently without affecting the total backup and restore sequence.

Few points:

A full backup would block other concurrent full or copy only full backup.
A full backup would not block transaction log backup if you are using SQL server 2008 and above. But log will only truncate when full backup finishes. Paul has series about backup I suggest you read it.
A differential backup would block concurrent differential backup.
A log backup would block concurrent log backup.
If you are using SQL Server 2008 and above you can run transaction log backup when any of full or differential backup is running.

This blogs.msdn article has details

  but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

If you are running concurrently the one started first will block other and the other will finish after the fist one allows it to start. But at the end both will finish

If you are not running concurrently, both will just finish fine. Unless there is some other issue.

In both cases the integrity of backup is NOT compromised.


How can I determinate the pc specs I need for my database?

300 orders per day for 10 years is about a million rows. Assuming 10KB of data per sale (probably will be a lot less), that's 10GB of disk space. As for how to check your assumptions about disk usage and throughout, you will need to write some test scripts to create those million sales and measure latency for each transaction with an increasing number of concurrent connections.
A Raspberry Pi would be adequate for a volume of data that small.
My advice is to start with an Oracle free tier VM and see if it is responsive enough for you. If it isn't, get a VM from Scaleway for €3/month. Or, if your client is really welded to the idea that he must have the server on site, get a Raspberry Pi.
Whatever solution you go with, make sure you have reliable, frequent backups.
In terms of how to work out the resources, work out how many transactions an order creates. If it is 1 transaction, and you have 300 orders per day, that is 300 transactions per day. 300 transactions per second would be relatively low throughput.
What you really need to test is response time when somebody is using the system. With such low throughput, your requirements will be dictated by response latency, not throughput for 300 transactions per day.


Better ways to update columns of a table than using Merge Statement

Assuming your second update needs to only work on the records that were just updated in the merge statement, the following should do the trick:

MERGE INTO request tgt
  USING vmi_dimcustomer src
    ON (tgt.cust_no = src.customer_num AND tgt.is_checked IS NULL)
WHEN MATCHED THEN
  UPDATE SET tgt.first_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_first_name END,
             tgt.last_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ELSE src.cust_last_name END,
             tgt.error_code = CASE WHEN src.cust_first_name IS NULL THEN 2 ELSE 0 END,
             tgt.error_desc = CASE WHEN src.cust_first_name IS NULL THEN 'Customer Not Found' ELSE NULL END,
             tgt.is_checked = CASE WHEN src.cust_first_name IS NULL THEN 0 ELSE 1 END;

COMMIT;

This works by checking the results of the join between the source and target tables and using the relevant values for the columns being updated, depending on whether the first_name column in vmi_dimcustomer is null or not.


Index rebuild at partition level on a table

How can I do the same process for a partitioned table?

While you can rebuild a single partition, you can't disable just one partition.

So you would use a staging table on the same partition scheme.  Disable its indexes, load it, rebuild the indexes, and then switch one or more partitions into the main table.


DB user restricted permissions vs Docker?

Given a brutal super user command or DROP TABLE / DROP DATABASE as likely to be the SQL injection commands for the malicious hacker, you may as well defend yourself against those. No point invoking a data recovery procedure pre-emptively.


Where MySQL is taking time either in disk read or computation?

Rule of Thumb:  If the necessary data is cached, the query will run 10x faster than if not.  I have seen this in a wide variety of queries.  (Of course, the "10" varies a lot, too.)

Assuming

 SELECT yeard, COUNT(*) FROM t GROUP BY yeard;

 INDEX(yeard)

The Optimizer will scan the entire INDEX(yeard) of 150M 'rows'.  It does not have to do much more (at a high level) than count rows until yeard changes.  That is the INDEX and the GROUP BY work well together; they are not two separate steps.

About 400 index entries per 16KB block.
100 blocks/second is a more realistic estimate for InnoDB hitting HDD.  (This goes away for any blocks that are cached.)
150M/400/100 = 3750 seconds.  So, 54s for SSD seems about optimistic.  Or some of the blocks were cached.

If you run the query a second time (after all the blocks are cached), it may run 10x faster.

It's hard to say how many CPU cycles a query will take.  The code to "get the next row" (even of an index) is somewhat generic, and meanwhile, it is building (probably) an in-memory hash of the results.  There are also steps to parse the query, decide how to optimize it, deliver the results, etc.

The EXPLAIN in this example will say "Using index", meaning that it only used the index's BTree and did not need to touch the data's BTree.

The BTree is really a B+Tree, meaning that consecutive blocks are linked, making linear scans (as with your query) efficient.


Dynamic SQL:

DECLARE @SQL nvarchar(max);

SELECT
   @SQL = IsNull(@SQL + ' OR ', '') + QuoteName(Column_Name) + ' IS NULL'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   Table_Name = 'YourTable'
   AND Table_Schema = 'YourSchema';

EXEC ('SELECT Count(*) FROM YourSchema.YourTable WHERE ' + @SQL);

Another way:

SET ANSI_NULLS OFF; -- deprecated in SQL 2005 - 2012, will eventually be removed

SELECT Count(*) FROM YourSchema.YourTable WHERE NULL IN (Col1, Col2, Col3, Col4 ... Col80);

And yet another way, assuming your column names don't have space character in them:

In SSMS, navigate to the table in the object browser and drop open the column list. Drag the "Columns" folder to a query window. Use search and replace to change all the spaces in the selection to IS NULL OR. Prepend the following code and run it!

SELECT Count(*) FROM YourSchema.YourTable WHERE [put modified column list here]


Authentication only controls access to clients connecting to a MongoDB deployment. Securing remote access to your server or encrypting your data files are separate security measures.

If an attacker is able to gain access to a shell on your server and run privileged commands (like restarting the mongod service), access control is irrelevant. Similarly, even if your data files were encrypted an attacker with privileged access will be a major security problem.

As the administrator of a server, it is your responsibility to have appropriate access control, firewalls, auditing, and other security measures in place. Security involves multiple layers of defense, and access control is only a starting point. At a minimum you should also enable network encryption (TLS/SSL) and appropriately limit network exposure.

For a list of recommended security measures, see the MongoDB Security Checklist.

  Now, if I restart Mongo without the --auth parameter, I shouldn't be able to connect to Mongo using the localhost exception.

If you restart MongoDB without the --auth parameter (or equivalent configuration file directive), you are explicitly disabling access control and allowing clients to connect without requiring authentication. This may be useful for administrative intervention, such as resetting a forgotten admin password.


I find these multiple closings with age=0 worrying

No, this whole log looks fine and tends to show that pooling is working.

These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with milliseconds appearing in that log.

A line 1, a client connects to pgBouncer.

At line 2, there's a first connection made with a new postgres backend.

At line 3, the client quits.

At line 4, a new client arrives and no new connection is made with a backend, which implies that the previous one is reused as expected.

At line 8, a second server connection (LOG S) is made with a new postgres backend, which is necessary since there are two concurrent clients (see sucessful login attempts on the two previous LOG C lines within the same second).

Then the clients quit, and there's no mention of connections to postgres backends being closed, as expected again. They should be kept open until they reach server_lifetime or server_idle_timeout when idle.


InnoDB - How to get top locked tables and rows which are locked

Just search for 'lock'

mysql> pager grep -C 3 -i lock
PAGER set to 'grep -C 3 -i lock'
mysql> show engine innodb status\G
... results ...
1 row in set (0.00 sec)

mysql> \n
PAGER set to stdout
mysql>


Yes, it looks all right. (but only looks so)

Beware! SQL logic is a 3-valued logic, not the same as the "usual" math logic. The three values are TRUE, FALSE and UNKNOWN.

In your case, if the id or the values in the ( %s ) list contain NULL, the two versions are not equivalent. Example: 5 NOT IN (6, 7, NULL) and 5 IN (6, 7, NULL) will both evaluate to UNKNOWN so no row will be returned by the 1st version. While the 2nd version will return the rows that satisfy the other condition greatest() ....

If you have no Nulls, the two versions are equivalent.

You can find the truth tables in Wikipedia: Three-valued logic


What are the minimal MySQL grants for the debian-sys-maint user?

It depends on what you want to protect you MySQL server from.

To be able to automatically manage the MySQL db server, Debian has added the account debian-sys-maint@localhost.  That account is used to start, stop and do some other maintenance on the database without any user is needed to type in an administrator password.  That might look frighting at first, but actually are not, as long as you have the server protected from unauthorized use.

The debian-sys-maint@localhost user are only accessible from the local machine through localhost, so you can't connect to MySQL as that user unless you already are logged in on the server machine.
As the password for that MySQL account is only readable by the administrator account root, the attacker need to be root to be able to read and use it to access this Debian acccount.

$ ls -l /etc/mysql/debian.cnf
-rw------- 1 root root 666 sep 14  2010 /etc/mysql/debian.cnf

So, when the privileges for debian-sys-maint@localhost is of any real concern, you have a much worse problems with an unauthorized user with root access on your server. The access to the MySQL db is your least concern then.

Also notice that if an unauthorized user has access to the computer as root, there are nothing you can do to stop them from restarting MySQL with all authorization code in MySQL deactivated. With that, they have access to everything, like adding a new Administration account and then restart the MySQL server with authorization activated again. And you wouldn't noticed a thing, at first...

So no, this isn't as a large security problem as it might look at first sight.  So no, one doesn't need to change this from the access already given by Debian.


Steps after restoring to a different server

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time).

Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I  restore a database to a different server

You can happily use the SQL Server Index and Statistics Maintenance - from Ola Hallengren.

  Is there any other obvious step which we are missing - We don't have an in-house DBA as the work we do on the database front is not much.

I have a detailed post restore steps outlined in this answer. That will help you.
Even though it is for an upgrade, but the post restore steps are same.

  The only exception being the new VM has a lot more RAM

If the only exception is more RAM on the new server, then try enabling the Trace Flag T2335. Paul White has a detailed explanation of why that would happen with MORE Memory. I have shared, what Microsoft recommended in our case as well.


Since it's just a string, you might be able to accomplish a simple change/deletion of a node with the regex_replace function.

For example, this is how I recently deleted a certain JSON node in a table (all rows):

UPDATE my_db.my_table
SET my_column = (regexp_replace(my_column::text, ',"some_json_node":(.*),', ','))::json
WHERE NOT my_column IS NULL

Note, for all my JSON data, I keep a "version":"(n).(n)" (i.e. schema version) node in the object. That way I can update objects that comply with a specific version. Your requirements may not be that complex, but if they are, it certainly helps.


It's simple:

SELECT s.id
     , count(*) as COUNT
FROM B
GROUP BY s.id;


Database Blocking, kill the session which is responsible after 5 minutes

You can use the SQL script I wrote up below to build a dynamic SQL string that will kill all blocking sessions that have been running for at least 5 minutes.
You'll want to create a SQL Agent job (SQL Agent Job Docs) that executes this SQL script. (You can schedule the SQL Agent job to run as often as every second if you want, but the Agent Job GUI only allows you to select a minimum of 1 minute when scheduling, so if you need greater frequency you have to script the job out.)
-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = '';

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER
INNER JOIN CTE_BlockingSessions AS BS
    ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
    AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL


How can I design a database to track each individual's stat changes weekly, in a group?

You are thinking along the right track in wanting to store information about each person.  Since you want to record a history of their statistics, the best way to do that is to use another table which will keep one row for each time your record statistics with the date when they were recorded.  Now the question becomes how do you want to record each statistic?  You have two basic options.
Column Wise
If you have a fairly fixed set and want to make make it very simple, you can place each statistic by name as a column on Measurement along with the Date.  So for example, you'd have a column for Weight, Steps Walked, and so on.  The advantage of this approach is it is simple and clear, and you can use a data type specific to each type of statistic.  The disadvantage to this approach is that to add new kinds of statistics you have to add new columns, and you have to make all of the statistic types optional should you not record one or more of them for a given Measurement.

Row Wise
A second option would be to make the statistic more generalized by having a Statistic Type table.  There would be a row in Statistic Type for each kind of statistic you want to record.  Then, you would associate Measurement to the Statistic Type and record the Value.  The advantage of this approach is that you can easily add more statistics, and you only have to record just the values for the statistic types you measured.  The disadvantage is that this is more abstract and complex, and you have to use a generalized data type that can support all of the various units of measure, or you have to create a mutually exclusive set of columns each of a data type matching a statistic type.  If you go with the first approach, you can really turn it into a science project in order to support all the various data types and still ensure data integrity.

Other Points
The diagrams I show are created using Oracle's Data Modeler tool which is a free download and very powerful.  You can create DDL directly from it.  One thing you want to consider that I didn't discuss is that, if you use surrogate keys as I did here, you want to also define an alternate unique key for each table using more natural columns.  In the case of Person that could be Name.  This way, you won't accidentally add the same person twice if your list gets long.  A second thing is you have to think about if you have one value per statistic or more than one.  For example, Weight and Steps are good examples of a single statistic for the Measurement.  Today you record that I weight 200 pounds and walked 5000 steps. But for something like Bad Foods Eaten there could be more than one.  If that is the case you have what we call a repeating group.  To resolve this, you either have to create a fixed number of columns - one for each bad food - or create a new table that will be a child of the Measurement that will have one row for each bad food.
Examples
Here is an example of the way your data would look using the column approach (skipping the surrogate keys for simplicity of display):

And here is an example of the way your data would look using the row approach:

I hope this helps! A great book on data modeling like this is Steve Hoberman's Data Modeling Made Simple.


Data Deplucation and relationship integrity

There's no real hard rules when it comes to data cleanup/deduplication as it usually depends on these factors:

The quality of your dataset(s)

You don't mention quality being an issue so I won't say any more on this but usually matching data from different systems can be the biggest hurdle to tackle. e.g. David in one system might be Dave in another.

What the business needs

You already have a list of requirements and that's guided you so far. Now you've hit something that will impact the quality of the output. What would be beneficial is to sit down with the process and data owners and tell them the concerns you have with either method. It's not a solely DBA/Developer issue to solve and the other stakeholders can then be part of the solution. It help will avoid those "Why did you do that TheDemonLord! That's not what we wanted" accusations down the line when you thought you were doing the right thing but someone else disagrees!

You can then define the logic and rules around the dedupe effort together e.g. If a person has an account in with Brands X & Y then we will use the details from the most recent purchase etc...

How much effort can you devote to cleaning or transforming the data

Like any task there's a clear time/quality equation to deal with. You can easily get trapped in an ever growing list of manual fixes to have a perfect dedupe / extract process that captures all you need it to. This is another one to solve with the business. It's not your job to find every error and fix every issue. Define a process for highlighting and logging any accounts that might not fit your extract model. Then decide who fixes it, where and when.

As you first said, it is quite theoretical, and that's why the questions you uncover as you build it should be solved by everyone who is using the system.


Selecting data from multiple rows into a single row multiple columns

SELECT dm_ID, MAX(Amount1) Amount1, MAX(Amount2) Amount2,...
FROM (
SELECT dm_id,
CASE Value WHEN 1 THEN Value ELSE NULL END AS Amount1,
CASE Value WHEN 2 THEN Value ELSE NULL END AS Amount2,
....
FROM Table
) RearrangedData

Old school pivot. It will work, but you need to know the possible values of Amount column, to use in the CASE statement...  TSQL so should be supported by mySQL.


Mysql master-master permission replication issue

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA.  There is no reason to exclude this, as it is not replicated anyhow.  When replication filters are in place, you have to USE a database or your statements are not replicated.

For example...not replicated

Grant all privileges on *.* to foo@localhost identified by 'bar';

This is replicated...

USE anydb
Grant all privileges on *.* to foo@localhost identified by 'bar';

....but instead, just remove that replication filter.


Get all items from list table which don't have a successful log entry for 'sent' in log table

Query itself seems to be quite straightforward (from data sample provided I assume that a record with action:sent , failure_reason:NULL is the final state for a given list_id)  :

SELECT a.*
FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

However, if tables are huge , performance will suffer even if you have a covering index in log table which is probably not good - name "log" implies that you are mostly inserting data into it (and insert has to be as fast as possible), so each extra index defeats the main purpose of this table.
It looks to me though that date/datetime field is missed in the model; I guess you typically want failed tasks for a particular (presumably relatively small) date interval .


Create a monthly job to export from SQL Server 2014 to Excel

PowerShell is the way to go:

# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by     : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
                    # Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !

# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "servername\INSTANCE1"  #YourServer Name e.g. server1\instance1
$database = "your_db_name"    #your database name
$query = "select some columns from table inner join other table on table.id = othertable.id"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
# change here to the path that you want
$extractFile = @"
C:\SQLtoExcel.csv
"@

# Use windows authentication !!
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
# -NoTypeInformation will remove the first #TYPE System.Data.DataRow
$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

See also:

Multi-Tabbed Excel Through Powershell by Kevin Feasel


Datatype for phone number: VARCHAR, INT or BIGINT?

How would you handle a phone number with an extension, such as "+1-000-000-0000 ext 1234" ?

Note, the "+" indicates international dialing rules should be applied; so from North America, the system automatically knows "011" in front of international calls, etc.

Also, what about phone numbers such as "1-800-DBA-HELP"?

I would typically store phone numbers as text. Having said that, it really depends how critical your phone number column is. If you are running automated dialers from that column, then you'd really want to ensure that only numbers are included, and the data represents well-formed phone numbers.

You could have separate columns for extensions, and phone numbers that have text, such as the "1-800-DBA-HELP" example I provided.


IN is like writing = ANY, e.g.

regress=> SELECT 1 = ANY (ARRAY[1, 2, 3]);
 ?column?
----------
 t
(1 row)

A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense:

regress=> SELECT 1 = ALL (ARRAY[1, 2, 3]);
 ?column?
----------
 f
(1 row)

What you really want is to find all posts that have a corresponding post_categories entry for each of a listed set of category_id.

As is usual in relational databases, as soon as you clearly frame the question, the solution starts writing its self.

You could do a multiple join, like Daniel suggests, but that's a pain because it requires dynamic SQL. Or you could use relational set operations.

Instead, I'd possibly use PostgreSQL's array features. This would be easier if you'd provided sample data, but I think you want something like the following, which finds each category that matches the post then does a check to see if all the categories match (the HAVING clause):

SELECT
   posts.id
FROM posts
INNER JOIN post_categories ON posts.id = post_categories.post_id
WHERE post_categories.category_id IN (1,2,3)
GROUP BY posts.id
HAVING array_agg(post_categories.category_id) @> ARRAY[1,2,3]
LIMIT 10

Untested, since you didn't provide sample data in CREATE TABLE and INSERTs form, but that's the general idea. @> means "array-contains"; I suspect it'll be more efficient than using array_agg(category_id ORDER BY category_id) and an equality test against a sorted ARRAY literal, because you avoid the sort on aggregation.

You could leave out the WHERE clause and this would still work, but it might be quite slow, as it'd fail to filter out posts in which none of the categories matched before doing aggregation.

BTW, LIMIT without ORDER BY is usually a bug. The database can return any set of 10 matching results it feels like, in any order.


How to capture missing information in SQL Server tables

Retain the correct data types in each column.  Use what is referred to as a magic number to indicate "not applicable", or "unknown".

For instance, with an int column, you might use -2147483647 to indicate "not applicable", and -2147483646 to indicate "unknown", presuming those two values do not show up in the "normal" data.  For dates, use values like N'1900-01-01T00:00:00' and N'1900-01-01T00:00:01'.  Of course, you'll need to programmatically exclude these values when performing statistical analysis or reporting, since they would skew things like averages and sums.

An example table containing an int column using magic numbers:

CREATE TABLE dbo.Items
(
    SomeValue int NOT NULL
    , CONSTRAINT CK_SomeValue
        CHECK (
            SomeValue = -2147483647 /* Not Applicable */
            OR SomeValue = -2147483646 /* Unknown */
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

Note, the check constraint that "documents" the magic/canary values.  As Erik Darling details in a blog post, canary values remove the need for the column to allow NULLs, which in turn can alleviate some nasty surprises when using NOT IN (...) and JOIN ON ISNULL(...) = ISNULL(...), etc.

An example may be the best way to show the problems associated with using magic/canary values, and using NULL values instead.  We'll do this work in tempdb, and create two almost identical tables.  One uses magic/canary values, one uses NULL instead.

USE tempdb;

IF OBJECT_ID(N'dbo.ItemsNoNulls', N'U') IS NOT NULL
DROP TABLE dbo.ItemsNoNulls;
IF OBJECT_ID(N'dbo.ItemsNulls', N'U') IS NOT NULL
DROP TABLE dbo.ItemsNulls;

CREATE TABLE dbo.ItemsNoNulls
(
    SomeValue int NOT NULL
    , CONSTRAINT CK_ItemsNoNulls_SomeValue
        CHECK (
            SomeValue = -2147483647 /* Unknown */
            OR SomeValue = -2147483646 /* Not Applicable */
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

CREATE TABLE dbo.ItemsNulls
(
    SomeValue int NULL
    , CONSTRAINT CK_ItemsNulls_SomeValue
        CHECK (
            SomeValue IS NULL
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

Here, we'll insert a load of rows where 2% of the rows are "magic" values of either -2147483647 or -2147483646:

;WITH src AS (
    SELECT val = ABS(CONVERT(int, CRYPT_GEN_RANDOM(4)))
    FROM sys.columns c1
        CROSS JOIN sys.columns c2
)
INSERT INTO dbo.ItemsNoNulls(SomeValue)
SELECT CASE src.val % 50
    WHEN 1 THEN -2147483647
    WHEN 2 THEN -2147483646
    ELSE src.val
END
FROM src;

This will insert those previously generated rows into the table where NULL represents Unknown or Not Available:

INSERT INTO dbo.ItemsNulls (SomeValue)
SELECT CASE inn.SomeValue
        WHEN -2147483647 THEN NULL
        WHEN -2147483646 THEN NULL
        ELSE inn.SomeValue
    END
FROM dbo.ItemsNoNulls inn

Compare the output of these two simple queries:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
FROM dbo.ItemsNoNulls;

╔═══════════╦════════╗
║ Avg       ║ Count  ║
╠═══════════╬════════╣
║ 944303347 ║ 549081 ║
╚═══════════╩════════╝

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
    , [CountNull] = COUNT(COALESCE(SomeValue, 0))
FROM dbo.ItemsNulls

╔════════════╦════════╦═══════════╗
║ Avg        ║ Count  ║ CountNull ║
╠════════════╬════════╬═══════════╣
║ 1073162998 ║ 527112 ║ 549081    ║
╚════════════╩════════╩═══════════╝

The "average" in the first resultset is artificially low because of the presence of the canary values.  The "count" in the second query is lower than expected since aggregates eliminate NULL values, as shown by the warning:

  Warning: Null value is eliminated by an aggregate or other SET operation.

If we modify the first query to eliminate the canary values:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
FROM dbo.ItemsNoNulls
WHERE SomeValue <> -2147483647
    AND SomeValue <> -2147483646;

We get the correct average value, however the count no longer reflects the actual number of rows in the table:

╔════════════╦════════╗
║ Avg        ║ Count  ║
╠════════════╬════════╣
║ 1073162998 ║ 527112 ║
╚════════════╩════════╝

To get the accurate average, and an accurate count, we need to do something like:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = (SELECT COUNT(SomeValue) FROM dbo.ItemsNoNulls)
FROM dbo.ItemsNoNulls
WHERE SomeValue <> -2147483647
    AND SomeValue <> -2147483646;

╔════════════╦════════╗
║ Avg        ║ Count  ║
╠════════════╬════════╣
║ 1073162998 ║ 549081 ║
╚════════════╩════════╝

As you can see, both NULL columns and magic/canary values introduce some unexpected problems that can be bad for reporting.  Making sure you and other team members fully understand the implications is extremely important for accuracy.

One other method of recording "unknown" or "not applicable" values is to allow NULL values on those columns, combined with a separate column that indicates the status of the value.  For example:

USE tempdb;
IF OBJECT_ID(N'dbo.Items', N'U') IS NOT NULL
DROP TABLE dbo.Items;
IF OBJECT_ID(N'dbo.StatusValues', N'U') IS NOT NULL
DROP TABLE dbo.StatusValues;

CREATE TABLE dbo.StatusValues
(
    StatusValue int NOT NULL
        CONSTRAINT PK_StatusValues
        PRIMARY KEY CLUSTERED
    , StatusDescription varchar(30) NOT NULL
);

INSERT INTO dbo.StatusValues (StatusValue, StatusDescription)
VALUES (0, 'Normal')
    , (1, 'Unknown')
    , (2, 'Not Applicable');

CREATE TABLE dbo.Items
(
    SomeValue int NULL
    , SomeValueStatus int NOT NULL
        CONSTRAINT FK_StatusValue
        FOREIGN KEY REFERENCES dbo.StatusValues(StatusValue)
    , CONSTRAINT CK_ValueStatus
        CHECK ((SomeValue IS NULL AND (SomeValueStatus = 1 OR SomeValueStatus = 2))
            OR (SomeValue IS NOT NULL AND (SomeValueStatus = 0)))
);

This series of statements shows how the check constraint prevents invalid inserts:

--this fails, since 0 indicates a status of "normal"
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 0);

--this succeeds since status "1" is Unknown
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 1);

--this succeeds since status "2" is Not Applicable
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 2);

--this succeeds since the SomeValue column contains a non-null value
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 0);

--this fails since "Unknown" is incompatible with a non-null SomeValue
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 1);

--this fails since "Not Applicable" is incompatible with a non-null SomeValue
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 2);

The contents of the table:

SELECT i.SomeValue
    , sv.StatusDescription
FROM dbo.Items i
    INNER JOIN dbo.StatusValues sv ON i.SomeValueStatus = sv.StatusValue;

╔═══════════╦═══════════════════╗
║ SomeValue ║ StatusDescription ║
╠═══════════╬═══════════════════╣
║ NULL      ║ Unknown           ║
║ NULL      ║ Not Applicable    ║
║ 1         ║ Normal            ║
╚═══════════╩═══════════════════╝

The inserts above that are invalid will cause the following error to be returned to the client:

  Msg 547, Level 16, State 0, Line 30
  The INSERT statement conflicted with the CHECK constraint "CK_ValueStatus". The conflict occurred in database "tempdb", table "dbo.Items".


The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user.
So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc]
,dp.default_schema_name as [database_principal_default_schema_name]
FROM
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'


What permissions are required for complex cross-schema materialized view in Oracle?

The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation:

"To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege."

So either go with "ON DEMAND" or grant the additional privilege for each source table.


Performance considerations for Caching Aggregate counts

What performance considerations should tip the scales in favor of a
  particular strategy?

Essentially, as any form of cache, these strategies allow to increase performance of reads at the expense of writes and disk space.

So, you should consider the following :

The expected number of writes/updates vs. number of reads.
What is more important: fast writes or fast reads.
Whether extra disk space is available and how "cheap" it is.
Whether the summary has to be always up to date, or it can be delayed.

The first strategy is a baseline. You don't duplicate the data, writes are as fast as they can be.

The last strategy (indexed view) slows down writes immediately (as they happen), but the aggregated amounts are always up to date.

The second strategy with an explicit summary table gives you greater control over when to perform the aggregation. You can delay the aggregation and perform it when the server is under less load. If you accumulate a bunch of pending changes for calculating the summary and perform these calculations in bulk, it may be more efficient than updating the summary after every single change of the source data.

On the other hand, the engine that maintains the indexed view during updates should be smart enough to update the summary by applying only the changes to the summary without reading through the whole table each time. For example, if you update only, say, 2 rows in 10M table, then to calculate the new SUM(Quantity) the engine can subtract two old values of Quantity and add two new values. I don't have an authoritative source at hand that would confirm that the engine indeed works like this, but it should be fairly easy to test and verify by measuring the amount of reads and writes of a few test statements on a large table.

This leads to another thing to consider:

The type of aggregation that is done in the indexed view and whether the engine is smart enough to keep it up-to-date efficiently. For example, it is easy to keep SUM up-to-date efficiently and not so easy for MIN.
How big is the table that is being aggregated and what percentage of this table changes with each update.


Update unpivoted table with one to many relation

DECLARE @ExistentData
TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));

INSERT INTO @ExistentData([Row], [Col], Val)
VALUES
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')
select * from @ExistentData

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')
select * from @NewDataToUpdateExistent

With ROW_NUMBER you do the trick

DECLARE @A TABLE (ID INT, [Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @A (ID, [Row], [Col], Val, RowNumb)
SELECT A.ID, A.[Row], A.Col, A.Val,
ROW_NUMBER() OVER
    (PARTITION BY A.[Row], A.[Col] ORDER BY A.[Row], A.[Col] DESC) AS RowNumb
FROM @ExistentData A
;

DECLARE @B TABLE ([Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @B ([Row], [Col], Val, RowNumb)
SELECT B.[Row], B.Col, B.Val,
ROW_NUMBER() OVER
    (PARTITION BY B.[Row], B.[Col] ORDER BY B.[Row], B.[Col] DESC) AS RowNumb
FROM @NewDataToUpdateExistent B
;

Now just joining tables in the following way you get the expected result

--Delete
DELETE ed
FROM @A a
LEFT OUTER JOIN @B b
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID
WHERE b.[Row] IS NULL

--Update
UPDATE ed
SET ed.Val = B.Val
FROM @A a
INNER JOIN @B b
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID

--INSERT
INSERT INTO @ExistentData([Row], [Col], Val)
SELECT b.[Row], b.[Col], b.Val
FROM @B b
LEFT OUTER JOIN @A a
    ON a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
WHERE A.[Row] IS NULL

  SELECT * FROM @ExistentData
      ORDER BY id

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3


How to check with update trigger if only one column was updated postgresql

Instead of using the value "syncstatus_yes", you could use your Syncstatus column to store a binary code that represents the columns you are updating.... and then move the syncstatus as it is to the log. This way you will know exactly which columns you are syncronizing...

whether you use your app code or either the trigger OLD and NEW values, you can know exactly which columns are being updated. Then you can use those columns to build your Syncstatus column

For example: (suppose you have 6 columns + syncstatus column)

Syncstatus = 19 = 010011 = power(2,(6-2))+power(2,(6-5))+power(2,(6-6))

this means that columns 2, 5 and 6 are being updated.


MongoDB replicate data item across multiple shards

Create 'c' -database
Add  c -collection
Shard this collection with _id
Set this collection balacing:false
Pre-split this collection with _id values: 0,1,2,3,.. (or your real shard names). How many shards you have; one per shard
With moveChunk command, move one chunk to every shard, so that right _id is on right shard.

Now if you update this c.c's counter-key with $inc -operator (what is atomic operation) and without _id is set but multi:true, you can always "query" min value of that counter-key and answer tells what shard and what was value.

db.getSiblingDB('c').c.update({},{$inc:{"counter":1}},{multi:true})


Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

Community wiki answer

When I got back from lunch the backup completed successfully when maxtransfersize was not specified. After seeing it fail all morning, and now having one successful, uncompressed backup, it seems as if the failure is happening when it gets to the log file.

  The error you're hitting is a sanity check (it seems) for having consumed all of the vlf which is why it errors there. I cannot tell you why it is failing the check. – Sean Gallardy Jan 3 2017

Thanks for the hint. The number of VLFs was the root of my problem. This particular database had CDC issues which meant the transaction log wasn't being cleared out on backups. It had grown to over 300 GB. Once I disabled CDC, I was able to flush the log and give it a better growth plan. The compressed backup works again.


Is it a bad practice to always create a transaction?

Is a bad practice to create a transaction always?

It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly).

But wait there is more to understand first :
Everything in sql server is contained in a transaction.

When the session option IMPLICIT_TRANSACTIONS is OFF and you explicitly specify begin tran and commit/rollback then this is commonly known as an Explicit Transaction. Otherwise you get an autocommit transaction.

When  IMPLICIT_TRANSACTIONS is ON an Implicit transaction is automatically started when executing one of the statement types documented in the books online article (e.g. SELECT / UPDATE / CREATE) and it must be committed or rolled back explicitly. Executing a BEGIN TRAN in this mode would increment @@TRANCOUNT and start another "nested" transaction)

To switch which mode you're in, you'd use

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

select @@OPTIONS & 2

if above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

how much is the cost of creating a transaction when is not really necessary?

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.
Refer: Using Row Versioning-based Isolation Levels

Even if you are using an isolation level read_uncomitted. Is a bad practice? because it shouldn't have problems with locking.

READ_UNCOMMITED isolation level will allow dirty reads by definition i.e. One transaction will be able to see uncommitted changes made by other transaction. What this isolation level does is, it relaxes the over head of locking - method of acquiring locks to protect Database concurrency.

You can use this on a connection/query level, so that it does not affect other queries.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Found an interesting article by Jeff Atwood describing Deadlocks due to Dining Philosophers Puzzle and describing  read committed snapshot isolation level.

EDIT:

Out of curiosity, I did some test measuring the impact on T-log with Perfmon counters like Log Bytes Flushed/Sec, Log Flush Waits/Sec (No. of commits per sec that are waiting on LOG flush to occur) as below graph :

sample code :

create table testTran (id int, Name varchar(8))
go

-- 19 sec
-- Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
---------------------------------------------------
-- 2 sec
-- Implicit transaction
SET IMPLICIT_TRANSACTIONS ON
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF

----------------------------------------------------
-- 2 sec
-- Explicit transaction
declare @i int
set @i = 0
BEGIN TRAN
WHILE @i < 100000
Begin
INSERT INTO testTran values (1,'Kin Shah')
set @i = @i+1
End
COMMIT TRAN

Autocommit Transactions: (Edited as highlighted by @TravisGan)

Insert took 19 secs.
Every Autocommit will Flush the T-log buffer to the disk due to autocomit (after @TravisGan highlighted, and I missed that to mention) .
The CHECKPOINT process will be completing fast as the amount of dirty log buffer required to be flushed will be less as it runs quiet often.

IMPLICIT & Explicit Transaction:

Insert took 2 secs.
For EXPLICIT transaction, the log buffers will be flushed only when they are full.
Contrary to Autocommit transaction, in EXPLICIT transaction, the CHECKPOINT process will take longer duration as it will have more log buffers to flush (remember that log buffers are flushed only when they are full).

There is a DMV sys.dm_tran_database_transactions that will return information about Transactions at database level.

Obviously, this is more sort of a simplistic test to show the impact. Other factors like disk subsystem, database auto growth settings, initial size of the database, other processes running on the same server\database, etc will have influence as well.

From the above tests, there is near to no difference between Implicit & Explicit transactions.

Thanks to @TravisGan for helping to add more to the answer.


Setting up AlwaysONAG in SQL server 2016 on windows 2016

This is generally a consulting engagement since there are lots of minutiae. But to answer your techincal questions:

The AG handles the data synchronization process. There is no storage-based replication needed or involved with AGs whether it is local or multi-site.
You can use the NJ site as a FSW, but arguably the best option for multi-subnet in WS2016+ tends to be cloud witness.Figuring out the right witness for quorum needs a bit more informaiton about your infrastructure, which is again, generally a much deeper consulting engagement.

Also, it's not AOAG. Just AG. Always On (which has a space) is just a marketing umbrella term which covers both AGs and FCIs. The ON is not captialized.


The docs for MongoDB discuss the behavior of copyDatabase in detail:
  http://docs.mongodb.org/manual/reference/method/db.copyDatabase/#behavior

Specifically, copyDatabase reads the data from the source DB the same way something like a backup or full collection query would read it - that means that it may impact the performance of that DB (for example, by bringing more data into RAM which may displace some other data) but the impact would be about the same as running mongodump on that DB.

The docs do explicitly mention that the copied database does not get locked.

If you are running a replica set, you can copyDatabase from a secondary node


Show Primary and Secondary Accounts in a table

If you use sql server 2017 you could use the STRING_AGG function
and your query would look like

SELECT testRelate.AccountRef AS ACref,STRING_AGG(testRelate_2.AccountRef, ', ')
FROM testRelate AS testRelate_2
INNER JOIN  testRelate AS testRelate_1 ON testRelate_2.[Relationship Reference] = testRelate_1.[Relationship Reference]
RIGHT OUTER JOIN testRelate ON testRelate_1.[Primary Relationship Reference] = testRelate.[Primary Relationship Reference]
GROUP BY testRelate.AccountRef

but if not, it would have to be with FOR XML PATH Y STUFF


MySql trigger having null variables during bulk inserts

Looks like i needed to take care of null,

Here is what i did, though the columns might have changed

CREATE TRIGGER calculate_balances AFTER INSERT ON transactions FOR EACH ROW
             BEGIN

                SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.credit);
                SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.credit);

                IF @debit IS NULL
                THEN
                    SET @debit = 0;
                END IF;

                IF @credit IS NULL
                THEN
                    SET @credit = 0;
                END IF;

                SET @credit_balance = @credit - @debit;

                SET @credit = (SELECT SUM(amount) FROM transactions WHERE  transactions.credit = New.debit);
                SET @debit  = (SELECT SUM(amount) FROM transactions WHERE  transactions.debit = New.debit);

                IF @credit IS NULL
                THEN
                    SET @credit = 0;
                END IF;

                IF @debit IS NULL
                THEN
                    SET @debit = 0;
                END IF;

                SET @debit_balance = @credit - @debit;

                UPDATE accounts SET account_balance = @debit_balance WHERE account_no =  New.debit;
                UPDATE accounts SET account_balance = @credit_balance WHERE account_no =  New.credit;

             END


decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

If all your data is InnoDB, I have a surprise for you.

It is a little-know fact, but there are some occasions when an older version of MySQL/InnoDB can outrun/outgun a newer of MySQL/InnoDB. How can that be possible?

I wrote about this in DBA StackExchange before

Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) (Nov 24, 2011)
Query runs a long time in some newer MySQL versions (Oct 05, 2011)
Multi cores and MySQL Performance (Sep 20, 2011)
How do I properly perform a MySQL bake-off? (Jun 19, 2011)

You will have to perform some due diligence in configuring MySQL to harness more threading and more CPUs.


CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)

This appears to be a straightforward connect by where you're getting the next person based on the prior manager. The only difference being you want to get the top-level (root) person listed for each row it applies to.

This can easily be found using the connect_by_root function (docs), which will return you the value of the column listed at the root node in each hierarchy.

This gives you a query like:

SELECT connect_by_root p.pers_id per, m.manager_id, level
FROM   fact_tab f
join   dim_per p
on     f.person_ky = p.person_ky
join   dim_mgr m
on     f.manager_ky = m.manager_ky
connect by  p.pers_id = prior m.manager_id

For a full example, see this fiddle.


Why is MongoDB oplog size different between servers

If you haven't defined in config (file or parameter; replication.oplogSizeMB) what size of opLog you wanted when node was initialized, it is automatically set base on free space on data disk. In wireTiger it's 5% of free disk space (990MB-50GB).

You can change that oplog size if you want, but after that you need to wait until it's full.


How to clear temp database content in SQL Server?

If you truly feel you need to clear it, then yes, restarting SQL Server will clear it.  However, that will likely be a temporary solution and you'll be back in the same condition after a few days.  Restarting SQL Server is the only way to clear the tempdb content from a SQL Server perspective.  Another possible alternative would be to restart the application that is creating the objects in tempdb.

It's impossible for us, given the bit of information that you provide, to know what the best course of action is.  Some things to consider:

Just because SolarWinds says something is a problem doesn't necessarily mean it's a problem.  Many monitoring tools are set with defaults that will send "critical" alerts for things that are 100% expected on a given server.  For example, I would expect a Dynamics AX SQL Server to trigger many of the default tempdb thresholds of most monitoring tools.  If there is no significant tempdb contention and it's not running out of space, then there's probably nothing critical.   Is the application that is using the SQL Server having performance issues?  Is anyone complaining about performance?  If not, then the SolarWinds alerts aren't reflecting reality and thresholds maybe need to be adjusted.  One thing to keep in mind is that many of these applications were initially coded and tested when storage was much slower.  If you have tempdb on SSDs, they can take much more beating than a couple of mirrored local SCSI disks that were probably used when SolarWinds decided what thresholds to use.
Do you have tempdb sized appropriately?  Some application make heavy use of tempdb and will need far more than other applications typically use.  If tempdb is just running out of space, you may just need larger files for tempdb
Do you have one tempdb database file for each processor, with a maximum of 8?
Do you have trace flags 1117 and 1118 enabled if you are using SQL Server 2014 or earlier?  If not, research them--if you have an application that has significant tempdb usage, you'll probably want to enable these (I see you have used the sql-server-2016 tag, so this is for the benefit of other readers).
Is there a defect in an application that is causing it to "leak" tempdb objects?  If the number of tempdb tables and usage is constantly climbing and never stabilizes, then it would be advisable to investigate this.

If you can't resist the urge to shrink it, shrink it with TRUNCATEONLY.  TRUNCATEONLY may shrink it a lot, or none--it just depends on where the last used extent is.  If you shrink it to a specified size, it will start reorganizing the data (i.e., fragmenting it) in order to move data from the end of the file to any free space that exists in the middle. In order to do that it will require locks and your users/application my have significant performance impact.  And you never know how long it's going to take to reorganize that data.

If you have more info to add to the question about the exact metric(s) SolarWinds is alerting on that would be helpful.


Writing functions in Postgres that returns strings with dots in reverse like java namespaces

You can do this with a combination of array functions:

select string_agg(x, '.' order by idx desc)
from unnest(string_to_array('foo.bar.baz.example.com','.')) with ordinality as t(x, idx);

you can put this into a function if you want:

create function reverse_elements(p_words text)
  returns text
as
$$
  select string_agg(x, '.' order by idx desc)
  from unnest(string_to_array(p_words,'.')) with ordinality as t(x, idx);
$$
language sql;


Mongo Super User won't connect to another database other than admin

Although the admin user you've created has global permissions through *AnyDatabase roles, the user credentials only exist in the admin database. As per your example, you will fail to authenticate if matching user credentials don't exist in the database you are trying to access.

To authenticate with the mongo shell using credentials stored in a different database from the one which you are trying to access (admin vs iot) you can either:

1) Specify the database with user credentials using --authenticationDatabase:

 mongo iot --authenticationDatabase admin -u admin -p password --host 192.168.99.100

2) Specify the default database as /admin and then change to the desired target database:

 $ mongo -u admin -p password 192.168.99.100/admin

 MongoDB shell version: 3.2.9
 connecting to: 192.168.99.100/admin

 > use iot
 switched to db iot

The first example is the most typical approach.

NOTE: If you want to specify host and database as per the second example, you have to provide the database connection string without using the --host parameter.


In simple terms...

A telephone directory: the data is the index/the index is the data. To look you up, I'd start with Rezaei, Amir for example. No external lookup is needed.

In database terms:

The table data and clustered index are one and the same (in SQL Server, also InnoDB, Oracle IOT)

Best practice is narrow, numeric, strictly increasing (think IDENTITY or Autonumber column). Data types like GUIDs are not a good idea

Links to read (to avoid bloating this answer)

"Effective Clustered Indexes" (Simple Talk)
Wikipedia
MSDN (SQL Server 2000)
MSDN (SQL Server 2005+)

Edit, to fully explain my best practice suggestions, follow the link in  Eric Humphrey's comment: Queen of Indexing: Kimberly Tripp


How do I use a stored procedure in SQL Server 2014 to call a external XML source?

EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

sometimes doesn't return the output parameter.  Particularly if the response XML is longer than your @response variable.

Try:

declare  @result table(data text)

INSERT into @result (Data) exec sp_OAGetProperty @obj, 'responseText'

select * from @result


Using an older MySQL binlog filename (with position zero)? Risks? Downsides?

I have a surprise for you

Every position within a binary log is based on the filesize of the binary log as of the moment an SQL statement was written.

What is even more interesting about this concept is the fact that header for binary logs differ in start position (There is no such thing as a literal position 0)

These are the starting points for replication from a Master's empty binary log

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

I first wrote about this back on Feb 4, 2011

Here is a suggested course of action

If the relay logs still exists on the Slave

find the last relay log
run mysqlbinlog against that relay log
locate the timestamp of the moment you performed the mysqldump
Take that timestamp and locate the same timestamp in the master's binary logs\
Near the master's copy of the timestamp in the appropriate  should be the position
Whatever master binary log you selected, use that binary log in the CHANGE MASTER TO command on the Slave
Whatever position you found in the master binary log you selected, use that position in the CHANGE MASTER TO command on the Slave

That's the best way to minimize any data collision due to duplcaiet keys

Just to be sure, on the Slave add this to my.cnf

[mysqld]
slave-skip-errors=1062

Then restart mysql. This will bypass all duplicate key collisions after running START SLAVE;. When Seconds_Behind_Master reaches 0, remove that line and restart mysql on the Slave. Everything in terms of MySQL Replication should be fine from there.


How to Check and Report time taking queries

To capture the slow running queries in MySQL, please enable slow query log. Refer the below URL:

http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

To optimize the queries, you need to get the explain plan of the queries using EXPLAIN command:

EXPLAIN SELECT * FROM categories

Detailed documentation is available in the below URL:

https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/


Mysql 8: count(*) on INT primary is 13500 times slower than supposed to be. any innodb dev here?

Add INDEX(last_error_code), then do SELECT COUNT(*) FROM logos, it will run a lot faster because it will use the much smaller BTree for that secondary index.

SHOW TABLE STATUS LIKE 'logos' will give you an approximate count.

Why is the PK bloated?  InnoDB orders the data by the PRIMARY KEY; hence the PK, as an INDEX is essentially included in the data.  So to fetch a PK implies fetching the entire row.  (Caveat:  Bulky columns such as your texts and blobs are stored in a separate place, so they are not fetched when doing the COUNT(*).)

The Data+PK is in one B+Tree; each secondary index is in its own B+Tree.

Any "size" math (such as INT takes 4 bytes) needs to be multiplied by about 2 or 3 due to various overheads -- per column, per row, per block, etc.  And the block nature has its own overhead due to block splits.

If the entire table happens to be cached in RAM (that is, in the innodb_buffer_pool), then there won't be any I/O.  Still there is a non-trivial amount of CPU work to scan through your millions of rows.

I would guess that your table is slightly bigger than will fit in the buffer_pool.  So, every time you do that COUNT(*) it is competing with all other queries for room in the cache.  Not cool.

Note that the index I propose will take about 3GB, so it has a chance of being cached and perhaps even staying cached.  The first time (after a restart) you do the COUNT(*), it will be slow because of lots of I/O.  The second time (unless blocks have been bumped out of cache), it will be faster; perhaps ten times as fast.  That will probably be less than an hour, but more than a few seconds.

What is the value of innodb_buffer_pool_size?

How do others solve the sluggishness of COUNT(*)?  Have you noticed that search engines, if they say anything about 'how many', give you round numbers like "About 14,000,000 hits"?  That's one approach -- periodically get the exact number and round it off until you get around to checking again.

Or they have, say, daily subtotals.  Then the query SUMs up the daily counts.  This is much faster.  (But it only works for stuff that is continually coming in and not changing.)

Or ... (There are other techniques.)  What is your use case?

The idea of "random areas, plus extrapolation" probably has a lot of hassles; I would not embark on such.  Anyway, that is essentially what SHOW TABLE STATUS does.

MySQL 8.0.14 has Parallel scanning of by PRIMARY KEY (cf innodb_parallel_read_threads) for COUNT(*) w/o WHERE.  That will provide a little speedup for your original query.


Error SQLCODE=-443, SQLSTATE=38553 after applying fix pack 8 to DB2 v10.5

What could we forget to do?

As documented in the DB2 fixpack readme, after you install on the server, db2schema.bnd needs to be bound to existing databases.

  How could we check that bind command executed properly?

If you installed the fix pack on DB2 database products that have existing databases, perform the following commands once for each database:

db2 terminate
db2 CONNECT TO dbname user USERID using PASSWORD
db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
db2 terminate

where dbname represents the name of a database to which the files should be bound, and where path is the full path name of the directory where the bind files are located, such as INSTHOME\sqllib\bnd where INSTHOME represents the home directory of the DB2 instance. db2ubind.lst and db2cli.lst contain lists of required bind files used by DB2 database products.

  How could we try to solve the issue or reveal its root?

may be the installation of the DB2 fixpack was incomplete.

For further your ref:- DB2 SQL Error occurs after DB2 patch install


Why the COPY 2 is missing

As said in the doc about \copy:

  Unlike most other meta-commands, the entire remainder of the line is
  always taken to be the arguments of \copy

Clearly your intention is that COPY and END commands are sent consecutively, but in fact the SQL command that is sent to the server and successfully executed is the compound statement:

COPY a FROM STDIN CSV HEADER; end;

That does work, but when dealing with the results of compound statements, psql only handles the last one, as mentioned in Backslash-semicolon:

  psql prints only the last query result it receives for each request

The expected COPY 2 command tag is not displayed, because it's the first of two results of a compound statement: COPY and COMMIT.

Workarounds

The simplest one is probably to use -1 or --single-transaction as an argument to psql to make it enclose the sequence of commands inside a transaction:

echo "delete from a; \copy a from a.csv CSV HEADER" | psql -1 [other options]

With this form, the end of the line is clear so \copy can consume it. Plus that's why -1 is made for.

Another option if you had to put other commands after the \copy anyway: use the heredoc syntax:

$ psql [options] << ENDOFCOMMANDS
begin;
delete from a;
\copy a from a.csv CSV HEADER
-- other commands
end;
ENDOFCOMMANDS


Plan cache memory: parameterized SQL vs stored procedures

No, there really isn't, for the same query taking the same parameters (though this hasn't always been the case - SQL Server wasn't always able to optimize at the statement level).

Now, if the ad hoc SQL you're sending to SQL Server varies by quite a bit (say a lot of similar but not identical queries), then you could have a lot of plan cache bloat due to single-use plans. You can reduce this somewhat by using the optimize for ad hoc workloads server setting. And depending on the parameterization techniques you're using, you may want to play with dynamic SQL vs. "kitchen sink" style queries (WHERE (c1 = @p1 OR @p1 IS NULL) AND (c2 = @p2 OR @p2 IS NULL) ... etc ...), and also parameterization forced vs. simple.

If you're looking for opinions with justification, I prefer stored procedures for a variety of other reasons - including the fact that you can drastically reduce the number of calls (and the amount of data) you send over the wire, you can also simplify the hardest parts of transaction management and error handling in a single layer, and the database people can optimize the SQL without having source code access and without having to re-compile and re-deploy the application code (be it the front end or middle tier - trust me, we're not good at that, just like developers are stereotypically not very good at writing good SQL queries :-)).