Create a mysql slave database from master database

Going by your question, I will like to review what I believe you did thus far:

You stopped mysql on the Master
You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
I surmise the binlogs on the Master were copied as well

Look at the Slave's last binlog. From the question, it should be

mydbm1-bin.008524
Filesize 1330529

Believe it or not, you have to do a few things:

1) On the Master, create a replication user like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';

2) Make /var/lib/mysql on the Slave owned by mysql user

chown -R mysql:mysql /var/lib/mysql

3) Make sure Master's server_id is explicitly set in my.cnf

[mysqld]
server_id = 1

4) Make sure Slave's server_id is explicitly set in my.cnf

[mysqld]
server_id = 2

5) Startup mysql on the Slave

service mysql start

6) Setup replication by running this on the Slave

CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

You will see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

If Slave_IO_Running and Slave_SQL_Running are both Yes, CONGRATULATIONS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql


What's causing my postgresql database to freeze?

Turns out the issue was the format of the external HD.  It was in NTFS, which was accessed via Paragon NTFS, and for whatever reason the connection would simply screw up after a few hours.  I reformatted the external HD into Mac OS Extended (Journaled) format and all problems went away.


Designing an Accumulating Snapshot with Repeated Groups, need advise

It's not really clear what is asked here but I think your solution is simplest when you have one row per student.  That allows further aggregating later however you want to do it.

So this is a vote for b.


What should be the rule for excluding an FD from consideration while doing BCNF or any other Normalization?

The algorithm that decomposes a relation schema R to produce the BCNF operates in the following way: at each step, a dependency is searched that violates the Normal Form. If one if found (as A -> B C D E in the example), the schema is decomposed in two subschemas, one with the attributes of A+, and the other one with the attributes of the relation R minus all the attributes determined by A. Then the same steps are applied to all the schemas obtained, while there are other dependencies that violates the BCNF.

So, in the example, the original schema R is decomposed in the following two schemas:

R1 = (A B C D E F G H)  (with key A)

and

R2 = (A I J K L M)  (with key AIL)

In the next step, all the functional dependencies (projected over the subschemas) should be tested to see if they violate the BCNF, first for R1, then for R2.

But obvioulsy in relation R1, (which has key A, for the way in which it has been build), the dependency A -> B C D E does not violate the BCNF (that says that each dependency should have a superkey as determinant), so it is not used anymore to decompose the relation.

So, from this example you can see that the dependency used for the decomposition is not used in the later steps of the algorithm: its determinant is the key of the first of the two new relations produced, and for this reason that particular dependency will not used anymore, since it does violate the BCNF of the schema produced (while other dependencies in the schema could still be used to decompose, like in the example linked).


How do I stop MYSQL trying to load a missing InnoDB table?

I fixed this by creating the missing database and table (with just one field), then dropping both the database and table.


Does loading data to temp table improve performance?

if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

It sometimes is.  It is a common performance optimization to "materialize" or "spool" intermediate results into a temp table, if putting the logic for returning the intermediate results in the main query turns out to be too complex or expensive.

This is not something you should do always.  Just if you discover that putting the logic in a single query is not running acceptably.

A good way to organize this is with Common Table Expressions, where you can have a "pipeline" of query logic.  eg:

with
q1 as ( select ...),
q2 as ( select ...),
q3 as ( select ...),
select ...
from q3;

Then if you want to spool one of the subqueries, it's as easy as

select ...
into #q1
from ...;

with
q1 as ( select * from #q1 ),
q2 as ( select ...),
q3 as ( select ...),
select ...
from q3;

"Lifting" the query logic from the CTE into a temp table load, and referencing the temp table in the CTE.


Is a "Ranking" query's ORDER BY in FROM subquery is preserved?

As the article explains: No, the internal ORDER BY will be recognized by MariaDB's optimizer as redundant and will be "optimized away". If you want a result set returned in a specific order, use ORDER BY - in the most external level of the query.

The solution is to use ORDER BY in the external query. The x subquery (derived table) can be removed and replaced by a simple sakila.address x (that's what the optimizer will recognize and do anyway):

SELECT CASE
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
       END
         `rank`
FROM
    (SELECT @prev := NULL, @a := 0) y,
    sakila.address x                    -- remove the derived table

ORDER BY x.city_id ;                    -- add ORDER BY in the external query

About the second query, yes, I think the ORDER BY x.city_id will be ignored, too. The answer in the linked question is 6 years old and is accurate but it's for MySQL (and the versions available then). MariaDB recent improvements on the optimizer make this kind of subqueries with variables, sometimes to fail because the ORDER BY is "optimized away".


SQL Server 2012 defrag job runs interactively, but not as a job - why?

Ok, I found a resolution by doing the following:

I moved dba_indexDefrag_sp and its associated objects into a 'HouseKeeper' database, rather than putting them in the Master database. (I note Michelle Ufford recommends this, saying: 'It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.')
I created a new 'SQLJobRunner' Windows login, then added it to SQL Server, setting it as db_owner on the dbs I'm defragmenting. I also applied to same to the HouseKeeper db, to give the login full access to that database.
I changed the defrag SQL Agent job to run using the 'SQLJobRunner' login.

and then it worked, which deals with my issue. It is notable that I have the same security setup against my own login and that still doesn't work. Odd... but that isn't really a problem for me now - I don't know why it that doesn't work though.

Note that I'm aware that giving the login db_owner is a bit of a sledgehammer approach, but now that it works I'm able to make the security a bit more fine-grained.

Thanks!


What minimum permissions and/or roles must a SQL Server login possess to be able to grant database db_datareader role to other logins?

If this were a permission then you could use WITH GRANT OPTION, but it's not.

Assuming that you want to allow someone, or anyone in a particular Database-level Role, to perform this operation but not allow them to assign other Roles, then there is no permission or Role for that.

However, you can achieve this fine-grained "permission" via Module Signing. The minimum permission anyone needs is EXECUTE on a Stored Procedure that you write to do nothing more than add a Login or Role to that specific Database Role.

This is similar to what I outlined in the following answer:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

except in your particular case it is even easier since no server-level permissions are needed. Just do the following:

Create a Stored Procedure that has an input parameter for the Principal name, and simply adds that Database Principal to the db_datareader Database-level Role
Create a Certificate in that same DB
Create a User, in that same DB, from that Certificate
Add the Certificate-based User to the db_owner Database-level Role
Sign the Stored Procedure (from Step 1) with that Certificate
Grant any User who should be able to do this EXECUTE permission on that Stored Procedure

For the sake of completeness:

Technically speaking, you could add EXECUTE AS 'dbo' to the CREATE PROCEDURE statement, but that uses Impersonation which has issues of its own, and is less secure than the Module Signing approach I described above.


After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

This is a known issue (not a bug) all the days of Storage Engine technology
Someone asked a similar question in a Percona Forum in November 2014: The table size between InnoDB and MyISAM engines differs in 7 times.
This may have something to do with the granularity of storage between MyISAM and InnoDB.
MyISAM
According to MyISAM Dynamic Data File Layout

Variable length records are contained in "frames". A record can be put in one or more frames, also called the record "parts" or "blocks". The sense of the frames is to allow reusage of the space of deleted records. Starting with an empty data file, records are put in a single frame each, unless a record is bigger than the maximum frame size (16MB - 4). When a record is deleted, its frame is marked deleted. When a record is inserted after this, it reuses the old frame. If the new record is smaller, the frame is split. The unused part is marked deleted. If a new record is bigger than the old frame, the frame is filled with the record as much as fits. The rest is inserted in other old frames, or, if non is available, in a new frame at end of file.

InnoDB
The size of an InnoDB Page is 16K by default : See innodb_page_size
COMPARISON
Since InnoDB stores in a fixed 16K "frame" whereas MyISAM stores in frames that can vary in size up 16777212 (16M - 4), one should always expect InnoDB tables to be larger due to page splits that are required to fit data into fixed 16K "frames".


What is the best way to have full text search where data in database is frequently updated

I presume that update frequency in the worst case, would be a day, in your case. You could try Sphinx for this, as it is really fast and works well with MySql. And you could think of ElasticSearch also if the data is huge.

https://stackoverflow.com/questions/2271600/elasticsearch-sphinx-lucene-solr-xapian-which-fits-for-which-usage


It may help you more if you think about it, not as a WITH statement but as a Common Table Expression (CTE).

That way you can go back to the Microsoft Documents on CTEs and read the following line;

  A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
  statement that references some or all the CTE columns. A CTE can also
  be specified in a CREATE VIEW statement as part of the defining SELECT
  statement of the view.

Although technically you can define a CTE and then not reference it at all but then what would be the point in defining it? e.g.

WITH Missing AS (SELECT 'The Point' AS What)
SELECT * FROM sys.messages;

Going back to your case, you could rewrite it using CASE statements instead of IF statements but again what's the point? You don't need them.

You're query can be re-written to:

DECLARE @device char(3) = 'en3',
        @other_parameter varchar(3) = 'yes';
WITH measurements AS (
                         (SELECT    *
                          FROM  measurement_database1)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database2)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database3)
                     )
SELECT  *
FROM    measurements
WHERE   device LIKE @device
  AND   op LIKE @other_parameter;

So there's no need of IF statements at all!
HTH


Use Older Versions Of SQL Server Databases With 2012?

The version (539) indicated in the error message says that this database is a Sql Server 2000 database and thus is not supported in Sql Server 2012 See here

Probably in your SqlServer 2008R2 this particular database has a compatibilty level set to Sql Server 2000. You could try to change this level going to the properties of the database, Options page  and changing the compatibility level to 2008, then retry to attach the database to Sql Server 2012


Use a Common Table Expression (CTE):

    CREATE PROCEDURE [dbo].[sp_JobSearch]

    @EnteredKeyword nvarchar(200) = '',
    @EnteredLocation nvarchar(200) = '',
    @PageNumber INT = 1,
    @PageSize   INT = 40

    AS
    BEGIN

    WITH CTE AS
    (
      SELECT
        ROW_NUMBER() OVER ( ORDER BY [MasterJobId] ) AS RowNum ,
        MasterJob.Title, MasterJob.CompanyName, MasterJob.ShortDesc,
        MasterJob.Url,MasterJob.PostedTime, MasterJob.Location, JobBoard.JobBoardName
      FROM MasterJob
        LEFT JOIN JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
      WHERE
      (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
      AND( MasterJob.Location LIKE '%' + @EnteredLocation + '%' )
    )
    SELECT
      Title, CompanyName, ShortDesc, Url, PostedTime, Location, JobBoardName
    FROM CTE
    WHERE
      (RowNum > @PageSize * (@PageNumber - 1) )
      AND
      (RowNum <= @PageSize * @PageNumber )
    Order By RowNum

    END


Insert into table which contains nested-table inside (Object in Oracle)

A simple mistake, I forget to call FoodIngredients_type. Sorry for disturbing all again.

insert into Foods values ('F1', 'Food #1', Food_type('FT1', 'Noodle'),
FoodIngredients_table(FoodIngredients_type(Ingredients_type('I1','Ingredient #1', 0, 0), 10),
                      FoodIngredients_type(Ingredients_type('I2','Ingredient #2', 0, 0), 20)),
                                           1000, 0, 0);


I am moving databases from one instance to another

You should use powershell for automation and backup restore for a guranteed way of migrating your databases.

For powershell, use dbatools --> COPY-SQLDATABASE or for migrating entire server with logins, jobs etc use Start-SqlMigration

  by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance

Why not backup restore ? detaching and attaching database files for moving from one server to another is not a recommended approach !

  List all databases not attached to SQL server

You can use Find-DbaOrphanedFile to find out orphaned database files .mdf, .ldf and .ndf files.


How do I run a large script with many inserts without running out of memory?

The maximum batch size for SQL Server 2005 is 65,536 * Network Packet Size (NPS), where NPS is usually 4KB. That works out to 256 MB. That would mean that your insert statements would average 5.8 KB each. That doesn't seem right, but maybe there are extraneous spaces or something unusual in there.

My first suggestion would be to put a "GO" statement after every INSERT statement. This will break your single batch of 45,000 INSERT statements into 45,000 separate batches. This should be easier to digest. Be careful, if one of those inserts fails you may have a hard time finding the culprit. You might want to protect yourself with a transaction. You can add those statements quickly if your editor has a good search-and-replace (that will let you search on and replace return characters  like \r\n) or a macro facility.

The second suggestion is to use a Wizard to import the data straight from Excel. The wizard builds a little SSIS package for you, behind the scenes, and then runs that. It won't have this problem.


How to have a one-to-one relation to either one of two other tables

There are several options:

Combine Paypal and Stripe tables into a single Payment table, with fields to hold either type of payment;
Have both a paypal_id and a stripe_id in the Order table; use a condition on the table to ensure that at least one of them is NULL.
Have a single Payment_ID with a flag to indicate whether it's an id from Paypal or Stripe as you propose.

The first two options allow for the payment ID(s) to be foreign keys to the payment table(s), the third does not.

I would lean towards a single table with all payment data, but that may be a personal preference. Which of the first two options is better would depend on whether there are any common columns between Paypal and Stripe, and just how large the distinct columns are.

UPDATE: joanolo's answer lays out how to actually implement my option 2; take a look!


Traditionally the use-case for Hadoop is when you need to split your data storage over dozens or more of machines, and you're not using a traditional RDBMS solution. When you only have one machine, you're likely to negate any potential gains the use of Hadoop would have provided.

Additionally, 20 columns * 5 million rows is considered by most DBA to be a small database, and aside from index lookups is not worth much in the way of optimization because most DBMS would handle this amount of information quite quickly.

Back to the topic of Hadoop, however, is this: Hadoop is a distributed file system, not an outright database. A potential use (and one I know fairly well) of Hadoop is when you have large sets of binary files, which have a common data format, and you need to run the same operations on each binary file, or you need to find those binary files quickly. In this case, Hadoop is effectively a massive lookup engine for all the files on the DFS. This way you can quickly find the files that you need to work with to run the parallel data analysis. One such group using Hadoop for such a goal is CERN.

I would not encourage you to consider transitioning your data to Hadoop when a traditional RDBMS would work well for your needs.


Contents of transaction log file in more detail

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.

This is also why you can't rollback this INSERT: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.

You can see what I mean directly from inside SQL Server.

Microsoft ships SQL Server with a DMF called sys.fn_dblog that can be used to look inside the transaction log of a given database.

For this simple experiment I'm going to use the AdventureWorks database:

USE AdventureWorks2008;
GO

SELECT TOP 10 *
FROM dbo.Person;
GO

INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;

BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO

SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO

Here I'm doing two inserts: one with and one without an explicit transaction.

In the log file you can see that there's absolutely no difference between the two:

The red one is the INSERT within an autocommit transaction and the blue one is the INSERT with an explicit transaction.

As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.


Auto increment value needs to be changed inside trigger

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly):

CREATE TRIGGER innodb_seqno_trigger
   BEFORE INSERT ON my_table
   FOR EACH ROW
BEGIN

  SELECT MAX(seqno) INTO @newseqno
  FROM my_table
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1);

END;

Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?


Backup on Share NFS fails - Windows Server 2008 R2

Backups over the network are risky as network connectivity is not redundant.  While you can setup your server to have multiple Network Interface Cards (NICs), if one were to fail while sending the stream of data establishing a connection from the secondary NIC would still cause a disruption to the original stream, which in this case, will cause a backup failure.

There are 2 things I can readily suggest you do, as follows:

Backup Locally: Make a local backup first and then use a secondary job such as robocopy, PowerShell, etc. to copy these backups over to your network share.
Stripe your backups: Make use of backup striping.  This will often times speed up the backup process significantly.  I still think you should backup locally, but striping your backup over the network may help you beat whatever network traffic is likely causing your latency issues.  Even Ola's solution supports striping in the event you're using that.

Backing up locally is most important for your TLog backups.  If enough of those fail in a row and your Tlogs fill the drive, you can run into a database halt situation which will be a service outage.  I'm not a fan of outages, nor should you be, which is why I really think you should make a backup locally, then offload a copy of those files to your network share.


Is there a way to rewrite this MySQL query to use window functions, and not use session variables?

Ok, I solved it using danblack's suggestion of a recursive CTE.

with recursive rankedTradesAndActions AS
(
  select t.*, ROW_NUMBER() over (PARTITION by t.transik order by t.ca_date, t.multiply desc) rowNumber
  from tradesAndActions t
), adjustedForCAs(transik, adjustedPrice, adjustedShares, rowNumber) as
(
  select t.transik
    , if(t.multiply = 1, t.originalPrice * t.terms, t.originalPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, t.originalShares / t.terms, t.originalShares) adjustedShares
    , 1
  from rankedTradesAndActions t
  where t.rowNumber = 1
  UNION ALL
  select t.transik
    , if(t.multiply = 1, a.adjustedPrice * t.terms, a.adjustedPrice + t.terms) adjustedPrice
    , if(t.multiply = 1, a.adjustedShares / t.terms, a.adjustedShares) adjustedShares
    , a.rowNumber + 1
  from rankedTradesAndActions t
    inner join adjustedForCAs a on t.transik = a.transik and t.rowNumber = a.rowNumber + 1
  where t.rowNumber > 1
)
select * from adjustedForCAs a order by a.transik, a.rowNumber
;

No session variables used, so mission accomplished as far as I am concerned.

If anyone can come up with another interesting (possibly simpler) way to solve this, please post it. For now I consider this to be the answer, but if someone can come up with a simpler way to do it, I will consider that to be the answer.


How to install SQL Server on a different machine and set it to be a live replica of the first

The two most important parts of your question are SQL Server 2008, and this line:

  it is probably time to migrate from a single r/w node to a r/w node
  _plus a read only node to do a minimum of load balancing.

Unfortunately, in SQL Server 2008, your only two options for a readable secondary server are:

Log shipping - which isn't real time. When it's time to update the data, you have to kick everyone out of the database for the next restore to finish. That either means everyone's getting kicked out of the database all the time, or they're querying old data. The business isn't usually okay with either of those. Or...
Transactional replication - which is fairly straightforward in theory, but can be challenging in practice. When set up incorrectly, it can add a lot of load on the primary server, and you may also have to change your database structures in order to really support replication.

Since you noted that you're not a DBA, here's what I would do in your shoes: beef up the primary server instead. It's easier to accomplish, and it's probably more in your comfort zone.

For advice on where to start with performance improvements, download sp_Blitz (disclaimer: I'm one of the authors of that open source tool) and run this:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1

Copy/paste the output of that into your question text here at Stack, and we can give you advice about where to start for performance improvements. (Feel free to redact lines that your company wouldn't want shown publicly.)


This sort of thing is typically caused by an out-of-memory error. It could be a segfault/bug in MySQL, but this is less likely. Check your syslog for additional clues, there should be something in there, regardless of whether it is an OOM or a segfault.


INNER JOIN between 2 tables vs WHERE on one table performance

The second query. But why are you comparing them?

They may return the same data but they are answering two different questions. The first retrieves all records from doctors where a record exists in clinic with id=1. The second retrieves all records from doctors with clinic_id=1 regardless of whether a record exists in clinic.

In the absence of a foreign key constraint between the two tables, the queries are not comparable.


MariaDB: point in time backup recovery with binary log using position

The point in time recovery actually works, but you forget, that your restoration of the dump also gets written to the binary log and this undoes your point in time recovery.

What you should actually do, is to also note the binary log position before you restore your dump. Then you limit the point in time recovery with --end-position. Your workflow therefore is

create table
insert into table
mysqldump --master-data=2
insert into table
show master status; /* note log file and position */
mysql < out.sql
mysqlbinlog --start-position=368315 --end-position=<note from above> binlog | mysql

Alternatively you can also not write the mysqldump to the binary log. The 6th step would then be

mysql > set session sql_log_bin=0;
mysql > source out.sql

You have to re-setup slaves then of course.


Yes, it is.

Moreover, a correct table composed by two attributes is always in BCNF.

In fact, if we have R(A,B):

Proof

If A->B but B doesn't imply A, A is key => R(A,B) is in BCNF
If B->A but A doesn't imply B, B is key => R(A,B) is in BCNF
If B->A and A->B, keys: {A}, {B}; => R(A,B) is in BCNF


The relations between sessions, DOP and sets of parallel execution servers

That quote is definitely unclear. I interpret "one set" to be one set of operations, which is also called a data flow operation in the context of a parallel plan. One parallel query may have more than one data flow operation, so one session may contain more than one set. I recommend reading Understanding Parallel Execution - Part 1:

The question is often asked: "How parallel?" For example, what actual Parallel Degree was used for the execution of that statement last night? The question in fact is not that simple to answer since it lacks some precision. First of all there is a so called Degree Of Parallelism (DOP) that is determined by the optimizer when generating the execution plan. A recent overview of how this DOP is determined can be found here, on the The Data Warehouse Insider blog, including the latest Automatic Degree Of Parallelism (Auto DOP) feature introduced in Oracle Database 11.2.
Then there is the problem that it is possible that the optimizer generates multiple so-called "Data Flow Operations (DFO)" per execution plan, and that each DFO has its own, possibly different, DOP.
At execution time for each DFO there can be up to two Parallel Slave Sets active at the same time, each Parallel Slave Set consisting of DOP number of Parallel Slaves.
So for a single SQL statement executed in parallel, there is potentially no single answer to the question "How parallel?" because the optimizer might de-compose it into several DFOs, each having its own DOP.


you could enable the MDA tables (little performance overhead) then use first sp_monitor 'statement'. If it is not enough, you could select into the MDA tables to see the statement running in ASE server such as:

print "**** SQL of the 10 statements having the most CPU time used ****"

select top 10 a.KPID,a.BatchID,a.LineNumber,a.CpuTime,a.StartTime,b.SQLText
from master..monSysStatement a, master..monSysSQLText b
where a.KPID = b.KPID
and a.BatchID = b.BatchID
order by a.CpuTime desc

List of MDA tables:
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70002.1550/pdf/asemtpst.pdf


How to create a custom view (using lookup) from aggregated data

To disaggregate
SELECT id,
   json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups

string_to_array converts the CSV string into ARRAY
array_to_json   converts ARRAY into JSON
json_array_elements each element into a separate row

next I just need to aggregate again.


... MySQL ... slows down dramatically as the product number exceeds 60,000.

MySQL does not run slowly.  Trust me; it really, really doesn't.
What does get slower are the queries that you run against the data structures inside MySQL.

I was considering setting up a RAMdisk and using that for the MySQL temp data.

Ah ha!
It's not the size of the database that's giving you trouble.  It's the amount of temporary space being used to process the queries.  That says to me that you have some [poorly written] queries that are in need of some tuning.
Turn on the Slow Query log, see which queries appear in there and then start tuning those.
Often, it's only a handful of poor queries that bring an entire system to its knees.
Since volume/size seems to be an issue, here's some common things to think about:

Indexes.
Do you have [the right] indexes to support your queries?

Using "select *"?  Don't.
Always specify the columns you want explicitly in every query.
Pulling back redundant columns only adds to the network overhead seen by the client.

Using "select distinct" to get rid of "duplicate" rows.  Again, don't.
Fix the join conditions that are introducing the "duplicates".

Joining to a bracketed select statement?  (This might be fixed in newer versions).
In some older versions, joining from a table to a select statement wrapped in brackets caused MySQL to "create" the bracketed result set in temporary space before joining it to the other table.

... running the whole MySQL database in RAM would be fast but unfeasible, I know.

Fast?  Probably not.  You're effectively "conning" the operating system into thinking that a big chunk of memory is actually disk space but, unless you have a lot more memory  besides that, the O/S will probably need to page that memory in and out, to and from disk, which is slow.
Worse still, as soon as the machine was powered off, the RAMDisk and everything on it (i.e. "the whole MySQL database") would evaporate, like the morning dew.
Possibly not the most resilient operating model.  :-)


How to combine date and time to datetime in SQL Server?

The time part is the fractional part and the date part the integer part, so if you date is genuinely just a date (no time part at all) and your time is genuinely just a time (no date part at all) you can simply combine them by adding them together.

For example:

SELECT CAST(40597 AS DATETIME), CAST(0.5 AS DATETIME)
SELECT CAST(40597 AS DATETIME) + CAST(0.5 AS DATETIME)

The top SELECT shows what values represent today and 12 noon, and the second line adds them to make a single value representing today at 12 noon.


Remove string after second specific character occures from left

You can use the third parameter of charindex() that is used to specify where in the string the search will start.

declare @S varchar(20) = '45465@6464@654';
select left(@S, charindex('@', @S, charindex('@', @S)+1)-1);

Result

45465@6464


Disk usage not decreased after the full and log backup in SQL Server

Full backups have nothing to do with the size of the database files.  Log backups only peripherally affect log file size.  If your database is in full recovery mode then the data in the log file will remain until a log backup is taken.  This means that as more data is written to the log file additional space will be required.

Think of it as a bucket.  Over time water is added to the bucket.  Every time you take a picture of the bucket (log backup) you can pour the water out.  Regardless of how full the bucket is it remains the same size.

Now let's say you have a large transaction (requiring a lot of water) or you haven't been taking your log backups frequently enough.  SQL wants to add water to the bucket but it's now full.  You now need a bigger bucket.  So SQL goes out and gets a bigger bucket.  Now your bucket is larger.  You take a log backup and the bucket is emptied.  The bucket doesn't shrink back to it's original size.  Your regular usage may now only fill a small portion of the bucket but that again doesn't affect the size of the bucket.

Note I'm way over simplifying. There are other things that can cause your bucket not to empty.


MySQL Server is Restarting automatically after some minutes

I suspect your max_allowed_packet and the presence of a large TEXT/BLOB field.

I answered two questions in the past that involved mysql crashing because of the max_allowed_packet being too small.

MySQL server has gone away obstructing import of large dumps
InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

I recommend raising your max_allowed_packet to the max value, which is 1GB

If you have TEXT/BLOB fields, you may have entered a TEXT/BLOB field that is too big to pass through a MySQL Packet or through the InnoDB Log Files. You may need to resize your innodb log files to the max value, which is 2047M. Here is what to do:

STEP 01) Add this to /etc/my.cnf

[mysqld]
max_allowed_packet=1G
innodb_log_file_size=2047M

NOTE: In Google Cloud, you can't enter 1G, you must use the value 1073741824

STEP 02) service mysql stop

STEP 03) rm -f /var/lib/mysql/ib_logfile[01]

STEP 04) service mysql start


Optimising a query with two aggregate fields

What database?  Does your database support row_number().

And you changed the query.  In the first max(scanTime), max(scanId) are not necessarily on the same row.  Those are two independent max.

The scanTime of the row with the max(scanID) is different and what you got with the second.  It is faster as scanID is indexed (PK) and scanTime is not. Since scanID is the PK you don't need:

AND q.regionId = sq.regionId AND q.typeId=sq.typeId

If your database supports row_number()

select * from
(
SELECT scanTime, mscanId, typeId, regionId
     , row_number over (partition by typeId, regionId order by mscanId desc) rn
FROM scanTimes
) tt
where tt.rn = 1


Cloning a SQL Server Virtual Machine with TDE

Yes, cloning or copying a VM should not make a database using TDE unreadable or unavailable on the new cloned VM.
If you check the Move a TDE Protected Database to Another SQL Server doc, which by the way is not specifically what you're trying to do, but lists the restrictions that could cause a TDE database unable to be decrypted on a different server, you'll see that none of the Limitations and Restrictions rules are broken when copying or cloning a VM.
Every "piece" needed to decrypt the database is going to be copied along with the VM and everything shall be at the same place within the new VM.


This is the right query:

WITH counts AS (
    SELECT device_model, device_type, COUNT(id) AS cnt, rank() OVER (PARTITION BY device_type ORDER BY COUNT(id) DESC) AS model_rank
    FROM devices
    GROUP BY device_model, device_type
)
SELECT device_model, device_type, cnt, model_rank
FROM counts
WHERE model_rank <= 5
ORDER BY device_type, model_rank

It handles models that has the same rank, displaying more than 5 rows per device type just in case.

In order to test it, create a table and fill it with data:

CREATE TABLE devices (
    id serial,
    device_model text,
    device_type integer
);

INSERT INTO devices (device_model, device_type)
    VALUES
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('iphone6', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone2', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0)
;

The output of the initial query is the following:

 device_model | device_type | cnt | model_rank
--------------+-------------+-----+------------
 ipad         |           0 |   5 |          1
 kindle       |           0 |   4 |          2
 iphone1      |           1 |   6 |          1
 samsung      |           1 |   6 |          1
 iphone3      |           1 |   3 |          3
 iphone4      |           1 |   2 |          4
 iphone6      |           1 |   1 |          5
 iphone2      |           1 |   1 |          5
(8 rows)

Feel free to ask questions about the logic behind this solution.


You should purge the archived logs with RMAN:

RMAN> delete archivelog <delete conditions here>;
RMAN> crosscheck archivelog all;

or alternatively all of them:

RMAN>delete archivelog all;

You can check your recovery area usage like this:

SQL>select * from v$flash_recovery_area_usage;

If you encountered that problem though, you will very likely encounter it again. Purging the archived logs is a quick fix.

Whoever maintains the server needs to verify:

That archived logs backups are performed and are successful
That obsolete archived logs are deleted
That obsolete backups are deleted.
If the backups are performed regularly and obsolete archived logs deleted, maybe the database usage has increased and filling the fast recovery area faster than the archived logs get backed up. Increasing the db_recovery_file_dest_size parameter to a higher value to fit the business needs may be required.


ORA-39165 Schema was not found in export

When I run into issues like this, I like to simplify the command as much as possible.  I would try putting all of the parameters directly in the command line and removing the parameters that are not required.  And I would use a dba user to do it.  Something like this.

expdp {dbausername}/{dbapassword} SCHEMAS=OFDAT DUMPFILE=dev_ofdat_2-1-3-0_afterbackfill.dmp


You find all available metrics in

V$SQL_MONITOR - disk rate, requests and consistent gets per execution per session - here you also found if parallel execution is degraded
V$SQL_PLAN_MONITOR - disk and memory data per session, execution and plan line
V$ACTIVE_SESSION_HISTORY - 1 s sampled data per session, execution and plan line
V$PROCESS - current counters for process memory usage (join to session using PADDR = ADDR)

If you are familiar with execution monitor in Enterprise manager or other Oracle monitoring tools, they use exactly same views. You just need to join the data from them and model into an output you want.

There one more view useful for finding out parallel skew - V$PQ_TQSTAT.


TSQL Extract first word as First name. If only one word or null, set to ‘’

This is a little different than what your stated goals were, but it might be what you want.

with cte as (
  select Prefix
  from (values ('Dr '),('Mr '),('Miss ')) v (Prefix)
)
select
    prefix = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then left(s.Name,charindex(' ',s.Name+' '))
        end
  , first_name = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then stuff(s.Name,1,charindex(' ',s.Name+' '),'')
        else  nullif(left(s.Name,charindex(' ',s.Name+' ')),'')
        end
from (
  select name = replace(Name,'.',' ')
  from t
    ) s

rextester demo: http://rextester.com/BQQO51384

returns:

+--------+------------+
| prefix | first_name |
+--------+------------+
| NULL   | Abhijit    |
| NULL   | Ravi       |
| Dr     | Rajiv      |
| Dr     | sharma     |
| Mr     | Raju       |
| Miss   | Anjali     |
+--------+------------+


What does normalization say about storing data from objects that use a decorator pattern?

Normalization says nothing about the Decorator Pattern, as normalization is much older.

Check out Table Inheritance though, which I think is more along the lines of what you're talking about.

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html


What MySQL storage engine should I choose?

My recommendations are to start as follows:

Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5
Go for InnoDB - comes as the default for the higher MySQL versions
Configure InnoDB as follows:

file_per_table - creates an data file for each table instead of putting all tables in a single data files
innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory

Table column characteristics:

use the smallest column size needed for the data
use numeric foreign keys
index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again


How can extract the table schema from just the .frm file?

I have answered questions like this before

Dec 21, 2011 : Table compression in InnoDB?
Feb 03, 2012 : How to recover MySQL table structure from FRM files
Apr 23, 2012 : restore table from .frm and .ibd file?

I have referred to a blog from Chris Calendar on connecting .ibd files back to the data dictionary.

If you do not have the .ibd along with .frm, chances are MySQL was running with innodb_file_per_table off. Please refer to the Feb 03, 2012 post on that. If this is Linux, make sure you run

cd /var/lib/mysql
chown -R mysql:mysql *

before starting mysql.

Give it a Try !!!

SUPPLEMENTARY INFO

Percona's mysqlperformanceblog.com has information on this from over 4 years ago
MySQL Utility Team has the tool mysqlfrm.

At the bottom of that last blog it says:

  Ok, I'm Hooked! Where Can I Get It?

  The .frm reader utility is part of the new release-1.3.0 Alpha
  available as a separate download at
  http://dev.mysql.com/downloads/tools/utilities/. Simply choose the
  platform repository or source repository and download it.

  We welcome your comments and hope that this utility will help expand
  your diagnosis and recovery toolkit.

UPDATE 2013-06-11 14:05 EDT

I have good news. I installed the Windows version of the MySQL Utilities. I tested the mysqlfrm program from the command line. I tested getting the .frm info from the user.frm in the mysql schema. Here is what I got:

C:\MySQL_5.6.10\data\mysql>mysqlfrm --server=root:rootpassword@localhost mysql:user.frm --port=3307
# Source on localhost: ... connected.
# Starting the spawned server on port 3307 ... done.
# Reading .frm files
#
# Reading the user.frm file.
#
# CREATE statement for user.frm:
#

CREATE TABLE `mysql`.`user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

#...done.

C:\MySQL_5.6.10\data\mysql>

It really works. The database is running on my desktop computer using port 3306. I had to give 3307 as a port to run a spawned process.

I could move the .frm from a Linux box to the Windows machine and do the same.

Give it a Try !!!


Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable

0> "and set 5500 was impossible due to the error - 5500 seemed busy"

This means that the port 5500 is already "in use". Well, I bet it is not in use but Oracle thinks that. There are a lot of ports reserved in /etc/services.

Open the file and change the following lines:

fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1

Change them into:

#fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
#fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1

Retry to set the port with SQL> EXEC dbms_xdb_config.sethttpport(5500);.

EDIT 1:

Your listener shows that it's listening on port 5500 for https requests. So maybe you already did what I just posted.
Could you please share the output of the following command?

service iptables status

EDIT 2:

I just activated EM Express on one of my 12c databases. Here is what I did:

OS: Oracle Linux 6.5
DB: Oracle Database 12.1.0.2.0
service iptables status --> OFF

Action log:

$ sqlplus / as sysdba
SQL> SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;

GETHTTPSPORT
------------
        5500

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPPORT
-----------
          0

I'm going to set https port to 5501 and overwrite the http port with 5500.

SQL> EXEC DBMS_XDB_CONFIG.sethttpport(5500);
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_XDB_CONFIG.sethttpsport(5501);
PL/SQL procedure successfully completed.

Afterwards the listener shows that I's listening on these ports (before the 5500 record was there already but it was not working)

$ lsnrctl status | grep PORT
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=***)(PORT=5501))(Security=(my_wallet_directory=/opt/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***)(PORT=5500))(Presentation=HTTP)(Session=RAW))

I did the test in a browser:

Browse to http://***:5500/em --> login screen appears
Browse to https://***:5500/em --> login screen appears

Note the tailing "/em" is important.


Do I need to add more memory to my SQL Server 2008 R2 instance?

What are the other things that I should be looking at?

First few remarks

  This is a SQL Server 2008 R2 Standard Edition running on a x64 machine
  with 40 CPUs and 64 GB of RAM.

Why is your max memory only at 24000MB when you have 64GB of phyisical memory? Are there other instances / services / ... running on it?

If the answer to this is no, then you could set max memory to about 56GB = 57344 MB.

As a side note, sql server 2008 R2 is out of extended support. It would be best to be looking towards migrating to a newer version.

Which objects are consuming buffer pool memory

Since your PLE has always been pretty low and has gotten even lower over time, you could look into what exactly is using this BP memory at different points during the day.

First of all look into which databases consume the most:

-- get memory usage per database
SELECT
[DatabaseName] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
COUNT_BIG(*) [Pages in Buffer],
COUNT_BIG(*)/128 [Buffer Size in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY [Pages in Buffer] DESC;

Afterwards you can dig deeper on a database level to see which objects are in memory and how many pages these objects hold in memory.

-- get memory usage for objects in a database

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc;

If the more granular query reports less, you would have to remove and bd.page_type in ('data_page', 'index_page') to include LOB data pages.

Next steps

You should put this information together with queries running on your system, you might be able to optimize certain queries / add indexes so less data is needed in memory.

If you want to go even further, ROW / PAGE compression could be looked at if you have the CPU power to do so.

Compressed data remains compressed in memory.

Also note that if you are on sql server 2008 R2 standard edition, you could only go to 64GB of max memory. Starting from sql server 2014 this goes up to 128GB of max memory. This is also another reason to migrate.

  If the above is sufficient, do I have to recommend adding more memory
  (maybe make it 50 GB) because PLE shows a degradation over time (while
  Total IO Wait Time shows going up)?

Solely based on PLE, which is not the only factor to look at, I would say that your pages get flushed too frequently out of memory. Either optimize your queries or increase your memory.

  Target Server Memory is equal to the maximum memory set. Is it an
  indication that the instance can utilise more memory if it is
  configured with?

  Target Server Memory (KB) is the amount of memory that SQL Server is
  willing (potential) to allocate to the buffer pool under its current
  load. Total Server Memory (KB) is what SQL currently has allocated.

Source

It has allocated all possible memory to the buffer pool as allowed by max memory, it will not go over this amount for buffer pool allocations.

  So, how do I know if my SQL Server needs more memory? or maybe it
  doesn't

Again, looking at what objects are in memory and if the queries responsible for it can be optimized would be a good start.

Additionally I would increase either max memory if you have the room to do so (if you in fact have 64GB of memory on the machine) or increase the memory on the machine.

Again remember that there is a limit of 64GB of max memory for sql server 2008 R2 standard edition and that this is increased to 128GB starting from sql server 2014 standard edition.

There is no limit on enterprise edition.


Script not emailing from Maintenance Plan/SSIS

Currently, your code will not send any email if any fields used to create the subject or body are NULL.

I'd add a case to deal with that such as:

IF (@EmailBody IS NOT NULL)
BEGIN
    SET @Subject = @Subject +
    CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
         WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
         WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
         WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
         ELSE 'Unknown'
         END;

    EXEC sp_send_dbmail
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END
ELSE /* catch any issues with NULL for @EmailBody */
BEGIN
    SET @EmailBody = 'nothing to report';
    SET @Subject = 'alert!';
    EXEC sp_send_dbmail
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END


Storage strategy for combination of all table items

Here's a way to count how many of each pair of items is upvoted:

CREATE TABLE pairs (
    item1 ...,
    item2 ...,
    ct INT
    PRIMARY KEY(item1, item2) )

INSERT INTO pairs
     (item1, item2, ct)
     VALUES
     (?, ?, 1)   -- value list
ON DUPLICATE KEY UPDATE
     ct = ct + 1;

It has a flaw of saying the pair (x,y) is different than (y,x).  If that matters, there are two workarounds:

On input:  Change "column list" to

    (LEAST(?,?), GREATEST(?,?), 1)

Or, on output: combine the counts for matching pairs.

(I don't know why you mentioned "users".)


Help needed to tidy up deprecated (and mal-functioning) SQL code

First attempt at 1. (re-writing the Oracle (+) Operators as LEFT OUTER JOIN's); the subquery-part still isn't good though I guess; this is very-much draft-stage...

SELECT
    *
FROM
    a1

LEFT JOIN ( select * from b3 where col4 in
        (select col5 from b6 where col7 = '007')
  ) c ON a1.col8 = c.col9

LEFT JOIN a2 ON c.col4 = a2.col5

INNER JOIN a3 ON a2.bla = a3.bla

LEFT JOIN a7 ON a2.id = a7.id


Why can't I create a MySQL user even if i have 'all priviliges'?

You need to grant CREATE USER using similar syntax:

grant create user on *.* to 'admin'@'%';


MySQL repair deleted data - Any chance of recovery?

myisamchck did its best job, so that may be the maximum what you can get out of mytable.MYD.

The structure of MyISAM record is pretty simple, integers are stored in packed format, strings are prefixed with their length(1 or 2 bytes). So you can write a C program with pre-defined pattern that would try to fetch records of out the MYD file. This approached worked for me  on wp_comments table.

Two major problems here:

A record may be fragmented. Recollecting the fragmented record in a corrupt MYD seems to be extremely difficult if not impossible problem.
Table structure should allow to identify a good record in a stream of bytes.

P.S.

InnoDB is recommended storage engine for a reason.


Difference between database dump (export) and database backup?

I'll answer this at a high level for you. The two backup methods work at different levels. An RMAN backup is a physical backup and a Data Pump backup is a logical backup.

A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data.

An RMAN backup is a point-in-time backup of an entire database (for the purposes of this question). It backs up the physical blocks that make up the database (data files, control file, archive logs etc) and, in combination with the database archive logs, allows point in time recovery options.

In the event of a complete database loss an RMAN backup can be used to restore the complete database. However, a data dump taken using expdp would need a new database creating before the data could be imported using impdp.

For a hobbyist Oracle XE database (that may not be in archivelog mode), backups using expdp will probably suffice.

The Oracle Documentation covers this far better than I could ever explain. Oracle® Database Concepts - Backup and Recovery


This is kind of complicated, but you could try this.

Create a duplicate table to store the duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 2

Create a nonDuplicate table to store the non duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 1

Create a duplicateValue table to store the modified values of the duplicates. You will need to modify the case statement below to work with Access, before using this query in an insert statement. Here are some links that should help you.

How to add row number in Access

Access case statements

--Modify the 2nd instance of the duplicate field
--If the row number is Odd, then the value is the same
--If the row number is Even, then 200 is added to the value
SELECT
  --This is how you would do it with SQL Server
  --You will need to figure out how to modify this code to work with Access
  CASE WHEN ROW_NUMBER() OVER (ORDER BY field-1 ASC) % 2 <> 0
  THEN field-1 ELSE field-1 + 200 END AS 'field-1'
  --THE ABOVE CASE STATEMENT NEEDS TO BE MODIFIED FOR ACCESS
  ,field-2
FROM yourtable
    JOIN duplicateTable ON yourtable.field-1 = duplicateTable.field-1

Create a nonDuplicateValue table to store the non duplicate values. Use this query in your insert statement:

--Get the values of field 1 and field 2
--from non duplicate rows
SELECT field-1,field-2
FROM yourtable
    JOIN nonDuplicateTable ON yourtable.field-1 = nonDuplicateTable.field-1

Query to union the results together and to use for the insert into the final table

SELECT field-1,field-2
FROM duplicateValues

UNION

SELECT field-1,field-2
FROM nonDuplicateValues


SqlPackage importing of very large bacpacs to Azure

Is this SQL Server on a VM or Azure SQL Database / Azure SQL Managed Instance ?
Probably one of the last 2 as you could just use a backup to restore to SQL on a VM, If you find the import runs to slow just scale up your machine until the import is completed


Multi-AZ failover - replica still read only after failover

One thing I need to answer this question. Which endpoint you are using in the application Instance or Cluster?

In Aurora there are two types of endpoints.
1. Instance endpoint
2. Cluster Endpoint

Instance Endpoint:

This endpoint will always points to that particular instance whether it is read or writer. It never care about the role, it'll point that particular instance.

Clusrer Endpoint:

Actually it has 2 endpoints. One is for Writer and another one os for reader. This is the endpoint we should use in the application. Because unlike Normal RDS, Aurora's failover and MultiAZ is different. So these cluster endpoints will point bases on the role.

Writer endpoint will always point to the writer role instance, after failover this endpoint will go to the new writer. Like the same for Read endpoint.

Answers for your three questions

What other information can I provide to clarify my configuration or issue?

  Check which endpoint you are using.

Any clues on why this replica instance stayed read only?

  If you are using instance endpoint, after the failover the application
  still refers the same instance for write. Thats why its failing.

If this is the expected behavior of read_only:TrueIfReplica, where else should I look to fix my configuration?

  Nothing, just again check which endpoint you are using.


Repair Mysql table structure from existing frm file

I have never done it, but this blog post might be useful. You should be able to just create the .MYI and .MYD files (with the correct mysql file permissions) to recover the table structure. But as you said, you won't have the data recovered.


MS SQL - CLR Stored Procedure runs as deleted user

Assuming that the SQLCLR method truly is trying to log into SQL Server as a non-existing login ("user" is database-level), then that should only be due to using a regular / external connection (instead of the internal "context connection") and specifying a SQL Server login and password (instead of using a trusted connection / integrated security).
{ still, having the exact error message is always helpful :-) }

In terms of keeping the same process, you have two main options:

Re-compile the code with a new login and/or password:

There is a slight chance that the source code is in sys.assembly_files (it would be a row where file_id <> 1), but that practice was done (at least by default) in earlier versions of Visual Studio / SSDT and you don't really see it that much anymore.
Assuming that the source code isn't in that system view, you would need to export the actual assembly (same view, but this time you do want WHERE [file_id] = 1) and then you can use a disassembler to get the source code.

A much easier path should be to just re-add that deleted login. Is there a reason not to? If you aren't sure what password is being used, the two options noted above for getting the source code would also be how you find the password.


ORA : 12519 No appropriate Service Handler

http://docs.oracle.com/cd/E18283_01/network.112/e10836/concepts.htm

sales=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

It looks like you haven't put the SID in your TNSNAMES.ORA.


Difference between mongod and mongodb upstart jobs

I suspect you had MongoDB 2.4 installed previously.

In MongoDB 2.4 the service was called mongodb and used /etc/mongodb.conf (ref: Install MongoDB 2.4 on Ubuntu).

With MongoDB 2.6 there was an attempt to have more standard package names across Linux distributions, so the service was renamed to mongod (to reflect the actual daemon being started) and the config was changed to mongod.conf (ref: Install MongoDB 2.6 on Ubuntu).


The MSDN documentattion page about ALTER TABLE explains these:

ALTER TABLE: modify the table's structure
(and some of the possible actions/modifications are):

CHECK CONSTRAINT ..: enable the constraint
NOCHECK CONSTRAINT ..: disable the constraint
There are also additional, optional steps to do while creating/enabling/disabling a constraint:

WITH CHECK: check the constraint as well
WITH NOCHECK: do not check the constraint

In their words:

| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
    { ALL | constraint_name [ ,...n ] }

  ...

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

  If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

  The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  ...

  { CHECK | NOCHECK } CONSTRAINT
  Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

Test in dbfiddle:

CREATE TABLE a (aid INT PRIMARY KEY);

  GO

✓

INSERT INTO a (aid)
VALUES (1), (2), (3) ;

  GO

3 rows affected

CREATE TABLE b
( aid INT,
  bid INT PRIMARY KEY,
  CONSTRAINT [My_FORIEGN_KEY]
    FOREIGN KEY (aid) REFERENCES a (aid)
) ;

  GO

✓

INSERT INTO b (aid, bid)
VALUES
  (1, 11),
  (1, 12),
  (2, 21),
  (3, 31) ;

  GO

4 rows affected

INSERT INTO b (aid, bid)
VALUES
  (6, 61),
  (6, 62) ;

  GO

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31

ALTER TABLE b NOCHECK CONSTRAINT [My_FORIEGN_KEY];   --disable

  GO

✓

INSERT INTO b (aid, bid)
VALUES
  (4, 41),
  (4, 42) ;

  GO

2 rows affected

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

ALTER TABLE b WITH NOCHECK CHECK CONSTRAINT [My_FORIEGN_KEY];
-- enable constraint without checking existing data

  GO

✓

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

INSERT INTO b (aid, bid)
VALUES
  (6, 61),
  (6, 62) ;

  GO

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM b ;

  GO

aid | bid
--: | --:
  1 |  11
  1 |  12
  2 |  21
  3 |  31
  4 |  41
  4 |  42

ALTER TABLE b WITH CHECK CHECK CONSTRAINT [My_FORIEGN_KEY];
-- check existing data and enable constraint

  GO

Msg 547 Level 16 State 0 Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.


"Unrecognized configuration parameter: qreplace_function" trying to backup a database

You have not used  PostgreSQL. You are using  PostgresPlus created by EnterpriseDB.

The parameter qreplace_funtion is mentioned in EnterpriseDB's document (page 21). I guess this is a typical privilege problem.

OR, if you use PostgreSQL but run PostgrePlus's pg_dump, try original PostgreSQL's pg_dump.


Copy views from one database to other database

select DB and Right Click

then this window will appear click Next

then select DB object which you want to generate script

select file path if you want to change

then Click Next 2 times and check path which you selected and pickup that script from there and run it


How can I insert without changing the identity seed?

You could put the old seed into a variable and then reseed the table after the insert.

DECLARE @Seed INT = IDENT_CURRENT( 'tableB' )

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFF

DBCC CHECKIDENT ('tableB', RESEED, @Seed)

This is assuming that no other inserts are going to take place while you do your insert.


Query list of logins that have logged in the last X days on SQL Server

As mentioned by @Tibor, unless you set up logging or trace you cannot retrieve that information natively. You do not need to use trigger.

Using SQL Server Management Studio

Configure Login Auditing (SQL Server Management Studio)
Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. I did describe in this blog post how to read from the error log.

Using Extended Event Trace

Audit Login Event Class

The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.

Steve Jones explained in detail in his blog post: Tracking Logins with Extended Events


What is an efficient storage method to store Graph Data from GraphX (Spark)?

TitanDB stores graphs in adjacency list format which means that a graph is stored as a collection of vertices with their adjacency list. The adjacency list of a vertex contains all of the vertex’s incident edges (and properties).

They used VertexID is the partition key, PropertyKeyID or EdgeID as clustering key and property value or edge properties as normal column.

Their Cassandra Table schema :

CREATE TABLE edgestore (
    key blob,
    column1 blob,
    value blob,
    PRIMARY KEY (key, column1)
) WITH COMPACT STORAGE AND CLUSTERING ORDER BY (column1 ASC);

Where

key     => VertexID
column1 => PropertyKeyID or DIR+OtherID+EdgeID
value   => PropertyValue or Pair of EdgePropertyKeyID and EdgePropertyValue


Migrating from 32 bit using pg_basebackup

pg_basebackup is not the right approach. It won't work, because the 32-bit and 64-bit data directories are not compatible.

You will have to dump and reload using pg_dump and pg_restore, plus a pg_dumpall --globals-only restored via psql for user accounts etc.


You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or  use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression.  Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table.

Since you have a limited number of questions (7), you can easily hard-code the query:

select
  id,
  name,
  max(case when question = 1 then Answer end) Q1_Answer,
  max(case when question = 1 then Reason end) Q1_Reason,
  max(case when question = 2 then Answer end) Q2_Answer,
  max(case when question = 2 then Reason end) Q2_Reason,
  max(case when question = 3 then Answer end) Q3_Answer,
  max(case when question = 3 then Reason end) Q3_Reason,
  max(case when question = 4 then Answer end) Q4_Answer,
  max(case when question = 4 then Reason end) Q4_Reason,
  max(case when question = 5 then Answer end) Q5_Answer,
  max(case when question = 5 then Reason end) Q5_Reason,
  max(case when question = 6 then Answer end) Q6_Answer,
  max(case when question = 6 then Reason end) Q6_Reason,
  max(case when question = 7 then Answer end) Q7_Answer,
  max(case when question = 7 then Reason end) Q7_Reason
from tablename
group by id, name;

See SQL Fiddle with Demo


Putting back online SQL AlwaysOn servers

Here is how I see it.

Patch & upgrade the secondary/passive node first

after this do a failover and check if everything is still working like it should.(this can mean waiting a week) If this is the case, then you can proceed with patching the other node.

If this is not the case you can fallback to your old node and revert the changes done on the other server, and get them back into sync.

I would definitely not patch both nodes at the same moment

IMPORTANT: make sure you can take the hit on your logspace because during the downtime of your secondary node you will not have any log truncation

Also make sure you have your Quorum settings configured properly, otherwise you might have some issues on your cluster level!


How to enable missing Tempspc column in Oracle explain plan

This TempSpc column will only be shown if Your query will need any Temporary space like if you are sorting a big chunk of records and viewing it.

If query don't need any temporary space, this column will not be shown.
This varies from explain plan of one query to another, so you will not find it in every explain plan result.

As per this Oracle Page;

  Temporary space, in bytes, used by the operation as estimated by the
  query optimizer's approach. For statements that use the rule-based
  approach, or for operations that do not use any temporary space, this
  column is null.


What kind of time should I be hoping to achieve inserting 300k records into a SQLite DB?

Things to improve performance of multiple inserts in SQLITE:

wrap in a transaction

You already done it. This is the first and most important thing.

Don't use big sentences

You can split the insert statement in serveral

INSERT INTO Point (X, Y, PolygonId)
  VALUES (1,1,1) ,(1,1,2), (1,1,3), ...100 or less... ;
INSERT INTO Point (X, Y, PolygonId)
  VALUES (2,1,1) ,(2,1,2), (2,1,3), ...100 or less...;
... 10.000 or more statements

Use prepared statements

If the API allows that you can use prepared queries:

INSERT INTO Point (X, Y, PolygonId) values ($1, $2, $3);

and send the data separately


Change pg_dump directory output to table name instead of object number

There is no built in way to do this.  You would have to change the code to do what you want. Or you could script something to rename the files based on the output of pg_restore -l .

Whatever you come up with wouldn't be very general. For example, what if <table name>.dat.gz is not a legal file name on your file system?


As a side note, rman is very fast when backing up to solid state disks.  If you can get your hands on some put them in a raid you'll probably back up and restore in probably less than 30 minutes.

In either event, here's how to move the db to another host with hardly any downtime.  Here's the overview:

1. do a full rman backup.

2. copy everything in the flash recovery area to the new host

3. go through the process of restoring the db to the new host.
   a. There are a handful of steps to do this.  It's not too hard.
   b. First the full backup is restored.
   c. Then each archive log is applied to the new instance.
   d. When all of the archive logs are have been applied, the db is opened.

Keep the production db up and running while you are doing steps 1,2,3a,3b. This may take a few hours if you have slow disks/network.

Once the level 0 backup is restored, apply the archive logs sitting in the flash recovery area which you copied in step 2.

At this point, all you need to do put the production db in read only mode and then copy and apply the last set of archive logs which were created after you copied the FRA.  This should be very fast.

Then open the db and change clients so they now use the new db.


"[ftdc] serverStatus was very slow" - MongoDB 4.0.4

That means, your server is overload. It can't even reply to the serverStatus command without delay.
Some people solved that by adding more hardware to their server. But, I think first of all you should check your queries and indexes, maybe you can optimize them.
Check out your queries:
db.currentOp()


Slave failing to replication in MariaDB 10

max_connections=100000 -- Unreasonable, and dangerous.  Even 1000 may be unnecessarily high for the number of simultaneous connections.

10G for the buffer pool -- I assume you have at least 15GB of RAM?

It sounds like the data on the two servers, at least for redacct, is not consistent.  How did you initially populate the Slave?


PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction

You could always double check your problem by issuing a command such as

/usr/bin/pgsql/bin/createdb test

You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.

But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. You can do this by either

rebooting the server or
reloading the .conf file or
change on the client side

From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.

I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).

For a system reboot,

pgctl restart

To reload, you could also try these commands (from here - or check the documentation page given above).

Option 1: From the command-line shell

su - postgres /path/to/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

You can verify that the setting has "taken" from the table pg_settings. Also of interest is this page.

For the default_transaction_read_only parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters - reboot, reload or client.

test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting
-------------------------------+---------
 default_transaction_read_only | on
(1 row)

test=#
test=# `SET default_transaction_read_only = OFF;`
SET
test=#
test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting
-------------------------------+---------
 default_transaction_read_only | off
(1 row)

test=#
test=# SET default_transaction_read_only = ON;
SET


Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships

This looks like the party relationship model (see https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories).

I recently went through something similar, combining different content types under a single table with subtables for each unique content type.

I'm not sure about the entity framework, but in the new model you have, you'll have an easier way to link content to other content by having a central place storing the ids/primary keys. It's a common design pattern, so I don't think there's anything "inherently stupid" about it.

I don't think you needed to worry about the first one, but the second one is obviously better thought out.


Memory Pressure in SQL Server - Enable LPIM or not?

is there a memory pressure in this server.

With the limited information, I cannot tell with surety. Need more parameters and output from perfmon to tell about memory pressure. But yes, from the query output you posted for the time 2018-10-23 11:22:30:457 there was indeed low memory for some process(s), not the SQL Server system as whole.

  the RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL server.?

Look at the output it says RESOURCE_MEMPHYSICAL_LOW has values of 2 from IndicatorProcess which means certain processe(s) which were running were indeed facing memory pressure, if IndicatorSystem would have had value of 2 it would have been system wide memory pressure. Also note the memory pressure is coming from low physical(RAM) not low virtual memory(VAS). So you can see just taking a value for one piece of time it is difficult to say there is continuous memory pressure. I suggest you read Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server to understand what the values Indicatorsystem and IndicatorProcess means

  should i enable LPIM to fix this issue.?

LPIM is not going to fix the issue it is only going to mask the issue and would come as workaround. You need to find what is causing resource monitor to flag low memory notification, it could be possible that some process running is causing it. Now plan cache getting frequently cleared is something lot of people have reported from SQL Server 2016 onward and it may be due to query store, are you using one ?. Query store used to be culprit behind plan cache clearing but since you are on latest SP I doubt this factor.

For seeing memory pressure for cache we can also use DMV sys.dm_os_memory_cache_clock_hands

SELECT  *
FROM sys.dm_os_memory_cache_clock_hands
WHERE rounds_count > 0

If rounds_count is increasing this means there was pressure which forced clocks to sweep the cache more frequently and remove the entries.

EDIT:

  I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.Does this huge memory grant(one query i checked ,memory grant is ~7GB) result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.

Yes quite likely, I have seen you raised specific thread for this question and I would only add that before you go for anything make sure you have stats updated and indexes defragmented. An outdated stats may lead to bad plan which can end up asking more memory and hence semaphore waits.

Below are list of threads talking about similar issue

Stackexchange thread about plan cache getting cleared frequently.
MSDN thread about plan cache getting cleared.


This looks like number of 100 nanosecond ticks since 1601-01-01 (FILETIME)

So based on that the following works (125911584000000000 is the number of these ticks between 1601 and 2000. This is subtracted first to avoid overflowing an int)

It also looks like the time returned is UTC so you will need to convert to your local time zone (which I have made an assumption about).

DECLARE @value    BIGINT = 132327053636850000,
        @datetime DATETIME2

SELECT @datetime = DATEADD(MILLISECOND,
                           @value%10000000 / 10000,
                             DATEADD(SECOND,
                             ( @value - 125911584000000000 ) / 10000000,
                             CAST('2000-01-01' AS DATETIME2)))

SELECT CONVERT(DATETIME2(3),
                 SWITCHOFFSET(@datetime,
                 DATEPART(TZOFFSET, @datetime AT TIME ZONE 'GMT Standard Time')))

Returns

2020-04-30 08:29:23.685


Changing string in all objects in all databases on one server

sp_msforeachdb is undocumented. There are viable alternatives to it.

Try changing:

SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as
 ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

to:

SET @SQL='USE [?]; select Db_Name(), o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

As originally written, the @SQL's sys.objects will reference the database being used for sp_msforeachdb's execution and thus the statement's 2 object_id values may or may not join.


How can I drop relations in a PostgreSQL database without foreign key check?

I've got it.

For a single table I can do:

DROP TABLE "tableName" CASCADE

For all tables inside a schema, for example "public", I can do:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Source:

http://www.postgresql.org/docs/current/static/sql-droptable.html

https://stackoverflow.com/a/13823560/937110

Or, as suggested by a_horse_with_no_name:

DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ]

source:
http://postgresql.org/docs/current/static/sql-drop-owned.html


Is it possible to setup MySQL replication through ProxySQL?

This sounds really bad.  Replication and Galera clustering will (I think) get confused if a slave/node is suddenly talking to a different master/node.

Sure, use HAProxy or proxysql for connecting clients to slaves/nodes.  And have rerouting happen.  That's their purpose.


How to make stored procedures more readable without harming performance

As you said, start with easy to read consistent formatting.  Here is a general idea of how I like to do it, although it's by no reason the only good way to format.

DECLARE @Test int

IF @Test = 1
BEGIN
    -- Indent within blocks
    PRINT @Test
    SELECT Field1, Field2, -- If the list get's long
            Field3, Field4 -- split into multiple lines
    FROM Table1
    -- Line up JOIN statement and FROM statement
    JOIN Table2
        -- Indent ON condition with one condition per line
        ON Table1.Field1 = Table2.Field2
        AND Table1.Field2 = Table2.Field2
    WHERE Field1 = @Test
      AND Field2 = 'ABC'
END

On top of that you will notice that I've got comments all over the place.  Including in the middle of the SELECT statement and at the end of some lines.  Over commenting can make it more difficult to read also so you need to find a happy balance.  Another commenting trick is to include "block comments" like this:

/************************************************
********* Title for section 1 *******************
************************************************/

These help you divide up your code into obvious sections.  Say "Variable Declarations and Initialization", "Loading Temporary Tables", "Generating Dynamic SQL", etc.

For subqueries I tend to find CTEs and even nested CTEs easier to read although in some cases a mix is best.

SELECT *
FROM (
    SELECT *
    FROM (
            SELECT *
            FROM Table1
        ) Level2
    ) Level1

vs

WITH
    Level2 AS (
        SELECT *
        FROM Table1 ),
    Level1 AS (
        SELECT *
        FROM Level2)
SELECT *
FROM Level1

And a little SSMS trick while you are viewing your SPs.  Pay attention to those little - signs next to the code.

Click on one of them and it will collapse the block below it.  Doing this you can get a similar effect to separating things out as stored procedure calls.

Of course your mileage will vary with any of these and in some cases a monster SP is just a monster.


As was stated in the comments by @yercube, you have a case sensitivity issue.

SOLUTION #1

Create the first table with the name Staff

CREATE TABLE Staff(
   StaffID INT AUTO_INCREMENT PRIMARY KEY,
   FirstName VARCHAR(15) NOT NULL,
   LastName VARCHAR(15) NOT NULL,
   Address VARCHAR(30) ,
   City VARCHAR(15) ,
   State VARCHAR(3) ,
   Position VARCHAR(20) NOT NULL,
   Wage INT NOT NULL
);

Then, create the other two tables. It should work.

SOLUTION #2 (based on @ypercube's suggestion)

Keep the table staff, but create the other two like this:

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
 );

CREATE TABLE staffphone (
    StaffID INT NOT NULL,
    PhoneNumber VARCHAR(15) NOT NULL,
    PRIMARY KEY (StaffID, PhoneNumber) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
);

Give it a Try !!!


I gave it a quick look, but the query itself looks fine AFAIK tell. What I would suggest is adding a couple of indexes:

CREATE UNIQUE INDEX X1 ON product_categories (categoryId, productId);
CREATE UNIQUE INDEX X2 ON product_categories (productId, categoryId);

CREATE UNIQUE INDEX X4 ON product_highlights (productId, highlightId);

I would also recommend the following change for the tables:

CREATE TABLE products
( productId int NOT NULL,  -- keep the name the same throughout the model

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_categories
( productId` int NOT NULL
, categoryId int NOT NULL
,   primary key (productId, categoryId) -- Skip index X2 if this is added

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_highlights
( productId int NOT NULL
, highlightId int NOT NULL
,    primary key (productId, highlightId) -- Skip index X4 if this is added


One way to avoid your issue is to make it a business rule that the userid and friendid values be inserted in a specific order. You could add a row constraint (either by adding INSERT/UPDATE triggers, or by applying your changes via a Stored Procedure) to ensure that userid<friendid so it would be okay to insert (userid, friendid) VALUES (1, 2) but any attempt to add (userid, friendid) VALUES (2, 1) would fail.

Example using TRIGGER:

DELIMITER $$
DROP TRIGGER IF EXISTS `userfriends_INSERT` $$
CREATE TRIGGER `userfriends_INSERT`
BEFORE INSERT ON `userfriends`
FOR EACH ROW
BEGIN
    IF new.`userid` >= new.`friendid` THEN
        SIGNAL SQLSTATE '45000';  # unhandled user-defined exception
    END IF;
END $$
DELIMITER ;

Test:

mysql> SHOW VARIABLES LIKE "version";
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| version       | 5.5.29-0ubuntu0.12.04.1 |
+---------------+-------------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE userfriends;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| userid   | int(11) | NO   | PRI | NULL    |       |
| friendid | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = "userfriends";
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

mysql> SELECT trigger_name FROM information_schema.triggers;
+--------------------+
| trigger_name       |
+--------------------+
| userfriends_INSERT |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM userfriends;
Empty set (0.00 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'


By default MySQL doesnot log general SQL statements. Configure MySQL to do so. The MySQL documentation on logging (MySQL :: MySQL 5.1 Reference Manual :: 5.2 MySQL Server Logs) should help you do that.