Why would splitting up a query make it faster, and can I/should I fix this?

There is no direct way to tell MySQL to work on a single query "in chunks," since SQL is (primarily, at least) a declarative language ("platform, I need you to accomplish this end result") not a procedural one ("platform, I need you to process this data using these steps: for each...").

Memory might be your issue -- which storage engine do these tables use?  ...but really, it seems like there are some more likely possibilities:

According to the EXPLAIN SELECT you posted, no index is being used to join the maps table (key is NULL), even though the mapExpireTime index is available.   When the optimizer determines that the index doesn't have sufficient selectivity, it won't be used -- and the more records in the maps table your @mapExpireTime would not eliminate, the less likely the optimizer will be to select that index, and instead opt to a full table scan, which may explain why performance falls off.  On shorter date ranges, does the index appear in key for the maps table in EXPLAIN?  If so, that's the short answer to "why does it slow down?"

It's possible that ANALYZE TABLE maps; might build some better index stats and improve the query performance.   Conventional wisdom is that the constant "shiftiness" of InnoDB index stats makes this assertion untrue, but I've seen too many times where the stats on an InnoDB table are in a state that biases the optimizer towards a phenomenally bad query plan and ANALYZE TABLE cleans it right up.  (I think the reason the conventional wisdom on this is so widespread is the fact that SHOW TABLE STATUS actually triggers a random index stats dive, so the act of looking makes what you intended to look at different than what it was before you looked, but I digress).

Another possibility would be that you could add an index (ID,mapExpireTime) on the maps table, which seems at first glance quite redundant, but might be used as a covering index... which would be far better than the full table scan and join buffer that's getting used now.


MSX Job deployment: Not creating job on certain servers

I solved this issue myself by completing the following tasks:

In some cases, even though the server existed in the list of registered servers, it was giving me an error that the server wasn't registered, if this occurred, I deleted the server and then re-added it.
I defected the server and then enlisted it again as a target, but before doing so I would make a record of the existing MSX jobs on the target. After defecting the target loses its MSX jobs, so it was then necessary to re-add them.
During the enlistment, SQL Server made modifications to the service account that runs the target jobs, and made other changes. This process resolved the issues mentioned above.

There is also the ability to resynchronise jobs on the target servers, but this did not assist in my case.


SQL Server: what should be done when a process is blocking another process?

Despite this question having already being answered and the answer accepted, I'm going to try and make a case for the contrary approach...

DO NOT EVER BLINDLY KILL A SPID

Unless you know what work that SPID has done, you have no understanding of the scale of the rollback you're about to initiate.

The worst kind of blocking chains are those which have unexpectedly brought your 24/7 system to its knees. 99% of the time, the query at the head of that blocking chain is going to be a plain old select query gone awry, queueing up any and all write activity behind it. In these circumstances, a KILL will save the day.

The other 1% of the time, that rogue query will be the last step in a long running transaction that will take as long to rollback as it did to get to the stage it's at now. Taking a moment to understand how you got here will 99% of the time be unnecessary. 1% of the time, you could be saving minutes/hours/days of downtime.


Modify legacy table schemas that have no primary key

I would add a BIGINT Identity column as the non-clustered primary key for any table that does not have a primary key.  This will cause minimal disruption and use minimal storage space.

It will often improve performance especially for updates and deletes.


How to generate auto increment values for my duplicate column in my sql?

Try this one, execute one at a time set variable and select statement.

SET @Variable:=0;
SELECT @Variable:=@Variable+1 AS AddNewColumn,ID,Title,Lscno
FROM tutorials_tbl
WHERE Lscno IN (
    SELECT Lscno
    FROM tutorials_tbl
    GROUP BY Lscno
    HAVING COUNT(Lscno) > 1
)ORDER BY Lscno


How to investigate memory grant warnings?

This is usually resultant (though I believe other things can also cause it) from Cardinality Estimate issues. You should look at the Actual Execution Plan and see if you have any mis-estimates by comparing the Estimated Number of Rows total to the Actual Number of Rows. If you update your question with the Execution Plan (you can upload it to Paste The Plan and link it to your question) then we can give you better direction on the source of your excessive memory grant issue.


Is there a better way to add failed node back to postgres cluster?

That's exactly what pg_rewind was written for. It allows you to undo any transactions that have happened on node1 after node2 was promoted. It can be seen as a fast version of pg_basebackup in this case.
There is no guarantee that pg_rewind will succeed. It depends on whether you have all the WAL since the last common checkpoint of node1 and node2. If there is not enough, you have to resort to pg_basebackup.
You can make sure that old WAL is kept around for a while by setting wal_keep_size (wal_keep_segments in older releases) appropriately.


If A is in one-to-zero-to-one relation with B, then B inherits its primary key from A:

If B is in one-to-zero-to-one relation with C, then C inherits its primary key from B, which is the primary key of A:

Nothing out of sort here, this is perfectly acceptable.


Does Resource Governor help protect page life expectancy?

This MSDN page states the memory settings only affect query memory:

Resource Governor Resource Pool

  MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

  These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

The above indicates that buffer pool is excluded from resource pool limitations.  Therefore, if you are intending on performing some actions that may cause the entire buffer pool to be emptied in order to run your process, you may want to ensure that process is ran during off-hours.

The following is an excerpt from the MSDN blog, SQL Server Page Life Expectancy:

  Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object.  There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

  Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

  So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

  Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

  So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

  PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

From questions in the comments here:

  What is "query memory" and how is it different from the cache?

Query memory is used for execution of queries. It's temporarily used for things like sorts, bitmap creates, hash tables, etc. Cache contains in-memory table data.

  It sounds like the answer to my question is that the PLE will not be protected by the Resource Governor at all. Does that sound right?

Correct. Page Life Expectancy is all about how long data pages stay in memory.


IMHO I think that the fast way on this case is looking first at TableC, and if not exists on TableC, search the record on TableA/B.

I've set up a small example:

create table tableA (id int, path varchar(30), other_things int);
create table tableB (id int, path varchar(30), other_things int);
create table tableC (id int, path varchar(30), other_things int);
insert into tableA values
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file2/account1/0002', 1),
(5, '/file1/account2/0003', 1),(6, '/file2/account1/0003', 1);
insert into tableB values
(1, '/file1/account1/0001', 1),(2, '/file1/account2/0001', 1),
(3, '/file1/account1/0002', 1),(4, '/file1/account2/0002', 1),
(5, '/file2/account1/0003', 1),(6, '/file1/account1/0003', 1);
insert into tableC values
(1, '/file3/account3/0004', 1),(2, '/file3/account4/0004', 1),
(3, '/file4/account3/0005', 1),(4, '/file3/account4/0005', 1),
(5, '/file3/account3/0006', 1),(6, '/file4/account3/0006', 1);

As far as you are using a regular expression /file/account/[0-9]+ to filter records, you could use the operator ~. (POSIX Regular Expressions)

But have a look at this answer of Erwin Brandstetter about using regular expressions, just in case you could add some extra index to your table.

I've used a function because it allows me to use IF EXISTS (). It only search the record on TableA if it doesn't exists on TableC. Obviously you can change the search order.

CREATE OR REPLACE FUNCTION getABC (MyPath VARCHAR)
 RETURNS TABLE (
 ID text,
 client_id text,
 other int
)
AS $$
BEGIN

   IF EXISTS(SELECT 1 FROM TableC WHERE path ~ $1) THEN
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid,
                            t1.other_things
                     FROM   TableC t1
                     WHERE  t1.path ~ $1;
    ELSE
        RETURN QUERY SELECT split_part(t1.path::text, '/'::text, 2)::text AS id,
                            split_part(t1.path::text, '/'::text, 3)::text AS clientid,
                            t1.other_things
                     FROM   TableA t1
                     JOIN   TableB t2
                     ON     t1.path = t2.path
                     WHERE  t1.path ~ $1;
    END IF;

    RETURN;
END; $$
LANGUAGE 'plpgsql';

Now you can search for a specific path on this way:

  This records are fetched from TableC

select * from getABC('/file3/account4/[0-9]+');

id    | client_id | other
:---- | :-------- | ----:
file3 | account4  |     1
file3 | account4  |     1

  This records are fetched from TableA

select * from getABC('/file1/account1/[0-9]+');

id    | client_id | other
:---- | :-------- | ----:
file1 | account1  |     1
file1 | account1  |     1

dbfiddle here


Query Plan showing missing index warning on trivial statements

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes.  Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index.  The missing index feature isn't always right, so you should always review whether you really need the columns suggested indexed or if you can modify another index to include the columns instead of adding a separate index.

You can use this query from Glenn Berry to look at the missing index dmvs and see what the index advantage, unique_compiles, and user_seeks to help make  the decision:

select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] as [Database.Schema.Table]
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with (nolock)
inner join sys.dm_db_missing_index_groups as mig with (nolock) on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid with (nolock) on mig.index_handle = mid.index_handle
--where mid.[statement] = '[yourdb].[dbo].[OrganisationLocations]'

order by index_advantage desc
option (recompile);


What are the main differences between InnoDB and MyISAM?

First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.

The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.

So to summarize (TL;DR):

InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.


Speed impact of using varchar in SQL Server 2008

You are confusing text/varchar(max) (LOB types) with varchar(n)/char(n)

LOB types will still be stored in-row/out-of-row based on size, UPDATE, sp_tableoption etc
varchar(n)/char(n)  have always been "in-row"

Otherwise, if you use char(1000) not varchar(1000) and the average data length is, say, 42 this is bad design and a lot of wasted space.


Always On best practice for secondary node maintenance

No AG maintenance mode. You can restart secundary node and you will get events in logs, both SQL server and cluster, but nothing else. If you need to restart primary, first do an AG manual failover and check everything went fine.


How to Solve the Failed to initialize the CLR

You could have a bad / incomplete install of .NET 4 (I have seen it happen at least once). Try downloading and running the following repair utility from Microsoft to see if it can fix the problem:

Microsoft .NET Framework Repair Tool


ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6

If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL 5.6.

The column plugin is column #41 in mysql.user in MySQL 5.5/5.6

mysql> SELECT column_name,ordinal_position FROM information_schema.columns
    -> WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
+-------------+------------------+
| column_name | ordinal_position |
+-------------+------------------+
| plugin      |               41 |
+-------------+------------------+
1 row in set (0.04 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.24    |
+-----------+
1 row in set (0.02 sec)

mysql>

That column does not appear in MySQL 5.1, 5.0 or 4.x. What this tells me is that somehow MySQL was upgraded to 5.6 but still has the mysql.user from 5.1 or older.

If you run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; and you get 39, I have just the fix for you.

See my post MySQL service stops after trying to grant privileges to a user on how manually fix mysql.user from 5.1 straight to to 5.6.


Is it possible to set initial index number in a table?

In most circumstances with optimal queries and good index choices this should not be necessary at all. It would only make a difference when the database engine is scanning the whole table (or in the cases where LIMIT is in effect, scanning the table until  matches are found) and you should be trying to eliminate this sort of query plan anyway.

Indexes are usually implemented as tree structures, usually some variety of b-tree. A lookup for a single value (so a single row) in a binary tree has a complexity of the order of Log2 N so for 1,000,000 rows you can expect "up to 20" page reads (and most of those pages should already be in RAM so that isn't disk reads/seeks). For 1,000,000,000 rows in the table this only grows to "up to 30" page reads. This effect is massive compared to the effect of just ignoring the old rows in your table. The matter is the same for range queries: the initial seek on the index is the same, and from there the reading is sequential with respect to the tree structure anyway.

So instead of trying to ignore old data artificially, instead have good index and query design and it will not be an issue.

Obviously this is a generalism, there obviously are some queries that simply have to scan large chunks of the table, even all of it, that can't be further optimised, and no end of indexing will help the query planner, but these are genuinely rare. Some database systems have features for dealing with this sort of issue over massive tables: for instance MS SQL Server, postgres, and others, support filtered indexes (called partial indexes in postgres' terminology) which can exactly implement what you are asking for in your question, but that is probably not what you actually need at this point. You might also look at data partitioning but again I suspect that is not what you need, partitioning is more about managing the storage of large data than about searching it more effectively.


SQL Backup with Mirror option in maintenance plan

Ola Hallengren automated backup solution will solve your problem for newly added databases.

Check for the MirrorDirectory option:

  MirrorDirectory

  Specify one or multiple directories to perform a mirrored backup.

  The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option
  in the SQL Server BACKUP command.


SQL Server merge replication across domains

This turned out to be to do with the permissions on the snapshot folder. I added a user to each server with the same credentials and ran the subscription under that account.

Still don't know why I couldn't get verbose logging to happen, but in the end I didn't need it.


How can I select using the same fields from an undetermined number of tables using MySQL?

As far as I know, the only way you can dynamically put something in FROM is to use Prepared Statements. Also, I believe you should use UNION to get results you want, not cross join. For instance, you result query should look like

SELECT * FROM
(
   select bar_id, bar_weight from table1
   UNION
   select bar_id, bar_weight from table2
)a
WHERE ... ORDER BY ... LIMIT ...

Not

select bar_id, bar_weight
from table1,table2
WHERE ... ORDER BY ... LIMIT ...


Multiple questions, multiple answers:

No need to "spot" potential redundant indexes: use either common_schema or Percona Toolkit to find such keys. But I've checked -- and no, there are no redundant keys.
You do not have to use sent_at in the indexes
If you do, it does not matter (partition-wise) where in the index.

But the one thing that is not told is how you query your table. Do you do an equality check on most fields and range-check on sent_at? In such case better have sent_at indexed last. If there is an equality check on sent_in or range check on same field, with little or no filtering on other columns, then have sent_in indexed by itself, or first in sequence. Right now you have 1 month of data in each partition; getting it indexed by time is not such a bad idea.


SQL Server Index and Statistics Maintenance performance question

Can somebody share with me his experience in using IndexOptimize for a
  database with a very high number of indexes ?

You are right, Ola's script first gets data from all the index related DMV's and inserts them inside of @tmpIndexesStatistics, even if one table or one index is specified.
For a lot of indexes, it gets stuck on this first command, which is a fairly huge query. See below for the query and even further below for a possible fix for this.

TL;DR

In one of the queries, the dynamic management views are called a couple of times each with multiple filters.

All this results in a fairly big execution plan.

Creating temp tables and storing the data from the DMV's prior to running the query is a workaround. You could optimize further with indexes on these temp tables / Rewrites.

Test data: >100K empty tables + >200k empty indexes

USE Test2
GO
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @i int = 0

DECLARE @counter int = 1000
WHILE @i <+ @counter
BEGIN
SET @SQL = N'CREATE TABLE dbo.'+QUOTENAME(cast(@i as nvarchar(20)))+'( id int, val varchar(255));'
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ ' ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(id) '
EXEC(@sql)
SET @SQL = N'CREATE INDEX IX_'+cast(@i as nvarchar(20))+ '_2 ON '+QUOTENAME(cast(@i as nvarchar(20)))+'(val) '
EXEC(@sql)
set @I +=1

IF @i = @counter
BEGIN

  IF @counter < 100000
  BEGIN
        SET @counter += 1000;
  END
END
END

select count(*) from sys.tables;
--100731

select count(*) from sys.indexes  where index_id != 0;
--201614

When changing the parameters to  work on all the indexes, and printing instead of executing the statement ( @Execute='N')

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@Execute='N';

This huge query comes into play

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2];
SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed
FROM (SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, indexes.allow_page_locks AS AllowPageLocks,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN('image','text','ntext')) THEN 1 ELSE 0 END AS IsImageText,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN('xml') OR (types.name IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 WHEN indexes.[type] = 2
            AND EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id]
            AND index_columns.column_id = columns.column_id
            INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN('xml') OR (types.[name] IN('varchar','nvarchar','varbinary') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1)))
            THEN 1 ELSE 0 END AS IsNewLOB,
CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream,
CASE WHEN EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore,
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed,
CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id
WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = 'timestamp') THEN 1 ELSE 0 END AS IsTimestamp,
CASE WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id AND destination_data_spaces.destination_id = partitions.partition_number) THEN 1 WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1 WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM sys.tables tables INNER JOIN sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup, CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = 'PAUSED' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.indexes indexes INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] LEFT OUTER JOIN sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id] LEFT OUTER JOIN sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id] WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0

UNION

SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, tables.is_memory_optimized AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, NULL AS AllowPageLocks, NULL AS IsImageText, NULL AS IsNewLOB, NULL AS IsFileStream, NULL AS IsColumnStore, NULL AS IsComputed, NULL AS IsTimestamp, NULL AS OnReadOnlyFileGroup, NULL AS ResumableIndexOperation, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, stats.no_recompute AS NoRecompute, stats.is_incremental AS IsIncremental, NULL AS PartitionID, dm_db_incremental_stats_properties.partition_number AS PartitionNumber, NULL AS PartitionCount, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.stats stats INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id] OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0 AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)) IndexesStatistics

Now, when we try to find what triggers this command inside [dbo].[IndexOptimize]

IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')

With @ActionsPreferred corresponding to the fragmentationlevel actions and @UpdateStatistics being update statistics. Meaning that it will run whenever we do a stat update or a index rebuild / reorganize.

no filtering is applied, the query runs over all the indexes in the database.

As a result, at the moment we will have to go through all that trouble, even if we specify one table.

EXECUTE MNGDB.dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N'

The actual index filtering happen after the the huge command runs:

IF @Indexes IS NULL
      BEGIN
        UPDATE tmpIndexesStatistics
        SET tmpIndexesStatistics.Selected = 1
        FROM @tmpIndexesStatistics tmpIndexesStatistics
      END
      ELSE
      BEGIN
...

and updates the tmpIndexesStatistics table accordingly, as to execute index operations on these indexes.

This query was running over 60 minutes on my small dev server before killing it.

What could possibly be a fix for this?

Well, you would have to change ola's script, but a solution with temp tables worked for me, and executed in 1 minute. Without a doubt, it could be optimized further:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE [test2];

select *
INTO #Indexes
from sys.indexes;

select *
INTO #Index_columns
FROM sys.index_columns;

SELECT *
INTO #columns
FROM SYS.columns;

SELECT *
INTO #types
FROM
sys.types;

SELECT *
INTO #destination_data_spaces
FROM sys.destination_data_spaces;

SELECT *
INTO #filegroups
FROM sys.filegroups;

SELECT *
INTO #stats
FROM sys.stats;

SELECT *
INTO #objects
FROM sys.objects;

SELECT *
INTO #partitions
FROM sys.partitions;

SELECT *
INTO #tables
FROM sys.tables;

SELECT *
INTO #index_resumable_operations
FROM sys.index_resumable_operations

SELECT *
INTO #schemas
FROM sys.schemas

+ rerun the query with temp tables instead (char limit)

To change this in the index optimization procedure

Sorry Ola this will hurt to look at

When I scripted the proc, this was from line 1430 Until 1537

 IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
    BEGIN

      -- Select indexes in the current database
      IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
      BEGIN
        SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'
                              + 'USE ' + QUOTENAME(@CurrentDatabaseName) + ';
                                select *
                                INTO #Indexes
                                from sys.indexes;
                                select *
                                INTO #Index_columns
                                FROM sys.index_columns;
                                SELECT *
                                INTO #columns
                                FROM SYS.columns;
                                SELECT *
                                INTO #types
                                FROM
                                sys.types;
                                SELECT *
                                INTO #destination_data_spaces
                                from sys.destination_data_spaces;
                                SELECT *
                                INTO #filegroups
                                FROM sys.filegroups;
                                SELECT * kill 64
                                INTO #stats
                                from sys.stats;
                                SELECT *
                                INTO #objects
                                from sys.objects;
                                SELECT *
                                INTO #partitions
                                from sys.partitions;
                                SELECT *
                                INTO #tables
                                FROM sys.tables;
                                SELECT *
                                INTO #index_resumable_operations
                                FROM sys.index_resumable_operations;
                                SELECT *
                                INTO #schemas
                                FROM sys.schemas; '
                              + ' SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed'
                              + ' FROM ('

        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')
        BEGIN
          SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID'
                                                    + ', schemas.[name] AS SchemaName'
                                                    + ', objects.[object_id] AS ObjectID'
                                                    + ', objects.[name] AS ObjectName'
                                                    + ', RTRIM(objects.[type]) AS ObjectType'
                                                    + ', ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE '0' END + ' AS IsMemoryOptimized'
                                                    + ', indexes.index_id AS IndexID'
                                                    + ', indexes.[name] AS IndexName'
                                                    + ', indexes.[type] AS IndexType'
                                                    + ', indexes.allow_page_locks AS AllowPageLocks'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN(''image'',''text'',''ntext'')) THEN 1 ELSE 0 END AS IsImageText'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1'
                                                    + ' WHEN indexes.[type] = 2 AND EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 ELSE 0 END AS IsNewLOB'

                                                    + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM #columns columns WHERE columns.[object_id] = objects.object_id  AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed'

                                                    + ', CASE WHEN EXISTS(SELECT * FROM #Index_columns index_columns INNER JOIN #columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN #types types ON columns.system_type_id = types.system_type_id WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = ''timestamp'') THEN 1 ELSE 0 END AS IsTimestamp'

                                                    + ', CASE WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN #filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id' + CASE WHEN @PartitionLevel = 'Y' THEN ' AND destination_data_spaces.destination_id = partitions.partition_number' ELSE '' END + ') THEN 1'
                                                    + ' WHEN EXISTS (SELECT * FROM #Indexes indexes2 INNER JOIN #filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1'
                                                    + ' WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM #tables tables INNER JOIN #filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup'

                                                    + ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM #index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'

                                                    + ', stats.stats_id AS StatisticsID'
                                                    + ', stats.name AS StatisticsName'
                                                    + ', stats.no_recompute AS NoRecompute'
                                                    + ', ' + CASE WHEN @Version >= 12 THEN 'stats.is_incremental' ELSE '0' END + ' AS IsIncremental'
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_id AS PartitionID' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionID' END
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_number AS PartitionNumber' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionNumber' END
                                                    + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'IndexPartitions.partition_count AS PartitionCount' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionCount' END
                                                    + ', 0 AS [Order]'
                                                    + ', 0 AS Selected'
                                                    + ', 0 AS Completed'
                                                    + ' FROM #Indexes indexes'
                                                    + ' INNER JOIN #objects objects ON indexes.[object_id] = objects.[object_id]'
                                                    + ' INNER JOIN #schemas schemas ON objects.[schema_id] = schemas.[schema_id]'
                                                    + ' LEFT OUTER JOIN #tables tables ON objects.[object_id] = tables.[object_id]'
                                                    + ' LEFT OUTER JOIN #stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'
          IF @PartitionLevel = 'Y'
          BEGIN
            SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN #partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id'
                                                      + ' LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM #partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'
          END

          SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')'
                                                    + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END
                                                    + ' AND indexes.[type] IN(1,2,3,4,5,6,7)'
                                                    + ' AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'
        END

When testing and rerunning the procedure with one table,it executed in one minute:

EXECUTE dbo.IndexOptimize
@Databases = 'test2',
@Indexes =  'test2.dbo.[83631]',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60,
@EXECUTE= 'N';

Duration      00:01:18

Earlier filters / temp tables with indexes / ... could be used to improve the query further.


sp_blitzfirst not showing memory grant columns

Do you perhaps need to use sp_BlitzCache instead?

In the source code for sp_BlitzFirst is the line:

  Run sp_BlitzCache @SortOrder = 'memory grant' to find queries with huge memory grants and tune them.

On July 14, 2016, the following changes were made (commit 62b6e9df):

  Put all of the what’s running now work inside an IF statement so it
  only runs if we’re in ExpertMode (to minimize executions, since this
  proc needs to go fast under load.) Cleaned up some of the “memory not
  granted” and “memory already granted” stuff to just be N/A since it can
  mean different things depending on whether the query needs memory.


Query only for the first n results of like

I understand you do not want only to limit the number of returned results (which could be done with a LIMIT at the end of the query), but the number of records that the LIKE operation is applied.

One idea could be, instead of apply the LIKE operation on all records of EXPLANATIONS table, doing on a limited number of items of the table:

SELECT exp.ID FROM(select * from EXPLANATIONS limit 1000) exp WHERE
exp.EXPLANATION LIKE "%" + contains + "%"


In SQL 2016 there are 5 filter predicates you can use to filter logins.
For NT login/user you can use all 5 but SQL login/user you can use number 3,4 and 5 only.

sqlserver.nt_username-Collect NT username
sqlserver.session_nt_username-Get the current session NT user
sqlserver.server_principal_name-Get the name of the Server Principal in whose context the event is being fired.
sqlserver.username-Get the current username
sqlserver.session_server_principal_name-Get the name of the Server    Principal that originated the session in which the event is being    fired.

Using tsql, you need to change my code depending on  what kind of account and real value you are using.

    ALTER EVENT SESSION [test] ON SERVER
    ADD EVENT sqlserver.sp_statement_completed(
     WHERE ((((([sqlserver].[username]=N'')
       AND ([sqlserver].[session_server_principal_name]=N''))
       AND ([sqlserver].[server_principal_name]=N''))
       AND ([sqlserver].[session_nt_user]=N''))
       AND ([sqlserver].[session_server_principal_name]=N'')))
    GO

From GUI:

Once you pick select events, highlight event and click configure.

Under filter set your And/or condition, filed, operator, value.


You can have stored procedures execute as the definer instead of the invoker:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

See: Access Control for Stored Programs and Views


There are any number of ways to do literally what you ask, but I would generally advise against using any of them. In most cases, all you need do is prevent any changes to the table while your process completes. This can be done with a TABLOCKX hint inside the transaction:
SELECT TOP (0) NULL FROM Production.Product AS P WITH (TABLOCKX);

You could also temporarily revoke permissions, or use an application lock. Other alternatives exist; the best approach depends on the details.
If you really want to acquire Sch-M (a system lock), one way is to call sp_compile e.g.:
-- AdventureWorks sample database
BEGIN TRANSACTION;
EXECUTE sys.sp_recompile @objname = N'Production.Product';
SELECT * FROM sys.dm_tran_locks AS DTL WHERE DTL.request_session_id = @@SPID;
ROLLBACK TRANSACTION;

If the entire contents of the table are invalidated by whatever maintenance you are doing, you'll naturally get a Sch-M lock when you perform a TRUNCATE TABLE. Again, what is best depends on details that have not been provided.


Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)

There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but for example:

Updates that affect an index key might appear to do an in-place update from the execution plan, but this is not the case at the lowest level. An update that affects an index key results in a separate delete and insert at the Storage Engine level in SQL Server 2005 onward.
Updates that change the value a key in a unique index generally mean that SQL Server has to introduce a Split/Sort/Collapse combination in the execution plan to avoid transient unique key violations. The Split explicitly turns each update into separate delete and insert operations. The cost of the extra operators (particularly the Sort) can often result in reduced performance. Plans that insert or delete to a unique index do not require this same protection, though Split operators may still be seen for other reasons.
There are internal optimizations available for inserts that simply do not apply to updates (or deletes). Some of these are associated with minimal logging, and some require a hint or trace flag (like 610). Other optimizations do not require trace flags or hints, and are not exposed in execution plans.

  I mean the performance gains are there

This is the key point. I can tell you that my experience has been that updates and deletes are generally less optimized in SQL Server than inserts, but the key is to test in your environment. An insert and delete will not always be faster than a single update, in much the same way that separate operations will not always be faster than a single MERGE (though that is probably even more common).

There are even cases where an insert followed by an update (!) can be faster than a single update. Though not directly related to your example, you may find the details of that in my article, "Optimizing Update Queries" interesting.

Also related:

Optimizing T-SQL queries that change data
Minimal Logging with INSERT…SELECT and Fast Load Context
Minimal Logging with INSERT…SELECT into Empty Clustered Tables
Minimal Logging with INSERT…SELECT into Heap Tables


One of the server IDs conflicted further down the chain which caused the issue of the bin logs syncing but not replicating

Warning to all, make sure all your server IDs are unique!


Oracle random number as default column value

Trigger is the only way to do it.

Virtual column is different from what you want, and that will not accept DBMS_RANDOM.RANDOM, because that is not deterministic:

Column Expressions

  ...it can contain only the following forms of expression:

  Columns of the subject table — the table being created, altered, or indexed
  Constants (strings or numbers)
  Deterministic functions — either SQL built-in functions or user-defined functions

The syntax of default values for columns is similar to your original statement, but that does not accept DBMS_RANDOM.RANDOM because PL/SQL is not allowed there:

Restrictions on Default Column Values

  Default column values are subject to the following restrictions:

  A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or
  date constants that are not fully specified.
  The expression can be of any form except a scalar subquery expression.


As far as I can tell, the default users for a fresh mysql server is root@localhost and mysql.sys@localhost, you'll need to create a bharath@localhost user and create a .my.cnf under your $HOME folder with the following contents:

[mysql]
user=bharath
password=<yourpreferredpassword>
host=localhost


is it possible to modify identify columns start with value in PostgreSQL 13

You can use setval() to change the current value of the underlying sequence.
To get the sequence's name use pg_get_serial_sequence()
select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), 1234);

or more dynamic:
select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), max(id))
from public.rss_sub_source;

Online demo


PostgreSQL : Where is index kept until check point occures ?

Your explanation here is not quite right:

  When checkpoint reached or WAL buffer becomes full, these WAL files are written to data files.

What happens when a CHECKPOINT hits is that dirty buffers held in shared_buffers are guaranteed to be written (i.e. fsync'ed) to disk. The WAL files must have already been fsync'ed to disk at COMMIT time, assuming synchronous_commit=on and fsync=on, etc.

Your question of:

  My question is where does the database keep indexes before writing to data files.

is answered here, in particular this bit:

  Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint.

(emphasis added). Also, your explanation of:

  then in this time window [before a CHECKPOINT] all the data is in WAL files and when user request for data, database read WAL files and provide data.

PostgreSQL reads its (index, heap, etc.) blocks either from shared_buffers, if they are available there, or from disk if not. Postgres does not need to read out of its WAL files other than for crash recovery (similarly, standby) purposes.


Using SQL Server native backup and Veritas Backup Exec to backup the database and transaction log on the same database

i suggest you to configure one of the two backups in copy only:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver15
It is not strictly disallowed to use two different backup solutions but it exists only one single chain of backup so restore would be a nightmare.
Better to do full and ltog backup with one solution and use the other solution for full backups with copy only option.


Any script to auto test tune and benchmark mysql config?

Such a tool would be almost completely worthless in practical use.

max_connections has nothing to do with performance.  It's a safety mechanism to prevent a runaway application from making so many connections that the server becomes overwhelmed.  The value makes absolutely no difference unless you are using that many connections.  It is also a value that you should never reach under normal operating conditions.

table_open_cache I had hoped by now that everybody knew just to leave this one alone.  It tends to scale negatively -- the more "sensible" the value seems to be, the more wrong you are.  The worst performing servers in my entire network were the ones where someone, at some time in the past, had felt a need to mess with this.

query_cache_type just set it to 1, with a reasonable size, something in the 32 to 128 MiB range, and walk away.  You will be hard-pressed to develop a meaningful benchmark here, and a small query cache definitely has its value for many workloads.  But keep it reasonably small, or turn it off. Above a certain size, it will scale negatively and degrade performance.  The more processor cores you have, the more potential exists for the query cache mutex to become a bottleneck. For workloads where there is virtually no chance of a cache hit, it's a waste of cpu cycles.

innodb_buffer_pool_size has only one correct answer: How much memory can you spare?  Set that here.  Lower will always be slower, and higher will also be slower if the server goes into swap, and of course infinitely slower if it crashes because the system ran out of memory entirely.  Correlated to this, if you're running anything else on your database server, you are doing it wrong.

innodb_buffer_pool_instances As long as each pool is at least 1GB, there's not a lot of variation available or needed here on a decent sized server.

innodb_adaptive_hash_index_partitions is probably a poster child for an argument that applies in some sense to all of the other values above, and some not mentioned: even if you tweaked this to find a sweet spot with sysbench... who cares?  How does that help you?  It doesn't.

Your specific workload is the only thing really relevant to the ideal values for the adaptive hash index, the query cache, and so many of the other tweakable parameters on MySQL Server.

Given two servers with "identical everything" -- storage, memory, CPUs, background cosmic radiation levels -- identical everything, that is, except the workload (meaning the schemata, datafill, and the actual queries being run) the "optimum" configuration parameters may still be wildly divergent.

Benchmarking with anything other than a real live workload will not give you anything of significant value, and tweaking a server is not usually the ticket to performance.

Well-written queries against well-structured schemata and appropriately indexed tables, and use of read replicas, will take you much further than tweaking.


Data Type to use for Exchange Rate in SSAS Multidimensional

The range of the Double data type is huge but is it an imprecise floating point.


Database Full Backup fails being deadlock victim

SQL Server backup operations do not acquire locks, see - Paul Randal's post.
Therefore BACKUP DATABASE... or BACKUP LOG... cannot become deadlock victims.

Probably some other queries that take part in your backup jobs take part in deadlocks.

If you can describe your backup solution in detail, we might be able to help you.

I personally recommend Ola Hallengren's backup solution to do the job.


Converting MongoDB instance from Standalone to Replica Set and Backing Up

Is such conversion considered a safe operation?

Converting a standalone node to a replica set is a straightforward procedure, but if you do not have a backup of your deployment (and this data is important) I would definitely prioritise creating and testing a backup. If you only have a single copy of your data (the one being used!) you will have very limited (and possibly painful) recovery options if something unwelcome accidentally (or intentionally) happens to your data.

  Are there any caveats considering the time of "conversion", the additional memory needed etc. or does that only apply if I connect more nodes?

A replica set member will use some extra storage space and I/O for the operation log (oplog) which stores a rolling record of operations for use cases like replication and change streams. The conversion process is essentially restarting mongod with a new replSet option and then initialising the oplog and replica set configuration using rs.initate().

  What are the ways of backing up such big instances? I have heard of and read about Filesystem Snapshots but I'm not sure what are they and how to handle them.

The MongoDB documentation describes supported Backup Methods including Back Up and Restore with Filesystem Snapshots. Filesystem snapshots use system level tools that vary depending on the O/S and filesystem used for your deployment.

For example, Linux has LVM (Logical Volume Manager) which enables taking a consistent backup of a block device. The initial snapshot will have some more noticeable overhead, but subsequent snapshots are generally quick. However, snapshots typically depend on the same storage infrastructure as the original disk so it is essential that you have a plan for archiving snapshots and saving backups elsewhere. If you are using a cloud provider (Amazon, Google Cloud, Azure, ...) with network data volumes, these also have snapshot APIs.


What does WITH FORMAT do on a DIFFERENTIAL backup?

But to use COMPRESSION in a differential backup, I need to use FORMAT.

I'm not sure why you think you need to use FORMAT at all. This works just fine, I end up with two different differential backup files, each about 80kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif1.bak' WITH COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif2.bak' WITH COMPRESSION, DIFFERENTIAL;

As does writing both differentials to the same file (though I don't know why people like doing this), I end up with a single backup file that contains two differential backups, about 165kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH COMPRESSION, DIFFERENTIAL;

As does overwriting the same file each time, I end up with a single differential backup representing the most recent differential backup I took, about 80kb:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;

All of these result in compressed differential backups, and I didn't use FORMAT once.

I don't have a 2008 R2 instance to test, and it might be the case that it is impossible in that version to - say - capture multiple diffs to the same backup file (what the documentation might call a media set, but that was more relevant back in that decade when we used tape drives) and take the first diff compression (or encryption, etc.) and the second one without. Compression on and off is fine in modern versions, though I didn't test encryption. In any case, you won't be in that situation if you make each backup its own file in the first place, which I think is what you should do.

This makes your question rather academic. But to answer it, for all intents and purposes, FORMAT on a differential backup effectively does the same thing as INIT - it wipes out any previous backups in the file. So with this:

BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH INIT, COMPRESSION, DIFFERENTIAL;
GO
BACKUP DATABASE db TO DISK = 'c:\bk\dif.bak' WITH FORMAT, COMPRESSION, DIFFERENTIAL;

I end up with the same, single 80kb backup as if I had used INIT in both statements. All older backups in that file, regardless of format, except the last one, are removed.


How do I return two aliases from the same table query?

This query will return both queries as a single result. The last column added QueryIndicator is there to indicate which query the results came from. Note that UNION ALL allows duplicates, use UNION if you need to filter them out.

(SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Unread' AS QueryIndicator
FROM notifications
WHERE READ = false LIMIT 99)

UNION ALL

SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Read' AS QueryIndicator
FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 99;

Here would be a JSON export example. Based on code found by Jack Christensen on hashrocket.

DB Fiddle Results and Example

select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(d)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 99
     ) d
    ) as Read_Notifications
    FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 1)t
    union all
    select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(c)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications
WHERE READ = false   LIMIT 99
     ) c
    ) as Unread_Notifications
    FROM notifications
WHERE READ = false
 LIMIT 1)t;


Should you use the schema name in WHERE, ON, and other clauses?

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

  In the first scenario should I add dbo.Table to each column name?

For single tables, table aliases are OK to be excluded.

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

  IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

Table alias will include the FULLY qualified table name.

e.g. dbo.Table as T1 will reference the dbo.Table.

  And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

First your query example is wrong. It should be (carefully see the aliases I have used, else it will be syntactically wrong - SQL Server will throw errors).

 SELECT T1.Col1, T1.Col2, T2.Col3,
    FROM dbo.Table T1
    INNER JOIN dbo.Table2 T2
    ON T1.Col1 =  T2.Col5
    WHERE T2Col6 = Value;

Hopefully, the above query will answer your 3rd question as well.


How to properly clean Merge Replication Setup

Somehow you are ending up with orphaned replication metadata after deleting the subscriptions and/or the publication.  This can happen when using the GUI to drop subscriptions and publication.  Instead, try using T-SQL replication stored procedures to delete the subscriptions and publication.

How to: Delete a Push Subscription (Replication Transact-SQL Programming)

How to: Delete a Pull Subscription (Replication Transact-SQL Programming)

How to: Delete a Publication (Replication Transact-SQL Programming)

After deleting the subscriptions and publication, verify the replication metadata was removed.  If not, use sp_removedbreplication to remove all replication objects from the databases.


Oracle 12c: textual search over all PL/SQL code

Provided that the following is true:

1) The function is NOT in a package
2) The function call is NOT dynamic SQL

Then you can simply see what its dependencies are from dba_dependencies:

SELECT *
  FROM dba_dependencies
 WHERE referenced_owner = 'MYOWNER' AND referenced_name = 'MYOBJECT';

Otherwise, you can check dba_source:

SELECT *
  FROM dba_source
 WHERE upper(text) like '%MYOWNER.MYOBJECT%';

To view the source for all objects for a schema:

SELECT *
  FROM dba_source
 WHERE owner = 'MYOWNER'
 ORDER BY type, name, line;


Splitting a csv column from select query into multiple columns

Making use of Jeff Moden's Tally-Ho! CSV splitter from here:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
WITH E1(N) AS (
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
       ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
            SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER()
                                                        OVER (ORDER BY (SELECT NULL)) FROM E4
        ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just
                     -- once for each delimiter)
            SELECT 1 UNION ALL
            SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
        ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
            SELECT s.N1,
                   ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
            FROM cteStart s
        )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final
     -- element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
go

We can code the solution as an apply against Jeff's function and a pivot like so:

with data as (
    select Code,Location,Quantity,Store from ( values
        ('L698-W-EA',          NULL,                                      2, 'A')
       ,('L82009-EA',          'A1K2, A1N2, C4Y3, CBP2',                  2, 'A')
       ,('L80401-A-EA',        'A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20',    17,'A')
       ,('CWD2132W-BOX-25PK',  'A-AISLE',                                 1, 'M')
       ,('GM22660003-EA',      'B1K2',                                    1, 'M')
    )data(Code,Location,Quantity,Store)
)
,shredded as (
    select Code,Location,Quantity,Store,t.*
    from data
    cross apply [dbo].[DelimitedSplit8K](data.Location,',') as t
)
select
    pvt.Code,pvt.Quantity,pvt.Store
   ,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1
   ,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2
   ,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3
   ,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4
   ,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5
   ,cast(isnull(pvt.[6],' ') as varchar(8)) as Loc6
from shredded
pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5],[6])) pvt;
;
go

yielding this:

Code              Quantity    Store Loc1     Loc2     Loc3     Loc4     Loc5     Loc6
----------------- ----------- ----- -------- -------- -------- -------- -------- --------
L698-W-EA         2           A
L82009-EA         2           A     A1K2      A1N2     C4Y3     CBP2
L80401-A-EA       17          A     A1S2      SHIP     R2F1     CBP5     BRP      BRP1-20
CWD2132W-BOX-25PK 1           M     A-AISLE
GM22660003-EA     1           M     B1K2


Oracle 11.2.0.4: Basic info using RMAN for backups

QUESTION 1
So, how far back do I need to keep the archive log files?

You need to keep archive logs until you take another full backup.
If you take full backup weekly then you need to keep archive logs for week.

QUESTION 2
Does the database automatically remove the unneeded log files, or is this something I must address using cron jobs, etc.?

If you are using Fast Recovery Area for your backup and recovery purpose then Oracle will take care of it. Otherwise you need to delete it manually using RMAN commands.
For more:Configuring the Fast Recovery Area

QUESTION 3
To complete my understanding of archive logs, do I even need to put the database in archivelog mode to fully restore the database to the point in time of the RMAN backup, if I'm willing to accept loss of data from that time forward?

If you need to perform point time recovery then you need to keep archive logs, thus you are putting your database in archivelog mode. If not simply take cold backup and restore it you can not recover it since you don't have required archived logs.

QUESTION 4
When RMAN finishes, and I want to copy all required backup/restore files to another server, which files need to get copied?

You need to have control file and spfile backup which will be created automatically you have configured control file auto backup in RMAN and backup files to restore it into new host.

QUESTION 5
Not sure if anyone can help answer this, but I do a lot of scientific work that uses all available digits in a  binary_double number. If I choose compression for backups, will I lose accuracy when uncompressing the data?

I have never heard or read about inaccuracy in RMAN decompression. The only disadvantage that I came across is time taken to restore the compressed RMAN backup.


sql server query execution plan via jdbc driver

Running SET STATISTICS XML ON prior to running the query will result in the query being processed, and the XML "actual" execution plan being returned to the client.  This works regardless of the client you are using to connect to SQL Server.  Requesting this via JDBC will result in several resultsets returned to the client, the first result is the query output, the next resultset will be the XML output for the actual query plan.
See the Microsoft Docs for details about the command.
I'm using SQL Server Management Studio (SSMS) below since it provides a nice graphical interface I can use to show the output.
SSMS will show the resulting XML in a resultset that you can simply "click" to see the graphical plan in a new window, for example:
SET STATISTICS IO, TIME, PROFILE, XML ON;
GO

SELECT *
FROM sys.objects

Will show the following results:

In the image above, the last row shows the XML for the actual execution plan.  Clicking it shows this:

You may notice I've also turned on STATISTICS PROFILE which contains a wealth of interesting details as shown in the middle results in the image above.
Specifically for jDBC, the statistics and plan will be returned in additional Result Sets to the client that can be obtained from the Server via Statement.getMoreResults(). See https://docs.microsoft.com/en-us/sql/connect/jdbc/using-multiple-result-sets for details.


Does Batch Mode only apply to Agg Functions on ColumnStore Index

Background
Back in July 2017, Niko blogged about a change in SQL Server 2017

...in SQL Server 2017 there is an internal optimisation for the Query Optimiser, that shall apply FULL optimisation to the execution plans with the Columnstore Indexes, avoiding the pitfall of executing them in the Row Execution Mode

Prior to that, you would get row-mode execution for "trivial plans," even when there were columnstore objects involved.
The execution plan you shared shows that the first query has this problem, which you can confirm in the XML:
StatementOptmLevel="TRIVIAL"

Workaround
One way to work around this, get FULL optimization, and thus get a batch mode scan, is to add a subquery that won't affect your results, like this:
SELECT * FROM dbo.TransCS WHERE (SELECT 1) = 1;

The second query gets full optimization already because of the aggregate function.
Why is this happening?
The only things that come to mind for why this might happen are:

your database has a compatability level of 130 or lower (this seems unlikely because I can see in the execution plan that the 140 cardinality estimator is being used)
this is a change / regression in Azure SQL Database specifically

The "build" indicated in the execution plan is 15.0.1100.504, which looks like a SQL Server 2019 build number (which makes sense, since this is Azure).  I don't have a 2019 instance to test on, but the change in behavior might be there as well

You may want to report this to Microsoft on the feedback site.


Finding opening and closing price using sqlite GROUP BY statement (accessing main query alias in subquery)

You first query is OK although not entirely correct.

You need to be careful with GROUP BY and not have any column in the SELECT list that is not also in the GROUP BY list. So, add the symbol in the group by list.
Next, the LIMIT needs an ORDER BY so you are 100% certain that you get always the same results. SQLite (and many other DBMS) may return the same results all the time but that "all the time" is not guaranteed. You never know when you'll get a different execution plan and weird result.
If you want more aggregate results (like minimum or maximum price) over the windows, you can add them in the list.

The query becomes:

SELECT symbol,
       MIN(datetime) AS mindt,
       MAX(datetime) AS maxdt,
       AVG(price)    AS avg_price,
       MIN(price)    AS min_price,
       MAX(price)    AS max_price
FROM stocks
WHERE datetime < '1999-06-02 00:00:00'
GROUP BY symbol, strftime('%s', DATETIME)/(60*5)
ORDER BY symbol, strftime('%s', DATETIME)/(60*5)
LIMIT 4 ;

Now to get also the first and the last price ("opening" and "closing" in finance terminology) in these 5-minutes windows, you'll either need window functions or correlated subqueries or to wrap this query in a derived table and then join again to the original table. I'll write the 3rd option.

It assumes there is a UNIQUE constraint on (symbol, datetime) or (even without a constraint) that you don't have 2 rows with same symbol and datetime:

SELECT
    s.*,
    s1.price AS opening_price,
    s2.price AS closing_price
FROM
  ( SELECT symbol,
           MIN(datetime) AS mindt,
           MAX(datetime) AS maxdt,
           AVG(price)    AS avg_price,
           MIN(price)    AS min_price,
           MAX(price)    AS max_price
    FROM stocks
    WHERE datetime < '1999-06-02 00:00:00'
    GROUP BY symbol, strftime('%s', DATETIME)/(60*5)
    ORDER BY symbol, strftime('%s', DATETIME)/(60*5)
    LIMIT 4
  ) AS s
  LEFT JOIN
    stocks AS s1
      ON  s.symbol = s1.symbol
      AND s.mindt  = s1.datetime
  LEFT JOIN
    stocks AS s2
      ON  s.symbol = s2.symbol
      AND s.maxdt  = s2.datetime ;


Create an AD user from a SQL Server trigger

What @Mike_Fal said. This is a huge gaping security hole. Not only does SQL need xp_cmdshell, but it will need Domain Admin rights. I also agree with the other sentiments around not using a trigger.

You can still tie it in with your existing process and make it database driven with the security table you are using. I'd probably write a separate, asynchronous process to read from the security table and add the AD users that way. This is a great job for PowerShell; connecting multiple systems and technologies.


Multiple aggregates with cross (lateral) join

The unnesting generates 2 rows for (key = 1, a = 0, b = 1), and the cross join removes the row with the empty array.

So your group by operates on the following set:

key | a  | b | c
----+----+---+--
  1 |  0 | 1 | 1
  1 |  0 | 1 | 2
  1 |  1 | 2 | 3
  1 | -1 | 3 | 2

One solution is to combine two group by queries, each one grouping n a different level:

select *
from (
  select d1."key", min(d1.a), sum(d1.b)
  from data d1
  group by d1."key"
) m
  join (
    select "key", array_agg(DISTINCT x.c) AS c
    from data d2
      left join lateral unnest(d2.c) as x(c) on true
    where x.c is not null
    group by "key"
  ) a on a."key" = m."key";

Another approach is to only include the "first row" for each "unnested" group in the aggregates:

select d."key",
       min(d.a) filter (where idx = 1 or idx is null),
       sum(d.b) filter (where idx = 1 or idx is null),
       array_agg(distinct x.c)
from data AS d
  left join lateral unnest(d.c) with ordinality  AS x(c,idx) on true
group by d."key";

with ordinality returns the position of the unnested element in the original array. For the row with the empty array, that will be null.


MySQL cluster write performance with Galera (MariaDB)

Data Warehousing (your app) and OLTP are somewhat different.  So, I question the relevancy of the benchmark.  OLTP, if I am not mistaken, involves very short 'transactions', which can suffer from the cross-node syncing done at COMMIT by Galera's wsrep.  On the other hand, your insertion of new datapoints could be done in batches of, say, 1000, and not have to worry about the commit overhead.

With 60GB, innodb_buffer_pool_size should be more like 45G.  And set innodb_buffer_pool_instances=16.

On the surface, 19K/sec (per node?) seems faster -- 19K*3 > 54K.  But maybe I am misreading things.

Let's see your SHOW CREATE TABLE for the Fact table.  There are a number of mistakes that can cause severe performance problems in your type of application.

When do you do the "consolidating"?  And how?  Summary table (which I assume is what you are talking about) is key to efficiency.  Sometimes it is even better store the summary data, yet toss the raw data.

Check out my discussion of High speed ingestion and Summary tables.  My point in those are that there are significant speedups that can (and should) be made aside from the underlying structure (hardware, Galera, etc).

Your Galera settings are in the right direction; there may be extra tips in Galera.

More questions (which will lead to more discussion): How many clients are pumping data into the table?  How many rows are inserted at once?  How easy would it be to gather the rows and batch-insert them?  Is any Normalization occurring during the insertion?  Does it make sense to do the summarization in parallel with the insertion?  Did you intend to have clients writing to all 3 nodes?  Do you have some form of Proxy between the clients and the nodes?


can mysql user be blocked on the local server but have remote acces?

Yeah its possible that an user only have access from serveral locations. Did you try just google 'reset root password windows server'? Or just contact that guy who installed it. Is that a dumb idea?


Yes, it is.

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

See: Deleting Archived Redo Logs After Backups in non-CDBs

Or you can use FRA and configure an archivelog deletion policy:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK';

This way when the FRA is getting full, the database will automatically delete already backed up archivelogs.

See: CONFIGURE delalConf


How can I minimize impact of expensive queries in Database A on Databases B, C, D

I would recommend taking a look at the resource governor...
In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical IO, and memory.
By using Resource Governor, you can:

Provide multitenancy and resource isolation on single instances of
SQL Server that serve multiple client workloads. That is, you can
divide the available resources on a server among the workloads and
minimize the problems that can occur when workloads compete for
resources.
Provide predictable performance and support SLAs for workload
tenants in a multi-workload and multi-user environment.
Isolate and limit runaway queries or throttle IO resources for
operations such as DBCC CHECKDB that can saturate the IO subsystem
and negatively impact other workloads.
Add fine-grained resource tracking for resource usage chargebacks
and provide predictable billing to the consumers of the server
resources.

https://msdn.microsoft.com/en-us/library/bb933866.aspx


The hypothetical indexes are not actually there and can be ignored.  They don't contain any data and can be safely deleted.


You can copy a query results directly into a CSV file, e.g.

copy (
    select id, field1
    from (
        values (1, 'val1'), (2, 'val2')
        ) as v(id, field1)
) to '/data/test.csv' (format csv, header);

The file /data/test.csv:

id,field1
1,val1
2,val2

Use the function concat_ws() to get an output in expected format. Base types will be automatically converted to texts:

select concat_ws(', ', id, field1, date1, dec1)
from (
    values
        (1::int, 'val1', '2017-01-01'::date, 1.1::dec),
        (2::int, 'val2', '2017-01-02'::date, 1.2::dec)
    ) as v(id, field1, date1, dec1)

        concat_ws
--------------------------
 1, val1, 2017-01-01, 1.1
 2, val2, 2017-01-02, 1.2
(2 rows)


select data which is bigger than buffer pool size

Your scenario sounds exactly like what a mysqldump will do : Push every data and index page out of the InnoDB Buffer Pool. You can see that this is the case because when a mysqldump is in progress, the processlist will have something like SELECT /*!N SQL_NO_CACHE */ from .... See an example of the appearance of a SELECT from a mysqldump in MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */
It makes sense that any full table scan will push every data page from the table into the Buffer Pool, causing every old page in the Buffer Pool to get evicted. If you push 50G of data pages into your 40GB Buffer Pool, your Buffer Pool will contains the last 80% of that table because the first 20% would have been pushed into the Buffer Pool and then get pushed right out. I mentioned about 4 months ago (See my old post Is it safe to run parallel innodb single-transaction dumps of individual tables?)
SUGGESTION
If your goal is to keep Buffer Pool's contents looking the same before and after a huge query, there is only one thing you can do: Dump the map of the Buffer Pool to disk, run your big query, and restore the Buffer Pool from the map.
mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;
mysql> ( Your Big 50GB query )
mysql> SET GLOBAL innodb_buffer_pool_load_now = 1;

Please see MySQL Documentation on these options

innodb_buffer_pool_dump_now
innodb_buffer_pool_load_now
Saving and Restoring the Buffer Pool State


Instead of thinking about "type of join based relationship multiplicity" you should start thinking about the type of join based on what you want to happen if there isn't a corresponding relationship.  This is a much more useful place to start.

See this post by Jeff Atwood.  As you can see in the sample results next to his queries, inner joins reduce the number of results and outer joins allow more results in comparison.  Both situations can be useful.

Generally speaking inner joins should be preferred to outer joins due to performance reasons, but the driving force for which type of join needs to be what you want to happen if there isn't a match.  Also in my experience there is a general preference for left outer joins over right outer joins.

As with anything in life there are caveats and gotchas, but this is a much better place to start.

Another useful visualization: SQL joins are easy


How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

You already have a database, so you don't need the 'create database'.
Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set.
When you create a user, don't use that @localhost construct.  Your simple create user foo identified by bar is the correct syntax, but as @a1ex07 pointed out, you need to be attached to the pluggable database, not the container.  This is a drastically new architecture for Oracle, beginning with 12c.  You need to spend a bit of time with the 12c Concepts manual (see my link above), especially Part VI - Mutlitenant Architecture.


Security Risks with having Test Stored Procedures that have SQL Injection Vulnerability

One of the biggest IT security misconceptions people have is that there is little or no risk if the system is not public-facing.  There is certainly less risk, but there is still a huge amount of risk because many breaches are coming from within the attacked network.

Assume that your network is infiltrated by a hacker by gaining remote access to a workstation.  From this workstation, they can then launch attacks on all of your systems, not just the public-facing ones.  All of the millions of phishing attempts that are made each day would not be occurring unless some of them were successful, so you have to acknowledge that this type of infiltration does happen, and could happen on your network.

If a hacker were to discover this stored procedure, they could then add objects to the test server, which could possibly end up being promoted to the production server.  You have to assume that once you have bad code somewhere that it can spread.  The fact that there is a SQL injection vulnerability seems to indicate that your developers are not as concerned about security as maybe they should be.

How much damage could be done and how easily would depend on how many other security vulnerabilities exist.  It's certainly unlikely that a hacker would be able to find this stored procedure, but it's certainly possible if they are sniffing network traffic and your client connections are not encrypted.  And how secure are the servers that are calling this stored procedure?  You have to assume that the test code could be decompiled, which would allow someone to discover it and how it's used.

Another misconception is, "there's nothing important on this server."  That may be true, but it could be used as a bridge to servers that do have something important.  The current line of thought is that your network will be breached and there will be damage, so every system has to be locked down as much as possible to minimize the damage.

And as you mentioned, a less-seasoned developer might see this and think it's an acceptable practice, and then some emergency code that is not properly reviewed ends up in production.  So it certainly can be seen as setting a bad precedent.


How does name resolution work in Oracle 10g?

When you need to connect any database , you need to provide connection string. Oracle is the same. What you need to provide is following information basically.

Database Server Name or IP.
Database Name, Oracle SID or Service Name
Datapase Port
Username and password

First connection method is Local naming which uses Oracle Net. Before TCP/IP was standardized a lot of third party networking protocols existed. To overcome this problem, oracle used Oracle NET.  According to Oracle Documentation Local Naming : "Resolves a net service name stored in a tnsnames.ora file stored on a client". tnsnames.ora file contains 1-3 of above. Therefore you only need to provide (4) username and password. Below an example line of tnsnames.ora can be seen.

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=my-server) (PORT=1521)))

Second easy connect is same. You provide information contained in tnsnames.ora in command line. Here host = 1 , port (2), service_name = (3). As you can see it is same as first one. You give same information to connector as before. You may skip default information like port.

CONNECT username/password@host[:port][/service_name]

http://docs.oracle.com/cd/B13789_01/network.101/b10775/naming.htm#i476040

Third is Directory Server, LDAP Server like Microsoft Active Directory. Here you provide a key and directory server gives you back connection string. You use this connection string to connect to oracle.

Fourth one is same as Directory server , you only use other network services. As explained in oracle documentation.
"Configuring External Naming Methods

External naming refers to the method of resolving a net service name, stored in a non-Oracle naming service, to a network address."
According to NIS wikipedia: "Network Information Service, or NIS is a client–server directory service protocol for distributing system configuration data ...over time other modern and secure distributed directory systems, most notably LDAP, came to replace it."
As you can see, External Naming is same as Directory Server, only uses a different server technology (older) than LDAP.

http://docs.oracle.com/cd/B13789_01/network.101/b10775/naming.htm#i476040

These are four connection methods. I do think other than where do they get connection string information, they differ. Therefore easy connect should not have less performance than local naming.


SQL Server 2008 R2 Database Diagram Error Code

This can happen if your database was created in a previous version of SQL Server. You need to recreate the table [dbo].[sysdiagrams] or if you do not want to lose already existing diagrams try this:

ALTER table [dbo].[sysdiagrams]
DROP COLUMN [diagram_id]

GO

ALTER table [dbo].[sysdiagrams]
ADD [diagram_id] [int] NOT NULL IDENTITY (1, 1)


how to setup percona pmm monitoring of percona 5.7 in docker container

Can you show us the command you used to launch the Percona Server for MySQL 5.7 container? You need to ensure you are allowing access to 3306/tcp.

Otherwise from PMM Server perspective there will be nothing unique to your use case of running Percona Server for MYSQL from docker or via yum/apt-get, the mysqld_exporter will connect to 3306/tcp and collect MySQL metrics as needed.

You will be missing collection of node_exporter data so you won't have anything on your Disk Performance, Disk Space, etc graphs.


I don't want to have the error when I drop a table although the table doesn't exist

Unfortunately Oracle does not have anything like for example IF EXISTS in MySQL. You can do the above in a PL/SQL block, catch the exception, then do nothing with it.

begin
  execute immediate 'drop table continent';
  exception when others then null;
end;
/


How do I get the GRANT statements for a specified schema?

Use pg_dump

pg_dump -d myDatabase --schema mySchema --section=pre-data |
  grep '^GRANT'

If you want just the GRANT on the schema object.

pg_dump -d myDatabase --schema mySchema --section=pre-data |
  grep '^GRANT .* ON SCHEMA .* TO'


Converting string to date with timezone adjustment

How about a stored function?

DELIMITER $$

DROP FUNCTION IF EXISTS `ts_from_offset` $$
CREATE FUNCTION `ts_from_offset`(in_ts TINYTEXT) RETURNS datetime
NO SQL
DETERMINISTIC
BEGIN

-- Thu Oct 23 16:46:47 2014 +02:00

-- this function takes an input timestamp value with an offset formatted as above,
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone.  Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.

-- if you are taking the value here and stuffing it into a non-UTC DATETIME column, you need to have
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(ts_from_offset('input value'),'UTC','Your Desired Time Zone');

-- http://dba.stackexchange.com/questions/83898/converting-string-to-date-with-timezone-adjustment/84041#84041

DECLARE offset_string TINYTEXT DEFAULT NULL;
DECLARE date_string TINYTEXT DEFAULT NULL;
DECLARE offset_sign TINYINT DEFAULT NULL;
DECLARE offset_hours TINYINT DEFAULT NULL;
DECLARE offset_minutes TINYINT DEFAULT NULL;

SET offset_string = SUBSTRING_INDEX(in_ts,' ',-1);

SET in_ts = LEFT(in_ts, LENGTH(in_ts) - 1 - LENGTH(offset_string));

SET offset_sign = IF(SUBSTRING(offset_string FROM 1 FOR 1) = '+', -1, +1); # we need to flip the sign, to "back out" the offset to get a time in UTC
SET offset_hours = CAST(SUBSTRING(offset_string FROM 2 FOR 2) AS SIGNED) * offset_sign;
SET offset_minutes = CAST(SUBSTRING(offset_string FROM 5 FOR 2) AS SIGNED) * offset_sign;
RETURN CONVERT_TZ(DATE_ADD(DATE_ADD(STR_TO_DATE(in_ts,'%a %b %e %T %Y'), INTERVAL offset_hours HOUR), INTERVAL offset_minutes MINUTE),'UTC',@@time_zone);

END $$

DELIMITER ;

Example output...

mysql> SET @@TIME_ZONE = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00');
+---------------------------------------------------+
| ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00') |
+---------------------------------------------------+
| 2014-10-23 14:46:47                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

No warranty, but it seems like this should do the trick.


Permission denied when creating a database

If you are a Windows admin, you may create a sysadmin login for yourself following the procedure documented here: Connect to SQL Server when system administrators are locked out
Here it is in batch file form to run (from an elevated command prompt), for a default instance:
net stop mssqlserver
net start mssqlserver /mSQLCMD
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssqlserver
net start mssqlserver
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

or for a named instance:
net stop mssql$sqlexpress
net start mssql$sqlexpress /mSQLCMD
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Note that if you create the login for BUILTIN\administrators and you've got User Account Control enabled then you'll have to elevate to connect.  If you create a login for your individual account you won't need to elevate.


Is there any way to break out of the string and inject SQL without using a single quote in oracle?

Yes, it is possible to perform an SQL injection attack without supplying quotes in the parameter.

The way to do this is with an exploit to do with how numbers and/or dates are processed. You can specify at the session level what the format of a date or number is. By manipulating this you can then inject with any character.

By default in the UK and US, a comma is used to indicate the thousands separator in numbers, and a full stop for the decimal point. You can change these defaults by executing:

alter session set nls_numeric_characters = 'PZ';

This means that "P" is now the decimal point and "Z" is the thousands separator. So:

0P01

Is the number 0.01. However, if you create a function P01, the object reference will be picked up before number conversion. This allows you to execute functions on the database giving you increasing powers, as follows:

Create a basic "get by id" function:

create procedure get_obj ( i in number ) as
begin
  execute immediate 'select object_name from all_objects where object_id = ' || i;
end;
/

Also create a function P01 which does something undesirable (in this case just creating a table, but you get the idea):

create function p01 return number as
  pragma autonomous_transaction;
begin
  execute immediate 'create table t (x integer)';
  return 1;
end;
/

And we're good to go:

alter session set nls_numeric_characters = 'PZ';

SELECT * FROM t;

SQL Error: ORA-00942: table or view does not exist

exec get_obj(p01);

anonymous block completed

SELECT * FROM t;

no rows selected

No quotes anywhere, but we've still managed to execute the "hidden" function P01 and create the table t!

While this may be difficult to do in practice (and may require some internal knowledge/help), this does show that you can inject SQL without having to have quotes. Altering the nls_date_format can allow similar things to be done.

The original findings for numbers were by David Litchfield and you can read his paper here. You can find Tom Kyte's discussion of how dates can be exploited here.