Select ID from costumers that do not have contacts last 7 days and 2 times in the month

These are the customers that have not received an e-mail in the last 7 days AND have received less than two e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID NOT IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate>=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)>=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate>=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))>=2);

Conversely, here are customers that have receive an e-mail in the last 7 days OR have received two or more e-mails this month:

SELECT *
FROM customer AS c
WHERE c.ID IN (
    SELECT ID        --- the customer ID
    FROM c_contact
    WHERE contdate>=DATEADD(day, -31, SYSDATETIME()) -- for optimization
    GROUP BY ID
    HAVING MAX(contdate)>=DATEADD(day, -7, SYSDATETIME()) OR
           SUM((CASE WHEN contdate>=DATEFROMPARTS(
               YEAR(SYSDATETIME()),
               MONTH(SYSDATETIME()),
               1) THEN 1 ELSE 0 END))>=2);

Because I've put the two conditions in a single HAVING clause, the two queries only scan the c_contact table once, making them much more efficient. In particular, an optimal index for this type of query would probably look something like this:

CREATE INDEX indexName ON c_contact (ID, contdate);


IF condition in connect command in SQL*Plus

Use whenever sqlerror in your script before connecting.

$ cat 1.sql
whenever sqlerror exit sql.sqlcode
connect bp/bp
select * from dual;

$ cat 2.sql
whenever sqlerror exit sql.sqlcode
connect bp/123
select * from dual;

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 27 10:07:12 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> @1
Connected.

D
-
X

SQL> @2
ERROR:
ORA-01017: invalid username/password; logon denied

$


Decode everything that is not defined as 'internal' or 'Web' to 'external' (oracle)

Oh... I actually read the answer before i posted the question, but didn't realize it. ><

Just use the "else" variable at the end.

DECODE(supplier_id, 10000, 'IBM',
                10001, 'Microsoft',
                10002, 'Hewlett Packard',
                'Gateway')

the last variable 'Gateway' is assigned to everything that is not defined with a new value in the decode function. So in my question above i used 'External' after defining Internal and Web.

Answer and example form here:
Oracle / PLSQL: DECODE Function | techonthenet.com


Increasing timeout period for schema compare

You have to increase the query timeout allowed on the connection. Unfortunately there is no user-interface-exposed way to do this. You have to search the Windows Registry for the key "QueryTimeoutSeconds" and increase the value. I increased mine from 60 to 360 and that made my schema compare timeouts disappear in Visual Studio 2012 and Visual Studio 2013.


Continuously move data from server A to Server B while deleting the data in server A?

I don't think that your approach is wrong, but I don't have much information. Your question is clear, but this space is limited compared to the complexity of your system.

Definitely you shouldn't consider using Clickhouse for OLTP. Not only because DELETE and UPDATE are not (yet) supported, but also because this database is designed to provide good performance for analytics. It lacks more or less everything is needed to optimize an OLTP workload.

Kafka is a good idea? Maybe. But you won't have transactions, for example.

I suggest to try to optimize your MySQL environment first. Some very generic points - sorry if they sound obvious to you, but of course I can't know your skills lever:

Tune your configuration for speed, as far as I understand you can give up some consistency.
Use READ UNCOMMITTED wherever there is not a risk of conflicts. The benefits of this isolation level are greatly underestimated.
Instead of running many DELETEs, consider partitioning tables by time and periodically drop the oldest partition. This is much faster.
Consider switching to a storage engine optimized for write-intensive workloads, namely MyRocks or TokuDB.
Consider using multiple slaves for scaling reads.
Consider using multiple masters for scaling writes.
These servers can even be distributed geografically, if needed.
Don't consider Galera or InnoDB Cluster, as writes don't really scale.

Hope these hints are useful.


Identity column as clustered index bad idea?

I usually use an identity column as clustered primary key. However in some (rare?) cases this is not ideal because of the LastPageInsertLatchContention. This happens if a table is heavely filled with data. Because of the identity key all this INSERT's wants to write the last page of the table (index). So this page can be locked and the performance may be better with another solution.

See

http://dangerousdba.blogspot.ch/2011/10/bit-reversion.html
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx
http://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/

for details.


Yeah, this can be pretty bad! I've seen it go on for more than a minute.

There's no way to forecast it without testing it on your server, then checking the error log. You'll see messages for I/O being frozen, and I/O being thawed, along with timestamps.

There are some showstoppers you might wanna consider, though.

Microsoft recommends not doing more than 35 databases per snapshot.

  We recommend that you create a snapshot backup of fewer than 35 databases at the same time.

And cloud vendors like AWS who use snapshots set a hard limit of 30 databases per instance.

  You can create up to 30 databases on each of your DB instances running Microsoft SQL Server. The Microsoft system databases, such as master and model, don't count toward this limit.

Aside from data being frozen for a long time, you'll want to make sure that your snaps are transactionally consistent (dirty snaps can cause corruption), and that they play nice with your native backups.

For instance, most people will take daily snaps, but still take native log backups to meet RPO. You want to make sure that after you mount a snapshot, you can still restore log backups to get you to a point in time. Otherwise, you're losing all the data in between snapshots.

Of course, if you're in Simple recovery model, or your RPO is pretty wide open, it matters a bit less.


Previously I assumed that all users would connect to a central database. Here it sounds like you want to have a copy of the database local to each team.

The decision to have one or many instances of the database might affect which data is stored in each instance, but it should not affect the logical model that relates languages, chemicals, and the names of chemicals.

SIngle-instance solution

It should be clear that a central database would have to store all languages, all chemicals, and all names so that each team could use it. For example, the database would store a code for every chemical that is studied by each team, a code for every language spoken by each team, the name of every chemical in every language.

I assume that the database has a table like this to store the names of chemicals in each language:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode)
);

INSERT INTO ChemicalNames (
  ChemicalCode,
  LanguageCode,
  ChemicalName
)
VALUES
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

I assume that the user interface of the client application will display the names of chemicals. For a Spanish-speaker, the application would load the names of chemicals in this way:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';

For a French speaker, the application would load the names using the same query with a different language code:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'fr';

Multiple-instance solution

If you have multiple instances, you can simply treat each of them as if they were a central database. Each instance would have all the data needed by any team, even though only one team would use the instance.

The benefit of this approach is that you don't need to worry about which teams need which data - just give all the data to each of them.

They queries to extract data from the database would be the same. The American team would use the American database to get the English names. The French and Spanish names might not be useful to them, but their presence should do no harm.

You haven't made it clear what data you would consider to be 'unnecessary' in a multiple-instance solution. Do you want to limit the languages available to each user? Are you sure that you will never have a French-speaker working with the American team? Do you want to limit the chemicals available? Does each team work with a strict set of chemicals and never cooperates with other teams?

Can you demonstrate that the share-all approach would be impractical for the amount of data you have?

If you really have so much data, you could provide each team with an empty instance of the database and load only the data that each team requires. Load only the languages spoken by the team, load only the chemicals they study, and load only the names of the chemicals in the spoken languages.


Which indexes for joining a table onto itself on two columns

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.

ADDITION:

You create clustered index on only one column. Technically, you can do it on multiple, but the main goal to have it as short as possible.
If you created clustered index on account_id you DO NOT NEED any other indexes on that column.
I can guess that your performance improved because before there was no Clustered index and indexes were so bad that SQL decided to do full table scan instead.
Suggestion: read a book about Indexes, their differences and how they work.


PostgreSQL last_vacuum is null for some tables

There could be several reasons for autovacuum to not vacuum the table:

a table storage parameter is configured different from the default: enable_autovacuum = off, autovacuum_vacuum_scale_factor is absurdly high or similar.
Find out by looking at the \d+ output for the table in psql.

the rate of change on the table is so high that autovacuum just cannot keep up.
This is the case if you find autovacuum workers running for the table that never finish. The remedy is to lower autovacuum_vacuum_cost_delay.

data corruption in the table or its indexes make autovacuum fail with an error, so that it never finishes processing the table. Look at the log file to diagnose that.


Why do composite foreign keys need a separate unique constraint?

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN KEY that REFERENCES (a2, a1) unless there is a unique constraint on that (a2, a1) that essentially is redundant.

It wouldn't be terrifically difficult to add this as a feature:

  When there is a UNIQUE constraint on (a), then any (a, b, c, ..., z) or (b,c, ...a, ...z) combination is also guaranteed UNIQUE.

or the generalization:

  When there is a UNIQUE constraint on (a1, a2, ..., aN), then any (a1, a2, ..., aN, b1, b2, ..., bM) combination or any rearrangement is also guaranteed UNIQUE.

It seems that it hasn't been asked or it hasn't been considered high enough priority to be implemented.

You can always make a request - in the respective channel - for the feature to be implemented. Or even implement it yourself, if the DBMS is open source, like Postgres.


SQLServer - Detect who is connecting via an AG listener

[…] is there any way to verify this from the SQL side once they are connected?

Unfortunately there is not, most of the client side connection string settings are not persisted or sent across the wire as they are specific to the connection library - one such is the MultiSubnetFailover setting.

  These older generation applications have historically used the literal server name, but we're trying to migrate all over to listeners.

I'll point you to a post I made a while ago for someone that had a very similar issue and they wanted a way to track it down. It was very helpful for them, it may or may not be helpful for you but it's the closest you're going to get without asking to see the connection string of the application. The very minimum it'll show you if they are using the listener and which one. It gives some extra information so you can narrow down which client on the remote (note that this data can be spoofed, as I show).


Query performance differs greatly between a development setup and production

On an average of 3 executions, does the below code run any faster?

SELECT ProductId INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

SELECT DISTINCT ProductId, Onhand INTO NEWQuantityOnHand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand
SELECT ProductId, Onhand FROM NEWQuantityOnHand

DROP TABLE TempQTYOnHand
DROP TABLE NEWQuantityOnHand

That one is a small, small difference but it won't hurt to test anyway, right?

Also, there are articles that say pushing is slower than pulling, and a few say otherwise.

So, in the interest of experimenting, try both cases where you also invoke pushing from the linked server. Yeah, sadly- you'll have to create a stored procedure on the linked server to push the data, and then call it from your main query so it 'pushes' the data.


I am not familiar with Rocket.Chat.

Will dare to suggest to check that MongoDB collection's indexes with db.collectionName.getIndexes() and see if there is an index with the email address field as unique index. If so, then, drop it with db.collectionName.dropIndex("indexName").


You can add an additional CROSS APPLY to break out the rows in the way you require:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]', 'decimal(18,2)') QtyLastOrd,
        T3.lin.value('(./dIni/@Date)[1]', 'date') DIni,
        T3.lin.value('(./dEnd/@Date)[1]', 'date') DEnd,
        T3.lin.value('(./uni/@Qty)[1]', 'decimal(7, 2)') DIni
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin)
CROSS APPLY T2.lin.nodes('pLin') as T3(lin);

I had to guess the datatype for DIni, correct as required.


$ echo '\?' | psql > psql_backslash_help.txt
$ head psql_backslash_help.txt
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode


Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows

I cannot understand why the query is so complex.

Query text taken from published plan:

with Object1 as
(
select Object2.Column1 as Column2
from Schema1.Object3 as Object2
left outer join Schema1.Object4 as Object5
    on Object2.Column1 = Object5.Column1
where Object5.Column1 is null
)
insert into Schema1.Object4
select * from Schema1.Object3
where Column1 in ( select Column2
                   from Object1 )

Let's think.

What do you do in CTE? you select records from Schema1.Object3 which have no according record in Schema1.Object4.

What records are inserted in main query? those which were selected in CTE.

So CTE seems to be excess, and the query may be simplified to

insert into Schema1.Object4
select Schema1.Object3.*
from Schema1.Object3
left outer join Schema1.Object4
    on Schema1.Object3.Column1 = Schema1.Object4.Column1
where Schema1.Object4.Column1 is null

or

insert into Schema1.Object4
select Schema1.Object3.*
from Schema1.Object3
where not exists ( select 1
                   from Schema1.Object4
                   where Schema1.Object3.Column1 = Schema1.Object4.Column1 )

Please check - does I'm right?


Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

First: Make sure that the owner of /var/lib/mysql and its files and subdirectories is mysql user of mysql group

to do that run the following command:

chown mysql:mysql -R /var/lib/mysql/*

Second: change write permissions to rwxr-xr-x (755) for mysql directory, and all its files and subdirectories, by running the following command:

chmod 755 -R /var/lib/mysql/*

now start mysql service

service mysql start


Reason why SQL Max memory expansion seems slow when migrated to newer version of SQL server

This seems that memory allocation extension somehow is very slow on this server

More likely that you had query plans on SQL 2012 that required scanning more data than the plans on SQL 2017, and a side-effect of those scan-heavy plans is that they loaded most of your database into memory.
Or your disks could be slower, requiring more time (PAGEIOLATCH wait time) to to load the database pages into the page cache.


Oracle 11.2: Backup and recovery strategy based on full cold backup plus expdp

I would highly recommend taking weekly RMAN hot full backups and daily RMAN incremental backups as well as retaining archivelogs for at least 1 week.

The issue with using expdp/impdp is that it can and will create inconsistent snapshots of your schema during the hot backup process (unless you do a cold backup daily...which is not very practical). If you happen to lose your entire database and begin to recover using impdp, you're at the mercy of the exports to be logically consistent which is only possible if no inserts, updates, or deletes happened at the time of the expdp export. You'll get data, but you can't guarantee that all the tables will be consistent with one another.

Your weekly hot backup will provide you a new "ground truth" database backup that will have guaranteed consistency after applying enough recovery to push through the database changes after the end of the hot backup. You can either use archivedlogs or incremental backups to apply recovery.

A daily incremental backup will speedup mean-time-to-recovery in the event that you do need to make a restore happen. Incremental backups recover faster than archivedlogs. On their own, incremental backups are meaningless without some sort of full backup that preceded them.

To this statement:

  The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?

You're technically correct with the information you're providing. However, above I point out a critical flaw about the schema that makes this information irrelevant. That flaw is that you cannot guarantee data integrity with an expdp export without making it a cold export, and being cold is not advantageous for anyone.


Auditing which tables get joined most often

I don't know if you can differentiate joins vs straight queries, but you can use index activity (what version of SQL Server, by the way?) to get an idea of your "hot" tables.  This query should get you started, I use it to identify heavily fragmented or heavily used indexes, but it should still get you there.  I don't think it will work with heaps that well, but you really shouldn't have many of those (or any) without a good reason.

I've written this mostly myself but of course cribbed and borrowed bits and pieces from others work.  My thanks go out to everyone who has ever posted code to the internet.

/**
    Author: Jonathan Fite

    Index Analyzer

    Requires CONTROL permission in the database, VIEW DATABASE STATE and VIEW SERVER STATE

*/

/** Initialize Variables.

    @DatabaseID - The database we want to examine, must be specified, but defaults to the current database...
    @IndexScanLevel - The level of detail you want to scan to.
        - LIMITED - This is the default or NULL value, only looks at the parent level of the index.
        - SAMPLED - Detailed look at ~1% of the pages in the index. (Heaps and indexes smaller than 10K Pages use DETAILED)
        - DETAILED - full scan of all pages, heavy IO
    @MinFragmentation - If you want to see only indexes fragmented to a certain level.
    @MinPageCount - To limit to indexes that are over a certain page count.  Small indexes don't typically benefit
        from being defragmented.  A good number to start with is 100

*/

DECLARE @DatabaseID INT;
SET @DatabaseID = (SELECT DB_ID());

DECLARE @IndexScanLevel NVARCHAR(10);
SET @IndexScanLevel = 'SAMPLED';

DECLARE @MinFragmentation DECIMAL(4,2);
SET @MinFragmentation = 0.0;

DECLARE @MinPageCount INT;
SET @MinPageCount = 100;

/** Begin Work on the Query *********************************************************/

--Get Column List Information
DECLARE @ColumnList AS TABLE
    (
    object_id INT NOT NULL
    , index_id  INT NOT NULL
    , IndexedColumns VARCHAR(8000) NULL
    , IncludedColumns VARCHAR(8000) NULL
    );

WITH CTE_ColumnList AS
(
    SELECT IC.object_id
        , IC.index_id, C.name
        , IC.key_ordinal AS Position
        , DisplayName = C.name + CASE WHEN IC.is_descending_key = 1 THEN ' DESC' ELSE '' END
        , IC.is_included_column
    FROM sys.index_columns IC
        INNER JOIN sys.columns C ON C.object_id = IC.object_id AND IC.column_id = C.column_id
)
INSERT INTO @ColumnList
        ( object_id ,
          index_id ,
          IndexedColumns ,
          IncludedColumns
        )
SELECT DISTINCT
    C.object_id
    , C.index_id
    , IndexedColumns = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 0 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
    , IncludedColumns  = STUFF((SELECT ', ' + E.DisplayName FROM CTE_ColumnList E WHERE E.object_id = C.object_id AND E.index_id = C.index_id AND E.is_included_column = 1 ORDER BY E.Position FOR XML PATH('')), 1,1,'')
FROM CTE_ColumnList C;

SELECT DB_NAME(IPS.database_id) AS DatabaseName
    , SchemaName = S.name
    , ObjectName = O.name
    , O.type_desc AS ObjectType
    , I.name AS IndexName
    , IPS.index_type_desc
    , IPS.alloc_unit_type_desc
    , IPS.avg_fragmentation_in_percent
    , IPS.avg_page_space_used_in_percent
    , IPS.page_count
    , PageSize_MB = (IPS.page_count/128.0)
    , I.is_unique
    , I.fill_factor
    , I.is_disabled
    , I.has_filter
    , I.filter_definition
    , IUS.user_seeks
    , IUS.user_scans
    , IUS.user_lookups
    , IUS.user_updates
    , UsageActivityTotal = IUS.user_seeks + IUS.user_scans + IUS.user_lookups + IUS.user_updates
    , IOS.leaf_insert_count
    , IOS.leaf_update_count
    , IOS.leaf_delete_count
    , OperationalActivityTotal = IOS.leaf_insert_count + IOS.leaf_update_count + IOS.leaf_delete_count
    , LastAccessed = IUS_LastUsed.LastUsed
    , CL.IndexedColumns
    , CL.IncludedColumns
FROM sys.dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, @IndexScanLevel) AS IPS
    INNER JOIN sys.objects O ON O.object_id = IPS.object_id
    INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
    INNER JOIN sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id
    LEFT OUTER JOIN @ColumnList CL ON CL.object_id = IPS.object_id AND CL.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats IUS ON IUS.database_id = IPS.database_id AND IUS.object_id = IPS.object_id AND IUS.index_id = IPS.index_id
    LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DatabaseID, NULL, NULL, NULL) IOS ON IOS.object_id = IPS.object_id AND IOS.index_id = IPS.index_id
    LEFT OUTER JOIN (
                    SELECT S.database_id
                        , S.object_id
                        , S.index_id
                        , MAX(S.lastused) AS LastUsed
                    FROM sys.dm_db_index_usage_stats UNPIVOT (lastused FOR nlastused IN (last_user_seek, last_user_scan, last_user_lookup, last_user_update)) AS S
                    GROUP BY S.database_id
                        , S.object_id
                        , S.index_id
                    ) IUS_LastUsed ON IUS_LastUsed.database_id = IPS.database_id AND IUS_LastUsed.object_id = IPS.object_id AND IUS_LastUsed.index_id = IPS.index_id
WHERE ISNULL(O.is_ms_shipped, 0x0) = 0x0 --Exclude System Generated Objects
    AND ISNULL(I.is_disabled, 0x0) = 0x0 --Exclude Disabled Indexes
    AND ISNULL(IPS.page_count, 0) >= @MinPageCount --Only worry about indexes with worthwhile page counts
    AND ISNULL(IPS.avg_fragmentation_in_percent, 0.0) >= @MinFragmentation --Only display indexes that exceed fragmentation threshold
ORDER BY IPS.avg_fragmentation_in_percent DESC;


MySQL InnoDB data file directly on a disk slice (fixed-size)?

First of all, what are the entry types that reside in ibdata1 ? Four(4) entry types:

Table MetaData
Table Data Pages
Index Data Pages
MVCC Data

Whenever an InnoDB table experiences DDL, DML, or being used in a Transaction, all four of these types of entries are either read or written. Meanwhile, if innodb_file_per_table is disabled, all these entry types live in ibdata1. If it is enabled, only the Table MetaData and the MVCC Data would reside in ibdata1 while the Table Data Pages and Index Data Pages would reside in the database subfolder as a .ibd file.

That being considered, what would happen if ibdata1 were placed in another volume and symlinked ?

For starters, how does MySQL represent a table regardless of the storage engine ? As a .frm file. Where do .frm files live ? In the datadir. What's wrong with that ?

Here is an example:

Using the default datadir of /var/lib/mysql, let's use an InnoDB table called mydb.mytable.

With innodb_file_per_table disabled, everything would sit in ibdata1 (which you are proposing to symlink and send off to another data volume). For the table mydb.mytable, this is what you would have:

/var/lib/mysql/mydb/mytable.frm
Everything else about the table lives in ibdata1

Picture this now: You access the table, MySQL would first hit /var/lib/mysql/mydb/mytable.frm and then hit the data and index pages in ibdata1 for mydb.mytable. This would constantly be happening with every access of mydb.mytable. This cascading back-and-forth would somehow make things a little slower and you may not get the performance you were expecting by moving ibdata1 to some other data volume. In fact, the cascading effect would now be a factor of the number of InnoDB tables multiplied by two(2).

Imagine having innodb_file_per_table enabled. Now you would have a slightly different setup:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
MVCC data and Table MetaData would reside in ibdata1

This cascading would be a little worse because the cascading for table access would now occur among three files instead of two.

Here is one more scenario some have thought of: Instead of moving the ibdata1 to a different volume, how about enabling innodb_file_per_table and moving the .ibd files to one or more different data volumes ? The cascading effect would now be a factor of the number of InnoDB tables multiplied by three(3). Percona has expressed very good reasons for not doing this that you will find helpful.


Export each row of a table into a separate text file

You have to use dynamic sql, so something like this:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, notes from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      execute format('COPY (select notes from multi_text where id = %L) TO %L (FORMAT CSV)',  arow.id,files);
    end loop;
  end;
$$;

It is possible that a maliciously crafted value of arow.name could cause the value in "files" to climb out of the /tmp/ directory and write a file someplace else, which could lead to security breaches.  If arow.id is a text type (rather than an integer for example) it would be even easier for a maliciously crafted value of that to do so.


What are the security implications of the privileges needed by the Bluemedora Postgres account?

The big security problem here is that it doesn't revoke execution on its created functions from PUBLIC, which means that users other than bluemedora can use them, which is evidently not what is intended.  For pg_stat_statement this isn't really a problem, as it is located in a schema that no one else should have access to, but for explain_this it is in public schema and with PUBLIC execution rights.  And why is explain_this created in public in the first place?  If there is a reason to spread the functions over two schemas, I don't know what it is.  It looks like maybe explain_this was added to the script by someone who didn't know what they were doing.
Granting SELECT on all those catalog table is unneeded on most installations, as those already have SELECT granted for PUBLIC.  However, there is no guarantee this script will be run on "most installations", so it is reasonable to explicitly grant those permissions rather than relying on someone having not mucked with the defaults.  Most concerning to me would be the lack of schema qualification on those grants.

Based on the info present an attacker could find out the names of
users and superusers of the db, all the tables being used and with the
queries probably a good deal of the table structures. Which could then
be used to craft a targeted attack.

All of those things except for query text will be true for almost any compromised user, even with no special permissions granted.  Are you worried about social engineering attacks?


Reusing the same connection to read uncommited transaction

You can only see uncommitted data modifications withing the transaction that performed them, so your client code would have to use the same transaction for everything. This sounds a lot like you are going to end up with long running transactions, which is a very bad idea in database, particularly in PostgreSQL.
I recommend that you approach this in a different way. Add a column committed to the table that you set to TRUE or FALSE (or something similar). Then you don't have to keep transactions open for a long time, and other transactions that choose to do so can simply SELECT “uncommitted data”.


You can clear the log during the REORG process as its not done as a single transaction. The log bloating  depends on index fragmentation level and amount of work done.

Again, REORG is a fully logged operation on all recovery models.
https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


How can I "merge" two queries into a single one?

To fix the syntax of your second attempt:

SELECT @MyTag1 = CASE WHEN TagName= 'MyTag1' THEN TagID ELSE @MyTag1 END
     , @MyTag2 = CASE WHEN TagName= 'MyTag2' THEN TagID ELSE @MyTag2 END
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

(I'm not sure what @myValue was doing in there as it wasn't mentioned in the first example, so I've left that out.)

You may find this inefficient (it might scan, or at least do a partial scan) due to WHERE ... IN ... so watch the query plan. If that is the case then the sub-query option would be more efficient as you'll get two seeks instead.

When using the sub-query method if there might be many rows matching, add in TOP 1 so that the query engine will stop on finding the first value:

SELECT @myTag1 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag1'
            )
     , @myTag2 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag2'
            )

(it feels logical to assume that the query planner will be clever enough to know this is what you want, but I have seen it not do so in more complex examples and the explicit TOP 1 encourage a more efficient plan)

If you need many more properties pulling out than just the one or two, then you might find a PIVOT based solution more efficient (see Scott's answer for more detail on that). Again, check the resulting query plans for confirmation (and make sure you test with realistic data sizes/patterns or you may get a different plan and therefore performance profile in Dev/Test than in Production).

A question though: is there a reason why you need this to happen in a single statement? The sub-query version should be exactly equivalent to the two statement version and some may find the time simple statements slightly clearer to read. If you are concerned about data changing between the two statements, then wrap them in an explicit transaction (or the larger process if they are part of one) to try enforce consistency.


What is the etymology of the hstore datatype in postgres?

The name of the additional module hstore is derived from "hash store", because it was inspired by perl hash.
Oleg Bartunov and Teodor Sigaev - the authors of the module - say so in their presentation here:

  Hstore — key/value storage (inspired by perl hash)


Can I create a semaphore with locked transactions in MySQL?

Plan A -- If the task takes only a few seconds,

    START TRANSACTION;
    do all the task (possibly including FOR UPDATE)
    COMMIT;

Plan B -- If the task takes longer than a few seconds.  (Note:  The UPDATEs can probably be done with a single statement, hence no need for a transaction.)

    (with autocommit=ON)
    UPDATE ... to grab an item and announce who has the item
    do the task
    UPDATE ... to release the item

Plan C -- Read about GET_LOCK() and RELEASE_LOCK().  Note that there is a timeout on the GET.

lock_wait_timeout (which applies to Plan A) defaults to 50 seconds.  But I think you should not have transactions that last anywhere near that long.  Hence, Plan B.

Yes, Plan A might include

SELECT .. WHERE claimed=0 ORDER BY RAND() LIMIT 1  FOR UPDATE;

But notice the FOR UPDATE.  And there should be no case for failure.  You have limited the choices to claimed=0.  Caution:  if this is a huge table, the random selection will take time.


how to get child categorys based on categoryid

Using recursive CTE. Is this what you wanted?

sample data:

/*
CREATE TABLE #Table
(
    CategoryID INT
    , Name VARCHAR(MAX)
    , IsParent BIT
    , PCategoryID INT
);

INSERT INTO #Table
  (CategoryID, Name, IsParent, PCategoryID)
VALUES
  (1, 'IIT', 1, 0),
  (2, 'IIT Maths', 0, 1),
  (3, 'IIT Chemistry', 0, 1),
  (4, 'IIT Physics', 0, 1),
  (5, 'Education', 1, 0),
  (6, 'Learnig', 0, 5),
  (7, 'Writing', 0, 5),
  (8, 'Sports', 1, 0),
  (9, 'Cricket', 0, 8),
  (10, 'Algebra', 0, 2),
  (11, 'Basic Algebra', 0, 10),
  (12, 'Trigonometry', 0, 2),
  (13, 'Geometry', 0, 2),
  (14, 'Al Cat-1', 0, 10),
  (15, 'Al Cat-2', 0, 10),
  (16, 'Tri Cat-1', 0, 12);
*/

the actual query:

;WITH CTE (CategoryID, Name, PCategoryID, Level, SortOrder)
AS (
    SELECT T.CategoryID, T.Name, T.PCategoryID, 0 AS Level, T.Name AS SortOrder
    FROM #Table AS T
    WHERE T.CategoryID = 1    --Your CategoryID here! :)

    UNION ALL

    SELECT T.CategoryID, T.Name, T.PCategoryID, C.Level + 1,  C.SortOrder + T.Name
    FROM #Table AS T
    INNER JOIN CTE AS C
        ON C.CategoryID = T.PCategoryID
)
SELECT CategoryID, PCategoryID, Level, SPACE(Level*3) + Name AS NameIndented
FROM CTE
ORDER BY SortOrder;


Calculating the number of times a motive was cited over a given period

I think I might have figured this out using Postgresql's array_agg, but with a big downside, so I'd still be interested in seeing other answers.

SELECT
    period,
    ("array_agg"(ROW(motive_value, motive_name, sample_size)))[1:5] AS sample_list
FROM
    (
        SELECT
            period,
            motive_value,
            COUNT(motive_value) AS sample_size,
            motives."name" AS motive_name
        FROM surveys
        INNER JOIN motives ON surveys."motive_value" = motives."value"
        WHERE var BETWEEN 0 AND 10
        GROUP BY period, motive_value, motive_name
        ORDER BY sample_size DESC
    ) AS row
GROUP BY period
ORDER BY period DESC
LIMIT 6

The only down side is that, instead of columns, I get an array like this:

-----------------------------------------------------------------------------
| period | sample_list                                                      |
-----------------------------------------------------------------------------
| 2      | {"(1,\"Blocked\",1)", "(2,\"Delivery\",1)", ... }                |
| 1      | {"(1,\"Blocked\",1)", "(3,\"Forgotten\",1)", "(5,\"Other\",1)" } |
-----------------------------------------------------------------------------


how many cores uses gbak (firebird) on restoring?

As indicated in the tickets linked by Val Marinov in the comments (CORE-1365, CORE-2992 and CORE-3958), a restore in Firebird is currently entirely linear and will only use a single CPU core. There is nothing you can do to change this, short of sponsoring/funding an improvement to Firebird to get this into a next version with priority.


If count >= row then insert row (substitute for variables and else/if)

Start with getting all the required counts:

SELECT
  post_id,
  meta_value AS required_count
FROM
  this_table
WHERE
  meta_key = 'requirement'

Then derive another row set, which is the actual counts per post_id:

SELECT
  post_id,
  COUNT(*) AS actual_count
FROM
  this_table
WHERE
  meta_key = 'adder_id'
GROUP BY
  post_id

Now join the two sets as derived tables and filter the results on actual_count >= required_count to get the post_ids fulfilling the requirement:

SELECT
  req.post_id
FROM
  (
    SELECT
      post_id,
      meta_value AS required_count
    FROM
      this_table
    WHERE
      meta_key = 'requirement'
  ) AS req

  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act

  ON req.post_id = act.post_id

WHERE
  act.actual_count >= req.required_count

You can simplify the last query by joining the act set directly to this_table and slightly modifying the WHERE condition by referencing meta_value instead of required_count:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
WHERE
  -- the filter from the first derived table is moved here
  req.meta_key = 'requirement'

  -- the 'req.required_count' reference is no longer valid or needed;
  -- use 'req.meta_value' instead
  AND act.actual_count >= req.meta_value

We are almost there. Now you need to filter out the post_ids that already have a completed_date. This is called an anti-join (a self-anti-join in this specific case, as you excluding rows from a table based on a condition involving the same table). There are different methods of implementing an anti-join. Last I checked, the method that worked in MySQL better than other methods was the one involving a left join and an IS NULL check. This is how it would be implemented in your case:

SELECT
  req.post_id
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id

  -- part of anti-join implementation
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'

WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value

  -- part of anti-join implementation
  AND exc.post_id IS NULL

An outer join could be basically described as an attempt to join. So by using a LEFT JOIN you are attempting to join each req row (which is all the requirement rows) to each exc row (all the completed_date rows) on post_id. The IS NULL check in the WHERE clause is saying that actually you want the rows where there was no match.

Now that you have all the right post_id values, it only remains to add 'complete_date', NOW() to the SELECT list, and you are ready to insert new rows into the table:

INSERT INTO
  this_table (post_id, meta_key, meta_value)
SELECT
  req.post_id, 'complete_date', NOW()
FROM
  this_table AS req
  INNER JOIN
  (
    SELECT
      post_id,
      COUNT(*) AS actual_count
    FROM
      this_table
    WHERE
      meta_key = 'adder_id'
    GROUP BY
      post_id
  ) AS act  ON req.post_id = act.post_id
  LEFT JOIN this_table AS exc ON req.post_id = exc.post_id
                             AND exc.meta_key = 'completed_date'
WHERE
  req.meta_key = 'requirement'
  AND act.actual_count >= req.meta_value
  AND exc.post_id IS NULL
;


According to this

This error occurs when the slave server required binary log for replication no longer exists on the master database server. In one of the scenarios for this, your slave server is stopped for some reason for a few hours/days and when you resume replication on the slave it fails with above error.

When you investigate you will find that the master server is no longer requesting binary logs which the slave server needs to pull in order to synchronize data. Possible reasons for this include the master server expired binary logs via system variable expire_logs_days – or someone manually deleted binary logs from master via PURGE BINARY LOGS command or via ‘rm -f’ command or may be you have some cronjob which archives older binary logs to claim disk space, etc. So, make sure you always have the required binary logs exists on the master server and you can update your procedures to keep binary logs that the slave server requires by monitoring the “Relay_master_log_file” variable from SHOW SLAVE STATUS output. Moreover, if you have set expire_log_days in my.cnf old binlogs expire automatically and are removed. This means when MySQL opens a new binlog file, it checks the older binlogs, and purges any that are older than the value of expire_logs_days (in days). Percona Server added a feature to expire logs based on total number of files used instead of the age of the binlog files. So in that configuration, if you get a spike of traffic, it could cause binlogs to disappear sooner than you expect. For more information check Restricting the number of binlog files.

In order to resolve this problem, the only clean solution I can think of is to re-create the slave server from a master server backup or from other slave in replication topology.


Optimize query that only returns when the number of rows to be returned is less than an amount

I didn't really mean round trip all the way to the application. I consider a stored procedure one form of "application" in this context.

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

IF @c < 200
BEGIN
    SELECT cols FROM dbo.table WHERE ...;
END

If the application always expects either an empty or non-empty resultset, you can do this:

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

SELECT TOP (CASE WHEN @c <= 200 THEN @c ELSE 0 END) cols
  FROM dbo.table WHERE ...;

Yes, you have to read the table twice (in the case where there are fewer than 200 rows; TOP (0) should short-circuit), but I don't see a way to avoid that, unless you maybe dump rows into a temporary structure first (this will be useful I suppose in cases where your I/O is way faster than your network, but you'd still only be writing 201 rows at most, which is probably still way better than moving a lot more rows across the network):

SELECT TOP (201) cols INTO #f FROM dbo.table WHERE ...;

IF @@ROWCOUNT <= 200
BEGIN
  SELECT cols FROM #f;
END

DROP TABLE #f;

You could also try this, but I'm not sure the results will be any better than your original, it all depends if SQL Server sees that it doesn't have to materialize the CTE twice:

;WITH c AS
(
   SELECT cols, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM dbo.table WHERE ...
)
SELECT cols FROM c WHERE NOT EXISTS (SELECT 1 FROM c WHERE rn > 200);


You may use

WITH
cte1 AS ( 1st query ),
cte2 AS ( 2nd query )
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2 WHERE ( SELECT COUNT(*) FROM cte1 ) = 0

Of course, record structure of both subqueries must be the same.


MongoDb replica set: How do I verify data replication on other servers?

For querying secondary run the following command first. After running this, you can do all regular stuff on secondary.

rs.slaveOk()

To see the replication status, run the following command on primary. This will show you if the secondaries are up to date or lagging.

db.printSlaveReplicationInfo()

Hope this helps.


Performance tuning for multi-tenant database

Your comment:

  Possibly, but there's a lot of queries to one table of bookings with
  many people trying to get bookings at the same time. It's more complex
  than that, with queries needing to join other tables, with data being
  loaded in to these tables by other tenants at the same time, etc

So, it's a database! Great, let's answer some questions.

  Changing clustered indexes from the auto-incrementing int PK to being
  tenantID + id. (note all tables have tenantID column and already a
  non-clustered index on tenantID)

You know that Simpsons episode where they bring in lizards to catch pigeons, snakes to catch the lizards, and snake eating gorillas to catch the snakes? That's what you're doing here. The identity column clustered index is not the problem.

  Changing from default isolation level to Read Committed Snapshot so
  writes don't block reads and vice versa

This is a wonderful option that you should fully explore, but you have to make sure that you don't run into any race conditions with queueing type queries that depend on locking and blocking to assign work. Since your database is so small now (20 GB), you're in a great place to start exploring optimistic isolation levels. If parts of your code can't handle RCSI, Snapshot Isolation may be easier to approach because you can apply it to specific queries.

  Moving to faster physical disks - currently on a RAID 10 array of
  SSDs, looking at the Intel NVMe PCI-e SSDs

That's silly all the way around. You have a 20 GB database. Buy 32 GB of RAM. If it's all in memory, disk doesn't matter, and 32 GB of RAM is way cheaper than all that SSD. Though I'm totally on board with you grabbing some NVMes for tempdb, memory is the better approach.

Of course, no change means anything without some basic troubleshooting. Try a free script like sp_BlitzFirst (full disclosure, I contribute to this OSS) to figure out what your queries are actually waiting on.

Run EXEC sp_BlitzFirst @SinceStartup = 1 and look at your wait stats. Pay attention to the avg ms per wait column.

If you have any questions about those, post back here with them.

Update

  Cluster indexes to include tenantID - this was in an attempt to stop page/table locks due to activities from one tenant from affecting
  all other tenants. This seems recommended practice for multi-tenant
  DBs. Otherwise if a tenant inserts 4,000 rows or similar this seems to
  lock the table.

You likely won't get much different locking behavior with this over proper nonclustered indexes. If you really want one tenant to not bother another, you can try a partitioned view (or if you updgrade to 2016 SP1, actual partitioning), or separate tenants out to their own databases.

Lock escalation will occur regardless of if locks are against clustered or nonclustered indexes. I'm not sure what you've done for index tuning, but missing indexes can definitely be a factor if lock escalations are inappropriately frequent. Same full disclosure about contributing as above, sp_BlitzIndex can help you identify both aggressively locked indexes, as well as missing indexes.

  Disk IO vs RAM. We use SQL Mirroring with High Safety Mode as cannot lose a single booking/row due to a hardware error. Was under
  the impression all writes are written to disk, and the transaction
  won't complete till written to disk on the mirror. Networking between
  the two servers is sub 1ms and no contention. There's plenty RAM in
  the machine (64GB and doubling to 128GB in a few days), but does host
  a second multi-tenant DB (~40GB) for a different product.

This is the sort of important information that should have been in your original question. If the network is fine, then your two culprits for Mirroring latency will likely be transaction size or transaction frequency. Some of that might be mitigated by putting the log file on faster disks, but ultimately controlling the size or frequency of transactions is a smarter approach.

  Just to add, in the latest slowdown, a big chunk was deleting single
  rows from this table with queries like DELETE FROM tableX WHERE ID =
  x, and a lot of Transaction (Process ID 183) was deadlocked on lock
  resources with another process and has been chosen as the deadlock
  victim.

Theoretically two write queries won't deadlock, they'll just block each other until one is done. Are you using foreign keys or other constraints that touch other tables with cascading actions? Have you identified which queries they deadlocked with?

RCSI won't help with write query blocking, though Snapshot can if you're willing to spend a decent amount of time around error handling and query retries.

But again, this is all hand-wavey without wait stats or other diagnostic information.

Best of luck with everything.


Why would SQL Server stop excluding partitions appropriately?

In the second execution plan, the seek predicate has a CONVERT_IMPLICIT function in it. This means the wrong data type was input and SQL Server has to convert the data type to something else. In this case, it was expecting a DATE data type and something other than DATE was passed in... most likely a (VAR)CHAR or N(VAR)CHAR value. The fix would be to either explicitly cast/convert the value before submitting the query or fix the code to use the proper data type.

Here are some further resources on the matter:

Implicit_Conversions
Conversion Matrix
Performance Issues with Convert_Implicit


The short answer, I believe, is I don't think you can like that.

I believe you'll have to use the copy() SQL statement to load the file into a temporary table (DECLARE GLOBAL TEMPORARY TABLE session.tables) and use

SELECT temp_table FROM session.tables

in the IN clause.


Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?

If you were to compare the number of reads required in 100,000 lookups with what’s involved in doing a sort, you might quickly get an idea about why the Query Optimizer figures that the CIX+Sort would be the best choice.

The Lookup execution ends up being quicker because the pages being read are in memory (even if you clear the cache, you have a lot of rows per page, so you’re reading the same pages over and over, but with different amounts of fragmentation or different memory pressure from other activity, this might not be the case). It really wouldn’t take all that much to have CIX+Sort go quicker, but what you’re seeing is because the cost of a read doesn’t take into consideration the relative cheapness of hitting the same pages repeatedly.


How to change schema so that account_id reference is unique among 3 tables

Start with:

Create a ChartOfAccounts table with the Account code as Primary Key.
Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field.
Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transaction reversals (if used at all). This is necessary in order to generate T-Balances and Trial Balances properly from your Journal and Ledger
Create a Journal table with Primary Key: TransactionType, PostingDate, Account, SubledgerCode, IsDebit and minimum attributes of: Amount, CreatedDate, CreatedBy, DocumentReference
Design and spec a stored procedure (or type of routine suitable for where your business logic is located) for each type of transaction to be handled by the system. For your system these might be:

Ticket Purchase for Cash
Ticket Purchase on Account
Prize Payout in Cash
etc.

Please note that I am CGA, CPA in addition to being primarily a professional developer.

Update - Terminology:

A Journal is a chronological list of the details of all transactions of a given type, such as Cash Receipts, Cash Disbursements, Sales, etc.
A Ledger is a listing By Account of the aggregates of all transactions in a given time period.

It is occasionally necessary or expedient, when a wide variety of transaction types will be supported by the system (or to increase parallelism, as when many clerks need to be working at once Bob Cratchit style), to have multiple Journal files with different structure.

In a modern SQL Server system with only one Journal the Ledger could be defined as an Indexed View on the Journal. This would eliminate the need for either a trigger on the Journal to update the Ledger, or a batch-processing design.

Also, it is acceptable to have separate DrAmount and CrAmount columns in place of an IsDebit flag and single Amount column.


I am not sure if Access supports this but generally SQL allows you to solve this with the help of a derived table. If you use your present query as a derived table, you can reference all the computed columns exposed by it simply by their aliases, like this:

SELECT
  [School number],
  [Total Form and Sparring],
  [Total Weapons],
  [Total Combat],
  [Total Form and Sparring] + [Total Weapons] + [Total Combat] AS [Total Entries]
FROM
  (  /* this is the query that gives you most of what you need */
    SELECT
      [tourney report].[School Number],
      SUM( IIF( [tourney report].[Traditional Forms]   IN ('', 'Did Not Compete')
            AND [tourney report].[Sparring]            IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Form and Sparring],
      SUM( IIF( [tourney report].[Traditional Weapons] IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Weapons],
      SUM( IIF( [tourney report].[Combat Weapons]      IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Combat]
    FROM
      [tourney report]
    GROUP BY
      [School Number]
  ) AS derived

You can see that the aggregate results are both returned as their own columns and used to create another computed column, the total of all entries.

As you can also see, with the above query I have also taken the liberty of slightly rewriting your conditionals. I just wanted to make them more compact. But there, again, I am not entirely sure if Access will accept that syntax. If not, you can always revert the syntax to yours.


Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How about using with recursive

test view:

create view v as
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.


SQL Server 2008, database completely locked

Whilst not answering your question re. tracing locks by date range, all sorts of things can have this effect on the database.

I would first check whether the problem is environmental. Use Windows Perfmon to see what the system resources are doing at the time. For example, I have seen this type of thing occur when SQL Server checkpoint operations takes an overly long time to complete. The aforementioned issue can normally be solved by configuring a "recovery interval" of 1 minute or so.

To be sure of what's going on though, I would setup Windows Perfmon counters to monitor CPU usage, memory usage, and disk queue lengths of any disks on which your DB / Log files may be residing. In my experience (High OLTP) disk queue lengths should be no more than 2 for any disk. You can normally configure a separate server to log these counters to disk every 30 seconds or so in order for the monitoring not to adversely affect your DB server's performance. The number of concurrent connections and locks can also be monitored in this way.


ALTER DATABASE SET ARITHABORT not taking effect

Why are you comparing the database arithabort setting to the settings for a connection (which are not scoped to a database)?

If the client connections are explicitly setting ARITHABORT OFF for that connection, then that's their setting. The code you wrote might not be issuing set arithabort off explicitly; it could be the application, middle tier, data layer, ORM, driver, or any helper code you are inheriting from projects etc. You should figure out where this is being set, and remove whatever is setting it, because after compat 80 there is simply no reason to ever set this off.

Anyway, try this example, and notice where the explicit session setting overrides the database setting in every case:

USE Testing;
GO
SET ARITHABORT ON; -- default
ALTER DATABASE Testing SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1
SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
ALTER DATABASE Testing SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1

This was also true if I created the database, set it to arithabort off, created a login, added the login to the database, set that database to be the default database for the login, and connected directly to that database in the connection string. It still had the setting the application (in this case Azure Data Studio) defined for the session: arithabort on. And all of that in reverse (with the additional step of setting the initial session setting to off).

You could probably use a LOGON TRIGGER to hack sp_configure to set this option for all new connections, but step back for a second. What exactly are you trying to accomplish by attempting to turn arithabort on for all existing connections to this database, and why do you think the database setting should apply to all connections (existing and future), even if they set their own session setting? And why are you still using compatibility level 80?


Circumstances under which a windows server login becomes disabled

SQL Server never automatically disables any account. Only a human intervention can do that....

Probably you could put a request for your DBA to provide error logs for the timings you wanted....and you could find the info from the logs....


Try like this

WHERE....
AND...
AND (x.DATE>= @Startdate OR @Startdate IS NULL)
AND (x.DATE<= @enddate OR @enddate IS NULL)

Possibly with OPTION (RECOMPILE).

See Dynamic Search Conditions in T‑SQL by Erland Sommarskog.


how to get History of queries executed with username in SQL

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you don't trust.

Remember that DMV data gets reset if the DMV is cleared out, SQL Server is restarted, etc.

The closest you can get is using below query:

/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/
USE master
go
SELECT sdest.DatabaseName
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id


How to export own objects using expdp without special privileges?

Creating Directory Objects and Export/Import are two different operations. Directory Objects are owned by SYS.

  A directory object maps a name
  to a directory path on the file system. DBAs must ensure that only approved users are
  allowed access to the directory object associated with the directory path.
  If you are not a privileged user, then before you can run Data Pump Export or Data
  Pump Import, a directory object must be created by a database administrator (DBA) or
  by any user with the CREATE ANY DIRECTORY privilege.

  Schema Mode

  A schema export is specified using the SCHEMAS parameter. This is the default export
  mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of
  schemas, optionally including the schema definitions themselves and also system
  privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_
  DATABASE role, then you can export only your own schema.

Data Pump Export-Schema Mode


PostgreSQL server integrity after losing a tablespace

I can't describe this better than the manual does

  Warning

  Even though located outside the main PostgreSQL data directory, tablespaces are an integral part of the database cluster and cannot be treated as an autonomous collection of data files. They are dependent on metadata contained in the main data directory, and therefore cannot be attached to a different database cluster or backed up individually. Similarly, if you lose a tablespace (file deletion, disk failure, etc), the database cluster might become unreadable or unable to start. Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster.

Or this blog post

  You can’t just throw a tablespace away and have the rest of the database system keep on working.

  Most importantly, the write-ahead log (WAL) that provides PostgreSQL’s crash safety is stored in pg_xlog, and is shared across all tablespaces. PostgreSQL expects to be able to replay this log in order and without errors after a crash or shutdown. Until the log has replayed, the database is assumed to be in an unsafe state and connections will be refused


Postgresql-Server won't start after moving data directory

Verify the permissions on /main with

ls -l /media/daveknave/BIGFS/PGSQL/9.5/

Does postgres still have access to main?

Switch user to postgres with su - postgres and then navigate through the directory structure /media/daveknave/BIGFS/PGSQL/9.5/main/. Can you navigate past main into base?

Verify the file permissions beneath ..../base with the following comamnds:

ls -lR /media/daveknave/BIGFS/PGSQL/9.5/main/base > ~/postrgres_main_perm.log

and then

vi ~/postrgres_main_perm.log

If you find that the permissions are not correct in the log file, then run the following command to grant postgres all permissions to the directory ...main/base and all underlying sub-directories:

chown -Rv postgres:postgres /media/daveknave/BIGFS/PGSQL/9.5/main/base/

  Caution:
  Before you modify all permissions ensure the command works by replacing chown -Rv with something less destructive like ls -R or navigate down to the .../main directory and issue the shorter command: chown -Rv postgres:postgres base/. This will ensure you are applying the command to the correct directory.

Try restarting your PostgreSQL instance again.


Get INSERT/UPDATE/DELETE statements executed at specified time

If there is no auditing enabled to capture CDC(Change Data Capture) and no trigger defined on Insert/Update/Delete statement then, I don't think its possible to get details.

If you enable them now then, you would be able to capture them in the future. There are tonnes of article on how to configure auditing over SQL server. Full recovery can only help you in restoring database upto a specified point in time however that again depends how frequently you are taking log backup.

Hope above helps.


OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)

If you are referring to the base/pgsql_tmp, then you should be fine. But I don't speak from experience of having done that myself. The only gotcha is that you have to make sure that the location is accessible when your PostgreSQL server starts up.

(Ref.: Book PostgreSQL 9.0 High Performance, page 93).

The book in question refers to creating a simlink of the base/pgsql_tmp on the OS partition or other less "safe" partition (i.e. non-RAID or the like).


UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

Try something like this:

UPDATE viewOrderAlla
SET viewOrderAlla.tblOrder_verkligtid = 0
FROM tblTid
RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
WHERE viewOrderAlla.tblOrder_verkligtid <> 0
    AND tblTid.iTidID IS NULL
    AND viewOrderAlla.tblOrder_levdatum >= DATEADD(month, - 6, GETDATE())


At one point, this was in principle possible.  All you had to do in theory was to reset the current transaction ID, and you would see a previous state of the database.  Search for "time travel" in very old documents.

That, however, was before WAL, HOT, lazy vacuum, freespace map, virtual transaction IDs, and all the other goodies that were introduced among other things to work around the drawbacks of the append-only system for the majority of applications that don't need to look back into the past.  Now, the system is more like append-mostly-but-clean-up-old-stuff-as-quickly-as-possible.

So, if you want to look back at an old state of the database, you need to use point-in-time recovery, which is a log-based mechanism and has nothing to do with the nature of the storage management.


Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state

From the comments moved to chat, this note from the OP:

I just tried attaching it via GUI. As I selected the .mdf file, the SSMS identified the database consisted of 3 files (data, index, log), but somehow even though I was restoring WITH MOVE, the database details about file path were saying it wrong! So I just pointed them at the old log/data/index file and... The database is online.


DB design advice - shortcut foreignkeys vs long queries

The table structure you show looks fine from a database point of view, and it should work fine for an OLTP workload. Using nested loop joins, it is no problem to fetch a few rows by joining a number of tables.

If the schema is properly normalized, data modifications will be as localized as possible, and consistency is guaranteed.

Things become different when you want to perform analytical queries. Then joining many tables really hurts. This is why for OLAP workloads, you tend to denormalize the data and put them in a star schema or a similar structure. Such a “data warehouse” also usually doesn't hold the data in detail, but pre-aggregated in a way that makes the queries fast and the database not too large.

If you want to run both short transactions and analytical queries on the same database, it is hard or impossible to create a data model that fits both. Either you are ready to suffer with your analytical queries (possibly off-loading them to a streaming replication standby), or you create a second database with an analytical schema and an ETL process that regularly updates it from the live database.


Issues Setting Up SQL Server - Multi Server environment

It is an old post and I assume you've fixed the issue by now but for people having same problem;

Check the Sql server configuration manager and make sure the TCP/IP protocol is enabled for master and target servers.


Does not writing a checkpoint record damage anything?

Does the failed checkpoint write (by its self) cause any damage that I might need to address?

There would be no damage as such, what checkpoint does is it writes dirty pages from buffer pool to the disk reducing the amount of recovery time and amount of work SQL Server has to do after restart or crash recovery. In extreme case if database goes under crash recovery the amount of time taken to recover database would increase.

In your case it is good you found out that it was actually the index rebuild which produced some good amount of logs and eventually failed because of low disk space on log drive. This would just rollback the operation and would bring fragmentation as it was before the rebuild


sqlplus: ORA-01017 logon denied from one host but OK from another

At first make sure the database host is available ping host1.

Then you have to install a client and configure service name.

To install the oracle client correctly and be able to use it you have to:

Choose the "Administrator" type of the oracle client installation, not instant client, it provides you database administrator tools.
After your client installation run Net Manager (netmgr) to add a new service name. When you start testing the connection (Final step), make sure the "scott" user is available in your database.
Then run tnsping YOUR_SERVICE_NAMECONNECTION_NAME(Thanks @vapcguy for the correction) - tnsping is a tool that comes in the administration installation).
Run sqlplus sys/passwd@<YOUR_SERVICE_NAME> as sysdba or sqlplus sys/passwd@host1:PORT/<DATABASE_SERVICE_NAME>


Create failed for Availability Group 'AGN'

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with the set of servers that can run HRITS\BAK1.

An availability group doesn't give you much additional fault tolerance in the scenario you described because if Node1 or NODE2 failed, both failover cluster instances would be running on the same server.

To get around the error, you could reinstall the SQL Servers as standalone instances. Alternatively, if this design is what you really want, I believe the installer might work if you change the 'possible owner' setting for resources in each cluster group. This is possible with Powershell, for example:

Get-ClusterGroup -Name "SQL Server (BAK)"|Get-ClusterResource|Set-ClusterOwnerNode -Owners NODE2
Get-ClusterGroup -Name "SQL Server (BAK1)"|Get-ClusterResource|Set-ClusterOwnerNode -Owners node1

That said, this design might not make any sense for the aforementioned reasons unless it's just for a lab.


How would I undo or edit the last line of a multi-line query in mysql?

Type edit.

mysql> edit

It would open a vi editor. You can make any number of mistakes and undo them.
Once done with your multiline query, exit(:wq) out the editor.
Type ; to execute your query.

mysql> ;


Please use this with caution. Especially  reading registry via tsql is not allowed in many company policy, or recommended in many case. This script assumes you have enough SQL error log retention.  Especially the error log from the time you database restore was done.  I cannot remember if found this query from a open source or I wrote this. If you find it somewhere else let me know and I will add the source.

USE [MSDB];
GO
SET NOCOUNT ON;
DECLARE @sqlversion VARCHAR(10) ,
    @restore_finish_time DATETIME ,
    @reccount INT ,
    @maxerrorlogtoscan INT ,
    @Lognumber TINYINT;

--Checking how many error log is retained in this instance by reading registry
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',  @maxerrorlogtoscan OUTPUT;

--If registry value is null  set it to 6
IF @maxerrorlogtoscan IS NULL
    SET @maxerrorlogtoscan = 6;

SET @reccount = 0;
SET @Lognumber = 0;
SET @sqlversion = RTRIM(LTRIM(SUBSTRING(@@version, 22, 5)));

IF OBJECT_ID('tempdb..#dbrestored') IS NOT NULL
    BEGIN
        DROP TABLE #dbrestored;
    END;

CREATE TABLE #dbrestored
    (
      RowID INT IDENTITY
                PRIMARY KEY ,
      logdate DATETIME ,
      dbname VARCHAR(40) ,
      infotext VARCHAR(4000)
    );

IF @@version LIKE '%2000%'
    OR @@version LIKE '%2005%'
    BEGIN
        PRINT 'SQL 2000 and 2005 are not supported';

    END; -- SQL 2000
ELSE -- SQL 2005 or higher
    BEGIN

        WHILE @Lognumber <= @maxerrorlogtoscan
            BEGIN
                INSERT  INTO #dbrestored
                        EXEC xp_readerrorlog @Lognumber, 1,
                            N'Restore is complete',
                            N'The database is now available.';
                SET @Lognumber = @Lognumber + 1;
            END;

        UPDATE  a
        SET     a.dbname = SUBSTRING(b.infotext, 34,
                                     ( CHARINDEX('''', b.infotext, 34) - 34 ))
        FROM    #dbrestored a
                JOIN #dbrestored b ON a.RowID = b.RowID;

    END;

SELECT  AA.destination_server_name ,
        AA.destination_database_name ,
        AA.restore_start_time ,
        AA.restore_finish_time ,
        LEFT(CONVERT(VARCHAR(20), AA.restore_finish_time
             - AA.restore_start_time, 108), 8) AS [RestoreDur hh:mm:ss] ,
        AA.source_server_name ,
        AA.source_database_name ,
        AA.backup_start_date ,
        AA.backup_finish_date
FROM    ( SELECT    @@ServerName AS destination_server_name ,
                    #dbrestored.[dbname] AS [destination_database_name] ,
                    rs.[restore_date] AS restore_start_time ,
                    #dbrestored.logdate AS restore_finish_time ,
                    bs.[server_name] AS [source_server_name] ,
                    bs.[database_name] AS [source_database_name] ,
                    bs.[backup_start_date] ,
                    bs.[backup_finish_date] ,
                    ROW_NUMBER() OVER ( PARTITION BY [destination_database_name] ORDER BY restore_date DESC ) ROWNUM
          FROM      [dbo].[restorehistory] rs
                    JOIN [dbo].[backupset] bs ON rs.[backup_set_id] = bs.[backup_set_id]
                    JOIN [dbo].[backupmediafamily] bmf ON bs.[media_set_id] = bmf.[media_set_id]
                    JOIN #dbrestored ON #dbrestored.dbname = rs.destination_database_name
          WHERE     bs.type = 'D'
                    AND rs.restore_date < #dbrestored.logdate
        ) AA
WHERE   ROWNUM = 1
ORDER BY restore_start_time DESC;


How to know when a refresh of a materialized view has been completed

You you have said-

  After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results...

You may use ON COMMIT refresh instead of ON DEMAND BUT I just said may be, not sure about your requirement and implementation.

  ON COMMIT Refresh

  A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view.

The following query can be used to know when the MV was last refreshed.

SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select owner, mview_name, last_refresh_type, last_refresh_date
    from all_mviews;

Sample output:

USERNAME         M_VIEW_TEST        COMPLETE   02/02/17 01:01:19

You can also use v$mvrefresh dynamic performance view to know which MV is being refresh.

select currmvowner, currmvname from v$mvrefresh;

References:
ALL_MVIEWS
ON COMMIT Refresh


How do I get more details on a full text catalog error?

According to a post in the MSDN forums, SQL Server 2005 (and probably earlier) contained a flaw that results in the error.  This was reported by "A Fellows" in his problem resolution back in 2010.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9626c8f8-9171-4625-a9c0-ef72628066b9/fulltext-error-indexing-rows-with-jpgs

In part he says: "Well microsoft looked into it and with SQL2008 it works properly, with SQL2005 it doesn't ... (but) ... SQL2008 full text has been rewritten and works as it should."

If you are still on SQL Server 2005 and still stuck with the problem, then he also offers a workaround of sorts for SQL Server 2005.


Restrict access to new database by default

I think you cannot do this using only the tools PostgreSQL offers.

On one hand, there are no default privileges, as you noticed. The REVOKE command issued on a template DB (template1 in most cases) has no effect on the created DB. Last, the event trigger mechanism does not support shared objects (including databases).

I think the best way would be to create one DB for them each, and confine them to that one from wherever you create the users currently.


Like:
SELECT Al.id AS Aid,
       Al.name,
       C.id AS Cid,
       C.item
FROM
  (SELECT A.id,
          A.name
   FROM A
   WHERE A.owner = 1
   ORDER BY A.id DESC
   LIMIT 3) AS Al
INNER JOIN B b ON Al.id = B.Aid
INNER JOIN C c ON B.Cid = C.id

fiddle


How to concatenate old string and new string in mysql procedure

CONCAT will return NULL if any of the inputs are NULL. This suggests that your select thainame... is returning NULL in at least one iteration.

CONCAT_WS will skip NULLs. Try using that instead and see what it gives you. Note the first argument is the desired separator, so if you want to duplicate CONCAT() but with NULL skipping just set that to an empty string, CONCAT_WS('',AttrTH,TmpAttrTH);

This should help you debug the problem. Alternately you can also use COALESCE on your SET TmpAttrTH if you want to set a default in cases where the result might be NULL. If you use COALESCE to set a default empty string you shouldn't get NULL back from the subsequent CONCAT.

Presuming the thainame is in Thai script, you may very well have collation/character set issues, which could be why it is returning null in the first place, your comparison may be failing. Check that all the columns you are comparing have the same character set/collation.

@Rick James is also entirely right that looping in SQL should be avoided, and likely is not necessary in the first place. So ideally rewrite the whole thing without the loop. But the above hopefully will help you get to the bottom of where the problem is.


Automatically reject certain expensive query statements

The main options are the query governor cost limit and Resource Governor.
The former is a bit limited because it applies to all queries and limits them based on estimated cost, which can be very inaccurate.
Further, SQL Server Management Studio issues SET QUERY_GOVERNOR_COST_LIMIT 0 when connecting (with SSMS options set to default). This overrides the instance-level setting and so renders this option pretty ineffective.
The better option is Resource Governor (if it is available to you) when combined with:
Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2014, 2016 and 2017

When Trace Flag (TF) 2422 is enabled, and if a request exceeds the maximum time set by REQUEST_MAX_CPU_TIME_SEC, then the request is aborted. Additionally, you may receive an error message 10961 that resembles the following:

The request has been aborted because its CPU usage exceeds the maximum configured limit for the workload group.

It isn't possible to manage this with Extended Events.
Your other options were described by Dominique Boucher:

Why not simply talk to those people who are running those query and told them not to? [...] Remove access from those who run the queries after you told them not to and that should do it. Otherwise, [...] you could have a job that run all the time and check running queries and kill the one you don't what to have running. They will still start to execute but technically, the end user will get errors all the time so they may eventually give up.

Erik Darling had a similar suggestion:

If I knew the query text ahead of time, I'd probably just set up a while loop in an Agent Job to find when those queries are executing, collect the SPID, and kill the session.


SQLIO is technically deprecated - it's been replaced by the newer utility Diskspd. However, the same basic answer applies.

If you look at an existing server and guess how much storage throughput it's USING, then look at the counters:

IOPs - Physical Disk Reads/Sec and Writes/Sec
IO size - Physical Disk Read Bytes/sec divided by Reads/sec (and same for writes)
Latency - Physical Disk Avg Sec/Read and Sec/Write

However, this only reflects what your current storage is able to deliver, not what your server would rather have. Think about it as drinking water through a small straw. If I ask how fast you want to drink, and I measure your consumption through that tiny straw, it doesn't tell me how fast you could actually drink if you weren't constrained by such a small straw. Instead, I need to hook you up to a pipe (or straw) with unlimited throughput, and then measure how much you actually consume - and then use that to size the storage you need. (Or want, anyway.)


Mongo import error: exception:read error, or input line too long (max length: 16777216)

For data migration (or backup) between MongoDB servers, you should be using mongodump and mongorestore (binary backups) rather than mongoimport/mongoexport (text backups).

Backups (and restores) of sharded collections need to be done through a mongos.

There are several reasons to use mongodump/mongorestore:

binary backups preserve type fidelity in your BSON documents; mongoexport to a text format can lose type information because JSON can only represent a subset of the BSON types
you can dump/restore full databases (rather than being limited to a single collection)
information on index definitions is included so they can be recreated by mongorestore

If you're trying to do a live migration by dumping/exporting records, this is going to be problematic because your dump will not represent a single point in time if there is write activity on the collections you are dumping.

You definitely want to disable the balancer on the source cluster before taking your mongodump, to ensure there are no active data migrations.

You may also want to look into a live migration tool like hydra.


Duplicate Ips for cluster nodes causing backup issues

We have a four node cluster (SQL Server 2014 on Windows 12) and we recently setup heart beat network on the nodes following a best practices source after experiencing some cluster instability issues.

Health checking goes across any available interface, some interfaces have metrics lower or higher than others but the loss of a single interface should be handled assuming there are other interfaces available. Just for future reference there is no heartbeat network, specifically I believe since 2008 came out.

  Ever since a couple of secondary nodes are returning multiple IPs in the DNS.

You've found the issue, duplicate IPs on the network. Look at all of the adaptors and figure out which are duplicated... or better yet, run the cluster validation wizard and have it do the heavy lifting for you!

  We have removed the entries from the DNS server for both the nodes multiple times but the IPs of the heartbeat network (the IPs are supposed to be non-routable) keep popping ultimately returning two IPs for the same node and this is causing issues with our backups.

This doesn't make much sense to me... if the underlying issue is that there are duplicated IP addresses (assuming IPv4) then the fix shouldn't be deleting records from DNS, that'll just delay the inevitable which would be that they register back with DNS at some point in the future. The fix would be to identify which adaptors and interfaces are improperly configured.

Non-routable doesn't mean it can't register or otherwise talk with anything else. It obviously is getting registered in DNS and can talk to other servers.

So, yes, this seems like it's working as it's been configured though configured incorrectly.

  I have checked the settings on all four nodes and they are the same in the network adapter properties. What am I doing wrong?

My guess is there is a duplicate IP address (tongue-in-cheek). Give the cluster validation wizard a run and choose the networking tests, that should give some actionable output.

  PS: In addition, I have also changed some settings in the TCP/IPv4 properties such as removing the DNS server addresses and unchecking the 'register this connection's address in DNS'.

That's probably not a good thing to do and could have undesirable side effects... like the cluster not working at all. Again, you know the root issue so instead of managing the symptoms fix the root cause.


How to create clustered index on 100 GB table

If you've got a short-term need for disk space, one option would be to:

Shrink tempdb temporarily, freeing up as much space on that drive as seems safe.
Create a secondary data file for the DB the table is in on the tempdb drive.
Add the clustered index to the table.
Shrink the secondary file by migrating all data out of it.
Remove the secondary file.
Make sure the tempdb file is allowed to grow to its former size.
Rebuild indexes in the table's DB (the removal of the secondary file will have caused some fragmentation).

NOTE: as others have suggested, I'd only do this after things like temporarily removing non-clustered indexes from the table in question. This in particular will allow the addition of the clustered index to go faster, as the non-clustered indexes would all have to be rebuilt anyway (with a clustered index in place, the index key is used to locate the rows in the table itself).

That's actually another point - how wide is the key on the clustered index? If you do have non-clustered indexes, and the key on the clustered index is significantly wider than the pointer into the heap was, then the non-clustered indexes will consume more space after the clustered index is created.

If the cluster key consists of several columns, or even one large column (say, a varchar column with an average length of 25 or more), you may want to consider a surrogate key instead (usually a monotonically increasing value, for best INSERT performance.


How MySQL Queries and Questions status variables are calculated?

I answered an old question like this Feb 25, 2014 : Questions since MySQL Startup

In that post, I described how everything MySQL executes to retrieve info is a question. I also mentioned how a billion questions is no big deal (See my ServerFault post 1 billion mysql queries in 24 days? Can something be wrong?)

Here is the difference

Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

You should go through the MySQL Documentation and read up on all Server Status Variables.


MySQL remote connection: Public vs. private IP address

The explanation here is a straightforward one: You can't bind a listening socket to an IP address that your machine's IP stack isn't aware of.

In AWS (whether EC2 Classic or EC2/VPC) as in many network environments these days, your external IP is not configured in your machine -- it's mapped through an external device (router, firewall) to your machine's private IP via NAT (network address translation).

You can confirm that your server itself has no idea about the external IP:

 $ ifconfig

The simplest solution, of course, is binding to 0.0.0.0 unless you have a specific reason not to.

http://en.m.wikipedia.org/wiki/Network_address_translation


You may have to do the following
NEWROOTPASSWORD="Cha@g3m3"
service mysql restart --skip-grant-tables --skip-networking
SQL="update mysql.user SET password=PASSWORD('${NEWROOTPASSWORD}') WHERE user='root';"
mysql -ANe"${SQL}"
service mysql restart
mysql -uroot -p${NEWROOTPASSWORD}

If you get the mysql prompt, CONGRATULATIONS !!!
Then, go change the password properly. Don't use the exclamation point or semicolon for a password as they mean something to Linux.
MySQL Documentation for this situation: http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html
Give it a Try !!!


Grant postgres superuser to linux account

Create the user on the OS

# Identify yourself as root
su -

# Create the user who will have access to a postgres database
useradd mypostgresuser

# Add a password
passwd mypostgresuser

Give local users access to postgres

You need to locate the data directory for your postgresql install, i.e. where you have created the database files. They are typically located in /var/lib/pgsql/data
The value for your install might be available in the environment variable $PGDATA

# Make sure that local users can access postgres
cat /${PGDATA}/pg_hba.conf

# this was the default setting on my 8.4 install
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all

if you make any changes, reloading postgres will be necessary

/etc/init.d/postgresql reload

Or as postgres

pg_ctl reload -D ${PGDATA}

Now connect to psql as postgres

# Create the user in postgres
postgres=# create user mypostgresuser;
CREATE ROLE

# Give that user access to a database
postgres=# grant all privileges on database mytestdb to mypostgresuser;
GRANT

Test the connection

# Identify yourself as mypostgresuser
su - mypostgresuser

# Connect to the database
psql -d mytestdb


Display all days of the month with/out values

Here are the joined queries:

SELECT d.empID, DATE_FORMAT(d.date_field,'%e') as day_of_month, d.date_field, o.am_in, o.am_out, o.pm_in, o.pm_out
FROM (
SELECT e.empID, emp_day,
MAX(e.am_inA) as am_in,
MAX(e.am_outB) as am_out,
MAX(e.pm_inC) as pm_in,
MAX(e.pm_outD) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_inA,
  CASE WHEN am_pm='am' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_outB,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_inC,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_outD
  FROM attendance
  WHERE MONTH(`time_stamp`) = MONTH(NOW()) AND YEAR(`time_stamp`) = YEAR(NOW()) AND empID = 152
) as e
GROUP BY empID, emp_day) as o
RIGHT OUTER JOIN
(SELECT date_field, `employee`.empID
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
   ) as AB, `employee`
   WHERE MONTH(date_field) = MONTH(NOW()) AND YEAR(date_field) = YEAR(NOW()) AND empID = 152
) as d
ON o.emp_day = DATE_FORMAT(d.date_field,'%e') AND o.empID = d.empID
ORDER BY d.date_field;

And your fiddle: http://rextester.com/VRTYM92242


Some things to check (for future readers, since we now know what happened here), off the cuff:

Case sensitivity: on a case sensitive instance, the metadata needs to be an exact match; TableName <> TABLENAME <> tablename.
Collation: in some cases you may have names in your objects that were created with a different collation than you're using (and in general it is just a good practice to always explicitly define parameters involving metadata as nvarchar and prefix all string literals with N, since metadata is always stored as nvarchar).
Correct database/server: on the new server, when there are no results, it could mean that you've left the database context set to master, or tempdb, or your default database, or the database of the most recent query window before this one. Can't hurt to double-check all connection string settings... several times the solution ends up being the person is connected to the right database, but on the QA or staging server.
Correct schema: when you use a name like TableName with certain functions, procedures, or DMVs, it may look in the wrong schema or even return the wrong object from a different schema. You may also not have correct permissions on the right schema. Always explicitly specify the schema (even when it's dbo) and, if that doesn't help, get someone with sysadmin to try your queries.
Permissions: for many metadata queries, you need more than SELECT rights, you also need VIEW DEFINITION. Again, have someone with sysadmin try your queries and see if they get different results. If they do, you're on the right track!


How do I find I.O. statistics (by index) for queries in progress

You can see some information about the indexes that are in use right now using the DMV sys.dm_db_index_usage_stats. An example:

declare @dbid int
select @dbid = db_id()
select db_name(db_id()) as [database]
, objectname=object_name(s.object_id)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes =  user_updates
from sys.dm_db_index_usage_stats s join sys.indexes i  on i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id  and s.database_id = @dbid  and i.index_id <> 1
order by reads

(sample code from MSDN).

Reads and writes columns will grow as soon as the specific index is used for data gathering.


Metrics for monitoring master-slave replication

In the primary side, you have to use the view pg_stat_replication and compare differents fields to see the drift. The function pg_xlog_location_diff give the drift in size :

select   pid, client_addr, state, sync_state,
         pg_xlog_location_diff(sent_location, write_location) as write_lag,
         pg_xlog_location_diff(sent_location, flush_location) as flush_lag,
         pg_xlog_location_diff(sent_location, replay_location) as replay_lag
from pg_stat_replication ;

You can use the lag in monitoring tools, as gauge, to monitor your replication.

If a standby is missing, you have to check in the log's standby why.

If you use replication slots, you have to check the pg_replication_slots view.


SQL Server table and view show different values

When a view is created it is bound to the metadata it needs in order to execute.

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

That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."

The outdated metadata binding causes the surprising "wrong" results.  Using schema-bound views is one way of avoiding the problem.

Read the link that Aaron Bertrand posted:

https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list


How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It

This should do the work. I tested it in my test environment, works for me.

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TutorialDB]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
GRANT CREATE TABLE TO [TestLogin];
GO

Create a Login on the server with public role.
Then Create a user on the database that they need to access.
Grant create table to that user.


Multiple charsets and Collations for a Multinational Database

If the data is the same (same tables/columns) and the only change is that now there is the possibility of more languages being used, I'd say go for UTF-8. Force it as the default charset on all the table definitions (new ones at least), and set it in your config to be used for client connections that don't specify a charset.

You have not specified what DB type you are using (MySQL, SQL Server…etc) so I am trying to be as general as can be :)