mysql workbench migration changed my database. Can I trust it? I'm migrating a large production database(s) from mysql 5.5 to 5.6.
Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines.

The process worked well but there are major changes to the database:

For example:

Changed order of fields in primary key
PRIMARY KEY (ru_sid,ru_rule_id), =>   PRIMARY KEY (ru_rule_id,ru_sid)
Messed encoding while the data is expected to be utf8
I did a query of the same data in the original and new server and the output is different:
Pré-Home Flash Sales => PrÃ©-Home Flash Sales

Possibly there are other changes.

My question is - how can I tell if the migration is trustable. I cant check every table to see if changes in the index will not break something in my code.
Can the workbench migration be trusted ?
Is there a better way to do the migration? What would you do?

EDIT:
Create statement before:
 (I removed the fields that are the same in the before and after)

CREATE TABLErules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
ru_datatext COLLATE utf8_unicode_ci NOT NULL,
ru_namevarchar(64) COLLATE utf8_unicode_ci NOT NULL,
ru_tagsvarchar(64) COLLATE utf8_unicode_ci DEFAULT '',
ru_priorityvarchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
  PRIMARY KEY (ru_sid,ru_rule_id),
  KEYnum(ru_rule_id) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9998 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create statement after:

CREATE TABLErules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) CHARACTER SET utf8 NOT NULL DEFAULT '',
ru_datatext CHARACTER SET utf8 NOT NULL,
ru_namevarchar(64) CHARACTER SET utf8 NOT NULL,
ru_tagsvarchar(64) CHARACTER SET utf8 DEFAULT '',
ru_priorityvarchar(12) CHARACTER SET utf8 NOT NULL DEFAULT '1',
  PRIMARY KEY (ru_rule_id,ru_sid),
  KEYnum(ru_rule_id)
) ENGINE=MyISAM AUTO_INCREMENT=9954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Seems you've found a bug in migration wizard, I follow your description and I was able to repeat issue on my machine. Can you please fill bug report at bugs.mysql.com? Appreciate. We'll do our best to fix that isse.
As workaround you can try this way - use Data Export to export only structure of your database. Run exported stricpt on target server. Then run migration wizard normally, but skip create target schema step. Let me know if that works for you.


Eliminate key lookup in execution plan I have the following query:

DECLARE @p__linq__0 UNIQUEIDENTIFIER

SET @p__linq__0 = '... some guid ...'

SELECT TOP 1
    [EventId] AS [EventId],
    [DateCreated] AS [DateCreated],
    [LocationId] AS [LocationId],
    [SourceName] AS [SourceName],
    [SourceState] AS [SourceState],
    [Priority] AS [Priority],
    [EventDescription] AS [EventDescription],
    [FirstTrigger] AS [FirstTrigger]
FROM [dbo].[Watchdog]
WHERE
    [LocationId] = @p__linq__0
    AND
    [FirstTrigger] = 1
ORDER BY [DateCreated] DESC

Watchdog table defines 2 indecies:

Clustered index on EventId primary key column
Unclustered index on DateCreated column

This is actual execution plan for the query:

Reading other posts on how to eliminate key lookup I added another non-clustered index which includes all columns from SELECT

CREATE NONCLUSTERED INDEX [LocationId_FirstTrigger] ON [dbo].[Watchdog]
(
    [LocationId] ASC,
    [FirstTrigger] ASC
)
INCLUDE (   [EventId],
    [DateCreated],
    [SourceName],
    [SourceState],
    [Priority],
    [EventDescription]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

However, this didn't help and actual execution plan is the same. If I look at key lookup the output is actually included in newly added non clustered index.

My question is, why it's still doing key lookup instead of index scan/seek ?

UPDATE

Following some suggestions in the comments, I dropped newly created non clustered index & instead recreated non clustered index on DateCreated column including columns from SELECT.

Now execution plan is the following:

Also query execution time dropped from 1+ minute to few seconds (this table has 18+ million rows).

Does this mean key lookup was done due to ORDER BY on non-clustered index ?

My question is, why it's still doing key lookup instead of index scan/seek ?

The query specifies that results should be ordered by DateCreated.  Since you already had a nonclustered index on DateCreated, the optimizer decided that the cost of doing key lookups was lower than sorting all of the data by DateCreated.

  Does this mean key lookup was done due to ORDER BY on non-clustered index ?

Essentially, yes.  It was estimated to be cheaper* to read the data in the required order, and get any additional fields through a key lookup, rather than reading all of the fields from a single index and then sorting it by DateCreated.

You could confirm this by comparing the estimated costs between

the original query (with the original indexes), and
the original query with an index hint

The index hint would be like this on the FROM line:

FROM [dbo].[Watchdog] WITH (INDEX (LocationId_FirstTrigger))

This should produce a plan with no key lookups (since LocationId_FirstTrigger is covering for that query), and a Sort operator.  I'd expect the "Estimated Cost" to be higher, thus the other plan was chosen.

* To explain the optimizer's choice here:

The TOP (1) in your query means the optimizer sets a row goal, meaning the plan is geared toward producing one row quickly. The optimizer expects to find one row from the Index Scan matching your LocationId predicate very quickly, since it assumes values are distributed uniformly. This may or may not be true in reality. The cost of one Key Lookup following the Index Scan is pretty small.

The scan + lookup option therefore looks cheaper to the optimizer than finding matches using LocationId_FirstTrigger and sorting. You can turn the row goal logic off for the query as a test by adding an OPTION (QUERYTRACEON 4138) hint. You will likely find the optimizer then chooses the LocationId_FirstTrigger index without an index hint.

Still, the best alternative is to modify your index as Mikael Eriksson suggests.


SQL Server 2017: How do Batch Mode Adaptive Joins work? Batch Mode Adaptive Joins are part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how do Adaptive Joins work?

Batch Mode Adaptive Joins

For Batch Mode Adaptive Joins, the goal is to not pin join choice to a specific type at compile time.

When available, Adaptive Joins allow the optimizer to choose between Nested Loops Joins and Hash Joins based on row thresholds at run time.

At this time, Merge Joins are not considered. Pure speculation is that needing data to be sorted, or needing to inject a sort into the plan would add too much overhead when changing the course of a query.

When Do Batch Mode Adaptive Joins Occur?

At this time, Batch Mode query processing requires the presence of a ColumnStore index. They also require, well, a join, and an index that allows for the choice of a Nested Loops or Hash Join.

How do I know if my Join is Adaptive?

Query plans for adaptive joins are quite distinctive.

The Adaptive Join operator is new to SQL Server 2017, and currently has the following properties in actual execution plans.

Physical Operation: Adaptive Join
Actual Join Type: Will be Hash match or Nested Loops
Adaptive Threshold Rows: Signifies the tipping point when the join type will switch to Hash Match
Is Adaptive: True for Adaptive Joins
Estimated Join Type: Rather self-explanatory!

In an estimated or cached plan, there is rather less information:

Most notably, the Actual Join Type is missing.

What breaks Batch Mode Adaptive Joins?

To monitor this, there's an Extended Event session called adaptive_join_skipped, and it has the following reasons for skipping a Batch Mode Adaptive Join:

eajsrExchangeTypeNotSupported
eajsrHJorNLJNotFound
eajsrInvalidAdaptiveThreshold
eajsrMultiConsumerSpool
eajsrOuterCardMaxOne
eajsrOuterSideParallelMarked
eajsrUnMatchedOuter

Aside from those, Batch Mode Adaptive Joins may be skipped for other reasons. Take these two queries for example:

/*Selecting just integer data*/
SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

/*Selecting one string column from Users*/
SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

They're identical except that the second query selects the DisplayName column, which has a type of NVARCHAR(40).

The Batch Mode Adaptive Join is skipped for the second query, but no reason is logged to the XE session. It appears that string data remains the steadfast enemy of ColumnStore indexes.

There are other query patterns that fail to get Adaptive Joins, that also do not trigger events.

Some examples:

CROSS APPLY with a TOP
OUTER APPLY

eajsrExchangeTypeNotSupported

One thing that will trigger this event appears to be the presence of a Repartition Streams operator. In this query, the partitioning type is Hash Match. Special thanks to the Intergalactic Celestial Being masquerading as a humble blogging man known as Paul White for the bizarre query.

SELECT uc.Id, uc.Reputation, CONVERT(XML, CONVERT(NVARCHAR(10), p.Score)).value('(xml/text())[1]', 'INT') AS [Surprise!]
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01'
OPTION ( USE HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ));
GO

eajsrHJorNLJNotFound

No queries have triggered this XE yet.
What doesn't work:

Merge Join hint
Query patterns that rule out join type, for example Hash and Merge joins require at least one equality predicate. Writing a join on >= and <= does not trigger the event.

eajsrInvalidAdaptiveThreshold

This event can be triggered by various TOP, FAST N, and OFFSET/FETCH queries. Here are some examples:

SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01'
OPTION ( FAST 1);
GO

SELECT TOP 1 uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01';
GO

In some circumstances, they can also be triggered by paging-style queries:

WITH pages
AS ( SELECT TOP 100 uc.Id, ROW_NUMBER() OVER ( ORDER BY uc.Id ) AS n
     FROM   dbo.Users_cx AS uc ),
     rows
AS ( SELECT TOP 50 p.Id
     FROM   pages AS p
     WHERE  p.n > 50 )
SELECT u.Id, u.Reputation
FROM   pages AS p
JOIN   dbo.Users AS u
ON p.Id = u.Id;

eajsrMultiConsumerSpool

No known query patterns have triggered this event yet.

What hasn't triggered it so far:

Recursive CTEs
Grouping sets/Cube/Rollup
PIVOT and UNPIVOT
Windowing functions

eajsrOuterCardMaxOne

A couple different types of queries have triggered this event. A derived join with a TOP 1, and a join combined with a WHERE clause with an equality predicate on a unique column:

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   (SELECT TOP 1 p2.OwnerUserId, p2.Score FROM dbo.Posts AS p2 ORDER BY Id) AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01';
GO

SELECT p.Id, p.ParentId, p.OwnerUserId
FROM dbo.Posts AS p
JOIN dbo.Users_cx AS uc
ON uc.Id = p.OwnerUserId
WHERE p.Id = 17333;
GO

eajsrOuterSideParallelMarked

One type of query that can trigger this event is a Recursive CTE.

WITH postparent AS
    (
    SELECT p.Id, p.ParentId, p.OwnerUserId
    FROM dbo.Posts_cx AS p
    WHERE p.Id = 17333

        UNION ALL

    SELECT p2.Id, p2.ParentId, p2.OwnerUserId
    FROM postparent pp
    JOIN dbo.Posts_cx AS p2
    ON pp.Id = p2.ParentId
    )
    SELECT pp.Id, pp.ParentId, pp.OwnerUserId, u.DisplayName
    FROM postparent pp
    JOIN dbo.Users AS u
    ON u.Id = pp.OwnerUserId
    ORDER BY pp.Id
    OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

The reason here appears to be that the recursive portion of the CTE, which causes a serial zone in the plan, disallows Batch Mode Adaptive Join choice.

eajsrUnMatchedOuter

This is the most common, and seems to occur when an index is used for the join that cannot support a seek. For instance, this query causes a Key Lookup:

SELECT uc.Id, uc.Reputation, p.Score, p.LastActivityDate
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20080101'
       AND uc.DisplayName = 'harrymc'
       AND p.Score > 1;

The resulting query chooses a Row Mode Nested Loops join to execute both the Key Lookup and the table joins, which triggers the event.

Another example is a query that skips the narrow nonclustered index in favor of the PK/CX. In this case, the PK/CX does not lead with OwnerUserId, so the only join choice is a Hash Join.

In both cases, the "unmatched outer" seems to indicate that the index chosen does not sufficiently cover our query.

SELECT uc.Id, uc.Reputation, p.*
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

Do Batch Mode Adaptive Joins work with multiple joins?

Yes, but as of this writing, there appears to be a limitation:

Joining from one ColumnStore index to multiple Row Store indexes will yield multiple Adaptive Joins, whereas a join between multiple ColumnStore indexes will not be Adaptive.

For example, these two queries

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments_cx AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

The first query joins one ColumnStore index (on Users) to two Row Store indexes on Posts and Comments. This yields two Adaptive Join operators.

The second query joins two ColumnStore tables (Users and Comments) to one Row Store table (Posts), and yields one Adaptive Join.

Is there any overhead to Batch Mode Adaptive Joins?

Yes, all Batch Mode Adaptive Join plans receive a memory grant. This isn't always the case with Nested Loops join, unless they receive the Nested Loops prefetch optimization. The memory grant is to support a Hash Join should the plan require one based on row thresholds.


SQL server FileTable partitioning I'm a beginner at SQL servers, and would like to ask something to the pros as I'm not 100% sure of the answer. Is it really not possible to partition a FileTable?

No, partitioning is NOT supported for FileTable.

  Partitioning is not supported on FileTables. With the support for multiple FILESTREAM file groups, pure scale-up issues can be handled without having to resort to partitioning in most scenarios (unlike SQL 2008 FILESTREAMs).


Performance of checking for data in related tables NB: this question is purely academic / to help improve my understanding of SQL Server performance.

Given a master table which relates to one or more other tables, how would you determine the best approach to querying that master table for records, which include an indicator to the presence of records in the related tables?

For example, say we have a Person table and wanted to get a list of all people along with an indicator of whether they have children (in this example Person can be reused as the related table):

create table Person
(
    Id bigint not null constraint pk_Person primary key clustered
    , ParentId bigint null constraint fk_Person_Parent foreign key references Person(Id)
    , FirstName nvarchar(256) not null
    , LastName nvarchar(256) not null
)

We could run any of the queries below to check for the presence of related children:

--variables for restricting our result set, just to keep things interesting
declare @LastName nvarchar(256) = 'Be%'
, @FirstName nvarchar(256) = null

example 1

--  fairly straight forward, but requires grouping to account for the
--  potential of a parent having multiple kids (which I don't care about here)
--  which could be adding some inefficiency.
select parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child --1:n
on child.ParentId = parent.Id
where (@LastName is null or parent.LastName like @LastName)
and (@FirstName is null or parent.FirstName like @FirstName)
group by parent.Id, parent.FirstName, parent.LastName --resolve 1:n

example 2

--  avoid the need to group the results by first getting
--  a single child per parent.
--  may be inefficient because we get children for all parents
--  even if we filter for only a few parents.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join --1:? (0 or 1)
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId is not null --not sure if this adds value
) child
on child.ParentId = parent.Id
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
--group by removed since we're 1:?

example 3

--  same as #2 except we limit the child results to those
--  related to the parents we're interested in / having stored
--  them in a CTE to avoid querying for the same parent data
--  in the inner query and outer query.
--  Getting a bit silly now, but could overcome some inefficienies?
;with parentCTE as (
    select Id, FirstName, LastName
    from person
    where (@LastName is null or LastName like @LastName)
    and (@FirstName is null or FirstName like @FirstName)
)
select parentCTE.Id
, parentCTE.FirstName
, parentCTE.LastName
, coalesce(child.hasChildren, 0) HasChildren
from parentCTE
left outer join --1:? (0 or 1)
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId in --reduce the amount of data we return here based on the records we're interested in
    (
        select Id
        from parentCTE
    )
) child
on child.ParentId = parentCTE.Id

example 4

--  back to a simple one; just check for children on our parents
--  but this time having brought back the full parent set.
--  may be inefficient because we're querying the table once per
--  matching parent to check for children.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId = parent.Id),0) HasChildren
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)

I'm after information on how to better understand the trade-offs involved in such situations as opposed to a simple example 3's best. Pointers to articles which may help me understand would also be welcome.

Related SQL Fiddle: http://sqlfiddle.com/#!6/edc17/3

Example 4 has the fewest scans and reads:

Example 1

  SQL Server parse and compile time:
      CPU time = 4 ms, elapsed time = 4 ms.

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

example1    Id    FirstName
-----------------------------
1           2     Aaron
1           3     John
1           8     Aaron
1           9     John
1           14    Aaron
1           15    John
1           20    Aaron
1           21    John

  (8 row(s) affected)

Table 'Person'.
Scan count 9, logical reads 27, physical reads 0,

Rows                 Executes             StmtText
-------------------- -------------------- ------------------------
8                    1                    select 1 example1

, parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child
on child.ParentId = parent.Id
where (@LastName is null or parent.Las 1           1           0
0       0     |--Compute Scalar(DEFINE:([Expr1005]=(1), [Expr1
8       1          |--Nested Loops(Left Outer Join, OUTER REFE
8       1               |--Clustered Index Scan(OBJECT:([sub].
3       8               |--Stream Aggregate(DEFINE:([Expr1004]
7       8                    |--Clustered Index Scan(OBJECT:([

  (6 row(s) affected)

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

Example 2

  SQL Server parse and compile time:
      CPU time = 0 ms, elapsed time = 4 ms.

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

example2  Id   FirstName
----------------------------
2         2    Aaron
2         3    John
2         8    Aaron
2         9    John
2         14   Aaron
2         15   John
2         20   Aaron
2         21   John

  (8 row(s) affected)

Table 'Person'.
Scan count 9, logical reads 27, physical reads 0,

Rows   Executes   StmtText
------ ---------- -----------------------
8      1          select 2 example2

, parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId is not null
) child

 1           1           0           NULL
0     0       |--Compute Scalar(DEFINE:([Expr1007]=(2), [Expr1
8     1            |--Nested Loops(Left Outer Join, OUTER REFE
8     1                 |--Clustered Index Scan(OBJECT:([sub].
3     8                 |--Stream Aggregate(DEFINE:([Expr1006]
0     0                      |--Compute Scalar(DEFINE:([Expr10
7     8                           |--Clustered Index Scan(OBJE

  (7 row(s) affected)

  SQL Server Execution Times:
      CPU time = 0 ms,  elapsed time = 0 ms.

Example 3

SQL Server parse and compile time:
   CPU time = 7 ms, elapsed time = 7 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example3 Id   FirstName
---- ------ ------------------------
3    2      Aaron
3    3      John
3    8      Aaron
3    9      John
3    14     Aaron
3    15     John
3    20     Aaron
3    21     John

(8 row(s) affected)

Table 'Person'. Scan count 9, logical reads 41, physical reads 0, r

Rows     Executes   StmtText
------   ---------  ------------------
8        1          with parentCTE as (
    select Id, FirstName, LastName
    from person
    where (@LastName is null or LastName like @LastName)
    and (@FirstName is null or FirstName like @FirstName)
)
select 3 example3
, parentCTE.Id
, parentCTE.FirstName
, parentCTE.La 1           1           0           NULL
0     0     |--Compute Scalar(DEFINE:([Expr1011]=(3), [Expr1
8     1          |--Nested Loops(Left Outer Join, OUTER REFE
8     1               |--Clustered Index Scan(OBJECT:([sub].
3     8               |--Stream Aggregate(DEFINE:([Expr1010]
0     0                    |--Compute Scalar(DEFINE:([Expr10
7     8                         |--Nested Loops(Inner Join,
7     8                              |--Clustered Index Scan
7     7                              |--Clustered Index Seek

(9 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Example 4

SQL Server parse and compile time:
   CPU time = 3 ms, elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example4 Id  FirstName
---- ----- -------------
4    2     Aaron
4    3     John
4    8     Aaron
4    9     John
4    14    Aaron
4    15    John
4    20    Aaron
4    21    John

(8 row(s) affected)

Table 'Person'. Scan count 3, logical reads 26, physical reads 0,

Rows     Executes   StmtText
-------- ---------- ---------------------
8        1          select 4 example4
, parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId =
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName  1           1           0           NULL
0       0     |--Compute Scalar(DEFINE:([Expr1002]=(4), [Expr1
8       1          |--Nested Loops(Left Outer Join, PASSTHRU:(
8       1               |--Nested Loops(Left Outer Join, OUTER
8       1               |    |--Clustered Index Scan(OBJECT:([
0       0               |    |--Compute Scalar(DEFINE:([Expr10
3       8               |         |--Top(TOP EXPRESSION:((1)))
3       8               |              |--Clustered Index Scan
0       0               |--Compute Scalar(DEFINE:([Expr1008]=(
3       3                    |--Top(TOP EXPRESSION:((1)))
3       3                         |--Clustered Index Scan(OBJE

(11 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

You want the fewest scans possible.  The more times we have to scan the table, the longer it will take.


Prevent duplicate entries I have log in form with Username and Password.

$con=mysqli_connect(LOGINDETAILS);
/*if (!$con) {
die("Can not connect: " . mysql_error());
}
*/

$Username = $_POST['Username'];

$pswd = $_POST['pswd'];
$pswd = md5($pswd);

$sql="INSERT IGNORE INTO CyberSecurity (pswd, Username)
VALUES('$pswd', '$Username')";

mysqli_query($con,$sql);
echo "The password that you provided has been stored in our database for research purposes.";
mysqli_close($con);
?>

I tried using the INSERT IGNOREbut it stills saves the password even though its the exact same one.

INSERT and INSERT IGNORE add a new row unless the row specifies a duplicate for some PRIMARY or UNIQUE key on the table.  The only difference is whether the "duplicate key" is an error or ignored.

Do you want one row per user?  Or can the user provide many passwords?  In the former case, PRIMARY KEY(username); in the latter, PRIMARY KEY(username, password).

Do you want the user to be able to change his password?  Then consider:

INSERT INTO CyberSecurity (username, password)
    VALUES ('$username', '$password')
    ON DUPLICATE KEY UPDATE password = '$password';

But... In any case escape both the username and password, else a username such as O'Brien will blow up your program.  (And a hacker can quickly take over your machine by quickly discovering you have failed to escape stuff from $_POST.)


Upgrade Oracle APEX 4.0.2 to 5.1.1 error I installed Oracle 11g XE on CentOS 7 and everything went fine.

I wanted to upgrade APEX to the latest version and followed instructions in Upgrading Oracle Application Express within Oracle Database 11g Express Edition (XE). Did everything that said and get following error:

ORA-24344: success with compilation error ...
in create_upgrade_triggers for ...
create or replace trigger wwv_flow_upgrade_trg133
before insert or update or delete on WWV_PURGE_WORKSPACE_RESPONSES begin

This is

Bug 25560625 : ORA-24344 ERRORS IN INSTALL.LOG THROUGH UPGRADE FROM APEX 4.0.2 TO 5.1

Still under investigation.

You could upgrade to 4.1 and then to 5.1 to avoid this bug, or you could also try 5.0.


How to secure SQL Server being accessed from the DMZ We have an internal stand alone SQL Server 2014 default instance that needs to be accessed from an application server residing in a DMZ.   The application team wants to open the default 1433 port so connections can be made to the SQL Server.

Is there a best practice for securing the SQL instance when it is going to be accessed from an application server within a DMZ?

For best practices on SQL Server security there are hundreds of pages of documentation from the DoD ("Security Installation Checklist" and "Security Checklist") and CIS ("SQL Server Security Benchmark").

This aside a more realistic approach is rooted in common sense.

Your firewall should only allow access from the specific IPs in the DMZ only directly to the IP of the database in your main domain. This is to reduce the attack surface.
The logins being used to access the new data should be specific to this purpose, and only have the permissions required; i.e. read or write (not necessarily both).
And as your server is used for other things you'd need to review its existing security setup.

What existing logins are there that may be able to access the data? For example sysadmin role users, users that can restore backups, users that are in msdb agent roles which can often be used to escalate privileges.
What administrator users are on the server itself? Anyone who has this will be able to access SQL Server and they can't be stopped, only slowed.
Is it patched (Windows and SQL)?

And what is in the data? Is it private? Could it provide insiders/competitors with financial advantage, or could an attacker use it to further penetrate a network? Those answers determine what other functionality you may need to put in place to safeguard it, and may require nothing special, or may require AlwaysEncrypted or encryption at rest for the disk files and backups.

But primarily once this is all together you put this in a document/email and get someone of appropriate authority to accept the risk in order to protect yourself. You are unlikely to be able to stop all risks, only reduce attack surface and add layers to the security onion.


Drillthrough action on semi-additive measure group I have created a semi-additive (Last Non-empty) measure in a cube that I am developing.  For that measure group I have created a drillthrough action.  When I use that drillthrough action it is returning not just one row for each item as I would expect, but one row for each item for each date, as though it is not recognizing that the measure is semi-additive.

The same issue is mentioned in a couple of Microsoft Connect articles from 2009 here and here, but there is no comment on how to resolve the issue, and I've found very little else on this problem.

I'm using SQL Server 2012 Enterprise.

Is there a solution or a work-around for this problem?

Assuming it is SSAS Multidimensional you can deploy ASSP and build a rowset action which returns the right rows. The rowset action expression would be something like:

ASSP.GetDefaultDrillthroughMDX(Tail(NonEmpty(Existing [Date].[Date].[Date].Members), 1).Item(0))

To explain that code, we get the existing dates (the dates in context of the current cell) then we filter down to only dates with a non empty value for the current measure (inheriting all the other filters on the current cell) then find the most recent date.

More info on this stored procedure can be found here:
http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

Note a rowset action can't be the double click drillthrough action in Excel unfortunately. You will have to expose your action when users right click on the cell and choose Additional Actions.

Excel has a double click behavior. Double click performs a drillthrough returning either the default columns or returning the columns you define in the drillthrough action you define for that measure group as default. If I execute a DRILLTHROUGH SELECT statement manually without specifying the columns I want then I will get back the columns defined in the drillthrough action flagged as the default


pg_ctl says "no server running" when I'm connected to postgres and running queries EDIT: the original question can be found below. Basically, this is a misconception on my part of how pg_ctl works and if you're getting this message too (from pg_ctl), then the TLDR; is, as @jjanes explains in the accepted answer:

pg_ctl doesn't track your database. It interacts with a specific directory, either specified by -D, or through the PGDATA env variable.

So the postgres you're connected to must have been started somehow, maybe not by you, but was given a PGDATA directory to work with.
pg_ctl is not your interface to multiple running postgres instances.

(original question):
I created a directory in postgres user's home dir called mydb: /var/lib/postgresql/mydb
I did this using initdb -D mydb.
Now, I want to start postgres and use mydb as the location where all this db's data is stored; so I run:
pg_ctl -D /var/lib/postgresql/mydb start

It starts successfully, but if I try:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/12/main
(1 row)

i.e. it's using the "main" or default data directory, completely ignoring the -D argument.
So now it occurs to me that I might be connecting to a different db... even though running pg_ctl status before pg_ctl start gives me:
pg_ctl: no server running

However, this has to be the case because... with "no server running", I am able to psql and query the main database.
So then, how do I get the main database "tracked" by pg_ctl? Also, how do I use psql to connect to the right database?
Thank you
Edit: was able to connect to the postgres started with pg_ctl using:
psql -h /var/run/postgresql

(even though I don't see any changes to that directory's contents before / after I pg_ctl start i.e. it remains: 12-main.pg_stat_tmp  12-main.pid).
Edit: Also I can see that the postgres process not being tracked by pg_ctl was started using postgres executable (from running pstree -a):
postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

Probably from a startup file. I would imagine I need to find this startup file and replace use of postgres with pg_ctl...

pg_ctl doesn't track your database.  It interacts with a specific directory, either specified by -D, or through the PGDATA env variable.  If neither are set, then it throws an error.
$ unset PGDATA
$ pg_ctl status
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

If your database truly did successfully start up, then it either must be configured to run on a non-default port, or it must be configured not to use a port at all but only a unix domain socket.  Neither of these configurations will be set up for you automatically by initdb, so I think you may be mistake that it started up in the first place.  Or maybe you did something beyond what you have described to us.
The Unix domain socket is named with a leading ., which makes it a hidden file.  You will only see it in /var/run/postgresql with ls if you give the -a option to it.
What pg_ctl start does behind the scenes is call postgres, so the database is always started via postgres.  It might be directly, or indirectly, or even more indirectly, but eventually that is how it is started.
It is not clear what it is you want to do.  If you want to run out of your own directory, then you probably want to disable the start up script that runs the other one automatically.  On the other hand, why not just use the one that is already configured and managed for you by the packaging system?  You can have both running side by side, buy why?


Restoring a MS SQL Server database on Linux I am running Manjaro Linux and I am trying to restore a Microsoft SQL Server database from a .bak file.

I am trying to follow the official documentation, but it doesn't seem to work!

➜  ~ sudo sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [bejebeje] FROM DISK = N'/home/jwan/Dropbox/projects/personal/bejebeje/backups/2018-04-13/BejebejeDb2018-04-13.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS =5"
[sudo] password for jwan:
Password:
Msg 3201, Level 16, State 2, Server jwan-pc, Line 1
Cannot open backup device '/home/jwan/Dropbox/projects/personal/bejebeje/backups/2018-04-13/BejebejeDb2018-04-13.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server jwan-pc, Line 1
RESTORE DATABASE is terminating abnormally.

I also tried without the sudo and I got the same thing. What am I doing wrong?

UPDATE

I tried the suggestion in the comments, and it still didn't work, here's what I got:

➜  ~ sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [bejebeje] FROM DISK = N'/home/jwan/sqlbackup/BejebejeDb2018-04-13.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS =5"
Password:
Msg 3201, Level 16, State 2, Server jwan-pc, Line 1
Cannot open backup device '/home/jwan/sqlbackup/BejebejeDb2018-04-13.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server jwan-pc, Line 1
RESTORE DATABASE is terminating abnormally.
➜  ~ ls -l sqlbackup
total 5720
-rwxrwxrwx 1 jwan jwan 5854720 Apr 15  2018 BejebejeDb2018-04-13.bak
➜  ~

Try to edit the script (/opt/mssql/lib/mssql-conf/mssql-conf.py) using text editor, It is line 1: #!/usr/bin/python22, which may be wrong.

I changed it #!/usr/bin/python2
Plz try this


Is it safe for me to switch from an 8GB server to a 4GB server? (Stats inside) We are looking to cut some of our server costs and the only place I think it might be possible is our db server.

We're currently running an 8GB rackspace cloud that houses a mysql instance and I believe is running a sphinx searchd instance as well.

Here are the stats from free -m:

       total       used       free     shared    buffers     cached
Mem:    7971       4748       3223          0        773       2773
-/+ buffers/cache: 1201       6769
Swap:  16378        467      15911

Here are the stats from top:

top - 15:23:52 up 349 days, 19:28,  1 user,  load average: 0.39, 0.72, 0.92
Tasks: 141 total,   1 running, 139 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.5%us,  0.1%sy,  0.0%ni, 98.1%id,  1.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8162332k total,  4861112k used,  3301220k free,   791832k buffers
Swap: 16771852k total,   478512k used, 16293340k free,  2839936k cached

It looks like were using just about half of our available 8Gigs but it would save us almost $200/mo if we could downgrade to a 4GB instance.

Any advice on this situation is much appreciated.

Thank You

It's possible. You will potentially lose capacity to serve all data requests from in memory buffers but that depends on how large your data is.

select
ROUND(SUM(data_length+index_length/pow(1024,2))) as TotalSizeMB,
SUM(table_rows),
engine
from information_schema.tables
where table_schema not in ('mysql','information_schema')
group by engine

to see your distribution per engine. You'll be able to judge whether all data and indexes fit inside the innodb bufferpool or indexes within the myisam key buffer. Assuming you set these buffers up yourself in the my.cnf config file and you're not running with the defaults.


How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance:
create or replace procedure first_test(i_tx varchar2, o_count out number) is

qry varchar2(1000);
begin
    qry := 'select count(*) from z_test_a where c_num = :1';

    execute immediate qry
    into o_count
    using i_tx;

   dbms_output.put_line(qry);
end;

what I want to know is that , how I can see the exact qry after using i_tx. What I mean is that if the input parameter is i_tx = 2 how I can see this select count(*) from z_test_a where c_num = 2 in the output section of the procedure so that I can debug the code in case of error? Is this even possible to see the exact query? In this procedure , what I'm getting right now in the output page is :select count(*) from z_test_a where c_num = :1.
Thanks in advance

Would REPLACE do any good? See line #6 and result of DBMS_OUTPUT.PUT_LINE.
SQL> create or replace procedure first_test(i_tx varchar2, o_count out number) is
  2    qry varchar2(1000);
  3  begin
  4      qry := 'select count(*) from z_test_a where c_num = :1';
  5
  6      dbms_output.put_line(replace(qry, ':1', i_tx));    --> like this
  7
  8      execute immediate qry into o_count using i_tx;
  9  end;
 10  /

Procedure created.

SQL> declare
  2    l_cnt number;
  3  begin
  4    first_test(1, l_cnt);
  5  end;
  6  /
select count(*) from z_test_a where c_num = 1               --> result you want, I guess

PL/SQL procedure successfully completed.

SQL>


How does an encrypted DynamoDB (the same question applies to RDS as well) query work? I am trying to understand whether the primary key is encrypted when I choose to encrypt at rest for AWS DynamoDB

If the primary key is encrypted, how does a primary-key lookup get
performed under the hood?

If the primary key is not encrypted, then I know how DynamoDB is
implemented under the hood, but it seems that it's not entirely safe
for some use cases. Any comment on this?

Furthermore, if I choose to have a Global Secondary Index for my DynamoDB table, are the primary key of the Global Secondary Index table encrypted?

If not encrypted, I kinda understand how it works under the hood, but certainly some of my data get revealed in plain text;

If encrypted, I'd appreciate understanding how it works.

For DynamoDB encryption at rest, it means the table is encrypted on disk, not each of the attributes of the items in the table. This is true for any database talking about encryption at rest. That at rest means on disk. Now if you were to implement application data level encryption on your own, then what you are talking about would come into play.

One thing to know is that as of a few weeks ago, all DynamoDB tables are encrypted at rest by default.


Generate script for all stored procedures in Azure SQL database I'm trying to create a SQL script that will create all the stored procedures that were exported from another database.
Basically navigating in SSMS to the database > Generate Scripts > select stored procedures > ..
I found this query that lists all stored procedures CREATE statements, but the question is how to get all of these rows (result) in a single query/file?
select  mod.definition
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', '
             from sys.parameters p
             where p.object_id = obj.object_id
                   and p.parameter_id != 0
             for xml path ('') ) par (parameters)
where obj.type in ('P', 'X')

I tried via a PowerShell script, but the commands are cut short...
The PowerShell script:
Param
(
    [Parameter(Mandatory=$false)][string]$SqlServer = "sql_server_name",
    [Parameter(Mandatory=$False)][string]$SqlDatabase = "db_name",
    [Parameter(Mandatory=$false)][string]$destination = "C:\Users\userx\Desktop\",
    [Parameter(Mandatory=$false)][string]$SqlUser = "sqluser",
    [Parameter(Mandatory=$false)][string]$SqlPassword = "password"
)

function Invoke-Sql
{
    $ErrorActionPreference = "Stop"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$($SqlServer); Database=$($SqlDatabase); User ID=$($SqlUser); Password=$($SqlPassword)"

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = Get-Content "$PSScriptRoot\Get_Create_Statement_All_Stored_Procedures.sql" | Out-String
    $sqlCmd.Connection = $sqlConnection

    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $dataTable = New-Object System.Data.DataTable
    try
    {
        $sqlConnection.Open()
        $sqlOutput = $sqlAdapter.Fill($dataTable)
        Write-Output -Verbose $sqlOutput
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
    catch
    {
        Write-Output -Verbose "Error executing SQL on database [$SqlDatabase] on server [$SqlServer]."
        return $null
    }

    if ($dataTable) { return ,$dataTable } else { return $null }
}

cls

cd $destination

Invoke-Sql | Out-File -FilePath $destination\Create_All_Stored_Procedures.sql

PS. This concerns an Azure SQL database hosted in a Microsoft managed subscription. It seems that using SMO doesn't work on the database as the $smoServer.Databases attribute is empty.

The solution can be found below:

https://www.geekshangout.com/powershell-an-example-of-how-to-save-a-queries-results-to-a-csv-file/
https://www.reddit.com/r/PowerShell/comments/530jhx/csv_file_to_txt_file/

First save as .csv and later convert to .txt or .sql


SELECT [Employee]
      ,[Year]
      ,[Period]
      ,[Activity]
      ,Sum([Day1]) as Day1
      ,Sum([Day2]) as Day2
      ,Sum([Day3]) as Day3
      ,Sum([Day4]) as Day4
      ,Sum([Day5]) as Day5
      ,Sum([Day6]) as Day6
      ,Sum([Day7]) as Day7
FROM [HoursAccounting]
where Employee = '1234'
group by Employee, [Year], Period, Activity;


Removing a Key From HSTORE in Postgresql At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According to the postgresql docs I should be able to remove a key from an hstore with the following operator

As per the postgres docs
hstore - text  delete key from left operand    'a=>1, b=>2, c=>3'::hstore - 'b'::text

In my code I have

IF item.details ? 'vin' THEN
            item.details::hstore - 'vin'::TEXT;
        END IF;

When I go to create the function I get the syntax error

'syntax error at or near "::"'

When I remove the type declarations from the end of the values I get the error

'syntax error at or near "-"'

As far as I can see this conforms to how the postgres docs say the operator should work at http://www.postgresql.org/docs/9.3/static/hstore.html . Is there something I am missing?

You're trying to use the expression item.details::hstore - 'vin'::TEXT as a PL/PgSQL statement.

Where's the result supposed to go?

At a guess, I think what you intended to write is:

item.details := item.details - 'vin'::TEXT;

i.e. "set item.details to the old value of item.details with the key vin removed".

It's kind of hard to be sure without more context.


How long to learn PostgreSQL? I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question.

I used MySQL before and knew relational database. However, I would like to use PostgreSQL the implement my web back end database management system. I want sufficient knowledge to do it, design not ugly structure, write not ugly code, like I used MySQL for another web app project before.

I am not asking the specific time, but at least I want a sketch in my mind. How much effort I need to invest? What kind of problem I would have? Any lessons you learned can be shared?

Thank you very much!

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped me.

I look at Postgres not as a product to be mastered such as a new IDE or email client app. I see it more as a computer system unto itself, to be explored bit by bit. Some areas I may never touch (PostGIS, geo data), while others are vital to me (UUID data type, XML data type, date-time data types).

➥ So I see "learning Postgres" not as a goal to be accomplished but as an on-going process.

The more I learn about Postgres, the more impressed and awed I become. It took some investment of time and effort, but now I ♥ 🐘 (the Blue Elephant).

Tips

Smart Defaults

When in doubt, go with Postgres’ defaults.

UTF-8

One exception to the 'smart defaults' tip: Almost always best to specify UTF-8 as the character encoding. Do so when installing Postgres, as this defines the default when you later create databases. When you do create databases, verify that UTF-8 is the encoding is in use.

Security

Postgres is designed for security as a priority. By default you can only connect from a process/app running locally on the same machine. You must change the security configuration to connect from across a local network or internet.

Installers

To get started you must install Postgres. The main 3 ways to do that:

Compile from source.
Download easy-to-use installers built and provided by the EnterpriseDB as a courtesy to the community. They provide installers for their commercial enhanced version of Postgres as well as plain-vanilla free-of-cost Postgres for Mac OS X, Linux, and Windows.
For Mac OS X users, Postgres.app is an alternative, a double-clickable Mac app.

On some hosted servers such as Heroku or Amazon, the host may offer to spin up Postgres on your behalf. So no installation required in that case.

Regarding uninstalling:

Uninstall Postgres 10 on macOS – EnterpriseDB installer

Client-Server

Postgres is a black-box, hiding your data inside. To access, you must use a utility tool to make a connection to the Postgres daemon process. For getting started, use either of these tools usually included with your Postgres installation.

pgAdmin for GUI app access
psql for command-line access

Virtual Machines

When playing around or experimenting with Postgres, I suggest using a virtual machine so you can later throw it away. Postgres is a pretty heavy installation, creating a new Unix-level user. Entirely removing Postgres can be a bit tricky.

Local

I use Parallels 9, 10, and 11 for virtual machines running Mac OS X Mountain Lion or El Capitan to install various versions of Postgres 9.*. On a Mac, besides Parallels, you might be able to use VMware Fusion or Oracle VirtualBox.

Cloud

Another route is a cloud server. Renting by the hour can be quite inexpensive, perhaps in the range of a penny (USD $0.01) or nickel or dime an hour. You should be able to delete your virtual machine when done or when you want to start from scratch with a fresh one. Examples include DigitalOcean, Heroku, and Amazon.

In-Person Resources

PUGs

Find a PUG. Not a dog, a Postgres User Group. Search MeetUp.com and search this list.

Attend every meeting. Let discussions wash over you even when you cannot understand it all. Postgres has many features, add-ons, related products. Postgres has much depth. So don't expect to master Postgres, just keep looking for ways to use it.

If you cannot find one near you, start one.

Experts

Learn the names of some of the Postgres experts who are generous about sharing their knowledge. Pay special attention when they speak. For example, Craig Ringer on this page. Other examples, Josh Berkus, Bruce Momjian, David E. Wheeler, Selena Deckelmann.

Events

pgDay

You can find one-day "pgDay" events as well as multi-day Postgres conferences all over the world. For example, France, Prague, India, Finland, and Singapore. See this list.

Multi-Day Conferences

In North America the multi-day conferences include:

PGCon in Ottawa.
pgOpen in Chicago.

Other Events

You can often find a Postgres booth at other geek events as well.

For example, visit me while volunteering at the SeaPUG booth next April at LinuxFest Northwest 2016 in Bellingham WA, USA (outside Seattle). Postgres consulting companies have had booths in the past.

Online Resources

Postgres Weekly

Read Postgres Weekly, a collection of select Postgres-related articles and news. Curated by an expert, Craig Kerstiens. Published weekly, thus the name.

Mailing Lists

Subscribe to the Postgres mailing lists.

I find it enlightening to skim through the weekly PostgreSQL Weekly News email delineating that week's work including a list of patches submitted. Posted on the Announce list. All the nitty-gritty details are beyond my comprehension, but nevertheless I become more and more familiar. I learn a bit about the many different features inh Postgres. And I learn how seriously the team takes their work, with an emphasis on solid quality.

Planet Postgres, blog portal

Planet Postgres is a consolidator of Postgres-related blogs.


How do I check that the database master key encryption is valid? I have a scenario where I'm restoring a db from one server to another. On the source server, the database master key (DMK) is encrypted with both a password and the service master key (SMK). When I go to restore it to the new server, the row in sys.key_encryptions still says that it's encrypted by the SMK. This isn't true though since the SMKs don't match between the two servers. Is there any programmatic way to verify that the DMK is indeed encrypted with this server's SMK?

In order to programmatically determine if the current SMK was used to protect the DMK, you should be able to simply attempt an operation that would require the DMK. Such an operation would need to first decrypt the DMK in order to use it. Assuming that you have not opened the DMK explicitly (using the password supplied when creating it), decrypting the DMK will require the SMK. If the current SMK is not the correct SMK, then the DMK won't be automatically decrypted and the operation will fail. So:

If you have a Certificate that is guaranteed to exist in the Database being restored, try using it:

SELECT SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' );

That should return a non-NULL VARBINARY value. If the return value is NULL, then the DMK needs to be regenerated (per the instructions below).
If no Certificate is guaranteed to exist in the Database being restored, then try to create one. If the SMK can be used to automatically decrypt the DMK, then the Certificate will be created, else the operation will fail:

BEGIN TRY
  CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
  DROP CERTIFICATE [TestCert];
  PRINT 'All good, yo!';
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;

However, since you just restored a database coming from another instance and did not restore that other instance's SMK into the new instance, it is safe to assume that the answer is: "no, the DMK is not encrypted with this server's SMK."

This is an expected scenario that requires the following steps to remedy:

USE [newly_restored_db];
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;

The MSDN page for CREATE MASTER KEY states (emphasis added):

  For SQL Server and Parallel Data Warehouse, the Master Key is typically protected by the Service Master Key and at least one password. In case of the database being physically moved to a different server (log shipping, restoring backup, etc.), the database will contain a copy of the master Key encrypted by the original server Service Master Key (unless this encryption was explicitly removed using ALTER MASTER KEY DDL), and a copy of it encrypted by each password specified during either CREATE MASTER KEY or subsequent ALTER MASTER KEY DDL operations. In order to recover the Master Key, and all the data encrypted using the Master Key as the root in the key hierarchy after the database has been moved, the user will have [to] either use [the] OPEN MASTER KEY statement using one of the password[s] used to protect the Master Key, restore a backup of the Master Key, or restore a backup of the original Service Master Key on the new server.

The MSDN page for OPEN MASTER KEY states:

  When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

The MSDN page for ALTER MASTER KEY states:

  The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.


Why does the Tuple Mover in C-Store consider only rows older than LWM? In the Tuple Mover section of the C-Store paper by Michael Stonebraker (link: http://db.csail.mit.edu/projects/cstore/vldb.pdf) the following is described:

  MOP (merge out process) will find all records in the chosen WS segment with an insertion time at or before the LWM (low water mark; a timestamp order/epoch value) [...] The most recent insertion time of a record in RS’ becomes the segment’s new t_lastmove and is always less than or equal to the LWM. [...] Hence, LWM “chases” HWM (high water mark), and the delta between them is chosen to mediate between the needs of users who want historical access and the WS space constraints.

I could not understand, when moving records from WS (write optimized storage) to RS (read optimized storage), why the tuple mover considers only records older than LWM? Doesn't this mean that all the rows that were inserted in the system after LWM would only be in WS? In a system, with small LWM, i.e, in a system where old historical queries are supported, this may mean that much of the records would be in WS only and we would miss out all the optimizations provided by the read optimized storage.

Am I missing something?

Given that the referenced paper is 10 years old, I would recommend looking at a  The Vertica Analytic Database: C-Store 7 Years Later since Vertica has more automatic epoch advancement mechanisms.

For reference, the acronyms used now are:

WOS - Write Optimized Store
ROS - Read Optimized Store
AHM - Ancient History Marker (Low Water Mark)
LGE - Last Good Epoch

A quick overview of how epoch's work in Vertica:

  I could not understand, when moving records from WS (write optimized storage) to RS (read optimized storage), why the tuple mover considers only records older than LWM?

Vertica will automatically advance the epoch as a background process. In the example below, once data is committed, it will belong to the current epoch.

-- Get the current epoch
dbadmin=> SELECT CURRENT_EPOCH FROM system;
 CURRENT_EPOCH
---------------
           238
(1 row)

-- Insert a row into the table without committing (WOS)
dbadmin=> INSERT INTO tbl (a) VALUES (1);
 OUTPUT
--------
      1
(1 row)

-- Get the epoch for the row
dbadmin=> SELECT a, epoch FROM tbl;
 a | epoch
---+-------
 1 |
(1 row)

-- Commit the insert
dbadmin=> COMMIT;
COMMIT

-- Get the epoch for the row
dbadmin=> SELECT a, epoch FROM tbl;
 a | epoch
---+-------
 1 |   238
(1 row)

  Doesn't this mean that all the rows that were inserted in the system after LWM would only be in WS?

It does not. WOS is just a temporary storage location until the data gets moved to ROS. The epoch is just a way to manage transactions.


SQL Server - Benefits of splitting databases across different logical drives We're about to start a project to migrate a large DWH to new physical servers in a new data centre. The current server spec is SQL Server Enterprise 2016 SP2 running on Windows 2012 R2. The new servers will be MSSQL 2019 Enterprise running on Windows 2019.
SAN storage for the current and new servers is an all flash storage array. In the current environment as well as separating data and log files onto different logical drives, different databases (data files only) are also split across different logical drives.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files for staging databases
Logical Drive 3 - data files for user facing databases
Logical Drive 4 - data files for support databases (ReportServer, MDS database)

As part of the server migration I am considering combining all data files onto a single logical drive.

Local SSD - TempDb
Logical Drive 1 - log files
Logical Drive 2 - data files

As well as database file management is there any performance benefits to keep the data files split across different logical drives? Does multiple logical drives give better IO, even though ultimately it's the same physical storage array?

is there any performance benefits to keep the data files split across different logical drives?

If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes.  For instance, they can be hosted across separate SAN controllers, have different caching policies, be separately monitored, etc, even if the LUNs share the same underlying storage.
If each volume maps to a separate set of physical disks, then splitting them up is very costly, as you can't pool and share IO resources between the volumes.  And as most database file IO is background IO, you should normally pool all the IO resources for all database files together to maximize efficiency, sharing, and per-database peak-IO throughput.
So, it depends, and you need to work with your SAN experts to choose, and to appropriately configure the SAN and the server (eg IO Queue Depth).


How to set Innodb Buffer Pool Size I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user.

But no matter what I do it won't change. I have added a value of 1G both in /etc/my.cnf
and also in /etc/mysql/my.cnf and restarted the mysql service. It doesn't seem to update.

My mysql keeps on stopping and when i run mysqltuner & tuning-primer.sh all point to the fact I have to increase the innodb_buffer_pool_size but it simply won't increase.

Is there any other way to enforce this?

Adding the values to a new my.cnf file inside, /etc/mysql/conf.d solved the issue. Added only the value we wanted to override. For some reason, values added elsewhere were ignored by mysql.

Note:
Make sure /etc/mysql/conf.d is included in my.cnf file located at /etc/mysql/my.cnf.
It included by default.


MongoDB not using all memory in Windows server I have a stand-alone MongoDB instance running in windows. It's recently experiencing slowness and my troubleshooting has found the following symptoms.

The slowness normally occurs after a full text search query against a huge collection. I guess this is because it swaps many pages in the collection from disk into memory hence data from other collections are squeezed out of memory.
The server memory usage is capped around 55%. The server (Win2012) has 32GB of memory but MongoDB is not trying to utilize beyond 16GB.

I have seen a couple of Q&A in limiting MongoDB memory usage but not on how to encourage MongoDB to be more greedy.

Would anyone has experience with similar issue?

MongoDB: 3.2.0
Storage Engine: wiredTiger
OS: Win2012
Memory: 32GB

Thanks

Figured out why, here is the explanation.

wiredTiger has a default cache size limit of 60%RAM - 1GB, should be overwritten in a system with large memory.


Can I install SQL Server 2012 Express edition on a same machine with 2012 SQL Server Standard version As I would like to say that we are going to downgrade the SQL Server Standard 2012 SP1 edition with SQL Server Express 2012 SP4 in one Production Server.

For the time being I want to keep the SQL Server Standard 2012 SP1 in that server and I want to install the SQL Server Express 2012 SP4. Until unless the SQL Server Express 2012 SP4 will be not come to production. Till time I want to keep the SQL Server 2012 Standard SP1.

  My Question is that at the same time can I install the SQL Server
  Express 2012 SP4 along with the SQL Server Standard 2012 SP1 in the
  same Server.

Note : Suppose that as the Running SQL Server 2012 Standard SP1 instance name is ABC. Can I install the same instance name (ABC) of SQL Server 2012 Express SP4 or it will be different.

Suppose that as the Running SQL Server 2012 Standard SP1 instance name
  is ABC. Can I install the same instance name (ABC) of SQL Server 2012
  Express SP1 or it will be different.

You cannot have two named instances of SQL Server with the same name on the same pc.

So your options are

first uninstall your first instance and then install the second and
use the old name for newly installed second instance
give the second instance different name, but you can then alias it
with the first server's name once you uninstalled the first server

Here you can find information about aliases


Drive Configuration In Virtual Environment My company is getting some new VM (VMWare) servers provisioned for purposes of installing SQL 2014 or 2016 (version to be determined) with AlwaysOn Availability Groups.  The server and storage provisioning is being done by managed services within the data center.

As a DBA with the company, I recommended our standard drive layout (OS, App, SqlData, SqlLog, TempDB, and SQLBackup), so six drives per server.

The Virtualization Lead with the data center is recommending just two drives per server.  One for OS/Apps, and the other for SQL.  His exact response was "With an aag [AlwaysOn Availability Groups] and modern storage, we can typically flatten it out to an OS and a SQL disk.  The era of separating disk groups to alter performance  is behind us with object based arrays on the back end".

To me, this configuration seems counter-intuitive to everything I've been taught and experienced.

My question is this:  Are modern storage arrays (I have no insight into what the actual infrastructure is, other than it's supposed to be "state of the art") getting that good that we don't need to manage for splitting up IO for data, logs, and TempDB?

I won't be managing the servers, nor even doing the SQL configuration on them, so I don't have much skin in the game.  I'm more interested in hearing if this is others experience on server drive configurations going forward as we move to better virtualized environments.  I should note that the data going on these servers will not likely have a high amount of IO, so I don't think performance will be a real factor.  The two main priorities are that the system has very high up-time and is very secure.

I think it comes down to the IO subsystem your vendor is using. If it is still spinning disks, then for performance purposes, what you suggested is best practice. However nowadays, with all-flash storage arrays (SSDs), there are no moving parts, thus the IO patterns are not much of a concern anymore.

Bottom line, your vendor should be well-versed with what best practice is for SQL Server install. I suggest asking for supporting documentation to be safe. For example, VMWare has a SQL Server on VMWare Best Practices Guide.pdf which pretty much spells out the above.


Sql server service wont start after disabling TLS 1.0 and SSL 3.0 on windows We have been hardening our servers for some time now and recently we disabled SSL 3.0 because of the poodle attack. When I did this on one of our test servers SQL Server failed to start up after the restart.

I have been able to reproduce this on Windows Server 2012 and Windows 7 by disabling TLS 1.0 and SSL 3.0 through the registry. I am using SQL Server 2012 on the server machine. On my windows 7 machine sql server 2012 and sql server 2005 will not start with those disabled.

These are the event log errors I get:

Application Logs:

  (28/10/2014 8:38:54 AM) SQL Server could not spawn
  FRunCM thread. Check the SQL Server error log and the Windows event
  logs for information about possible related problems. (28/10/2014
  8:38:54 AM) Could not start the network library because of an internal
  error in the network library. To determine the cause, review the
  errors immediately preceding this one in the error log. (28/10/2014
  8:38:54 AM) TDSSNIClient initialization failed with error 0x80090331,
  status code 0x1. (28/10/2014 8:38:54 AM) TDSSNIClient initialization
  failed with error 0x80090331, status code 0x80.

System Logs

  (28/10/2014 8:38:54 AM) The SQL Server (MSSQLSERVER)
  service terminated with service-specific error %%-2146893007.
  (28/10/2014 8:38:54 AM) A fatal error occurred while creating an SSL
  server credential. The internal error state is 10013.

Done anyone know have we can keep SSL 3.0 and TLS 1.0 disabled and get SQLServer server to start?

The issue still remains if TLS 1.0 and SSL 3.0 are disabled.  At the moment I don't see any way around this and maybe Microsoft needs to look into this for the future as TLS 1.0 is likely to be phased out over time.

The reason I had TLS 1.0 disabled was to mitigate the BEAST attack, as I found in some reading last night this was the wrong way to do this.  To properly disable the BEAST attack on a server one should elevate a specific RC4 cipher so it is the one used with TLS 1.0.  Unfortunately this raised another about the fact that the RC4 cipher is also vulnerable but that is another discussion.

I realize that I have not found an answer to the question. But my issue has been solve by keeping TLS 1.0 enabled in the registry.


creating table syntax error I have a problem creating the table. It says unexpected parenthesis at line 3

CREATE DATABASE scl_teachers;

USE scl_teachers;

CREATE TABLE clteacher (
    'id' INT (10) PRIMARY KEY auto_increment,
    'f_name' VARCHAR(255) NOT NULL,
    'mid_initials' (255) IS NULL,
    'l_name' VARCHAR(255) NOT NULL,
    'saturation' VARCHAR(2)
);

You don't use single quotes ' to quote identifiers in MySQL.  You use a backtick ` or you can use nothing at all, if the identifier isn't a reserved word.

'mid_initials' (255) IS NULL,

Should be

`mid_initials` VARCHAR(255) DEFAULT NULL,

...although using a VARCHAR(255) seems unnecessarily large.


Cannot shrink transaction log Bit of a headscratcher here.

I have a db that has less than 1GB of data, but a 40GB log file.
Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately once a week it records new payroll information and then regurgitates this data for reporting purposes.
The database is set to Auto Shrink.

running sp_spaceused  @updateusage = true yields the following information:

database_name   database_size   unallocated space
PayrollImports  39412.06 MB 105.00 MB

reserved    data    index_size  unused
321728 KB   278640 KB   42816 KB    272 KB

running DBCC shrinkfile (N'PayrollImports_log', 1 , notruncate) yields the following:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

...the discrepancy between the UsedPages and the EstimatedPages is mystifying, but I continue with DBCC shrinkfile (N'PayrollImports_log', 1 , truncateonly) and get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Nothing has changed at this point.  The log file is still 40GB.  So I think, maybe I have some open transaction.  Running dbcc opentran should verify:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Crap.  Well maybe my indexes are fragmented.  I will defrag them with sp_msForEachTable 'DBCC indexdefrag([PayrollImports], ''?'')' and try to shrink again:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

Still nothing has changed.  Ok, how about I reindex with sp_msForEachTable 'DBCC dbreindex([?])'?

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

...and now we get:

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

no change.  All right, how about sp_msForEachTable 'ALTER INDEX ALL ON [PayrollImports].[?] REBUILD WITH (FILLFACTOR = 10)'?

Immediately, this fails with:

Cannot find the object "(One of my tables)" because it does not exist or you do not have permissions.

Huh?  It's there, all right.  I do a select top 10 * from (My table) and it comes up empty.  Well, that's not right at all.  This is a lookup table that should have over 200 rows.  Is this a data corruption issue maybe?  I collect the data from my development environment, re-insert it in.

But I am out of ideas.  I cannot shrink this thing.  What else can I try?  Why are my UsedPages so incredibly higher than my EstimatedPages?  What is going on here?

Check your output for DBCC LOGINFO within the context of your database.  This will show you the status of all VLFs within the log file.  You shrink operation will not reduce the log file past the last active one (Status=2).  As VLFs are used in a sequential, round-robin fashion, you will need to run transaction log backups enough times until your active VLFs are at physical point in the file that you want to shrink to.

While you state the database is not very active, transaction log backups once a day seem very infrequent and, depending on the kind of transactions that run against this database, could be contributing to the continued growth of this database.  I would recommend running your log backups at least hourly.  If you don't need this kind of recovery in your database and just need to restore to sometime in the last 24 hours, I suggest you convert the database to SIMPLE mode and do a full backup once a day.

Mike Walsh provides a detailed explanation here.

Your reindexing will do very little to actually shrink or allow the file to shrink and, in fact, may hamper it.  This is because reindexing will create additional transactions in your log that will be active until you run a log backup.

Additionally, some best practices recommendations:

Turn off autoshrink.  This is an incredibly bad practice for a number of reasons.  You are likely creating more problems for yourself by enabling this "feature".
Do not ever use truncate_only.  This basically makes your log useless for purposes of recovery.  You might as well go with SIMPLE mode and full backups at this point.


How to unlock a table in IBM DB2 Data Studio 3.1? I know the process to unlock a table through IBM DB2 Control Center using activity monitor, but I am completely new to IBM DB2 Data Studio 3.1 Administrative Tool. Whenever i am right clicking on that particular table only, and clicking on edit data*(to update the records in the table)*, i get the error com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=-57016,SQLERMC=-7;
How do i unlock it using IBM DB2 Datastudio 3.1?

Three unlock table commands are

    COMMIT
    ROLLBACK
    DISCONNECT


Making a domain conditional on other values in the table How can I define or change a domain based on another attribute value?

F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be > 1000.

I wouldn't try to solve that with a DOMAIN since that cannot reference other columns. Use a CHECK constraint instead:

ALTER TABLE foo ADD CONSTRAINT foo_min_salary
CHECK (salary > 1000 OR hired < '2013-01-01' AND salary > 500);

Define the column NOT NULL in addition to disallow NULL values.
Using the IS0 8601 format for the date, which is valid with any locale and the recommended form.
Note how I inverted your logic to arrive at a simpler form.
I wrote hired < '2013-01-01' on purpose, suspecting that your phrase hired > January 1st, 2013 is off by one.
Be aware of operator precedence. AND binds before OR.

If you are building on a domain that already demands value > 500, you can simplify the CHECK constraint to:

ALTER TABLE foo ADD CONSTRAINT foo_min_salary
CHECK (salary > 1000 OR hired < '2013-01-01');


List Jobs, Schedules, and Next Scheduled Run Datetimes List Jobs, Schedules, and Next Scheduled Run Datetimes

I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes.  However, I don't appear to be succeeding.

sysjobactivity has next_scheduled_run_date, which is what I need, but I cannot figure out to connect it to both the Job and Schedule.

If there's a way other than through sysjobactivity, please guide me there.

SELECT
sj.name AS jobName
, ss.name AS scheduleName
, sja.next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sja.job_id
INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE (sj.name LIKE 'xray%')
AND (sja.next_scheduled_run_date > GETDATE())

I have this script in my toolbox:

    select
       S.name AS JobName,
       SS.name AS ScheduleName,
       CASE(SS.freq_type)
            WHEN 1  THEN 'Once'
            WHEN 4  THEN 'Daily'
            WHEN 8  THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
            WHEN 16 THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' else 'Monthly' end)
            WHEN 32 THEN 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' -- RELATIVE
            WHEN 64 THEN 'SQL Startup'
            WHEN 128 THEN 'SQL Idle'
            ELSE '??'
        END AS Frequency,
       CASE
            WHEN (freq_type = 1)                       then 'One time only'
            WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
            WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
            WHEN (freq_type = 8) then (select 'Weekly Schedule' = MIN(D1+ D2+D3+D4+D5+D6+D7 )
                                        from (select SS.schedule_id,
                                                        freq_interval,
                                                        'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                                                        'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                                                        'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                                                        'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                                                        'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                                                        'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                                    from msdb..sysschedules ss
                                                where freq_type = 8
                                            ) as F
                                        where schedule_id = SJ.schedule_id
                                    )
            WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
            WHEN (freq_type = 32) then (select  freq_rel + WDAY
                                        from (select SS.schedule_id,
                                                        'freq_rel' = CASE(freq_relative_interval)
                                                                    WHEN 1 then 'First'
                                                                    WHEN 2 then 'Second'
                                                                    WHEN 4 then 'Third'
                                                                    WHEN 8 then 'Fourth'
                                                                    WHEN 16 then 'Last'
                                                                    ELSE '??'
                                                                    END,
                                                    'WDAY'     = CASE (freq_interval)
                                                                    WHEN 1 then ' Sun'
                                                                    WHEN 2 then ' Mon'
                                                                    WHEN 3 then ' Tue'
                                                                    WHEN 4 then ' Wed'
                                                                    WHEN 5 then ' Thu'
                                                                    WHEN 6 then ' Fri'
                                                                    WHEN 7 then ' Sat'
                                                                    WHEN 8 then ' Day'
                                                                    WHEN 9 then ' Weekday'
                                                                    WHEN 10 then ' Weekend'
                                                                    ELSE '??'
                                                                    END
                                                from msdb..sysschedules SS
                                                where SS.freq_type = 32
                                                ) as WS
                                        where WS.schedule_id = SS.schedule_id
                                        )
        END AS Interval,
        CASE (freq_subday_type)
            WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(active_start_time)))+ convert(varchar(6),active_start_time),3,0,':')),6,0,':'),8)
            WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
            WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
            WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
            ELSE '??'
        END AS [Time],
        CASE SJ.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
        END AS NextRunTime
from msdb.dbo.sysjobs S
left join msdb.dbo.sysjobschedules SJ on S.job_id = SJ.job_id
left join msdb.dbo.sysschedules SS on SS.schedule_id = SJ.schedule_id
order by S.name


Interview SQL question Given a table 'employees'

 employee_id | salary | department_id
-------------+--------+---------------

Only using SQL find all the variants of employee-transfers from one department to another, so that average salary in both 'departure' and 'arrival' department grew.

PS I was asked the question on a interview, which never gave an answer, and Google is of little help.

So you are looking for Employees that earn below the average in their current department but above the average in their prospective new department.

One possible way of getting all employee transfers that would meet this would be

WITH departments
     AS (SELECT AVG(salary) AS AvgSalary,
                department_id
         FROM   employees
         GROUP  BY department_id)
SELECT e.employee_id,
       dept_current.department_id AS current_department_id,
       dept_new.department_id     AS new_department_id
FROM   employees e
       JOIN departments dept_current
         ON e.department_id = dept_current.department_id
            AND dept_current.AvgSalary > e.salary
       JOIN departments dept_new
         ON dept_new.AvgSalary < e.salary


Create publication is not working in PostgreSQL I am trying to create a publication (for logical replication) in PostgreSQL database but it throws an error:

indsolv_dev=# CREATE PUBLICATION my_publication;
ERROR:  syntax error at or near "PUBLICATION"
LINE 1: CREATE PUBLICATION my_publication;

As JJanes mentioned, Publication is available from Version 10.
I was using version 9.6.


Purchased in Previous 12 Months from Date of Purchase - SQL I'm new, and not even sure how to ask this question, but here goes:

For each customer/product purchase, I need to determine with SQL whether the same customer purchased that product in the 12 months prior to this purchase. So, if purchase is on 9/1/2018, I need to know if the customer purchased the same product in previous 12 months.

I've tried combinations of RANK() OVER(PARTITION BY but haven't really accomplished what I need to.

Edit: adding attempted code below:

select
   s.CUST_ACCT_ID
  ,s.PRODUCT
  ,s.BUS_MO
  ,s2.BUS_MO

from INS_REF001_SALES_SUM_R48 s

left outer join INS_REF001_SALES_SUM_R48 s2
on s.CUST_ACCT_ID = s2.CUST_ACCT_ID
and s.PRODUCT = s2.PRODUCT
and s.BUS_MO < s2.BUS_MO
and s.BUS_MO >= dateadd(mm, datediff(mm, 0, s2.BUS_MO) - 12, 0)

inner join INS_REF006_ACCT a
on a.CUST_ACCT_ID = s.CUST_ACCT_ID
and a.CUST_ACCT_ID = s2.CUST_ACCT_ID

where s.BUS_MO between '11/01/2016' and '10/31/2018'

Thanks!
Scott

You can accomplish this with a self join:

SELECT a.customer_id, a.product_id, a.purchase_date, MAX(b.purchase_date) IS NOT NULL AS previous_12_months
FROM products a
LEFT JOIN products b
  ON a.product_id = b.product_id AND
     a.customer_id = b.customer_id AND
     b.purchase_date < a.purchase_date AND
     b.purchase_date >= a.purchase_date - INTERVAL 12 MONTH
GROUP BY a.customer_id


Identify Query Waiting on resources I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below.

When i checked the status it is suspended.That means it is waiting for some process to release resource.As far as my understanding CXPACKET is the producer and CXCONSUMER is the consumer and CXPACKET is the main culprit and action should be taken on that.But i don't see any CXPACKET.
Since this is a parallel execution i am not sure where i should start fixing.

Can anyone suggest me how to find out the process on which my queries are waiting(because i see suspended as status)?

Additional Info

When i query sys.dm_os_wait_stats ,the top two entries are CONSUMER and CXPACKETS.

SQL Server 2016
128 GB RAM
MAX DOP : 8
Cost of threshold : 20

Check out this post from Erik Darling: CXCONSUMER Is Harmless? Not So Fast, Tiger

That shows a really extreme example of a problem query where CXCONUSMER is the highest wait.  So while the Microsoft recommendations indicate it's harmless, it definitely can be a sign of other problems.

Based on that, and your screenshot, it's quite possible that you're running into issues with skewed parallelism.  You can find some great info about skewed parallelism from Paul Randal: More on CXPACKET Waits: Skewed Parallelism

In theory, waits due to skewed parallelism should be surfaced as CXPACKET waits.  This doesn't appear to always be the case in practice at this time though.

Without the query text, and especially an actual execution plan, we can only guess at what might be causing the CXCONSUMER waits for your scenario.  But hopefully this helps gives you a direction to look in.

If you edit your question to include the execution plan and query text (and relevant table / index definitions), we can be more help in tracking down the root cause.


Is it recommended to have redundant foreign key columns? Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs:

Without redundance

A
|  id | ... |
------------
| ... | ... |

B
|  id | a_id | ... |
--------------------
| ... |  ... | ... |

C
|  id | b_id | ... |
--------------------
| ... |  ... | ... |

D
|  id | c_id | ... |
--------------------
| ... |  ... | ... |

Would be recommended to have more columns in C e D with the reference to A and B?

With redundance

C
|  id | a_id | b_id | ... |
---------------------------
| ... |  ... |  ... | ... |

D
|  id | a_id | b_id | c_id | ... |
----------------------------------
| ... |  ... |  ... |  ... | ... |

It's a redundance, but I usually do this to make simpler queries, I can make less JOINs. I think it probably have better performance too.

Is it recommended? (At least when the columns are immutables) Is there a better solution for this?

I can find no fault with adding columns to tables that meet the requirements of your system.

It may in fact make some queries much faster to have the "redundant" columns present.  Take this example, which is pretty contrived, where you want to get a list of parents, and the names of the schools their children attend.  The first example, where parents are not linked directly to schools:

SELECT p.ParentName
    , s.SchoolName
FROM dbo.Schools s
    INNER JOIN dbo.Children c on s.School_ID = c.School_ID
    INNER JOIN dbo.Parents p ON c.Parent_ID = p.Parent_ID;

Versus the query where Parents are linked to schools, even though the link is "redundant" since each parents child already has the link:

SELECT p.ParentName
    , s.SchoolName
FROM dbo.Schools s
    INNER JOIN dbo.Parents ON s.School_ID = p.School_ID

Clearly, you'd want to do the second variant because (a) it's less complicated to understand the intent, and (b) SQL Server has to do less work, which is always a good thing.


How does a column-oriented DBMS filter on multiple columns? I'm learning how column-oriented DBMS / "columnars" work for OLAP situations.
Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to know the products sold in the shop A in a certain time range:
SELECT DISTINCT product FROM data
WHERE timestamp BETWEEN 1600010000 AND 1602678400
            AND shop = 'A'

This will be stored like this (admittedly, this is more or less an abstraction):
timestamp [1600000000, 1600000005, 1600000005, 1600000017, 1600000018, ... ]
shop      [A, A, B, D, C, ...]
product   [X153, S76D, TYA6, LKJ6, SDH7, ...]

For this query:

I totally get how we can achieve fast lookup by timestamp, since this column is sorted: with 2 dichotomic searches we can find the index for which timestamp=1600010000 and 1602678400. With less than 30 read operations of a few bytes, it's done, we have rowid_start, rowid_end (I don't know if it's still called rowid in the context of a columnar) that make the boundaries of this time-range. The key thing is that we haven't had to read megabytes of data, but just a few bytes.

Question: then, how can a columnar filter by shop = 'A'? Do we have to read each entry of the column shop in the range rowid_start .. rowid_end to test if it's A or not? This could potentially be hundreds of MB or GB of data.

TL;DR: once we have filtered by one column, how can a columnar do a second-column-filtering, without doing a FULL SCAN?

There are a few factors which reduce the horror of a full scan of the shop column.

The values of each column can be stored in the same order: the first value of timestamp corresponds to the first value in shop and to the first in product; the second to the second to the second, and so on. So a fast lookup that gives the offsets for the start and end of the timestamp range also gives the offset of the corresponding values in shop. The search can jump straight to that offset in the list of shop values, if shop codes are fixed length or can be coerced into being fixed length. More on this soon.

For disk-oriented rather than fully in-memory systems, meta data can be held to show which disk files correspond to what offsets for each column. So IO is limited to only the files necessary.

IO is still block-oriented and all the shop codes are contiguous on disk. One (relatively fast) sequential read will return a lot of shop codes into memory.

Those codes can be stored contiguously in memory, which is very pre-fetch- and processor cache-friendly.

Compression. Even if the shop codes are quite long or of variable length, likely there will be relatively few unique values (Walmart has fewer than 12,000 stores). Dictionary compression can be applied, which maps each long string to a much shorter fixed length integer. The mapping table is held once and it is these integers which become the values held in column array. Run-length encoding can further reduce the size of the "shop" array, producing a virtuous feedback with file size, memory and CPU cache utilisation.

The RowID you speak about is a useful visualisation but need not be physically implemented. Since tuples' columns are stored in the same order, the offsets perform the function the RowID would. For multi-predicate queries it is unlikely an actual array of integers representing offsets would be built for each predicate and the intersection of these computed. Rather, each predicate would produce a bitmap and these bitmaps would be AND-ed (or OR-ed) to produce the final predicate. Each bit represents the offset of a tuple that satisfies the predicate.
One of the earliest column stores (C-Store) allowed for redundant storage of column sets, each of which could be sorted differently to facilitate fast predicate lookup. I know of no recent system which implements this, but it's an interesting idea.


SSAS Drillthough Limit There is a limit of 10,000 rows that can be returned for a drillthrough rowset in recent version of SSAS. I'm concerned about the risk of of returning partial results without warning if the selection set contains more data than the 10K limit. Is there some mechanism to either display a warning or return no rows at all in that case?

MDX Drillthrough allows you to specify a limit for the row set returned.  As far as I am aware it doesn't have a 'show how many rows this would produce' facility.  You could fake it by having a count measure for each of your fact tables and selecting this for the slice in question before you execute the drillthrough.

I'm not sure whether Excel 2010 has much in the way of facilities to manage this out of the box.  Back when I were a lad Excel 2000-2003 didn't do drillthrough out of the box so you had to roll your own.  This is an example of a VBA script to do a drillthrough action.  You can hook the menu on the pivot table fairly easily to provide access to this sort of script.  This would at least allow you to provide some sort of validation.

Introspection into the pivot table through the API is possible if a little fiddly.  You can get the dimension and slicer state out.  Back in the SQL Server 2000 days I did a 'drill across' this way that allowed you to open another cube on the same slice (think insurance policies going across to claims).  Now SSAS supports cubes with multiple fact tables (measure groups) this is less useful.

However, if you could intercept the (IIRC) double click event that would kick off a drillthrough and knock that on the head then you could use the introspection to formulate a count query that could guard against over-large drillthroughs.  This would then pop up a dialog box along the lines of 'You're going to drill through into 1,347,961 rows.'


Mysql takes up to 50% cpu after aborting DELETE query I have a problem I have never met before. I was clearing yesterday client's db and table with stats of visits that had 15GB of data. I decided to delete all rows older than 1 Feb, because we have all that data stored in daily stats and there's no need to keep those old single stats.

I tried to run DELETE FROM sv WHERE datetime < ? (index on datetime) and DELETE FROM sv WHERE id < ? (primary index on id), but both queries where too slow. They took more than 15 mins. So I have just aborted those queries by Ctrl+C.

Then I thought a better idea would be copying the table structure and February rows and remove the old table. But today I found out that mysql process on server is running up to 50% cpu usage with normal traffic on our site.

After moving the sv table out of the database folder (MyISAM table) usage is still up to 50%.

First I thought that table must have been corrupted, but phpmyadmin does not show it marked as crashed.

How can I debug this one? What have happened there?

Since sv is a MyISAM table, I would expect a backlog of DB Connections attempting to write to the table in a high-traffic environment.

In fact, any DML (INSERT, UPDATE, DELETE) against the sv table will result in a full table lock on sv.

You should still run a CHECK TABLE sv; as a precaution.

Please keep in mind that data for MyISAM tables are never cached. It is possible that the OS is just swapping used memory as a result of the heavy activity against the MyISAM.

All you can really  do in that instance is reboot the OS


SSRS in database server Is it advised to use SSRS in the database server itself? Will it hamper the performance of the server itself. (in SQL SERVER 2005)
What are the merits and demrits of the same?

When you run SSRS (or any other application for that matter) on the same server as the database engine, you need to be mindful that overall server resources are shared, especially memory and processor.  Make sure the server is sized to accommodate both, and consider configuring SQL Server 'max server memory' so that the memory used by SSRS, the OS, and SQL Server doesn't exceed physical memory.

Running SSRS (and/or SSAS) on the same box may be advantageous from a licensing cost perspective since you will need additional SQL Server licenses to run on a separate physical server.  However, it will only detract from the resources available to the database engine.