How to create a dummy Linked Server in SQL Server 2012

Create dummy, empty tables on the local server so that the procedure creation can happen.
Change the procedure code to use two-part synonyms. On the server that needs to use the linked server:

CREATE SYNONYM dbo.whatever FOR linkedserver.dbo.whatever;

On the local server that doesn't need the remote references to exist:

CREATE SYNONYM dbo.whatever FOR dbo.emptydummytable;

The stored procedure using the latter can be created thanks to deferred name resolution (which doesn't work when creating a procedure that references a linked server). This will require changing the stored procedure code, but it will be a one-time change.


Replicating a SQL Server database & Optimization

If I were you I'd be looking at AlwaysOn Availability Groups with a synchronous replica.  It'll add a couple of milliseconds of latency to the SQL commands when they are run, but it'll give you a second server that's available in the event of a server failure without any loss of data.  It requires no shared storage so you can keep using your local SSD drives.

It does require setting up Windows clustering but the SQL Server instances don't need to be clustered.


Which queries are faster with Postgres than with MySQL InnoDB

To avoid flame war, I'll just glance the way each storage work on querying, not really a benchmark. I'll use this table as reference (the code should be slightly modified to run on both RDBMS):

CREATE TABLE employees (
    emp_id int,
    name varchar,
    depto_no int,
    salary decimal,
    CONSTRAINT emp_pk PRIMARY KEY (emp_id);
);
CREATE INDEX emp_depto_idx ON employees (depto_no);

On PostgreSQL there will be 3 structures:

The employees heap, which is basically the table stored sequentially (just like you imagine a table)
The emp_pk index (which is also the primary key), stored as a B-tree index where each element has a pointer to the employee's heap, with the exact page/offset in disk
The emp_depto_idx index, that is like emp_pk, a B-tree with pointers to the heap, except that it does not enforce uniqueness

On MySQL InnoDB, there will be only two:

emp_pk and employees will be stored as one structure, a B-tree ordered by emp_id column, and just keep the value on the other columns as a payload in the leaf nodes.
emp_depto_idx index is a B-tree that on each element it will have the emp_id value referencing that row (not a physical location pointer).

Primary key lookup

  why PK lookup is much better for MySQL

I know you know that, but let's make it clear.

When you query it like:

SELECT * FROM employees WHERE emp_id = 10;

On PostgreSQL it can navigate through emp_pk index (one scan on the B-tree index) and then get the page/offset to get referencing row from employees heap table (one direct page/row fetch, not really a scan). So, one scan on the index, and one direct fetch on the heap.

On MySQL it will just navigate through primary key index (one scan on the B-tree index), as all the information is already there, no other lookup is required. So, just one scan on the index.

So, while PostgreSQL needs to do one scan and one fetch, MySQL just do one scan.

Secondary index lookup

  Why lookup through two indexes (InnoDB, lookup through non-PK index) is much slower? Does it requires two times more I/O or CPU? Can it compensate that huge benefit of PK lookup boost?

Now, assume this other query:

SELECT * FROM employees WHERE depto_no = 14;

On PostgreSQL, it won't be much different from the other. It will scan emp_depto_idx and then, for each row returned, fetch the value directly from the heap. So, one scan on the index, and a direct fetch on the heap for each row matched.

On MySQL it will scan the emp_depto_idx (one scan on the index), then, for each row returned it will get the referencing emp_id and scan the primary key index. So, one scan on a secondary index, and one scan on the primary index for each row matched.

See the difference? While PostgreSQL will do a scan and then fetch each matching row with a direct pointer, InnoDB will do a similar scan first, then another scan for each matching row. Now, it may be fast enough if department 14 has few employees, but really slow as it gets more employees (of course it will get slower on both RDBMS, but the curve is probably higher with InnoDB).

Full scan

  Why InnoDB sequential scans are slower?

Simple answer, because it is not really "sequential"...

Well, let's see the simplest (and certainly slow for all) query:

SELECT * FROM employees;

On PostgreSQL, it may can simple scan the whole employees heap, row by row, in its physical sequential order (no matter the insertion order here, what matters is how the tuples and pages are physically arranged now).

On InnoDB, it needs to traverse the index, which means more random scans (as index pages are not necessarily ordered physically and logically the same way).

If you think about magnetic disks, the difference is quite obvious, it is know that sequential access is way faster than random access. For SSDs it is not necessarily true, although there is still advantages on sequential access, like read-ahead. So in most scenarios, PostgreSQL's full scan will probably faster than InnoDB's, at least for considerable large tables (notice I didn't define what is "large", you must try it out and see where that difference really matters, it may just not matter in many cases). The best, for both RDBMS, is to design your model and your queries to avoid full scans, if possible.

Complex queries

  Internet says that Postgres is better for complex queries and subquering, but I still don't understand WHY is it better?

This is a huge topic, and probably the one that would generate more flame war, so I'll just give you some examples. It is common to say that PostgreSQL is better for complex queries, and it may be a true if you simple think on its query capabilities that are not present in MySQL (not considering performance yet), like:

Common Table Expression (CTE)
Window Functions
LATERAL joins
Arrays
JSON types, functions and operators
etc.

Besides that, there are many difference in the planner and executor of those two. For instance, PostgreSQL can do joins using Nested Loops, Hash Joins and Merge Joins, while MySQL can only do using Nested Loops. Despite that, MySQL has many optimizations in its Nested Loop algorithms, and PostgreSQL has a harder choice on its planner, and sometimes it makes mistake (but so does MySQLs).

Final words

This answer is just a really fast glance on the topic, there are still many things to consider for those two RDBMS when it comes to performance, like index-only scan, vacuum vs undo, parallelism, etc. The truth is that you can't simple say one is faster than the other, it is clear to me (is it to you?) that one can be faster in some environments while the other may be faster in others.


Try to export the data into csv format, and recreate the table in Vertica using the DDL from Oracle. Check the types as they differ from Oracle to Vertica.

Example: assuming the following table structure on Oracle:

CREATE TABLE PEOPLE
(
    ID NUMBER (10),
    NAME VARCHAR2 (50),
    ADDRESS VARCHAR2 (250),
    PRIMARY KEY (ID)
)

Assuming a compatible table structure on Vertica:

CREATE TABLE PEOPLE
(
    ID BIGINT NOT NULL    /*ORACLE datatype: NUMBER(10, 0)  */,
    NAME VARCHAR(50)      /*ORACLE datatype: VARCHAR2(50)   */,
    ADDRESS VARCHAR(250)  /*ORACLE datatype: VARCHAR2(250)  */,
    PRIMARY KEY (ID)
);

Load the table content using the copy command in Vertica:

copy "table name" from 'full csv file path'
    delimiter ',' null as '' exceptions 'full error file path';

Done!


How to restrict in SSMS to allow remote connections only specific IP / Host

SSMS is only a tool that connects to a SQL Server instance. SQL Server listens on a specific IP address for connections. It doesn't really care which tool is connecting.

SQL Server is not like PostgreSQL that has a pg_hba.conf where you can restrict connections, nor is it MySQL where you assign permissions to a user@source combination when creating the user login.

Answer

It's not currently possible by using simple means (PostgreSQL, MySQL), but if you are willing to start creating Logon Triggers (Microsoft Docs), then you might be able to build a restriction that meets your requirements.

The SQL Syntax for triggers can be found in the article CREATE TRIGGER (Transact-SQL) (Microsoft Docs). The article lists an example for a Logon Trigger.

Because your requirements are pretty dire, I am unable to provide a solid solution. Please edit your question and provide as much details as possible.

USE master
GO
CREATE LOGIN login_test WITH PASSWORD = 'password' MUST_CHANGE, CHECK_EXPIRATION = ON
GO
GRANT VIEW SERVER STATE TO login_test
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK
END

Alternative

You could equally create a blocking firewall rule or hack the routing table on the server / network.


Sql Server Restoring Process Time Takes Too Long Time

As long as you state that you did restore before and it took much less time I would quess that some of the following could be the reason:

some other process on the reporting server taking a lot of resources (in- and outside of SQL Server), that put high load on disks;
slow network connection between backups and reporting server if backups are located on a network share;
some process loading the disks with backups preventing fast reading of them by SQL Server.


Error 297, Severity 19 trying to create new table as sysadmin

This is not likely a permissions issue, at least not the type of permission you are thinking of. Typical permissions errors show up as follows:
Msg 262, Level 14, State 1, Line XXXXX
CREATE TABLE permission denied in database 'tempdb'.

Now that we have a more complete error message, we can see that indicates a "severity" of 19.
According to Microsoft SQL Server documentation:

Severity         Meaning

17-19          Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
19               Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.

This sounds more like (hypothetically) using SQL Server Express and reaching the 10 GB DB limit (even a CREATE TABLE statement needs to add meta data) than it does a permissions problem.
With more info it will hopefully be possible to narrow down the specific source of this. Here is what testing has shown so far:

database is not read-only
user = dbo
able to run statements in question on several other identical instances of this exact database just fine. Just one local instance giving the error
unable to create a table using CREATE TABLE dbo.[_test] ([Col1] INT);. but able to edit tables using SSMS and also add rows
unable to create anything, not just Tables (View / Function / etc). get the same error.
The size of the database is 5008.00 MB and space available is 114.16 MB
able to create a temp table while in the problem DB: USE [problem_db]; CREATE TABLE #Bob (Col1 INT);
able to create a real table in tempdb: USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT);
DBCC TRACESTATUS(-1); shows no trace flags in use
Compatibility mode is SQL Server 2014 (120), but have also tried 2017 (140)
able to create a schema in the problem DB: CREATE SCHEMA [_DeleteMe];
UNable to create a table in the new schema: CREATE TABLE [_DeleteMe].[_test] ([Col1] INT);
Owner SID did not match between sys.databases and dbo user in the problem DB, but that has been fixed and still not able to create a table
TRUSTWORTHY is OFF, but also OFF on instance where this DB is just fine

Here are some places to check for things can cancel submitted operations:
SELECT * FROM sys.triggers;
SELECT DB_NAME(ts.[database_id]) AS [Database],
       OBJECT_NAME(ts.[object_id], ts.[database_id]) AS [Trigger],
       OBJECT_DEFINITION(ts.[object_id]) AS [Definition],
       *
FROM   sys.dm_exec_trigger_stats ts;

SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;

SELECT * FROM sys.security_policies;


Multiple schema or multiple databases for better performance

It's impossible to evaluate the options without knowing what the actual problem is.

Your first port of call should be to identify the reason the servers run slowly - it may be memory or CPU, rather than disk throughput. Perfmon will be able to tell you this; you can also run Microsoft's PAL tool to get a better insight. If it's not disk throughput that's limiting you, option 2 and 3 are pointless.

Secondly, it may well be possible to optimize the queries - this may be cheaper, quicker and less risky than changing the infrastructure. Find the query that's running slowly, work out what's constraining it, and see if you can optimize it.

If you do find out that the queries are optimally tuned, and that it is disk throughput that's the bottleneck, I'd put each of the two back-end DBs on separate drives - it doesn't sound like they are competing against the front DBs (as they run at different times), but they might be competing against each other if both DBs are used in overnight batch jobs.


I can not get Postgres to listen on anything but localhost

I just wanted to make sure that this showed up as solved.

I had unknowingly installed a version 9.5 and a version 10. I had been editing the version 10 config, not knowing that both versions were running.

sudo service postgresql stop
sudo pg_renamecluster 10 main main_pristine
sudo pg_upgradecluster 9.5 main
sudo service postgresql start
sudo pg_dropcluster 9.3 main

Make sure you have copies of your postgresql.conf and pg_hba.conf files before you start as these got returned to default state when I did the upgrade.

Now it makes A LOT of sense why my config changes weren't taking effect ;-)

Many thanks to Colin 't Hart for the invaluable clue!


Transaction Logs: How much log data generated (hourly or daily)?

Lots of questions in there.
First, are we talking about transactional replication? I know you said that, but we need to be certain that you aren't talking about Always On Availability Groups, log shipping or some other solution to get a "copy" of your database.
Answered: yes, transaction replication - I will refer to it as just "replication".
No, replication doesn't copy VLFs. I think it extremely difficult to get anything usable from the transaction log.
First of all, consider the replication architecture:
An Agent job, the Log Reader, reads the transaction log, generates DML statements from this and stored them in the distribution database. So first question is whether Log Reader is local or not.
Then the next Agent job, the Distributor reads these DML commands in the distribution database and applies them to each subscriber. And you probably realize, there is not a 1:1 mapping of the size of the log records (binary data) and the DML commands as results from the log records (text). And, of course, the question is where the distributor is running.
And the final question is where the subsriber is. So, you should draw a picture with the involved databases, where you have network between the databases and where the Agent jobs are running.
And there's more: Some operations aren't carried over with transactional replication. One example is index rebuild. It can generate massive amount of log records (if full recovery model), but such an operation isn't executed on the suscriber - so it is ignored by the log reader.
And since you say subset, if you mean subset of the database: How would you, from the log, determine which log records apply to tables/rows that are in this subset?
I'm mainly raising issues here, just to point out how easy you can fall into a too simplified "solution" for this which can be misleading. This MS article talks about a few options that can be usable, when you have implemented replication.
Disclaimer: I'm no replication expert, so feel free to correct me, y'all.


How do I get HeidiSQL to connect to Postgres databases for the first time?

First of all, make sure you can connect to your PostgreSQL database from your Linux machine itself (if you have two, you'll have everything on both machines).

You do that using psql:

 psql -h localhost -p 5432 postgres

This assumes psql is in your path; if not... finding where it resides depends on your specific Linux version, PostgreSQL version and installation methods; please, specify them.

If you cannot connect to the database from the local machine... make sure it is running, and that it is the process actually using the 5432 port.

Next, you need to check your PostgreSQL configuration file settings. The file to check is called postgresql.conf. Its location, again, depends on how you installed it. Check Where are my postgres *.conf files?.

In the configuration file, you need to check for the parameteres listen_addresses and port, which indicates PostgreSQL which TCP-IP addresses and ports it should connect to. You might have port 5432 blocked, and PostgreSQL listening to it, but only for your 127.0.0.1 (localhost) IP address, but not listening to the "outside world". The default setting (AFAIK) is to listen only to 'localhost'.

If your Linux box is connected to the outside world through port 192.168.1.2, and you want to use the standard 5432 port, you should have:

listen_addresses = 'localhost, 192.168.1.2'
port = 5432

If you change any of these parameters, you should stop and restart the PostgreSQL service. [There are alternatives that don't need to stop, but at this point, I think this is the easiest way.]

Once you have PostgreSQL listening to the outside world, you still need to allow connections from users. That is done through the pg_hba.conf file. You need to have some user(s) allowed to connect to your database(s) from some IP address (or some range, or all of them, ...) using a specific authentication method. The most usual settings would look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# local net
host    all             all             192.168.1.0/24          md5

This means: all users can connect to all databases using a TCP/IP host on an address 192.168.1.0 .. 192.168.1.255, authenticating using md5 (meaning a password).

If your Linux box uses SELinux/iptables, make sure they allow the connections. Check, for instance Linux: Iptables Allow PostgreSQL server incoming request, or Connect to Postgres remotely, open port 5432 for Postgres in iptables.

If you need to setup SSL for PostgreSQL connections... check Secure TCP/IP Connections with SSL. I would suggest that you use SSL only after you have managed to have insecure (non-SSL) connections first.

Check the firewall in your Windows Machine, and/or any external Firewall between your Windows and your Linux machines. If any of them exists and are active, make sure they allow for communications to port 5432. Your specifics depend a lot on your actual setup. Resource: How to open firewall ports in Windows 10

Make sure you can ping your Linux box from within Windows. If you have telnet on your windows machine, try telnet (ip-of-your-linux-box) 5432 and check also that you can connect (as soon as you type something, you're very likely to get disconnected, that's ok).

At this point, HeidiSQL should be able to connect to your remote Linux machine. (Now, repeat with the 2nd one).


An SQL query to derive data from one to many rows based on another field

As far as I can tell, you want the "first" non-null value for the c3 column for the "group" that is defined through the c2 column.

This can be done with a window function:

select c1,
       c2,
       first_value(c3) over (partition by c2 order by c3 nulls last) as c3
from table_1

This does return the result you showed us.

Edit (after the question was changed)

To update the existing values you can use this:

update t1
  set c3 = tu.c3
from (
  select c1, c2, first_value(c3) over (partition by c2 order by c3 nulls last) as c3
  from t1
) tu
where t1.c1 = tu.c1
  and t1.c3 is null;


So it's a bit confusing - you're writing a SQL*Plus script, which accepts both SQL*Plus commands as well as SQL commands and PL/SQL blocks.

SQL*Plus commands are executed locally on your client machine, so they have access to other scripts on your local filesystem.

Everything inside a BEGIN..END block is PL/SQL code, and it'll behave differently than the rest of your script because it's a different language. The entire block of code is sent to the server and executed there, and the database doesn't know how to interpret sqlplus commands like prompt and @.

There are ways to have a PL/SQL block execute scripts on the database server - see this answer for more details - but I don't think that's really what you want.

It's hard to tell what you want from your example, but I'd suggest looking into handling sqlplus errors using a shell script - split this into two sqlplus scripts, then run the first script, check the return code, and if you got an error, run your second script. Sqlplus itself doesn't really have much in the way of built-in exception handling.


Testing Database Replication and timestamps are different

Do this on both machines:

show variables like '%zone%';

Also check the OS dates.

I think you will find a configuration difference.


If all you want to do is confirm that the input string consists of a sequence of <...> elements:

Split the string on the < character using your favourite string splitter
The first element must be blank
Subsequent elements must contain exactly one > character

The input string is valid if every element passes the tests above.

Example

String splitter

I am using the SQL#.String_Split4k streaming table-valued function from the free edition of the SQLCLR SQLsharp library by Solomon Rutzky.

If the input string might be longer than 4000 characters, the (less efficient) SQL#.String_Split function would be used instead (it handles strings up to 2GB).

Any efficient string splitter (T-SQL or otherwise) that can return empty elements correctly with a sequence number will work.

The example below shows the sort of output SQL#.String_Split4k returns.

The first parameter is the string to split. The second parameter is the delimiter. The third parameter (SplitOption) is set to 1, which means Keep Empty Elements.

SELECT
    SS.SplitNum,
    SS.SplitVal
FROM SQL#.String_Split4k(N'<c>', N'<', 1) AS SS;

Output:

╔══════════╦══════════╗
║ SplitNum ║ SplitVal ║
╠══════════╬══════════╣
║        1 ║          ║
║        2 ║ a>       ║
║        3 ║ b>       ║
║        4 ║ c>       ║
╚══════════╩══════════╝

Table and test data

CREATE TABLE #T
(
    row_id integer IDENTITY PRIMARY KEY,
    string varchar(50) NOT NULL
);

INSERT #T
    (string)
VALUES
    ('Hello World'),
    ('<Hello World'),
    ('<Hello World>');

Solution

SELECT
    T.row_id,
    string = (SELECT TOP (1) T2.string FROM #T AS T2 WHERE T2.row_id = T.row_id),
    is_valid =
        MIN
        (
            CASE
                -- First element must be blank
                WHEN SS.SplitNum = 1 AND SS.SplitVal = N'' THEN 1
                WHEN SS.SplitNum = 1 THEN 0
                -- Other elements must contain exactly one >
                WHEN SS.SplitVal NOT LIKE N'%>%' THEN 0
                WHEN SS.SplitVal LIKE N'%>%>%' THEN 0
                -- Otherwise valid
                ELSE 1
            END
        )
FROM #T AS T
CROSS APPLY SQL#.String_Split4k(T.string, '<', 1) AS SS
GROUP BY T.row_id
ORDER BY T.row_id;

Output

╔════════╦════════════════════════════╦══════════╗
║ row_id ║           string           ║ is_valid ║
╠════════╬════════════════════════════╬══════════╣
║      1 ║ Hello World   ║        1 ║
║      2 ║ <Hello World  ║        0 ║
║      3 ║ <Hello World> ║        1 ║
╚════════╩════════════════════════════╩══════════╝


Which domain to use for sql server developer

The Dev license is not about where, but rather how the installation is used.  If you are not using it for Production, its fine.    Separating out by domain is great, but not required.


How does autocommit=off affects bulk inserts performance in mysql using innodb?

If autocommit is turned off, you can always do a ROLLBACK to get back to the state the database was in before you started doing something.
COMMIT makes sure all changes to the database are written permanently, doing this for every INSERT takes more time than doing this for a  lot of INSERTs, because not only the records needs to be inserted, also the indexes needs to be updated. Doing this last piece for a lot of records is more efficient than doing this for 1 record at a time.


How to restore a database, ignoring the free space within each file?

Is there a way I can restore this database ignoring the free space in each file?

No you cannot. You can restore the database and then do a shrink of database file.

I assume that you are aware of all the caution that the smart people have given.

I Need to Shrink My Database - I just freed a lot of space
Why Does the Transaction Log Keep Growing or Run Out of Space? - since you have a large ldf file.

  Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

Why dont you use a data generator or from here or just get a sample of data from your PROD for testing ?


SQL Job fails due to QUOTED_IDENTIFIER = OFF

Clearly something has caused this to be changed

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

This can be set at the ODBC/OLEDB/ADO.NET layer, or at the SQLAGENT

MSSQL Client Config

Something changed the SQLAgent script or modified the Job parameters

If there is script running that alters the stored procedure and dropped the SET command.  Yes that could do it..  Why would this stored procedure need to be altered??


Why does my scalar UDF perform so differently on two different (but extremely similar) servers?

The situation that you described can happen when there's some kind of monitoring enabled (trace, extended event session, some third party tool), which does some kind of logging or work per UDF execution (or even per statement inside the UDF).

If the UDF is executed many times in a query there can be a very large amount of overhead to do that monitoring. If the monitoring is only occurring on one server then you would see a large performance difference between them.


RECURSIVE CTE does not use an INDEX. (Disabling seqscan forces it using an index however and it is faster)

There may be several causes; I cannot be certain, because you didn't post the EXPLAIN (ANALYZE, BUFFERS) output for both executions.

PostgreSQL might mis-estimate the row counts.  Running ANALYZE as you did is a good approach here, but in a recursive CTE the row counts are often hard to predict, and it is hard to fix these estimates.
If you don't mind a nasty trick, you could try adding another superfluous join condition to make PostgreSQL think that the result will have fewer rows:
JOIN events ev
   ON ev.match_id = dgt.match_id
      AND ev.seq = dgt.seq + 1
      AND ev.seq - 1 = dgt.seq

PostgreSQL might price an index scan too high, which induces it to choose a sequential scan and a hash join instead of a nested loop join.

If you have an SSD as disk, you should lower random_page_cost to 1 or 1.1 to give the PostgreSQL optimizer an idea that index scans are not four times as expensive as sequential scans.

If you have enough RAM, you should set effective_cache_size high enough, so that PostgreSQL knows that data are likely cached. That will also lower the cost of an index scan.


You need to use EXEC[UTE] or BEGIN ... END ;.

EXEC DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '');

Or:

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '');
END;
/


SQL Server: query performance (search 2 million rows)

Without indexes, any time  the search query is run, all 2 million records have to be searched to find the 650K 'FR' instances.  With indexes, the database can essentially go straight to them.  Even if you left the search query as-is, I think proper indexes would give you speed improvements.

As far as the temp table, I really don't see why that couldn't be done as a sub in the final results query.  Aside from that, I think temp tables would be more useful for persistent connections to the DB, or pools of users.  If you're just creating the temp table and immediately destroying it... then it's basically just being used as a subquery.

Update: M_M makes a good point in his comment on this answer.  However, I still feel an index would be better if the majority of activity is not just on sets where country is the only criteria.  For me (just my opinion) it would come down to how often the 'FR' subset would be needed by itself with no other criteria.  Otherwise, indexes could be used in the marjority of the searches.


How to spool output from SQL*PLUS to appear in single line with 1 space in between them?

You can use the following query to get all rows in single row.

SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
FROM your_table_name;

Sample Data:

SQL> select * from tbl1;

COL1
--------------------
file 1
file 2
file 3

Output:

tar cvzf file 1 file 2 file 3

To spool-

SQL> set heading off
SQL> spool 'output.txt'
SQL> SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
     FROM your_table_name;
SQL> spool off

Here is the fiddle.


Change the configuration file to increase max_connections.  But be aware that that can increase RAM usage.  How much RAM do you have?  What is the value of innodb_buffer_pool_size?
You must streamline the "registration process" as much as possible.  Please provide all the SQL that happens for registration so we can help optimize it.
Max_used_connections is a "high water mark".  You never had more than 10 connections at the same time, at least not since the last restart.  10 is fine.  And it cannot be changed.  Since it is much smaller than Max_connections (151), there does not seem to be an issue.  10 connections at a time is fine.
Look at the STATUS Connections.  That is a "counter" that has been running since restart.  It will probably say more than 3000.  3000 in 3 minutes is only 17/second, not difficult if the registration process is simple.
The timeout probably indicates something else -- such as too much data flowing across the network.  Does registration involve showing some biggish images on the registration page?
Instrument your registration process to see how many milliseconds the whole process takes, and how long each SQL statement takes.
What was the value of max_user_connections?


As I said, simplify it using count(DISTINCT...)

SELECT county,
  date_s,
  count(DISTINCT date_mdy) AS unique_date_count
FROM air_quality2
GROUP BY county,
  date_s
ORDER BY 1,2;

Now wrap it in crosstab

SELECT *
FROM crosstab($$
    SELECT county,
      date_s::int,
      count(DISTINCT date_mdy) AS unique_date_count
    FROM air_quality2
    GROUP BY county,
      date_s
    ORDER BY 1,2;
  $$,
  $$ SELECT DISTINCT county FROM air_quality2; $$
)
AS ct(
  year               int,
  "Bergen County"    int,
  "Essex County"     int,
  "Hunterdon County" int,
  "Middlesex County" int,
  "Monmouth County"  int,
  "Morris County"    int,
  "Union County"     int
);

Update

forgot to put the row_name year in the definition.
You may have to cast date_s to int
You may have to cast count(DISTINCT...) to int, (or bigint and change the types).
You will have to make sure you gave all the counties in your sample data. Or, paste the results of SELECT DISTINCT county FROM air_quality2;.


I assume you want to look in all attributes and all elements.

Use the function exist against the XML column in table B with a predicate to check for the existence of values from Table A. The function sql:column is used to get values from A into the XQuery expression.

declare @A table(Name varchar(50));
insert into @A(Name) values('John'),('Jim'),('Jason'),('Mike');

declare @B table(XMLCol xml);

insert into @B(XMLCol) values
('<Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle="">
  <ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" />
</Show>'),
('<vars>
  <var name="MatrixName"><value>="LockExitPairCompatability"</value></var>
  <var name="Jason"><value>=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)</value></var>
  <var name="MinNum"><value>=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)</value></var>
  <var name="Where"><value>="Site=''" + Root.Site + "'' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)</value></var>
  <var name="Where2"><value>=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)</value></var>
  <var name="CompatibleList"><value>=usr.FetchMatrix</value></var>
</vars>'),
('<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>');

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[. = sql:column("A.Name")]') = 1
                 );

// searches all descendants.

@ specifies that you are looking for attributes.

* is a wild card for the attribute name.

//text() gives you all descendant text values (not attributes).

(//@*, //text()) combines the attributes with the text values

[. = sql:column("A.Name")] a predicate that checks the current value against A.Name.

If you want to check if a value contains A.Name you should use the contains function.

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[contains(., sql:column("A.Name"))]') = 1
                 );

If need a case-insensitive comparison, lower-case both parameters to contains.


Oracle:How to determine the size of available shared memory of operating system while setting MEMORY_TARGET parameter for oracle instance?

You should go for the solution that fulfills your requirements.

Both shmall and /dev/shm are configurable, so first you should to configure them, and after that, you can set MEMORY_TARGET. The default value (on Oracle Linux 7) of shmall is 4 TB, which is irrelevant for most systems. The default size of /dev/shm is 50% of the physical memory.

If you use the default settings, following /dev/shm for sizing can be a viable option, but it may be suboptimal ("Unused memory is wasted memory").

To make things more complicated, MEMORY_TARGET includes PGA, which is not shared memory.

Also, my personal opinion/practice: whenever I can, I use ASMM (sga_target/sga_max_size + pga_aggreate_target) instead of AMM (memory_target/memory_max_size). Especially since hugepages works with ASMM, but not with AMM.


Is there any disadvantage to using Oracle's Easy Connect syntax?

The only real downside is that there are a number of configuration options that you can put in a TNS alias that you can't use when you use the easy connect syntax.  Easy connect is designed to simplify the syntax for simple connections.  In order to do that, though, it loses the ability to create more complex TNS aliases that do things like load balancing/ failover or that request a dedicated server session.  If you don't need any of that, there is no downside to using the simpler syntax.  As soon as an organization has one use case that uses something that the easy connect syntax doesn't support, though, it either has to support a hybrid configuration where some things use easy connect and others use TNS aliases or it has to use TNS aliases everywhere.

Supporting hybrid configurations or changing how connections are handled can be somewhat painful depending on your perspective.  It's relatively easy for the Oracle client to handle a bunch of different naming conventions.  It's much more difficult for an organization to support.

It's relatively easy for organizations to come up with ways of distributing a single, shared tnsnames.ora file to everyone or to stand up a central LDAP server to host the organization's TNS information.  If some applications use easy connect syntax and some organizations use TNS aliases, that can be problematic when an organization wants to do things like consolidate databases or move databases from one host to another.  If an organization wants to add load balancing support, it can be problematic when some applications fail over automatically and some don't.  Someone has to track which applications are which, admins have to treat applications differently rather than being able to count on organization conventions that are adhered to across the board.  None of this is impossible, of course.  But consistency across the environment makes DBAs and admins much more productive and allows teams to support many more applications/ databases/ whatever with any given level of staffing.


How can I append a column to an inner SQL statement?

INSERT INTO A_WITH_ADDITIONAL_COLUMN (newcol, col1, col2, col3,...)
    SELECT NULL, A.col1, A.col2,a.col3,..... FROM A;

Should do what you're asking.


To solve your issue, I did the following:

Create table and data:

CREATE TABLE salesperson (id INTEGER, salary INTEGER);

INSERT INTO salesperson VALUES (1, 100000);
INSERT INTO salesperson VALUES (2,  80000);
INSERT INTO salesperson VALUES (3,  60000);
INSERT INTO salesperson VALUES (4,  50000);
INSERT INTO salesperson VALUES (5,  30000);
INSERT INTO salesperson VALUES (6,  25000);
INSERT INTO salesperson VALUES (7,  12000);
INSERT INTO salesperson VALUES (8,  10000);

Then run this query (SQLFiddle here):

WITH cte AS
(
  SELECT id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
  FROM salesperson
)   --     <<== you can do a SELECT * FROM cte to see what's happening!
SELECT
       id,
       salary,
       CASE quartile
         WHEN 1 THEN salary * 1.08
         WHEN 2 THEN salary * 1.08
         WHEN 3 THEN salary * 1.06
         WHEN 4 THEN salary * 1.04
       END as new_salary
FROM cte;

Result:

ID  SALARY  NEW_SALARY
1   100000  104000
2   80000   83200
3   60000   63600
4   50000   53000
5   30000   32400
6   25000   27000
7   12000   12960
8   10000   10800

HTH and welcome to the forum! :-)

You can read the Oracle NTILE documentation here and an explanation from the excellent oracle-base site here.

EDIT:

You can also do it in one pass - but I think the cte (COMMON TABLE EXPRESSION - or WITH clause) shows the thought process more clearly. Thus:

SELECT
       id,
       salary,
       CASE NTILE(4) OVER (ORDER BY salary)
         WHEN 1 THEN salary * 1.08
         WHEN 2 THEN salary * 1.08
         WHEN 3 THEN salary * 1.06
         WHEN 4 THEN salary * 1.04
       END as new_salary
FROM salesperson
ORDER BY id;

Same result (fiddle)!


Difference between On Delete Cascade & On Update Cascade in mysql

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here.

In the SQL 2003 standard there are 5 different referential actions:

CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

To answer the question:

CASCADE

ON DELETE CASCADE means that if the parent record is deleted, any child records are also deleted. This is not a good idea in my opinion. You should keep track of all data that's ever been in a database, although this can be done using TRIGGERs.  (However, see caveat in comments below).
ON UPDATE CASCADE means that if the parent primary key is changed, the child value will also change to reflect that. Again in my opinion, not a great idea. If you're changing PRIMARY KEYs with any regularity (or even at all!), there is something wrong with your design. Again, see comments.
ON UPDATE CASCADE ON DELETE CASCADE means that if you UPDATE OR DELETE the parent, the change is cascaded to the child. This is the equivalent of ANDing the outcomes of first two statements.

RESTRICT

RESTRICT means that any attempt to delete and/or update the parent will fail throwing an error. This is the default behaviour in the event that a referential action is not explicitly specified.

  For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT`.

NO ACTION

NO ACTION: From the manual. A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

SET NULL

SET NULL - again from the manual. Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. This is not the best of ideas IMHO, primarily because there is no way of "time-travelling" - i.e. looking back into the child tables and associating records with NULLs with the relevant parent record - either CASCADE or use TRIGGERs to populate logging tables to track changes (but, see comments).

SET DEFAULT

SET DEFAULT. Yet another (potentially very useful) part of the SQL standard that MySQL hasn't bothered implementing! Allows the developer to specify a value to which to set the foreign key column(s) on an UPDATE or a DELETE. InnoDB and NDB will reject table definitions with a SET DEFAULT clause.

As mentioned above, you should spend some time looking at the documentation, here.


In what situation the .NDF files are created?

You have a database with a datafile(PRIMARY) in a filegroup(PRIMARY) which stores metadata as well as user data. When you reach the maximum size of the datafile(depended on underlying Operating System) then you need to add an extra file which is know as secondary data file. According to the reference, datafile can grow up to 16TB, so for normal sized database this situation may no arise. So it's optional to have any secondary datafiles.

However, these can be used to spread data across multiple disks by putting each file on a different disk drive.

  I'm curious to know under what conditions the .NDF files are generated ?

When you add extra datafiles on a database.

  If it is generated, how we can know which data base is behind it ?

It is added on A DATABASE. So you need to know database first in order to add the secondary data files. If you know that database then you can know the it's datafiles.

More details and useful links are given here


I assume that your tablespace name is tblspc.

Get Oid of your tablespace

test=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname
-------+---------
 24580 | tblspc
(1 row)

Stop postgres

$ pg_ctl -D $PGDATA stop

Move directory from old one to new one

$ mv /old/dir /new/dir

or

$ cp -r /old/dir /new/dir
$ rm -rf /old/dir

Change tablespace's link to new directory

$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580

Start postgres

$ pg_ctl -D $PGDATA start

Then, you can see the tablespace's directory has changed.

test=# SELECT pg_tablespace_location(24580);
 pg_tablespace_location
------------------------
 /new/dir
(1 row)


Generate rows based on value in 2 columns. Bug?

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row.

What I would do is to define m to return a single row with the minimum and maximum values, and then subtract these in n to get the number of rows you need to generate.

Something like (untested):

with m as (
  select max(to_number(substr(t.END,3))) - min(to_number(substr(t.START,3))) as num
  from FCS_FP_TOMB t
), n as (
  select level - 1 as n
  from m
  connect by level <= m.num + 1
)
select ft0.*, regexp_substr(ft0.START,'([A-])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0
join n on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))
order by 2,3,7;


MongoDB replicaset number of connections too high

As Antonis said, the number of connections has little relation to the number of databases.

In general, the number of connections aren't something to worry about. The MongoDB drivers keep the connections alive to reuse them and to prevent the overhead of setting up new connections.

However, each connection is provided with about 1MB of stack server side. Unnecessary connections might eat up precious RAM, which is used by MongoDB to for the indices and as much of the working set as possible in order to speed things up.

In case you have enough RAM on your server, you have nothing to worry about – just adjust your alert thresholds to more suitable numbers. If you have a lot of page faults, however, you should investigate a bit further.

Since you are using connection pooling, it is safe to assume that you either more concurrent connections on your application than your MongoDB server (hardware, that is) can handle, or you are opening unnecessary connections.

High number of concurrent connections

As a rule of thumb, your MongoDB should be able to handle as many connections as you have concurrent request. In order to give you a decent amount of time to scale out when you reach the server's limits, your alert should trigger at about 80% utilization. For example, let's assume your server can handle about 1500 connections easily, your alert go off at

  1500 * 0.8 = 1200 connections

If your server gets into problems with the 1000 connections you mentioned or when you hit 80% utilization, you should first scale up , for example by putting more RAM into the machine or – more generally speaking – eliminate the limitation which prevents the server from handling this number of  connections. Which point to scale up to is not easy to determine, but generally speaking, you want to scale up as long as you get more bang than you have to put bucks into it.

There is a point where the bang you get for each buck you put in decreases drastically – of course you want to stop scaling up a bit earlier. Now what can you do in case your server still does not meet your requirements? The answer is to scale out, which in MongoWorld means setting up a sharded cluster. A word of warning: While creating a sharded cluster is no rocket science, there are quite some caveats and pitfalls. Make sure you have read the documentation about sharding thoroughly before implementing a sharded cluster. A good consultant usually is worth the money, too.

That being said: Usually it is the application server which first reaches the limit of concurrent users it can handle, so have a close look there.

Multiple open connections per concurrent user

Usually, you request a connection from the pool by doing your stuff on the reused db object and the connection is returned transparently to the pool after the stuff is done (simplified, but should be sufficient in this context). The pool is handled by the client transparently. Each time you use MongoClient.connect, a connection pool is created. You should only call this method once per application and reuse it. Doublecheck if you follow the described pattern.

Conclusion

Make sure you reuse the db object returned by MongoClient.connect
Find out your number of concurrent users.
Check wether the number of connections made to the replica set is much higher than the number of concurrent users. If not, everything is working as expected.
If the numbers roughly match and you are experiencing problems from the database side (long response times, high latency), either scale up or out after you made sure that it is not your application slowing the responses down.


In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

define my_schema_name = 'SCHEMA_1';

CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER(
     p_userid IN DBUSER.USER_ID%TYPE,
     p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN

  UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

  COMMIT;

END;


Assign maximum memory limit for a particular database in sql server

There is no way to limit the amount of memory a specific database takes.  SQL Server uses a least used / first evicted from memory policy (basically).


Using the topology you mentioned from the MySQL Documentation

Let's setup the first scenario
EXAMPLE IPs for Each DB Server

Master1 : 10.20.30.40
Master2 : 10.20.30.50
Slave_1 : 10.20.30.60
replication user is repluser@'%'
replication password is replpass

Make sure binary logging is enabled on all the Slaves
We will do the following

Promote Master2 to Master1
Promote Slave_1 to Master2
Demote Master1 to Slave1

Step 01 : On Master2, run the following
mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;

Step 02 : Move your CNAME/VIP to Master2
Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;
Step 04 : On Slave_1, dump the data
NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql

Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1
Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1
Don't worry about the real binary log filename and position.
Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.
You can see it with the following
less MySQLData.sql.gz | head -35 | tail -1

Step 07 : Load the data into Master1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

Step 08 : Login to MySQL on Master1
mysql> SHOW SLAVE STATUS\G

and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes
EPILOGUE
When Done, the topology should be

Master1 : 10.20.30.50
Master2 : 10.20.30.60
Slave_1 : 10.20.30.40

DISCLAIMER
Please try this out on Test Servers before deploying to Production
GIVE IT A TRY !!!
Note: I would advise that you have multiples slaves

One for Nightly Backups
The others for load balancing SELECTs


Starting oracle rac node2 error: ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

You need to make sure the the Grid Infrastructure services are started first. They provide all of the networking and ASM storage for the cluster, and you can't start a database instance without them.
On Linux, as root, run crsctl start crs
The command crsctl stat res -t should show you the state of all cluster services.


Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive?

Did you search your system catalogs for stored procedures that might be calling them? In DBs where you see this:

SELECT OBJECT_NAME([object_id]), definition
   FROM sys.sql_modules
   WHERE definition LIKE '%sp[_]OA%';

To do this for all databases at once:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10)
   + 'SELECT ''' + name + ''', o.name, m.definition
   FROM ' + QUOTENAME(name) + '.sys.objects AS o
   INNER JOIN ' + QUOTENAME(name) + '.sys.sql_modules AS m
   ON o.[object_id] = m.[object_id]
   WHERE m.definition LIKE ''%sp[_]OA%'';'
FROM sys.databases;

EXEC sp_executesql @sql;

You might also check job steps.

SELECT j.name, s.step_name, command
   FROM msdb.dbo.sysjobsteps AS s
   INNER JOIN msdb.dbo.sysjobs AS j
   ON s.job_id = j.job_id
   WHERE s.command LIKE '%sp_OA%';

If you don't find anything there, you will want to run a server-side trace because it must be coming from ad hoc batches.


CPU usage on RDS instance monotonically increasing with no change to query volume

I have some queries for you regarding table sizes that you can run in MySQL during these spikes

Database size in terms of StorageEngine (MB)

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Database size in terms of Databases (MB)

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT
IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Database size in terms of Database/StorageEngine (MB)

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(REPLACE(FORMAT(
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Pay attention to certain markers

Innodb_buffer_pool_pages_dirty
Innodb_data_reads
Innodb_data_writes

I recommend downloading MySQL Administrator (I know, it's old but I still you it for quick and dirty "I WANNA SEE STATS NOW" moments of day) and set it up. I customized my own graphs to watch the size of the InnoDB Buffer Pool and its dirty pages. You could also just use the Connection Health tab.


What is the best way to find out the sum of a field from a table?

VIEWs do not improve performance.  (You are talking about CREATE VIEW, correct?)

Either of these is perfectly good:

SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id = $id
SELECT SUM(debit - credit)      FROM debit_credit WHERE ledger_head_id = $id


Row size limits in Mysql w.r.t to TEXT and BLOB columns

It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly.

https://github.com/jeremycole/mysql/blob/master/storage/innobase/include/btr0cur.h#L762

/** The reference in a field for which data is stored on a different page.
The reference is at the end of the 'locally' stored part of the field.
'Locally' means storage in the index record.
We store locally a long enough prefix of each column so that we can determine
the ordering parts of each index record without looking into the externally
stored part. */
/*-------------------------------------- @{ */
#define BTR_EXTERN_SPACE_ID     0   /*!< space id where stored */
#define BTR_EXTERN_PAGE_NO      4   /*!< page no where stored */
#define BTR_EXTERN_OFFSET       8   /*!< offset of BLOB header
                        on that page */
#define BTR_EXTERN_LEN          12  /*!< 8 bytes containing the
                        length of the externally
                        stored part of the BLOB.
                        The 2 highest bits are
                        reserved to the flags below. */
/*-------------------------------------- @} */


SQL Server - update all data in all tables from backup

Create a table on your QA environment inside a service database or master. Something like below:

CREATE TABLE [dbo].[SQLAccess](
    [DBName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [UserName] [sysname] NOT NULL
)

Fill the table with users and logins that have to be remapped:

INSERT SQLAccess (DBName, LoginName, UserName)
VALUES
('YourRestoredDBName', 'TestServiceLogin', 'ProdServiceLogin'),
('YourRestoredDBName', 'TestDomain\ADUser', 'ProdDomain\ADUser')
-- and so on

After restoring:

USE [YourRestoredDBName];

DECLARE @SqlStatement NVARCHAR(max)
    , @LoginName    sysname
    , @UserName     sysname

DECLARE MyCursor Cursor
FOR
    SELECT LoginName
        , UserName
    FROM YourServiceDBName.dbo.SQLAccess
    WHERE DBName = 'YourRestoredDBName'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @LoginName, @UserName

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @SqlStatement = 'ALTER USER [' + @UserName + '] WITH LOGIN = [' + @LoginName + ']'

    EXEC sp_executesql @SqlStatement

    FETCH NEXT FROM MyCursor INTO @LoginName, @UserName
END

CLOSE MyCursor
DEALLOCATE MyCursor

As a result you will get a database where your test logins have access to test data the same way as prod logins had on the prod environment.

Of course, it is just a concept, feel free to expand the solution with adding logging, error handling, adding additional roles or permissions for some users, etr.


Getting the "flat" query corresponding to a view using nested views?

So I know this doesn't directly answer your question but I think are things to consider that may be of help:
Why do you lose the ability to create Views and would you be able to create a second database to house your Views instead? (Views can reference other databases besides the one they exist in.)
If the previous solution isn't possible, could you use a data synchronization feature like Replication or Availability Groups to copy the SAP database to another database where you would be able to create your Views (either on the same server or another server)?
After getting a better picture from your comments, you can still look into Replication to centralize the data into one database outside of SAP, that your product can work off of. Another feature in SQL Server useful for consolidating multiple sources of data into one destination is SSIS.


EXPLAIN the SELECT subquery.  (This will workaround the point that jynus brought up.)

Your KEY vigi_unique can be made somewhat better by adding id onto the end of it.  This will change the EXPLAIN to say "Using index" because it will be a "covering index".

But the real reason for 5.6 to be faster is ...

The subquery is after "LEFT JOIN", so 5.5 evaluates it over and over (I think), and
5.6 will create an INDEX (on the fly) for the subquery.

Suggest using EXPLAIN FORMAT=JSON SELECT ... on 5.6 to see a little more.

You should consider moving to InnoDB.

Another way to "dedup" is to create a new table (with a UNIQUE index to prevent future dups), populate it with INSERT ... SELECT DISTINCT ..., then RENAME TABLE to move it into place.


You need to think about your partitions as if they are separate tables.

A clustered index on a partitioned table effectively has the partition number as its first key. For you, with a partition per unique PartitionKey, the clustered index will essentially have that column in the front of it. Or as good as...

I'd expect having Id first would feel better, in case you don't know the month and need to search all partitions. It'll seek on each partition rather than scan them.


How to view user created table in OracleDB?

Is there any other command to view just the tables that I created?

If you know the table name just add it in a WHERE clause:
SELECT table_name
     FROM user_tables
     where upper(table_name) = 'MY_NEW_TABLE'
     ORDER BY table_name;

Or perhaps something like this:
select owner,
       object_name
from dba_objects
where object_type = 'TABLE'
  and created_date > trunc(to_date('05-15-2021','dd-mm-yyyy'))
  order by owner,
           object_name;

Also can someone also explain me, what these tablespace are while
executing the following command

SYSTEM is where the data dictionary lives.  Don't touch it. Don't create your own objects in it.
SYSAUX is an auxiliary to SYSTEM.  Treat it the same.
UNDOTBS1 is where oracle writes 'undo' information - information needed to rollback a transaction.  Not only used for actual rollback, but when a query needs a transactionally-consistent view of the data.
TEMP is the 'temp' tablespace.  Used when operations like sorting cannot be completed in memory.  Kind of like Oracle's version of the OS's page file.
USERS - created by oracle by default to be the default tablespace assigned to users.

More information can be found in the Database Concepts manual.
-Expanding original answer to address additional questions:

So where my user created tables are stored, I mean in which
tablespace?

If you did not specify the TS when you created the table, it will be in the default TS for the user that created it.   You can check this with
select username,
       default_tablespace
from dba_users
order by username
;

To check specifically for which TS a table is in:
select owner,
       table_name,
       tablespace_name
from dba_tables
order by owner,
         table_name
;

What if there are 100 of user created tables, how do I view in that
case of course I can't filter by table name in that case

Then remove the WHERE clause, and (if using sqlplus) spool the output to a text file, which you then open with a text editor:
spool myfile.txt
select .....
spool off


Find Rows with the Minimum Date per Group

If you are using SQL Server 2005 onward you can use the ROW_NUMBER() function:

WITH cte_GetLatest
AS
(
  SELECT ID, [Date], Alloc, Prod, Water, Tub, Cas,
         ROW_NUMBER() OVER (PARTITION BY ID,
                                         Alloc,
                                         Prod,
                                         Water,
                                         Tub,
                                         Cas
                            ORDER BY Date) as rownumber
  FROM Example

)
SELECT *
FROM cte_GetLatest
WHERE rownumber = 1

Lothar


How can I make this DECLARE PROCEDURE working?

And that is a great example why you should never name your PL/SQL variables the same as column names.
set serveroutput on
DECLARE
  PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM DBA_USERS u
    WHERE u.USERNAME = userName;
  dbms_output.put_line('User count: ' || dbcount);
end;
begin
  create_user('abc', 'abc', 'abc');
end;
/

User count: 26

The u.USERNAME = userName filter is username = username, which returns all rows where username is not null.


How to structure a model to properly and efficiently represent tree-like data on relational databases?

I believe he is going for something like a binary tree.  I would just include three keys that are tied to the unique id of the same table, one for the left, one for the right child, and one for the parent.

i.e.- (very much pseudocode)

TABLE tree
int         id                  autoinc
varchar(16) data_you_care_about
int         parent_id
int         left_child_id
int         right_child_id

FOREIGN KEY parent_id = tree.id
FOREIGN KEY left_child_id = tree.id
FOREIGN KEY right_child_id = tree.id


How do I corrupt an mdb intentionally for test purposes

How to launch the Detect and Repair Tool you may see below

Start MS Access 2007.
Click the MS Access button and click the PowerPoint options button.
Select the Resource Tab.
Click the Diagnose button.

But also you can get more resources to solve problem .mdb file

http://www.itninja.com/question/copying-1-2-gb-mdb-from-pc-to-network

Ultimate solution you may use only if previous methods didn't work, that denotes your .mdb corruption is severe, see and try it http://www.mdb.openfiletool.com/ MDB Open File Tool


Excel Values Uploading as NULL to database

Answer originally left as comments

This is a data type issue on Excel's side, and the file was probably created by someone else. By copying and pasting using Notepad, you're stripping all formatting and effectively converting the data types to strings. I'm betting it's a date field or a number that is being formatted in an odd way and the SQL implicit conversion is failing because of a leading apostrophe or something ridiculous. – Randolph West

Unfortunately, importing Excel data is rife with difficulties like this. You'd be far better off to get the source data in another format such as CSV, or better yet, a binary format of some type. – Max Vernon


changing domain name for Mongodb primary

To add replica set members with hostname, pass the host name when initializing the replica set.

rs.initiate(
    {
        _id: "config",
        version: 1,
        members: [
        {_id: 0, host: "vagrant-ubuntu-trusty-64:27022"}
        ]
    }
)

  _id --> is your replica set name

Or if you want to edit the existing configuration follow the below steps

var conf = rs.conf()

conf.members[0].host="vagrant-ubuntu-trusty-64:27022"

rs.reconfig(conf)

  If you have more than one member in your replica set, all members
  should refer a hostname. Which means all members should be localhost or all members should be hostnames


By default MySQL uses ft_min_word_len=3 setting which tell MySQL full-text engine to ignore all the words LESS than 3 letters. You need to decrease this variable. It's not dynamic so you may need to restart MySQL. However setting it to less than 3 may affect performance.


How to create Postgres DB with case insensitive collation

This may be too late for the original poster, but for completeness, the way to achieve case insensitive behaviour from PostgreSQL is to set a non-deterministic collation. This is only for Postgres 12.

Details are described in docs here. Reproducing relevant portion for completeness:

  A collation is either deterministic or nondeterministic. A
  deterministic collation uses deterministic comparisons, which means
  that it considers strings to be equal only if they consist of the same
  byte sequence. Nondeterministic comparison may determine strings to be
  equal even if they consist of different bytes. Typical situations
  include case-insensitive comparison, accent-insensitive comparison, as
  well as comparison of strings in different Unicode normal forms. It is
  up to the collation provider to actually implement such insensitive
  comparisons; the deterministic flag only determines whether ties are
  to be broken using bytewise comparison. See also Unicode Technical
  Standard 10 for more information on the terminology.

  To create a nondeterministic collation, specify the property
  deterministic = false to CREATE COLLATION, for example:

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

  This example would use the standard Unicode collation in a
  nondeterministic way. In particular, this would allow strings in
  different normal forms to be compared correctly. More interesting
  examples make use of the ICU customization facilities explained above.
  For example:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

  All standard and predefined collations are deterministic, all
  user-defined collations are deterministic by default. While
  nondeterministic collations give a more “correct” behavior, especially
  when considering the full power of Unicode and its many special cases,
  they also have some drawbacks. Foremost, their use leads to a
  performance penalty. Also, certain operations are not possible with
  nondeterministic collations, such as pattern matching operations.
  Therefore, they should be used only in cases where they are
  specifically wanted.


How to recover data around corrupted data in table?

In the end, I truncated the table as the data wasn't essential and this cleared out the corruption. I know this won't be a solution for most folks - usually you need the data.


Why Are My Mirrored Databases Changing States on Their Own?

Can you please look at SQL Server errorlog and see if you could find some relevant information on both Principal and mirror. Generally if SQL Server CPU utilization goes high above 50 % there can be failover. This is specifically caused by heavy load on SQL Server this is documented in Below Microsoft Web resource See 'recommendation for Configuring partner Server' in link : http://msdn.microsoft.com/en-us/library/ms366349%28v=sql.100%29.aspx

This can be avoided by Alter partner time out

  Alter database db_name set partner timeout 20

Edit: After User provided more details

  Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.

As per this message its clear that network connecting principal and mirror flipped I dont know for how much time but this caused the failover. What happens is Mirror server keeps sending pings to Principal whether it is alive or not and suppose Principal goes down and now mirror will send 10 pings to principal(By default) and if it does not gets any response it will initiate failover with help of Witness( its little more complex with witness ,it involves quorum)

If it was for moment you can increase partner timeout( not a good practice though) but you should Immediately speak to network team and ask them RCA for this glitch as this caused failover. My course of action would be to meet them personally ask them to run bunch of commands to check response time


Any workarounds for lack of primary key on partitioned tables?

The most common solution is to make an exception and not add the foreign key. I guess this must be very large tables and there are usually not that many of them, so a few exceptions for better performance are typically acceptable. You could try to enforce the foreign key relationship with triggers, though it is messy. I don't know of a better alternative for PostgreSQL 10.


Postgresql partitioned table timestamptz constraint problem

I cannot fully respond as to why date_trunc('day', CURRENT_TIMESTAMP) is not equivalent to a constant... even if both CURRENT_TIMESTAMP and date_trunc are defined as IMMUTABLE.

But I think we can make an experimental educated guess: Apparently, the PostgreSQL planner does not evaluate functions. As such, it doesn't have any good way to know which partitions to check, and makes a plan that checks them all.

Experimental check

We create a base (parent) table:

 -- Base table
 CREATE TABLE reports
 (
     rpt_datetime timestamp without time zone DEFAULT now() PRIMARY KEY,
     rpt_unique_clicks integer NOT NULL DEFAULT 1,
     something_else text
 ) ;

We create an auto-partition insert trigger:

 -- Auto-partition using trigger
 -- Adapted from http://blog.l1x.me/post/2016/02/16/creating-partitions-automatically-in-postgresql.html
 CREATE OR REPLACE FUNCTION create_partition_and_insert ()
 RETURNS TRIGGER AS
 $$
 DECLARE
     _partition_date text ;
     _partition_date_p1 text ;
     _partition text ;
 BEGIN
     _partition_date := to_char(new.rpt_datetime, 'YYYYMMDD');
     _partition := 'reports_' || _partition_date ;

     -- Check if table exists...
     -- (oversimplistic: doesn't take schemas into account... doesn't check for possible race conditions)
     if not exists (SELECT relname FROM pg_class WHERE relname=_partition) THEN
        _partition_date_p1 := to_char(new.rpt_datetime + interval '1 day', 'YYYYMMDD');

        RAISE NOTICE 'Creating %', _partition ;

        EXECUTE 'CREATE TABLE ' || _partition ||
              ' (CHECK (rpt_datetime >= timestamp ''' || _partition_date || ''' AND rpt_datetime < timestamp ''' || _partition_date_p1 || '''))' ||
              ' INHERITS (reports)' ;
     end if ;

     EXECUTE 'INSERT INTO ' || _partition || ' SELECT(reports ' || quote_literal(NEW) || ').* ;' ;

     -- We won't insert anything on parent table
     RETURN NULL ;
 END
 $$
 LANGUAGE plpgsql VOLATILE
 COST 1000;

 -- Attach trigger to parent table
 CREATE TRIGGER reports_insert_trigger
 BEFORE INSERT ON reports
 FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

Fill the (partitioned) table with some data; and check the partitions the trigger made:

 INSERT INTO
     reports (rpt_datetime, rpt_unique_clicks, something_else)
 SELECT
     d, 1, 'Hello'
 FROM
     generate_series(timestamp '20170416' - interval '7 days', timestamp '20170416', interval '10 minutes') x(d) ;

 -- Check how many partitions we made
 SELECT
     table_name
 FROM
      information_schema.tables
 WHERE
     table_name like 'reports_%'
 ORDER BY
     table_name;

 | table_name       |
 | :--------------- |
 | reports_20170409 |
 | reports_20170410 |
 | reports_20170411 |
 | reports_20170412 |
 | reports_20170413 |
 | reports_20170414 |
 | reports_20170415 |
 | reports_20170416 |

At this point, we check two different queries. The first one does use a constant compared to rpt_datetime:

 EXPLAIN (ANALYZE)
 SELECT
     SUM(rpt_unique_clicks)
 FROM
     reports
 WHERE
     rpt_datetime >= timestamp '20170416' ;

Using a constant timestamp, only 'reports' and the appropriate partition are checked:

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=25.07..25.08 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)                                 |
 |   ->  Append  (cost=0.00..24.12 rows=378 width=4) (actual time=0.009..0.010 rows=1 loops=1)                             |
 |         ->  Seq Scan on reports  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)             |
 |               Filter: (rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone)                              |
 |         ->  Seq Scan on reports_20170416  (cost=0.00..24.12 rows=377 width=4) (actual time=0.006..0.007 rows=1 loops=1) |
 |               Filter: (rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone)                              |
 | Planning time: 0.713 ms                                                                                |
 | Execution time: 0.040 ms                                                                                |

If we use the equivalent SELECT using a function-call (even if the result of this function call is a constant), the plan is completely different:

 EXPLAIN (ANALYZE)
 SELECT
     SUM(rpt_unique_clicks)
 FROM
     reports
 WHERE
     rpt_datetime >= date_trunc('day', now()) ;

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=245.74..245.75 rows=1 width=8) (actual time=0.842..0.843 rows=1 loops=1)                               |
 |   ->  Append  (cost=0.00..238.20 rows=3017 width=4) (actual time=0.837..0.838 rows=1 loops=1)                           |
 |         ->  Seq Scan on reports  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)             |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |         ->  Seq Scan on reports_20170409  (cost=0.00..29.78 rows=377 width=4) (actual time=0.214..0.214 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170410  (cost=0.00..29.78 rows=377 width=4) (actual time=0.097..0.097 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170411  (cost=0.00..29.78 rows=377 width=4) (actual time=0.095..0.095 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170412  (cost=0.00..29.78 rows=377 width=4) (actual time=0.096..0.096 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170413  (cost=0.00..29.78 rows=377 width=4) (actual time=0.131..0.131 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170414  (cost=0.00..29.78 rows=377 width=4) (actual time=0.098..0.098 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170415  (cost=0.00..29.78 rows=377 width=4) (actual time=0.095..0.095 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170416  (cost=0.00..29.78 rows=377 width=4) (actual time=0.004..0.005 rows=1 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 | Planning time: 0.298 ms                                                                                |
 | Execution time: 0.892 ms                                                                                |

dbfiddle here


group by, order by and pick last value of a column in each group

I suppose you want a "greatest-n-per-group" query. The easiest (but not necessarily the most efficient) way to achieve this in Postgres is using DISTINCT ON:

select distinct on (item_id)        -- defines the "group"
    item_id, date, value_num        -- show 1 row per group
from
    events
order by
    item_id, date desc ;            -- which order to be used


Same session ID(with same SQL text) but 3 different connection ids

The clue here is that those connections each share a "parent_connection_id," which per the docs:

  Identifies the primary connection that the MARS session is using

I noticed the aliases used in those queries heavily implies your app is using Entity Framework.  Default connection settings (scaffolded out by the Visual Studio project templates) will enable Multiple Active Result Sets (MARS) in the Entity Framework application's connection string.

So this is normal behavior of MARS - where multiple queries will share a "parent connection" (and thus share a session), interleaving the queries submitted to it by the application.

The most common reason cited for turning on MARS with EF is to help facilitate lazy loading - which is probably why all of the queries look similar (it'll be pulling data in by ID as needed).

As far as performance problems related to this, it's hard to know without seeing the queries and code.  With the MARS and EF stuff, I'd be tempted to guess that it's the N+1 query problem, which I've discussed on my blog.  The solution is to change the .NET code so that it's not executing a query for each row of an already-retrieved resultset.


SQL Server - Slow Database for no apparent reason

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands.
If these things don't help, check how the databases are set up. Is it possible that the data is identical, but server configurations are different, and, for example, there is a much lower threshold on available memory for the production version.
As you said Indexes are not fragmented, Kindly check for the one with values above 30 % as FRAG value for pages greater than 1000.

You can proceed point 3 with Updating stats by running command: EXEC sp_updatestats; on database with slowness issue.


The problem is clearly stated: the first replica set member is bound to localhost.

Since with this name the other replica set members could not connect to this instance, you can only have other replica set members on the same host (but a different port for obvious reasons).

Chances are that the MongoDB instances at DO are bound to localhost by default. You need to bind them to a different IP address using the net.bindIp configuration option. Please keep in mind that this makes you MongoDB instance accessible to everybody being able to connect to the chosen port. You should enable authentication if a MongoDB instance is bound to a different IP than localhost.


mysql assign different partition for specific DB

You should try this:
How can I move a MySQL database to another drive?

  Shutdown mysql.
  Move all the files in your current data directory to
  the new location (check out the location in step 3 - datadir
  parameter).
  Locate my.ini file (it is in the mysql installation
  directory). Change datadir parameter value to point to the new
  location.
  Start mysql.


Actual Row Size vs My Estimated Row Size

Michael pointed you to the source (Paul Randal's "Anatomy of a Page" and "Anatomy of a Record") in an above comment, but to summarise:

SQL Server data is stored in 8KB pages, each of which contains a 96-byte header, and some room at the end of the record for the slot array (which tells the engine the logical order of rows in the page).
Because you use a clustered index, the b-tree structure must also be taken into account, per Dan's comment.

All this considered, we can make the following observation:

All things being equal, at 12 bytes per row, we would be able to fit 674 rows in a single 8KB data page. That works out to be 741,840 pages in total, for 500 million rows. No doubt, this is where your 6GB figure comes from, or more specifically, 6,077,153,280 bytes.

However, there's an overhead per row, as you discovered. Summarising from Paul's Anatomy of a Record:

Two bytes of record metadata (record type) = 2
Two bytes pointing forward in the record to the null bitmap = 2
Fixed length portion for the data itself = 12
Null Bitmap: Two bytes for count of columns in the record = 2
Null Bitmap: Variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not = 2 bits

That's 2 + 2 + 12 + 2 + 1. Yes, it works out to 7 bytes as you calculated, but it could easily be more if there are more columns in the table.

Assuming then a length of 19 bytes per row, you can fit 426 rows in a page. That works out to 1,173,709 pages, or 9,615,024,128 bytes.

If we have a b-tree structure with 500,000,000 rows, your 1,173,709 pages will be at the leaf level. You'll need roughly 2,500 pages for the intermediate level (I don't know the exact length of the intermediate index record off the top of my head, so I guessed 16 bytes), which is an additional 200MB, give or take, and you may have two intermediate levels between root and leaf level.

With this in mind, based on my terrible estimates for the b-tree structure, that would take you to around the 10GB range you see for your data.

(All credit to Dan Guzman and Michael Green for their comments, and of course Paul Randal for his blog post.)


Repacking RDS database with pg_repack isn't online

We found a similar issue. The issue appears to be that log tables created in the repack schema don't have the correct permissions. Our solution was to use ALTER DEFAULT PRIVALEGES to fix this:

CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;


Designing a database where records can reference different foreign keys

When you are trying to relate a table row to rows in different tables, depending on the circumstances, it is nearly always a case of type/subtype aka class/subclass aka generalization/specialization.  The second of these terms is widely used in object modeling, while the third one is widely used in ER modeling.

In your case, the three subclasses would be PersonNames, PetNames, and Lists.  I'm not sure what to call the superclass.  I also wonder whether your fundamental entities are PersonNames or Persons, and ditto for Pets.

If you deside to model these items as a superclass/subclass case, then I'm going to recommend two design patterns or techniques to you.  They are class table inheritance, and shared primary key.  There are tags for these two techniques over in StackOverflow, and there is a tag for Subtypes here in the dba area.  The questions tagged, and the info under the tags may help you to understand these techniques as they apply to your case.


Finding interval overlap between two tables

The simplest way to work on this is to follow Martin Fowler's (and many others as well) technique: http://c2.com/cgi/wiki?TestIfDateRangesOverlap

Note: SQL Server syntax

create table Layers (layer_top_depth int, layer_bottom_depth int, layer_lith_type int);
create table Samples (sample_top_depth int, sample_bottom_depth int, sample_lith_type int);
go
insert into layers  values(4, 7, 1) ;
insert into layers  values(6, 8, 2);
insert into layers  values(5, 6, 3);
insert into Samples values(6, 7, 1);
insert into Samples values(6, 7, 2);
insert into Samples values(3, 8, 3);
go
SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type,
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth <= s.sample_bottom_depth AND
        S.sample_top_depth <= L.layer_bottom_depth);
go
drop table Layers;
drop table samples;

If the samples at boundary layer should not be reported, replace the comparison of <= with < as mentioned by ypercubeᵀᴹ and is shown below:

SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type,
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth < s.sample_bottom_depth AND
        S.sample_top_depth < L.layer_bottom_depth);

The lyth_type is just riding along in this since you did not mention how to use it.


What are the implications of changing the SQL Server Service Logon Account?

If you are talking about the immediate impact, then the service will need to be restarted in order for that change to take effect.  As per TechNet:

  When you change the service startup account for the Database Engine and SQL Server Agent, the SQL Server service (the Database Engine) must be restarted for the change to take effect. For more information, see Additional Considerations for Changing Service Accounts below. When the service is restarted, all databases associated with that instance of SQL Server will be unavailable until the service successfully restarts. If you have to change the service startup account of SQL Server or SQL Server Agent, make sure that you do so during regularly scheduled maintenance or when the databases can be taken offline without interrupting daily operations.

Using a domain account as the SQL Server service account is considered best practice, so you are making a step in the right direction.

For further reading, the TechNet reference on Change the Service Startup Account for SQL Server.


This will give you correct estimates only if you are sure that the server has not been restarted during the time frame you are intending to check..

--to check when server is last restarted

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

--to see stats for  table

SELECT TableName = object_name(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

References:https://stackoverflow.com/questions/1575676/how-to-find-out-sql-server-tables-read-write-statistics


Log Shipping -Monitor Server Down - Copying happening but not restoring on secondary

As per error it  does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Log shipping is out of synchronization beyond the backup threshold.

Instead, this message might indicate below problem:

  The backup job is not running. Possible causes for this include the following: the SQL Server Agent service on the primary server instance is not running, the job is disabled, or the job's schedule has been changed.

The following list includes some of these reasons:

The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages
When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. This seems to be the cause of LS going out of sync in you're case.
The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.

  It was preventing the secondary instance from applying the backups even though I later explicitly ran the restore job. When the monitor server came back on - everything sorted itself out.

When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_primary table is not updated with the current values before the alert message job runs. To update the monitor tables with the latest data for the primary database, sp_refresh_log_shipping_monitor on the primary server instance ran and sync you're LS status.


Drawing an entity-relationship diagram for a movie sales business domain

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.


Where are SYS Audit Entries in Windows Event Log?

Audit files get written into the folder audit_file_dest points to.

select value from v$parameter where name='audit_file_dest';


Tsql help - doing a large update from CSV file to a table in ssms

You can use the Import Flat File feature in SSMS to import a CSV file to a Table in your database. (Import Data is another feature you can use too but it's a little more bulky and older.) Either of these features can be found be right clicking on the database you want to import into, clicking Tasks, then clicking Import Flat File (or Import Data if you prefer). Then it's just a matter of following the Import Wizard which is only a few steps.
If the update you're making to your main table are the same values for all 83,000 rows that are being updated, then you can just update them with a similar hard-coded update query to this, after you import your CSV file:
UPDATE M
SET M.enabled = 0, -- Hard-coded value for all rows
    M.agreed = 'yes' -- Hard-coded value for all rows
FROM MainTable AS M
INNER JOIN CsvImportedTable AS C
    ON M.id = C.id

Otherwise if you have different values you need to set for different rows within your list of 83,000 IDs then you'll need to pre-define those values in new columns in the CSV file first (using Excel for example) before you import it. Then after you import it, your CSV table will have those columns as well so you can do a more dynamic update like this:
UPDATE M
SET M.enabled = C.enabledNewValue, -- Values you entered in your CSV
    M.agreed = C.agreedNewValue -- Values you entered in your CSV
FROM MainTable AS M
INNER JOIN CsvImportedTable AS C
    ON M.id = C.id

Additional resources on importing CSVs and data into a database via SSMS:

SQLShack - Importing and Working with CSV Files in SQL Server

Microsoft Books Online on the Import Data feature

MSSQLTips - Simple way to import data into SQL Server


SQL query for next occurrence of specific date

Overly complex but doesn't need using any other tables:

SELECT ADD_MONTHS(d, 6-MOD(1+MONTHS_BETWEEN(d, TRUNC(d, 'year')),6)) AS next_dt
FROM
  ( SELECT TRUNC(dt, 'month') AS d
    FROM <table>
  ) x ;

Tested at SQL-Fiddle