Mongodb - Best Practice for TTL index on huge dataset I want to create a TTL index for a table.

Table size 1.2TB

I have a shard infra. Each shard is having 3 node replica set.

For creating this index, can I go with the rolling index creation method? Or any other suggestion?

The rolling index build procedure is recommended. Here is what they say in the doc:

  To minimize the impact of building an index on replica sets and sharded clusters, use a rolling index build procedure as described on Build Indexes on Replica Sets.

for more info:

https://docs.mongodb.com/manual/tutorial/build-indexes-on-replica-sets/
https://docs.mongodb.com/manual/tutorial/build-indexes-on-sharded-clusters/
https://docs.mongodb.com/manual/core/index-ttl/
https://docs.mongodb.com/manual/reference/method/db.collection.createIndex/#db.collection.createIndex


MySQL table_cache and Opened_tables I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so this is not a valid comparison. The only caveat being that perhaps Opened_tables is only bumped on misses - although even then if the tables being opened per second is still small, it's probably not a problem for it to grow gradually.

If comparing Open_tables to Opened_tables is not valid, is there another way to get measured data for this?

This is on MySQL 5.0, but differences between version are welcome too.

The biggest reason to have a large table_cache is so that LOCK_open mutex is not hot.  MySQL prior to 5.5 has a lot of contention when you are trying to open/close tables, so you want to restrict doing this as much as possible, i.e. have a large table cache.

So you don't care about any particular ratio of hits to misses (infact you should ignore ratios altogether - this blog post explains why).  What you care about is the miss rate, because the more times this happens per second, the higher the chance that you will have contention (one thread has to wait for another thread to release the lock.)

How do you spot the miss rate?  You fetch a few samples of Opened_Tables a few seconds apart during the busiest period of the day, and if there are increases in each sample, it's probably a good idea to see if you can bump up the table_cache.

Note: I very specifically do not recommend comparing to uptime.


can access to a view be controlled by access to a referenced table? I have a table Table_A and a view View_A that is a select of the contents of Table_A.

If I deny SELECT on Table_A to a user, the user can still use the view View_A.

Is there a way that I can force the view to return an error when the user tries to use it?

The real world case here is a database with about 20 tables and hundreds of views.  User access is logically tied to the tables so we are hoping to stop folks from using the views based on those tables.

You need to give the views a different owner than the tables so that the ownership chain is broken and the permissions are checked on the base objects.


SQL Cluster failover doesn't work after applying SQL Server 2016 Service Pack 2 I tried to apply SQL Server 2016 Service Pack 2 on the clustered instance of SQL  Server (SQL Server 2016 SP1 CU7, 2 nodes, active-passive architecture). We did this patching previously on the other cluster with similar configuration and it went fine. I started with the passive node, the installation succeeded but, when I tried to move SQL role to the newly patched node (in order to start patching the second node), the failover failed. In the cluster events log I found the corresponding error:

The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.

As per error message I checked system event log on passive node and I found few consecutive errors at that time:

6/9/2018 7:08:07 PM

Cluster resource 'SQL Server' of type 'SQL Server' in clustered role 'SQL Server (MSSQLSERVER)' failed.
Based on the failure policies for the resource and role, the cluster service  may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

6/9/2018 7:08:07 PM

The Cluster service failed to bring clustered role 'SQL Server (MSSQLSERVER)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

6/9/2018 7:08:07 PM

Clustered role 'SQL Server (MSSQLSERVER)' has exceeded its failover threshold.  It has exhausted the configured number of failover attempts within the failover period of time allotted to it and will be left in a failed state.  No additional attempts will be made to bring the role online or fail it over to another node in the cluster.  Please check the events associated with the failure.  After the issues causing the failure are resolved the role can be brought online manually or the cluster may attempt to bring it online again after the restart delay period.

and eventually the critical one at 6/9/2018 7:08:37 PM

The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.

Unfortunately, this information is not very informative for me, so I tried to dig into cluster log, there is definitely more information there, but I still struggle to find the root cause and solution.

You can find the full cluster log here: https://1drv.ms/u/s!Aiz8LIBP787C6ABO8aqfN9ayyFC0

I think the key to solving the issue is the cluster log, but the error messages are not clear to me. Perhaps some of you guys spotted this issue before?

I think it's worth to mention we run our SQL Clusters on Azure and we use S2D technology for cluster storage purposes. I mentioned about this as we can see following erros in cluster log:

000009c8.00002e04::2018/06/09-17:07:35.889 ERR   [API] ApipGetLocalCallerInfo: Error 3221356570 calling RpcBindingInqLocalClientPID.
000009c8.00002e04::2018/06/09-17:07:35.889 INFO  [CftCache] Querying whether disk {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} supports cache state [ReadWrite]
000009c8.00002e04::2018/06/09-17:07:35.889 INFO  [CftCache] Can't query seek penalty storage property for device [Disk: Id {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} # 2 #paths 0 #id 0 H      ], error 31.
000009c8.00002e04::2018/06/09-17:07:35.891 INFO  [CftCache] Disk [Disk: Id {166faa04-8d1a-4901-e0b4-a7c75bdd54e3} # 2 #paths 0 #id 0 H      ] Path \\?\Disk{166faa04-8d1a-4901-e0b4-a7c75bdd54e3} candidate? true cacheSupported? true supportStatus 0
000009c8.00002e04::2018/06/09-17:07:35.892 ERR   [API] ApipGetLocalCallerInfo: Error 3221356570 calling RpcBindingInqLocalClientPID.

Perhaps it's an issue with storage? But if that's the case why it has been revealed by sql patching?

Actually, it looks like storage issue. I would recommend you check S2D storage. This and this might help you.
Assuming you already run SQL Cluster on top of S2D, what is the number of nodes?

If it is a 2-node S2D cluster, I suggest you consider to move to AlwaysOn Availability Groups where is no need in shared storage or Failover Cluster Instances but on top of StarWind VSAN Free. AlwaysOn Availability Groups provides you with active-passive application level replication. StarWind will do active-active block-level replication featuring SQL instance failover between nodes.


Unable to create read-only user in PostgreSQL 9.5 I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far:

Ran this,

CREATE ROLE dbowner LOGIN ENCRYPTED PASSWORD 'dbownerpassword';
CREATE DATABASE thedb WITH OWNER dbowner;
GRANT ALL PRIVILEGES ON DATABASE thedb TO dbowner;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

GRANT ALL ON ALL TABLES IN SCHEMA public to dbowner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public to dbowner;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to dbowner;

CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'ropassword';
GRANT CONNECT ON DATABASE thedb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

Then I restarted,
    sudo service postgresql restart

Now,

psql -h localhost -U dbowner thedb
thedb=> CREATE TABLE testtable(col1 VARCHAR(100));
CREATE TABLE
thedb=> INSERT INTO testtable values('123');
INSERT 0 1
thedb=> SELECT * FROM testtable;
 col1
------
 123
(1 row)

psql -h localhost -U readonly thedb
thedb=> SELECT * FROM testtable;
ERROR:  permission denied for relation testtable

pg_hba.conf already has local all all md5 and host all all 127.0.0.1/32 md5. Anything else that I might be missing?

The question doesn't specify what user executed the following statement:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

but based on the surrounding code, let's guess it's postgres. That's the reason why readonly gets a permission denied trying to read it: it is the future creator of the table, dbowner, that should execute this statement.
(to be sure, note that you should also mention to what database you're connected when running the mentioned commands).

As stated by the doc (emphasis mine) at https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html :

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of.


What is the generally accepted method and tools for profiling MySQL queries? Profiling database queries is difficult, and dependent on database technology.  In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query?

For example:

Run the query.
Use X tool on the query.
Analyze the results of X using a certain tool.
Check indexes....

The first tool is the EXPLAIN that reports the query execution plan supposed by SQL engine. That plan contains the sequence of subselects, available and chosen indices for each, the number of rows selected in each subselect, as well as mechanism chosen to perform each subselect.

Also mysql have a nice profiling tool that can help to localize the problem:

SET profiling=1;
SELECT . . . . .
SHOW PROFILE;
SET profiling=0;


Wrong SIZE, MAXSIZE and Autogrowth? I know that basically while creating a database, the model system DB is copied so based on the pictures below:

Why the initial size is 3MB for PRIMARY file and 1MB for LOG If the documentation clearly says that It should be 8MB for versions above 2016 and 1MB for anything lower (I'm on this category as I'm using 2014)
I understand that a log file could grow to 2TB maximum but why the Model database says unlimited and the STACK database says limited to 2TB?
Assuming that the actual default size is indeed 3MB and 1MB, why on the disk, I see 2240 KB and 560 KB?

1.Why the initial size is 3MB for PRIMARY file and 1MB for LOG If the documentation clearly says that It should be 8MB for versions above 2016 and 1MB for anything lower (I'm on this category as I'm using 2014)

The initial size is going to be whatever Model is set to, in this case your model is 3 MB. Who knows why, could be a host of reasons which in the end aren't very important to be honest. Set Model to the starting size you'd like for all new databases or specify the size in the create database command/window.

  2.I understand that a log file could grow to 2TB maximum but why the Model database says unlimited and the STACK database says limited to 2TB?

They are equivalent, I wouldn't get hung up on this. Most likely the way the GUI is coded, honestly not even worth making a connect item about.

  3.Assuming that the actual default size is indeed 3MB and 1MB, why on the disk, I see 2240 KB and 560 KB?

Model is set to autogrow by 1 MB, so it's 2.2 MB right now... I'm going to guess at 2 MB it grew by 1 MB. The size on disk and the metadata sizes aren't going to match up unless you have a database that is completely and utterly devoid of free space. The log has a size it can't be smaller than, which you're at right now.


Execute a stored procedure in a SELECT or FROM statement or a SQL function Are there any possibilities to execute a stored procedure in a SELECT or FROM statement or a SQL function?

Like

select sp_name_and_parameters
from table_name_or_sp_name_and_parameters

or

select function_name(sp_name_and_parameters)

I have no ideas

I would look at these two additional posts at stackoverflow:  https://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query https://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure    As aasim.abdullah has said, I would look into Table valued functions more too.  Information about them can be found here:  https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx    I know the Microsoft link refers to SQL Server 2008 R2, the theory is the same though.


Execute immediate call causes 'variable not in select list' error Execute immediate call causes 'variable not in select list' error

hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code:

set serveroutput on;
declare
  type TTabList  is table of table1%rowtype index by pls_integer;
  vtabList       TTabList;
  vstrQuery      varchar2(32000);
  vNum           number(10)     := 100;
  vStrs          varchar2(100)  := '''45'',''11'',''20'',''71''';
begin
  dbms_output.enable;
  vstrQuery := 'select field1
                  from table1
                 where field1 =   '|| vNum ||'
                   and field2 in ('|| vStrs ||')';

  execute immediate vstrQuery bulk collect into vtabList;

  if vtabList.count = 0 then
    dbms_output.put_line('nothing to show!');
  else
    while vtabList.count > 0 loop
      for i in vtabList.first .. vtabList.last loop
        dbms_output.put_line('we have: '|| vtabList(i).field1);
      end loop;
      vtabList.delete;
    end loop;
  end if;
end;

But its results following error which i can't resolve:

Error report -
ORA-01007: variable not in select list
ORA-06512: at line 14
01007. 00000 -  "variable not in select list"
*Cause:
*Action:

Any ideas to fix this?

As  mustaccio pointed out, types must be the same. You either have to change TTabList definition to be collection of just one field (see example below), or select : vstrQuery := 'select * ...
Everything else should work after fixing misprint with rowrype (should be rowtype) .  For instance, if you prefer to select just field1, you can do

set serveroutput on;
declare
  type TAB1_FIELD1 IS RECORD (field1 table1.field1%type);
  type TTabList  is table of TAB1_FIELD1 index by pls_integer;
  vtabList       TTabList;
  vstrQuery      varchar2(32000);
  vNum           number(10)     := 100;
  vStrs          varchar2(100)  := '''45'',''11'',''20'',''71''';
begin
  dbms_output.enable;
  vstrQuery := 'select field1
                  from table1
                 where field1 =   '|| vNum ||'
                   and field2 in ('|| vStrs ||')';

  --works fine with collections (at least starting from 10g)
  execute immediate vstrQuery bulk collect into vtabList;

  ....


Error 1242(21000) after creating following trigger and performing delete operation I want to create a trigger which copies the record from maintable to undostack before deleting it in maintable and undostack table acts a stack of 5 records space and this trigger performs a push operation.
This is my trigger
CREATE TRIGGER undo_op
BEFORE DELETE ON maintable FOR EACH ROW
BEGIN

When undostack is not full
IF (SELECT COUNT(*) FROM (SELECT * FROM undostack)u)<5 THEN
INSERT INTO undostack VALUES(((SELECT COUNT(*) FROM undostack)+1),
(SELECT id FROM maintable WHERE id=OLD.id),
(SELECT descp FROM maintable WHERE descp=OLD.descp),
(SELECT cat FROM maintable WHERE cat=OLD.cat),
(SELECT dat FROM maintable WHERE dat=OLD.dat),
(SELECT amt FROM maintable WHERE amt=OLD.amt));

When undostack is full and has to delete the oldest record entered by updating. here undo_no acts as pointer.
ELSE

1<-2
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=2)
WHERE undo_no=1;

2<-3
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=3),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=3),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=3),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=3)
WHERE undo_no=2;

3<-4
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=4),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=4),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=4),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=4)
WHERE undo_no=3;

4<-5
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack)u WHERE undo_no=5),
descp=(SELECT descp FROM (SELECT * FROM undostack)u WHERE undo_no=5),
cat=(SELECT cat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
dat=(SELECT dat FROM (SELECT * FROM undostack)u WHERE undo_no=5),
amt=(SELECT amt FROM (SELECT * FROM undostack)u WHERE undo_no=5)
WHERE undo_no=4;

5<-update with the values which are to be deleted from maintable
UPDATE undostack SET
id=(SELECT id FROM maintable WHERE id=OLD.id),
descp=(SELECT descp FROM maintable WHERE descp=OLD.descp),
cat=(SELECT id FROM maintable WHERE cat=OLD.cat),
dat=(SELECT id FROM maintable WHERE dat=OLD.dat),
amt=(SELECT amt FROM maintable WHERE amt=OLD.amt)
WHERE undo_no=5;
END IF;
END

The trigger has no errors
but when I execute
DELETE FROM maintable WHERE id=10;

There is following error
ERROR 1242 (21000):Subquery returns more than 1 row
Please help me with this error
I'm using MySQL 5.7.3

The easiest way to do this, is(for example undo_no 1)
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack) u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack) u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack) u WHERE undo_no=2)
WHERE undo_no=1;

By using (SELECT * FROM undostack) u Mysql generates a table which it uses to select your data.
usully i would say make this better in your pplication code, but five rows is very samll


Proper MySQL database maintenance I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it.

I'm a systems admin that got put into the roll of DBA (because I helped the CEO with his outlook, so I can obviously manage our databases!). It's not that big or busy of a database server... a mysqldump is about 6GB in size and it's taken us 5 years to get it that big. Munin reports that we're averaging 40-60 queries a second at our peak hours.

My boss paid for me to take the Oracle University systems admin course, but having gone through it, it simply explains the different parts of the mysql server, what things do and how they do it. But it's not that in-depth and you definitely don't come out of that course a DBA.

So as the current DBA, what should I do to make sure everything is running smooth? Are there daily maintenance tasks I can perform? Are there certain metrics I should be checking? Or to put it another way, as DBAs, what do YOU do on a daily basis to keep everything in good shape?

If it'll help tailor the answer a little bit, here are some specifics. We have 171 tables, all but 3 are innodb, the others are myisam. We have Master/Slave replication set up between our primary datacenter and our disaster recovery site, using RBR. Version is 5.5.28.

What can I do?

First things first. Make sure you have developed and documented a disaster recovery (DR) strategy. Spend some time thinking up ways that things can go wrong, how to recover from them, and testing them to get an idea of how long it will take, especially when restoring from a backup. Some general ideas:

mysql crashed, but server is fine: fix error and start mysql.
MySQL has to be restored from a backup: restore from a backup and start mysql <-- test this before it becomes something you have to do in an emergency.
Server died and has to be replaced: get a replacement server in place and restore from a backup.

Once you have a DR strategy and a method developed to test your backups in place, then you can move in to more periodic tasks:

test your restore process regularly. This ensures familiarity in case when it's necessary.
ensure proper indexes. If you're using percona server, you can get statistics on which indexes are unused after a given time (month or so)
slow query review. Enable the slow query log with long query time of 1second or so and use pt-query-digest to review them on a weekly/monthly basis.
read http://www.mysqlperformanceblog.com/ and blogs from http://planet.mysql.com/ .. on such a tiny database, you'll rarely have many of the performance issues of interest. So you'll have plenty of time to read up on interesting issues and how to resolve them.


To run SQL Server Profiler or to use the SQL Server Agent's Jobs function in SSMS or ...? I have a problem here - I wish to keep track of all transaction executed on the SQL Server but was wondering how should I proceed.

If I were to use the SQL Server Profiler and configure it to store all the transaction history into a file and perform a roll-over if a specified size is hit, then I will need to make sure that the SQL Server Profiler is always open and running. Also from my understanding, it will decrease the server performance.

If I were to use the Jobs function under the SQL Server Agent in SSMS, then it become a schedule task that only run when it is being called.

So, how do I go about to keep track of all SQL transaction make to the SQL Server and record it 24/7 or until I manual stop it (and also would not slow down the performance of the server)?

In the past I wrote some scripts to capture slow running queries.

I used SQL Profiler via T-SQL stored procedures. The overhead for this wasn't huge and at the time provided valuable insight.

More info and some examples are here: http://www.toadworld.com/platforms/sql-server/w/wiki/9827.running-traces-with-tsql.aspx

I would write two stored procedures, one to start the trace and one to stop it.  These could be called via a SQL Agent job or a scheduled task, whatever is preferred. Log only the info you want to a table rather than files for easier management. You could create a third job to clean up records after "X" days/weeks/months depending on your requirements.

The performance hit will depend on the existing load on the server and there is no easy way of doing this so test any recommended solutions thoroughly before deciding which way to go.


Does a database restore remove existing stored procedures I have a PROD and NON-PROD system

NON-PROD already has a copy of an application PROD database

I have also created new stored procedures on the NON-PROD application database and made changes to a few old ones

If I were to take a fresh backup of the PROD application database, and were to restore it on the NON-PROD server, will this:

remove the new stored procedures which I created on the NON-PROD application database?
remove the changes made to the stored procedures (NON-PROD application database) which have the same name as on PROD application database, and replace them with the original one's from PROD application database?

A restore will completely wipe the target database (if it exists, otherwise created of course).

Any changes to tables, code security and data will be lost in the target database, which will be 100% identical to the source database at the time the backup happened

Yes it will
Yes it will


Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database? A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I disagreed and provide my evidence below. If you disagree with me or have a better rebuttal, than by all means supply it.

I am using AdventureWorks2012 on this version of SQL Server:

SELECT @@VERSION;
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Having loaded the database, I run the following query:

Firstly, run Jonathan K's AW fattening script found here:

AW Get Fat

---------------------------
-- Step 1: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

The result is shown here:

Detach and re-attach the database and then re-run the query.

---------------------------
-- Step 2: Detach/Attach
---------------------------
-- Detach
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012'
GO

-- Attach
USE [master];
GO

CREATE DATABASE [AdventureWorks2012] ON
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Data.mdf'
)
    ,
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Log.ldf'
)
 FOR ATTACH;
GO

What is in the bpool now?

---------------------------
-- Step 3: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

And the result:

Are all the reads logical at this point?

--------------------------------
-- Step 4: Logical Reads Only?
--------------------------------
USE [AdventureWorks2012];
GO

SET STATISTICS IO ON;
    SELECT * FROM DatabaseLog;
    GO
SET STATISTICS IO OFF;

/*
(1597 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.
*/

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Another option is to offline and then online the database. Let us try that.

--------------------------------
-- Step 5: Offline/Online?
--------------------------------
ALTER DATABASE [AdventureWorks2012] SET OFFLINE;
GO
ALTER DATABASE [AdventureWorks2012] SET ONLINE;
GO

---------------------------
-- Step 6: Bpool Stuff?
---------------------------
USE [AdventureWorks2012];
GO

SELECT
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [buffer size(MB)]
   , COUNT(*) AS [buffer_count]
FROM
     sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
WHERE
     b.database_id = DB_ID()
     AND p.object_id > 100
GROUP BY
     p.object_id
   , p.index_id
ORDER BY
     buffer_count DESC;

It appears that the offline/online operation worked a lot better.

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

  And we can see that the buffer pool was not totally blown away by the
  detach/attach. Seems like my buddy was wrong. Does anyone disagree or
  have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

  Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads
  0, read-ahead reads 768, lob logical reads 94, lob physical reads 4,
  lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

  When SQL Server performs a sequential scan of a large table, the
  storage engine initiates the read ahead mechanism to ensure that pages
  are in memory and ready to scan before they are needed by the query
  processor.  The read ahead mechanism tries to stay 500 pages ahead of
  the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.


Python MySQL prepared statements slower than regular queries I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script).

Here is a small benchmark script I wrote:

In essence what I do for regular queries:

cursor = connection.cursor()
cursor.execute("INSERT into test (id,name) VALUES (1,'Value 1')")
... multiple inserts ...
connection.commit()

And for prepared statements:

cursor = connection.cursor(prepared=True)
sql = "INSERT into test (id,name) VALUES (%s,%s)"
cursor.execute(sql, (1, 'Value 1'))
... multiple inserts ...
connection.commit()

The script runs 2 loops with same number of iterations
1st loop runs with the default .cursor() while 2nd with .cursor(prepared=True) as per above

And here are the results:

As you can see MySQLCursorPrepared=9746ms while MySQLCursor=1299ms.

Why are my prepared statements slower than regular queries? Is it an issue with the python mysql.connector driver or do I need to tweak something in my MySQL config to fix this?

Much faster is to build a multi-row insert:

INSERT INTO t (a,b)
    VALUES
    (1,2),
    (11,23),
    (111,234), ...

On the MySQL side, this runs about 10 times as fast (for 100-1000 rows) than single-row inserts.


Is there a way to make a null check on a variable in a WHERE clause only occur once? I have a query on a large table that looks like this:

declare @myIdParam int = 1

select *
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

There are several similar conditionals like this in the where clause, and there are also a lot of joins, but this is a summary.

Effectively, if @myIdParam is null, we do not want to restrict the results using this parameter.

I am not a DB pro, but from my tests it seems like this NULL check is done for every record and not optimized out in any way.

If I remove the null check and assume the parameter is not null, the query returns instantly.  Otherwise, it takes up to ten seconds.

Is there a way to optimize this so the check is done only once at runtime?

One way is to use dynamic SQL, using a null check to optionally add that part of the where clause.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql


Insert into table select * from table vs bulk insert I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?

If no, Is there any way exclude index while inserting records. Process is inserting 150 Millions data in one execution.

We are planning to create stage table (Which will not have any index Table1), then transfer it from stage table to target table (Which will have index Table2)

We are not in the situation to create flat file from the process.

But we are looking something that can speed up process when we transfer data from Table1(not indexed) to Table2 (indexed).

Any way to use BulkInsert from Table1 to Table2?

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide.

At SSIS level, you can look into below things to speed up data read and data load :

Fast Parse Option along with its limitations.
Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection
Set the Packet Size to 32767
Select the OLE DB Destination  Data Access mode “Table or View – fast load”  option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour


When will autovacuum work in PostgreSQL? I tested the autovacuum in PostgreSQL. So I create a table company and insert 100 records. Then I checked the table size like,

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 54 kB |

After that I update all the records in a table like,

ganapathy=> update company set address = 'California' where id between 1 and 100;

Then I checked the table size, It gives the size like this,

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So checked the configuration file of postgresql.conf, In that the autovacuum is already enabled,

autovacuum = on

But the autovacuum has not worked. So after two days I checked the table size again,
But even though the table size has not changed.

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So I want to know,

  Why its not working for me?
  When the autovacuum process will work in PostgreSQL?

Please help me.

Thanks in Advance...!

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

PostgreSQL documentation

AUTOVACUUM is the standard form of VACUUM run automatically. If you insist on freeing unused space from tables, then do manual VACUUM FULL.


Is it bad practice to use separate columns for parts of date values? I recently posted these questions in order to try and improve the performance for my queries.

DBA Q&A 1

DBA Q&A 2

One of the suggestions was that I should use a DATE() column rather than columns such as yr, yrmth(e.g. 201502), mth, day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.

I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.

If it is bad practice, why is it?

Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?

Edit:  My use case is write once, read lots, and the date values in rows will never change.

It is a bad practice in a transactional/operational database and a good practice in an analytic/data warehouse database.

In a data warehouse I recommend using an integer "smart key"† for your date dimension (like 20160328), but also including an actual date-type column, as well as the broken-down date parts.

The broken down date parts are more for ease of analysis, than speed of querying.

† This makes it easy to partition your fact tables.


Force MS SQL Service Account to re-authenticate So here's the deal:
- SQLserver.exe running as domain\myserviceaccount

The (network) backups are running based on the SQLserver.exe service, so the network share is accessed as domain\myserviceaccount.

Now, I made some changes to the share (secured with an AD Security Group and domain\myserviceaccount is a member of it). When I run as or map the drive with domain\myserviceaccount everything works fine. However, the backups are failing. The only way to have this to work so far, is to restart the sqlserver.exe service.

Am I missing something here? I tried rebooting the file share server to make sure all sessions were cleared and also tried logging on as myserviceaccount on that server, nothing. Tried a klist purge, 0.

Any idea?
Thanks

When a Windows account logs on to the domain the session receives a ticket that includes all of the group memberships.  Accordingly, if you add the account to a new group, it will not be in effect for sessions that are already logged in.  So you'll need to either:

Restart the SQL Server service
Add the user account specifically to the share permissions as a temporary solution until the next time the SQL Server service is restarted
Change the path of the backup config to use an IP address instead of a servername--again, temporarily.  This will cause the connection to bypass Kerberos and use NTLM authentication, which will pick up the group membership (not 100% sure this will work for you, but it's totally a thing).


SQL Server monthly Backup I'm quite new to SQL Server and some employee asked me if is possible to make monthly database backups.

We do daily full backups to NAS server via Cobian and we use SQL Server Management Studio to make maintenance plan for daily full backup as well.

My problem is that the database that I want to make the monthly backup is used by a software that is not developed by us so I have no clue how to make queries to extract data from date types, etc. The database has a lot of tables.

All I want is to extract data from January only and make a backup with January data only so i can send to external company. Is that even possible without T-SQL queries?

No. What you seem to want is not a Backup of the database but some sort of extraction or export. A SQL Server backup performs the extraction of the whole contents of the database, in an internal format, so the database is restorable as a whole in a coherent point in time.

If you want what is usually known as export of the database, and it seems you need a partial export (just exporting a subset of the records). As you suspect, there is no way to do this without:
1/ well knowing the structure of data inside the database, and the relationships and constraings on tables, columns, ...
2/ running queries against the tables in order to extract just the rows you want.
3/ knowing which format do you want the data in.

Even if you get to extract just the data you want, it would be not easy to use this data as a backup, because restoring this data into the original database in the event of a data loss or Corruption would not be an easy task...

If you just want to save some space, you can replace the full backups by differential ones. you would need to save the first full backup in order to be able to restore with a differential one, but the sizes of the generated files would be much smaller.

hth


Database design for an E-commerce website I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?

For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.

Can anyone tell me how to design my database to manage this situation?

If you know most of the attributes your products will have ahead of time, you could hard-code them into the tables for different product types, such as:

books
-----
  id
  price
  title
  author
  isbn
  etc....

mobile_device
-------------
  id
  price
  size
  colour
  model
  etc...

You could also try something like this:

base_product
------------
  id
  base_price
  product_type

book_product_attributes
-----------------------
  base_product_id (FK to base_product)
  title
  author

mobile_dev_product_attributes
-----------------------------
  base_product_id (FK to base_product)
  model
  colour

This way, you can group your common attributes in a base product table, and more specific attributes are stored in other tables when necessary.

This will work if your product types are mostly static. On the other hand, if you have no idea what product types or attributes you'll need in the future, and entity-attribute-value based system might work better. The idea behind that model is that you have a table for the different attributes you might have, a table for the different entities in your system, and a table for the attribute values that an entity has. Example:

entities
--------
  id

attributes
----------
  id
  name

values
------
  id
  entity_id
  attribute_id
  value

The data might look like this:

entities
ID
----
  1

attributes
ID |  name
----------
1  |  title
2  |  author

values
ID  | attribute_id   | entity_id   | value
---------------------------------------------------------
1   | 1              | 1           | "Great Expectations"
2   | 2              | 1           | "Charles Dickens"

This data very briefly describes a product which has two attributes: title, and author, with the valus "Great Expectations" and "Charles Dickens", respectively.

Be aware that using an EAV database can make queries very awkward. With enough data and the wrong model design, it's also possible to run into performance problems. The example I gave was very simple but more realistic designs tend to be more complicated than that. It can take time to get this kind of database correct and it's not always the best solution.


SQL Server data structure (in protocol) If I have a Book table and Author table, with Book having a foreign key to Author, many rows in Book may have the same author. If I then run this query:

SELECT * FROM Book
INNER JOIN Author ON Author.Id = Book.AuthorId
WHERE Author.LastName = 'Shakespeare'

how will the underlying data be formatted and sent to the client? In essence - will each Book row have it's own Author (i.e. the data from Author is duplicated for each row), or will there be one single Author entry (representing Shakespeare)?

UPDATE:

To be clear, I just want to know whether joining tables will increase packet size (and transmission time) significantly, or if the protocol has some compression to ensure that only unique entries are transmitted.

It doesn't look like there is compression like you are talking about in the TDS

A question that might help you: Is data retrieved from SQL Server compressed for transmission?


Why low cpu usage on sql server developer edition 2014 The CPU usage is low, around 15 %. CPU is 4770K. The SQL server data file is split in 8 parts and are stored on EVO 970 SSD, along with tempdb (also split in 8 parts).
The temdlog and database log are on another SSD (read/write potential around 500MB/s).
I don't see any high disk usage in task manager below (10 MB/s), and RAM usage is not high either (have 32 GB).
Max parallelization is set to 8.

It seems SQL server is mostly using 1 core, but I can't figure out why.
Does not seem to be bound by disk nor ram, so how can I get it to work a bit harder (use more CPU)?

Update

Tested Dan Guzman's query and confirmed the cpu is more utilised although it is hard to have time to see in task manager (query time 2 seconds).
Good to know the developer version isn't locked to one core.

Tested the queries from Dan Brown's link. The parallelized version got my cpu usage over 95 % so it seems case is closed; the low cpu usage is because of the query.

I've a lot of correlated subqueries (select in select), which does not look good for performance. For maintenance it's been easiest to calculate several statistics in one query, but it seems only one core is used in that way.
I tested splitting them so that I only calculate one metric per query, and that way sql server starts using more cores.

In the future I think I will test the performance of Spark for the same queries and see how it compares to sql server. Perhaps also by running the queries against sql server through spark on several workers (in that case I need to split the work by partitioning according to e.g. modulo of pk).

For the interested:
what I need to calculate are various metrics for races, so that for each race and each competitor I fetch it's top record e.g. during last 3 months, last 6 months etc. or average time last 6 months.
When the query time is around 10 hours it starts affecting the analytics work I want to do (one iteration to change or correct something takes too long).

SQL Server Developer Edition can use all available cores unless you've specifically configured it otherwise. Whether or not it will use all cores depends on the workload.

If you are running a query like SELECT * FROM dbo.YourTable, it will likely be a single-threaded plan and only use one CPU. You'll need to run multiple instances of such a query to see more CPUs used.

A single query that uses parallelism should be able to use multiple cores. The example below uses all 12 cores of my box, although total CPU averages about 40%.

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
SELECT COUNT(*) AS CountOfRows
FROM t1g;


Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700?  You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either.  It could only be used to find the rows that "might" match a WHERE and the server layer (above the storage engine) would have to further filter the rows matched.  Do you really need 1000 characters for a last name?

update to illustrate:  I have a table test table with a litle over 500 rows in it, each with the domain name of a web site in a column domain_name VARCHAR(254) NOT NULL and no indexes.
mysql> alter table keydemo add key(domain_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

With the full column indexed, the query uses the index:
mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | keydemo | index | NULL          | domain_name | 764     | NULL |  541 | Using index |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

So, now, I'll drop that index, and just index the first 200 characters of domain_name.
mysql> alter table keydemo drop key domain_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table keydemo add key(domain_name(200));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | keydemo | ALL  | NULL          | NULL | NULL    | NULL |  541 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql>

Voila.
Note also, that the index, at 200 characters, is longer than the longest value in the column...
mysql> select max(length(domain_name)) from keydemo;
+--------------------------+
| max(length(domain_name)) |
+--------------------------+
|                       43 |
+--------------------------+
1 row in set (0.04 sec)

...but that doesn't make any difference.  An index declared with a prefix length can only be used for lookups, not for sorting, and not as a covering index, since it doesn't contain the full column value, by definition.
Also, the above queries were run on an InnoDB table, but running them on a MyISAM table yields virtually identical results.  The only difference in this case is that the InnoDB count for rows is slightly off (541) while MyISAM shows the exact number of rows (563) which is normal behavior since the two storage engines handle index dives very differently.
I would still assert that the last_name column is likely larger than needed, but it still is possible to index the entire column, if you are using InnoDB and running MySQL 5.5 or 5.6:

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
— http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html


Oracle how do i see who connected to my database I am using oracle 11g and I would like to see who connected to my database some hours ago. In what file can I see those who have connected to my database through a network connection?

Thanks

you need auditing in your database, by default Oracle database audit LOGON/LOGOFF action, please take a look into following article Auditing in Oracle 10g Release 2. query from DBA_AUDIT_TRAIL is a good starting point


APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created I am trying to install APEX 19.1 on ORACLE XE 18c but the problem is that APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created. While I execute apex_rest_config.sql script via sqlplus, an error message appears saying: GetConsoleMode failed, Last Error=|6|

Then I check the list of users and I see that these two users are not created. Is this a bug of APEX installation? Please can anybody help?

That is because you tried installing APEX in the root container, I was able to reproduce the error.

Oracle Express Edition 18c uses the CDB architecture, meaning you have a root container and a pluggable database called XEPDB1.

Whatever you did, you should do it inside the XEPDB1 pluggable database instead of the CDB$ROOT root container. I am not saying it can not work like that, I just would not bother with it.

Understanding the Installation Choices

  Oracle recommends removing Oracle Application Express from the root
  container database for the majority of use cases, except for hosting
  companies or installations where all pluggable databases (PDBs)
  utilize Oracle Application Express and they all need to run the exact
  same release and patch set of Oracle Application Express.

So if you log in with sqlplus / as sysdba, you can change to XEPDB1 like this:

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
SQL> alter session set container=XEPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

To fix what you have, just log in with sqlplus / as sysdba and remove everything with apxremov.sql. Then log in like above, into the XEPDB1 pluggable database, and try again installing APEX. You can use these steps:

Downloading and Installing Oracle Application Express


Select rows with the highest value for one ID, after grouping by two IDs It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, Count(*) AS BustCount
FROM InvolvedIn I
WHERE I.AgentID IS NOT NULL
AND I.isCulprit = TRUE
GROUP BY I.AgentID, I.PersonID HAVING COUNT(*) > 1;

This what I get:
AID     PID     BustCount
1       11      4
2       22      2
3       22      5
4       33      5
5       44      4

But I would like to get only the PID with the highest BustCount (not line row 2).
AID     PID     BustCount
1       11      4
3       22      5
4       33      5
5       44      4

Simply adding DISTINCT ON (PersonID) will do the job in PostgreSQL:
SELECT DISTINCT ON (PersonID)
       AgentID, PersonID, Count(*) AS BustCount
FROM   InvolvedIn i
WHERE  AgentID IS NOT NULL
AND    isCulprit
GROUP  BY AgentID, PersonID
HAVING COUNT(*) > 1
ORDER  BY PersonID, BustCount DESC, AgentID;

I added AgentID as (optional) third ORDER BY expression and tiebreaker to get a deterministic result in case of multiple agents with the same amount of busts. Adapt to your requirements.
Detailed explanation:

Select first row in each GROUP BY group?

We can do all of it in a single SELECT without subquery, because DISTINCT ON is applied after aggregation. About the sequence of events in an SELECT query:

Best way to get result count before LIMIT was applied

Oh, and WHERE isCulprit = TRUE is just a more noisy way of saying WHERE isCulprit.


Instead of
'2020-04-30' = date_add(op.dataDate, INTERVAL 14 DAY);

Use
op.dataDate = date_sub('2020-04-30', INTERVAL 14 DAY);

Your first statement will be interpreted as "add 14 days to all dataDate and return when that is 2020-04-30."  This will require a full scan of the table.
The second statement will evaluate to: "return records where the dataDate is 2020-04-16."  This allows the engine to perform a seek on your index that begins with dataDate.
Do whatever weird stuff you want to do to expDate since that won't factor much into how the query engine will optimize.


Combine a generated series with each row from a select The below select is returning an array of all the projects owned by all my users.

WITH user_projects AS
(SELECT
  u.id as user_id
  ,COALESCE(array_agg(DISTINCT pa.project_id), '{0}'::integer[]) as project_ids
FROM users u
LEFT JOIN project_assignments pa on pa.user_creator_id = u.id
GROUP BY u.id
)

user: 1, project_ids: {1, 2}
user: 2, project_ids: {3, 4}

Using that select I would like to proceed a report, which will display for each user a 12 month summary.

SELECT
  to_char(pk.created_at,'Mon') as mon
 ,extract(year from pk.created_at) as yyyy
 ,up.user_id
 ,COALESCE(count(distinct pk.keyword_id), 0) as total_keywords
FROM user_projects up
LEFT JOIN project_keywords pk on pk.project_id = ANY(up.project_ids::int[])
GROUP BY up.user_id, up.project_ids, 1, 2

Output:

 mon: sept, yyyy: 2014, user_id: 1, total_keywords: 10,
 mon: , yyyy: , user_id: 2, total_keywords: 0,

The select from above is doing what I need but is not displaying all 12 months, only months for which it can find data in project_keywords and for users which don't have any data into project_keywords it will display 0 and the month and year columns will be empty.

So how can I produce a report for each user displaying the last 12 months since now(), and where no data is available fill the month and year columns?

I believe in order to do that I need to have a generate_series which will return the last 12 months so I did this:

(select created_at from generate_series(now()::date - INTERVAL '1 year',now()::date,'1 month') as created_at)

But now I'm stuck and I don't know how to combine all of this.

The expected output should be

For user 1:

mon: sept, yyyy: 2013, user_id: 1, total_keywords: 10,
mon: oct, yyyy: 2013, user_id: 1, total_keywords: 11
...
mon: aug, yyyy: 2014, user_id: 1, total_keywords: 0
mon: sept, yyyy: 2014, user_id: 1, total_keywords: 2

For user 2:

mon: sept, yyyy: 2013, user_id: 2, total_keywords: 10,
mon: oct, yyyy: 2013, user_id: 2, total_keywords: 0,
...
mon: aug, yyyy: 2014, user_id: 2, total_keywords: 0,
mon: sept, yyyy: 2014, user_id: 2, total_keywords: 0

http://sqlfiddle.com/#!15/8c911/6

Using PostgreSQL 9.3.

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood)
FROM generate_series(
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH)
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)


How to verify data dumps between two MS SQL and MySQL I'm trying to verify data exported from MSSQL and imported into MySQL is 100% accurate using MD5 hash. On Linux, I'm using md5sum and WinMD5 on Windows. The encoding is the same: UTF8. I tried both UNICODE and ANSI, but the results were the same.

When I visually compare the data in the export files or the hashes of individual cell values, it's 100% accurate. However, comparing the two files results in different hashes.  Anyone know why the hashes would be different?

Your comments are much appreciated.

UPDATE:
The data is exported from MSSQL using BCP into a text (.txt) file. MySQL data is exported using: SELECT * INTO OUTFILE.... into a text file. Both use "%^&" as the field delimiter,"!@#$" as the row delimter, and escape using "\".

There can be various reasons why file hashes different:

Line Endings differ;
Spacing might differ (tabs vs spaces);
Different layout for same data
Encoding (of the file that data is saved in)
Quotes (single vs double)
Case sensitivity

What I would do, is compare both files with diff tool. git diff can come in help here as it has ability to diff word by word and if you pass it --no-index option don't even have to place them in git repository. This command can help

$ git diff --no-index --color-words file1.txt file2.txt


SQL server indexing foreign keys, covering indexes included columns I have pulled a list of FK's that do not have indexes on them.

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created ?

for example mytable below is a 20 column table with one varchar(255) and the rest ints, datetime or smallints.

it is missing indexes on

AdvertiserId
Dirty
MasterAdGroupId

AdvertiserID missing a foreign key index, but [advertiserID] is part of an index with [dirty]&[error] which are smallints. Should this have its own dedicated index ?

Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys ?

    index_keys
    -------------
    [AdvertiserAdGroupId]
    [AdvertiserHierarchyId]
    [AdvertiserHierarchyId], [AdvertiserAdGroupId], [MasterAdGroupId]
    [AdvertiserAdGroupCode]
    [AdvertiserAdGroupId], [MasterAdGroupId], [AdvertiserId]
    [AdvertiserId], [Dirty], [Error]
    [MasterAdGroupId], [Deleted], [AdvertiserAdGroupId]
    [AdvertiserHierarchyId], [Error], [AdvertiserAdGroupId], [MasterAdGroupId], [Deleted]
    [Error], [AdvertiserHierarchyId], [Dirty], [AdvertiserAdGroupId]
    [MasterAdGroupId], [AdvertiserAdGroupId], [AdvertiserHierarchyId]
    [AdvertiserId], [AdvertiserAdGroupCode], [LastSyncedDate], [CreatedDate]
    [AdvertiserId], [Deleted], [Paused], [AdvertiserAdGroupCode]

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created?

It depends on the table's access patterns. If the column is being searched a lot (and, ideally, is highly selective), then yes, you absolutely should have an index on that column, with the column as the first key column in the definition.

  Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys?

What was given in the question is somewhat unclear, and the question you've asked is a bit... confused, so let's take a step back for a second.

In SQL Server 2005+, the three most important parts of an index definition are:

The key columns, which determines the index sort order. This means the order of the key columns is very important, because SQL Server uses an index by searching for a value in the first key column, then in the second key column, etc.
The included columns, which are copies of row data tagged onto the index structure. The order included columns are specified is irrelevant.
Is the index unique? This means that the index key can contain only unique combinations of column values.

(While this is not relevant to the discussion at hand, for completeness I will mention it here: SQL Server 2008+ introduces the concept of filtered indexes, which only includes rows in the index that satisfy a predicate.)

The first thing you should do is index consolidation. This involves using the points above to combine indexes that share commonalities.

For example, consider the following two indexes:

CREATE INDEX IX_1 ON [dbo].[t1](C1) INCLUDE(C3, C4);
CREATE INDEX IX_2 ON [dbo].[t1](C1, C2) INCLUDE(C5);

These indexes share the leading key column, C1. Included columns can be specified in any order, so these two indexes could be combined as follows:

CREATE INDEX IX_3 ON [dbo].[t1](C1, C2) INCLUDE(C3, C4, C5);

Where index keys differ in their composition or other properties, you have to be very careful. Consider these indexes:

CREATE INDEX IX_4 ON [dbo].[t1](C1, C3) INCLUDE(C4);
CREATE UNIQUE INDEX IX_5 ON [dbo].[t1](C1, C4) INCLUDE(C5);

Now the decision is not as easy. You have to determine what to do based on your workload, which queries hit the table, and the selectivity of the data itself.

So to answer the question more directly: if you currently have one or more indexes where the column of interest is the first key column in those indexes, you don't have to add more indexes, because the indexes you have are useful.

If the column is searched frequently and there isn't an index with that column as the first key column, you should create an index with that column as the first key column. (Depending on query requirements, you may want to specify other columns as well, for either the key or the included columns.)

If the column is not searched frequently, you can potentially get away with having it contained in another index (not the first key column): the query may be satisfied by scanning the index that contains the column. This is not as efficient as an index seek (for many reasons), but if this operation doesn't happen too often, and the performance in this case is acceptable, you may be okay.

Remember that creating indexes isn't free -- they take up data space, log space, cache memory, and can potentially slow down INSERT/UPDATE/DELETE activity (having said that, there can be other advantages to creating indexes). It's a balance you have to strike for your environment.


Oracle Enterprise Manager - top activity I'm looking at the Top Activity view in OEM.  The left axis is labeled "Active Sessions" (up to 24 in the graph I'm looking at) and there is a line labeled "Maximum CPU".  The measured activities include: user i/o, concurrency, commit, etc...

My question:  Is OEM simply reporting the number of sessions involved in the activity, as the axis indicates?  Or is it measuring the total duration of the activity over all sessions?

To put a point to it, concurrency (we believe) is the activity of presenting a consistent block to each session that requires it.  Session A may see one version block 33.444  where session B might see a different version of block 33.444.  (Because session A cannot rows updated by B which are not yet committed, and vice versa.)

But presenting a consistent version of a given block might be a simple task, or it might  be a complicate task depending on how many uncommitted changes have been made to a block, and how many sessions are interested in it.

So if "concurrency" simply counts the session involved (as the vertical axis indicates) then we get one interpretation of the graph.  But if it counts the duration, we get a different view.

Any insights on what OEM specifically measures would be appreciated.  Thanks in advance!

Chris Curzon

The graph is showing the number of sessions in wait. The colors represent a broad category of wait class, which assists in quickly identifying the primary type of wait faced by sessions in the database.

The maximum CPU line simply gives you a marker to determine if the amount of CPU wait is reaching extremely critical levels.


MySQL update row cell with 0 value with value from closest row that does not have 0 value I have a list of entries that has a time field where unix timestamp is stored.
I recently discovered that some of the entries have that field value 0.
I want to run an update that will update those records with the value of the closest row that does not have 0 value.

This is what I have built so far but I am unsure it will use the value of the closest record.

UPDATE `records` AS `m`
  JOIN `records` AS `s` ON `s`.`time` > 0
   SET `m`.`time` = `s`.`time`
 WHERE `m`.`time` = 0

My schema.

CREATE TABLE `records` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `string` VARCHAR(254) NOT NULL DEFAULT '',
    `time` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `time` (`time`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

SET @var :=0;
UPDATE records
SET time= (@var :=CASE WHEN time = '0' THEN @var ELSE time END)
ORDER BY id;

Fiddle

Add DESC after ORDER BY if you want to update time with the time value of the next id.


How to understand a database layout? I want to understand a database layout.

It has 180 tables.

I only know the high level use case: Owncloud (File Hosting Server. Can be compared to DropBox).

How to understand the database layout?

How to identify the most important tables?

Is there a tool which helps me to understand the layout?

At the moment am interested in two facts:

ForeignKey Relations: It would be nice to have graph
Number of rows in the table.

I use PostgreSQL 9.5.5

SchemaSpy is a free piece of software that enables you to output html documentation of your databases, one of the many vendors it supports is PostgreSQL.


Slow insert performance in mysql innodb table for solr indexing I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.

Following are the InnoDb parameters.

| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 10                     |
| innodb_buffer_pool_size         | 12884901888            |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 5000                   |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 0                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 180                    |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 104857600              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 90                     |
| innodb_old_blocks_time          | 100                    |
| innodb_open_files               | 300                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 1                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 32                     |
| innodb_read_io_threads          | 64                     |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | OFF                    |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 32                     |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.1.8                  |
| innodb_write_io_threads         | 64                     |

The OS is linux and physical memory is 20GB.

Please help.

Thanks
Kuldeep

OBSERVATION #1
Larger transactions need a larger log buffer
Please expand innodb_log_buffer_size to 32M because as the MySQL Documentation says

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB.
A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

OBSERVATION #2
You have innodb_thread_concurrency set to 32. It should be 0. It allows infinite concurrency. Even Percona Server has innodb_thread_concurrency set to 0 by default.
I have written about this in the past

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)

OBSERVATION #3
Please Change the Following :

innodb_flush_method to O_DIRECT. This may alleviate the OS of caching responsibility
innodb_io_capacity to 5000 (You can set this to other value dynamically with SET GLOBAL innodb_io_capacity = 5000; or any other value)
innodb_change_buffering to inserts to help InnoDB focus on INSERTs


Best practices to run time offset delayed replication I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 days) to prevent application and human mistakes on data update/delete

Someone got some experience with that kind of setup? What are real life disadvantages and problems with that solution?

I haven't looked into the built-in replication in Postgres 9, but if you're using Slony-I with log shipping, you can just wait to apply the logs files 'til after they're a given age.  We use a perl script under cron that connects to the master server and pulls the log files, then applies them.  It should be nothing to pull stuff to the replica to queue, and then go through and apply them, stopping when you hit the ones that are too new.


Selecting a value from a row where another column is max I have the following SQL query:
SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`code`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC;

I now want to select version.name instead of version.code from the row where version.code is maximal. Is this possible? If so, how do I do this with minimal amount of queries/overhead?

Relevant Tables (stripped):
CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO `bug` VALUES();
INSERT INTO `bug` VALUES();

CREATE TABLE `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `version_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_s_v` (`version_id`),
  KEY `FK_s_b` (`bug_id`),
  CONSTRAINT `FK_s_b` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_s_v` FOREIGN KEY (`version_id`) REFERENCES `version` (`id`) ON DELETE CASCADE
);

CREATE TABLE `report` (
  `id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `stacktrace_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_r_s` (`stacktrace_id`),
  CONSTRAINT `FK_r_s` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE
);

As SQLFiddle

In your case you can simply exchange the column names, but remember that text sorts differently then integer 1.10 comes before 1.2, so if you get such numbers you have to check and change your approach. see https://lists.mysql.com/mysql/183349
SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`name`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC;


SQL Server Log Shipping restore fail - The Log Is too recent to apply The restore is missing a backup log, and the "physical_device_name" of the missing backup log is "NUL". This is a Transaction Log that gets made out of sequence with the log shipping backup job. I can't find another service/job that triggers this job. If I understand log shipping correctly, this missing log causes the restore to fail every day at about midnight.

The bkSize is 0 but the LSN number did change after the missing backup:

I would appreciate advice to resolve this error.

Regards

Niel

I use the following query to get a good overview of running Backups / LOG Shipping Status.

SELECT  [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            [b].[type] ,
            [b].[first_lsn] ,
            [b].[last_lsn] ,
            [b].[checkpoint_lsn] ,
            [b].[database_backup_lsn]
            --,f.media_set_id
            ,f.physical_device_name,
            script='RESTORE LOG DBNAME FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY; '
    FROM    [msdb].[dbo].[backupset] AS [b]
    LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
    WHERE   [b].[database_name] = 'DBNAME'
        AND [b].[backup_start_date] >= '2017-05-10 13:00:00.000' ORDER BY [b].[backup_start_date] asc;

It gives a picture of the Backups taken and so you will See which Backups you need to RESTORE them on secondary DB.

Sample :
The Backups Marked yellow need to be restored on 2ndary, only those.

Regarding your NUL backup, have you already scripted All of the stored procedures in Place and Looked for NUL in there?

Have you observed any Logins the time this NUL Backup happens? If a login FROM outside starts this Backup you could cut the login off the Server.

Hope it helps.


Prevent SSMS changing tab to space when copying text from Result Tab SSMS 2016 changing tabs to spaces when copying from Result Tab (does not matter ctrl+c or drag&drop).
For example if execute this code

SELECT 'a' + CHAR(9) + 'b'

then copy text from result tab and paste in any editor it will be space instead tab between 'a' and 'b' characters.

How to prevent this behaviour?

P.S.: Saving result to CSV working properly and saving tab (code 9). Error caused only on copying.

Go to Options and check Retain CR/LF on copy or save.


Create Views Quickstart I have setup a few buckets and now I’d like to create a view but I’m not sure where to start.  I have logged in to the web console but the views I have created to not return any results.  Also, is it possible to create a view that spans buckets?

More than likely, you have not promoted the views to be production views. When views are "development" views they will only select a very small subset of data for efficiency purposes. Once they are promoted to production and gather everything, then they will actually see the entire data set.

At this time, there is no way to span buckets with views in Couchbase. If you need to do that, I would ask why you have to have things in different buckets? What is your strategy for why you have different buckets. Usually you would split it out into different buckets if you need to isolate data for performance or application reasons since buckets have different resource allocations. Another reasons is for cardinality of data. Say you need to use views and some data will never be seen by a set of views, it might make sense to split that data into different buckets.

All that being said, take a look at Couchbase's upcoming N1QL query language that will let you query across buckets. http://query.couchbase.com/.


Can I let PostgreSQL report the offending rows when a multi-row INSERT fails? When inserting multiple rows into a table by a single INSERT INTO invocation, and the INSERT statement fails due to reasons specific to some individual rows (e.g. a value has the wrong type for the target column, or a NOT NULL constraint is violated), can I let PostgreSQL report the content of the offending rows?

For UNIQUE constraint or exclusion constraint violation errors, it seems I could use the ON CONFLICT clause to access the offending rows, but is there something similar for other row-specific violations?

When inserting multiple rows

Not sure what exactly you mean. It should already report the content of the offending rows.

# CREATE TABLE foo ( x int );
-- Two rows that work.
# INSERT INTO foo VALUES (1), (2);
INSERT 0 2
-- Three rows that explode.
# INSERT INTO foo VALUES (1), (2), ('a');
ERROR:  invalid input syntax for integer: "a"
LINE 1: INSERT INTO foo VALUES (1), (2), ('a');
                                          ^

What more content are you looking for?


AG replicas across two FCIs I have the following setup:

One Windows cluster consisting of four cluster nodes: A, B, C, D. Cluster nodes A and B in one data centre, cluster nodes C and D in a second data centre.

SQL failover cluster instance SQLFCI1 defined over nodes A and B, SQL failover cluster instance SQLFCI2 defined over nodes C and D.

SQL availability group SQLAG1 defined on SQLFCI1 with asynchronous replica being SQLFCI2. SQLAG1 associated with  listener SQLListener1.

All client apps use SQLListener1 in their application connection strings.

End result

High availability because of FCI between A and B in data centre 1 as well as disaster recovery to FCI between C and D via async availability group. That DR site has high availability as well because the availability group sits on an FCI as well.

Minimal impact to applications because they all use the AG listener name that in effect can move across all four nodes.

Quorum partner in a third data centre.

Is this a valid setup?

Is this a valid setup ?

Yes.  This configuration is detailed here:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

If you want automated failover between data centers, look at using a Cloud Witness for cluster quorum.


Ola backups happening on both Primary and Secondary AG I configured Ola's scripts on a staging AG configuration to test out the logic. When I checked the backup folder today after a week, I see that the backups are happening both on the primary replica and copy_only backups on the secondary replica.

It is my understanding that the backups are supposed to happen on the secondary replica - And I have configured the AG to "Prefer secondary". I don't want the backups happening on both primary and secondary on this 2 node AG. What can be done for this to work? Please advise.

Validate that:

Backup Priority is higher on the Secondary instances
None of the secondaries are excluded
You are looking at Synchronized (in the AG) databases
You do not have OverrideBackupPreference set in the Ola cmd line

Check your DBName. If I call sys.fn_hadr_backup_is_preferred_replica with a @DatabaseName that does not exist on the instance it returns a 1. If you have validated all this and it still returns a 1 on both replicas then the issue is not the OLA scripts, the issue is your AG configuration.

Community Wiki answer created from comments by IanG


Consolidate / sort / combine / merge data for dummies I am looking for a non-scripting solution to combine data from 3 different tables (preferably using  spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I have tried searching for an answer but have not managed to find anything useful.
So, how do I take these 3 tables...
Table 1: 4 most popular past 1 day
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |2        |4   |
|Pears    |5        |2   |
|Oranges  |10       |1   |
|Kiwis    |3        |3   |

Table 2: 4 most popular past 7 days
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |20       |3   |
|Pears    |50       |2   |
|Plums    |100      |1   |
|Oranges  |10       |4   |

Table 3: 4 most popular past 30 days
|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |550      |4   |
|Pears    |650      |3   |
|Plums    |1000     |1   |
|Mangoes  |990      |2   |

...and automatically merge them into...
Table 4: combined table
|Label    |30 days  |    |7 days   |    |1 days   |    |
|---------|---------|----|---------|----|---------|----|
|         |Frequency|Rank|Frequency|Rank|Frequency|Rank|
|Apples   |550      |  4 | 20      |3   |2        |4   |
|Pears    |650      |  3 | 50      |2   |5        |2   |
|Plums    |1000     |   1| 100     |1   |1        |    |
|Kiwis    |         |    |         |    |3        |3   |
|Mangoe   |990      |2   |         |    |         |    |
|Oranges  |10       |    | 10      |4   |10       |1   |

As, you can see, Table 4 comprises the columns from Tables 1, 2, and 3. No mathematical operations are performed.
Where there is an entry that does not have data, the cell is left blank, for example, I just got some kiwis in the shop to sell yesterday and they sold very well and got a rank but because I only just got the kiwis yesterday there is no data for them selling over the past 7 days and past 30 days. Similarly, there have been no mangoes in the shop to sell in the past 1 day or 7 days but actually they have sold very well over the past 30 days.
Please not, as far as I can tell the "Consolidation" routine in Microsoft Excel does not do what I am looking for.
Thank you!

You want to merge the data, in database terms it is called joining tables. If you want it non-script please refer here
to get a understanding how to do it non-scripted on MS-Access.


Backing up database with missing datafile Is there a way to backup a Oracle 12c database which has a missing datafile?

I dont have backups. Database is running in archive log mode. One of the data files is physically missing from the disk.

But querying dba_data_files, it shows up as a datafile and when I try to backup using RMAN or expdp, it throws an error and fails.

What are the options I have got here?

Oracle12c/RHEL 6

You can specify SKIP options in your backup command.

For example:

BACKUP DATABASE SKIP INACCESSIBLE;

or take the datafile offline, and:

BACKUP DATABASE SKIP OFFLINE;

skipSpec


Restore Database Wizard not launching I'm using SQL Server Management Studio 2012 and trying to use the 'Restore' functionality to copy an existing database into a new one.

This is something I've done a dozen times before, but I'm encountering an unusual problem: the wizard will not launch.  When I select 'Backup' from the Tasks menu, I get the expected 'wait' cursor and a delay before the tool opens, but no other issues.  When I select 'Restore', I don't get the wait cursor, and the wizard doesn't launch (even after 10+ minutes).

I have restarted both SSMS and my PC, this has not resolved the issue.

The only change I've made recently is installing several of Redgate's tools, but 30 minutes of google haven't brought to light any references to this being a known problem.

Can someone recommend a fix for this?

BTW, I have seen this question, but it sadly isn't applicable here - the wizard won't open at all, and this is in SSMS 2012, not 2014.

To pinpoint the issue being with your installation of SSMS on your workstation, can you launch management studio on the database server, assuming you have installed it on the server, and, if so, do you get the same behavior?


SQL Server Replication Alternative Software We have used SQL Server Replication for a long time and had some issues with it that sometimes we needed to reinitialize subscriptions to fix some issues & other times we needed to destroy the whole replication structure & rebuild it again!

Our main concern is that once we have a replication issue, almost all the time the easy solution will be to reinitialize the replication which is not accepted for our business requirements.

Now we are preparing to release a new big project & we are trying to look for 3rd party software for doing SQL Server replication.

Our setup includes Servers distributed in branches ( Different countries ) + mobile clients ( laptops with local SQL Server databases ) and we need to replicate data between all of these with the ability to offer article filtering.

Would somebody please suggest some alternate solutions for us?

It's highly unlikely you'll find a better merge replication solution than what's built-in.

Merge replication in particular is hard to implement.

I would suggest that if you're having issues with the current setup breaking, take it up with Microsoft. They should be very motivated to help you find the root cause of the problem so their customers are happy, and not looking for other solutions like you're asking for here.


Table with multiple lookups I bet this may be a duplicate and I'm not forming the question correctly, so feel free to point me in the right direction.

Background

We have a SQL Server with some legacy data. They're all addresses, but stored in separate tables (I know, ugh.) so, we have a tblDefenseCounsel, tblPlaintiffCounsel, tblVendor, tblInsurer, etc., all with addresses. I don't have the ability to change that currently unfortunately.

We are creating a new table for a feature that has to store a contact -- except the contact can be from any of those locations.

Goal

Organize this generic contact table in a way that will cause us the least amount of agony.

Choices

As I see it, we have the following choices:

Create a Table such as | TableReferenceID | ContactID | with a lookup table that stores the database tables its from
Create a table such as | TableName | ContactID |, which stores the table name in text
Some third option?

Question

What is the proper way to store data of this nature, or what are the valid options? Are there additional options besides those I've listed?

Have you considered modeling where contact info exists independent of type. Could a contact be used for defense counsel and latter for vendor?

Assuming you are trying to fix the data and hide it from the program might i suggest the following:

Create a generic contact table and import all contacts here
Create bridge tables like tblDefenseCounselContacts with (oldID,ContactID)
View to replace tblDefenseCounsel and triggers as needed.
repeat for other tables.


ORACLE execute error: ORA-01950: no privileges on tablespace 'PDATA' I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like this:
create pluggable database PEEI admin user PEEI_SYS identified by PEEI
roles = (DBA);
-- open PDB PEEI
alter pluggable database PEEI open read write;`

I have created another user called PEEI which should only do select, update, insert on tables owned by PEEI_SYS. I have created the user PEEI like this:
CREATE USER "PEEI" IDENTIFIED BY "PEEI" DEFAULT TABLESPACE PDATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;`
Now I would like that the user PEEI could insert rows in the table PEEI_SYS.PEEI_P_TRACKING. This table is created like this:
 `CREATE TABLE PEEI_SYS.PEEI_P_TRACKING
(
"CODE_WORKFLOW" VARCHAR2(30 BYTE),
"STATUS" VARCHAR2(15 BYTE),
"DATE_UPDATE" DATE,
"USER_UPDATE" VARCHAR2(20 BYTE),
"DEB_WORKFLOW" DATE,
"FIN_WORKFLOW" DATE,
"TIME_SECOND" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PDATA" ;
GRANT SELECT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_READ;
GRANT DELETE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT INSERT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT UPDATE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;

When I got the error I granted unlimited privileges to PEEI user on PDATA tablespaces like this: ALTER USER PEEI QUOTA UNLIMITED ON PDATA;
I have still the error. Could you please help me ?
Thank you very much in advance.
Kind regards,

You did not grant PEEI_SYS any quota in the tablespace. PEEI_SYS owns the table, and therefore the data blocks that need to be created. PEEI does not need any quota if it is not going to own objects.
ALTER USER PEEI_SYS QUOTA UNLIMITED ON PDATA;


MSSQL Server XE column client_connection_id accessible through system view/table for auditing? In our .NET application we create a SqlConnection to talk to the database.
After the connection is opened, we get the ClientConnectionId, a GUID.
Within the extended events I can manage to trace the information what happens on the database regarding this ClientConnectionId via the column client_connection_id (for e.g. in the sql_statement_completed event).
Looking at the sys.dm_exec_sessions or the sys.dm_exec_connections I can't find a matching to the client_connection_id from the extended event.
In sys.dm_exec_connnections is a column connection_id but the uuid differs from the one created by the SqlClient in .NET.
Question
Where does the SQL Server store the client_connection_id (table/view) and is this accessible without creating an extended event?
Background
Our support team still uses the SQL Profiler and don't know anything about extended events. The profiler is good enough to get some small insight what happens on the database which then can be handed over to the developing team for further investigations.
Usually we used the SPID, but there will be some changes made to the application where we will use connection pooling in the future and with ef core there will be no permanent connection opened for each client, but rather for each db query a new connection.
Now I am trying to get a solution in how to be still possible to audit the database for each client (and not too complex for the support team).
Manipulate the application name or the hostname in the connectionstring would be another possibility, but I doubt this is recommended.
Any thoughts and help are warmly appreciated.

You may use this and filter using program_name, login, IP address...

select c.connection_id from sys.dm_exec_connections c
join sys.dm_exec_sessions s on c.session_id=s.session_id


Difference between surrogate key(s) and composite key(s)? I'm a beginner with databases and have been perplexed by the various definitions to distinguish these two. Various google searches have not fully clarified their differences. Please help explain them on a simple level.

A composite key is simply a key that contains two or more columns.

A surrogate key is one which is not naturally unique to the data but is added or imposed onto the data for (hopefully) a good reason. Examples can include IDENTITY columns in SQL Server - known as autonumbers sometimes in other products.

Perhaps you are storing information about people, but you can't be sure that they can be uniquely identified by a combination of their name, date of birth etc.. If you don't have some external information about them that ascribes uniqueness (e.g. Social Security Number), you can use a surrogate key to uniquely identify the rows.