How to hot-backup mysql slave database with read operations I have a slave database of about 120GB in size on which application is performing select statements.

I also have a backup system (Acronis backup) available to me from a hosting company. Unfortunately backup agent is not mysql-aware. It can perform file backups and whole machine backup - it makes a snapshot of machine. It also enables to run a script before/after a backup and before/after snapshot creation.

I went with simple script making flush tables with read lock before making a snapshot and removing a lock after. Unfortunately, because of long-running select statements, it happens that acquiring a lock takes a lot of time.

Script was waiting for lock for some time (300 secs) and then killing the session. I thought that I will be able to cancel acquiring a lock and allow application to run. Unfortunately it appears it is not possible (or I am unable to do it correctly).

Is there any command sequence that would allow me to create a valid snapshot and not interrupt database functions for too long? For example, would:

stop slave
flush tables
make snapshot
start slave

or similar do the trick (some data de-synchronization between master ans slave is not a big issue for my app)?

I was looking into some alternatives like xtrabackup or mysqldump to make a backup and simply copy files later but:

Xtrabackup needs at least twice as much space as database and, because of that, I cannot use it.

Mysqldump restore time is huge comparing to copying files.

Taking above under consideration I would love to stick to 'cold' backup with snapshot or a solution like xtrabackup that could provide compressed data without intermediate steps.

Here is how I would do it (Similar to your suggestion)

Terminal 1: FLUSH TABLES WITH READ LOCK It is important to keep this session/terminal open till the end of the process. Wait till you get the message the tables are now locked.
Terminal 2: Create the snapshot. Depending on what technology you use, this may vary. LVM snapshot is a common one.
Terminal 1: UNLOCK TABLES
Copy the files from the snapshot location

It is essential not to close terminal 1 before you are done with step #2.

Notice please that there is no need to stop the slave.

This link would be very helpful. It explains with more details, using LVM.

HTH


Help wrapping my mind around many-to-many relationships I'm making a database that needs to have the following tables: Article, Video, Image, and Keyword. Keyword contains a fixed list of keywords. (For example when creating an article, you'd have to select from a list of keywords, not create a new one) Each Article, Image, and Video can have n number of keywords. I'm having a hard time wrapping my mind around how I'd define these relationships in my database, as I've really only dealt with one-to-one and one-to-many in the applications I've had to develop in the past. Would I do a many-to-many between Keyword and the other tables? Or would I need to make a join table between each table? KeywordImage (keyword_id, image_id)? Or are neither of those options the right way to think about this?

If a join table is the right approach, would it be one-to-many between Article and ArticleKeyword?

edit
Keyword isn't a required field in any of the tables

Something like:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
article_id (PK)  <---  article_id (FK)
                       keyword_id (FK)  --->  keyword_id (PK)

There could be zero-to-many ArticleKeyword rows for each row in Article.

There could be zero-to-many ArticleKeyword rows for each row in Keyword.

This equates to a many-to-many relationship between that Article and Keyword tables.

The primary key for ArticleKeyword would be the composite key (article_id, keyword_id) unless you wanted to count multiple occurrences of the same keyword in the same article separately (perhaps noting the position in the article) in which case you may instead want a surrogate key like so:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
                       ak_id      (PK)
article_id (PK)  <---  article_id (FK)
                       keyword_id (FK)  --->  keyword_id (PK)
                       kw_location

(or you could add the location properties to the combined key, of course, if those extra columns would never be NULL)


How do I configure my SQL Server instance to use a different Edition with minimal side effects? I'm in the process of migrating an old server running Windows 2008 R2 with SQL 2012 Express to a new server running SQL Server 2017 Standard and Windows 2016. I would like to mirror databases between them, but the Express Edition doesn't support it. I tried to upgrade it to SQL 2012 Standard Evaluation, but I received the following error:

  Failed The selected SQL Server instance does not
  meet upgrade matrix requirements.

I discovered this isn't possible:
Upgrade SQL Server 2012 Express to Evaluation Edition

I was forced to install SQL Server 2012 Evaluation as a new side-by-side instance and named it ./SQLSERVER2. In order to use this instance, however, it would require moving a ton of data files and changing the instance names in all my configuration files. This will also require server downtime as well. This will be a major PITA without the humous and I want to avoid it if at all possible.

Is there a way of reconfiguring ./SQLSERVER to use the Standard Evaluation Edition with minimal downtime and side-effects?

You could possibly follow these steps to "migrate" the databases to a "new" SQL Server 20xx instance.

detach the databases in the running SQL Server Express instance and leave them where they are.
Script out any logins that you require.
Don't delete/drop any databases.
De-install the SQL Server 2008 R2 Express Edition.
Install the SQL Server 20xx Standard Edition. (you can even use the instance name SQLEXPRESS; how clever is that?)
Add the SQL Server Logins to your "new" SQL Server 20xx Standard Edition instance.
Once the "new" SQL Server 20xx Standard Edition is running attach the "old" databases to your new SQL Server 20xx instance.
Change the database compatibility level to 110 (if required).

Enjoy a "new" SQL Server 20xx Standard Edition instance hosting your "old" databases.


MySQL Data Synchronisation from different Databases in C# I am supposed to create a C# solution to compare the data (table, column and row) from two different databases and update the 2nd one as follows:
1. with just the rows that are updated recently (incase of duplicates)
2. check for new/updated column in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one.
3. check for new/updated table in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one.
So basically I have to synchronize the data between the two databases.

As of now, I have just got to connecting the database part. I am not sure how to proceed (should I be dumping the data from the database and checking or is there a simpler way?).!!!

Any help would be appreciated.

Thank you!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace DBsync2
{
      //table2 is the Master(with duplicates) Table and table1 is the one without duplicates

<pre>
public partial class Form1 : Form
{
    private MySqlConnection conn1 = null;
    private MySqlConnection conn2 = null;
    string cs1,cs2;

    class IdTsEntry
    {
        public Int64 fid { get; set; }
        public String fdate { get; set; }
    }

    void Init()
    {
        //initialize cs1, cs2 to suit the database connection details.

        try
        {
            string servername1 = server1.ToString();
            string uname1 = uid1.ToString();
            string pass1 = pwd1.ToString();
            string dbName1 = dbname1.ToString();

            string tbl1 = table1.ToString();
            string tbl2 = table2.ToString();

            conn1 = new MySqlConnection(cs1);
            conn1.Open();
            conn2 = new MySqlConnection(cs2);
            conn2.Open();

            tbl1 = "abc";
            tbl2 = "def";

            // Load target list's ids and timestamps
            string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
            MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
            List&lt;IdTsEntry&gt; A = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd1.ExecuteReader())
            {
                //while (reader.Read())
                //{ // I assume the fields are set to NOT NULL
                //    A.Add(new IdTsEntry()
                //    {
                //        fid = reader.GetInt64(0),
                //        fdate = reader.GetString(1)
                //    });
                //}

                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    A.Add(new IdTsEntry()
                    {
                        fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
                        fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
                    });
                }
            }
            // Load source list's ids and timestamps
            string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2;
            MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

            List&lt;IdTsEntry&gt; B = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd2.ExecuteReader())
            {
                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    B.Add(new IdTsEntry()
                    {
                        fid = reader.GetInt64(0),
                        fdate = reader.GetString(1)
                    });
                }

            }

            // Filter lists
            List&lt;Int64&gt; List1 = new List&lt;Int64&gt;();
            List&lt;Int64&gt; List2 = new List&lt;Int64&gt;();
            foreach (IdTsEntry b in B)
            {
                var a = A.FirstOrDefault(e =&gt; e.fid.Equals(b.fid));
                if (a == null)
                    List2.Add(b.fid); // b.id not in A -&amp;gt; new row
                else if (!a.fdate.Equals(b.fdate))
                    List1.Add(b.fid); // b.id in A but other timestamp -&amp;gt; altered row
            }

                        // Update altered rows
            //string ct1 = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //string ct2 = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = {0}";
            foreach (Int64 id in List1)
            {
                // Read all entry values into parameters
               // cmd1.CommandText = String.Format(ct1, id);
                cmd1.CommandText = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();

               // MessageBox.Show(cmd1.CommandText);
                cmd2.Parameters.Clear();
                int i = 0;
                using (MySqlDataReader reader = cmd1.ExecuteReader())
                {
                    if (!reader.Read()) continue;
                    for (int n = 0; n &lt; reader.FieldCount; n++)
                    {
                        cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                            reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                    }

                }
                // Update row
                //cmd2.CommandText = String.Format(ct2, id);
                cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = " + id.ToString();
                cmd2.ExecuteNonQuery();
            }

                            // Insert new rows
            //ct1 = "SELECT fid, variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //ct2 = "INSERT INTO " + tbl1 + " (seriennummer, variante,charge,fdate) " +
            //    "VALUES (@val1, @val2, @val3, @val4)";
            //    cmd2.CommandText = ct2;
                cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
                    "VALUES (@val1, @val2, @val3, @val4)";
                foreach (Int64 id in List2)
                {
                    // Read all values into parameters
                    //cmd1.CommandText = String.Format(ct1, id);

                    cmd1.CommandText = "SELECT fid,variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();
                    cmd2.Parameters.Clear();
                    int i = 0;
                    using (MySqlDataReader reader = cmd1.ExecuteReader())
                    {
                        if (!reader.Read()) continue;
                        for (int n = 0; n &lt; reader.FieldCount; n++)
                        {
                            cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                                reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                        }
                    }
                    // Insert row
                    cmd2.ExecuteNonQuery();
                }

        }

        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.ToString());

        }

    }
    public Form1()
    {
        InitializeComponent();

        Init();

    }

}

}


How do I generate a SQL script from my diagram in MySQL Workbench? I've created an EER diagram with tables, foreign keys, etc in MySQL Workbench and now I want to generate a ".sql" script that will create this database. How do I do that?

See File -> Export -> Forward Engineer SQL Create Script...


A simple T-SQL query plan reuse question Even though I have parameterized this INSERT on SQL Server, it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?
The query is simple and fully parameterized:
INSERT INTO USER_LOG_DATA (POSTDT, SESSION_ID, USERID, SUBJECT, APP, ACTION_NAME, ARGS, SUMMARY, DETAIL) VALUES (@timestamp, @session, @user, @subject, @app, @action, @argsString, @summary, @detail)
However if the length of one of the strings changes, the plan is not re-used.  So if the value of the @userid changes from 10 to 8, a new plan is created.

...it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?

This is by design, and no you should not pad the data.
The ideal solution is to declare your variable size to the same length as the data type of the column it's being inserted into.  This will give you predictable plan reuse without having to send extra whitespace data.
The way you do this will vary based on how the insert is being performed.  If you're using stored procedures, just update the parameters to use the same length as the columns.  If you're using .NET's AddWithValue method (which is evil), use one of the APIs that allows you to set the parameter length.


Mysql master-master replication auto flush old logs I have mysql-5.1.41 master-master setup running on ubuntu machines and below is the configuration of a master:

skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.1.106
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
binlog-ignore-db = information_schema
binlog-ignore-db = lb1

log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.1.105
replicate-ignore-db = information_schema
replicate-ignore-db = lb2

relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

The log files seems to be growing everyday. Does anybody know what parameter I should include in above configuration to automatically remove/flush old unnecessasry logs with a decent value causing no damage to sync?.

To rotate out logs more than 7 days old, add this to my.cnf
[mysqld]
expire-logs-days=7

then restart mysql.
To perform this manually, run this command:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;

This will erase binary logs back to Midnight 7 days ago.
WARNING !!!
Do not erase binary logs from the OS level because it disturbs mysqld's internal functionality for rotating binary logs.
UPDATE 2012-01-24 13:20 EDT
Make sure you do not purge binary logs that replication is using.
Here is an example how you can safely erase binary logs manually...
For a Master-Master setup M1 and M2
Go to M2 and run this:
mysql> SHOW SLAVE STATUS\G

You will see two lines that show binary log names

Master_Log_File
Relay_Master_Log_File

Master_Log_File represents the current binary log on M1 that was last read on M2.
Relay_Master_Log_File represents the current binary log on M1 that was last executed on M2.
Let's suppose that M2 has this:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.176.205
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000131
          Read_Master_Log_Pos: 570079419
               Relay_Log_File: relay-bin.003496
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000131
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 570079419
              Relay_Log_Space: 545
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

mysql>

ON M2, Relay_Master_Log_File says mysql-bin.000131. So on M1, you can run this:
mysql> PURGE BINARY LOGS TO 'mysql-bin.000131';

Since Master_Log_File and Relay_Master_Log_File are the same, why not use Master_Log_File?
REASON #1 : SQL Error in the Slave
When an SQL Error happens in M2 on the SQL Thread, Replication stops processing SQL from the Relay Logs. The IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
REASON #2 : Long Running SQL Query in the Slave
If a query comes along, such as UPDATE that updates every row in a table and it takes 5 minutes. During those 5 minutes, the IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
Given both reasons, there are some occassion where Relay_Master_Log_File and Master_Log_File are different. In that instance, always use Relay_Master_Log_File. In light of this, there is another reason:
REASON #3 : Log Corruption in Slave or Master
There are rare times when bad network transmission will produce corrupt relay logs. When you do SHOW SLAVE STATUS\G, an error message will explain the the binary log may be corrupt. You should start firsdt with the relay logs.
To clear them out and start with fresh set, do SHOW SLAVE STATUS\G, get the Relay_Master_Log_File (RMLF), the Exec_Master_Log_Pos (EMLP), and run this on M2
STOP SLAVE;
CHANGE MASTER TO master_log_file='RMLF',master_log_pos=EMLP;
START SLAVE;

The CHANGE MASTER TO command erases all relay logs and starts downloading from scratch.
Once you START SLAVE;, if you get the corrupt error log message again, then network transmission was not the issue. The binary log on M1 may just be corrupt after all.
You must go to M1, make copy of the the suspected log, run mysqlbinlog against that copied binary log and redirect to a text file. Read the text file. If the text files contains gibberish or indiscernable characters, then you must perform a full sync of the slave.


Why is PL/Python untrusted? According to the docs:

  PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python.

Why exactly is it difficult to develop a secure execution mechanism for Python but not for other languages such as Perl?

It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:

https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter
http://tav.espians.com/paving-the-way-to-securing-the-python-interpreter.html
https://stackoverflow.com/q/525056/398670

The limitations aren't anything to do with PostgreSQL its self, they're inherent to the CPython interpreter implementation or possibly even the Python language its self.

Some other languages have checked runtimes, like Perl, Java, JavaScript and Lua. Most of them have faced a series of security issues as such confined execution environments are very hard to protect against all possible jailbreak exploits.

There's really nothing stopping PostgreSQL from adding a semitrusted Python interpreter, since rexec is "good enough" for many purposes. PostgreSQL doesn't tend to be keen on only-mostly-kinda-good-enough-maybe though. It would probably only be accepted if marked superuser-only, but you could always then grant access to it for specific users. It'd be better than untrusted Python.

Personally I think PL/V8 or similar is the future here, and would like to see it move toward being supported in core.

I've also vaguely explored the idea of a trusted Mono that can load "safe" assemblies written in C#, VB.NET, IronPython, or whatever but haven't been able to do much on that topic.


MySQL count rows from other table AND get most recent title I have a situation which can be boiled down to the following:

TABLE students
COLUMNS id, name, email

TABLE attendances
COLUMNS id, student_id, event_id

TABLE events
COLUMNS id, name, date_of_event

I want to be able to see all the students, and how many events they've attended, so I have:
select name, email, count(attendances.id) as number_of_shows_attended from students
left join attendances
   on student_id = students.id
group by name, email

RESULT

Jane Doe | jane@uni.edu | 0
John Doe | john@uni.edu | 10
Will Doe | will@uni.edu | 2

Perfect. Except I also wish to have a final column which lists the most recent event attended (which can be based on the events.date_of_event date column).
I have tried simply adding the missing column, like so:
select name, email, count(attendances.id) as number_of_shows_attended,
   event.name as most_recent_event_attended from students
left join attendances
   on student_id = students.id
inner join events
   on attendances.event_id on events.id
group by name, email

It gives me  an event name as expected, but how can I ensure it is the most recent one? I tried adding order by events.date_of_event desc, but of course it's sorting the results of the query.

You could do something like this (see fiddle here - it shows development of all stages of the query). There is a small possible flaw in your design - see discussion and (slightly changed) DDL and DML in an alternative schema available here (with slightly changed SQL also!).
In future, could you please provide us with some sample tables (DDL) data and data (DML) so that we have a Single Source of Truth for the question - it avoids errors and eliminates duplication of effort on behalf of those trying to help you!
I couldn't test my solution because I didn't have any data and I've stopped doing my own data mockups since when I do, the OP (frequently) comes back with something like "Oh, but, if I put in records with x, y and z...) your solution doens't work..." - that's then me then going #FF0000/rgb(255,0,0) and swearing at the computer (and the OP)...
I simulated data and tested - my previous solution fails - this one now works - it even takes account (through LEFT JOINs) of students who haven't attended any events!
Please test with real datasets with a large number of records and apply indexes to your tables' fields as appropriate. You can use MySQL 8.0.18 and above's new EXPLAIN ANALYZE (and see here) functionality to get a good idea of where indexes are required! If you run into problems, ask a new question here.
CREATE TABLE student
(
  s_id INT NOT NULL PRIMARY KEY,
  s_name VARCHAR (100) NOT NULL,
  s_email VARCHAR (255) NOT NULL
);

CREATE TABLE event
(
  e_id INT NOT NULL PRIMARY KEY,
  e_name VARCHAR (100) NOT NULL,
  e_date_scheduled DATE NOT NULL
);

CREATE TABLE attendance
(
  a_id INT NOT NULL PRIMARY KEY,
  a_sid INT NOT NULL,
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  a_eid INT NOT NULL,
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);

Populate the tables:
INSERT INTO student VALUES
(1, 'name_1', 'email_1@domain_1.net'),
(2, 'name_2', 'email_2@domain_2.net'),
(3, 'name_3', 'email_3@domain_3.net'),
(4, 'name_4', 'email_3@domain_3.net'); --   <<<---- attends no events!

INSERT INTO event VALUES
(1, 'event_1', '2020-01-01'),
(2, 'event_2', '2020-02-01'),
(3, 'event_3', '2020-03-01'),
(4, 'event_4', '2020-04-01'),
(5, 'event_5', '2020-05-01'),
(6, 'event_6', '2020-06-01');

INSERT INTO attendance VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),

(5, 2, 2),
(6, 2, 3),
(7, 2, 5),

(8, 3, 5),
(9, 3, 6);

The run the query:
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail,
       COUNT(a.a_id) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM
        student s2
      LEFT JOIN attendance a2
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id;

Result:
sid snam                   smail  a_cnt         eds  coalesce
1   name_1  email_1@domain_1.net      4  2020-04-01   event_4
2   name_2  email_2@domain_2.net      3  2020-05-01   event_5
3   name_3  email_3@domain_3.net      2  2020-06-01   event_6
4   name_4  email_3@domain_3.net      0        NULL     -----

Alternative schema:
The "flaw" to which I referred at the beginning is that you have an attendance table with an identity type PRIMARY KEY - it is not required - at least in this case.
Your use case (at least for this query) is a classic example of the JOINing table design pattern - aka a linking table (and many other names - there are 16 listed on the reference below). The formal name for this type of database object is an Associative Entity and the PK for such a table is normally just the two JOINing fields - no need for an identity column at all!
So, your attendance table definition should look more like this:
CREATE TABLE attendance
(
  -- a_id INT NOT NULL PRIMARY KEY,   -- << NOT necessary!
  a_sid INT NOT NULL,
  a_eid INT NOT NULL,
  CONSTRAINT a_s_e_pk PRIMARY KEY (a_sid, a_eid),
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);

The final query is now as below - the joins are now done exclusively using the JOINing fields and not the, no longer needed, surrogate PRIMARY KEY.
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail,
       COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM
        student s2
      LEFT JOIN attendance a2
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id, t2.eds  -- << Can invert depending on requirements

Result identical!
Performance seems very similar - see the profiling at the end of the fiddle - however, the composite PK appears on average - tried 5 runs of each - to be slightly better but YMMV depending on your data distribution?


How to get for a percentage here in below query? A small change: report for each year the percentage of movies in that year with only female actors, and the total number of movies made that year.

SELECT Year,
       COUNT(*),
       (COUNT(*)*100/(count(Movie.mid)*1.0))
FROM Movie
WHERE TRIM(MID) IN (SELECT TRIM(MID)
                    FROM M_Cast
                    WHERE TRIM(PID) IN (SELECT TRIM(PID)
                                        FROM PERSON
                                        WHERE Gender ='Female'
                                       )
                   )
GROUP BY Movie.Year
ORDER BY COUNT(*) DESC

What should I change in this to get the correct percentage?

I'll approach it with a new DB design that suits the purpose.

So the Movie entity has a n:m relation with the Person entity. A person is cast in many movies and a movie has many persons in its cast. The movie entity has an year column and the person entity has a gender column.

So create this database as done at SQLFiddle here. Create two tables by queries as follows.

DROP TABLE IF EXISTS temp1;
DROP TABLE IF EXISTS temp2;
CREATE TABLE temp1 (
  SELECT COUNT(Pname) AS pcount,
    Pname,
    YEAR
  FROM Movie m,
    Person p,
    M_Cast MC
  WHERE  gender="Female"
  AND m.mid=mc.mid
  AND p.pid=mc.pid
  GROUP BY YEAR,pname
);

create table temp2
select Year, sum(pcount) as sumyear
from temp1
group by Year;

Then query the two temp tables as so:

select
  t1.pname,
  t1.year,
  t1.pcount as MoviesByPerson,
  t2.sumyear MoviesInYear,
  t1.pcount*100/t2.sumyear as percentage
from temp1 t1, temp2 t2
where t1.year=t2.year

You can run the SQL Fiddle snippet and see the result.


How can I write queries as fast as possible in MySQL? I'm looking for two things:

Awesome manual about writing fast MySQL queries.
Tool which will decompose my query into discrete steps, and give estimations of each step.

I've seen the next tool, but I don't know how to use it.

I have used HeidiSQL for a while and it have very useful profiling feature that allow to get detailed profile of query execution. Also EXPLAIN is the first tool you have to learn.

Less obvious is that you have to place your databases on the filesystem with noatime option. Each time some software read/write from/to the disk, another implicit write(s) performed - to update "last access time" for accessed files. When noatime active that time is not updated and overall iops is halved.


SQL Server: INSERT in transaction locks the table until transaction finished The sample VB.Net code below gets stuck and times out at the first read operation on CMD2.
That seems to contradict the top ranked answer to question 6374.

If that answer was correct, what can help me avoid the lock here?

SQL Server 2008 was used for testing this.

    Using cn1 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cn2 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cmd1 As SqlCommand = cn1.CreateCommand,
          cmd2 As SqlCommand = cn2.CreateCommand

        cn1.Open()
        cn2.Open()

        Try
            cmd1.CommandText = "CREATE TABLE ttt1 (x int, y int)"
            cmd1.ExecuteNonQuery()

            cmd1.Transaction = cn1.BeginTransaction(IsolationLevel.ReadCommitted)

            cmd1.CommandText = "INSERT INTO [ttt1] VALUES (1,2)"
            cmd1.ExecuteNonQuery()

            ' The next query holds up execution,
            ' waiting for the table to become available
            ' until a timeout occurs.
            ' Tried "select top 1 x" as well: also times out.
            cmd2.CommandText = "SELECT count(*) FROM ttt1"
            Dim i1 As Integer = CInt(cmd2.ExecuteScalar)

            cmd1.Transaction.Commit()

            Dim i2 As Integer = CInt(cmd2.ExecuteScalar)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            cmd1.CommandText = "DROP TABLE ttt1"
            cmd1.ExecuteNonQuery()
        End Try

        cn2.Close()
        cn1.Close()
    End Using

I would post this as a comment to the highest quoted answer to question 6374, but (1) it's too long to post as a comment, (2) I don't have the required points to comment yet, (3) posting a question as an answer to another question seemed even more wrong than launching a new question about the same thing.

By issuing a SELECT COUNT(*) FROM ... you are requesting to read every row in the table. This will include the newly inserted, uncommitted, rows. As the original answer says, at no time is the table locked. By designing a test that explicitly looks at the locked rows you are, as expected, blocking yourself.

Well tuned workloads never scan the entire table. Scans create performance problems because they need to read the entire table and, as you discovered, are guaranteed to block behind any lock.


Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

You might not need a custom type - numeric(38,0) might be suitable here (though I don't know the full range for valid 128-bit integers or whether IPv6 might be constrained to the range of values that would fit):

CREATE TABLE #ips
(
  iplow numeric(38,0),
  iphigh numeric(38,0),
  c varchar(64),
  d int,
  e nvarchar(128)
);

INSERT #ips VALUES
(58568835385568506466387976054061924352,58568835464796668980652313647605874687,
    '2c0f:f288::/32',328039,N'JSDAAV-ZA-Telecoms-AS'),
(58568842991472107835764385034281156608,58568842991473316761583999663455862783,
    '2c0f:f2e8::/48',37182 ,N'TERNET'),
(58568844892948008178108487279335964672,58568844892949217103928101908510670847,
    '2c0f:f300::/48',37153 ,N'Hetzner'),
(58568847428249208634567290272742375424,58568847507477371148831627866286325759,
    '2c0f:f320::/32',37126 ,N'BELL-TZ'),
(58568849329725108976911392517797183488,58568849408953271491175730111341133823,
    '2c0f:f338::/32',327983,N'Interworks-Wireless-Solutions');

DECLARE @ip numeric(38,0) = 58568842991472107835764385034281156617;

SELECT iplow,iphigh,c,d,e
  FROM #ips
  WHERE @ip BETWEEN iplow AND iphigh;

DROP TABLE #ips;

If this doesn't work for the addresses you need to support then you may need to separate out the network prefix (48 bits), subnet ID (16 bits), and interface ID (64 bits) into three separate numbers before passing them off to SQL Server. Your where clauses would become more complex but this might be a simpler approach than trying to represent an IPv6 as a single number anyway.

  Is there a way to properly handle IP address ranges in SqlServer ?

SQL Server has no built-in capability to understand what an IP address is.


Listener IP address offline I have an availability group on SQL Server 2016 SP1 CU5 on Windows 2012 R2.
It works fine, but I need to add an IP address on another network.

So I added the IP:

ALTER AVAILABILITY GROUP [AG_MOON]
MODIFY LISTENER N'AGLISTMOON'
(ADD IP (N'10.20.2.102', N'255.255.255.0')
);

The command runs successfully. Then I query the view: sys.availability_group_listener_ip_addresses:

SELECT * FROM sys.availability_group_listener_ip_addresses;

I can see both addresses, but the old address is in state 1 (ONLINE). The new address is OFFLINE.

There are no errors or other indications of a problem in the error log.

How can I set it ONLINE?

There can only be one IP address online for the listener at a given time.  Both IP addresses are registered in DNS (by default) even though one is offline, and when a client looks up the address of the listener, it will get two addresses.  If the client is "multi-subnet failover aware," it will try both addresses and use the one that responds

If the client is not "multi-subnet failover aware," it may try to use the IP address that is offline, and accordingly will fail to connect.  So if you start having intermittent client connection failures, this is why.

To have it only register the IP address that is online, change the RegisterAllProvidersIp setting on the listeners network name resource to 0.  See Multi-subnet Clustered SQL + RegisterAllProvidersIP + SharePoint 2013


Do too many subquries create multiple sleeping processes in MySQL? I am using java & MySQL to make an app.
When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed.

I have read many answers around which tell the command to masskill the process or some suggests checking the code for bug.  I have checked that I'm closing the connections properly using the Apache Commons DbUtils.

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(con);

I am not able to figure out why these processes don't get killed after closing the connection.

My question is

Do too many subquries create multiple sleeping processes in MySQL? I don't see much relation though and didn't find anything on search.
Does passing connection object through methods create extra/multiple processes?

Regards

One connection = one process (or thread).  Period.  Subqueries, UNIONs, and PARTITIONing do not create subprocesses.

What causes Sleeping processes:

Connections that are busy with non-SQL work.  (Usually 'Time' is 0 or small.)
Connections that don't go away.
Connection pooling at the client.

Sleeping processes are relatively harmless.  No need to kill them.  All connections (sleeping or not) are capped at max_connections, after which, trying to get a new connection gets an error.

On the other hand, sometimes the client(s) have parameters that lead to an unreasonable number of connections.  Apache has what used to be called MaxClients, which (in my opinion) should not be set higher than 20.


Compare two columns for difference mark the difference in mysql I need to compare two fields and mark the similar fields in third table.

For Example:

Tables

customer:
  phone id,
  phonenumber
  followstatus

received_Customer:
  rc_id,
  rc_phonenumber.

I need to mark the fdnumber with f or n/a in follow up which is in received and customer table.

update customer
select b.phonenumber
from received_customer n
join customer b
on b.phonenumber = n.phonenumber
SET followUpStatus= 'y' ;

I run query in php.

That is not the correct syntax for an UPDATE+JOIN. Try the following (assuming it is what you want, updating the table customer):

UPDATE customer b
JOIN received_customer n
ON b.phonenumber = n.phonenumber
SET b.followUpStatus= 'y' ;


Duplicate record script | Efficiency boost I have devised a script to search through my table for duplicate records and display their count.

I am wondering if there is a way to improve the efficiency of the search, given the current search takes 7 seconds. Here's the code:

use gameStore;
select ID, count(*) as idRepl,
console, count(*) as consoleRepl,
price, count(*) as priceRepl,
purchased, count(*) as purchRepl from wishlist
group by console, price, purchased having count(*) > 1;

EDIT 1

EXPLAIN RESULTS

FIELD     |TYPE       |NULL|Key    |DEFAULT|Extra
ID        |INT        |NO  |PRI    |       |auto_increment
console   |VARCHAR(50)|NO  |       |       |
Price     |FLOAT      |YES |       |NULL   |
Purchased |VARCHAR(4) |YES |       |NO     |

The troubles with count aside, probably simplest way to make that query fast is adding multicolumn index

(console, price, purchased)

if table wishlist is InnoDB and ID is primary key or

(console, price, purchased, ID)

if table wishlist is MyISAM.

Such index is enough to cover the query.


oracle crash with ORA-00942: table or view does not exist we use oracle9i database. and since last week my oracle crash many times, after running for 30 hours more or less.
   we see errors repeat in my trc file and alert log :

qmhGetHTTPError: Got unknown oracle error. Error stack is:
ORA-00942: table or view does not exist

we do not find the exact table or view does not exist.
   if we upgrade our database to oracle 10g or 11g,will solve the problems?
   I found some error another log,

error 1242 detected in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 16 (block # 156925)
ORA-01110: data file 16: 'D:\ORACLE\ORADATA\SDH\SDH_DATA4.ORA'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile()
O/S-Error: (OS 1450)

 will above implies hard disk error? anyone gets an idea? thanks for all.

The error you're getting indicates that a query is trying to reference an object that doesn't exist or that the user doesn't have access to.  If you started getting it a week ago and weren't getting it before that, something changed a week ago so that an object was dropped, a privilege was revoked, or a SQL statement was added that references an invalid object.  It is unlikely that upgrading the database would resolve the error.

Can you determine the SQL statement that your application is issuing when it receives this error?  If not, can you add additional logging so that you can determine what statement is generating the error?  If your application doesn't log the SQL statements it generates, you could create a SERVERERROR trigger (based on this trigger code).  Obviously, you would probably want to log more than just the SQL statement.

create table stmt_err_log (
  sql_stmt varchar2(1000)
);

 CREATE OR REPLACE TRIGGER after_error
  AFTER SERVERERROR ON DATABASE
  DECLARE
  pragma autonomous_transaction;
  id NUMBER;
  sql_text ORA_NAME_LIST_T;
  v_stmt CLOB;
  n NUMBER;
 BEGIN
  n := ora_sql_txt(sql_text);
  --
  IF n >= 1
  THEN
    FOR i IN 1..n LOOP
      v_stmt := v_stmt || sql_text(i);
    END LOOP;
  END IF;
  --
  FOR n IN 1..ora_server_error_depth LOOP
   IF ora_server_error(n) = '942'
   THEN
     insert into scott.stmt_err_log( sql_stmt )
       values( v_stmt );
     commit;
   END IF;
 END LOOP;
  --
 END after_error;


How can I get the page size of a Postgres database? Is there some way to select the page size of a Postgres database?

I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.

If you don't have access to pg_controldata ...

There is a simple way:

test=# SELECT current_setting('block_size');
 current_setting
-----------------
 8192

The manual:

  The following “parameters” are read-only, and are determined when
  PostgreSQL is compiled or when it is installed. [...]

  block_size (integer)

  Reports the size of a disk block. It is determined by the value of BLCKSZ when building the server. The default value is 8192 bytes. The
  meaning of some configuration variables (such as shared_buffers) is
  influenced by block_size. See Section 19.4 for information.

To verify

Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()

test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:

  Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).


Inserting with implicit type conversion causes warning for cardinality estimates I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:

  Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.

Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?

I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.

This question has attracted some potential duplicates, including:

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?
Type conversion in expression may affect "CardinalityEstimate" in query plan choice?

I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.

What I'm specifically looking for out of an answer is:

An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?
What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?
Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?
Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

I recreated it with the below simple example (paste the plan)

DROP TABLE IF EXISTS #MyFunStringTable;
DROP TABLE IF EXISTS #MyFunIntTable;

CREATE TABLE #MyFunStringTable
(
  SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL
);

CREATE TABLE #MyFunIntTable
(
  EvenCoolerColumn bigint NULL
);

INSERT INTO #MyFunIntTable
( EvenCoolerColumn )
VALUES
( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT EvenCoolerColumn
    FROM #MyFunIntTable;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 ),
( 2 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
SELECT 1
UNION ALL
SELECT 2;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1
    FROM #MyFunIntTable;

Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

As a purely practical matter, that is pretty much the end of it. The precise conditions that trigger this optimizer warning are undocumented.

That said, I will go into a little detail to satisfy a little of your curiosity.

  1. An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?

There are things going on of interest to the cardinality estimation component of the query optimizer. This component keeps track of a wide range of properties and other information for each logical operator in the query tree. This includes histograms (from disk, or derived entirely in-memory), domain information, functional dependencies etc.

In your specific example, the warning is triggered when the input to the convert is known to be monotone but the output is not. Conversion from integer literal to bigint retains this property. Conversion from bigint to nvarchar(50) does not.

When there is a single literal integer constant, parse-time constant-folding converts this to nvarchar before optimization starts. This avoids type conversion in the plan, and the associated warning.

There are detailed internal implementation differences between the original cardinality estimator and the new cardinality estimator that mean some statements will generate a warning under one CE but not the other.

  2. What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?

In your example statements, none. As noted, the conversion can affect other internal data, which might affect e.g. other plan operators, or the code path taken through the optimizer. The broad point is that there is more than a raw cardinality estimate to consider. Internal differences can affect things like indexed view or computed column matching, among other possibilities.

  3. Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?

This appears to be the same as the previous question.

  4. Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

There is no option to turn off these warnings.


Throttle DDL statement to avoid impacting performance of other queries In MySQL, is there any way that I can make an ALTER TABLE or other DDL statement run more slowly, to mitigate its impact on the performance of other queries against the same database? I have found that a long-running DDL statement can cripple the performance of other queries against the database, and I'd like to be able to ALTER huge tables without suffering this impact.

To be clear, this question isn't about locks - online DDL solves most of our locking issues and allows us to run DDL commands on big tables without outright blocking writes to the table. It's purely about mitigating the performance impact that the heavy CPU and IO load imposed by running a DDL statement against a large table can have on the MySQL server. Nor am I interested in strategies that involve setting up a replicating slave on another computer and running the DDL statement there; I know that this is possible, but would prefer the more lightweight approach of just throttling the DDL if that is possible.

MySQL has the LOW_PRIORITY keyword for DML statements to mitigate their performance impact, and there's some discussion on Stack Overflow about the similar-in-principle problem of mitigating the performance impact of mysqldump, but I can't find anything similar for ALTER TABLE statements.

I hope your tables are InnoDB, not MyISAM.  Some of the following will not work well with MyISAM.

Plan A:  Use Percona's pt-online-schema-change.  It is self-throttling.

Plan B:  (You don't like this.)  Many ALTERs can, especially in 5.6 or 5.7, run ALGORITHM=INPLACE, making it much less invasive than the way (ALGORITHM=COPY).

Plan C:  (You don't like this.)  Play replication games.  This leads to virtually zero impact on other queries.

Plan D:  Galera-based replication (PXC, MaraiDB+Galera, etc).  This is also a replication game, but the tools probably make it less effort for the DBA.

Plan E:  If you are adding a column, build a parallel table for the extra column(s).  This has zero impact to set up.

Plan F:  Have a layer of code to hide the original table from the modified table.  Write some extra code to handle, with zero downtime, the transition, regardless of how long it take.

Plan G:  Pay for MEB - it plays some nifty games to make backup faster.


Why do OLAP cubes perform better than Relational databases on certain tasks? My team uses a Multidimensional OLAP database for a lot of it's analytics and predictive modeling. The standard line in all the documentation is "OLAP cubes are better suited for this type of application than relational databases".

On the other hand, you frequently hear that a relational database with a star or snowflake schema (sometimes called ROLAP) can do anything that a true OLAP database can do.

Why is it exactly that OLAP is better for analytical processing, aggregating, etc... than the relational model?
Is it true that ROLAP can do anything that a true OLAP can do, or are there still limitations or performance issues that can only be solved by using a true OLAP db?

OLAP cube stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures. Dimensions organize data with relation to an area of interest, such as customers, stores, or locations, time etc. Measure performs business-specific calculations to the required format. Both these drastically improves the performance of queries when come to reporting.

Advantages:

•   High Speed of Data Processing

•   Aggregated and Detailed Data

•   Multidimensional Data Representation

Limitations:

•   OLAP requires restructuring of data into a star/snowflake schema

•   There is a limited number of dimensions (fields) a single OLAP cube

•   It is nearly impossible to access transactional data in the OLAP cube

•   Changes to an OLAP cube requires a full update of the cube

ROLAP and MOLAP are two models in OLAP. ROLAP creates a multidimensional view of data dynamically while MOLAP already stores the static multidimensional view of data in MDDBs.


Deadlock on Delete Statement I get a deadlock when a SQL Server Job runs. The deadlock occurs on a simple DELETE statement.
I would have thought there would have to be a SELECT/UPDATE query running to cause the deadlock? But looks like it is DELETE/DELETE deadlock...

What I am looking for is why I am getting a DELETE/DELETE deadlock. It is (to my knowledge) passing in different parameters.

Any ideas?
Thanks.

deadlock-list
2014-05-20 07:30:09.66 spid25s      deadlock victim=process409048
2014-05-20 07:30:09.66 spid25s       process-list
2014-05-20 07:30:09.66 spid25s        process id=process409048 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127294 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x397219620 lockMode=U schedulerid=5 kpid=3792 status=suspended spid=150 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process432e08 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=2648 ownerId=629859744 transactionname=DELETE lasttranstarted=2014-05-20T07:30:04.833 XDES=0x4c3426b50 lockMode=U schedulerid=6 kpid=5988 status=suspended spid=146 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:04.833 lastbatchcompleted=2014-05-20T07:30:04.820 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629859744 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2
2014-05-20 07:30:09.66 spid25s     DELETE FROM dbo.UserDetailsData WHERE        (Username = @P1) AND (UserDate = @P2)
2014-05-20 07:30:09.66 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2014-05-20 07:30:09.66 spid25s     unknown
2014-05-20 07:30:09.66 spid25s         inputbuf
2014-05-20 07:30:09.66 spid25s        process id=process39ea562c8 taskpriority=0 logused=0 waitresource=PAGE: 12:1:7127916 waittime=4352 ownerId=629860973 transactionname=DELETE lasttranstarted=2014-05-20T07:30:05.307 XDES=0x13e0e4b50 lockMode=U schedulerid=2 kpid=7124 status=suspended spid=150 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2014-05-20T07:30:05.307 lastbatchcompleted=2014-05-20T07:30:05.307 clientapp=QSQL25 hostname=MORRIS hostpid=1528 isolationlevel=read committed (2) xactid=629860973 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2014-05-20 07:30:09.66 spid25s         executionStack
2014-05-20 07:30:09.66 spid25s          frame procname=adhoc line=1 stmtstart=68 sqlhandle=0x020000000b887a18f75d0aa07c25a9b8630fca696aa0e5d2

What I am looking for is why I am getting a DELETE/DELETE deadlock.

It appears the deadlock occurs because:

spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422
spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked
spid 54 ecid 2 requests an update (U) page lock on the same page...

Pages are being prefetched for the query, with update locks acquired by ecid 0. That is step 1 above. In step 3, a child thread of the same parallel query (ecid 2) requests the same lock. Normally this would not be a problem. SQL Server knows ecid 0 and ecid 2 are threads of the same parent process. Unfortunately, step 2 gets in the way of this, and a deadlock results.

That said, you shouldn't really care much about why the deadlock occurs, the important question is how do you avoid it. The answer is to provide an efficient access path for the DELETE. The statement needs to find rows WHERE Username = @P1 AND UserDate = @P2, so you should have an index keyed on these columns.

And of course you do have such an index. The real question is why your problems started occurring after you added filtered indexes.

The answer to that is extra column information is needed to locate the filtered index rows to delete (and to check their predicates). If the query uses a narrow/per-row execution plan, the execution engine is unable to fetch the extra columns in the Clustered Index Delete operator, as it would in a wide/per-index plan.

You can find more details about that, and a worked example in this blog post.

In this case, the column information needs to come from the part of the plan to the right of the Clustered Index Delete, and so a parallel clustered index scan is used, and you get a slow query with high deadlock potential.

The answer is to do one of the following:

Remove the filtered indexes
Add filtered index key/include/predicate columns to the existing name/date index
Force a wide update plan (no supported way to do this)
Run the query under snapshot isolation (not RCSI)

Option 2 would be my strong preference.

Option 4 (thanks Jack Douglas) has the advantage of removing deadlocks, and ought not cause any "update conflicts", given the disjoint nature of the changes, but it does require enabling snapshot isolation at the database level, explicitly changing the isolation level, and will not fix the underlying issue: you'll still end up with a wasteful parallel table scan, where a nice index seek is what you really want.


Daily SQL Server Alert: BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device I'm running a ~100Gb database on Microsoft SQL Server 2016 (SP2-GDR)  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

Every weekday night just after 7pm I get a SQL Server Alert email like this:-

SQL Server Alert System: 'Severity 016: Miscellaneous User Error' occurred
BackupVirtualDeviceFile::SendFileInfoBegin:  failure on backup device

My regular daily and weekly database backups all happen at 2am (so it's not those), so I can only assume it's my hourly transaction log backups. But the only SQL agent job that fails is a C# program doing a database read (it times-out), and I can find no other errors in my SQL Agent Logs at that time. And my daily CheckDB Maintenance "Check Database Integrity Task" says the databases are fine. Any thoughts or suggestions? Thanks.

BackupVirtualDeviceFile usually indicates a VDI or VSS backup from a third-party tool. These are usually snapshot-based VM backups that use VSS writers to take application-consistent snapshots of machines, tools like Veeam or CommVault for example.

They can also be third-party backup tools that backup to a Virtual Device and stream the data to an agent or application for further processing, such as compression or encryption.

Check your Windows System and Application event logs for errors, warnings or info messages relating to VSS providers, requestors or writers. In the case of SQL Server, it is installed by default with the SQL VSS Writer which handles VSS requests from these sort of tools. If you see warnings in your SQL Server Error Log about I/O freezes at the same time, this is another indicator of VSS-based backups occurring.

For VDI-based backups not using VSS, it can be a little harder to identify. Try capturing the output of sp_whoisactive frequently during the window this occurs and identify the source of the BACKUP command.

Once you identify the tool in question, check the relevant logs, or with the system administrators responsible to get further error details as this is potentially an issue outside of SQL Server causing this problem.


Storing time pricing information I have an application for a business customer and one of the new components is a time tracking system. The actual time tracking is done (pretty standard schema), but the structure this company uses to price their time is really odd and I don't know how to properly store this.
0-10 minutes: €16
10-15 minutes: €23
...
35-40 minutes: €58
more minutes: €63
In short, it's a completely irregular structure and while I could make a table with a column per interval, that seems to me like a smelly solution. A second table with a foreign key and interval and price also doesn't sound ideal. There should also be multiple of these timetables (different per employee / role).
Does anyone have any advice on storing this?

Store it in a way that makes sense, which is to say ignore the interval approach and just store the breakpoints like such:
Role        | MaxMinuteAmt      | Cost
Plumber     | 10                | 16
Plumber     | 15                | 23
Plumber     | 40                | 58
Plumber     | 32767             | 63

The primary key for this table is (Role,MaxMinuteAmt).  This prevents any data integrity issues with improperly entered intervals and assists in the quick lookup of the appropriate value.
To return the appropriate Cost, you would use the following query (which can be hardened into a stored procedure):
SELECT
  EmpRoleCost.Cost
FROM
  EmployeeRoleCost EmpRoleCost
WHERE
  EmpRoleCost.Role = 'Plumber'
    AND EmpRoleCost.MaxMinuteAmt =
      (
        SELECT
          MIN(MaxMinuteAmt)
        FROM
          EmployeeRoleCost
        WHERE
          Role = EmpRoleCost.Role
            AND MaxMinuteAmt >= <Number of Minutes>
      )

So if we query with a duration of 1 minute, the minimum value greater than or equal to that value is 10 and so on. Anything over 40 minutes up to the maximum SMALLINT value would return the same value (you could go with a larger datatype if needed).
This is assuming that anything up to 32,767 minutes would be charged €63, but my guess is there's probably some fixed rate once you get above a certain value (say 60 minutes).  In that case you'd change the above table to:
Role        | MaxMinuteAmt      | Cost
Plumber     | 10                | 16
Plumber     | 15                | 23
Plumber     | 40                | 58
Plumber     | 60                | 63

And create a second table to store the rate for amounts over, along with the duration of the rate (let's say 15 minutes for this example):
Role        | MinuteAmt     | Rate
Plumber     | 15            | 15.75

Then we'd change the query to utilize that rate if no flat cost is found:
SELECT
  COALESCE
    (
      EmpRoleCost.Cost
     ,EmpOverage.Rate * CEILING(<Actual number of minutes>/EmpOverage.MinuteAmt)
    ) AS Cost
FROM
  EmployeeRole EmployeeRole
LEFT JOIN
  EmployeeRoleOverageRate EmpOverage
    ON EmpOverage.Role = EmployeeRole.Role
LEFT JOIN
  EmployeeRoleCost EmpRoleCost
    ON EmpRoleCost.Role = EmployeeRole.Role
        AND EmpRoleCost.MaxMinuteAmt =
          (
            SELECT
              MIN(MaxMinuteAmt)
            FROM
              EmployeeRoleCost
            WHERE
              Role = EmployeeRole.Role
                AND MaxMinuteAmt >= <Number of Minutes>
          )
WHERE
  EmployeeRole.Role = 'Plumber'

So in this case if the number of minutes was 65, the cost would be €78.75 (CEILING(65/15) * 15.75).
See this Fiddle.


High insert rate on MySQL I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event creates a record in the log table. The database must be MySQL.

My experience in database consists of making queries, not building the database. And, i'm new to MySQL.

The log table is:

create table LogEvent
{
    id bigint auto_increment primary key,
    area smallint unsigned not null,
    area_zone tinyint unsigned not null,
    value1 float not null,
    value2 float not null,
    reason char(2) not null
}

The id is just to have a primary key. area is from 1 to 100 at the moment, but it can grow. We can consider it as a category. area_zone is like a subcategory of area. Other values are insignificant for my problem.

There will be a lot of inserts but no delete/update. The select queries will always be like select * from LogEvent where area=1 and area_zone=1. All select statements will include area and area_zone. Select queries won't happen often, maybe a few times a week.

It will grow over 1 million records easily. And eventually, millions per area/area_zone.

So, my questions are:

1) I'll need partitioning for sure. But, can I do it per area/area_zone? So, two values partitionning. Or should I use a mediumint and concatenate the two values together in my application (bitwise operation)? That way, it will be a single value partitioning

2) MyISAM or innoDB?

3) Should I put index somewhere? I'm very bad in this domain.

4) Hard disk space won't be a problem, but what about RAM? How much should I have? According to MS SQL Server, it use 28 bytes to store a record, so it should be similar in MySQL.

It's the only table in this database.

I hope I have been clear enough to help you help me. Thanks.

Partition Question

You could partition by area and area_zone. As a result your CREATE TABLE statement would look something like this.

create table LogEvent
(
    id bigint auto_increment,
    area smallint unsigned not null,
    area_zone tinyint unsigned not null,
    value1 float not null,
    value2 float not null,
    reason char(2) not null,
    primary key(id,area,area_zone)
)
PARTITION BY RANGE COLUMNS (area,area_zone)
(
  PARTITION p01 VALUES LESS THAN (10,20),
  PARTITION p02 VALUES LESS THAN (20,30),
  PARTITION p03 VALUES LESS THAN (30,40),
  PARTITION p04 VALUES LESS THAN (40,MAXVALUE)
);

You will notice that I modified your PRIMARY KEY definition as a PRIMARY KEY must include all columns in the table's partitionning function.  There is also some learning you will have to do to understand in what partition you're rows would be stored.  Here is a link that explains how and where rows would be inserted based on the way you define you're partitionning scheme.

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

The data types you choose for area and area_zone will depend on how unique both will be. As you said you are expecting millions of rows, the question is, will there be millions of different areas and area_zones. The number of unique areas and area_zones could not go past the limits defined by smallint and tinyint.

Also keep in mind that some partitions will be much larger than others. If you partitioned by ID, the partitions would eventually be about the same size.  Using area and area_zone, some may be much larger than others.

InnoDB versus MyISAM

Rolando answers your question about MyISAM versus InnoDB quite nicely in this post:

Choosing MyISAM over InnoDB for these project requirements; and long term options

What columns to Index

For question 3, knowing where to add an index depends on the cardinality/selectivity of the column you wish to index. If a column is very dense, in otherwords it has a large number of duplicate values, then it makes less sense to create an index for that column alone. In your situation, you have two columns, area and area_zone.  Area alone may not be selective enough as there may be many area_zones per area.  I think you could create a composite index on both columns to obtain a better cardinality and thus a more useful index.  Keep in mind that when you add an index, there may be an impact on performance and storage as MySQL must store the data in the index you created as well as in each column being indexed.  Here is a MySQL post on numeric data types to give you an idea of the amount of space each type takes.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

CREATE INDEX idx_area_area_zone ON LogEvent(area, area_zone);

Here is a great article on composite indexes that you may find useful:

http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/

Memory Attribution Question

To answer your RAM question, you could start with a relatively amount 2GB and a small InnoDB buffer pool size example (512Mb) since you are saying that SELECTs won't happen often.  Once your server is in production, you can calculate how much of the buffer pool MySQL is actually using.

To help you determine that value, I am quoting Rolando from one of his previous posts:

"What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server." Once your server is in production, you will be able to calculate what percent of your InnoDB buffer pool is actually in use. He provides formulas to help identify that percentage.

What to set innodb_buffer_pool and why..?

If you choose to use MyISAM for your LogEvent table, then you would give that memory to other variables such as key_buffer and join_buffer_size.  The ratio of interest as far as memory usage is concerned, would be your Key Cache Hit Ratio

  1 - (Key_reads/Key_read_requests)

Which should be as close as possible to 100%


Prepared statement overhead I am using libpqxx to communicate with a PostgreSQL 9.3 server.

For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each.  If they are all wrapped up into a single CTE, the entire prepared statement costs approximately 2ms.

In fact, nearly all individual prepared statements cost approximately 2ms, 1ms for unlogged tables.

What explains this exactly?

Edit

I first broke down my most expensive set of 14 prepared statements into two CTEs to avoid the single row write rule.

This dropped the average time consumed from below 40ms to approximately 10ms.

To avoid the single row write rule, I tried a function with input parameters.  This was a disaster despite its convenience, driving time consumed to approximately 500ms.

I just completed the single CTE, and I have found no difference between using two CTEs in halves and the single, but I think there may be some caveats.

To merge the two CTEs, I had to work a variant of this solution because of how I'm moving data between tables, copying from one table to another, deleting that source data, and rebuilding it.

Keys that are not present in the new data are deleted, coexisting ones are updated if they have new data, and keys not present in the old data are inserted, converting a delete+insert into a delete+update+insert.

This prepared CTE is now almost 25 queries long vs the original 14; however, very few rows are being moved.  In the future, many more rows should be moved.

It seems strange that the single CTE would consume as much time as the two-CTE since all queries are running more or less concurrently, and less rows are being altered.  My guess is that since this CTE is much larger, it's taking more trials for the server to come up with an optimal query plan.

Single CTE now only consumes 5ms on average

As I ran the query more, the average started dropping.  It's currently dropped to a 5ms average.  This is on an i7 laptop. I still haven't ran the query that many times, so there may be room for more improvement.

Since I have no knowledge of the PostgreSQL source code and can't determine how much of the performance was due to roundtrips between database and application despite them being on the same box, my best guess is that nearly all of the performance increase is due to the efficiency of PostgreSQL CTEs, considering the default concurrent execution of the sub-statements and main query.

Hopefully, an expert can give a full explanation, but as for me and for performance, I will try to cram every process into a single CTE.

The next to last major step was to find out that it is, unfortunately for MySQL users, only MySQL that joins slowly, so these write intensive tables were all given unique integer identifiers to join upon.  That took off 1/3 of the remaining average time consumption reduction.

The last step was taking CTE optimization fencing into account which took off the last 2/3 of reduction.

In the case of the above set of queries, the average time consumed is now between 1 and 2ms on an i7 laptop tuned for SSDs even though I'm using the included hard drive.  On a server with a single SSD, the time consumed averages less than 1ms.

To get the last performance boost, almost all queries were condensed, so for example if a table needed to be updated, it's best to do it in one query no matter how strange or performance reducing it might appear.

Reading to assemble relevant primary key values required more testing.  If it was a combination of RETURNINGs, it was best to allow each sub-statement to recalculate it rather than depend upon another sub-statement to do the calculation.  If the read had to go to disk, even if it was small, it was best to do that once and reference it across the multiple writing queries.

Copying was another grey area.  If a table required inserts, updates, and deletes based upon data from another table, it was slower to pre-select the data.  It was faster to simply reference the primary key data.

In general, aside from reads that go to disk to assemble primary key values to reference, it is best to compress a CTE as much as possible though it may appear strange to keep the chains as short and narrow as possible.

Multi-prepared statement transactions that previously consumed 10s of ms in my application now consume max 5ms.

This approach should probably be limited to small amounts of redundant data.


Is there a better option than Union All for multiple selects from the same row? Example Schema:

CREATE TABLE [dbo].[Base](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Option1ID] [int] NULL,
[Option2ID] [int] NULL,
[Option3ID] [int] NULL,
[Option1Name] [varchar] NULL,
[Option2Name] [varchar] NULL,
[Option3Name] [varchar] NULL,
[Option1LName] [varchar] NULL,
[Option2LName] [varchar] NULL,
[Option3LName] [varchar] NULL,)

Is there a way to get results that show up like:

ID | OptionID | OptionName | OptionLName

I have tried to achieve this using UNION ALL but this means going over the same row 3 times in my example, in my real problem I have to do it 10 times. I cannot normalize the table due to legacy code. Is there a way to only go over the Base row once?

You can use CROSS APPLY ... VALUES to UNPIVOT multiple columns

SELECT ID,
       OptionID,
       OptionName,
       OptionLName
FROM   [dbo].[Base]
       CROSS APPLY (VALUES([Option1ID], [Option1Name], [Option1LName]),
                          ([Option2ID], [Option2Name], [Option2LName]),
                          ([Option3ID], [Option3Name], [Option3LName]))
                     V( OptionID, OptionName, OptionLName)

The execution plan for this has one scan of Base. The plan is in fact the same as for the 2005 compatible rewrite that uses UNION ALL

SELECT ID,
       OptionID,
       OptionName,
       OptionLName
FROM   [dbo].[Base]
       CROSS APPLY (SELECT [Option1ID], [Option1Name], [Option1LName] UNION ALL
                    SELECT [Option2ID], [Option2Name], [Option2LName] UNION ALL
                    SELECT [Option3ID], [Option3Name], [Option3LName])
                     V( OptionID, OptionName, OptionLName)

But I presume the UNION ALL you were trying to avoid was the multiple scans of

SELECT ID,
       [Option1ID],
       [Option1Name],
       [Option1LName]
FROM   [dbo].[Base]
UNION ALL
SELECT ID,
       [Option2ID],
       [Option2Name],
       [Option2LName]
FROM   [dbo].[Base]
UNION ALL
SELECT ID,
       [Option3ID],
       [Option3Name],
       [Option3LName]
FROM   [dbo].[Base]


How much RAM should SQL Server have I have a database which takes up 500gb, I have noticed that my ple is low even though I have 100gb of RAM. I have searched in google to find reasons of that and I found some interesting queries. I have a query which runs on very big table even though it uses clustered index it causes ple to drop. I checked  what is in buffer pool before and after query and moment after query is done and it looks like DB has to free some space for index used in my query and after query old indexes are loaded to the buffer pool. My question is how much RAM memory should DB have, how could I calculate it, should it be amount which will let me keep all used indexes in memory at once? Or maybe I should reduce data kept in tables so indexes will be smaller?

Every time you scan a big index (or a big table, if you prefer) that data has to be brought from disk to memory. When this happens, if the buffer pool is holding data from other objects and does not have enough free space to accomodate the pages you are reading from disk, something has to be discarded.

Low PLE means that this process happens too often, thrashing your buffer pool and overwhelming your I/O subsystem.

Some ways to improve it:

Add more RAM. Easy and not very expensive nowadays. Doesn't really solve the problem, but can be an acceptable short-term solution. How much RAM? Enough to hold the active portion of your databases. Only you can tell how much that is. Not all versions/editions of SQL Server and Windows can use the same amount of RAM, so make sure you're not limited by that.
Purge some data. Are you sure you need all the data you have in
there?
Create smaller indexes. That means including in your indexes the
minimum set of columns that cover your queries. Big scans work
faster on smaller indexes and require less RAM. The tradeoff is more
space used on disk and more operations to perform when updating the
table.
Create filtered indexes. If you are often filtering on a common
non-selective condition (eg: active=true or something similar) a
filtered index might help reducing the size of the indexes. This has
some impact on the application as well (some SET options
restrictions to make it work).
Use compression. Data is compressed both on disk and in memory, so
ROW/PAGE compression can be a way to reduce memory comsumption.
You will need Enterprise Edition and you have to give up some CPU,
but it's often worth it for huge objects.
Use the correct data types: using (n)char over (n)varchar or int when tinyint is enough will waste not only disk space, but also buffer pool space. This includes character and numeric data types: make sure you're using the right type/size/precision/scale for your data columns.


How to represent aggregates in a data warehouse I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries.

Basically, I want the aggregate to group by:

(PizzaType), (Size), (Month), (Year)

I would have num_pizza_types * num_sizes * num_months * num_years aggregate rows.  One row for all combinations of that.  But how does this change my dimensional model (or aggregate schema, I suppose)?  How is this represented in the dimensional model?  Do I remove all columns except month/year from my Date dimension?  Do I remove all columns except Size and PizzaType from my Pizza dimension?

You should not change your date or pizza dimensions. What you need is a fact table that keeps your aggregate data.

Therefore you will have Date dimension with these columns:

DateId -Pk

Day

Month

Quarter

Year

DateName

....

Your Pizza dimension will be like:
PizzaId -Pk

PizzaName

PizzaSize

PizzaType

...

And now your fact table which will be linked to the dimensions above using their IDs:

Create Table PizzaSale (keeps the aggregate data):
Id

PizzaId -Fk to Pizza dimension

SaleMonthId -Fk to Date dimension

Pizza_total_sale -Aggregate value

... Other aggregate values if needed

So as you can see by joining these 3 tables you can get the number of sold pizzas per month. The type can be got from Pizza dimension.


SSAS Tabular: ImpersonationMode that is not supported for processing operations I have a SQL 2016 SP1 SSAS Tabular Instance. I've deployed a model with the following properties

When I try to process the database or a table I get an error "The datasource contains an ImpersonationMode that is not supported for processing operations".

But if I change the impersonation info on the connection properties to use the service account instead of the current user it works fine.

We also don't get this issue if we change the Default Mode to DirectQuery instead of import, but we need to use Import because we need to use DAX username function for row level security.

I am an admin on the SSAS instance and also an admin on the SQL Server instance that is the data source. Why can't I process the SSAS tabular model as my user?

I agree the documentation on this is a bit terse, but the error message on the other hand is very clear. ImpersonateCurrentUser is not supported for models attached to a SSAS instance.

This is mentioned in the documentation of the ImpersonationMode Enumeration:

  ImpersonateCurrentUser: Not supported for tabular model databases attached to an Analysis Services instance.

As well as in the documentation on Impersonation (SSAS Tabular) under the Options header:

  When configuring impersonation, or when editing properties for an
  existing data source connection in Analysis Services, you can specify
  one of the following options:

  ImpersonateWindowsUserAccount
  ImpersonateServiceAccount


Setting DATEFIRST to 6 I would like to set FIRSTDATE to 6 (ie: saturday), i have read that to change it parmanently i could set the default language of the login to one of an existing language from the sys.syslanguages and alter the login of the user like:

USE [master]
GO
ALTER LOGIN [some_user] WITH DEFAULT_LANGUAGE = [Français]
GO

But:

SELECT * FROM sys.syslanguages;

i get many languages, but no one has the DATEFIRST set to 6 (they are all set to 1 or 7).

Question:

Can i add another language based on an existing one, and set de field datefirst to 6 ?

Can i modify an existing syslanguage ?

All what i want, is te set it to 6 parmanently, because i need it to get the right week number.

Thank you for help !

Can i add another language based on an existing one, and set de field datefirst to 6 ?
Can i modify an existing syslanguage ?

You cannot modify sys.language in 2005 and up.

All what i want, is te set it to 6 parmanently, because i need it to get the right week number.

Is there any specific reason to do this at sql server level ? Why cant this be done at the application level ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891 has a method to do it using registry changes, but be CAUTIOUS of doing it without proper testing !!

Edit: Added my comment in answer:

How about using SET DATEFIRST N (where N = 1 through 7 and 7 is default). Ref: msdn.microsoft.com/en-us/library/aa259210(v=sql.80).aspx


Can CDC or Change Tracking be against a read only secondary? We will be deploying SQL Server 2012 Availability Groups with read only secondaries and would like to execute our SSIS extracts against the secondary while utilizing CDC or change tracking (the decision hasn't been made as to which will be used at this time).

I am hoping that the underlying implementations of CDC and CT would allow for their functionality to exist and be consumed on the secondary but have not yet been able to find anything stating this one way or the other.

Does anyone have any experience (and better yet official documentation) that points to the viability of this solution (or not)?

We are leaning toward Change Tracking at this point because we do not have a hard requirement for historical data but I would like to be able to identify any issues/risks for either implementation.

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately.

The link is specified below but here are the relevant parts for my needs.

  Redirecting the Query Load to a Readable Secondary

While in many cases a client application will always want to connect to the current primary replica that is not the only way to leverage AlwaysOn availability groups. If an availability group is configured to support readable secondary replicas, change data can also be gathered from secondary nodes.

When an availability group is configured, the ALLOW_CONNECTIONS attribute associated with the SECONDARY_ROLE is used to specify the type of secondary access supported. If configured as ALL, all connections to the secondary will be allowed, but only those requiring read only access will succeed. If configured as READ_ONLY, it is necessary to specify read only intent when making the connection to the secondary database in order for the connection to succeed.

  Change Tracking Note

Change tracking data must always be obtained from the primary replica. An attempt to access change data from a secondary replica will result in the following error:

Msg 22117, Level 16, State 1, Line1

For databases that are members of a secondary replica, change tracking is not supported. Run change tracking queries on the databases in the primary replica.

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)


Select most recent record per day based on 3 columns I have a table that looks like this:
CREATE TABLE tbl_AppUsage(
ID int IDENTITY(1,1) NOT NULL,
UserName nchar(250) NULL,
UPN nchar(250) NULL,
AppDisplayName nchar(250) NULL,
AppId nchar(250) NULL,
CreatedDateTime nchar(250) NULL);

INSERT INTO tbl_AppUsage (UserName, UPN, AppDisplayName, AppId, CreatedDateTime)
VALUES
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:39:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:51:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 09:39:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 09:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 09:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 10:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 10:51:14')
;

I want to create a query that returns the newest record per day per user

I tried with the following:
SELECT
       ID
      ,UserName
      ,UPN
      ,AppDisplayName
      ,AppId
      ,CreatedDateTime
  FROM tbl_AppUsage T1
  WHERE CreatedDateTime=(
  SELECT MAX(CreatedDateTime)
  FROM tbl_AppUsage
  WHERE UserName = T1.UserName AND AppId = T1.AppId)
  GROUP BY UserName

No success and I am a bit stuck, to be honest.
The table that I will query has 200 000 plus records and I am using MS SQL 2017 as DBMS.
I am unsure whats the best method to get the data with performance in mind.
Note: I know that the columns (like CreatedDateTme) can be other types but in this case, it has to be a nchar.

One way to accomplish this, is to use ROW_NUMBER
because you need on record  per day per user, I added in the PARTITION: a.UserName, CAST(CreatedDateTime as DATE)
 and for the newest record , I added ORDER BY CAST(CreatedDateTime as DATETIME) DESC
SELECT
    src.ID ,
    src.UserName,
    src.UPN,
    src.AppDisplayName,
    src.AppId,
    src.CreatedDateTime
FROM
(
    SELECT
        ID ,
        UserName,
        UPN,
        AppDisplayName,
        AppId,
        CreatedDateTime,
        ROW_NUMBER()OVER(PARTITION BY a.UserName, CAST(CreatedDateTime as DATE) ORDER BY CAST(CreatedDateTime as DATETIME) DESC)as rn
    FROM dbo.tbl_AppUsage as a
)as src
WHERE
    src.rn = 1

output:
ID          UserName    UPN              AppDisplayName  AppId         CreatedDateTime
----------- ----------- ---------------- --------------- ------------- -------------------
3           aaa         aaa@mail.com     Adobe DC        1234fdr234    07/10/2020 09:39:14
4           aaa         aaa@mail.com     Adobe DC        1234fdr234    07/11/2020 09:39:14
6           bbb         bbb@mail.com     Adobe DC        1234fdr234    07/10/2020 09:39:14
8           bbb         bbb@mail.com     Adobe DC        1234fdr234    07/11/2020 10:51:14

Base on your query, same output
SELECT
       ID
      ,UserName
      ,UPN
      ,AppDisplayName
      ,AppId
      ,CreatedDateTime
  FROM tbl_AppUsage as T1
  WHERE CreatedDateTime=(
      SELECT MAX(CreatedDateTime)
      FROM tbl_AppUsage as T2
      WHERE T2.UserName = T1.UserName
        --AND AppId = T1.AppId
        AND CAST(T1.CreatedDateTime AS DATE) =CAST(T2.CreatedDateTime AS DATE)
    )
  --GROUP BY UserName


Is there a way to list all tables and when they were last accessed? I'm wondering if there's a way in SQL Server to make a list of all tables and when they were last accessed (select / update / insert or delete) either by a user, view or stored procedure?

Jon gives you some links to SQL Server audit information in the comments. That is a definite way to see who is doing what and when on a server. Depending on what you are looking for that may be a good enough answer. Auditing isn't free, though. Capturing some of the data required can be expensive to trak and take some time to track.

One approach here - depending on what you are looking to do with this information is to look at the DMV - sys.dm.db_index_usage_stats this DMV shows you various statistics about access (insert, update, delete, scan, seek and lookups) for your indexes. You can join this DMV to tables that contain information about your tables or views like sys.tables or sys.indexes to see the details.

There are some caveats about this approach (data gets reset when SQL is restarted, there is a bug in SQL 2012 where the stats are apparently reset after an index rebuild, and other caveats described by Joe Scan in a couple posts) You can see an example of querying this data in this post from 2007 written for SQL 2005 but still most applies today.


Multiple Schemas for increasing MySQL (5.7) Performance Lets start with my question first:

Does having multiple schemas (1 main and 1 daily - having 5-6 tables with same structure as main, but only working for the day and will be synced back with the main one every night) in MySQL (5.7) server can impact its performance ?

More background:

The problem I'm facing with my database is that the application was working fine under certain load (still does but LA goes up to 8), but past few days I'm fighting over CPU usage ... I dig the problem and the reason for that is that there are locks going on and my workers are stuck trying to insert/update new data.

To go even deeper I'm doing at least 1K inserts per minute, up to 2K updates per minute (some are on the previously inserted records), and more than 4-5K selects per minute.

And I'm not talking for simple integer inserts ... lots of strings, timestamps, integers and 6 indexes (total column number in the table is 32)

My server is fairly powerful (huh, fairly).

16GB RAM
1TB SSD
8 Core, Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz
Software: MySQL 5.7

Part of my my.cnf

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 30G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8

and still I'm getting LA of up to 8.00

The problem with all this is because all my workers start every minute and they sort of confront each other.

What I came with is following scenario:
- create separate schema with my 'daily' working tables (mostly the SELECTS) and do the inserts/updates in my original schema.

Will that decrease the load on the server?

And one off-topic question: Is LA of up to 8.00 healthy for the server ?

Thanks

P.S. If you need any further info, just let me know I'll share it.

PROBLEM #1
You innodb_thread_concurrency is too small. SUGGESTION: It should either be 0 or 64. Please read my post from Aug 14, 2017 (MariaDB 10.1.22 to use more RAM rather than CPU) on why.
PROBLEM #2
I don't see innodb_read_io_threads and innodb_write_io_threads mentioned. By default, both are 4. SUGGESTION: I would double both to 8 (Percona Server's default). Please see my post from Jun 20, 2013(MySQL using too much CPU).
PROBLEM #3
Why is your innodb_log_file_size 30G ? InnoDB Redo Logs are written sequentially. IMHO, writing sequential files on an SSD is not that great (See my post from Feb 06, 2014 : MySQL on SSD - what are the disadvantages? and Sep 25, 2014 : What is the best storing device for DB transaction log?). SUGGESTION #1: You could configure InnoDB to have the Redo Logs on a separate disk. SUGGESTION #2: Please make innodb_log_file_size 1G for now. Then, go figure out the proper size by reading my post from Jan 17, 2013 (MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?) where I point to two posts from Percona's mysqlperformanceblog:

Jun 03, 2006 : Choosing proper innodb_log_file_size
Nov 21, 2008 : How to calculate a good InnoDB log file size

GIVE IT A TRY !!!
As for the one off-topic: Server with 16GB RAM and LA of 8.00 ??? Of course, not !!! Try these suggestions and see if it really helps.


Master Key vs Password Certificate Encryption in SQL Server We can encrypt certificates in SQL both by the database master key, and the other option is by password. I want to know the advantages and disadvantages of DMK encryption. Does password usage make the certificate more secure?

thanks

We can encrypt certificates in SQL both by the database master key, and the other option is by password.

You can do both at the same time, which is preferable.

  I want to know the advantages and disadvantages of DMK encryption.

The big two are:

If you forget the password or can't use the SMK (service master key), you can still open the Certificate (think AG, DBM, etc.)
You can use Transparent Decryption which removes the need to specifically open by password


How many rows can SQL Server process in a single INSERT statement into a table? To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them:

With NewOrders
As
(
    Select OrderID From Orders
    Except
    Select OrderID From FactOrders
)
Insert Into FactOrders(OrderID, OrderDate, CustomerId)
    Select OrderID, OrderDate, CustomerId From Orders
    Where OrderID in (Select OrderID from NewOrders);

Say the query is run for the first time and Orders contains 400 million rows or more:

Can SQL Server handle this number of rows in one single INSERT statement?

If not, how should I proceed? Should I limit the number of rows fetched in the INSERT statement? How many rows can the engine handle in a single INSERT statement?

According to the documentation the only limitation on the number of rows stored per table is as below

  Limited only by available resources

What local resources does this potentially include?

Memory
Space in the database data files to physically write the inserted data to
Space in the database log files
Space in TempDB (Data and log)

As for how much a single SELECT statement can extract, I cannot find any documentation pertaining to this directly, but I would assume it is safe to take this to be exactly the same as the above.