Why can't RDBM's cluster the way NoSQL does?

Distributed Database Systems 101

Or, Distributed Databases - what the FK does 'web scale' actually mean?

Distributed database systems are complex critters and come in a number of different flavours.  If I dig in to the depths of my dimly remembered studies on this at university I'll try to explain some of the key engineering problems to building a distributed database system.

First, some terminology

ACID (Atomicity, Consistency, Isolation and Durability) properties: These are the key invariants that have to be enforced for a transaction to be reliably implemented without causing undesirable side effects.

Atomicity requires that the transaction complete or rollback completely.  Partially finished transactions should never be visible, and the system has to be built in a way that prevents this from happening.

Consistency requires that a transaction should never violate any invariants (such as declarative referential integrity) that are guaranteed by the database schema.  For example, if a foreign key exists it should be impossible to insert a child record with a reverence to a non-existent parent.

Isolation requires that transactions should not interfere with each other.  The system should guarantee the same results if the transactions are executed in parallel or sequentially.  In practice most RDBMS products allow modes that trade off isolation against performance.

Durability requires that once committed, the transaction remains in persistent storage in a way that is robust to hardware or software failure.

I'll explain some of the technical hurdles these requirements present on distributed systems below.

Shared Disk Architecture: An architecture in which all processing nodes in a cluster have access to all of the storage.  This can present a central bottleneck for data access.  An example of a shared-disk system is Oracle RAC or Exadata.

Shared Nothing Architecture: An architecture in which processing nodes in a cluster have local storage that is not visible to other cluster nodes.  Examples of shared-nothing systems are Teradata and Netezza.

Shared Memory Architecture: An architecture in which multiple CPUs (or nodes) can access a shared pool of memory.  Most modern servers are of a shared memory type.  Shared memory facilitates certain operations such as caches or atomic synchronisation primitives that are much harder to do on distributed systems.

Synchronisation: A generic term describing various methods for ensuring consistent access to a shared resource by multiple processes or threads.  This is much harder to do on distributed systems than on shared memory systems, although some network architectures (e.g. Teradata's BYNET) had synchronisation primitives in the network protocol.  Synchronisation can also come with a significant amount of overhead.

Semi-Join: A primitive used in joining data held in two different nodes of a distributed system.  Essentially it consists of enough information about the rows to join being bundled up and passed by one node to the other in order to resolve the join.  On a large query this could involve significant network traffic.

Eventual Consistency: A term used to describe transaction semantics that trade off immediate update (consistency on reads) on all nodes of a distributed system for performance (and therefore higher transaction throughput) on writes.  Eventual consistency is a side effect of using Quorum Replication as a performance optimisation to speed up transaction commits in distributed databases where multiple copies of data are held on separate nodes.

Lamport's Algorithm: An algorithm for implementing mutual exclusion (synchronisation) across systems with no shared memory.  Normally mutual exclusion within a system requires an atomic read-compare-write or similar instruction of a type normally only practical on a shared memory system.  Other distributed synchronisation algorithms exist, but Lamport's was one of the first and is the best known.  Like most distributed synchronisation mechanisms, Lamport's algorithm is heavily dependent on accurate timing and clock synchronisation beteen cluster nodes.

Two Phase Commit (2PC): A family of protocols that ensure that database updates involving multiple physical systems commit or roll back consistently.  Whether 2PC is used within a system or across multiple systems via a transaction manager it carries a significant overhead.

In a two-phase commit protocol the transaction manager asks the participating nodes to persist the transaction in such a way that they can guarantee that it will commit, then signal this status.  When all nodes have returned a 'happy' status it then signals the nodes to commit.  The transaction is still regarded as open until all of the nodes send a reply indicating the commit is complete.  If a node goes down before signalling the commit is complete the transaction manager will re-query the node when it comes back up until it gets a positive reply indicating the transaction has committed.

Multi-Version Concurrency Control (MVCC): Managing contention by writing new versions of the data to a different location and allowing other transactions to see the old version of the data until the new version is committed.  This reduces database contention at the expense of some additional write traffic to write the new version and then mark the old version as obsolete.

Election Algorithm: Distributed systems involving multiple nodes are inherently less reliable than a single system as there are more failure modes.  In many cases some mechanism is needed for clustered systems to deal with failure of a node.  Election algorithms are a class of algorithms used to select a leader to coordinate a distributed computation in situations where the 'leader' node is not 100% determined or reliable.

Horizontal Partitioning: A table may be split across multiple nodes or storage volumes by its key.  This allows a large data volume to be split into smaller chunks and distributed across storage nodes.

Sharding: A data set may be horizontally partitioned across multiple physical nodes in a shared-nothing architecture.  Where this partitioning is not transparent (i.e. the client must be aware of the partition scheme and work out which node to query explicitly) this is known as sharding.  Some systems (e.g. Teradata) do split data across nodes but the location is transparent to the client; the term is not normally used in conjunction with this type of system.

Consistent Hashing: An algorithm used to allocate data to partitions based on the key.  It is characterised by even distribution of the hash keys and the ability to elastically expand or reduce the number of buckets efficiently.  These attributes make it useful for partitioning data or load across a cluster of nodes where the size can change dynamically with nodes being added or dropping off the cluster (perhaps due to failure).

Multi-Master Replication: A technique that allows writes across multiple nodes in a cluster to be replicated to the other nodes.  This technique facilitates scaling by allowing some tables to be partitioned or sharded across servers and others to be synchronised across the cluster.  Writes must be replicated to all nodes as opposed to a quorum, so transaction commits are more expensive on a multi-master replicated architecture than on a quorum replicated system.

Non-Blocking Switch: A network switch that uses internal hardware parallelism to achieve throughput that is proportional to the number of ports with no internal bottlenecks.  A naive implementation can use a crossbar mechanism, but this has O(N^2) complexity for N ports, limiting it to smaller switches.  Larger switches can use more a complex internal topology called a non-blocking minimal spanning switch to achieve linear throughput scaling without needing O(N^2) hardware.

Making a distributed DBMS - how hard can it be?

Several technical challenges make this quite difficult to do in practice.  Apart from the added complexity of building a distributed system the architect of a distributed DBMS has to overcome some tricky engineering problems.

Atomicity on distributed systems: If the data updated by a transaction is spread across multiple nodes the commit/rollback of the nodes must be coordinated.  This adds a significant overhead on shared-nothing systems.  On shared-disk systems this is less of an issue as all of the storage can be seen by all of the nodes so a single node can coordinate the commit.

Consistency on distributed systems: To take the foreign key example cited above the system must be able to evaluate a consistent state.  For example, if the parent and child of a foreign key relationship could reside on different nodes some sort of distributed locking mechanism is needed to ensure that outdated information is not used to validate the transaction.  If this is not enforced you could have (for example) a race condition where the parent is deleted after the its presence is verified before allowing the insert of the child.

Delayed enforcement of constraints (i.e. waiting until commit to validate DRI) requires the lock to be held for the duration of the transaction.  This sort of distributed locking comes with a significant overhead.

If multiple copies of data are held (this may be necessary on shared-nothing systems to avoid unnecessary network traffic from semi-joins) then all copies of the data must be updated.

Isolation on distributed systems: Where data affected on a transaction resides on multiple system nodes the locks and version (if MVCC is in use) must be synchronised across the nodes.  Guaranteeing serialisability of operations, particularly on shared-nothing architectures where redundant copies of data may be stored requires a distributed synchronisation mechanism such as Lamport's Algorithm, which also comes with a significant overhead in network traffic.

Durability on distributed systems: On a shared disk system the durability issue is essentially the same as a shared-memory system, with the exception that distributed synchronisation protocols are still required across nodes.  The DBMS must journal writes to the log and write the data out consistently.  On a shared-nothing system there may be multiple copies of the data or parts of the data stored on different nodes.  A two-phase commit protocol is needed to ensure that the commit happens correctly across the nodes.  This also incurs significant overhead.

On a shared-nothing system the loss of a node can mean data is not available to the system.  To mitigate this data may be replicated across more than one node.  Consistency in this situation means that the data must be replicated to all nodes where it normally resides.  This can incur substantial overhead on writes.

One common optimisation made in NoSQL systems is the use of quorum replication and eventual consistency to allow the data to be replicated lazily while guaranteeing a certain level of resiliency of the data by writing to a quorum before reporting the transaction as committed.  The data is then replicated lazily to the other nodes where copies of the data reside.

Note that 'eventual consistency' is a major trade-off on consistency that may not be acceptable if the data must be viewed consistently as soon as the transaction is committed.  For example, on a financial application an updated balance should be available immediately.

Shared-Disk systems

A shared-disk system is one where all of the nodes have access to all of the storage.  Thus, computation is independent of location.  Many DBMS platforms can also work in this mode - Oracle RAC is an example of such an architecture.

Shared disk systems can scale substantially as they can support a M:M relationship between storage nodes and processing nodes.  A SAN can have multiple controllers and multiple servers can run the database.  These architectures have a switch as a central bottleneck but crossbar switches allow this switch to have a lot of bandwidth.  Some processing can be offloaded onto the storage nodes (as in the case of Oracle's Exadata) which can reduce the traffic on the storage bandwidth.

Although the switch is theoretically a bottleneck the bandwidth available means that shared-disk architectures will scale quite effectively to large transaction volumes.  Most mainstream DBMS architectures take this approach because it affords 'good enough' scalability and high reliability.  With a redundant storage architecture such as fibre channel there is no single point of failure as there are at least two paths between any processing node and any storage node.

Shared-Nothing systems

Shared-nothing systems are systems where at least some of the data is held locally to a node and is not directly visible to other nodes.  This removes the bottleneck of a central switch, allowing the database to scale (at least in theory) with the number of nodes.  Horizontal partitioning allows the data to be split across nodes; this may be transparent to the client or not (see Sharding above).

Because the data is inherently distributed a query may require data from more than one node.  If a join needs data from different nodes a semi-join operation is used to transfer enough data to support the join from one node to another.  This can result in a large amount of network traffic, so optimising the distribution of the data can make a big difference to query performance.

Often, data is replicated across nodes of a shared-nothing system to reduce the necessity for semi-joins.  This works quite well on data warehouse appliances as the dimensions are typically many orders of magnitude smaller than the fact tables and can be easily replicated across nodes.  They are also typically loaded in batches so the replication overhead is less of an issue than it would be on a transactional application.

The inherent parallelism of a shared-nothing architecture makes them well suited to the sort of table-scan/aggregate queries characteristic of a data warehouse.  This sort of operation can scale almost linearly with the number of processing nodes.  Large joins across nodes tend to incur more overhead as the semi-join operations can generate lots of network traffic.

Moving large data volumes is less useful for transaction processing applications, where the overhead of multiple updates makes this type of architecture less attractive than a shared disk.  Thus, this type of architecture tends not to be used widely out of data warehouse applications.

Sharding, Quorum Replication and Eventual Consistency

Quorum Replication is a facility where a DBMS replicates data for high availability.  This is useful for systems intended to work on cheaper commodity hardware that has no built-in high-availability features like a SAN.  In this type of system the data is replicated across multiple storage nodes for read performance and redundant storage to make the system resilient to hardware failure of a node.

However, replication of writes to all nodes is O(M x N) for M nodes and N writes.  This makes writes expensive if the write must be replicated to all nodes before a transaction is allowed to commit.  Quorum replication is a compromise that allows writes to be replicated to a subset of the nodes immediately and then lazily written out to the other nodes by a background task.  Writes can be committed more quickly, while providing a certain degree of redundancy by ensuring that they are replicated to a minimal subset (quorum) of nodes before the transaction is reported as committed to the client.

This means that reads off nodes outside the quorum can see obsolete versions of the data until the background process has finished writing data to the rest of the nodes.  The semantics are known as 'Eventual Consistency' and may or may not be acceptable depending on the requirements of your application but mean that transaction commits are closer to O(1) than O(n) in resource usage.

Sharding requires the client to be aware of the partitioning of data within the databases, often using a type of algorithm known as 'consistent hashing'.  In a sharded database the client hashes the key to determine which server in the cluster to issue the query to.  As the requests are distributed across nodes in the cluster there is no bottleneck with a single query coordinator node.

These techniques allow a database to scale at a near-linear rate by adding nodes to the cluster.  Theoretically, quorum replication is only necessary if the underlying storage medium is to be considered unreliable.  This is useful if commodity servers are to be used but is of less value if the underlying storage mechanism has its own high availability scheme (for example a SAN with mirrored controllers and multi-path connectivity to the hosts).

For example, Google's BigTable does not implement Quorum Replication by itself, although it does sit on GFS, a clustered file system that does use quorum replication.  BigTable (or any shared-nothing system) could use a reliable storage system with multiple controllers and partition the data among the controllers.  Parallel access would then be achieved through partitioning of the data.

Back to RDBMS platforms

There is no inherent reason that these techniques could not be used with a RDBMS.  However lock and version management would be quite complex on such a system and any market for such a system is likely to be quite specialised.  None of the mainstream RDBMS platforms use quorum replication and I'm not specifically aware of any RDBMS product (at least not one with any significant uptake) that does.

Shared-disk and shared-nothing systems can scale up to very large workloads.  For instance, Oracle RAC can support 63 processing nodes (which could be large SMP machines in their own right) and an arbitrary number of storage controllers on the SAN.  An IBM Sysplex (a cluster of zSeries mainframes) can support multiple mainframes (each with substantial processing power and I/O bandwidth of their own) and multiple SAN controllers.  These architectures can support very large transaction volumes with ACID semantics, although they do assume reliable storage.  Teradata, Netezza and other vendors make high-performance analytic platforms based on shared-nothing designs that scale to extremely large data volumes.

So far, the market for cheap but ultra-high volume fully ACID RDBMS platforms is dominated by MySQL, which supports sharding and multi-master replication.  MySQL does not use quorum replication to optimise write throughput, so transaction commits are more expensive than on a NoSQL system.  Sharding allows very high read throughputs (for example Facebook uses MySQL extensively), so this type of architecture scales well on read-heavy workloads.

An interesting debate

BigTable is a shared-nothing architecture (essentially a distributed key-value pair) as pointed out by Michael Hausenblas below.  My original evaluation of it included the MapReduce engine, which is not a part of BigTable but would normally be used in conjunction with it in its most common implementations (e.g. Hadoop/HBase and Google's MapReduce framework).

Comparing this architecture with Teradata, which has physical affinity between storage and processing (i.e. the nodes have local storage rather than a shared SAN) you could argue that BigTable/MapReduce is a shared disk architecture through the globally visible parallel storage system.

The processing throughput of a MapReduce style system such as Hadoop is constrained by the bandwidth of a non-blocking network switch.1  Non-blocking switches can, however, handle large bandwidth aggregates due to the parallelism inherent in the design, so they are seldom a significant practical constraint on performance.  This means that a shared disk architecture (perhaps better referred to as a shared-storage system) can scale to large workloads even though the network switch is theoretically a central bottleneck.

The original point was to note that although this central bottleneck exists in shared-disk systems, a partitioned storage subsystem with multiple storage nodes (e.g. BigTable tablet servers or SAN controllers) can still scale up to large workloads.  A non-blocking switch architecture can (in theory) handle as many current connections as it has ports.

1 Of course the processing and I/O throughput available also constitutes a limit on performance but the network switch is a central point through which all traffic passes.


Fast DELETE with double subquery on big table for dupe remover

I wonder if that works at all, MySQL always had a stupid restriction: You cannot delete from a table and select from the same table in a subquery :-)

So create a temporary table first with the result of

select min(id)
from mytable
group by text;

And use this in your delete

select from mytable
where id not in
 ( select id from mytemptable);


importing postgres tar file throws parallel not supported in postgres 12

Because the "tar" format cannot be restored in parallel.
Use the "custom" or "directory" format of pg_dump so that it can be restored with parallel processes.
But Jeff had a good idea (see the comment): If you untar the backup into a directory, you can then do a parallel restore by pointing pg_restore to that directory.


postgresql compare column value from first row only

You could it much more simply:

SELECT end_datetime > now() AS result
FROM history
ORDER BY start_datetime DESC
LIMIT 1 ;

The query is similar to your subquery, it returns only one row, the latest start_datetime. The end_datetime > now() in the select list is a boolean expression so it retuns either TRUE or FALSE, depending on the value of end_datetime in that row. We don't need to join this result set to the table because all the information we need is there, in the one row.

You could modify the select list, to include other columns, too, or all the columns if you need them:

SELECT end_datetime > now() AS result,
       start_datatime,
       end_datetime
...

For efficiency, you need an index on (start_datetime). Your query would be efficient, too, despite the unneeded self-join.


Log Shipping with Database Snapshot for Read Queries

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

Yes, all users has to be disconnected before the logs can be restored.

  (2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores
  occurs later on database, will be snapshot break or continue to
  operate normally?

This depends on how you setup your Log shipping session. While setting up the Transaction log shipping, you have the option to Forcibly disconnect any users connected to the database or wait for users to disconnect, before the logs can be restored. But either way all users must be disconnected before the transaction logs can be restored.

Depending on your business needs (Preferably, get the business people involved in this decision) you can set the precedence for Log restore or Snapshot.


Data and index file compatibility between patch/revision releases of MySQL

As long as you are dealing with two GA releases in the same series (5.6.y to 5.6.x), you should be okay, because that is not an unsupported operation.

  To downgrade between General Availability (GA) status versions within the same release series, typically you just install the new binaries on top of the old ones and do not make any changes to the databases.

  https://dev.mysql.com/doc/refman/5.6/en/downgrading.html

Or, better (I think) than just copying the new (old version) binaries over the old (new version), use the mechanism I use, where /usr/local/mysql is a symlink to the directory where the live version binaries are extracted, and data, inside that directory, is a symlink to the datadir.

This is a perfectly valid configuration and it allows both sets of binaries coexist on the same machine.  I use the "Linux generic" binary tarball, which extracts the files into a directory named after the release and OS... as a DBA, I don't let the distro's package manager anywhere near my MySQL.  Nobody decides when we upgrade or what version we run, other than me, so upgrades occur when planned and after testing.

With this setup, it's a quick operation to stop the service, move one symlink, and start the service, and I'm running the new version... and similarly quick to switch it back, if you have to.  I still do it exactly this way, though I am a lot less edgy about the possibility of needing to roll back.

You also need to run mysql_upgrade to finalize the upgrade process, but you don't typically have to do it immediately, during a GA to GA release upgrade.  You can do it after the server is back up, and, in fact, the server has to be running before you can do that part.

Of course, during any upgrade, it's critical to have a mysqldump of your data ready to reload in an emergency... but within the same release series, with GA versions, you should be able to pull this off... but also, you shouldn't need to.  The biggest jump I recall  doing in one shot was 5.1.34 to 5.1.69, with no ill effects, and of the dozens of machines I manage, I don't recall a 5.5 or 5.6 upgrade that I had any reason to roll back.

Another suggestion for before you upgrade is to sweep the entire server for cobwebs with mysqlcheck --check-upgrade --all-databases which confirms that the tables are fully structurally compatible with the current version you're running... and then mysqlcheck --optimize --all-databases.  The latter is recommended (by me) not because of what it does, but because it's likely to find sketchy structural issues that could cause problems that appear to be upgrade-related, but were in fact latent corruption just waiting to surface.  Optimize is time-consuming and will lock each table while running, then unlock it and move to the next, so it's potentially disruptive, but with InnoDB, it also defragments each table, so you might reclaim some disk space in the process.  Skip the optimize if you can't tolerate the potentially long locks it needs while it's running.


Cannot restore data using Postgres WAL files

You need to read up on how backup and recovery works. WAL doesn't let you go backwards in time, only forwards.
When restoring you would normally restore your data files from your backup (eg one taken with pg_basebackup). That will get you back to the moment the backup was started. If you want to go further in time, you need to supply the WAL files for changes made since the backup. In your recovery_conf you can specify to restore to the time just before the table was dropped using recovert_target_time =  as you have done.
Normally such a restore would be done in a secondary environment. You would then export the dropped table(s) or modified data and import that into your production database (or run update/insert/delete statements to change the state of the database). That way you don't lose other modifications made since the table was dropped.


How does a subquery work in sql server 2012?

The answer is that they run in parallel (although the sub-query starts first, according to the execution plan) and then are merged to give you the result you want.

You can see this for yourself if you select the button to display either the estimated execution plan or the actual execution plan.  The last item is your query.  The execution plan is read from bottom right to upper left.

But as a bonus, there is a better way to get that answer, and will allow you to get the answer for any Nth highest salary.  The query below uses the temp table you have defined above.  It uses ROW_NUMBER() with the PARTITION and ORDER BY clauses to rank the salary per ID.

;WITH CTE_Temp AS
    (
    SELECT id
        , sal
        , SalRank = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY sal DESC)
    FROM #tmpTbl
    )
SELECT * FROM CTE_Temp WHERE SalRank = 3
;


Why does the Tuple Mover in C-Store consider only rows older than LWM?

Given that the referenced paper is 10 years old, I would recommend looking at a  The Vertica Analytic Database: C-Store 7 Years Later since Vertica has more automatic epoch advancement mechanisms.

For reference, the acronyms used now are:

WOS - Write Optimized Store
ROS - Read Optimized Store
AHM - Ancient History Marker (Low Water Mark)
LGE - Last Good Epoch

A quick overview of how epoch's work in Vertica:

  I could not understand, when moving records from WS (write optimized storage) to RS (read optimized storage), why the tuple mover considers only records older than LWM?

Vertica will automatically advance the epoch as a background process. In the example below, once data is committed, it will belong to the current epoch.

-- Get the current epoch
dbadmin=> SELECT CURRENT_EPOCH FROM system;
 CURRENT_EPOCH
---------------
           238
(1 row)

-- Insert a row into the table without committing (WOS)
dbadmin=> INSERT INTO tbl (a) VALUES (1);
 OUTPUT
--------
      1
(1 row)

-- Get the epoch for the row
dbadmin=> SELECT a, epoch FROM tbl;
 a | epoch
---+-------
 1 |
(1 row)

-- Commit the insert
dbadmin=> COMMIT;
COMMIT

-- Get the epoch for the row
dbadmin=> SELECT a, epoch FROM tbl;
 a | epoch
---+-------
 1 |   238
(1 row)

  Doesn't this mean that all the rows that were inserted in the system after LWM would only be in WS?

It does not. WOS is just a temporary storage location until the data gets moved to ROS. The epoch is just a way to manage transactions.


Try this:. It uses DateFromParts(year, month, 1) to find the correct month for any value 1..12 or it uses DateAdd(week, date) to figure for any value 13..52. This way, we let the database engine worry about pesky things like leap year for us rather than doing that kind of math on our own.

Create or alter function udf_SplitYear(
    @TargetYear numeric(4,0),
    @SplitCount tinyint )
returns @ReturnVals table (
    [Year] numeric(4,0),
    SplitCount numeric(3,0),
    SplitNum numeric(3,0),
    StartDate datetime,
    WeekNum numeric(2,0)
)
as
 -- Split this into evenly divisible blocks based on months or weeks.
begin
    -- Valid dates for SQL Server are 1/1/1753 to 12/31/9999  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
    If @TargetYear < 1753
        Return;
    if @splitcount < 2      --cannot split something into an unsplit thing...
        Return;
    if @SplitCount > 52     --not going to divide this year into days or worse, hours...
        Return;

    declare @rowNumber int = 1;
    declare @PreviousDate datetime = DateFromPartS(@TargetYear, 1, 1);
    declare @ThisDate datetime = DATEFROMPARTS(@TargetYear, 1, 1);
    declare @dateGap int = 0;
    declare @Divisor int;
    declare @ThisSplit int = 1;

    -- 01 Jan <year>
    insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, 1, @ThisDate, DatePart(week, @ThisDate));

    if @splitCount < 13
      Begin
        --split across month boundaries
        set @Divisor = Round(12 / @SplitCount, 0)
        set @ThisSplit =  @Divisor + 1;
        While @rowNumber < @SplitCount
        Begin
            set @ThisDate = DATEFROMPARTS(@TargetYear, @ThisSplit, 1);
            set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
            insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
            set @PreviousDate = @thisDate;
            set @ThisSplit = @ThisSplit + @Divisor;
            set @rowNumber = @rowNumber + 1;
        End;
      end
    else
      begin
        --split across weeks.
        set @Divisor  = Round(52 / @SplitCount, 0);
        set @ThisSplit = @Divisor ;
        While @rowNumber < @SplitCount
        Begin
            set @ThisDate = DateAdd(wk, @ThisSplit, DateFromParts(@TargetYear, 1, 1));
            set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
            insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
            set @PreviousDate = @thisDate;
            set @ThisSplit = @ThisSplit + @Divisor;
            set @rowNumber = @rowNumber + 1;
        End;
      end;

    Return
end

This

Select * from udf_SplitYear(2019, 3)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   3   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   3   |   5   |   2019-05-01 00:00:00.000   |   120
2019   |   3   |   9   |   2019-09-01 00:00:00.000   |   123

This

Select * from udf_SplitYear(2019, 6)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   6   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   6   |   3   |   2019-03-01 00:00:00.000   |   59
2019   |   6   |   5   |   2019-05-01 00:00:00.000   |   61
2019   |   6   |   7   |   2019-07-01 00:00:00.000   |   61
2019   |   6   |   9   |   2019-09-01 00:00:00.000   |   62
2019   |   6   |   11   |   2019-11-01 00:00:00.000   |   61

This

Select * from udf_SplitYear(2019, 26)

Gives:

Year   |   SplitCount   |   SplitNum   |   StartDate   |   WeekNum
2019   |   26   |   1   |   2019-01-01 00:00:00.000   |   1
2019   |   26   |   2   |   2019-01-15 00:00:00.000   |   3
2019   |   26   |   4   |   2019-01-29 00:00:00.000   |   5
2019   |   26   |   6   |   2019-02-12 00:00:00.000   |   7
2019   |   26   |   8   |   2019-02-26 00:00:00.000   |   9
2019   |   26   |   10   |   2019-03-12 00:00:00.000   |   11
2019   |   26   |   12   |   2019-03-26 00:00:00.000   |   13
2019   |   26   |   14   |   2019-04-09 00:00:00.000   |   15
2019   |   26   |   16   |   2019-04-23 00:00:00.000   |   17
2019   |   26   |   18   |   2019-05-07 00:00:00.000   |   19
2019   |   26   |   20   |   2019-05-21 00:00:00.000   |   21
2019   |   26   |   22   |   2019-06-04 00:00:00.000   |   23
2019   |   26   |   24   |   2019-06-18 00:00:00.000   |   25
2019   |   26   |   26   |   2019-07-02 00:00:00.000   |   27
2019   |   26   |   28   |   2019-07-16 00:00:00.000   |   29
2019   |   26   |   30   |   2019-07-30 00:00:00.000   |   31
2019   |   26   |   32   |   2019-08-13 00:00:00.000   |   33
2019   |   26   |   34   |   2019-08-27 00:00:00.000   |   35
2019   |   26   |   36   |   2019-09-10 00:00:00.000   |   37
2019   |   26   |   38   |   2019-09-24 00:00:00.000   |   39
2019   |   26   |   40   |   2019-10-08 00:00:00.000   |   41
2019   |   26   |   42   |   2019-10-22 00:00:00.000   |   43
2019   |   26   |   44   |   2019-11-05 00:00:00.000   |   45
2019   |   26   |   46   |   2019-11-19 00:00:00.000   |   47
2019   |   26   |   48   |   2019-12-03 00:00:00.000   |   49
2019   |   26   |   50   |   2019-12-17 00:00:00.000   |   51


Design database to query a date range for scheduling

...if I put fields like begin_time, end_time in every row, then when I
  do the query the database engine has to walk all rows, for example
  with a consult like

If you create an index on begin_time and an index on end_time, your dbms should use the indexes to avoid a full table scan. I just tried that with PostgreSQL and a table of 50k rows. It used the index on begin_time, and returned in 0.02ms. A multi-column index (an single index on both begin_time and end_time) might perform even better.

You might want to update a column in those rows to make sure the notification (or whatever) doesn't happen every minute between begin_time and end_time.


The only way MyISAM can be faster that InnoDB would be under this unique circumstance
MyISAM
When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this:
ALTER TABLE mytable ROW_FORMAT=Fixed;

I wrote about this in my past posts

Best of MyISAM and InnoDB (Please Read This One First)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Optimized my.cnf for high-end and busy server (Under the heading Replication)
Which DBMS is good for super-fast reads and a simple data structure? (Paragraph 3)

InnoDB
OK, what about InnoDB? Does InnoDB do any disk I/O for queries? Surprisingly, yes it does !! You are probably thinking I am crazy for saying that, but it is absolutely true, even for SELECT queries. At this point, you are probably wondering "How in the world is InnoDB doing disk I/O for queries?"
It all goes back to InnoDB being an ACID-complaint Transactional Storage Engine. In order for InnoDB to be Transactional, it has to support the I in ACID, which is Isolation. The technique for maintaining isolation for transactions is done via MVCC, Multiversion Concurrency Control. In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O.
COMPARISON
Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?

Size of Columns
Column Format
Character Sets
Range of Numeric Values (requiring large enough INTs)
Rows Being Split Across Blocks (Row Chaining)
Data Fragmentation caused by DELETEs and UPDATEs
Size of Primary Key (InnoDB has a Clustered Index, requiring two key lookups)
Size of Index Entries
the list goes on...

Thus, in a heavy-read environment, it is possible for a MyISAM table with a Fixed Row Format to outperform InnoDB reads out of the InnoDB Buffer Pool if there is enough data being written into the undo logs contained within ibdata1 to support the transactional behavior imposed on the InnoDB data.
CONCLUSION
Plan your data types, queries, and storage engine real carefully. Once the data grows, it might become very difficult to move data around. Just ask Facebook...


How can I combine a large CROSS JOIN query and return 1 result?

Try this using jsonb_object_agg(), and jsonb_build_object(k,v)

CROSS JOIN (
  SELECT json_agg(jsonb_build_object('plan', planName, 'count', subscribers) ORDER BY subscribers DESC) as popularplans
  FROM (
    SELECT *
    FROM plans
    WHERE userid=$1 AND status='active'
    FETCH FIRST 6 ROWS ONLY
  )
) popularplans


User created new login - how to find where they got permissions from

As first thing I'd check server level permissions of that login:

exec as login = 'TheLogin';
select * from sys.fn_my_permissions(null, 'server');

As second thing I'd check login's groups/roles, like this:

exec as login = 'TheLogin';
select *
from sys.login_token
where principal_id > 0;

And my future actions depend on what I've got for now.
The thing may be tricky: One can have impersonate on sa or be a db_owner of trustworthy's database, so he has "hidden" permissions


PostgreSQL cluster has two primary databases after network glitch

the bad news is: so far as I know the only solution is to demote one of your primaries and re-clone.

the good news is: rsync should be able to do the cloning fairly quickly if most of the data is unalteted,


Query that marries data between two databases

If you have doubts / questions about your database design, you can improve your question and continue.

Otherwise, let us assume that the database design is perfect - atleast as databases.

You shall start creating views - per requirement - comprising of both databases. And can make all your other select queries to look at the appropriate view.

They are much faster.

Arriving at a most wanted set of columns of rows matching the where condition through a view is easy.

You shall even think of arranging a view which has everything into it (all rows and all columns - just eliminating duplicates) and use that view in all your queries. This itself will never hinder the performance for sure.


One way to find the cause of ORA errors is to use oerr tool by Oracle. In the error stack the top most is the application error which is followed by other secondary errors.

For example, the ORA-39002 and ORA-39070 are Datapump errors. Lets see what the oerr tool says for ORA-39070.

  [oracle@localhost ~]$ oerr ORA 39070
  39070, 00000, "Unable to open the log file."
  Cause:  Errors were detecting while opening the log file.  Subsequent
           messages will detail the problems.
  Action: Fix the problems outlined in the secondary messages.

The action is to fix the problem outlined by the secondary error message that is ORA-29283.

  [oracle@localhost ~]$ oerr ORA 29283
  29283, 00000, "invalid file operation"
  Cause:  An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
  Action: Verify file and directory access privileges on the file system,
            and if reading, verify that the file exists.

And that's it.


MySQL select by primary key against 100M of records within 30ms

The problem here is "large number of queries" which you likely have other ideas of than me (who says sure - you need a lot of hardware for 30.000 queries per second).

Generally otherwise - yes. This is possible.

Lots of memory needed ;) keep it all in memory.

O rget a proper disc subsystem. Seriously, your "regular hard disc" is dead slow. Like terribly slow. SSD area good and low cost alternative (roughly 550 times as fast as your hard disc in random access). Unless you RAM up to keep it all in memory your disc IO is the typical bottleneck... and SSD are the cheapest solution. Unless you REALLY up your RAM you simply have no other alternative than gettig some decent IO capabilities, the (end user level) hard disc (i.e. a non SAS 15k high performance disc) just wont get you far.


How to restrict users to select only query through application textbox field?

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything.

Be careful about granting execute permissions, as you may inadvertently allow users to change data depending on what the procedures do.


What types of corruption can DBCC CheckDB miss?

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.
First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:

You're right - if torn-page or checksum isn't turned on then there's nothing
that can be detected as far as page protection options are concerned.
CHECKDB may still pick up on corruptions that it finds from doing all the
consistency checks that it does - but it won't see corruptions in the middle
of data values, for instance.

Ha - that's the bummer about turning on page checksums - nothing happens
until a page is read in, changed, and written back out. The only way to
force pages to get checksums is to make them change - e.g. through
rebuilding all your indexes, whcih may be unpalatable - there's no 'touch'
tool out there at all.

The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.
BACKUP WITH CHECKSUM will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".
Now there is a second possibility for DBCC CHECKDB not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":

So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. Don't get confused between reserved and allocated - I explain that in the first misconceptions post here. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is allocated at the time a DBCC CHECKDB runs, but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt, but the second time it's not allocated, so it isn't consistency checked and won't be reported as corrupt. The corruption looks like it's mysteriously vanished. But it hasn't - it's just that the corrupt page is no longer allocated. There's nothing stopping SQL Server deallocating a corrupt page - in fact, that's what many of the DBCC CHECKDB repairs do - deallocate what's broken, and fix up all the links.

I don't have a final answer to your question, but as DBCC CHECKDB only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB.


Adding a column to aggregated data where new value only applies to a subset of the aggregation

SELECT  `Label`, `Value`, ID
    FROM  table1
    LEFT JOIN  table2 USING(Label, Value);


Your first query plan shows parallelism, whereas your second query is purely serial; this is why the second version is showing longer "duration".

The key lookup operations could be prevented by a suitable covering index for the tables where the key lookup is occurring.  The standard warning about not blindly creating indexes applies here - don't create duplicate indexes, and check to see if you can leverage an existing index by possibly adding an include clause.  For instance, the key lookup on the Customers table is pulling these columns, which it couldn't get by scanning the IX_CustomersSocialSecurityNumber index:

[GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].lastName
, [GoOutdoorsTN_TEST].[dbo].[Customers].DocTypeNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseState

If you added those columns to the index in an INCLUDE clause, that scan would not need to go back to the table to get those columns, making the output that much faster.

Your query uses the "kitchen sink" pattern; i.e. this:

WHERE (@x IS NULL OR someCol = @x)
     AND (@y IS NULL OR someOtherCol = @y)

You can typically get much better query plans, customized for each variation, using dynamic SQL instead of the @x IS NULL piece.  Pseudo-code would be:

IF @x IS NULL AND @y IS NOT NULL
    SET @where = 'WHERE someOtherCol = @y';
IF @y IS NULL AND @x IS NOT NULL
    SET @where = 'WHERE someCol = @x';
IF @y IS NULL AND @x IS NULL
    SET @where = '';

This allows the query optimizer to use column statistics in a far more effective manner, since it only needs to think about the columns presented in each unique where clause.

Also of note, I see you're using WITH (NOLOCK) in an effort to prevent your query being affected by blocking.  You may want to ensure you understand the effects of reading uncommitted rows inherent in the READ UNCOMMITTED isolation level used by the NOLOCK hint.  Aaron Bertrand has a great article about that here

I've noticed the plans show a couple of computer scalar operators where you're doing:

= LTRIM(RTRIM(lastName))

Does your data really have blank space around the real content of lastName?  If not, getting rid of those needless functions will really help the query processor provide better plans.

As a way of showing how you might approach the kitchen sink problem, and strictly for learning purposes, consider the below code.

CREATE TABLE dbo.Customers
(
    CustomerID int NOT NULL
        CONSTRAINT PK_Customers
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FirstName nvarchar(100) NOT NULL
    , LastName nvarchar(100) NOT NULL
    , SSN char(9) NULL
);

Some sample data:

INSERT INTO dbo.Customers (FirstName, LastName, SSN)
VALUES ('Joe', 'Belfiore', '012345678')
    , ('Bill', 'Gates', '876543210')
    , ('Max', 'Vernon', '123123123');
GO

A stored procedure to perform searches:

CREATE PROCEDURE dbo.SearchCustomers
(
    @FirstName nvarchar(100) = NULL
    , @LastName nvarchar(100) = NULL
    , @SSN varchar(9) = NULL
)
AS
BEGIN
    /*
        BE AWARE THIS IS PROTOTYPE CODE THAT IS NOT SAFE
        AGAINST SQL INJECTION VULNERABILIES.

        IT IS STRICTLY TO SHOW HOW TO COMPILE A DYNAMIC
        WHERE CLAUSE!
    */
    SET NOCOUNT ON;
    DECLARE @Where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @qry nvarchar(max);
    SET @qry = 'SELECT CustomerID, FirstName, LastName, SSN
FROM dbo.Customers c
';
    SET @where = 'WHERE ';
    SET @connector = '';
    IF @LastName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.LastName LIKE ''%' + @LastName + '%''';
        SET @connector = ' AND ';
    END
    IF @FirstName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.FirstName LIKE ''%' + @FirstName + '%''';
        SET @connector = ' AND ';
    END
    IF @SSN IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.SSN LIKE ''%' + @SSN + '%''';
        SET @connector = ' AND ';
    END
    IF @connector <> '' SET @qry = @qry + @Where + ';';
    EXEC sys.sp_executesql @qry;
    PRINT @qry;
END
GO

Some test searches:

EXEC dbo.SearchCustomers @FirstName = N'Max';
EXEC dbo.SearchCustomers @LastName = N'Vernon';
EXEC dbo.SearchCustomers @SSN = N'994', @LastName = N'V'

The queries show in the "Messages" tab are:

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.FirstName LIKE '%Max%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%Vernon%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%V%' AND c.SSN LIKE '%994%';

Before you implement that code, you really need to read Erland Sommarskog's seminal work on dynamic SQL.  He also has a great article about dynamic search which should help.


How Do I Use PowerShell to Get a SQL Server Backup Status?

Code gets backup history for all databases on a server and generates HTML report

Step 1:

   DECLARE @Body VARCHAR(MAX),
        @TableHead VARCHAR(MAX),
        @TableTail VARCHAR(MAX)
    SET NoCount ON ;

    SET @TableTail = '' ;

    SET @TableHead = '' + '' + '' + ''

    SELECT  @Body = ''

    SELECT  @Body = @Body + '
'
            + '
Database Name 	'
            + 'Backup Size	'
            + 'Time Taken	'
            + 'Backup Start Date	'
            + 'First LSN	'
            + 'Last LSN	'
            + 'Backup Type	'
            + 'Server Name	'
            + 'Recovery Model	'
            + 'Physical Device Name
'

    -- Backup History script of AAsim Adbullah
    -- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/
    SELECT  @Body = @Body
            + ( SELECT  td = s.database_name, '',
                        td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
                        td = CAST(DATEDIFF(second, s.backup_start_date,
                                           s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
                        td = s.backup_start_date,  '',
                        td = CAST(s.first_lsn AS VARCHAR(50)), '',
                        td = CAST(s.last_lsn AS VARCHAR(50)), '',
                        td = CASE s.[type]
                               WHEN 'D' THEN 'Full'
                               WHEN 'I' THEN 'Differential'
                               WHEN 'L' THEN 'Transaction Log'
                             END,'',
                        td = s.server_name, '',
                        td = s.recovery_model,'',
                        td = m.physical_device_name,''
                FROM    msdb.dbo.backupset s
                        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
                WHERE   s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
                ORDER BY backup_start_date DESC,
                        backup_finish_date
              FOR
                XML PATH('tr')
              ) + '
'

    SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
    SET @Body = REPLACE(@Body, '_x003D_', '=')
    SET @Body = REPLACE(@Body, '
<TRRow>1</TRRow>', '
')
    SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')

    SELECT  @Body = @TableHead + @Body + @TableTail

    IF ( @Body IS NULL )
        BEGIN
                EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com',  -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = 'No Backup History Found for past 1 Day',
                @body_format = 'HTML' ;

        END
    ELSE
    Begin
        EXEC msdb.dbo.sp_send_dbmail @recipients='sqlcache@blogspot.com', -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'AA.SC', -- Change Profile Name
                @body = @Body,
                @body_format = 'HTML' ;
    END

Step 2:
create a new job add this code in step and schedule it according to your requirement


Ok I got the answer.

I used the following:

EXEC sys.sp_cdc_add_job
 @job_type = N'cleanup'
,@start_job = 0
,@retention = 52494800;

This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)


When mysql innodb undo log write and fsync to disk?

Make data changes to the blocks in the buffer_pool, but do not write them to disk.
Put changes to secondary indexes (eg for INSERT) into the Change Buffer for later processing.  Again, no write to disk (yet).
Write and fsync the undo log file if innodb_flush_log_at_trx_commit = 1 (else this write/fsync is delayed).
Tell the client that the COMMIT is finished.

The idea is to have no more than one fsync per COMMIT.  Otherwise, performance would be really bad.

If you are using Replication, then the binlog is written to (see log_bin) and possibly fsync'd (see sync_binlog).  And the data is shipped to each Slave.  I don't know exactly where these happen in the sequence.

If you are using Galera / FXC / Group Replication, the syncing to the other nodes adds another dimension to the question.  (I'll assume you don't need these details.)


Browse for Servers does not display local instance

Your title says you have an installation problem, but I don't think this is the case (it seems like you have installed SQL Server okay, you just can't find it). Of course I don't know exactly what options you selected during setup, but from the programs applet, it seems like you should have at least one instance installed.

The Browse for Servers dialog is hokey and won't always find all instances, even on your own computer, even on the best days. I would rather use Configuration Manager to determine what's installed. Find the most recent version of this, since older versions of the Configuration Manager won't be able to see/manage newer versions of SQL Server (but the older versions of Config Manager don't get replaced or uninstalled).

If you can't find Configuration Manager, you can add it to any generic MMC console. Run mmc from the command line:

mmc.exe

(If you have that awful thing called UAC enabled, you may have to give the tool permission to run.)

Then choose file > add/remove snap-in:

Then pick SQL Server Configuration Manager and click Add (if there are multiple, they are from different versions, but not labeled correctly, so add all of them), then click OK.

Then open SQL Server Services, and you will see the list of instances on the right. If you had to add multiple nodes, make sure you check all of the nodes, since it is impossible to tell visually which node represents which version of Config Manager.

If that list is empty, you did not install SQL Server correctly.

To connect to one of those, first you need to make sure it's running; if not, right-click and select Start:

Now in Management Studio you should be able to connect by entering the right instance name. It will be in this form:

(local)\instance_name_from_within_brackets_in_the_list

So, once I have started that instance picture above, I would enter the following in the SSMS connect to server dialog:

(local)\SQL2016_407

Unless the instance name in brackets is MSSQLSERVER - this is a special display name reserved for the default instance. In this case you should be able to connect using just (local).

There are some other exceptions as well - for example if you only installed LocalDB, this is not going to show up as an active instance in Configuration Manager - this is by design (the whole point of LocalDB is to remove instance-level management). For more information on using LocalDB, see this tip.


SQL Server: How much space needed to restore?

The size of the data and log files as seen by the operating system when the database is backed up, is what will be restored, regardless of how much free space is in a file. You can see exactly how large, in bytes, the data file will be when restored by executing this:

restore filelistonly from disk = 'location_of_backup_file.bak';

The [Size] column, 5th column along, has the value you can use to check whether you'll have enough drive space to restore a database.


mysql table very large size although it doesn't have a lot of records

When it comes to a MyISAM table, deleting rows does trigger a kind of garbage collection in that all the unused space is recording in a linked list.

The data length you saw is correct. Your cron job deleted every row less than UNIX_TIMESTAMP(NOW()). This will not cause the table to shrink. All the delete rows are just chained together.

THere are other options you could have chosen to shrink the table:

Option 1 : ALTER TABLE cache_form ENGINE=MyISAM;
Option 2 : OPTIMIZE TABLE cache_form;
Option 3 : Manually copy to a temp table

Option 1 simply does the following

copies data pages in the .MYD to a temporary .MYD and renames it back to cache_form.MYD.
copies index pages in the .MYI to a temporary .MYI and renames it back to cache_form.MYI.

Naturally, Option 1 will not copy the unused space.

Option 2 performs the same operation as Option 1 and then performs ANALYZE TABLE cache_form; to compute index statistics.

Option 3 would simply be an emulation of Option 1. Here it is:

CREATE TABLE cache_form_new LIKE cache_form;
INSERT INTO cache_form_new SELECT * FROM cache_form;
ALTER TABLE cache_form RENAME cache_form_old;
ALTER TABLE cache_form_new RENAME cache_form;
DROP TABLE cache_form_old;

Whenever you did the compression this way, find out what data_length is for cache_form_new. This will give you an idea what the actual size should be.


Indexing varchar column to search for first letter

I'm not sure what DBMS you are using but in general yes.  It looks like your index is doing what it is supposed to.  As a general case the optimizers can take advantage of an index in a "like" query if there are no pattern (_%[] etc) characters on the left hand side.

So this will use an index

oggetto.titolo like 'C%'

But this could not

oggetto.titolo like '%C'


Find objects linked to a PostgreSQL role

Answer to question asked

To look for the function in the error message and its owner:

SELECT oid::regprocedure AS function
     , pg_get_userbyid(proowner) AS owner
FROM   pg_proc
WHERE  oid = 'text(boolean)'::regprocedure;

Related:

DROP FUNCTION without knowing the number/type of parameters?

Actual problem

The error message says:

  DETAIL:  privileges for function text(boolean)

It's not about ownership but about privileges.

The manual for DROP ROLE:

  Before dropping the role, you must drop all the objects it owns (or
  reassign their ownership) and revoke any privileges the role has been
  granted on other objects.

And for ALTER DEFAULT PRIVILEGES:

  If you wish to drop a role for which the default privileges have been
  altered, it is necessary to reverse the changes in its default
  privileges or use DROP OWNED BY to get rid of the default privileges
  entry for the role.

It also looks like you only executed REASSIGN OWNED in one DB, but the manual instructs:

  Because REASSIGN OWNED does not affect objects within other
  databases, it is usually necessary to execute this command in each
  database that contains objects owned by a role that is to be removed.

Bold emphasis mine.

And you restricted your commands with IN SCHEMA public. Drop that clause to target the whole DB. But don't bother, there is a ...

Simple solution with DROP OWNED

REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;

All the role's objects changed ownership to postgres with the first command and are safe now. The wording of DROP OWNED is a bit misleading, since it also gets rid of all privileges and default privileges. The manual for DROP OWNED:

  DROP OWNED drops all the objects within the current database that are
  owned by one of the specified roles. Any privileges granted to the
  given roles on objects in the current database and on shared objects
  (databases, tablespaces) will also be revoked.

Repeat in all relevant DBs, then you can move in for the kill:

DROP ROLE user1;


Force SQL Server to use fragmented indexes?

Index Hint

You can use an index hint to do that:

SELECT *
FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index)
WHERE u.Reputation = 2;

The downsides are:

Potentially changing a lot of code
If you rename an index, this breaks
If you change an index definition, it might not be the best index to use anymore

Plan Guides

You can also use a Plan Guide, which sets the plan you want in place.

The downsides are:

It's fickle; if your query isn't exactly the same, your plan might not get used
It's tough to set up; you have to get everything exactly right
Also the same stuff as an index hint, so uh... That's rough.

Query Store?

Since you're on SQL Server 2017, you may be able to use Query Store to force plans. This is much easier than using a Plan Guide, but you may end up with an unexpected plan.

Downsides:

Same stuff as above
Having to enable Query Store, if you're not already using it
Having to query Query Store to see if forced plans are failing

Alternatives

Without knowing more, I'd probably be more keen on investigating if the index use changes for other reasons, like parameter sniffing.


What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I don't think you will see a performance difference between trigger and using cascade. I would prefer ON CASCADE DELETE just because in my opinion it better describes model and reveals creator's intention. Also, if you later use one or another ORM, it will be able to build relationship between entities (surely, I'm talking about 'Data First' approach)if it's implemented through foreign key constraints, but not through the triggers.


Calculate number of consecutive years (not gapless)

So I've built a short SQL script to do this operation, based on the original sample input data:

Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234

The script is as follows:

WITH RECURSIVE ranked_order AS(
  SELECT order_year, contact, 1 AS counter FROM orders
  UNION
  SELECT orders.order_year, orders.contact, ranked_order.counter + 1
  FROM orders JOIN ranked_order
  ON orders.order_year-1 = ranked_order.order_year
  WHERE orders.contact = ranked_order.contact
),
max_from_ranked_order AS(
  SELECT order_year AS report_year, contact, max(counter) AS consecutive_year
  FROM ranked_order
  GROUP BY order_year, contact
),
start_and_consecutive_years AS(
  SELECT  contact, max(consecutive_year) AS consecutive_years,
  report_year - consecutive_year AS start_year
  FROM max_from_ranked_order
  GROUP BY contact, start_year)
SELECT *, start_year + consecutive_years AS end_year
FROM start_and_consecutive_years
ORDER BY contact;

Now, for a little explanation:

Recusive Query

First, you need to find all the occasions of consecutive years, so I built the ranked_order table(maybe a bad name... maybe should have called it counted_order?) to count instances where a given contact ordered in back-to-back years.

Filter Results

So, due to the recursive query, I am left with all counted elements, rather than simply the maximum consecutive counts.  So, I built max_from_ranked_order to extract the max counter value per order_year,contact grouping.

A little arithmetic...

This still doesn't quite yield what we need.  Since we grouped over order_year,contact, you still end up with entries like

report_year, contact, consectuive_year
2015, 123, 3
2014, 123, 2
2013, 123, 1
2015, 234, 2
2014, 234, 1
etc...

What you'll notice is that you have unique, information which you can group on by observing the starting year of the consecutive years.  So, I created start_and_consecutive_years to take the maximum number of consecutive purchasing years for any given starting year, on a per customer basis.

Finally, I get my result by adding the consecutive_years to the start_year, and presto, the final SELECT yields:

contact, consecutive_years, start_year, end_year
123, 3, 2012, 2015
123, 2, 2009, 2011
234, 2, 2013, 2015
234, 1, 2010, 2011

Note that it would be very easy to filter out for casesWHERE consecutive years >= 2 to ignore "irrelevant" results.

Now for the caveats: I wrote this series of CTEs for ease of readability and explanation.  There may be a more computationally or programatically efficient way to achieve your results, but on even a moderately sized table this should get you your results pretty quick.


How long time does it take to TRUNCATE 50 million rows?

In normal circumstances this should take very little time since it is a metadata change. Whereas a regular delete will delete all data row by row, a truncate operation will deallocate all data pages instead.

Be aware though that a truncate table requires a Sch-M lock, so on a busy environment it could take a while before it can get hold of its lock and truncate the data. If your statement keeps running for a long time, it's probably locked by (an)other transaction(s).

If you want to be sure, try it on a copy.


How to generate this MongoDB diagnostics HTML report?

These charts are produced by some internal tools being developed by the MongoDB engineering team for working with time series data as sampled from metrics like MongoDB's serverStatus command and Linux tools such as  iostat. There have been a few iterations of tools using languages like Python and Go, but usage and interpretation of data is currently more of an art for engineers familiar with the MongoDB internals. A general challenge is filtering the available metrics into those that are relevant (or possibly relevant) for a performance problem you are trying to understand. The chart images you see in Jira are the result of engineers manually analyzing and filtering metrics of interest.

I'm not aware of a public version of these tools yet, however you should be able to find a library for generating similar compact charts for metrics of interest: the chart format is called sparkline.  All of the metrics in your screenshot are from the wiredTiger section of the serverStatus command.

For operational metrics and monitoring, a production environment would normally feed into a metrics aggregation server or service which could provide more interactive monitoring and alerting across your entire deployment(s). For example, MongoDB Cloud Manager (SaaS) and MongoDB Ops Manager (on-premise) both include charts for common metrics. If you want to implement more custom monitoring, a good reference is the list of commands used by the MongoDB Cloud Manager monitoring agent.

MongoDB 3.2+ also includes an FTDC (Full-Time Data Capture) feature which periodically logs metrics (serverStatus, replication status, ...) to compressed files in the  diagnostic.data directory in the MongoDB dbPath. The diagnostic history is limited to 200MB of metrics as at MongoDB 3.4. You can see what commands are used in the FTDC source code, eg: src/mongo/db/ftdc/ftdc_mongod.cpp. For versions of MongoDB prior to 3.2 the engineers used to request the same information be collected manually for relevant problematic periods. FTDC information can be useful for MongoDB engineers to be able to request recent metrics history to help diagnose issues for postmortem analysis without having access to your full monitoring solution.


Should the filtering column(s) always be in the keys / includes?

Yes!

For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes

The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.

Key lookups when the query predicate doesn't match the filter expression

First of all, the documentation has this to say about including filter expression columns:

  A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.

Including the Reputation column in the index (key or includes) removes the need for this lookup.

See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.

Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index

Key lookups when the filtering column is included in the resultset

The documentation goes on to say this:

  A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).

Poor row estimates when using equality expressions

There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan).  Including the filtering column can prevent these more-accurate estimates from being discarded.

More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index

An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows

Key lookups when using IS NULL in the filtering expression

Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup.  See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?

As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.


Performance of rare SELECT vs. frequent INSERT in timeseries data

Create an index on (item_id, event_time).

It will jump to the specified item_id, jump to the specified event_time for that item_id, and then move back one.  No sorting involved.


In PostgresQL, how do I make a query match exactly one row or fail?

As mentioned by Arkhena, use a scalar subquery to determine the row to access:

DELETE FROM MyTable
WHERE ID = (SELECT ID
            FROM MyTable
            WHERE ...whatever ...);

If the subquery returns more than one ID, the query fails.
This requires some unique key (here: ID).

If you do not want to get an error from the query, you can use CTE for the filter, and then check the number of rows in the CTE:

WITH r AS (
  SELECT ID
  FROM MyTable
  WHERE ...whatever...
)
DELETE FROM MyTable
WHERE (SELECT count(*) FROM r) = 1
  AND ID IN (SELECT ID FROM r)
RETURNING ID;

This statement will return an ID value only if one row was actually deleted.


What's the best way to diagram this "relation" looking for performance and|or disk space savings

If you have a lot of specific statistics about how often your optional columns will be null and how often the non-null columns need to be read (as opposed to your core, mandatory columns) then you could calculate space savings (or not) and at the very least you could derive some performance experiments to test each approach.

There is no rule of thumb for whether it is "better" to segregate optional columns into a separate table.

"Better" is a subjective term.  What is of value?  Disk space, CPU cycles, query response times, simplicity of code? You can't consider the relative merits of one approach over another without first considering what it is that you're trying to optimize.

There are a lot of reasons why you might want to move optional columns to a separate 1:1 related sub-table.  See my answer to this question for more discussion of these reasons.

In your case, since you're concerned about space, you need to keep in mind a few things about how data is physically stored:

Variable size strings don't take up much space if they are empty.
Every nullable column needs a bit to indicate whether it is null or not.
A separate table with optional columns needs to store the primary key of the main table again (as a foreign key) and there may be a separate index on this column as well.

There are a lot of competing influences on the matter of which takes less space and which performs better.  You need to consider:

How often is the optional data going to be missing?  If it's really rare then maybe you will save space by segregating it out.
How often do you need to read the optional data?  If you read it every time (whether it's there or not) then maybe joining for it all the time is less efficient.
On the other hand, if it isn't there very often and if you hardly ever read it, even when it is, then maybe it's more efficient to read shorter records containing only the mandatory columns?

Another thing to consider, perhaps even the most important thing to consider, is whether you are overthinking the issue by trying to pre-optimize.  Disk is pretty cheap.  CPU is pretty cheap.  Programmers are expensive.  Unless you need to be concerned about massive scale, maybe the best answer is the one that leaves you with the simplest (least buggy, easiest to maintain) code.


Is there a common approach to encrypting data at the database level?

Depending on your security restrictions you could:

use cell-level encryption (basically column-level encryption), which decrypts the data once it hits the buffer pool; it's available in all SQL Server versions, including Express
encrypt/decrypt data in your own application, so unencrypted data is never present in the database server's memory


MySQL: How to fetch nth level child menus of a parent menu

A long, long time ago (Oct 24, 2011), in a galaxy far away, someone boldly asked

Find highest level of a hierarchical field: with vs without CTEs

In my answer to that post, I wrote three stored procedures to find specific relationships

GetParentIDByID
GetAncestry
GetFamilyTree

If you use the code in GetFamilyTree, just pick the nth member of the output.

I have referred others to that post

Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
Jul 11, 2013 : Recursive self joins
Dec 10, 2012 : MySQL: Tree-Hierarchical query

Give it a Try !!!


Data inconsistency prohibition if a table refers to another via two many-to-many relationships

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company.

This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships:

Realm          Company
   \           /     \
    \         /       \
     \       /         \
    CompanyRealm     Project
             \         /
              \       /
               \     /
             ProjectRealm

The common solution, if you want to enforce this via DRI (Declarative Referential Integrity), ie. through usual UNIQUE, FOREIGN KEY and CHECK constraints - and not though triggers - is to use composite keys for the ProjectRealm REFERENCES CompanyRealm and for the ProjectRealm REFERENCES Project foreign keys.

You will need to replace the company_realm_id with 2 columns (company_id, realm_id) and add a composite UNIQUE constraint on each of the two referenced tables (or modify their PRIMARY KEY):

Company [id]

Realm [id]

CompanyRealm [id, realm_id=>Realm.id, company_id=>Company.id,
              UNIQUE(company_id, realm_id)]

Project [id, company_id=>Company.id,
         UNIQUE(company_id, id)]

ProjectRealm [id, project_id, company_id, realm_id,
              (company_id, project_id) => Project (company_id, id),
              (company_id, realm_id) => CompanyRealm (company_id, realm_id)]

See also several other questions, where the same (or quite similar) problem appears. The diamond pattern is obvious in all:

Best data modelling approach to handle redundant foreign keys in relational model
and the nice answer and logical models and diagrams by MDCCL.
Foreign Key Constraint over two tables
How to represent it with UNIQUE indexes and foreign keys?
Many to Many and Weak Entities
Constrains relationship


From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference):

Logical Query Processing Order:

FROM
WHERE
GROUP BY
HAVING
SELECT

Evaluate Expressions
Remove Duplicates

ORDER BY
OFFSET-FETCH/TOP

Window Functions operate in steps 5.1. and 6.

Obviously then, you cannot access the results of a Window Function in the WHERE or HAVING clauses. You can always use a CTE or a subquery.

See also:
- Logical Processing Order of the SELECT statement (SQL Server 2014 - 2017)
- Logical Query Processing: What It Is And What It Means to You (Itzik Ben Gavin - ITProToday)


Preventing users who are dbo from dropping their own databases

That is your only option.

As a side note, you may also want to check for server role db_creator = 0 as well.


SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

BCHR can be very misleading due to read-ahead:

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.[link]

You are very likely seeing the impact of read-ahead filling the buffer cache pre-emptively, which distorts the BCHR counter. Excellent and very in-depth article from Jonathan Kehayias titled Great SQL Server Debates: Buffer Cache Hit Ratio covers this.


Use Group Managed Service Account for SSRS

Try removing the managed service account like so by running the following command on your local SQL/SSRS Server:

Remove-ADComputerServiceAccount -Identity [Your Server Name] -ServiceAccount domain\SSRSProdSvc$

https://technet.microsoft.com/en-us/library/dd378979(v=ws.10).aspx

And if this hasn't been done, add the new managed account to the local server:

  On the domain controller, click Start, and then click Run. In the Open box, type dsa.msc, and then click OK to open the Active Directory Users and Computers snap-in. Confirm that the Managed Service Account container exists.
  Click Start, click All Programs, click Windows PowerShell 2.0, and then click the Windows PowerShell icon.
  Run the following command: New-ADServiceAccount [-SAMAccountName ] [-Path ].
  Associate the new MSA to the computer account by running the following command: Add-ADComputerServiceAccount [-Identity] <ADComputer> <ADServiceAccount[]>

  See Add-ADComputerServiceAccount for the complete syntax.

per Microsoft:
https://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx


Probable circular dependency, but query still runs

There's nothing wrong with your model. Au contraire - it's pretty advanced!

employment_statuses provide data in a time-lined, historical, fashion, whilst departments identifies the actual manager.

These entities are very different both in purpose and use (queries on employment_statuses will serve different scenarios than those on manager).

I have to congratulate you, as I repeatedly work with peers that just can’t abstract this kind of scenario (the majority just see the now, or, at maximum, a single then - not the natural sequence of thens leading to the as today).

A remark: you should move high/low_salary from positions to another time-lined entity.


print @T & @F

Returns 0

& is the Bitwise AND operator.

  The & bitwise operator performs a bitwise logical AND between the two
  expressions, taking each corresponding bit for both expressions. The
  bits in the result are set to 1 if and only if both bits (for the
  current bit being resolved) in the input expressions have a value of
  1; otherwise, the bit in the result is set to 0.

In your case @T & @F resolves to 1 & 0 and so returns a result of datatype BIT with value 0

When passed to the PRINT operator this bit result is implicitly cast to string and the result output to the client.

print @T and @F

Has quite a lot wrong with it.

AND

  Combines two Boolean expressions and returns TRUE when both expressions are TRUE

bit is not the same as boolean. They are not interchangeable and SQL Server won't implicitly cast bit to a boolean datatype when needed (SQL Server does not implement the SQL Boolean datatype.).

So you would need to use an expression like

@T = 'TRUE' AND @F = 'TRUE'

instead of

@T and @F

Even then your problems aren't over - PRINT doesn't accept a boolean expression anyway. You could use the expression in CASE as below.

PRINT CASE
          WHEN     (@T = 'TRUE' AND @F = 'TRUE') THEN 'True'
          WHEN NOT (@T = 'TRUE' AND @F = 'TRUE') THEN 'False'
          ELSE 'Unknown' -- SQL uses three valued logic
      END


Sql Server 2016 errror Incorrect syntax near 'EXTERNAL'

External data source type SHARD_MAP_MANAGER and RDBMS is only supported only on Azure SQL Database v12 or later. It is not supported on SQL Server 2016.

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql

-- Elastic Database query only: a shard map manager as data source
-- (only on Azure SQL Database v12 or later)
CREATE EXTERNAL DATA SOURCE data_source_name
    WITH (
        TYPE = SHARD_MAP_MANAGER,
        LOCATION = '<server_name>.database.windows.net',
        DATABASE_NAME = '\<ElasticDatabase_ShardMapManagerDb'>,
        CREDENTIAL = <ElasticDBQueryCred>,
        SHARD_MAP_NAME = '<ShardMapName>'
    )
[;]

-- Elastic Database query only: a remote database on Azure SQL Database as data source
-- (only on Azure SQL Database v12 or later)
CREATE EXTERNAL DATA SOURCE data_source_name
    WITH (
        TYPE = RDBMS,
        LOCATION = '<server_name>.database.windows.net',
        DATABASE_NAME = '<Remote_Database_Name>',
        CREDENTIAL = <SQL_Credential>
    )
[;]


query all audit entries defined with 'audit' command

Depending on the method you use for auditing, you can find that information in the below views:

DBA_OBJ_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_ENABLED_POLICIES

The content of the audit trail, also depending on configuration and version, can be found in the below views:

DBA_AUDIT_TRAIL
UNIFIED_AUDIT_TRAIL


SSIS VS17 Unsupported: This version of visual studio is unable to open the following projects dtproj

These actions solved my problem:

Install the latest version of SSDT
Open Visual Studio 2017: Go to: Tools > Extensions And Updates > Search: Microsoft Integration Services Projects > Click: Enable
Restart Visual Studio
In SSIS Project On Solution: Right Click > Reload Project


Is SQL Server Data Tools a paid product of Microsoft or a Free Product?

It is free and a part of Visual Studio Community Edition.


Find which session is holding which temporary table

I asked for something to be built in back in 2007, on Connect. This was rejected for the 2008 release, and subsequently ignored, until Connect died a few years ago. I tried to find it on the new feedback site for SQL Server, but that search is an absolute dumpster fire. The title of my request was "dmv to map temp table to session_id" - since the search can only do OR, "map temp table" returns 118 pages of results. Google seems to suggest the item didn't make the cut when they killed Connect.

In the meantime, for SQL Server 2005 and 2008, you should be able to pull this information from the default trace:

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
     o.name,
     o.OBJECT_ID,
     o.create_date,
     gt.NTUserName,
     gt.HostName,
     gt.SPID,
     gt.DatabaseName,
     gt.TEXTData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
     ON gt.ObjectID = o.OBJECT_ID
WHERE gt.DatabaseID = 2
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

Shamelessly lifted from this Jonathan Kehayias blog post.

To determine space usage you could further enhance this to join in data from views like sys.db_db_partition_stats - e.g.:

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
     o.name,
     o.OBJECT_ID,
     o.create_date,
     gt.NTUserName,
     gt.HostName,
     gt.SPID,
     gt.DatabaseName,
     gt.TEXTData,
     row_count = x.rc,
     used_page_count = x.upc
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
     ON gt.ObjectID = o.OBJECT_ID
INNER JOIN
(
 SELECT [object_id], SUM(row_count), SUM(used_page_count)
   FROM tempdb.sys.dm_db_partition_stats
   WHERE index_id IN (0,1)
   GROUP BY [object_id]
) AS x(id, rc, upc)
ON x.id = o.[object_id]
WHERE gt.DatabaseID = 2
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

The problem here is trying to correlate a table name by query text; this just isn't practical, since most of the time, the user isn't still executing a query against that table (never mind still running the one that created / populated it).

However, and this is for other readers (or for you when you upgrade), the default trace in 2012+ no longer tracks temp table object creation, if the #temp table is a heap. Not sure if that is a coincidence or directly related to the fact that starting in 2012 all temp tables now have a negative object_id. You could of course move to Extended Events to help you collect and track this information, but that is possibly a lot of manual work (and I've only verified that this is no longer tracked in trace - you may not be able to pick it up in Extended Events either). The default trace will pick up #temp tables created with a PK or other constraint, or with constraints or indexes added after the creation event, but then you'll have to loosen up the time-based restrictions above (an index can be created much later than 100ms after creation).

Some other answers on this site that may be useful:

How to identify which query is filling up the tempdb transaction log?
Issues with TempDB mdf file ever increasing
Find transactions that are filling up the version store

I've also blogged about this, with a custom Extended Events session to track this information in SQL Server 2012 and up:

Dude, who owns that #temp table?

And Paul White has blogged about reading pages directly (not exactly for the faint of heart, nor easy to automate in any way):

Viewing Another Session’s Temporary Table


Which is suitable db2 version on windows server 2012 server?

db2 v11.1 was recently released, it works great on a 2012 server.


InfluxDB:  3% cpu; MySQL: 1%.  Do you have something else to use the CPU for?

Can you get InfluxDB to perform a MySQL LOAD DATA INFILE?  That will do all that work in a single SQL statement, and probably do it faster than 6 minutes elapsed.


Adding Foreign Keys to Composite Primary Key, referencing 2 tables

I had UNSIGNED on the referencing table but not on the referenced. That caused the issue it seems.


MAX Query returns set results when using dbmail

You have at least one from eiwdata..eiwtcontentreceived (nolock) too many. You are fetching the max value for each row in eiwdata..eiwtcontentreceived.

SET @tableHTML16 =
    N'
BDI Feeds Last Received Batch
' +
    N'
' +                          -- close
 here
    N'
Cerner	CPACS	DSCH123
' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

/*
             This table reference should be removed
             from eiwdata..eiwtcontentreceived (nolock)
*/

        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;


You have three options.

Use the gui of SQL Server Management Studio.
Use BCP to export your data in a loop (see here)
Use Powershell and script your export in a loop for example.

Here an example in PowerShell:

 $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'list[_]%'" -database "ETTEDB_list" -serverinstance "YOURSERVER"
 foreach ($Table in $Tables)
 {
     $TableName = $Table["name"]
     write-host -ForegroundColor White "Creating File $TableName.csv"
     invoke-sqlcmd -query "SELECT * FROM $TableName" -database "ETTEDB_list" -serverinstance "YOURSERVER" |export-csv -path c:\scripts\$TableName.csv
 }

You'll need to adjust "YOURSERVER" to the right value and maybe change the path property to the correct folder. Hopefully this will help.


Is this SQL schema normalized according to 3NF?

Assuming a yellow background means that column is part of the primary key, I think Game could use some more attention.  First up, homeScore and awayScore shouldn't be part of any key.  It is acceptable (though unlikely) that every singe row have the same values for these two columns.  Second, id_WinningTeam is can be calculated from the home and away teams and their scores.  It could be handy to retain it for performance but I'd suggest it be taken out of any key.

I can see some candidate keys which could provide uniqueness, depending on some other rules.  If you limit Venue to single-concurrent-use places, like a football field where only one game can take place at a time, id_Venue and datetime becomes a candidate key.  If you are including places like squash clubs where many games can be running simultaneously then this won't work.  Similarly id_HomeTeam and datetime will be unique and could be a key.

PlayerTeams may be redundant to PlayerGames, depending on what questions you will be asking of this system.  If you keep it adding joining and leaving dates could prove useful.

On a point of style - you use both singular (Game) and plural (PlayerGames) names.  This is the stuff of religions wars but I'd suggest you pick one and stick with it.


How does paging work with ROW_NUMBER in SQL Server?

An alternative to test might be:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:

http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

;WITH x AS
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

I also blogged about this in more detail here:

SQL Server v.Next (Denali) : Using the OFFSET clause (paging)
Pagination with OFFSET / FETCH : A better way


SQL Server Deferred Stored Procedure Execution

How often is this stored procedure called?

One way to do this would be to use a table to track the times/parameters you want to execute. Create a table with a datetime to execute the stored procedure at, and an active flag.

Every time SP_DoSomething is executed, it inserts values into this table. Run a scheduled job every few minutes to check the table for Datetime values less than GETDATE(), run the stored procedure and change the active flags.

To store the parameters, you would need columns in your table, one for each parameter that you'll be using. Instead of executing SP_DoSomethingElse, place those values into a table.


How to test MySQL master-slave replication latency and get the exact time of slave library latency (accurate to milliseconds)

INSERT INTO latency (master, slave) VALUES
    (NOW(3),      -- same time on Master and Slave
     SYSDATE(3))   -- time at which this query was run on each

Then diff them to see the latency.

Caveats:

Running with --sysdate-is-now would prevent the trick from working.  Test via SELECT NOW(6), SYSDATE(6);.
I have not tested this.
SYSDATE is "unsafe for replication" (but we are taking advantage of that!); however, the binlog_format may impact it.


SELECT country,
       COUNT(state) total,
       SUM(state='xxx') xxx,
       SUM(state='yyy') yyy,
       SUM(state='zzz') zzz
FROM table
GROUP BY country


Creating Event that execute at end of each month

You should never have to drop and create new events automatically.

You should just make the event repeatable:

Every minute, check the date and time
If the hour is 23 and minute is 50 on the last day of the month, call IncidentsSummaryRecorder();

Here is that code

DELIMITER $$

CREATE EVENT ISRSP
ON SCHEDULE
    EVERY 1 MINUTE
    STARTS '2014-10-31 23:50:00'
    ON COMPLETION PRESERVE
DO
BEGIN
    DECLARE rightnow DATETIME;
    DECLARE hh,mm TINYINT;

    SET rightnow = NOW();
    SET hh = HOUR(rightnow);
    SET mm = MINUTE(rightnow);

    IF DATE(rightnow) = LAST_DAY(DATE(rightnow)) THEN
        IF hh = 23 THEN
            IF mm = 50 THEN
                CALL IncidentsSummaryRecorder();
            END IF;
        END IF;
    END IF;

END $$

DELIMITER ;

Give it a Try !!!


How to deal with seldom used big database and postgresql?

depending on your usage, you could try to partition the data to reduce the scanned data (at 400gb even your indexes are large and heavy to read...)
I think it's your best option as working with 400gb tables is heavy even on a very optimized queries...
You could also pre-warm the cache with the index data , by issuing some of your queries on frequent gps points or by using something like pg_prewarm


SSIS - How do I execute a single OLE DB Command for mutiple paths?

I don't think your reason justifies using this solution (sisyphean labor is part of a programmer's life...)
It is not efficient and i would not recommend it in your situation.
If, however, your aim was to perform one logical operation for several streams, and your streams have similiar data structure... , there is a way to do it.

Your best option is to use the Union All Data Flow Transformation component.
You can map the overlapping input columns to the same output columns, or map them to different columns for easier split in the next step if necessary.
You can also add a derived column for each of the inputs befor the union, which will hold an input identifier, again for the split to come.

There is another way to do it, using a Script Component (one for each path) instead of the OLE DB Command and passing a variable to it.
The variable holds the command to execute, and is initialized in the control flow once.
I find it too cumbersome, maybe because I'm just a DBA (not programmer) and lack scripting knowledge...


Do conditional 1:1 relationships improve normalization?

Normal forms based on functional dependencies (e.g. BCNF) are defined for relation schemas and relations consisting of values without nulls. As soon as you permit nulls you already have something that is not a normalized relational database.

In your first scenario, if your ADMIN table has (non-nullable) attributes, X,Y,Z, then the dependency {UserID}->{X,Y,Z} is enforced and will be satisfied for every admin user. If you combine the tables and make X,Y,Z nullable then those attributes may be null even for admin users. The dependency {UserID}->{X,Y,Z} is therefore no longer automatically enforced if you combine the tables. Assuming that dependency is important to you then you would have to add extra integrity constraints to validate that each of those attributes has a value for admin users and a null for non-admins. The advantages of BCNF (i.e. having both ADMIN and USER tables) are that the intended business rule {UserID}->{X,Y,Z} is declared as part of your table design, is obvious to anyone inspecting the schema and is guaranteed simply by the presence of keys and non-nullable attributes in your tables.


Which of MultiStatements is "out of sync"

Usually that refers to Stored routines with PREPARE + EXECUTE + DEALLOCATE_PREPARE.  Have you done all three of those, in that order?  Optionally, EXECUTE can be repeated.

If you are referring to stringing together statements with ;, don't do that; it is a security hole waiting for some hacker to jump into.

What client language are you using?

If you need more help, let's see your code.