In the absence of any answers I've explored the issue further myself.

It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much.

I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla configuration. The relations to store that actual signal data were as follows.

Large Object for entire file

CREATE TABLE BlobFile (
    eeg_id INTEGER PRIMARY KEY,
    eeg_oid OID NOT NULL
);

SMALLINT array per channel

CREATE TABLE EpochChannelArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal SMALLINT[] NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

BYTEA per channel in each epoch

CREATE TABLE EpochChannelBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

SMALLINT 2D array per epoch

CREATE TABLE EpochArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals SMALLINT[][] NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

BYTEA array per epoch

CREATE TABLE EpochBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

I then imported a selection of EDF files into each of these relations via Java JDBC and compared the growth in database size after each upload.

The files were:

File A: 2706 epochs of 16 channels, each channel 1024 samples (16385
samples per epoch), 85 MB
File B: 11897 epochs of 18 channels, each channel 1024 samples (18432 samples per epoch), 418 MB
File C: 11746 epochs of 20 channels, each channel 64 to 1024 samples (17088 samples per epoch), 382 MB

In terms of storage cost, here's the size occupied in MB for each case:

Relative to the original file size, Large Objects were about 30-35% larger. By contrast, storing each epoch as either a BYTEA or SMALLINT[][] was less than 10% larger. Storing each channel as a separate tuple give a 40% increase, as either BYTEA or SMALLINT[], so not much worse than storing as a large object.

One thing I hadn't initially appreciated is that "Multidimensional arrays must have matching extents for each dimension" in PostgreSQL. This means that the SMALLINT[][] representation only works when all channels in an epoch have the same number of samples. Hence File C fails to work with the EpochArray relation.

In terms as access costs, I haven't played around with this, but at least in terms of inserting the data initially the fastest representation was EpochBytea and BlobFile, with EpochChannelArray the slowest, taking about 3 times as long as the first two.


Add foreign key constraint to an existing table column, referencing a column in a newly created table

Select all distinct date from sales tables and insert data into the dates tables. After populating data into that table try to create the foreign key constraint.


Is there a way to determine if a certain query need to have an index?

Query:

SELECT name
FROM table_name
WHERE lastname = 'jobs'
ORDER BY date_joined DESC ;

Indexes:

(lastname)
normal index for this type of queries, a simple index on the column in WHERE. It covers all queries that have WHERE last_name = 'some_vale' and will be quite efficient, assuming that the condition is "selective" enough, i.e. that you don't have millions of people with last name 'Jobs':
(lastname, date_joined)
a slightly different index, has the column in the ORDER BY as well. It will perform almost identically to the above index, except it will not require a sort. This is unlikely to be helpful, in most cases.
(lastname, date_joined, name)
a slightly better index, has the column from the ORDER BY and the SELECT lists. It will be slightly more efficient than the normal index, as it is "covering" completely the query. It won't need any sorting and all the values needed will be read from the index, without additional lookups in the main table. Whether this is needed, depends on the requirements about efficiency (will reducing the run time from 20 ms to 5 ms make any difference?).
It will however not be better when the query changes, for example by adding another column in the SELECTlist. So it is an index directed for a very narrow case.

What to do?

As a conclusion, you most probably only need a simple index on (last_name).

  Do I need an index?

Yes, if these queries are common, the difference between not having an index and having one will be the difference between a full table scan and an index seek (with a few additional lookups in the table).
In terms of complexity, that's O(n) vs O(logn), where n is the size of the table, assuming the default B-tree indexes.


Replicating a database that uses Fail Over

That is not true. I've done transactional replication with FCIs for years. Interestingly I cannot find any supporting documentation on the Microsoft site, however it is supported, and it does work.


Install MariaDB and MySQL in CentOS 6 32bit in same server

MariaDB is a total drop-in replacement for MySQL - it even uses the same configuration files and folders.

If you're utterly sold on using both simultaneously, then you'd probably be best compiling from source and configuring all the folders manually. I am speaking from Debian derivative basis, so I may be off the mark regarding yum, but generally the package manager versions tend to be a simple replacement.

I would still run the following before taking the plunge, just in case something goes wrong:
mysqldump -u root -p --all-databases > alldbs.sql. Then you can easily restore with mysql -u root -p < alldbs.sql.

Source: I recently attempted to do this myself, and found that there was no feature I used in MySQL that wasn't totally compatible with MariaDB.


SQL Server query with Linked Server running without Parallelism

Joining to a table via a Linked Server is problematic.  You can workaround the issue by pulling the required rows from the remote table into a local #temp table, and JOINing against that #temp table instead.


Enable TLS 1.2 and disable SSL 3 on SQL Server 2008

In order to be compatible with TLS 1.2, you must be running SQL Server 2008 R2 version 10.50.6542.0 or later.

To get to that version level, you need to install Service Pack 3 for SQL Server 2008 R2, then install the hotfix from KB3144114 from https://support.microsoft.com/en-us/Hotfix/KbHotfix?kbnum=3144114&kbln=en-us which will update your SQL Server to v 10.50.6542.

Be aware, the above hotfix should never be installed on a production system unless you've extensively tested it in your test-environment first.


Should developers be able to query production databases?

It really depends on whether the developer has any support responsibilities.  If they are on the hook for third line support then they will probably need to look at the production database to do this.

Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.

For most development purposes, mirrors or snapshots of the production database will be adequate, and probably better than the live production database.  If you are doing anything involving integration then you will want stable database environments where you can control what's in them.  Anything involving reconciliation will also need the ability to look at a controlled point in time.

If the problem is that you don't have production mirror environments or any means to put a copy of production data somewhere for your developers then this is a somewhat different question.  In that case your developers really need at least one mirror environment.  If you can't see what the problem is in the data then it's kind of hard to troubleshoot it.


How to keep Mongodb log small via the conf file (or other persistent method)

You might already know the answer just posting the answer here for someone else like me ending in this post :D

vi /etc/mongo.conf
smallfiles = true

This would set smallfiles true once you restart your mongodb.

UPDATE:

Rounding a little bit more the answer smallfiles option in mongo what does is reducing the initial and journal data files size, this is useful if your machine do not have to much space or if you are gonna have multiple databases with small data each.

I my personal opinion smallfiles do not increase performance but like mentioned before could help for this specific scenarios another example is with Docker if you are not using -v "path/to/local/drive:"/data/db" then is better to set smallfiles true in my case if i don't the container breaks because it can't allocate the default data file space.

To add smallfiles to the mongo.conf you can do:

echo "smallfiles = true" >> /etc/mongo.conf
service mongod restart

More detailed info here: http://docs.mongodb.org/manual/reference/configuration-options/#storage.mmapv1.smallFiles

Hope this helps :D

Important UPDATE:
Starting in version 4.2, MongoDB removed smallfiles option.

More details at: https://docs.mongodb.com/manual/reference/program/mongos/#bin.mongos

So for latest available options, check the updated manual at: https://docs.mongodb.com/manual/tutorial/rotate-log-files/


SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

I know this question, based on the Title, is mainly concerned with the PREEMPTIVE_OS_DELETESECURITYCONTEXT wait type, but I believe that is a misdirection of the true issue which is " a customer who was complaining about high CPU usage on their SQL Server ".

The reason I believe that focusing on this specific wait type is a wild goose chase is because it goes up for every connection made. I am running the following query on my laptop (meaning I am the only user):

SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type = N'PREEMPTIVE_OS_DELETESECURITYCONTEXT'

And then I do any of the following and re-run this query:

open a new query tab
close the new query tab
run the following from a DOS prompt: SQLCMD -E -Q "select 1"

Now, we know that CPU is high so we should look at what is running to see what sessions have high CPU:

SELECT req.session_id AS [SPID],
       req.blocking_session_id AS [BlockedBy],
       req.logical_reads AS [LogReads],
       DB_NAME(req.database_id) AS [DatabaseName],
       SUBSTRING(txt.[text],
                 (req.statement_start_offset / 2) + 1,
                 CASE
                     WHEN req.statement_end_offset > 0
                        THEN (req.statement_end_offset - req.statement_start_offset) / 2
                     ELSE LEN(txt.[text])
                 END
                ) AS [CurrentStatement],
       txt.[text] AS [CurrentBatch],
       CONVERT(XML, qplan.query_plan) AS [StatementQueryPlan],
       OBJECT_NAME(qplan.objectid, qplan.[dbid]) AS [ObjectName],
       sess.[program_name],
       sess.[host_name],
       sess.nt_user_name,
       sess.total_scheduled_time,
       sess.memory_usage,
       req.*
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess
        ON sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) txt
OUTER APPLY sys.dm_exec_text_query_plan(req.plan_handle,
                                        req.statement_start_offset,
                                        req.statement_end_offset) qplan
WHERE req.session_id <> @@SPID
ORDER BY req.logical_reads DESC, req.cpu_time DESC
--ORDER BY req.cpu_time DESC, req.logical_reads DESC

I usually run the above query as it is, but you could also switch which ORDER BY clause is commented out to see if that gives more interesting / helpful results.

Alternatively you can run the following, based on dm_exec_query_stats, to find highest-cost queries. The first query below will show you individual queries (even if they have multiple plans) and is ordered by Average CPU Time, but you can easily change that to be Average Logical Reads. Once you find a query that looks like it is taking a lot of resources, copy the "sql_handle" and "statement_start_offset" into the WHERE condition of the second query below to see the individual plans (can be more than 1). Scroll to the far right and assuming there was an XML Plan, it will display as a link (in Grid Mode) which will take you to the plan viewer if you click on it.

Query #1: Get Query Info

;WITH cte AS
(
   SELECT qstat.[sql_handle],
          qstat.statement_start_offset,
          qstat.statement_end_offset,
          COUNT(*) AS [NumberOfPlans],
          SUM(qstat.execution_count) AS [TotalExecutions],

          SUM(qstat.total_worker_time) AS [TotalCPU],
          (SUM(qstat.total_worker_time * 1.0) / SUM(qstat.execution_count)) AS [AvgCPUtime],
          MAX(qstat.max_worker_time) AS [MaxCPU],

          SUM(qstat.total_logical_reads) AS [TotalLogicalReads],
   (SUM(qstat.total_logical_reads * 1.0) / SUM(qstat.execution_count)) AS [AvgLogicalReads],
          MAX(qstat.max_logical_reads) AS [MaxLogicalReads],

          SUM(qstat.total_rows) AS [TotalRows],
          (SUM(qstat.total_rows * 1.0) / SUM(qstat.execution_count)) AS [AvgRows],
          MAX(qstat.max_rows) AS [MaxRows]
   FROM sys.dm_exec_query_stats  qstat
   GROUP BY qstat.[sql_handle], qstat.statement_start_offset, qstat.statement_end_offset
)
SELECT  cte.*,
        DB_NAME(txt.[dbid]) AS [DatabaseName],
        SUBSTRING(txt.[text],
                  (cte.statement_start_offset / 2) + 1,
                  CASE
                      WHEN cte.statement_end_offset > 0
                          THEN (cte.statement_end_offset - cte.statement_start_offset) / 2
                      ELSE LEN(txt.[text])
                  END
                 ) AS [CurrentStatement],
        txt.[text] AS [CurrentBatch]
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPUtime DESC

Query #2: Get Plan Info

SELECT  *,
        DB_NAME(qplan.[dbid]) AS [DatabaseName],
        CONVERT(XML, qplan.query_plan) AS [StatementQueryPlan],
        SUBSTRING(txt.[text],
                  (qstat.statement_start_offset / 2) + 1,
                  CASE
                        WHEN qstat.statement_end_offset > 0
                        THEN (qstat.statement_end_offset - qstat.statement_start_offset) / 2
                        ELSE LEN(txt.[text])
                  END
                 ) AS [CurrentStatement],
        txt.[text] AS [CurrentBatch]
FROM sys.dm_exec_query_stats  qstat
CROSS APPLY sys.dm_exec_sql_text(qstat.[sql_handle]) txt
OUTER APPLY sys.dm_exec_text_query_plan(qstat.plan_handle,
                                        qstat.statement_start_offset,
                                        qstat.statement_end_offset) qplan
-- paste info from Query #1 below
WHERE qstat.[sql_handle] = 0x020000001C70C614D261C85875D4EF3C90BD18D02D62453800....
AND qstat.statement_start_offset = 164
-- paste info from Query #1 above
ORDER BY qstat.total_worker_time DESC


How to set roles and users with correct permissions on Postgres?

The concept of groups & users has largely been done away with in PostgreSQL, with the terms USER and GROUP may be interchangeably used with ROLE. See documentation.

Suffice to say that what you want to do is create your "group" role first like this:

CREATE ROLE devs NOLOGIN;

Then grant all desired permissions to devs. Now create the users with IN ROLE devs clause like this:

CREATE ROLE jsmith LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
CREATE ROLE sjones LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
...

This will allow "real" users to login & inherit all the same permissions but no one can explicitly become devs (or the "group" role).


PostgreSQL: Save roundtrips / Network performance

there is nothing on PostgreSQL side to deal with this.

JDBC side i know nothing about but quick google search points to no

A solution i use when working with large data sets is to fetch only a few records at a time  1000 to 10000.

Select * from tablename where tablename.id > 1 ordered by id limit 1000

next set of queries

Select * from tablename where tablename.id > 1000 ordered by id limit 1000

Some people call this  asynchronous  fetching/paging.

Some background
Most db connections run in synchronize mode by default, "command sent"  client waits until all data is delivered.  This makes the app appear to hang for long periods of time, this makes the users think the app has crashed or something else has gone wrong.

Using asynchronous technique the data is continuously fetch and  delivered using a background process/thread while the rest of the code is free to continue working giving the user the appearance the application is very snappy.

Its allot harder to write code around fetching only a few records at a time but can be  worth it on big data sets, as the app does not have to wait on all the data

There are several tools to make this easier,  such as creating cursor on the server and fetch only X records from the server. But there are draw backs to cursors,  eating server resources,  required to run clean up code.

Some client libraries makes this easy by directly supporting cursor other do not support this feature  JDBC does not appear to support this so it will have to be written.


I was correct, and the query above worked as desired. For anyone stumbling on this in the future, the format seemed to work given my context.

UPDATE
    features,
    images
SET
    features.deleted = 1
WHERE
    features.objectType = 'image'
    AND images.id = features.objectId
    AND images.uploaded = 0;

Thanks for your time everyone :)


New login is created but 'sa' cant assign grant/deny permissions to user in SQL Server 2008 R2?

Syntax is OBJECT::Schema.Table not OBJECT::Database.Table

e.g.

use test
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dbo.T1 to public


Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server

When SQL Server creates the index on the computed field, the computed field is written to disk at that time - but only on the 8K pages of that index. SQL Server can compute the InvoiceStatusID as it reads through the clustered index - there's no need to write that data to the clustered index.

As you delete/update/insert rows in dbo.Invoice, the data in the indexes is kept up to date. (When InvoiceStatus changes, SQL Server knows to also update IX_Invoice.)

The best way you can see this for yourself is to actually do it: create these objects, and execute updates that touch the InvoiceStatusID field. Post the execution plan (PasteThePlan.com is helpful for this) if you want help seeing where the index updates are happening.


Null column for username and password when using Login with *social network*

I'd rather separate user and authentication mechanism/provider. Application may allow more than one authentication provider (say , logins with facebook, openid, etc). Adding columns/constraints every time you add a new authentication option doesn't sound like a very good idea to me (even though that doesn't happen often, such an action usually requires lots of changes on application side).
Something like

user(user_id);
authenticator (authenticator_id, name, description, authentication_type);
user_authentication(user_id, authenticator_id, authentication_identity, authenticator_token) ;

seems more general to me.

** In case of username/password authentication you can keep username in authentication_identity and password (surely not plain text) in authenticator_token. If you feel the pair authentication_identity/authenticator_token doesn't cover all the cases you can add couple more tables that store information specific to particular authentication_type


Duplicate table structure with different data

Since you have determined that the two database approach is not right for you, you want to merge the two databases. (And I agree for all the little value that is)

(1) One suggestion, the simplest, is to rename one of the tables and make the needed changes to the code to use the proper table instead of the proper database and table.

(2) Another suggestion would be to merge the two tables into a single table. You would need to add a column to differentiate between the "Quotes" rows and the "Jobs" rows.

Also, you would need to make sure that your primary keys are unique between the two tables. If these rows were generated with Access ascending keys it is likely that you have duplicate keys between the two tables.

If this is the case, you would need to change the Primary Keys in, let us say "Jobs", to avoid any PK conflicts with "Quotes" rows.  Then also change all the referencing tables to have Foreign Key values that reference the new Primary Key values.

If there are many duplicate tables, then repeat as needed to bring everything into the new united database schema.

This will be a bit of work to set up, but once completed you should not need to think about that any more.


mysqldbcompare object definitions differ

Is this the only table with AUTO_INCREMENT?

How many nodes?

SELECT MAX(id) from each node.

I'm pretty sure there is no data inconsistency -- each replicated statement will be done using RBR, so it will include the actual id, and not look at that setting on the CREATE TABLE.

(I do not know exactly where the 303 and 301 come from.)


Trouble Enabling Query Caching on MySQL 5.6?

You are right. Your problem is actually very simple.
In Windows, mysqld is looking for my.ini, not my-default.ini
SUGGESTION
Open Windows Command Line (as Administrator) and run the following:
cd "C:\Program Files\MySQL\MySQL Server 5.6"
copy my-default.ini my.ini
net stop MySQL
net start MySQL

Keep in mind that MySQL was running with default settings and no my.ini
So, maybe the lines above
[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

should be the only lines in my.ini


Access denied to user ODBC, but trying to login as root

The only thing I can suggest is to explicitly choose a host by doing one of the following:

mysqldump -hlocalhost -uroot -ppassword ideal_inv > ideal_inv.sql

mysqldump -h127.0.0.1 -uroot -ppassword ideal_inv > ideal_inv.sql


If you are using MySQL 5.1+, you can create a scheduled event in MySQL or a cronjob to poll the processlist from the information schema.

What queries do you look for in the processlist? Any DB Connection that ...

runs longer that 30 seconds
not used for replication
runs a SELECT query

Here is that query:

SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:

CREATE DATABASE myutil;
USE myutil
DELIMITER $$
DROP PROCEDURE IF EXISTS `Kill_Long_Queries` $$
CREATE PROCEDURE `Kill_Long_Queries` ()
BEGIN

    CREATE TABLE IF NOT EXISTS IDs_To_Kill;
    (ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL) ENGINE=MyISAM;
    TRUNCATE TABLE IDs_To_Kill;

    INSERT INTO IDs_To_Kill (ProcessID)
    SELECT id FROM information_schema.processlist
    WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

    SET @ndxnext = 0;
    SELECT MAX(ndx) INTO @ndxlast FROM IDs_To_Kill;
    WHILE @ndxnext < @ndxlast DO
        SET @ndxnext = @ndxnext + 1;
        SELECT id INTO @ProcessID from IDs_To_Kill WHERE ndx = @ndxnext;
        SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
        PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$
DELIMITER ;

If you want to go beyond terminating the query and just terminate the DB Connection, replace

SET @SQL = CONCAT('KILL QUERY ',@ProcessID);

with this

SET @SQL = CONCAT('KILL ',@ProcessID);

Launch Stored Procedure Using MySQL Event

USE myutil
DELIMITER $$
CREATE EVENT kill_query_event
    ON SCHEDULE EVERY 1 MINUTE
    STARTS (NOW() + INTERVAL 1 MINUTE + INTERVAL SECOND(NOW()) SECOND) DO
BEGIN
    CALL Kill_Long_Queries();
END $$
DELIMITER ;

Launch Stored Procedure Using cron

In the OS, set up the crontab to go off every minute as follows

0 * * * * mysql -u... -p... -Dmyutil -ANe"CALL Kill_Long_Queries()"

CAVEAT for MySQL 5.0 Users

Since there are no events in the MySQL 5.0 and prior, you will have to do something like this:

for PROC_TO_KILL in `mysql -h... -u... -p... -ANe"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -ANe"KILL QUERY ${PROC_TO_KILL}" ; done

SPECIAL CAVEAT

Without a MySQL Event, you would have to connect to mysql each time and poll the processlist. In the event that there are too many connections or if the mysqld starts blocking connection due to connect errors, your only hope is to write a perl script whose sole job is to kill long running query and using only one live session at the same time.

I wrote a perl script two years to run flush hosts every 10 minutes (How do you tell which host is close to being blocked in MySQL?)

Here is that script

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

I am not a perl expert, but I suggest that you alter this script to collect and kill the process IDs. Save it to a file called killquery.pl. All you need to do is launch this once from the command line as a background process.

nohup perl killquery.pl 5 30 &

This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second

Give it a Try !!!


DB2 ARCHIVE mode - Can all archive logs be deleted after doing the FULL OFFLINE backup?

There are two ways to determine which logs to keep. One you write yourself. You would need to issue a db2 list history backup all for <db name>. This will show you the history file and locate the last backup and the last needed archive log. Anything prior to that can be deleted.

Now you can add in automation.

First step set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database you are connected to. This will now allow you to use the history file to prune unneeded backups and archive logs. You still will have to use fancy scripting and text parsing, but you can do it. (You can probably also locate a table in DB2 to get at the data. That may be easier to grab the data.) But then you can issue db2 prune history <timestamp> [with force option] and delete to remove unneeded backups. And db2 prune logfile prior to <log file name> to delete unneeded archived logs.

This is great, but why not let DB2 handle it for you all the way? Set db2 update db cfg using AUTO_DEL_REC_OBJ ON for the database in question. Then you can set db2 update db cfg using NUM_DB_BACKUPS <number>. This set the maximum number of backups you wish to retain for the period of REC_HIS_RETENTN days. Now set db2 update db cfg REC_HIS_RETENTN <number> to the number of days. If you set NUM_DB_BACKUPS to 3 and REC_HIS_RETENTN to 2, it will keep 3 backups for 2 days. In other words a backup needs to be at least two days old before it will be consider for removal. Once a backup is older than 2 days, it will check to see if there are more than 3 backups. If this is true, then it will remove the older backups.

Now for the logs.....As long as you set LOGARCHMETH1 to DISK rather than LOGRETAIN, DB2 will also clean up any archive logs that are no longer necessary. It will retain archive logs between each of the saved backups, but delete everything else. Example setting: db2 update db cfg using LOGARCHMETH1 'DISK:/archive/logs'. If you set to LOGRETAIN, then DB2 will not auto clean the logs and you are left to do it yourself.


How does mydumper's ''--use-savepoints" option reduce metadata locking

I had to do a little digging in the source code of mydumper to find the answer to this question. If you look at the actual source code of mydumper:

You will find the following starting on line 415 as part of the process queue:

if(use_savepoints && mysql_query(thrconn, "SET SQL_LOG_BIN = 0")){
    g_critical("Failed to disable binlog for the thread: %s",mysql_error(thrconn));
    exit(EXIT_FAILURE);
}

This shows using --use-savepoints in mydumper requires the ability to turn off the MySQL bin log. I attempted this on my own MariaDB server which still uses MySQL as the base DBMS and got the following error when using a non administrator account:

  MariaDB [(none)]> SET SQL_LOG_BIN = 0;
  ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

From what I was reading of the actual code and testing this condition in my own MySQL server my understanding is you need "SUPER privilege" as mydumper will be disabling logging to the binlog while it runs. This is part of "the enabling or disabling logging" power of a SUPER mentioned in the DOCS.

More specific info on the binlog is here:

http://dev.mysql.com/doc/refman/5.6/en/set-sql-log-bin.html

As far as savepoints go:

http://dev.mysql.com/doc/refman/5.6/en/savepoint.html

After reading through the manual and this bug report it looks like if savepoints are released they will release locks on the table being worked on which can prevent locking issues which have been seen on mysqldump as recently as MySQL 5.5.

I hope this gives a little more insight into the mydumper tool.


Is any caching mechamism used for non-persisted computed columns?

The non-persisted computed column values are not cached in memory.

Here's a brief-ish demo that confirms this.  First I'll create a database for our testing:

CREATE DATABASE CC_BufferPool_Test;
GO
USE [CC_BufferPool_Test];
GO

And then create a table without a computed column as a baseline.  The column sizes in this table are specifically chosen so that 4 rows will exactly fill one 8KB data page (and thus one 1 page in SQL Server's buffer cache):

CREATE TABLE T
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T (Filler) VALUES(REPLICATE('A', 2011));
GO 4

Next I'll create the same table, but with the addition of a computed column:

CREATE TABLE T_WithCC
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    C2 AS 'Row #' + CONVERT(CHAR(8), C1, 1),
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T_WithCC(Filler) VALUES(REPLICATE('A', 2011));
GO 4

Next, I'll make sure these 8 rows are written to disk, and then clear the buffer cache, and then select all 8 rows so that they get pulled into memory:

CHECKPOINT
DBCC DROPCLEANBUFFERS;

SELECT * FROM T;
SELECT * FROM dbo.T_WithCC;

Finally, I'll use this query that I totally did not steal from Aaron Bertrand*

From that, you can see there is only one page in memory for each of those two tables.  If the computed column were stored in memory, it would have pushed the second table's rows onto a second page.

You can also see the calculation occurring each time in the actual execution plan:

If you do need to "cache" non-persisted computed columns for specific queries, you can actually create a nonclustered index on them.  That way they are stored in the index, but not on the base table.

*see Determine SQL Server memory use by database and object, and here's the code:

;WITH src AS
(
    SELECT
        [Object] = o.name,
        [Type] = o.type_desc,
        [Index] = COALESCE(i.name, ''),
        [Index_Type] = i.type_desc,
        p.[object_id],
        p.index_id,
        au.allocation_unit_id
    FROM sys.partitions AS p
        INNER JOIN sys.allocation_units AS au
            ON p.hobt_id = au.container_id
        INNER JOIN sys.objects AS o
            ON p.[object_id] = o.[object_id]
        INNER JOIN sys.indexes AS i
            ON o.[object_id] = i.[object_id]
            AND p.index_id = i.index_id
    WHERE
        au.[type] IN (1,2,3)
        AND o.is_ms_shipped = 0
)
SELECT
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type,
    buffer_pages = COUNT_BIG(b.page_id),
    buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
    INNER JOIN sys.dm_os_buffer_descriptors AS b
        ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type
ORDER BY buffer_pages DESC;


show list of master tables in postgresql


How do I order by NULLS LAST if my database doesn't support it?

There is a (closed) connect item requesting support for NULLS LAST in SQL Server.

A couple of other ways would be

ORDER BY
        CASE WHEN FullName IS NULL THEN 1 ELSE 0 END,
        FullName,
        Login

I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider that on some case sensitive collations z sorts before Z and on others the reverse applies, will that be an issue? Similarly will "Željko Ivanek" sort before "ZZZZZZZ" reliably on all collations? Are there other characters from other languages that might exist in a name and sort after "Z"?

A potentially more efficient solution if there is an index on FullName, Login (as it hopefully will avoid a sort)

CREATE TABLE [User]
  (
     FullName VARCHAR(100),
     Login    VARCHAR(100),
     INDEX IX (FullName,Login) /*Use separate create index if < 2014*/
  );

WITH T
     AS (SELECT 1 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NOT NULL
         UNION ALL
         SELECT 2 AS Grp,
                *
         FROM   [User]
         WHERE  FullName IS NULL)
SELECT FullName,
       Login
FROM   T
ORDER  BY Grp,
          FullName,
          Login


How to sum the buy order and sell order in one row per each ticker

below should help you :

CREATE TABLE dbo.Trading
        ([Ticker] int, [iOrder] int, [Price] int, [Amount] int)
    ;

INSERT INTO dbo.Trading
    ([Ticker], [iOrder], [Price], [Amount])
VALUES
    (1, 1, 90, 180),
    (1, 1, 89.5, 179),
    (1, -1, 91, 91),
    (2, 1, 5, 20),
    (2, -1, 4, 16),
    (2, -1, 3, 9)
;

SELECT Ticker
    ,sum(CASE
            WHEN iorder = 1
                THEN Amount
            ELSE 0
            END) AS BuyAmount
    ,sum(CASE
            WHEN iorder = - 1
                THEN amount
            ELSE 0
            END) AS SellAmount
FROM dbo.Trading
GROUP BY Ticker


How to update sys.login_token without restart SQL Server Service

The client refreshes the login token, not SQL Server.

Either log out/in, or lock+unlock their PC
This forces an AD authentication(token rebuild)

There also other methods...

Serverfault also suggest crashing explorer.exe on the client
I've not tried the klist approach. YMMV

Until the token is rebuilt, the login token as read by SQL Server will be stale.


First thing is after changing recovery model from full to simple you loose point in time recovery. Is this database production database(which i feel it is)? if so you are not doing correct thing. If database fails at this point in time you would loose point in time recovery and would loose data are you ok with it.

If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, its strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction lo

  My question is. Can I do it while database in use? It's used all the time being the printer database.

Yes you can do it. But is it not possible for you to take transaction log backup on some other drive. Transaction log backups will truncate logs(if no long running transaction still requires it) and then you can shrink the log file. You must also note that shrinking logs also cause performance issue if you are doing it for just one time its ok.

Why did log file grew out of proportion have you thought about it ? I guess because you did not took transaction log backups. Please schedule proper transaction log backup for the database

if you don't require point in time recovery why not let database in simple recovery mode and you can still take full and differential backups.


.NET SQL Server Authentication Schema issue

I'm not sure what is preventing you assigning a default schema to the user. You can simply use:

ALTER USER [NT AUTHORITY\Authenticated Users]
WITH DEFAULT_SCHEMA = [Production]

Obviously replacing the user/schema names as appropriate.

However, it would be best if you could update the code to use a schema prefix - some more reading on that is here (functional reasons) and here (performance reasons).


How do I find the cumulative distance between geometry points in SQL Server 2014?

OK, then this is the query, SQL Server 2012+ . I'll use test data instead of your query and omit all customer, order details but orderid. The query also doesn't use spatial features which doesn't help much here as we use no geography but plain geometry distance.

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
    values
        (1, 1, 10, 20),
        (1, 2, 15, 30),
        (1, 3, 25, 5),
        (2, 1, 15, 20),
        (2, 2, 17, 30),
        (2, 3, 27, 8)
    ) t(orderid, id, Coord_X, Coord_Y)
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points pto on pto.orderid = pfr.orderid and pto.rn=pfr.rn+1

May I say that this cumdist is far from being shortest in many cases.

Trying to use the second query from the question as a data source, not tested:

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
   -- data source
    SELECT odrerid = 1, -- the data source returns single transportation order or route i suppose
        id = t_Addresses.ID -- probably you have it. It is intended to be a separate id for every point to be visited
        Coord_X = t_Addresses.Coord_X,
        Coord_Y = t_Addresses.Coord_Y
    FROM t_Addresses
    WHERE t_Addresses.Cust_ID IN (
            SELECT DISTINCT t_Orders.Cust_ID
            FROM t_Orders
            WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1)
        AND ( (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
           OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y)) )
    --
    ) t
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points as pto on pfr.orderid = pto.orderid and  pto.rn=pfr.rn+1

I also changed the WHERE to:

WHERE t_Addresses.Cust_ID IN (..) AND ( (..) OR (..) )

My guess is you need proper Cust_ID AND points within a quadrant.

I made some assumptions, please correct me if they are wrong.


SQL Server Replication :: Dedicated Distributor Hardware Requirements

Running on a VM is a good idea as it's hard to define resource requirements but in most cases distributors are quite lightweight. The main factors are number of subscribers, transactions count and the number of articles in replication.

Some tips to help:

Only replicate what you need to, this includes SPs, views etc.
Using pull will reduce CPU and memory requirements on the distributor.

This is harder to implement in Web edition but possible with scripts.
If you have a lot of push agents running, you should allow more memory outside of SQL.
Consider increasing the desktop heap size if using push and have a lot of agents. (this is a registry change)

Choose the right type of replication.

Snapshot is light on distributors as it doesn’t have the overhead of the log reader or clean-up jobs but heavier on the network and the subscriber when pushing out.

Enterprise edition doesn’t provide any real performance benefits on the distributor.

I hoped that merry-go-round reads would but no, it’s mostly in CPU when problems occur.

You may find it helpful to reserve some cores for the network as large distributors can get overwhelmed during imports or clean-up jobs and struggle to get the data out.

I’m talking 1000 subscribers and 100,000 articles (not my idea).

Throwing CPU at the distributor rarely fixes the high CPU issue. You need to treat the distribution databases like any other database and tune it (I believe Denny Cherry has an article which recommends some indexes).
CPU can also be caused by the locking and deadlocking during the clean-up job or large transactions failing to be delivered causing the subscriber agents to scan the entire table. You may find that reducing the frequency of the clean-up job actually help overall throughput but could impact latency.

In a nutshell, if replication is configured correctly, updates are batched properly (don’t update millions of rows in one transaction) and the subscribers are up to date then it’s happy.


How To Restrict a Column To Only A Default Value

For this sort of specific requirement, I would use an INSTEAD OF trigger set on the table. Here's a simple example showing how this would work.

-- I chose this table structure (nullable elements) so no error would be thrown on insert,
--  and because no values are expected from the user for these columns
CREATE TABLE Bob (
    dataElement varchar(200) not null,
    LastUpdate datetime,
    UnchangeableID UniqueIdentifier
    )
GO

CREATE TRIGGER tr_MakeItHard1 ON Bob
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Bob (DataElement, LastUpdate, UnchangeableID)
    SELECT dataElement, GETDATE(), NewID()
    FROM inserted
END

GO
CREATE TRIGGER tr_MakeItHard2 on Bob
INSTEAD OF UPDATE
AS
BEGIN
    -- Here, you can either choose to allow manual updates of the LastUpdate value
    -- from the insert, or only update the data here. I chose to only allow updates
    -- here.
    Update Bob
    SET dataElement = i.dataElement,
        LastUpdate = GetDate()
    FROM inserted i
END
GO

DECLARE @uid uniqueidentifier

SELECT @uid = NewID()

INSERT INTO BOB values ('Data Element Insert', '20100101', @uid)

SELECT b.*,
    @uid as Generated
FROM Bob b

DECLARE @uid uniqueidentifier
SELECT @uid = NewID()

UPDATE Bob
SET dataElement = 'Data Element Update',
    LastUpdate = '20120101',
    UnchangeableID = @uid

SELECT b.*,
    @uid as Generated
FROM Bob b

/*
Results of the queries -- the top row is from the INSERT, the row below is from the UPDATE

dataElement             LastUpdate              UnchangeableID                          Generated
Data Element Insert     2017-01-05 08:59:12.187 7829000B-5BC8-4488-A006-DCB2933C5C95    3670E5AA-FBD4-49C0-88CF-8E2DE9F89BFC
Data Element Update     2017-01-05 09:04:57.527 7829000B-5BC8-4488-A006-DCB2933C5C95    86E9617D-F238-4E46-BE8F-85A318853593

*/


These "indexes" are actually not indexes at all. They're called heaps, which is an internal structure that holds the table data without using any ordering mechanism like an index does.

Since there's no order, there's nothing to do as far as reorganizing or rebuilding goes, so these can be safely ignored as far as consideration for index maintenance. I would recommend using a 3rd-party solution for index maintenance though.

If you're new to index structures in SQL Server, I have a video here that explains them.


There is a better option with json_build_object() in Postgres 9.4+:

SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM   myt;

But there is also a simpler and faster way with row_to_json() in Postgres 9.3:

SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data
FROM   myt;

db<>fiddle here
Old SQL Fiddle on Postgres 9.6.

Related answers:

Select columns inside json_agg
Return as array of JSON objects in SQL (Postgres)
Return total number of rows and selected (aggregated) data


Would it be better to do a sepparate query instead of a left JOIN?

By your fine naming conventions I can take it that the fk_recipes_id column is indexed. This alone suggests that the query should be OK (pending cardinality of fk_recipes_id). If you further expand that index to include (fk_recipes_id, in_use), that would improve your query's execution plan.

Generally speaking, it is better to do a JOIN (including a LEFT one) within your database than to loop in your application code and submit multiple queries.

I can't know for sure what goes on in your app; you should indeed just test it as @Jon suggests. With a large dataset I suspect you will see the difference very quickly (and it will be in favour of the database JOIN).


Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

USE db_99;
do something with db_1 or 2 or 3;

will not replicate because of your binlog-do-db and replicate-do-db.  (The latter is redundant in your case.)

This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement.

Is it a "bug"?  Well, a lot of people get burned by it.


You seem to be making the query a lot more complicated than it needs to be, and while I agree with Erik that the results you want don't seem to make any logical sense (if you want total bills to a doctor, then why have individual rows for each patient?), here is a simpler way:

DECLARE @Startdate date = '20170101',
        @Enddate   date = '20170131';

;WITH data AS
(
  SELECT doctorname, patientid, bill = SUM(totalbill) OVER (PARTITION BY doctorname),
    r = ROW_NUMBER() OVER (PARTITION BY doctorname ORDER BY doctorname)
  FROM @TotalBill
  WHERE vdate >= @Startdate
    AND vdate <  DATEADD(DAY,1,@Enddate)
)
SELECT doctorname, patientid, totalbill = COALESCE(CASE r WHEN 1 THEN bill END, 0)
FROM data;

A couple of notes:

don't use datetime for your variables when the column is date
don't use messy and unnecessary CONVERT() functions (why convert date to date?)
don't use unsafe regional formats like 'mm/dd/yyyy' - further reading
don't use BETWEEN for any range queries - further reading

(While it's true that BETWEEN works fine for queries only involving date queries, it is far too easy to copy that logic elsewhere, where it doesn't work -
just be consistent.)

also see this post about "dating responsibly"


So the best result I've managed to come up with is adding the following improvements
- optimising the queried table (creating a new temporary table with suggested indexes)
- adding a path element to check I'm not revisiting an existing part of the path
- adding a depth counter as a limiter. This does however mean I'm consciously choosing not to have the full result set

  select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID

  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
  drop table #temp2;
with cteNext as
(
    select a.*,
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc], 0 as [depth]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%'
           then 1
           else 0
         end as [cyc], n.depth+1 as [depth]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0 and n.depth <=11
    )

select *
into #temp2
from cteNext
where cyc =0

The initial purpose of this data is "fluffy", I'm generating a graph-map out of it to show our workflows so a limited depth is fine in the first instance at least. But better answers accepted if anyone has any


What is the best architecture to run Oracle sucessfully run on VMWare?

You can run Oracle under VMware just fine.  The big question is what is the current hardware spec and what VMware version and edition will be hosting the database?


How to install Distributed Replay (dreplay.exe) for SQL Server 2017

@Randi Vertongen: You really find dreplay.exe in the location you mention just by installing the DReplayClient and the DReplayController from the SQL 2017 ISO? I just installed the Controller and the Client and it is definitely not there. As mentioned in my post in the MSDN Forum  it can be installed by installing SSMS 17.x which I am going to do now - but that cannot be the solution. I have opened a Connect Item back in the days - Maybe you would like to upvote it:
https://feedback.azure.com/forums/908035-sql-server/suggestions/38242585-where-is-dreplay-exe


SQL Server "fix" query for Hash Match instead of Nested Loops

If you are going to this you should at least use the OPTION ( HASH JOIN ) syntax instead.  Using the LEFT HASH JOIN syntax implicitly enforces the join order.

This is not desirable for hash joins where normally you would expect the smaller of the two tables being joined to be the build input, the top-most table in the execution plan.  Using the option syntax allows the optimizer to decide at run-time which is the best join order.  See these two articles for further reading:

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

https://msdn.microsoft.com/en-GB/library/ms173815.aspx


I have figured out what's wrong in this, actually nothing wrong. The glitch here is the mapping.

I was mapped my table names in prm file like below.

MAP HR.TEST, TARGET bqtest.TEST

I was thinking in bqtest dataset, I can see the Test table, But its not true. Goldengate will convert your mapping as bqtest_test. This is the actual table name. So you can see that this table got created automatically.


After having all of this typed up... I had an epiphany. I've seen it mentioned in slightly different context around the web put didn't put it together until now. You need to execute the following grant:

grant administer database trigger to mydba;

This is provided via the DBA role, but I believe a direct grant is necessary to compile the trigger on the fly.


need to correct this Query/Loop to find and start "new branch" in the output

SOLVED (yay!):

I simply put a Start ['s'] toggle, 1/0, in ASSOCS

ASSOCS
p_id    a_id   s   p_to_a      a_to_p
1       2      1   employs     worked for
1       3      0   knows       knows
3       4      0   workedwith  worked with
4       6      0   knows       knows
5       3      1   brother to  sister-in-law to
6       7      1   wife of     husband of
7       3      0   knows       knows
5       1      0   test        test_

and dug it out in my query (a.s AS s):

SELECT a.p_id, a.a_id,
    CONCAT(p1.fname,' ', p1.lname) as name1,
    p_to_a,
    CONCAT(p2.fname,' ', p2.lname) as name2, a.s AS s, a.info
        FROM assocs a
        INNER JOIN persons p1 ON p1.person_id = a.p_id
        INNER JOIN persons p2 ON p2.person_id = a.a_id

stored it and called it in place of the $level switch:

if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    if ($data[$pid]['s']==1) {
     echo "
{$data[$pid]['name']}
";
}

/* was » if ($level==0) { */

right, I can't believe it worked, either.


How to create a TSVECTOR column in a materialized view?

Why don't you drop the materialized view ,and recreate it in a transaction? You can't update a materialized view. They're a view that's been written to disk.

Instead of

CREATE MATERIALIZED VIEW mv AS
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
[...]
UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

Just do

BEGIN;
  DROP MATERIALIZED VIEW IF EXISTS mv;
  CREATE MATERIALIZED VIEW mv AS
     SELECT
       id,
       textsearchable_index_col = to_tsvector('english', coalesce(title,''))
     FROM tbl
COMMIT;

You don't have to have a materialized view for this though, yo can create a functional index which will work on the table directly

CREATE INDEX ON tbl USING GIN (to_tsvector('english', title));

Or, just use the default cast..

CREATE INDEX ON tbl USING GIN ((title::tsvector));

And then depending, on your index condition,

SELECT * FROM tbl
WHERE 'foo & bar' @@ to_tsvector('english', title);

SELECT * FROM tbl
WHERE 'foo & bar' @@ title::tsvector;


Shot in the dark: It means you client has reached the configured data/log file size and is now auto-growing them as a snail pace. Make sure you properly pregrow the data and the log files before your data transfer.


MySQL queries still slow after deleting bunch of records

Rather than change a pair of tables to InnoDB, it would be better for every table in the database to be converted as InnoDB is a much better storage engine. That said, MyISAM is workable if you don't mind babysitting it every once in a while.
Do I need to clear cache or optimize table in order to see the effects?
⇢ The database engine will clear the cache on its own, but you can force this by restarting the server. Optimising the tables is certainly recommended for MyISAM tables if you haven't done so in a while. This will ensure that the data is in a consistent state and that the indexes are properly linked.
Also I have read that it is better to recreate the table than just optimizing?
⇢ It is sometimes faster to recreate than optimise. If you plan on tackling just the indexes, then DROP and CREATE are your friend. If you want to also check the integrity of the tables, then OPTIMIZE TABLE is what you're looking for. If your site doesn't get too much traffic, you could even look at dropping the index, optimising the table, then recreating the index.
Ideally this should be done for every table in the database, not just the two in question. Databases, like many other things, need the occasional bit of maintenance to remain reliable.


Can't you just use the COALESCE function like this?

DECLARE @columnList VARCHAR(MAX), @sql VARCHAR(MAX)
SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName
FROM TableB

SET @sql = 'SELECT ' + @columnList + ' FROM TableA'

EXEC(@sql)


Long/complex INSERT and UPDATE query parser or visual editor/query builder

One method is with the Microsoft.SqlServer.TransactSql.ScriptDom. This can be used from a .NET application or PowerShell script. The assembly is available as a NuGet package or, if you already have SQL Server tools installed, you can reference an existing assembly in the Program Files (x86) folder.

Below is a PowerShell example that uses the assembly installed by SSMS.

Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

$query = "INSERT INTO sometable(col1, col2, col3) VALUES (1,2,3)"

# use the appropriate TSqlParser version for the scripts being parsed
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

$scriptReader = New-Object System.IO.StringReader($query)

$fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -eq 0) {
    for($i = 0; $i -lt $fragment.Batches[0].Statements[0].InsertSpecification.Columns.Count; ++$i) {
        $columnName = $fragment.Batches[0].Statements[0].InsertSpecification.Columns[$i].MultiPartIdentifier[0].Value
        $columnValue = $fragment.Batches[0].Statements[0].InsertSpecification.InsertSource.RowValues[0].ColumnValues[$i].Value
        Write-Host "$columnName`: $columnValue"
    }
}
else {
    Write-Host "$($parseErrors.Count) parsing errors. First error: $($parseErrors[0].Message)" -ForegroundColor Red
}


You need to pass @IsKit as well, between AllowAttr and Body.


You are getting the error because you are grouping by Giver in NumberGiven (the same error occurs for NumberReceived). A simple example:

x   y
1   1
1   2
2   3

select count(1) from T group by x

will result in:

2  -- the number of rows for group x=1
1  -- the number of rows for group x=2

Your subqueries probably contain multiple groups, and you, therefore, get more than one count, which leads to your error.

Since I don't know what your table looks like, I can only guess what the query should look like:

Select A, C, D  -- distinct not necessary because of group by
      , sum(received amount) as received_amount
      , count(case when receiver = A then 1 end) as #received
      , count(case when Giver = A then 1 end) as #given
NumberReceived
FROM Table
WHERE A = '12345'
Group By A, C, D


Another way, that doesn't use dynamic SQL would be to first CROSS JOIN the period with a (derived) table that holds DISTINCT name values from tt and then LEFT JOIN tt:

SELECT
    period.end,
    n.name,
    IFNULL(SUM(tt.punkte), 0) AS punkte
FROM
        period
    CROSS JOIN
        ( SELECT DISTINCT name FROM tt ) AS n
    LEFT JOIN
        tt
    ON
       tt.name = n.name
       AND (tt.zeit < period.end AND tt.zeit >= period.start)
GROUP BY
    period.end,
    n.name ;


SQL Server 2012 DBCC memorystatus has wrong information. How can I correct it?

x86-64 Virtual Address Space Details:

Although virtual addresses are 64 bits wide in 64-bit mode, current implementations (and all chips known to be in the planning stages) do not allow the entire virtual address space of 264 bytes (16 EB) to be used. This would be approximately four billion times the size of virtual address space on 32-bit machines. Most operating systems and applications will not need such a large address space for the foreseeable future, so implementing such wide virtual addresses would simply increase the complexity and cost of address translation with no real benefit. AMD therefore decided that, in the first implementations of the architecture, only the least significant 48 bits of a virtual address would actually be used in address translation (page table lookup).1
In addition, the AMD specification requires that bits 48 through 63 of any virtual address must be copies of bit 47 (in a manner akin to sign extension), or the processor will raise an exception.1 Addresses complying with this rule are referred to as "canonical form."1 Canonical form addresses run from 0 through 00007FFF'FFFFFFFF, and from FFFF8000'00000000 through FFFFFFFF'FFFFFFFF, for a total of 256 TB of usable virtual address space. This is still approximately 64,000 times the virtual address space on 32-bit machines.
[...]
The first versions of Windows for x64 did not even use the full 256 TB; they were restricted to just 8 TB of user space and 8 TB of kernel space. Windows did not support the entire 48-bit address space until Windows 8.1

There you have it. As the OS supports more than the default 8TB VA, it has become necessary to report in the DBCC MEMORY_STATUS how much is the OS actually supporting. Your OS is still the classical 8TB flavor.


PostgreSQL 9.6.17 COALESCE function error

I don't think you can "fix" this without changing your query or going back to Postgres 9.3, where apparently type casting rules were more relaxed.
date_part() returns a floating point value, and in the absence of an implicit type cast Postgres tries to convert the other operand of coalesce() to a compatible value, and an empty string ('') cannot be so converted.


The stored procedure DatabaseBackup is not supported on Amazon RDS.

As you note, there is logic that checks for the RDS platform.

If these checks return that your server is an RDS instance the following code block is encountered.

  IF @AmazonRDS = 1
  BEGIN
    SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.'
    RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT
    SET @Error = @@ERROR
    RAISERROR(@EmptyLine,10,1) WITH NOWAIT
  END

Pretty self explanatory, eh? Sorry about that

You're probably best served starting by browsing AWS docs for SQL Server backups on RDS


Basically, you need a window function instead of the aggregation. Appending an OVER clause to an aggregate function makes it a window-aggregate function:

SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000, 1
FROM   time_raw
GROUP  BY "time";

And since you want the minimum of the whole table, it's OVER () (nothing between the parentheses).

And you don't need a subquery. Fewer steps, right?

But what do you mean by "time_pyramid will have 3 columns".

Do you mean to create the table? Then use CREATE TABLE AS and apply aliases aliases to all columns in query:

CREATE TABLE time_pyramid AS
SELECT 0 AS layer_number
     , EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000 AS time_converted
     , 1 AS count_number
...

Or that's just a typo and you mean to insert into an existing table. Then append target columns to make your INSERT safer (or valid to begin with):

INSERT INTO time_pyramid (layer_number, time_converted, count_number)
SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") <b>OVER ()</b>))::bigint * 1000000, 1
...


Will canceling InnoDB compression that's already in progress corrupt data?

If innodb_file_per_table is enabled, then a temp table is being populated with the new compression. The original table is currently intact, just not writable.

Running KILL QUERY 12345 on the Connection running the ALTER TABLE should do the following:

release the write lock
drop the temp table
leave the original table as is


Postgres - Importing multiple rows with Foreign key constraint

The solution to this problem is the one that one uses quite often when importing data: to use a staging table.

Let's say you have a CSV with two fields (student_id and grade).  This maps to the grade table nicely, and the only problem is the nonexistent students.  So, first create a temporary table and then import the data there:

CREATE TEMPORARY TABLE tmp_grade AS
    SELECT *
      FROM grade WHERE FALSE;

Then you have multiple options, but the gist of it is only picking the rows from the temp table that have a matching student:

INSERT INTO grade
SELECT *
  FROM tmp_grade
 WHERE EXISTS (SELECT 1 FROM student WHERE id = student_id);

And done.  The performance of this depends on the size of the data (not surprisingly).  The definitions of the unique student.id suggests this won't be the case.  However, if it happens, you can still play around with other variations to the same theme.

Notes:

choosing char(2) as the student ID is interesting.
pgAdmin is a client to PostgreSQL, it does not have tables by itself.


Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference:
http://help.scibit.com/mascon/masconMySQL_Field_Types.html


"Your password has expired" after restart mysql when updated mysql 5.7.8-rc(debian)

MySQL password expired

Typically, in a script shell, you get the message: "Your password has expired. To log in you must change it using a client that supports expired passwords."

From MySQL 5.7.4 to 5.7.10, the default value for the default_password_lifetime variable is 360 (a year). For those versions, if you make no changes to this variable or individual user accounts, all user passwords will expire after 360 days.

To prevent automatic password expiry log in as root:

mysql -u root -p

then, for clients that automatically connect to the server (e.g. from scripts.) change the password expiration settings for those clients:

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

or just disable automatic password expiration for all users:

SET GLOBAL default_password_lifetime = 0;

Links and resources:

MySQL: Password Expiration and Sandbox Mode
MySQL: Password Expiration Policy
Password expiration policy in MySQL Server 5.7


ORA-40341: Access violation on model storage object in Oracle?

Database tables created using ore.push will have the prefix ORE$. Normally, these are automatically cleaned up when the R session ends or the ORE db connection terminated (unless you save the ore.frame objects in an ORE datastore).

In-database models from Oracle Data Mining (ODM)  as created from ore.odmSVM will also normally be deleted automatically and the end of the R session or ORE db connection termination. You should not attempt to remove DM$ tables as they are part of the in-database model. If you do delete any of these, their corresponding models will be corrupted/invalid.

You can invoke

SELECT name FROM dm_user_models;

to see the list of models in the database for your schema.

You can drop ODM models using the following in SQL*Plus or SQL Dev:

BEGIN
  DBMS_DATA_MINING.DROP_MODEL('MY_MODEL');
END;

Lots of thanks goes to Mark Hornick, Senior Manager, Oracle Advanced Analytics for his constant support in ORE implementation in my project.


CXPACKET Waits performance tune for SQL Server 2008

There is a lot of misunderstanding about CXPACKET.  CXPACKET isn't the cause of your problems, it is a side effect.  What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something.  Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else.  When you see CXPACKET waits you need to look at the other threads of the SPID and see what other waits are besides CXPACKET.  That other wait is the problem.

To your specific issue, the reason that the run times are so crazy is probably because the SQL Server is generating a different plan on some days because the statistics are out of date (making the job run long).  Then you either manually update statistics (or via a job) or auto stats kicks in and then the plan gets better and the job runs quickly again.

Once you've solved the stats problem you can start looking at other reasons why the job is running slow.  The fact that you only have a single disk is not helping any for sure.


PowerShell: Test-SqlAvailabilityReplica running script never completes

The commands around Availability Groups for SQLPS require you to pass in the provider path for the replica, or server itself (if you are building the AG).

As the example in the help documentation shows:

Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\<ServerName>\<InstanceName>\AvailabilityGroups\<AG Name>\AvailabilityReplicas\*<SpecialCase>*"

If you are using a named instance change "DEFAULT" to the name of the instance.

So after some testing I found out the replica name is a special case if you are dealing with named instances. If you are dealing with a named instance you cannot just use:
    "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer\MyInstance"

You will get path not found errors...here is a kick on how to fix it.

I happened to issue this command against a named instance with an AG:

dir "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\" | select *

The PSChildName and other references to the replica names show that the backslash is being presented as the ASCII hex equivalent value of "5C". So to work with named instances you have to use this path format:

"SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer%5CMyInstance"


Concurrency when simulating autoincrement with triggers on MySQL

You can avoid the whole problem by using this pattern.  Old tables had

id MEDIUMINT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id)

New table has

tenant_id SMALINT UNSIGNED,
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(tenant_id, id),
INDEX(id)

Notes:

INDEX(id) is sufficient to handle AUTO_INCREMENT.
The ids for a given tenant will have a lot of gaps and will grow faster than before.  So you may need a larger datatype.
Nothing prevents you from inserting a duplicate id, but that would not happen in ordinary programming.


How to Export Table sql to Excel file in sql server 2014?

This may be a file permissions/uac issue.

To prove/disprove that, create a new folder and copy (not move) the excel file into it, then give the windows security group "Everyone" Full control on the folder and its files. Update your export query with the new file location and try running it again.

If that works (or if you at least get a different error), then bear in mind that wherever you want to have the export file in the end, the file will need read/write permission assigned for the user account your SQL Server service runs under. (Or the "Everyone" group, but that wouldn't be best security practice.)


Customer table with last order id - violate normalization?

No it does not violate normalization rules because NF forms are defined in terms of dependency rules that involve ONLY A SINGLE TABLE.

Obviously, it creates a form of redundancy in your database, because as has been said, the "last order" can be derived by looking at the ORDERS table, but that is a form of redundancy that the traditional normal forms do not seek to address.  Traditional normal forms up to BCNF consider functional dependencies only, and since it is presumably indeed the case that your customerid will unambiguously determine the corresponding last_order (*), having the last_order in the customers table is even the only place where the NF forms will say it belongs.

(*) if it can be the case that a customer has no orders at all, then there will be nothing to put in this column.  You will probably use NULL in that case, and there are those who say that this is in itself a violation of 1NF.  The more traditional version of normalization theory glosses over NULL cases completely.

EDIT

I noticed I have forgotten to answer the bonus question "what would be a better methodology for this type of work".  IMO, the proper way, at least theoretically, to address these kinds of situation is by using materialized views.  Define the aggregate query that computes the last_order for each customer as a view, and have the DBMS keep a physical record of its value ("materialize the view").  This way, the redundancy in the design has been brought under control (it is now entirely managed by the DBMS itself).  Unfortunately, few DBMS's will be able to support this for views that involve aggregation.  The rulesets of what is possible and what isn't, in this area, for a given DBMS, are rather diverse and extremely ad-hoc.

The trigger you say you already have is the next best thing if materialized view isn't an option.  Or remove the redundancy altogether and just recompute the last_order from the ORDERS table whenever it is needed, if that is feasible given the applicable performance requirements/expectations.


Possible to do OPENQUERY without named pipes?

Most likely, the error is just because you didn't change the order for the netlibs to be tried on the calling SQL Server. It will then try Shared Memory, TCP and finally NP. If all fails, then you will get the error for the last one tried, i.e., Named Pipes.

I.e., this is a general "cannot connect to the SQL server" (from the source SQL server to the destination server) error and should be troubleshooted as such. This and this are good resources.

In other words, OPENQUERY does not require Named Pipes.


Selecting values from one table which don't exist in the other

I think it's a matter of using EXCEPT ?
Instead of NOT EXISTS()

Simple example:

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'OrderLines')
    DROP TABLE [dbo].[OrderLines];

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'InvoiceLines')
    DROP TABLE [dbo].[InvoiceLines];

-- create tables
CREATE TABLE [dbo].[OrderLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_InvoiceLines] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)
) ON [PRIMARY]
GO

-- insert sample data
INSERT INTO dbo.OrderLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(1,3),(2,1),(2,2)
INSERT INTO dbo.InvoiceLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(2,1)

-- query result missing jobnumber/subjobnumber records in second table
SELECT JobNumber, SubJobNumber
FROM dbo.OrderLines
EXCEPT
SELECT JobNumber, SubJobNumber
FROM dbo.InvoiceLines

Results:

JobNumber   SubJobNumber
----------- ------------
1           3
2           2

(2 rows affected)

I would work with the key values that make the result set (Jobnumber, Subjobnumber), and then get the other data for those rows.


Insert data into table where 4 fields are the same and the fifth changes

No loops are needed in this (please!).

You should be doing an INSERT...SELECT.

Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted.

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
  username,
  name,
  startdate,
  enddate,
  saledate,
  41
FROM subscription;

If there is a specific startdate and enddate you want to use for all users, then you'll need to set them as variables.

DECLARE @startdate DATETIME;
DECLARE @enddate DATETIME;

SET @startdate = '2015-05-21 10:00:00.000';
SET @enddate = '2015-06-21 00:00:00.000';

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
   username,
   name,
   @startdate,
   @enddate,
   NULL,
   41
FROM subscription;


Alias of COUNT is not being "recognized" by SQL Server

The SELECT clause is logically processed after the HAVING clause. Therefore the aliases used in SELECT don't exist (yet) when the HAVING clause is processed.

On MSDN you can look at SELECT (Transact-SQL):

  Logical Processing Order of the SELECT statement

  The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  FROM
  ON
  JOIN
  WHERE
  GROUP BY
  WITH CUBE or WITH ROLLUP
  HAVING
  SELECT
  DISTINCT
  ORDER BY
  TOP

This query works because it repeats what will later be defined (the COUNT) in the SELECT clause:

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING COUNT(ProductID) > 5;


Unable to install MySQL 5.7.12 in RedHat in a separate directory

bin/mysqld --initialize --user=mysql --datadir=/app/mysql/data --basedir=/app/mysql/5.7

I specified --user parameter and the error no longer appearing.

2016-05-19T10:27:17.440353Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-19T10:27:17.661087Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-05-19T10:27:17.713953Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-05-19T10:27:17.777797Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: xxxxxxx-xxxx-xxxx-xxx-xxxxxxx.
2016-05-19T10:27:17.780306Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-05-19T10:27:17.781230Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxx


Job to remove logins on secondary replica that don't exist in primary replica

You can create a linked server to the primary and write an Agent job that compares the logins on both servers.


How to prevent big performance impact during index creation

There are many possible explanations for this. It may be that you have to accept it as the cost of making an improvement but it may be highlighting issues elsewhere in the system. Possible causes:

Fragmentation of the table.
Fragmentation of the physical database files.
Poor choice of clustered index key.
Large table with low percentage of its data in the buffer pool, requiring lots of physical IO to read.
Lack of spare memory/io/cpu capacity to deal with this one off task on top of the normal workload.
Index creation requiring tempdb for sorting but tempdb on same drive as data and or log files.

Your question is a little sparse on the details necessary to provide a conclusive answer. To start with, can you add the table DDL (including clustered index definition) to your question along with row counts and database size. Also, a description of the server spec (memory, cpu, drive configuration) and whether or not tempdb is on a separate array.

Next, the following will give us an indication of how your IO subsystem is performing.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

The following will tell us how badly fragmented your existing indexes are. You may want to run this at a quiet time as it can impact on performance.

SELECT
    DB_NAME(database_id) AS [Database Name]
  , OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
  , i.name AS [Index Name]
  , ps.index_id
  , index_type_desc
  , avg_fragmentation_in_percent
  , fragment_count
  , page_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN
    sys.indexes AS i
ON  ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE
    database_id = DB_ID()
AND page_count > 500
ORDER BY
    avg_fragmentation_in_percent DESC ;


MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance


How do I prevent changes to my PostgreSQL database?

One work-around:

Change the port of PostgreSQL on prod-server-old. This way it is unlikely that clients connect to the DB during pg_dumpall --port=OTHER_PORT.


You can use RAISERROR instead of PRINT. With a severity of 0 the two are equivalent but there is some primitive string formatting functionality available as below.

RAISERROR('%d %d',0,1,@one, @two)


Execution plan of on-premises sql server and Azure sql server is not the same

If the plan is the same (which it looks to be), and the query speeds up when you bump up to more DTUs, it's likely you're simply being limited by the Azure tier you're on.
You can confirm this by looking in the execution plan at the wait stats - if you're being CPU-capped by your Azure tier, there will be RESOURCE_GOVERNOR_IDLE waits (and related SOS_SCHEDULER_YIELD waits as well).
I've blogged about this here: RESOURCE_GOVERNOR_IDLE in Azure
Looking at the Azure plan you provided:
<WaitStats>
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" />
  <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />
</WaitStats>
<QueryTimeStats CpuTime="2756" ElapsedTime="3351" />

The difference between elapsed and CPU time is 595 - which is almost exactly the amount of RESOURCE_GOVERNOR_IDLE waits that were incurred.  Without those waits, the query would have finished in 2.7 seconds in theory, which is still slower than the 1.1 seconds it took the SQL Server 2014 query.
The main solutions are to either increase the service tier, or change the query / indexes so it uses less CPU.
My guess would be that the other hosting provider machine has a faster clock speed on the CPU, as well as not being limited by the resource governor.
To be clear though, you're client may not be considering the manageability "wins" with Azure, even at a more expensive / higher tier.  You'll get many advantages (including high availability, automated backups, automated tuning, all the latest database engine and security patches, and not having to deal with SQL Server instance level or VM maintenance) that are included in the slightly higher cost.

For this specific query, you might try creating an indexed view of this data if it is needed frequently, to "pre-calculate" this distinct list of user groups that have placed orders.  Then the query would only need to read the 20 rows from that view, which would be less likely to be impacted by the service tier.
Note that you would have to use GROUP BY instead of DISTINCT in the view definition due to the limitations on indexed views.


Solr uses lucene to do the spatial and full text indexing, not mysql. It basically wraps a REST-style service on top of lucene's index library so you can access it as a service. The wikipedia article is pretty good for an overview: http://en.wikipedia.org/wiki/Apache_Solr

An alternative to Solr that also uses lucene is Elastic Search, which I think is superior in at least a few ways--see this answer from the creator of Elastic Search: https://stackoverflow.com/questions/2271600/elasticsearch-sphinx-lucene-solr-xapian-which-fits-for-which-usage/2288211#2288211


Can not re-set [log_reuse_wait] after log back up

You have a couple actual questions here, so let's address each one individually.

To answer the question of why the log_reuse_wait won't update, this field (along with other metadata within SQL Server) will only update after certain events occur.  With this field, it will update after a database checkpoint occurs.  To force this after taking a log backup, do the following:

BACKUP LOG [foo] TO DISK=N'X:\Backups\foo_20141008120000.trn';
CHECKPOINT;

The checkpoint will occur and the field in sys.databases will update.

For why your log file won't shrink, this is a MUCH larger question.  You should read this question/answer by Mike Walsh on why your log file is growing and how best to manage it.  I also provide some more detail in this answer.

Regarding VLFs, I would strongly recommend you not worry about that right now.  Yes, you can see a performance impact over having to many VLFs (and understanding what a VLF is and how it works is fundamental for understanding the log file), but managing that should only be addressed when you're regularly managing your log with backups and it's not growing out of control.

Finally, as for the size of your log, this is a very open ended question.  It very much depends on how much activity your database sees along with the scope of that activity.  Chances are that you will shrink your log file, only to see it grow again to accommodate your regular database activity.  Before considering shrinking your log file, you need to understand what's making it grow and whether or not you're running regular scheduled log backups against it.


INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working

I think the problem with your attempt is that you put quotes around the plugin name, along with the fact that you used a .so suffix to the library name.

Step-by-step:

Check that your MySQL install is dynamic_loading capable:

mysql> show variables like '%dynamic%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>

Check that your plugin directory is set correctly:

mysql> show variables like '%plugin%';
+---------------+-----------------------------------------------------+
| Variable_name | Value                                               |
+---------------+-----------------------------------------------------+
| plugin_dir    | C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\ |
+---------------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Then verify the .dll exists in that location:

The plugin install should then work OK:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.dll';
Query OK, 0 rows affected (0.01 sec)

mysql>


This is an incorrect encoding issue. The characters are coming in encoded as DOS Code Page 850 yet the target Code Page you are using (based on the Latin1_General Collations) is Windows Code Page 1252. For example, in DOS Code Page 850, the Ç character has a value of 0x80 (or 128 in Decimal). However, that same value of 0x80 in Windows Code Page 1252 gives you €. Likewise, Ã in DOS Code Page 850 has a value of 0xC7 (or 199 in Decimal). However, that same value of 0xC7 in Windows Code Page 1252 gives you Ç.

The incorrect characters are incorrect due to being imported into SQL Server with the wrong encoding being specified for the source. This is not happening within SQL Server as that would be a code page conversion issue, in which case the same "character" would have its value translated for the same character in the target Code Page (if the character exists in the target Code Page, else you get ?). For example:

SELECT ASCII('Ç' COLLATE Latin1_General_CI_AS) AS [CP1252 Value],
       'Ç' COLLATE SQL_Latin1_General_CP850_CI_AS AS [CharacterInCP850],
       ASCII('Ç' COLLATE SQL_Latin1_General_CP850_CI_AS) AS [CP850 Value];

Returns:

CP1252 Value     CharacterInCP850     CP850 Value
199              Ç                    128

Meaning, this is happening most likely during a file import — BCP.exe, SQLCMD.exe, BULK INSERT, OPENROWSET(BULK...), custom app code that reads a file, etc — where either the wrong source Code Page is being specified, or no Code Page at all is being specified for the source. If an import is being done that specifies Code Page 1252 for this file, it will have the effect that you are seeing here since those bytes are encoded for Code Page 850, not Code Page 1252.

It should be noted that this could also happen with data coming in from app code if the driver (ODBC, etc) is being told to use the wrong code page.

Now, regarding the method of fixing this:

Ideally the method of importing the data would be updated / fixed to properly account for the actual code page with which the data has been encoded.
If it is not possible to fix the import process, then the function that the other company provided is not the best way to go. In fact, it is probably the slowest, most convoluted approach which is also prone to errors (if they didn't map all of the characters). There is no reason to do two loops along with SUBSTRING when loading the characters, in pairs, into a table variable would have allowed for a single loop using the REPLACE function. And using the ASCII function and a case-sensitive, accent-sensitive Collation is unnecessary and prone to error (if two characters match what is being searched for) when using a _BIN2 Collation would have been better.
Use the following function which does the conversion. First it gets the bytes of the current string, then it injects those bytes into a VARCHAR column that uses Code Page 850, then it selects that value from the table variable into a local variable (necessary anyway to return the value) which has the effect of converting the string into the Code Page used by the default Collation of the Database (which here would have to be Code Page 1252 else you would not be getting the "correct" string out of the function):

USE [tempdb];
GO
CREATE FUNCTION dbo.FixCodePage850toCodePage1252
(
    @CodePage850String VARCHAR(8000)
)
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Convert850to1252 TABLE
  (
    [String] VARCHAR(8000) COLLATE SQL_Latin1_General_CP850_CI_AS
  );
  DECLARE @ReturnValue VARCHAR(8000);

  INSERT INTO @Convert850to1252 ([String])
  VALUES (CONVERT(VARBINARY(8000), @CodePage850String, 0));

  SELECT @ReturnValue = [String] -- automatic conversion to Code Page of database
  FROM @Convert850to1252;

  RETURN @ReturnValue;
END;
GO

Testing both functions returns the same results:

SELECT dbo.fixcollation('Ç‰§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

SELECT dbo.FixCodePage850toCodePage1252('Ç‰§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

I came up with a test to check the mappings of all characters just in case the company providing the translation function missed any mappings. I filtered out the graphics characters and dotless "i" that are only found in Code Page 850.

USE [tempdb];
GO
;WITH nums AS
(
    SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
    FROM   master.sys.columns
), vals AS
(
  SELECT nums.[num] AS [Value],
         CHAR(nums.[num]) AS [Character],
         dbo.fixcollation(CHAR(nums.[num])) AS [OldWay],
         dbo.FixCodePage850toCodePage1252(CHAR(nums.[num])) AS [NewWay]
  FROM   nums
)
SELECT vals.*,
       ASCII(vals.[NewWay]) AS [NewValue]
FROM   vals
WHERE  vals.[Character] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[OldWay] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[Value] NOT IN (176, 177, 178, 179, 180, 185, 186, 187, 188,
                            191, 192, 193, 194, 195, 196, 197, 200, 201,
                            202, 203, 204, 205, 206, 217, 218, 219, 220,
                            223, 254, 213); -- characters only in CP850

That returns a list of 52 characters that could have come through the import process mistranslated like the others, but skipped by the UDF that you were provided by that other company that only handles 46 of the apparently 98 possible characters.


Determine total no of logical/physical reads

I can think of 3 options to get the total reads:

Extended Event session
SQL Profiler
IO Statistics Parser

IO Statistics Parser is pretty simple, just copy/paste your STATISTICS IO output and voila!


creating a historical database, need help making a decision

Your first design is the way to go.  Keep everything about a process on a single record.  Your challenge isn't one of database design, it's one of restartability/disaster recovery.

Since your processes are long-running, you have a higher risk of the data getting out of sync with the processes that are being tracked in the data.  For example, you lose power causing a process to crash.

The solution is that you need to change your processes so that when they start they confirm what the data says.  Does the database say that process X is still unfinished?  Is process X still running?  If not, your data is out of sync and you need to mark the record for process X as failed.

You might want to consider adding a couple of columns to your first table design, for example:

last_update - the date/time when the record was last updated.  Update this periodically during your long-running process.
status - keep a code to indicate "Active", "Complete", "Failed"


Decomposition of a relation to 2NF and 3NF

The goal of 3NF (and BCNF) is to avoid situations when a set of attributes is functionally determined by something that is not a key (or, more formally, a superkey), which may lead to anomalies and redundancy.

Keeping that in mind, we can describe certain functional dependencies as either "bad" (if they prevent the relation from being in 3NF) or "good" (if they do not).

So your solution is wrong, because "your" R1 has the E->D dependency and E is not R1's key. Determining a key for a relation is another topic which, I suppose, is beyond the scope of the task you need to accomplish (we are told that ABC is a key). But if you are interested, you can look the method up using "attribute closure" keywords.

Anyway, the E->D dependency is "bad". But that is not the only problem. Another one is that your decomposition is not a "lossless-join" one. In other words, if we have some real world data, split the relation according to your proposition and try to join R0 and R1 back, the data will be corrupted. Data corruption is not what normalization is designed for.

We have to solve 2 problems:

Decide which dependencies are "bad" (the definition of normal forms is the answer to this)
Decide how to split relations to prevent data corruption (we have Heath's theorem to help us)

Getting back to your problem, finally.

In R, neither B, nor E is the key. So both B->E and E->D are "bad", and we need to take measures. On the contrary ABC->EF is "good", because ABC is a key.

Currently R is in 1NF, because B is a part of a key and functionally determines E, which is not a part of a key. This kind of situation is unacceptable in 2NF.

E->D dependency is not that "bad", because E is not a part of a key.

Another thing to mention at this point is that a set of dependencies {B->E, E->D} implies the dependency B->ED (according to so called Armstrong axiom of transitivity).

So we decide to split using B->ED dependency as "the worst" one. That's the way we do this, according to Heath:

R = ABCDEF
R0 = BED (B->E, E->D, B is a key)
R1 = ABCF (ABC->F, ABC is a key)

Now we have R1, which is in 3NF and R0, which is in 2NF (it has a transitive dependency B->E->D, which is unacceptable in 3NF).

We need to split R0 and to do so we have to choose a "victim" dependency among B->E and E->D. As we see, B->E is now a "good" one (B is a key) and E->D is still "bad".

Our split:

R0 = BED
R2 = BE (B->E, B is a key)
R3 = ED (E->D, E is a key)

R1, R2 and R3 are the final decomposition.


Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions:

-- No result
SELECT
    session_id,
    transaction_id
FROM sys.dm_tran_session_transactions;

Using an explicit transaction does return results:

BEGIN TRANSACTION;

    -- Row returned
    SELECT
        session_id,
        transaction_id
    FROM sys.dm_tran_session_transactions;

ROLLBACK TRANSACTION;

The DMVs are views on internal structures, and the documentation is not often as comprehensive as in other areas. Some of that may be because it would be inconvenient to go through a complete deprecation cycle each time a change in DMV behaviour occurs, but it's likely just an oversight in this case. You could report the documentation deficiency on Connect.

Adam Machanic found all sorts of odd little behaviours with the DMVs while writing his sp_WhoIsActive tool. If it suits your purposes, you could use that rather than trying to write your own monitoring queries.