Replicate some tables without transferring the entire log I have a mysql database that contains some tables with private information, and some tables with public information.

I would like to replicate only the tables containing public information from one database to another, making sure that NO confidential information ever gets stored on the slave.

I know I can use the replicate-do-table to specify that only some tables are replicated, but my understanding is that the entire bin log is transferred to the slave.

Is there a way to ensure that only the public information is transferred to the slave?

I am very hesitant to add another copy of the database to an existing server - I just don't think the existing server has the available capacity, in either RAM or CPU.

The only way to filter on one DB server is to run multiple instances of MySQL on one DB Server.

SERVER DB1

Port 3306 would be your normal database instance for your application

Port 3307 would be a slave of Port 3306

There are a few things you need to do with the MySQL instance running on DB1

Enable the following in my.cnf for

log-bin=mysql-bin
log-slave-updates
replicate-do-table=db1.public_tb1
replicate-do-table=db2.public_tb2
...
replicate-do-table=dbn.public_tbn

As an option, convert all tables in DB1 3307 to the BLACKHOLE storage engine.

That way, DB1 3307 only has binlogs with information. No actual data.

SERVER DB2

Setup MySQL instance and make it Slave from the 3307 instance of DB1. Why is that good ?

Because the binlogs in the DB1 3307 instance should only contain the public info. Thus, all slaves from DB1 3307 will only see public info.

CAVEAT

Please see my other posts on how to use BLACKHOLE tables in replication

Apr 18, 2013 : Single slave - multiple master MySQL replication
Feb 03, 2012 : One slave, multiple masters MySql
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?
Mar 11, 2011 : MySQL in star topology

For something specialized like this, a common approach is to copy the rows that you want to move into a temp table/database (or equiv buffer) and then have replication take it from there.  The industry term for this is ETL (Extract, Transform, Load).  The intermediate/temp/buffer gives you an opportunity to manipulate (Transform step) the data before it ships-out (Load step).  It can be a little tedious but guarantees a consistent result.


Find numbers not used in a column How can I find in a column filled with numbers, the ones that are not used.

Table_A:

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'client_code' int(4) unsigned NOT NULL,
'client_name' varchar(50) NOT NULL,
PRIMARY KEY ('id')

INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

If I SELECT 'client_code' FROM Table_A the output will be 1,2,5,6,10. How can I have the numbers that are not used?

(3,4,7,8,9,11,12...)

To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.

Helper table to provide numbers from 0-9:

CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Building on that, generate a complete range of numbers:

SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

Now you can generate the list of missing numbers, exactly as requested:

SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:

SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

-> SQLfiddle demo.

You can have it by the difference between  "rownumber"  generated for all the rows in the Table_A and client_code:

select * from (
  SELECT @ROWNUMBER:=@ROWNUMBER+1 AS COL FROM Table_A
  JOIN    (SELECT @ROWNUMBER := 0) D) B
where B.COL not in (select client_code from Table_A);

your client_code values have to start from 1 and increment by one.


Why is selecting all resulting columns of this query faster than selecting the one column I care about? I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.

This is the query:

select top 1000 c.ID
from ATable a
    join BTable b on b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
    join CTable c on c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
where (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff)
    and b.IsVoided = 0
    and c.ComplianceStatus in (3, 5)
    and c.ShipmentStatus in (1, 5, 6)
order by a.LastAnalyzedDate

This finished with 2,473,658 logical reads, mostly in Table B.  It used 26,562 CPU and had a duration of 7,965.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=BJAp2mQIQ

When I change c.ID to *, the query finished with 107,049 logical reads, fairly evenly spread between all three tables.  It used 4,266 CPU and had a duration of 1,147.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=SyZYn7QUQ

I attempted to use the query hints suggested by Joe Obbish, with these results:
select c.ID without hint: https://www.brentozar.com/pastetheplan/?id=SJfBdOELm
select c.ID with hint: https://www.brentozar.com/pastetheplan/?id=B1W___N87
select * without hint: https://www.brentozar.com/pastetheplan/?id=HJ6qddEIm
select * with hint: https://www.brentozar.com/pastetheplan/?id=rJhhudNIQ

Using the OPTION(LOOP JOIN) hint with select c.ID did drastically reduced the number of reads compared to the version without the hint, but it is still doing about 4x the number of reads the select * query without any hints.  Adding OPTION(RECOMPILE, HASH JOIN) to the select * query made it perform much worse than anything else I have tried.

After updating statistics on the tables and their indexes using WITH FULLSCAN, the select c.ID query is running much faster:
select c.ID before update: https://www.brentozar.com/pastetheplan/?id=SkiYoOEUm
select * before update: https://www.brentozar.com/pastetheplan/?id=ryrvodEUX
select c.ID after update: https://www.brentozar.com/pastetheplan/?id=B1MRoO487
select * after update: https://www.brentozar.com/pastetheplan/?id=Hk7si_V8m

select * still outperforms select c.ID in terms of total duration and total reads (select * has about half the reads) but it does use more CPU.  Overall they are much much closer than before the update, however the plans still differ.

The same behavior is seen on 2016 running in 2014 Compatibility mode and on 2014.  What could explain the disparity between the two plans?  Could it be that the "correct" indexes have not been created?  Could statistics being slightly out of date cause this?

I tried moving the predicates up to the ON part of the join, in multiple ways, but the query plan is the same each time.

After Index Rebuilds

I rebuilt all of the indexes on the three tables involved in the query.  c.ID is still doing the most reads (over twice as many as *), but CPU usage is about half of the * version.  The c.ID version also spilled into tempdb on the sorting of ATable:
c.ID: https://www.brentozar.com/pastetheplan/?id=HyHIeDO87
*: https://www.brentozar.com/pastetheplan/?id=rJ4deDOIQ

I also tried forcing it to operate without parallelism, and that gave me the best performing query: https://www.brentozar.com/pastetheplan/?id=SJn9-vuLX

I notice the execution count of operators AFTER the big index seek that is doing the ordering only executed 1,000 times in the single-threaded version, but did significantly more in the Parallelized version, between 2,622 and 4,315 executions of various operators.

It's true that selecting more columns implies that SQL Server may need to work harder to get the requested results of the query. If the query optimizer was able to come up with the perfect query plan for both queries then it would be reasonable to expect the SELECT * query to run longer than the query that selects all columns from all tables. You have observed the opposite for your pair of queries. You need to be careful when comparing costs, but the slow query has a total estimated cost of 1090.08 optimizer units and the fast query has a total estimated cost of 6823.11 optimizer units. In this case, it could be said that the optimizer does a poor job with estimating total query costs. It did pick a different plan for your SELECT * query and it expected that plan to be more expensive, but that wasn't the case here. That type of mismatch can happen for many reasons and one of the most common causes is cardinality estimate problems. Operator costs are largely determined by cardinality estimates. If a cardinality estimate at a key point in a plan is inaccurate then the total cost of the plan may not reflect reality. This is a gross oversimplification but I hope that it will be helpful for understanding what's going on here.

Let's start by discussing why a SELECT * query might be more expensive than selecting a single column. The SELECT * query may turn some covering indexes into noncovering indexes, which might mean that the optimizer needs to do addition work to get all of the columns it needs or it may need to read from a larger index. SELECT * may also result in larger intermediate result sets which need to be processed during query execution. You can see this in action by looking at the estimated row sizes in both queries. In the fast query your row sizes range from 664 bytes to 3019 bytes. In the slow query your row sizes range from 19 to 36 bytes. Blocking operators such as sorts or hash builds will have higher costs for data with a larger row size because SQL Server knows it's more expensive to sort larger amounts of data or to turn it into a hash table.

Looking at the fast query, the optimizer estimates that it needs to do 2.4 million index seeks on Database1.Schema1.Object5.Index3. That is where most of the plan cost comes from. Yet the actual plan reveals that only 1332 index seeks were done on that operator. If you compare the actual to the estimated rows for the outer parts of those loop joins you'll see large differences. The optimizer thinks that many more index seeks will be needed to find the first 1000 rows needed for the query's results. That's why the query has a relatively high cost plan but finishes so quickly: the operator that was predicted to be the most expensive did less than 0.1% of its expected work.

Looking at the slow query, you get a plan with mostly hash joins (I believe the loop join is there just to deal with the local variable). Cardinality estimates definitely aren't perfect, but the only real estimate problem is right at the end with the sort. I suspect most of the time is spent on the scans of the tables with hundreds of millions of rows.

You may find it helpful to add query hints to both versions of the query to force the query plan associated with the other version. Query hints can be a good tool to figure out why the optimizer made some of its choices. If you add OPTION (RECOMPILE, HASH JOIN) to the SELECT * query I expect you'll see a similar query plan to the hash join query. I also expect that query costs will be much higher for the hash join plan because your row sizes are much bigger. So that could be why the hash join query wasn't chosen for the SELECT * query. If you add OPTION (LOOP JOIN) to the query that selects just one column I expect you'll see a query plan similar to the one for the SELECT * query. In this case, reducing the row size shouldn't have much of an impact on the overall query cost. You might skip the key lookups but that's a small percentage of the estimated cost.

In summary, I expect that the larger row sizes needed to satisfy the SELECT * query push the optimizer towards a loop join plan instead of a hash join plan. The loop join plan is costed higher than it should be due to cardinality estimate issues. Reducing the row sizes by selecting just one column greatly reduces the cost of a hash join plan but probably won't have much of an effect on the cost for a loop join plan, so you end up with the less efficient hash join plan. It's hard to say more than this for an anonymized plan.

Can you please include the index scripts?
Have you eliminatated possible issues with "parameter sniffing"? https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
I have found this
technique to be helpful in some cases:
a) rewrite each table as a
subquery, following these rules:
b) SELECT - put join columns
first
c) PREDICATES - move into their respective subqueries
d)
ORDER BY - move into their respective subqueries, sort on JOIN COLUMNS
FIRST
e) Add a wrapper query for your final sorting and SELECT.

The idea is to pre-sort join columns inside each subselect, putting join columns first in each select list.

Here is what I mean....

SELECT ... wrapper query
FROM
(
    SELECT ...
    FROM
        (SELECT ClientID, ShipKey, NextAnalysisDate
         FROM ATABLE
         WHERE (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff) -- Predicates
         ORDER BY OrderKey, ClientID, LastAnalyzedDate  ---- Pre-sort the join columns
        ) as a
        JOIN
        (SELECT OrderKey, ClientID, OrderID, IsVoided
         FROM BTABLE
         WHERE IsVoided = 0             ---- Include all predicates
         ORDER BY OrderKey, OrderID, IsVoided       ---- Pre-sort the join columns
        ) as b ON b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
        JOIN
        (SELECT OrderID, ShipKey, ComplianceStatus, ShipmentStatus, ID
         FROM CTABLE
         WHERE ComplianceStatus in (3, 5)       ---- Include all predicates
             AND ShipmentStatus in (1, 5, 6)        ---- Include all predicates
         ORDER BY OrderID, ShipKey          ---- Pre-sort the join columns
        ) as c ON c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
) as d
ORDER BY d.LastAnalyzedDate


About single threaded versus multithreaded databases performance H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.

My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many processes? What is the trigger? Anyone has experience to share?

Summary

The usual bottleneck is disk access
SSD's are fast, but fragile (failure procedure is a must)
One long query on a single thread system will block all others
Configuring multi-threading system can be tricky
Multithreaded databases are beneficial even on single core systems

If there is one thing I can say about MySQL is that InnoDB, its transactional (ACID-compliant) storage engine, is indeed multithreaded. However, it is as multithreaded as YOU CONFIGURE IT !!! Even right "out of the box," InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you must remember to activate a lot of options.

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Most people are unaware of these features and a quite satisfied with InnoDB just doing ACID-compliant transactions. If you tweak any of these options, you do so at your own peril.

I have played with MySQL 5.5 Multiple Buffer Pool Instances (162GB in 9 buffer pools instances) and have attempted to have data auto-partitioned in memory this way. Some experts say that this should give you 50% performance improvement. What I got was a ton of thread locking that actually made InnoDB crawl. I switched to 1 buffer (162GB) and all was well again in the world. I guess you need Percona experts at your disposal to set this. I'll be at the Percona MySQL Conference in New York tomorrow and will ask about this if opportunity affords itself.

In conclusion, InnoDB behaves well now in a multi CPU server given its default settings for multithreaded operations. Tweaking them takes great care, great patience, great documentation, and great coffee (or Red Bull, Jolt, etc.).

Good morning, good evening, and good night !!!

UPDATE 2011-05-27 20:11

Came back from Percona MySQL Conference in New York on Thursday. What a conference. Learned a great deal, but I got an answer I will look into concerning InnoDB. I was informed by Ronald Bradford that setting innodb_thread_concurrency to 0 will let InnoDB decide the best course of action internally with thread concurrency. I will experiment with this further in MySQL 5.5.

UPDATE 2011-06-01 11:20

As far as one long query goes, InnoDB is ACID-compliant and operates very well using MultiVersion Concurrency Control. Transactions should be able carry isolation levels (repeatable reads by default) that prevents blocking others from accessing data.

As for multi core systems, InnoDB has come a long way. In the past, InnoDB could not perform well in a multicore environment. I remember having to run multiple mysql instances on a single server to get the multiple cores to distribute the multiple mysqld processes across the CPUs. This is no longer necessary, thanks to Percona, and later MySQL (eh, Oracle, saying that still makes me gag), as they have developed InnoDB into a more mature storage engine that can access the cores with simplicity without much tuning. The current instance of InnoDB today can operate well in a single core server.

A multithreaded database will only benefit you when you have more than 1 parallel query going to the database. It depends on number of users you have. If you have more than ten users working on the application at the same time, most likely they are going to produce more than one query on the database at the same time.

Moreover, a multithreaded database can only benefit when there's multi core on the CPU. If there's single core, multi threaded database has to queue the job and execute them sequentially on the single core. When there's multi-core, each core can run one thread in parallel. Thus better performance.

Does this answer your query?


Filter condition not correctly applied to Clustered Columnstore index Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match:

declare @barcode nchar(22)=N'RECB012ZUKI449M1VBJZ'
declare @tableId int = null
declare @total decimal(10, 2) = 5.17

SELECT 1
FROM
    [dbo].[transaction] WITH (INDEX([IX_Transaction_TransactionID_PaymentStatus_DeviceID_DateTime_All]))
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

SELECT 1
FROM
    [dbo].[transaction]
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

Why could this be happening?

Further info:

The Non Clustered Index in the top statement is NOT filtered
CheckDB returns 0 issues
Server Version: Microsoft SQL Azure (RTM) - 12.0.2000.8   Dec 19 2018 08:43:17   Copyright (C) 2018 Microsoft Corporation

Paste the Plan link:

https://www.brentozar.com/pastetheplan/?id=S1w_rU68E

Further Info:

Have ran dbcc checktable ([transaction]) with all_errormsgs, extended_logical_checks, data_purity which indicates no issues.

I can reliably reproduce the issue against this table when restoring a backup of this database.

This is a bug with SQL Server. If a column is deleted from a table with a clustered columnstore index, and then a new column is added with the same name, it appears to be using the old, deleted column for the predicate. Here is the MVCE:

This script starts off with 10000 rows with statusId of 1 and statusId2 of 5 - then drops the statusID column and renames statusId2 to statusId. So at the end all rows should have a statusId of 5.

But the following query  hits the non clustered index...

select *
from example
where statusId = 1
    and total <= @filter
    and barcode = @barcode
    and id2 = @id2

... and returns 2 rows (with the selected statusId different from implied by the WHERE clause)...

+-------+---------+------+-------+----------+
|  id   | barcode | id2  | total | statusId |
+-------+---------+------+-------+----------+
|     5 |    5    | NULL |  5.00 |        5 |
| 10005 |    5    | NULL |  5.00 |        5 |
+-------+---------+------+-------+----------+

... whereas this one accesses the columnstore and correctly returns 0

select count(*)
from example
where statusId = 1

MVCE

/*Create table with clustered columnstore and non clustered rowstore*/
CREATE TABLE example
(
id        INT IDENTITY(1, 1),
barcode   CHAR(22),
id2       INT,
total     DECIMAL(10,2),
statusId  TINYINT,
statusId2 TINYINT,
INDEX cci_example CLUSTERED COLUMNSTORE,
INDEX ix_example (barcode, total)
);

/* Insert 10000 rows all with (statusId,statusId2) = (1,5) */
INSERT example
       (barcode,
        id2,
        total,
        statusId,
        statusId2)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 1,
                   statusId2 = 5
FROM   sys.all_columns c1, sys.all_columns c2;

ALTER TABLE example
  DROP COLUMN statusid
/* Now have 10000 rows with statusId2 = 5 */

EXEC sys.sp_rename
  @objname = N'dbo.example.statusId2',
  @newname = 'statusId',
  @objtype = 'COLUMN';
/* Now have 10000 rows with StatusID = 5 */

INSERT example
       (barcode,
        id2,
        total,
        statusId)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 5
FROM   sys.all_columns c1, sys.all_columns c2;
/* Now have 20000 rows with StatusID = 5 */

DECLARE @filter  DECIMAL = 5,
        @barcode CHAR(22) = '5',
        @id2     INT = NULL;

/*This returns 2 rows from the NCI*/
SELECT *
FROM   example WITH (INDEX = ix_example)
WHERE  statusId = 1
       AND total <= @filter
       AND barcode = @barcode
       AND id2 = @id2;

/*This counts 0 rows from the Columnstore*/
SELECT COUNT(*)
FROM   example
WHERE  statusId = 1;

I have also raised an issue on Azure feedback portal:

And for anyone else who encounters this, rebuilding the Clustered Columnstore Index fixes the problem:

alter index cci_example on example rebuild

Rebuilding the CCI only fixes any existing data. If new records are added, the issue arises again on these records; so currently the only known fix for the table is to recreate it entirely.

Based on the plans, it appears that the Columnstore index was created with SET ANSI_NULLS OFF.  Tables and indexes retain the setting as it was when the index was created. You can check by creating a duplicate Columnstore index while ensuring that ANSI_NULLS is ON, then either dropping the original or disabling it.

But, unless you have discovered a SQL Server bug, this is the only way that the results could happen.


Getting error while starting mysql by just typing in mysql terminal I am getting an error while starting mysql, by just typing mysql in the  terminal:

The error is:

amtz@amtz-VPCEA23EN:~$ mysql
ERROR 1045 (28000): Access denied for user 'amtz'@'localhost' (using password: NO)

That is not how you start mysql from the command line.

When you type mysql in the command line it will launch the mysql-client not the mysql-server, which looks for the user/password options to be provided as arguments, if they aren't provided it will expect them to be specified in ~/.my.cnf or /etc/mysql/my.cnf. If it still can't find the details there then it will then try the your current TTY user, which is what has happened to you.

If you want to login from the command line you can provide the username and password like so:

mysql -u <username> -p

If you don't want to have to do that every time then you can set the details in your ~/.my.cnf file.

[client]
user=<username>
password="<password>"

If you do actually want to start the mysql-server then it would depend on your operating system. For instance on Ubuntu you would do that with:

sudo service mysql start

Generally speaking you should be able to do

/etc/init.d/mysql start

Did someone GRANT ... TO 'amtz'@'localhost'; ?  Apparently not.  When mysql (not mqsql) is first installed user=root can get in without a password.  That user should promptly assign a password to root and GRANT other users access.


How do I select distinct json columns? I have over 10,000 geojson rows in a table. How do I go about selecting only distinct coordinates?

The data was imported from a csv file into PostgreSQL 9.5. The columns in the table are:

Trip Duration (seconds)
Start Time and Date
Stop Time and Date
Start Station Name
End Station Name
Station ID
Station Lat/Long

The imported contains I took the latitude and longitude columns and turned it into a geojson object:

update h set thestartgeom =  st_asgeojson(startstationgeom);

The column thestartgeom in the table is

  thestartgeom text;

The database contains 10,000+ lat/lng and other records. Some of the coordinates are duplicated. I am trying to eliminate all duplicates.

startstationgeom is a geometry(POINT,4326).

Here is the geojson column:

                    thestartgeom
---------------------------------------------------------------------

{"type":"Point","coordinates":[40.6882247924805,-73.9793853759766]}
{"type":"Point","coordinates":[40.7329139709473,-74.0071105957031]}
{"type":"Point","coordinates":[40.7429084777832,-73.9770584106445]}
{"type":"Point","coordinates":[40.7160568237305,-73.9919052124023]}
{"type":"Point","coordinates":[40.7497177124023,-74.0029525756836]}
{"type":"Point","coordinates":[40.7349281311035,-73.9920043945312]}
{"type":"Point","coordinates":[40.7281455993652,-73.9902114868164]}
{"type":"Point","coordinates":[40.691650390625,-73.9999771118164]}
{"type":"Point","coordinates":[40.7501983642578,-73.9909286499023]}
{"type":"Point","coordinates":[40.7503814697266,-73.9833908081055]}

You'll want PostGIS's ST_GeomFromGeoJSON().

SELECT DISTINCT ST_GeomFromGeoJSON(geojson)
FROM tbl;

Should work to get the points. However may find this is faster... I'd be interested in seeing a benchmark. ST_Union will eliminate duplicate points and geometries too.

SELECT ST_Union(ST_GeomFromGeoJSON(geojson))
FROM tbl;

Update

Is it faster, what advantages and disadvantages does this have?

DISTINCT requires a SORT. faster to sort floats than strings.
My ST_Mem_Size(ST_GeomFromGeoJSON(geojson)) shows 32 bytes (two floats).
Not sure how much space the raw JSON takes, likely more than 32 bytes.
Likely have to have these as ::geography at some time.
Processing the JSON and creating points is a one time cost but it may be significant.
ST_GeomFromJSON supports all geometry objects. Including MultiPoints.

Update2

If you already got the type as startstationgeom, just do

SELECT ST_AsGeoJSON(ST_Union(startstationgeom)) FROM table

That will output only unique geometries.

simple form of query is:

select
    max(id),
    jtext->>'coordinates' as coordinates

from t_json
GROUP BY coordinates

more information about JSON - https://www.postgresql.org/docs/9.5/static/functions-json.html

just other form of same query:

select
    distinct
    jtext #>> '{coordinates,0}' as lat,
    jtext #>> '{coordinates,1}' as lon
from t_json

tyson and j_text - it name of table and json column in my example

first form allow return other columns, not only coordinates in distinct query


View the final query executed by PostgreSQL Function I have a PostgreSQL function with Language plpgsql.
Since the EXPLAIN ANALYZE VERBOSE for my function executed like this:

EXPLAIN ANALYZE VERBOSE SELECT foo()

doesn't seem to log any valuable details except the following:

"Result  (cost=0.00..5.25 rows=1000 width=32) (actual time=122.115..122.204 rows=25 loops=1)"
"  Output: postgre.foo()"
"Planning time: 0.026 ms"
"Execution time: 122.243 ms"

I wanted to know if there is any way I can see the real query\queries used by the function, so that I can do EXPLAIN ANALYZE on these SQL queries.

Thanks.

In psql just do \sf name_of_function and it will show you the source code. Or \ef will load it into an editor.


Analyze, Vacuum equivalent for SQL Server? In Postgres, ANALYZE collects statistics about the contents of tables in the database, and store it. These statistics are used to determine the most efficient query plan, in PG. Running analyze command improves db's performance. I want to know if there is something similar in SQL Server.

Does SQL Server have commands equivalent to Postgres' Analyze and Vacuum?

If SQL Server doesn't have the exact equivalents, are there any similar commands?

If those commands do have SQL Server versions, please explain them or post links to useful documentations.

As far as I understand, rough equivalent of ANALYZE would be UPDATE STATISTICS.

  Updates query optimization statistics on a table or indexed view. By
  default, the query optimizer already updates statistics as necessary
  to improve the query plan; in some cases you can improve query
  performance by using UPDATE STATISTICS or the stored procedure
  sp_updatestats to update statistics more frequently than the default
  updates.

Quite often automatic updates of statistics is enough, but I came across few cases when I had to do it manually to get a good query plan. One common case is when rows are added to a large table and the range of values for new rows lies outside the existing statistics histogram, the so called Ascending Key Problem.

Rough equivalent of VACUUM FULL would be commands that shrink database.

  Shrinking data files recovers space by moving pages of data from the
  end of the file to unoccupied space closer to the front of the file.
  When enough free space is created at the end of the file, data pages
  at end of the file can be deallocated and returned to the file system.

In many cases shrinking a database is a bad idea.

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

When is it OK to shrink a Database?

The feature is there, but it should be used to address some very specific problems.

As @dezso pointed out in the comments, simple VACUUM command only marks pages as free for reuse in the same table, it doesn't compact the file. SQL Server does it automatically via the Ghost cleanup background process. Actually, it is possible to turn it off. So, it looks like this Ghost cleanup process is a rough equivalent to a simple VACUUM.

Try this:
DBCC SHRINKDATABASE (UserDB, 10);
"UserDB"- the database name.
"10" -percent free space in the database.


Passing comma separated values to parameters in stored procedure I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure how to make it work for multiple values.

Sample table:

CREATE TABLE [dbo].FinalStatus
(
    [ID] [int] Primary key IDENTITY(1,1) NOT NULL,
    [Col1] [varchar](15) NULL,
    [Col2] [varchar](15) NULL,
    [Col3] [varchar](100) NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL DEFAULT (getdate())
)

Test data:

Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg2')
Insert into FinalStatus (Col1, Col2, Col3) values ('11','C21','Some Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('12','BC21','Some Msg2')

Stored procedure:

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL,
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)

    SET @c1 = @col1
    SET @c2 = @col2

    SELECT
        Col2, Col1,
        LastUpdatedDate, Col3
    FROM
        dbo.FinalStatus
    WHERE
        (Col1 = @c1 OR @c1 IS NULL)
        AND (Col2 = @c2 OR @c2 IS NULL)
    ORDER BY
        LastUpdatedDate DESC
END

Execution script for single value (it works till here):

--To get all data
EXEC [dbo].[FindResult]

--passing first parameter alone
EXEC [dbo].[FindResult] @col1 = '10', @col2 = NULL

--passing second parameter alone
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'c21'

Now how to make it return appropriate result even when we pass multiple values to the parameter?

Something like this:

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21, c21'

The underlying table would minimum have 100000 records at any given point in time.

Did tried reading "Arrays and Lists in SQL Server 2008" by Erland Sommarskog but its way over my head. So looking for some help in modifying this stored procedure.

There are several ways of doing it. Changing the data model may also be a better option if you can do it.
1. Comma separated parameters
If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all have their own issues. For more information you can look at this post from Aaron Bertrand: Split strings the right way – or the next best way
Sample query (using XML conversion):
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @delimiter nvarchar(1) = N',';
SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node);

Output:
v1
10
15
13
14

Main query:
The idea is to convert it to a table. It can then be used in a correlated sub query:
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @c2 nvarchar(100) = N'C21, B21';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c2, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Only the WHERE clause has to be updated in your procedure. Parameters remains of varchar type with comma separated values.
Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

2. XML parameter(s)
If you can change the parameter' types, the XML data type can be used. It can be easily deserialized by the procedure and the query.
This query is pretty similar to the previous one. However it gives a better control over invalid values and special characters since there is no conversion or search and replace.
DECLARE @c xml = N'
    <root>
        <c1>10</c1><c1>15</c1><c1>13</c1><c1>14</c1>
        <c2>C21</c2><c2>B21</c2>
    </root>';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c1') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c2') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

The parameter' types have to be changed to xml. I used only one variable with c1 and c2 nodes but 1 variable for each node (c1, c2, ...) and different node names can also be used:
DECLARE @c1 xml = N'<root><data>10</data><data>15</data><data>13</data><data>14</data></root>';
DECLARE @c2 xml = N'<root><data>C21</data><data>B21</data></root>';
DECLARE @c3 xml = N'...';

The correct path and variables must be updated in the nodes part of the CROSS APPLY.
Using .Net, Powershell or other languages, an array or other types can easily be converted to xml and used as a parameter of the procedure.
...
3. User-Defined Table Types
Another option would be to create a Table Value Type that can be used by the Stored Procedure parameters.
Table Type
CREATE TYPE [dbo].[TableTypeCols] AS TABLE
(
    [col] varchar(15)
);

Stored Procedure with Table Type parameter(s)
You then update the Stored Procedure using this newly created type:
CREATE OR ALTER PROCEDURE [dbo].[FindResult]
    @c1 [dbo].[TableTypeCols] READONLY
    , @c2 [dbo].[TableTypeCols] READONLY
AS
    SELECT * -- fs.[...], fs.[...], ...
    FROM [dbo].[FinalStatus] fs
    WHERE
        EXISTS (
            SELECT 1 FROM @c1 c1 WHERE c1.col = fs.Col1
        )
        OR EXISTS (
            SELECT 1 FROM @c2 c2 WHERE c2.col = fs.Col2
        );
GO;

READONLY is mandatory in the parameter declaration.
Stored Procedure call with Table Type parameter(s)
With SQL, you can call it the same way using a table variable:
DECLARE @tc1 [dbo].[TableTypeCols];
DECLARE @tc2 [dbo].[TableTypeCols];

INSERT INTO @tc1(col) VALUES('10'), ('15'), ('13'), ('14');
INSERT INTO @tc2(col) VALUES('C21'), ('B21');

EXEC dbo.FindResult @c1 = @tc1, @c2 = @tc2;

Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

Please note that if you are planning on using more than a couple rows for each parameters, you should run some performance test with real data and make sure it works properly on your system.
.Net C# Call
The procedure can the be call from you .Net code
// Create & Fill Parameter 1
DataTable dt1 = new DataTable();
dt1.Columns.Add("col", typeof (string));
DataRow row = dt1.NewRow();
row["col"] = ("10");
dt1.Rows.Add(row);
/*
    ... add more row ...
*/

// Create & Fill Parameter 2
DataTable dt2 = new DataTable();
dt2.Columns.Add("col", typeof (string));
DataRow row = dt2.NewRow();
row["col"] = ("C21");
dt2.Rows.Add(row);
/*
    ... add more row ...
*/

// Output dataset
DataSet ds = new DataSet("SQLDatabase");

using (SqlConnection conn = new SqlConnection(...))
{
    // Stored Procedure with table parameters
    SqlCommand sqlComm = new SqlCommand("dbo.FindResult", conn);
    sqlComm.CommandType = CommandType.StoredProcedure;

    // Parameter 1
    SqlParameter param = new SqlParameter("@c1", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt1
    };
    sqlComm.Parameters.Add(param);

    // Parameter 2
    SqlParameter param = new SqlParameter("@c2", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt2
    };
    sqlComm.Parameters.Add(param);

    // Call Stored Procedure
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;
    da.Fill(ds);
}

SqlDbType.Structured is mandatory.
Please note that I am not a .Net expert and there are other or better ways to write and use this code.
4. Json Type Parameters (Since SQL Server 2016)
With minimal changes, you can declare a NVARCHAR(MAX) parameters with json string.
json parameters
DECLARE @C1 NVARCHAR(MAX);
SET @jsonC1 =
N'[
    { "col" : "10"}, { "col" : "15"}, { "col" : "13"}, { "col" : "14"}
]';
DECLARE @C2 NVARCHAR(MAX);
SET @jsonC2 =
N'[
    { "col" : "C21"}, { "col" : "B21"}
]';

Query
SELECT *
FROM dbo.FinalStatus f
WHERE
EXISTS (
    SELECT 1 FROM OPENJSON(@C1) WITH(col nvarchar(15)) AS c1 WHERE c1.col = f.Col1
)
OR
EXISTS (
    SELECT 1 FROM OPENJSON(@C2) WITH(col nvarchar(15)) AS c2 WHERE c2.col = f.Col2
);

Please note that OPENJSON requires SQL Server 2016.

The simplest way I found was to use FIND_IN_SET:

FIND_IN_SET(column_name, values)

For example define values=(1,2,3) and execute:

SELECT name WHERE FIND_IN_SET(id, values)


How to format a phone number in a SELECT statement? I have a database with table name student.

I would like to display the register_number and phone_number of the student.

The phone_number should be in the following format:

+91-123-456-7890

and if the phone_number is NULL, then it should display N/A.

The table looks like:

R_NO      |     STUDENT_NAME    |  PHONE_NUMBER
-------------------------------------------------------
  1       |      Rajesh         |  9632545123
  2       |      Sridevi        |  9512647359
  3       |      Shiva          |  9632155862
  4       |      HariHaran      |  8426911231
  5       |      Ravi           |  9111558899
  6       |      Pauline        |  NULL

Different areas have differing number of digits in their prefix (wikipedia's list):

COALESCE(CONCAT('+',
  CASE
    WHEN LEFT(phone_number, 1) IN ('1', '7')
      THEN INSERT(INSERT(INSERT(phone_number, 2, 0, '-'), 6, 0, '-'), 10, 0, '-')
    WHEN CONVERT(LEFT(phone_number, 2), SIGNED) IN (21, 22, 23, 24, 25, 26, 29, 35, 37, 38, 42, 50, 59,
                                                    67, 68, 69, 80, 85, 87, 88, 96, 97, 99)
      THEN INSERT(INSERT(INSERT(phone_number, 4, 0, '-'), 8, 0, '-'), 12, 0, '-')
    ELSE   INSERT(INSERT(INSERT(phone_number, 3, 0, '-'), 7, 0, '-'), 11, 0, '-') END), 'N/A')

With the query above, all phone numbers will show as +Country_code-XXX-XXX-XXXX although each area has specific formatting rules, which vary greatly, so that formatting will look funny for certain regions, such as Australia where formatting is +54-X-XXXX-XXXX.

select cname
      ,coalesce(concat('+91-',substring(contactNo,1,3),'-',substring(contactNo,4,3),'-',substring(contactNo,7)),'N/A') as Contact_Number
  from company
 order by cname asc;


Cannot add an account [non domain account ] to SQL Server 2012 Background:

I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012.

When I try to add to the Logins I get the following error.

===================================

Create failed for Login 'IIS APPPOOL\ABCDEMO'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLoginData.LoginPrototype.ApplyGeneralChanges(Server server)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLogin.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

===================================

Windows NT user or group 'IIS APPPOOL\ABCDEMO' not found. Check the name again. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

------------------------------
Server Name: USALVWCOQA10
Error Number: 15401
Severity: 16
State: 1
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

It's probably because that's not the way that the SQL Server instance is seeing the application.  Attempt to use the application.  This should log an error in the SQL Server's ERRORLOG file when the application isn't able to connect.  This will tell you how the SQL Server is seeing the login.  Just copy the login from the ERRORLOG file and create a login for it.

When using Apppool identity: You have to start the Apppool (a process hosting the application pool) in order for the account to get created.

EDIT: Either set the Identity of the application pool to "Application pool identity" and the use [IIS Apppool\Name] or create a login for network service (NT AUTHORITY\NetworkService) in your sql server, because that is what your process will be running as.

Looks like you are confusing the two things.


Recommended plugin for SQL server 2008 Is there any recommended plugin for SQL server 2008 to use when you are working as database administration, Business Intelligence developer or Database developer?

SentryOne Plan Explorer (free) - analyze execution plans in a way you could never do with Management Studio alone (I also wrote a demo kit here)
SSMS Tools Pack (free) - lots of valuable features here - snippets, connection color coding that works, CRUD generation, and query execution history

I have written SSMSBoost add-in (www.ssmsboost.com).

I develop databases since SQL Server 6.5 and like clear and quick interface of SSMS, but it definitely lacks some productivity features - so that motivated me to improve it.

Here is a list of features:

Script object located at cursor position directly from SQL Editor. Shortcut:[F2]
Locate object: quickly find object located at cursor position in object explorer. Shortcut:[Ctrl-F2]
Track current database in ObjectExplorer permanently
Locate current database in ObjectExplorer
Search for objects using wildcards
QuickConnection Switch combobox on SSMSBoost toolbar allows switching connection in SQLEditor
Preferred connections management: "remember" frequently used server/databases, quickly swith between them
Shortcuts editor - feature, that is missing in SSMS2008, but is coming in SSMS2012
Define macros with existing commands
Auto replacements
Connection coloring - in more useful way
Server aliases in quick connection switch combobox

SSMS2012 support is coming. I will always release "community" edition in some days, that will be functional after trial period, but with some small limitations.


Is UNIQUE(), INDEX() different from UNIQUE INDEX()? Is there any different how mysql treats

UNIQUE (column1, column2),
INDEX (column1)

and

UNIQUE INDEX (column1, column2)

and

UNIQUE (column1, column2)

In the latter, I think mysql will automatically creates index for UNIQUE to avoid duplicate records. Am I right?

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error

INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does allow duplicate (column1, column2) pairs

The second and and third are equivalent.
The first is like the third, but adds a (non-unique) index in addition to the unique index. Which is unnecessary and wasteful.


SQL Server Windows NT 64 bit Sudden High CPU Spike I am facing a strange behavior of my SQL Server 2016 web edition. SQL Server Windows NT 64 bit suddenly jump to use 90% of the CPU and then it goes down after 5 sec, then the spike comes again after 3 to 5 mins (normal usage is just aroud 2% to 5% of cpu)

My server specs

Windows Server 2012 R2 Standard Evaluation (9600 build)
MS SQL Server 2016 Web
Intel Xeon E3-1245v5
64GB DDR4 ECC (2400 MHz)
3x480 GB SSD (Micron)

Server is up to date with latest updates installed and MS SQL Server is on default settings.
How can I find what is causing this. Please help me. Thanks

Please check the screenshots

There are a few ways to approach this.

Download sp_WhoIsActive and run it when you notice a CPU spike, or log it to a table and query the table looking at the CPU column (maybe ORDER BY CPU DESC).
Examine the plan cache using a free script -- I co-author one called sp_BlitzCache. By default, it will return the top 10 CPU consuming queries in your server's plan cache.
Buy a monitoring tool like SentryOne Performance Advisor and use it to examine your server during a spike.

You can also start with wait stats.

use the DMV sys.dm_os_wait_stats and capture the wait stats over a period of time.

If you see very high CXPACKET waits (more than 50% than the rest of the waits) you must read this article and watch the 15 minute video by Brent Ozar.


Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource I'm trying to update a cluster to SQL Server 2008 R2 SP3.  I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts.  The messages & error messages from the SQL Logs when it's back on it's happy node are as follows:

Msg - Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate.cer
ErrMsg - Could not export certificate, trying again with random name. If this is a mkmastr build then this is an error.
Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer
Error: 15240, Severity: 16, State: 1.
Cannot write into file 'N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15240, state 1, severity 16.
This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

There are other error messages related to not being able to recover the master database but I'm not worried about them.
On reviewing the error messages I can see that the path the certificate is trying to be exported to is not the same as the default data path for the instance.
The default path for the instance is N:\SQL\Data\MPData5 which I can see from the server properties and the registry. The SQL Server service account has full read/write access to that path.

If
(the path to the master database or system databases (including certificates) exist and the SQL Server domain account have the appropriate permissions)
Then
(Look into further troubleshooting by introducing the global trace flag 902. This skips script upgrade of the database after a SP or CU patch upgrade. See link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
.This will let you know if your patch on the passive was actually successful. If cluster still fails to come online, then its either a permission or an unusual install. Can you check the setup bootstrap logs on the passive node to confirm that the patching was indeed successful. Check the Detail_%datetime%.txt and the Summary_%datetime%.txt files to confirm this. Let us know what you find to continue troubleshooting.

Try to run Repair on Passive node once that come clean try to failover to passive node, once failover test complete successful then try to apply SP3. Also make sure you have SQL Server service account is LOCAL ADMIN on both NODE.


Did PostgreSQL write amplification reduction WARM efforts get released in 10 / 11? The MariaDB vs PostgreSQL debate continues to rage in my firm :-( and its fuelled by this somewhat famous uber blog post. I found some interesting discussions about write amplification mitigations here. My question is, did anything like this get release as part of PostgreSQL 10 or 11? I would search the documentation, but I'm not sure what it would be called.

No, nothing major along these lines has been released.

Main answer: Per this presentation, Postgres 10's introduction of a logical-replication option (as opposed to the byte-replication we had natively * before) reduces write-amplification on replicas to a degree, though – as of my most recent edit to this post – the fundamental complaint of Postgres tying indexes directly to CTIDs (which requires all indexes associated with a row to be updated when that row has a  change, an op that must also flow to replicas) still stands.

* Logical replication has been available as a simple plugin since 9.4, and in some form or other since long before then.

Aside: Apparently, write-amplification was not the only reason for Uber's switch according to this thread (apparently backed up by some messages which appeared on the mailing list); difficulties with performance were only part of the reason they decided to switch.

A presentation discussing the legitimacy of Uber's complaints against Postgres can be found here. It contends that some of the problems Uber's post highlighted either had solutions or were self-caused.


UPDATE table based on the same table I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same product_id where the language_id is 1.

So far I've tried the following query, but I'm getting errors indicating that MySQL doesn't want to update a table where the table's also being used in the subquery.

UPDATE
  products_description AS pd
SET
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  pd.language_id <> 1

Is there a "simple" way around this limitation in MySQL? Or any "tricks"? I'm a little surprised that my query doesn't work, as it seems logical.

This is rather risky business, and I can understand why. It has to do with the way MySQL processes subqueries. I wrote about it back on Feb 22, 2011 : Problem with MySQL subquery
Performing JOINs involving SELECTs and subquery SELECTs are OK. On the flipside of things, UPDATEs and DELETE can be a rather death-defying adventure.
SUGGESTION
Try refactoring the query so that it is an INNER JOIN of two tables
UPDATE
    products_description pd INNER JOIN products_description pd2 ON
    (pd.products_id=pd2.products_id AND pd2.language_id=1 AND pd.language_id<>1)
SET pd.products_seo = pd2.products_seo;

Give it a Try !!!

update products_description pd1,
 (select products_seo from products_description where language_id = 1) pd2
set pd1.products_seo = p2.products_seo
where pd1.products_id = p2.products_id and pd1.language_id <> 1;


Here is your original query

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture
FROM online i join usertable u on i.uid = u.id
order by i.expiration DESC limit 0,12;

Try refactoring the query to coerce the retrieval of 12 rows before doing the JOIN

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT * FROM online ORDER BY expiration DESC limit 0,12) i
LEFT JOIN usertable u on i.uid = u.id;

The subquery may be forced to use the expiration index now.

You can also try this one:

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT id FROM online ORDER BY expiration DESC limit 0,12) ik
LEFT JOIN online i on ik.uid = u.id;
LEFT JOIN usertable u on ik.uid = u.id;

but you will need redo the expiration index

ALTER TABLE online DROP INDEX expiration;
ALTER TABLE online ADD INDEX expiration (expiration,id);

Give it a Try !!!

explain SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture, count(*) as bogusvar
-> FROM online i join usertable u on i.uid = u.id
-> GROUP BY i.id
-> order by i.expiration DESC limit 0,12;

Adding group by to force the index. Ugly but worked for me.


PostgreSQL: changing password for a user is not working I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
I do

$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'newpasswd';
ALTER ROLE

Then I exit the shell and try to login with new password

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

My PostgreSQL version is

$ psql --version
psql (PostgreSQL) 9.1.5
contains support for command-line editing

What is that I am doing wrong?

Thank you

UPDATE
I made changes in pg_hba.conf and this is how it looks now

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then I restarted the postgres

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server                                                                                [ OK ]

I tried logging in again, but failed

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

Like willglynn said, it's probably your pg_hba.conf file.

If you have the following line:

local   all    all     peer

then change it to:

local   all    all     md5

That should then let you login with your new password (assuming that you correctly supply it) :)

For the folks, who are going crazy after changing password many times and trying everything mentioned here, remember to check user validity expiration. You can set it to 'never expire' like that in psql 9.x:

ALTER ROLE <username> VALID UNTIL 'infinity';


Move SQL Server 2008 database from machine A to B with no downtime I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by there are new data in the db so we cant do it at some random time in the middle of the night.

Is there any way to achieve this? I am pretty much able to do whatever I want so any suggestion will be useful.

Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime.  This is likely the fastest way of moving a database from machine A to machine B.

You can setup mirroring during the day with no downtime, then cutover at the time of your choosing.  As long as the clients are programmed to retry whatever operation they are doing at the time of cutover, there will be no data loss (if you do the client correctly).

Considering the fact that "no downtime is allowed" and "we have only one machine" is a very flawed design, you might just as well prepare the new machine and say "what do you know, the old server just happened to crash, but look, the data is still intact, I can move it to the new server" :)

Sooner or later this is going to happen and it better happen when you want it to, not in the middle of an extended weekend.


Conditionally insert a value in an array when creating a new column I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
  {
    "v": "birthday"
  },
  {
    "v": "reference"
  }
]

What I want (_text):
{birthday, reference}

My query:
select array[col -> 0 ->> 'v', col-> 1 ->> 'v'] from src

gives the expected result as above.
However, when the value is null, I want the value not to be inserted i.e. no NULL value nor empty:
[
  {
    "v": "birthday"
  }
]

should result in
{birthday}

and not
{birthday, [NULL]} -- NULL value
{birthday,} -- Empty string with coalesce

How do you conditionally insert an element into an array when creating one, based on the value of the element?

array_remove() seems to answer my use case (by removing in a 2nd step the null values):
select array_remove(array[col -> 0 ->> 'v', col-> 1 ->> 'v'], null) from src


In Sql Server, is there a way to check if a selected group of rows are locked or not? We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended periods of time (as it is waiting to get locks on all the rows or page lock or table lock) resulting in timeouts or taking multiple days to complete the task.

So, we are changing the approach to delete small batch of rows at at time. But we want to check if the selected (let's say 100 or 1000 or 2000 rows) are currently locked by a different process or not.

If not, then proceed with delete/update.
If they are locked, then move on to the next group of records.
At end, come back to the begining and attempt to update/delete the left out ones.

Is this doable?

Thanks,
ToC

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then return to any batches that were not previously deleted and retry our original delete logic. We must repeat this cycle until all required batches of rows are deleted.

As has been mentioned, it is reasonable to use a READPAST hint and the READ COMMITTED (default) isolation level, in order to skip past ranges that may contain blocked rows. I will go a step further and recommend using the SERIALIZABLE isolation level and nibbling deletes.

SQL Server uses Key-Range locks to protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level...find more here:
https://technet.microsoft.com/en-US/library/ms191272(v=SQL.105).aspx

With nibbling deletes, our goal is to isolate a range of rows and ensure that no changes will occur to those rows while we are deleting them, that is to say, we do not want phantom reads or insertions. The serializable isolation level is meant to solve this problem.

Before I demonstrate my solution, I would like to add that neither am I recommending switching your database's default isolation level to SERIALIZABLE nor am I recommending that my solution is the best. I merely wish to present it and see where we can go from here.

A few house-keeping notes:

The SQL Server version that I am using is Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
My test database is using the FULL recovery model

To begin my experiment, I will set up a test database, a sample table, and I will fill the table with 2,000,000 rows.

USE [master];
GO

SET NOCOUNT ON;

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
    ALTER DATABASE [test] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [test];
END
GO

-- Create the test database
CREATE DATABASE [test];
GO

-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;

-- Create a FULL database backup
-- in order to ensure we are in fact using
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO

USE [test];
GO

-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
      c1 BIGINT IDENTITY (1,1) NOT NULL
    , c2 INT NOT NULL
) ON [PRIMARY];
GO

-- Insert 2,000,000 rows
INSERT INTO dbo.tbl
    SELECT TOP 2000
        number
    FROM
        master..spt_values
    ORDER BY
        number
GO 1000

At this point, we will need one or more indexes upon which the locking mechanisms of the SERIALIZABLE isolation level can act.

-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
    ON dbo.tbl (c1);
GO

-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2
    ON dbo.tbl (c2);
GO

Now, let us check to see that our 2,000,000 rows were created

SELECT
    COUNT(*)
FROM
    tbl;

So, we have our database, table, indexes, and rows. So, let us set up the experiment for nibbling deletes. First, we must decide how best to create a typical nibbling delete mechanism.

DECLARE
      @BatchSize        INT    = 100
    , @LowestValue      BIGINT = 20000
    , @HighestValue     BIGINT = 20010
    , @DeletedRowsCount BIGINT = 0
    , @RowCount         BIGINT = 1;

SET NOCOUNT ON;
GO

WHILE  @DeletedRowsCount &lt  ( @HighestValue - @LowestValue )
BEGIN

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION

        DELETE
        FROM
            dbo.tbl
        WHERE
            c1 IN (
                    SELECT TOP (@BatchSize)
                        c1
                    FROM
                        dbo.tbl
                    WHERE
                        c1 BETWEEN @LowestValue AND @HighestValue
                    ORDER BY
                        c1
                  );

        SET @RowCount = ROWCOUNT_BIG();

    COMMIT TRANSACTION;

    SET @DeletedRowsCount += @RowCount;
    WAITFOR DELAY '000:00:00.025';
    CHECKPOINT;

END;

As you can see, I placed the explicit transaction inside the while loop. If you would like to limit log flushes, then feel free to place it outside the loop. Furthermore, since we are in the FULL recovery model, you may wish to create transaction log backups more often while running your nibbling delete operations, in order to ensure that your transaction log can be prevented from growing outrageously.

So, I have a couple goals with this setup. First, I want my key-range locks; so, I try to keep the batches as small as possible. I also do not want to impact negatively the concurrency on my "gigantic" table; so, I want to take my locks and leave them as fast as I can. So, I recommend that you make your batch sizes small.

Now, I want to provide a very short example of this deletion routine in action. We must open a new window within SSMS and delete one row from our table. I will do this within an implicit transaction using the default READ COMMITTED isolation level.

DELETE FROM
    dbo.tbl
WHERE
    c1 = 20005;

Was this row actually deleted?

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20010;

Yes, it was deleted.

Now, in order to see our locks, let us open a new window within SSMS and add a code snippet or two. I am using Adam Mechanic's sp_whoisactive, which can be found here: sp_whoisactive

SELECT
    DB_NAME(resource_database_id) AS DatabaseName
  , resource_type
  , request_mode
FROM
    sys.dm_tran_locks
WHERE
    DB_NAME(resource_database_id) = 'test'
    AND resource_type = 'KEY'
ORDER BY
    request_mode;

-- Our insert
sp_lock 55;

-- Our deletions
sp_lock 52;

-- Our active sessions
sp_whoisactive;

Now, we are ready to begin. In a new SSMS window, let us begin an explicit transaction that will attempt to re-insert the one row that we deleted. At the same time, we will fire off our nibbling delete operation.

The insert code:

BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.tbl ON;

    INSERT  INTO dbo.tbl
            ( c1 , c2 )
    VALUES
            ( 20005 , 1 );

    SET IDENTITY_INSERT dbo.tbl OFF;

--COMMIT TRANSACTION;

Let us kick off both operations beginning with the insert and followed by our deletes. We can see the key-range locks and exclusive locks.

The insert generated these locks:

The nibbling delete/select is holding these locks:

Our insert is blocking our delete as expected:

Now, let us commit the insert transaction and see what is up.

And as expected, all transactions complete. Now, we must check to see whether the insert was a phantom or whether the delete operation removed it as well.

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20015;

In fact, the insert was deleted; so, no phantom insert was allowed.

So, in conclusion, I think the true intention of this exercise is not to try and track every single row, page, or table-level lock and try to determine whether an element of a batch is locked and would therefore require our delete operation to wait. That may have been the intent of the questioners; however, that task is herculean and basically impractical if not impossible. The real goal is to ensure that no unwanted phenomena arise once we have isolated the range of our batch with locks of our own and then precede to delete the batch. The SERIALIZABLE isolation level achieves this objective. The key is to keep your nibbles small, your transaction log under control, and eliminate unwanted phenomena.

If you want speed, then don't build gigantically deep tables that cannot be partitioned and therefore be unable to use partition switching for the fastest results. The key to speed is partitioning and parallelism; the key to suffering is nibbles and live-locking.

Please let me know what you think.

I created some further examples of the SERIALIZABLE isolation level in action. They should be available at the links below.

Delete Operation

Insert Operation

Equality Operations - Key-Range Locks on Next Key Values

Equality Operations - Singleton Fetch of Existent Data

Equality Operations - Singleton Fetch of Nonexistent Data

Inequality Operations - Key-Range Locks on Range and Next Key Values

You can use NoLOCK while you are deleting and if the rows are locked, they wont be deleted. Its not ideal but may do the trick for you.

DELETE TA FROM dbo.TableA TA WITH (NOLOCK) WHERE Condition = True


Couldn't install SQL Server 2012 on machine with Windows 7 SP1, VS 2010 SP1 I am trying to install SQL Server 2012 RTM on my pc, I have installed Windows 7 SP1, VS 2010 SP1 but it again and again giving this error:

  A network error occurred while attempting to read from the file:
  D:\Microsoft SQL Server 2012
   RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi
  Error help link:
  http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=sqlncli.msi%40PublishProduct%401316

When I go to the Microsoft link I find

  We're sorry
  There is no additional information about this issue in the
  Error and Event Log Messages or Knowledge Base databases at this time.
  You can use the links in the Support area to determine whether any
  additional information might be available elsewhere.

How can resolve this, at first i thought adding current user in Display Replay controller making the problem, then uninstalled everything and again tried installing but couldn't succeed. Can anyone suggest what may be causing these problem and possible resolve for it.
I have also SQL Server 2008 R2 installed which I want to keep simultaneously with 2012

Additional Information's:

Its on local drive i.e on my harddisk, so there should be no error and Its the RTM version of SQL 2012.
I find below logs on C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Feature: SQL Client Connectivity SDK Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Native Client Access Component
Component error code: 1316 Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20121022_164723\sqlncli_Cpu64_1.log Error description: A network error occurred while attempting to read from the file: D:\Microsoft SQL Server 2012 RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi Error help link: go.microsoft.com/…

This problem not yet solved, although other people installed SQL2012 with this installer on their PC's. I think something wrong with my PC or some settings i am using.
Please can anyone help me out?

Here's the troubleshooting options I'd try:

Disable antivirus. Man, I hate saying this, but I've seen issues where antivirus took too long to scan a file, and it caused installation problems.
Re-download the installation media. I know you've seen other users run this same setup and it's worked, but I've seen enough corrupt downloads that I would try this anyway. It doesn't cost you anything.
If D is a network share (a mapped drive), copy the installation files locally before you start.

naturally, two instance of two different version of SQL Server database reside two different machine. if you use development purpose, VM may be a instant solution to gear-up your development.

could you share the event log of the message displaying moment? It may gives some clue!


Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2 I have SQL Server 2008 R2 running on Windows Server 2008 R2 in a virtual environment (ESXI 5.5). Client has been advised to switch the existing E1000 adapter with a VMXNET 3 adapter because they were told it would perform better.

Anyhow, I've done this several times on member servers as well as domain controllers. As I understand it, SQL Server is more concerned with the network layer so I don't believe this should be an issue. I am not a SQL guru so I figured I would ask here to be safe.

Assuming SQL Server is configured to use a dynamic TCP port via SQL Server Configuration Manager, and the SQL Server Browser service is running and not blocked by a firewall, replacing the network adapter should not change connectivity significantly, within the bounds of the network adapter operational characteristics (i.e. its speed, etc).

If, however, you're using a specific TCP port for SQL Server, you'll want to ensure you document that port number before replacing the network adapter.  Once you install the replacement adapter, and remove the existing adapter, set the new adapter to use the same port via SQL Server Configuration Manager.

This is a snip of the configuration screen:

If anyone is curious, I performed this operation without incident.


Changing the Parametization option in SQL Server 2012 from Simple to Forced Does changing the Parametization option in SQL Server 2012 from Simple to Forced clear the cache; or should DBCC FREEPROCCACHE be run after the change?

It frees the plan cache for the database that you change the option for, and nothing else.

On my server, I run these queries:

USE StackOverflow;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 2;

USE StackOverflow2010;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 1;

They I use sp_BlitzCache to look at my plan cache:

After that, change one database to use Forced Parameterization:

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED

Give sp_BlitzCache another run:

Only the plan from StackOverflow2010 remains.

Full disclosure: I contribute a lot of mostly working code to the open source sp_BlitzCache stored procedure.

edit: retested with Erics comment.  Further testing showed what was expected and it does NOT keep the plan.
We can see plan in bucketID 4969 is removed after we change the DBAdmin DB.

The process to verify this remains the same but i'll update the script to include the results of my test run.

Original post:
I just tested it and oddly enough from what I've seen it keeps the plan.  I'm going to review this further but this was my testing so far.  I expected it to flush the results.  I'll do more testing and post but you can follow the logic here.

On non prod server:

USE DBAdmin
GO
SELECT name, is_parameterization_forced FROM sys.databases
/*
name    is_parameterization_forced
master  0
tempdb  0
model   0
msdb    0
ReportServer$xxxx   0
ReportServer$xxxxTempDB 0
dbadmin_Central 0
DBAdmin 0
*/

DBCC FREEPROCCACHE
/* DBCC execution completed. If DBCC printed error messages, contact your system administrator. */
GO

SELECT * --plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
--26904 2   1   57344   0x000001D8BE2C6060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000

select top 10 * from dbadmin.dbo.Databases
GO
--(10 row(s) affected)

SELECT * --plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
4969    2   1   57344   0x000001D8C6D66060  Compiled Plan   Adhoc   0x060008004ABF810AE0C82DBDD801000001000000000000000000000000000000000000000000000000000000
22197   2   1   40960   0x000001D8C414A060  Compiled Plan   Adhoc   0x0600080034126A2B60C12DBDD801000001000000000000000000000000000000000000000000000000000000
25346   2   3   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   3   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8BE2C6060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
*/

ALTER DATABASE [DBAdmin] SET PARAMETERIZATION FORCED
GO

SELECT * --plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8CFF82060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
25346   2   6   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   6   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
*/
select top 10 * from dbadmin.dbo.Databases
GO
SELECT * --plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
/*
34706   2   1   65536   0x000001D8C6D54060  Compiled Plan   Proc    0x0500050035F9F31A80D12FDBD801000001000000000000000000000000000000000000000000000000000000
29213   2   1   40960   0x000001D8C6D7C060  Compiled Plan   Proc    0x0500050029B1F43320CA2FDBD801000001000000000000000000000000000000000000000000000000000000
4969    2   1   57344   0x000001D8C43D6060  Compiled Plan   Adhoc   0x060008004ABF810AE0D72DBDD801000001000000000000000000000000000000000000000000000000000000
32469   2   1   40960   0x000001D8CA498060  Compiled Plan   Adhoc   0x060008008F27113060D02DBDD801000001000000000000000000000000000000000000000000000000000000
30470   2   1   229376  0x000001D8C410A060  Compiled Plan   Adhoc   0x060001004765200BE0C82DBDD801000001000000000000000000000000000000000000000000000000000000
21758   2   1   327680  0x000001D8C4DF0060  Compiled Plan   Prepared    0x06000100962E9C1160C12DBDD801000001000000000000000000000000000000000000000000000000000000
26904   2   1   57344   0x000001D8CFF82060  Compiled Plan   Adhoc   0x06000800AF0A5E2F400A98DBD801000001000000000000000000000000000000000000000000000000000000
25346   2   7   253952  0x000001D8C4D9E060  Compiled Plan   Adhoc   0x06000500AACD810E401998DBD801000001000000000000000000000000000000000000000000000000000000
19925   2   7   147456  0x000001D8D15A8060  Compiled Plan   Adhoc   0x060005001BA31F37C01198DBD801000001000000000000000000000000000000000000000000000000000000
*/

USE DBAdmin
GO
SELECT name, is_parameterization_forced FROM sys.databases
/*
master  0
tempdb  0
model   0
msdb    0
ReportServer$xxxx   0
ReportServer$xxxxempDB  0
dbadmin_Central 0
DBAdmin 1
*/


Removing spaces from postgres SQL I have a sequence of insert statements which I want to run on a Postgres database. It looks something like this -

INSERT INTO test_admin.test_admins (id,
                                    "name",
                                    description,
                                    start_date,
                                    end_date)
        VALUES (
                  3,
                  'admin',
                  NULL,
                  TO_TIMESTAMP ('10/01/2015 00:00:00.00 -0500',
                                'MM/DD/YYYY fmHH24fm:MI:SS.FF Z'),

Due to the extra spaces preceding each line, neither pgadmin 3, nor the postgres console would accept this query and give syntax errors. What is the best way to remove these?

As spaces can be freely used in SQL, maybe you have some non-printable characters in your code. This problem usually relates to using Windows and/or word processors for programming. How (using what program) did you write your code?

Always use an editor designed for programming and always save in 'text only' mode or something similar. If you are in Windows, you can use Notepad (until you get a better editor which is out of scope here), copy-paste your code there and save it.

The REPLACE command. I've used it several times for a specific search function. This should do it: REPLACE('your string', ' ', '') It will remove all spaces.


Why would one need multiple instances of same Oracle database? I came across following statement while reading this short article -

  An instance is the software (and memory) that Oracle uses to
  manipulate the data in the database. In order for the instance to be
  able to manipulate that data, the instance must open the database. A
  database can be opened (or mounted) by more than one instance,
  however, an instance can open at most one database.

I am wondering, why would one create multiple instances of same database?

For availability (one instance goes down, another instance can continue to work), handling load between nodes (adding more nodes on as needed basis) and possibility to dynamically allocate resources based on needs. That is achieved by using Oracle RAC (Real Application Cluster) where instances are running on separate machines.
In Oracle for version up to and including 11.2, one database can be used by one instance (basic setup) or more instances (Oracle RAC).
 It's expected to see paradigm shift for instance vs database with soon-to-be-released Oracle 12c (having more databases inside one instance).

There are many reasons to have multiple database instances.

You can tune instances for different stages of development and for production.

Having separate instances allows you to apply specific security requirements or configuration to an instance and re-configuring may require only a restart of that instance instead of the whole system.

The benefits are obviously only proportional to it's neccesity.


Deleting would-be orphans when creating FK in Postgres I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the FKs so that if a would-be orphan gets inserted in a child table after I copied the parent (while I am running the script), I still get the basic schema and the data, even though the FK creation fails due to the orphan data.

My next step is to do something like wrap a try/catch around the create FK statement and, if it fails, delete the orphan data that caused it. Doing this will be somewhat complicated because I would have to extract all the reference keys and assemble my own delete statement (I currently don't do that because PG system tables provide a ready REFERENCES statement which I can concatenate).

But I am wondering if there is a way to do a ALTER TABLE x ADD CONSTRAINT fk_y FOREIGN KEY ... DELETE ANY WOULD-BE ORPHANS which would save me that step.

So if my parent table is:

 parent_id     | parent_name
---------------------------------
 1             | Joe
 2             | Mary

and the child is:

child_id      | child_name     | parent_parent_id
--------------------------------------------------
 11           | Jimmy          | 1
 21           | Joey           | 2
 31           | Jeff           | 3

'Jeff' would be deleted before enforcing referential integrity because his parent and he were introduced in the source after the source parent was copied to the target. Then the FK can be created against consistent data.

Clarifications (from comments / chat discussion):

  Do you read the data in a single transaction or not?

I don't use a single transaction as it would be too huge. I break the retrieved data by table and for some large tables by table segment (batch of 5000 or 10000 rows).
This is essentially an ETL that makes an "evil twin" out of a DB. hope that makes sense.
On average a DB copy in my environment takes between 20 min (for smalled DBs) to 1.5-2 hrs (for larger DBs) some tables have millions of rows. I load chunk by chunk in the memory of my Python script

  What about pg_dump or pg_basebackup?

My app does some other things that pg_dump doesn't do, such as better logging. Also, pg_dump expects the same users on both the source and target and a host of other issues. I tried it, didn't like it. I like my tool much better, I want to  work through this minor issue that is left and happens only sporadically.

As has been suggested in comments, the standard tool pg_dump might serve you better. It takes a consistent snapshot and preserves referential integrity during backup automatically - given the source DB is consistent (FK constraints in place). Use it if you can!

The related tools pg_dumpall and pg_basebackup have also been mentioned, but those work on whole DB clusters while you obviously only want to copy (parts of) a single database.

However, there may still be valid reasons for the question. It seems unfair to turn it down on speculation. The question itself is interesting enough:

How to import inconsistent data into a FK relationship and fix it?

NOT VALID option

There is no DELETE ANY WOULD-BE ORPHANS option in the creation of FK constraints (like you'd wish), but the related NOT VALID option for FOREIGN KEY constraints in Postgres 9.1 or later should be instrumental. The manual on ALTER TABLE:

ADD table_constraint [ NOT VALID ]

  This form adds a new constraint to a table using the same syntax as
  CREATE TABLE, plus the option NOT VALID, which is currently only
  allowed for foreign key and CHECK constraints. If the constraint is
  marked NOT VALID, the potentially-lengthy initial check to verify that
  all rows in the table satisfy the constraint is skipped. The
  constraint will still be enforced against subsequent inserts or
  updates (that is, they'll fail unless there is a matching row in the
  referenced table, in the case of foreign keys; and they'll fail unless
  the new row matches the specified check constraints). But the database
  will not assume that the constraint holds for all rows in the table,
  until it is validated by using the VALIDATE CONSTRAINT option.

So you can create involved tables and import data and create PK constraints, and FK constraints with NOT VALID.

ALTER TABLE child ADD CONSTRAINT child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) NOT VALID;

That prevents any new writes from violating referential integrity, while tolerating existing violations. (And the system is aware of it.)

Delete orphans later, at the earliest opportunity:

DELETE FROM child c
WHERE  NOT EXISTS (
   SELECT 1 FROM parent p
   WHERE  p.parent_id = c.parent_id
   );

This does nothing if the data is consistent, and the work needs to be done either way to verify integrity. So it won't get any more efficient for unreliable data that needs verification.

Then validate the constraint:

ALTER TABLE child VALIDATE CONSTRAINT child_parent_id_fkey;

Doesn't have to be in the same transaction. Read the manual for details.

Related:

Partial db dump/restore
Disable all constraints and table checks while restoring a dump

I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the FKs so that if a would-be orphan gets inserted in a child table after I copied the parent (while I am running the script), I still get the basic schema and the data, even though the FK creation fails due to the orphan data.

This is not sound design. Integrity first, always. What you're describing leaves your database in a state that requires later clean-up.

What you really want is to set up a schema that is tight, and fool proof and then to  INSERT .. ON CONFLICT DO NOTHING This will skip rows that fail to make the foreign key constraint.

Shy of that, if you already have dirty data, and you just want to delete that you need to

BEGIN a transaction
DELETE the rows that conflict
add the foreign keys CONSTRAINTS
COMMIT the transaction.

There is nothing that does DELETE ANY WOULD-BE ORPHANS, and that would be a bad idea. The above is the closest you can get.


Sql Server Agent notification email  with custom message I have set email notification on the completition of a scheduled job.

Is it possible to customize the email content?

The way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration.

It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for example, other actions to perform in addition to sending an e-mail).

You may also consider a management tool like SQL Sentry which provides lots of flexibility over how to handle successful/failed jobs, and has comprehensive options for subsequent actions.

Using the native tools, no.  You'd need to use the method that Aaron talks about.


How to update Different Date formats to one specific datetime formate I need advise on conversion of date time, below you may see the example data with images for current and expected output

Input Table

Expected Output

You haven't mentioned an RDBMS, so assuming SQL Server, you need to use CONVERT and a style definition to interpret and convert the string into DATETIME:

DECLARE @Table TABLE (ID INT IDENTITY, Country VARCHAR(255), DateString VARCHAR(25))

INSERT INTO @Table
VALUES ('USA', '02/13/2018'),
    ('Germany', '02.12.2018'),
    ('England', '23/02/2011'),
    ('UAE', '08 Jun 2008')

SELECT *,
    CASE
        WHEN [Country] = 'USA' THEN CONVERT(DATETIME, [DateString], 101)
        WHEN [Country] = 'Germany' THEN CONVERT(DATETIME, [DateString], 103)
        WHEN [Country] = 'England' THEN CONVERT(DATETIME, [DateString], 104)
        ELSE TRY_CAST([DateString] AS DATETIME)
    END AS DateValue
FROM @Table

Results

ID  Country     DateString      DateValue
-------------------------------------------------------
1   USA         02/13/2018      2018-02-13 00:00:00.000
2   Germany     02.12.2018      2018-12-02 00:00:00.000
3   England     23/02/2011      2011-02-23 00:00:00.000
4   UAE         08 Jun 2008     2008-06-08 00:00:00.000

Note that any strings that you can't implicitly identify from the Country will be caught by the TRY_CAST and will return NULL if they cannot be accurately converted. These NULLs might be difficult to handle automatically, and this is where data cleansing is important, if you can clean up data at the source and better control the input formats then you will have less of these issues.

CONVERT Styles

select [Id], [Name], [Country], [Date],
  parse([Date] as datetime) as [ActualDate]
  from {your table}

Parse reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/parse-transact-sql

You might need to make use of the culture setting, but I'm not sure it will work for you as your dates appear to be multi-cultural.  Might need some kind of error catching and several passes with different cultures to get them all.  So you'd be looking at creating and then updating a temp table or adding that new column to your original input table.


What's default path for "db2move export" command's output What's default path for "db2move export" command's output in Linux RedHat 7.2 and how to change it to specific folder?

You can specify the path(s) for lob and xml columns with the -l parameter of the db2move utility only.
Other files will be created in the current directory and this can't be changed.

1- Create directory anywhere you want through the Terminal Command
mkdir my_export
2- Open the created directory
cd my_export
3- Run the db2move and export command as per below then you will find all your files in this directory
db2move YOUR_DB_NAME export


How can I use a default value in a Select query in PostgreSQL? I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?

E.g. something like this:

SELECT MAX(post_id) AS max_id DEFAULT 0 FROM my_table WHERE org_id = 3

And if there is no rows with org_id = 3 in the table I want to return 0.

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3

or

SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id
FROM my_table
WHERE org_id = 3;

if you want max(post_id) to be null when there is 1 row but post_id is null

dbfiddle

Just return the default value if no rows are found:

SELECT IFNULL(s_stock, 5)
  FROM stock_info
 WHERE s_product_id = '43';