AlwaysOn - Running SQL Agent Jobs on Secondary read-only node writing audit information to database

No, it won't automatically write to the primary database instance. You need to connect to the primary instance and perform your data inserts on that instance (linked servers is one option to do this).

You also need to make sure you're running your jobs in the right location. For example, Transaction Log Backups can be offloaded to a secondary, but even a synchronous secondary can run behind the primary (sometimes by a lot of time). If that happens, are you going to be able to meet your RPO goals?

Index maintenance is going to have to run on the primary as it needs a writable database - you can't offload this task.

CHECKDB can be run on the secondary, but I think it should be run on all instances if at all possible. When replication happens it only transfers the log data, not data pages. If you have a disk subsystem corruption on one of your replicas that you don't run CHECKDB on then you may not find corruption until you failover.


BBDD are Bases de Datos in this funny way in which Spanish refers to plural for acronyms (EEUU, Estados Unidos = United States).
http://forum.wordreference.com/showthread.php?t=390785


Simple SELECT-query on big table gets stuck in "Sending data" state

I believe that for a small interval the index chosen by the query optimizer is the one that uses the timestamp, so it is faster.

I think a composite index class, timestamp would solve the problem.

Ps: Can you place the query plan when the range is small?


Oracle Select Query Over Database Link Suddenly Hangs Endlessly

We learned that in rare circumstances a distributed transaction fails and leaves some kind of "half lock" on one side of the distributed transaction. Because the other side was gone it never resolved. At least one of the common tools that looks up locks didn't see these locks. However, we were able to find them (I think we queried v$lock, I don't remember, it's been over a year since it last happened). To fix it we determined what process was running that session and our DBA killed the OS process with a kill -9. It was a rare problem and I haven't seen it in about 18 months, so it's possible that it is only something that happens on earlier versions of Oracle . (However, it usually happened only once or twice a year at most, so it could be we've just been lucky.)


Is there any way to tell SQL to stop using an index?

That would be disabling an index:

alter index IX_YourIndex
on dbo.YourTable
disable;
go

But that, too, will be running into concurrency issues on a highly transactional object, as that operation above still requires a schema-modify (Sch-M) lock on the object.

  Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index?

That would be the default behavior of SQL Server.  The only "command timeout" is going to be enforced by client-side software.  I'm going to assume that you're using some sort of GUI to try to delete/disable the index (SSMS maybe?).  Either way, if you were to put this in a new query window (SSMS), it would wait indefinitely for the locked resource to become available so it can be granted the necessary locking:

drop index IX_YourIndex
on dbo.YourTable;
go

This is one of the reasons why it's almost always easiest to do these types of operations during a slow time, or a maintenance window.  That typically can ensure little to no user activity, making these simple tasks...simple and quick.

And if you want to see what/who your drop index request is waiting on, you can run a query similar to this:

select
    session_id,
    command,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource
from sys.dm_exec_requests
where command = 'drop index';


Does Restore "From database" restore from the most recent backup of the database or the actual live database?

It will not pull from the live database. There is no SQL command for this (yet). It will give you a selection of recent backups you can pick from. You can only restore from a database snapshot and from a backup, not from a live database.


Is it possible to determine the default Collation and server version of a database without a full restore?

You can get some info by inspecting a backup file using the RESTORE HEADERONLY command. The linked documentation explains what all of the result set fields are and mean, but the ones you are looking for should be:

[SoftwareVersionMajor]

8 = SQL Server 2000
9 = SQL Server 2005
10 = SQL Server 2008 or 2008 R2 (see [SoftwareVersionMinor] for distinction)
11 = SQL Server 2012
12 = SQL Server 2014
13 = SQL Server 2016
14 = SQL Server 2017
15 = SQL Server 2019

If you might be getting SQL Server 2008 R2 backups, then you will also need [SoftwareVersionMinor] as this value should be "50" for that version, else "00" for all others (so far)
[Collation]

For example:

RESTORE HEADERONLY FROM DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'

That will return a result set. If you are executing this from app code, it should be fairly easy to grab those specific fields. If you are executing this from T-SQL, and you need to act on it programmatically, then you will need to:

create a local temporary table with all possible result set fields from that command
execute the RESTORE HEADERONLY command within an INSERT INTO ... EXEC(...) construct:

INSERT INTO #TempBackupInfo
  EXEC(N'RESTORE HEADERONLY FROM DISK =
  N''C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'';');

select the fields in question from that local temp table


UTF8mb4 MariaDB 10.4 - Max key length is 767 bytes error

I'd keep the utf8mb4 as the default.

You can use innodb_large_prefix=1 to get past the index length limitation.

Its also quite possible that symmetricDS doesn't actually need 255 length character field and its just a length for arbitrary default reasons.


Have you tried mysqldump using --single-transaction ? In this way it doesn't lock the table for threads doing SQL,DML operations provided no DDL statements should be issued and you will get the backups for conistent states only. This will be efficient  only for innodb tables. Below url might give some insights about the option.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction


That error would imply to me that there is a table layout difference between the publisher and the subscriber. Have any recent updates to the DB structure been applied to one but not the other?

You could run a DB comparison tool (something like the free edition of http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html should be more than sufficient) against the two databases to check for this.

You probably shouldn't be using SQL 2000 any more - it is somewhat old and the extended support period for its last release (SP4) ended in April this year.


No, your given index cannot be used for the ORDER BY.  You can see this by doing set enable_sort=off and then explaining the query, you get a sort anyway because that is the only way PostgreSQL can get the job done.

But if you create the correct index:

CREATE INDEX ON message_log (website,  in_use, insert_timestamp NULLS FIRST);

then it will be used for the ORDER BY.


The three NICs won't help with redundancy, because the VM layer should have already handled this configuration under the hood in the virtualization layer itself.

However, three NICs would allow for segregation of particular types of server traffic (backups, AG replication, etc.) away from the primary NIC, which allows you to monitor and profile the usage patterns of these NICs better.


Optimal design for 90mm row clickstream MySQL

OPTIMIZE TABLE is almost always not worth doing.

INDEXes are your friend -- they can (in many cases) make queries run orders of magnitude faster.  Provide a Slow query, plus SHOW CREATE TABLE, and we can help you.  Or study my cookbook.  As that says, picking the 'right' index (perhaps a composite index) requires that you first have some clues of the SELECTs you will be doing.

"Rightsizing" datatypes is a good idea, especially when first creating the tables.  Smaller --> more cacheable --> less I/O --> faster.

Two tables (visit info and page info)?  If they are 1:1, usually 1 table is 'right'; if they are 1:many, then two tables is a must.


How to reduce the required frequency of ANALYSE on a postgres table?

Try to use Analyze without stopping the process. I think you already tried this, wny is this not working ?
One option is to Analyze only specific columns , this will take less than analyzing the whole table.
Usually autovacuum is active on a postgres database, having this enabled is allowing the db to to the Analyze in the background.

Check :
https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#AUTOVACUUM

This is the formula when an analyze is run :

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

The params are here :

https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD

https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR

The values can be set on the table level , no need to have the same rule for all tables.

ALTER TABLE a_lot_of_inserts SET ( autovacuum_analyze_threshold = 50 , autautovacuum_analyze_scale_factor = 0.01 )

Try to play with them until you find the right values.


Appropriate situation to data warehouse and how to best manage the warehouse?

Calculating and storing a value is a cache; that's a very common strategy.  A data warehouse is a very different beast (usually more than one table!).

The main thing to be concerned about is timeliness.  If customers expect the results to be closer to real-time, that's OK for now, as ten seconds once an hour should be fine.  Even if it bloats to ten times that length, it should be OK.

If you absolutely must have current data, rather than "up to one hour old," you'll need to know when your source tables have changed.  If you already have a DateLastModified column in each of the tables, then you can check to see if there's a more recent values than the DateRecorded you'll be capturing when you save this query.  A stored procedure can query these values and refresh the cache if necessary.  The problem is that the run time will be slow when any source data has changed, which I suspect will be most of the time.  Also, you'll probably need indices on your DateLastModified fields, to quickly identify when the source data hasn't changed.

Alternatively, you could scatter triggers hither and yon, so any time a key table is updated, you mark the cache as dirty so it can be updated.  This would be high maintenance and fragile, but if you don't already have DateLastModified fields it might be easier.

If your customers are OK with this report being out of date by up to a few minutes, just run it on a schedule.


SQL Server 2016: How to restrict memory and CPU for specific database

You can't. There's no such thing as per-database restrictions because a query can cross multiple databases (think joins and fully qualified objects.)

SQL Server's Resource Governor in theory exists to support this, but it only works on things like CPU count, query workspace memory, and disk IO. It doesn't work on the buffer pool, so it still won't meet your needs.


There are a chapter in the MySQL documentation about ORDER BY optimisation

I'm unable to find a MySQL reference, but for comparison here is the SQL Server logical query processing phases. SELECT has to be done before ORDER BY.

Without code, I'm guessing that you have unsuitable indexes for the query you are running

Edit, after code added

You probably need an index on user_id, type, product_id DESC to cover the both WHERE predicates and the ORDER BY: you have no suitable index currently so a filesort operation is generated.

Also, you have SELECT * which requires an extra lookups and processing to get all columns, no matter what index is used (unless the table is just 3 columns)


InnoDB engine seems lock the table automatically in Mysql5.7

I apologize for the other answer you were given that just copied and pasted from the manual without being related to your question.

This is a common misconception- Innodb does not do full table locks to ensure isolation between transactions- however, it does apply row and gap locking by default to assure no phantom reads happen. Because your table is unindexed the only (inefficient) way to do gap locking is to setup individual lock in every row and every gap between ( for example if you try to insert a new row with id 0, it should get blocked too.

You have two options: create an index on id (probably a primary key is the right way) so only a single row is actually locked thanks to the new index, or change your default isolation level to read committed (SET SESSION transaction_isolation='READ-COMMITTED';).

This is a common question here on dba.stackexchange so here you have a more detailed explanation I wrote on another similar question: MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

However, in your case I strongly recommend you to go the route of creating the index.


Clarification on mysql deadlock situation

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.
Both transactions are locking

space id 10787
page no 139
n bits 176
index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each
Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.
SUGGESTION
Instead of doing
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT
SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks


SQL Xml returning empty root tag on empty record set

A possible solution to your question is neither elegant or clean (IMHO), but it does satisfy what you are looking for:

SELECT ISNULL(s.lmx, '<Users></Users>')
FROM
    (
        select 1 as tag, null as parent,
        FirstName as [User!1!FirstName!Element],
        LastName as [User!1!LastName!Element]
        FROM Users
        FOR XML EXPLICIT
    ) AS s(lmx)

The output for this on my system is:

<Users></Users>

Please let me know what you think.

Edit:

I took your select statement and made it a subquery.  The subquery is run first and generates the output XML string or NULL.  If the result is NULL, the outer SELECT replaces the NULL-value with an empty Users node (<Users></Users>) else the generated XML string is the result.


How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

Next time this happens, run sp_WhoIsActive (download / documentation) and see who is running what and look over the logic. Check to see if the TSQL can be optimized to run quicker or perhaps chop it up into smaller transactions.

I've had cases where the transaction log of a bad query by a report writers, data loaders, etc. would grow the transaction log larger than the data file size, and it's usually a bad performing, poorly written query that's not optimized or chunked into smaller transactions to give back free space once the transaction completes - this was on a SIMPLE recovery model database too by the way - FULL recovery model databases would need to have transaction log backups completed to enable reuse of transaction log space of committed transactions.

The root problem is likely the query so determining who is doing what and reaching out to them and reporting the issue with your findings would put the pressure on them to fix their logic to not hose up the server disk space for that disk partition - hopefully it's not your logic but if it is, look at optimizing the logic to query tune for performance.


How to filter column output based on specific format value

Assuming that your output is all one column, I would go with the conditional clause below. If you output is more than one column, then you can make the adjustments accordingly.

DECLARE @table TABLE
(Column1 VARCHAR(50))

INSERT INTO @table
        ( Column1 )
VALUES  ( 'AB-123456-99 Checker')
    ,('123456-10000-00 2016')
    ,('111119-20000-00 2016')

SELECT t.Column1 FROM @table AS t
WHERE t.Column1 NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%'

So, you would essentially replace the x with [0-9] for numeric. If it was alpha, you would replace the x with [a-z] for case insensitive collations and [A-Za-z] for case sensitive collations. If it's alpha or numeric, it would be [0-9a-z] or [0-9A-Za-z]


Select query slow in the big table because of expired stats in Microsoft SQL Server

Just looking at the details you have provided, there could be quite some reasons. we can't just predict the exact possible reasons unless you share those select query or queries running slow or you share the execution plans.
Looking at first glance it looked you might have issue with skewed stats. But if you are saying update stats is running daily may be you can try with better sampling rate to see if that helps.
However based on what you are saying that things looked better after manual update stats, this is very much possible that process gets recompiled or bad plan might be getting evicted from cache from that update stats.
My personal experience says you might also been seeing something called parameter sniffing-. There are lot of great articles over the web in case you are experiencing the same.
If you can add more details what is slow with execution plans for pre and post changes can confirm above possibilities.


but is it possible to use a single listener and address multiple AGs.

A listener is specific to Instance of AG so you cannot connect to multiple AG's using one listener. If you have multiple instances you have to use multiple AG's.

How do you think a single listener will manager multiple AG's now how will it know which AG to connect ?


In Oracle DB 11.2g, is it possible for the audit trail to explicitly mention account locking events?

I do not think it is possible. The AUDIT_ACTIONS table contains all possible actions in the audit trail. There is a LOCK action, but that refers to LOCK TABLE.

The LOCK_DATE column in DBA_USERS contains this information though.


Having a single tenant CDB allows you to plug/unplug on upgrades, you can use the clone function and it allows stricter separation of admin users in the CDB and the PDB users.

You find this arguments in multiple articles and presentations of Oracle, all hint to the fact that you also should get used to the new normal.

https://blogs.oracle.com/multitenant/single-tenant-configuration


I think all you need to know is listed here http://technet.microsoft.com/en-us/library/ms142509.aspx Look for the right languages in the index and the query parser (http://technet.microsoft.com/en-us/library/ms142583.aspx#Additional_Considerations).
...and check/post your params


How to get rid of ORA-28002 message the password will expire within 6 days?


Getting collation confict in IF statment and need help resolving it please

Well, that is interesting. I created your 3 databases using different collations for each database.  I tried starting from different databases to see the results.  Naturally I do not know which collations you used on each database, but apparently the SQL Server is (in fact) aware of this.

FIRST - SQL_Latin1_General_CP1_CI_AI
DOD - SQL_Latin1_General_CP850_CI_AS
SPRAGUEENERGY - SQL_Latin1_General_CP850_CI_AS
SRAPROD - SQL_Latin1_General_CP1_CI_AI

Depending on the database I start with (I tried all 4) some give collation errors and some do not. So...  The answer is:

IF (@Source COLLATE DATABASE_DEFAULT = 'DBNAME' COLLATE DATABASE_DEFAULT)

This raises no errors no matter which database I start with.


SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END ORDER BY `facility_type`) AS `facilities2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`;

http://sqlfiddle.com/#!9/ceec20/13

  do you know a way to get rid of the rows with NULL for facilities2 in the third query?

We cannot refer to alias from output list in WHERE clause. To remove records with facilities2 IS NULL we must use appropriate condition in HAVING clause:

SELECT `b`.`building_name`, `b`.`address`,
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END) AS `facility2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`
HAVING `facility2` IS NOT NULL;

http://sqlfiddle.com/#!9/ceec20/17


Can Checksum alerts make it to the end user?

Yes, any corruption alerts can make it to the end user.

To see it yourself, grab one of the corrupt databases from Steve Stedman's Corruption Challenge. Attach the corrupt database to one of your development environments, and run the query that Steve describes for that database. You'll see corruption errors - just as your end users would.

Your application (C#, Java, whatever) can be coded to trap incoming errors and display different things to the users, but that's an exercise left for the reader.


Run a stored procedure only the last 3 days in a month

Adding this script will help you to execute the SP only on the last three days of month.

Note: In the scheduler you make a schedule to execute it daily.

DECLARE @FromDate DATE
DECLARE @ToDate DATE
SELECT @FromDate=DATEADD(DD,-2,CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE)) --Last Date -2
SELECT @ToDate= CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE) --Last Date of current month

IF (GETDATE() BETWEEN @FromDate AND @ToDate)
BEGIN

EXEC SP_YourSPName--------------------------Execute your SP here

END

I took query to get the last date of the month from https://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))

Same can be done using the query @Chris Rolliston mentioned in his comment.

DECLARE @Today DATE = GETDATE();
DECLARE @EOM DATE = EOMONTH(@Today);
IF (DAY(@EOM) - DAY(@Today) BETWEEN 0 AND 2)
EXEC SP_YourSPName;

Thanks!


SQL Server Developer Edition Downgrading to Older Developer Edition

Microsoft gives Downgrade Rights to Volume Licensing customers and, to some extent, even to OEM and retail customers.

However, in general, if you don't have a Volume License, you can't have the bits and the product key, which are necessary to excercise your downgrade rights.

Long story short, if you want 2008 R2, you'll have to buy it.

Here's a blog entry of the licensing team on this subject


Mixed SQL Server instances with static and dynamic ports

When a SQL Server named instance configured with dynamic port is started, the dynamic port is provided by windows. The dynamic port range is outside the range of well-known static ports, where port 1433 resides. The dynamic port range is between 49152 and 65535, as documented here.

At subsequent startups, the instances reuse the same dynamic port used during previous startups (the port is written in the registry). If the port is found to be in use, SQL Server asks the OS for a new port. This behaviour is documented in this page, in the "Dynamic Port Allocation" section.

To answer your question in a single sentence: the scenario you describe is impossible (in a supported version of Windows with ranges properly configured).


Can someone explain the result set from sys.dm_tran_locks?

You said, there are different 'request_session_id' showing up, so there are many sessions, not one, to read the same table. Shared locks are compatible with other shared locks so there is no problem at all. If you are warring about the memory comsumed by locks, every lock takes about 96 bytes thes is quite small and server can decide to escalate to table lock when:

  A single Transact-SQL statement acquires at least 5,000 locks on a    single nonpartitioned table or index.
  A single Transact-SQL statement acquires at least 5,000 locks on a    single partition of a partitioned table and the ALTER TABLE SET
  LOCK_ESCALATION option is set to AUTO.
  The number of locks in an instance of the Database Engine exceeds    memory or configuration thresholds.

More about lock escalation here:  Lock Escalation (Database Engine)

Your situation is normal, no lock escalation takes place but just many sessions are reading the same table


Ola Hallengren - DatabaseBackup SQL Version - Backup to Azure requirements

From Microsoft's documentation:

  Manage your backups to Microsoft Azure: Using the same methods used to
  backup to DISK and TAPE, you can now back up to Microsoft Azure
  storage by specifying URL as the backup destination. You can use this
  feature to manually backup or configure your own backup strategy like
  you would for a local storage or other off-site options. This feature
  is also referred to as SQL Server Backup to URL. For more information,
  see SQL Server Backup to URL. This feature is available in SQL Server
  2012 SP1 CU2 or later. This feature has been enhanced in SQL Server
  2016 (13.x) to provide increased performance and functionality through
  the use of block blobs, Shared Access Signatures, and striping.


SQL Server Merge Replication, Agent Batch Size

The prefered method would be to alter the merge agent profile.

See this link for a detailed explaination on how to do that:
http://msdn.microsoft.com/en-us/library/ms152515.aspx

The setting will take effect the next time the merge agent runs.


SQL Join slower than subquery on View with a Function

When you join on the field in the view which is generated by the function, SQL will need to scan the entire table and execute the function for every row in order to do the join, so this will inevitably be much slower than the equivalent set based operation.

You can improve matters a lot using a computed column.  The result of your function will be computed and cached by SQL each time dependent data is changed.


SQL Server - Order by case statement very slow

Any calculation or function, when applied to an index key column, will prevent SQL Server from using that index.

In your case, ORDER BY Id DESC can make use of an ordered scan of the Clustered Index on your table, as Id is the key column on that index. That means, only 20 rows have to be read.

Once you apply the CASE statement, SQL Server cannot use that index anymore and instead has to scan the entire row set and execute (very expensive) sorting on all rows to find the 20 rows you are interested in.

One of the solutions is to use dynamic SQL to include the appropriate ORDER BY without any calculations (as Lamak suggested in the comments above) or to just write the query 4 times and use an IF statement to decide which one to execute.


Convert SQL server stored procedures to postgreSQL

Unfortunately you'll probably have to handle the stored procedures by hand. If you're lucky, they're simple procedures following standard SQL syntax, and you'll be able to just copy the existing queries over to PostgreSQL, otherwise you'll have to handle any fallout on a case by case basis.
Side note: this is one benefit of some database agnostic ORMs like Dapper, if your database is mainly consumed by application code. By using the ORM framework's native calls, you decouple your database logic from the actual database system, but there's pros and cons.


The error message in your screenshot indicates you are trying to add identical members to your replica set under different names:

"The hosts x:27017 and y:27017 all map to this node in new configuration".

If you want to configure multiple replica set members on a single machine for testing purposes, all members need to use different ports and/or IP addresses.

  Version: The master is on Enterprise. I think 3.2 and the slave is on 2.6.

Mixed-version replica sets are only supported for consecutive major versions (i.e. 3.2 and 3.0) and expected to be used transiently during a rolling upgrade to a new major version of MongoDB. A mixed replica set with 3.2 and 2.6 members is definitely not supported.


How to change dbid in oracle to specific value?

You would be better off using the nid command to change the dbid to a new value. then register your database with the rman catalog, if you use one.

nid help=y

DBNEWID: Release 12.1.0.2.0 - Production on Wed Oct 7 13:56:42 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO


Remove Data From Memory when Using SSAS Tabular In-Memory Mode

For the "Is it possible to remove old partitions from RAM?" portion of your question:

Yes.

The BOL entry for Process Database, Table, or Partition mentions Process Clear in the "To process one or more partitions" section. Effectively you could issue an XMLA command to Process Clear a specific partition (i.e. an old partition).

Here's a method to get the SSMS GUI to generate the XMLA command to clear a specific partition.

In SSMS, while connected to your SSAS Tabular server, expand the Tables node, then right click any of the tables and select "Partitions..." which will bring up a new window UI.

Then, click the button that looks like 3 green arrows in a circle (Process) which will bring up yet another new window UI.

Next, Check the partition(s) that you want to clear.

Then, in the Mode dropdown, select Process Clear.

Next, click the Script button at the top, and it will generate the XMLA code in a new query window. You'll get something like below with the object names (with seemingly random GUIDs on the end of some of them):

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessClear</Type>
  <Object>
    <DatabaseID>YourTabularDatabase</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>Your Measure Group_06e45707-4027-41d9-8fbd-9217a864d859</MeasureGroupID>
    <PartitionID>Your Partition_06e45707-4027-41d9-8fbd-9217a864d859</PartitionID>
  </Object>
</Process>

You can then save the script and execute it at-will, or copy the script and have an Agent Job run the code as a SQL Server Analysis Services Command type of job step.

For the "Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?" portion of your question:

Yes.

The BOL entry for Partitions and DirectQuery Mode discusses this some.

In SSDT, within the Partition Manager (Table menu | Partitions), you can set specific Partitions to be either DirectQuery or In-Memory (Set As Sample).
When a partition is set to DirectQuery, it will have (DirectQuery) with the parentheses appended to the front of it.

Incidentally, if you want to know how much memory objects in your Tabular model is using, check out Kasper de Jonge's blog post on What is using all that memory on my Analysis server instance ?


How to find the nearest lat and lang in postgresql

yes i got the output with this query, and thank you to all who tried to help me:)

select * from (SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lang ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance FROM table name) al where lat >= 22.566 OR lat >= 59.52 AND lat <= 22.566 OR lat <= 59.52 ORDER BY distance LIMIT 3;


Doesn't this log file get trimmed at some point in time after hourly
  transactional backup and reduce the size of the log file?

No, it does not reduce the size of the log file on disk. The physical log file is made up of virtual log files (VLFs) and each VLF represents a portion of the transaction log. When a VLF is included in a transaction log backup (because there are no active transactions using that VLF) that VLF is marked inactive and can be overwritten. These inactive VLFs are considered free space within the log file because they can be overwritten at any time by an active transaction if required.

It is possible to shrink the physical file and reclaim the disk space, however, it is generally not recommended as the log file most likely requires that space for large operations, such as index maintenance, and regrowing the log file is a performance bottleneck.

Overview of Transaction Log in SQL Server

  The way the DBA from one of our vendors explain is that during the
  rebuild the tables are locked and hence make the log file grow. I
  don't understand this clearly. Doesn't the log files grow in both
  rebuilding and re-organizing? Cant a long running re-organize fill up
  logs quickly?

Index maintenance operations (REBUILD, REORGANIZE etc) are logged in the transaction log, even ONLINE operations that don't lock the underlying table. So the vendor's DBA is correct that rebuilding indexes can cause log growth, but it is not due to table locking, it is because it is a logged operation.

Check out these links for additional information on space utilisation during index DDL:

Transaction Log Disk Space

Disk Space Requirements for Index DDL

  How frequently should we update statistics after a daily index
  re-organize - immediately or weekly/monthly?

It depends on a number of factors, how many rows in the underlying table/index, rate of data modifications, are you using trace flag 2371 and auto-update statistics on your databases etc?

Also, how frequently are you rebuilding your indexes, because the statistics related to your indexes will be getting updated at the same time, and if you're tables have clustered indexes then your column statistics should also be getting updated.

If you're using Auto-Update Stats, then you can read about the threshold for updating stats here. There's also some useful information in the answers to a similar question here.


TSQL Generic Row number theoretical question

Since the update is produced by dynamic SQL (as was revealed in the comment discussion – A. M.), it would be fairly easy to change (the dynamic part) so it knows which columns are updated and use them in the where clause.
Another route might be to use the OUTPUT clause so you get the list of primary key values of the updated rows in a temp table/variable. Then get the maximum of them and use it the next iteration of the loop with something like: WHERE PRI.pk_column > @previous_max. (All these assuming that all the target tables have a single-column integer primary/unique key.) This could prove more efficient as that column would be indexed (while an arbitrary F1 column might not be).


Identifying values that are not existent in other table

These are pmid values that are in abstracts but not in abstract_mesh

select a.pmid from abstracts a
left join abstract_mesh am on a.pmid = am.pmid
where am.pmid is null;

or

select a.pmid from abstracts a
left join abstract_mesh am using (pmid)
where am.pmid is null;


Unless DB_CHAINING is set to true for all 3 databases, you should give the SELECT permissions on the underlying tables.

In case you set DB_CHAINING to true, all 3 owners of these databases are the same and all 3 objects have the same owner (for now it's not true in your design because your tables are owned by dbo and view is owned by vdr) permissions on the underlying tables will not be checked due to Ownership Chaining

To make ownership chaining work in your case you should do the following:

Run:

use Reporting;
go
alter authorization on schema::vdr to dbo;`

set db_chaining to true on all 3 databases:

use master;
go
alter database Reporting set db_chaining on;
alter database DatabaseA set db_chaining on;
alter database DatabaseB set db_chaining on;

check owners of all 3 databases and if it's not the same change it to the same login using

alter authorization on database::yourDB to yourLogin


prevent clustered index insert operator on unqualified indexed view

Thank you for providing a full script to recreate the problem.

I tested it with SQL Server 2014 Express.

When I add OPTION(RECOMPILE) it works:

ALTER procedure [dbo].[insert_testdata]
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data)
  values (@kind, @data)
  OPTION(RECOMPILE);
end

When I run this in SSMS:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

I get this message:

(1 row(s) affected)

and a row is added to the table.

What version of SQL Server are you using? I vaguely remember that in versions before 2008 this OPTION(RECOMPILE) behaved a bit differently.

  I'm working with an existing data structure and need a fast lookup on
  unique integer values stored in subset of nvarchar(4000)'s, a filter
  on another column defines that subset of rows.

In this case it may be better to use filtered index instead of indexed view:

CREATE UNIQUE NONCLUSTERED INDEX [IX_DataFiltered] ON [dbo].[testdata]
(
    [data] ASC
)
WHERE ([kind]='integer')

Optimizer should use this index when the WHERE filter of the query matches exactly the WHERE clause of the index.

Yes, here index is on nvarchar column which may be not the best thing, especially if you join this table with an int column of another table, or try to filter values in this column using int values.

Another variant that comes to mind is persisted computed column that converts nvarchar to int. In essence it is very similar to your view, but the persisted nvarchar values that are converted into int are stored with the same table, not in a separate object.

CREATE TABLE [dbo].[testdata](
    [testid] [int] IDENTITY(1,1) NOT NULL,
    [kind] [varchar](50) NULL,
    [data] [nvarchar](4000) NULL,
    [int_data]  AS (case when [kind]='integer' then CONVERT([int],[data]) end) PERSISTED,
PRIMARY KEY CLUSTERED
(
    [testid] ASC
))

CREATE UNIQUE NONCLUSTERED INDEX [IX_int_data_filtered] ON [dbo].[testdata]
(
    [int_data] ASC
)
WHERE ([kind]='integer')

With this set up I tried to use your original stored procedure to insert rows and it worked even without OPTION(RECOMPILE).

Actually, it seems that the main reason why the above persisted column works is that I use CASE. If I add CASE to the definition of your view, the stored procedure works without OPTION(RECOMPILE).

create view integer_testdata2 with schemabinding
as
select
    case when a.kind='integer' then CONVERT(int, a.data) end as data
    , a.kind, a.testid
from dbo.testdata a
where a.kind = 'integer'
go


You could use EXISTS instead of IN. That's likely to perform better.

SELECT *
       FROM "wallet"
            LEFT JOIN "amount"
                      ON "wallet"."id" = "amount"."walletId"
                         AND EXISTS (SELECT *
                                            FROM "order"
                                            WHERE "order"."id" = "amount"."orderId"
                                                  AND "order"."status" IN ('new',
                                                                           'accepted'))
       WHERE "wallet"."locked" = false;

To further improve the performance of the above maybe try indexes on "wallet" ("locked", "id"), "amount" ("walletid", "orderid") and order ("id", "status"). Unless you have them already, of course.

Check the plan if you're unsure which version performs better and what indexes help.


How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine?

if it was on premises, only thing you need to is to mount it first and then and run this as a sql query :
DO $$
DECLARE ssql varchar := 'COPY ( SELECT  * from schema.table1   ) TO ''/projeler2/Raporlar/SatisRaporlari/test'
|| TO_CHAR(NOW(),'DD_MM_YYYY')
|| '.csv'' WITH CSV DELIMITER '','' HEADER QUOTE ''"''  ;';

BEGIN
    EXECUTE ssql;
    --raise notice 'Value: %', ssql;
END; $$;

but remote server may change the game. give it a try


How to design a table that each rows have 5K boolean attributes?

Combine the binary values into one or more integers and use binary arithmetic when computing the actual values. This will generate some decent cpu overhead, but the complexity of the data is massively reduced. i.e.

True True True True = 8+4+2+1 = 15
True False False True = 8+0+0+1 = 9
False True True False = 0+4+2+0 = 6


Use both b-tree and BRIN indexes in PostgreSQL

Your index on (study, analysis, gene, sample) INCLUDE (value) would still be usable for a query like select gene, sample, value from t where study='PNAS908' and analysis = 'the correct one'.  And will probably be better than BRIN, or at last not worse by enough to matter.
Note that if "analysis" is just labelled in a low-entropy way within a study (a, b, c, 1, 2, 3, log, linear, sqrt, intervention, SOC, placebo, ...), such that the same analysis identifier is reused from study to study, then the multi-column BRIN index is unlikely to do what you want.  The columns in a BRIN index are not hierarchical like they are within a btree index, they are each independent.


SQL Server Profiler - Disable auto scroll by default

Here you go my good man,

Create the following MyProfiler.vbs file in insert the following code:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "PROFILER.EXE /S <your SQL Server Name> /T Standard"
WshShell.AppActivate "PROFILER.EXE"
WScript.Sleep(5000)
WshShell.sendkeys "%(WO)", True

Now, run MyProfiler.vbs.

Note:

It takes a few seconds to load Profiler.exe so sleep is set to 5 seconds. Otherwise, the auto-scroll commands will be input into the command window where MyProfiler is running. Cheers...

p.s.

You can run whichever template you prefer. I'm running the SQL Server Standard Template with /T.


Backup stored procedure for full, differential and log backup

There are quite a few solutions to have your daily backups (full, diff and logs). You could:

Write your own script: it might be useful if you need a fine control on it, or if you need to respect some naming convention. I did not test it, but your code looks reasonnable.
Use the maintenance plans: very easy to set up, but less control. This might be a good solution in your case.
Use a 3rd party script / tool: Ola Hallengren's scripts are very good, but you can find plenty of others.


From your question, it doesn't sound like you need true transactional semantics, and that asynchronous replication would be fine.

If that's the case, it's fairly trivial to build a replication system using triggers within Oracle to track data changes, and then replaying those changes into MySQL, using something like perl (or other scripting languages) as the glue code to talk between both systems using SQL.

This might sound like it would be a lot of overhead, but we've built numerous systems like this for our clients, and the overhead from Oracle's standpoint is pretty low; we've run it on some OLTP systems powering top 1000 websites that had hefty transactional workloads, and Oracle handled it like a champ.


CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible.  The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition.

Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource on how to choose a processor for SQL Server.

Once you take into consideration SQL Server's per-core licensing structure, it makes sense to always go with the fastest processor speed available, regardless of workload type, whether OLTP or analytics.  Having the fastest possible core speed is never going to be a problem.  Increase the core count as required, but never do that by reducing core speed.

In other words, don't think of 16 x 2.2Ghz processors being the same as 8 x 4.5Ghz processors.  The cost savings of using the 2.2Ghz processors over the 4.5Ghz processors is likely to be a maximum of about $10,000 USD (for a typical Xeon-based two processor machine).  Jumping from 8 cores to 16 cores with SQL Server Enterprise Edition is likely to cost over $60,000 USD in licensing fees.  In other words, you might save $10,000 in hardware costs, but you'll lose an extra $50,000 in licensing.

If you decide you need a lot of parallel processing muscle, and decide you need 32 cores for the task at hand, going with the fastest cores will pay dividends in reduced processing time.  Nobody will fault you for that.

Having said all that, if the choice is one CPU or more than one CPU, always go with more than one.  Running SQL Server (or any DBMS) on a single CPU can cause all kinds of problems since the capability for concurrent operations is vastly limited.


Does database systems use basic system calls of local file system?

What mysql_user wrote is probably true for all systems using files to store data as those just have to communicate with the filesystem used and it does not make sense to rewrite the "drivers" for all known filesystems just to manage them directly (and would collide with the OS drivers anyway).

But some DBMS allow the use of disks/partitions directly. It allows to skip one layer of indirection and buffering, to manage caches directly.., but loses the benefits of using files (simpler backup etc.).

It is often called raw partition

MySQL InnoDB
SQL Server
Oracle used to support it but seems to be desupported - probably getting replaces by Oracle ASM which looks more like a dedicated filesystem, but in the end has similar benefits - being optimized for databases instead of universal files.
Firebird
raw devices deprecated in DB2, but it can use Linux block devices instead which is still quite low-level


SQL Server RandomNumbers Stored Procedure Error

Because you evaluate @X and @y once before the update.

You need to do in the UPDATE itself

Update driver_tbl
set
    pos_x = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX,
    pos_y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

The answer I gave on your SO question shows this: https://stackoverflow.com/q/8546985/27535


What is faster Asynchronous SAN Replication or Asynchronous AAG?

Let me make one thing clear before we go any further.

  ... SAN Replication or AAG, i.e. block-level or transaction-level

AlwaysOn Availability Groups do not work at the transaction level. The unit of work is at the log block level. This is very different as we ship log blocks for all transactions, whether they are committed or not.

The most likely "fastest" shipping mechanism would be the SAN replication given the parameters set forth and given the current versions of SQL Server generally available. This is only taking into account the literal question of "what method makes my data get to the other end the in the least amount of time?".


How to disable replication when Distribution db is lost?

Finally, I have found a solution. Problem was that I was calling the stored procedures from query windows of Management Studio (some stored procedures must be called from sqlcmd).

Then, it is enough to follow instructions for TSQL (with @no_checks and @ignore_distributor).


Best Practice : Schema design for Large Number of rows with few columns

I would just go with a bigint with Identity column. Just remember to start it at the negative seed value since sql server does not support unsigned ints. Something like this...

CREATE TABLE Records
(
 RecordID bigint IDENTITY(-9,223,372,036,854,775,808,1),
 RowLabel varchar(255) NOT NULL,
 CatLabelID INT,
 StartPosition varchar(255),
 EndPosition varchar(255)
)


Postgres compound index not delivering expected improvements

Maybe the compound index isn't used because it thinks the single-column index is selective enough that there is no point in adding more selectivity.  The way to find out is by seeing the results of EXPLAIN (ANALYZE, BUFFERS) SELECT ...

You can disable the indexes by dropping them inside a transaction you later rollback:

BEGIN;
DROP index i1,i2;
EXPLAIN (ANALYZE, BUFFERS) SELECT....;
ROLLBACK;

Rolling back the index-drop will be nearly instantaneous, it doesn't have to rebuild them.  If you run it in production, be aware that the table will be AccessExclusive locked for the duration of the transaction.

You also don't have to experiment with the full 600 million rows.  6 millions is probably plenty to get some valid insight:

create table foo_exp as select * from foo where random()<0.01;
vacuum analyze foo_exp
create index...;
explain (analyze, buffers) ...;

The order in the WHERE clause doesn't matter, but the order in the btree index does.  The equality-constrained column should be the leading column in the index, with the range-constrained column after.

For asking performance questions, see https://dba.stackexchange.com/tags/postgresql-performance/info


Latest version to support MySQL 5.1.69 ib* files

You should be able to load it with 5.5. As a general rule, next version up should always work. You should be able to run mysqldump from there, or run mysql_upgrade to keep it as is for 5.5. Then if you want to upgrade from there, you have to go up to 5.6 and run mysql_upgrade again. Repeat for 5.7 and 8.0 depending on how far you need to upgrade.


Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):

RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';

In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.


Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop

For that purpose you can download the pre-built virtual Machines (VMs) provided by Oracle. They only need Oracle Virtual Box in order to check them out (https://www.virtualbox.org/wiki/Downloads).

You can download them here:
http://www.oracle.com/technetwork/community/developer-vm/index.html

This is the best solution for learning Oracle DB, without actually messing up your system.


understanding index fragmentation statistics

Your query is slightly off, you need to include the index id in your join on sys.indexes

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
AND d.indexID = i.index_id
ORDER BY
i.object_id,d.fragmentation DESC

DROP TABLE #indexDefragList


How do I monitor bandwidth used by SQL Server on port 1433?

Not aware of any built-in method to record bytes over a particular port so if you really need that level of detail, Wireshark is a good bet.

If the server is dedicated to SQL Server I'd be reasonably confident that the majority of traffic over the network was related, so the perfmon counter Network Interface\Bytes Total/Sec should give you a broad guideline.

To be honest it's a mute point in my book. Unless there is a large and nonsensical additional cost for the gig port where your servers are hosted, take it.


Report Server tempdb's chunk data table is growing very large on daily basis

Most of all facing same issue: Report server tempdb growing very fast, It's better to schedule a job which purges the chunk table (can add required tables if need) and make sure that the scheduled time will have a light load on the server (lets start for every 8 or 12 hrs)

try with below query for scheduling job:

DECLARE @RowCount INT
SET @RowCount = ( SELECT    COUNT(1)
                  FROM      Segment WITH ( NOLOCK )
                )

WHILE ( @RowCount > 0 )
    BEGIN
        DELETE TOP ( 1000 )
        FROM    Segment
        SET @RowCount = ( SELECT    COUNT(1)
                          FROM      Segment WITH ( NOLOCK )
                        )
        PRINT @RowCount
    END

Then Truncate table and Shrink files (both data and log files)

TRUNCATE TABLE Table_name

-- Shrink the truncate tables data and log file to a specified target size (to 1 MB).

DBCC SHRINKFILE (TempDB', 1)
GO

DBCC SHRINKFILE (TempDB_log', 1)
GO

Edit:

Table grows significantly may occur due to the following reasons:

Check the report execution
Report snapshots are too large to process for reporting services.

Because of this issue, as the time progresses it left lots of orphaned snapshots which in turn resulted in growth of the database.

You can use following query for obtaining those reports with the largest size contribution to ReportServerTempDB:

--Reports that are largest contributors to ReportServerTempDB size through tables Segment, ChunkSegmentMapping, SegmentedChunk and SessionData:
SELECT
   sd.ReportPath,
   COUNT(s.SegmentId) as CountOfSegmentId
FROM
   [ReportServerTempDB].dbo.Segment s
INNER JOIN
   [ReportServerTempDB].dbo.ChunkSegmentMapping m
ON
   m.SegmentId = s.SegmentId
INNER JOIN
   [ReportServerTempDB].dbo.SegmentedChunk c
ON
   c.ChunkId = m.ChunkId
INNER JOIN
   [ReportServerTempDB].dbo.SessionData sd
ON
   sd.SnapshotDataID = c.SnapshotDataId
GROUP BY
   sd.ReportPath
ORDER BY
   CountOfSegmentId DESC;

Note:

  Make sure temp DB is Simple - recovery model


Get list of students having actions in Oracle

This is simply an aggregation problem.

select
  student_id,
  count(case when type in ('Expulsion','Probation') and quarter=3-1 then 1 end) as previous,
  count(case when type in ('Expulsion','Probation') and quarter=3 then 1 end) as "CURRENT"
from student_actions
group by student_id;

See http://sqlfiddle.com/#!4/f4263/10/0


Scaling out MySQL & Redundancy-Speed tradeoff?

As you grow, keep an eye on the slowlog -- it will help you find the queries that need better indexes and/or reformulation.
No sharding for those small specs.
Clustering (eg, Galera or Innodb Cluster) give a high level of HA, plus some scaling.
Do not fear JOINs.  On the other hand, don't "over-normalize".
If the monthly reports run to slowly, "Summary tables" can fix that.
No PARTITIONing unless you plan to purge "old" data.


Why ALTER DATABASE .. MODIFY NAME reports an error while succeeding at the same time?

One possibility is that there's a DDL trigger that fires for ALTER DATABASE, and that trigger code try to refer to the (now old) database name.


Trouble setting up Nagios to monitor Oracle services

As Phil mentioned, I created $ORACLE_HOME/network/admin and tnsnames.ora.  I have something like this:

OFFDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OFFDB1)
    )
  )

I then modified the monitoring commands:

/usr/local/nagios# libexec/check_oracle_health --connect=OFFDB1 --username user
    --password "pass" --mode tablespace-can-allocate-next --warning 20 --critical 30


Autovacuum only runs when necessary:

if too many rows have been changed or deleted sine the last VACUUM run
if there are rows old enough that they should be “frozen” to prevent data loss due to transaction counter wraparound.

See the documentation for details.

If autovacuum never ran on some tables, that could also be because there was no reason for it to run yet.


(adding a second answer -- to address the VARIABLES and STATUS)

Observations:

Version: 5.6.24
52 GB of RAM
Uptime = 21d 02:26:20
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

table_open_cache = 200K, yet 156 tables opened per second, and all are misses/overflows from the cache.  You must redesign you schema to have a civilized number of tables.
Are there really 403 ROLLBACKs per second?  If so, think about how to avoid some of them.  That's probably more than 10% of the transactions.
innodb_lock_wait_timeout = 300 -- Assuming you really need transactions to hang around waiting for 5 minutes, this could be consuming RAM.
tmp_table_size = 8M, together with 51 tmp tables created per second, may be leading to quite a few GB of transient MEMORY tables.  Look through the slowlog to find which queries need tmp tables and see if they can be rewritten.
Decrease long_query_time to 2 (now 10), turn on the slowlog, wait a day, run pt-query-digest.  Then study the worst few queries -- improving them will speed up the entire system and probably decrease RAM usage.

Details and other observations

( Innodb_buffer_pool_reads ) = 329,533,959 / 1823180 = 180 /sec -- InnoDB buffer_pool I/O read rate
-- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 589,768,119 / 1823180 = 323 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 16,106,127,360 / 53248M = 28.8% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 285,544,112 / 1823180 = 156 /sec -- Frequency of opening Tables
-- increase table_open_cache

( table_open_cache ) = 200,000 -- Number of table descriptors to cache
-- Several hundred is usually good.

( Table_open_cache_overflows ) = 285,343,673 / 1823180 = 156 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 285,544,112 / 1823180 = 156 /sec
-- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((329533959 + 589768119) ) / 1823180 = 504 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size.

( Innodb_log_writes ) = 167,769,519 / 1823180 = 92 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 332,945,398,784 / (1823180 / 3600) / 2 / 100M = 3.13 -- Ratio

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,823,180 / 60 * 100M / 332945398784 = 9.57 -- 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.

( Com_rollback ) = 735,486,482 / 1823180 = 403 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_lock_wait_timeout ) = 300 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
-- Fix the cause of timeouts rather than increasing this value.

( Innodb_dblwr_writes ) = 14,574,293 / 1823180 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( Questions ) = 7,836,091,851 / 1823180 = 4298 /sec -- Queries (outside SP) -- "qps"
-- >2000 may be stressing server

( Queries ) = 8,262,090,685 / 1823180 = 4531 /sec -- Queries (including inside SP)
-- >3000 may be stressing server

( Created_tmp_tables ) = 92,537,439 / 1823180 = 51 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 6,158,440 / 1823180 = 3.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM.
Better indexes and reformulation of queries can

( Handler_read_rnd_next ) = 266,467,437,770 / 1823180 = 146155 /sec -- High if lots of table scans
-- possibly inadequate keys

( Com_rollback / Com_commit ) = 735,486,482 / 741864016 = 99.1% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Com_show_variables ) = 6,666,028 / 1823180 = 3.7 /sec -- SHOW VARIABLES ...
-- Why are you requesting the VARIABLES so often?

( Select_scan ) = 315,076,005 / 1823180 = 172 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 315,076,005 / 3888296954 = 8.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (613230844 + 1238003 + 0 + 30479625 + 71282659 + 0) / 1823180 = 392 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 23 - 22 ) / ( 23 + 22 ) = 2.2% -- Are you closing your prepared statements?
-- Add Closes.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Com_change_db ) = 31,947,189 / 1823180 = 18 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.

( Connections ) = 21,121,749 / 1823180 = 12 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Threads_running - 1 ) = 22 - 1 = 21 -- Active threads (concurrency when data collected)
-- Optimize queries and/or schema

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

39 issues flagged, out of 125 computed Variables/Status/Expressions. Settings not mentioned are mostly OK.


Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data:

CAST the column as NVARCHAR(260) in all codes that uses it. The query optimizer will calculate the memory grant using the casted data type instead of the raw one.
Rename the table and create a view that does the cast instead. This accomplishes the same thing as option 1 but may limit the amount of code you need to update.
Create a non-persisted computed column with the right data type and have all of your queries select from that column instead of the original one.
Rename existing column and add computed column with the original name. Then adjust all of your queries making updates or inserts to the original column to use new column name instead.


Database design: User table and relationships

Here is one idea that I believe is pretty common:

CREATE TABLE users
( user_id ... not null PRIMARY KEY
, user_name ... not null UNIQUE
, surname ... not null
, ...
, user_type ... not null
,     CHECK (user_type IN ('T','S','A'))
,     UNIQUE (user_type, user_id)
);

CREATE TABLE teachers
( user_id ... not null PRIMARY KEY
, license --- not null UNIQUE
, ...
, user_type ... not null
,     CHECK (user_type = 'T')
, FOREIGN KEY (user_type, user_id)
      REFERENCES users (user_type, user_id)
);

Similar tables for STUDENTS and ADMINISTRATORS.

To determine the name of a teacher, you join with users

CHECK constraints may not be availible in your version of MySQL, you can achieve the same thing with foreign keys to a domain table

All of the above assumes that - for example - teachers and students are disjoint.


How to determine the read / write ratio of your server?

For your specific question - Yes. I think looking at sys.dm_io_virtual_file_stats, as you are doing is a good way to see which tables have the most reads or writes or the highest throughput of reads/writes. And I would say that looking at the returned data is good to look at in three angles. Look at the number of operations (reads or writes), look at the stalls and stall time so you can see who is waiting on IO stalls the most and look at the total bytes transferred. I would probably look at the total size last and focus on IO Operations/second IOPs number first for what you are looking for here.

I think you should also look at sys.dm_db_index_usage_stats and look at the amount of times your tables and indexes are written to (user_updates) or read from (user_lookups, user_scans, and user_seeks) to get an idea of the distribution.

Steven brings up a good idea of looking to see the buffers as well.

For The WRITELOG Waits

I think you are heading in a good direction here to see which databases are your busiest in terms of writes. That said I would consider taking a look at a script like sp_whoisactive that Adam Machanic wrote. You can periodically run this and collect the results to a table and you can actually see which statements of yours are the ones most waiting on WRITELOG.

I would argue that you may save some time in tracking down the read/write ratios of your databases (though this is great information to know and track) in this case and you can dive into a couple of other places to start. Presuming you are seeing significant WRITELOG waits and suffering issues from it (You can see these waits happening and not have them be a problem. You should ask what kind of averages you are seeing, what frequency you see the counter and if there are symptoms - are queries taking longer than expected in an app? Are users complaining? Or are you just being proactive), you could, for instance:

Analyze your IO Subsystem. Ask questions like:

Are you logs and data files on the same drive?
What kind of latency (avg sec/write and avg sec/read) are you seeing to these drives? If you are seeing consistent averages of 20ms and up you could have an issue.. The higher the average and the longer the spikes, the more likely this is.
What do the disks look like for your log drive(s)?
And some other questions. Lie - Do you need to take two active log files and split them onto separate storage? Are you write cache optimized? SQL Server typically doesn't take much advantage of a read cache in IO but it loves some write cache.

Analyze your activity. Run sp_whoisactive. Get an idea of which queries are waiting for these waits, look at your insert activity in these databases.
Ensure transactions are being managed appropriately. Are you committing too frequently? Waiting too long to commit? Both could have an impact on time to write the log. Too frequently - especially in bulk operations means more commits, means more log records.. Too infrequently can mean you wait to accumulate too much.


Performance on same table vs 1:1 relationship in MySQL

1:1 is usually a 'wrong' design.

One exception is when some column (such as "Likes" or "click count") is very frequently incremented.  Putting that in a different table offloads the main table, and cuts back on the incrementation from interfering with other operations (and vice versa).

When does the recommendation kick in?  You would have to test it for yourself.

However, one thing can be said with a fair amount of certainty -- you really should switch from MyISAM to InnoDB.  UPDATE locks the entire table for MyISAM.  For Innodb it only locks the rows to be modified.  This can make a big difference when updating lots of different rows, or other high-volume activity.

Conversion tips .


PID is the internal (to the database) ID of database processes.

The SPID column contains the OS process ID, that is what you are looking for.


Preordering the table by a specified column

There is no natural, implied or intrinsic ordering to a table
Any order is only guaranteed by the outermost ORDER BY

If this is slow, then you can either

make the index covering (a UNIQUE constraint is an index already)
make it the clustered index (which is covering by its nature)


Changing the port of the postgresql server for security reasons

Opinions vary, but in my view it's mildly useful to use a non-default port on any Internet-exposed service that isn't for general public use. Changing your port is not security. It adds absolutely no security in normal operation, and is only minimally useful if your security is already compromised by the discovery of a remotely exploitable security hole. At best it's a delaying and mitigation option to give you a slightly higher chance of patching a security hole before a worm exploits it.

Using a non-default port will not protect you from having vulnerabilities exploited, or from any kind of targeted attack. It may help reduce the chances that a simple worm or casual broad-based scanning effort will attempt to exploit a security hole in your service, but it won't slow anyone who's interested in your system specifically down for a heartbeat.

Note that PostgreSQL doesn't have the same kind of history of severe remotely-exploitable security holes that MS SQL Server does. That doesn't mean it won't ever have remotely exploitable holes in it or in plug-in authentication mechanisms like Kerberos, so you should always be ready for the possibility.

For actual security, you want to be thinking about:

Limiting connections to trusted machines. If your DB is on a VLAN that only contains its self and the application server there's no point playing with ports. Similarly, if you don't need the DB exposed to the Internet, make sure it doesn't run on a public host or at least set it to bind only to an internal IP address and firewall it off too.
Banning clear-text auth. Use at least SSL, if not Kerberos or other moderately strong protocol.
Strong or multi-factor authentication; none of this username-the-same-as-password business.
Ensure your apps never use superuser roles and they only have the least required priveleges on the database objects they interact with. Your apps shouldn't connect with the user/role that owns the database or its tables unless absolutely necessary. GRANT them the minimum rights required.
Disallow remote connection by superuser roles in pg_hba.conf
IP connection limits. If all the machine's clients use a particular ISP, use a firewall to drop connections from outside the IP netblocks you know may be used. You can do it with pg_hba.conf too, but if you do it in the firewall you're preventing them from even handshaking with Pg.
... and lots more. There's lots on the 'net about locking down and securing Pg. See, eg, Depesz's article, Bruce Momjian's presentation, and this DeveloperWorks article.

I change the public SSH port on all my machines for a similar reason. It won't stop anybody, but it might slow automated attacks and worms down slightly. More importantly, it reduces the enormous volume of scanning spam in my system logs to the point where the logs become useful again.


SQL Server Standard High Availability (HA) with two nodes

Use Database Mirroring. Witness node is optional.

http://technet.microsoft.com/en-us/library/ms190941.aspx

You may configure a failover cluster with virtual shared storage. There are guys who do the VSAN for Windows (however they call it "for Hyper-V" but don't get confused it works bare metal on Windows as well).

http://www.hotwebclips.com/Home/Show/tGgqG8oEpBQ


Here is a query that finds duplicate role_tab_privs. It uses Oracle LISTAGG aggregate function to create a list of privileges for each role, owner, table.

SELECT arole, brole, aowner, atable, aprivlist
FROM
      --join to each grouped view by role, owner, table
      (
      SELECT A.ROLE AS arole, A.owner AS aowner, A.table_name AS atable,
                  --one field for all privileges concatenated with | separator
                  listagg(a.privilege, ' | ') WITHIN GROUP(ORDER BY a.privilege) AS aprivlist
      FROM role_tab_privs a
      GROUP BY A.ROLE, A.owner, A.table_name
      )
JOIN
      (
      SELECT b.ROLE as brole, b.owner as bowner, b.table_name as btable,
                  listagg(b.privilege, ' | ') WITHIN GROUP(ORDER BY b.privilege) AS bprivlist
      FROM role_tab_privs b
      GROUP BY b.ROLE, b.owner, b.table_name
      )
  -- the roles cannot be equal but the owner, table, and privileges must be
  ON AROLE != bROLE
 AND aowner = bowner
 AND atable = btable
 AND aprivlist = bprivlist
ORDER BY aowner, atable, arole
;

Note that the LISTAGG is used only on the privileges within a role, owner, table group. The result of a `LISTAGG' function must fit within the max varchar(4000) or the error:

ORA-01489: result of string concatenation is too long

This query gives a good view of how the table privileges relate.


The easiest and safest way to delete a database is using the RMAN DROP DATABASE command.

If on Linux, set up your ORACLE_HOME and ORACLE_SID environment variables appropriately then start the database(s) using startup mount exclusive restrict.  Then drop it using RMAN's DROP DATABASE.  If you want to delete the backups/contents of the FRA as well, then use DROP DATABASE INCLUDING BACKUPS.

You could also use the GUI (DBCA - Database Creation Assistant), but this may be trickier depending on the environment.


MySQL: I need to return rows according to certain conditions

Step by step:

  Basically, for each work, I want to return the recording where is_art
  = 1 and is_vid = 0 AND were the release is the oldest (oldest year, month and date). Sometimes, the release can have the same year, month
  and day, so, in that case, I guess I need to find a unique identifier,
  so ill go with latest release_id

SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id
WHERE r.is_art = 1 and r.is_vid = 0;

With above you will get multiple lines for 1 recording, when there is more than 1 release. We can sort, and take the first record.

SELECT r.recording_id, r.name, r.work_id, s.year, s.month, s.day
FROM recording r
INNER JOIN releases s on s.release_id = r.release_id
WHERE r.is_art = 1 and r.is_vid = 0
ORDER by s.year DESC, s.month DESC, s.day DESC
LIMIT 1;

This should get all releases, which can be linked to work:

SELECT w.work_id, w.name, rr.recording_id, rr.name
FROM work w
INNER JOIN (
    SELECT r.recording_id, r.name, s.year, s.month, s.day
    FROM recording r
    INNER JOIN releases s on s.release_id = r.release_id
    WHERE r.is_art = 1 and r.is_vid = 0
    ORDER by s.year DESC, s.month DESC, s.day DESC
    LIMIT 1
) rr on rr.work_id = w.work_id

Above is untested, so change if needed...

untested, and indeed it is/was wrong, so the changed query to return the recording and release info is:

SELECT r.recording_id,
       r.name,
       r.work_id,
       MAX(s.year) as year,
       MAX(s.month) as month,
       MAX(s.day) as day
    FROM `recording` r
    INNER JOIN `release` s on s.release_id = r.release_id
    WHERE r.is_art = 1 and r.is_vid = 0
    GROUP BY r.recording_id, r.name, r.work_id
    ;

Above is wrong! Is there is one release with a recording in (yyyy/mm) '2020/05' and '2019/06' the month returned for that recording will be '2020/06' ...


mysqldump, preserve update_time and modify_time attributes as listed by show table status from

There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamica entities. For instance, look at INFORMATION_SCHEMA.TABLES:

mysql> show create table INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(80) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

It is a temporary table. If you restart mysql, how does it get populated? Evidently, it is popoulated during mysql startup. What would be included? Retrieve OS metadata of the table.

Let's look at mysql.user from the OS

[root@*** ~]# ls -l /var/lib/mysql/mysql/user.*
-rw-rw---- 1 mysql mysql 10466 Jun  5  2012 /var/lib/mysql/mysql/user.frm
-rw-rw---- 1 mysql mysql  2144 Jul 15 23:29 /var/lib/mysql/mysql/user.MYD
-rw-rw---- 1 mysql mysql  2048 Dec 10 18:25 /var/lib/mysql/mysql/user.MYI

Let's look at mysql.user from the mysql client:

mysql> select * from information_schema.tables
    -> where table_schema='mysql' and table_name='user'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 31
 AVG_ROW_LENGTH: 69
    DATA_LENGTH: 2144
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 2048
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2012-06-05 23:45:23
    UPDATE_TIME: 2012-07-15 23:29:05
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)

mysql>

Please notice the following

The Creation Time

file datetime for .frm is Jun  5  2012
CREATE_TIME: 2012-06-05 23:45:23

The Update Time

file datetime for .MYD is Jul 15 23:29
UPDATE_TIME: 2012-07-15 23:29:05

The OS metadata for mysql.user is loaded into INFORMATION_SCHEMA.TABLES on mysql startup and is updated through INSERTs, UPDATEs and DELETEs.

SUMMARY

While you can mysqldump INFORMATION_SCHEMA.TABLES, it is impossible to manually reload it. Theoretically, you could shutdown mysql, tweek the OS clock, tweek every file, then startup mysql. It's not worth the effort.


Hostgator php code error while creating a new database table

This is not a PHP error, but a MySQL error. Basically, as the error message says you're either not using the correct username, password or the user privileges haven't been flushed since you added the user. That is, I'm assuming that you've removed the username from the error message, and not trying to log in with an empty parameter.

You need to check that the selected user has access to the database in question, and that the privileges has been flushed. Using the MySQL CLI client you can do this by the following commands:

USE mysql;
SELECT host,db,user,password FROM db;
SELECT host,user,password FROM user;
FLUSH PRIVILEGES;

The latter should only be used after you've added, deleted or changed the permissions of any MySQL users in the above tables. If you don't have access, look at the MySQL manual for help on GRANT. Alternatively, search the web.


More than 15000 sleeping sessions in SQL Server 2014

The application probably is leaking connections. I have no idea what they mean by

  The application team is telling they are using some 'Connection Pool'
  for the the connection management.

But it sounds as they are trying to implement their own connection pooling system.

Generally connection leaking happens when you have calls to  .open() without calling  .close() on the same connection.

Since this is generally a client issue I think the only thing you can do from the database side (short of killing them every once in a while but I wouldn't suggest doing that) is use the information from sys.dm_exec_sessions to find the application leaking the connections and then talk to your developers or vendor so they can look for the offending code.

For example this query (taken from here:

select count(*) as sessions,
     s.host_name,
     s.host_process_id,
     s.program_name,
     db_name(s.database_id) as database_name
from sys.dm_exec_sessions s
where is_user_process = 1
group by host_name, host_process_id, program_name, database_id
order by count(*) desc;

will show you the number of connections per host per process. This should be enough to identify the offending application.

You could also create a job logging this information over time to chart out if the connections are really always increasing so you can "prove" to your application team there really is a leak.


Is it fine to run a public website on the sql express db?

Here is a partial list of restrictions of this version:

Number of CPUs:  1
Maximum memory utilized:  1 GB
Maximum database size:  10 GB
Replication: Subscriber only
Backup compression: N/A
Most of performance features is not available.
...

See comparison list for more details


CHECKDB with default settings and parallelism involved

Also, CHECKDB in Standard Edition is single threaded, whereas on Enterprise edition it's multi-threaded - could this be the reason why the 2 servers have different performance?
More info : https://www.brentozar.com/archive/2020/08/3-ways-to-run-dbcc-checkdb-faster/


Mysql settings for query_cache_min_res_unit

Observations

Your question says you have 426870 for Qcache_total_blocks. Since each block is 1K, blocks are aligned to the nearest multiple of 1024. Thus, your query cache has about 417M.
If you divide Qcache_not_cached (7128902) by Qcache_inserts (49557287), this shows that about 14.4% (1 out of 7) of your queries could not be cached.
(Qcache_total_blocks (426870) - Qcache_free_blocks (35327)) / Qcache_queries_in_cache (195659) = 2K
On average, each query cache result hold about 2K, which is theoretically not possible. This could indicate some fragmentation in the query cache.

Recommendations

You need a bigger value for query_cache_size (perhaps 2G). If you are using InnoDB, you may get some performance increase if you disable the query cache altogether by setting query_cache_size to 0.
You need a smaller value for query_cache_min_res_unit (minimum value is 512 (0.5K)) if you want to hold small result sets.
You need a bigger value for query_cache_limit if you want to cap how big a result is too big.
See my earlier post : Is the overhead of frequent query cache invalidation ever worth it?


SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

No, it caches all versions. Or rather, it caches one version with all paths explored, compiled with the first set of passed in variables. Cardinality estimation for all plans will be done using them. This can be extra bad if some passed in values are NULL.
Here's a quick demo, using the Stack Overflow database.
Create an index:
CREATE INDEX ix_yourmom ON dbo.Users (Reputation) INCLUDE (Id, DisplayName);
GO

Create a stored procedure with an index hint that points to an index that doesn't exist, in branched code.
CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS
BEGIN

    IF @Reputation = 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;
    END;

    IF @Reputation > 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = ix_yourdad)
        WHERE u.Reputation = @Reputation;

    END;

END;

If I execute that stored proc looking for Reputation = 1, I get an error.
EXEC dbo.YourMom @Reputation = 1;

Msg 308, Level 16, State 1, Procedure YourMom, Line 14 [Batch Start
Line 32] Index 'ix_yourdad' on table 'dbo.Users' (specified in the
FROM clause) does not exist.

If we fix the index name and re-run the query, the cached plan looks like this:

Inside, the XML will have two references to the @Reputation variable.
<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" />

A slightly simpler test would be to just get an estimated plan for the stored proc. You can see the optimizer exploring both paths:

And if on the following execution, the value of the parameter changes,
will it re-compile and re-cache the stored procedure, because a
different branch of the code must be executed? (This query is quite
expensive to compile.) Thank you.

No, it will retain the runtime value of the first compilation.
If we re-execute with a different @Reputation:
EXEC dbo.YourMom @Reputation = 2;

From the actual plan:
<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(2)" />

We still have a compiled value of 1, but now a runtime value of 2.
In the plan cache, which you can check out with a free tool like the one my company develops, sp_BlitzCache:

The stored procedure has been called twice, and each statement in it has been called once.
So what do we have? One cached plan for both queries in the stored procedure.
If you want this sort of branched logic, you'd have to call sub-stored procedures:
CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS
BEGIN

    IF @Reputation = 1
    BEGIN

        EXEC dbo.Reputation1Query;

    END;

    IF @Reputation > 1
    BEGIN

        EXEC dbo.ReputationGreaterThan1Query;

    END;

END;

Or dynamic SQL:
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql +=
N'
SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u '
IF @Reputation = 1
BEGIN
    SET @sql += N' (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;'
END;

IF @Reputation > 1
BEGIN

SET @sql += ' WITH (INDEX = ix_yourmom)
        WHERE u.Reputation = @Reputation;'

END;

EXEC sys.sp_executesql @sql;

Hope this helps!


how to force postgresql to filll work_mem?

You definitely have too high memory settings. What you can do in your situation:
1) Lower work_mem
2) Lower max_connections

If you want to completely get rid of OOM-killer coming to your postgres server, then you should set kernel parameter vm.overcommit = 2 is sysctl.conf. This will forbid kernel to allocate more memory than it really have.

Important notes about this parameter:

1) Tune our memory-related settings. If something will try to allocate memory when it is full, you'll get Can't allocate memory problem. This can end even in no possibility of launch any bash command from command line because of no memory enough to fork().

2) Create relatively small swap area and set vm.swappiness to some low value (like 5 maybe, default is 60). This will save some nerves in case of trouble.


Check if a user exists in a SQL Server database

Use sys.database_principals instead of sys.server_principals.

So the final query would look like this (accounting for the user filter):

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT [name]
                FROM [sys].[database_principals]
                WHERE [type] = N'S' AND [name] = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool]
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO


How to get a list of current Oracle CDBs on server

This is how I list running database instances. Container-databases or non-container databases:

  cat >> ~/.bash_profile <<EOF
  oinst() {
      /bin/ps -ef | grep ora_lgw[r] | awk '{print $8}' | sed -e 's/ora_lg[w]r_//'
  }
  EOF

  $oinst
  CDB1
  CDB2
  CDB3

If database instances are not running you are left with information from  /etc/oratab, init<SID>.ora, orapw<SID> or database-files on ASM, filesystem or RMAN.


Mysql Query Optimization [Match Against]

MATCH is not the primary villain in the sluggishness; these are:

OR
Using LIKE / RLIKE
pagination via OFFSET
Most of the above prevent the use of any INDEX, hence the query must scan the entire table, thereby being slow for lots of reasons.

Some can be solved in SQL; most require changes to the specifications for the query.

Since the first thing the Optimizer will use is a FULLTEXT clause, I will focus on them.

        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE)
     OR `title` LIKE '%word1%'

This can be simplified (approximately) by:

FULLTEXT(keywords, title)   -- add this

MATCH(`keywords`, `title`) AGAINST('word1*' IN BOOLEAN MODE)

That should make query2 much faster.

Query1 is pretty hopeless as a starting point.  Can we start with query2 instead?

SELECT ...
    FROM ( ...query2... ) AS x
    JOIN table_name AS t2  USING(id)
    WHERE ...
    HAVING ...
    ORDER BY ...  LIMIT ...   -- moved from query2

The idea is to

combine the tests as discussed above;
do the most filtering possible with the least work in the derived table (the subquery);
join back to the same table to pick up things left out of the subquery;
finish with the rest of the work.


If OOM triggers the "crashes", then there is too much running on that machine and/or the processes are using too much ram.
It sounds like you have a mixture of MyISAM and InnoDB tables:
innodb-buffer-pool-size        = 4G  -- change to 3G

That might decrease the frequency of OOMs.
Yes, moving to InnoDB is a good idea for many reasons, including avoiding the need to "repair" that big table.  Caution: That big table may grow to 15GB when switching to InnoDB.


OK, I'll stop trying to figure out why your query is not working and give you a technique for solving it...

SELECT  u.*, p.*
    FROM
       ( SELECT  user_id   -- minimal data (just PK in this case)
            FROM  users
            ORDER BY  user_id
            LIMIT  100    -- now in subquery
        ) AS x
    JOIN  users AS u  ON u.user_id = x.user_id
    JOIN  phonedetails AS p  ON p.phone = u.phone
    ORDER BY  x.user_id   -- yes, need extra ORDER BY

The general principle:  When doing a LIMIT or GROUP BY and the query is even somewhat complex, it may be better to build a subquery that identifies which rows as simply as possible.  Then do the JOIN, etc, to get the rest of the data.

The subquery (called a "derived" table in this syntax) finds the user_ids -- and only the 100 needed.  Since you are doing LEFT, there is no need to involve phonedetails in this query.
Then reach out to the other tables to get the desired columns.

Caveat:  This version of the query may be slow for the same yet-to-be-discovered reason.  This reformulation is effective in some situations.


SQL Server 2016 - Parameter Sniffing Turned Off - Still seeing where a recompile improves performance

According to this link, turning the Parameter Sniffing option off in 2016 is equivalent to setting trace flag 4136.
You should read up on that trace flag.

Especially important information from that trace flag link:

  Enabling trace flag 4136...is equivalent to adding an OPTIMIZE FOR UNKNOWN hint

and

  Queries that meet any of the following conditions still undergo parameter sniffing even if the trace flag is set.

  The parameter is specified by using the "optimize for (@p = )" query hint.
  The query uses the "option(recompile)" query hint.
  The query is a part of a stored procedure that uses the "with recompile" option.

So by adding a recompile, you're telling the optimizer to build a plan based on whatever parameter is passed, but not to reuse the plan. With Parameter Sniffing off (and no recompile), a plan is built based off of statistics on the parameters and will be reused, even for values that would otherwise generate a different (perhaps better) plan.


How does PARALLEL_MIN_PERCENT affect parallel execution?

I believe for that configuration you will get an error like this:

  ORA-12827: insufficient parallel query slaves available

See the references at PSORG and ORAFAQ.

If parallelism is configured differently on the server it's possible for the DOP to be downgraded. In some cases it will be downgraded to execute serially if there aren't enough PX servers available. There are a few examples here for parallel_degree_policy=MANUAL:

  Since there are only 12 available PX servers this statement gets downgraded from DOP=8 to DOP=6 and gets all 12 available PX servers.

  Consider another query submitted at this time with DOP=8. Even though this query requires 16 PX servers it will be given none because there are no available PX servers left. This query gets downgraded to DOP=1 and runs serially.

I have experienced that when running my own code. One way to see when DOP is downgraded is through SQL monitor. Here are a few examples of the messages you can see:

  350 DOP downgrade due to adaptive DOP

  351 DOP downgrade due to resource manager max DOP

  352 DOP downgrade due to insufficient number of processes

  353 DOP downgrade because slaves failed to join


Try to use case inside count:

Count(Case WHEN Successful = 'Y' THEN 1 ELSE 0 END)

Regarding division operator, looks ok, if you want to order by count.


RaiseError on SQL Server stored procedures

If you want to raise error and exit from the procedure, you may want to try return. Example below.

alter PROCEDURE sp1
@PersonID int
as
begin
   IF (@PersonId is null)  --you can check 0, if that is the intention
   begin
       RAISERROR(N'Error already raised. See previous errors.', 16, 1);
       return;
   end;
   print '@PersonId is not null';  --Condition when error is not raised
end;


You can see which queries are slow by enabling the slow query log (SET GLOBAL slow_query_log=1; and setting a suitable value for the long query time variable to set a threshold for what you consider to be "long". (E.g. maybe SET GLOBAL long_query_time=3; - the unit is seconds). However, queries may be slow for other reasons than high CPU utilisation, e.g. due to high I/O utilisation. Still, this can be a starting point for at least identifying poor queries.

The slow log is by default written to a file in the data dir called '${HOSTNAME}-slow.log'. You can then use a tool like mysqldumpslow to analyse this file, e.g.: mysqldumpslow -s t /var/lib/mysql/localhost-slow.log. It will extract each slow query and order them from slowest to least slow. Or you can use pt-query-digest to do the same thing

You can then enable query profiling for your session (SET profiling = 1;), execute each of the candidate queries you have identified in the slow query log, and do:

SHOW PROFILES;

This gives you a list of the recent queries and their query IDs. Use this ID to find the CPU utilisation for each query with:

SHOW PROFILE CPU FOR QUERY <query id>;

This will show the various stages of the query execution, and the duration and CPU utilisation (user and system) for each of them.

Note that this isn't 100% accurate - the documentation page for SHOW PROFILE notes that:

  Profiling is only partially functional on some architectures. For
  values that depend on the getrusage() system call, NULL is returned on
  systems such as Windows that do not support the call. In addition,
  profiling is per process and not per thread. This means that activity
  on threads within the server other than your own may affect the timing
  information that you see.

Note also that the page says SHOW PROFILE is deprecated as of MySQL 5.6.7, and that the performance_schema is meant to replace it; see Query Profiling Using Performance Schema. However, there doesn't seem to be a way to extract CPU utilisation info from it, so you may just have to use SHOW PROFILE CPU ...


Row deletion operation on pg table with numerous foreign keys

Create an SQL that does the same as the ON DELETE CASCADE would do. Remove the rows from the corresponding ~50 tables in the right order before you remove the users. This is some work but it keeps your data safe from an application error that does an accidental remove of a user with (lots of) messages.


cluster_database TRUE/FALSE what does it mean in Oracle RAC?

What the SIDs on all the nodes? Is this really the same DATABASE. For example you can have 3 node cluster having a single ASM on all the nodes, but database A is clustered on node1/node2. But database B runs only on the node 3.

The database B then is still started and stopped by the Clusterware, but is not clustered. You can also check output from:

ps -ef | grep -e lmd

The lmd process is started on clustered instances only.

EDITED: maybe I understand now. You do not have RAC database. RAC is active-active cluster. .i.e databases instances run on multiple nodes at the same time.

You have standalone instances guarded by Oracle Clusterware. So you have active-passive failover cluster. As described here :Using Oracle Clusterware to Protect
A Single Instance. Then you do NOT need the parameter cluster_database set to true. It only applies to RAC databases.