SQL Server 2014 change license key I need to change the Microsoft SQL Server 2014 license key (just key - not versions). I have found that it is possible via SQL Server Installation Wizard:

http://www.alexandreviot.net/2015/11/10/sql-server-2014-how-to-change-the-product-key/
http://www.sqlcoffee.com/SQLServer2012_0015.htm

Do I need to stop SQL services for that? Or will some downtime be needed?
I think that when upgrade is clicked, SQL services will be restarted probably, right?

I have been going through this exercise for a large amount of servers. I am not going to explain why I am doing this due privacy reason.

I will give you high level steps and let me know if this is not enough.

Get binaries for the version you want to change to.
Start setup.ext
Chose maintenance-->Edition upgrade
Follow standard installation process
If you have more than one instnace on a single host choose the
intance you want to change key.
For me it normally takes about 5 minutes.
Will not ask for a recycle of SQL Server engine in most cases.Microsoft engineers recommend you do a recycle anyway.  You can stage the change before you schedule maintenance window to avoid an extra downtime.


Very fast replication I have used the Streaming Replication guide on the PostgreSQL Wiki to configure replication between my two db servers.

But, when I updated a row on master server it is available on the slave instantly. It should be asynchronous, isn't it? What I am missing?

Servers are in separate places. The master's upload speed is ~75kb/sec. As far as I know, first of all, 16MB xlog file should be upload to slave, after that transactions available on slave. But it is happening in 1 sec. Why is it so fast?

Async and Slow are not the same thing. If you start putting real load on the replication stream, you will start to see delays, but not with a single test command.

From the PostgreSQL Wiki entry you linked to:

  In SR, XLOG records in partially-filled XLOG file are shipped too, implementing record-based log-shipping.'

So streaming replication is exactly that: The stream of updates is transported from master to slave.


Having problems joining table I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:

Emp_Players (eid,pid,team) composite key is (eid,pid)

Plays(pid, team, matchName, scores) composite key is (pid, team, matchName)

Emp(empID, dob, salary) empID is pk

Players(pid, name, team) composite keys are (pid, team)

The query that I wrote is this, however I get an error because I'm using group by operator.
Select name, address, dob, sum(scores)
From Emp e
JOIN Emp_Players ep ON e.empID = ep.eid
JOIN Plays p ON p.pid = ep.pid
Group by p.pid;

Is there an alternate way that can work? Also I'm not sure if I'm doing the JOIN right? I am not able to use the where clause as where name = 'Jack'. It tells me

'no column named Jack'

I would really appreciate any help. Thanks.

When you use the GROUP BY clause, you can only select the fields you group on, or aggregate functions on top of ungrouped fields, like so for example:
Select name, address, dob, sum(scores)
From Emp e
JOIN Emp_Players ep ON e.empID = ep.eid
JOIN Plays p ON p.pid = ep.pid
Group by p.pid, name, address, dob

This will give you the sum of the scores for that player (pid) as you want if they only have one name, address, and dob per pid. But if they have two addresses for example, you'll see two records for that pid, one for each address, with the score summed for just that address. (I assume that's not the case here but just so you're aware. I was unsure since the schema you provided is incomplete and doesn't mention address.)


Can I just restore the .diff file over full backup to speed up restore? I'm doing a server migration, and i'm looking at ways to speed up restore.
I have a 1TB full backup file which takes about 2 hours to restore
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\202010152000.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

This file takes a while to create, and copy to new server, so I also need to include a DIFF which gets the latest data and copy over quickly,
I would include the diff in the restore like so:
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\202010152000.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\diff-final.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

However, this takes about the same time to restore (a little longer)
I was wondering if there was a way to restore the Full backup first, then just apply the diff and it restore just the diff bits, rather than full and diff in one go.
is this even possible? if so I could dramatically reduce downtime.
Database is in simple recovery model so i'm stuck with full and diff backups.
(Sql server 2017 Ent)

EDIT
So based on the first answer I can just do this
alter database [MYDB] set single_user with rollback immediate
go

USE [master]
RESTORE DATABASE [MYDB] FROM  DISK = N'C:\db\Full.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

this takes around 2 hours, then I can do my final diff, and just run this:
USE [master]
RESTORE DATABASE [MYDB] FROM  DISK = N'C:\db\diff2final.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5
GO

alter database [MYDB] set MULTI_USER
GO

Yes, it is possible.
You should restore full backup first WITH NORECOVERY  option, and after that, you can restore only DIFF backup.
Just try without FULL backup;
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\diff-final.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5

RESTORE WITH NORECOVERY Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery
path. To recover the database, you will have to perform a restore
operation by using the RESTORE WITH RECOVERY option (see the preceding
option).

Check out the document.


Replacing Filegroup Used in Last Partition Slice for RANGE LEFT-based Partition Scheme I have a simple date based partition function using RANGE LEFT...

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT
    FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')
GO

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT]
    TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])
GO

In the above code, filegroup [DATA_1995] would retain the values beyond the last partition boundary. So, if I elect to add new filegroups and split the function for additional boundaries in this fashion...

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1995]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1995-12-31')
GO

ALTER PARTITION SCHEME [PS_YEAR_LEFT] NEXT USED [DATA_1996]
GO

ALTER PARTITION FUNCTION [PF_YEAR_LEFT]() SPLIT RANGE (N'1996-12-31')
GO

The filegroup [DATA_1995] remains in the last partition position for values beyond the upper-most boundary.

Can this last filegroup be changed after the partition scheme is initially created or is the only way through creation of a new partition scheme and rebuilding all indexes utilizing the old scheme into the new scheme?

Can this last filegroup be changed after the partition scheme is
  initially created or is the only way through creation of a new
  partition scheme and rebuilding all indexes utilizing the old scheme
  into the new scheme?

The last partition of a RANGE LEFT function is a permanent partition that cannot be removed nor can the first partition of a RANGE RIGHT function. This is one of the considerations detailed in my Table Partitioning Best Practices article and why I recommend a more intuitive RANGE RIGHT function for incremental temporal types along with explicit boundaries for data within the expected range.

You don't need to go so far as to rebuild the existing table and indexes to remediate the misnamed filegroup. If you add new partitions using your script  (taking care to ensure that the SPLIT partitions are empty), you'll end up with this scheme:

CREATE PARTITION SCHEME PS_YEAR_LEFT AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [DATA_1995]
    );

You could create a new scheme with the last partition on a dummy filegroup (or PRIMARY), create a new aligned table, switch all partitions but the last one (which will be empty if you have no 1997+ data) into the new table, drop the old table, rename, etc. Example below.

CREATE PARTITION SCHEME PS_YEAR_LEFT_NEW AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [PRIMARY]
    );
GO
CREATE TABLE dbo.YourTable(
    DateColumn date
) ON PS_YEAR_LEFT(DateColumn);
GO
CREATE TABLE dbo.YourTable_New(
    DateColumn date
) ON PS_YEAR_LEFT_NEW(DateColumn);
GO
DECLARE @Date date = '19921231';
WHILE @Date <= '19961231'
BEGIN
    ALTER TABLE dbo.YourTable
        SWITCH PARTITION $PARTITION.PF_YEAR_LEFT(@Date)
        TO dbo.YourTable_New PARTITION $PARTITION.PF_YEAR_LEFT(@Date);
    SET @Date = DATEADD(year,1,@Date);
END;
GO
--drop original table
--drop original partition scheme
--rename new table, constraints, indexes to original name
--update statistics


mysql restart won't kill child processes on CentOS I have a mysql database running on a CentOS server. I have noticed that, when left to run for several weeks without restarting the service, then when I need to do a restart (/etc/init.d/mysqld restart), the service will display "fail" for both stop and start. It will then refuse to start again, unless I kill -9 the mysqld_safe and mysqld processes.

Why does this happen and what can I do to prevent it?

Using mysql 5.1.66 on Centos 6.3

The script /etc/init.d/mysqld is dependent on the presence of the file mysql.sock. If that file is missing, run service mysqld restart or service mysqld stop simply does not work.
Over the years, I have seen many incidents where mysql.sock just disappears (going back to MySQL 4.x). See ServerFault Post : What should mysqld.sock contain, why don't I have it?. When this happens, you will have to resort to mysqladmin shutdown.
If you tried this:
mysqladmin -h127.0.0.1 -uroot -p shutdown

this may fail if it authenticates as root@localhost
Instead, shutdown mysql using the TCP/IP protocol
mysqladmin -h127.0.0.1 --protocol=tcp -uroot -p shutdown

This bypasses checking for the socket file mysql.sock.
Give it a Try !!!
CAVEAT
I have discussed using mysqladmin shutdown before

Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)
May 08, 2012 : How to properly stop MySQL server on Mac OS X?
Feb 06, 2012 : MySQL Database and Table Loss


ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a) Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing.

The tables are in a production MySQL 5.6.19a database running on Amazon RDS.

How can we do an ALTER like this without disrupting the production reads and inserts that are happening all the time?

ALTER TABLE MYTABLE CHANGE id id BIGINT NOT NULL AUTO_INCREMENT;

Here is DDL for the table:

CREATE TABLE `MYTABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteId` int(11) NOT NULL,
  `filter` varchar(10) NOT NULL DEFAULT 'ALL',
  `date` varchar(10) NOT NULL,
  `cards` varchar(250) NOT NULL,
  `apples` varchar(45) NOT NULL,
  `carrots` varchar(45) NOT NULL,
  `corn` varchar(45) NOT NULL,
  `peas` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`siteId`,`filter`,`date`,`cards`),
  KEY `date_k` (`date`),
  KEY `cards_k` (`cards`),
  KEY `apples_k` (`apples`),
  KEY `siteId_k` (`siteId`)
) ENGINE=InnoDB AUTO_INCREMENT=1748961482 DEFAULT CHARSET=utf8

If you have enough space, you can create a copy of the actual table and do the work on that:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

Then you can change the column as desired:

ALTER TABLE tbl_name MODIFY COLUMN col_name BIGINT AUTO_INCREMENT;

Once the process is done, you can rename the tables:

RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2;

Then drop the original table, and you should have the spected result.


How to achieve many to many relationship in this database design? I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist.

What I have tried so far is:

 user (user_id(pk), user_name)

 trip(trip_id(pk), trip_name, user_id(fk))

 wishlist(trip_id(fk), user_id(fk))

But, since  multiple users can add multiple trips into their wishlist, How to associate these relations?

And if user retrieves his personal wishlist, the associated trips in the wishlist for 'that' particular user can be shown?

Your design looks OK to me. The following query will give the trips from a particular users wish list:

SELECT user_name, trip_name

FROM user u
JOIN wishlist w ON u.user_id = w.user_id
JOIN trip t ON w.trip_id = t.trip_id


Postgres Autovacuum Progress Monitoring I have an autovacuum (to prevent wraparound) happening on one of my tables for almost 4 days. I'm wondering if there is any way for me to tell if it's not keeping up with the amount of updates/deletes that are happening.

For reference, I am using Postgres 9.6 on Google CloudSQL. They have a known issue where pg_stat_progress_vacuum returns null for everything.

I've also been checking pg_stat_all_tables and have noticed that n_dead_tup for my table is going up instead of down, but I'm not sure if this number would be aware of the autovacuuming that is going on.

Bad luck. If you have a hosted database, and the monitoring view is broken, there's nothing you can do.

To speed up autovacuum as much as possible, increase maintenance_work_mem and lower autovacuum_vacuum_cost_delay. But that won't have an effect on an already running autovacuum worker.


Expand table rows based on column value I have a table with two columns; EVENT_DATE date and RANG number
the first column holds a date for an event while the second column is for the period of that event. here is a sample of data

| EVENT_DATE | RANG |
|------------|------|
| 03/01/2015 |    1 |
| 09/04/2015 |    3 |
| 15/10/2015 |    2 |

is there any way to expand the EVENT_DATE by increment it based on the RANG value, so the output will be like,

| EVENT_DATE |
|------------|
| 03/01/2015 |
| 04/01/2015 |

| 09/04/2015 |
| 10/04/2015 |
| 11/04/2015 |
| 12/04/2015 |

| 15/10/2015 |
| 16/10/2015 |
| 17/10/2015 |

You could use a CTE to get a table with a sequence of numbers up to the highest rank in your table, and then join with that:

with nums as (
  select
    level-1 lvl
  from dual
  connect by
    level-1 < = (select max(rang) from foo)
)
select
  foo.event_date + nums.lvl
, rang
from foo
join nums
  on nums.lvl <= foo.rang
order by
  foo.event_date + nums.lvl;


Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following:

Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 15:11:21)
Retrieving the total amount of queries within each hour interval for given timestamps. (For example in here, every day from July 13 to 31, total amount queries between hours 10 and 15 are retrieved in hourly intervals (10:21:09 to 11:00, 11:00 to 11:59, 12:00 to 12:59...,15:00 to 15:11:21)

The query is this:

;WITH cal AS
    (
        SELECT generate_series('2-2-2011 00:00:00'::timestamp,
            '1-4-2012 05:00:00'::timestamp,
            '1 hour'::interval) AS stamp
    )
, qqq AS (
        SELECT date_trunc('hour', calltime) AS stamp
            , count(*) AS zcount
        FROM mytable
    WHERE calltime >= '13-7-2011 10:21:09'
        AND calltime <= '31-7-2011 15:11:21'
        AND calltime::time >= '10:00:00'
        AND calltime::time <= '15:59:59'
        AND date_part('hour', calltime) >= 8
        AND date_part('hour', calltime) <= 15
    GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp
    , COALESCE (qqq.zcount, 0) AS zcount
FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '13-7-2011 10:00:00'
    AND cal.stamp <= '31-7-2011 15:11:21'
    AND date_part('hour', cal.stamp) >= 10
    AND date_part('hour', cal.stamp) <= 15
ORDER BY stamp ASC;

And a snippet of the result is like this: (zcount values vary, this is just a by-product of copy-and-paste during editing the question)

stamp                 zcount
"2011-07-13 10:00:00"  123
"2011-07-13 11:00:00"  338
"2011-07-13 12:00:00"  184
"2011-07-13 13:00:00"  298
"2011-07-13 14:00:00"  162
"2011-07-13 15:00:00"  293
"2011-07-14 10:00:00"  216
"2011-07-14 11:00:00"  392
"2011-07-14 12:00:00"  268
"2011-07-14 13:00:00"  483
"2011-07-14 14:00:00"  327
"2011-07-14 15:00:00"  419
.
.
.
"2011-07-31 10:00:00"  216
"2011-07-31 11:00:00"  392
"2011-07-31 12:00:00"  268
"2011-07-31 13:00:00"  483
"2011-07-31 14:00:00"  327
"2011-07-31 15:00:00"  419

My query works well for inputs in which the hour of the first timestamp is smaller than second one -e.g. in here, 10 AM for first timestamp's hour and 3 PM for second timestamp's hour.

However, when I want to retrieve total counts of query per every hour interval for inputs such as 07-13-2011 22:11:43 and 07-31-2011 04:06:04, - so that I get query counts between 10:11:43 pm to 10:59pm, 11pm to 11:59pm,...,3:00am to 3:59, 4:00am to 4:06:04am for every single day,  I'm having problems.

I need to retrieve the total counts of queries like the following for each given day in input:

    stamp                 zcount
    "2011-07-13 22:00:00"  123
    "2011-07-13 23:00:00"  338
    "2011-07-14 00:00:00"  184
    "2011-07-13 01:00:00"  298
    "2011-07-13 02:00:00"  162
    "2011-07-13 03:00:00"  293
    "2011-07-14 04:00:00"  216
    "2011-07-14 22:00:00"  392
    "2011-07-14 23:00:00"  268
    "2011-07-15 00:00:00"  483
    "2011-07-15 01:00:00"  327
    "2011-07-15 02:00:00"  419
    "2011-07-15 03:00:00"  121
    "2011-07-15 04:00:00"  231
     .
     .
    "2011-07-30 22:00:00"  392
    "2011-07-30 23:00:00"  268
    "2011-07-31 00:00:00"  483
    "2011-07-31 01:00:00"  327
    "2011-07-31 02:00:00"  419
    "2011-07-31 03:00:00"  544
    "2011-07-31 04:00:00"  231

What do I need to change in my query in order to make sure it works for both timestamp cases as I presented above?

I currently use a workaround for the second case but I would like to handle all of it in a single query.

As Erwin Brandstetter pointed out, cross post from stackoverflow.com/q/12379598/939860. Solved.


SQL: Custom Sort Order Suppose I have a column with colour names (colour). Naturally, it will be a character field.

I would like to sort the table by colour, but not in alphabetical order, but by rainbow or transistor colour code order.

What is the simplest way to sort a column using an arbitrary sort order? Something like this:

id  name        colour
1   this        red
4   that        red
2   stuff       orange
5   nonsense    orange
3   whatever    yellow
6   etc         yellow

where the colours in in rainbow order.

The use of colours is just an example. There are many sets of textual data where the order is not alphabetical.

Use a case statement to establish the order, or add a lookup table with all colours where you can set an order.

declare @t table(id int, name varchar(20), colour varchar(10));
insert into @t values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

select *
from   @t
order by (case when colour = 'yellow' then  1
               when colour = 'orange' then 0
               else 2
           end);
GO

id | name     | colour
-: | :------- | :-----
 2 | stuff    | orange
 5 | nonsense | orange
 3 | whatever | yellow
 6 | etc      | yellow
 1 | this     | red
 4 | that     | red

dbfiddle here

create table foo(id int, name varchar(20), colour varchar(10));
insert into foo values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

create table bar(colour varchar(10), [order] int);
insert into bar values
('orange', 0),
('yellow', 1),
('red', 2);

select *
from   foo
inner join bar
on foo.colour = bar.colour
order by bar.[order];

GO

id | name     | colour | colour | order
-: | :------- | :----- | :----- | ----:
 2 | stuff    | orange | orange |     0
 5 | nonsense | orange | orange |     0
 3 | whatever | yellow | yellow |     1
 6 | etc      | yellow | yellow |     1
 1 | this     | red    | red    |     2
 4 | that     | red    | red    |     2

dbfiddle here


Constant Scan joining In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me.

Setup is simple

CREATE TABLE #data ([Id] int);
INSERT INTO #data VALUES (101), (103);

Then there is a query

DECLARE @id1 int = 101, @id2 int = 102;

SELECT *
FROM (VALUES (@id1), (@id2)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

There is nothing special about it. It works and produces its result, if you run it. Here is its execution plan

Removing a row from VALUES however

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

causes optimizer to fail

  Msg 8622, Level 16, State 1, Line 1
  Query processor could not produce a query plan ...

Why? Is there a way (besides putting parameter into temporary table) to make it work using hash algorithm?

Note: This is of no real appliance and serve purpose of studying optimizer behavior and capabilities.

The example above was tested on

  Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)

Why?

Saying it in short. Because of HASH hint shots to optimizer's leg and optimizer itself shots to the other. Being shot to both optimizer can't cross the finish line.

To better illustrate what is going on, let's rewrite problematic query to join two VALUES and to use merge algorithm instead

DECLARE @id1 int = 101, @id3 int = 103;

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL MERGE JOIN (VALUES (@id1), (@id3)) d([Id]) ON d.[Id] = p.[Id];

Execution plan of this query is simple. There is the Merge Join operator with two Constant Scan inputs.

These two Constant Scans are different to optimizer though.

The one representing single-row input has column name prefixed with Expr, whereas the other representing multiple-rows input has column name prefixed with Union. Data from multiple-rows Constant Scan is accessed in the Merge Join predicates a kind of "by reference" ([Union1001]), whereas single-row Constant Scan data is accessed a kind of "by value" (see that @id1 is substituted instead of [Expr1000]).

This "by reference"→"by value" substitution is the result of scalar mapping performed at early optimization stages.

One can see (using trace flag 8606) that in the Input Tree join predicate is [Union1001] = [Expr1000]

*** Input Tree: ***
    ...
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: Expr1000
    ...

but then in the Simplified Tree it becomes [Union1001] = @id1

*** Simplified Tree: ***
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: @id1

Scalar mapping is the part of projection pulling logic and actually performed before simplification stage is entered.

One may did noticed earlier, that Merge Join node has only residual predicate and no join equality predicate. This is because of join equality predicate has been eliminated by scalar mapping. The [Union1001] = @id1 is equality predicate, but it can not serve as a join equality predicate. To be such it has to reference columns from both inputs, but @id1 is variable and not a column.

So, being equijoin ON d.[Id] = p.[Id] originally, the query transformed to non-equijoin (which is special case, and because of that, by the way, optimizer did not introduced sorting below Merge Join for the non-sorted Constant Scan inputs). Fortunately, in case of merge algorithm optimizer has such non-equijoin alternative.

In case of using hash algorithm non-equijoin alternative does not exists, and so, join equality predicate elimination causes optimizer to fail later on.

  Is there a way (besides putting parameter into temporary table) to
  make it work using hash algorithm?

There is no trace flag(*) that prevents scalar mapping, neither query lever nor session level nor start-up. And there is no optimization rule that can be turned off to prevent it, because of it is not performed by a rule.

I was only able to execute problematic query by setting breakpoint in the COptExpr::PexprMapScalar routine

and modifying value of the eax register after call to ScaOp_Identifier::ClassNo to make SQL Server think that second operand of ScaOp_Comp is not identifier.

Here is the Simplified Tree of the problematic query posted in the question

*** Simplified Tree: ***
    LogOp_FullOuterJoin
        LogOp_ConstTableGet (1) COL: Expr1000
            ScaOp_Identifier COL: @id1
        LogOp_Get TBL: #data(alias TBL: d)
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier QCOL: [d].Id
            ScaOp_Identifier COL: Expr1000
*******************

and here is its plan obtained.

It has little sense actually, because of obtained plan cost is 0.0210675 units, whereas running query without HASH hint leads to execution plan with Merge Join (notice no sorting below Merge Join again)

costing 0.0088948 units.

(*) There may exist a combination of trace flags. I think it doesn't though, but I didn't explored all code paths, so I don't know it for certainty.


Restore exceeds licensed limit of 10240 MB per database. All I really need is the DB structure I received a .bak file from a customer that I need to Restore, but the Restore exceeds licensed limit of SQL Server 2008 R2 10240 MB per database.  All I really need is the DB structure. Is there a way to Restore anyway or for me to just Restore the structure?

The only way would be to restore to an edition of SQL Server besides the express edition, then delete the data that you don't need.  Then shrink the database below the 10 Gig limit then backup and restore to your express instance.

If this is for a development machine look at getting a copy of SQL Server Developer Edition. It's usually about $50.00 and doesn't have any limitations like database size.  It's only limitation is that you can't use the instance for production use.


Mongodb how to cancel a --fork without mongod service 'option' I am currently having issues with sudo service mongod start/stop/etc so instead I have been running mongo with sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all  --logpath /var/log/mongodb/mongod.log --auth --fork but I received errors if I wanted to change any of those --options (such as --bind_ip_all to something more specific) to something else and I am assuming it is because the --fork has never been cancelled.

To summarize, how do you cancel mongod through the cli so that I can update the call?

You just kill the process. killall mongod

To check that you don't have any mongod process running anymore, you can use ps -ef|grep mongod|grep -v grep  or just pgrep mongod. With that latter, if you get pid number (as answer), mongod is still running


does SQL automatic backup runs even the machine/server is offline I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?

No SQL Jobs cannot run without the SQL Server & the SQL Agent Service running


Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable.

Right now we are running this query daily, at midnight, using a cron job:

DELETE FROM table WHERE endTime < '1393632001'

The last time the query ran it examined 4,602,400, took over 3 minutes and CPU went through the roof.

What can we do to keep the CPU, synchronous db connections, disk cue depth etc from spiking unreasonably while still clearing old data?

PS: You will notice that the query is actually happening at a fairly inopportune time in our usage cycle. Assume that we have already shifted the query's timing to occur at the lowest point of usage each day. Also, there is no index on "endTime" and I would prefer to keep it that way if possible because there is a ton of data being inserted very regularly, and not much lookup.

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

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

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


Select every n-th row, don't pull the entire table In light of the Meta discussion on allowing basic SQL questions on dba.SE I present an issue that I'm having now, for which the answer on Stack Overflow is inadequate and naive. I hope that there is a better solution to the issue than those presented on SO (as I am currently facing this issue in an application), and dba.SE seems to be the perfect place to find a better answer.

Here is the original question on Stack Overflow: How do you select every n-th row from mysql?

Here is the accepted answer:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, [column name]
    FROM (
        SELECT @row :=0) r, [table name]
    ) ranked
WHERE rownum % [n] = 1

The critical problem with the accepted answer is that it requires pulling the entire table into a temporary table. Thus, I've addressed that concern in the title of this question.

Consider also that the table might have deleted rows, thus an alternative query which were to simply test WHERE MOD on the primary key is not a good solution either. Id est, the primary key cannot be trusted to be sequential.

Is there a better way to phrase a query which would return every second, tenth, or arbitrary n-th row, which does not require pulling the entire table into memory yet also considers deleted rows?

Every n-th row can be defined as such:

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

My target DB is MySQL 5.5 running on a common Debian-derived Linux distro.

EDIT: In response to Thomas' answer:

The suggested solution does not produce the expected result, see below:

mysql> SELECT
    ->     @i:=@i+1 AS iterator
    ->     , t.name
    -> FROM
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)

For test data in events

id  txtcol
--  ------
 1  event0
 2  event1
 4  event2
 5  event3
 6  event4
 8  event5
 9  event6

Retrieve the primary key values in ascending order

SELECT id FROM events ORDER BY id

Wrap that in a query to assign a zero-based rank

set @row:=-1;
SELECT @row:=@row+1 AS rownum, id
FROM
    (
        SELECT id FROM events ORDER BY id
    ) AS sorted

Wrap that in a query to select the first row and every third row thereafter

set @row:=-1;
SELECT id
FROM
    (
        SELECT @row:=@row+1 AS rownum, id
        FROM
            (
                SELECT id FROM events ORDER BY id
            ) AS sorted
    ) as ranked
WHERE rownum % 3 = 0

Finally, wrap that in a query to retrieve the other columns

set @row:=-1;
SELECT events.*
FROM
    events
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id
                FROM
                    (
                        SELECT id FROM events ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 3 = 0
    ) AS subset
        ON subset.id = events.id

returning

id  txtcol
--  ------
 1  event0
 5  event3
 9  event6


SharePoint 2013 Database encryption I'm using SharePoint 2013 with SQL Server 2012 as persistence layer. I need to encrypt the data stored in the database. I stumbled upon the SQL Server feature Transparent Data Encryption (TDE).

What will happen to the files (Office Documents) from the document libraries, when TDE is used? Are the files encrypted also or will only tables be encrypted?

The files are stored in a blob in the sharepoint database and as TDE encrypts all the pages in the database all the files will be encrypted there within.

It's important to notice that while the database is mounted on the server the database server will serve the files unencrypted to the Sharepoint application server and it's clients.

The Sharepoint binary cache will store them unencrypted as well as all the clients. You are only encrypting the data at rest on the SQL Server when using TDE.

You can add to the security by using encrypted connections to the database server and HTTPS to connect to the Sharepoint application but after the files leave the database storage they will be unencrypted.


SQL Server EXECUTE AS trouble I'm missing something while trying to make my stored procedure use EXECUTE AS. The stored procedure is reading data from source_db, aggregates it and stores result in target_db.

The sp itself is in target_db. I have a dedicated login and map it to users in both source_db and target_db for sp's owner (so there is a user app_agent in source_db and in target_db for login app_agent).

If I log in as app_agent, and execute

EXEC target_db.app_agent_schema.import_data

everything works fine. But if I change

ALTER PROCEDURE app_agent_schema.import_data WITH EXECUTE AS OWNER` (or `AS SELF`)

and try executing it, it throws

  The server principal "app_agent" is not able to access the database
  "source_db" under the current security context.

I'm using SQL Server 2008.

Could someone point out my error?

Thanks

Update
After doing some research, I found that ALTER DATABASE target_db SET TRUSTWORTHY ON solves the problem, but that doesn't seem as the right solution to me...

This is explained in Extending Database Impersonation by Using EXECUTE AS. The EXECUTE AS context is trusted only in the current database and allowing it to spill over to other databases is a escalation of privilege attack vector.

There are two solutions, both described in the article linked above:

the easy one is to mark the database TRUSTWORTHY: ALTER DATABASE [source_db] SET TRUSTWORTHY ON;. Although easy, is also dangerous in as it makes the dbo of source_db a de-facto sysadmin.
the safe one is use code signing, see Call a procedure in another database for an example. This is more complex, but is 100% buletproff security.


Soft delete: Copy row data to archive table when deleting How can I move data from my current table to an archive table when a delete is called on the table?

My current table and archive table needs to be in sync in their schema too.

Can someone help me?

Edit:
I am using MySql

(This answer is assuming SQL Server)

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *
    from deleted
go

This would result in an audit table of a copy of the deleted records.  I would recommend putting a datetime stamp on that (and maybe more fields for typical auditing).  You could do that like this:

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

alter table YourTableDeleteAudit
add DeleteDate datetime null
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *, getdate()
    from deleted
go


pt-heartbeat showing "Host is blocked because of many connection errors"...but connections are well under limit I'm running MySQL in a master-slave configuration. pt-heartbeat is showing "Host is blocked because of many connection errors"...but connections are well under limit (limit is 600 connections, MySQL workbench shows I'm hovering around 250, and I'm having no other problems connecting).

Here is my CLI, running from the master:

PTDEBUG=1 pt-heartbeat --host="mysql-slave" --user=root --ask-pass --database="percona" --monitor --master-server-id="14"

Any ideas? Should I run the suggested 'mysqladmin flush-hosts' as PTDEBUG suggests? I just don't feel that's the problem if I'm below the limit and not seeing this anywhere else.

Thx

If 'host_name' is blocked because of many connection errors.
then to Unblock only options mysqladmin flush-hosts

unblock documentation link


PostgreSQL Division In Query Not Working So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this:

(tier1 / total) as per

But no luck. I have CAST these as int and still nothing. I will either get an error and the query won't execute or I will get a value of 0.

Here is what I am trying to work with:

SELECT count(student_id) as total
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
FROM national_assessments.aimsweb
WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL

I had this idea as well but no luck:

SELECT tier1,total, tier1/total as test
FROM (
    SELECT count(student_id) as total
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
    FROM national_assessments.aimsweb
    WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL
) as test

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0.

To get "percentage", first multiply by 100.
To also get fractional digits, cast to numeric (before you divide) - or multiply by 100.0. The presence of a fractional digit in the numeric literal coerces the result to numeric automatically.

Typically, you would round to two fractional digits or something. Use round() for that (only works for numeric).

Adding some other basic optimizations to the query you get:

SELECT total
      ,tier1, round((tier1 * 100.0) / total, 2) AS tier1_pct
      ,tier2, round((tier2 * 100.0) / total, 2) AS tier2_pct
      ,tier3, round((tier3 * 100.0) / total, 2) AS tier3_pct
FROM  (
   SELECT count(*) AS total
         ,count(fall_september_tier = 1 OR NULL) AS tier1
         ,count(fall_september_tier = 2 OR NULL) AS tier2
         ,count(fall_september_tier = 3 OR NULL) AS tier3
   FROM   national_assessments.aimsweb
   WHERE  general_outcome_measure = 'PSF'
   AND    fall_september_tier IS NOT NULL
   ) sub;

Explanation for the counting technique:

For absolute performance, is SUM faster or COUNT?


Should I expect better performance out of MySQL? I'm new to MySQL and don't know what performance to expect. Does the performance I'm seeing below seem outrageous, or about right?

I have a table with 4M rows, indexed on userId, and I want to count all of the rows with a certain userId, so I run:

SELECT count(userId) FROM Test_schema.Events where userId = 7205

It takes 0.95 seconds to run this query!

To make sure it's using an index I tried explain:

> explain SELECT count(userId) FROM Test_schema.Events where userId = 7205
# id, select_type, table,  type,  possible_keys,     key,               key_len, ref, rows,    Extra
   1, SIMPLE,      Events, index, idx_Events_userId, idx_Events_userId, 138,        , 3972452, Using where; Using index

I believe this shows that the query will indeed use my index on userId. My server has 13Gb of memory, with 8Gb free.

Should it take MySQL a full second to count 54 rows out of 4 million? Is this about the performance I can expect from MySQL, or should I be hiring an expert to get this down? My target would be well under 100ms, which is about what I can expect from distributed data stores for a similar problem.

--- update ---

I used MySQL Workbench to profile the query, and saw:

"Sending data" is a single huge bottleneck. Is this about a connection between my client & server more than query perf time?

You need an index beginning with UserId.  It sounds like you have INDEX(Events, UserId).  That was somewhat helpful, but it still had to scan the entire index.

More on building optimal indexes.


Why is SQL Server 2012 Recommending This Index Be Created? I have the following query:

Select SubId, Max(ReadTimeLocal)
From dbo.PanelWorkflow
Where ProcessNumber IN (802,1190,1605,1620,1645,1660,1695,1790,1990,2690,2795,2990,3090,3290,3590,3790,4190,4390,4590,5000,5200,5400)
Group By SubId

I have the following index on the dbo.PanelWorkflow table:

CREATE NONCLUSTERED INDEX [IX_PanelWorkflow_ProcessNumber_Lineage] ON [dbo].[PanelWorkflow]
(
    [ProcessNumber] ASC,
    [Lineage] ASC
)
INCLUDE ( [SubId],[ReadTimeLocal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The query plan from SSMS looks as follows:

SSMS is recommending  I create the following index (with an impact of 54.3)

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[PanelWorkflow] ([ProcessNumber])
INCLUDE ([SubId],[ReadTimeLocal])

Does anyone know why it's recommending I create an index that essentially exists already, and is not using the one that does exist?

There are 830 million rows in the table. Using SQL Server 2012.

Just to clarify, SSMS is not recommending that index, it is from the query engine/optimizer. Just to note as well a good MSDN article notes some limitations to the Missing Index Feature.

The fact that your current non-clustered index contains [ProcessNumber] and [Lineage] the query engine found simply scanning the clustered index of the table to be more optimal. It is suggesting an index strictly on ProcessNumber because the query is filtering on that column only. I believe if this is also the column that the clustered index is based on then your non-clustered indexe will be ignored by the query optimizer.

I would look into all the missing indexes the optimizer sees for this table and evaluate the current unused indexes as well.


ADO connection suddenly starts failing Intermittently I have an Access 2010 front end connecting to a SQL Server 2014 back end. I've got SSMS 2014 installed on my machine, and everything was working fine. (Running Win7 64-bit with 64-bit Office.)

Long story short, in trying to trouble shoot an end user problem, I attempted to install AccessDatabaseEngine_x64.exe from Microsoft. (I ended up getting it from downloads.com because the MS link wouldn't download it - I should have taken that as a clue.)

Installing that didn't help the issue, so I uninstalled and rebooted my machine. Now, when my Access application is attempting to connect to the SQL Server via this code:

Dim ConfigRS As New ADODB.Recordset
Dim SQLString As String
SQLString = "SELECT <fields> " & _
            "FROM <table> " & _
           "WHERE <condition>"
ConfigRS.Open Source:=SQLString, ActiveConnection:=CurrentProject.Connection

I usually get this error (server name, starts with 'c', blacked out), though sometimes it works just fine:

I can browse the linked tables from within Access, I can select from the tables in SSMS with no problem, I just cannot open an ADODB query via code.

The only thing that's changed today is the installation and uninstallation of AccessDatabaseEngine.

Does anyone know of specific files I need remove to finish removing all traces of AccessDatabaseEngine?
Would it be easier to reinstall SSMS to ensure all the proper drivers are in place?

Since Access can successfully connect to the SQL Server and execute queries against it, the connection string the Access is using must be correct.  The connection made through ADO, on the other hand is relying on CurrentProject.Connection property, which we don't know what it is, and fails unexpectedly.  Inside AccessDataBaseEngine_X64.exe seems to have no ODBC drivers, and Access works with or without AccessDataBaseEngine_X64.exe installed.  So the DSN the Access is using didn't change, therefore the problem must be the connection string used in ADO.

So if you use the same connection string as that of Access, the problem should go away.

As for why it started behaving erratically all of sudden I can't fully comment on that.  But what Microsoft states about the CurrentProject.Connection property might give you a clue (the relevant section in bold).

  Use the Connection property to refer to the Connection object of the current Microsoft Access project (.adp) or Access database object. You can use the Connection property to call methods on the Connection object such as BeginTrans and CommitTrans.

  Note:
  The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database. Since the Connection property is the main Shape provider connection, the following information is necessary when using this property.

  MSDataShape uses Recordset.CursorLocation = adUseClient. Do not set   CursorLocation prior to assigning a recordset to CurrentProject.Connect.
  MSDataShape uses Recordset.CursorType = adOpenStatic. Do not set CursorType prior to assinging a recordset to CurrentProject.Connection.
  MSDataShape accepts Recordset.LockType = adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly (default). If set to adLockPessimistic, it is changed to adLockOptimistic.
  The shape connection does not support the all ADOX operation, specifically the Columns.Properties collection is not supported.
  In order to ensure that a shape connection will work correctly, the Command.CommandType must be set to adCmdTable.

Bonus:

How to know what files were involved with AccessDatabaseEngine_x64.exe? (I was curious!)

Use Orca


Copy all data to slave before mySQL replication connect I try to set up mySQL replication, however I have question before I connect 2 servers

Q1. Should I import all data from master into slave before I connect 2 servers?

ex. if My db have millions of rows, should I copy all data to slave first?

Q2. I have read few post about when disaster strikes on master, it switch to slave, so you have time to fix master. What kind of disaster could happen? I have set up mySQL only SELECT, ADD and UPDATE, just wondering what could happen?

Q1. Yes, not only "should" you copy the data from the master, you have to.

Replication in MySQL relies on the two servers beginning with an identical set of data, after which, every query that modifies data on the master makes the same changes to the data on the slave.

There are two ways to do this, either by stopping the master completely (shutting down mysqld) and copying the data files... or using mysqldump, as explained in the documentation:

http://dev.mysql.com/doc/refman/5.6/en/replication-howto-existingdata.html

Q2.

  when disaster strikes on master, it switch to slave

Well, not exactly.  "It" (the master) or "it" (replication) doesn't do anything when there's a failure on the master.  You, or your application, must choose if and when to abandon a failed master and start writing to the slave, instead of writing to the master.  At this point, the slave becomes master and is the only server in the system, because the data on the old master is now stale and obsolete, and must be resynchronized.

  What kind of disaster could happen?

The fact that you ask this question suggests that you are not thinking of the kinds of disasters that replication actually protects you from: hard drive failure, memory corruption, cpu failure, cooling fan failure, power supply failure ... as well as utility power outage, fire, flood, earthquake (assuming the slave server is in a different data center) ... and a pretty-much endless list of the things that can go wrong in or around a server.

Your question suggests that you were thinking along the lines of things that might happen to your data in properly-functioning server... such accidental or malicious altering or deleting or rows, or dropping of tables or databases.  Replication doesn't protect against these things, because they'll replicate perfectly.

There was, however, a new feature introduced in MySQL 5.6, "Time Delayed Replication," which allows the slave to lag a fixed period of time behind the master, to give you time to recover from such a change to your data.  The slave still "discovers" the changes to the master in near real time, but does not actually apply the changes to its data until the configured amount of time has passed from the time they were originally logged on the master.

http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html


performance: {ts 'datetime'} vs DATEADD() I received a claim for an incredible slow query in our print label software. With SQL Profiler I got the offender query and started to test in SSMS with really bad performance (around 00:50:00).
The query is long, and the tables are big, but there is a

WHERE
 ...
 fieldX > {ts '2016-01-01 00:00:00} /* fieldX is a DATETIME */
 ...

I changed to

WHERE
 ...
 fieldX > DATEADD(YYYY, -1, GETDATE())
 ...

And the execution time now shows 00:00:00 !!

I just want understand why?

I recommended (originally, in the comments) that HEDMON try some different dates. Since his test date and his production dates were different, it seemed like it might be possible that there was a selectivity issue with the dates; that SQL could return the data much faster with a more recent date than with an older one.

HEDMON did try that, and found (from his comments) that dates from 2017-02-25 forward responded quickly, while dates older than that were slow.

With this, he felt comfortable that the presentation of the date (using the {ts } syntax) had nothing to do with the issue.


How to get a selection from a join, where there is no result in one of the tables I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify the table structure as an example.

I have 2 tables I am working with. Each is an audit table which keeps track of changes such as updates and inserts via a trigger. Each table also has a column to store the date of change.

One table contains a county code, the other contains the street address: t_county and t_address

t_county

id:       NUMBER(9)
county:   NUMBER(2)
date:     DATE

t_address

id:       NUMBER(9)
address:  VARCHAR2(60)
date:     DATE

I need to build a query which selects the county and address if either one has a row on a specific day.

Say there is a change of address on a specific day, but there is no change in the county. I would like for the query to have a result such as below

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  NULL   |123 test|
+---------+---------+--------+

If there is a change of county but the address is the same:

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  32     |  NULL  |
+---------+---------+--------+

How can I build such a query?

Here's one option, which uses UNION of those tables. Aggregate (MAX) is used to display changes in both county and address in a single row; otherwise, you'd get two separate rows. This won't work correctly if there are several changes within a single date.

SQL> with t_county (id, county, cdate) as
  2    (select 1, 10, date '2018-08-02' from dual union all
  3     select 2, 20, date '2018-08-05' from dual
  4    ),
  5  t_address (id, address, cdate) as
  6    (select 1, '123 test', date '2018-08-10' from dual union all
  7     select 2, '222 twix', date '2018-08-05' from dual
  8    ),
  9  -- union of those tables
 10  t_union as
 11    (select id, county     , null address, cdate from t_county union all
 12     select id, null county, address     , cdate from t_address
 13    )
 14  select id,
 15    max(county) county,
 16    max(address) address
 17  from t_union
 18  where cdate = to_date('&par_date', 'yyyy-mm-dd')
 19  group by id;
Enter value for par_date: 2018-08-02

        ID     COUNTY ADDRESS
---------- ---------- --------
         1         10

SQL> /
Enter value for par_date: 2018-08-05

        ID     COUNTY ADDRESS
---------- ---------- --------
         2         20 222 twix

SQL>


Is it possible for a client to kill a long-running Postgres query? A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on the query?

Possible workaround: we created a stored procedure for users that would accept a PID to be killed (eg: sp_kill 4221) and would write the requesting user name and PID into a table.  That table was checked every 5 minutes by a script.  If the PID requested belonged to the user who requested it and it was still active (ie, select pid, usename from pg_stat_activity;) then the superuser would do the kill on the user's behalf.

Note that we only implemented this in development.


How to Prevent heavy queries on one database, affecting the performance of other databases on same server? I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server.

One of the databases has some computationally intensive queries run on it from time to time. This causes the CPU utilization to jump up to 100% and the sevrer often does not respond to requests.

How to Prevent heavy queries on one database, affecting the performance of other databases on same server? How can I make sure that other databases on that PostGres Instance still respond to requests? Is there a way to prevent one database from consuming all the CPU cycles?

I'll quote a few things from Postgres wiki, Priorities:

  Prioritizing users, queries, or databases

  PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

  There are three main resources that PostgreSQL users, queries, and databases will contend for:

  Memory
     CPU
     Disk I/O

  Of these, disk I/O is commonly a bottleneck for database applications, but that's not always the case. Some schema designs and queries are particularly CPU heavy. Others really benefit from having lots of memory to work with, typically for sorting.

Consider this part in in particular:

  Are priorities really the problem?

  Before struggling too much with prioritizing your queries/users/databases, it's worthwhile to optimize your queries and tune your database. You may find that you can get perfectly acceptable performance without playing with priorities or taking extreme measures, using techniques such as:

  Improving your queries
  Tune autovacuum to reduce bloat
  Generally polishing your cluster's performance
  Avoiding use of VACUUM FULL. That eat lots of memory and take forever to scan, wasting disk I/O bandwidth. See the wiki page on VACUUM FULL for more information.

And especially this:

  Is CPU really the bottleneck?

  People often complain of pegged (100%) CPU and assume that's the cause of database slowdowns. That's not necessarily the case - a system may show an apparent 100% CPU use, but in fact be mainly limited by I/O bandwidth. Consider the following test, which starts 20 `dd' processes, each reading a different 1GB block from the hard disk (here: /dev/md0) at 1GB offsets (run the test as root).

... test follows ...

  ... which could be confused for a busy CPU, but is really load caused by disk I/O. The key warning sign here is the presence of a high iowait cpu percentage ("%wa"), indicating that much of the apparent load is actually caused by delays in the I/O subsystem. Most of the `dd' processes are in 'D' state - ie uninterruptable sleep in a system call - and if you check "wchan" with "ps" you'll see that they're sleeping waiting for I/O.

  Rather than assuming that CPU contention is the issue, it's a good idea to use the available Performance Analysis Tools to get a better idea of where your system bottlenecks really are.

So, I'd say that you should:

first, follow the advice above, perform some more analysis tests and make sure that the bottleneck is from the database you suspect and that it's caused by CPU contention (which may well be the case if your queries are so computationally expensive). Or it may be partially true and there's CPU contention but also room for query improvements.
follow the advice in the 2nd paragraph above (improve the queries, generally polish the cluster, check and optimize vacuum and autovaccum settings.)
You should also optimize the various memory settings (mostly work_mem) with different values for the clients that connect to this database that has different workload.
If after all those, the issue remains, and there are still CPU or disk IO contention or both, there are some options described in the linked Priorities page that you can try ("renice", prioritize extension, etc).
One of the suggestions there (that is certainly worth considering) is separating your databases into 2 clusters (instances), one for the computanionally expensive db and another for all the rest, with the usual workload. See Prioritizing databases by separating into multiple clusters, where it mentions the likely cleaner option:
Separate machines

  Your best option is to separate PostgreSQL instances onto different physical machines. They won't compete for resources at all.


How to restart the distributor agent of transactional replication? After troubleshooting query time out error I would like to restart my distributor agent.

So I go to my distributor server, in my distributor DB (called distribution_SA)  I run the following query:

select s.name as [servername],
       d.name,
       d.publication
      ,s.*
   --d.publication, d.local_job, d.publisher_db, d.job_id, s.name
  from distribution_SA.dbo.MSdistribution_agents d
      inner join master.sys.servers s on d.subscriber_id = s.server_id
 --where d.publication = @publisher
order by d.publication, s.name

this gives me the following result (I had to leave the server names on, apologies):
What I want to achieve is the list of jobs that I need to restart in order for the changes that I did on my distributor agent profile to take place.

The problem is that I haven't been able to find these jobs. Where are them?

a) on the distributor

b) on the publisher

c) on the subscriber

Is there any better way of restarting the distributor agent?

I would prefer a T-SQL solution, when possible.

I found a simple solution here.

Distribution Agent - Start /Stop in T-SQL
-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db

My complete implementation, working on my test environment is below.
I have left everything there, select queries and procedures, to remind myself, where to go to get the data to be used as parameters to the sp_MSstartdistribution_agent and sp_MSstopdistribution_agent stored procedures.

--==================================================================================
-- script to start-stop the distribution agent job for a publication
-- marcelo miorelli
-- 12-mar-2015
--==================================================================================

--http://www.msqlserver.net/2013/01/distribution-agent-start-stop-in-t-sql.html

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test

use Bocss2
GO

-- get the distributor
sp_helpDistributor
--SQLDISTLON1 -- distributor server

--==================================================================================
-- Script to run on Distribution database
--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test

-- distributor database in this instance is called "distribution"
USE Distribution
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select * from dbo.MSrepl_errors
ORDER BY [TIME] DESC

-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
     AND da.subscriber_id = s.subscriber_id
--WHERE da.name = 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'
ORDER BY 1,2,3

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test

-- Run from Publisher Database
-- Get information for all databases
DECLARE @Detail CHAR(1)
SET @Detail = 'Y'

BEGIN TRY
DROP TABLE #tmp_replcationInfo
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #tmp_replcationInfo (
PublisherDB VARCHAR(128),
PublisherName VARCHAR(128),
TableName VARCHAR(128),
SubscriberServerName VARCHAR(128),
)
EXEC sp_msforeachdb
'use ?;
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
insert into #tmp_replcationInfo
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
'
IF @Detail = 'Y'
   SELECT * FROM #tmp_replcationInfo
ELSE
SELECT DISTINCT
PublisherDB
,PublisherName
,SubscriberServerName
FROM #tmp_replcationInfo

-------------------------------------------------------------------------
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
,SP.*
,SA.*
,S.*
,SRV.*

from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
-------------------------------------------------------------------------

--==================================================================================

--Msg 21482, Level 16, State 1, Procedure sp_MSstopdistribution_agent, Line 22
--sp_MSstopdistribution_agent can only be executed in the "distribution" database.

--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test

 --Distribution Agent - Start /Stop in T-SQL
    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
     @subscriber, @subscriber_db

--Msg 21846, Level 16, State 1, Procedure sp_MSdistributionagentjobcontrol, Line 60
--Cannot find a distribution agent job for the specified transactional or snapshot push subscription.

use distribution
go

sp_helpdistpublisher

select msda.*
       ,sjv.*
       ,sjs.*
      from dbo.MSdistribution_agents as msda
inner join msdb.dbo.sysjobs_view as sjv
        on msda.job_id = sjv.job_id
inner join msdb.dbo.sysjobsteps as sjs
        on sjv.job_id = sjs.job_id

    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent 'SQLBOCSSLON1', 'Bocss2Archive', 'Bocss2ArchiveMergedAccounts',
     'REPLON1', 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'

    --To START the Distribution Agent:
    sp_MSstartdistribution_agent @publisher = 'SQLBOCSSLON1',
                                 @publisher_db = 'Bocss2Archive',
                                 @publication = 'Bocss2ArchiveMergedAccounts',
                                 @subscriber = 'REPLON1',
                                 @subscriber_db = 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'


Backup up database to compressed local folder - what will be the performance implication? I have sql server 2016 web edition on Windows 2016 server VM.

I want to backup the database, to a folder on a local drive (SSD) - please don't ask why, long story.

Due to the size of the DBs, and comparatively small size of disk, I want to back up to a compressed folder.

What would be the performance implications? Will they be noticeable? (VM with 16 cores, 128GB ram, typically cpu utalization ~ 20%)

Looking for some suggestions based on experience

Don't backup to a compressed folder.  There are supportability problems for SQL Server with compressed folders.  See here.  Take a compressed backup instead.


Error 9002 state 3 after RESTORE DATABASE I'm trying to restore a database from a validated backup (through RESTORE VERIFYONLY).

The command I've used :

RESTORE DATABASE [RESTORE] FILE = N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8'
FROM  DISK = N'0000008B.bak'
WITH
    FILE = 1,
    MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' TO N'F:\RESTORE.mdf',
    MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' TO N'F:\RESTORE_log.ldf',
    NOUNLOAD,
    REPLACE,
    STATS = 10
GO

At the end of the restore operation, I got this error :

...
100 percent processed.
Processed 16888 pages for database 'RESTORE', file 'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' on file 1.
Processed 1707474 pages for database 'RESTORE', file 'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' on file 1.
Msg 9004, Level 16, State 3, Line 1
An error occurred while processing the log for database 'RESTORE'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then the database is stuck in RESTORING status. The error message suggest to rebuild the log, BUT in RESTORING state I can't do anything : the database can't be accessed for repair with DBCC CHECKDB.

I've tried :

RESTORE DATABASE [RESTORE] WITH RECOVERY
GO

Result :

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I've googled It many times, searching on this forum, but can't find a solution to apply suggested procedure "it might be necessary to rebuild the log."

My question is  : how to get rid of this status, ie make the database status to be EMERGENCY so I can try to run repair tools on it ?

Hope somebody can help me.
Regards.

Update 1

I've managed to get our database in "SUSPECT" status, with Sahnky's tweak. From there, and with some external help, I have run DDBCC CHECKDB with REPAIR_ALLOW_DATALOSS and got our database out of SUSPECT status.

I've omitted to mention that I've tried to RESTORE without the FILE option to perform a full restore, but it hangs with errors (I can't remember which ones).

Please note that restore 'verifyonly' does not checks consistency of backup completely only a successful restore will guarantee that you backup is in consistent state. There is a option to use Continue_after_error can you please use that in your restore script.

    RESTORE DATABASE [RESTORE] FILE = N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8'
    FROM  DISK = N'0000008B.bak'
    WITH
        FILE = 1,
        MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' TO N'F:\RESTORE.mdf',
        MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' TO N'F:\RESTORE_log.ldf',
        NOUNLOAD,
        REPLACE,
        STATS = 10,
        CONTINUE_AFTER_ERROR

Please note that this is a temporary workaround if it works. You should get a fresh backup from production again. Also I highly recommend you to run dbcc checkdb on production database


Mariadb on Centos 7 not changing max connections based on my.cnf The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#symbolic-links=0

#vish start
open_files_limit = 8000
max_connections = 500
#set-variable=max_connections=500
thread_concurrency = 8
#concurrent_insert=2
thread_cache_size=2000

interactive_timeout=180
wait_timeout=180

max_allowed_packet = 32M
key_buffer_size = 3000M
read_buffer_size = 16M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 256M
myisam_repair_threads = 1
#myisam_recover = 4
max_heap_table_size = 2048M
tmp_table_size = 1024M
table_open_cache = 2000
table_cache = 2000
sort_buffer_size = 128M
join_buffer_size = 128M
query_cache_size = 128M
query_cache_limit = 128M

#slow_query_log=1
log-slow-queries
long_query_time=2
#log_queries_not_using_indexes=1
slow_query_log_file = /var/log/mariadb/host_name-slow.log

[mysqldump]
user=root
password=*********
#max_allowed_packet = 1024M

[myisamchk]
key_buffer_size = 2500M
sort_buffer_size = 1024M
read_buffer = 32M
write_buffer = 32M

[innodb]
innodb_buffer_pool_size=3G
innodb_buffer_pool_instance=2
innodb_additional_mem_pool_size=20M
innodb_log_file_size= 512M
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=16
#innodb_read_io_threads=16
#innodb_write_io_threads=16
#innodb_io_capacity=2000
#innodb_lock_wait_timeout=120

innodb_flush_method=O_DIRECT
#vish end

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#!includedir /etc/my.cnf.d

You might find that the limit is not where you think.

I've had trouble with open file limits and that's set by Centos itself.
Please see:

How to permanently raise ulimit 'open files' and MySQL 'open_files_limit'

Some key points from that link:

The default ulimit on open files is 1024.
MySQL sets its open_files_limit to whatever the system's ulimit is set to.
MySQL cannot set it's open_files_limit higher than ulimit.

You can raise the ulimit temporarily or permanently, globally or for specific users; see the link for details.

There are more resources like that around.


For BCNF decomposition, is it okay to have a relation with none of the original functional dependencies? Given the relation R[c, f, g, h, e, j, a, b, d, i] with the below functional dependencies I have come up with the below solution. However, I am unsure about it, considering R8 has none of the listed functional dependencies even though it has the LHS of some. I followed the instructions for decomposition into BCNF but I am unsure it this is the correct solution?

Functional Dependencies

a → b
{ c, a } → d
c → e
f → { g, h, i, j }

Solution

In BCNF:
R1[f, g, h, i, j]
R3[a, b]
R5[c, a, d]
R7[c, e]
R8[c, f, a]

Your solution is correct, and correctly in R8 no non-trivial dependency holds.

This happens since the classical so-called “analysis” algorithm for BCNF, works by removing from the original relation (and the decomposed relations) the dependencies that violates the Normal Form, leaving behind only the left hand side of the dependency.

In this case, at the end of the algorithm, the relation that remains is {c, f, a} in which there are no non-trivial dependencies (so that {c, f, a} is the only candidate key of the relation).

One can note that these three attributes are also a candidate key (actually the only candidate key) of the original relation.

In fact, there is a theorem that says that to have a loss-less decomposition it is necessary to have at least a relation that contains all the attributes of a candidate key.

Finally, note that the decomposition also preserves the functional dependencies (and this is not obvious since the algorithm to decompose in BCNF sometimes produces a decomposed schema with a loss of dependencies).


Retain indent formatting when compiling a stored procedure without Data Studio When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out.  Only when I compile in Data Studio is the formatting retained, but I would like to deploy projects with a script.
I expect there is something simple I am missing, but have not been able to find a solution or even any other posts with the same issue.
Using CLPplus I ran this script file @c:\temp\proc1.spsql
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end

The SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'; produced the the following:
create or replace procedure proc1()
begin
declare vId int default 0;
call dbms_output.put_line(vId+1);

/*
Testing
*/
end

It works fine for me in clpplus 1.6
$ clpplus -h | head -1
CLPPlus: Version 1.6
$ cat sql
create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);

/*
    Testing
*/
end
/
SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME = 'PROC1'
/
$ clpplus -nw $user/"$password"@$host:$port/$database @"$PWD"/sql
CLPPlus: Version 1.6
Copyright (c) 2009, 2011, IBM CORPORATION.  All rights reserved.

Database Connection Information :
---------------------------------
Hostname = localhost
Database server = DB2/LINUXX8664  SQL11054
SQL authorization ID = paul
Local database alias = BP
Port = 50003

DB250000I: The command completed successfully.

create or replace procedure proc1()
begin
    declare vId int default 0;
    call dbms_output.put_line(vId+1);
/*
    Testing
*/
end
SQL>


How to improve performance on PostgreSQL when using multiple concurrent processes? Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database.  We may have around 100 - 200 or more concurrent processes executing queries against the database.

We have no connection pooling systems like pgpoolII or pgbounce currently installed.

I have tested a query which can take in the order of 1-10 seconds from our application, which when run over psql will take at most 20 ms.

We believe this issue is related to concurrent connections particularly because of the behavior described above and because we see these performance hits when accessing different tables in the system.  However, we are not sure of this and could very readily accept alternate suggestions or solutions.

What is the usual way of handling concurrent connections from multiple processes against a single database server, in a way that will enable queries to execute quickly?

You sort of answer your own question when you say you have no pooling but...

This is not an answer out of the box, with all client/db stuff you may need to do some work to determine exactly what is amiss

backup postgresql.conf changing

log_min_duration_statement to 0
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on                # Enable capturing of stderr and csvlog
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan output = on
log_min_messages = info (debug1 for all server versions prior to 8.4)

Stop and restart your database server ( reload may not pick up the changes ) Reproduce your tests ensuring that the server time and client times match and that you record the start times etc.

copy the log file off an import into editor of your choice (excel or another spreadsheet can be useful for getting advance manipulation for sql & plans etc)

now examine the timings from the server side and note:

is the sql reported on the server the same in each case

if the same you should have the same timings

is the client generating a cursor rather than passing sql

is the query arriving on the server when you believe it should do

is one driver doing a lot of casting/converting between character sets or implicit converting of other types such as dates or timestamps.

and so on

The plan data will be included for completeness, this may inform if there are gross differences in the SQL submitted by the clients.


Some timeout in query store but get all SP:Starting and SP:Completed in profiler I have a report SP running. The user complaint report timeout sometimes. And I do get some canceled execution plan in my query store.

But in my profiler tracing file, every SP:Starting has its SP:Completed. Am I missing any? or could you please give me some direction?

Thanks,

If you capture queries when they finish with the SQL:BatchCompleted or SP:Completed trace running, aborting a query will show up as a finished query.

Lets say I run this query:

select * from master..spt_values spt1
CROSS APPLY master..spt_values spt2;

And kill it end it after 2 seconds of executing:

It will still show up in my profiler trace. What will be different is the error column. On normal executions it will show as: 0 = OK. When aborting the query, it shows as: 2 - Abort

The same is true for aborting a procedure, it will show up in sp:completed.

Avoiding aborted queries as they time out is finding why they took over 30 seconds.
One of the things you should read through is Why is my query suddenly slower than it was yesterday?.

You can post a new question to seek advice on improving the query runtime.

We would need the actual execution plan, table definition, ... (generally as much information as possible). I am sure that we would be able to help you with the reporting query.

Another band-aid solution could be increasing the time the queries are allowed to run.

  BTW: The canceled query used the same execution plan as the quick one.
  It should not be the execution plan issue

It could be running with different parameters, resulting in parameter sniffing.
Adding OPTION(RECOMPILE) to the query could disable parameter sniffing if you are willing to live with the cost of recompiling the query on each execution.

Other reasons could be waiting for resources, such as being blocked, cpu / memory pressure, ....


Exclude All Matching Rows I have a table that has a unique ID, name, email, and some other fields. I want to match this table against another table on email, but sometimes emails are shared. The unique ID is not shared among tables, but I will input the ID into the other table when I have identified them. Name is not unique.

I'd like to build two rounds of joins. The first round I would like to match on email and append the ID, and the second round would match on ID and update fields as necessary.

Is there a way to query into the table and grab only those that have unique emails, while ignoring those that share emails? I could then append the ID and re-query those that don't have IDs and join on the name. Would that even work?

Would this be a good use of a partition? I don't understand those well.

Is there a way to query into the table and grab only those that have
  unique emails, while ignoring those that share emails?

I can think of a couple ways to do this:

1) If I restate this, I might say "Get all of the email addresses that have only 1 entry in the table, and exclude email addresses having more than one entry in the table.
Use GROUP BY...HAVING to identify the non-duplicated email addresses, grouping by the address, and literally looking for a COUNT of 1 (or > 1):

--UNIQUE EMAILS
SELECT EmailAddress
FROM TableName
GROUP BY EmailAddress
HAVING COUNT(*) = 1;

---NON-UNIQUE EMAILS
SELECT EmailAddress
FROM TableName
GROUP BY EmailAddress
HAVING COUNT(*) > 1;

This would literally give you the list of email addresses, which you would then need to do stuff with.

2) If I restate your question another way, "Get all of the emails where there doesn't exist another row with the same email address. Use 'EXISTS' and 'NOT EXISTS' and a correlated subquery to identify rows with or without duplicates.

--UNIQUE EMAILS
SELECT t1.EmailId, t1.EmailAddress
FROM TableName t1
WHERE NOT EXISTS (SELECT 1 FROM TableName t2
                 WHERE t1.EMailId <> t2.EmailId AND t1.EmailAddress = t2.EmailAddress);

--NON-UNIQUE EMAILS
SELECT t1.EmailId, t1.EmailAddress
FROM TableName t1
WHERE EXISTS (SELECT 1 FROM TableName t2
                 WHERE t1.EMailId <> t2.EmailId AND t1.EmailAddress = t2.EmailAddress);

Personally, I'd go with the second approach, as I think the logic is easier to follow, and I suspect it would perform better (though, that's just a guess).


can we call CRUD operations as service methods? I was asked this question by one of my superiors. The usual operations we do on database table from Service layer in java is Create, Update, Retrieve, Delete. These operations are basically services, so is that is the reason why they call it as Service layer also can we call all the CRUD operations as service methods?

Sometimes when people do this (abstract the SQL layer) it's because they want to (maybe in the future) use multiple database backends (with their own code).

Sometimes, it's as a result of a framework abstraction which occurs for the same reason - i.e. those developing the framework want to promise "all things to all men" - i.e. don't worry about SQL - we'll take care of that for you.

See this thread in response to a related question (in particular the accepted answer and also mine) - the perils of database "abstraction layers" - basically they lead to "lowest common denominatorism".

Using these for standard CRUD operations can make sense, but it's when you start trying to optimise complex queries (big differences between different servers) that these "panaceas" become worse than the illness they're trying to cure (my database is sick - i.e. slow).


mongodump failing with invalid json input mongodump failing with invalid json input
mongod --version
db version v3.4.1
git version: 5e103c4f5583e2566a45d740225dc250baacfbd7
OpenSSL version: OpenSSL 1.0.2n  7 Dec 2017
allocator: tcmalloc
modules: none
build environment:
    distmod: ubuntu1404
    distarch: x86_64
    target_arch: x86_64

/usr/bin/mongodump -u backup -p backup12345  --host=XX.XX.XX.XX --port XXXX --out=/tmp -d local -c oplog.rs --gzip --authenticationDatabase admin --query "{'ts': { \$gt: Timestamp(1619548593, 1) } }" --ssl --sslAllowInvalidCertificates

2021-04-28T01:21:37.694-0700    Failed: error parsing query as Extended JSON: invalid JSON input

anyone can figure what's the issue here ?
I understand that I'm using 3.4.1 which is older but still it is working on the other server and with same version. The same query runs perfectly alright on a different cluster node.
Also tried using the latest version of mongodump still the same error.
mongodb-database-tools-ubuntu1804-x86_64-100.3.1.tgz

I passed the same --query parameter as below in my command and I'm getting corresponding error
--query '{ "ts": { "$gt": Timestamp(1111111, 1) }}'

2021-04-28T11:10:01.328-0700    Failed: error parsing query as Extended JSON: error decoding key ts: invalid JSON input. Position: 17. Character: T

--query '{ "ts": { "$gt": ISODate("2021-04-05T08:00:00.000Z") }}'

2021-04-28T11:10:35.627-0700    Failed: error parsing query as Extended JSON: error decoding key ts: invalid JSON input. Position: 17. Character: I

--query "{ 'ts': { \$gt:  ISODate(\"2021-04-05T08:00:00.000Z\") }}"

2021-04-28T11:11:20.797-0700    Failed: error parsing query as Extended JSON: invalid JSON input

Try adjusting the input:
/usr/bin/mongodump -u backup -p backup12345  --host=XX.XX.XX.XX --port XXXX --out=/tmp -d local -c oplog.rs --gzip --authenticationDatabase admin \
--query '{"ts": { "$gt": Timestamp(1619548593, 1) } }'
--ssl --sslAllowInvalidCertificates

I tried multiple versions and they worked fine with me:

--query '{ "ts": { "$gt": Timestamp(1111111, 1) }}'
--query '{ "ts": { "$gt": ISODate("2021-04-05T08:00:00.000Z") }}'
--query "{ 'ts': { \$gt:  ISODate(\"2021-04-05T08:00:00.000Z\") }}"

In older mongo versions you need to use the "extended json":

--query '{ "ts": { "$gt": {"$timestamp":{"t":1565545664,"i":1}} }}'

this is completetly valid json and should work with the old client as well.
also see mongodump command with timestamp query


how to Enable these Windows Firewall rules using powershell? I have a few servers where I see a blue question mark on the sql server service, as per the picture below.

This is the solution:

Enabling these Windows Firewall rules did the trick for me
Windows Management Instrumentation (WMI-In)
Windows Management
Instrumentation (DCOM-In)

Is there a way I could achieve this via powershell?

You would use the Enable-NetFirewallRule cmdlet:

Enable-NetFirewallRule -Name "WMI-WINMGMT-In-TCP", "WMI-RPCSS-In-TCP"

In order to find the relevant rule names (in order to enable it), you can search existing firewall rules by name by using the Get-NetFirewallRule cmdlet, for example:

Get-NetFirewallRule -Name "*WMI*"

This uses wildcards to final all the rules that contain "WMI" as an example.


How to verify a SSAS Backup and how to perform Integrity Checks in SSAS? I have a procedure to backup SSAS Databases that is working successfully.

I also delete the SSAS backup files that are older than 2 days.

What I am looking forward to implement is:

1) How to verify my SSAS backups? (restore verify only)

2) How to run a DBCC CheckDB in SSAS? (database integrity)

But I have not found any substantial documentation for these.

Any suggestions?

I know about the right-click/ Validate database on Visual Studio but I would like an automated process, not manually clicking.

I found a post by Darren Gosbell, a SQL Server MVP (at the time at least).  His post can be found here:

http://geekswithblogs.net/darrengosbell/archive/2008/09/13/ssas-verifying-backups.aspx

He points out that you cannot run this from your desktop, but you can run it on the server to do the verification.  The instructions from 2008 are:

Navigate to:   <Program Files>\Microsoft SQL Server\MSAS10.SQL08\OLAP\bin

Then type:  msmdsrv /?

Look at the commands available with msmdsrv and see what you want to do.  (I am away from a SSAS installation at the moment, so I cannot verify, but it should be worth checking out.)

EDIT:
2) How to run a DBCC CheckDB in SSAS? (database integrity)

Starting with SQL 2016 we have a DBCC CHECKDB command for SSAS for Dimensional as well as Tabular

  DBCC provides on-demand database validation for Multidimensional and
  Tabular databases on an Analysis Services instance. You can execute
  DBCC in an MDX or XMLA query window in SQL Server Management Studio
  (SSMS) and trace the DBCC output in either SQL Server Profiler or
  xEvent sessions in SSMS. The command takes an object definition and
  returns either an empty result set or detailed error information if
  the object is corrupted. In this article, you’ll learn how to run the
  command, interpret results, and address any problems that arise.


Where to look for a bottleneck after elimitating CPU, memory, and disk I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run in the shortest amount of time through experimentation (WITH INDEX NOLOCK, FORCE ORDER, LOOP JOIN, MAXDOP 0). I'm happy with the improvement, but I hate not knowing why a query takes as long as it does. I'm perfectly OK with bottlenecks, as long as I know where they are. When this query is running, there are significant periods of time where the obvious resources are all underutilized. What is SQL Server doing at these times?

The execution plan and statistics IO will tell you everything you need to know about what is going on.

There are four places the SQL Server can bottleneck and you listed three of them.  Disk (logical and physical IO), RAM or CPU.  After that it's just crappy code that generates more logical IO than is actually needed.

With all those tuning hints in there you've probably got some code that needs to be cleaned up.  You need to look at the execution plan for the query (post it if you'd like) and see where the problems in your query are at.  You can see if the IO is logical (cache hits) or physical (disk hits) from looking at the output when set statistics io on is enabled.


Postgres SQL output JSON Error I'm trying to create a SQL code in which I want to return JSON similar to this format:

{
  "type": "FeatureCollection",
  "features": [
   {
     "type": "Feature",
      "properties": {
      "id": 1,
      "zone": "A",
      "nb_stats": 136,
      "occupation": 0
   },...]}

So far I manage to code a script that output a certain part of the JSON format, but I'm enable to generate the properties field with the values.

Here my SQL code:

Select json_build_object(
'type', 'FeatureCollection',
'features',json_build_array(
  json_build_object(
  'type','Feature',
'properties', (select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties))))

By executing this code, I'm always getting a error saying that the subQuery return more than line. Error 21000.

Please note that if I only execute the select row_to_json(properties) I am able to output the result all the values of the table:

select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties

"{"id":1,"zone":"A","nb_stats":136,"occupation":0}"

You can use a PostgreSQL Aggregate Function to form a JSON array from the set of subquery results.

Aggregate functions compute a single result from a set of input values.

Also, the function json_build_object expects key value pairs, but you can deal with that by expressly providing the column name as a string for the key.

For example, given this setup:

CREATE TABLE zone (
    id serial PRIMARY KEY,
    name varchar(150) NOT NULL,
    nb_stats integer NOT NULL,
    occupation integer NOT NULL
);

INSERT INTO zone VALUES (1, 'a', 136, 0);
INSERT INTO zone VALUES (2, 'b', 145, 1);
INSERT INTO zone VALUES (3, 'c', 120, 0);

And this query:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select json_agg(p1) from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

You get this result:

=# \i query.sql
                                                              json_build_object
---------------------------------------------------------------------------------------------------------------------------------------------
 {"type" : "FeatureCollection", "features" : [{"type":"feature","properties":{"id" : 1, "name" : "a", "nb_stats" : 136, "occupation" : 0}}, +
  {"type":"feature","properties":{"id" : 2, "name" : "b", "nb_stats" : 145, "occupation" : 1}},                                             +
  {"type":"feature","properties":{"id" : 3, "name" : "c", "nb_stats" : 120, "occupation" : 0}}]}

Rows are joined by newlines.  If you need to get rid of the newlines, you can use the regexp_replace() function with the pattern E'\n' and the 'g' global modifier.  But json is an actual type in PostgreSQL, and regexp_replace() expects text type input.  So to use that function you'll need to cast the json to text, then cast the newline-free resulting text back to json, like so:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select regexp_replace(json_agg(p1)::text, E'\n', '', 'g')::json from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

Alternatively, you can solve the problem programmatically by writing a script that connects to the database, selects rows from the zone table, and then marshals them into JSON.

For example, here's a simple Go script that does that:

package main

import(
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/lib/pq"
)

type ZoneRow struct {
    Id          int
    Name        string
    NbStats     int
    Occupation  int
}

type Feature struct {
    Type        string
    Properties  ZoneRow
}

type Result struct {
    Type        string
    Features    []Feature
}

func (res *Result) AddZoneRow(id int, name string, nbStats int, occupation int) {
    newZoneRow := Feature{`Feature`, ZoneRow{id, name, nbStats, occupation}}
    res.Features = append(res.Features, newZoneRow)
}

func main() {
    db, err := sql.Open(`postgres`,
        `host=/path/to/postgresql/ user=me dbname=mine`)
    if err != nil {
        fmt.Println(err)
    } else {
        rows, err := db.Query(`SELECT * FROM zone`)
        defer rows.Close()
        if err != nil {
            fmt.Println(err)
        } else {
            res := new(Result)
            res.Type = `FeatureCollection`
            for rows.Next() {
                var id int
                var name string
                var nbStats int
                var occupation int
                err = rows.Scan(&id, &name, &nbStats, &occupation)
                if err != nil {
                    fmt.Println(err)
                } else {
                    res.AddZoneRow(id, name, nbStats, occupation)
                }
            }
            err = rows.Err()
            if err != nil {
                fmt.Println(err)
            } else {
                empty := ``
                fourSpaces := `    `
                b, err := json.MarshalIndent(res,empty,fourSpaces)
                if err != nil {
                    fmt.Println(err)
                } else {
                    fmt.Println(string(b))
                }
            }
        }
    }
}

Which, given the same table and row setup above, prints this output:

{
    "Type": "FeatureCollection",
    "Features": [
        {
            "Type": "Feature",
            "Properties": {
                "Id": 1,
                "Name": "a",
                "NbStats": 136,
                "Occupation": 0
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 2,
                "Name": "b",
                "NbStats": 145,
                "Occupation": 1
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 3,
                "Name": "c",
                "NbStats": 120,
                "Occupation": 0
            }
        }
    ]
}