SQL Server 2016 Collation and Internal table Glitch?

I have looked at the internal table columns collations across most of my test instances and they are all either SQL_Latin1_General_CP1_CI_AS or Latin1_General_BIN. Some instances are SQL Server Express LocalDB, which are always SQL_Latin1_General_CP1_CI_AS at the instance level (unfortunately), but I have a 2017 Express installed with Korean_100_CS_AS_KS_WS_SC at the instance-level and a 2019 CTP 2.3 installed with UTF8_BIN2 at the instance-level. I have DBs restored into the 2019 instance (from CTP 2.2, not an earlier version as in your case) with collations of French_100_CI_AS_SC_UTF8 and French_100_CI_AS.

I have another SQL Server 2017 Express instance currently using Estonian_100_CS_AS_SC at the instance and DBs levels, and that instance has been changed using the -q option many times for testing said option. It even has a Contained DB (containment_type = PARTIAL), and all columns in Internal Tables are either SQL_Latin1_General_CP1_CI_AS or Latin1_General_BIN, no exceptions.

I would not worry about these columns. They are clearly all MS Shipped objects that desire to keep their specific collation. And you will never have a time when 100% of collations are what you set the instance to. There are collations used for meta-data that have their own static collation. There are collations coming from expressions in system catalog views and functions with explicit collations set, etc. You can use the following query to see all of the collations that are not what you set the instance and database to:

SELECT OBJECT_SCHEMA_NAME(col.[object_id]) AS [SchemaName],
       ISNULL(obj.[name], OBJECT_NAME(col.[object_id])) AS [ObjectName],
       col.[name] AS [ColumnName],
       col.[collation_name] AS [Collation],
       obj.[type_desc],
       ISNULL(obj.[is_ms_shipped],
              OBJECTPROPERTY(col.[object_id], 'IsMSShipped')) AS [IsMSShipped],
       col.[object_id]
FROM   sys.[all_columns] col
LEFT JOIN sys.[all_objects] obj
       ON obj.[object_id] = col.[object_id]
WHERE  col.[collation_name] IS NOT NULL
AND    col.[collation_name] NOT IN (
                         CONVERT([sysname], SERVERPROPERTY('collation')),
                         CONVERT([sysname], DATABASEPROPERTYEX(DB_NAME(), 'collation')))
ORDER BY [SchemaName], [ObjectName], [ColumnName];

Some objects are not in sys.all_objects, such as sys.pdw_nodes_partitions, which is the same object_id everywhere (across instances and even versions of SQL Server, but did not exist in SQL Server 2012):

SELECT OBJECT_SCHEMA_NAME(-1046288262) AS [SchemaName],
       OBJECT_NAME(-1046288262) AS [ObjectName],
       OBJECTPROPERTY(-1046288262, 'IsExecuted') AS [IsExecuted],
       OBJECTPROPERTY(-1046288262, 'IsView') AS [IsView],
       OBJECTPROPERTY(-1046288262, 'IsMSShipped') AS [IsMSShipped];

Still, I am unsure how to explain the results you saw for Test 1. You said:

  Will return the internal tables with Old collation.

Based on everything I found across all of those versions, etc in the top part of this answer, it should not ever be possible to get a column in sqlagent_jobs to be anything other than SQL_Latin1_General_CP1_CI_AS. This could be a fluke with the database upgrade process when creating those internal tables. The "CONTAINED_FEATURES" tables did not exist in SQL Server 2012, so when you restored into SQL Server 2016, they had to be created at that point. And your 2016 collation is SQL_Latin1_General_CP850_CI_AS, which is the only connection I see so far.

I WAS ABLE TO REPRODUCE THIS!! I backed up a DB from SQL Server 2012 LocalDB that had a collation of Latin1_General_100_CS_AS_KS_SC. I then restored that into SQL Server 2019 (the instance-level collation being UTF8_BIN2). Checking the internal tables I saw that the 3 queue_messages_* tables and the filestream_tombstone_2073058421 table (all of which existed in SQL Server 2012) still had a collation of Latin1_General_BIN. BUT, the sqlagent_* tables and plan_persist_* tables (none of which existed in SQL Server 2012), all had a collation of Latin1_General_100_CS_AS_KS_SC (same as the DB that was restored).

I then backed up a DB from SQL Server 2014 LocalDB that had a collation of Latin1_General_100_CS_AS_KS_WS_SC. I then restored that into SQL Server 2019 (the instance-level collation being UTF8_BIN2). Checking the internal tables I saw that the 3 queue_messages_* tables and the filestream_tombstone_2073058421 table (all of which existed in SQL Server 2012) still had a collation of Latin1_General_BIN. BUT, this time the sqlagent_* tables (which did exist in SQL Server 2014), all had a collation of SQL_Latin1_General_CP1_CI_AS. The plan_persist_* tables (only 2 of the 6 existed in SQL Server 2014), all had a collation of Latin1_General_100_CS_AS_KS_WS_SC (again, same as the DB that was restored).

I changed the compatibility mode of the DB that was restored from SQL Server 2012 to be "SQL Server 2019 (150)", but that did not fix the collation issue.

This is clearly a problem with the Database Upgrade process, and has nothing to do with the sqlservr.exe -q method of changing all collations on a server (which is officially undocumented and unsupported, but I did document it here: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?). I have reported this bug to Microsoft via the barely functional, substandard-even-by-2008-2010-standards reporting site, UserVoice: Restoring databases into newer versions of SQL Server will create missing Internal Tables with incorrect collation.

Regarding Test 2:

  collation = SQL_Latin1_General_CP1_CI_AS

Yes, it is to be expected that those internal tables will use that old, should-have-been-removed-by-now, collation.

Regarding Test 3:

  Nothing returns because I converted it in 2012.

Not true. Nothing returns because:

You filtered out the 3 queue tables via AND object_name(c.object_id) NOT LIKE 'queue%', and those use Latin1_General_BIN.
You filtered out all of the other tables via WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'.


how to design movies with show times database structure

I would also suggest the following:

Add a movie genres table - as a movie can have one or more genres
Split up the movie_showing table as follows:

movie_showing - id, movie_id, cinema_id, details and restrictions at cinema level (this means the movie is showing at a specific cinema)
movie_showing_times - movie_showing_id, availability_status, showdate, showtime,  show details (prices, cinema showing, 3D or not, other offers for example half price etc)


SQL Database size doesn't match with the total table sizes in the database

Depending on the RDBMS being used, it is possible that the large file size as compared to summed-up table sizes is caused by any or more than one of the following:

Last database cleanup (in terms of table deletion, record deletion, etc) did not affect any disk reclamation by the OS/RDBMS
Last database cleanup does not normally affect disk space reclamation by OS/RDBMS
Indexes/constraints are being stored in other files. You won't see these when checking just table sizes.
No shrinking has been performed, though that may also possibly end up as first and/or second in this list
A possible miscalculation of table size retrieval (I just need to add this because it happens to some) and so the numbers don't tally
And maybe a few others.. Hopefully the stackers can add

I'm not saying these are definite items that impact your situation. I just mean that these may occur, and not just in your situation..

One other thing. It can also depend on the database settings for new disk allocation sizes. Say for example one allocation for a certain DB is 1GB. Once the usage exceeds that, another 1GB is added, regardless of whether or not you consumed all of it -- at the moment more allocation is required, it gets set.


Postgres sharding installation with citusdb

It looks like pg_shard files are not installed. You will need to build pg_shard from sources and install it using make install within pg_shard source folder.

Please be aware that pg_shard has reached end of life, and it is included in  recently released (Citus 5.0).

You can also get Citus from PGDG if you are on RPM based system.

sudo yum install -y
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-ami201503-95-9.5-2.noarch.rpm
sudo yum install -y citus_95


Should I add SET NOCOUNT ON to all my triggers?

Personally, I would recommend it - I can't think of a reason not to, unless you have a trigger where you specifically want to call out the fact that it's doing additional work behind the scenes.

I wrote a short article about things to watch for when writing triggers, and this is one of them:
http://dave.brittens.org/2012/03/29/writing-well-behaved-triggers/

tl;dr version:

Cut the chatter. Use NOCOUNT.
Make sure your trigger can handle multiple rows.
Prevent unbounded trigger cascading and recursion.
Avoid horrible performance of the INSERTED and DELETED virtual tables.


Why do wildcards in GROUP BY statements not work?

GROUP BY A.* is not allowed in SQL.

You can bypass this by using a subquery where you group by, and then join:

SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2
        GROUP BY FKey
      ) AS B
    ON A.PKey = B.FKey ;

There is a feature in SQL-2003 standard to allow in the SELECT list, columns that are not in the GROUP BY list, as long as they are functionally dependent on them. If that feature had been implemented in SQL-Server, your query could have been written as:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

Unfortunately, this feature has not yet been implemented, not even in SQL-Server 2012 version - and not in any other DBMS as far as I know. Except for MySQL which has it but inadequately (inadequately as: the above query will work but the engine will do no checking for functional dependency and other ill-written queries will show wrong, semi-random results).

As @Mark Byers informed us in a comment, PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation.


What exactly does PostgreSQL (or other databases) do internally when you "prepare" a query rather than just running it directly?

Actually, the difference between a parameterized statement and a prepared statement is not that big in PostgreSQL: the former is internally an unnamed prepared statement, and the main difference is that it is automatically deallocated when the next unnamed prepared statement comes along.
When you prepare a statement, PostgreSQL remembers that statement in your current session (nothing is shared between sessions).
For the first five executions, PostgreSQL will generate a custom query plan that takes the parameter values into account. After that, PostgreSQL will generate a generic plan that is independent of the query parameters, and if that generic plan is estimated to be no more expensive than the previous custom plans, it will be used from that point on.
For insert statement like you describe, PostgreSQL will always switch to the generic plan. The main advantage there is that the query doesn't have to be planned any more. With simple statements, planning time can exceed execution time, so the savings can be considerable.
PostgreSQL only caches query plans for prepared statements and statements in PL/pgSQL functions.
The other advantage of prepared (or parameterized) statements is that they avoid the danger of SQL injection.
If you have to insert a lot of rows, using COPY would be even faster.


Index creation in extremely slow in Mysql Aurora 5.7

You have a lot of rows., every row has to be inserted into the index, that takes time, live with it.

You can try to use only one index for all columns

CREATE INDEX name
ON yourtable(column1,column2,column3,column4...colmn9);

That takes also long, but should be faster


Optimize BETWEEN Query in SQL Server Azure

To make this query run fast you will need the data ordered by timestamp.
You have a few options
If you use a clustered columnstore index you need to FIRST order the data in the table by adding a clustered index on Timestamp and then create the columnstore index using maxdop = 1 and with drop_existing.
full syntax https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-ordered-cci
You could get rid of the columnstore index and just use a clustered index on Timestamp.
You could use a nonclustered index on Timestamp and include all the other indexes in the table (might be OK if you only have 5 columns depending on the datatypes).
You could partition the table by timestamp - this is another longer conversation


Explicitly update a table after sp_tableoption "text in row" advices wanted

Sounds to me you're missing indexes for the queries your application actually uses. select ntextcol1, ntextcol2 from table takes 5 min is not the point: this is a table scan that retrieves all LOBs. Your application (hopefully...) never issues this query. Pulling ntext (you should be using `NVARCHAR(MAX) btw) in row or pushing them out of row is not what you should be looking at.

Read General Index Design Guidelines and all the related articles in the section (clustered index design guidelines, non-clustered index guidelines etc etc). And learn how to identify the problem queries in your app, the indexes should target those queries: Troubleshooting Performance Problems in SQL Server 2005, Waits and Queues


How to package a PostgreSQL extension as a binary .deb file in Ubuntu?

Is there a way to combine the manual approach above and the PGXS
  approach to automatically create a PostgreSQL extension as a .deb
  file?

Sure. First you need to create a debian/ directory with a control and rules files copied from other extensions. There are many examples in the PGDG Apt repository. Add the following line in your APT sources (bionic is the codename for Ubuntu 18.04) if you don't have it already:

deb-src http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

To see the list of packages: apt search 'postgresql-*'

To download the sources: apt source name-of-package

Once your debian directory is good and the dependencies are installed, basically you build the package for the extension with dpkg-buildpackage -rfakeroot -b like any other Debian package.


is there any need to index b again as it is already a under primary

If you have index on (a,b) and a query with b = ? then that query can't make efficient use of that index. There are DBMS that can make use of the index in a decent way, but mysql is not one of those.


MS Access displays alle columns in SQL Server tables as "deleted"

It looks like the linked SQL Server table does not have a unique index on in. MS Access uses the MS Jet DB engine that is designed around a keyset model. Actions like inserts, updates, etc, uses this keyset. This may be missing from your linked SQL Server table. Click here for more detailed info.


MySQL - [Note] Got an error reading communication packets

After spending lot of time I am able to resolve my issue and reason is that in Openshitf/kubernetes we have section to verify readiness of mysql like

  readinessProbe:
    tcpSocket:
      port: 3306
    initialDelaySeconds: 20
    periodSeconds: 2
    timeoutSeconds: 1

So it was pinging every two seconds on 3306 port, i changed it to below and its resolved the issue,

 readinessProbe:
        exec:
          command:
          - sh
          - -c
          - "mysqladmin ping -u root -p${MYSQL_ROOT_PASSWORD}"
        initialDelaySeconds: 5
        periodSeconds: 2
        timeoutSeconds: 1


Is it possible to backup a database that is not ONLINE?

It appears that no you can not backup a database in an offline state based on this comment from BOL.  Offline and Emergency appear to be the exceptions.

Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. For information about database states, see Database States.

However that being said the chances of the database state being the reason for your backup problem are slim.  As said in the comments by @StrayCatDBA MSDB is required to do backups (among other things).  You can look at the error for a backup by right clicking on your maintenance plan (assuming you are using one) and selecting view history.

Once you are in the Log File Viewer you can see what errors you have.  Click on the + to drill down to details and then select each of the lines with the red X in turn to see what the current error is.

In this particular case we had a maintenance plan that was trying to back up databases that no longer existed.  Once they were removed from the plan we stopped getting the errors.


Why temdb and log file size is not reduced by SQL Server

Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation.

Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too.

TempDB is re-initialized whenever Sql Server service is restarted. In most of the cases, this effectively shrinks the tempdb data and log files.


Convert varchar2 column to number in Oracle

You are sort of almost there. You sound like you're doing a conversion of a selected value rather than a filter and you need to cater for NULLs so:-

SELECT TO_NUMBER(NVL(emp_salary,'0'),'9999D99','nls_numeric_characters=,.')
FROM emp_details


Alternatives to DBCC PAGE for working with page numbers in SQL Server

Yes, you can use sys.dm_db_database_page_allocations if you're on SQL Server 2012 or greater.

For example, this will give you information about tempdb -- the number two passed in is database id.

SELECT *
    FROM sys.dm_db_database_page_allocations(2, NULL, NULL, NULL, 'DETAILED') AS dddpa;


With and without autoincremented id performance

Let's categorize things into 3 buckets:

Case 1: No natural PK.  Then do have an AI.  Soner or later, you will find that having it makes maintenance easier.  Example: Deleting or modifying a specific row due to a data error.  That is, do not depend on the hidden PK that InnoDB will generate.

Case 2: There is a natural PK and you need an index by that column(s).  Bingo!  A PK is an index, so you can get the performance benefit of the index inherent in a PK.  Plus you avoid the space, clutter, etc, of the AI.

Case 3: Natural PK possible, but indexing it is not needed.  Now we are in a gray area.  Flip a coin.  Or, since a VARCHAR is slightly slower for indexing, pick the INT AI.

Case 4: (Yeah, I started with 3 buckets.)  If the table is PARTITIONed, a lot of additional considerations come into play.  I won't go into them in this Q&A.

Let me provide an example of Case 2 -- a many-to-many mapping table.

A lot of people build essentially

CREATE TABLE A_to_B_mapping
    id INT AUTO_INCREMENT,       -- not used
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id),
    INDEX(id_a),  -- for joining from `A` to `B`
    INDEX(id_b)   -- if going the other way
)

But this would be much better:

CREATE TABLE A_to_B_mapping
    -- no AUTO_INCREMENT,      -- saving space
    id_a,  -- link to table A
    id_b,  -- link to table B
    PRIMARY KEY(id_a, id_b), -- PK, covering, unique, and for joining from `A` to `B`
    INDEX(id_b, id_a)   -- if going the other way, now 'covering'
) ENGINE=InnoDB;        -- so PK is 'clustered'

A "Covering" index is an INDEX that has all the columns needed anywhere in the SELECT.  A non-covering index must reach into the data BTree (after reaching into the index BTree) to get the rest of the columns.  That is, "covering" is more efficient.

Note:  Even if the ids are VARCHAR, the benefit of 'covering' outweighs the string-vs-int argument.

More on many-to-many:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Another Case 2 example:  wp_postmeta is inefficient .  That applies to other EAV schemas, as well.

I find that 2/3 of the time, a 'natural' PK can be made from a column (or combination of columns).


I would think so. Especially if you don't want the status published on users.. If you want to add a status to one, you're going to special case code for one of the consumers to exclude?

I would keep it simple -- two separate tables. Or if they're immutable (you won't be deleting and updating) status's and you're only using them on the one table, you can use an ENUM macro/check constraint.


Do you have a reason to believe that they will perform differently? If so, why not test them on your RDBMS and with your data? In general I would say to start with the simplest query, test performance, and only attempt something more complicated (such as option 2) if necessary.

You didn't list an RDBMS so I'll give a SQL Server example. The SQL Server query optimizer doesn't directly work with the SQL that you write. It transforms it into an internal format for optimization. The following image describes the various phases of optimization and is borrowed from Query Optimizer Deep Dive - Part 1 by Paul White:

Here's my test query against the publicly available Adventure Works 2014 database:

SELECT COUNT(*)
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNUmber = '2E53-4802-85'

I can get a representation of the query tree after simplification with the undocumented trace flag 8606:

*** Simplified Tree: ***
        LogOp_GbAgg OUT(COL: Expr1002 ,)

            LogOp_Select

                LogOp_Get TBL: Sales.SalesOrderDetail Sales.SalesOrderDetail TableID=1154103152 TableReferenceID=0 IsRow: COL: IsBaseRow1000

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [AdventureWorks2014].[Sales].[SalesOrderDetail].CarrierTrackingNumber

                    ScaOp_Const TI(nvarchar collate 872468488,Var,Trim,ML=24) XVAR(nvarchar,Owned,Value=Len,Data = (24,506953514552564850455653))

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1002

                    ScaOp_AggFunc stopCount

                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)

The above is an internal representation of your query plan. The actual steps in that query plan aren't important here. What is important is that I get the exact same simplified tree for this query:

SELECT COUNT(*)
FROM
(
    SELECT *
    FROM Sales.SalesOrderDetail
    WHERE CarrierTrackingNUmber = '2E53-4802-85'
) t;

Effectively, that means that SQL Server rewrites those two queries to the exact same thing before continuing with optimization. This means that there will not be a performance difference between the two of them. As expected, they have the exact same query plan:


Why is "select join insert" so much faster than "loop insert"?

QUERY #1
Each time you do an INSERT, you are doing this under the hood
SET @sql = 'insert t select null';
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times.
QUERY #2
Running insert t select null from(, you fully parse, compile, execute and deallocate structures for the prepared SQL statement ONCE !!!
The execution of the Cartesian joins is really a self-contained atomic operation. Though it does perform iterations ( O(n2) running time ) to form the result table, it is not tethered by creating/destroying query parsing structures with each iteration as you are doing in the stored procedure (despite an O(n) running time). The execution plan of the single INSERT may be uglier to behold, but will surely run faster.
The inline queries are most likely memory tables. The resulting join could be a temporary MEMORY table or temporary MyISAM table. Either way, the formation of the result set is a single operation.
CONCLUSION
Inserting 2 million rows on a single INSERT is always faster that than doing an INSERT of a single row 2 million times. In principle, this would be more evident if you connected, inserted and disconnected. Connecting once beats connecting 2 million times (See my post How costly is opening and closing of a DB connection?)


I have delete database from mysql but storage is not freed

You may find this surprising, but there are lots of little transactions that occur inside ibdata1. I wrote about this back on Jan 14th : AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?
Even though MySQL 5.6 has innodb_file_per_table on by default, the only way ibdata1 can grow is by having lots of transactions. If you look at this diagram:

you will see that ibdata1 has 1023 rollback segments for supporting MVCC and Transaction Isolation. In a heavy-write DB environment, ibdata1 can grow. I have discussed this before in my old post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
In that post, I mentioned the following:
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

Thus, I would expect ibdata1 to outgrow your current disk allocation if you chose a number too small.
BTW Where does the ibdata1 and the transaction logs actually reside ?
When you run show variables like 'innodb%home_dir'; you find those two folder

/rdsdbdata/db/innodb
/rdsdbdata/log/innodb

You cannot just reach into it and expand it that easily.
Just to make sure you have nothing of yours in the database, run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

If you get nothing back, then you can reasonable assert what I just said. Otherwise, go delete your data.
You may want to check for binary logs as well. Just run one of the following:
SHOW BINARY LOGS;
SHOW MASTER STATUS;

You may or may not have rights to purge them. Amazon may not want you to touch them since they control binary logging for the sake of spinning up read-only Slaves in the cloud.
SUGGESTIONS

Get more disk space allocated for the RDS Instance (if possible).
You may need to switch to a higher server model and then migrate.
Get away from RDS and switch to EC2 where you can micromanage everything, but you must shoulder the responsibility for monitoring the MySQL instance.
Contact Amazon for any pricing before you do anything (switching server models, migration to another RDS Instance, migration to EC2, etc).


Process Data cube from job on SQL server

There are 2 ways you can go about this. You can either create a service account for the sql agent service and give that account permissions on the olap server, or you can create a sql agent proxy account.

Personally I think the proxy account is the cleanest solution.

To create a proxy account refer to this msdn article: Create a SQL Server Agent Proxy.

This way you can for example create a user DOMAIN\OlapUserOnY which is only used for agent jobs running things on the Y server Analysis Services.

If you want to create a service account you have to be sysadmin on the SQL server where agent is running to be able to configure that on the Agent job.

Then on the agent job you just select either the proxy account or the service account in the run as field.

Refer to Create an Analysis Services Job Step to learn how to create a job and specify the account it's running under.

From that link:

  In the Run as list, select a proxy that has been defined to use the
  Analysis Services Command subsystem. A user who is a member of the
  sysadmin fixed server role can also select SQL Agent Service Account
  to run this job step.


When are InnoDB table index statistics updated?

You should look for this variable

mysql> show variables like '%metadata%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

According to the MySQL Docs, when innodb_stats_on_metadata is set (by default), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Once disabled, you would have to run ANALYZE TABLE on the InnoDB tables of your choice. Make sure you have SELECT and INSERT privileges.


MySQL Subquery hangs / terrible perfomance

The query is slow because the dateindex is used and that is not the best index for this query (or no index at all, please fix the EXPLAIN output). When the subquery is executed, the inventory_ID is already known and the price_type_ID is fixed but there is no composite index that can make this information useful.

Possible explanations for the single query running faster is that you either get cached results from previous execution of the query or it uses different index (and by luck this is a bit more efficient than the dateindex). You have several indexes, on (price_type_ID), on (inventory_ID) but no composite one. So, depending on statistics, the actual parameters and the index chosen each time, the performance will vary.

Try adding an index that will always do an index seek, no index or table scan (and get the price from the index, too, so no additional read form the table will be needed) and thus make both the query and the single query much faster:

ALTER TABLE _inventory_prices
  ADD INDEX price_type_ID__inventory_ID__date__brutto__IX        -- choose a name
    (price_type_ID, inventory_ID, price_date, price_brutto) ;


the first thing I'd worry about is zombie data. the data on your dead node might overwrite the data that you truncated. to be sure, I'd move that data somewhere else. also, delete the contents of the /commitlog and /saved_caches directories (or move them if you feel the need).

I'm assuming you have a suitable replication factor on your data? So there will be a copy of the data elsewhere in the cluster. When you re-add the node, the data will stream from the other nodes.


Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1

In a perfect world, you'd have a backup of your databases created with pg_dump. If you did, you could restore those database backups to your 9.1 version.  Otherwise, you could reinstall 8.4 somewhere else, copy in your original database files and make a backup of all your databases and users.  Then restore that backup to your 9.1

Install your old version of PostgreSQL

1.Install an instance of PostgreSQL 8.4, same version as you had on OpenSUSE 11.3.  This doesn't have to be on the same machine.  If it does, you could compile
a version from a .tar.gz. example: http://ftp.postgresql.org/pub/source/v8.4.8/postgresql-8.4.8.tar.gz

Decompress, configure, make and install to some other directory

2.Make sure your 9.1 isn't listening otherwise 8.4 will not be able to use the 5432 port.

3.start up postgres. somedirectory/data is where you have your 8.4 data files.

su - postgres

/postgresql84_directory/bin/pg_ctl start -D /somedirectory/data -l serverlog

4.Backup your databases to a text file

/postgresql84_directory/bin/pg_dumpall > all.dbs_8.4.sql

5.Stop postgresql 8.4

/postgresql84_directory/bin/pg_ctl -D /somedirectory/data -l logfile stop

Restore your 8.4 dump file to 9.1

1.If your 9.1 version isn't working anymore, you will probably want to reinitalize your data directory.

/postgresql91_directory/bin/initdb -D /postgresql91_directory/data/

2.Startup Postgresql9.1 again

3.Now restore the 8.4 dump file to your 9.1 instance

/postgresql91_directory/bin/psql -f all.dbs_8.4.sql


How to increase the value of a column in a table by a constant by consulting another table?

The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work:

update crs_regd r
set marks = marks + 5
where marks < 50
  and exists
      ( select 1
        from crs_offrd o
        where r. crs_cd = o. crs_code
          and o. crs_name = 'DBMS'
      ) ;


SQL Parameter Sniffing Possible Workaround

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects.

This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from parameter sniffing. (I'd always rather get to the accurate root cause rather than troubleshooting a simplified example, but I've gotta work with the code you posted, so here it goes.)

Your query only has one table in it (assuming that Widgets isn't a view):

SELECT * FROM Widgets WHERE CustomerId=@0

That means if you have a nonclustered index on CustomerID, some CustomerID values might produce a plan with a nonclustered index seek followed by a key lookup, whereas other parameters will do a clustered index scan across the Widgets table.

There are a few ways you could fix this scenario, and I'm going to list them in a generally safest-to-most-risky way:

Use OPTION (RECOMPILE) on the query. This does require a code change to add the line to the query, but then every execution of this query should get the most appropriate plan. The risk is higher CPU use for plan execution (although that generally won't matter in a single-table, single-predicate query like this where the plan will be ultra simple to generate).

Cache every variety of the plan. You noted passing the query in as a string will get each parameter to cache its own individual plan. While that will work today, it does bloat the plan cache (taking up more of SQL Server's memory). The risk here is that someone will turn on Forced Parameterization, a database-level option that will parameterize all of your queries whether they're sent in as strings or not, and suddenly you're back to troubleshooting this issue again.

The rest of these are valid solutions, but not for your single-table, single-predicate query. I'm only listing them here for posterity and clarity:

Use the OPTIMIZE FOR UNKNOWN query hint, or as we like to call it, optimize for mediocre. Requires a query change, and gives you a generally-good-enough plan. This will avoid random changes of the query plan due to parameter sniffing, but the risk is that it still won't be the most performant plan.

Use the OPTIMIZE FOR query hint with a specific CustomerID. This also requires a code change, and you would optimize the query for one of your larger customers. This will get a query plan that's great for big customers, and not-so-good for small customers. Small customer performance will go down, but the big customers won't cripple the system. The risk is that your customer distribution will change, and this will no longer be the right plan for the app as a whole.

Use a query plan guide. You can get exactly the query plan you want, and then pin the plan guide to memory. Here's the Books Online section on plan guides. I'm not usually a big fan of this because if your indexes change, the query plan won't take advantage of the new indexes. If your query changes, the plan guide will no longer be in effect. Suddenly the system might perform terribly, and people will have forgotten that a plan guide was helping before.

Use a stored procedure with manual logic. Have branches that call different stored procedures, one for large customers and one for small customers. This is only used for much larger, more complex queries that can have variations between minutes and hours (or not completing at all).


why is io_stall_writes_ms so much higher for tempdb?

Short Answer: Seeing higher IO stalls may or may not be a problem in an of itself. You need to look at more information to suss out if it you have an issue. It does seem a bit high, yes, but are you suffering? If so, it is probably because either your IO system is not handling the load right (because it can't, because you have everything on one drive or some other reason) or you are doing too much in TempDB (changing the first problem - the IO performance - is probably an easier and more efficient fix, but first determine if you have a problem)

The longer discussion/answer:

There are two questions at play here -

1.) What do I do when I see high IO Stalls?

First off, "high" is in the eye of the beholder. If you were to ask 10 DBAs what "too high" is for IO stalls you'd probably get 2-3 different answers with numbers in them, 5-6 "It depends" answers and one blank stare. My assumption is an average of 400ms is potentially too high here, especially when the other DBs are 2ms or lower for the average stall time.

Regardless of which database is seeing the high stalls you should approach it the same way. An IO stall is what it sounds like... An IO request taking longer than expected.. Stalling. These happen. They happen all the time in a system with resources being shared and finite resources (really all of our systems). They become an issue when the stalls become performance problems or lead to them. So I trust that you are looking here as a proactive part of monitoring or because you were experiencing performance issues that you are troubleshooting. We also don't want to get lost in just IO stalls. We are looking at a piece of the puzzle and not the big picture. It can be troublesome to just look at wait stats or file stats since SQL was last restarted because you are looking at all time and some maintenance window or heavy load window could skew counters. So make sure you look at the full picture.

But when I suspect I have a disk performance issue or see something off in a query like this, I normally follow a process that looks like:

Look at the wait statistics on the server. @swasheck shared a great link as a comment in a below answer. This takes you to Paul Randal's post on looking at and analyzing wait statistics in SQL Server. Go there.  What kind of waits are you seeing? Do you see waits related to IO performance (PAGEIOLATCH_*, IO_COMPLETION, WRITELOG, etc. ?). If you do this is another indication that you have some IO related performance issues, just like the IO stalls. But it gives you another form of agreement here.
Look at the IO performance. In particular, look inside of perfmon at the Physical Disk:Avg Disk Sec/Read and Avg Sec Disk Sec/Write counters. These measure your latency. Watch these counters over a period of time saved to a performance log file. What did you see for averages? If you are seeing numbers over 0.020 seconds (20ms) this could be an issue. If you see numbers over 40-50ms avg or higher is a more firm indication of a problem. Also look at your spikes? How high do they go and how long do they last? If you see spikes into the hundreds of ms and they last for tens or scores of seconds or more and/or happen frequently you are more likely to have an issue with your IO performance for your workload.
Look at your IO setup. What is it? Local disks? SAN? Storage Array? What kind of throughout and IOPs should you see out of this? Is it sufficient for what you are trying to do? You may have undersized your IO for your workload. Don't just look at your physical spindles, RAID settings, etc. Look at your paths to your disks. Are you pushing everything through a single 1GB link that you are sharing with a lot of other traffic? Can you look at disk performance metrics from the storage's perspective.

(Note: for this wait stats analysis and perfmon analysis - look at various periods and type of usage. Do you have different usage statistics at night than you do during the day? Batch processing windows? Maintenance windows where you rebuild a lot of indexes? Look at these tools during each of these periods and understand what you are seeing for each)

Another IO performance consideration here -

You said system DBs and User DBs are shared. Is this production? If so, that isn't always the best scenario. Are you also sharing log file and data files on the same drives? That isn't the best scenario either. What else shares this storage? In a world where you are worrying about spindles and raid groups and disks and have to make decisions on who gets the best performing disks, I tend to (as a general rule of thumb.. which aren't great to have in the DB world but this one tends to hold true) go with my fastest and most dedicated to TempDB (more on that below), then the log files, then the data files. In a world where you have a big pile of disks on a device like a NetApp, Dell Equal Logic or EMC VNX, etc. you don't need to necessarily worry about separation as much - but you still need to make sure that the total throughput and IOPs your system can deliver (and the switches, cards and cables between you and your disks can deliver) is sufficient for your workload.

2.) What are some reasons TempDB could be higher?

So TempDB is a database and it can have IO stalls like any other database as I just discussed. But what are some reasons TempDB can have higher reads? (not exhaustive, I welcome additions or thoughts in edits, other answers or comments) -

Because of your code - Are you using TempDB a lot in your code purposefully? A lot of temp tables and table variables created and destroyed? Doing a lot of things in TempDB like this? That isn't bad or good necessarily, but you might look at that and understand your intentional TempDB usage pattern.
TempDB is a shared workhorse - TempDB is one database that is used as a temporary space for user defined temporary objects and various work tables and operations used by your entire SQL instance. How many user DBs are there? What kind of workload do you see in general? TempDB is one resource for all things to share.
Inefficient queries and insufficient memory - Perhaps there are queries that aren't using indexes tightly enough or are doing large scan and sort operations. Large hash operations, and the memory on the server isn't sufficient for these. These operations will "spill" to TempDB as worktables behind the scenes. Sometimes this can be avoided with looking at your query plans and indexing or query tuning. Sometimes it happens (more so on warehouse workloads, I find). If you have enough memory, this can help, but these queries can still spill at times. Look a this as well.
Are you using Read Committed Snapshot Isolation level with a fair number of updates in your system? This can also result in increased TempDB activity.

The point is - TempDB is used in a lot of ways, and it doesn't surprise me at all to see it as one of your busiest, if not the busiest, database. It also doesn't surprise me when I see it as having the highest number of and highest average stalls of all databases at a client's site. It is the nature of its workload sometimes. Looking at some of the things I've mentioned here can certainly help you determine if these numbers indicate a problem and if so, how to go deeper in solving it.


SQL Join too slow on postgresql 9.0 vs postgresql 9.1

Seems the optimalization came with adding combined index on both two fields.

This was unnecesarry on postgres 9.1 machine but on postgres 9.0 adding combined index boosted the performance rocket like.

Thank you very much, for the answers!


SUGGESTION #1
Why use a subquery at all? Try it like this:
update Programs
set category="Adobe Suites"
where programName regexp '^adobe.*cs[1-9]$';

PROS AND CONS : Simpler query with no subquery, but a single-pass full table scan.
SUGGESTION #2
Put the distinct names in a separate table and then do an UPDATE JOIN:
create table ProgramNames
    select distinct programName
    from Programs
    where programName regexp '^adobe.*cs[1-9]$'
;
alter table ProgramNames add primary key (programName);
update Programs A INNER JOIN ProgramNames B USING (programName)
set A.category="Adobe Suites";
drop table ProgramNames;

PROS AND CONS : Clean UPDATE JOIN, but requires creating the temp table manually. This will be great if programName is indexed in the Programs table.
CAVEAT
There is great reason why you get that error message. It has to do with the MySQL Query Optimizer. Back on Feb 22, 2011, I wrote an answer to this post : Problem with MySQL subquery. I mentioned that there are times the MySQL Query Optimizer can make rows disappear intermittently in the optimization process. The join would not be clean internally. So, rather than rewrite it, MySQL simply kicks out an error so as not to have to rewrite. IMHO I think that maybe due to the way MySQL interacts with storage engines.
Give it a Try !!!


Getting Running Total from user defined function

You got the syntax slightly wrong. It should be:

SELECT
    x1.TotalAmount,
    SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate))    -- no AS TaxAmount here
        OVER (PARTITION BY x3.InvId
              ORDER BY x3.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,           -- it's ok here
    x2.DocumentType
FROM table1 x1
  INNER JOIN table2 x2 ON t1.id = t2.t1id
--- the rest of the code, that joins x3, etc
  ;

The alias should be only once, after the whole column expression, which includes the SUM(...) OVER (...)

You could use a derived table or a CTE as your idea (no need for temporary table). It would be equivalent:

SELECT
    t.TotalAmount,
    SUM(t.TaxAmount)                    -- here we use the alias, defined in
        OVER (PARTITION BY t.InvId      -- the derived table, below
              ORDER BY t.createddate
              ROWS BETWEEN UNBOUNDED PRECEDING
              AND 1 PRECEDING) AS TaxAmount,
    t.DocumentType
FROM
    ( SELECT
          x1.TotalAmount,
          [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount,
          x2.DocumentType,
          x3.InvId,
          x3.createddate
      FROM table1 x1
        INNER JOIN table2 x2 ON t1.id = t2.t1id
      --- the rest of the code, that joins x3, etc
    ) AS t ;


How can I improve this nested MySQL query to avoid redundant WHERE statement?

Reformulating your query:

SELECT
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT
          ( SELECT COUNT(DISTINCT s.userID)
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID)
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID)
            FROM tbl_actions AS a
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM
          ( SELECT
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter
          ) AS param
    ) AS cte ;


When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'

I'm not a PostgreSQL guru, but according to PostgreSQL docs:

DateStyle (string)
Sets the display format for date and time values, as well as the rules
for interpreting ambiguous date input values. For historical reasons,
this variable contains two independent components: the output format
specification (ISO, Postgres, SQL, or German) and the input/output
specification for year/month/day ordering (DMY, MDY, or YMD). These
can be set separately or together. The keywords Euro and European are
synonyms for DMY; the keywords US, NonEuro, and NonEuropean are
synonyms for MDY. See Section 8.5 for more information. The built-in
default is ISO, MDY, but initdb will initialize the configuration file
with a setting that corresponds to the behavior of the chosen lc_time
locale.

Now, looking at Section 8.5.2 Date/time Output

The output format of the date/time types can be set to one of the four
styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date
format), or German. The default is the ISO format. (The SQL standard
requires the use of the ISO 8601 format. The name of the “SQL” output
format is a historical accident.) Table 8.14 shows examples of each
output style. The output of the date and time types is generally only
the date or time part in accordance with the given examples. However,
the POSTGRES style outputs date-only values in ISO format.

+---------------------+------------------------+------------------------------+
| Style Specification | Description            | Example                      |
+---------------------+------------------------+------------------------------+
| ISO                 | ISO 8601, SQL standard | 1997-12-17 07:37:16-08       |
+---------------------+------------------------+------------------------------+
| SQL                 | traditional style      | 12/17/1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+
| Postgres            | original style         | Wed Dec 17 07:37:16 1997 PST |
+---------------------+------------------------+------------------------------+
| German              | regional style         | 17.12.1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+

If you want an output format as YMD, you should use ISO instead of SQL.

set datestyle = 'ISO, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 2020-02-01 00:00:00 |

set datestyle = 'SQL, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 02/01/2020 00:00:00 |

db<>fiddle here


PostgreSQL Failover - What tools should I use?

If you are interested in synchronous DB Replication and Failover, I have a suggestion. You could look into using two tools:

DRBD (Disk Replicated Block Device) provides Disk Level Replication (synchronous) between disks on two different servers. The disk changes are replicated over the network. It is best to use a crossover cable on eth2 for the NICs using the 192.168.x.x netblock.
ucarp allows for DBVIP management and failover between two different servers.

You can use them in conjunction as follows:

DBServer1 has IP 10.1.2.30
DBServer2 has IP 10.1.2.40
DB VIP is 10.1.2.70

Setup ucarp on two different servers in such a way that each ucarp instance communicates with the other along a virtual router ID

DBServer1 will have

/usr/local/sbin/ucarp -v 200 -p sometagname --interface=eth2 -a 10.1.2.70 -s 10.1.2.30 -b 3 -r 5 --upscript=/usr/local/sbin/vip-up.sh --downscript=/usr/local/sbin/vip-down.sh --neutral -z -B

DBServer2 will have

/usr/local/sbin/ucarp -v 200 -p sometagname --interface=eth2 -a 10.1.2.70 -s 10.1.2.40 -b 4 -r 5 --upscript=/usr/local/sbin/vip-up.sh --downscript=/usr/local/sbin/vip-down.sh --neutral -z -B

What's the reason -b (broadcasts) is 3 on one server and 4 on the other? In case both servers are restarted, the server with the lowest -b will bring ucarp first. As for -r, that is dead ratio. Thus, DBServer1 will come up in 15 seconds (3X5) and DBServer2 comes up in 20 seconds (4X5).

Let's say DBServer1 crashes. ucarp on DBServer2 will check for 20 seconds for a handshaking fro ucarp on DBServer1. If nothing is detected, the vip-up.sh script on DBServer2 will take control of the DBVIP.

OK all of this is just for failover and DBVIP management. What about the PostgreSQL database? Surprisingly, PostgreSQL is only running on one server at a time. Whoever has the DBVIP should have DRBD in Primary state. This ties back in to the vip-up script. How do you script it?

Here is /usr/local/sbin/vip-down.sh script (conceptual)

#! /bin/sh
exec 2> /dev/null
/sbin/drbdadm disconnect drbd0
/sbin/drbdadm  primary drbd0 &&
/bin/mount postgres-data-folder /mnt/drbd0
/sbin/ip addr add 10.1.2.70/24 dev eth2
/sbin/service postgres start &&
/usr/local/sbin/vipmon.sh &
touch /tmp/vip-up

Here is /usr/local/sbin/vip-down.sh script (conceptual)

#! /bin/sh
exec 2> /dev/null
/sbin/service postgres stop
/bin/umount -l  /dev/drbd0
/sbin/drbdadm secondary drbd0
/sbin/ip addr del 10.1.2.70/24 dev eth2
/bin/rm /tmp/vip-up
/usr/bin/killall -9 ucarp
/usr/local/sbin/vip-down.sh
kill `ps auxww | grep vipmon | awk '{print $2}'`

All you need to do is setup pg_hba.conf to make sure all users come via 10.1.2.70

Essentially, vip-up performs this sequence

Have DRBD go Primary
Mount postgres data folder on /dev/drbd0
Startup postgres
start vipmon process

Contrawise, vip-down performs this sequence

Shutdown postgres
unount /dev/drbd0
Have DRBD go Secondary

What about vipmon.sh? You could script that to check, in an indefinite loop, the state of postgres (it is running), check the DRBD device (can you still write to the posts data folder)

With this setup, you have postgres on the DRBD Primary and a disk level copy of the postgres data folder on the other DBServer (the DRBD Secondary). postgres is not running on the DRBD Secondary.

When a failover happens, you just need time for ucarp to detect is is safe to mount the postgres data, startup postgres, and acivate the vipmon script.

What great about this setup is that in the event of a failover, the DBServer that becomes DRBD Primary should have a 100% disk level copy of the server you failed away from. Thus starting postgres should level you at a consistent state. The transaction logs (in pg_xlog) should be in tact (less the intermittency due to failover)

I hope these suggestions help. Although I am a MySQL DBA, I use MySQL and DRBD at my employer's web hosting company on a regular basis. I installed MySQL/DRBD in the aforementioned manner. I did this once for a client using PostgreSQL/DRBD. It works great. It's Open Source. You just need to perform due diligence in learning DRBD and ucarp. This would include reconnecting DRBD after a failover, handling split brain scenario where both DB Servers go Primary, and things like these.


Recursive calls in stored procedure and temporary tables

Drop the table if it exists before the next run.

...
begin
drop table if exists test;
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
...

Or, as @dezso suggests, you could create the table only if it doesn't exist.

...
begin
create temp table IF NOT EXISTS test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
TRUNCATE test;
a := endIndex - startIndex;
...

As far as efficiency goes, this function works about as well as it can (O=n).  That is, each statement is executed a single time and the flow is pretty linear.  It would take some knowledge of the underlying tables to make any significant improvements.  Those improvements are likely to be measured in small percentages, not geometric or exponential.


What are implications for changing encryption from TRIPLE_DES to AES_128?

As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128?

The title is actually quite misleading. Changing from a symmetric to asymmetric key in of itself doesn't change too much, potentially the amount of processing power in order to encrypt or decrypt the key and associated data.

  ... returns a result with theh name: ##MS_ServiceMasterKey##, using the TRIPLE_DES algorithm.

This is a the SMK or Service Master Key for the instance and can't have the algorithm type changed. This is where the additional context is key, because currently there isn't anything you can do to change it except upgrade to a newer version. You're using sql-server-2008 which has about 9 months of extended support left (July 2019) and is over a decade old - in terms of cryptography that's quite the lifetime. Newer versions of SQL Server, starting with 2012 use AES for the Service Master Key (SMK).

  I need to updated the algorithm used for encryption on it.

Your only option, in this case, is to upgrade to something made in this decade (2012+).


Apparently, my CLR assembly function is causing deadlocks?

What version(s) of SQL Server are you using?

I do recall seeing a slight change in behavior in SQL Server 2017 not too long ago. I will have to go back and see if I can find where I made a note of it, but I think it had to do with a schema lock being initiated when a SQLCLR object was being accessed.

While I am looking for that, I will say the following regarding your approach:

Please use the Sql* types for input parameters, return types. You should be using SqlString instead of string. SqlString is very similar to a nullable string (your value?, but it has other functionality built in that is SQL Server-specific. All of the Sql* types have a Value property that returns the expected .NET type (e.g. SqlString.Value returns string, SqlInt32 returns int, SqlDateTime returns DateTime, etc).
I would recommend against this entire approach to begin with, whether or not the deadlocks are related. I say this because:

Even with deterministic SQLCLR UDFs being able to participate in parallel plans, you are most likely going to get performance hits for emulating simplistic built-in functions.
The SQLCLR API does not allow for VARCHAR. Are you ok with implicitly converting everything to NVARCHAR and then again back to VARCHAR for simple operations?
The SQLCLR API does not allow for overloading, so you might need multiple versions of functions that do allow for different signatures in T-SQL and/or PL/SQL.
Similar to not allowing for overloading, there is a big difference between NVARCHAR(4000) and NVARCHAR(MAX): the MAX type (having even a single one of them in the signature) make the SQLCLR call take twice as long as not having any MAX type in the signature (I believe this holds true for VARBINARY(MAX) vs VARBINARY(4000) as well). So, you need to decide between:

using only NVARCHAR(MAX) to have a simplified API, but take the performance hit when you are using 8000 bytes or less of string data, or
creating two variations for all / most / many string functions: one with MAX types, and one without (for when you are guaranteed to never go over 8000 bytes of string data in or out). This is the approach I chose to take for most of the functions in my SQL# library: there is a Trim() function which likely has one or more MAX types, and a Trim4k() version which never has a MAX type anywhere in the signature or result set schema. The "4k" versions are absolutely more efficient.

You are not being careful to emulate functionality given the example in the question. LTRIM and RTRIM only trim spaces, while .NET String.Trim() trims white space (at least space, tabs, and newlines). For example:

  PRINT LTRIM(RTRIM(N'      a       '));

Also, I just noticed that your function, both in T-SQL and in C#, only uses 1 of the 3 input parameters. Is this just a proof of concept, or redacted code?


Is there any way to find out when particular view was compiled?

I think that you can determine the time a view was last compiled querying the data dictionary view dba_objects:

sql> select last_ddl_time from dba_objects where object_type = 'VIEW' and object_name = 'MOO';

You can also see the text the view is based on in the dba_views dictionary view:

sql> select * from dba_views where view_name = `MOO`;


Efficiently search for a prefix range in SQL Server

If you use the >= and < operators you can return the required range by "increasing the second argument" by 1. Technically, replacing the last character ('5') with the next character ('6') in the character set.

Below is a query that returns all the rows in your range of A010 and A025. To get this I pass 'A026' as the second argument. The expression < 'A026' includes the value 'A0259456546'.

DECLARE @Test TABLE
(
    Val NVARCHAR(100)
) ;

INSERT @Test
VALUES ('A0004534543'),
       ('A01034950834'),
       ('A020043553'),
       ('A0259456546'),
       ('A0264565464') ;

SELECT  *

FROM    @Test

WHERE   Val >= 'A010'
        AND Val < 'A026' ;


Transaction log filling the drive if mirroring fails

What I see from your question is that you are not addressing the root cause, instead you are building solutions around the core problem which will resurface until you address the root cause - why does mirroring get suspended ? Did you see any errors in error logs (windows or sql server) ? Does it happen on a frequent basis, what are the patterns that contribute to this ?

Address the above ones first. You wont have to re-setup mirroring for 600GB DB once you address the root cause.

This is what I have done in my current environment -

Have a process to check for mirroring health like using powershell or sql agent job :

select db_name(database_id) as MirrorSuspendedDBs
        from sys.database_mirroring
    where mirroring_state_desc = 'SUSPENDED'
    and mirroring_role = 1

If it finds any db/s that are suspended, try to resume mirroring using

ALTER DATABASE dbname SET PARTNER RESUME;

if above fails (use error codes to catch errors, may be retry 2-3 times by sleeping in between a retry), generate a ticket or email for the DBA team.

This way, you know that something is wrong and address that for a permanent fix.

see this : Why Does the Transaction Log Keep Growing or Run Out of Space?

Edit:

  The problem is with the transaction log if one server fails, or if mirroring gets suspended for any other reason. If I understood the principle correctly, healthy server will retain the transaction logs as long as it doesn't get information from partner that everything is OK. So if mirroring fails, the HDD will get filled within several hours.

For mirroring or AlwaysON, the secondary has to be up for the tlog to get truncated and reused by sql server.

If your concern is that secondary goes down and your drive fills up due to Tlog not getting truncated, then look into switching to logshipping provided it does not break your RTO (how fast you can bring your system up) and RPO (how much data loss is acceptable to your business).

Failover for mirroring is almost instantaneous vs logshipping (if you dont have a witness configured) is manual.

You can even go with clustering - DR-1 (2 nodes in each side clustered) and mirroring between the 2 sites.

Again - preparing for failure is a good thing, but do test out all your scenarios when choosing between HA/DR technologies.


It seems like you are running SQL Server together with an application on one server.

SQL Server will consume all the memory it can by design. (The default MAX memory setting in SQL Server being 2147483647 MB.)

If you changed the default MAX memory setting to something else, then SQL Server will start consuming memory after a reboot for data cache, query plan cache, and for other SQL Server relevant components.

At some point in time the SQL Server will pass the MIN memory setting. SQL Server will not release memory back to the OS past this setting.

Later on the SQL Server instance will possibly reach the MAX memory setting. SQL Server will not pass this line for data cache and plan cache. However, the SQL Server process can use more than the MAX memory server setting, because some objects run outside of the MAX memory setting (depending on version) and SQL Server can overcommit memory. This is discussed in the following article

Memory configuration and sizing considerations in SQL Server 2012 and later versions

Your SQL Server will now consume memory between the MIN and MAX memory setting until you reboot the SQL Server instance.

  CAUTION: Reducing the MAX memory setting of a running SQL Server instance will purge the data cache in memory and result in a reduction in performance.

If your SQL Server instance is running on an application server and not on its own, then you might have to reduce the MAX memory setting for SQL Server to allow the application to have some share of the memory.

If your SQL Server instance is running on its own, then try configuring the MAX memory setting according to the following recommendation:

  ...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM...

Reference: How much memory does my SQL Server actually need? (SQLSkills.com)

There are some other good articles out their that explain how SQL Server uses RAM.

A Sysadmin’s Guide to Microsoft SQL Server Memory (BrentOzar.com)
Server Memory Server Configuration Options (Microsoft.com)
How much memory does my SQL Server actually need? (SQLSkills.com)


We actually ran into a similar issue at StackOverflow and Kyle blogged about it:

http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/

The problem can be a number of things:

Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields)
Client-side apps processing data row by row instead of pulling it all into memory and then doing whatever work necessary
Underpowered app server hardware (or in your case, client machines) that are paging to disk or churning on CPU.  I see this a lot on over-committed virtual machines.

To tune it, start by looking at Perfmon counters on your own machine.  My tutorial on it is at http://www.BrentOzar.com/go/perfmon, and it includes a list of counters to gather, how to analyze 'em, and how to interpret your bottleneck.


How do you write to a folder with spaces in it?

You should (pretty much always) enclose paths inside quotation marks.  So, like this:

declare @date varchar(25),
@sql varchar(1000);

set @date = cast(datepart(month, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(day, current_timestamp) as varchar(2)) + ' ' +  cast(datepart(year, current_timestamp) as varchar(4));
print @date;

set @sql = 'copy "D:\Data\ED_72.xlsx" "\\ehsintra3\ED 72 weekly\ED 72 ' + @date + '.xlsx"';
print @sql;

exec  xp_cmdshell @sql;

Putting paths inside quotation marks ensures the command-processor can understand where a path starts and ends.

Also, instead of using copy you probably want to look at either robocopy or xcopy.  The copy command is brutally weak and frail.

Also, a minor note, add semi-colons to the end of your statements in T-SQL.


MongoDB Replica Set: Can't Add Member/Connect

The issue was a result of having different values for replSet in /etc/mongod.conf. I thought the servers should be named uniquely -- this is incorrect. Each server in the replicaset must have the same value for replSet.


Can I restore a differential backup to the 2nd newest full backup?

No, that's not possible. You will not be able to start the backup operation.

What you are trying to do there could leave the database physically inconsistent if SQL Server allowed it. You would get a Frankenstein database with pages mixed from different points in time.


A value of thousands per second is pretty normal.  (Manually compute Handler_read_key / Uptime.)

Do the following to get a better feel for what it, and its siblings, are about:

FLUSH STATUS;  -- resets the Handler counters (and a few other things)
SELECT ...
SHOW SESSION STATUS LIKE 'Handler_%';

You will see read_key for "point queries" and for "range queries", possibly one each.  You will also see a larger value for a "range query" in read_next.

Handler_write% values indicate that a temp table needed to be built and reread -- such as for GROUP BY and/or ORDER BY.

I find the Handler values to be handy in comparing two queries -- bigger numbers is a good indication of a less-well optimized query.

If you like, present us with a small number of SELECTs, together with SHOW CREATE TABLE and EXPLAIN SELECT and the Handler% values.  We can go into more details 'by example'.

Handler_read_key

I think read_key is bumped by one in these cases:

Reaching into a table, either for a specific row, or to start a range scan.
At least once per table in a query with multiple tables due to JOIN or subqueries.
Once per row in a "nested loop join".  This is the usual case for a simple JOIN two tables.  It scans the first table (one Handler_read_key, many Handler_read_next) and reaches into the next table repeatedly (many Handler_read_key)

Think of it this way:  Most reads from a table work in 1 or 2 steps:

Use a "key" to find the row (or the first row of a range) --> Handler_read_key bumped by 1.
If multiple rows needed, then get the "next" row --> Handler_read_next bumped by 1 per extra row.  If using something like ORDER BY .. DESC, then bump Handler_next_prev.


Copy snapshot files from Publisher, Paste and Apply on subscriber in SQL Merge Replication

Yes - With Merge we can copy the snapshot files to the Subscriber and apply the snapshot locally using the Merge Agent -AltSnapshotFolder parameter.


Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits

These calls are all part of loading the Oracle OleDb provider.  Not sure why they would block instead of failing, but it suggests that your Oracle client setup is still not not right.

The 11g OraOLEDB.Oracle provider not only needs to access the correct TNSNAMES.ORA file.  It also needs to load the correct OCI.DLL, which it does through the path.

OraOLEDB.Oracle, like all OleDB providers, is a COM component.  So it's loaded with a call to CoCreateInstance().  The OleDb provider in turn calls calls LoadLibrary() to find OCI.DLL, and then (in the normal DLL-loading process) calls GetProcAddress() to find the expected DLL entry point functions.  Any time SQL Server calls one of these Win32 functions it enters a 'PREEMPTIVE_...' waits.

So you need to ensure that the LoadLibrary function call finds and loads the correct copy of OCI.DLL.  You typically do this by setting the system path so that the folder containing your desired OCI.DLL appears first, and rebooting.

You can verify you're loading the expected OleDB provider from an elevated powershell prompt by running

PS C:\WINDOWS\system32> ls  hklm:\SOFTWARE\Classes\OraOLEDB.Oracle

to discover the CLSID of the OleDb provider, and then looking it up

PS C:\WINDOWS\system32> ls  "hklm:\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}

Name                           Property
----                           --------
ExtendedErrors                 (default) : Extended Error Lookup Service
InprocServer32                 (default)      : c:\oracle\odac64\bin\OraOLEDB12.dll
                               ThreadingModel : Both
OLE DB Provider                (default) : Oracle Provider for OLE DB
ProgID                         (default) : OraOLEDB.Oracle.1
TypeLib                        (default) : {0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}
VersionIndependentProgID       (default) : OraOLEDB.Oracle

The full process is ProgId `OraOLEDB.Oracle' is resolved to a CLSID.  The CLISD is resolved to a .dll (InProcServer32).  The .DLL is loaded and it, in turn, runs LoadLibrary to load oci.dll.  Any link in that chain can break, including finding a copy of OCI.DLL of the wrong "bitness".  CoCreateInstance is bitness-aware, but LoadLibrary is not.

You can also test connectivity in powershell.  First ensure you are in a process with matching "bitness" to SQL Server.

PS C:\WINDOWS\system32> [system.environment]::Is64BitProcess

Then

PS C:\WINDOWS\system32> $con= new-object system.data.oledb.oledbconnection
PS C:\WINDOWS\system32> $con.connectionstring="provider=OraOLEDB.Oracle;data source=//server/xe;user id=xxx;password=xxx"
PS C:\WINDOWS\system32> $con.Open()


Modify MySQL foreign key related column without dropping foreign key relation

Yeah i think you have a SQL command like :

SET foreign_key_checks = 0;

which should be session based. Then you set =1 once you're done.


How can I insert a record into a table that depends on a foreign key linking to a previous table?

INSERT INTO Entry (EntrySequence)
VALUES ('new EntrySequence');

INSERT INTO Keyword (Keyword, EntryFK)
SELECT 'new Keyword', Entry.EntryID
FROM Entry
WHERE Entry.EntrySequence = 'new EntrySequence';

INSERT INTO Keyword_Priority (KeywordPriority, KeywordFK)
SELECT 'new KeywordPriority', Keyword.Id
FROM Keyword /* , Entry */
WHERE Keyword.Keyword = 'new Keyword'
/* AND Entry.EntrySequence = 'new EntrySequence'
   AND Entry.EntryID = Keyword.EntryFK */


Sql failover cluster issue with different edition setup ( enterprise with standard)

to solve the issue do an edition upgrade of sql server on the node 2.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-2017
It should not even require the service to be stopped.
At the end you have to restart the node 2


One way to do this is to look up the previous row for each item and check if that row has the same data. If so, we can delete the row:

DELETE c
FROM CommerceState c
CROSS APPLY (
    -- For each row, find the previous row for this item
    SELECT TOP 1 prev.id, prev.Listings, prev.UnitPrice, prev.Quantity
    FROM CommerceState prev
    WHERE prev.itemId = c.itemId
        AND prev.[Timestamp] < c.[Timestamp]
    ORDER BY [Timestamp] DESC
) d
-- If that row matches the current row, we'll delete the current row
WHERE d.Listings = c.Listings
    AND d.UnitPrice = c.UnitPrice
    AND d.Quantity = c.Quantity

Here is a full test script that creates your dummy data, runs the query, and confirms the results.

You can optionally add the following index in order to optimize the lookup of the most recent row, but it would depend on your workload whether the index is worth the extra overhead it incurs when inserting and deleting data.

-- Optionally add this index if you deem the tradeoff to be worthwhile
CREATE INDEX IX_CommerceState
    ON CommerceState (ItemId, [Timestamp])
    INCLUDE (Listings, UnitPrice, Quantity)

With the index, here is the query plan:


Let's move the problematic expression from WHERE condition to SELECT output list. EXISTS gives boolean, and SQL server don't want to display it directly, so we'll use CASE and convert it to readable form.

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
/*   There we insert our EXISTS moved from WHERE   */
/*              and wrapped into CASE              */
, CASE WHEN EXISTS ( SELECT  *
                     FROM GapsIslands AS b
                     WHERE b.ID = a.ID
                       AND b.SeqNo = a.SeqNo - 1 )
       THEN 'Exists'      /* EXISTS is true there, NOT EXISIS is false */
       ELSE 'Not exists'  /* NOT EXISTS is true there, EXISIS is false */
       END AS previous_SeqNo_exists
/* ----------------------------------------------- */
FROM GapsIslands AS a
ORDER BY SeqNo

fiddle

The query gives us the next output:

ID | SeqNo | RowNum | previous_SeqNo_exists
-: | ----: | :----- | :--------------------
 1 |     1 | 1      | Not exists
 1 |     2 | 2      | Exists
 1 |     5 | 3      | Not exists
 1 |     6 | 4      | Exists
 1 |     8 | 5      | Not exists
 1 |     9 | 6      | Exists
 1 |    10 | 7      | Exists
 1 |    12 | 8      | Not exists
 1 |    20 | 9      | Not exists
 1 |    21 | 10     | Exists
 1 |    25 | 11     | Not exists
 1 |    26 | 12     | Exists

It seems it is absolutely clear. SeqNo=1 have no previous value, because there is no such record at all, SeqNo=5 (and all another) - because there is a gap before.

When we use this EXISTS expression in WHERE the records with "Exists" will be removed, and we will obtain only records with "Not exists" (do not forget that there is additional NOT operator, so only records which gives FALSE will be returned).

ROW_NUMBER(), which works after WHERE, simply enumerates returned records in given ordering.

PS. The query under consideration (posted by OP) does not contain ORDER BY clause. That is bad. This may cause the records will be returned in random order. Nevertheless record numbers calculated by ROW_NUMBER() will be correct because the function have its own, local, ordering.


You'll need to declare the namespaces in the modify function.

Something like this:

DECLARE @xml xml = N'<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <CentralData>
    <SMData>
      <CentralSDItem>
        <ControlData>1234</ControlData>
      </CentralSDItem>
    </SMData>
  </CentralData>
</SMObjInfo>';
SET @xml.modify('
    declare default element namespace "DataService/1.0.0.0";
    replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1]
    with "6789"
    ');
PRINT CONVERT(nvarchar(max), @xml);

In your original xml fragment, you have declared the following namespace that is never used:

xmlns:i="http://www.w3.org/2001/XMLSchema-instance"

If your actual xml documents do make use of this namespace, and you want to modify those elements, you'd need to add the following declaration into the @xml.modify function:

declare namespace i="http://www.w3.org/2001/XMLSchema-instance";

Results (formatted for readability):

<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <CentralData>
        <SMData>
            <CentralSDItem>
                <ControlData>6789</ControlData>
            </CentralSDItem>
        </SMData>
    </CentralData>
</SMObjInfo>

I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com


It depends on what you are needing from the solution. If you want to 100% prevent duplicate errors you go with a constraint. If it is to minimize duplicate rows and prevent errors from happening you go with the conditional insert. If you need to prevent duplicate rows and the errors from attempting to insert a duplicate row you go with both.

If you are depending on the conditional insert you can get a duplicate row if someone is doing an insert and they do not add the condition as expected.

If you just use the constraint you will get errors that could be prevented (sometimes you want to know of the error) when attempting to insert the duplicate record.


ts_rank() is not meant to check for complete equality. It's a simple ranking function that basically just counts the number of matches in a document. The manual:

  Ranks vectors based on the frequency of their matching lexemes.

Consider:

select ts_rank(to_tsvector('eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('xxx eat apple yyy'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple eat apple'), phraseto_tsquery('eating apples'))
     , ts_rank(to_tsvector('eat apple eat apple eat apple eat apple eat apple eat apple eat apple eat apple eat apple'), phraseto_tsquery('eating apples'));

The rank is highest for the last expression just because there are many matches. The first two share the same rank.

  Is there any other type of queries to check that "eat apple" completely matches "eating apples"?

You could compare two tsvecor values for equality:

SELECT to_tsvector('eat apple') = to_tsvector('eating apples');

To be sure (and not depend on current locale settings), spell out the text search configuration:

SELECT to_tsvector('english', 'eat apple') = to_tsvector('english', 'eating apples')

Returns true for an exact match.
Not supported by a text search indexes, though, which only support the operators @@, @@@, <@, and @>. You could combine both. Like:

SELECT * FROM tbl
WHERE  to_tsvector(text_field) @@ phraseto_tsquery('eating apples')  -- with index support
ORDER  BY to_tsvector(text_field) <> to_tsvector('eating apples')  -- exact match first
        , ts_rank(to_tsvector(text_field), phraseto_tsquery('eating apples')) DESC

Sorts exact matches first because FALSE sorts before TRUE.


Maybe an error appears in the target database's alert log?  Run this:

SQL> show parameter background_dump_dest

...and go to the directory given to check the alert_<SID>.log.  Or do a trace of the imp session in the target database instance.  (Usually the errors appear in the exp / imp output themselves (saved to a log with LOG=log_file.log ) but I assume the export ran file with no warnings and completes with):

"Export terminated successfully without warnings."

When you are copying the file, are you sure it's not getting corrupted somehow? (md5sum remains the same)
Did you try importing into an empty schema so you don't have to set IGNORE=Y?
And when you wrote:

  The problem persists with rows=N and with only selected tables from this schema.

Did you mean no matter which one or two tables you choose, it still crashes?
Try simulating an import with SHOW=Y?


Can't select database for transaction log backup in maintenance plan

See this Connect item, which is almost identical to the scenario you're trying -- they're close enough that I would say that it's highly likely to be the same issue. I would attempt to reproduce the exact same scenario presented in the bug report (I assume you will be able to).

While it's unclear in which release the fix for that bug landed, by the date of Microsoft's response, I believe this should be fixed in SP3.

If applying a service pack is not an option at this time, you'll want to go with a trusted 3rd-party solution, as mentioned in the other answer.


Upgrading Mongodb from 3.2.6 to 4.2. Which is the better way to do it?

I would follow one of these documents that are published by MongoDB.

Upgrade a Replica Set to 4.2
Upgrade a Standalone to 4.2


One way to accomplish this is to use LEFT OUTER JOIN along with COALESCE. To get the missing values in the J_ID and K_ID column you can join to R_ID and JK_ID. To get the missing values in the Y_ID column you can join to JK_ID and R_ID.

Something close to the following should work:

SELECT
  cus.USER_ID
, cus.NAME
, COALESCE(cus.J_ID, r_to_jk.J_ID) AS J_ID
, COALESCE(cus.K_ID, r_to_jk.K_ID) AS K_ID
, COALESCE(cus.Y_ID, r_to_y.Y_ID) AS J_ID
FROM Customers cus
LEFT OUTER JOIN R_ID y_to_r ON cus.Y_ID = y_to_r.Y_ID
LEFT OUTER JOIN JK_ID r_to_jk ON y_to_r.R_ID = r_to_jk.R_ID
LEFT OUTER JOIN JK_ID jk_to_r ON cus.J_ID = jk_to_r.J_ID AND cus.K_ID = jk_to_r.K_ID
LEFT OUTER JOIN R_ID r_to_y ON jk_to_r.R_ID = r_to_y.R_ID;


Do not use ENGINE=MyISAM, that will lead to blobs being costly for any operation on the table.  Instead, use InnoDB.

"Vertical partitioning" is generally not needed, because of the way InnoDB effectively does it for you....

With InnoDB, there are 4 ROW_FORMATs; let's try to pick the optimal one.  In all cases, some, or all, of each blob is stored away from the row itself.  More in a minute.

But first, let's check the queries.  Do not use SELECT *; always specify only the necessary columns.  This avoids reaching into other blocks to get the blobs.

The ROW_FORMATs are redundant, compact, dynamic, and compressed.  For your case, I recommend either DYNAMIC or COMPRESSED.  These store none of the blob in the record itself; instead they have a 20-byte pointer to where the blob is stored.  Only if you need to touch the blob does it run off to that location.

COMPRESSED is the same as DYNAMIC, except for a complicated block-level compression technique.  Most benchmarks I see say you get about 2x compression.  This is not as good as you might get by compressing the blobs in the client.  For example, most 'text' compresses 3x.  But most image formats are already compressed, so compression is a waste.

So, I leave it to you to decide between dynamic and compressed.  If in doubt, test with your data and your queries.

You should have innodb_file_per_table=1 when you CREATE the table.  You may need BARRACUDA and you do need at least version 5.6; 5.7 would be better.

Two tables, one for raw, one for combined?  Maybe.  Given the benefits described above, there may not be any benefit in having two table.  However, if there is a lot of activity in one column, it might be better to move it to a separate table (plus 1:1 id, etc).  (This is especially important for things like "like/unlike" counters.)

Since you are working with bulky things, be careful that you do not have long-running transactions.  Such can lead to timeouts, etc.  Still, use BEGIN...COMMIT where appropriate.


Use the INNER JOIN whenever possible because it's easier to read. It otherwise should not make a difference.


I would write the raw data to a very basic table without indexes or constraints, not even a primary key.

If you can, insert many rows at once, that's faster than single-row inserts.

If you can afford loosing some data in a catastrophic event, make that an unlogged table, that's faster.

If you can afford loosing some data in a catastrophic event and all inserts can run in a single session, make that a temporary table, that's even faster. Probably not possible, though.

Start a new partition every time unit of your choice (daily?) and aggregate the old partition into permanent storage at some opportune moment.


Adding an additional "order by" column gives me a much worse plan

Your question is missing a lot of detail but I can reproduce something similar.

Setup

CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)

CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))

INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;

Query 1

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;

Query 2

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y

Query 3

WITH T  AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y

Query 1 just picks off the TOP 1 from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.

Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1 value and SQL Server decides to join the whole lot and then get the TOP 1 from that.

Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort on any rows tied with the same value for the first sort key.

For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.

You can try this rewrite and see how it fares

WITH T
     AS (SELECT TOP 1 WITH TIES so.OrgType,
                                ch.Status,
                                rcs.DBSstatusDescription,
                                cid.ApplicationId,
                                ch.DateAdded AS chDateAdded
         FROM   tbl_application_crb_initialData cid
                INNER JOIN tbl_season_organisationId so
                        ON cid.OrganisationId = so.OrgId
                LEFT JOIN tbl_crbHistory ch
                       ON cid.ClientReference = ch.ClientReference
                LEFT JOIN ref_crbStatus rcs
                       ON ch.Status = rcs.statusId
         ORDER  BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
             Status,
             DBSstatusDescription,
             ApplicationId
FROM   T
ORDER  BY chDateAdded DESC


Does reporting webservice url contain the function that helps to export the report to the client version?

follow the given instruction, and should be able to print the report in format passed format:

https://surendragurjar.wordpress.com/2015/07/11/ssrs-wsdl-web-service-endpoint-using-java/


Percona MySQL 5.7 | XtraBackup 2.4 - xb_load_tablespaces() failed witherror code 11

I had a similar issue with an upgrade where I ended up with mixed libraries. Make sure you don't have dated library files still out there. I had to completely uninstall MySQL and reinstall it from scratch, and then it worked.


Selecting attributes from multiple tables via foreign key

Essentially I'm trying to select all from table 2, ...

So we start with Table2 as our basis for the query:
FROM Table2

... but table2.att_1 and table2.att_2 are foreign keys of table1.id.

We'll likely need two (outer) joins to the same table (Table1), one for each foreign key:
FROM Table2
    LEFT JOIN Table1 ..
    LEFT JOIN Table1 ..

What I want to achieve is extract the following:
Value of Table2.ID

SELECT Table2.ID

Table1.Att_1 where Table2.Att_1 = Table1.ID
Table1.Att_1 where Table2.Att_2 = Table1.ID

Right, those verifies that we want 2 left joins and the ON conditions:
SELECT
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_1 = Table1.ID

and
SELECT
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_2 = Table1.ID

Oops, we have a problem to combine the two joins. We'll need to alias those tables as we have a table appearing twice and it's good practice nonetheless:
FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID

Value of Table2.Att_3

SELECT t2.Att_3

The whole query becomes (after adding some column aliases as well):
SELECT t2  . ID     AS  ID,
       t1a . Att_1  AS  Att_1_a,
       t1b . Att_1  AS  Att_1_b,
       t2  . Att_3  AS  Att_3
FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID
  ;


How to restore postgreSQL schema dump to schema with different name

If the data is smallish, I would just do the pg_dump, restore it to a temporary just-for-this-purpose database server, rename the schema within that temporary server (ALTER SCHEMA...RENAME...), and dump it out of that temporary server to do the final restore.


How can I change an existing type from "bigint" to "bigserial"?

bigserial is not a type. It's a pseudo-type, a notational convenience that is resolved to type bigint internally, plus a sequence, a column default, a dependency and an ownership.

Basic commands to convert an existing bigint column with existing rows to a bigserial:

CREATE SEQUENCE tbl_tbl_id_seq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE tbl_tbl_id_seq OWNED BY tbl.tbl_id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(tbl_id), 0)) FROM tbl;

setval() to get the SEQUENCE in sync with the currently highest id.  Replace "tbl" with your table name (prove_identity).

However, zooming into your added image (which should be plain text), it looks like it may be a proper bigserial column already. This might all be a misunderstanding, then.

More details:

Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Related:

How to convert primary key from integer to serial?
Safely and cleanly rename tables that use serial primary key columns in Postgres?

In Postgres 10 or later, consider an IDENTITY column as alternative:

Auto increment table column


Distribution database corrupt - What to do?

However, CHECKDB cannot be run without first putting the database in single user mode. Is this correct?

It shouldn't need to be in single user in order to run checkdb. You can run DBCC CHECKDB('distribution') WITH NO_INFOMSGS, ALL_ERRORMSGS to check it.

Since we don't know where the current issue is, it's hard to say what/if any impact to the current topology would be. We'd really need to know more, feel free to update the question with the output from CHECKDB.

Please do note that system databases, which the distribution database is considered, are not supported to have REPAIR_ALLOW_DATA_LOSS run on them. In fact, it should only be run in the most devastating of situations and under duress. Choosing to do this to a system database will immediately become an unsupported configuration.


Copy Table from one MySQL server to another

If you can ignore changes that might happen while the copy is taking place...

Use the PRIMARY KEY to walk through the source table, fetch chunks of 100-1000 rows, copy them.  More on chunking, but adapt for just fetching, not deleting or updating.


Nested Loop with Materialize resulting in very slow query on postgresql 9.5

A couple of observations:

1.

The query plan for the slow query shows:

  Seq Scan on event_page o (cost=10,000,000,000.00..10,000,000,000.00 rows=1 width=274)

... which indicates that you ran with SET enable_seqscan = off;. And that means your Postgres version positively could not find any other way than the sequential scan.

2.

Did you enable constraint_exclusion like the manual advises here:

  Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized
  as desired.

3.

The day boundaries in your query are not in sync with the partitioning. (But that has no immediate effect for the given query, see comments.)
The CHECK constraint reads:

CHECK ( timestamp_ >= '2017-4-01'::timestamp AND timestamp_ < '2017-05-01'::timestamp )

There may be confusion with timestamp vs. timestamptz and / or time zones. Or something got lost in translation and the question is misleading in that respect.

Either way, the expression in your WHERE clause opens up a sneaky corner case (even if your day boundaries were in sync):

where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'

Postgres timestamps are implemented as 8-byte integers allowing 6 decimal places. A row with '2017-4-29 03:59:59.9995' would not behave as expected.
Details:

Ignoring timezones altogether in Rails and PostgreSQL

Plus, this does not play well with constraint exclusion. More partitions than necessary may have to be read.

Use instead:

WHERE o.timestamp_ >= '2017-4-28 04:00' -- include lower bound
AND   o.timestamp_ <  '2017-4-29 04:00' -- exclude upper bound

You may want get "days" in your partitions in sync with your queries - 00:00 vs. 04:00 (or the evil 03:59:59.999) - and maybe use timestamptz to begin with.

4.

I see for the index-only scan on alias:

  Heap Fetches: 1918543

... which is exceptionally high. There may or may not be problems with the visibility map. (See also: Updating The Visibility Map) Did you run VACUUM FULL on the table(s)? (Compare this thread on plpgsql-performance.) Try plain VACUUM, which updates the visibility map properly. But that's all hard to say, since you seem to have been running with enable_seqscan = off. Not sure how this plays out in combination with all the other stuff at work here.

There have been various improvements to VACUUM for Postgres 9.6 (among other things), this may explain the difference - or what you observed in pg 9.6 is coincidence and due to other factors.

5.

If alias.alias is unique (and referential integrity can be assumed), the query can be simplified. Just count(DISTINCT o.person_alias) without even joining to table alias at all. Like:

SELECT count(DISTINCT o.person_alias) AS thecount
FROM   event_page o
WHERE  o.timestamp_ >= '2017-4-28 04:00' -- include lower bound
AND    o.timestamp_ <  '2017-4-29 04:00' -- exclude upper bound
AND    o.location_host = 'www.foo.com'
AND    o.location_path = '/ca/sale';

(Or use the subquery like you had it, may be faster than count(DISTINCT ...).)

If alias.alias is not unique, your query may be wrong / ambiguous.

Why do you have person_alias in table event_page and not person_id to begin with?

6.

You use the data type CHAR(24) as PK for your tables, which is almost certainly a bad choice. Consider a serial or bigserial column or maybe a uuid. Numeric types are smaller and faster and not burdened by collation rules, varying byte length, etc. Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
What is the overhead for varchar(n)?


Inheritance discriminator column better int or varchar or enum?

Use always the smallest data types that fits the values it will hold. 'Fit' is a flexible term, depending on thing like:

Can it hold all current values of the table?
Can it hold future values of the table?
Is the data in a normal form? Can I create duplicate values? (think string vs. integer id that is a foreign key to an external table)
Can I insert undesired values? Will I control that at db or application level?
Is it easy to use/compatible with my application? (think ASCII encoding vs. utf8) Some datatypes are not "sql standard" and some frameworks do not recognize them.

Once you have those under consideration, use the smallest data type, as it will reduce memory and disk footprint (potentially reducing IOPS).

An int will take 4 bytes always, and it can store values from -2147483648 to 2147483647 (or from 0 to 4294967295, if using unsigned).
A varchar will take 1 + char_encoding * length (or 2 + char_encoding * length if it is larger than 255). char_encoding can be between 1 and 4 bytes, depending on the character and the encoding. Also, that assumes dynamic storage, fixed row engines like memory will store it with the maximum theoretical size. Usually inefficient for numeric values.
An enum will take 1 byte (or 2, if it holds more than 255 values), but will not allow for values not defined at creation time without an ALTER.

So it is always a trade-off between flexibility and performance.

Inheritance serialization, however, deserves its own discussion.


You can use ISNULL and NULLIF for this purpose:

SELECT * FROM `TAB1` WHERE ISNULL(col1, '') = '' OR ISNULL(col2, '') = ''

or

SELECT * FROM `TAB1` WHERE NULLIF(col1, '') IS NULL OR NULLIF(col2, '') IS NULL


Your statistics need updating.

See Statistics, row estimations and the ascending date column.

Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the >= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested loops and a seek on the other table.

The use of right(tbl1.id,7) = right(tbl2.id,7) prevents a seek being used so you get the hash join more suitable for the actual number of rows involved.

If you update  the statistics on that column it should choose a more appropriate plan naturally. You might also consider using Trace Flags 2389 & 2390 to make this issue less likely in the future.