Show table records based on user condition in SQL Server

One way to approach this is to start with the Enrollment table. Using the ROW_NUMBER ranking function, you can assign row numbers in groups of SemesterID:

SELECT
  *,
  RN = ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC)
FROM
  dbo.Enrollment
;

For your example you will get output like this:

Id  StudentID  SemesterID  RN
--  ---------  ----------  --
1   1          1           1
2   2          2           1
3   3          2           2
4   4          1           2
5   5          2           3
6   6          2           4
7   7          1           3
8   8          2           5
9   9          1           4
10  10         1           5
11  11         2           6
12  12         1           6

Now subtract one from RN and then divide the result by the required number of students. If the number of students is 3, then you will find that the aforementioned calculation turns a sequence of

1, 2, 3, 4, 5, 6, ...

into

0, 0, 0, 1, 1, 1, ...

That is to say, you will get a sequence consisting of groups of repeating numbers, three per group (because we divided by three). You can use that result as the primary sorting criterion.

Choose SemesterID as your second criterion and Id or StudentID as the third, so now the query will look like this:

SELECT
  *,
  GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
FROM
  dbo.Enrollment
ORDER BY
  GroupID ASC,
  SemesterID ASC,
  Id ASC
;

And this will be its output based on the sample provided:

Id  StudentID  SemesterID  GroupID
--  ---------  ----------  -------
1   1          1           0
4   4          1           0
7   7          1           0
2   2          2           0
3   3          2           0
5   5          2           0
9   9          1           1
10  10         1           1
12  12         1           1
6   6          2           1
8   8          2           1
11  11         2           1

You can see that the order of rows already matches the expected output, you just need to replace StudentID and SemesterID with their descriptions and get rid of the GroupID. Declare the above query as either a derived table or a CTE and then join tables Student and Semester to it. For my answer I chose the CTE option, and this is what I got:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student AS st ON rd.StudentID = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

Output:

Id  StudentName Semester
--  ----------- --------
1   A           First
4   D           First
7   G           First
2   B           Second
3   C           Second
5   E           Second
9   I           First
10  J           First
12  L           First
6   F           Second
8   H           Second
11  K           Second

As you can see, the query we have so far works perfectly for the provided example: the results now match your expected output precisely. But the query is not finished yet: the filter on semester is missing.

Assuming that semesters will be provided as a comma-separated list of names/descriptions, what you can do is turn that list into a row set, with one name/description per row. You can then match that row set against the Semester table to include just the selected semesters. For the purpose of this answer I am assuming that you are using SQL Server 2016 or later version, so that the built-in SPLIT_STRING function can be used to help implement the semester filter:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT('First,Second', ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

In an earlier version of SQL Server you will have to provide your own method of turning a string list into a row set of substrings. Thankfully, solutions abound on the Internet, including the many questions and answers dedicated to this problem both here and on Stack Overflow. Aaron Bertrand discusses various methods in his blog article, Split strings the right way – or the next best way, which can be a good starting point.

As for our query, there remains one last step to take: parametrisation. Both the number of students and the list of selected semesters were hard-coded in the above queries. The final solution will need to use parameter references instead of the hard-coded values, something like this:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / @NumberOfStudents
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT(@SemesterList, ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

A live demo of this solution is available at db<>fiddle.uk.

Disclosure: this answer borrows partly from the now deleted answer by Josh Darnell.


What could be the reason for disallowing a sub query in the values clause?

If you want to use a SELECT statement where only a single value is allowed you need to put that SELECT statement into brackets:

insert into dates (d)
values
( (select sysdate from dual) )

That could be extended for multiple columns:

insert into dates
(
  id,
  d,
  other_column
)
values
(
  some_sequence.nextval,
  (select sysdate from dual),
  (select max(some_col) from other_table)
)

You just need to make sure that the SELECT returns exactly one row and exactly one column


Spiky COMMIT results in performance slowdowns with Postgres 8.4

It's hard to tell what exactly your graphs say.  But commit is designed to be slow.  By default, Postgres waits for changes to be written to disk before it lets commit return.

You can let commit complete before changes are written to disk with this option:

synchronous_commit = off

That should certainly help to spread the disk I/O required for commit over more time.  The downside is that a server outage can cause completed transactions to be lost.


I have three conjectures on this situation. One or more of them may apply...
CONJECTURE #1
There may be other operations that happen apart from fetching data back-and-forth across the network. Here are a couple of of my old posts where I actually discuss Bytes_received and Bytes_sent

Apr 18, 2012 : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
Apr 13, 2014 : Determining replication traffic

In these posts, I talk about how MySQL Replication creates traffic in its framework. How ?
There are two threads involved for MySQL Replication

I/O Thread : TCP/IP Connection back to its Master read binlog events and record the binlog events in the Slave's Relay Logs.
SQL Thread : DB Connection that reads binlog events from the Relay Log and executes the SQL. If log-slave-updates and log-bin are enabled in the Slave, the binlog event whose SQL was executed in this thread is recorded in the Slave's binary logs.

If the WordPress DB Server is in Master/Slave Replication topology, note the effect

If DB Server is a Master, any Slave's I/O thread connected will make a Master's Bytes_sent increase.
If DB Server is a Slave, the I/O thread will increase Bytes_received reading binlog events from the Master and the SQL Thread will increase Bytes_received reading binlog events from the Relay Logs.

Based on the numbers you are showing, I would suspect the DB Server would be a Master and it replicates any log changes to one of more Slaves.
CONJECTURE #2
If the DB Server is not in MySQL Replication Topology at all, I would then suspect some exporting operations, such as mysqldump to another Server or even locally. Why ? A DB Conneciton is still require by mysqldump. Bytes being written to a dump have to be recorded in Bytes_sent.
CONJECTURE #3
If there are neither MySQL Replication nor regular mysqldump backups in place, then I can only see one thing running up Bytes_sent so high and leaving Bytes_received so low: DB Monitoring.
Someone asked this in ServerFault back in August 2011 : 1 billion mysql queries in 24 days? Can something be wrong?. In that post, I explained that running SHOW GLOBAL STATUS often can run up the status counts on Queries and Questions. Retrieving status values frequently can also contribute to increasing Bytes_sent.
UPDATE 2014-11-28 15:10 EST
If you control everything in Vultr and you know for sure that the connections are all from localhost, the problem may not be WordPress itself. It would have to be your either remote connections or just very frequent traffic spikes.
At this point, WordPress would be out of my wheelhouse. Try out the info in this post : How can I improve site/page performance of WordPress websites?


Oracle 11g database structural change doesn't autobackup controlfile/spfile

I found the answer as I think:

"Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database."

After about(not sure how much) 10-15 minutes I run the following command from the RMAN:

list backup;

and now it shows me the result. So it waited for a time and then backed up the controlfile.


MongoDB - Unable to change mongodb storage engine to WiredTiger

Based on the warning message included in your description, you are already using the WiredTiger storage engine but your dbPath is using the ext file system (in theory ext4, but the Linux filesystem magic number doesn't distinguish ext2/ext3/ext4). Note: WiredTiger is the default storage engine for MongoDB 3.2+, so there is no need to specify it in your configuration file.

This warning was added for the MongoDB 3.4 production release per SERVER-22606: Startup warning if ext4 is used with WiredTiger. There are some known performance issues with ext4 (in particular, periodic stalls) so this warning is meant to proactively ensure administrators are alerted to potentially problematic configurations. Filesystem and other production caveats are also included in the Production Notes in the MongoDB manual.

To suppress this warning you could either:

Host your dbPath on a supported filesystem other than ext4 (XFS is recommended)
Start your mongo shell with the --quiet parameter (which might suppress other interesting startup info / warnings)

You can certainly choose to ignore startup warningd (particularly for a development environment), but should consider using XFS with WiredTiger if this is a production environment.

You may want to watch/upvote SERVER-19790: Provide mechanism to clear/acknowledge startup warnings, which could allow startup warnings to be ack'd so they do not appear on every new mongo session.


SQL Agent jobs in Basic Availability Group setup

Sadly, I'm going to argue that you're just 'scratching the surface' in terms of some of the complexity associated with SQL Server Agent Jobs and some of the complexities you face with Availability Groups (AGs). And, I say "sadly" because Microsoft has actually done a VERY poor job of addressing these concerns to the point where there's virtually no guidance from them.

So, for example - assume you get this working as expected and then you've got a job set to run at the same time on both servers and it'll automatically run on the current/correct replica (depending upon which is the primary) - great/perfect. Until someone comes in and disables the job or changes the schedule - but only makes that change on ONE server (not both) - and then there's a failover. Now what? (Or, in other words, if it's important that a job run (or NOT run) then it's important that whatever changes are made to the job should be synchronized between one server and the next (in a 2-node AG) and, yet, it's incredibly easy to let jobs like this get out of sync - even when there's only 1 'DBA' managing the servers.

As such, I spewed out a set of blog posts on all of this a while back:
http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-27-options-and-concerns-more-advanced-dep

The blog posts are VERY MUCH a 'stream of consciousness' sort of affair - and there are a couple of mis-steps in the posts and things are LESS insightful than I'd like. Still, by skimming through those posts, you can gain some great insights (i think) on some of the problems to expect and OPTIONS for working around those (with various pros/cons configured).

Eventually, I'll rewrite that set of posts into something more concise (and more 'wise' - i.e., that benefits from the number of AGs I've worked with since those posts - and all of the changes I've made). That said, here's a rough overview of what I do now to address these details:

I fully use the UDFs that determine whether the replica is the principal or not (either based on db-name or ag-name).
Rather than use the 'job state table' I outlined (as that's too clunky), jobs are either a) run as 'server-level' jobs (backups, server event logs/history maintenance/etc.) or b) assigned a 'disabled category' - meaning the job is/should be disabled (and then my code checks to make sure 'disabled' jobs are disabled and so on) or c) set to a SQL Server Agent Job category where the NAME of the category = the name of the AG they're in - so that when failover happens, code traps said failover and responds on the new primary by enabling all jobs of categoryname = 'ag-name' and doing the opposite on the secondary (if it's still up).
Still running synchronization checks (vs server-level details and jobs) every x minutes - to catch situations where something changes on one server - but not the other. (I don't bother letting CODE try and sync the differences - instead, it just reports that something is different.)


Generally, refreshing the MV immediately only seems reasonable if write access to underlying tables is a rare event. A statement-level trigger is better than a row-level trigger, but may still prove too much for big tables. I would consider a solution that polls the database every n minutes checking for updates. You could have a trigger write to a table with a single row.
Or you could use LISTEN / NOTIFY.

As long as you want to propagate every change (almost) immediately, you may be better off with row-level triggers on insert / update / delete writing to a hand-knit "materialized view" (a table), like @trygvis suggested.

Since pgbouncer keeps sessions open, there may be locking issues with uncommitted transactions slowing the process down, but I am merely speculating here. You could run tests in a copy of your database without pgbouncer. Or check for zombi transactions by looking up pg_stat_activity.


Whats the best and fast way to import a huge DBF file to a Postgres in Windows?

We faced a similar problem but our solution involved some coding. I don't know if that's an option for you.

We followed the usual phases:

We read the DBF metadata and created an analogous SQL 'create table' statement. The mapping is fairly straightforward except that we checked the number of digits for numeric types and coerced them to ints or longs (int8) as appropriate. Our data had a lot of foreign keys and it was better to treat them as int8 than numeric(15,0).
We read the DBF metadata again and created a (java) prepared statement that would insert a single line of data.
We "called" that prepared statement for each row in the DBF file. This makes sure we don't have problems with inadvertent SQL injection, e.g., names like O'Reilly. In reality we used batches of 400 rows (ps.addBatch() and ps.executeBatch() in java) and used an explicit begin/commit block of 20 insert statements. Both dramatically improve performance.
Finally we added any primary keys.

We could have gone through an intermediate file and used COPY but it was easier (and safer) for us to do it programmatically.

We also wrote our own DBF reader. It only took a day and we were able to work around some numeric overflow bugs that we found in third-party libraries. You can find enough information on Google that this isn't too hard.

One thing to be careful about is that "dbf" is vague. All dBase files have the same overall structure but there are tweaks from different publishers and versions. They're documented but you'll want to determine what version your file uses and then code to it.


06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent
DETAIL:  privileges for database mydb 24 objects in database mydb

Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL will see some dependencies with the specified pg_catalog.pg_shdepend.dbid, but will not be able to query the system catalog of that database to determine which objects they are. So, postgresql does hint only database names in error details for other databases. (this is also a reason why PostgreSQL has no drop user ... cascade - we can't modify or even read objects description from another database)
But if you connect to the database specified in the details and try to delete a user, you will get a much more detailed description:
postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database m2
postgres=# \connect m2
You are now connected to database "m2" as user "postgres".
m2=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table foo
privileges for table foofk
owner of table test

In this case user1 is owner for one table (REASSIGN OWNED will solve this easily) and some priviilegies on another two tables (REASSIGN OWNED will not touch this).
Certain commands are required for different objects (revoke for permissions, alter default privileges for default privileges and so on), unfortunately, there is no omnipotent ring to manage all dependencies even in one database.


Your software is most likely not using the default postgres database (that is a database that is normally reserved only to store the list of databases, users, and other globals).

From psql, run \l to list the available databases. Your database will be something other than postgres, template0, or template1. Try connecting to one that is not one of the above (\c dbname) and then run \d again to list tables.


Classical MySQL replication replication should work:

Assumptions made:

your cloud instance is read only.

The master won't be able to trigger the replication however by default a slave will retry every 60 seconds (configurable CHANGE MASTER .. MASTER_CONNECT_RETRY=).

Ensure your expire_binlog_days is large on the ship based master. Account for the space required carefully on your master and cloud instance.

I'd recommend the ship based master connect via a VPN and the slave connects to the VPN address.

For multiple vessels, you can use use a multisource replication available in MySQL-5.7+ or MariaDB-10.0+. Keep each vessel in its own database for simplicity.

Choose a binlog_format that minimises your traffic volume.


redesign database around selecting rows by time range

Look into PostgresQL with Timescale Extension:

https://docs.timescale.com/v1.1/using-timescaledb


Constraint to only allow unique value in a group

CHECK constraints cannot use subqueries, so you have to use a trigger:

CREATE FUNCTION check_one_value_per_group() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT * FROM my_table
               WHERE "group" = NEW."group"
                 AND value <> NEW.value;
              ) THEN
        RAISE EXCEPTION 'different values in group %', NEW."group";
    END IF;
    RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER one_value_per_group
AFTER INSERT OR UPDATE OF "group", value ON my_table
FOR EACH ROW
EXECUTE FUNCTION check_one_value_per_group();


Oracle say: This is most probably due to table corruption. See the error number is 0, which indicates success, not actual error. So, this means that read from file was successful and OS returned 0, but actual read data length is different from expected data length. This is unlikely to be a problem on MySQL, but most likely to be a problem on OS including file system.

InnoDB writes data to data file per page. So, size of data file is always multiple of page size. In general, writes against a file is atomic. So, data file will never be non-multiple of page size, if OS works properly. If it's not multiple of page size, InnoDB will read incomplete page at the end of data file, then it results in this error.

Recommended solutions are:
Take following actions to mend or prevent this problem:

Run fsck and check if the file system is flawless.
Replace suspicious disk.
Ensure that the filesystem is transactional. (such as NTFS and ext4)
Check if journaling setting of filesystem is fine.
Change block size of filesystem to same as InnoDB page size (16KB by default)
Upgrade OS to the latest version.


MySQL - Large Table best practices/indexes

As a direct answer, what your query needs is a composite index on (shift, timestamp): ALTER TABLE historical_data ADD INDEX shift_ts(shift, timestamp)

However, you have a lot of indexes. and some are redundant.

tag_id: has low cardinality. Drop it if you don't have "WHERE tag_id=x" queries
shift, timestamp: combine them in 1 index (This is useful for your query)
hd_multicolumn: drop
value: if you don't query it, drop it
hd_multicolumn2: drop it
[IMPORTANT] If your table is an InnoDB table, add an auto increment field, and make it a primary key


List all database containing table and value

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

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

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;


Document Store & Relational DB Hybrid. Exists? Possible?

SQL Server has JSON support though it seems to be a variation on it's XML support.

PostGres has had JSON support since 9.2.
Teradata has had it since 2014.

This makes them hybrids.  Obviously PostGres is the open-source one.

It really depends on what you need from JSON support.  If it is to return the document on a key then that has always been possible without explicit JSON support.

If you want to index certain parts of the JSON document then that does require specific JSON support.  There is the question of how sparse the attribute is that you want to index.  If it is present in the vast majority of cases (or even a mandatory attribute) then I would break it out of the JSON and have it as an explicit attribute in a hybrid store.

If by parallelize or die you mean support a distributed dataset or die then I'm not sure that I agree.  For OLTP work you would have to be in a Times top 100 company to approach the limits of the traditional RDBMS.  By definition most companies just don't generate as much useful data as they would like to think.

Companies like Facebook, Twitter and NetFlix are dealing with data on an order of magnitude far beyond what most people will see.

For high end web analytics work then yes, you might want a NOSQL product as a collector.  Cassandra is useful in that respect, plus it has tunable consistency.

The gotchas in distributed systems is Brewers CAP theorem.  You can have any two of Consistency, Availability or Partition tolerance.  It's a bit more blurred than that but the 2 of 3 rule is generally true.

There's the challenge of how you handle referential integrity and in some cases how do you honour primary key constraints.  Your data might be splashed across many servers so honouring a foreign key concept might require a lookup between one server and another.  Even if this were a feature it would seriously impact performance.

If people want to interact with an API that only talks JSON then I see no problem with that providing there is an underlying DAL that obfuscates whether a data attribute is present in a JSON document or explicit attribute in a hybrid design.


Insert records from one table to other with default serial

You could list out the non-gid columns in your SELECT:

edb=# create table foo (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# create table foo_new (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# insert into foo values (default, generate_series(1,100)::text,'mytext');
INSERT 0 100
edb=# insert into foo_new values (default, generate_series(1,100)::text,'yourtext');
INSERT 0 100
edb=# insert into foo_new select * from foo where id = 1;
ERROR:  duplicate key value violates unique constraint "foo_new_pkey"
DETAIL:  Key (id)=(1) already exists.
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo where id = 1);
INSERT 0 1
edb=# select * from foo_new where col2 = 'mytext';
 id  | col1 |  col2
-----+------+--------
 101 | 1    | mytext
(1 row)

You could also try creating a temporary table, then drop the gid column, and then copy that table into the new table:

edb=# create temporary table foo_temp AS SELECT * FROM foo;
SELECT 100
edb=# alter table foo_temp drop column id;
ALTER TABLE
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo);
INSERT 0 100
edb=# select count(*), col2 FROM foo_new group by col2;
 count |   col2
-------+----------
   100 | yourtext
   100 | mytext
(2 rows)

If you don't want to list out all the columns by hand, because you have too many, I suppose you can use a script to create a listing of all the non-gid columns, and then copy/paste into your INSERT statement:

edb=# select array_to_string(ARRAY(SELECT column_name::text
                                     FROM information_schema.columns
                                    WHERE table_name = 'foo'
                                      AND column_name::text NOT IN ('id')),',');
 array_to_string
-----------------
 col1,col2
(1 row)


What is the difference between int(8) and int(5) in mysql?

See 12.2 Numeric Types (MySQL Reference Manual)

Essentially what you listed is the max value for INT and not BIGINT.

INT(8) is the equivalent of typing INT with a display width of 8 digits
INT(4) is the equivalent of typing just INT with 4 display lengths.

The range of BIGINT is -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615.


How to get number of locks acquired by my MySQL Query?

As per info linked in comment - it needs some preparing, cannot probably be used automatically:
that is output from

show engine innodb status

You can see it if you do in commandline:

begin;
update employees set store_id = 0 where store_id = 1;

and then in any (even the same) session

show engine innodb status\G

Where you look for section labeled

------------
TRANSACTIONS
------------

and find transaction which the update is part of.

Original answer (still holds for general usage IMHO)
I don't think it is possible directly, but you can get some details about locks from server status:

show session status like 'Innodb_%_lock%';

if you run it before and after query, you can check difference and analyze it.


The log vs. data separation is sound advice, but the devil is always in the details. A write intensive workload that commits frequently requires the log stream to flush to disk as fast as possible. This in turn requires a sequential write pattern, as the log does, undisturbed by any other operation. Hence, isolate the data writes (and reads!) from the log writes.

But the question is: is your workload write sensitive? Is the log commit flush wait the most important performance bottleneck? You may have a tempdb sensitive workload. Or you may have a workload that is not even touching the disk. Lock contention, memory restrictions, latch contention all could be your nemesis, none would be solved by how you separate LDF and MDF. And lets not forget that the modern SQL Server instance usually hosts several databases, implying several separate log streams.

In lack of further evidence, both you and your admin are equally right/wrong. You need to measure, identify your problems, and address them accordingly. Read How to analyse SQL Server performance for general directions how to identify (and address) your bottleneck(s).

Before I go, I recommend you read:

Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 2: sequential inserts)
Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)

Not saying that, w/o proper consideration of the workload and value, you should use SSDs (yes, you should, always...), but just food for thought.


It's hard to say exactly but it looks like something went wrong during the reinitialization process.  Transactions are flowing from Publisher to Distributor but the Subscriber is requiring a new snapshot.  I'd recommend generating a new snapshot and then it will be reapplied by the Distribution Agent.  At that point you should be back in sync.


What are the most relevant similarities between T-SQL and MDX?

General notes

First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data.

I'll start with addressing your question about common mistakes. In my opinion the most common mistake is to try and apply SQL knowledge to MDX. You really need to get into the multidimensional mindset and forget about thinking in relational structures.

For example, you don't query columns and rows but you slice dimensions and measures. See for example my answer here.

Pretty much the only thing in MDX that resembles SQL is the fact that you have a SELECT and a FROM and a WHERE clause but MDX doesn't necessarily return rows and columns but returns "axes", of which you can have more than 2.

Addressing some of your points will probably show you why SQL concepts don't map to MDX concepts:

Pivot and unpivot

You pretty much don't do this in MDX. If you look at the MDX select syntax you'll see an example like this (returning in this case a 2 dimensional result):

SELECT
    [Date].[Calendar].[First8Months2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

Say, you want to pivot that resultset, you just swap the axes around:

SELECT
    [Product].[Category].Children ON COLUMNS,
    [Date].[Calendar].[First8Months2003] ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

Bear in mind that the COLUMNS and ROWS axes are just shorthand names, you could also do:

SELECT
    [Product].[Category].Children ON 0,
    [Date].[Calendar].[First8Months2003] ON 1
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

and add a third axis and a fourth axis if you wish to do so (and if your client supports rendering that), so the result isn't a table but rather a multidimensional cellset.

Obviously, pivot and unpivot wouldn't keep up when you have more than 2 axes in your result set.

Recursive CTE's

Since MDX doesn't return "tables", there is no such thing as table expressions. You can have recursion in your calculated measures if that is something you need.

When you encounter the WITH keyword, it's defining Query-Scoped Calculated Members.

Window functions

Window functions also have no use in MDX, since they are used to calculate things that are already calculated in your cube and calculated measures. The OVER clause is pretty much the dimension members you put on your axes.

Take for example a running total. In SQL you would write something along the lines of

SELECT SUM(internet_sales) OVER (ORDER BY date) AS running_total
FROM sales;

However, in MDX you would have a calendar dimension and a sales amount measure, and your MDX would look something like:

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({NULL:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

Conclusion

As you can see, trying to solve MDX problems by applying T-SQL principles, you will likely run into problems.

If you want to learn MDX, you need to learn MDX as if it were a completely new language and not a new syntax, because that's what it is, a different language and not a SQL dialect.


How to know if I am running SQL Server 2008 R2?

If you were running SQL Server 2008 R2 it would have 10.5.xxx instead of 10.0.xxx.  And it would also say Microsoft SQL Server 2008 R2 ....

You can always find out the named version by the major/minor build number:

10.5 : 2008 R2
10 : 2008
9 : 2005
8 : 2000
...so on and so forth


I just put your command into a SQL Server Agent Jobstep configured to use CmdExec; it ran the command perfectly.

USE [msdb]
GO

/****** Object:  Job [CmdExecTest]    Script Date: 2018-03-19 10:55:07 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2018-03-19 10:55:07 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CmdExecTest',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'This job provides a way to test SQL Server Agent Jobs',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'<me>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 2018-03-19 10:55:07 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''C:\Backup'', @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = NULL, @CheckSum = ''Y'', @LogToTable = ''Y''" -b',
        @flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The result was a job failure for me because I don't have the dbo.DatabaseBackup stored procedure:

Msg 2812, Level 16, State 62, Server MyServer, Line 1
Could not find stored procedure 'dbo.DatabaseBackup'.

However, this indicates that sqlcmd was successfully executed.


How to normalise a relation using functional dependencies

A simple way to check if a set of attributes is a key, is to compute the closure of those attributes with respect to the functional dependencies, and see if this contains all the attributes of the relation.

So:

{A B}+ = {A B C I}

{A B D}+ = {A B C D E F G H I J}

And so {A B D} is a key. You can also see that every key must contain A B D since those attributes appear only in the left parts of the dependencies, and since {A B D} by themselves are a key, there are no other keys.


The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable.

For the weird header row, you can always specify that there is no header row and the connection manager should skip N rows.

Unfortunately,  there's not much you can do about the parsing itself when there's embedded delimiters. Logically, you can look and see that Kansas City, MO was all one field but the parser is simplistic and breaks the string on the delimiter, baring an escape character.

For spaces not empty strings, the Import-Export wizard way would be to import to a table of all varchar columns, and then write an extract query performing whatever preferred magic to make it into a NULL NULLIF(RTRIM(MyCol), '') AS MyCol

Random notes for future readers

If you're on 2005 or 2008, then the only way to get the SSIS editor, BIDS (Business Intelligence Design Studio) is to have a copy of the SQL Server installation media. 2012+ Microsoft allows for the download and installation of the SQL Server Data Tools-BI edition without the need for a SQL Server installation.

The Import/Export wizard is available in all editions of SQL Server, including Express, but the limitation here is that you cannot save the generated SSIS package if you are using Express. All the other versions work fine.


TIME Function changes between 5.5 and 5.6

I cannot see a problem here. If anything, I would say the problem was with the older behaviour, because it was just inconsistent. Depending on whether '0000-00-00 00:00:00' was passed as a literal or as a column value, TIME would return you either a 00:00:00 or a null. Since version 5.6, however, it consistently returns 00:00:00.

So, to answer why the behaviour changed, the previous behaviour was buggy and the new one is (more) consistent.


Run Querys on List of Databases (For Each loop?)

ALTER DATABASE does not take arguments for database name, so you'll have to construct dynamic SQL, e.g. instead of:

ALTER DATABASE @DBName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

You'll need this:

DECLARE @sql nvarchar(max);

  ... inside loop ...

  SET @sql = N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    '
    + N'ALTER DATABASE ' + QUOTENAME(@DBName)
    + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;';

  EXEC sys.sp_executesql @sql;

However assuming you have a backup (and if you don't, you should), why bother detaching? Much cleaner to just drop, then you aren't tempted to use an mdf file as a backup, and you don't have to worry about your data folder being permanently full of abandoned data files...


ROR application with Mysql 5.6 - transaction-isolation with Master -Slave

Let's look at it in a different way...

Are the transactions running longer than a second?  If so, rethink them.
Do you test for errors and rerun a transaction if there is a Deadlock?

If it is not too big, let's see the transaction and the SHOW CREATE TABLE.


Can spatial types be used to store the dimensions of 3-dimensional objects?

Yes, of course ST_MakePoint takes a Z cord (and even an M cord, hello time!)

  Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason. [...] Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

Dollars are a unique function of cents; pounds a unique function of ounces (x,y,z) are nothing like that. They're three coordinates for discrete spatial dimensions.

  You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

There is no point in reinventing the wheel. You could store them a lot of ways. They tend to be stored something ISO/IEC 13249-3:2011, detailed in PostGIS.

  Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

The conventional approach to store (x,y,z) is with a spatial 3d type.


PostgreSQL - Enforcing unique constraint on date column parts

Using EXTRACT(year/month ...) works, too:

create unique index year_month_uq
  on foo
  ( extract(year from mydate),
    extract(month from mydate)
  ) ;


It's a bit hard to follow your sample, but something along the lines should do it:
select to_jsonb(main)||ch.data
from main_table main
  join (
     select main_id, jsonb_agg(to_jsonb(child)) as data
     from child_table child
     group by main_id
  ) ch on ch.main_id = main.id

If you want everything as one gigantic JSON array, use select jsonb_agg(to_jsonb(main)||ch.data) in the outer select.


Replication says eu-central-1 but the actual name is eu-central. It can't find eu-central-1 because it doesn't exist so it throws NoHostAvailable. Change the replication strategy to match the DC name.


Why is mysqldump not saving newly added databases?

1) check your last 10 lines of dump file

 tail dump.sql

it should show your dump file properly completed or not.

2) grep the database name in dump file

 cat dump.sql | grep newdb

with the above 2 steps you can find is your backup is completed propery or not and the new database exists or not.


Optimize user segmentation by bytea column

I'm not sure, if PostgreSQL each time of this update will overwrite
  full file or only one bit in the hard disk?

PostgreSQL will definitely rewrite the whole column on any change, as it cannot do in-place updates, let alone in-place intra-column updates.

But you could break down the segment into fixed-sized sub-segments or partitions. For instance with segments of 2^16 bits, you would at most rewrite 65536/8 = 8192 bytes per update. Personally I've been using this for custom full-text indexing (one vector per word and document-IDs used as bit numbers) and have found this one-bit-per-match-inside-bytea structure very efficient overall when segmented like that.

Also bytea in TOAST storage is compressed by default, so if there are large portions of identical bytes in the vectors, they're going to be stored in less bytes than the nominal size.


Getting SQL Server Agent to run multiple instance of a job at a time

As stated in the Note of MSDN Create and Attach Schedules to Jobs

  Only one instance of the job can be run at a time. If you try to run a job
  manually while it is running as scheduled, SQL Server Agent refuses the
  request.

Then I'd rather rewrite the SSIS package to do the paralelism inside the package. As I  don't use SSIS - except with MAintenance Plans - I cannot tell you how to do that.


The only key is {BC}. You have a partial key dependency: C->D. Since you have a partial key dependency, R is not in 2NF. So R is also not in BCNF.

An informal way of expressing BCNF is that every arrow in every FD is an arrow that comes out of a candidate key. In R, the arrow in "C->D" doesn't come out of a candidate key.


sub and sub-sub item organization system

If you can only have one Parent article, add a column and call it 'parentArticle'.

ALTER TABLE table_name
ADD parentArticle int

This will link to the article id of the parent article. You can then have as many depth as you would like.

  I would like to be able to shift a set (articles and all sub-articles)
  under the domain of another article with (relative) ease.

You could then add a 'category' column and a category table or, to keep it under articles, have a master article named after its category.


The solution is to move the enclosed by clause into each line instead of overall for the table. For example:

create table ext_tb (
    a varchar2(255),
    b varchar2(255),
    height varchar2(255)
) organization external (
    type oracle_loader
    default directory my_data_dir
    access parameters (
        records delimited by newline
        fields terminated by '\t'
        -- DO NOT add the enclosed line here
        lrtrim
        missing field values are null
        (
            a char optionally enclosed by '"',
            b char optionally enclosed by '"',
            height
        )
    ) location('my_file.ttx')
)

Now, that pesky height field will be in the database as "5'6.35"" (three double-quotes in it). You can run a query on the column to remove the quotes.


SQL Server 2014 Always on listener on named instance for default port

This is happening because you have configured LAB03 listener to listen on port 1433 which is default port of default SQL Server instance, that is why when you connect using the listener it is connecting to SQL Server which is listening on port 1433. While Microsoft allows you to use default port 1433 for listener but this is not a correct thing to do specially when you have multiple instances and listeners. To get around this problem you have to change the port number of listener VT-DB-LAB03
sqllab04\inst02 - sqllab05\inst02 - Listener VT-DB-LAB03 (port 1433)

You can choose free port and configure listener to listen on this port after you do so the issue will resolve.
If you are choosing a non default port for listener you would have to use port number in connection string
Quoting from Docs.Microsoft

Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.
You can also designate a non-standard listener port; however this
means that you will also need to explicitly specify a target port in
your connection string whenever connecting to the availability group
listener. You will also need to open permission on the firewall for
the non-standard port.
If you use the default port of 1433 for availability group listener
VNNs, you will still need to ensure that no other services on the
cluster node are using this port; otherwise this would cause a port
conflict.

I suggest you also read AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.


Why does a parameterised query lead to an index scan but hardcoded values leads to index seek?

This is what is called parameter sniffing. What happens is when you execute a query, the underlying plan is stored and reused however that particular plan may not suit your variable. This is a normal and expected behavior in SQL Server. Because compiling queries is expensive and takes time, SQL server stores plan in the cache and as a DBA we want our SQL Server to re-use them as much as possible so that we can save our time and resources.

There is a very nice video from SQL Server SME Mr. Brent Ozar listed here which can give you full details on parameter sniffing, possible work-around and what should be done in order to avoid the same.

What you have asked in the question, almost same thing has been explained by Mr. Greg Larsen and you would get detailed info on variable and hard coding the values at this link.

You may also read details of parameter sniffing for azure on this link.

Few more interesting reads on the same topic:

https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc

I hope above helps.


Based on a simple test case I just wrote:

@Test
public void test() throws SQLException {
    PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
    ps.setString(1, "someuser");
    ps.executeUpdate();
}

I think the error you refer to is probably:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 10
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2245)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1974)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:254)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:565)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    ....

The reason for this is that PostgreSQL's protocol can only bind placement parameters for "plannable" statements, which are SELECT, INSERT, UPDATE, DELETE and a few others. For these statement types it only supports placement parameters for literal values, not for identifiers or other syntax elements.

For other statement types, literals must be substituted in by the client. Some drivers support client-side emulation of prepare for these statements, so they seem to work transparently, but PgJDBC does not currently support this.

PgJDBC actually supports client-side parameter binding if you're using the legacy v2 protocol, but it doesn't expose this functionality for connections on the v3 protocol. Even if you're not using server-side prepared statements it'll still use server-side parameter binding. Forcing the v2 protocol will work, but is a poor workaround with lots of other consequences elsewhere - plus at some point PostgreSQL will drop support for the v2 protocol entirely.

I think this is an issue with the driver and/or PostgreSQL's wire protocol. The user shouldn't have to care about whether the server supports parameter binding for some statements and not others, the driver should be taking care of that - or better, the server should just support it for all statement types.

Unfortunately, PgJDBC also doesn't expose its internal implementations of safe identifier and literal escaping for client application use. If standard_conforming_strings is on, though, the rule is very simple:

Replace every ' with ''; and
Wrap the whole thing in single quotes

The same applies to identifiers, with double quotes.


What "import" mechanism was used? Some will automatically postpone the secondary-index creation for you.
I don't understand "I'm using stored procedure and with indexes it was very very slow". SPs being used for the import? Or indexes interfering with SPs? What?
Is /tmp on the same disk and in the same disk 'partition'? This is critical when it needs to "move" the result to the mysql directory.
You really need to upgrade -- 5.6 has some indexing improvements; 5.7 and 8.0 have more.
MySQL uses a combination of RAM and disk -- trying to minimize I/O.  So, the question of "will it use RAM" is moot.  It will "do what is optimal".
To create a secondary, non-UNIQUE, BTree index, MySQL probably

reads the entire data, writing the secondary keys and primary keys to a file
sorts that file (OS file sort is quite efficient)
reads the file and builds the BTree for the INDEX; this will involve writing to disk.

However. "read" and "write" and "file" are cached in based on InnoDB's whim and the OS's whim.  Furthermore, step 3 may delay the actual write to disk of the BTree.
UNIQUE requires a check; FULLTEXT and SPATIAL were not available in InnoDB in 5.5.  If you are using Engine=MyISAM, some of what I have said is incorrect.
A related question -- Is that OS sort faster than inserting the rows one at a time and building the index on the fly?  The answer is "probably".  On-the-fly requires keeping index entries in the in-RAM key_buffer or buffer_pool, plus flushing such as it goes along.  That gets into another long discussion.
More
The main part of ALTER TABLE (at least on 5.5 and before) involves reading the entire table and (depending on the specific Alter) rewriting the data BTree and/or writing index BTree(s).
If tmpdir refers to a directory that is on the same disk partition as the main mysql/ directory then various ALTER TABLEs can "move" a temp file (which such is required) from tmpdir to mysql/databasename/ at the end of the Alter.  The "move" is accomplished (at least on Linux) by a couple of quick directory tasks.  If different disk partitions are needed, a physical "copy" is needed; this requires a big read and write.
A file sort is likely to be used as one step in building an index.  How this is done is up to the OS.  But it is equivalent to reading and writing the data being sorted (the index's BTree) one or more times (depending on number of rows in table).  For a tiny table, the OS will effectively cache the effort in RAM.  For a huge table multiple "sort-merge" passes will be required.
Sorting is "O(N*logN)".  That is, if you double the number of items, it takes more than twice as long to complete the sort.  (However, I/O dominates the time consumed, so one can estimate time very precisely.)
If the server is configured with multiple not-big-enough physical disks, the above should help with understanding that part of the Alter.
It is optimal (for MySQL) to give most of RAM to MySQL.  But, when sorting is necessary, the OS needs some room, too.  The tradeoff here is beyond my understanding of the details.


Check Free Space in SQL Server (License Limit)

You can utilize the below to see how much free space is left in the data file(s) as well as how much you've already used. The limit for data files in Express edition is 10 GBs currently(https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15)
The code is based off of: https://am2.co/2016/04/shrink-database-4-easy-steps/
USE DemoDB
GO

SELECT
DB_NAME(),
dbf.name AS LogicalName,
dbf.type_desc AS FileType,
fg.name AS FileGroupName,
dbf.physical_name AS PhysicalFileLocation,
CAST(  (dbf.size / 128.0)  / 1024.0 AS DEC(10,2)) AS FileSizeGB,
CAST(  (FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024 AS DEC(10,2))  AS UsedSpaceGB,
CAST(   ((dbf.size / 128.0)  / 1024.0) - ((FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024) AS DEC(10,2)) AS FreeSpaceGB

FROM
sys.database_files dbf
LEFT JOIN sys.filegroups fg
ON dbf.data_space_id = fg.data_space_id

ORDER BY
FileType DESC,
FileGroupName,
LogicalName


Change the database from restoring state to standby mode

The way I solved is (in case it helps others): I first identified the last log file restored from the database properties (shows by date) and continued to restore on standby mode starting from the last log file then works perfect!


use cases for db.collection.validate() - checks the structures within a namespace for correctness by scanning the collection’s data and indexes

There is no prescribed need to run db.collection.validate(true) on a regular basis for a healthy MongoDB deployment. Validation with the true or {full:true} parameter can be resource intensive, as this iterates through the collection's data & index structures.

The validate(full) command is typically only used as a diagnostic aid in the event of suspected local data corruption or (for WiredTiger in particular) to true up collection counts after an unclean shutdown. As at MongoDB 3.4, validate() is a read-only command with the exception of validate(true) which will check & adjust collection counts if you are using the WiredTiger storage engine.

Validation generally only surfaces obvious problems in data structures, and cannot detect all possible forms of data corruption. Successful validation can be used as a sanity check if one of your replica set members is encountering data problems that result in an obvious assertion and you want to verify if other secondaries appear to be healthy. If full validation is unsuccessful on a replica set member, the general remedy is to resync the member rather than attempting to repair (which may result in a divergence of data from the other replica set members).

  We have regularly unclean shutdowns.

If you have regular unclean shutdowns and are using WiredTiger it would definitely be advisible to run validate to true up collection counts after a restart as these may be inaccurate. Ideally it would be preferable to investigate and resolve the issues or practices leading to your frequent unclean shutdowns.


Is it safe to add new features to production SQL Server Instance

The only danger I would say is installing new features that require the database engine service to get taken down when you don't expect it to be taken down.

Other than that, adding new services to an old instance isn't dangerous on its own. I have added reporting, analysis, and integration services post install plenty of times over the years without any unforeseen issues.


Is an automatic failover to the primary replica of a readonly replica in an availability group possible?

This is covered in the documentation: Configure Read-Only Routing for an Availability Group (SQL Server), but for this approach to work, you will need to configure your read only routing URL on both replicas.

  Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

This is a PowerShell example from MSDN on how to add read only routing URLs to both the replicas available:

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

There are some requirements for connection strings as well,if you want to use read only routing:

Use the TCP protocol.
Set the application intent attribute/property to readonly.
Reference the listener of an availability group that is configured to support   read-only routing.
Reference a database in that availability group.


Do I need tweak something for this MySQL setup?

Your questions come down to, basically, "Do I have a problem" and you go on to say at the moment no but not sure about the future.

  The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not.

I don't think anyone can suggest that there is or is not a problem here based on the fact that performance is currently ok but you are worried about long-term performance concerns.  Without knowing what sort of growth you are expecting nobody can give more than a guess.

So I am going to go a different approach and suggest that you need to start with a framework for determining whether you need to start thinking about optimizing/upgrading.  This seems the best way at this current point.

Your database looks like it fits in RAM.  Is it growing?  How fast?  How big is it?  When will it exceed 250M in size?  When will it exceed your server's RAM?
How many concurrent connections do you typically have?  How fast is this growing?

Once you have this sort of data  (and you really need to start with hard numbers here!) then it should be pretty straight-forward to determine whether you need to be thinking about capacity or performance tuning.  However as long as performance is good right now, then you have time to gather data, evaluate, and plan.

UPDATE BASED ON FEEDBACK

So the db doesn't fit in RAM.  It is apparently a virtualized server with only 512MB of RAM.  I don't think you need a larger system yet but it is worth keeping your eyes on the performance numbers.

The cache is going to be the MySQL page cache.  This is used to store frequently used parts of the database in memory.  Note that ALL of your select and update queries are currently hitting the cache.  Your inserts are not as expected.  This means you have enough ram reserved for the MySQL page cache to hold everything being queried in RAM.  While I had assumed it was the full db, actually unqueried portions of the db would not need to fit in RAM for these numbers.

You have a few options.  You could decide to get more RAM (and run tuner programs as @RolandoMySQLDBA suggests) when your cache utilization hits 90% or so.  You could upgrade things now with the idea you may likely need it.  Or you could be more conservative and wait for some read queries not to hit the cache on a consistent basis.  It depends on what you see as necessary performance.


SQL Server Replication between a domain and VLAN

Is it possible to configure replicaiton ?

Yes.

  if so, is there any way to open connection only unidirectional , from production domain to VLAN ?

Packet-flow must, of course, work in both directions.  If the Distributor can connect to both the Publisher and Subscriber, you can use Push Subscriptions.  With a Push Subscription all the processes run on the Distributor, and the Subscriber would never need to connect to the Publisher or Distributor.


I recreated a SAS PROQ FREQ Missing values example due to lack of more information.

Example:
https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect016.htm

Sas data set:

   data one;
      input A Freq;
      datalines;
   1 2
   2 2
   . 2
   ;

Sas Query:

   proc freq data=one;
      tables A / missing;
      weight Freq;
      title 'MISSING Option';
   run;

Sas result set

SQL Server Data Set --> Using two tables to number duplicate missing values

DECLARE  @A TABLE (id INT,
      Freq INT);
      DECLARE  @B TABLE (id INT,
      Freq INT,
      rownum int);

      INSERT INTO @A
   VALUES( 1, 2),
   (2,2),
   (NULL, 2),
   (NULL, 2)
   ;
   insert into  @B(rownum,id,Freq)
   select ROW_NUMBER() OVER (ORDER BY  id) , id, freq
   from @A

SQL Server Query: (compatible with SQL Server 2008)

SELECT   B.id,
B.Freq as Frequency,
        (cast(B.Freq as DECIMAL(2,1)) / cast(sum(B2.freq) over() as DECIMAL(2,1)))  * 100 as Percentage ,
        sum(B2.Freq) as cumulative_frequency,
        ( (cast(sum(B2.freq) as DECIMAL(2,1))) / cast(B.Freq as DECIMAL(2,1)) *2) / cast(sum(B2.freq) over() as DECIMAL(2,1))  * 100 as CumulativePercentage
FROM @B B
inner join @B B2 on B.rownum >= B2.rownum
GROUP by B.rownum,B.id,B.Freq,B2.Freq

SQL Server result set:

id      Frequency   Percentage  cumulative_frequency    CumulativePercentage
NULL    2          25.000000    2                       25.000000000
NULL    2          25.000000    4                       50.000000000
1   2              25.000000    6                       75.000000000
2   2              25.000000    8                       100.000000000


Risks of Switching Between Simple & Full Recovery in SQL Server 2005

In this situation you should first investigate what amount of data loss is possible-- i.e., whether you need point in time recovery at all times.

If you DO need to be able to restore up to the minute, then breaking the log chain by switching to the simple recovery model is not acceptable. You lose the ability for up to the minute restore after the final log backup is taken prior to switching to simple, and you do not regain it  until  you have switched back to FULL and then performed an additional log backup.

If you do NOT need to be able to  restore up to the minute, you can investigate using the BULK LOGGED recovery model as a better alternative than switching to simple. Do note that  you will still have a large transaction log backup after completing BULK LOGGED transactions, however.

Here is a helpful MSDN reference on switching from the FULL recovery model to either BULK LOGGED or SIMPLE.

From the context of your question, it sounds that your database log files are on the same drive as your tempdb files. If it is possible to isolate your TempDb files to another drive, that would be beneficial for management purposes, as well as performance (depending on your drive layout).


I suspect the "tragic tale" might be that someone who had sysadmin privileges (but shouldn't have) went in and deleted one of these things (possibly by mistake).

But another thought occurred to me: someone who should have sysadmin privileges might have done the same thing (by mistake).

In lieu of being able to cripple Management Studio (which I suppose you could do by writing your own an add-in), you should consider simply not logging in as sysadmin except when absolutely necessary.

At my old job we tried to implement a policy where by default you connect to SQL Server as you, and you are given the rights you should have for most tasks. When you need to escalate to do something out of the ordinary, only then do you connect as sysadmin. This worked with some success - however you need to be tolerant of the moaning and griping that will happen. We actually had different database roles such as a read only for our QA department - allowing them, say, to validate a report output by selecting from certain tables directly.


Is it needed to add additional column to make this clustered index unique?

Should I add ProductID also to make the clustered index unique?

You should delete cidx_RequistionProducts and make the primary key clustered instead. The index that is created to support the primary key will do the job for you when filtering on RequisitionID.

  What are the pros and cons?

I see no benefit of having two indexes when one is enough to get the job done. Removing the extra index will save you disk space and IO cost when updating the table.


sql update query for two connected tables

Try using an INNER JOIN statement:

UPDATE rooms r
INNER JOIN reservation s
ON s.room_no = r.room_no
SET r.vacant=true
WHERE s.cus_id=1


Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Dynamic SQL is another option.  I would try both this method and @a1ex07's method and see which runs faster for you.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25)
                                 ,@---Name  varchar(25)
                                 .........
                                 ......

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * ' + CHAR(13) +
        'FROM [Ticket]  ' + CHAR(13) +
        'INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID  ' + CHAR(13) +
        'INNER JOIN dbo.[Status] ON [Ticket].Stat .... ' + CHAR(13) +
        '..... ' + CHAR(13) +
        ' ' + CHAR(13) +
        ' ' + CHAR(13) +
        'WHERE 1=1 '

IF @Category IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [TicketType].Name LIKE @Category '

IF @ReportName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Ticket].Name LIKE @ReportName  '

IF @Status IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Status].StatusName LIKE @Status '

IF @PhaseName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Phase].PhaseName LIKE @PhaseName  '

IF @Name1 IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND (BA.FullName = @Name1 OR AD.FullName = @Name1) '

EXEC sp_executesql @sql, N'@Category varchar(25), @ReportName varchar(25), @Status varchar(25), @PhaseName varchar(25), @Name1 varchar(25)',
                @Category, @ReportName, @Status, @PhaseName, @Name1


Weird case , how do I delete a cluster under these circumstances

Use the Remove-Cluster PowerShell command with -Force.


How to get response from stored procedure before it finishes?

You probably are looking for the RAISERROR command with the NOWAIT option.

Per the remarks:

  RAISERROR can be used as an alternative to PRINT to return messages to calling applications.

This doesn't return the results from a SELECT statement, but it will let you pass messages/strings back to the client. If you want to return a quick subset of the data you're selecting then you might want to consider the FAST query hint.

  Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

Added by Shannon Severance in a comment:

From Error and Transaction Handling in SQL Server by Erland Sommarskog:

  Beware, though, that some APIs and tools may buffer on their side, thereby nullifying the effect of WITH NOWAIT.

See the source article for the full context.


Change current working directory from MySQL client prompt

You can't. There is no code in the mysql client executable to change directory.


Updating 3 million rows in 100million+ table

Let's say your statement is:

update A set z = 'HELLO WORLD' where x = 1 and y = 2;

1) This does extra work, because it updates column z to HELLO WORLD, even if it was HELLO WORLD already. Rewrite it as:

update A set z = 'HELLO WORLD' where x = 1 and y = 2 and (z != 'HELLO WORLD' or z is null);

2) If you have 2 columns indexed seperately, it is possible, but quite rare, that the database uses both indexes for the lookup. You can try creating 1 index having both columns, but I highly doubt this would be of any help, you would need a big, wide table with exceptionally good clustering for x and y columns to benefit from this for 3/100 million rows.

3) DML does not run in parallel by default. You can enable parallel execution as:

alter session force parallel dml parallel 8;

Note that this will not force parallel execution even if the syntax suggests that. It's like adding parallel 8 hint to your statements in your session.

4) If column z is indexed, than the UPDATE statement need to maintain the index(es) as well. Sometimes it is better to make the index(es) unusable, run the DML, then rebuild the index(es).

5) If the content of column z does not fit its original place (HI becomes HELLO WORLD), and your table blocks are full, you may experience row migration, that makes performance worse not only during the UPDATE, but for later queries as well.

You would be surprised how much better CTAS can be.

6) If column z is part of an enabled foreign key constraint, you can not simply update it to any value. The database needs to check the parent values to make sure you are updating to a valid value. Again, disable, run the DML, enable validate, may improve performance.

But this is just guesswork without the facts.
An SQL Monitoring output, or execution plan with runtime statistics could help.
If that is not enough, monitoring the wait events + parameters in v$session, or a raw SQL trace could help even more.


How to know which query is resulting in errors in the trace?

I would add the events SQL:BatchStarting and/or SP:StmtStarting as well. That way, you can compare the SPID (the session ID) and find which query executed on which SPID.

For the record, Profiler is being replaced by Extended Events (XE for short) as of SQL Server 2008, though the GUI is really only usable as of 2012 in my opinion.


Is it possible to purge the whole content of Oracle's SYS.INDEX_STATS view?

The contents of INDEX_STATS are temporary. Every time you start a new session, the view becomes empty.
Additionally, this view contains data only for the last index that was analyzed.

So there is no need to purge the contents, it is just a matter of organizing your code properly and storing the results in a permanent table if you need them.


I recently answered similar question here: database-archive-solutions

You should read about  postgresql table partitioning, if you have good way to split data in parts.


Defining OLAP or OLTP on table level in Oracle/postgres/SQLserver

Oracle includes an in-memory columnar store that lets you combine OLAP-style queries on top of a plain tabular data mode without requiring any transformation
See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/intro-to-in-memory-column-store.html#GUID-BFA53515-7643-41E5-A296-654AB4A9F9E7


Purge old records from mysql ibdata1 file

What you are asking for has never been documented or attempted.

However, I have answered two posts like this in the past:

Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Dec 17, 2012 : Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have an additional suggestion for data you are going to delete, going forward. You will have to put some good developer effort in this one:

Before dropping a table, you should

go to every INT and FLOAT value and make them 0
go to every CHAR, VARCHAR and TEXT field

get the length of the field
update the column with the same number of bytes with all Xs or all 0s

You will have to examine the entry in INFORMATION_SCHEMA.COLUMNS for every column to determine which is numeric and which is character. Once you have manually overwritten all data in the old table, then you are free to drop it, knowing you  masked the data beforehand.

CAVEAT

If all your InnoDB tables have no constraints/foreign keys, here is something you can do during some down time.

Convert all InnoDB tables it to MyISAM
Shutdown mysql
delete ibdata1
Startup MySQL
Convert those MyISAM tables back to InnoDB

I can't take credit for that idea. Shlomi Noach suggested it back on September 19, 2013.

If the DB is not too big, here is how you can script it.

MYSQL_USER=root
MYSQL_PASS=password
MYSL_CONN="-u${MYSQL_CONN} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Convert_To_MyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Convert_To_InnoDB.sql
mysql ${MYSQL_CONN} -ANe"SET GLOBAL innodb_fast_shutdown = 0"

At this point, look at the two scripts in vi or less.

If you are satisfied with the code, then you can do this:

mysql ${MYSQL_CONN} < Convert_To_MyISAM.sql
service mysql stop
rm -f /var/lib/mysql/ibdata1
service mysql start
mysql ${MYSQL_CONN} < Convert_To_InnoDB.sql

Give it a Try !!!


Disable the log shipping jobs on both servers.
Back up the database on Server A.
Restore the database with the backup you just created and with norecovery on Server B.
Re-enable the log shipping jobs on both servers.


What happens on the primary when log cannot be restored on the secondary on Log Shipping

A restore failing on the secondary will have no impact on the primary. You want to monitor your secondary DBs to make sure this hasn't happened.


pg replication connection not returns wal events to java code client , it hangs some times

I have fixed issue using SQL slot creation sql command.
Removed below code which blocks executions
     ReplicationSlotInfo info=       replConnection.getReplicationAPI()
                    .createReplicationSlot()
                    .logical()
                    .withSlotName(slotName)
                    .withOutputPlugin(wal2json)
                    .make();

And my issue was fixed.
Let me know if anyone face same issue I can help.


How to get data from MySQL table based on a junction table

You can do something like this:
SELECT a.*
   ,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(a.`longitude`, a.`latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     )
     AND ai.interest_id IN (1,2,3)
HAVING distance <= 22

As long as you don't provide data, I can only hint at how it would look like.
SELECT a.*,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(`longitude`, `latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     )
     AND SUM(ai.interest_id =1) + SUM(ai.interest_id =2) + SUM(ai.interest_id =3) = 3
GROUP BY a.id,distance
HAVING distance <= 22


Constraint to one of the primary keys as foreign key

You could create a TRIGGER on the grid table that will do an outer join on the inserted values and the grid_col table.This should give you NULL values for non-matched records which you can than remove from the grid table.


count distinct column without null value in MySQL 5.7

MySQL works like you expected and it should return 1.
create table t (a int, b int);
insert into t (a, b) values (1, null), (2, 1);
select * from t;
select count(distinct b) from t;

dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4fd6a3744a042c376880f030f4d12101


Where is my audit as Oracle sys audit is enable

I know I am late to the party but anyways:

your AUDIT_TRAIL value should be set to DB,EXTENDED and not DB_EXTENDED.

You can change it with:

alter system set audit_trail=db,extended scope=spfile;

Bear in mind that after setting it to DB,EXTENDED you have to restart your instance for changes to take effect.

Find more on very handy Oracle's documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams016.htm#REFRN10006


DB<>fiddle and other online-services allow you to test different DBMS without having to install them locally. As shown in:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7692d5abba854bdc57d90c95335014c0

There is no access to table B.

You may find the following article by Lukas Eder of interest, he compares algebraic optimizations for different DBMS:

https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/


It seems that you want multiple bidders to be returned if there are more than one with the lowest bidderrate.

In that case, you could use TOP with TIES

select TOP(1) WITH TIES  auctionid, bidder, bidderrate
FROM [dbo].[Ebay]
WHERE auctionid = 8211851222
ORDER BY bidderrate asc;

DB<>Fiddle based on your example.


Actually if you get Actual Plan, you will be able to see, indexes that modified by every update.

When you do update, Cluster Index and all indexes that keys and included column , had updated, will be modified.

Example 1 :
update #Temp set ClientName = 'foo'
Execution plan : The clustered index will be update

Example 2:
update #Temp set Balance = 'foo'
Execution plan : The clustered index and [IX_Temp_RegistrationNumber] will be update.

Tip :If your table haven't so many indexes or update operations isn't high, then there isn't any performance issue.


I don't think you have many options here. If you want to maintain high availability I'd say shutdown on node and configure it as a replication slave of one of the cluster nodes. Rebuild it from a mysqldump of the cluster node. Once slave catches up, restart it as wsrep node 1 of a new cluster and then promote it as master. Shutdown the other two nodes of the old cluster and empty datadir, and let each join one at a time to the new cluster. Others may have a better suggestion though.


Need help to build SQL query for a complex scenario

Exactly, How you will pass  parameter value ?

In my example, I am passing parameter value in variable.

Mention it clearly.I understand that above output is only for explanation.

create table #Cuisine(CuisineId int,CuisineType varchar(50))
insert into #Cuisine values
( 1 ,'Chinese'),( 2 ,'Indian'),( 3 ,'Italian'),( 4 ,'French')

create table #DeliveryType (DeliveryTypeId int, DeliveryType varchar(50))
insert into #DeliveryType values
 (  1,'Delivery') ,(  2,'Pickup') ,(  3,'Dine In')

  create table #MealType (MealTypeId int, MealType varchar(50))
 insert into #MealType values
(1,'Breakfast')
,(2,'Lunch')
,(3,'Dinner')

 create table #Food(Food  varchar(50),CuisineId int,MealTypeId int,DeliveryTypeId int)
insert into #Food values
('Cakes',               NULL ,        NULL,          NULL)
,('Pizza',               3 ,  NULL,            NULL)
,('Noodles',             1 ,  2 ,       NULL)
,('Butter Chicken',      2  ,  3,       1 )
,('Ice cream',           NULL,         2 ,      2 )
,('Soup',                NULL,        NULL,             2 )
,('Drinks',              NULL,        NULL,             1 )

Parameter value,

Declare @Cuisine int,@Mealtype int,@DeliveryType int=2

Since you have condition like if delivery is Any then consider both null and Delivery=1 and vice versa.

So Create one local temp table to define value.It is easier and cost effective to join than write complicated and expensive where condition.

Create table #DeliveryParameter (DeliveryTypeId int)

if(@DeliveryType is null or @DeliveryType=1)
insert into #DeliveryParameter values(-1),(1)
else
insert into #DeliveryParameter values(@DeliveryType)

select * from #Food F
inner join #DeliveryParameter DP on f.DeliveryTypeId=dp.DeliveryTypeId
where ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine))
and ((@Mealtype is null and MealTypeId is null) or(MealTypeId=@Mealtype))

drop table #Cuisine,#DeliveryType,#MealType,#Food,#DeliveryParameter

Notice : ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine))

This type of condition is very expensive if you records are in millions .

So if you treat Null or Any as -1 or 0 for example, then condition will be simplified to

CuisineId=@Cuisine

Like in #Food table store -1 instead of Null and in variABLE pass -1 instead of null.

If you need output in some other format then mention it.


Linked Server already exists in sys.server

Issue was resolved.

Actually it was for some ETL process for which I need to access a server which is not in AOAG and a server which is part of AOAG.I have added the listener only in one remote server as Linked server but not AOAG servers.

  I have to add Listener as Linked server in each AOAG servers(Replicas) and the remote server which is a stand-alone
  to make the ETL process work.

Hope this helps someone.


Given two known statements, how can I replicate deadlock?

Nothing can prevent deadlocks in a RDBMS. However, you can greatly reduce the chance of the occurring by following a few simple rules:

Make sure your queries are tuned well, using appropriate indexes.
If you have to take concurrent locks on multiple tables, all your statements and procedures should access those tables in the same order.
Keep your transactions as short as possible.

That will make it a lot less likely for you to encounter deadlocks. However, you cannot completely avoid them, so your code should be resilient and just automatically wait a random time interval (a few milliseconds) and then try again. Only if after three or so retries you still cant get the statement executed log the error.

Now to your question, of how to reproduce the deadlock: There are several types of deadlocks. The simplest one is the deadlock on two resources:

connection A takes lock on resource 1
connection B takes lock on resource 2
connection A tries to take conflicting lock on resource 2 and has to wait
connection B tries to take conflicting lock on resource 1 and has to wait

Now both connections wait for each other causing a deadlock. This type of deadlock can be fairly easily reproduced by stepping through the code one statement at a time on two connections. Other deadlocks are not always as easy to reproduce because e.g. they might only occur on memory pressure situations or similar.

The easiest way to get more information about deadlocks is, to set up a deadlock-graph trace. The deadlock graph contains a lot of information that can be helpful understanding the captured deadlock. As your system (hopefully) has deadlock only relatively infrequent, you might have to run the trace for a while. But if you are capturing only that one event, it won't use a lot of resources. Once you captured the deadlock graph look at it as XML. The graphical representation given by the profiler does reveal only a limited amount of information, but it is a good starting point.


WHERE IN query to very large table is slow

Sometimes large in clauses can be improved by rewriting them as a join against a values clause.

so instead of

select *
from all_items
where user_id in (1,2,3,4,5)
 ...

you can use

select ai.*
from all_items ai
   join (
       values (1),(2),(3),(4),(5)
   ) t(id) on t.id = ai.user_id
where item_add_date >= ...
  and item_add_date <= ...;

An index on (user_id, item_add_date) should improve the performance


Here are the exact steps I had to run to get it working:

sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

In new terminal:

mysql -u root

UPDATE mysql.user SET Password=PASSWORD('my-new-password') WHERE User='root';
FLUSH PRIVILEGES;

\q

sudo /usr/local/mysql/support-files/mysql.server start


If you're storing it into the same database you're measuring you could use a MySQL Scheduled Event to run the query at a certain time.

an example being:

DELIMITER $$
CREATE
    EVENT IF NOT EXISTS db_measurement
    ON SCHEDULE EVERY 1 DAY
    STARTS '2016-11-15 00:00:00'
    ENABLE
    DO
    INSERT INTO tableName
    SELECT
        table_schema AS "Database",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    FROM information_schema.TABLES
    where table_schema = 'testdb'
    GROUP BY table_schema$$
DELIMITER ;

If it's a different database you'll need to use a shell script of some sort.

Bearing in mind I'm far from an expert on Linux (and ignoring the discussions around permissions, passwords in scripts, SSL etc etc), you could do something along the lines of:

mysql -hxxxx -Pxxxx -uxxxx -pxxxx -se"SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS Size FROM information_schema.TABLES where table_schema = 'dbtest' GROUP BY table_schema;" | { read dbSizeVal; mysql -hxxxx -Pxxxx -uxxxx -pxxxx -e"INSERT INTO dbname.tablename VALUES(${dbSizeVal})"}

Which should get the size value from the remote database, and load it into a table in the local database.

I just used:

CREATE TABLE `tableName` (
    `dbsize` varchar(45) NOT NULL
);

for testing this, but you would probably want a decimal, integer, float datatype depending on the data you return and how accurate you need it.

Also this doesn't include the dbname or time fields. You could use timeVal TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP which would give you the timestamp field.

The database name is tricky, as you would need to carry two variables across, and my script (above) only allows for one (if you are only looking at one database you could just type it directly into the query / set a default value).


Central backups occurring on a replica is a decent strategy.
The guide on quick glance looks ok. Ensure that your server-id is unique. Per mariadb galera guidance galera cluster members shoudl have the same server-id in most scenarios.. Its unclear why binlog-format=mixed when galera requires row so I'd just keep row.
'log-slave-updates' is the critical thing of the master node but for consistency its easy enough to have this on all galera nodes in case you need to change master at some point.
Like all backups consider the process that a restoration would take and business requirements on MTTR (mean time to recover). And test the restoration process when you've got backup to see it works and how long it takes.


Manage multiple short-term tables with same schema or use single table for short-term data?

I don't see much benefit of storing Web App Sessions per table:

Creating and dropping tables is a little more expensive than inserting and deleting rows
Depending on how many sessions can exist (current + inactive that have not been cleaned up yet), 10,000 tables is a bit more to deal with than 100,000 rows in a table
Temp tables are not an option. http is a connectionless / stateless protocol, hence the need for sessions ;-). Since temp tables -- local and global -- are automatically dropped when the session in which they were created ends, they wouldn't persist between calls.
Any app-based mechanism that can clean up obsolete tables can just as easily delete obsolete rows (i.e. an OnSessionEnd event and/or job that runs periodically).

IF, you are using ASP.NET, there is a somewhat built-in option to store Session State in SQL Server:

Session-State Modes
Session Management
Potential enhancements to ASPState (some tips from @Aaron B. to improve performance if you are using ASP.NET with the SQLServer Session-State mode)

That might work with JAVA / JSP, but probably not?

If not using ASP.NET, then you can:

use a single table to store the serialized Session object. (this is one of the options mentioned in the question).
OR, possibly use a cache server, such as memcached, redis, or even Windows Server AppFabric Caching. They might be typically intended for longer-term caching, but I see some benefits to them, such as:

they are based in memory, not on disk (i.e. faster, which is why they exist in the first place)
they have built-in mechanisms for key expiration so you probably won't need app-based clean-up logic. Something to at least consider / look into.


Having a subquery somehow do the same group by as the outer query

It is legal, but confusing and a bad idea, that you use the same table aliases in different levels of the query.  You could likely do what you want by adding the condition and d.start_date = d2.start_date in the subquery, but of course you would have to have one of the aliases be "d2" rather than "d" to do that.

But it seems like you could get rid of the subquery altogether by doing:

select d.start_date,
       count(a.status),
       count(distinct a.id) as checked_ins
from schema1.employee_se_assignment a,
     schema2.se d
where a.se_id= d.id
  and a.status not in ('unassigned')
  and d.customer_name in (‘Test Client’)
  and a.status = 'checked_in'
group by d.start_date
Order by d.start_date

Although these do differ in how NULL values of a.id are tallied, if it is possible for there to be any of these.


SQL server - Re. Filegroup / partitioning

It will depend on the predicate of the query you write against the partitioned table. If you filter on the column that is used as the basis of the partition function and scheme and that filter restricts the data to come from filegroups in the partition that are still online, your query can return results.

This is what makes partial database restores possible; you can restore the PRIMARY filegroup followed by read-write filegroups to bring 'current' data for your VLDB online and then restore the read-only filegroups.

As for what's the better disk-to-filegroup strategy, it'll depend on whether you're using older rotational or newer SSD media, whether the VLDB is for an OLTP application that is IOPS-heavy or a OLAP data warehouse where throughput when scanning tables is paramount, and whether the storage is local to the host or attached via SAN. Ultimately, you'll want to try different configurations, collaborating with your hardware support team and vendor, using benchmarks like from TPC and your application's typical and peak workloads.

For a deeper dive into the methods and strategy, Microsoft's series of documents describing their Fast Track Data Warehouse architecture over the years are a good resource.


Is non-dynamic scalar subquery evaluted for each row?

It is evaluated once.

EXPLAIN ANALYZE will display lines like these:

...
InitPlan 1 (returns $0)
    ->  Index Scan using t2_pkey on t2 (cost=0.29..5.31 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
          Index Cond: (id = 10)
...

loops=1 tells you the subplan has only been evaluated once. The manual:

  In some query plans, it is possible for a subplan node to be executed
  more than once. For example, the inner index scan will be executed
  once per outer row in the above nested-loop plan. In such cases, the
  loops value reports the total number of executions of the node, [...]

With a correlated subquery like:

SELECT *, (SELECT id FROM t2 WHERE id = t1.parent_id) AS t2_id
FROM   t1;

(or something more useful) you would see loops=n,  where n is the number of rows t1.


update table with data from other table in remote oracle db

I did it by:
creating dblink from db A to DB B
and using : merge into Y y using select X.columns from X@dblink x on (x.idX=y.idY)
when matched then update set
y.columns=x.columns --here ids are not included
when not matched then
insert (y.columns) values(x.columns) --ids are included
x.colums and y.colums mean shared columns


Why Can't I shrink log file in full recovery mode

However, why can I not shrink the file in full recovery mode?

It's working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to take.

That, or you're just trying to shrink it at the wrong time (see my note #7 below).

First of all, "log file" is a bit of a misnomer: SQL uses the transaction log as its "working space" for everything it does, so it will always be non-zero in size. In my experience, I normally expect the tran log to be between 10% and 25% the size of the data files, depending on what recovery mode you're using, and the kind of activity on the database. You didn't mention the size of your data file, so I can't judge whether 302MB is big (it frankly doesn't sound very big to me).

Regarding recovery mode, the key question here is: if you have to recover the database (due to an emergency), will you need "point-in-time" recovery? Or is a restore to the latest full backup sufficient?

Putting aside some unusual situations (replication/CDC/mirroring/availability groups), your recovery mode choice is more of a business decision than it is a technical one. In either mode, SQL server uses the transaction log for in-process transactions, the difference is what happens next:

FULL recovery mode: When a transaction completes, it sticks around in the  tran log until you perform a transaction log backup. This tends to make the log larger (depending on how often you run the log backups), but means you can do a "point-in-time" recovery in the event of a disaster: you can restore the latest full backup, then use the tran log backups to "roll forward" to just before the disaster occurred.
SIMPLE recovery mode: When a transaction completes, the space it used in the tran log is immediately available for re-use by later transactions. This keeps the transaction log file (comparatively) small, but means that your only choice in the event of a disaster is to restore from the latest full backup.

In some companies, you'll use FULL for all production databases, and SIMPLE for all QA/DEV environments. Or perhaps some specific databases, like ones used for  reporting and/or data processing don't need full recovery because you can simply re-run the prior day's processing to get back up to date. But like I said, that's a business decision/risk analysis question, not really a technical one.

So, if you don't need point-in-time recovery, then switch it to simple mode, and leave it that way.

If you do need point-in-time recovery, then switch it to full mode, and run regular tran log backups throughout the day, according to your business risk/recovery needs. Hourly is popular, but some people recommend them far more often.

One final point that I alluded to before: once you've selected the correct recovery mode, you might still need to shrink an over-sized log. A few things to keep in mind:

Again, make sure you've chosen the right recovery model
If you're running in FULL mode, make sure you're running frequent tran log backups
Choose a reasonable target size for the log, never try to shrink it to zero. I typically scale it based on the total size of the data files (or at least the used space within the data files)
Read and understand this important advice about VLF size. This might provide more guidance about deciding on a reasonable log size, or give you a better method for getting your log to that size.
Never use SHRINKDATABASE, always use SHRINKFILE
In both simple and full mode, there is a chance that the end of the log file is currently being used by SQL for an active transaction. If that's the case, then it won't shrink as much as you want. Just shrink it again a few minutes later, and if the transaction is complete, it should work better the second time.
With a particularly stubborn tran log in full recovery mode, I've had luck doing SHRINKFILE, immediately followed by a transaction log backup, immediately followed by another SHRINKFILE.