Use a case statement to establish the order, or add a lookup table with all colours where you can set an order.

declare @t table(id int, name varchar(20), colour varchar(10));
insert into @t values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

select *
from   @t
order by (case when colour = 'yellow' then  1
               when colour = 'orange' then 0
               else 2
           end);
GO

id | name     | colour
-: | :------- | :-----
 2 | stuff    | orange
 5 | nonsense | orange
 3 | whatever | yellow
 6 | etc      | yellow
 1 | this     | red
 4 | that     | red

dbfiddle here

create table foo(id int, name varchar(20), colour varchar(10));
insert into foo values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

create table bar(colour varchar(10), [order] int);
insert into bar values
('orange', 0),
('yellow', 1),
('red', 2);

select *
from   foo
inner join bar
on foo.colour = bar.colour
order by bar.[order];

GO

id | name     | colour | colour | order
-: | :------- | :----- | :----- | ----:
 2 | stuff    | orange | orange |     0
 5 | nonsense | orange | orange |     0
 3 | whatever | yellow | yellow |     1
 6 | etc      | yellow | yellow |     1
 1 | this     | red    | red    |     2
 4 | that     | red    | red    |     2

dbfiddle here


SQL Server: Should I overwrite existing file for additional backups?

we can do both append as well overwrite while taking backup .

Please find the link:https://technet.microsoft.com/en-us/library/ms191211(v=sql.105).aspx


The reason for the difference is that the articles describe two different versions of Linux.  For the install MongoDB on Ubuntu tutorial you are talking about Ubuntu.  If you are following the install MongoDB on Amazon EC2 tutorial then you are talking about Amazon Linux.

Ubuntu uses deb/apt based packages (which gets you the mongodb user) and Amazon Linux uses rpm/yum based packages (which gets you the mongod user).  You are using Ubuntu according to your question, hence the relevant user for you is "mongodb".

I would point out though, that this likely has nothing to do with the issue you are having with the service status or shutting it down.  That's more likely some sort of problem with the wrong PID being referenced by upstart, or possibly an issue with MongoDB starting in the first place (check your logs).

I would recommend:

Stop MongoDB manually (log in with the mongo shell and issue the shutdown command)
Start the service again, with sudo service start mongodb
Verify that it started correctly (logs, login with the mongoshell again)
Re-check with sudo service mongodb status


SSRS report stuck on Loading but ExecutionLog says otherwise

Sometimes the browser will time out or otherwise not return a rendered report even though the process is still running in the background and ultimately succeeds.  The browser will likely not even tell you it failed, it will just keep spinning or return a blank page.  The logs are indicating "success" meaning the process itself did indeed run correctly, but the logs won't tell you if browser successfully returned a rendered report.  I've usually seen this happen around the 10 minute mark, regardless of the configuration of server or report level timeout settings.  Your Execution Log shows it as over 10 minutes, so this is the possible reason.  Also, the Execution Log returned a massive Byte Count (~353 MB), and this may also be a culprit.

There are a few possible solutions to this problem:

Limit the amount of data that will be rendered in browser using pagination.  The Data Retrieval & Processing time should stay the same, but the Rendering time will be reduced as it is only returning the first page, and will only render other pages when you navigate to those using the arrows.  Reflecting back on the aforementioned massive Byte Count, so somewhere along the way it may have broke the camel's back.
Once the data is processed, exporting to a static format, such as Excel or PDF, will also likely be successful & should throw an error if it failed, rather than it attempting to render the entire report in browser as HTML.
Deploy the report in the export format to a File Share using a Subscription.  This will allow the process to run in the background on the server and render it to a file directly.  If you are unable to schedule this on a regular interval, this may be a bit inconvenient for the user to request it from an admin and require that admin to manually deploy it.  However, I believe this is better than the report user rerunning the same report multiple times, resulting in concurrent executions, further compounding processing issues, affecting all users.


You can use UNION and use it to get the wanted result.
Instead of WHERE email = '' you can use WHERE email IS NULL, if the column doesn't have '' as text

CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT Primary KEY,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);

INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

SELECT GROUP_CONCAT(DISTINCT fullname), email
FROM example
WHERE email <> ''
GROUP BY email
UNION
SELECT fullname, email
FROM example
WHERE email = ''
ORDER BY email;

GROUP_CONCAT(DISTINCT fullname) | email
:------------------------------ | :----------------
Name 2                          |
Name 4                          |
Name 1                          | name1@example.com
Name 3                          | name3@example.com

SELECT GROUP_CONCAT(DISTINCT fullname), email
FROM example
GROUP BY email

GROUP_CONCAT(DISTINCT fullname) | email
:------------------------------ | :----------------
Name 2,Name 4                   |
Name 1                          | name1@example.com
Name 3                          | name3@example.com

db<>fiddle here


Why and for what possible reasons 'sa' gets locked?

When you look at the General tab in the Login Properties of the sa account, you will notice different settings. One of them being Enforce password policy.

I discussed this setting in my answer to the question How to lock a sql login after N unsuccessful login attempts.

Possible reasons for change in lockout behaviour

If you previously didn't have an Active Directory policy for locking out accounts, then  ...

... the user's inability to use the correct password had no adverse effect on the sa account (No locking of sa account)
... the hacker was able to enter multiple passwords for the sa SQL Login until he had access. (No locking, but hacker in system)
... it didn't matter if any script, program, user, system used a wrong password for the sa account.

If you changed the Active Directory policy recently to lock accounts after n wrong password attempts or if you reduced the number of wrong password attempt to cause a lockout, then you might be encountering a locked sa account due to ...

... the user using the sa account is unable to enter the correct password after n attempts, which results in a lock out.
... the hacker is unable to gain access again, which results in a lockout.
... somebody is just having fun locking out the sa account.

Recommended actions

Change the password for the sa account to ensure nobody gains access to the server via this account.
Ensure that the option Failed logins only is enabled at server level in the in the Server properties - xyz | Security tab | Login auditing section . (Better: If you have enough space for your ERRORLOG and are cycling the ERRORLOG at a regular interval, set it to Both failed and successful logins.)

Check the SQL Server ERRORLOG file for login issues and track down who is behind the IP address in the error message.

2017-07-21 09:58:12.48 Logon       Error: 18456, Severity: 14, State: 8.
2017-07-21 09:58:12.48 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ]

this example was from the same host, hence no IP address


Need help about normalizing database to 3NF

You don't need four tables for the user account data. Each user only has one primary email, one secondary email, one first name, one profile picture, one count of trips etc. If there's only one of each entry per user, why not put them all in the same table?

You can set a lot of the fields to be nullable so that they don't all have to be filled in, and you might also want a "registration status" field to show if they're a guest or a registered user.

If you only want to show users certain parts of another user's profile, you don't need to store the publicly available information in a separate table, you can use a query like this:

select profile_picture, display_name, trips_count, likes_count, reviews_count
from user_table where user_id = @SelectedUser


Can I restore an SQL 2016 database mdf at an SQL 2014 Sp1 instance?

No You cannot. You have to bcp out data and bulk inert into 2014 or you can create sql 2014 schema + Data script and run the same on sql server 2014.

See my answer here : https://dba.stackexchange.com/a/55063/8783


Does Oracle 10g Express Edition have enough storage?

@a_horse_with_no_name is correct in recommending the 11g express and you would have more certainty that the project would not run out of room.

You should consider a proviso when you supply the database that you make no representation as to how long it can be used.  Even though you think you will only have ten tables software grows and requests to change or tinker with things never stop. For example:

oh, we have video content now, can you do that?
we need to have lending and borrowing audited
our client interface needs x
we need fast free text searching

All of these are possible but some are not advisable but they all need more tables and storage.

Also keep in mind that you are more likely to hit the limits for memory depending on how many concurrent users and the connection pooling methods used.
"Other hardware restrictions remain the same with CPU usage capped at one physical core and memory at 1GB".


mysql started with option --defaults-file=FILE  Use the specified defaults file

change config.file(my.cnf)


MYSQL database replication Error MY-002061

Also, you just can run

CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;

but this solution has a restriction (see the documentation https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

Use of a trusted local copy of the public key enables the client to avoid a round trip in the client/server protocol, and is more secure than requesting the public key from the server. On the other hand, requesting the public key from the server is more convenient (it requires no management of a client-side file) and may be acceptable in secure network environments.


v$ or rc views to report on RMAN restore database preview

V$BACKUP_DATAFILE

  V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.

Media recovery start SCN is 7962109213
Recovery must be done beyond SCN 7962156379 to clear datafile fuzziness
Finished restore at 28-JUN-2017 09:27:17

And what I am interested in is ABSOLUTE_FUZZY_CHANGE# and CHECKPOINT_CHANGE#  column of that view.

  CHECKPOINT_CHANGE#: All changes up to the checkpoint change number are included in this backup

  ABSOLUTE_FUZZY_CHANGE#:  Highest change number in this backup

I used the following query to get the same result as given by RMAN restore preview command.

SQL>   select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
                        (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
                        where  trunc(completion_time) = to_date('JUN-28-2017','MON-DD-YYYY')
                        and file# <> 0
                        order by completion_time desc
                        );

     FUZZ#    CHKPNT#
---------- ----------
7962156379 7962109213


Error 1046 Mariadb: No database selected

Try escaping it with backticks?

GRANT ALL ON `my-database`.* TO `my-user`@`10.0.0.1` IDENTIFIED BY 'password';

Also, if you already have a user my-user@10.0.0.1, you don't need to provide the IDENTIFIED BY... portion of your grant statement


Is it possible to choose RAISERROR or THROW depending on SQL Server version?

No this isn't possible.

This is invalid syntax in earlier versions and will cause a compile error.

It is not possible to hide the THROW in an EXEC inside the catch block either as a parameterless throw must be directly contained inside the catch.

You would need to deploy the code version you want according to the version of SQL Server you are deploying to (and unfortunately there is not good support for this either in the SSDT tooling that I am aware of - no equivalent of including code lines selectively through conditional compilation)


Are three equalities on a join operation legal in relational algebra?

The notation Producer=Actor=Director is wrong. Besides that, neither ΠActor(Film) nor ΠProducer(Produce) has an attribute Director.
The first projection has only an attribute Actor, the second projection has only an attribute Producer.

In the second answer what is meant if you divide two relations that have the same number of attributes. I think that does not make sense because the result is a relation with no attributes.

You first relation Film(Title,Director,Actor) is very strange.
It defines a relation between three attributes actor, directors and title.
But from my understanding of a film there does not exist such a relation.
There is a relation between actor an title, if an actors plays in a movie with this title.
And there is a relation between a director and a title if a person is a director of this movie.
But I don't understand the tuple (actor, director, title).
Does an actor have a director in a movie and another actor has another director in the same movie?
If that is not the case then you should better use two relations:
A relation Acts(Person, Title)and a relation Directs(Person, Title).

Here are the answers using only natural join, which makes them a little bit clumsy.

The first question

  Which actors produce at least a Film they directed?

has the following answer, using the notation from wikipedia:

   Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \pi_{\text{Actor}}\left(\rho_{\text{Actor}/\text{Director}}\left(\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce})\right)\right)$$

Here is an equivalent SQL code (Oracle):

select F2.Actor
from Film F2 join (
  Film F1 join Produce P
    on (F1.Director=P.Producer and F1.Title=P.Title)
  ) on F2.Actor=F1.Director

Example

For the following data the result is Donald Duck

Film

    Title              | Director    | Actor
    -------------------+-------------+-----------
    Mickey Mouse Revue | Donald Duck | Minnie Mouse
    Mickey Mouse Revue | Donald Duck | Mickey Mouse
    Duck Tales         | Walt Disney | Donald Duck

Producer

    Producer    | Title
    ------------+------------------
    Donald Duck | Mickey Mouse Revue
    Walt Disney | Duck Tales

The second question

  Which actors produce every film they directed

uses additionally the set difference operator \:

   Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \rho_{\text{Actor}/\text{Director}}(\pi_{\text{Director}}((\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce}))\setminus \pi_{\text{Director}}(\pi_{\text{Director},\text{Title}}(\text{Film}) \setminus  \rho_{\text{Director}/\text{Producer }}(\text{Produce}))))$$

These are all (Director,Title) pairs that were not produced by the director of the title:

and therefore these are all directors that haven't produced at least one of their titles:

Similar, these are all directors that produced at least one of their titles:

The difference are the directors that have produced all of the titles the directed.
Now we have to join it to the set of all actors to filter out the directors that are also actors (but not necessary of  the films they directed).

Here is an equivalent SQL Code (Oracle):

Select F2.Actor
from Film F2 join
(
  (Select F1.Director,F1.Title
  from Film join Produce on (
      F1.Director=P.Producer
      and F.Title=P.Filem))
  minus
  (Select Director, Title
  from Film
  minus
  select Producer,Title
  from Produce)
) F3
on(F2.Actor=F3.Director)


T-SQL to Exclude the primary from List of AOAG Replicas

You could adapt the query from this link (which is looking for PRIMARY replicas) to look for secondary replicas.  Example below:

IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
    SELECT AGC.NAME -- Availability Group
        ,RCS.replica_server_name -- SQL cluster node name
        ,ARS.role_desc -- Replica Role
        ,AGL.dns_name -- Listener Name
    FROM sys.availability_groups_cluster AS AGC
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
    INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
    WHERE ARS.role_desc = 'SECONDARY'
END


need to find instances which have multiple rows with the same userID and ResourceID and have a location of 1 and also have other locations

select distinct
  InstancesinLocation1.user_id,
  InstancesinLocation1.resource_ID,
  InstancesinLocation1.location_ID,
  InstancesinLocation1.Instance_ID
from
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage
    where location_ID=1
  ) InstancesinLocation1
join
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage where location_ID<>1
  ) InstancesNotinLocation1
on
  (
    InstancesinLocation1.User_ID = InstancesNotinLocation1.User_ID
    and
    InstancesinLocation1.resource_ID = InstancesNotinLocation1.resource_ID
  )
;

User_ID  Resource_ID  location_ID  Instance_ID
-------  -----------  -----------  -----------
1        1            1            1
1        1            1            19
4        4            1            9
4        4            1            15
6        7            1            17


Here you go. From: http://www.toadworld.com/platforms/sql-server/w/wiki/10351.sql-server-agent-job-query-samples.aspx

SET  NOCOUNT ON

DECLARE @MaxLength   INT
SET @MaxLength   = 50

DECLARE @xp_results TABLE (
                       job_id uniqueidentifier NOT NULL,
                       last_run_date nvarchar (20) NOT NULL,
                       last_run_time nvarchar (20) NOT NULL,
                       next_run_date nvarchar (20) NOT NULL,
                       next_run_time nvarchar (20) NOT NULL,
                       next_run_schedule_id INT NOT NULL,
                       requested_to_run INT NOT NULL,
                       request_source INT NOT NULL,
                       request_source_id sysname
                             COLLATE database_default NULL,
                       running INT NOT NULL,
                       current_step INT NOT NULL,
                       current_retry_attempt INT NOT NULL,
                       job_state INT NOT NULL
                    )

DECLARE @job_owner   sysname

DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()

INSERT INTO @xp_results
   EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
   SET last_run_time    = right ('000000' + last_run_time, 6),
       next_run_time    = right ('000000' + next_run_time, 6)

SELECT j.name AS JobName,
       j.enabled AS Enabled,
       CASE x.running
          WHEN 1
          THEN
             'Running'
          ELSE
             CASE h.run_status
                WHEN 2 THEN 'Inactive'
                WHEN 4 THEN 'Inactive'
                ELSE 'Completed'
             END
       END
          AS CurrentStatus,
       coalesce (x.current_step, 0) AS CurrentStepNbr,
       CASE
          WHEN x.last_run_date > 0
          THEN
             convert (datetime,
                        substring (x.last_run_date, 1, 4)
                      + '-'
                      + substring (x.last_run_date, 5, 2)
                      + '-'
                      + substring (x.last_run_date, 7, 2)
                      + ' '
                      + substring (x.last_run_time, 1, 2)
                      + ':'
                      + substring (x.last_run_time, 3, 2)
                      + ':'
                      + substring (x.last_run_time, 5, 2)
                      + '.000',
                      121
             )
          ELSE
             NULL
       END
          AS LastRunTime,
       CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
       END
          AS LastRunOutcome,
       CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END
          AS LastRunDuration
  FROM          @xp_results x
             LEFT JOIN
                msdb.dbo.sysjobs j
             ON x.job_id = j.job_id
          LEFT OUTER JOIN
             msdb.dbo.syscategories c
          ON j.category_id = c.category_id
       LEFT OUTER JOIN
          msdb.dbo.sysjobhistory h
       ON     x.job_id = h.job_id
          AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time
          AND h.step_id = 0


To solve this problem, I did the following (+1 for the question BTW - it was an interesting challenge and I learnt a lot by going through it).
The code below can be found on the fiddle here (Microsoft SQL Server 2017 Linux - works on all versions on dbfiddle going back to 2014) :
Created a table:
CREATE TABLE payment (CUST_ID int, LOAN_ID int, PMNT_ID int, PMNT_AMT float,  PMNT_DT date,   LOAN_AMT float,  LOAN_FUND_DT date,  PMNT_RUN_TOT int);

Populated the table:
INSERT INTO payment VALUES (1155,      215,         100,         100.00,   '2015-02-15',           300.00,        '2015-01-15' ,       100.00);
INSERT INTO payment VALUES (1155,      215,         101,          50.00,   '2015-03-15',           300.00,        '2015-01-15' ,       150.00);
INSERT INTO payment VALUES (1155,      215,         102,          75.00,   '2015-04-15',           300.00,        '2015-01-15' ,       225.00);
INSERT INTO payment VALUES (1155,      215,         103,          75.00,   '2015-05-15',           300.00,        '2015-01-15' ,       300.00);

INSERT INTO payment VALUES (1156,      223,         104,          30.00,   '2015-02-15',           390.00,        '2015-01-15' ,        30.00);
INSERT INTO payment VALUES (1156,      223,         105,          30.00,   '2015-03-15',           390.00,        '2015-01-15' ,        60.00);
INSERT INTO payment VALUES (1156,      223,         106,         300.00,   '2015-04-15',           390.00,        '2015-01-15' ,       360.00);
INSERT INTO payment VALUES (1156,      223,         107,          30.00,   '2015-07-27',           390.00,        '2015-01-15' ,       390.00);

And then I ran the following query (don't worry about the overkill/repetition on the PARTITION BYs - I was trying to get to grips with Window functions).
WITH tab1 AS
(
  SELECT n.cust_id, n.loan_id, n.pmnt_id, pmnt_dt, pmnt_amt,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id) AS loans,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.pmnt_id) AS payments,
  SUM(n.pmnt_amt) OVER () AS grand_total,
  SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id) AS payment_per_loan,
  SUM(n.pmnt_amt) OVER (ORDER BY loan_id) AS loans_cumulative,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.loan_id) loan_amount,
  (SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id)) -
  (SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id)) AS remainder_per_loan
  FROM payment AS n
  ORDER BY cust_id, n.loan_id, n.pmnt_id, n.pmnt_dt
)
SELECT cust_id, loan_id, loan_amount, pmnt_dt AS paid_off_date FROM tab1
WHERE remainder_per_loan = 0

And the result is (with the limited subset of data that I used):
cust_id|loan_id|loan_amount|paid_off_date
----------------------------------------------------
  1155|     215|        300|     2015-05-15
  1156|     223|        390|     2015-07-27


SQL Server 2019 Always On using Distributed Network Name

Something you were trying to do in May 2020 is doable now. In SQL Server 2019 CU8 (released 01/10/2020) they have added support for Distributed Network Name.
Have a look at this KB4578579 - Improvement: Availability Group listener without the load balancer in SQL Server 2019
Also for instructions on how to configure dnn take a look at this atricle Configure a DNN for failover cluster instance


Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

The MEMORY storage engine can either be a blessing or a curse depending on

what you intend to store
how often you plan to perform DML
how much RAM you leaving for the

Database caches (MyISAM Key Cache, InnoDB Buffer Pool)
OS caches
OS operation

The MEMORY storage engine

uses full table locking for INSERTs, UPDATEs, and DELETEs
Cannot perform concurrent INSERTs
uses the hash indexes instead of BTREE indexes by default
can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
has no transaction support
Single row queries are just great against MEMORY tables, especially using HASH indexes -- Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)

Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.


What is the best practice for free space for a SQL server database drive?

I'm trying to determine the best practice for sizing a single database on a 120Gb data drive that will contain the LDF and MDF files.

Best practice for sizing is always to do capacity planning - how fast does your data grow now?  How fast will it grow in the next 1-5 years?

Add on some contingency, too.

Set your data file large enough to accomodate that now, and put a calendar entry on your calendar to revisit your capacity planning annually (and check your RAID controller for a bad battery or a bad drive, too).

Set your T-log file likewise, but first read Kimberly Tripp's Transaction Log VLFs – too many or too few? article.

Then read Database File Initialization, particularly the Instant File Initialization section and the Group Policy Perform Volume Maintenance requirement.

Note that index maintenance can use up a lot of space in the log.

Then you can set autogrow on both to fixed increments of reasonable size based on how long you can afford to wait for the growth, as well as keeping VLF sizes in check.

Now set a calendar entry or automated process to check on free space regularly - weekly or monthly, perhaps.


Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

Looking at the execution plan, you can see that every query is done as a different query. Again, if you want to see if those query are using parallelism, you will see it in the execution plan.

If each of the query can go parallel, then they will.

Running this query agains StackOverFlow2013 DB:

  create table #tbl (tbl varchar(30));
  insert into #tbl values ('Comments'),('Comments')

  DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

  DROP TABLE IF EXISTS #Results
  SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue
  INTO #Results

  SELECT @DynamicSql = @DynamicSql +
  'select top 5000 * from [dbo].['+tbl+'] where text like ''%err%'''
  FROM #tbl t

  EXEC sp_ExecuteSql @DynamicSql

  SELECT *FROM #Results

  drop table #Results
  drop table #tbl

you get an execution plan that does both select with parallelism.


I need to audit all actions for a user on all databases - not sure best way

Colleen Morrow's got some great resources regarding SQL Server Audit, including deployable code via PowerShell.  Her articles are well worth a read and I've deployed her solutions in my environment to great fanfare:

http://colleenmorrow.com/tag/auditing/


Performance schema: 'stage/sql/Sending data'

Sadly, stage/sql/Sending data (something that you will see on SHOW PROCESSLIST as Sending data) does not tell us much about the query internals.

  The thread is reading and processing rows for a SELECT statement...

Is like saying nothing, why is it different in both cases?

Your effort is not in vain, you have proved what is NOT affecting the query- permissions, optimization, opening tables, locking, logging and other support bits. There is something wrong with the query itself, likely either a different query plan or a configuration/hardware issue.

In order to continue answering, please provide the output of the following commands on both servers:

EXPLAIN SELECT ... [REST OF YOUR QUERY] \G

and

FLUSH STATUS;
SELECT ... [REST OF YOUR QUERY, do not show the actual results, we do not care];
SHOW STATUS like 'Hand%';

Those are 2 commands that will tell us more about query execution before checking other more complex possibilities. It is the most common cause so there is a high chance of getting something interesting out of it. Also please show us the table structure SHOW CREATE TABLE (in both servers) for reference.


Why is a 'Select distinct limit 100' using tmp tables?

There are multiple ways that SELECT DISTINCT col FROM ... LIMIT 100 can be performed:

Case 1:  Create a temp table with all rows of col, sort it, then peel off the first 100 (de-dupping as it goes).

Case 2:  Build a hash in memory, read col putting it into the hash, stop after 100.  (Fast.)

Case 3 (if there is INDEX(col)):  Leapfrog through the index.  This effectively takes 100 BTree dives into the index.  (Fast, even if few distinct values.)

Which will it do?  It depends on the version, the data, the existence of an index, etc.  Note that 5.1 is antique; you should plan to upgrade to 5.5, then 5.6, then 5.7.  (8.0 is also available, but not "GA".)  The Optimizer has changed a couple of times in this area, so I cannot say what your version will do.

If you are going to do this for all columns, consider, instead,

SELECT GROUP_CONCAT(DISTINCT col1),
       GROUP_CONCAT(DISTINCT col2),
       ...
    FROM TABLE;

(No LIMIT.)  This won't give you exactly 100 of each, and it will probably give you only the alphabetically/numerically first several (1024 bytes' worth) of each list, but maybe that is good enough.  It is probably faster than column-by-column.

See also ANALYSE() (spelled that way).


Monitoring CDC (Change Data Capture) performance

We had a similar requirements like yours to have CDC enabled currently for one of ur database around 5 TB in size.

There are various parameters that you might want to consider when enabling CDC for capture job. Ones like maxtrans and maxscans really matter for what you want to extract regarding the changes happening in log file for tables configured for CDC

Please read here for more on parameters I am discussing above. Also it list dmvs to monitor the scan session for logs and latencies.

Also keep a monitor on transaction log file for both the user database involved in cdc along with growth of tempdb growing unusual. Might not be a scenario in your case but just telling you from our experiences.

I am not able to recall but I believe we browsed somewhere for the event that can monitor performance for CDC using xtended events. Apart from this you need to have a normal DBA monitoring of that sql server which we dbas have to do to avoid any last minute surprises.


Does Azure SQL Data Sync support MySQL or SQLExpress?

Azure SQL Data Sync does not support MySQL for synchronization but it does support SQL Server Express as you can read here.

One of the troubleshooting steps is to exit local sync agent, then open the services.msc applet in Control Panel, make sure SQL Data Sync service is started, if the service is started please restart the service. If the service cannot start change the service account to a local Administrator of the computer. Reopen the local sync agent.

Try also to disable any security software (Avast, Windows Firewall). Start SQL Server Browser Service on the computer.

Another possible reason, the agent key that you have entered is invalid. The agent key being submitted is not the most recent; you may have regenerated a newer key on Azure Sync portal. Try regenerating on Azure portal and resubmitting.


Check it here: http://rextester.com/MGKS16637

I'm getting max(Id) and difference between sum(fee) and total price from items table. If this value is <> 0, then update the item with id=max(id).

update items
       join (select max(i.id) mid, o.totalPrice - sum(i.fee) as diff
           from items i
           inner join orders o on i.order_Id = o.orderId
           group by i.order_id
           order by i.order_id, i.id) v
       on items.id = v.mid
set items.fee = items.fee + v.diff
where v.diff <> 0;


Because nothing is written to log file, the first (and only) problem is (probably) ownership of log file. Usermongodb don't have access to that file. sudo chown -R mongodb: /var/log/mongodb should fix that.

What comes to the original question of how to run mongod at the command line and see what is happening, you have two choices. First is comment out (from config file) line what orders mongod to fork itself to background. Second possibility is check from config file,  what parameters you need (like dbPath) and then replace that --config with those command line parameters. You can see list of command line parameters with mongod --help command


I suggest you go with the normalized data model and use JOINS. In practicality, sometimes there is no need to drill down to atomic level, you can stop the level of aggregation based on the requirement. Also, if the historical data is important for you, you could consider the historical table instead of updating the meta data information. For this you can refer, Slowly Changing Dimensions (SCD).

If calculation involved complexities, understand the relationship between respective entities and capture them in table. This you can achieve by creating stored procedures or functions.

I hope, this answer will help you to get start. Thank you.


UPDATE query periodically is slower by factor of 5000x

Since your server is under heavy load try to increase its mysql thread concurrency.

Try to set:

innodb_thread_concurrency at 0 (infinite)

innodb_read_io_threads at 64 (Maximum)

innodb_write_io_threads at 64 (Maximum)

Sources:

https://dba.stackexchange.com/a/2948/33029

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency


mysql query gets slower with increasing page number

when you're using the "limit 48 offset 7776" mysql need to scan all rows(48+7776), along with offset number increase, mysql need to scan more and more rows for "Next page"
To improve this, try:

select ... from ... where id > 7776 limit 48

or try:

select ... from ... where id between 7776 and 7824


It is not completely clear what your after with your question. I assume you want to know if and importantly when an alter index .. coalesce was performed.

I think without (an in other answers already mentioned auditing setup) it is impossible to get such a date (although I would want to know if someone knows better).

That said, the effects of an alter index coalesce can be made visible with a analyze index .. compute statistics.

create table ix_test (
  a number
);

create unique index ix_test_ix on ix_test(a);

insert into ix_test
select
  rownum
from
  dba_objects, dba_objects, dba_objects
where
  rownum < 1e7;

commit;

Now that the table is filled, the index is analyzed and some index related values extracted:

analyze index ix_test_ix compute statistics;

select
  LEAF_BLOCKS,
  AVG_LEAF_BLOCKS_PER_KEY,
  CLUSTERING_FACTOR,
  SAMPLE_SIZE
from
  user_indexes
where
  index_name = 'IX_TEST_IX';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          2                   19875                 1       15152

All but one row deleted and index analyzed (again):

delete from ix_test where a != 500000;
analyze index ix_test_ix compute statistics;

The query above returns the same values.

Coalescing the index:

alter index ix_test_ix coalesce;
analyze index ix_test_ix compute statistics;

The query above now returns

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          1                       1                 1           1

So, there is a possibility to find out if there was a coalescing done, but it's not straight forward and probably useless, depending on your context.


You can expand the array using jsonb_array_elements_text() with a lateral join in this way:

select
    users.id,
    role.name
from
    users
join lateral
    jsonb_array_elements_text(data->'role') r on true
left join
    role
    on role.id = r.value::int

db<>fiddle here


ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

First of all do not use database links for larger transactions. This can cause too many problems with blocking sessions. Another hint is to keep transactions in a good size (not to small, not to big).

If your table is not partitioned then write a piece of code to remove just 1000 rows, commit and delete the next 1000 rows.

You said something about alter table <table_name> truncate partition <partition_name>; Is this table partitioned?

Why do you want to delete data from a partitioned table on daily basis? Is the table partitioned the same way?

To archive an entire partition you best exchange it with an empty table of the same structure. Afterwards you can export/backup the table and then drop it. If your partition is in a dedicated tablespace you can also mark it as read only and skip it in the daily backup (enable backup optimization in RMAN).


How to optimize a SQL Server query to fetch the results faster

The first field in your index is SETUPID but this does not appear in the query, rendering the index unable to support this query.

Which of these fields is the best "discriminator" - the one that filters the rows down the quickest?  That should appear first in your index.

I would suggest that an Index on just CUSTOMER_ID might be a better place to start.


Reset the numbering at the tabs of the Microsoft SQL Server Management Studio

The numbers represent the process id of the session associated with that tab - as every time you open a new tab it opens a new connection to SQL Server for you. They are not necessarily sequential but they can be. If you run the following command you should be able to see an entry for each of the tabs/connections that you have open:

select * from sys.dm_exec_sessions

I hope this helps you.


How can I prefer a specific MongoDB replica set member be elected as primary?

You can influence the outcome of elections by adjusting the priority for specific members. The default priority of 1 can be changed to any floating point value between 0 and 1000 (where 0 indicates a non-electable member).

For your use case, you might want to set your preferred primary to have a priority of 10. The priority value doesn't have any special significance aside from numerical ordering for all member priorities. For example: priority values of 1.01, 10, or 1000 are all higher than the default priority of 1.

Note: using priorities may result in extra elections to ensure a preferred member becomes primary when eligible. Typically it is best to think about replica set members as peers with similar resources so that any member can take on the role of primary. Unless there is a strong reason to have a specific member as primary (for example, a replica set distributed across multiple data centres), I would avoid using priorities or consider having a few preferred members with equal priorities in order to minimise elections.

An example sequence with replica-1 having a higher priority (10) than all other members with default priority (1):

replica-1 is current primary.
replica-1 is shutdown in order to be upgraded.
replica-2 is elected as the new primary.
replica-1 is restarted and resumes syncing in order to catch up with the current primary.
replica-1 catches up with the current primary and triggers an election so it can become primary.  replica-2 will drop all current connections when it steps down to a secondary.

Setting replica-1 and  replica-2 to the same priority would avoid the extra election.


Can SQL Server be forced to use a cached plan for executing a SP or SQL script?

According to these comments

  Can we somehow force SQL Server to use a cached plan for executing a
  SP or sql query?

  I am unable to get the same plan while executing the SP or the
  internal query manually so cannot really do a like for like comparison
  of any performance improvement that I make to the query / SP.

It looks like you only want to run the query with the plan in the cache that has the high row estimates for testing purposes.

Altough you should not implement this in production, you could run it like this:

Also note while you can force the estimated execution plan using below hints, values such as the estimated rowcounts, estimated total subtreecost, ... will be recalculated. Even when forcing the cached plan. What remains the same is the general shape of the plan, such as operators used and indexes touched.

1) Find the estimated plan

You could search for the estimated execution plan in the cache by using the query in this post on Searching the SQL Server Plan Cache

2) Display the plan XML

After getting the plan, display the XML:

3) Replace the apostrophes

Replace all 's with ''s, for example with find and replace:

4) Run the query with the USE PLAN hint

Rerun the query with

OPTION(USE PLAN
N'
Execution plan XML here
');

Make sure that there are no spaces between USE PLAN N' and the execution plan XML.

Correct use:

OPTION(USE PLAN
N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
...

Incorrect use :

OPTION(USE PLAN
N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
...

You would have to create a temporary stored procedure with the OPTION(USE PLAN N'') or alter the existing stored procedure or use a plan guide to add these hints when using stored procedures.

More information on query hints can be found here.

Looking into why these estimates are so high seems like more of a necessity.
We could help with that, but we would need the (actual) execution plan.

You could upload the plan to PasteThePlan and add it to the question, or ask a new question with the uploaded plan.


You can't download a snapshot from RDS you have to use a tool like pg_dump

This has already been answered multiple times on regular Stack Overflow:
https://stackoverflow.com/questions/14916899/download-rds-snapshot


Close button action when manually running a job

The "close" button simply closes the dialog; it does not stop the running job.

You can use the following stored procedure to see the current runtime state of a given job:

EXEC msdb.dbo.sp_help_jobactivity @job_name = '<job name here>';

To prove the "close" button does not actually stop a running job, I've created a test job:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_page_operator_name=N'<Operator Name>', @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'WAITFOR DELAY ''00:00:15'';',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The job takes 15 seconds to complete, by virtue of the WAITFOR DELAY statement.  It does nothing else.

Open a new query window, and add the following T-SQL to it:

EXEC msdb.dbo.sp_help_jobactivity @job_name = 'TestJob';

Run the job via the SSMS user interface, and immediately hit the "Close" button.
Run the job activity query in step 1 by hitting the F5 key or clicking the "Execute" button.

The status of the job will be shown, something like:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════╦═════════════════════════╦════════════════╦═════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║ stop_execution_date ║ next_scheduled_run_date ║ job_history_id ║ message ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════╬═════════════════════════╬════════════════╬═════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║ NULL                  ║ NULL                    ║ NULL                ║ NULL                    ║ NULL           ║ NULL    ║ NULL       ║ NULL                ║ NULL                ║ NULL              ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════╩═════════════════════════╩════════════════╩═════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

In the above output, the stop_execution_date column is NULL, indicating the job is still running.  If you wait 15 seconds for the job to complete, and re-run step 1, you'll see the output is changed to reflect the status of the job, as in:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════════════════════════════════════════════════════════════════════════════════════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║   stop_execution_date   ║ next_scheduled_run_date ║ job_history_id ║                                            message                                            ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════════════════════════════════════════════════════════════════════════════════════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║                     1 ║ 2018-01-31 08:51:13.000 ║ 2018-01-31 08:51:28.000 ║ NULL                    ║        3514780 ║ The job succeeded.  The Job was invoked by User me.  The last step to run was step 1 (Step1). ║          1 ║                   0 ║                   0 ║                 0 ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════════════════════════════════════════════════════════════════════════════════════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

The dbo.sp_help_jobactivity stored procedure references the dbo.sysjobactivity table.  Microsoft docs for that table shows they use the following construct to determine the current status of a job:

CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'


Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

Streaming replication fires the triggers on the master only.  The slaves never see the actual table writes, but rather re-run the recovery logs from the master.  Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level.  No triggers are necessary.  Triggers may be possible with Slony or Bucardo but I am not 100% sure.  My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony.  In 9.2 streaming replication will allow for cascading.

Hope this helps.


The engine will only be able to make use of one index per table so while the other indexes may help you in the future with other queries, their benefit will be limited in assisting you with this particular query.

Given what we know about your query and limited knowledge of the table and the insert pattern, I would suggest the following.

Table A - (Date, Type)
Table B - (merk, ID, Code)
Table C - (Uniq)

Of course, if you were able to limit the result set columns (getting rid of the SELECT *) then further columns in the index could be of benefit.

These indexes above will allow the engine to seek quickly and get quickly to the rows that it wants.

I do have one question about the "merk" on Table B.  If that is something like a status that changes then I would not index on it, leaving just an index on ID and Code.  If the value of the field changes frequently then it is usually a poor index candidate as it will cause much fragmentation.


Query performance degrades with time and use

Queries can start to slow down over time for a few reasons and you rebuilding the indexes can be fixing the problem a few ways. I'll share some of the more common reasons in my experience but there could be other causes as well. My guess is you are suffering from one of these issues.. I've also asked some questions as a comment to your question to see if we can get more details. But a few thoughts:

Statistics Getting Stale SQL Server maintains column and index statistics. These essentially tell the Query Optimizer how your data is distributed. This information is critical to the optimizer in choosing the right access method for data (Seek vs Scan) and then choosing the join method being used. If you have auto update statistics enabled (default setting in SQL.. At the database level) these get recomputed, but only when "enough" data changes. So if you have some inserts into your table but never manually update statistics and the inserts/updates are not enough to trigger an auto stats update you could be suffering from poor plans for your data distribution... Rebuilding your indexes also recomputes your index statistics I would create a job to manually update statistics on a regular basis, this is a best practice anyway - and the next time this happens try and just run sp_updatestats in your database and see if you notice a difference

Query plan issues You could be suffering from parameter sniffing - basically the first time a query runs one value is passed in - the query gets optimized for that value. When you next run it with a different value that would benefit from a different query plan, it suffers with the original query plan resulting in a slow query. When things run slow for the app - are they also slow if you run the same query in SQL Server Management Studio? If it is fast in SSMS but slow in the app - that can be a good sign pointing towards parameter sniffing. If it is consistently slow across the board over time for all queries and regardless of parameters, then I wouldn't look here. This article talks quite a bit about parameter sniffing.

Not enough memory/too many ad hoc plans It sounds like you are sending ad hoc SQL to SQL Server. This can bloat your plan cache sometimes, especially if you have a separate plan for each execution of a query. Depending on the memory on your server, this can also lead to the issue. How much memory is on your server? Check out this link on the problem with single use plans. You don't have a lot of great solutions in SQL Server 2005 for this problem, if you have it. If you can recreate this problem in a non-prod environment, I would suggest running DBCC FREEPROCCACHE in your non-prod environment if this happens again. Please note! This is an instance wide setting, if you do this on production - any stored query plans in cache for any database will no longer be there. It means you have to "pay" for compilations again. If you have high concurrency and a busy system, this could prove to cause issues. If this is the only real database and you are suffering from performance issues anyway, it doesn't hurt to try this in production.. If you have other Databases and just want to do it for this database, this blog post explains how to approach a clear for just one DB.

Index Fragmentation - It is possible that index fragmentation is the actual issue here, but I'm surprised it gets so bad so quick. If your tables are clustered on a key that causes fragmentation quickly and you have a lot of inserts, this could be the case. It would be made much worse if you were underpowered in terms of memory and disk IO. Setting up a job to rebuild/reorganize your indexes on a regular basis would be good. Based on your answers to some questions in the comments above there may be other things to do to minimize the impact of this.


What MariaDB storage engine should i use?

Your only real choice here is between InnoDB and Percona's XtraDB engines - see this page.

Coincidentally, I wrote an answer to Change all table ENGINE at once from "MyISAM" to "InnoDB" in MySql with single query this morning on how to convert from MyISAM to InnoDB which might be of interest to you? There are very good reasons for not going with anything non-transactional!

  Any particular reason you recommend InnoDB/XtraDB since MariaDB blog recommend Aria instead?

Yes - for their transactional capabilities - you get DRI (Declarative Referential Integrity) thrown in. Enforcing DRI app-side is a disaster waiting to happen - whereas if you use the RDBMS, you have a system that's been tested by literally millions of people. If you "roll-your-own", you have to rewrite it every time you want to write a new app for that particular schema - and if your data is important, this will happen! I would urge you to use either InnoDB or XtraDB but if you do insist on an NTE, then go with Aria! -  Vérace


Matthew,

I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance.

280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried.

The above is because when SQL Server creates a new table, it doesn't know how big will that table be. Data is stored in pages of 8 kb and an extent is a collection of 8 contiguous pages.
There are 2 types of extents, mixed and uniform extents. Uniform extent means all the 8 pages will be used by the same object (say TableA) and mixed extent means that this extent can be used by as many as 8 different tables. Since SQL Server doesn't know how big the table will be, it will start with single page allocations until the table reaches 3 extents and there after it uses uniform extents. This is the reason, fragmentation is high in small tables and but you shouldn't be worried abt it.

Instead of looking how big is the database, you should look at how much of it is empty. I am NOT sure how this affects the billing though. HTH


SQL Server Update Statement joining on index column

SQL server may choose to perform an in-place update or an insert and a delete depending on many factors and you shouldn’t worry about it.
Since the keys are not being updated I would leave them out of the SET clause to help the optimizer make the best decision.

If you are using SQL Server 2008 or later, look at the MERGE statement which can replace both operations:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

HTH


Postgres advisory lock when key doesn't fit

The usual way of solving such issues is to use a hash function that would produce a (nearly-)unique value that fits your purposes - in this case a pair of integers or a single bigint.

Unfortunately, to my knowledge there is no such built-in hash function in PostgreSQL.  You can adopt different solutions though:

use the built-in md5() and truncate it to bigint
find a 3rd party solution, like hash64 (warning: I haven't tried this yet, so no idea if it is a good hash function or not)


I see your Info Center link goes to LUW 9.7, and you mention that you've programmed in Java, but most of the experience I have with binding is with DB2 on the Mainframe with COBOL.  So, you may need to adapt the explanation a bit (but generally, the concepts should be the same).

I believe that binding is only relevant when you are compiling programs that include embedded SQL which is precompiled (statically bound SQL).  If, for example, you're using JDBC, you aren't required to run a BIND.  The JDBC driver will PREPARE the statement dynamically.

When you run a program through a DB2 pre-compiler, PRECOMPILE runs through your program, and if it finds any embedded SQL (in COBOL, these are statement blocks that go from EXEC SQL to END-EXEC.), it carefully rips the SQL out, and replaces it with a call to the COBOL-DB2 interface.  After this, there are two outputs of the PRECOMPILE, the COBOL source that has had all the embedded SQL removed (A from now on), and a DBRM that contains all the SQL that was removed (B).

Precompile does do some basic syntax checking, but be aware that the checks are only based on your table declarations within the program.  It doesn't attach to DB2 to verify these!

These two files are completely separate, and when you run the COBOL program, it has to find an A and a B that were generated at the same time.

At this point, A is compiled and linked with the standard COBOL compiler into a load module and placed in a load library to be used later.

However, there is still a lot of work to be done with B, the DBRM.  This is where BIND comes in.  BIND is sort of like a compiler for the embedded SQL code, and the output of the "compile" is a package.

In order to BIND the SQL into an executable "package", the BIND process attaches to DB2 and does a few things:

Verifies that the current AuthID is authorized to perform a bind.
Checks the syntax of your SQL, with help from the data in the DB2 catalog.
Finally, and most importantly, the bind will optimize your SQL

During the last step, all of your SQL is run through the Optimizer, which takes into account all of the statistics and various paths that the DB2 engine could take to fetch your data.  It then chooses the path it came up with that has the lowest cost associated (with newer versions of DB2 [DB2 10 for z/OS], it may decide to take a "higher cost", but "lower risk" path).  Once the path is selected, it is compiled and becomes a package, which is stored in the catalog (you can see all of your current packages with SELECT * FROM SYSIBM.SYSPACKAGE (z/OS)).

Finally, there is a last piece that allows our programs to reunite with their packages, the PLAN.  You create a plan by doing another BIND (BIND PLAN).  A plan is a collection of packages that the program is allowed to look through to find the package that shares the same name.  With COBOL, you specify which plan the program should search in in your JCL.

In short, compiled code goes through these steps to generate a usable BIND PLAN:

Precompile -> Creates a DBRM (with C[++], the precompiler outputs the precompiled SQL to an HFS file, which can be sent through the command-line bind program) -> the DBRM is optimized and a set of access paths (a package) is created -> The package is added to a BIND PLAN, which is a group of packages that allow you to create a "search path" for your programs to look through.

Since these programs are statically bound, if your table statistics change drastically, then the access path the optimizer chose at bind-time might not be the best path anymore, and re-binding will allow it to re-evaluate the SQL and perhaps choose a better path.

Edit (update for comment): If you are using the command line processor, you can pass in either a single bind package (.bnd), or a list of bind filenames (.lst).  If you pass in a list, the filename has to be prepended with a @ (e.g. /path/to/@packages.lst).  Inside the .lst file, you can either put each package on an individual line, or you can separate them with +:

package1.bnd
package2.bnd
package3.bnd+package4.bnd+package5.bnd


Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC

I'm not sure what you mean by

  It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.


I sounds like you are doing partitioning based on a virtual column.

virtual_column_based_partitioning


Before using hints, I would check the indexing on the table you are updating; it's probable you want a clustered index on MachineID; without it, an update may be forced to lock the entire table to update one row, which may be what you are seeing. Also examine any triggers on the table, make sure they are optimized.

Post your table schema, or execution plan for the update if you can.


How to approach copying a schema for testing?

The 12c Multitenant option is a nice tool for this.

Create a "master" pluggable database that is your starting point, and you can create other pluggable databases by cloning this PDB.

When you clone a PDB, you have the option to clone the PDB as a "snapshot copy". This does not actually copy the files, but uses storage snapshots, and that is what makes this feature great. For example, cloning a 111 GB database took 20 seconds in this example: Snapshot clone of a PDB using ACFS

When I experimented with it, smaller databases took even less time to be cloned. When you do not need the database anymore, you can just simply drop it.

Also you are not limited to 1 schema, because you can create many clones and use them at the same time concurrently, up to 252 PDBs.

Note that, the Multitenant option is an extra cost option for Enterprise Edtition databases. So you need the proper license to use it.

SNAPSHOT COPY


Start by understanding the plan that the query optimizer generated by your query. In your question you say that the query returns around 590 rows but the query plan included only returns 18 rows. Did you attach the correct plan? I will walk through it anyway. Reading from right to left:

Do a full table scan of roster and get back 18 rows. This result set is used as the outer part of all of the following nested loop joins. For each row in the outer result set:

Do a full scan on the ia table
Do a full scan on the inb table
Do a full scan on the ripcord table
Do a full scan on the YRTR table
Do a full scan on the students table
Do a full scan on the potstudents table

At some point the number of rows gets bumped up to 19. The GROUP BY is implemented as the SORT with a node id of 4. The DISTINCT is implemented as a Sort (Distinct Sort) with a node id of 0.

As the programmer you do not have direct control over the query plan but there's a lot that you can do to influence it. You said that the query execution time is too slow. Does the above plan sound efficient to you? Is it what you would do if you could choose the query optimizer's actions? You're doing around 140 table scans just to return 18 rows. For a very small number of rows this might be okay but it sounds like you have more data in production.

One way to optimize a query is to reduce the IO requirements of that query. Here you are doing lots of table scans so that should be straightforward to implement. Create indexes so that the query optimizer can get the relevant data more efficiently. Even if you are referencing views you can create indexes on the tables used by the view to improve performance. To give you an example, here is the predicate for the table access on fc:

[Test].[dbo].[roster].[Teacher] as [rost].[Teacher]=[Test].[dbo].[fc].[Teacher]

If you create an index on the Teacher column of fc that may result in a different, and possibly more efficient, table access method for that table. Optionally, you could add all of the columns used by the Teacher table as INCLUDE columns.

Looking at just your code, you should be aware of the impact of using local variables in your query. The query optimizer will not know the values of those local variables when creating a query plan. It will do default cardinality estimates based on hard-coded rules. For some queries, adding an OPTION (RECOMPILE) hint or replacing the local variables with hard-coded values can result in a much better query plan because the query optimizer knows the values of the variables before creating the plan.

Also, do not haphazardly add DISTINCT and GROUP BY to your queries. It looks like the query optimizer optimized away some of them, but most of the time if you have both GROUP BY and DISTINCT in the same query you're doing something wrong. It is not clear how much of a performance impact making that change would have for this query.


You state that "Connections, ports, user accounts and credentials are all fine.", but I have my doubts.

Mentioning the exact error from the documentation:

  [The error] occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host

Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:

SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';

If that doesn't return a result, you'll need to add that user. If it does return a result, can you add that to your question?


MSSQL - Query efficiency to many filters

The basic form you have is correct, though you want to avoid concatenating parameters into the string the way you have. See:

Revisiting catch-all queries
#BackToBasics : An Updated "Kitchen Sink" Example

The efficiency part will depend ultimately on the supporting indexes you have for your queries, along with other local factors, like hardware, blocking, rows returned, etc.


Migrating SQL Server 2000 to 2008/14 - Stored procedures compatibility?

You should check breaking changes.

you can use upgrade advisor or

dbatools - Test-DbaMigrationConstraint

  This function only validate SQL Server 2008 versions or higher.

I have written an extensive list of things you should check when you migrate from 2000 to higher versions


Why do MySQL MyISAM table indexes go out of date?

This is only a guess but here it goes...
The only way I know that a PRIMARY KEY for a MyISAM table would be OK and all secondary indexes be out-of-date would be under this unique circumstance:
Running this
ALTER TABLE mytable DISABLE KEYS;

and forgetting to undo it with
ALTER TABLE mytable ENABLE KEYS;

When you disable keys, the PRIMARY KEY and all UNIQUE keys are unaffected. When you enable keys, all secondary indexes get rebuilt.
According to MySQL Documentation on ALTER TABLE:

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.
This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.
While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

SUGGESTION
First, make a copy of the table.
Try running
ALTER TABLE mytable ENABLE KEYS;

and see if it does anything. If not, run
REPAIR TABLE mytable;

Give it a Try !!!


Dropping multiple foreign keys of unknown name

One way is dump the result of below query into a Temp Table and execute resulted rows one by one.

CREATE TABLE #foregnkeys
    (
      ROWID INT IDENTITY(1, 1),
      SQLStmt VARCHAR(3000)
    )

DECLARE @count INT= 0,
    @SQLStmt VARCHAR(3000)

INSERT  INTO #foregnkeys
        SELECT  'alter table [' + sc.name + '].['
                + OBJECT_NAME(fk.parent_object_id, DB_ID('DBNAME'))
                + '] drop constraint [' + fk.name + ']'
        FROM    DBNAME.sys.foreign_keys fk
                INNER JOIN DBNAME.sys.tables tbl ON tbl.[object_id] = fk.parent_object_id
                INNER JOIN DBNAME.sys.schemas sc ON sc.[schema_id] = tbl.[schema_id]
        WHERE   tbl.name LIKE 'TA_%'
                OR tbl.name LIKE 'XTB_%'    --- AND of your query is converted to OR

SELECT  @count = ( SELECT MAX(rowid) FROM #foregnkeys )

WHILE ( @count > 0 )
    BEGIN

        SELECT  @SQLStmt = ( SELECT SQLStmt
                             FROM   #foregnkeys
                             WHERE  ROWID = @count
                           )

        PRINT ( Cast(@count as varchar)+'_ '+ @SQLStmt )
        EXEC ( @SQLStmt)

        SELECT  @count = @count - 1

    END

DROP TABLE #foregnkeys


Join two tables brings mismatch values MYSQL

Point 1 : INNER JOIN & JOIN  both are same thing.
Point 2 : No need to do an extra join on K2
You can use this :
INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
and
 K.exchange_pair = B.exchange_pair  ;


The syntax should be exp username/password@instance , please also check if the listener is running ps -ef|grep tns and if it has the service listed lsnrctl services (the error "ORA-12154: TNS:could not resolve the connect identifier specified" points out that there is a problem related to that). You can also use the new tool expdp which in my opinion is more easy to use. Ex: expdp sys/password schema=userID dumpfile=userID.dmp directory=data_pump_dir logfile=userID_dmp.log  . You can see where the data_pump_dir points to from sqlplus select * from dba_directories and even create your own directory CREATE DIRECTORY dmpdir AS '/opt/oracle'; and use it in the directory clause. But if you want to use exp, check the listener and also the syntax of your command.


Best design to reference multiple tables from single column?

I personally don't like to use a multi-table schema for this purpose.

It's hard to ensure integrity.
It's hard to maintain.
It's difficult to filter results.

I've set a dbfiddle sample.

My proposed table schema:

CREATE TABLE #Brands
(
BrandId int NOT NULL PRIMARY KEY,
BrandName nvarchar(100) NOT NULL
);

CREATE TABLE #Clothes
(
ClothesId int NOT NULL PRIMARY KEY,
ClothesName nvarchar(100) NOT NULL
);

-- Lookup table for known attributes
--
CREATE TABLE #Attributes
(
AttrId int NOT NULL PRIMARY KEY,
AttrName nvarchar(100) NOT NULL
);

-- holds common propeties, url, price, etc.
--
CREATE TABLE #BrandsClothes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
VievingUrl nvarchar(300) NOT NULL,
Price money NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId),
INDEX IX_BrandsClothes NONCLUSTERED (ClothesId, BrandId)
);

-- holds specific and unlimited attributes
--
CREATE TABLE #BCAttributes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
AttrId int NOT NULL REFERENCES #Attributes(AttrId),
AttrValue nvarchar(300) NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId, AttrId),
INDEX IX_BCAttributes NONCLUSTERED (ClothesId, BrandId, AttrId)
);

Let me insert some data:

INSERT INTO #Brands VALUES
(1, 'Brand1'), (2, 'Brand2');

INSERT INTO #Clothes VALUES
(1, 'Pants'), (2, 'T-Shirt');

INSERT INTO #Attributes VALUES
(1, 'Color'), (2, 'Size'), (3, 'Shape'), (4, 'Provider'), (0, 'Custom');

INSERT INTO #BrandsClothes VALUES
(1, 1, 'http://mysite.com?B=1&C=1', 123.99),
(1, 2, 'http://mysite.com?B=1&C=2', 110.99),
(2, 1, 'http://mysite.com?B=2&C=1', 75.99),
(2, 2, 'http://mysite.com?B=2&C=2', 85.99);

INSERT INTO #BCAttributes VALUES
(1, 1, 1, 'Blue, Red, White'),
(1, 1, 2, '32, 33, 34'),
(1, 2, 1, 'Pearl, Black widow'),
(1, 2, 2, 'M, L, XL'),
(2, 1, 4, 'Levis, G-Star, Armani'),
(2, 1, 3, 'Slim fit, Regular fit, Custom fit'),
(2, 2, 4, 'G-Star, Armani'),
(2, 2, 3, 'Slim fit, Regular fit'),
(2, 2, 0, '15% Discount');

If you need to fetch common attributes:

SELECT     b.BrandName, c.ClothesName, bc.VievingUrl, bc.Price
FROM       #BrandsClothes bc
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
ORDER BY   bc.BrandId, bc.ClothesId;

BrandName   ClothesName   VievingUrl                  Price
---------   -----------   -------------------------   ------
Brand1      Pants         http://mysite.com?B=1&C=1   123.99
Brand1      T-Shirt       http://mysite.com?B=1&C=2   110.99
Brand2      Pants         http://mysite.com?B=2&C=1    75.99
Brand2      T-Shirt       http://mysite.com?B=2&C=2    85.99

Or you can easily get Clothes by Brand:

  Give me all clothes of Brand2

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.ClothesId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ---------------------
T-Shirt       Brand1      Color      Pearl, Black widow
T-Shirt       Brand1      Size       M, L, XL
T-Shirt       Brand2      Custom     15% Discount
T-Shirt       Brand2      Shape      Slim fit, Regular fit
T-Shirt       Brand2      Provider   G-Star, Armani

But for me, one of the best of this schema is that you can filter by Attibutes:

  Give me all Clothes that has the attribute: Size

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ----------
Pants         Brand1      Size       32, 33, 34
T-Shirt       Brand1      Size       M, L, XL

Using a multi-table schema whatever of the previous queries will require to deal with an unlimited number of tables, or with XML or JSON fields.

Another option with this schema, is that you can define templates, for example, you could add a new table BrandAttrTemplates. Every time you add a new record you can use a trigger or a SP to generate a set of a predefined attributes for this Branch.

I'm sorry, I'd like to extend my explanations by I think it is more clear than my English.

Update

My current answer should works on no matter which RDBMS. According to your comments, if you need to filter attributes values I'd suggest small changes.

As far as MS-Sql doesn't allow arrays, I've set up a new sample mantaining same table schema, but changing AttrValue to an ARRAY field type.

In fact, using POSTGRES, you can take advantatge of this array using a GIN index.

(Let me say that @EvanCarrol has a good knowledge about Postgres, certainly better than me. But let me add my bit.)

CREATE TABLE BCAttributes
(
BrandId int NOT NULL REFERENCES Brands(BrandId),
ClothesId int NOT NULL REFERENCES Clothes(ClothesId),
AttrId int NOT NULL REFERENCES Attrib(AttrId),
AttrValue text[],
PRIMARY KEY (BrandId, ClothesId, AttrId)
);

CREATE INDEX ix_attributes on BCAttributes(ClothesId, BrandId, AttrId);
CREATE INDEX ix_gin_attributes on BCAttributes using GIN (AttrValue);

INSERT INTO BCAttributes VALUES
(1, 1, 1, '{Blue, Red, White}'),
(1, 1, 2, '{32, 33, 34}'),
(1, 2, 1, '{Pearl, Black widow}'),
(1, 2, 2, '{M, L, XL}'),
(2, 1, 4, '{Levis, G-Star, Armani}'),
(2, 1, 3, '{Slim fit, Regular fit, Custom fit}'),
(2, 2, 4, '{G-Star, Armani}'),
(2, 2, 3, '{Slim fit, Regular fit}'),
(2, 2, 0, '{15% Discount}');

Now, you can additionally query using individual attributes values like:

  Give me a list of all pants Size:33

AttribId = 2 AND ARRAY['33'] && bca.AttrValue

SELECT     c.ClothesName, b.BrandName, a.AttrName, array_to_string(bca.AttrValue, ', ')
FROM       BCAttributes bca
INNER JOIN BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN Attrib a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
AND        ARRAY['33'] && bca.AttrValue
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

This is the result:

clothes name | brand name | attribute | values
------------- ------------ ----------  ----------------
Pants          Brand1       Size        32, 33, 34


Join multiple tables without overhead on the DB Server using PDO

You need to rethink the schema...

Plan A:  Have temp, humid, etc, as columns in a single table.  If the date_time is the same from all the readings, then that does not need repeating.

Plan B:  Have those values in a JSON string in a single column in a single table.

Plan C:  Have a 3-column table:  (unitid, reading_name, value) and fetch the 4 values from 4 rows, but in a single SELECT.

In any case, Id is probably not needed.  The PRIMARY KEY can be (or start with) (unitid, date_time)


A partial index typically doesn't need the columns from the WHERE clause in the index as well. You should put other columns that you retrieve in the queries that make use of that index in the column. e.g. the PK or something else.
So if you e.g. frequently use this query:
select pk_column, other_column
from the_table
where num_col = 0
and bool_col = false;

Then it usually makes sense to create the index like this:
create index on the_table (pk_column, other_column)
where num_col = 0
and bool_col = false;

That way the index scan can be used to also get the values needed for the


How to refactor this subquery to be written only once?

I came up with this:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
with sq as (SELECT z, someId from T2 where T2.y=12)
select T1.*
from T1
where T1.x=p1
    and (
        (p2,T1.someId) in (select * from sq)
    or
        not exists (select * from sq where sq.someId=T1.someId)
        );
end;


is it possible to rollback only a partition/subpartition?

Unfortunately this is not possible in SQL Server - you can of course create a backup, or create snapshots on the server (HyperV for example) to accomplish this. Other than that, it's not possible to only do it for a table - the closest I'd consider is differential back/restore.


What happens to non clustered index when included column is updated using update statement?

Just as when you update a value in an index that's stored in leaf and non-leaf pages, all those pages get updated with the new value. Columns stored at the leaf level only through include are updated when you update values. It's possible for this to lead to spage splits too.


Identity seed increased when using IDENTITY_INSERT

This is a known behavior change that was reported on Connect and closed as "by design." You'll notice that the docs for 2008 & 2014 are slightly different, partially to reflect that the output of commands like DBCC CHECKIDENT() (2008 vs. 2014) better reflect reality.

The workaround you're using now is as good as any I can think of, since there is no magic trace flag to change the behavior of either version. It does seem odd to me that you prioritize your tests over the code. You should be making your tests so that they can interpret all code paths, not changing the code to match the one output the test expects. I have to wonder why the test even cares what surrogate ID value is generated by the database, because you shouldn't IMHO. These should be invisible to users and only used for referential integrity.


If you assume the following:

1 - Clients are actively using the SQL Server, sending inserts,
updates and deletes.
2 - Power loss causes the SQL Server to crash.

On startup, the server will do a brief recovery check on all of the databases and will rollback any transactions that were "in-flight" at that time.  Note that any committed transactions will be preserved, only the ones where SQL Server had not done an acknowledge back to the client.

Caveats:

If you have delayed durability turned on, then you may lose more than
that.
If you suffer disk/file corruption as a result of the power loss /
server interruption then that will also hurt you here.

When the database comes up, it will be in a transactionally consistent state, depending on the size of the transaction that was in flight, the server may have to roll back quite a bit of data, but it will be transactionally consistent.


Automatically rollback two-phase transactions on session disconnect

Anyway, it seems impossible after reading PostgreSQL source code. I settled for rolling back all the prepared transactions during application initialization.

Python code is:

def cleanup_prepared_xas(engine):
    conn = engine.connect()
    trs = [t[0] for t in conn.execute('select gid from pg_prepared_xacts')]
    conn.execute("rollback;")
    for t in trs:
        conn.rollback_prepared(t)
    conn.close()


Is it possible to remove useless GRANTS in MySQL

I don't see any option in pt-show-grants to do this kind of restriction. There are options for --only and --ignore specific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables.

I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason.

For what it's worth, I don't see any error when I try to grant to a non-existant table. I tested on Percona Server 5.6.16:

mysql> grant all on test.nonexist to 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

$ pt-show-grants
. . .
GRANT ALL PRIVILEGES ON `test`.`nonexist` TO 'user'@'%';

Can you tell me anything else about your version of MySQL and the specific error that it produces when you try to run that grant script?

Re your comment:

The difference between ALL PRIVILEGES and specific privileges seems to be deliberate, according to Bug #10406 Grant all command does not give error though table does not exist.

If you use GRANT CREATE, SELECT ... ON test.notexist TO 'user'@'%' then this works. As long as you include CREATE privilege, then it permits the grant to the non-existant table.

Anyway, that's interesting trivia, but it doesn't address your original question.

This has been requested before, but never implemented. It was recorded as a "blueprint" (like a feature request) here: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-show-grants-for-nonexistent-tables

Perhaps someone would like to implement a patch and contribute it to Percona Toolkit?


Does remove document automatically remove the index that reference this document?

Delete just put a flag on the document that is deleted. I am not sure but i think it doesn't reorganize the indexes at the same time, if that is your question, because reorganize the b-tree is an expensive operation.


How to debug a growing Redo Queue Size in the Availability Group Dashboard?

There is no way to "debug" this other than to open an incident with Microsoft for investigation.  They may be able to get a series of process dumps or enable some more verbose logging to see if there is a hung thread, but you may have to leave it in the current condition for quite a while, so that is the downside.

Based on my experiences with the same symptoms, stopping and starting the SQL Server service on the secondary will resolve the issue (unless you have an underlying I/O issue that is causing it).  It may take a while for the database to recover as it will have to play all of the transactions in the redo queue, and during this time, replication will be paused.  You can track progress of database recovery in the error log, and once the database is recover, replication will resume.


MongoDB Server has authorization schema version 3, but found a schema version 1 user

Problem solved

Upgrading to mongo shell 2.6.3 resolved the issue.


Event Triggers

There are event triggers to control DDLs, trapping for the event sql_drop.

Call CREATE EVENT TRIGGER. In the trigger function, check to see if the object being dropped is one of your important columns. If so, raise exception to roll-back transaction.

  The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.28). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.

Example:

drop table if exists t;
drop event trigger if exists etg;
drop function if exists fetg();

create function fetg() returns event_trigger language plpgsql as $$
begin
  if exists (
    select 1
    from pg_event_trigger_dropped_objects() as t
    where
      t.object_type = 'table column' and
      t.object_identity like any(array['%.t.y', 'public.tt.zz']))
  then
    raise exception 'Columns t.y and public.tt.zz are important!';
  end if;
end $$;

create event trigger etg on sql_drop execute procedure fetg();

create table t(x int, y int);
alter table t drop column y;

More docs:
Events
Functions


Mysql Or MariaDb for high traffic server

Here is my opinion. As long as you are going to use innodb engine it doesn't matter which one are you in.
Provided Maria gives you the option of multiple writes to master-master architecture using Galera-cache. But as your TPS goes beyond a particular point, you might face issues in getting writes paused due to flow-control. Hence you may use innodb engine let there be 2Nodes using MariaDB using GaleraCache Cluster or simple Master&Slave. But have your applications writing to one node only either case.
For my.cnf you may use percona tool wizard to generate as per your requirement.

Generate data as per expected prod including right data using datatypes. To an expected 2Yr down the line. In your case lets say
1Million records for one year lets consider 2Million records to
2years. Ensure data is random not same for all records.

Lets say you have above data generated and sizing is placed for right indexes and data. Check the data dir size, I would recommend you
to have data size to be your innodb_buffer_pool_size if it is
affordable.

Memory - innodb_buffer_pool_size(as per data) + 30% of IBPS . I.E.
120GB + 36GB.
CPU - As per user parallel connections and type of queries involved.
But this can go as per assumption. If it is 156GB RAM lets start with
1core*8CPU, considering 100Connections Per Second.
Disk IO - NVMe,SSD which ever best fit as per your Infra.
Network - Set maximum network bandwidth for DB Servers.
Have your applications in-place to throttle MAX-TPS for read/write,
ensure this MAX-TPS value helps number of customers using at peak-hrs
projected by business team.

Initially you may do a perf test with MAX-TPS read/write with right indexes in place and queries not going more than 30ms.

Now make a note of latency on app if it 50ms or whatever and see if business and apps team are ok with it. If not try to tweak if problems
are at query end or app end. After all enough variables tweaking (you
need to drill DBA here) if things don't satisfy the latency increase
which ever is required MEM/CPU/DISKIO/Network in DB Server.


Auditing user queries of specific rows in a table

Wouldn't it make more sense to set the permissions so they can only access the things they are authorised to rather than audit and log that they did something unauthorised - that said:-

I think if you built 2 views which filtered the base tables to "sensitive" and "normal" rows and only permitted access via the views you could then audit the views.


Getting database sizes on an MSSQL Server named instance

If you just need the size of the entire database then below script will help you :

SELECT  @@servername as server_name,
        d.name,
        CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00)) Size_MBs
FROM    sys.master_files mf
        INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
AND     d.state_desc = 'ONLINE'-- ONLY get size for ONLINE database.
GROUP BY d.name
ORDER BY d.name

If you want to automate your migration of databases from one server to another, I highly recommend using sqlmigration - a powershell script that will migrate your databases, jobs, logins, etc from one server to another. Also, be aware of the pre and post migration steps if you are moving from one version of sql server to higher (2012 or 2014) version.

Depending on your size of databases and the amount of downtime, there are ways that you can implement to minimize the downtime for your entire migration - e.g. using logshipping or mirroring and then during cutover, just failover to the new server, etc.

Always prepare and test your migration and rollback procedures.

Edit: From your screenshot, it seems that you do not have permissions. Can you just do a select * from sys.master_files


Finding contiguous ranges in grouped data

I've set up the next sample on rextester:

DROP TABLE IF EXISTS race;
CREATE TABLE IF NOT EXISTS race (ID int, DURATION time, TOUR int, PILOT int);
INSERT INTO race VALUES (1 , '15:00:01', 216, 2);
INSERT INTO race VALUES (2 , '15:00:02', 216, 2);
INSERT INTO race VALUES (3 , '15:00:03', 217, 7);
INSERT INTO race VALUES (4 , '15:00:04', 218, 7);
INSERT INTO race VALUES (5 , '15:00:05', 219, 7);
INSERT INTO race VALUES (6 , '15:00:06', 220, 2);
INSERT INTO race VALUES (7 , '15:00:07', 221, 2);
INSERT INTO race VALUES (8 , '15:00:08', 222, 7);
INSERT INTO race VALUES (9 , '15:00:09', 222, 7);
INSERT INTO race VALUES (10, '15:00:10', 223, 2);
INSERT INTO race VALUES (11, '15:00:11', 224, 2);
INSERT INTO race VALUES (12, '15:00:12', 225, 2);

First it flags involved groups:

| ID | DURATION | TOUR | PILOT | GRP |
|----|----------|------|-------|-----|
| 1  | 15:00:01 | 216  | 2     | 1   |
| 2  | 15:00:02 | 216  | 2     | 1   |
| 6  | 15:00:06 | 220  | 2     | 2   |
| 7  | 15:00:07 | 221  | 2     | 2   |
| 10 | 15:00:10 | 223  | 2     | 3   |
| 11 | 15:00:11 | 224  | 2     | 3   |
| 12 | 15:00:12 | 225  | 2     | 3   |

Then calculates time difference of each group:

| DUREE | DIF_TOUR | MIN_TOUR |
|-------|----------|----------|
| 1     | 0        | 216      |
| 1     | 1        | 220      |
| 2     | 2        | 223      |

And finally returns aggregated values:

SELECT SUM(DUREE) DUREE, MIN(MIN_TOUR) + SUM(DIF_TOUR) TOURS
FROM
     (SELECT MAX(DURATION) - MIN(DURATION) AS DUREE,
             MAX(TOUR) - MIN(TOUR) AS DIF_TOUR,
             MIN(TOUR) AS MIN_TOUR
      FROM (SELECT ID, DURATION, TOUR, PILOT,
                   CASE WHEN TOUR <> @LTOUR AND TOUR <> @LTOUR + 1
                        THEN @GRP := @GRP + 1 ELSE @GRP END AS GRP,
                   @LTOUR := TOUR
            FROM   (SELECT @GRP := 0, @LTOUR := 0) X,
                   (SELECT ID, DURATION, TOUR, PILOT
                    FROM race
                    WHERE PILOT = 2
                    ORDER BY PILOT, TOUR) Y) Z
      GROUP BY GRP) W;

| DUREE | TOURS |
|-------|-------|
| 4     | 219   |

Rextester here


Check if master is up from slave server(replication) postgresql

I wanted to write this as comment but I don't have enough reputation.
Did you tried, pg_stat_wal_receiver? It came with PostgreSQL-9.6.

dbs=# select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 12345
status                | streaming
receive_start_lsn     | B0/F0000000
receive_start_tli     | 1
received_lsn          | CE/13C7B18
received_tli          | 1
last_msg_send_time    | 2018-09-26 16:15:25
last_msg_receipt_time | 2018-09-26 16:15:25
latest_end_lsn        | CE/13C7B18
latest_end_time       | 2018-09-26 16:15:25
slot_name             |
conninfo              | connection info

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-WAL-RECEIVER-VIEW


How to update a table with a stored procedure with XML parameter in SQL 2008?

The .nodes() method must start at the root of your xml.  It will then return one "row" per matching node for the SELECT clause to work on. It should look like this:

from @notesToUpsert.nodes('/Notes/Note') as note(col)

(BTW using "note" as an alias is confusing.  Perhaps T(c) would be easier to debug?)

Then the XQuery in the SELECT clause operates in the xml context of each "row" i.e. a <Note> element.  The query path will be:

 col.value('(./Text)[1]','varchar(500)')

,col.value('(/Notes/Note/Text)[1]','varchar(500)')

A handy debugging aid is to just dump the xml for each "row":

select
    col.query('.') as Debug
...

Edit: re-tested in light of Mikael's comments.  I couldn't reproduce my earlier output and have removed the offending line.


Cannot enter psql command "FATAL: the database system is starting up"

There is no more space on the device:

could not write to log file: No space left on device

Check it with df and make some space


mysql replication and innodb_file_per_table

Yes you can use innodb_file_per_table = 1 for your slaves.
Additionally take below points in your consideration

If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding  innodb_file_per_table = 1 on slave in my.cnf
Please have a look at How to set Master Slave replication

If you have different version of MySQL , Please make sure that you are taking backup properly because different version would have mismatches in System tables (MySQL Database).
Please have a look answer by @RolandoMySQLDBA for How to take backup properly


Problem with compare value with its aggregate function within the same table

Just use a common table expression:

WITH CustTotals AS ( ... your query above ...)
SELECT *
FROM CustTotals
WHERE customer_total > (SELECT AVG(customer_total)
                        FROM CustTotals);