Proper Guidelines for new Database setup in SQL Server I need proper guidelines to setup Database in SQL Server for sales Application (Its a huge Data Transaction System). Specifically i'm looking below information:

How to define initial size to create Database
Should i use different file and filegroups
What type of Collation should i select
Any tips for tempdb configuration
How to defined how much resource needed and how to allocate them.
How to handle Server Memory Setup (Minimum server memory, Maximum
server memory, Index creation memory, Minimum memory per query)
How to calculate/estimated number of Maximum worker threads
How to calculate/estimated Maximum number of concurrent connections
How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

Thanks in Advance

How to define initial size to create Database

Well I believe you are asking what value to select for initial size ?. If so, I am sorry to say that it depends on your environment. But please do not leave it to default of 2 MB (for data file) and 1 MB (for log file). I wish MS had better value set as default but this is AFAIK from SQL Server 2005 and has not been changed yet. If you ask me, I would suggest to keep initial size to as much as possible to avoid autogrowth. Suppose you know your database would be 500 GB in year keep it to 300 GB and forget the hassle of autogrowth and slowness due to it, there is no harm in giving as much space to data file as it needs this is only going to benefit the queries. This size if for data file, for log file you would have to figure out yourself. SQL Server Database growth and Autogrowth Setting article would help you more.

  Should i use different file and filegroups

Well again the answer is "it depends", file and filegroups are more for making DBA's management job easier and segregating things. If you have large database where you want to offload non critical objects which are accessed less on slow drive and move more accessed objects on faster drive you can do it via filegroups. You can also move one BIG table to altogether different filegroup residing on fast drive to make queries faster. Please read below 2 discussions.

Benefit to use multiple filegroups
Benefit of filegroups and setting filegroups to read-only

  What type of Collation should i select

That definitely I cannot tell and you should ask the respective teams.

  Any tips for tempdb configuration

A lot, please refer to below links. Since you have not added what version of SQL Server you are talking I would only suggest very basic things. Putting tempdb on faster drive makes queries using temp tables fast. Make sure you correctly add temdb data files and keep initial size and autogrowth same for all the data files. For SQL Server 2014 and before enabling TF 1117 and 1118 is also recommended. For SQL Server 2016 and above TF's are not required. More details Here

Correctly adding Tempdb Files
Cheat Sheet How to Configure Tempdb

  How to defined how much resource needed and how to allocate them

That needs lot of information from your side. There is no direct formula for it.

  How to handle Server Memory Setup (Minimum server memory, Maximum server memory, Index creation memory, Minimum memory per query)

To configure max server memory please refer What is a deterministic method for evaluating a sensible buffer pool size?. Don't touch index creation memory and minimum memory per query. If you are having just one instance of SQL server leave min server memory to default.

Ignore points 7  should not touch those settings unless you are expert. The default just works fine. You should let SQL Server decide worker threads it does the job beautifully.

  How to calculate/estimated Maximum number of concurrent connections

Unless you start putting some load on server there is hardly any point in getting concurrent connection. For getting max connections on SQL Server hitting see Stackexchange thread Max Number of connection

  How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

You just need to setup proper max degree of parallelism rest you should leave to default. For maxdop setting refer MAXDOP setting algorithm for SQL Server


PostgreSQL: initdb fails with: 'FATAL: could not access file "$libdir/euc_kr_and_mic": No such file or directory I have installed postgis-bundle-pg96x64-2.3.1-1 on my Windows workstation. I'm trying to create a database but initdb fails with

  "FATAL:  could not access file "$libdir/euc_kr_and_mic": No such file or directory"

Any idea why this is happening?

I have uninstalled and installed the package just in case, and rebooted the machine...

Here is the full log:

$ initdb -D PGDATA -E utf8 --no-locale -U $PGUSER
The files belonging to this database system will be owned by user "PKuchta".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory PGDATA ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... windows
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... FATAL:  could not access file
"$libdir/euc_kr_and_mic": No such file or directory
STATEMENT:  -- EUC_KR --> MULE_INTERNAL
        CREATE OR REPLACE FUNCTION euc_kr_to_mic (INTEGER, INTEGER, CSTRING,
INTERNAL, INTEGER) RETURNS VOID AS '$libdir/euc_kr_and_mic', 'euc_kr_to_mic'
LANGUAGE C STRICT;
        COMMENT ON FUNCTION euc_kr_to_mic(INTEGER, INTEGER, CSTRING,
INTERNAL, INTEGER) IS 'internal conversion function for EUC_KR to
MULE_INTERNAL';
        CREATE DEFAULT CONVERSION pg_catalog.euc_kr_to_mic FOR 'EUC_KR' TO
'MULE_INTERNAL' FROM euc_kr_to_mic;
        COMMENT ON CONVERSION pg_catalog.euc_kr_to_mic IS 'conversion for
EUC_KR to MULE_INTERNAL';

child process exited with exit code 1
initdb: removing data directory "PGDATA"

It turned out the problem was caused by leftovers from another PostgreSQL installation. After removing that the problem was solved.


Should database directory and file access be restricted to only the db user? I don't know exactly how to word this question and I haven't been able to find any specifics on it so apologies if it's a duplicate.

I know that it's standard practice to have database server software run as a specific operating system user, with access to nothing but the necessary directories and files. I understand that this is done to prevent intruders that gain access through the software to have priveleges outside the minimum necessary for the db to run.

However, should I also restrict read and/or write permissions to the database directories and files at the operating system level to the db user alone? Is this standard practice? Are there any problems that can arise by other software or the operating system performing read operations on a live db?

I'm interested in both the general case (a live server, like a vps) and the case where it's running on a local desktop, used by only one person. Using postgres at the moment and I'm not experienced.

I'd say this is a good security practice to restrict OS access to your DB files.  First you should definitely restrict write privileges to DB files at the OS level, if not already configured that way.  Don't allow anyone other than the OS user that runs the DB to write to the DB files.  Practically any DB software I can think of restricts this by default.

If you want to additionally restrict read access, this would be a good idea, especially if your database contains sensitive information.  Any unprivileged OS user that shares your OS, if he/she has read-access to your DB files, they basically can get read access to your DB.  They could simply steal copies of your DB files, send them to their own server, and setup the DB system and grant for themselves unlimited access to that clone.  I'd say the majority of DB software does NOT proactively restrict read access at the OS level to the datafiles.  It's up to you, the DBA to restrict this.  If your data is super sensitive, you might want to consider encryption, which is available in most major DBMS.  If the datafiles are encrypted, a would-be information thief would most likely be foiled even if he/she made off with your datafiles.

Don't forget to encrypt your backups, too, since they are just as sensitive as the DB itself.

Generally, you don't need to worry about the impact of cutting off access to other OS tools like backup software.  Most DBMS can't and don't use any OS-based backup tools since DB files are changing.  Rather you use specific tools like Oracle RMAN, SQL Server native backup, Litespeed, mysqldump, etc.  These tools know how to handle a database that is changing and to get a proper backup.  And some other OS tools like antivirus scanners run with root/system authority, so you can't keep them from reading your datafiles by permissions alone (encryption can).

If you're doing this on Unix, it can sometimes be a matter of simply chmod 700 (or perhaps 750) on the directory that contains your DB files.  On Windows you could likely do this via NTFS permissions.


How to split table into two tables in one transaction or query? I have a table "profiles" with these columns: "id", "name", "surname", "email", "avatar".

I want to create table "media" which will contain columns "id" and "path".

After that I want to create column "profiles"."avatar_id" and migrate data by this rule: each value of "profiles"."avatar" must be inserted into "media" ("media"."id" is autogenerated), returning id must be written into "profiles"."avatar_id" in the same row.

It should be executed in one query, transaction or procedure, and I want to use only postgres functionality.

I tried to rename "profiles" to "profiles_old", create new "profiles" with "avatar_id" but without "avatar". Then run something like:

INSERT INTO profiles(id, "name", email, surname, avatar_id)
SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old;

...but SELECT doesn't support subquery INSERT. Maybe I should use procedure?

I am using Postgres 9.4.9. I tried a transaction and temp tables. I cannot understand how to insert each row of one table into another table and update the same row of first table with returned id? My code:

BEGIN;
ALTER TABLE profiles DROP CONSTRAINT profiles_pkey;
ALTER TABLE profiles RENAME TO profiles_old;
CREATE TABLE profiles (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
"name" character varying(255),
email character varying(255),
surname character varying(255),
avatar_id uuid NOT NULL,
CONSTRAINT profiles_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE );
ALTER TABLE profiles OWNER TO app;
INSERT INTO profiles(id, "name", email, surname, gender, avatar_id) SELECT id, "name", email, surname, (INSERT INTO media(origin) VALUES(profiles_old.avatar) RETURNING id) FROM profiles_old;
DROP TABLE profiles_old;
COMMIT;

I cannot understand how to insert each row of one table into another
  table and update the same row of first table with returned id

You don't have to use an intermediate table. It can be done in one statement, with a writeable CTE:

WITH t as
 (INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile RETURNING id,origin)
 UPDATE profile SET avatar_id=t.id FROM t WHERE avatar=t.origin;

(assuming that media.id is automatically set by its DEFAULT clause).

Or the same as two statements in a transaction, using a different form of update:

BEGIN;
INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile;
UPDATE profile SET avatar_id=(SELECT id FROM media WHERE profile.avatar=media.origin);
COMMIT;

If you know that profile.avatar is unique, DISTINCT can be omitted, otherwise it's required.


MAX(id) function in query with indexed condition I have a table

create table test
(
  id int auto_increment,
  createdAt TIMESTAMP not null,
  primary key (id)
);
create index createdAt
  on test (createdAt);

And here is my query

SELECT MAX(id) FROM test WHERE DATE(createdAt) <= '2020-02-07';

Explain says Using where; Using index
But slow query log says 1 - 10+ seconds.

Important notice: Old rows with createdAt < NOW() - 1 MONTH periodically removed by separate cron job. Average rows count 100k-10m.

As I understand, MAX() function compares each row, so results are so volatile.

Question: Is there any possible optimizations for such aggregation function?

DATE(), not MAX() is the villain...

DATE(createdAt) <= '2020-02-07' is not "sargable".

This has the equivalent effect, and can use INDEX(created_at):  created_at <= '2020-02-07'.  Yes, that works for datatypesDATE,DATETIME, andTIMESTAMP`.


MySQL get_lock timeout misunderstanding? Am I missing something here. I'm looking at using GET_LOCK to allow two applications to co-ordinate which data to wok on.

From reading the manual it suggests that I need to apply a timeout to the GET_LOCK command.

e.g.

SELECT GET_LOCK('testlock1',10);

So I'm thinking that this means I am creating a lock called testlock1 that will remain in place until either I release it, or 10 seconds has passed.

If I then open another session (simulating a second application), and try to run SELECT GET_LOCK('testlock1',10); it just returns a 0.

No matter how long I leave it, the lock remains in place.

I would presume the full process would be something like:

SET @id = "SELECT id from dbname.tablename WHERE . . . "
SELECT GET_LOCK(CONCAT(@id,'-dbname-tablename'),10);
# DO STUFF HERE . . .
UPDATE dbname.tablename . . . WHERE id = @id;
RELEASE GET_LOCK(CONCAT(@id,'-dbname-tablename'));

But if something happens, and the RELEASE_LOCK doesn't run (and the session remains open), that row will, in essence, be locked from any further updates.
Hence the reason I presumed the timeout was there?

The timeout applies to how long GET_LOCK will wait to acquire the lock (before timing out), in the event the lock is already held by another client.   It does not indicate how long a LOCK will be held as you mention


What happens on the primary when log cannot be restored on the secondary on Log Shipping I would like to setup a log shipping but I have a concern as to what would happen if the restore operation on the secondary would fail for some reason. I want the primary database keep working no matter what happens on the secondary database - even if the log restore can't be completed or secondary db goes offline. Does log shipping offer such an option?

Thank you all.

A restore failing on the secondary will have no impact on the primary. You want to monitor your secondary DBs to make sure this hasn't happened.


What is refreshing my table in SQL Server? Is there any way to figure out what process or trigger is refreshing a table in SQL Server? I have this table that I thought is a standalone table and all the records I manually inserted were truncated overnight. So wondering if any of you can give details.

Depending on the situation outside of what you have mentioned, you might be able to eliminate this problem by creating a new account on mysql and remove access to the db or the table of all other accounts.  Then you would have to update the system elements you want affecting that data.  This approach potentially is easy, but it also potentially is unfeasible.

If you cannot do that, I'd recommend a divide and conquer approach.  There a lot of ways to divide this up.  My first impulse is to look at the following:

SEARCH FOR THE PROBLEM AREA 1:  INFORMATION SCHEMA
Search the information_schema for %tablename% to see if there are procedures, triggers, or foreign key constraints referencing that table.  Look to see what all those references are doing.  In the triggers and procedures I would look for DELETE on the table and if there are foreign key contraints affecting that table, I'd be looking to see what other tables having data deleted would delete records from the table where you entered data.

SEARCH FOR THE PROBLEM AREA 2:  APPLICATIONS TOUCHING THE DATABASE
I would search the source code for the tablename, again looking to see what each reference to the table is doing.  Depending how your application, if you have one, sets up its sql, you might be able to find the culprit by searching for "delete from tablename" to filter references that cannot delete anything.

SEARCH FOR THE PROBLEM AREA 3:  CRON
Cron could be running shell scripts referencing the database.  The cron statements could reference shell scripts that could be anywhere on a server with access to the database.

SEARCH FOR THE PROBLEM AREA 4:  PEOPLE
If other people have the permissions to have done this either directly or through their programs, how you handle this depends a lot on the nature of your responsibilities and theirs, and the relationships you have with them.  Asking around is usually a good idea, but you have to assume you might not get correct information that way. Taking away all permissions except new ones you assign is a good way to find out what is going on, but it is not always appropriate to take that sort of action, especially if you are junior to the person who permissions you eliminate.


Need help merging multiple full SQL Server Backups into single database Got a really strange issue with SQL Server database backup files I am getting backup files from the state of California for petroleum production.  Each year a database with the same name is created for the data for that year The database is always named, WellProductionInjection.  Each year the same tables with the same contents are created and populated.  The only difference is that the table names contain the year, so separate tables as supposed to exist for each year.  I can get what is labelled as a full backup file for a single year. But when I try to use SQL Server Management Studio to restore all the backup files to a single database, I get only the data for one year, or an error about the database already existing.  Any tips on how deal deal with merging multiple backups into a single database? Examples of the backup files can be put on the web if anyone is willing to try their luck with this.

SQL server doesn't have any built-in capacity to merge multiple backups into a single database, you'll have to restore each backup with a different database name (like WellProductionInjection_2019 and WellProductionInjection_2020), then do the work yourself to combine them however you need.
Once the databases are restored, you can combine them using a variety of tools; the simplest one is called the "Import/Export Wizard". You can use this to copy entire tables from one database to another (if all tables have different names), or to append the data from table in one database to the tables in the other (if you actually want to combine the data into the same table).


In the context of another principal what permissions hinge on the identity of the database owner? By "database owner", here, I mean the principal whose SID is sys.databases.owner_sid.

This answer to the question of best practices for database ownership includes the following:

  The answer here comes down to priorities: Convenience xor Security? Pick one.

  One issue is that the database owner is used to determine permissions for certain operations. The sa account, by its very nature, is not restricted from anything (outside of maybe placing restrictions on itself, but now we're just being silly). I'm not sure what that list of operations is, but using a low-privileged SQL Server login as the owner is definitely one way that people discover what those operations are ;-) (hence why so many people prefer to use sa, even if it is a poor choice; more on that in a moment).

This suggests that the permissions granted to the database owner are factored into permissions checks during operations invoked in the security context of other principals.  This sounds reasonable and I can imagine a circumstance where changing the database ownership resolved some permissions issue, but I have not seen that play out first-hand.

This answer seems to support this suggestion:

  the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually Service Broker users that discover this problem first.

This suggests that EXECUTE AS context could lead to unintended escalation of privileges to those of the database owner.

Microsoft's "Summary of the Permission Check Algorithm" does not, however, seem to mentioned the database owner:

  Security context

  This is the group of principals that contribute permissions to the access check. These are permissions that are related to the current login or user, unless the security context was changed to another login or user by using the EXECUTE AS statement. The security context includes the following principals:

  The login
  The user
  Role memberships
  Windows group memberships
  If module signing is being used, any login or user account for the certificate used to sign the module that the user is currently executing, and the associated role memberships of that principal.

Microsoft's Chart of SQL Server Permissions also doesn't seem to mention owner_sid (although a lot seems to hinge on the db_owner role, but that is a different matter).

Furthermore, Microsoft's "Understanding Execution Context" does not seem to make any special mention of the database owner.

This raises the following questions:

What permissions, if any, hinge on sys.databases.owner_sid?
Are there operations that SQL Server invokes using sys.databases.owner_sid to which the "SQL Server Permission Check Algorithm" does not apply?
Does EXECUTE AS ... context somehow allow a normal user to inherit the permissions of sys.databases.owner_sid?

I had meant to write a blog post where I explain this in detail (more than is appropriate here), with example scripts, etc, but just haven't had the time. But, rather than have this remain unanswered until I have time to write that post (test scripts are done but need to be cleaned up), I will post the basic info here and come back later to adjust once that post is published.

There are two related scenarios that I know of that are dependent on the permissions of the database owner:

Trying to access instance-level resources, or another database, while using either impersonation or an App Role
Trying to obtain either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (instance-level permissions) while using unsigned SQLCLR assemblies (and using either SQL Server 2005 - 2016 or SQL Server >= 2017 and not using the horrible "trusted assemblies" hack)

Both scenarios are related by being times when enabling TRUSTWORTHY is required. And, both are also scenarios that can avoid the evil TRUSTWORTHY ON by implementing the far better approach of Module Signing.

Scenario #1

is very similar to, but still not the same as, actually accessing instance-level resources or another database under impersonation. There is a lot of overlap as they are two parts of the same functionality. I would guess that most of the time impersonation is being used to become the dbo user via the EXECUTE AS clause of a CREATE {module} statement, specifying either OWNER or N'dbo'. In that case it's very difficult to separate the indirect requirement, but impersonating a non-dbo user makes it clear.

When impersonating a user (we're assuming a non-dbo user), in order to access instance-level resources or another database, not only does the current database need to be marked as TRUSTWORTHY, but the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have the AUTHENTICATE SERVER permission. This is not well known, which is likely why so many people simply resort to having sa own the database: it has all permissions. Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the impersonation working.

EXAMPLE:

Setup:

There is a database, [TestDB], that has TRUSTWORTHY set to ON.
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a login, P, that has an associated user P in database [TestDB]. Login P is not a member of sysadmin nor has CONTROL SERVER, but does have the VIEW SERVER STATE permission.
There is a stored procedure in [TestDB] named dbo.ViewAllSessionInfo that selects from sys.dm_exec_sessions and sys.dm_exec_requests, and is not filtered to only display the row for the current login or SPID / session_id.

Behavior:

Login A (the app login) can execute dbo.ViewAllSessionInfo, but only gets 1 row returned, the row for the current session.
The stored procedure is altered to contain the EXECUTE AS N'P' clause (P has the required permission).
Login A executes dbo.ViewAllSessionInfo, but still only gets 1 row returned, the row for the current session (even though the stored procedure is now executing as P, and P does have VIEW SERVER STATE permission, and the database has TRUSTWORTHY enabled).
Login O is then granted the AUTHENTICATE SERVER permission.
Now, when login A executes dbo.ViewAllSessionInfo, rows for all sessions are returned (even though the only new permission added was granted to O, and not to either A or even to P).

Scenario #2

occurs when creating or altering an assembly, or even trying to use any code (stored procedure, trigger, functions, or type) from within an assembly that has been marked as either EXTERNAL_ACCESS or UNSAFE. Additionally, starting in SQL Server 2017, if you keep the "security" setting of "CLR strict security" enabled (the default), then even assemblies marked as SAFE need to meet this requirement (again, we are assuming that neither module signing nor "trusted assemblies" is being used). In this case, assuming that a non-dbo account is attempting one of those actions, the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is more widely known than Scenario #1 (due to an error message that provides this information), yet many people simply resort to having sa own the database (again, everything "magically" works). Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the operation working.

EXAMPLE:

Setup:

There is a database, [TestDB], that has TRUSTWORTHY set to OFF (different than test case for Scenario #1).
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a SQLCLR assembly, C, in database [TestDB]. Assembly C has a PERMISSION_SET of UNSAFE.
There is a stored procedure in [TestDB] named dbo.DoUnsafeStuff that is really a .NET method in assembly C.

Behavior:

Login A (the app login) can execute dbo.DoUnsafeStuff, but gets a security error.
Database [TestDB] is altered to set TRUSTWORTHY to ON.
Login A can execute dbo.DoUnsafeStuff, but still gets a security error.
Login O is then granted the UNSAFE ASSEMBLY permission.
Now, when login A executes dbo.DoUnsafeStuff, the stored procedure operates as expected, without the security error (even though the only new permission added was granted to O, and not to A).

In both scenarios,

the required permission is not required for the principal performing the action. It's more of a gate-keeper to determine if the requested action is allowed to be performed by any principal within that database.


Polling for new records in a table We have a table that is append+read only. We'd like to have multiple applications consume all new rows that show up in that database, in a cheap and easy way (without change tracking).

The simple suggestion of having each app record the last ID doesn't seem safe. Suppose 3 connections (2 writers and 1 readers) do this:

W1: BEGIN TRAN, INSERT 1000 Records (IDs 1-1000)
W2: BEGIN TRAN, INSERT 1000 Records (IDs 1001-2000)
W2: COMMIT
R1: SELECT * WHERE Id > 0 (last sync value)
W1: COMMIT (or possibly ROLLBACK)

In this case, the reader would get records 1001-2000 and save 2000 as the highest ID. Obviously that's not correct. Assuming the various writers are running at varying speeds (so we can never assume they'll commit in order, due to latency or perf), what options are there?

Must we resort to something like change tracking, or require every application to save the last (batch*writers*x) IDs and re-read them to make sure they didn't miss any?

Rollback and other gaps in the sequence (MSSQL can jump the IDs by 1000; pretty sure other DBs do that under certain situations) make it complicated to try to guess if rows were skipped. Plus the batch inserts might not be a proper batch; they could consist of multiple INSERT statements, meaning each tx might not even have a contiguous block of IDs assigned.

We're currently using MSSQL 2012 if there's a hack specific to that system, but I'm more interested in general.

Some of the implementation specifics depend on exactly what type of behaviour you need from this table (e.g. FIFO), but a typical design for a queue table uses DELETE with an OUTPUT clause:

DELETE TOP (n)
FROM QueueTable WITH (ROWLOCK, READPAST)
OUTPUT deleted.RecordID;

Or if ordered removal is required:

DELETE FROM
(
    SELECT TOP (n) RecordID
    FROM QueueTable WITH (ROWLOCK, READPAST)
    ORDER BY RecordID
) AS DF
OUTPUT deleted.RecordID;

If the records should remain in the table after processing, the table would have an extra columns to record the status (e.g. New, Processing, Processed) and probably the process id owning the row (if Processing or Processed). The de-queuing code is then modified to choose only records with the desired status. An alternative design is to DELETE rows and move them to a separate audit/archive table at the same time. As I mentioned, the specific solution depends on the precise requirements.


Is it possible to have MySQL slave outside firewall For various reasons I need one of the databases from intranet MySQL server to be made available to (insecure) online server. Due to the requirements it was decided that a db dump will be used via cron to transfer data as the firewall does not allow any connections to be initiated from outside. Then MySQL Master-Slave solution was suggested. From what I have understood, the slave should be able to connect to the master via (preferably insecure) TCP protocol. Such a solution is preferable, however, via secure TCP connections without permitting the slave to initiate (possibly lost) connections to the master.

I would like to know if it is possible to have Master-Slave replication via secure TCP where connections are always initiated by the Master to the slave. The use case does not require too much data transfer, and data base size is within 5GB on a 1GBPS intranet. It is acceptable that there could be some delay in updating to the slave.

Rather than exposing your database's ports to the public network at all, I would recommend arranging for the services to be able to see each other by more secure means.

The SSH tunnel option already mentioned will work, though for reliability you'd want to look into making sure that the tunnel remained active after a network blip or other event (look into autossh, or hooking your SSH tunnel into init/systemd).

I would instead recommend a "proper" VNP solution. OpenVPN is common (available in just about all Linux distributions' standard repositories) and easy enough to setup (IMO), though there are many other options. If you setup a point-to-point VPN between the two hosts (or a more general arrangement if more machines need to take part) then the database instances will be able to connect to each other securely over that without needing to be exposed to the outside world.


Joining two processed tables without Primary Key I recently started such an exiting activity as digging in databases.
Straight to the point.
There are two tables. First one shows the current stock of products. I simplify it by following query:

SELECT *
FROM dbo.INDIVID
WHERE PAWHLO ='h550'--Specific warehouse

The second one is showing past, current and future orders.It contains hell lot of info including multiple orders of same SKU:
 SELECT DISTINCT P1MTRL AS SKU, SUM(P1UPL1) AS PLANNED_quantity
FROM dbo.PORDHV
WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
GROUP BY SKU
ORDER BY PLANNED_quantity DESC

Both of the queries work on themselves, but combining them is something I cant manage,
How can I combine those two queries in one,preferably outer joint, so I dont lose the data neither from stock nor future orders?
I tried:

SELECT DISTINCT PORDHV.P1MTRL,INDIVID.PAANTX AS ON_STOCK, PORDHV.P1UPL1 AS PLANNED
FROM dbo.PORDHV
FULL JOIN
 dbo.INDIVID
ON
PORDHV.P1MTRL=INDIVID.PAANUM
WHERE PORDHV.P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND PORDHV.P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
AND INDIVID.PAWHLO ='h550'
AND INDIVID.PAANTX  0
ORDER BY PLANNED DESC

But it apparently returns noncense.
Thanks in advance.

P.S.

I was asked to clarify.
I will leave the UNION, with which I managed to get an something the resembles what I need:

SELECT
  INDIVID.PAANUM as P1MTRL,
 SUM(INDIVID.PAANTX) AS AVAILABLE_QUANTITY,
 '' as PLANNED_QUANTITY,
 convert(date,getdate()) as Date
  FROM
  INDIVID
    WHERE PAWHLO ='h550'
  GROUP BY INDIVID.PAANUM
  --ORDER BY QUANTITY DESC
  union
   SELECT DISTINCT DS1.P1MTRL,
  '' as AVAILABLE_QUANTITY,
  SUM(P1UPL1) AS PLANNED_QUANTITY,
  convert(date,convert(varchar(10),P1PTF)) as Date
FROM dbo.PORDHV as DS1

INNER JOIN (
  SELECT DISTINCT P2ANR as P1MTRL
  FROM [POPPROD].[dbo].[PORDRDART]
  WHERE PMWHSL = 'H550'
  ) as DS2 on (
  DS1.P1MTRL = DS2.P1MTRL
  )

WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
--AND P1MAS2 in ('ph640','ph625')
GROUP BY DS1.P1MTRL, P1PTF
--ORDER BY SUM(P1UPL1) DESC
order by P1MTRL

In this way it returns the union of tables with several rows related to same SKU. I also added date to be able to play with periods in the future. Date for the row of current stock is TODAY, when there are two or more orders of the same SKU on particular date - they are summed up.

400011873   0       0       2018-03-02
400015114   0       0       2018-03-02
400052975   24894   0       2018-03-02
400052977   540     0       2018-03-02
400052979   0       12000   2018-03-05
400052979   8311    0       2018-03-02

How to get those rows of SKU's distinct. What would You improve and aggregate before going to Report Manager?

I'm not sure if this is what are you looking for.

I've set up next example:

CREATE TABLE INDIVID (PAANUM INT, PAWHLO VARCHAR(10), PAANTX INT);
INSERT INTO INDIVID VALUES (1, 'h550', 100),(2, 'h560', 300),(3, 'h550', 50);

CREATE TABLE PORDHV (P1MTRL INT, P1STAT CHAR(1), P1PTF VARCHAR(10), P1UPL1 INT);
INSERT INTO PORDHV VALUES
(1, 'A', '20180301', 50),
(1, 'T', '20180401', 50),
(1, 'A', '20180401', 75),
(1, 'A', '20180501', 60),
(2, 'A', '20180301', 50),
(2, 'A', '20180401', 75),
(3, 'A', '20180301', 50),
(1, 'A', '20180401', 75);
GO

If you're trying to get all orders, order by quantity you can use a CROSS APPLY solution:

SELECT    PAANUM AS SKU,
          PAANTX AS ON_STOCK,
          P.PLANNED
FROM      INDIVID
CROSS APPLY (SELECT P1UPL1 AS PLANNED
             FROM   PORDHV
             WHERE  P1MTRL = INDIVID.PAANUM
             AND    P1STAT NOT IN ('E','T','P','R')
             AND    PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD?
            ) P
WHERE     PAWHLO = 'h550'
AND       PAANTX > 0
ORDER BY  PAANUM, PLANNED DESC
GO

SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |      75
  1 |      100 |      75
  1 |      100 |      60
  1 |      100 |      50
  3 |       50 |      50

But it you want the sum of all orders by SKU you can sum all orders of each SKU and then LEFT JOIN with INDIVID table.

SELECT    PAANUM AS SKU,
          PAANTX AS ON_STOCK,
          P.PLANNED
FROM      INDIVID
LEFT JOIN (SELECT   P1MTRL, SUM(P1UPL1) AS PLANNED
           FROM     PORDHV
           WHERE    P1STAT NOT IN ('E','T','P','R')
           AND      PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD?
           GROUP BY P1MTRL) P
ON        P.P1MTRL = PAANUM
WHERE     PAWHLO = 'h550'
AND       PAANTX > 0
ORDER BY  PAANUM;
GO

SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |     260
  3 |       50 |      50

dbfiddle here


Design SQL Server 2008 new install ... which collation? A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS, with 8 databases ... for a new application, we have been asked to install a SQL Server 2008 with collation SQL_Latin1_General_BIN.

In order to maintain a single database server, and then also by addressing the previous db migration to the new version, which instance collation should be adopted?

Thanks a lot

Bye,
Francesco

The default collation is based on the Windows system locale of your server. SQL_Latin1_General_CP1_CI_AS is usually the default collation for computers based in the North American locale. Collation can be changed if you have a reason for it, like: sorting order, case sensitivity, accent sensitivity, non-English characters, width.

Collation for a DB can be set at the time of the creation of the DB, so you can have a server that has DBs with different collations. The Alter database command can be used to change the collation of all newly created objects in the DB. I have used the default SQL_Latin1_General_CP1_CI_AS all the time and have not had any issues, but then I do more DB administration that development.


having problem in insering date in mysql database On inserting date in table date is being displayed as 0000-00-00

$content = file_get_contents("http://api.themoviedb.org/3/movie/$id?api_key=c1259239b41c48480d727f85c677fbf6");

$moviedetailsArray = json_decode($content,true);

$budget = $moviedetailsArray['budget'];
$rlsdate = $moviedetailsArray['release_date'];

$query = "UPDATE `latestmoviesdetails` SET `budget` = $budget,`releasedate` = $rlsdate WHERE movieid = $id";

mysqli_query($link,$query);

Is this Perl code?

I think the problem is that SQL doesn't recognize the date format. Just before doing the query, insert a print statement to see what exactly is in $rlsdate.

MySQL expects date in this format: '2019-03-13 16:35:42'

If your date is in unix timestamp format, you can use FROM_UNIXTIME() in your query:

$query = "UPDATE `latestmoviesdetails` SET `budget` = $budget,
                 `releasedate` = FROM_UNIXTIME($rlsdate)
          WHERE movieid = $id";

If it's in a date format that is not YYYY-MM-DD, use STR_TO_DATE() function. Example:

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'


Get list of permisison for a SQL role I have a role named db_executor and need to find out what permissions it has on what objects. I found List all permissions for a given role? but the only solution that remotely works is:

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'db_executor'

The problem is that the ObjectName and ObjectType is NULL. So while I know it only has the EXECUTE permission in a state of GRANT I don't know to what object(s) that applies to. Is there a better way to get this list or how can I modify this code to list the Object(s)?

The script is missing a left join to sys.types to grab table type information, which also requires execute permissions to use (alternatively join sys.table_types, you'll get the same data). Try the following:

SELECT  DB_NAME() AS 'DBName' ,
    p.[name] AS 'PrincipalName' ,
    p.[type_desc] AS 'PrincipalType' ,
    p2.[name] AS 'GrantedBy' ,
    dbp.[permission_name] ,
    dbp.[state_desc] ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[name], t.name)
    END AS 'ObjectName' ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[type_desc], N'TYPE')
    END AS 'ObjectType'
FROM    [sys].[database_permissions] dbp
        LEFT JOIN [sys].[all_objects] so ON dbp.[major_id] = so.[object_id]
        LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id]
        LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]
        LEFT JOIN [sys].[types] t ON dbp.major_id = t.user_type_id
WHERE   p.[name] = 'db_executor';


Datatype for different categories in a database I have got the following categories for accounting and cash flow purposes:

Equity
     Contribution
     Distribution
     Valuation
Debt
     Issuance
     Payment
     Valuation Allowance
Interest
     Accrued
     Paid
     Valuation Allowance

I have broken them into 3 tables: equity, debt, and interest. Each table has the following columns:

ID
Date
Amount
Type

My question is, within the "type" column, should I be using a number, i.e. 1,2,3, or text to indicate the value?

Speaking from the programming side of the house, I would do a numerical value that is a foreign key to a "type" table. This way if you change the text value, all previous entries automatically update, and adding new types is just a matter of adding them into the table.

This has the added benefit that if you do something like an interface with a dropdown list to select the value, it also automatically updates and there are no coding changes needed to update the list.


Oracle Triggers - before any UPDATE or DELETE or INSERT I am trying to create a database trigger in Oracle SQL Developer that would fire before any UPDATE, DELETE, or INSERT statement occurs on the sessions table.

My sessions table:

CREATE TABLE sessions (
SessionNo NUMBER (2,0),
SessionTime VARCHAR2 (5),
SessionDay VARCHAR2 (20),
SessionDate DATE,
SpecID VARCHAR2 (2),
RoomNo CHAR (3),
BuildingNo CHAR (2),
FormID CHAR (4));

My example insert statement:

INSERT INTO sessions VALUES (1, '9', 'Monday', TO_DATE ('02/06/2016', 'mm/dd/yyyy'),
                        12, 108, 'M', 0050);

This trigger is supposed to check the date and time attributes, and if the day is a Saturday or a Sunday, or if the time is outside the school business hours (9am to 3pm), a message:

  Out of business hours – this transaction has not completed, please change the
  date/time

...should be displayed.

I do not think that the check is needed on delete. So create a trigger like:

CREATE OR REPLACE TRIGGER check_session_timing
  BEFORE INSERT OR UPDATE ON sessions
  FOR EACH ROW
BEGIN
    if   :new.SessionDay = 'Saturday'
      or :new.SessionDay = 'Sunday' then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
    if :new.SessionTime not between 900 and 1500 then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
END;
/


If else with 5 possible cases PostgreSQL I have bunch of records in Database and i have been searching for query on NULL json object and i got that right,but now its difficult for me to continue it on 5 possible cases

For example: am showing data on basis of 5 cases and 3 attributes

config->'external_http_port' , config->'external_host' , config->'snapshots'->>'jpg'

cases are

1: config->'external_http_port' can be null

2: config->'external_host' can be null

3: config->'snapshots'->>'jpg' can be null

4: All three can be null

5: two of three can be null.

for just checking all three nulls i am doing query as

select * from cameras where (config->'external_http_port') is null and (config->'external_host') is null and (config->'snapshots'->>'jpg') is null

any help will be appreciated

Using PostgreSQL 9.3

Despite the long comments, it's still not clear if you need one query for each case or a single query that includes all five of them. So, we'll take them separately:

config->'external_http_port' is null

select * from cameras
where config->'external_http_port' is null ;

config->'external_host' is null
(omitting the select):

where config->'external_host' is null ;

config->'snapshots'->>'jpg' is null

where config->'snapshots'->>'jpg' is null ;

all three are null (you got that right!):

where config->'external_http_port' is null
  and config->'external_host' is null
  and config->'snapshots'->>'jpg' is null ;

two out of three are are null (and the third isn't)

where (config->'external_http_port' is null)::int
      + (config->'external_host' is null)::int
      + (config->'snapshots'->>'jpg' is null)::int
      = 2 ;

And if you want all five cases in one query, i.e. any of the five conditions to hold, this can be written (using a complicated condition, similar to the 5th case above):

    where (config->'external_http_port' is null)::int
          + (config->'external_host' is null)::int
          + (config->'snapshots'->>'jpg' is null)::int
          >= 1 ;

or more simply:

    where config->'external_http_port' is null
       or config->'external_host' is null
       or config->'snapshots'->>'jpg' is null ;


Database Mirroring error, but everything looks OK I'm setting up a Mirroring study session in separated servers.

Everything looks ok, the firewall in my machine is disabled and in the another server it has an inbound\outbound rule to accept port 5022.

I could check that both endpoints are created and STARTED in both servers
with SELECT * FROM sys.database_mirroring_endpoints.

I tried with both configurations, by using services account, and leaving them blank.

I checked with port query that the server is listening on this ports:

What more can I check?

The reason I'm asking this question is because I read every other question about mirroring here, but none of them could help me.

In a post, I read that someone fixed the error with:

  Go to services.msc and check the sql server is running under which
  account. Make sure that sql server and sql server agent services
  should run with same credentials.

But I use different credentials for AGENT and SQL SERVER.

Is this a problem? I've never seen this before.

EDIT2:

I could fix this error ( it still shows me this error, but if I run the mirror again, it shows me error 1431.)

Using ALTER DATABASE [Database_Name] SET PARTNER OFF didn't solve the problem. it just restart the task, and errors and errors again.

Have you granted the CONNECT permission to the Mirroring Endpoint for the SQL Server service account?

GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [domain\user];


Reseed identity column in all empty tables I need to write a query that resets the identity on all tables that have an identity column and are empty.

Since you can reset the IDENTITY by issuing a simple TRUNCATE:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Though this will only work for tables not referenced by a foreign key. If you have foreign keys referencing these tables (likely), then you need:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DBCC CHECKIDENT(N''' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N''', RESEED);' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Always-On Configuration Clarifications Do we have to Install stand-alone instances of SQL Server 2012 or Failover-Cluster SQL Server Instances while configuring Always-On?

I am asking this question because I saw few configurations using stand alone instead of Clustered SQL Server Instances.

And when we say:

  Always-On will support up to two secondary replicas synchronously replicated per availability group with Automatic Failover.

Does it mean:

  A Database configured for Always-On HA/DR can be in synchronous mode and can be failed over to Two replicas(Two other SQL Server Instances)

Maybe I am getting confused but can you please explain about Always-On supporting two secondary replicas synchronously with Automatic Failover?

Be careful with the term Always ON, This refers to Always On Failover Cluster Instances and Availability Groups (Also database mirroring but this is deprecated). FCIs and AGs are very different technologies.

To answer your question AGs can be installed on stand alone and clustered instances. It all depends on your HA/DR requirements.

2nd question:
Yes AGs can automatically failover their databases to other SQL instances (refered to as nodes). The servers running the SQL instances all need to be in the same windows failover cluster.

This white paper from Microsoft might be a good place for you to start reading on AGs.


Login failed for user - Error 18456 - Severity 14, State 38 Message that SQL Server Log File Viewer shows:

Login failed for user [User]
Error: 18456, Severity: 14, State 38

What it actually means:

Failed to open the explicitly specified database

My Question:

Is there a list somewhere of all the variations of the 18456 errors (Login failed), for each combination of severity and state, with the helpful description text?

I've had a Google but can't find anything other than specific combinations.

State codes and their meaning.
1      'Account is locked out'
2      'User id is not valid'
3-4    'Undocumented'
5      'User id is not valid'
6      'Undocumented'
7      'The login being used is disabled'
8      'Incorrect password'
9      'Invalid password'
10     'Related to a SQL login being bound to Windows domain password policy enforcement.
        See KB925744.'
11-12  'Login valid but server access failed'
16     'Login valid, but not permissioned to use the target database'
18     'Password expired'
27     'Initial database could not be found'
38     'Login valid but database unavailable (or login not permissioned)'

More detailed information is available in Aaron Bertrand's blog.
To find the status code, you need to inspect the SQL Server Error Log after ensuring that failed logins are recorded in the error log.


Determining minimal cover of a relation I am having trouble understanding why a certain set of functional dependencies is not minimal.
We have a relation R(A,B,C,D,E,F,G) with the following dependencies F:

1. A->CDE
2. B->FG
3. AB->CDEFG

Minimal cover of F is just dependency 1 and 2. It is intuitive that attributes CDEFG are already determined by A and B separately. Hence, no new attribute is determined by union of AB. Is there an exact rule that determines that such dependency (union of AB->CDEFG) is redundant?

The Armstrong rules listed so far in the book are:

    IR1 (reflexive rule): If X ⊇ Y, then X →Y.
    IR2 (augmentation rule): {X → Y} |=XZ → YZ.
    IR3 (transitive rule): {X → Y, Y → Z} |=X → Z.

and

    IR4 (decomposition, or projective, rule): {X → YZ} |=X → Y.
    IR5 (union, or additive, rule): {X → Y, X → Z} |=X → YZ.
    IR6 (pseudotransitive rule): {X → Y, WY → Z} |=WX → Z.

I am not sure which rule is responsible for redundancy of AB->CDEFG. Union rule seem close, but the left-hand side attributes are listed as being the same (both X), which I can't say for FD1 U FD2 in my case.

We can derive AB → CDEFG in this way:

1. A → CDE (given)
2. B → FG  (given)
3. AB → BCDE (by augmentation of 1)
4. AB → AFG (by augmentation of 2)
5. AB → ABCDEFG (by union of 3 and 4)
6. AB → CDEFG (by decomposition of 5)

Note that in general to find a minimal cover is not necessary to perform some proof using the Armstrong’s axioms, but is sufficient to use one of the algorithms presented in database books.


How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL? I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege.

db=> create role replicator login password 'something';
CREATE ROLE
db=> alter role replicator replication;
ALTER ROLE
db=>

However, when I try to run pg_basebackup from the target host to create the replica, I get a pg_hba.conf error:

# pg_basebackup -D /data/from-master -U replicator -h db-master.example.com -v
pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL on
FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL off

The problem is that, this being RDS, I can't get to the pg_hba.conf at all.  Any ideas?

You can use AWS Database Migration Services (DMS) to replicate full and/or change data to another system (such as S3 or a PostgreSQL database running on-premises or on EC2)

make sure use DMS v3.3 if you use PG 10+
create a new pg parameter group. set:
rds.logical_replication=1
save. modify the RDS instance to use that parameter group, apply changes
reboot the RDS instance

You'll need a VPN connection if your PG instance is on-prem (not on AWS network)

DMS supports DDL changes too

You can use AWS Schema Conversion Tool to migrate your schema from source to target


SQL Server "fix" query for Hash Match instead of Nested Loops At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened.

To debug what was going on I used an old dataset and compared both queries. On the old dataset the view returns the data within a second.

I noticed one difference: the current dataset runs on "nested loops" while my old dataset runs with a Hash Match.

You can see that in the picture:

I'm aware of the fact that you simply cannot "force" it to use Hash Match as I saw in other topics, yet I'm having a really hard time finding out why its going for a nested loop.

Is this because something in my dataset is not unique?

How can I fix my current dataset to start using this Hash Match again?

If you are going to this you should at least use the OPTION ( HASH JOIN ) syntax instead.  Using the LEFT HASH JOIN syntax implicitly enforces the join order.

This is not desirable for hash joins where normally you would expect the smaller of the two tables being joined to be the build input, the top-most table in the execution plan.  Using the option syntax allows the optimizer to decide at run-time which is the best join order.  See these two articles for further reading:

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

https://msdn.microsoft.com/en-GB/library/ms173815.aspx


Concurrency question? I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about displaying a label indicating that another user is also viewing the same record?

I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.


How do I measure performance of a single sql execution in oracle I need to measure the duration of individual sql executions in Oracle DB.

I have the following query but it has two major problems.

SELECT
   instance_name,   host_name,
sql_id, sql_fulltext, plan_hash_value,
    CASE
        WHEN executions > 0
        THEN ROUND(elapsed_time/executions,3)
        ELSE NULL
    END elap_per_exec,
    elapsed_time,
    executions,
    PHYSICAL_READ_BYTES/1024 read_kb,
    buffer_gets,
    rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5

It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.

Is the any way to get the performance for a single execution?

Generally not possible. You will have to time the execution of a new instance of that statement.

If you are lucky and the statement was running long enough to be sampled by ASH it should however show up in dba_hist_active_sess_history or for very recent invocations V$ACTIVE_SESSION_HISTORY.

It should show CPU time separate from DB time.


Oracle Internal Error Getting oracle Internal error in oracle database 11gr2:

  ORA 600 [kkpogppi1]

It shows everyday in incidents but whenever i check oracle logs it shows nothing and when i open problem details it shows me:

So is this internal error and if yes then how could i solve this error and also is this any harmful to database?

The wording on this error might as well be:

  ORA-00600: "Something" is broken in the database.

It "recently" gained a close "cousin":

  ORA-07445: "Something" is broken, but not necessarily in the database itself.

If there's nothing immediately obvious in the logs, raise a Support call with Oracle.


MySQL command "SHOW DATABASES" returns invalid databases I have a MySQL server with a number of databases on it - 16 if I'm not mistaken.

When I run the SHOW DATABASES command I get 30 rows.

I would have expected 17 rows: The 16 databases and "information_schema".

I do however get 13 rows where the database name is prefixed and postfixed with a "?".
Does anybody know what that means?

When I look at the database folder I see my 16 databases, but also the 13 weird databases but here they are pre- and postfixed with @feff

Does anybody know what that means?

I have no idea how these databases got there - I just want to get rid of them.

Can I just delete the database folders?

You need to make sure they do not contain any tables

Please run this query

select
    table_schema AS BlankDatabases
from
    (select
        A.table_schema, COUNT(B.table_schema) table_count
     from
        (select
            schema_name AS table_schema
        from
            information_schema.schemata
        where
            schema_name not in ('information_schema','mysql','performance_schema')
        ) A
        left join
        (select
            table_schema
        from
            information_schema.tables
        where
            table_schema not in ('information_schema','mysql','performance_schema')
        ) B
        using (table_schema)
    group by
        A.table_schema
    ) AA
where
    table_count = 0;

This will tell you which databases do not have any tables

For example, let's say you run that query and get the following output

mysql> select table_schema BlankDatabases from
    -> (select A.table_schema,COUNT(B.table_schema) table_count from
    -> (select schema_name table_schema
    -> from information_schema.schemata
    -> where schema_name not in
    -> ('information_schema','mysql','performance_schema')) A
    -> left join
    -> (select table_schema
    -> from information_schema.tables
    -> where table_schema not in
    -> ('information_schema','mysql','performance_schema')) B
    -> using (table_schema)
    -> group by A.table_schema
    -> ) AA where table_count = 0;
+----------------+
| BlankDatabases |
+----------------+
| junk           |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql>

You can just run

DROP DATABASE junk;
DROP DATABASE test;

In your case, you should surround the database names with back quotes (`)

DROP DATABASE `@feff...@feff`;

If you are having a hard time, go to the OS and remove the folder.

For example, if your datadir is /var/lib/mysql and you are removing @feff...@feff, do this

cd /var/lib/mysql
rm -rf "@feff...@feff"

GIVE IT A TRY !!!


Understanding a PostgreSQL join I have seen the following query in the PostgreSQL documentation. Unfortunately I don't understand the join criteria.

create temp table tmp_stat_user_tables as select * from pg_stat_user_tables;

select * from pg_stat_user_tables n
join tmp_stat_user_tables t
on n.relid=t.relid
and (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <>
(t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

How I need to understand the JOIN clause?

What the tables are for and what the first query is doing are clear for me.

I assume:

(n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <>
(t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del)

is what puzzles you. It means that if any of the columns differ the predicate is true. It is a shorter form of:

NOT ( (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) =
      (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del) )

which in turn can be rewritten as

NOT (  n.seq_scan = t.seq_scan AND
       n.idx_scan = t.idx_scan AND
       ...                          )

not sure if it makes it easier to grasp, but it can also be written using disjunctions

    (  n.seq_scan <> t.seq_scan OR
       n.idx_scan <> t.idx_scan OR
       ...                         )

EDIT:

Nulls makes logical reasoning difficult so I created a small example to verify the logical equivalence of the expressions above. I think it is sufficient to use only two variables (http://sqlfiddle.com/#!15/73c52/2):

create table s (x int);
insert into s(x) values (null),(0),(1);
create table t (x int, y int);
insert into t(x,y) select s1.x, s2.x from s s1 cross join s s2;

The following expressions corresponds to the ones I used above:

select (t1.x,t1.y)<>(t2.x,t2.y) as a
     , not( (t1.x,t1.y)=(t2.x,t2.y)) as b
     , not ( t1.x = t2.x and t1.y = t2.y ) as c
     , t1.x <> t2.x or t1.y <> t2.y as d
from t t1
cross join t t2;

a       b       c       d
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
false   false   false   false
true    true    true    true
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
false   false   false   false
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
false   false   false   false
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
false   false   false   false


SQL temporary variable query Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery  AND typeId = (SELECT @id = typeId FROM EmpType, near @id =.  I'm looking at getting the typeid in a variable and use it in the next subquery following this one, not sure if that is not allowed.  Its SQL Server 2012.

Employee:

  empId empName
  1     Jim
  2     Sam

EmpType:

  typeId  type
  1         Full Time
  2         Contractor
  3         Temporary
  4         Work Permit

FamilyTree:

  familyId  familyName
  1         APAC
  2         Technology-APAC
  3         Domain-EU
  4         Platform-US

EmployeeMap:

  empId   typeId    familyId
  1         1       1
  1         2       2
  2         2       3
  2         3       3

Query:

  DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor';
  DECLARE @family VARCHAR (50) = 'Domain-EU';

  SELECT * FROM  EmployeeMap
  WHERE empId = @empId
  AND typeId = (SELECT @id = typeId
                FROM EmpType
                WHERE type like @type
                AND familyId = (SELECT familyId
                                FROM FamilyTree
                                WHERE familyName like @family))

You can rewrite this using JOINs rather than subselects to get what should be the correct answer.

This is assuming you are aiming to set the @id in this query and carry it forward to another query.

In this situation, the LIKE is only useful if you have a case sensitive collation. If you have case insensitive then you can just use =.

 DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor';
  DECLARE @family VARCHAR (50) = 'Domain-EU';

  SELECT TOP 1 @id = et.typeid
  FROM  EmployeeMap AS em
  INNER JOIN EmpType AS et ON et.typeId = em.typeId
  INNER JOIN FamilyTree AS ft ON ft.familyId = em.familyId
  WHERE empId = @empId
    AND type LIKE @type
    AND familyName LIKE @family;


How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL I have three tables with these structures:
1) z_test_a(c_num   number , <other columns>)
2) z_test_b(c_num   number , Amount  number , <other columns>)
3) z_test_b(c_num   number , Amount  number , <other columns>)

Inspired by one the answers in this post: https://stackoverflow.com/questions/2514254/how-can-i-create-a-dynamic-where-clause , I've learned that one way to use Dynamic Query and still use Bind variables is to write your query with With clause. I tried to apply this approach with Intersect but I could not succeed and my procedure does not work.It ends up giving me this error:ORA-00904:"B"."AMOUNT1" invalid identifier for this input parameter:
1)i_table_names:z_test_b,z_test_c
2)i_table_names:1000
3)i_amnt_second:1000

Is this even possible to use this approach when there is intersect or union or the query must be as simple as it is in the post mentioned above? And is there a better way to write this procedure?Maybe it can be written without dynamic query .
My procedure is:
create or replace procedure bind_variable_intersect(i_table_names in varchar2,
                                                    i_amnt_first  in number,
                                                    i_amnt_second in number,
                                                    o_out         out sys_refcursor) is

v_base_query varchar2(2000) := ' with binds as
                                   (select :bind1 as amount1,
                                           :bind2 as amount2
                                    from dual)
                                 select t.c_num
                                 from z_test_a t , binds b where 1=1  ';

begin

-- Check input parameter " i_table_names "
if i_table_names like '%z_test_b%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_b where amount = b.amount1  ';
end if;

if i_table_names like '%z_test_c%' then
   v_base_query := v_base_query ||
                ' intersect select c_num from z_test_c where amount = b.amount2  ';
end if;

-- Debug Code
dbms_output.put_line(v_base_query);

-- Execute
open o_out for v_base_query
   using i_amnt_first,i_amnt_second;

end;

You got it wrong, I'm afraid. This is what your query evaluates to:
with binds as
  (select :bind1 as amount1,
          :bind2 as amount2
   from dual)
select t.c_num
  from z_test_a t,
       binds b               --> this is "B"
  where 1 = 1
intersect
select c_num
  from z_test_b
  where amount = b.amount1   --> you can't reference "B" here

Instead of intersect, you could:

use :bind1 in z_test_b, i.e.
intersect
  select c_num
  from z_test_b
  where amount = :bind1

or join z_test_b with z_test_a and binds (but I presume that former suggestion is simpler because of possible where clause; currently, it is useless (where 1 = 1), but I presume that it is here just for illustration. Something like this:
-- CTE you already have; remove WHERE from it
with binds as
  (select :bind1 as amount1,
          :bind2 as amount2
   from dual)
select t.c_num
  from z_test_a t,
       binds b
                      --> WHERE's not here any more; it is moved to join

-- JOIN
, z_test_b z
where z.amount = b.amount1       --> moved here


SQL Server CPU usage is overloaded sometimes We do have a couple of servers, one is dedicated to the website without SQL and the other server is dedicated to SQL.

Now the server running SQL is quite powerful, yet sometimes the server CPU is just maxed out at 100%.

Here is a couple of screenshots showing whats going on.

CPU maxed out:

CPU running ok:

As you can see the server is quite powerful.

Additional notes.

We are running nopcommerce version 3.70
The website has been heavily customized, by other developers.
The website has about 4000 - 5000 products.
When the cpu is maxed out load times are shocking. > 30 seconds and sometimes more than 1 min.

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Cheers

UPDATE:
The following two screenshots are the results from the two queries @S4V1N suggested I run.

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Let's start with the obvious stuff. Your server, for a production SQL box, isn't that highly powered. For example, this is what I do development work on, and I'm just a consultant goofing around most of the time.

That being said, more hardware may not solve all your problems. I'm not familiar with the platform you mentioned, but typically when you start with a base product, and you start referring to it as "highly customized", that means the code and indexes that used to work with the base product probably don't work so well anymore.

What can you start with?

The company I work for writes free scripts that can help you get to the root of these things. Whether it's something you can fix with hardware, or with vendor support is another matter.

A lot of vendors don't like you making changes to their products. But hey, at least you can start a conversation about making changes.

Run sp_Blitz

EXEC sp_Blitz @CheckUserDatabaseObjects = 1, @CheckServerInfo = 1;

This will give you a general idea about what's going on health-wise with your server. Pay attention to a couple settings here: MAXDOP and Cost Threshold for Parallelism, and see my answer here for why they can make a difference in your situation.

Run sp_BlitzFirst

EXEC sp_BlitzFirst @SinceStartup = 1;

This will tell you what your server has been up to since it started up. Look at the wait stats pane to see where your bottlenecks are.

Run sp_BlitzCache

EXEC sp_BlitzCache @SortOrder = 'cpu';

Since you're concerned about your plan cache, look at it by CPU first. You may find other sort orders helpful down the line, but start here.

We'll warn you about all sorts of stuff in your query plans, and give you as much historical information as possible.

Run sp_BlitzIndex

EXEC sp_BlitzIndex @DatabaseName = N'YourDatabaseName', @Mode = 4

The main thing you'll want to look at here is high value missing indexes, which should be right up at the top.

When you catch cpu spiking, run sp_BlitzWho

EXEC sp_BlitzWho

This'll let you know what queries are running when CPU is high. They may be different from what's in the plan cache.

Hope this helps!


sqlcmd :out not working as expected? Consider the following code:

DECLARE @db sysname;
DECLARE @filename varchar(260);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = 'C:\temp\create_database_' + @db + '.txt';
    :setvar c @filename
    :out $(c)
    PRINT $(c);
    FETCH NEXT FROM cur INTO @db
END
CLOSE cur;
DEALLOCATE cur;

I'm expecting to get multiple files created in C:\temp, one for each database, but nothing is created, and no errors are reported by SQL Server.

I've tried running this in SSMS using SQLCMD mode, and I've also tried running it from sqlcmd.exe

Using SQLCMD in this way was new to me and got me searching the Internet for examples.

After studying the information in SQL Server Management Studio: Hacking SQLCMD Mode and pondering the limitations of SQLCMD variables, I took a slightly different approach to your problem. My solution may not fully answer your question about why :OUT is not working as expected, but I'll offer it up anyway.

My full script is at the end of my answer, but here are some fragments with explanations.

I started out by redirecting the output to my local user temp directory

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:\Users\uswsh\AppData\Local\Temp'
:OUT $(TEMP)\DBScript.sql

Now, we invoke the cursor to read through the list of databases.  I'm using the PRINT command to generate operating system echo commands to create the individual database create files.  The output of the entire cursor execution is written to the DBScript.txt file in my user temp folder.

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:\temp\create_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

Upon execution of the above, the DBScript.txt file now contains these commands (The double bang at the beginning of each command will tell SQLCMD to execute these as operating system commands):

!!echo nul > C:\temp\create_database_master.txt
!!echo nul > C:\temp\create_database_tempdb.txt
!!echo nul > C:\temp\create_database_model.txt
!!echo nul > C:\temp\create_database_msdb.txt
!!echo nul > C:\temp\create_database_Test.txt
!!echo nul > C:\temp\create_database_AdventureWorks2014.txt

Now, back to the original script, we revert the SQLCMD output to stdout and execute the commands in DBScript.txt

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)\DBScript.sql
GO

When I look at my C:\Temp directory, I now have the following individual files:

Here is the full script:

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:\Users\uswsh\AppData\Local\Temp'
:OUT $(TEMP)\DBScript.sql

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:\temp\create_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)\DBScript.sql
GO


how to quickly insert data as select In an Oracle DB have about 200M rows (about 20GB data) that I would like to insert into another table by

insert into foo select * from bar where some_unimportant=condition;

How do I do that the fastest way if I can always drop the foo table and redo the insert in case of any problem? Like, I don't need to be able to do a rollback.
Since I can drop the foo table, I have complete control over its creation also, if there are some good options to use to speed up the insert.

Of course, after the inserts, I wish the table to behave as an ordinary table (support undo by using rollback, etc.)

UPDATE: The table was created as an IOT, so I guess that changes things. Should I run

 alter index foo_pk nologging;

also?

Improving INSERT Performance with Direct-Path INSERT

Example:

alter table foo nologging;
alter session force parallel query parallel 8;
alter session force parallel dml parallel 8;
insert /*+ append */ into foo select * from bar where some_unimportant=condition;


Performance differences between RID Lookup vs Key Lookup? Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?

Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)

Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?

However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.

I blogged about this recently, and I'm reproducing the content here to avoid a  comment answer.

CREATE TABLE el_heapo
(
    id INT IDENTITY,
    date_fudge DATE,
    stuffing VARCHAR(3000)
);

INSERT dbo.el_heapo WITH (TABLOCKX)
      ( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)

CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);

We can look at the table with sp_BlitzIndex

EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
                              @SchemaName = 'dbo',
                              @TableName = 'el_heapo';

This query will produce bookmark lookups.

SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);

Now we can cause some forwarded records to occur:

UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)

BlitzIndex will show them to us:

And if we re-run the lookup query:

Profiler will show a difference as well:


Differential databases (not backups) When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own writeable copy.

Is something similar available in Microsoft Sql Server?

The situation I am in is we host a demo copy of our product that our clients can connect to for about 30 days or so. When we create a demo account we have to create a new copy of the base database the program uses and have the software point at that. Each image is a little over 2GB, but on average the demo users will only change about maybe 100MB worth of data in the database.

What I would like to do is have a read-only database that acts as a base then have the demo databases create a "differencing database" and write out it's data and log information to that differencing database. Is this possible? I have been searching through the MSDN documentation but I have yet to find anything and unfortunately google searching has been fairly useless as searching for Sql server differential database gets polluted with all the pages about doing differential backups (doing Sql server differential database -backup did not return any useful results either, all I got was information about differential deployment scripts).

The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot.

You do have space savings between the snapshot and the DB, because the copy-on-write semantics of the snapshot. But unfortunately this only works on a single customer, you cannot have one snapshot serving as origin for multiple writable customers, which is what you ask. Snapshots can server something like a demo boot, where your restore it every morning.


Azure SQL database query performance degradation I have a Azure SQL database (compatibility level = 150). I'm running into a performance problem executing a database query.
So after I've rebuilt all my indexes, the query runs fast. When testing, with different parameters, all of a sudden it slows down. The same query with the same parameters is fast in the beginning, and slow after I do around 50 tests.
Now when I purge the Query store data via the UI or via SQL:
ALTER DATABASE [mydb] SET QUERY_STORE CLEAR;

the performance is back.
I've tried all settings on the Query Store, the problem remains, only deleting the data collected gives me back the initial performance.
Ok, so executing DBCC FREEPROCCACHE WITH NO_INFOMSGS; had the same performance impact as clearing the query store.
The query is built form my data model in Power BI (direct query). It can be fast for all possible parameter combinations. I tested all of these when the cache was recently cleared.
Power BI sends the query to Azure SQL db with a syntax for parameterized queries.
I just becomes slower and slower.
So is it the right solution, optimize for unknown, knowing that it can be fast for that same parameters?
Underneath the that is called from the app, I have 3 table-valued functions. I changed the 3 off them with OPTION (OPTIMIZE FOR UNKNOWN), update the statistics and rebuild all indexes. I must say the performance stays, which is good, although it's still a bit black magic for me.

After discussion, the issue here is that the optimizer in SQL Server/SQL Azure currently "sniffs" current parameter values when compiling the query.  The query plan is then cached and used on future executions of the query.  The plan that is generated may be more optimal for the originally sniffed value, but if other values are more commonly used at execution time (or, more broadly, if the impact on the rest of the query is different statistically such as one value being uncommon when sniffed vs. more common cases being sniffed), then this can be a source of runtime variance in a data application.  There are a few knobs to control this at different scopes, but using the option OPTIMIZE FOR UNKNOWN query hint will cause the optimizer to not sniff.  This will generate a query plan that is more generic (but still usually reasonably fast).
The original question suggested that clearing out the query store "fixed" the problem, but this was not actually the real cause.  It so happens that clearing the query store will force recompiles for all the query plans it was tracking on the next execution of each query.  That just allowed the optimizer to sniff a different value and, at least for the customer's experience, it happened to be better in that experiment.  Anything that forces a recompile would lead to a similar result.  You could do DBCC FREEPROCCACHE (empties the plan cache), reboot your server, force/unforce the plan for the query in query store, any metadata change to the database or tables involved in the query, etc.


Why does constructing this jsonb_array from two jsonb objects fail? Just trying to answer someone's question, and I'm wanting to run this by someone else. Constructing a jsonb array with two key value pairs.

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'::jsonb
  ))
) AS t(x);

Getting this error

ERROR:  invalid input syntax for type json
LINE 5:         '{"k1":"v1","k2":"v2"}'::jsonb
                ^
DETAIL:  Token "" is invalid.
CONTEXT:  JSON data, line 1: {"k1":"v1","k2"...

But this works (dropping the second k/v pair of the second element in the array)

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1"}'::jsonb
  ))
) AS t(x);

Constructing the jsonb array from a jsonb and a text also works..

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'
  ))
) AS t(x);

I'm thinking this is a bug.

You have some characters after the "k2" in first query. And because of that it is complainig about invalid syntax.

select *,ascii(c) from unnest(regexp_split_to_array('''{"k1":"v1","k2":"v2"}''','')) as c;
 c | ascii
---+-------
 ' |    39
 { |   123
 " |    34
 k |   107
 1 |    49
 " |    34
 : |    58
 " |    34
 v |   118
 1 |    49
 " |    34
 , |    44
 " |    34
 k |   107
 2 |    50
 " |    34
   |  8204
   |  8203
 : |    58
 " |    34
 v |   118
 2 |    50
 " |    34
 } |   125
 ' |    39

If you run your first query like this, it will work:

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'::jsonb
  ))
) AS t(x);


A column of database type "geometry" results in two extra columns hidden from ssms If I create a table like
create table test2 (foo geometry)

foo will show up in the table designer as expected

but when I query sys.columns for column information...
select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.system_type_id = ty.system_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

...I also got a columns of the types hiearchyid and geography (using the same column name).

Why?
Can I change my sys.columns query to filter out those pseudo columns?
(I am using that query to recreate a create table query).

use this query instead:
select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.user_type_id = ty.user_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

the join is based on the user_type_id so the results are the same as the GUI.


How to return "NaN" using SQRT function in Oracle SQL? How to return "NaN" using SQRT function in Oracle SQL?  Here is my unsuccessful attempt:

SELECT SQRT(4) AS "SQRT of 4",
       SQRT(0) AS "SQRT of 0",
       SQRT(-1) AS "SQRT of -1"
FROM DUAL;

As I understand, a successful query might use the TO_BINARY_DOUBLE function but additional approaches are welcome.

Cast the number, as you said:

SELECT SQRT(4) AS "SQRT of 4",
   SQRT(0) AS "SQRT of 0",
   SQRT(TO_BINARY_DOUBLE(-1)) AS "SQRT of -1"
FROM DUAL;


Contiunous rise of mysqld memory consumption Mysqld memory consumption rises forever and never seems to be freed. It starts out about 6GB but gradually rises to around 10GB in a few weeks, and of the 10GB only 4GB and 50MB is being used by the buffer pool and the dictionary respectively when checking innodb status.

This is with MySQL 5.6.16 on a server with 12GB memory. A few of the tables are partioned and there are roughly 8000 idb files. Also one table is created each day.

I have tried to FLUSH TABLES with no success. The tables are closed but the memory does not get freed at all. In fact, more memory gets consumed.

Why is the memory being consumed? And are there any known issues with memory not being freed when using partioned tables?

my.cnf

query_cache_size = 512M
query_cache_limit = 16M
max_allowed_packet = 16M
table_open_cache = 1024
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 2MB
myisam_sort_buffer_size = 1M
max_connections = 1024
thread_cache = 1024
tmp_table_size = 16M
max_heap_table_size = 16M
wait_timeout = 20
join_buffer_size = 256KB
thread_cache_size = 50
table_definition_cache = 400
key_buffer_size = 256M

We tracked it down to glibc. With glibc-2.12-1.149.el6_6.4.x86_64, memory is not fully freed when closing tables.

It seems that under high concurrent connections, accessing partitioned tables increases Open_tables which increases memory usage, however memory is not fully freed when the table is closed using glibc.

Newer glibcs faced the same problem as well.
Switching to jemalloc-3.6.0-1.el6.x86_64 has resolved our problem.

Test environment
CentOS release 6.6 (Final)
kernel 2.6.32-504.el6.x86_64
mysql-5.6.16

physical ram 2G
bufferpool 256M
other default

memory usage with gblic-2.12-1.166.el6_7.7.x86_64
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 1.0G

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.2G

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.4G

memory usage with jemalloc-3.6.0-1.el6.x86_6
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 582M

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 583M

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.2G
"flush table" -> 584M