Oracle standby database recovery stopped

You then have the option of either starting a managed recovery operation or opening the database for read-only access.

Start the database:

SQL> STARTUP NOMOUNT;

Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Start the managed recovery operation:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  2> DISCONNECT FROM SESSION;


SQL (Redshift) SELECT COUNT from CSV column

Was able to figure this out by unioning my individual count statements.

SELECT 'dog' as 'animal', count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
SELECT 'cat' as 'animal',  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
etc.


MongoLab: Does hardware effect performance of a find() query

All things being equal, it is logical to assume that better hardware = better performance. However, computers are strange beasts - I know that with Oracle, if you assign very large sizes (you need good hardware) to certain caches, you can actually slow down the machine.

The only real suggestion that I have is that you test, test and test again. You cannot manage what you cannot measure.


Best way to add Foreign Key to massive table?

First of all, depending not just on the number of rows but also on the total size of your table (based on the average row size) , it may not be excessive to create a new Foreign Key. Especially if scheduled properly.

However, it may well be too heavy a change.  In that case, you could consider creating an Intersection Table like:

CREATE TABLE FlightPair
(
     departurekey INT REFERENCES arrival NULL,
     arrivalkey INT REFERENCES departure NULL
)

This would allow you to incrementally batch the insert of rows into the intersection table a bit at a time, thus keeping the update transactions quite small.  Perhaps insert one day at a time, 10,000 rows at a time, or some other batching method.  Each batch should be a transaction that begins before the insert/update and commits when finished.

That means that the updating of the batches of your choice would be many small transactions instead of one big one.  That would limit the resources needed and, in many cases, would run faster that one big update.

Perhaps having a date included would help to filter the results more effectively.  NULL keys are probably needed for the periods that are incomplete.

What indexes do you currently have?  SQL performance issues often depend on having the indexes properly defined. The columns in the intersection table likewise need to have indexes, as do the departure and arrival tables.


I think this will do it
I changed this answer as I may have read the question incorrectly
Look at edit history if you are looking for something different

as for the fk

with CTE as
(  select id, mail
        , row_number() over (partition by mail order by id) as rn
   from table
)
update fk1
   set fk1.fkID = cte1.ID
  from fk as fk1
  join cte as CTE2
    on CTE2.ID = fk1.fkID
   and CTE2.rn > 1
  join cte as CTE1
    on CTE1.mail = CTE2.mail
   and CTE1.rn = 1;

run above for addition fk

delete *
from
(  select id, mail
        , row_number() over (partition by mail order by id) as rn
   from table
) as t2
where rn > 1;


The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

First, verify the service account, whether it has appropriate permissions on the server.

Then  consider Re-configuring Word Breakers as previous version of Word Breakers might not compatible with SQL 2017. For more details


Data Warehouse Design and Double Dipping

I don't see why you can't have a factless fact table, with the Event, Athlete, and Country as dimensions. You are using the table to record events, rather than measures.

I guess that athletes can change countries for different Olympics, so it is a bit tricky to include them in the same hierarchy, though it could be a slowly changing dimension.

You would use Count(*) as the aggregation, instead of Sum(number_of_medals).


The main problem you are hitting is the one on line 8:

160513  0:11:16 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

You can run the command mysql_upgrade to create the missing table.


At what point is a (disk-based) table removed from memory after data is read from it?

This is a complicated topic, much of which is covered in this Microsoft Docs article.

To answer a couple of points:

  Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them?

Sort of.  Entire tables aren't necessarily persisted in memory.  Individual pages from a table or index are stored in memory - it could be the entire table, or just a portion of it.

  If so, what determines how long they stay in memory for?

You'll have to reference the article I linked to, but lots of things can cause "memory pressure" which will lead to data pages being "evicted" from the cache.

Things like the amount of memory available to SQL Server, other data and index pages being pulled into memory, other SQL Server data caches needing memory, "external" pressure from the OS and other processes on the box, etc can all result in pages being kicked out of cache.

SQL Server uses a "least recently used" cache, so the least recently referenced pages will generally get kicked out first.

  Is there a way to manage how long they persist in memory for?

Only by making sure SQL Server has enough memory to keep the pages in the buffer pool, and making sure other processes don't put pressure on SQL Server.

One thing that can help prevent the OS from taking memory back from SQL Server is turning on the Lock Pages in Memory (LPIM) feature.

As a historical note, DBCC PINTABLE used to work for keeping a table in memory, however, it was deprecated as of SQL Server 2005 (and now does nothing at all).


The script you linked to uses SQL Server's built-in Blocked Process Report. It doesn't have any configuration options about the level of blocking - it simply wakes up on an interval (say, every 5 seconds), checks for blocking, and if it finds any, runs the script.

There is a Blocked Process Threshold setting, but that just determines the interval at which the Blocked Process Report runs - like every 5 seconds. (Don't go lower than that.)

If you want to build your own custom Blocked Process Report and put logic in there about how much blocking is occurring, and in which databases, that would be an exercise left for the reader. You can start with Darko's Building a Custom Blocked Process Report.

From my own experience over the years, I've seen people who wanted to build these kinds of reports, and then the very next thing they do is build an Outlook rule to move all of these alerts into a folder, and they read 'em later. If you're going to do that, don't bother sending emails.

Just set up an Extended Events session to look for blocked processes and deadlocks - note that you'll need to change the file locations & names for your server:

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

Enable SQL Server's Blocked Process Report:

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

And when you want to check for blocking, this query reads the files:

WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
    ('C:\temp\XEventSessions\blocked_process*.xel',
     'C:\temp\XEventSessions\blocked_process*.xem',
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

Hope that helps get you started. Also, just FYI, I edited your question to remove the part about sp_WhoIsActive being mine - it's not. It's Adam Machanic's. If you ever meet him in person, you should say thank you to him and buy him his drink of choice.


Delete rows from report based upon specific field value

There is no concept of DELETE for a report itself, specifically SSRS, because a report is meant to be read-only. If by DELETE you mean you don't want to bring those rows into your report's dataset at all, then you need to filter the dataset appropriately to eliminate the rows upstream.
For example if your dataset is a SQL query, you can add a predicate in the WHERE clause of that query to filter out the data (or if it's a stored procedure you could do the same via a parameter). We'd need to see the query that populates your SSRS report's dataset.

If instead, you're asking how to have a "delete button" in an SSRS report, so a user can choose which rows to delete from your database, then that's a little more complicated.
It would require you creating a stored procedure that deletes from the underlying tables appropriately and takes in parameter(s) of the key(s) for the rows being deleted. Then you'd make that a hidden parameter of your report, and would have to add a column to your report with a link that has an expression that references the report itself but with the additional hidden parameter. Then it would re-run the same report but passing in the key of the row that the link was clicked on to the stored procedure, so it knew which row to delete from your database, before returning the rest of the rows needed for the report.


I am running Availability Groups and using Ola's backup solution, and here is how I have it set up:

First, Availability Groups do not do their own backups, something has to be scheduled to run a backup.  So make sure there is not a Maintenance Plan already doing backups on the SQL Server
Make sure Ola's scripts are installed and up to date on all of your Availability Group servers. :)
Create a SQL Agent job for your FULL backups that calls Ola's dbo.DatabaseBackup procedure; schedule this to run however often you need.
Repeat step 3 for your DIFF and LOG backups.
Copy the backup jobs you just created to all of your secondary Availability Group servers.

Here's what you need to know about Ola's scripts:

They are what I like to refer to as "Availability Group Aware", meaning that you can run the same backup jobs on both your Primary and Secondary AG servers, and they will act accordingly. (ex: If you run a FULL backup on a Secondary, Ola's script will recognize it is on a secondary and exit gracefully without doing anything)
If you specify ALL_AVAILABILITY_GROUPS as your backup database, it will backup only the databases in ALL of the availability groups on that server. If you specify USER_DATABASES, it will back up ALL user databases on the server (including the ones in the AG).


T-SQL query to find Primary Management Server for SCOM Agents

In case anyone else is interested, I found a query that seems to do the trick finally. The query was found here

SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryManagementServer
FROM Relationship  R WITH  (NOLOCK)
JOIN BaseManagedEntity SourceBME ON  R.SourceEntityID  = SourceBME.BaseManagedEntityID
JOIN BaseManagedEntity TargetBME ON  R.TargetEntityID  = TargetBME.BaseManagedEntityID
WHERE  R.RelationshipTypeId  = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication()
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK)
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK)
where TypeName  = 'Microsoft.SystemCenter.GatewayManagementServer') and IsDeleted  ='0')
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK)
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK)
where TypeName  = 'Microsoft.SystemCenter.ManagementServer') and IsDeleted  ='0')
AND  R.IsDeleted  = '0'
ORDER BY [PrimaryManagementServer],[Agent]


How do you authenticate the database connection when using Reporting Services Forms authentication?

I somehow missed seeing the Credentials section you can set for a data source. This way I can use SQL Server authentication and it works.


Transactional log files keeps log records for operations on database. It is expanding when necessary by claiming new pages from OS. However, Log file doesn't shrink automatically. It will stay 10GB if you don't shrink it manually.

When log backups performed, the data on log file is marked as reusable and you can see log usage from

SSMS: Double Click on Database-> Tasks-> Shrink->File and choose Log File.
DBCC SQLPERF(logspace) command.

If your log usage is below 50 percent and log file size is big such as 10GB you may consider shrinking it. However, Shrinking is always a bit tricky in my opinion. You can claim back the space but what is the point of taking pages back if OS is going to grant it again? For instance, If batch insert is performed last night and your log file is 10GB suddenly, you can shrink it back. On the other hand, if this operation runs every night, there is no reason to shrink log file.


A composite index on (status, empID) will be faster.  It might be better as (empID, status) depending on the cardinality of these two columns.  An EXPLAIN would have shown you that it was only using one value against one index.


How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

You may find this surprising, but did you know that there are several moving parts to ibdata1? Even with innodb_file_per_table enabled, here the classes of information stored in ibdata1

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

Pictorial Representation of InnoDB Architecture

What can make ibdata1 grow suddenly?
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

As long as your total InnoDB dataset is relatively small and you would like to shrink ibdata1, you can do the following:
STEP 01: Schedule downtime
STEP 02: mysqldump all databases to /root/MySQLData.sql
mysqldump -uroot -p --all-databases --routines --triggers > /root/MySQLData.sql

STEP 03: Run SET GLOBAL innodb_fast_shutdown = 0;
STEP 04: Drop all databases except the mysql and information_schema database
STEP 05: service mysql stop
STEP 06: rm -f /var/lib/mysql/ib*
STEP 07: service mysql start (recreates ibdata1, ib_logfile0, ib_logfile1)
STEP 08: Login to mysql
STEP 09: At mysql prompt, run mysql> source /root/MySQLData.sql
That's it. I have done this many, many times : Howto: Clean a mysql InnoDB storage engine?
From here, you just have to live with the weird growth due to transactions.
Give it a Try !!!


High performance count with where clause and dynamic data

Create a composite index and use only one SELECT statement. That index would either be on (date, amount) or (amount, date). Which one you choose depends on your query and the selectivity for each column.
Imagine an old-school (physical) phone book. You find something by last name, and keep searching for the first name you want (Doe, John).
Sure, columnstore indexex can be useful, but that might also be overkill.  There are specific performance concerns when you modify data frequently and there are no SEEKs in a columnstore index.


Selecting all jobs that have an enabled schedule but no next run date.
SELECT
    sj.name AS job_name,
    sjs.next_run_date,
    syss.enabled AS schedule_enabled
  FROM msdb.dbo.sysjobs sj
  INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
  INNER JOIN msdb.dbo.sysschedules syss ON sjs.schedule_id = syss.schedule_id
  WHERE
  1=1
 AND  sjs.next_run_date = 0
 AND syss.enabled = 1


Is there a performance consideration if stored procedures are in a separate database than the source data?

I've seen this type of thing done before.  Particularly where the data & possibly some code is coming from an outside location and may be overwritten with restores (for example).  It also allows the code to be backed up separately from the data (yes I know you could do that with filegroups also).

In general as long as your code is on the same instance as the data then there are no performance issues.

However you will likely run into some permissions issues.  There is something called ownership chaining that allows someone to successfully execute a stored procedure that updates a table even though they don't have permissions to the table directly.  This does not work cross-database.

Unless of course you turn on something called cross database ownership chaining.  This is not turned on initially and there are of course security implications of turning it on.  It basically allows for exactly what it says.  Ownership chaining will work across databases.  It can be turned on at a server or database level, although it is highly recommended that you not turn it on at a server level unless required for some reason.  You can however turn it on for your two databases and you should be fine.

I recommend reading the links above.  They appear to be quite good and will give you a better understanding of what I'm talking about.


The way I prefer to make another Slave (S2) is to clone an existing Slave (S1)

Install the mysql on S2, but turn off mysql.
STOP SLAVE on S1.
Stop mysql on S1.
Copy the entire mysql tree from S1 to S2.
Copy my.cnf (or its include files) from S1 to S2, but be sure to change server_id.
Start mysql on S1; START SLAVE.
Start mysql on S2; START SLAVE.

Both S1 and S2 will then catch up on replication from the same point.

Apparently your creation of S2 failed in some way; I am recommending you start over rather than "fix" the inconsistent data.

If you would prefer to fix, then see Percona toolkit.


convert historic local time to UTC taking into account of daylight saving

See https://github.com/mj1856/SqlServerTimeZoneSupport by Matt Johnson for Sql Server 2008R2+.


I know what you mean. Alter table sometimes has to rebuild the whole table when you use it.

Assuming that the Alter table statement is annoying you because it takes too long AND it locks your table, then you can use 2 tools to make it an "online" alter table.

One if from facebook https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

And one is from openark http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html (MySQL 5.1+ only)

Both these tools basically create a new table, copy the data over and keep it updated by updating both the old and the new table with new queries until the new table is completed.
This means that you need to have the space available to do this operation.

Hope it helps.


Your definition of 2NF is not quite correct.

2NF is when a relation is in 1NF and it has no partial dependencies, meaning there are no predicates (columns) that depend on only part of a multi-part key.

What that means is that a table where all the (one or more) keys are single column ones cannot violate 2NF by definition.  However, it's also possible to have a table with a multi column (composite) key which also doesn't violate 2NF.  It's not about how many columns are in your key, its about whether there are non-key columns which depend on only part of any candidate key.

Since it's possible for a table to have a multi column key be in 2NF, it's also possible for a table with a multi column key to be in 3NF.  You just have to make sure there are no transitive dependencies as well.


Can't login ORA-28031: maximum of 148 enabled roles exceeded

to let the user connect you need revoke some roles from the user

see discussion about here: AskTom

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1565923300346644029

the discussion about parameter MAX_ENABLED_ROLES that used to limit max count of roles to 150 roles. the parameter is marked as deprecated but as the discussion says it still had some value for limit

  It is deprecated because they are currently maxing it out (just like
  maxtrans is deprecated - but still "exists", it is 255 no matter what
  you set it to)
  MAX_ENABLED_ROLES

Range of values 0 to 148

  deprecated

  MAX_ENABLED_ROLES specifies the maximum number of database roles that
  users can enable, including roles contained within other roles. The
  actual number of roles that users can enable is 2 plus the value of
  MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC
  and the user's own role.


Lowering MySQL Memory Usage without Restart

Have a look at the InnoDB Architecture from Vadim Tkachenko

When you set innodb_max_dirty_pages_pct to 0, it just expedites dirty pages out of the buffer pool. The changed pages also exist in the Double Write Buffer inside the System Tablespace (ibdata1).
Even if mysqld crashes, the crash recovery cycle is initiated during mysqld's start up. Thus, no need to worry about data loss.

Does it clear the clear memory ?

No, it does not clear memory. It simply sends the pending changes (data pages and index pages) from the Buffer Pool into the Double Write Buffer and then to the .ibd files.

does it require a mysql restart ?

No. Login to MySQL and run
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Please note that this clearing of the buffer pool happens on MySQL shutdown anyway. Setting innodb_max_dirty_pages_pct to 0 will keep the buffer pool as clean as possible. Doing this about 5 minutes before doing an actual shutdown will allow for a faster shutdown without changing anything else.
If you want the setting to be permanent, add this to my.cnf
[mysqld]
innodb_max_dirty_pages_pct = 0

You can do this in a production environment and it would be safe if your have really good disks. If the Disks are slow, you will see increased I/O. If you have a high-write application, you should leave it set to the default value of 75. (If you use MySQL 5.1, the default is 90).
OTHER THINGS TO TRY
Drop the Query Cache and Disable Query Cache Mutexing
mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

From my post https://dba.stackexchange.com/questions/65888/recommendation-for-mysql-please-mysqltuner/65928#65928

Reduce max_connections in half
Reduce the sizes of your per-connection buffers

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size

GIVE IT A TRY !!!
CAVEAT : If you change any of these settings and you want them permanent, add them to my.cnf so that future restarts of mysqld will have the setting in place.


Error at line 1, column 25: ORA-00923: FROM keyword not found where expected

As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes:

SELECT T1.*,
   (SELECT T3.SMN_DATEC-1
    FROM
        (SELECT T2.DET_NUMBERA
              , T2.SMN_DATEC
              , row_number() over (ORDER BY DET_NUMBERA,SMN_DATEC) AS top
         FROM CHRISCS_EMSAL T2
        ) T3
    WHERE T3.TOP=1
      AND T3.DET_NUMBERA = T1.DET_NUMBERA
      AND T3.SMN_DATEC > T1.SMN_DATEC
   ) AS EndDate,
   T1.DET_NUMBERA,
   T1.SMN_DATEC
FROM CHRISCS_EMSAL T1
ORDER BY T1.DET_NUMBERA,
         T1.SMN_DATEC;

First off remove the square brackets, then move your ORDER BY clause in T3 to a ROW_NUMBER analytic function aliased as TOP, then in the outer query of your scalar valued subquery (aliased as EndDate) change the TOP 1 clause in the select statement to a TOP=1 predicate in the where clause.

Also remove the AS from table aliases, Oracle doesn't allow it (but it's OK in column aliases.)

On closer inspection of your code it looks like your scalar valued subquery is just getting the next chronological DET_NUMERA's SMN_DATEC and subtracting 1 day from it.  That's something really easy to do with Oracle analytic functions without all the scalar valued subqueries:

SELECT T1.*
     , LEAD(T1.SMN_DATEC-1) OVER (PARTITION BY T1.DET_NUMBERA
                                      ORDER BY T1.SMN_DATEC) EndDate
     , T1.DET_NUMBERA
     , T1.SMN_DATEC
  FROM CHRISCS_EMSAL T1
 ORDER BY T1.DET_NUMBERA,
       T1.SMN_DATEC;


How to convince Oracle to create optimal execution plans in SQL queries with bind variables?

Test 1 is logically incorrect for this use-case. If you provide NULL as the value of :B1, it will return 0 rows.
Test 2 is logically correct, but its execution plan is suboptimal, so is performance. In this case the optimizer is unable to utilize the index, because NULL values are not indexed by default.
Test 3 is logically correct, and its execution plan is optimal, and this should be used for a simple case like this. But be very careful with this.
employee_id = NVL(:B1,employee_id) was transformed into a union of two branches collecting results based on  employee_id = :B1 and :B1 is null. This is called NVL optimization and is displayed as an OR-expansion (ORE) in the execution plan. This is a nice little trick of the optimizer.
The problem is, this works properly only when you have 1 predicate like that. If you have more predicates like that, the optimizer will not do this trick for the remaining predicates, and it will not created additional branches for the UNION.
A query like this:
SELECT * FROM MY_TABLE
WHERE COL1 = NVL(:B1, COL1) AND COL2 = NVL(:B2, COL2) AND COL3 = NVL(:B3, COL3);

will not produce branches for all path. The optimizer will perform the NVL optimization for 1 predicate, then it will place the remaining predicates in the 2 branches created by the transformation of the first predicate. This means a plan generated from a query like that will never be optimal. This is something that causes a big share of SQL performance problems I encounter, and a not a single developer was aware of this so far. If you want to have optimal execution plans and performance, you should generate the simplest SQL statement on the fly instead of trying to cover all possible scenarios with a single SQL statement. For example, if there was no value provided for :B1, then simply do not include that predicate in the SQL statement.


Views in MySQL are handled using one of two different algorithms: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it.
The 'third' option is UNDEFINED, which tells MySQL to select the appropriate algorithm. MySQL will attempt to use MERGE because it is more efficient. Main Caveat:

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the select list

Refers only to literal values (in this case, there is no underlying table)

[src]

I would venture to guess your VIEWS are requiring the TEMPTABLE algorithm, causing performance issues.
Here is a really old blog post on the performance of views in MySQL and it doesn't seem to have gotten better.
There might, however, be some light at the end of the tunnel on this issue of temporary tables not containing indexes (causing full table scans). In 5.6:

For cases when materialization is required for a subquery in the FROM clause, the optimizer may speed up access to the result by adding an index to the materialized table.
...
After adding the index, the optimizer can treat the materialized derived table the same as a usual table with an index, and it benefits similarly from the generated index. The overhead of index creation is negligible compared to the cost of query execution without the index.

As @ypercube points out, MariaDB 5.3 has added the same optimization. This article has an interesting overview of the process:

The optimization is applied then the derived table could not be merged into its parent SELECT which happens when the derived table doesn't meet criteria for mergeable VIEW


I will answer you question point wise like you asked

No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the checkpointLSN of full backup and this will show to which full backup this differential backup relates to. Differential backup is also kind of full backup just taking backup of extents which has changed so LSN number might not be same as that of Full backup.
Neither Transaction Log backup nor LSN chain would is affected by Differential backup or Full database backup. Only log backup effects the chain.Proof you can see last LSN of 12 AM trn backup is same as first LSN of 1 AM transaction log backup.

Hope this helps


sp_BlitzCache slow results on @SortOrder='cpu'

The most likely reason for this is a very large execution plan in the cache that doesn't show up in other sort orders. That can certainly hold things up when we hit the RelOp section of the XML.

You can use the @SkipAnalysis parameter, and set it to 1. This will skip all the XML analysis, but it will get you results. You'll want to use @ExpertMode to get the SqlHandle and QueryHash for the big plan that's holding things up.

EXEC sp_BlitzCache @ExpertMode = 1, @SkipAnalysis = 1

When you figure out which plan it is, you can use the @IgnoreSqlHandles or @IgnoreQueryHashes to skip over that plan in the cache in the future.

EXEC sp_BlitzCache @IgnoreQueryHashes ='', @IgnoreSqlHandles = ''

Hope this helps!


Restore Database w Master Key and storing password in plain text

Without a doubt, a new design is needed for step 2. I would suggest that, instead of passing the Database Master Key password, which typically remains fairly constant and is not changed frequently, step 2 would back up the Database Master Key using a randomly generated password of sufficient length. The password can then be encrypted at the source, passed to the destination server encrypted, then decrypted and used to restore the Database Master Key. For this design, you will need to add a procedure, a certificate and a view to the source server msdb database and a procedure and a certificate to the desitination server msdb database. You will also need to create a share folder on the destination server with write permissions granted to the prod server's SQL Agent service account. The objects involved are:

ProdServer.msdb.vwGetRandomPass - view that generates random long
passwords
ProdServer.msdb.DMKEncryptionCertificate - certificate to encrypt DMK
password
ProdServer.msdb.usp_BackupDMK - retreieves a random password from the
view vwGetRandomPass, uses it to back up the DMK to the share,
encrypt the password using the certificate DMKEncryptionCertificate,
return the encrypted password as an output varbinary variable
TargetServer.msdb.DMKEncryptionCertificate - certificate to decrypt
DMK password restored from a backup of the cert and private key on
the prod server
TargetServer.msdb.usp_restoreDMK - accepts varbinary parameter
password, decrypts password using DMKEncryptionCertificate, restore
DMK from share using decrypted password, upon successful restore
delete the DMK file

Step 2 would consist of calling the prod server procedure, followed by a call to the TargetServer procedure to complete the restore. You can use a linked server, osql call or other method to call the procedure on the target server.

For even further security, you can drop the private key on the Production Server after backing it up. That way only the destination server can decrypt the password. The password will also be different and unpredictable every day. The other benefit is that the DMK would be deleted every time and would only exist on the share for the duration of step 2, which should be a matter of seconds.

This can be done successfully, however, I would also ask if it should be done. If this data is so sensitive that it needs to be encrypted, then should it be available outside of your production system? If you decide against it, then you could just drop the certificate and symmetric key in the target database and create new ones with the same name to avoid exceptions. Any call using these would return null. I've included the view below:

/*
generates a random 128 character string from all
valid password characters except single quote

*/
CREATE VIEW dbo.vwGetRandomPass
AS
WITH s1
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 48
            AND 57 -- number characters
    ORDER BY newid()
    )
    ,s2
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 65
            AND 90 -- Upper letters
    ORDER BY newid()
    )
    ,s3
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 97
            AND 122 -- Lower letters
    ORDER BY newid()
    )
    ,s4
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 33
            AND 38
        OR number BETWEEN 40
            AND 47 -- sign characters
        OR number BETWEEN 58
            AND 64
        OR number BETWEEN 91
            AND 96
        OR number BETWEEN 123
            AND 126
    ORDER BY newid()
    )
    ,final
AS (
    SELECT chr
    FROM s1

    UNION ALL

    SELECT chr
    FROM s2

    UNION ALL

    SELECT chr
    FROM s3

    UNION ALL

    SELECT chr
    FROM s4
    )
SELECT pass = (
        SELECT chr AS [text()]
        FROM final
        ORDER BY newid()
        FOR XML path('')
        )
GO


Complex `UNIQUE INDEX` scenario - Games, teams, players, positions!

You could use an EXCLUDE constraint:

create extension btree_gist ;
create table foo (game text, team text, player text, position text,
    exclude using gist (game with =, player with =, team with != ));

You will want to put the != column at the end of the list of columns, otherwise enforcing the constraint will be slower.

insert into foo values ('1', 'detroit', 'parish','catcher');

insert into foo values ('1', 'cubs', 'parish','LF');

ERROR:  conflicting key value violates exclusion constraint "foo_game_player_team_excl"


sys.partitions row count is badly wrong - how to correct this?

Try using sys.dm_db_partition_stats instead of sys.partitions, as in:

SELECT ObjectName = QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
    , RangeValue = rv.value
    , sys_partitions_RowCount = p.rows
    , sys_dm_db_partition_stats_row_count = ddps.row_count
FROM sys.tables t
    INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
        AND p.index_id = i.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    INNER JOIN sys.partition_schemes ps ON ps.data_space_id = ds.data_space_id
    INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
    INNER JOIN sys.partition_range_values rv ON rv.function_id = pf.function_id
        AND (rv.boundary_id + 1) = p.partition_number
    INNER JOIN sys.dm_db_partition_stats ddps ON t.object_id = ddps.object_id
        AND p.partition_id = ddps.partition_id
WHERE p.index_id <= 1
    and t.name ='tbl'
    and sc.name = 'temp'
ORDER BY sc.name
    , t.name
    , rv.value;

For Azure SQL Data Warehouse, you'll need to use sys.dm_pdw_nodes_db_partition_stats instead of sys.dm_db_partition_stats, even though they contain the same details.

Note, I removed the CONVERT(date,...) functionality so this code is compatible with all partition schemes, not just those with date range values.

In the on-prem version of SQL Server, sys.partitions gets its row counts from the internal table ALUCOUNT or sys.sysrowsets, if ALUCOUNT.rows is NULL.  The definition of sys.partitions is:

CREATE VIEW sys.partitions AS
    SELECT rs.rowsetid AS partition_id
        , rs.idmajor AS object_id
        , rs.idminor AS index_id
        , rs.numpart AS partition_number
        , rs.rowsetid AS hobt_id
        , isnull(ct.rows, rs.rcrows) AS rows
        , rs.fgidfs AS filestream_filegroup_id
        , cmprlevel AS data_compression
        , cl.name AS data_compression_desc
    FROM sys.sysrowsets rs OUTER APPLY OpenRowset(TABLE ALUCOUNT, rs.rowsetid, 0, 0) ct
    LEFT JOIN sys.syspalvalues cl ON cl.class = 'CMPL' AND cl.value = cmprlevel

The on-prem version of sys.dm_db_partition_stats gets its row counts differently, from the internal table, PARTITIONCOUNTS:

CREATE VIEW sys.dm_db_partition_stats AS
    SELECT c.partition_id
        , i.object_id
        , i.index_id
        , c.partition_number
        , c.in_row_data_page_count
        , c.in_row_used_page_count
        , c.in_row_reserved_page_count
        , c.lob_used_page_count
        , c.lob_reserved_page_count
        , c.row_overflow_used_page_count
        , c.row_overflow_reserved_page_count
        , c.used_page_count
        , c.reserved_page_count
        , c.row_count
FROM sys.indexes$ i
CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS, i.object_id, i.index_id, i.rowset) c

While both sys.partitions and sys.dm_db_partition_stats should both have correct row counts, I'd put more trust in the PARTITIONCOUNTS internal table.


Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP

This is an old question but figured I would provide some pointers here.  The first place to start, naturally, is with the documentation, which is excellent.  It will work exactly as written on any supported OS.

This being said, if you are on Windows, some of the specifics of PostgreSQL are sufficiently UNIXy to be confusing.  Its thus helpful to remember what streaming replication is in PostgreSQL, which is a continuous disaster recovery which never completes.  So in addition to the replication docs you want to read all about point in time recovery and incremental backups.

The key aspects of replication is the recovery.conf which configures the PostgreSQL system to basically continually recover to now() with logs provided by another server.  If you think about it from that perspective, the docs and tutorials make more sense.  You do need to know where the data directory of Postgresql is however.


MySQL - Optimize subquery in Select, where and order by

Turn it around so that you start with the grouping:

SELECT  c.id, c.`job-cat`, x.jobCount
    FROM
    (
        SELECT  mul_category, COUNT(*) AS jobCount
            FROM  jobs
            WHERE  ending >= CURDATE()
            GROUP BY  mul_category
    ) AS x
    JOIN  jobscat AS c  ON c.id = x.mul_category

Indexes:

jobs:  (mul_category, ending)
jobscat:  (id, `job-cat`)


How to reformat Oracle SQL quadratic-equation solver script?

Well i got the following to get the output in two columns:

SELECT 'Not a quadratic equation.' as "ans1", '0' as "ans2"
FROM dual
WHERE &&A = 0
UNION
SELECT 'x = ' || to_char(-&&B/2/&A) as "ans1", '0' as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&&C = 0
UNION
SELECT 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) as "ans1", 'x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C > 0
UNION
SELECT 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' as "ans1", 'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')' as "ans2"
FROM dual
WHERE &A != 0
  AND &B*&B-4*&A*&C < 0;


Difference between two rows in MySQL Workbench, but LAG is not authorized

SELECT id
    ,product_id
    ,date
    ,weight
FROM
(
SELECT t.id
    ,t.product_id
    ,t.date
    ,t.weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date < t.date ORDER BY date LIMIT 1) AS prev_weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date > t.date ORDER BY date LIMIT 1) AS next_weight
FROM table AS t
) AS t1
WHERE weight <> prev_weight OR weight <> next_weight


Delete several billion records old records (roughly 600GB)

In this case it's may be more convenient to copy the rows that should remain rather than delete. Note that all 600Gb will move to your log file surely.

  By default, a DELETE statement always acquires an exclusive (X) lock
  on the table it modifies, and holds that lock until the transaction
  completes.

This is wrong. SQL Server never starts with table lock unless the corresponding hint was specified.

  By default, SQL Server will acquire the finest-grain lock possible, in
  order to attain the greatest concurrency. In most cases, this means
  SQL Server will acquire row (RID or  KEY) locks. SQL Server can
  acquire hundreds or thousands of individual locks on data  in a single
  table without causing any problems. In some cases, however, if SQL
  Server  determines that a query will access a range of rows within a
  clustered index, it may instead acquire page locks. After all, if
  every row on a page is going to be accessed, it's easier to manage a
  single page lock than dozens, or hundreds, of row locks. In other
  cases, primarily when there is no usable index to help process a
  query, SQL Server may lock an entire table right at the beginning of
  processing a query.

In your case whith filtering on PK ranges: MyID BETWEEN 1 AND 1000000 it most probably will start with page locks. And here all depends on how many records fit in one page. If locking of 1000000 records with page granularity takes less than 5000 locks you'll be fine with page locks, if you'll have more locks per statement lock escalation will happen and your table will be completely locked.

  ...

  In addition to escalating locks when an
  instance-wide threshold is crossed, SQL Server  will also escalate
  locks when any individual session acquires more than 5,000 locks in a
  single statement. In this case, there is no randomness in choosing
  which session will get  its locks escalated; it is the session that
  acquired the locks.

SQL Server Concurrency: Locking, Blocking and Row Versioning by K.Delaney

So only by testing and monitoring locks acquired you can find the optimal batch size and avoid lock escalation. You can approximately calculate your batch size if you know the average of rows per page in your table. Or you can force page locking by using PAGLOCK and even disable lock escalation on your table:

ALTER TABLE MyTable SET ( LOCK_ESCALATION = DISABLE )


You should be able to lock all of the tables you intend to alter (or otherwise want to isolate, in order to modify triggers) with one statement, and your session will hold the lock while you make the changes, until you release the lock.

Example, I have 3 tables here, t1, t2, and t3.  I'm going to make changes to t2 and t3, and I'm going to use t1 to illustrate whether I am actually still holding locks on t2 and t3.

Obtain write locks on t2 and t3.  Any time you lock table you implicitly release any other table locks, so you have to lock them all together.  Your session will block until you get all the locks, then your prompt returns.

mysql> lock table t2 write, t3 write;
Query OK, 0 rows affected (0.00 sec)

Table t2 is locked, and I can alter it.

mysql> alter table t2 add column foo int default 1;
Query OK, 25 rows affected (0.81 sec)
Records: 25  Duplicates: 0  Warnings: 0

I also have access to t3 (which happens to be empty):

mysql> select * from t3;
Empty set (0.02 sec)

However, I didn't lock t1, so I can't even select from it.  When you hold one or more table locks, you can only access tables you have locks for.

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Now, to test creating a simple trigger on t2, which is still locked.

mysql> create trigger t2_ai after insert on t1 for each row set @foo = now();
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Ha! That's a typo: I incorrectly said "after insert on t1" instead of "t2" and the server stopped me from touching a table I didn't lock, while I hold other locks.  I can, however, modify triggers on a table I have actually locked, if I type this correctly.

mysql> create trigger t2_ai after insert on t2 for each row set @foo = now();
Query OK, 0 rows affected (0.08 sec)

After all this, I can confirm that I still have locks on t2 and t3 by selecting from them, and I can also confirm that I have locks on "something" by trying to touch a table that I know I didn't lock, (t1 again):

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Changes complete, release the all of the locks you originally acquired.

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

The only caveat that comes to mind is that if something fairly serious goes wrong (not a syntax error, but a low level error like IO errors, data corruption, and OS permissions) while altering a table, the table can become implicitly unlocked... and the fact that, as I mentioned, you have to obtain all of the table locks in a single statement, because the next LOCK TABLE will unlock anything you already have locked.

Other threads trying to access the tables you've locked should block until you are done or lose your connection, which would implicitly release the locks, since they're associated with your connection/session.


A few points:

1 - This is indeed a bad idea.

2 - Your syntax should be updated.  Using implicit JOINs is generally bad since it can lead to a cartesian product if you aren't careful.

3 - Have you looked into using a recursive CTE?

4 - It also looks to me like the row-by-row could be unneeded anyways.  Your date isn't used anywhere in your actual query except to be passed as a field and checked against the subexpression.

5 - Does this return a single row per date?

6 - Finally, #temp tables are dropped when the connection is closed.  You don't need to drop them manually.  If you can create them, as long as you don't make another with the same name in the same connection you won't need to drop it manually.


Can the LOG file fill up from index rebuilds where the indexes are not fragmented?

My question is this, can automated index rebuild jobs that run hourly fill up the log file?

Yes. An index rebuild typically rewrites all of the data contained in the index. I don't know of any exceptions, but there might be some. From the documentation:

  Rebuilding an index drops and re-creates the index. This removes
  fragmentation, reclaims disk space by compacting the pages based on
  the specified or existing fill factor setting, and reorders the index
  rows in contiguous pages.

You also asked:

  My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false?

Fragmentation does not matter because a new copy of the data is created. You may be thinking of the REORGANIZE command. The amount of work performed by that command depends on fragmentation of the data, but it's done in small transactions.


Wrapping query in IF EXISTS makes it very slow

As has been explained by Paul White in his blog post: Inside the Optimizer: Row Goals In Depth the EXISTS introduces a row goal, which prefers NESTED LOOPS or MERGE JOIN over HASH MATCH

  As a final example, consider that a logical semi-join (such as a
  sub-query introduced with EXISTS) shares the overall theme: it should
  be optimised to find the first matching row quickly.

In your query this apparently happens to introduce nested loops and remove parallelism, resulting in a slower plan.

So you would probably need to find a way to rewrite your query without using the NOT EXISTS from your query.

You might get away with rewriting your query using a LEFT OUTER JOIN and checking there wasn't a row in smalltable by testing for NULL

If EXISTS(
    SELECT databasename
    FROM somedb.dbo.bigtable l
    LEFT JOIN dbo.smalltable c ON c.source = l.source
    WHERE databasename = 'someval'
    AND source <> 'kt'
    AND c.source IS NULL
)

You could probably use a EXCEPT query too, depending on how many fields you need to compare on like this:

If EXISTS(
   SELECT source
   FROM somedb.dbo.bigtable l
   WHERE databasename = 'someval'
   AND source <> 'kt'

   EXCEPT

   SELECT source
   FROM dbo.smalltable
)

Mind you, Aaron Bertrand has a blog post providing reasons why he prefers NOT EXISTS which you should read through to see if other approaches work better, and to be aware of the potential correctness issues in case of NULL values.

Related Q & A: IF EXISTS taking longer than embedded select statement


Why does SQL Server not use the Index it recommends?

Assuming you WHERE condition still utilizes ChildId, FatherId and StatusCd.  It is possible that from a statistics perspective, that either ChildId or FatherId is more selective.  Taking ChildId and FatherId out of the WHERE clause should result in that new index being used, since StatusCd is the indexed column in the index.

If you hover over the Table Scan section you should see something like the below image:

It is possible that even though it recommended creating that index, that it is still querying by either ChildId or FatherId.

It would do this if ChildId or FatherId is more selective.  Lets say StatusCd >= 2 returns 6,000 of the 8,000 rows.  But either ChildId = 123 or FatherId = 234 only has 1 row.  Then doing a table scan on that one column, and applying the rest of the conditions after the fact is a more efficient query plan (theoretically speaking) than returning all 6,000 rows from StatusCd >= 2 and trying to apply the ChildId or FatherId conditions after.

This was something I learned from a question I asked a little bit ago.  The guy who answered it had a really great way of explaining what I tried to say here.  Does the Query Optimizer Prefer to Query on Constants before Columns?

Hopefully that helps.


Plain  JOIN a,b generate the cartesian product where each row from a is combined with each row of b, despite of related they are or no. There are not only duplicates for certain user but also the rows for PCs not related to the certain user. If you restrict the output by DISTINCT there is no guarantee you'll get the proper row from the cartesian set. You have to JOIN .. ON by some column but you have to provide your tables structure for further analysis.


stored procedure logic,data flow visualization tool

I've used a program called Code Visual to Flowchart, if you feed in a SQL file it will generate a flowchart based on it. The free version is all I've used and it met the needs I had, making it much easier to work my way through a 3000 line procedure that was completely devoid of comments.

"I can confirm I have no affiliation to this product"


Different thread states in query execution

differences among these states?
The MySQL Documentation provides a list of those states.
From your MONYog graph, here are the states:

Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
Copying to tmp table
The server is copying to a temporary table in memory.
Sorting result
For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.
freeing items
The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

Why "sending data" is taking 76%?
As the Documentation says, data is being sent to the client.
What would attribute to the high load ?

Queries that return lots of rows
Lots of queries

Queries that return lots of rows
If you have queries returning lots of rows, tune your queries to return less data, perhaps adding effective WHERE and LIMIT clauses to SELECTs.
Lots of queries
If you do not have queries returning lots of rows, then it must be lots of queries. You may find this surprising, but MONYog queries mysqld for the global status variables with either
SHOW GLOBAL VARIABLES;

or
SELECT * FROM information_schema.global_status;

These constitute queries as well.
Perhaps you could configure MONYog to retrieve status information less often.


redundant foreign keys in PostgreSQL - are there any indexing benefits?

(For similar relations simple hierarchy are more sensible... (id, parent))

Apart from that the last foreign key only slows down a bit the DML queries.


You can achive this situation using ROW_NUMBER() function. In the example below it will return rows with minimal Program number.

;WITH cte AS (
    SELECT
      LastFirstName AS Client,
      DOB,
      LastFirst AS Worker,
      DATEADD(DD,839,DOB) AS [Date1],
      DATEADD (DD,1058,dob) AS [Date2],
      Name AS Program,
      EndDate,
      [rn]=ROW_NUMBER() OVER(PARTITION BY Intakes.ClientId ORDER BY Intakes.Program)
    FROM Intakes INNER JOIN
            Client ON Intakes.ClientId = Client.ClientId INNER JOIN
            ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
            Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
            CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

    WHERE (Program = 36 OR
          Program = 37 OR
          Program = 9) AND
          EndDate = '2079-12-31'
)
SELECT  Client,
        DOB,
        Worker,
        [Date1],
        [Date2],
        Program,
        EndDate
FROM cte
WHERE rn=1

Of course it depends, which verion of SQL Server you are using.


Simplest way to constrain an SSIS Execute Process Task

Use

TimeOut

Specify the number of seconds that the process can run. A value of 0 indicates that no time-out value is used, and the process runs until it is completed or until an error occurs.

TerminateProcessAfterTimeOut

Indicate whether the process is forced to end after the time-out period specified by the TimeOut option. This option is available only if TimeOut is not 0.

more about Execute Process Task Editor (Process Page)


Why is it slow?
I would advise to use the query @ypercube provided in combination with the mentioned indexes. But why is the query you had so slow in comparison?
You did not provide your table definition, but I assume from the column names and what you wrote that you have several (large) character type (text or varchar) columns in the table articles:
title, shorttitle, description, markdown, body, idx_fti

I further assume that you are running your database with a locale other than C. Sorting large text columns according to a locale is rather expensive. The relevant thing is the collation. Check your (current) setting for LC_COLLATE:
SHOW LC_COLLATE;

With Postgres 9.1 or later you can pick a collation for evaluating your expressions ad hoc. With PostgreSQL 8.4, however, this is set at cluster creation time and can't be changed later.
We recently had a related question on SO where after much deliberation and testing we found sorting according to a locale to be the major slowdown:

Slow query ordering by a column in a joined table

I expect @ypercube's query to solve that problem radically: No GROUP BY for the long text columns eliminates the expensive sort altogether. Problem solved.


Rich text: VARCHAR, TEXT or BLOB in MySQL

It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data.

To sum it up, there is no "best" solution, but if your data fits in VARCHAR, then use it :). If not, you will have to use TEXT or BLOB, or you will have to zip the data before saving it (Check compress)


MSSQLSERVER service does *not* start, port 1433 in use

Find out what process is already using TCP 1433.  You can do this through the use of TCPView.  That utility is a sysinternals tool that gives you the information you need.  Just start it, pause the data view, and look at which process is using the local port of 1433 with the TCP protocol.

Note: You'll probably want to uncheck "Resolve Addresses" in the Options menu item so that you will be able to see the default instance port number.

Then once you know the process that is already using that port, you can take the appropriate action or reach out to the appropriate personnel that may know more about the process, why it's there, etc.

Or you could always change the port in which your SQL Server instance is listening on, but I'd personally be extremely curious as to which process is already using that port.


You say the directory has rw permission; does it also have x permission.  You need x permission to access the files in a directory (so you might set the permissions on the directory to ... gritted teeth ... 777, but you shouldn't.  Is the directory owned by user informix and does it belong to group informix?  Ideally, it should be owned by informix:informix, and the permissions should be 770 (or drwxrwx---).


Can I drop an overlapping index if the larger index is unique

Yes, it should be safe to remove the IX1 as IX2 is already covering col1. If IX2 was defined in reverse order i.e. col2,col1 then scenario would be a bit different as first column in the index is of utmost importance. Moreover IX2 is a unique constraint which means combination of col1 and col2 will be unique in the table so, you should be careful for dropping or modifying this kind of index.

Indexes help in select however slows down inserts/updated and deletes. If you have multiple indexes with same set of column that means you have that many copy of those columns and will slow down any DML operation as optimizer has to modify all the copies of columns in all these places.

Preferred solution for indexes is to have less number of indexes with higher number of columns than more number of indexes with less columns(Ideally speaking however there are many exceptions).

Same is described by SQL Server SME Mr. Brent Ozar as below:

  I prefer to drop that first index and let the slightly bigger index
  pick up the slack.  Reading a slightly larger index will take slightly
  more time: if a query didn’t need that  field, it still has to pull it
  off the disk in order to perform the query.  However, dropping the
  index pays off during inserts/updates/deletes, because it’s one less
  index SQL Server has to manage.  It also makes the database smaller,
  thereby making database maintenance tasks smaller/faster.

  If:

  I have two indexes with the exact same fields in the same order, but
  One has 1-2 extra fields, and There aren’t include fields, or the
  include fields are the same Then I’ll drop the shorter index with
  extreme prejudice.

You may check below links also for more details:

https://blog.sqlauthority.com/2015/03/26/sql-server-mistake-to-avoid-duplicate-and-overlapping-indexes-notes-from-the-field-073/

https://www.sqlservercentral.com/articles/finding-and-eliminating-duplicate-or-overlapping-indexes-1

https://stackoverflow.com/questions/30604471/sql-index-is-this-overlapping

Hope above helps.


CTE Query copy value if exists into an existing table

What you have so far can be rewritten to this 100 % equivalent query:

INSERT INTO parts
        (description, item_code, created,   updated,   sale_p     ,   price, code_p_id, s_id_code)
SELECT t.description, t.code , t.created, t.updated, t.sales_price, t.price, rp.id    , rc.id
FROM  (
   VALUES
     ('Small Bus', '1dfsdf4', now(), now(), 0.15 , 0.51 , 'Tax0'      , '242')
   , ('Window'   , 'QSF-312', now(), now(), 350.0, 550.0, 'cdc'       , 'ded')
   , ('Test 1'   , 'EXD-TUH', now(), now(), 5.0  , 10.0 , '54289hijlj', 'fjDIashdukah')
   ) t(description, code, updated, created, sales_price, price, code_p, sales_c)
JOIN   parts       p ON pa.item_code = t.code  -- why?
LEFT   JOIN rates rp ON rp.code      = t.code_p
LEFT   JOIN rates rc ON rc.code      = t.sales_c
WHERE  p.company_id = 65312322                 -- why?
RETURNING *;

The query already inserts NULL into code_p_id and s_id_code respectively if no matching row in rates is found.

You don't need CTEs here.
Be aware that the RETURNING clause returns actual resulting values of new rows after applying any rules or triggers there might be.

You description is still unclear.
The join to parts seems highly suspicious. parts.company_id is not defined UNIQUE, the join will probably multiply rows. I seriously doubt that's intended.

You might really mean to UPDATE selected rows in table parts, not INSERT new rows ...


Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?

That was my old ServerFault post from two years ago. Forgive me, it is slightly misworded. It should read:

As for MyISAM, you need to periodically run one of the following:

OPTIMIZE TABLE myisam-tablename;
ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename;

OK, I just fixed it in ServerFault.
CLARIFICATION
OPTIMIZE TABLE tblname; performs the following:
ALTER TABLE ENGINE=MyISAM tblname;
ANALYZE TABLE tblname;

According to the MySQL Documentation on OPTIMIZE TABLE

For MyISAM tables, OPTIMIZE TABLE works as follows:
 If the table has deleted or split rows, repair the table.

 If the index pages are not sorted, sort them.

 If the table's statistics are not up to date
(and the repair could not be accomplished by sorting the index), update them.

The last part on the table's statistics is what ANALYZE TABLE does.
If you do not want OPTIMIZE TABLE tblname; to perform ANALYZE TABLE tblname;, then you must execute ALTER TABLE tblname ENGINE=MyISAM; instead.
WARNING : This may not be a good idea for a MyISAM that has heavy INSERTs, UPDATEs, and DELETEs because

This will make table and index statistics stale very quickly
Stale statistics will cause the Query Optimizer to make bad choices for query EXPLAIN plans

Please just stick with OPTIMIZE TABLE.


How to get CPU usage of PostgreSQL server?

That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics.

For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the check_postgres tool is likely to be of interest.

If you're interested in getting the CPU use of a particular PostgreSQL backend process from your own custom programs, you must SELECT pg_backend_pid() or get the pid of the task of interest from pg_stat_activity then use OS-level tools and APIs to collect the desired information.

If you desired, you could write a PostgreSQL C extension function to collect the information and return it as a row or rowset over the PostgreSQL protocol, so you could SELECT my_system_stats(). See:

C-language functions
Extensions

Essentially, you write a library in C using the PostgreSQL APIs, compile it using the pgxs make helper and have the server dynamically load the resulting shared object. Then you tell it what C functions are callable from SQL using CREATE FUNCTION. You can then call them like any other function. C extensions can call OS-level APIs or do pretty much whatever they want, though it is generally very unwise for them to create threads, write directly to PostgreSQL files/filehandles, or read/write PostgreSQL sockets.


Why does sql configuration manager show 32 and 64 bit options when only 64 bit version is installed?

Because you could potentially have some old 32 bit instance on that computer. The config manager tool is designed to allow you to configure instance of that version and lower.
One could argue that the tool shouldn't show that node if there isn't any 32 bit instances on that computer, but that would be a wish for MS to do: https://feedback.azure.com/forums/908035-sql-server


Multiple group by with inner query in Oracle

WITH mins AS
(SELECT P_id
    ,score AS min_score
    ,city AS min_score_city
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score) AS rn
)
, maxs AS
(SELECT P_id
    ,score AS max_score
    ,city AS max_score_city
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score DESC) AS rn
)
SELECT maxs.P_id
    ,maxs.max_score
    ,maxs.max_score_city
    ,mins.min_score
    ,mins.min_score_city
FROM mins
JOIN maxs ON mins.P_id = maxs.P_id
WHERE mins.rn = 1 AND maxs.rn = 1


What are the pro's and con's of using PostgreSQL vs MySQL for binary data?

That reference you link to is complete nonsense.  PostgreSQL supports binary data and binary-safe handling of data just fine.

There will be some small differences between MySQL and PostgreSQL about how the types and certain functions are named, but overall it won't make much difference.  There are probably other, better reasons for choosing one system over the other.


MySQL insert operation hang while another update operation is running, but can't figure out why

---TRANSACTION 11465908, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 51589, OS thread handle 139964999091968, query id 157268 172.18.70.57 root update
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5547 page no 3 n bits 80 index PRIMARY of table `lock_spy`.`t_info` trx id 11465908 lock_mode X locks gap before rec insert intention waiting
------------------

Transaction 1: would lock the whole table because of  MySQL optimization itself since the table rows is very small.

One thing: if the table rows is large enough, eg. more than 1000, or others. MySQL would use the idx_score index to scan c_score>85;

Another:
Using  force index(idx_score) to query the data. Then, MySQL only lock secondary index idx_score ( also gaps), and corresponding records( not record gap).


Using collate: When to use and when not to use

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases.

In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables:

SELECT c.name, c.collation_name
FROM sys.all_columns AS c
INNER JOIN sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.name = N'sys' AND v.name = N'tables'
AND c.collation_name IS NOT NULL;

Results:

name                    SQL_Latin1_General_CP1_CI_AS
type                    Latin1_General_CI_AS_KS_WS
type_desc               Latin1_General_CI_AS_KS_WS
lock_escalation_desc    Latin1_General_CI_AS_KS_WS
durability_desc         Latin1_General_CI_AS_KS_WS

So here we have columns with two different collations inside a single system object. No database can have a DATABASE_DEFAULT that matches both...

You don't have any control over the collation of your customer's columns or databases, or the server collation. So really the only ways to resolve the conflict are to:

use a method that doesn't hard-code a specific collation (like DATABASE_DEFAULT)
hard-code some specific, compatible collation on both sides

Since the latter is more work, makes for more complex queries, and introduces more opportunities to turn seeks into scans, I think the former is really your best option.


This KB is the closest thing I can find regarding your error, that would come from SQL Server. I would see if you could find any other errors that may give you more explanation as to the issue. Since this seems to be dealing with XML data I cannot help you that much, have not worked with XML much. Other folks on this forum might be able to chime in though...


Postgres Server staying in read only mode after a barman recover command is executed

Since barman 2.4, there are two option for after recovery behavior.
They are
--target-action option, accepting the following values:

shutdown: once recovery target is reached, PostgreSQL is shut down
pause: once recovery target is reached, PostgreSQL is started in pause state, allowing users to inspect the instance
promote: once recovery target is reached, PostgreSQL will exit recovery and is promoted as a master

Barman 2.4 also adds the --standby-mode option for the recover command which, if specified, properly configures the recovered instance as a standby by creating a standby.signal
Good luck.
If you like the answer, pls vote it up.
WY


Is it possible to remove a database server from a cluster resource, but keep it running on the node

No. The instance is configured to use cluster resources and as such if the cluster is removed the instance will not function correctly.

If you like to play with fire in an experimental-not-in-production-learn-by-trial-and-error way, you can do the opposite of How to move SQL Server from Standalone to Cluster environment.


KILLED/ROLLBACK query doesn't stop running

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command.  These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll keep on running until the kill command is issued from the remote server end


You can do it this way

-- Tasks that were running at specific (e.g. 2014-02-01 12:15:00) point in time
SELECT id, name, task_start, task_finish
  FROM tasks
 WHERE '2014-02-01 12:15:00' BETWEEN task_start AND task_finish;

-- Tasks that are running right now
SELECT id, name, task_start, task_finish
  FROM tasks
 WHERE NOW() BETWEEN task_start AND task_finish;

Here is SQLFiddle demo


Oracle Enterprise Manager Database Console - default login and password?

Here is the Oracle 11.2 installation document describing the administrator user accounts after installation.  Once logged in, you would change passwords with the syntax

alter user system identified by mynewpassword;

Note the SYS and SYSTEM accounts are assigned the DBA role. The DBA role does not have a password, the accounts do. The DBA role is created with every installation. You could grant the DBA role to another user if needed.


VBA @ Access on replacing values on a huge list

I think I can guess why your VBA is only changing around 150 records instead of all of them, but more importantly, you need to know that the specific method you're using--changing all the fields using VBA--can lead to incorrect data in your table. And, you can accomplish what you want much more easily with a SQL query ... even one made using the query designer. But for now I'll stick with plain SQL. There are three possibilities and corresponding approaches:

1 You just have to change a limited number of different sizes from mm to European. In that case use the switch function, e.g.:

update BS_2704
set Sizes = switch(
  Sizes = 150, 24
, Sizes = 160, 25
, ...
, Sizes = 325, 50
, true, Sizes
)

2 You can use a mathematical formula to convert from your mm size to your European measure, e.g. like http://en.wikipedia.org/wiki/Shoe_size#Europe:

update BS_2704
set Sizes = Sizes * 3 / 20

3 You can't use a mathematical formula, and you have such a large number of conversion mappings that putting them all in a single switch function is unwieldy: in this case you create a new table, e.g. named tblSizeMappings, with the mm sizes and the corresponding European sizes:

SizeMM SizeEuropean
   150           24
   160           25
   325           50

Then, do an update query like above but using the tblSizeMappings table to get the European sizes:

update BS_2704
inner join tblSizeMappings
on BS_2704.Sizes = tblSizeMappings.SizeMM
set BS_2704.Sizes = tblSizeMappings.SizeEuropean


One-To-One relation is this a good practice?

I hope I don't get marked down for this simplistic answer, but I think the answer is "it depends".

Are you always going to join the tables? Bad
Does your main table have ONLY columns that are fixed-width (not VARCHAR
or TEXT) and you are going to separate the variable columns from the
fixed columns? Good (unless you are joining on every query)

AT least in the MySQL world (and it would make sense to be true everywhere), a table with ONLY fixed-width columns are faster to find, so for the main table to be fixed width and putting the less-frequently needed and variable-width columns in another table...it can be a good design for speed.

On the other hand, if this is never going to be a big table, and you're usually going to grab the extra data anyway...this may just be increasing your maintenance cost for no reason...so might be best to keep it simple.

[UPDATE 2015-02-13]

At 20 fields, your table is still tiny. But, in your defense, I wish someone had thought to split some of the tables that I work with because 80% of the data (a couple hundred columns) aren't needed on every query, and we probably could have made the main table fixed-width if we had put some forethought into it. So, it's a good thing to always ask the question of whether you should split. But, at the same time try to keep YAGNI in mind so that you don't split tables when you don't need to.

If you ever expand your table to have a separate 'resume' for your users, and then a 'personal profile', and a complex mapping of user's 'view settings'...each of these might have a lot of data on their own, and each would be loaded completely separately. The resume only loads when people are viewing it, and the same with the personal profile, and the 'view settings' only load when the user is navigating the site. So, these would make 3 logically separate tables because you would query them at separate times and never together. Splitting the table isn't wrong, you just have to ask if it's worthwhile.


Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL

Avoid Row_Number because first it process whole table then again you select row
where rn=1 for each group

DECLARE @Customer_Channel_Branch TABLE (
    Customer_Num VARCHAR(10)
    ,Channel_Num INT
    ,Branch_Code INT
    ,Issue_date INT
    )

INSERT INTO @Customer_Channel_Branch
VALUES (
    'x'
    ,1
    ,1000
    ,1990
    )
    ,(
    'x'
    ,2
    ,2000
    ,1991
    )
    ,(
    'x'
    ,3
    ,3000
    ,2000
    )

DECLARE @Channel_general_Code TABLE (
    Channel_Num INT
    ,Channel_gnrl_Code INT
    )

INSERT INTO @Channel_general_Code
VALUES (
    1
    ,1
    )
    ,(
    2
    ,1
    )
    ,(
    3
    ,1
    );

WITH CTE
AS (
    SELECT a.*
        ,b.Channel_gnrl_Code
    FROM @Customer_Channel_Branch A
    INNER JOIN @Channel_general_Code B ON a.Channel_Num = b.Channel_Num
    )
SELECT c.Customer_Num
    ,c.Channel_Num
    ,ca.Branch_Code
FROM CTE C
CROSS APPLY (
    SELECT TOP 1 Branch_Code
    FROM CTE C1
    WHERE c.Customer_Num = c1.Customer_Num
        AND c.Channel_gnrl_Code = c1.Channel_gnrl_Code
    ORDER BY c.Issue_date
    ) ca


How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?

Use Innodb unless you have a specific reason not to. If your coming from Oracle you will find its internal behaviour familiar therefore learning curve is shorter.

For what you described you probably will want to enable binary logs (check which format suits your use case better: row, statement or mixed).

For hot backups I recommend to use percona-xtrabackup. It can stream data directly to a remote server so you don't even have to have double the free space available on the backup host. The documentation is very good and detailed and you can also find many source online.

I hope this helps getting you started. Good luck!


MongoDB mongos router and microservices architecture

If a mongos is killed a running migration should still complete, there just won't be another balancing operation initiated until a new mongos is able to pick up the balancer lock. That lock may end up being held for a while (I am being purposely vague, just how long a stale lock sticks around is very hard to define).

Aborted chunks are usually an issue with the primaries (they do all the heavy lifting for a migration) rather than the mongos which is just doing the coordination. The most common cause of an abort is that there is already one happening, or there is cleanup happening because of previous migrations. If you want to see a full description of the whole process, have a look at this (~11 minute) video where I describe it in depth (for MongoDB University).

That video will also give you some idea as to what the steps are that you are seeing in the abort messages. If you want to debug the aborts further I will recommend that you get the relevant information about the aborts from the primaries involved, and the changelog so that we can get an idea of the step the abort happens, and what the view is from both sides of the migration.

Finally, in terms of recommendations as to how to deal with this, I would recommend running a mongos process somewhere more permanent which does not take actual traffic from your applications. You would basically run this in the same way you do a mongod - it could even share a host/vm/instance with a mongod. You can't specify which mongos takes a balancer lock but just by being the only permanent process it should have the advantage.


Order by x DESC - but only continuous rows

This should work:

SELECT id, name
FROM data
WHERE id <= 8 AND id >= (
    SELECT MAX(d1.id)
    FROM data d1
    LEFT JOIN data d2 ON d2.id = d1.id - 1
    WHERE d2.id IS NULL
)
ORDER BY id DESC
;

The subquery look for the first gap (i.e. 5 to 6). See SQL Fiddle.

Output:

id | name
 8 | Test 8
 7 | Test 7
 6 | Test 6


Creating a partial unique constraint for MySQL

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint',
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]


Chance of duplicate on single select max into statement

Assuming that you allow multiple users in your application, max()+1 will guarantee that you will get duplicate values.  Therefore, it is a bad practice.  It's also less efficient but that is a lesser concern.  If you have two sessions, session 1 gets the max() + 1, and session 2 tries to get the max() + 1 before session 1 commits (or rolls back), session 2 will get the same value that was given to session 1.

Using a sequence will eliminate the possibility of duplicates.  Use a sequence.


Deleting duplicate records when using text datatype

You don't have to convert the actual column. Just its usage in the SELECT query.

For a one off clean up task I'd probably just go

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY record_id,
                                                from_add,
                                                to_add,
                                                subject,
                                                CAST(body AS VARCHAR(MAX))
                                       ORDER BY email_id DESC) RN
         FROM   emails)
DELETE FROM cte
WHERE  RN > 1

Unless dealing with huge amounts of data that might make spending time on a more efficient solution (that doesn't have to sort huge strings) worthwhile.

Or alternatively you could try

;WITH T
     AS (SELECT MAX(email_id) AS max_email_id
         FROM   emails
         GROUP  BY record_id,
                   from_add,
                   to_add,
                   subject,
                   CAST(body AS VARCHAR(max)))
DELETE e1
FROM   emails e1
WHERE  NOT EXISTS (SELECT *
                   FROM   T
                   WHERE  max_email_id = e1.email_id)

This is likely to give you a better plan than NOT IN as SQL Server treats MAX(not_nullable_column) as being potentially nullable and hence you end up with a potentially more expensive plan.


How to export a single schema from Oracle EE to Oracle XE?

There is no such one command to do this kind of migration of a schema because as you mentioned, Oracle Express edition doesn't support all the features that are used in the Enterprise edition. However, I tried to collect some of the useful parameters which can be used during expdp/impdp to modify or include/exclude (un)wanted objects and features.

   partition declarations can be omitted.

In that case you can use PARTITION_OPTIONS parameter in impdp.

PARTITION_OPTIONS=[NONE | DEPARTITION | MERGE].

PARTITION_OPTIONS

   storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables).

TRANSFORM parameter in impdp enables you to alter object creation DDL for objects being imported.

TRANSFORM = transform_name:value[:object_type]

TRNASFORM

   tablespace declarations omitted or replaced (a single tablespace used in XE).

You can use REMAP_TABLESPACE parameter during impdp on Oracle XE instance.

REMAP_TABLESPACE=source_tablespace:target_tablespace

REMAP_TABLESPACE

  materialized views with some advanced features can be simplified or replaced with simple views.

I think you need to import DDL of such materialized views into a SQL FILE then create view manually.

  jobs can be omitted.

You can use EXCLUDE parameter to exclude jobs but currently, Data Pump Export/Import can exclude/include jobs which were created with DBMS_JOB.

Reference

  java functions (scarsely used but present in one pl/sql package) can be disabled or imported with compilation errors.

You can use EXCLUDE to ignore such java classes.

EXCLUDE=JAVA_CLASS

Now you can combine these parameters to form your expdp/impdp command.

Note: All included reference links are related to Oracle 11g version, please refer to the Oracle documentation related to your version.


Fastest way to disable indices and drop constraints and rebuild SQL Server table

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful.

If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your Clustered Index order in the first place.

However, if you want to disable your indexes, then that would be as follows:

-- Disable indexes on OBSERVATION_FACT
-- If you're dropping, don't disable. If you're disabling, don't drop...
ALTER INDEX ALL ON OBS_FACT DISABLE;

-- Truncate your table
TRUNCATE TABLE dbo.OBS_FACT;

-- ETL Process here....

-- REBUILD indexes on OBSERVATION_FACT
-- Or recreate them if you've dropped them in step 1
ALTER INDEX ALL ON OBS_FACT REBUILD WITH (ONLINE = ON);

Creating following by immediately rebuilding your indexes as per your script, is pointless, as creating builds the index. Why then immediately rebuild?


If I were you I would break this into a minimum of three tables:

Devices
Ports
Interfaces

You have already got most of the data you will need, I do suggest also considering the MAC address of different ports where available.

If I were building these out, I would use the following fields:

    CREATE TABLE device {
    serial_num VARCHAR(25) PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE KEY,
    ip_add BINARY(16)
};

CREATE TABLE ports {
    device_serial VARCHAR(25) NOT NULL,
    port_id INT NOT NULL,
    port_type varchar(40),
    port_num varchar(15),
    mac_add BINARY(48),
    PRIMARY KEY (device_serial, port_id),
    UNIQUE KEY (mac_add)
};

CREATE TABLE interfaces {
    interface_id INT NOT NULL PRIMARY KEY,
    interface_name VARCHAR(255),
    port_a INT,
    port_b INT,
    ip_add BINARY(16),
    UNIQUE KEY (port_a, port_b)
};

This would allow you to specify most of the fields with a degree of specificity.

Each "device" could either be a router or a switch, or even an end user device, as far as the network is concerned it's simply something which can be connected. It might benefit you to add an ENUM type to let you easily assign a status to each device as well, however for now you can keep it simple.

Since most ports will have a MAC address, and MAC addresses are globally unique, it would make sense to give each MAC address a unique key, allowing it to be nullable allows you to use serial ports and the like as well which don't have MAC addresses afaik. The point of the composite Primary key in the ports table is to tie each port to a device, that way ports are not allowed to float around on theIr own confusing the net admins.

The Interfaces table is how things tie together, an interface is simplY a connection between two ports. I gave the interface the IP address, as monitoring of a device over TCP/IP tends to start from seeing if the IP address is responding and it makes the most sense, it also helps to make sure only one pair of ports are using an IP address at a time.

A few notes on this design, you will need to use some scripting language to make full use of this, I determined to use a binary datatype for IP and MAC addresses based on a useful answer on our sister site. Likewise, converting a MAC Address back and forth can be done with this PHP function.

There are certainly improvements which could be made to this design, however I think it serves as a good boilerplate for a simple network management DB.


RMAN backup copy - read once and write many?

In case you specify COPIES 2 each block is read once and written two times to backupset copies. The two backupset copies are supposed to be bit-to-bit identical. Both copies have the same backupset key (BS_key) in RMAN. You cannot mix tape and disk copies - either both copies go to DEVICE DISK or both to DEVICE SBT.


postgres - c  =  not working for postgres 11 in docker

You cannot put spaces around the equals sign when using the "-c" option.  So you need something like postgres -c max_connections=200

When I try it with the spaces, it refused to start at all.  So I don't know what you are connecting to in order to SHOW max_connections;.  Maybe you already had a server running before you tried to start with the invalid syntax?


Don't be fooled by what you see.

mysqld will not multiple instances on the same datadir and ibdata1. How do I know this?

On Sep 26, 2014 I answered MySQL got signal 11 error and slave is down. In my answer, I mentioned how InnoDB Architecture only allows one mysqld process to open a Master Thread to ibdata1.

Therefore, if you do not see the following lines in your error log

6 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:53 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

In light of these things, I assure you, you do not have multiple mysqld processes.

What, then, are you seeing? Those are actual DB Connections emanating from the mysqld instance. I have seen this freakish display come from source-compiled versions of mysqld. I have mentioned it in passing in my old post innodb_buffer_pool_size not changed and Identical mysql processes (ServerFault). My suspicion is that the OS is manifesting DB Connections as if they were mysqld processes. On the contrary, they are just ordinary DB Connections.

You should run this in Linux

ps -ef | grep mysqld | grep -v grep | wc -l

You should get an output of 2 (1 for mysqld and 1 for mysqld_safe)

If you get just 1, you probably got a very old version of MySQL.


Does the number of columns been selected affect query speed?

With most modern relational database management systems, there are three important things to consider relating to your question:

The amount of data being returned to the client. As you've already started to hint at, more data being returned to the client consumer generally means the longer it'll take for the query to complete. (This is generally globally true regardless of where the data is coming from, relational database, non-relational database, etc.)

The amount of data needed to be loaded from the server. This is arguably a subset of #1, but besides longer times to return the data to the client, there's also an increased time in locating and serving the data. Most relational database management systems store data rows  in an object called pages on the disk. In Microsoft SQL Server, for example, the default size of a data page is 8 KB. If the amount of data in a single row exceeds 8 KB, then that row will be stored across multiple pages. When executing a query with SELECT *, that will instruct the server to locate all the pages for the rows being returned which can be multiple pages per row and thus extra work besides additional data being loaded from the server which isn't necessary if you're only using a subset of columns from the dataset being queried. The disk is typically the slowest hardware component to a server, so minimizing the amount of data the server needs to locate and load off disk is substantially beneficial.

Indexes usually won't be able to be used as efficiently when executing a SELECT * query because it's not usual to have a covering index on all the fields of a table. That means instead of being able for the server to efficiently seek out the data on the most performant index for your data needs, it'll likely instead need to scan the entire clustered index which is generally a slower operation.

Long story short, it's bad practice and an anti-pattern to use SELECT * which will 99% of the time be slower than specifying only the columns you need in the SELECT list.


Using a mysqldump export to create data with a different database name

Use the option --no-create-db when you execute your mysqldump, this option avoid inserting CREATE DATABASE IF NOT EXISTS db_name; in dump file.

Max.


DB2 on Windows: Fix encoding in db2cmd and for JDBC

After opening a support case with IBM, they gave me the clue that lead to a solution:

  -> Could you first right click in the title bar of the Command Window and take "Properties".

  Is it using a TrueType font for displaying characters?

As it turns out, changing the font (wtf?) changes the display of said characters.

This is some specific behaviour for the DB2 CLI window: Opening a regular cmd with a non TrueType font works as expected.


Item not automatically selected in intellisense dropdown

Do you mean "Toggle Completion Mode", on the Edit->Intellisense menu?


Less restrictive query return less result due to simple removing one additional constraint

The FULLTEXT index acts very funny with regard to the MySQL Query Optimizer. I have written about this before:

https://stackoverflow.com/a/6092216/491757 (May 23, 2011)
FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)
Mysql fulltext search my.cnf optimization (Jan 26, 2012)
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

Try refactoring your first query so that the FULLTEXT clause and the Spatial clause are not performed in the same level of the EXPLAiN plan.

SELECT * FROM
(
    SELECT DISTINCT
        TB.ID,
        TB.Latitude,
        TB.Longitude,
        111151.29341326*SQRT(pow(-6.185-TB.Latitude,2) +
        pow(106.773 - TB.Longitude,2)*0.98839228980165) AS Distance
    FROM
    (
        SELECT * FROM `tablebusiness`
        WHERE MATCH (FullTextSearch) AGAINST
        ('kucing*' IN BOOLEAN MODE)
    ) AS TB
    join
    (
        SELECT ID FROM tableauxiliary
        WHERE MBRContains(GeomFromText ('MULTIPOINT
        (-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),TA.Latlong)
    ) as TA
    on TA.BusinessID=TB.ID
    ORDER BY
        Distance
) AA LIMIT 0, 20;


Postgres synchronized backup - what's with the archive?

Wal archive is useful for backup purposes. For example - you make full backup every day, and keep wal segments since last abckup, thanks to this you can always get database to any state that it was during the day (for example - just before someone did run: drop table users cascade;).


Can you DENY access to a certain schema for a user with dbcreator role?

DENY access to a certain schema for a user with dbcreator role?

Before I answer this, I want to mention what Daniel eluded in his answer. You do not have a user with dbcreator role, it is a login that is member of dbcreator role.  As dbcreator is a server-level role.

You can have a user denied control (Alter any schema, delete, execute, insert, select update etc) privilege whose mapped login is a member of dbcreator server-level role.

Lets do a demo of your question.

Login with a account with admin privilege. Run this statement.

USE [master]
GO
CREATE LOGIN [testDBcreator] WITH PASSWORD=N'testDBcreator', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [testDBcreator]
GO

CREATE DATABASE [testDBcreator]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'testDBcreator', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'testDBcreator_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE [testDBcreator]
GO

CREATE USER [testDBcreator] FOR LOGIN [testDBcreator] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA schema1
GO
CREATE SCHEMA schema2
GO
CREATE TABLE schema1.table1 (id int)
GO
INSERT INTO schema1.table1
VALUES (1),(2)
GO
CREATE TABLE schema2.table2 (id int)
GO
INSERT INTO schema2.table2
VALUES (3),(4)
GO

USE [testDBcreator]
GO
ALTER ROLE [db_owner] ADD MEMBER [testDBcreator]
GO
DENY CONTROL ON SCHEMA::schema1 TO testDBcreator
GO

Change connection to login with testDBcreator and run this.

USE [testDBcreator]
GO
SELECT * FROM schema1.table1
GO

You will get following error.

  Msg 229, Level 14, State 5, Line 3 The SELECT permission was denied on
  the object 'table1', database 'testDBcreator', schema 'schema1'.

Now run this.

USE [testDBcreator]
GO
SELECT * FROM schema2.table2
GO

You get the result as expected.

I am not sure if you can run your .DACPAC with this login.


Does running pg_dump on live db produce consistent backups?

From the manual:

  It makes consistent backups even if the database is being used
  concurrently.

So yes, you can trust the backup. Of course, it's PostgreSQL, you can trust your data in PostgreSQL.