You can index a view that contains an XML column. You cannot index a view that contains any of the XML datatype methods ( eg .nodes, .query, .value, .exist ).

I would say your greatest issue is always casting the VARCHAR column to XML.  Is there are a particular reason you are not using the XML datatype?  Although it can alter XML slightly ( eg whitespace ), storing it as VARCHAR means you can't take advantage of the usual performance tuning methods with the XML datatype such as typing the XML ( XML SCHEMA COLLECTION ) and XML Indexes.

In the meantime, add the text() accessor and avoid repeating XML calls ( ie no need to .value IsBlocked as you have it as WHERE clause criteria ) for a performance boost:

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

These were my results from a simple 100k row test rig I created:

Simple test rig:

-- Simple test rig
USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.ci') IS NOT NULL DROP TABLE dbo.ci
IF OBJECT_ID('dbo.ct') IS NOT NULL DROP TABLE dbo.ct
IF OBJECT_ID('dbo.cv') IS NOT NULL DROP TABLE dbo.cv
IF OBJECT_ID('dbo.My_View') IS NOT NULL DROP VIEW dbo.My_View
IF OBJECT_ID('dbo.My_View2') IS NOT NULL DROP VIEW dbo.My_View2
IF OBJECT_ID('dbo.My_View2_4') IS NOT NULL DROP VIEW dbo.My_View2_4
GO

CREATE TABLE dbo.ci ( Id INT IDENTITY PRIMARY KEY, ContentType_id INT, data VARCHAR(MAX) )
CREATE TABLE dbo.ct ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Latest BIT )
CREATE TABLE dbo.cv ( ContentItemRecord_id INT PRIMARY KEY )
GO

;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.ci ( ContentType_id, data )
SELECT rn, '<Data>
    <MyImage>
        <MyID>' + CAST( rn AS VARCHAR(MAX) ) + '</MyID>
        <Image>' + CAST( NEWID() AS VARCHAR(MAX) ) + '</Image>
        <IsBlocked>0</IsBlocked>
        <IsPublished>1</IsPublished>
    </MyImage>
</Data>'
FROM cte
GO

CHECKPOINT

INSERT INTO dbo.ct ( Name, Latest )
SELECT 'MyImage', ContentType_id
FROM dbo.ci

CHECKPOINT

INSERT INTO dbo.cv ( ContentItemRecord_id )
SELECT Id
FROM dbo.ci
GO

-- Original view
CREATE VIEW [dbo].[My_View]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1
GO

CREATE VIEW [dbo].[My_View2]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked/text())[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished/text())[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

--... a few other aborted attempts. 2.1 to 2.3

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

CREATE VIEW [dbo].[My_View2_5]
AS
WITH xoutput AS (
    SELECT ci.[Data],  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
FROM xoutput
    CROSS APPLY (SELECT CONVERT(xml, [Data]).query('.')) AS T(x)
        CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

/*
SELECT *
FROM [dbo].[My_View]
GO
SELECT *
FROM [dbo].[My_View2_4]
GO
SELECT *
FROM [dbo].[My_View2_5]
GO
*/


There is no concept of Master in Vertica.

But there is the concept of Initiator - this node will be the node who receives the connection and will compute the query plan, divide the workload among the nodes and then gather the result sets and format the output.

As for the multi-read and multi-write. Vertica is fully ACID complaint, this articles will give you more info.
https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/ConceptsGuide/Other/Transactions.htm


What would be the Candidate key (s) of this relation?

Unless I have mistaken the question, your response of candidate keys is incorrect.

Candidate Key AB:
AB -> CD, C->E, DE->P, B -> G. This is a candidate key.

Candidate Key AG:
A, G, and AG have no inherent relations. Just because B->G doesn't mean that G->B (a similar mistake was made after assuming B was solved and getting the columns of A and B). This is not a candidate key.

Candidate Key CD/DE:
This is not a candidate key. There is no possible way for F to have a candidate key that does not include A, as there is no way to determine A without it being part of the key. Similarly, nothing implies B and thus it is required to be part of any candidate key.

Since AB is a candidate key, there is no reason to go further. Any candidate key must include AB and AB is a candidate key, therefore the only candidate key is AB. A and B must both be supplied as part of the key or else there can be no unique identifiers to their values. Since keys are typically generated with the intent to use the least information possible to determine uniqueness, AB being required for row uniqueness, and AB being a potential key then you can select it as your primary composite key and move on.


archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

I'd recommend a slightly different approach.  Instead of testing for number of failures because a large number might lead to low disk space, test for low disk space directly.

archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f || (df -P /var/data/pg_wal|awk "NR==2 && \$4< 400000 {exit 0}; NR==2 {exit 1}")'

But be advised that rsync is a dangerous approach in the first place, because it doesn't guarantee a successful fsync of the file written to the remote side (and the directory that that file is in) before it returns success locally.

Or an entirely different approach: set your replica up as a streaming replica, rather than log shipping replica.  Then set "wal_keep_segments" to a value a little bit less than the amount of space reserved for pg_wal.  Note that this only works if the space is hard reserved for pg_wal, that is, no one can "borrow" some free space for other purposes, like temp files.

And of course, if your replica was down for a while and you didn't notice, then you probably need a monitoring solution as well.


How to create a database with "polymorphism"

Your best bet is likely going to be to create a single child table that references all three of the other tables onto a single GAC_id. So you'd end up with something more like this:

create table gac (
    GAC_id,
    accelerometer_id,
    loadcell_id,
    potentiometer_id,
    primary key (GAC_id),
    foreign key (accelerometer_id) refrences (accelerometer.accelerometer_id)
    foreign key (loadcell_id) references (loadcell.loadcell_id)
    foreign key (potentiometer_id) references (potentiometer.potentiometer_id)
)

Here, each foreign key should reference the primary from the appropriate table:

create table accelerometer (
    accelerometer_id,
    name,
    range,
    serial,
    primary key (accelerometer_id))
create table loadcell (
    loadcell_id,
    name,
    description,
    primary key (loadcell_id))
create table potentiometer (
    potentiometer_id,
    product_id,
    tag_id,
    primary key (potentiometer_id))

This will work from a relational standpoint if I understand what you are looking for correctly. Then you just make sure that all items in gac have a unique index associated with them, they cannot be null, and that each entry has to have a corresponding entry in the appropriate parent table. For Access specific SQL, use the MSDN Access SQL Reference to see Access specific queries to perform each task.

From a design standpoint, it would be a horrible idea to have three different tables with identical primary keys (GAC_ids in this case). Theoretically you could do it, but there are all sorts of issues that it can and will cause from a practical standpoint.

Theoretically you could also do something like what you had originally:

create table accelerometer (GAC_id, name, range, serial, primary key(GAC_id, <insert one of the other fields here>))
create table loadcell (GAC_id, name, description, primary key(GAC_id, <insert one of the other fields here>))
create table potentiometer (GAC_id, product_id, tag_id, primary key(GAC_id, <insert one of the other fields here>))

Then you could just create a view that joins them all on the GAC_id. Of course, this will only work if the GAC_ids correlate one-to-one:

create view my_view as select * from accelerometer a
    full outter join loadcell lc ON a.GAC_id = lc.GAC_id
    full outter join potentiometer p ON a.GAC_id = p.GAC_id

That would create a view that accomplishes the same task as the child table in the first explanation i gave, but it has the advantage of auto updating whenever an item is added to any table. the only catch is that if there is no corresponding entry in any of the other tables, you will see null values where those entries should be.

look into the MSDN Access SQL Reference for the specifics on how to do the different things i've mentioned, in Access SQL.

One thing that is worth mentioning is that Access SQL may not support full outter joins. I know that is a quirk with MySQL, but i am not familiar with Access SQL, so you will have to use the reference, and probably this website(dba.stackexchange.com) and google to see exactly how you can/should build your create queries in Access SQL.


From my limited understanding, blocks should be embedded in the C# or put in a package and called.  I've seen it like this:

@"
BEGIN
   SchemaName.PackageName.ProcedureName(p_LoginID => :loginID,
      p_Password => :password, p_IPAddr => :ipAddr)";
END;”.Replace(Environment.NewLine, “\n”);


That's because the WHERE executes before the SELECT. You could try calculating  DELAY inside the WHERE

WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID
AND TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE,
             SYSDATE,
             ITK_NORMAL_BEGIN,
             ITK_NORMAL_END,
             ITK_WEEKEND_BEGIN,
             ITK_WEEKEND_END,
             ITK_FREEDAY_BEGIN,
             ITK_FREEDAY_END),
        GET_HS(ITK_OPENED_DATE,
             SYSDATE,
             ITK_NORMAL_BEGIN,
             ITK_NORMAL_END,
             ITK_WEEKEND_BEGIN,
             ITK_WEEKEND_END,
             ITK_FREEDAY_BEGIN,
             ITK_FREEDAY_END)) -
        ITK_EXPIRATION_TIME,
      2) < 0


MySQL replication not finished on next site call

While an application-independent solution could be possible, in general, a replication-aware application could be much better, because you can use the high performance of asynchronous replication for most of the time, and only revert back to (slower) synchronous replication when it is needed.

This is not a theoretical method, we at Wikipedia use this model with specific code called "chronology protector", which only gets triggered when an important event happens (e.g. an edit) and you don't want the user to be served outdated results.

In order to do this, after doing your master writes, you should keep your master replication file and position (if not using GTID), or the GTID coordinates (if using GTID) after your write (once the transactions has been commited on the master), and then on the replica connections, wait until the server reaches those positions or higher. There is some extra details to consider, such as when exactly to wait (on write or on read), for how long until you give a timeout (e.g. if replica breaks or lags), but that is highly dependent on your environement.

In terms of implementation, MASTER_POS_WAIT() / WAIT_FOR_EXECUTED_GTID_SET() are the functions commonly used for this.

Some of the clusters you mention give you shortcuts to perform this easier (e.g. Galera's causal reads), but the logic is similar- you don't want to be on fully consistent mode all the time; just forcing it when necessary.


what could be preventing my transaction log autogrowth to be set to UNLIMITED?

If you open BOL article sys.database_files (Transact-SQL)

you'll find that
268435456 = Log file will grow to a maximum size of 2 TB

2 Tb is the max size of SQL Server database log file, if you need it more than 2Tb you add the second log file.

Here you can find Maximum Capacity Specifications for SQL Server that states that 2Tb is the maximum log file size:


Since you've already added the successful login auditing to the instance, you can dump the error log data to a #temp table, then parse the individual login names, and then find all the rows in sys.server_principals that don't exist in the filtered data.

CREATE TABLE #x
(
  LogDate datetime,
  ProcessInfo nvarchar(max),
  [Text] nvarchar(max)
);

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

INSERT #x(LogDate, ProcessInfo, [Text])
  EXEC master.sys.xp_readerrorlog 0, 1, N'Login succeeded', NULL,
    @dt;

;WITH p(pattern) AS
(
  SELECT SUBSTRING([text], CHARINDEX(CHAR(39), [text]) + 1, 500) FROM #x
),
x(name) AS
(
  SELECT SUBSTRING(pattern, 1, CHARINDEX(CHAR(39), pattern)-1) FROM p
)
SELECT p.name, p.create_date, p.is_disabled
  FROM sys.server_principals AS p
  WHERE p.principal_id > 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);

Assuming that perhaps your current error log file is less than a month old, you can see the following article for ideas on searching more than one error log at a time:

Search multiple SQL Server Error Logs at the same time

In the future, I would rather create an audit specification or extended events session to avoid polluting the error log.

CREATE SERVER AUDIT GoodLogins
TO FILE (FILEPATH = 'C:\temp\GoodLogins\');
GO

CREATE SERVER AUDIT SPECIFICATION GoodLoginsSpec
  FOR SERVER AUDIT GoodLogins
  ADD (SUCCESSFUL_LOGIN_GROUP);
GO

ALTER SERVER AUDIT GoodLogins WITH (STATE = ON);
GO

Then you can just query all of the audit data:

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

;WITH x(name) AS
(
  SELECT server_principal_name
    FROM sys.fn_get_audit_file('C:\temp\GoodLogins\*', NULL, NULL)
    WHERE event_time >= @dt
    GROUP BY server_principal_name
)
SELECT p.name
  FROM sys.server_principals AS p
  WHERE p.principal_id > 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);


SSIS: Check file for row delimiter and dynamicly update row delimiter in Flat File Connection Manager at run time

I just found a Easy way here

You Just need to Set an Expression to Replace the '/r'(CR) in Data Flow Task

Just Awesome trick that can accept both CRLF and LF within a SSIS Package


SQL Server Resource governor user-defined resource pools

Think of the resource pool as physically dividing the CPU, memory, and IO of the server. This can be useful when you need to limit resources used by overlapping workloads. The workload groups further limit resources used at a query level within a pool. This can be useful when you need to limit resources used within a workload, perhaps to improve throughput.

The resource governor overview provides a few use cases that can only be satisfied with resource pools as opposed to resource groups. For example:

  Throttle IO resources for operations such as DBCC CHECKDB that can saturate the IO subsystem and negatively impact other workloads.

Resource pools have the MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME options to limit IO. Workload groups do not have any options to limit IO.

Workload groups are also limited in how they can control CPU usage. The REQUEST_MAX_CPU_TIME_SEC option doesn't actually enforce a hard limit on CPU by query:

  Resource Governor will not prevent a request from continuing if the maximum time is exceeded. However, an event will be generated. For more information, see CPU Threshold Exceeded Event Class.

To properly limit CPU through a workload group you would need to use the MAXDOP option for workload groups but that may not be granular enough. Resource pools offer much better options for preventing runaway queries from using all of the CPU available on a server.

It may seem that workload groups could be used to balance query memory grants for competing workloads with the GROUP_MAX_REQUESTS and REQUEST_MAX_MEMORY_GRANT_PERCENT options. However, consider a workload that sends thousands of queries of various sizes to run against the server with a concurrency of up to 40 queries at once. A small percentage of the queries are memory hogs and if left unchecked would use all available memory for query grants. You find through performance testing that limiting REQUEST_MAX_MEMORY_GRANT_PERCENT to 4% gives reasonable overall throughput. If you only configured this through a workload group you'd end up with:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 4

That could lead to good performance for that workload but could use all of the memory on the server. If you need another workload to run at the same time then you need to do configuration at the resource pool level. Suppose that you determine that the original workload can get by with 50% of the memory on the server. At the pool level you could set the following:

MAX_MEMORY_PERCENT = 50

And at the workload group level you could set the following:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 8

That would give you reasonable throughput for the first workload but also allow the second workload to run at the same time. The resource pool is used to divide resources between workloads and the workload group is used to divide resources between the queries that belong to the pool.


I did a small simulation of that situation, in a simple bash script that does that:

➜  rsandbox_5_6_30 cat test.sh
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

Result:

➜  rsandbox_5_6_30 cat tx1
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

Transaction status:

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

If you execute the first transaction within certain delay, you can incur on a frame where one of the subsequent transactions is killed by deadlock detection, making the other transaction to insert the value.

The status of the alive transaction reflects that still needs an explicit commit (undo log entry 1).

-- Edited 2nd

"Neither can acquire an exclusive lock for the row because of the shared lock held by the other."  Actually the deadlock kills only 1 transaction, not both. So the EL is acquired by the 2nd session.

Due to how gap locking works we see the difference between executions. The deadlock happens as an intention of the 3rd transaction to acquire the shared lock when a duplicate key error is detected. When this happens, the 2nd transactions has the shared lock acquired, producing a deadlock and killing the 3rd transaction.  This is a limitation in the amount of concurrent writes over the same record.


Azure SQL Database throwing error 1132 "The elastic pool has reached its storage limit" error, even if it's not full

The error message refers to the elastic pool so the problem might relate to another database than the current one. You can retrieve the information about the elastic pool using the following DMV query vs the Master database
SELECT TOP 5 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB, *
FROM sys.elastic_pool_resource_stats


SQL server agent unable to open step output file the step failed - mapped drives

Once you get the code straightened out for the local drive, you'll need to set permissions on the network share for the server's computer account since you are using the default NT Service account for the services. The computer account is the server's name and then a dollar sign.

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window.

You would set the permissions on the remote server. RDP to it, navigate to the share and grant the access there. I am not sure for Azure how it works. But if this were a hosted server instead of a cloud server, it would be on the actual folder and not on the mapped network drive.


How to keep history of SQL Server stored procedure revisions

While I am in full agreement that source control is the right way to do this, I also understand that not all environments are disciplined enough to rely on that alone (if at all), and that sometimes changes have to made directly to keep the app running, save a client, what have you.

You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). Assuming you have a utility database:

USE Utility;
GO

CREATE TABLE dbo.ProcedureChanges
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(100),
    EventDDL     NVARCHAR(MAX),
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     NVARCHAR(255),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Now in your database, first let's grab what we'll call "initial control" - the current version of the stored procedures:

USE YourDB;
GO

INSERT Utility.dbo.ProcedureChanges
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName
)
SELECT
    N'Initial control',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
FROM
    sys.procedures;

Now to capture subsequent changes, add a DDL trigger to the database:

USE YourDB;
GO

CREATE TRIGGER CaptureStoredProcedureChanges
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

    SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

    INSERT Utility.dbo.ProcedureChanges
    (
        EventType,
        EventDDL,
        SchemaName,
        ObjectName,
        DatabaseName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO

Over time it will become easy to see and compare changes to procedures, watch new procedures get added to the system, see procedures get dropped, and have a good idea of who to talk to about any of these events.

More information here:

http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/


How to make a replica slave member to be primary member?

Your problem is that one node of three cannot be "majority". If you remove those two nodes from setup, that one can become primary. Altho better solution is to get those two other nodes to response.

That error message tells that this node (where you gave rs.status() -command) cannot connect to other two nodes. I guess that there is a firewall between and there is no hole for port 27017.

As always, the best source for the problem solving is mongodb.log file, where program tell you what is wrong with setup.


Mysql Fulltext search doesn't return any results

Did you know that MySQL has a stop word list ?
For MyISAM, there are 543 words that do not get included in a fulltext index ???
In the MySQL 5.6 Docs on this (Stopwords for MyISAM Search Indexes), you can see the list.

name is on line 55 column 4
your is on line 108 column 4

That why all attempts to search on those two words will fail.
If you convert the table to InnoDB, drop and create the FullText index, you will find them. There are only 36 stop words for InnoDB FullText Indexes.
If the table has to remain MyISAM, you have to provide an empty stopword list, restart mysqld, drop and create the FullText Index again.
Here are some of my past posts on this:

May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Apr 24, 2013 : MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'

Please refer to the Jan 26, 2012 post on how to setup and empty stopword list.
UPDATE 2018-05-04 22:44 EDT
If you run this in SQL Fiddle
SELECT * FROM site_plugin_products_cache_texts
WHERE match(item_text) AGAINST ('+CALL' IN BOOLEAN MODE);

This will work because CALL in not in the stopword list.


MS SQL Server Copy Database Wizard vs Backup / Restore Script

I suggest using regular SQL jobs, using the Ola Hallengren solutions for your backups.
 Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com
It's used by a lot of DBAs, and has been around for years.

All you have to do is deploy the scripts and add some schedules, to your full/diff and log backups job.
You can also fine tune everything to fit your needs. There's code examples and options on the Ola site.
For example this link gives you all the options for the backup SP.

A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.

Another nice to have, is that it also includedes jobs like IndexMaintenance and IntegrityChecks, which are needed on any SQL Instance. So it's a good all in one solution.


For the first part, expiring based on a timestamp, you will want to check out TTL collections (requires version 2.2+).  They won't cap size like a capped collection, but let's you set the constraints via time as long as you have a BSON Date type field to index on.

For the second part, automatically storing them to a new database before expiry, I think what you need is a periodic query (via cron, at, or other and in the language of your choice) to run and grab those documents before they expire.  This should just be a simple range based query over time, find the appropriate docs, insert to new DB, always at enough of an offset to avoid missing documents.

The other approach I can think of would be to tail the oplog, look for deletes on that collection, and then transcode them into inserts on a different DB.

For extra safety you could run a secondary on a slavedelay to give an extra window if one of the approaches above failed.

I don't know of anything that will do this by default, but I believe either of the approaches above should work.


FK relationships between schemas in azure SQL don't work

The error message is self-explanatory. You need a primary key or unique index/constraint on the UserGUID column in dbo.[User].

ALTER TABLE dbo.[User]
  ADD CONSTRAINT PK_User
  PRIMARY KEY (UserGUID);

If that is there (you edited it in later), you shouldn't get that error message.

If you are only getting this error message when deploying a dacpac, can you post that dacpac somewhere? I can't make that code fail on Azure SQL Database by running it directly, but it's possible the dacpac has creation scripts listed in the wrong order, or the constraints are separated out into a different (later) step and aren't in the right order either, or the dacpac is only deploying changes to one schema and the other one hasn't been updated.


What is the correct way to make an exact copy of a database with pg_dump?

I'm not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you're using is pretty much all you need (you might need to specify the password with a flag - not sure), unless you do want to do it in two steps with --schema-only and then with --data-only:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql.

This tutorial on pg_dump and pg_dumpall should help get you going too.
Finally, best thing you can do is run the scripts after they're generated, in a test database and spot check for the different things you'd expect to get created (Table, View, Primary Key, Foreign Key, other constraints, etc) before you make any drastic changes to the original database it came from.


Should I use the deprecated MD5 function in SQL Server?

From Microsoft's Documentation:

  When a feature is marked deprecated, it means:

  The feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features.
  We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.
  For new development work, we do not recommend using deprecated features.

Another part of that page says:

  The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been determined.

Depending on how you're using MD5 and HASHBYTES, you may have other options available aside from using SHA_256.

You may be interested in using this methodology I documented to determine which rows have changes.  It uses a rowversion column in the source table to allow you to instantly see rows that are new or have changes.  This avoids using a deprecated feature, and is much faster than any hashing function will ever be.


How to import Azure blob file into Sql Server 2008

Use AzCopy, the Azure Storage Explorer, or even the Azure Portal to download the file to somewhere the SQL Server can read it, then use BCP or BULK INSERT to load it.


What MySQL configuration options require more than editing the my.cnf file and restarting the server?

It depends on which version you are using.  By the time you get to 8.0, virtually everything can be dynamically changed.  Here are 2 types of pages, each with a list.  Note the version number in the URL; change as needed.

http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html -- Note the "Dynamic" column.

innodb_log_file_size used to be one of the worst settings to change.  You could not simply change the config and restart -- the server would complain that the size does not match.  Recently (sorry, don't know what version), the 'right thing' would be done with the files.  But it still required a restart.

In my experience, 90% of users can get away with setting innodb_buffer_pool_size based on RAM size, then ignore the rest of the settings.

(If you wanted to talk specifically about innodb parameters, say so.)

If you want a critique of your settings, provide RAM size, SHOW VARIABLES; and SHOW GLOBAL STATUS; after it has been up at least a day.  I have about 150 formulas to test; they typically lead to 5-10 recommended changes of the VARIABLES.


Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7

You are correct, this was different in earlier versions

  ...However, in MySQL prior to version 5.7 the default behaviour has been
  more forgiving because it will not complain and then arbitrarily
  choose any value! It also has an ANY_VALUE() function that could be
  used as another solution to this question if you really needed the
  same behaviour as before...

Source

But this change is a good thing, as the error used to be silenced.
I would recommend changing the query to get the results you want. I believe you want the one where field1 is the largest.

Field1 is the primary key, so you could possibly do something like the following

SELECT docs.field1,docs.field2,docs.field3,docs.field4
FROM
(
SELECT max(field1) as field1 ,field2 FROM docs
GROUP BY field2
) temp
INNER JOIN docs
on temp.field1 = docs.field1
ORDER BY field2, field1 desc;

DBFiddle

The other way around this are to either disable the functionality or use ANY_VALUE() but I would advise against that.

ANY_VALUE() can give an inconsistent resultset, an example could be the number 50 instead of the expected 17 as field4 where the field1 column = 27908.

DBFiddle


Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2


What are "sys.extended_index..." indexes?

Extended Indexes are full text indexes, XML indexes, and Spatial indexes.  Reason, the 3 types of indexes are better handled outside the B+ tree index.

I know you do not care to correct this but, did you recently upgrade?  Try changing the compatibility level if it's not SQL Server 2014 and run CHECKDB again

You can identify the table object for the extended index error by using the below code. You may need to tweak the name in the where clause if I have it wrong.

SELECT
    OBJECT_NAME(PARENT_OBJECT_ID) AS table_object,
    name,
    create_date
FROM sys.internal_tables it
WHERE INTERNAL_TYPE = 207
AND it.name = 'extended_index_1066482673_384000'

Hope this helps!


MySQL partitioning relatively large DB Table

So going down your list of questions:

Do this partitioning do any good in terms of performance with my
existing table structures and indexes.

No!  If you always query on date, maybe.

Do this partitioning do any good for selects statements I have
specified above?

Only your third query, and that's a maybe.

What this partitioning will impact on other three queries where no
ArrivalDate is used in WHERE condition?

It depends on if the secondary indexes are also partitioned.  But generally there can be more overhead as you have to query each partition that possibly contains rows.  Partitioning also breaks a lot of features in MySQL so it shouldn't be undertaken lightly.

Do this partitioning do any good for lots of inserts going every
moment?

No.  You're still appending according to the meaningless row identifier.  Everything will be stuck on the last page.

I know we can delete particular partition, but is it easily possible
to archive particular partition to another archive database before
deleting?

I'm not sure about MySQL - moving/deleting partitions is relatively simple in other databases.

Do I need to worry about column id int(10) when data is increasing
rapidly?

Yes!  You either fix the problem now or have to rebuild the table with a larger, equally meaningless row identifier and further your woes.

Is there any other better idea, to get maximum possible read and write
performance in the database?

Yes.  It's called define the primary key, and cluster on that.
Right now you have a big unorganized mess of rows.  I'd call it a heap, but people reserve that for a certain type of unorganized mess of rows that hides the row pointer from the user instead of declaring it a primary key.
Your rows are located in the table according to their approximate insert order.  To locate these rows more efficiently you slap an index on top so you can find where they're hidden, but even with the index you could be reading one page per row, or the entire table, depending on how the data is distributed.  See my answer here for a very basic illustration: Save performance with large update on Index with Included Column
So it's not entirely obvious from your question if (SerialNumber, ArrivalDate, ItemCode) is unique, but let's assume it is (we can adjust later if an additional column is necessary to define uniqueness).  If we define your table like so:
CREATE TABLE `ProductCatalog` (
  `SerialNumber`    varchar(20)  DEFAULT NULL,
  `ItemCode`        varchar(5) NOT NULL,
  `ArrivalDate`     datetime NOT NULL,
  `BasePrice`       decimal(10,2) DEFAULT NULL,
  `BatchCode`       tinyint(3) unsigned DEFAULT NULL,
  `Type`            varchar(5) DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID`         tinyint(3) unsigned DEFAULT NULL,
  `Model`           varchar(10) NOT NULL DEFAULT 'RX209',
  `Description`     text,
  CONSTRAINT PK_ProductCatalog PRIMARY KEY (SerialNumber, ItemCode, ArrivalDate)
)

Three things happen:

We save space because we're not slapping an unnecessary row pointer on every row
We are guaranteeing uniqueness
Rows are now grouped (for the most part) according to (SerialNumber, ItemCode, ArrivalDate).

The consequences of #3 is that all of the queries you submitted will traverse one b-tree and at usually no more than 1-2 pages to satisfy the query.
Yes, this can cause page splits (fragmentation), but the impact of page splits is generally less than the cost of additional indexes (which also fragment) and more read I/O.  If it gets really bad (which you'll monitor just like anything else), you can rebuild the table (clustered index).
Other general observations:

Don't store timestamps as anything other than DATETIME/TIMESTAMP.  The data type exists for a reason, if you have to convert on insert, that's better than having to convert on read.
If one or more of Brand, Model, or Description is dependent on the ItemCode, then that data should be stored in its own table.  Makes your main table even more compact.
If SerialNumber and ItemCode are fixed length, you're probably better off using CHAR(<the max length>) versus VARCHAR.


Convert Ruby regex to Postgres regex, for selecting invalid email addresses

You should debug regexp by first just selecting the raw text make sure it is perceived at the textual level the way you want.

select '\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z';

                 ?column?
------------------------------------------
\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z

So the doubled backslashes did not get condensed to single, the way you probably thought they would. Note that this is with the default (on) setting of standard_conforming_strings setting.

So regexp parser is going to see a backslashed backslash, which will match a literal backslash character, followed by a literal 'A', which will match a literal 'A'.  Which is obviously not what you want.

You can remove the double backslashes.  Then you have to worry about whether all the notations you use are supported in PostgreSQL. \W is illegal in PostgreSQL within brackets, so at least that one is not supported.


Is PostgreSQL appropriate for processing this large but simple dataset?

Seems like your x,y is a fixed pair. So you can combine them to form a single key. If all your access is based on a key, then the NoSQL dbs can fit your bill. Some NoSQL dbs offer range queries too but that is not their biggest strength.

The limitation with the basic/standard RDBMS systems are that they are monolithic. You need a powerful machine to store your 15/40/60 billion objects on a single machine. You will also end up paying cost in terms of performance for the full ACID capabilities of the regular RDBMS systems even though you know that you dont need them.

NoSQL dbs which are inherently designed keeping distributed systems in mind can help greatly here. When you want to grow from 15->40->60 billion objects, probably you just have throw more machines into the cluster. When the data gets sharded, the amount of data stored on each machine is less (making the index sizes etc smaller). This can give you better read performance.


Because your WHERE clasue referencing timestamp has the column wrapped in a function, it is not going to be able to use an index efficiently to may currently be scanning the whole table.

An index on region may help, but not a lot unless that column is quite selective (or at all if the majority of the rows are from the region you are looking at).

As you are applying the same functions to the column and the value you are searching through it with, you can just drop that: the inequality

UNIX_TIMESTAMP(CONVERT_TZ(timestamp, '+00:00', 'SYSTEM')) > UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(NOW(), INTERVAL 1000 DAY), '+00:00', 'SYSTEM'))

is exactly equivelant to just

timestamp > DATE_SUB(NOW(), INTERVAL 1000 DAY)

and that will allow in index on timestamp to be useful.

With that change in place, a combined index on both timestamp and region is likely to be most helpful here. Perhaps also adding price to the end then the query running will not need separate lookups to find that column after filtering by the others (though this will increase the size of the index so you are trading space for perhaps only a small amount of speed).


You need the next indexes:

gallery:       info_id
gallery_likes: (info_id, user_id, photo_id)
gallery_info:  (id, insert_date)


Quick and easy ways to check SQL/DB performance

The Brent Ozar Unlimited First Responder Kit is a good and free place to start. It will walk you through looking for slow queries and comes with a handy worksheet of what to do.


Granting Permissions for a limited period of time

A very simple approach would be to open up an SSMS query window that's connected to the instance you want and use either WAITFOR DELAY or WAITFOR TIME

WAITFOR DELAY '00:01:00' --waits for 1 minute
PRINT 'FINISHED' --revoke permission code
--(optional TSQL code to send an email when complete

or ------

WAITFOR TIME '08:00'  --waits until 8am
PRINT 'FINISHED' --revoke permission code
--(optional TSQL code to send an email when complete

You could even include TSQL code to send you an email indicating the process had completed.

Keep in mind that if your SSMS session dies for any reason, the revoke code will not be executed.

A more robust (and more complex) solution would be to use Service Broker BEGIN CONVERSATION TIMER


Load data from flat file into oracle table

According to your question you have data in the flat file and you want to access it or can load to another table. Basically you are talking about external tables. You want to read more on it. Here is the Oracle Documentation link: External Tables Concepts.

Here is the example how I loaded your data into my external table and created another table from it.

[oracle@testsrv1 Desktop]$ cat user.tbl
 1|john|email@email.com|active
[oracle@testsrv1 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 3 06:45:26 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user user1 identified by user1;

User created.

SQL> create directory user_dir as '/home/oracle/Desktop';

Directory created.

SQL> grant resource, connect to user1;

Grant succeeded.

SQL> grant read, write on directory user_dir to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL> create table user_load(user_id number, name varchar2(20), email varchar2(50), status varchar2(10))
organization external
(default directory user_dir
access parameters
(fields terminated by '|')
location('user.tbl')
);

Table created.

SQL> select * from user_load;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active

SQL> create table final as select * from user_load;

Table created

SQL> select * from final;

   USER_ID NAME          EMAIL                  STATUS
---------- ----------   -------------------    ----------
     1 john         email@email.com        active


How do I get column name from a SQL statement?

I prefer this query, for a number of reasons:

SELECT
  [table]  = s.name + N'.' + t.name,
  [column] = c.name
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
WHERE t.name IN (N'ATable', N'BTable', N'CTable', N'DTable');

You can tell which column comes from which table
You can see the schema (schema is important)
You avoid the broken and incomplete INFORMATION_SCHEMA views - this doesn't cause a problem for this particular query, but there will be cases where you can't rely on metadata from INFORMATION_SCHEMA
You should be in the habit of always using the N prefix on string literals when the underlying data type in nchar/nvarchar.
You should also get into the habit of using proper statement terminators

The cons with my solution:

It takes a few seconds longer to type
It won't run on Oracle, MySQL, Postgres, etc.


A SQL Server bit field can be 0, 1 or NULL, so I'm unsure where the -1 part of your question features.

Skipping past that, the most appropriate solution for "sticky threads" in a forum database is to use a separate filtered index. Flag a sticky thread as IsSticky=1 and create a filtered index along the lines of:

CREATE NONCLUSTERED INDEX IX_Posts_StickyThread
    ON dbo.Posts (ForumId, PostId)
WHERE IsSticky = 1;

Filtered indexes were new to SQL2008 so this wouldn't be an option for SQL2005. The SQLCat article Using a Low-Selectivity BIT Column First Can Be the Best Strategy would be applicable reading for an approach to this in 2005 (edit: article has been archived to the eBook SQLCAT's Guide to Relational Engine).

And just to address the title question bit fields in an index are ordered same as per ORDER BY bit_field i.e. NULL, 0, 1 (credit @MartinSmith).


Calculate Num_Index_Pages for sizing design

Attempting to implement the same, I ended up using this to approximate at a decent level of accuracy:
WHILE (@NonLeafLevels > 1)
BEGIN
    DECLARE @TempIndexPages FLOAT;

    -- TempIndexPages may be exceedingly small, so catch any arith overflows and call it 0
    BEGIN TRY
        SET @TempIndexPages = @NumLeafPages / POWER(@IndexRowsPerPage, @NonLeafLevels);
        SET @NumIndexPages = @NumIndexPages + @TempIndexPages;
        SET @NonLeafLevels = @NonLeafLevels - 1;
    END TRY
    BEGIN CATCH
        SET @NonLeafLevels = @NonLeafLevels - 1;
    END CATCH
END;


Understanding non clustered index locking during an update

If you try the following...

CHECKPOINT;

GO

BEGIN TRAN

UPDATE t2
SET    [col2] = [col2] + 1
WHERE  col1 = 6

SELECT Operation,
       AllocUnitName
FROM   sys.fn_dblog(NULL, NULL)
WHERE  AllocUnitName IS NOT NULL

ROLLBACK

You will see

+-----------------+---------------+
|    Operation    | AllocUnitName |
+-----------------+---------------+
| LOP_MODIFY_ROW  | dbo.t2.cl     |
| LOP_DELETE_ROWS | dbo.t2.ncl    |
| LOP_SET_BITS    | dbo.t2.ncl    |
| LOP_INSERT_ROWS | dbo.t2.ncl    |
+-----------------+---------------+

Showing that the UPDATE against the non clustered index is implemented as a delete/insert pair.

(5ebca7ef4e2c) is the hash for the initial record 6,6
(1bfceb831cd9) is the value after update 6,7.
Look also at slot number

select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)
from t2 with (nolock,index = ncl)
where
    %%lockres%% = '(5ebca7ef4e2c)'
    --OR
    --%%lockres%% ='(1bfceb831cd9)'

begin tran
update t2 set [col2]=[col2]+1  where col1=6
commit tran

select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)
from t2 with (nolock,index = ncl)
where
    --%%lockres%% = '(5ebca7ef4e2c)'
    --OR
    %%lockres%% ='(1bfceb831cd9)'

update t2 set [col2]=[col2]-1  where col1=6

col1    col2    HashValue   bPhysicLocation crackedLocation
6       6       (5ebca7ef4e2c)  0xB401000001000200  (1:436:2)

col1    col2    HashValue   bPhysicLocation crackedLocation
6       7      (1bfceb831cd9)   0xB401000001000300  (1:436:3)


The best way would be to enable binary logging on the Slave. Why ?
With binary logging enabled on the Slave, each INSERT, UPDATE, DELETE recorded in the binary logs comes with the server_id where the SQL statement originated.
What if log-slave-updates was enabled ?
If you have log-slave-updates enabled on the Slave, every INSERT, UPDATE, DELETE from the Master comes with the Master's server_id. If any writes were executed on the Slave directly, you will have to separate the commands.
EXAMPLE: suppose the Master's server_id is 10 and Slave's server_id is 20. Now, let's say you have these binary logs on the Slave:
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1073741824 |
| mysql-bin.000002 |  987741325 |
+------------------+------------+

You can separate the Master's commands from the Slave's commands like this:
mysqlbinlog --server-id=10 mysql-bin.* > Master_Commands.sql
mysqlbinlog --server-id=20 mysql-bin.* > Slave_Commands.sql

Then, you examine Slave_Commands.sql to see what commands were run and when.
What if log-slave-updates was disabled ?
This would be better because the binary logs on the Slave should never increase in size on a Slave unless someone or something ran INSERTs, UPDATEs, DELETEs locally.
EXAMPLE: Let's say you ran RESET MASTER; SHOW BINARY LOGS; on the Slave. For MySQL 5.6, you should see this:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+

From this point forward, if the binary logs ever change in size, this quicky tells you that someone or something ran INSERTs, UPDATEs, DELETEs locally. Next, you run
mysqlbinlog mysql-bin.* > Local_Commands.sql

Then, you examine Local_Commands.sql to see what commands were run and when.


Auditing of modification of change to encryption keys

Turns out SQL Server Audit  has this out of the box.

Querying the contents of sys.dm_audit_actions revealed all the audit actions available and the Audit action types that I needed to add to the audit

Once I added DATABASE_OBJECT_CHANGE_GROUP and SERVER_OBJECT_CHANGE_GROUP to my audit and enabled it they appeared in the log.


Breaking a big insert query into smaller ones

DECLARE @rc INT = 1;
WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.target(cols)
    SELECT TOP (5000) cols
    FROM dbo.source AS s
    WHERE NOT EXISTS
    (
      SELECT 1 FROM dbo.target AS t
      WHERE t.key = s.key
    )
    ORDER BY clustering_key;

  SET @rc = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Also see:

Break large delete operations into chunks


SQL Job Agent Service Account - Access is denied to run Powershell

You have to call powershell.exe instead of the foldername

Change this

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\test\testscript.ps1"

To this

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "C:\test\testscript.ps1"

Testing by creating the same jobstep

The original command

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\test\testscript.ps1"

The error

  Executed as user: ServiceAcc. The process could not be created for
  step 1 of job .. (reason: Access is denied).  The step failed.

Changing the command and running the job again

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "C:\test\testscript.ps1"

Great success


Very fast individual queries but when joined super slow

Sorry it was a such a dumb thing to ask, but again if this is a dumb answer please feel free to delete it. I've used the intersect instead of inner joining the two queries as subqueries.


SQL Server unusual behavior on LOB insertion

If your SQL server trace is not showing anything, it is most likely on the application. Even if it is resolved by restarting the SQL service. I have faced a very similar issue in the past and the connection for the upload process was the culprit. Restarting the SQL service basically gives you another attempt at reconnecting. Since it works some of the time, this second attempt may be all that you need to get it going.

Look into how you build your connection for large files. Limit the time it takes to send the query to the server, and when you get past it, mimic the service restarting by trying it again.


Prevent SQL from apparently executing an expensive sub query

If you add OPTION (RECOMPILE) to the very end of the WHERE clause so it looks like this, does the procedure run faster now?
WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
OPTION (RECOMPILE)

If so, then you likely have a parameter sniffing issue which could be causing bad execution plans to be generated with poor cardinality estimates. If you update your post with your execution plan, then we can pinpoint exactly how that's happening.
If that's the case, this is a good article on Parameter Sniffing and OPTION (RECOMPILE).
Note I usually only use OPTION (RECOMPILE) for debugging to quickly confirm a parameter sniffing issue and try to resolve the underlying problem instead. There are use cases for OPTION (RECOMPILE) however it's a trade-off for minor performance hits since the query gets recompiled every time it's executed.
Brent Ozar has an awesome series of blog posts on Parameter Sniffing issues. Too many to individually link to be honest, so here's the root page that links the main important ones: Parameter Sniffing


Converting Data Type of a full Column/Field

I'm not sure what you mean by 'whole field", but this is how you'd convert an Integer to a varchar string.  The 'second' select proves it's a string because you can concatenate brackets around it.

You'd apply the same technique when selecting rows from a table.

DECLARE @IntColumn int = 5
select convert(varchar(20),@IntColumn)
select '[' + convert(varchar(20),@IntColumn) + ']'


SSIS Package Truncation may occur due to inserting data from data flow column

This warning message displays when any time source column data type length will be more than then length of destination datatype. To resolve the warning message check here -


How to store search keyword or browsing history in database

So, I'm not sure just what you are looking for here.

Since you are using xampp you can manage user sessions in php using their session functions.

Apache also has it's own domain logs which you can keep for however long.

You can also use redis for session management if you are ok with the session information being volatile.

In general I don't see this as a database question, and suggest moving this question to one of the other SE sites such as Stack Overflow or Server Fault.


Error using sp_send_dbmail with a @query parameter

Going an entirely different direction than what I was thinking of in the comments, if you're running this from within a job, this blog post suggests adding the SQL Agent account to the sysadmin fixed server role.

I doubt you have to go that far to fix this, and I suspect you only really need to grant the SQL Agent user (or whatever user owns the job in question) a minimum set of permissions, which are execute permissions on the sp_send_dbmail procedure, ensuring it's a member of the DatabaseMailUser database role in the msdb database, and finally, making sure it can access the data being returned by the query you're passing to the sp_send_dbmail routine itself.


How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

Oh, goodness, I have some bad news here.

On a 32-bit OS, SQL Server only uses the first 4GB of memory for things like query workspace. (And it's fighting the OS for that 4GB, too - any other running apps will also compete for that memory.)

4GB might sound like a lot, but it's relatively easy to write a query that needs several GB of memory in order to run. When enough queries demand enough memory, SQL Server throws RESOURCE_SEMAPHORE waits because queries can't get enough memory in order to start. RESOURCE_SEMAPHORE_QUERY_COMPILE means they can't even get enough memory to compile an execution plan - and yeah, that's pretty bad.

So how do you fix it?

Switch to a 64-bit OS (the OS you're running is long out of support anyway)
Switch to a 64-bit build of SQL Server
Reduce memory demands on the server (don't run any other apps on this box, and that's especially critical on 32-bit boxes since we're capped at just 4GB)
Use more memory with the AWE/PAE switches - except that doesn't work for RESOURCE_SEMAPHORE waits because SQL Server can only use that first 4GB for query workspace
Tune queries & indexes so they need less memory

I hesitate to even say that last one, because the 32-bit issue is so bad, and it's really hard on the older versions of SQL Server. If you were on a current one, you could go through the plan cache and sort queries by memory grant, find the biggest grant recipients, and tune those. Not an option on this old antique, though.


Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or never - if the machine loses power for example). After a page has been written is is simply marked not dirty.

A transaction commit leads to a corresponding log record. The very fact of hardening that log record is what makes the transaction considered committed (except for delayed durability in 2014+).

The recovery model has little to do with this. It controls whether the log is eligible for truncation after a checkpoint has completed.


This refers to the role public.
every user gets this role, and so the rights that the role public has, are valid for all users, so there is no singular username shown in the result only the text `{All Users}´


Oracle SYS views missing from DB instance

No, the database will not drop its own views.

But I have seen this many times, for example when developers thought naming their own tables as 'T$...' and views as 'V$...' was a good idea, then they wanted to clean up their schema, but they messed up all the V$ synonyms and views.

I would just start the database in upgrade mode and run the below scripts to rebuild these objects:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

Run Scripts to Build Data Dictionary Views


Azure SQL Data Migration Assistant is Stuck on "Migrate Data"

A Microsoft Engineer examined the logs you shared with us and found the following error:

  Dma.exe Information: 0 : 'Exception type: System.IO.IOException
      Message:
          There is not enough space on the disk.

It seems like the workstation that DMA is running on doesn’t have enough disk space to do schema extract.

The following is the explanation the Microsoft engineer shared with us about why DMA requires disk space:

  The Extract service connects to a database, reads all of its objects
  and their properties, and then creates an in-memory model of the
  database. Similar to how the Build service validates the defined
  objects, the Extract service also checks for consistency and
  containment. The validation done here will also result in failures if
  you have a view that refers to a non-existent table same as with
  T-SQL! Additionally, unsupported or uncontained objects are blocked
  because these objects are not yet allowed in SQL Azure or the DAC
  in-memory model. Finally, once the in-memory model is complete and
  validated, a DACPAC is written to disk. Looking at the stack it’s a
  well-known Windows exception on not enough disk space.


Yes, that is a good way to use FULLTEXT, especially if you need to 'cleanse' the data in any way, or gather text from related tables.

There is probably not much difference between having the new column in the same table or in a separate table.

The redundancy is wasteful.  However, there is no way to have any kind (FULLTEXT or other) of index that spans multiple tables.  Might it be better to have all the columns in the same table, and do FULLTEXT(this, that, the_other)?

And the redundancy is potentially error-prone.  This could be tempered by having a Stored Procedure that users must call when inserting or manipulating any of the text involved in the issue.  This SP would always make the copy for you.

When using a FT index with other things, such as

WHERE price > 50
  AND MATCH(stuff) AGAINST(...)

the FT test will be done first.  Hopefully, that will select only a small number of rows.  Then the other tests will be applied to further filter the results.  When the situation is reversed (price is more selective than MATCH), well, too bad.

If you always have the MATCH, there is no need for indexing price, the index won't be used.


How to export some rows of multiple tables linked between them according to one column value?

you can, if you use a correlated subquery. Check the manual.

QUERY=son:"where exists (
    select *
    from mother
    where
        mother.id_mother = ku$.id_mother and
        mother.last_updated > trunc(sysdate,'month')"

ku$represents the table you are actually exporting, so here it is the table son.

Starting with Oracle 12c you have the VIEWS_AS_TABLES parameter. Then you can export  the data of a view and store it in a table, e.g. with sqlplus do

create view new_son
as
select son.*
from son, mother
where
    mother.id_mother = son-id_mother and
    mother.last_updated > trunc(sysdate,'month')"

Now export this using

VIEWS_AS_TABLES='new_son'

If you import this it will create a table new_son.


MySQL - Where can I find metrics on the performance of Blob vs file system?

The main cost of handling the data is the I/O.  You are doing approximately the same amount of I/O, whether it is in 4KB chunks in the OS (plus directory traversal) or 16KB chunks in InnoDB (plus indirect block lookup).

The filesystem and InnoDB are cached in radically different ways; this may factor into a difference -- depending on how cacheable the blogs are.

You say "rarely retrieved". So why does speed matter?

So, I doubt if it will be more than 25% difference.  And I can't predict which will be faster.

As for space, again there are several differences, so it is hard to predict which would be tighter.  In any case, the diff can't be more than about 2% for the size blobs you mentioned.

How compressible are the blobs?  (Most image formats are already compressed; text is typically compressible 3:1.)  If compressible, then do so in the client.  (InnoDB's builtin optional compression is easier, but not as good.)

And, yes, having it in a "parallel table" (as you mentioned) is often better.

Another point -- If the blob is an image destined for a web page, it is more efficient to simply have it in a file and say <img srg=file-path>.  If it is in a table BLOB, you have to do extra work to hand it off to the web page.  Since I/O is the main difference, I might expect the img tag to be 2x faster.


Whoops my bad!  I was sure I had set the sort key on the advanced editor on ole db source but I hadn't!


What permissions are required to return rows from information_schema.schemata?

The user needs to be the owner of a schema to get any rows from information_schema.schemata

alter schema $schemaName owner to $user;

Or the user needs to be a member of a group that owns the schema

Usage grant is not sufficient. The user does not need to be a superuser. The user can own a table in a schema, but that is not sufficient either.


Try this:

SELECT z.id, z.name, z.step, jsonb_array_elements(z.magnitude)
FROM (
SELECT aa.id, aa.name, ao.key AS step, ao.value AS magnitude
FROM todos aa
CROSS JOIN LATERAL (SELECT * FROM jsonb_each(tags)) AS ao) z
ORDER  BY z.id;


Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?

First, let's assert that manipulating a single row  is positively guaranteed by a UNIQUE or PRIMARY KEY constraint on my_table.identifier. Else it should be a table function to begin with.

Next: Should you write a function for such a simple DELETE at all? Or use the plain DELETE instead? Barely longer and less overhead. (Maybe your actual function is more sophisticated ...)

Then, while your function is that simple, a plain SQL function might serve:

CREATE FUNCTION my_table_delete(_identifier int)
  RETURNS TABLE(identifier int)
$func$
   DELETE FROM my_table t
   WHERE  t.identifier = _identifier
   RETURNING t.identifier;
$func$  LANGUAGE sql VOLATILE STRICT;

Also using RETURNS TABLE like you pondered: Note the table-qualified column name to avoid conflicts. All function parameters (including columns of a RETURNS TABLE clause) are visible in SQL commands in the function body (in PL/pgSQL as well as in SQL functions). You need to avoid conflicts between column names and parameters.

An SQL function has less overhead. But if you call the function many times in the same session, the PL/pgSQL variant may actually be faster since it manages SQL statements like prepared statements, meaning the query plan is saved and reused if some preconditions are met. Then again: much of the same effect is achieved with prepared statements. See:

Function Performance
Difference between language sql and language plpgsql in PostgreSQL functions
Select column values linked to exact set of values in another column

As to your reasoning:

1. If you don't want to deal with NULL, then the case is settled. Make it a table function. But you may have to deal with NULL after all: Various query constructs (have to) convert "no row" to NULL.

2. sounds like an argument, but is just a logical fallacy. Does not carry weight.

3. If it's your personal preference, then the case is settled again. Can't argue with that. But a data-modifying CTE would work with the scalar function just as well:

WITH d(id) AS (
   SELECT my_table_delete(1234)
   )
INSERT INTO log_table(id, action)
SELECT  d.id, 'DELETE'
FROM    d
WHERE   d.id IS NOT NULL;

In summary: go with RETURNS TABLE, but it hardly matters for the given case.


How do I write a query that inserts values while AND gets values from another table?

You need to look at the DEFAULT clause. I think you want something like the following as an example:

CREATE TABLE tbl2
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  org TEXT NOT NULL DEFAULT SUBSTRING(MD5(RANDOM()::TEXT) FROM 1 FOR 4),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  update_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT tbl2_pk PRIMARY KEY (id)
);

Note that I am using

  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT))

as the PRIMARY KEY. This is close enough to a UUID for the purposes of this answer - I'm using dbfiddle (see this link) and can't install the UUID extension. I'm also using a SUBSTRING of the same function to generate the org field values, since I don't know exactly what that is - I would have thought that a reference table to things like the UN, EU, NATO, ASEAN... &c. would be more appropriate for this, but then, only you know that - see @Philᵀᴹ 's com

Then populate it with some values:

INSERT INTO tbl2 (name, address, gender)
VALUES
('Name_23', 'Address_23', 'M'),
('Name_24', 'Address_24', 'F'),
('Name_25', 'Address_25', 'M');

Notice how I don't have to specify the id or the org fields since there's a DEFAULT in place. Now, we do the same for tbl1.

CREATE TABLE tbl1
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  CONSTRAINT tbl1_pk PRIMARY KEY (id)
);

and:

INSERT INTO tbl1 (name, address, gender)
VALUES
('Name1', 'Address_1', 'M'),
('Name2', 'Address_2', 'F'),
('Name3', 'Address_3', 'M');

And then the INSERT is performed:

INSERT INTO tbl2 (name, address, gender)
SELECT name, address, gender
FROM tbl1
-- WHERE tbl.id NOT IN (SELECT id FROM tbl1) -- MD5 should ensure uniqueness
RETURNING name, address, gender;  -- this is optional - check it out.

And then we check our tbl2:

SELECT * FROM tbl2;

Result:

id  org name    address gender  created_at  update_at
1feaceed5c2d8e37f5565d222b5bd31c    28a1    Name_23 Address_23  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
508c0eecc16e305687b355c684cd5d56    d057    Name_24 Address_24  F   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
247d6d7bdcef078c4b4f7bef33e91d01    78a5    Name_25 Address_25  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
07bcc6cf6a3a3b79fcc05bafe9ebc8c2    05f8    Name1   Address_1   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
029ef1b1c21df2a0fe25da352114aa1f    48c2    Name2   Address_2   F   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
f4f51eccf9d4bfa7e011bdc4b2c135b8    8723    Name3   Address_3   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00

A simple inspection shows that our INSERT has worked. Finally, notice that our created_at and updated_at fields have also been included - as the default is NOW(). In order for the updated_at field to be modified automatically, you'll need to use a TRIGGER - if you do a search, there are millions on the all-singing, all-dancing web. If this doesn't answer your question, let me know and I'll see what I can do. p.s. welcome to the forum!


Jobs on the Mirror are running against the Primary

It seems there are many who has faced this issue in the past and they all have implemented one or the other work-around to get rid of it. Not documented at Microsoft site or presented by any known SME(Subject Matter Expert).
First of all, I would say database mirroring is deprecated and will not be supported by Microsoft in near future, so you should plan to change it.

This feature will be removed in a future version of Microsoft SQL
Server. Avoid using this feature in new development work, and plan to
modify applications that currently use this feature. Use Always On
availability groups instead.

Having said above, I think there are few interesting work arounds which could be used to avoid this issue:

To disable SQL Server Agent jobs for the database which are part of
database mirroring. This can be done using code given at end:
To use database name as a first step for jobs running on secondary database, since database will be restoring or stand-by state. Job will fail.
Stop SQL Server Agent if all the jobs are related to databases part of database mirroring.
Get the value of mirroring role from msdb.sys.database_mirroring and include this in your job step. Read more here how to do it.

To disable job steps, you may use below script if there are many jobs:
DECLARE @sql nvarchar(max)
    ;WITH cte AS (
    SELECT  j.job_id,
            j.[name]
    FROM msdb.dbo.sysjobs j
    WHERE j.[name] like 'Job_Name%'
    )
    SELECT @sql = (
        SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''+CAST(job_id as nvarchar(max))+''', @enabled = 0;'+CHAR(10)
        FROM cte
        FOR XML PATH('')
    )
    EXEC sp_executesql @sql

Hope this helps.


There is a lot of Client Locks and IPC Locks in PostgreSQL

Those are not locks, those are wait event types. If you are searching for them specifically as lock types, that might prevent you from finding much.  It would be nice to know what the actual wait events are, rather than just the general category.  If POWA doesn't provide a way to drill down on that, you can inspect pg_stat_activity directly for the wait_event column.
These probably do not indicate a problem.  But if you already know you have a problem, they could tell you where to look for it.  Do you have a problem?  E.g. are your users complaining about slowness?


How to run SQL Job only on primary replica in AlwaysOn Availability Groups?

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.

Just like Aaron pointed out in the comments, a job still has to be created on all servers with the same logic to ensure that it only runs on primary replica and not on the secondaries. There are scripts and tools that can be used to copy jobs from primary replica to the secondary replicas.


You have SELinux enabled, and your policy doesn't allow Apache to write to those files.

Adjust the policy, relabel the files, or (not recommended) disable SELinux.  The sealert can help provide guidance.


Why SQL Server 2008 R2 do full installation for just adding another instance on the same machine

Actually Sql server has two types of component\features.
1.  Instance specific features
2.  Shared Features
And Database Engine, Analysis Services, and Reporting Services are part of instance specific features which has to copy many files for installing new instance.

http://msdn.microsoft.com/en-us/library/ms143786.aspx


mysql replication - missing table for foreign key on slave

Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld] group header in the Slave's my.cnf as follows:

[mysqld]
slave-skip-errors=1452

You will have to restart mysql for this to take effect.

Give it a Try !!!

CAVEAT : Your data integrity will deteriorate when added new data because many INSERT queries or multiple-row INSERTs will fail because of just one row not having parent keys.

You should really consider loading all referenced tables into the slave to have good, clean data. Otherwise, it's not a true copy of the Master.


Try without the first BEGIN TRAN and last COMMIT TRAN.

If you are using a stored procedure, the procedure itself already creates a transaction.

Your transaction locks because of the following script:

//This select does a table scan, so I believe does X lock on all rows
Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

From what I see in the script, the with(xlock, rowlock) is not needed.

Try with the following script:

SET XACT_ABORT ON
SET ISOLATION LEVEL REPEATABLE READ

-- This select does a table scan, so I believe does X lock on all rows
SELECT a, b, c
  FROM queuetable
 WHERE a = x

DELETE queuetable
 WHERE a = X

-- Get oldest row - this is the deallocking query
SELECT TOP 1 a, b, c
  FROM queuetable
 ORDER BY b

-- Mark row to be processed
UPDATE queuetable
   SET c = processing
 WHERE a = y


Correlated subquery to find total larger than state average

You can do it like this:

select *
from
    dbo.Invoice I1
    join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where
    I1.InvoiceTotal > (
        SELECT
            Avg(I2.InvoiceTotal)
        from
            dbo.Invoices I2
            join dbo.Vendors V2 on V2.VendorID = I2.VendorID
        where
            V1.VendorState = V2.VendorState
    );

Or using window functions, which may be faster since it doesn't need an extra join:

SELECT X.*
from (
    select
        *,
        Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
    from
        dbo.Invoices I
        join dbo.Vendors V on V.VendorID = I.VendorID
) X
where
    X.InvoiceTotal > X.AvgInv;

The window function option might not be faster. Though it saves a join in the query, the execution plan will feature a subexpression spool (with two extra joins). The table spool is needed to compute and save the average for the current partition. The spooled result is replayed once per partition.


How many LEFT JOINS make a VIEW unusable?

The link provided by Martin Smith to an existing SO question provided me with the answer:

  The main restrictions are that foreign key relationships must be based
  on a single key to contribute to the simplification process


Your table is big, and so is any index spanning the whole table. Assuming that:

only new data (with timestamp = now()) is entered
existing rows are neither changed nor deleted.
you have data since 2012-01-01 but queries are predominantly on the current year (?)

I would suggest a partial, multi-column (covering!) index:

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

Only include the time range that is queried regularly. Effectiveness deteriorates over time with new entries. Recreate the index from time to time. (You may need to adapt your queries.) See linked answer below.

The last column value is only included to get index-only scans out of this. Aggressive autovacuum setting may help by keeping the visibility map up to date, like @jjanes already mentioned.

The partial index should fit into RAM more easily and stay there longer.

You may need to include this WHERE condition in queries to make the planner understand the index is applicable to the query, like:

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411
AND   "timestamp" > '2014-06-11 0:0'
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

Since your query is summing a lot of rows (rows=13578), this is going to take some time, even with an index-only scan. It shouldn't be anywhere near 50 seconds, though. Less than a second on any halfway decent hardware.

Related (but ignore CLUSTER and FILLFACTOR, both are irrelevant if you can get index-only scans out of this):

Index optimization with dates

Aside:
Since you currently have an index on (prop_id, "timestamp"), the additional index on just (prop_id) may cost more than it's worth:

Is a composite index also good for queries on the first field?


No, from uniqueness you cannot deduce full functional dependency.

I you have a relation (=table) with a candidate key(~primary key) P and additional attributes A,B,... than all attributes depend from P because P is the candidate key, but all atrributes depend from the combined superkey PA too (this means the key containing both P and A), but this is not a full functional dependency because they depend on a subset of attributes of PA, on the attribute P. PA is unique and not null but PA is no candidate key.


You could have used the default location by mentioning it an UNC path. Snapshot Agent job will need write and Distribution Agent job will need read privilege. Snapshot file location (directory) is a property of Publication, keep that in mind when you mention alternate location.

When configuring a Distributor through the Configure Distribution Wizard or the New Publication Wizard, the snapshot folder defaults to a local path: X:\Program Files\Microsoft SQL Server\\MSSQL\ReplData. If you are using a remote Distributor or pull subscriptions, you must specify a UNC network share (such as \\snapshot) rather than a local path.

Reference:

Specify an Alternate Snapshot Folder Location (SQL Server Management
Studio)
Alternate Snapshot Folder Locations

  The Snapshot Agent must have write permissions for the directory you
  specify, and the Distribution Agent or Merge Agent must have read
  permissions. If pull subscriptions are used, you must specify a shared
  directory as a universal naming convention (UNC) path, such as
  \computername\snapshot.

Alternate snapshot locations enable you to store snapshot files in a location other than, or in addition to, the default location, which is typically located on the Distributor. Alternate locations can be on another server, on a network drive, or on removable media such as CD-ROMs or removable disks.

More about securing snapshot folder.

Secure the Snapshot Folder


Turns out to be a permissions error. The permissions looked right from a database server point of view (Security -> Logins). These permissions as far as I can tell were moved over as part of the replication.

But when I looked at the individual databases the security under them was missing (database -> Security -> Users).

Under the database security the only users were dbo, guest, INFORMATION_SCHEMA, and sys.


sp_BlitzIndex - "Cannot Insert the value null into column 'index_sanity_id'..."

That typically happens when indexes are being added or dropped at the same time sp_BlitzIndex runs. If you have code that is dynamically adding/dropping tables in a user database, for example, that'll trigger this. The Github issue for that is tracked here.

To test that theory, try running sp_BlitzIndex again at a time with lower end user activity, or restore the same database to another server with no activity. (Granted, that's not going to get you index utilization data, but it'll prove out the theory.)

If you still get the error even with no activity on the server (again, restored to a different server so you can make sure), then we would need to step through the code to understand what kinds of indexes on your database are triggering the error. You can see it by modifying this part of the T-SQL:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY PRIMARY KEY,

Change that to just:

    CREATE TABLE #IndexSanity
        (
          [index_sanity_id] INT IDENTITY NULL,

And then at the end of the proc:

    SELECT * FROM #IndexSanity WHERE index_sanity_id IS NULL

That way you can see which indexes are triggering the error, and we can figure out how to exclude them from being checked.

I created an issue in Github to track this and fix it in the next build:

sp_BlitzIndex - null error when populating #IndexSanitySize


concatenating columns from multiple unrelated 1-row resultsets

Two subqueries?

SELECT Q1.A, Q1.B, Q2.C, Q2.D
FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B)
CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);


whether to merge partitioned table with only partially overlapping columns

Having all your data in fewer, common tables is obviously going to make your coding simpler.  I would suggest you pursue this option.

Partitioning is not that scary.  Annualised data like yours is the archetypal usage for partitioning.  It will help with your query performance and may help with your data management tasks (load / delete), depending on MonetDB's capabilities.

You say some data is in different formats in different years - tinyint v/s character.  This will be a problem for you whatever architecture you choose because you will eventually want to combine these in one resultset.  Fix this sooner rather than later.  Fixing in the data and table definition will be less work overall than fixing it in each query as you write it.

In summary 1) Fairly, especially if you have to reconcile type differences 2) "A bit" if your queries are simple to "a lot" of you get it badly wrong 3) not a MonetDB expert but most RDBMS handle data many times the size of RAM without problem.


There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Based on this statement, let's play with some numbers. Lets's say there are 500 writes a day and 20,000 reads a day. That computes out to the following

97.56% reads per day
2.44% writes per day
40 reads / write

As much as I love InnoDB, I would have to choose MyISAM in this case for several reasons

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run     of the the following to physically shrink the InnoDB table mydb.mytable:

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

  The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)


SQL Server 2016: Granting security to do everything but read / write data

It is not possible. Everything includes granting access, which means they can change whatever access you give them.

There are some encryption methods that can be used so DBA's can have full access but not have readable access to the data. I believe Transparent Data Encryption (TDE) is one option, but I am not an expert on it.

Make a list of "everything" you want the DBA's to do, then look at the rights required to do that. IMHO, a DBA who does not have sysadmin, is not a DBA.


I think best approach is try to find connections, which use most IO.

Look at open connections, see who use most IO.
Look at currently executing commands, what they wait. Would be seen some waits on latches.
After finding who use most IO, it would be easier to make next step in investigation.

As for CLR, there are performance counters which shows quite a lot information about its behavior, so it is possible to look on it.
May be there is usage of swap on the server? Like page/sec significanly above zero? It also may cause such behavior of server.


How to join two columns and find unique combinations

Try something like

    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_from = t2.id_to
UNION
    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_from = t2.id_from
      AND t1.id > t2.id
UNION
    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_to = t2.id_to
      AND t1.id > t2.id


Select all columns from table but apply function to 1 column

Select all columns ... such that I don't have to type out all of the column names

But that is exactly what you should be doing!

Never use select * in production code.

Why?  Two reasons.

The database has to work harder, first to work out which columns exist, construct the query and then actually retrieve the data for you.  OK, the amount of extra work is small but involves core, data dictionary tables which can result in performance bottlenecks.
Databases are inherently shared environments.  Today, your query might be perfectly happy, pulling back all three of the columns in a given table.  Life is Good.  Tomorrow, someone else might add a hundred BLOB fields into that table and suddenly your query's performance goes through the floor, pulling back all that data in which your application has absolutely no interest.

Always specify the columns that your query requires explicitly.

Also, are those really your column names?  If so, then your table design worries me greatly.

Database != Spreadsheet.

Basic Data Normalisation rules should extract those repeated attributes out into a another [sub-]table, rather than running them "along" a row.