As mention your error like

  MongoError: failed to connect to server [private-network-ip:27017]

This error is what you would see if the mongo shell was not able to talk to the mongod server.

As per MongoDB documentation Connection String URI Format The URI format for defining connections between applications and MongoDB instances in the official MongoDB drivers.

The following is the standard URI connection scheme:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

For further your ref here


How can I configure MySQL Innodb to handle 1000s of inserts per hour?

You are dealing with a deadlock, not a performance bottleneck issue.

If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.

Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in some specific situations.

InnoDB can show you detailed deadlock information by running SHOW ENGINE INNODB STATUS at the MySQL prompt, or with mysql -uroot -p... -e "SHOW ENGINE INNODB STATUS".

However, this only shows the last deadlock that occurred, there is no deadlock log.

Thankfully, there's a tool pt-deadlock-logger which takes care of that problem, it takes care of polling InnoDB status and saves all the detailed deadlock information before it's refreshed with a new deadlock.


Database Administrator vs Database Architect?

I would say the term Database Administrator covers the full gamut of database duties: design, development, production support, performance tuning, report writing, OLAP, etc.

A Database Architect is involved in the design and development of large or complicated database solutions. However, the Architect is usually not involved in the day-to-day operations of the system once it is deployed.

I don't really like either term.  Database Administrator is too vague, Database Architect sounds pretentious.

I am a computer programmer at heart, but aren't we all?


What mongodb database layout would fit better

I think your first idea is better.  The second way, as you state, is how you would model the data in an RDBMS.  If you're going to use MongoDB for fun, you might as well explore the fact that it has a different data model, and structure your collections accordingly.  While I'm sure performance is not going to be an issue for the scale of this project, keeping the data in a single document avoids joins, which can be expensive.  It's commonly held that, within MongoDB environments, denormalization is faster (see here).

In the schema describe in your first idea, playlists could all be subdocuments in a kind of catch-all artist document called "various".  I imagine the same would be done in a relational implementation, with the table artist having a record for "various", to cover compilation albums and soundtracks, for example.

I have a JSFiddle here with a JSON representation of what a music collection might look like implemented as your first idea.

Of course, there is yet another way, and that is a collection albums, with most documents having a key artist, which could either be a reference to the _id from a document in a collection artists, or just have the name of the artist itself.  In this implementation, playlists wouldn't have this key; or, alternatively, it could be an array with all the artists with tracks in the playlist.


Is it possible to recover day's worth of queries from sql server log file?

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point.

Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), or switch to FULL and take log backups as well to get point-in-time recovery ability. Corrupt backups notwithstanding.


Difference between Hash, Merge and Loop join?

From MSDN, in the topic of Advanced Query Tuning Concepts:

  SQL Server employs three types of join operations:

  Nested loops joins
  Merge joins
  Hash joins

  If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins.

  If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other. For more information, see Understanding Merge Joins.

  Hash joins can efficiently process large, unsorted, non indexed inputs.

But I believe that you should start with a more basic topic: Query Tuning and lastly go to using the query hints.


Index for speeding up sorting by boolean, timestamp

Try creating the following index, running ANALYZE cms_news, and posting the EXPLAIN ANALYZE plan again

CREATE INDEX cms_news_flash_ts_idx ON cms_news ( flash desc, timestamp desc );

Tip: You can post your EXPLAIN ANALYZE plans to http://explain.depesz.com/ and post the link back here. I've found it to be a pretty helpful tool over the years.


What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

SPEEDING UP SHUTDOWN / STARTUP
Since you are dealing with the InnoDB Storage Engine, you need to think about what MySQL does during a startup and during a shutdown.
During a shutdown, InnoDB tends to freeze everything in terms of transactions Dirty pages in the InnoDB Buffer Pool are flushed. During a startup, InnoDB crash recovery rolls forward any leftover changes in the transaction logs. The InnoDB Double Write Buffer may also be scanned during InnoDB crash recovery.
The first variable I think you should set innodb_fast_shutdown to 0.
Please note the MySQL Documentation:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow
shutdown, a full purge and an insert buffer merge before shutting
down. If the value is 1 (the default), InnoDB skips these operations
at shutdown, a process known as a fast shutdown. If the value is 2,
InnoDB flushes its logs and shuts down cold, as if MySQL had crashed;
no committed transactions are lost, but the crash recovery operation
makes the next startup take longer.
The slow shutdown can take minutes, or even hours in extreme cases
where substantial amounts of data are still buffered. Use the slow
shutdown technique before upgrading or downgrading between MySQL major
releases, so that all data files are fully prepared in case the
upgrade process updates the file format.
Use innodb_fast_shutdown=2 in emergency or troubleshooting situations,
to get the absolute fastest shutdown if data is at risk of corruption.

OK that takes care of a faster startup. The tradeoff is longer shutdown.
Is there anything you can do speed up a shutdown ? Yes !!!
Get dirty pages from the InnoDB Buffer Pool written to disk more frequently. How ?
You need to set innodb_max_dirty_pages_pct = 0. By doing this, the shutdown process takes less time to flush dirty pages to the .ibd files.
Is there anything else to help startup ? Yes, there is one more.
When you startup mysql, usually the InnoDB Buffer Pool is empty after its allocation.
You could make mysql save the block numbers of all 16KB pages that were in the the buffer pool. Just add innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown. This will not time a long time since it writes block numbers to a binary files, not the blocks themselves.
LARGER NUMBER OF FILES
InnoDB's default for open files (innodb_open_files) is 300 for MySQL 5.5. For MySQL 5.6, the ideal number is set for you. You may need to increase this value as well. InnoDB may cache the open files once you reach this limit.
You may have to compensate by raising the value to 100000 or 200000. You could also increase the ULIMIT in the OS to allow MySQL to have more file handles and set open_files_limit to that max value.
I would further suggest increasing RAM in the DB Server.
SUMMARY
Here are the options you need to add
[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_open_files=(much higher value)
open_files_limit=(much higher value)

Depending on the environment, you may not need all four options. Perhaps setting innodb_fast_shutdown to 2 is better than 0. Maybe the default setting for innodb_max_dirty_pages_pct is fine if you have low-write activity. In any case, test which options help your shutdown and startup concerns.
Keep in mind that innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown are for MySQL 5.6. Therefore, check to see if the version of Percona Server you are using has those two options.
Give it a Try !!!


ORA--07445 error during online Re-org process in 10.2.0.4 database

Bug 6494048 - Dump [ktsk_get_prevl2] from ALTER INDEX .. SHRINK SPACE (Doc ID 6494048.8)

This issue is fixed in

11.2.0.1 (Base Release)
10.2.0.5 (Server Patch Set)

Upgrade from that ancient, unsupported version.


It depends on why you are creating the temporary tables in MySQL.

Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers.  In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data.  Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else.  If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.

Assuming that you really need a temporary copy of the data, you can create global temporary tables.  You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code.  The global temporary table ensures that each session can only see the data that session has inserted.  The only difference is that you're not dropping and recreating the structure of the table inside your procedure.

Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table.  PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously.  You can do something like

DECLARE
  TYPE emp_tbl IS TABLE OF emp%rowtype;
  l_emps emp_tbl;

  CURSOR emp_cur
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Fetch 10 rows at a time from the cursor into the collection.
    -- You'd realistically want a larger limit, something between 100 and 1000 generally
    FETCH emp_cur
     BULK COLLECT INTO l_emps
    LIMIT 10;

    EXIT WHEN l_emps.COUNT = 0;

    -- An example of manipulating the collection in memory
    FOR i IN 1 .. l_emps.COUNT
    LOOP
      l_emps(i).sal := l_emps(i).sal * 2;
    END LOOP;

    -- And an example of using the collection to update a table
    FORALL i IN 1 .. l_emps.COUNT
      UPDATE emp
         SET sal = l_emps(i).sal
       WHERE empno = l_emps(i).empno;
  END LOOP;
END;

Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.


What data type should I use: ENUM, TINYINT, or CHAR?

ENUMs scare me because of any issue regarding adding/removing type values. You are probably better off representing both ProfessionalType and Decile as TINYINT or CHAR(3). Anything but ENUM.

See my past posts on ENUM :

Ocy 19, 2011 : Advantages and Disadvantages to using ENUM vs Integer types?
Jan 24, 2012 : Is it possible to change ENUM() lists?

For efficiency, please do not index this field alone, given such a low cardinality (8,10).


TEMPDB full causing login problems with server login trigger

I would like to point out an another way to do this instead of the trigger. As @Nic pointed out in the comment, your solution does not prevent users from connecting through SSMS (as you can change the app name).

You should create SQL-logins for the application or use service accounts if you are using Active Directory and create windows logins for them. This is only solution that does not allow your denied logins to connect at all and it does not matter if tempdb is full.


Identify co-related record between two columns with in the group

Your update statement, is not covering all possibilities.
See record id 6 and 22 ; these records are not marked.

To have an efficient query , you need also some indexes on your table. One that starts with column GroupID ...

-- preparation part

create table dbo.tblSource
(UniqueID int not null
,GroupID int not null
,Item1 char(1) not null
,Item2 char(1) null
,Result char(1) null
,Flag char(1) null
,FlagRes char(1) null)

insert into dbo.tblSource(UniqueID,GroupID,Item1,Item2,Result)
 select 1 , 1, 'x',NULL, '' UNION ALL
 select 2 , 1, 'b','c', 'Y' UNION ALL
 select 3 , 1, 'c','d', 'Y' UNION ALL
 select 4 , 1, 'd',NULL, 'Y' UNION ALL
 select 5 , 1, 'e','d', 'Y' UNION ALL
 select 6 , 1, 'e',NULL, 'Y' UNION ALL
 select 7 , 1, 'e','c', 'Y' UNION ALL
 select 8 , 2, 'a',NULL, '' UNION ALL
 select 9 , 2, 'b',NULL, '' UNION ALL
 select 10 , 3, 'x',NULL, '' UNION ALL
 select 11 , 3, 'y',NULL, '' UNION ALL
 select 12 , 3, 'a','b', 'Y' UNION ALL
 select 13 , 3, 'b',NULL, 'Y' UNION ALL
 select 14 , 4, 'x',NULL, '' UNION ALL
 select 15 , 4, 'y',NULL, '' UNION ALL
 select 16 , 4, 'x',NULL, '' UNION ALL
 select 17 , 4, 'c',NULL, 'Y' UNION ALL
 select 18 , 4, 'd',NULL, 'Y' UNION ALL
 select 19 , 4, 'd','c', 'Y' UNION ALL
 select 20 , 5, 'a','c', 'Y' UNION ALL
 select 21 , 5, 'b','c', 'Y' UNION ALL
 select 22 , 5, 'b',NULL, 'Y' UNION ALL
 select 23 , 5, 'c',NULL, 'Y'

your's updates

Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] where (a.Item1 = b.Item2 or b.item1 = a.Item2)

Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] and (a.Item1 = b.Flag or a.item2 = b.Flag)
where a.Flag is null

this is what I come with:

update s1
set flagRes = case when exists(select 1 from dbo.tblSource as s2
                           where s2.GroupID  = s1.GroupID
                             and s2.UniqueID <> s1.UniqueID
                             and
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null)
                             or
                             (s1.item2 is null and s1.item1 = s2.item2)
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2))
                             )

                 ) then 'y'else '' end
from tblSource as s1

select of it, with the output:

select UniqueID,GroupID,Item1,Item2,Result as yourResult
,case when exists(select 1 from dbo.tblSource as s2
                           where s2.GroupID  = s1.GroupID
                             and s2.UniqueID <> s1.UniqueID
                             and
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null)
                             or
                             (s1.item2 is null and s1.item1 = s2.item2)
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2))
                             )

                 ) then 'y'else '' end as queryRes
        ,flag
        ,flagRes
from dbo.tblSource as S1

UniqueID    GroupID Item1   Item2   yourResult  queryRes    flag    flagRes
1           1       x       null                            null    null
2           1       b       c       Y           y           c       y
3           1       c       d       Y           y           b       y
4           1       d       null    Y           y           c       y
5           1       e       d       Y           y           d       y
6           1       e       null    Y           y           null    y
7           1       e       c       Y           y           c       y
8           2       a       null                            null    null
9           2       b       null                            null    null
10          3       x       null                            null    null
11          3       y       null                            null    null
12          3       a       b       Y           y           b       y
13          3       b       null    Y           y           a       y
14          4       x       null                            null    null
15          4       y       null                            null    null
16          4       x       null                            null    null
17          4       c       null    Y           y           d       y
18          4       d       null    Y           y           c       y
19          4       d       c       Y           y           c       y
20          5       a       c       Y           y           c       y
21          5       b       c       Y           y           c       y
22          5       b       null    Y           y           null    y
23          5       c       null    Y           y           a       y

dbfiddle here


GUID vs Auto Increment Insert performance

What you are missing is the setting of innodb_buffer_pool_size (assuming you are using InnoDB), and how it compares to the size of the index on id.  The "more data" needed:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW TABLE STATUS LIKE 'guid';

When id is AUTO_INCREMENT or some kind of "increasing" timestamp, all the work will be done at the "last" block of the index.  Hence, very little needs to be cached to avoid I/O.
When id is a uuid/guid/md5/etc, the cache space used (buffer_pool) grows as the table grows.  This is because the id is jumping around "randomly".  You won't notice much performance hit until the buffer_pool is no longer big enough.  Then things gradually hit the fan.

Let's carry things even farther...  Let's say the buffer_pool that can hold 1M entires, but there are 20M entries.  When the next row is to be inserted, it has a 1M/20M chance that the desired block is currently cached in the buffer_pool.  That is, only 5%.  Or, a 95% chance of a "miss" -- almost always requiring a disk hit.  I/O is the major cause of sluggish SQL.

More discussion:  http://mysql.rjweb.org/doc.php/uuid

Bottom line:  If you have a huge table, guids will kill performance, whether it is the PRIMARY KEY or some other index.


Modeling Optional Foreign Key from Composite Primary Key field (Oracle Data Modeler)

You have the foreign key in the wrong table. Your defining entity is in the MKT_DATA_BONDS table. The refering entity is in the BONDS table. Each BOND must be defined in MKT_DATA_BONDS. The "optional" part is carried out by the fact that not all market bonds need be represented as a company bond.

To put it in OO language, market bond is the super or parent class. A company bond is one (of possibly several) sub or derived classes. Subclasses know about super classes, but the super class doesn't know about (or refer to) any subclass.


Is it a bad backup strategy in Oracle to have only Level 0 backups and Archive Logs in between with no incremental?

Do you have enterprise edition or standard edition? If you have enterprise edition you can create a block change file and do a true level 1 incremental backup. Doing a level 0 backup is a FULL backup. There is not reason for you to do a FULL backup every 6 hours. Most people do a FULL backup every day and possibly send the archive logs to more than one server. I do two level 0 backups per week and level 1 backups on the days when I am not doing a level 0 backup. If you are new to Oracle you may want to do a backup/recovery class. You should practice backup and recovery on development databases.


In your first query you do ...orders.created  <= '2015-07-31'; which is actually

orders.created  <= '2015-07-31 00:00:00';

since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like

orders.created  <= '2015-07-31 23:59:59';


If you're trying to track down memory usage and what queries are the biggest offenders, run through the following steps:

Identify what DB on the instance is consuming the most memory

-- DB using most cache
DECLARE @total_buffer INT
SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name IN ('Total Pages', 'Database Pages');

WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

Dig into what objects within said db are consuming the most memory

--objects in DB using most space
USE DatabaseName
GO
WITH src AS
(
    SELECT
        [Object] = o.name,
        [Type] = o.type_desc,
        [Index] = COALESCE(i.name, ''),
        [Index_Type] = i.type_desc,
        p.[object_id],
        p.index_id,
        au.allocation_unit_id
    FROM
        sys.partitions AS p
    INNER JOIN
        sys.allocation_units AS au
        ON p.hobt_id = au.container_id
    INNER JOIN
        sys.objects AS o
        ON p.[object_id] = o.[object_id]
    INNER JOIN
        sys.indexes AS i
        ON o.[object_id] = i.[object_id]
        AND p.index_id = i.index_id
    WHERE
        au.[type] IN (1,2,3)
        AND o.is_ms_shipped = 0
)
SELECT
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type,
    buffer_pages = COUNT_BIG(b.page_id),
    buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
    src
INNER JOIN
    sys.dm_os_buffer_descriptors AS b
    ON src.allocation_unit_id = b.allocation_unit_id
WHERE
    b.database_id = DB_ID()
GROUP BY
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type
ORDER BY
    buffer_pages DESC;

Review what queries are hitting said objects in memory

USE DatabaseName
GO

SELECT DISTINCT TOP 100
        s.total_logical_reads / s.execution_count
    , SUBSTRING(t.TEXT, (s.statement_start_offset / 2) + 1, (
            (
                CASE s.statement_end_offset
                    WHEN - 1
                        THEN DATALENGTH(t.TEXT)
                    ELSE s.statement_end_offset
                    END - s.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    , s.execution_count AS ExecutionCount
    , s.max_elapsed_time AS MaxElapsedTime
    , ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime
    , s.creation_time AS LogCreatedOn
    , *
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE TEXT LIKE '%ObjectName_From_QueryAbove%'
    AND TEXT NOT LIKE '%sys.dm_exec_query_stats s%'
ORDER BY s.total_logical_reads / s.execution_count DESC

Hopefully this gives you some troublesome queries.  You may find that you just don't have the proper amount of memory in the instance for the workload it's trying to accommodate.  Alternatively you may find a query written by a user missing something as simply infuriating as a predicate (e.g. the WHERE clause).

If you want to dig into the execution plan, you can take the plan_handle and send it into this query:

SELECT query_plan
FROM sys.dm_exec_query_plan (PLAN_HANDLE_FROM_Query_3)

and review the execution plan.  If you are not able to tune said query, this probably isn't as useful to you.

Once you figure out what's using the memory on the instance, you can come up with a better idea on how to get the system back to a healthy state.


Can SQL Express be used as a Log Shipping monitor server?

No, log shipping is not supported on SQL Express 2008 R2, even as the monitor.

The official MSDN page does not state this explicitly, but I found this case where someone tried to use Express and got an error.

If you have either a Workgroup or Web edition licence lying around unused, those support what you need, as well as Standard (and higher).


Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service

Hopefully I can answer your question and explain how I did so.

To alter the service start mode using your code above you run

# Check Start Mode
$MyServerObject.StartMode
# Set Start Mode
$MyServerObject.StartMode ='Manual'
# Alter Start Mode
$MyServerObject.Alter()
# Check Start Mode again
$MyServerObject.StartMode

You can establish by using Get-Member as shown below

$MyServerObject|Get-Member

In the list of results you will see

StartMode                Property   Microsoft.SqlServer.Management.Smo.Wmi.ServiceStartMode StartMode {get;set;}

The {get;set;} shows that you can alter the property

You will also see

Alter                    Method     void Alter(), void IAlterable.Alter()

This is the method that you will use to apply the change to the object.

You can use the same principle throughout your PowerShell use.

It is also possible to do this remotely, if you have the correct privileges for your account, to do the following

# Get Service
$Service = Get-WmiObject win32_service -ComputerName COMPUTERNAME -Filter "Name = 'MSSQLSERVER'"
# Get Start Mode
$Service.StartMode
# Change Start Mode
$Service.ChangeStartMode('Manual')
#Check Start Mode
$Service.StartMode

to achieve the same result


Unexpected Shutdown affects SQL Server DB start at OS start up

Your DB has AUTO_CLOSE ON (which is typical of SQL Express instances, like yours). It means that the database is not opened until first accessed. This is when the first mention of it appears in the SQL ERRORLOG file.

The delay of 23 minutes is because your application is not opening the database. If you think this is a problem, it must be in your app. Are you sure your app, which must be an NT service, is configured to auto-start on OS boot?


Alter table add column like another column syntax

No. This is not possible using command.

All you can do is write a procedure to grab the table & column-name. Get the column definition from information_schema. Create alter-table-modify-column syntaxes & exec... Something like following:

call modify_table(target_table, source_table, source_column);


Use this code to see who has access to what:

;WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1;

It is likely the account in question is a member of some role, such as db_datareader which is giving it the ability to query other tables.  By default, accounts do not have the ability to access anything.


Should I run SQL Server services on multiple servers under different AD accounts?

Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.

Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).

The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.

SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.

In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.

Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.


Get durations from time series table where some condition holds consecutively

You could try along:

SELECT
  ts
  , CASE status
      WHEN false THEN 'end'
      WHEN true THEN 'start'
      ELSE 'ERROR'
    END AS mode
FROM (
  SELECT
    ts
    , status
    , LAG(status, 1) OVER (ORDER BY ts) AS lg
  FROM log
) AS T
WHERE
  (lg IS NULL)
  OR
  (status <> lg)
ORDER BY ts
;

The sub-select is necessary as window functions are not allowed directly in the WHERE clause.

See it in action: SQL Fiddle (Thanks for preparing the fiddle! ;-) )

Update

To get the duration actually listed, try

WITH
S1 AS (
  SELECT
    ts
    , status
    , LAG(status, 1) OVER (ORDER BY ts) AS lg
    , CASE status WHEN LAG(status, 1) OVER (ORDER BY ts) THEN 0 ELSE 1 END AS toggle
  FROM log
  ORDER BY ts
),
S2 AS (
  SELECT
    ts
    , status
    , lg
    , SUM(toggle) OVER (ORDER BY ts) AS streak
  FROM S1
  ORDER BY ts
),
S3 (ts, last_ts, status, lg, streak) AS (
  SELECT
    ts
    , LAST_VALUE(ts) OVER (PARTITION BY streak
                           ORDER BY ts
                           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                          )
    , status
    , lg
    , streak
  FROM S2
  ORDER BY ts
),
S4 (start, duration) AS (
  SELECT
    ts
    , last_ts - ts
  FROM S3
  WHERE
    status
    AND ((status <> lg) OR (lg IS NULL))
  ORDER BY ts
),
S5 (ts, status, duration) AS (
  SELECT
    ts
    , status
    , LEAD(ts, 1, (SELECT MAX(ts) FROM S3)) OVER (ORDER BY ts) - ts
  FROM S3
  WHERE
    (status <> lg)
    OR
    (lg IS NULL)
  ORDER BY ts
),
S6 (start, duration) AS (
  SELECT
    ts
    , duration
  FROM S5
  WHERE status
)
SELECT
--  * FROM S4
  * FROM S6
;

See it in action: SQL Fiddle

It starts off by identifying the consecutive ranges. In S4, the ranges end when status is TRUE for the last time respectively; in S6 when status changed to FALSE.

This could probably be condensed a little bit. But as window functions cannot be nested (directly) anyway…

Please comment, if and as this requires adjustment / further detail.


Changes access method for non-correlated subquery

The database may be ignoring your index if your statistics are stale (including both the system and table statistics) or if your initialization parameters are not optimized. The default optimizer cost adjustment is likely wrong for most applications, for example.


A restore of the snapshot database resolved the error. Looks like the index was corrupted.


Should I join datetime to a date using cast or range?

"It depends".

One advantage of the = predicate and cast to date is that the join can be hash or merge. The range version will force a nested loops plan.

If there are no useful indexes to seek into the datetimecolumn on tasks this would make a substantial difference.

Setting up the 5K/ 2 million rows of test data mentioned in the question

CREATE TABLE events
  (
     eventId    INT IDENTITY PRIMARY KEY,
     datecolumn DATE NOT NULL,
     details    CHAR(1000) DEFAULT 'D'
  )

INSERT INTO events
            (datecolumn)
SELECT TOP 5000 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY @@SPID), GETDATE())
FROM   spt_values v1,
       spt_values v2

CREATE TABLE tasks
  (
     taskId         INT IDENTITY PRIMARY KEY,
     datetimecolumn DATETIME2 NOT NULL,
     details        CHAR(1000) DEFAULT 'D'
  );

WITH N
     AS (SELECT number
         FROM   spt_values
         WHERE  number BETWEEN 1 AND 40
                AND type = 'P')
INSERT INTO tasks
            (datetimecolumn)
SELECT DATEADD(MINUTE, number, CAST(datecolumn AS DATETIME2))
FROM   events,
       N

Then turning on

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

And trying the CAST version

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
GROUP  BY events.eventId

Completed in 7.4 seconds

Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 1, logical reads 28679
Table 'events'. Scan count 1, logical reads 719

   CPU time = 3042 ms,  elapsed time = 7434 ms.

The estimated number of rows coming out of the join and into the GROUP BY was far too small (5006.27 vs actual 2,000,000) and the hash aggregate spilled to tempdb

Trying the range predicate

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datetimecolumn >= events.datecolumn
            AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)
GROUP  BY events.eventId

The lack of an equality predicate forces a nested loops plan. As there are no useful indexes to support this query it has no option but to scan the 2 million row table 5,000 times.

On my machine that gave a parallel plan that eventually completed after 1 minute 40 seconds.

Table 'tasks'. Scan count 4, logical reads 143390000
Table 'events'. Scan count 5, logical reads 788
Table 'Worktable'. Scan count 0, logical reads 0
  CPU time = 368193 ms,  elapsed time = 100528 ms.

This time the number of rows coming out of the join and into the aggregate was grossly over estimated (at estimated 124,939,000 vs actual 2,000,000)

Repeating the experiment after altering the tables to make the respective date/time columns the clustered primary key altered the results.

Both queries ended up choosing a nested loops plan. The CAST as DATE version gave a serial version that completed in 4.5 seconds and the range version a parallel plan that completed in elapsed time 1.1 seconds with CPU time of 3.2 seconds.

Applying MAXDOP 1 to the second query to make the figures more easily comparable returns the following.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 5000, logical reads 78137
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3167 ms,  elapsed time = 4497 ms.

Query 2

Table 'tasks'. Scan count 5000, logical reads 49440
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3042 ms,  elapsed time = 3147 ms.

Query 1 had an estimated 5006.73 rows coming out of the join and the hash aggregate spilled to tempdb again.

Query 2 again has a large overestimate (at 120,927,000 this time).

The other obvious difference between the two results is that the range query looks like it manages to seek into tasks more efficiently in some way. Only reading 49,440 pages vs 78,137.

The range that the cast as date version seeks into is derived from an internal function GetRangeThroughConvert. The plan shows a residual predicate on CONVERT(date,[dbo].[tasks].[datetimecolumn],0)= [dbo].[events].[datecolumn].

If Query 2 is altered to

   LEFT OUTER JOIN tasks
     ON tasks.datetimecolumn > DATEADD(day, -1, events.datecolumn)
        AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)

Then the number of reads then becomes the same. The dynamic seek used by the CAST AS DATE version reads unnecessary rows (two days worth rather than one) and then discards them with the residual predicate.

One other possibility would be to restructure the table to store the date and time components in different columns.

CREATE TABLE [dbo].[tasks](
    [taskId] [int] IDENTITY(1,1) NOT NULL,
    [datecolumn] date NOT NULL,
    [timecolumn] time NOT NULL,
    [datetimecolumn]  AS DATEADD(day, DATEDIFF(DAY,0,[datecolumn]), CAST([timecolumn] AS DATETIME2(7))),
    [details] [char](1000) NULL,
PRIMARY KEY CLUSTERED
(
    [datecolumn] ASC,
    [timecolumn] ASC
))

The datetimecolumn can be derived from the component parts and this has no effect on row size (as width of date + time(n) is the same as the width of datetime2(n)). (With an exception being if the additional column increases the size of the NULL_BITMAP)

The query is then a straight forward = predicate

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datecolumn = events.datecolumn
GROUP  BY events.eventId

This would allow a merge join between the tables without any need to sort. Though for these table sizes a nested loops join was chosen anyway with stats as below.

Table 'tasks'. Scan count 5000, logical reads 44285
Table 'events'. Scan count 1, logical reads 717
   CPU time = 2980 ms,  elapsed time = 3012 ms.

As well as potentially allowing different logical join types storing the date separately as the leading index column would also potentially benefit other queries on tasks such as grouping by date.

As for why the = predicate shows fewer logical reads on tasks than the > <= version with the same nested loops plan (44,285 vs 49,440) this appears to be related to the read ahead mechanism.

Turning on trace flag 652 reduces the logical reads of the range version to the same as that of the equals version.


Run Transaction Log backups only during Work hours?

On a technical level: is this a good practice or not?

I would say not. If the database is experiencing no activity at all during the business off hours, then taking the backups is very low overhead. On the other hand, if the database is experiencing any activity during business off hours then not taking the backups is a serious problem.

My stance is that taking the backup has advantages:

is "future proof": if someone adds an off-hours job, your backups will 'just-work'
less complex, is easier to explain/understand/operate: no special hours for log backups. No special recovery procedure for 'off hours' vs. 'on hours'
it does no damage


How can I add a numeric version_id column to an existing table?

This is exactly what ROW_NUMBER() window function can do:

select
    id, version_date,
    row_number() over (partition by id order by version_date) as version
from
    your_table ;

I don't see a reason to have these version numbers stored in a column of the table. It would be fairly easy to insert the values the first time but quite complicated to keep them updated after every insert, delete and update operation (probably done via triggers or stored procedures).

It might not be that complicated if no rows are ever deleted and the version_date is never updated. That would mean that only during inserts the new (inserted) values would have to be correctly calculated.

Another option, would be to use a materialized view.


How to setup Mysql master-slave replication with the slave meant for testing where divergence is OK

Given the situation that you’ve described, any form of replication will fail spectacularly within minutes of a rigorous pen test. Having a completely isolated database instance for this purpose alone is the most logical solution.
Here are a couple of reasons why:

as you said, someone issues a DROP TABLE and, because there’s no RI in your database to prevent this and because it sounds like the application is using a user account with full privileges, the command is carried out. Replication won’t “fix this” for you.
Someone creates a new record on the replicated instance. Are all primary keys GUIDs? If so, you might be lucky enough to never run into a primary key collision but if you’re using incremental integers, there will be overlap (even if the replica is configured to use a higher range of numbers)
Someone deletes a record
Someone adds a column, then that same column is later added to the main server
Someone truncates a table
And on and on and on …

Automated restores of your production database should already be something that the company is doing as part of its backup verification process, so I don’t see how this would be cumbersome or difficult to automate. Every operating system under the sun can handle a little script that takes a recent backup and restores it to a database. Unless your system is already many hundreds of gigabytes in size, this would be a very simple and cost effective way forward.
As an added bonus, this script would make refreshing the development database(s) an absolute breeze


I experimented with trying to create a trigger for a system table (mysql.slow_log). It simply cannot be done. However, there is a rather unconventional method you may want to try:

Step 01) Activate the slow_log with the output as a table and restart mysql

[mysqld]
log-output=TABLE
long-query_time=0.0001

Step 02) Convert slow_log to MyISAM, copy it to areva_log, and index slow_log on db

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
CREATE TABLE mysql.areva_log LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log ADD INDEX (db);
SET GLOBAL slow_query_log = @old_log_state;

Step 03) Create a Stored Procedure to Copy all Slow Log entries whose db is areva into mysql.areva_log

DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`AppendToArevaLog` $$
CREATE PROCEDURE `mysql`.`AppendToArevaLog` ()
BEGIN
    SET @old_log_state = @@global.slow_query_log;
    SET GLOBAL slow_query_log = 'OFF';
    INSERT INTO mysql.aerva_log
    SELECT * FROM mysql.slow_log WHERE db = 'areva';
    SET GLOBAL slow_query_log = @old_log_state;
END $$
DELIMITER ;

Step 04) Call the stored procedure every 15 minutes using a crontab or using a mysql event

Step 05) Zap the slow log every week, month, or year:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
CREATE TABLE mysql.slow_log_empty LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_to_zap;
ALTER TABLE mysql.slow_log_empty RENAME mysql.slow_log;
SET GLOBAL slow_query_log = @old_log_state;
DROP TABLE mysql.slow_log_to_zap;

Give it a Try !!!


SQL Server Cross Domain Transcational Replication

Replication across non-trusted domains or workgroups can be done using Push Subscriptions in conjunction with SQL Authentication for the replication agent process accounts. Alternatively, you can also use Windows Authentication by configuring pass-through authentication.

To use pass-through authentication, create a local Windows account on both the Publisher and Subscriber that has the same username and password. Use this account for the replication agent process account and have the connections to the publisher, distributor, and/or subscriber impersonate this account. Ensure the account has the permissions required in Replication Agent Security Model.

This approach is covered in the section Use Windows Authentication to Set Up Replication Between Two Computers Running SQL Server in Non-Trusted Domains in HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet.

If you have anymore questions, please let me know.  I hope this helps.


10+ minute query time with DATEPART in WHERE clause

Why not try using a calendar table?  Using the code to create one (took about 1 second) from here.

SELECT COUNT(1)
FROM My_Table
JOIN #Dim
    ON My_Table.my_datetime = #Dim.date
WHERE DATEPART(year,CAST(getdate() AS DATE))-1 = [year]
  AND DATEPART(week,CAST(getdate() AS DATE)) = [week]
  AND DATEPART(weekday,CAST(getdate() AS DATE)) = [DayOfWeek]

In general (as I'm sure has been mentioned in the comments by now) any time you use functions on a column in the WHERE clause you are going to take a performance hit.  Frequently a very large one.  Among other things those columns are not SARGable (search argument, ie can use an index).


Cannot register SPN, error 0x80090350, state 4

The error message you're seeing, 0x80090350, is defined as:

c:\util\Err>err 0x80090350

# for hex 0x80090350 / decimal -2146892976 :
  SEC_E_DOWNGRADE_DETECTED                                        winerror.h
# The system detected a possible attempt to compromise
# security.  Please ensure that you can contact the server
# that authenticated you.
# 1 matches found for "0x80090350"

I'm looking this up with the Exchange Error Lookup Tool.

According to the information in this post, this error is often caused by the MaxTokenSize issue caused by an account (indirectly or directly) being a member of a large number of groups.

Another possibility I'd consider is that a duplicate SPN exists. You can determine which SPNs Windows thinks are duplicates by running setspn -X -F (info here).


Is Sql server thread-safe?

This issue is not with that SQL Server being thread safe or not.The issue is there with SQL Server Connection Pooling

In Short:- Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. More reading from the link above.

Things to try with Connection object to fix this

Don't reuse connections or any ADO.NET objects at all and don't make them static.
Always create, open(in case of Connections), use, close and dispose them where you need them
Use the using-statement to dispose and close(Connections) implicitely

Now update on same row:
If the update statement are to acquire a row/table lock, for instance, any other update process to the same row/table at the same time would be blocked.

Further Reading:

Locking and Row Versioning
and Table Hints


Using a PostgreSQL tablespace from two different servers

I strongly recommend doing a dump and reload.

Don't put tablespaces on removable media, it doubles your chances of data loss. If either the main database on the main disk or the removable media fails / is lost, your data's gone. A tablespace cannot be read without the database it's from.


For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

I would not bother with persisted columns at all. I would just define a computed column and have it recalculated every time we select, so that I save considerable storage and have my selects do less reads, at the expense of the negligible amount of CPU needed to calculate volume.

Of course, I did some benchmarks in the past.


Limit results to the first 2 ranking rows

select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2


Truncate database in mysql server by half?

there is no single query for this, but first what you need to analyze:

are all tables have a similar size or not?
if any relations explicit or implicit?

often case when only a few tables take 80% of space, and in this case, you can copy all other as is and a half for biggest tables.

if foreign keys enabled - you must prepare a proper sequence of queries
if no foreign keys - you must think, do you strictly require data consistency or it just a test data and this moment not important


You could just create new index with different name by
create index concurrently index_new on ...

Then drop corrupted index with
drop index concurrently index_old;

Then you could rename new index to old name:
alter index index_new rename to index_old;

Latter will require lock, but for few milliseconds of runtime after acquire the lock. So you do not need downtime due to write lock.
The definition of the index can be obtained from the command pg_dump -s -t tablename --no-acl
This is exactly the same procedure that does reindex concurrently under the hood. But reindex concurrently is a bit cheaper since do not need lock for index rename phase.

Also widely known pg_repack has feature to reindex table with option --only-indexes. This option is implemented as create + drop index concurrently.

Is there a simple metric I can use to determine how much data will be required to be read to reindex fully?

Well, any index creation without concurrently will read the entire table sequentially (concurrently will read the table twice). Something else depends on access method. Btree will sort all live tuples. This is the most time-consumption part of create index, for large indexes the work will be done in temporary files (remember increase maintenance_work_mem). This part also depends on datatypes and values. Text with small selectivity (e.g. some status field) will be noticeable slower to build than integer sequences.
I have no way to estimate, except for one: to measure the creation time of an index on some data sample:
create table estimate_table as (
  select * from tablename
  where created_at > '2020-01-01'
);
\dt+ estimate_table
\timing on
create index on estimate_table ...

Reindex is just a special form of index creation. Hmm, and an important point: reindex table has no difference with several reindex index in terms of resourse usage. reindex table is implemented by calling reindex_index for each individual index on table. So, table with 5 indexes will be scanned 5 times.


Replicating from MySQL 5.1 master to 5.6 slave failing because 'INSERT ... VALUES (NOW())' results in 'Error_code: 1062'

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is
  triggering the error here?

Yes that's exactly what the error telling

Error 'Duplicate entry '8072' for key 'PRIMARY'' on query.
Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())',
Error_code: 1062

  Can I get replication to work without skipping rows with SET GLOBAL
  sql_slave_skip_counter = 1;?

Yes, You can go and delete the row with the ID 8072 in the table.. you can add the option of slave-skip-errors = 1062 in my.cnf and restart mysql

But you have to be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

The only clean way is:

In the master

You FLUSH TABLES WITH READ LOCK; in the database you want to
replicate
SHOW MASTER STATUS and copy the file name and the position
Excute a full backup to the database you want to replicate
Then release the tables with UNLOCK TABLES;

In slave

Restore the databse
Excute this CHANGE MASTER TO MASTER_HOST = 'masterhost', MASTER_USER
= 'masteruser', MASTER_PASSWORD = 'masterpass', MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = 'position';
Then start slave;


How to model a course > section > lesson structure where lessons can belong to either sections or courses?

Generally when I need to handle this sort of situation in textbooks, I “hide” the full structure of a book from the author. So, for your situation, I would have the following structure:
Course
⇢ Section
  ⇢ Lesson

This way, all courses would have a minimum of one section. This offers a course author a bit of flexibility in the future if they choose to switch to (or from) a Course⇢Lesson to Course⇢Section⇢Lesson model. It also drastically simplifies the SQL queries that will be used, as everything is consistent. Other developers will appreciate this when being introduced to your system.

Disclaimer: I write textbook and online learning software for a living, so my design patterns may be biased as a result of experience working with public and private institutions.


If your server's ip address is static, you can modify your hosts file with a reference to DBNODE2's ip address and specify DBNODE2 in your Connect to Server Server Name file.


mysql set auto increment to 1 with foreign key

If you ever need ALTER TABLE tablename AUTO_INCREMENT = 1 you are probably misusing AUTO_INCREMENT.  AUTO_INCREMENT is designed to provide unique keys, nothing more.  If you need it to be "1", you should invent your own mechanism; do not trust AUTO_INCREMENT.

Would you care to explain your purpose?  Perhaps we can help you find a suitable workaround.  (No "setting it to 1" is not the purpose, it is your attempted solution to some higher purpose.  Step back.)


MySQL Replication - transactions missing in M->S1->S2 setup

The problem stems from log_slave_updates. You need that in S1 so the binary logs of S1 can be the source for S2 reading events.
Please add this line to my.cnf in S1
[mysqld]
log-slave-updates

and restart mysqld on S1. Then, setup replication from S1 to S2. S2 is currently out of sync with S1, so S2 may need to be reloaded or sync'd up using pt-table-checksum and pt-table-sync.
UPDATE 2018-06-25 12:23 EDT
Thank you for the update,
Here is your problem:
M1 and S2 have the same server_id.
S2 will never process an event if the incoming event's server_id is the same as S2.
Change the server_id on S2 to something other than 4 or 6 used by M and S1.


Suppress SQL Server Management Studio auto complete

Hit ESC to close the window when done typing the word before hitting space, but if you hit ESC after 'fo' and then type 'o' it will kick in again.

Another option if you don't use it often would be to disable it (Tools->Options->Text Editor->Transact-SQL->General->Auto list members) and use CTRL+J to bring it up manually when you want to use it.  Red Gate also has a product called SQL Prompt which might offer more control.

In addition there is an "Intellisense Enabled" option under the Query menu and on the Query toolbar if you want to temporarily disable/enable it.


MariaDB 10.2.7 doesn't accept trailing Z for DATETIME timezone when inserting, but earlier version does

MariaDB does not recognize your date format. In previous versions it autocorrefted value for you, but starting from version 10.2.4 MariaDB changed SQL mode to stricter and raises error instead of warning for faulty SQL queries.
If it's production, then temporary solution would be changing sql_mode to be not so strict. Change file /etc/my.cnf (on CentOS)

[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

And restart mariadb service. I suggest you changing your query for MariaDB to fully understand it.
More info on sql_mode here: https://mariadb.com/kb/en/mariadb/sql-mode/


Are columns which are not indexes, sorted on disk together with index?

They may be sorted in some cases. The sorting index is usually called the clustering key. If it is the case then the entire table is stored inside such index (usually in some sort of a B-tree structure).

In the other case the table structure is known as a heap, rows are stored as they come, deleting leaves "holes" in the data blocks and those holes are later filled with new rows, so not even the "insertion order" is preserved.

MyISAM uses the heap structure, with each row being identified by the offset(sort of array index) into the data file. Each index then contains the indexed column(s) for each row, sorted in the proper order and with the offset number to locate the real row. That means that accessing the row by any index means locating the right node(s) in the index (B-tree) and then reading the right offset(s) from the data file (random seek to a different part of the disk may happen).

InnoDB uses clustering by the primary key (or if none is defined, first non-null unique key is used, or an internal autoincrementing column is added - so the rows are always sorted somehow). In such case an access by the primary key is "direct", when the proper value is located, you have entire row at hand, no need to do a second read. The secondary indexes on the other hand cannot store an offset like in MyISAM (because the B-tree is dynamically rebalancing itself, so the offset of a specific row can change anytime) and they store the primary key values of the row instead - so an access by a secondary key means two B-tree searches in InnoDB.

MS SQL Server offers an option to make the primary key (or another index) either clustered or nonclustered, so you can choose between the heap (no index is clustered) and the tree structure (one index is clustered). All other non-clustered indexes store a special (RowID) values in the heap case or the clustered key values of the row in the case of the CI.

PostgreSQL uses only heap tables but lets you reorder them by some index on demand (you have to trigger it, so the rows are ordered after the action but further writes to the table can break that order again).

TokuDB (a 3rd-party MySQL/MariaDB engine) can use multiple clustering keys on one table - effectively it maintains multiple copies of the table, each sorted different way. It comes with a penalty on writes, but TokuDB claims to use somethink they call fractal indexes which should make that penalty quite small.

If you need to use that functionality for some queries, you can "emulate" it by creating a covering index - that way the colums your query needs are available in the right order anytime, but again it means maintaining an ordered copy of (parts of) the table in your indexes.


Unable to search between IPv6 range when converting string IP Address to VARBINARY(16)

Initially there are two problems with your conversion:

It's the wrong "type" of conversion. You are converting a string into a hex / binary representation of the characters them selves. For example, a "26" will become 0x3236 in hex as 0x32 is the same as doing CHAR(0x32) and 0x36 is the same as CHAR(0x36), or it can be referred to as the ASCII value of the number "2". What you want is to simply change the form of the string "26" (which represents a byte) into 0x26. And for that you need to use a style number of "2" for the CONVERT function. For example:

SELECT CONVERT(VARBINARY(2), '26'), CONVERT(VARBINARY(2), '26', 2);
-- 0x3236    0x26

This is why you are getting 0x323630303A383830303A366130363A32 from 2600:8800:6a06:2000:1d29:3b7:8c7c:271b. You are really only getting the first 16 characters from that string, which are: 2600:8800:6a06:2. The two characters on the left I explained directly above. The 3 characters on the right, 6:2 are the 0x363A32 on the right side of the hex value, since 0x36 = "6", 0x3A = ":", and 0x32 = "2".
You will need to get rid of the colons as they are not valid binary digits. But the colons are there as delimiters for a reason: there are 8 segments, and if leading "0"s are removed, which is valid to do, then 0 really needs to be 0x0000, not 0x00 or 0x0. So, you need to ensure that each segment comes back as 4 hex digits.

You can accomplish all of this by:

splitting on the colons
left-padding with "0"s
converting to BINARY(2)
concatenating those pieces together

If you are using SQL Server 2016 or newer, you can probably use the built-in STRING_SPLIT function. Or, if you are using a version prior to SQL Server 2016, you can either use whatever splitter you already have, or you can use the free version of my SQL# SQLCLR library (there are several string splitters in the Free version).

For example:

SELECT RIGHT('0000' + prt.[SplitVal], 4) AS [string],
       CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2) AS [hex]
FROM   SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

returns:

string    hex
2600      0x2600
0100      0x0100
0400      0x0400
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000

That was just a way to see what the pieces are. To put them together into a single hex value, you can do the following:

DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2)
FROM SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

You can create a function to handle this conversion. You can then store these VARBINARY(16) values and create an index on them, and then convert the string coming in and store that in a variable to use in the query.

Here is that same approach, but using the built-in STRING_SPLIT function:

DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[value], 4), 2)
FROM STRING_SPLIT('2600:100:400:0:0:0:0:0', ':') prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

Just keep in mind that some folks are hesitant to use STRING_SPLIT in this type of situation because here the order that the split parts are returned is important as it can change the value if they came back in a different order, and the built-in function doesn't return a row number. I personally don't see how the returned values could come back out of order, but that is just an educated guess and not a guarantee. If you want a guarantee, then don't use the STRING_SPLIT built-in function.

P.S. The solution shown above (i.e. splitting on the colons) does not handle the "reduced" notation / syntax whereby "::" can be substituted for one or more consecutive ranges of "0000". For example: 2600:100:400::0. Hence, that solution requires that all 8 segments exist with at least "0" in them.

P.P.S. If you are using .NET either in the app or SQLCLR or SSIS, you can use a combination of IPAddress.TryParse and IPAddress.GetAddressBytes to handle the reduced "::" notation. Or, you could always write a bunch of code to handle it in pure T-SQL.

P.P.P.S. INET_IPv6toBinary will be available in the next release (version 4.3) of SQL# :-)


I'm not familiar with the layout of your database design (there are many layouts in the market). But what I'm figuring out is that you are trying to connect two entities together, directly. Which is wrong. Two entities can only be connected by a relationship (no matter what ER layout you are using).

Your first query can be subdivided into three parts:
1) An event can have only one organizer
2) Organizer is an instance of User
3) There is a one to many relationship between event and organizer.

So you have three entities: Event, Organizer, User.
Then, if you have studied about weak entities, you would want to convince yourself that organizer is a weak entity which is dependent on the entity User. Connect entities User, and Organizer with a relationship "is_instance". Now you are done with the entity User.

Let's now move on to the relationship between entities Organizer, and Event. You already figured out that it has a one to many relationship from Event to Organizer. So great!!

Now coming to your next query: An event can have at most three categories but a category can have any number of events. You can say that this is a kind of many to many relationship. Some ERD allow you to explicitly specify the cardinality between the entity and the relationship (as far as I remember it is done by specifying the number over the arrow joining the entity and relationship). So you can model this as joining the entities Event, and Category by a relationship has_category and specify the range 1..3 over the line joining Event and has_category.

Extra clarification:
There is always a many to one relationship from a weak entity towards its underlying entity. Your inclusion of details to the entity organizer and the dependence of Organizer's ID on User's ID makes it a separate entity.

(Note: due to lack of information, I assumed that there may be more than one value for the attribute "detail" for the same user in the entity Organizer. If this is not so and that there is only one tuple in the entity Organizer for every ID such that every tuple in the entity Organizer is unique on attribute Organizer_id, then you could also choose to make the Organizer entity as a strong entity and have a total participation of entity Organizer in the relationship between User and Organizer)

Additionally, your query also states that the user can participate in many events, so there can be a many to many relationship between entities User, and Events.

Hope this helps.


For SELECTs:

show global status like "Com_select";

UPDATEs:

show global status like "Com_update";

INSERTs:

show global status like "Com_insert";

DELETEs:

show global status like "Com_delete";

ALl values are "cumulativ" since MySQL last restart.

So to get your SELECTs in one hour:

At 9pm:

[21:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 671664 |
+---------------+--------+
1 row in set (0.00 sec)

At 10pm:

[22:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 672363 |
+---------------+--------+
1 row in set (0.00 sec)

The number of SELECT in the past hour : 672363 - 671664 = 699

Best Regards


Extract street address from string with multiple delimiters and positions

Try this.

;With cte as
(
Select
    places,
    StartHere =
        CASE
            WHEN PATINDEX('%@[0-9]%', places) > 0 THEN PATINDEX('%@[0-9]%', places) + 1     -- @ plus number, start here
            WHEN PATINDEX('%;[0-9]%', places) > 0 THEN PATINDEX('%;[0-9]%', places) + 1     -- ; plus number, start here
            ELSE 0
        END
    ,EndHere =
        CASE
            WHEN PATINDEX('%@[a-z]%', places) > 0 THEN PATINDEX('%@[a-z]%', places)   -- @plus letter, stop here
            WHEN PATINDEX('%@[0-9]%', places) > 0 AND PATINDEX('%;%', places) > 0 THEN  PATINDEX('%;%', places)  --@ plus number stop on semicolon if there
            WHEN PATINDEX('%;[0-9]%', places) = 0 AND PATINDEX('%;%', places) > 0 THEN PATINDEX('%;%', places)   --no semi plus number, so stop on semicolon if there
            ELSE len(places) + 1
        END
FROM
    foo
)
    SELECT
        places,
        StartHere
        ,EndHere
        ,SUBSTRING(places, StartHere, EndHere - StartHere)
    FROM
        cte


Generally, this pattern is more efficient and far less likely to lead to deadlocks or other concurrency issues:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND COALESCE(FavoriteColor, '') <> 'Green';

DELETE dbo.Person
  WHERE Name = 'Bob'
  AND FavoriteColor = 'Green';

...simply because you only have to check for the row once. You might also write the UPDATE as:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND (FavoriteColor IS NULL OR FavoriteColor <> 'Green');

...which may be more index-friendly in some cases.

Exclusive locks for write operations are not taken until just before a row to modify is actually updated. While SQL Server is searching for rows to update, it uses update locks, which do not conflict with concurrent reads. The update lock is released immediately if SQL Server determines that the row being checked does not qualify for the update.

The only reason I can think of using the if exists method is if there are UPDATE/DELETE triggers in the table that you want to avoid being fired, especially if you have INSTEAD OF triggers which can take some action before any update or delete is actually attempted.

It is normally best to write DML such that only rows that require a change are affected.


Estimated versus actual query plan with function calls

And nothing about the functions. Why is the function information
  missing in the actual plan?

This is by design, for performance reasons.

Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row.  Put another way, the function body is executed separately for each input row.  This single fact explains most performance problems associated with T-SQL scalar and multi-statement functions (note that in-line table valued functions do not use  the BEGIN...END syntax).

In the context of your question, this would result in full SHOWPLAN output for each row.  XML plan output is quite verbose and expensive to produce, so producing full output for every row would be a bad idea in general terms.

Example

Consider the T-SQL scalar function below, created in the AdventureWorks sample database, which returns the name of a product given its ID:

CREATE FUNCTION dbo.DumbNameLookup
(
    @ProductID integer
)
RETURNS dbo.Name
AS
BEGIN
    RETURN
    (
        SELECT
            p.Name
        FROM Production.Product AS p
        WHERE
            p.ProductID = @ProductID
    );
END;

Pre-execution plan

A pre-execution plan (estimated plan in SSMS) shows plan information for the parent statement and nested function calls:

-- Pre-execution plan shows main query and nested function call
SET SHOWPLAN_XML ON;
GO
SELECT dbo.DumbNameLookup(1);
GO
SET SHOWPLAN_XML OFF;

SSMS output:

The same XML viewed in SQL Sentry Plan Explorer shows the nested nature of the calls more clearly:

Post-execution output

SSMS shows details for only the main query when post-execution plan output is requested:

-- Post-execution plan shows main query only
SET STATISTICS XML ON;
SELECT dbo.DumbNameLookup(1);
SET STATISTICS XML OFF;

The performance impact of doing otherwise can be shown using the Showplan XML Statistics Profile Event Class in SQL Server Profiler, using a query that calls the function multiple times (once per input row):

SELECT TOP (5)
    p.ProductID,
    dbo.DumbNameLookup(p.ProductID)
FROM Production.Product AS p;

Profiler output:

There are five separate post-execution plans for the function executions, and one for the parent query.  The five function plans look like this in the profiler lower pane:

The parent query plan is:

Executing the query without the TOP (5) clause results in a full execution plan for each of the 504 rows in the Product table.  You can probably see how this would quickly get out of hand with larger tables.

The situation for triggers is reversed.  These do not show any pre-execution plan information, but do include a post-execution plan.  This reflects the set-based nature of triggers; each is fired once for all rows affected, rather than once per row.


PostgreSQL - automatically create a database for users on first login

No that's not possible.

Even if it was possible: as you need to specify a database when you connect, the new database wouldn't be the one the user connected to (and there is no way to re-route the user to the newly created one).


Try putting the name in brackets.

DROP USER [\username];

Given the problems you are having try doing this through dynamic SQL just to confirm that you are catching every possible character.  You could also try this through Object Explorer.

DECLARE @sql nvarchar(1000)
SELECT @sql = N'DROP USER [' + name + N']' FROM sys.database_principals WHERE name like '\user%'
PRINT @sql
EXEC sp_executesql @sql

@solomonrutsky brought up an interesting question.  Try running the following:

SELECT s.name AS Sysusers_Name, dp.name AS DP_Name
FROM sys.sysusers s
JOIN sys.database_principals dp
    ON s.sid = dp.sid
WHERE s.name = '\user'

See if you get any results, and if so if the Sysusers_name doesn't equal the DP_Name.  If you get a DP_Name try dropping that one instead.


how to convert query related to calculate work hours in MySQL to MS Access

SELECT MachineNo, Empcardno, [Date],
       FORMAT(SUM(IIF(rn MOD 2 = 0, -TimeValue([Time]), TimeValue([Time]))),'hh:nn:ss') AS total_time
FROM (
  SELECT t1.MachineNo, t1.Empcardno, t1.Date, t1.Time, COUNT(t2.MachineNo) AS rn
  FROM timesheet t1
  LEFT JOIN timesheet t2 ON t2.MachineNo = t1.MachineNo
                        AND t2.Empcardno = t1.Empcardno
                        AND t2.Date = t1.Date
                        AND TimeValue(t2.Time) < TimeValue(t1.Time)
  GROUP BY t1.MachineNo, t1.Empcardno, t1.Date, t1.Time
)  AS t
GROUP BY MachineNo, Empcardno, Date

for complete detail please see the link
https://stackoverflow.com/questions/62297919/how-to-convert-query-related-to-calculate-work-hours-in-mysql-to-ms-access/62317847#comment110225804_62317847


The dbimport tool assumes that it must create the database (and won't work with an existing database).  There isn't a utility that migrates data like you're trying to do.

You have multiple options.  One is to use one of the replication schemes (ER, HDR, RSS, etc) to create a clone of the original database.  Another is to create the relevant SQL yourself and run that via DB-Access or another tool.  You might find external tables or HPL relevant for getting good performance on the loading.

What do you do with the data that already exists in the target database?  Delete it before importing the new?  Save it in case the new doesn't import cleanly?  Do you have constraints — primary key, foreign key — that will complicate any data loading?  Do you need to defer those constraints while all the loading takes place?  Do you want to rebuild the tables from scratch?

What size of database is under discussion?  Megabytes, gigabytes, terabytes, petabytes?  How many tables?  How big are the biggest tables — row size, number of columns, number of rows?

Incidentally, if the PostgreSQL tools simply export the data from one table and import into another, then that can be done with my SQLCMD program — a program which pre-dates Microsoft's johnny-come-lately program of the same name by over a decade.  SQLCMD has two aliases, sqlunload and sqlreload.  You could use:

sqlunload -d old_database -t old_table |
sqlreload -d new_database -t new_table

You can save the data in a file, of course:

sqlunload -d old_database -t old_table -o old_table.unl
sqlreload -d new_database -t new_table -i old_table.unl

And there can be file transfers, etc, as required in between the unload and reload operations.  If you need to truncate the new table before loading, that's a separate operation:

sqlcmd -d new_database -e 'truncate table new_table'

And so on.  By default, sqlreload (you can also use sqlcmd -R … for that, and sqlcmd -U … for sqlunload) will batch the loading into sub-transactions of 1024 rows, but you can configure that, of course.


Calling MySQL stored procedure in python with field names

sorry for the late answer, but I really do not think the chosen answer is good advice, the python mysql module has a method for this already, no need to do weird manips in the ddb. For this, just do:

cursor.callproc('sql_procedure', arguments)
for colid in cursor.stored_results():
    columnsProperties = (colid.description)
    print ([column[0] for column in columnsProperties])

you get all properties of the column in colid.description, the first property is the filename which is what you're after.


As the two comments mention, there are two things at work here.

sys.dm_os_wait_stats records accumulated wait times since the server was restarted. So if the server has been up for a while, these can be fairly high. To get a meaningful value out of sys.dm_os_wait_stats, you need to take two snapshots e.g. 5 minutes apart and compare them to each other. Keep in mind, there you cannot tie them to a single connection. Anything on the server that is waiting for something during that time will cause a change in one of the values returned by sys.dm_os_wait_stats.
If a query gets executed in parallel, each thread will use up CPU time as well as potentially wait time. So both the total CPU time as well as the total wait time for a single query can be higher than the elapsed time.


1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ?

SELECT *  FROM db.table HAVING COUNT(symbol) > 1

2. How can I remove all but one row ?

a) If you want to keep the row with the lowest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id > n2.id AND n1.symbol= n2.symbol

b) If you want to keep the row with the highest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id < n2.id AND n1.symbol= n2.symbol

3. Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

Please try http://befused.com/mysql/reset-auto-increment


How does Postgres make its B-tree index?

Masi,

The PostgreSQL B-Tree index is very strongly based on the implementation by Lehman and Yao, which includes a lot of work oriented around multi-version concurrency control, but there's still great info in this paper.

Of course, PostgreSQL doesn't make a 100% accurate replica of the method in the paper, and to find the differences, there will be almost no way to do it other than to (1) find someone who understands the PostgreSQL B-Tree, and has the time to go through the intricate explanation, or (2) dig through the source code yourself.

Another possibility is for you to visit Bruce Momjian's excellent reference website, where he discusses PostgreSQL internals in more detail.

In this case, however, based on the nature of your questions, I feel like you may have a fundamental misunderstanding about how B-Tree indexes work. In this case, I think a little Google searching, or maybe reading through a portion of a textbook like Fundamentals of Database Systems by Elmasri & Navathe would do you some good.


Database Mail Configurations using gmail account

Basically your error message says what you have to focus on.
As SSL is enabled make sure to set ON external unsafe application access in gmail setting and not to forget to refresh your account. If unsafe access is off, gmail will notify you by a message to assure that it is you trying to access and will show the way how to enable if you click yes.


MySQL master/slave replication... structure not being replicated

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence.  The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

  In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing.  It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions.  Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

  it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.


SQL Server Unique Constraint on two columns with an exception

You can create a unique index with a where clause to filter out those values for which you want to avoid duplicates.


Sometimes:

When restoring into Express Edition, the database size can't be larger than the size supported by Express (which varies by version)
When restoring into Express or Standard Edition from Enterprise or Developer Edition, if your database uses Enterprise-level features, the recovery portion of restore won't succeed (the Enterprise features vary starting with 2016 SP1, which put more features in Standard)
When restoring into any server, some features need to be enabled on the database server itself. Examples include replication, full text search, CLR.


That depends on how you take the backup.
If you use pg_dump or pg_basebackup, the backup time is not influenced by how long ago the previous backup was taken.
If you backup with pg_basebackup or another online file system backup technique, you don't need a new backup. Just use the previous one and the WAL archives, and you can restore any point in time in the life of the database.


How to retrieve disk spaces by removing tables from tablespaces in Oracle 10g?

You need to shrink the data files, which can be tricky.

However, possibly the easier method to describe and implement is to create a new tablespace of approximately the right size, use ALTER TABLE to move the table segments, and ALTER INDEX to rebuild the indexes in the new tablespace. When the old tablespace is empty of objects you can drop it.

Oracle Enterprise Manager can do this automatically for you. This is described in detail in this post: http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php#em_reorg


Recover oracle 10g database from old data files

Try to "recover until SCN..." before doing "open resetlogs".

You'll need the online redo logs to perform this (not clear from your question whether you have them or not)


Continuing failovers on production server

I'm not familiar with this particular error but I've encountered situations when a two-node cluster had multiple failovers due to MPIO issues with the SAN LUNs. More often than not, it was resolved by updating HBA drivers.

One other thing to look for is to ensure that the disk dependencies are properly set. The SQL Server service should be dependent on all that disks that are hosting the DB files and the backups as well as the disk with the drive letter acting as the mountpoint host. I've run into a few hosts where a missing disk dependency caused a disk to go offline before SQL could close out the DB files.


set serializable isolation for update query postgresql --9.4

A function takes part of an existing transaction. To avoid this context error, the transaction mode should be changed before the call to the function. Therefore you must remove the SET TRANSACTION statement function, and then use the code (for example):

Using your example...

function code -

--Atomically, using your example
CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

calling code to function -

    ...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT FROM foofunction (1,1);
COMMIT;
    ...

Or yet:

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
EXCEPTION WHEN others THEN
    ROLLBACK;
RETURN;
    COMMIT;
End $$ LANGUAGE plpgsql;

And

...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT FROM foofunction (1,1);
...

I hope have contributed!


Your table clearly has had a troubled history and became extremely bloated.  However, nothing in the log files you showed us indicate what the nature of that history is.

It could be that autovacuum was turned off for a time, or misconfigured and so ran too slow to keep up, or that there was a transaction held open for a very long time which prevented vacuum from being effective, or maybe someone was constantly locking the table in a mode stronger than ROW EXCLUSIVE, causing autovacuum to yield before finishing everytime it tried to run.


SQL Server Error 8632 due to over 100,000 entries in WHERE clause

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering.

Something with more than 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, consider Swart's Ten Percent Rule: if you're approaching 10% of a SQL Server limit, you're probably going to have a bad time.


Multicolumn GIN index not fully facilitated by query planner

I cannot reproduce this issue where it chooses to use two indexes, but I suspect it has something to do with the massively incorrect estimate for JSON containment condition; combined with another unknown factor like maybe some kind of index bloat.

...rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589

That is off by over 400 fold.  But there isn't much you can do about it, other than pull "CountryId" out into its own column, or upgrade to v13 where json containment selectivity estimation has been improved (though not by as much as if it were in its own column).
In any event, the choice of two different indexes is unlikely to actually be a problem.  When you use a GIN multicolumn index like this, it behaves pretty much the same as two different indexes combined with a BitmapAnd.  It is just that the BitmapAnd is occurring inside a black box where EXPLAIN can't show you the details.  I would generally not expect much difference in performance.  (One place I have seen a difference is if the tuples which match one condition are physically separated from the ones which match the other, i.e. all the tuples with campaignid = 97 are physically before all the tuples with attributes @> '{"CountryId": 3}' in the table.  The opaque BitmapAnd of the gin indexes has a optimization for this case that the transparent BitmapAnd does not seem to have.)
If your main goal is to make the query faster rather than either micromanaging exactly how to sublists get combined or redesigning your table, then I see two things to try.  One is increasing work_mem, as the massive number of lossy blocks can't be good for performance.  The other would be to create a multicolumn btree index on (campaignid, ((attributes->>'CountryId')::int)); and then rewrite the query accordingly:
WHERE campaignid = 97 AND (attributes->> 'CountryId')::integer=3


It seems like you want the following:

SELECT p.*,
  `e`.*,
  GROUP_CONCAT(`n`.`partnumber`) as `partnumbers`,
  GROUP_CONCAT(`o`.`oem`) as `oems`
FROM `w7nwd_com_mtapp_product` AS `p`
LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e`
  ON `e`.`product_id` = `p`.`id`
LEFT JOIN `w7nwd_com_mtapp_product_partnumber` AS `n`
  ON `p`.`id`=`n`.`product_id`
LEFT JOIN `w7nwd_com_mtapp_product_oem` AS `o`
  ON `p`.`id`=`o`.`product_id`
GROUP BY `p`.`id`

This replaces the subqueries with a LEFT JOIN on each table. Then to get the GROUP_CONCAT() to work, I added a GROUP BY p.id


SQL Server - Tempdb data/log file placement

If you're already certain you have IO contention, start by understanding which files are the cause.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

With the data from that query you can judge whether the log or data files are the point of contention in tempdb. It's probable you'd be better off with both data files on one drive and the log file separate, but you'll never know without the numbers to back the decision.


Should I create separate table or have the information as columns in the same table

If you really want to normalize your design, here is a suggestion to do so. You should be able to create foreign key constraints. This is without the extra Instruction table since all documents will have the instruction details, stored in the Document table:

Executor
----------
ExecutorID

Document
----------
DocumentID
ExecutorID
Deadline
Target
-- other columns, relative to all documents

DocumentA
----------
DocumentID
-- other columns, relative only to documents of type A

DocumentB
----------
DocumentID
-- other columns, relative only to documents of type B

and their relationships:

              Executor
                /1
               /
              /
             /0..N
         Document
         1/    \1
         /      \
        /        \
   0..1/          \0..1
 DocumentA      DocumentB

*Note: the only constraint not shown (and enforced) with the above design is that a document has to be of either A or B type (and not both.) This can be enforced as well, with a somewhat more complex design.


Which metrics should a product owner look at?

I cannot list everything you need to monitor for a database. The biggest factor is 'It Depends'. I am going to list a few articles that might give you a starting point and from there you can adjust.

SQL Server Monitoring Tips--I know you are not looking for how to monitor but what to monitor. If you go thru these categories and cover all you will cover most of the KPI.
Key metrics for SQL Server monitoring by Paul Gottschling
Server Performance and Activity Monitoring


How do I cast varchar to macaddr without changing client code

Shamelessly stolen from Craig's answer over SO.

You need to create a cast to make the varchar -> macaddr coercion work automatically.  It is slightly tricky, as there is no function that does exactly this, so we have to wrap an internal function into something we can use:

CREATE TABLE mac (addr macaddr);

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> ERROR:  column "addr" is of type macaddr but expression is of type character varying

CREATE OR REPLACE FUNCTION macaddr_invarchar(varchar)
RETURNS macaddr LANGUAGE SQL AS $$
    SELECT macaddr_in($1::cstring);
$$ IMMUTABLE;

CREATE CAST (varchar AS macaddr) WITH FUNCTION macaddr_invarchar(varchar) AS IMPLICIT;

INSERT INTO mac VALUES ('11:11:11:11:11:11'::varchar);
=> INSERT 0 1


SSRS 2016 report is failing with 'EXCELOPENXML' render format error

It looks like you have a couple of issues going on here.

You have the *OPENXML extension paths listed for both the legacy and new versions of both Excel and Word.  It looks like the proper extension paths for the legacy renderers are commented out in your config file.
The other issue is that you've got the *OPENXML extensions listed with the visibility flag set to false.

I think if you change the lines listed in your question to the following, all renderer options should show up in the dropdown on the subscription page:

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering">
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>

More information on the Excel renderers can also be found here.


Streaming Replication failing from standby with connection issue

On the primary server, in pg_hba.conf replace your entry with the below:

host    all  all  10.0.0.5/32  trust

Now reload the conf (for example, doing pg_ctl reload or doing a SELECT pg_reload_conf(); in the DB itself).

On the standby, check recovery.conf: it should contain the following:

standby_mode='on'
primary_conninfo=' host=10.0.0.5'
restore_command = 'cp /var/lib/postgresql/9.1/main/archive/%f %p'
recovery_target_timeline = 'latest'

Now restart the slave machine.

And if you are still unable to connect, check replication user attributes -  replication should be set there.  Use \du to check this when using psql.


MariaDB (RDS) going temporarily offline (with memory drop)

Enable the slow query log to see what is going on: https://mariadb.com/kb/en/slow-query-log/
Running a fulltext index with a minimum token length of 1 also slows things down, if not necessary: https://mariadb.com/kb/en/innodb-system-variables/#innodb_ft_min_token_size


How memory allocation happened in SQL Server

Now SQL Server is run out of memory when it required more memory, OS is going to assign more memory:

By OS I believe you mean Windows OS, if so, no windows OS does not do memory management of SQL Server it is taken care by SQLOS (Specific OS built for SQL Server) primarily which has component memory manager(MM). This MM takes care of memory allocations and interacts with Windows OS.

For the other part if you are running something which is going to consume entire 12 GB yes you may get OOM error, but its rare to see such process.

  Now here is my thought, the scheduler will check the priority of the task if the task has got the highest priority then, how the memory is going to allocate?

AFAIK SQL Server does not allocates memory as per task priority if you boost priority the task would get higher "CPU time" not memory.. The memory allocation would be done normally as it should be, subject to condition how much free memory is available.

  Is the maximum server memory parameter play a role in memory allocation in the SQL server? How important it is for memory allocation and troubleshooting?

Yes very much, Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Basically max server memory control how much maximum memory SQL Server can utilize.

NOTE: Their are some direct memory allocations which are not tracked by clerks and are done directly to windows, if such request comes this can further increase SQL Server memory ( more than specified in max server memory value).

PS: The topic is very deep it may not be completely possible to make you understand with simple answer, so I would suggest you to read SQL Server Memory Architecture Guide which I have added as a link.


MySQL: SELECT and JOIN with not exists values?

Change AND c.tar_id = 87 to WHERE c.tar_id = 87.

Your soultion would work only if it was an INNER JOIN instead.
With LEFT JOIN it works different. The filter is applied for the join predicate and the result is different.


Does T-SQL MOVE actually move the database, or just copy?

There is no MOVE command.

There is the "RESTORE" command with arguments "WITH MOVE"

This deletes the old MDF and LDF files (if database already exists) and recreate the MDF/LDF files (and NDFs etc) where specified