Using index on datetime field not working really

Using an index involves back-and-forth between the index BTree and the data BTree. So, if more than something like 20% of the table needs to be looked at, it is faster to simply scan the data (ignoring the index) and filter out those that fail the WHERE clause.


It appears that this was added in the 2008 time period.  You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at:

http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf

It is interesting that the request was made to prevent the CDC updates from blocking the CDC queries that select the data.  This change was posted to the Connect item on 3/18/2008.

Considering the potential velocity of change, this may make sense for this case, but it does not make NOLOCK a generally good idea for other code.


Is there a difference if I put the where condition at the join
  section?

In your case, NO. Optimizer is smart enough to understand.

Where Clause further put filter on what is produce by JOIN.

INNER JOIN :Here WHERE clause do not have much to to put filter.It has smaller resultset to apply filter.  In fact, in case of, INNER JOIN, it do not matter where you put condition in the JOIN or WHERE. Optimizer is smart enough to understand.There is no performance difference .

OUTER JOIN: Here WHERE clause  have much larger resultset to apply filter.
Since Where clause have much larger resultset to filter out, it make sense to Filter as much as possible in JOIN ON condition,so that smaller resultset is produce.

If where condition is applied in Right table ,then such LEFT JOIN is equivalent to INNER JOIN

Though in most cases of OUTER JOIN, optimizer is smart enough to understand and convert the WHERE to ON internally.

It depend upon example to example, specially in complex query involving large volume of data.It is always wise to check the query plan and decide WHERE condition accordingly.

You should use predicate column in ON ,which logically relate the joining table.

If it is filter condition then use it in Where clause.

  Should I add an index for the timestamp, row_id, session_id?

Yes. Create composite Non clustered index.Choice of Clustered index([id]) is perfect.

Create NonClustered Index NCI_Table_Action_Row_Session_Timestamp
on table_name(action_id,row_id,session_id,Timestamp)
GO

Notice the order of columns. The narrow column should be in LEFT most.
There is no point in Covering index as nvarchar(max) are not use in covering index.

  What else can I do to make it run faster?

I think instead of INNER use EXISTS.Don't use *

SELECT d.[id]     ,
      [session_id] ,
      [row_id]     ,
      [timestamp]  ,
      [action_id] ,
      [value1]     ,
      [value2]     ,
      [value3]
FROM [table_name] d
where(select 1 from [table_name] i
   where  i.[id] = X -- where X is the input
  AND i.[action_id] = 11 ---- where 11 is the input
  AND  d.[action_id] = i.[action_id]+1 --12 ---- if 12 is  input then it is more good
  AND d.[session_id] = i.[session_id]
   AND d.[row_id] = i.[row_id]
   AND d.[timestamp] = i.[timestamp]

   )


How do I reduce the logging of my procedure?

As you are completely dropping all the data and rebuilding it, if there is any overlap in the new data and old (especially if there is a lot of overlap - i.e. many of the rows in each table will be the same after the process has completed) then it would be far more efficient to only delete rows that are no longer needed, insert rows that are new to this revised snapshot, and update rows that already exist and need to change.

This could be a lot more code, but it will potentially be much more efficient.

To do this in a DB agnostic way you will need up to three statements to delete, insert and update, wrapped in a transaction to avoid partial results. In many cases updates aren't needed and you only need the delete and insert phases - you will know if this is the case as you know your data better than I.

If portability is not an issue then Oracle supports a MERGE statement that will likely be far more efficient still and result in smaller, easier to maintain, code. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm for reference documentation, if that isn't clear you will likely find many examples/tutorials that flesh out the explanation further.


Change SSIS package location (REPOST from SO)

Can you point the 'full path' to the UNC path i.e. \\<servername>\<sharename>\<directoryname>\<packagename> instead and see if that works?

Just like when you map the "X" drive to \\<servername>\<sharename> just use that in the full path of one of your packages and run to see if it'll work.

If one of your jobs work like that (assuming most all are setup the same and this way, etc), you can probably script out the SQL Agent jobs through SSMS by pressing F7 (once SQL Agent jobs is highlighted), selecting them all from the right pane window, right click, then create to new query window, then do a mass CTRL+H and do a find and replace to replace X:\ with \\<servername>\<sharename>, and then run that.

Just be sure the SSIS proxy account or the SQL Server Agent account has appropriate NTFS and SHARE permissions where the SSIS packages reside to read them.


a) when copying files from one volume to another, assuming the bigger one is also faster (more PIOPS), is it efficient to use parallelism ? as in copy a maximum of ~10 files at a time?

Limiting factors for your transfer are likely to be the speed of reading from your original storage as well as the network bandwidth between old and new storage paths. Starting parallel copies is unlikely to speed up the process, and could potentially slow down the overall transfer if your copy process requires reading multiple files which are randomly located on slow origin storage (eg. non-SSD).

Some EBS volumes (notably those restored from snapshots) may also benefit from initialization or prewarming to achieve maximum performance after being attached. See: Initializing Amazon EBS Volumes in the EC2 User Guide.

  b) some would say if i already put the effort for copying the data then i should simply wipe the data and let it resync anew - however i'm worried about the network and read impacts on the "living" primary which may degrade performance for my applications. is that a right assumption?

With MMAPv1 there are some possible benefits to copying the data files vs a resync:

You could potentially compress the data files before transferring, which may save transfer time if the files compress significantly.
The new data volume could be usable faster, as data files and indexes will already be built.

Some possible benefits of resyncing are:

If you have excessive fragmentation or storage usage, the actual data to transfer may be less than the file size on disk.
This saves some overhead of transferring index data but has the expense of rebuilding indexes on the destination node.
You could change the storage engine to WiredTiger, which includes on-disk compression. If your main concern is disk space this might alleviate some of your resource pressure. Since this is a more significant change than simply increasing storage space, you definitely want to test against your application in a staging/UAT environment.

  c) how can i measure/verify that the oplog retention is sufficient for resyncing the database after copying the files?

You could estimate how long the file transfer will take and ensure the oplog is a reasonable multiple of the worst case file transfer time. I strongly recommend avoiding any approach which leaves you without a viable secondary while you are copying/syncing data; racing against the oplog duration is risky if something goes amiss in the copy/sync process and it takes much longer than you planned for.

  d) is there a better idea/solution ?

You currently have a Primary/Secondary/Arbiter configuration. Instead of compromising replication by stopping your only secondary in order to copy the files, I recommend adding a new secondary with increased storage and dropping the arbiter (since it won't be needed if you have an odd number of voting nodes).

Once your new secondary completes initial sync you can then upgrade and resync the other secondary, and finally step down the primary. At this stage you could either drop the former primary and add an arbiter to return to your Primary/Secondary/Arbiter config, or consider adding another secondary to the replica set so you have a more robust Primary/Secondary/Secondary deployment.

For a critical production environment I would encourage you to use three data-bearing replica set members instead of two plus an arbiter. A main consideration when using a three node configuration with an arbiter is that if one of your data-bearing nodes is unavailable, you no longer have replication or data redundancy.


If the only thing you are storing in the database is the XML data, what is to stop you from storing the file directly on the filesystem and bypassing the overhead of querying the database for the single field?

If backups are your concern, there are methods to backup a directory as well.

Usage of MySQL (or other RDBMS) implies there is a relational aspect to the data you are storing. If there's no relationship, this is probably not the tool you are looking for.


Try:

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\' quote '''' CSV header"

You don't need to escape the escape character when specifying it. QUOTE defaults to double-quote so you need to pass that.


How large are blocks of data in SQL Server?

From the column documentation of sys.database_files:

  size:  Current size of the file, in 8-KB pages.

So a more meaningful query might be something like this:

SELECT
    df.name AS LogicalFileName,
    df.physical_name AS PhysicalPath,
    CONVERT(int, (((df.size * 8192) / 1024.0) / 1024.0)) AS SizeInMB
    FROM sys.database_files df;

(You can expand on that further as necessary.)


What to check for when receiving intermittent error on Kill Command

When you execute srv.KillAllProcesses; SMO will just launch a KILL <spid> for every process connected to the database.

Once those kill commands are executed, some transactions may still be rolling back. I'm not sure what may be going on behind the scenes that puts your database in the state returning your error message when transactions are still rolling back but in any case that call isn't needed.

Calling only srv.KillDatabase; will execute a ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before attempting to drop the database using DROP DATABASE, effectively killing/rolling back any active transactions.

It's the ALTER DATABASE statement that is returning the error you receive.

This can be verified by looking at a profiler trace or extended event target.

Have a look at Set a Database to Single-user Mode

  If other users are connected to the database at the time that you set
  the database to single-user mode, their connections to the database
  will be closed without warning.

There is no reason to kill the processed first and you should probably remove that line of code


If you want the file locations to be different on the secondary, which you should not do if possible, you must manually restore a backup of the primary database.  Instructions are here: Prepare a secondary database for an Always On availability group


Make sure you're running the CMD window "As Administrator".
You will also need to set the ORACLE_SID variable.

 set ORACLE_SID=MyOracleSID
 emctl start dbconsole

It should provide a more detailed error message at this point, if it does fail.


SQL Server on Linux: Allocation unit on ext4

I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of the pages.

It doesn't have to be 64k, in fact you'll generally get the best performance based on your workload and the storage vendor's preferred size. If you don't know either, generally 64k will be used.

  Is this also required for the SQL Server on Linux and if yes is this supported on ext4?

It's not a requirement. If you can do testing ahead of time and figure out the sweet spot for your hardware and workload, then that would be the best. Most of the time, the performance difference is negligible unless there truly is a bad configuration for the hardware or workload.

  At the system requirements on the docs of Microsoft is only the information of which file system but not more details.

That's because it isn't required. You can use whatever you'd like, but if you truly wanted to find the proper value - testing is required.


How to generate TPC-DS query for sql-server from templates

You can download fix error "STATE" and endcoding dump data to database at https://github.com/grahn/tpcds-kit


MySQL begin end from documentation doesn't work

While creating procedure, function or another compound statement consisted from more than 1 command, You MUST use DELIMITER statement.

The reason is simple - command to create procedure is one command. But it consists from a lot of common commands. When You enter its code, server has no any marker, what kind of command is terminated by any delimiter - simple statement or compound statement. To avoid it You must use separate delimiters for that aims. Because standard delimiter will be used when server executes procedure, You must NOT alter default delimiter used for common commands. So You must alter delimiter used for fixed compound statement.

You must update Your code:

DELIMITER @@; -- Alter delimiter to 3-symbol one
              -- not contained in compound statement we want to create

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5; -- Std delimiter, terminates single statement
  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;
@@;  -- Altered delimiter terminated compound statement

DELIMITER ; -- restore delimiter to standard semicolon


PostgreSQL event trigger to force extension installations into particular schema

Make sure you're using ddl_command_end, it can still fail or remind users which is what you want. From the docs,

  Similarly, if a ddl_command_end trigger fails with an error, the effects of the DDL statement will be rolled back, just as they would be in any other case where the containing transaction aborts.

Unfortunately, you have two options after that

Process it possibly making assumptions, but only knowing the place in which the extension was installed. For instance, CREATE EXTENSION x WITH SCHEMA CURRENT_SCHEMA and CREATE EXTENSION x will be conflated. Simple access to the command executed isn't available because some simple commands issue multiple commands (ex, serial being a macro).
Process the internal pg_ddl_command with a C function.

Because the second option sounds like less joy, let's go with the first.

Check out the list under Capturing Changes at Command End which supports objid being returned from the SRF pg_event_trigger_ddl_commands(). Note, schema_name is not what you're after.

CREATE OR REPLACE FUNCTION verify_extension_schema()
RETURNS event_trigger
AS $$
DECLARE
  schemafail bool;
BEGIN
  schemafail = (
    SELECT n.nspname = 'public'
    FROM pg_event_trigger_ddl_commands() AS ev
    INNER JOIN pg_catalog.pg_extension AS e
      ON ev.objid = e.oid
    INNER JOIN pg_catalog.pg_namespace AS n
      ON e.extnamespace = n.oid
  );
  IF schemafail THEN
    RAISE EXCEPTION 'Creating extensions into "public" is disabled';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER nag_my_users
  ON ddl_command_end
  WHEN TAG IN ('CREATE EXTENSION')
  EXECUTE PROCEDURE verify_extension_schema();

Now you can

test=# CREATE EXTENSION intarray ;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA public;
ERROR:  Creating extensions into "public" is disabled
test=# CREATE EXTENSION intarray WITH SCHEMA foo;
CREATE EXTENSION

If you just want a notification, you can change it to RAISE NOTICE


Do it in 2 steps...

Write a SELECT against information_schema.TABLES to generate one mysqldump statement per table.  Send the output of that to a file; then execute that file.  (Also, give the file execute permission).


8 months later, but here's the answer:

No problem in having a MySQL 5.5 slave to a 5.1 master. There is a problem with having it the other way around -- I've seen it fail.

Your approach is very viable and is being done frequently. You have both B and C replication from A. Then, at some stage you:

freeze A (via FLUSH TABLES WITH READ LOCK)
Verify both slaves are caught up with A's last statement.
Take A down
Change C to replicate from B, using B's SHOW MASTER STATUS
Tell your application B is the new master
Celebrate with beer

This does require a minimal downtime of a few seconds (and up to a minute) -- it mostly depends on your skills and confidence.


Get relation data on comma separated values

You can use MySQL INSTR to achieve this:

SELECT U.u_id
FROM Userassigned U
JOIN FormTable F ON INSTR(U.Fname, F.Fname) > 0;

SQL Fiddle for the same: http://sqlfiddle.com/#!9/6f27ce/1


Trigger firing despite no rows being affected

For DML actions, there are row based and statement based triggers.

Row triggers fire when (before, after or instead of) each row is affected (inserted/updated/deleted). So they will fire 100 times if 100 rows are affected and not at all if 0 rows are affected.
Statement triggers fire when an INSERT / UPDATE / DELETE statement is executed. It doesn't matter if no rows are affected. Statement level triggers fire anyway and only once for a statement (whether it's 0, 100 or a billion rows affected).

Some DBMS have only row level triggers (MySQL).

Others (like SQL Server* which is your DBMS) have only statement level triggers.

Some others (DB2, Oracle, Postgres) have both kinds of triggers.

* SQL Server CREATE TRIGGER docs state:

  DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.


Your conclusion is correct about the debian-sys-maint process.  That's not technically part of MySQL and can be disabled.

Your DROP DATABASE gave the appearance of a lockup because opening/closing/dropping tables uses a global mutex in MySQL -- only one such operation can be happening at any point in time and every other similar operation has to wait.  That wouldn't really be a problem, except that MySQL has to wait for the filesystem to report that the underlying table file(s) has been deleted, which can take a significant amount of time.

Dropping the tables individually won't eliminate the problem, but will spread out multiple, shorter blocking events over more time and be potentially less disruptive... or you might be able to use a different filesystem if dropping big tables is a frequent requirement for your environment:

http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/
https://stackoverflow.com/questions/150058/mysql-drop-database-takes-time-why


Unable to connect to pluggable database in Oracle 12c

Is the pluggable database (PDB) online?

Connect to the container database where you attached the PDB. Issue this SQL command: show pdbs; This should list all of your Pluggable Databases (including the seed PDB) and what state they are in.

You can start the DevDB database via this command: alter pluggable database DEVDB open;

If that works and the database opens and comes online, you can tell Oracle to "remember" this state via:  alter pluggable database DEVDB save state;

If, however, the database does not come online via the alter...open command, check select * from PDB_PLUG_IN_VIOLATIONS; for possible problems.

From your edits, I see that the PDB is up and available. Try connecting to the DEVDB from another computer?

When you connect to the container, you can then switch your context to the PDB via alter session set container = DEVDB;

In my Oracle 12 environments, I do not have the PDBs listed explicitly in the server's TNSNames file (just the container DB). On client PCs, I then list the PDB in the TNSNAMES file.

On the server, I use the ORACLE_SID environment variable and do not use the @service, so: Set ORACLE_SID=DEVDB and then sqlplus user/pwd
(But that is in a Windows environment.)


Trigger After Insert is preventing insertion

The trigger is part of the original transaction. Just because it happens after the original insert does not mean that everything doesn't get rolled back in the event of an error. A rollback does not just roll back the last thing that ran. Take this example:

BEGIN TRANSACTION;
INSERT dbo.table1 ...;
INSERT dbo.table2 ...;
ROLLBACK TRANSACTION;

Surely you don't expect the rollback to only affect the 2nd insert?
You might consider using TRY/CATCH or an INSTEAD OF trigger, which will allow you to handle other stuff without suffering from the errors. Or simply checking first
Your use of declaring a bunch of variables, assigning them from a join, and then inserting seems to be both inefficient and problematic. What happens if more than one row matches the join? You know that if you have a multi-row operation, the trigger only fires once, so you'll only end up with one arbitrary row being inserted, right? Why not:

INSERT dbo.TBL_USER_LOGS(DEV_NID, LOG_DATE, USE_NID, LOG_MODE)
  SELECT DEV_NID, LOG_DATE, REL_ENROLLNUMBER, REL_MODE -- table prefixes?
FROM dbo.TBL_DEVICE -- always use dbo prefix!
INNER JOIN dbo.TBL_READER_LOG
ON DEV_ORIGINAL_NID = RDR_NID -- which table do these columns come from?
WHERE DEV_TYPE = 1; -- which table does this column come from?

As an aside, I notice that your query doesn't seem to take the inserted pseudo-table into account. Shouldn't the insert have something to do with the row(s) actually affected by the trigger?


mySql Relationships / Index - Checking for duplicates

I'm not sure if I understand correctly. As long as you have a unique key on both columns, it does not matter what order the columns (or combination) they are in. Otherwise, the tuple must also exist in the referencing table in order to enforce it's uniqueness.

CREATE TABLE tbluser (
  user int,
  details int,
  primary key(user, details));

CREATE TABLE tblconnections (
  user int,
  details int,
  connection int,
  primary key(user, details, connection),
  unique key(user, details),
  foreign key (user, details)
    references tbluser (user, details));

Make sure that you specify a column list when you make this INSERT.


Safe to delete old data after pg_upgrade?

They generally compress well.  I'd just tar and compress it and remove the original, and your monitoring system will probably be happy. (And anything trying to use it should start throwing errors once it is no longer there, and then you can fix them). But you should have a bigger-picture data retention policy, and that should govern how long you keep the compressed copy.

At some point enough activity has happened in v10, that if a major problem surfaced you would just have to fix it rather than rolling back to v9.5 and losing the changes made in v10.  At that point, the old copy is pretty useless except for PITR purposes.


How can I drop all triggers in a single database?

You can use Dynamic SQL and the sys.triggers DMV to build query that you can execute.

is_ms_shipped excludes any triggers that were shipped with SQL Server.
parent_class_desc filters for object level triggers, rather than database level.

Change the PRINT to an EXEC once you are happy with the output.

USE system_db_audits;
GO

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

SELECT @sql +=
    N'DROP TRIGGER ' +
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' +
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

PRINT @sql;


SYSDBA is not a user, it is a system privilege. When you connect as / you are connecting to the SYS user. You don't need a password when you connect as you are - as long as it's a local connection from an OS account in the DBA group.

You haven't said what happens when you try to connect, or why you think you need a password; possibly that is that failing (in which case giving the error message would be helpful) and giving you a SQL*Plus login prompt, maybe indicating you aren't in that group.

Passwords are stored within the DB, the exact location depends on version; but they're stored hashed (of course) so you're not going to be able to get the actual passwords in plaintext (without a lot of time and effort anyway).


Is the SQL Server IsShutdown property useful to determine whether a database is in a good state?

A better practice might be to look at the status of the database in sys.databases or sysdatabases.  If the status is not 0 then there is probably a problem (unless you are using mirroring, log shipping, etc. where the database status shouldn't be 0).


You can select SQL:StmtStarting and SP:StmtStarting along with SQL:BatchStarting or SP:Starting

Better to use server-side trace rather than using GUI.


Majorly FULL Backup job runs the time we get this alerts.DBs which are close to 1.2 TB

Memory allocated to SQL Server is 120 G and backup running is for 1.2TB + database and in such scenario of course a high page write would be done. IF this message is only coming when backup is running you might  ignore it. In your exact previous thread, I asked you to ignore it as well.

I also asked you to collect perfmon counter values using performance data collector set and this is much needed because to answer your question whether the alert actually should be considered or can be ignored depends on that. You have to have performance/system benchmark to which you can compare other results.

NOTE: Seeing the database size I can say with confidence that adding more RAM would always help. Ideally if you take my advise RAM allocated to SQL Server should be 1/3rd of total database size. RAM's are cheaper those days instead of wasting too much effort in solving an alert I suggest you to go ahead and add more RAM, that would really be worth.

If you want me to analyze the data collector result personally my website has my mail id send me the details I can help you with the analysis.


How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user?

When you restore a database all the users and privilege assigned to users (including database role membership) from that database get restored at the destination server.  If you already have a login in the destination server with matching Server-Level Identification Number (SID) then you do not have to do anything.

If you do not have a login you will need to create one:

Copy login from source to destination using this or this.

You have have a login with different SID you can match those using solution from here.


MongoDb linearizable read concern restrictions

Dmitry,

As of MongoDB v3.6, MongoDB only supports the linearizable read concern on single documents, hence the wording of the documentation. However, it doesn't mean you have to run the query using a unique index, but that you have to be sure your query only returns one document. Of course, it can only help to have a query filter that uses a unique index (such as the default index on the _id field), since this will ensure that your query returns a single document.

In your specific cases, queries #2 and #3 can enforce linearizable reads and it will not return stale reads.

For query #4, you will also get a linearizable read, assuming the query only returns one document (which is your assumption).

For query #1, you're not guaranteed to get linearizable reads IF your query returns more than one document so you can indeed get stale reads in that situation (and your cluster happens to be partitioned at the time the query hits the old primary).

Note that your query syntax for the find() method means that you're requesting in documents that include an A property with a value equal to 1 (as a double) [and a B property of value 1 in query #3), while your ensureIndex({A:1}) command specifies that the index should be built on the A attribute. I hope you are aware of that because your queries are otherwise a bit confusing.


Does all of the rows affected by the insert statement from db1 gets
  transfered into db2 immediately and are waiting for the commit line to
  execute in order to make the insert official?

Transafer is made immediatly, but other sessions will not see inserted data until commit (depends on transaction isolation level)

  Or, are the rows affected by the insert statement are still inside db1
  and are just waiting to be transfered into db2 once the "commit"
  command executes?

Data are not waiting for commit, just storage engine wait for commit to make changes persisted.

  I wanted to know this facts because I will be copying data from a
  database through the internet and I'm worried that I might lose some
  data in the process... Any help would be greaty appreciated.

Every TCP based connection guarantee data integrity.

You should use replication instead of this approach


What is the different between resource pools and workload groups in sql server?

Resource Governor Concepts

  Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL
  Server instance inside of a SQL Server instance.
  Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload
  allows for aggregate monitoring of the sessions, and defines policies
  for the sessions. Each workload group is in a resource pool.


Using both DISTINCT and ORDER BY in json_agg

WITH cte AS (SELECT DISTINCT * FROM test)
SELECT json_agg(cte.* ORDER BY index) FROM cte GROUP BY tag;


How to make Postgres autovacuum not impact performance?

Few basic points could alleviate your fear.

Running autovaccum will eventually improve the performance of queries. It is not something to harm the performance. This is because autovaccum does the garbage collection of dead tuples in table. so queries need to scan though less number of tuples.
In your question, it is not clear how you analysed that autovaccum is creating performance problem. if possible please provide details.
Autovaccum algorithms/logic in Postgres is continuouly improved in new versions. for example, Version 9.2 received a patch for skipping a table if there is lock. so autovaccum will do better job in handling bloat. This logic is further improved to skip blocks which are having locks. in latest version 9.6 (which is in development) received a patch to completely skip all the blocks where all tuples are already freezed.
Robert Hass posted about this recently: http://rhaas.blogspot.in/2016/03/no-more-full-table-vacuums.html
You may want to fine tune autovaccum related parameters to suit your system. if possible take the help of expert DBA. Each parameter is documented very well in Postgres documentation : http://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html

So Regarding your question : "Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?"

Answer is yes. by fine tuning the parameters mentioned in the above link you can achieve the best results for your system

A general advice will be to always upgrade to latest stable version of PostgreSQL to take advantage of all the autovaccum improvements.
Always we should keep in mind that autovaccum is very necessary for keeping the performance of the database. If there is CPU or Memory starvation in your system, consider getting better machine.
otherwise tables will become bloated and performance goes down over a period of time.


Mysql getting first and last value from timestamp column

full answer required more understanding of all tables structure, but You need start from debug Your inner query:

select
    distinct tasks_id,
    complete,
    createdate,
    min(createdate) as firstEntry,
    max(createdate) as lastEntry
from tasklogs
WHERE complete in (90) and createdate > '2018-08-25'
GROUP BY createdate

it wrong (it wrong and with sql_mode='', and total), with GROUP BY createdate, all 3:

createdate
min(createdate)
max(createdate)

would be the same

as variant - may be You need GROUP BY tasks_id, and include only 3 column:

task_id
min(createdate)
max(createdate)


Extremely bloated transaction log during delete

So my question is, am I logging every delete from every index when deleting from the table?

Of course you are.  The log contains a record for every change to every page with sufficient detail to either redo or undo the change.

If so, is that normal behaviour or could the reason be because of missing clustered index?

Unrelated.  You're deleting 18 million rows from each of 17 seperate "indexes", 16 nonclustred indexes and one heap.
And it's safer to disable/rebuild the non-clustered indexes than drop/create them because the index definitions are retained on the server.


What impact do indexes have on the performance of table modifications?

As @mrdenny said, every index will need to be changed with INSERT/UPDATE/DELETE operations.

If you are weary about an index is actually be utilized to its fully capacity, and that it's not just a performance degrade, you should query the sys.dm_db_index_usage_stats.

Here are some important fields that are returned:

user_seeks
bigint
Number of seeks by user queries.
user_scans
bigint
Number of scans by user queries.
user_lookups
bigint
Number of bookmark lookups by user queries.
user_updates
bigint
Number of updates by user queries.
last_user_seek
datetime
Time of last user seek
last_user_scan
datetime
Time of last user scan.
last_user_lookup
datetime
Time of last user lookup.
last_user_update
datetime
Time of last user update.


Interesting question and a +1. I was interested by this because I can see several use cases for this fuctionality.

Unfortunately, for your case where you cannot switch on general logging, there is only one, rather inadequate, workaround.

That is to use the SQL_LOG_OFF variable to disable logging for a given connection. An ideal solution would have been to have an "SQL_LOG_ON" variable as one can do in Oracle (equivalent) - maybe you could try and switch logging off for all but the connection(s) of interest?

Furthermore, and regretably, this requires the SUPER privilege. Again, this may not (even probably not) be possible in your case.

Depending on the severity of your problem, working hours and server load at given times, you may be able to find a use for Percona's pt-query-digest which can help with log analysis. Small comfort, but as usual PostgreSQL is streets ahead of MySQL (1, 2).

If you'd care to file a feature request, I'd be happy to follow up with a "me-too" if you post the link back here.


Impact of an Asynchronous Mirror / Full Recovery Model on Performance

With an async mirror there should be little to no performance impact on the active system.  When you are in full recovery there is more transaction log activity on the system than if the database was in simple recovery mode, but that's about it.  Are you seeing specific waits or something that prompted the question?


Default value format of a stored procedure parameter(of datatype : money)

I checked the Decimal symbol in the Regional settings and changed it from ',' to '.'

Now,the CREATE PROCEDURE display it with '.' instead of ','

Thank you for your suggestions!


Deriving a computed column based on several nullable columns

You could use the CONCAT function in SQL Server 2012 or later, which automatically ignores NULL, and implicitly converts the inputs to string types if necessary.

Example:

CREATE TABLE #Demo
(
    [Tag Type] char(1) NOT NULL,
    [Parent Tag Type] char(1) NULL,
    [Tag Area No] integer NOT NULL,
    [Tag seq No] integer NOT NULL,
    [Tag Suffix] char(1) NULL,

    [Tag] AS
        CONCAT([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
);

INSERT #Demo
    ([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
VALUES
    ('T', NULL, 1234, 1, NULL),
    ('T', 'P', 5678, 1, 'S');

SELECT * FROM #Demo AS D;

Output:

Tag Type | Parent Tag Type | Tag Area No | Tag seq No | Tag Suffix | Tag
:------- | :-------------- | ----------: | ---------: | :--------- | :-------
T        | null            |        1234 |          1 | null       | T12341
T        | P               |        5678 |          1 | S          | TP56781S

... and just in case the example was simplified and a separator is to go between the elements, check out CONCAT_WS() introduced in SQL Server 2017.


Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance

How up-do-date do your read queries need to be?

You could partition the database by time if the map just needs to show the most recent measurement.  This would reduce your query load for the map.

For the history of a given point, you could hold a second store by x and y showing the history.  This could be done with a nightly refresh/update as the the historical data won't change.

Then you could pre-compute averages at more coarse resolutions for integrating with maps at different zoom levels.  This would reduce the number of points to retrieve for large map areas (zoom out).  Finer resolutions would be used for more zoomed in maps which were querying smaller areas.  If you really need to speed this up you could compute tiles as blobs and interpret them in your application.

Because these would involve some re-computing of aggregate information there would be some latency in query results.  Depending on how much latency was acceptable you could use this sort of approach to optimise your reads.

OK, so your points need to be computed averages over time.  With this computation I guess your actual queries come down quite a lot from 22 trillion items as the raster values can be pre-calculated for querying.


Disable MySQL binary logging with log_bin variable

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here.
The key part of the question is:

I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf

You can do this from an included option file using the option skip-log-bin. E.g. you might create /etc/mysql/conf.d/disable_binary_log.cnf with the following contents:
[mysqld]
skip-log-bin

You can add skip- as a prefix to other options to disable them in this way too.
Related Q & A: To clear currently accumulated logs, see this answer.


"it depends"

Using a table variable or temp table with requires overhead of creating populating this object

However, if the you require multiple processing steps then this is small compared to querying the same data over and over, especially as the query gets more complex.

Also, for multiple steps, using a table variable or temp table means working on the same set of data for all steps. Typically the underlying data usually changes as a result of other processes.

Finally, have you tried both techniques and benchmarked for your real situation? I use both approaches depending on all of the above factors. Sometimes it doesn't matter of course...


Because of deferred segment creation.

In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace.  Oracle doesn't actually create the segment until you try to insert data into the table.  This is a difference from earlier versions in which the segment was created when the table was created rather than when data was inserted.

The reason for the new feature is that there are a lot of packaged applications on the market that create potentially hundreds of tables that will never have data because the customer isn't using some specific module of the project that uses those tables.  It was annoying for someone that packaged a big ERP tool, for example, to end up with potentially GB of space allocated to tables that would never have any data.

You'll only see deferred segment creation if you've set COMPATIBLE to 11.2 and if DEFERRED_SEGMENT_CREATION is set to the default of TRUE

SQL> conn / as sysdba
Connected.
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> create user deferred identified by deferred default tablespace users;

User created.

SQL> crant create table, create session to deferred;
SP2-0734: unknown command beginning "crant crea..." - rest of line ignored.
SQL> grant create table, create session to deferred;

Grant succeeded.

SQL> conn deferred/deferred
Connected.
SQL> create table test( col1 number );

Table created.

SQL> insert into test values( 1 );
insert into test values( 1 )
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


Master-Master replication MariaDB failover resync

I would recommend to upgrade to MariaDB 10.0 and use Global Transaction IDs. Replication isn't crash safe in 5.5, so you might encounter issues such as the one you've described above.


column name like '%search%' cannot take advantage of standard BTREE/B+Tree indexes on MyISAM/InnoDB, so you are effectively doing a full table scan. The index is useless, so every single row has to be compared individually.

In order to perform string searches in a performant way you have to create a FULLTEXT index and use the operator MATCH ... AGAINST.

Alternatively, you can use an external indexing engine like Sphinx or Apache Solr.

Edit: Good source material on full text search: http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql


What happen with the Log backup if I set the SQL Server clock back?

A point-in-time restore to an a timestamp that appears twice in the log file will either fail, or arbitrarily select one occurrence of the target time.  I don't know which, and it doesn't really matter. You can restore to LSN instead of a timestamp using STOPMARK instead of STOPAT.


Why isn't the following query using the `always` index?

It might be possible to rewrite it into a UNION

SELECT * FROM (
 (
  SELECT movies.* FROM movies
   WHERE site='5' AND always='true'
 ) UNION
 (
  SELECT movies.* FROM movies
  INNER JOIN showtimes ON showtimes.movie=movies.id
  WHERE site='5' AND showdate BETWEEN '2012-4-11' AND '2012-04-18'
 )
) AS movie_shows
GROUP BY id
ORDER BY listorder

I haven't tested the above, but am curious the results of the EXPLAIN

Your always attribute is failing in two ways:

It is very low selectivity as @ypercube points out, since there are only two possible
values. MySQL will discard using these type of indexes
The query has OR. Meaning it would have to do a table scan anyway to get match both conditions.


What is the optimized way to pass multiple parameter from a single table to a function?

As a complement to Edgars answer you can do it without declaring any variables as:
SELECT dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name = 'Delhi'
  AND y.place_name = 'Mumbai' ;

I wouldn't expect it to perform any better though.
In addition you can calculate several distances at once
SELECT x.place_name
     , y.place_name
     , dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name in ('Delhi', 'Bangalore')
  AND y.place_name = 'Mumbai' ;


why there is no incremental backup in sql server

It seems you are coming to SQL Server from Oracle background and hence looking for similar terminology. Incremental backup in Oracle is similar to that of differential backup in SQL Server.
Below is definition of Incremental backup from Oracle:

An incremental backup copies only those blocks in a data file that
change between backups. A level 0 incremental backup, which copies all
blocks in the data file, is used as a starting point for an
incremental backup strategy.
A level 1 incremental backup copies only images of blocks that have
changed since the previous level 0 or level 1 incremental backup.
Level 1 backups can be cumulative, in which case all blocks changed
since the most recent level 0 backup are included, or differential, in
which case only blocks changed since the most recent level 0 or level
1 incremental backup are included.

And below is definition of Differential backup from Microsoft:

A differential backup is based on the most recent, previous full data
backup. A differential backup captures only the data that has changed
since that full backup. The full backup upon which a differential
backup is based is known as the base of the differential. Full
backups, except for copy-only backups, can serve as the base for a
series of differential backups, including database backups, partial
backups, and file backups. The base backup for a file differential
backup can be contained within a full backup, a file backup, or a
partial backup.

Hope above clears your concern.


While I don't know of any tool that provides this information specifically, Percona Server does have a setting (log_slow_verbosity=full) that enables extended statisitics in the slow log along with microsecond granularity.  The data does not explicitly include CPU utilization, but it does provide a number of additional statistics about the internals of MySQL and InnoDB that may help you determine whether or not the problem is CPU related or a result of poor optimization.

This is an example of the additional information in the slow log:

# Time: 120114  6:34:33
# User@Host: user[user] @  [10.10.10.10]
# Thread_id: 28313080  Schema: mydb  Last_errno: 0  Killed: 0
# Query_time: 0.588882  Lock_time: 0.000068  Rows_sent: 3  Rows_examined: 183839  Rows_affected: 0  Rows_read: 100
# Bytes_sent: 121  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 9903E4DB1
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 11359

Also, pt-query-digest from the Percona Toolkit understands this additional information and will summarize the slow query log for you and produce output, per query, that looks like this:

# Query 1: 0.09 QPS, 0.07x concurrency, ID 0x20112A1CCDBDBA4E at byte 4337645
# Scores: Apdex = 1.00 [1.0], V/M = 0.01
# Query_time sparkline: |     ^  |
# Time range: 2012-01-14 06:34:33 to 08:51:57
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         63     775
# Exec time     59    539s   522ms   855ms   695ms   816ms    87ms   640ms
# Lock time     18    60ms    43us   498us    76us   103us    36us    66us
# Rows sent      1   3.15k       0      38    4.17   12.54    4.90    2.90
# Rows examine  22 135.93M 179.38k 179.90k 179.60k 174.27k       0 174.27k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      0   3.43k       0     118    4.53   20.43   11.93    0.99
# Bytes sent     1 103.07k      82     576  136.18  246.02   63.85  118.34
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     5 222.51k     294     294     294     294       0     294
# InnoDB:
# IO r bytes     0       0       0       0       0       0       0       0
# IO r ops       0       0       0       0       0       0       0       0
# IO r wait      0       0       0       0       0       0       0       0
# pages distin  63   8.44M  10.66k  12.72k  11.15k  11.34k  357.67  10.80k
# queue wait     0       0       0       0       0       0       0       0
# rec lock wai   0       0       0       0       0       0       0       0
# String:
# Databases    mydb
# Hosts
# InnoDB trxID 9903E4DB1 (1/0%), 9903E4E09 (1/0%)... 773 more
# Last errno   0
# Users        user
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mydb` LIKE 'table'\G
#    SHOW CREATE TABLE `mydb`.`table`\G
# EXPLAIN /*!50100 PARTITIONS*/

...followed by the query itself.

I have blogged a bit about using pt-query-digest and other tools for finding performance bottlenecks (and promised more blogging, but haven't gotten around to it yet).

Another tool which may help you is pt-stalk and pt-collect.  This will allow you to set a threshold based on a running server variable, such as when the number of concurrently running threads jumps up (Threads_running) and then collect a wealth of data about the running system, including cpu, I/O, MySQL statistics, the running processlist, etc to give you the ability to perform a thorough post-mortem.

The additional statistics are only available in Percona Server, but these tools will all work with MySQL GA.


How to grant permissions to users based on table information?

Something like that could be achieved through dynamic sql, or via using stored procedures instead of views (for selecting) or triggers (for controlling inserts/updates).alter

But you can also create the memberview to return values according to the currently logged on user, which is probably more practical:

create view memberview as
  select Dept, Name
  from Employees
  where Dept= (
    select Dept
    from Employees
    where Name = coalesce(nullif(mid(current_user, 1, locate('@', current_user)-1), ''), current_user)
    );


What's the difference between "shrink" file vs simply reducing the initial size?

That "Initial Size" label is misleading.  It's really the "current size" of the file.  Also, changing that number and clicking "OK" does a SHRINKFILE behind the scenes, so the two things you're comparing are actually...the same thing
Here's my copy of the StackOverflow2010 database (where the current size really is 9,105 MB):

If I change that to "5":

And then click the "Script" button in the top left of the window, this is what SSMS generates:
USE [StackOverflow2010]
GO
DBCC SHRINKFILE (N'StackOverflow2010' , 5)
GO

So the answer to your question is that these two features of SSMS do the same thing.
By the way, I highly recommend using that "Script" button in SSMS frequently as you are learning.  It's a great way to understand what the SSMS GUI is doing, and is also helpful in automating and overcoming the limitations of the user interface as you get more comfortable with T-SQL scripts.
Note that you should be wary of shrinking database files, see here for more information:
I Need to Shrink My Database - I just freed a lot of space
When is it OK to shrink a Database?


It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID):

SELECT
    customerID,
    eventID,
    ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency
FROM
    customerEvent
ORDER BY
    COUNT(*) OVER (PARTITION BY customerID) DESC,
    customerID,
    frequency DESC ;

The second part of the order by needs to be customerID just in case of ties. I assume  that if you have two or more customers with the same number of events, you don't want their events mixed up.


How to configure Oracle traffic director for full site failover to have high availability?

For a true HA architecture, HTTP traffic must be balanced with a front-end load balancer on a separate host. See here for a list of open source balancers (any of which would be simpler and cheaper to implement than OTD):

https://geekflare.com/open-source-load-balancer/

Oracle DB connections should be configured for high availability failover as described here (use the architecture that best fits your technology stack):

https://www.oracle.com/technetwork/database/availability/client-failover-2280805.pdf

An example of how to implement tomcat's native clustering to compliment the load balancing can be found here on my blog site:

https://pmdba.files.wordpress.com/2019/09/ha-oracle-ords-apex-with-http-header-authentication.pdf


Can I restore a .bmp file of full database backup to an Oracle 12c EE instance running on RDS?

After exchanging several comments I came to know that you have Data Pump file of full database. Anyway you can use the SQL Developer to import export database objects. Just follow the steps:

1) Launch the SQL Developer and go to View menu and click on DBA.

2) Then add database connection to perform Data Pump operations.

3) Click on Data Pump and select the appropriate job wizard.

4) Right click on Data Pump and Data Pump import wizard.

Useful link:
 Using DBA Features in SQL Develope


General answer is that you could remove the paritioning and the performance would not drop. Hitting only one partition is quite similar in performance compared to filtering out 95% of the row based on a proper index. Partitiions generally are not for increasing select performance, but for allowing super fast DROPPING of partitions (when the data falls out of scope).

So, you really need to do the basics - get the slow queries, analyze them, find out how to write them better and / or whether other queries are blocking them. Unless you have really special cases (which we can not evaluate given the information you give)... the partition is totally not helping you in the selects.


Problematic slashes and questions marks in unique index values

Your problem is that you passes unicode strings as non-unicode.

Your '281/263-8400' is a string of 15 characters, not 12, there are 3 non printable 8206 symbols, Left-to-right mark

select len('281/263-8400'); -- 15 !!!

Try this code where I pass the unicode values as unicode and see that there is no problem at all:

CREATE TABLE dbo.Phone
(
  Id int identity(1, 1) primary key,
  Phone nvarchar(448) not null
)
go

create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go

insert into Phone(phone) Values (N'?281/?263-?8400');
insert into Phone(phone) Values (N'281/263-8400');

select * from Phone;

And this is what your string '281/263-8400' really contains (dbo.nums is my table that contains natural numbers):

declare @t table(col1 nvarchar(100), col2 nvarchar(100));
insert into @t values (N'?281/?263-?8400',  N'281/263-8400');

select n, unicode(substring(col1, n, 1)), unicode(substring(col2, n, 1))
from @t cross join dbo.nums
where n <= 15;

Now what happens when you pass your unicode string as non unicode.

Your non-printable symbol 8206 is transformed into ?, that is how non-unicode strings works: every character that cannot be find in the corresponding codepage and represented as ascii code is substituted with question mark.

So for example if you use Latin Collation and try to compare Hebrew and Cyrillic characters (the same number of characters) as varchar they always be equal just because they are transformed to question marks while comparing as nvarchar they are different:

So if now you'll try to insert these 2 values (Hebrew + Cyrillic) into your Phone table passing them as non-unicode (without using N), only one of them will be inserted, and the other will be rejected by unique constraint.
And if you'll try to select from Phone, '?????' will be returned


I thought about another ColumnFamily "ArticlesByDateAndCountry" dynamic columns.

You're on the right track with this thought, but I'd stay away from dynamic columns.  Currently, there isn't a way to really manage column families with dynamic columns in CQL3.  So, the only real solution is to go the route of creating it in the cassandra-cli, which is being deprecated.  Sticking with CQL tables gives you a much easier path to data access, and that in itself (IMO) makes it worth it.  Besides, all the latest drivers work with CQL, so you're really backing yourself into a corner by choosing a path that can't be managed by it.

While it may not be readily apparent, there is a way to solve your current problem to adequately serve your queries.  I would build a (CQL) table, like this:

CREATE TABLE ArticlesByDateAndCountry (
 countrycode text,
 articledate timestamp,
 field1 text,
 field2 text,
 PRIMARY KEY ((countrycode),articledate))
WITH CLUSTERING ORDER BY (articledate DESC);

Note: I have created two sample payload fields, field1 and field2.  I'm sure your payload fields will vary.  Also, I have opted to use a timestamp instead of a timeuuid, as it makes the example easier.

Essentially, this will group your data by countrycode.  And within each countrycode, your data will be sorted by articledate.

SELECT articledate, field1, field2
FROM ArticlesByDateAndCountry
WHERE countrycode='US'
AND articledate >= '2015-01-23 00:00:00' AND articledate < '2015-01-24 00:00:00';

You should read Patrick McFadin's article Getting started with Cassandra time series data modeling.  It has several examples that are quite similar to what you are doing here.  While I have demonstrated this with the timestamp type, you could very easily make this work with time UUIDs instead.  Here is a link to DataStax's documentation on Cassandra's timeuuid functions that I am sure you will find useful.


How to tell if database tuning is fruitful (PostgreSQL)

How well the database is running is just how well SQL statements are running.

Pick a representative sample of SQL statements that hit each database on your server. Picking a sample is easy; picking a representative sample isn't. Ideally, a representative sample should  include some SELECT, INSERT, UPDATE, and DELETE statements. But it might not be practical to run INSERT, UPDATE, and DELETE statements against a production database.
Put them under version control.
Run them pseudo-randomly against the server. The goal is to get representative performance, not to get the fastest performance.  The last time I did this, I didn't have much time to spend on this stuff, so I hoped that running the statements somewhat randomly would be representative. I expected that sometimes they'd hit the cache, and sometimes they wouldn't, just like user queries.  How you execute these queries can bias your results; be careful and thoughtful.
Time the execution. Save the timings in a database.
Plot the timings on a control chart. Done correctly, this will show you  baseline system performance, random variation around the baseline, and patterns that suggest that something might have changed. If you improve overall server performance, for example, expect the pattern to shift toward the lower control limit.
Think about the right time to change your control limits. (Same reference)


My question really is, is mysql a good choice if you want to store multiple values in one column?

I would suggest that the answer to this question is no - there is a better choice on all 3 levels for the treatment of arrays in database servers.

1. Simple arrays:

MySQL doesn't really have an array datatype - the closest thing they have is the SET datatype, the functionality of which is very limited. As you note in your question, a search leads to lots of links to PHP code which implements array functionality in the app rather than the db.

PostgreSQL has a an ARRAY datatype with a large range of functions to deal with manipulating, comparing and finding elements in arrays.

2. JSON:

Nowadays, arrays have been largely superseded by JSON - hardly surprising since JSON is basially an array++ (but XML-- :-)). This is true for MySQL and for PostgreSQL.

There are a large number of JSON functions available on both systems - MySQL and PostgreSQL - but more on PostgreSQL. I'll use the terms JSON and JSONB (binary - example is for mongodb but applies to binary JSON generally) interchangeably. MySQL JSON is only binary, PostgreSQL has both - but JSONB is generally regarded as much more efficient. See this post for a good discussion of what PostgreSQL "NoSQL types" are good for. The "NoSQL" is a misnomer; you can do SQL JOINs using these types.

With respect to indexing - MySQL essentially implements this by means of a GENERATED column hack whereas PostgreSQL implements 3 sorts of index (Gin, Btree and Hash - see section 8.14.4. jsonb Indexing here) for JSONB.

Joins can be done between JSON and tables in both MySQL and PostgreSQL.

Overall, it is fair to say that JSON has been around longer and is more mature in PostgreSQL than in MySQL.

Classic SQL:

You can of course implement some "multi-element" functionality by using a classic Associative Entity approach. Associative entities are otherwise known as  joining, bridging, many-to-many or m-to-n tables. This strategy is outlined here with an example using students enrolling in university courses. 1 course can have many students and 1 student can attend many courses. The joining table contains the PRIMARY KEY from both the course and student tables and the PK of the joining table itself is the two together - a student can't be in the same course twice!

In favour of the classic approach, Erwin Brandtstetter (a guy well worth listening to about databases!) pointed out in his answer referring to a JSON solution: (Aside: A normalized DB design with basic data types would be way more efficient for this.). JSON is not a panacea and judgement must be exercised in its use. There's been a lot of bandwagon-hopping in recent years with the NoSQL "Cambrian Explosion" from ~ 2008 - 2015 (Mongodb especially springs to mind). Now virtually all of the NoSQL systems have or are trying to put SQL interfaces and instrumentation into their systems. For my money, if your arrays are relatively simple, stick with the classic approach, otherwise go with JSONB.

SQL wasn't designed for and is not good at manipulating comma-separated lists of values. In any case they are a breach of Rule 2 of Codd's Rules which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.. With .csv lists a further positional parameter is required which SQL doesn't deliver but various systems have "tricks" to get round this. SQL's forté is "slicing and dicing" records made up of small simple atomic datums.

PostgreSQL is the F/LOSS (Free/Libre and Open Source Software) system which comes closest to offering the best for all 3 array methods. MySQL is more widespread and possibly has better inbuilt and 3rd party replication and/or sharding, but PostgreSQL is more standards compliant and has been making strides in those areas recently. It also has superior JSONB functionality.

p.s. - oh, yes, I forgot - welcome to the forum! :-)


Is my case statement broken by the use of outer apply?

The Outer Apply is working like a Left Outer Join so when no row is returned by the Outer Apply a row filled with NULLs is returned.  I would question the need for the Outer Apply but placing a Coalesce(GIVE_BACK1.CLUB_TOT,0) in the SELECT list would fix the immediate issue.


Consequences of changing NLS_TIMESTAMP_FORMAT

The database-level NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameters are not particularly useful because they are always overridden by the client settings.  So even if you change the database setting, 99+% of the time when a user comes along and connects to the database, their session is going to set a NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT based on the client's NLS_LANG or other national language settings (i.e. applications using the thin JDBC driver use the JVM's internationalization settings rather than relying on the client NLS_LANG) and overrule the setting you made in the database.  There are a few corner cases involving database jobs using DBMS_JOBS or DBMS_SCHEDULER where there is no client where the database NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT are used, I believe, but those are rather rare.

You could create a login trigger that did an ALTER SESSION to set the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT for your sessions.  That would overrule the settings the client requested when it created the session.  As Nick points out, you'd have to worry that other queries are relying on the current setting for implicit conversions and your change might break that code.

In general, you're much better off avoiding implicit conversions wherever possible but particularly when it comes to converting strings to or from dates and timestamps simply because there are so many different formats that you're setting yourself up for problems.  If you want to specify a date literal in your code, you're much better using the ANSI date and timestamp syntax

SQL> ed
Wrote file afiedt.buf

  1  select cast( date '2011-09-14' as timestamp ) dt,
  2         timestamp '2011-09-14 13:15:30' ts
  3*   from dual
SQL> /

DT                             TS
------------------------------ -----------------------------------
14-SEP-11 12.00.00.000000 AM   14-SEP-11 01.15.30.000000000 PM

If you want to convert a string to a date or a timestamp, you're better off using a TO_DATE or a TO_TIMESTAMP with an explicit format mask.


I think you need this:

Select * from b.table as b
where  not exists
     (SELECT 1
      from A.table as a
      where a.C1=b.C1 and a.C2=b.C2 and a.C3=b.C3 ) ;

You shouldn't add b.table in the subquery's from clause, because you already have it under the b alias in the main query.
You don't need to specify columns in the subquery's select clause, because that doesn't have any effect. "Is there a row satisfying the where clause?" – that's the question that the exists predicate is meant to answer.
You need AND between the conditions in WHERE, not commas.


In a database where SQL Server Audit was not previously configured you could try to Read SQL Server Transaction Log Backups to Find When Transactions Occurred with the use of the undocumented function fn_dump_dblog.
For future situations where you need to audit a database I advise you to Create a server audit and database audit specification.

Unfortunately, based on your comment

all I have is whatever is built-in by default

I'd say you have no means to find the info you seek and the reason is that, by default, a new database you create on a SQL Server Express uses the Simple Recovery Model, which doesn't allow taking log backups. Therefore, the function I mentioned will not help.


I am not sure if your case is similar, but you can see similar behavior with Distribution Agent SPIDs on subscriber.

When its not applying any changes it looks something similar.

So in this case its normal behavior.

Hundreds of idle sessions, as you can imagine, would not impact CPU.  But those sessions might be waking up and doing things. The real question is where are those spids coming from? Are all of them from the same application? If so, it might be time to ask the application support guys why it has so many connections hanging around. Maybe they are not closing connections properly.


Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

Your select on dba_tables doesn't take into account:

Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others)
Empty space in the blocks (due to pct_free mainly)
Block headers (initrans influences this size, among others)

I.e. it doesn't take into account the physical storage of the data at all. dba_segments on the other hand only counts physical blocks for the segment, regardless of how much data they contain.

A table with a pct_free set to 50% and never updated will have statistically half-empty blocks for example, and the calculation you did on dba_tables will reveal that when compared to dba_segments.

See the Logical Storage Structures docs to learn more about how data is stored in your database (and the overhead involved).


There is nothing native in SQL Server that is going to track that level of information. Based on what version of SQL Server you are using there is SQL Server Audit (SQL Server 2008 or higher) that can track database level access, but it does require Enterprise Edition.

You could also roll out your own method of logging using Profiler or Extended Events that just capture object access for a given database. As long as you are not trying to capture the query and all it should be pretty low overhead, but it would be based on how active your system is.


Finding number such as 111111,0000,4444, using REPLICATE

columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

E.g.

columnname = '111111'
=>
LEFT(columnname,1) = '1'
LEN(columnname)    = 6

REPLICATE(LEFT(columnname,1),LEN(columnname)) =
REPLICATE(        '1'       ,       6       ) =
'111111'

You take the leftmost character of the column and replicate it to the length of the column.
If the result is equal to the column then you know that all the characters of the column are the same of the leftmost character.


in-order to change innodb_lock_wait_timeout default value you need to edit you my.cnf file and look for innodb_lock_wait_timeout if the parameter exist then you need to edit the current value to 600 and restart the service, otherwise you need to add the parameter innodb_lock_wait_timeout = 600 and restart the service.
Demo:
my.ini content (I am using windows here, but MySQL) configuration is the same

Re-staring MySQL service and check if the parameter value changed


MySQL reads Date columns in Excel as DateTime?

Kindly Note that MySQL stores date in YYYY-MM-DD format by default.

One way is to convert all dates into YYYY-MM-DD format so that it will be compatible with MYSQL.

We can format the date column in excel before exporting it to MYSQL by following the below steps:

Select the column which contains the date
Right click and select format cells
i. Choose date under category(on left-hand side)
ii. Then choose custom under category
iii. Under type insert the format --> YYYY-MM-DD
You will see all the dates in the columns get converted into the format --> YYYY-MM-DD

Then you can import the date into MYSQL safely.

Another way is to keep the column datatype as VARCHAR and use TO_DATE() function to parse the string data into DATE format.

Hope this solves your problem !!


best way to avoid redundant aggregate functions and/or group by columns

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

(Emphasis added.)

So, if foo.id is the PK, this query would be valid:

select
  foo.id,
  foo.baz,
  foo.whatever,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;


PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?

In scalability, part of the problem is that this is pretty heavily use-case dependent.  There is no scaling solution that hits every use case equally. For example, Slony is very helpful for scaling out in some cases, but is a bear in others.  For example, Slony lets you replicate only part of your database.  On the other hand, Slony lets you replicate only part of your database....  Overall, MySQL seems a little easier to scale out of the box in the workloads it supports but PostgreSQL seems to offer more advanced tools which you can take quite a bit further.

As far as which is more scalable in common use cases, now that Postgres-XC has been released, PostgreSQL is the clear winner here.   We have Slony, Bucardo, Streaming Replication, PGPool, etc. on the replication side.  We have Postgres-XC on the scale out clustering side.  We are extremely scalable now.

However, all these come with complexity costs.  These costs appear a bit higher on the Pg side, but the flexibility is there to scale however you need to.

Scaling up right now is also a major PostgreSQL goal.  9.2 makes some important gains in that regard.


In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator.  For instance:

exec msdb.dbo.sp_update_operator
    @name = 'OldOperatorName',
    @new_name = 'NewOperatorName';
go

As for SSMS:  most things you do in SSMS you have to option to script out the changes you are making in the GUI.  So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing.  It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story?  Don't rely on a UI.  If you want to have full control over what's happening then you should be using T-SQL.  There will be no question in your mind if you're writing out the code to do what you want.  No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name.  So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.


Does Lazy Writer spend resources on flushing out pages from the buffer pool?

does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free

Yes it does, this is primary responsibility of Lazy writer. Its job is to make sure there is some free buffer so that new pages can be brought into memory when required.

Lazy writer would periodically scan buffer pool, look for pages which are least recently used, or you can say "cold pages" and flush them to disk. In case there is sufficient memory available Lazy writer would not do anything. In case of memory being required to bring more page into buffer pool Lazy writer would flush old pages, create space and bring new pages. It does not marks pages as clean


Importing CSV file using SQL Server Import and Export Wizard (SQL Server Express)

The method of insert doesn't appear to be the problem, more the data itself

The error you are receiving is because one of the columns you have in your database table is too short (see your column 'wikipedia_link') see what length it is and if you can increase it, note it is quite possible to have more errors on other columns that are similar, judging by the data contained in that field the longest field is 128 characters, so I'd make the field nvarchar(130) as a minimum

If you load the csv in excel you can find out the max length of a column by using {=MAX(LEN(Q:Q))} (NOTE to get the {} array calculation after typing the query press ctrl + shift + enter)

Check all your fields are long enough and try again


Get partial results in Postgres in case of timeout?

There is nothing to do exactly what you want, making a query end normally on the server based on a time limit rather than a row LIMIT.  You could declare a cursor and then FETCH rows in chunks (perhaps chunks of 1), closing the cursor after a certain amount of time has passed as witnessed on the client. Depending on the driver you are using, there may be other options to get similar results, such as PQsetSingleRowMode in libpq.


Workload group "Importance" vs. Resource pool IOPS throttling

Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific.

Correct. Not to delve too deep into this since it isn't configurable - but let's take a quick second and think about that sentence and what you're really talking about here.

How do you know what your IO capacity is? Let me take your information:

  Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS).

Is that 2800 IOPs @ 4k? 8k? 256k? Is that at outstanding IO of 1? 4? 8? 24? 128? Should SQL Server now monitor the entire server for everything IO related? What if it's in a hyper-visor and can't "see" the base infrastructure... now what? Is that 2800 IOPs with 99% IOs within 10 ms? 20 ms? 100 ms?

This isn't as easy as saying "50%" as there are too many variables of what 50% means... Not nit picking, just not as easy of a subject as one may think.

  So far the best idea I have is to remove IOPS throttling and instead use the "Importance" of a workload group to defend the rest of the server's I/O capacity from these queries.

Importance doesn't touch IO, it deal with scheduling. High runs more often than normal which runs more often than low. It doesn't mean it won't eat up your CPU and IO, it just tries to be more fair with scheduling based on your interpretation of the workload (i.e. those marked high are more important). This most likely will not help what you're attempting to solve.

  Or is there a better way to achieve our goals?

Yes :) Scale out the reporting workload to something such as a readable secondary in an availability group, transactional replication subscriber, read-only copy of the database (depending on data freshness required), CDC/CT to another server with SSIS, etc. It'll also isolate your storage subsystems (unless it's on the same SAN/NAS/etc.) from each other.


How to use 'mysql -uroot -pXXXX' on Debain 9.8?

If you do sudo su - to root, are you able to log in without a password? If not, you can always log in by skipping the grant tables. See e.g. step 2 and 3 in this tutorial.

Once inside, you need to change the authentication plugin back to 'mysql_native_password' for the root@localhost user:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');

See documentation on this here.


Because you're taking one key value, and assigning it another key value.

It's 2017, so I'm not going to use sp_lock. I'm going to use sp_WhoIsActive.

If we run it like this: EXEC sp_WhoIsActive @get_locks = 1 we can get lock information.

CREATE TABLE t
(
    id INTEGER NOT NULL PRIMARY KEY
);

INSERT dbo.t ( id )
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY @@DBTS)
FROM (SELECT 1 AS n FROM sys.messages AS m CROSS JOIN sys.messages AS m2) AS x

If we update one key value:

BEGIN TRANSACTION;

UPDATE t
SET    id = 50001
WHERE  id = 5;

ROLLBACK

The lock information looks like this:

<Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
      </Locks>
    </Object>

Two key locks! One key lock to get the key value we're modifying, and one lock to reserve the key value we're modifying it to.

If we modify two values...

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10)

ROLLBACK

We end up with four key locks. This pattern will continue if we do 3 or 4. There will be two key locks for each ID touched.

<Object name="t" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="4" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
  </Locks>

Hitting three values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15)

ROLLBACK

Hitting four values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15, 20)

ROLLBACK

At least up until we modify everything.

BEGIN TRANSACTION;

UPDATE t
SET    id += 1

ROLLBACK

Then the lock type changes!

   <Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" index_name="PK__t__3213E83FE76849A4" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

This is due to the magic of the split, sort, and collapse operations, which you can see in the query plan here.

Hope this helps!


OOM is usually a result of bad my.cnf config and actually your my.cnf overcommits memory big time. Run mysqltuner.pl to get a sense of what parameters needs tweaking. Many of your configuration values are way high and some of them doesn't even make sense.

For example:

1) binlog_cache_size 3Gb? default value is 32768 bytes(!)

According to dev.mysql.org:

  The size of the cache to hold changes to the binary log during a
  transaction. A binary log cache is allocated for each client if the
  server supports any transactional storage engines and if the server
  has the binary log enabled (--log-bin option). If you often use large
  transactions, you can increase this cache size to get better
  performance.

Do you have 3GB big transactions?

2) Per connection buffers:

read_buffer_size = 2M
read_rnd_buffer_size = 16M

Why are you optimizing for random read? These values are way too high even for a very heavily used databases.

You can easily drop these under 1MB. Maybe 256K as a start and increase if necessary.

Same goes for sort_buffer_size and join_buffer_size. 1Mb for the former and 128kb for the latter should be sufficient if your tables are well indexed for the common queries.

Especially with max_connections = 40000 these values can easily run your out of memory.

+) Innodb

innodb_log_buffer_size = 64M

Do you actually need this? You should be able to drop this to a much lower level and like 8MB or 16Mb and monitor the Innodb_log_wait status variable.

innodb_log_files_in_group = 3

You don't get any benefit of having 3. Keep it to 2.


NoSQL and RDBMS are totally different animals.  You should pick the one which suits your data and how you intend to record and access it.

Don't pick your style of database system on the grounds of performance.  Performance can be managed in a range.  If HBase isn't performant at your start up volumes, then maybe you'll just have to work harder to tune it, or throw more money at hardware, or just live with slightly lower performance until your volumes catch up to HBase's sweet spot.


Querying a many to many relationship in access

I didn't really do a lot with MS Access, but you can try something like this, assuming that company.id is primary key and country.id is primary key and the corresponding tables are related to locations through company_id and country_id, respectively:

SELECT company.name, country.name
FROM locations
LEFT JOIN company
ON company.id = locations.company_id
LEFT JOIN country
ON country.id = locations.country_id
GROUP BY company.name
HAVING count(locations.country_id) > 1


Adding SQL Agent output to text file for all agent jobs in one go

The msdb database contains a stored procedure that allows modifications to job steps in a wide variety of ways, including the ability to modify only the output properties.

The following code will output the commands required to modify all job steps that have no "output file" specified, such that job step output will be written into C:\temp\job_name_here.txt.  Output will be overwritten each time the job step runs.

USE msdb;
DECLARE @OutputPath nvarchar(260);
DECLARE @cmd nvarchar(max);
SET @OutputPath = N'C:\temp';
DECLARE @msg nvarchar(1000);
SET @msg = N'
/*
flags:
    0 - overwrite output file
    2 - append to output file
    4 - Write T-SQL output to step history in MSDB
    8 - Write log to table (overwrite existing)
    16 - Write log to table (append)
*/

';
PRINT @msg;
DECLARE cur CURSOR LOCAL FORWARD_ONLY LOCAL
FOR
SELECT N'EXEC msdb.dbo.sp_update_jobstep @job_id = N''{' + CONVERT(varchar(50), (sj.job_id)) + N'}''
    , @step_id = ' + CONVERT(nvarchar(30), sjs.step_id) + '
    , @output_file_name = N''' + @OutputPath + N'\' + sj.name + N'_Step_' + CONVERT(nvarchar(10), sjs.step_id) + N'.txt''
    , @flags = 0;

'
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.output_file_name IS NULL OR sjs.output_file_name = ''
ORDER BY sj.name
    , sjs.step_id;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEN(@cmd)>4000
    BEGIN
        SET @msg = 'Output of the next command is truncated.';
        RAISERROR (@msg, 14, 1);
    END
    PRINT @cmd;
    --EXEC sys.sp_executesql @cmd; --uncomment this line to actually run the command
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

You could run this against multiple servers by creating a local server group in SSMS using the "Registered Servers" functionality, adding the desired servers to that group, then executing a query against the group.


Oracle: Delete data from one table based on starttime and finishtime column of another table

In your second query, your where clause has 1 column trying to compare to TWO columns in your subquery.  That's probably the error you're getting.

Have you tried using CTE?
http://www.dba-oracle.com/t_common_table_expression_cte.htm

However, I think your REAL problem is that you're using a function (SCN_TO_TIMESTAMP) in your where clause, and it's ignoring any indexing on the ora_rowscn column.

Here's the CTE anyway, but I doubt it will be much of a performance improvement.

WITH CTE_TasksToDelete
AS
(select starttime,
        finishtime,
        taskid --Best if you're going to be using multiple taskIDs
 from table2
 where taskid=10502)
DELETE FROM table1
WHERE SCN_TO_TIMESTAMP(ora_rowscn) BETWEEN ( SELECT starttime
                                             FROM CTE_TasksToDelete)
                                   AND (SELECT finishtime
                                        FROM CTE_TasksToDelete);

If anyone else wants to edit this and make it better, feel free.  Just wanted to give a starting point.

Again, I'm fairly certain your issue is that SCN_TO_TIMESTAMP function.  Find a way to make that an actual column in your table1, then index it, and you'll be a happy camper.
Also, batch your deletes friend.


What type of data ingestion does Azure Data Factory use?

According to the following picture found in the documentation, it has a Batch Ingest

You could also use triggers to move data incrementally. But it is not real time. There is frequency limitation.


Capacity planning for SQL Server consolidation

Like Randolph mentioned, disk is the kind of the last thing you need to worry about. If it's clustered as you mentioned, you can easily add storage to accommodate data growth.

The things that are harder to add and cost real money are:

CPUs (Licensing)
Memory (If you're on standard edition, you're limited to 128 GB)

In a consolidation scenario, you need to get a baseline of CPU use (easy perfmon--SQL Server CPU) and memory--harder (you'll want to track to page life expectancy as it relates to total memory use by SQL). Finally, you'll want to tack IOPS--the performance component of storage--if your source environments are on SQL 2014 or higher, there is a counter you can check (disk read/write IO/sec) to get that data. The disk data will simply be cumulative, whilst the CPU and memory data will need to evaluated. In a consolidated environment, you'll probably use a little less CPU and memory.

Finally, for a shared environment like this, you probably want to use Enterprise Edition with Resource Governor so you can prevent the "noisy neighbor" problem.


You are passing the username incorrectly:

The "short" parameter names don't use the = sign:

So it should either be:

psql -e -a -f"C:\Users\utente\Desktop\1Giswater impNode.sql" -dpostgres  -Upostgres

or, when using the long parameter names:

psql -e -a --file="C:\Users\utente\Desktop\1Giswater impNode.sql" --dbname=postgres  --username=postgres


The innodb threads have their place; but they do not help you out in your SELECT query. With MySQL, unfortunately, your SELECT can only use one single core.

This immediately means that if you want to use more cores, you must parallelize your queries. Very often this is possible. Do you have this huge script you're running? Are there independent parts to it? Parallelize them.

But if you cannot parallelize for some reason, you're more limited. Take a look at Shard Query. Either use it or get some ideas from it.


Folks I see going in this direction tend to go with Amazon RDS or Microsoft Azure SQL DB. If you’re interested in reducing DBA costs, you’re also usually interested in reducing sysadmin and hardware costs, too.


Install MariaDB 10 Generic Binaries + TokuDB

The right place to ask this question in by filing a ticket in the MariaDB Jira or on the #mariadb IRC channel on freenode.


SQL Server 2014 SP1 Setup Freezes Removing Temporary Files On Windows 10 x64

You are installing SQL Server 2014 Enterprise Edition on Windows 10 (a client OS), which is not supported. If you were to take this case to Microsoft they would say you are trying an unsupported scenario. Although your installation won't fail as such if you are trying to install Enterprise Edition on a client OS, what is the point in wasting such a costly license on a client OS which has so many limitations, compared with a server OS?

The Log you posted is incomplete. You can use this link to locate all the log files regarding installation, then post it on on shared drive for analysis. Please share the link with us so that we can study the logs.


Galera wih MariaDB master-master to support write enable on disconnect

First, Galera should not be used for more than about 5 nodes.  This is because each node talks to every other node on each write.  This means 100 nodes would have 9900 'connections' among them.  And each write involves 99 roundtrip messages.

You have solved the main 'key' problem by ensuring uniqueness.  But there are other possible ways of having conflicts.

Furthermore, Galera's approach to re-acquiring a node will not work for you.  It will assume the node is corrupt.  It will not assume you have inserted valid new records.

Your dual-master setup does not affect what will eventually happen on the Galera nodes.

Are you more vulnerable to server outage?  Or network outage?


Does a hidden member count toward and operate as part of a write_concern majority in MongoDB 2.6?

Indeed hidden members take part in voting. If your write concern is equals to visible nodes and there is a hidden member who can vote, it shouldn't be an issue. You have to plan write concern carefully.

Please see https://docs.mongodb.org/manual/core/replica-set-hidden-member/


Best practice for database where the values can't change

I suggest you to create a new table, InspectionHistory or something like this, with the following structure:

InspectionID|PlaceID|DateStart|DateEnd|Status

Here you would have foreign keys with your Inspection table and Place table and the information, you want to store. With such structure you can store several record about your Inspection places.


sp_AllNightLog - running on an existing SQL server

Great job on troubleshooting, and yes, you are correct. Ola Hallengren's backup scripts need a change as described in this Github issue for his scripts:

https://github.com/olahallengren/sql-server-maintenance-solution/issues/12

Ola hasn't accepted the pull request yet (he's fairly new to Github), so if you want to use my fixed version with the new parameters I describe in that issue, here's my repo:

https://github.com/BrentOzarULTD/sql-server-maintenance-solution

If all that sounds too scary - and I wouldn't blame you - then you'll need to seed the restores yourself manually for each new database.


A PL/SQL table is NOT the same as a temporary table in Oracle.

An Oracle temporary table is created with they syntax

CREATE TEMPORARY TABLE xxx ...

and is a DBMS table the contents of which are transient, vanishing either at the end of the transaction or the end of the session. It can be used in any SQL just like a normal table.

A PL/SQL table which you have defined above cannot be used in SQL but must be referenced programmatically. See http://docs.oracle.com/cd/A57673_01/DOC/server/doc/PLS23/ch4.htm for an overview on how to do this.


This is due to the account used for Linked Servers not being added to the security of the database trying to be accessed.

On the databases that are shown in the Linked Servers container, you will also find the account (back on the original db server) added to the Security section with Read access. Add that account to the newly created db (catalog in this case) on the original server and then refresh the linked servers on the target db server. Your catalog will now show.


What is the advantages and disadvantages of maintaining one ORACLE_HOME?

First of all if you want to have multiple oracle version (11g, 12c etc.) you must have multiple homes.

The advantage of one oracle home is less space consumed. Less work with patching one home.

But if you have more instances under this one home, you have to patch all databases (post patch sql) which is more work :-).

So it depends on how many versions and how many instances do you have on the server.


Restore back up strategy for history tables

SQL Server supports piecemeal restores.  From that link:

  Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

So you should be OK with the FULL recovery model.

I'd suggest you partition your tables.  The log tables especially should be amenable to partitioning by write date.  Direct the older partions to a filegroup on the slower, cheaper storage.  Then you can restore the important (i.e. recent) portions first and the ancient logs later.


As someone who spent 4 years of his life dealing with converting CLOB type data from legacy systems into Oracle, I feel your pain.

I highly recommend using an Oracle built function, such as REPLACE to go through and scrub all of the garbage characters from CLOB type fields. Additionally, remember that most systems consider a "return" to be a new line and carriage return in unison, so you might have to scrub ascii characters below 33. IIRC the new line/carriage return combo was char(10)char(13). You can easily google a list of of the "invisible" ascii characters, and hunt down the ones you want removed.

HTH.


Password reset for database for VCenter Install

newDBA,

Welcome to dba.stackexchange.com.  What you are looking for is the SQL Server SA password which was probably installed with VCenter, or it was installed prior and VCenter was configured to use that SQL Server.

Option 1:
If you have admin rights to the server, you can try logging in 'as a windows user' and seeing if the admin was configured to have SA rights.  If you can get in, you can change the SA password.

Option 2:
If you do not have access to SQL via Windows Logins, you can still use what some people consider an exploit, and access SQL Server via the NT Authority System userCheck out some blogs on it here, it's pretty easy to do.  This is the command as copied out of the blog:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Recover access to a SQL Server instance.  Written by our very own Aaron Bertrand.

Option 3 (The nuclear option):
If you are sure there is nothing you need on this SQL Server and it was only installed for VCenter you could always uninstall and reinstall.  This is a last resort however, I don't see any reason why you would have to entertain this idea for your current issue.


Best way to transfer millions of rows from one database to other

First rename the schemas in the current databases to have the names you want to end up with:

alter schema public rename to schema_a;

And the same for other databases.  Whether you need to change your apps, SQL functions, etc. depends on what kind of coding standards you used in the first place.  This can probably be done with the system online, if your apps can cope and if you juggle search_path appropriately.

Then lock people out of each database in turn, and use pg_dump to dump out the schema and data, and load it to the successor database.

pg_dump database_a -n schema_a | psql -1 database_common

If you have extensions in special schemas, elaborate systems of roles and grants, and stuff like that, then you might need to do some work to get that to work.  There is no substitute for having a test/QA database and using it for testing and practicing.


Is it possible to have extra tables in a Slave with MySQL Replication

I think I got a rough idea how you setup replication. I think you have taken mysqldump --all-databases ... something along those lines.

Now if you want extra tables inside one of those database that you exported by mysqldump and those that belong to the master replication will break as you mentioned aswell.

Now to create more tables you need to create a new database with some name and then create tables in there. This will not break the replication.

I do not know if this helps....

Masood


Likely, the issue is caused by the very long IN lists. Instead of using the IN list, create a temporary table with the values you want to filter on. Then rewrite the query as a join:

CREATE TABLE #FilterOutputArea (...)
INSERT #FilterOutputArea VALUES ('E00139237')
... etc...

CREATE TABLE #FilterColumnID ()
INSERT #ColumnID VALUES (298)
... etc...

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[OutputArea] AS [OutputArea],
[Extent1].[ColumnId] AS [ColumnId],
[Extent1].[Value] AS [Value]
FROM [dbo].[ColumnValues] AS [Extent1]
JOIN #FilterOutputArea ON ...
JOIN #ColumnID ON ...