What is the sense to use server_lifetime with PgBouncer?

This is base of pgBoouncer or any pooler software - the main work of pgBouncer is reusing PostgreSQL connections. Creating lot of connections can be very expensive for short sessions - with fast queries. When physical connection can be shared (reused) for more clients, you can get much faster execution.

Second important role of pgbouncer and similar software is a protection against overloading. PostgreSQL has not internal pooling - any connected user can run any SQL. There are lot of applications with long idle connections. These applications requires high MAX_CONNECTION number. Hidden assumption is low activity on these connections. But sometimes can be pretty high activity, pretty high requests on new connects and the database can be overloaded. The MAX_CONNECTION should be about 10 x CPU cores. Inadequate value doesn't protect database against overloading and finish by failure. For these application can be pgbouncer safe solution - this software is able to share physical idle connections for lot of clients.

The server_lifetime enforces returning allocated sources to operation system. Some sources (like memory) is internally managed and it is newer returned to operation system (usually). Some memory expensive operations can require lot of memory. First time this memory is allocated from system. Next times this memory is managed by PostgreSQL only - it is much cheaper and faster. But sometimes it can be ineffective too. pgbouncer has not information which process has preallocated memory - and sometimes memory can be used better. The safeguard is server_lifetime. One hour is conservative value - and can be shorter - 10 minutes. pgbouncer close only not active connection - so if the connection is used by any client: opened transaction, long active command, .. server_lifetime cannot be applied.


All tables used in a FROM clause must have a "correlation name" in standard SQL terms a.k.a "alias" in common vernacular SQL

When you use a table directly, the name is obvious. When you use an inline derived table it must be aliased whether it is actually referenced or not

You can use a CTE too. Here "MyCTE" is the name and doesn't required an alias

;WITH MyCTE AS
(
    select
      DateDiff (second, date,outtime)  as TotalSeconds
    from
        attendance.attn_card_register)
)
Select
   convert(varchar(10),  TotalSeconds / 3600) +':'+
   convert(varchar(10),   TotalSeconds % 3600 / 60) +':'+
   convert(varchar(10),  TotalSeconds % 60) as Seconds
from
   MyCTE;

Edit: not all RDBMS require it. SQL Server, MySQL, and Teradata do, Oracle doesn't.


SSIS via Import Wizard SQL Server 2008 R2

You could use the wizard and load the data into a temp table then process it from there.  That's probably your easiest option.  Unfortunately while I know there is a relatively easy way to do the processing I just can't remember what it is.

The other option is going to be to use BIDS and you will probably have to use a script source to construct your input.


Agent Xps getting disabled automatically

I had a similar problem that I just resolved. Turned out, a new group policy had been applied to our server. This group policy disallowed domain users access to the D:, E:, & F: drives. Our SQL Server Agent was configured to write out its error log to the E: drive. As the Log On account for the SQL Server Agent service was a domain based account, SQL Server Agent was now unable to write to its log file. It died immediately at service start up. It was very frustrating in that we saw nothing in the Windows Event Viewer other than that the service started up and then stopped.


How to ensure pg_stat_activity shows all my queries?

pg_stat_activity is not an auditing facility.  It only shows one query per connected session.  Either the query which is currently executing, or if the connection is idle than the immediately prior query.

If you want to see historical queries, use a different tool. Maybe  pg_stat_statements, or log_statement=all.  Or any of a large number of third-party tools.


Merging two rows into one by creating two new columns based on criteria from same table (SQL)

Here is one way:

SELECT name, year, quarter, type, coalesce(x_score, y_score) score, x_score, y_score
FROM (
   select name, year, quarter, min(type) type
      , min(case when type='x' then score else null end) x_score
      , min(case when type='y' then score else null end) y_score
   from t1
   group by name, year, quarter
);

Using this setup data:

drop table t1;
create table t1 (name varchar2(1), year number(4), quarter Varchar2(2), type varchar2(1)
   , score number(3));
insert into t1 (name, year, quarter, type, score) values ('a',2016,'Q1','x',40);
insert into t1 (name, year, quarter, type, score) values ('a',2016,'Q1','y',50);
insert into t1 (name, year, quarter, type, score) values ('b',2016,'Q1','x',40);
insert into t1 (name, year, quarter, type, score) values ('a',2017,'Q1','x',40);
commit;
select * from t1;


Transfer Data between MySQL and MongoDB Databases

This will probably get closed, but last time I was working with MongoDB heavily (1-2 years ago) nothing beat RoboMongo. It was free before and is open sourced now as well, which is pretty neat.


The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team).

You probably want to use 2 joins to teams in you query:

SELECT
    home.team_name AS home_team_name,
    away.team_name AS away_team_name,
    r.score
FROM
    results AS r
  JOIN teams AS home
    ON r.home_team = home.team_id
  JOIN teams AS away
    ON r.away_team = away.team_id ;


SQL Rebuild Index, Recovery Model, and db log filesize?

Switching to SIMPLE means you break the log chain. When you 'revert' to FULL you need to start a new log chain, meaning you have to take a full backup and start taking again new log backups. The switch to simple, no matter how short, in effect creates a new 'epoch' in your backup chain, as any backup from before the switch to simple can no longer be applied to the database after the switch, nor the other way around.

So at this moment you have to stop and think: what is the business requirement that made you have FULL recovery model to start with? Whatever reason it is, is unlikely that it can be 'suspended' every Saturday at 3am, and is just unlikely that it will tolerate your 'epoch' situation in which you can restore back in time from Friday to Thursday, but you cannot from Saturday to Friday because Saturday is a new 'epoch'. In other words, if you have a business requirement for FULL recovery model, then you better not break it.

But if you do not have a business requirement for FULL recovery model, then you have room to play. And I do not mean to switch to SIMPLE, I mean use the 'other' recovery model: BULK_LOGGED. The reason your reindex  operations generate copious log is that they occur under FULL recovery model. Under BULK_LOGGED the index rebuild (both offline and online) are going to use minimally logged operations, see Operations That Can Be Minimally Logged:

  If the database is set to the simple or bulk-logged recovery model,
  some index DDL operations are minimally logged whether the operation
  is executed offline or online. The minimally logged index operations
  are as follows:

  CREATE INDEX operations (including indexed views).
  ALTER INDEX REBUILD or DBCC DBREINDEX operations.
  DROP INDEX new heap rebuild (if applicable).

So, if possible, switch the database recovery model to BULK_LOGGED and leave it as such.


What is the "shrinkd" in SQL Server logs?

This question was also asked in a comment on Paul Randal's blog Inside the Storage Engine: Ghost cleanup in depth

Paul replied:

  Looks like

  a) you’ve got auto-shrink enabled for the database, or there’s an Agent job doing shrink
  b) you’ve had a lot of deletes, and the shrink ‘touched’ a lot of the pages with deleted records on so they got queued up for the ghost cleanup task to take care of.

  You need to fix a).

Feedback from the question author:

  When it happened again, I set dbcc traceon(661,-1) and the server resumed immediately. Why would there be so many ghostcleanup tasks suddenly without any shrink job? The answer: if the number of pages with ghosted rows is above the specified (hard-coded) limit, the task runs non-stop till the count comes down below the threshold value.


How to migrate MySQL database to SQL Server on a separate machine?

Use Microsoft SQL Server Migration Assistant for MySQL to migrate data from MySQL into SQL Server.

The steps involved consist of:

Mout/Restore/Load the datadump file to a new MySQL server.
Use SSMA for MySQL to migrate to SQL Server.  SSMA does not require MySQL to be installed on the SQL Server machine, however it requires a running database engine to communicate with.


SSIS Package truncation and possible data loss (error code: 0xC020901C)

Okay I found the solution after searching and testing many things.
The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, or when you close the solution and reopen it.

I don't know why this occurred, but now the package is running again perfectly.


Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

On Arch, the systemd script for PostgreSQL (/usr/lib/systemd/system/postgresql.service) enables private /tmp, which means the PostgreSQL server process can't communicate with the user using /tmp.

Two possible solutions:

Create a new folder somewhere else and give user postgres read privileges on it: # sudo mount -t tmpfs -o users,rw tmpfs /data
(NOT RECOMMENDED) Set PrivateTmp option in postgresql.service to false.


Does an ad hoc, full backup, using Oracle RMAN, flag files, so they are not backed up by the regular backup?

Overview of Backup Optimization

  When you enable backup optimization, the BACKUP command skips backing
  up files when the identical file has been backed up to the specified
  device type.

  ...

  RMAN uses backup optimization when the following conditions are true:

  The CONFIGURE BACKUP OPTIMIZATION ON command has been run to enable backup optimization.
  You run BACKUP DATABASE, BACKUP ARCHIVELOG with ALL or LIKE options, or BACKUP BACKUPSET ALL, BACKUP RECOVERY AREA, BACKUP RECOVERY FILES,
  or BACKUP DATAFILECOPY.
  Only one type of channel is allocated, do not mix disk and SBT channels in the same backup command.

With backup optimization, the database will not backup the same, identical file more than once to the same device type.

Other than that, the files will be backed up by the regular backup as well.


How to tell which indexes are not being used

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes

More details about the statistics collector can be found in the manual: http://www.postgresql.org/docs/current/static/monitoring-stats.html

You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably vital to the integrity of your data.


Since these are system stored procedures, you need to deny permissions in the master database using a new role in master, not the one you already have:

Add a new role DenyExecRole in master
Add the desired login to that role
Deny execute permissions:

USE master;
DENY EXECUTE TO DenyExecRole;


Extract one MySQL database from a FULL MYSQL Server backup file

Your same question seems to be answered here:

https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump

And as far as an improved backup script, here's what I'm currently using for several years and it rocks:

http://sourceforge.net/projects/automysqlbackup/


this should do the trick if you are using T-SQL

select C1, C2, a.Chuck
from testa
right join  (select C1 as Chuck from testa UNION ALL select C2 as Chuck from testa) as a  ON a.Chuck = testa.C1


Multiple Availability Groups on one instance

The first availability group has an endpoint which is listening on port 5022. how can I change the port for second and third availability group?

All availability group traffic flows over a single endpoint and there is only one endpoint allowed per instance. So, in this case, since you already have an endpoint setup you only need create the new availability group.


SQL Server Express Edition does not include SQL Agent functionality.

While the installer does register the service entry, the service will fail to start if you try. (I verified with one of my own instances -- the error message I got is exactly the same.)

See this MSDN article for a comparison of features available in each edition of SQL Server 2008 R2.

If it's appropriate for what you need to accomplish, you can use the Windows Task Scheduler instead.


sp_BlitzLock
I'd recommend using sp_BlitzLock, which you can point at the deadlock XML you've captured via the @EventSessionPath parameter (assuming you're collecting to a file, and not the ring buffer or something else). By default, it will look at the system health Extended Event, which may also have useful data in it.
There are some important fixes in the dev branch, so you should use that version until the main release later this month (2020-12, if you're reading this far into the future).
It will accurately categorize regular and parallel deadlocks:

Plan Explorer
You may also get more readable information if you open the deadlock graph using the free Plan Explorer tool, from Sentry One, a subsidiary of SolarWinds Worldwide, LLC, which is a subsidiary of The Sheinhardt Wig Corporation.
It can also tell you if your deadlocks are from Exchanges or Syncpoints, which would denote a parallel deadlock.


Is it possible to upgrade MongoDB from 2.0 to 2.4 directly

In general, yes, you can do this, the 2.4 binaries can be used as drop in replacements.  However, there are exceptions.  In sharded clusters for example, you have to make sure to do the meta data upgrade

Essentially, the caveats are all really deployment and feature (authentication), not version based.  Basically make sure to read the notes here:

http://docs.mongodb.org/manual/release-notes/2.4-upgrade/


Can I upgrade MariaDB replication infrastructure gradually?

A MariaDB replica can replicate from an older master. The other way around won't work.
You should add a step 2.5: stop replicating from M1 to M2 (even if it's just a standby master). After step 4, M1 can replicate from M2 again.


I want to Move some Mysql table to InnoDB but i don't know what is the best my.cnf conf

innodb_buffer_pool_size is the most important setting for InnoDB.  See http://mysql.rjweb.org/doc.php/memory
Tips on converting from MyISAM:  http://mysql.rjweb.org/doc.php/myisam2innodb
Don't use utf8 for ascii strings.  md5id?
Change user_id_md5id to INDEX(user_id, md5id, ptype) for the LEFT JOIN ( SELECT ... ).
INDEX(md5id, ptype, date), with date last, for the outer query.
Rethink having lots of tables named status_nnn; there may be better ways to design the schema.
max_connections = 600 and 5 running, 775 sleeping -- It may be better to throttle the number of clients, and not let so many get to MySQL.
How big is the table?  (rows or GB)


Hive. Over partition by vs where conditions

In order to make use of the partitions -

Your query on the whole month should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
group by    col1,col2,year,month,day

Your query on a single day should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
        and day   = '01'
group by    col1,col2,year,month,day

Your query on a range of days should look something like this:

select      col1,col2,sum(col3),year,month,day
from        mytale
where       year  = '2017'
        and month = '02'
        and day between '01' and '07'
group by    col1,col2,year,month,day


Large Data Long Query A Lot of Joins horibble performance

Looking at your comments, I can see that the problem is not the JOINs, but the LIKE '%term%' operators.

There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'.

mysql> CREATE TABLE person (id SERIAL, first_name VARCHAR(100));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into  person (first_name) VALUES('AARON'), ('ABDUL'), ('ABE'), ('ABEL'), ('ABRAHAM'), ('ABRAM'), ('ADALBERTO'), ('ADAM'), ('ADAN'), ('ADOLFO'), ('ADOLPH'), ('ADRIAN'), ('AGUSTIN'), ('AHMAD'), ('AHMED'), ('AL'), ('ALAN'), ('ALBERT'), ('ALBERTO');
Query OK, 19 rows affected (0.01 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE person ADD FULLTEXT INDEX(first_name);
Query OK, 0 rows affected, 1 warning (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM person WHERE MATCH(first_name) AGAINST ('+al*' IN BOOLEAN MODE);
+----+------------+
| id | first_name |
+----+------------+
| 17 | ALAN       |
| 18 | ALBERT     |
| 19 | ALBERTO    |
+----+------------+
3 rows in set (0.00 sec)

Please note that it is not exactly the same query- it will only match whole words, it may have length restrictions, controlled by ft_max_word_len and ft_min_word_len (or innodb_ft_min_token_size and innodb_ft_max_token_size) and stop words are into play.


For SSL renewal procedure in Db2 LUW , DO i need to create CSR request for each instance residing in same server?

There is no reason to create a different CSR for each instance on a host.  A non-wildcard SSL Certificate is tied to a particular server, so you would typically use the same certificate for all instances.

With multiple instances, you can create a CSR for the first instance to send to the CA, then receive the certificate into that instance’s keystore.  As long as you don’t have different contents for each instance’s SSL keystore, you can simply copy the files that make up one instance’s keystore to each other instance’s keystore.

Some might suggest having a single keystore that all Db2 instances use, but this creates other problems for maintaining security (as the keystore should only be accessible by the instance owner).  It might be possible to do this with ACLs, but creating that complexity may outweigh the benefit you’d get from having a separate keystores for each instance.


To locate your data try the following command:

sudo find / -name postgresql.conf

The problem probably is that your server isn't automatically started. You can manually start it with a command like:

pg_ctl start -D <your data dir>

If you need help starting it automatically please specify which os/distribution you are running and how you installed postgresql (source/yum/rpm/...)


mongodump replicaset - 10 hours data loss acceptable - do we need --oplog (oplog.bson) option?

Yes. OPlog is kind of the same as MySQL binary logs as both are used to sync data to secondary and point in time recovery.

You can use mongodump without --oplog option. You will only loose current writes in your backup. As you have a 10 hour window it wouldn't be an issue.

In this scenario I recommend you to use one of your replica set with slaveDelay.


Why is there a separate describe and explain statement in MySQL

This is another example of the liberties the MySQL developers have been taking when interpreting the SQL standard (if they ever read it, that is).
DESCRIBE [OUTPUT], with slightly different semantics, is a statement prescribed by the SQL standard, and its purpose is to provide information about the result set (column names and data types) of an executable SQL statement that returns a cursor (result set). This information is inserted to the SQL Descriptor Area (SQLDA) that an application can then access in order to properly process the result set. The standard does not allow for the form DESCRIBE <table name>. This means that the DESCRIBE implementation by MySQL is non-standard.
EXPLAIN is not a part of the SQL standard, but most major DBMSes use it, with slightly different syntax, to generate a statement execution plan in some form. It applies to all executable statements, not just those that return cursors. In that sense the MySQL implementation of EXPLAIN <statement> follows this convention.


PostgreSQL - Querying Second Table with Multiple Results

Test:

SELECT e.name,
       MIN(a.startDate) startDate,
       MAX(a.endDate) endDate,
       CASE WHEN SUM(a.Status != 'Approved')=0
              THEN 'Completed'
              ELSE 'Pending'
         END status
FROM Events e
JOIN Activities a ON e.id = a.eventId
GROUP BY e.name

If fail replace SUM(a.Status != 'Approved') with SUM(CASE WHEN a.Status != 'Approved' THEN 1 ELSE 0 END).


Deny access to information schema in SQL Server

You should be able to just deny permissions on the entire sys and information_schema schema as a whole:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

That should basically just prevent that user from doing any selects in those two schemas.


Why does SQL Server allow a float(53) value that falls outside the range specified by Microsoft?

I suspect this is a validation error (ie. a bug). Wouldn't be the first, specially in 2005-2008 (there was a big data purity fixes push in 2012 I think). I suspect that a DBCC CHECKDB WITH DATA_PURITY will complain about the value.
SQL Server stores floats as IEEE 754, same way as .Net and C++ represents them. 64 bit can represent values way above and bellow the 2.23E-308, as you already found out about 4.94065645841247E-324 (ie. 0x0000000000000001 interpreted as a float). But such values fail another IEE754 requirement, namely reproducibility:

The standard requires operations to convert between basic formats and external character sequence formats. Conversions to and from a decimal character format are required for all formats. Conversion to an external character sequence must be such that conversion back using round to even will recover the original number. There is no requirement to preserve the payload of a NaN or signaling NaN, and conversion from the external character sequence may turn a signaling NaN into a quiet NaN.
The original binary value will be preserved by converting to decimal and back again using:

5 decimal digits for binary16
9 decimal digits for binary32
17 decimal digits for binary64
36 decimal digits for binary128

This requirement places the limit at documented numbers:

1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

The fact that ADO binding allows basically any combination of bits to be passed as the value and the SQL Server accepts the binding buffer w/o validation results in the behavior you noticed. Technically, the data you inserted this way is a corruption. Again, I suspect that a) newer versions of the engine validate the buffer before write (in other words they catch the invalid value) and b) WITH DATA_PURITY check will report the problem (even on 2005/2008/R2 versions).


MS SQL 2014 AlwaysOn High Availibiliity, Error 15023, not about to connect on failover

It's likely that your Test-User account has different SIDS on the primary and replica servers.  The best way to resolve this is drop the Test User on the Secondary, generate a create user statement from the primary with the proper SID (e.g. sp_revlogin, dbatools.io, or another tool of your choice) and then run that generated CREATE USER statment against the secondary.  This will create a user that has the same SID on both replicas so you won't get this error going forward.

Another option, and one that I would recommend instead, is to convert this database to a Partially Contained Database so that all SQL Logins you create with the database are automatically transferred over to any replicas you setup.  This is a very handy feature (if the limitations don't adversely affect you) for databases in Availability Groups and will help minimize the administrations headaches that come with them.


Ingredients for SQL Server Version decision

The is some information directly from MS that you might find useful: Breaking Changes to Database Engine Features in SQL Server 2016 and Discontinued Database Engine Functionality in SQL Server 2016, which indicate that officially there is very little by way of breaking changes. Those pages link to similar information about older versions too.

As a general rule I recommend going for the latest version for new installations once that version has been released long enough for any major bugs to have been found by someone else and fixed by MS, and SQL2016 had a large enough beta release that anything major should have been found already. But I would also recommend a full regression check of the applications to cover the possibility of:

reliance in your application on undocumented/undefined behaviour that may differ in the newer version: changes to officially undefined behaviour won't be listed in places like the pages linked to above.
performance issues due to changes in the query planner: the new carnality estimation method in 2014 generally lead to noticeable improvements but in a some use cases they had the opposite effect and hints are needed to push the query planner back towards the optimal plan, I'm not aware of such a significant change in that area in SQL2016 but there will no doubt be smaller changes that could have a detrimental effect for a minority of queries/statements.
new and interesting bugs as yet undiscovered.

As is sounds like you have recently performed a large scale regression test, you might not be able to justify (to the business) doing so again this soon in which case sticking with 2014 would be the safer option.


Can a SELECT with SELECT Subquery cause deadlock with Update query in SQL Server?

From my experience, when you use READ COMMITTED and the concurrency is high, deadlocks will happen. The frequency of deadlocks will grow with concurrency and length of transactions.

Reducing the transaction isolation level could improve things to the point that you will not have deadlocks due to SELECT statements, but still, there might be a chance to have deadlocks due to the UPDATE statements.

The best solution is to just assume that deadlocks are possible and will happen and to allow for a certain number of retries.


I try to use select union with order by and now i can select it but it seem like it not order by date with what i expect why?

Looks like you are sorting by close_date column in all the tables in DESC order then UNION. Instead of this approach SELECT UNION all the tables and, in the final result you can ORDER BY the Close Date:data:200 as DESC.

The working query will be:

SELECT T.*
FROM (
    SELECT sale.NAME AS "Name"
        ,sale.responsible AS "Responsible:data:200"
        ,sale.action_type AS "Action Type:data:200"
        ,sale.id_customer AS "Id_Customer"
        ,sale.workflow_state AS "Workflow:data:200"
        ,sale.close_date AS "Close Date:data:200"
        ,sale.modified_by AS "Done By:data:200"
    FROM `tabsales_action` sale
    WHERE sale.workflow_state = 'Done'

    UNION

    SELECT mfi.NAME AS "Name"
        ,mfi.responsible AS "Responsible:data:200"
        ,mfi.action_type AS "Action Type:data:200"
        ,mfi.id_customer AS "Id_Customer"
        ,mfi.workflow_state AS "Workflow:data:200"
        ,mfi.close_date AS "Close Date:data:200"
        ,mfi.modified_by AS "Done By:data:200"
    FROM `tabmfi_loan_action` mfi
    WHERE mfi.workflow_state = 'Done'

    UNION

    SELECT credit.NAME AS "Name"
        ,credit.responsible AS "Responsible:data:200"
        ,credit.action_type AS "Action Type:data:200"
        ,credit.id_customer AS "Id_Customer"
        ,credit.workflow_state AS "Workflow:data:200"
        ,credit.close_date AS "Close Date:data:200"
        ,credit.modified_by AS "Done By:data:200"
    FROM `tabin_house_credit_action` credit
    WHERE credit.workflow_state = 'Done'

    UNION

    SELECT service.NAME AS "Name"
        ,service.responsible AS "Responsible:data:200"
        ,service.action_type AS "Action Type:data:200"
        ,service.id_customer AS "Id_Customer"
        ,service.workflow_state AS "Workflow:data:200"
        ,service.close_date AS "Close Date:data:200"
        ,service.modified_by AS "Done By:data:200"
    FROM `tabservices_planning` service
    WHERE service.workflow_state = 'Done'

    UNION

    SELECT account.NAME AS "Name"
        ,account.responsible AS "Responsible:data:200"
        ,account.action_type AS "Action Type:data:200"
        ,account.id_customer AS "Id_Customer"
        ,account.workflow_state AS "Workflow:data:200"
        ,account.close_date AS "Close Date:data:200"
        ,account.modified_by AS "Done By:data:200"
    FROM `tabaccounting_action` account
    WHERE account.workflow_state = 'Done'
) AS T
ORDER BY T.`Close Date:data:200` DESC


Try by using a CROSS/OUTER APPLY solution:

It should return one record ordered by ASSESMENT_DATE DESCfor each record of main table dbo.PB_BASIC

SELECT      TOP (100) PERCENT
            B.PATIENT_CODE,
            B.NAME_FULL,
            A.ASSESSMENT_DATE,
            A.QUESTION_TEXT,
            A.ANSWER_TEXT
FROM        dbo.PT_BASIC B
CROSS APPLY (SELECT   TOP 1
                      PATIENT_ID,
                      ASSESSMENT_DATE,
                      QUESTION_TEXT,
                      ANSWER_TEXT
             FROM     dbo.VW_ASSESSMENT_DATA
             WHERE    PATIENT_ID = B.PATIENT_ID
             AND      (ANSWER_TEXT LIKE '%Level %')
             AND      (ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102))
             AND      (QUESTION_TEXT LIKE '%Plan/Class%')
             ORDER BY ASSESSMENT_DATE DESC) A
ORDER BY   B.NAME_FULL;


Database recovery pending on external drive

As previously mentioned, make sure the SQL service account has permissions on the files.
Here is a quick snippet to help you do that:

-- From SQL:
-- get full path for master
SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

-- get the service accounts
SELECT servicename, service_account FROM sys.dm_server_services

# From Powershell (run as admin) and replace with values you got from the previous step

# replace with master db path
$masterDBLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL1\MSSQL\DATA\'

# replace with SQL service account
$serviceAccount = 'DOMAIN\svc_DatabaseEngine'

$mdf = 'G:\SQL_DATA\TEST_NEW.mdf'
$ldf = 'G:\SQL_DATA\TEST_NEW_log.ldf'

$Acl = Get-Acl $masterDBLocation
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($serviceAccount, "FullControl", "Allow")
$Acl.SetAccessRule($Ar)

Set-Acl -Path $mdf -AclObject $Acl
Set-Acl -Path $ldf -AclObject $Acl


Suppress successful insert statements or review errors on large inserts in Oracle 12c

SET FEEDBACK OFF suppresses the insert notifications.

See the documentation.

You can also use SET ERRORLOGGING to log errors to a table of your choice. Again, see the documentation.


Generally one row per respondent, and one column per question item.  A battery of ten check-all-that-apply questions would thus get ten columns (either boolean or integer).  Each open end gets a String field.  During analysis, you can get fancy and aggregate questions, stack repeated batteries of questions, etc. in SAS, SPSS or Excel.


Unable to enable publishing on restored database

After some further investigation through profiling, we found the issue.

We traced the whole process of enabling publishing on the database by running

sp_replicationdboption @dbname = 'restoreddb', @optname = 'publish', @value = 'true'

and zeroed in on the part that creates the sys tables:

EXEC @retcode = sys.sp_MScreate_pub_tables

This is where it gets interesting:

EventClass  TextData
44  IF not exists (select * from sys.objects where name = 'sysarticles')
45  IF not exists (select * from sys.objects where name = 'sysarticles')
44  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
45  IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
44  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
45  IF not exists (select * from sys.objects where name = 'sysschemaarticles')
44  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
45  IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
44  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
45  IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
44  IF not exists (select * from sys.objects where name = 'syssubscriptions')
45  IF not exists (select * from sys.objects where name = 'syssubscriptions')
44  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
45  IF not exists (select * from sys.objects where name = 'sysarticleupdates')
44  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
45  IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
44  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
45  IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
44  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )

45  CREATE TABLE dbo.systranschemas
            (
                tabid int not null,
                startlsn binary(10) not null,
                endlsn binary(10) not null,             typeid int not null default 52
            )

With eventclass 44 = SPStmtStarting and 45 = SPStmtCompleted.

So what's happening here? It looks like the 'IF NOT EXISTS' statements were returning false for all the sys tables EXCEPT systranschemas, but we checked that these tables don't exists at all under System Tables.

So we picked a select from there and ran it

select * from sys.objects where name = 'sysarticles'

We found that there is a SYNONYM with the same object name but under a different schema in the database. This is a remnant of a migration project we did a few months ago where we moved all the tables from another database to this one under a different schema. The synonyms were created as the first step - instead of just creating them for user tables, we created them for the system tables as well.

Regardless, I am a little appalled at how lazy the coding is in this system procedure - it does not check for the 'is_ms_shipped' flag in the sys.objects, nor does it check for the schema - strangly except for 'systranschemas' where it does check for the schema and thus was able to create the table without any issues.


Should I favor using a param or a scalar function result within a SELECT?

It sounds like a situation where you could potentially fall prey to - or benefit from - parameter sniffing. In a nutshell, SQL Server will use parameter/variable values when compiling an execution plan in order to determine optimal index usage (based on column statistics). Depending on the value that's passed in, you could get very different execution plans, and very poor performance if the "wrong" value is used later. (A nice article about it.).

I'd personally calculate the date within the procedure using a scalar function, unless there's a need for whatever is calling this procedure to specify the date itself. Then you could always use the scalar function in the procedure to fall back on a default value. As long as parameter sniffing isn't giving you surprising performance changes, it's mostly just a matter of application design.


xtrabackup prepare - after dump or before restore?

The chief reason to use --apply-log until the time of restore is to support incremental backups.

If you make incremental backups, you would use --apply-log --redo-only each incremental backup, and then just before you restore, you do a final prepare with --apply-log.

Otherwise if you don't make incremental backups, there's no reason not to do --apply-log right after you make the back up.  Then it's ready to restore on a moment's notice.


The RENAME TABLE syntax allows for atomic, multiple renames. See as follows:

CREATE TABLE myschema.mytable_new LIKE myschema.mytable;
RENAME TABLE myschema.mytable TO myschema.mytable_old, myschema.mytable_new to myschema.mytable;
DROP TABLE myschema.mytable_old;

In the above it is impossible for your code to access the table during the RENAME operation, and this it cannot happen that the code "does not find the table". It just blocks until RENAME operation completes, and the table is always there.

Also worth noting that unless using Percona Server with "fast DROP TABLE patch", DROPping a table might hold locks on your database (namely the table cache) for the duration of the operation. And the operation may take a while since it involves evacuating table pages from InnoDB's cache and deleting the table file(s) from the filesystem.

Waiting for some time (eg a couple hours) after the RENAME operation can do good since your old-table's pages are more likely to be flushed out by that time, reducing the overall time from DROPping the table.


The Problem

The server option log_slave_updates needs to be enabled on both m1 and m2

The Solution

Please add the following in /etc/my.cnf on both m1 and m2

[mysqld]
log_slave_updates

then run this on both m1 and m2

service mysql restart

The Reason

Let's use your example above

m1 is Master of m2
m1 is Master of s3
m2 is Master of m1

Here is what happens when you run INSERT INTO mydb.mytable ... on m1

m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT

OK, all well and good. But, why doesn't s3 get to receive anything? Because log_slave_updates is not enabled, m1 does not save the INSERT and the original server_id posting the INSERT into its own binary logs.

If log_slave_updates was enabled on m1, now follow along your example and let's see what should happen:

m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT
m1 records INSERT into the current binary log along with server_id=23
s3 retrieves INSERT from m1 via I/O Thread
s3 records INSERT via I/O Thread into lastest relay log
s3 retrieves INSERT from relay log via SQL Thread and examines its server_id
s3 sees that the server_id of the INSERT is 23 which is not the same as s3's server_id (21)
s3 executes INSERT

This fully explains why s3 never sees anything from m2


Insert openquery results into an existing table

I will give you an example that has been working for me, it will give you an idea:

DECLARE @Radhe  TABLE(
    [n] [bigint] NOT NULL,
    [Login_Name] [nvarchar](128) NOT NULL,
    [Login_From] [nvarchar](128) NULL,
    [Account_Type] [nvarchar](128) NULL,
    THE_ORDER INT NOT NULL
    PRIMARY KEY CLUSTERED (Login_Name,N)
)

        SELECT @SQL = 'SELECT
                        [n],
                        [Login_Name],
                        [Login_From],
                        [Account_Type],
                        the_order
                      FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO @Radhe
        exec ( @sql)


SQL Server - Files timestamps unchanged so what is it that's being backed up?

In answer to your main question ("what is being backed up"), everything that is supposed to be   More seriously, you can't use those time stamps as a sign of changes happening in the database files.  SQL Server doesn't make any guarantees about how the modified date of database files will be changed or updated.

There is an interesting blog post here that attempts to find patterns with when and how the date modified stamp is updated on MDF files:

SQL Server Database File - Date Modified

Their observations line up with your situation I think: normal writes to the data file don't update the date modified stamp.  It looks like growth events (manual or automatic) would update that value, as would restarting the service (or anything else that might "open" or "close" a database - using the autoclose feature, explicitly taking the DB offline, etc).

I expect 19/12/2019 9:52 PM corresponds with a Windows or SQL Server restart, which is how several of the date / times got sync'd up.

However, again, this behavior is undocumented.  Any patterns we notice could change at any time, or have unexpected edge cases.

For SQL Server data files residing on an NTFS file system, this applies:

  The only guarantee about a file time stamp is that the file time is correctly reflected when the handle that makes the change is closed.

Though, to be clear, that's a guarantee provided by Windows.  We can't necessarily know a ton about how SQL Server creates and destroys file handles in every scenario.

David Browne (a Top Microsoft Man) mentioned this in a comment, which is useful to know:

  SQL Server keeps the database files open whenever it’s running, for better performance and protection against other processes changing them. So the attributes don’t get changed on every file write.


Does mysqldump export indices, by default?

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs:

DROP TABLE IF EXISTS `tblAccountLinks`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tblAccountLinks` (
  `ID` int(11) NOT NULL auto_increment,
  `FirmNo` varchar(10) NOT NULL,
  `CustomerNo` varchar(20) NOT NULL,
  `AccountNo` varchar(20) NOT NULL,
  `LinkType` smallint(6) NOT NULL,
  `AccessLevel` smallint(6) NOT NULL,
  `Status` smallint(6) NOT NULL,
  `CreatedOn` datetime NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=27023 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tblAccountLinks`
--

LOCK TABLES `tblAccountLinks` WRITE;
/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;
INSERT INTO `tblAccountLinks` VALUES (1,'F0001','C001','T00000001',1,2,1,'2008-06-30 07:55:43'),(2,'
F0001','C001','T00000002',2,2,1,'2008-06-30 07:55:43'),(3,'F0001','C002','27601012',1,2,1,'2008-06-3 ...

The line after LOCK TABLES is

/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;

This is what the --disable-keys option embeds in the mysqldump.

Also, this is embedded after all the INSERTs are done

/*!40000 ALTER TABLE `tblAccountLinks` ENABLE KEYS */;
UNLOCK TABLES;

CAVEAT #1

  DISABLE KEYS and ENABLE KEYS were
  implemented to disable the reloading
  of nonunique indexes while a table is
  being reloaded. Primary Keys and
  Unique Keys are not disabled. They are
  loaded at the same moment the INSERTs
  are being. Once you ENABLE KEYS, the
  nonunique indexes are rebuilt via
  sorting (or using the MyISAM key cache is there isn't
  enough available memory)

  Unfortunately, DISABLE KEYS and ENABLE
  KEYS only work for MyISAM tables, not
  InnoDB.

CAVEAT #2

You do not have to --disable-keys. You could disable DISABLE KEYS (no pun intended) them with --skip-disable-keys:

  -K, --disable-keys  '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
                      '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
                      in the output.
                      (Defaults to on; use --skip-disable-keys to disable.)

This could result in a slower load and a potential lopsiding of the index pages for nonunique indexes.

CAVEAT #3

You can dump the actual InnoDB tablespaces (MySQL 5.5.12)

  -Y, --all-tablespaces
                      Dump all the tablespaces.
  -y, --no-tablespaces
                      Do not dump any tablespace information.


ERROR: could not find array type for datatype information_schema.sql_identifier

Just cast column_name to text to bypass the error:

SELECT ARRAY(
    SELECT column_name::text
    FROM information_schema.columns
    WHERE table_name ='gis_field_configuration_stage'
);

Its original type is information_schema.sql_identifier and its happens that an array of that type is not provided in the predefined types.


You are looking in a bad way. Those security limits can get back to bite you.

The right solution is to sanitize user input etc. to prevent injection to happen.

SQL Injection isn't an error on the database side instead it is on the application side.

If you try to protect only db somebody will attack the app server and once there he can do full dump.


Count rows for each latest status of tickets

Use
WITH cte AS ( SELECT DISTINCT
                     ticket_number,
                     FIRST_VALUE(status) OVER (PARTITION BY ticket_number
                                               ORDER BY datetime DESC) last_status
              FROM test )
SELECT last_status, COUNT(last_status)
FROM cte
GROUP BY last_status

fiddle
for to obtain needed statistic in vertical form. If you need pivotted output strictly then use CROSSTAB() or similar, or emulate it using 4 CASEs.


How to visualize joins between inner queries to their respective outer queries?

Just break it down into stages and consider what the output of each stage represents

SELECT *
FROM Ninjas AS N
INNER JOIN NinjaWeapons AS NW
    ON N.WeaponId = NW.WeaponId

This can be thought of as "all ninjas with at least one weapon along with their weapons". The data model does not appear to tie weapons to particular movies.

So you are now left with the simpler query to analyse (there is no value in including TOP 1 in an EXISTS so that has also been removed.)

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT *
    FROM WeaponOwningNinjasWithTheirWeapons AS N
    WHERE N.NinjaId = NM.NinjaId
)

There seems to be a normalisation issue here as apparently NinjaMovies will contain multiple rows for the same movie starring more than one Ninja and repeats the title in each one - but perhaps NinjaMovies is itself a view resulting from a join of normalised tables.

In any event SELECT NM.MovieId, NM.MovieTitle clearly selects the Movie Ids and Title from it so the only thing left to analyse is the EXISTS.

This simply says to only return rows where the corresponding Ninja exists in the WeaponOwningNinjasWithTheirWeapons result set.

So to sum up. The query returns all rows in NinjaMovies where the corresponding Ninja has at least one weapon.

The semantics of the query seem a bit odd as NM.NinjaId is not projected so in the event that the movie stars multiple ninjas - some with weapons and some without - there is no indication as to which led to the inclusion of the row. If you don't care about this then you should probably add a DISTINCT to remove duplicates in the event that the movie stars multiple weapon bearing ninjas.


Why use separate databases for high traffic/volume tables?

You've got two different questions in here that I'll answer separately.

Q: Can the performance of one table suffer from other large tables in the same database?

Yes, during insert/updates/deletes. SQL Server only has one log file per database. When you insert/update/delete, your transaction doesn't complete until your data is written into the log file.

Let's say we have two tables:

Documents - stores large XML representations of stuff. Has just two fields, an ID and an XML field, but that doesn't mean the records are small. When we do an insert into here, we need to write the entire XML to the log file, and our XML documents tend to be 5-10MB each.
Orders - stores incoming orders from our web site. It's a small table - no XML or VARCHAR(MAX) fields, just a few IDs for customer and product. When we do an insert here, it's just 1K.

If both of those tables are in the same database, and we try to place orders while we're writing documents too, the performance of order transactions can be slower.

Now, this is an extreme example, but generally this is why I recommend storing mission-critical, high-value, small-width-record tables in a separate database from less-critical, lower-value, large-width-record tables. However, if you need to restore both sets of tables to the exact same point in time, or fail them over together between servers, then they need to be in the same database. SQL Server doesn't guarantee transactional consistency between databases on failover with things like AlwaysOn Availability Groups.

Q: If I have fast-changing data, slow-changing data, and never-changing data, should I separate them into different databases?

Yes, for a few different reasons:

Different backup schedules - I need to make sure the fast-changing data is backed up as fast as possible. Generally people use a single backup job to do their full backups for all of their databases, and often they don't do transaction log backups at the same time they're doing full backups. If I've got a 10GB fast-changing database and a 1TB never-changing database, and if I use a single full backup job, that means I may not be getting any transaction log backups while my 1TB full backup is going on. That's a bigger window for potential data loss. You could mitigate this with separate backup jobs or tighter schedules. What you really want to do is avoid backing up never-changing data - less IO loads, less backup files to shuffle off to tape.
Different index rebuild & stats update jobs - I don't want to be doing index rebuilds/defrags on large databases that aren't changing. Ideally I rebuild all their indexes with 100% fill factor, update the statistics with fullscan, and set the database to read-only. Presto, dense pages, faster performance without locking hassles. (Plus, as a paranoid DBA, I can just make sure nobody's changing it that way.)
Different HA/DR strategies - if I've got a large, stable database that rarely changes, I might put it in read-only mode, and then restore that read-only copy to my disaster recovery environment. When I'm copying data from my primary data center to my secondary, then I only have to worry about the frequently-changing data.


transactional replication-snapshot agent failure ... can anyone help me out

Looks like you timed out.
Add this to your snapshot agent job step:
-QueryTimeOut 3600
The default is 1800 seconds which is 30 minutes.  Try giving it 1 hour or more.
http://msdn.microsoft.com/en-us/library/ms146939.aspx
http://msdn.microsoft.com/en-us/library/ms151326.aspx


You can possibly limit the output of foo():

SELECT foo() LIMIT 10000;

Or, most probably better, avoid using arrays altogether (pass the set, not an array made of it).  For this, you will, of course, have to rewrite my_func().  Thinking about it, I can imagine that this will be beneficial from a performance point of view, too.


SSIS Create New Folders On The Fly When Writing To A Flat File Connection

You need to use a File System Task and set the Operation to Create Directory. Pass it a variable of the file path without the filename and it should create the directory.

See this blog article for a good example of creating folders from dynamic paths.


Best practices for ensuring up to date sequences after table import

For sequences the best way is to copy the current value from dba|all|user_sequences.last_number. Fetching the current value from the sequence is not safe; it can have higher values in other instances of the database.
Also make sure that your app is not using sequences for ordering ....


Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not

Running OPTIMIZE TABLE on all the tables in the Master is a very bad idea. Why ???

Since the Master has innodb_file_per_table disabled, running OPTIMIZE TABLE on all the table in the Master would make ibdata1 grow. I discussed this in my old StackOverflow post like 5 years ago : Howto: Clean a mysql InnoDB storage engine?

  Can I run OPTIMIZE TABLE ?

  Unfortunately, running OPTIMIZE TABLE against an InnoDB
  table stored in the shared table-space file ibdata1 does two things:

  Makes the table’s data and indexes contiguous inside ibdata1
  Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

You could run OPTIMIZE TABLE on the Slave as follows:

OPTIMIZE_SCRIPT=/tmp/OptimizeInnoDBTables.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE engine='InnoDB' ORDER BY (data_length+index_length) DESC"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${OPTIMIZE_SCRIPT}
mysql ${MYSQL_CONN} < ${OPTIMIZE_SCRIPT}

This will shrink every .ibd file. Again, DO NOT RUN THIS ON THE MASTER !!!

GIVE IT A TRY !!!


How to keep off-site developer databases up to date with production?

I manage a DB environment for several tier 1 telcos and our data/environments are distributed across the world, there are many ways you can achieve what you're looking for but it depends on your pain points.

Questions:

-How big are the databases, how far are the nodes (or more importantly what is the latency between them), and how much bandwidth do you have?

-How much transaction log activity do you generate?

-How often do you want to restore?

-Are you enabling data compression on the backups?

Some Lessons Learned

-One thing I noticed with copying data across the world is that the Windows CIFS protocol absolutely sucks for doing this.  It is highly inefficient.

We ended up using HTTPS and issuing a GET request.  It was over 10x faster copying backups from Asia to Los Angeles.

Using Multithreading options in Robocopy or other copy programs was useful if you break up the backup into multiple files.

Some Options:

2 Surprising Linux Solutions

A solution I really like is to RSYNC with Linux servers on each end and mount the backup folder.  This is really nice as backups are automatically synced using a protocol that is efficient for WANs.  If you don't want to sync ALL of your backups, mount another drive on your linux server that really mounts to a windows share that you will place backups in.  The RSYNC will take care of it from there and do it much faster than CIFS.  Linux protocols are much better for long distance file copying than Windows CIFS based on my experience.  I spoke with some of CCIEs and they stated the same.
You could also look at a VM WAN Optimizer (Google or 'Bing' WAN optimizer or get more specific, for example high latency high bandwidth WAN optimizer or low latency high bandwidth, whatever your environment is)

SQL Transaction Log Shipping Using FTP is not scary at all.  I had a junior .Net developer get it running fast, although I was impressed and got him hired at another company I worked at.  Problem is you might send a lot more data in a 24 hour period if there are a lot of UPDATES but not INSERTS using log shipping, instead of just copying backups.

SAN File System Block Level Replication: Not sure if you have hardware that supports this, but many mid range SANs allow you to replicate live data to the other ones.  If yours does, this is a great option as it has 0 sql server overhead, unless your network pipe is capped in which case you have other issues.

Transaction replication to a central database:  You could create a replication scheme where you have an up to date copy of the data.  You could then take your backups from that copy so it doesn't have to copy full backups across the wire.

Let us know some of your pain points and we can go through other options.

Also be sure to read this excellent white papers by MS if you go with replication:

Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008


Error: Unable to write inside TEMP environment variable path

I was having this issue on Windows 7 64-bit and the following solution is what worked for me (via http://igordcard.blogspot.com/2012/03/unable-to-write-inside-temp-environment.html):

Why
To understand why this error was happening, I went to my TEMP folder and read the log from the PostgreSQL installer: C:\Users\myuser\AppData\Local\Temp\bitrock_installer.log

Which mentioned an interesting detail:
Input Error: There is no script engine for file extension ".vbs".

So, I picked up another file from the TEMP folder, the actual script used by the installer:
C:\Users\myuser\AppData\Local\Temp\prerun_checks.vbs

And tried to run it through the console using cscript:
Input Error: There is no script engine for file extension ".vbs".

I researched a bit more and discovered that the type of file associated with the vbs extension had been changed. In my case, by Notepad++. After I installed Notepad++, I associated some file extensions with it (including vbs), thus causing problems when detecting the type of the script (which shouldn't happen).

Solution
Run regedit, navigate to HKEY_CLASSES_ROOT\.vbs and change the (Default) key back to the string VBSFile. In my case it had been previously altered to Notepad++_file.


In order to compile correctly SysBench 0.5 on a FreeBSD system you have to download the source code from launchpad.net (SysBench v0.5), see bug 1029088

Then, for benchmarking purposes, you can take a look into this post:

Stress test MySQL with queries captured with general log in MySQL

Or you can use the standard oltp test provided by SysBench


The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.

Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML.

The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are:

TRUNCATE can change storage parameters (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.
TRUNCATE does an implicit commit, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does.

The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations (but some direct path DML operations also skip triggers, so that's not a clear indicator).

That same documentation notes that DELETE generates UNDO, but TRUNCATE doesn't, so your statement is correct in this respects. (Note that TRUNCATE does generate some REDO so that the truncation can be replayed in case of restore/recovery.) But some NOLOGGING operations can also produce reduced UNDO (not sure about none at all), so that's not a clear indicator either in my opinion.

So I'd sum it up as:

truncate is not "transactional" in the sense that it commits and can't be rolled back, and can modify object storage attributes. So it's not ordinary DML - Oracle classifies it as DDL.
delete is an ordinary DML statement.


Are there major drawbacks/risk of using oracle blob to store files

It really comes down to cost vs. importance.  The more important the files are, the more beneficial storing them in a database becomes.  Here are some things to consider.

Pros of database file storage:

Security - The same security model your data has, same users, etc.
Backups - Usually the database is backed up more frequently, tested more frequently, and has more thorough recovery documentation.  Backups can also be faster with block change tracking.
Recovery - Old versions of files can be restored to any point in time as far back as the backups are kept.
Auditing Available.
Scalable - The more files you have the more important this becomes.
Transactional integration with other data and code.
Flashback Query (for Oracle)
Locking Available.
Read consistency.

Cons of in database file storage:

Initial setup may take longer.
Specific expertise required.

Tom Kyte said it this way:

  If this data is valuable to your business, if this data is necessary
  for your business, if the loss of this data would damage your ability
  to do business - it has no business NOT being in the database.


Accurately simulate SQL Server work load with diskspd

Now my question is what parameters do we pass in to diskspd to validate this kind of behavour.

We really don't know enough about the clustered index to really make those kind of assumptions. For example, you specify MaxDOP 6 but was it run with MaxDOP 6? Does the clustered index have data in it stored in off-row? How much read-ahead was used?

We can approximate sorta kinda the workload given the disk results, but it would, at best, approximate the workload of scanning your clustered index of this specific table with MaxDOP 6. Since this is a secondary replica that's readable and servicing reports, I highly doubt that's indicative of your overall workload.

DiskSpeed is best when trying to test the storage and validate it against a DiskSpeed on another server, making sure you can hit your latency and bandwidth requirements. It's not made to act like SQL Server and you'll rarely be able to get it to behave in such manner due to multiple other influences that do not exist in DiskSpeed (such as waiting on locks, latches, coordinators, spinlocks, etc.).

  Even if I bump up the thread to 80, I am able to push way higher IO's through diskspd on the older disk than what SQL Server could.

See above. SQL Server works cooperatively in scheduling, you won't get more throughput through SQL Server than you will through dedicated threads for testing such as this, which isn't doing nearly as much as SQL Server (not that it's a good or bad thing, it's just different as they have different uses.)

  Is what I am seeing have something to do with the file that diskspd uses for testing and that somehow is compressed by san?

Benchmarking software, of which DiskSpeed is no exception, has been targeted by storage vendors and has built in algorithms to detect workloads and artificially speed them up. I'm not saying that's what you're seeing but there are multiple factors at play, here. Some things to think about:

Size of SAN cache
Size of SAN cache devoted for read
HBA settings such as Queue Depth
Multipathing setup
IO Group port statistics
Any SVCs in front of the SAN
Any specialized drivers that may also be caching, and their cache sizes
File data, such as completely random, all zero's, repeating patterns
File initialization such as setting a high watermark vs writing random data
Thin or thick provisioning of disk resources

Once you know the cache sizes and initialization types of the files along with the settings per server such as HBA settings and multipathing setup, then you can start to investigate the differences in values between the servers/SANs.

Since we don't know any of this data, at best it'll be pure speculation though given that flash is obviously faster could be a majority, or that it's a new array and less systems are on it thus it's less loaded could be a major factor. Really, more investigation on the SAN backends from the SAN admins is required, such as average IOPs while the tests are running on the entire device, max specifications for the devices, IO group/port statistics on load, etc.

  We used dskspd and ran various tests and compared to the existing nodes and the results were not drastically different.

This means that either the workload does not hit any limitations or exceed maximums of the SAN, or it means that there are infrastructural issues that are artificially limiting your latency/throughput.

  We tried different file size and different block sizes.

The filesize will need to be large enough that it can't be entirely in read cache of the device, unless of course that's what you want to test. You'll need to check the sizes, generally 2x to 4x the size is enough to not be able to cache the data.

Block size changes and number of threads will determine either latency or throughput. It's unclear which you're attempting to test from the results. Testing each has different setups, generally when testing latency a very small block size is used such as 512 bytes or 4k with 4k being the most prevalent these days. This will lead to a maximum number of IOPs with very small sizes, thus very small throughput. To test throughput, a large block size is used, 2MB, 4MB, 8MB and latency tends to be much higher but the maximum throughput is now tested.

  Does anyone know how to create a file that is less compressible by the sam or use an actual sql server data file for the test with diskspd?

DiskSpeed doesn't know what SQL Server is, so while you could use a SQL Server data file... DiskSpeed wouldn't use it like SQL Server does. It would just be a file that it reads offsets and data.


Translating a query from MS Access to SQL Server

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join.

So in your case change the top row from

DELETE category.*

to

DELETE category


The EXECUTE permission was denied on the object xp_availablemedia

The reason is that the user has enough permissions to perform a backup, but not enough permissions to enumerate the filesystem on the server, which is what SSMS does behind the scenes.

This behaviour is described in this support page, which suggests adding the login to the sysadmin role.

To work around it, add a backup device with sp_addumpdevice and let the user backup to that device.


if you want to access a database using a login you created, you need some decisions to make,
first : you have to create a user in database for the login you created, it could be a same name or a different name for it.
you also need to decide what amount of permissions you need for the login,

the sample below is for creating a login with a specific password, that is going to access adventureworks2012 with db_datareader permission over database:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test] GO


How to calculate the sums of two columns, when that two columns brought by join queries?

What you are primarily looking for is a SUM command run on your queries where you will GROUP BY jobId.  That will allow you to get the SUM of your hours worked as well as the cost of the parts.

I can see two options that you can utilize.  Now there may be some variables I am missing but this should give you a framework to work with.

Basically what you need to do is get the SUM separated out which you should and then the math is fairly easy after that.  You are able to get SUM value by using the GROUP BY clause in a SELECT query.

Sub Queries As "Tables"

With this approach you create a sort of "table" which has your SUM pre calculated which is then added in the main SELECT.  (I am assuming you have some sort of a JOB table which is where the jobId column comes from).

SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
    LEFT OUTER JOIN
    (
        SELECT  j.jobId,
        SUM(p.productUnitPrice) AS SumOfProductUnitPrice --This becomes the name of this "column" in the main SELECT statement
        FROM    JobRequiredProducts J
        JOIN    Product p
          ON    p.productId = j.productId
        GROUP BY J.JobID
    ) A
        ON A.JobID = J.JobID
    LEFT OUTER JOIN
    (
        SELECT  j.jobId,
        SUM(j.hoursOfWork) AS SumOfHoursWorked --This becomes the name of this "column" in the main SELECT statement
        FROM    JobRequiredLabours J
        JOIN    Labour L
          ON    l.labourId = j.labourId
        GROUP BY j.jobId
    ) B
        ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE Command can change to return the same information for a different jobID

CTE's (Common Table Expression)

An alternative that also works is using a CTE (a Common Table Expression) which is almost like a VIEW that exists only for this query.  It allows you to pre-define a query and utilize it later in a different command.  In this particular instance we are using it to pre-generate the SUM so we can then use it later in a main SELECT.  The benefit of this is the logic of how the CTE's are created is not in the main SELECT which helps with readability.

--All CTE's need to be precided with a ";" if it is not the first statement in the batch
;
WITH ProductSum (JobID, SumOfProductUnitPrice)
AS
(
    SELECT  j.jobId,
    SUM(p.productUnitPrice) AS SumOfProductUnitPrice
    FROM    JobRequiredProducts J
    JOIN    Product p
      ON    p.productId = j.productId
    GROUP BY J.JobID
), --Comma seaparates the two CTE's that could be used in the next statement
HoursWorkSum (JobID, SumOfHoursWorked)
(
    SELECT  j.jobId,
    SUM(j.hoursOfWork) AS SumOfHoursWorked
    FROM    JobRequiredLabours J
    JOIN    Labour L
      ON    l.labourId = j.labourId
    GROUP BY j.jobId
) --No comma follows the last CTE in the chain

SELECT J.JobID,
A.SumOfProductUnitPrice + (B.SumOfHoursWorked * 30)
FROM Job J
    LEFT OUTER JOIN ProductSum A
        ON A.JobID = J.JobID
    LEFT OUTER JOIN HoursWorkSum B
        ON B.JobID = J.JobID
WHERE J.JobID = 1 --WHERE command can change to return the same data for a different jobId

These queries will get slightly more complicated, if a job requires more than one instance of a single part AND if that is held through some kind of QUANTITY COLUMN or if the Hourly Rate for a given person is not 30 every time.  But hopefully this will give you something to work with as a start.


Count and sum, counted and summed values

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

You can get same result using a single query:

SELECT
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(invoice_amount) AS total_invoice_amount
FROM
  order_items;


SSIS 2012: What rights are needed to connect to SSDB via DMO

The answer is contained in http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

  1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a
  folder where the project is in: a.Map it to a member of the SSISDB
  database role -- public. (This leverages SQL security mechanism.)
  b.Grant it Read to the folder, and grant it Read/Execute to the
  project. (This uses SSIS Catalog security mechanism.)

  2.To allow a login (user or group) to be able to read/execute all projects in a folder: a.Map it to a member of the SSISDB database role
  -- public.  b.Grant it Read/Execute/Read Objects to the folder.

  3.To allow a login to be able to do anything on SSISDB: a.make it a member of the SSISDB database role -- ssis_admin.


How can I query data from a linked server, and pass it parameters to filter by?

The performance problem actually had to do with the LEFT OUTER JOIN tables. If I changed them to INNER JOIN, or if I excluded their data from the SELECT columns, the query ran fine.

What I ended up doing was creating a View on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds table.

I didn't think this would work since I thought joining everything and pulling it down to the second server before filtering was the same as what I was doing now, and would lead to the same performance problem, but surprisingly that doesn't appear to be the case.

CREATE VIEW MyView
AS

SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id

GO

and

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

All the joined columns were correctly indexed, however according to this answer

  Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

And this one

  Let the linked server do as much as possible.
  It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

so I am guessing that the query plan used for the query was not using the Indices defined, and SQL Server was generating a poor query plan for the LEFT OUTER JOIN tables.


MySQL Workbench sync keeps requesting the same changes

It is possible that this is a bug in MySQL Workbench. You should confirm this with some test database to see if you can replicate the issue and file a bug report.


I looked at the trim function and it removes the character from the beginning or the end of the string.  I'd bet that the two where it's not working have spaces after the ".".  Try trimming spaces off the end first then trim the "."

select trim(trim(value,' '), '.') from mytable


Creating view will not give you any performance gain but may help you with abstraction of underlying object and management of security from users.
Using Index view can definitely help you by doing the calculations before hand but you need to be careful as explained in detail here.

SQL Server must always ensure that the data in the index and base tables is synchronized, so we need to be careful when adding indexes to views. Every time an underlying column has a new row added or deleted, or is updated, SQL Server must maintain every clustered and non-clustered index, whether on the base table or the referenced indexed view. This will lead to additional writes, which can decrease performance.
I would not create computed column in the same table as you will have redundant data (average for a student in every row for that student) in your table. Using extra storage, less number of pages in memory etc.
You should also consider indexing strategy based on how you query this table. Meaning is it grouping by one student, student + date, one student + range of date?


Undocumented Wait Type QDS Loaddb and an unusable database

Has anyone ever encountered the wait type QDS_Loaddb?

I have not, however I did some digging.

  I have found zero documentation for this wait. The description on the microsoft site says TBD.

Disclaimer: This is not meant to be an official documentation or replace any documentation that the official team publishes. It may or may not contain errors and is not meant to be an official source.

QDS_LoadDB is a wait type, associated with (you guessed it) Query Store. This is the wait type that will be encountered when Query Store is starting up for a specific database. Once startup is finished the wait should go away.

While query store is starting up, many different things happen... one of these seems to block queries against the database. Eventually, when startup is completed, those queries will be signaled and would continue to execute.

Please note that the above is a simplification of many processes and is not meant to be an exhaustive look at Query Store components.

  I have an unusable database that shows QDS_Loaddb waits any time a query is run. It doesn't matter if the query is against system tables or user tables. The wait is the same QDS_Loaddb.

Part of this I explain above but let me go a little further.

First, thank you for the comments explaining a few things about what you did and the environment. Sadly, since this was production, immediate and drastic measures were taken. Also, there was no hint of any errors in the errorlog, which is odd.

What I believe happened is the database was brought online and Query Store was initialized. There was a failure of some sort (which we won't know now, why) on the initialization of Query Store which was suppressed from the errorlog (again, no idea why). Since the initialization fails, the queries are never signaled and thus you get into the state you are in. Again, this is my assumption of what happened based on what I looked into.

The interesting item is that there should be a timeout period, where if Query Store isn't loaded within said timeout - everything should just continue on and not wait any longer. You should have easily hit this timeout - which makes we not exactly sure this is what happened... again, though, not too much to go on.

There are a few extended events that we can monitor to see if this truly was the case or not.

query_store_load_started
query_store_loaded
query_store_database_initialization_failed
query_store_catch_exception
query_store_unloaded

So what is the fix?

Unfortunately it is very hard to say why it happened - and even if my assumption was correct, we'd need to know what error was being swallowed. Since we have neither of those, and just a wait type, I'm afraid the answer is "I don't know".

Having said that, since it has to deal with Query Store (and if my assumption is correct) then turning off Query Store - without digging into the issue further - would be the only good route to go.

Disclaimer: I work for Microsoft

If you run into this again (or anyone for that matter) I would highly advise you open up a support case with Microsoft.


Unable to get the latest value added to Mysql table based on posted time

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql>

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
);

INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example
    GROUP BY id
  ) m
  ON  m.id = e.id
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle


Selecting data from multiple rows and insert it into a column

The elegance of the answer will vary with the DBMS of your choice. In it's simplest form:

select name, city as address from temp1
union
select name, phone_no as address from temp1
union
select name, pincode as address from temp1

I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do something like:

select x.name, y.address
from temp x
join lateral ( values (x.name,x.city)
                    , (x.name,x.phone_no)
                    , (x.name,x.pincode)) y(name,address)
    on x.name = y.name

Edit: The T-SQL (SQL Server) syntax uses CROSS APPLY to perform the same task, i.e.:

SELECT x.name, y.address
FROM temp x
CROSS APPLY ( VALUES (x.city),
                     (x.phone_no),
                     (x.pincode)
            ) AS y(address)

CROSS APPLY in SQL Server also often generates more effective execution plans than a bunch of union operators, so it's definitely worth a try.

Edit: As @ypercube suggests it is not necessary to join, removing the name column and taking the cartesian product gives same result

select x.name, y.address
from temp x
cross join lateral (
    values (x.city),(x.phone_no),(x.pincode)
) y(address)


According to MySQL documentation the Connections status variable shows "The number of connection attempts (successful or not) to the MySQL server." This means that it does not represent current open connections, but all connection attempts since the server has been started.

The variable showing number of current open connections, again according to MySQ docs, is Threads_connected.


How to perform backup on quite active 500G+ MariaDB Galera Cluster?

Plan A:  Using just the nodes of the cluster:

take a node out of the cluster: SET GLOBAL wsrep_desync = ON; SET GLOBAL wsrep_on = OFF; (See wsrep_desync and wsrep_on.)
back it up via Xtrabackup (or whatever).
add it back to cluster: SET GLOBAL wsrep_on = ON; SET GLOBAL wsrep_desync = OFF;
eventually IST will get that node in sync with the others.

Cons:  You lose a node for some time -- steps 2 and 4 are time consuming.
Need gcache to be big enough to allow for IST instead of SST.

Plan B:  Permanently add a slave to one of the nodes.  Take that Slave out of replication when you want to take a backup; then put it back into replication.

Con:  Requires another server.
Pros:  Not the Cons of Plan A.


Slow fulltext search for terms with high occurence

Questionable use case

  ...each CONTENT entry consists of one random word and a text string that is the same for all rows.

A text string that is the same for all rows is just dead freight. Remove it and concatenate it in a view if you need to show it.

Obviously, you are aware of that:

  Granted, it is not realistic ... But since I can't control the text ...

Upgrade your Postgres version

  Running PostgreSQL 9.3.4

While still on Postgres 9.3, you should at least upgrade to the latest point release (currently 9.3.9). The official recommendation of the project:

  We always recommend that all users run the latest available minor
  release for whatever major version is in use.

Better yet, upgrade to 9.4 which has received major improvements for GIN indexes.

Major problem 1: Cost estimates

The cost of some textsearch functions has been seriously underestimated up to and including version 9.4. That cost is raised by factor 100 in the upcoming version 9.5 like @jjanes describes in his recent answer:

PostgreSQL not using index

Here are the respective thread where this was discussed and the commit message by Tom Lane.

As you can see in the commit message, to_tsvector() is among those functions. You can apply the change immediately (as superuser):

ALTER FUNCTION to_tsvector (regconfig, text) COST 100;

which should make it much more likely that your functional index is used.

Major problem 2: KNN

The core problem is that Postgres has to calculate a rank with ts_rank() for 260k rows (rows=261011) before it can order by and pick the top 5. This is going to be expensive, even after you have fixed other problems as discussed. It's a K-nearest-neighbour (KNN) problem by nature and there are solutions for related cases. But I cannot think of a general solution for your case, since the rank calculation itself depends on user input. I would try to eliminate the bulk of low ranking matches early so that the full calculation only has to be done for few good candidates.

One way I can think of is to combine your fulltext search with trigram similarity search - which offers a working implementation for the KNN problem. This way you can pre-select the "best" matches with LIKE predicate as candidates (in a subquery with LIMIT 50 for example) and then pick the 5 top-ranking rows according to your rank-calculation in the main query.

Or apply both predicates in the same query and pick the closest matches according to trigram similarity (which would produce different results) like in this related answer:

PostgreSQL FTS and Trigram-similarity Query Optimization

I did some more research and you are not the first to run into this problem. Related posts on pgsql-general:

thread 1 (2011)
thread 2 (2012)
thread 3 (2014)

Work is ongoing to eventually implement a tsvector <-> tsquery operator.

Oleg Bartunov and Alexander Korotkov even presented a working prototype (using >< as operator instead of <-> back then) but it's very complex to integrate in Postgres, the whole infrastructure for GIN indexes has to be reworked (most of which is done by now).

Major problem 3: weights and index

And I identified one more factor adding to the slowness of the query. Per documentation:

  GIN indexes are not lossy for standard queries, but their performance
  depends logarithmically on the number of unique words. (However, GIN
  indexes store only the words (lexemes) of tsvector values, and not
  their weight labels. Thus a table row recheck is needed when using a
  query that involves weights.)

Bold emphasis mine. As soon as weight are involved, each row has to be fetched from the heap (not just a cheap visibility check) and long values have to be de-toasted, which adds to the cost. But there seems to be a solution for that:

Index definition

Looking at your index again, it doesn't seem to make sense to begin with. You assign a weight to a single column, which is meaningless as long as you don't concatenate other columns with a different weight.

COALESCE() also makes no sense as long as you don't actually concatenate more columns.

Simplify your index:

CREATE INDEX "File_contentIndex" ON "File" USING gin
(to_tsvector('english', "CONTENT");

And your query:

SELECT "ITEMID", ts_rank(to_tsvector('english', "CONTENT")
                       , plainto_tsquery('english', 'searchTerm')) AS rank
FROM   "File"
WHERE  to_tsvector('english', "CONTENT")
       @@ plainto_tsquery('english', 'searchTerm')
ORDER  BY rank DESC
LIMIT  5;

Still expensive for a search term that matches every row, but probably much less.

Asides

All of these issues combined, the insane cost of 520 seconds for your second query is beginning to make sense. But there still may be more problems. Did you configure your server?
All the usual advice for performance optimization applies.

It makes your life easier if you don't work with double-quotes CaMeL-case identifiers:

Are PostgreSQL column names case-sensitive?


Comparing the contents of 2 .nsm (naming standards) files

Export both files, see How do I open an ERwin Naming Standards file (.nsm) in Excel?

Use a file compare tool to compare the contents.

If you decide to write a program yourself, follow the advice in this Stack Overflow answer:

Comparing the contents of 2 excel files


How to get the records from Amazon Athena for past week only

@Philᵀᴹ's answer is almost there. I just used it on my query and found the fix. I would have commented, but don't have enough points, so here's the answer.

You have to use current_timestamp and then convert it to iso8601 format. Like so:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin'
and eventTime > to_iso8601(current_timestamp - interval '7' day);

You can test the format you actually need by doing a test query like this:

SELECT to_iso8601(current_date - interval '7' day);

Returns: '2018-06-05'

SELECT to_iso8602(current_timestamp - interval '7' day);

Returns: '2018-06-05T19:25:21.331Z', which is the same format as event.eventTime, and that works.


What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

NONE :-)

Starting in SQL Server 2005, Microsoft provided a mechanism to infer permissions via a "proxy". The proxy for the permissions is either a Login (for Server-level permissions) or a User (for Database-level permissions). In either case, the principal (server or database) is created from either an Asymmetric Key or Certificate, is granted the appropriate permissions, and then the permissions get inferred by signing one or more pieces of code using ADD SIGNATURE. By doing this, you are giving the code the permissions that you had granted to that server or database principal. So now you can create a stored procedure or function to do a very specific thing, and effectively grant only that code the appropriate permissions to run properly. Then you just handle permissions naturally, by granting EXECUTE / SELECT permission to only those users or roles who should be allowed to perform that action.

In terms of executing xp_servicecontrol, if you grant EXECUTE on it to a User, then that User can run it to get info on ANY service, which is not nearly as fine-tuned as allowing for only running it to get info on one particular service.

With the inferred permissions method in mind, we could create a Login and place it in the sysadmin role, but we don't even need to go that far. Starting in SP1 of SQL Server 2008 R2, a new DMV was introduced that shows status information for SQL Server related Windows / NT Services: sys.dm_server_services. The only permission this DMV needs is VIEW SERVER STATE. But again, you don't even need to grant this permission to the User in question. You can:

Create a Function (UDF or Multistatement TVF) or Stored Procedure that encapsulates the very specific usage of this DMV
Create a Certificate
Create a Login based on that Certificate
Grant VIEW SERVER STATE to that Login
Create that same Certificate in the database where the User in question exists
Sign the Function and/or Stored Procedure with that Certificate
Grant the User in question EXECUTE on that Function and/or Stored Procedure

In this setup, the User isn't given any permissions outside of running a Stored Procedure or Function. And while the VIEW SERVER STATE permission allows for A LOT of info to be seen, that permission is only used to run whatever code is in the Stored Procedure or Function. And as long as that code isn't Dynamic SQL, then that permission can't be applied outside of that narrowly-defined usage (or outside of any other code that has been signed by that same certificate).

Worried that you might slip up and accidentally grant ALTER PROCEDURE to that User and then they can put in any code that requires that permission? WRONG! Any change at all to an object that is signed will drop the signature from that object. So even if someone does manage to change that Function or Stored Procedure, it will no longer be signed by the Certificate and hence will no longer be able to infer permissions from the Certificate-based Login. And even if that User somehow has permission to add a signature to an object, that won't help without the password used to create the Certificate. And if the User has access to your passwords (or deployment scripts that might have them), then you have serious flaws in your process and standards, in which case it isn't worth the time and energy to do all of this, and just put that person in the sysadmin role ;-).

Try the following:

Initial setup:

USE [Test];
GO -------------------------------------------------------------
-- DROP PROCEDURE dbo.CheckSqlAgent
CREATE PROCEDURE dbo.CheckSqlAgent
AS
SET NOCOUNT ON;

SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

GO -------------------------------------------------------------
-- DROP FUNCTION dbo.IsSqlAgentRunning
CREATE FUNCTION dbo.IsSqlAgentRunning()
RETURNS BIT
AS
BEGIN
  DECLARE @IsRunning BIT = 0;

  IF (EXISTS(SELECT dss.*
             FROM   sys.dm_server_services dss
             WHERE  dss.[servicename] LIKE N'SQL Server Agent (%'
             AND    dss.[status] = 4 -- Running
            )
     )
  BEGIN
    SET @IsRunning = 1;
  END;

  RETURN @IsRunning;
END;

GO -------------------------------------------------------------

CREATE USER [MrNoLogin] WITHOUT LOGIN;
GRANT EXECUTE ON dbo.CheckSqlAgent TO [MrNoLogin];
GRANT EXECUTE ON dbo.IsSqlAgentRunning TO [MrNoLogin];

Test #1:

SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
dbo

status  status_desc
------  -----------
4       Running

IsSqlAgentRunning
-----------------
1

Test #2:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
MrNoLogin

Msg 297, Level 16, State 1, Procedure CheckSqlAgent, Line 7
The user does not have permission to perform this action.

Msg 297, Level 16, State 1, Line 6
The user does not have permission to perform this action.

The Fix:

REVERT;
SELECT SESSION_USER AS [SessionUser];
-------------------------------------------------------------
USE [master];

-- DROP CERTIFICATE [DoStuffCert];
CREATE CERTIFICATE [DoStuffCert]
    ENCRYPTION BY PASSWORD = N'W0rdUp,Yo!'
    WITH SUBJECT = N'Certificate for Managing Special Permissions';

CREATE LOGIN [MrDoStuff] FROM CERTIFICATE [DoStuffCert];
GRANT VIEW SERVER STATE TO [MrDoStuff];

BACKUP CERTIFICATE [DoStuffCert]
    TO FILE = 'C:\TEMP\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'W0rdUp,Yo!',
        ENCRYPTION BY PASSWORD = 'DontStartNoneWontBeNone'
    );
-------------------------------------------------------------
USE [Test];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD SIGNATURE TO dbo.CheckSqlAgent
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

ADD SIGNATURE TO dbo.IsSqlAgentRunning
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

Test #3:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

REVERT;

And now both work for MrNoLogin :-).


on my personal experience the answer is it depends

yes -
When you're not getting acceptable query response times from a properly indexed table, or tables that get a huge number of inserts/updates/deletes

if you have been monitoring query performance, or stored procedures performance, and the estimates do not match the real values
but you need to be careful with a few things when using fullscan

if you keep the AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON
and use update stats with percentage
and take good care of your indexes (when you rebuild an index it automatically update the stats for that index)

  The following example updates the statistics for all indexes on the
  SalesOrderDetail table.

USE AdventureWorks2012;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

there is another good article about UPDATE STATISTICS: the Secret IO Explosion

and this one: SQL SERVER STATISTICS: MAINTENANCE AND BEST PRACTICES
where the author mentions examples and situations.


Some users can have a fixed amount of additional fields, EAV or not?

Since Consumers and Merchants share some common data as Users they should be in a single base table, as you have already mentioned.

An EAV implementation can be successful under some well planned and controlled use.  But I see nothing in your question that would imply that an EAV solution is the right answer.

In fact, as you move further in your development you might find that not only do Merchants have some additional attributes, but so do Consumers.  If so, you might want a simple Supertype-Subtype model.

That would give you 3 tables that are related as shown below:

               USERS (contains common information)
                 |
                 |
     ____________|_____________
     |                        |
     | (details specific to   |
     |       a subtype)       |
Consumers                Merchants

If this works for you, much of the time you would only access the Users table, but when you needed additional information for a Consumer or a Merchant you would like to those details as well.

This is a pretty simple approach and avoids unneeded duplication of data.

For a perspective on EAV implementations, you might look at Aaron Bertrand's post, which explains the advantages and the limitations.

https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Note that in this case it was rapidly changing requirements which made the EAV decision a good choice.


In MySQL (which does not support FULL JOIN) you must use gathering subquery:
SELECT id_, t1.value1, t2.value2
FROM ( SELECT id_ FROM t1
       UNION
       SELECT id_ FROM t2 ) total
LEFT JOIN t1 USING (id_)
LEFT JOIN t2 USING (id_);


How to define custom path to MySQL socket file?

Your error message says it's connecting to the wrong socket:

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

You can either specify that in command line:

mysql -S /var/lib/mysql-socket/mysql.sock ...

Or in the my.cnf file under the client section (this can also be in users home directory ~/.my.cnf):

[client]
socket=/var/lib/mysql-socket/mysql.sock