Maximizing throughput on bulk data processing

I should think your workload is not using the index on IsFetched at all. Each successive UPDATE will be starting at the beginning of the table and reading forward until it has processed 100 rows (your TOP value). Subsequent iterations have to read over the previously-fetched rows to get to new ones. Conceptually, the first iteration reads 100 new rows and updates them. The second iteration reads the first 100 rows and skips them then reads & processes 100 new rows. The third reads those 200 rows, skips them, read 100 new rows and process them, and so on.

Why does the query not use the index? The query execution plan is generated when the query is first submitted. At that point all rows in the table have IsFetched = 0. So using the index would not help distinguish which rows to process and which to skip. Indeed using that index would be an overhead since execution would have to read other structures for the other columns' values. The optimizer has no way of accounting for the fact that this UPDATE will be run many times in succession.

The solution is to use a different architecture based on SourceID, which is unique (and clustered). On each iteration store the highest value of SourceID that was processed, then start the next iteration from that value.

First some set-up and test data

drop table if exists Source;
drop table if exists Target;
go

create table Source(SourceID int primary key);
create table Target(SourceID int primary key);
go

insert Source(SourceID) values (1), (2), (3), (4), (5), (6), (7), (8), (9);
go

I'll show the code to iterate then describe it after.

declare @batch int = 4;      -- for 9 test rows this gives me 2 full + 1 partial batches
declare @highest int = -1;   -- set to any value lower than what is in your data
declare @var table (SourceID int primary key clustered);
declare @c int = @batch;     -- essentially a flag showing if the previous
                             -- iteration found rows

while @c >= @batch    -- stop once a not-full batch is processed as that
                      -- marks the end of the data
begin
    print 'Highest: ' + cast(@highest as varchar(99));  -- debug

    insert top (@batch)
    into target(SourceID)
    output
        inserted.[SourceID]
        into @var
    select
        SourceID
    from Source
    where SourceID > @highest
    order by SourceID;    -- important to order so each iteration is guaranteed to get
                          -- a contiguous block

    set @c = @@ROWCOUNT;  -- flag whether to iterate

    -- select * from Target;    -- interesting for debug, do NOT use in production!

    set @highest = (select max(SourceID) from @var);
end    -- end iteration

The output messages are

Highest: -1              -- this is the start with the dummy initial value

(4 rows affected)        -- we find rows 1, 2, 3 & 4 and process them
Highest: 4               -- start the second iteration from "> 4"

(4 rows affected)        -- rows 5, 6, 7 & 8 done
Highest: 8               -- third iteration from "> 8"

(1 row affected)         -- only row 9 left.

There's an assumption that there is an index on the key column (SourceID). Each iteration performs and index seek to where the last left off and then scans leaf pages from there, reading @batch rows. Therefore it obviously works best if the index is covering or is the clustered index, which is covering by definition. Without an index this will revert to table scans and you're no better than you were before.

I put an index on @var. Inserts will be in slustered sequence so there will be no page splits. The MAX() query will be a single-row lookup, which is nice. If you can a non-durable in-memory table works nicely in this role, too.

I make @batch a variable for ease of debug. It could be hard-coded.

Variable @c is really a flag. Defining as an integer and setting it as I do avoids unnecessary IF statements.

It is important to have an ORDER BY on the SELECT. Without it we are not guaranteed that the rows returned are adjacent. For example, given the data above and @highest = -1, rows 3, 5, 7 & 9 satisfy the WHERE clause, which is not what we desire.

There is no requirement for SourceID values to be contiguous. If there are holes in the sequence, if data has been deleted, this is OK.

I stop once an iteration processes fewer than the desired number of rows.

Note that IsFetched is not required for this. It can be dropped from the schema if this is its only purpose.

There's a nice write-up here including benchmarking over a large-ish table. Swart's solution reads ahead to find the end of a batch then processes the batch in a second statement. Mine determines the end of a batch retroactively after a batch is processed. His scans the source table twice and mine once plus an index lookup. His solution will always perform a "no-op" iteration at the end of the table, mine when the table cardinality is an exact multiple of the batch size. I think mine is slightly more efficient but you're unlikely to notice in a real workload.

If the table source is continuously populated with new rows and not truncated between runs you can save the value of @highest to a table at the end of a run and populate it from that table during the next run. If future runs can insert SourceID values lower than the current largest, or if there are concurrent writes, the algorithm can be adjusted to accommodate.

My experience is that batch sizes of a few thousand rows give the best balance between throughput, blocking and resource consumption. Test on your environment, of course, to see what works for you.


How to access anonymous user account in MySQL?

Have them login as test with no password.

To check the existence of such logins, you connect as root into the commandline mysql tool (or some other tool) and run

SHOW GRANTS FOR 'test'@'localhost';
SHOW GRANTS FOR 'test'@'%';

The first will tell you if users can get in from the same machine.  The second will say whether you can get in from arbitrary other machines (assuming no network or firewall limitations).


Compare only day / month / year portion of a timestamp field

Use date_trunc() function..

SELECT
  *
FROM tblFoo
WHERE ...
ORDER BY date_trunc('day', planned_ts) ASC NULLS LAST, priority ASC

Please see the documentation for the functions if necessary.


Only do log backup if necessary in SQL Server

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO.  Your logic would look something like this:

CREATE TABLE #vlfs(
   RecoveryUnitID int
  ,  FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);

DECLARE @MBthreshhold int

SELECT @MBthreshhold = 5 --Enter your threshhold in MB here

INSERT INTO #vlfs
EXEC ('DBCC LOGINFO')

IF (select sum(filesize/1024/1024)
    from #vlfs
    where [Status] = 2) > @MBthreshhold
BEGIN
    BACKUP LOG [foo] to <<backup location>>
END

Please note there are a lot of caveats here.  Your transaction log will never be completely empty and there is always something to backup to it.  You can use this to determine at what size point you want to actually run your backup, but this can be a HUGE risk to your Recovery Point Objective (RPO).  Your log backups will happen at variable times, meaning you can never guarantee the maximum amount of data loss you will incur in a disaster.  This sort of strategy should be pursued with extreme caution.


Excessive memory grants from indexed foreign key cascade delete

I believe you're encountering the same bug in the new Cardinality Estimator that I did. I wrote about the problem here, with a more-detailed look here.

In short, the new Cardinality Estimator has a bug when estimating rows to be deleted on a cascading delete. If the driving value on the parent table is far enough outside its histogram, the CE will assume that every single row of the child table gets deleted.

Lucky you though, my server took the high estimates and decided to start scanning instead :(

Also luckily for you, you're dealing with a proc instead of ORM madness, so there are viable solutions. One would be to add DBCC TRACEOFF( 4199) to your procedure (potentially causing permissions issues). That doesn't future-proof you though, since in 2016+ 4199 status doesn't matter for the CE bug. Another option might be to add OPTION(OPTIMIZE FOR (@1 = <some middle value>)).

Since you're not getting a scanning plan, you could also just strongarm the memory grant with MAX_GRANT_PERCENT.


Set sql_mode “blank” after upgrading to MySQL 5.6

PROLOGUE

Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well.

ROOT CAUSE

I just found out why what you did does not work and the workaround is very simple.

According to MySQL 5.5 Documentation, sql_mode default is a blank sting.

According to MySQL 5.6 Documentation, sql_mode is default is

blank string in MySQL 5.6.5 and back
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in 5.6.6 and into GA

OK, I hope you are sitting down.

This is the lazy way Oracle implemented sql_mode in MySQL 5.6: There is an additional my.cnf file.

If you run

cat /usr/my.cnf

you will see the following

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

See line 28 ?

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

SOLUTION

STEP 01 : Comment out line 28 of /usr/my.cnf

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

STEP 02 : Set sql_mode by hand

mysql> SET GLOBAL sql_mode = '';

THAT'S IT !!!

EPILOGUE

STEP 01 prevents restarts of mysqld from changing sql_mode.
STEP 02 sets sql_mode now, so restarting mysqld is not needed immediately

GIVE IT A TRY !!!


MySQL Full Text search increase relevance for exact matches?

Messy workaround answer:

    SELECT *
      FROM table
     WHERE MATCH ("doobdy institute") AGAINST (name)
  ORDER BY MATCH ("doobdy institute") AGAINST (name),
           ABS( LENGTH("doobdy institute") - LENGTH(name) )


I'm pretty certain the table definitions are close to this:

CREATE TABLE dbo.households
(
    tempId  integer NOT NULL,
    n       integer NOT NULL,
    HHID    integer IDENTITY NOT NULL,

    CONSTRAINT [UQ dbo.households HHID]
        UNIQUE NONCLUSTERED (HHID),

    CONSTRAINT [PK dbo.households tempId, n]
    PRIMARY KEY CLUSTERED (tempId, n)
);

CREATE TABLE dbo.persons
(
    tempId  integer NOT NULL,
    sporder integer NOT NULL,
    n       integer NOT NULL,
    PERID   integer IDENTITY NOT NULL,
    HHID    integer NOT NULL,

    CONSTRAINT [UQ dbo.persons HHID]
        UNIQUE NONCLUSTERED (PERID),

    CONSTRAINT [PK dbo.persons tempId, n, sporder]
        PRIMARY KEY CLUSTERED (tempId, n, sporder)
);

I don't have statistics for these tables or your data, but the following will at least set the table cardinality correct (the page counts are a guess):

UPDATE STATISTICS dbo.persons
WITH
    ROWCOUNT = 5239842,
    PAGECOUNT = 100000;

UPDATE STATISTICS dbo.households
WITH
    ROWCOUNT = 1928783,
    PAGECOUNT = 25000;

Query Plan Analysis

The query you have now is:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n;

This generates the rather inefficient plan:

The main problems in this plan are the hash join and sort. Both require a memory grant (the hash join needs to build a hash table, and the sort needs room to store the rows while sorting progresses). Plan Explorer shows this query was granted 765 MB:

This is quite a lot of server memory to dedicate to one query! More to the point, this memory grant is fixed before execution starts based on row count and size estimates.

If the memory turns out to be insufficient at execution time, at least some data for the hash and/or sort will be written to physical tempdb disk. This is known as a 'spill' and it can be a very slow operation. You can trace these spills (in SQL Server 2008) using the Profiler events Hash Warnings and Sort Warnings.

The estimate for the hash table's build input is very good:

The estimate for the sort input is less accurate:

You would have to use Profiler to check, but I suspect the sort will spill to tempdb in this case. It is also possible that the hash table spills too, but that is less clear-cut.

Note that the memory reserved for this query is split between the hash table and sort, because they run concurrently. The Memory Fractions plan property shows the relative amount of the memory grant expected to be used by each operation.

Why Sort and Hash?

The sort is introduced by the query optimizer to ensure that rows arrive at the Clustered Index Update operator in clustered key order. This promotes sequential access to the table, which is often much more efficient than random access.

The hash join is a less obvious choice, because it's inputs are similar sizes (to a first approximation, anyway). Hash join is best where one input (the one that builds the hash table) is relatively small.

In this case, the optimizer's costing model determines that hash join is the cheaper of the three options (hash, merge, nested loops).

Improving Performance

The cost model does not always get it right. It tends to over-estimate the cost of parallel merge join, especially as the number of threads increases. We can force a merge join with a query hint:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (MERGE JOIN);

This produces a plan that does not require as much memory (because merge join does not need a hash table):

The problematic sort is still there, because merge join only preserves the order of its join keys (tempId, n) but the clustered keys are (tempId, n, sporder). You may find the merge join plan performs no better than the hash join plan.

Nested Loops Join

We can also try a nested loops join:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (LOOP JOIN);

The plan for this query is:

This query plan is considered the worst by the optimizer's costing model, but it does have some very desirable features. First, nested loops join does not require a memory grant. Second, it can preserve the key order from the Persons table so that an explicit sort is not needed. You may find this plan performs relatively well, perhaps even good enough.

Parallel Nested Loops

The big drawback with the nested loops plan is that it runs on a single thread. It is likely this query benefits from parallelism, but the optimizer decides there is no advantage in doing that here. This is not necessarily correct either. Unfortunately, there is no built-in query hint to get a parallel plan, but there is an undocumented way:

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n
OPTION (LOOP JOIN, QUERYTRACEON 8649);

Enabling trace flag 8649 with the QUERYTRACEON hint produces this plan:

Now we have a plan that avoids the sort, requires no extra memory for the join, and uses parallelism effectively. You should find this query performs much better than the alternatives.

More information on parallelism in my article Forcing a Parallel Query Execution Plan:


Why does importing a 12 GB .sql file take more than 36 hours?

Try this:

$ ps -ef|grep [m]ysql

Identify the process id then

$ strace -cp <pid>

Leave it 10 seconds or a minute then ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seen read and write dominate.


SQL Server Linked Server to Azure SQL using Azure AD

Not possible today.  AD auth works, AAD auth is not implemented in linked servers.  I have not tried ADFS, however.


How to free postgres space after a DELETE without VACUUM

You are in a tough spot.  You will probably need to schedule some down time and get some more disk space to act as scratch space.  VACUUM might not free up any space anyway because it can only free up contiguous chunks of completely empty pages at the physical end of the table.  With downtime, the fastest solution may be to copy the data out of the table with \copy, perhaps compressing as it goes so that it all fits on your existing storage.  Then drop the table and re-import it into the individual partitions.  But note that partitioning the data won't make it smaller, unless there is currently lots of bloat.  So it doesn't solve the fundamental problem.  If you want to keep a 1TB of data around, you need to have enough storage to do that.

If you have the luxury of patience, you can try to use pg_repack to squeeze the space out of the table while it is still live.

  or to speed up the VACCUM process?

The default settings for manual VACUUM are for it not to be IO throttled.  That is because the default setting for vacuum_cost_delay is zero.  Make sure you haven't changed that.  Make maintenance_work_mem be 1GB if you have enough RAM to support that.  Drop any indexes on the table you can get away with dropping.  Use system tools (e.g. top, vmstat, perf, strace) to figure out where the bottlenecks in your VACUUM are.


I would like to suggest something radical. I got this idea from StarTrek : Deep Space 9 (Call to Arms)

  A minefield was set up around a wormhole to prevent the Dominion
  Forces and the Jemhadar from passing through. Each explosive mine was
  armed with a replicator that allowed a mine to recreate another mine
  after it explodes. Hence, the minefield stays up indefinitely.

With that DS9 analogy I bring your an interesting idea.

You set up 2 initial read slaves with MySQL Slave with innodb disabled

[mysqld]
skip-innodb

This is optional. My preference is an all-MyISAM slave to do reads because it is faster for reads than InnoDB for small datasets. Should you choose to go with InnoDB, make sure you relax the ACID compliance with this

[mysqld]
innodb_flush_log_at_trx_commit = 0

In the event of a crash, just destroy the Slave and spin up a new one

We'll call the Slaves S0 and S1

Here is something else: have this in /etc/my.cnf in S0

[mysqld]
innodb_max_dirty_pages_pct = 0;
innodb_fast_shutdown = 0

These will help S0 shutdown fast with completely flushed data.

The following is what you must script in the replicator process

When you need to generate a new slave (we will call it S2), here is what you must do

STEP 01) On S0, run service mysql stop

STEP 02) Install the same version of MySQL that S0 has into S2

STEP 03) On S0, scp /etc/my.cnf S2:/etc/.

STEP 04) On S2, you need to change the server-id of /etc/my.cnf in S2 to a Unique Value (suggestion : use the 2nd,3rd, and 4th octet [without the dots] of the private IP of S2)

STEP 05) On S2, either remove or comment out the innodb_max_dirty_pages_pct = 0 from /etc/my.cnf

STEP 06) On S0, run rsync -av /var/lib/mysql S2:/var/lib/mysql (Note: If you have to spin up 5 Slaves, run the 5 rsyncs at this point)

STEP 07) On S2, service mysql start (MySQL Replication start immediately where S0 had left off)

STEP 08) On S0, run service mysql start

Once you create the replicator script, you can use it spin up MySQL on new Slaves.

Meanwhile, S1 is available for SELECT queries and continues replicating.

S0 is used to recreate a new Slave.

If you are doing this in conjunction with spinning up Amazon EC2 or some other Cloud DB Servers, check with your System Administrators on any Linux commands/API that allows you to spin up a DB Server. Then, you apply the replicator to the newly generated DB Server. Even better, you can incorporate the Db Server creation API in your Replicator Script.

CAVEAT

If you have to spin up dozens or hundreds of Slaves, all you need to do is have 10 Replicator Slave Servers (S0 - S9) and have 10 copies of the replicator script operator on different Replicator Slaves.


Doing string manipulation in SQL is non-optimal for the engine; it's not meant for that kind of row by row processing.

Your option of using temporary tables to hold intermediary results is going to be your best bet on solving the performance problem.  At least that way it's only split once.  But there are solutions that may work better for you.

Can you modify the import process so the data is split up before it's saved to the database?
What about importing to staging tables and using either an Agent Job or external process to split the data up into permanent tables?

I'm not sure how much reporting you are doing on older data sets; if you only need to report on the data once or twice then temp tables are probably your best bet.  But if you want or need to do longer term analysis then continually splitting the same data is unnecessarily expensive.  Storage is usually cheaper than CPU.


Why does msdb.dbo.backupset have entries for each database every 15 minutes?

Backups for SQL Server can be scheduled in different way and we do not know how it is set up in your case.That is why we cannot tell you where to look. Most common scenarios are:

SQL Server Agent Job
Maintenance plan
3rd party tool

If you run the script mentioned by @Kin in the comment section, it will show that your databases are most likely taking transaction log backup every 15 minutes.

If that is the case you also might want to purge your backup history periodically. You have to be careful on the first run as it might take very long depending on the amount of rows to be deleted and transaction log for MSDB can grow significantly. See the comments by Erik and Randi.


Enumerate drives available to SQL Server

I'm guessing you're actually running on a clustered instance.

EXEC master..xp_fixeddrives is returning all the drives that the underlying OS is aware of, so a lot aren't available to your cluster instance

use:

SELECT * FROM sys.dm_io_cluster_shared_drives

instead, which will just list the ones available to your instance


The comma , is not a valid logical operator. Try using AND or OR, depending on what you need, rows that match either one or both criteria (use OR) or only the rows that match both criteria (use AND).

You should also avoid writing dates with ambiguous formats, like the '10/15/2012 00:00:00.000'. Use either 2012-10-15T00:00:00.000 or '20121015 00:00:00.000' format. You can read Aaron Bertrand's blog for more details about dates and range queries: Bad habits to kick : mis-handling date / range queries. In this particular query and because the date has no time part, you can also use the shorter '20121015':

select *
from [dbo].[AccessFeeDetail]
where LastLoginDate >= '20121015'
      AND
      jobname not like 'Uptest_%' ;


How can I ensure the user can insert and edit data while updating table

Get rid of all the complex design.

Plan A:  Get rid of A and A_cache and the trigger and the rebuild; run queries against B and C.  Perhaps you need better indexes?  Or "Summary tables"?

Plan B:  (More directly in line with your design.)  Get rid of A_cache. Do this, which will avoid A ever being unreachable:

Create A_new from B and C
RENAME TABLE A TO A_old, A_new TO A;  -- atomic and instantaneous
DROP TABLE A_old;


Using JSONB Column or Another Table to Save Relationsips

Document types like json or jsonb (or xml or hstore) are convenient to store, well, documents. Ideal for data with varying keys and rarely updated and not-too-complex filter criteria in queries. Manipulating big documents a lot inside the database would be an anti-pattern.

Structured data that is often written in small increments, and probably searched a lot (like in your case most likely) are much more efficient with a normalized design - regarding storage, performance, concurrent write access and data integrity. Hence the advice in the manual:

  JSON data is subject to the same concurrency-control considerations as
  any other data type when stored in a table. Although storing large
  documents is practicable, keep in mind that any update acquires a
  row-level lock on the whole row. Consider limiting JSON documents to a
  manageable size in order to decrease lock contention among updating
  transactions. Ideally, JSON documents should each represent an atomic
  datum that business rules dictate cannot reasonably be further
  subdivided into smaller datums that could be modified independently.

Implement your n:m relationship with one ore more junction tables. You can enforce data integrity with constraints (PK, FK, UNIQUE, CHECK, ..), none of which is easily possible with document types. I would lean towards a single table unless you have substantially differing requirements for "like", "bookmark" etc.

Example:

If you are unsure about the typical layout, here are the basics:

How to implement a many-to-many relationship in PostgreSQL?

Assuming that each of your relation types can only be used once per user/doc combination. For just a hand full of values (3 in your case) I would use a 1-byte "char" column as PK of a lookup table to optimize storage and performance in tables and indexes.

CREATE TABLE reltype (
   reltype "char" PRIMARY KEY
 , relation_type text UNIQUE NOT NULL
);

INSERT INTO reltype(reltype, relation_type) VALUES
   ('l', 'like')
 , ('b', 'bookmark')
 , ('s', 'save');

CREATE TABLE user_doc (
   user_doc_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY
 , user_id     int REFERENCES users     ON UPDATE CASCADE ON DELETE CASCADE
 , document_id int REFERENCES documents ON UPDATE CASCADE ON DELETE CASCADE
 , reltype     "char" REFERENCES reltype NOT NULL DEFAULT 'l'
 , CONSTRAINT user_document_pkey UNIQUE(user_id, document_id, reltype)
);

You can always export aggregated data as JSON documents. Even have a VIEW or MATERIALIZED VIEW to read like a table. But don't manage relationships inside a single big JSON document.


Select Items that has one item but not the other

You can use NOT EXISTS to eliminate rows linked to undesirable types (like @a_horse already suggested).

But since multiple rows can match any row in table1 also use EXISTS to qualify rows linked to desirable types:

SELECT t1.*
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'one'
   AND    t3.table1_id = t1.id
   )
AND    NOT EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'two'
   AND    t3.table1_id = t1.id
   );

With joins like a_horse demonstrated, you would multiply rows and be forced to add an aggregation or DISTINCT step to get distinct rows again. Don't.

Also, you can use the same subquery twice, making the query simpler to understand and maintain.

I would see this as special case of relational-division, btw. Related:

Select rows which are not present in other table
How to filter SQL results in a has-many-through relation


MariaDB Column Store Suitability for Data Warehouse

This is what I say about MySQL/MariaDB without columnstore:
Not good:  "sum up to 1 TB" versus "1,25 TB of SSD storage".  As a Rule of Thumb, you should have half your disk free for maintenance and growth.  As a minimum, there should be enough room for an extra copy of the largest table -- data+indexes.  This allows any ALTER to run without running out of disk space.
Let's discuss the largest table.  Please show us SHOW CREATE TABLE and the main queries -- for inserting, for updating and deleting (if needed), and for selecting.  With that we can discuss shrinking the disk footprint, indexing, and partitioning (or not).
Here's my discussion of DW: http://mysql.rjweb.org/doc.php/datawarehouse See also its link to a Summary table discussion.
Please elaborate on "simply no common keys".
With "overnight batch loading", I highly recommend:

load into a temp table;
normalize (tips on that in another link);
augment the summary table(s) from the temp table;
copy the data into the main Fact table;
drop the temp table.

If you are "purging" "old" data, then PARTITION the table so as to replace the big, slow, DELETE with a fast DROP PARTITION.  More details: http://mysql.rjweb.org/doc.php/partitionmaint
Your other numeric specs are not scary but could be problematic.  Need to get into the actual analytic queries to see.
Note: if your team grows and the system fails to provide adequate concurrent queries, you can build a Replication system, wherein the Primary copies to read-only Replicas.  Each Replica can independently handle several big analysis queries.
The amount of RAM will be determined by the table sizes, query complexity, etc.  More ram is better -- but there is a point of "diminishing returns" that cannot be predicted.  Start with your 64GB, try to optimize the "worst queries"; then decide whether to add Replicas.
The layout of the SSDs won't matter much.  (Aside from needing more room.)  With multiple small drives, you could use RAID striping and/or parity.  I would want a raid controller with battery-backed-write-cache (costly, good, but probably overkill since you write only nightly, and possibly don't care if it takes 2 hours instead of 1?)  That would allow for `RAID-5 deployment of 4x500g or 7x250G drives,  Either would give you effectively 1.5TB plus parity.
(Years ago, I did a similar project (0.5TB data; HHD; RAID-5; hourly load; 7 summary tables loaded in about 7 minutes.  Primary-Replica was implemented, but was overkill in my case.  The Summary tables turned hour-long queries into minute-long queries; YMMV.)
columnstore
Columnstore shrinks typical data by 10x.  (I suspect that a well designed schema on InnoDB can't be shrunk by that much.)  Some of the performance characteristics come from this compression; some from parallel operations.
What does your 1TB come from?  Does it mean that you would need only 0.1TB of disk for Columnstore?  Or that InnoDB would need 10TB?  (My discussions above may need adjusting accordingly.)
Columnstore is excellent at querying on an arbitrary column because every column is indexed.  But when you filter on more than one column, it will efficiently filter on one, then (perhaps) have to brute force on the others.
Without more insight into your queries, I can't judge between

Columnstore
MySQL/MariaDB with regular indexing
MySQL/MariaDB with Partitioning / FULLTEXT / Spatial

It is likely that CS will outperform or underperform an equivalent database without CS -- depending on the query.


How can I hunt down what is trying to use SA?

To track login failure reason SQL Server Profiler would be a good option. One can also use extended events trace to capture login failed events and reason behind the failure.

You need to first launch SQL Server Profiler.Create a new template or you can use existing one if you have already. Please include below when creating the trace

1.Audit Login Failed (under Security Audit)
2.User Error Messages (under Errors & Warnings)
3. Errorlog (under Errors & Warnings)

Then clear the Show All Events tick box and select following columns

TextData
SPID
ClientProcessID
Hostname
LoginName
NTUserName
NTDomainName
ApplicationName
DatabaseName
Error

More details can be taken from This Link and This Link

To read more about SQL Server profiler refer to this Microsoft Online Doc


Oracle Secure Backup + Amazon S3 / RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Did not setup bucket name that was originally used duting backup creation:
echo "OSB_WS_BUCKET=mycompany-oracle-database-bucket" >> /home/oracle/S3BCKP/config/s3backup.cfg


HAProxy - Load-Balancing in 2 Node setup - Performance Gain possible?

There are several different topics you comment on your question, and with many "IF"s, depending on your specific workload and architecture. Let's start with the things you are right:

It is true that Galera by itself should not give you better write workload- it is a shared-nothing, non-sharded cluster, which means you will have to write everywhere. The only way to improve significantly your write performance is if you can share the write load between several nodes, while in your case you are duplicating them. In fact, as it is shared nothing architecture, the more servers you add, the slower the whole usually will be, as the slowest or further away server will be the bottleneck of your replication. If you need write performance, galera is not for you (it will usually have less throughput than a single server). You normally setup galera to get high availability(survivability of data and service if a node goes down), not better write performance.

There are some buts:
* I had some clients that claimed better write performance, probably because horrible SQL queries + Galera requiring row-based replication, and in some special cases, you could get some gains with that (if you do 30-second writes but you only write a few records, you will get some extra scalability). That is normally very rare: you should fix first your queries, but I am just pointing to a (very) specific exception.

The meat of your question is that if the fact that you are adding a proxy in the middle will not be worth the improvement you get from load balancing queries. To answer that, you need to say 6 things:

What is your current average round-trip time between the client and the servers
What is your average round-trip time between the client and the proxy
What is your average round-trip time between the proxy and the servers
What is the overhead of the proxy processing time
What is the local latency of the actual mysql query time
How loaded is your average client?

This will tell you if you are interested on using a proxy or not for performance reasons.

You can find out the first 3 by using ping, the last 3 by profiling the actions. Normally, query time is much larger than round time within a datacenter, but that depends on which queries you are doing and how far located (physically) are those VMs. To cancel some of those times, people install the proxy on the same machine than the clients, so any overhead is mostly canceled. Also, HAproxy being mostly an IP proxy, the overhead is very low.

Now, if your servers are not very loaded, you may not get any advantage in latency- querying the server will double your throughput- if that has an impact on latency or not will depend on your current load.

There is usually a more important reason to use a proxy, which is high availability- using HAProxy will allow you to switch to a secondary galera node in case the active one goes down, automatically. It will also simplify manual switchovers. Of course, the proxy itself can be a single point of failure.

I hope that helps you decide- but the most important advice is try it yourself and measure!

Edit: BTW, with 2 nodes only, I hope you use galera as a replication solution, not as a cluster (requires special configuration) or with garbd, because if you don't, and a node goes down, the second will go down too to avoid a slit brain (no node has 50%+ quorum).


How can I remove identical (but negative) rows that are also in some days of each other from a SELECT on a table

SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM TABLE t2
                   WHERE t2.`value` = -t1.`value`
                     AND t2.`date` BETWEEN t1.`date` - INTERVAL 7 DAY
                                       AND t1.`date` + INTERVAL 7 DAY )


advantages and disadvantages of using direct sql querying in application

Raw SQL

A good inline SQL query, if made correctly using parameters  is as
good as a stored procedure.
Raw SQL is  more flexible to deal with.

Stored procedures

Stored procedures have the benefits of security , but just raw
performance isn't one of their major plus points.
Stored procedures are more maintainable.
Stored procedures are precompiled
The security and data layer separation benefits and increased ease of
performance tuning provided by strictly using stored procs generally
make them a better solution than ad-hoc queries regardless of any
relative performance enhancement that may or may not come with the
pre-compilation of the procs.
scalability and modularity

Conclusion

I'd personally like to utilize only Stored Procs for database access.
The only thing I see SQL doing that stored procs can't do (easily) is dynamically choosing the sort order. Other than that, it's about knowing how to code the stored procs the way you need.


Delete with subquery with no unique columns

Try using an EXISTS operator in your WHERE clause. I am not 100% of the exact syntax for sqlite but in SQL Server I would do something similar to the following:

DELETE from likes
WHERE  EXISTS (SELECT *
               FROM   table
               WHERE  likes.id1 = table.m
                      AND likes.id2 = table.n
                      AND your other conditions )


Design db schema in correct way for parent child kind of requirement

This is an impressive ERD. Are you sure you want to have every table inherit from CreatedAtAbstractBase?

The tables CarTypePanelPrice and CarModelPanelPrice are almost the same, differing only by car_model identifier. I would have the car_model field be optional (NULLable) and merge both tables. You'll know if one record is one type or the other if that field is NULL or NOT NULL. The orders/bookings seem alright.


Report Builder CTP 2.3 Installation DLL Error

Simple solution... posting for anyone else testing SQL 2016.

Do NOT supply a default SSRS Server URL during the installation process and it completes without error (Windows 2012 Datacenter and Windows 7 confirmed).

Please help MS fix this issue. Happy reporting!


Retrieving SQL Server InfoMessages in PowerShell 5

You will not be able to capture the print statements in the manner you are trying. The handler is meant to output the statement to the console, not to actually capture it.

This would cause the message to write the console, but you will never (at least my testing) be able to append this into your $message variable. Which I don't recall this working like you have it in PowerShell 2.0, but I don't have that version available anymore to try.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };

Now why this has changed I have not found yet. If I ever do I will try to remember to come update this post.


Sql logshipping Last_restored,last_copied file null in sysjobhistory

Found the answer finally, The logshipping linked server between monitor and Secondary server was not working, Opened up rules on firewall to let them communicate. Started working


Is there any reason to back up these system-created certificates?

Your first certificate needs to be backed up if you are moving the MSDB between instances.  https://support.microsoft.com/en-us/kb/2000274

In short, its not typically required to explicitly backup those certificates.  Particularly if you are backing up your system databases correctly.


Ring topology support of MongoDB ReplicaSet and Sharding?

MongoDB (as at 3.0) only supports a single primary per replica set. Replica sets can have up to 50 members, with up to 7 voting members. The 2-node replica sets you have described should have a third member (either a data-bearing secondary node or a voting-only arbiter) to allow for failover. Replica sets require a strict majority of votes (n/2+1) in order to elect and maintain a primary, so a 2 node replica set requires both members to be healthy (see: Replica Set Elections).

Replication Topology

In terms of replication flow, the deployment of a replica set could be described as either:

A self-configuring tree topology, with the current primary as the root node. By default MongoDB allows replication chaining so secondary nodes can choose to sync from other secondaries based on ping time (for example, to mitigate bandwidth usage when multiple secondaries are in a remote data centre).
A self-configuring star (or hub-and-spoke) topology, if you disable replication chaining.

You can influence selection of the current primary by configuring member priorities for a replica set, but the general goal is to provision a set of peer nodes (with similar hardware resources such as CPU/RAM/disk) to allow any data-bearing secondary to be promoted to primary.

Sharded Cluster Query Topology

With a sharded cluster your application connects via the mongos routing service, which will direct queries & commands to the appropriate shard(s) and aggregate results as required. The networking topology here might be described as a partial mesh, as each mongos connects to the 3 config servers and the current primary for each shard (and potentially secondaries, based on read preferences).

Note: Your approach with the use of distinct ports for different roles (replica set membership, config server, mongos) makes sense as a way to self-document the sort of service you are connecting to.


What is the best way to cross check the record in multiple collections

You could do an aggregation pipeline using the lookup operation, where you only need to specify the user ID.

Would be something like this:

var my_user_id = "1234";
db.getCollection('campaigns').aggregate([
{
    $lookup:
    {
        from: "user_activity",
        let: { campaign_id_var: "$campaign_id" },
        pipeline: [
        { $match:
            { $expr:
                { $and:
                    [
                    { $eq: [ "$user_id",  my_user_id ] },
                    { $eq: [ "$campaign_ad_id", "$$campaign_id_var" ] }
                    ]
                }
            }
        },
        { $project: { user_id: 1, campaign_ad_id: 0 } }
        ],
        as: "someactivity"
    }
},
{
    $match: { "someactivity": { $eq: [] } }
}
]);


WHERE  BaseCurrency = 'EUR'
  AND  QuoteCurrency = 'USD'
ORDER BY  RateTime DESC

Needs

INDEX(BaseCurrency, QuoteCurrency, RateTime)

and

WHERE  CurrencyFrom = 'EUR'
  AND  CurrencyTo = 'USD'
  AND  ExchangeTime <  '2018-01-29 12:00:00'
ORDER BY  ExchangeTime DESC

needs

INDEX(CurrencyFrom, CurrencyTo, ExchangeTime)

Ditto for the third.

More discussion of how I came up with those indexes is in my Cookbook .

The reason for the sudden slowdown is buried in the size of the table, the value of innodb_buffer_pool_size, the number of rows that your too-short index touched, etc.

My indexes will be fast, regardless of any of those issues (for those queries).


Setting root password in fresh mysql 5.7 installation

If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.

This guide is misleading.

The only way to set password is to switch to native authentication like:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';


Show all variations of 2 characters in postgres

with characters (c) as (
  select unnest(string_to_array('xy', null))
)
select *
from characters c1
  cross join characters c2

Edit:

Apparently this does not work in 8.4, but the following should:

with characters (c) as (
  select unnest(regexp_split_to_array('xy', ''))
)
select *
from characters c1
  cross join characters c2

Thanks to Bruno for testing this.


SQL server bulk insert fail code 5 [SQLSTATE 42000] (Error 4861)

in-order to access a file in another machine using SQL Server you may give permission to the Account which you have used to start your sql-server instance.

if you have active directory on your environment make sure to start sql-server instance with an active directory user and give required permission to to that user on "Server-A".
Change the Service Startup Account for SQL Server

but if you do not have any active directory on your Environment the only way to do this is to have a user with the exact name and password of your sql-server service user on Server-A


What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to

Choose (and document) what it means for each nullable field
Make sure it means one thing only

With those rules, consider the following use case:

You have a table 'shop' (eg individual premises)
You have a table 'retailer' (eg chains)
The 'shop' table has a lookup field referring to the key of 'retailer'
You have defined null to represent an independent shop (ie one that is  not part of a chain)
A 'retailer' closes branches to the point that you consider its shops to be independent

In this case, an on delete set null makes sense. There are other ways of modelling these business rules but this is the simplest and if it accurately fits the facts that you care about in the real world, I suggest it is perfectly ok


No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation.

To do it manually:

ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2;

If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.


When do I need more than one Oracle Instance on the same server?

You most certainly don't want to have dev, test, and production all sit in the same instance. I wouldn't even put them on the same server.

Having separate instances for environments that serve different puproses allows you to isolate them from the security and maintenance perspectives. For example, if your development database needs to be restored, you don't want to put your production environment offline for that reason. You'll probably also have stricter security in production.

Even performance-wise separate instances are useful while sharing the same physical (or virtual) machine. For example, by limiting memory_target on the development instance you will ensure that runaway sessions in development won't eat up all resources on the server.


Here's how I would do it in Powershell:

$instance = 'localhost'
$dbs = dir SQLSERVER:\SQL\$instance\DEFAULT\Databases
$sql = 'CREATE USER [foo] FROM LOGIN [foo]; ALTER ROLE db_datareader ADD MEMBER [foo];'
$dbs | ForEach-Object {Invoke-Sqlcmd -ServerInstance $instance -Database $_.Name -Query $sql}

This assumes the login foo has already been created. It also assumes running on the localhost instance.


How to make SQL Server Management Studio use latest version of SQL Server?

Microsoft SQL has several components.  You can install SQL Studio (the GUI you're using), SQL Server itself (the actual database, to which SQL Studio can connect), Analysis Services, Reporting Services, and others.

You currently have a SQL 2005 server running, and you're connecting to it with SQL Studio 2012.  That's OK!  But if you'd rather have a SQL 2012 database on your local machine, you will need to run the SQL 2012 installation process again, and add that component.  You may want to remove the 2005 installation first (backup your database first, of course).

If you've used WAMP or LAMP, what you have is equivalent to installing different versions of MySQL (the database) and PHPMyAdmin (the GUI).  Or Oracle and Toad.


How can I get the hierarchical values from the below query?

I was able to replicate your results with your example data.

Your issue is that in the "base" case of the recursive CTE (the first select statement) you are getting the records where fParentCategoryID is null and casting the fParentCategoryID to a character column ready to append the "children". However the 'string' is still a NULL value at this stage (try it with SELECT CAST(NULL AS VARCHAR(200)) or such like).

The second select statement then tries to append other string values to the NULL but by default, the database option "concat null yields null" says if you try to concatenate anything to a NULL value then you still just get NULL - rather than treat it as an empty string.

You could SET CONCAT_NULL_YIELDS_NULL OFF for this query although this is deprecated and will ultimately be removed in a future version of SQL Server (so you probably shouldn't be adding it to production code!)

A better way is in the first select statement, instead of casting the fParentCategoryID to a string, just cast the CategoryID - we already know the parent should be empty as they are the NULLs.

SELECT c.CategoryID,
   c.fParentCategoryID,
   [level] = 1,
   ParChild=cast(CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX))
FROM   retail.Category c
WHERE c.fParentCategoryID is NULL

If your data could have a mixture of null and non null values in that select statement, you could also use ISNULL around it to treat any nulls as empty strings.

It probably needs some cleanup on the ParChild value it produces as you get values like " ,461,464" so you might want to strip the beginning comma off.


Answer originally left by Dan Guzman in comments:

The method I usually use is an explicit transaction plus a UPDLOCK, HOLDLOCK locking hint on the SELECT query. That will help avoid deadlocks and avoid the race condition, without resorting to serializable isolation for the entire transaction.

Also SERIALIZABLE won't really help as the initial select will get shared (S) range locks.  So both sessions will get the S lock, and then, instead of a PK violation, you'll get a deadlock.

With the UPDLOCK in addition to HOLDLOCK, only one session can select the same key regardless of whether it exists (holding update key lock) or not (holding update key range lock). This will serialize access to the specified key in this code path.

This worked for the OP, who implemented it as follows:

@Id = SELECT *
  FROM ANIMAL WITH(UPDLOCK, HOLDLOCK)
  WHERE Owner = x AND Name = y
IF(@id <> NULL)
  UPDATE ANIMAL
ELSE
  INSERT ANIMAL

--DO OTHER STUFF


Statistics for AlwaysOn Availability DBs

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it?

You are correct.

  Can you please help me understand why there is a need for SQL Server to maintain statistics for DBs that are part of Always On availability group in tempdb?

This really comes into play when there are secondary replicas which are readable. A readable secondary replica may have a workload on it that is different than the primary and could benefit from different statistics on the columns and indexes. This is where the temporary statistics in TempDB come into play (because the database is [for all outside purposes] readonly).

These statistics are really just to support differentiating workloads on a readable secondary without having to explicitly create them on the primary and let them flow through.

If readable secondary replicas aren't used, these will be of no use to you and won't even be created.


It appears that you created your pfile with a double extension .ora.txt (Windows Notepad sometimes does this).

You can try to:

rename the pfile removing the .txt extension, or
specify the full path to your parameter file in the CREATE statement, e.g. CREATE SPFILE FROM PFILE = 'c:\yourpath\initlittle2.ora.txt';


What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself?

SQL Server does not expose performance counters for any user defined object.

In fact PerfMon architecture is not a suitable platform for highly dynamical changes. If SQL Server would provide such feature it has to create counters every time a SP created. Think about SP renames, restores etc.

A couple of suggestions for monitoring problem of yours would be :

Creating a wrapper stored procedure for Adam Machanic's sp_WhoIsActive to filter out sessions you are not concerned about and apply practices at How to grant permissions for non-dbas section of sp_AskBrent from Brent Ozar to that stored procedure.
You can log activities to a table using an agent job like every 15 second execute sp_WhoIsActive filter out unwanted records from resultset log them to a table then let GUI generate reports from that table. With a quick search while i'm writing this answer i have come across a blog post from Brent Ozar. I didn't read the whole post but the idea is same.


SQL Server Deadlock Graph - Table, Page or Row Lock?

In the deadlock graph XML you will see something like:

<deadlock-list>
  <deadlock victim="...">
    <process-list>
      <process id="..." ... waitresource="X:..."
...

The X is the interesting bit, possible values you are interested in are:

RID for row id (row level locking)
PAG for page level lock
OBJECT (which may be further qualified TAB indicating a table lock)

There are a few others kinds listed in the documentation also.


How to make Database to support Multiple Languages

What do you mean by "data is showing correct in the application (front end) but incorrect in database"? The database doesn't show anything, it stores the data and hands it back to you (your app) when commanded. If you used the right types, you're good to go as far as the data's integrity is concerned. The database's language doesn't affect storage but it will affect display and some tasks (e.g. sort).

Applications need to be able to handle the types, languages and fonts. For instance, if you use a version of TOAD that does that not support Cyrillic characters, it won't render the data correctly even though it is stored correctly in your database. That's why you see the ???????? when you retrieve non-ANSI character data.

Older version of TOAD did not support all Unicode characters and fonts by default (they did EASCII/ANSI); you needed a Unicode version of TOAD that had the right font support and/or the right NLS_LANG setting. Check your version of TOAD against their specs to see if you have both the language support and font support.

It used to be a really weird dance getting the settings right between the OS, Oracle, TOAD, SQL*PLUS and whatever else you have running. That's largely gone with recent versions of the product stack. I think TOAD's been Unicode enabled since Toad 10 but please verify. If you are not or cannot get on the latest version, check out TOAD's documentation or the Oracle community in Toadworld for workarounds.


Need to filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)

From my understanding of your question, you wish to group the data by date and time based on a specified degree of granularity. There is a good article by Steve Stedman which demonstrates how to round a date in T-SQL.

You can use the technique that he pointed out to group by the date to a specific granularity. Based upon your comments you say that you do not wish to apply any aggregation technique to the non-date/time data - this will give you a lot of duplicates based on your example data. To get the result you are looking for I simply apply a DISTINCT across the data set; however if the values of the tags vary you will still get multiple rows per specific date and time.

Example for minute-level granularity:

SELECT DISTINCT Date_Time, Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
  FROM (SELECT DATEADD(minute, DATEDIFF(minute, 0, Date_Time), 0) AS Date_Time
             , Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
         FROM ProDataTable
        WHERE Date_Time BETWEEN '2017-11-17 19:05' AND '2017-11-21 19:23') AS a
GROUP BY Date_Time, Tag1_Value, Tag2_Value, Tag3_Value, Tag4_Value
ORDER BY Date_Time


How SQL Server agent calculates next run date for a job

In the dbo.sysschedules table you will see all of the data needed to calculate the schedules.

The columns named active_* delimit the periods in which the jobs can run.

The columns named freq_* define the frequency of the job runs.  You will notice a freq_type, which defines a type of frequency, which drives how the freq_interval and other columns are interpreted.

Please read the definitions at:
http://msdn.microsoft.com/en-us/library/ms178644.aspx

EDIT: You can calculate the next_run_datetime by using one of the following:

sp_help_jobschedule @job_name = 'Jobname'
sp_help_jobschedule @job_id= '<job id guid>'

Since there can be several schedules you can also use the schedule_id as such:

sp_help_jobschedule @schedule_id = 1, @job_name = 'Jobname'

This will return a data set in which the last columns give the data, such as:

next_run_date   next_run_time   schedule_uid                             job_count
20141110        190000          301DC5D5-9569-49FA-8BC5-9129109AFA6A     1

And you can script out the msdb procedure sp_help_jobschedule to see the code in the procedure, in case you want to include some of that in your own script.


Get Single greatest date for each record

Another way is to use window functions, either ROW_NUMBER() or MIN():

WITH
  ct AS
  ( SELECT
        g.[TimeStamp], d.DeviceId, p.IsHistorical,
        g.Latitude, g.Longitude, g.Speed, g.Bearing,
        g.Fix, g.SatelliteCount, p.MessageTypeId,
        rn = ROW_NUMBER() OVER (PARTITION BY d.DeviceID
                                ORDER BY g.[TimeStamp] DESC)
    FROM Obd.Device d
      JOIN obd.Packet p ON p.DeviceId = d.Id
      JOIN obd.GPSData g ON g.PacketId = p.Id
    WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL)
  )
SELECT
    [TimeStamp], DeviceId, IsHistorical,
    Latitude, Longitude, Speed, Bearing,
    Fix, SatelliteCount, MessageTypeId
FROM ct
WHERE rn = 1 ;

Irrelevant to the question but the condition on g.Latitude and g.Longitude makes the LEFT joins equivalent to INNER joins.


Azure Data Factory 'Failed Validation' with Folder does not exist

I had wrong file extension.  Error message was misleading. Instead of cannot find file I thought the error was about missing folder.

I had to change this

 "typeProperties": {
            "fileName": "{Day}.csv",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },

To (change file extension from csv to txt)

"typeProperties": {
            "fileName": "{Day}.txt",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },


How can this query be improved to be faster?

I considered an extra column that will hold the accent-ignored copies
  of content and title columns but this almost doubles the db size so I
  skipped that option.

Do this. Add the extra column. The cost of the additional memory is nothing compared to anything else you try.


An alternative to querying with multiple JOIN over the same table?

You could try to aggregate the features into a JSON value, then searching for a combination of multiple features is quite easy:

select h.*, hf.features
from houses
  join (
    select house_id, jsonb_object_agg(feature_name, feature_value) as features
    from house_features
    group by house_id
  ) hf on hf.house_pkid = h.pkid
where hf.features @> '{"rooftype": "flat", "has_basement", "true", "wallcolors": "white"}';

Performance can be improved by adding a WHERE clause to the sub-select which repeats the feature names, e.g:

where feature_name in ('rooftype', 'has_basement', 'wallcolors')

or even

where (feature_name, feature_value) in (('rooftype', 'flat') ('has_basement', 'true'), ('wallcolors', 'white'))

The outer condition is still necessary, because the inner where will include houses that don't have all the features.

This also has the advantage (in my eyes) that you only get one row with all the features, rather then one row for each feature.

Unless you remove, add and change features for a house very frequently, storing them as a single JSONB column on the house table  (features) and getting rid of the house_features table, might be an alternative. In that case you could create an index on the column to speed up the search.


Combine many table into one table in mysql table design

I have designed with @Walter Mitty opinion.

CREATE TABLE new_function_table(
  id int unsigned not null auto_increment,
  category tinyint not null comment '1: bill, 2: payment...'
  primary key(id)
)engine=innodb

CREATE TABLE bill(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_bill_new_function_table_id_idx (new_function_table_id),
  constraint fk_bill_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb

CREATE TABLE payment(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_payment_new_function_table_id_idx (new_function_table_id),
  constraint fk_payment_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb
...etc

If it is wrong, then please feedback! Thank you.


Try this:

SELECT session_id, client_net_address
    FROM sys.dm_exec_connections


Back up the old database files to a dump using mysqldump -u username -p database_name > dump.sql then import the new sql file using mysql -u username -p database_name < new_sql_file.sql.


Questions about Database Design for a Web Application

100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.

Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new  question for feedback.


Why does sys.fn_hadr_backup_is_preferred_replica take so long? Is there a better way?

The problem I'm experiencing is that the function takes a long time to run (about 3 seconds per database)

It's possible that this is among a growing list of dynamic management objects that don't support predicate push-down, so you will probably notice higher delays as the number of AGs and/or AG databases increases on any given replica.

And it's possible that's exactly what was fixed, as @Kin pointed out, in Connect #783011; sadly the Connect item doesn't indicate where exactly you can find the fix.

Based on some quick searches (and the date of the comment), I think you will want at least 2012 SP1 CU8, which (via KB #2918791) fixed a regression introduced in 2012 SP1 CU7 (via KB #2887115).

I don't see any evidence in later builds that suggest this function has been modified after 2012 SP1 CU8, and the modification is possibly related to the regression that was introduced.

But I am a huge proponent of using the most recent service pack branch:

SP2 is the only currently supported branch; RTM and SP1 have been retired, so if you're still on those, it's time to move;
the latest CU (currently that is SP2 CU #8);
if your applications use connection pooling, you may also be interested in this on-demand hotfix.

  can anyone recommend an alternative that is faster?

If you look at the source for sys.fn_hadr_backup_is_preferred_replica, you can see that there is some convoluted logic and waterfall variable population that you could probably short circuit if you are not using mirroring or log shipping, by writing your own, cleaned-up version. But then you get to calls to forbidden system functions like RetrieveDbReplicaState(), which you can't call yourself, and you realize that there isn't a way to rewrite this function and replicate its full functionality

So, your best bet is to apply whatever fix Microsoft has supplied in order to optimize the function. If you still experience slowness after moving to the latest build, you can file another Connect item, or you can raise a case with CSS directly (just be aware that if you have 10,000 databases in your AG, they may just tell you to stop doing that).


How do I query for system views in a SQL Server database?

It's not just if objects exist as a whole, you also have to make sure columns line up. Microsoft adds new stuff, and sometimes backports it. It's nice not to rely on version checking, or worry about it failing if someone hasn't rebooted after patching.

As a related example, when dm_exec_query_stats got information about tempdb spills and I wanted to show those columns, I used code like this to test for them.

DECLARE @tempdb_spills BIT = 0;

IF 4 = (   SELECT COUNT(*)
           FROM   sys.all_columns AS ac
           WHERE  ac.name IN ( 'total_spills', 'last_spills', 'min_spills', 'max_spills' )
           AND    OBJECT_NAME(ac.object_id) = 'dm_exec_query_stats' )

SET @tempdb_spills = 1;


How often should MySQL (stock, Percona, etc.) be upgraded?

Personally, I cringe when I hear about upgrades gone wrong due to human error. Most have success stories when upgrading MySQL in succession (5.0, 5.1, 5.5, 5.6) Some create their own disaster movies by moving datadir, uninstalling old MySQL, installing new MySQL, putting back the datadir and think everything's OK ... NOT !!!

MySQL Upgrade

MySQL created mysql_upgrade for an inventoried check of everything that could be different from the currently installed MySQL Binaries. When you used responsibly, this is the all-out solution. It is the only to go with very large database installations. There are certain rules you must follow. Although quite rare, even running mysql_upgrade right, strange things still can happen : See In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table and my answer to it.

Alternative

For smaller database installations, I have a much more simplistic approach: mysqldump (but with caveats). The most overlooked database in a MySQL Instance is always the mysql schema. People use mysqldump --all-databases and try to load the mysqldump from an older version into a newer version thinking that's all to it. Not so. Here is why:

With each major release of MySQL, table mysql.user has a different number of columns.

MySQL 5.6 has 43 columns
MySQL 5.5 has 42 columns
MySQL 5.1 has 39 columns
MySQL 5.0 has 37 columns
MySQL 4.x has 31 columns
See the actual columns in my post : Cannot GRANT privileges as root
See my most recent post in ServerFault about this : MySQL upgrade from 5.0 to 5.5

The script mysql_upgrade attempts to fix those columns among the other compatibility issues to adjust. My approach total removes concerns about compatibility issues.

My Approach

STEP 01: mysqldump all databases EXCEPT the mysql schema to a text file (/root/MySQLData.sql)

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > /root/MySQLData.sql

STEP 02: Dump the MySQL Grants as an SQL file

There are two techniques to do that

TECHNIQUE #1: Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any version MySQL/Percona 5.x

TECHNIQUE #2: Emulate mk-show-grants

cd
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

I have recommended these techniques before

Jul 26, 2011 : Restoring an old backup to latest MySQL release
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end

STEP 03: Shutdown mysql

service mysql stop

STEP 04 : Get old datadir and my.cnf out of the way

mv /var/lib/mysql /var/lib/mysql_old
mv /etc/my.cnf /etc/my.cnf_old

STEP 05 : Uninstall Old MySQL/Percona Server

STEP 06 : Install New MySQL/Percona Server

STEP 07 : Login to MySQL

STEP 08 : Run the two scripts

mysql> source /root/MySQLData.sql
mysql> source /root/MySQLUserGrants.sql

That's it !!!

Don't worry, I have done this before : MySQL upgrade 5.0.88 to latest

I trust my alternative approach because a mysqldump is a logical representation of the data that can be moved from installation to installation with the except of storage engine specifics. If such is the case, I would mysqldump the data without the schema in one file and mysqldump the schema only in another file. Then, I would edit the specifics in the schema-only file.

I'll leave the gory details of software installation in your capable hands...

Give it a Try !!!

EPILOGUE

There are many old sayings

If it ain't broke, don't fix it.
Fortune Favors the Bold (From an Episode of Star Trek DS9)

In light of these saying, use common sense. If you are satisfied with MySQL's performance heretodate, upgrading would just be needless downtime. Stick with what you have. Don't just upgrade because you can. I have personally seen an upgrade to MySQL 5.5 which was slower than the original MySQL 5.1 database. here are my past answers on this phenomenon:

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

If you want MySQL's new features, such as

InnoDB Multiple Core Engagement (MySQL 5.1's InnoDB Plugin, MySQL 5.5/5.6)
Semisynchronous Replication (MySQL 5.5)
Parallel Slave Threads (MySQL 5.6)

you will have to boldly go where no man has gone before.

Learn the technology you want in your database before engaging in upgrades.


Logical Drives - I/O aside, does SQL Server care?

If you are able to segregate drives so they use dedicated communication channels to the drive (D: has it's own channel, E: has a separate channel, etc.), then I believe you may be able to achieve some gains.  If the different drives all go through the same channel to the storage then I don't think it will have an impact.

Whether or not you can segregate the drives depends on your environment (VM/Physical, storage, host, etc.)


.Net Application Using Inefficient Query Plan - caused by ARITHABORT setting?

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key.

Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this:

  We have seen that there is a large number of different cache keys, so
  potentially there can be very many plans for a single stored procedure. But we have also learnt that many of the SET options that
  are cache keys are legacy options that you should never change.

  In practice, the most important SET option is ARITHABORT, because the
  default for this option is different in an application and in SQL
  Server Management Studio. This explains why you can spot a slow query
  in your application, and then run it at good speed in SSMS. The
  application uses a plan which was compiled for a different set of
  sniffed parameter values than the actual values, whereas when you run
  the query in SSMS, it is likely that there is no plan for ARITHABORT
  ON in the cache, so SQL Server will build a plan that fits with your
  current parameter values.

If you can change the stored procedure I would try to fix it there rather than with plan guides. The possible solutions are also explained in the linked article but basically you have these options:

OPTION (RECOMPILE) / WITH RECOMPILE
Add the "optimal" index
Drop the index you don't want the plan to use
Use an index hint to force use of any of the other indexes.
The query hint OPTIMIZE FOR.
Copy the parameters to a local variable.
Change the application behaviour.


You can't remove the wallet or change the password, but if the wallet is set to auto-open (or it is open) you should be able to export the data out of that database and create a new one that does not have an associated wallet.


Sql_text Data Missing When Querying Extended Event File Target Using TSQL

You're parsing the XML wrong. Since sql_text is an action, you need to do this:
value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)')


Getting recovered db files to work on rebuilt server

If the user that the MySQL server does not have permissions to read and write to the files, then the server will not recognize the table. Usually the server runs under the 'mysql' user.

I would first stop your mysql instance before trying to change the ownership.

Once that is done, then (from a non-root user) do sudo chown mysql bsf_session.* in the directory with the files.

You could also try to change the group (sudo chgrp mysql bsf_session.*), however having the files as the same owner as the server is the best option.

If you do not have sudo privileges or root access, then you will need to contact whoever does to have them change the ownership.


Can't change _log database physical location in SQL Server

You need to do several steps to modify the location of a database's files.

Alter the catalog entries for the database in question using the `ALTER DATABASE' command
Take the database itself offline.
Move the physical files to their new location
Bring the database back online.

For instance, for the SbManagementDB database, you'd do something like:

ALTER DATABASE SbManagementDB
MODIFY FILE (
    NAME = 'SbManagementDB'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.mdf'
);

ALTER DATABASE SbManagementDB
MODIFY FILE (
    NAME = 'SbManagementDB_Log'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.ldf'
);

ALTER DATABASE sbManagementDB SET OFFLINE;

Now you physically move the files, probably using Windows Explorer, into F:\path\to\new\location, and bring the database back online.

ALTER DATABASE sbManagementDB SET ONLINE;

In your question, you are attempting to take database files offline, which is not necessary, and causing you grief.


Benefits of making a sql server database as read-only?

You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
You can expect a modest performance benefit from absence of locking.
On versions of SQL Server prior to 2012 statistics can't be auto created or updated on read only databases.
Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default FILL_FACTOR settings will not be of benefit in a read only environment. Additionally create/update any statistics anticipated to be of use for SELECT queries if on version < 2012.


I need to hide an open Access table from users

This article at TechRepublic offers two approaches.
One is to set the table as hidden.

Right-click the object in the Database window.
Choose Properties.
Click the Hidden option.

The other is to rename the table.

Another way to hide a table is to add the prefix Usys to the table's name.

Both aren't really secure, as the object is still accessible, but it's not visible at least.


Will Rebuild and Reorganize improve Database Migration Script performance execution?

If you are just migrating the Schema itself, and not any of the data, Index Fragmentation of your user database tables should be irrelevant. The instructions in the migration script will come from system database tables holding the metadata for your user database/schema.


Availability Group Monitoring Redo Rates

Should we care about the redo rate when there is nothing in the redo queue?

If there is nothing in the redo queue and the send queue is empty then I wouldn't worry about it as there is literally nothing to be done. This will happen on AGs that have light usage or usage based on specific hours and is then idle the rest of the time.

If, however, there IS something in the log send queue and/or redo queue we are extremely interested in the redo rate.

We are ALWAYS interested in blocked redo threads as well.

  It would seem to me that the Log Send Queue and Redo Queue are better indicators of when the replicas are behind on replication. Would an alert based on the these queues be a better alert than alerting on redo alerts?

These all measure different aspects of the AG and where performance might be lacking. For example, we'd want to troubleshoot a large log send queue much differently than a low redo rate. Sure, they may be related but we'd be looking in two different areas at first.

It would be best to build in some logic for the alerts. I would still keep the redo alert and add one for blocked redo threads (since that one wasn't mentioned), but I would monitor the full stack as redo rate is just giving you one small piece of the puzzle.


I know it is too late for the OP but here some advice to tackle such a  problem:

The error message says that the clients sends but does not get a response. Most of the time it is a firewall problem.

The windows getaddrinfo function is described here. Error number 11001 is WSAHOST_NOT_FOUND and is described here:

  Host not found.
  No such host is known. The name is not an official host name or alias, or it cannot be found in the database(s) being queried. This error may also be returned for protocol and service queries, and means that the specified name could not be found in the relevant database.

Linux/Unix error numbers can found in the appropriate header files.
I don't know if that windows error message is is the reason for your error. Even if you can access the listener port with telnet that does not mean that there is no firewall problem. I you used share servers or an oracle RAC with a scan listener the database will try to connect to a server by a different prot that can be blocked by a firewall.
TNS ping does only contact the listner. So first you should check if the tnsping arrives at the server. Can you see it in the listner.log file? Check if you use the right tnsname.ora file by adding the connect string with a completely new tns-name and do a tnsping to this new tns-name. Do you get the same error message?
If you want to remove all your Oracle installations on a windows server you can do that by hand. Remove all the directories and registry entries. Look for appropriate advice.


If you look at incomes and expenses tables, you see they are the same, they differ only in a sign of the change of user balance. You can easily create only one table  of "transactions" to keep both and either add a column with a type [Income, Expense] or decide it only by a sing on a value column (I suppose you want that one as you talk about summing all expenses and incomes, but it is not defined in your question).

Then you can do simple

select sum(value) as balance from transactions where user_id = X and datetime <= Y;

As you can see, I use datetime as one column - I suggest you do do that as otherwise all conditions for checking ranges get quite cumbersome:

where date < xxx or (date = xxx and time <= yyy)
-- and now add once more for the upper limit

This is good enough if you need that only sometimes and have a good index with user_id and datetime and preferably a value too. But if know that you will check that often or that there will be really lots of transactions for each user, it makes sense adding one more column where you will store the value of user balance after applying that transaction. That way getting the user balance for a given datetime is like

select balance_value
from transactions
where user_id = X and datetime <= Y
order by datetime desc
limit 1;

with again using index on (user_id, datetime) for a really fast retrieval.

This can have a secondary usage too - it lets you simply verify the users_balance records and check that there is no transaction missing. That can come heplful in a case of a bug or problem with data storage.


How to speed up a loading data into MySQL from a zipped archive

Please keep in mind the behavior a mysqldump

It dumps all databases in alphabetical order
It dumps all tables in each database in alphabetical order
It loads each tables like this

Drops the table
Creates the Table
Table is Loaded

Disables Keys
Locks the Table for Writes
Multiple Exteneded INSERTs
Unlock the Table
Enable Keys

When the keys of table are disabled, only PRIMARY KEYs and UNIQUE KEYs are reloaded. When keys are enabled, then all non-unique keys are built linearly.

I would recommend you add this to /etc/my.cnf setting bulk_insert_buffer_size as follows

[mysqld]
bulk_insert_buffer_size=256M
key_buffer_size=1G

because the default is 8M. You do not need to restart mysql. Just run this SQL command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

Give it a Try !!!


Shrinking Databases in a maintenance plan

What you are saying is right. The database should not be shrinked. However there are some exceptions.

Let's say you move a lot of historical data out of your DB and it goes down from 100GB to 10GB. You may want to reduce it back to 10 or 20 or 50GB.

The thing to remember when you decide to go on with a shrink is that data (clustered and non clustered indexes) will get (possibly highly) fragmented. By data in indexes, I mean that rows won't be ordered by the indexed columns and stored continuously on continuous pages anymore. The server will have to jump through pages to read continuous rows (=> more IO).

Therefore, after a shrink you should defragment or rebuild your indexes. Choose for each index if it should be rebuilt or defragmented based on their fragmentation percentages.
You probably already have a maintenance plan taking care of this so you just have to run it again right after the shrink.

If your need their support, you might have to make them happy... The guy asking you to do it probably know nothing about SQL Server and is just following a written procedure.
In that case, you may proceed with the shrink and then update the indexes.

Note that I don't know much about Enterprise Vault.


using a column that references an object_id in your own table

Do not rely on object_id for your application.
My experience is that once an object is created its object_id will be stable for as long as that object exists. The difficulty is in knowing the meaning of "as long as it exists."
If the object is dropped and re-created it will get a new object_id, even if the exact same statement is used both times, creating an object with the same name.
If the creation script runs against different databases (say DEV and TEST) it will generate different object_ids.
If the database is restored old object_id values may re-appear.
Most risky is that stability of object_ids is not documented and therefore is not guaranteed. A patch, upgrade, security patch or anything else may change IDs for existing objects if MS determines this is something they want to do for any reason whatsoever at all.
MS can use object_id in their internal metadata because they know all the places that can invalidate these values and can write their code accordingly. They do not publish these details for us, as application programmers, to consider.
If you need to reference objects in a data-driven way, store their names. Use the correct datatype (SYSNAME) and don't forget to store the object's schema, too. If a numeric value must be passed around there are some options. An identity column could be added. Values will be different between environments and when objects are removed and re-added. Really this is no better than object_id. Better may be to calculate a hash of the schema and object names. If the hash algorithm used is communicated broadly every consumer should be able to generate it on demand. If you have authority over the objects' names (which seems likely since you're writing them) you could simply choose the corresponding ids and publish them. Don't forget the unique constraint on the name and id columns!


Best high availability solution to avoid maintenance downtime?

Have you considered a Failover Cluster Instance?  It'll give you the high availability of the entire instance.  And provided you have more than one node in the FCI, you would be able to failover while patching the other server.

There are quite a few details regarding failover clusters, but please see this MSDN reference to get an overview and other how-to topics.

  We currently only have one production SQL Server 2008 server.

You will need a cluster (WSFC), and you will benefit from multiple servers/nodes to ensure maximum uptime.

  We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.

You can have automatic failover of the FCI to a different node in the cluster.  When are you patching a SQL Server instance, you will basically want to shuffle current and possible owners and apply the patch to the passive node.  This will give you a solution of minimized downtime for updates.

The real question you need to answer is, at what level are you looking for redundancy and high availability?  The instance?  A database?  Database objects?  By your question, I'm guessing at the instance level, which is why I recommend an FCI.  But if a database, you could consider database mirroring.


RMAN only works at the block level & has no idea about the contents of a given block, and therefore cannot do this.

You need to use expdp with the query parameter:

expdp phil/phil directory=myexportdir dumpfile=yourtable.dmp query=yourtable:\"where groupid in (1,2,3)\" tables=yourtable

Obviously, this isn't incremental. There's no really easy way of doing it incrementally based on a query/the data. You'd have to roll your own tool to do it - flashback query could help.

What data volume are you dealing with? If < a few million rows, just export the full data each time.


How to export table definitions and data in mysql workbench

I think you are looking for the following window on workbench.


Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

For large tables I would definitely work with T-SQL or Powershell rather than the wizard - clunky UIs that run all kinds of background things and take over-protective locks do not have a great track record. For example, this wizard actually offers an option to generate Powershell:

I would do that. Here is what it produces:

# Generated by SQL Server Management Studio at 9:10 PM on 2/22/17

Import-Module SqlServer
# Load reflected assemblies

[reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null

# Set up connection and database SMO objects

$sqlConnectionString = 'Data Source=SERVER\INSTANCE;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"'
$sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString
$serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection
$smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection
$smoDatabase = $smoServer.Databases['DatabaseName']

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [dbo].[TableName]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.TableName.ColumnName -EncryptionType Deterministic -EncryptionKey ColumnKey

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Now, this still might take a long time; I haven't done thorough benchmarks on encrypting existing data. But you won't run out of memory (this error was because of SSMS limitations, not SQL Server's).

I looked quickly and, while it is easy to generate T-SQL scripts for creating master and column keys, I don't know of a way to run ALTER TABLE. This passes syntax checks:

ALTER TABLE dbo.TableName
ALTER COLUMN ColumnName varchar(11)
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ColumnKey,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');

However it fails at runtime with:

  Msg 206, Level 16, State 2
  Operand type clash: varchar is incompatible with varchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'DatabaseName') collation_name = 'Latin1_General_BIN2'


How to remove known elements from a JSON[] array in PostgreSQL?

jsonb in Postgres 9.4 or later
Consider the jsonb data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=) for jsonb. Most people will want to switch.
Depesz blog about jsonb.
json
There is no = operator defined for the data type json, because there is no well defined method to establish equality for whole json values. But see below.
You could cast to text and then use the = operator. This is short, but only works if your text representation happens to match. Inherently unreliable, except for corner cases. See:

How to query a json column for empty objects?

Or you can unnest the array and use the ->> operator to .. get JSON object field as text and compare individual fields.
Test table
2 rows: first one like in the question, second one with simple values.
CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
 , jar    json[]
);

INSERT INTO t VALUES
   (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
        ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
        ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')

 , (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
        ,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
        ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');

Demos
Demo 1
You could use array_remove() with text representations (unreliable).
SELECT tbl_id
     , jar, array_length(jar, 1) AS jar_len
     , jar::text[] AS t, array_length(jar::text[], 1) AS t_len
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM   tbl;

Demo 2
Unnest the array and test fields of individual elements.
SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  j->>'value' <> '03334/254146'
AND    j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP  BY 1;

Demo 3
Alternative test with row type.
SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  (j->>'value', j->>'typeId') NOT IN (
         ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
        ,('a', 'x')
       )
GROUP  BY 1;

UPDATE as requested
Finally, this is how you could implement your UPDATE:
UPDATE tbl t
SET    jar = j.jar
FROM   tbl t1
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT j
      FROM   unnest(t1.jar) AS j  -- LATERAL JOIN
      WHERE  j->>'value'  <> 'a'
      AND    j->>'typeId' <> 'x'
      ) AS jar
   ) j
WHERE  t1.tbl_id = 2              -- only relevant rows
AND    t1.tbl_id = t.tbl_id;

db<>fiddle here
About the implicit LATERAL JOIN:

How to turn JSON array into Postgres array?
Query for element of array in JSON column

About unnesting arrays:

How to preserve the original order of elements in an unnested array?

DB design
To simplify your situation consider an normalized schema: a separate table for the json values (instead of the array column), joined in a n:1 relationship to the main table.


You can do it either way.

There isn't much benefit to starting all of the overhead for sharding now, unless you simply want to try it out to see how it works. It is important to pick the correct shard key, so you would want to make sure your schema and insert/update usage is firm enough to know what to pick.

Kristina Chodorow has a great blog entry about how to pick a shard key: http://www.snailinaturtleneck.com/blog/2011/01/04/how-to-choose-a-shard-key-the-card-game/

For general information about how sharding works:
http://www.snailinaturtleneck.com/blog/2010/03/30/sharding-with-the-fishes/

For some instructions on how to set up clustering on a single machine, see here:
http://www.mongodb.org/display/DOCS/A+Sample+Configuration+Session
and here:
http://www.snailinaturtleneck.com/blog/2010/08/30/return-of-the-mongo-mailbag/


Here are some options.

CBool—Converts a value to a Boolean data type.
CDate—Converts a value to a Date data type.
CInt—Converts a value to an Integer data type.
CStr—Converts a value to a String data type.
CVar—Converts a value to a Variant data type.

Syntax

CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)

Converting to an Integer Data Type

The CInt function takes a numeric or string value and converts it to an Integer data type. The argument is required and needs to represent a value within the range of -32,678 to 32,767. If the argument contains a decimal, Access rounds to the next whole number. A value of .5 or higher is rounded up; anything lower is rounded down. Some examples of CInt functions follow:

Dim MyDouble, MyInt
MyDouble = 2345.5678    ' MyDouble is a Double.
MyInt = CInt(MyDouble)    ' MyInt contains 2346.

IN SQL, you could use CAST.

SELECT
   CAST([Column] AS INT) AS [Column Name]
FROM [Table]
WHERE ...

Or, if you wanted to SUM off the SQL query.

SUM(CAST([Column] AS INT)) AS [Column Name]


Validation of availability for a new order

I only see you doing a SELECT in both variants. If you want to make sure you don't sell more than you have in store (stock), you must decrease your stock in the same transaction you place the order. Use a data-modifying CTE for the job (Postgres 9.1 or later):
WITH u AS (
   UPDATE product SET quant = quant - <put_order_quant_here>
   WHERE  product_id = <order_prod_id>
   AND    quant >= <put_order_quant_here>
   RETURNING product_id, <put_order_quant_here> AS quant
   )
INSERT INTO order_detail (order_id, product_id, quant)
SELECT <put_order_id_here>, product_id, quant
FROM   u;

The UPDATE in the CTE only returns values if the product has sufficient stock (and it takes a write lock on the row). In this case, the quantity is reduced in the same transaction, just before the order is placed.
Put all order-details into one transaction, if any of them fails to INSERT then ROLLBACK.
Possible deadlocks
This scenario can easily lead to deadlocks. Say, you have two orders coming in at the same time, both want product A and B. The first order starts with A, the second starts with B. Then the two transactions block each other out. Each waits for the other to complete. A deadlock ensues.
In PostgreSQL a transaction will wait for some time when it is stalled by locks. Depending on your setting of deadlock_timeout (default is 1 sesond), checks for possible deadlocks are performed.
Once detected, one transaction is aborted and a deadlock exception reported. The other one can finish. Which one is hard to predict.
There is a simple way to avoid this kind of deadlocks: Always place your order_details in a consistent order. Like products ordered by product_id. This way, the above scenario can never happen.


How can I assign group IDs depending on content?

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

You can get it using a simple CASE.

select linenr, key, value,
       case when key <> '...' then 1 end as rst
from   source;

linenr | key   | value      |  rst
-----: | :---- | :--------- | ---:
     2 | tags  | a          |    1
     3 | ...   | b          | null
     4 | title | The Title  |    1
     5 | note  | this is    |    1
     6 | ...   | an EXAMPLE | null
     8 | title | over       |    1
     9 | ...   | three      | null
    10 | ...   | lines      | null
    11 | about | grouping   |    1

Now SUM(rst) ignores null values and do the trick.

select linenr, key, value,
       sum(rst) over (order by linenr) grp
from (
      select linenr, key, value,
             case when key <> '...' then 1 end as rst
      from   source
     ) x;

linenr | key   | value      | grp
-----: | :---- | :--------- | --:
     2 | tags  | a          |   1
     3 | ...   | b          |   1
     4 | title | The Title  |   2
     5 | note  | this is    |   3
     6 | ...   | an EXAMPLE |   3
     8 | title | over       |   4
     9 | ...   | three      |   4
    10 | ...   | lines      |   4
    11 | about | grouping   |   5

dbfiddle here


Can we generate SQL Server MDS Model from an existing database schema?

Does SQL Server MDS support creating a Model by reverse engineering a database?

I wrote a command-line utility to do this, called MdsModelImport.  It's intended to be a one-time import, and simply save you from having to type all the attributes.