Configure SQL Server to encrypt data traffic in both directions

There are two possible ways to ensure that you encrypt connections from clients to the SQL Server. The first option is to setup IPsec either manually on both clients and server or by using a IPSec policy or you can setup SSL encryption. The simplest way to do so is to use the self signed certificates from the server but as those are exchanged during the connection handshake their security is limited so it's recommended to use certificates from a trusted root.

You can create a test certificate with MAKECERT.exe by following TheSQLDude instructions and copy the self created root certificate to all the clients.

So to set up SSL encryption over the wire in SQL Server you either need a certificate signed by a trusted provider, which means that your SQL Server needs to have a valid FQDN as no certificate providers create certificates for invalid Fully-Qualified Domain Names (eg .local). Or you can also install your own trusted PKI infrastructure for instance Active Directory Certificate Services (ADCS).

This is needed as all the clients need to trust the root certificate that signs the certificate you install on the SQL Server.

When creating a certificate for SQL server the certificate needs to be created with certain extended properties most importantly it needs to be setup as Server Authentication (1.3.6.1.5.5.7.3.1) which means you might need to create a template in ADCS or you can create the certificate CSR using certreq

For this you need to create an csr.inf file

[Version]
Signature = "$Windows NT$"
[NewRequest]
Subject = "CN = SERVER.DOMAIN.TLD"
FriendlyName = SERVER.DOMAIN.TLD
MachineKeySet = true
RequestType=Cert
;SignatureAlgorithm = SHA256
KeyLength = 4096
KeySpec = 1
KeyUsage = 0xA0
MachineKeySet = True
Exportable = TRUE
Hashalgorithm = sha512
ValidityPeriod = Years
ValidityPeriodUnits = 10
[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1

You can then create a CSR to send to a CA

  certreq.exe -new csr.inf keycertrequest.csr

or get a certificate from ADCS by in an elelvated command prompt

  Certreq -new -f cer.inf Certificate.cer

And then you can export that certificate from your personal store and import that to the store for the SQL Server account.

When you have a certificate installed you can enable encryption in the SQL Server Configuration manager.
It's recommended to only use TLS 1.2 but then you need make sure you have updated the SQL Server native client and your SQL Server to the latest update. This is all well explained by SentryOne and in the release announcement


Query: All employee with sum of salary > 500

After playing around with this some more, I came up with this query:

SELECT ssn, name, salary FROM (
  SELECT ssn, name, salary, (@total:=@total+salary) as total
  FROM worker, (select @total:=0) t
  ORDER BY salary ASC
) as foo
WHERE (total-salary) <=500

This seems to do what you want on this limited data, but could be very slow on large datasets.


Database existence not happened correctly

Sounds like someone changed the defaults that exist in the model database on the server (512 is usually the default from what I've seen--not 4096). Since this is for a deployment on possibly any SQL Server (where model specifications could well be different) just remove the SIZE = 4096KB in both the mdf and ldf sections--to avoid this issue altogether. Each SQL Server will create the database based on specifications of the model database on SQL Server.

Example:

CREATE DATABASE [BIDS] CONTAINMENT = NONE
ON PRIMARY ( NAME = N'BIDS', FILENAME = N'C:\temp\BIDS.mdf' ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'BIDS_log', FILENAME = N'C:\temp\BIDS_log.ldf' ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)


When is it safe to disable InnoDB doublewrite buffering?

The only situation I can think of is reloading a large mysqldump. Why ?

Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko)

From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to

Log Buffer
Insert Buffer in ibdata1
Double Write Buffer in ibdata1
.ibd file for each InnoDB table

Shutting off the Double Write Buffer will let a mysqldump write data and index pages in the tables faster since it does not have to write the same 16K pages to ibdata1.

Production Servers should never have the Double Write Buffer disabled. If you do so for loading data faster (during maintenance of course), enable it immediately after reloading the DB Server.

In other words,

Add innodb_doublewrite = 0 to my.cnf
Run SET GLOBAL innodb_fast_shutdown = 0;
Restart mysql
Load mysqldump
Remove innodb_doublewrite = 0 from my.cnf
Run SET GLOBAL innodb_fast_shutdown = 0;
Restart mysql


Transactional replication between SQL Server 2014 (Publisher) and SQL Server 2012 (subscriber)

1) If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be am looking somewhere wrong. Please suggest if it can be set up?

For your replication to work, the PUBLISHER and DISTRIBUTOR should be on same version. The subscriber can be on lower version.

From BOL :

  For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)

  For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.

So, essentially, your SERVER A should be PUBLISHER and DISTRIBUTOR and your SERVER B should be SUBSCRIBER.

  2) We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?

For your scenario, you should enable TDE on PUBLISHER database ONLY.

From BOL :

  Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files.  During transactional or merge replication, encryption can be enabled to protect the communication channel.


Options for setting NOLOCK hint in dataset queries

Have you considered READ_COMMITTED_SNAPSHOT row versioning for the database?

Kim Tripp has a good article about it at http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

READ_COMMITTED_SNAPSHOT allows better functionality than WITH (NOLOCK) in that it provides absolute point-in-time consistency for long-running aggregations or queries with increased throughput because of reduced lock contention.


oracle database - sql for counting the columns with the same id with the one in current row

select rap.DENUMIRE,a3.ID, op.FK_ID, count(*) over (partition by op.FK_ID) AS count
  from ANEXA3_TABEL1_ROWS a3
 inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
 inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID


Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4.

At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship.

CREATE TABLE user_roles (
   username character varying(12) references users(username),
   "role" character varying(80) references roles("role"),
   PRIMARY KEY(username, "role")
);

I suggest using surrogate keys in this case, too, rather than storing the usernames/role names directly in the join table. The first time you want to rename a user or role you'll be happy you used surrogate keys. Just place a unique constraint on roles."role" and users.username.


Combining a parent table with multiple child tables

You need outer joins. Specifically to LEFT JOIN the parent table with each child:

  parent
LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id

and then SELECT from the appropriate tables. Columns that are in both children tables can be combined with COALESCE(). The order of the expressions inside the function matter only if there are cases where an id appears in both childen.

The query will be something like:

SELECT
    p.parent_id, p.name,
    COALESCE(c1.child_id, c2.child_id) AS child_id,
    COALESCE(c1.date, c2.date) AS date,
    c1.color,
    c2.lenght
FROM
    parent AS p
  LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
  LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id ;


How to change the expiration date of binlog files?

How to change the expiration date of binlog files ?

To purge binary logs prior to a binary log file or prior to a particular date, use purge binary logs statement.

To set an expiration period for binary logs, use binlog_expire_logs_seconds. I would recommend to keep this more than 2 days, whether or not you have replication enabled.

  I can not find the file to configure.

To see the order in which MySQL looks at the configuration files, run the following:

mysqld --help --verbose | grep -A 2 "Default options are read from"

If you also want to see what values the different variables would take on start, remove the grep part.

For a variable whose value is specified multiple times in the configuration files, or multiple times in the same file, the last value would be considered by MySQL. Some of these configuration files may not already be present, in which case you can create them on your own, and under [mysqld] in the configuration file, add the parameters you want to set.

  Is it safe for the database to change the timeout ?

Yes, it is safe if there is no replication involved. Even if a slave does exist, if all the logs that are to be deleted have been already processed and executed by the slave, it is safe to delete those logs from master.


Better way to full text query across multiple tables?

I do not know how dynamic your queries are, but naturally each table needs a separate query for that table.

Assuming that you are constantly querying this 4 table group, it might be worthwhile to create an indexed view that included the searchable fields you need from the 4 tables into a single view.

This means the overhead of maintaining the indexed view, but may help in this case.

Of course, if you have a widely varying array of tables that might be joined, you would need to consider whether this is a good idea for you or whether you should query each table separately as you are doing.


Error on PostgreSQL streaming replication

It sounds like the standby is now several months out of date.  It's probably not useful at this point.  You can replace the standby server with a new basebackup and start up the streaming again.  You may want to look into using replication slots this time to ensure that WALs won't be prematurely removed from the primary.


What exactly is "pinning" in relation to indexes?

Search for Keep Pool.  You're probably better off not using it unless you know its purpose and limitations very well and it fits the problem you are trying to solve.


It is not possible per request, but you might want to give the free MMS a try, which has quite some metrics. Together with the hardware monitoring provided by munin-node, you get a pretty good picture on what's going on on your MongoDB servers.


Why is my newly installed but isolated SQL Server 2016 instance filling disk with transaction log

Sounds like there may be a maintenance job running intermittently in the background which is filling your log. Do you have access to the job system to look? On non-production systems you'll also want to put the database in simple recovery mode in most circumstances.


How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?

Use alter diskgroup mydiskgroup check all; or the remap command. But ASM should manage this for you; perhaps you need a higher redundancy setting on your JBODs,


Query job history for TSX servers from MSX server using TSQL

Whenever you want to see what SSMS is running, you can always fire up a Profiler/Extended Events trace and filter on your login.

Doing this from MSX master and viewing a Multi-Server's job history gets you this query:

SELECT null as instance_id,
     sj.job_id,
     job_name = sj.name,
     null as step_id,
     null as step_name,
     null as sql_message_id,
     null as sql_severity,
     sjh.last_outcome_message as message,
     sjh.last_run_outcome as run_status,
     sjh.last_run_date as run_date,
     sjh.last_run_time as run_time,
    sjh.last_run_duration as run_duration,
     null as operator_emailed,
     null as operator_netsentname,
     null as operator_paged,
     null as retries_attempted,
     sts.server_name as server
  FROM msdb.dbo.sysjobservers                sjh
  JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id)
  JOIN msdb.dbo.sysjobs_view     sj  ON(sj.job_id = sjh.job_id)
  WHERE sj.name = 'MULTI_SERVER_JOB_NAME'


Hi It's not about just not the number of number of session or queries which are running.

Below will help you to understand how to troubleshoot this problem

Download the logs from RDS console for this instance to generate the Pgbader report. Below is the format which is most suitable to generate the logs
Download the pgbadger and use below command to generate the report.
https://github.com/darold/pgbadger
Match your CLoudwatch or RDS CPU time stamp with Pgbagder report to see how many queries are getting executed
Try to find the query which may be eating a lot of CPU.
pgbadger -q /postgresql*.log -f stderr -b "2019-07-17 08:55:19" -e "2019-07-22 07:30:07" -o pgbadger.html

I may be able to give specific guidance if you can hare more data.

Cheers,
Aj
http://www.postgresql-blog.com


Streaming replication in PostgreSQL uses the same security mechanisms as a normal frontend-backend connection.  So in order to encrypt the traffic, SSL should be used, and access control should be set up.

Alternatively, or additionally, consider a VPN of some kind.  PostgreSQL instances aren't really meant to be run facing the open internet.


Here are four approaches that each have advantages and disadvantages depending on what the current bottleneck is.

Normalize by adding a surrogate key column

--Sql Server syntax (My Oracle knowledge seems to have
--                   rusted due to lack of use)
CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code char(10) not null,
  amount decimal(10,2) not null
)

SELECT
    order_id,
    CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

Transforms into:

CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code_key int not null,
--  source_code char(10) not null,
  amount decimal(10,2) not null
)

CREATE TABLE source_codes (
  source_code_key int not null primary key,
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes ( advertising_campaign)
SELECT DISTINCT
   CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT
    order_id,
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes
            ON orders.source_code_key = source_codes.key

This approach is the cleanest from a pure dimensional modeling perspective. The logic in your CASE statements would be applied in an ETL process and the size on disk is reduced.  In Sql Server the CPU overhead is increased because a join is typically more cpu intensive then a CASE statement (I assume this is also true for Oracle).  If I/O is the bottleneck then this is a trade-off worth making and is the preferred approach.

Join on Business Key to Lookup table

CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code char(10) not null,
  amount decimal(10,2) not null
)

CREATE TABLE source_codes (
  source_code char(10) not null primary key,
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes (source_code, advertising_campaign)
SELECT DISTINCT
    source_code,
    CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT
    order_id,
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes
            ON orders.source_code = source_codes.source_code

This approach would require one row in source_codes for each distinct source_code.  Size on disk increases due to source_codes table.  Disk IO remains the same or increases depending on the ability of the source_codes table to fit in memory.  This approach makes sense only in the case where the queries are CPU bound due to logic in the CASE statement that is horrendously complex.

Keep Existing Schema

If the bottleneck is the CPU and the cost of computing the CASE statements is less than the cost of joining to a another table then keeping your existing queries may be the best bet.

Keep Existing Schema + Move CASE logic into client application/reporting tool

This approach makes sense if the bottleneck is network bandwidth to the client or the CPU load is too high with either the JOIN or the CASE statements.  By moving the complex CASE logic off the server you can distribute CPU load.  This would be the most intrusive solution and should be considered last resort.


Which text-index I should create for xooops engine to achieve better search results?

Your understanding isn't quite correct.  No type of index on MySQL can be used to optimize LIKE '%string%' expressions.

LIKE 'string%' can use an ordinary b-tree index, but this only matches strings at the beginning of the column's value, which wouldn't help you.

You're correct that MyISAM (and InnoDB in MySQL 5.6) support full text indexes, but to query them, you have to use the a fulltext search query, which looks like this:

... WHERE MATCH (col1,col2,...) AGAINST (expr [search_modifier])

As things are now, if you do not have a multi-column index on (approved,post_time) in the xps33_bb_posts table, it seems like you should at least want that as a minimum, since the optimizer could then choose to read the only rows with approved = 1, ordered from newest to oldest, and the query would finish as soon as the first 5 rows were found (or whatever the LIMIT of the particular query required).

If you could take an example from your slow query log and post the output from EXPLAIN SELECT, you may get some additional helpful suggestions.


1)MongoDB - Multiple cluster data synchronizing master to master

As MongoDB jira documentation Why was MongoDB not designed to have master-master replication actually, don't think master-master perform better than master-slave.
but it indeed easy to scale out.

my point about this is that mongo have provided too much functions to support all DB requirement.and lose its core design brief.this cause messes when using mongo db.it want to provide RDBMS function,also provide high availbility,scale out.

today mongo provide replica set deployment to meet normal data storage.and sharding cluster for large data storage.it is easy to convert replica set architect to sharding cluster deployment.but from application side,it is not easy to keep same query operation efficiency.

and other things like Data engine.mongo provide MMap,WireTiger,also other third party data engine.which also cause application developer to optimize the performance.

MongoDB doesn't do master-master replication.

  2) MongoDB - Multiple cluster DR

As per Ron Galbraith BMC documentation MongoDB configuration across 2 sites - and manual DR option  the issue arises around forming a Majority to elect a new Primary server (based on information on the MongoDB Strategy around fault tolerance.

  3)MongoDB - Multiple cluster Blue-green deployment

blue-green deployment strategy is Zero downtime during application deployment is one of the key requirements for continuos delivery. And no business would like their site to be down and showing maintenance page every few days/weeks during deployment.

To achieve this we decide to go for blue-green deployment. However, we were challenged with how to do this in legacy style data center infrastructure where we are,

Not able to spin new and throw away machines automatically using scripts
Don’t have ability to add/remove instances using scripts from load-balancer
Network level configurations are done manually like firewall setting

As per Sunit Parekh blog documentation of Decision 1 MongoDB database didn’t require any schema migration etc. However, we required to follow design principle, always write code which is backward compatible with the data models. And if there is any database migration required it should be run after deployment.


transaction across multiple databases - what is the overhead?

the spid in each database is the same, 6434, however the transaction_id seems to be different in each database.
how is it so?

What you have is wrong (may be you are looking at an older transaction which has the same name name).
From my test, I can see that the transaction IDs match (see below)

Using sp_WhoIsActive with @get_transaction_info = 1, I get below transaction detail
test_HV: 15 (1 kB),tempdb: 0 (0 kB),AdventureWorks_2005: 0 (0 kB)

You can easily check if the database is part of cross database transaction using
-- check if database is involved in Cross database transactions
if exists (
        select *
        from fn_dblog(null, null)
        where Operation = 'LOP_PREP_XACT'
            and [Master DBID] <> 0
        )
    print 'Based on the active part of the transaction log read, there is evidence that this database has participated in cross-database transactions.'
else
    print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in cross-database transactions.'

---- check for Distributed Transaction Coordinator involvement (below)
if exists (
        select *
        from fn_dblog(null, null)
        where Operation = 'LOP_PREP_XACT'
            and [Master DBID] = 0
        )
    print 'Based on the active part of the transaction log read, there is evidence that this database has participated in distributed transactions.'
else
    print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in distributed transactions.'

what is the overhead of having transactions across multiple databases?

A transaction itself does not have any cost associated (or to be more specific, it is very very negligible) To quote from my answer :

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.

Any modification to the database will generate log records, use memory and CPU along with tempdb space (depends on if you are doing a big sort and then updating or deleting records then spills to tempdb will be seen or if you are using snapshot isolation level, etc).

is there a way to make these transactions run quicker?

Yes, do your updates in batches or chunks. First select and update only those batches and make sure you have proper indexes on the tables involved, so that sql server will just seek the rows and update them.

I was thinking about DISTRIBUTED TRANSACTIONS.

I would avoid distributed transactions as they are not supported in database mirroring or AlwaysON (SQL Server 2016 has limited support) and if you are lucky - you will get an Orphan DTC Transaction SPID.
As a side note,

If you are using sys.fn_dblog for educational purpose or on non-PROD environments, you should be good. I would avoid using it in PROD environments!
You are using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. This means that you will have phantom rows (which can lead to unexpected results) - so just making sure you are aware of what you are using.

Unlike a read committed scan, a repeatable read scan retains locks on every row it touches until the end of the transaction.  Even rows that do not qualify for the query result remain locked.  These locks ensure that the rows touched by the query cannot be updated or deleted by a concurrent session until the current transaction completes (whether it is committed or rolled back).  These locks do not protect rows that have not yet been scanned from updates or deletes and do not prevent the insertion of new rows amid the rows that are already locked.


You can, but read this article on the implications of doing so.
https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services


For all general cases, the second transaction will be blocked until the first one commits or rolls back. As Martin notes, your isolation level may affect this, but the entire point of ACID compliance is to keep them separate.


Need to add leading zeroes to string representing a date

My first recommendation would be to use a DATE column type as it is best practices.
If that isn't possible, you can convert the string into a date, then put that in the format you need:
SELECT
  TO_CHAR(TO_DATE(Column,'MM/DD/YY'),'MM/DD/YY') /* I would recommend ISO standard YYYY-MM-DD so the column will sort properly) */
FROM
  /* etc */


Casesensitive vs. Caseinsensitive Collatesequences?

In oracle it is possible since 10.2. Take a look here Case-Insensitive and Accent-Insensitive Linguistic Sorts,so you might want to take a look again, 8i is a while ago now ....


Login failed for user - Error: 18456, Severity: 14, State: 38

As mentioned by Aaron, you're login 'say' X would be pointing to  a different database rather than NordenWeb, when it throws error with state 38:

You need to check the login for any other mapped databases. To find correct error you need to enable the trace when above error is created:

To dig in deep do nslookup 82.71.5.169 from windows to find the host name, just to verify we are on the correct page:

Now to track the correct database the login is throwing error , start the trace with default template using below 2 category of events:

1.Security Audit

2.Errors and Warning

a) From the Security Audit category (which is automatically listed in the default trace),  keep the "Audit Login Failed" event.

b) From the Errors and Warnings category, only two events were chosen. First, the "ErrorLog" event - this would trap every instance the Error Log is accessed. The second and the most important event was the "User Error Message". This is the event that would capture the error returned to the client

For the columns, only five would be fine: TextData, ApplicationName, NTUserName, LoginName and SPID. Again, you may think the DatabaseID or DatabaseName columns would yield the required information

Finally,  choose  filter the NTUserName field  to determine the account name from the Error Log.

After running trace you will see error message saying:

"Cannot open database  requested by the login. The login failed."

From above error message you will get the correct DB name, for which login is throwing error 38.

Try capturing with trace and see if this works!


SQL Trigger to verify account, if not verified dont process stored procedure/transactions

No you cannot use Triggers for this because Triggers do not fire in response to Read-type events (Execute or Select).  Nor should you because, in general, triggers are not an appropriate device for implementing security.

If you want to implement your security token idea on SQL Server(*) there are several general ways to go about it:

Alter your Stored Procedures:  Add an additional parameter to your stored procedures, then have your stored procedures themselves check this.  This is the recommended method.
Wrap your Stored Procedures: If you do not want to alter all of your stored procedures, you could instead do as one of the commenters suggests and write wrappers for all of your stored procedures.  The wrapper has the token as an additional parameter, which it first checks and then calls the actual procedure.  You use SQL-object security to prevent the actual procedures from being executed by anything other than the wrapper procedures.  This approach is not usually recommended because of the extra work and its higher maintenance.  However, this can be alleviated through automation if your database's security, schema and procedure design is structured correctly for it (this is how I do it, though not for this purpose).
A variant of #2 (above) would be to have a single Wrapper Procedure that calls all of the other procedures.  This would require that the first two parameters were the token and the actual procedure name and the rest would be the actual parameters shifted over two places.  The Universal Wrapper would then check the token and if valid, then call the actual procedure.  This is actually surprisingly easy to do (and does not require dynamic SQL either), however, it does also require properly designed security schemas to enable closing other security holes.  This approach is not usually recommended because of it's even higher dependence on properly designed security and schema structures and because auditors do not generally understand it and it makes them nervous.

(* - and this may well be something that is better addressed at the web/app server level, since that seems to be where the weakness is.)


As far as I know, running a database in archivelog mode, backup is taken at short intervals, which I don't prefer.

No, archivelog mode and backup frequency has nothing to do with each other. Its you who defines the backup intervals.

  I need the backup to be taken only once in a day, that too without shutting down my database. Is that possible?

No, it's not possible to take hot backup in noarchivelog mode.

  You can only backup a database in NOARCHIVELOG mode when the database is closed and in a consistent state.

-Oracle Documentation.

  Which is better option, database to be run in archive mode or noarchive mode?

Oracle recommends to put your database in archivelog mode. There are chances that you may lose data in case of media or disk failure.

  Also, if I'm going for noarchive mode, do I need to delete the backups manually or, does it happen automatically?

You need to take care of it, no matter whether you are operating on archivelog mode or noarchivelog mode. However, you can define backup retention policy

Documentation:
Configuring the RMAN Environment
Introduction to Backup and Recovery


This seems to be one of those errors that won't go away. In this case, it may be a case of human error.
First of all, here are a series of links with similar error messages on Shutdowns

http://www.webhostingtalk.com/showthread.php?t=518682
http://bugs.mysql.com/bug.php?id=7403
http://bugs.mysql.com/bug.php?id=48879
http://lists.mysql.com/mysql/206094
http://dev.mysql.com/doc/refman/5.6/en/error-log.html (See User Comments)
http://www.mail-archive.com/mysql@lists.mysql.com/msg12324.html

They all have seem to have one thing in common: MySQL for Windows.
It was claimed that Bug 7403 (the second link) was fixed and patch implemented July 14, 2005: http://lists.mysql.com/internals/27091
The versions of MySQL that this fix should be in 4.0, 5.0.40, 5.1.42. Looking in the error log you posted, I see you are using MySQL 5.1.67. Apparently, no one knows how to fix this issue or wants to (http://bugs.mysql.com/bug.php?id=7403) I also noted that the error messages indicate that you are using MySQL in Linux already.
I have three(3) suggestions that range from the most passive to the most aggressive...
SUGGESTION #1
Try setting log_warnings to 0, if you don't mind flying blind on warnings.
SUGGESTION #2
Upgrade to MySQL 5.6.11. Hopefully, there is should be more updates and bug fixes for this.
SUGGESTION #3
Move the Database to Linux. There should be more stability for this problem away on a better machine with the most updated kernel for the OS.
Give it a Try !!!
UPDATE 2013-04-30 11:20 EDT
Here is the fastest way to check for table corruption


How to correctly create a table that has multiple columns self reference another row?

In the relational model, each row represents a dataset representing a single item and so you do not 'refer' to another row's value.

The concept of an employee sharing a room of another employee is similar in fact to the relationship you already have of an employee having a manager. This could be represented similarly in the table like so:

EmpNo |Designation     | ReportsTo | SharesRoomWith | Room
10    | Director       | NULL      | NULL           | 1
20    | Senior Manager | 10        | NULL           | 2
30    | Typist         | 20        | 10             | NULL
40    | Programmer     | 30        | NULL           | 4

Where a NULL in SharesRoomWith means that an employee has their own room.
You could then query the list of employees and their rooms:

-- Employees that have their own room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e
where e.SharesRoomWith is NULL

UNION
-- Employees that share a room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e

join Employee e2
on e.SharesRoomWith = e2.EmpNo

to get:

EmpNo | Designation    | Room
10    | Director       | 1
20    | Senior Manager | 2
30    | Typist         | 1
40    | Programmer     | 4

This achieves the result that if the Director changes rooms, an update is only made in one location (the room number) and the typist's room will also reflect that change.

Note this design is not fully normalised so may not be an ideal solution, but demonstrates at least how you could represent that relationship.


Normalization, field types and relationship

Based on the comments to your question, I would strongly recommend using InnoDB since it is ACID complaint and supports your foreign key layout.

When it comes to using InnoDB:

Every working table needs to be InnoDB.
You cannot use InnoDB in its Default setttings. You must tune its disk and memory settings.
Every query doing JOINs cannot have a mixture of InnoDB and MyISAM or every SELECT query deteriorates with full tables locks : https://dba.stackexchange.com/a/9222/877
You must give InnoDB a big enough Buffer Pool so data and index pages are cached : https://dba.stackexchange.com/a/2194/877
You need to have InnoDB tables dwell in their own tablespace files or ibdata1 (default tablespace file) will grow out-of-control : https://dba.stackexchange.com/a/8983/877

As for the log table you have that uses the ARCHIVE storage engine, keep in mind

ARCHIVE storage engine has no indexes
Only INSERTs and SELECTs allowed
All SELECTs are full table scans every time

As for SELECTs, you must tune the SELECTs to take proper advantage of table indexes. Otherwise, add the appropriate indexes to the tables.


SQL server 2008 Analysis Services DSO properties

Answered by An Expert friend:

It wont effect other cubes. DSO component will only be used when a user runs DSO 8.0 application and will have no impact on SQL Server 2008 Analysis Services database.


The most frequent issues with these solutions concern concurrency.
For example:

You have group id 'A' with activity 1, and group id 'B' with activity 2.
Ali inserts activity 1 into group 'B' but doesn't commit
Bernie inserts activity 2 into group 'A' but doesn't commit
As far as both Ali and Bernie are concerned, there is no conflict as they cannot see the other individual's change
If your application tries to do a check then commit, you have a race condition where bad data can be recorded between the check and commit
If your application does a commit and then check, you have the possibility of the committed data being left in a bad state if no-one responds to the check.

Depending on how important the check is, and how much concurrency you need to accommodate, the simplest solution is to lock the entire table at the start of the transaction (ie Bernie can't do an insert until Ali does a commit).


Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL

You’re asking why the two queries with the WHERE id < 423 clause have different query plans correct?
Well the type of scan used is dependent on the actual characteristics of the table. Your first table uses a serial so it increments by 1 and there are only 423 rows to get so an index scan is most efficient. In your second example there you use a numeric and multiply that by a random so there are potentially quite a few rows in that filter and PG decides an index scan will not be most efficient.
TL;DR even though these queries and table properties look similar, your tables’ data is actually quite different.


Clients require instance name to connect to clustered instance

From further research, it appears the issue may be a result of older SQL clients performing an "InstanceValidity" check when connecting to SQL Server (specifically sqlsvr32.dll driver). The driver passes "MSSQLServer" as the instance to verify for the InstanceValidity check.  In this case, since the instance name listening on the Default port (1433) is named "Instance1", the check fails because the Instance names do not match the InstanceValidity check.

Specifying the port number or instance name explicitly in the connection string works.


Is there a tool to check mysql/mariadb database is all OK?

Verifying that a MariaDB or MySQL instance is "OK" is a complicated task, not least because there are different levels of "OK", and one man's "OK" is another man's "not OK". There are various tools you can use, though. To name a few in no particular order:

Percona Toolkit's pt-variable-advisor
SHOW ENGINE ... e.g. SHOW ENGINE INNODB STATUS\G,
Activate and check the slow query log
PMM - Percona Monitoring & Management
MONyog
mysql-tuner, see e.g. article here.


How to remove some data not needed on new row from mysql query with rollup

Plan A (rather tedious):

IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id),  -- conditionally blank each
...

Plan B:

( SELECT ... )  -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... )  -- Just total
ORDER BY ...   -- to get "Grand Total" to be last


Can hardcoding parallelism in Oracle hints lead to sub-optimal performance

Yes, yes it can. If the number of running parallel threads exceeds the number of CPU cores on the host machine, each of these threads will compete for CPU resources. This can be extremely inefficient and harm performance.


MySQL Queries to get only runtime/duration, not fetched results

(I have too many Comments for Comments...)

Please provide SHOW CREATE TABLE.  There are many details left out of the Question.
Please provide EXPLAIN SELECT ....  We need to discuss whether it is really doing any pruning.
How many rows are returned by those queries?
Are they realistic queries -- do you really need all columns; do you really need all rows (versus need to summarize them)?
Depending on the indexes, you may find that partitioning is slowing down the operation, not speeding it up.
Let's see the real queries.
The following avoids computing the end date, leap-years, etc.  And it grabs the missing second.

.

      event_time >= '2018-01-01'
  AND event_time  < '2018-01-01' + INTERVAL 2 MONTH

In my experience,

The only purpose for PARTITION BY RANGE(event_time) is to purge old data.
Summary tables are the real 'fix' for performance problems in log tables.

Once you have addressed most of my comments, the title Question should be moot.


So, I found a workaround...

First I made a proxy function on which the first proxy function will run :

CREATE OR REPLACE
FUNCTION p_count_toto(
    in_id_toto INTEGER
) RETURNS INTEGER AS $func$
BEGIN
        RETURN (SELECT sum(tmp_nb_toto) out_nb_toto FROM count_comm(in_id_alias));
END;
$func$ LANGUAGE plpgsql;

And I modified my first PL/Proxy function which looks like this now :

CREATE OR REPLACE FUNCTION count_toto(
        in_id_toto INTEGER,
        OUT tmp_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

I think that's not the most elegant way to do it, because at the application level, devs will have to change the name of the procedure they'll call, but it does the work.


You can use DBA_HIST_SQL_PLAN view to get the execution plan for SQL statement. In order to include more SQLs in your report you have to change AWR setting as shown below.

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHORT_SETTINGS(
RETENTION => 7200,
INTERVAL =>60 ,
TOPNSQL =>10 , --Here you can define the number of SQL to collect at each AWR snapsho
DBID => 123661118);
END;
/

You can get the SQL_ID from the AWR reports and can get their execution plan as shown below:

SQL>select plan_table_output from table (dbms_xplan.display_awr('&SQLID'));

Another way is to use Oracle supplied script called awrsqrpt.sql which can be found under $ORACLE_HOME/rdbms/admin directory.
During the execution of the script it asks for the SQL_ID for which you want to get the execution plan.

References:

MODIFY_SNAPSHOT_SETTINGS Procedures
DBA_HIST_SQL_PLAN
How to Control the Number of SQL Statements and other information displayed in AWR Report (Doc ID 1357637.1)


mysql error 1064

The error that gets thrown if the syntax is wrong. You did not clearly state what you are doing.

If you are changing a column

ALTER TABLE table_name MODIFY COLUMN column_name datatype

If you are trying to change column definition

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size)

If you are adding a new column

ALTER TABLE table_name ADD column_name datatype


SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 24G

Solution:

SQL> alter system set aq_tm_processes=1 scope=both sid='*';
SQL> alter system set streams_pool_size = 256M scope = both sid='*';

If you get any error like this:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

Reduce the db_cache_size:

SQL> alter system set db_cache_size = 12G scope = both sid='*';

and re-run:

SQL> alter system set streams_pool_size = 256M scope = both sid='*';

Now, you can run the expdp again without issues. Once done, don't forget to roll back all changes.


How to load balance mongodb replica cluster

You don't use ha-proxy.

Every node what connects to this replica set should have mongodb://mongo1,mongo2/?replicaSet="yourRS" -connection string, what tells which nodes are on that replicaset.. String don't need always list all nodes, most of them is enough, because Application can "ask" where current primary is located.


The function pg_total_relation_size() returns

  Total disk space used by the specified table, including all indexes and TOAST data

Given the disproportionate size between the tables and total relation size (100 to 1), it is probably mainly due to TOAST data. TOAST data is already compressed (LZ), so it will be unlikely to be able to compress it any further.

If you have a lot of duplication, or want to store blobs outside the database, you could resort to this: Storing blobs outside the database.


I need to somehow make those reads be processed only one-by-one,
  because if I have 2 concurrent queries, both would read, get that the
  sum is less than 100 and both insert, which would result in an
  undesired state, which would have been avoided if queries were
  executed sequentially.

That's a typical use case for the serializable isolation level, with an automated retry of the transaction when it fails with a serialization failure (SQLSTATE=40001)

Short of adapting your schema specifically to deal with the concurrency issue, the other techniques you mention are not going to work directly anyway:

the BEFORE INSERT trigger is limited in visibility to the already committed transactions, so sum(value) <= 100-newvalue may be true even  though another transaction has already inserted a row that will make this untrue as soon as it commits.
In the select for update technique, what row(s) is it supposed to lock? Even if doing select for update on the entire table, that does not prevent reads or new inserts. The select for update technique would work if it targeted a single pre-existing row, that the transaction would need to lock before proceeding further, implementing effectively a critical section.
But that table/row does not exist in your schema as mentioned in the question.


To flesh out what Aaron states a little more, linked server performance, particularly for large result sets, for cross-server joins and cross-server subqueries, is often disappointing. If you watch the remote server with Profiler, you may find that the local server bombards the remote server with requests for the fetch of a single row to match join columns. When that happens, the network latency and calling overhead conspire to kill performance.

If you can query local data without too much trouble, that would be best. You might be able to restore a production backup or use SSIS (or even bcp, it still works) to copy data from the production server to some working tables on the local server. Generally SSIS, bcp and similar tactics are faster than linked servers and may help to avoid issues with log file growth.

If you must query the data from a remote server, you may find that rewriting the query so that it uses OPENQUERY() (rather than four-part names) and 'sends' all of the 'remote parts' of the query over to the remote server and then joins the results of that to the local data will be more effective. SQL is supposed to be smart enough to move all of the joins to the remote server, but sometimes it doesn't and OPENQUERY() gives you a method to force SQL to do want you want.

Another, similar, tactic would be to run the 'remote part' of the query first and put the results into a temporary table, (optionally) index the temporary table and then join the 'local part' of the query to the temporary table. Again, this helps you to force SQL to do what it ought to.

It sounds like more work, but SQL may be able to behave more efficiently. As always, watch your data types on the joins and your SARGs or your indexes will be ignored.


Rebuild and Reorganize index while altering index

When you add/remove any column in existing non-clustered index, the index will be recreated(not altered) and hence index rebuild is not required.

The larger your tables, the greater the impact of executing DDL statements against indexes, both in the consumption of server resources and in the slowing of other queries that are executing at the same time.

On Enterprise Edition, you can choose to use CREATE INDEX  ... WITH (DROP_EXISTING = ON, ONLINE = ON) (for certain index changes at least) to minimize interference with other queries (they can continue using the original index while the new one is building, at the cost of the additional disk space / activity required to maintain both). And in 2019 you can use RESUMABLE so, you can pause the creation of the new index during high activity, or spread the operation across multiple maintenance windows.


Why does it take so long to calculate an Execution Plan?

I hate to answer my own question, especially since I have had so much help from others to work out the solution, but here goes.

The problem was due to some dud statistics in the database. Looking at the execution plan, the optimiser was expecting 11.5tb of data returned from the query. In reality is was receiving 87kb. I now know that huge mismatches between expected and actual rows returned is a sign that the statistics are out of date.

Simply running

exec sp_updatestats

forces the database to update the statistics for all tables.

This has taken the query execution time down from 3 minutes to 6 seconds. Everyones a winner!

Thanks for all the help guys. :0)


The UPDLOCK will prevent a subsequent call from reading an order that has already been picked up, but won't allow that subsequent call from reading others because it will wait to read all the rows that fit the criteria and you have explicitly told the engine that you intend to update them in this transaction.

If you add a READPAST hint as well, it will allow subsequent calls to grab any available rows that have not been picked up previously.

SELECT *
FROM Orders o WITH (UPDLOCK,READPAST)
WHERE NOT EXISTS (SELECT *
              FROM OrderEvent oe
              WHERE oe.OrderId = o.OrderID
                AND EventTypeId = 1)


Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter:

; with r as
  ( select id, submitter, answer,
           rnd = row_number() over (partition by submitter order by newid())
    from #tmpData
  )
select id, submitter, answer
from r
where rnd = 1 ;

Test at: SQLfiddle


First things first, if your application needs a data set to function then it needs access to that data set. Normally the computer would be networked to the central repository, but if that isn't possible then the computer needs to have a local copy that synchronizes with the central repository whenever it can communicate with that repository.

Your application will need to maintain some sort of change log since the last synchronization. Then when the application discovers it can communicate with the central repository it will need to synchronize the local changes with the remote changes. Keep in mind this can cause problems.

For instance if your application is a sales database for Acme Widgets a sales person might check inventory to verify they have 15 gizmos in stock. They proceed to sell those gizmos at the fair, but when they get home to and synchronize their laptop the realize that during the day 5 other sales representatives in the home office sold 15 gizmos each. Now you have over sold your gizmos.

In order to avoid the catastrophe of over selling gizmos you should ask management to get cellular data plans for the laptops so they always have access to the central database. Alternatively you might not care if the database synchronization results in oversold gizmos. I don't know the details of your application.


I think your explain plan is doing a heavy index scan. What you might need is an index with those exact three columns.

Please create this index

ALTER TABLE t_group_tag_relation ADD INDEX
resource_id_domain_id_resource_type_index (resource_id,domain_id,resource_type);

and try your query again.


Errors declaring variables in stored procedure?

DECLARE is permitted only inside a BEGIN ... END compound statement
  and must be at its start, before any other statements. Declarations
  must follow a certain order. Cursor declarations must appear before
  handler declarations. Variable and condition declarations must appear
  before cursor or handler declarations.

Try this :

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
       -- DECLARE STATEMENTS first
        DECLARE how_many INT;
        DECLARE `_rollback` BOOL DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
        --
        SELECT COUNT(*) INTO how_many FROM things;

        -- Start transaction.
        START TRANSACTION;
                -- Do all the things.
        IF `_rollback` THEN
                ROLLBACK;
        ELSE
                COMMIT;
        END IF;
END $$
DELIMITER ;


This was operator error - I was looking at the RDS console and I noticed the CPU was pegged at 100% after resizing.  I did some digging and found some errant processes that were hogging DB resources, after killing and letting the server settle down, all of the indices GIN & BTREE were found.


PostgreSQL - How to list all foreign key link to Primary key of a table

You can query the catalog tables:

SELECT la.attrelid::regclass AS referencing_table,
       la.attname AS referencing_column
FROM pg_constraint AS c
   JOIN pg_index AS i
      ON i.indexrelid = c.conindid
   JOIN pg_attribute AS la
      ON la.attrelid = c.conrelid
         AND la.attnum = c.conkey[1]
   JOIN pg_attribute AS ra
      ON ra.attrelid = c.confrelid
         AND ra.attnum = c.confkey[1]
WHERE c.confrelid = 'res_users'::regclass
  AND c.contype = 'f'
  AND ra.attname = 'id'
  AND cardinality(c.confkey) = 1;


Insert result of a parallellized select query into a table, in Postgresql

I think the only even worth trying is to use PostgreSQL FDW which may or may not work. Your use case would also have to be super-niche for that to pay off, like a complex GIS aggregation query.

You can see this restriction is still very much noted in the code,

  The most significant restriction imposed by parallel mode is that all operations must be strictly read-only; we allow no writes to the database and no DDL.  We might try to relax these restrictions in the future.

And, again here

 * Don't allow writes in parallel mode.  Supporting UPDATE and DELETE
 * would require (a) storing the combocid hash in shared memory, rather
 * than synchronizing it just once at the start of parallelism, and (b) an
 * alternative to heap_update()'s reliance on xmax for mutual exclusion.
 * INSERT may have no such troubles, but we forbid it to simplify the
 * checks.

I think it's being stopped by a call to ExecCheckXactReadOnly here in ExecCheckXactReadOnly

if (plannedstmt->commandType != CMD_SELECT || plannedstmt->hasModifyingCTE)
    PreventCommandIfParallelMode(CreateCommandTag((Node *) plannedstmt));

Shy of that Maybe in PostgreSQL 11, or 12?


Windows SQL Server Express 2012 and LDAP external server integration

Do you just want to configure this? Or are you not averse to writing some code?

In the latter case, it should be relatively easy to write a small program that connects to both the LDAP server and the database and synchronizes the data. I've written such a thing once in PL/SQL in Oracle -- if it's possible there it should be possible in most languages and environments!


What is the correct way to declare and use MarisDB user defined variables?

The main difference ist that you can use DECALRE ony in stored function and procedzres

DECLARE var_name

This statement is used to declare local variables within stored programs.

see manual

User-Defined Variables

User-defined variables are variables which can be created by the user and exist in the session.

see manual
Which means every user defined variable is accessible every where(in a session) not only in a stored procedure/function. So you set your user defined Variable in a stored procudure, you can get the value eve after the stored procedure ends. That is very helpful for debugging such procedures/functions.
Also when you DECLARE a variable, you set also its data type and size.
With user defined MysQL decides what it needs when You SET @a = 123455


What does the output of a JOIN statement look like?

Every result from a SQL operation is functionally a new table, whether or not it is stored on disk or in memory

The function of a join is to "Join" two tables together into a synthetic third table that (usually) only exists in memory during the time it is output to the application.

The reason to use a join is to reduce Data Anomalies, by insuring that data appears in one and only one place in the database.

Consider the following:

Person ( ID Integer Primary Key, FName String, LName String, Address String, City String, ZipCode String)

Here, whenever the government changes zip-codes (which happens entirely too frequently for database developers' tastes * the persons table will need to be updated such that all old zipcodes correctly belong to the correct city and new zip-code.

In this instance, the same zip code is in multiple tuples of the table, and if the table is editable manually, will be subject to severe update anomalies as new city/zip-code combinations are entered without the database as a whole being updated.

By having three tables instead, we get the same data, but with increased flexibility and reliability (at the cost of some performance, though questions of "appropriate denormalization" are a topic for another series of answers.)

Person ( PersonID Integer Primary Key, FName String, LName String)

City ( CityID Integer Primary Key, City String, State String, ZipCode String )

Address ( AddressID Integer Primary Key, AddressLine1 String, CityID Integer References City, PersonID Integer References Person)`

And we can get a single "table" out of the above through the following query:

    SELECT Fname, Lname, AddressLine1, City, State, ZipCode
      FROM Person
INNER JOIN Address USING (PersonID)
INNER JOIN City USING (CityID)

This allows us to update the individual tables whenever something specific about them changes, without corrupting the rest of the associated data.

An INNER JOIN is the default type of join. Roughly speaking it notes: "combine tuples where this record from table a matches this record from table b" The other types of joins are OUTER JOINS and what's known as a CARTESIAN PRODUCT. An outer join is "Take everything from the left or right table (as specified) and where the other table matches, join it. Where it doesn't match, fill it with nulls" And a cartesian product (without getting into the relational algebra is: Take every possible combination of tuples in both tables and output them into one table, without matching anything.

* More than never, especially in the case of moving the boundaries of zipcodes because of gerrymandering


A constant scan takes either 0 seconds or 2-3 minutes

It appears as though even with a ... WHERE 0 = 1 clause that there will still be a requirement for an intent shared (IS) lock on the table.  Let's prove this:

I will start out by creating a test table:

use TestDb1;
go

create table dbo.MyTestTable1
(
    Id int identity(1, 1) not null,
    SomeInt int not null
);
go

insert into dbo.MyTestTable1 (SomeInt)
values (10), (20), (30), (40), (50);
go

Now that I have my test table, in one session (query window) I'm going to execute the following to put an exclusive (X) lock on dbo.MyTestTable1:

use TestDb1;
go

begin tran;
    select
        Id, SomeInt
    from dbo.MyTestTable1 with (tablockx);
--commit tran;

I can verify the exclusive lock by looking at the sys.dm_tran_locks DMV.  Then in another session (new query window) I do exactly what your query does:

use TestDb1;
go

select
    Id, SomeInt
from dbo.MyTestTable1
where 0 = 1;

At first glance I see that it isn't completing.  Looking at sys.dm_exec_requests, I see exactly why this is the case:

select
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where st.text like '%where 0 = 1%'
and r.session_id <> @@spid;

I can see here that my ... WHERE 0 = 1 query is waiting on an IS lock for this object (that object_id translates to dbo.MyTestTable1).

I am by no means saying that concurrency is your problem, but by the sounds of it you are exhibiting the symptoms.  The example above is to prove that your aren't exempt from locking and blocking even with a WHERE clause that'll never return data.

All we can do is guess, so what you need to do when it's "taking a long time" is to see exactly what that request is doing that is taking so long.  If it's waiting on something, then see what it's waiting on.


Overlapped I/O error installing SQL Server 2008 R2 SP2 Express

I eventually located an answer to this. There are multiple steps that you need to perform to ensure that this does not occur, and any one of the items on the list can cause the issue.

If any of these MS patches are installed, delete them. Any one can cause this issue

KB2918614 (The classic cause, but not the cause in this case)
KB3000988
KB3008627
KB3072630

Rename or delete the folder C:\ProgramData\Microsoft\Crypto\RSA\S-1-5-18
Rename or delete the folder C:\Users\[username]\AppData\Microsoft\Crypto\RSA\S-1-5-18

In this particular case, one patch (KB3072630), and the two files all needed to be dealt with to fix the issue - or, to be more precise, I dealt with these three items before the issue was fixed.


can I create an additional column in each UUID table and use it at the address?

You can add an alternate key using a UUID like this:

alter table SomeTable add uuid uniqueidentifier unique not null default newsequentialid()

Which will add minimal overhead, and provide efficient single-row lookups.


How can I optimize dashboard performance?

suggest any missing indices which could help

You need to show what indexes you currently, and the size & shape of your data (approx how many rows historically?, and how many new are added each day/hour/other?, how much do fields vary?, how often are they NULL?), have defined for us to give absolutely relevant advice.

Any thoughts on

As most of your queries follow the stated pattern, you are always performing a query on a range of dates so at very least you need an index on [date]. Further more it would probably be a good idea for this to be your clustered index[†] to reduce the page accesses needed for such range queries.
If you can afford the extra space (and the extra RAM needed for your common working set to stay in there to avoid IO thrashing) then you may get a noticeable boost from having indexes on [date],FieldA, [date],FieldB, ... so that the grouping does not have to perform a sort operation (once the data is found by date it is already in order in the index used). If there are particular fields that are queried for much more often than the others, then perhaps just do this to help the queries on those fields instead of spending the resources doing it for all of them.
[†] even if you have a unique integer as your primary key[‡] (or something else like a UUID)
[‡] and you should have a surrogate key in this example otherwise you could have rows that are otherwise identical which doesn't fit the relational model and could cause issues
As a side-note: date is a keyword, as it is a type, so I would avoid using that as a column name even in examples.


Suggested ways to route traffic between master / slave

PgPool-II can do this, with some limitations, as outlined in its user manual.

The best way is to make the application aware of which queries are read-only/poolable, and which are write queries that must go to the master.

There aren't really any other alternatives at this point.


How to add newly discovered cell node as member of exadata storage server grid in oem12c?

Got answer for my question.Here is the command to add newly discovered cell nodes under existing exadata server grid

emcli modify_system -name='Exadata Grid da13.myhost.com' -type='oracle_exadata_grid' -add_members='da13cel04.myhost.com:oracle_exadata'

output:
System "Exadata Grid da13.myhost.com:oracle_exadata_grid" modified successfully


Tablespace 'XXX_SML_INDEX' does not exist using impdb

Find the default tablespace at database level (which is not necessarily the default tablespace of your user):

SQL>  select property_value from database_properties
      where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------
USERS

Find the default tablespace for your user while logged in another user with adequate privileges for querying DBA_USERS:

SQL> select default_tablespace from dba_users
     where username = 'BP';

DEFAULT_TABLESPACE
------------------------------
USERS

Find the default tablespace for your user while logged in as that user:

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

Using that information, remap the tablespace as: remap_tablespace=XXX_SML_INDEX:USERS.


You can use Linked Server for SQL Server side. Creata a stored procedure to check online mysql tables data. If product sold decrease the local product count on the Local SQL Server.

Why do you cover this problem on the application side using transaction ?


Could not continue scan with NOLOCK due to data movement

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, but before it attempts to read the data row.

Craig Freedman gives a good write up here

Fortunately, the fix is relatively simple: http://support.microsoft.com/kb/815008

If that doesn't work, you have the slightly more painful option of removing all your WITH (NOLOCK) hints and setting your transaction isolation level to something above READ UNCOMMITTED.


What is Sql Server Clustered Index in Oracle terminology

In Oracle terminology, it is an Index Organized Table, commonly referred to as an IOT.

  An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.


This is an XY problem. You want to fix the VLFs in your log file, not fill a transaction log so it overflows into a second log file, freeing up the first log file.
There are definitely ways to stabilize VLFs, even in an AG, even in full recovery, and without disrupting your log chain, creating a second log file, or sending a bunch of bogus transactions to your secondaries. Here is a long-winded but easily reproducible example.

First, let's make a database with less than optimal filegrowth for the log. This will generate some pretty bad VLF layout. Let's also take a backup so it isn't operating in pseudo-simple mode:
CREATE DATABASE abc
      ON (name = N'abc_data', filename   = N'c:\temp\abc.mdf')
  LOG ON (name = N'abc_log',  filename   = N'c:\temp\abc.ldf',
          size = 1024KB,      filegrowth = 1024KB);
GO

ALTER DATABASE abc SET RECOVERY FULL;
GO

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH INIT, COMPRESSION;
GO

Now, let's look at the VLF and file info (note that your metrics may differ slightly). Before adding any data:
SELECT current_vlf_size_mb, total_vlf_count, active_vlf_count,
  avg_size_mb = total_log_size_mb/total_vlf_count , [used_log_%]
    = (SELECT used_log_space_in_percent FROM abc.sys.dm_db_log_space_usage)
FROM sys.dm_db_log_stats(DB_ID(N'abc'));

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             4                1                 0.24804675   49.6063

Let's create a table and generate a bunch of log activity to encourage some sub-optimal log growth (this is to simulate the problem in a new database, not to solve the problem you already have):
SET NOCOUNT ON;
GO

CREATE TABLE dbo.flarb(a int, x char(4020) not null default 'x');
GO

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;
GO 500

After we add that data, let's run query 1 again (and also EXEC abc.sys.sp_helpfile;):
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11035            11032             0.997552532  99.97239

name     size
-------  -----------
abc_log  11272192 KB

Your data may vary depending on SQL Server version and how many objects are in your database, but order of magnitude should be similar. Let's see what happens when we try to shrink our log file:
DBCC SHRINKFILE(abc_log, 4000);

Running query 1 + sp_helpfile again:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11033            11032              0.99755208  99.9913

name     size
-------  -----------
abc_log  11270144 KB

I mean, it shrank a little wee bit, but didn't have a meaningful impact on VLFs. Think about it - it can't shrink the log because the log hasn't been backed up, so all that data is still active and can't be removed. So let's try backing up the log, then running another shrink, and run the query again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.1.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             11033            6                 0.997552088  3.1719937

name     size
-------  -----------
abc_log  11270144 KB

No good. While we got the log usage down to 3%, we still couldn't reduce the size of the log. Let's try again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.2.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             11033            5                 0.997552088  3.1494207

name     size
-------  -----------
abc_log  4096000 KB

Getting closer! This time, the log file agreed to shrink, but the VLF count is still out of proportion. So, we'll try one more time:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.3.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             4027             1                 0.993293317  3.153424

name     size
-------  -----------
abc_log  4096000 KB

This time, it worked a little better. We got the size down, and the log % down, and VLFs are reduced, but still too high (and average size is too small).
So let's try halving the log file, then growing it in even, 8GB chunks. Using 64GB as an example; you may need more, or that may be ridiculous. But the point is to pick a size it's never exceeded before, because if you want to control VLFs, you're going to want to prevent it from auto-growing, and if it grew to 120GB before, it will grow to 120GB again (Lots of background here):
DBCC SHRINKFILE(abc_log, 1);
GO
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size =  8000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 16000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 24000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 32000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 40000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 48000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 56000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 64000MB);
-- should probably fix auto-growth here, too

Now, query 1 + sp_helpfile returns:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             132              1                 484.8484257  0.00710449

name     size
-------  -----------
abc_log  65536000 KB

Your results may vary - you may have 1000 VLFs, you may have 700, it can vary depending on how the shrink and growth operations work through the log. But you should be in a better place. You can keep experimenting with sizes that get you toward an optimal VLF count/size (say, 0.5 GB per VLF).
Remember these log backups were introduced into your log chain. So you should put them with your other log backups, just in case you need to recover, or do this right before your next full backup.
Also, you may wonder, why wasn't one log backup enough? Some background here.


Changing primary key column type from varchar to integer

Hunker down and write the script. What you're describing isn't difficult just a little time consuming.

I'd normally recommend to install SQL Server Data Tools; create a new database project by pointing it at the existing database and doing an import (this populates all the tables, views, procedures, indexes, etc). That will then likely build with little or no changes, and you can then make changes in the designers and see what the change script that it outputs is like.

In this case it's not a complete solution because SSDT will usually abort when it finds data in the tables when you're making large changes. But it might give you something to compare against insofar as dropping and recreating all the dependencies goes.


Find rows matching only values from a specific list with SQL

One way would be to use NOT EXISTS.

SELECT *
FROM   YourTable T1
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND NOT EXISTS (SELECT *
                       FROM   YourTable T2
                       WHERE  T2.Id = T1.Id
                              AND T2.Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )) ;

Or an alternative method if your DBMS supports windowed aggregates..

SELECT Id,
       Status
FROM   (SELECT *,
               COUNT(CASE WHEN Status NOT IN ('Closed','Rejected','Cancelled')
                             THEN 1 END)
                     OVER (PARTITION BY Id) C
        FROM   YourTable) D
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND C = 0 ;

And another option using EXCEPT:

SELECT *
FROM YourTable
WHERE Id IN
      ( SELECT Id FROM YourTable
        WHERE Status IN ( 'Closed', 'Rejected', 'Cancelled' )
      EXCEPT
        SELECT Id FROM YourTable
        WHERE Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )
      ) ;


postgres jsonb return specific paths of json object

You almost certainly can't.

JSON doesn't [yet] have the equivalent of XML's XSLTransform, which could do what you describe, but [still] only after retrieving the entire blob of XML to work with.

Databases are really good at finding little bits of stuff and putting them together; they're far less good and taking large blobs of stuff and pulling them apart.

I assume what you're showing is a simplification but what you've shown could be done easily with ordinary, relational tables.


Pivoting should be broken into two steps.

Step 1:  Create a SELECT with the data not yet pivoted:

SELECT product, date, qty
    FROM ...

In your case, that query is likely to have a JOIN.  (I am uncomfortable with which table date is in, but that is a separate issue.)

Step 2:  Do the Pivoting.

SELECT product,
       ( ... ) AS '2018-06-01',
       ( ... ) AS '2018-06-02',
       ...
    FROM ( the-above-SELECT ) AS x

There could be issues with step 2 -- As shown, it requires a fixed number of columns.  If you have a variable number of columns, you cannot build it this way, but need to construct the SELECT dynamically, then 'execute' it.

More discussion:  http://mysql.rjweb.org/doc.php/pivot


MySQL: Queries limitations per host/user

Yes you can. You can create users with resource limits, e.g.:

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'some_password'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

See here for more details:
https://dev.mysql.com/doc/refman/5.7/en/user-resources.html

Since every connection can run at most one query at a time, for the specific case you are describing, you probably want to set MAX_USER_CONNECTIONS to 100.


How can I determine the size of a dump file prior to dumping?

Please run this query:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;

This will give you a ballpark figure. The column index_length is not used because mysqldump does not dump indexes, only data. Just to be safe, you should always gzip it immediately:

mysqldump --all-databases --routines --triggers | gzip > MySQLData.sql.gz

Give it a Try !!!


You simply need to balance your ().

Either delete the ( between when and left or add a ) after '2015'.


There have been no significant enhancements in pgcrypto, and there's no functionality to allow functions to be marked sensitive such that queries that use them don't get logged. That'd fill one security hole and create another anyway.

So long as you don't actually store the key in the DB, you're not inherently giving the DB the ability to decrypt the data. You're just increasing the risk of key exposure through logs, network protocol captures (if not SSL), pg_stat_activity, etc. If you trust both the DB server and app server, this may not actually matter as much. Nonetheless, I still think it's better that the DB never see the decryption keys at all.

See also:

https://stackoverflow.com/q/12614523/398670
https://stackoverflow.com/q/21095026/398670
https://stackoverflow.com/q/8054503/398670
https://stackoverflow.com/q/12329652/398670
https://stackoverflow.com/q/12329652/398670


SQL Server effectiveness of mirroring partial database

I think you should consider transactional replication instead of pulling data into a separate database and then mirror that database.

Replication in my opinion is not a high availability solution because for example it does not provide automatic failover, but I think it fits to your needs because you just want to replicate some tables and you can also apply filters to the published tables so you can replicate just part of the data and not the entire table (in case you want to do something like that). If you want to redirect applications to your replica, you could create a DNS entry pointing to the primary sql instance and set the connection string to use that DNS instead the servername or the ip and you just change the DNS to make it point to the principal instance or the replica so you don't have to touch your applications to failover.

Of course replication look at the limitations of replication and evaluate if this fits your needs.

As you mentioned, it is hard to provide a recommendation without knowing all the specs of your scenario, but I hope this can be an alternative to you situation.


Package deployment model has nothing to do with SSISDB. Instead when Package deployment model deploys to SQL Server it ends up in the msdb database in the sysssispackages table. Follow these instructions to view deployed packages. Or you can query that table.


Job not running as agent but as engine account

SQL Server itself is performing the backup; therefore the DOMAIN\sqlengine account needs access to the share.

SQL Server Agent is simply starting the job.

This is exactly the same as if you started a backup using the T-SQL BACKUP DATABASE [xyz] TO ... command.  SQL Server would still require access to the destination path.  You can still lock-down the destination path, and should do so, to prevent unauthorized access to the data stored in the backup files.

Also, instead of using Maintenance Plans to perform maintenance, I'd recommend using Ola Hallengren's scripts; they are excellent, well-supported, and widely-used.


(Consolidating the comments into an answer, so it's more easily consumable by searchers.)

It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb.

I have an old, but still technically accurate, video on how to move all the system databases (not just tempdb) here, which includes showing the process of assigning permissions to the new data container. The database engine requires Full Control on This Folder, Subfolders, and Files starting at the data container root.

After the permissions are set correctly on the data container, the dangling files should be removable from the system catalogs using ALTER DATABASE ... REMOVE FILE ....


NOSQL Manager for MongoDB throws command failed collection already exists

Please make sure you have dropCollection privilege on the destination database.


How to unify aggregating function with Postgres per different group by

Use grouping sets:

select
  coalesce(ind1, ind2, ind3) as ind,
  ...
group by grouping sets (
  (ind1, import_date),
  (ind2, import_date),
  (ind3, import_date));

But its was introduced only in 9.5 version.


Search on part of a column in a SQL Server cross-database view

Are you allowed to replicate that table in another database? Using transactional replication, for latest data access. If yes, then I'd test a more cumbersome process like replicating the needed table, building another table or materialized view with a computed column as you need.

If replication is not an option, I'd say that you need to establish a scheduled process to take that table in your database and continue locally to process the data. Exactly like the first suggestion, but using your own scripts and schedule.

That, or I think you're stick to the scalar functions that aren't really the best option from performance point of view.


If you have the default trace configured, and if the change was recent enough, you might be able to find the change.  The default trace only maintains a certain amount of trace, but I have found unexpected changes by examining its contents.

See: http://msdn.microsoft.com/en-us/library/ms175513(v=sql.105).aspx

This describes how to read the default trace so that you can research problems.

There is no backward looking tool to find database growth and other changes.

If you want to have a persisted history of changes you will need to periodically query the needed data and store it in a database table.  With SQL Server 2012 Change Tracking can also answer questions like this.


MongoDB insert performance rapidly dropping

The MMS monitoring graphs show that your disk basically can't keep up with the volume of data you are trying to persist to disk.  The flush average is hitting over 100 seconds.  Since MongoDB flushes data to disk every 60 seconds by default, that means that successive queues are queuing up and then impacting everything else.  Basically you need more IO to sustain the write workload you are performing, likely an SSD, for that kind of sustained writing.

You can try throttling things by using different write concerns interspersed to get a more consistent throughput without completely overwhelming the system (check out j:true and a concern greater than 1 if you are running a replica set).


Database subsetting a viable method of slicing a database?

Database subsetting is a great substitute for testing when you're dealing with a very large database or you want to limit the exposure of a production dataset. I would not recommend trying to do this manually, as you point out, unless you're dealing with a very simple schema (1-3 tables). Most commercial databases have tools for subsetting (e.g. Oracle does) although MS SQL Server surprisingly doesn't. There are a number of decent open source projects out there (e.g. Jailer, Condenser, RDBMS-Subsetter) that are good, but not quite out-of-the box.

A few additional caveats to keep in mind:

If you select too small of a subset, you may miss outliers and important edge cases. If you know of a particular set of records that you want in your test set, you should explicitly include these as part of subset.
You want to select an unbiased subset. Don't pick a geographic region or a fixed attribute to segment your subset population. I recommend you pick a primary table (e.g. users) and a randomly selected percentage of that table.
Scale testing does not always scale linearly. Don't assume that you can simply 100x the performance you measure on 1% of the original database.

(note: I'm a contributor to an open source database subsetting tool, you can find out more about it here: https://www.tonic.ai/blog/condenser-a-database-subsetting-tool)


The create table DDL in your question is for a non-partitioned table. It cannot be partitioned without schema changes since the partitioning column must be part of the primary key (and other unique indexes/unique constraints) and primary key columns cannot allow NULLs.

That said, you can create non-clustered indexes on a partitioned table. The index will be partitioned using the same scheme as the table by default. You can also specify a filegroup for the non-clustered index, which would create  a non-partitioned index. It is generally advisable to partition non-clustered indexes similarly to the table (align) in order to allow switching partitions between tables.

BTW, I strongly recommend you avoid using reserved keywords line TRANSACTION as object and column names.


What is controlling SQL Server's page file usage? SQL Server or Windows?

Firstly, the most important thing you really need to know is that it is critical to configure memory usage by SQL Server and other applications on the system to ensure that it never starts paging to disk.  RAM access is measured in nanoseconds, and disk access is measured in milliseconds, so whatever gets paged out is going to take hundreds, if not thousands, of times longer to access.  It will also create a massive load of I/O on the system to do the swapping, contending for memory, CPU, and disk access.  So even if you have SSDs, it is still going to hurt if it starts swapping to disk.

When the max memory setting is changed, SQL Server discards everything in the buffer and starts over.  So if it was swapping the buffer to disk, yes, this may have caused the disk swapping to stop and performance somewhat restored.  However, if it was swapping to disk with a 40 GB setting, it will be more likely to swap in the future now that it's going to use even more memory.

However, the change you made could have just been a coincidence--do you know for sure that there were no long-running queries blocking those jobs?  There are dozens of possible causes, and unless you have data from monitoring mechanisms, there's no way to be sure at this point.

Memory management to avoid swapping to disk is one of the dangers of running multiple instances of SQL Server on one system.  If you have 128 GB of RAM, for example, the max memory setting of all instances should only total around 110 GB or so, perhaps a few GB more after monitoring available memory for a while

If SQL Server cannot lock pages in memory, then it has no control over what memory will be swapped to disk.  A process has an address space of virtual memory, and when the process reads the memory, the OS will retrieve it from the physical location (memory, or disk).  The OS keeps track of when memory was last accessed and it tries to page out the memory that is least active to reduce the impact.

The process does not know about the physical location of each offset of its virtual memory--this could create quite a lot of pointless overhead in tracking it.  SQL Server does, however, have a way of knowing how much of its memory is swapped to disk, as it will log a message as seen in How to reduce paging of buffer pool memory in SQL Server when it reaches a certain threshold.

Highly recommended reading: RAM, virtual memory, pagefile, and memory management in Windows, particularly to understand that monitoring Memory, Pages/Sec is not a valid measure of memory being swapped to disk.  Many other articles on the Internet incorrectly advise this counter to be used.

  Memory, Pages/Sec: This counter is one of the most misunderstood measures.

  A high value for this counter does not necessarily imply that your performance bottleneck stems from a shortage of RAM. The operating system uses the paging system for purposes other than swapping pages because of memory over-commitment.

You can easily verify this by simply having an application write a large amount of data to disk, and you'll see high numbers for Memory, Pages/Sec.  This is because the data that's being written to disk first has to be in memory, and then it is "paged" to disk.  The terminology around "paging" is misunderstood by many system administrators and it is often defined as "swapping process working set to disk," but "paging" is a much broader term than covers every read and write to disk.


What determines the maximum number of concurrent connection to SQL Server 2016 Express

So are so many factors because there are so many different types of loads.  When it get loaded the only defense mechanism is to deny connections.

I you are looking for max connection you are looking for the wrong thing.

Design your application to be scalable - acquire the connection as late as possible and release the connection as soon as possible.  A well designed application could have 100 active users and never have more than 10 active connections.


How do I find the median value of a column in MySQL?

There is quite a bit of discussion here on calculating median values from a MySQL table. Just search the page for 'median'.

As an aside, it strikes me as remiss that there is no built-in function to do this. Median is often more descriptive of central tendency than mean. Access/VBA has the same hole in its function list.


When to choose an attribute vs. a new dimension?

To make gender a freestanding "dimension" is, more likely than not, an attempt to normalize your dimensional model. Unless you have attributes associated with gender then how is it a dimension? Is it possible to have a conformed gender dimension?

As for diagnosis are you modeling the process or the values? Very different requirements and issues to deal with. I would imagine a series of dimensions and facts to accurately keep track of the diagnosis process.

Disk space... considering our modern computing environment, space is your last optimization/design concern. If it truly is, you should know this for certain.

  what if gender changes? How can I track that?

SCD Type 2 Dimension

  how can I track a patient's flow/change of diagnoses?

Transactional or Accumulating Snapshot Fact table(s)


Hopefully, today you learned that the "Table-per-Day" model for storing your data is a Bad Idea.
Generally speaking, "Table-per-anything" is a poor choice these days.
With proper indexing, there is absolutely no reason why all of this data couldn't live in just one table.
Also, I'm pretty sure MySQL still has the hard limit for the number of tables in a query (including those in any Views used) of [just] 61 tables, which is only double what you're currently asking for.
As to your immediate question, "union all" is your friend.
select * from tablename_2021_01_01
union all
select * from tablename_2021_01_02
union all
select * from tablename_2021_01_03
. . .
where ...


Why can't sa be impersonated in a procedure?

Please, remove the WITH EXECUTE AS OWNER and it should work
--Create the procedure with the impersonation
USE master;
GO

CREATE PROCEDURE impersonate_procedure
--WITH EXECUTE AS OWNER
AS
BEGIN

EXECUTE AS LOGIN = 'sa';
CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

END;
GO


Getting duplicate record errors like "Duplicate Entry for key 'PRIMARY', error code 1062" when importing a full backup into slave

From your steps, you are most likely backing up and starting replication from a different position:

  Get the GTID of the master by checking the variable "gtid_binlog_position". Save this value.

Do not use that value, between that time and the time the backup was taken (even if seconds), more rows could have been changed, which means you will not get an accurate representation of the start position- being the most probable cause of the duplicate key errors. You are taking the backup with:

  --single-transaction --gtid --master-data

You must be using InnoDB for single transaction to work properly, otherwise, your other tables will not be consistent. Then, use the binary log coordinates or the gtid position written near the start of the dump .sql file to kickstart your slave.

The mistake may be because on the official guide it mentions the SELECT of the variable, but that would only work on a running replication if the master is not being written or all the tables are locked. The same guide mentions that using the feature integrated into mysqldump or xtrabackup is a better option.

Here it is a full guide for GTIDs on MySQL (some options and features change, but it may be a better overview for the overal procedure): https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/


Creating indexes vs Rebuilding indexes (larger db/log file)

I'd be a bad human being if I didn't start by saying this:

YOU NEED TO START TAKING BACKUPS

After looking at your other questions, I'd be an even worse human being if I didn't say this next:

MOVE TO A NEW SERVER

It sounds like there's a more serious problem here if you're running into data corruption over and over again that repairing isn't fixing. Simply moving to a new database on the same server may lead you right back into corruption.

With that out of the way:

  Do I need to rebuild nonclustered indexes after I create them?

Rebuilding indexes immediately after creating them doesn't really do anything for you. A newly created index is about as "optimized" as an index can get. Rebuilding indexes should be a rare event, typically reserved to correct an issue that isn't fragmentation. Rebuilding indexes is a size-of-data operation. However big your largest index is is how much space your log file needs when it's rebuilt.

  How do I keep my database as small as possible?

Check your recovery model. If you're in Full, you need to take log backups to control the size of the log file. Typically, more frequent will keep them smaller. If you're not in the mood to take log backups, put your database in Simple recovery. If you're unsure about which recovery model is right for you, start here.
If you're dead set on rebuilding indexes in the future, try using the SORT_IN_TEMPDB option.
If you're on Enterprise Edition, or SQL Server 2016 SP1 Standard Edition, you can use Page or Row compression to keep index sizes down.
Look at archiving data. The best way to keep database sizes small is to keep data small.
If they suit your workload and query patterns, filtered indexes can help, because you're not putting all the table data into an index.
Make sure your tables have clustered indexes. Heaps can do weird things to space used.
Check your data types. Are you using CHAR or NCHAR? Are you using NVARCHAR where VARCHAR would do? DATETIME where you just need a DATE? If you're worried about database sizes at the 4GB mark, bytes saved here can matter.

  Why did my log file grow so much?

Memory seems to serve that when you issue the REBUILD ALL command, this rebuilds all your indexes in a single transaction, whereas issuing individual CREATE statements will give your log backups or Simple recovery model CHECKPOINT magic a chance to kick in.

If you're looking to bring some sanity to index maintenance, check out Ola Hallengren's free scripts, and some examples of how to not run them foolishly here.


ALTER TABLE SWITCH fails with indexed view

You need an indexed view on the staging table that matches the definition of the one on the production table, and indexes on the staging view that match every index on the production view.

See SqlFiddle

The idea is that the engine must replace every index, including the ones declared on the views. If it has build an index (ie. if there is an index on production but not on staging) then the switch will fail. Also all constraints, filters etc must match so that the engine knows that the data is valid (staging data will not violate production constraints).

Not sure why you need outer joins or anything similar, this should be straight forward.


You need to work with your storage vendor to figure out why the writes are being torn. If it's happening to tempdb it's very likely that it's happening to another database as well.

This is especially true if it's happened more than once.


select id from user_input
union all
select id from main_set where not exists(select * from user_input);

--edit based on your comment

select *
from main_set
where id in(select id from user_input) or not exists(select * from user_input)

or, seeing as you mention performance, perhaps you should try a join as an alternative:

select main_set.*
from main_set left outer join user_input on(user_input.id=main_set.id)
where user_input.id is not null or not exists(select * from user_input)


How to install Microsoft SQL Server 2014 Standard Edition on trial?

Evaluation Edition is always Enterprise Edition. If you want to run comparisons, get your admins to give you Standard Edition rather than Evaluation Edition.

For more proof, check Books Online, which notes that there is an Enterprise Evaluation, but not a Standard:

https://msdn.microsoft.com/en-us/library/ms174396.aspx


The documentation says:

  PRAGMA index_info(index-name);

  This pragma returns one row each column in the named index. The first column of the result is the rank of the column within the index. The second column of the result is the rank of the column within the table. The third column of output is the name of the column being indexed.

> pragma index_info(comments_idx);
seqno       cid         name
----------  ----------  ----------
0           0           books