MySQL still using one CPU even though extra CPU added I m SQL server DBA. My company just handover one MySQL server to manage which i m still learning. Recently extra CPU has been added to the server due to CPU is 100% but when checked on the server , MySQL still using one CPU only and doesn't utilized other CPU. Is it anything need to configure so that MySQL will use other CPU as well?

Short answer:  MySQL never uses more than one CPU per connection.  It never has, and there is very little hope on the horizon.
Exceptions:

There are some background threads that many use a small amount of CPU.

There are a limited number of cases in 8.0 of making use of multiple CPUs.

I would argue that it is not worth much effort to make use of multiple CPUs:

The orchestration of splitting up a query and recombining is a lot of overhead and complexity.
Some things virtually prevent using multiple CPUs.  Think ORDER BY.
Many queries are I/O-bound.  Extra cpus don't help in that case.
The typical "busy" system has many connections doing work.  In that case, many CPUs may actually be used.


Stored Procedure Performance using Temporary Tables I have the following Stored Procedure I would like to improve the performance of

DELIMITER $$
CREATE PROCEDURE NEXT_UNIT_OF_WORK
(
    IN batchId INT,
    IN size INT
)
BEGIN

   start transaction;

   CREATE TEMPORARY TABLE IF NOT EXISTS BATCH_ITEMS_UOW ENGINE=MEMORY AS (SELECT ID FROM BATCH_ITEMS WHERE STATUS = 'UNPROCESSED' AND BATCH_JOB_ID = batchId LIMIT size);

   UPDATE BATCH_ITEMS SET STATUS = 'PROCESSING' WHERE BATCH_JOB_ID = batchID AND ID IN (SELECT ID FROM BATCH_ITEMS_UOW);

   SELECT * FROM BATCH_ITEMS_UOW;

   DROP TABLE BATCH_ITEMS_UOW;

   commit;
END $$
DELIMITER ;

The intent is to grab a chunk of batch items via a stored procedure identified by a batch job id and restricted to a number of items (size).

The query seems to take 1 second initially and then takes consecutively longer with each call to the stored proc.

Given this stored proc is going to be called hundreds of times per minute by multiple threads, its unacceptable to take so long.

Is there a better approach to this stored proc?

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad.  The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment.  This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be.  That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer.  Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS
     WHERE STATUS = 'UNPROCESSED'
       AND BATCH_JOB_ID = batchId
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL.  This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;


MySQLdump leads to exceeding max connections. Skip-Quick as a solution? Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections to rise to max_connections which led to connections errors.
As the Server has enough RAM 64G (30G free), I increased max_connections from 150 to 300 in a first reaction.
However looking at the dump command I found the option -- quick (also enabled by default) which tells me that it is exporting row by row.
--single-transaction --routines --quick --compact

I am thinking of changing this to --skip-quick. but dare to change this since I would need to check the restore again and this is very time consuming.
Looking at the connections over time I also noticed that there are some interruptions around that time period. So maybe connections stack up since there is a block during MySQLdump?
MySQL error log shows a large amount of the following error, although not at this time points but continuously throughout the day:
Aborted connection 63182018 to db: 'mydb' user: 'test' host: 'myhost' (Got an error reading communication packets)

How would you approach this problem?

mysqldump takes only 1 connection.
There is 1 extra connection for root.  (Note the 151 vs 152.)  I hope you are not using root for your app; change that if you are.  Then allow mysqldump to be the only root process running when it is doing a dump.
Limite the number of connections requested -- by adjusting the web server (or whatever is doing connections).  Web servers tend to allow far more children than is efficient.
Disconnect (in the app) from MySQL when finished processing.
Lower wait_timeout -- this will lead to disconnecting idle threads or threads that are held unnecessarily long by a "connection pool".
Running mysqldump takes resources away from other queries; be sure to run it at off times.
Use the slowlog to find out what queries are the most impacted by the dump; then let's discuss improving them.
If you have an LVM snapshot, why bother with mysqldump?  LVM is so much faster and less invasive.


How to write a query for picking 20 items from six tables with two conditions I have six tables.

candidate,candidate_joborder,company,extra_field,joborder,user

I want to pick up 20 items from these tables with query those items are as follows.

name from company table.
title,city from joborder table.
status from candidate_joborder.
candidate_id,first_name,phone_cell,city,source,notes,current_employer,date_created,email1,desierd_pay,current_pay from candidate table.
user_name from user table.

Where as extra_fields i want to pick up

total_experiance,relevent_experiance,notice_period,education

These are user entered data i am picking up using this query.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id

And the where condition is this.

WHERE entered_by = $user_id AND date_created LIKE '$date%'

In these tables.

extra_field.data_item_id = candidate.candidate_id
candidate.entered_by = user.user_id
candidate_joborder.candidate_id=candidate.candidate_id
joborder.companycompany_id=company.company_id
joborder.entered_by=user.user_id
company.company_id=joborder.company_id

And the fiddle is here.
https://www.db-fiddle.com/f/gWr5g2222yAE8Fh1Gc6Awf/5

Finally i made the query.

SELECT can.date_created,jobo.title,can.first_name,can.phone_cell,can.email1,can.current_employer,comp.name,canstatus.short_description,can.notes,can.city,jobo.city,extra.TotalExperience,extra.RelevantExperience,can.current_pay,can.desired_pay,extra.Noticeperiod,extra.Education,can.candidate_id
FROM candidate AS can
LEFT JOIN candidate_joborder AS canjob
ON can.candidate_id = canjob.candidate_id
LEFT JOIN joborder AS jobo
ON jobo.joborder_id = canjob.joborder_id
LEFT JOIN company AS comp
ON comp.company_id = jobo.company_id
LEFT JOIN candidate_joborder_status canstatus
ON canjob.status = canstatus.candidate_joborder_status_id
LEFT JOIN (
 SELECT T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience FROM extra_field AS T
GROUP BY
    T.data_item_id) extra
ON extra.data_item_id = can.candidate_id
WHERE can.entered_by =$user_id can.date_created LIKE '$date%'"

Thank you for every one who helped me


How to get mysql automatically running when I start tomcat apache on Windows? When starting my Tomcat Apache Server, I've been having to start up mysql before than using the mysqld command. I was wondering if there was a work around this so that I could have mysql start up when I run the startup.bat command for tomcat.

Sorry, if this is an easy question. I have a very limited understanding of both databases and web servers so I wasn't able to find or understand any information I found on google.

You are going to love this.

Just install mysqld as a Windows Service. Ready ?

From the Windows DOS shell (ran as Administrator), go to the folder where mysqld.exe is located

Run this

mysqld --install

That's it. You can go to

ControlPanel
Administrative Tools
Service

When the Services Window opens, scroll alphabetically can you will see MySQL as a service

Restart Windows

When Windows comes back up, mysqld.exe should already be running.

Give it a Try !!!

See my other from ServerFault : (Feb 10, 2011 : Installing isolated instance of MySQL on Windows using silent install with .msi)


In MySQL Multiple instance, default instance is running, second instance is not I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running.

root@ubuntu:/var/lib# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running

Below is my my.cnf file :

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
basedir         = /usr
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = admin123

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err
[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

When I check my error log file mysql2.err, the error was like,

130120 18:41:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-databases/mysqld2
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test
130120 18:41:59 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130120 18:41:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130120 18:41:59  InnoDB: Initializing buffer pool, size = 8.0M
130120 18:41:59  InnoDB: Completed initialization of buffer pool
130120 18:41:59  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
130120 18:41:59 mysqld_safe mysqld from pid file /var/lib/mysql-databases/mysqld2/mysql.pid ended

Below is my apparmor file.

#include <tunables/global>

/usr/sbin/mysqld {
capability dac_override,
capability sys_resource,
capability setgid,
capability setuid,
network tcp,
/etc/hosts.allow r,
/etc/hosts.deny r,
/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
/usr/lib/mysql/plugin/ r,
/usr/lib/mysql/plugin/*.so* mr,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/{,var/}run/mysqld/mysqld.pid w,
/{,var/}run/mysqld/mysqld.sock w,
/sys/devices/system/cpu/ r,
}

My second instance data directory path is : /var/lib/mysql-databases/mysqld2/mysql
Please let me know how to fix this issue.
Thanks in advance.

I looked and saw this

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

That's a file permission issue.

Please go to Linux and run

cd /var/lib/mysql-databases/
chown -R mysql:mysql mysqld2

Now, you should be able to restart mysql for port 3307


What are Measures and Dimensions in Cubes I'm very new to Microsoft Sql Server Business  Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If it's possible with images)?

thanks

Think about a process or event that you want to analyze.

Let's say you were building Lougle Analytics, and want to analyze visits (as opposed to single-page requests) to your site. Visiting a website is a process.

A fact table represents a process or event you want to analyze, in this case it is a list of site visits. You can have as many fact tables as you want, one per process or event.

What things might be useful to you in analyzing site visits?

Information about the web browser (brand, screen resolution, ...)
Information about the user (country, state, city, ISP, based on their IP address)
Information about when the visit started (year, quarter, month, week, day)
The duration of the visit
The referring page, landing page, and exit page (title, url path)
The number of pages visited during the visit

Technically, you could put this all into one table - as you would in Excel, but that'd get real big real fast, so we'll do ONE level of normalization. Dimensional design is often just "put everything you need for an analysis in one big table", and then normalize out one level.

So your fact table will look like this:

web_browser_key          bigint
ip_address_key           bigint
start_date_key           int
referring_page_key       bigint
landing_page_key         bigint
exit_page_key            bigint
duration_seconds         int
number_of_pages_visited  int

It has a bunch of key values that refer to values in other tables†, and two non-keys. The non-keys are numeric values and are called Measures. Can you take the average of the web browser brand name? No, so it's a dimension. Can you take the average of the visit duration? Yes, so it's a measure.

The other tables are called Dimension tables, and the ip_address dimension table might look like this:

ip_address_key  bigserial primary key, /* use meaningless surrogate keys */
ip_address      inet unique,
country         text,
division        text,
locality        text,
latitude        numeric(8,6),
longitude       numeric(9,6)

Notice that it is not normalized: country could be derived from city (locality). However in a data warehouse, we care about facilitating analysis first. By facilitating analysis, I mean avoiding deep joins.

Notice that some of the data in the dimension table is hierarchical: country > division > locality. Your OLAP server (hopefully) understands hierarchies to support drill-down operations.

A logical Cube is just the collection of Dimensions and Measures that you are working with. Unlike a Rubik's cube, it can have more than 3 dimensions. Just think of a dimension as a column in your data set.

OLAP is a set of operations you can do against a data set, such a pivoting, slicing, dicing, drilling. Think of Excel PivotTables. An OLAP Server facilitates OLAP operations.

† usually without foreign keys


How to perform AVG, arithmetic operations in MySQL query I am trying to perform some arithmetic operations and avg in single query, e.g I had two column data in a table which looks like this

Temp            value         value2

1401631200      -85.5582      6.87686
1401631201      -85.266       6.54354
1401631202      -83.266       6.9174344
1401631203      -84.1243      6.900333
.
.
.
.
.
1401632400      -83.43532     6.3335622

So I perform AVG by using this query statement and it works perfectly fine and produces the average value. and SQL statement is

SELECT avg(value) as value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400

and for average of value2 I can use this statement

SELECT avg(value2) as value_avg2 FROM kami_all_dat WHERE usec >= 1401631200 AND
usec <= 1401632400

I am trying to perform some arithmetic operations after getting this average value on the fly in query. my formula looks like:

final_val = value_avg1 + (5.67 * 10^(-8) * (value2_avg + 273.14)^4)

I am trying to achieve like this, for example

SELECT (avg(value) as Average FROM kami_all_dat WHERE usec >= 1401631200
AND usec <= 1401632400) + {arithmetic operation} * SELECT (avg(value2) as
value_avg1 FROM kami_all_dat WHERE usec >= 1401631200 AND usec <= 1401632400) +
{arithmetic operations}

I am trying to achieve the single value as final_val and I am trying to perform all these operation in a single query.
But I am facing difficulty to do these arithmetic operations as mention in final_val in a single query as well as doing this average values calculation in single query.

I am beginner to database technology. Any help or guidance will be greatly appreciated. Thanks

You can simply use the arithmetic operators in the first SELECT

SELECT
   AVG(value) as value_avg1,
   AVG(value2) as value_avg2,
   AVG(value) + (5.67E-8 * POWER(AVG(value2) + 273.14, 4)) as final_value
FROM kami_all_dat
WHERE usec >= 1401631200 AND usec <= 1401632400


SQL Server - How to get corrupted rows id I have a corrupted SQL Server database.

Running command

dbcc checkdb repair_allow_data_loss

tells me there would be data loss.

Error:

  Msg 8964, Sev 16, State 1, Line 1
  Table error: Object ID 165575628, index ID 1, partition ID 72057594120568832, alloc unit ID 72057594040483840 (type LOB data). The off-row data node at page
  (1:269), slot 4, text ID 1116865298432 is not referenced. [SQLSTATE 42000]

How can I get the corrupted rows id before to run that command?

Thanks

There is probably a better way, because the following is time consuming, but I know it will work:  First, backup your corrupted database.  Second, try to bcp out the data.  As it copies the data out, it will display on the screen the number of rows copied.  When it errors, note the number of rows copied.  Those are all the rows prior to hitting the corruption.  Let's say the last entry of number of rows copied prior to hitting the error is 1078466. You narrow down your bcp command by adding -F 1078566 -L 1080466.  This will skip over 100 rows and try to copy out the next 1900 rows.  If it succeeds, then you know that the corruption is somewhere between rows 1078466 and 1078566.  Adjust your -F and -L to narrow down your search.  If it errors, then you know that the corruption is after 1078566.  Adjust your -F and -L to narrow down your search.  Repeat as necessary to locate your corrupted rows.


How To: Optimize SQL Query (SQL Server) Anyone have any suggestions for how to optimize this query? Running SQL Server 2008

SELECT
    o.id_testu,
    o.pub_int_od,
    p.tk AS stanowisko,
    p3.tu AS jednostka,
    ox.id_testu AS lk,
    ox1.id_testu AS wn,
    ox2.id_testu AS nd,
    o.jozus_wprow AS jozus_utw,
    o.priorytet
FROM
    dbo.TEST AS o
    LEFT OUTER JOIN
        dbo.POLA AS px ON
        px.id = o.id_testu
        AND px.id_definicji_pola = 143
    LEFT OUTER JOIN
        dbo.TEST AS ox ON
        ox.id_testu = px.id_testu
        AND ox.data_pub IS NOT NULL
        AND ox.pub_int_od < GETDATE()
    LEFT OUTER JOIN
        dbo.POLA AS px1 ON
        px1.id = o.id_testu
        AND px1.id_definicji_pola = 138
    LEFT OUTER JOIN
        dbo.TEST AS ox1 ON
        ox1.id_testu = px1.id_testu
        AND ox1.data_pub IS NOT NULL
        AND ox1.pub_int_od < GETDATE()
    LEFT OUTER JOIN
        dbo.POLA AS px2 ON
        px2.id = o.id_testu
        AND px2.id_definicji_pola = 147
    LEFT OUTER JOIN
        dbo.TEST AS ox2 ON
        ox2.id_testu = px2.id_testu
        AND ox2.data_pub IS NOT NULL
        AND ox2.pub_int_od < GETDATE()
    INNER JOIN
        dbo.POLA AS p ON
        p.id_testu = o.id_testu
        AND p.id_definicji_pola = 129
    INNER JOIN
        dbo.POLA AS p3 ON
        p3.id_testu = o.id_testu
        AND p3.id_definicji_pola = 127
WHERE
    (ox1.pub_int_do > GETDATE())
    AND (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    OR (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    AND (ox1.data_pub IS NULL)
    OR (ox1.pub_int_do < GETDATE())
    AND (o.id_typu_testu = 8)
    AND (o.czy_gotowe = 1)
    AND (o.data_apr IS NOT NULL)
    AND (o.data_superapr IS NOT NULL)
    AND (o.data_pub IS NOT NULL)
    AND (o.pub_int_od < GETDATE())
    AND (ox2.pub_int_do > GETDATE())
    AND (ox2.data_pub IS NOT NULL)

The overall optimization process consists of two main steps:

Isolate long-running queries.
Identify the cause of long-running queries.

Step 1. Isolate Long-Running Queries

You can isolate long-running queries using SQL Profiler. For more information about how to identify the queries that take the longest to execute, see "Isolating a Slow-Running Query with SQL Profiler" in "How To: Use SQL Profiler" in the "How To" section of this guide.

Step 2. Identify the Cause of Long-Running Queries:

Several techniques can be used to identify the cause of long-running queries. The two most commonly used options are:

Using SET statements.
Using SQL Query Analyzer options.

Using SET Statements: Use such statements as SET SHOWPLAN_ALL, SET STATISTICS IO, SET STATISTICS TIME, and SET STATISTICS PROFILE. For more information about using these SET statements, see the SQL Server product documentation.

Using SQL Query Analyzer: SQL Query Analyzer displays query execution plans in text mode or graphical mode.

To use SQL Query Analyzer

Start SQL Query Analyzer, connect to the server, and select the database that you are working on.
Paste the query into the SQL Query Analyzer window.
If you are using SQL Profiler to trace queries, the query text can be copied from the trace window and used within SQL Query Analyzer.
On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.
On the Query menu, click Show Execution Plan, and then run the query in the query window. Execution plans and query results now appear in separate panes of the window so you can view them together.
Place the mouse pointer over any icon displayed in the query execution plan. Details of the query step are displayed, including information about the execution and cost.

Before Optimize Query "write your query so you can read it... if you can't read it how can you possibly optimize it?" @Jacob H Thanks.

The complexity of the results can vary depending on the nature of the query. The following list identifies some common things to look for:

Red icons and warning messages: Look for icons that are color-coded red, and for warning messages. You might see a warning message such as "Warning: Statistics missing for the table." If the Physical operation in the query step details is in red, then it indicates that the query optimizer has chosen a less efficient query plan. The graphical execution plan suggests remedial action for improving performance. In the case of missing statistics you can right-click the icon, and click Manage Statistics to create the missing statistics.
Estimated cost: The estimated cost values indicate whether the query is I/O intensive or CPU intensive.
Table scan and clustered index scan icons: Look for table scan and clustered index scan icons, which indicate either that the table is small (not a problem), that the indexes are not properly designed, or, if you have indexes in place, that the optimizer has ignored the indexes. The Index Tuning Wizard can be used to identify the indexes needed. You need to ensure that you drop all hints before proceeding with the Index Tuning Wizard.
Queries with the highest cost: When a batch of queries is executed, a query plan is displayed for each query. The query cost is displayed for each query relative to the batch. Concentrating optimization effort on the highest relative cost query in a batch may yield the best improvements.
Some queries are inherently resource intensive — for example, queries that return large number of rows back to the caller, or queries that perform many calculations. In some cases, the only way to improve performance is to redesign the database or rewrite the query.


Method (Policy Based Management, DDL Trigger, whatever) to prevent logins with sysadmin from granting sysadmin to other logins I have scoured the internet looking for something even close to what I'm wanting to do, and all I can find is how to get around security controls to be able to grant sysadmin to a login.  (!!!)
Here's the situation; I'm a veteran DBA at a new company that has never had a DBA before, so everyone and their brother's cousin has sysadmin. That's fine, I have a plan to clean it up and reduce the number of logins that have sysadmin permission. However, now that I've started taking that permission set away from some logins, "someone" (and that's the trouble, I don't know who) is granting sysadmin to other logins.
I need a way to either report or (ideally) prevent logins that have sysadmin permission from granting sysadmin to other logins.
Of course, this is not going to be easy, because a DDL trigger for "ALL SERVER" is not going to work, because from time to time, I will need to elevate certain logins to have sysadmin (upgrade/config change/new app/etc), so my login still has to have this ability. But, all other logins, with either sysadmin or securityadmin need to be blocked from granting sysadmin (and I suppose securityadmin) to any other login...newly created or existing.

Does anyone have any idea how to go about doing this? PBM may be asking too much, because I've never been able to do anything useful with it, outside of a training class. Perhaps using a DDL trigger(s) would get the job done (???).

Thanks in advance for any suggestions and/or advice.

P.S. I need to be able to log/prevent this for both sp_addsrvrolemember and ALTER SERVER ROLE, as we have 2008 R2 instances along with 2012+ instances.

For whodunnit, there's a profiler trace event "Audit Add Login to Server Role" under security. Presumably it catches ALTER and not just the SPs specified, but that would need to be tested.

I'd suspect the guilty once caught would start assigning specific permissions instead so would trace that too.


What should I do with this large database filling up the disk? I have a SQL Server 2008 R2 Standard Edition warehouse database of around 400GB and face daily hard disk full issues. I've checked table sizes and row counts. Below is an image of what I've got:

What steps should I take in order to maintain the database size on a daily basis without any data loss?

You have a few options. However, if your database is this size, normally it would only grow.

First option, make sure you need all the data. Ask to the business if it is necessary to keep all data, if not you could write some kind of archiving process to clean up older data, making place for new data to come in.

If your SQL Server version is enterprise you could benefit greatly by implementing row compression or even page compression, this will definitely compress your tables. However before you implement this always test, because this can have a negative effect on your performance. Especially when using Page compression. If your bottleneck is CPU I would not implement it. Here is a blog on what benefits you can get.
https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

Another thing you can do is add another disk to your database and add an additional filegroup & files on that disk. Then rebuild your indexes to that filegroup, in that way you open up space inside your current files, and you can use 2 disks instead of 1 for your data storage. This will also in most cases improve your IO operations. https://www.mssqltips.com/sqlservertip/2601/using-multiple-filegroups-for-a-database-and-changing-the-default-filegroup/

If you have partitioning in place, you could store parts of tables on other disks as well. This will also make the maintenance of your indexes easier since you can rebuild a partition instead of a whole index.

Also if this is a datawarehouse database, I've seen epic datacompression using Clustered Columnstore Index on your Fact Tables.

That are the thing I would look into.


SQL Server Virtualization best practices - NUMA, HT, Cores vs Speed We run on Cisco UCS + VMware ESX + HP 3PAR.

Host blade config :
UCSB-B200-M4 , Xeon E5-2667 v3 3.1GHz , 2 sockets , 8 cores each, Hyper-Threading Active
So total 16 physical cores, or 32 logical cores.

We have Software Assurance with M$, so all are Enterprise editions, and lot of additional SQL core licenses paid for, so money is not an issue.

Our primary single OLTP SQL VM is 'dedicated' to one of the hosts, i.e. no other VMs are allowed to run on it, cos it requires all 16 cores of power. Even with that, CPU regularly runs ~60-80% , so we're planning to upgrade hardware. Questions below :

http://imgur.com/a/1t8oQ

SQL Server Processor NUMA config - all 16 processors are on 1 NUMA node - But I thought since the host has 2 sockets, there would be 2 NUMA nodes ?
vCenter Socket/Core config - 16 sockets with 1 core per socket - Is it better to change this to 2 sockets with 8 cores per socket since that matched underlying hardware ?
I recently heard at PASS conference, that if an entire VM is being dedicated to a single host, then leave Hyper-Threading disabled. Anybody agree/disagree ?
As a basic pen-paper calculation, we need to double our 'compute capacity' from 50 (16 cores x 3.2Ghz) to ~100 GHz.
Is it better to go for higher core-count across 2 sockets:
E5-4669 : 2 socket x 22 cores x 2.2 Ghz = 96.8 Ghz
or higher clock speed across 4 sockets:
E5-4627 : 4 socket x 10 cores x 2.6 Ghz = 104 Ghz

The best thing you could do is read the VMWare guide "SQL Server on VMWare Best Practices Guide". This holds all the information you need. However...

Unless the instance is configured as a "wide VM", taking vCores from each socket, then you won't see 2 NUMA nodes. With Hyperthreading enabled, then you're only using the cores on one socket. (3.3.4 NUMA Consideration).
From the best practices guide: "VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each." (3.3.5 Cores per Socket). Test this yourself though.
Where I'm working, the answer is "it depends". Some instances we leave it enabled, and some we don't. However... "VMware recommends enabling Hyper-threading in the BIOS so that ESXi can take advantage of this technology." (3.3.3 Hyper-threading)
If there are going to be no other instances on the host, then it's not as big a factor as if there's going to be more than one instance. It would be of benefit to have those allocated to separate sockets if you're looking to maximise performance. Unfortunately, you'd need to do quite a lot of testing and data capture to make an objective determination - I haven't found anything from VMWare specifically relating to this.

Go through the entire guide and read it: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf.

Other considerations are checking power schemes, because these can throttle CPUs so low that you get very poor performance, even from the best CPUs you can afford.


DBCC CloneDatabase - sys.partitions rows value wrong I'm using DBCC CLONEDATABASE to create a shell of a schema.
One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions.
as a matter of fact, the values of the row column are the ones I had in the source database.

Do you know if this a bug or "as designed"?
Do you know any other way to "refresh" the sys.partitions DMV without running TRUNCATE to all tables?

Code to reproduce -
USE [master];
GO
SET NOCOUNT ON;
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue')
BEGIN
    DROP DATABASE [CloneDBIssue];
END
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue_Clone')
BEGIN
    DROP DATABASE [CloneDBIssue_Clone];
END
GO
CREATE DATABASE [CloneDBIssue];
GO
USE [CloneDBIssue];
GO
--13213
SELECT  M.message_id,
        M.language_id,
        M.severity,
        M.is_event_logged,
        M.text
INTO    dbo.CDB_messages
FROM    sys.messages M
WHERE   M.language_id = 1033;
GO
CREATE CLUSTERED INDEX CIX ON dbo.CDB_messages(message_id);
GO
SELECT  'After Insert - Source DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
DBCC CLONEDATABASE (CloneDBIssue, CloneDBIssue_Clone);
GO
USE master
GO
DROP DATABASE CloneDBIssue;
GO
USE [master]
GO
ALTER DATABASE [CloneDBIssue_Clone] SET  READ_WRITE WITH NO_WAIT;
GO
USE [CloneDBIssue_Clone];
GO
SELECT  'dbo.CDB_messages' [Table],COUNT(*)[Rows]
FROM    dbo.CDB_messages;

SELECT  'After Clone - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
INSERT dbo.CDB_messages
VALUES (0,0,0,0,N'');
GO
SELECT  'After Insert - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);

GO
DELETE TOP(3214) FROM dbo.CDB_messages;
GO
SELECT  'After DELETE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
TRUNCATE TABLE dbo.CDB_messages;
GO
SELECT  'After TRUNCATE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO

If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database.

VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required
if the cloned database is intended for production use. Enabling
VERIFY_CLONEDB also disables statistics and query store collection,
thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS,
NO_QUERYSTORE. This option is available starting with SQL Server 2014
(12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x)
CU8.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-ver15
This is an artifact of the history of this command.  Initially it was just for troubleshooting, but was later enhanced to enable you to use the cloned database for production.


Database backup questions When we back up a database or log, what steps determine the name of the media set? Is it WITH MEDIANAME clause when we format the media? Is this information kept inside the header?
Or is this for naming a particular media (such as a particular tape)? Even though I don't see the point of naming the individual tape.

If I add a subsequent backup sets to the same media set, is it possible to change the media set name in anyway?

Thanks

According to Books Online, a media set is an:

  [O]rdered collection of backup media, tapes or disk files, to which
  one or more backup operations have written using a fixed type and
  number of backup devices.

This comprises an ordered set of backup media, which can be tape, disk files, Azure Blob Storage, etc., but (and this is important) not a combination of two or more of these.

So if you create a media set, your backup media in that media set must be of the same kind.

On to your question about the name of the media set. According to the same article (emphasis added):

  Every volume of backup media (disk file or tape) contains a media
  header that is created when [sic] by the first backup operation that uses
  the tape (or disk). That header remains intact until the media is
  reformatted.

The media set name is assigned by the WITH MEDIANAME keyword, as you surmise. It is not possible to change the name once you have created the first backup media in the set, because it is set by the first backup operation.

You will need to explicitly format the media set if you wish to change its name.


Changed root password from '' to ''. Now cannot connect to databases in Cloud SQL prompt I changed the root password using Google APIs SQL Prompt from blank to  using:

UPDATE mysql.user SET password = PASSWORD('<password>') WHERE user = 'root';

It has been documented here that SQL prompt and some other APIs Services will lose functionality when a password is set on root. (Thanks for Hasturkun's comment below).

How then do we reset the password to <blank>?

Login to MySQL using current password and then you can change password like this

mysql> SET PASSWORD FOR root@'localhost'=PASSWORD('');
Query OK, 0 rows affected (0.00 sec)

Now you can login MySQL using BLANK password.

This is not the most secure way to change password for better approach please have a look at How to Reset the Root Password.


Cost displayed by explain for insert I wonder why there is a difference in execution plan cost for the 2 queries :

create table test_insert(id int not null primary key, value varchar2(10));

--1
insert into test_insert(id,value)
values (2,'111');

--2
insert into test_insert(id,value)
select 3,'111' from dual where not exists (select null from test_insert where id =3);

Cost for query 2 is always higher (how much depends on number of rows in the table).
In my understanding not exits ... should not add any overhead - in order to enforce PK constraint  the engine must check the corresponding unique index anyway, so subquery adds extra parsing time, but not execution time or extra step to the plan.

The example is for Oracle, but I checked Postgres as well, results are similar.

For example,

--1
| Id  | Operation                | Name        | Cost  |
--------------------------------------------------------
|   0 | INSERT STATEMENT         |             |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT |       |
--------------------------------------------------------
--2
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |       |       |     3 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT  |       |       |            |          |
|*  2 |   FILTER                 |              |       |       |            |          |
|   3 |    FAST DUAL             |              |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN     | SYS_C0012345 |     1 |    13 |     1   (0)| 00:00:01 |

I'm not exactly sure what you're trying to demonstrate, in the bottom you have a NOT EXISTS. That's entails either an

anti-join or
as PostgreSQL and Oracle plan it, a correlated index only scan

PostgreSQL plan,

test=# explain analyze insert into test_insert(id,value)
select 3,'111' where not exists (select 1 from test_insert where id =3);
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on test_insert  (cost=8.17..8.19 rows=1 width=4) (actual time=0.190..0.190 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=8.17..8.19 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
         ->  Result  (cost=8.17..8.18 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)
               One-Time Filter: (NOT $0)
               InitPlan 1 (returns $0)
                 ->  Index Only Scan using test_insert_pkey on test_insert test_insert_1  (cost=0.15..8.17 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
                       Index Cond: (id = 3)
                       Heap Fetches: 0
 Planning time: 0.294 ms
 Execution time: 0.241 ms
(10 rows)

So of course it's slower. It's checking the index before it inserts, and then the btree-insertion for the PRIMARY KEY is checking again (as it always must).

If you want to roll that into a try-or-skip, use ON CONFLICT DO NOTHING

EXPLAIN ANALYZE
INSERT INT test_insert(id,value)
VALUES (3,111)
ON CONFLICT DO NOTHING;

---------------------------------------------------------------------------------------------------
 Insert on test_insert  (cost=0.00..0.01 rows=1 width=0) (actual time=0.034..0.034 rows=0 loops=1)
   Conflict Resolution: NOTHING
   Tuples Inserted: 0
   Conflicting Tuples: 1
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.054 ms
 Execution time: 0.065 ms
(7 rows)

Here is an example of your problem.. Sample schema,

CREATE TABLE f (id PRIMARY KEY);

Sample transactions,

TXN1                                TXN2

INSERT INTO f(id)
SELECT x FROM (VALUES (1))
  AS g(x)
WHERE pg_sleep(10) IS NOT NULL
AND NOT EXISTS (
  SELECT 1
  FROM f
  WHERE g.x = f.id
);

                                     INSERT INTO f(id) VALUES (1);

What happens in the above?

The EXISTS tests operating in the default READ COMMITTED ISOLATION level gets a snapshot. And then it holds up and goes to sleep.
The second transaction inserts a value.
The first transaction wakes up, and tests its snapshot for exists (or ran the test prior to going to sleep)
The first transaction tries to update the btree which is implementing the unique constraint.
The first transaction throws an error,

ERROR:  duplicate key value violates unique constraint "f_pkey"
DETAIL:  Key (id)=(1) already exists.

Switch the order of those transactions though and the first transaction sees that the key is there and never tries to insert into the btree.

Hopefully that helps you understand what's happening.


Why is the following query so bad performance-wise After asking this question, I got to thinking about why a certain query was doing all the problems.

In short, there was a query which took 500ms, I ran some execution plans and applied the recommended indexes but it only cut off about 50-60ms.

After running it again I found out that the following query(which occur in multiple locations) was really slow:

SELECT @TempCardNumber = CardNumber
FROM Cards
WHERE (CardNumber=@CardNumber or FullCardNumber=@CardNumber)

When I changed it to be

SELECT @TempCardNumber = CardNumber
FROM Cards
WHERE (CardNumber=@CardNumber)

It ran super fast, and even the removal of indexes didn't matter too much, leading me to believe this was the main bottleneck, but I don't understand.. whats so wrong about it?

OR is not usually "SARGable" when you use different columns

That is, case you can optimise for either CardNumber or FullCardNumber searches with an index on each column but not both columns. This is a "seek".

An index on both columns won't work because you it is OR: values for every row must be examined. This is a scan.

If the condition was AND, SQL Server could do a residual lookup on FullCardNumber after findng CardNumber. Seek followed by a secondary seek basically.

Anyway, try this to remove the OR scan and have 2 individual efficient seeks

SELECT
  @TempCardNumber = CardNumber
FROM
    (
    SELECT CardNumber
    FROM Cards
    WHERE CardNumber = @CardNumber
    UNION -- removed dupes
    SELECT CardNumber
    FROM Cards
    WHERE FullCardNumber = @CardNumber
    ) X

Note that the query assume you only get one row: is this intended?


copy schema across different physical databases in db2 Is there any way to programatically copy across schema (i.e. tables,functions,views) etc from one database over to another one. Assuming both are running same versions of DB2.

I found a few utilities like
  i)  db2move -- i could not find a way to access it via jdbc. So is ruled out.
  ii) admin_copy_schema -- copies across schemas in same database.
  iii)db2look -- again a utility. no way to call it from jdbc.

Even if we find a way to run those utilites db2move or db2look produces files which then have to be copied over to the target machine - making it highly complicated to automate the whole thing.

Is there a simple way to clone over a database through jdbn

JDBC is not really going to answer your problem directly. It is a generic programming API for working with any database. So it can't possibly know about utilities that are specific to an implementation, be it DB2, Oracle, MySQL, etc.

I would look at using one of the utilities you mentioned above. That is their purpose. They assist with moving databases and/or data across environments. (There is also the restore redirect option as well, which is good if you are doing a backup/restore option).

If you insist on doing this through application code, then you will have to pass DDL through the JDBC API's (which dependent on your driver and your permissions, may or may not work). Or you would have to try to execute the DB2 utilities from "command line" through you program (look at the Runtime class to do this).

Again, I would keep this out of the application (perhaps you can share more of why you want to do this? That would help with answering your question). If you insist on doing it inside your application, you may also want to check out Stack Overflow, as you may get answers from a "programmatic" perspective.


Can I tell when and who updated my database from v11 to v12 on Azure? Earlier today I noticed that all my Azure SQL database servers where updated from v11 to v12. I am the only one in my company that should be touching those dials, but not the only person in my company with the access. Since I did not initiate this upgrade I would like to find out who did, and when this unplanned update occurred.

I know I could write an email to everyone in my company with the access asking WTF for information politely, but I'd rather start the conversation with as many facts as possible. Also, I'm inclined to think this was a Microsoft/Azure SNAFU, because upgrading all servers feels too deliberate.

I believe this question was answered in the comments:

  It is indeed MS Azure (since you did not do it). They send you an email that on this day, the Azure SQL database servers will be upgraded. - Kin

  v12 has been GA since when? - Aaron Bertrand

  since July 2015 I think. You're probably right that we just ran out of time. I found this article, which seems to confirm your suspicion: – Erik

The article linked in Erik's contribution is Stopping or Postponing an upgrade to SQL Database V12.


Is MySQL Community Server 5.7.11 compatible with SLES 11 SP3? According to the MySQL download page for the MySQL Community Server there are only binaries available for SLES 12.

My question is if the MySQL Community Server 5.7.11 [and higher] is compatible with SLES 11 SP3 when I compile MySQL on my own?

Nobody guarantees their software. The GPL doesn't and neither do the commercial companies - the IT industry is notorious for not actually guaranteeing their work as being fit for purpose in any way and indemnifying themselves against any damages that could be caused.

This is even though they're actually paid for software - at least the GPL doesn't charge money. To clarify, they can, but generally don't. For example, MySQL is available under the GPL and a commercial licence.

If you compile it yourself, you're on your own.
If you download community binaries you're on your own.
If you pay, you're pretty much on your own. What you're really paying for is support and monitoring/backup tools which are not available under the GPL.

Finally, and perhaps to put you at ease (and even answer the question :-) ), if you can compile it, it works. I've compiled many versions of MySQL, MariaDB and Percona Server (it's my preferred way of installing - multiple servers on the same machine are a piece of cake) and I've never had a problem with buggy software (apart from the car-wreck that is MySQL that is! :-) ).

Compiling an old version on a new OS will work - the reverse may not be, but I've never done that.


Locate multiple duplicate columns in PostgreSQL table I am trying to report on duplicate records in a single table which has a unique key of app_cao_number.  The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is duplicated, or; 3. The Surname+FirstName are duplicated.

I can do this easily enough with three passes of the table using ORDER BY.  But I am hoping to use a single SELECT statement, with subqueries, to do the job.

Starting with just finding duplicate IDs I have the following statement:

SELECT app_cao_number, app_id,
    (SELECT app_id FROM people p2
        WHERE p2.app_id IS NOT null
        AND p2.app_id <> ''
        AND p1.app_cao_number <> p2.app_cao_number
        AND p1.app_id = p2.app_id
        GROUP BY p2.app_id) AS DupId
FROM people p1
WHERE app_id IS NOT null
AND app_id <> ''

This appears to get me the results that I want, but also include rows that have a null DupId - despite my attempts to ignore blank and null values in the SELECT statement.  Once this works I should be able to expand it to include the passport and name checks.

Please can someone explain why I have the following data output with nulls in the DupId column?  Thank you.

Further:
I thought it might be the GROUP BY clause, but I replaced it with a DISTINCT clause (below), but this gave the same result.

(SELECT DISTINCT p2.app_id FROM people p2
    WHERE p2.app_id IS NOT null
    AND p2.app_id <> ''
    AND p1.app_cao_number <> p2.app_cao_number
    AND p1.app_id = p2.app_id
    ) AS DupId

UPDATE

sample fiddle

Look for the model - does you need something like this?

fiddle

create table test (id int, value1 int, value2 int)

✓

insert into test values
(1,11,21),
(2,12,22),
(3,13,23),
(4,14,24),
(5,12,24),
(6,16,26),
(7,17,24),
(8,18,28)

8 rows affected

select t1.id id,
       t2.id dup_id,
       case when t1.value1 = t2.value1 then 'value 1'
            when t1.value2 = t2.value2 then 'value 2'
            else 'some error'
            end dup_field,
       case when t1.value1 = t2.value1 then t1.value1 :: text
            when t1.value2 = t2.value2 then t1.value2 :: text
            else 'some error'
            end dup_value
from test t1, test t2
where t1.id < t2.id
and ( t1.value1 = t2.value1
      or
      t1.value2 = t2.value2 )

id | dup_id | dup_field | dup_value
-: | -----: | :-------- | :--------
 2 |      5 | value 1   | 12
 4 |      5 | value 2   | 24
 4 |      7 | value 2   | 24
 5 |      7 | value 2   | 24


MAXDOP Setting on a SQL Server When upgrading and creating my new SQL server farm on 2016 I researched things to set on the server, one being MAXDOP setting.
From reading up about the setting I have set it at 8 on a server that has 8 logical CPUs.  The server is a VM and I believe it has a single NUMA node.  The worload is OTLP.
However I recently went on a training course where the suggestion was to set MAXDOP to 4 so I have started to second guess myself about this setting.
CTFP is set at 50.
I would appreciate help in determining the correct setting for this.

The actual value to use for your MAXDOP is going to vary by your workload.  If it's truly pure OLTP then you should probably get away with setting it to 1 and be done.
HOWEVER
We (the royal we) almost never get away with a pure OLTP workload, there are always going to be reports driven off of it, or screens in the app that look at lots of data at once, or you have to SHARE the server with BI.  So a more reasonable number is usually selected.
We have a 24 core server (for the memory, SQL Managed Instance) and have set our MAXDOP to 8 and our CTFP to 50.  I am currently toying with the idea of increasing CTFP as many of the times when we have problems it is because a largish query has gone parallel and has tied up resources doing so.  No benefit to speed, but large increase in resources required. In fact, many of our larger queries have MAXDOP hints limiting them to single thread.
For an 8 core server, I would set MAXDOP to 4 and leave CTFP where it is now (50).  For larger number of cores, I would set MAXDOP to 8 and start with CTFP at 50.  Then look at your workload, especially for queries with high CX waits.  If the majority of your workload is still going parallel and shouldn't, tweak the numbers.  Otherwise, tune the queries that are going parallel that shouldn't.


How to determine when to reduce physical memory for SQL Server? If I repeatedly notice SQL Server reporting a lot of free memory, can I likely remove about that amount of memory from the server without affecting performance? Or, can/should it use more for caching?

For this particular example, the server is running SQL Server 2012 Standard Edition on Windows Server 2012. It has 20 GB of physical memory and hosts over 100 GB of data.
SQL Server is the only application running on the server.

SQLServer;Buffer Manager
    Buffer cache hit ratio       99.737 %
    Page life expectancy            874 s
SQLServer:Memory Manager
    Database Cache Memory         6,744 MB
    Free Memory                   5,937 MB
    Optimizer Memory                  5 MB
    SQL Cache Memory                 29 MB
    Target Server Memory         19,015 MB
    Total Server Memory          18,407 MB

Your Page life expectancy counter is at 874 seconds.  That isn't a lot.  I'm guessing that the server isn't configured correctly, or something on the OS is taking the memory.  Odds are this server could use more RAM to up the PLE.


UPPERCASE Constraint in SQL What will be the query to add UPPERCASE Constraint to a column of pre-existing table? The question demands to ensure that the entries are in UPPERCASE.

You could use a CHECK constraint to enforce that the column always contains uppercase values.

-- as new table
CREATE TABLE t1(c1 VARCHAR(200) CONSTRAINT c1_upper CHECK (BINARY UPPER(c1) = BINARY c1));
INSERT INTO t1(c1) VALUES ('a');
INSERT INTO t1(c1) VALUES ('A');
SELECT * FROM t1;

-- altering existing table
CREATE TABLE t2 (c2 VARCHAR(200) NOT NULL);
ALTER TABLE t2 ADD CONSTRAINT c2_upper CHECK (BINARY c2 = BINARY UPPER(c2));
INSERT INTO t2(c2) VALUES ('a');
INSERT INTO t2(c2) VALUES ('A');
SELECT * FROM t2;

SQL Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a4d4e277ea6993d7b66a3b2f4871a320

Older versions of MySQL don't have check constraints. You can work around this by doing DML against a checked view. This works in version 5.5.

CREATE TABLE t1(c1 VARCHAR(200));
CREATE VIEW v1 AS SELECT * FROM t1 WHERE BINARY c1 = BINARY UPPER(c1) WITH CHECK OPTION;
INSERT INTO v1(c1) VALUES ('a');
-- fails
INSERT INTO v1(c1) VALUES ('A');
-- succeeds

SQL fiddle: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=7416cfbbbbbd1161d48785dd384d5d30


Is it necessary to drop foreign keys before adding a null column to a table and then re-add them? I have generated some SQL Server(2008) upgrade scripts using SqlCompare.  The primary objective is to add a null column.  However, the generated script wants to drop foreign keys in a couple of other tables and the primary key of the target table before the add column.  It then re-adds them.  Is this necessary?  What is the reasoning behind doing it?.

No it is not necessary.  See this working example below:

use TestDatabase;
go

-- create the parent table (houses the PK)
create table dbo.ParentTable
(
    ParentId int identity(1, 1) not null
        constraint PK_ParentTable_ParentId primary key clustered,
    some_int int not null
);
go

-- insert some dummy data
insert into dbo.ParentTable(some_int)
values (5), (4), (3);
go

-- create the child table (houses the FK)
create table dbo.ChildTable
(
    ChildId int identity(1, 1) not null,
    ParentId int not null
        constraint FK_ChildTable_ParentId foreign key references dbo.ParentTable(ParentId)
);
go

-- insert some dummy data
insert into dbo.ChildTable(ParentId)
values (1), (3);
go

-- view the contents of each table
select *
from dbo.ParentTable;
select *
from dbo.ChildTable;

-- add a nullable int column
alter table dbo.ParentTable
add another_col int null;
go

-- view the new layout
select *
from dbo.ParentTable;
select *
from dbo.ChildTable;

As you can see by the code, I modify dbo.ParentTable by adding a column.  That is a successful operation with the primary key constraint still in place.

As to why your third party software does this, we could guess all day.  But most likely they do it to handle some corner case without first testing whether or not the current operation falls into that.


Will composite indexes or unique indexes help with query performance? I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The following indexes are currently available:

t1.id = Primary key
t1.status
t2.owner
t2.user
t2.total

select t1.`id`
from (tbl1 t1 inner join tbl2 t2 on t1.`id` = t2.`id`)
where (t2.`owner` = `ABC123` or t2.`user` = `ABC123`)
  and t1.status = 1
order by t2.`total`
limit 0, 100;

(t2.`owner` = 'ABC123' or t2.`user` = 'ABC123')

This search term will force a table-scan. Composite indexes won't help.

Suppose you had a composite index on (owner, user). It could do a quick lookup for owner = 'ABC123' but then the desired values for user could be anywhere in the table, not just in the subset matching the desired owner value. So it has to scan the whole table anyway.

Likewise if you had a composite index on (user, owner). It could do a quick lookup for user = 'ABC123' but the same problem exists, finding the rows for owner will force a table-scan.

The one remaining possibility is to use index merge union optimization, but in my experience this doesn't work as well as you'd think.


Slow query in production, bad execution plan or bad index? I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
Arguments in favor of bad execution plan :

Before dropping the index, I looked up the last update date for the statistics on the given table and they were up to date.
My DBA has put in place Hallengren's index and statistic maintenance solution
The slow query was a select statement executed from sp_executesql with dates parameters. Executing the same select statement without sp_executesql was fast, but also didn't use the same execution plan.

Arguments against bad execution plan :

Before dropping the index, we went real wild and ran the forbidden dbcc freeproccache to clear any bad plan, but this didn't fix or change the performance issue.

Note:

The slow query happen to be  using a table indexed by date. However,
there is wide differences in the amount of records for each date. In
other word, any given date range from few records to more than 100k
and it is pretty random.

The database is running under compatibility level 140 (SQL Server
2017)

Was the source of the problem a bad plan or a stale index?
If it is a bad plan, then why dbcc freeproccache didn't work in getting rid of it?
Plan before issue was solved
Plan after issue was solved
Edit :
From what I know, this looked very much like a bad plan, but somehow dbcc freeproccache didn't work. So, I'm left in the dark, confused about the whole situation.

Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan.

You're alerted of a slow query that was previously fast

Bad times, I empathize!

You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help

Usually, clearing the cache fixes problems like this if the problem is parameter sniffing.

You drop and recreate a nonclustered index that's being used by the bad plan.  Now your problem is solved

Another reason for bad plans is bad stats.  If the problem is bad stats, then clearing the procedure cache will just result in the same bad execution plan being produced again (based on the bad stats).

Creating an index on a column creates new statistics on that column WITH FULLSCAN:

  Compute statistics by scanning all rows in the table or indexed view.

These are usually the best stats, because they include the most comprehensive view of the data.

Statistics can be automatically updated over time, as rows in the table are changed / added / deleted.  SQL Server will use different "sampling percents" based on the size of the table.  From that same docs page:

  For most workloads, a full scan is not required, and default sampling is adequate.
  However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan.

It sounds like what happened was:

an automatic stats update ran for this index, with a low or otherwise problematic sample rate
this triggered the creation of a new execution plan, and that plan was bad
you re-created the index, which produces good stats again

You mentioned you're using Ola's index and stats maintenance solution.  You may want to add a special case for this table / index to update stats with FULLSCAN, as it sounds like the table's data is skewed enough to warrant that.

If you want to avoid scanning the whole table (I don't know how big it is), you can experiment with different sample percentages (starting at 100, and dropping by 5 or 10 percent until you get the bad plan).

Looking the execution plans you provided (thanks!), you can see evidence for my theory.  Here's the index for the bad plan:

<StatisticsInfo Database="[BDM]" Schema="[dbo]" Table="
                [SubTypeInstanceProcessAdvancements]" Statistics="[AsOfDate]"
                ModificationCount="288" SamplingPercent="23.4265"
                LastUpdate="2019-08-15T22:00:09.91" />

And here's the good plan:

<StatisticsInfo Database="[BDM]" Schema="[dbo]" Table="
                [SubTypeInstanceProcessAdvancements]" Statistics="[AsOfDate]"
                ModificationCount="0" SamplingPercent="100"
                LastUpdate="2019-08-16T13:50:10.43" />

The bad plan has a sample percent of 23.4265, while the good plan had stats sampled at 100% (because of the index recreation).


Query for frequency of e-mail addresses in a field Newbie here. I know the answer is out there, but lack the vocabulary to ask the right question...

I am looking for a MYSQL query which gives me the frequency of addresses in the field emailaddress. I would like to know how many are unique, how many occur twice, three times etc.

I think you want a "count of counts", which can be done with 2 GROUP BY, once to find how many times an emailaddress has been used:

    SELECT emailaddress, COUNT(*) AS frequency
    FROM tablename
    GROUP BY emailaddress ;

and then enclosing the above in a derived table and doing another GROUP BY to find how many emailaddresses were used once, twice, thrice, etc...:

SELECT frequency, COUNT(*) AS emailadresses
FROM
  ( SELECT COUNT(*) AS frequency           -- we don't need email any more
    FROM tablename
    GROUP BY emailaddress
  ) AS x
GROUP BY frequency ;

Minor mysql quirk. If you want:

GROUP BY frequency
ORDER BY frequency DESC ;

it can be abbreviated (and it will be a bit more efficient) in MySQL as:

GROUP BY frequency DESC ;


How to create a train schedule in SQLite3? I am interested in what a SQLite3 database schema would look like for a train timetable.

Generally this is presented in table format as the final result.

Sample Schedule for M to F

STATION A   STATION B    STATION C ... STATION K
6:00        6:15         7:30          9:00
6:30        6:45         7:45          9:30
7:00        7:15         ----          10:00
8:00        8:15         9:30          11:00

Sample Schedule for Weekends

STATION A   STATION B    STATION C ... STATION K
7:00        7:15         8:30          10:00
8:30        8:45         8:45          10:30
9:00        ----         9:30          11:00
10:00       10:15        10:30         12:00

The trains don't have any number (like the A-Train, 12 Train, etc). So we can make an internal number. Should the internal number be unique or duplicated for a Monday-Friday to a Saturday and Sunday.  Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.

Stations can be ordered by their distance.

So far I am thinking I need tables for:

Stations (ID, name, distance, line)
Train (ID, train no, Day Operation, details)
Train run (Train.ID, Station.ID, arr/dep, time, notes)

Is this normalised enough?

Is my Schema correct or so I need to make any changes?

I think you need a track_segment table to track the segments between stations. The problem with your Station table is that you include distance there, but distance to/from what?

So let's try this:

Train
-----
  ID
  Train_number
  Days_of_operation
  other_details

Station
-------
  ID
  Name
  other_data

Track_Segment
-------------
  From_Station_ID
  To_Station_ID
  Length
  Line_ID

Line
----
  ID
  Name
  other_data

Train_Run
---------
  ID
  Train_ID
  from_Station_ID
  to_station_ID
  Depart_datetime
  Arrive_datetime
  other_details

This allows a Train_run to reference a specific train, as well as the two endpoints of the journey. Of course, there might be multiple segments of track between these two stations, so the total length of the journey could be had by looking at all the segments between from_station_id and to_station_id in track_segment. I also included the line table, since you had a refernce to it in your tables. I assume that a "Line" is just a descriptive term for a specific train journey that might have multiple stops along the way, like "Orient Express" or "Rocky Mountain Route".


Mysql settings for query_cache_min_res_unit What is the best setting for query_cache_min_res_unit for these results:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 35327     |
| Qcache_free_memory      | 295242976 |
| Qcache_hits             | 236913188 |
| Qcache_inserts          | 49557287  |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 7128902   |
| Qcache_queries_in_cache | 195659    |
| Qcache_total_blocks     | 426870    |
+-------------------------+-----------+

Do I need to change any other settings?

My website creates very large results. This is the current setting:

query_cache_min_res_unit = 4096

Info on the mysql dev website

If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

Observations

Your question says you have 426870 for Qcache_total_blocks. Since each block is 1K, blocks are aligned to the nearest multiple of 1024. Thus, your query cache has about 417M.
If you divide Qcache_not_cached (7128902) by Qcache_inserts (49557287), this shows that about 14.4% (1 out of 7) of your queries could not be cached.
(Qcache_total_blocks (426870) - Qcache_free_blocks (35327)) / Qcache_queries_in_cache (195659) = 2K
On average, each query cache result hold about 2K, which is theoretically not possible. This could indicate some fragmentation in the query cache.

Recommendations

You need a bigger value for query_cache_size (perhaps 2G). If you are using InnoDB, you may get some performance increase if you disable the query cache altogether by setting query_cache_size to 0.
You need a smaller value for query_cache_min_res_unit (minimum value is 512 (0.5K)) if you want to hold small result sets.
You need a bigger value for query_cache_limit if you want to cap how big a result is too big.
See my earlier post : Is the overhead of frequent query cache invalidation ever worth it?


Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are string data type and I need to convert this row data to column data. This is why I wrote this question. I just did it with case. Can anyone help me......Thanks in advance.

Sample format

empid    wagecode    amount
  1      basic       1000
  1      TA           500
  1      DA           500
  2      Basic       1500
  2      TA           750
  2      DA           750

Desired output:

empid   basic    TA    DA
  1     1000     500   500
  2     1500     750   750

Don't pivot if you always have 3 rows per empid. Just self join

SELECT
   t1.empid,
   t1.amount AS basic,
   t2.amount AS TA,
   t3.amount AS DA
FROM
   MyTable t1
   JOIN
   MyTable t2 ON t1.empid = t2.empid
   JOIN
   MyTable t3 ON t1.empid = t3.empid
WHERE
   t1.wagecode = 'basic'
   AND
   t2.wagecode = 'TA'
   AND
   t3.wagecode = 'DA'

Also, welcome to the pitfalls of EAV designs


How to prevent/detect fetching data from another database on same server I have discovered a problem in our development where I need some help.
We had some reports developed by an outsourced person, and now I am cleaning up his mess.
The biggest problem is that he has often written select statements using 3 parts, for example
[DB_T].[dbo].[SomeTable]

Whether this is good practice or not, I don't care right now.
My problem is he sometimes fetches data from our TEST database in Production queries.
So I was hoping for some way to detect this.
Let's say my test database is called DB_T and my production database is called DB_P and that the application has a connection string to DB_P
Now I discovered query like this
select *
from   table1 t1
  join [DB_T].[dbo].[Table2] t2 on t1.id = t2.id

When this query is executed, the resultset will unfortunate contain data mixed from both databases.
My question is, is there some way I can detect this ?
Some of these queries are written in a C# Winforms application, some are in a view.
But all will be executed from a c# WinForms application with a connection string pointing to DB_P
It will take me weeks to dig thru all his queries and look for this (some are build at runtime in string variables)
So it would help if I could detect this somehow, or maybe there is a way to disallow reading from another database. In that case the application would throw an error, which is fine by me, better an error then working with wrong data.

To find objects in the database (views, procedures etc) that use 3 (or 4) part naming, you can use the scripts in this article to search the SQL Dependencies in the database.
SELECT OBJECT_SCHEMA_NAME(objects.object_id)
                            AS referencing_schema_name,
       objects.name AS referencing_object_name,
      objects.type_desc referencing_object_type,
      CASE WHEN referenced_database_name = DB_NAME()
                  AND referenced_server_name IS NULL
            THEN 'Internal' ELSE 'External' END
                        AS referenced_database_location,
COALESCE(sql_expression_dependencies.referenced_server_name,
         '<localserver>') AS referenced_server_name,
       sql_expression_dependencies.referenced_database_name,
       sql_expression_dependencies.referenced_schema_name,
       sql_expression_dependencies.referenced_entity_name
                                    AS referenced_object_name
FROM sys.sql_expression_dependencies
        JOIN sys.objects
            ON objects.object_id =
                   sql_expression_dependencies.referencing_id
WHERE referenced_database_name IS NOT NULL
ORDER BY referencing_schema_name,referencing_object_name

There is also a script on that article for finding dynamic SQL in objects in the database. This doesn't mean they're using 3-part naming, but at least gives you a starting point for your searching.
As for the code in C#, that is going to be more difficult to find. You might be better placed to take a copy of your production DB and restore it to a non-production server that doesn't have the Test DB on it, then run your application against that database (which is now technically non-production). With the Test DB missing, you should get errors wherever you have references to the Test DB in application code.
You can then iteratively resolve each error and progress further with the application until you've got it working without errors. You could also setup up an Extended Events session filtered by user and database (Test DB) to record sqlserver.sql_statement_completed events. Periodically check this events session to see any queries executing against the Test DB by your production user to further identify problem queries and resolve.
Lastly, as a general best practice - take Test DB off your production server and put it somewhere else. This will guarantee that cross database queries to Test DB will fail (because it won't exist on production) and help prevent these kind of cross-contamination issues between environments. It also means you can treat your production environment as a true production, so the next time you have an external contractor come in to write reports/applications, they can develop in the test environment, and any issues with hard-coded DB or server names will become immediately apparent during deployment.


Temporal Tables Granularity I've start using temporal tables on SQL Server 2017 and I've noticed that rapid updates aren't recorded in the history table. Does anyone know if this is configurable or do I just have to live with this limitation.

More info...

For testing I have a Table setup with a single row

In SSMS If I do something like this:

UPDATE MyTable SET MyCol = 'a'
UPDATE MyTable SET MyCol = 'b'
UPDATE MyTable SET MyCol = 'c'

The History Table doesn't have any records for 'a' and 'b' changes.

I repeated this with a short delay between the updates like this:

UPDATE MyTable SET MyCol = 'a'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'b'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'c'

And this results in the 'a' and 'b' being recorded in the history. However the lower the delay the less likely the records are to appear.

UPDATED:

Here a full script to show the issue. With the delays in place the select return 4 rows, with the delays commented out only one row is returned

-- Create Table
CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] NOT NULL,
    [MyCol] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- Add History
ALTER TABLE  [dbo].[MyTable] ADD
    SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE  [dbo].[MyTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));

-- Add a Single Row
INSERT INTO [dbo].[MyTable] (Id, MyCol)
VALUES ('2a2979d2-529a-442b-b272-3662feda731b', 'init');

-- Update the Row Multiple times,  Comment out the Delays or lower them to under around 400ms to see inconsistent results
UPDATE MyTable SET MyCol = 'a';
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'b';
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'c';

-- Query the history
SELECT * From [dbo].[MyTable] FOR SYSTEM_TIME ALL;

-- Remove the test tables
ALTER TABLE [dbo].[MyTable] SET(SYSTEM_VERSIONING = OFF);
DROP TABLE [dbo].[MyTable_History];
DROP TABLE [dbo].[MyTable];

The documentation says:

  ⓘ Note

  FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Those rows will be
  generated if you perform multiple updates on the same primary key
  within the same transaction. In that case, temporal querying surfaces
  only row versions before the transactions and ones that became actual
  after the transactions. If you need to include those rows in the
  analysis, query the history table directly.

You have typed SysStartTime and SysEndTime as datetime2 with no fractional seconds. This increases the chance they will compare equal, giving a "period of validity with zero duration".

You can see the history rows exist by looking in the history table directly:

SELECT * FROM [dbo].[MyTable_History];

The only issue is that the FOR SYSTEM_TIME query does not show rows where SysStartTime and SysEndTime are equal.

db<>fiddle demo


Postgresql group by for many columns I have such query:

SELECT "Movie"."title",
       "Movie"."id",
       "Movie"."publicationDate",
       "Movie"."authorID",
       "Author"."photoURL" as "authorPhoto",
       concat("Author"."name", ' ', "Author"."surname") as "authorName",
       "Movie"."seriesID",
       "Series"."name" as "seriesName",
       "Series"."logoURL" as "seriesLogo",
       "Movie"."languageID" as "language",
       "Movie"."ratingUp",
       "Movie"."ratingDown",
       "userWatched"."watchedDate",
       CASE WHEN "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
            THEN TRUE
            ELSE FALSE
            END as "favourited",
       CASE WHEN "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
            THEN TRUE
            ELSE FALSE
            END as "onWatchlist",
       CASE WHEN "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' AND "userWatched"."watchedDate" IS NOT NULL
            THEN TRUE
            ELSE FALSE
            END as "watched",
            count(DISTINCT "overallWatched"."watchedDate") as "viewCount"
FROM "Movie"
LEFT JOIN "Series" ON "Series"."id" = "Movie"."seriesID"
LEFT JOIN "Author" ON "Author"."id" = "Movie"."authorID"
LEFT JOIN "UserMovieFavourited" ON "UserMovieFavourited"."movieID" = "Movie"."id"
                                AND "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchlist" ON "UserMovieWatchlist"."movieID" = "Movie"."id"
                               AND "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "userWatched" ON "userWatched"."movieID" = "Movie"."id"
                                                    AND "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN "UserMovieWatchProgress" as "overallWatched" ON "overallWatched"."movieID" = "Movie"."id"
GROUP BY "Movie"."id",
         "Author"."photoURL",
         "Author"."name",
         "Author"."surname",
         "Series"."name",
         "Series"."logoURL",
         "userWatched"."watchedDate",
         "UserMovieFavourited"."userID",
         "UserMovieWatchlist"."userID",
         "userWatched"."userID"

In MySQL i needed only one column for grouping that's Movie.id. For postgresql I needed to add 10 columns to make this query work as shown above.

Is this natural for postgresql or this query should be refactored to remove need of so many grouping columns?

Imagine there is a row in "Movie" where id is 42.

Then imagine that there are two rows in "UserMovieWatchProgress" where "movieID" is 42.

Then the result of

"Movie" LEFT JOIN "UserMovieWatchProgress"
   ON "UserMovieWatchProgress"."movieID" = "Movie"."id"

would be something like

 "Movie".id | "UserMovieWatchProgress"."movieID"
------------+------------------------------------
         42 |                                143
         42 |                                 93

Now consider the following query:

SELECT "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id"
GROUP BY "Movie".id;

By definition of GROUP BY, there can be only one result row per "Movie".id.
Which number should we take for "UserMovieWatchProgress"."movieID"? That is unclear and not well defined, so this is forbidden by the SQL standard.

Now if PostgreSQL can deduce that there can be only one possible "UserMovieWatchProgress"."movieID", it will allow you to omit the column from the GROUP BY clause.

If you just want any of the "UserMovieWatchProgress"."movieID" in the query result, and you don't care which one, you can do the following in PostgreSQL:

SELECT DISTINCT ON ("Movie".id)
       "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id";

Perhaps that is what you are looking for.


Count where two or more columns in a row are over a certain value [basketball, double double, triple double] I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a problem: counting the number of double doubles and/or triple doubles a player made over the season from his game statistics.

The definition of a double double and a triple double is as follows:

Double-double:

A double-double is defined as a performance in which a player accumulates a double-digit number total in two of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Triple-double:

A triple-double is defined as a performance in which a player accumulates a double digit number total in three of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Quadruple-double (added for clarification)

A quadruple-double is defined as a performance in which a player accumulates a double digit number total in four of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

The "PlayerGameStats" table stores statistics for each game a player plays and looks as follows:

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 1, 1,  1, 'Nuggets',    'Cavaliers',  6,  8,  2, 2,  0 ),
  ( 2, 1,  2, 'Nuggets',     'Clippers', 15,  7,  0, 1,  3 ),
  ( 3, 1,  6, 'Nuggets', 'Trailblazers', 11, 11,  1, 2,  1 ),
  ( 4, 1, 10, 'Nuggets',    'Mavericks',  8, 10,  2, 2, 12 ),
  ( 5, 1, 11, 'Nuggets',       'Knicks', 23, 12,  1, 0,  0 ),
  ( 6, 1, 12, 'Nuggets',         'Jazz',  8,  8, 11, 1,  0 ),
  ( 7, 1, 13, 'Nuggets',         'Suns',  7, 11,  2, 2,  1 ),
  ( 8, 1, 14, 'Nuggets',        'Kings', 10, 15,  0, 3,  1 ),
  ( 9, 1, 15, 'Nuggets',        'Kings',  9,  7,  5, 0,  4 ),
  (10, 1, 17, 'Nuggets',      'Thunder', 13, 10, 10, 1,  0 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

The output I want to achieve looks like this:

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

The only solution I found so far is so awful it makes me puke ... ;o) ... It looks like this:

SELECT
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10)
                THEN 1
                ELSE 0
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

... and now you're probably also puking (or laughing hard) after reading this. I didn't even write out everything that would be needed to get all double double combinations, and omitted the case statement for the triple doubles because it's even more ridiculous.

Is there a better way to do this? Either with the table structure I have or with a new table structure (I could write a script to convert the table).

I can use MySQL 5.5 or PostgreSQL 9.2.

Here is a link to SqlFiddle with example data and my awful solution I posted above: http://sqlfiddle.com/#!2/af6101/3

Note that I'm not really interested in quadruple-doubles (see above) since they don't occur in the game I play as far as I know, but it would be a plus if the query is easily expandable without much rewrite to account for quadruple-doubles.

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work

select a.player_id,
a.team,
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles,
sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles
from
(select *,
(case when points > 9 then 1 else 0 end) +
(case when rebounds > 9 then 1 else 0 end) +
(case when assists > 9 then 1 else 0 end) +
(case when steals > 9 then 1 else 0 end) +
(case when blocks > 9 then 1 else 0  end) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team


Which accented characters are considered the same? In MySQL, certain characters are considered the same when performing searches, e.g. 'n' and 'ñ'; unless you specify BINARY in the condition.

Is there a list somewhere of the characters that are considered the same?

Case or accent sensitivity behavior is defined by the collation you are using.

The full list of comparable characters in a given collation can be found in collationcharts where you find the MySQL list of collations

If you find your collation in that list you will see a chart of comparable characters.


Using TDE and SQL Server 2016 AlwaysOn Availability Groups We currently use SQL Server 2012 AG are in process of migrating to SQL 2016. We use EMC SAN for storage and it provides the data encryption at rest on hardware level. Some folks are thinking about start using TDE that also provides encryption at rest only that we already have.

While it's not so difficult to implement TDE, it, in my opinion, creates a lot headache in terms of managing and administrating. From your experience, is it good idea to use TDE in our situation? Is it worth? How well TDE compatible with AlwaysOn AGs? I read, for example, you can't use AG GUI to do anything. Everything has to be done through scripts.

From your experience, is it good idea to use TDE in our situation?

No idea, you didn't tell us the attack vectors you are looking to secure. In the rest of my answers, I'll assume the only attack vector is on disk at rest.

  Is it worth?

If you're already running Enterprise Edition (which is required) then there is no direct extra cost. There will obviously admin cost for it in terms of your working hours.

  How well TDE compatible with AlwaysOn AGs?

It's 100% compatible.

  We use EMC SAN for storage and it provides the data encryption at rest on hardware level.

What happens when the backups, data files, etc., are moved off the SAN? What about backups in transit over network?

  I read, for example, you can't use AG GUI to do anything.

In older versions of SSMS, that's correct. This has been fixed for a while.

  Everything has to be done through scripts.

I don't see why that's a bad thing, though.


SQL Server Agent Invokes Failed 'sa' Login I recently upgraded to SQL Server 2016. I was looking through the SQL Server logs and noticed the following error.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]

I have figured out that the running a transaction log backup triggers the error. The weird thing is that the maintenance job is successful. I can't figure out how the job can be successful with the failed login attempts. Another thing is that every time the job is ran, it logs exactly 30 failed attempts. What is failing to log in during the execution of the transaction log backup and how do you fix it?

Any ideas or solutions would be appreciated. Thanks

After a few days of research, it looks like this issue is caused by a bug in SQL Server 2016. In the following thread that describes this issue, other DBAs are experiencing the same thing. One person troubleshooted the issue with a Microsoft support engineer who was confident it was a bug.  Maintenance plan - Login failed for user 'sa' on master database

BUG: Use SQL authentication when creating a maintenance plan through the maintenance plan wizard. Run the maintenance plan. It will succeed, but after it succeeds, it will log a number of failed logins.

WORKAROUND: Use Windows authentication instead of SQL authentication. If you have already created the maintenance plan, you will need to right click on it and select modify. Look for "manage connections" at the top window. You will be able to modify the local connection here. You will need to be logged on your machine with that domain account and that domain account will need the proper SQL privileges.


How to not "duplicate" a table if it has a unique constraint over many columns? I have a very large table (35GB) that is unique over a combination of four of its columns.

The table isn't very wide and the four columns it is unique over are the columns that are larger (in bytes).  The end result is that the index to keep the table unique is 21GB.  This isn't a result of the index bloating in size over time but is the size of the the index immediately after it is created.

I don't need to optimize for insert speed at all, as inserts will only happen in batches once per month.  There won't be any updates to any rows once they are inserted.

I'm running PostgreSQL 9.5.0.

Is there a way to not duplicate such a large portion of the database just to enforce a unique constraint?  Possibly using something like a clustered index?

Full table description:

CREATE TABLE medi_cal_base_eligibility (
    client_index_number text NOT NULL,
    medi_cal_date date NOT NULL,
    eligibility_date date NOT NULL,
    aidcode text,
    responsible_county text,
    status text,
    cardinal smallint NOT NULL,
    id SERIAL PRIMARY KEY
);

Indexes:

"medi_cal_base_eligibility_pkey" PRIMARY KEY, btree
    (id)
"medi_cal_base_eligibility_uq_dates_cin_cardinal" UNIQUE CONSTRAINT, btree
    (eligibility_date, client_index_number, medi_cal_date, cardinal)

With PostgreSQL 9.5 you can enjoy a BRIN index (which will make the index very small, yet functional), and handle the exclusions via a trigger, like this:

CREATE INDEX ON medi_cal_base_eligibility USING BRIN (client_index_number);

CREATE OR REPLACE FUNCTION tf_medi_cal_base_eligibility_insert() RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_OP = 'INSERT' OR (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal) IS DISTINCT FROM (OLD.client_index_number, OLD.eligibility_date, OLD.medi_cal_date, OLD.cardinal)) AND
     EXISTS (SELECT 1 FROM medi_cal_base_eligibility
             WHERE (client_index_number, eligibility_date, medi_cal_date, cardinal) = (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal)) THEN
    RAISE 'Duplicate key: %, %, %, %', NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal;
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER t_medi_cal_base_eligibility_insert BEFORE INSERT OR UPDATE ON medi_cal_base_eligibility
    FOR EACH ROW EXECUTE PROCEDURE tf_medi_cal_base_eligibility_insert();

As stated by dezso, a BRIN index would only be useful in a situation in which client_index_number has a correlation to the position of the record in the file.

If the solution above using BRIN won't cut the mustard, a good choice is using a hash of the data to search for. The size of the hash will determine how many records it will have to scan to look for uniqueness; also, the bigger the hash, the bigger the index. A hash of 32 bits would most likely render unique results (or at most a handful) and would be just as large as your primary key. In the following example I'm getting the 32 bits hash by using the last 8 hexadecimal digits of the md5 function using your four unique fields concatenated together.

CREATE OR REPLACE FUNCTION f_medi_cal_base_eligibility_to_int (p_client_index_number text, p_medi_cal_date date, p_eligibility_date date, p_cardinal smallint) RETURNS int AS $BODY$
  SELECT ('x'||right(md5($1 || to_char($2, 'YYYYMMDD') || to_char($3, 'YYYYMMDD') || $4::text), 8))::bit(32)::int
$BODY$ LANGUAGE SQL IMMUTABLE SECURITY DEFINER;

CREATE INDEX ON medi_cal_base_eligibility (f_medi_cal_base_eligibility_to_int(client_index_number, medi_cal_date, eligibility_date, cardinal));

CREATE OR REPLACE FUNCTION tf_medi_cal_base_eligibility_insert() RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_OP = 'INSERT' OR (NEW.client_index_number, NEW.eligibility_date, NEW.medi_cal_date, NEW.cardinal) IS DISTINCT FROM (OLD.client_index_number, OLD.eligibility_date, OLD.medi_cal_date, OLD.cardinal)) AND
     EXISTS (SELECT 1 FROM medi_cal_base_eligibility
             WHERE (f_medi_cal_base_eligibility_to_int(client_index_number, medi_cal_date, eligibility_date, cardinal), client_index_number, medi_cal_date, eligibility_date, cardinal) =
                   (f_medi_cal_base_eligibility_to_int(NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal), NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal)) THEN
    RAISE 'Duplicate key: %, %, %, %', NEW.client_index_number, NEW.medi_cal_date, NEW.eligibility_date, NEW.cardinal;
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER t_medi_cal_base_eligibility_insert BEFORE INSERT OR UPDATE ON medi_cal_base_eligibility
    FOR EACH ROW EXECUTE PROCEDURE tf_medi_cal_base_eligibility_insert();


What happens at low level when I add a column to a table in PostgreSQL? I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this query.

Doesn't relational databases store row data pointers together so it will have to lookup each row to add a pointer of the added column to the end of that row block on disk? This is my current understanding.

In Postgres nothing really happens.

It just adds a row to the system catalogs so that it knows that you added a column. It doesn't even touch the table data. So even on a table with a billion rows this would only take a few milliseconds once it could obtain the exclusive lock on the table.

Starting with Postgres 11, this even is the case when adding a NOT NULL column with a constant as the default value, e.g. add some_flag integer not null default 42

If the default value is a volatile function (e.g. current_timestamp), Postgres is forced to call this once for each row and put in the result of that. Then this would take a substantial amount of time to add the column with billions of rows.


How to make pg_dump skip extension? This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I make sure it also has plpgsql extension. Then when restoring this happens:

pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2053; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

I'm creating a bunch of scripts and it's pretty important for me that pg_restore returns 0, so the fact that I can just ignore this isn't doing any good. What puzzles me is that IIRC I need to create extension as postgres master user, so I have no idea why all this EXTENSION stuff ends up in my dump. After all, I'm not owner of the language/extension?

Anyway, I'd be gratefull for any suggestion how to get rid of this. Please note, that I'm aware how the -l/-L switches work. This however seems to be way too much effort to correct just one simple extension comment.

For anyone looking for a workaround, limiting pg_restore to a specific schema has helped me get around this bug. See https://stackoverflow.com/a/11776053/11819