Without knowing more about your data I'm not sure about your table structure, but I will say that GUIDs/uniqueidentifiers are controversial for clustered indexes.  Traditional logic says that your clustered index should be unique, small, and sequential.  GUIDs are unique, large, and random.  (There is a newsequentialID() function that you could use as a default value to get around the random bit, however.)

An article against random GUIDs as PK/clustering keys:  http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/  Summary:  Your data can end up a scrambled mess, and selects of ranges that should be next to each other can be slowed down.  Also, it takes up too much space in large tables and indexes.

An article for random GUIDs as PK/clustering keys:  http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/  Summary:  If you have a LOT of inserts, it might be faster to spread them out instead of having them all be "at the end."

Personally, I think that if you have a specific business/logic reason to use GUIDs, then use them.  Otherwise, you might want to consider using an autonumber column instead.


Is it best practice to install client tools (like Oracle SQL Developer) on the database server or another machine?

Best practice would be to install and use it from client machines, since every tool running on the server is consuming some resources which will not be available to the Oracle server

Installing it on the server -- probably no penalty as pointed out in the comments


The backup set holds a backup of a database other than the existing

This is a type of safety check done by SQL Server so you don't by mistake overwrite the wrong database.
I believe it includes things such as that the destination database exists and the name of the destination database isn't the same as the name of the database you have in the backup. Possibly a few more things.
So double check and triple check that you connected to the right SQL server etc.
But the REPLACE option should override that safety check, that is why it exists! So make sure that you are 100% certain that you executed with the replace option.
You can of course first delete the destination database since the RESTORE will (internally) do it for you anyhow, and then do the restore.


Choosing sort and distkeys for redshift with multiple joins

The ideal situation is if you can have a user_id column added onto subscriptions, then you can distribute all three by user_id. Then your joins would be written as:

FROM subscriptions
LEFT JOIN accounts
  ON  accounts.id = subscriptions.account_id
  AND accounts.user_id = subscriptions.user_id --Note this add'l condition
LEFT JOIN users
  ON  users.id=accounts.user_id

The one caveat here is that your number of users should be large enough to ensure that this distribution key does not result in significant skew.

As a bonus, if you do not frequently filter these, you can choose the same column (user_id) as your sort key to enable merge joins rather than hash joins

If you can't add this column, then you are going to have network distribution happening in one of your two joins :-( Pick the one that you want based on frequency of that join and average width of rows participating in that join.


How to access a shared folder from Windows Cluster IP

I figured it out. The problem was we mounted and unmounted the drives initially and that share is only created once automatically. You have to create it manually after the fact.


SSIS package hits "login failed for user" when simultaneous operations occur against the same target database

Here it is:  the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments:

ServerName - MyServer
InitialCatalog - MyDB
UserName - MyUser
Password - ***********
ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

While this worked perfectly on the old SQL 2008 environment, and it was no problem from Visual Studio, it failed during simultaneous database operations from the SQL 2012 Integration Services Catalog, but started working when we added Password=****** to the connection string.

ConnectionString - Data Source=MyServer;User Id=MyUser;Password=********;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

It would seem that if a second simultaneous connection is being opened while in the SQL 2012 Integration Services Catalog execution environment, only the connection string itself (of the initial connection?) is consulted, not Password or other constituent properties of the connection manager.  Therefore, Password has to be in the connection string.


How to select date of only today from datetime column in mysql

Date format is YYYY-MM-DD

SET @dt = '2017-01-03 00:00:00';
SELECT * FROM some_table
WHERE datefield >= @dt AND datefield < (@dt + INTERVAL 1 DAY);


SQL Server End to End Encryption Options

You are correct; SSL is the official means to support encryption for data in motion.

  Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt
  data that is transmitted across a network between an instance of SQL
  Server and a client application. The SSL encryption is performed
  within the protocol layer (technet)

Always Encrypted is a bit deeper. It encrypts the data at the client application before it is transmitted, and never stores it in any unencrypted way. So the data that's encrypted via Always Encrypted is "safe" while in transit and at rest (stored as encrypted data), but the packets themselves are NOT encrypted at the network level. (MS Docs)

So if you want to prevent your DBAs and/or report writers from viewing cleartext data (such as credit card numbers or social security numbers), then Always Encrypted is a possible tool to get that level of security.

But if you don't want a packet monitoring tool to see your database username and password, or to see cleartext data as it moves from DB to application, then you must use SSL.


How can I run multiple concurrent versions of my application against the same database?

Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'

You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END

Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.


InfluxDB - How can I limit the measures that a user can read by their tags?

First and foremost, I would suggest moving away from any version of InfluxDB before 1.0 as there have been a number of improvements to InfluxDB since 0.9.

At the moment there is no way to limit read and write access for a particular tag. This functionality is a planned feature in version 1.2 of Enterprise InfluxDB.

That being said there is one alternative implementation that I see as feasible. OSS InfluxDB allows administrations to set database level read and write permissions. You could use this to your advantage. Rather than have a single database and setting permissions on the tags within that database, you'd have a database for each user and give each user read and write access to that database.

One issue that comes to mind with this approach is that if you have 1000s of users, you'll end up having 1000s of databases. This may cause some weirdness within InfluxDB itself. If you did have 1000s of users, you could simply partition your users across multiple InfluxDB instances.


Database Mail works for one Instance but not three others

I'm not sure what the actually issue here was but after dropping the account and profile and re-creating them everything worked.

Looking back, I realize that I had set it up on the first instance with the GUI, and then scripted it out and ran the script for the other instances. Why that didn't work I'm not sure.

A big thank you to everyone who tried to help me.


Update table from a join which returns multiple rows for each result

Combine the rows of B (GROUP BY a_fk); in the process decide whether to keep Y or NULL.
Store the result of that into a temp table.
Then do the original UPDATE, but against the temp table.
So two statements, no looping:
CREATE TEMPORARY TABLE t
    SELECT ...
        GROUP BY a_fk;
UPDATE A JOIN t ON ...
    SET A... = t...;


Create one or more new tables that capture the data in event_rules in a more useable form.  Then materialize those tables from the existing table.    Without more details, I can't say exactly what those tables should look like.  But here are two design problems that ought to be solved:

The column for allowable values violates first normal form, because it contains a list of values.  Decompose the tables in order to achieve first normal form, and create the right indexes to speed up the join that will be required to recompose the data.

The column for target table contains metadata.  In general, mixing metadata with data creates a confusing mess, unless you really know what you aere doing.  Consider decomposing the event_rules table into separate tables for each target table.  This looks complicated, but the resulting queries will be much much simpler, and probably faster.


First of all, Welcome to dba.stackexchange.com and thanks for your post !!

Is it possible to limit what values are allowed in a column based off of other values in the row.

Yes using CHECK CONSTRAINTS as described here

Example :

create table myTable (ID int identity(1,1)
                        , Test_mode int
                        , Active int
                        )
go

-- Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.
ALTER TABLE myTable WITH CHECK ADD
   CONSTRAINT ck_active CHECK (active IS NULL OR active IN (1, 0))
   go

-- some test data
insert into myTable (test_mode, Active) values (1, null)
insert into myTable (test_mode, Active) values (0, null)
insert into myTable (test_mode, Active) values (1, 0)
insert into myTable (test_mode, Active) values (0, 1)
insert into myTable (test_mode, Active) values (1, 1)

select * from myTable

-- Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

update myTable
set Test_mode = case when Active = 1 then  0
        else Test_mode
        end
where Active = 1

If Test_mode is 1 not allow insertion/update of Active --OR--
Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Use TRY/CATCH as described here


What units used in DBA_TABLESPACE_USAGE_METRICS?

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE.

EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example:

SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status ;

TABLESPACE_NAME                SUM(BLOCKS) STATUS
------------------------------ ----------- ---------
UNDOTBS1                               128 ACTIVE
UNDOTBS1                              5312 UNEXPIRED
UNDOTBS1                              8960 EXPIRED

SQL> select USED_SPACE from DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME='UNDOTBS1';

USED_SPACE
----------
      2864

SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


How do I prevent a massive SELECT from blocking other statements?

Your main options are:

Tune the SELECT query so it uses fewer I/O resources.
Run the query at a quiet time.
Run the query on a separate copy of the database (e.g. a readable secondary).
Run the query in an I/O-limited resource pool as described here*.
Move to a size/tier with higher I/O capacity.

Side note: This answer does not mention using isolation levels like RCSI or SI. Those apply to lock blocking, and the question contains no reference to that: PAGEIOLATCH_XX waits are associated with waits on physical I/O.

*This feature may not be available in Azure SQL Database.


Display Total under name column and sum of amount under sal column

You could use Grouping Sets:

select empno
     , coalesce(ename, 'Total') as ename
     , sum(sal) as sall
     , hiredate
from table1
group by grouping sets ((empno, ename, hiredate),());

If ename is nullable this is not sufficient since ename might be null for other reasons than grouping. In that case you can use the GROUPING function:

select empno
     , case when GROUPING(ename) = 1
            then 'Total'
            else ename
       end as ename
     , sum(sal) as sall
     , hiredate
from table1
group by grouping sets ((empno, ename, hiredate),());

But I agree with @Mark Sinkinson that it is probably better to do this in the presentation layer.


creating 2 database at the same time in db2

I'm also not quite sure why you are posting this without just attempting it, but yes, this will work without problem.

The only issues you might have are:

If the NUMDB database manager configuration parameter is small enough that you can't activate both databases at the same time (in addition to whatever other databases are activated within the DB2 instance).
If you use the AUTOCONFIGURE option for CREATE DATABASE with some silly default values so you exhaust system memory
System resource contention (disk, CPU) slowing the process down.

This is really an odd question – it's hard to image a scenario where you would need to create multiple databases simultaneously.  Are you coming from a different DBMS (like MySQL or SQL Server), where the concept of a database is more like a schema in DB2?


I tend to be a little conservative and cautious when using FULLTEXT indexes because FULLTEXT indexes has a nasty habit of playing head games with the MySQL Query Optimizer.

Try getiing all monkey rows first in a subquery, and then eliminate "monkey business":

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('+monkey' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey business',title) = 0;

You could also do the reverse by getting all rows that do not have "monkey business" and then scan for any rows with monkey:

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('-"monkey business"' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey',title) > 0;

The second query would make the temp table from the subquery much larger and would take longer to scan for the outside WHERE clause. Go with the first query.

Give it a Try !!!


Automating Redshift data dump to a PostgreSQL DB every hour

Your best bet is probably to use the dblink feature of PostgreSQL to create a connection to Redshift and query the necessary data from Redshift to add to your PostgreSQL instance.

Here is an example using RDS PostgreSQL, but I think you would probably be able to get this to work with an EC2 hosted PostgreSQL instance as well:

https://aws.amazon.com/blogs/big-data/join-amazon-redshift-and-amazon-rds-postgresql-with-dblink/

Note the use of pgbouncer-rr in the example is optional, you will probably not need this.


need information on a deleted SSRS report

If you need to get the information restore a backup taken prior to removing the report into a different environment and get it from there.


Find all my.ini file
Replace no-beep from everywhere (not only client) 'coz there is no such option.
But to do a temporary fix you can use no-defaults as follows:

"C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --no-defaults -u root -p******* my_database > backups/my_database_backup_20151221121320.sql


In PostgreSQL, if crashes during VACUUM, will it recover from a checkpoint that is before the frozen transaction ID of VACUUM?

It could well be that the freeze limit is younger than the latest checkpoint, if you set vacuum_freeze_min_age low enough, but that is no problem.
Tuples are either frozen (if they are all-visible and older than vacuum_freeze_min_age transactions) or removed (if they are "dead": invisible to everyone and no longer needed). Dead tuples are never frozen.
During crash recovery, all actions by VACUUM are replayed (with the possible exception of hint bits), so when recovery is done, all dead tuples are sure to be gone, and all frozen tuples will be frozen again.
Nobody can connect to the database before crash recovery is finished, so there is no danger of anybody seeing inconsistent data.


I don't know about mysql/mariadb, but a postgresql DB allows you to store arrays.  An array of 86400 sensorValues would allow you to have one record per entry_id, dim_id,  dim_date.

create table sensorvalues (
  entry_id serial primary key,
  dim_id int,
  dim_date date,
  sensorvalue int[86400]
);


Strategies for summary data in parent record based on detail data?

I would suggest doing it in the application rather than in the database with a trigger, only because it's not integral to the structure of the database; rather it's for performance reasons and I don't think doing it in the database will actually improve performance of the query that's running (it might slightly improve performance of the actual insert/update statements, but that's something that profiling could tell you better than I).

Doing it in the application would also give you more control of performing this operation optionally. It sounds like you would want this to always happen, but I have seen situations where testing would have been made easier if such logic were moved out of triggers so they could be turned on and off for testing purposes.


Slow query in production, bad execution plan or bad index?

Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan.

You're alerted of a slow query that was previously fast

Bad times, I empathize!

You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help

Usually, clearing the cache fixes problems like this if the problem is parameter sniffing.

You drop and recreate a nonclustered index that's being used by the bad plan.  Now your problem is solved

Another reason for bad plans is bad stats.  If the problem is bad stats, then clearing the procedure cache will just result in the same bad execution plan being produced again (based on the bad stats).

Creating an index on a column creates new statistics on that column WITH FULLSCAN:

  Compute statistics by scanning all rows in the table or indexed view.

These are usually the best stats, because they include the most comprehensive view of the data.

Statistics can be automatically updated over time, as rows in the table are changed / added / deleted.  SQL Server will use different "sampling percents" based on the size of the table.  From that same docs page:

  For most workloads, a full scan is not required, and default sampling is adequate.
  However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan.

It sounds like what happened was:

an automatic stats update ran for this index, with a low or otherwise problematic sample rate
this triggered the creation of a new execution plan, and that plan was bad
you re-created the index, which produces good stats again

You mentioned you're using Ola's index and stats maintenance solution.  You may want to add a special case for this table / index to update stats with FULLSCAN, as it sounds like the table's data is skewed enough to warrant that.

If you want to avoid scanning the whole table (I don't know how big it is), you can experiment with different sample percentages (starting at 100, and dropping by 5 or 10 percent until you get the bad plan).

Looking the execution plans you provided (thanks!), you can see evidence for my theory.  Here's the index for the bad plan:

<StatisticsInfo Database="[BDM]" Schema="[dbo]" Table="
                [SubTypeInstanceProcessAdvancements]" Statistics="[AsOfDate]"
                ModificationCount="288" SamplingPercent="23.4265"
                LastUpdate="2019-08-15T22:00:09.91" />

And here's the good plan:

<StatisticsInfo Database="[BDM]" Schema="[dbo]" Table="
                [SubTypeInstanceProcessAdvancements]" Statistics="[AsOfDate]"
                ModificationCount="0" SamplingPercent="100"
                LastUpdate="2019-08-16T13:50:10.43" />

The bad plan has a sample percent of 23.4265, while the good plan had stats sampled at 100% (because of the index recreation).


is it possible to use AAG with two 2-Node Clusters across two datacenters?

Simply put, if you have, or can create, a Windows Server Failover Cluster setup across the data centres as a stretch cluster, then there is no reason why this is won't be possible. The fact the DC's are geographically dispersed is irrelevant. Good networking can bring them together as if they were not.

You could also do this as a SQL Cluster, with replication across the disks, and the disks presented to the cluster as a shared cluster resource.

I think you need to read up on the Pro's and Con's of each solution, and maybe speak to a Windows Server and/or Networking professional.


Cannot access Properties page of a database

You could probably have just set the database owner to [sa] (or any valid login really) rather than detach / re-attach.

ALTER AUTHORIZATION ON DATABASE::dbname TO [sa];

This probably happened because the login responsible for creating, attaching or restoring the database initially no longer exists. The UI isn't smart enough to fail gracefully when there isn't a valid owner.


MySQL got signal 11 error and slave is down

The reason for this ? Please note the following diagram of the InnoDB Architecture

From this diagram, you can see an insert buffer in the InnoDB Buffer Pool and a connection to the insert buffer in ibdata1. This thread is known as the Master Thread. In its current design, InnoDB can only have one Master Thread per MySQL Instance.

Apparently, given the messages above, mysqld is trying to start and another mysqld process is already running. You need to go to the OS do the following:

Locate the mysqld_safe process and kill it
Locate the mysqld process and kill it
Run service mysql start
If mysqld complains about the PID file already existing, delete the PID file and try to start mysql again.


SQL SSRS and Default Values Stored Procedure

You could handle nulls inside the stored procedure with an isnull() like this:

create procedure rpt.FinanceReport
    @AccountYearParam int
as

set @AccountYearParam = isnull(@AccountYearParam, 2018)
--
select * from FinanceTable where AccountYear = @AccountYearParam

and if you want to get really fancy, you can use this (it will return the current year):

create procedure rpt.FinanceReport
    @AccountYearParam int
as

set @AccountYearParam = isnull(@AccountYearParam, datepart(year, getdate()))
--
select * from FinanceTable where AccountYear = @AccountYearParam

You can also handle it in the where clause, although this method might be considerably slower:

create procedure rpt.FinanceReport
    @AccountYearParam int
as
--
select * from FinanceTable where AccountYear = isnull(@AccountYearParam, datepart(year, getdate()))


How do you block ad-hoc distributed queries from specific sql server instances?

It's a simple setting for sp_configure (that should work for all newer versions of SQL Server - 2005, 2008, 2008 R2):

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

This will enable the use of OPENROWSET and OPENDATASOURCE.


Best Innodb Buffer Pool Size for 8GB RAM?

I have four(4) old posts to demonstrate how to measure and size up the InnoDB Buffer Pool based on the amount of RAM on the DB Server and how much space is currently being taken up by data/index pages:

Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Oct 22, 2012 : How much memory do I need for innodb buffer pool?
Oct 22, 1012 : How large should be mysql innodb_buffer_pool_size?
Apr 08, 2013 : MySQL performance impact of increasing innodb_buffer_pool_size

Please look over these posts, run the queries, and use good judgment. Why did I say to use good judgment ? If the ideal Buffer Pool Size is greater than 6GB (75% of your DB Server's RAM), the highest innodb_buffer_pool_size permissible would be 6G.

Give it a Try !!!


It is possible to export a MySQL database without BLOB fields?

mysqldbexport utility does this, I have myself not tried though,
It has --skip-blobs which prevents exporting of blob data.
For more information refer this:

http://dev.mysql.com/doc/workbench/en/mysqldbexport.html


SQL script to count rows for all views in a database with SELECT-only access

A few improvements.

QUOTENAME() to thwart bad namers
add schema name, since schema is important
use sys.views instead of sys.objects (and remove type from output)
limit sys.partitions to index_id 0 or 1, to prevent double-counting in the case of an indexed view with additional non-clustered indexes (not sure what you think AVG will accomplish - what if there is an indexed view with a non-clustered filtered index?
use dynamic SQL to avoid manual copy/ manual paste/ manual massage/ manual run

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL
SELECT '
  + 'N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N''',
  FromMetadata = N' + CONVERT(varchar(11),COALESCE(SUM(p.rows),0)) + N',
  TheHarderWay = COUNT(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) +N'
'
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.schema_id = s.schema_id
LEFT OUTER JOIN sys.partitions AS p
ON v.object_id = p.object_id
AND p.index_id IN (0,1)
GROUP BY s.name, v.name
ORDER BY s.name, v.name;

SET @sql = STUFF(@sql, 1, CHARINDEX(N'SELECT', @sql)-1, N'');

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Note that FromMetadata will be 0 unless the view is indexed.


mysql: how to copy database and required dependencies

Users and grants are stored in the mysql database, so you could insert rows directly. MySQL Workbench includes the mysqluserclone utility, which provides a safer way to copy user accounts and permissions from one server to another.

I don't know of any direct way to check dependencies, but INFORMATION_SCHEMA contains all the necessary metadata. Other utilities from MySQL Workbench such as mysqldiff and mysqldbcompare would probably help in determining when something is missing.


Is it recommended to monthly planned restart MongoDB production server to clean non-mapped virtual memory?

Is it recommended to do a planned restart every month in order to clean the non-mapped virtual memory and to avoid server crashes?

No, scheduled restarts are not a recommended practice. You should investigate and address the source of your issue rather than restarting your server periodically.

If your deployment is correctly configured, MongoDB shouldn't crash when out of memory. Restarting your mongod processes will temporarily free up memory, but it is expected that a database server will try to take advantage of available memory as needed for your workload.

If you are running Linux, one likely cause for unexpected process shutdown is the Out-of-Memory (OOM) Killer which looks for processes using a lot of memory (like a database server) and shuts them down if there is insufficient memory or swap space. If the OOM Killer decides to kill mongod, the telltale log message is normally in your system logs  -- try grepping for "oom-killer" or "killed process" in /var/log/messages. See Taming the OOM Killer for more background if this sounds potentially applicable to your environment.

If your overall data size is 5.5TB, I assume there is also general contention for server resources unless you have a relatively small working set or very large host instances.

One source of non-mapped memory usage is your current connection count. mongod allocates up to 1MB per connection, so 10,000+ connections is 10GB+ of RAM. Other consumers of RAM are server-side processing such as aggregation queries, in-memory sorts, Map/Reduce, and JavaScript evaluation. Allocated non-mapped virtual memory or memory outside of the WiredTiger cache will normally be freed when no longer needed

The MongoDB Production Notes and Operations Checklist include some advice that is worth reviewing for applicability to your environment.


Log report 'bad password', but still grants access

Alright, I have a resolution maybe, and an explanation for the log entries.
Following the advise of @John-Eisbrener & @Danielle-Paquette-Harvey (did I do the attribution right?) In particular John's advise of a different user for Crystal I learned the following.

Issue: Log entries that say that the user (sa originally) has entered bad credentials.
Discovery: When a Crystal Report 'template' is opened, there is no log entry.
When the report is 'executed', a dialog box is presented with the users name pre-populated.
Behind the scenes, Crystal apparently sends a request to the OBDC connector with the username that the report database was connected to, without a password, which (of course) fails.
This is the source of the non-fatal, 'credential failure', log entry.
[This was reported (tangentially) here: I am getting a failed login message in the SQL Server error logs however, nothing actually failed? four years ago!]
Note that if a report has five 'template pages', for say an end-of-month report; five attempts rapid fire, and can cause a user lockout unless 'enforcement policy' is loosened! Thus my first problem.
At this point the user enters the (presumably) correct credentials and the report queries the database and populates the report.
If the user has an open instance (say Sage300, with credentials); then the report is generated without a credential dialog box or an failed password in the logs.

So, I will call the mystery log entries 'benign', and close that issue. I will (of course) take the advise for further lock-down of my systems. I may be small, but that does not absolve me of 'best practices'. (Read: Bullseye Breach -G.Scott)
Thanks again to the community. -30-


How to create automatic partitions on a non-time based column in Postgres?

There is nothing built into Postgres to do "automatic partition creation", so you either need to write a little script that automates that process or use a tool like pg_partman if it supports that.
In psql you could use a query like this to generate the create table statements and then run it using \g:
select format('create table %I partition of my_table for values with (modulus 10, remainder %s);', 'my_table_p'||to_char(g.i, 'FM00'), g.i)
from generate_series(0,9) as g(i)
\g

Otherwise just use a do block:
do
$$
declare
  l_sql text;
begin
  for i in 0..9 loop
    l_sql := format('create table %I partition of my_table for values with (modulus 10, remainder %s)', 'my_table_p'||to_char(i, 'FM00'), i);
    execute l_sql;
  end loop;
end;
$$
;

Not tested!
Of course you can put the PL/pgSQL loop into a function or procedure and call it with the number of partitions you want.


Would it be possible/wise to execute 2 parallel restores from a single backup?

I am pretty sure that I have done this, though I didn't think twice about it at the time.

You definitely can restore a bak file using a UNC path. Mapping drive letters is to be avoided because the mapping must be done in the context of the SQL service account. Most people map the drive under their own account and are frustrated when the restore command doesn't work as they expect.

Naturally, there will be a little more load on the NIC on the server that holds the BAK file and there could be some contention if the database mdf and ldf files are on the same SAN, but these are probably minor problems, if you notice them at all.


Is there a need to keep refreshing the indexes from time-to-time?

Indexes are automatically updated regarding the what is stored in them (the column values of the rows that are indexed).

However some DBMS require regular maintenance (aka "rebuild") of them in order to optimize the storage of the index values.

Older versions of Microsoft SQL Server required an index rebuild on a regular basis to keep them effecient. But I don't know if that is still true with newer versions (2005,2008)

There is no need to recreate indexes in Oracle.

The (auto)vacuum process of PostgreSQL should take care of reclaiming space, but they sometimes (rarely though) require a rebuild as well.


Transforming multi column table to different structure table

As you have a large number of sensors, I would probably create a mapping table.  Here's a fuller demo using UNPIVOT, eg

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Sensors') IS NOT NULL DROP TABLE dbo.Sensors
IF OBJECT_ID('dbo.SensorValues') IS NOT NULL DROP TABLE dbo.SensorValues
IF OBJECT_ID('dbo.SensorValuesNormalized') IS NOT NULL DROP TABLE dbo.SensorValuesNormalized
GO

CREATE TABLE dbo.Sensors(
    sensorId    SMALLINT PRIMARY KEY,
    sensorName  VARCHAR(20) UNIQUE NOT NULL,
    dateAdded   DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE dbo.SensorValues(
    [timestamp] DATETIME PRIMARY KEY,
    Sensor1     FLOAT NULL,
    Sensor2     FLOAT NULL,
    Sensor3     FLOAT NULL
)
GO

CREATE TABLE dbo.SensorValuesNormalized(
    Id          SMALLINT NOT NULL,
    [timestamp] DATETIME2(3) NOT NULL,
    Value       FLOAT NOT NULL,

    PRIMARY KEY ( [timestamp], id )
)
GO

-- Populate sensor mapping table
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensors ( sensorId, sensorName )
SELECT rn, 'Sensor' + CAST( rn AS VARCHAR(5) )
FROM cte
GO

-- Create some dummy sensor data for testing
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.sensorValues ( [timestamp],  Sensor1, Sensor2, Sensor3 )
SELECT DATEADD( second, rn, '1 Sep 2014' ), RAND() * rn, RAND() * rn * rn, RAND() * rn * rn * rn
FROM cte
GO

-- Remove a few nulls
UPDATE TOP(100) dbo.sensorValues
SET Sensor3 = NULL

UPDATE TOP(10) dbo.sensorValues
SET Sensor2 = NULL

INSERT INTO dbo.SensorValuesNormalized( id, [timestamp], value )
SELECT s.sensorId, x.[timestamp], x.value
FROM
    (
    SELECT
        sourceColumn, [timestamp], value
    FROM SensorValues sv
    UNPIVOT ( value for sourceColumn In ( Sensor1, Sensor2, Sensor3 ) ) upvt
    ) x
    INNER JOIN dbo.Sensors s ON x.sourceColumn = s.sensorName
GO

SELECT *
FROM sensorValues
WHERE [timestamp] = '2014-09-01 00:00:01.000'

SELECT * FROM dbo.SensorValuesNormalized
ORDER BY [timestamp], id

NB, the UNPIVOT will remove NULLs.


Painless way to create a clustered index on a huge table?

If your sql server is Enterprise+ edition and table has no any BLOB fields - CREATE CLUSTERED INDEX ... WITH(ONLINE=ON)
If not - there is no any way except to create the table with the same schema on side and wisely transfer data to it, including all INSERT/UPDATE/DELETE operations (using trigger, for example), and then accurately drop old table and rename new to the same name as old - cons are: slow, adds extra load to server and storage


This is bug in Workbench. We have bug report for that start/stop issue. This problem was caused by migration from python2.7 to python3 and only affect last WB release. We working on that and it is already partially fixed.


Can I iterate a value depending if the previous is what I want?

For Postgres 9.4+:

UPDATE trajs t
SET    traj_id = upd.traj_id
FROM  (
   SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min')
                           OVER (PARTITION BY obj_id ORDER BY t) AS traj_id
   FROM  (
      SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0
      FROM   trajs
      ) sub
   ) upd
WHERE t.id = upd.id
AND   t.traj_id IS DISTINCT FROM upd.traj_id;

Increases traj_id only if the previous row is 6 minutes or more earlier - exactly like requested.

About the last condition I added to optimize performance (last paragraph):

How do I (or can I) SELECT DISTINCT on multiple columns?

The way I formulated it, it also works for the corner case of the first row per obj_id, where lag() returns NULL.

About the aggregate FILTER I combined with the second window function:

Return counts for multiple ranges in a single SELECT statement


Put simply you should design your database so that all information is stored with a its natural level.

In your case, I would create a table Patients. Each row in that table should store all the pieces of information that are relevant to that patient and not dependent on anything else. The other tables then follow on from this. For instance, PatientVisits, the unique key would be the identifier of the visit; but you need to know what patient this visit relates to so you should also have a foreign key back into Patients. You schema then looks something like this:

Patients

Id - primary key, auto-increment as there's no natural key
Phone number, name, gender, date of birth.

Never store age, you have to re-calculate it each and every day. Store the date of birth instead so that you can easily work out the current age.
ImageFindings

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
created, comments, lesion size, site, procedure

I've renamed date to created as it's a reserved word and really quite confusing to use in a database. I doubt this should be the primary key as it's possible to insert two simultaneously
LabFindings

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
test - 1, 2, 3 etc -- foreign key to the primary key of Tests
test result
created

If a Image is dependent on a LabFinding then you should change the foreign key in ImageFindings to be the primary key of LabFindings. As you might have more than 1 test these should be split out into rows rather than columns as it might be possible for you to add more tests.
Treatment

Id - primary key, auto-increment as there's no natural key
patient id -- foreign key to the primary key of Patients
more columns

Tests

Id - primary key, auto-increment as there's no natural key
Description
More data related to the test

You note that the primary key of every table is a surrogate key. This is because there is no natural key, i.e. one that should obviously be the primary key of the table. You ask what the primary key of PatientBasicInformation (Patients) should be; it can't be anything but a surrogate key as you can't rely that you will never have a patient with the same name as another, or the same name and date of birth etc. The naming conventions of people are inherently chaotic and so you can't rely that people will nicely fit into your database.

I would strongly suggest reading up about the Third Normal Form and trying to understand it.


Why is MySQL order by performance poor within a single partition of a partitioned table?

Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:

EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...

The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.

If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like

SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...

But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.


what should be the good normalization for the following scenario?

What is it we are trying to get to here? Do you want to list out all the outfits (combinations of shirts, belts, and pants) a user can wear? Or just what they have worn for a particular day? Sorry, I'm not following the needs for the three relational tables (user_shirts, user_shirts_pants, user_shirts_pants_belts).
I would just have one table for each item (with pkid and description of item), then a table that has a separate foreign key to those item tables:

users (id int, name varchar(100))
shirts (id int, desc varchar(100))
pants (id int, desc varchar(100))
belts (id int, desc varchar(100))
user_outfits (id int, user_id int, shirt_id int, pant_id int, belt_id int, dt date)
Then you could enter any item they wore or null if they didn't wear that item.


Checking existence and syncing data with millions of rows

I would not design a system to do lookups on the fly. Instead I would design the system to validate if a given number is on the DND list at the time the number is added to your system, and weekly (right after the DND list is updated). Then you don't need to check the number each time you send an SMS.

If you follow that design pattern, you'll only need to interact with the DND table very infrequently, and at times that don't impact the speed of sending SMS messages.

If I was doing this, I'd likely use Microsoft SQL Server, and I'd consider a design similar to the one below.  It should be trivial to adapt these SQL statements into whatever flavor of SQL you want to use.

The table to hold the DND rows:

CREATE TABLE dbo.DND
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DND
        PRIMARY KEY CLUSTERED
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

The table to hold your subscribers phone numbers:

CREATE TABLE dbo.Subscribers
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_Subscribers
        PRIMARY KEY CLUSTERED
    , IsOnDND BIT NOT NULL
    , DNDLastChecked DATETIME NOT NULL
);

This query could be used to update the Subscribers table weekly, right after the DND list is updated.

UPDATE dbo.Subscribers
SET IsOnDND = 1
    , DNDLastChecked = GETDATE()
WHERE EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE DND.TelephoneNo = Subscribers.TelephoneNo
    );

I would handle updating the DND list from the weekly change list using a scheduled SQL Server Agent Job.

The table to hold the weekly changes might look like:

CREATE TABLE dbo.DNDChanges
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DNDChanges
        PRIMARY KEY CLUSTERED
    , Opstype CHAR(1) NOT NULL
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

The scheduled job would perform the following steps:

TRUNCATE TABLE dbo.DNDChanges;

--insert rows into the DNDChanges table using SSIS, BCP, etc

/*
    Here we remove rows from the DND table where the number has
    been deleted (Opstype = 'D')
*/
DELETE
FROM dbo.DND
WHERE EXISTS (
    SELECT 1
    FROM dbo.DNDChanges
    WHERE DNDChanges.Opstype = 'D'
        AND DNDChanges.TelephoneNo = DND.TelephoneNo
    );

/*
    This updates the PhoneType, and ServiceAreaCode from the DND
    incremental change list.
*/
UPDATE dbo.DND
SET DND.PhoneType = DNDChanges.PhoneType
    , DND.ServiceAreaCode = DNDChanges.ServiceAreaCode
    , DND.TelephoneNo = DNDChanges.TelephoneNo
FROM dbo.DND
    INNER JOIN dbo.DNDChanges ON DND.TelephoneNo = DNDChanges.TelephoneNo;
    /* no need to add OPSType = 'A' to the WHERE clause
       since we've already removed those rows from the
       DND table */

/*
    Add new rows from the DND incremental change list
*/
INSERT INTO dbo.DND (
    TelephoneNo
    , PhoneType
    , ServiceAreaCode
    )
SELECT *
FROM dbo.DNDChanges
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE dbo.DND.TelephoneNo = DNDChanges.TelephoneNo
    );


Mongo db custom role with different privilages on differenct collections

The only solution is to move that col1 to different DB, but then your application must, of course, read two different databases.


Transaction atomicity implies consistency

This is not true. A transaction that adds an order for a nonexistent customer is atomic, but makes data inconsistent.

To follow your example, a consistent bank transaction must guarantee that the sum of all debits and credits is exactly 0. An atomic transaction must only guarantee that either all necessary tables, or none, are updated, but says nothing about the values they are updated with.

Atomicity is one of tools, but not the only one, used to ensure data consistency.


MySQL - How to convert an existing int column with duplicates to auto-increment?

Let's assume I have the following table (id is the row that we want to be autoinc, and name, all other columns):

MariaDB [test]> CREATE TABLE without_auto_inc (id int, name text);
Query OK, 0 rows affected (0.013 sec)

Let's add some sample data, with some 0s:

MariaDB [test]> insert into without_auto_inc VALUES (1, 'a'), (0, 'b'), (3, 'c'), (0, 'd'), (0, 'e'), (5, 'f'), (0, 'g'), (100, 'h');                                             Query OK, 8 rows affected (0.001 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM without_auto_inc;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    0 | b    |
|    3 | c    |
|    0 | d    |
|    0 | e    |
|    5 | f    |
|    0 | g    |
|  100 | h    |
+------+------+
8 rows in set (0.001 sec)

We can simply get the max id, reinsert (duplicate) the rows with id = 0 but with an variable incrementing its value for each selected row, and then delete the 0 rows:

MariaDB [test]> BEGIN;
SELECT max(id) INTO @id FROM without_auto_inc;
INSERT INTO without_auto_inc SELECT @id := @id + 1 as id, name FROM without_auto_inc WHERE id = 0;
DELETE FROM without_auto_inc WHERE id = 0;
COMMIT;
ALTER TABLE without_auto_inc MODIFY id int primary key auto_increment;
Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.034 sec)
Records: 8  Duplicates: 0  Warnings: 0

You can add an ORDER BY TO the select to create the auto-inc in the order you prefer. You can also do the insert from a file if you prefer there is never duplicate rows. The exact method will depend on your constrains- make sure you are not inserting new values while it runs, or you may have undesired results/data loss (e.g. consider blocking the table for new writes). Sadly, most versions of MySQL don't allow transactional ALTERS (as in part of a larger transaction), so it will force an autocommit before running.

This is the state of the table after running:

MariaDB [test]> SELECT * FROM without_auto_inc;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   3 | c    |
|   5 | f    |
| 100 | h    |
| 101 | b    |
| 102 | d    |
| 103 | e    |
| 104 | g    |
+-----+------+
8 rows in set (0.001 sec)


How do I grant the privilege of connecting to SSIS?

I eventually found the solution on MSDN.  To grant users rights to the Integration Services service:

Open Component Services; from a Run dialog, you can enter "dcomcnfg".
On the left-hand tree, navigate to Component Services | Computers | My Computer | DCOM Config.
Find "Microsoft SQL Server Integration Services 12.0".  This is for 2014; I believe earlier versions leave out "Microsoft," so check under "S" if you don't see it under "M".
Open Properties | Security and for each type of permission hit Edit and add an appropriate AD group or user.  Be sure to check the specific permissions required, such as Remote Launch when editing Launch and Activation Permission.  If anyone knows exactly which permissions are necessary for running SSIS, please share.
Launch Services, and restart the SQL Server Integration Services service.

You should now be able to connect to Integration Services from within SQL Studio or dtexec.exe.


How to get the value of column that the same for few rows by selecting values from other columns?

This may work:

SELECT product_attribute.product_id
FROM product_attribute
WHERE product_attribute.text = 'wi-fi'
    OR product_attribute.text = 'bluetooth'
GROUP BY product_attribute.product_id
HAVING COUNT(*) = 2;

The idea here is to use an OR in the WHERE clause to allow rows that have either wi-fi or bluetooth in the text column, then we only return products that have at least two rows.

Thanks to @jkavalik for creating a test sqlfiddle that works, and proves the above code!


How (if at all) does Galera enforce authentication for SST via rsync when adding a node?

The cluster simply doesn't know. It'll send a sst to every node which requests it.


How to send an alert based on SQL Query results

I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

You can do this using a TSQL Task and using sp_send_dbmail as below :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your mail profile',
    @recipients = 'dba@company.com',
    @body = 'results -- some body goes here',
    @subject = 'Sensible subject goes here ',
-- to send file attachments (if any)
    @file_attachments = 'L:\logs.TXT',
  -- send query results
    @query = 'Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory';


Using WITH REPLACE allows you to overwrite the DB without backing up the tail log, which means you can lose commited work.

Additionally, if you use WITH REPLACE you can, and will, overwrite whatever database you are restoring on top of. This means you could restore a database over another database that is used for something completely different, which can be quite dangerous if you aren't careful.

From the docs:

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

For example, you backup Server1.DB1 and then restore it to Server2.DB2 but DB2 in this case is NOT an old restore from DB1. It's actually a completely different database used for something completely different. WITH REPLACE would replace DB2 with DB1 despite them not being the "same" database.


You have two options, both of which will require SQL Server Management Studio to be installed on the host you are running the commands from:

SQLPS
SMO

I lean toward the latter because it includes a bit easier format to learn for basic task such as getting a list of objects in an instance.

With SMO you have to load the appropriate assemblies you want to work with. In most cases Microsoft.sqlserver.management.smo.server will be the most common one used.

With SQLPS you have to know what version of SQL Server you are working with. SQL Server 2008 R2 (and R1) you will add-pssnapin *sql* and SQL Server 2012 and beyond you will import-module SQLPS.

Now to the question at hand of getting a list of databases.

SQLPS method

dir SQLSERVER:\\SQL\ServerName\Default\Databases | select name

The above would be for a default named instance, you would change "default" to your instance name if it was a named instance.

SMO method

$srv = New-Object 'Microsoft.SqlServer.Management.SMO.Server' "myInstance"
$srv.Databases | select name

Now the above code can easily be wrapped into a function or for each loop that would allow you to easily pass in multiple server names.

Edit

Depending on what literature you read on PowerShell you will find that most folks that take time to write out a script, will go ahead and spend the time to make it a repeatable one. This is done either through a function (basic level for me) and then modules (more advanced that I have not touched yet).

So for your example code:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

Your foreach loop is not needed in order to output the list of databases. This is an object within SMO that contains multiple properties, one of which is name and PowerShell will simply output that in the default format, as shown below:

A difference to note between SMO and SQLPS here is that SQLPS would not output the system databases by default, SMO will.

If you look up blog post on PowerShell Profiles a common thing to do is add the assembly command to that so ever time you open your PowerShell command it is already available. I will then add functions that are basically commands I use often, and happen to have one for databases:

function Get-DBList ($server)
{
    $srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
    $srv.Databases | Select name, RecoveryModel,
        @{Label="CompatibilityLevel";Expression={($_.CompatibilityLevel).ToString().Replace("Version", "")}}
} #end Get-DBList

You can modify this to your liking but if I call it using my local instance I get the output below.

I will go ahead and let you know something that I found out the hard way. Using SMO you can connect to SQL Server 2000 up to SQL Server 2014, however, each version of SQL Server can vary on where particular properties are located. I had a learning conversation with Jonathan Kehayias on Twitter one afternoon learning this but I cannot recall what property I was looking for. In these situations the .NET class library on MSDN for SMO is your friend because it can help guide you to properties you are looking for.


How to implement on duplicate update for SQL Server?

The MERGE statement, introduced in SQL Server 2008, would be the usual recommendation here. It is a feature that seems quite underused (in my case because until recently I've had to support SQL Server 2005) but pretty powerful.

See:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

There are a number of issues to be careful of though with regard to concurrency (MERGE deadlocking itself(!)), performance, key consistency, and other bugs:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73d2b0de-d6cc-4938-a303-b24d59c1be54/deadlock-on-simple-merge-statement?forum=sqlgetstarted
https://sqlperformance.com/2013/02/t-sql-queries/another-merge-bug

The alternative is to use a multi-step process (delete what is no longer need, update what is already present, insert what is missing) wrapped in a transaction with appropriate settings.


Force SQL Engine to prepare Non-Null plan only for a query that use variable

Following are the three ways that can fix the issue. More approaches are welcome.

Solution 1

Instead of a variable(@DelimitingCharacter), use a temporary table.

CREATE TABLE #VariableHoldingTable (DelimitingCharacter CHAR(1) NOT NULL)
INSERT INTO #VariableHoldingTable
SELECT '<'

--And do a join

  INNER JOIN #VariableHoldingTable V
    ON 1 = 1

Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

Solution 2

CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1nKne9nX

Solution 3

OPTION (RECOMPILE)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=BkRgalqnX


Is there a way to explicitely have a not-null column in a view

From the documentation:

You can specify only unique, primary key, and foreign key constraints
on views. However, you can define the view using the WITH CHECK OPTION
clause, which is equivalent to specifying a check constraint for the
view.

So it seems that there is no way to hint to the RDBMS that the underlying data (after the UNION ALL inline view is created) is NOT NULL.
This all seems a bit silly, given the UNION/UNION ALL/INTERSECT/MINUS statements all require matching datatypes!


I'm not really sure if you have much of an issue, as long as you update the products.stock field every time you either INSERT INTO the purchases or sales tables. No need for a Trigger in this scenario. Also you should ensure your update on the products.stock happens in the same Transaction as your INSERT INTO the purchases and sales tables.
Alternatively you could also design your schema to not store the stock field on the products table. Rather create a View that joins products, purchases, and sales together, grouped on the product and aggregates the difference in purchases and sales per product as its stock, that way it's instantly always up to date whenever the purchases or sales change.


Change SQL Server backup destination while retaining server's knowledge of location

If your retention period for backups is 1 month or something relatively limited, I would suggest the following approach.

Retain the OLD NAS until the expiry of your retention period for those backups.
Create your directory structure on the NEW NAS and redirect your backups there.
Once your retention period on the OLD NAS expires, then drop use of the OLD NAS.

As you delete old backups the SQL Server will still have the OLD NAS path on the backups that went there.  So it will progressively delete those files without any further action from you.

I generally recommend against updating system tables.

When we have to move files, we just move them and cope with an extra script to handle the moved files until they are purged.  If you have a week or two on NEW NAS the likelihood of needing the older backups is small.


PostgreSQL return word after matching first character

Perhaps this will fit your needs?

SELECT regexp_matches('select :barns, :fowl, :cows from farm', '(:[a-zA-Z]*)', 'g');

Cheers!


How to find if the application uses the database snapshots in Management Studio

Sql server 2012 will go out of support next year July 2022.
For more recent sql server edition key points are HA & DR features like AG.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15
Other good point are the index online rebuild feature, but also memory limit, read-ahead, partitions and data compression.


How to add a day/night indicator to a timestamp column?

SELECT record_id, rec_date
     , CASE WHEN rec_date::time <  '08:00' THEN 'Night'
            WHEN rec_date::time >= '20:00' THEN 'Night'
            ELSE 'Day' END AS indicator
FROM  records;

Why?

It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in question.

McNets already shed some light on BETWEEN vs. BETWEEN SYMMETRIC. But BETWEEN SYMMETRIC 20 AND 07 would still be dead ugly, slow, and incorrect, ultimately.

SYMMETRIC only makes sense with parameterized bounds where you don't know which will be greater ahead of time. Not the case, 20 and 07 are constants.
Applied to your case naively, you would get day and night inverted, because BETWEEN SYMMETRIC 20 AND 07 ends up being evaluated as BETWEEN 07 AND 20 (just more expensively).
OK, easily fixed by switching 'Day' and 'Night'. But now, the times 20:** and 07:** would be tagged 'Day'. BETWEEN, with or without SYMMETRIC, includes upper and lower bound. That's why it is almost always the wrong tool to use with timestamps. In this particular case, date_part() happens to counter the built-in issue with including both bounds to some extent. Either way, to match your original intent, it would have to be:

CASE WHEN date_part('hour', ts) BETWEEN '08' AND '19'  -- adjusted
     THEN 'Day' ELSE 'Night' END AS indicator

The query with the expression date_part('hour', rec_date) BETWEEN SYMMETRIC 20 AND 07 is roughly twice as expensive as my suggestion. The larger part due to the pointless SYMMETRIC, the smaller part due to the function being more expensive than the cast.
The suggested expression is much less likely to be misunderstood than the devious BETWEEN, as it makes clear which bounds are included.

Asides

I would not call a timestamp column "record_date", as date is a different basic data type than timestamp. More potential for confusion.

If your actual data type happens to be timestamptz (or, possibly, in any case), you may have to define where in the world it's supposed to be  "day" or "night". See:

Ignoring time zones altogether in Rails and PostgreSQL


Is it possible to have a temporary, on-the-fly PostgreSQL table which only ever lives in RAM?

You might be missing the obvious candidate: a TEMPORARY TABLE.
It lives in memory if enough RAM has been allocated with the temp_buffers setting. Only the current session can see it, no WAL is written for it (which is the main reason why it's faster) and it is dropped at the end of the session automatically.
If it must live past the current session, consider an UNLOGGED TABLE instead.
Be aware that temp tables are not covered by autovacuum. See:

Are regular VACUUM ANALYZE still recommended under 9.1?

But there are still entries in regular system catalogs, so it's not 100 % "in RAM".
Details in the manual for CREATE TABLE.


Handling performance problems with jit in postgres 12

In my opinion changing the default of jit to 'on' in v12 was a mistake, so based on that opinion turning it back off again seems pretty natural to me.  I would have objected to the change if I had been paying more attention at the time (which doesn't mean I would have prevailed, of course).  The change seems to harm at least as many people as it helps.  And the people who were looking forward to the feature could surely be counted on to go change the default.

  The defaults are sane and there is low probability they need to be changed

I think you have far too much respect for the defaults in general. They are basically set for the minimal credible machine size.  Even the settings that were well chosen in general may not be well chosen for you.

  This would also mean throwing away possible performance gains when running long queries.

Do you have example of such a query? Generally by the point the query is long-running enough that jit would make sense, it also starts touching enough data that it is no longer CPU bound and so that using jit or not is relatively unimportant (or it is missing an index, which the fixing of is far more important than jit). This is not universally true of course, and if you have an example you could then possibly use that to target the setting of jit_above_cost.  (One problem with the jit_above* things is that they are based on total cost estimates, which are generally dominated by IO cost estimates.  But choosing to use JIT or not based on estimated IO costs doesn't make sense.  But the infrastructure isn't in place to separate out those types of cost estimates.)


How to split list of values into multiple rows after semi-colon?

Use the SPLIT_STRING function to separate the string on each ';'
Use the REPLACE function to change all '@' to a ';' so this also signals a split.
Prefix the value with '@'
declare @s nvarchar(max)

set @s = '@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly'

SELECT '@' + value
FROM SPLIT_STRING(REPLACE(@string, '@', ';'), ';')


Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

It sounds like you have a slight misunderstanding of how these relationships works.

For the parent table, Customer_Info, your insert statements will look like this:

INSERT INTO Customer_Info
  (Customer_Name, Customer_Phone)
VALUES
  ('csandreas1', '555-5555');

Notice I didn't include the CustomerID field.  As long as you have this set as an IDENTITY field, it will be auto-incremented in the way you describe.

Now here's an insert for the child table, Reservations:

INSERT INTO Reservations
  (RV_DAY, Customer_ID, Table_Arrangement, RV_OT, RV_PW, RV_SIOC)
VALUES
  ('', 5, '', '', '', '');

Note that I left all the other columns blank but Customer_ID, only because I don't really know what they are.

So, when inserting into the Reservations table, you will need to explicitly indicate which Customer_Info row is associated with that reservation.

SQL Server will validate during the insert process that there is really a row in Customer_Info with an ID of 5.

Regarding your code, you will need to execute the insert into Customer_Info first, and then get the resulting generated CustomerID, and then insert into the Reservation table.

You can get the inserted value using the SCOPE_IDENTITY() T-SQL function.  In your example code above, you would replace "50" with "SCOPE_IDENTITY()" to insert the CustomerID for the just-added customer record.

As a VERY IMPORTANT side note regarding your .NET code, you should never concatenate user input with SQL strings.  Use a SqlCommand object and then add parameters.  There are many examples of this online, check one out here.

Doing what you're doing opens you up to the risk of SQL Injection attacks.


In reality, on the modern devices the impact on performance is likely to be minimal.

The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices.  I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk.

Having both indexes and data on the same disk increases disk contention as Oracle can't read both index and data at the same time.

However, in most reasonably specc'd servers these days a 'disk' as far as Oracle is concerned is not necessarily a single disk.  Often they are striped disk arrays.  In this case there often isn't an increase in disk contention as there are multiple physical disks and IO cards in the server and the indexes and data don't always clash anyway.

So, in short - it depends on your hardware set-up.

Though, to be honest, unless you're really on top of monitoring, responding to your "top SQL" reports, have good data model designers and are building the right indexes you are much more likely to find performance improvements by learning about those things first.  I'm not saying you don't do those things, but if you don't you should look to improve there first.

If you think the tablespaces issue is a problem, then (as others have said) you can just move the tables and indexes around to put them back in the right place.

Once done, a good plan would be to implement some kind of standard for how you implement table and index creates, probably using substitution variables for the names of tablespaces, most certainly supported by a scripted test that runs as part of your commit / build process to check that people aren't putting things in the wrong place.

You can then follow that up with a regular process on your demo / test server that checks for things in the wrong place and sends an e-mail to the team when it discovers something.  That way you stand a chance of discovering problems before they hit the live environment.

I'd use this approach for any standard that is broken more than once or twice.

As for non-performance related implications, it can have an impact on the maintainability of your tablespaces and ensuring that the datafiles are properly configured.  Even that is probably negligible on modern servers.

Is should say though - I'm not a DBA, I've just worked as a developer with Oracle a LONG time - so when it comes to maintainability, I'm no expert...


How to swap primary key between records in postgres

You could use a deferred constraint. For that you need to drop and re-create the primary key:

CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey
   DEFERRABLE INITIALLY DEFERRED;

The update itself could then be done like that:

UPDATE mytable SET id = 3 - id WHERE id IN (1, 2);

Here 1 and 2 are used as examples, you can do that with any numbers.

If you cannot afford the down time required for adding a deferred primary key, you could do it with one more update like this;

BEGIN;
UPDATE mytable SET id = 0 WHERE id = 1;
UPDATE mytable SET id = 1 WHERE id = 2;
UPDATE mytable SET id = 2 WHERE id = 0;
COMMIT;

Here 0 is an arbitrary value that is not used as a value for id.


You have not included your server config so I am writing all I could think of.

You have already innodb_buffer_pool_size so skipping that.

On Staging Server use following

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

On mysqldump use following options

--disable-keys
--extended-insert
--add-locks

You should use change log size this way.


The token WHEN is a reserved word, and can't be used as a column name without explicitly quoting it. Either rename the column, or quote it:

select "WHEN" from foo

Be aware that quoting names makes them case sensitive. Unquoted object names in Firebird are case insensitive, but stored in uppercase.


In Change Data Capture (CDC), why lsn is used instead of timestamp?

A date/time value is generally not considered to be unique enough. No matter how fine you define it, it is possible to have 2 actions occurring for the same datetime value.

Prior to SQL Server 2008, the most accurate value was a rounded 3.33 milliseconds with datetime which is an eternity really. datetime2 resolves to 100 nanoseconds which isn't accurate enough either.

From another view, time is is a real number. No matter how accurately you store a time, there is always some rounding to your level of resolution (In this case, 3.33 ms or 100 ns)

Using LSN (which is numeric (25,0)) gives a strictly monotonically increasing unambiguous whole number value. It is a natural number


How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?

I'm not sure how to fix this

In the comments we investigated but never found out conclusively whether the page is allocated or not.

If it's allocated this is definitely a corruption that CHECKDB missed. A bug in CHECKDB. Please report that to Microsoft.

If it's not allocated here's how I would fix it: Fill the file with dummy data  e.g. CONVERT(binary(8000), 0x). Now the page should have been consumed by the dummy data and overwritten with something clean.

Actually, you can use this idea to test whether SQL Server thinks the page is allocated. If this works it's definitely not allocated.

Alternatively, you could use the write page feature to zero fill the page but that leaves an audit trace in the database boot page. Microsoft support will not like finding this, I guess, when they help you in the future with something. They might think your page write caused damage. Also, it steals all the fun from this investigation by terminating it without resolution :)


If you right click the registered servers group and select "New Query" you get a query window that connects to all the servers, whatever you execute will run on all and return combined results.

http://msdn.microsoft.com/en-us/library/bb964743.aspx

If you want to programatically do it, you can use a central management server and query that for a list of servers, and use linked servers to run the query at each location using openquery

http://sqltidbits.com/scripts/query-central-management-server-list


The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses.  (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)

I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl $userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code.

The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table?  The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.

(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)

There's a workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.

User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection.  Their value is independent of database transactions, but never visible from one connection to another.

$dbh->do(q[ SET @app_user_id = ? ], undef, $userid);

Until you disconnect from this connection, the variable @app_user_id (which I just made up, it's just a variable name, but the @ designates it as a user defined variable) will contain the value you stored there unless you set it to something else.  Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of using CURRENT_USER() in a trigger, use @app_user_id.

This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.

So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html


Login as the mystery user. Then run:

select * from sys.login_token

Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work):

select *
from sys.login_token lt
join sys.server_principals sp on sp.sid = lt.sid;


Mysql self Join query with Limit never terminates

Your comma between two table means that you make a cross join and then select only the fitting rows.
do a proper JOIN with ON Clause, like
Select Distinct t1.id1, t2.id1
FROM image as t1 INNER JOIN image as t2
ON t1.id2 = t2.id2
LIMIT K;

Also have an index on id2 in the table image


Why does my DELETE command require huge amount of temporary run storage?

You need to batch your deletes so that you don't lock the table and fill your transaction log.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
 DECLARE
    @RC INT = 1;

WHILE (@RC > 0)
BEGIN

  delete top 1000 art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate);

  SET @RC = @@ROWCOUNT

END

You can experiment with larger batch sizes (I usually go 5000-50000) but this is a good place to start. You do have to be careful about lock escalation attempts when choosing batch sizes.

You'll probably also see an improvement by sending your list of ArticleIds to a temp table so that you only have to scan art.Article one time.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
create table #ArticlesToDelete (ArticleId int primary key not null);
insert into #ArticlesToDelete (ArticleId) select ArticleId from art.Article where PublishDate < @deleteDate;

DECLARE @RC INT = 1;

WHILE (@RC > 0)
BEGIN
  delete top 1000 A from art.ArticleConcept A join #ArticlesToDelete D on A.ArticleId = D.ArticleId;
  SET @RC = @@ROWCOUNT
END
drop table #ArticlesToDelete


Per your comment:

  Max RAM=2147483647 (24GB) AWE not enabled

The Max Memory setting is in MB, not bytes. The above setting (the default) is 2,048 TB (i.e., unlimited).

For a box with 24 GB of physical memory with a single instance of SQL Server, a good starting point for Max Memory would be 20480 (20 GB).

You may need to reserve more memory for the operating system or other applications. Note also that on SQL 2008 R2, memory allocated for the procedure cache is not included in the Max Memory setting, so generally you have to take a conservative educated guess (see above) and tweak from there.

How do you know if you guessed right? In addition to other PerfMon counters, I strongly recommend monitoring (recording) the Memory\Available MBytes counter to make sure it's >= ~500 MB at all times.

Finally, changing the memory settings in the server properties flushes the procedure cache -- I normally recommend making this change during an off-peak time, as this will create extra CPU load.


Why did the MySQL Storage Engine Change on Restore in one scenario?

This brings to mind something I have done a dozen times (fortunately with small datasets less then 10GB). When restoring a mysqldump to a new server, you must make absolutely sure the InnoDB settings on the new server is identical to the InnoDB settings.

For a new server that you have opted to restore a mysqldump to, run this command:

SHOW ENGINES;

This will display all storage engines available in in the MySQL instance. Here is a sample from a server running MySQL 5.5.9:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Notice that InnoDB has DEFAULT in the Support Column. There are four possible values for the Support column:

YES
NO
DEFAULT
DISABLED

One day, a server I was restoring to had mismatched configurations on the innodb_buffer_pool_size. Because I had set innodb_buffer_pool_size=512G instead of 512M (obvious typo), InnoDB would come up DISABLED. When loading the data into the new server, every CREATE TABLE defaulted to MyISAM upon execution. I didn't notice anything until I saw dozens of DB Connections that were waiting to write to the same table, which was typical of heavy writes against MyISAM. The solution was simply to start over with the correct InnoDB settings and reload.

MORAL OF THE STORY

Always make sure you can restore data to the same configuration as specfied in /etc/my.cnf. If the new server has less diskspace and/or less RAM than the server you mysqldump'd, they carefully configure /etc/my.cnf to the maximum settings that make the most sense for the new server you are restoring to.
Always run SHOW ENGINES; to make sure every storage engine is functioning.
Always read the error log and verify any anomalies with regard to startup, InnoDB crash recovery, fulltext stopword list declarations, MyISAM repair options, the amount of memory requested for buffers and DB Connections.
Always make sure ib_logfile0 and ib_logfile1 are the same size as the one specified in the old server's /etc/my.cnf.
Always use dev and staging DB servers to load old backups and test usability.


There is no Description column in your index but there is in your query. So, SQL Server has to get this column. There are two options for doing that:

non-clustered index seek + key lookup
clustered index scan

If your statistics make SQL Server think it will read a lot of data, SQL Server makes a choice between the non-clustered index seek + key lookup or clustered index scan, this called tipping point.
Because, if SQL Server has to read huge data, the clustered index scan can be more efficient rather than non-clustered index seek + key lookup.
There is a good blog post here.


Which is the most efficient way to run a particular select query?

I'd probably use

SELECT TOP (1) WITH TIES PolicyNumber,
                         decpageid,
                         Risk
FROM   StatRiskDecpages
WHERE  PolicyNumber = 'AR-0000301132-04'
ORDER  BY decpageid DESC

Assuming the covering index on (PolicyNumber, decpageid) INCLUDE(Risk) this will give you a plan like


SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

I'll just summarize all my comments here as an answer.

You should read this:

http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts

The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since there is no setting to have the Server Service impersonate another account when it connects through the filesystem.

The SQL Server Agent is a separate process which runs scheduled jobs.  If you use this and it connects to network shares on its own (not just telling SQL Server engine to run some statement against a linked server which would be under the engine permissions above), it would need to run under an appropriate domain account.  In your case, that doesn't appear to be running, so I wouldn't bother fooling with it.

Running the SQL Server Express has certain database size and functionality limitations, and I wouldn't consider it for general server-class production use except for high numbers of lightweight servers - for instance a deployment of an small inhouse application to several worksites - where you wouldn't want to pay for licensing and the requirements fit the product.

The built in network account is "NT AUTHORITY\NETWORK SERVICE" and you can just put that in and blank out the passwords to restore it.

If you switch the SQL Server Service to run as a domain account, you will need to give it sufficient permissions to access the OS on the database server - i.e. folder access to where the database files are if the account doesn't have access.


How do I uninstall only SSMS and Profiler for 2008R2?

Go to Programs and Features, select SQL Server 2008 R2, and then the Remove option (people tend to panic that this will just remove everything without further input, but this merely launches a wizard):

After this, you will be guided through a wizard much like installation. On the Select Instance screen, select the "remove shared features only" option:

Then you will get to this screen, where you can select just the management tools:

Though, I don't know why you don't just leave them there. They don't take up an inordinate amount of space, and you never know when you may have to use them (e.g. to manage an SSIS package or maintenance plan from your 2008 R2 instance).

(Also, the astute observer will note that these screen shots all came from different operating systems - I swiped them from the Internet.)


Two node galera is a fundamentally broken design as it cannot maintain uptime without a quorum and the ability of a node to go down to aid recovery.
garb is a toy test thing and not a production tool.
Use 3 nodes all as full galera member nodes.
Use haproxy in a redundant configuration on all nodes. Use a different IP addresses for haproxy and galera without overlap.
If it can't be reliable in test then it won't be reliable in production.


You could implementation Replication if there is a subset of data that you are looking to read from.  Replication publishes articles to subscribers.  It's a great way for a subset of data that needs to be recent data.

If you are looking for a read-only copy of the whole database, though, and the data doesn't need realtime accuracy, you could create snapshots every morning with a job.  That way the OLAP can be done against the snapshot.  Or if you need the data to be a maximum of 4 hours old you can do that. (or whatever schedule you find the need to "refresh" the data)


Order of Execution of Set clause in Update Query (Postgres)

There is nothing "strange" about this behaviour - it's the only sane way to process such an update.

There is no such thing as "order of updates" - all SET clauses should be assumed to happen in parallel.

The SQL standard requires that the right hand side of the assignment is the value of the columns as it was before the UPDATE statement started. The order in which the column assignments are listed in the UPDATE statement is irrelevant for the outcome of the UPDATE statement.

Any other behaviour of the UPDATE statement would be a bug and a violation of the SQL standard.

For the same reason, the following statement will swap the two column values:

update some_table
   set x = y, y = x;

or

update some_table
   set y = x, x = y;


MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups

ASPECT #1: BAD FILE

Something tells me you somehow ran out of space in /tmpfs.

Whenever MySQL creates a temp table

It is created as a MyISAM table
No .frm is created
Initial .MYD is 0 bytes
Initial .MYI is 1024 bytes (1K)
There are never any indexes in the .MYI

I find that strange that MySQL could not create a 1K .MYI.

Since ext3 has a default block size of 1K, while ext4 has 4K, perhaps the 4K blocksize may have allowed for more data. This, in itself, is not the real problem.

ASPECT #2: YOUR QUERY

If you have tens of thousands of tables, the query you are running looks quite demanding. In fact, the query says "Get me all databases that have 1 or more tables in them." If you have a large number of tables, you could be looking at a rather large temp table in order collect table_schema values before doing the DISTINCT.

Sometimes it is easy to forget

the SELECT DISTINCT executes a GROUP BY internally
This true in the older versions of MySQL.

There are two Stackoverflow posts that mention this

Jun 06, 2012 : How to select distinct rows without using group by statement (My Post)
Feb 24, 2009 : What's faster, SELECT DISTINCT or GROUP BY in MySQL?

ANALYSIS

My guess is a combination of anomalies

ANOMALY #1 : You have a large number of tables

ANOMALY #2 : The temp table being made must have run out of space to have a damaged .MYI

ANOMALY #3 : The query does not use an index.

Look at the table

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

A full table scan is required, resulting in the need for a temp table with an in-memory sort.

SUGGESTIONS

SUGGESTION #1 : Use ext4. You already said the query works fine in ext4.

SUGGESTION #2 : Increase tmpfs to 16G

SUGGESTION #3 : Query the information_schema so that it does not use as much memory

DROP TABLE IF EXISTS mysql.dbtbcount;
CREATE TABLE mysql.dbtbcount
(
    table_schema VARCHAR(64) NOT NULL,
    table_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (table_schema)
) ENGINE=MEMORY;
INSERT INTO mysql.dbtbcount (table_schema)
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql');
INSERT INTO mysql.dbtbcount (table_schema)
SELECT table_schema FROM information_schema.tables
ON DUPLICATE KEY UPDATE table_count = table_count + 1;
SELECT * FROM mysql.dbtbcount;
SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;

This counts all tables in all databases

When any row has a zero table_count, that database is empty.
Count all rows that have a nonzero table_count to get the DISTINCT count.
When done, run DROP TABLE mysql.dbtbcount;

Give it a Try !!!

UPDATE 2013-08-19 17:40 EDT

Your problem should have been posted as a separate question. Nevertheless, let's see what we can surmise from this.

You should not be using /tmp as a backup location. Why?

The OS needs to write temp file there. At OS reboot, all files vanish from it. So, a transient folder should not be the place for backups.
mysqld has /tmp defined as the default folder for temp files

You need to reorganize the script to do parallel dumps

SUGGESTION

You should collect the databases and parallel mysqldump 25 databases at a time, starting from the biggest databases to the smallest. It gets faster with each next set of 25...

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

UPDATE 2013-08-20 10:03 EDT

If you don't care about the size of the database here is an alternate method that will start the mysqldump process faster:

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

I wrote about doing parallel database and table dumps before : How can I optimize a mysqldump of a large database?

If you cannot get mydumper to throttle based on database size, this is the way to go.

GIVE IT A TRY !!!


Getting proper output of my SELECT clause. SQLite

SELECT * FROM ... gives this output:

ID  TIME   STOP_ID  ID  STOP_NAME  ROUTE_ID  ID  STOP_NAME     ROUTE_ID
--  -----  -------  --  ---------  --------  --  ------------  --------
5   08:00  2        2   Smolajny   1         3   Dobre Miasto  1
21  08:30  6        6   Smolajny   2         7   Dobre Miasto  2
6   12:00  2        2   Smolajny   1         3   Dobre Miasto  1
22  12:30  6        6   Smolajny   2         7   Dobre Miasto  2
7   15:00  2        2   Smolajny   1         3   Dobre Miasto  1
23  15:30  6        6   Smolajny   2         7   Dobre Miasto  2
8   19:00  2        2   Smolajny   1         3   Dobre Miasto  1
24  19:30  6        6   Smolajny   2         7   Dobre Miasto  2

Both route 1 and route 2 have stops at Smolajny and Dobre Miasto, and the first stops (2 and 6) have four times each. This output is correct.


Joining tables based on time and distance: PostGIS with PostgreSQL?

I think you want something like this,

SELECT
  big.id,
  small.id,
  ST_Distance(big.geom, small.geom),
  big.event_time <-> small.event_time
FROM small
CROSS JOIN LATERAL (
  SELECT *
  FROM big
  WHERE (small.event_time <-> big.event_time) < '2 hours'::interval
  ORDER BY small.geom <-> big.geom
  OFFSET 0
  LIMIT 1
) AS big;

You can put both of these on both timestmap and your geom for extra joy using btree_gist

CREATE EXTENSION btree_gist;

CREATE INDEX ON big USING gist (event_time, geom);
CREATE INDEX ON small USING gist (event_time, geom);
VACUUM FULL ANALYZE big;
VACUUM FULL ANALYZE small;

Note: I would upgrade to PostgreSQL 10 so you'd have parallel stuff, as it'll make a major difference for this work load.

You may also consider BRIN indexes on the timestamp, and clustering by timestamp. And after that's done going to GIS. Or even creating a new indexed MATERIALIZED VIEW with only the last week of data or whatever.


Fastest way of restoring log shipping of multiple databases

Below is the approach that I would take :

Stop logshipping jobs on both primary and secondary database servers.
If a FULL Backup job is run on the primary server then you have to initialize logshipping by restoring the latest full backup and a diff or subsequent log backups.
If a full NON COPY ONLY backup is not run on primary server, you can follow the rest

Take   differential backups of the databases on primary server.
Move all the backups to the secondary database server. You can use Robocopy or powershell to do this.
Restore the DIFFS on the secondary server using norecovery.
start logshipping jobs on primary and secondary.
At this point your logshipping should be resumed.


Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis

The solution to your problem is a MySQL capability called "partitioning".  The documentation is here.

What partitioning does is store a single table in separate "partitions".  These are defined by a particular expression, usually a column value or range.  In your case, this would probably be based on endTime -- assuming that it is known when a record is created and it doesn't change.

You would store a day's worth of endTime in each partition.  Then the deletion step would be truncating a partition rather than deleting a bunch of rows in a big table.  The partition truncation would be a much faster method.


SQL Server Management Studio 2012, authentic multi-user login

You just need to create your user and assign it to login for accessing the database.

Assuming you want to have read/write access:

USE [master]
GO
CREATE LOGIN [Domain\User_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [Database_name]
GO
CREATE USER [Domain\User_name] FOR LOGIN [Domain\User_name]
GO
USE [Database_name]
GO
EXEC sp_addrolemember N'db_datareader', N'Domain\User_name'
GO
USE [Database_name]
GO
EXEC sp_addrolemember N'db_datawriter', N'Domain\User_name'
GO


Copy SQL Server Maintenance Plan between instances on same server

Here you go:
I believe only Windows server can have only one copy of SSIS installed. So even if you have seven instances of SQL on the same box, you'll only have SSIS devoted to one of them. SSIS will work for all them but will initially only be aware of one of them. I think the following link still holds the answer by modifying MsDtsSrvr.ini.xml to make SQL Server SSIS aware: https://www.mssqltips.com/sqlservertip/2061/how-to-manage-ssis-packages-stored-in-multiple-sql-server-database-instances/


Postgressql - could not fork new process for connection: Cannot allocate memory

If each of these 3000 connections used work_mem only once (this limit is per node of the query plan, so it can be used several times by one database session), that could use almost 1TB RAM. I am not surprised that you are going OOM.
max_connections = 3000 is an insane setting. Do you have 3000 cores? Can your storage subsystem handle 3000 concurrent I/O requests?
I recommend that you use pgBouncer or another connection pool to reduce the number of database sessions.
By the way vm.overcommit_ratio should be 100 if you have no swap, otherwise you cannot allocate more than 80% of the available RAM (but perhaps that's on purpose).