Three databases : one instance or three?

The only benefit to using three separate instances would be if you plan to take one instance offline for some reason, e.g. to patch SQL Server one instance at a time, without affecting the other two databases. Of course this doesn't buy you isolation from other types of events, such as patching Windows, never mind that all your eggs are in one VM's basket, too - so all three databases will be vulnerable to underlying hardware issues, resource limitations, etc. whether they are on one instance or three. And of course all three instances will be sharing the same resources within the VM; there is significant overhead involved with running each instance regardless of the databases or volume.

Also, using separate instances - and especially separate VMs - seems to fly in the face of your overall mission (consolidation).

So, I strongly recommend you create a single instance, and put all three databases on one instance.


Citing the reference:

  VACUUM FULL rewrites the entire contents of the table into a new disk
  file with no extra space, allowing unused space to be returned to the
  operating system

Without FULL, the database files are not even truncated, so data may still be in the files. With FULL, deleted data should be gone from the files (since they are recreated). However, data may still be in deleted parts of the underlying file system of course.

In any case, if it is important to you that data is destroyed, you must probably erase or destroy the disk.


How to reset a postgres database that has used all of the available disk space?

First I'd have a look around /var/log to see if there are any files I could get rid of. If that's the case, you could then start your service and remove the appropriate databases.

If you do not care of your data, you could drop your cluster with the pg_dropcluster command. Look at the man file for the appropriate options.

And by the way, 8.4 support has ended a while ago (https://www.postgresql.org/support/versioning/) and you shoudl really consider upgrading to a supported version.


How can I eliminate this costly index seek operation from my query?

In addition to the T-SQL techniques discussed in Aaron Bertrands article, "Best approaches for grouped running totals", you might want to look at a SQLCLR procedure (if you are running less than SQL Server 2012).

The main advantages are that a SQLCLR procedure requires only a single scan of the source records, and benefits from the increased execution speed of compiled code. The sample below is taken from Aaron's post:

DDL

CREATE TABLE dbo.SpeedingTickets
(
  IncidentID    INT IDENTITY(1,1) PRIMARY KEY,
  LicenseNumber INT          NOT NULL,
  IncidentDate  DATE         NOT NULL,
  TicketAmount  DECIMAL(7,2) NOT NULL
);

CREATE UNIQUE INDEX x
ON dbo.SpeedingTickets(LicenseNumber, IncidentDate)
INCLUDE(TicketAmount);

Sample data

;WITH TicketAmounts(ID,Value) AS
(
  -- 10 arbitrary ticket amounts
  SELECT i,p FROM
  (
    VALUES(1,32.75),(2,75), (3,109),(4,175),(5,295),
          (6,68.50),(7,125),(8,145),(9,199),(10,250)
  ) AS v(i,p)
),
LicenseNumbers(LicenseNumber,[newid]) AS
(
  -- 1000 random license numbers
  SELECT TOP (1000) 7000000 + number, n = NEWID()
    FROM [master].dbo.spt_values
    WHERE number BETWEEN 1 AND 999999
    ORDER BY n
),
JanuaryDates([day]) AS
(
  -- every day in January 2014
  SELECT TOP (31) DATEADD(DAY, number, '20140101')
    FROM [master].dbo.spt_values
    WHERE [type] = N'P'
    ORDER BY number
),
Tickets(LicenseNumber,[day],s) AS
(
  -- match *some* licenses to days they got tickets
  SELECT DISTINCT l.LicenseNumber, d.[day], s = RTRIM(l.LicenseNumber)
    FROM LicenseNumbers AS l CROSS JOIN JanuaryDates AS d
    WHERE CHECKSUM(NEWID()) % 100 = l.LicenseNumber % 100
    AND (RTRIM(l.LicenseNumber) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
    OR (RTRIM(l.LicenseNumber+1) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
)
INSERT dbo.SpeedingTickets(LicenseNumber,IncidentDate,TicketAmount)
SELECT t.LicenseNumber, t.[day], ta.Value
  FROM Tickets AS t
  INNER JOIN TicketAmounts AS ta
  ON ta.ID = CONVERT(INT,RIGHT(t.s,1))-CONVERT(INT,LEFT(RIGHT(t.s,2),1))
  ORDER BY t.[day], t.LicenseNumber;

SQL CLR procedure DDL

CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM [encoded data, 11266 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE [dbo].[TicketRunningTotals]
AS EXTERNAL NAME [Demo].[StoredProcedures].[TicketRunningTotals];

Usage

EXECUTE dbo.TicketRunningTotals;

Output

Execution plan

SQL CLR source code

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void TicketRunningTotals()
    {
        using (var conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText =
                    @"
                    SELECT
                        ST.LicenseNumber,
                        ST.IncidentDate,
                        ST.TicketAmount
                    FROM dbo.SpeedingTickets AS ST
                    ORDER BY
                        ST.LicenseNumber,
                        ST.IncidentDate
                    ";

                var columns = new SqlMetaData[4]
                {
                    new SqlMetaData("LicenseNumber", SqlDbType.Int),
                    new SqlMetaData("IncidentDate", SqlDbType.Date),
                    new SqlMetaData("TicketAmount", SqlDbType.Decimal, 7, 2),
                    new SqlMetaData("RunningTotal", SqlDbType.Decimal, 9, 2)
                };

                int licenseNumber, lastLicenseNumber;
                var ticketAmount = 0m;
                var runningTotal = 0m;

                var pipe = SqlContext.Pipe;
                var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
                var record = new SqlDataRecord(columns);

                pipe.SendResultsStart(record);

                if (reader.Read())
                {
                    lastLicenseNumber = reader.GetInt32(0);

                    do
                    {
                        licenseNumber = reader.GetInt32(0);
                        ticketAmount = reader.GetDecimal(2);

                        if (licenseNumber != lastLicenseNumber)
                        {
                            runningTotal = 0m;
                            lastLicenseNumber = licenseNumber;
                        }

                        record.SetInt32(0, licenseNumber);
                        record.SetDateTime(1, reader.GetDateTime(1));
                        record.SetDecimal(2, ticketAmount);
                        record.SetDecimal(3, runningTotal += ticketAmount);

                        pipe.SendResultsRow(record);
                    }
                    while (reader.Read());
                }

                pipe.SendResultsEnd();

            }
        }
    }
}


If a database is in memory does it still need indexing?

Yes, an index will help.  Without an index all the RDBMS can do is start at the beginning of the data and work its way toward the end, stopping when it finds what it's looking for.  This is a O(N) operation.  With a B-Tree index in place it reduces to O(log(N)).  This is true whether the data is held on disk or in memory.


How do you migrate a database from SQL Server 2008 to MySQL?

You can use tools which are very efficient in migrating the data from one type of the database to other.

There are many types of tools some of them are freely available.I have used Talend which is very efficient in doing this.

It is also freely available.


This would be a file created by using a DBCC.

Summarizing the pertinent information in the link that Shekar Kola provided:

  The behavior of DBCC CHECKDB commands beginning with SQL Server 2014

  DBCC CHECKDB creates an internal snapshot database.
  The internal snapshot database is created by using physical database    files.

  Consider a database that has the three files E:\Data\my_DB.mdf, E:\Data\my_DB.ndf, and E:\Data\my_DB.ldf.
  Consider that the internal snapshot database is created with database ID 10.
  The internal snapshot database will be created by using the files E:\Data\my_DB.mdf_MSSQL_DBCC10 and E:\Data\my_DB.ndf_MSSQL_DBCC10.
  Notice that new files are created in the same folder with the naming convention
  _MSSQL_DBCC.

  The new files are marked as sparse at the file system level.

  The "Size on Disk" used by the new files will increase based on how much data is updated in the source database during the DBCC
  CHECKDB command.
  The "Size" of the new files will be the same file as the .mdf or .ndf file.

  The new files are deleted at the end of DBCC CHECKDB processing.

  These additional files that are created by DBCC CHECKDB have the "Delete on Close" attributes set.
  If the operating system encounters an unexpected shutdown while the DBCC CHECKDB command is in progress, then these files will not be
  cleaned up. They will accumulate space, and potentially will prevent
  future DBCC CHECKDB executions from completing correctly. In that
  case, you can delete these new files after you confirm that there is
  no DBCC CHECKDB command currently being executed.

  The new files are visible by using ordinary file utilities such as Windows Explorer.


Recovering MySQL 5.7 tables in MariaDB 10

Before doing anything else I would recommend that you backup the old mysql data directory and the configuration file on /etc. So should something go wrong you don't loose your data.

Then I would check what specific MariaDB version of you have installed. As per MariaDB compatibility chart, you need MariaDB 10.2 to migrate from MySQL 5.7. Once you are sure you have the right version installed, you should refer to the migration doc:

https://mariadb.com/kb/en/library/migrating-to-mariadb/

Alternatively you could uninstall MariaDB and install MySQL again, just check that you are installing the latest 5.7 version available. For this you can download a deb package from the mysql downloads and install it manually or set up the mysql repository: https://dev.mysql.com/downloads/repo/apt/


[Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12

errno 12 is Out of memory - So probably you did not set any value for the innodb_buffer_pool_size and the default is used. Depending on your previous version the default might change on upgrade. Or you did have it configured but the upgrade rewrote your config? Or your new Ubuntu just needs more memory for itself.

Change the value, add more RAM, find some memory hog which might eat your RAM or enable swap (might slow everything down when it hits the swap).


As your dataset rises, ensure that your active dataset is still mostly in the innodb buffer pool.

As you increase number of tables make sure the table cache can contain all the active tables.

Overall you don't have a large database or a runtime problem but your skills in monitoring have to be in place.

Yes, you have a backup problem, in general look at xtrabackup, replication slave (that you could take offline and copy the datadir), binary logs for PITR (point in time recovery). For a logical backup look at mydumper running on a replication slave.

Tradeoffs are normally infrastructure required vs restore time. Work out your acceptable restore time, a couple of failure scenarios, and run the backup and restore procedure to ensure you are ready.


What is running on my server every night?

From your profiler trace you should be able to see the Login and Host that the queries are coming from. I would then speak to your operations and/or development team to identify the application and function of the queries (or they should at least be able to point you in the direction of where you need to go/who you need to speak to).

Once you have that information you can see if the queries can be tuned in order to reduce the pressure on your disks.


How to create tablespace if it does not exist

The point here is that Postgres functions are almost but not quite like true stored procedures. Postgres functions (unlike stored procedures) run in the context of an outer transaction. Therefore, you cannot execute commands that cannot run in a transaction block, like VACUUM, CREATE DATABASE, ... or CREATE TABLESPACE. The manual is clear about that:

  CREATE TABLESPACE cannot be executed inside a transaction block.

You have to run these commands as singular SQL commands. Postgres currently (up to version 9.6) also doesn't have autonomous transactions, which could be a workaround.

So the only workaround to include the command in a function or transaction is faking an autonomous transaction with dblink, just like you found already.

There are some other minor problems in your code. I suggest:

CREATE OR REPLACE FUNCTION make_tablespace(tablespace text,
                                           directory text,
                                           owner text)
  RETURNS void AS
$func$
BEGIN
   IF tablespace <> '' THEN  -- catches '' *and* NULL
      -- do nothing
   ELSE
      RAISE EXCEPTION 'No tablespace.';
   END IF;

   IF EXISTS (SELECT 1 FROM pg_tablespace WHERE spcname = tablespace) THEN
      RAISE NOTICE 'Tablespace % already exists.', tablespace;
      EXIT;
   END IF;

   IF directory <> '' THEN
   ELSE
      RAISE EXCEPTION 'No directory.';
   END IF;

   IF owner <> '' THEN
   ELSE
      RAISE EXCEPTION 'No owner.';
   END IF;

   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec(format('CREATE TABLESPACE %I OWNER %I LOCATION %L', tablespace, owner, directory));
   RAISE NOTICE 'Tablespace % created.', tablespace;
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

Major points:

Do not use the data type character. You want text.

Any downsides of using data type “text” for storing strings?

While checking for '', also check for NULL.
Use format() for short, clean dynamic SQL code and properly escape user input to avoid sneaky syntax errors and SQL injection.

Related answer with code examples and more explanation:

Persistent inserts in a UDF even if the function aborts
Does Postgres support nested or autonomous transactions?


CSSCAN for the character set change, database version 11.2.0.3.0

You can just edit the file $ORACLE_HOME/rdbms/admin/csminst.sql and change identified by csmig from csmig to a more complex password; you may have to enclose the password with double quotes if you have characters other than alphanumeric, underscore (_), dollar sign ($) or pound/hash sign (#).

Example:

create user csmig identified by "W#$!?X8Y" password expire account lock;


Complex query performance in MySQL InnoDB versus MyISAM

In your case, your problem is RAM. Why?
In my old post What are the main differences between InnoDB and MyISAM?, I discuss how each Storage Engine does caching.

InnoDB has a Buffer Pool that cache data and index pages
MyISAM does no dedicated caching of data, only indexes. Data get cached in OS RAM.

Here is a picture from Vadim Tkechenko that shows what InnoDB looks like:

InnoDB has more housecleaning to do against the buffer pool as new data/index pages come in and old data/index pages get purged. MyISAM does not have any housecleaning of data to do.
You will need a Cloud Server with more RAM and a larger InnoDB Buffer Pool.
UPDATE 2015-03-20 18:00 EDT
If you can give more memory to the Buffer Pool without getting a bigger Cloud Server, that's fine.
If your Cloud Server is a dedicated DB Server, you should do 75% of RAM. So, that's 3G for innodb_buffer_pool_size. If your Cloud Server is a LAMP stack, perhaps 60% of RAM, which would be 2457M.
If you have lots of data, then I would suggest a Cloud Server with more RAM.
UPDATE 2015-03-20 18:06 EDT
I also noted that you said

There are some users that have a lot of detections (tens of thousands) per month.

If this means the table has lots on INSERTS, you should run this once a week
ANALYZE TABLE raw_detection;

Then, try the query again. The index statistics might be out of sync with the amount of data currently in the table.


According to given sample data there is "Type" which is common in both table

Select B.PERS, T.DESC
from BaseTable B
right outer Join Testtable T
on ( T.type is null or B.Type=T.type or B.Type is null)
and T.Grp = 'G1'
where T.DESC<>''
order by B.PERS asc


Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

Not a proper slave because you are not using mysql replication but anyway it's very common to have an slave or more than one to reduce the load in the master server.
Make really sure that its only used for read queries.


This article gave me the hint:

  It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user.

My user myuser was a member of the Windows Administrators group and
there was an SQL Server login defined for the Administrators group.

After removing the user from this group, everything works fine.

I've read this documentation before, but skipped the crucial paragraph.


Statment running significantly slower than component parts and causes high CPU usage

You should check your execution plan.

It can be that cardinality estimation for the last Anti Semi Join is wrong:
you join 247.769 rows with 237.660 rows and there should be Hash Join but if it underestimated cardinality (AdminWIP_DS.dbo.fnTblMembers()) it used NL so you've got your 20 minutes.

Another thing to consider here is to eliminate right join:

You concatenate 2 strings and if one of them is null you'll get null as the result.


Any benefit to having multiple filtered indexes?

To my eyes, all 97 indexes could be dropped and replaced with one
  index on just StateCode (since ActivityID is the primary key and is
  already included as a hidden column). My question is: Is there any
  reason why I would want to keep multiple filtered indexes?

Like Brent said in the comments, this will be hard to give a good anser without many more details.

  I think it's tough to get a good answer here without knowing a lot
  more specifics, like the queries involved that are using the indexes.
  That's probably where I'd start with the research - start looking at
  the index usage statistics on each filtered index, see if there are
  any differences between them, see if maybe there are some
  ActivityTypeCode values that aren't included in the filters, etc

And as Jonathan Fite said, if the queries are using the filtered indexes, they could be beneficial.

  If your queries are actually using the filtered indexes and then
  especially if the index is a covering one for the query, then they
  could be beneficial. But I'm guessing (given the number of them) that
  they were added because someone had read about them and thought it
  would be a good idea (tm). See here
  for guidance on how to tell if the indexes are being used in queries.

Testing

This answer will simply be some tests and considerations in the hope of making your decision easier. There could be reasons for having all these filtered indexes depending on your workload, or they could be hoarding space and resources without having benefits. My amount of data will be smaller than yours, testing on SQL Server 2017. As always YMMV.

Trying to recreate parts of your setup

  I have a SQL Server database and stumbled across a table with a
  uniqueidentifier primary key named ActivityId and columns StateCode
  (int) and ActivityTypeCode (int) along with many others. The tables
  has 13M rows and there are 94 unique values for ActivityTypeCode with
  row counts ranging from 1 to 4M

CREATE TABLE dbo.Activities(ActivityId UNIQUEIDENTIFIER PRIMARY KEY NOT NULL  DEFAULT NEWSEQUENTIALID() , StateCode int, ActivityTypeCode int,val1 int, val2 int);

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT 1,1,1,1;
-- 1 value

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT TOP(4000000) 2,2,ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn1,ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn2

FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
--4m values

INSERT INTO dbo.Activities(StateCode,ActivityTypeCode,val1,val2)
SELECT TOP(9000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) / 1000 % 300 as rn1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) / 1000 % 93 as rn2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn3,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rn4

FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CROSS APPLY master..spt_values spt3;
-- 9M other values, ActivityTypeCode from 0 - 93

  I have 97 different filtered indexes on (StateCode, ActivityId) where
  the only difference is the where clause on
  (ActivityTypeCode=(_some_value_)).

I went for 94 indexes, for the 94 distinct values in ActivityTypeCode

SELECT 'CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_'+CAST((rn-1) as varchar(2))
        + ' ON dbo.Activities(StateCode,ActivityId) INCLUDE(ActivityTypeCode)'
        + ' WHERE ActivityTypeCode = '+CAST((rn-1) as varchar(2)) +';'
FROM
(
SELECT TOP(94) ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) as rn
FROM master..spt_values
) as a

The space used

EXEC sp_spaceused 'dbo.Activities'

name        rows                    reserved    data        index_size  unused
Activities  13000001                994736 KB   527920 KB   437488 KB   29328 KB

Filtered index testing

In your question you did not specify if ActivityTypeCode is included in the filtered indexes. If they are not included, only exact filter matches will be used. More on that here.

Parameterized queries without OPTION RECOMPILE

When using parameterized queries without OPTION(RECOMPILE), the parameter will not be known to sql server at runtime.

This means that you will not be able to use your filtered indexes. We would not be able to reuse the plan with different parameters if we were able to use filtered indexes.

--If your queries are parameterized
EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2',N'@P1 INT, @P2 int',@P1=1,@P2=2

This is also true for stored procedures like the following:

CREATE PROCEDURE dbo.bla(@P1 int, @P2 int)
AS
BEGIN
SELECT StateCode,ActivityId
FROM dbo.Activities
WHERE ActivityTypeCode = @P1 and StateCode = @P2

END
EXEC dbo.bla @P1=1, @P2=2;

Parameterized queries with OPTION RECOMPILE

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2
                     OPTION(RECOMPILE)',N'@P1 INT, @P2 int',@P1=1,@P2=2

As stated earlier, OPTION(RECOMPILE) enables us to use filtered indexes when creating the plan.

You could add the same OPTION(RECOMPILE) to the procedure talked about earlier.

You can also resort to dynamic SQL to pass parts of the filtering as a literal and part of the filtering as a parameterized query. More on that here.

Estimates

The one issue with the plan that uses the filtered index is that the query estimates are way higher than the actuals:

You could resolve these estimates by creating / changing the filtered index so that it has ActivityTypeCode as one of the key columns instead of ActivityId.

CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_1_B
ON dbo.Activities(StateCode,ActivityTypeCode)
INCLUDE(ActivityId)
WHERE ActivityTypeCode = 1;

This is only true when also filtering on StateCode.

I was not able to create custom statistics or get sql server to use custom statistics to fix this. There might be a way to do this without having to change the index

Filtered index < - > regular index(es)

  To my eyes, all 97 indexes could be dropped and replaced with one
  index on just StateCode (since ActivityID is the primary key and is
  already included as a hidden column). My question is: Is there any
  reason why I would want to keep multiple filtered indexes?

Assumptions

Lets assume that the queries can leverage the filtered indexes and that the ActivityTypeCode is included in the index.

The indexes

Proposed index by OP

CREATE INDEX IX_StateCode
ON dbo.Activities(StateCode);

This index seems strange to me, if you are only filtering on StateCode, why would the filtered indexes on ActivityTypeCode even exist?

Which is why we will focus on two other indexes in this example:

CREATE INDEX IX_StateCode_ActivityTypeCode
ON dbo.Activities(StateCode,ActivityTypeCode);

CREATE INDEX IX_ActivityTypeCode_StateCode
ON dbo.Activities(ActivityTypeCode,StateCode);

The space used

EXEC sp_spaceused 'dbo.Activities'

name        rows                    reserved    data        index_size  unused
Activities  13000001                1819584 KB  527936 KB   1260992 KB  30656 KB

Testing

Filtering on StateCode & ActivityTypeCode

Running the query that used the filtered index earlier:

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1 and StateCode = @P2
                     OPTION(RECOMPILE)',N'@P1 INT, @P2 int',@P1=1,@P2=2

The new index is used and the estimates are correct.

We saw earlier that the estimated for the filtered index were ~4M rows for these specific parameters. This could have been circumvented by creating a new index

Filtering only on ActivityTypeCode

When only filtering on ActivityTypeCode it starts to get interesting

EXEC SP_EXECUTESQL N'SELECT StateCode,ActivityId
                     FROM dbo.Activities
                     WHERE ActivityTypeCode = @P1
                     OPTION(RECOMPILE)',N'@P1 INT',@P1=1

The non filtered index is used

While in theory, the entire filtered index could be scanned without having to apply any filtering:

And both estimated rows are correct.

Even when filtering on the parameter that should return +4M rows @P1=2 the non filtered index + seek is chosen.

This is due to the estimated rows and the index seek <-> scan tipping point.

When adding 4M extra rows, the scan is chosen.

If it comes down to this, I would be fine with dropping the filtered indexes and using the regular indexes specified above. But again, this is based on one query on own test data. More details would be needed to get an answer closer aligned with your dataset & workload.

What does the testing tell us?

The testing results only gives us information about my own tests and my own queries.

Some takeaways are that:

the size of the filtered indexes is not going to be higher than
regular indexes.
Managing these will be harder, what if new values are added that have no filtered index
There are  cases when you cannot use filtered indexes, such as
parameterization
If you are using literals / OPTION(RECOMPILE) you will be able to
use filtered indexes and might get better query plans at the cost of
recompilations
You should at include the column in the filtered index & watch out
for wrong estimates

The rest is up to you to figure out.


Postgres COPY, is it possible to load multiple files into the same table?

You have two questions here,

Is it possible to load multiple files into the same table?
On the benefit of it, with concerns about time "in parallel against the same table" .. "Despite dropping indices and creating them later, it still takes a considerable amount of time."

Yes you can load up multiple files. Functionally, it doesn't matter how many times you run the COPY command -- it'll work more than once.

Normally, there isn't much an advantage to split up the copy command if the target is the same table, or even on the same tablespace. One background-writer process can easily saturate a disk. The only time this would matter is if you had something that was computationally intensive. Moreover, there are some reasons to not break up copys. For instance, if you create and load a table in the same transaction you have less WAL,

  COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.

If you need faster writes, check out the Non-Durable Settings


Why does MySQL produce so many temporary MYD files?

There are some options that can cause temp tables to materialize as MyISAM tables or can be configured to delay it. Keep in mind that for disk-based temp tables, there are no .frm files, but only .MYD and .MYI files (of course. the .MYI file is never used since it is impossible index an internal temp table).

Here are the options:

Temp Table Variables That Should Be the Same Size

tmp_table_size
max_heap_table_size

sort_buffer_size
join_buffer_size

You should also consider the MySQL Documentation on Internal Temp Table Usage

The situations where in-memory temp tables are made are

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
DISTINCT combined with ORDER BY may require a temporary table.
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

When an in-memory temp table exceeded the minimum of (tmp_table_size or max_heap_table_size), mysqld does the following:

Suspends the query
Copies the in-memory table's contents into a MyISAM temp table
Discards the in-memory table
Continues the query, sending the temp data into the MyISAM temp table

The situations where in-memory temp tables are bypassed in favor of disk are

Presence of a BLOB or TEXT column in the table
Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

Some due diligence is required to reduce temp table creation on disk

Setting join_buffer_size bigger
Setting sort_buffer_size bigger
Setting tmp_table_size and max_heap_table_size bigger
Tuning queries to minimize or even prevent temp tables
Creating indexes to create presorted view of data from individual tables
Installing additional RAM to accommodate large in-memory temp tables

If after such due diligence, there are still temp tables being formed on Disk, here is one desperate move: Mapping disk-based temp table creation to memory.

Here is a quick-and-dirty way to set up a 16GB RAM Disk using tmpdir

STEP01) Create RAM Disk Folder

mkdir /var/mysql_tmpfs

STEP02) Add this to my.cnf

[mysqld]
tmpdir=/var/mysql_tmpfs

STEP03) Add this to /etc/fstab

echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab

STEP04) Reload /etc/fstab

mount -a

STEP05) service mysql restart

After this, all temp table that become MyISAM are written to the RAM Disk. This should speed disk-based temp table creation.

Give it a Try !!!


What would cause the speed difference between servers?

Sounds like your statistics are out of date.  Run UPDATE STATISTICS on the tables on the server running slow and see if that fixes the problem.


De-duplication method for fields that are identical except for Unique ID

It seems to me that this should work:

INSERT INTO tblDupScanLines (ScanTime, ScannerNo, ScanDate, EventName, PID, ID)
SELECT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate,
    qryDupScanLines.EventName, qryDupScanLines.PID, MIN(qryDupScanLines.ID) AS minID
FROM qryDupScanLines
GROUP BY qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate,
    qryDupScanLines.EventName, qryDupScanLines.PID


New SQL Server 2016 install -- confused logging on via the command line

If you're connecting to a local instance with Windows authentication you specify -E (or nothing, it's the default). Specifying -U will attempt to login with a SQL login.

Here's an example where I connect to a local named instance using Windows auth and run a simple command.


Temporal Table that doesn't use system time

Temporal tables are system-versioned1, so the only way to "manually set" the timestamp of a given history row is to alter the OS time at the moment the row is modified which... you probably don't want to do.

If you want to "manually set" time bounds on a history table so that it supports temporal query syntax, you can do so by manually applying a temporal table on top of your existing data. This is a bit tricky and requires the underlying data to conform to the temporal history versioning rules. Google is peppered with various blog posts on this from back when Temporal Tables were introduced; since I haven't played with that exact use case in a while now I'll arbitrarily link off to this one example that looks promising.

Quickie Demo

Remember that a system-versioned temporal table is actually two tables - a "now" table and a "history" table squished together...

create table dbo.b_now (
     i int not null primary key
    ,info varchar(10)
    ,start_dt datetime2 generated always as row start
    ,end_dt   datetime2 generated always as row end
    ,period for system_time (start_dt, end_dt)
) with (system_versioning = on (history_table = dbo.b_history));
go

insert b_now (i,info)
values
 (1,'AAA')
,(2,'BBB');
go
update b_now set info = 'XXX' where i = 1
go
select * from b_history
select * from b_now
go

Note that while b_now appears to be the "base table" and supports temporal syntax - it is just a separate object. Removing the system versioning binding between the objects reinforces this.

Using this information to "manually version"

Keeping this in mind - and remembering that we can switch SYSTEM_VERSIONING from ON to OFF and back ON again, we can tell SQL Server to arbitrarily apply (and arbitrarily disregard) system versioning rules to an arbitrary pair of objects so long as...

the schemas match
the data conforms exactly as it would if it had been system versioned

  But I want example code!

Yea, I thought you might - so try this...

drop table if exists a_now ,a_history;
go
create table a_now (
     i    int not null primary key
    ,info varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null
        -- you'll want this CHECK later...
        check (end_dt = convert(datetime2,'9999-12-31 23:59:59.9999999'))
);
go
create table a_history (
     i        int not null
    ,info     varchar(10)
    ,start_dt datetime2 not null
    ,end_dt   datetime2 not null
);
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values
 (1,'XXX','2017-01-01',@end_of_time)
,(2,'BBB','2017-01-01',@end_of_time)

insert a_history values
 (1,'AAA','2016-01-01','2017-01-01');
go
select * from a_now
select * from a_history

...Looks a lot like b_now and b_history, don't it? Too bad it's not a proper system table...

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

Wait wut...? You can even plop a view on top of the "now" table to hide your abuse of the naming conventions.

create or alter view a
as
select * from a_now
go

select *
from a
for system_time as of '2016-06-01'

  Okay yea... but how do I add new rows Mr. Smarty Pants?

Very carefully... that's how.

alter table a_now
    set (system_versioning = off);
alter table a_now
    drop period for system_time;
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values
 (3,'CCC','2018-01-01',@end_of_time);

update a_now set
     start_dt = '2018-01-01'
    ,info = 'YYYY'
where i = 1;

insert a_history
values
  (1,'XXX','2017-01-01','2018-01-01')

alter table a_now
    add period for system_time (start_dt, end_dt);
go
alter table a_now
    set (system_versioning = on (history_table = dbo.a_history));
go

select * from a for system_time as of '2017-06-01'

I leave it to the reader (you) to wrap up the INSERT/UPDATE/DELETE logic and relevant DROP/re-SET system versioning into a re-useable module.

1. N.B. this is hinted at with the syntax the period for SYSTEM_time in the required DDL for creating a temporal table


Create event trigger as non-superuser in Postgres

I was just looking into this topic, found this question, and another one with the answer. I figured I'd link back to the original answer, and the RDS docs.

The short answer is:

Yes you can, using the RDS master account.
You have to delete the event triggers before upgrading between major versions.

Details:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.EventTriggers


why does it shows truncated incorrect integer values when using char(salary,'$99,999)

You are receiving a warning as '$99,999' is not an integer. The CHAR() function expects integer(s) as input.

As suggested by McNets: it seems you are trying to use the CHAR() function for something it is not intended to do. The CHAR() function accepts integers as input and will interpret each integer as a character code, returning corresponding, concatenated character(s) as a string.

The function you seem to be looking for is the FORMAT() function, which will format a numerical value and returns a string. This will however not prepend a currency symbol, so that should then be added via the CONCAT() function.

As suggested by Kumar: the following will produce the result (I expect) you wish to achieve:

SELECT
    CONCAT(e.first_name,"  ",e.last_name) AS 'employee name',
    CONCAT('$', FORMAT(s.salary*15/100, 2)) AS 'No.Of Dollors'
FROM
    employees e
JOIN
    salaries s
    ON e.emp_no = s.emp_no
GROUP BY
    e.emp_no, s.emp_no;


Dynamic conversion of long data to wide with multiple columns

are you looking for this,

CREATE TABLE #T(ID INT,SchoolID INT,Section INT,RepScore DECIMAL(5,2)
,SportsScore DECIMAL(5,2),PartyScore DECIMAL(5,2))
INSERT INTO #T VALUES
(1,20,1 ,23.2,70.2,42.3)
,(2,20,6 ,65.2,75.8,52.3)
,(3,20,7 ,77.2,72.2,66.3)
,(4,21,10,13.2,40.2,72.3)
,(5,21,11,25.2,55.8,72.3)
,(6,21,12,37.2,62.2,76.3)
--SELECT * FROM #T

DECLARE @colRpName VARCHAR(1000)
DECLARE @colRpVal VARCHAR(1000)
DECLARE @colSportVal VARCHAR(1000)
DECLARE @colPartyVal VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT
@colRpName = STUFF(( SELECT distinct  '], [' + cast([Section] as varchar)
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ']'
,  @colRpVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[a ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colSportVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[b ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colPartyVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[c ' + cast([Section] as varchar)+']'
                        FROM #T t1 FOR XML PATH('') ), 1, 2, '') + ''
from #T t

--SELECT @colRpName,@colRpVal

SET @sqlquery = '
SELECT fp.SchoolID,'+@colRpVal+'
,'+@colSportVal+'
,'+@colPartyVal+'
FROM
(SELECT SchoolID,[section],RepScore

FROM #T z) base
PIVOT (sum(RepScore) FOR [section] IN ('+@colRpName+')) AS   fp
CROSS APPLY
(
SELECT fp1.SchoolID,'+@colSportVal+'
FROM
(SELECT SchoolID,[section],SportsScore

FROM #T z) base1
PIVOT (sum(SportsScore) FOR [section] IN ('+@colRpName+')) AS   fp1
WHERE fp1.SchoolID=fp.SchoolID
)ca
CROSS APPLY
(
SELECT fp2.SchoolID,'+@colPartyVal+'
FROM
(SELECT SchoolID,[section],PartyScore

FROM #T z) base2
PIVOT (sum(PartyScore) FOR [section] IN ('+@colRpName+')) AS   fp2
WHERE fp2.SchoolID=fp.SchoolID
)ca1
  '

--print @sqlquery
  EXEC(@sqlquery)-- please use sp_executesql

DROP TABLE #T


MongoDB Upgrade to 3.0: HostAndPort Exception

The error/exception is because you point to a local database with --configdb. That should be a serverip:port:

##shards/replicas:
mongod --shardsvr --replSet ReplName --dbpath /data/mongodb (--port 27018) --logpath /data/log/log.log ...
##configservers:
mongod --configsvr --dbpath /data/cfg (--port 27019) --logpath /data/log/log.cfg
##mongos routers:
mongos --configdb cfg1IP:cfg1PORT,cfg2IP:cfg2PORT,cfg3IP:cfg3PORT (--port 27017) --logpath /data/log/log.mongos

Or you can use a configuration file.

But here you seem to mix the configuration for configservers with mongos-routers.


How to find the last data backup on secondary replica?

The differential_base_time and lsn columns in sys.database_files indicate the point in time and in the log stream when the last non-copyonly backup was taken.  So:

select min(differential_base_time) last_full_backup_utc,
       datediff(second,min(differential_base_time),SYSUTCDATETIME())/( 24*60*60.) days_since_last_full_backup
from sys.database_files
where type_desc = 'ROWS'
and is_read_only = 0

If you have read-only filegroups a backup will not update the differential_base_time, so ignore those.


Is this good practice?

In this circumstance I would say yes. I'd probably also add an OPTION (RECOMPILE) to let it "sniff" the variable values. The optimal plan will likely vary dependant on the proportion of rows in the larger table that match this range.

It provides a potentially useful extra path to the optimiser and it is not something that the query optimiser ever does by itself as far as I know. The closest thing to it is that with a merge join it will stop processing an input when either one is finished. Thus meaning that it potentially avoids a full scan.

The only downside that springs to mind would be if the calculation of the min/max range values itself might be expensive (but this should be very cheap if the table you are using as a filter is indexed on that column).

I created two test tables

CREATE TABLE EmployeeBenefitData(EmployeeID INT PRIMARY KEY);

CREATE TABLE FilteredEmployee(EmployeeID INT PRIMARY KEY);

And loaded EmployeeBenefitData with integers from 1 to 4,000,000 (6,456 pages)

And FilteredEmployee with integers from 2,000,000 AND 2,010,000 (19 pages)

And then ran 6 queries of the following form

DECLARE @E1 INT,
        @E2 INT

SELECT @E1 = FE.EmployeeID,
       @E2 = EBD.EmployeeID
FROM   FilteredEmployee FE
       INNER LOOP JOIN EmployeeBenefitData EBD
         ON FE.EmployeeID = EBD.EmployeeID
OPTION (MAXDOP 1);

The 6 permutations were made up by inverting the order of the two tables and trying all three join types LOOP, MERGE, HASH.

Results were as follows

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |      6456 |             0 |  8250009 |
| FE         | EBD         | Merge |              1 |      3257 |             1 |       19 |
| EBD        | FE          | Merge |              1 |      3257 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |      6456 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |      6456 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

The above figures illustrate the point about merge join as it "only" scans just over half the larger table. It still read all the rows from 1 to 1,999,999 first though and discarded them.

Repeating the experiment with a WHERE EBD.EmployeeID BETWEEN 2000000 AND 2010000 gave the following.

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |        21 |             0 |    20636 |
| FE         | EBD         | Merge |              1 |        21 |             1 |       19 |
| EBD        | FE          | Merge |              1 |        21 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |        21 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |        21 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

The only query that didn't benefit from the additional range predicate is the one where the larger table was on the inside of a nested loops join.

This is of course not surprising as that plan (plan 1 below) is driven by repeated index seeks using the values from FilteredEmployee.

Plan 1 was also the one chosen "naturally" by the optimiser without the range predicate. With the range predicate in place it chose a different merge join plan seeking into the relevant index range without scanning unnecessary rows and costed that as significantly cheaper (plan 2)


Why did a Redshift UNLOAD increase precision of a float4 field in the output?

Answer from AWS forum:

this happens when you use FLOAT for the decimal data because it cannot store values
of arbitrary precision. https://en.wikipedia.org/wiki/IEEE_754

I would generally always recommend using the DECIMAL datatype unless you have an
existing application that has an unchangeable requirement for FLOAT, e.g.,
calculation expects FLOAT and output cannot change.

Additionally, by using DECIMAL you are able to use our new AZ64 compression encoding
which will reduce the amount of storage needed and improve your query performance.
https://aws.amazon.com/about-aws/whats-new/2019/10/amazon-redshift-introduces-az64-a-new-compression-encoding-for-optimized-storage-and-high-query-performance/


Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

This is a query you can try. Sure it can be simplified but is a good start I think.
The key is to traverse all the managers chain recursively retaining salary information in order to compare later.

with recursive t1 as ( -- Traverse emps all the way up in the manager chain
  select id, name, salary, manager, salary indsalary, manager indirect, 1 as level
  from emp
  union all
    select t1.id, t1.name, t1.salary, e.manager, e.salary, t1.manager, t1.level + 1
    from emp e, t1
    where t1.manager = e.id
)
, t2 as ( -- Check salary condition
  select *
  from t1
  where  indsalary >= 2 * salary
)
, t3 as ( -- get the row numbers
  select t2.*, row_number() over (partition by id order by level) rn
  from t2
)
, t4 as ( -- retain only first row per emp
  select * from t3
  where rn = 1
)
-- Add emps that has no manager or don't fullfill the salary condition
select e.id, t4.indirect manager
from emp e
left join t4
  on (e.id = t4.id);

Result:

 id | manager
----+---------
  1 |  [null]
  2 |       1
  3 |       1
  4 |       1
  5 |       2
(5 rows)


ora-49204: recursive dde invocation at phase i - after switchover to standby

Per Oracle Support, this is probably a bug
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2322290_1.html

Function kcidr_file_header_check_common is File Header check which cause of this issue. If you see this in the Stack Trace and Standby opens Read only successfully then you are most likely encountering this bug.


Is the mac and Windows machines running same version of FrontBase? if not you have to export the database to file format and import it:

On mac :

WRITE ALL OUTPUT(DIR = '<path>', TYPE = 'FrontBase',
CONTENT = TRUE);

On Windows:

script <path>/schema.sql;

See more in release note: http://www.frontbase.com/cgi-bin/WebObjects/FBWebSite


Can't migrate standalone MongoDB to Cluster

Logged into mongodb, to the admin database, with a user who has the following roles: clusterMonitor, readAnyDatabase, userAdminAnyDatabase

...

  not authorized on admin to execute command { replSetInitiate: undefined, $db: \“admin\” }“

This error is expected since clusterMonitor is a read-only role.

To initialise or update replica set configurations when access control is enabled your user needs a cluster management role like clusterManager or clusterAdmin.


is it recommend to set "--setParameter ttlMonitorEnabled=false" for better performance if you don't use TTL Indexes

Except under edge case conditions I can't see this making any noticeable impact if you are not using the feature, it is intended as an emergency measure if you are using the feature and experiencing heavy load (stop deletes until load subsides basically).

Since you are not using TTL anywhere all MongoDB will be doing is scanning your indexes for one that contains the expireAfterSeconds field. The example you linked was an edge case itself, not many people have a quarter million indexes in a database, but even in that case a read query on such a small collection as system.indexes is unlikely to use enough resources to cause you issues. It might show up in the slow query logs, but since it is basically a no-op and only takes a read lock, there would be minimal impact elsewhere.

That's the theory out of the way, if you want to judge impact for your particular use case, profile your system so you know what current performance looks like, then profile it after you turn it off. If it does have an impact, then I am sure the engineers at MongoDB would like to know about it (and your use case) so they can evaluate how the feature operates and see if the behavior you see is intended or not.


MariaDB indices do not start at 1 after removing database and re-creating it

Looking at the current content, there are 4 records with id from 24 to 27. After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?

As the database is recreated by he application, and the application is inserting those rows as part of the recreation, it's completely in the hands of the application. It's possible the application does recreate the tables with auto_increment=1, but then inserts those rows and deliberately specifying the PK values. Or, it's recreating the table, but with auto_increment=24, so when it inserts the rows, they start at that value. You could try to recreate the table manually yourself without any rows and auto_increment=1.

Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have "hidden" somewhere else...

Yes, it seems the start values are hidden inside the application.


Exponential log file growth on quiet database with log backups

Have you checked the error log ?
Run select * from sys.databases & look at the log_reuse_wait_desc column.
Run DBCC OPENTRAN and see if you have any open active transactions ?
Query the default trace and get the timings of when the file growths are happening
https://www.mssqltips.com/sqlservertip/3445/using-the-sql-server-default-trace-to-audit-events/


You should let the table rollback fully. If you kill the mysqld process, crash recovery will happen anyway.

As far as the DELETE query goes, you may want to execute it in chunks or all-in-one. I would like to suggest an alternate method for deletion. Here is a DELETE JOIN method:

Create a Table to Collect Primary Keys for Rows to DELETE from conv_with
Perform a JOIN and DELETE simultaneously

Here is the SQL for it

CREATE TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);
INSERT INTO conv_with_delete
    SELECT id FROM conv_with
    WHERE user_id_from IN (uid1, uid2, ..., uid100000);
DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);

You can either load conv_with_delete with all 20M ids, or cycle through one user_id_from  at a time. Perhaps a stored procedure might work for you.

First let's make some sample data:

DROP DATABASE IF EXISTS noam;
CREATE DATABASE noam;
USE noam
CREATE TABLE conv_load (user_id_from int not null) ENGINE=MyISAM;
INSERT INTO conv_load (user_id_from) VALUES
( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29);
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
SELECT COUNT(1) FROM conv_load;
CREATE TABLE conv_with
(
    id int not null auto_increment,
    user_id_from int not null,
    primary key (id),
    key `from` (user_id_from)
) ENGINE=InnoDB;
INSERT INTO conv_with (user_id_from) SELECT * FROM conv_load;

Here is the creation of the sample data:

mysql> DROP DATABASE IF EXISTS noam;
Query OK, 2 rows affected (0.33 sec)

mysql> CREATE DATABASE noam;
Query OK, 1 row affected (0.00 sec)

mysql> USE noam
Database changed
mysql> CREATE TABLE conv_load (user_id_from int not null) ENGINE=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO conv_load (user_id_from) VALUES
    -> ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
    -> (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
    -> (20),(21),(22),(23),(24),(25),(26),(27),(28),(29);
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 60 rows affected (0.00 sec)
Records: 60  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 120 rows affected (0.00 sec)
Records: 120  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 240 rows affected (0.00 sec)
Records: 240  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 480 rows affected (0.00 sec)
Records: 480  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 960 rows affected (0.00 sec)
Records: 960  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 1920 rows affected (0.00 sec)
Records: 1920  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 3840 rows affected (0.00 sec)
Records: 3840  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 7680 rows affected (0.00 sec)
Records: 7680  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 15360 rows affected (0.01 sec)
Records: 15360  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 30720 rows affected (0.01 sec)
Records: 30720  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 61440 rows affected (0.02 sec)
Records: 61440  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 122880 rows affected (0.04 sec)
Records: 122880  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 245760 rows affected (0.07 sec)
Records: 245760  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 491520 rows affected (0.15 sec)
Records: 491520  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 983040 rows affected (0.30 sec)
Records: 983040  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(1) FROM conv_load;
+----------+
| COUNT(1) |
+----------+
|  1966080 |
+----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE conv_with
    -> (
    ->     id int not null auto_increment,
    ->     user_id_from int not null,
    ->     primary key (id),
    ->     key `from` (user_id_from)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO conv_with (user_id_from) SELECT * FROM conv_load;
Query OK, 1966080 rows affected (25.41 sec)
Records: 1966080  Duplicates: 0  Warnings: 0

mysql>

OK, the table has 1,966,080 rows. Let's go about deleting anything whose user_id_from is 13 or 23.

mysql> CREATE TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_with_delete
    ->     SELECT id FROM conv_with
    ->     WHERE user_id_from IN (13,23);
Query OK, 131072 rows affected (2.39 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);
Query OK, 131072 rows affected (8.22 sec)

mysql>

OK so the DELETE JOIN works in practice manually executed.

OK now for the Stored Procedure to Encapsulate This

DELIMITER $$

DROP PROCEDURE IF EXISTS `noam`.`BulkZap` $$
CREATE PROCEDURE `noam`.`BulkZap` (ListOfIDs VARCHAR(1024))
BEGIN

    DROP TABLE IF EXISTS conv_with_delete;
    CREATE TEMPORARY TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
    ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);

    SET @sql = CONCAT('INSERT INTO conv_with_delete SELECT id ',
    'FROM conv_with WHERE user_id_from IN (',ListOfIDs,')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);

END $$

DELIMITER ;

Let's Test it with :

Count before the DELETE JOIN
Execute the DELETE JOIN
Count after the DELETE JOIN

Here we go:

mysql> SELECT COUNT(1) FROM conv_with;
+----------+
| COUNT(1) |
+----------+
|  1966080 |
+----------+
1 row in set (0.50 sec)

mysql> CALL BulkZap('13,23');
Query OK, 131072 rows affected (5.49 sec)

mysql> SELECT COUNT(1) FROM conv_with;
+----------+
| COUNT(1) |
+----------+
|  1835008 |
+----------+
1 row in set (0.48 sec)

mysql>

Hey, not bad. Deleted 131,072 rows in 5.49 seconds. You need to use the stored procedure  passing in a comma-separated list of used_id_from values.

Give it a Try !!!


This is quite clearly explained in the documentation. The value in parentheses indicates the string length. So nvarchar(2) has a string length of 2.

https://msdn.microsoft.com/en-gb/library/ms186939.aspx

  nvarchar [ ( n | max ) ]  Variable-length Unicode string data. n
  defines the string length and can be a value from 1 through 4,000.


How does the alter command work internally when adding a column?

See the ALTER TABLE docs here:

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

"In most cases, ALTER TABLE makes a temporary copy of the original table."


Ola Hallengrens code for Index Optimization - Clarifications

The difference between user_databases & availability_group_databases is that when specifying user_databases you will optimize every user database (also the ones in the AG group) if you specify availability_group_databases you will only optimize the indexes of databases inside an availability group.

If you do not have tables with fragmentation above 1000 pages, this will not hurt your SQL Server performance. I recommend you also read this post from Brent Ozar. https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/


PostgreSQL - Variables on transaction-level

From today's point of view it was rather a stupid idea or bad design and I would pass this parameter directly to the database.


MySQL slow_log table: querying takes forever

Answer originally left as a comment by Michael - sqlbot:

Mostly it's a MySQL thing.  The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which emulates a table but uses a simple CSV file as its tablespace.

This allows appending to the slow query "table" to be a low overhead operation, but of course a CSV file has no indexes, so it has to be scanned from the beginning.

I haven't used Google Cloud SQL so I can't offer an authoritative answer on what options you might have, there, such as changing it to a MyISAM table and indexing it.

See Selecting General Query and Slow Query Log Output Destinations in the product documentation.


WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive

Set "wal_level" back to "archive", and restart.  Then drop the slot.  Then if you don't want leave it at "archive", set it back to "minimal" again, set max_replication_slots to 0, and restart again.

  Which I find strange because I'm trying to remove it, not use it.

Removing a slot requires access to the slot machinery.  It is a form of "use".


how to join table from different database with different Users of database?

This looks like MySQL.

But independent of the rdbms, you need to grand a user access to a database for it to be able to do anything (like reading data) in that database. In your example you have one user that has access to the one database and another user that has access to the other database. You need to give the user that you connect as when executing this query access to both databases.

How you do that is dependent on your hosting provider. If you use PHPMySQL you can go to the User section and grant the necessary rights. However, you hosting provider might have turned that option off. In that case you need to talk to them to get this resolved.


How to reduce size of PostgreSQL table after dropping columns?

TEXT type columns arent stored with the table data. PostgreSQL treats them like a blob and use a toast table to store the actual data. Maybe thats the reason that you are not seeing the table size shrink.


Difference between SSAS 2005 and SSAS 2008

I went through a pretty thorough 2008R2 training a year ago. It wasn't radically different from 2005, but it was different enough that you're going to face quite a bit of frustration when you can't do things in 2005, or the things you want to do are done differently. SSAS has been changing quite a lot over the last several releases. This is the list of changes between 2005 & 2008, and it's not small. The new designers are the things that are going to cause you the most pain.


SQL Only display data with Max latest date date


Does SQL Server use pointers instead of storing duplicate rows?

No, SQL Server does not detect duplicate rows

SQL Server is filling up empty or partially empty pages within the allocated pages.

So if I have a very narrow row (2 columns say), I can add a few hundred more rows on the same page without increasing space used.

Quick and dirty demo (without duplicate rows, but you can play with this if you want)

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO
CREATE TABLE dbo.Demo (DemoID int NOT NULL IDENTITY(1,1), Demo char(1) NOT NULL)
GO
SELECT 'zero rows, zero space', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('a');
GO
SELECT 'one row. Peanuts', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 100
SELECT '101 rows. All on one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 1899
SELECT '2000 rows. More than one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

TRUNCATE TABLE dbo.Demo
GO
SELECT 'zero rows, zero space. TRUNCATE deallocates pages', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('c');
GO 500
SELECT '500 rows. Some space used', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

DELETE dbo.Demo
GO
SELECT 'zero rows after delete. Space still allocated', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO


Oracle - Archivelog files , can we delete them?

It sounds like you have not been doing regular housekeeping by performing an rman backup of the archivelogs and using the DELETE INPUT option of the BACKUP ARCHIVELOG command.  If this is truly a test machine, I wou
ld first manually delete the archives using the proper OS command. That will physically free up the disk space.  Then you need to inform Oracle of that fact:

rman target /

rman> crosscheck archivelog all; -- this will detect that the logs have gone missing and mark their records in the control file as 'expired'

rman> delete noprompt expired archivelog all; -- this will remove the record of the archivelogs from the control file and adjust the FRA usage accordingly

rman> backup database plus archivelog all delete all input;

An important concept that many miss is that the FRA is merely a bookeeping mechanism.  It does not physically reserve space on the disk and does not guarantee that the specified space is actually available.  As regards the archlogs, when one is created, a record of that is written to the control file.  If the log is deleted manuall, the FRA bookeeping mechanism doesn't know and thinks the file is still there consuming space.

If you are in archlog mode, you need to be taking regular backups, including backups of the archlogs with the DELETE INPUT option.


multi-column index has 1 unique column vs single column

I would suggest both,

Making comment_id a PRIMARY KEY: which is both UNIQUE and NOT NULL
If shop_name is frequently a part of the query, making shop_name it's own index.


Missing rows in Visual DBA, exist when queried specifically

(Please forgive the SQL Server test case - the problem is common to all SQL implementations because that of common semantics mandated by the SQL Standard.)

Even though you have used a LEFT OUTER JOIN, the semantics of SQL can convert this to an implied INNER JOIN if you improperly put constant-test conditions in a WHERE clause instead of the JOIN clause. The example below ilustrates this.

Preliminaries to create test data:

if object_id('Address') is not null drop table Address;
if object_id('Person') is not null drop table Person;
create table Person (
    ID          int identity    not null primary key clustered,
    Name        varchar(10)     not null unique nonclustered
);

create table Address (
    ID          int identity    not null primary key clustered,
    PersonID    int             not null references Person(ID),
    SequenceNo  int             not null,
    Address     varchar(40)     not null

    ,constraint NK unique nonclustered (PersonID,SequenceNo)
);

insert Person(Name) values
     ('Fred')
    ,('George')
    ,('Ron')
    ,('Ginny')
;

with data as(
    select * from(values
            ('Ron',   1,'Attic bedroom, The Burrows')
           ,('Ron',   2,'Junior Boys Dorm, Gryffindor House')
           ,('Fred',  1,'Blue bedroom, The Burrows')
           ,('Fred',  2,'Senior Boys Dorm, Gryffindor House')
           ,('George',1,'Green bedroom, The Burrows')
           ,('George',2,'Senior Boys Dorm, Gryffindor House')
           ,('Ginny', 1,'Pink bedroom, The Burrows')
    )data(PersonName,SequenceNo,Address)
)
insert Address(PersonID,SequenceNo,Address)
select
    Person.ID,
    SequenceNo,
    Address
from data join Person on data.PersonName = Person.Name
;

Now these two queries

select *
from Person
left join Address on Address.PersonID = Person.ID
               where SequenceNo       = 2
;
select *
from Person
left join Address on Address.PersonID = Person.ID
                 and SequenceNo       = 2
;

respectively return

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
4           Ginny      NULL        NULL        NULL        NULL

Note how, despite the LEFT OUTER JOIN which one expects to ensure that all Person rows are returned, the placement of the condition SequenceNo = 2 in the WHERE clause instead of the JOIN clause coerces the join into an INNER JOIN; so that Ginny is dropped from the first result set.

This is a specific example of how, more generally, the occurrence of a NULL value in a field being tested violates intuition. One loses the Excluded Middle, so that when a predicate A may be NULL it is no longer tautologically true that A or NOT A will give you all rows; all rows with a NULL value for A will be silently dropped.


numeric is bigger and slower than bigint (8 bytes) in every respect. Unfortunately, like you said, bigint is just not big enough. Maximum is 2^63-1 = 9223372036854775801, 19 decimal digits.
varchar or text (same thing internally) are bigger and slower, yet. Plus, character types are encumbered with COLLATION rules unless explicitly defined without.
SELECT pg_column_size(numeric '18446744073709551616')  -- 16 bytes in RAM
     , pg_column_size(text    '18446744073709551616')  -- 24 bytes in RAM

(But 13 / 21 bytes on disk.) See:
db<>fiddle here
numeric saves additional space for trailing zeroes, which text cannot.
numeric disallows non-numbers from being stored and enforces some sanity out of the box.
Faced with your requirements, choose numeric and never look back.
Related:

What is the overhead for varchar(n)?

Or you could install the extension pguint by Peter Eisentraut, one of the core hackers of the Postgres project.
Be sure to read the readme first. The extension provides several additional (most of them unsigned) integer types. You might just extract uint8 (unsigned 64-bit integer) and ditch the rest to avoid over-crowding the type system.


Ideally, we would like to use a plan guide to add a TABLE HINT, so the guided query becomes:
UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState
OPTION (TABLE HINT (JOB_TRIGGERS, INDEX(ix_jobname_jobgroup_triggerstate)));

Unfortunately, this is not possible because the UPDATE is written without a FROM clause:

Msg 8724, Level 16, State 1, Line 45
Cannot execute query. Table-valued or OPENROWSET function 'JOB_TRIGGERS' cannot be specified in the TABLE HINT clause.

You can work around this by capturing the XML showplan for the equivalent:
UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState

Note this form of the query has a FROM clause to support the index hint. The query must be written exactly as above, without the usual alias for the target table.
You can then use this XML (without the opening <?xml version="1.0" encoding="utf-16"?>) element as the @hints parameter in sp_create_plan_guide.
Example
Given the table and indexes:
CREATE TABLE dbo.JOB_TRIGGERS
(
    JOB_TRIGGERS_ID integer PRIMARY KEY,
    JOB_NAME nvarchar(18) NOT NULL,
    JOB_GROUP nvarchar(28) NOT NULL,
    TRIGGER_STATE nvarchar(6) NOT NULL,
);

CREATE NONCLUSTERED INDEX
    ix_jobname_jobgroup_triggerstate
ON dbo.JOB_TRIGGERS
    (JOB_NAME, JOB_GROUP, TRIGGER_STATE);

The plan guide (using the captured XML from the index-hinted form above) is:
EXECUTE sys.sp_create_plan_guide
    @name = N'UPDATE JOB_TRIGGERS using nonclustered index',
    @stmt = N'UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @hints = N'<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4411.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0365109" StatementText="DECLARE&#xD;&#xA;    @state nvarchar(14),&#xD;&#xA;    @jobName nvarchar(18),&#xD;&#xA;    @jobGroup nvarchar(28),&#xD;&#xA;    @oldState nvarchar(6);&#xD;&#xA;&#xD;&#xA;UPDATE JOB_TRIGGERS&#xD;&#xA;SET TRIGGER_STATE = @state &#xD;&#xA;FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))&#xD;&#xA;WHERE JOB_NAME = @jobName &#xD;&#xA;AND JOB_GROUP = @jobGroup &#xD;&#xA;AND TRIGGER_STATE = @oldState" StatementType="UPDATE" QueryHash="0xA993366BDAC14B06" QueryPlanHash="0x21B868F786AB4C56" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="320">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="52428" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="6723992" />
            <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0365109">
              <OutputList />
              <Update DMLRequestSort="false">
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[PK__JOB_TRIG__CF66DD90CB121F34]" IndexKind="Clustered" Storage="RowStore" />
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = RaiseIfNullUpdate([Expr1002])">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullUpdate">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="Expr1002" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                  <OutputList>
                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                    <ColumnReference Column="Expr1002" />
                    <ColumnReference Column="Expr1008" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1008" />
                        <ScalarOperator ScalarString="[Expr1008]">
                          <Identifier>
                            <ColumnReference Column="Expr1008" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                      <OutputList>
                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                        <ColumnReference Column="Expr1002" />
                        <ColumnReference Column="Expr1008" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1008" />
                            <ScalarOperator ScalarString="CASE WHEN [Expr1005] THEN (0) ELSE (1) END">
                              <IF>
                                <Condition>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1005" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Condition>
                                <Then>
                                  <ScalarOperator>
                                    <Const ConstValue="(0)" />
                                  </ScalarOperator>
                                </Then>
                                <Else>
                                  <ScalarOperator>
                                    <Const ConstValue="(1)" />
                                  </ScalarOperator>
                                </Else>
                              </IF>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="22" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165088">
                          <OutputList>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                            <ColumnReference Column="Expr1002" />
                            <ColumnReference Column="Expr1005" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1002" />
                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(6),[@state],0)">
                                  <Identifier>
                                    <ColumnReference Column="ConstExpr1006">
                                      <ScalarOperator>
                                        <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@state" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                    </ColumnReference>
                                  </Identifier>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1005" />
                                <ScalarOperator ScalarString="CASE WHEN [Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = CONVERT_IMPLICIT(nvarchar(6),[@state],0) THEN (1) ELSE (0) END">
                                  <IF>
                                    <Condition>
                                      <ScalarOperator>
                                        <Compare CompareOp="BINARY IS">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="ConstExpr1006">
                                                <ScalarOperator>
                                                  <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="@state" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Convert>
                                                </ScalarOperator>
                                              </ColumnReference>
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Condition>
                                    <Then>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Then>
                                    <Else>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Else>
                                  </IF>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="21" EstimateCPU="0.00010056" EstimateIO="0.013125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="5" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0165087">
                              <OutputList>
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                              </OutputList>
                              <Spool>
                                <RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                  <OutputList>
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_NAME" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_GROUP" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[@jobName]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobName" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@jobGroup]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobGroup" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@oldState]">
                                                <Identifier>
                                                  <ColumnReference Column="@oldState" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </Spool>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>';

Submitting the query:
EXECUTE sys.sp_executesql
    @stmt = N'UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState',
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @state = N'', @jobName = N'', @jobGroup = N'', @oldState = N'';

Gives the desired plan:

The plan properties show the plan guide was used:

You cannot just use the exact plan guide above - it is just an example that worked on the toy schema I created to reproduce your issue. Nevertheless, the general process outlined should work for you.


Select and update specific mysql data in single query

You can make a SELECT as part of an UPDATE statement, but you can't make an UPDATE part of a SELECT statement. However, based on your example you don't need to, instead you can just do the following:

UPDATE parts
SET keywords = CONCAT(keywords,'Phillips')
WHERE name LIKE '%Phi%'

If you want to retrieve the results after you've updated them just execute the following afterwards:

SELECT *
FROM parts
WHERE name LIKE '%Phi%'


You can use UNPIVOT to transpose the question columns from SurveyResults into rows and then average the KRA values from there. This db<>fiddle shows an example.

The first query shows the UNPIVOT and the second query shows a dynamically generated SELECT statement that would allow you to add additional question columns without needing to recode the SELECT statement.


Discrepancy in CPU time and reads in SQL Server profiler

@DanGuzman correctly pointed you to Sommarskog's excellent article, which you should read.

Then, if the below simple steps don't work, try looking at the plan cache and seeing what's in there.  Or use the Query Store, since you're on SQL 2016, to see how many different execution plans there are.

You're comparing SET STATISTICS (anything) to Profiler.  Don't do that.  It doesn't work.  Profiler and Extended Events capture relatively accurate numbers.  SET STATISTICS (anything) does NOT - as a single example, it doesn't capture function cost!
You're trying to match plans done at different times, probably with different statistics.  Stop that.
Go ahead and update statistics for ALL tables involved in the suspect query, preferably with FULLSCAN, bother to see if that changes the behavior, and before your "run it myself" tests.
Look at the query itself.

Is parameter sniffing involved?
Is it thousands of lines long once reasonably formatted, with dozens of joins, often to the same table?  If so, quit having Entity Framework try to create complex SQL statements on its own.  It does a terrible job.


Latitude and Longtitude Distance Calculation

Okay, few notions about your procedure

DECIMAL(12,12) means 12 digits, where ALL 12 are behind a
floating point (so basically, 0.*). To see an example, execute
SELECT CAST(23.45 AS DECIMAL(12,12)); and see what output does it
give you.
I suggest that you change DECIMAL definitions to DECIMAL(9,6)
which means 9 digits, where 6 are after the point. I think that
would suffice for your calculations.
Why are you escaping all your numbers?
Why are you using SELECT xx INTO yy for setting variables? Why
don't you just use SET yy = xx;?
Your function returns INT, which means that it will cancel
everything after the floating point. Is this what you need?

If you change all your decimal definitions properly (even the input ones), I think that your function should return proper result then (altough without the floating point). After that unquote your numbers (there is no need to quote them, even in your select query).

Let me know how it works out.


PAGEIOLATCH_XX waits are logged by SQL Server when it is waiting for data to be read from the disk. Index maintenance is a notoriously intensive operation and because of this it should be performed at your quietest times to avoid any impact on production.

You mention you have queries that are causing the same waits. If this is at the same time as the index maintenance then that is not that odd but if it's happening at other times it could be down to memory pressure (not enough room in RAM to store pages so they need to be read from the disk again), large scans or it could even indicate there is a potential problem with your disks. More investigation is needed to rule each of these out.


Is there a way to copy/initialize values in a set

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess.

Assuming that your code involves only a table and its columns (no @variables involved) and is something like:

UPDATE
    tablename
SET
    column_a = column_b,
    column_b = 0
WHERE
    (condition) ;

It is valid SQL and 100% correct. Correct, meaning that after the update column_a will have the values that column_b had before the update and column_b will have 0, for all rows that pass the (condition).

SQL-Server follows this behaviour strictly and there is no reason to assume that it will deviate from the standard in a future release.

SQL works in sets and the updates are done "simultaneously" on the whole row. (they may not be done simultaneously but the result is the same as if they were.) You could change the code to this and the result will be (guaranteed) the same:

...
SET
    column_b = 0,
    column_a = column_b
...

In fact, the following is valid and equivalent SQL as well (although not yet implemented in SQL-Server);

UPDATE
    tablename
SET
    (column_a, column_b) = (column_b, 0)
WHERE
    (condition) ;

I haven't tested Azure but if it deviated from this basic SQL behaviour, it would better be documented somewhere.


Using Dynamic Queries Across Multiple Databases to Change Views

The call needs to be dynamic for the databases, as does the object_id.

This is basic (does not check for offline databases and the like), but gives you an idea as to how it would function, looping through the databases in sys.databases.

DECLARE @viewDef NVARCHAR(max);
DECLARE @DatabaseList sysname;
SELECT @DatabaseList = MIN(name) FROM sys.databases;
WHILE @DatabaseList IS NOT NULL
BEGIN

SELECT @viewDef = 'SELECT Definition from ' + QUOTENAME(@DatabaseList) + '.sys.sql_modules WHERE OBJECT_ID = OBJECT_ID(N'+CHAR(39)+QUOTENAME(@DatabaseList) +'.dbo.view'+CHAR(39)+');'
--PRINT @viewDef
EXEC (@viewDef)

SELECT @DatabaseList = MIN(name) FROM sys.databases WHERE name > @DatabaseList;
END


You can use server side trace  (different from using Profiler GUI that incurs more resources) during your testing or your business cycle and capture only stuff related to SP's. Then you can load that in a table or excel for further analysis.

Second approach, is to use DMV sys.dm_exec_procedure_stats (with limitation that if sql server is restarted, then the data is flushed).

You can even schedule a job to capture DMV data to a table to keep it persisted.

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

Refer to :

How to find unused Stored Procedures in SQL Server 2005/2008
Identifying Unused Objects in a Database


Is there any index with O(1) complexity for lookup in PostgreSQL?

You'll need to the read the PostgresSQL Index Types documentation for the version that you are using. I think you are looking for a Hash Index, but as as the documentation explains the real world performance and pros and cons depend on the implementation details and recommendations of the particular version that you are using.


Should we consider "sqlplus" as an Oracle client?

SQL*Plus is a client that is already available for a very long time. If you use sqlplus username/password then it does not use the tnsnames.ora. It connects to the local database that is defined in the ORACLE_SID environment variable. If you use sqlplus username/password@SID then it will search in the tnsnames.ora file to find out the location of the SID database.