Performance impact of Extended Events filter [like_i_sql_unicode_string]

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture.

You are basically capturing every query that comes across the server and telling it to wait so you can first check the text of the query and then grab information through the extended event session, then letting it complete.

As well the constant activity SCOM has, depending on environment configuration, it is likely to have an extreme performance hit either with trace or extended events. I would suggest looking at the plan cache for those types of queries initially. However you might try adding more filters to your session such only grabbing a sample of data from active sessions and then also only capturing data for the particular database. That would look something like this through the GUI:

Or code your WHERE clause would look something like:

WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5)))
   AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))
   AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))

This would capture I think 20% of the active sessions for that database at any given time. However if you want all events then you would have to suffer the overhead of that action.

With it being a Microsoft product I would not be to concerned with what queries it is producing. If it is experiencing performance issues you should follow Microsoft guidelines for the product or consult Microsoft support.


How do I join a parent table to two child tables without duplication?

What you're looking to do is denormalize your data into a JSON object. This is usually not a standard thing to do from a relational database, but PostgreSQL has a cool function that makes it possible called row_to_json(). You should be able to distinctify your JSON object using that function accordingly on top of your query's result set.


Is it recommend to create a table for addresses?

If the address is a single attribute there's nothing to be gained from moving out to another table. You're not suffering from a low normal form here, since there's no functional dependency from a non key attribute to the address. Things might be different if, say, you were storing the address as a set of attributes.


SQL Cluster without a shared disk. Possible?

I am afraid that this is not possible. Clustering works on the basis of resource ownership and acquisition; so anything that is not accessible to all nodes in the cluster cannot be used.

If shared storage is not an option for you then have a look at using mirroring which does not require storage to be shared across multiple servers. You could even use a witness server to automatically fail over should the primary become unreachable for any reason:

DATABASE MIRRORING

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

SQL CLUSTERING CONSIDERATIONS

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

I hope this helps you.


What is the best practice for installing/using SQL Server Management Studio?

I recommend installing SSMS on your laptop/desktop, and connecting to servers remotely. In general, I like to minimize the number of situations that require me to RDP into a server.
(That being said, I generally install SSMS on the server at the same time I install a SQL instance. But I don't use the server installation of SSMS except for emergencies.)

Do they have to be SQL Servers?

SSMS can connect to a variety of SQL products, including the Database Engine, Analysis Services, integration Services, Reporting Services, and Azure versions of SQL. But it can't connect directly to other database systems, like Oracle, DB2, or Access. You can, however, create Linked Servers within SQL that will allow a SQL instance to talk to non-SQL databases.


SQL Server storage - evaluate the number and types of read write operations for the existing deployment

Who is right - me, Learning material, or you (your option ) ?

None of the options is correct.  The "Disk Usage Standard Report" is a database-level report that displays disk space utilization, not IO.

And what they mean by "types" of read/write operations ?

Judging that the "correct" answer was SQLIOsim, it probably meant sequential/random IO size that you can measure with SQLIO or Diskspd.
Here's what they're getting at:
When preparing for a SQL Server migration, a simple and effective migration plan is to provide equivalent-or-better resources in the new environment.  Alternatively you can attempt to measure the SQL Server resource utilization and base size the new environment off of that.
Of the two approaches the first is simpler and safer, and for a migration to an Azure VM, where you get to choose the VM family, size, and storage configuration, and where subsequent right-sizing is simple, that's the approach I would take.
Use Diskspd or SQLIO to measure the IO system, and use the system specs or standard benchmarks to measure the CPU and Memory capabilities, and spec the new enviornment from that.  Very simple, and can be done by a sysadmin, developer, or DBA.
The problem with this approach is that sometimes the existing deployment is unnecessarily fast, and results in an unnecessarily expensive new environment.  Then I would dig in with DBA and admin tools and adjust the estimate based on the actual utilization.
This happens frequently for storage where either:

There's a high-end shared SAN device providing storage for multiple servers.  You might measure a very high peak throughput on such a device, and would be unlikely to want to provide an Azure Disk configuration that matches it for each server.

SQL Server databases are stored on local NVMe flash storage and replicated with AlwaysON Availability Groups.  Storing your databases on the local flash drive is not supported for SQL Server on Azure VMs (although that's how Azure SQL Database Premium/Business Critical runs).

For that you would use something like the Azure SQL Database DTU Calculator, which will collect performance counters and create an estimate of the cpu and IO throughput required.
For IO I would start with:
Logical Disk - Avg. Disk Bytes/Read
Logical Disk - Avg. Disk Bytes/Write
Logical Disk - Avg. Disk sec/Read
Logical Disk - Avg. Disk sec/Write
Logical Disk - Disk Reads/sec
Logical Disk - Disk Writes/sec
Logical Disk - Disk Read Bytes/sec
Logical Disk - Disk Write Bytes/sec

Focusing mainly on the log and tempdb volumes, unless you see consistently significant data file read IO.  But you still have to imagine how the workload would behave if you reduced peak capacity in the new environment.  Do you really care if backup takes a little longer?  Sometimes yes, sometimes no.  Is the CPU maxed only during an overnight batch operation, and do you really care if that takes a bit longer?
The additional data collection and the need to interpret it with the understanding of how SQL Server works and in the context of the business needs of the application create significant additional complexity.


When you add a column to a table, most, if not all, queries referencing the table will need changing.  That is the cost of adding a column.

Using ? is good.  It (or the equivalent) is available in most client languages.  But... It only lets you change numbers and strings, not table names.  Numbers and strings are the flexibility you need.  A parameterized table name implies that the schema is poorly designed -- It is usually bad to have multiple tables with the same sets of columns.


How do I stop PG from wasting ids when doing ON CONFLICT DO NOTHING?

This is Working As Designed™.
Firstly, serial data types are not "real" types, they are just syntactic sugar for integer columns populated from sequences.
Then about sequences the manual says so:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values.

Subsequently (no pun intended), you cannot "stop PG from wasting ids" (even if you stop using Postgres altogether, because all other DBMSes behave in the same manner).


Converting huge ASCII file to flat and convert

When using a databse to sort the data one needs to know the structure of the data. In other words, there must be a field delimiter and a row delimiter.
Then you can import the file into a SQL-table and index it and sort it the way you want. Importing flat files is easy done with tools provided by any Database Engine, for example SQL Server Integration Services (SSIS) or MySQL LOAD.


Are scripts like sp_BlitzIndex and the others accurate on restored backups?

BlitzIndex will return some useful information on a non-production server as it is analyzing various things, some of which have nothing to do with what queries are running.  It should be clear to you when viewing the output what is relevant and what is not.

BlitzCache will not return useful information (or not much, at best) on a non-production server unless you run the same queries and load that are on the production server.

To answer your question as to "why not?", run them, review the output, and read the info at the URL returned in the URL column.  There is way too much info returned by these tools to give you a line-by-line description of why it would or wouldn't be relevant on a non-production server.


How to give title to the result of a select query in postgresql?

If you use psql, you can change the field separator using \f and \a meta commands. I show an example below:

testdb=# SELECT * FROM first;
 id | name
----+------
  1 | Ali
  2 | Sami
(2 rows)

testdb=# \f ','
Field separator is ",".

testdb=# \a
Output format is unaligned.

testdb=# SELECT * FROM first;
id,name
1,Ali
2,Sami
(2 rows)

And, \C meta command can be added any titles.

testdb=# \C 'The Query Result'
Title is "The Query Result".

testdb=# SELECT * FROM first;
The Query Result
id,name
1,Ali
2,Sami
(2 rows)


If the overhead you are concerned about with the local SQL installs is a licensing overhead, and the database is smaller than 10Gb, then you could instead install Express edition which is free (but has the 10Gb limit). If the DB is a little over 10Gb then you could restore to the server, compress enough tables & indexes to make up the difference, then backup and restore that locally.
There is no copy-on-write support in SQL Server itself. If you are running it on Linux you could perhaps try using LVM snapshots to try acheive this:

Drop the existing DBs if present
Drop the LVM snapshots if present from last time
Backup the DB and restore it with the data and log files on the reference LVM volume
Detach (sp_detach_db) the restored copy
Make enough read-write LVM snapshot volumes of the volume now containing the updated DB copy
Attach the DB from each of the snapshots (sp_attach_db)

If your storage subsystem is a SAN with RW-snapshot features than you could potentially use a similar method with that.
I would not recommend this, or other options with 20 copies of the DB on the same instance and drive(s), if the users might pose much load - they will all compete for IO, they will all compete for buffer pool memory, and while the on-disk data is CoW the SQL instance will not know this so will happily hold duplicate copies of unmodified data in memory.


Select * on inner join with common columns

You need to specify every column by your self.

Example : Table Country and City both have a name column

SELECT
    a.Name AS CountryNane, b.Name AS CityName, c.*
FROM
    Country a
        JOIN
    City b ON a.Code = b.CountryCode
        JOIN
    CountryLanguage c ON a.Code = c.CountryCode;


Insert to a table using function with default arguments

You need to remove the clause STRICT according to the documentation :

  ...RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically...

Use your code like this:

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                          , opt_arg1 varchar DEFAULT NULL
                                          , opt_arg2 varchar DEFAULT NULL) RETURNS void AS
$$
BEGIN
      INSERT INTO pl_table (mandatory, option1, option2) VALUES
             (mand_arg, opt_arg1, opt_arg2);
END;
$$
      LANGUAGE plpgsql ;


Why merge replication wants to replicate view before replicating related table?

I don't know if this apply to your issue, but according MS-DOCS about this error:

Error number: MSSQL_REPL-2147201001

Maybe it is not about trying to replicate a VIEW before the TABLE, but another reasons.

Quoting from docs: (Bold is mine)

  Explanation

  COM object initialization failed for an XML Subscriber. Some reasons
  why merge replication did not apply schema changes to the Subscriber
  include the following:

  A failure to create a directory to write the temporary snapshot files.
  A failure to enumerate schema articles.
  For SQL Server Compact Subscribers, a failure to reinitialize the
  subscription.
  If the object is message based, a failure to write to the message
  file.

Perhaps for some reason it can't enumerate the schema properly, and then it is trying to replicate the view before the table.

You can have a look at errors log in this way:

  User Action

  Run SQL Server Profiler and examine replmerg.log for
  failures. If you are using Web Synchronization, elevate the severity
  of the websync log, rerun the scenario, and check for errors in the
  websync.log file.


You have to shrink database so the deleting the data takes place
Right Click on your database , Tasks => Shrink => Files
In shrink action choose the second Option and enter your file ideal size
You're done
:)


Migrating from MySQL RDS to Aurora RDS with CNAME record

As far as i know you can not replicate Aurora (slave ) from EC2 (master), please correct me if I am wrong. Due to the super grant lack required to do the "Change Master". I would recommend to use DMS (databawse migration server) for that task.

migration steps:

set route53 TTL to 1 min
set read_only in current master, to make sure no more reads go into the master.
wait for all transactions go into the new master.
Stop DMS replication
change CNAME
...

Pay special atention if your application uses sticky connections, if so you can stop the old master completely to make sure all traffic goes into the new one.


Reducing deadlocks with update query hint or other method

I resolved the deadlocks by using a NOLOCK hint in the first select statement.
No dirty reads as this is the only process that manipulates the rows and only does so once.


How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?

I don't think you can restrict this by the choice of the datatype.

However you can create a check constraint that ensures that no value is inserted that could not be represented in your programming language:

alter table foo
   add constraint check_type_range check (int_column < 32767);

You will have to replace the actual value with the one that matches the chosen datatype (I don't do C, so I don't know what those limits are).


Heaps are tracked in sys.indexes and sys.dm_db_index_usage_stats with index_id=0.  Try

select
   db_name(database_id) database_name,
   object_name(object_id,database_id) table_name,
   s.*
from sys.dm_db_index_usage_stats as s
where index_id = 0

You should see your heap tables.


How to create aggregate function, like sum, with an implicit DISTINCT?

Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?

Yes, it is possible — you need a User-defined Aggregate, such as this:

create or replace function f_sum_distinct (numeric[], numeric) returns numeric[]
    language sql as $$
select $1||$2;
$$;

create or replace function f_sum_distinct_final (numeric[]) returns numeric
    language sql as $$
select sum(v) from (select distinct unnest($1) v) z;
$$;

create aggregate sum_distinct(numeric)
( sfunc     = f_sum_distinct
 ,stype     = numeric[]
 ,finalfunc = f_sum_distinct_final
);

with w(v) as (select 2 union all select 2 union all select 3)
select sum(v) "Plain SUM", sum(distinct v) "SUM(DISTINCT)", sum_distinct(v) "SUM_DISTINCT" from w;

/*
|Plain SUM|SUM(DISTINCT)|SUM_DISTINCT|
|--------:|------------:|-----------:|
|        7|            5|           5|
*/

dbfiddle here

Note though (thanks @Erwin), that performance is going to be very substantially worse than the built-in aggregates. If this matters you will have to consider writing the helper functions in C, which is much more of an undertaking.


You have your terminology a bit wrong which makes it hard to tell what exactly you mean by your 2 options but you should use the OrderByAttribute (which is what I think you mean by option 1). The OrderByAttributeis meant to be used for exactly that, ordering the attributes by something else than their Key or Value.

If by option 2 you mean you create the attribute using the DayOfWeek column (essentially creating an attribute with a KeyColumn of DayOfWeek) and then set the NameColumn to DayName you would in this case essentially have the same end user experience but that is not what those attributes are meant to do.

The KeyAttribute should be used to define which members are unique and the NameColumn should be used to define what you want to be shown to the users.
Each of these properties have their own purpose and you should use them as such.

Abusing KeyColumn to define an order will get you in trouble when you will actually need that property to resolve issues with duplicates and so on.

Some excerpts from the documentation:

  KeyColumns:   Contains the column or columns that represent the key for
  the attribute, which is the column in the underlying relational table
  in the data source view to which the attribute is bound. The value of
  this column for each member is displayed to users unless a value is
  specified for the NameColumn property.

  NameColumn    Identifies the column that provides the name of the
  attribute that is displayed to users, instead of the value in the key
  column for the attribute. This column is used when the key column
  value for an attribute member is cryptic or not otherwise useful to
  the user, or when the key column is based on a composite key. The
  NameColumn property is not used in parent-child hierarchies; instead,
  the NameColumn property for child members is used as the member names
  in a parent-child hierarchy.

  OrderBy   Describes how to order the members that are contained in the
  attribute hierarchy. The default value is Name, which specifies that
  ordering of the attribute members is based on the value of the
  NameColumn property, if any. Otherwise, members are ordered by the
  value of the key column. The available options are as follows:


Postgresql Function Trying to Insert into 1 row instead of 2

I have tried it with this function, it works fine for me.

create or replace function stats_test() returns numeric as $$
  begin
    insert into stats(ave_age, stdv) select avg(age), stddev(age) from "T1";
    return 1;
  end;
$$ language plpgsql


As stated in the referenced answers, SQL Server will use as much memory as you let it use. Additionally, SQL Server uses memory for a variety of things that are isolated to the domain of "data storage." In addition to the things that mrdenny noted, it also caches the query plan of a query

In SQL Server, memory use is tracked by Memory Clerks. Take a look and see how many there are:

SELECT *
    FROM sys.dm_os_memory_clerks;

These can be roughly broken down into four categories:

General ("Default") - These are the ones that start with "MEMORYCLERK_" (buffer pool is tracked in here so that's where your "buffer pages" query is looking)
CACHESTORE - "CACHESTORE_" objects. This is where query plans are monitored and stored.
USERSTORE - "USERSTORE_" objects. Database metadata and permissions are monitored and stored.
OBJECTSTORE - "OBJECTSTORE_" objects. Internal operations are handled here.

How SQL Server coordinates requests for memory is through what's called a Memory Broker.

SELECT *
    FROM sys.dm_os_memory_brokers;

These brokers are in charge of managing the memory. The CACHE broker will be the first to release memory to system when pressure is encountered. What this means is that your query plans will be evicted from memory before your data pages are flushed from the buffer pool. (Most of the above taken from my notes from Bob Ward's memory presentation at PASS Summit 2013).

This is just a brief overview. There's more to it than just this, but the reality is that there are so many things going on in your system that it's not quite correct to assume that SQL Server isn't handling memory properly just because the size-on-disk value of your database isn't less-than-or-equal-to the value of buffer pages*8/1024.

Here's another query that lists buffered objects, just for fun.

        SELECT

            object_sysname = OBJECT_NAME(p.[object_id]),
            index_id = p.index_id,
            buffer_size_mb = COUNT(*)/128.,
            buffer_count = COUNT(*),
            compression_type = p.data_compression_desc,
            allocation_type_desc = a.type_desc,
            rows = p.[rows]
        FROM sys.allocation_units AS a
            INNER JOIN sys.dm_os_buffer_descriptors AS b
                ON a.allocation_unit_id = b.allocation_unit_id
            INNER JOIN sys.partitions AS p
                ON a.container_id = p.partition_id
        WHERE b.database_id = CONVERT(int,DB_ID())
            AND p.[object_id] > 100
        GROUP BY p.[object_id], p.index_id,
            p.data_compression_desc,
            a.type_desc, p.[rows]
        ORDER BY buffer_count DESC

(A modified version of Glenn Berry's DMV query).

To see what you're allowing SQL Server to use for the buffer pool (including all page allocations) and CLR allocations, you can run this query:

SELECT *
FROM sys.configurations
WHERE name = N'max server memory (MB)';

OR

sp_configure N'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure N'max server memory (MB)';
GO

A config_value of this (2147483647) means that you're allowing SQL Server to use everything it can get. This may lead to resource starvation on the OS which will, in turn, start taking memory back and swapping to the page file - a nasty scenario. Glenn Berry (again) has a good recommendation for choosing an appropriate setting for max server memory (though it's for up to 2008 and I've not found good guidance for >= 2012).

Additionally, since you've noted that you're testing in SQL Server 2014, there is also the possibility of extending the buffer pool to disk (only if you have SSD storage attached).


This likely needs a recursive CTE, since you want the value of the column to depend on the value of the previous (calculated) value of the same column.
(Recursive CTEs are available in MariaDB since version 10.2.2)

I assume that (date) is unique, otherwise the result will be non-deterministic:

WITH RECURSIVE
  t AS
  (
    SELECT
        date, col2,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM
        tableX
  ),
  cte AS
  (
    SELECT
        date, col2, rn,
        CAST(1 AS DECIMAL(10,3)) AS col1     -- starting value
    FROM
        t
    WHERE
        rn = 1

    UNION ALL

    SELECT
        t.date, t.col2, t.rn,
        (c.col1 + 1) * t.col2                -- calculate col1
    FROM
        t JOIN cte AS c
        ON t.rn = c.rn + 1
   )
SELECT date, col2, col1
FROM cte ;

Tested at: dbfiddle.uk


SQL Server - Defining and populating a new temp table column fails only once

When a stored procedure is executed for the first time, the query
  processor reads the text of the stored procedure from the
  sys.sql_modules catalog view and checks that the names of the objects
  used by the procedure are present.

Deferred Name Resolution and Compilation

That explains the observed behavior.  On the first call, the stored procedure body cannot be parsed and compiled because it references an object that doesn't exist: Column2.

When you create Column2 before invoking the procedure it will parse, compile and run.  Then in the third invocation the stored procedure has a cached plan, and doesn't need to be parsed again.  If you call dbcc freeproccache before Case_3 it will fail too.

And so the answer to:

  How can I add a new column to a temp table dynamically in a stored procedure and populate it with some values, without resorting to dynamic SQL?

Is that you can't and shouldn't.  It's a very strange thing to even attempt.

You can normalize the the temp table and then use it in a subsequent stored procedure.  Like this:

CREATE or alter PROCEDURE TestAddTempTableColumn AS
BEGIN
    IF NOT EXISTS
    (--don't require all callers to have their table declarations updated
        SELECT 1
        FROM tempdb.sys.columns
        WHERE
            object_id = OBJECT_ID('tempdb..#TestTempTable')
            AND name = 'Column2'
    ) ALTER TABLE #TestTempTable ADD Column2 VARCHAR(MAX) NULL

    exec TestAddTempTableColumn_impl
end

go
CREATE or alter PROCEDURE TestAddTempTableColumn_impl AS
BEGIN
    INSERT INTO #TestTempTable(Column1, Column2)
    VALUES ('column1 value', 'column2 value')
END

But it strikes me as somewhat surprising to have code that creates a temp table, calls a stored procedure to load that temp table, and have the procedure alter your temp table.


Join mysql tables to find siblings and get their notifications

I am not sure I really get your purpose, but this is what would gives the answer you're looking for:

SELECT
    company.ID, company_notes.NotesID
FROM
    company
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;

If you use a DB that implements WITH, this would show it:

WITH company (ID, parentID, value) AS
(
VALUES
    (1, 0, 22020),
    (2, 1, 23423),
    (3, 2, 22222),
    (4, 2, 23423)
),
company_notes(ID, FromID, ToID, NotesID) AS
(
VALUES
    (1, 1, 2, 33)
)

SELECT
    company.ID, company_notes.NotesID
FROM
    company
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;


Actual Query Plan Estimated Cost and dm_exec_query_stats worker time do not gel

The estimated cost is a unit-less measurement for the query cost as estimated by the SQL Server query optimizer. Just because query A has a lower estimated cost than query B does not mean that query A will be more efficient than query B. The query optimizer makes assumptions about your hardware, current workload, and data that may not exactly match your system. There may be model limitations or SQL Server may not have access to perfect information. Even on a well-configured server with well-written queries you will not almost certainly not see the estimated query cost correlate exactly with CPU time. Keep in mind that the query cost includes more factors than just CPU time as well.

The estimated cost that you see in your actual query plan is actually still an estimate. It will have the same value as the estimated cost from an estimated plan if the plan shape does not change due to a recompile. Consider the following simple query:

SELECT t1.high AS high, t2.high AS high2
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (FAST 1);

For the estimated plan I get a total estimated cost of 0.0065704. For the actual plan I still have an estimated cost of 0.0065704 even 6431296 rows were returned instead of the single estimated row. The actual plan was uploaded to Paste the Plan if you want to take a look.

If I had to guess why your query was slower I would start with the index spool at node 42:

It's odd that your query now has an index spool after you added an index. You may have created an index with the key columns in the wrong order. Look at the code in fn_get_samples and evaluate if your index is truly the best one. Sometimes the indexes suggested to you by SSMS may suggest a poor ordering for the key columns. You could also consider adding the NO_PERFORMANCE_SPOOL hint to your query but that's probably not the right corrective action to take.


Is it possible to block a specific query, or return a static result?

As far as I can tell the answer to this question is no.  You can't block this SELECT at the database level, or rewrite it in versions before 5.7.6 (dev milestone 16 for the 5.7 release).

If the offending statement were an UPDATE, INSERT, or DELETE then you could use the tactics described in this StackOverflow answer to nullify the statement via a trigger.

As established in this answer (also on StackOverflow), you can't put a trigger on a SELECT statement.

Given the above is true, you will have to do one of three things:

Put a proxy like MySQL Proxy (that proxy was discovered by Dr. McKay) between the database and the application that will replace the query and return a cached/static result as suggested by jkavalik initially.
Wait for your support ticket to work it's way through WHMCS's bug system.
Figure out how to optimize this query until it isn't a problem anymore or option 1/2 are in place.

We can't help with option 1 or 2, but we can help you optimize your query.  Optimizing the query may bring the execution time down from tenfold, which will lessen your organization's pain, and make you look like a hero.  I vote you take this route while you are waiting for the bug ticket if you can't install the proxy or get it working.


Query To Show YTD Total Summing Each Month Prior To Current

It is always a bad idea to save Dates Month as rtext mysql really has issues with that
Your approach what very good, but i had a problem to get the right order

with MonthAgg as (
select IFNULL(spendType,'Total') spendType
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select DISTINCT spendMonth , Sum(spendAmt) Over(PARTITION by spendMonth  order by MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y"))) Amt
FROM empSales
)
,mycteYTD as (
select spendMonth , sum(Amt) Over( ORDER BY MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y"))
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'ytd' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD
, max(Case when spendMonth='May' then YTD else null end ) MayYTD
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD
from mycteYTD

spendType | January | February | March | April |  May | June | July | August | September | October | November | December
:-------- | ------: | -------: | ----: | ----: | ---: | ---: | ---: | -----: | --------: | ------: | -------: | -------:
Barb      |    4.13 |     4.13 |  7.13 |  null | null | null | null |   null |      null |    null |     null |     null
James     |    1.00 |     3.00 |  2.00 |  null | null | null | null |   null |      null |    null |     null |     null
Richard   |    3.28 |     3.28 |  5.28 |  null | null | null | null |   null |      null |    null |     null |     null
Total     |    8.41 |    10.41 | 14.41 |  null | null | null | null |   null |      null |    null |     null |     null
ytd       |    8.41 |    18.82 | 24.82 |  null | null | null | null |   null |      null |    null |     null |     null

db<>fiddle here


Postgres database insert become slow after 10 days, and then it needs a full vacuum

If performance is restored after a VACUUM ANALYZE, I would question whether the table(s) in question are being auto_vacuumed and auto_analyzed.
If you look at the pg_stat_user_tables view, do the values for last_autovacuum and last_autoanalyze seem reasonable?
If tables are constantly being locked then auto vacuum might not be able to run.


You might wish to check out this article on join methods and how DB2 chooses which one when.

Based on this documentation (which is for DB2 8 and I'm assuming it is similar for 9+, though not necessarily), nested loop joins are always available to the optimizer to use. DB2 can select merge sort and hash as the optimizer level is upped from 1 and other things are present (read the article to see which).


Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step

You can try using the maintenance plans under management folder. There are Execute SQL Server Agent Job task and other task that fit your need. you can design the plan in the way that after a step completion, it run multiple tasks like shown below.


"freeing items" taking forever on inserts, updates and deletes

My knowledge of MySQL is somewhat limited so I may be entirely wrong here, but this is me thinking through your issue given my experience with MySQL w/innodb and other MVCC db's.

If you are seeing processes or threads waiting on eachother it has to be a locking issue.  That's the only explanation.  Given MySQL's structure, it has to be a thread locking issue.  There are no other options.

This leads to a few specific questions:

How is performance writing the log files?  Any issues there?
I there something else going on?  Any deadlocks being reported?
What does top show regarding CPU time?  Sleeping, idle, or wait?
What sort of write concurrency are we talking about?  How many concurrent writers?


Here's what I have understood from your question :

Your Full backup taken yesterday failed to restore on your TestingDB. Hence, you took a latest full backup and that got restored successfully. After this you are trying to restore a differential backup from yesterday.

This will not work. Here's the reason it will not work. Everytime a backup happens let it be FULL\DIFF or LOG. it generates an LSN, this LSN is a chain and unique number which matches with each other and are compared at the time of restore.

If you have taken latest FULL backup then now you should take the latest diff backup and try to restore it on TestingDB and you should not get any errors you received earlier. Also, while restoring you can check the LSN of Full and Diff backup files by using RESTOREHEADERONLY  command which reads the headers of your .bak file and gives you the information about First and Previous LSN.

I hope this helps in solve your problem.


Validation logic for Mobile Number Column in SQL Server

I seriously doubt the real world utility of the specified validation rules but a reasonably concise way of validating them is below.

SELECT PhoneNumber,
       Valid = CASE
                 WHEN PhoneNumber LIKE CONCAT('[23456789]', REPLICATE('[0123456789]', 9))
                      AND PhoneNumber LIKE CONCAT('%[^', LEFT(PhoneNumber, 1), ']%')
                   THEN 'valid'
                 ELSE 'invalid'
               END
FROM   (VALUES ('0123456789'),
               ('1144684679'),
               ('5555555555'),
               ('5555555554'),
               ('9876543210'),
               ('9876')) PhoneNumbers(PhoneNumber)

+-------------+---------+
| PhoneNumber |  Valid  |
+-------------+---------+
|  0123456789 | invalid |
|  1144684679 | invalid |
|  5555555555 | invalid |
|  5555555554 | valid   |
|  9876543210 | valid   |
|        9876 | invalid |
+-------------+---------+


Searching SSIS packages for the use of specific tables

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column.

Finding SSIS Packages having References to a Table or Column will help you.


In SQL server is a check constraint applied before or after a default constraint on insert?

The check constraint is applied after.

You can see this from the fact that the following succeeds.

DECLARE @T TABLE(X INT DEFAULT 1 CHECK (X = 1));

INSERT @T DEFAULT VALUES;

Also the  execution plan shows that the assert operator operates on the values output from the table insert operator so is checking the values that were actually inserted.


Looking for a Trigger to change inserting strings?

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below

This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger
ON  table1
AFTER INSERT
AS
BEGIN
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger
ON  table1
AFTER INSERT
AS
BEGIN
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END


Show All online SID Database Oracle 11 in Linux SuSe and Find Database file

cat /etc/oratab

As a DBA, you need to learn the Oracle Data Dictionary.

"One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database"
SQL>select name from v$datafile;

# set environment
. oraenv <<< SID1;
# login as sys user (IPC)
sqlplus / as sysdba
# list datafiles
SQL>select name from v$datafile;
SQL>exit
# repeat for SID2 and SID3

Side note.
One Oracle Database System (SID) can serve multiple databases (schemas). It is expensive, in terms of resources, to have multiple systems running.
# create database 1
SQL>create user DB_OWNER_1 identified by 'pass';
SQL>create table db_owner_1.t (x char(1));
# create database 2
SQL>create user DB_OWNER_2 identified by 'pass';
SQL>create table db_owner_2.t (x char(1));

# login as 'app user A' who has privileges to manipulate tables (DML) in database 1.
sqlplus appuserA/pass@TNS_ALIAS_1
SQL>alter session set current_schema = "DB_OWNER_1"
SQL>insert into t values ('x');

# login as 'app user B' who has privileges to manipulate tables (DML) in database 2.
sqlplus appuserB/pass@TNS_ALIAS_2
SQL>alter session set current_schema = "DB_OWNER_2"
SQL>insert into t values ('x');

You now have two applications operating on two different databases served by the one and same Oracle Database System.
Best of luck!


Does CREATE/DROP VIEW require a SCH-M Lock

This is easy enough to test on your own with a dummy table. It doesn't have to be anything fancy.

CREATE TABLE dbo.t1 (id INT)
INSERT dbo.t1 ( id )
VALUES ( 1 )

After the table is created, start a blocking transaction.

BEGIN TRAN
UPDATE dbo.t1 SET id = 2

--ROLLBACK

Over in another SSMS window, create a view.

CREATE VIEW dbo.YourMom
--WITH SCHEMABINDING /*this makes no difference*/
AS
SELECT *
FROM dbo.t1

This completes successfully, immediately, but trying to query the view or table will be blocked.

SELECT *
FROM dbo.YourMom

SELECT *
FROM dbo.t1

It's worth noting that views don't have any special properties. They're just a query.

I'm not sure why your vendor creates and drops them as part of a process, unless the definition needs to be dynamically generated because you have custom tables or something.


Difference between OCCI and Instant Client package Oracle

I googled 'occivc9win32_111060.zip' and found "OCCI Downloads - Oracle" :
http://www.oracle.com/technetwork/database/occidownloads-083553.html

Maybe you have to register for the download.


debugging an ssis package which is invoked from a .net application?

Your SSIS package would be stored as a .dtsx file, or perhaps in the MSDB database. Either way, you should be able to locate it, open it in a copy of BIDS that you run as the user that executes the application, configure BIDS to have the same set of conditions as how you call the package, and then debug it from there.


Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?

Yes, it is syntactically correct.

The ON clause can be any boolean expression - i.e. as long as it results in a boolean value (TRUE, FALSE, UNKNOWN).

Note that you have 3 tables that are visible in that ON clause: employee, department and payroll. You'll get an error if payroll_type is a column in more than one of them.


How to set the grow rate of database in SQLserver

Right-click on your database and choose Properties.  Then choose Files.  This will allow you to change the Initial size as well as Autogrowth and file size limits for your data and log files.

You should also enable Instant File Initialization (if possible).

Check this link for some excellent guidance on autogrowth.


I created a DB schema for a project relating to collecting comments for web pages, is relational the way to go and how are my relationships?

A couple of cmments:

CommentReplies seems to be implementing many-to-many.  I would expect it to be one-to-many -- as in, each comment has one 'parent'.  One-to-many does not need an extra table; you simply have parent_id as a column in Comments.

Consider using ENUM datatype instead of adding a XxTypes table.


If I understand what you are asking then like this.

WHERE DateModified BETWEEN @BeginDate AND @EndDate
AND  (@Troop IS NULL OR d.CreatorLEA = @Troop)


Dynamically change column names in query in PostgreSQL

I've changed v_id from numeric to int, because in your sample data id is integer.
Them IMHO you don't need to use USING, you can simply format your sentence and execute it.
Finally you should escape single quotes around last 0.
CREATE OR REPLACE FUNCTION return_at_query(p_column text)
 RETURNS TABLE(at_id character varying, v_id int)
 LANGUAGE plpgsql
AS $function$
declare
    ret text;
begin
       RETURN QUERY EXECUTE
       FORMAT('SELECT %s, id FROM tab1 WHERE %s <> ''0''', p_column, p_column);
end;
$function$;

select return_at_query('at_11')

| return_at_query |
| :-------------- |
| (High,3)        |

db<>fiddle here


What is the differences between to and = in set search_path?

There is no difference.

If you look at the manual one is the alternative to the other. { TO | = } means either TO or =

The meaning of that is explained at the start of the manual

  and vertical lines | indicate that you must choose one alternative


There are times when you may want to take any number of hosts from Domain A and Peer to Domain B. The most common example is moving hosts between development environments. By default our Durable Domain state will prevent this from happening because the new host is a known entity in the currently defined domain.

The typical error message that is received in the agent log on the peering host is:

SEVERE PeerService$EntryListener.messageArrived (serv-socket5-thread-2)
Failed to set ServerStateUUID
java.lang.IllegalArgumentException: UUID was already set

To resolve this execute the following:

Ensure that your host's default.properties files is updated with the new peer value (IP or DNS).

Stop / Start the nuoagent service with the --reset-broker-state flag.
It's important that you do this only once otherwise you will be resetting your domain state with each restart.

$ sudo service nuoagent stop

$ sudo service nuoagent start --reset-broker-state


How can I create a Foreign Key to a Compound Primary Key with a single column?

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK
  ADD CONSTRAINT [FK__OtherTable__Blah]
    FOREIGN KEY (FieldName, Blah)
    REFERENCES Lookup (FieldName, Value) ;

Tested at SQL-Fiddle.

The table can then be used as if the FieldName does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2

The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK
  ADD CONSTRAINT [FK__OtherTable__Blah]
    FOREIGN KEY('This', Blah)
    REFERENCES Lookup (FieldName, Value) ;

Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.

For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

If you really don't want to  use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)


calculate the difference between two column aliases in a SELECT statement?

OK. So I am going to go out on a bit of a limb here. I may have misunderstood the data model in the OP but as I understand it:

1) tasks holds a list of unique tasks

2) TA.task_average = the average time in minutes that a task takes to complete

3) users holds a list of unique users

4) summary represents users attempts at the tasks along with how long each attempt took to complete (through reference to the minutes table)

Gross assumptions out the way here is what I've come up with:

Firstly to get a list of all users, how long they took to complete each task in each attempt and the difference between each user's attempt and the task average:

select U.user_name,
TA.task_id,
TA.task_average,
M.minutes,
M.minutes-TA.task_average as diff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id;

then aggregating that up to get the average difference from the task average for each user (which I think is what the OP was asking for):

select U.user_name,
avg(M.minutes-TA.task_average) as avgDiff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id
group by U.user_name
limit 0,30;

If I have then wrong end of the stick then please ignore this. Thought I'd post it up anyway in case it is useful.


Would appreciate some suggestions on how I should model this data

NOTE to people with itchy downvote fingers: I know that OP has asked about MongoDB and the answer that follows is RDBMS.  However, if you check out the comments you'll see that I did ask why MongoDB and OP's answer is a presumption of necessity due to performance.  Since nobody has come forth with a Mongo-centric answer in four days, I am going to offer my suggestion, which is to let RDBMS do what it's good at.

Raladical, as I noted in my comments to your question, I think that what you are trying to build is perfectly suited to the relational data model.  Here is a quick data model sketch:

This model makes the following assumptions:

Users can have exactly one library.
Users can have many playlists.
Songs can appear in any number of playlists.
Users can only put songs in a playlist if it's already in their library (Note that there is an easy way around this if that is not your intention.)
Depending on the constraints you put on LIBRARY_ITEM and PLAYLIST_ITEM you can have the same song exactly once or several times in both the library and any given playlist.

You can also easily extend this model to normalize for things like ARTIST and possibly even location, if that makes sense in your application.

This model is normalized (3NF) and if you index it properly it will be very performant.  If you really want MongoDB or if there is something else about your system that is well suited to non-relational then you can definitely do it that way.  If not, you should think about RDBMS for this one.


How to monitor InnoDB Disk I/O using Server Status Variables?

Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time.

Additionally, MySQL may write to the mysql binary, general or slow log, or use certain temporary structures for pending transactions, binlog, tables for intermediate results, etc. if they do not fit in memory. Also, bear into mind that if a query involves a temporary table, it can show I/O activity in other engines, as it may create such tables in MyISAM format.

Depending on the configuration and change, a write to InnoDB may cause 0 writes to the transaction log (for example, if it has not yet been synced, or no page was modified) or several (at least one per page modified). Even if you only update 1 record, it may case the modification of several pages (e.g. because a blob is involved, or an index have to be updated). You can check the size of the writes to the transaction log by monitoring the InnoDB Log Sequence Number. Under a strict "flush log at transaction commit", the writes to the log are the only thing that is done to disk, all other changes are tried to be buffered on memory and are written at any future time.

While somehow inaccurate, InnoDB has a section in File I/O in its SHOW ENGINE INNODB STATUS command, for a more global view. As @Ela says, innotop is a good summary tool for monitoring InnoDB operations, including the former command.
Yes, index changes can be buffered, as any writes. Not only the index pages, but index updates are usually heavily buffered through the change buffer and merged later (more than normal updates, as they involve reading data that may not be on cache). Check the INSERT BUFFER AND ADAPTIVE HASH INDEX section on the previous INNODB STATUS command.

In a nutshell, InnoDB is a complex "set of buffers and asynchronous reads and writes", and there is never a guarantee that we can stop all I/O operations (some of them only kick when the server is idle). This is a good introduction material to InnoDB architecture and operations: https://www.percona.com/live/london-2013/sessions/innodb-architecture-and-performance-optimization


Indexing the columns that only have yes and no values (cardinality of 2) would never get use. You would quickly subject table1 to table scans. Index merges would be out of the question : See Combining columns in index

In the given table, note that you could separate the clothing types from the person

Here is your table. Let's call it person

CREATE TABLE person
(
    id INT NOT NULL AUTO_INCREMENT,
    state CHAR(2),
    jacket CHAR(3),
    coat CHAR(3),
    shoes CHAR(3),
    raincoat CHAR(3),
    sweater CHAR(3),
    pants CHAR(3),
    PRIMARY KEY (id),
    KEY (state)
);

Let's create two tables

Here is one to hold types of clothing

CREATE TABLE articles_of_clothing
(
    id INT NOT NULL AUTO_INCREMENT,
    article VARCHAR(20),
    PRIMARY KEY (id)
);
INSERT INTO clothing_types (article) VALUES
('jacket'),('coat'),('shoes'),('raincoat'),('sweater'),('pants');

Here is one to hold types of clothing associated with a person

CREATE TABLE clothing
(
    id INT NOT NULL AUTO_INCREMENT,
    person_id INT NOT NULL,
    article_id INT NOT NULL,
    PRIMARY KEY (id),
    KEY person_article (person_id,article_id),
    KEY article_person (article_id,person_id)
);
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='jacket') B
WHERE A.jacket='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='coat') B
WHERE A.coat='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='shoes') B
WHERE A.shoes='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='raincoat') B
WHERE A.raincoat='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='sweater') B
WHERE A.sweater='yes';
INSERT INTO clothing (person_id,article_id)
SELECT A.id,,B.id FROM person A,
(SELECT id FROM articles_of_clothing WHERE article='pants') B
WHERE A.pants='yes';

Let's remove the clothing columns from the person table

CREATE TABLE person_new LIKE person;
ALTER TABLE person_new DROP COLUMN jacket;
ALTER TABLE person_new DROP COLUMN coat;
ALTER TABLE person_new DROP COLUMN shoes;
ALTER TABLE person_new DROP COLUMN raincoat;
ALTER TABLE person_new DROP COLUMN sweater;
ALTER TABLE person_new DROP COLUMN pants;
INSERT INTO person_new (id,state) SELECt id,state FROM person;
ALTER TABLE person RENAME person_old;
ALTER TABLE person_new RENAME person;
DROP TABLE person_old;

Now that they are separated, let's look at your queries. We'll use just the first one.

SELECT * FROM table1 WHERE pants = 'yes' AND sweater = 'yes';

How can you accomplish this under the new design? Do it in stages

Create a Table to collect person_ids from each pass
Query the clothing table for all person_ids that have pants and collect them
Query the clothing table for all person_ids that have sweater and collect them
Count how many have person_id written twice
Join all person_ids found twice back to person table

Here are those steps

CREATE TEMPORARY TABLE queryids (person_id INT NOT NULL,KEY (person_id));
INSERT INTO queryids
    SELECT B.person_id FROM
    (SELECT id article_id, FROM articles_of_clothing WHERE article='pants') A
    INNER JOIN clothing USING (article_id) B
;
INSERT INTO queryids
    SELECT B.person_id FROM
    (SELECT id article_id, FROM articles_of_clothing WHERE article='sweater') A
    INNER JOIN clothing USING (article_id) B
;
SELECT B.* FROM
(
    SELECT COUNT(1) rcount,person_id
    FROM queryids GROUP BY person_id
    HAVING COUNT(1)=2
) A LEFT JOIN person B;

Since you only have 6 articles of clothing this may not be the best solution. If you have over twenty articles of clothing, the Query Optimizer stands a better chance of using indexes. I say this because when the Query optimizer has to read more than index entries for more than 5% a table, the Query Optimizer gives up an does a full table scan.

I have given this rule-of-thumb as a root cause to many index searching misadventures:

Dec 11, 2012 : How do I force a JOIN to use a specific index in MySQL?
Jun 18, 2012 : Why does MySQL only sometimes use my index for a range query?
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Mar 22, 2012 : Why does MySQL choose this execution plan?
Jan 18, 2012 : MySQL status variable Handler_read_rnd_next is growing a lot
Oct 20, 2011 : In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Jul 12, 2011 : MySQL very slow query when changing one WHERE field despite no index/key


Postgres Identifying 2 records with 1 attribute equal and 1 attribute not equal

This could be a way:

select
    ID
from
    t t1
where exists (select 1
              from   t t2
              where  t2.A = t1.A
                     and t2.B <> t1.B)
order by
    ID;

It could be read as: Give me all ID's if exists another row with same A and different B.

| id |
| -: |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |

db<>fiddle here


Duration of adding versus dropping a column

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered.

(This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ordinary data types.)

On the other hand, when you physically drop a column (rather than hide/set unused), all the rows need to be at least inspected to check if any data needs to be removed. So the theoretical best case cost is a full table scan. (There's some table locking involved too I believe.) Worst case all rows have to be rewritten. This can be quite expensive.


Can insert on tables with FK impact operations on other tables that have FKs to the same table?

My attitudes in this area:

Make transactions so fast that there are rarely any conflicts.
Use SELECT ... FOR UPDATE to "lock" rows that I am about to update.  (Unless there is a way to avoid them.)
FKs imply extra checks.  These take time.  And the checks are usually redundant.
I don't use FK CASCADE since I like to see all the interactions in the same code -- namely the code between BEGIN and COMMIT.
I don't use non-cascading FKs; I debug my code instead.
I do use INDEXes and I want to know exactly what indexes exist and not be surprised by extra (redundant?) ones created by FKs.
Do check for errors after every SQL statement.  These can be surprise deadlocks.
Check for errors even after COMMIT.  This is in case the app is ever migrated to Galera or InnoDB Cluster.
Restart at the BEGIN if there is a deadlock.
Beware of "burned" AUTO_INCREMENT ids.  (That's a lengthy discussion of its own.)

Back to your question.

There are cases where InnoDB takes a stronger (eg, X vs S) lock than it needs to.  This may be because of an edge case that is too costly to check for.  Your example may (or may not) be an example of such -- I don't have enough brain cells think it through at the moment.  I go back to my first bullet item -- make the transaction fast enough so the problem goes away.


If you have a fixed number of pivot points, you could do what McNets suggests in the comments:

Change your select * by select isnull([1],0), isnull([2],0), ..

For a variable number of pivot points, you can use dynamic SQL - here is an example:

Note the references to colCoalesceNull

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT
    'Q' +
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) +
    CONVERT(VARCHAR(4), year([DatePaid]))
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;


Depending on what Database you are using, you should be able to cast the string to a date and then do your comparison.

i.e. in Sql Server

cast(date as datetime) >= '20150131' ...

That said, I'd typically recommend storing the date as a date if at all possible, and if you do need to use strings, It'd be best to use a more standardized format

i.e.
'YYYY-mm-dd' or even better 'YYYYmmdd' instead of 'mm/dd/YYYY' which, for example, can easily be mixed up with 'dd/mm/YYYY' which is common in many cultures. See this blog post: Bad habits to kick : mis-handling date / range queries


Sounds right to me 432.6 GB = 443,000 MB

The SQL Servers are consuming less than "min memory" because that is all they need.

  SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

That is, your working set of data does not exceed min memory and this is false:

  total of more than 2 TB worth of often accessed data.


As already stated, the filesystem is the problem.  However, there may be some tuning that would help.
If innodb_file_per_table has been ON, then there are 2*12 files in each database directory.  Turning that OFF would lead to fewer files (but not fewer directories).  This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';

There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;.  I will run a number of checks to see if the table_open_cache is too small, and other things.


Log-in as service account in SQL Server Management Studio

Question is, can I login using a service account in SQL Server
  Management Studio?

How To Add A Service Account To Microsoft SQL Server

Open SQL Management Studio using SQL Authentication SysAdmin (SA)
Account.
Open up Security then Right Click on Logins, choose “New Login”
On the new login screen choose “Search”
On the search screen ensure you are searching the Entire Directory,
type in the user name, choose Check Names, then choose ok.
After you click OK, Click Server Roles and check “sysadmin”
Click OK and you are done
Test out by logging into SQL Management studio using Windows
Authentication.

For further your ref here and here


Now to normalize table when position in group uniquely identifies entity

Which happens more often?  Renaming/reordering versus fetching?

If you are fetching most of the time, then make that more efficient (PK order), while letting the other be more costly.

If you are renaming/reordering most of the time, then normalize to simplify that -- at the cost of ordering during fetching.

If the two tasks are the same (or even remotely similar in frequency), the do whichever is easier to code.


Adding extra total column that sums all column values on each row

I'm not sure you need the CTE you're attempting.  Seems like you're doing all the work needed in your first query and can use THAT as the CTE.

Here's a mockup you may be able to just run:

I named your first column "Category" so that you'd have something to use in the final SELECT statement.  Also surrounded the values to be added in individual ISNULL([Value],0) so that any NULL values wouldn't cause trouble.

Hope this helps a bit.

WITH CTE_Data
AS
(
SELECT 'Total_Invoices' AS Category, SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
)

SELECT Category,
       [Amnt(Exl)],
       [VAT],
       [Amnt(Incl)],
       ISNULL([Amnt(Exl)],0) + ISNULL([VAT],0) + ISNULL([Amnt(Incl)],0) AS SumRequested
FROM CTE_Data;


How to get the sequence name for a serial column

In the comments, a_horse_with_no_name said:

  Are you using Greenplum or Postgres? In Postgres you can just use pg_get_serial_sequence

You can find the docs here.

  pg_get_serial_sequence returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. The first input parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter is potentially a schema and table, it is not treated as a double-quoted identifier, meaning it is lower cased by default, while the second parameter, being just a column name, is treated as double-quoted and has its case preserved. The function returns a value suitably formatted for passing to sequence functions (see Section 9.16). This association can be modified or removed with ALTER SEQUENCE OWNED BY. (The function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it's typically used with serial or bigserial columns.)


Difference between avg Disk sec/write and io_stall_write_ms

I think your conclusion that these two numbers should match up is fair.  That measurement of write latency from sys.dm_io_virtual_file_stats should provide similar numbers to the "Logical Disk" ➡ "Avg. Disk sec/Write" Perfmon counter.
Make sure that you are comparing "apples to apples" as much as possible.  The default for that Perfmon counter is to show you latency across all disks, so make sure you've selected the disk you're interested in (not "Total"):

Similarly, on the SQL Server DMV side, make sure you're only aggregating and comparing data for files on that same disk.  The sys.dm_io_virtual_file_stats is giving you data per file, which could be across multiple disks.
It could just be the differences in sample rates between the different ways of measuring that are throwing things off.  For instance, you're getting the DMV data every 15 minutes.  But presumably you were viewing the live output of the default Perfmon, which will show you the average over 100 seconds.  There might just be outliers in the 15 minute interval that lead to the average being higher for the DMV than what you're seeing in Perfmon.  To try and rule this out, you could (at least temporarily) sample virtual file stats more frequently to see if the numbers match up better.
I expect, based on the lower latency-related values you see in Perfmon and wait stats, that you're not actively having disk latency issues, and you're just curious about the difference measurements you're getting from the different tools.
If you are having issues, you might be in for a deeper dive into "what's getting between SQL Server and the disk writes."  Top Microsoft Man Sean Gallardy talks about that pretty in depth here: Slow checkpoint and 15 second I/O warnings on flash storage


Problems caused by SQL Server 2016 Query Store

This was a known bug with Query Store on Standard and Express.

  Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file. Cleanup activation period depends on the workload frequency, but it is no longer than 15 min.

The work around is to turn off cleanup policies and manually clear query store data.

to clear all query store data

ALTER DATABASE <database name> SET QUERY_STORE CLEAR

to selectively clear data

sp_query_store_reset_exec_stats

sp_query_store_remove_plan

sp_query_store_remove_query

The issue was first fixed in Cumulative Update 1 for SQL Server 2016:

Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition of SQL Server 2016 (KB 3178297)


How to sync MySQL database between two servers

You should probably go for Master-Master Replication .

http://www.howtoforge.com/mysql_master_master_replication


DNS CNAMEs take care of the host name only, but do absolutely nothing for the port number, which is what the instance name represents.

If you want to use CNAMEs for this, you will have to make the two instances behave as if they both were default instances: in other words they will both have to listen on port 1433.

Each instance in a cluster has its own IP address, so this is totally possible.
More information can be found on this blog post.

In your case, you could set up something like this:

MEANINGFULNAME1 -- > WIN2K8SQL1\INST1
MEANINGFULNAME2 -- > WIN2K8SQL2\INST2

Where MEANINGFULNAME1 is a CNAME for WIN2K8SQL1 and MEANINGFULNAME2 is a CNAME for WIN2K8SQL2.


Difference between SID and Database name

Database is just storage that user applications' data is stored.

Yes. It is a collection of the files (datafile, controlfile, redo log, tempfile, block change tracking file, etc.) on the disk.

  Instance is server ORACLE processes are running, which contains SGA.

Yes. Instance is the collection of processes + SGA.

Typically one instance belongs to one database, but a RAC database can have multiple instances on multiple nodes.

db_name is the name of the database. It is stored in the database files as and pfile/spfile as well.

SID identifies the instance by the name. It can be changed by setting the instance_name parameter. It is not stored in the database files, only in the pfile/spfile. (Note that it is not a strictly unique identifier, multiple instances can exist on the same server with the same name, running from different Oracle Homes).

SID (instance_name) defaults to db_name.

In case of a cluster database, SID (instance_name) defaults to db_name + instance_number.

On top of the above, SID (instance_name) can be changed by setting instance_name to a custom value.


Read Only Users on secondary replica AlwaysOn

You can set an Asynchronous secondary to be readable and connect to it for read-only workloads. See the documentation here.

There are, however, a number of considerations for this:

You must licence the secondary replica. Read workload counts as production workload, so you must have a fully licensed secondary and not rely on Software Assurance coverage which provides a free warm standby licence.
Your data, in an Async replica, may not be current with production. Depending on your latencies, this could result in your reports returning incorrect data. You need to understand your current latency averages and determine if this is acceptable.
You said "Can i read SSRS report from the Node 2?" - If you mean can you run reports from a report server database in the Availability Group from the secondary, no you cannot. SSRS must still access the ReportServer databases in an AG on the Primary Replica. If instead, you meant can you point your SSRS report datasource at a secondary database, yes you can.
You should ensure your read-only routing URLs and lists are configured correctly so that you can utilise read-only routing without hard-coding secondary replica names in connection strings. This helps future-proof against architectural changes over time (new replicas added, server name changes etc).


SQL query to convert Column to Row using Pivot/Unpivot

one way of approaching this is by doing two pivots and joining them together. see below. I also believe your expected output example is slightly wrong based on the input data, but that doesn't matter anyway.

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
          SELECT 'A',2000, 'X',10,2
UNION ALL SELECT 'A', 2001, 'X',21,1
UNION ALL SELECT 'A', 2000, 'Y',13,3
UNION ALL SELECT 'A', 2001, 'Y',2,3
UNION ALL SELECT 'B', 2000, 'X',0,2
UNION ALL SELECT 'B', 2001, 'X',5,1
UNION ALL SELECT 'B', 2000, 'Y',6,4
UNION ALL SELECT 'B', 2001, 'Y',9,5
UNION ALL SELECT 'C', 2000, 'X',3,5
UNION ALL SELECT 'C', 2001, 'X',2,8
UNION ALL SELECT 'C', 2000, 'Y',9,2
UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select * INTO #t from PersonOptions

;WITH pvt1 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM
    (
        SELECT t.ID, t.Year, t.Text, t.Value1
        FROM #t t
    ) x
    PIVOT
    (
        MAX(Value1) FOR Year IN ([2000], [2001])
    ) pvt
), pvt2 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM
    (
        SELECT t.ID, t.Year, t.Text, t.Value2
        FROM #t t
    ) x
    PIVOT
    (
       MAX(Value2) FOR Year IN ([2000], [2001])
    ) pvt
)
SELECT *
FROM pvt1
INNER JOIN pvt2 ON pvt2.ID = pvt1.ID AND pvt2.Text = pvt1.Text

DROP TABLE #t


Will the Query Optimizer Ignore A Fragmented Index?

AFAIK the optimizer is not aware of index fragmentation. This can be a problem if it picks a plan that scans a fragmented index.

The optimizer is aware of the allocated data size, though. If the index pages have a lot of free space (possibly due to internal fragmentation) this makes the index less likely to be used. 50% empty space means twice the amount of IO to scan. For random access that should not matter to any significant extent, though.

This is not a huge effect, though. It might explain what you are seeing.

If this small effect flips the query plan to not use the index then the index was never super great in the first place in the eyes of the query optimizer. This might be a hint that you can improve it.

Also, the optimizer seems to have a guess for how much of the index is cached in the buffer pool. There are some references to that in the XML execution plans. I have no detailed knowledge of that.

  I'm beginning to think that adding an index to this table won't help at all

I wouldn't go that far. Maybe all you need is a rebuild or a drop-DML-create sequence in the right places? Or, maybe this is just a query tuning problem (ask a new question with the actual execution plan included).


Indexed View over multiple partitioned tables and ALTER TABLE SWITCH

It seems you are partitioning on one of the grouping columns of the indexed view aggregation.
In this case, the indexed view will be aligned with only one of the partitioned tables, which is one with the exposed grouping column you're partitioning on.

The Partition Switching When Indexed Views Are Defined books online topic states:

  Where the view references several tables (using joins, subqueries,
  functions, and so on), the indexed view is partition-aligned with only
  one of the partitioned tables.

Consequently, you'll be able to SWITCH partitions in/out of only one of the tables. Below is a demo script.

CREATE PARTITION FUNCTION PF_START_DATE_TIME_KEY(int)
    AS RANGE RIGHT FOR VALUES(1);
CREATE PARTITION SCHEME PS_START_DATE_TIME_KEY
    AS PARTITION PF_START_DATE_TIME_KEY ALL TO ([PRIMARY]);
CREATE TABLE dbo.DATE_TIME_DIM(
      START_DATE_TIME_KEY int
    , DATE_TIME datetime2(0)
    );
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT(
      START_DATE_TIME_KEY int NOT NULL
      , CorrelationID int NOT NULL
      , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

CREATE VIEW dbo.V_STATE_RSN_AGG_BASE
WITH SCHEMABINDING
AS
SELECT
      SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY
    , SUM(SM_MEDIA_NEUTRAL_STATE_FACT.col1) AS col1
    , SUM(SM_RES_STATE_REASON_FACT.col1) AS col2
    , COUNT_BIG(*) AS CountBig
FROM dbo.SM_MEDIA_NEUTRAL_STATE_FACT
JOIN dbo.SM_RES_STATE_REASON_FACT ON
    SM_MEDIA_NEUTRAL_STATE_FACT.CorrelationID = SM_RES_STATE_REASON_FACT.CorrelationID
GROUP BY SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V_STATE_RSN_AGG_BASE ON
    dbo.V_STATE_RSN_AGG_BASE(START_DATE_TIME_KEY)
    ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

--this SWITCH succeeds because the indexed view partitioning column is from the SM_RES_STATE_REASON_FACT table
TRUNCATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING;
ALTER TABLE dbo.SM_RES_STATE_REASON_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1)
    TO dbo.SM_RES_STATE_REASON_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO

--this SWITCH fails because the indexed view partitioning column is not from the SM_MEDIA_NEUTRAL_STATE_FACT table
TRUNCATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING;
ALTER TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1)
    TO dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO


As you don't tell us ´,w hat for an Update you will have,
I selected one . where the lst of a type get 5 Minutes more on his End time, this is of corse only an example for an   update, to show how it works.
The latest seen time we get from the WINDOW Function  MAX OVER PArtition
And the update only update the types with the latest time (i choose Last_seen_in_garage) because the update only updates the rows , that are joined together
In the new query i also check out the same data from the temptaböe(see that Ferrair has now 2020 date as Last_seen_in_garage and use those data to update the CARS table

CREATE TABLE Cars  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp
);

INSERT INTO Cars
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('Ferrari', '2002-10-13 07:12:58', '2002-10-13 10:12:58'),
  ('Lambo', '2002-10-13 08:12:58', '2002-10-13 11:12:58'),
  ('Renault', '2002-10-13 09:12:58', '2002-10-13 12:12:58'),
  ('Ferrari', '2002-10-13 10:12:58', '2002-10-13 15:12:58');

✓

4 rows affected

  CREATE TABLE Tmp_table  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp
);

INSERT INTO Tmp_table
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('Ferrari', '2002-10-13 07:12:58', '2002-10-13 10:12:58'),
  ('Lambo', '2002-10-13 08:12:58', '2002-10-13 11:12:58'),
  ('Renault', '2002-10-13 09:12:58', '2002-10-13 12:12:58'),
  ('Ferrari', '2020-10-13 10:12:58', '2020-10-13 15:12:58');

✓
4 rows affected

 UPDATE Cars
SET "Last_seen_in_garage" = maxtempcar."maxdateime"
FROM (SELECT DISTINCT Cars."Type",
       MAX("Last_seen_in_garage") OVER (PARTITION BY Cars."Type") "maxdateime"
       FROM Cars) maxcar
INNER JOIN (SELECT DISTINCT Tmp_table."Type",
MAX("Last_seen_in_garage") OVER (PARTITION BY Tmp_table."Type") "maxdateime"
FROM Tmp_table) maxtempcar ON maxtempcar."Type" = maxcar."Type"
WHERE Cars."Type" = maxcar."Type" AND Cars."Last_seen_in_garage" = maxcar."maxdateime";

3 rows affected

SELECT *
FROM Cars
;

Type    | First_seen_in_garage | Last_seen_in_garage
:------ | :------------------- | :------------------
Ferrari | 2002-10-13 07:12:58  | 2002-10-13 10:12:58
Lambo   | 2002-10-13 08:12:58  | 2002-10-13 11:12:58
Renault | 2002-10-13 09:12:58  | 2002-10-13 12:12:58
Ferrari | 2002-10-13 10:12:58  | 2020-10-13 15:12:58

db<>fiddle here


How to start and then stop one cyclic process

If you want the stored procedure to be invoked by one process only with no other concurrent callers, you'd use sp_getapplock. Note that you'd have to throw an error for a 2nd process (with a zero timeout) but you can swallow this with a BEGIN TRY/CATCH arrangement.

Then you'd release it when the stored proc finishes.

Note that this uses of a stored proc is basically wrong. You'd have SQL Server Agent running the stored proc every minute. You may want sp_getapplock to prevent manual execution too but SQL Server Agent won't allow overlapping calls for the same job.

Or trigger is with Alerts in SQL Agent or Service Broker queues. We don't have enough info: you've asked for help with your chosen code-level solution. We don't know the problem you're trying to solve

If this is a single unit of work, then you need a transaction, and a 12-24 hour transaction is plain wrong


postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

Your mistake is in the ALTER DEFAULT PRIVILEGES command. It should be

ALTER DEFAULT PRIVILEGES FOR ROLE manager_u ...

This way, the views created by manager_u will get the permission granted.


What is the default username and password to the oracle pre-built virtual machine?

The default password for the HR, sys and system accounts is oracle.

The Unix password for the oracle user is also oracle.


Re-page Oracle tables after removing CLOB fields

One way to "re-page the table" is to use the ALTER TABLE... MOVE command. Behind the scenes Oracle will make a temporary copy of the table and switch to that copy. If your table has a lot of unused space in blocks then you might be able to reclaim some of that after the move. If your table has any indexes you will need to rebuild them. I struggled to find a good documentation link for MOVE but try this one.


How would I model the Twitter lists functionality?

I'll try to explain why they merge all relationships into one table.

Whether a users follows, likes or comments a photo all these activities are somewhat similar they involve a user and photo. Note that the comment also requires some text.

So instead of having 3 tables like your data model they went with one.

I don't like that approach though because it merges data that may is similar in its appearance but differs in meaning. Also the comments require extra data (content) that is empty for likes and follows. Having the type described as a string as in their example is also bad (better make an extra table).

A database model would like this for your problem

User(**UserID**, Name, ....)
Post(**PostID**, UserID[FK]Data, Timestamp, ...)
Follow_Users(**UserID[FK]**, **FollowingUserID[FK]**)
Follow_Posts(**UserID[FK]**, **PostID[FK]**)

However this is a database model and not a Data model.
A Data model that is kept in memory within some application will be different because it features pointers to other objects and arrays...


Optimizing query. FOR XML PATH or some alternatives?

Looking at this in Sentry One Plan Explorer, there are four major indexing issues, all associated with index scans hiding an expensive residual predicate:

The following four indexes will eliminate these scans and speed up this execution plan:

--- Currently scanning 7 million rows to return 11
CREATE INDEX give_me_a_name
ON [wts].[Prepaid_TransactionLog]
    ([tvmTransactionId])
INCLUDE
    (tp_terminalId, tp_POSId, status);

-- Currently scanning 21.5 million rows to return zero
CREATE INDEX give_me_a_name
ON [wts].[KBETicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (carrierSN, tp_terminalId, tp_POSId, status);

-- Currently scanning 6.25 million rows to return 12
CREATE INDEX give_me_a_name
ON [wts].[Intercity_TicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (formType, status, tp_terminalId, tp_POSId);

The fourth index is a little more complicated since the key involves a conversion to integer to match [wts].[IOYLog].[tvmTransactionId]. This means either altering the existing column, or providing a computed column as follows:

-- New computed column, should be instant to add (metadata only)
ALTER TABLE [mazovian].[Transaction]
ADD [SequenceAsInteger] AS CONVERT(integer, [Sequence])

-- Currently scanning 61.6 million rows to return 70
CREATE INDEX give_me_a_name
ON [mazovian].[Transaction]
    ([SequenceAsInteger])
INCLUDE
    (Id, Sequence, Terminal_id, Detail_id, Location_id);

Adding those four indexes should speed up the query significantly. As with all indexing changes, you should assess them on a test system before deciding on deployment.

There are a number of other small indexing improvements you could consider to avoid the Key Lookups in the plan:

add [status] to the existing index [IOYLog].[IX_IOYLog_tp_terminalId_tvmTransactionId_issuanceDate_owedAmount]
add carrierSN, tp_POSId, and status to index [TransportTransactionLog].[IX_TransportTransactionLog_tp_terminalId_tvmTransaction_KK]
add End_Status to index [Transaction_Distribution].[I_mazovian_Transaction_Distribution_Ticket_id]


can I have 2 heartbeat networks between the nodes for redundancy
The whole setup will be virtualized.

I'm assuming you're going to bind these directly to the hardware NICs, otherwise it's kind of a moot point. If the host already has the NICs teamed, etc., then you really shouldn't need multiple interfaces in your guest (except for management or performance reasons, but not for health check reasons).
Cluster health checks go over any interface you allow cluster communication on (the public/private nomenclature died with Windows Server 2003). NetFT will take care of which interfaces are what and the metrics for each, adding additional interfaces wouldn't really be ideal. Instead use teaming or other technologies to make sure the interfaces stay up and available. Setting the client network for client and cluster communication will also do what you're asking without the need for extra interfaces.
Edit:
Unless you need to segregate traffic, such as the AG traffic for specific reasons such as NICs of higher speeds, network appliance between (compression/encryption), auditing needs, network protections, different virtual switches, etc., then there isn't really a need. If you meet one of these items, then it would make sense to have multiple NICs. Having a single NIC may require you to make changes to the host configs to have larger receive or send buffers.


MySQL: 2 separate queries run very fast, combined query with OR runs very slow

In my experience though, doing anything with an OR condition can have horrible implications when running queries, have you considered running this as two separate queries and then combining the results?

E.g.

SELECT * FROM (

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action,
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
    FROM log, action, firewall, proto, rule_uid
    WHERE ( rule_uid.id = log.rule_uid )
            AND ( proto.id = log.proto )
        AND ( log.action = action.id AND action.action='accept' )
        AND ( log.orig = firewall.id)
        AND ( ( src >= inet_aton('0.0.0.0')
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( xlatedst >= inet_aton('173.82.122.94')
                AND xlatedst <= inet_aton('173.82.122.94') ) )
    GROUP BY src, dst
    ORDER BY hits DESC, dst

    UNION ALL

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action,
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
    FROM log, action, firewall, proto, rule_uid
    WHERE ( rule_uid.id = log.rule_uid )
        AND ( proto.id = log.proto )
        AND ( log.action = action.id AND action.action='accept' )
        AND ( log.orig = firewall.id)
        AND ( ( src >= inet_aton('0.0.0.0')
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( dst >= inet_aton('173.82.122.94')
              AND dst <= inet_aton('173.82.122.94') ) )
    GROUP BY src, dst
    ORDER BY hits DESC, dst

) ORDER BY hits DESC, dst
LIMIT 100;

That may do the job, or something similar :)


Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

To use the DAC in Express, you need to enable startup trace flag 7806. This is documented here and in more detail here.

Open Configuration Manager, in your SQL Server Services pane right-click the Express instance and select Properties. On the Startup Parameters tab add -t7806, hit Add, then OK, and restart the service.

You can check the error log or the DMVs after the restart to verify the DAC is enabled.

The reason this is disabled by default in SQL Server Express is for performance reasons (you're already limited to 1GB of memory, and DAC locks up a chunk of that). So, unless you are really sure you're going to need the DAC, it might be best to leave it off.


Seaching points within a radius - performance issues

Question 1:  The temp table and filesort are unavoidable; don't worry about it.

Question 2:  Let's remove the JOIN... and change AND status=1 into

AND EXISTS ( SELECT * FROM users
                WHERE uid = userprofile.uid
                  AND status = 1 )

(I assume uid is the PRIMARY KEY of users?)

In theory this will delay force the test for status until after the Spatial test.


Stored procedure deployment - push or pull into database?

Although it's all opinion I strongly favour using pull techniques for the following reasons:

I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push".

Most DBMSes have better control of transactions etc in the current database vs the remote database when running code in stored procedures.
For example, in Oracle, you can't truncate a table on the other side of database-link. So you'd require a helper-package on the other side to perform these actions. Much better to have the stored procedure execute the truncate locally and pull the data to populate the table.

I think "pull" is a better fit to the requirements of a reporting data-warehouse / reporting system. These tend to run at scheduled times.
If you have an architecture that does incremental updates of the warehouse or reports, then "push" is a better fit. I don't like to mix the flows.

Talk to your admins / ops team. If I was the DBA, I would prefer to admin systems that "pull" there data from others. I think it's easier to manage the cascading effects of eg down time in this way.

Just some of the reasons and the ones I can think of right now.


Login fails even though it exists when failing over to mirror

You need to ensure the SIDs of the logins are the same on both servers.  The only way to ensure they are the same is to script the login from the working server to the mirror server.   You cannot simply recreate the logins with the same name and password since the login will not match the user that exists in the database.  When you CREATE LOGIN you must use the SID = xxx option.

Perhaps the code below will help point you in the right direction.

/*
    Copy SQL login info for movement to another SQL server.
    Copies the SID and the Password hash.

    Run this piece on the source server, and copy the results to another query window
    connected to the destination server.  Run the results on the destination server to
    DROP and CREATE LOGINs with the same SIDs and PASSWORDS as the source server.
*/
DECLARE @txt varchar(max);
SET @txt = '';
SELECT @txt = @txt + 'DROP LOGIN ' + QUOTENAME(l.name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE LOGIN ' + QUOTENAME(name) + CHAR(13) + CHAR(10) + CHAR(9) + 'WITH PASSWORD = ' + CONVERT(varchar(max), l.password_hash, 1) + ' HASHED'  + CHAR(13) + CHAR(10) + CHAR(9) + ', SID = ' + CONVERT(varchar(max), l.sid, 1) + CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_DATABASE = ' + QUOTENAME(l.default_database_name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_logins l
WHERE l.principal_id > 1
    AND l.is_disabled = 0;
SELECT @txt;

/*
    Look for orphaned USERS with a matching LOGIN
    Run the below SQL code in the same window used to run the code generated by the above SQL text.

    This code will look for and correct orphaned users.  Relies on the LOGIN name and the USER name
    being identical.
*/
GO
DECLARE @dbname nvarchar(max);
DECLARE @txt nvarchar(max);
SET @dbname = '';
SET @txt = '';
DECLARE dbcur CURSOR LOCAL FORWARD_ONLY FOR
SELECT name
FROM sys.databases
WHERE database_id > 4;
OPEN dbcur;
FETCH NEXT FROM dbcur INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @txt = @txt +
    '
USE ' + QUOTENAME(@dbname) + ';
GO
DECLARE @txt nvarchar(max);
SET @txt = '''';
SELECT @txt = @txt + ''ALTER USER '' + QUOTENAME(dp.name) + '' WITH LOGIN = '' + QUOTENAME(dp.name) + '';''
FROM sys.database_principals dp
WHERE dp.type = ''S''
    AND sid NOT IN (SELECT sid FROM master.sys.sql_logins)
    AND name IN (SELECT name from master.sys.sql_logins);
IF @txt <> '''' EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @txt;
    ';
    FETCH NEXT FROM dbcur INTO @dbname;
END
CLOSE dbcur;
DEALLOCATE dbcur;
SELECT @txt;


Postgres: ERROR: operator does not exist: point  point

Possible explanation: you installed the extension in a schema that's missing from your current search_path.

Diagnose with:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

SHOW search_path;

Is the schema of the extensions in your current search_path? If not, there is your explanation. Either install to a different schema or adapt your search_path.

And:

SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<@>';

Related:

Is it recommended to install extensions into pg_catalog schema?
How does the search_path influence identifier resolution and the “current schema”


Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations

You could attempt to solve the issue by changing the transaction isolation level. Specifically enabling Snapshot isolation and setting the Read Committed Snapshot so that reads and writes won't interfere with each other. You could also just update the production query to use snapshot isolation without updating the default database isolation level.
Be careful though and thoroughly test this because it will increase the traffic on tempdb as row snapshots are written. Setting Read Committed Snapshot is also a default behavior change across the whole database which needs regression testing to verify there are no unexpected side effects.
A better explanation on this option is available from Microsoft here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server


Server-wide Database Permissions in SQL Server

So basically it sounds like you want to give this group of individuals db_owner access to all databases on the server.  The way to do this is:

Create a local group account on the Windows Server (e.g. \\MYSERVER\SpecialGroup)
Add the AD accounts to that group.
Create a SQL Server login for \\MYSERVER\SpecialGroup
Then create a job, that runs every night (or however frequently you want) that loops through all your user databases and issues a CREATE USER statement for \\MySERVER\SpecialGroup and assigns them to db_owner.