How to optimize STDistance execution? I am creating temporary table during stored procedure execution with the following structure:

[ID] BIGINT
[Point] GEOGRAPHY

the ID is not unique - there are about 200 records for each ID.

I need to find a list with distinct IDs for which there is at least one Point to Point distance larger then constant value (for example 200 meters).

So, I am using something like this:

SELECT DISTINCT DS1.[ID]
FROM DataSource DS1
INNER JOIN DataSource DS2
    ON DS1.[ID] = DS2.[ID]
WHERE DS1.Point.STDistance(DS2.Point) > 200

For 23 000 points, the query is executed for 4-5 seconds. As I am expecting to have more values, I need to find better solution.

I guess that if there is faster way, I can always create a materialized table and implement additional logic that will calculated this on ID base.

I have created a spatial index, but the query optimizer is not using it. If I use a hint like this WITH (INDEX(SPATIAL_idx_test)) I am getting the following error:

  Msg 8635, Level 16, State 4, Line 78
  The query processor could not produce a query plan for a query with a spatial index hint.  Reason: Spatial indexes do not support the comparator supplied in the predicate.  Try removing the index hints or removing SET FORCEPLAN.
  `

Regardless of any other improvements you make, be sure to test the impact on spatial execution times of enabling trace flags 6532, 6533, and 6534 (start-up only). These turn on native code spatial implementations. SQL Server 2012 Service Pack 3 or SQL Server 2014 Service Pack 2 required (Microsoft Support article). Native compilation is on by default from SQL Server 2016.

For STDistance the important trace flag is 6533. In a simple test, this improved execution time from 2100ms to 150ms without using a spatial index on my laptop's SQL Server 2012 instance.

Example adapted from SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s) by Bob Ward):

Test Data

CREATE TABLE dbo.SpatialTest
(
    ID      integer NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Points  geography NOT NULL
);
GO
-- Insert random sample points
SET NOCOUNT ON;
GO
DECLARE @Point float = 1.1;

INSERT dbo.SpatialTest (Points)
VALUES ('POINT(' + CAST(@Point AS varchar(20)) + ' ' + CAST(@Point AS varchar(20)) + ')' );

WHILE(SCOPE_IDENTITY() < 100000)
BEGIN
    SET @Point = @Point + RAND(SCOPE_IDENTITY());
    IF (@Point > 90.0)
    BEGIN
        SET @Point = -89.0 + RAND(SCOPE_IDENTITY());
    END;

    INSERT dbo.SpatialTest (Points)
    VALUES ( 'POINT(' + CAST(@Point AS varchar(20)) + ' ' + CAST(@Point AS varchar(20)) + ')' );
END;

Test Query

DBCC TRACEON (6533);
DBCC TRACESTATUS;
GO
DECLARE
    @s datetime2 = SYSUTCDATETIME(),
    @g geography = 'POINT(1.0 80.5)';

SELECT [Matches] = COUNT_BIG(*)
FROM dbo.SpatialTest AS ST
WHERE ST.Points.STDistance(@g) > 10000000.5
OPTION (MAXDOP 1, RECOMPILE);

SELECT [Elapsed STDistance Query (ms)] = DATEDIFF(MILLISECOND, @s, SYSUTCDATETIME());

Average execution times: 2100ms (trace flag off); 150ms (trace flag on).


MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock" Good morning/afternoon/night.

I attempted to add a column to a table with a good few million rows. Here is the full query:

alter table date_tasks_mark add column noDuplicate int unsigned not null default 0

However it seems to be stuck.

| 28893 | root         | localhost | database_name   | Query   | 10668 | Waiting for table metadata lock | alter table date_tasks_marks add column noDuplicate int unsigned not null default 0

Is it safe for me to kill the query or will it corrupt my table (it has been in this state for about a half hour)?

Yes, it's safe. Your alter table query hasn't even started.
However, the waiting for table metadata lock means, that there's another transaction operating on that table for a long time and you should rather kill that one to solve the problem. (read more about it here: 8.11.4 Metadata Locking)

To find this transaction, this thread is probably helpful: How do I find which transaction is causing a “Waiting for table metadata lock” state?


How do you capture the output message from a successful MERGE statement when using it in a procedure? Sorry if this sounds like a rather simple question..

When using the console, normally the statement outputs something similar to

X records merged

How do I capture this output into an out variable when using a merge statement in a procedure?

You can use SQL%ROWCOUNT. This return rows processed in last sql statement
For example:

declare
res number;
begin
select 0 into res from dual;
dbms_output.put_line(sql%rowcount || ' rows selected');
end

This code prints 1 rows selected because you selected one row. I hope this helps


how to count specific product in sql table I have a table product and its fields are maker, type and model I want to get the makers who produce only one product type and more than one model. Here is something I am trying

select count(distinct type) as type from product where type = '1'

expe data is: maker = D  &  type = printer

Sample data:

create table product (maker varchar2(100), type char(1), model varchar2(100));
insert into product values ('ABC', '1', 'MODEL1');
insert into product values ('ABC', '1', 'MODEL2');
insert into product values ('ABC', '2', 'MODEL3');
insert into product values ('DEF', '3', 'MODEL4');
insert into product values ('GHI', '4', 'MODEL5');
insert into product values ('GHI', '4', 'MODEL6');
commit;

Query:

select
  maker
from
  product
group by
  maker
having
  count(distinct type) = 1
  and count(distinct model) > 1
;

MAKER
-----
GHI

First we GROUP BY maker, so we inspect each maker on its own. In the HAVING part, count(distinct type) = 1 means the maker produces exactly 1 type of product. Finally, count(distinct model) > 1 means the maker produces more than one model.

With count(distinct type) = 1, we have only 1 distinct type value, so we got lucky in this case and we can use a simple aggregate function on type, we will get the expected value:

select
  maker,
  min(type)
from
  product
group by
  maker
having
  count(distinct type) = 1
  and count(distinct model) > 1
;

MAKER TYPE
----- ----
GHI   4


Is MySQL Cluster a right choice? I'm having a MySQL dual-master Replication of MySQL, each one of the servers have 21 GB memory, I'm now searching for a clustering solution for this project, It's possible to have 6 server: two of them with 20 GB of RAM and others with 10 GB of RAM. in the MySQL Cluster documentation it has written below formula for total memory and each node RAM, It seems that I need much more RAM of my existing Replication, and It's hard to make my company agree with this kind of RAM requirement, besides the purpose of clustering is to instead of making a big server use smaller (in resources) servers attached to each other but with this formula each node of clustering is much bigger than my existing servers.

  If designing a completely new database, the following calculations can
  be used to help determine the approximate memory sizing requirements
  for the data nodes:

  •  (in memory) Data Size * Replicas * 1.25 = Total Database Memory
  Requirements Example: 50 GB * 2 * 1.25 = 125 GB

  •  (Data Size *
  Replicas * 1.25)/Nodes = RAM Per Node Example: (2 GB * 2 * 1.25)/4 =
  31.25 GB

In this situation I have some questions :

Is it necessary to obey from these formulas? can I use less RAM for nodes?

When evaluating clustered solutions you need to think about the read/write profile of your application and match that against available solutions.

For a heavy read based profile of an application, you would be able to scale up reads by having multiple MySQL replicas off of your MySQL master-master replication setup.

For write heavy profile, you are implementing some of the obvious choices in master-master replication.

When choosing between clustered MySQL options there are two technology options: NDB based Oracle MySQL Cluster and Galera based options from Percona (XtraDB Cluster) and MariaDB (MariaDB Cluster), and Galera Cluster for MySQL.  They are architected differently and have different advantages and disadvantages.

A good webinar on the distinction is here:

http://severalnines.com/blog/galera-cluster-mysql-vs-mysql-ndb-cluster-high-level-comparison-webinar-replay-slides

The formula mentioned is primarily used in measuring the projected per node requirements for a NDB cluster.  Originally MySQL Cluster, previously NDB Cluster, was a memory only database, which required all of the data to reside in the memory of available nodes.  If all your data was required to stay in memory, then if you didn't enough RAM to hold all your data, you are under provisioned in terms of hardware.

Nowadays, each node in MySQL cluster, namely a data node, is a MySQL instance.  Even though you should keep your data in memory for optimal performance, it is not required.  So, basically if you don't "obey" the formulas, you will end up dropping down to disk and would have to deal with all the IO performance of the particular hardware available to your nodes.


Connect mongodb 4.0 with robomongo I am facing an issue with the mongodb 4.0. I have setup mongodb on remote server and want to connect it with robo3t. But getting the below error.

And here is my mongodb.conf file

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true

#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0

# how the process runs
processManagement:
  timeZoneInfo: /usr/share/zoneinfo

#security:
 #authorization: "enabled"

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

Mongodb version --> 4.0
Os --> ubuntu 16.04

Update-->

First, make sure the MongoDB service is running in the remote machine.

Next, check if the MongoDB port is open to the outside network or not.

The following will help you check if the 27017 is accessible from an outside machine.

nmap -Pn 13.56.140.11

If the port is not enabled, use the following command to expose the port.

sudo iptables -I INPUT -p tcp -s 0.0.0.0/0 --dport 27017 -j ACCEPT


How to join 2 tables when calling a count on the second table I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest assignment at the top of a list.

coaches
+user_id
+bio
+title

coach_assignments
+id
+client_id
+coach_id
+deleted_at

I wrote this code to get the count from the coach_assignments table
select count(coach_id), coach_id from coach_assignments where deleted_at is not null group by coach_id order by count(coach_id) ASC

and it worked, however the glaring hole is that a new coach doesn't have any entries in coach_assignments so they aren't counted. I don't want to add a client_count column to coaches as I don't feel coaches should have that info.

I tried:

select  user_id, count(coach_id)
    from  coaches
    left join
    (
        SELECT  count(coach_id), coach_id
            from  coach_assignments
            where  deleted_at is not null
            group by  coach_id
            order by  count(coach_id) ASC
    ) as counted  ON coaches.user_id = counted.coach_id;

but this only returned 1 row and there should be 4 for my test data.
How do I do something like this?

select coaches.user_id, coalesce(counted.cnt,0) cnt
from coaches
left join (select count(coach_id) cnt, coach_id
           from coach_assignments
           where deleted_at is not null
           group by coach_id) as counted
     on coaches.user_id = counted.coach_id
order by 2 asc


Where to start to understand a unknown database So, the title sums it up.

I've got a SQL Server database with 28 tables and 86 stored procedures that must be reverse engineered. I'm pretty sure that some tables are never used and that not all procs are used as well.

The biggest problem is that all the Windows Services that were created to be used with this DB and all the software and database documentation is lost, and the person that designed the whole system is nowhere to be found.

I've already managed to create an ER diagram to help me understand the relationships, but as I am not experienced in database administration, I have no idea where should I start.

Also I'm sorry if this kind of question is not meant to be asked here.

Your best bet to start is to document your database using SQL Power Doc

SQL Server & Windows Documentation Using Windows PowerShell
SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2014, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 R2 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

Note: I have used it and it will give you a really good start in documenting and understanding your database server instance.


What exactly is the datatype input into a DATETIME value in MySQL I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right format.
Actual question: I assume it's string, because when I manually input values using and INSERT, the value has to be in ' '. Is this a stupid assumption on my part?
When the output of my PLC is a string and the datatype of my column is DATETIME, what I read from a SELECT in my DB is completely wrong : '1905-07-13 00:00:00' instead of '2021-04-30 08:51:30'. However, if I write it in the DB column set to be of type VARCHAR, it's written correctly, but not usable for my next app. So if I then change the column to DATETIME, everything works great, but that's super impractical and I can be changing the datatype of my column every time I need to add to the DB.
Another assumption I'm making here is that the input datatype is what's causing this problem, but as it is now, DATE and DATETIME are not supported by the driver I'm using, so maybe it's just wishful thinking on my part that any of this will work at all.
Note: Please do migrate this if it's off topic, wasn't really sure if it was a best fit here or stack overflow...
Second Note: The only way I have found to make this work is to write into the DB as a VARCHAR and then use CONVERT(,DATETIME) in my app, but it feels weird that I can't just write a date in my DB for some reason.....

You must provide the value as string literal or as a numeric literal.
Possible formats are described in MySQL 8.0 Reference Manual  /  ...  /  Date and Time Literals.
Alternatively you may provide the data in any custom format and apply STR_TO_DATE() to it in INSERT query with according parsing pattern.
DEMO


Tuple calculus: unsure if my reasoning is correct I'm currently working through a tuple calculus question and I ran into a situation where I have two answers that seem correct to me and I am unsure if my logic is wrong or if they are both correct answers (and if so, is one more correct than the other).

I am trying to find the sid of the store that supplies every item. The schemas are:

Stores(sid: integer, sname: string)

Items(iid: integer, iname: string)

Supplies(sid: integer, iid: integer, price: integer)

What I came up with was:

{ P | ∃ S1 ∈ Supplies(P.sid = S1.sid ∧ ∀ i ∈ Items(∃ S2 ∈ Supplies (S2.iid = i.iid ∧ S2.sid = S1.sid)))}

{ P | ∃ S1 ∈ Supplies(P.sid = S1.sid ∧ ∀ i ∈ Items(S1.iid = i.iid))}

My reasoning is that I am looking for the sid where for all Items there is an entry in the Supplies table that corresponds to the iid. The only real difference between my answers is that the second one doesn't use a second separate Supplies table which allows us to cut out the additional S2.sid = S1.sid. My textbook has a similar question to this and has an answer similar to 1 but my reasoning gets me the second answer.

Answer

I'm going to rename your tables a bit:

Store(s) -- store [s.sid] has name [s.sname]
Item(i) -- item [i.iid] has name [i.iname]
Offer(o) -- store [o.sid] offers item [o.iid] for $[o.price]

You want the tuple(s) s of the form < sid > where

-- store s.sid offers every item, ie
-- for all Items i: store s.sid offers item i.iid, ie
-- for all Items i: there exists Offer o: [o.sid = s.sid ∧ o.iid = i.iid], ie
∀ i ∈ Items ∃ o ∈ Offer [o.sid = s.sid ∧ o.iid = i.iid]

The query for such stores--and there can be more than one such store--is:

{ s : < sid > | ∀ i ∈ Items ∃ o ∈ Offer [o.sid = s.sid ∧ o.iid = i.iid] }

Your answer 2

  I am looking for the sid where for all Items there is an entry in the Supplies table that corresponds to the iid.

That has a "for-all for which there-exists", just like my solution. But your proposed answer 2 has a "there-exists for which for-all":

{ P | ∃ S1 ∈ Supplies(
        P.sid = S1.sid
    ∧ ∀ i ∈ Items(S1.iid = i.iid)
    )}

That asks for tuples P where there is an offer S1 where [P's sid is S1's sid and for all Items S1's iid is that item's iid]. I hope you can see that this involves a single iid being the same as all the ones in Item, not what you want.

Your answer 1

{ P | ∃ o ∈ Offer (
        P.sid = o.sid
    ∧ ∀ i ∈ Item (∃ o2 ∈ Offer (o2.iid = i.iid ∧ o2.sid = o.sid))
    )}

Notice from above that the ∀ ... means store o.sid offers every item. So:

{ P | ∃ o ∈ Offer (P.sid = o.sid
    ∧ store o.sid offers every item  )
    )}

If there are no offers and some stores then every store offers every item. But since there are no offers, it's false that ∃ o ∈ Offer (P.sid = o.sid)). No Ps make that true. So this is {}. So this is not the answer.

(Your answer 1 may be modified from a textbook answer expressed by Codd's original relational division operator. But so dividing Offers by Items doesn't actually return tuples s where store s.sid offers every item. It returns rows where store s.sid offers every item AND there is at least one item. So the answer is not expressed by Offers/Items.)

(Indeed 2 has an extra ∃ compared to your natural language description and my answer, but it's the outer ∃. So your solution 1 is in that way reminiscent of the correct answer.)


Turning multiple fields in column to row I have an issue that has really stumped me in SQL Server.

I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something like the below.

id | CustomerID  | Key         | Value
-------------------------
1       2           Firstname    Tim
2       2           Secondname   Skold
3       2           Age          48
4       2           Gender       Male

So the output should be something like:

customerid  |   Firstname | Secondname | Age | Gender
-----------------------------------------------
 2                Tim          Skold       48     Male

I have tried to pivot the result, but can't get it to work, and have also tried to union the results, but this gives the results as a column, not as a single row.

The messy way in which this is being done currently is by joining on to the same table multiple times to get each field (firstname, secondname etc...)

Maybe unpivoting?

Really not sure, thanks for any help...

James.

EDIT***

Updated table view.

You're looking for a pivot. Either of these queries will work: SqlFiddle

/* case */

select
      CustomerId
    , FirstName =max(case when [Key]= 'FirstName' then Value end)
    , LastName  =max(case when [Key]= 'SecondName'  then Value end)
    , Age       =max(case when [Key]= 'Age'       then Value end)
    , Gender    =max(case when [Key]= 'Gender'    then Value end)
from Customers
group by CustomerId

/* pivot */

select CustomerId, FirstName, SecondName, Age, Gender
  from (Select CustomerId, [Key], Value from Customers) c
    pivot ( max(Value)
      for [Key] in (FirstName, SecondName, Age, Gender)
    ) as p

Schema Setup for SqlFiddle:

    create table Customers (
    id int identity (1,1) not null primary key
  , CustomerID int not null
  , [Key] varchar(32) not null
  , Value varchar(32)
  )

insert into Customers (CustomerId, [Key], Value) values
    (2,'FirstName','Tim')
  , (2,'SecondName','Skold')
  , (2,'Age','48')
  , (2,'Gender','Male')
  , (3,'FirstName','Sql')
  , (3,'SecondName','Zim')
  , (3,'Age','32')
  , (3,'Gender','Male')

Links

Technet - Using PIVOT and UNPIVOT
Cross Tabs and Pivots, Part 1 - Jeff Moden


Does Extended Event session without target affects server performance? Extended event session can be created without targets. In such situation you can watch collected events in SQL Server Management Studio in the Live Data view. For such session a LiveStreamTarget is created whenever Live Data view is opened. If no one is monitoring such session, there is no target created for it.

Does such XEvent session affects server performance when no one is monitoring events with Live Data tab?

The overhead of an active event session with no targets depends on the frequency of events. Costs will be significant enough as to impact performance only under extreme cases.

Below are batch requests/sec measurements with no trace as well as unfiltered  sql_batch_completed and sql_statement completed traces. The results of these tests show about a 8% decrease on throughput with the sql_batch_completed trace while the sql_statement_completed trace reduced throughput by a considerable 63%.

Given that it's quite easy to start an existing trace with a right-click in SSMS Object Explorer, my recommendation is to run traces that don't have targets only when you want to actively view live data, even though the impact will be insignificant unless high-frequency events are captured.

Below are the scripts I used for the test.

--script to generate sql_batch_completed and sql_statement completed events
SET NOCOUNT ON;
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 1000
BEGIN
    SET @IterationCount += 1;
END;
GO 1000000

Script to run traces during test execution and measure throughput:

DECLARE @BeginCounterValue int;

--measure throughput with no trace
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'no trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_batch_completed trace
CREATE EVENT SESSION sql_batch_completed ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
ALTER EVENT SESSION sql_batch_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_batch_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';

--measure throughput with sql_statement_completed trace
DROP EVENT SESSION sql_batch_completed ON SERVER;
CREATE EVENT SESSION sql_statement_completed ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
ALTER EVENT SESSION sql_statement_completed ON SERVER STATE=START;
SELECT @BeginCounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
WAITFOR DELAY '00:01:00';
SELECT 'sql_statement_completed trace' AS TraceName, (cntr_value - @BeginCounterValue) / 60.0 AS BatchRequestsPerSecond
FROM sys.dm_os_performance_counters
WHERE
    object_name = N'SQLServer:SQL Statistics'
    AND counter_name = N'Batch Requests/sec';
DROP EVENT SESSION sql_statement_completed ON SERVER;
GO


Slow checkpoint and 15 second I/O warnings on flash storage Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.

At first glance it looks to be clearly an I/O subsystem error and the SAN admin was to be blamed for it. But recently we changed the SAN to utilize Full Flash but as of today the error still pops up and I have no clue as to why since every metric, whether wait stats or any other metric, that I run to check if SQL server is a possible culprit seems out to return normal.

It doesn't really add up. It could also be very likely that something else is chewing the disk and SQL Server is becoming victimized here...but I am not able to find out what?

Dbs are in Availability Groups and as and when these events occur we do see role changes and flip overs occurring along with timeouts.

Any help in figuring this out would be highly appreciated. Let me know if any further details is needed.

Error msgs. below

  SQL Server has encountered 14212 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [E:\MSSQL\DATA\ABC.mdf] in
  database [ABC] (7).  The OS file handle is 0x0000000000000D64.  The
  offset of the latest long I/O is: 0x0000641262c000

  SQL Server has
  encountered 5347 occurrence(s) of I/O requests taking longer than 15
  seconds to complete on file [E:\MSSQL\DATA\XYZ.mdf] in database [XYZ]
  (7).  The OS file handle is 0x0000000000000D64.  The offset of the
  latest long I/O is: 0x0000506c060000

  FlushCache: cleaned up 111476 bufs with 62224 writes in 925084 ms
  (avoided 19 new dirty bufs) for db 7:0
              average throughput:   0.94 MB/sec, I/O saturation: 55144, context switches 98407
              last target outstanding: 10240, avgWriteLatency 14171 FlushCache: cleaned up 5616 bufs with 3126 writes in 248687 ms
  (avoided 3626 new dirty bufs) for db 6:0
              average throughput:   0.18 MB/sec, I/O saturation: 10080, context switches 20913
              last target outstanding: 2, avgWriteLatency 3

Here's the virtual file stats info over a 30 minute span:

And wait stats as well:

Here is the note from the system architect:

  We separate workloads for high I/O intense workloads (such as DB) so that we only have one per host. The specs for the current host is Dell R730 with 16 cores of Xeon E5-2620 (2 sockets), 512GB, and 2x10G interconnects for storage. No other VM on the cluster nor host are experiencing these issues. Storage for VMs and workloads is on Pure FA-x20.

General System Information:

SQL Server 2012 sp3-cu9 (Enterprise Edition)
Total RAM: 128 GB
Total DB size: Close to 1 TB

Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.

Sounds good. Have you collected and cut up the minifilter and storport tracing, yet? If so, what did it show?

  At first glance it looks to be clearly an I/O subsystem error and the SAN admin was to be blamed for it. But recently we changed the SAN to utilize Full Flash but as of today the error still pops up and I have no clue as to why since every metric, whether wait stats or any other metric, that I run to check if SQL server is a possible culprit seems out to return normal.

I want go over two different areas here.

The first is that SQL Server itself doesn't actually do anything with I/O, it posts it to Windows using the typical Windows APIs. Whether it is ReadFile, WriteFile, or the vectored I/O of those it's all up to Windows. SQL Server keeps a list of pending I/O and checks that I/O at various times to get the status if it isn't completed. This is done using, again, the typical Windows asynchronous I/O model. The message is printed when the I/O has been pending and not completed, according to Windows for over 15 seconds as we're using the GetOverlappedResult Windows API to check status. This means, SQL Server doesn't really have a say in the matter, it's what is being returned via Windows.

The second item is that just because it's all flash and 10 Gb fiber doesn't mean something isn't setup or configured incorrectly, that a driver, filter, or other bug or item isn't hit, or that something isn't physically wrong. Just to get an idea:

Windows Config
Windows Drivers such as mutli-pathing being setup and the latest version
Filter Drivers (you know, disk devices, antivirus, backup, etc.)
Hypervisors (if any)
HBA drivers
HBA firmware
HBA configuration
Physical Cabling
Fiber switching
I/O Group connections/SAN/Device
Configuration of SAN/Device

That's all under SQL Server, it's just that SQL Server is the one telling you about it.

  Dbs are in Availability Groups and as and when these events occur we do see role changes and flip overs occurring along with timeouts.

That's really good information to know, although it doesn't necessarily mean it's exactly related. Now, if it only happens when there is a failover, then that would hone in the issue much more and that would sound to me more like the drivers et al. doesn't like throwing a whole lot of mixed I/O at it as a failover typically results in the redo/undo and resync's happening which could be a spike in outstanding I/O.

  Any help in figuring this out would be highly appreciated.

Unless it's a query or set of queries that are pushing high IOPs, which it doesn't sound like as the snapshot for 30 minutes you have was only 737,465 I/O operations which averages to 410 IOPs (not that high, especially if it's flash) looking inside of SQL Server isn't going to help with this issue since SQL Server is the messenger.

You'd want to collect if not already:

Minifilter time spent. This can be done through WPR (XPerf) if you don't have anything else. This can help if the I/O is getting stalled in a filter driver.
Storport trace. This will be the last stop on the way our and the first stop on the way back. Any time between these two readings is time spent outside of Windows... It'll also show you the targets and where the slowness might be on the other end (but isn't always conclusive).

If none of those are helpful in diagnosis or narrowing in the scope of the issue, it may be time to open a ticket with Windows Storage support and have all the data already collected so that you all can start on the same page.


Can I use PHP function strtotime() in SQL queries? I have this table, and I need to use a PHP function in the query.

id     time       lastTime
1      08:02
2      08:04
3      08:30

I am using this query with the strtotime(), and it's not working:

UPDATE table
SET lastTime = strtotime(time) - strtotime('08:00')";

How can it be done?

UPDATE table
SET lasttime=TIME_FORMAT(SEC_TO_TIME(((SUBSTRING_INDEX(time,':',1)*60)+SUBSTRING_INDEX(time,':',-1))-480),'%i:%s');

Do the calculation in seconds then back to time,no need for php

FIDDLE


hide instance not working We do not appear (yes/No) for hide instance in SSCM for all SQL Server 2012 servers.
We are unable to see (yes/No) option for hide instance . Kindly assist me here .
Thanks a lot !

This is the proedure.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/hide-an-instance-of-sql-server-database-engine?view=sql-server-ver15
Is the SQL Server Browser running?
The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :
EXEC master..xp_instance_regwrite
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
  @value_name = N'HideInstance',
  @type = N'REG_DWORD',
  @value = 1
  — 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:
DECLARE @getValue INT
EXEC master..xp_instance_regread
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key=
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
  @value_name = N'HideInstance',
  @value = @getValue OUTPUT
SELECT @getValue

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.


Schema/Structure to handle secondary computed data I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play.

One of the features of our league is that we convert raw scores to matchpoints.  For example, suppose four players play a match and their scores are:

14,325,320
35,332,110
34,003,990
11,345,920

then their matchpoints would be 2, 4, 3, 1 respectively.

Sometimes we might want a more complex matchpoint algorithm, which might be based on properties of the raw scores (e.g., if the highest score is better than all other scores combined, award an extra matchpoint).

Obviously whatever database schema I use, I would like it to store not just the raw score, but also easily display the matchpoints associated with that score.

What I'm confused about is the best place to compute those matchpoints.  Should I:

Set a SQL trigger each time a raw score is entered into the database, then have some complex logic to determine if matchpoints should be computed and then modify rows as necessary?  I'm not sure if SQL can necessarily handle our matchpoint algorithms easily.
Don't store matchpoints in the database at all, but compute them on the fly each time they are requested?  This seems like a waste of computation power.
Having a scheduled event in SQL, or a cron job on the server, that goes through the database and computes matchpoints, then adding that data to the database as necessary?  That seems to have the drawback of needing to wait around for that computation to happen.

Right now the best approach I can think of is a hybrid of 2 and 3, where during the competition matchpoints aren't stored, but computed on the fly, and then having a human-thrown trigger at the end of each week's competition that computes all the matchpoints and puts them into the database.  Possible bugs might be that I have to be careful to reuse the same code or else the database scores might differ from the temporary scores.  Also in case of human error the trigger might not be thrown.

It seems like I can't be the only person who needs a bit of automated business logic to parse database entries as they come in and then display them.  In some sense the ideal result would be something like a SQL View, but where I can add my own logic to it.  I have no idea if something like that exists.

Are there any better ideas?

I think a spin-off of option 2 would be ideal based on your requirements that seem to only be for presentation and not further computation.

You query the database for all the scores from match X and order them from high to low. Then in the presentation layer of the web application, simply add ranked number assignments to them starting with 1 for the first and n+1 from there on for each row you fetch from the result set.

A query could be

SELECT name, score FROM matches WHERE matchId = 'X' ORDER BY score DESC


Return previous running total when value is null in a time series This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).

All of this is being done on a PostgreSQL 11.5 DB.

SELECT *
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01', date_trunc('day', localtimestamp), interval '1 day') the_day
   ) d
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'tsla')) OVER w AS tsla_running_amount
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'goog')) OVER w AS goog_running_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   WINDOW w AS (PARTITION BY customer_id ORDER BY created_at::date)
   ) t USING (the_day)
ORDER  BY customer_id, the_day;

When a customer has NO transactions on a day, I need to be able to display the "most recent" running total for that customer for that day.

For example:

2020-01-01: Customer purchases 5 "tsla" for a total balance of 5.
2020-01-02: Customer takes no action. Current total balance display as 5.
2020-01-03: Customer sells 2 "tsla", total balance now 3.
2020-01-04: Customer takes no action. Current balance still display as 3.

Currently, the way that this is set up, the record for a customer on a day where they have no transactions will return as a NULL row for balance totals.

How can I display their "most recent" running balance on days when they have no transactions?

This query will be running for all customers that exist within the transactions table (have had a transaction at least once in the past).

One way is to generate one row per (customer_id, the_day) before running the window function. Like:

SELECT c.customer_id, d.the_day
     , sum(t.tsla_amount) OVER w AS tsla_running_amount
     , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01'
                        , date_trunc('day', localtimestamp)
                        , interval '1 day') the_day
   ) d
CROSS  JOIN (SELECT DISTINCT customer_id FROM transactions) c  -- !
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
        , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   ) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

db<>fiddle here

If the table is big, and there are only relatively few distinct customers, you might want to optimize performance. See:

Optimize GROUP BY query to retrieve latest row per user

But you would typically have a separate table of customers to work with ...

If you want 0 instead of NULL in rows before the first transaction of a customer, add COALESCE. Like:

SELECT c.customer_id, d.the_day
     , COALESCE(sum(t.tsla_amount) OVER w, 0) AS tsla_running_amount
     , COALESCE(sum(t.goog_amount) OVER w, 0) AS goog_running_amount
FROM ...


I have delete database from mysql but storage is not freed I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage?

I have a mysql database that the total storage is 15GB. After adding multiple databases (copied from another mysql database) and deleting them the free storage available is 0.
I can not simply delete the RDS instance as there is another database on this instance that I need it. but I am 100% sure that the database is not using 15GB of storage.

I have read about this issue on other threads but most of the solutions where talking about deleting all the data and shutting down the server which I can't do as the database is being used all the time.

You may find this surprising, but there are lots of little transactions that occur inside ibdata1. I wrote about this back on Jan 14th : AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?
Even though MySQL 5.6 has innodb_file_per_table on by default, the only way ibdata1 can grow is by having lots of transactions. If you look at this diagram:

you will see that ibdata1 has 1023 rollback segments for supporting MVCC and Transaction Isolation. In a heavy-write DB environment, ibdata1 can grow. I have discussed this before in my old post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
In that post, I mentioned the following:
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

Thus, I would expect ibdata1 to outgrow your current disk allocation if you chose a number too small.
BTW Where does the ibdata1 and the transaction logs actually reside ?
When you run show variables like 'innodb%home_dir'; you find those two folder

/rdsdbdata/db/innodb
/rdsdbdata/log/innodb

You cannot just reach into it and expand it that easily.
Just to make sure you have nothing of yours in the database, run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

If you get nothing back, then you can reasonable assert what I just said. Otherwise, go delete your data.
You may want to check for binary logs as well. Just run one of the following:
SHOW BINARY LOGS;
SHOW MASTER STATUS;

You may or may not have rights to purge them. Amazon may not want you to touch them since they control binary logging for the sake of spinning up read-only Slaves in the cloud.
SUGGESTIONS

Get more disk space allocated for the RDS Instance (if possible).
You may need to switch to a higher server model and then migrate.
Get away from RDS and switch to EC2 where you can micromanage everything, but you must shoulder the responsibility for monitoring the MySQL instance.
Contact Amazon for any pricing before you do anything (switching server models, migration to another RDS Instance, migration to EC2, etc).


PostgreSQL translate special characters? Description:

PostgreSQL 9.3

String: 'ì ằ ú ề'

Desired Result:  'i a u e'

My code:

select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e

Question:

If I use it this way, I have to define a manual translation between 'ìằúề' and 'iaue'. Is there a better solution?

Ref: PG Document

Use unaccent() provided by the additional module unaccent.
Details:

'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX?
Does PostgreSQL support “accent insensitive” collations?
Postgres accent insensitive LIKE search in Rails 3.1 on Heroku

Substantial improvements went into Postgres 9.5:

  Allow empty replacement strings in unaccent (Mohammad Alhashash)

  This is useful in languages where diacritic signs are represented as
  separate characters.
  Allow multicharacter source strings in unaccent (Tom Lane)

  This could be useful in languages where diacritic signs are
  represented as separate characters. It also allows more complex
  unaccent dictionaries.

Custom rules

If you edit the default rules (like you commented) any updates (or new compilations if you compile yourself) will overwrite your manual changes. Be sure to make a backup of both the original and your edited ruleset immediately.

The unaccent() function is overloaded. While the simple form uses the default dictionary 'unaccent':

SELECT unaccent('ì ằ ú ề');

You can chose a separate dictionary with the 2-parameter form:

SELECT unaccent('my_dictionary', 'ì ằ ú ề');

I would consider not to alter the default ruleset (like instructed in the link in your comment), but install your ruleset additionally under a custom name. This way you keep the default behavior, which receives all updates automatically, and you can clearly see in your code where you apply your alternative / extended ruleset. Follow instructions in the manual or consider this blog.

And consider my link above for use in indexes or other contexts where you need the function to be immutable.


How to run MongoDB as a non root user in Linux? I can start MongoDB as $sudo service mongod start. When I start without sudo it gives me this error:

/etc/init.d/mongod: line 58: ulimit: open files: cannot modify limit: Operation not permitted
/etc/init.d/mongod: line 60: ulimit: max user processes: cannot modify limit: Operation not permitted
Starting mongod: runuser: may not be used by non-root users

1) Now, I've changed ownership to the non root user on all mongo directores I could find i.e. /var/lib/mongo, var/log/mongodb, data/db, var/run/mongodb

$sudo chown -R nonRootUser:nonRootUser [directory]

2) I've deleted mongod.lock files

3) I've run --repair too

It still gives me the same error.

I also tried

$mongod --fork --logpath /var/log/mongodb.log

about to fork child process, waiting until server is ready for connections.
forked process: 18566
ERROR: child process failed, exited with error number 1

mongod.log file says this:

2016-03-17T15:03:49.053+0000 I NETWORK  [initandlisten] waiting for connections on port 27017
2016-03-17T15:03:54.144+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends

It's Amazon Linux. I am able to start it like this $mongod, but I want to run it as daemon so it runs continuously.

The nonRootUser is a new user I created in addition to ec2-user. Maybe there are some config issues relating to running daemon processes if you're not ec2-user?

UPDATE: Changed ownership on everything to ec2-user, still getting exactly the same errors as before.

You mention that you used the rpm installer for installing MongoDB on the Amazon Linux AMI. When you do this, the mongod user is created on the system and all of the appropriate files are created and owned by the mongod user. When starting MongoDB installed by the rpm, the command must either be run by root or run using sudo. This is because the init script must execute a change user command so the mongod process can be forked as a child of the mongod user.

Failure to use root or sudo to start the service results in the errors you quoted above.

/etc/init.d/mongod: line 58: ulimit: open files: cannot modify limit: Operation not permitted
/etc/init.d/mongod: line 60: ulimit: max user processes: cannot modify limit: Operation not permitted
Starting mongod: runuser: may not be used by non-root users

So, in order to run as a service, you must use sudo or root. There is no simple way around this except granting the mongod user a shell, assigning it a password, logging in as that user, then running the service. This will still result in errors attempting to modify the ulimits and is generally not recommended.

To start MongoDB without running it as a service, since you used the rpm, the mongod binary should be in your path, so the following should work

mongod --dbpath /path/to/data --logpath /path/to/mongod.log --fork

Note that doing this will run it as the currently logged in user, that means all of the data files as well as mongod.log must be writable by that user. Ideally, these files should be owned by that user.


Automatically generate database creation script with default values for SIZE and FILEGROWTH By using a mix of several PoSH scripts I managed to get a working script that generates, copies and remotely executes a script to create the basic structure for a given database with all its objects, logins, etc. But is using for SIZE and FILEGROWTH the values stated on the production environment (say 1GB and 512MB). The goal is to create the database on a dev environment for developers to play with it. So an initial default size and filegrowth will be more than enough. For SQL2016 those values are 8MB and 64MB respectively as stated here by MSSQL Tiger Team.

Is there a way to generate the script with those values instead of the ones configured for the database? Or should I search and replace the values on the script?

One method to modify SQL scripts is with the Microsoft.SqlServer.TransactSql.ScriptDom. Below is a PowerShell script example that changes the file sizes using a TSqlFragmentVisitor implemented in C# that you can extend for your needs. This same technique may be used to change database names, file paths, size, etc.

Note that if you are using the PowerShell ISE for script development, you'll need to close and re-open the PowerShell ISE after making changes to the C# code in order to reload the .NET app domain.

# C-Sharp TSqlFragmentVisitor visitor class for ExplicitVisit(SizeFileDeclarationOption node)
Add-Type @"
    using System;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    using System.Collections.Generic;

    public class CreateDatabaseVisitor : TSqlFragmentVisitor
    {

        public Action<CreateDatabaseVisitor, TSqlFragment> Visited;

        public override void ExplicitVisit(SizeFileDeclarationOption node)
        {
            onVisit(node);
        }

        public override void ExplicitVisit(FileGrowthFileDeclarationOption node)
        {
            onVisit(node);
        }

        void onVisit(TSqlFragment node)
        {
            if(Visited != null)
            {
                Visited(this, node);
                node.AcceptChildren(this);
            }
        }

    }
"@ -ReferencedAssemblies "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

# create new class instance
$visitor = New-Object CreateDatabaseVisitor

# callback method for each visit
$visitor.Visited = {
    param($obj, $node)
    # Write-host "Visited node type: $($node.GetType())"

    # change SizeFileDeclarationOption as desired
    if($node.GetType().Name -eq "SizeFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 100
        $node.Size = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

    if($node.GetType().Name -eq "FileGrowthFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 10
        $node.GrowthIncrement = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

}

$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$originalScript = [System.IO.File]::ReadAllText("C:\temp\CreateDatabase.sql")
$scriptReader = New-Object System.IO.StringReader($originalScript)

$frament = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0)
{
    throw "$($parseErrors.Count) parsing errors. First error is: $($parseErrors[0].Message)"
}

$frament.Accept($visitor)

Write-Host "Original Script:"
Write-Host $originalScript

#generate new script
$options = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.SqlScriptGeneratorOptions
$options.SqlVersion = [Microsoft.SqlServer.TransactSql.ScriptDom.SqlVersion]::Sql120
$options.IncludeSemicolons = $true
$generator = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.Sql120ScriptGenerator($options)
[String]$newScript = ""
$generator.GenerateScript($frament, [ref]$newScript)

Write-Host "Modified Script:"
Write-Host $newScript

Example output:

Original Script:
CREATE DATABASE [YourDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf' , SIZE = 15360 , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
 FILEGROUP [YourDatabase]  DEFAULT
( NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf' , SIZE = 78561280 KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
 LOG ON
( NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf' , SIZE = 4308416KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 120
GO

Modified Script:
CREATE DATABASE [YourDatabase] CONTAINMENT = NONE
    ON
    PRIMARY(NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB),
    FILEGROUP [YourDatabase] DEFAULT(NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB)
    LOG ON (NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf', SIZE = 100 MB, MAXSIZE = 2048 GB, FILEGROWTH = 10 MB);

GO
ALTER DATABASE [YourDatabase]
    SET COMPATIBILITY_LEVEL = 120;


Best way of order processing I would like to write a procedure for order processing
Single Order can have multiple items, how can I process the order?

Consider below JSON as an example:

{
    "Order":
    {
        "OrderId":"",
        "OrderDate":"",
        "CustomerId":"",
        "Price":300,
        "Items":
        [
            {
                "ItemId":103,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":123,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":122,
                "Qty":2,
                "Price":100
            }
        ]
    }
}

Corresponding objects:

public class Item
{
    public int ItemId { get; set; }
    public int Qty { get; set; }
    public int Price { get; set; }
}

public class Order
{
    public string OrderId { get; set; }
    public string OrderDate { get; set; }
    public string CustomerId { get; set; }
    public int Price { get; set; }
    public List<Item> Items { get; set; }
}

I found two ways to process the above order

Prepare complete order items as a string like ItemId|Price|Qty,ItemId|Price|Qty,ItemId|Price|Qty (split functionality inside procedure)

@orderDate datetime = null,
@itemStr nvarchar(2000) = null, --(split functionality inside procedure)
@orderPrice decimal(18,2) = 0.00,
@customerId int = null

just find my approach based on my parameters declaration.

First order creation done separately and order under items will be inserted separately.
(programmatic insertion instead of native procedure call, no need to implement split functionality inside procedure).

Looking for best way of order processing (transaction management and performance are major constraints)

This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side.

You could convert json to xml similar to this:

<Order OrderId="" OrderDate="" CustomerId="" Price="300">
    <Items>
        <Item ItemId="103" Qty="2" Price="100" />
        <Item ItemId="123" Qty="2" Price="100" />
        <Item ItemId="122" Qty="2" Price="100" />
    </Items>
</Order>

You can then execute this stored procedure with this xml parameter:

CREATE PROC InsertItems(
    @xml xml
) AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO data(ItemId, Qty, Price)
    SELECT x.value('@ItemId', 'int') as ItemId
        , x.value('@Qty', 'int') as Qty
        , x.value('@Price', 'int') as Price
    FROM @xml.nodes('/Order/Items/Item') t(x)
END

You must add the nodes and value path to your xml.

It should work fine for a few rows but it may not be very efficient with hundreds or thousands of Items.

You can also create a Table Type and pass is to the stored procedure using a Table-Valued Parameters:

CREATE TYPE dbo.ItemsTableType AS TABLE
    ( ItemId int, Qty int, Price int)

CREATE PROCEDURE insertItems
    (@items dbo.ItemsTableType READONLY)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO data(ItemId, Qty, Price)
    SELECT ItemId, Qty, Price
    FROM @items
END

You then call it from your code using one of the .Net options and examples on this page: Table-Valued Parameters.

If you have to insert too many rows at once, you can split it in batch of 100, 1000 or whatever works best on your system.


MySQL error 1674 even when binlog is set to ROW I am trying to alter table by doing,

ALTER TABLE Account
ADD `AccessCode` binary(16) NOT NULL  DEFAULT (UUID())

It gives me error,

Error Code: 1674. Statement is unsafe because it uses
a system function that may return a different value on the slave.

My Binlog format is Row

show variables like 'binlog_format'

--------------------------
|Variable_name  |  Value |
--------------------------
|'binlog_format'| 'ROW'  |
--------------------------

Though I will find some alternative, but why it is not allowing me? Even after setting binlog format as ROW, what is the problem here?

ALTER TABLE statements are still replicated as statements. Even with binlog_format=ROW this will be requiring the existing table populate the existing rows with an AccessCode.

I recommend:

ALTER TABLE Account ADD AccessCode binary(16) NOT NULL;
UPDATE Account SET AccessCode = UUID_TO_BIN(UUID());

This manual page says that non-determinstic default functions on columns may produce a warning or error (CURRENT_TIMESTAMP was an exception).

I'm not sure you can create this, even on the master after executing SET SESSION sql_log_bin=0; ALTER TABLE Account Alter AccessCode SET DEFAULT (UUID_TO_BIN(UUID())), I tried MySQL-8.0 however your version may differ.


Change column data type across database I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also because I can't use such columns with DISTINCT, GROUP BY etc.). Is it possible to accomplish this without using ALTER TABLE with ALTER COLUMN for each table manually?

I am using MS SQL Server 2008.

No, there's no magic or hand-waving here. It'd be great if synonyms, say, applied to types, but that is not the case. If you want to make these columns first-class citizens, you'll need to change the table. You can automate this to some degree, though I won't post code to help with this unless you specify what you mean exactly by "manually" and why you think this will be a significant burden you want to avoid.

To automate this, you could say:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  + ' ALTER COLUMN ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
  + CASE is_nullable WHEN 0 THEN ' NOT NULL;' ELSE ';' END
FROM sys.columns
WHERE system_type_id = 99;

PRINT @sql;

-- EXEC sp_executesql @sql;

That said, you probably don't want to use these columns with distinct / group by anyway.


SQL transaction: Delete between two selects possible? I have two SELECT statements within a transaction (repeatable read)

Select @firstItem =id from myTable Where ....

--Do some more magic, so I can't concat the the two queries!!!!

and

Select * from myTable where parent=@firstItem

Is it possible that the children (second query) of the parent(first query) are deleted (from another transaction) after the initial/first select?

How can I prevent this with locks?

Is it possible that the children (second query) of the parent(first
  query) are deleted (from another transaction) after the initial/first
  select?

Certainly it is possible.

When you execute your first query only the result row of the first query has S-lock (I suppose your table has an index on id).

If you want to lock the rows where this id is a parent, you should just do this select in the same query:

select *
from myTable
where parent = (
    select id
    from myTable
    where ...);

This way you lock that id and all the rows where parent = this id so they cannot be deleted.

Note that only these rows are locked because your TIL is repeatable read, this means that if during the duration of this transaction other rows with this parent are inserted they can also be deleted.

To prevent insertions/deletions of the new rows with this parent you should use serializable TIL.

Here is my repro:
The first session(56) creates the table and fills it with id 1..10000
and then adds random parents from the same range 1..10000.

Then I search for most "popular" parent with max count of corrensonding childs,
and in session 54 I try to delete one of the child, the query is blocked.

The "central" window shows you the locks held, I used it twice: first to see what my first session locks, second time to see what the second session is waiting for. First session has 1+ 6 locks 1 on parent row and 6 on child rows that you see in the output of the first query, the second session waits on conversion from update lock to exclusive lock on the row it wanted to delete.
The session is "hanging" until rollback or commit.

Here is the picture:

if object_id('myTable') is not null drop table myTable;
create table myTable(id int identity not null primary key clustered,
                     parent int,
                     col varchar(10) not null);

alter table myTable
add constraint FK_paerent_id foreign key (parent) references myTable(id);

alter table myTable add constraint UQ_myTable_col unique(col);

create index ix_parent on myTable(parent);

insert into myTable with(tablock) (col)
select top 10000 cast(row_number() over(order by getdate()) as varchar(10)) as col
from sys.columns c1 cross join sys.columns c2;

update myTable
set parent = abs(checksum(newid())) % 10000 + 1;

select parent,
       count(*) as cnt
from myTable
group by parent
having count(*) > 1
order by cnt desc;

set transaction isolation level repeatable read;

begin tran

select id
from myTable
where parent = (
    select id
    from myTable
    where col = '481');

--rollback


Can I reduce MySQL memory usage, or do I need to add more RAM? I have a VPS with 2.25GB of RAM. MySQL is using 1.4GB with only four users on my site right now. It seems to use this amount even when no users are online. The available memory of my server regularly dips below 100MB, which is disturbing.
Yesterday MySQL kept crashing with OOM errors and resulted in database corruption, so that's what really prompted me to look into this issue.
I'm using the following configuration generated by the Percona Configuration Wizard (slightly modified):
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5_20120208
# MODIFIED SLIGHTLY

[mysql]

# CLIENT #
port                           = 3306
socket                         = "/var/lib/mysql/mysql.sock"

[mysqld]
slow-query-log=1
long-query-time=1

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = "/var/lib/mysql/mysql.sock"
pid_file                       = "/var/lib/mysql/mysql.pid"

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 256M     # Percona said 16M, but I need it larger to avoid errors
max_connect_errors             = 1000000

# DATA STORAGE #
datadir                        = "/var/lib/mysql/"

######### removed the following because the logs were taking up TONS Of space:
# BINARY LOGGING #
#log_bin                        = /var/lib/mysql/mysql_bin
#expire_logs_days               = 14
#sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0 # was previously 1
query_cache_size               = 0 # was previously 32M
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535 # was previously 4822
table_definition_cache         = 1024
table_open_cache               = 2048 # was previously 1024

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1456M

# LOGGING #
log_error                      = "/var/lib/mysql/mysql_error.log"
log_queries_not_using_indexes  = 1

As you can see, I increased max_allowed_packet, open_files_limit, and table_open_cache. I made the changes because I was receiving errors (I forget exactly what was causing them now) and making these changes caused the errors to go away.
Also, I noticed the innodb_buffer_pool_size setting is close to how much RAM MySQL is using (which may just be a coincidence), but as this answer suggested I ran the following:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

And the output was 2, which I believe means the recommended innodb_buffer_pool_size is 2GB. If that's correct, then my current setting is even below the recommended amount.
I really don't know what I'm doing and would prefer not to screw things up by blindly making changes to my.cnf. Are there changes I can make to reduce memory usage, or do I just need to upgrade my VPS plan?

Strictly speaking the calculation of the recommended buffer pool size above is not correct. It assumes InnoDB works actively with all data. In practice, InnoDB could only touch a fraction of the database.

Check how Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests changes over time.

mysqladmin -r -i 1 ext | grep -e Innodb_buffer_pool_read_requests -e Innodb_buffer_pool_reads

if Innodb_buffer_pool_reads doesn't change much then all working set (a part of the database InnoDB actively works with) fits in the buffer pool.

Ideally the buffer pool should be as large so Innodb_buffer_pool_reads per second is zero.

You can make the buffer pool smaller, then some reads will be served from the disk, not from the memory.


Where Database Alerts & Operators Exist In SQL Server? My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find it? I have master, model and msdb system backups.

That's all in msdb.  Note that the new server must be at exactly the same version to restore a system database.

  System databases can be restored only from backups that are created on
  the version of SQL Server that the server instance is currently
  running. For example, to restore a system database on a server
  instance that is running on SQL Server 2012 (11.x) SP1, you must use a
  database backup that was created after the server instance was
  upgraded to SQL Server 2012 (11.x) SP1.

Backup & restore: system databases - Limitations on Restoring System Databases


Query keeps on executing I'm executing below query, but it keeps on executing.

In place of some time I have a time value.

select distinct(col1)
from table1
where col2 > 'some time'
  and col1 not in
      (select distinct(col1)
       from table2
       where col2 not like '%XYZ%');

Apart from the fact that where not in () is almost always very slow, distinct will also be slow.

Try this, and let me know if it is better:

select distinct(col1)
from table1
    left join table2 on table1.col1 = table2.col1
        and table2.col2 not like '%XYZ%'
where table1.col2 > 'some -time'
  and table2.col1 is null;

Note: You have to bear in mind that there could be a magnitude of other issues affecting the performance, such as hardware, indexing, other users etc.; the above query represents some improvement, but is not guaranteed to run blindingly quick.


SQL maximum memory limit exceeded Microsoft SQL Server 2016 (SP1) (KB3182545)

  Server Memory : 61GB  SQL

  Server Max Memory : 56320 MB

I have three database (OneDB,TwoDB,ThreeDB) in the my production environment.

I running sp_BlitzCache on OneDB

  Available memory %7-8

I create 'Blitz' db for FirstResponderKit, sp_WhoIsActive and so on dba tools.

I running sp_BlitzCache on Blitz database

  Available memory %0

Differences between two databases:

SQL Server why exceeds the maximum memory limit? How can I resolve it?

Are your links supposed to show anything? Currently none works.

TT is right, that setting is just for the Buffer Pool and some memory clerks. See docs. There are other areas and even services part of SQL Server package that use memory.

  max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

Suggestion: You've most likely allocated too much memory to SQL. Dial it down - suggest going down to 40 GB to let also the OS breathe. If you have any signs of memory pressure then you might need to turn the knobs for more memory.

If you don't have any problems or memory pressure signs, then SQL Server it's doing its job.

Articles on the subject:

https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
https://www.brentozar.com/blitz/max-memory/
https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
http://www.sqlpassion.at/archive/2015/02/23/the-3-configuration-options-that-you-always-have-to-change-in-sql-server/
https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/
https://straightpathsql.com/archives/2017/02/sql-server-max-memory-best-practices/
https://voluntarydba.com/post/2013/03/19/Is-my-SQL-Servers-memory-over-committed.aspx

Later edit, after your reply:

You need first to establish what's the problem. Is it the report itself that there's no available memory? Do your applications/queries really suffer due to low memory? Is it just a training exercise?

You playing with database settings might just have caused the buffer pool to be flushed and now the reports look cool, but apps may suffer.

See what's in that server's memory by running:

https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-october-2017/

And after you find out how your server's memory is populated, only then you'll find out where's it spent and if you have a problem.


recover broken msde 2000 8.0 database i have a msde (mssql) 2000 / v8.0 database that has one corrupted table. Microsoft SQL Server Management Studio Express can see the database STRUCTURE, but not the data. i can see the DATA of the tables using "SysTools MDF Viewer 1.0" (working link), but that's just for viewing, not for recovering the data.

the databases are corrupt because of harddrive failure (i know, backup backup.. i'm focusing on getting all the data back for now :( )

UPDATE 1:
   tried the following query:

CREATE DATABASE NewDbName
      ON(NAME=’OldDbName,
            FILENAME='C:\my\path\to\file.mdf')
      LOG ON(NAME='mydatabase_Log',
            FILENAME='C:\my\path\to\file.ldf')
      FOR ATTACH

and got the following result :

Server: Msg 823, Level 24, State 2, Line 1
   I/O error (bad page ID) detected during read at offset 0x00000001354000 in file 'C:\my\path\to\file.mdf'.

Connection Broken

currently, i'm trying to investigate this error msg (with little progress), mainly reading here. haven't tried all that yet

i was wondering if i can't construct the db from the transactional history, but i don't have "a good backup" to transaction from. is it going to work if i try that method using a "empty db" (with just the structure)

really big thanks for giving me suggestions on this! highly appreciated!

Update 2:
    Ran all the (updated) steps and i got to the data (YAY!), i'm seeing it in the SQL query analyzer, i just need to figure out how to export it safely and i'll be able to breathe! THANK YOU VERY MUCH!

Update 3:
   following this recover guide

Update 4: (epilogue)
   First and foremost, thank you Craig for the awesome help, your input was invaluable in solving this crisis.

After following steps 1-4 (with no error messages) i got stuck at DBCC CHECKDB. The process was taking more than 6 hours to complete. With no IO/CPU activity, i decided to stop the operation as it seemed to hang.

After checking with the client, it was revealed that straight data backup is desirable (as triggers  were recoverable by other means ), so i proceeded with Step 5a and copied the data (using
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.) to a sane (new and empty) database. From there on all utilities (such as export / backup ) worked as expected.

for backup i used (writing here for reference)

BACKUP DATABASE backup_db TO DISK = 'c:\data_files\working.dat'

this should serve as (yet another) reminder: backup EVERYTHING

The transaction log file can't make up for a corrupted database file.  Its just going to try and rollback or roll forward transactions on the database file as it is recovered.

One more possible thing to try although I doubt it will work.  Even if it does, with a corrupt mdf, you're probably not going to get back everything you need.

As a preliminary step, and being sure that you have a copy of your mdf and ldf files somewhere safe, remove any entries of the original database

DROP DATABASE mydatabase

1. Create a new database

Use the the same physical file names and logical file names as was defined by the original corrupt database

--create the database
CREATE DATABASE mydatabase
  ON(NAME=’mydatabase,
        FILENAME='C:\my\path\to\file.mdf')
  LOG ON(NAME='mydatabase_Log',
        FILENAME='C:\my\path\to\file.ldf')

GO

2. Shutdown MSSQL

SHUTDOWN

Now copy in the corrupt mdf and ldf files over the newly created ones

3. Start MSSQL

You should now see the database in suspect mode

4. Put database into emergency mode SQL Server 2000

USE master
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases SET status= 32768 WHERE name = 'mydatabase'

5. restart MSSQL

5a. (optional)  Since you're data is now accessible via Query Analyzer, you could just export your important data into a new database.

-- create the new database
CREATE DATABASE mynewdatabase

-- import data from the old database into the new
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.

This won't help though with your triggers and metadata, but at least you'd have your data accessible in case the repair fails

6. Repair the database

This is the method I've used for repairing the database once its in emergency mode.

USE mydatabase
GO

-- put database into single user mode
sp_dboption 'mydatabase', 'single_user', 'true'
GO

-- repair the database, please be sure you have an extra copy somewhere of
-- your two db files
DBCC CHECKDB ('mydatabase' repair_allow_data_loss)
GO

-- turn off emergency mode
USE master
sp_configure 'allow updates', 1
UPDATE sysdatabases SET status= 0 WHERE name = 'mydatabase'
GO

-- check space allocation consistency
DBCC CHECKALLOC ('mydatabase')

-- verify db integrity
DBCC CHECKDB ('mydatabase')

-- take the database out of single user mode
sp_dboption 'mydatabase', 'single_user', 'false'
GO

-- disallow updates to the system tables
USE master
GO
sp_configure 'allow updates', 0
GO


Rebuild indexes on replication server I have 1 live server and 1 replication server. For reporting purpose we use the replication server and on the replication server we have created an index. So I want to know if I rebuild my index on replication server or do I have to remove the replication server first and then rebuild the index or can I directly rebuild the indexes on replication server?

According to official advice from Microsoft there is no need to remove the subscriber.

How do I rebuild indexes or tables in replicated databases?
There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.
How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

There is a potential, AFAIK, if you're using transactional replication that you will get a big queue of transactions that can't be applied to your subscriber (assuming that your publisher is quite busy otherwise you wouldn't be using replication). This may need to be managed, possibly by doing a REORGANIZE instead of a REBUILD or some such, but you can gauge that best for your situation.


CTE Insert then select with join I'm trying to figure out why this query doesn't work, the goal is to insert an item, then return its ID and use that ID to get related data from another table, however it returns an empty record set

WITH inserted as (
    INSERT INTO food_update(diary_id, food_id, value)
    VALUES($1, $3, $4) RETURNING id )
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)

However,

WITH inserted as (
        INSERT INTO food_update(diary_id, food_id, value)
        VALUES($1, $3, $4) RETURNING id )
(select id from inserted)

If I change it to the query above, it returns the newly inserted ID, then when I run SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted) (second part of the first query) with the newly inserted ID in a separate query it works, but they don't seem to work together. Am i not seeing something obvious, or is there a better way to do this?

I can provide the schema however it's a very general question really i'd just like to know why the insert doesn't seem to return the newly created sequence value. One other way of doing it was to use CURRVAL, though I'm not exactly sure how concurrency works with postgres so i wasn't sure if two queries ran at the same time it may get the second ones ID, though i'm pretty sure the tables meant to be locked while the query is being executed?

As requested here's a schema and a minimal example so that my issue is isolated + anyone that wants to test it can have a look. This implies there's an active database with a public schema

Schema creation + records

CREATE TABLE public.food (
    created timestamptz NULL,
    modified timestamptz NULL,
    id bigserial NOT NULL,
    "name" text NULL,
    CONSTRAINT food_pkey PRIMARY KEY (id)
);

CREATE TABLE public.food_update (
    id bigserial NOT NULL,
    created timestamptz NULL,
    modified timestamptz NULL,
    food_id int8 NULL,
    value numeric(10,2) NULL,
    CONSTRAINT food_update_pkey PRIMARY KEY (id),
    CONSTRAINT food_update_food_id_fkey FOREIGN KEY (food_id) REFERENCES food(id)
);

INSERT INTO public.food
(created, modified, id, "name")
VALUES(NULL, NULL, 1, 'TEST');

This is the query (slightly modified version of mine), and it shows that it still returns nothing.

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values($1, $2) RETURNING id )
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)

I'm not sure I understand your scenario, but the id returned from the CTE is unlikely to match any other table, since it has just been invented. Would something along:

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values(1, 12) RETURNING id, food_id
)
SELECT *
FROM inserted
INNER JOIN food
    ON food.id = inserted.food_id

do?


Linked Server Error on SQL Agent Job I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine.
Why is this? Is it just SQL Agent that requires MSDTC?
I don't believe it is permissions issues as the job runs under a service account that is in sysadmin role.

Message

  Executed as user: SERVERS\quinsql_sa. The operation could not be
  performed because OLE DB provider "SQLNCLI10" for linked server
  "TestServer" was unable to begin a distributed transaction. [SQLSTATE
  42000] (Error 7391)  OLE DB provider "SQLNCLI10" for linked server
  "TestServer" returned message "The transaction manager has disabled
  its support for remote/network transactions.". [SQLSTATE 01000] (Error
  7412).  The step failed.

The involved versions are SQL Server 2008R2 and SQL Server 2012. The SQL Job is on the 2008R2

I've not seen this exactly before, it does seem strange that you can run the query manually but not as an agent job. This is not unheard of though SQL Server Central has a similar post a couple of years ago, but has some reasonably complex queries involved.

What is the query you're trying to run doing?

As Tom V said, it would be helpful to know what version of SQL Server you're both running the query from, and where the linked server is connecting to

When you run the query manually:

Is it everything that the Agent job is doing (There isn't more than one step in the Agent job)?
Are you using the same account as the Agent job is run as?

As a start, it's worth checking your MSDTC configuration on both servers. I don't know if it'll be that in this case but it's best to try and eliminate simple things first. Have a look at these sites to get some details on that:

https://stackoverflow.com/questions/10130767
MSDN


The target table user of the INSERT is not insertable-into MariaDB 10.4.11 I did a wonderful thing of deleting all root users from mysql by accident. That kind of a day.

However before I panic I figured I would use this method here from searching online that didn't seem so bad.

"Add 'skip-grant-tables' to my.cnf under the [mysqld] section restart mysql"

then insert a new root user using "INSERT INTO mysql.user..."

When I do this I get an error I cannot find much information about

The target table user of the INSERT is not insertable-into
Does anyone have any ideas about this and how can I re-create a root user?

at first I thought this might have something to do with virtualmin because they talk about it here but I did this from CLI into mysql and it still says the same error

my environment

Centos7 Webmin version 1.940
Usermin version|1.780
Virtualmin version 6.08
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1

I finally found my solution.

This is a big thanks to @RolandoMySQLDBA on this thread here

The only problem I had with his answer was that in the /var/lib/mysql/InitFile.sql file the missing WITH GRANT OPTION;

example:

GRANT ALL PRIVILEGES ON *.* TO root@localhost
IDENTIFIED BY 'Gstlouis78' WITH GRANT OPTION;

Do not forget to remove the file when you are done, update to remove the init-file string you added the /etc/my.cnf

I am still not sure why mariadb was giving me the error "INSERT is not insertable-into"
and I cold not find any documentation that explains this was a mariaDB problem.  If anyone has more information about this please share


Maintaining replicated data in a staging-to-warehouse environment I am trying to come up with a way to move metric data from the staging server to the data warehouse servers.

I have a bunch of dimension tables that gets populated during the ETL process (on the staging server), these are being shifted to all the data warehouse servers (in a scale-out environment) using transactional replication... this is working fine due to the nature of these tables (basically since they are dimension tables, they can just be copied over as-is to all servers).

Now am having issues moving the ETL'ed data in the metrics (or data) tables to the warehouse server from the staging server...

because this type of data requires data (from the staging server) to be "Merged" (as in, Update, Insert, Delete) with the existing data in the warehouse server, and once that's done, the data in the staging server that has just been processed needs to be deleted (so it staging server doesn't keep building up data and becoming huge). The data in the data-warehouse must be maintained when the data in the staging server is truncated (so if data is deleted in the staging environment, it doesn't get deleted in the warehouse).

The only way I can think of doing this is by moving the data from "staging server" to "staging table on the data-warehouse server" (through replication), then perform the merge in the data-warehouse to the proper metric data warehousing tables. In this case the main data warehousing tables aren't involved in the replication directly, and so data in those tables doesn't get deleted once I truncate the metric tables in the staging server.

It just sounds in-efficient!, isn't there a way to get replication to handle all this (maybe through using clever replication filters?)

Maybe I shouldn't use replication at all, and just do custom linked server queries that shift and truncates data?, although from experience linked server queries tend not to perform very well.

Any guidance or just general idea dumps would go a long way!

P.S. Sorry if this isn't clear, any questions you have just fire them over

I presume you have substantial data volumes if you have separate staging and data warehouse servers.  From your posting It sounds like your staging server is doing a bit more than just staging the data.  You describe merging data into the fact table on the warehouse server, so I presume that the staging server actually has ETL processing on it as well.

I would not generally have recommended a transform-push approach like this; running ETL processing from the warehouse and pulling from a staging area is generally better.  The merge happens on the warehouse.  If you need to scale out you can replicate the warehouse, and the same operations will happen on the source and targets.

However, I guess you're stuck with the transform-push architecture, so you've got a couple of options:

Use SSIS or another ETL tool to copy the incremental load to a staging table on the warehouse and merge it to the fact tables from there.  This will be fairly efficient and can be done with set operations - i.e. a merge statement or insert and update.
Replicate the staging area into another database on your warehouse server and execute the merge from there after the ETL processing on the staging server has run.  This is probably more fiddly than the first option, and I can't really see any advantages to it.
If your staging and warehouse databases are located on the same database instance then you can create synonyms in the warehouse and merge through the synonyms.  The synonyms prevent a need to hard-code database refererences.


How to restore mysql table from .frm files so I'm trying to restore my tables with the *.frm files in my mysql folder. I have tried the method in this answer: Restoring MySQL Tables from .ibd, .frm and mysqllogbin files but I got an error saying [Errno 13] Permission denied: version check after running the mysql utilities. I am stuck here, I have tried other options which have failed as well. Kindly help

Permissions is an OS problem.  The files need to be owned by "mysql".  Were you 'root' when copying the files?  Did you preserve the ownership and permissions during the copy?  What OS are you using?


INSERT statement COPY equivalent I would like to transform this:

INSERT INTO users (username) VALUES ('JohnDoe');

Into a copy equivalent:

COPY users (username) FROM ('JohnDoe');

What would be a correct syntax for the copy statement?

The point of COPY is to receive the data from outside the query.
The closest syntax to what you're asking is

COPY users(username) FROM STDIN;

If done from within the psql interpreter in interactive mode, it displays this invite:

  Enter data to be copied followed by a newline. End with a backslash
  and a period on a line by itself, or an EOF signal.

If done from a non-interactive program, dedicated functions can be used to send the data. They're provided by the PostgreSQL driver that the program uses.


Does the Query cost in Sql Server account for buffering? If I am comparing 2 queries by checking their relative "Query cost" as a high level indicator of performance, does the buffer status have any effect on the values shown in the execution plan?

Yes and no, depending on what you mean by 'buffer status'.

The state (content) of the buffer pool does not directly affect the estimated cost of an execution plan (the cost model assumes every query starts with a cold data cache.

The size (not content) of the buffer pool can have an impact on plan selection for several reasons.

The first occurs when memory-consuming operations like hash and sort are present. The amount of memory estimated to be available for the memory grant depends on buffer pool size (note interesting bug in this area).

The following AdventureWorks-based query shows an example of this costing difference based on the size of the buffer pool.  In this case, the plan shape is the same, but these costing differences can, and do, change plan selection with larger tables or more complex queries:

-- Set to 256 MB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 256;

RECONFIGURE;

-- Estimated cost 5.92241 with 256MB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

-- Set to 2GB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 2048;

RECONFIGURE;

-- Estimated cost 5.48355 with 2GB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

Second, the optimizer contains logic to assess (for example) the chance that a page read from disk (under the cold cache assumption) during execution will be required again, and if so, whether it will still be available from cache.

More to the point though, optimizer cost estimates are mainly only useful for internal server purposes. They are not intended to be used to assess potential performance, even at a 'high level'. The model is an abstraction that happens to work reasonably well for the internal purposes it was designed for. The chances that estimated costs bear any sensible resemblance to real execution costs on your hardware and configuration is very small indeed.

Choose other metrics to compare performance, based on whatever real issues are important to you.


MySQL Incremental backup on Master/Slave I've a mission critical MySQL Master/Slave config, which I'm backing up with innobackupex. I want to speed it up a little as the backup is about 60GB, so I'm considering something like a full backup on Sunday's and incremental backups from Monday to Saturday, or something like that.

My questions, as I've master and slave:

Is a good idea to backup from slave?
Am I going to be able to spin up a new slave from this backups? (if not, I'll backup from master)
Do you recommend any other incremental backup strategy?

Thanks.

It's a good idea to take a backup from a slave as long as you make sure the slave is consistent with the master. Usually a regular pt-table-checksum job gives you a good level of confidence.

Yes, you can create clones from a backup taken from the slave. Just make sure you add --slave-info option so xtrabackup records the binary log position. You can use it in CHANGE MASTER when setting up a new slave.

At TwinDB we decided to go with differential backups. It's when you take a full copy on Sunday and each other day you save the difference between Sunday and the current day. That has some benefits:

Faster recovery time. You need to prepare the full copy and only one incremental copy.
Lower probability of failure. If you have a chain of incremental copies every copy must be good otherwise you can't restore. With differential backups there is only one copy to apply on top of the full copy. So it's less likely to fail.
In most of cases total size of backup copies is still reasonable. But of course with incremental copies save more space.

Between daily backup jobs it makes sense to store binary logs. So you can do point in time recovery and recovery time can be reasonable.


Disabled SSL on SQL Server, now connection fails I have a customer that is worried about the latest SSL vulnerability POODLE. They have a utility (IIS Crypto 1.4) that disables SSL 2.0-3.0 on the servers they run it on. They have disabled SSL on both the server my service is running and the remote SQL Server. The SQL connection string is pretty standard:

Server=myServerIPAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

When my service starts it tries to perform a Linq to SQL query and gets the error:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

If they re-enable SSL on the SQL Server it works fine. My program is a Windows Service built on .NET 3.5. What is the cause of this error, and where can I make adjustments so my service will run?

edit:
below is the stacktrace that is spit out by my program when it tries to load

System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArrayTSource(IEnumerable`1 source)

You are using SQL Authentication. This requires the use of a self signed SSL certificate which is provided by the SQL Server so that the client can encrypt the authentication packets of the connection process.

My guess, no SSL breaks this process.


Doubts on Setting up Master-Master replication (Percona) I have two 5.6 Percona servers in which I'm planning to setup Active master- Passive master replication. Secondary master will be in read-only mode.

Caveat is that the servers in which i'm going to setup is live databases. Master is already running with server-id=1 and it is in place. Secondary master is newly created instance.

Now to backup the production master and copy it to the secondary master, is it mandatory that I need to take backup the live database and then copy it to secondary? Or will it suffice if I take last night's backup?

Also, when I set the Active master to be the slave for secondary, will it impact my production?

I know I need to do this off-live hours but situation demands, hence I wanted to reconfirm before proceeding.

Please highlight on what impact that could do in live database for setting up this replication.

Set up slave

You can set it up from the nightly backups. That's actually a common practice to provision slaves from backups to also have them tested. You can go back as far as you have binlogs stored on the master (expire_log_days variable).

The impact of having a slave starting from older backup is that it has to fetch the logs from the master but that shouldn't be a heavy operation. Unless your master is already on the edge you shouldn't really notice it. The other impact is that it will take longer for the server to catchup which is of course expected.

Switchover

Switchover can have some impact if you use auto-increment IDs and your passive master is not fully caught up with replication. Best is to make the old active read-only for a second or two while checking the passive for master position. Once there is no change you can move change it to be the active and make it writeable.

Depending how you point to the active master (DNS switch for example) it might take some time during which time both masters will accept writes and the old active being read-only will report errors.

I hope this helped.