From the graph you provided (that I'm reporting below, for the convenience of other readers), I saw you're running munin to monitor your server.

As such, it would help to have a look also to other graphs (like CPU, Memory, I/O and, even more important, network-traffic) that unfortunately where not included in your question.

Anyway, the munin mysql throughput plugin (that generate the graph below) describe itself as: "mysql_bytes - Plugin to monitor the number of bytes sent from and received by mysql"

So based on the graph, we saw that your MySQL is serving content to the underlying network at a speed ranging from 40MBps (Mega Bytes per second) to 70/80MBps.

Should this not be normal in your particular case, than, in addition to give a look to this answer, you can check/monitor what's going on on your server, searching for high-traffic query results.

As a first step, I suggest:

show processlist: to see which users are connected and which query are they running on which DB, running for how much time (should they be "long");
enable "slow query log": to see which queries are requiring lots of time (and, hence, might impact your performances while, at the same time, having high changes to be optimized);

Should this not be enough to get a figure on what is going on, you can:

enable "general log": please note that this will cause a write-log for every query run by your mysql instance. This might seriously impact your performance so... use it wisely.

As a first guess, I think you are running under two conditions:

a small number of queries, returning very large recordsets, executed relatively few times (...so to generate 40/80MB of traffic) (eg.: a single recordset made by 100.000 records, each of 300 bytes, counts for up to 30MB of traffic. So two of them, per second, can... answer your problem).
a sligher higher number of queries, returning relatively small recordsets, but executed very frequently (...so to generate 40/80MB of traffic as well) (eg: a single recordset made by 10 records, each of 1000 bytes, counts for up to  1MB of traffic when executed 100 times per second).

The slow-query-log will help you catching the first issue. The general-log is the only one helping in the second case.

Also:

if you are NOT experiencing any issues in terms of storage-I/O, than everything is happening in RAM and... mysql surely cached all the recordsets. In such a case show status will help you figuring out what's going on.

Should you provide further details, we might be more helpful.

P.S.: I'm not a mysql-performance-tuner at all so don't blame me for all the (more important) tuning issues I've surely missed :-)


Select maximum of a count in a grouped clause

You can use a window function like ROW_NUMBER() to generate a unique ID for each record within a grouping (PARTITION) of each value of category like so:
SELECT SalesDateYear, model, Sold, category
FROM
(
    select YEAR(Sales.saleDate) SalesDateYear
         , Vehicles.model
         , count(Vehicles.model) 'Sold'
         , Vehicles.category
         , ROW_NUMBER() OVER (PARTITION BY Vehicles.category ORDER BY COUNT(Vehicles.model) DESC) SortId
      from Vehicles
      JOIN vehicleSold
        on Vehicles.vin = vehicleSold.vin
      JOIN Sales
        on Sales.saleID = vehicleSold.saleID
     group
        by YEAR(Sales.saleDate)
         , Vehicles.model
         , Vehicles.category
) Results
WHERE SortId = 1

(I normally like to use a CTE instead of subquery for these kinds of things, but I don't think your version of MySQL has CTEs, so the above uses a subquery.)


Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool

You'll have to completely uninstall the client tools portion of your Express instance first, since side-by-side installations of Management Studio Express and Management Studio are not supported - there is no direct upgrade path, it's either one or the other.


You could use LEFT JOIN LATERAL to get a list of all equipment names sorted alphabetically for each growth space, and compare it with another sorted list of elements:

SELECT gs.*
FROM growth_spaces gs
LEFT JOIN LATERAL (SELECT ARRAY(
  SELECT e.name
  FROM growth_space_equipment gse
  LEFT JOIN equipment e ON (gse.equipment_id = e.id)
  WHERE gse.growth_space_id = gs.id
  ORDER BY e.name) AS names) e ON (true)
WHERE e.names = ARRAY(SELECT UNNEST('{Mist Propagation,Automated Curtain}'::varchar[]) ORDER BY 1)

A variant of this is comparing a list of IDs rather than the texts, which would perform better for large sets:

SELECT gs.*
FROM growth_spaces gs
LEFT JOIN LATERAL (SELECT ARRAY(SELECT equipment_id FROM growth_space_equipment WHERE growth_space_id = gs.id ORDER BY 1) AS eq_ids) e ON (true)
WHERE e.eq_ids = ARRAY(SELECT id FROM equipment WHERE name IN ('Mist Propagation', 'Automated Curtain') ORDER BY 1)


Does creating a unique constraint on a Postgres column remove the need to index it?

Yes. A UNIQUE constraint is implemented with the help of a unique index -  a b-tree index with default ascending sort ordering over all involved columns. The index is created and maintained automatically, and is used for all purposes like a plain unique index by Postgres.

There is no need to create another (redundant) unique index like it, that would be a waste of resources.

Detailed explanation:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Is unique index better than unique constraint when an index with an operator class is required

Some rare exceptions apply for multicolumn indexes with special sort options:

Optimizing queries on a range of timestamps (two columns)


Use PGXN to install pg_repack on postgres 9.6 on top of Ubuntu 14.04

pgxn will install using the PostgreSQL associated with whatever pg_config it finds first in $PATH.  If you set your PATH (either permanently or temporarily) to list first the directory containing that pg_config then that should fix the problem

sudo su
PATH=/some/version/postgres/bin:$PATH
pgxn install pg_repack


search xml column inside large table (7 million rows)

You may improve your performance by using xml exist, instead of extracting the values.

SELECT playId
from plays
where PlayFieldValues.exist('/PlayAttributes/PlayFields/PlayField[@ID = "Play.GenericInt1" and text()="25"]')=1

...but without any xml indexes this is still going to involve a table scan.


User has db_owner through AD group but cannot create table

There are so many possibilities, but here are a few things to check:

that the user is a member of the ADB group in the Active Directory.
that ADB exists as a server principal (a login). Example: SELECT * FROM sys.server_principals WHERE [name] LIKE '%ADB';
that this login has a user in the database in question: SELECT * FROM myDatabaseName.sys.database_principals WHERE [sid] IN (SELECT [sid] FROM sys.server_principals WHERE [name] LIKE '%ADB');
that the database principal (the user) is a member of db_owner (not to be confused with schema ownership)
like @mlachman points out in another answer, that the database principal hasn't been specifically denied any actions.

Also,

You don't need db_public, db_datareader or db_datawriter on ADB, if it's db_owner. Good housekeeping to clean those memberships up, and it may even help with your problem.

If it's any use to you, I've written a script that compiles all of the permissions in a database. It's available on the Downloads page of my blog, and it's completely "as-is", without any warranty or guarantee.


createdb is a commandline tool which should be run in a Windows commandline window ("Start -> Run -> "cmd.exe"). You have to have PostgreSQL's bin directory in the windows PATH in order for this to work without supplying the full program path.

As an alternative you can run SQL statements using the program psql (which is also a command line program).  You probably also have a start menu entry in the PostgreSQL folder name "SQL Shell" (or something similar) which starts psql directly.

A list of GUI tools (apart from pgAdmin) is available in the Community Guide to PostgreSQL GUI Tools.


does xml element exist, regardless of value?

Try this:

For attributes

SELECT COUNT(x.a2) cnt_a2, COUNT(x.a4) cnt_a4
FROM
(
VALUES
  '<doc><c a2=""/></doc>'
, '<doc></doc>'
) T (D)
, XMLTABLE
(
  '$doc' PASSING XMLPARSE(DOCUMENT T.D) AS "doc"
  COLUMNS
    a2 XML PATH '//*[fn:exists(@a2)]'
  , a4 XML PATH '//*[fn:exists(@a4)]'
) X
WHERE
--XMLEXISTS('$doc//*[@a4 or @a5]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")
XMLEXISTS('$doc//*[@a2 or @a4]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")
;

The result is:

|CNT_A2     |CNT_A4     |
|-----------|-----------|
|1          |0          |

For elements with namespaces

SELECT COUNT(x.code1) cnt_code1, COUNT(x.code3) cnt_code3
FROM
(
VALUES
  '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code1>MYCODE</conditionmessage:code1></doc>'
, '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code2>MYCODE</conditionmessage:code2></doc>'
) T (D)
, XMLTABLE
(
  XMLNAMESPACES('http://xyz.com' AS "conditionmessage"), '$doc' PASSING XMLPARSE(DOCUMENT T.D) AS "doc"
  COLUMNS
    code1 XML PATH '//*[fn:exists(conditionmessage:code1)]'
  , code3 XML PATH '//*[fn:exists(conditionmessage:code3)]'
) X
WHERE XMLEXISTS('declare namespace conditionmessage = "http://xyz.com"; $doc//*[conditionmessage:code1 or conditionmessage:code3]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc");

The result is:

|CNT_CODE1  |CNT_CODE3  |
|-----------|-----------|
|1          |0          |


Seems like the file was not read properly.

make sure that you restarted the postgresql-server after editing the file
make really sure that it's restarted (I ran into this twice...)
try again.

I searched one day for one suche error and a simple restart was all that was needed.

Also: with the last homebrew-install I made, my current user was setup automatically without password. so I could just execute psql template0 and was connected to the server.

From there, you can create databases, roles and so on.


Hex and Binary Literals in MySQL / MariaDB?

This is expected behaviour in MariaDB since versions 10.0.3 and 5.5.31. It looks like you're running the queries in MariaDB, but reading the MySQL docs. These are similar, but diverging RDBMSes.

According to the MariaDB documentation on hexadecimal literals:

  The first two syntaxes; X'value' and x'value, follow the SQL standard,
  and behave as a string in all contexts in MariaDB since MariaDB 10.0.3
  and MariaDB 5.5.31 (fixing MDEV-4489). The latter syntax, 0xvalue, is
  a MySQL/MariaDB extension for hex hybrids and behaves as a string or
  as a number depending on context. MySQL treats all syntaxes the same,
  so there may be different results in MariaDB and MySQL (see below).

The queries in your example will give warnings such as:

  | Warning | 1292 | Truncated incorrect INTEGER value: '\x01' |

So you can get your desired behaviour by using the non-standard 0xvalue syntax:

SELECT 0x01 & B'00000001', 0x01 & 0x01, B'00000001' & B'00000001';

Which gives:

+--------------------+-------------+---------------------------+
| 0x01 & B'00000001' | 0x01 & 0x01 | B'00000001' & B'00000001' |
+--------------------+-------------+---------------------------+
|                  1 |           1 |                         1 |
+--------------------+-------------+---------------------------+


SQL Server Profiler was designed to do this.

With it you can:

capture queries as they are executed on the target instance
capture other instance and database events like deadlocks, logins, and errors
filter captured queries by text in the query, login name, database name, and so forth


Modify the column names of database tables to fit my app's technical limitations?

Build Views on top of the tables.  Every RDBMS should have this functionality as VIEWS are part of the SQL Standard.
 A View will allow you to call the columns whatever you so choose without having to change the underlying structure provided by the 3rd parties you say are providing the databases.  Views may also allow you some flexibility to standardize names and maybe even data types to your liking.  Finally, if you get additional data from the 3rd parties that can easily be loaded into the current structures, this will minimize any rework needed for future ETL processes.


The error message defines the problem: you can't use a window function directly in an assignment (SET). You can use a CTE to pull out the aggregate and then apply the update to the CTE (which pushes it back to the table).

;WITH t AS
(
  SELECT key1, date1, date2,
    date1max = MAX(date1) OVER (PARTITION BY key1 ORDER BY key1)
  FROM #table
)
UPDATE t SET date2 = CASE date1max
  WHEN date1 THEN NULL ELSE date1max END
WHERE date2 IS NULL;

If dates are not unique for any given key, you might need to do something to deal with ties. Please add sufficient sample data that would represent all edge cases and how you want to handle those.


NLS_TIMESTAMP_FORMAT changing automatically and randomlly

First change the parameter at the system level:

alter system set nls_timestamp_format = 'dd/mm/yyyy' scope='both';

This will also update the spfile so even if you bounce the db you'll keep your definition.

Second, check for any specific definitions in your client.
If you're using windows go to

the registry - check if you have the nls_timestamp_format set differently.
the glogin.sql file - this is a file that executes every time you log into sqlplus. there might be an alter session... there that changes the system setting.


How to add a check constraint in Postgres without locking the table?

You can create a NOT VALID CHECK constraint, which will enforce the constraint going forward, but will not check the entire table for validation upon creation.  At some later date, you can attempt to VALIDATE the constraint (when a lock on the table is ok)

Please review the documentation - Quote below:

  ADD table_constraint [ NOT VALID ]

  This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.


Sybase ASE 15.5 : Create a custom function for partitionning

You cannot write your own partition function. What you need to do is create an additional column, assign a value resulting from the function you want to use, and partition on that column. Unfortunately, you cannot partition on a computed column, which would be the easiest solution.


How can I get partition elimination with a variable?

In this case, adding an OPTION(RECOMPILE) hint to the query will get you the plan you want.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID
OPTION (RECOMPILE);

The hint allows SQL Server to compile a plan specific to the current value of @ServerID. More specifically, it enables the Parameter Embedding Optimization, meaning the query is compiled as if you had written WHERE ServerID = 1, allowing partition elimination.


Missing Database Mail Configuration Wizard in SQL Server 2008 R2

I figured out the problem.  I found that I was unknowingly running Express.  I had incorrectly assumed the Express designation would be more visible withing the initial result of the @@version query

In SSMS run the SELECT @@version query
Grab the column edge and drag it all the way to the right. Within the copyright information it will list Express.

From what I am finding online there are features of Database mail that exist in Express and that is why I was able to enable and run it.


It is absolutely not a requirement in this very specific case, but it is a requirement in many other scenarios. If you're creating a database called Sales, and you arelady have a database called Sales, you'll need to change your database context before you:

Restore with replace; or,
Drop the current database and then:

Create from scratch; or,
Create for attach.

There are plenty of other scenarios outside of database creation that also require either (a) not being in the context of the current database, or (b) being in the context of master specifically (or at least not a specific database), and many of these things you may be doing during or around creating databases:

Setting a database to a different state, like single_user
Preventing errors when a script has a USE command but that user database may be offline or otherwise inaccessible
Granting server-level permissions like CREATE DATABASE
Granting server-level role membership
Marking a module as a system object (sp_MS_marksystemobject) or as a startup procedure
Certain types of certificate, server audit, and Availability Group operations

Probably a slew of other things. USE master; isn't always necessary, but sometimes it is, and it doesn't hurt to always execute server-level commands from that database.


set compatibility mode in Azure Sql Database?

I am not disgarding anything of @Shanky, but I would like to say that at presently in Azure SQL Server database version is 12.0.2000.8 . Which is the RTM version of SQL Azure and MAJORVERSION of SQL2014.

I am attaching the screen shot here which I have connected to My Azure SQL Server database through on-premises SSMS 2017.

Here I am writing the TSQL query to check the MajorVersion, ProductLevel, Edition, ProductVersion through the below mention query in on-premises as well as in Azure.

SELECT
  CASE
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion;
  Go

Here I am just attaching the screen shot of Azure SQL database compatibility level , which I have got after execution of this TSQL query in Microsoft Azure portal.

Even I have also checked out with New Blank database ( which is created by myself) and also checked with Sample (AdventureWorksLT) (this is the sample database which Microsoft is providing at present in the Azure portal).

With the respect of both database I have got the same version with compatibility.

To determine the current compatibility level of your database, execute the following Transact-SQL statement:

SELECT compatibility_level
FROM   sys.databases
WHERE  [name] = 'Your Database Name';

The alignment of SQL versions to default compatibility levels are as follows:

100: in SQL Server 2008 and Azure SQL Database
110: in SQL Server 2012 and Azure SQL Database
120: in SQL Server 2014 and Azure SQL Database
130: in SQL Server 2016 and Azure SQL Database
140: in SQL Server 2017 and Azure SQL Database

As per Joseph Sack SQL Database Engine Blog documentation here as on dated
July 7, 2017 we are announcing the official public preview of compatibility level 140 in Azure SQL Database.

As per MSDN BOL documentation here On Azure SQL Database Managed Instance, this T-SQL feature has certain behavior changes. See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details for all T-SQL behavior changes.

To change the COMPATIBILITY_LEVEL through TSQL.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }

The database_name is the name of the database to be modified.

COMPATIBILITY_LEVEL { 140 | 130 | 120 | 110 | 100 | 90 | 80 }

Is the version of SQL Server with which the database is to be made compatible.

The following compatibility level values can be configured:

 Product       Database Engine Version  Compatibility Level Designation Supported Compatibility Level Values
    SQL Server 2017 (14.x)     14             140                     140, 130, 120, 110, 100
    Azure SQL Database         12             130                     140, 130, 120, 110, 100
    SQL Server 2016 (13.x)     13             130                     130, 120, 110, 100
    SQL Server 2014 (12.x)     12             120                     120, 110, 100
    SQL Server 2012 (11.x)     11             110                     110, 100, 90
    SQL Server 2008 R2         10.5           100                     100, 90, 80
    SQL Server 2008            10             100                     100, 90, 80
    SQL Server 2005            9              90                      90, 80
    SQL Server 2000            8              80                      80

To Change the compatibility level of SQL Azure database in Microsoft Azure Portal.

ALTER DATABASE CompabilityTestDB
SET COMPATIBILITY_LEVEL = 140;
Go

I am also attaching the screen shot.

After changing to check the compatibility level of Azure SQL database in Azure Portal.

SELECT compatibility_level
FROM   [sys].[databases]
WHERE  [name] = 'CompabilityTestDB';

After changed the compatibility level of Azure SQL database in portal I am also attaching the screen shot here.

  Very Important Note: As of January 2018, in Azure SQL Database, the default compatibility level is 140 for newly created databases. We do not
  update database compatibility level for existing databases. This is up
  to customers to do at their own discretion. With that said, we highly
  recommend customers plan on moving to the latest compatibility level
  in order to leverage the latest improvements.

  If you want to leverage database compatibility level 140 for your
  database overall, but you have reason to prefer the cardinality
  estimation model of SQL Server 2012 (11.x), mapping to database
  compatibility level 110, see ALTER DATABASE SCOPED CONFIGURATION
  (Transact-SQL), and in particular its keyword
  LEGACY_CARDINALITY_ESTIMATION = ON.

  For details about how to assess the performance differences of your
  most important queries, between two compatibility levels on Azure SQL
  Database, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.
  Note that this article refers to compatibility
  level 130 and SQL Server, but the same methodology applies for moves
  to 140 for SQL Server and Azure SQL Database.

For further you ref here , here, here and Default compatibility level 140 for Azure SQL databases


Yes it can. We are currently using it on a 11g RAC database.

Do you need any other details?


MS Access- Attachments to SQL Server -Images or Varchar(max)

If the files was in the same sql server you can you the  OPENROWSET like this

Select * from Openrowset(Bulk 'C:\HaxLogs.txt', SINGLE_BLOB) as a

Or in the remote server

Select * from Openrowset(Bulk '\\server\backup\CSVcontagem_SEND_Akarollynegs.csv', SINGLE_BLOB) as a


Blocking in AG that causes app not to launch

As addition to Tony's comment this MSDN post can help you with performance counters and latency factors.

Also, verify one of the known issue (lag) "redo blocking on secondary due to schema changes on primary", you can verify that via built-in Xevent's session or follow these steps for validation of redo blocking and quick fix.


UnShrunk SQL Server database restore which is larger than available disk space

I'm not sure if this is an answer, but it's too big to comment

I had the same issue many times. In my case it always was about the log file. when you get backup, the log file may not contain any active log/data and it will not take any space in the backup file, but when restoring, SQL server tries to allocate the same space. If there is any way you can ignore log file when restoring, you might be able to restore.

check this out, it may help


You can use the admin command:

db.adminCommand({setParameter:1, ttlMonitorEnabled:false});

To re-enable, use the same command with true.


Sql Rank for records contain multiple combinations

I'm not familiar DB2, but if it allows using count as analytic function you can do

select *
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK ,
            count(*) over (partition by b.store) cnt
     from Library.Table1 b
    ) tmp
where tmp.cnt > 1
order by tmp.Store

If not, the query below should give you the same result (maybe not the best solution from performance perspective ) :

select tmp.*
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK

     from Library.Table1 b
    ) tmp
    INNER JOIN
    (
        SELECT store
        FROM Library.Table1
        GROUP BY store
        HAVING (COUNT(*) >1)
    )tmp2 ON (tmp2.store = tmp.store)

order by tmp.Store


Postgres: Efficient schema for querying with one exact string match and two range matches

Don't dismiss the simple btree index until you try it.  It surely won't scale well to a trillion rows, but you surely don't have a trillion rows.  It might be good enough.
To use an gist index, you would need to treat the ranges explicitly as ranges, not as end points.  It would probably be best to reformat your table in that format, but you can instead use expression indexes to reformat on the fly.  But then your query will have to be written to match.
Some other complexities are that simple scalars don't have built in gist operators, so to put "state" into a gist index requires you to use the btree_gist extension; and there is not a built-in text range.  Rather than creating a text-range, you can just cast the zip to int (which would probably be better done in the table in the first place), so something like this:
create index on regions using gist(
  state,
  daterange(date_begin,date_end,'[]'),
  int4range(zip_begin::int,zip_end::int,'[]')
);

And then the query would look like:
SELECT data
FROM regions
WHERE state = {state}
  AND {date}::date <@ daterange(date_begin,date_end,'[]')
  AND {zip}::int <@ int4range(zip_begin::int,zip_end::int,'[]')

Now I haven't tested this query on the gist index, because gist indexes are so ridiculously slow to build that I don't have one available yet to try it on. It has finished building and I have tested it, and it runs and gives the correct answer with '0' left-padded 5 digit zip codes.  With the range sizes I used, it is sometimes faster and some times slower than the btree index on a 10e6 row table, but was never markedly different.


Why did Microsoft kill "English Query" on SQL Server?

The reason it was dropped was because of lack of use. This is confirmed in the MS response to a Customer asking if it could be re-instated.

  Posted by Microsoft on 1/13/2012 at 1:37 PM

  English Query feature when introduced in SQL Server was not a popular
  feature and we didn't find lot of customers using it. So it was
  eventually removed from the product. We have no plans to invest in
  such technology so I am resolving this as "won't fix". You can look at
  the Semantic Search feature in SQL Server 2012 to see if that meets
  your needs.


What we see is impossible, so the logical assumption is that it's an illusion.

Multiple MySQL server instances can't listen on the same port.  They also can't share the same pid file, as these do, nor can the data files for InnoDB be shared.

The most sensible explanation for this is that the tools are showing you threads, not processes -- for whatever reason. Threads in Linux (if that's what this is) share the same process ID, but they also do have their own thread ID, a number that comes from the same numbering space as PIDs.

Most compelling, I suspect, is there are 20+ entries, each consuming 10.9% of available memory.  While it is highly unlikely for multiple identical instances if MySQL server to hold the same amount of memory (particularly the busy ones), it is impossible that your memory usage on this server is in excess of 200%.

mysql> SHOW FULL PROCESSLIST; when executed by a user with the SUPER or PROCESS privilege should show you the active client threads, and will likely contain entries representing to two that seem particularly busy, here.  Of course, there will be fewer in the output than are shown here, since not all threads are client threads.

Prove to yourself that there is really only one instance running, by repeatedly connecting and disconnecting and issuing SELECT CONNECTION_ID(), UUID_SHORT(); and/or by doing this on more than one connection at the same time. Both values will increment pretty much monitically (connection_id increments with each connection but remains the same while connected, uuid_short increments with each query) and will never be smaller.  This would be virtually impossible if multiple MySQL server daemon instances were involved.


For MySQL, is there tool to dump a set of rows from one tables and all related (by foreign key) rows from all related tables?

I think Jailer is what you are looking for:

http://jailer.sourceforge.net/

(Though I haven't used it so far)


"Lambda" function returns type string instead of type record

As lambda_record function returns RECORD and doesn't have OUT parameter, you must define the columns and types it will return when calling using an alias:

SELECT r.out1, r.out2
FROM lambda_record(...) AS r(out1 numeric, out2 numeric);

In this case r is the table alias, and out1/out2 the alias for the columns with the types defined (defining types is only necessary when it returns RECORD and doesn't have OUT parameters).

EDIT: even though it uses a RECORD, lambda_record still calls the function as it had only one column returned, using SELECT func_name(params...) method:

sql := format(
    'select %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

You could change that line to call it use SELECT * FROM func_name(params...):

sql := format(
    'select * from %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

That way you should already have it return the columns correctly (even for functions with a single column), and now you can call as I showed in the beginning.


Moving all Heap Tables to another Filegroup in SQL Server

As far as I know, you have two options:

Physically move the data.
Create a new empty heap in the secondary filegroup / Copy the data / rename the object, everything inside of implicit transaction. And don't forget to script out indexes, constraints etc.
Create a clustered index in the secondary filegroup, and then drop it.

I'd prefer the first option.


Does leaving queries open in SSMS lockup memory on the SQL Server?

The only significant memory that would be held would be if SSMS was being run on the server itself (and then you'd still be limited to the 2GB of memory space that SSMS can allocate).

There will be a very small amount of memory allocated to maintaining the connection in SQL, but that's truly trivial.

Of course, if you leave an open transaction, that's a whole other level of pain, but it doesn't sound as if that's the trouble your developers are trying to avoid (unless this is what they are trying to avoid, but came up with a PC way of telling them that).


Index Optimization for Datetime comparing day of week and hour

Per Verace's suggestion I created a test table with 10M records. The TLDR: Indexes on datetime functions need to be explicit, any conversion of that datetime column for joining/filtering will not be served by an index.

SELECT obs_ts AS weekday_indexed, obs_ts AS hour_indexed , *
INTO index_test
FROM observations
LIMIT 10000000;

CREATE INDEX ON index_test(EXTRACT('isodow' from weekday_indexed));
CREATE INDEX ON index_test(EXTRACT('hour' FROM hour_indexed));
CREATE INDEX ON index_test(obs_ts);
ANALYSE index_test;

Table size is 1.7 GB. All of the indexes are 214MB. From my tests the index on obs_ts is never used (probably because these comparisons involve changing the data-type of the timestamp column...). The function specific indexes are used, so there is definitely a performance-index size tradeoff.

Day of Week

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from obs_ts )=5
--3 secs 276 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from weekday_indexed)=5
--1 secs 586 msec.

Hour of Day

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from obs_ts )=5
-- Total query runtime: 2 secs 420 msec.

EXPLAIN ANALYZE SELECT COUNT(1)
FROM index_test
WHERE obs_ts::TIME >= '05:00' AND obs_ts::TIME < '06:00'
-- Total query runtime: 2 secs 391 msec.

SELECT COUNT(1)
FROM index_test
WHERE tx::TIME @> timerange('05:00', '06:00')
-- Total query runtime: 1 secs 928 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from hour_indexed)=5
-- 779 msec.


To avoid problems with cycles in your graph, I would suggest that you re-design your structure to:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_groups
( synonym_group_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_group_products
( synonym_group_id ... NOT NULL
, product_id NOT NULL PRIMARY KEY  -- A product belongs to exactly one group
,     CONSTRAINT ...
      FOREIGN KEY (synonym_group_id)
          REFERENCES synonym_groups (synonym_group_id)
,     CONSTRAINT ...
      FOREIGN KEY (product_id)
          REFERENCES products (product_id)
);

I created a Fiddle, I just used integer for keys since I don't know what identifies your entities. To get the synonyms of chiptune:
select p2.*
FROM products p1
JOIN synonym_group_products sgp1
    ON p1.product_id = sgp1.product_id
JOIN synonym_group_products sgp2
    ON sgp1.synonym_group_id = sgp2.synonym_group_id
JOIN products p2
    ON sgp2.product_id = p2.product_id
WHERE p1.product_name = 'chiptune'
  AND p1.product_id <> p2.product_id;


MySQL performance slows down when accessed from different regions via the Internet

If you have a lot of back-and-forth between your front-end and your database,  network link latency could kill your application's performance.

Measure ping times between your US front-end and your Brazilian database server. Your front-end needs to wait for that round-trip time for each and every database access (sometimes more than once per request).

If this is indeed your problem, then I'm afraid there is no easy fix for latency generally speaking (unless you can find a provider that has a more direct route to your Brazilian endpoint). Adding more "bandwidth" (Mb/s) won't solve high latency issues.
You need to keep your front-end closer to your database, or reduce the number of database calls.


pg_repack not organizing table by column

This problem is fixed on the version 1.4.1 of pg_repack.

github issue for reference


Availability Group Clustering - Cluster Group Preferred Owner

Yes, you need to add a DR site IP address for the cluster name.  It may not be critical if you aren't using the cluster name for anything, but it is certainly a best practice and considered the correct configuration.

You don't really need to be concerned about the core cluster resources (cluster name, fileshare witness, etc.) failing over to the DR site.  The cluster will run just fine if that occurs, and if the network to the DR site is interrupted, the two nodes in the local data center will arbitrate for ownership of the core resources and one will bring them online.  This will not cause the availability groups to go offline and come back online--that will only occur if the AG primary can't retain ownership of the AG resources.

Having said that, you can set the possible owners of the cluster name resource on the Advanced tab, but in the case where you need to fail over to the DR node, you would need to change it again in order for the resources to come online on the DR node.


Is there any benefit for natively compiled TVFs when used in non native modules?

it is usually much better to use inline table value functions instead of multi statement TVFs or scalar functions, even if they are natively compiled, so you should better try to rewrite them as inline (single SELECT statement).

Gail Shaw made a performance test with scalar functions. The native compiled version was about 10 % faster (3352 instead of 3814 millseconds, while the inline table value function only took 300 ms). So I would not waste time to optimize something for 10 % when I could reach 1266%...

Edit:
I made another test by myself comparing native compiled inline table value fuctions with common inline table value functions (none of them accessing a table, so they are only CPU bound).

The most time the native compiled version is about 10 % faster, sometimes slower (as usual, since the CPU metric fluctuates usually).

use tempdb
go
CREATE FUNCTION [dbo].[f_test]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING,
     NATIVE_COMPILATION
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3,
       calc.total

  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
CREATE FUNCTION [dbo].[f_test2]
(
   @nr int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
select L1.n n1, L2.n n2, L3.n n3,
       calc.total

  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
  where calc.total = @nr
go
create view v_test as
select L1.n n1, L2.n n2, L3.n n3,
       calc.total
  from (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L1
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L2
  cross join (select 1 as n
        union all select 2 as n
        union all select 3 as n
        union all select 4 as n
        union all select 5 as n
        union all select 6 as n
        union all select 7 as n
        union all select 8 as n
        union all select 9 as n
        union all select 10 as n

        ) AS L3
  cross apply (SELECT L1.n * 100 + L2.n * 10 + L3.n as total) as calc
go
set statistics time on
create table #t (total int, n1 int, n2 int, n3 int)
go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test(v.total) as f
 where v3.total <= 20

go
insert into #t
select f.total, f.n1, f.n2, f.n3
  from dbo.v_test as v
 cross join dbo.v_test as v2
 cross join dbo.v_test as v3
 cross apply dbo.f_test2(v.total) as f
 where v3.total <= 20
 GO
 truncate table #t


How to do this binary appearance analysis

Assuming data type jsonb and current Postgres 10.

Exact matches are simpler, since you can use the built-in jsonb contains operator @>:

SELECT id
     , (websites @> '[{"url": "http://wikipedia.org"}]')::int AS "wikipedia.org"
     , (websites @> '[{"url": "http://google.com"}]'   )::int AS "google.com"
     , (websites @> '[{"url": "http://example.com"}]'  )::int AS "example.com"
FROM   results
ORDER  BY id;

The cast to integer only to meet your desired form of 1/0. Or omit the cast and just use the boolean t/f instead.

Pattern matching is more sophisticated, since there is no direct jsonb operator:

SELECT id
     , count(*) FILTER (WHERE url ~ '^https?://wikipedia\.org\M') AS "wikipedia.org"
     , count(*) FILTER (WHERE url ~ '^https?://google\.com\M')    AS "google.com"
     , count(*) FILTER (WHERE url ~ '^https?://example\.com\M')   AS "example.com"
FROM   results r
LEFT   JOIN LATERAL (
   SELECT elem->>'url' AS url
   FROM   jsonb_array_elements(r.websites) w(elem)
   ) w ON true
GROUP  BY id
ORDER  BY id;

db<>fiddle here

Replace count(*) FILTER ... with bool_or(url ~ '^https?://wikipedia\.org\M') etc. if URLs are never contained more than once and/or you are not interested in the actual count.

Related:

Search for nested values in jsonb array with greater operator
Indexed range comparison of array key's value in row inside JSONB
Return counts for multiple ranges in a single SELECT statement
Collapsing a column to yes/no on query
What is the difference between LATERAL and a subquery in PostgreSQL?


Database design for a simple association

No, adding a year table would add nothing to this data model.

Think about what a Year table would be like. What columns might it have? It really only has the year number itself - 2018, 2019 .. 2031, 2032 etc. Cards would then have a foreign key to this table, which the currently existing column Cards.Year already represents.

If you were doing a logical data model you may choose to put the Year entity type in it. For a physical database implementation & table design there is no need to implement a Year table.

One caveat - if your years are not calendar years, and you care about the calendar dates that each membership year starts and end, then having a separate Year table makes sense. Think about the tax year. It starts in, say, calendar April and runs to the following calendar March. So tax year and calendar year do not cover the same set of days.


Which AWS RDS; current t3.medium over CPU baseline, but not sure what's the next best option

Analysis of STATUS and VARIABLES:
Observations:

Version: 5.6.10
4 GB of RAM
Uptime = 68d 06:45:29
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:
innodb_buffer_pool_size could be increased, but not so much as to cause swapping.  (If you don't have much data, then increasing it won't make any difference.)
If the disk is SSD, increase innodb_io_capacity to 500.  (The I/O seems low, so this may not have any impact.)
max_allowed_packet is 12% of RAM; suggest lowering it to 1% of RAM unless you need a huge packet size.
Some things indicate very little I/O, as most of the work is in the CPU.  This further pushes toward studying the slow queries.
The Query cache (query_cache_type) is ON.  And there is some indication of heavy activity with it.  This may be adding to the CPU for "pruning" or it may be helping with the CPU.  (I can't tell which.)  Suggest you change to DEMAND; at the same time, add SQL_CACHE to queries that are likely to benefit from the QC and SQL_NO_CACHE to the others.  This goes right after SELECT.  These changes will make the QC more focused, thereby (hopefully) decreasing the CPU usage on both sides.
If you turn on the slowlog (which I recommend), be sure to lower long_query_time to, say 1.
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 22 * 1024 / 16M = 0.13% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 16777216) to avoid unnecessary memory usage.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 1513M / 0.70)) / 4096M = 54.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( innodb_change_buffering ) = innodb_change_buffering = none
-- Pre-5.6.11 / 5.5.31, there was a bug that made ="changes" a safer option.
( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.
( Innodb_os_log_written ) = 512 / 5899529 = 0.31 /HR -- This is an indicator of how busy InnoDB is.
-- Very idle InnoDB.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 512 / (5899529 / 3600) / 2 / 48M = 3.1e-9 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,899,529 / 60 * 48M / 512 = 9.67e+9 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)
( Handler_rollback ) = 68,089,346 / 5899529 = 12 /sec
-- Why so many rollbacks?
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( max_allowed_packet ) = 512M / 4096M = 12.5%
-- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 1586495488) to make room. Swapping is terrible for performance.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 4096M = 46.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Qcache_lowmem_prunes ) = 142,717,868 / 5899529 = 24 /sec -- Running out of room in QC
-- increase query_cache_size (now 88158208)
( Qcache_lowmem_prunes/Qcache_inserts ) = 142,717,868/210841277 = 67.7% -- Removal Ratio (frequency of needing to prune due to not enough memory)
( Qcache_not_cached ) = 624,955,002 / 5899529 = 105 /sec -- SQL_CACHE attempted, but ignored
-- Rethink caching; tune qcache
( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 624,955,002 / (608264262 + 835804146 + 624955002) = 30.2% -- Percent of SELECTs that were not cached in the QC.
-- QC is not very useful.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (88158208 - 1227312) / 34276 / 8192 = 0.31 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 8192)
( Select_scan ) = 171,787,477 / 5899529 = 29 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 171,787,477 / 835804146 = 20.6% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( relay_log_space_limit ) = 1,000,000,000 = 953.7MB -- The max total size for relay logs on a Replica. (0=unlimited)
-- Let's discuss the rationale for having a limit.
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
Abnormally small:
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.0118
Innodb_buffer_pool_bytes_data = 262 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_data_fsyncs = 0
Innodb_data_read = 192 /sec
Innodb_data_reads = 1.3 /HR
Innodb_data_writes = 1.3 /HR
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 1.3 /HR
Innodb_data_written = 0
Innodb_dblwr_pages_written = 0
Innodb_log_write_requests = 0
Innodb_os_log_fsyncs = 0
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0.0MB
Innodb_pages_read = 42 /HR
Innodb_pages_read + Innodb_pages_written = 42 /HR
Innodb_pages_written = 0
host_cache_size = 128

Abnormally large:
(query_cache_size - Qcache_free_memory) / query_cache_size = 98.6%
1 - Qcache_free_memory / query_cache_size = 98.6%
Com_create_trigger = 0.00061 /HR
Com_drop_trigger = 0.00061 /HR
Com_empty_query = 0.06 /HR
Com_flush = 21 /HR
Com_purge_before_date = 12 /HR
Com_rename_user = 0.00061 /HR
Com_revoke = 0.0012 /HR
Com_show_tables = 1.6 /sec
Handler_read_last = 1.6 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Qcache_total_blocks = 118,191
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 14,123
Ssl_accepts = 172
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 166
Ssl_session_cache_misses = 172
Ssl_verify_depth = 1.84e+19
Ssl_verify_mode = 5
back_log / max_connections = 75.6%
innodb_stats_persistent_sample_pages = 128
table_definition_cache = 5,383

Abnormal strings:
core_file = ON
ft_boolean_syntax = + -><()~*:\"\"&
innodb_checksums = OFF
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
log_output = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
thread_handling = multiple-connections-per-thread
time_zone = Pacific/Auckland


Query - Want the count of ID based on the LAST DATE it appears

So here's what i did --

For # 1:
I used the answer given here: need help with this query

For #2 Here's what I did:

CREATE VIEW MaxDateStockNo(StockNo, Date) AS
SELECT StockNo, MAX(Date)
FROM my_table
GROUP BY StockNo

Then I ran a second query
SELECT Date, Count(*)
FROM MaxDateStockNo
GROUP BY Date

Now Any idea how i can get both of these Querys into one so that the output is just Date, Query 1 Result, Query 2 Result?

Thanks!


Stored Procedure taking longer than a normal query

It is usually better to cleanse the data before inserting it into the database.
The query can be sped up in both cases by some rearranging; see below.
Is this the only difference? -- CompanyID = 931 versus CompanyID = Company_ID
Is CompanyID declared BIGINT?
Can you get EXPLAIN output, both in the SP and outside, to help verify the theory that it is easier to work with a number than a parameter passed in.
If you know that there will be no duplicates, use UNION ALL, which is faster.

By moving the WHERE into each SELECT but leaving the `ORDER BY outside, the query is likely to be faster:

( SELECT ... WHERE ... )
UNION [DISTINCT|ALL]
( SELECT ... WHERE ... )
ORDER BY ...

Of the main problem is 931 versus Company_ID, then the solution may involved constructing the query and using prepare and execute.


Getting the value from a column during a trigger

Triggers in SQL Server are fired once per set, not per row (like Oracle), so you need to build triggers that handle this.

In all triggers, you have two special tables called inserted and deleted, that contain  a virtual "mirror" of the entire table row(s) after and before the action that triggered it, respectively.

A word of warning: triggers should be small and very light-weight. In your case, running a cursor over your log rows and executing a stored procedure for each row can cause you locking mayhem and slow performance. (Triggers run synchronously, in the same transaction as the original DML statement.)

I would write your solution not in a trigger, but as a separate stored procedure and run it on-demand.


Recommended page file size for SQL 2008R2 on Windows 2008R2

There are no special settings for SQL Server which only uses physical memory normally

Just do what MS say for Windows and that's it

Oh, and buy more RAM anyway while we're one the subject... ;-)


The character set of an Oracle database is set at the database level, not tablespace level.

You have 3 options:

Recreate the database with the correct character set
Convert the database to AL32UTF8. Documentation link
Change the databases National Character Set to AL32UTF8 & change your application to use
NVARCHAR2()/NCHAR() datatypes instead of VARCHAR2() (documented at the same link as above)


Maybe a compatibility level will help you. You can define it in the options of your database under compatibility level. Change it to a version earlier of your current sql server at home. This way I'm sure it will be correctly used in your school.


Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6

Fixed it. Listening to /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found, I took a peek inside. Turns out this is the script that's called when the startup item or the preference pane want to start or stop MySQL. Unfortunately, it doesn't seem to work out of the box anymore.

The comments near the top of the script seem to know this, and give you a list of options to make the script work. I took the easiest and added the installation path to the basedir variable assignment.


Running select filename from dbo.sysfiles returns short foldernames with tilde

If you use the non-deprecated equivalent sys.database_files view's column physical_name, does it provide you the full path?


What are the different ways to keep track of active and archived data?

The two options that spring to mind avoiding triggers are:

Flag the deleted rows as such with an extra field, and update all your existing code to deal with it. This is "simple" but will touch a lot of your code so can be a bug source. Delete is not delete, mearly "set is_achived flag".
Flag the deleted rows in the table, and have views for active and archived data. This avoids changing existing code as you can give the combined table a new name (users_all) and the view of active users (SELECT <fields> FROM users_all WHERE is_archived=0) the name of the old table. Code such as audit reports simple need to query users_all directly. You will need to run a few tests to make sure mysql's query planner handles this efficiently though - I've had it work well is MSSQL but don't use mysql myself so have no expectations one way or the other about it's QP's intelligence.
Separate tables for active and archived. The delete operation then becomes an insert into archived and a delete from active (be sure to wrap this in an explicit transaction so if there is an error half way through you don't lose records completely or end up duplicating them). For code that needs to see both subsets, either UNION directly in the views/procs or have a view that is a UNION of the two tables and use that. This is likely to be less efficient than the above for those code paths that do need to deal with both subsets, but will not affect other areas of the codebase so may be preferable. One key disadvantage with this option is not being able to use the two tables in FK relationships - you can get around this by adding a third table containing just the identifiers for each row, then all tables that contain links to users (including the active users and archived users tables) would use that as the FK source - the only extra work in your business logic as a result would be to create that row before creating the row in the users table.

Triggers can be very powerful, but you need to design them very carefully as they can be a performance problem if not, and of course silent things happening can be a problem when bugs to arise as it can be difficult to see everything that is going on at first glance. they can greatly simplify some processes though, creating easier to maintain structures as a result, and sometimes having a bit of structural logic in the DB is far more efficient than having the next layer up deal with it, so never discount them out of hand. In this instance though, I'd stick with one of the simple boolean flag options.


How to reference column of DAX variable?

In DAX you can't use a variable name as with tables that are defined in the model.
What you can do is to use the original table names using the table variable as a filter.
For instance like in the following code
DEFINE
    VAR tv1 =
        FILTER( Tab1, Tab1[F1] = "A" )
EVALUATE
ROW( "a", CALCULATE( COUNTBLANK( Tab1[Value] ), tv1 ) )


Cannot Attach Database to SQL Server Management Studio 2008 R2

Something seems not quite right with your question.

SQL 2008 is version 655
SQL 2008R2 is version 661

I would expect that you would only see that error if you are attempting to attach a 2008R2 DB to 2008, not vice versa.

As soon as you attach a DB to 2008 R2, the version is changed to 661 and can no longer be attached to a 2008 server.


Looking at physical IO is not a good indication of what work your query is doing. SQL Server does not write changes to disk immediately--it writes to the buffer cache (in memory), then flushes dirty pages to disk during the next checkpoint. Likely, all 1000 inserts will fit into memory, and no writes will happen on the data file until the next checkpoint.

Instead, try looking at the wait type that you are experiencing when you experience slowness for this operation.

If you are experiencing WRITELOG waits, try wrapping the WHILE loop into an explicit transaction. This would reduce the number of times SQL Server flushes the log buffer to disk, and improve performance.

You can then investigate sys.dm_tran_database_transactions and sys.dm_io_pending_io_requests to zero in on whether the IO is pending at the OS or disk subsystem.

Before digging too deeply into the DMVs, you might try looking into the following things:

Is your log file properly sized? Do you have an appropriate number of VLFs?
Use Perfmon to look at the PhysicalDisk object to look at Avg. Disk sec/Read, Avg. Disk sec/Write, and Avg. Disk Queue Length. Are IOs taking longer than 15ms? Is your queue length larger than 1?
Move the transaction log to a different physical disk to segregate IO.

Assuming your disk subsystem is not the bottleneck (and you seem confident that is not the problem), then the first place I would look would be at the number of VLFs in your log file.


is it possible to trim the table output for a specific job?

Yes.

The CommandLog cleanup is from Ola Hallengren's Maintenance Solution, so doesn't affect msdb history directly.

Use sp_purge_jobhistory, like so:

sp_purge_jobhistory
   {   [ @job_name = ] 'job_name' |
     | [ @job_id = ] job_id }
   [ , [ @oldest_date = ] oldest_date ]

Per the documentation, 'job_name' is the name of the job for which to delete the history records. Either job_id or job_name must be specified, but both cannot be specified.


ms sql query multiple table colums as identical tag (sequel)

That is a bit of odd looking XML you want to produce. You are relying on element position to distinguish what value is what.

declare @T table
(
  ID  int,
  Name varchar(10),
  Age int,
  HairColor varchar(10),
  HasCar bit
);

insert into @T(ID, Name, Age, HairColor, HasCar) values
(1, 'Bert' , 25, 'blonde'  , 1),
(2, 'Agnes', 61, 'white'   , 0),
(3, 'Diane', 43, 'brunette', 1);

select row_number() over(order by T.ID) as '@Group',
       T.ID as 'MyTag/@Value',
       null,
       T.Name as 'MyTag/@Value',
       null,
       T.Age as 'MyTag/@Value',
       null,
       T.HairColor as 'MyTag/@Value',
       null,
       T.HasCar as 'MyTag/@Value'
from @T as T
for xml path('MyGroupingTag'), root('MyRootElement');

This might generate a better version of your XML.

select row_number() over(order by T.ID) as '@Group',
       'ID' as 'MyTag/@Name',
       T.ID as 'MyTag/@Value',
       null,
       'Name' as 'MyTag/@Name',
       T.Name as 'MyTag/@Value',
       null,
       'Age' as 'MyTag/@Name',
       T.Age as 'MyTag/@Value',
       null,
       'HairColor' as 'MyTag/@Name',
       T.HairColor as 'MyTag/@Value',
       null,
       'HasCar' as 'MyTag/@Name',
       T.HasCar as 'MyTag/@Value'
from @T as T
for xml path('MyGroupingTag'), root('MyRootElement');

Result:

<MyRootElement>
  <MyGroupingTag Group="1">
    <MyTag Name="ID" Value="1" />
    <MyTag Name="Name" Value="Bert" />
    <MyTag Name="Age" Value="25" />
    <MyTag Name="HairColor" Value="blonde" />
    <MyTag Name="HasCar" Value="1" />
  </MyGroupingTag>
  <MyGroupingTag Group="2">
    <MyTag Name="ID" Value="2" />
    <MyTag Name="Name" Value="Agnes" />
    <MyTag Name="Age" Value="61" />
    <MyTag Name="HairColor" Value="white" />
    <MyTag Name="HasCar" Value="0" />
  </MyGroupingTag>
  <MyGroupingTag Group="3">
    <MyTag Name="ID" Value="3" />
    <MyTag Name="Name" Value="Diane" />
    <MyTag Name="Age" Value="43" />
    <MyTag Name="HairColor" Value="brunette" />
    <MyTag Name="HasCar" Value="1" />
  </MyGroupingTag>
</MyRootElement>

The null between columns in the column list is there to separate MyTag into different elements.


Is there a MySQL maximum database or table count?

Let's look at many perspectives
PERSPECTIVE #1 : SELECTs against MyISAM tables
If you are doing only SELECTs, you need to run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the correct size for the MyISAM Key Cache. This maxs at 4GB for 32-bit. You can go higher on 64-bit machines. Yet, use common sense based on the amount of RAM the DB Server has. This should be a concern for you because only the pages from the .MYI are cached. Any thing from the .MYD must be read from disk over and over again.
If the amount of RAM recommended from this query far exceeds the amount of RAM installed by orders of magnitude, just set key_buffer_size to 4G and call it a day on this perspective.
PERSPECTIVE #2 : Leveraging Different Row Formats for MyISAM tables
Altering MyISAM tables to use a FIXED row format can increases overall performance for SELECTs. Why ?
Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of their VARCHAR counterparts. This is not any conjecture on my part.
The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:
ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces are VARCHARs to have the fixed amount of space allocate like that of a CHAR. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%.
Of course, by changing the ROW_FORMAT to FIXED, you will be double the MyISAM table's size in the worst case scenario. If you choose to do this, make sure you have sufficient diskspace.
PERSPECTIVE #3 : Limits on the Number of Open Files from mysqld's vantage point.
Whenever you startup mysqld, many variables are set based on /etc/my.cnf. The rare exception is the option open_files_limit. If my.cnf does not have this setting, mysqld will attempt to compute the best number for this option such that mysqld can still operate and leave whatever files handles available to the OS.
You can set this number higher strictly at your own risk.
PERSPECTIVE #4 : Disk Considerations
Since your storage engine of choice is MyISAM, you must have fast reading disks since data pages are never cached. Please use RAID10 sets. They are easier to do disk maintenances on than other setups. You can could also go with SSD drives. Whatever you choose, prepare for aging disks and periodic disk maintenances. Make sure your RAID cards have the latest firmware upgrades.
SUMMARY
All the information you supplied must be examined from these 4 perspectives. You must strike a good balance and may have to make concessions on your choices based on either budget, available hardware, amount of diskspace, and overall limitations of MyISAM that you have not anticipated.


Availability Group Read Only Routing Not Working for SQL Logins

The issue was not in SQL or Windows Authentication methods, but in default_database setting
Found that for my Windows Authentication Login, default_database = [MyDatabase] (which participates in availability group), while my SQL Authentication Login has default_database = [master]
Because of above setting, while connecting in SSMS using Windows Authentication, Read Only Routing worked right after ApplicationIntent=ReadOnly was specified. For the SQL Authentication Login, not only ApplicationIntent=ReadOnly needs to be specified, but also it should contain Initial Catalog=[MyDatabase]
Adding Initial Catalog=[MyDatabase] fixed the issue, SQL Authentication Login could  connect and was directed to Read-Only secondary


postgresql does not seem to respect the pg_hba.conf config - how can I diagnose this?

If you have restarted the database and the contents of the view pg_hba_file_rules doesn't match your pg_hba.conf file, then you probably have more than one such file, and edited the one which is not being used.  If you can connect via another means, you can use show hba_file; to get PostgreSQL to tell you which file is the one that is being used.


How to add a linked server to a sql server management studio?

Linked Servers are not database specific, but provide a pipeline between two servers.  Of course, the rights granted to the users will control which databases they can access.  The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query.

Yes, creating a Linked Server is an administrative function and not available to all.  Doing it yourself requires the ALTER ANY LINKED SERVER permission.

The parameters for the sp_addlinkedserver stored procedure are found at: http://msdn.microsoft.com/en-us/library/ms190479(v=sql.110).aspx

Also, under that link are instructions for several different ways to connect the servers together.  If Server X and Server A are both SQL Servers, the setup is quite simple.

You can use sp_addlinkedsrvlogin to manage how the logins authenticate across the linked server.   The details are at: http://msdn.microsoft.com/en-us/library/ms189811(v=sql.110).aspx

Depending on your landscape and if you are using Windows Logins, you may need to have Kerberos configured properly on your landscape so that the Kerberos ticket (allowed permissions) can be forward to the other server.  See: Configuring Linked Servers for Delegation:

http://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx


I am not very clear on your question however you can alter your code as below for your null question:

select U.Mail, count(isnull(P.ID, 0)) as PostCount,
    case
        when count(P.ID) = 0 then 'none' --doesn't work
        when (count(P.ID) <= 2 and count(P.ID) > 0) then 'few'
        when (count(P.ID) <= 5 and count(P.ID) > 2 )then 'medium'
        else 'many'
    end PostCountCategory
from Users U
left join Tag T on U.Mail = T.Mail
left join Post P on T.IDPost = P.ID
group by U.Mail

Alternatively you can use count(isnull(P.ID, 0)) inside your case statement. Hope this helps.

As suggested by Mustaccio, you may use coalesce also instead of isnull.


Relate few records in table to eachother

Ok, so as I forgot about one more important thing: I use soft deletes, I decided to:
- Create new table containing only autoincrementing unique id and nullable datetime containing date of soft delete
- Add nullable foreign key linking my main table to the new one with ondelete cascade to sort out hard deletes
- When batch adding create new records first create the bundle entity and link them to it on creation
- Make my bundle entity cascade soft deletes to all related records
- When (soft) deleting entity from main table call proper function on bundle entity (if exists) or main entity otherwise

This way everything works as I expected, but the extra table still hurts my eyes and I don't find it the solution, just something that works, but I wanted to avoid


Matching Custom Exported CSV File with Main MySQL Database

Load the Filtered output into a Temp table with columns User, Counter, ...  Then do something like

SELECT ID, User, Country, ...
    INTO OUTFILE ...
    FROM MyTable AS m
    LEFT JOIN Temp AS t  ON USING(User, Country, ...)
    WHERE t.User IS NULL;


SQL Server memory issues after upgrade from Express

max server memory is the maximum you will allow SQL to use.  This is primarily set to leave memory for other process like the operating system, or if you have multiple instances on one machine.

  Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. Source

SQL only uses as much memory as it needs, so it is not unusual to see memory usage much lower, when SQL does not need it.

Even when you think SQL should use more CPU, sometimes it chooses not to. I have some index reorg jobs that I want to work harder, but they will never use more then 10 or 20% of CPU.  See for yourself, make several "Ugly" indexes and then use the tool of your choice to fix them.

Setting Max at 12GB when you have 16GB is a well balanced choice in most single instance configurations.


How to identify where a package execution has spent most of the time?

Method 1 :  YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012:

  '...
  '   Declare and instantiate objects here.
    Dim app As New Application
  '...
  ' Create a RunningPackages collection, named pkgs, and fill it
 ' with the running packages from the application object.
    Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)

 ' Enumerate over each package in the collection and display some data.
     For Each package As RunningPackage In pkgs
    Console.WriteLine("InstanceID: " & package.InstanceID.ToString())
    Console.WriteLine("PackageDescription: " & package.PackageDescription)
    Console.WriteLine("PackageID: " & package.PackageID.ToString())
    Console.WriteLine("PackageName: " & package.PackageName)
    Console.WriteLine("UserName: " & package.UserName)
     Next
     '   Insert more code here.

Method 2: Refer to below excellent read from Jamie Thomson which have neat and clean scripts to provide info on you're running packages:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

Method 3: YOu can use below SQL code which i've been using:

     SET NOCOUNT ON
  -- Check if the SQL Server Agent is running
    IF EXISTS ( SELECT  1
        FROM    MASTER.dbo.sysprocesses
        WHERE   program_name = N'SQLAgent - Generic Refresher' )
BEGIN
    SELECT  @@SERVERNAME AS 'InstanceName' ,
            1 AS 'SQLServerAgentRunning'
END
 ELSE
     BEGIN
    SELECT  @@SERVERNAME AS 'InstanceName' ,
            0 AS 'SQLServerAgentRunning'
    RAISERROR('The SQL Server Agent is not running.', 16, 1) WITH SETERROR ;
END
     -- Execute the script
    IF EXISTS ( SELECT  *
        FROM    tempdb.dbo.sysobjects
        WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]') )
DROP TABLE [tempdb].[dbo].[Temp1]
GO
   CREATE TABLE [tempdb].[dbo].[Temp1]
(
  job_id UNIQUEIDENTIFIER NOT NULL ,
  last_run_date NVARCHAR(20) NOT NULL ,
  last_run_time NVARCHAR(20) NOT NULL ,
  next_run_date NVARCHAR(20) NOT NULL ,
  next_run_time NVARCHAR(20) NOT NULL ,
  next_run_schedule_id INT NOT NULL ,
  requested_to_run INT NOT NULL ,
  request_source INT NOT NULL ,
  request_source_id SYSNAME COLLATE database_default
                            NULL ,
  running INT NOT NULL ,
  current_step INT NOT NULL ,
  current_retry_attempt INT NOT NULL ,
  job_state INT NOT NULL
)
 DECLARE @job_owner SYSNAME
  DECLARE @is_sysadmin INT
  SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
 SET @job_owner = SUSER_SNAME()
 INSERT  INTO [tempdb].[dbo].[Temp1]
    EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE  [tempdb].[dbo].[Temp1]
  SET     last_run_time = RIGHT('000000' + last_run_time, 6) ,
    next_run_time = RIGHT('000000' + next_run_time, 6) ;
-----
   SELECT  j.name AS JobName ,
    j.enabled AS Enabled ,
    CASE x.running
      WHEN 1 THEN 'Running'
      ELSE CASE h.run_status
             WHEN 2 THEN 'Inactive'
             WHEN 4 THEN 'Inactive'
             ELSE 'Completed'
           END
    END AS CurrentStatus ,
    COALESCE(x.current_step, 0) AS CurrentStepNbr ,
    CASE x.running
      WHEN 1 THEN js.step_name
      ELSE NULL
    END AS CurrentStepName ,
    CASE WHEN x.last_run_date > 0
         THEN CONVERT (DATETIME, SUBSTRING(x.last_run_date, 1, 4) + '-'
              + SUBSTRING(x.last_run_date, 5, 2) + '-'
              + SUBSTRING(x.last_run_date, 7, 2) + ' '
              + SUBSTRING(x.last_run_time, 1, 2) + ':'
              + SUBSTRING(x.last_run_time, 3, 2) + ':'
              + SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
         ELSE NULL
    END AS LastRunTime ,
    CASE h.run_status
      WHEN 0 THEN 'Fail'
      WHEN 1 THEN 'Success'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Cancel'
      WHEN 4 THEN 'In progress'
    END AS LastRunOutcome ,
    CASE WHEN h.run_duration > 0
         THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
              + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
                                                          / 100 % 100 )
              * 60 + ( h.run_duration % 100 )
         ELSE NULL
    END AS LastRunDuration ,
    js.command AS SSISPackageExecutionCommand
     FROM    [tempdb].[dbo].[Temp1] x
    LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
    JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
    LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id
                                                AND x.last_run_date = h.run_date
                                                AND x.last_run_time = h.run_time
                                                AND h.step_id = 0
 WHERE   x.running = 1
    AND js.subsystem = 'SSIS'

 DROP TABLE [tempdb].[dbo].[Temp1]


mariadb won't start (InnoDB error: space header page consists of zero bytes in data file ./ibdata1)


Should I create two endpoints for two availability groups on the same SQL Instance?

If you read Prerequisites, Restrictions, and Recommendations for Always On availability groups it says

Each server instance requires a database mirroring endpoint. Note that this endpoint is shared by all the availability replicas and database mirroring partners and witnesses on the server instance.

An endpoint is for instance of SQL Server and all Availability Groups configured within that instance can use this endpoint.


What is the size of currently-existing tmp tables in innodb?

The only way to see the temp table needed would be to micromanage the process.

Here is how:

USE mydb
CREATE TABLE anonymized_table_name_new LIKE anonymized_table_name;
ALTER TABLE anonymized_table_name_new ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;
INSERT INTO anonymized_table_name_new (list of all columns except anonymized_column_name)
SELECT * FROM anonymized_table_name;
ALTER TABLE anonymized_table_name RENAME anonymized_table_name_old;
ALTER TABLE anonymized_table_name_new RENAME anonymized_table_name;
DROP TABLE anonymized_table_name_old;

After the third command you can run

SELECT (data_length+index_length)/power(1024,3) tmptablesize_GB
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='anonymized_table_name_new';

This may not be all that useful because anonymized_table_name_new may be about the same size as anonymized_table_name since anonymized_column_name has no value. Notwithstanding, you would run that query to see the temp table size in gigabytes.


Store server password in table for reading with CURSOR

It's definitely a bad practice and a bad idea to store any kind of passwords in plain text in the database, let alone the specific passwords for access to your other servers. Database servers are frequent targets of attacks, and storing your password in an unobfuscated and unencrypted manor makes them so much easier of a target.
I've seen a couple of your questions on here, and it makes me think that you'd best be served by centralizing your data into one server. A couple options to do that you can look into are Replication and SSIS. (AlwaysOn Availability Groups is another feature you can use to synchronize data across servers as well, but might be overkill for your use cases.)
Otherwise if you want to continue to query through Linked Servers you should only have to set them up once on your primary server, and not need to store or use the password to access them. You can follow either this guide or the Microsoft Docs on how to setup a Linked Server appropriately.
After they're setup, you can access entities from them using the four part naming scheme like so: SELECT SomeField FROM LinkedServerName.DatabaseName.SchemaName.TableName


migrating to a bigger server - how to copy over differences

Change Data Capture is meant to capture changes that happen in tables and offers you a way to then read what those changes are to synchronize data to a target server.


how to give access to view only one database to a particular users in sql server 2008

Method 1:

If you want users to View only,

USE <database name here>
GO
GRANT VIEW Definition TO User1

USE <database name here>
GO
GRANT VIEW Definition TO User2

and so on for all 5 of them

Method 2:

If you meant to give them read only access

Step 1:

In case for domain account users, create a login from windows account with below( for the users you want to give access to say Database A):

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;
GO

Else (in case not windows account)

 CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
GO

Step 2: Perform below for all 5 users to grant login permission to access the database in question

Say for user 1:

 USE (your database name)
 CREATE USER (username1) FOR LOGIN (login name)

Similarly for user 2

USE (your database name)
 CREATE USER (username2) FOR LOGIN (login name)

and so on for all 5..

Step 3: Once you have users added in your database, you can give them any rights you want, e.g. you could assign it the db_datareader database role to read all tables.

USE (your database)
EXEC sp_addrolemember 'db_datareader', '(username1)'

.... for all 5


Returning data where all categories exactly match

You cqan use the IN clause for that
SELECT
`id`, `doc_title`, `doc_version`
FROM Documents WHERE id in (SELECT `id`
                            FROM Categories
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

CREATE TABLE Documents (
  `id` INTEGER,
  `doc_title` VARCHAR(10),
  `doc_version` INTEGER
);

INSERT INTO Documents
  (`id`, `doc_title`, `doc_version`)
VALUES
  ('1', 'item 1', '4'),
  ('2', 'document 2', '3');

CREATE TABLE Categories (
  `id` INTEGER,
  `cat_id` INTEGER,
  `cat_type` INTEGER
);

INSERT INTO Categories
  (`id`, `cat_id`, `cat_type`)
VALUES
  ('1', '1', '3'),
  ('1', '7', '2'),
  ('1', '4', '2'),
  ('2', '7', '2');

✓

✓

✓

✓

SELECT
`id`, `doc_title`, `doc_version`
FROM Documents WHERE id in (SELECT `id`
                            FROM Categories
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

id | doc_title  | doc_version
-: | :--------- | ----------:
 1 | item 1     |           4
 2 | document 2 |           3

db<>fiddle here


How to Filter values in two tables using inner join?

The first thing to do is a self JOIN on SalaryIncrement. This will be used to calculate the SUM of SalaryIncrease.

Query:

SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
FROM @SalaryIncrement s1
INNER JOIN @SalaryIncrement s2
    ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom <= s2.SalaryIncreaseFrom
GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease

Then this query only needs to be JOIN to Employee and it can do the math between the SUM and the current MonthlySalary (sample link).

Query:

SELECT sal.SalaryIncrementId, sal.SalaryIncreaseFrom, emp.MonthlySalary-sal.OldSalary, sal.AmountOfSalaryIncrease,  emp.MonthlySalary-sal.OldSalary + sal.AmountOfSalaryIncrease
FROM Employee emp
INNER JOIN (
    SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
    FROM SalaryIncrement s1
    INNER JOIN SalaryIncrement s2
        ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom <= s2.SalaryIncreaseFrom
    GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease
) as sal
    ON sal.EmployeeId = emp.EmployeeId
ORDER BY sal.EmployeeId, sal.SalaryIncreaseFrom

Output:

SalaryIncrementId   SalaryIncreaseFrom  OldMonthlySalary    AmountOfSalaryIncrease  NewMonthlySalary
2                   2012-02-05          7000,00             1000,00                 8000,00
3                   2013-05-05          8000,00             2000,00                 10000,00
5                   2014-08-08          10000,00            1500,00                 11500,00
6                   2016-03-01          11500,00            500,00                  12000,00
1                   2012-02-05          14000,00            5000,00                 19000,00
4                   2013-04-05          19000,00            1000,00                 20000,00


Proactively Determining Bad Plans/Poor Performance at a Query Level

Instead of developing a solution yourself you could consider upgrading to SQL Server 2017 and trying out the automatic tuning feature. Reproducing part of the documentation:

  Database Engine automatically detects any potential plan choice
  regression including the plan that should be used instead of the wrong
  plan. When the Database Engine applies the last known good plan, it
  automatically monitors the performance of the forced plan. If the
  forced plan is not better than the regressed plan, the new plan will
  be unforced and the Database Engine will compile a new plan. If
  Database Engine verifies that the forced plan is better than regressed
  one, the forced plan will be retained until a recompile (for example,
  on next statistics or schema change) if it is better than the
  regressed plan.

  Note: Any plans auto forced do not persist on a restart of the SQL
  Server instance.

This could make it easier to meet the "react to it before the users notice the performance degradation" option.


Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed)

SubQuestion 1: I don't know of any way to get rid of the 23 byte header. There is also a 24 byte header on the page. http://www.postgresql.org/docs/8.3/static/storage-page-layout.html

SubQuestion 2: This guide should help. As suggested, increasing maintenance_work_mem is advised, but also increasing checkpoint_segments should help (to reduce the frequency of checkpoints). You've set your work_mem at 64GB, which seems really high. I would suggest keeping it under 2GB even on a system like yours. work_mem is for doing sorts and operations of that nature. http://www.postgresql.org/docs/current/interactive/populate.html

SubQuestion 3: I am not able to find any information on the physical layout of indexes within the pages. I would create a temporary table, fill it with test data (1 million rows?), create a temporary index, get the size of the index from the system, then divide by the number of rows to get the average. I can't show you an SQLFiddle version because it doesn't allow access to system functions. Try these techniques: http://postgresql.cc/postgres-index-size

PS - 25 billion rows? That's more than I've ever dealt with. I'm in the 4 billion range for one particular table ... For comparison, other database servers, like SQL Server 2008+, have a minimum 9 byte header for each row, so you can't entirely get rid of it but I've always thought that PostgreSQL's 23 byte header was a bit excessive.


You should calculate this during your ETL process or create a calculated column on your fact table.  Then, aggregations will be easier and queries will be somewhat faster than calculating the values at runtime.


Creating an Oracle 11.2-Compatible Database in Oracle 12.2.0.1

Long story short:

If you want a 11.2.0.4 database, then use 11.2.0.4. Yes, I know, downloading 11.2.0.4 requires a valid support contract which most visitors here do not have, but you can still download a virtual machine with that version at Pre-built Virtual Machine for Oracle Data Integrator 12c Getting Started Guide.

And here is how to create a 12.2 database with compatible set to 11.2.0.4 (no, I did not even bother with DBCA):

[oracle@o71 ~]$ cat ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
*.audit_file_dest='/u01/app/oracle/admin/EX/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/EX/control01.ctl','/oradata/EX/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/EX'
*.db_name='EX'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EXXDB)'
*.filesystemio_options='setall'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
[oracle@o71 ~]$ echo ${ORACLE_SID}
EX

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 20:06:47 2018

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
SQL> create database;

Database created.

The above creates the database without any dictionary views and packages. I performed the next steps in UPGRADE mode, because without that, my database ended up with several invalid objects that could not be compiled at all.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/catalog.sql
...
SQL> @?/rdbms/admin/catproc.sql
...
SQL> @?/rdbms/admin/utlrp.sql
...
SQL> alter user system identified by oracle;

User altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/sqlplus/admin/pupbld.sql
...

And done.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 21:14:45 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views            12.2.0.1.0                     VALID
Oracle Database Packages and Types       12.2.0.1.0                     VALID
Oracle XML Database                      12.2.0.1.0                     VALID

SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
         0

SQL> create table t1 (id number generated always as identity);
create table t1 (id number generated always as identity)
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
ORA-00722: Feature "SQL identity columns"

Steps are from Creating a Database with the CREATE DATABASE Statement

I have seen people using 12.1.0.2 production data warehouses intentionally with optimizer_features_enable=9.2.0.8, but this was new...


Error when creating a stored procedure in postgreSQL-10

As mentioned in the comments, PostgreSQL-10 does not have stored procedures. Same is true about earlier versions.

Workaround alternatives:

Upgrade to version 11 or later
Use a function

Thanks, @Erwin Brandstetter and @a_horse_with_no_name


Restoring a backup of a database from SQL Server 2014 into SQL Server 2012

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server (2014) down to an older version (2012) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances
otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool
or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.


ORA-00338: log # of thread # is more recent than control file

It could be that you are opening using the wrong control file[s]. This is easily caused when using for example a parameter file in ASM - the correct one - and one in the default location $ORACLE_HOME/dbs/ that still has the old references in place after a migration to ASM. In that case search in ASM to the current control files.

In such a setup, normally the parameter is found by using srvctl config database -d YOUR_DB

During every opening attempt the log# is increased .... so after many attempts, the error can go away and the problem can still exist ....


MYSQL reduce column length without changing datatype

If you change the column length you still have to specify the data type in the ALTER TABLE statement, as well as NOT NULL, DEFAULT and COMMENT unless you want to use the defaults (i.e. NULL DEFAULT NULL without any comment):

CREATE TABLE table1 (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  col1 VARCHAR(45) NOT NULL DEFAULT 'a' COMMENT 'My favourite column'
);

ALTER TABLE table1 MODIFY COLUMN col1 VARCHAR(20) NOT NULL DEFAULT 'a' COMMENT 'My favourite column';


Indeed the bottleneck was IO. Since I was using tar -czf to compress the files and the CPU was so performant, the drive simply couldn't handle all the data coming so fast (although we use mirrored RAID volumes).

The mysqldump itself is no problem at all, but tar was slowing down the whole system massively.

I'm now using pv to shape the pipe stream:

tar -czf - ./ | pv -q -L 10m > output.tgz

-q disables output of pv.
-L 10m limits the write operation to 10 MB per second. Just test which value you should use on your server by watching the gzip process via top. I wanted it to have around 50% CPU usage, so the IO impact is lowered to 50% as well.
CPU impact is lowered implicitly, because pv slows down everything (except of the dump itself, it is done before compression).

Many thanks to @Michael - sqlbot and others for pushing me into the right direction.


Large MySQL table that need to index some fields?

If you just wish to create a single index, CREATE INDEX would be the way to go.

However, if you wish to setup your table with multiple indices, setting up a new empty table and then copying from your old to your new table is IMO a more practical solution, since you can get everything ready and then simply run the insert when it's convenient.