Implenting Min Max memory configuration in Alwayson I have never changed this setting for an AlwasyOn setup. What is the best practise to do this? Should I change the setting on the Secondary node first and then the primary? Please suggest . The current setting is set to the default max value and that is something not which I want. Each of the server has 24gb of memory. The total size of the databases on each node is 105gb

Thanks

The current setting is set to the default max value and that is something not which I want.

You should change your max memory setting away from the default. Its a dynamic setting that does not require a restart of sql server service. Bear in mind that running reconfigure it will flush out the plan cache.

Refer to my answer here for more details.

  Each of the server has 24gb of memory. The total size of the databases on each node is 105gb

Start with 21GB as max memory provided you are running only SQL Server (no SSIS, SSRS, SSAS,etc). Leave 3 GB for OS.

Depending on the database activity, the RAM (24GB) seems to be low. You should look out for low memory notifications to see if server is having memory pressure.


Sql Server - Get database constraints of all tables ( default and check constraints) I need to get constraints of all columns, tables in my database result like this :

DatabaseName | SchemaName | TableName | ColumnName | ColumnType | ConstraintName |

-

ConstraintType | Definition | CreateDate

I already used this answer but it shows just TableName, ColumnName, ConstraintName and definition.

Answer that I used:

SELECT
    TableName = t.Name,
    ColumnName = c.Name,
    dc.Name,
    dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
ORDER BY t.Name

Thank you for your help.

Hope this script includes all you are trying to retrieve.

This script retrieving CHECK and DEFAULT constraints. You can use INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE to retrieve PK & FK columns.

    DECLARE @Constraint TABLE (
        [Database] [nvarchar](128) NULL,
        [Schema Name] [nvarchar](64) NULL,
        [Table Name] [sysname] NOT NULL,
        [Column Name] [nvarchar](128) NULL,
        [Data Type] [nvarchar](64) NULL,
        [Constraint Name] [sysname] NOT NULL,
        [Constraint Type] [varchar](7) NOT NULL,
        [definition] [varchar](512) NULL,
        [Create Date] [datetime] NULL
    )

    insert into @Constraint
    exec sp_MSforeachdb 'use ?
    select db_name() [Database],schema_name(t.schema_id) [Schema Name],t.name [Table Name],ac.name [Column Name],dt.name [Data Type],dc.name [Constraint Name],''Default'' [Constraint Type],dc.definition,dc.create_date [Create Date]
    from
                sys.tables t
    inner join  sys.all_columns ac
    on          t.object_id=ac.object_id
    inner join  sys.types dt
    on          ac.user_type_id=dt.user_type_id
    inner join  sys.default_constraints dc
    on          t.object_id=dc.parent_object_id and ac.column_id=dc.parent_column_id
    where t.type=''U''
    union all
    select db_name() [Database],schema_name(t.schema_id) [Schema Name],t.name [Table Name],ac.name [Column Name],dt.name [Data Type],cc.name [Constraint Name],''Default'' [Constraint Type],cc.definition,cc.create_date [Create Date]
    from
                sys.tables t
    inner join  sys.all_columns ac
    on          t.object_id=ac.object_id
    inner join  sys.types dt
    on          ac.user_type_id=dt.user_type_id
    inner join  sys.check_constraints cc
    on          t.object_id=cc.parent_object_id and ac.column_id=cc.parent_column_id
    where t.type=''U'''

    select * from @Constraint

Note: You can filter the required result by using where condition with select * from @Constraint.
Thanks!


SQL 2008 - DBCHECK Failed I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup.

This has been working fine for months. It has started failing. If I run a DBCHECK on the Databases, they are pass fine. I even went into the maintenance plan and clicked 'View SQL' to get the TSQL that is run during the Plan and it again all passed:

USE [ClientPortal]
GO
DBCC CHECKDB(N'ClientPortal')  WITH NO_INFOMSGS
GO
USE [PHJ_Common_Objects]
GO
DBCC CHECKDB(N'PHJ_Common_Objects')  WITH NO_INFOMSGS
GO
USE [PilotWebApp]
GO
DBCC CHECKDB(N'PilotWebApp')  WITH NO_INFOMSGSenter code here

Why would this fail during the plan but work fine when run manually?

The maintenance plan is usually executed with another user account.  Check the properties of SQL Agent.  If the Connection tab specifies Use Windows Authentication, it runs under the same user as the SQL Server Agent service.  You can see that in the Local Services control panel, under the Log On tab.

Once you figure out the user account, try to run the script as that user.


How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL? Given two numbers n and m, I want to generate a series of the form

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

and repeat it m times.

For instance, for n = 3 and m = 4, I want a sequence of the following 24 numbers:

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the generate_series function, and a few tricks to guarantee that the order is the right one:

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0
CROSS JOIN
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

... or use a function for the same purpose, with adjoint and nested loops:

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?

Postgres
You can make it work with a single generate_series() and basic math (see mathematical functions).
Wrapped into a simple SQL function:
CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$  LANGUAGE sql IMMUTABLE;

Call:
SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n*2*m does not overflow int4.
How?
In the subquery:

Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it n2m. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

Take it % n*2 (% is the modulo operator) to get a series of n ascending numbers, m times. I name it n2.

In the outer query:

Add 1 to lower half (n2 < n).

For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not warranted by the SQL standard.

Unfortunately, generate_series() is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:
Standard SQL
You can generate the serial numbers with a recursive CTE instead of generate_series(), or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!
CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

Then, the above SELECT becomes even simpler:
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;


Is Oracle DB licensing needed for a test environment? I'm planning to setup an Oracle Standard Edition instance on a server in our test environment.

Do I need to purchase a license for this?

In general, yes.  Once you have an application in production, every environment (dev, test, staging, production, etc.) needed to support that application needs to be licensed.  As with all licensing advice, however, you need to get it in writing from Oracle Sales.  Advice from some guy on the internet holds up about as well as you might expect if there is ever a question about whether you're properly licensed and/or if you're overpaying for licenses.  And remember that agreements your company has with Oracle, laws in your country, etc. can change this general statement.

If you are a reasonably large customer or if you have some other leverage in the negotiation process, however, you can generally get the licenses for the test and development environments at a good discount from Oracle Sales.


MongoDB can't add new replica set member [rsSync] SEVERE: Got signal: 6 I have a replica set and I added a new member to the set. The initialSync begins for the new  member and rs.status (on primary) shows STARTUP2 as status. However, after a long enough time, there's a cryptic fassert error coming on the new instance.

Log dump is as following:

2014-11-02T22:53:23.995+0000 [clientcursormon] mem (MB) res:330 virt:45842
2014-11-02T22:53:23.995+0000 [clientcursormon]  mapped (incl journal view):45038
2014-11-02T22:53:23.995+0000 [clientcursormon]  connections:27
2014-11-02T22:53:23.995+0000 [clientcursormon]  replication threads:32
2014-11-02T22:53:25.427+0000 [conn2012] end connection xx.xx.xx.xx:1201 (26 connections now open)
2014-11-02T22:53:25.433+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1200 #2014 (27 connections now open)
2014-11-02T22:53:25.436+0000 [conn2014]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:26.775+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1058 #2015 (28 connections now open)
2014-11-02T22:53:26.864+0000 [conn1993] end connection xx.xx.xx.xx:1059 (27 connections now open)
2014-11-02T22:53:29.090+0000 [rsSync] Socket recv() errno:110 Connection timed out xx.xx.xx.xx:27017
2014-11-02T22:53:29.096+0000 [rsSync] SocketException: remote: xx.xx.xx.xx:27017 error: 9001 socket exception [RECV_ERROR] server [168.63.252.61:27017]
2014-11-02T22:53:29.099+0000 [rsSync] DBClientCursor::init call() failed
2014-11-02T22:53:29.307+0000 [rsSync] replSet initial sync exception: 13386 socket error for mapping query 0 attempts remaining
2014-11-02T22:53:36.113+0000 [conn2013] end connection xx.xx.xx.xx:1056 (26 connections now open)
2014-11-02T22:53:36.153+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1137 #2016 (27 connections now open)
2014-11-02T22:53:36.154+0000 [conn2016]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:55.541+0000 [conn2014] end connection xx.xx.xx.xx:1200 (26 connections now open)
2014-11-02T22:53:55.578+0000 [initandlisten] connection accepted from xx.xx.xx.xx:1201 #2017 (27 connections now open)
2014-11-02T22:53:55.580+0000 [conn2017]  authenticate db: local { authenticate: 1, nonce: "xxx", user: "__system", key: "xxx" }
2014-11-02T22:53:56.861+0000 [conn2015]  authenticate db: admin { authenticate: 1, user: "root", nonce: "xxx", key: "xxx" }
2014-11-02T22:53:59.310+0000 [rsSync] Fatal Assertion 16233
2014-11-02T22:53:59.723+0000 [rsSync] 0x11c0e91 0x1163109 0x114576d 0xe84c1f 0xea770e 0xea7800 0xea7af8 0x1205829 0x7ff728cf8e9a 0x7ff72800b3fd
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x11c0e91]
 /usr/bin/mongod(_ZN5mongo10logContextEPKc+0x159) [0x1163109]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xcd) [0x114576d]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xe84c1f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x18e) [0xea770e]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x30) [0xea7800]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0xa8) [0xea7af8]
 /usr/bin/mongod() [0x1205829]
 /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a) [0x7ff728cf8e9a]
 /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7ff72800b3fd]
2014-11-02T22:53:59.723+0000 [rsSync]

***aborting after fassert() failure

2014-11-02T22:53:59.728+0000 [rsSync] SEVERE: Got signal: 6 (Aborted)

.

The worst part is that when I try to re-start the mongod service, the replication begins afresh trying to reSync all the files which are already there. This seems bizarre and useless.

Can someone please help me understand what is going on?

In the dump you have above, I see this error

  SocketException: remote: xx.xx.xx.xx:27017 error: 9001 socket
  exception [RECV_ERROR] server [168.63.252.61:27017]
  2014-11-02T22:53:29.099+0000 [rsSync] DBClientCursor::init call()
  failed 2014-11-02T22:53:29.307+0000 [rsSync] replSet initial sync
  exception: 13386 socket error for mapping query 0 attempts remaining
  2014-11-02T22:53:36.113+0000 [conn2013] end connection xx.xx.xx.xx:

Which indicates that there is a socket exception error. To me that means that the configuration of a replica is incorrect or the host definded in the replica set configuration can't be contacted (firewall, network issues, mongodb not running)

STARTUP2 indicates that threads were forked for replication. While in the STARTUP2 state, the member creates threads to handle internal replication operations, and then the member transitions to the:replstate:RECOVERING state. During initial sync, members stay in:replstate:STARTUP2 until the all data transfers and all indexes finishing building. Members in the STARTUP2 state are not eligible to vote.

Running rs.status() from the primary will show you how the primary sees the secondaries and what address (host/port) it's using to contact them. You can also run rs.status() from a secondary after running the rs.slaveOK() command.

Try connnecting from the secondary to the primary and vis-versa using the mongo client.
Or you can use telnet mongo_host mongo_port_number from either server to see if both are listening to the ports you've defined.


Finding most recent entry per group running very slowly I have a table (notifications) containing about 300k entries.  Each entry relates to a price change on one of about 500 different products (Trigger_ASIN).

I need to frequently pull the most recent notifications entry for each Trigger_ASIN.  (I'll be using the resulting query to look up related details in another table)

My query as it currently stands is:

SELECT n1.Trigger_ASIN, n1.UniqueId
from notifications n1
  left outer join notifications n2 on n1.Trigger_ASIN = n2.Trigger_ASIN
and n1.Trigger_TimeOfOfferChange < n2.Trigger_TimeOfOfferChange
WHERE n2.UniqueId is NULL
limit 20;

When limited to 20 records, it runs in 5secs (0 secs to fetch).
Limited to 40 records it takes 15 secs.
Limited to 80 records it take 125 secs.
By the time I get to my c.500 different products it's so slow it's unusable.

The EXPLAIN output on the query shows that an index is being used (one one side of the join at least).

1   SIMPLE  n1  index       notifications__index_most_recent2   221     302974  Using index
1   SIMPLE  n2  ref ix_notifications_Trigger_ASIN,notifications_Trigger_TimeOfOfferChange_index,notifications__index_most_recent_entry,notifications__index_most_recent2    notifications__index_most_recent_entry  63  amz.n1.Trigger_ASIN 240 Using where; Not exists; Using index

I've looked through a number of related questions, but still haven't managed to find the reason for the query running so slowly.

Can anyone see what I'm doing wrong to cause this to run so slowly?

For reference, the notifications table and associated indices is are described below.

Notifications Table:

    -- auto-generated definition
CREATE TABLE notifications
(
  UniqueId                  VARCHAR(50) NOT NULL
    PRIMARY KEY,
  PublishTime               DATETIME    NULL,
  SellerId                  VARCHAR(30) NULL,
  MarketplaceId             VARCHAR(20) NULL,
  Trigger_MarketplaceId     VARCHAR(20) NULL,
  Trigger_ASIN              VARCHAR(20) NULL,
  Trigger_ItemCondition     VARCHAR(50) NULL,
  Trigger_TimeOfOfferChange DATETIME    NULL
)
  ENGINE = InnoDB
  CHARSET = utf8;

CREATE INDEX ix_notifications_PublishTime
  ON notifications (PublishTime);

CREATE INDEX ix_notifications_SellerId
  ON notifications (SellerId);

CREATE INDEX notifications__index_most_recent_entry
  ON notifications (Trigger_ASIN, Trigger_TimeOfOfferChange, UniqueId, Trigger_ItemCondition);

CREATE INDEX ix_notifications_Trigger_ASIN
  ON notifications (Trigger_ASIN);

CREATE INDEX ix_notifications_Trigger_ItemCondition
  ON notifications (Trigger_ItemCondition);

CREATE INDEX notifications__index_most_recent2
  ON notifications (Trigger_TimeOfOfferChange, Trigger_ASIN, UniqueId);

CREATE INDEX notifications_Trigger_TimeOfOfferChange_index
  ON notifications (Trigger_TimeOfOfferChange);

SELECT n1.Trigger_ASIN
     , n1.UniqueId
FROM notifications n1
JOIN ( SELECT n2.Trigger_ASIN
            , MAX(n2.Trigger_TimeOfOfferChange) Trigger_TimeOfOfferChange
       FROM notifications n2
       GROUP BY n2.Trigger_ASIN ) n3
WHERE n1.Trigger_ASIN = n3.Trigger_ASIN
  AND n1.Trigger_TimeOfOfferChange = n3.Trigger_TimeOfOfferChange
/* ORDER BY something -- obligatorily, limitation does not make sense without it!!! */
LIMIT 20;

And ensure the notifications__index_most_recent_entry index is used.


Insert space in MySQL column value I have a value like "this - is - a -comment". However I need to run an update statement to add a space after the last - (-comment above should be - comment)

Any able to help me with an update statement?

Here is one solution, there may be more efficient ways of doing it but this is one way that avoids more complicated solutions like using regular expressions.

update YourTable SET [ColumnValue] = REPLACE(REPLACE([ColumnValue],'- ', '-'), '-', '- ')

So what this is doing is setting all instances of '- ' to '-' first, in order to make the spacing between hyphens/words consistent throughout your whole string. It then takes all instances of '-' and transfers them to '- '.

The reason behind doing this double replace is to avoid adding extra spaces between the hyphen and the word.

If you just did:

update YourTable SET [ColumnValue] = REPLACE([ColumnValue], '-', '- ')

You would end up with multiple spaces between the hyphen and the words (in the case of "- is" and "- a").


Loading sql server 2014 Trying to load SQL Server and SSDT-BI from Visual Studio.  Got the below error when loading SQL Server 2014 and apparently it caused the Reporting Services-Native to fail to load.

I didn't take a screen shot of the actual error but the screen looked just like the second image except the file was in the windows/temp folder as stated on the below text.

Feature:                       Reporting Services - Native
  Status:                        Failed: see logs for details
  Reason for failure:            An error occurred during the setup process of the feature.
  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:                SQL Server Reporting Services
  Component error code:          0x84CF0003
  Error description:             Updating permission setting for folder 'C:\Windows\Temp' failed. The folder permission setting were supposed to be set to 'D:(A;OICI;0x1200af;;;S-1-5-80-425977601-1203083412-1631309457-2457533047-3321749933)'.

Do I have to get a 'succeeded' status for Report Services before I can load and use SSDT-BI/Visual Studio?  Suggestions on how to get Reporting Services to load successfully would be greatly appreciated.

BI Tools for SSDT is a standalone install meant for development and testing.

You can preview (render) SSRS reports within SSDT without having SSRS installed on your dev machine. This is for development only and will only be visible in SSDT. On a side note SSIS packages will function in the same manner. SSAS has no preview option - it requires a functional/full SSAS service available for deployment.

To deploy the reports you will obviously need a functional SSRS service running on the target server.


What are thresholds in SQL Server 2014 "big table join to small table" cardinality estimation optimization? The SQL Server 2014 Cardinality Estimator white paper says:

  The new CE, however, uses a simpler algorithm that assumes that there is a one-to-many join association between a large table and a small table. This assumes that each row in the large table matches exactly one row in the small table. This algorithm returns the estimated size of the larger input as the join cardinality.

But it doesn't say how SQL Server determines what is a "large table" and "small table" for purposes of this optimization.

Are these criteria documented anywhere?  Is it a simple threshold (e.g. "small table" must be under 10,000 rows), a percentage (e.g. "small table" must be <5% of rows in the "large table"), or some more complicated function?

Also, is there a trace flag or query hint that forces use of this optimization for a particular join?

Finally, does this optimization have a name that I can use for further Googling?

I'm asking because I want this "use the cardinality of the large table" cardinality estimation behavior in a join of master/detail tables, but my "small table" (master) is 1M rows and my "big table" (detail) is 22M rows. So I'm trying to learn more about this optimization to see if I can adjust my queries to force use of it.

The whitepaper does not define "large" for any examples, it uses the terms "large" and "small" to help explain the math the new CE is doing compared to the legacy CE.

The section you referenced shows a join predicate that contains a mix of equality and inequality predicates. The new CE will look at the rowcounts for the tables and determine which one is "large", and use that for the estimate. The legacy CE didn't look at rowcounts, it would just multiply the selectivity of each predicate.

HTH


PostgreSQL force sslmode=verify-full I have created my CA, Server and Clients certificates and keys. I have tried connections using sslmode flags with psql. I have edited the pg_hba.conf file to force SSL using hostssl only. Now I would like to set my server in order to force the verification to be sslmode=verify-full. I have read twice the documentation I and do not understand how to proceed. They say that it is controlled by an environment variable, but just cannot make it work. How must I set this variable and how would I make it persistent. Do anyone know how to proceed?

Update: I am runing a PostgreSQL 9.3 on Ubuntu Server 14.04

Maybe you want to limit the clients to those which present a given certificate, as described in Using client certificates:

  To require the client to supply a trusted certificate, place
  certificates of the certificate authorities (CAs) you trust in the
  file root.crt in the data directory, set the parameter ssl_ca_file in
  postgresql.conf to root.crt, and set the clientcert parameter to 1 on
  the appropriate hostssl line(s) in pg_hba.conf

On the other hand, sslmode=verify-full is a client-side feature. It benefits a client by ensuring that it connects to the intended server. It does not benefit a server as it's the server that is being checked.

Having a server being configured to "refuse to not being checked" doesn't seem to make much sense, as if a server would say "I don't trust myself".


What inefficiency does sharding & partitioning attempt to address? I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data.

Is this because of a limited hardware budget or to address end-user latency?

What I mean is that provided an unlimited budget providing for ideal processing, storage, memory, network, etc, would sharding or partitioning be able to still increase performance?

This should get you started to wonder more:

The Basics of Database Sharding
Shard (database architecture)
Partition (database)
SQL Server Table Partitioning: Resources
SQL Server 2005 Partitioned Tables and Indexes

A lot to read but will give you all the answers that you're looking for!


How do I move SQL Server database files? I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.

How can I do this?

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline.  This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

The process to move them is fairly simple.  Detach/Attach was already described, but it is not nearly this complex.

Change the file locations with an ALTER DATABASE command:

USE master; --do this all from the master
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

Note, you do not need to declare the old location in this command.  Changing this path does not take effect immediately, but will be used the next time the database starts up.

Set the database offline

(I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Move/Copy the files to the new location

Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

Bring the database online

ALTER DATABASE foo SET ONLINE;

You can see this described in more detail here.


Mongodb index keeps growing in size eventhough the collection has a TTL index I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes.
One of the indexes is a TTL index (24h only, 86400) on a date field named d, used to expire old data, this leads to almost fixing the number of entries in the collection within our use case. Given the fixed structure and model of the data, this means an almost fixed size of the collection and it's indexes, it has always been the case for index and collection sizes throughout the whole cluster.
Our issue is, we have another index on this collection, on a field named k which is a large string.
ex : GigabitEthernet_XXXX-XXXXX-01_ABC_DEFG-HIJKLMNOP-5-QRS_TUV_7_0&#47;0&#47;1
This index keeps growing indefinitely in size, and causes memory outages and we don't know why. Any help ?
Knowing that the TTL index always expires/removes data from the collection, isn't the second index, on the k field supposed to also remove the references to that data, hence be fixed or at least not be evergrowing in size ?
We use this same technique (TTL index + other indexes) on all our collections and it always works in terms of fixing the sizes, not on this collection though. It is also important to note that other collections have a much smaller k field than this one, is that the issue ? Any explanations ? Any mongodb docs about this ?
Happy to provide any needed details to explain the issue.
Thanks in advance

Compact the collection in your staging environment. If index size decreases, space is taken up by overhead.
Compare the size of compacted collection & index to the size of both pre compaction, identify the overhead amount/percentage.


moving large number of tables to different filegroups An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of the table name.

for example, with 5 tables named like this :

ABC_XXXX
ABC_YYYY
DEF_ZZZZ
DEF_TTTT
GHI_UUUU

All tables beginning by ABC should be moved to filegroup FG1, DEF to filegroup FG2 and other tables to filegroup DEFAULT.

This can be done using the command CREATE INDEX :

CREATE (UNIQUE|CLUSTERED|) INDEX <Index Name> ON <Table Name>(<Index Columns>)
       WITH (DROP_EXISTING = ON) ON <New Filegroup>

The biggest problem with this command is to retrieve the columns of each index in their proper order.

There are a lot of things you are leaving out of your description, which the script will need to accommodate for - is the index a primary key or a unique constraint? are any of the columns descending? is the index filtered? does it have any INCLUDE columns? While you can certainly generate a script manually, why not use the Generate Scripts wizard?

in Object Explorer, right-click your database
choose Tasks > Generate Scripts...
Click Next
Choose "Select specific database objects" and then select all the tables named ABC...
Click Next
Click Advanced
Scroll down and change "Script Indexes" to True
Click OK
Change the option to "Save to new query window"
Click Next / Next / Finish

Now you have a query window with all of your scripts, you'll have to remove some and do some hand-massaging of others, but for the indexes that aren't part of the PK you should simply be able to search and replace for DROP_EXISTING = OFF to DROP_EXISTING = ON and then swap out [PRIMARY] for FG1...

Here is a script that uses sys.indexes, sys.columns, and sys.index_columns - it creates a unique index in the case where an explicit primary key existed (since you haven't provided the "re-create PK with DROP_EXISTING" syntax I've asked for). This deals with include columns, fill factors, puts the columns in the correct order and even makes sure to build the unique/clustered indexes first.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10)
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
        WHERE cols.object_id = src.object_id
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')'
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
        WHERE cols.[object_id] = src.[object_id]
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')', '')
    + ' WITH (DROP_EXISTING = ON' + ff
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

For completeness, here is a script that adds sys.partitions and several SQL Server 2008-specific features such as filtered indexes and data compression.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    filter = CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dc = CASE p.data_compression_desc WHEN 'NONE' THEN ''
        ELSE ', DATA_COMPRESSION = ' + p.data_compression_desc END,
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10)
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols
        WHERE cols.object_id = src.object_id
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')'
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols
        WHERE cols.[object_id] = src.[object_id]
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal
        FOR XML PATH('')), 1, 1, '') + ')', '')
    + filter + ' WITH (DROP_EXISTING = ON' + ff + dc
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;


InnoDB errors and crashes with MyISAM database? I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes. The config is mostly the default one that comes with Ubuntu 18.04 package, I just adjusted some myisam buffers and cache. (Now I also increased innodb pool size, hoping it will make those errors go away)
What may cause those errors and how I get rid of them?
Examples of errors:

2019-09-19T04:39:34.464347Z 5169718 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 22967494 OS file reads, 55308825 OS file writes, 1252 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
2019-09-20T08:27:30.209263Z 0 [ERROR] [FATAL] InnoDB: Page [page id: space=45, page number=4551] still fixed or dirty
2019-09-20 10:27:30 0x7f9350594740  InnoDB: Assertion failure in thread 140270684948288 in file ut0ut.cc line 910

Innodb tables:
+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | OPTIMIZER_TRACE           |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | engine_cost               |
| mysql              | gtid_executed             |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | innodb_index_stats        |
| mysql              | innodb_table_stats        |
| mysql              | plugin                    |
| mysql              | server_cost               |
| mysql              | servers                   |
| mysql              | slave_master_info         |
| mysql              | slave_relay_log_info      |
| mysql              | slave_worker_info         |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| sys                | sys_config                |
+--------------------+---------------------------+

Version
mysql> show global variables like '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.27                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.27-0ubuntu0.18.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

I have some annoying news for you. MySQL 5.7 has InnoDB enabled permanently. It's in the Docs:

  Important

  InnoDB cannot be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

The number of InnoDB tables have increase from version to version.

I mentioned this Apr 04, 2018 in my old post Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

  MySQL SCHEMA FILES

  The number of InnoDB tables in the MySQL schema also changes between major releases.

  Back on Dec 09, 2017, I answered ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option and discussed when InnoDB tables starting being used in the MySQL schema.

  In MySQL 5.6, it was 5 tables. In MySQL 5.7, it went to 19. In MySQL 8.0, all 31 tables are InnoDB

From my old post, I stated that MySQL 5.7 has 19 InnoDB Tables in the mysql schema.

In order to hunt down your InnoDB properly, run this

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

The 19 tables from the mysql schema should appear. If you have additional InnoDB tables, you will have to get rid of or rebuild those tables. If the 19 tables do not show up from this query, you must fix this.

I just noticed you said in your question : Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes

If you did not upgrade the system tables correctly, mysqld is probably looking for the mysql schema tables to be InnoDB.

Please read MySQL 5.7 Docs on mysql_upgrade and look at --upgrade-system-tables. It should convert the mysql schema tables from MyISAM to InnoDB.

Please do this in a test environment first beforehand.

UPDATE 2019-09-20 18:09 EDT

After a nice chat session, it turns out that there is a lot of temp tables being generated. All the temp tables were using InnoDB, consuming 482MB of the InnoDB Buffer pool. The InnoDB buffer Pool was at default (128M). Increase above 512M solved it.

I also recommended switching every to InnoDB.


Time spent on the server vs network Is there a way i can track for a given query how much time was spent by SQL server vs time spent in transferring data in network? I am learning performance tuning, hence this question.

I am looking for a way to do it in SSMS but would like to know if there is any other tools/framework for the same.

Thanks.

I would use extended events for this to break down the wait stats per query.  You can then see how much of it was waiting on "Network_IO" vs. mostly everything else.  There are a lot of wait stats and most of them would be server side work but not all so make sure you review all the other significant wait stats and also the time it took to come up with the query plan.

I'd recommend either downloading a trial of the SQL Sentry Plan Explorer and put your query in there.  It'll tell you right away how long it took, you'll want to do this from the client machine.

This is the link to SQL Sentry Plan Explorer.  This is super easy to use and doesn't require any coding. Simply install the free product on the client machine you want to test from, activate the free pro mode for 2 weeks (I would recommend buying this tool, it's great), then look at your wait stats.

This one will provide you with the wait stats per query (From Paul's blog):

    — Drop the session if it exists.
    IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorWaits')
        DROP EVENT SESSION MonitorWaits ON SERVER
    GO

    CREATE EVENT SESSION MonitorWaits ON SERVER
    ADD EVENT sqlos.wait_info
        (WHERE sqlserver.session_id = 53 /* session_id of connection to monitor */)
    ADD TARGET package0.asynchronous_file_target
        (SET FILENAME = N'C:\SQLskills\EE_WaitStats.xel',
        METADATAFILE = N'C:\SQLskills\EE_WaitStats.xem')
    WITH (max_dispatch_latency = 1 seconds);
    GO

--Then use this:
SELECT xmv.map_key, xmv.map_value
FROM sys.dm_xe_map_values xmv
JOIN sys.dm_xe_packages xp
    ON xmv.object_package_guid = xp.guid
WHERE xmv.name = 'wait_types'
    AND xp.name = 'sqlos';
GO

-- Start the session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = START;
GO

-- Go do the query

-- Stop the event session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = STOP;
GO

And then I can see how many events fired:

SELECT COUNT (*)
FROM sys.fn_xe_file_target_read_file
    ('C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

13324

/* And then pull them into a temporary table and aggregate them (various ways of doing this, I prefer this one): */

— Create intermediate temp table for raw event data
CREATE TABLE #RawEventData (
    Rowid  INT IDENTITY PRIMARY KEY,
    event_data XML);

GO

-- Read the file data into intermediate temp table
INSERT INTO #RawEventData (event_data)
SELECT
    CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file (
    'C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

SELECT
    waits.[Wait Type],
    COUNT (*) AS [Wait Count],
    SUM (waits.[Duration]) AS [Total Wait Time (ms)],
    SUM (waits.[Duration]) – SUM (waits.[Signal Duration]) AS [Total Resource Wait Time (ms)],
    SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]
FROM
    (SELECT
        event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [Time],
        event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') AS [Wait Type],
        event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') AS [Op],
        event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS [Duration],
        event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS [Signal Duration]
     FROM #RawEventData
    ) AS waits
WHERE waits.[op] = 'End'
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait Time (ms)] DESC;
GO

-- Cleanup
DROP TABLE #RawEventData;
GO

This script from Paul Randall will provide you all the wait stats over a period of time:


Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement? I have this table:

SELECT * FROM items

id | item      | position
---|-----------|----------
 1 | USB cable |        0
 2 | SD card   |        4
 3 | Mouse     |        2
 4 | Keyboard  |        0
 5 | Monitor   |        3

Sorting this table gives this result:

SELECT * FROM items ORDER BY position

id | item      | position
---|-----------|----------
 4 | Keyboard  |        0
 1 | USB cable |        0
 3 | Mouse     |        2
 5 | Monitor   |        3
 2 | SD card   |        4

Now I want to update the table and save the order in the position column:

SELECT * FROM items

id | item      | position
---|-----------|----------
 4 | Keyboard  |        1
 1 | USB cable |        2
 3 | Mouse     |        3
 5 | Monitor   |        4
 2 | SD card   |        5

Can this be done with a single query or do I have to manually loop over all rows and do a manual update?

In case the order is not fully defined (e.g. for USB cable and Keyboard above) I just arbitrarily decided the order.

UPDATE
   T1
SET
   position = T2.rn
FROM
   myTable T1
   JOIN
   (
   SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY position) AS rn
   FROM
      myTable
   ) T2 ON T1.id = T2.id

Note: the order of "Keyboard" and "USB cable" is arbitrary. They both have position = 0

To tie-break positions based on item, add a secondary sort

      ROW_NUMBER() OVER (ORDER BY position, item) AS rn

If you have duplicate position,item pairs this will be arbitrary too...


Creating a mysql trigger without hard-coding database name Is there a way to create a trigger in mysql without hardcoding the database name?

Doing a typical create trigger will stop working if I dump the database and then import it into a database of a different name.

I guess I'll guess an answer to my question. A lot of mysql documentation just references a table name without specifying a database, so I am assuming the database is optional and if you don't include it, mysql will use the current one.


How to order and partition by 2+ columns and then select a random sample based on N number of ranks in the sample? I am using PostgreSQL.

I have a table with 2 columns in this example:

I want to add a new column with a unique id corresponding to partitions by name and category as shown in the result. Then, I want to take a random sample choosing 2 (or more) unique ids because under each unique id, there will be a lot of other historical data.

I have tried this so far, but I get 1s for everything. I'm missing something really simple here, but how do I correct my mistake? I have to do this operation for several million rows in the real table.

SELECT
  dense_rank() over (partition by name, category order by category) as unique_id,
  *
FROM
 example_table

After this, presumably I'll have to use RAND() somewhere but how do I do this?

This is my naive approach to get the solution in the above pic.

with ranks (
  ...
)

select * from ranks where unique_id = 2 or unique_id = 4

Use ROW_NUMBER() function better.

SELECT
    name,
    category,
    ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY name, category) unique_id
FROM
    test;

name | category | unique_id
:--- | :------- | --------:
A    | Alpha    |         1
A    | Alpha    |         2
A    | Alpha    |         3
A    | Beta     |         1
A    | Beta     |         2
B    | Alpha    |         1
B    | Alpha    |         2
B    | Theta    |         1

db<>fiddle here

To get a unique_id for each pair of name-category, remove the PARTITION part and use only ORDER BY:

SELECT
    name,
    category,
    DENSE_RANK() OVER (ORDER BY name, category) unique_id
FROM
    test;

name | category | unique_id
:--- | :------- | --------:
A    | Alpha    |         1
A    | Alpha    |         1
A    | Alpha    |         1
A    | Beta     |         2
A    | Beta     |         2
B    | Alpha    |         3
B    | Alpha    |         3
B    | Theta    |         4

db<>fiddle here

To select two rows of each unique_id: (As far as I've not used any order it is supposed to be random)

WITH ct AS
(
  SELECT
      name,
      category,
      DENSE_RANK() OVER (ORDER BY name, category) unique_id
  FROM
      test
)
SELECT
    a.unique_id, c.name, c.category
FROM
    (SELECT DISTINCT unique_id
     FROM ct) a
JOIN LATERAL (SELECT b.name, b.category
              FROM   ct b
              WHERE  b.unique_id = a.unique_id
              LIMIT 2) c ON TRUE
ORDER BY
    a.unique_id;

unique_id | name | category
--------: | :--- | :-------
        1 | A    | Alpha
        1 | A    | Alpha
        2 | A    | Beta
        2 | A    | Beta
        3 | B    | Alpha
        3 | B    | Alpha
        4 | B    | Theta

db<>fiddle here


How can I tell what recovery model my SQL database has? Is there a SQL command I can run to determine the recovery model of my database?  I want to know if its full recovery or not.

Some variety in how to do it...

SELECT D.recovery_model_desc FROM sys.databases D WHERE name = 'MyDB'

or

SELECT name, D.recovery_model_desc FROM sys.databases D

or

SELECT DATABASEPROPERTYEX('MyDB', 'Recovery')


Column Store Index over an clustered index (created when primary key is declared) - SQL Server When a clustered index is created, the table itself becomes the index structure sorted by the index key.

Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and we create a column store index over table XPTO with columns (b,c).

What is the structure of this index? Is a different structure comparing to the clustered table? Or does the column store have pointers to the clustered table (like an other non clustered index).

Finally, the same scenario but creating the column index with all attributes, what is the structure?

We can learn a lot by creating a test script!  You should be able to run the SQL below on any SQL 2014 instance (probably SQL 2012 as well, but I didn't test it there).

From this script, we can see that the non-clustered columnstore index on (b, c) does store the data for the clustered index column (a) and it does so in the same manner (via segments) that it stores b and c.  This allows the columstore to efficiently process queries that access a, b, and c.  Technically, it even allows the columnstore to be used, in combination with a key lookup, in order to process a query that needs all of the columns in the table (although this is not likely to be favored by the optimizer given the expense of the key lookup).

In terms of your questions about the "structure" of the columnstore index, I think that's too deep a question to answer here.  But if you are interested in learning more, I think that Niko Neugebauer's excellent 55-part (and growing) series on columnstore has a ton of valuable information on the structure and internals: http://www.nikoport.com/columnstore/

-- Create a table to your specs
CREATE TABLE dbo.XPTO (a BIGINT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, e INT NOT NULL, CONSTRAINT PK_XPTO PRIMARY KEY (a))
GO

-- Insert some trivial dummy data
INSERT INTO dbo.XPTO (a, b, c, d, e)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)), v2.x, v3.x, v4.x, v5.x
FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v2(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v3(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v4(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v5(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v6(x)
GO

-- Create the nonclustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_XPTO ON dbo.XPTO (b, c)
GO

-- Turn on "Include Actual Execution Plan"

-- Access only the columns specified in the columnstore index
-- Here we get a Columnstore Index Scan, as expected
SELECT SUM(b), SUM(c)
FROM XPTO
GO

-- Add in the clustered index column
-- Now we also get a Columnstore Index Scan!
-- This shows that the non-clustered columnstore does maintain data for "a"
-- because it is able to process this query without touching the clustered index
SELECT SUM(a), SUM(b), SUM(c)
FROM XPTO
GO

-- Access all columns
-- Now we get a Clustered Index Scan, and the columnstore index is not used
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO
GO

-- Try to force usage of columnstore
-- Now we see a tplan that uses the columnstore index but performs a key looked up to the clustered index
-- This is a plan shape I have never seen the query optmizer generate on its own, but it makes sense
-- that it is possible given that the columnstore index stores data for the clustered index column,
-- which is essentially a pointer to the corresponding row in the table
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO WITH(INDEX(cs_XPTO))
GO

-- View the column store segments
-- Here we see that there is a column store segment for columns a, b, and c (even though we asked for just b and c!)
-- This seems to indicate that clustered index columns are implicitly added to any nonclustered columnstore index
-- and are stored within that columnstore index in the same way as the requested columns
SELECT cs.*
FROM sys.partitions p
JOIN sys.column_store_segments cs
    ON cs.partition_id = p.partition_id
WHERE p.object_id = OBJECT_ID('XPTO')
GO

-- Cleanup
DROP TABLE dbo.XPTO
GO


Backing up Master-Master replicated MySQL databases I have two MySQL 5.5 databases which replicate each other. What's the best way to back these machines up? I would like to prioritise speed/ease of restoration.  Should I add extra slaves to both machines and also replicate there, and then periodically stop those slaves and copy the data somehow? Is a mysqldump enough, or should I be copying the whole data directory somehow?

This is a Master/Master setup, so I would advise the following

Do not mysqldump from either Master. Both Masters should be used as Active/Passive

Active : Receives all writes
Passive : Replicates from Active (Hot Standby for Failover)

Add an additional slave from each Master

Can be low commodity in CPU since SQL thread processes one command at a time
Should have the same RAM and plenty of Diskspace for Backups

If you do go with additional slaves, you can use either mysqldump or xtrabackup.


Which Indexes in which situations is more efficient in SQL Server 2012 I use SQL Server 2012.

I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... Include and in what situation I have to use Create NonClustered Index... with all columns.

What are the advantage and disadvantage relative to the other

Exemple :

Use [AdventureWorksDW2012]
Go

CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

Drop Index csi_FactResellerSales On [dbo].[FactResellerSales]
Create NonClustered Index Ardalan
    On [dbo].[FactResellerSales] (ProductKey) include(UnitPrice, CustomerPONumber,
                                                      OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

Drop Index Ardalan On [dbo].[FactResellerSales]
Create NonClustered Index Ardalan1
    On [dbo].[FactResellerSales] (ProductKey,UnitPrice, CustomerPONumber, OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;
Drop Index Ardalan1 On [dbo].[FactResellerSales]

I think you are already on the right track here - and I encourage you to keep experimenting with different options and see for yourself what the differences to performance are. But, here are my thoughts below:

Columnstore Indexes
These perform really well when you are genuinely interested in all (or most) of the data in a column. If you are keen to see the distribution of data in a column, e.g. number of sales per order or location data, then a column store is a highly performant way to access the whole column.

Writing to columnstore indexes is slower than writing to a row. I would not use a columnstore index if you have a high-velocity OLTP system. Would also avoid columnstore indexes if you are interested in filtering down to small result sets.

Covering Indexes
Use these when you have wide tables and queries that regularly access a small number of the columns in the table. Covering indexes are a superb way to remove clustered index scans, reduce your overall IO and improve concurrency.

Covering indexes over all the columns of a table
Never. I cannot see a good reason to do this. Am happy to be corrected though.
Perhaps... maybe... still not convinced... you might do this if you are implementing something like a filtered index. This would essentially chunk you table into discrete ranges. But I am not convinced this is a good method.


How to create DBA user from oracle APEX 11gR2 XE Please anyone tell me how to create DBA user from ORACLE Application express(http://127.0.0.1:8080/apex/apex_admin)?

I installed Oracle 11gR2 XE on windows 8.1 PC and I am able to login to Oracle APEX (above link) with username: "ADMIN" and password used during installation. but I can't use same username/password to login to "Oracle SQL Command Line". I am trying to connect with command

CONNECT username/password

All other solutions suggests a command "CREATE USER....." but I don't know where to write that command without connecting SQL Command line. I am missing a simple step somewhere. please help me.

My question is exactly opposite of this question. User with dba privs can't login to APEX, but can from SQL*Plus

Where I can login to Oracle APEX web interface and create new workspace, Schemas, Users but I am not able to login to SQL Command line to create Database objects.

Here is an article from dba-oracle which talk about this same problem. This might solve the issue you are having.


Creating CDC system table on SQL Server 2008 I am facing problem with CDC tables in my database. I think few of these tables are damaged and are not capturing any audit information. I tried to disable CDC on these tables, but that did not work (I didn't even get an error). So I manually dropped a table.

Now I cannot see the table in system tables - but its still not properly deleted. So I cannot re-enable it.

I have got a fix here: http://support.microsoft.com/kb/966306

which is to manually create the CDC table of the same name in system tables - and retry disabling it.

Now the question is how do I manually create a system table.

Thanks

As the very article you linked says:

  You can script those objects from a new dummy database in which you
  have successfully enabled CDC

These tables are not 'system' as in 'they belong to the sys namespace and protected from direct user operations' but are 'system' as in 'they are absolutely ordinary tables used by stored procedures shipped with CDC and MS just wishes nobody would mess with them'...


Disk file effects of delete and vacuum I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk insert (using copy) time was cut from 22 minutes to 2.3 minutes. The deletion time was also improved. I plan to make this bulk update every two hours or every hour.

Although the performance now (after SSD) is compatible with a more frequent update I have read some horror stories about SSD death due to limited NAND endurance combined with write amplification. As SSDs are expensive I would like to push its death as far into the future as possible. Hence my question: What really happens to the disk file in a delete and subsequent vacuum? I guess there are two disk writes, one to mark the row as deleted and the other when vacuuming to mark it as available to overwrite. If instead of deleting and vacuuming I partition the table creating and dropping tables at each bulk insert/delete would I be minimizing the SSD wearing?

You can save on the delete and vacuum if you will use smart partitioning!
Make sure your design is compatible with dropping partitions instead of actual DELETE.


Is SQL Server Mirroring dependent on SQL Server Agent running? I have SQL Server mirroring set up between two 2008R2 SQL Server instances.  I have multiple databases mirrored.  Prior to planned failover to the backup server I want to turn off the SQL Server Agent on the primary server so I don't have jobs running and failing while the DB is running from the failover server.  (I'm trying to patch my primary database server.)

However I'm not sure how dependent mirroring is on the SQL Server Agent.  Will mirroring continue to function with SQL Server Agent off?

I've seen articles that say mirroring is dependent on SQL Server Agent but I'm unsure if that is just for some sort of routine cleanup or if that is a requirement for mirroring to function.  I'm assuming just cleanup but I'd like to know more and I'm struggling to find answers to this exact question.

Thanks for any help you can give me (programmer wearing a newb-dba hat).

Mirroring is not dependent on SQL Server Agent running.  Communication between primary and secondary happens using TCPIP ports.  In database mirroring, principal and mirror server communicate through TCP connection and write data change directly to remote transaction log file.

Database Mirroring Overview
COMPARISON BETWEEN MIRRORING VS LOG SHIPPING AND MIRRORING VS
CLUSTERING

But if you want to monitor and get alerted you need the agent job running which get created automatically when you set up mirroring. Details below:

Monitoring Database Mirroring (SQL Server)


TDE - Encryption on a large database I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server.

What kind of problems (if any) can I expect? My testing shows very little impact in a test environment, but I'm concerned about how long this is going to take to setup in prod. Any feedback is appreciated.

I read somewhere that I'll need to disable Backups and database maintenance during the encryption process. Is this true?

This is very concerning, since my testing took ~4 days to complete the encryption.

We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014.
In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, so do a thorough load testing with some realistic PROD datasize.
Takeaways from embracing TDE :

Backup compression and TDE does not go hand in hand i.e. You wont be able to leverage backup compression when you enable TDE. This means your VLDB backups will be HUGE !

Instant File Initialization is not available when you enable TDE.

When restoring the databases from PROD to other environments (e.g. UAT, etc), you need to export the certificate so that the database can be restored.

You need to properly keep safe and backup certificates that you use to enable TDE.

Enabling TDE on a VLDB takes time. You can use this script to list out the databases, encryption status along with other useful information.
Remember: TDE will encrypt data at rest (on disk) ONLY. You still have to use encryption (e.g. force encryption) to protect data inflight.

Edit:
SQL server 2016 now supports backup compression on TDE enabled databases

while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB).


Does MySQL have a version of Change Data Capture? We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until everything was fully migrated out in our field. During that process, I was able to build an integration between our legacy system and our new system that leveraged SQL Server's Change Data Capture to track changes and integrate those over incrementally.

For this next migration, the legacy system that we will be phasing out is based on MySQL v5.1.69 instead of SQL Server. I am not familiar with MySQL and I was wondering if there are any technologies similar to CDC that can be leveraged on MySQL in our current version or a newer version that would be worth migrating towards?

LinkedIn has open-sourced its CDC tool, Databus, which supports MySQL. You can read about how to support MySQL on the wiki of the project: Databus for MySQL and read the blog statement here: Open sourcing Databus: LinkedIn's low latency change data capture system

However, you should note that there may be some limitations:

  The current implementation although fully functional should be considered a proof-of-concept. Its main goal is to demonstrate how to write a MySQL fetcher for Databus. The basic functionality of the fetcher has been tested but has not been deployed in our production environment. At LinkedIn, we run a slightly different implementation which relies on a custom-patched MySQL.

Another open-source CDC tool is FlexCDC (implemented in PHP) which was written as a part of the FlexViews project.

  [...] FlexCDC which will aim to provide a flexible CDC layer for MySQL based on RBR logging, and Flexviews, which uses FlexCDC to collect the changes necessary to incrementally maintain materialized views.

  From: Why are there no (other) change data capture utilities for MySQL 5.1?


How to get the last inserted id using scope identity without using a stored procedure in Mysql? My project contains  2 tables one is profile_table and another is address_table

Profile_ table contains:

+------+---------+----------+
| sid  | sname   | saddress |
+------+---------+----------+
|  1   | xyz     |   india  |
|  2   | abc     |     U.S  |
+------+---------+----------+

‘sid’ field in profile_table is made as primary key and autoincremented field.

The address table also  contains 'sid':

+------+---------+----------+-------
| sid  | squalification    | smarks |
+------+---------+----------+-------
|  1   |    B.E            | 80     |
|  2   |    M.S            |  85    |
+------+------------------ +--------+

The sid column in 'address_table' is made as foreign key in the address table and when I inserted the first record in the profile_table that sid value must be inserted in the   address_table along with squalification,smarks values.I want to use 'last inserted id' concept here and i dont want to use the stored procedure. please explain me how to get the last inserted id using scope identity without using a stored procedure in Mysql.

The LAST_INSERT_ID() function will return what you are looking for. It is a built-in function, does that qualify?

I'm not sure what you mean by excluding "stored procedure"; maybe it's just wrong terminology and you wish to also exclude built-in functions?


What happens to an SSIS package when I alter a table that is used as a destination? My apologizes if this question doesn't correspond to this site.
I have this question because in a SQL Server is deployed a SSIS Package (is a very old package) and I don't have the dtsx file.

Now I need to add some columns to a table, but this table is used as a destination on this package. So my question is, if I alter the table, will the package continue to work?

If you aren't changing the destination table name or the name or properties of any existing columns, then the package may continue to run without any changes.

If you need to actually include these new columns in your import, though, then you will probably need to update the package.

If you don't have the dtsx file, then I assume the package is published in an SSISDB?

Here are some articles that discuss editing/exporting a published package for different SQL/SSIS versions:

How to export a package from SSISDB?
Editing Published SSIS Package in SQL Server 2012.


MySQL table indexes change location Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?

You cannot move just indexes to another location. You have to move both data and indexes.

When you use innodb_file_per_table, this will create a file with the extension .ibd.

For example, if you have an InnoDB table as follows

datadir /var/lib/mysql
database mydb
table mytable

The physical files are the following

/var/lib/mysql/mydb/mytables.frm
/var/lib/mysql/mydb/mytables.ibd

The file /var/lib/mysql/mydb/mytables.ibd contains both data and index pages for mydb.mytable.

If you have another disk (says an SSD or HDD) and you want the .ibd file to be moved over there, you may have to use the DATA DIRECTORY directive for the CREATE TABLE command.

Here is what the MySQL Documentation for CREATE TABLE says on this:

  DATA DIRECTORY, INDEX DIRECTORY

  For InnoDB, the DATA DIRECTORY='directory' option allows you to create
  new InnoDB file-per-table tablespaces in a location outside the MySQL
  data directory. Within the directory that you specify, MySQL creates a
  subdirectory corresponding to the database name, and within that a
  .ibd file for the new table. To use the DATA DIRECTORYoption with
  InnoDB tables, the innodb_file_per_table configuration option must be
  enabled. The directory must be the full path name to the directory,
  not a relative path. See Section 14.5.4, “Specifying the Location of a
  Tablespace” for additional information.

Just a heads up on this: INDEX DIRECTORY only applies to MyISAM not InnoDB.
Suppose you have an SSD mounted on /innodb_data and you ran chown -R mysql:mysql /innodb_data

Here is how you can move mydb.mytable (by essentially copying)

CREATE TABLE LIKE mydb.mytable_new DATA DIRECTORY '/innodb_data' LIKE mydb.mytable.
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

If you are concerned with indexes in RAM, just tune the InnoDB Buffer Pool size.

Give it a Try !!!


Design for a Testing system I want to redesign a part of my system. I got the following objects:

Site:
id
Name

Site Version:
id
idSite
Name

Task:
id
idSite
Name

Test:
id
idTask
Name

TestResult:
idTest
idSiteVersion
dateStarted
dateEnded
result

Two problems:

You can see that I have a circle in the definitions. The problem is, I got Tasks defined for Sites, and also through the TestResult > SiteVersion > Site I can get back to Site again.
The main query to answer is, for a given Site, show all SiteVersion and their compatibility to Tasks defined for them.

I've solved 2. using sub-querys

SELECT
  tr1.id AS id,
  dsv.idSite_id AS idSite,
  tr1.idSiteVersion_id AS idSiteVersion,
  tr1.idTest_id AS idTest,
  tr1.result AS result,
  tr1.timeStarted AS timeStarted,
FROM
  dash.testresult AS tr1
  NATURAL JOIN (
    SELECT idTest_id, idSiteVersion_id, MAX(timeEnd) AS timeEnd
    FROM dash.testresult
    WHERE timeEnd IS NOT NULL
    GROUP BY idTest_id, idSiteVersion_id
  ) AS tr2
  JOIN dash.siteversion AS dsv
    ON dsv.id = tr1.idSiteVersion_id
  JOIN dash.test AS dtest
    ON dtest.id = tr1.idTest_id AND dtest.ignored = 0


Disable foreign key checks during import I have started a humongous (~800GB sql dump) import to a mysql 5.5.

At some point I realized that while the db was being imported, the progress was starting slowing down as the db size increased;

That was a clear hint that the increasing size of foreign key checks was probably slowing the process;

I realized I had forgotten to disable them, which I just did (in the middle of the import);

So my question is if whether the specific import will benefit from this or it will keep performing FK checks (which were on in the import initialization)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

The command

SET FOREIGN_KEY_CHECKS=0;

sets the value of a variable in a session scope. So it affects on the current session only, and do not affect on another sessions, both already running and those that will be launched later.

If you specify new value as a global-scope one using the command

SET GLOBAL FOREIGN_KEY_CHECKS=0;

then the global setting will be set. It affects on the current session and sessions that will be launched later, and do not affects on another sessions, which are already running.

You can see it in practice.

Start 2 CLI (mysql.exe) sessions.
Check the variable value in all windows.
Alter it in the 1st window.
Start 3rd window.
Check the variable value in all windows.
Alter it globally in the 1st window.
Start 4th window.
Check the variable value in all windows.


Does the recovery model affect growth of the .mdf file? On my production database, the .mdf file is rapidly growing on a daily basis.

If I change the recovery model from SIMPLE to FULL, will that help?

the simple recovery model only affects the log files of your database (.ldf).

If your datafiles are growing, you can check the tables that are occupying more space, you can use the management studio: right click on the database> reports > standard reports > disk usage by top tables

this way you can determine which table is taking all the space and plan a cleanup task for that (or move data to another DB)


psql ^C takes long time to cancel query I am using psql to query a large database. and (understandably) many queries that I type in take a long time.  I typically want to cancel these queries and so I enter ^C, and psql responds with the message

Cancel request sent

This cancel request sometimes cancels queries quickly (milliseconds), but sometimes takes very long times (>10 minutes) to cancel the query and display a prompt where I can type in new commands.  These long cancel times even happen on queries that have only been running less than a second.

Why does psql take so long to cancel some queries?  And what can I do to make psql cancel the queries faster?

Version info:

$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)

I usually see this when the resultset is large, and has already started to be sent to the client.  The client (apparently, I have not dug into the source code of this) still needs to read everything that was in the pipeline to be sent to it but not yet received, and this can take a long time, especially if the client is in the midst of a swap storm.

Are you using any third party or little-used extensions?

If these aren't the case for you, you will need to provide a reproducer or do some investigation of your own.  While you wait for the cancel to take effect, what is busy on your system?  Just the psql process itself, just the postgres backend it was connected to, or both?  Can you run perf on the busy process and see what it is doing?  Or attach gdb to it and get some backtraces at random times?


Subquery uses ungrouped column "s.student_id" from outer query I have the following tables:

1 teacher can have 1 or more students
1 student can have 1 or more homeworks

I would like to know in a list of teachers, for each teacher if it has any student with any homework assigned.

Query I tried:

select t.teacher_id as id,
       t.name as name,
       (select count(*)
          from homework
            where s.student_id = h.student_id ) as teacherHasHomeworkToVerify
from teacher t
       left join student s on t.teacher_id = s.teacher_id
       left join homework h on s.student_id = h.student_id
       group by t.teacher_id
       order by t.name COLLATE "en_US"

Error:

  ERROR: subquery uses ungrouped column "s.student_id" from outer query

SQL Fiddle link: http://sqlfiddle.com/#!15/a00ac/4

Schema:

create table teacher (
    teacher_id serial primary key,
    name text
);

insert into teacher (teacher_id, name)
values (1, 'bob'),
       (2, 'mary');

create table student (
    student_id serial primary key,
    teacher_id bigint,
    name text
);

insert into student (student_id, teacher_id, name)
values (1, 1, 'smith'),
       (2, 2, 'jimmy'),
       (3, 2, 'james');

create table homework (
    homework_id serial primary key,
    student_id bigint,
    subject text
);

insert into homework (homework_id, student_id, subject)
values (1, 3, 'math');

I found the issue, don't need the subquery, just a simple case statement and count.

Fixed query:

select t.teacher_id as id,
       t.name as name,
       case when
           count(h.student_id) > 0
         then 't'
         else 'f'
       end as teacherHasHomeworkToVerify
from teacher t
       left join student s on t.teacher_id = s.teacher_id
       left join homework h on s.student_id = h.student_id
       group by t.teacher_id
       order by t.name collate "en_US"

Fixed SQL Fiddle version link: http://sqlfiddle.com/#!15/a00ac/5


Display the the result of a substraction in a MySQL query (1 table) I've made a table that looks like this:

stop (table name)
-cityName {PK}
-schedule {PK}
-price
-routeNo

Basicly the customer wants to know what's the cost difference between 2 different stops by giving the city name of both A and B and the routeNo (routeNo is the same for both stops)

Now I have to make a MySQL query in order to display that difference:

Say City A cost 10$ and City B cost 15$ The returned value should be 5$ (price of B - price of A)

Sorry about the rookie question but I'm lost I've tried:

SELECT (b.price - a.price) AS Returned
FROM stop
WHERE a.cityName = 'A' AND b.cityName = 'B' AND routeNo = 'x';

I'd try this :

SELECT ROUND(b.price - a.price, 2) AS Returned
FROM stop a,(select price from stop  where cityName = 'B') as b
WHERE a.cityName = 'A' AND a.`routeNo ` = '1';

  OR even this :

SELECT ROUND(b.price - a.price, 2) AS Returned
FROM stop as a join stop as b
ON a.cityName = 'A' AND b.cityName = 'B'
WHERE a.routeNo = 'x';

  (Updated)


MySQL query took extremely long time to finish and it was in 'Sending data' state This query took extremly long time and it never seemed to finish.

Below is the query:

INSERT INTO `zhihu_answer_meta_reconcile`

    SELECT a.*
    FROM `zhihu_answer_meta` a
    LEFT JOIN `zhihu_answer_meta_staging` b
            ON a.answer_id   = b.answer_id
           AND a.insert_time = b.insert_time
    WHERE b.answer_id IS NULL

UNION ALL

    SELECT *
    FROM `zhihu_answer_meta_staging`

The row numbers of these tables are:

zhihu_answer_meta_staging: 1074588
zhihu_answer_meta: 2315697
zhihu_answer_meta_reconcile: 0  this table is a newly created table for inserting data into.

Besides, both of three tables' structure is the same.

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Appretiate if anyone can give some advice. Thanks a lot.

the combination of answer_id and insert_time is unique. I already created that index for the combination

Create this UNIQUE index on destination table (zhihu_answer_meta_reconcile).
Perform the action you need with 2 separate queries:

-- TRUNCATE `zhihu_answer_meta_reconcile`;
INSERT INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta_staging`;
INSERT IGNORE INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta`;

These queries may be combined into one line. While using from some code check that the inserting function/method allows multi-query in that case.


How to call Oracle's stored procedure from DB2 stored procedure? I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure.

I have a scenario where a trigger on a DB2 table will invoke a DB2 stored procedure. And further this stored procedure have to call to connect with Oracle DB to do some certain operation (a stored procedure).

Is my understanding is right, and can Oracle's stored procedure can be called through DB Link?

Edit: The version of DB2 is 9.7 and my platform is AIX 6.

Depending on the DB2 platform, it may indeed be possible to call an Oracle (or other database) stored procedure from a DB2 stored procedure. With DB2 for Linux, Unix, and Windows the process of setting up communication with a foreign data source is called federation. You can read more about this in the manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.iis.fed.sysconfig.doc/topics/iiyfafsporc.html


mongos is not listen when configured for sharded architecture (version 3.4) Deal all,
I am trying to mount a sharded mongodb server. I configured a replica set, and a configure replicat set. Both replicate are composd from 3 servers

root     23792     1  0 14:43 ?        00:01:55 mongod --replSet rs01 --journal --port 27102 --shardsvr --dbpath /mongo/rs01i01/ --logpath /mongo/rs01i01/mongo_102.log --fork
root     23820     1  0 14:44 ?        00:01:56 mongod --replSet rs01 --journal --port 27103 --shardsvr --dbpath /mongo/rs01i02/ --logpath /mongo/rs01i02/mongo_103.log --fork
root     23852     1  0 14:45 ?        00:01:21 mongod --replSet rs01 --journal --port 27101 --shardsvr --dbpath /mongo/rs01iar/ --logpath /mongo/rs01iar/mongo_101.log --fork
root     25240     1  0 16:18 ?        00:01:37 mongod --replSet conf --journal --port 27201 --dbpath /mongo/config --logpath /mongo/config/mongo_201.log --fork
root     25269     1  0 16:19 ?        00:01:37 mongod --replSet conf --journal --port 27202 --dbpath /mongo/config2 --logpath /mongo/config2/mongo_202.log --fork
root     25297     1  0 16:19 ?        00:01:38 mongod --replSet conf --journal --port 27203 --dbpath /mongo/config3 --logpath /mongo/config3/mongo_203.log --fork

However, when I try in the last step, to start mongo, the process is lunched  (3 times :O ) but is not listening on the corresponding port.

mongos --port 27301 --configdb conf/127.0.0.1:27201,127.0.0.1:27202,127.0.0.1:27203 --logpath /mongo/mongos/mongos_27301.log --fork
about to fork child process, waiting until server is ready for connections.
forked process: 9803

but, mongo does not connect on that port

mongo --port 27301
MongoDB shell version v3.4.10
connecting to: mongodb://127.0.0.1:27301/
2017-11-01T22:56:00.898+0000 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27301, in(checking socket for error after poll), reason: Connection refused
2017-11-01T22:56:00.899+0000 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27301, connection attempt failed :
connect@src/mongo/shell/mongo.js:237:13
@(connect):1:6
exception: connect failed

Please help

Your config servers mongod should have --configsvr parameter


Two names for a column I have a column named foo in a table. I have constructed queries in scripts, which use this name.

I would now like to rename the column in the table to bar. This will break the scripts (there are several, but they could be changed if necessary). The table is referenced by other tables. I'd like to rename because the context of what is stored in that column has changed.

Is it possible to have two names for a column?

I will assume your table is named baz and is in schema public

create table baz (
  foo text primary key
);

insert into baz (foo) values ('hello');

select foo from baz;

Now you want to rename that column:

alter table baz rename column foo to bar;

But now your scripts are broken:

select foo from baz; -- nope!

So create a new schema, put a view in it, and alter your search_path to hide the table "behind" the view.

create schema qux;

create or replace view qux.baz as
    select
      bar as foo
    from public.baz;

set search_path to qux, public;

select foo from baz; -- works!

This is kind of the postgres way to do create synonym


Deploy dacpac fails with compatibility set to 130 When we do a build with dacpac, there is a timeout error connecting to the server when the compatibility level is set to 130 on one of the databases. The process builds just fine as long as the one database is set to compatibility level 100. That is where it used to be.

We use SQL Server 2016
This is the sqlpackage executable:
C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe

Am I missing settings or something else on the database that would cause this?

I had (have) this same problem.

When the dacpac generates the script, it queries the target database to see what objects are there. One or more of those queries went from sub second to much longer, when we moved to 2016. By updating the statistics of some system tables I was able to bring it back down to normal behavior.

update statistics sys.sysschobjs;
update statistics sys.sysobjvalues;
update statistics sys.sysidxstats;
update statistics sys.syssingleobjrefs;

So, I don’t know exactly what changed to make SQL Server run those queries slowly, but this is how we've solved the problem.


MySQL get a lock to update certain rows and prevent other sessions to read that row my question is:
How can I get a certain row to update and on the other side, block that row to be selected by other sessions. I don't want a simple SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE. I want the second session not to be able to select the row at all. Maybe some isolation level, I tried them all but nothing.
Thanks

Unless you set the transaction level to read uncommitted, a transaction will see the unmodified row. The second session only has to acknowledge the existence of the first session when both sessions want to lock that row, otherwise they will act as if the other transaction doesn't exist (as that session could rollback at any time) - which is the whole point of using transactions.

In MySQL, you can get a locking read by either explicitly using SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE, or by using the "serializable"-isolation level for the second transaction, which implicitly adds the LOCK IN SHARE MODE for you to every select in that transaction:

  This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

So if you can force the second transaction to use that transaction level and to not enable autocommit, you can lock that row for this session; but keep in mind that this mode can slow down concurrent queries, as every read locks, so you might want to use this mode only when you need it.

This behaviour complies with the requirements of the sql standard. The exact implementation is a design choice, and other databases will do this differently. E.g. the MSSQL Server will not read your modified, uncommitted row (unless the isolation level is read uncommitted).


Mysql Getting Access denied for user 'root'@'localhost' (using password: YES) constantly in log I have MonYog running and it is monitoring my DB Server. It gives me constant alerts that the number of refused connections has exceeded the threshold etc. Looking in the LySQL.Log I can see constant login failures for root@localhost.

This is on a hosted server out in the ether so I have been as strict as possible with access etc. Firstly, the 3306 port on the firewall is open only to a couple of selected IP addresses. All other users (for dev and testing etc) use SSH. Secondly, there is no 'root' user. I renamed the root account to something else. This new named account only has localhost access.

So from the above, I just don't see how any login attempts are getting to the DB from localhost. The 3306 port is closed to all but one IP address (mine) and I am not attempting any connect to the server all through the night as the logs are showing. And anyway, I never connect as the (new) root dba user unless I need to do some special admin task.

Is there any way of finding out where these login attempts are coming from (IP address, host name, application name etc...).

Or am I looking in the wrong place. I just cant work out where they are coming from but I am convinced it cant be good!

A) You can check TCPdump output for MySQL port connection.

B) Also you can verify whether any script is running on MySQL server may be something like monit etc.. which continuously pings MySQL port.


At what point is SQL Server's is_policy_checked enforced? At what point is SQL Server's is_policy_checked enforced - just at next password change or at each login?

The CHECK_POLICY option is used to enforce Windows Password Policy restrictions and mechanisms for SQL Server logins. This applies when a login is created or when a password is changed for a SQL Server login as it affects the validity of a chosen password based on the setting state and the Windows password policy. It does not perform any validation or checks when a user logs on.

If you're trying to force passwords to be validated after enabling this option on existing logins, you should also enable the MUST_CHANGE option to force users to change their password on next login as the new passwords would then have to comply with the new policy.


What specifically does OracleBulkCopy do, and how can I optimize its performance? To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we try to scale up to 5M, the performance starts slowing to a crawl once it hits the 1M mark, gets progressively slower as more rows are loaded, and eventually times out after 3 hours or so.

I suspect it's related to a primary key on the table, but I've been trawling the Oracle forums and Stack Overflow for information and a lot of what I'm reading contradicts that (also, a lot of posts seem to contradict each other).  I'm hoping that somebody can set the record straight on some closely-related questions about the process:

Does the OracleBulkCopy class use conventional or direct-path loading? Is there some way I can confirm this, one way or another?
Assuming it does use direct-path loading: Is it true that Oracle automatically sets all indexes to unusable during the load and puts them back online afterward? I've read several statements to this effect but again, cannot confirm it.
If #2 is true, then should it make any difference what indexes are on the table before I initiate a bulk copy operation?  If so, why?
Related to #3, is there any practical difference, in general, between bulk loading with an unusable index vs. actually dropping the index before the load and recreating it afterward?
If #2 is not correct, or if there are some caveats I'm not understanding, then would it make any difference to explicitly make the index unusable before the bulk load, and then  explicitly rebuild it afterward?
Is there anything else, other than index builds, that could cause a bulk copy operation to grow progressively slower as more and more records are added? (Maybe something to do with logging, although I would expect that bulk operations are not logged?)
If there really is no other way to get the performance up to snuff aside from dropping the PK/index first, what steps can I take to make sure that the index doesn't completely disappear, i.e. if the connection to the database is lost in the middle of the process?

A few more days of reading and experimentation and I was able to (mostly) answer a lot of these:

I found this buried in the ODP.NET documentation (ironically not in the OracleBulkCopy docs):

  The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to,
  but not the same as Oracle SQL*Loader. Using direct path load is faster than conventional loading (using conventional SQL INSERT statements).

So it appears that it does use direct path.
This I was able to verify by doing a large bulk copy operation and getting the index properties from SQL Developer. The index did appear as UNUSABLE while the bulk copy was in progress.  However, I've also discovered that OracleBulkCopy.WriteToServer will refuse to run if the index starts in an UNUSABLE state, so clearly there's more going on here, because if it were as simple as disabling and rebuilding the index then it shouldn't care about initial state.
It does make a difference specifically if the index is also a constraint. Found this little gem in the documentation linked above:

  Enabled Constraints
  During an Oracle bulk copy, the following constraints are automatically enabled by
  default:

  NOT NULL
  UNIQUE
  PRIMARY KEY (unique-constraints on not-null columns)

  NOT NULL constraints are checked at column array build time. Any row that violates the NOT NULL constraint is rejected.

  UNIQUE constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE constraint.

The documentation is a little hazy on what happens during the load, especially with primary keys, but one thing is absolutely certain - it behaves differently with a primary key vs. without one. Since the OracleBulkCopy will happily allow you violate index constraints (and punt the index into UNUSABLE state when it's done), my hunch is that it's building the PK index during the bulk copy but simply not validating it until afterward.
I'm not sure whether the difference observed is within Oracle itself or just a quirk of the OracleBulkCopy. The jury's still out on this one.
OracleBulkCopy will throw an exception if an index is initially in the UNUSABLE state, so it's really a moot point.
If there are other factors, indexes (and especially PK indexes) are still the most important, as I found out by:
Creating a global temporary table with the same schema (using CREATE AS), then bulk copying into the temporary table, and finally doing a plain old INSERT from the temp table into the real table. Since the temp table has no index, the bulk copy happens very fast, and the final INSERT is also fast because the data is already in a table (I haven't tried the append hint yet, since a 5M row table-to-table copy already takes less than 1 minute).

I'm not yet sure of the potential ramifications of (ab)using the temporary table space this way, but so far it hasn't given me any trouble, and it's much safer than the alternative by way of preventing corruption of either the rows or indexes.

The success of this also pretty clearly demonstrates that the PK index is the problem, as that is the only practical difference between the temp table and the permanent table - both started with zero rows during the performance tests.

Conclusion: Don't bother trying to bulk copy more than around 100k rows into an indexed Oracle table using ODP.NET. Either drop the index (if you don't actually need it) or "preload" the data into a different (non-indexed) table.