Postgres Row Level Security and BDR I am implementing a multi tenant web app using postgres. I want to use Row Level Security (RLS ) for data separation. RLS is available from PG version 9.5. However, at the same time, the HA / DR teams plan to use BDR ( Bi directional replication) for the HA solution. It seems that BDR is not yet supported in 9.5.

What can be done in such a scenario ?
Is it possible to get BDR on 9.5 ?
What other replication strategies provide the same benefits as BDR?

Yes, schemas is also an approach, but I am already using that for tables which have less data. I want RLS for tables that will eventually get enormous, so replicating them in different schemas wont be ideal.

One way to achieve BDR (in the PostgreSQL) use is pgPool, which has multi-master replication with failover.
For a complete tutorial, please use the official product site or wiki even at the addresses below:

pgpool

http://www.pgpool.net/mediawiki/index.php/Main_Page

https://wiki.postgresql.org/wiki/Pgpool-II


Why did the MySQL Storage Engine Change on Restore in one scenario? In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all referential integrity were lost.

What could have caused this incident? My production database is MySQL Server 5.1.

This brings to mind something I have done a dozen times (fortunately with small datasets less then 10GB). When restoring a mysqldump to a new server, you must make absolutely sure the InnoDB settings on the new server is identical to the InnoDB settings.

For a new server that you have opted to restore a mysqldump to, run this command:

SHOW ENGINES;

This will display all storage engines available in in the MySQL instance. Here is a sample from a server running MySQL 5.5.9:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Notice that InnoDB has DEFAULT in the Support Column. There are four possible values for the Support column:

YES
NO
DEFAULT
DISABLED

One day, a server I was restoring to had mismatched configurations on the innodb_buffer_pool_size. Because I had set innodb_buffer_pool_size=512G instead of 512M (obvious typo), InnoDB would come up DISABLED. When loading the data into the new server, every CREATE TABLE defaulted to MyISAM upon execution. I didn't notice anything until I saw dozens of DB Connections that were waiting to write to the same table, which was typical of heavy writes against MyISAM. The solution was simply to start over with the correct InnoDB settings and reload.

MORAL OF THE STORY

Always make sure you can restore data to the same configuration as specfied in /etc/my.cnf. If the new server has less diskspace and/or less RAM than the server you mysqldump'd, they carefully configure /etc/my.cnf to the maximum settings that make the most sense for the new server you are restoring to.
Always run SHOW ENGINES; to make sure every storage engine is functioning.
Always read the error log and verify any anomalies with regard to startup, InnoDB crash recovery, fulltext stopword list declarations, MyISAM repair options, the amount of memory requested for buffers and DB Connections.
Always make sure ib_logfile0 and ib_logfile1 are the same size as the one specified in the old server's /etc/my.cnf.
Always use dev and staging DB servers to load old backups and test usability.


Empty table being returned with plpgsql and EXECUTE USING Why this function return an empty table (while running the plain query from command line returns different values):

CREATE OR REPLACE FUNCTION get_area_values (_tbl regclass, _column text, indicator_id integer) RETURNS
TABLE (indicator integer, postcode text, date date, value double precision) AS $$
        BEGIN
                execute format(
                'WITH ad AS (select distinct area, district from postcode_lookup)'
                || ' select $1 as indicator, ad.area as postcode, t.date, sum(t.%I) as value'
                || ' from %I as t'
                || ' join ad on t.postcode = ad.district group by ad.area, t.date', _column, _tbl)
                USING indicator_id;
        END
$$ LANGUAGE plpgsql;

I had even tried to use execute with || without any luck.

I think you want RETURN QUERY EXECUTE ... USING context doesn't matter: EXECUTE .. USING won't work from psql, it will not append the result to the functions return value.

This returns an empty table, and should probably trigger a warning when you create such a function.

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  EXECUTE 'SELECT $1' USING 42;
END
$$ LANGUAGE plpgsql;

This returns a table with one row, with one int column a with a value of 42,

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT $1;' USING 42;
END
$$ LANGUAGE plpgsql;

See also,

PL/pgSQL - SQL Procedural Language - Control Structures


Replace a materialized view in Postgres I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it.

  ERROR:  cannot drop materialized view latest_charges because other objects depend on it

It also appears from the documentation that the REPLACE keyword isn't valid for a materialized view.  Is there any shortcut aside from dropping all dependent objects and rebuilding each one?

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.

When you do so, I can only recommend two small things:

Use DROP MATERIALIZED VIEW blabla CASCADE to get a list of all dependent objects
Do the drop and recreation of all dependent object in one transaction.


Is it possible to add a DB to an Always On AG and setup replicas at different time? I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency.

I will add a huge DB (10TB) to the AG, so I would like to be able to just get it added to 2 replicas on the LAN in a first stage, and some days later, to get it added on the 3rd (remote) replica after manually copying it on-site.

Is this a supported scenario?
If so...What would be the recommended approach?

Adding the database to all nodes at the same time is not an option unfortunately.

I would recommend you either restore/attach the database to the primary node on the SQL server. From there you could add it to the AG but not have it replicate. Ultimately, to get it on the 3rd(remote) node - you'd likely have to break the AG and re-add the database (if you're allowed the downtime) after it's already copied to the remote node. This would allow it to sync up.

So this approach would mean you'd have 3 stages:

Stage 1: 10TB Database on Primary Node restored/attached - not in AG
Stage 2: 10TB Database added to AG. Remove remote node from AG.
Stage 3: Copy 10TB on remote node. Rebuild AG to include remote node. Allow AG to sync and catch up with data.

If this isn't clear let me know more so I can help you (such as OS/SQL version).


ISNULL() OR Is NULL in UPDATE statement I have to run an UPDATE statement on live online database with 500k records. I want to know which statement will run faster:

Update Table set REC_ID = isnull(REC_ID,'')

Update Table set REC_ID = '' where REC_ID is null

i am using Microsoft SQL Server 2012

Those queries won't do the same thing.

Update Table set REC_ID = isnull(REC_ID,'')

This one will update each record and if REC_ID is NULL it will set it to ''.

Update Table set REC_ID = '' where REC_ID is NULL

This one will only update records containing a null value in REC_ID, and set it to ''.
While both of them will end up giving the same result, the second one will be executed on less records (except if every REC_ID is NULL), it should be faster.


Sql not exist, returning 0 rows and should return one After some time I really don't know what is going wrong in here. (SQL is not my cup of tea I would say, but still need to figure this out hehe)
I have 2 tables, in different schema, that I need to pass the information from the "primary" into the "secondaries" if the "secondaries" don't have that line, so that's why I'm trying to use the not exist.
this is the query that I'm using:
--Select all from the primary
select
    *
from
    (select
         *
     from
         [server\sqlexpress].xptoPrimary.dbo.CcArtigos
     where
         Tipo_Doc in ('INE','INS')
         and Armazem=2
         and data_mov >='01-07-2018') as tprimary
where not exists
      --That not exist in the secondary
      (select *
       from
           [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary
       where
           tprimary.cod_art = tsecondary.cod_art
           and Tipo_Doc in ('INE','INS')
           and Armazem=2
           and data_mov >='01-07-2018')

So from what I did understood from what I did find online (https://www.tutorialgateway.org/sql-not-exists-operator/), I'm trying to select all from the xptoPrimary that doesn't exist on the xptoSecondary BUT it returns 0 rows and acording to the site, don't see what is the problem with the query, the logic from the site:
SELECT [Column Names]
FROM [Source]
WHERE NOT EXISTS (Write Subquery to Check)

The test db's the primary have 91 lines and the secondary has 90 lines so I should get the row from the primary that the secondary doesn't have but I get 0 rows :/
Edit:
select *
from [server\sqlexpress].xptoPrimary.dbo.CcArtigos
where not exists
  (select * from [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tsecondary)

Edit 2
select * from [server\sqlexpress].xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE NOT EXISTS (SELECT
                      *
                  FROM
                      [server\sqlexpress].xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE
                      tPrimary.cod_art = tSecondary.cod_art
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);

I don't know table schema of both tables, but assuming they have the same schema you can do something like this:
SELECT
    *
FROM
    xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE
    Tipo_Doc in ('INE','INS')
    AND Armazem=2
    AND data_mov >='01-07-2018'
WHERE NOT EXISTS (SELECT
                      1
                  FROM
                      xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE
                      tPrimary.cod_art = tSecondary.cod_art
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);

That is, for each row of tPrimary table, there isn't a row in tSecondary that match the same criteria.


SQL 2016 Always Encrypted, options appear in one environment, not the other We have two different environments that are replicas of each other - DEV/STG and PROD.  We recently upgraded both environments to SQL Server 2016 using the same upgrade process (we're on AWS, so it was as simple as spinning up a new instance), but are unable to find any SQL Always Encrypted options, configurations, menus, or wizards in our DEV/STG environment (single server, two instances).  I checked our PROD server and there they are, Always Encrypted Key, Encrypt Columns, etc.

Is there a configuration or module I need to install in our DEV/STG environment to allow it to utilize the Always Encrypted functionality?

I see this question is a few months old, but I wanted to tell you about a great update from Microsfot if you've missed the news.

On November 16, Microsoft announced the release of SQL Server 2016 SP1. Along with the normal things found in Service Pack releases, they also included CREATE OR REPLACE functionality. And, really important for your question, they've made quite a lot of the features that you can develop for available in editions other than Enterprise.

Most critical for you here is the availability of the Always Encrypted feature in SQL Server 2016 Standard and even Express starting in SP1 of SQL Server 2016.

You can read about that here on Microsoft's blog post.


SQL Server - Add signature to stored procedure by certificate does not work I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2

To avoid granting calling login/user permissions to do table updates, I try to use code signing

Created certificate in the master database, and created login from this certificate
Then I've created users for this certificate-mapped login, in Database 1 and Database 2, and granted them permissions to do table updates

When I try to run below t-sql:

use [Database 1]
add signature to MyProcedure
by certificate MyCertificate

It throws:

  Msg 15151, Level 16, State 1, Line 168
  Cannot find the certificate 'MyCertificate', because it does not exist or you do not have permission.

Then I try

use [master]
add signature to [Database1].[dbo].MyProcedure
by certificate MyCertificate

I am in a sysadmin server role, but it throws:

  Msg 15151, Level 16, State 1, Line 168
  Cannot alter the object 'Database1.dbo.MyProcedure', because it does not exist or you do not have permission.

Question:

How can I properly add signature to my stored procedure in Database 1, by certificate that is in the master database ?

You need to copy the certificate from master to your user database. For example:

-- Copy master certificate to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'your encryption password',
              'your decryption password')

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''your encryption password'',
          ENCRYPTION BY PASSWORD = ''your decryption password'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

EDIT

If the certificate is encrypted with the database master key instead of a password, omit the decryption password from CERTPRIVATEKEY (to decrypt with the source database DBMK) and omit the ENCRYPTION BY PASSWORD clause of CREATE CERTIFICATE (to encrypt with the target database DBMK). The password in this case is used only to encrypt the private key in transit.

-- Copy cert to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = CERTENCODED(@cert_id),
        @private_key varbinary(MAX) =
           CERTPRIVATEKEY(@cert_id,
              'your encryption password');

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

--create certificate in target database encrypted with DBMK
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE your_certificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''your encryption password'');'
PRINT @sql

EXEC YourUserDatabase.sys.sp_executesql @sql;
GO

I'll add that for only cross-database permissions, one doesn't need to create a login from the certificate. Create a certificate in a user database, copy the cert to other databases as needed, create a user from the certificate in each database and grant permissions to the cert user, and finally sign the proc with the certificate.

A server level principal is needed only when server-level permissions must be granted.


Run SQL Server agent job in a particular resource governor pool I read Considerations for Writing a Classifier Function on MSDN. The only way I see possible is to create a separate user account for each resource governor pool and to classify sessions based on that username.

Is there a better way?

Yes, your classifier function can identify the job that's running through APP_NAME(), which will actually contain a representation of the job_id. This means your jobs don't have to run as different users, but the classifier function does need to be able to identify jobs by id.

See the white paper I wrote back in 2008 for an example, in the section "Limiting Parallelism for Intensive Background Jobs." In that case I simply created a table with the mapping to the set of job_ids I cared about, and set the workload group in those cases.


Peak load upgrade and downgrade SQL Server 2017 Standard  SQL Server 2017 Enterprise We are currently running on SQL Server 2017 standard edition and for higher traffic weekends like Mothers day we want to go on SQL Server 2017 enterprise version and during non peak hours we want to come back on Standard. We run on AWS EC2 images of respective versions. My question is

Can we just take the backup of 2017 standard and restore on 2017 enterprise? I mean do we have to do an in place upgrade and not a backup and restore
Similarly can we do a restore from 2017 enterprise to 2017 standard during non peak hours? I mean it is mandatory to do an in place downgrade

We tried the backup restore option and running into memory issues
What is the best approach?

Answers to your questions below, this doesn´t necessarily mean that I think it is a good idea, it seems quite a high risk approach to me.

Yes restoring a standard edition db to enterprise edition just works as long as it is not on a later version than the destination server.
Restoring an enterprise edition to a standard edition works AS LONG AS you have not started to use enterprise features

you can query sys.dm_db_persisted_sku_features
see
https://www.brentozar.com/blitz/enterprise-edition-features/


Help me to RANK correctly in Access I have such a table:
I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top.

Please advise me SQL code for MS-ACCESS.

Variant.

Create a module. Copy the below code into it:

Public Function CustomRank(Optional key) As Integer
Static CurrentNumber As Integer
If IsMissing(key) Then
    CurrentNumber = 0
Else
    CurrentNumber = 1 + CurrentNumber
End If
CustomRank = CurrentNumber
End Function

Modify your query.

Add a field CustomRank(some_field) AS Rank into output list.

some_field must be some NOT NULL field (the best way - primary key, if PK is composite then use any field from primary key expression). If LEFT JOIN is used then it must be a field from the most left table.

If GROUP BY is used in the query then use any NOT NULL field from GROUP BY expression.

Any NOT NULL expression from output list is safe too.

Add the condition OR (CustomRank() > -1) to your WHERE conditions if no GROUP BY is used, or to your HAVING conditions if GROUP BY is used.
Add proper ORDER BY.

PS. This solution is NOT safe for parallel queries execution.

In your particular case the query will be:

SELECT sourcetable.id, sourcetable.NAME, sourcetable.V1, sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8, CustomRank(sourcetable.id) AS Rank
FROM sourcetable
WHERE (CustomRank()>-1)
ORDER BY sourcetable.V1 DESC , sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8;

Output:


Is a join optimized to a where clause at runtime? When I write a query like this...

select *
from table1 t1
join table2 t2
on t1.id = t2.id

Does the SQL optimizer, not sure if that is the correct term, translate that to...

select *
from table1 t1, table2 t2
where t1.id = t2.id

Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?

Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.

Those will usually collapse to the same thing internally. The former is the one you should always write, IMHO. More importantly, why does it matter? They're identical in terms of execution plan and performance (assuming you don't mess it up, which is easier to do with the lazy, old-style syntax).

Here is proof using AdventureWorks that there is no CROSS JOIN and filter going on.

The explicit join:

The implicit join:

Look, ma! Identical plans, identical results, no cross joins or filters seen anywhere.

(For clarity, the warning on the SELECT operator in both cases is a cardinality-affecting implicit convert, nothing to do with the join in either case.)


Making collations uniform across multiple SQL Server 2008 databases I have been assigned to try and make collations uniform across databases. Currently, it's a mess, different collations everywhere.

This is mostly solved by using collate database default in queries and stored procedures.

Bit of a broad question, but what would be a good approach for making it all uniform. Has anyone got tips / or a T-SQL script?

what would be a good approach for making [collations for columns] all uniform?

Well, "good" is highly subjective. The first thing to consider is the scope of what you believe should be changed. How many databases are affected? How many columns in each database would be changed? And how many total string columns are there? Does the collation need to change at the database level for any of the databases? Does the collation need to change at the server level? Does the issue you are trying to fix revolve around column-to-column comparisons / joins, or is it column-to-variable / literal comparisons, or both?

If most columns have the same collation but a small percentage have different collations, was that intentional such that changing the collation would change the behavior and/or performance of some queries? Meaning, it is one thing to want to change SQL_Latin1_General_CP1_CI_AS to Latin1_General_100_CI_AS as that is at least the same code page, culture (i.e. linguistic rules), and sensitivities (accent-sensitive, case + kana type +  width insensitive). But it is quite another to change Latin1_General_100_CI_AS to Latin1_General_100_BIN2 or Latin1_General_100_CS_AS, or French_100_CI_AS, or even to SQL_Latin1_General_CP1_CI_AS.

If it is a small number / percentage of columns to change, AND assuming that the "incorrect" collations were not intentional and purposeful, then you should probably change them one at a time via ALTER TABLE ... ALTER COLUMN. You might need to drop and recreate dependencies if any exist, such as FKs, indexes, functions referencing those columns, etc.

If it is a lot of columns, and even more so if there is a need to change database and/or instance collations, then you could look into the undocumented approach that I documented here:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are pros and cons to that "undocumented" approach, but it does greatly reduce the workload of making the changes as it side-steps the dependency issues. There are a few gotchas that will affect some folks and not others, but only one that can't be fixed easily enough: changing code pages on a CHAR / VARCHAR / TEXT column1. However, I am guessing that it is fairly rare that someone would be changing collations that also change the code page (but if that is the case, then you can't use this undocumented approach without first making sure that you aren't using any of the characters that would be changing). Still, it changes ALL2 collations, which is generally desired in these cases, but does require that you might need to change a few back manually if there are some non-standard collations being used that are there for a good reason.

1 Code page change happens when the code page used by the old collation is different than the code page used by the new collation. If the old and new collations are in the same culture / locale (i.e. Latin1_General, French, Japanese, etc), then there shouldn't be any chance of that happening. And even going from Latin1_General to French isn't a problem as they both use Code Page 1252. But, going from Latin1_General to Japanese is a problem as they use different code pages. You can determine the code page using the COLLATIONPROPERTY built-in function. For example:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS', 'CodePage') AS [Latin1_General],
       COLLATIONPROPERTY(N'French_100_CI_AS', 'CodePage') AS [French],
       COLLATIONPROPERTY(N'Japanese_CI_AS', 'CodePage') AS [Japanese];

returns:

Latin1_General    French    Japanese
1252              1252      932

2 Almost all: collations of User-Defined Table Types are skipped.


Deploying Ola Hallengrens scripts - Index Optimization Never before I used Ola Hallengren Scripts .

I am planning to deploy the Ola Hallengren scripts for Index Optimization for the first time. I need to deploy them in PROD so I want to be more careful. Anyhow will first test in Dev environment. But please help me understanding the important things to keep in mind before deploy OH Index optimization scripts.

I copied the media from link and below commands :

As I mentioned I never deployed the OH scripts before.

Can anyone who already deployed and successfully using the OH Index Optimization scripts guide me on below:

Important parameter Values which I need to modify?
Where will these commands create the SP and related Command Execute and Command Log, all in Master Database or MSDB?
Series of code execution - Which code I need to execute first?
What is command Log here?
We are with AOAG set up on SQL Server 2014. Please guide me keep this in view.

NOTE : We are using AOAG 2014.

Please help me keeping this in mind. I can first test in Dev and later deploy in PROD.

The parameters will depend on what tasks you want, how often and when you want to run them. This is really up to you.
The default location is master db, you can put them elsewhere. But i dont see much benefit in doing that.
Run the script to create the command log table first.
The command log table can be used by the scripts to log events.
Availability groups are supported.

Most of these questions are answered in Ola's FAQ. I highly reccomend you read it.

Personally, I find the full maintenance solution is too much. I prefer to just call the tasks I want using the examples provided.

But it's worth running it in dev as it will give you working examples of how to create the agent jobs. There is no "right answer" with this stuff, you really need to experiment and select what is right for you.

One thing I struggled with is that if you want to back up to a network address you need to grant permission to the account that SQL Server uses. Therefore it should be a domain account.


Infix search on large table when majority of rows can be filtered out We have a Postgres table, which looks like this:

+-----------+---------+---------+---------+
| CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 |
+-----------+---------+---------+---------+
|     11234 | aabcdef |   mnfng |  lflgbm |
+-----------+---------+---------+---------+
|     11234 |   xdfef | nfnvnaz | fngnnva |
+-----------+---------+---------+---------+
|     84590 |  pflghh |   otngp | cignral |
+-----------+---------+---------+---------+
|    ...    |   ...   |   ...   |   ...   |

CLIENT_ID is an indexed column.

We need to run a query which will return all the rows that belong to a specific CLIENT_ID and will have a particular substring in some of the columns VALUE_X.

Naive query for client id 11234 and substring ng would look like:

SELECT * FROM tbl WHERE client_id = '11234' AND (value_1 LIKE '%ng%' OR
value_2 LIKE '%ng%' OR value_3 LIKE '%ng%')

The problem is that our table is large. It can possibly contain 10+ millions of rows. Rows can be first filtered by CLIENT_ID - then the subset of rows matching will only have about 100k+ rows.

Our tests show that when the table is relatively small, the database does a sequential scan of the whole table. It's not very fast, but it's reasonably fast (about 1s). When the table gets larger, the database first filters the rows with a specific CLIENT_ID (which is indexed) and then does sequential scan of the rest. This is super slow (about 30s+).

Is there any way how we can speed this up, only by using Postgres (we don't want to introduce Elasticsearch etc.)?

We are currently using Postgres 10.9 in production, but we can possibly upgrade to the latest version, if there is significant performance gain (our staging tests show there are not).

Thank you.

Note that there are already threads on speeding up infix searches, but this case is more specific - we can possibly filter many of the rows out otherwise.

Thank you all for your input. My colleague just solved the problem by using Trigram index on the VALUE_N columns. Now the queries run under 10ms.


Result set of SELECT statement in LOOP in MySQL Stored Procedure I have the following SP:

CREATE PROCEDURE vessels_near_anchorage_test()
   BEGIN
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT field1, field2 FROM table1;
      OPEN cur1;
      read_loop: LOOP
           FETCH cur1 INTO arg0, arg1, arg2, arg3;
           SELECT * FROM table2 WHERE c1=arg0 AND c2=arg1;
      END LOOP;
      CLOSE cur1;
END;

When I try to result set from this SP in the application server, I get DataError: 1329 (02000): No data - zero rows fetched, selected, or processed. I want to understand how the SP is really being executed and what is causing this error.

In the SP above, the SELECT statement is inside the loop. It returns rows in some iterations while there may be no result in some iterations. When I run this in PhpMyAdmin, I get 850 rows, but it's entirely possible that the last iteration does not return any row.

When a SELECT statement is executed in a loop like that, can I combine results from all iterations and output as a single result set available by the time the loop completes?
For the application, is it only the result set from the last iteration in the loop made available which in this case has no rows?

CURSORs are rarely worth using.  Try to get rid of them.  In your example:

SELECT b.*
    FROM table2 AS b
    JOIN table1 AS a  ON b.c1 = a.field1
                     AND b.c2 = a.field2

Anyway, when you execute multiple SELECTs inside a Stored Proc, you get "multiple resultsets".  Some interfaces either can't handle such, or need different coding.

And you had an error:  The cursor SELECT fetched 2 columns but the FETCH expected 4.

Coding questions should be addressed to stackoverflow.com


Adding CASE statement to flag percentage available less than 5% as red ELSE green USE [TEST]
GO

CREATE PROCEDURE [dbo].[SQLDBA_DatabaseSpaceReport]
AS
BEGIN
DECLARE @dMaxLogDate Datetime = (SELECT TOP 1 LogDate FROM
SQLDBA_PS_Get_DbaDatabaseSpace ORDER BY LogDate DESC)

DECLARE
@html NVARCHAR(MAX),
@table VARCHAR(MAX)

SET @html = --HTML layout--
            '' +
            '
Prod Databases with less than 10%
            space available
' +
            '' +
            '' +
            '
' +
            '
'+
            'SqlInstance	' +
            'Database	' +
            'File Name	' +
            'FileType	' +
            'Used Space MB	' +
            'Free Space MB	' +
            'File Size MB	' +
            'Percent Used	' +
            'Auto Growth	' +
            'Auto Grow Type
';

   SELECT @table = CONVERT(nvarchar(max) ,
(SELECT
td = [SqlInstance],
'',
td = [Database],
'',
td = [FileName],
'',
td = [FileType],
'',
td = CAST([UsedSpaceMB]as varchar(30)),
'',
td = CAST([FreeSpaceMB]as varchar(30)),
'',
td = CAST([FileSizeMB]as varchar(30)),
'',
td = CAST([PercentUsed]as varchar(30)),
'',
td = CAST([AutoGrowth]as varchar(30)),
'',
td = [AutoGrowType]

FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace D
INNER JOIN [TEST].[dbo].[Environment] E on D.ComputerName =
E.VirtualName AND E.ServerUse IN ('PROD','DR')
WHERE ( FileName NOT LIKE '%temp%' and FileName NOT LIKE 'SP%' and FileName
NOT LIKE 'Share%' )
AND LogDate = @dMaxLogDate and AutoGrowth > 0
ORDER BY [PercentUsed] desc

FOR XML PATH(N'tr'), TYPE));

SET @html = @html + @table + CHAR(10) +
      N'
';

IF LEN(@HTML) > 10

BEGIN

--print @HTML

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Tester@TEST.com',
@body_format =  'HTML',
@body= @HTML,
@subject = 'Alert!! Prod Databases With Less Than 10% Space Available!!',
@profile_name = 'SQL_DBA'

END

END

Something like this: (Not 100% sure about the code below or where to add it)

CASE WHEN PercentUsed > 95 THEN
'' +
CAST([PercentUsed]as varchar(30)  +'	'
ELSE
'' +
CAST([PercentUsed]as varchar(30)  +'	'
END +
'
'
FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace

In this instance replacing td = CAST([PercentUsed]as varchar(30)), with something like:

td = ' 95 THEN '#FF0000' ELSE '#40C211' + '">'
   + CAST([PercentUsed]as varchar(30))
   + ''
   ,

is probably what you are looking for. Or if you need to use font tags instead of more modern styling options (perhaps because some of your recipients have truly ancient email readers) then edit as needed.

Your inner query is using the FOR XML hack* to concatenate everything into a string, the column names forming tags (in this case they are all td) and the PATH parameter meaning each row gets wrapped in
/
. For more information on forming XML document fragments (or in this case, HTML document fragments) this way see the official documentation such as https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server and for various tutorials on using this for string building more generally search for "TSQL FOR XML concatenation" or similar.

* I call it a hack as you are not actually returning an XML typed result, which is what the feature is designed for, it is being used here in a way that makes it behave like a string concatenation aggregate, the most common use for FOR XML in my experience, see articles like https://sqlblog.org/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-youre-looking-for for notes on this and other techniques to achieve the same result.


Mongodb Secondary Node not recovering Secondary node of mongodb cluster has entered in Recovering state and it's not coming out of it. Below is what I see in log. I know one way to fix this issue is to reinitialize secondary node by deleting data directory and restarting secondary. But I don't want to try that option as I have 2 tb of data and primary is getting write continuously.

  2017-06-13T12:02:14.946+0000 I REPL [replication-12569] We are too stale to use mongodb.prod.mcse-reporting-olap.services.dal1.prod.walmart.com:27017 as a sync source. Blacklisting this sync source because our last fetched timestamp: 59351d47:3357 is before their earliest timestamp: 593f8b97:5b11 for 1min until: 2017-06-13T12:03:14.946+0000 2017-06-13T12:02:14.946+0000 I REPL [replication-12569] could not find member to sync from 2017-06-13T12:02:14.948+0000 E REPL [rsBackgroundSync] too stale to catch up -- entering maintenance mode 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] Our newest OpTime : { ts: Timestamp 1496653127000|13143, t: 499 } 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] Earliest OpTime available is { ts: Timestamp 1497336727000|23313, t: 502 } 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] going into maintenance mode with 11386 other maintenance mode tasks in progress

Link in the error message exactly explain what happened.

  A replica set member becomes “stale” when its replication process
  falls so far behind that the primary overwrites oplog entries the
  member has not yet replicated. The member cannot catch up and becomes
  “stale.” When this occurs, you must completely resynchronize the
  member by removing its data and performing an initial sync.

To avoid this in future:

You need to investigate why secondary fall behind so much. Possible
more writes then normally expected.
Your oplog size might not be set up correctly. Once your secondary is
behind than the first entry in oplog it will never catch up as it has
no way to get those transactions.


How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server? On SQL Server 2016, how do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?  I don't mind there being another layer in there, such as the MSDASQL provider, but I do want the ODBC Driver 13 for SQL Server to be what ends up making the connection to the remove instance.

With testing on SQL Server 2016 RC2 on Windows 2016 Technical Preview 4, both fresh installs on a blank VM, I can use odbcad32 to see the "ODBC Driver 13 for SQL Server", version 2015.130.1300.275, with file name MSODBCSQL13.DLL.

The version and the filename are identical in the 64-bit odbcad32 screen as well as the 32-bit odbcad32 screen from c:\windows\syswow64, so I do not believe it to be a 32 vs 64 bit issue at this time (particularly since the driver was installed by the SQL Server 2016 RC2 install).

On SQL 2014, for instance, to use Native Client 11, I would use

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'YourTargetServer'

On SQL 2016 RC2, when I try

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'MSODBCSQL13', @datasrc=N'YourTargetServer'

The linked server creates just fine, but when I try to use it, I get:

Msg 7403, Level 16, State 1, Line 7
The OLE DB provider "MSODBCSQL13" has not been registered.

I didn't have any luck trying provider names of
ODBC Driver 13 for SQL Server
Microsoft ODBC Driver 13 for SQL Server

or even trying combinations of that that the MSDASQL provider name, from

Using Always Encrypted with the Windows ODBC Driver
and
sp_addlinkedserver (Transact-SQL)

And even looking through the registry didn't reveal a provider name I recognized.

Note that using odbcad32 to create a System DSN does, in fact, test successfully when I choose ODBC Driver 13 for SQL Server, so I know it can work.

Ideally, I just want a sample sp_addlinkedserver command that specifies the new ODBC driver in it.

It's not possible to use just ODBC. Linked servers use Distributed Queries which is dependent on the OLEDB interface. This is not an optional or interchangeable API. Below that API can be any OLEDB, ODBC or even JDBC provider so long as its implementation supports the OLEDB interface. For example, there is an OLEDB provider for ODBC which is one of the most common ways to run DQ against non-SQL Server data sources. SQL Server natively supports OLEDB so there's no need for the extra layer. Most/all of this information can be found in Books Online or MSDN.

Why do you need to use only ODBC? If want it for Always Encrypted, suggest you log a feature request via https://connect.microsoft.com/SQLServer/feedback/. I don't know if the scenario is even supported where you run AE through DQ. The "calling SQL Server" essentially becomes the client to the "target SQL Server" but you don't fully control the client since DQ isn't something you can modify.

If it's for some other reason, share details here so folks help come up with alternate solutions.


Mirror log empty on primary but fine on the mirror We have dozens of principle servers and corresponding mirrors, most are working fine, however, 1 of them is acting strange. Specifically, the sp_dbmmonitorresults proc returns all columns as NULL for a given principle database whereas sp_dbmmonitorresults on the mirror returns valid information:

Now, i have a rough idea what the cause was; when the principle and mirror servers where setup, the regional settings where incorrect on the primary (the time was correct but was using a +4 region (America etc), the time region was corrected to GMT +0 AFTER the mirroring was setup (school boy error i know, but c'est la vie!)

Edit: 19/12/2012

Today i removed the mirroring, deleted the copies on the mirror and re-setup the mirroring, however this did not solve the problem! I'm going to pop a bounty on this question to try and get to the bottom of this.

To confirm, if i run EXEC sp_dbmmonitorresults @database_name = 'ProScript' manually on each server it returns NULL for most values on the primary but returns fine on the mirror, as shown:

When Ran on primary:

Zoom: Click for full size version

When Ran on the mirror:

Zoom: Click for full size version

As you can see, the times are current and the database is synchronised but the log_generation_rate, unsent_log, send_rate etc vare all NULL on the primary?

Both servers have the montior job running every minute with exec sys.sp_dbmmonitorupdate as a job step etc.

Looking at the source of sys.sp_dbmmonitorupdate it grabs these values from sys.dm_os_performance_counters - so if i run the following on the mirror:

SELECT  counter_name ,
        cntr_value
FROM    sys.dm_os_performance_counters
WHERE   instance_name = 'ProScript'
        AND counter_name IN ( N'Log Send Queue KB', N'Log Bytes Sent/sec', N'Redo Queue KB', N'Redo Bytes/sec', N'Transaction Delay', N'Log Bytes Flushed/sec', N'Transactions/sec' )

I get good results:

However, if i run the same SQL on the primary there are no rows!

This would suggest that the sys.dm_os_performance_counters table isnt being populated for the mirroring? What would cause that???

Well, its took months but i finally found the problem!

The sys.dm_os_performance_counters wasnt being populated at all on the primary server.

Turns out that the counters where not installed, i was able to do this manually as follows:

C:\Windows\system32>lodctr "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSS
QLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"

Restarted SQL, and boom, the table is now being populated - including my mirroring statistics.


SQL Server Multi-Site HA/DR Solution I want to deploy Multi Site SQL Server HA/DR Solution. I am not sure if I use FCI/AG. In my case whenever the wan link is down at Site1 my ISP is redirecting my wan traffic to Site2.

I was thinking of deploying Node1 at Site1 and Node2 at Site2 and File Share Witness at Site3/Cloud, also making sure SAN - SAN Async Replication in place. So whenever my Site1 wan is failing I want the SQL at DR site to be active.

So Kindly Suggest what kind of deployment will be best for this case.

I have SQL Server 2012 Enterprise and Windows Server 2012 R2 Standard. I have to chose whether AOFCI or AOAG.

I have experience with 2 Node SQL Cluster but in single site. But this is new application and I want to have multi-site (multi-subnet) deployment. I have very less knowledge about AOAG but willing to learn of course.

Suppose if I chose to implement FCI i will have Node1 at Site1 with one shared disk1 from SAN1 and Node2 at Site2 with same disk1 from SAN2 but off-line. There will be SAN-SAN asynchronous replication in place and I will use Site3/Cloud for File Share Witness. Will this be the good solution ? Or you suggest AOAG assuming Node1(primary) and Node2(secondary replica - synchronous) at Site1 and Node3(secondary replica - asynchronous) at Site2.

Lets make sure we understand some of the terms involved.

WSFC=Windows Server failover cluster
FCI=failover clustering instance
AOAG=Always On Availability Groups

I suggest you make you requirement absolutely clear before you start implementing. As @John suggested version of SQL Server and Windows Operating system can detect most of your decision.

You definitely need WSFC, does not matter what configuration you chose.  For multi site (assuming multi subnet) WSFC follow this document.

Then you have 3 options:

FCI
AOAG
FCI+ AOAG

For creating multi-subnet Failover cluster instance follow this document.

Creating AOAG between multi subnet is not an issue as long as WSFC is configured correctly.  Issue mostly arises because listener is not configured correctly. See this for details.

Now you need to decide if you need to combined solution of step 1 and 2 meaning FCI+AOAG. This document will give a good start.

For storage level replication you definitely want to discuss with your vendor. In general you can do that when you are deploying AOAG.  Because AOAG will read transaction log and write to secondaries. Most storage level replication that I am aware off, you will need disks to be offline for implementing block level changes.

Here are few more resources that can help you.

https://www.sqlhammer.com/  has bunch of example of different configuration by Derik Hammer.
https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/


How can I improve performance on a 3 table query? I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year).  I need the aggregation down to the minute.  To accomplish this, I'm querying two tables and a view, but it's taking approximately 1 hour per execution so I'm trying to improve its performance.

My Setup

View 1 holds my time intelligence for period reporting.  It's based upon a customized calendar table that uses date as its primary level of aggregation.  This is my primary filter for values, returning 14 rows for a bi-weekly reporting period.

Table 1 is a calendar table, with values to the minute.  It has values for a period of 20 years, so there are in excess of 10.5 million records.  This table provides the level of detail I need for my analysis.

Table 2 is a proprietary table from our RMS vendor and holds the resource utilization records necessary for the analysis.  It resides in a separate database (on the same server) as Table 1 and View 1, and I can't make any changes to it.  For a biweekly period, it will return approximately 350 rows.

My Problem
For a period of time defined through View 1, I need to return all of the rows from Table 1 left joined to Table 2.

My Query:

SELECT t1.calendar_dttm, t2.unit, t2.start_dttm, t2.end_dttm
FROM Table_1 t1
LEFT JOIN Table_2 t2 on  t1.calendar_dttm >= t2.start_dttm
                     and t1.calendar_dttm <=t2.end_dttm
JOIN View_1 v1 on  t1.calendar_dttm >= v1.start_dttm
               and t1.calendar_dttm <= v1.end_dttm
WHERE v1.period_diff = -1

This takes 24.5 mins to return 26855 rows, and eventually I'll need to run it for year (or more) long periods of time.

EDIT:  As I look at the execution plan, I think I need to filter both tables for the reporting period before conducting the joins.  Here's the execution plan:

And here's View_1:

SELECT
c1.[Date ID]
, c1.Date
, c1.DAY
, c1.DayofMonth
, c1.DayofWeek
, c1.Week
, c1.MONTH
, c1.YEAR
, c1.Shift
, c1.DayofPayperiod
, c1.DayofFLSA
, c1.PayPeriod
, c1.paydate
, c1.flsa_end
, FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14)) period_diff
, DATEDIFF(yy,GETDATE(), c1.date) year_cal_diff
, DATEPART(dy,c1.date) doy
, DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) day_offset
, CASE
    when DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) < 0
    then '-'
    else '+'
    end + right('0000'+ REPLACE(DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date),'-',''),4)
    day_offset_label
, CEILING(FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14))/26) year_run_diff
, DATEADD(HH,7,c1.Date) shift_start,DATEADD(s,111599,c1.date) shift_end

FROM Table_Calendar c1

It takes static, custom date data from a table and then applies calculates dynamic period info based upon the date its run.

I see joins to v1 on start_dttm and end_dttm but dont see it in the view or the table listed.

Looking at your definition of your view, are you sure this is an issue with the join, or does the view itself have issues?

There are a few problems, and I will list them in order my brain saw them:

, DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) day_offset
, CASE
    when DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) < 0
    then '-'
    else '+'
    end + right('0000'+ REPLACE(DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date),'-',''),4)
    day_offset_label

These sections of the query indicate you might have RBAR behavior, eg the queries in the parenthesis may be running for each row that you need to return, as you add more data this will take longer and longer.

Of the columns you use that I can see:

FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14))

Uses a calculation which is not SARGable in anyway, and so that -1 comparison would need to be calculated for each row as well.

This may be a case where the execution plan's estimates vs actual rows would be off, you may want to download a tool such as SQL Sentry Plan Explorer (dont need to buy the pro version to easily compare things like this http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view) which will highlight problem areas.

From what I can see of the plan (not much) it is giving me the impression that the planner cannot produce a useful plan and causing you to have a bad time.

My recommendations:

Compare like types so that you can use an index, or worst case make some sort of materialized view if you cant. See if you can unwrap some of those calculations and use less human readable/nice numbers to evaluate date offsets.

You may need to add an index or two to make this fly.

Consider using a numbers table to calculate offsets or values needed for far in the future, Why are numbers tables "invaluable"? so that you can use the power of the index to query this instead of churn your CPU each time you run this query.
Try to query some of this information in a SARGable fashion and then do the gross formatting work on a temp table or something that is your "final set", if this is still to slow you may want to consider doing this in some other programming paradigm.


How to create and send mail in SQL Server 2008? I want to send E-mail from SQL Server 2008. What are the basic steps for that? Can you explain me if someone used this facility.

Any help will appreciated.

this link will helpful as commented by  marc_s (also I have accepted an answer below)
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

In SSMS object Explorer, click Management
Right-click Database Mail
Click Configure Database Mail

4.Select Set up Database Mail by performing the following tasks

5.Enter a profile name and click Add

6.Enter the settings for your email account. I could not set the account for my company's email account - it just didn't work. But I had no trouble with setting the Gmail account I use and these are the parameters that you should use for the Server name and Port number

It is not possible to send attachments from SSMS, you have to use T-SQL for that:

EXEC msdb.dbo.sp_send_dbmail
@profile_name='carol Gmail',
@recipients='john@john.com',
@subject='Test email',
@body='Attachment test',
@file_attachments='D:\123\pic.jpg'


Stored procedure triggered on insert causes deadlock in transaction I'm trying to debug a deadlock that happens when a stored procedure is triggered inside a transaction. Table and trigger definitions are as follows:

CREATE TABLE a (
  id int(11) NOT NULL AUTO INCREMENT,
  b varchar(10) NOT NULL,
  d date NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; //

CREATE PROCEDURE CheckNoDuplicates (new_id int(11), new_b varchar(10), new_d date)
BEGIN
  DECLARE existingId INT(11);

  SELECT id INTO existingId
  FROM a
  WHERE new_b = b
      AND d IS NOT NULL AND new_d IS NOT NULL AND YEAR(d) = YEAR(new_d)
        OR d IS NULL AND new_d IS NULL
       AND id <> new_id;

  IF existingId IS NOT NULL THEN
    SIGNAL SQLSTATE '23000';
  END IF;
END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE INSERT ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE UPDATE ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

When two inserts happen at the same time inside a transaction, sometimes they succeed, sometimes they give me back a deadlock (Deadlock found when trying to get lock; try restarting transaction).

If I get rid of the stored procedure, I get no deadlocks anymore.

How are stored procedure executed inside a transaction?
Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?

Is there a better way to keep the constraint I'm trying to get (uniqueness on b and the YEAR of date d)?

Create virtual generated column which calculates value You'd like to be unique, and create unique secondary index over it.

For more information read MySQL 5.7 Reference Manual / Secondary Indexes and Generated Columns


Create MS SQL login for support engineer to access only vendor database I have sql server 2005,
 I need to create separate database login to support engineer of my software vendor to access their own database. My only worry is deny him to take backup from our database while he connected remotely to resolve any incident in the application.

To sum up,

1- How to create login with access to specific database only
2- How to monitor his activity on database (what query he runs, is he try to took any backup and when)
3-Deny Any backup and restore task (I want to keep this only to sysadmin)

I - To create a login with access to specific database only:

Use SSMS and connect to the SQL server instance
Open Security -> right click Logins -> New Login (e.g. login named SE)

3 Enter the login details. In Server Roles, make sure only 'public' is checked

4 In User Mapping, make sure only the vendor database is selected.

If public role for the vendor database is not enough, grant the necessary permissions but only on the vendor database

Another solution is to deny access to all databases and then grant just to the vendor database

DENY VIEW ANY DATABASE TO [SE]

See more info here: http://msdn.microsoft.com/en-us/library/ms177518.aspx

II- How to monitor his activity on database (what query he runs, is he try to took any backup and when)

I suggest using some third party tools. Transaction logs don't capture info about executed queries

III-Deny Any backup and restore task (I want to keep this only to sysadmin)

Having  public role will prevent the user to backup or restore databases

he'll get the following messages when he tries to backup or restore a database

T-SQL statement to deny creating backups is:

DENY BACKUP ANY DATABASE to [SE]


JOIN tables and return count even if it is 0 I have this query:

select settings.customer_id, current::jsonb->'alarms' as alarms, from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
where res.alarms is not null;

which returns

+------------------+
|customer_id|alarms|
|1          |{...} |
|2          |{...} |
|3          |{...} |
+------------------+

Then I have another entity registration, which fields customer_id and date, where date is of type date and I store the values in format YYYY-MM-DD.

What I want to achieve is to join the registration entity on customer_id and get a count on the registration records for a given single date, f.e. '2018-11-20'. But (here's the catchy part) I want to retrieve it in a way so if there are no registration records for the date, then return 0, otherwise return the actual number.

With the solutions I've tried so far, I would get only records that have the alarms AND have registrations as well, as if I have this:

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(regs.count, 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join (select customer_id, count(customer_id) from registration where date='2018-11-20' group by customer_id) as regs on settings.customer_id=reg.customer_id
where res.alarms is not null;

or

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(count(regs.customer_id), 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join registration as regs on settings.customer_id=regs.customer_id
where res.alarms is not null and regs.date='2018-10-10'
group by settings.customer_id, settings.current;

If I would put it in other words: get records which have alarms object and also get the number of their registrations for the given date, no matter if its 0 or > 0.

LEFT JOIN is the key, like ypercube commented.

And you probably don't need the self-join - assuming settings.customer_id is unique not null.

If your predicate is selective (more than a few rows without alarms), I suggest to count in a LATERAL subquery:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN LATERAL (
   SELECT count(*) AS reg_ct
   FROM   registration
   WHERE  customer_id = s.customer_id
   AND    date = '2018-10-10'
   ) r ON true
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Else, a plain subquery should be faster:

SELECT s.customer_id, s.current::jsonb->'alarms' AS alarms
     , coalesce(r.reg_ct, 0) AS reg_ct
FROM   settings s
LEFT   JOIN (
   SELECT customer_id, count(*) AS reg_ct
   FROM   registration
   WHERE  date = '2018-10-10'
   GROUP  BY 1
   ) r USING (customer_id)
WHERE  s.current::jsonb->'alarms' IS NOT NULL;

Either way, it's typically faster and less error prone to aggregate before the join.
Related:

Understanding multiple table join with aggregation
What is the difference between LATERAL and a subquery in PostgreSQL?


MySQL - Store date as a value with an ID? I've got a database with the following structure:

Elements table
+------------+---------------+---------------+-----+
| element_id |    user_id    | element_data2 | ... |
+------------+---------------+---------------+-----+

Tag categories table
+-------------+---------------+
| category_id | category_text |
+-------------+---------------+

Tag values table
+----------+------------+
| value_id | value_text |
+----------+------------+

Tag-element relationship table
+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+

An element can have multiple tags associated with it, even multiple values on the same category.

What the app does is to let the user put new tag_values as unique elements (categories right now are not user-editable, but may be in a future). As they are not associated to the user, if two users write the same value_text, then the same ID is assigned to it.

So, to have a data sample, the tag-element table - the most important here - would look something like:

+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+
|  1 |          1 |               3 |            9 |
|  2 |          1 |               3 |            5 |
|  3 |          1 |               6 |           13 |
|  4 |          2 |               6 |            2 |
|  5 |          2 |               5 |           15 |
+----+------------+-----------------+--------------+

The question

As such, there is a category called game_date, which stands for the date where the element (that is an event) is celebrated.

Should the game_date values be stored as the other values, so two equal values for a date have a unique ID, or its uniqueness makes its performance poor?

I mean, it can be possible for multiple events to be on the same date and hour, but I don't know if it would be better another kind of structure where that date is stored as a Date field on any of the tables above. Just as the game_date can be also other categories involving date (such as game_recorded, etc.).

Thank you!

It sounds like you have reinvented "Entity-Attribute-Value" schema.  This will be painful in the long run.  Meanwhile...

Do not "normalize" dates or other 'continuous' values (such as FLOAT).

The DATE datatype is only 3 bytes, and may as well be put in whatever table it best fits in, not normalized into an attribute table.

Relationship tables usually do not need an AUTO_INCREMENT id PRIMARY KEY since a combination of columns makes a better PK.  More tips here.


Select Items that has one item but not the other I have three tables:

Table1:

ID      Name
1       Item1
2       Item2
3       Item3

Table2:

ID      Name         Type
1     Logistics       One
2     Human Resource  Two
3     Finance         Three

Table3 (Many to Many Table):

ID     Table1ID      Table2ID
1         1             1
2         1             2
3         2             1
4         3             1
5         3             2
5         3             3

I want to select all the items from table1 that exist in many to many table for just type one from table2 and not to the type two. the type three is not concerned. So all the items from table one that has a record in table3 for only type one and not two.

You can use NOT EXISTS to eliminate rows linked to undesirable types (like @a_horse already suggested).

But since multiple rows can match any row in table1 also use EXISTS to qualify rows linked to desirable types:

SELECT t1.*
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'one'
   AND    t3.table1_id = t1.id
   )
AND    NOT EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'two'
   AND    t3.table1_id = t1.id
   );

With joins like a_horse demonstrated, you would multiply rows and be forced to add an aggregation or DISTINCT step to get distinct rows again. Don't.

Also, you can use the same subquery twice, making the query simpler to understand and maintain.

I would see this as special case of relational-division, btw. Related:

Select rows which are not present in other table
How to filter SQL results in a has-many-through relation


Connections remain active even after terminating the session in Postgres Yesterday, I added the pldbgapi extension in my Postgres DB to debug a function. I was using PgAdmin for debugging the same. But today, when I tried invoking a simple query, it was taking a lot of time to execute. I tried to find out the active sessions using the below query:
select * from pg_stat_activity where datname = 'dbname';

In the results, 6 sessions were there, out of which 4 were active. On checking the query that these active sessions were executing, I found that all the 4 sessions were executing the same query and these queries were invoked yesterday when debugging the function.
So every time I debug a function, a new session was created and all the created sessions didn't get closed and remained in the active state.
I tried to close the session using pg_terminate_backend and pg_cancel_backend but nothing works and all 4 sessions remain in the active state.
I didn't know how this happened and why I am not able to close these connections.

If you had permission to kill them with pg_cancel_backend, but doing so did not work, then they must be in a state where they are not responding to interrupts.  That is probably a bug, either in PostgreSQL itself, or in your code, or in pldbgapi.  What does wait_event_type and wait_event say?  Can you attach to them with gdb and get backtraces?


errno: 150 "Foreign key constraint is incorrectly formed" CREATE TABLE IF NOT EXISTS `Register_Per` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Name` varchar(50) NOT NULL,
 `DOB` varchar(20) NOT NULL,
 `Gender` CHARACTER(8) NOT NULL,
 `ConNo` int(11) NOT NULL UNIQUE,
 `Email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
 );

CREATE TABLE IF NOT EXISTS `login`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(20) NOT NULL,
    PRIMARY KEY (`username`),
FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`)
);

CREATE TABLE `images` (
 `id` int(11) NOT NULL AUTO_INCREMENT UNIQUE,
 `file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `uploaded_on` datetime NOT NULL,
 `submittedby` varchar(50),
 FOREIGN KEY(`submittedby`) REFERENCES `login`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What to change in this..??

It starts to work on 5.7.27 if second table uses matching DEFAULT CHARSET and COLLATE options:

CREATE TABLE IF NOT EXISTS `login`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(20) NOT NULL,
    PRIMARY KEY (`username`),
    FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`)
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


innodb_buffer_pool_size more data than pool size I have 64gb ram on server.
InnoDB buffer pool is set to 26.1gb

[OK] InnoDB buffer pool / data size: 26.1G/24.3G

Im wondering what happens if data over grow current pool size, as i understand it should store more important data and data more often used in pool and other access from hard drive ?

I had similar situation before, and as soon it has overgrown pool size mariadb server become unstable, i have been trying to lift up innodb_buffer_pool_size to 32gb but it caused stability problem as well. Even that most of time memory usage is 33gb/64gb and most of this ram is used by mariadb server, only 500mb ram is used by other applications. There other half seems not used. I have temporary solved it by truncating unnecessary data but this time i can't do it.

I have considered to use swap but since i have only 2x2TB Soft Raid Mode 1 and if InnoDb will read from disk anyways there is no point to do so i guess.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

Shows that i should use 60gb innodb pool size.

If it's important innodb_flush_method=O_DIRECT

If the buffer_pool is set at 26GB in a 64GB server, what is the rest of RAM being used for?  Normally you should simply set it to 45-50G and not worry.

On the what-if question:

Case 1:  There is high "locality of reference".  A buffer_pool that is much smaller than the total data (24.3G in your case) works fine.  The buffer_pool is a "cache", and this Case is an example where caching is important.

Case 2:  Lots of random access or lots of table scans.  In this Case performance will degrade, possibly seriously, if the buffer_pool is not big enough for the dataset.  The caching fails, and I/O ensues.

UUID primary keys or other indexes can lead to random accesses and performance problems in Case 2.

I see no clues in what you said that could explain "32gb .. caused stability problem".

In most servers, O_DIRECT is good.

innodb_buffer_pool_instances should probably be 16 in your range.


What is an Oracle ASSEMBLY? What is an Oracle ASSEMBLY, in the context of Oracle SQL?

It's listed in GV$SQLCOMMAND.  Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of MERGE.

   INST_ID COMMAND_TYPE COMMAND_NAME             CON_ID
---------- ------------ -------------------- ----------
         1          215 DROP ASSEMBLY                 0
         1          216 CREATE ASSEMBLY               0
         1          217 ALTER ASSEMBLY                0

The commands work although I'm not sure what they are doing.  Oddly the syntax requires a /, implying these objects may contain PL/SQL.

SQL> create or replace assembly some_assembly is 'some string';
  2  /

Assembly created.

SQL> select * from all_assemblies;

OWNER           ASSEMBLY_NAME   FILE_SPEC       SECURITY_L IDENTITY        STATUS
--------------- --------------- --------------- ---------- --------------- -------
JHELLER         SOME_ASSEMBLY   some string     SAFE                       VALID

The data dictionary views work and are documented: ALL_ASSEMBLIES.

There are some Oracle products that use assemblies but I don't know if they tie directly to this SQL statement.  For example, there is the Oracle® Fusion Middleware Using Oracle Virtual Assembly Builder, Database assemblies in Oracle Enterprise Manager, and Database Extensions for .NET (which involve assemblies).

What is an ASSEMBLY?  A deprecated command?  An unimplemented or undocumented feature?  Or a real command that's documented somewhere I haven't looked?

There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show.

However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$:

remark
remark  FAMILY "ASSEMBLIES"
remark
remark  Views for showing information about PL/SQL Assemblies:
remark  USER_ASSEMBLIES, ALL_ASSEMBLIES and DBA_ASSEMBLIES
remark
create or replace view USER_ASSEMBLIES
(ASSEMBLY_NAME, FILE_SPEC, SECURITY_LEVEL, IDENTITY, STATUS)
.......

This definitely says to me that these views, and the associated CREATE/DROP/ALTER ASSEMBLY commands, are for .NET stored procedure support.


Does `SERIAL PRIMARY KEY` create two indexes on the table? Since SERIAL is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, it looks like SERIAL PRIMARY KEY doubles up on the UNIQUE and PRIMARY KEY:

> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);

> SHOW CREATE TABLE foo \G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

There's definitely the wtf factor of seeing both PRIMARY KEY and UNIQUE KEY id in SHOW CREATE TABLE, but is that really two physical indexes I'm seeing?

Should one avoid using PRIMARY KEY with SERIAL and instead opt for writing bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, or using SERIAL with no primary keys on the table?


ACID-compliance of MySQL master-master replication? I've created two MySQL 5.1 servers, replicating off each other (log-bin) in a low latency environment (different AZs within an EC2 region). All tables that are being replicated are using the InnoDB storage engine.

It appears to work as expected in a number of scenarios I have tested (not brimming with confidence, though), but I am worried that I cannot find any answers on whether this setup guarantees ACID for both nodes on transactions made to either node.

If not, are there steps I can take to meet this requirement?

I've seen various bits and pieces around the mysql.com site such as:

  ... For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1 ...

but I find it rather worrying that I cannot find a definitive answer (or even guide) on the matter.

MySQL replication is not ACID compliant since it is asynchronous.  There are no guarantees that queries executed on one server will be committed on the other.  In fact, the master doesn't even know about the slave except that it's pulling binary logs.  The binary logs themselves "might" be considered ACID compliant when sync_binlog=1 is on, but one server does not know if the other server has committed the change.

If you truly need something close to ACID compliance in your replication, what you want is synchronous replication, which is provided by products like Tungsten Replicator, Galera, and Percona Xtradb Cluster (which is Galera + Percona Server), and MySQL Cluster.  I'm not sure if they fit a strict definition of ACID compliance, but they are certainly a lot closer.  MySQL 5.5 has something called semi-synchronous replication, but don't be fooled...it's still asynchronous.

You can improve the reliability of MySQL replication by doing a few things:

Use Row-based Replication (RBR) by setting binlog_format=ROW in my.cnf.  This will replicate the entire row for every change made instead of replicating the SQL statement that made the change (as with statement-based replication, [SBR]).  That minimizes the chances that a statement executes differently between servers if there are small discrepancies already.
Run pt-table-checksum frequently to verify the consistency between master and slaves.  If there are certain tables that are more important, run it on those tables more frequently.
Set up monitoring that checks the output of pt-table-checksum and alerts you when there are discrepancies.  You can use pmp-check-pt-table-checksum from the Percona MySQL Nagios Plugins if you use nagios.  Otherwise, it's just a simple query to determine this information.
If you have discrepancies, use pt-table-sync to resolve them.  Read the documentation carefully, as you can shoot yourself in the foot relatively easily with this tool.


Databse ER Design for Apartment Complex and Automaker Employees I recently completed an assignment which required me to complete an ER diagram for an apartment complex housing automaker employees. There are multiple automaker companies, and each of these companies have employees. There is a series of apartment complexes owned by the same person that houses these employees. If an employee stays in an apartment complex for over 7 days, a discount is applied that is unique to the company.

Some of the requirements are as follows:

Keep track of the new Employees/Trainees and their permanent (Home) addresses and contact information who stay at each of the Alpha Apartment Buildings during a given training session.
Keep track of the new Employee's/Trainee's current address and contact information. (What apartment are they residing in?)
Keep track of the length of stay for an Employee/Trainee in a specific apartment. This should be based on a Check In and Check Out date.
Keep track of all the individual apartments a given Employee/Trainee has ever stayed in. Note: This is historical data. An Employee/Trainee that stays at the one of the Boarding Houses, may get the same apartment or a different one each time that they come to Detroit for training.
Keep track of the different apartments that are in each apartment building, along with each apartment   number and floor
Keep track of the different nightly charges for each of the different apartment types.
Keep track of the name, location, address and contact information of each of the apartment buildings
Keep track of the address and contact information of the Big Three Automakers
Keep track of which Employee/Trainee works at which auto company for billing purposes.
Keep track of which apartments at each building are available for rental.

My ER-diagram:

As someone new (4 months) to the logical design, I am wondering if there was anything that could be improved on or changed regarding this ER diagram. I have a firm grasp on the concept of relationships, but I get confused when it comes to invoices, order lines, etc.

I also want to make it clear that this assignment has already been turned in and cannot be re-submitted. This is strictly a question out of the desire to learn.

I think overall you have a decent enough design there. My only points of issue might be with the Invoice Table having a calculated field (TotalNights) and that the Discount uses just the DiscountID, not the amount being discounted. Like this, a person looking at the table would have to JOIN to the Discount Table to see what the discount is by itself, unless he wanted to do the manual math of breaking down the Total, Flat Rate and Discount amount.


Can't create a backup of data of a single table I try to use a command:

pg_dump -U postgres -d "ProjectsManagement" -t public.Statuses -a > path

But it doesn't work and outputs:

  pg_dump: соответствующие таблицы не найдены (English: Tables not found)

But this table exists and without a -t argument it works.

What is happening?

\l output:

  ProjectsManagement | postgres | UTF8      | Russian_Russia.1251 |
  Russian_Russia.1251 |

Just taking a look at postgres documentation and samples I've found this specific case:

  To specify an upper-case or mixed-case name in -t and related
  switches, you need to double-quote the name; else it will be folded to
  lower case (see Patterns). But double quotes are special to the shell,
  so in turn they must be quoted. Thus, to dump a single table with a
  mixed-case name, you need something like:

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

You can read about it here.


Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans? I have a SQL Server database where the queries are pretty slow, and there's a lot of locking and blocking.

When I look at the missing index DMVs and query plans, there aren't any suggestions.

Why is that?

There are many reasons why you may not have missing index requests!

We'll look at a few of the reasons in more detail, and also talk about some of the general limitations of the feature.

General Limitations

First, from: Limitations of the Missing Indexes Feature:

  It does not specify an order for columns to be used in an index.

As noted in this Q&A: How does SQL Server determine key column order in missing index requests?, the order of columns in the index definition is dictated by Equality vs Inequality predicate, and then column ordinal position in the table.

There are no guesses at selectivity, and there may be a better order available. It's your job to figure that out.

Special Indexes

Missing index requests also don't cover 'special' indexes, like:

Clustered
Filtered
Partitioned
Compressed
XML-ed
Spatial-ed
Columnstore-d
Indexed View-ed

What columns are considered?

Missing Index key columns are generated from columns used to filter results, like those in:

JOINs
WHERE clause

Missing Index Included columns are generated from columns required by the query, like those in:

SELECT
GROUP BY
ORDER BY

Even though quite often, columns you're ordering by or grouping by can be beneficial as key columns. This goes back to one of the Limitations:

  It is not intended to fine tune an indexing configuration.

For example, this query will not register a missing index request, even though adding an index on LastAccessDate would prevent the need to Sort (and spill to disk).

SELECT TOP (1000) u.DisplayName
FROM dbo.Users AS u
ORDER BY u.LastAccessDate DESC;

Nor does this grouping query on Location.

SELECT TOP (20000) u.Location
FROM dbo.Users AS u
GROUP BY u.Location

That doesn't sound very helpful!

Well, yeah, but it's better than nothing. Think of missing index requests like a crying baby. You know there's a problem, but it's up to you as an adult to figure out what that problem is.

You still haven't told me why I don't have them, though...

Relax, bucko. We're getting there.

Trace Flags

If you enable TF 2330, missing index requests won't be logged. To find out if you have this enabled, run this:

DBCC TRACESTATUS;

Index Rebuilds

Rebuilding indexes will clear missing index requests. So before you go Hi-Ho-Silver-Away rebuilding every index the second an iota of fragmentation sneaks in, think about the information you're clearing out every time you do that.

You may also want to think about Why Defragmenting Your Indexes Isn’t Helping, anyway. Unless you're using Columnstore.

Adding, Removing, or Disabling Indexes

Adding, removing, or disabling an index will clear all of the missing index requests for that table.  If you're working through several index changes on the same table, make sure you script them all out before making any.

Trivial Plans

If a plan is simple enough, and the index access choice is obvious enough, and the cost is low enough, you'll get a trivial plan.

This effectively means there were no cost based decisions for the optimizer to make.

Via Paul White:

  The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.

When a plan is trivial, additional optimization phases are not explored, and missing indexes are not requested.

See the difference between these queries and their plans:

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2;

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2
AND 1 = (SELECT 1);

The first plan is trivial, and no request is shown. There may be cases where bugs prevent missing indexes from appearing in query plans; they are usually more reliably logged in the missing index DMVs, though.

SARGability

Predicates where the optimizer wouldn't be able to use an index efficiently even with an index may prevent them from being logged.

Things that are generally not SARGable are:

Columns wrapped in functions
Column + SomeValue = SomePredicate
Column + AnotherColumn = SomePredicate
Column = @Variable OR @Variable IS NULL

Examples:

SELECT *
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 1000) > 1000;

SELECT *
FROM dbo.Users AS u
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000

SELECT *
FROM dbo.Users AS u
WHERE u.UpVotes + u.DownVotes > 10000000

DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren'
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo
      OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

None of these queries will register missing index requests. For more information on these, check out the following links:

Optional Parameters and Missing Index Requests
SARGable WHERE clause for two date columns
What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

You Already Have An Okay Index

Take this index:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

It looks okay for this query:

SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND   p.CreationDate < '20181231'
AND   p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;

The plan is a simple Seek...

But because the leading key column is for the less-selective predicate, we end up doing more work than we should:

  Table 'Posts'. Scan count 13, logical reads 136890

If we change the index key column order, we do a lot less work:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

And significantly fewer reads:

  Table 'Posts'. Scan count 1, logical reads 5

SQL Server Is Creating Indexes For you

In certain cases, SQL Server will choose to create an index on the fly via an index spool. When an index spool is present, a missing index request won't be. Surely adding the index yourself could be a good idea, but don't count on SQL Server helping you figure that out.


What impact on the index storage size does overprovisioning of varchar types have? Does over-provisioning varchar columns (as in using varchar(100) to store values from 1 to 10 characters long vs using varchar(10)) have any impact on the index storage size and if yes than why?

I recall reading a Q/A here about this but cannot find it using any of the terms which come to mind.

I do not care about memory grants or plans - only about the physical storage of the indices on such data.

Variable length columns only store the actual data present in the column.  In other words, the defined length of a varchar column has no bearing on the amount of physical storage allocated.

However, querying the data requires a memory allocation that is commonly calculated to be the number of rows expected multiplied by 50% of the defined column size.  So, if you define a column as varchar(500), but only store 50 bytes in the column,  you're wasting 200 bytes of RAM per row in memory.


Postgres - Update multiple rows in the same query with limit I am trying to update multiple rows in a table by using the following statement:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)
) as c(column_b, column_a)
where c.column_b = t.column_b;

However, in my database the values in column_b are not unique (e.g., multiple rows can have the '123'). I also have a column_c with a DATE type. For each of the rows in the update statement, I only want the update stated above to happen on the row with the most recent date in column_c, for example by ordering the data by date and using LIMIT 1.

Therefore, I am trying to combine this query with the answer provided here. However, I have trouble doing this.

You can use a derived table or cte to find one row (the latest) per column_b:

with upd as
( select distinct on (t.column_b)
      t.pk, c.column_a              -- pk : the primary key column(s)
  from test as t
    join
      (values
         ('123', 1),
         ('345', 2)
      ) as c (column_b, column_a)
    on c.column_b = t.column_b
  order by t.column_b, t.date desc
)
update test as t
set column_a = upd.column_a
from upd
where upd.pk = t.pk ;


How to get mongodump --quiet to still report errors? I'd like to run mongodump so that it only outputs errors (to avoid extraneous logwatch reporting for an automated nightly backup script).

I've tried mongodump --quiet but from a quick test this supressess error messages also (running it at root folder without permission suppresses the "error creating directory" message that I get without --quiet).

How do I get only error output from mongodump?

Here's the error I get when running without --quiet but which I do NOT get with --quiet:

  2016-04-17T13:50:26.294-0400  Failed: error dumping metadata: error creating directory for metadata file dump/234-watchdog: mkdir dump: permission denied

MongoDB is running on Ubuntu. I understand why this error is happening. I want errors like this NOT to be suppressed with the --quiet option.

As you already said

  I've tried

mongodump --quiet

As per mongodb documentation Here

  Runs the mongodump in a quiet mode that attempts to limit the amount
  of output.

This option suppresses:

output from database commands
replication activity
connection accepted events
connection closed events

mongodump is an useful tool to backup Mongo database. Apart from taking a cold backup, it can also take hot backup. i.e You can connect to a running instance of MongoDB and take a backup even when users are using the database.
There are two way you can mongdodump

Backup by Shutting down Mongod Instance
Backup without Shutting down Mongod Instance

For further your ref from @RAMESH NATARAJAN Blog How to Backup and Restore MongoDB using mongodump and mongorestore


Deleting & Reclaiming space from InnoDB table I have a 700GB InnoDB table which I'm not writing any more data to (only reading).
I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?
Edit by RolandoMySQLDBA
Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:
SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.
Edit by Noam
This is the output of the query:
datsize ndxsize tblsize
682.51  47.57   730.08
This is the table structure (SHOW CREATE TABLE)
`CREATE TABLE `mybigtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `tid` bigint(20) NOT NULL,
  `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ft` tinyint(1) NOT NULL,
  `irtsd` bigint(20) NOT NULL,
  `irtuid` int(11) NOT NULL,
  `rc` int(11) NOT NULL,
  `r` tinyint(1) NOT NULL,
  `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `timezone` varchar(5) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`

This is a good question. You have several solutions but your table is quite big so none  will be without pain :)

You have three solutions to "shrink" InnoDB tables:

1. OPTIMIZE TABLE

You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable :

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Let me explain:

The OPTIMIZE TABLE with InnoDB tables, locks the table, copies the data in a new clean table (that's why the result is smaller), drops the original table and renames the new table with the original name. That why you should make sure to have twice the space of the original table available on your disk (You'll probably need less, since the optimized table will be smaller, but it's better to be safe than sorry).

innodb_file_per_table = ON : In this mode, all tables have their own data file. The OPTIMIZE statement will then create a new data file with optimized space usage. When the operation is finished, MySQL will drop the original one and replace it with the optimized version (so at the end the 700GB -- probably less because it will be optimized -- of data generated during the operation will be released)

innodb_file_per_table = OFF: In this mode, all data is contained in one data file: ibdata. This mode has a big drawback since it cannot be optimized. So during the OPTIMIZE process, your new table will be created (near 700GB), but even after the drop and renaming operation (and the end of OPTIMIZE phase) your ibdata will not released the ~700GB, so you wanted to free some data, instead you have 700GB more, cool isn't it?

2. ALTER TABLE

You can also use an ALTER TABLE statement, the ALTER TABLE will work in the same way as OPTIMIZE TABLE. You can just use:

ALTER TABLE myTable ENGINE=InnoDB;

3. ALTER TABLE (ONLINE)

The problem of OPTIMIZE and ALTER TABLE is, that it locks the table during operation. You can use the Percona tool : pt-online-schema-change (from Percona Toolkit : link
). pt-online-schema... provide mechanisms to optimize the table, while keeping the original table available for read and writes. I use this tool in production for ALTER statements on big tables and it's pretty cool.

Note that any FOREIGN KEYs referencing your table might complicate things, since locks might lead to locks on other tables and so on. To check this, simply query:

mysql> SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME = "myTable";
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

Here is how I use pt-online-schema-change:

pt-online-schema-change --alter "ENGINE=InnoDB" D=myBase,t=myTable --user --ask-pass

Note that my note on innodb_file_per_table is true also for this solution.

4. mysqldump

The last solution is to recreate all databases from a dump. It takes forever, but it's extremely efficient. Note that this is the only solution to optimize your ibdata file, if innodb_file_per_table is OFF

Max.