Is it good idea to invoke Web Service from the database function or trigger? I am using PostgreSQL in my Web API project. I have created Web APIs using .net core. Now I have a situation, where I  want to process the inserted or updated data and store the processed data in some other table.
I was thinking to create a trigger for the table that will invoke the REST API created in .net core along with the data and in .net core, I can easily process the data and will insert it into the table I want. The API will be OneWay web service - i.e. fire and forget.
But I was wondering, whether it is a good idea to invoke web service from the database? If it is, then how could I invoke a OneWay Web service from Postgres trigger?
Another approach I found was to use LISTEN/NOTIFY. For this approach, https://shashangka.com/2020/05/17/listen-postgresql-in-asp-net-core/ article seems fine.
I was wondering, which of the approach will be better for my use case and why?

You could write a trigger that calls a web service in PL/Python, but I don't think it is a good idea to do something like that from a trigger:

Even though you think that your HTTP communication is one-way, it isn't, since HTTP is implemented on top of the connection-oriented TCP protocol. There is some back-and-forth involved in establishing a TCP connection and sending a request. Now if for example the destination host were unreachable, you would have to wait for a surprisingly long timeout.

Database transactions should be as short as possible, so that locks are not held longer than necessary and connection pooling works well (not to mention VACUUM). So any database-external activity of uncertain duration should be avoided during a database transaction. But a trigger always runs in the same transaction as the statement that triggers its execution.

I think the idea with LISTEN/NOTIFY or an approach with a queuing table would be better: the trigger just creates a task, and a worker process picks up the tasks and client-side code sends the HTTP request.


SQL Server 2014 Availability Group failed I have 3 servers on VMware workstation:

Win 2012 R2 as ADDC -Cluster service.
Win 2012 R2 as SQL server primary replica .
Win 2012 R2 as SQL server secondary replica (auto fail-over).

My Availability group fail in this scenario:

When I disconnect primary replica from the network the cluster fail to announce a fail-over to secondary replica when I try to manual fail-over from secondary replica instance I get this error:

however this not the case when I stop primary replica from cluster manager because it will do auto fail-over.

Ok I found what I was missed and it was the cluster because there is only two nodes in the cluster so I add third node and it is working now.


Design problem with auto-increment fields I've made this design similar to my problem, it's not exact but it close enough to understand my problem:

Some cellular client frequently send their GPS position and the WLAN name that they have in range, sometimes only their position.

I've auto-generated timestamp and auto-incremental idModelGPS from ModelGPS table and I'm using javaDB to write to the DB.

I want to link the ModelGPS to an WLAN name where it happens, but I can't do it because the idModelGPS is autogenerated.  I cannot access it to write in the ModelGPS_WLAN table.

I don't know how to handle this, maybe there is a design error or maybe there is a way to solve this problem that I'm unaware of. I'll be glad to hear your opinions.

So you just need to fetch the last auto-increment value that was inserted? There are a couple of ways to do this, but they're all pretty simple.

Query: SELECT LAST_INSERT_ID()

php mysql: $id = mysql_insert_id($mysql_conn);
http://php.net/manual/en/function.mysql-insert-id.php

php mysqli: $id = $mysqli->insert_id;
http://php.net/manual/en/mysqli.insert-id.php

Both of those links have some example code that looks pretty easy to follow.


possible to INclude duplicate rows? I have MariaDB version 10.2.14-MariaDB. I have a table that looks like this:
CREATE TABLE `card_data_link` (
    `card_id` INT(11) NULL DEFAULT NULL,
    `data_id` INT(11) NULL DEFAULT NULL,
    INDEX `card_id` (`card_id`) USING BTREE,
    INDEX `data_id` (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

It has this data:
card_id data_id
"63"    "21"
"34"    "3"
"34"    "21"
"34"    "21"

It joins to this one:
CREATE TABLE `card_data` (
    `data_id` INT(11) NOT NULL AUTO_INCREMENT,
    `data` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51
;

It has data that looks like this:
data_id data
"21"    "{R}"
"3"     "{3}"

If I run this query:
select
    cdl.card_id,
    cdl.data_id,
    cd.data
from
    card_data_link cdl
left join
    card_data cd on cd.data_id = cdl.data_id
group by
    cdl.card_id
    ,cd.data_id

results:
card_id    data_id    data
"34"        "3"     "{3}"
"34"        "21"    "{R}"
"63"        "21"    "{R}"

Is there a way I can get the second
"34"        "21"    "{R}"

row to show up so it returns:
card_id    data_id    data
"34"        "3"     "{3}"
"34"        "21"    "{R}"
"34"        "21"    "{R}"
"63"        "21"    "{R}"

It's like it's doing a select distinct, even though I'm not putting in distinct. Am I not grouping correctly? Thanks!

GROUP BY aggregates the result values of the SELECT statement based on its arguments.
Removing the GROUP BY clause and arguments from your SQL will allow duplicates to be presented.
For MariaDB recommend sql_mode=ONLY_FULL_GROUP_BY (not default, ref: manual) to ensure that SQL statements are correctly formed with respect to GROUP BY.


Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008 If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what.

If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM
allocation for tempdb. This could be helpful if there are usage scenarios where SQL Server refuses to allocate RAM for tempdb and uses it for something else.

So I wonder if those usage scenarios exist where it makes sense to force RAM allocation for tempdb by using RAM Disk, thus overriding SQL Server decision for RAM allocation.

I was kind of asking this here: https://stackoverflow.com/questions/10199349/can-sufficient-increase-of-available-ram-eliminate-usage-of-tempdb-when-querying, but didn't get desired and specific response, so I am asking it differently now.

I'll suggest that moving tempdb to SSDs (which you can do in a cluster, too, as of SQL Server 2012) will:

provide a roughly equivalent benefit (not quite the same, but leaps and bounds better than spinny disks);
be supported (I don't believe Ram Disks are officially supported, but I'll confess it's been a long time since I looked into it); and,
won't use up that valuable RAM for tempdb when tempdb is not using it - at those times your system would benefit more from being able to use it for buffer, query execution, etc.

In general I agree with @Dan - there are probably optimizations you can make within SQL Server that will have much more noticeable impact than moving tempdb into RAM. For example, have you investigated optimizing or even eliminating some of the things that are using tempdb in the first place?


How to find dropped or ghosted columns in Azure SQL DB? In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt.

On prem, I use system_internals_partition_columns to determine what indexes have this ghosted column so I can rebuild and reclaim the space. This internal table is not accessible in Azure.

Does anyone know of a dmv or system table in Azure with this information?

Below is an example of what I am talking about.  These rows are approx. 2000 bytes so you get 4 rows per page.  Dropping a column should make each page about 50% free space but doesn't.  Worse, new records are still added at 4/page rather than 8/page.

DROP TABLE IF EXISTS dbo.TEST

GO
CREATE TABLE dbo.TEST (ID INT IDENTITY PRIMARY KEY,
                       a CHAR(995),
                       b CHAR(995))

 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i,@i)

      SET @i +=1
    END

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/
GO

ALTER TABLE dbo.TEST
DROP COLUMN a

GO

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - No change from the dropped column

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/

 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i)

      SET @i +=1
    END

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - Same amount of space taken despite rows being half the size

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    2000    0.35    99.6540647393131

--*/

--This is how I find columns that are dropped but still being added to the page on prem
--DOES NOT WORK IN AZURE SQL DB
--SELECT tables.name
--  FROM sys.partitions
-- INNER JOIN sys.system_internals_partition_columns ON partitions.partition_id = system_internals_partition_columns.partition_id
-- INNER JOIN sys.tables ON tables.object_id = partitions.object_id
-- WHERE is_dropped = 1

I've continued looking into this off and on, but between not finding anything online and Randi's comment below, this may not be possible.

Since Azure database is MSSQL server then you can execute the following command to find how many ghost process per database

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id)
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
group by database_id
order by total_ghost_records desc

And if you are looking for more detailed report then you can execute the following command

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

Looking for long term solution to start recording this data then you can check this post. This will create table that store ghost records in the long term, but I recommend you to create it on test environment first to see if this is what you actually want.


How do you drop an unnamed check constraint in a PostgresQL table? I created a PostgresQL table but I added an unnamed check constraint on one of the columns:

CREATE TABLE FOO
(
id serial primary key,
price_range smallint CHECK (price_range > 0),
url varchar(255)
);

Now I want to remove this constraint but I can't figure out how. The typical ALTER TABLE...DROP CONSTRAINT... needs a constraint_name but I don't have one.

I know there is an answer here but when I tried to determine the name of my check constraint by following the answer there:

SELECT *
FROM information_schema.constraint_table_usage
WHERE table_name = 'your_table'

All I got back was a single constraint whose constraint_name entry was foo pkey which refers to the primary key constraint and not my check on the price column. So that answer did not help me, unless I am missing something.

How can I drop this constraint without losing any data?

Thanks!

The constraint has a name, whether you specified it or not.

From psql, \d foo will list all table constraints, along with their automatically assigned names.

You can also find these by querying the catalog tables directly:

SELECT conname
FROM pg_constraint
WHERE
  conrelid = 'foo'::regclass AND
  contype = 'c'

The table name can be schema-qualified if necessary (e.g. 'public.foo'::regclass).

contype = 'c' filters it down to CHECK constraints; the contype values for other constraint types are documented here.


How to know when a query was executed? How to find out when a query was executed? I ran a query couple of days ago, I'm not sure about the exact time. I want to know at what date and time the query was executed.
Is it possible to get that information?
How to check the server status when a query is running? What information should be checked?

version -oracle 11g

select LAST_LOAD_TIME, LAST_ACTIVE_TIME
from v$sql
where sql_id = Your SQL ID

Hope this will give you the expected result


SQL Mail Failing To Send SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-04-14T21:34:10). Exception Message: Cannot send mails to mail server. (The operation has timed out.).  )

I have tried several ways of setting up Database Mail, a mixture of precreated scripts and following the interface in SSMS.

I have also tried two different mail server credentials (Google and a Web Hosting Company), and both fail with the same error.

The Web Hosting Credentials are used on a PHP Script on another Server which works perfectly fine.

What should I check to get this working?

I was having a different issue about a month ago. I came across this script that should help troubleshooting.

 USE msdb
 GO

-- Check that the service broker is enabled on MSDB.
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on.
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.
-- These are system stored procedures that list the general
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the
problem.
-- Assuming of course that I don't want to send out potentially thousands of
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp
  @sent_before =  '2017-03-16',
  @sent_status = 'failed';


Preventing full backups for unmodified/slightly modified databases Is there a way to either:

ensure that unmodified databases are skipped during SQL backups, or even better
choose between DIFF or FULL backup depending on the percent database changed since last full backup?

We are using MS SQL Server 2008 R2 + Ola Hallengren's maintenance scripts, ~500 databases - total data files 500 x 2GB, and only about 5% databases get modified each week - but the backup procedure transfers all data files on each full backup.

Background:

Our MS SQL 2008 R2 server is configured to make daily DIFF backups and weekly FULL backups, using the "widely known" Ola Hallengren's maintenance scripts. The system is configured for multi tenancy, and databases are also physically split not only by customers, but also by months. Since the system tracks these databases very well, this makes the system a bit easier to maintain; each customer*month datafile can be located wherever you choose, individual files are smaller in size and easier to manage, and most of the r/w queries for a single customer only go into the last month's db, making them more performant (smaller indexes, less locking, etc.). Recovery model is set to SIMPLE, but losing data is not such an issue for us since these measurements are collected from distributed devices (which also have their local storages) and can re-transmit data automatically.

But the problem is that the backup procedure is becoming bizarrely slow, because each backup copies even the old, unmodified (well, presumably unmodified) backups over and over again. Normally, only the databases for the last month are being modified at any time, so we would only like to backup these weekly. I checked that old databases don't get accidentally modified by index optimization or whatever: e.g. running fn_dblog on old databases only shows some entries seemingly added by the backup procedure, like "CommitDifferentialBase" or "InvalidateDiffMaps".

Update

Using the link from @spaghettidba's answer, I slightly modified Paul Randal's stored procedure to simply return the percentage (to make it slightly easier to get the return value):

-- use at the end of 'sp_SQLskillsDIFForFULL'
return ROUND ((CONVERT (FLOAT, @DIFFtotal) / CONVERT (FLOAT, @sizeTotal)) * 100, 2);

And then I added this check inside Ola Hallengren's DatabaseBackup stored proc:

-- get the percent of changed extents since last full backup
EXEC @CurrentDbChangedPercent = sp_SQLskillsDIFForFULL @CurrentDatabaseName
IF @AutoSelectDiffOrFullOnChange = 1 AND @DiffFullChangePercentThreshold > 0
BEGIN
  IF @CurrentDbChangedPercent <= @DiffFullChangePercentThreshold
    BEGIN
      SET @CurrentBackupType = 'DIFF'
    END
  ELSE
    BEGIN
      SET @CurrentBackupType = 'FULL'
    END
END

As Mr. Hallengren pointed out, DBCC PAGE command used in Paul's proc is not really supported/documented, and this is the main reason this isn't currently a feature of his SQL Server Maintenance Solution. Few people except Paul would be able to parse this data properly.

There is a request to get a supported DMV for this on Microsoft Connect, dating from 2009:

DMV to check the percentage of a database changed since the last full backup

Skipping the backup altogether is not a good idea in my opinion. You should probably keep on taking differential backups instead. They will probably be very small and not a concern at all.

Detecting the percentage of data that changed since last FULL backup can be done using this function by Paul Randal.


SQL Server index randomly performs poorly In a SQL Server and an Entity Framework based PMS application, we have a table that stores data of Postings (for invoices). One of the columns in this table is the following:

[PostingDate] [datetime] NOT NULL

It has no triggers or constraints or anything else, except for an index that is created extremely simply, like the following:

CREATE NONCLUSTERED INDEX PostingDateIndex
ON dbo.Posting(PostingDate)

For some reason queries against this index randomly become slow, about twice per month, while the server of the application is 24/7 online (not even reboots) and - being a PMS - is used frequently through day and night.

DBCC CHECKTABLE('dbo.Posting')

does not find any errors when the queries slow down. The only symptoms are that queries that heavily rely on filtering rows by this PostingDate column start getting horribly slow, they usually go out of the 10min timeout our queries have (when the index is okay they take less than 30sec to complete).

The dbo.Posting table itself still remains useful, you can insert and edit rows, you can also make simple selects, whatever you want, but when you start queries that are heavily dependent on PostingDate, they just run infinitely.

The database has over a dozen other indexes (one of them is on another, int type column of this very same table) but none of them encounters this issue. Also the issue randomly occurs both with SQL Server 2014 and SQL Server 2016.

To fix it all we have to do is right click on the index in Management Studio and select Rebuild. Once that is done everything runs quickly again... however the issue randomly comes back and it's not really acceptable in a production environment that some of the queries just die until we manually rebuild an index.

I have absolutely no idea what could cause this issue and why only this one specific index is affected. Could anyone point me in the right direction?
How do I even find what causes it?

I don't think this corruption. It sounds to me like out-of-date statistics are resulting in bad Execution Plans. When you rebuild the index the statistics are implicitly updated which is temporarily fixing your issue but not for the reason you think. You should have a strategy for updating Statistics (and re-organising/rebuilding indices).

We use Ola Hallengen's index maintenance scripts.


Using same table in a UNION multiple times I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better way to do that.
Query statement is like below:
    Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

    UNION ALL

    Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

    UNION ALL

    Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

Thanks!

Use
Select X.Column1, X.Column2, Sum(X.Column3) AS Test, Types."Type"
From Table X
CROSS JOIN ( SELECT "First Query" AS "Type"
             UNION ALL
             SELECT "Second Query"
             UNION ALL
             SELECT "Third Query" ) AS Types
Where X.Column1 = "Fly"
Group by X.Column1, X.Column2, Types."Type"

Maybe SELECTs in Types needs additional FROM DUAL or similar, or DB2 uses some another method for to select scalar value - test.
UPDATE (copied from the comment)

You can shorten the CROSS JOIN part with:
CROSS JOIN ( values ('First Query'),('Second Query'),('Third Query')) types (type)

FWIW, Db2 also allows
CROSS JOIN ( values 'First Query','Second Query','Third Query') types (type)

but that's non standard I beleive – Lennart


I can't see some databases even though my user has the "root" role I installed MongoDB 3.2 and I enabled authentication:

security:
  authorization: enabled

I created an admin user with the following command in the terminal:

mongo admin --eval 'db.createUser({user:"admin",pwd:"password",roles:["root"]})'

Then I created new database and a user for this database using the following command:

mongo -u admin -p password admin --eval 'db.getSiblingDB("shop").createUser({user:"shop_user",pwd:"password",roles:["dbOwner"]})'

Everything seem to be working. When I'm logged in with the shop_user user I can do everything in the shop database and I can only see this database. But when I'm logged in with the admin user I cannot see the shop database. Since the admin user has the root role, I assume I should be able to see all the databases in my system, but the shop database is hidden from me.

Does anybody know what is happening and how I can make the admin user see all databases?

This is because you have not restarted your MongoDB instance in auth mode.
Restart your MongoDB in auth mode in linux like this

mongod --auth --fork --logpath /var/log/mongod.log

Reference: Enable Auth

Remember if you have enabled auth mode for your MongoDB instance, you must have put it in your code and start the mongod process in auth mode.

To simplify this (authenticating the db) use Studio 3T (MongoDB Client) but you still need to start the instance with the --auth command line option.


DMV Queries as Perfmon Counters Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.

For example, take:

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;

Most monitoring tools know how to take an incrementing number and turn it into a rate since this is how SNMP counters work. Is anyone aware of any preexisting tools that turn these sort of stats into DMV queries?

This doesn't exactly answer your question, but Paul Randal included a very helpful query for aggregating wait stats with one of his surveys last December.  I have shamelessly copied and pasted it here, but am providing a link to his site, because there is a wealth of knowledge to be had from it.  Wait Stats

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO


How to write "Verify backup when finished" in T-SQL? In SQL Server one can cross an option under Reliability which says "Verify backup when finished", when one makes a backup through the management studio. How can I write it in T-SQL? (I want to write a job which do the backup so I need to put it all to code)

You can use ola.hallengren Backup scripts and schedule the job once you have created the required scripts from Ola's link:

In job you can use:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup',
@BackupType = 'FULL',
@Verify = 'Y'

Here as you can see Verify='Y' will do the job in addition to backing up the user databases.

The best approach would be above as you can log the history in table as well for the verification that is being done by using extra parameter in above code:

i.e. @LogToTable='Y' which Log commands to the table dbo.CommandLog.

Apart from this if you still like T-SQL that would be

RESTORE VERIFYONLY FROM DISK = B:\databasebackup.BAK
GO


Postgresql search in json type column In postgresql db I have a 'data' column which type is json. It looks like following:

{"columns":[{"name":"cusno","displayName":"Customer number","type":"text"},{"name":"cname","displayName":"Name","type":"text"},{"name":"cadd1","displayName":"Address","type":"text"},{"name":"cstte","displayName":"State","type":"text"}],"variables":[{"name":"var1", "type":"text"}, {"name": "var2","type":"text"}]}

I need to do search for example when user types 'cu' it should return rows which columns name or display name contains this word or variables name contains this word. How should i write the where clause ?

Test table & data:

create table jsontest
(
jsoncolumn json
);

insert into jsontest values ('{"columns":
[{"name":"cusno","displayName":"Customer number","type":"text"},
{"name":"cname","displayName":"Name","type":"text"},
{"name":"cadd1","displayName":"Address","type":"text"},
{"name":"cstte","displayName":"State","type":"text"}],
"variables":[{"name":"var1", "type":"text"},
{"name": "var2","type":"text"}]}');

Query:

with data as
( select json_array_elements( jsoncolumn ->'columns' ) as element
  from jsontest
)
select *
from data
where cast(element->>'name' as varchar)  like '%cu%'
or cast(element->>'displayName' as varchar)  like '%cu%';

Test:

postgres=# with data as ( select json_array_elements( jsoncolumn ->'columns' ) as element from jsontest )
select * from data where cast(element->>'name' as varchar)  like '%cu%' or cast(element->>'displayName' as varchar)  like '%cu%';
                            element
----------------------------------------------------------------
 {"name":"cusno","displayName":"Customer number","type":"text"}
(1 row)

postgres=#


Using MySQL ver. 5.5.55 in a production env Is it still OK to use this older version of MySQL on a production web server (LAMP on Ubuntu 14.04)? It's a low traffic volume business website. Loads fast enough. So far, no serious issues (like being hacked or something).
Also we use older version of PhpMyAdmin (ver. 4.0), so my question also regarding this too.

There are four reasons to upgrade the software:

Significant bug was removed
Security breach was removed
Hardware have
to be changed
Database structure was modified dramatically and
require some features not available in the older versions.

Updates performed just for software actuality are inappropriate in the production.

phpMyAdmin is well-known as unsecure software with lot of breaches so its location should be at least password-protected and restricted to the secure subnet(s).


how to restrict usage of datafile in Oracle We have several datafiles with a big amount of a free space.
How to shrink those files on production system. I tried to move all objects from this files into another tablespace, but RDBMS is still putting something there near MAX block number that makes "ALTER DATABASE DATAFILE RESIZE" impossible.
is there any option to block file to get create segments to make it possible to have resize as an empty file?

First, I'd check that it is not specified as the default TS for any user:
select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;
select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

-- Edit - add comments about quota
One other thing to check and that is user quota.  If a user has available quota on a TS, they can create segments in it even if it is not their default TS.
select *
from dba_ts_quotas
where tablespace_name='nameofyourtablespace';


Deleting the MySQL 'root' user on purpose I created a new mysql user with all the same privileges as the current 'root' user. For security reasons I thought why not create another user for this so people at least won't know the username of my super user.

I then dropped the root user.

Immediately my DB started throwing connection refused errors for all of my other users.

I quickly recreated the original 'root' user and everything magically started connecting again.

My question is then, is it possible to delete the root user in a MySQL database? And if so how?

Thanks.

EDIT 1:

All other security options are in place. We are not just securing our system by trying to remove the root user. We have some over zealous security people here and removing the MySQL root user was just an extra step.

I agree with @Pleun for the reasons I tried to remove it.

You can rename the 'root' user.

To rename the administrator’s username, use the rename command in the MySQL console:

mysql> RENAME USER root TO new_user;

The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:

mysql> use mysql;

mysql> update user set user=”new_user” where user=”root”;

mysql> flush privileges;

From here.


How to query time specific performance in postgres? The data in the pg_stat_statements table is really useful for me. I need to know the queries and the time they take. How can I perform a query to pg_stat_statements to get the "total_time" over the last 2 hours? I see no column for this.

I don't know of a canned tool to do this.  But you can easily create a table which has the same columns as pg_stat_statements and an extra timestamptz column, then have a scheduled task take a snapshot of pg_stat_statements into this table every 2 hours.  Then you can take the differences between adjacent snapshots of the same query.


Will Ola's default IndexOptimizer job update my stats? I just installed Ola Hallengren's MaintenanceSolution.sql maintenance scripts. After looking at the output of the log generated by the IndexOptimize - USER_DATABASES job, I'm concerned that it's not going to update the stats. The reason for my concern is that I see @UpdateStatistics = NULL in the parameters listed in the log output:

Date and time: 2013-07-16 13:58:24
Server: XXXXX
Version: 10.0.5512.0
Edition: Standard Edition (64-bit)
Procedure: [PROD-PH].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'N', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

The motivation behind this is that there is a query that runs once an accounting period (every four weeks) for the date range of the period. The query takes over 20 minutes if I do not manually run sp_updatestats. The query takes less than 5 seconds if the stats are current. The table in question has ~ 1.6M rows but is only growing by ~20K a week. As such, it is nowhere near the 20% auto threshold for a given four week period. Several other tables have a similar growth pattern.

My plan is to schedule this job to run once a week. Will it update the stats as required?

By default, the script does not perform statistics maintenance. The documentation for the script's parameters can be found here.

Value   Description
================================
ALL     Update index and column statistics.
INDEX   Update index statistics.
COLUMNS Update column statistics.
NULL    Do not perform statistics maintenance. This is the default.

So if you let the main script create your maintenance jobs for you, and you wanted to update index and column statistics, then you'd need to edit the IndexOptimize job and add , @UpdateStatistics='ALL' into the procedure call.


Pass multiples values to script into sqlplus in Oracle I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus.

With this select I have the values but I don't know how to pass this values.

 select column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec')))

 LOOP
   start script.sql column_name;
 END LOOP;

I don't understand your example. I have try to use but it doesn't works. I want to pass both values to "prueba.sql" (one in each execution)

cat prueba.sql
 prompt &1;

SQL> COL column_value NEW_VALUE v_column_value
SQL> select column_value column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec'));

COLUMN_VALUE
--------------------------------------------
Logons Per Sec
Executions Per Sec

SQL> @prueba.sql &&v_column_value
Executions

I want to execute a report inside the prueba.sql each time with one of this values as parameter.

Thanks

You can use COLUMN command in SQL*PLUS as shown below.

SQL> COL column_value NEW_VALUE v_column_value
SQL> select column_name column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec')));

SQL> @"test.sql" &&v_column_value

Documentation: COLUMN in SQL*PLUS


Oracle 11g: performance improvements of inserts I have a table of 500 millions of rows (and growing)

I did the following to improve performance of inserts:

On database side:

dropped all indexes and constraints
disabled logging

On application side:

switched from JPA managed entities to native insert queries, added APPEND Oracle hint to the query
tried to commit in batches per 1k/2k/3k of rows
tried to write in parallel (multiple threads, thread count = to core count on server) to one table

This gave me about 300 rows per second

Additionally tried:

write in parallel in batches to multiple tables (to group then back results using UNION)

This gave me about 1k rows per second, but on empty tables. But when I filled tables with dummy data (200 of millions each), speed of inserts dropped to 250 - 300 per second.

Could anyone suggest what else can I do to speed-up inserts? Basically I want to understand what is (what could be) the bottleneck first.

UPD:
Table is partitioned by insert date, table has about 60 columns - most of columns are VARCHAR2(2000 BYTE)

Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table.

First things first...

You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much time the DB process is taking AND how much (if any) wa% time the machine is showing.

If top is showing you ANY wa% time, that means your DB is I/O bound and you likely need to consider multiple DB machines (shards) or consider throwing SSDs on the host machine.

That's it; your research stops here. It doesn't matter how much CPU the DB was taking or how saturated your app client was; if you are hitting I/O latency issues on the host DB, that is as fast as it will EVER go for you.

TIP If hardware changes are out of the question, depending on the filesystem you are running (Linux) you can try disabling logging or metadata writing for the DB to slightly improve performance at the filesystem level. You can do something similar on NTFS, but this will only give you a little boost. This won't be 2x.

Now, second things second...

Let's say you had next to no wa% time but your CPU is pegged fully by the DB process. Your only option now is to introduce more DB machines (shards) and split the work.

Again, you're done with your research if this is the case. Nothing you can do to tweak the CPU to go faster.

Lastly, third things... third...

Let's say the DB isn't doing much of anything. Then, go to the client machine running the batch insert and check the CPU load -- is it pegged? If so, fire up some more machines doing the exact same batch inserts and see if you can get a linear ramp.

If the CPU isn't pegged, fire up some more threads on the same machine until it is pegged and see how the DB scales.

I think you may have already tried that, so my guess is that either your client host was already pegged (and more threads isn't going to make a difference) or the DB was already hitting its limit and can't scale any farther.

Addendum

Doing raw inserts on an unindexed table that has no garbage in it is essentially an APPEND operation which should be going as fast as the disk can handle the writes.

Creating more tables on the same host machine isn't going to help, if anything it will increase your disk seeks (to get to the other tables on disk to append to) and will slow things down.

It is critical to figure out that bottleneck first, then we can optimize the hell out of it.

Hope that helps! Keep us posted.


Integrity checks permissions issue I am running Hallengren's IntegrityChecks job which fails with the following error:

  CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'T:\SQL_Data\Example.mdf_MSSQL_DBCC9'.

SQL Server Agent service account is NT Service\SQLAgent$INSTANCENAME, which has full access to the T drive including all sub folders.

Any idea how to overcome this?

Thanks

SQL Server Agent will schedule a job and cause it to be run by the SQL Server service - the database engine. The account which is trying to create this snapshot for your CHECKDB to be run concurrently with users in it is not the Agent service, but the SQL Server service. So look to see who your SQL Server service is running as and what permissions that account has.


Compare the structure of two MySQL Databases I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table?

the databases are in different schema.
It's only the structure I want to compare, not the data.

Using INFORMATION_SCHEMA.COLUMNS, here is my proposed query

SELECT B.* FROM
(
    SELECT DISTINCT table_name FROM
    (
        SELECT table_name,column_name,ordinal_position,data_type,column_type,COUNT(1) match_count
        FROM information_schema.columns WHERE table_schema IN ('db1','db2')
        GROUP BY table_name,column_name,ordinal_position,data_type,column_type
        HAVING COUNT(1) = 1
    ) AA
) A INNER JOIN
(
    SELECT table_schema,table_name,column_name,ordinal_position,data_type,column_type
    FROM information_schema.columns WHERE table_schema IN ('db1','db2')
) B;
USING (table_name)
ORDER BY B.table_name,B,table_schema;

The output will be each the columns differences. You will see differences by data type, column type, and/or column position. You should quickly see if a table only appears in one one database and not another.


Transaction and data consistency during a failure When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable).  These are pretty much the definition of a transaction.

Wikipedia states that there are some databases that insure a transaction remains isolated by locking the rows and not unlocking them until the transaction has committed.

My question is:  How can a database that solely relies on locking guarantee consistency?  If a power outage occurs mid-transaction, there may be data partially written to the row.

Even for databases like SQL Server that use a Temporary DB to perform all the transactions, what happens if a power outage occurs as the database is committing the transactions to disk?

Does the data become corrupted or does the database handle that correctly?

Bear with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID.

  When a database begins a transaction, all statements executed in that
  transaction are isolated and atomic (and consistent and durable).
  These are pretty much the definition of a transaction.

The isolation part of ACID is widely misunderstood. There is a degree to which transactions are isolated from each other, as determined by the transaction isolation level. The other elements of ACID are absolute.

  Wikipedia states that there are some databases that insure a
  transaction remains isolated by locking the rows and not unlocking
  them until the transaction has committed.

This relates to the isolation part of ACID, it doesn't have any impact on your main question.

  My question is: How can a database that solely relies on locking
  guarantee consistency? If a power outage occurs mid-transaction, there
  may be data partially written to the row.

Your example is not concerned with consistency, it's durability and atomicity. These are guarenteed by write ahead logging (WAL). With WAL, all changes are written to the undo/redo log before they are applied to the data.

In the event of a power failure, a recovery process is run which will read the log to identify a) "mid-flight" transactions that did not commit and b)  transaction that did commit but which were not applied to the data. The changes from a) transactions are undone (rolled back), returning the data to its pre-transaction state. The changes from b) are redone (rolled forward), ensuring the data is in the expected post-transaction state.

  Even for databases like SQL Server that use a Temporary DB to perform
  all the transactions, what happens if a power outage occurs as the
  database is committing the transactions to disk?

TempDB (assuming that's what you're referring to) has absolutely nothing to do with SQL Servers execution of transactions. Are you confusing the role of TempDB in snapshot isolation levels?


Can't create an Availability Group with Powershell when instance uses non-standard port I am trying to create an Always On Availability Group with PowerShell, something I have done many times before but am hitting an issue now that I am using non-standard ports for the SQL Instance

I generally use a slightly modified version of this script

But I get an error saying that the current instance SERVER\INSTANCE,Port must be added to the availability group before it can be created. Now obviously that is the value I pass in at the top of the script

$SqlServerPrimName = "SERVER\INSTANCE,Port"

Which is then used to create a SMO object

$SqlServerPrim = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerPrimName)

And then added to the Availability Group here

$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)

But it obviously is not working!

I notice also that if you try and call $SQLServerPrim it comes back empty for InstanceName even though the object is correctly loaded with other values

Creating an Availability Group through the GUI works

Using the New-SQLAvailability* Cmdlets have the same error

Any idea what I need to change in the code to make this work?

Change $SqlServerPrimName = "SERVER\INSTANCE,Port" to $SqlServerPrimName = "SERVER\INSTANCE" and change the port for the instance afterwards.

$ComputerName = gc env:computername
$SqlServerPrimName = "SERVER\INSTANCE"
$SQLServerPort = 1433
$wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $computerName
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].value = "$portNumber"
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpDynamicPorts"].value = [System.String]::Empty
#We need to commit the changes by calling the Alter method
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].Alter()


Multi master replication with different database I have three database server.

ip=192.168.1.101 db=google
ip=192.168.1.102 db1=google db2=yahoo
ip=192.168.1.103 db1=google db2=yahoo

I want to do master replication between server1 and server2 for db=google
and
master master replication between server2 and server3 for db=yahoo

Can I have a suggestion or reference links to do that?

I assume you have M1 -> M2 -> M3 -> M1  ?

On M1, have the tables in yahoo be ENGINE=BLACKHOLE.


How do I solidify my SQL Server / T-SQL Knowledge? I am a C# developer and not a database admin, but I have been using SQL Server for application developnment for over 4 years now and was really interested in getting a more complete understanding, and filling the gaps in my knowledge.

I read the training kit and passed the 70-461 exam today, learnt a ton of things from that book which I had not known or used before.

My question is, I would like to become proficient in all of what I have learn't, so that I know it like the back of my hand. It seems like I can see the correct answers from a list of choices but writing the T-SQL from scratch is a challenge.

How would I go about doing that? Keeping in mind the developer jobs will always use a small subset of the functions, is there any way I can practice everything, and become better at it instead of forgetting everything I have just learnt?

There are probably a thousand good answers here and only some of them will apply to your learning style and situation. If you were able to read the 70-461 book and then use it to immediately pass the test, then you've already got a pretty good foundation. The question really becomes, what is your personal learning style and which methods apply to your learning style?

For me personally, I tend to read about a new concept and then try to apply it as soon as possible. Let's say for example that I have read about Window functions in the 70-461 book. The concepts are abstract and hazy at first and the words accompanying the explanation make sense but they don't have any solid value in my bank of experience. What I will try to imagine is a single scenario in which a Window function would be useful.

Sometimes I think back to my early days of writing SQL and wonder if I could have applied a better technique such as a Window function. Sometimes I'll actually dig up old queries and rewrite them. I will spend a lot of time comparing the results of the old query and the new query and the performance metadata. Some questions I will ask myself are: Was I able to perform the same task quicker and with fewer resources?

Also, one of my favorite techniques is to imagine a problem and try to solve it in at least 3 different ways. Let's say I need to create a time series comparing one year of traffic data to the previous year of traffic data. It turns out that there are at least 3 (maybe 4) ways to do this. Some are faster, some are slower, some are easier to write. I will try to master them all and understand them all. I never feel completely satisfied with a query. I will always leave open the possibility that I can improve upon a prior technique.

Another method I like to use is to find some old iterative code involving queries (something that runs a query 20000 times in a loop) and then try to come up with a set-based method for  producing the same results. Like you, I started out as an imperative language programmer and stumbled upon sets later in life. I didn't have a framework for how to work with sets and now I do. At work, I often run across old stored procedures that have 1000 lines of code using lots of materialized tables and loops. What I like to do is reduce them down to a single query of 100 lines. I don't know how much that will help you, but trying to re-solve old problems tends to enrich my learning experience.

As for administration itself -- I can only recommend that you download the virtual machine for the Windows Server trial edition and install the trial or CTP SQL Server editions over and over again and in as many ways as you possibly can. You might have to learn a lot more about Windows administration than you ever hoped possible, but that's the life of a Production DBA.

As for SSIS and SSAS and SSRS -- it's challenging to come up with imagined scenarios for those. There are probably a ton of packages out there for the AdventureWorks and other sample databases that you can play with. I find for those that it's extremely helpful to go through online tutorials. Try Microsoft's Virtual Academy or lynda.com to get started or even YouTube has a million video tutorials.

To answer your secondary question, how to prevent forgetting ... Reinforce those neural pathways in your brain by returning to them over and over. Every time your spark a synapse it'll get stronger and the memory of the process will become more focused/permanent. Try to learn as much as you can, as holistically as you can, with as much context as you can and keep applying it as often as you can.

PS, I was a teacher for 4 years and I took many pedagogy courses to specifically understand learning techniques and styles. The best learning environment is probably a low stress, contextual environment that tolerates mistakes -- i.e., a sandbox. So build yourself a sandbox where you can play and make as many mistakes as necessary.


Slow mysqldump on big table I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file.
using mysqldump --opt --add-drop-table  --quick DATABASE BIG-TABLE >DATABASE.TABLE.sql to export the table ends up with poor read performance of around 8-15 MB/s with high iowait.
more details:

Not a destination disk issue, the destination disk is SSD as well with 120.00 MB/s writes (tested)
Not a source disk issue, according to GCP and dd the disk (sdb) is SSD volume with 180.00
MB/s reads.

time dd if=BIG-TABLE.ibd of=/dev/null status=progress
2886054912 bytes (2.9 GB) copied, 17.044179 s, 169 MB/s^C
5709061+0 records in
5709060+0 records out
2923038720 bytes (2.9 GB) copied, 17.237 s, 170 MB/s

iostat:

iostat -m -x 5 -d /dev/sdb
Linux 3.10.0-957.1.3.el7.x86_64 (sql-server)      12/26/2018      _x86_64_        (8 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     4.91  170.00   29.18    19.75     0.73   210.57     3.78   19.00   21.78    2.77   4.92  98.08

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  103.60    0.00     7.33     0.00   144.99     1.68   16.31   16.31    0.00   9.43  97.66

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  145.60    0.00    12.71     0.00   178.81     1.90   13.03   13.03    0.00   6.75  98.22

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  140.00    0.00    12.73     0.00   186.24     2.03   14.52   14.52    0.00   6.98  97.78

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  172.40    0.00    17.16     0.00   203.84     2.81   16.16   16.16    0.00   5.69  98.14

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00   94.20    0.00     6.56     0.00   142.61     1.37   14.79   14.79    0.00  10.41  98.02

iotop

cat my.cnf:

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table=ON
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=3G
innodb_log_file_size = 2G
innodb_old_blocks_time=1000
innodb_stats_on_metadata=off
innodb_log_buffer_size = 5M
bulk_insert_buffer_size = 1G
skip_name_resolve = ON
thread_cache_size=8
max_allowed_packet=4G
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

general-log   = off
server-id       = 1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

show create table:

| BIG-TABLE | CREATE TABLE `BIG-TABLE` (
  `major` int(11) NOT NULL,
  `minor` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `attributeid` int(11) NOT NULL,
  `value` varchar(128) NOT NULL,
  `longvalue` mediumtext NOT NULL,
  UNIQUE KEY `componentIndex` (`major`,`minor`,`version`,`attributeid`),
  KEY `componentAttrIdx` (`attributeid`),
  KEY `componentValIdx` (`value`,`attributeid`,`major`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Look at this bugreport: https://bugs.centos.org/view.php?id=15723

Long story short: kernel 3.10.0-957 shows much higher disk util than the previous kernel


Query To Return Values Where NOT All Text I have a field that is varchar(100) - if the field is all text, I want to ignore it, if it is alpha-numeric I want to return the data.  This is sample DDL and my ISNUMERIC() excludes both values (I assume because they contain text with the numbers).

How should this be altered in order to return any value that is not just text?

Declare @Table Table (field1 varchar(100))
Insert Into @Table Values
('1ZCLJKSDFLKSJDF'),
('0301190XFS3'),
('Pink Socks Run Free'),
('White Goats Stink')

Select * from @table where ISNUMERIC(field1) >= 1

Fairly simple:

Select * from @table where field1 LIKE '%[0-9]%'


why a session becomes a head blocker I am troubleshooting a problem that seems to be random

The problem is sometimes an application (dynamics SL) is freezing

When the application is freezing , I notice there is a blocked by in SQL activity monitor
and a head blocker

in my limited understanding , head blocker means a session is currently running and is locking a resource and that resource is also needed by another session. so all sessions that need access to that resource cannot continue thus the "blocked by" that I see in activity monitor

my question is
in five cases that I have seen is that the head blocker task status is actually blank. usually a task status is "running" or "suspended"
now how can a session that is not running becomes the head blocker?

I am thinking my understanding of head blocker maybe wrong. but please enlightened me ELI5 style :)

each of the 5 cases. I just kill the process of the head blocker. then everyone is not freezing anymore.

I am hoping to identify the source of this head blocker and come up with a solution

When I click the details of the head blocker. all I see is

select @@identity

please advise?

thank you

Actually in order to be blocking a session only has to hold a lock to a resource something else wants.  It does not actually have to be doing anything.  For example if you run the following

BEGIN TRANSACTION
    UPDATE MyTable SET Col1 = 'ABC'

And don't run a COMMIT then  you are going to leave a transaction open.  The session is not doing anything and in fact it will not show up in sys.dm_exec_requests.  However the locks are going to continue to be held and continue to block other sessions.  You can find the information on open transactions in sys.dm_tran_session_transactions.

Next point is the command you are seeing.  That is just the last batch of the session.  If you open a transaction and run several batches through it you may very well be holding locks from earlier commands that are not going to show up.

I wrote about both of these things in quite a bit more detail in the following links:

Transactions: Who What and Where

Using sys.dm_exec_sql_text to figure out blocking is sometimes flawed.

Also the first link will have information on how to find who is running/ran the offending transaction.  I frequently find that if the login is a shared one, or a SQL ID I can track the individual down by using the information in the host_name column of sys.dm_exec_sessions.


What is a good way to copy data from one Cassandra ColumnFamily to another on the same Keyspace (like SQL's INSERT INTO)? Trying to find a way to easily transfer all the rows from a Cassandra ColumnFamily/Table to another.

The COPY command, as I understand, is a good option.  However, as it dumps all the data to .csv on disk and then loads it back, I can't help but wonder if there is a better way to do it in-engine.

A specific example of what I mean would be the INSERT * FROM my_table INTO my_other_table available in many SQL databases.  Of course, I realize that Cassandra is NoSQL and therefore does not to work the same way - but it seems like something which might be available.

What is a good way to accomplish this?

Thanks very much!

cqlsh -k mykeyspace -e 'COPY fromTable(columnNames) TO STDOUT' | head -n -1 |
cqlsh -k mykeyspace -e 'COPY toTable(columnNames) FROM STDIN'


Is it possible to edit sys.messages texts? Is it possible to add a new language to SQL Server 2008 and edit SQL error messages with message_id less than 50000?

I don't think it is possible or advisable to edit messages below 50000 in SQL server. You can create user defined messages for ids greater that 50000 using sp_addmessage.

Another possibility is to use RAISERROR to return dynamic messages. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.


MSSQL - Limit Query to last 5 records of group by I have a data table that is expected to grow at about 20,000 records a day

I'm wanting to sum the last five 'detector_record_status' for each 'installation_id''s 'detector_id'.

If the sum of the last 5 (detector_record_status) is >=3 then its deemed Active.

eg
installation_id
detector_id
Active = sum(detector_record_status) => 3

installation_id|  detector_id  |  Status
            1  |            8  |  Active

I do how to limit to 5 records for each 'detector_id' of the 'installation_id'
And to show 'Active' rather than the sum(detector_record_status)

I've created a sqlfiddle
http://sqlfiddle.com/#!18/3f65f/3/0

You can accomplish this using a Common Table Expression (CTE) that assigns a row number to each detector_record_id partitioned on each installation_id and detector_id.

Order it descending and return the top 5 in your outer query. This grabs the most recent 5 records (based on detector_record_id) per installation_id and detector_id. The outer query then sums the detector_record_status and a simple HAVING clause eliminates those that don't meet the "Active" threshold.

As your table grows, it will become important to monitor the inner SELECT performance as it will be returning all rows from detector_record and this could become slow to execute.

Example

;WITH dRecords AS (
  SELECT installation_id,
    detector_id,
    detector_record_status,
    ROW_NUMBER() OVER (PARTITION BY installation_id, detector_id ORDER BY detector_record_id DESC) AS [Rn]
  FROM detector_record
)
SELECT
  installation_id,
  detector_id,
  SUM(detector_record_status) as Activity
FROM dRecords
WHERE [Rn] <= 5
GROUP BY   installation_id, detector_id
HAVING SUM(detector_record_status) >= 3
ORDER BY   installation_id


sp_refresh_parameter_encryption fails to refresh objects I am testing always encrypted feature and once the operation of encrypting column is done I have a file with hundreds objects failed to be refresh by the sp_refresh_parameter_encryption procedure.

Should I investigate these errors and perform the refreshing by myself? What are the risks of not doing this?

Generally, you need to check each object this system routine do not refresh.

It is a good start point to locate routines that are referring the new encrypted columns in not correct way - for example, performing scalar functions on the columns (cast/convert, aggregate, string functions or concatenation, etc.).

If you do not do this, you risk your application to fail when the encryption is applied on the production database.

It also seems, the engine is not capable of checking/refreshing the CLR routines. In my case, I was completely sure that my CLR objects do not work with encrypted data and just ignored them.


Insert into value as generate row with different minutes I try backup and restore using WAL-E/WAL-G.

I can backup and restore.

But i want check recovery_target_time.

During recovery, by default WAL-E will attempt to pull and restore all WAL segments available. If you wish to restore to a specific POINT IN TIME than specify.

# restore to 4:38PM on 3/6/2012
recovery_target_time = '2012-03-06 16:38:00'

I think need generate value every minute long time and Insert into table.

Found insert events with CURRENT_TIMESTAMP

INSERT INTO events (ts,description) VALUES (CURRENT_TIMESTAMP,'disc full');

Found insert SELECT NOW()

INSERT INTO indexing_table(created_at) SELECT NOW() FROM generate_series(1,100);

This is no answer :(

How build table?

         created_at
-------------------------------
2019-02-13 14:16:20.799219+03
2019-02-13 14:17:20.799219+03
2019-02-13 14:18:20.799219+03
2019-02-13 14:19:20.799219+03

or

    created_at
-------------------
2019-02-13 14:16:20
2019-02-13 14:17:20
2019-02-13 14:18:20
2019-02-13 14:19:20

Need generate row with different minutes.

Any help would be appreciated in creating these tables!
Thanks!

I am not entirely sure what your question is, but if you are confused that all rows have the same timestamp value, this is because now() and current_timestamp return the time at the start of the transaction.

Quote from the manual

  Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp

And then:

  clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

So if I understood your question correctly, you probably want to use clock_timestamp() rather than now()

Another option is to use generate_series() to generate timestamps:

INSERT INTO indexing_table(created_at)
SELECT *
FROM generate_series(timestamp '2019-01-01 00:00:00',
                     timestamp '2019-03-31 00:00:00',
                     interval '1 minute') ;


What kind of data type to use for numbering in the Harmonized System? The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example:

01  LIVE ANIMALS
0103  swine, live
010310  Purebred breeding animals

For each level there are two digits available, and this can go down in my case to up to eight digits in total (so four levels). I would like to catalog the harmonized system codes and their descriptions, but wonder what a good data type is to use in MySQL. These numbers are unique so I would like to make them my Primary Keys as well. Given the hierarchy, I should be able to do some SELECTs that allow me to find out the child records. What I am thinking of is using some sort of INT type, and then doing things like this:

SELECT * FROM accounts WHERE CAST(id AS TEXT) LIKE '01%'

Which should allow me to find the child accounts that fall under the '01' category. The problem here would be to work with the leading zeros. If I use zerofill, then 0103 will become 00000103, so that is not ideal. But if I do not use zerofill then this will be 103. Again not ideal.

Using a text data field instead would solve this, but this feels quite wrong as these are clearly numbers. Alternatively I could make several columns - each for the number group - but this again feels like a weird solution. Any advice is appreciated.

"The harmonized system uses a numbering" - they are not numbers, they are codes.  They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit.  You have to go with textual types.  Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text?  Just think of them this way, but with the A, B and C removed.  Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.


How do I "link" two or more rows of the same table with a unique "link" id? I am looking for a better way to create a link between two or more rows of the same table, but the link itself must have a unique ID. What I am currently doing seems rather convoluted. I have a positions table that I am "linking" via the table linked_positions, but using yet a tertiary link via the linked_positions_1nf table. Perhaps it will be easier to see the structures with some sample data...

mysql> SELECT id FROM positions WHERE id IN (104, 105);
+-----+
| id  |
+-----+
| 104 |
| 105 |
+-----+

mysql> SELECT id, linked_ids position_ids FROM linked_positions LIMIT 1;
+----+--------------+
| id | position_ids |
+----+--------------+
|  1 | 104, 105     |
+----+--------------+

mysql> SELECT * FROM linked_positions_1nf WHERE link_id = 1;
+-----+---------+-------------+
| id  | link_id | position_id |
+-----+---------+-------------+
| 123 |       1 |         104 |
| 124 |       1 |         105 |
+-----+---------+-------------+

It seems like it would be more ideal to only have one linking table to achieve the above, but I haven't been able to find a more refined solution. Thanks in advance for any guidance.

If the ID in your 'link' table absolutely has to be unique, and if it's ALWAYS a link between two IDs, you could use 3 columns: ID, min_position, max_position. That would allow you to use joins with your positions table.

I'm a bit confused regarding the goal of your application, i must admit ;).


How can I calculate the cost of the query, if there is a `between` condition? How can I calculate the cost of the query, if there is a between condition?

Select *
From A
Where A.id between 10 and 50

If Index not exists and there is only one condition:

Search in B+ tree to find the first block that meets the condition (Binary search).
Cost: log_2(b_A).
Search in the following blocks that meets the condition. (Suppose half of the blocks meet the condition). Cost: b_A/2.

If the condition was Where A.id > 10, then the cost would be: log_2(b_A) + b_A/2
If the condition was Where A.id < 50, then the cost would be: log_2(b_A) + b_A/2

What is the correct way to calculate cost of 2 ranges of the same field?

If there is no index for A.id column, then database engine perform full table scan.  So O(N) operations are performed.

If there is a B+tree index for A.id then database optimizer may choose it, if it is better than full table scan.

In a B+tree all the leaf nodes/records are linked; a node may have more than 2 elements.  Because all the records/leaves are linked in order (asc|desc), to get matching value for given range, it is sufficient find the min record location then we can iterate sequentially till max value.

For a b-order B+ tree, finding a record requires O(logb(N)). If there is a m matching elements for (10,50 ) range, if we assume DB engine follow the above said method, then it requires O(logb(N))+O(m) operations to find matching rows.


SQL Server log shipping: How to give SQL Agent the needed rights to remote share? This is an old topic, and somehow I have not been able to find a clear answer.

Setup: Two stand alone SQL Servers. They have full network visibility to each other. They are NOT in a domain. There is NO domain available. (I am using SQL Server 2014, but this exact issue applies at least back to SQL 2005.)

Everything "works" ... the only piece missing is that the sql job that tries to copy the log backup fails with this error:

Error: Cannot open backup device '\\sql2\sqllogs\NORTHWND_20150209053800.trn'.
Operating system error 5(Access is denied.).

SQL Agent is running as the "NT Service\SQLSERVERAGENT" account. On SQL2, I have given this user full rights to \sql2\sqllogs\ share and folder.

The SQLSERVERAGENT account on both systems has the same password, and the agent has been restarted after setting that password.

I have seen some folks solve this by making SQLSERVERAGENT a member of the administrators group on the OS, but that does not sound like a safe path.

What is the correct way to solve this?

Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions.

http://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager.

https://msdn.microsoft.com/en-us/library/ms191543.aspx

To set the Service Startup Account for SQL Server Agent:

In Registered Servers, click the plus sign to expand Database
Engine.
Click the plus sign to expand the Local Server Groups folder.
Right-click the server instance where you want set up the Service
Startup Account, and select SQL Server Configuration Manager….
In the User Account Control dialog box, click Yes.
In SQL Server Configuration Manager, in the console pane, select SQL
Server Services.
In the details pane, right-click SQL Server Agent (server_name),
where server_name is the name of the SQL Server Agent instance for
which you want to change the service startup account, and
select Properties.
In the SQL Server Agent (server_name)Properties dialog box, in
the Log On tab, select one of the following options underLog on as.

https://msdn.microsoft.com/en-us/library/ms186264.aspx#SSMSProcedure

You must grant read permissions on this network share to the proxy account under which the copy job will run at the secondary server instance.

By default, this is the SQLServerAgent service account of the secondary server instance, but an administrator can choose another proxy account for the job.

Network path to the download folder


Why turning off condition_fanout_filter speed up query bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &

Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#52'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 4
         AND l_quantity <= 4 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#11'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 18
             AND l_quantity <= 18 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#51'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 29
             AND l_quantity <= 29 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' );

condition_fanout_filter=off
RESULT:  1 row in set (13.73 sec)

explain:
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |   100.00 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

condition_fanout_filter=on
RESULT:  1 row in set (30.57 sec)

explain:
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | SIMPLE      | PART     | NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                | 198000 |     1.33 | Using where |
|  1 | SIMPLE      | LINEITEM | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | tpch.PART.P_PARTKEY |     30 |     0.67 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+

From column 'filtered', "condition_fanout_filter=on" should be faster.
Other important things
this only happens in the 15k RPM SAS 600GB HDD and with --innodb-flush-method=O_DIRECT.
Either using an SSD or not set O_DIRECT, this problem would disappear.

you have one of the cases where the optimizer searching for a good solution costs much more time than the query it self, but that can change with increasing numbers.
You should read about what this optimization switch does.
if you need more speed you can try if one of https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html work better. additinal to different indexes


How to get last inserted rows where a condition is satisfied table1:

esps
--------------
A
B
C
--------------

table2:

id      _name       esp
------------------------------
1       Ana        A
2       Ana        B
3       Mario      A
4       Jorge      A
5       Jorge      C
6       Ana        A
------------------------------

My query:

SELECT * FROM table2 where esp = 'A' group by _name having id = max(id);

Query result:

id      _name      esp
-----------------------------
3       Mario      A
4       Jorge      A
-----------------------------

Expected result:

id      _name      esp
-----------------------------
6       Ana        A
3       Mario      A
4       Jorge      A
-----------------------------

Any advice or sugestion will be appreciated. Mysql version 8.0.16

If you want the 'last' A:

SELECT * FROM table2
    where esp = 'A'
    ORDER BY id DESC
    LIMIT 1;

If you need something more complicated, see "groupwise-max".


6 datacenters running the same mysql database, replication layout design question So each of the datacenters is around the conus. The Master slave replication route seems like my best option here. I have never done this with more then 1 slave and 1 master. Advice? Links?

The commits to each DB are around 2/second with some spikes of 20/second. ID consistency is my main concern with this. They each have different rates of commits that vary a lot.

20 commits/second is not very fast.

A Master can have any number of Slaves.  Those Slaves can be local to the same datacenter or remote, in the other datacenters.

You can have "relay" servers that act like a Slave to the Master, but then act like a Master to 'downstream' Slaves.  With this kind of topology, you can have an unlimited number of Slaves.  (I have not seen it more than 3 level deep including the Master.  Nor have I seen more than a few dozen Slaves total.  But no "limit" was being hit.)

Multiple Masters is a much different matter.  Usually the best topology is to have two Masters, one being written to and the other being a hot backup.  This is "Dual Master, single writer".  Each of them can have Slave(s).

Even better is Galera Clustering -- See MariaDB or Percona (PXC).  It allows for 3 or more writable masters and handles most of the nasties that led me to say "single writer" in the previous paragraph.  Again, these "nodes" can have "Slave(s)" hanging off them.

Keep in mind that a Slave can only be read from.  To "write" you need to hit a Master.  If the master is on the "other side of the country (or world)", there are speed-of-light delays, upwards of 100ms to go across the US.

The delays do not necessarily impact the "20 commits/sec".  At 100ms, you cannot perform more than 10 commits/sec from a single connection.  But, you probably have multiple connections, correct?

At 100 commits/sec, some disk tuning issues come into play.  At 1000/sec, other issues raise their ugly heads.  Some carefully crafted benchmarks have run at 1M/sec.

You have not explained what you mean by "ID consistency".  One thing comes to mind:  When writing to both Masters in a Dual-Master setup, you are asking for this kind of trouble.


How does CALs Licenses work with Azure SQL I looked all over the web for concrete information regarding CALs licenses in Azure  SQL but couldn't find much event on Microsoft Azure site.
Is this included within subscription ?
Or
Is it part of Azure Active Directory ?

There are no license restrictions in Azure SQL Database. No CAL licenses are required.  You pay for the database, and you may use it for any kind of application you wish and for any number of hosts or users. You only need to take in consideration Azure SQL Database limits for the maximum number of concurrent sessions allowed on each tier. Here are the limits for the DTU model and here you will find limits for the vCore model.


concatenate two columns into one I am using Oracle SQL Developer and am trying to concatenate two columns into one for each row of students.  Under pivot below, I have "1" and "1A" separately but would like to show both into one column.

SELECT
  *
FROM (
    SELECT
      students.student_number   id
     ,students.last_name ||',' ||students.first_name student
     ,students.grade_level      gr
     ,sections.external_expression
     ,teachers.last_name        class
    FROM cc
      JOIN sections ON cc.sectionid   = sections.id
      JOIN courses ON sections.course_number   = courses.course_number
      JOIN teachers ON sections.teacher   = teachers.id
      JOIN students ON cc.studentid   = students.id
    WHERE
      SYSDATE BETWEEN cc.dateenrolled AND cc.dateleft
      AND cc.schoolid         = sections.schoolid
      AND sections.schoolid   = '300'
  )
    PIVOT (
      LISTAGG ( class,'/' ) WITHIN GROUP ( ORDER BY class )
      FOR external_expression
      IN ( '1A(A) 1B(A)' "1",'1A-2A(A)' "1A",'2A(A) 2B(A)' "2"
          ,'1B-2B(A)' "2A",'3A(A) 3B(A)' "3",'4A(A) 4B(A)' "4"
          ,'5A-5B(A)' "5",'5A(A) 7A(A)' "5A",'7A-7B(A)' "7"
          ,'5B(A) 7B(A)' "7A",'8A(A)' "8",'9A(A)' "9",'10A(A)' "10" )
    )
ORDER BY student

CONCAT for string concatination.

Note: PIVOT, SYSDATE also isn't a syntax in MySQL

MariaDB-10.3 has an ORACLE MODE which might make your transition easier.


Tables names in a supertype/subtype schema I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited information about each user. I then have 3 tables for each user type: admin, reader, approver.

Now, the design I have gone with thus far has been something like this:

General Tables (don't pertain to users)
e.g.,
country
city

Tables That Pertain to all Users
e.g.,
person_contact
person_contact_category

Tables That Pertain to User Type
e.g.,
approver_approve_for
reader_can_view

Now, based on this information, I am wondering a few things:

Global tables do not get a prefix. Is it considered bad practice to prefix the tables that pertain to a subset with the subset name (e.g., admin_)? I figured it makes it easy to look at the DB design and see exactly what subtype each table pertains to, but the next question is what made me ask this question at all.
The table person_contacts_categories_map is a junction table between the many categories a person can have, and the many contacts in those categories. OK, typically I name my junction tables something like contact_category. Unfortunately, there are more things that get categories in this app than just contacts, thus I am saying this table relates to all persons (e.g., person_) and relates to contacts (contact_). I was thinking then that the junction table should be named person_contact_person_contact_category, but that is just horrible.

So, given what I've laid out, does anyone know a better approach to this problem, or is what I am doing fine?

Instead of changing the names of individual tables, I would recommend using schema names to denote the different types of users.

For instance:

Admin.Users
Reader.Users
Approver.Users

Using Schemas in this way allows a more flexible method for controlling access since you can assign rights by schema instead of at the individual table level.


Why is a scan faster than seek for this predicate? I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals.

On my machine, the following query does a clustered index scan and takes about 6.8 seconds of CPU time:

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

The following query does a clustered index seek (only difference is removing the FORCESCAN hint) but takes about 18.2 seconds of CPU time:

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

The query plans are pretty similar. For both queries there are 120000001 rows read from the clustered index:

I am on SQL Server 2017 CU 10. Here is code to create and populate the two_col_key_test table:

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

I am hoping for an answer that does more than call stack reporting. For example, I can see that sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataX takes significantly more CPU cycles in the slow query compared to the fast one:

Instead of stopping there, I'd like to understand what that is and why there's such a large difference between the two queries.

Why is there a large difference in CPU time for these two queries?

Why is there a large difference in CPU time for these two queries?

The scan plan evaluates the following pushed non-sargable (residual) predicate for each row:

[two_col_key_test].[ID1]<>N'1'
AND [two_col_key_test].[ID1]<>N'10'
AND [two_col_key_test].[ID1]<>N'11'
AND [two_col_key_test].[ID1]<>N'12'
AND [two_col_key_test].[ID1]<>N'13'
AND [two_col_key_test].[ID1]<>N'14'
AND [two_col_key_test].[ID1]<>N'15'
AND [two_col_key_test].[ID1]<>N'16'
AND [two_col_key_test].[ID1]<>N'17'
AND [two_col_key_test].[ID1]<>N'18'
AND [two_col_key_test].[ID1]<>N'19'
AND [two_col_key_test].[ID1]<>N'2'
AND [two_col_key_test].[ID1]<>N'20'
AND [two_col_key_test].[ID1]<>N'3'
AND [two_col_key_test].[ID1]<>N'4'
AND [two_col_key_test].[ID1]<>N'5'
AND [two_col_key_test].[ID1]<>N'6'
AND [two_col_key_test].[ID1]<>N'7'
AND [two_col_key_test].[ID1]<>N'8'
AND [two_col_key_test].[ID1]<>N'9'
AND
(
    [two_col_key_test].[ID1]=N'FILLER TEXT'
    AND [two_col_key_test].[ID2]>=N''
    OR [two_col_key_test].[ID1]>N'FILLER TEXT'
)

The seek plan does two seeking operations:

Seek Keys[1]:
    Prefix:
    [two_col_key_test].ID1 = Scalar Operator(N'FILLER TEXT'),
        Start: [two_col_key_test].ID2 >= Scalar Operator(N'')
Seek Keys[1]:
    Start: [two_col_key_test].ID1 > Scalar Operator(N'FILLER TEXT')

...to match this part of the predicate:

(ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))

A residual predicate is applied to rows that pass the seek conditions above (all rows in your example).

However, each inequality is replaced by two separate tests for less than OR greater than:

([two_col_key_test].[ID1]<N'1' OR [two_col_key_test].[ID1]>N'1')
AND ([two_col_key_test].[ID1]<N'10' OR [two_col_key_test].[ID1]>N'10')
AND ([two_col_key_test].[ID1]<N'11' OR [two_col_key_test].[ID1]>N'11')
AND ([two_col_key_test].[ID1]<N'12' OR [two_col_key_test].[ID1]>N'12')
AND ([two_col_key_test].[ID1]<N'13' OR [two_col_key_test].[ID1]>N'13')
AND ([two_col_key_test].[ID1]<N'14' OR [two_col_key_test].[ID1]>N'14')
AND ([two_col_key_test].[ID1]<N'15' OR [two_col_key_test].[ID1]>N'15')
AND ([two_col_key_test].[ID1]<N'16' OR [two_col_key_test].[ID1]>N'16')
AND ([two_col_key_test].[ID1]<N'17' OR [two_col_key_test].[ID1]>N'17')
AND ([two_col_key_test].[ID1]<N'18' OR [two_col_key_test].[ID1]>N'18')
AND ([two_col_key_test].[ID1]<N'19' OR [two_col_key_test].[ID1]>N'19')
AND ([two_col_key_test].[ID1]<N'2' OR [two_col_key_test].[ID1]>N'2')
AND ([two_col_key_test].[ID1]<N'20' OR [two_col_key_test].[ID1]>N'20')
AND ([two_col_key_test].[ID1]<N'3' OR [two_col_key_test].[ID1]>N'3')
AND ([two_col_key_test].[ID1]<N'4' OR [two_col_key_test].[ID1]>N'4')
AND ([two_col_key_test].[ID1]<N'5' OR [two_col_key_test].[ID1]>N'5')
AND ([two_col_key_test].[ID1]<N'6' OR [two_col_key_test].[ID1]>N'6')
AND ([two_col_key_test].[ID1]<N'7' OR [two_col_key_test].[ID1]>N'7')
AND ([two_col_key_test].[ID1]<N'8' OR [two_col_key_test].[ID1]>N'8')
AND ([two_col_key_test].[ID1]<N'9' OR [two_col_key_test].[ID1]>N'9')

Rewriting each inequality e.g.:

[ID1] <> N'1'  ->  [ID1]<N'1' OR [ID1]>N'1'

...is counterproductive here. Collation-aware string comparisons are expensive. Doubling the number of comparisons explains most of the difference in CPU time you see.

You can see this more clearly by disabling the pushing of non-sargable predicates with undocumented trace flag 9130. That will show the residual as a separate Filter, with performance information you can inspect separately:

This will also highlight the slight cardinality misestimate on the seek, which explains why the optimizer chose the seek over the scan in the first place (it expected the seeking portion to eliminate some rows).

While the inequality rewrite may make (possibly filtered) index matching possible (to make the best use of the seeking ability of b-tree indexes), it would be better to subsequently revert this expansion if both halves end up in the residual. You could suggest this as an improvement on the SQL Server feedback site.

Note also that the original ("legacy") cardinality estimation model happens to select a scan by default for this query.


Get not matching record as null while joining two queries I have two queries having same columns, 1 returning 3 rows and other returning 2 rows. While I join this two query I am able to get only 2 rows. How can I get three rows with not matching record as null in sql server?

Query1:

Select UDA.production_date_T, Count (WO.order_state) AS 'cntplan'
From WORK_ORDER WO
INNER JOIN UDA_Order UDA ON UDA.object_key = WO.order_key
group by UDA.production_date_T) TBL1

Query2:

Select UDA.production_date_T, Count (WO.order_state) AS 'ACTUAL'
From WORK_ORDER WO
INNER JOIN UDA_Order UDA
ON UDA.object_key = WO.order_key
WHERE WO.order_state = 'BOOKED'
GROUP BY UDA.production_date_T

My desired Output is:

production_date_T           cntplan     ACTUAL
2019-04-26                       22         4
2019-05-03                        4        NULL
2019-09-05                        19        0

Seems to me like you just need to move the filter you are using in the second query as a CASE expression:

SELECT  UDA.production_date_T,
        COUNT(WO.order_state) cntplan,
        SUM(CASE WHEN WO.order_state = 'BOOKED' THEN 1 ELSE 0 END) ACTUAL
FROM dbo.WORK_ORDER WO
INNER JOIN dbo.UDA_Order UDA
    ON UDA.object_key = WO.order_key
GROUP BY UDA.production_date_T
;


Querying dates with different timezones at db and application level There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to the user as 'America/Tijuana'. Hence user input is expected in that timezone.

I'm very new to SQL programming, in fact I'm using the framework's integrated query building methods. Anyway, for "debugging" is what I am manually trying in phpmyadmin

SELECT * FROM `order` WHERE date(`created_at`) LIKE '%somedate%'

It works, but is comparing against UTC datetimes in the db. I want it to compare to 'America/Tijuana'.

I'm trying

SELECT * FROM `order`
  WHERE date(convert_tz(`created_at`, 'UTC', 'America/tijuana')) LIKE '%30%'

which runs, but return 0 values. Am I doing it correctly?

Check this:

SELECT created_at,
convert_tz(created_at , '+00:00', '-07:00'),
convert_tz(created_at , 'UTC', 'America/tijuana')
FROM
    (SELECT TIMESTAMP '2016-06-01 09:26:50' as created_at FROM DUAL) orders;

If the last column is null , see prerequisites at https://mariadb.com/kb/en/mariadb/convert_tz/