It's not a weird situation at all - lots of project managers say they want this until they discover just how hard it is. This is way, way harder than it sounds. Let's take a simplified scenario to illustrate it:

CustomersV1 table, original bad design you're moving away from:

CustomerID uniqueidentifier
CustomerName varchar(100)
BalanceOutstanding - decimal, total amount they owe us

CustomersV2 table, new design:

CustomerID - integer, autonumbered identity field
CustomerName nvarchar(100) - because now we want to store unicode data
(No BalanceOutstanding field - instead we're going to have the app calculate current balance by querying several other tables)

Moving from CustomersV1 to CustomersV2 is hard enough, but how do you deal with:

Keeping a mapping of the old CustomerID to new CustomerID
What happens if a customer puts unicode data in the new CustomersV2.CustomerName, which simply can't be sent back to CustomersV1.CustomerName due to the mismatched data types
Changing application code to refer to BalanceOutstanding gathered via different ways

In reality, I've been on several projects where managers wanted to accomplish something like this, but once we even white-boarded out the changes to just one table, they realized we couldn't keep them both in sync at a price that the customers were willing to pay.

I have been on one project where the customers did pay, and here's how they did it:

Built custom SSIS packages to keep the data in sync in both directions
Built complex business rules to handle scenarios like we discussed above (what happens if someone stores data in the new schema that the old schema won't handle - in that case, the customers were willing to simply walk away and lose that data)
Tested migrations in both directions, repeatedly, leading up to go-live
Froze the database schemas on both sides months in advance to prevent changes to the complex SSIS packages

So to start the discussions with your management, lay out the differences in just one of the complex tables, and white-board out what would happen if they had to fail back from the new to old schemas. Talk about the ways you could lose data, and how much work it would be to build apps that would migrate the data back and forth.


How to restore by-default saved 'sys' database in MySQL 8.0?

It depends on how it was saved, but there's an example at the bottom of the manual page
mysql < sys_dump.sql


Advantages of using PgBouncer vs HAProxy?

HAProxy is a low lewel high availability and load balancing solution. PgBouncer combines a very good connection pooler and a very limited load balancing for PostgreSQL.

So both has different goals and can not be compared like "vs" in my opinion. Though they can be used in a combination with each other, eg. if one needs to load balance read only queries between several replicas with HAProxy and these replicas are served by their own PgBouncer instances to get pooling advantages.

More about what PgBouncer is and what it is not in is perfectly described in this blog post.


What is the relation between MySQL pages and Operating system pages?

The OS decided that XKB (often 4KB today) was a good size.  (In the old days, it was 1KB, or even less.  Note that disks are frozen in 512-byte units!)

InnoDB decided that 16KB was a good size.  InnoDB also implements tablespaces (eg ibdata1 or .ibd files) as files, not as raw disk partitions.  So, InnoDB is at the mercy of what the OS is going to do when asked to "in file y.ibd, seek to offset z, and read 16KB bytes.  (Side note:  InnoDB can be configured to use 4/8/16/32/64KB, but there are disadvantages, and I have yet to hear of anyone actually using such.)

One can hope, but not necessarily control, that a tablespace assigned at some disk address that is a multiple of 16KB, or even that it is aligned with the pages that the OS sees.  But, the OS will deliver the 16KB, regardless of the details of where the piece(s) are.

InnoDB takes pains to avoid the "torn page" problem.  This is where the system crashes in the middle of writing, say, the four 4KB blocks for the one InnoDB block.  Such a half-written block would have the effect of destroying the data integrity for that table.  To compensate, InnoDB has a "double-write" buffer in which it effectively writes some things twice.  Upon recovery, it will be able to discover and fix the "torn page" if necessary.

Since the OS might choose to scatter its pieces of InnoDB's 16KB, there is a potential performance problem with extra head seeks (on HDD).  For SSD, this does not matter much, if at all.

Hardware RAID controllers with "Battery Backed Write Cache" are an excellent way to both speed up write (making them virtually instantaneous without risk of loss) and avoid the scattering problem, even for HDDs.

Now enter some disk manufacturers.  To be "the best" in the MySQL market, their disk (SSDs) can be configured such that 16KB is the "atomic" write size.  With that kind of disk, InnoDB can be configured to turn off the double-write, hence run faster.

O_DIRECT is usually optimal for InnoDB because it avoids a copy of the 16KB to/from an OS buffer.  But this depends on the details of the disk driver.

innodb_flush_neighbors/innodb_flush_neighbor_pages is a setting to flush adjacent pages at the same time, thereby avoiding a disk seek; it has some performance benefit for HDDs.  It may have a negative impact on SDDs.

(Sorry for the TMI, sometimes my fingers just keep going.)


"Invalid object name" error when executing query

I'd guess the problem is your SQL Server login does not have it's "default database" properly set.

This will show you what your "default database" is set to:

SELECT sp.name
    , sp.default_database_name
FROM sys.server_principals sp
WHERE sp.name = SUSER_SNAME();

You can either change the default database of your login, or you can specify the database in the connection string.

This will modify your default database:

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = [testDB];

If you want to specify the database in the connection string, make your connection string:

SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOP\\ANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=testDB");


is there some way to give me the order in which inserts should be made so all FK constraint are met?

If you are sure enough that all constraints would be met..You can think off turning off the foreign key checks and after making sure that all constraints are ok, you can turn on foreign key checks.
You can write a script to verify that if all constraints are ok.

use  following to turn off foreign key checks

  set foreign_key_checks=0

use following to turn on checks again after necessary verification

  set foreign_key_checks=1

Hope it helps


60 minutes to shut down sql server service?

It shouldn't take 60 minutes to shutdown the server. As you mentioned, the DBA is performing a pause before shutting down - this is probably the major contributor to the slow shutdown.

Unless he's planning to resume the database, there isn't normally reason to pause. Removing this step from the procedure should cut down a significant portion of the 60 minutes.

Here is an example of a log that shows a pause followed by a shutdown.

The pause begins at:

SQL Server IS NOT allowing NEW connections because the Service Control Manager
requested a pause. TO resume the service<c/> USE SQL Computer Manager OR the Services
application IN Control Panel.

And the shutdown sequence begins with:

SQL Server IS terminating IN response TO a 'stop' request FROM Service Control
Manager. This IS an informational message ONLY. No USER action IS required.

Check with the DBA first though, to make sure there isn't a specialized case that necessitates the pause.


Influxdb and Grafana combine multiple SELECT

To combine aggregated measurements under different tags is a bit tricky. I managed to find this post which has the answer.

The key is to use sub-queries (InfluxDB 1.2+) to capture each tag's measurements separately. Then, since the field names are the same, they need to be renamed. Finally you can manipulate them as needed. Here's your example:

SELECT 8 * (non_negative_derivative(mean("inoctets1"), 1s )
          + non_negative_derivative(mean("inoctets4"), 1s ))
AS "inbytestotal"
FROM (
    SELECT "value"
    AS "inoctets1"
    FROM "inoctets"
    WHERE "host" = 'myhost1' AND $timeFilter
), (
    SELECT "value"
    AS "inoctets4"
    FROM "inoctets"
    WHERE "host" = 'myhost4' AND $timeFilter
)
GROUP BY time(1s) fill(null)


Can you change recovery mode from simple to full with users working on the server?

You can - as long as there's no running transaction on that database, or you run the alter statement using WITH ROLLBACK IMMEDIATE. Be careful with this clause as it will rollback nonqualified transactions - see here for a human-readable explanation.
Also, remember to take a full backup after you change the recovery model from simple to full, or the change won't really take place (info here)!


Rename the constraint of a possibly referenced primary key in PostgreSQL

Not sure why Teradata has that limitation, but ALTER INDEX ... RENAME TO ... should be fine in PostgreSQL even when other tables have foreign keys depending on that index. PostgreSQL has fairly sophisticated tracking of such dependencies -- for example, if you tried to do ALTER TABLE foo DROP CONSTRAINT foo_pkey; you would see a complaint like:

  ERROR:  cannot drop constraint foo_pkey on table foo because other objects depend on it

  DETAIL: constraint bar_fkey on table bar depends on index foo_pkey

  HINT: Use DROP ... CASCADE to drop the dependent objects too.

You can also see in the pg_constraint table (e.g. via SELECT * FROM pg_constraint WHERE conname = 'bar_fkey' for some referencing foreign key bar_fkey) that PostgreSQL is internally tracking this dependency by the OID (in particular, the conindid column in pg_constraint) of the index rather than the name of the index. The OID of the index does not change when you use ALTER INDEX ... RENAME TO ..., which is how Postgres avoids becoming confused by the rename.


The FOREIGN KEY user_chat_messages_user_chat_id_foreign is the cause of your deadlock, in this situation.

Fortunately, this is easy to reproduce given the information you've provided.

Setup

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id)
);

insert into user_chats (id,updated_at) values (1,NOW());

Note that I removed the user_chat_messages_from_user_id_foreign foreign key as it references the users table, which we don't have in our example. It is not important for reproducing the problem.

Reproducing the deadlock

Connection 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

At this point, Connection 1 is waiting.

Connection 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

Here, Connection 2 throws a deadlock

  ERROR 1213 (40001): Deadlock found when trying to get lock; try
  restarting transaction

Retrying without the foreign key

Let's repeat the same steps, but with the following table structures. The only difference this time around is the removal of the user_chat_messages_user_chat_id_foreign foreign key.

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

insert into user_chats (id,updated_at) values (1,NOW());

Reproducing the same steps as before

Connection 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

Connection 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

At this point, Connection 1 executes, instead of waiting like before.

Connection 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

Connection 2 now is the one waiting now, but it has not deadlocked.

Connection 1

commit;

Connection 2 now stops waiting and executes its command.

Connection 2

commit;

Done, with no deadlock.

Why?

Let's look at the output of SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-07-04 10:38:31 0x7fad84161700
*** (1) TRANSACTION:
TRANSACTION 42061, ACTIVE 55 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140383222380288, query id 81 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42061 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) TRANSACTION:
TRANSACTION 42062, ACTIVE 46 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140383222109952, query id 82 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000a44b; asc      K;;
 2: len 7; hex b90000012d0110; asc     -  ;;
 3: len 4; hex 5b3ca335; asc [< 5;;
 4: len 4; hex 5b3ca335; asc [< 5;;

*** WE ROLL BACK TRANSACTION (2)

You can see that transaction 1 has a lock_mode X on the PRIMARY key of user_chats, while transaction 2 has lock_mode S, and is waiting for lock_mode X. That is a result of it obtaining a shared lock first (from our INSERT statement), and then an exclusive lock (from our UPDATE).

So, what is happening is Connection 1 grabs the shared lock first, and then Connection 2 grabs a shared lock on the same record. That's fine, for now, as they are both shared locks.

Connection 1 then tries to upgrade to an exclusive lock to perform the UPDATE, only to find that connection 2 has a lock already. Shared and exclusive locks don't mix well, as you can probably deduce by their name. That is why it waits after the UPDATE command on Connection 1.

Then Connection 2 tries to UPDATE, which requires an exclusive lock, and InnoDB goes "whelp, I'm never going to be able to fix this situation on my own", and declares a deadlock. It kills off Connection 2, releases the shared lock that Connection 2 was holding, and allows Connection 1 to complete normally.

Solution(s)

At this point, you are probably ready to stop with the yap yap yap and want a solution. Here are the my suggestions, in order of my personal preference.

1. Avoid the update altogether

Don't bother with the updated_at column in the user_chats table at all. Instead, add a composite index on user_chat_messages for the columns (user_chat_id,created_at).

ALTER TABLE user_chat_messages
ADD INDEX `latest_message_for_user_chat` (`user_chat_id`,`created_at`)

Then, you can obtain the most recent updated time with the following query.

SELECT MAX(created_at) AS created_at FROM user_chat_messages WHERE user_chat_id = 1

This query will execute extremely quickly due to the index, and doesn't require you to store the latest updated_at time in the user_chats table as well. This helps avoid data duplication, which is why it is my preferred solution.

Make sure to dynamically set the id to the $message->getUserChatId() value, and not hard coded to 1, as in my example.

This is essentially what Rick James is suggesting.

2. Lock the tables to serialize requests

SELECT id FROM user_chats WHERE id=1 FOR UPDATE

Add this SELECT ... FOR UPDATE to the start of your transaction, and it will serialize your requests. As before, make sure to dynamically set the id to the $message->getUserChatId() value, and not hard coded to 1, as in my example.

This is what Gerard H. Pille is suggesting.

3. Drop the foreign key

Sometimes, it is just easier to remove the source of the deadlock. Just drop the user_chat_messages_user_chat_id_foreign foreign key, and problem solved.

I don't particularly like this solution in general, as I love data integrity (which the foreign key provides), but sometimes you need to make trade offs.

4. Retry the command after deadlock

This is the recommended solution for deadlocks in general. Just catch the error, and retry the entire request. However, it is easiest to implement if you prepared for it from the start, and updating legacy code may be difficult. Given the fact that there are easier solutions (like 1 and 2 above) is why this is my least recommended solution for your situation.


No, THROW is missing some RAISERROR functionality, including WITH LOG. I blogged about this back when SQL Server 2012 was still in beta, and no changes have been made since:

SQL Server v.Next (Denali) : Exploring THROW

However, you don't need to use sp_addmessage to send RAISERROR messages to the log, unless you need a custom message number outside of 50000:

DECLARE @s VARCHAR(32) = 'friend';
RAISERROR(N'What happened %s?', 19, 1, @s) WITH LOG;

Results:

Reading the guts of the question again, you probably do want to use sp_addmessage to achieve your ultimate goal. I shouldn't have tried to separate the logging on its own from that (50000 doesn't help much if you have other errors getting logged and want different alerts).

Example of putting the error number into the text for the message raised by RAISERROR:

BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  DECLARE @err INT = ERROR_NUMBER();
  RAISERROR(N'What happened %d?', 19, 1, @err) WITH LOG;
END CATCH


ALTER USER postgres PASSWORD null; now has locked me out of psql

Check your PostgreSQL pg_hba.conf. It should have lines like this copied directly from the docs.

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

You don't have to keep these. But it's normal to have these so you can connect locally without having to worry about passwords using your local system authentication methods. You also don't need both of these. If you only ever connect over sockets, feel free to nuke the one with tcp. This will allow you to log in with any locally authenticated user.

So you can log in as PostgreSQL with,

sudo -u postgresql psql -d myDatabase

or using tcp

sudo -u postgresql psql -d myDatabase -h 127.0.0.1


Multiple instances of same foreign key in a single table

Pedantically, A table doesn't reference a foreign key, it references a table via a foreign key.

It makes sense if it's necessary for the data model.

Here's an example. A Sales Order is associated with multiple parties playing different roles, such as the Selling Party, Customer Party, Paying Party, and Order Taking Party.

create table sales_order (
  order_id int primary key,
  seller_party_id int not null references parties(party_id),
  customer_party_id int not null references parties(party_id),
  order_taken_by_party_id int not null references parties(party_id),
  payer_party_id int not null references parties(party_id),
  ...
);


Execute stored procedure in another server from sql agent job

As the error message suggests, OPENQUERY looks for a linked server.  If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go.  This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string.

In your case, you may get the results by using something like:

SELECT spresults.*
FROM OPENROWSET('SQLNCLI', 'Server=1.1.1.1;Trusted_Connection=yes;',
                'EXEC [db_name].dbo.[sp_ProcName]') AS spresults

CAVEAT:  This will fail at runtime if your SP returns multiple resultsets.

CAVEAT 2: This will also fail if Ad Hoc queries haven't been allowed.  To sort that out, you need to run the appropriate sp_configure statement(s):

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'ad hoc distributed queries', 1
reconfigure

Of course, in order to run sp_configure, you'll need the appropriate rights in SQL Server.  According to the "Permissions" section of the sp_configure documentation, that's "ALTER SETTINGS", which is by default part of the sysadmin and serveradmin roles, but may be assigned to other roles too.


Updating a PostgreSQL Table With Different Random Dates

First you never gave a test case for when there is only one date and it is null, so we create that.

INSERT INTO employee_table (id, name) VALUES (3, 'Evan Carroll');
INSERT INTO leave_table VALUES ( 10, null, 3 );

Then we run a command to check whether or not an emp_id has more than one entry in leave_table. The results are in that derived table. We update accordingly. Here we generate a date that represents the year start between 1900-2020. Just update this for what you mean by "random date" you didn't define it in your question.

UPDATE leave_table
SET leave_date = CASE
  WHEN t.count = 1 OR t.count IS NULL
  THEN '01/01/2000'::date
  ELSE '1/1/1900'::date + ('1 year'::interval*floor(random()*120))
END
FROM (
  SELECT emp_id, count(*) FROM leave_table
  WHERE leave_date IS NULL
  GROUP BY emp_id
) AS t
WHERE leave_date IS NULL
AND t.emp_id = leave_table.emp_id;

Then we have it

TABLE leave_table;
 id | leave_date | emp_id
----+------------+--------
  1 | 1993-01-10 |      1
  4 | 1990-02-12 |      2
  2 | 1964-01-01 |      1
  3 | 1929-01-01 |      1
  5 | 1933-01-01 |      2
  6 | 1902-01-01 |      2
 10 | 2000-01-01 |      3

Now, as @McNets pointed out yesterday, I am kind of cheating. Instead, try this (much more complex query) which suffices the question's update his [emp_id] null entries with two different dates

WITH t AS (
  SELECT
    id,
    emp_id,
    leave_date,
    count(*) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table
  WHERE leave_table.leave_date IS NULL
)
UPDATE leave_table
SET leave_date = CASE
  WHEN t.max_nulls = 1 OR t.max_nulls IS NULL
  THEN '01/01/2000'::date
  ELSE date_series_emp.ds
END
FROM t
INNER JOIN (
  SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)
) AS date_series_emp
  USING ( emp_id, row_number )
WHERE t.id = leave_table.id;

Breaking it apart, the CTE does this

  SELECT
    id,
    emp_id,
    leave_date,
    count(*) FILTER (WHERE leave_date IS NULL) OVER (PARTITION BY emp_id) AS max_nulls,
    row_number() OVER (PARTITION BY emp_id)
  FROM leave_table

That generates how many nulls are in the set, and row numbers from within the set that we can join on for a 1:1 with the update query,

 id │ emp_id │ leave_date │ max_nulls │ row_number
────┼────────┼────────────┼───────────┼────────────
  2 │      1 │            │         2 │          1
  3 │      1 │            │         2 │          2
  5 │      2 │            │         2 │          1
  6 │      2 │            │         2 │          2
 10 │      3 │            │         1 │          1

The only other tricky part is the inner-join select,

  SELECT distinct_emps.emp_id,
    gs.ds,
    count(*) OVER (PARTITION BY emp_id ORDER BY random()) AS row_number
  FROM ( SELECT DISTINCT emp_id FROM leave_table ) AS distinct_emps
  CROSS JOIN generate_series('1/1/1900'::date, '1/1/1990'::date, '1 month')
    AS gs(ds)

There we're taking the distinct emp_ids, and joining them on a sequence of dates that you're calling random. We count(*) over that sequence to give it a corresponding random number from within the cardinal sequences generated.

Then we join this to the table and perform the update..

This method does have a one drawback, if the input size ever exhausts your pool of "random dates" (only 1081 of them), the update on rows past that max won't be performed at all.


Slow update on large table with subquery

While I agree with other commenters that this is a computationally expensive problem, I think that there is a lot of room for improvement by tweaking the SQL that you are using.  To illustrate, I create a fake data set with 15MM names and 3K phrases, ran the old approach, and ran a new approach.

Full script to generate a fake data set and try out the new approach

TL;DR

On my machine and this fake data set, the original approach takes about 4 hours to run.  The proposed new approach takes about 10 minutes, a considerable improvement.  Here is a short summary of the proposed approach:

For each name, generate the substring starting at each character offset (and capped at the length of the longest bad phrase, as an optimization)
Create a clustered index on these substrings
For each bad phrase, perform a seek into these substrings to identify any matches
For each original string, compute the number of distinct bad phrases that matches one or more substrings of that string

Original approach: algorithmic analysis

From the plan of the original UPDATE statement, we can see that the amount of work is linearly proportional to both the number of names (15MM) and the number of phrases (3K).  So if we multiple both the number of names and phrases by 10, the overall run time is going to be ~100 times slower.

The query is actually proportional to the length of the name as well; while this is a bit hidden in the query plan, it comes through in the "number of executions" for seeking into the table spool.  In the actual plan, we can see that this occurs not just once per name, but actually once per character offset within the name.  So this approach is O(# names * # phrases * name length) in run-time complexity.

New approach: code

This code is also available in the full pastebin but I've copied it here for convenience.  The pastebin also has the full procedure definition, which includes the @minId and @maxId variables that you see below to define the boundaries of the current batch.

-- For each name, generate the string at each offset
DECLARE @maxBadPhraseLen INT = (SELECT MAX(LEN(phrase)) FROM Bad_Phrase)
SELECT s.id, sub.sub_name
INTO #SubNames
FROM (SELECT * FROM SourceTable WHERE id BETWEEN @minId AND @maxId) s
CROSS APPLY (
    -- Create a row for each substring of the name, starting at each character
    -- offset within that string.  For example, if the name is "abcd", this CROSS APPLY
    -- will generate 4 rows, with values ("abcd"), ("bcd"), ("cd"), and ("d"). In order
    -- for the name to be LIKE the bad phrase, the bad phrase must match the leading X
    -- characters (where X is the length of the bad phrase) of at least one of these
    -- substrings. This can be efficiently computed after indexing the substrings.
    -- As an optimization, we only store @maxBadPhraseLen characters rather than
    -- storing the full remainder of the name from each offset; all other characters are
    -- simply extra space that isn't needed to determine whether a bad phrase matches.
    SELECT TOP(LEN(s.name)) SUBSTRING(s.name, n.n, @maxBadPhraseLen) AS sub_name
    FROM Numbers n
    ORDER BY n.n
) sub
-- Create an index so that bad phrases can be quickly compared for a match
CREATE CLUSTERED INDEX IX_SubNames ON #SubNames (sub_name)

-- For each name, compute the number of distinct bad phrases that match
-- By "match", we mean that the a substring starting from one or more
-- character offsets of the overall name starts with the bad phrase
SELECT s.id, COUNT(DISTINCT b.phrase) AS bad_count
INTO #tempBadCounts
FROM dbo.Bad_Phrase b
JOIN #SubNames s
    ON s.sub_name LIKE b.phrase + '%'
GROUP BY s.id

-- Perform the actual update into a "bad_count_new" field
-- For validation, we'll compare bad_count_new with the originally computed bad_count
UPDATE s
SET s.bad_count_new = COALESCE(b.bad_count, 0)
FROM dbo.SourceTable s
LEFT JOIN #tempBadCounts b
    ON b.id = s.id
WHERE s.id BETWEEN @minId AND @maxId

New approach: query plans

First, we generate the substring starting at each character offset

Then create a clustered index on these substrings

Now, for each bad phrase we seek into these substrings to identify any matches.  We then compute the number of distinct bad phrases that matches one or more substrings of that string.  This is really the key step; because of the way that we have indexed the substrings, we no longer have to check a full cross-product of bad phrases and names.  This step, which does the actual computation, accounts for only about 10% of the actual run-time (the rest is the pre-processing of substrings).

Lastly, perform the actual update statement, using a LEFT OUTER JOIN to assign a count of 0 to any names for which we found no bad phrases.

New approach: algorithmic analysis

The new approach can be divided into two phases, pre-processing and matching.  Let's define the following variables:

N = # of names
B = # of bad phrases
L = average name length, in characters

The pre-processing phase is O(N*L * LOG(N*L)) in order to create N*L substrings and then sort them.

The actual matching is O(B * LOG(N*L)) in order to seek into the substrings for each bad phrase.

In this way, we have created an algorithm that does not scale linearly with the number of bad phrases, a key performance unlock as we scale to 3K phrases and beyond.  Said another way, the original implementation takes roughly 10x as long as we go from 300 bad phrases to 3K bad phrases.  Similarly it would take another 10x as long if we were to go from 3K bad phrases to 30K.  The new implementation, however, will scale up sub-linearly and in fact takes less than 2x the time measured on 3K bad phrases when scaled up to 30K bad phrases.

Assumptions / Caveats

I am dividing the overall work into modestly sized batches.  This is probably a good idea for either approach, but it is especially important for the new approach so that the SORT on the substrings is independent for each batch and easily fits in memory.  You can manipulate the batch size as needed, but it would not be wise to try all 15MM rows in one batch.
I am on SQL 2014, not SQL 2005, since I don't have access to a SQL 2005 machine.  I have been careful not to use any syntax that is not available in SQL 2005, but I may still be getting a benefit from the tempdb lazy write feature in SQL 2012+ and the parallel SELECT INTO feature in SQL 2014.
The lengths of both the names and phrases is fairly important to the new approach.  I'm assuming that the bad phrases are typically fairly short since that is likely to match real-world use cases.  The names are quite a bit longer than the bad phrases, but are assumed not to be thousands of characters.  I think this is a fair assumption, and longer name strings would slow down your original approach as well.
Some portion of the improvement (but nowhere close to all of it) is due to the fact that the new approach can leverage parallelism more effectively than the old approach (which runs single-threaded).  I'm on a quad core laptop, so it is nice to have approach that can put these cores to use.

Related blog post

Aaron Bertrand explores this type of solution in more detail in his blog post One way to get an index seek for a leading %wildcard.


Running Powershell Code embedded in SSIS or TSQL, not in external file

If you choose to use xp_cmdshell to invoke your workflow, see this answer for guidance (tl;dr: replace all your line feeds with spaces).

If you want to use the SSIS Execute Process Task to invoke powershell.exe without referencing a file, you can use the existing guidance, but instead of putting "-F D:\myScript.ps1" in the Process > Arguments dialogue you can put an ad-hoc script to be run (test with -C 'hello' | Out-File C:\temp\foo.txt) - although I'm sure there's a list of caveats and gotchas for actual implementation of this approach.

You can also create a Powershell SQL Agent job step and execute it either ad-hoc or on a schedule.


Is there a query to see which stored procedures are using a specific symmetric key?

While I don't know of a solution that solves your specific problem, you can search the raw source code of your stored procedures using the sys.sql_modules view. This, however, won't work with encrypted (i.e. WITH ENCRYPTION) procedures.

SELECT s.[name]+'.'+o.[name]
FROM sys.procedures AS o  -- or sys.objects for all objects
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
WHERE [object_id] IN (
    SELECT [object_id]
    FROM sys.sql_modules
    WHERE [definition] LIKE '%symmetric%' -- search criteria goes here
    );

Important: The query above will return all stored procedures containing the text string "symmetric", not just procedures that use symmetric encryption or keys. Remember that it will also match for instance text that is in comment blocks och quotes.


Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

This is code that I took from a working Perl script. There is a limit of 32k for using utl_file, last I checked. You probably need to reset the v_curr_idx variable with every loop, otherwise it would write the first file and skip any smaller files.

DECLARE
   v_column_name VARCHAR2(50) := 'my_column';
   v_pkey_val    NUMBER       := 123;
   v_source_code BLOB;
   v_curr_idx    NUMBER       := 1;
   v_max_chunk   NUMBER       := 32760;
   v_lob_len     NUMBER;
   v_raw_bytes   RAW(32760);
   l_output      utl_file.file_type;
BEGIN
-- define output directory
   l_output      := utl_file.fopen( '$db_dir_name', '$file_name', 'WB' );
-- get length of blob
   SELECT dbms_lob.getlength( v_column_name )
     INTO v_lob_len
     FROM <my table>
    WHERE pkey_val = v_pkey_val;

-- save blob length
   v_lob_len := v_lob_len;

-- select blob into variable
   SELECT v_column_name
     INTO v_source_code
     FROM <my table>
    WHERE pkey_val = v_pkey_val;

   WHILE v_curr_idx < v_lob_len
   LOOP
      IF ( v_lob_len - v_curr_idx ) < v_max_chunk
      THEN
         v_max_chunk := ( v_lob_len - v_curr_idx )+2;
      END IF;

      DBMS_LOB.READ(    v_source_code, v_max_chunk, v_curr_idx, v_raw_bytes );
      UTL_FILE.PUT_RAW( l_output,      v_raw_bytes, TRUE );
      UTL_FILE.FFLUSH(  l_output );
      v_curr_idx := v_curr_idx + v_max_chunk;
   END LOOP;

   utl_file.fclose(l_output);
END;
/


Personally, I would use the BOOLEAN for a boolean value. But keep in mind the caveat that in MySQL, BOOLEAN is just a synonym of TINYINT(1) [src].

This, of course, means you can have values other than 0 or 1 if you are not careful. To avoid this, you can use the aliases TRUE and FALSE when inserting and updating data, as those map to 1 and 0 respectively.

For the sake of being complete, prior to 5.0.3, BIT was also a synonym of TINYINT(1).


mongorestore failure: error writing file "/var/lib/mongodb"

Finally after two days, I found the answer, I get this error because of lack of memory. You need to add more memory if you work with big collection.


When to create a new table if functionality is the same?

Quit thought (and MS SqlServer background).
Your order table will already have multiple tables, Header, Details.
You could 'link' your OrderDetails with the products directly...
Ok al basic design, but for Statistics and Query Oprimizers these direct links are gold, if yoy work with a relations table you cannot create a foreign key from that table to all tables as you don't know what parent value it will hold.
The parent could be a order, customer, product en the detail also order, customer, product, the kind of relation would be specified in a field.
Works fine for querying, that's not the issue, but on performance you would loose a lot i'm afraid.


Quickly establishing if a remote MySQL connection is valid using PHP

Well, on the mysqli constructor page for the php manual you have several examples on how to deal with connection errors, depending on the PHP version. For example, on the first code snippet, change the die() to

if ($mysqli->connect_error) {
    //try connecting to the backup node
    $mysqli = new mysqli('backup_node_host', 'my_user', 'my_password', 'my_db');
    ...
}

However, doing everything programmatically has its disadvantages, and as you are on DBA.stackexchange, and not on stackoverflow, I could suggest an alternative setup:

 read-only      read/write
application     application
     |              |
     |              |
 HA Proxy           |
     |\             |
     | \            |
     |  \           |
     |   \          |
     |    \         |
     |     \        |
     |      \       |
     |       \      |
     |        \     |
     |         \    |
     |          \   |
     |           \  |
 read-only      read/write
MySQL slave    MySQL Master
     ^              |
     |              |
     ----------------
    MySQL asynchronous
       replication

Obviously, this may cost more than a couple of shared hosting but avoids manual intervention (imports, failover management) and there is better management and control or certain type of errors (network timouts, etc.).


Normally, any single database can only ever1 be opened by a single SQL Server instance.  There is no way for multiple instances, even on shared storage, to simultaneously provide write access to the same pages of a database.

SQL Server does indeed cache pages, however each SQL Server instance allocates its own private memory, and no other process, including other SQL Server instances, can access that memory.  This is by-design, and works that way as a principle tenet enabling consistency.

In the case of shared storage, typically used for SQL Server Clustering, you have two (or more) computers connected to the shared storage, however only one of those servers can actually open the databases stored on that shared storage at any given time.  If one of the those servers stops, the clustering service will provide access to the other server, allowing the instance on that machine to serve the database.

A single Windows Failover Cluster Server can have multiple SQL Server Clusters installed, however each has it's own shared storage, and are essentially for all intents-and-purposes independent of each other.  They may occupy the same server computer, and they may not.  Still, they'll never be able to read from the same database file.

1 - read-only reporting workloads can be supported across multiple instances of SQL Server via Scalable Shared Databases.  The cost-benefit-ratio of such a setup is typically far below that required to make it feasible.


How to sync a database with an external full text engine?

Depends on your database engine.  For example, in Microsoft SQL Server, there are several database engine features that can track which rows have been changed, and then you can grab just those rows in your periodic query.

Another technique I've seen is to add an UpdatedDateTime field on the tables you want to search.  Use a default value of the current date/time, and add an update trigger so that whenever the record is updated, the UpdatedDateTime is reset to the current date/time.  Keep in mind that you'll probably want to index that field since you'll be querying it frequently.  Then, in your app, just poll for all records where UpdatedDateTime > the last time you updated.

If you take the latter approach, you'll probably want to do full repopulations periodically to catch any goofups where the polling app failed for a while.


Transferring Data from OLTP to OLAP databases

It is mostly referred as ETL process (extraction, transformation, and load).

Here are a link of MSDN article on Transforming OLTP Data to OLAP Data Warehouses. It is an old article but the same concept applied.


Granting Select access to all tables within a specific Database in MS SQL

If all your tables are in the dbo schema then this should work, you can just deny single tables if required

GRANT SELECT ON SCHEMA :: [dbo] TO myspecialrole


Probably you do not want group_concat() at all, you want a join:

SELECT t1.name AS p1, t2.name AS p2
FROM
    players AS t1
INNER JOIN
    matches m1 ON t1.id = m1.player_id
INNER JOIN
    matches m2 ON m1.pair_id = m2.pair_id and m1.id < m2.id
INNER JOIN
    players t2 ON t2.id = m2.player_id

The m1.id < m2.id part ensures that each pair will be returned only once, with p1 being the one with smaller id in the matches table for a given pair_id.


Help with SQL Server Error "ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'"

I have just had the same problem yesterday, and here is how I was able solve it:

Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio),

the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there.

After I added the key to the registry, I was able to run the Query with a non-admin user as well.


Why do you think that you will  not be able to bare a join? In fact if I understand correctly, your other idea is to put a list of tags into a varchar column (am I correct?) and search links by searching text in that column and that will definitely be much slower since you will have to full-scan the links table for every search.

Joins are normal, every-day operations in relational databases and are not something that you should avoid.

So say you have 3 tables,

links(link_id, url)
tags(tag_id, tag)
tags_links(tag_id, link_id)

You want to find all links with tags in (php , mysql)?

select l.link_id, l.url
from links l
    inner join link_tags lt on (l.link_id = lt.link_id)
    inner join tags t on (lt.tag_id = t.tag_id)
where t.tag in ('php', 'mysql')

links.link_id and tags.tag_id must be primary keys. In tag_links you can put primary key on combination (tag_id, link_id) - the supporting index will make searches by tag_id fast. You can an should also index the link_id column in that table, so that you can find all tags for a given link fast. Also, put an unique index on tags.tag column so that you can find tag_id from tag fast (and tags should be unique anyway, so you're enforcing an important business rule with such a constraint and index).

Try that out, fill some imaginary test data into such a schema, say, 1M links, 10k tags and 10M tags_links, and test the performance. I think you'll be surprised with the results.


Just found out very neat explanation:

superkey: a set of attributes which will uniquely identify each tuple in a relation

candidate key: a minimal superkey

primary key: a chosen candidate key

secondary key: all the rest of candiate keys

prime attribute: an attribute that is a part of a candidate key (key column)

nonprime attribute: a nonkey column


What should I ask at a MySQL DBA interview?

Not a complete list, but rough list of things I would try to cover. It also depends on whether they will be the first "true" DBA or in a DBA team. Whether the DBA is responsible for the machines, too, or only the database on top of that. etc.

what RAID configuration should be used
backup strategies for databases.
MySQL specific things like differences between MyISAM and InnoDB
backup strategies and recovery
Let them do some SQL queries and some query optimization (usage of explain etc. even when using hibernate. Sometimes it's good to bypass hibernate to gain performance)
did I mention backup strategies
For scalability a DBA should know about different replication modes (RBR, SBR, mixed mode replication, replication maintenance such as observing replication lag and binlog maintenance)
InnoDB tuning
What kind of files are being written by the DB (eg, ibdata and log files) and how they can be arranged (eg, one ibdata file per table, move them around on different partitions, InnoDB compression)
Discuss monitoring tools. What are you using, do they have experience with that tool or a similar one?
I'd also look into system tools like iostat/memstat/vmstat/whatever your OS provides.  Give them a system with some load and let them find the cause
And maybe discuss issues in MySQL backup and especially restore ;-)

I'm sure others here can extend this list


Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

There is no specific option for dumping views.

You can try the following:

mysqldump -h... -u... -p... --all-databases --routines --triggers --no-data > /root/MySQLDBSchema.sql
grep "CREATE ALGORITHM" /root/MySQLDBSchema.sql

You should be able to see the views. This indicates that when you dump databases, the view comes with it.

Another stunt you can try, just to get the views only, is this:

mysql -uroot -prootpass AN -e"select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views" | sed 's/;/\\G/g' | mysql --uroot -prootpass > /root/MySQLDBViews.sql

Give it a Try !!!


Msg 823, Level 24, State 2 for Clustered Column Store Index in SQL Server 2014

Follow the steps in KB2015755: How to troubleshoot a Msg 823 error in SQL Server. Message 823 usually indicates a hardware problem:

  The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged.

The typical approach is to use SQLIOSIM to stress the IO subsystem and see if errors occur, see KB231619: How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem.

If the stress does not report errors but the statement continues to consistently reproduce the issue then you should contact product support.


Conditional window function with a twist -- include at most X preceding rows but exclude some

SELECT *
FROM data as d
  OUTER APPLY
  (
      SELECT SUM(prev_amount) as sum_not_bad
      FROM
          (SELECT TOP(4) (p.amount)as prev_amount
           FROM data as p
               LEFT JOIN bad_weeks as b
                    ON p.week = b.week
           WHERE p.week < d.week
                AND b.week is null
           ORDER BY p.week DESC
          )a
  )oa

output

week        amount                                  sum_not_bad
1           100.00                                  NULL
2           100.00                                  100.00
3           100.00                                  200.00
4           100.00                                  300.00
5           100000.00                               400.00
6           100.00                                  400.00
7           50000.00                                400.00
8           50000.00                                400.00
9           50000.00                                400.00
10          100.00                                  400.00

dbfiddle


SSIS Package not retaining parameters in SQL Server agent job

I've discovered what the issue was.

2 of the parameters were connection strings and if a connection string contained an Application Name then the value of the preceding parameter was not being stored.

This worked fine from my machine so I'm assuming a difference in the version of SSMS client is to blame.

For the time being I have simply removed the application name from the connection strings.


What is the best way to save a large array in database?

It depends on where the data is coming from:

If you do not have a .NET application and you do have the data in a text file, then you can use the BCP.exe utility.
If you have a .NET application, then my preference would be to use a Table-Valued Parameter (TVP). I have an example of one (with .NET / C# and T-SQL code) in the following Stack Overflow answer:

How can I insert 10 million records in the shortest time possible?


MaridDB 10.3.29 prevents me from deleting a foreign key

This is actually a bug in MariaDB. See the bugtracker.
They recommended to do FLUSH TABLES; before the ALTER statement.
I can confirm that this "hides the bug".


Is execution plan cached “better” for stored procedures than for a non-dynamic query?

The answer is also available as a standalone blog article.

In order to find it out, I did some tests. The goal is to have the same parametrized query executed either directly from C# or by calling a stored procedure and to compare the runtime performance.

I started to create a stored procedure which does a sample query using Adventure Works database:

create procedure Demo
    @minPrice int
as
begin
    set nocount on;

    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
end

Then, I use the following piece of code to compare the performances:

long RunQuery(SqlConnection connection, int minPrice)
{
    const string Query = @"
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
    option (recompile)";

    using (var command = new SqlCommand(Query, connection))
    {
        command.Parameters.AddWithValue("@minPrice", minPrice);
        var stopwatch = Stopwatch.StartNew();
        command.ExecuteNonQuery();
        stopwatch.Stop();
        return stopwatch.ElapsedMilliseconds;
    }
}

long RunStoredProcedure(SqlConnection connection, int minPrice)
{
    using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
    {
        command.Parameters.AddWithValue("@minPrice", minPrice);
        var stopwatch = Stopwatch.StartNew();
        command.ExecuteNonQuery();
        stopwatch.Stop();
        return stopwatch.ElapsedMilliseconds;
    }
}

ICollection<long> Execute(Func<SqlConnection, int, long> action)
{
    using (var connection = new SqlConnection("Server=.;Database=AdventureWorks2014;Trusted_Connection=True;"))
    {
        connection.Open();
        using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
        {
            command.ExecuteNonQuery();
        }

        return Enumerable.Range(0, 100).Select(i => action(connection, i)).ToList();
    }
}

void Main()
{
    var queries = Execute(RunQuery);
    var storedProcedures = Execute(RunStoredProcedure);

    Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
    Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}

Notice option (recompile) and with recompile. This will force SQL Server to discard previously cached execution plans.

Each query is run une hundred times with a different parameter every time. The time spent by the server is measured at client side.

By running DBCC FreeProcCache; DBCC DropCleanbuffers; before gathering metrics, I make sure that all previously cached execution plans are removed.

Running this code gives the following output:

  Stored procedures: 786 ms. Details: 12, 7, 7, 9, 7, 7, 9, 8, 8, 6, 8, 9, 8, 8, 14, 8, 7, 8, 7, 10, 10, 7, 9, 6, 9, 8, 8, 7, 7, 10, 8, 7, 7, 6, 7, 8, 8, 7, 7, 7, 14, 8, 8, 8, 7, 9, 8, 8, 7, 6, 6, 12, 7, 7, 8, 7, 8, 7, 8, 6, 7, 7, 7, 12, 8, 6, 6, 7, 8, 7, 8, 8, 7, 11, 8, 7, 8, 8, 7, 9, 8, 9, 10, 8, 7, 7, 8, 8, 7, 9, 7, 6, 9, 7, 6, 9, 8, 6, 6, 6.
  Queries: 799 ms. Details: 21, 8, 8, 7, 6, 6, 11, 7, 6, 6, 9, 8, 8, 7, 9, 8, 7, 7, 7, 7, 7, 7, 10, 8, 8, 7, 8, 7, 6, 11, 19, 10, 8, 7, 8, 7, 7, 7, 6, 9, 7, 9, 7, 7, 8, 7, 12, 9, 7, 7, 7, 8, 7, 7, 8, 7, 7, 7, 9, 8, 7, 7, 7, 6, 7, 7, 16, 7, 7, 7, 8, 8, 9, 8, 7, 9, 8, 7, 8, 7, 7, 6, 7, 7, 7, 7, 12, 7, 9, 9, 7, 7, 7, 7, 9, 8, 7, 8, 11, 8.

Let's run it again:

  Stored procedures: 763 ms. Details: 11, 8, 10, 8, 8, 14, 10, 6, 7, 7, 6, 7, 7, 9, 6, 6, 6, 8, 6, 6, 7, 6, 8, 7, 16, 8, 7, 8, 9, 7, 7, 8, 7, 7, 11, 10, 7, 6, 7, 8, 7, 7, 7, 7, 7, 7, 10, 9, 9, 7, 6, 7, 6, 7, 7, 6, 6, 6, 6, 6, 10, 9, 10, 7, 6, 6, 6, 6, 6, 8, 7, 6, 6, 7, 8, 9, 7, 8, 7, 10, 7, 7, 7, 6, 7, 6, 7, 11, 13, 8, 7, 10, 9, 8, 8, 7, 8, 7, 7, 7.
  Queries: 752 ms. Details: 25, 10, 8, 8, 12, 8, 7, 9, 9, 8, 6, 7, 7, 6, 8, 6, 7, 7, 8, 9, 7, 7, 7, 7, 6, 10, 8, 7, 7, 7, 7, 7, 7, 7, 8, 9, 7, 6, 6, 6, 7, 13, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 6, 10, 7, 7, 8, 9, 8, 7, 6, 6, 7, 7, 9, 7, 8, 6, 9, 7, 7, 8, 7, 6, 6, 7, 7, 7, 7, 6, 7, 7, 8, 7, 7, 6, 7, 9, 8, 7, 7, 7, 7, 6, 7, 6, 6, 9, 7, 7.

It seems that the performance is very close between stored procedures and direct queries. Running the code a dozen times, I notice that stored procedures seem to be slightly fast, but the gap is very narrow. Possibly passing around the whole query creates this additional cost, which may increase if SQL Server is hosted on a dedicated machine with a slow LAN between it and the application server.

Let's now turn execution plan caching on and see what happens. To do this, I remove option (recompile) and with recompile from the code. Here's the new output:

  Stored procedures: 26 ms. Details: 23, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
  Queries: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.

It becomes clear that caching has exactly the same effect for both direct queries and stored procedures. In both cases, it reduces the time to nearly zero milliseconds, and the most expensive query is the first one—the one which runs after the removal of cached execution plans.

Running the same code again shows a similar pattern. Sometimes, queries are faster, and sometimes stored procedures are. But every time, the first query is the most expensive one, and all others are close to zero milliseconds.

Reopening SQL connection

If the SQL connection is opened for every query, such as in this slightly modified code:

long RunQuery(string connectionString, int minPrice)
{
    const string Query = @"
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
    option (recompile)";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(Query, connection))
        {
            command.Parameters.AddWithValue("@minPrice", minPrice);
            var stopwatch = Stopwatch.StartNew();
            command.ExecuteNonQuery();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }
}

long RunStoredProcedure(string connectionString, int minPrice)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
        {
            command.Parameters.AddWithValue("@minPrice", minPrice);
            var stopwatch = Stopwatch.StartNew();
            command.ExecuteNonQuery();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }
}

ICollection<long> Execute(Func<string, int, long> action)
{
    var connectionString = "Server=.;Database=AdventureWorks2014;Trusted_Connection=True;";
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
        {
            command.ExecuteNonQuery();
        }
    }

    return Enumerable.Range(0, 100).Select(i => action(connectionString, i)).ToList();
}

void Main()
{
    var queries = Execute(RunQuery);
    var storedProcedures = Execute(RunStoredProcedure);

    Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
    Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}

the observed metrics are very similar:

  Stored procedures: 748 ms. Details: 11, 8, 6, 6, 8, 9, 9, 8, 8, 7, 6, 8, 7, 9, 6, 6, 6, 6, 6, 6, 7, 7, 6, 9, 6, 6, 7, 6, 6, 7, 8, 6, 7, 7, 7, 13, 7, 7, 8, 7, 8, 8, 7, 7, 7, 7, 6, 7, 8, 8, 8, 9, 7, 6, 8, 7, 6, 7, 6, 6, 6, 6, 8, 12, 7, 9, 9, 6, 7, 7, 7, 8, 10, 12, 8, 7, 6, 9, 8, 7, 6, 6, 7, 8, 6, 6, 12, 7, 8, 10, 10, 7, 8, 7, 8, 10, 8, 7, 8, 7.
  Queries: 761 ms. Details: 31, 9, 7, 6, 6, 8, 7, 7, 7, 7, 7, 6, 8, 7, 6, 6, 7, 10, 8, 10, 9, 7, 7, 7, 7, 10, 13, 7, 10, 7, 6, 6, 6, 8, 7, 7, 7, 7, 7, 7, 7, 9, 7, 7, 7, 6, 6, 6, 9, 7, 7, 7, 7, 7, 6, 8, 10, 7, 7, 7, 7, 7, 7, 7, 8, 6, 10, 10, 7, 8, 8, 7, 7, 7, 7, 7, 6, 6, 7, 6, 8, 7, 7, 7, 7, 7, 7, 7, 8, 7, 8, 7, 9, 7, 6, 6, 12, 10, 7, 6.

with option (recompile) and with recompile and:

  Stored procedures: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
  Queries: 32 ms. Details: 26, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0.

without.

Under the hood

Let's see what happens under the hood. The following query shows cached execution plans:

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

When running this query after executing the stored procedures one hundred times, the result of the query looks like this:

usecounts   size_in_bytes cacheobjtype                                       objtype              text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100         90112         Compiled Plan                                      Proc                 create procedure Demo
    @minPrice int
as
begin
    set nocount on;

    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[Product
100         16384         Compiled Plan                                      Prepared             (@minPrice int)exec Demo @minPrice --with recompile
1           49152         Compiled Plan                                      Adhoc                --DBCC FreeProcCache
--DBCC DropCleanbuffers

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

(3 row(s) affected)

When running the query directly one hundred times, the result is:

usecounts   size_in_bytes cacheobjtype                                       objtype              text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100         73728         Compiled Plan                                      Prepared             (@minPrice int)
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[
1           49152         Compiled Plan                                      Adhoc                --DBCC FreeProcCache
--DBCC DropCleanbuffers

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

(2 row(s) affected)

Conclusion

The execution plan is cached for stored procedures and direct queries.
The performance between stored procedures and direct queries is very similar when the SQL Server and the application are hosted on the same machine. When SQL Server is hosted on a dedicated server accessed through LAN, using stored procedures may result in better performance.


How do I reset the SQL Server password on Linux?

While not mentioned on technet, there is an argument to mssql-conf that works here,

sudo /opt/mssql/bin/mssql-conf set-sa-password

You'll have to pick a password, note for reasons unexplained The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.. I suggest everyone use MICROSOFTsucksH4RD, as it satisfies that.

Also, while you're there you may as well use mssql-conf to disable the spyware

I'm just going to put there here so when I'm looking for SQL Server Password on Google because I can't remember it, I can find it.

  The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters


Can nologging in certain tables boost the performance?

Not for a normal transactional load. nologging only affects some very specific cases of DML (essentially direct-path inserts) and some DDL (see logging_clause). So essentially only for bulk data-loading scenarios. If that's not what your database is doing, it won't help at all. If it is what your database does all day, then look at the most expensive statements and see if they can benefit from nologging.

  Data corruption because of nologging is a concern

No it's not. Recoverability is. But since you're in noarchive mode, you don't really seem to care about that.

  Can someone give insight of how truly enabling nologging helps in this situation?

As said above, it generally doesn't. It is certainly not something you should try and turn on everywhere for everything. Look at specific statements and objects. Test to make sure it does make a difference and doesn't break your application (direct-path operations have specific requirements in terms of when you need to commit in particular).

  What are the other options I have?

You already know one: get hardware that can handle the load you need to process. Sounds dumb, but if you're generating 8Mb/s writes and your drives can only handle 7Mb/s, performance will suck. There's no magic.

The other is: tune your application. Poorly written code can generate way more activity than necessary on the database - sometimes massively. You need to look at what's hurting (with AWR or whatever other tools you use) and figure out if there are better ways of doing things.


Is MySQL Clustering a viable option for a DB expecting 5TB of data in 5 years?

currently the maximum data set size is around 3TB, if you are using all in-memory data.
You can increase that if you use disk-based tables

MySQL Cluster also has very flexible replication to external storage engines, ie to InnoDB, so many users manage their "hot" data in cluster, then replicate "colder / aged" data to innodb.

There is a blog describing how to configure this here:
http://johanandersson.blogspot.co.uk/2009/05/ha-mysql-write-scaling-using-cluster-to.html


Azure SQL Database finding latest available restore point

You can use Get-AzureRmSqlDatabaseRestorePoints to get the earliest possible restore point for the db.  The output from this command is below:

ResourceGroupName        :
ServerName               :
DatabaseName             : testdb
Location                 : East US
RestorePointType         : CONTINUOUS
RestorePointCreationDate :
EarliestRestoreDate      : 2/23/2019 1:53:30 AM
RestorePointLabel        :

Unfortunately, this command does not give you the latest restore point.

According to Microsoft the transaction log backups generally occur every 5 - 10 minutes.  I attempted to do a test restore with the recovery point 2 minutes behind the current time and got the following error:

  Choose a restore point between earliest restore point and latest backup time which is 6 minute before current time.


SSRS Scale-Out Deployment: cannot decrypt the symmetric key

I work with Roger and the issue turned out to be after a little work that the second server was a cloned image that had been OBEd so it was a "different" server from the server SID and identity. However, the install of SQL 2008R2 SSRS was already on the server. The SSRS install creates a unique InstallationID (GUID) and this is used to identify the SSRS instance in a scale out of SSRS. So both servers have the same InstallationID and that means they both appear to be the same server and thus the key is the same for both and it cannot backup or restore the key since it conflicts with an existing key in dbo.keys in ReportServer DB. The InstallationID can be found in rsreportserver.config file, it will also be in the registry in many, many locations and trying to find and fix them is not a feasible choice.

The solution is to remove and then re-install the SSRS instance on the second server so that it will have a new InstallationID and crypto key. Then it can be added to scale out installation.


Slow query performance on large table with no unique keys

INDEX(a) is unnecessary when you also have INDEX(a,b). So get rid of these:

KEY `company_id` (`company_id`),
KEY `ca` (`company_id`,`account_number`),

ca is a good index, but cab is better; removing ca will encourage the optimizer to use cab.  How many rows are in the result set?

Was the SELECT slow during one of those big DELETE + INSERT times?  If so, please provide those queries for further discussion.


In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?

From this wonderful website:

INFORMATION_SCHEMA.COLUMNS

CHARACTER_MAXIMUM_LENGTH

  Maximum length, in characters, for binary data, character data, or
  text and image data.
  -1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see

Data Types (Transact-SQL).

Column name      Data type      Description
TABLE_CATALOG   nvarchar(128)   Table qualifier.
TABLE_SCHEMA    nvarchar(128)   Name of schema that contains the table.
TABLE_NAME      nvarchar(128)   Table name.
COLUMN_NAME     nvarchar(128)   Column name.
ORDINAL_POSITION    int Column identification number.
Note: In SQL Server 2005, these column IDs are consecutive numbers.

COLUMN_DEFAULT              nvarchar(4000)  Default value of the column.
IS_NULLABLE                 varchar(3)  Nullability of the column.
                            If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
DATA_TYPE                   nvarchar(128)   System-supplied data type.
CHARACTER_MAXIMUM_LENGTH    int Maximum length, in characters, for binary data, character data, or text and image data.
-1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).

CHARACTER_OCTET_LENGTH      int Maximum length, in bytes, for binary data, character data, or text and image data.
-1 for xml and large-value type data. Otherwise, NULL is returned.

NUMERIC_PRECISION           tinyint Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_PRECISION_RADIX     smallint    Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_SCALE               int Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
DATETIME_PRECISION          smallint    Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
CHARACTER_SET_CATALOG       nvarchar(128)   Returns master. This indicates the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.
CHARACTER_SET_SCHEMA        nvarchar(128)   Always returns NULL.
CHARACTER_SET_NAME         nvarchar(128)    Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
COLLATION_CATALOG         nvarchar(128) Always returns NULL.
COLLATION_SCHEMA          nvarchar(128) Always returns NULL.
COLLATION_NAME            nvarchar(128) Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.
DOMAIN_CATALOG            nvarchar(128) If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.
DOMAIN_SCHEMA             nvarchar(128) If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.
DOMAIN_NAME               nvarchar(128) If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.


Does existing msrepl_tran_version column need to be removed when re-creating replication?

Refer to : Why does replication add a column to replicated tables; will it be removed if the table isn't published?

If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

you can drop the columns after you restore the database (if you want). Nothing harm in keeping them as well.

-- this script will generate drop statements.
-- copy the generated statements to DROP columns related to T-rep msrepl_tran_version
-- First drop constraints
SELECT 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.NAME + ''') and Type = ''D'')
            ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP CONSTRAINT [' + DC.NAME + ']'
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'

UNION

SELECT 'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N''' + SO.NAME + ''') and Name = ''msrepl_tran_version'')
           ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP COLUMN [msrepl_tran_version]  '
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'
ORDER BY 1

Also, refer to How to cleanup Replication Bits


Assuming they are all ints and you always want the first three digits to appear before the decimal:

WITH n(r) AS (
  SELECT 123 UNION ALL SELECT 1234 UNION ALL SELECT 1234567
)
SELECT LEFT(r, 3) + CASE
  WHEN LEN(r) > 3 THEN '.' + SUBSTRING(RTRIM(r),4,38) ELSE '' END
FROM n;


CHeck to see if you are having the backups stored on the same physical location as your data and log files. If you are trying to write all activity to one disk, that is going to be a likely bottleneck.

Also, if you are using a 3rd party tool (such as Litespeed) for backup compression, there is a chance you are consuming more CPU than you expected, and that could also result in less than optimal performance.

HTH


How do I return data from a table where the data is the max date of the previous month's data submissions (-1 of current date)?

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));

/* example query for procedure */

declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus
          from tracker_live i
          where i.client_nbr=tl.client_nbr
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;


A minimum of a 3 Node Galera Cluster is Recommended, but is it better to have 5 nodes?

If the criteria for "safe" is "no single point of failure", then 3 suffices.

If "safe" means surviving "any two points of failure", then there is no solution.  5 only handles the case where two servers go down, not arbitrary combinations of things.

RAID 10 (or 5, but not 1 or 0) provides recovery from a single disk failure on a single machine.  Since a 3+ node cluster can survive the failure of one entire disk subsystem, RAID is not required; it just gives you an extra level of comfort.

I do like RAID 10 with Battery Backed Write Cache -- this has the bonus of making writes virtually instantaneous.

Here is a situation that can happen with a 3-node cluster (N1, N2, N3).  Let's say N1 dies.  After putting a new (or repaired) N1 into the cluster, it will rebuild the data.  This uses N2 to as the 'donor' to rebuild N1.  That leaves only N3 at full functionality.  (N2 will be somewhat busy sending data to N1.)  The Cluster is still alive, though "slowed".


Postgres Partition Table Trigger inserting duplicate records

You can (pick one)

Create a view and build on it the trigger  with INSTEAD OF. Then you mae insert on the view, not on the table (you can have a trigger on the table raising an error if you want to be sure).
Do not make direct insert. Call a stored procedure and move logic there

More on trigger and where they can be applied here: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html


PostgreSQL - Check value is not the same in multiple columns

To ensure that the e-mail field has priority, use ORDER BY:
SELECT password
FROM account_users
WHERE username = $1
   OR email = $1
ORDER BY email = $1 DESC
LIMIT 1;

Alternatively, use UNION ALL, which might be easier to understand:
SELECT password FROM account_users WHERE email = $1
UNION ALL
SELECT password FROM account_users WHERE username = $1
LIMIT 1;

Is there any reason to allow @ in the user name?
username      text not null unique check (username !~ '@'),
email         text not null unique check (email    ~  '@'),

(You might allow the user name to be an e-mail address if it is the user's own.)

A CHECK constraint is not allowed to look at other rows. So you would have to use triggers (on any INSERT or UPDATE, check that no other row with the same value exists).


Does it make sense to define a new "Data type"? Is there a way to do so?

This isn't something that requires a custom data type to solve.
If the requirement is Campaign 09 can only be associated with one year, then Year is simply an attribute for Campaign (although I'd argue perhaps StartDt or something similar, from which year can be derived might be more appropriate).
To illustrate:
CREATE TABLE Campaign
(
  CampaignNbr  INT   NOT NULL
 ,StartDt      DATE  NOT NULL
 /* Everything else */
 ,CONSTRAINT PK_Campaign PRIMARY KEY (CampaignNbr)
)


How can I set the auto_increment value of a column in an existing InnoDB table, without incurring downtime?

Bite the bullet and take the downtime.

Setting the auto-increment value for the table is useless since it will be reset to the MAX(id)+1 anyway.

ALTER TABLE -- I don't think INPLACE will work in this case, so it will be a full table copy.  (Especially if the id is the PRIMARY KEY.)

pt-online-schema-change -- full copy.  Its benefit is that the table continues to be usable.  But your table is not usable for another reason, namely the inability to add another id.

What to alter?  If you have INT, you have a 2 billion limit; changing to INT UNSIGNED would give you a 4 billion limit.  The table size won't change (aside from the shuffling of BTrees).  BIGINT is 8 bytes and gives you an unreachable limit.  Caution:  All tables referencing this one need to change the datatype at the same time.  This involves more ALTER TABLEs.

Reuse 0..1.8M?  You can explicitly set ids as you do INSERTs, but that probably requires code changes (and risk).

This may be the fastest fix:  Move the top 1.8M rows down by doing something like

UPDATE tbl
    SET id = id - (some constant: about 2B -1.8M)
    WHERE id > (the same constant);

and restart the server so that it can discover the new MAX(id).  Caution:  This technique will not work for version 8.0.
CAUTION:  If other tables use this id (as a FK or for JOINing, you must also do a similar UPDATE on their table to adjust the foo_id in it.

Caution:  Don't set any id to 0 or less than 0.  (I think that UPDATE is correct in that respect.)
You will have room for only 1.8M rows before you are in trouble again.

Gaps?  Do you have lots of gaps in ids?  They are caused by INSERT IGNORE, REPLACE, and several other types of INSERT, plus (of course) DELETE.  Many of the 'insert' methods that 'burn' ids can be recoded to eliminate the burning and usually not be any slower.

Going forward...  It is not always 'right' to have an AUTO_INCREMENT PRIMARY KEY on every table.  I estimate that 2/3 of tables are better off with a 'natural PK' -- that is some column (or combination of columns) that is UNIQUE and not to bulky.


Using SSIS to extract query data and present it through a cube

tl;dr;

SSIS moves data. SSAS creates cubes. SSRS displays data.

Given the following problem statements

The client cannot directly query the database.
The client can only query the cube.

Therefore, you're going to need to modify the existing SSAS cube solution to expose the missing data for them to consume. Creating a new table in the database isn't going to directly help as you've specified they cannot query the database.

It might help in that you can materialize the existing query into a table. Whether that's correct approach is beyond the scope of a question here. You would then use SSIS to pop the data into the table(s) needed for reporting.

Since we're talking cubes, you might not have much to do or you have plenty. The general idea being you're going to define a central table that contains the measurable things at the lowest possible grain required. If they need to report a store's daily sales, then you're probably looking at a table that contains a sales amount and then two foreign keys: store and day. This table will be a fact or a measure table.

You have an existing query that helps identify what they want to filter or slice the data by - those are your dimensions. In the previous example, we'd have a date dimension and a store dimension. Dimensional modeling can get complex pretty quick but your saving grace is there is already an existing cube out there. Your predecessor has likely created "conformed dimensions" which means you don't need to create your own store or date dimension, you simply need to ensure you are correctly tying this new fact table into the existing dimensions.

You might even be able to fake out the dimension table by creating a view. In fact, it's usually a good idea to drop a view over your fact tables before presenting it to SSAS as it abstracts away the physical implementation from the logical.


SQL job using SSIS package takes a long time to run especially in the evenings

Though not really enough info here to give a full answer these steps are how I would approach trouble shooting this...

check the execution logs for the package. Take note of the timings on steps. It should he obvious which takes longest.
execution plans are helpful once you know which to look at. If it's stored procs or a fairly static query the should be in the query plan.
change the package to manually log individual steps and information that you can clearly analyse later it will help identify the slow running points. Add row counts to see if you can quantify if a per row metric to calculate run time. Even run a query to log current connections and transactions to see if it's clashing with other tasks.

This being said 8 hour sync between staging and live is huge unless we're talking about 100s of millions of rows. The run time could suggest missing indexes on staging but the execution plans should tell you that.


Commit() takes 50 seconds after inserting about 100000 rows

In WAL mode, the database must do regular checkpoints to prevent the log from growing infinitely large.

So after a large amount of changes, it decides to do an automatic checkpoint (especially since you have set wal_autocheckpoint to a rather small value).

You can move the checkpoint delay to another time (but not avoid it altogether) by doing checkpoints manually.


Hierarchy of Employee "names" with CTE: Cannot create a correct version

If you specify the root node in the CTE, it works ok.

WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
             [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
              CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
        UNION ALL

        SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
             Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss]
     )

SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

SQL Fiddle here.


Database with insert data every 2 minutes

Inserting 10 rows each 2 minutes will result in (24*60/2)*10 rows per day (7200) -> that is not a large value to worry about. Also, it's great that you think on the "future - 10 years", but don't lose time with premature optimization.

If your only concern it's about this table where you're inserting data every 2 mins, there's no point in creating additional databases (one per year), so let's stick with this table (Table_A).

Now, because this table will slowly increase in time, and your want your queries to run fast, you have plenty of options:

you can partition the table by certain criteria. Because your using the table for a "weather station", and your data is time series values, your best option would be to partition by [station_id] then by [created]
Create a Table_A_Archive, where you can move data that would be to old to keep in Table_A.
How long do you intend to keep data in Table_A ? would it make sense to delete old rows that become obsolete for you application

... And so on.

Best Bet: Partitioning the existing table by [station_id] and [created],  you will have "A" partitions for each station, "B" partitions for each month, and a total of AxB posible number of partitions. Once you partition Table_A, do the same thing for Table_A_Archive, and on the end of each year, move the data from Table_A to Table_A_Archive.

** IMPORTANT:** After you make the partitioning schema, keep in mind that all queries should have in the WHERE clause the conditions necesarly so that the query will hit as little partitions as posible.

Ex.

Select AVG(pressure)
FROM Table_A
WHERE station_id = 123
AND created between '2013-01-01 00:00:00' AND '2013-02-01 00:00:00'

.

The query above will only touch the partition "123" and the 2 sub partitions for months 1 and 2


sudo service mongo stop [This didn't work]

As MongoDB BOL  mongo is an interactive JavaScript shell interface to MongoDB, which provides a powerful interface for systems administrators as well as a way for developers to test queries and operations directly with the database. mongo also provides a fully functional JavaScript environment for use with a MongoDB.

And mongod is the primary daemon process for the MongoDB system. It handles data requests, manages data access, and performs background management operations.

As simple language we can say that mongo is shell interface for MongoDB. And mongod is server , which handles data requests, manages data access, and performs background management operations.

So, in place sudo service mongo stop it should be the below mention command

  sudo service mongod stop

As MongoDB documented Here Some of basic procedure to check MongoDB services in RHEL is as mention below:

Start MongoDB

sudo service mongod start

Stop MongoDB

sudo service mongod stop

Restart MongoDB

sudo service mongod restart

Verify that MongoDB has started successfully

sudo chkconfig mongod on

Begin using MongoDB

Start a mongo shell on the same host machine as the mongod

mongo --host 127.0.0.1:27017


Why would anyone ever disable `index_condition_pushdown` (ICP)?

There are two related things...

Covering index:  When the index is "covering" because all columns mentioned anywhere in the SELECT are contained in the index, the query has always been performed via "index-only scan".  MySQL's EXPLAIN indicates it with

   Using index

Think of it as treating the Index BTree as a 'table'.

Index Condition Pushdown:  The relatively new (5.6) 'ICP' applies to other situations -- when the part of the index being used for filtering is not sufficient.

Version 4.0 split the "Handler" from the "Storage Engines".  The "Handler" does parsing, optimization, etc, etc.  The "Engine" talks to the disk.  This implies that rows are often fetched from disk by the engine, then handed off to the handler for more processing.  In some situations, this handoff can be avoided -- by pushing more processing down from the handler into the engine.  ICP is such a case.  In the past, stuff that was not in the index, but tested in the WHERE, was handled only by the Handler.  Now, the engine can handle it.  EXPLAIN indicates it via

   Using index condition

With ICP, InnoDB tries to read the row and make the rest of the WHERE tests.  This (usually) avoids handing off rows that are to be filtered out.

I have not heard of a case to turn ICP off.  I suspect most 'modes' exist "just in case" the Optimizer has a surprise bug for which the best solution is to turn off a feature.

Your query

Are there only 3 columns in the table?  If so, then a "table scan" is identical to an "index scan"; either would suffice.  Given a choice, the optimizer would pick an index starting with zipcode.  But it would not use the other 2 columns in the index for filtering -- because of the leading wildcard.

If there are only 3 columns in the table then a 3-column index would be "covering".

If there are more then 3 columns in the table, ICP lets all the filtering is done in the storage engine.  Without ICP, only the filtering by zipcode is done in the storage engine; the rest would be handed off to the Handler.


Measure CPU and Memory usage while running pgbench

I show some tools I know.

pg_activity  is a simple monitoring tool like top.

pg_statsinfo and
pg_stats_reporter are  high quality utilities that monitor statistics and the activity of PostgreSQL as well as Operating system.

There are some general purpose monitoring tools such as  Cacti and Munin. Those support postgres by providing modules and they can easily be used.

BTW, I know a great book: The Art of Computer Systems Performance Analysis. This gives you some hints for benchmarking.


Max allowed packet changed still replication not starting

Try these steps:

1) Take a mysqldump from master

2) Restore it on a slave, whether it gets restored successfully?

3) You should not keep max_allowed_packet to a high value like 1G or 2G keep it minimal like 16M or 32M and restart Master server.

4) Apply same max_allowed_packet size in slave as well.3

5) Now, stop the slave, execute Change Master .... query accourdingly and start slave

I hope this will work for you.


Set up a MySQL slave using mysqldump to get the initial data

If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:

mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

Since --opt is enabled by default, the following options are already enabled

--add-drop-table
--add-locks
--create-options
--quick
--lock-tables
--set-charset
--disable-keys

If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

The reason this is better to do is that --single-transaction does not protect MyISAM from changing during the dump.

Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:

Step 01) Add this to /etc/my.cnf on the master

[mysqld]
log-bin=mysql-bin

Step 02) service mysql restart

This will enable binary logging on the master.

On the new slave, you can run the following command:

CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Make sure the rep_username exists in the master. If it does not, run this command on the master:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';

Then do the mysqldump to /root/mydata.sql.

Move the /root/mydata.sql from the master to the slave.

Next, execute in the mysql client this command on the slave:

source /root/mydata.sql

This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.

Finally, run START SLAVE; on the slave and mysql replication should get going.

Run SHOW SLAVE STATUS\G to check replication status.

Give it a Try !!!


Putting a condition related to a table in JOIN is better?

As JNK said, for an INNER JOIN, these are going to be the same. You can prove it by comparing actual (don't bother with estimated) execution plans. In this case, they are exactly the same (click to enlarge):

Personally, I like to keep the join conditions and the filter conditions separate. Join conditions go in the ON clause, and filter conditions go in the WHERE clause. This is one of the primary benefits of having explicit INNER JOIN syntax in the first place, which helps to reduce the risk of returning too many rows because of not enough (or even no) join criteria inherent in old-style joins (where filter and join criteria are thrown together) - see this blog post for more details.

JNK is also right though that you need to be careful when you're talking about OUTER JOIN. A simple example:

CREATE TABLE dbo.a(id INT);

CREATE TABLE dbo.b(id INT, name SYSNAME);

INSERT dbo.a(id) VALUES(1),(2),(3);

INSERT dbo.b(id) VALUES(2,N'a'),(3,N'b');

SELECT a.id, b.name
  FROM dbo.a
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id
  AND b.name LIKE N'b%';

SELECT a.id, b.name
  FROM dbo.a
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id
  WHERE b.name LIKE N'b%';
--^^^^^ only difference

DROP TABLE dbo.a, dbo.b;

Results:

id    name
----  ----
1     NULL
2     NULL
3     b

id    name
----  ----
3     b

As you can see from the results, the first query still returns all three rows from a (as you would expect), however the second query turns the OUTER JOIN into an INNER JOIN, and only returns the rows from a with a match from b on all conditions. You may want one behavior or the other, so neither of these is "worse" or "wrong," it's just important to understand the different functionality so you know to write the query to get the results you are after.


With the current design you could have several customers with the same user_id (similarly for merchants). This is a one-to-many association. To solve this problem, simply do not introduce id for table customers, but use user_id as primary key for it, in addition to it being a foreign key for table users. Then you can have only one customer for user. Similarly for table merchants.

Then you can enforce the constraint that users cannot exists without either being a customer or a merchant by defining in your application  functions to create customers or merchants, but not users. Those functions will create the appropriate rows. Finally, is up to you to decide if a customer can be also a merchant or not. In both cases, the function to create the customer or the merchant must take appropriate action.