Data inherently ordered as if it were a clustered index

I mocked up test data that mostly reproduces your issue:
INSERT INTO [dbo].[TestTable] WITH (TABLOCK)
SELECT TOP (7000000) N'*NOT GDPR*', N'*NOT GDPR*', N'*NOT GDPR*', 0, DATEADD(DAY, q.RN  / 16965, '20160801')
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
ORDER BY q.RN
OPTION (MAXDOP 1);

DROP INDEX IF EXISTS [dbo].[TestTable].IX_TestTable_Date;
CREATE NONCLUSTERED INDEX IX_TestTable_Date ON [dbo].[TestTable] ([Date]);

Statistics for the query that uses the nonclustered index:

Table 'TestTable'. Scan count 1, logical reads 1299838, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 984 ms,  elapsed time = 988 ms.

Statistics for the query that uses the clustered index:

Table 'TestTable'. Scan count 1, logical reads 72609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:  CPU time = 781 ms,  elapsed time = 772 ms.

Getting to your question:

Is it possible to take advantage of this fact in order to improve the performance of my query?

Yes. You can use the nonclustered index that you already have to efficiently find the maximum id value that needs to be updated. If you save that to a variable and filter against it you get a query plan for the update that does the clustered index scan (without the sort) that stops early and therefore does less IO. Here's one implementation:
DECLARE @Id INT;

SELECT TOP (1) @Id = Id
FROM dbo.TestTable
WHERE [Date] <= '25 August 2016'
ORDER BY [Date] DESC, Id DESC;

UPDATE TestTable
SET TestCol='*GDPR*', TestCol2='*GDPR*', TestCol3='*GDPR*', Anonymised=1
WHERE [Id] < @Id AND [Date] <= '25 August 2016'
AND [Anonymised] <> 1 -- optional
OPTION (MAXDOP 1);

Run stats for the new query:

Table 'TestTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 1, logical reads 4776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:  CPU time = 515 ms,  elapsed time = 510 ms.

As well as the query plan:

With all of that said, your desire to make the query faster suggests to me that you plan to run the query more than once. Right now your query has an open ended filter on the date column. Is it truly necessary to anonymize the rows more than once? Can you avoid updating or scanning rows that were already anonymized? It should certainly be faster to update a range of dates with dates on both sides of it. You could also add the Anonymised column to your index, but that index will needed to be updated during your UPDATE query. In summary, avoid processing the same data over and over if you can.
The original query that you have with the sort is slower due to work done in the Clustered Index Update operator. The amount of time spent on the index seek and the sort is only 407 ms. You can see this in the actual plan. The plan executes in row mode so the time spent on the sort is the time of that operator along with every child operator:

That leaves the sort operator with about 1600 ms of time. SQL Server needs to read pages from the clustered index in order to perform the update. You can see that the Clustered Index Update operator does 1205921 logical reads. You can read more about sorting optimizations for DML and optimized prefetch in this blog post by Paul White.
The other query plan that you have (without the sort) takes 683 ms for the clustered index scan and about 550 ms for the Clustered Index Update operator. The update operator does not do any IO for this query.
The simple answer as to why the plan with the sort is slower is that SQL Server does more logical reads on the clustered index for that plan compared to the clustered index scan plan. Even if all of the needed data is in memory there's still an overhead and cost to doing those logical reads. A better answer is much harder to get, in that as far as I know the plans won't give you any further details. It is possible to use PerfView or another tool based on ETW tracing to compare call stacks between the queries:

On the left is the query that does the clustered index scan and on the right is the query that does the sort. I marked call stacks in blue or red that only appear in one query. Not surprisingly, the different call stacks with a high numbered of sampled CPU cycles for the sort query appear to have to do with the logical reads required to perform the update on the clustered index. In addition, there are differences in number of sampled cycles between the queries for the same operation. For sample, the query with the sort spends 31 cycles acquiring latches whereas the query with the scan only spends 9 cycles acquiring latches.
I suspect that SQL Server is picking the slower plan due to a query plan operator costing limitation. Perhaps part of the difference in run time is due to hardware or your edition of SQL Server. In any case, SQL Server is not able to figure out that the date column is implicitly ordered exactly the same as the clustered index. Data is returned from the clustered index scan in clustered key order, so there is no need to perform a sort in an attempt to optimize IO when doing the clustered index update.


In addition to the rather odd (and possibly over-restrictive modeling of the phone number components), the "data" concerns me.  It basically looks like the EAV pattern restricted to DECIMAL data type, which can have it's place, however there appears to be NO OTHER data modeled at all.

When you finally get round to querying these things, it's a lot easier and involves less pivoting if you can just do things like SELECT revenue - expenses AS profit or whatever it is these different types of data are that are attached to each user each week.


What's the purpose of /*!*/; in mysql binlogs?

They are evidently place markers within the binary logs for processing internal statements according to the context of the commands issued.

When you performed mysqlbinlog against a binary log, you see it acting as a delimiter. There are also opportunities for MySQL to process certain directives native to the particular version of MySQL cranking out the binary log.

Example:

I ran the following commands:

reset master;
use test
show binary logs;
create table rolandotable (a int);
show binary logs;
insert into rolandotable values (1);
flush logs;
show binary logs;

I got this:

mysql> reset master;
Query OK, 0 rows affected (0.21 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.01 sec)

mysql> use test
Database changed
mysql> create table rolandotable (a int);
Query OK, 0 rows affected (0.22 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       203 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> insert into rolandotable values (1);
Query OK, 1 row affected (0.05 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       439 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

Here comes the mysqlbinlog dump of mysql-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111221 14:56:13 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.12-log created 111221 14:56:13 at startup
ROLLBACK/*!*/;
BINLOG '
3TnyTg8BAAAAZwAAAGsAAAAAAAQANS41LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdOfJOEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#111221 14:56:59 server id 1  end_log_pos 203   Query   thread_id=8     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1324497419/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C cp850 *//*!*/;
SET @@session.character_set_client=4,@@session.collation_connection=4,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table rolandotable (a int)
/*!*/;
# at 203
#111221 14:58:24 server id 1  end_log_pos 271   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
BEGIN
/*!*/;
# at 271
#111221 14:58:24 server id 1  end_log_pos 369   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1324497504/*!*/;
insert into rolandotable values (1)
/*!*/;
# at 369
#111221 14:58:24 server id 1  end_log_pos 396   Xid = 99
COMMIT/*!*/;
# at 396
#111221 14:58:29 server id 1  end_log_pos 439   Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Most lines have /*!*/ as a delimter between entries. Sometimes, a special directive needs to executed between commands, such as this tag: /*!\C cp850 *//*!*/;

That directive, \C, sets a character set with mysql client for processing multibyte charsets within binlogs. I learned that from the help command in mysql client:

mysql> \?

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

There are still other directives, such as the first two lines:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

The first line works as of MySQL 4.0.19

The second line works as of MySQL 5.0.3

If you run mysqlbinlog and import that output to a machine running MySQL 4.x, the second line is ignored.

I also addressed this type of directive from the output of mysqldump concerning stored procedures. That way, If you import a MySQL 5.0 mysqldump into 4.x, all commands to create stored procedures are completely ignored.

This is the agenda for using /*!*/


How to restore a SQL Server 2008 R2 database

If the SIDs change on re-imaging then SQL Server will regard them as new Windows logins. They'll have to be dropped and re-added: as you are doing.

You can test for changed SIDs by using SUSER_SID('domain\somelogin').
The results should b e different before and after.

What you can do is to use a Windows Group instead. So the Windows group contains the 2 users. SIDs will change but you only have to drop and re-add the single group.

Now, you may be able to use a start-up stored procedure to test for SID differences using sys.server_principals and SUSER_SID. If different for a given name you can automate the DROP LOGIN, CREATE LOGIN, ALTER USER

Note: "logins" at the server level are expected to be static. SQL Server provides tools to remap a database-level user to a login via ALTER USER


Set database offline with rollback immediate takes an hour to complete

I believe what you are seeing is completely normal.

  Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures

Why do you do this ?. This is not the correct thing to do always and this is precisely why you are facing the issue. You can take backup of the database when it is online and this is how you should backup SQL Server database. The full backup which you will take when database is online would include all the committed changes done to database when backup finishes. Keeping in single_user and restricted_user mode before the activity may not solve your problem completely.

  This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete.

You also said that there is no long running query, for that I would say triple check. Alter database requires exclusive access on the database and it may be blocked by some other query, have you checked that.

Did yo checked for any transaction running with begin transaction and may be have missed commit. Run DBCC OPENTRAN() to see for any open transactions. Errorlog would have more information on this

The other thing I see is sometimes when you run alter command there are hardly any queries running which needs rollback and so the command finishes quickly while other days there are some command running which needs to be completely rolled back before database can be made offline. Depending on amount of rollback that needs to be done the time varies.

I would not suggest you to put database in offline mode, I do not see any use in this.


Aggregate function / query in Oracle 11g

Simple, but "dirty":

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 3 as id, 'Y' as received from dual
)
select min(max(received)) from t group by id;

M
-
Y

Change input (e.g. change id to 4 in last line):

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 4 as id, 'Y' as received from dual
)
select min(max(received)) from t group by id;

M
-
N


You'll often want a lot more control over that than WSUS offers. For example, you may have certain servers that you want to patch during maintenance windows. You may also want to be very careful about how you patch clusters, servers participating in log shipping, mirroring, replication, etc.

While they certainly exist, I don't personally know of any shop that uses WSUS to patch SQL Server. That doesn't mean it's not a good idea, but combining the variables above with the fact that there are only so many public updates to SQL Server that propagate to Windows Update in any given year, I just don't know how much you'll really gain by trusting it to do its thing.


the answer provided at the bottom of that page states that F1 is not equivalent.

That is correct. F2 and F3 are equivalent but F1 is not.

F2 is equivalent to F3 because from A → C we can infer that AB → C (therefore F2 => F3)
and from A → B, AB → C we can infer: A → AB and then A → C (therefore F3 => F2).

You could also prove that F1 => F2 (and F1 => F3) but not F2 => F1.

A counterexample should convince that there are cases where F2 (A → B, A → C) holds but not F1 (B → C):

 A   B        C
----------------------
 1   Bill     Clinton
 2   Hilary   Clinton
 3   Bill     Murray


Create a view that summarize vehilce owned from two tables cars and bikes

As far as you should not provide information enough about your table schema, I've improvised this one:

create table _owner(_name text, _ssn text);
create table _cars(_ownername text, _owner_ssn text, _model text);
create table _bikes(_ownername text, _owner_ssn text, _model text);
insert into _owner values
  ('me' ,'1'),
  ('you','2');
insert into _cars values
  ('me', '1','Ferrari Modena 360'),
  ('me', '1','Porsche Macan'),
  ('you','2','peugeot 205');
insert into _bikes values
  ('me', '1','Specialized Tarmac'),
  ('me', '1','Pinarello Dogma'),
  ('you','2','Scott Silence');

You can use COUNT and GROUP BY to get your desired result:

select o._name, o._ssn,
       ((select count(*) from _bikes where _bikes._owner_ssn = o._ssn)
       +
       (select count(*) from _cars where _cars._owner_ssn = o._ssn)) as total_cars_and_bikes
from _owner o
;

+-------+------+----------------------+
| _name | _ssn | total_cars_and_bikes |
+-------+------+----------------------+
| me    | 1    | 4                    |
+-------+------+----------------------+
| you   | 2    | 2                    |
+-------+------+----------------------+

Check it here: http://rextester.com/UZMF16252


I had the primary and arbiter configured successfully but then for some reason my primary has become the secondary

The issue is that electing or maintaining a primary requires a strict majority (n/2+1) from your configured voting replica set members. A replica set with only two voting members does not provide any fault tolerance for a primary since votes from both members are required to achieve a strict majority.

Since your arbiter is currently "not reachable/healthy" from node2's perspective, node2 no longer has enough votes to remain primary and will step down to become a secondary.

The best way to resolve this would be to fix the communication issue between node2 and your arbiter before adding a third member.

Forced reconfiguration

  rs.add( { host: "node1:27017", force: true } )

The force option is only available with rs.reconfig(), so would be ignored in this usage example. If you pass a document as the host parameter for rs.add(host), any options (such as priority or tags) only apply to that individual replica set member.

If you can't get your arbiter back online for some reason, you could follow the forced reconfiguration tutorial to remove the arbiter from your replica set config.

As noted in the tutorial, forced reconfiguration is intended to be used as a last resort rather than routine admin:

The `force` option forces a new configuration onto the member. Use this procedure only
to recover from catastrophic interruptions. Do not use `force` every time you reconfigure.
Also, do not use the `force` option in any automatic scripts and do not use `force`
when there is still a primary.

  I'm guessing now it matters what order you add the DB servers and the arbiter

The order isn't significant aside from adding an initial data-bearing node so the replica set has a primary. However, you do need to ensure you have a majority of voting members online and able to communicate with each other.


Restore rman backup on another server with a higher Oracle release

The data dictionary versions/contents won't match. You would need to restore to the same version and then do an upgrade of the restored database. Either that or create an empty database in 9.2.0.8 and use exp/imp to migrate just your data and other application-specific content, rather than RMAN.


You want to avoid any inconsistency in prices - and you have already identified this in your question.

I'd say use a "Pricing" table which stores Supplier-Parts(PK) and Prices(Float or Decimal value etc).

That way, each part from a different supplier has a different price. In other words, the price depends on what the Part is AND who the Supplier is (Supplier-Part is the Primary Key or Unique Index).

The Purchases table should just include records from this new "Pricing" table - which links to all the info from Parts, Suppliers and Prices.

Makes sense?

EDIT: I just noticed. You are relating Parts and Suppliers again in your Purchases table, when you already do it in your Sells table.

Just have these 4 tables:

Parts: PartID (PK), PartName
Suppliers: SupplierID (PK), SupplierName
Pricing: (PartID,SupplierID) (Unique Index of these two Foreign Keys), Price
Purchases: PurchaseID (PK), (PartID,SupplierID) (Foreign Key), Quantity


Alternative defaults with a different table name

Found a clean solution thanks to a_horse_with_no_name for the idea and the syntax example set in this answer

CREATE VIEW tbl_view AS
    SELECT * FROM tbl;

ALTER TABLE tbl_view ALTER int_col SET DEFAULT nextval('another_sequence');


Oracle CTE to SQL Server Using SSIS OLE DB Source

I had the same problem, which I managed to resolve by updating the Oracle driver.

My environment:

SSDT 11.1.20627.00 running on Visual Studio 2012 Update 4
Oracle OLEDB driver 11.2.0.1 - this version was not working with CTE

I updated the Oracle OLEDB driver to 12.2.0.1 and the error disappeared. I can now use CTE's in the packages:

The Oracle OLEDB driver is part of Oracle Data Access Components package, which can be downloaded from Oracle (32-bit version, 64-bit version).

Tip 1: To find your current Oracle OLEDB driver version, locate the OraOLEDBnn.dll (where nn is a number, eg 11, or 12) file in the Oracle directory. Open file properties and look for File version under Details tab.

Tip 2: If you have a mess in the Oracle directory (eg multiple directories; old versions present), use the NirSoft RegDllView tool to show a list registered DLL's. The separate 32 and 64 build versions of the tool will show 32 and 64 bit DLL's, respectively.


In most cases, leave the default option (0).
Assuming you have default options, layman's version of the article you referenced:

Remember that SQL Server has a fixed page size of ~8K which means you cannot physically store more than ~8k per column in-row (it's possible to have bigger than 8K total rowsize but that's a different topic)
BLOB types support up to 2GB but actual usage is usually much lower, a recent investigation into found over 90% of databases we probed (using scripts) to have BLOB cells smaller than 2K.
If your total rowsize including BLOB data is less than the 8k max, SQL Server will try to store the whole thing (BLOB and other data) in the same row as the rest of your data for that row (I think this was introduced in 2008 or perhaps 2005, can't remember for sure)
If your total rowsize including BLOB data is greater than 8K, SQL Server will store the BLOB data in a separate location from the other data in your row. This other location is generically referred to as LOB storage space. This is highly simplified but just grok the concept for now.
If the BLOB is less than ~40K and you have space in the row for a 72b pointer string, SQL Server will store the string of pointers for the BLOB (sitting somewhere else) in the same row as the rest of the row data. This saves a bunch of things during insert and future access.
If the BLOB is more than 40K, SQL Server needs to store more pointers for the BLOB so instead of consuming lots of space in the row, it stores a shorter (24b) pointer string that points to a new page. That new pages stores as many pointers as needed for the BLOB. Don't know exact reason for picking 5 pointers specifically (for the 5x ~8K pages= ~40K) but the goal is to balance optimizing perf for the 80-90% of BLOB sizes and row space usage so there's a "reasonable" number of rows per page.
When accessing data, less movement is good, fewer operations is good, opposites are bad. SQL Server optimizes for this. For example:

if your BLOBs are small, you can read the entire row or multiple rows from a single request (however many rows fit into an 8K page, and however many pages you need in a 64K extent)
If your BLOB is bigger than the space available in-row but less than ~40K and the row has room for the pointer string, SQL Server can get all the pointers for the BLOB directly from the row and retrieve the BLOB with them.
If your BLOB is greater than ~40K or the row does not have room for the pointer string then SQL Server retrieves a pointer to the root node of a pointer tree on a different page, read that, then retrieve the actual BLOB chunks.

If that made sense then check out a really nice write-up on BLOB storage http://sqlmag.com/t-sql/varbinarymax-tames-blob


A full database backup contains almost no transactions - just the transactions that happened while the FULL transaction log was being created.
The LDF file created when a database is restored using a full database backup is almost empty, regardless its size. It should be inflated (with no data but active portion of transaction log) to its backed up size when restoring.

If you want to restore the transaction log file too, the best way to do that is to restore the database using its LDF and LDF files (attach them), instead of using a full database backup.

There are many reasons why the original file is huge:

The database is in FULL recovery model and you don't create transaction log backups at all
The database is in FULL recovery model and you don't create transaction log backups often enough
there are large-scale index operations

For any of the possible causes, creating transaction log backups regularly should keep the size of the LDF file.


No, pgAdmin cannot edit pg_hba.conf

Unfortunately, there is no mechanism by which pgAdmin can edit a pg_hba.conf file.  The reason why it's possible to edit postgresql.conf via pgAdmin is that the ALTER SYSTEM syntax is something that can be entered into a client interface.  Therefore, pgAdmin was able to "edit" postgresql.conf because it could send an ALTER SYSTEM query to the database.  No such syntax exists for editing access control.


Mongodb - Best Practice for TTL index on huge dataset

The rolling index build procedure is recommended. Here is what they say in the doc:

  To minimize the impact of building an index on replica sets and sharded clusters, use a rolling index build procedure as described on Build Indexes on Replica Sets.

for more info:

https://docs.mongodb.com/manual/tutorial/build-indexes-on-replica-sets/
https://docs.mongodb.com/manual/tutorial/build-indexes-on-sharded-clusters/
https://docs.mongodb.com/manual/core/index-ttl/
https://docs.mongodb.com/manual/reference/method/db.collection.createIndex/#db.collection.createIndex


Informix - dbaccess vs dbexport to export tables to text files

Using dbexport has similarities, advantages and disadvantages compared to dbaccess.

With DB-Access, you are presumably generating the list of UNLOAD statements that will copy the data to file.  You probably don't lock the entire database, so changes can occur while you're unloading the data, which could lead to inconsistencies.

If you use DB-Export instead, then it automatically locks the database (so the unloaded data will be consistent), and generates both the schema and unloaded data files using the same format the DB-Access generates.  The advantage is you don't have to work so hard.

The other part of the game is DB-Import (dbimport).  It expects to create a database (you can choose the name) and load it from the exported data.

Neither DB-Export nor DB-Import has support for parallelism; both operate on one table at a time.  With DB-Access, you could arrange for de facto parallelism (by using multiple invocations of DB-Access to unload or load the data into multiple tables).

With DB-Access, you can control which tables are exported.  With DB-Export, all tables are exported.

With DB-Export and DB-Import, you don't have to fret about creating and loading the tables in a correct sequence; with DB-Access, you have to think about ensuring that the data is loaded in a correct sequence.

On the whole, DB-Export plus DB-Import is simpler than using DB-Access, but DB-Access is ultimately more flexible.


update table column if provided text is not matched

You can update all your table with query:

UPDATE table
   SET value = IF(merchantDetails = "Amazon.com", "AMZ", "FBA");

where table and value should be changed to the actual names of table and field.


Install Oracle 10.2.0.4 on Windows Server 2012 R2

With no success with installing Oracle 10.2.0.4, we've tried 10.2.0.5.  The installation worked without any problem.


Use the case statement

select name, age,
 case show_email when 0 then email else null end
from my_table;


What is blocking and how does it happen?

Analogies

Sometimes it helps to use analogies away from computers.

Let's say you have a ball and two children.  Only one child can have the ball at any one time.  However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.

The other child is blocked from that resource.

If we compare this to the TV, for example, several children can watch TV at any one point.

Locks

If we move over to the database world, we see that there are different ways to use resources (just like our two examples above).  We can perform "reads" or we can perform "writes".

When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV.  However, if we want to write the data, then we need to make sure that no one else is looking at it.  If they are reading it while we're writing it, they will get "dirty" reads.  (Meaning, they'll see the data partially written out, which will be invalid.)

In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.

Read Lock

You can have several different connections reading from the same datasource at any given time.  But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data.  Others can, however, take out Read Locks of their own on that same piece of data.

Exclusive Lock

If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock.  This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).

When a connection has an exclusive lock on the data, no other connections may read from the data.  This helps prevent dirty reads by insuring that no one can read the data while its being written.

Blocking

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it.  It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Compare this to the case with a child holding the ball.  The child holding the ball is blocking all other children from holding the ball.

Deadlock

I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).

Deadlocks can happen when you have two connections that each have a lock, but they want each others resource.  In this scenario, it's like two children that each has a ball, but wants the other's ball.

Like children, these connections are not willing to share at all.  Each connection needs access to both of the resources in order to continue.  However, they are in a state of permanent blocking.  In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

So, the concept of a deadlock is where you have two resources that are blocking each other.

Here, you can read more about all the different types of locks SQL Server has to offer and the different resources that can cause blocking/deadlocks.  The article is old, but it still applies for SQL Server 2000 through 2008 R2.  (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)


You could limit the number of connections per hour per user. Restarting mysql would not be necessary for this.

For example, suppose all your web servers connect to mysql from the 10.1.2.% netblock. You should already have a user in mysql.user named something like 'myuser'@'10.1.2.%'.

To set the maximum number of queries per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_questions = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of updates per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_updates = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of connections per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_connections = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

For more clarification on this, please see MySQL Documentation.

BTW once you are done with the maintenance window for throttling user connections, simply set the values you changed back to zero(0) to remove the limits as follows:

UPDATE mysql.user SET
    max_questions = 0,
    max_updates = 0,
    max_connections = 0
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;


distributed queries are configured to run in single-threaded apartment mode

As Mark has mentioned in the comments, you are running into a bit-level mismatch between driver sets.  You will need to either install a 64-bit installation of Office (specifically Excel in this case) or install a 32-bit installation of SQL Server and import the data to that instance.


Log files in question are named ERRORLOG, ERRORLOG1 etc. and are usually stored in LOG folder somewhere inside MSSQL installation directory. In the beginning of the log there is a message like this:

  Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\LOG\ERRORLOG'

It can help to determine the exact path.

If the need to delete these logs is caused by a security concern, it's good to execute sp_cycle_errorlog proc to make log cycle. This way the ERRORLOG file will be empty and all the events will be in ERRORLOG1, ERRROLOG2 etc which can be deleted at any time.


How to prevent deadlocks in many-to-many insert/update trigger function?

This might require careful examination, knowledge of the complete situation and more time than I can spend now. Or maybe I am missing something obvious. Whatever else might go wrong here, some things stand out:

Remove the FOR UPDATE SKIP LOCKED completely. It makes no sense where you use it. While selecting from the CTE, which holds rows with exclusive lock already, that makes no sense. It also would not make sense to skip any rows at this stage of the query.
COST 1 is misleading. The default is COST 100 and your trigger function is more in the realm of COST 5000. Leave the default or set it higher. Probably unrelated to the deadlock.
An AFTER trigger might be more susceptible to deadlocks than rewriting the whole workflow with a single query (with multiple data-modifying CTEs).
My shot in the dark: the deadlock is caused by the FK constraint trying to take a ShareLock on url_starting while concurent transactions try to modify the same row after having taken a similar ShareLock themselves and vice versa. A quick and dirty solution might be to drop the FK constraint tweet_x_url_starting if you can afford that. You could at least try that to verify it's part of the problem.

If you want to continue your learning spree - here is one more that seems relevant:

How to use RETURNING with ON CONFLICT in PostgreSQL?


Unable to connect to the Reporting Services instance through SSMS with windows authentication

Since you have access to the server i'd reccomend you log in and use Reporting Services Configuration Manager to change whatever settings you want. If you cant do it via config manager then you may need to edit the config files.

From memory to access SSRS via SSMS requires permissions on the service. Not the database.


Grant privilege to see column names in null constraint violations when inserting in views

It seems that granting the privilege references allows the user to see the column names:

grant references on the_table to app_user;

The grant needs to be done for the base table not for the view.


Query exceeding 16384 bytes, skipping. Probably a bulk insert query

It has been confirmed by severalnines that the message is just cmon does not analyze those queries because of their sizes.

http://support.severalnines.com/entries/80914809-cmon-Query-exceeding-16384-bytes-skipping-Probably-a-bulk-insert-query-?page=1#post_29095145


Query to get all schemas in a database in PostgreSQL?

select * from information_schema.schemata;

Have a look at Postgres Docs:

  The view schemata contains all schemas in the current database that the current user has access to (by way of being the owner or having some privilege).

|                          Name | Data Type      | Description                                                                        |
|------------------------------:|----------------|------------------------------------------------------------------------------------|
| catalog_name                  | sql_identifier | Name of the database that the schema is contained in (always the current database) |
| schema_name                   | sql_identifier | Name of the schema                                                                 |
| schema_owner                  | sql_identifier | Name of the owner of the schema                                                    |
| default_character_set_catalog | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| default_character_set_schema  | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| default_character_set_name    | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| sql_path                      | character_data | Applies to a feature not available in PostgreSQL                                   |


I would use Decimal(4,2) if you are confident that 99.99 is the max value.  Decimal(4,2) will consume 2 bytes while decimal(5,2) will consume 3 bytes but give you a safety net if the value were to exceed 99.99; it's up to you how confident your data set will stay under 100.


Can I remove the middle slave in a MySQL "master -> slave -> slave" setup?

Add additonal step to avoid any confusion you could do flush tables with read lock after replication is caught up as it will do global lock and would avoid conflict in case of any change coming up, use unlock tables at the end to release the lock acquired. Moreover it would be better to have this done in low/off production time in case it is in production, other steps seems to fine

Hope it helps.


What is the safest way to run SSIS on Multiple Named SQL Server Instances?

I dont see anything wrong in your setup. To be fair, this is the correct way of setting up SSIS in a clustered environment.

Read up SSIS and clustering: What you should do instead

Also for the login failure, its worth to check  the Windows Server 2008 R2's firewall rules  to allow an inbound rule for either the msdtssrvr.exe executable or the DCOM port SSIS listens on.


This is called parameter sniffing, and it's one of the hardest problems to solve in a database.

The basic problem is that data is skewed in a way that makes the cost based optimizer choose different plans for returning your data.

There are a lot of ways people think they "fix" parameter sniffing:

Recompile hints
Optimizer for unknown
Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

Indexes used
Join order
Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

Lookups
Sorts
Join type
Memory grants (and by extension, spills)
Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting. If you want more help, please share the query plans, along with the code, table and index definitions.

Otherwise, good luck!


Another "log_min_duration_statement is ignored" question

Check individual users and / or dbs for settings that override server settings for logging:

smarlowe=# alter user smarlowe set log_statement='all';
ALTER ROLE
smarlowe=# alter database smarlowe set log_statement='all';
ALTER DATABASE
smarlowe=# \drds
                   List of settings
   Role   | Database |            Settings
----------+----------+---------------------------------
 smarlowe |          | log_statement=all
          | smarlowe | auto_explain.log_min_duration=1+
          |          | log_statement=all


The problem is that the IF statement cannot contain a set, it needs to be a scalar. Let's illustrate with:

create table T (x int not null);
insert into T (x) values (1),(2);
-- invalid since `SELECT x FROM T` is a set of rows
SELECT * FROM T WHERE 1 IN ( IF (1=1, SELECT x FROM T, 1) );

Fiddle

Let's see if your query can be reformulated (I changed it slightly to make it more readable):

SELECT *
FROM txt_campaign_message cm
LEFT JOIN txt_campaign c
    ON c.id = cm.campaign_id
LEFT JOIN txt_message m
    ON m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs
    ON cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2
  AND m.message_type = blast
  AND c.active = 1
  -- AND cs.subscriber_id IS NOT NULL, redundant and can be removed
  AND cs.subscriber_id IN (
    IF (cm.date_range_from IS NOT NULL
       , (SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
         )
       , cs.subscriber_id
       )
  )
  AND cm.campaign_id = 1386;

This is the problematic part:

AND cs.subscriber_id IN (
    IF (cm.date_range_from IS NOT NULL
       , (SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
         )
       , cs.subscriber_id
       )
  )

Two cases:

a) cm.date_range_from IS NOT NULL

AND cs.subscriber_id IN (
       SELECT cs1.subscriber_id
       FROM txt_campaign_subscriber cs1
       WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                     AND DATE(cm.date_range_to)
)

b) cm.date_range_from IS NULL

cs.subscriber_id = cs.subscriber_id

i.e. TRUE

This is equivalent to:

(
AND cs.subscriber_id IN (
       SELECT cs1.subscriber_id
       FROM txt_campaign_subscriber cs1
       WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                     AND DATE(cm.date_range_to)
    )
AND cm.date_range_from IS NOT NULL
)
OR cm.date_range_from IS NULL

All and all we end up with:

SELECT
    *
FROM
    txt_campaign_message cm
LEFT JOIN txt_campaign c ON
    c.id = cm.campaign_id
LEFT JOIN txt_message m ON
    m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs ON
    cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2
  AND m.message_type = blast
  AND c.active = 1
  AND (
      cs.subscriber_id IN (
          SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
        ) AND cm.date_range_from IS NOT NULL
      OR
      cm.date_range_from IS NULL
  )
  AND cm.campaign_id = 1386;

Totally untested, but it should give you an idea

You may also consider rewriting the in-predicate as a JOIN


Please read the replication documentation before you go into production. Make sure you have understood them.

As per your questions:

You don't need config servers, as they are only needed for sharded environments
You don't need mongos instances, as they are only needed for accessing shards.
You only need an arbiter if you have an even number of data bearing replica set members, as the elections for a primary need a qualified majority with a quorum of 50% to elect a new primary.

Again, please read the docs. As you are obviously inexperienced with running MongoDB, it might be worth to have a look at the a MongoDB provider such as MongoLab or Compose (former MongoHQ). They are not necessarily more expensive (quite the contrary in some use cases) and you don't need to administer and monitor MongoDB yourself. Disclaimer: As a MongoDB DBA, I do not have first hand experience with either of them.

Some notes on running a non sharded environment: Please keep in mind that the space an EBS volume can provide is limited. So if there is any chance that you exceed 1TB of data (including indices, oplog and journal), you should think of using a sharded cluster from the beginning in order to prevent the need of interrupting your services when the need of scaling arises.


Why is MySQL caching data on our dynamic PHP site?

Nothing that MySQL does will lead to stale data -- not the Query cache, not SQL_CACHE, not the key_buffer, not the buffer_pool.  The problem must be outside MySQL.


alter primary id field to uniqueidentifier GUID as default in sql server

every tables of my database has id primary key column and I want to change them to nvarchar(36) with default newID()

No. You do not want to make that change as no good can come of it.
You currently have a PK (Clustered, I assume) that is:

Compact: 4 bytes per row
Efficient: comparisons are simple 4-byte-value-to-4-byte-value (i.e. a binary comparison)
Minimally Fragmenting: new values are sequential and added to the end of the table.

You are wanting to change this to a Clustered PK that is:

Wide: 72 bytes per row (GUID is 16 bytes, which is 32 characters in string form for the hex values plus 4 dashes for 36 characters total, which is 72 bytes in UTF-16 / NVARCHAR).
Inefficient: If using UNIQUEIDENTIFIER it is not so bad as that is still a binary comparisons, just like with INT, but is 16 bytes unlike INT which is 4. Storing this as a string is now a 36 character comparison (36 bytes in VARCHAR and 72 bytes in NVARCHAR) which is slower than the 16 byte UNIQUEIDENTIFIER. Finally, most people storing GUIDs as strings forget to use a binary Collation (e.g. Latin1_General_100_BIN2) to at least make the comparisons byte-by-byte since linguistic rules aren't necessary. Using a case-insensitive Collation, or even a case-sensitive one, is definitely slower as it will apply locale-based linguistic rules.
Highly Fragmenting: new values are all over the place, leading to greatly increased page splits. And if you decrease FILLFACTOR to reduce the number of page splits, you are also reducing the performance of the index as it is spread out over a larger amount of pages.

Please keep in mind the down-stream negative effects of this change due to:

Clustered index keys are copied into non-clustered indexes. Assuming, again, that this PK is Clustered, then each non-clustered index on this table will have that 72 byte value copied into it. Three non-clustered indexes on this table is 72 bytes * 3 = 216 bytes plus the original 72 of the Clustered index = 288 bytes total. On the other hand, the current INT would only be 4 bytes * 3 = 12 bytes plus the original 4 = 16 bytes total. Per row.

PKs are often used by FKs, which is the PK copied into one or more other tables. 72 bytes per row is just for this table. If this PK is used in 2 other tables as a Foreign Key, then that is 72 bytes * 2 = 144 extra bytes. On the other hand the current INT would only be 4 bytes * 2 = 8 bytes.
Is the FK column indexed? If yes, then that is another 72 bytes instead of 4.

Data pages are loaded into memory (i.e. the Buffer Pool) before their row(s) can be read and used. Larger rows and/or lower FILLFACTOR means more data pages are required to hold those rows. That means more time to read them from disk into memory, and obviously the more memory they will require. This need competes with other queries, plan cache, etc.

If you need to have a GUID to have a value that is know to external systems, just add a UNIQUEIDENTIFIER column and index it; you can then look it up to get the INT value to use for all other JOINs, etc. And, if it has to be stored as a string, then use VARCHAR given the only characters are A, B, C, D, E, F, and - (NVARCHAR is a completely unnecessary waste of space) and be sure to specify a binary Collation (one ending in _BIN2).
But don't change the current structure.

Regarding the update to the question in which it was clarified that the intended datatype to change to is really UNIQUEIDENTIFIER and not NVARCHAR(36): the overall advice does not change from "do not do it". While UNIQUEIDENTIFIER is a better choice than NVARCHAR(36) (smaller and comparisons are binary), it is really just a "less bad" choice more than a "better / good choice".


How do I index two table having a one-to-many relation but only the latter is being frequently updated?

The thing about indexes are that they are excellent tools for speeding up queries, but they bring along overhead as well.

If you choose to index every column in a table that is involved in a query, you have chosen to store considerably more data.  This has an effect on the storage required and in the overhead of maintaining all those indexes.  (Or maintaining all columns in a very wide index.)
In past experience, having many indexes, especially several indexes with similar distribution statistics, can make it easier for the server to select a poor index and thus a poor plan.

My experience shows that indexing just about everything will hurt your performance overall, for the reasons mentioned above.

Both table A and table B should only contain the indexes that would be of best use for your queries.

Of course, your first indexing decisions may need to be refined over time as you learn more about your database's behavior.  Do not be afraid to add additional indexes when needed.  And do not be afraid to drop an index that is not being useful.


Why does this 120 GB insert grow the log by over 780 GB?

The log contains an exact description of every inserted record. That is at least 120GB, but must be multiplied by some factor for additional info. 1.5x is a good estimate => ~180Gb. Every record must reserve in the log space for compensation on rollback. 1:1 ratio between do and undo is a good estimate => ~360Gb. Now 780Gb is more than double this estimate. There could be explanations for such high size, like any other concurrent activity in the DB. You can use fn_dblog to investigate how exactly the log is used, if you must know.

Remember that SELECT ... INTO ... FROM ... is an operation that can be minimally logged but INSERT ... SELECT ... FROM ... is actually not, unless the special trace flag 610 is used, see Minimal Logging changes in SQL Server 2008 for details on all requirements to achieve minimally logging with INSERT ... SELECT.


Update with rolling sum grouped by another column

You will need some defined ordering of your rows, otherwise, you will end up with a different CUMULATIVE SUM every time you run your update. I added an auto-increment column, but you likely have a timestamp or similar you can use:

create table T
( seqid int not null auto_increment primary key
, user_id int not null
, total int not null
, delta int not null
);

insert into T (user_id, total, delta)
values (1, 0, 30)
     , (1, 0, -10)
     , (2, 0, -5)
     , (2, 0, 10)
     , (2, 0, -10)
     , (3, 0, 30);

Window functions were introduced in SQL99 some 20 years ago, but only recently made it into MySQL. They are very handy for this kind of task:

with TT as (
    select seqid, user_id, delta, total
         , sum(delta) over (partition by user_id order by seqid) as new_total
    from T
)
update T, TT
    set T.total = TT.new_total
where T.seqid = TT.seqid;

For some reason I could not get the update to work with an ansi join, it is left as an exercise for the reader;-)


It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well.

ALTER TABLE sales_details
ADD FOREIGN KEY fk_product_code(product_code)
REFERENCES products(product_code)
ON DELETE CASCADE;

But if you'd like to use a trigger to delete rows from the products table that have the same product_code as the row deleted, this should do the trick:

 delimiter //
 CREATE TRIGGER removeforeing
  BEFORE DELETE ON products
  FOR EACH ROW BEGIN
    DELETE FROM sales_details WHERE product_code = OLD.product_code;
 END//
 delimiter ;

Edit

Per documentation,

  Within the trigger body, you can refer to columns in the subject table
  (the table associated with the trigger) by using the aliases OLD and
  NEW. OLD.col_name refers to a column of an existing row before it is
  updated or deleted. NEW.col_name refers to the column of a new row to
  be inserted or an existing row after it is updated.


Database design for Time Sheet adopting different weekend

You don't say so explicitly, but I think maybe your concern is that you want to have a timesheet that has one column per day and you don't know what to do about inconsistent weekends.

If that's the case, then you're approaching it incorrectly.  Don't have columns for days.  Have rows in a details table for days.

Your timesheet should use individual entries at a date level, assuming you only want one entry per day.  If you want multiple entries per day (per worker) then have one entry per date and start time.

For weekends you keep a separate table for working days (of the week) per worker.

Consider something like this:

Note that the WEEKDAY and TIMESHEET_STYLE_DETAIL tables (in blue) are a bit contrived. You could these tables, but really in practice I think a lot of people would probably use something like a bit flag array in the TIMESHEET_STYLE table containing a flag for each day of the week instead.

Note however that some of your SQL queries would actually be simpler if you did have an explicit WEEKDAY table and an intersection table (TIMESHEET_STYLE_DETAIL) that contained a row for each day of the week.  This way SQL functions that convert a date into a day of week number could be used as part of joins against this table.


Table compression on 500GB table using Enterprise edition with online=on

Agreed with everything sepupic said, especially reinforcing the fact you won't be saving space on backups if you're using standard compressed backups anyways.
I also wanted to specifically add you aren't guaranteed to save Memory either. Compression is a way to save space on Disk. When the data is loaded into Memory from a compressed table, it may first use CPU to decompress the data before loading it into Memory. Visa-versa when writing to a compressed Table as well (the CPU runs the compression algorithm on the decompressed data in Memory and hardens it to the compressed data on Disk). (This isn't always the case though, and in some cases it does stay compressed in Memory as well.)
Basically data compression is great for reducing how much Disk space is being consumed by a particular Table, and it helps reduce I/O bottlenecks from the Disk itself (which is usually the slowest piece of the hardware pie). That's about it for its main uses.
You can read more about this in this great article Compression And Its Effects On Performance.


I'm going to suggest the following so that you can 1) Share the entire DB definition with developers and 2) allow them, or you, to recover after they mess it up.

Download SSDT and create a database project. Then import your database and remove any configuration/settings/accounts/etc. that shouldn't be seen. You can now simply Publish the database (or just share the visual studio project!) to any server with no data included for your developers to work with. They can populate it with fake data for dev/testing as needed. If you need to provide XML schemas then include that along with your other development requirements.

Developers are smart... if you truly don't want them to have the data then don't give it to them. Even developers with access to production data sets will often generate test data for various reasons, not least of which is that production data may not fulfill test cases.


How to find out which object is taking space?

It appears that your database data file has empty space.

If you know for sure that the data file will not grow out again, you could shrink the file.

If you have to shrink, the cleanest way is doing this with TRUNCATEONLY so you don't move any data pages and only release the space back to the OS if it is possible.

You can get the data file(s) with this query, change the databasename to your database.

SELECT [name]
FROM sys.master_files
WHERE database_id = db_id('DatabaseName')
AND type_desc = 'ROWS';

Afterwards you could try shrinking the data page until 100GB without any movement.

Use [DatabaseName]
GO
DBCC SHRINKFILE ('name',102400 ,TRUNCATEONLY);
-- try to shrink until 100GB, without moving data pages

You can read up on shrinking date files and...

why it sucks here
why it's bad for performance here


When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?

Assuming we're talking about a full or differential backup, a marker is placed at the start of the backup process. At the end of the backup process, any transactions that committed during the process are rolled forward into the backup. Any transactions that are not completed are marked as rolled back within the backup. So, the short answer to your question is, all completed transactions from the beginning to the end of the backup process. During a restore operation, the final step is the cleanup of these transactions.


SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

On the first run, there are no matches in the conditional check. That is, there are no rows in #MyTable that match the input rows.

Note that the matching in a MERGE does not consider rows inserted during the MERGE itself. If those rows could cause duplication, it is the responsibility of the developer to ensure that there are no potential conflicts in the source data. Since this is the case, and there are no constraints such as a Primary Key to conflict with the data being added, this insert succeeds for both rows.

Of course, on the second run, there are two matching rows, and the MERGE fails with the error you showed.

This is by design, and does speed the processing, as if the MERGE had to validate each row individually as they were considered (as opposed to set based validation performed by the MERGE internally), then the process would be more like using a cursor to handle each row one at a time.

How To Handle Source Duplicates

Using the simple case you have above, I would modify it like this to handle source duplication:

CREATE TABLE #MySource (        -- This represents the actual source of the data in the database
    KeyID1 int,
    KeyId2 int,
    SomeValue bigint
    )
INSERT INTO #MySource

    SELECT
         KeyID1 = 1
        ,KeyID2 = 1
        ,SomeValue = 1
    UNION ALL
    SELECT
         1
        , 1
        , 2

-- Find the duplicates, and set up an error condition / report for them.

    SELECT
        KeyID1,
        KeyId2,
        Count(*) as ct
    INTO #Duplicates
    FROM #MySource
    GROUP BY KeyID1, KeyId2
    HAVING COUNT(*) > 1

    IF EXISTS (Select * FROM #Duplicates)
    BEGIN
        -- handle error here. Perform a Raiserror or THROW, or SELECT
        -- the items from the duplicates and stop processing, or...
        Print 'Error - Duplicates!'
        SELECT *
        FROM #Duplicates
    END

    -- No duplicates, so continue
MERGE #MyTable AS TARGET
USING (
    SELECT *
    FROM #MySource m
    )
) AS SOURCE ON
    TARGET.KeyID1 = SOURCE.KeyID1
    AND TARGET.KeyID2 = SOURCE.KeyID2
WHEN MATCHED THEN UPDATE SET
    TARGET.SomeValue = SOURCE.SomeValue
WHEN NOT MATCHED BY TARGET THEN INSERT
    (
         KeyID1
        ,KeyID2
        ,SomeValue
    )
    VALUES
    (
         SOURCE.KeyID1
        ,SOURCE.KeyID2
        ,SOURCE.SomeValue
    );


Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?

It is not possible to configure Snapshot Isolation (SI) as the default isolation level.

To use SI, the database must be enabled for Snapshot Isolation:

ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then each connection must explicitly request SI, using for example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

It is possible to set the default Read Committed isolation level to use row versioning (RCSI).

If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:

ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON;

Further reading:

Snapshot Isolation in SQL Server (documentation)
How to set the default transaction isolation level server wide? by Nacho Alonso Portillo


Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

I can only reproduce this if mess_201811.loadedtime is nullable, in which case it has to be scanned for NULL values upon attachment.  If the column is NOT NULL, then the scan upon attachment is skipped.


MySQL Master-Slave setup: master is not indexed and slave is indexed

yes.

But 1) you have more trouble later on when doing maintenance on the tables (replicating alter statements might caue issues) and ypu might have issues when havin primary/unique/foreign keys on slaves only as it could reject data the master accepts

And 2) Slaves still have to insert the data and build indexes, yes the maser has a tiny bit less work, the overall system not, though.


How to get same column values to make multiple columns?

So grouping by emp_id, month and suming the count based on atten_type?

SELECT emp_id,
    SUM(IF(atten_type='present',1,0) as 'atten_type as present',
    SUM(IF(atten_type='leave',1,0) as 'atten_type as leave',
    SUM(IF(atten_type='absent',1,0) as 'atten_type as absent',
    MONTH(`date`) as 'month'
FROM
    attendance
GROUP BY emp_id, `month`


If you were only talking about accessing the database via PHP and ASP.net, then a stored procedure could serve as a common middle layer. Once you add the android and iphone applications into the equation, you're looking at having to design another middle layer. Reason for that it is most mobile devices don't have database drivers available to them.

In your case, you should build your middle layer in asp.net or PHP as a web service. Most are designing these web services as a REST API. You'll need to think about the information that your mobile apps need and build API endpoints for those things (users, posts, etc). I also recommend making your ASP.net and PHP site/application use the same API your mobile apps do.

By setting up an API like this, you can reduce the work required for your apps. You're mobile app doesn't know that to get the latest 10 posts, it has to query SELECT * FROM posts order by create_date desc limit 10. It just has to make a request to http://myapi/posts/latest.

There are several posts on Stack Overflow and Programmers.SE about setting up Web Service APIs, I recommend reviewing them.


Appropriate column type(s) for dates and dynamic pricing data

For a relational database the correct way is to have a normalized table:

hotel_id         <whatever type>,
collection_date  date,
price_date       date,
price            numeric()

It sounds like you want to "compress" the price values into some sort of array. There are relational database systems which support the array type, PostgreSQL for one. Document stores (Mongo et al) have arrays as a natural part of their JSON support. Time series databases are optimized to store a changing metric (the room price) over time, by key (the hotel ID).

Many relational databases support compression and column-wise storage (Vertica, Teradata et al). These will space-optimize the storage of the values that are shared by many rows. This optimization is transparent to the application, which sends and receives rows oblivious to the DBMS's internal processing.


Find out how many connections are there to the database vs how many available.  This command in Mongo Shell will tell you exactly that:

db.serverStatus().connections


How to get rid of a Bitmap Heap Scan if the table already has the proper indices

The "culprit" here takes up less than 1/4 of the total time.  The real bottleneck is Index Only Scan using instagram_categories_username_category_agg, which takes 0.200 * 118453 = 23690.6 ms, almost 24 seconds, which is most of the time.

It looks like every user has exactly one category, unless that is just an amazing coincidence, so why is there a separate table for user category rather than it being an attribute of profiles_1000?  That design appears to be the true culprit.

Anyway, the reason it has to do the Bitmap Heap Scan is because that is how it arrives at the correct answer.  If it only did the Bitmap Index Scan, it wouldn't have any data about the matching rows, just their address.  And it also wouldn't know if that address was really to a matching row, because of visibility, rechecks, and lossy bitmap compression.

Regular index scans also visit both the index and the table, they just don't separate out those operations as two different entries in the EXPLAIN plan the way a bitmap scan does.


how to grant permissions to all the synonyms inside a view?

Cross-database permissions are quite tricky. So, similar to my answer here:

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

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
Sign the TVF with the Certificate using ADD SIGNATURE
Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
Remove the Certificate's private key using ALTER CERTIFICATE
USE [master];
Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
Create a Login from that Certificate
Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

If you ever need to change the code within that TVF, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement. While you can extract the public and private keys to VARBINARY hex byte strings using two built-in functions and then create a new Certificate from those VARBINARY strings (starting in SQL Server 2012), you can only restore a Certificate's private key from a .pvk file. Hence Step 3 above to use BACKUP CERTIFICATE as it can be used for both creating the full Certificate in any other DB as well as for restoring the private key into the existing Certificate here.


replication breaks after upgrading master

The mysql_upgrade program tries to realign the mysql.user table with latest grants for that MySQL Major Release. Since you only did a minor upgrade (5.1.30 -> 5.1.47) the mysql_upgrade was needless. The grant tables for the Slave have no bearing on Replication since the Slave has to authenticate back at the Master.

Fixing MySQL Replication is just a case of repointing replication from the last know executable position and starting from there. Here is how you do this:

Run SHOW SLAVE STATUS\G and choose the following two fields

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

From the above display, your would choose mysql-bin.000504 and 326579450.

Simply do the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000504',master_log_pos=326579450;
START SLAVE;

Give it a Try !!!


How to not replicate index creation on ignored tables in MySQL/MariaDB

Plan A:  Include the index(es) in the CREATE TABLE statement.

Plan B:  Use this to add an index:

ALTER TABLE moodle.mdl_temp_stats_daily ADD INDEX(courseid);

(I have not tested either.)


Backup a remote database, from my machine, to another server (SQL Server 2008)

This problem occurs because the network firewall filters Kerberos traffic.

  To resolve this problem, configure the network firewall so that TCP port 88 and UDP port 88 are not blocked for either domain.

http://support.microsoft.com/kb/938457

Sorry for just quoting...

Another thing to check is to make sure that your Service Startup account for SQL Server has the rights to access the file share that you are backing up to.

In SQL Server Configuration in the instance name > Properties > Log On > Log On as Account Type.


How to reject unvalidated SSL client connections from the MongoDB server?

There was an issue opened on MongoDB Jira: https://jira.mongodb.org/browse/COMPASS-3369

TL;DR Compass allows connections with invalid certificates and it won't be fixed.


How to calculate a total by comparing current and next row

Using user variables, you calculate running total for each line like a spreadsheet

PROPOSED QUERY

SET @total = 0;
SET @curr_trx = 0;
SET @curr_con = 0;
SET @lineitem = 0;
SELECT GrandTotal FROM
(
    SELECT
        (@lineitem:=@lineitem+1) line,
        (@prev_con:=@curr_con),
        (@curr_con:=connect_time),
        (@prev_trx:=@curr_trx),
        (@curr_trx:=transmitted),
        (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
        (@total:=@total+@newvalue) GrandTotal
    FROM mytable
) A WHERE line = @lineitem;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS timyash;
Query OK, 1 row affected (0.22 sec)

mysql> CREATE DATABASE timyash;
Query OK, 1 row affected (0.00 sec)

mysql> USE timyash
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     transmitted INT NOT NULL,
    ->     connect_time INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO mytable (transmitted,connect_time) VALUES
    -> (2650131,117987),(6465178,78073),(25905117,159268),
    -> (59178089,410282),(73502942,596408),(75695427,683045),
    -> (77576167,740379);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GrandTotal FROM
    -> (
    ->     SELECT
    ->         (@lineitem:=@lineitem+1) line,
    ->         (@prev_con:=@curr_con),
    ->         (@curr_con:=connect_time),
    ->         (@prev_trx:=@curr_trx),
    ->         (@curr_trx:=transmitted),
    ->         (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->         (@total:=@total+@newvalue) GrandTotal
    ->     FROM mytable
    -> ) A WHERE line = @lineitem;
+------------+
| GrandTotal |
+------------+
|   82876429 |
+------------+
1 row in set (0.08 sec)

mysql>

PROPOSED QUERY EXPLAINED

Look at the subquery

SELECT
    (@lineitem:=@lineitem+1) line,
    (@prev_con:=@curr_con),
    (@curr_con:=connect_time),
    (@prev_trx:=@curr_trx),
    (@curr_trx:=transmitted),
    (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))),
    (@total:=@total+@newvalue) GrandTotal
FROM mytable

If you run this with the variables, you see everything being added up for you line by line

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@lineitem:=@lineitem+1) line,
    ->     (@prev_con:=@curr_con),
    ->     (@curr_con:=connect_time),
    ->     (@prev_trx:=@curr_trx),
    ->     (@curr_trx:=transmitted),
    ->     (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->     (@total:=@total+@newvalue) GrandTotal
    -> FROM mytable;
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
| line | (@prev_con:=@curr_con) | (@curr_con:=connect_time) | (@prev_trx:=@curr_trx) | (@curr_trx:=transmitted) | newval   | GrandTotal |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
|    1 |                      0 |                    117987 |                      0 |                  2650131 |  2650131 |    2650131 |
|    2 |                 117987 |                     78073 |                2650131 |                  6465178 |  9115309 |   11765440 |
|    3 |                  78073 |                    159268 |                6465178 |                 25905117 | 19439939 |   31205379 |
|    4 |                 159268 |                    410282 |               25905117 |                 59178089 | 33272972 |   64478351 |
|    5 |                 410282 |                    596408 |               59178089 |                 73502942 | 14324853 |   78803204 |
|    6 |                 596408 |                    683045 |               73502942 |                 75695427 |  2192485 |   80995689 |
|    7 |                 683045 |                    740379 |               75695427 |                 77576167 |  1880740 |   82876429 |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
7 rows in set (0.00 sec)

mysql>

The proposed query has the subquery return a lineitem number. I get the last one to show the final total.

Remember the formula from your question ?

if connect_time[i] > connect_time[j]
then total += transmitted[i]+transmitted[j]
else total += ABS(transmitted[i]-transmitted[j])
end

The newval column computes the formula


Extract column definitions from an IXF file of unknown origin

The IXF format is documented in the manuals. Conceivably, if other methods don't work as you would like them to, you could write a script to extract column definitions.


There seems to be a few queries you can try to get more information out of what is happening. The below query that was pulled from Stackoverflow takes advantage of FULLTEXTCATALOGPROPERTY.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'FTS_Demo_Catalog'

SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat

However, In this article, microsoft mentions

  it is usually a better option to check the corresponding
  PopulateStatus property at the table level,
  TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function.
  This and other new full-text properties in OBJECTPROPERTYEX provide
  more granular information about full-text indexing tables.

To this, I would use the following script:

DECLARE @TableId VARCHAR(MAX)
SET @TableId = OBJECT_ID('gGastroversion')

Select CASE OBJECTPROPERTYEX ( @TableId , 'TableFullTextPopulateStatus' )
            WHEN 0 THEN 'Idle'
            WHEN 1 THEN 'Full Population In Progress'
            WHEN 2 THEN 'Full population is in progress.'
            WHEN 3 THEN 'Propagation of tracked changes is in progress.'
            WHEN 4 THEN 'Background update index is in progress, such as autochange tracking.'
            WHEN 5 THEN 'Full-text indexing is throttled or paused.'
            WHEN 6 THEN 'An error has occurred. Examine the crawl log for details'
        END

Notice, that if you have number 6 as a result it will tell you to check the crawl log. Troubleshooting this information can be found here.


Backup-strategy for redundant data in postgresql-db.

One option would be to use materialized views. The main downside there is that at present there's no support for incremental refresh, so refreshing the views would be quite expensive.

Instead I suggest using UNLOGGED tables. That'll skip WAL archiving (and streaming replication) of the data. If you pass --no-unlogged-table-data it'll also omit their contents from database dumps. So you'll basically be implementing your own incrementally updated materialized views over the data.


Users running ANSI scripts are having problems with special characers

First question would be: "What does ANSI encoding mean?"

Answer depends on your Windows, see this list:  National Language Support (NLS) API Reference (column "ANSI codepage")

Let's assume you have a "western" Windows, then ANSI encoding most likely means Codepage 1252

In this case you must set your NLS_LANG environment value accordingly to NLS_LANG=.WE8MSWIN1252

See also https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600

As far as I remember SQL scripts must not have any BOM (Byte Order Mark) at the beginning.


Can't read password from config using --defaults-extra-file in MySQL

It looks like the access issue due to permissions level.
Try running the batch file as administrator by right clicking on it and then selecting Run as administrator ,Try wrapping your password with in '' if it contains special characters,Try removing any extra space that might be getting added,also please find few more solutions to similar problem here :

As per examples from http://dev.mysql.com/doc/refman/5.7/en/option-files.html
The password should look like:

[client]
# The following password will be sent to all standard MySQL clients
password="my password"


Mysql - when increase 'max_connections', what other paramters to adjust accordingly?

The main setting is innodb_buffer_pool_size.  It should be set to about 70% of available RAM if you have more than 4GB of RAM.
If you need more than the default 151 for max_connections it is worth looking into whether the client(s) are allowing too many connections.  It is better to reduce the load from that size.
A common problem is open_files_limit.  It comes from the OS's ulimit may be at a painfully low 1024.  That should be fixed in the OS.
There are no other "likely" settings to check on, especially with MySQL 8.0.
There are STATUS values that can help tuning busy systems.  See this for guidance in that help:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


Can we downgrade oracle database form 12cR2 to 12cR1

You can downgrade both major releases and patchset releases, based on the original Oracle Database release from which the database was upgraded. Major release downgrades are supported back to Oracle Database 11g Release 2 (11.2.0.2) and subsequent 11.2 releases, and Oracle Database 11g Release 1 (11.1.0.7). Patchset downgrades are supported back to Oracle Database release 11.2 patchsets, except release 11.2.0.1.

This link provides a summary of releases supported for downgrading.

Also, check this link for compatibility.


How to capture Query Status(Sleeping, Background, Running) in SQL Server Profile trace

Profiler does not capture point in time information (ie, what is the status of this query or wait times/types or current blocks, however you can get deadlock events). But rather, it captures activity/event information (ie, this activity/event occurred so record it). As such, it will not be able to give you that kind of information since it is always runnable when those activities are occurring.


"user does not exist " when calling DBMS_LOGMNR.START_LOGMNR

That is what you get when you break and ignore the basic concepts of the CDB architecture and common users and use unsupported "workarounds" (ALTER SESSION set "_ORACLE_SCRIPT"=true).
Above code works flawlessly when you create the user in a supported way.
-- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON users ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

Then:
sqlplus c##myadmin/myadmin

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/fra/RYCDB19/archivelog/2021_01_18/o1_mf_1_130_j0bo2onq_.arc',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
    159587

Whenever you use ALTER SESSION set "_ORACLE_SCRIPT"=true to create your own users and objects, they have their ORACLE_MAINTAINED property set to Y. This is not supported and can break functionality of features.


how to join below to queries in single query

Since you have different GROUPING SETS, you can use this clause:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  SUM(I.IP_WT) as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY GROUPING SETS (
    (ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE),
    (I.ORD_NO)
)

From there you can filter out any unwanted rows. A more manual version is to create union compatible results from two union legs:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  NULL as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE

UNION ALL

select NULL   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  NULL         AS "ORDER ITEM NUMBER",
  NULL AS "BATCH COUNT",
  NULL          AS "ORDER WEIGHT",
  SUM(I.IP_WT) as "TOTAL WEIGHT",
  NULL AS "PACKING TYPE"
FROM HR.BAF1_PDI I GROUP BY I.ORD_NO;

A third alternaive is to use a sub-select:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  ( SELECT SUM(IP_WT)
    FROM HR.BAF1_PDI
    WHERE ORD_NO = I.ORD_NO ) as "TOTAL WEIGHT"
  FROM ...

A 4:th alternative is to join in the second query

SUM(IP_WT)          AS "ORDER WEIGHT",
...
X.TOTAL WEIGHT,
...
FROM HR.BAF1_PDI
JOIN (
    select ORD_NO, SUM(IP_WT) as "TOTAL WEIGHT"
    FROM HR.BAF1_PDI
    GROUP BY ORD_NO
) X
    ON I.ORD_NO = X.ORD_NO
...


What you  must do is increase the join_buffer_size. What is  join_buffer_size ?

  The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

I highlighted that sentence because your query is as simple as it is going to get. You are doing a join on an integer which I assume is already indexed in both tables. If id in table b us not indexed, please index it ASAP.

I would suggest raising join_buffer_size to 4M.

You can change join_buffer_size in your session and rerun the query

SET join_buffer_size = 1024 * 1024 * 4;
SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

GIVE IT A TRY !!!


How to index cube style queries with changing aggrigations

I am an Oracle developer. Relating to how CI is implemented in Oracle, here are a few pitfalls to avoid.

Ensure that the table tbl is small enough to avoid overflow segments.
The table tbl must be ideally used only for bulk Inserts and Selects. Updates, Deletes and interspersed Inserts will cause page split.
If there are other non clustered indexes on tbl, ensure that those indexes do not end up with huge ClusteringFactor post converting tbl to CI.
Sharding tbl might help provided it does not affect other indexes and SQL performance.


Should the Query Store be enabled on all databases?

So you really have two questions:

  Are there any cases when Query Store should not be enabled?

Based on some reports of significant performance issues in the field when Query Store was enabled (although they are quite rare), one might infer that you should not enable Query Store on databases where it causes significant performance issues.

Of course the only way you'll know for sure whether it will cause a performance issue is to research all the known issues, make sure none of them will affect your system, and then enable it--but you still won't know if it will cause a performance issue until you enable it. For example, see FIX: Slow performance of SQL Server 2016 when Query Store is enabled. Note that enabling or changing settings in the query store can also flush the plan cache.

Secondly, if you aren't going to use the information and functionality it provides to improve the performance of the queries running on your server, then you shouldn't enable it.  Some environments, particularly large, heavily regulated organizations, may have such strict change control that you can't realistically do anything with Query Store.

  Should I turn it on on all databases?

I would certainly not enable it on hundreds of databases all at once, due to the possibility of causing a lot of problems and having too much data to analyze.  If your environment is such that you'll be able to use the Query Store to improve performance, then I would recommend enabling on a few database at a time.

If you have hundreds of databases, you probably don't have the staff to analyze all of the information, so it might make sense to enable it periodically on a few servers, see if there are any improvements to be made, then disable it and use it on some other servers for a while.  Or, when an application upgrade takes place, enable it for a while to see if the new code is triggering anything that Query Store can help with.


Cancelling the rebuilding of a partition with compression

Rollback could be 2x, 4x, etc., since rollback is single-threaded even for operations that were parallel.

My advice would be to let it finish. What's your alternative? Not do it ever? If you can't rely on the estimates your QA system gave, I don't know how you could ever have the confidence to start over and expect a different result.


Import Excel Table to SQL Server - with date column

Internally, Excel stores dates as an integer value. I've run into this before and solved it both in SSIS and straight TSQL

https://stackoverflow.com/questions/9085928/reading-in-date-column-if-first-row-isnt-a-date-with-ssis-excel-data-source/9086831#9086831

In your case, you might need to make a computed column that actually applies the formula, assuming you don't have to worry about the 1900 date.

-- code approximate as I'm late for a meeting
ALTER TABLE tbTableTest1
ADD opActualDate AS DATEADD(day, opDate - 1,CAST('1899-12-31' AS datetime))


Is there a way to make a null check on a variable in a WHERE clause only occur once?

One way is to use dynamic SQL, using a null check to optionally add that part of the where clause.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql


MySQL replication process parallel with user process?

It depends.

Older versions of MySQL run one replication query at a time.  Newer versions can do some level of parallelism.

Meanwhile, queries against the Slave can run in parallel just the same as reads and writes can go in parallel on a single server.  If the write (from replication) and the read (from a user on the slave) do not touch the same rows of the same tables, then they can go in parallel.  But if a significant lock is taken out by one, the other must wait.  Or get into a deadlock.

It's not clear where your INSERT is coming from.  It should not be coming from the Slave, since Slaves should be readonly.  If it is coming from the replication stream, then what version of MySQL is running and how is it configured?


Does an INSTEAD OF trigger gets re-created every time you insert into a view?

The trigger is not being recreated.  sql_text in sys.db_exec_requests is at the batch level, not the query level.  See eg

https://blogs.msdn.microsoft.com/dbrowne/2013/01/18/my-favorite-query-for-investigating-sql-server-performance/

For an example of how to parse out the currently-running query from the larger batch.

That wait is to fetch the page that the row is written to, either on the Clustered Index or some non-clustered index.  So I would look at the index design of the table, and see if it should be better-optimized for inserts.


Will large inserts crash a 3-node MySQL database cluster?

Some general considerations, first:

MySQL, and I am talking about InnoDB specifically, is optimized for OLTP loads, specially for good performance for point SELECTS and writes. That doesn't mean that you cannot do bulk inserts (I get 600 000 row insertions/second on my desktop-grade SSD), but that has an impact on the performance of other operations, mainly by taking extra locks and extra resources on transaction procesing/index merging/etc.
Large transaction processing overhead exacerbates when distributing those over the network on a tight clustering solution (for a MySQL model). The fact that you have to load synchronously a large number of rows over several nodes usually requires a large amount of memory and makes them a problem for tightly-attached-node solutions such as NDB and Galera.

Now into Galera specifics:

The first limitation that you will face is that by default, ws_max_size_rows is 128K (128 000 rows) and wsrep_max_ws_size is 1GB. Although those values are configurable, the load process will directly fail if the transactions are larger than the values configured.
I wouldn't be surprised that the cluster could crash - You can see an example here that happened during an import- ending up with 2 nodes getting an OOM even if that specific problem was later fixed. Extra memory is needed in order to certify every transaction among all nodes before actually executing it.
If you want the cluster to work adequately, I would recommend loading the files in chunks, using several transactions. There are event tools like [pt-fifo-split][1] that will help you do that. As an alternative, you could load those files individually on each node one at a time while they are temporally desynced.

This is one question that should not be argued about- If you have a production cluster, you should have a test cluster in which you can try your thesis first. Best case scenario, you will get a horrible performance/a deadlock. Normally, you will run out of memory and 1+ nodes will crash, leading to a full cluster crash.

To know more about the reasons behind galera behavior, and how to avoid downtime at the cost of sacrificing performance, you should read:

Galera flow control in Percona XtraDB Cluster for MySQL
Galera: Excessive memory use on sizable transactions
http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/10106-xtradb-limitations-wsrep-max-ws-size-and-wsrep-max-ws-rows


If  you're looking for graphs, Cacti (or Munin) will work. I've personally only used Munin.

However, I wouldn't put much stock in graphs to troubleshoot problems that arise. If it's showing in the graphs, it's been going on too long.

I would get familiar with the maatkit or percona tools (I think percona is a fork of maatkit) for trouble shooting problems.


How do I set up Azure Sql Server to have multiple instances?

There is a feature in Preview called SQL Data Sync, which can sync content between an on-prem SQL Server database and an Azure database.  However being in preview means you can't use it in production, and it does look to have been in preview for some time.  Register your interest,  try it out, this lets them know people might want to use the product.

Windows Azure SQL Database does not support replication so otherwise you're looking at a 'roll-your-own' type option at the moment.