Why did a query change its execution plan and how to anticipate the change?

The Optimizer takes the parsed representation of SQL statement and Statistics to generate final execution plan with the lowest cost. During this process the Optimizer generates multiple plans and compares them. Execution plans may change as the Optimizer inputs(Parsed SQL Statement and Statistics) get changed.

  Why Execution Plans Change

  Execution plans can and do change as the underlying optimizer inputs change.
  EXPLAIN PLAN output shows how the database would run the SQL statement when
  the statement was explained. This plan can differ from the actual execution plan a SQL
  statement uses because of differences in the execution environment and explain plan
  environment.
  The Oracle Documentation clearly states that Execution plans can differ when we change Schemas and have changes in Costs.

If we run the same SQL statement in different database under different Schemas then also the resulting plan can be different. Even the schema and database is same but the Cost of the execution plan is different then also the optimizer can choose different execution plans. Bind variables, size of the data and its statistics, optimizer's parameters may influence the Cost.

Guessing why the Execution plan has been changed is difficult because we don't have the metadata(which are dynamic) of your database on which we have to query for further investigation and its time consuming task.

You can use SQL Tuning Adviser, SQLTXPLAN, SQL Trace etc to make it easier to find the elements affecting the execution plan.

For details:
Oracle Database SQL Tuning Guide


MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?

In their current form: the behavior and performance of t1 and t2 should be identical,

and:
IF a string column is added that does not specify either CHARSET or COLLATE clauses: the behavior and performance will be different. This is due to:

new columns in t2 will be CHARSET=utf8 and COLLATE=utf8_bin (because they were specified as defaults for the table), but
new columns in t1 will be CHARSET=utf8 (because that was specified as default for the table), but will also be COLLATE=utf8_general_ci (because this should be the default for the utf8 character set)

Now, I created the two tables as you have them in the question, and that resulted in the following:

SHOW CREATE TABLE t1;

CREATE TABLE `t1` (
  `foo` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `bar` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `baz` char(80) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and:

SHOW CREATE TABLE t2;

CREATE TABLE `t2` (
  `foo` char(60) COLLATE utf8_bin DEFAULT NULL,
  `bar` char(64) COLLATE utf8_bin DEFAULT NULL,
  `baz` char(80) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm not sure why CHARACTER SET became explicitly stated in the stored version of the CREATE TABLE t1 statement, but I think I know why COLLATE did for CREATE TABLE t2: once you submit the statement, that's what it gets translated into as it's being processed. Why just the COLLATE statement and not also CHARACTER SET? That is most likely due to character sets working with 1 or more collations, so specifying the character set does not imply the collation. On the other hand, a collation works with only 1 character set, so specifying the collation does imply the character set. Yes, character sets have default collations, but defaults can change over time or between contexts. Yes, there are defaults explicitly declared for the table, but what if a) the table defaults aren't respected, and/or b) whatever is reading the DDL wants to know everything about a column as it parses the column, as opposed to needing to read the full table DDL first and potentially go back to apply defaults. Adding the COLLATE clauses makes the operation very clear and unambiguous.

Also, the documentation for CREATE TABLE Statement Retention states:

  The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. The information is retained so that if you change storage engines, collations or other settings using an ALTER TABLE statement, the original table options specified are retained. This enables you to change between InnoDB and MyISAM table types even though the row formats supported by the two engines are different.

I tested this on db<>fiddle against MariaDB 10.2 and 10.3. and MySQL 5.6, 5.7, and 8.0, and the behavior was the same across all of them.


How to restrict MySQL user to not import and export database

I believe phpMyAdmin uses plain SELECTs and INSERTs to import and export the database.

I don't think you will be able to prevent import/export.  I don't remember PhpMyAdmin having options to disable those features.


If you move these additional costs to ShoeInventory then they will be associated with your ShoeInventory entity. This entity also has a relationship with ShoeSize. If I understand your schema (and I might not, so correct me if I'm wrong), the implication of this is that different sizes for the same shoe type could potentially have different sub-costs. This might give you extra flexibility, or it might make things more complicated. If you want to separate this data from shoes and don't want it attached to any other table (in case it doesn't make sense), you could create a shoeCost table:

shoeCost
---------
  id (PK)
  shoe_id (FK to shoe.shoeId)
  leather_cost
  manufacturing_cost
  inventory_cost
  total_cost (this could be a computed column of
              leather_cost + manufacturing_cost + inventory_cost,
              if it makes sense to do that)


SSMS 2017: Open SQL Files in existing SSMS app with raised admin permissions?

In SSMS use Ctrl+O to open the file instead of double-clicking in explorer.


Slow query compilation, lots of reads during compilation phase

Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.

That's correct, forced paramterization doesn't work here because the query has "already been parameterized on the client-side application" (source).  In other words, the problem is that there are two actual parameters, so the embedded date and int parameters can't be forced.

On to what's going on with the reads / CPU...

Auto Created / Updated Stats

As far as the reads being shown in profiler, those do include statistics updates and creation.  That would explain the problem if it only happened occasionally.  Since you mentioned these long compile times and high reads occur on each execution, it doesn't seem like that's the culprit (unless this application is doing something really unusual, like aggressively deleting auto-stats).

Version Store Reads

There are other kinds of reads that are not reported in SET STATISTICS IO ON, which I talked about on my blog.  These include version store reads, so if you have SNAPSHOT or READ COMMITTED SNAPSHOT enabled on this database, that could explain some of the CPU / read discrepancy.  Especially if you have long-running transactions.

Additional Troubleshooting

It might be helpful if you could share an "Actual Execution Plan" for the query in question while reproducing the issue.  There could be clues in addition to the information you've already provided.

Plan Forcing

As far as I can tell, there isn't any way to prevent a plan from compiling each time one of these queries comes in (as you noted, plan guides won't work, and neither will Query Store plan forcing).

Ad-Hoc Workloads

As a side note, since you have these embedded parameters, your workload could probably benefit quite a bit from the optimize for ad hoc workloads server-level configuration option.  It will keep your plan cache from being bloated, which might also be contributing to CPU issues (as SQL Server has to evict items from the plan cache over time due to all the "new" plans coming in for each query).


Is there any AWS RDS Utility to Manage Master Slave mysql db with read write splitting?

AWS does not have a service that provides this.

Transparent, intelligent read/write splitting is a Hard Problem™ because there are so many details that can go wrong, such as queries that use user-defined variables, which aren't deterministic unless the state of the variables is maintained across multiple connections... inconsistent reads and read-after-write due to replication lag... and many others.

The only solutions for now are third-party products like Maxscale and ProxySQL, unless you are using a connector library or ORM that manages such things for you (assuming such things exist).


Updating Database Email Profile for all notifications

I think what you're looking for is msdb.dbo.sysjobs and the notify_email_operator_id field specifically. If you can get the ID of the profile after creation, then you likely just need to update the table sysjobs references in a single UPDATE statement, on each instance you run your script on.


Please select "Open With..." and then choose from the list "Microsoft SQL Server DAC Package File Unpack Wizard". It should be also default association for this file type.

If you don't have this option, you can also rename this file to .zip and use any unzip tool.


first you must identify the spid

look up

sp_who2 'active' ---> look for the spesific session_id

  SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60 SELECT
  command,percent_complete FROM sys.dm_exec_requests WHERE session_id =
  60


Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?

The problem may stem from the way MySQL Query Optimizer makes choices as well as the way indexes are internally represented in InnoDB.
First look at the Cardinality of the Indexes. A primary key's cardinality must always be the actual row count of the InnoDB table. Now, look at the cardinality of the field1. If the index iif is less than that of the primary key, the MySQL Query Optimizer will choose the secondary index. To verify that the Cardinaliry of field1 is lower, run these queries:
SELECT COUNT(DISTINCT field1) FROM largetable;
SELECT field1,COUNT(1) fieldcount FROM largetable
GROUP BY field1 WITH ROLLUP;

Now, look at the internal representation of the indexes. A secondary index will contain two items: 1) the column value(s) being indexed, 2) the rowid from the Clustered Index (a.k.a. the gen_clust_index). Every time a column is referenced in the secondary index, a lookup of the actual row is done as well. Picture it : Two Keys Looks Up for every row in InnoDB.
Putting these two issues together, you find that a secondary index with a lower cardinality than the primary key, will still lookup the actual row using the primary key. This explains why a secondary index is chosen over the primary key and has takes twice as long or even longer to query.
Some people would disagree with this line of reasoning because I answered a question similar to this in StackOverflow (Nov 15, 2011). Although my answer was accepted, it has mixed upvotes and downvotes because some do not view the MySQL Query Optimizer and InnoDB index structure the same way.
If anyone from Percona sees this question and my answer and sees any flaw in my reasoning, please correct me so all can learn.
UPDATE 2012-04-23 12:56 EDT
The InnoDB storage engine does deep dives into the BTREE indexes to take educated guesses at the cardinality. Try setting innodb_stats_on_metadata off
[mysqld]
innodb_stats_on_metadata = 0

According to the documentation, when disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.


Postgres logging_collector probably blocks auto_explain output

Works for me.

Could it be that the other long queries were run over existing connections (perhaps with a connection pooler) for which the session_preload_libraries has not taken effect?


Issues installing MySQL server on Ubuntu 13.04

Assuming this isn't an Internet connection issue ("Connection failed" is a needlessly obscure message), then this looks like a problem with the particular Ubuntu mirrors your system is trying to use, rather than something specific to MySQL.

The answer posted on What to try when apt-get install xxx Results in Connection Failed seems like it should get you pointed in the correct direction to resolve this.

It's also possible that sudo apt-get update might fix your problem now, even if it didn't fix your problem before, since some time has passed.  I'm speculating, but it could be that if the information fetched by the update operation causes your system behave differently when it's trying to fetch the files you're needing to install, that might also resolve the problem.


Can 'clustering' and 'alwayson failover clustering' be used interchangeably?

"Clustering" is definitely used here to refer to SQL Server's Failover Cluster Instances (FCIs). However, the terms are not interchangeable.
Both FCIs and Availability Groups (AGs) are built on top of Windows Server Failover Clusters (WSFCs). I've seen FCIs, AGs, and WDFCs referred to as "Clusters" or "Clustering."  The correctness of referring to any of these simply as "Clustering" is debatable.
What isn't debatable is that referring to any of those technologies simply as "Clustering" is ambiguous and can be confusing without proper context. Using the more verbose terminology, or even in abbreviations will always be unambiguous.


Can not drop table due to replication error, though table is not being published

TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions and a longer explanation.

Replication uses database triggers for certain things

When a database is published for transactional replication in SQL Server, as part of the set-up process several database-level triggers are created. Normally, they are:

tr_MStran_alterschemaonly
tr_MStran_altertable
tr_MStran_altertrigger
tr_MStran_alterview

You can find them by expanding your database in object explorer, then looking in "Programmability" and then "Database Triggers." They do pretty much what they say. If you were to look at the code in tr_MStran_altertable you'd see that each time this trigger fires for an ALTER TABLE statement, SQL Server uses the EventData() function to capture what's being changed, then calls another replication stored procedure that will actually capture the activity and send the related DDL transaction to the distribution database. This is different than the log reader agent that looks for and captures data changes.

These triggers are created each time you publish a database for replication, and are removed as part of removing replication as well.

New DROP TABLE support in SQL Server 2014 SP2 and SQL Server 2016 SP1

In SQL Server 2014 SP2 and SQL Server 2016 SP1, Microsoft introduced a change for SQL Server replication: Support for DROP TABLE (https://support.microsoft.com/en-us/help/3170123). Before, you couldn't drop a table that was marked for replication. This new feature, which is not enabled by default, means that the DROP TABLE statement will not throw an error anymore. To facilitate this change, a new database level trigger was added: tr_MStran_droptable. This is the trigger that fires for each drop table action, and it uses the event data to determine which table was being dropped, and if the option to support the transaction even though the table is marked for replication is enabled.

But what about "older" versions of SQL Server?

And that's where the problem comes in: this trigger only gets created by replication on versions that support it... and only removed by replication on versions that support it. So even if you decide to nuke replication with something like sp_removedbreplication this trigger won't be dropped, and continue to fire each time you try and drop a table, even if the table you're trying to drop isn't marked for replication. This can happen if:

You have a database published for replication on a version supporting this functionality (either 2016 SP1 or 2014 SP2)
You backup and restore this database to a version of SQL Server that does NOT support this functionality (like 2016 RTM or 2014 SP1)
You publish the restored database for replication.

You can test this yourself: if you disable replication and re-enable it, you'll notice the create times on the database triggers will coincide with when you re-enabled publishing, but the tr_MStran_droptable trigger remains.

Fixing the problem

To solve this problem, you can either:

Upgrade the affected instance to a patch level that supports this trigger (2014 SP2 or 2016 SP1).
Manually delete the trigger, since it isn't supported.


Oracle 11, incremental backup goes wrong

You are doing a differential incremental backup rather than cumulative, but the syntax for recover of copy tag 'sometag' indicates that you plan to incrementally update an image file backup.  Phil was on the right tack with the missing RECOVER COPY OF DATABASE WITH TAG 'sometag'.  Since you are not running this each incremental must be able to recover the un-updated tag 'sometag' causing it to grow each day as though CUMULATIVE were specified.

See Backup Sets and Image Copies in the Concepts Guide and see  Incrementally Updating Backups in the Backup and Recovery User's Guide (11.2).  If your intention was to use incrementally updated image copies you will want something more like this:

  RUN
  {
    RECOVER COPY OF DATABASE
      WITH TAG 'incr_update';
    BACKUP
      INCREMENTAL LEVEL 1
      FOR RECOVER OF COPY WITH TAG 'incr_update'
      DATABASE;
  }

If you didn't intend to do this, then simply remove the for recover... syntax.


Why is INSERT INTO ... SELECT not copying all rows?

It turns out the culprit was our old friend, ARITHABORT!

In the view documents, poNumber is actually the output of a scalar-function call. One of the inputs was a BIGINT that I was trying to pass to a INT parameter, so an overflow error was being raised for some rows. Because the linked server connection from SERVER-00 to SERVER-01 was leaving ARITHABORT unset (and thus OFF), the error was being swallowed so I wasn't seeing it when running the query from that side.

Once I corrected the problem with the function (expanded the parameter to be a BIGINT), all the rows started copying over correctly!

For reference, I confirmed the linked server connection's options by running this query on SERVER-00:

SELECT *
FROM OPENQUERY([SERVER-01], '
    SELECT
        CASE WHEN SESSIONPROPERTY(''ARITHABORT'') = 1
        THEN ''ON''
        ELSE ''OFF''
        END AS [ARITHABORT]
')


Can application_name be accessed from a trigger?

You can use the function current_setting()[1], eg:

sebastian=# select current_setting('application_name');
 current_setting
-----------------
 psql
(1 row)

Testing a new value:

sebastian=# set application_name to db_overflow;
SET
sebastian=# select current_setting('application_name');
 current_setting
-----------------
 db_overflow
(1 row)

References:

https://www.postgresql.org/docs/current/static/functions-admin.html


How can I get nodetool without Cassandra

The easiest (non-invasive) way is probably to download the tarball installation (you'll need to select either a Mac or Linux-based OS for it to allow you to download the tarball).  Based-on your mention of disabling the service, I'm going to guess that you want to accomplish this on Windows.  If that's not the case, please indicate so in the comments.

Un-tar dsc-cassandra-2.0.8-bin.tar.gz to the location you want to run Nodetool out of.  ex:

$ cd /tools
$ tar -zxvf dsc-cassandra-2.0.8-bin.tar.gz

Note: You may have a different application you use for tarballs.  I ran this from a Cygwin terminal.

Find the location of your JRE/JDK (not the bin directory) and set that as your "JAVA_HOME" (System) environment variable.  When you have it set properly, you should be able to query it via CMD:

>echo %JAVA_HOME%
C:\Program Files (x86)\Java\jre7

Once you have JAVA_HOME set, it should work from either CMD or Powershell:

C:\tools\dsc-cassandra-2.0.8\bin>nodetool -h 192.168.1.85 status
Starting NodeTool
Note: Ownership information does not include topology; for complete information, specify a keyspace
Datacenter: datacenter1
========================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address       Load       Tokens  Owns    Host ID                               Rack
UN  192.168.1.85  506.29 MB  256     100.0%  cd39f0fe-ed67-40cf-b6bd-504cedabf497  rack1

This way, you can run nodetool without messing with an installer or services.


PostgreSQL doesn't have an in-core natural language identifier.  I'm not aware of direct PostgreSQL extensions to do it either.  But there are lot of language identifiers out there, and it shouldn't be too hard to map one of them into PostgreSQL.

I would do that using pl/perl or pl/perlu to wrap an existing Perl module, like in this answer.  But pl/pythonu would be another good choice, but one I have less personal experience with.

You will probably want to store the determined language in a separate database column, rather than computing it on the fly all the time.  Doing it that way also means you can do the detection in the app, rather than in the database.


Same queries, different servers, different exec plans, stats are up to date

In the slow plan, the plan sub-tree that contains all the remote queries is executed 2928 times.  It would have been 3632 times (the number of rows on the outer input to the nested loops join that has the remote query sub-tree on its inner side), but the Distinct Sort is able to rewind (reuse) the prior iteration's results on a number of occasions.

In the fast plan, the join is a hash join rather than nested loops, so the problematic sub-tree is only executed once.  As usual, the cause is likely the small differences in cardinality estimation (visible in the plan as estimated row counts) and derived histograms and distribution statistics (not so visible).  This variation is probably due to slightly different samples taken to build statistics, or statistics built at different times between dev and test.

To quickly validate this, you could add OPTION (HASH JOIN) to the query.  There is a small risk that the optimizer will complain that it cannot produce a plan with that hint.  I haven't analysed the plan deeply enough to predict that, and this suggestion is just for demonstration purposes.  It is not my suggested solution.

A better fix is to break the query into sections.  Refactor the remote query sub-tree processing into a separate query, and store the 6737 rows it produces in a temporary table.  You will probably want to use a #temporary table rather than a table variable, since you will get auto-generated statistics, and you will have a wider range of indexing possibilities, should that prove to be useful.

Queries with many joins and remote queries have a very large number of possible arrangements.  By breaking the query up sensibly, and providing statistics (and perhaps indexes) you are giving the optimizer better information and a smaller search space to explore.  Your chances of a good plan go up commensurately.


Updating mongo sharded cluster without down time for mongod

All details are here: http://docs.mongodb.org/manual/release-notes/2.6-upgrade/

Answer 1: All metadata will get updated. I suggest to execute the mongos --upgrade using an extra mongos

Answer 2: You don't have any downtime from the config servers since mongos have the require cached information and balancer is disable

Answer 3: There is no other way. You upgrade the secondaries and then promote one of them to PRIMARY and upgrade the old-PRIMARY. Downtime is minimal (1-2 second my case) if replica set are well-sync


Pglogical - multiple database tables to single table

Never mind, Its possible, I have tested with this. But we have to make sure that if you have any PK, Unique key it should unique across your source databases(all 4).Or on the subscription database create the table without the constraints.


mysqldump with --no-tablespaces on InnoDB with index?

"Tablespaces" are for clumping tables into a single .ibd.  The mysqldump options relating to that control whether to output the DDL statements such as CREATE TABLESPACE.
mysqldump does not dump the contents of indexes, regardless of Engine.  It does dump the definitions of the indexes.  When reloading, the indexes will be recreated.  Yes, MyISAM has a separate file for indexes while InnoDB includes the indexes in the same "tablespace" file; but the locations of indexes is an implementation detail that does not matter when dumping and reloading tables.
If you have not explicitly created any "tablespaces", ignore options relating to them.  (Very few people use tablespaces.)


BAK files not created on Secondary or Primary

Our Senior DBA ended up opening a Microsoft ticket for this and so far the only solution that does not involve selecting ignore priority or copy-only is to change the Availability Groups backup settings to backup on Primary instead of Secondary. We have MANY databases and many of them are quite large and accessed throughout the day; we did not initially want to run on Primary due to the server load this could create.
So for anyone who is going through a similar situation, here are the tests I did and why we came to this conclusion. Again choosing Primary Backup for Availability Group always-on settings is not a fix to why Secondary option does not work the way it should, but it is the best solution if you want latest full database backup.

Prefer Secondary for AG backups
Created 2 plans one for DB backups (bak) and one for Trans Log (trn)

Note: When I first started testing I had one plan with both DB backup (bak) and transaction log backup (trn) but would fail and stop when bak were not created on secondary and no trn created and wanted to test each. separated bak and trn

Added both AG and nonAG Dbs to the plans

1. Test 1

NO options selected

Trans Log plans works as it should, Primary only had trn for nonAG DBs and Sec had trn for all Dbs, no errors on either

Bak files were created on both Pri and Sec but only for nonAG DBs

No errors from primary, not expecting there to be on primary as it is not set as AG backup.

Sec is the only one that triggered an error...

  "This BACKUP or RESTORE command is not supported on a database mirror
  or secondary replica. BACKUP DATABASE is terminating abnormally."

2. Test 2

Selected ignore replica on DB backups only.

Bak files were created on PRIMARY

NO bak files on Secondary

Secondary triggered same error above

3. Test 3

Unchecked ignore Priority and checked copy-only

Secondary created both AG and nonAG bak files

Primary only had nonAG bak files

4. Test 4 (NOT AN OPTION AT ALL)

Both Ignore and copy-only checked

ALL DB backed up on both Pri and Sec


How to get notices to the output stream?

I'm afraid that you won't like this answer, but currently it seems impossible.
From the psql documentation:

  Saves future query results to the file filename or pipes future
  results into a separate Unix shell to execute command. If no arguments
  are specified, the query output will be reset to the standard output.

  "Query results" includes all tables, command responses, and notices
  obtained from the database server, as well as output of various
  backslash commands that query the database (such as \d), but not error
  messages.

And as you noticed, there is no way redirecting error messages when using psql interactively.

(I've been playing around with all kinds of redirection from \o to no avail.  It looks like that the query output channel is different from the one getting error messages - and even errors from the server and raised in your procedures go different ways.

test=# SHOW client_min_messages;
 client_min_messages
---------------------
 notice

-- added the above to show it's not a config problem

CREATE FUNCTION raise_test() RETURNS integer AS
$body$
BEGIN
    RAISE NOTICE 'notice';
    RETURN 1;
END;
$body$
LANGUAGE plpgsql;

test=# \o | cat > out.sql
test=# SELECT raise_test(); -- you could put this in a file and call \i your_file, it's just the same
NOTICE:  notice

test=# \o | cat > out.sql 2>&1
test=# SELECT raise_test();
NOTICE:  notice

out.sql contains

 raise_test
------------
          1
(1 row)

in both cases.
This is why I am clueless about what channel/file descriptor is used for outputting the messages raised from a procedure.)
)

(There is a thread on PostgreSQL-hackers that may shed some light on this issue: http://postgresql.1045698.n5.nabble.com/psql-output-locations-td5068313.html )

What one could possibly do is starting psql like

psql test >/tmp/psql.out 2>&1

and this will redirect all output to the specified file.  The only problem with this that you don't even have a prompt, and lose the commandline editing capabilities.


Masters database not replicating on slave after restarting client

You need to rebuild the slave from a copy of the master. So take a full dump of the master:

mysqldump -uroot -p --single-transaction --master-data=2 > fulldump.sql

Restore the dump file on the slave server, reset slave and then execute CHANGE MASTER again. Make sure to remove replicate-do-db or any replication filters on slave my.cnf.


Database Design - Custom Attributes / Columns

A small JSON blob on Items to cover the wide range of descriptors of a item might be prudent. The rest of Orders / Clients tables should be fairly structured and not need any attributes.

Considering attribute tables as a primary design will get you into a mess when queries 3. EAV Tables — Entity-Attribute-Value Tables. A sharded Attribute table between a variety of other tables will make this even worse.


I edited the Query to the following:

EXPLAIN UPDATE jobcontrol.actual_values a
    JOIN jobcontrol.processing p ON a.para_id = p.para_id
    SET a.value = (select p.value from jobcontrol.processing p WHERE a.para_id = p.para_id ORDER BY t_ns DESC LIMIT 1)

The JOIN ON makes the recordset smaller (DB checks only the parameters which are also in processing, not every single one. The EXPLAIN however indicates that we at least use an index now)
In the SELECT : Replacing the MAX() with a ORDER BY ... DESC LIMIT 1 should boost the performance a bit, especially for big tables. If MAX() uses a filesort (like ORDER BY ... DESC does) nothing should change.

+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                       |
+---+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
|  1 | PRIMARY            | p     | index  | PRIMARY       | PRIMARY | 12      | NULL                 |   36 | Using index                 |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY       | PRIMARY | 4       | jobcontrol.p.para_id |    1 | NULL                        |
|  2 | DEPENDENT SUBQUERY | p     | ref    | PRIMARY       | PRIMARY | 4       | jobcontrol.a.para_id |    2 | Using where; Using filesort |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+


It really is to be the number of physical disks, in the old fashioned style, not using a SAN. The best article about this I found here http://blog.bensmann.com/testing-oracle-io. Most others blogs/docs I found were more like copies from manuals than original contents that explains something new. When using this on a SAN, the cache is going to play a big role, as is the inner working of those beasts. For example, they write in a buffer and give an acknowledgement to the rdbms that the write is complete, even when the data did not even come close to the intended disk. When continuously other blocks are written, that cache could be choked and then we get a better view of the real io performance. Reading and updating blocks from a regular sized datafile won't be sufficient to reach that.


You should be able to re-use the same variable that lets you pick your columns, like this:

    create procedure dynamic_group_by (@params nvarchar(max))

    as

    declare @sql nvarchar(max)

    set @sql = 'select ' + @params + ' from profiles p
    where email like ''%@%''
    group by ' + @params

    print sql

    exec sp_executesql @sql


weight of replication on SQL Server 2008 R2 instance

I assume replication is already running here. My general go to is to use the sp_whoisactive script. I will have the procedure run every few minutes and insert into a history table. After a few days you'll get an idea of what's running on your server. Ideally, I'd run that script for a few days with replication off and then turn it back on again to see the impact.

The table collects information based on your DMV's which is where I look for performance tuning. You need to get an idea though of what's normal or what regularly runs in your environment. If a query is using a lot of CPU, how do you know if that's the issue? Has it always used a lot of CPU or has something changed?

Paul Randal has a great article that talks about using DMV's to tune and it can help you here as well.

Keep in mind replication is using the Transaction logs here, so CDC or CDT can have a huge impact pending configuration.

There are a few warning signs to your DB if it's under pressure. Is the page life expectancy frequently dropping? Do you see your Buffer Cache Ratio dropping? What do your IO reads and writes look like during high transaction times? Do you have latency alerts configured on replication?


Find old linked servers on a restored copy of master/msdb db

The system catalogs (SYS objects) are stored in the resource DB. The resource DB is usually located at: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, i.e. the same location of the master DB. Yes, this is like the fifth system database.

Check this link on: Moving the master and Resource Databases to move your master and resource DB.


How to insert into table multiple geometry rows from function JSON param?

You can unnest the array values using json_array_elements function and ->> operator:

create table t (data json);

insert into t
values ('{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json);

select points->>'lat' as lat,
       points->>'lng' as lng,
       points->>'info' as info
from
     (select json_array_elements(data->'points') as points
      from t) p;

lat      | lng       | info
:------- | :-------- | :---
45.50884 | -73.58781 | x1;
41.56355 | -81.57327 | x2;
41.52335 | -81.57423 | x3;

db<>fiddle here

Now change your function by:

CREATE OR REPLACE FUNCTION add_multiple_geom (multiple_geom json)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point((points->>'lat')::text::float8,
                                 (points->>'lng')::text::float8),
                                  4326),
                        (points->>'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom ->'points') AS points) p;

    RETURN success;
END;

$BODY$;

And try to use it:

DO $$
DECLARE data JSON;
BEGIN
    data := '{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json;

    PERFORM add_multiple_geom(data);
END $$;

SELECT * FROM Points;

id | info | my_point
-: | :--- | :-------------------------------------------------
 1 | x1   | 0101000020E6100000C5724BAB21C14640C190D5AD9E6552C0
 2 | x2   | 0101000020E610000095D4096822C84440C971A774B06454C0
 3 | x3   | 0101000020E6100000492EFF21FDC2444074982F2FC06454C0

db<>fiddle here


Wich is the most efficient/easy way for end-user to get data from Analysis database?

You can put your cube formula in a separate cell and reference the cell where users type in the name they want.  For example, if you have users put the branch name in cell B2, you can put this formula in another cell.

=cubemember("Connection","[DimBranch].[Name].[All].[" & B2 & "])"


Restore a large number of transaction logs

I would suggest to use Restore Gene : Automating SQL Server Database Restores - written by Paul Brewer

  The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally includes override options for name of the target database and WITH MOVE overrides for data, log file locations, which might help recover lost data by restoring a temporary version of a database to the same instance.

It has both TSQL and PowerShell versions for automation.


How to copy non clustered indexes in transactional replication

This can be done.

But as far as i know, it will break the replication.You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have non-clustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them.

You can enable the option where you can replicate the non clustered indexes as described here.

As mentioned:

  Changing the article options will effectively create indexes on all
  subscribers with only a few clicks. Also, if a new snapshot is taken
  for the publication and applied to the subscribers, any indexes
  created directly on those subscribers will be removed.  If all indexes
  are not needed, alternatively, you can create selective indexes
  directly on a subscriber. However, it reduces administrative overhead
  to change the option directly in the replication settings for the
  article.


You can analyze the usage of your query cache using the following formulas:

Current size compared with maximum available size:

To calculate the percentage used value for the query cache you can use the following formula:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

The Query Cache Hit Rate:

The percentage hit rate on the cache can be calculated as follows:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

e.g.The usage of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.

Hits to Insert Ratio and Insert to Prune Ratio

These two ratios are calculated by the following two formulas:

Qcache_hits/Qcache_inserts
Qcache_inserts/Qcache_prunes

DO NOT allocate too much memory

Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is:

memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections


I've see at least two problems:
1. fields list doesn't match values.
2. serialized data doesn't quoted properly.

May be you have try native mysqldump instead of "Backup wizard"?

mysqldump \
-u USERNAME -pPASSWORD \
--opt                  \
--no-create-db         \
--comments             \
--complete-insert      \
--order-by-primary     \
DATABASENAME  > ./dbdump.sql

Keep in mind that there is no space between -p and PASSWORD.


Is there a way to see completed process list in mysql (SHOW FULL PROCESSLIST)

In MySQL, queries executes in the past can be seen in the general log.
See the reference manual:
https://dev.mysql.com/doc/refman/8.0/en/query-log.html


Is there a way to list the "last backup" file of a Database?

This is just a matter of using dynamic SQL to process your data. You can also leverage msdb.dbo.backupset and msdb.dbo.backupfamily to find your most recent full backups.  This script will get the most recent full backup file location for each database and use it to run your cursor:

declare @dbname sysname
    ,@backupfile nvarchar(max)
    ,@sqlcmd nvarchar(max)

declare recscan cursor fast_forward
for
select
    bs.database_name
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'

open recscan
fetch next from recscan into @dbname,@backupfile

while @@fetch_status != -1
begin
    PRINT 'Validating '+@dbname
    select @sqlcmd = N'restore verifyonly from disk = '''+@backupfile+''';'

    exec sp_executesql @sqlcmd

    fetch next from recscan into @dbname,@backupfile
end

close recscan
deallocate recscan


Rule of thumb for tmp_table_size and max_heap_table_size MySQL properties

I recommend a simple 1% of memory for each of those.

max_heap_table_size is the limit on a ENGINE=MEMORY table you CREATE.  Not many people use that engine, if you do, set the max size just before doing the create.
LEAST(max_heap_table_size, tmp_table_size) is the cap on how big to let certain implicit temp tables to get.  These are tables used inside SELECTs (etc) to handle GROUP BY, ORDER BY, subqueries, etc.

Since those temp tables can be created by every connection, and possibly multiple temp tables per query, 1% of RAM is a rather "safe" limit.  When MySQL runs out of RAM, the OS swaps things -- Swapping MySQL is terrible for performance.

Also, in setting these too large, you are stealing RAM from other uses (eg, buffer_pool), thereby potentially slowing down all queries.

Other Rules of Thumb

See also SQL_BIG_RESULT.


Non-Clustered-Index on a temporary table

Temporary tables comply to the same rules as permanent tables when it comes down to indexing. The only difference is in the storage location, which is Tempdb for temporary tables. However, if you are adding an index to a table that is heavily written, you have to take into account the write vs. read tradeoff.

Since the temporary table is probably used in a procedure or in a script, it's your code that controls how hard you're hitting the table with writes.
INSERTs are faster without indexes in place: if you're inserting lots of data in multiple statements, you probably want to create the index after fully populating the table.
UPDATEs and DELETEs have to find the row(s) to modify first, so they could highly benefit from proper indexing.

If your DBA wants to pay (a lot) more reads + CPU + elapsed time vs. some writes, I think (s)he should clarify his point.

Long story short, if your code runs faster with a NCI, go on and add it.


Remove extra return from median calculation

Extra return might comes from SortValues function. Use Trim4 custom function to remove it.

Trim4 ( SortValues ( beak.trait; 1) )

Use it for MiddleValues functions too.


postgres \dt functionality change from v9.4 to 9.6?

Check the search_path for each user you're trying to check on.

SHOW search_path;

If they're different you can set them with SET search_path TO mySchema, public;


Join select queries on an id in SQL Server

If I understand correctly, I think this should work...

We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char.

It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug.

SELECT
  a.id,
  a.name,
  a.comment,
  COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b
  ON a.id+100 = b.char
INNER JOIN TableC
  ON a.id+100 = c.eid
GROUP BY a.id, a.name, a.comment;

As Aaron Bertrand pointed out below, using EXISTS would help to eliminate double counting on where duplicate eids appear in  TableC.

SELECT
      a.id,
      a.name,
      a.comment,
      COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b
  ON a.id+100 = b.char
WHERE EXISTS (SELECT * FROM TableC where eid = a.id+100)
GROUP BY a.id, a.name, a.comment;


Displaying only the most recent activity of an item

Try this:

    SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM <tablename> a
    INNER JOIN
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM <tablename>
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Update:

In your specific case:

SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM tblSend_Recieve a
    INNER JOIN
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM tblSend_Recieve
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]


How does MySQL manage a delete then select of the same row within a transaction

As far as the SELECT statement is concerned, the id = 1 row is gone. There might be argument as to whether the physical storage still contains the bits of the data pertaining to the deleted row at the time of the SELECT, but in terms of access to that data, the select will (should) return nothing.


Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records

  ...

  The clustered index goes fragmeted to 90+% in a day or so.

Look at your clustered index, its key is 48 bytes long, it's not a good choice because your table is big enough and you have also 5 nonclustered indexes. All of them have these 48 bytes at every index level,
so every nonclustered index occupies at least twice of space it needs.

IMHO, the first thing to do is, if possible, to change clustered index key, your clustered index can be defined on identity, it will be unique, always increasing, narrow, and this will reduce yor clustered index fragmentation, and in case when JsonData field is never updated clustered index fragmentation will be 0.

This will also decrease your insert time: now too much time is spent to log page slits caused by insert into clustered index.

To your second problem: lock escalation.
As you said, every batch contains 2000 rows in the source table, but they cause 3402 rows to be inserted(according to estimated plan), and this is only for clustered index. You have 5 nonclustered indexes, so in one statement you insert at least 6 * 2000 = 12000 rows, or maybe all 20412 rows if the estimations are correct.

Lock escalation triggers on 5000 locks per statement:

  In addition to escalating locks when an instance-wide threshold is
  crossed, SQL Server will also escalate locks when any individual
  session acquires more than 5,000 locks in a single statement. In this
  case, there is no randomness in choosing which session will get its
  locks escalated; it is the session that acquired the locks.

and in your case they very probably are row locks, this is because of your clustered index key that is random.
It could take page locks in case of insertion into always increasing key, but your clustered key is really random. And in any case insertions into nonclustered indexes are random too, so it's normal that server chose row locks.

So you can disable lock escalation on your table or split your batches in 1000 rows per batch or even less, this should be tested.

Here is a small repro in response on this comment:

  inserts can't take locks (can't lock a resource that doesn't exist)

if object_id('dbo.t') is not null drop table dbo.t;
create table dbo.t(id int identity primary key, col1 varchar(10), col2 varchar(10));
create index ix_col1 on dbo.t(col1);
create index ix_col2 on dbo.t(col2);

begin tran
insert into dbo.t (col1, col2)
select top 1000 'aaa', 'bbb'
from sys.columns c1 cross join sys.columns c2;

select *
from sys.dm_tran_locks
where resource_type <> 'DATABASE'
      and request_session_id = @@spid
order by resource_associated_entity_id,
         resource_type;

rollback tran;


You can't reference the inserted pseudo-table from outside the trigger. And do you really want to send an e-mail for every insert/update? And make the transaction that invoked the trigger wait for all that to happen (even though database mail is asynchronous, it isn't free)?

I suggest you log the data to a queue table, and have a job that every minute (or five minutes, or some reasonable frequency) wakes up and sends mail for any new data it finds in the queue table.


Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying |
 foo       | text              |

db=> \d temp_person_two
   Column    |         Type         |                            Modifiers
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    |
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

  Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.


UPDATE statement not consistent when including NULL fields

In SQL Server, when you concatenate string, if one part is NULL the result of the string will be NULL. To avoid this behavior, you have to test all your string with the ISNULL function to ensure a string empty instead of the NULL value.

UPDATE Notes

SET

/* append previous month to notes */
Notes=ISNULL(Notes,'') + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(ISNULL(oppo_rota_last_progress_update,'') as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))


Trying to optimize query for performance (MySQL)

Your original query joins everything together then traverses 50000 rows into the joined data before presenting the next 100 rows. Try this

Get the 100 keys from ProjectManagement2 upfront
join everything afterwards

Here is my proposed query

SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM
(
    SELECT B.* FROM
    (SELECT id FROM ProjectManagement2 LIMIT 50000, 100) A
    INNER JOIN ProjectManagement2 B USING (id)
) i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
;

Give it a Try !!!


Since you have not provided any RDBMS I've tried to post an answer that should work in more than one system.

Records can be ordered by both ranks and numbered using row_number() window function.

select row_number() over (order by r1, r2) id, name
from (
      select t.name as [name], t.[rank] r1, 0 as r2
      from   thing t
      union all
      select w.name as [name], t.[rank] r1, w.[rank] r2
      from   widget w
      join   thing t
      on     t.id = w.thing_id
     ) x;
GO

id | name
:- | :--------------
1  | thing blue
2  | thing yellow
3  | thing green
4  | widget bean
5  | widget cucumber
6  | thing red

dbfiddle here


How to retrieve all data from table if column value providing null value?

This should do the job.

DECLARE @country1 varchar(30)
SET @country1 = 'asd'

SELECT country_id, country
FROM   country
WHERE country.country LIKE IF(@country = '', '%', @country);

The IF statement works like this : IF(condition, ifverifiedcondition, else);


Table Relationships for Property Bags Configuration

You've captured the relationships in the design that you've referenced correctly.  The one nuance that isn't clear in what you've documented is that the relationship from user_preference to allowed_preference_value is optional, meaning that user_preference.allowed_preference_value_id is nullable.  It's not clear whether you've accounted for this or not, but it matters if you have user preferences which are not selected from pick lists but are instead taken from another source.

Your design also omits the preference values themselves.  I'm assuming that was a deliberate choice to focus on the relationships and gloss over the rest.


Dynamic Server Name in SQL Statements (from clause)

Dynamic SQL will work. I have written similar things in SQL Server 2000 this way.

EXEC() can take a string variable as input. Construct this string as you show in the question, using the current DB context.

Instead of table variables, however, use temporary tables (#SomeName). These are visible in nested scopes, once defined. So create the #Table, then construct the SQL string referencing the temp table, then EXEC that string.


I came up with a solution for your data set based on some work that I was doing and some modifications to the solution posted by Serge Rielau and Rick Swagerman on IBM's developerWorks.

Data Setup queries:

DECLARE GLOBAL TEMPORARY TABLE sample_data (id INTEGER, otherid integer, data VARCHAR(255)) WITH REPLACE ON COMMIT preserve rows NOT logged;
INSERT INTO session.sample_data SELECT 5059, 73831, '5103,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73941, '5103,5104,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73974, '5103,5106,5107,5108' FROM sysibm.sysdummy1;

Solution Select Query:

WITH
split_data AS
(
    SELECT
        id as group_by_1,
        otherid as group_by_2,
        data AS split_string,
        ','  AS split
    FROM
        session.sample_data
)
,
rec
(
    group_by_1,
    group_by_2,
    split_string,
    split,
    row_num,
    column_value,
    pos
) AS
(
    SELECT
        group_by_1,
        group_by_2,
        split_string,
        split,
        1,
        VARCHAR(SUBSTR(split_string, 1, DECODE(INSTR(split_string, split, 1), 0, LENGTH(split_string), INSTR(split_string, split, 1) - 1)), 255),
        INSTR(split_string, split, 1) + LENGTH(split)
    FROM
        split_data
    UNION ALL
    SELECT
        group_by_1,
        group_by_2,
        split_string,
        split,
        row_num + 1,
        VARCHAR(SUBSTR(split_string, pos, DECODE(INSTR(split_string, split, pos), 0, LENGTH(split_string) - pos + 1, INSTR(split_string, split, pos) - pos)), 255),
        INSTR(split_string, split, pos) + LENGTH(split)
    FROM
        rec
    WHERE
        row_num < 30000
    AND pos > LENGTH(split)
)
SELECT
    group_by_1 as id,
    group_by_2 as otherid,
    column_value AS data
FROM
    rec
ORDER BY
    group_by_1,
    group_by_2,
    row_num;

Results:

ID  OTHERID DATA
5059    73831   5103
5059    73831   5107
5059    73941   5103
5059    73941   5104
5059    73941   5107
5059    73974   5103
5059    73974   5106
5059    73974   5107
5059    73974   5108

Comments:

The solution select query can be modified to fit your specific result needs by including as many GROUP_BY_X rows (O to many) in the REC table definition and matching rows in the two unioned sub-selects.


No and yes.

No -- in that you would need to dump the data from your single server; this is invasive and costly.

Yes -- in that there are several ways of setting up Replication to make this task a breeze, possibly near-instantaneous, regardless of dataset size.

With a Master (Prod) + Slave, you could disconnect the Slave to use it as Staging; it would be up to date to the moment when it was grabbed.  But if you modify the Staging server, it could not be put back as the Slave.

Galera -- Have 3 (or more) nodes.  Removing a node from the cluster is easy.  Adding a node to the cluster is easy -- the dump+reload is automatic.  Then the grabbed node becomes Staging; then throw it away.

LVM -- This requires setting up the Prod machine with LVM before building the database.  However, a one-minute downtime is all it takes to grab a snapshot of the prod machine.  You then have hours to copy (perhaps via rsync) to the Staging server.


TSQL success or failure result of EXEC dynamic SQL

I found the solution...
I wrapped the statements in another try catch within the big try catch
(Didn't know we can do that) :-(
    BEGIN TRY
        EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
        EXEC(@SQLStr)
    END TRY
    BEGIN CATCH
        SET @ErrorMessage='Error Creating '+@uniqueTMP+' table - verify query in @SRC'
        RAISERROR(@ErrorMessage,16,1)
    END CATCH

I tested it an it worked.
I hope this will help someone else...


Give execute permission to user which does not have execute permission for specific stored procedure

While a procedure runs, normally it has access to data in the database like the owner of the procedure, which is usually "dbo" and can access everything.  I may have got this wrong: I seem to be describing "EXECUTE AS OWNER" and I have documentation which says that the normal setting is "EXECUTE AS CALLER".  I just thought that is what a "procedure" was -for-.  Anyway, either is an option.

A procedure's references to objects in different databases use the rights of the actual user - they must do because a procedure only understands security in its own database.  An exception is where a user's connection invokes an "application role"; in that case, the user has the access of the "guest" account to objects in other databases.  This normally is or ought to be "no access".

None of this relates to who is allowed to execute a procedure in the first place, and that is what you're trying to set.  That has to be allowed with GRANT.

However, if you issue GRANT and DENY commands that apply to the same object access, the result is to DENY - that's absolute.  Perhaps you've made that mistake.  To un-grant privileges, use REVOKE when you don't want to DENY.  Also, normal practice is to GRANT or DENY privileges to roles, then manage which roles a user is a member of.  Or even roles with privileges, whose members are roles with business functions (job titles), whose members are actual user accounts.

What "EXECUTE AS" does is to control the object access rights that a user may be "given" (or denied) in a procedure which the user doesn't have in an ordinary batch of Transact-SQL commands.


Count values to show up with zeros instead of null

You need to join in the opposite way: managers LEFT JOIN employees

create view scnt as
    select E2.id as super,
           count(E1.id) as cnt
    from Employee E2                    -- managers
      left outer join Employee E1       -- employees
      on E1.super = E2.id
    group by E2.id
  ;

select * from scnt ;

dbfiddle


Microsoft SQL - How to merge when certain values match

You need to use the SQL equivalent of GROUP_CONCAT to achieve this result.

Please see this stack overflow answer

You could also use the CROSS APPLY method from that query.

I wrote an example using your query above. In your case, you will want to add this subquery:

SELECT STUFF((SELECT ',' + c.CaseNumber
             FROM CaseNumber as c
             WHERE Response_Master_Incident.ID = c.MasterIncidentID
             FOR XML PATH (''))
             , 1, 1, '')


Sort products by image first and by name ASC

Suggest an additional sorting column, imgsort, used as the first
criteria for the ORDER BY clause. Something along these lines:

   SELECT
       fields, ...
       , IF(image="",0,1) as `imgsort`
   FROM ...
   ORDER BY `imgsort` DESC, ...
   ;


Spotlight for SQL Server LockType explanation required

Shared database locks are normal, simply meaning a connection is open in that database context.  That lock prevents another session from performing an operation like dropping the database while it's being used.

A session AWAITING_COMMAND that is blocking other sessions is an indicator the session still has an open transaction holding a lock on a resource (probably not database) that is incompatible with the other session's requested lock type.  That resource is typically a table or row but you mentioned that's not the case here. You can query sys.dm_tran_locks to identify granted and waiting locks on resources for each session.  I expect Spotlight would use this or similar a DMV.


Restoring SQL Server databases - tips and tricks?

It looks like you did not restore a backup but you just transferred the tables with some kind of bcp tool.

Common issues with backup/restore are, in my experience:
a) Logins missing or mismatched, because they are in the master database
b) Collation problems when the collation of the server on which you restore the backup is not the same as the one from which you made the backup.

I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION".


I suspect that the MySQL query optimizer occasionally choose a sub-optimal path. I'm not certain why it would sometimes use the index and sometimes not, nor why it seemed related to the index in question.

I think the query itself could not be fully covered by the index (it included an ORDER BY that I did not cover). I made a change to the query to ORDER BY student.created_at and added an index on (created_at, school_id, student_status_id) and everything looks good now.


Postgresql 9.6 best settings for large admin work (min_wal and max_wal)

I guess you only want to test if the restoration of the dump works and nothing more, which means you can do some unsafe configuration changes. Let's first start with your settings.
These one are a good call:
fsync = off
synchronous_commit = off
full_page_writes = off
wal_level = minimal
autovacuum = off

These three are only important if you are using replication, and because you already set wal_level to minimum, you are not using it, so they are not important:
wal_keep_segments = 0
archive_mode = off
max_wal_senders = 0

You have a lot of RAM which won't be used for anything, I would bump this one up even more:
maintenance_work_mem = 3GB

I would leave wal_buffers to it's default:
wal_buffers = -1

and bump shared_buffers (wall_buffers will be automatically calculated):
shared_buffers: 4GB

What you should try to concentrate is to have no, or as less checkpoints as you can during your restore. Checkpoints are controlled by max_wal_size and checkpoint_timeout. First of bump checkpoint_timeout to something like 20h, so that a timed checkpoint never happens while you restore:
checkpoint_timeout = 20h

Then you can set max_wal_size to a value as high as your disk space allows you. If your restored DB is 200GB and your disk 500GB, you should be safe to set max_wal_size to 100GB because Postgres can store up to two checkpoints of wals (which is 2xmax_wal_size):
max_wal_size = 100GB

min_wal_size won't matter that much in your case, but you can probably bump it to 10GB
min_wal_size = 10GB

I would also recommend that you use pg_restore with --jobs=NUM where NUM would probably be the number of CPU cores, but it also depends on the speed of your SSD, so you can play with this parameter.
Besides Postgres settings, I would also recommend that if possible you add an additional SATA drive (7200RPM will be fine) and symlink pg_wal directory to that SATA disk. That is the directory where Postgres saves WALs, and because they are written in append, SATA is fast enough for them. It will reduced the load on the SSD, but will also mean you will be able to bump max_wal_size even more (depending on the size of the SATA disk).
Finally don't forget to restore your settings to the sane values after the dump is restored.


Postgres - fast way to enforce AND across multiple rows

I think you have unnecessary steps in your query, consider this:

SELECT *
FROM (SELECT
        COUNT(tag) AS numtags,
        tweet_id,
        MIN(score)      AS minscore
      FROM  core_tagusage
            WHERE tag = 'dog'
                  OR tag = 'grass'
                  OR tag = 'frisbee'
      GROUP BY tweet_id) AS ct
WHERE numtags = 3
ORDER BY minscore DESC
LIMIT 200;

and this:

SELECT
    tweet_id,
    MIN(score) AS minscore
FROM  core_tagusage
WHERE tag IN ('dog', 'grass', 'frisbee')
GROUP BY tweet_id
WHERE COUNT(*) = 3
ORDER BY minscore DESC
LIMIT 200;

I also think that running analyze on your table will help the query planner.

After playing with some fabricated data locally, I was seeing ~600K rows take ~1sec, so 10 seconds doesn't seem unreasonable (but my data was so predictable that analyze made the query planner just go for a sequential scan)


Can I connect mysql client to a mariadb server?

Anyway, I fixed the problem via
MariaDB [(none)]> Grant All Privileges ON *.* to 'remoteroot'@'192.168.0.15' Identified By '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;

where 192.168.0.15 is the host machine IP.
Then connected via
cmd> mysql -h 192.168.0.17 -u remoteroot -P 3306 -p

Looks like working.
Still concerned some features face problems.


How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

You are looking for the STR_TO_DATE() function. MySQL expects by default dates in ISO format.

In your particular case it would be something like:

SELECT * FROM Services ORDER BY STR_TO_DATE(nextdate, %d-%m-%Y) DESC;

%d-%m-%Y or %m-%d-%Y, whatever is the "wrong" format you are using.

Using ISO dates (either strings or, preferably, timestamps) plus an index will have the efect that it will speed up your queries.


UPDATE ... FROM ... syntax for iSeries for a MS SQL Server developer

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key:

UPDATE
  ( SELECT t1.a AS t1a, t2.a AS t2a
    FROM t1
      INNER JOIN t2 ON t2.b = t1.b
  ) u
SET
    t1a = t2a ;

Tested at (Oracle 11g) SQL-Fiddle

Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the uniqueness, although that is not a good thing*):

WITH u AS
 ( SELECT t1.a AS t1a, t2.a AS t2a
   FROM t1
   INNER JOIN t2 ON t2.b = t1.b
 )
UPDATE u
SET
 t1a = t2a ;

Tested at (SQL-Server 2008) SQL-Fiddle

*See the blog article: Let's deprecate UPDATE FROM! where Hugo Kornelis mentions):
 ... SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. ...

You can also check this syntax that doesn't use CTEs or updatable views - and thus will work in almost all DBMS:

UPDATE t1
SET a =
  ( SELECT t2.a
    FROM t2
    WHERE t2.b = t1.b
  )
WHERE EXISTS
  ( SELECT *
    FROM t2
    WHERE t2.b = t1.b
  ) ;

According to the online manual for DB2 i series7 : UPDATE - and if I read it correctly - only the 3rd way is an option in DB2 for iSeries7. The 1st way will work too if the derived table (u) has been defined as a view.


Query in PostgreSQL became slow and then got better on its own - why?

It is a very broad question, even more so as you give absolutely no details about your setup and kind of database (volume, type of queries, active connections, size of RAM, dedicated server or not, etc.)

You can start by enabling PostgreSQL to log slow queries, see the log_min_duration_statement in the configuration. That will give you historical data that you would then be able to analyze and maybe correlate with other things (like from the list of @Vérace)

You will then have various tools to help, as described on https://wiki.postgresql.org/wiki/Logging_Difficult_Queries :

pgFouine
PQA
EPQA
pgsi


The FILEPROPERTY is expecting a file (not a filegroup).
Make sure the the name you pass in parameter is on one of those :

select name from sys.database_files

This query will give you information about the data files of the selected database:

With A as
(SELECT db_name() as [Database],fg.name as [FilegroupName],  RTRIM(f.name) AS [Segment Name], f.groupid AS [Group Id], f.filename AS [File Name],
CAST(f.size/128.0 AS DECIMAL(10,2)) AS [Size in MB],CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(f.name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(f.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles f join sys.filegroups fg on f.groupid=fg.data_space_id )
select 1 as [order], * from A union  select 2 as [order], REPLICATE('-',10), REPLICATE('-',10), REPLICATE('-',10), 0, REPLICATE('-',50),0,0,0,0
union
select 3 as [order], [Database],[FilegroupName],  '', [Group Id], '', sum([Size in MB]), sum([Space Used]), sum([Available Space]), Cast(sum([Space Used])/sum([Size in MB])*100 AS DECIMAL(10,2))
from A
group by [Database],[FilegroupName], [Group Id]
ORDER BY [order], [Segment Name]

Thanks


Select unique value whereas the time is highest in the most optimal way

The obvious index for this query is on (test,time). Add it with:

ALTER TABLE test
  ADD INDEX text_time_IX          -- choose a name for the index
    (test,time) ;

You may also get efficiency gain if you use this version of the query (pushing the limit inside the derived table):

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`
    ORDER BY
        `text` ASC
    LIMIT 0,50 ) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    `text` ASC ;

If there cases where the same maximum time appears on more than one row with same text, it will not show the same exact results and will return more than 50 rows (but I think this version is more appropriate). If you really want the arbitrary choice in case of ties, you can add the LIMIT in the external select, too.


What is the best choose for PK in Partitioned Table?

For the first question, there's no issue with using the same function for two tables as long as the definition of the partition function doesn't ever need to change. You mentioned deleting daily data and your partition function is planned to contain 32 partitions, so I assume that you're creating one partition per day of the month. If so, I can't see a reason why you would need to merge or split partitions. With that said, given that you just have two tables involved there isn't anything wrong with creating two separate functions as well.
Whether or not the two tables should exist on the same database files depends on how you're defining your files and filegroups. Given that you're only keeping 14 days of data and you purge the rest, I assume that you aren't putting some partitions on different classes of storage. 5 million rows in the Sens table really isn't a large number, so it's likely true that there will be no issue with using the same database files with no tables. This is just a guess based on incomplete information.
For the second question, evaluate whether or not a partition-aligned index is the right fit for the table:

An index that is built on the same partition scheme as its
corresponding table. When a table and its indexes are in alignment,
SQL Server can switch partitions quickly and efficiently while
maintaining the partition structure of both the table and its indexes.
An index does not have to participate in the same named partition
function to be aligned with its base table. However, the partition
function of the index and the base table must be essentially the same,
in that:

The arguments of the partition functions have the same data type.
They define the same number of partitions.
They define the same boundary values for partitions.

My own opinion is that you shouldn't partition a table with 5 million rows unless you have a very good reason. Partitioning is a feature designed for large tables. Why do you need to partition a table with 5 million rows?
For the final question, I have worked with partitioned tables that have computed partition columns. I do not recommend it unless you have no other choice. We consistently ran into odd issues including partition elimination not working as expected.


External size of table still not released (as disk space) after issuing truncate command

I think with a truncate even without a VACUUM FULL the space is released back to the OS. My guess is per the other question that you've got a backend process holding the underlying file open.

Find out what the file is. SELECT pg_relation_filepath('links_groupseen');
Find the data directory SHOW data_directory;
Run truncate.
See what's holding it open. lsof $data_directory/$relation_filepath

I think the most likely bet is something is holding it open if not, we'll have to know whether or not Pg is deleting the underlying relation_filepath before and after the truncate command and if anything has that file open (if it exists). You may have to run lsof before the truncate too.


How to connect to oracle 12c from sqlplus without password in command line

This answer based on a1ex07 comment:

given the folowing detailes:

user name: demo
password: Pass1234
ip: 1.2.3.4
listener port: 1521
oracle SID: orcl
pdb service name: pdborcl

connect to core db from db host as sysdba:

without need of password: sqlplus / as sysdba
password manually: sqlplus sys as sysdba
password in command:  sqlplus sys/Pass1234 as sysdba

connect to core db from db host or remote as sysdba:

password manually:  sqlplus sys@\"1.2.3.4:1521/orcl\" as sysdba
password in command: sqlplus sys/Pass1234@1.2.3.4:1521/orcl as sysdba

connect to pdb from db host or remote as sysdba:

password manually:  sqlplus sys@\"1.2.3.4:1521/pdborcl\" as sysdba
password in command: sqlplus sys/Pass1234@1.2.3.4:1521/pdborcl as sysdba

connect to pdb from db host or remote as demo (regular user):

password manually:  sqlplus demo@\"1.2.3.4:1521/pdborcl\"
password in command: sqlplus demo/Pass1234@1.2.3.4:1521/pdborcl


Why does SSDT deploy inline FK Constraint as WITH NOCHECK?

There is only one kind of foreign key constraint (though it can be trusted, or untrusted).

If it was a new table, both code paths are essentially the same. But they do it this way because SSDT is taking into account it might need to tweak the DDL to add to an existing table.

If the table existed and it altered the table with check, the deployment will fail and will not put a check constraint on the table.
If the table existed and it altered the table with nocheck, leaving the check for afterwards, you'll get an error if old data doesn't meet the criteria, but at least the schema change will be in place.

What they've done is generate a consistent set of code that covers both non-existing and existing possibilities. They haven't tried to optimise for every use case.

Essentially it has no further inherent meaning. It's just an artefact of how they output the code.


Difference between logical and physical reads/writes

In short, I would like to understand the difference between physical and logical reads/writes in a database

A very terse explanation of the difference between logical and physical reads can be seen in the Microsoft documentation for the diagnostic command SET STATISTICS IO here:

logical reads - Number of pages read from the data cache.
physical reads - Number of pages read from disk.

Note that when it says "data cache" there it means RAM.

I've never heard of anyone talk about logical writes so I can't speak to that one.  If that just means writes, then it's talking about inserts, updates, and deletes.

Regarding this:

  My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.

Looking at the graph in your question, I do see the period of high disk utilization ("low idle time").  If you can identify what procedures are running during that time (for instance, by logging sp_whoisactive to a table), then you can try to tune those specific queries (or post questions on this site with sufficient detail to get some help).


To use file watchers, the database Java virtual machine (JVM) component must be installed.

Check whether java is installed :

COMP_NAME                                          STATUS
-------------------------------------------------- -----------
Oracle Expression Filter                           VALID
Oracle Enterprise Manager                          VALID
Oracle Workspace Manager                           VALID
Oracle Database Catalog Views                      VALID
Oracle Database Packages and Types                 VALID
JServer JAVA Virtual Machine                       VALID
Oracle XDK                                         VALID
Oracle Database Java Packages                      VALID

8 rows selected.

SQL> l
  1* select comp_name, status from dba_registry


Script for listing tables having no primary key oracle 9i

select table_name from user_tables
minus
select table_name
from all_constraints
 where (constraint_type ='P' ) and owner='dbSID_here'


Please try the experiment again; this time capture these for the two values of join_buffer_size:
 EXPLAIN SELECT ... (including the query)
 how long the query took
 SHOW CREATE TABLE ...  -- of reach table in the query

Meanwhile, I will guess at this explanation.  The Optimizer has several ways it might perform a query.

Ignore the join_buffer and use lookups (indexed or not) for joining.
Use the join_buffer:  Load the second table completely into a hash in RAM, then reach into it for lookups.

The potential problem include

The Optimizer may not have the "right" statistics for deciding between the two choices.  (There is no reliable way to fix this for all cases.)
There may be a LIMIT or something else that makes loading the entire table the "wrong" approach.
Once the join_buffer is loaded, the rest of the query is CPU-bound.  When not using the join_buffer, the query may be I/O-bound and is likely to take longer (elapsed time).  (Hence my request for the timings.)
Etc.


Query to join two subsets of table data (get it faster)

A few things that can improve your query:

There is no need for the derived tables (subqueries). You can have a 4-table join in the FROM clause.
Once we do the above, it's obvious that you join table1 to itself on a column that seems to be UNIQUE: (ON glv.keycode = gln.keycode). We can remove this join, so the result is a 3-table join.

The query:

SELECT
    t1.id,
    t1.value  AS keycode,
    ln.teksts AS teksts_ln
FROM
    table1 AS t1
    LEFT JOIN table2 AS lv
      ON t1.value = lv.keycode AND lv.lang_id = 5
    LEFT JOIN table2 AS ln
      ON t1.value = ln.keycode AND ln.lang_id = 10
WHERE
    t1.stat = '1'
ORDER BY
    t1.weight ASC, lv.teksts ASC ;

After we have it working correctly (producing the same results), you can improve efficiency by adding indexes:

on table2 (lang_id, keycode, teksts)
on table1 (stat)


How to rebind a package inside a Stored Procedure (DB2)

There is a way to rebind the packages via the following routine

db2 "CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'SCHEMA1.PROC1', 'ANY')"

P -> stands for Stored Procedure
SCHEMA1.PROC1 -> stored procedure name with the schema
ANY -> an optional input argument supported for backward
compatability. You can use ANY or CONSERVATIVE.

Please refer these links for more information.

http://www-01.ibm.com/support/docview.wss?uid=swg21250633

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0009863.html

Note: Explicitly rebinding the associated package does not revalidate the routine. Revalidate an invalid routine by using automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Routine revalidation automatically rebinds the dependent package.


That path is the full source of SQL installation info and is a goldmine.

The best way to deal with a failed patch is to find out what happened; either in those files or the SQL ERRORLOG. It's most commonly SSISDB in an availability group or a filestream error (fixable with add/remove programs on the instance to run a repair).

I'm not sure if you can rollback an unsuccessful patch but it's easy to reapply the patch and try again, they resume very very well. After its successfully installed then they're easy to uninstall with add/remove programs again.

There aren't many good reasons to uninstall an SP or CU for SQL though. They're pretty important.


Hourly differential backups with append option

If you are backing up with NOINIT the backup files will continue to grow if they have the same name and destination.
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options
These options operate on the media set as a whole.
{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Personally I always use INIT and a distinct file name (and/or backup the file that is overwritten before it is overwritten)
If you are taking hourly differential backups, you might want to consider changing to full recovery and doing hourly t-log backups.  But I understand this is not always an option.
You don't have a good description of how you are protecting the full and differential backups, but you unless you have a good reason not to, you probably want each diff and full to use INIT and have date/time stamp in the file name.
If you must append your diffs for more than one day, you should probably validate you can restore.  I have not tried it, but I suspect that restoring from appended diff is going to be painful at best.
Note that per this answer you can not use EXPIREDATE (backup expiration date) to keep rolling backups in the same file.


Counting unique records across 2 columns and computing a score based on boolean values

This would be very simple in SQL. As far as I can read the BigQuery Query reference, it supports all (GROUP BY clause, COUNT() and SUM() functions, CASE expressions):

SELECT
    custID, InteractionDate,
    1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success,
    SUM(Sales) AS Sales
FROM
    tableName
GROUP BY
    custID, InteractionDate ;


Availability Group database stuck in Initializing / In Recovery mode even after restarting service

Well, when I saw the error messages I figured the database was not added to the Availability group, but after checking the Availability Databases in SSMS on the new replica it was there with a pause symbol next to it. This means it was added but the data movement has stopped. I tried resuming data movement, but it was too far behind. So instead I had to remove it from the availability group and manually apply log files so it could start replicating again.

-- Remove database from Availability Group:
Alter Database [StackExchange.Aviation.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Aviation.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_RAG];
ALTER DATABASE [StackExchange.Aviation.Meta] SET HADR RESUME;

I should have noticed it was added, but the AG has over 300 DBs and was still in the process of seeding so it was missed among the noise. The "Lock request time out" error message also made me think that something had gone wrong in the seeding, but it was just a poorly worded message indicating the database was already part of the Availability Group.


It sounds like you are using Availability Groups and either one of your replicas is not syncing correctly, or it is getting so far behind that it is causing the log on your primary to grow until is reaches its maximum size (or you run out of disk).

Check that your secondary replica(s) are online and syncing
correctly.
See if you can increase the max size of your transaction
log on the primary
Increase the allocated storage on your transaction
log drive
Rebuild individual indexes with a wait between them to help
allow time for log blocks to flush down to your secondary replica(s) (check out Minion Reindex free tool that can help with this)


MySQL select...into variable yields null, even though data is there

For a while, this one eluded me. I don't like SELECT ... INTO, so I really wanted to blame it for your trouble, but no.

Here'e the problem.

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE authority=authority_id AND name=fqdn;

How does MySQL interpret this?

SELECT id
  INTO fqdn_id
  FROM UrlFqdn
 WHERE 'mail4' = 1 AND name = 'mail4.z.uk';
/* Impossible WHERE */
/* see DECLARE authority TEXT; */

  “A local variable should not have the same name as a table column.   If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”

  — http://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

Using the same identifier for a variable and a column name makes a debugging mess, one way or another.


Backup a sample of a PostgreSQL database

I had a similar problem, I wanted to copy the most recent rows from a few tables from one db to another hosted on a different server, and I ended up writing a bash script that executes the pg_dump command followed by various psql commands:

#!/bin/bash
declare -a arr=("my_table_1" "my_table_2" "my_table_3")
startdate=2014-12-01
enddate=2014-12-30
for table in  "${arr[@]}"
do
    echo -e \\n$table\\n$(for each in $(seq 1 ${#table}); do printf "-"; done)
    pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c | psql -h host_name_2 -U my_username db_name_2
    psql -h host_name_1 -U my_username db_name_1 -c "\copy (select * from $table where date >='$startdate' and date <='$enddate' order by date desc) to '/tmp/data.csv' csv header;"
    psql -h host_name_2 -U my_username db_name_2 -c "\copy $table from '/tmp/data.csv' csv header"
done

Line by line in the body of the loop:

the echo is just prettyfication: it prints the name of the table and underlines it like a title,
the pg_dump command generates some SQL to copy the table schema from the source and performs a few more SQL housekeeping commands (e.g. the -c flag means that the SQL will contain a command to delete the table first before creating it), and this SQL is piped with | to psql pointing at the destination (you can try running just the pg_dump part at the comand line, pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c, and see the SQL it generates),
the psql on the next line copies a table in the source db into a csv file,
the final psql copies from that csv file into a table in the destination db.


MariaDB ability to work in parallel across multiple db users

Each connection is controlled by its own limits.  The limits are initialized by global settings, but run independently for each connection.

Separate connections will be accessing common resources, so there will some contention (CPU, I/O, etc), but not of the type you described.

"What you do in Vegas stays in Vegas."


Table design for child tables with multiple many-to-many relationships

I'd recommend creating a table which encompasses subnets, VLANs and VPLSs; you can then have a simple self-join table.  If these three concepts each have a significant number of unique fields, you can use vertical partitioning and create a sister table for each of the three.  Using pseudo-code:

* Networks
NetworkID PK

* Locations
LocationID PK

* SubNetworks  -- This concept encompasses SubNets, VLANs, and VPLSs
SubNetworkID PK,
NetworkID  NOT NULL REFERENCES Networks

* SubNetworkLocations
SubNetworkID NOT NULL REFERENCES SubNetworks,
LocationID   NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkID, LocationID)

* SubNets
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VLANs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VPLSs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* SubNetworkRelationships
SubNetworkA NOT NULL REFERENCES SubNetworks,
SubNetworkB NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkA, SubNetworkB),
UNIQUE (SubNetworkB, SubNetworkA)  -- For efficient querying from the other direction

As with any many-to-many table, be sure your sproc for inserting into SubNetworkRelationships checks for the existence of both { A, B } and { B, A } before inserting a new row.  You can use check constraints to prevent a sub-network from relating to itself.

In this model, there's no RDBMS-level enforcement of the rule "if two sub-networks are related, they must have all of the same locations."  I'm going to go out on a limb and assume that all such tied-together sub-networks must belong to the same Network, too.  If you really want this:

* Networks
* Locations

* SubNetworkGroups
SubNetworkGroupID PK,
NetworkID         NOT NULL REFERENCES Networks

* SubNetworkGroupLocations
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
LocationID        NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkGroupID, LocationID)

* SubNetworks
SubNetworkID PK

* SubNetworkGroupMemberships
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
SubNetworkID      NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkGroupID, SubNetworkID)

* SubNets
* VLANs
* VPLSs

A group of SubNetworks belong to one Network but zero or more Locations.  A SubNetwork can belong to multiple groups.  You'd still need to enforce that the various groups a sub-network belongs to all have the same locations, so I'm not sure this buys you much.


Is my understanding correct? Is there any value in --oplog option of
  mongodump utility?

As MongoDB Blog documentation here Ensures that mongodump creates a dump of the database that includes a partial oplog containing operations from the duration of the mongodump operation. This oplog produces an effective point-in-time snapshot of the state of a mongod instance. To restore to a specific point-in-time backup, use the output created with this option in conjunction with mongorestore --oplogReplay.
Without --oplog, if there are write operations during the dump operation, the dump will not reflect a single moment in time. Changes made to the database during the update process can affect the output of the backup.

--oplog has no effect when running mongodump against a mongos instance to dump the entire contents of a sharded cluster. However, you can use --oplog to dump individual shards.

--oplog only works against nodes that maintain an oplog. This includes all members of a replica set, as well as master nodes in master/slave replication deployments.

--oplog does not dump the oplog collection.

  I understand that the recommended way to backup MongoDB data is to use
  file system level tools. However, mongodump utility is promoted as a
  valid alternative for smaller instances, when file system snapshots
  are not available for some reason. Many resources recommend running it
  with --oplog option to enable point-in-time restores.

You could use oplog option if you are taking backups of all the DBs.

mongodump  --oplog --out /dump_location

Now while restoring do something like this :

mongorestore --oplogReplay

This way mongodump won't require a write lock. Any writes that are made during the backup process will be written to oplog.bson. While restoring, mongorestore will use this oplog to include the write operations.

For your further ref here, here and here


The problem here is that it thinks to_tsvector is a very cheap operation to perform, even when performing it on an entire table.  Basically, it think it is the same cost as adding together two integers.  But of course it is not.  In the upcoming release 9.5, the default cost for that function has been increased 100-fold (which is probably still not enough to be accurate...but it should be enough to tip the scales for your case, which is all you need).

You can change the cost of that function yourself:

alter function to_tsvector ( regconfig, text) cost 1000;

(Note that because this is a built-in function, changes you make will not survive a pg_upgrade, or a dump, initdb and restore cycle)

This is much safer than fiddling around with the global cost parameters.

The general (global) cost parameters are described here, and ones for specific functions are described here.  The way the two interact with each other don't seem to be well-described anywhere in the docs.


You need to define the column that denotes the desired order of multiplication. I'm not sure if that's what column2 is supposed to be in your example data and have just used a column called id for that purpose below.

Assuming your RDBMS supports window functions then you can use the idea from here.

The below uses some non standard TSQL functions you will need to translate into the dialect of SQL that your implementation is actually using (if you are not in fact using SQL Server)

Online Demo

SELECT CASE
         WHEN Min(Abs(Column1))
                OVER (
                  ORDER BY id) = 0 THEN 0
         ELSE CASE
                WHEN Sum(Sign(CASE
                                WHEN Column1 < 0 THEN 1
                                ELSE 0
                              END))
                       OVER (
                         ORDER BY id) % 2 = 1 THEN -1
                ELSE 1
              END * Exp(Sum(Log(Abs(NULLIF(Column1, 0))))
                          OVER (
                            ORDER BY id))
       END
FROM   YourTable


Locking And Blocking With Merge Replication

What is occurring here is not what it appeared to be. The stored procedure to write into my top level tables were using transactions.

It was the transactions that were causing heaps of extra locks. It worked fine once those transactions were removed.