CPU Schedulers Offline I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me.

  Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.

Fair enough, I run the base query

SELECT
    DOS.is_online
,   DOS.status
,   DOS.*
FROM
    sys.dm_os_schedulers AS DOS
ORDER BY
    1;

That reports back that I have 8 set to VISIBLE OFFLINE, 43 to online. To my knowledge, no one at this client would have intentionally set any CPU affinity.

I decided to see if I could unbugger it. When I look at the properties window, I see 40 processors available and none of them set to have affinity.

Why there's 40 showing yet 43 entries in the dm_os_schedulers where is_online is true seems curious as well. The cpu_id of the 8 offline are 32 to 39.

sys.configurations seems to concur with affinity not being explicitly on

name                 value  value_in_use  description
affinity I/O mask    0      0             affinity I/O mask
affinity mask        0      0             affinity mask
affinity64 I/O mask  0      0             affinity64 I/O mask
affinity64 mask      0      0             affinity64 mask

This isn't an Enterprise Edition so the CAL grandfather thing shouldn't be factor here but I can ask that question tomorrow if need be

ProductVersion  ProductLevel  ProductUpdateLevel  Edition
11.0.5058.0     SP2           NULL                Standard Edition (64-bit)

Running Glenn Berry's diagnostic queries, this stuff might be relevant

System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R720'.
Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz

So, what gives? Am I virtualized and don't know it? Is there some other place I should be looking to determine why SQL Server can't use some of the CPUs?

Reference articles

A list of articles I read but clearly didn't comprehend well enough to answer my own question

BOL sys.dm_os_schedulers
Glenn Berry's DMV a day
SQLServerGeeks DMV a day

What you see makes perfect sense given the information available.

SQL Server 2012 Standard Edition is limited to the lesser of 4 sockets or 16 physical cores.

Since you have 40 cores (likely 2 socket 10 core + HT) 16 physical cores is going to be 32 logical cores (because of HT). If you have 40 total, I would EXPECT 8 to be VISIBLE OFFLINE due to licensing.

Features Supported by the Editions of SQL Server 2012 - Cross-Box Scale Limits

Edit: Additional Information

Depending on the OS involved the schedules may be bound to CPUs that are also making an unbalanced NUMA memory configuration for your system. While it is highly unlikely that this will cause you any issues (since it wasn't noticed that all the CPUs were being used in the first place) it could have a much higher impact on a highly used system where the foreign memory access can really add up.


Split Brain Administrators I have been appointed the DBA for our organization. I have been asked by senior officials to allow the two divisions of IT to have full control of their databases. They have asked for the following layout:

      Admins
SubAdmin  SubAdmin

These SubAdmins should be capable of performing all the functions possible on their own database, but should not be able to perform any functions on any other database.

That does not include server-level things like killing a session, building a logon trigger, changing Resource Governor settings, etc, but they should be able to create databases.

I have created a server role for the SubAdmin group which grants CREATE ANY DATABASE. This works well, except that when the SubAdmin attempts to change the owner of the newly created database to sa. They cannot because they cannot impersonate the sa account.

Would it be easier just to write some powershell script that changes the db owner to sa each night?

I use the sa account in this example just as a demonstration. We want the database owner to be a generic, low-privilege account. Therefore when the SubAdmin creates a database they:

Create the db
Grant their admin group db_owner rights
then should perform ALTER AUTHENTICATION to change the database owner to the generic, low-privilege account

The databases are all on the same server and same instance.

I would use a server level trigger on CREATE DATABASE.

Something like this:

CREATE TRIGGER [CreateDatabase_OwnerMapper]
    ON ALL SERVER
    WITH EXECUTE AS 'sa'
    FOR CREATE_DATABASE
    AS
    BEGIN

    SET NOCOUNT ON;

    DECLARE @DatabaseName NVARCHAR(128) =
    (SELECT CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVARCHAR(128)));

    DECLARE @SQL NVARCHAR(MAX) = '';
    SET @SQL = 'ALTER AUTHORIZATION ON DATABASE::' + @DatabaseName + ' TO sa';

    EXEC @SQL;

END

This will fire off a trigger to automatically change the owner whenever the CREATE DATABASE command is issued.

You could grant the db_owner yourself with the trigger potentially. I do this on our debt environments.


Mix of Cyclic and Chained MySQL replication - What do you do with "log-slave-updates"..? I have the following MySQL replication topology set up:

Master1 (M1) replicates to Master2 (M2)
Master2 (M2) replicates to Master1 (M1)
Master1 (M1) replicates to Slave1 (S1)

Pictorial View

+<------------+
|             ^
|             |
V             |
M1----------> M2
|
+-----------> S1

All users connect to their masters for selects and updates.

The theory in the cyclic replication is that if I need to do any maintenance on the Master, I can point everyone temporarily at Slave 1 whilst I do the work, and then when Master is back online and the changes have been replicated from M2, I can seamlessly swap everyone back to working on the Master.

S1 is an offsite server in case of a local disaster, so does not need the cyclic replication set up.

The problem I currently have is that any changes made on M2 are not replicating to S1, so in the scenario above where I do need to take the master offline, any changes which get made on M2 in the meantime, will not make it to the offsite copy on S1.

I understand I can set the flag "log-slave-updates" on Master which would then cause changes made on M2 and pushed to Master as part of the replication, to be passed to S1 but I am nervous, that with the cyclic replication in place between M1 and M2, that this will also, in some way try and push the same changes back to M2 again and end up in an endless loop of replication.

I am guessing that MySQL is clever enough to handle this, but can someone help guide me towards anything I may need to consider in this situation before I end up in an infinite loop of replication!!

Many thanks in advance!

Well, you are absolutely right to be concerned about log-slave-updates causing an issue with your Master-Master setup, though it won't necessarily be an infinite loop changes. I suspect if you are writing to both masters, that you will constantly be having to set skip_slave_counter and restart the slave thread.... In a word, not ideal.

I would look over this post, specifically the short section on data integrity:

  The safest solution is to simply never write data to both masters.

So, if both master's are up, but your clients only ever write to one master at a time (Master 1 by default), then you shouldn't have an issue.

*shouldn't doesn't mean you won't, though.


What could be the reason for disallowing a sub query in the values clause? For example

SQL> create table dates(d date);

Table created.

SQL> insert into dates select sysdate from dual;

1 row created.

SQL> select * from dates;

D
---------
28-MAY-11

SQL> insert into dates (d) values (select sysdate fom dual);
insert into dates (d) values (select sysdate fom dual)
                              *
ERROR at line 1:
ORA-00936: missing expression

SQL>

If you want to use a SELECT statement where only a single value is allowed you need to put that SELECT statement into brackets:

insert into dates (d)
values
( (select sysdate from dual) )

That could be extended for multiple columns:

insert into dates
(
  id,
  d,
  other_column
)
values
(
  some_sequence.nextval,
  (select sysdate from dual),
  (select max(some_col) from other_table)
)

You just need to make sure that the SELECT returns exactly one row and exactly one column


Build a single JSON value from two jsonb columns in two different tables I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id.

I am trying to build one aggregated JSON value from these jsonb columns.

Lets say table A has four rows and each has jsonb data like:

[
  {
    "student_id: 1,
    "others element from table A" ...
    ...
  },
  {
    "student_id: 2,
    "others element from table A" ...
    ...
  }
...
]

And table B has holds jsonb values of the form:

[
  {
    "student_id": 1,
    "others element from table B" ...
  },
  {
    "student_id": 2,
    "others element from table B" ...
  }
...
]

There is only one row from table B at a time that is to be mapped to multiple rows from table A, all containing different "other_elements" but with the same "student_id" value for each object.

My expect result is of the form:

[
  {
    "student_id": 1,
    "others elements from table A" ...
    "others elements from table B" ...
  },
  {
    "student_id": 2,
    "others elements from table A" ...
    "others elements from table B" ...
  }
]

Here is a fiddle with sample data demonstrating my case.

This should do it:

SELECT jsonb_agg(student) AS students
FROM (
   SELECT jsonb_object_agg((kv).key, (kv).value) AS student
   FROM (
      SELECT ass->>'student_id' AS student_id, "char" 'a' AS tbl, id, jsonb_each(ass) AS kv
      FROM (SELECT jsonb_array_elements(assessment       ) AS ass, id FROM assessment) a

      UNION ALL
      SELECT ass->>'student_id'              ,        'b'       , id, jsonb_each(ass)
      FROM (SELECT jsonb_array_elements(others_assessment) AS ass, id FROM result    ) r
      ORDER BY student_id, tbl, id
      ) sub1
   GROUP  BY student_id
-- ORDER  BY student_id  -- to be explicit
   ) sub2
;

db<>fiddle here

In the inner subquery sub1, for each involved table, unnest the top-level JSON array into its element objects with jsonb_array_elements(), and then expand these objects into into a set of key/value pairs. Then UNION ALL all of them together and (optionally) order rows, as the order bears significance in the next step.
Note how jsonb_each() is called once, returning two fields, which we carry over as one composite type kv - with well-known fields key and value  defined by the function.

In the next subquery sub2, aggregate per student_id to unite all attributes with jsonb_object_agg(). Values from later rows overrule earlier rows in case of key conflicts.
Fields of the composite type are accessed with (kv).key, (kv).value. This way, the function is only executed once (not once per field). Alternatively use LATERAL subqueries ... See:

How to avoid multiple function evals with the (func()).* syntax in an SQL query?

In the outer SELECT, aggregate all students into a jsonb array with jsonb_agg(). Voilá.
(ORDER BY student_id is carried over from the inner subselect, but add it if you want to be explicit.


How do I add/remove tables to a Windows Azure database? I have a Windows Azure database which I wish to add/remove tables to, and yesterday I witnessed a colleague do this online, on what looked like a web based SQL management studio, but I can't find it anywhere.

I can see a Manage button in the toolbar at the bottom of the page, but all this seems to do is refresh the page. How do I reach the web based management console I saw my colleague use yesterday?

I also tried to connect to the database using Visual Studio's server explorer, but when I try to add a table, I get this helpful error:

The Manage button is correct. It should bring up a page in a new window/tab that prompts for the user name and password to connect to the database. If that isn't working, there's something wrong with your web browser configuration (does it allow popups from the Azure site?).

Alternatively, the database server can be set up to allow external connections by managing the firewall rules. The interface makes it very easy to poke a hole just for your client computer. You can then connect to the database directly using Management Studio on your local machine. (Don't forget to delete the firewall rule when you're finished.)


Will a row update check all child tables which have foreign key relationships in InnoDB? I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table.

Foreign key relations are On-Delete Cascade and On-Update No-Action.

My doubt is when any update happens to the master table, will all child tables get checked and then the update occurs, or update will independently happen on master table.

I want to know how update/delete will happen when there are foreign key relations to child tables.

MySQL version is 5.0.24
OS is Windows

I wanted to know, when any update happens, will the update query cross verify all child tables and then update the master table, so that will the update query will take time to update master table compared to update on normal table which is not having any foreign key relations

ON DELETE CASCADE: When you delete rows in the master table referenced by other tables, all those child tables will also delete their rows that reference the deleted master data.
ON UPDATE NO ACTION: If you try to update a field in the master table referenced by others, MySQL will prevent you from doing that. NO ACTION and RESTRICT are synonyms in MySQL.

So to answer your updated question, MySQL will prevent you from updating a field in the master table that is referenced by any child tables because you specified NO ACTION.  If you are updating any other field in the master table it will take the same amount of time as an update on a table without foreign keys.


Find IDs from a list that don't exist in a table Say I have the following schema and data:

create table images(
  id int not null
);

insert into images values(1), (2), (3), (4), (6), (8);

I want to perform a query like:

select id from images where id not exists in(4, 5, 6);

But this doesn't work. The case above should return 5, since it doesn't exist in the table records.

You can use an outer join against a values list (similar to Martin's answer mentioned above):

select t.id
from (
  values (4),(5),(6)
) as t(id)
  left join images i on i.id = t.id
where i.id is null;

or a not exists together with the row constructor:

select *
from (
   values (4),(5),(6)
) as v(id)
where not exists (select *
                  from images i
                  where i.id = v.id);

If you like you can also put the values clause into a CTE to make the final query easier to read:

with v (id) as (
 values (4),(5),(6)
)
select v.id
from v
  left join images i on i.id = v.id
where i.id is null;


How to write a script to push the server time to out? Background:

I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data into the back-end that their computer time is the same as the SQL-server time to ensure that record locking is being effectively used and the data is being timestamped properly.

Extra gibberish:

I only had a major concern for this when I saw a user in my company with his computer time set in the future!!! He was exactly 12 years ahead of us but had no idea! Hence this I have been searching for a solution to keep everyone on the same page.

Question:

Is it possible to write a script that will push the SQL-server time out on to networked client computers?

Unfortunately, you're trying to solve a problem with an inherently broken design: relying on a user-editable client-side variable to determine object version is never going to work perfectly.

Forcing time synchronization on the domain may help, but clients can still be out-of-sync, either because of clock drift, or because the user is a local admin and therefore has permissions to change the clock manually at any time, to any time.

Normally a server-side value such as an ever-increasing integer row version number is used for concurrency control instead of a client-side-generated value.

Also, if you're recording auditing information, as much of it as possible (particularly time values) should be generated by the server for both security and synchronization reasons.


Is it possible to restore single PostgreSQL database to PITR? I have PostgreSQL instance with n databases on it.
Is it possible to restore just a single database using PITR ?
# restore
tar xvfz /var/lib/postgresql/db_file_backup.tar.gz -C /var/lib/postgresql/10/main/

# add recovery.conf
nano /var/lib/postgresql/10/main/recovery.conf

  restore_command = 'cp /var/lib/postgresql/pg_log_archive/%f %p'
  recovery_target_time = '2018-02-22 15:20:00 EST'

# start DB
sudo systemctl start postgresql@10-main

I see that pg_restore contains dbname parameter. But it's logical. I need to restore to specific Point-In-Time.
pg_restore --help pg_restore restores a PostgreSQL database from an archive created by pg_dump. Usage:  pg_restore [OPTION]... [FILE] General options:  -d, --dbname=NAME        connect to database name

You cannot have that.

PITR requires a file system level backup and recovery, and both encompass the whole cluster. I am not saying it is theoretically impossible, but no such functionality exists.


Postgres CTE query for a single row not optimized From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view.

Since it's a view, I pretend to calculate the sum for every single row in the table, and then I was hoping that the optimizer would realize when I am only asking for a single row and would optimize the query. However my query plan is massive, and is calculating 17 billion rows at its innermost point, where I think there should be at most a thousand.

Here is the query:

CREATE OR REPLACE VIEW weighted_stats AS
WITH
    clf AS (SELECT * FROM classifiers order by time_trained desc limit 1),
    weights AS (SELECT kv.key, kv.value from clf, each(clf.weights) AS kv),
    kvs AS (
        SELECT stats.player_id, kv.key, kv.value FROM
        stats, each(stats.hstore_column) AS kv),
SELECT
    stats.player_id,
    SUM(kvs.value :: numeric * weights.value :: numeric) AS stats
FROM
    kvs JOIN weights USING (key)
GROUP BY kvs.player_id;

This is the query plan:

explain analyze select * from weighted_stats where player_id=76561197960269296

GroupAggregate  (cost=53645.35..299471.72 rows=1 width=72) (actual time=1014.016..1014.016 rows=0 loops=1)
   Group Key: kvs.id
   CTE clf
     ->  Limit  (cost=20.65..20.65 rows=1 width=84) (actual time=0.017..0.018 rows=1 loops=1)
           ->  Sort  (cost=20.65..22.43 rows=710 width=84) (actual time=0.014..0.014 rows=1 loops=1)
                 Sort Key: classifiers.time_trained
                 Sort Method: quicksort  Memory: 25kB
                 ->  Seq Scan on classifiers  (cost=0.00..17.10 rows=710 width=84) (actual time=0.003..0.005 rows=1 loops=1)
   CTE kvs
     ->  Seq Scan on stats  (cost=0.00..53572.18 rows=10318000 width=722) (actual time=0.037..530.337 rows=336036 loops=1)
   CTE weights
     ->  Nested Loop  (cost=0.00..20.02 rows=1000 width=64) (actual time=0.036..0.046 rows=2 loops=1)
           ->  CTE Scan on clf  (cost=0.00..0.02 rows=1 width=32) (actual time=0.020..0.023 rows=1 loops=1)
           ->  Function Scan on each kv  (cost=0.00..10.00 rows=1000 width=64) (actual time=0.011..0.013 rows=2 loops=1)
   ->  Hash Join  (cost=32.50..241344.73 rows=257950 width=72) (actual time=1014.012..1014.012 rows=0 loops=1)
         Hash Cond: (kvs.key = weights.key)
         ->  CTE Scan on kvs  (cost=0.00..232155.00 rows=51590 width=72) (actual time=0.044..1013.877 rows=62 loops=1)
               Filter: (id = 76561197960269296::bigint)
               Rows Removed by Filter: 335974
         ->  Hash  (cost=20.00..20.00 rows=1000 width=64) (actual time=0.060..0.060 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  CTE Scan on weights  (cost=0.00..20.00 rows=1000 width=64) (actual time=0.040..0.054 rows=2 loops=1)
 Planning time: 0.286 ms
 Execution time: 1017.671 ms

This is still a lot slower than what I'd expect. The optimization is working partially by filtering before the join, instead of before the group by, but it seems like the kvs CTE (which should itself be filtered) is still being calculated for everyone.

Common Table Expressions are treated as an “optimization fence” by PostgreSQL: It will never push down predicates from the main query into the CTE nor collapse any joins across the CTE boundary. Instead, it will generally evaluate the whole CTE as it is, materializing the result; the main query will then access the temporary table generated from the CTE.

So yes, your query would likely benefit from converting the CTE into a subquery.

Note that an actual view (created by CREATE VIEW) does not act as an optimization fence. The view’s definition will be included in the query using it, then optimized as usual. For CTEs, there has been discussion about making the optimization fence behaviour optional so that they could be used “just” to make queries more readable. However, as of version 9.5, this hasn’t been implemented yet.


Running MySQL in virtualbox with data dir on shared folder sometimes results in data corruption I'm trying to move our development environment onto a vagrant box (virtual machine using Oracle virtualbox). Currently I'm stuck on one problem:

The environment needs a MySQL server and some databases. I've got all the scripts set up that install MySQL, create and populate the databases, etc. That works.

Since the developers are likely to set up further data of their own, my idea was to put the MySQL data folder in a shared virtualbox folder. This way when the vagrant machine is destroyed/recreated, the data folder remains and can be reused without loss of data.

However sometimes (but not always, I don't know what triggers it) when I simply suspend and then resume the virtual machine, MySQL starts complaining that a lot of its tables (but not all) are corrupted. Fixing the tables results in all data in them being lost. Obviously this is unacceptable.

Does anyone have any idea why this happens and how to prevent it?

A few possible clues or red herrings - since I read that virtualbox's shared folders are slow, I've increased the InnoDB buffer pool size to 1GB, so that it has a lot of cache space. We do use InnoDB and MyISAM in proportions of about 50/50. I haven't yet checked if the failed tables all belong to the same engine - I'll pay attention to it when it next crashes.

Update

Ha, I managed to catch it red handed! It's actually... suspense... BOTH InnoDB and MyISAM.

Selecting from a MyISAM table gives the error

  Table 'xxx' is marked as crashed and should be repaired

Trying to run mysqlcheck -A -a (analyze all tables) actually CRASHES MySQL. The log files tell the story (edited for clarity):

[Warning] InnoDB: Retry attempts for writing partial data failed.
[ERROR] InnoDB: Write to file ./ib_logfile0failed at offset 29885952, 1024 bytes should have been written, only 0 were written. Operating system error number 71. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
[ERROR] InnoDB: Error number 71 means 'Protocol error'
[ERROR] mysqld got signal 6
Query (0x7fbc12501b50): ANALYZE TABLE `yyy`

And then, in a moment, systemctl restarts the service and all runs perfectly again.

My educated guess is that suspending down the virtual machine releases the locks that virtualbox was holding on opened files, and when virtualbox starts up again, it doesn't re-acquire them. So from MySQL's point of view, it's keeping a file opened, but virtualbox has lost that grip, so when MySQL tries to access the file again, it gets an error.

When the production box crashes, and you find that the MyISAM tables are corrupted there, and you find how how many hours (or days) it takes to REPAIR them, you may rethink whether moving to InnoDB is justified.

At some point your 'suspend' + corruption may make the MyISAM tables so messed up that dev is impacted.

OK, lecture over; back to your questions.

How big is the VM?  If you are 50/50 InnoDB/MyISAM on that box, then use these settings if the VM is at least 4GB:

key_buffer_size = 10% of the VM size
innodb_buffer_pool_size = 35%

If less than 4GB, lower those percentages some.  The goal is to avoid ever swapping.


SQL Server error 242 with ANSI datetime I'm trying to debug a MS SQL Server error 242:

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error is originated by this statement:

CREATE TABLE db.schema.test (
    Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

This is my server version:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)
    May 15 2019 19:14:30
    Copyright (C) 2017 Microsoft Corporation
    Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

And the language @@LANGUAGE is Italiano.

The same statements works on

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
   Jul 27 2018 09:40:27
   Copyright (C) 2017 Microsoft Corporation
   Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

With language: us_english

Without without making explicit convert style it works

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'})));

Convert style 102 is ANSI yyyy.mm.dd, shouldn't be recognized by both English and Italian SQL Server?

VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime.

(ignoring the redundant convert to datetime)

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime, SET LANGUAGE, and Write International Transact-SQL Statements in the documentation.

  Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types.

Also SQL Server DateTime Best Practices by Aaron Bertrand.


Is my shopping list database schema good enough? I'm wondering if my schema is correct? It's intended to hold shopping list items for each user, a user belongs to a household. Items will be added to the household.

The stocklist will to hold the quantity of an iID (item ID) and that iID is belongs to a hhID .

So at the moment

stocklist.hhID references household.hhID
household.adminID references users.uID
items.hhID references household.hhID
stocklist.iID references items.iID

I'm wondering if these relationships are viable? Even more importantly what do you think of the schema?

Posted from my unused account before.

Your schema has a circular reference and redundant columns/foreign keys.

Consider the following normalization of your data structure:

Put itens as a reference table, no Foreign Keys.
Stocklist references Items
Users have a FK to Household, no use on including the reverse relation because you can always find the User records for a given Household with a table Inner Join.


Associating large numbers of Items with large numbers of Users in Mongo Say I have tables Items and Users, which are both in the order of millions. Users can be associated with rows in Items via a 1 to many relationship. This is expected to be on average, a few thousand. What is the best way to go about this? I can only think of 2 ways to do it:

Create a separate table linking the 2 up. This could end up having billions of rows, so I have space concerns here.
Embed the item id's in each User, but then i'd be often pulling out more than I need, and inserting/deleting ID's would also be inefficient.

It seems like it would be a common scenario, so am wondering what kind of solutions are out there.

Consider two collections (or entities) users and items. And, they are in a one-to-many relationship - users is the 'one' side and the items is the 'many' side.

items: [ 1.. millions ]
  itemId: <id value>,
  name: <some name>,
  description: <...>,
  other_fields:  <...>

users: [ 1.. millions ]
  userId: <id value>,
  name: <some name>,
  email: <some value>,
  items: [ 1.. 10000 ]

  Embed the item id's in each User, but then i'd be often pulling out
  more than I need, and inserting/deleting ID's would also be
  inefficient.

This is a often used approach - embedding the items ids within the users document. This is done by storing the items ids within the users document as an array. For example:

items: [ itemId-1, itemId-2, ..., itemId-5000 ]

With this approach:

You can retrieve all items associated with a user.
For more details of specific item, lookup the items collection (this can be an aggregation lookup operation which queries both collections as a "join").
All the related information for a user is within one document.
For efficient querying, create indexes on the most used query filter fields for the users (e.g., userId, name) and the items array field.
Indexes on array fields are called as Multikey Indexes.

Another Approach:

You can also consider storing more information (as sub or embedded documents) about the items in the items array. For example,

items: [ { itemId: <id-1>, itemName: <some name> }, { itemId: <id-2>, itemName: <some name 2> }, ... ]

In this use case, the itemName is often used when you query a user and related items. This way you can avoid accessing the items collection for the item's name. This approach works when the field like itemName does not change and mostly stable (fields like item price and quantity can change often, and description might not change often).

  ... but then i'd be often pulling out more than I need, and
  inserting/deleting ID's would also be inefficient.

When you query for a user (users collection), you have the option to restrict the fields (user related and also items array) to get as result of a query. MongoDB's projection allows this restricting the fields to retrieve within a single document. This also applies to the items array field - only specific item(s), specific number of items, specific fields for an item, etc.

Regarding the inserting / deleting / updating operations - MongoDB has methods to update a single or multiple fields (including array fields) for a document as atomic operation.

There are specific operators (referred as array operators) to work with arrays (with scalar or composite values) - for inserting, updating, deleting and querying (CRUD operations) on the array fields. Creating indexes on array fields will make these operations efficient.


Database VIEW with a dynamically changing filter I am trying to create a view based on a specific case. Following is my table:

Table Name: Calls

CustomerName | CallID | Timestamp | Quality | PacketLoss | Jitter

The goal is to create a view that can do the following:

SELECT * FROM [VIEW NAME] WHERE TIMESTAMP BETWEEN {Start Time} AND {End Time}
At this point the database need to go through the table and find top 5 CustomerNames with the most amount of rows where the Quality was marked as "Bad".
The database will use the return as a filter and send me back all the rows that had one of the CustomerName's listed in top 5.

I tried this:

CREATE VIEW "Calls_View"
AS (
SELECT *
FROM "Calls"
WHERE "CustomerName" IN (SELECT "CustomerName"
                         FROM "Calls"
                         WHERE "Quality"='Bad'
                         GROUP BY "CustomerName"
                         ORDER BY COUNT(*) DESC LIMIT 5)
  AND "Quality"='Bad');

Is my logic correct?

You can't do what you want with a view.  You have to use a function.  Note that I didn't retain all your double quoting of identifiers, so you will have to put them back if you insist.

CREATE OR REPLACE FUNCTION public.calls_view(timestamp with time zone, timestamp with time zone)
 RETURNS SETOF calls
 LANGUAGE sql
AS $function$
SELECT *
FROM Calls
WHERE CustomerName IN (SELECT CustomerName
                         FROM Calls
                         WHERE Quality='Bad' and timestamp between $1 and $2
                         GROUP BY CustomerName
                         ORDER BY COUNT(*) DESC LIMIT 5)
  AND Quality='Bad' and timestamp between $1 and $2
$function$

You can use it kind of like a view, but you must supply your parameters in parentheses, not a WHERE clause:

select * from calls_view(now() - interval '1 day', now());


POSTGRES Compare a json arrays against another array I have a postgres jsonb column called items having

 [{"id":"","name":""},{"id":"","name":""}]

I need to compare this whole column with the data

 [{"id":"","name":""},{"id":"","name":""}]

If you mean an exact string match then you can do:

SELECT * FROM table where items::text = '[{"id":"","name":""},{"id":"","name":""}]'

Note that JSONB does not necessarily keep the order in the objects the same so it might not work all the time because the order of items might change on the output.

You can lookup the Postgres docs for more advanced JSON operations where you can actually look inside the values.


Does it make sense to define a new "Data type"? Is there a way to do so? I'm basically new to SQL, and I was making my own project when this question came to my mind.
I'm making a database that has an entity where an attribute would be a "number" and a "year".
In many programming languages one would create a new object or data type that takes both the number and the year. (say a tuple with INT, YEAR)
I wanted to know if this makes sense in SQL or I should just go with an INT and YEAR attribute.
To put more details into the project, I'm creating a DB for my mom who runs a business that goes with "campaings" (the INT value) directly associated with the "Year", so one would put the campaign 09 for year 2020, and so on.
I wanted to create a new Data Type for the attribute so then I don't have multiple "campaign 09" with different years.
I hope my question is clear, thanks!
EDIT: Read the comments for the answer.

This isn't something that requires a custom data type to solve.
If the requirement is Campaign 09 can only be associated with one year, then Year is simply an attribute for Campaign (although I'd argue perhaps StartDt or something similar, from which year can be derived might be more appropriate).
To illustrate:
CREATE TABLE Campaign
(
  CampaignNbr  INT   NOT NULL
 ,StartDt      DATE  NOT NULL
 /* Everything else */
 ,CONSTRAINT PK_Campaign PRIMARY KEY (CampaignNbr)
)


sqlsrv_connect: use Windows authentication? I have a PHP server running under IIS on serverIIS1, and a MS SQL database on serverDB4. I created some PHP code that runs on the serverIIS1 server:

$serverName = "serverDB4\serverDB4";
$connectionInfo = array( "Database"=>"DATABASE_NAME", "UID"=>"DOMAIN.ORG\\databaseadm", "PWD"=>"passwordishere");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.
";
}else{
     echo "Connection could not be established.
";
     die( print_r( sqlsrv_errors(), true));
}

I verified all this info is correct (the login is a Windows account with proper access, so it's via Windows authentication), but I'm getting this error:

  Login failed for user 'DOMAIN.ORG\databaseadm'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN.ORG\databaseadm'. )

Why is the login failing? I know the info is right, as I can connect manually remotely via SQL Server Management Studio with those details.

Is it because I'm using Windows authentication? If that does not allow plaintext auth, how can I sqlsrv_connect with a Windows password?

As stated in the documentation you do not pass the UID and PWD to utilize Windows Authentication. You can see an example here.

From remarks within documentation link:

  If values for the UID and PWD keys are not specified in the optional $connectionInfo parameter, the connection will be attempted using Windows Authentication. For more information about connecting to the server, see How to: Connect Using Windows Authentication and How to: Connect Using SQL Server Authentication.


Trigger: move deleted rows to archive table I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.

I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will be stored automatically. Since restrictions has a serial id, there will be no duplicates.

How do I write such a trigger in PostgreSQL?

You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted.

CREATE TRIGGER moveDeleted
BEFORE DELETE ON restrictions
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
       INSERT INTO restrictions_deleted VALUES(OLD.column1, OLD.column2,...);
       RETURN OLD;
    END;
$$ LANGUAGE plpgsql;


enforcing function/constant names length in Oracle 18 to be 30 characters in Oracle 18 is there a way to enforce the max length of function names (and constant-names) to be 30 chars only (as it is in Oracle 11 and priors)?

In general it appears that the answer is "no". Allowable object name length is a function of the COMPATIBLE initialization parameter. See here:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-05F1B577-C08C-4DB9-925A-8799C76ADFF4

This parameter is usually intended to ensure your ability to downgrade an Oracle version to a previous release following an unsuccessful upgrade. Once set to a higher value, it cannot be lowered again, as it will have an impact on the data dictionary and other Oracle internal features. See here:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/COMPATIBLE.html#GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/tasks-complete-only-manually-upgrading-oracle-database.html#GUID-C2DE66A9-61F0-41AE-A028-FEAC57ECC0BC


Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3) I am new to postgres,

I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing this table (very frequently).

I have a way to delete in about 100-400K row chunks at a time. Between each delete, I want to make the query sleep - so that other operations can get a chance to access this table.

I have the code, but I believe in this version, it locks the table the entire time the query runs (with all the sleeps). How can I actually release the table while the process sleeps? Thank you!!

My code so far:

CREATE SEQUENCE tmp_sq increment by 1 minvalue 1 maxvalue 53 start with 1;

DO $$

DECLARE
w_counter integer;

BEGIN
w_counter := (SELECT nextval('tmp_sq'));

  while w_counter < 53 loop
    raise notice 'Value: %', w_counter ;

    w_counter := (SELECT nextval('tmp_sq'));

-- this way of breaking up the delete into chunks works for my table because of dates.
    delete from table_a where date_part('week',my_date) = w_counter;

    raise notice '     Rows Were Deleted ';

    --sleep
    raise notice 'Sleeping Now.....';
    perform pg_sleep(60);
  end loop;

END $$;

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX.

Each individual row that is being deleted will be locked for the duration.  This should only block other processes if those other processes are trying to update the rows (or delete them themselves).  Normally with bulk deletions you are deleting rows that no one else cares about, which means no one else will be trying to update them.

If you really need to unlock all the deleted rows and the table periodically, then you need to do each loop of the deletions in a separate transaction.  There is no easy way to do this in a DO block because the entire block runs in a single transaction.  So you should put your loop outside of PostgreSQL, like in bash, perl, python, etc.


Cannot view SQL Server Agent in SSMS v17 I'm logged into two accounts. The SA account and my personal account. On my SA account, I can see the SQL Server Agent node, and on the other I cannot. They are both connecting to the exact same server and instance.

In my personal user properties I have all roles including SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole checked. I'm also a sysadmin.

I ended up deleting my profile in MSSQL and recreating it to fix it.


Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view? I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables.

1, 2, 3, 4

In testing this approach, I've discovered something that doesn't make a whole lot of sense to me. When I filter on "partitioning column" on the fact view, the optimizer only seeks on the relevant tables. Additionally, if I filter on that column on the dimension table, the optimizer eliminates unnecessary tables.

However, if I filter on some other aspect of the dimension the optimizer seeks on the PK/CI of each base table.

Here are the queries in question:

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where o.ObservationDateKey >= 20000101
    and o.ObservationDateKey <= 20051231
group by od.[Year];

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.DateKey >= 20000101
    and od.DateKey <= 20051231
group by od.[Year];

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.[Year] >= 2000 and od.[Year] < 2006
group by od.[Year];

Here's a link to the SQL Sentry Plan Explorer session.

I'm working on actually partitioning the larger table to see if I get partition elimination to respond in a similar fashion.

I do get partition elimination for the (simple) query that filters on an aspect of the dimension.

In the meantime, here's a stats-only copy of the database:

https://gist.github.com/swasheck/9a22bf8a580995d3b2aa

The "old" cardinality estimator gets a less expensive plan, but that's because of the lower cardinality estimates on each of the (unnecessary) index seeks.

I'd like to know if there's a way to get the optimizer to use the key column when filtering by another aspect of the dimension so that it can eliminate seeks on irrelevant tables.

SQL Server Version:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Enable trace flag 4199.

I also had to issue:

UPDATE STATISTICS dbo.ObservationDates
WITH ROWCOUNT = 73049;

to get the plans shown below. Statistics for this table were missing from the upload. The 73,049 figure came from the Table Cardinality information in the Plan Explorer attachment. I used SQL Server 2014 SP1 CU4 (build 12.0.4436) with two logical processors, maximum memory set to 2048 MB, and no trace flags aside from 4199.

You should then get an execution plan that features dynamic partition elimination:

select
    od.[Year],
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where
    od.[Year] >= 2000 and od.[Year] < 2006
group by
    od.[Year]
option (querytraceon 4199);

Plan fragment:

This might look worse, but the Filters are all start-up filters. An example predicate is:

Per iteration of the loop, the start-up predicate is tested, and only if it returns true is the Clustered Index Seek below it executed. Hence, dynamic partition elimination.

This is perhaps not quite as efficient as static elimination, especially if the plan is parallel.

You may need to try hints like MAXDOP 1, FAST 1 or FORCESEEK on the view to get the same plan. Optimizer costing choices with partitioned views (like partitioned tables) can be tricky.

The point is you need a plan that features start-up filters to get dynamic partition elimination with partitioned views.

Queries with embedded USE PLAN hints: (via gist.github.com):

Serial plan
Parallel plan


Vertica UNION and ORDER BY I want to use ORDER BY clause but still have one specific entry on the top.
I thought this might do it but Vertica is ignoring the ORDER BY clause on the second query:

(SELECT country_id, country_name
FROM country_dim
WHERE country_dim.iso_country_code LIKE 'US')
UNION
(SELECT country_id, country_name
FROM country_dim
WHERE country_dim.iso_country_code NOT LIKE 'US'
ORDER BY country_name ASC)

Resulting in

12  United States of America
10  Germany
5   Brazil
6   Canada
7   China
8   France
4   Algeria
3   Aland Islands
2   Albania
8   Denmark
11  United Arab Emirates
13  Central African Republic
-1  Unknown
14  Svalbard and Jan Mayen

DDL is

CREATE TABLE country_dim
(
    country_id int NOT NULL,
    iso_country_code char(2) NOT NULL,
    country_name varchar(512) NOT NULL,
    create_ts timestamptz,
    update_ts timestamptz
);

ALTER TABLE country_dim ADD CONSTRAINT country_dim_pk PRIMARY KEY (country_id) DISABLED;

The query needs ORDER BY in the end, not in the subqueries (and note that the parentheses are optional there, at least in standard SQL, not sure if Vertica requires them):

( SELECT country_id, country_name
  FROM country_dim
  WHERE iso_country_code LIKE 'US')
UNION
( SELECT country_id, country_name
  FROM country_dim
  WHERE iso_country_code NOT LIKE 'US')     -- without ORDER BY
ORDER BY
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ;

But since you are grabbing all the data from the same table, there is no need for the UNION and the subqueries:

SELECT country_id, country_name
FROM country_dim
ORDER BY
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ;


In a data warehouse with small amount of writes, would RAID 1 be better than RAID 1+0? We are using HP Vertica database 7.2 community version.  300GB of data on one node. Thats a small amount of data.

HP officially recommends RAID 1+0.
https://community.dev.hpe.com/t5/Vertica-Knowledge-Base/RAID-Storage-for-HPE-Vertica/ta-p/234225

But I do not envision a problem with write performance which is the point of RAID 0.  I am not planning to load, insert or write large amounts of data.  I am planning to query large amounts of data, but not write a lot.  It seems to me that RAID 1 is sufficient.

Is there anything wrong with using RAID 1 instead of 1+0?

This is a pretty open ended question and you might want to just test it yourself and see.  It depends on how you query and write the data.  This will determine how many IOPS you need at peak use, which if you want to ensure you'd figure out by testing then deciding.

However if that is not an option, do you have hundreds of gigs of RAM?  Some systems do.  If so, perhaps your read/write patterns will hit RAM and Disk IO won't matter.  Are you using SSDs or magnetic disks?  SSDs have a much higher disk IO and IOPS than traditional spinning media so the write penalty might not even be noticeable.

Really not sure how much more to add here, it'll come down to your testing results and IOPS needs.


Strange query results for DELETE with ROW_NUMBER In the following DELETE statement I try to delete all rows except the first one ordered by some criteria. (The actual query makes more sense, this is just a repro. All the sys.objects stuff is just for generating test data.)

Note the filter r <> 1. Yet, the OUTPUT clause outputs deleted rows with r = 1. How can this be?

USE tempdb
SET XACT_ABORT ON

BEGIN TRAN

    SELECT *
    INTO #o
    FROM sys.objects

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

    DELETE k
    OUTPUT Deleted.name, Deleted.r
    FROM (
        SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
        FROM #o k
    ) k
    WHERE r <> 1 --OUTPUT returns rows with (r = 1)

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

ROLLBACK

Query results:

(The 3rd result set is complete - only one row.)

Note, that all rows except the first one were deleted. The numbering order of column r does not seem to match what was requested. And there is a row with r = 1.

This is SQL Server 2014 CU3 with trace flag 4199 enabled.

This is expected behaviour at the moment

  the function gets evaluated on the DELETE stream.

So it actually behaves like this (pseudo code)

DELETE k
OUTPUT Deleted.name,
       ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r
FROM (
    SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #o k
) k
WHERE r <> 1 --OUTPUT returns rows with (r = 1)

Although this is the currently defined expected behaviour it isn't really reasonable and they say

  Long term, we need to actually fix the behavior of OUTPUT clause to
  match that of the ANSI SQL standard which will result in change of
  results. So we will look at the correct semantics for a future version
  of SQL Server since there might be apps that rely on the current
  behavior.

I haven't tested on SQL Server 2014 but on 2012 the plan looks as the below.

After the delete operator (to the left) the column values from the deleted rows are sorted back into object_id order and the row_number re-applied.

It looks like the same is happening in your case from the results. (temp tables have a negative id and are sorted first before sysrscols which has a low positive object_id of 3).

As well as the dubious semantics of the result the second sort by object_id doesn't seem strictly necessary in this plan as it looks likely that they will already be sorted in that order in any event.

Regarding workarounds for this specific case changing the output clause to OUTPUT Deleted.name, 1 + Deleted.r AS r would work.

For more complicated WHERE clauses I think you'd need a pass to calculate the row_number and then a join. e.g.

ALTER TABLE #o
  ADD CONSTRAINT PK PRIMARY KEY (object_id);

WITH k AS
(
 SELECT *,
        ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #o
)
MERGE k AS k1
using k AS k2
ON k1.object_id = k2.object_id
WHEN matched AND k2.r <> 1 THEN
  DELETE
OUTPUT Deleted.name,
       k2.r;

Results


Optimal SQL Server 2012 Disk Configuration I will be installing SQL Server 2012 on a new Dell T430 server containing four (4) physical 15K SAS 6Gbps disk drives using a PERC H730P RAID Controller and RAID 1. Since this will be the first time I've installed SQL Server 2012, can someone please recommend an optimal configuration for disk allocations. My thoughts were to install the OS, programs and main databases on one physical drive and put the transaction and logs (both TEMPDB and main DBs) on the other disk drive. Would someone have a better recommendation so I can get the best performance out of the limited resources? Thanks.

Best practises state that the optimal storage configuration is to have at least three volumes: One for the OS, another for the Database files and the third for transaction logs and to that you should add a fourth one for tempdb.

Sometimes you just don't live in an optional world though. If you cant possibly get a third pair of disks, I would recommend that you benchmark different configuration using SQLIO. In your case you might be able to squeeze more I/O from the raid controller, without sacrificing redundancy by setting all four disks up as a single volume using RAID 1+0 and create four partitions on that for the os, data, log and tempdb. Only a benchmark will tell.


Postgres JSONB date range search I have a JSONB object with many elements, one of which is a date.  The following query is now very slow with millions of rows in my table:
SELECT * FROM table WHERE obj->>'dob' BETWEEN '2019-01-01' AND '2020-01-01'.

How can I improve performance on this query?

You can create an index on the column. However casting strings to dates for an index isn't straightforward, as the conversion isn't recognized as "immutable" by Postgres (although it is).
With a little workaround, this can be done though:
create function dob(p_value jsonb)
returns date
as
$$
  select (p_value ->> 'dob')::date;
$$
language sql
immutable;

Then you can create an index:
create index on the_table ( (dob(obj)) );

And use the function in the WHERE clause:
SELECT *
FROM the_table
WHERE dob(obj) BETWEEN DATE '2019-01-01' AND DATE '2020-01-01'


mysql service takes too much memory i have a mysql 5.6.19 on linux server, and it takes about 8 Gb of memory. That is much more than i expect.

There are about 150 db, each with 700 tables. All of them are innodb. We do not care about their safety.

SHOW ENGINE INNODB STATUS - Total memory allocated 4395630592.
So i can't understand why mysql takes additional 3.5 Gb, any suggestions ?

my.cnf
innodb_log_buffer_size = 100M
innodb_log_file_size=1000M
innodb_flush_log_at_trx_commit=0
innodb_open_files=50000
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_force_recovery=0
innodb_buffer_pool_size=4G
innodb_flush_log_at_timeout=5
innodb_stats_on_metadata=OFF
innodb_checksums=OFF
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_file_per_table=0
innodb_sort_buffer_size=2M
max_connections=400
tmp_table_size=20M
max_heap_table_size=20M
innodb_ft_total_cache_size=64000000
innodb_ft_result_cache_limit=100000000

interactive_timeout=1500
wait_timeout = 300

open_files_limit=50000
table_open_cache=20000
table_definition_cache=20000

There are many things that are wrong with your configuration and running status:

With your configuration, before running no query, you need 1.5 GB of static memory assignment- before the buffer pool is even used an no connection is performed. Most of it (1GB) goes to performance_schema, but there is still 500MB that are allocated (vs the 42k for the default configuration).

You also have quite some high number of temporary tables created (180 tables/s), half of them in memory -I would bet that the other 90 are created in memory, then on disk. That can account, in your setup, to up to 1.8 GB of temporary tables per second.

Your queries have a non-negligible amount of full joins and full table scans, reading randomly 26K rows per second and sorting 3K per second. In summary- you end up wasting lots of resources in read buffers, join buffers and sort buffers. And that is for 90 simultaneous connections. 90 is not a high number for MySQL, but you need more resources for the type of queries that you are executing- which examine lots of rows.

You say that you have 100K tables, which usually affects the InnoDB dictionary size (146M) and the open_table_cache. You have increased some of the caches, but I do not agree that that is the right way to proceed.

Aside from that, you have lots of connections using SHOW STATUS commands, typical from an ORM/framework. Not worrying in 5.6, where metadata commands do not update the InnoDB statistics automatically, but from that I can derive that the queries you are executing are not the most optimal ones.

So, my recommendations:

Turn down most of the memory-related, session-only or query-only variables (e.g. tmp_table_size)
Turn down table-cache related variables
Reduce the concurrency by setting up a pool of connections (or reconfigure the current one). This will allow you to turn down max_connections without affecting the application throughput.

These ones will increase latency, but that is needed in order to increase the concurrency. If you want more concurrency with the same memory resources you will have to split your tables on several servers (aka sharding). In general, there are many things that I do not like from your configuration file, but I am not bold enough to tell you to change them without evaluating them myself.

Turn off performance_schema (it will save you 1GB of memory for your configured number of connections)
Fix your queries and start using indexes. Or create the appropriate indexes. This is probably the first step.
Avoid using MySQL- Without seeing your queries, something tells me that most of them could be easily cached at application or middleware layer. 5.6 even comes with a free memcached server!
Watch for the rollbacks you are executing (1 was running at the time of the statistics gathering). Rollbacks are very expensive resource-wise (memory and CPU).

For more detailed information, I would need to perform a query audit.


Table behind synonym does not exist when I do

select *
from all_synonyms
where synonym_name='SYNAAA'

I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A.

Given SYNAA is public, I should be able to query it directly by just select * from SYNAA right? but I got "table or view does not exist" error, I tried to refer it by schemaname.SYNAAA or schemaname.TABAAA, or grant but all failed.

The thing is when I check schema A's table list, there is no table TABAAA under it. Is this the reason I got "table or view does not exist" error? table behind the synonym SYNAA does not exist?

Thank you very much!

A synonym is an alternative name for an object.

From Database SQL Language Reference

  Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.

So the synonym is an additional name that you can use to access the object. But you must have still appropriate grants on the base object.

If SYNAAA is a public synonym for the table A.TABAAA and you get an error
when you execute

select * from SYNAAA

then you will get the same error if you execute

select * from A.TABAAA

the error

ORA-00942 table or view does not exist

means either the table A.TABAAA  exists but you have no privileges on it or the table does not exist at all. The view all_tables doe not show you all tables of the database but only the tables that you can access. Generally  you cannot check if a table does not exist at all nor can you grant yourself privileges on a table of another schema. A DBA can  query the dba_tables view to check if the table exists.

select owner, table_name
from dba_tables
where owner='A'
and table_name='TABAAA'

If Oracle raises an error message and you post this here then you hould always post the complete error message, which is

ORA-00942 table or view does not exist

An explanation of the error message can be found in the documentation of your database version, in the documentation of the current version. If there is no explanation you should try the documentation of Oracle 9i.
For this error message  the 9i manual says

  (...)
  Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.


Ensure a Row is unique on insert SQL Server simple problem - ensure that only 1 batch is active at one time. Each batch has an assigned BatchID# - however that ID# might not be unique if a batch is re-run at some point. However, for a given ID# there should only ever be one "active" batch - that is only one batch with BatchComplete equal to NULL.

So the table I have is:

CREATE TABLE BatchTable (
     BatchNumber INT IDENTITY(1,1) PRIMARY KEY
     ,BatchID INT NOT NULL
     ,BatchStart DATETIME2(2) NOT NULL DEFAULT GETDATE()
     ,BatchComplete DATETIME2(2) NULL
     ,BatchLastOperation DATETIME2(2) DEFAULT GETDATE()
     BatchState INT NOT NULL
);

Then I have a constraint:

ALTER TABLE [dbo].[BatchTable]
ADD CONSTRAINT bt_OnlyOneBatchNumber UNIQUE (BatchID, BatchComplete)
GO

But last night this failed -- two inserts occurred in the same moment and both were inserted - chaos ensued.

I am guessing the problem was that ("20160619", NULL) != ("20160619", NULL) and therefore the rows were seen as unique. So the solution would be to create a non-null "IsComplete" column rather than relying upon the existence of the datetime in BatchComplete column.

So my questions are:

Is my guess correct?
Is there a better way? Surely this kind of pattern has to be pretty common (I have done something similar before though I used triggers rather than a constraint).

The BatchState might work rather than IsCompete -- but the constraint would need to be for a given BatchID all other rows must have a BatchState greater or equal to 4. Not sure how to make that constraint.

I don't have access to SQL Server so I googled the syntax for generated columns, but there's probably syntax errors in here anyhow. The idea is to use a generated column that has a unique value (BatchNumber) whenever BatchComplete is not null, and a non-unique number (BatchId) otherwise:

CREATE TABLE BatchTable
( BatchNumber INT IDENTITY(1,1) PRIMARY KEY
, BatchID INT NOT NULL
, BatchComplete DATETIME2(2) NULL
, Active AS (CASE WHEN BatchComplete IS NULL
                  THEN BatchID
                  ELSE BatchNumber
             END) PERSISTED
, CONSTRAINT ONLY_ONE_ACTIVE_AT_ATIME UNIQUE ( BatchID, Active )
);

If BatchComplete IS NULL then UNIQUE (BatchId, BatchId) must hold, i.e only one Active batch at a time.

If BatchComplete IS NOT NULL then UNIQUE (BatchId, BatchNumber) must hold, but BatchNumber itself is unique, so this will always be true.

As mentioned I can't test this, but you might be able to use the idea one way or another.


Splitting row into multiple records based on row value I have an Orders table (text customer_id, int total_orders) which contains overview stats of total orders for each customer.

What I am trying to do is generate a query output that will represent a customer as a row for each third order they've made similar to this.

The combined_key is used as the primary/unique key which is simply the customer_key and count combined as I cannot use an auto-number.

I know how I can identify which records I need to be in the query but I don't know how I can split the records as desired.

select *
from stats
where order_count / 3

I've included a fiddle, please note it's in MySQL because T-SQL is not available.

You can use db<>fiddle for sql-server:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c87b972491dda6462cc4f8b80ff2bd95

This is just a sketch. You need to generate something out of nothing (1 row -> 3 rows). For this you can use a recursive CTE:

with gen (account_id, n, order_count) as (
    select account_id, 1, order_count from stats
    union all
    select account_id, n+1, order_count
    from gen where n < order_count
)
select n-2 as x, n as y, account_id, n
from gen
where n % 3 = 0
order by account_id, n;

I used mod(%) to pick each 3:rd row.

x   y   account_id  n
1   3   1   3
4   6   1   6
7   9   1   9
1   3   2   3


What is the difference between node types and atomic types in XML Im Studying TSQL and I came to the chapter regarding XML, and here a question that I cannot find an answer: "What is the difference between node types and atomic types"?

Thank you!

Sounds like you are not asking about XML itself, but about the XQuery/XPath/XSLT data model known as XDM.

In these languages there are two kinds of value: nodes, which basically means anything within an XML document, and atomic values like integers and strings which have no relationship to any XML document. So for example the query count(//employee) takes a set of nodes as input, and produces an atomic value (an integer) as output. An important operation is atomization, which extracts an atomic value from a node, for example the string that is the value of an attribute node.


Versioning a dataset in an RDBMS using initials and deltas (xpost from SO) I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, the system does a bunch of work to translate it into updates, inserts, and deletes. Initials and deltas are processed inside long transactions to maintain data integrity.

Unfortunately the current solution isn't scaling very well. The transactions are so large and long running that our RDBMS bogs down with various contention problems. Also, there isn't a good audit trail for how the deltas are applied, making it difficult to troubleshoot issues causing the local and remote versions of the dataset to get out of sync.

One idea is to not run the initials and deltas in transactions at all, and instead to attach a version number to each record indicating which delta or initial it came from. Once an initial or delta is successfully loaded, the application can be alerted that a new version of the dataset is available.

This just leaves the issue of how exactly to compose a view of a dataset up to a given version from the initial and deltas. (Apple's TimeMachine does something similar, using hard links on the file system to create "view" of a certain point in time.)

Does anyone have experience solving this kind of problem or implementing this particular solution?

Thanks!

If the "bunch of work to translate it" is occurring during the transaction, then that is adding to the transaction time.  If you can do bulk processing on the data to translate it before you start dong inserts/updates/deletes that would at least decrease the transaction time providing fewer opportunities for contention.

An alternative would be to maintain a copy of the table and make your changes on the copy and then during a brief outage window swap the table names.  The downside is the added storage and the outage window, but the advantage is zero contention.

Update:

I see a couple difficulties with your solution.  Inserts would still cause contention if there were unique indexes on the table.  Updates would become inserts, so those wouldn't be any more difficult.  Deletes would have to be handled somehow.  You could insert a blank record with the delta number to indicate that the record has been deleted.  I'm concerned that the view necessary to pull this data together for querying would be too slow.

Perhaps someone with MySQL knowledge could suggest something.


Batch update timestamp records to correct timezone I'm looking to batch update some timestamp records in postgresql.

My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while after DST kicked in (25th March) so not all the records are 'wrong'.

     timestamp
-------------------
2018-04-13 00:02:01
2018-04-11 20:22:32
2018-04-10 12:00:56
2018-04-10 11:25:56
2018-04-10 01:57:05
2018-04-07 03:38:32
2018-04-05 12:25:10
2018-04-05 09:09:49

I want to convert the range of records in BST to UTC. What's the best way to go about doing this?

To adjust timestamp values for the difference between any two given time zones, use the AT TIME ZONE construct twice in a row. See:

Ignoring time zones altogether in Rails and PostgreSQL

Assuming you want to adjust all timestamps after the begin of British Summer Time (BST) at '2018-03-25 01:00':

UPDATE tbl
SET    ts = ts AT TIME ZONE 'BST' AT TIME ZONE 'UTC'
WHERE  ts >= '2018-03-25 01:00';

Of course, in your particular example you might just subtract an hour:

...
SET    ts = ts - interval '1 hour'
...


Dynamic SQL breaks statement I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the query with dynamic sql it breaks. Here is the statement that works fine.
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(record_name)
            FROM ams_data
            WHERE report_name = 'class_and_component_prices'
            AND commodity ='whey'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
    SELECT
        record_name,
        value,
        date,
        commodity
    FROM
       ams_data
        WHERE report_name = ''class_and_component_prices''
        AND commodity = ''whey''
) t
PIVOT(
    max(value)
    FOR record_name IN ('+ @cols +')
)  AS pivot_table ;';

EXECUTE sp.executesql @sql

And here is my version that takes parameters.
ALTER PROCEDURE  [dbo].[pivot_record]
(
    -- Add the parameters for the stored procedure here
    @tablename VARCHAR(max) = nass_data,
    @report_name VARCHAR(max) = class_and_component_prices,
    @commodity VARCHAR(max) = whey
)
AS
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
        DECLARE @cols AS NVARCHAR(MAX),
                @sql  AS NVARCHAR(MAX),
                @actualTable AS VARCHAR(MAX)

        SELECT @actualTable = QUOTENAME( TABLE_NAME )
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = @tablename

        SET @sql = '
        STUFF((
            SELECT distinct '','' + QUOTENAME(record_name)
            FROM ' + @actualTable + '
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + '''
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'')
            ,1,1,'''')'

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

        -- construct dynamic SQL
        SET @sql ='
        SELECT * FROM
        (
            SELECT
                record_name,
                value,
                date,
                commodity

            FROM
               ' + @tablename + '
                WHERE report_name = ' + @report_name + '
                AND commodity = ' + @commodity + '
        ) t
        PIVOT(
            max(value)
            FOR record_name IN ('+ @cols +')
        )  AS pivot_table ;';

        -- execute the dynamic SQL
        EXECUTE sp_executesql @sql;

When I attempt to run this, I get an error from the first sql statement. I printed off the sql statement prior to attempting to execute and store the result in @cols to ensure that it is exactly the same as my first statement that worked.

I am assuming that some quirk with dynamic sql is causing the issue, but assistance would be appreciated.
EDIT: I figured out that I needed to include a SELECT before the STUFF clause, but I still do not get my intended result, now it just outputs an empty table instead of the proper pivoted data.

It's hard to debug without seeing the output of your 2nd @SQL or @cols.
I'd start from there to debug, SELECT/PRINT the variable and copy-and-paste-run-it
or try different way of cols?
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

FYI this is my PIVOT query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = '
select *
from
(
  select SQLInstanceName, Date, node_name
  from #Final
  WHERE 1=1 AND  Has_Changed = ''Y''
) src
pivot
(
  MIN(node_name)
  for [Date] in ('  + @ColumnName + ')
) piv
order by 1
'
--SELECT @DynamicPivotQuery --debug
EXEC sp_executesql @DynamicPivotQuery


How to Add AFTER Delete Trigger in My case? I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables

1) The Patients which are currently admitted in the Hospital, that Table is represented as resident

and

2) This is The Table where i keep Records of the Outgoing Patient (i.e the Patient who successfully Undergoes treatment and walks out of Hospital) as outpatient.

Now, in the Resident table i have a column called residentID which distinguishes the Patients who are there by Just there Unique Number, and In outpatient i keep the records by OutpatientID of which patient who left Hospital.

So I want to make a Trigger in Such a way that Whenever i Write a DELETE FROM resident where residentID = 1 than the Person's ID who just got Deleted from resident table comes into Outpatient Table. So how do we make Such Trigger which does this, I am pretty much quizzed here. This is what i want, any Inputs would be highly appreciated.

You should take the vbalues of OLD and use them in INSERT command.

Something like this:

CREATE TRIGGER triger_name_after_delete
AFTER DELETE
   ON resident FOR EACH ROW
BEGIN
   -- Insert record into Outpatient table
   INSERT INTO Outpatient (residentID, [rest columns names...] )
   VALUES ( OLD.residentID, OLD.[rest columns names...])
END;


Retrieve next queue item I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be hugely grateful if someone could point me at the cause of this sudden dip in performance. (It seems to have been an almost overnight drop).

Here's the table definition:

CREATE TABLE [dbo].[messagequeue] (
    [id]             INT             IDENTITY (1, 1) NOT NULL,
    [testrunident]   VARCHAR (255)   NOT NULL,
    [filesequence]   INT             NOT NULL,
    [processed]      BIT             NOT NULL,
    [dateentered]    DATETIME        NULL,
    [filedata]       VARBINARY (MAX) NULL,
    [retries]        INT             NOT NULL,
    [failed]         BIT             NOT NULL,
    [msgobject]      VARBINARY (MAX) NULL,
    [errortext]      VARCHAR (MAX)   NULL,
    [sourcefilename] VARCHAR (MAX)   NULL,
    [xmlsource]      VARCHAR (MAX)   NULL,
    [messagetype]    VARCHAR (255)   NULL
);

CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
    ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
    INCLUDE([id], [testrunident], [filesequence]);

CREATE NONCLUSTERED INDEX [messagequeue_sequence_idx]
    ON [dbo].[messagequeue]([testrunident] ASC, [processed] ASC)
    INCLUDE([filesequence]);

CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
    ON [dbo].[messagequeue]([testrunident] ASC, [filesequence] ASC);

And here's the query used to retrieve the next row to be processed:

select messagequeue.id, messagequeue.testrunident, messagequeue.filesequence,
    messagequeue.processed, messagequeue.filedata, messagequeue.retries, messagequeue.failed,
    messagequeue.msgobject, messagequeue.xmlsource
    from messagequeue where id = (
        select top 1 id from messagequeue mqouter
        where processed = 0
        AND failed = 0
        AND (filesequence = 0 OR
        filesequence = (
                select max (filesequence) + 1
                from messagequeue mqinner
                where mqinner.testrunident = mqouter.testrunident
                and mqinner.processed = 1
            )
        )
        order by testrunident, filesequence
        )

There are multiple rows with the same testrunident, each has a filesequence which should be sequential, however some may be missing so the query should only return the NEXT row where the previous row has processed = 1 or filesequence = 0 which denotes this is the first row within a testrunident group.

Here's an SQLFiddle to give an idea: SQL Fiddle

Query plan: Query Plan XML

Is there a better way to write the query?

EDIT 1 - Example of ensuring previous row was processed before selecting a row:

Where `id` = testrunident and `fs` = filesequence

id | fs | processed
1  | 0  |  1
1  | 1  |  1
1  | 2  |  1
1  | 4  |  0 -- this shouldn't be next as no row with seqeuence = 3 and processed = 1
2  | 0  |  0 --this should be the next row
2  | 1  |  0

SQLFiddle highlighting this

Taking just the core part that identifies the id of the row to return, the following query encapsulates the logic needed:
SELECT TOP (1)
    MQ.id
FROM dbo.messagequeue AS MQ
WHERE
    -- Current row
    MQ.processed = 0
    AND MQ.failed = 0
    AND
    (
        EXISTS
        (
            -- Previous row in strict sequence
            SELECT *
            FROM dbo.messagequeue AS MQ2
            WHERE
                MQ2.testrunident = MQ.testrunident
                AND MQ2.processed = 1
                AND MQ2.failed = 0
                AND MQ2.filesequence = MQ.filesequence - 1
        )
        OR MQ.filesequence = 0
    )
ORDER BY
    MQ.testrunident ASC,
    MQ.filesequence ASC;

Executing this query efficiently needs a small change to an existing index, whose definition is currently:
CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
    ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
    INCLUDE([id], [testrunident], [filesequence]);

The change involves moving testrunident and filesequence from the INCLUDE list to the index keys. The new index still supports all the queries the old one did, and as a side-effect of the change, the redefined index can now be marked as UNIQUE. The following script will perform this change (you can perform this operation ONLINE if you are running Enterprise Edition):
CREATE UNIQUE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
ON [dbo].[messagequeue]
(
    [processed] ASC,
    [failed] ASC,
    testrunident ASC,
    filesequence ASC
)
INCLUDE
(
    [id]
)
WITH
(
    DROP_EXISTING = ON
    --, ONLINE = ON
);

With this index in place, the execution plan for the revised query is:

To return data from the identified row, the final query is a simple extension:
SELECT
    MQ3.id,
    MQ3.testrunident,
    MQ3.filesequence,
    MQ3.processed,
    MQ3.filedata,
    MQ3.retries,
    MQ3.failed,
    MQ3.msgobject,
    MQ3.xmlsource
FROM dbo.messagequeue AS MQ3
WHERE
    MQ3.id =
    (
        SELECT TOP (1)
            MQ.id
        FROM dbo.messagequeue AS MQ
        WHERE
            MQ.processed = 0
            AND MQ.failed = 0
            AND
            (
                EXISTS
                (
                    SELECT *
                    FROM dbo.messagequeue AS MQ2
                    WHERE
                        MQ2.testrunident = MQ.testrunident
                        AND MQ2.filesequence = MQ.filesequence - 1
                        AND MQ2.processed = 1
                        AND MQ2.failed = 0
                )
                OR MQ.filesequence = 0
            )
        ORDER BY
            MQ.testrunident ASC,
            MQ.filesequence ASC
    );

Second option
There is another option because you are using SQL Server 2012, which introduced the LAG and LEAD window functions:
SELECT TOP (1)
    ML.id
FROM
(
    SELECT
        M.id,
        M.testrunident,
        M.filesequence,
        M.processed,
        M.failed,
        PreviousProcessed = LAG(M.processed) OVER (
            ORDER BY M.testrunident, M.filesequence),
        PreviousFailed = LAG(M.failed) OVER (
            ORDER BY M.testrunident, M.filesequence),
        PreviousFileSequence = LAG(M.filesequence) OVER (
            ORDER BY M.testrunident, M.filesequence)
    FROM dbo.messagequeue AS M
) AS ML
WHERE
    -- Current row
    ML.processed = 0
    AND ML.failed = 0
    -- Previous row in strict order
    AND ML.PreviousProcessed = 1
    AND ML.PreviousFailed = 0
    AND ML.PreviousFileSequence = ML.filesequence - 1
ORDER BY
    ML.testrunident,
    ML.filesequence;

This query also needs an adjustment to an existing index, this time by adding processed and failed as included columns:
CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
ON [dbo].[messagequeue]
(
    [testrunident] ASC,
    [filesequence] ASC
)
INCLUDE
(
    [processed],
    [failed]
)
WITH
(
    DROP_EXISTING = ON
    --, ONLINE = ON
);

With that index in place, the execution plan is:

I should also mention that your general approach would be unsafe if more than one process were ever working on the same queue at once. For more information about this and general queue table design see Remus Rusanu's excellent article Using Tables As Queues.
Further analysis
The original execution plan showed a big difference between the number of rows SQL Server expected to process, versus the number actually encountered during execution. Opening the plan with SQL Sentry Plan Explorer shows these differences clearly:

SQL Server chose an execution strategy that would have worked well if the number of rows really had been as small as it had estimated:

Unfortunately, the chosen strategy did not scale well when the estimates turned out to be far too low. Parts of the execution plan were actually executed 458,260 times - not a recipe for instant results. Had the SQL Server optimizer known the true numbers, it likely would have chosen a different strategy.

It is tempting to think that a discrepancy between estimated and actual row counts must be due to inaccurate statistical information, but chances are you have reasonably up-to-date single-column automatic statistics on these tables. Improvements might be made by providing additional statistical information, but the root cause of the inaccurate estimates is something quite different in this case.
By default, when SQL Server chooses an execution plan for a query it assumes all potential result rows will be returned to the client. However, when SQL Server sees a TOP operator, it rightly takes the number of rows specified into account when estimating cardinality. This behaviour is known as setting a row goal.
Essentially, a row goal means the optimizer scales back estimates under the Top operator to reflect the fact that fewer rows will be needed than the full potential size of the result set. This scaling implicitly assumes that values of interest are evenly distributed within the set.
For example, say you have a table with 1000 rows, of which 10 satisfy some query predicate. Using statistics, SQL Server knows that 1% of the rows meet the criteria. If you write a query to return the first row that matches, SQL Server assumes that it will need to read 1% of the table (= 10 rows) in order to find that first match. In the worst case, the ten rows that meet the criteria might appear last (in whatever order they are searched) so 990 rows that do not match will be read before SQL Server encounters the one you want.
The improved query (using the improved index) still suffers from this problem to a certain extent, but the effects are much less marked:

The fundamental question we are asking of the optimizer's row goal logic here is: how many rows do we need to check before we find the first one (according to the order by clause specification) that is in sequence, unprocessed, and not marked as having failed. This is a tough question to answer based on the limited statistics SQL Server keeps.
In fact there is very little we can do to communicate all nuances of this complex problem to the optimizer effectively. The best we can do is to provide it with an efficient index that we expect to locate the qualifying row as early as possible.
In this case, that means providing an index that returns unprocessed, un-failed entries in order by clause order. We expect to have to check relatively few of these before finding the first one that is in sequence (i.e. the preceding row exists and is marked as processed and not marked as failed).
Both solutions shown above eliminate the Key Lookup operations seen in the original query because the new index now includes (covers) all needed columns. Also, the new index is likely to find the target row more quickly. The original execution plan scanned index IXd_testrun_sequence in (testrunident, filesequence) order, meaning it would encounter old test runs first, most of which will be marked processed. We are looking for unprocessed rows in the outer query, so this strategy was inefficient (we end up performing the sequence check on 458,260 rows).
Finally, checking for a particular sequence value is much more efficient than finding the maximum of a potentially large set. This is the difference I have highlighted in the previous code as looking for the previous row in strict sequence. There is a semantic difference between these two queries and the solution using MAX shown in the question. My understanding is that you are interested in the first matching row; that row need not be the highest filesequence for the testrunident.


Financial Statement table I want to ask about database design for financial statement.
I want to combine balance sheet, profit loss, cash flow,and ratio into one big table since each statement is dependent each other.

Here is my table structure:

Id,
RegistrationNo,
Year (date),
ProfitLossNetSales,
ProfitLossMonthlySales,
.....,
BalanceReliabilityFinInformation,
BalanceCashBank,
BalanceTimeDeposit,
.....,
CashFlowInterestExpenses,
......

Is it okay to have table structure like this (having many columns in one table)?

Update:
In application, the user input balance sheet form and profit loss form (like input data in excel).
The Cash Flow and Ratio form is read only and the value are based balance and profit loss.

It is OK to have many columns in one table. Indeed, this is the normal situation. It is not OK, however, to mix different types of information in a single table.

Each table models a single class of "thing" in the real world. The rules of normalisation guide a database designer to separate classes from one another. Denormalisation (storing several things in one table) is a legitimate technique but should be used sparingly, with caution, and proper risk mitigation in place.

In your specific case you will have to consider what is your business domain. Are your users interested solely in the balance sheet bottom-line numbers for several corporations over several years (e.g. investment and trading point of view), or do they need the detail of how those numbers are calculated (e.g. an accountancy / book-keeping point of view)? For the former the table you mentions would be OK in my opinion. For the latter you need to model individual transactions, double-entry ledgers etc., and calculate your bottom-line numbers from these as required.


viewing Original Object under synonyms I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like:

CREATE OR REPLACE SYNONYM "ABC"."ABC_CHAR" FOR "XYZ"."ABC_CHAR";

Now my question is: is there a way for me to see the Actual SQL for the View XYZ.ABC_CHAR that is in the other schema?

In the tree view on the left-hand-side, expand the tree for your database connection. Expand  "Other Users", then "XYZ", then "Views". Double-click on the view name "ABC_CHAR" - this will open the column list. Click "SQL" above the column list to see the query that created the view.

The other way to achieve this is to use SQL:

select text
from all_views
where owner='XYZ'
and view_name = 'ABC_CHAR';

This all assumes you have the correct grants to see the view text.


Create Synonym error for a user-SQL Server I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission.
When the user tried to create synonym as shown below.

USE [DEMODB]
GO
CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD]
GO
The user gets the below error message.
The specified schema name "dbo" either does not exist or you do not have permission to use it.

I checked the login/user settings.
And the login belongs to public server role and user is mapped to the database with default schema as [dbo]

And also create synonym permission is granted for that database for that user as mentioned earlier.

How can i fix this.

A user requires not only CREATE SYNONYM statement permissions but also ALTER permissions on the schema in order to create a synonym. ALTER permission on a schema, especially dbo, should be granted with care to follow the security principle of least privilege. It would be better (and easier) if the user creates the synonym in their own schema or in a schema with an owner other than dbo so that the user is sandboxed into that schema for ALTER purposes.

If the minimally-privileged user must create the synonym in the dbo schema, consider encapsulating the CREATE SYNONYM DDL in a stored procedure and use module signing to elevate permissions. That way, a user with execute permissions on the proc can create synonyms in the dbo schema but is limited to the functionality performed by the stored procedure. Below is an example of this technique.

CREATE OR ALTER PROC dbo.CreateSynonym
      @SynonymSchemaName sysname
    , @SynonymName sysname
    , @SynonymForServerName sysname
    , @SynonymForDatabaseName sysname
    , @SynonymForSchemaName sysname
    , @SynonymForObjectName sysname
AS
DECLARE @CreateSynonymStatement nvarchar(MAX) = N'CREATE SYNONYM '
    + QUOTENAME(@SynonymSchemaName)
    + N'.'
    + QUOTENAME(@SynonymName)
    + N' FOR '
    + QUOTENAME(@SynonymForServerName)
    + N'.'
    + QUOTENAME(@SynonymForDatabaseName)
    + N'.'
    + QUOTENAME(@SynonymForSchemaName)
    + N'.'
    + QUOTENAME(@SynonymForObjectName)
    + N';'
EXEC sp_executesql @CreateSynonymStatement;
GO

--create certificate and sign proc
CREATE CERTIFICATE CreateSynonymCert
   ENCRYPTION BY PASSWORD = 'T3mP0@rypAsso0rd'
   WITH SUBJECT = 'For CREATE SYNONYM in dbo schema';
ADD SIGNATURE TO dbo.CreateSynonym BY CERTIFICATE CreateSynonymCert WITH PASSWORD = 'T3mP0@rypAsso0rd';

--private key is ephemoral for this purpose so we remove it
ALTER CERTIFICATE CreateSynonymCert REMOVE PRIVATE KEY;

--create a user from certificate with the needed permissions
CREATE USER CreateSynonymCertUser FROM CERTIFICATE CreateSynonymCert;
GRANT CREATE SYNONYM TO CreateSynonymCertUser;
GRANT ALTER ON SCHEMA::dbo TO CreateSynonymCertUser;

--We could grant execute directly to the user but a role is easier to manage
--when multiple users are involved and adheres to RBAC security principles.
CREATE ROLE CreateSynonymRole;
ALTER ROLE CreateSynonymRole ADD MEMBER YourUser;
GRANT EXECUTE ON dbo.CreateSynonym TO CreateSynonymRole;
GO

--example usage
EXECUTE AS USER = 'YourUser';
GO
EXEC dbo.CreateSynonym
      @SynonymSchemaName = N'dbo'
    , @SynonymName = N'MYSYN'
    , @SynonymForServerName  = N'SERVER01'
    , @SynonymForDatabaseName  = N'DEMODB'
    , @SynonymForSchemaName  = N'dbo'
    , @SynonymForObjectName  = N'CHILD';
GO
REVERT;
GO


'Can't create thread to handle new connection' on RDS I have two production instance of MySQL on AWS RDS that failed with:

2017-06-20T15:48:35.226833Z 0 [ERROR] Error log throttle: 1 'Can't create thread to handle new connection' error(s) suppressed
2017-06-20T15:48:35.226873Z 0 [ERROR] Can't create thread to handle new connection(errno= 11)

These are two db.m4.2xlarge instances and one capped at 102 connections while the other capped at 38. Not more connections could be made past that. This type of instance should be able to accept more than 2K connections with the default config (which I have).

I opened a support ticket and they manually changed some settings on the instances. After that and a reboot, I could connect with 2500 connections on each server to validate resolution.

Any idea why they had to manually change some settings on the instance (underlying OS I guess)? Is this something to be expected from RDS? What could we be doing that triggered this condition?

Thanks!

This might help you
A Percona blog of tale of 2 limits

We (not RDS) had the second issue after we failed over as part of a disaster recovery exercise.

How old are the RDS instances, were they upgraded, replaced failed over...? Enjoy :)


Worth Splitting MySQL Server Into 2 MySQL Servers To Increase Performance Context
I am using an AWS RDS server to host my MySQL DB.

I work for a footfall company, we have our system installed all over the UK and every time a visitor is seen we write to the DB. We also provide a portal to our customers allowing them to view all the data as readable reports. Customers only use the portal once a week on average so there aren't anywhere near as many reads on the DB as writes.

RDS Stats

The graph above shows the read and write IOPS as you can see the write IOPS is loads higher then read IOPS.

Question
On the portal we don't show any of todays stats and at midnight every night we run a load of cron jobs which summarises all the data.

I am thinking of separating my RDS Server into 2. One would contain all the summarised data which the portal would read from to show the stats. The other would contain all of today's data and it would constantly be written to. At midnight the cronjobs would them summarise the data from the DB containing today's data and store the summarised data in the portal DB.

The idea behind this is to make the portal as fast as possible by removing all the writes.

I am no MySQL guru so this may have no effect, the RDS server can handle all the writes and reads to the DB with the current specs. If the current RDS server can handle the requests I am presuming this won't have an effect?

We are currently just trying to optimise our performance so any advice is really helpful.

Your RDS is coping with your write load but if you want more scalability, separating the read and write load is a good idea: they have different work patterns and probably have conflicting performance tuning needs.

Once you have decided to split then you are free to choose a different product to handle the incoming raw data for the daily write load.  Why not choose a storage medium that is more optimised for writes?  Consider sending the daily the writes to a DynamoDB dynamodb table.
As your cron jobs are already written then you know how you will query the raw data so this can help you structure your DynamoDB table.  You can autoscale dynamoDB to cope with changing or bursty loads.

You can also create a TTL on the raw data so that as time passes and the summary data is more relevant the raw data is deleted, or even send the older raw data to S3 for cheaper storage once it is no longer 'hot'.