Proper way to add new column in database What is the proper approach when we add a new column into an existing table?

For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3.

What is standard approach (if that is such thing) when I want to add a column with similar name?

I see 2 choices:

Create temp table with new structure where new column is next to columns with same name, copy data to new table from existing table and drop existing table and rename temp table. Bit complex process but makes database fields more readable.
Add new column in end. More simpler operation. But column names may not be clearly understood if they in very end.

To get some reference, we are using Database Projects to source control database changes and have a nicer GUI for app developers to make database changes. And we are using some kind of ORM to interact with database so nobody is querying database using database object names.

Update:
I have couple of indexes on some of existing columns. But columns that have similar names (including one that I want to add) are not part of any index.

As someone who used to try to keep columns together like this, I highly suggest that you go with option #2.

The debatable upside of keeping the columns visually together (in the SSMS column list / when you SELECT * from the table) is absolutely dwarfed by the downsides of option #1.

As the tables get larger, doing this "copy and replace" operation is going to take longer and longer, and utilize more resources (I/O, memory, CPU).

Additionally, you really need to make sure you don't lose any data during the copy and replace, so using the least concurrency friendly isolation level (SERIALIZABLE) is going to be necessary.  This means that access to modify the existing table will be blocked until the operation is complete.  So you can add long periods of blocking to the list of downsides.

SSDT publish uses this sort of code automatically when re-ordering columns like that (from my blog post on the subject):

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Post] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [CommentCount] INT          NULL,
    [PostType]     VARCHAR (10) NOT NULL
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[Post])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] ON;
        INSERT INTO [dbo].[tmp_ms_xx_Post] ([Id], [PostType])
        SELECT [Id],
               [PostType]
        FROM   [dbo].[Post];
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] OFF;
    END

DROP TABLE [dbo].[Post];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Post]', N'Post';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

On top of that, if you're doing this manually (not with SSDT schema compare), then it's really easy to make a mistake (put data into the wrong column, copy / paste errors, etc) - especially on a table with a lot of columns.


In this case Oracle works as it should, its your expectation that is incorrect.
Why do you expect 24 rows?
zz_value table has 8 rows with 9863 gg_id. zz_gg table has 8 rows with 9863 gg_id. pos_id and nach_id are the same in all rows in your sample. With your join conditions, this alone return 64 rows for gg_id 9863.
Your select with those columns returns duplicated rows. You can reduce that amount by using select distinct ..., but even that will return more than 24 rows because of the data_id column in zz_gg.


Need script for new login creation in sybase ase I have 470 new database accounts in SYBASE ASE

I certainly don't want to do it manually since it is definitely time consuming.

Here by I have old script which I try to add logins

Can someone help to me write this script in another way?

Data present in this file will be like as below  /tmp/addthese.txt:

i111111
i222222
i333333
i444444
i555555
i666666
i777777
i888888
i999999

SCRIPT :
source /cis1/sybase/SYBASE.sh

set passs=`cat /cis1/prod/dba/input/. ----`

isql -Usa -SPDSQTS1 <<EO_SQL
$passs

declare a varchar(10)
declare n int
declare count int
declare Iuser varchar(10)
declare date varchar(20)
declare id int

count=1

while n<427

a=sed -n 'count{p;q;}' "/tmp/addthese.txt"

select Iuser=name from syslogins where name='a'

if [Iuser!=a]
sp_addlogin a,Sybase123
go

use qts_db
go
sp_adduser a,a,eng
go
use engcomdb
go
sp_adduser a,a,eng
go
use sybasedb
go
sp_adduser a,a,eng
go
use engdb
go
sp_adduser a,a,eng
go
use msgdb
go
sp_adduser a,a,eng
go

use master
go
sp_modifylogin 'a',"authenticate with","ldap"
go

select id=suid from syslogins where name='a'
select date=crdate from syslogins where name='a'

use qts_db
go

insert into dbo.profiles(login_name,userid,security_level,delta_ts,delta_sy_user) values ('a',id,'ADMIN','date',id)
go

insert into dbo.users(userid,site_id,delta_sy_user,delta_ts,email,displayname,lastname,firstname)
values(id,'SYBASE',10407,'date','a',',  : id','', '')
go

end

count=count+1

end

There are many, many ways you could handle adding a list of logins/users to Sybase ASE, all of which include a mix of (Unix/Linux) shell scripting and T-SQL coding.

One simple example:

set passs=`cat /cis1/prod/dba/input/. ----`

cat addthese.txt | while read newlogin
do
isql -Usa -SPDSQTS1 <<EO_SQL > /tmp/error.out 2>&1
$passs
use master
go
if suser_id("${newlogin}") is NULL
    exec sp_addlogin ${newlogin},Sybase123
go
----------------------
-- repeat following block for each db of interest (eg, qts_db, engcomdb)
use <db>
go
if user_id("${newlogin}") is NULL
    exec sp_adduser ${newlogin}, ${newlogin}, eng
go
---------------------
-- rest of your coding
go
EO_SQL

... check for errors in /tmp/error.out ...

done

You could place all of the above into a shell script, make sure addthese.txt is correct/up-to-date, and then run the shell script.

Variations on the above could include:

putting the T-SQL code in a stored proc and then calling the stored proc for each new login
placing the above T-SQL code in a template.sql file with NEWLOGIN used as place holder, a shell script would then run sed 's/NEWLOGIN/${newlogin}/g' template.sql > runme.sql followed by isql -i runme.sql > error.out 2>&1

There are many variations based on which shell you're using and your coding style/preferences.


Saving the "view data options" in the "edit data" window in pgAdmin III I wonder whether there is any way to save the "view data options" in the "edit data" window in pgAdmin III:

It's annoying to have to input them again each time one opens a new "edit data" window.

As people said in the comments, it is not possible. I didn't create a feature request for this as I feel it is going to be rejected (got some previous negative experience proposing features there), but if you want to try: https://redmine.postgresql.org/projects/pgadmin3


MySQL Slave database size >> Master Database I have a Percona MySQL Master-Master setup (5.6) with second master as passive master(read-only).

Slave:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.70 |
| dataplay           |   1684.56 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+

Master:

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| common_schema      |      1.89 |
| information_schema |      0.01 |
| mysql              |      1.69 |
| dataplay           |   1614.80 |
| dataplay1          |      0.14 |
| percona            |      0.20 |
| performance_schema |      0.00 |
+--------------------+-----------+
7 rows in set (0.07 sec)

The size difference is massive. Is it something wrong with the setup which is running? What is wrong?

From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different.

Two potential reasons for this difference is:

Schema difference on the master vs slave. Does the slave have utf8 instead of latin1 on a column or three? Does the slave have additional indexes? You can compare the schema by running a mysqldump of just the schema (--no-data option) and then diff'ing the results.
The slave has actual different data. Do you have session-only data that the application stores and won't matter? If no, you can use pt-table-checksum to find rows that are actually different. Then you need to dig into why they are different and fix that issue before reseeding the passive master or using pt-table-sync to resolve the differences.


Databases entering "In recovery" mode on restore I am facing an issue while restoring the database.

I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore operation with the other dumps. During this restore operation all the other databases are entering the state of In Recovery . Also the database xyz is also entering the state of recovery and my restore operation fails for the 2nd dump saying the "Not able to achieve exclusive lock on the xyz" .

I am using SQL Server 2008 R2.

So how can I solve this issue please suggest.

In such situations I am trying this piece of code:

ALTER DATABASE <DB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE


MySQL - fastest way to ALTER TABLE for InnoDB I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.

I want to change the name of one of the columns and add a few more indices.

What is the fastest way to do it (assuming I could suffer even downtime - the server is an unused slave)?
Is a "plain" alter table, the fastest solution?

At this time, all I care about is speed :)

One sure way to speed up an ALTER TABLE is to remove unnecessary indexes

Here are the initial steps to load a new version of the table

CREATE TABLE s_relations_new LIKE s_relations;
#
# Drop Duplicate Indexes
#
ALTER TABLE s_relations_new
    DROP INDEX source_persona_index,
    DROP INDEX target_persona_index,
    DROP INDEX target_persona_relation_type_index
;

Please note the following:

I dropped source_persona_index because it is the first column in 4 other indexes

unique_target_persona
unique_target_object
source_and_target_object_index
source_target_persona_index

I dropped target_persona_index because it is the first column in 2 other indexes

target_persona_relation_type_index
target_persona_relation_type_message_id_index

I dropped target_persona_relation_type_index because the first 2 columns are also in target_persona_relation_type_message_id_index

OK That takes care of unnecessary indexes. Are there any indexes that have low cardinality? Here is the way to determine that:

Run the following queries:

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

According to your question, there are about 80,000,000 rows. As a rule of thumb, the MySQL Query Optimizer will not use an index if the cardinality of the selected columns is greater that 5% of the table row count. In this case, that would be 4,000,000.

If COUNT(DISTINCT sent_at) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX sent_at_index;

If COUNT(DISTINCT message_id) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX message_id_index;

If COUNT(DISTINCT target_object_id) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX target_object_index;

Once the usefulness or uselessness of those indexes have been determined, you can reload the data

#
# Change the Column Name
# Load the Table
#
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

That's it, right? NOPE !!!

If your website has been up this whole time, there may have INSERTs running against s_relations during the loading of s_relations_new. How can you retrieve those missing rows?

Go find the maximum id in s_relations_new and append everything after that ID from s_relations. To assure that the table is frozen and used only for this update, you must have a little downtime for the sake of getting those last rows that were inserted into s_relation_new. Here is what you do:

In the OS, restart mysql so that no one else can log in but root@localhost (disables TCP/IP):

$ service mysql restart --skip-networking

Next, login to mysql and load those last rows:

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Then, restart mysql normally

$ service mysql restart

Now, if you cannot take mysql down, you will have to do a bait-and-switch on s_relations. Just login to mysql and do the following:

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Give it a Try !!!

CAVEAT : Once you are satisfied with this operation, you can drop the old table at your earliest convenience:

mysql> DROP TABLE s_relations_old;


Creating Multiple Tables with WITH statements I have created Table1 and Table2 both with WITH statements. I have been trying to create Table3 with WITH statement as well that would be the UNION ALL of Table1 and Table2.

Here is my attempt:

 WITH table3 AS
 (
    SELECT c1 as c3
    FROM table1
      union all
    SELECT c2 FROM table2
 ), table2(c2, loc2) AS
    ( SELECT
        character2, location FROM lotrfirstencounters
    ), table1(c1, loc1) AS
    ( SELECT
        character1, location FROM lotrfirstencounters
    )

SELECT DISTINCT * FROM table3 ORDER BY c3;

So when I had WITH statement right before table2 and before attempting to create table3; both table1 and table2 were created as intended and I was able to call their distinct column like this:

  WITH table2(c2, loc2) AS
( SELECT
    character2, location FROM lotrfirstencounters
), table1(c1, loc1) AS
( SELECT
    character1, location FROM lotrfirstencounters
)

SELECT DISTINCT * FROM table1 ORDER BY c1;

I tried different ways to get a UNION ALL between them to get distinct columns for a new table but nothing I've tried seems to work.

You try to use table1 and table2 before their creation... reorder tables in WITH clause.

WITH table1(c1, loc1) AS (SELECT character1, location
                          FROM lotrfirstencounters),
     table2(c2, loc2) AS (SELECT character2, location
                          FROM lotrfirstencounters),
     table3 AS (SELECT c1 as c3
                FROM table1
                union all
                SELECT c2
                FROM table2)

SELECT DISTINCT *
FROM table3
ORDER BY c3;


Configuring PostgreSQL for write performance One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed data for that particular instant. The data rate is fairly high; it works out to about a gigabyte a day for one database, about a tenth of that for another one. I don't expect this rate to increase. Read performance is a much lower priority and is currently acceptable.

In the logs I have this message:

LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

This value is currently set to 16, which is courtesy of pgtune.

What are the settings I should consider to improve write performance? I would prefer to keep as much safety as possible. Considering the volume of data coming in, I could accept losing some recent data in a failure as long as the bulk of the data were intact.

Edit: I'm using PostgreSQL 9.0 for now, but I plan to upgrade to 9.1. I am not posting the hardware details because while I acknowledge their importance, I ultimately will be needing to make this optimization on several machines with very diverse hardware. If the hardware is essential to the answer, please give me the general information so I can apply the answer to machines with different hardware configurations.

1 Gigabyte a day is not that high of a write load.  Spread out throughout the day, that comes out to about 50kbytes a second. A slow USB thumb drive could handle that.  I'm assuming it's more bursty though.  As a_horse_with_no_name suggests, increase checkpoint segments.  100 or so is not out of the ordinary.

Then increase your checkpoint_timeout to 1 hour, as well as look at increasing your checkpoint_completion_target to something closer to 1.0 (100%).  The completion target tells PostgreSQL how aggressively to write in the background so that it's x% complete before running a checkpoint, which force all the data to be written out at once from the WAL and will slow the system to a crawl while it's happening.

The reason you don't usually set it to 100% is that it's pretty common to write to the same block more than once, and by delaying WAL writes out to the main store, you prevent the same block being written twice for no reason.

If it's unlikely you'll be writing to the same block more than once before your timeout occurs, i.e. all you do is insert then setting it pretty high makes sense to raise it to 0.9 or so.  The worst that'll happen is you'll write a little more often than you might otherwise need to, but checkpoints impact will be greatly reduced.


How to get the Oracle Text Index's underlying Virtual XML Document? The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML document for each row.
Also, in certain situations, we can use user datastore to define stored procedures that synthesize documents during indexing. The procedure can grab the data and put them in an XML format. A good example is shown here.
The Question
Is there a way to retrieve or display the underlying virtual XML document for each record? How?

Probably not

The view that may display native stored compiled indexes is CTX_INDEX_VALUES Or CTX_USER_INDEX_VALUES (depending on permissions and querying user). However, given that the index value length is only 500 characters, it is unlikely that it is stored there. (Just start selecting randomly from those indexes, if it exists, it will likely be there.)

Looking at the view design, however, it is unlikely that those virtual XML elements are stored, as it's much easier to compile them on the fly and then load them into memory.

Alternatively, CTX_DOC has a number of markup functions, but none indicate the ability to access native XML.

You may also want to explore the discussion around metalink: 122255.1 (Unfortunately, I don't currently have metalink access).

It may be easier to write your own parser-to-XML (as oracle has quite a few excellent native XML functions) than try to hack around the full text index.


Converting to MyISAM, `foreign_key_checks = 0` Not Working At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables.  However, when I attempt to alter the table

mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>

MySQL complains.  This is expected.  However, if I disable the foreign_key_checks, I get the same results

mysql> SET foreign_key_checks = 0;
mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This happens regardless of my setting the key check at the global or session level.   I assume the problem here is the table in questions has other InnoDB table which reference it, and MySQL refuses to end up in a state where an InnoDB table references a table that's not InnoDB. (I may be incorrect on this, and I'm more than happy to be corrected)

Is there a quick way to handle this situation?  I basically want all tables in the database to be MyISAM, and tracing out all the relationships myself and/or manually removing the contraints seems very time consuming, and  the sort of thing that a computer would be better at.

If the answer is "suck it up and do the work" I'm happy to hear that from an expert — I just don't want to waste the time if I don't need to.

Here's a method you can use without dumping all your InnoDB tables.

You will have to drop the foreign key constraints before you can alter the tables. You can generate the drop statements using the INFORMATION_SCHEMA this way:

mysql> SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
    '` DROP FOREIGN KEY `', constraint_name, '`;') AS _sql
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE referenced_table_schema = 'mydb' -- replace with your database name
    AND referenced_table_name = 'catalog_category_entity'
    AND ordinal_position = 1;

The output should be one or more a fully-formed DDL statements. Run the statements, and then you can change the storage engine for your parent table.


Backup policy for Oracle 11g database in non-archive mode My database is in non-archive mode and its size is 5 TB (monthly 200 GB increment). Currently we are taking the full backup monthly. As database growth is high, backup size and time had increased behold the acceptable limit. Hence we would like to take online RMAN backup by enabling the archive mode before starting the online backup and disable the archive mode after completion of online backup.

1) Are we able to recover the database till last backup using above backup policy?
2) Is it required to take archive log backup as well?

This DB only contains historical data and on daily basis an import will be done using dump files from the source DB. One month export dumps will be kept as backup, hence complete recovery till point of failure is not desired. Export dumps can be used to recover the database.

A partly online backup is certainly possible but switching to and back from archivelog mode needs downtime. As an alternative you could run in regular archivelog mode and trash the generated archived log files, until you run a backup.

For the backup to be recoverable it is important that it has ALL archived log files that were generated during the backup. End the backup with a log switch and also include those archives in the backup.

You must see this kind of backup as partially online. It does allow you to restore to the point in time where the backup completed, not to any other point in time. Very similar to regular offline backups, only, taken online. It saves downtime.

Why do you want this? This is a lot of effort just to safe maybe 10G of archive storage per day. It also introduces an extra error source and many dba's won't be able to work with this and expect to have all archives. My advice would be to keep it regular online backups, with regular archived log backups and keep things simple.


Delete records with multiple values for a column I am attempting to clean some data from within the AdventureWorks database on SQL Server 2012. Some of the records have multiple values for the Rate field.

I have already found a query for selecting the rows:

select x.*
from t as x
where x.ID
IN (
    select ID
    from t
    group by ID
    having count(Rate) > 1
)

Here is the simplified output:

ID   Rate   City
4    8.62   Minneapolis
4    23.72  Minneapolis
4    29.85  Minneapolis
6    24.00  Redmond
6    28.75  Redmond

Now, I wish to delete the records with multiple values for the Rate field and keep ONLY the highest Rate for each ID. For example:

ID   Rate   City
4    29.85  Minneapolis
6    28.75  Redmond

not sure about count(distinct Rate) > 1, think distinct is not necessary (but this depends from Your logic and not change query)

WITH tmp AS
        (select
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        having count(distinct Rate) > 1)

select x.*
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NOT NULL

must return expected data

LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
    WHERE tmp.ID IS NOT NULL

could be changed by INNER JOIN

INNER JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate

add final form as asked (delete all not), edited:
original query was have

having count(distinct Rate) > 1

as described in question - correct form of expected query, after comment - we need just remove it

WITH tmp AS
        (select
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        )

DELETE x
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NULL


Is there any performance difference in using a unique index on primary key? I know there are lost of similar questions to this one, but none that really answers this:

Is there ANY difference in performance in using an unique index in a primary key, using a MyISAM table on a server running MySQL 5.5?

If you need more informations, I can provide all.
I just want to know if it is worth (lets say, if the 'seek' time decreases by more than 5 milliseconds) to do that change or if I'm committing a hideous crime.

And yes, this is a serious question.

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keyword on it you are actually creating a separate index. This does not only make things less efficient but, in some cases, it will make your queries less performant.

You can check that two index were created by doing:

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Both in MyISAM and InnoDB, it will require additional space, it will make the writes slower (because the index has to be updated synchronously at write time), and it may waste optimizer (even if microseconds) time deciding which index is the best one. MySQL would usually select the PRIMARY key in MyISAM, but if for any reason both indexes were used, you will also waste memory.

If you do not believe me, believe MySQL:

mysql> INSERT test1 SELECT * from test;
Query OK, 1691648 rows affected (6.84 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test2 add unique(id);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT test2 SELECT * from test;
Query OK, 1691648 rows affected (10.94 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

A run of pt-duplicate-key-checker is usually enough to detect those duplicates.

If you mean using UNIQUE NOT NULL instead of PRIMARY KEY, I would not expect a difference on MyISAM in most cases (although some tools work better with a PK, like row-based replication); it is not the case with InnoDB, in which the PK is fundamental on its structure (clustered rows around it).


mysqldump changes primary key to unique key? While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables:

mysql> describe wp_yoast_seo_meta;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| object_id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| internal_link_count | int(10) unsigned    | YES  |     | NULL    |       |
| incoming_link_count | int(10) unsigned    | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+

Is dumped as:

CREATE TABLE `wp_yoast_seo_meta` (
  `object_id` bigint(20) unsigned NOT NULL,
  `internal_link_count` int(10) unsigned DEFAULT NULL,
  `incoming_link_count` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Notice the object_id field is used to create a UNIQUE_KEY index, instead of being set as the PRIMARY KEY. I'm not using any special options to mysqldump other than specifying the host name and user name.

How can I get mysqldump to include the primary keys?

In MyISAM, there is no implementation difference between PRIMARY and UNIQUE.  (I don't know why it would do what it did.)

You should upgrade to InnoDB.


ORA-00905: missing keyword when trying to create a synonym I have a problem when I try to create a synonym like this:

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET
 FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;

Why does this happen and how can I correct it?

As the documentation says implicitly, you can create synonyms in the current DB only. So the first @PACOMNET in the synonym name is unnecessary (and causing the error). You should instead connect to the PACOMNET database and run

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY
FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;


How do I get the aggregate of a window function in Postgres? I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so:

CREATE TABLE foo
(
  perm integer[] NOT NULL,
  combo integer[] NOT NULL,
  value numeric NOT NULL DEFAULT 0
);
INSERT INTO foo
VALUES
( '{3,1,2}', '{1,2,3}', '1.1400' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0.9280' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0.8000' )

I want to find out the average and standard deviation for each permutation, as well as for each combination. I can do that with this query:

SELECT
  f1.perm,
  f2.combo,
  f1.perm_average_value,
  f2.combo_average_value,
  f1.perm_stddev,
  f2.combo_stddev,
  f1.perm_count,
  f2.combo_count
FROM
(
  SELECT
    perm,
    combo,
    avg( value ) AS perm_average_value,
    stddev_pop( value ) AS perm_stddev,
    count( * ) AS perm_count
  FROM foo
  GROUP BY perm, combo
) AS f1
JOIN
(
  SELECT
    combo,
    avg( value ) AS combo_average_value,
    stddev_pop( value ) AS combo_stddev,
    count( * ) AS combo_count
  FROM foo
  GROUP BY combo
) AS f2 ON ( f1.combo = f2.combo );

However, that query can get pretty slow when I have a lot of data, because the "foo" table (which in reality, consists of 14 partitions each with roughly 4 million rows) needs to be scanned twice.

Recently, I learned that Postgres supports "Window Functions", which is basically like a GROUP BY for a particular column. I modified my query to use these like so:

SELECT
  perm,
  combo,
  avg( value ) as perm_average_value,
  avg( avg( value ) ) over w_combo AS combo_average_value,
  stddev_pop( value ) as perm_stddev,
  stddev_pop( avg( value ) ) over w_combo as combo_stddev,
  count( * ) as perm_count,
  sum( count( * ) ) over w_combo AS combo_count
FROM foo
GROUP BY perm, combo
WINDOW w_combo AS ( PARTITION BY combo );

While this works for the "combo_count" column, the "combo_average_value" and "combo_stddev" columns are no longer accurate. It appears that the average is being taken for each permutation, and then being averaged a second time for each combination, which is incorrect.

How can I fix this? Can window functions even be used as an optimization here?

You can have window functions on the result of aggregate functions in a single query level.
This would all work nicely after a few modifications - except that it fails for the standard deviation on mathematical principal. The involved calculations are not linear, so you cannot simply combine standard deviations of sub-populations.
SELECT perm
      ,combo
      ,avg(value)                 AS perm_average_value
      ,sum(avg(value) * count(*)) OVER w_combo /
       sum(count(*)) OVER w_combo AS combo_average_value
      ,stddev_pop(value)          AS perm_stddev
      ,0                          AS combo_stddev  -- doesn't work!
      ,count(*)                   AS perm_count
      ,sum(count(*)) OVER w_combo AS combo_count
FROM   foo
GROUP  BY perm, combo
WINDOW w_combo  AS (PARTITION BY combo);

For combo_average_value you would need this expression
sum(avg(value) * count(*)) OVER w_combo / sum(count(*)) OVER w_combo

Since you need a weighted average. (The average of a group with 10 members weighs more than the average of a group with just 2 members!)
This works:
SELECT DISTINCT ON (perm, combo)
       perm
      ,combo
      ,avg(value)        OVER wpc AS perm_average_value
      ,avg(value)        OVER wc  AS combo_average_value
      ,stddev_pop(value) OVER wpc AS perm_stddev
      ,stddev_pop(value) OVER wc  AS combo_stddev
      ,count(*)          OVER wpc AS perm_count
      ,count(*)          OVER wc  AS combo_count
FROM   foo
WINDOW wc  AS (PARTITION BY combo)
      ,wpc AS (PARTITION BY perm, combo);

I am using two different windows here, and reduce the rows with DISTINCT which is applied even after window functions.
But I seriously doubt it will be faster than your original query. I am pretty sure it isn't.
Better performance with altered table layout
Arrays have an overhead of 24 bytes (slight variations depending on type). Also, you seem to have quite a few items per array and many repetitions. For a huge table like yours it would pay to normalize the schema. Example layout:
CREATE TABLE combo (
  combo_id serial PRIMARY KEY
 ,combo    int[] NOT NULL
);

CREATE TABLE perm (
  perm_id  serial PRIMARY KEY
 ,perm     int[] NOT NULL
);

CREATE TABLE value (
  perm_id  int REFERENCES perm(perm_id)
 ,combo_id int REFERENCES combo(combo_id)
 ,value numeric NOT NULL DEFAULT 0
);

If you don't need referential integrity you can omit the foreign key constraints.
The connection to combo_id could also be placed in the table perm, but in this scenario I would store it (slightly de-normalized) in value for better performance.
This would result in a row size of 32 bytes (tuple header + padding: 24 bytes, 2 x int (8 byte), no padding), plus the unknown size of your numeric column. (If you don't need extreme precision, a double precision or even a real column might do, too.)
More on physical storage in this related answer on SO or here:
Configuring PostgreSQL for read performance
Anyway, that's only a fraction of what you have now and would make your query a lot faster by size alone. Grouping and sorting on simple integers is also a lot faster.
You would first aggregate in a subquery and then join to perm and combo for best performance.


backup log larger than log file Today I noticed that the backup of the log of our database was 18GB but the size of the log file is only 8GB.

I used the following command to backup:

BACKUP LOG [myDB]
TO DISK = N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn'
WITH CHECKSUM
    , COMPRESSION;

Can anyone explain how it's possible that the backupfile is larger than the logfile?

Update:

The database is in full recovery mode and here are 216 VLF's in my log file.
I just looked to the size of the ldf file to determine the size of the log (I know this is not the right way but you would expect if the backup is 18GB the ldf file would be greater than 8GB).

I asked my colleague and he shrunk the log file manually before backup, the size changed from 10GB to 8GB (only 2% of the logfile was being used).
Why would a shrink (after or before backup) of the log file cause this?
Also there is only one backup in my backup file.

Your T-SQL BACKUP LOG command does not use the INIT option.  If you do not specify the INIT option, and the file target backup file already contains one or more backups, the backup you are taking is appended to the end of the file.

This would explain how your log backup seems larger than the log it backed up.

You can tell if there is more than one backup in the target file by looking at the results of:

RESTORE HEADERONLY
FROM DISK =  N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn';

See the "Media Set Options" in the Backup MSDN page.


SQL Server 2008 R2 Ent trial expired move to express? So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything special other than keep the old website running for a couple months.

Is express or compact capable of running a small website?

Is it possible downgrade to express or compact and how?

How can I backup the existing databases now that we have been effectively locked out of them?

So someone let our SQL server 2008 R2 enterprise trial expire on a production web server

Violation of the EULA.

  Is express or compact capable of running a small website?

Both are capable of running a small website. Express is also capable of running medium or large websites. Personally I would go for Express if possible, Express handles concurrent access much better than Compact.

  Is it possible downgrade to express or compact and how?

Uninstall Enterprise, install Express and migrate the database to the new instance. Compact is much harder - Microsoft has no tools for downgrading to Compact. This tool, amongst others, can help you.

  How can I backup the existing databases now that we have been effectively locked out of them?

You might try starting the database in single user mode, or stopping the SQL Server service and getting the MDF and LDF files out of the DATA directory and attaching them to the Express instance.


SQL Analysis Services - SSAS Do you know what the best practices would be regarding SSAS and having real-time reports.

I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a Data warehouse database before re-processing the cube.

Any advice on what the best approach is would be greatly appreciated.

The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy load already, then the penalty is probably negligible. If SSAS queries are going to kick off 500MB+ reads, then that's a problem.

And unless you're doing some kind of high-frequency trading, you probably don't need to have your SSAS database that up to date. It seems like SSAS is more useful for big-picture kind of summaries, and if the past few hours of data aren't included yet, it's not going to make a big difference to those running the reports (ask around to be sure, obviously).

We load our data warehouses and process the cubes each night, and that's typically plenty. We also have a few simple reports built against OLTP tables for viewing more targeted, up-to-the-minute data (stuff that's really more OLTP than OLAP in nature).


Can sharding work with replication? Basically we need to have a replication, a minimal setup.

Primary
- Slave 1
- Slave 2

Is it possible in the future we Shared it? What would be the setup?

Is it going to have shared on Slaves like this?

Primary
- Slave 1 (Shared A)
  - Primary
  - Secondary
- Slave 1 (Shared A)
  - Primary
  - Secondary

I'm not sure I understand your question. But yes, sharding and replication work together.

http://docs.mongodb.org/manual/faq/sharding/#how-does-sharding-work-with-replication

Anyway, the master-slave replication approach has been discouraged for some time.

http://docs.mongodb.org/manual/core/master-slave/

The docs are pretty good, take a look at the replication section.


Monitoring sql server This question is related to monitoring of SQL Server.

I want to send an email alert with all open transactions on SQL Server in HTML report whenever there is sustained 50-60% CPU usage for more than 3 minutes. How to achieve this using TSQL or stored procedure so that I can schedule as a job and keep polling on regular basis.
Similar as above, I wanted to get notified whenever there is sustained 70-80% memory usage on the server for more than 3 minutes.
The reason for this, even though I have set the max server memory setting for sql server, there could some component eating up memory which can cause external memory pressure.
In such situations, DBA team should get an email alert notification even before users calls in and open a ticket.

I have anyone can provide sample code that will be great help. Based on that I can extend the functionality of monitoring like collecting trace etc...

You can use a combination of tools. I'll give you an abridged list of the ones I use.

First, I assume you have database mail configured.

Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters which will trigger SQL Server Alerts. When an alert happens, you can also execute a job in addition to notifying operators. If you know WMI, you can use Alerts to get to PerfMon counters.

Third, you can use SQL Server Extended Events, but they are more complicated and require a good understanding of the database engine and hand coding because there is very little in the way of GUI.

Finally, you can just schedule a stored procedure to run on a regular basis, allow it to collect metrics from the DMVs (database management views) and then send the email directly from the stored procedure.

Specifically for your situation, I might suggest you run sys.dm_os_performance_counters on a regular basis (scheduled job/stored procedure) and cache it in a metadata table, then use that for analysis.

Also, getting CPU usage information in SQL Server is not the easiest thing in the world because CPU stats can change on such a finely-granular timescale. Try this article for a good overview of how it works. Perhaps look at "free pages" to get you 70% RAM figure.


Deny certain user from updating certain rows Say I have a table like this:

canModify | name | age
------------------------
    1     | John | 24
    0     | Nick | 21

I want to grant update access to a specific user only where canModify=1

I know there is column level security, something like:

DENY UPDATE ON Table(column) TO user;

But I can't find anything like:

DENY UPDATE ON Table To user
WHERE Table.canModify = 1

Is there a trigger or something I can set up to do this?

In my opinion, if you are stuck with sql server 2012, the Row Level Security is not going to work for you.

So you have two options:
1) application layer, which was already mentioned.
2) on database level, an trigger may work


Query runs for too long I have a select query that should return a result with about 200 rows from 3 tables:

wb_mod_deposit_out - 700.000 rows
wb_mod_deposit - 100.000 rows
from_generale_java - 1200 rows.

The query is:

SELECT

    DATE(a.created) AS DATA_MANCANTE, a.stagione,
    c.status, a.modello, a.commessa, a.qnt_prod,
    a.fabrica, a.CODE, a.color, a.measure,
    ROUND(a.qnt,2) AS qnt_not_enough,
    a.dist_base, b.qnt AS qnt_dept,
    DATE(a.created) AS DATA_PRELIEVO,
    C.`DATA_PREV_INI_TAG`, C.`DATA_PREV_INI_CONF`

FROM

 wb_mod_deposit_out a,

 (SELECT CODE, color, ROUND(SUM(qnt),2) AS qnt
  FROM wb_mod_deposit
  GROUP BY CODE, color
 ) b,

 from_generale_java C

WHERE
      a.TYPE = 2
  AND a.code = b.code
  AND a.color = b.color
  AND b.qnt <= 0
  AND A.modello = C.MODELLO
  AND A.COMMESSA = C.COMMESSA
  AND A.STAGIONE = C.STAGIONE;

It takes about 2 min to return the result.

I have found a way to check myself with EXTEND:
https://www.slideshare.net/phpcodemonkey/mysql-explain-explained

Move the qnt <= 0 inside as a HAVING (not WHERE).

Use JOIN ... ON ... instead of FROM a,b WHERE a...=b...

Add the following composite indexes:

a: INDEX(type, code, color)
b: INDEX(code, color, qnt)
c: INDEX(modello, commessa, stagione)


Compact Spool file from Sqlplus I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed that the export from sqlplus is twice as large as the export file from SQL Developer. The contents/result of the files are exactly the same.  Here are my  sql file called oracle_sql.sql

set embedded on;
set pagesize 0;
set colsep ',';
set echo off;
set feedback off;
set linesize 1000;
set trimspool on;
set headsep off;
set termout off;
set heading on;
set und off;
spool mytable.csv
select item, item_desc
from mytable;
spool off

here are my .bat file code

@echo off
sqlplus jasons/jasons@localhost @oracle_sql.sql

Do I need to have an extra setting in my sql file that I am not aware off?

That's because of the set linesize 1000 clause.
That one is making all your lines in the spool file to be 1000 rows long, even if there is no such data.

Try this from a clean SQL sqlplus session:

set linesize 1000
spool test.txt
select * from dual;
spool off
exit

Then, go back to shell and open the test.txt with any text editor, move to the lines belonging to the SQL query results (not the lines with the prompt where you wrote the query) and check it's length.

Those 3 lines will be 1000 bytes long.


Query performance improvement Can I improve performance of this query?

SELECT transact.id
           FROM transaction0 transact
           JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
           JOIN product account ON transact.product_id = account.id
          WHERE account.classtype::text = 'loan'::text
          AND journ.notes_jdoid IS NOT null;

explain:

Nested Loop  (cost=1.01..135956337.96 rows=1 width=8) (actual time=393.040..50792.689 rows=20 loops=1)
  ->  Nested Loop  (cost=0.58..135956333.67 rows=1 width=16) (actual time=393.018..50790.822 rows=547 loops=1)
        ->  Seq Scan on journ_notes journ  (cost=0.00..469345.58 rows=30466258 width=8) (actual time=0.007..7229.810 rows=30466263 loops=1)
              Filter: (notes_jdoid IS NOT NULL)
        ->  Index Scan using i_transaction0_journal_jdoid on transaction0 transact  (cost=0.58..4.44 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=30466263)
              Index Cond: (journal_jdoid = journ.jdoid)
  ->  Index Scan using i_product_id on product account  (cost=0.43..4.28 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=547)
        Index Cond: (id = transact.product_id)
        Filter: ((classtype)::text = 'loan'::text)
        Rows Removed by Filter: 1
Planning time: 0.464 ms
Execution time: 50792.743 ms

I already have the index listed on the explain log, but also the unused ones below:

create INDEX  _i_transaction0_product_id_id on transaction0(product_id, id)

create INDEX  _i_product_classtype_id on product(classtype, id)

You are effectively asking PostgreSQL to scan the complete tables, since both of your WHERE conditions are not selective at all.

Since journ_notes contains some 30 million rows, that will always take some time.

PostgreSQL's estimates seem to be good, and the nested loop join is probably chosen because the other tables are either big enough to make a hash join unattractive, or work_mem is set too small to contain the resulting hash.

Try again with a large setting for work_mem and see if you get better results. Verify that PostgreSQL is doing the right thing by temporarily disabling nested loop joins with

SET enable_nestloop = off;

and checking if the execution becomes slower or not.

Other than that, perhaps parallel query can make it faster (though not cheaper), but you don't tell us your version.


Does SQL Server Cache Aggregate Results When Duplicated Across Columns? Suppose we have a table Orders containing the columns order_id, total, discount

Then we write a query similar to the following

SELECT
    COUNT(order_id) AS num_orders
    , SUM(total) / COUNT(order_id) as avg_total
    , SUM(discount) / COUNT(order_id) AS avg_discount
FROM Orders

Is the value for COUNT(order_id) Preserved across columns or re-computed (Eg, is there a performance hit)? Or is it better to determine the computed value(s) first and used those in the query, for example:

DECLARE @order_count AS INT

SELECT
    @order_count = COUNT(order_id)
FROM Orders

SELECT
    @order_count AS num_orders
    , SUM(total) / @order_count as avg_total
    , SUM(discount) / @order_count AS avg_discount
FROM Orders

Note that, while writing this question I noticed that since SQL Server 2008 AVG() is supported. However, I continued this question and intend this to be more general to wanting to understand how SQL server handles Identical aggregates across columns as I do sometimes run into this in other forms.

SQL Server only calculates the COUNT once. You can see this by looking at the properties of the execution plan for

create table Orders(order_id int, total int, discount int)

SELECT
    COUNT(order_id) AS num_orders
    , SUM(total) / COUNT(order_id) as avg_total
    , SUM(discount) / COUNT(order_id) AS avg_discount
FROM Orders

The stream aggregate (1) has the following defined values

[Expr1008] = Scalar Operator(COUNT([tempdb].[dbo].[Orders].[order_id])),
[Expr1009] = Scalar Operator(COUNT_BIG([tempdb].[dbo].[Orders].[total])),
[Expr1010] = Scalar Operator(SUM([tempdb].[dbo].[Orders].[total])),
[Expr1011] = Scalar Operator(COUNT_BIG([tempdb].[dbo].[Orders].[discount])),
[Expr1012] = Scalar Operator(SUM([tempdb].[dbo].[Orders].[discount]))

Expr1008 is the calculation of the COUNT that you ask about.

There are some other COUNT aggregates for the other two columns. These are needed because the correct result for SUM(total) (for example) if COUNT(total) is 0 should be NULL.

This is carried out by the next compute scalar along (2). This also converts the COUNT result (Expr1008) from bigint to int and labels that as Expr1003

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(int,[Expr1008],0)),
[Expr1004] = Scalar Operator(CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END),
[Expr1005] = Scalar Operator(CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END)

Finally the left most compute scalar (3) uses Expr1003 in the division operation...

[Expr1006] = Scalar Operator([Expr1004]/[Expr1003]),
[Expr1007] = Scalar Operator([Expr1005]/[Expr1003])

... and outputs columns Expr1003, Expr1006, Expr1007 as the final result

PS: AVG has been supported much longer than SQL Server 2008. I imagine it has likely been available in the beginning. However it does not have the same semantics as your rewrite in the presence of NULLs anyway.

I assume order_id is the primary key and therefore not nullable but for a table with 10 orders and two NOT NULL total values of 2 and 4 then AVG(total) would be 3 but SUM(total) / COUNT(order_id) would be 0.6 (or 0 once integer division is taken into account).


I am getting error while restoring FileGroup on SQl server 2012 I have create two filegroup FG1 and FG2.
and i have create one table student and associated with FG1. and i have created backup for FG1 with Test.bak file .

now i wanted to restore the Test.bak

i am getting error like attached screen ..please advise

regard's
Ravi

The database engine thinks you're trying to perform a piecemeal restore using the UI.  This is the formal name of restoring individual filegroups.  Sadly, it requires you restore the entire database to maintain transactional consistency (which I don't think you want to do in your scenario) and since you're not overwriting the database (as identified by Pinal Dave's answer), you're getting this error.

If you're only trying to restore that one table, you've got a couple of options available, as follows:

You can restore the database as a different name and then copy the data from the restored copy into the working copy.
You can store that table in a different database and if you need to revert that table, restore the database it's contained within.  Of note, you are unable to maintain Referential Integrity between tables stored in different databases without some trickery (e.g. Triggers).  This may not be a problem now, but it could crop up issues in the future, so just be aware of this if you do separate out this table into a different database.


Upgrading to MySQL 5.6, benefits to switching to GTID replication? We have the most basic of MySQL replication setups, a single Master doing statement-based replication to a single Slave, a 90/10 mix of innodb/myisam tables. Both run Percona MySQL 5.5 at the moment but we are upgrading to 5.6 in a couple weeks and have scheduled downtime.

Will this kind of setup see much benefit by switching to GTID replication? Do I need to switch to row-based replication as well?

Trying to decide if it is worth the extra downtime for converting to and testing GTID right now.

I would say that you benefit from future proofing your environment, because inevitably you will want to add more slaves.

I also don't like having to remember specific coordinates of what statements have applied/haven't when doing a restore.  Having the server know the current set of applied statements is very useful to prevent duplicate processing/rogue corruption.  In 5.7 mysqlbinlog even has a --idempotent mode.

Operationally GTIDs are much easier to manage, and are the future :)

I recommend row-based replication as well - I think it's a great option:
http://www.tocker.ca/2013/09/04/row-based-replication.html


Can you have multiple Oracle ACL files? I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to create a separate ACL file for each user that needs access to the network, and then check that it has been created properly by using the following query:

SELECT * FROM DBA_NETWORK_ACLS;

However, it seems that only one record is ever returned for this query - always the latest ACL that's been created. Does this mean that you can only ever have one ACL? If so, presumably this means that when installing this application on a client's server, I must just add privileges to their existing ACL rather than trying to create a new one?

Thanks!

UPDATE

These are the commands I'm using:

--create ACL for USER1
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl1.xml',
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER1',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl1.xml',
                                     PRINCIPAL => 'USER1',
                                     IS_GRANT  => TRUE,
                                     PRIVILEGE => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl1.xml', host => '*');

--create ACL for USER2
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl2.xml',
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER2',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl2.xml',
                                     PRINCIPAL => 'USER2',
                                     IS_GRANT  => TRUE,
                                     PRIVILEGE => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl2.xml', host => '*');

The problem is that testacl2 seems to be overwriting testacl1. Does this mean that I can only have one ACL file, or one ACL file per host?

Check the Creating an Access Control List for External Network Services documentation (look at step 2, after the parameter descriptions):

  Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range.

Your second assignment simply replaces the first. You can only have one ACL file per host/domain.


TSQL - How to strip out debit card numbers from a string of text? We have a table that stores a large string of text and within the text is a debit card number, the string of text is copy and pasted from an email the customer has sent in.

How can I search through the 7000+ records and identify/replace the card numbers from 0000-0000-0000-0000 to XXXX-XXXX-XXXX-0000?


How can performance of queries containing keywords like IN, EXISTS, etc. be optimized? I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database).

For example, something like

SELECT id FROM table1 WHERE A IN (SELECT B FROM table2 WHERE C < 200)

The execution plan for this query involves a hash join that takes almost 2 seconds on a 1 GB database. Given that I may be executing many of these queries, are there any steps I can take to optimize these queries? Something along the lines of creating an index/materializing some data?

Thanks!

Try to use equivalent query with JOIN and without IN or EXISTS and compare the runtimes:

select id
from table1 join table2 on table1.A = table2.B
where table2.C < 200
group by id

Look also at similar question here, where similar optimization is proposed for much more complex query: https://stackoverflow.com/q/17038193/684229. But note that the JOIN solution is not always faster; sometimes, surprisingly, the IN/EXISTS queries come out as faster. Depends on the optimizer. That's why I suggest to test both variants.


Shrink data MDF file on server running on low disk space Before proceeding, I know the cons of doing this and have read many questions on same. But since we are here on urgent need and adding disk would take over a couple of days and the DB in question being not that critical, so is there a way I can release the unused space from database.

Below screenshot says unused space as 36 GB, but when I execute sp_spaceused it says unused as some 400 MB but unallocated much more.

I waited for almost 4 hours, trying to shrink in chunks, but not even a single MB got shrinked.

Is there a way this can be done or how can I manage the unallocated space?

Please advise.

Your sp_spaceused provides 2 sets of the data:
- DB Size + Unallocated space - These numbers include BOTH: Data and log file;
- Total statistics of RESERVED space for all objects within the database;

I bet your 36 GB of free space are in the Log file.

For real numbers use following query:

SELECT
    f.file_id as "File Id",
    f.physical_name as "Physical Name",
    CAST(CAST(ROUND(f.Size/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS SizeMB,
    CAST(CAST(ROUND(FILEPROPERTY(f.name, 'SpaceUsed')/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB,
    RIGHT('  ' + CAST(CAST((1 - FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size) * 100 as DECIMAL(5,2)) as VARCHAR(6)),6) AS FreeSpacePrc
    ,  CASE f.file_id WHEN 2 THEN 'Log File' ELSE IsNull(g.name,'N/A') END as [File Group]
FROM sys.database_files as f with (nolock)
LEFT JOIN sys.filegroups as g with (nolock) ON f.data_space_id = g.data_space_id

Might happen you still have some opened transactions, which hold you from shrinking individual files. See who hols them, close them. Reboot server if necessary. Shrink individual files while nobody accessing the database. switch it temporarily to Single User if necessary:

ALTER DATABASE <Your database> SET SINGLE_USER


Where do i change the my.cnf on mysql cluster I'm trying to change the max_allowed_packet setting on mysql cluster (version 7.4.8) - i have 5 nodes

2x SQL Node
2x Data Node
1x Managament Node

I have updated the my.cnf on all of the servers ->

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
user=mysql
ndbcluster
ndb-connectstring=manager-db
default_storage_engine=ndbcluster
port=3306
max_allowed_packet=1073741824

But show variables like '%max_allowed_packet%' still returns 524288000

What am i doing wrong ? where do i need to set it ?

The answer is indeed the SQL Nodes - the key factor is related to the client - the setting doesn't apply until you close the client session and reopen it


Redshift is missing in target database list of AWS SCT Schema Convertion Tool I want to convert schema of SQL Server database to Amazon Redshift using AWS SCT tool

But as target database, Redshift is not listed

When I use PostgreSQL compatible with IP address of the cluster, I experience a lot of Telemetry errors in the log

Any help is appretiated much
Thanks

OK, Amazon Redshift is only valid as a target database when you select OLAP or dataware house project option. If the project is created for an OLTP database Redshift is not an option as a target data platform.
Please refer to https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html for detailed list


DBCC CHECKDB running longer We are using Ola's Maintenance solution and part of that we have job DatabaseIntegrityCheck - User Database and System Databases, sometimes job for some of the databases it's running longer like in days and sometimes also it's skipping the DB
We have Sql server 2012 (Sql Server version is 11.0.5388.0 -Standard) and database is not that much big like under 10GB.
Someone's help really appreciated!

Added:

MAster DB is only 5 MB
Ola command:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b
 DBCC CHECKDB ([master]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

Script is not blocked, it's running for longer for Master and another user DB
Other Databases ran fine.

There's a strong possibility that CU 4 for SP2 which was release in January of 2015 will resolve the issue you are experiencing: https://support.microsoft.com/en-us/help/3029825/fix-dbcc-checkdb-checktable-command-may-take-longer-in-sql-server-2012

That being said, you are running a SQL server that is out of support and SP2 CU 4 which should resolve the issue is out of support as well. You should go ahead and patch the server to the current SP4 CU 10 + the latest security updates.

It appears to me that you may also be running an old version of OLAs scripts based on the way you are calling them. They should be updated as well.


MySQL cycling between periods of high I/O and high lock contention Starting about 2 days ago, my CloudSQL DB started having this periodic cycle of high latency. The time between these latency bursts vary, from 30 minutes to 50 minutes.

I finally set up a PMM instance today to get a better look at things. I see that the periods of high latency coincides with high active thread count and a lot of lock activity. CloudSql metrics show that these high latency periods also correlate with a reduction in the buffer pool dirty page count

Our workfloads are pretty consistent, certainly we have nothing running that's cyclic with a varying period. Changing some MySQL configs (io threads and lru scan depth) seem to have some impact on this period.

I uploaded some PMM screenshots here, of I/O, locks, and flushing over the last couple of hours. The pattern is pretty apparent.

Data I/O

Locks

Flushing

What is going on? I assume there is something going on under the hood of MySQL operating normally while using up some resource, then going through this period of high latency while trying to free up more of that resource to be able to operate normally again, but I can't piece together exactly what this is.

For more context: this started happening when we added a couple columns (unindexed) to a table. Since then we've reversed that ALTER TABLE and restarted the DB a couple of times to apply config changes. I'm interested both in finding a root cause and in applying a short term band aid to decrease the effect of these spikes.

Thanks!

Point 1 : Locking Management

You must check which processes are staying in locking state & what you can do it for locking i.e creating index for faster retrieval ...
You can check deadlock in show engine innodb status

Point 2: Redo Log Buffer & Files

Make sure the redo log buffer & files are big enough to capture many transaction.

Point 3: Flusing Method

Set innodb_flush_method=O_DIRECT for speedup with write operation.

Still this is not enough, you need to check Innodb buffer pool,OS level parameters(iostat,vmstat...)


Index scan on UNIQUE index for count(*) I've got a table t with ~23 million rows (4248 MB in size). There's a column row_id in it, with a not null constraint. And a unique index p1 on t(row_id).

When I do select count(*) from t to count all the rows in the table, the planner tells me:

Seq Scan on t  (cost=0.00..686191.06 rows=23176906 width=0)

I would have expected a fast Index Only Scan (index p1 occupies only 698 MB - 6x less).

If I do SET enable_seqscan = off, then the planner still insists on reading the table rows:

QUERY PLAN
  ->  Bitmap Heap Scan on t  (cost=210923.32..897114.38 rows=23176906 width=0)
        ->  Bitmap Index Scan on p1  (cost=0.00..205129.09 rows=23176906 width=0)

Why is the unique index ignored in this case? What's the catch?

I am using PostgreSQL 10.4

For a clean room test I did the following:

create table tmp
(
  row_id      varchar(15) unique not null,
   <10 original cols>
);

insert into tmp (row_id, <10 cols>) select row_id, <10 cols> from t;
commit;
analyze tmp;

set enable_seqscan = on;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=744070.45..744070.46 rows=1 width=8) (actual time=5631.501..5631.502 rows=1 loops=1)
  Buffers: shared hit=209109 read=245254
  ->  Seq Scan on tmp  (cost=0.00..686128.96 rows=23176596 width=0) (actual time=0.014..3481.967 rows=23176906 loops=1)
        Buffers: shared hit=209109 read=245254
Planning time: 0.064 ms
Execution time: 5631.531 ms

SET enable_seqscan = off;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=980282.14..980282.15 rows=1 width=8) (actual time=16224.408..16224.408 rows=1 loops=1)
  Buffers: shared hit=26285 read=542015
  ->  Bitmap Heap Scan on tmp  (cost=236211.69..922340.65 rows=23176596 width=0) (actual time=10030.115..14157.288 rows=23176906 loops=1)
        Heap Blocks: exact=454363
        Buffers: shared hit=26285 read=542015
        ->  Bitmap Index Scan on tmp_row_id_key  (cost=0.00..230417.54 rows=23176596 width=0) (actual time=9929.582..9929.582 rows=23176906 loops=1)
              Buffers: shared hit=26285 read=87652
Planning time: 0.051 ms
Execution time: 16229.303 ms

No parallel index scan so far. PostgreSQL insists on accessing the table for some obscured reason.

As to why you do not get an index scan with SET enable_seqscan = off, you should be getting an index-only scan. Your situation can not yet be recreated with the data you've provided. This certainly works with PostgreSQL 10.4. I can't speak to your own use case, and there are a lot of reasons why you may not get an index scan in the real world. Ultimately, debugging a question along those lines will result in an answer that is simply "planner estimates" but will require a lot more data about your environment, configuration, and the plans with and without SET enable_seqscan = off.

Sample Data

BEGIN;
  CREATE TABLE foo ( x int NOT NULL UNIQUE );
  INSERT INTO foo (x) SELECT generate_series(1,1e6);
COMMIT;

ANALYZE foo; -- don't forget to analyze

With seq_scan

test=# EXPLAIN SELECT count(*) FROM foo;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8)
   ->  Gather  (cost=10633.33..10633.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8)
               ->  Parallel Seq Scan on foo  (cost=0.00..8591.67 rows=416667 width=0)
(5 rows)

Notice we're doing a "Parallel Seq Scan"

Without seq_scan

SET enable_seq_scan = off;

test=# EXPLAIN SELECT count(*) FROM foo;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=26616.97..26616.98 rows=1 width=8)
   ->  Gather  (cost=26616.76..26616.97 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=25616.76..25616.77 rows=1 width=8)
               ->  Parallel Index Only Scan using foo_x_key on foo  (cost=0.42..24575.09 rows=416667 width=0)
(5 rows)

Notice we're doing a "Parallel Index Only Scan"


DBCC CheckDB run into early hardware failure Our production database server (SQL 2008 R2 Enterprise on Windows Server 2008 R2 DC) run into a strange error during its nightly DBCC CheckDB (Prod_DB) since late June. The same job had been running fine up then.

I have restored the full backup from this server to a test server, and the data file seems ok, and the DBCC CheckDB returned no error there.

I applied this hotfix last week but did not get the issue resolved.

https://support.microsoft.com/en-us/kb/967351/en-us

The message sounds serious enough, however, the application is otherwise functioning normally. So I am not sure what I am having here.

It will be grateful if any one could share any advice.

Here is the error message from SQL agent job history:

Date                      7/28/2015 4:02:00 AM
Log                         Job History (DBCC Check DBs)

Step ID                 1
Server                   XXX
Job Name                            DBCC Check DBs
Step Name                         check dbs
Duration                              00:12:37
Sql Severity        17
Sql Message ID 823
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted          0

Message
Executed as user: XXX. During undoing of a logged operation in database

'Prod_DB', an error occurred at log record ID (21235:2462484:113).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE 42000] (Error 3314)  A database snapshot cannot be created because it failed to start.
[SQLSTATE 42000] (Error 1823)  A database snapshot cannot be created because it failed to start.
[SQLSTATE 42000] (Error 1823)  The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
[SQLSTATE 42000] (Error 7928)  The database could not be exclusively locked to perform the operation.
[SQLSTATE 42000] (Error 5030)  Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
[SQLSTATE 42000] (Error 7926)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001a68024000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
[SQLSTATE 01000] (Error 823)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00002fd543e000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
[SQLSTATE 01000] (Error 823).  The step failed.

When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:

Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.

Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.


backup of databases - consistently back up databases with control of log truncates My question is how can I back up my database by controlling the log truncates?

I need to perform a testing with VEBF (Veeam Endpoint Backup FREE). A test case that I have to make now is that I have to back up Windows Server 2012 R2 as well as SQL Server 2014 with this software, ie the databases and the requirement to do so is to control the log truncates. The database I am using already has the Recovery Model "Simple".

VEBF appears to be a desktop/workstation backup software, and doesn't (directly) support any capability for SQL server, except for backing up SQL BAK files you have already created via a SQL native backup.

Are you talking about Veeam Explorer for Microsoft SQL Server?

If your databases are in Simple mode, you don't need to do anything to "control your log truncates". When transactions complete, SQL will automatically re-use that portion of the transaction log. If you are somewhat impatient, you can issue a "checkpoint", but that really shouldn't be necessary.


Massive INSERTs blocking SELECTs I have a problem with a massive amount of INSERTs that are blocking my SELECT operations.

Schema

I have a table like this:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

I also have this little helper procedure, that allows me to insert or update (update on conflict) with MERGE command:

CREATE PROCEDURE [InsertOrUpdateInverterData]
    @InverterID bigint, @TimeStamp datetime
    , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
    MERGE [InverterData] AS TARGET
        USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
        AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
        ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
    WHEN MATCHED THEN
        UPDATE
        SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]
    WHEN NOT MATCHED THEN
        INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB])
        VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

Usage

I now have run service instances on multiple servers that perform massive updates by calling the [InsertOrUpdateInverterData] procedure rapidly.

There is also a website that does SELECT queries on the [InverterData] table.

Problem

If I do SELECT queries on the [InverterData] table they are proceeded in different timespans, depended on the INSERT usage of my service instances.
If I pause all service instances the SELECT is lightning-fast, if the instance perform rapid insert the SELECTs get really slow or even a timeout cancel.

Attempts

I'm done some SELECTs on the [sys.dm_tran_locks] table to find locking processes, like this

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

This is the result:

S = Shared. The holding session is granted shared access to the resource.

Question

Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands?

Do I have to use some kind of transaction with defined isolation mode inside of [InsertOrUpdateInverterData]?

Update 1 (related to question from @Paul)

Base on MS-SQL server internal reporting about [InsertOrUpdateInverterData] following statistic:

Average CPU-Time: 0.12ms
Average Read processes: 5.76 per/s
Average Write processes: 0.4 per/s

Base on this it looks like the MERGE command is mostly busy with reading operations that will lock the table!(?)

Update 2 (related to question from @Paul)

The [InverterData] table as has following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Here is the (allmost) complete sp_WhoIsActive result set:

SELECT command

dd hh:mm:ss.mss: 00 00:01:01.930
session_id: 73
wait_info: (12629ms)LCK_M_S
CPU: 198
blocking_session_id: 146
reads: 99,368
writes: 0
status: suspended
open_tran_count: 0

Blocking [InsertOrUpdateInverterData] command

dd hh:mm:ss.mss: 00 00:00:00.330
session_id: 146
wait_info: NULL
CPU: 3,972
blocking_session_id: NULL
reads: 376,95
writes: 126
status: sleeping
open_tran_count: 1

First, though slightly unrelated to the main question, your MERGE statement is potentially at risk of errors due to a race condition. The problem, in a nutshell, is that it is possible for multiple concurrent threads to conclude that the target row does not exist, resulting in colliding attempts to insert. The root cause is that it is not possible to take a shared or update lock on a row that does not exist. The solution is to add a hint:
MERGE [dbo].[InverterData] WITH (SERIALIZABLE) AS [TARGET]

The serializable isolation level hint ensures the key range where the row would go is locked. You have a unique index to support range locking, so this hint will not have an adverse effect on locking, you will simply gain protection against this potential race condition.
Main Question

Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands?

Under the default locking read committed isolation level, shared (S) locks are taken when reading data, and typically (though not always) released soon after the read is completed. Some shared locks are held to the end of the statement.
A MERGE statement modifies data, so it will acquire S or update (U) locks when locating the data to change, which are converted to exclusive (X) locks just before performing the actual modification. Both U and X locks must be held to the end of the transaction.
This is true under all isolation levels except the 'optimistic' snapshot isolation (SI) not - to be confused with versioning read committed, also known as read committed snapshot isolation (RCSI).
Nothing in your question shows a session waiting for an S lock being blocked by a session holding a U lock. These locks are compatible. Any blocking is almost certainly being caused by blocking on a held X lock. This can be a bit tricky to capture when a large number of short-term locks are being taken, converted, and released in a short time interval.
The open_tran_count: 1 on the InsertOrUpdateInverterData command is worth investigating. Although the command hadn't been running very long, you should check that you don't have a containing transaction (in the application or higher-level stored procedure) that is unnecessarily long. Best practice is to keep transactions as short as possible. This may be nothing, but you should definitely check.
Potential solution
As Kin suggested in a comment, you could look to enable a row-versioning isolation level (RCSI or SI) on this database. RCSI is the most often used, since it typically does not require as many application changes. Once enabled, the default read committed isolation level uses row versions instead of taking S locks for reads, so S-X blocking is reduced or eliminated. Some operations (e.g. foreign key checks) still acquire S locks under RCSI.
Be aware though that row versions consume tempdb space, broadly speaking proportional to the rate of change activity and the length of transactions. You will need to test your implementation thoroughly under load to understand and plan for the impact of RCSI (or SI) in your case.
If you want to localize your usage of versioning, rather than enabling it for the whole workload, SI might still be a better choice. By using SI for the read transactions, you will avoid the contention between readers and writers, at the cost of readers seeing the version of the row before any concurrent modification started (more correctly, the read operation under SI will always see the committed state of the row at the time the SI transaction started). There is little or no benefit to using SI for the writing transactions, because write locks will still be taken, and you'll need to handle any write conflicts. Unless that is what you want :)
Note: Unlike RCSI (which once enabled applies to all transactions running at read committed), SI has to be explicitly requested using SET TRANSACTION ISOLATION SNAPSHOT;.
Subtle behaviours that depend on readers blocking writers (including in trigger code!) make testing essential. See my linked article series and Books Online for details. If you do decide on RCSI, be sure to review Data Modifications under Read Committed Snapshot Isolation in particular.
Finally, you should ensure your instance is patched to SQL Server 2008 Service Pack 4.


SQL binaries on C drive? We are setting up a new SQL Server with over 2 TB size of Database, and this is a heavy OLTP. Let me know, if there is any impact of placing SQL binaries in C drive? or i would need another Drive? Server is already having these drives

SQL Server databases data ﬁles – G
SQL Server database log ﬁles – L
TempDB data ﬁle(s) – T (local drive)
Backups – H:

And these are Pure FlashArray storage mounted onto Windows.
SQL server 2017 Standard version will be installed on MS Windows Server 2016 Standard.

Your help is really appreciable. Let me know, if you need any other details?
Also let me know, any best practice for SQL installation.

The binaries can live on C:\ or another system drive (though said drive should be formatted using the default block size).  My personal preference is to place them on another system drive (again, formatted using the default block size) because of the following reasons:

The page file, by default, resides on the C:\ drive
Other processes keep all manner of junk on the C:\ drive, by default (e.g. Windows patches, C:\Temp, etc.)
SQL Server log files are written to where the binaries are installed, by default
I'm lazy and don't want to do a whole bunch of post-install reconfiguration, and this also makes it easier for future DBAs to find where the SQL-related log files are located in the event of an outage, etc.

The main point here is that between the page file, patching, etc. the C:\ drive can be utilized by processes outside of your control as a DBA.  The problem with keeping the SQL Server log files on the same drive as these other things is that over time this drive can (and generally does) get crowded which can potentially cause a system outage if it becomes full and you can't write to it.

Regardless where you install binaries, I also recommend you make sure the system databases (e.g. master, msdb, model) are located on your data drive.  If they're also located on the C:\ drive, they too can contribute to disk crowding with their growth over time.  Finally, just make sure the block size for the disks hosting database files (e.g. mdf, ldf, and backup files) are all formatted with 64Kb block size.  It's much more of a pain to make this happen after you lay down the binaries than before.

Final note; as pointed out by Aaron Bertrand in the comments, there are still some files that will always be created on the C:\ drive, such as various dlls and the Setup Bootstrap log files.  Leave those alone as they are necessary to keep your service running, but for me, moving most of my SQL Server Binaries off of the C:\ drive often proves beneficial in the long run.


Update a table from a join I have a table GEMS_PROJECTS:

du_proj_id
du_id
Proj_id
proj_desc
isactive
transuser
trandate

A second table GEMS_DEPARTMENT:

du_proj_id
dept_id
de_dec
isactive
transuser
trandate

du_proj_id from second table refers to du_id from first table and I need update second table isactive field.

Note duid getting from user to pass first table and second table.

I assume you are trying to set isactive in the department table to the same value currently in projects, for all rows involving the specified duid.

UPDATE d
  SET isactive = p.isactive
FROM dbo.GEMS_DEPARTMENT AS d
INNER JOIN dbo.GEMS_PROJECTS AS p
ON RTRIM(d.du_proj_id) = p.du_id
WHERE p.du_id = @duid;

The most common answer for this problem is found here.


Building year to date aggregations using SQL I need some help with building a monthly aggregation using only SQL.

Imagine the following table:

TranID  DateCode    Account Value
   1    20140101        1   5
   2    20140106        1   -3
   3    20140207        1   6
   4    20140409        1   3
   5    20140103        2   3
   6    20140215        2   7
   7    20140519        2   6

There are two accounts that have transactions on various dates. I would like to write code that gives me this result assuming we are in July:

MonthID     Account YTD
20140101        1   2
20140201        1   8
20140301        1   8
20140401        1   11
20140501        1   11
20140601        1   11
20140701        1   11
20140101        2   3
20140201        2   10
20140301        2   10
20140401        2   10
20140501        2   16
20140601        2   16
20140701        2   16

I'm thinking I should be able convert the dates to MonthCode using

DATEADD(day, -DAY(DateCode) + 1, DateCode) AS MonthCode

I'm thinking I should be able to solve this somehow by joining the table to itself but I don't really get the numbers right. Also I need to somehow get the months without transactions in as well..

Any and all help will be greatly appreciated!

Code to generate the mock data:

Create table #Tran(
TranID int identity(1,1),
DateCode Date,
AccountCode varchar(50),
Value int
);

insert into #Tran (DateCode, AccountCode, Value)
values ('20140101', '1', 5),
('20140106', '1', -3),
('20140201', '1', 6),
('20140401', '1', 3),
('20140101', '2', 3),
('20140201', '2', 7),
('20140501', '2', 6);

For SQL Server 2012, given this sample data:

CREATE TABLE #t(TranID INT, DateCode DATE, Account INT, Value INT);
CREATE CLUSTERED INDEX x ON #t(Account, DateCode);

INSERT #t VALUES(1,'20140101',1,5 ),(2,'20140106',1,-3),(3,'20140207',1,6 ),
(4,'20140409',1,3 ),(5,'20140103',2,3 ),(6,'20140215',2,7 ),(7,'20140519',2,6 );

I would use this query (imagine the first line as stored procedure parameters):

DECLARE @year INT = 2014, @cutoff DATE = NULL; -- just state the year

-- and optionally an explicit cutoff date (leave NULL for today)
SET @cutoff = '20140731';

DECLARE @startdate DATE = DATEADD(YEAR, @year-1900, 0);

;WITH accounts(a) AS
(
  SELECT DISTINCT Account FROM #t
    WHERE DateCode >= @startdate
    AND DateCode < DATEADD(YEAR, 1, @startdate)
),
months(m) AS
(
  SELECT TOP (12) DATEADD(MONTH, ROW_NUMBER() OVER
      (ORDER BY s.[object_id])-1, @startdate)
    FROM sys.all_objects AS s
),
s AS
(
  SELECT a.a, m.m, v = COALESCE(SUM(t.Value),0)
  FROM accounts AS a
  CROSS APPLY months AS m
  LEFT OUTER JOIN #t AS t
    ON t.DateCode >= m.m
    AND t.DateCode < DATEADD(MONTH, 1, m.m)
    AND t.Account = a.a
  WHERE m.m < COALESCE(@cutoff, SYSDATETIME())
  GROUP BY a.a, m.m
)
SELECT
  MonthID = m,
  Account = a,
  YTD = SUM(v) OVER(PARTITION BY a ORDER BY m ROWS UNBOUNDED PRECEDING)
FROM s
ORDER BY a,m;

If you are using a version older than SQL Server 2012, then you may want to try some of the alternative approaches here:

Best approaches for grouped running totals


What is the meaning of tinyint(N)? When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:

mysql> create table boolean_test (var1 boolean, var2 tinyint);
Query OK, 0 rows affected (0.10 sec)

mysql> show create table boolean_test;
+--------------+-------------------------
| Table        | Create Table
+--------------+-------------------------
| boolean_test | CREATE TABLE `boolean_test` (
  `var1` tinyint(1) DEFAULT NULL,
  `var2` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+---------------------------
1 row in set (0.00 sec)

First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.

mysql> insert into boolean_test values(101,112);
Query OK, 1 row affected (0.03 sec)

mysql> select * from boolean_test;
+------+------+
| var1 | var2 |
+------+------+
|   10 |  112 |
|  101 |  112 |
+------+------+
2 rows in set (0.00 sec)

I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1).
Shouldn't I see a 1 for var1? I.e. just 1 display digits?

Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint.

You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For example, the Connector/J (Java connector) treats tinyint(1) as a boolean value, and instead of returning a numerical result to the application, it converts values to true and false. this can be changed via the tinyInt1isBit=false connection parameter.


Monitoring Availability Groups I'm trying to find what query (or queries) need to be run in order to determine if there is any sort of a problem with an availability group.  I have been using:

SELECT @@SERVERNAME AS 'primary_replica', cs.database_name, rs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states rs
join sys.dm_hadr_database_replica_cluster_states cs on rs.replica_id =
    cs.replica_id and rs.group_database_id = cs.group_database_id
WHERE rs.is_local = 1

It turns out that the synchronization health (select * from sys.dm_hadr_availability_group_states) can be NOT_HEALTHY, but the query above does not show any problem with the replica.  I have tried looking through all of the various DMs that contain information, but it is a maze or words and I can't find my way to the SQL cheese that must be there somewhere.

What is the one query that will indicate that there is a problem that needs to be addressed?  I don't need it to be specific or to designate the cause, I just need to know that there is a problem.  I know that during failover some of the states will be NOT_HEALTHY, and we account for that by requiring x minutes of unhealthiness before sending an alert.

Following is a SQL query that can be run against all nodes in the cluster to check all of the AG-related health states.  The output could be formatted differently depending on your needs, but this one returns either a list of the objects that are not healthy, or a row that includes the server name and that all objects are healthy.

DECLARE @HealthStates AS TABLE (objectname VARCHAR(50), [type] VARCHAR(20), health_desc tinyint)

/*    Collect local database replica states    */
INSERT INTO @HealthStates
    SELECT cs.[database_name], 'database_replica', rs.synchronization_health
    FROM sys.dm_hadr_database_replica_states rs
    join sys.dm_hadr_database_replica_cluster_states cs ON rs.replica_id = cs.replica_id and rs.group_database_id = cs.group_database_id
    WHERE rs.is_local = 1

/*    Collect local AG states   */
INSERT INTO @HealthStates
    SELECT ag.[name], 'availability_group', gs.synchronization_health
    FROM sys.dm_hadr_availability_group_states gs
    join sys.availability_groups_cluster ag ON gs.group_id = ag.group_id
    WHERE gs.primary_replica = @@SERVERNAME

/*    Collect replica states   */
INSERT INTO @HealthStates
    SELECT cs.replica_server_name, 'server_replica', rs.synchronization_health
    FROM sys.dm_hadr_availability_replica_states rs
    join sys.availability_replicas cs ON rs.replica_id = cs.replica_id and rs.group_id = cs.group_id

/*    States to grid   */
--SELECT objectname, [type], health_desc FROM @HealthStates

/*    Test for not healthy   */
IF EXISTS (SELECT health_desc from @HealthStates where health_desc <> 2)
BEGIN
    SELECT objectname, type, 'NOT_HEALTHY' as health_desc from @HealthStates where health_desc <> 2
END
ELSE
BEGIN
    SELECT @@SERVERNAME AS objectname, 'All Objects' AS type, 'HEALTHY' as health_desc
END


Error Unknown command '\U' when restoring MySQL dump on Windows I am updating software that used MySQL 5.4.3 in its old version, and now uses MariaDB 10.2.11.
So, I dumped the old data with that command:

mysqldump -uroot -p --no-create-info --skip-triggers --complete-insert theDBName -r C:\Users\myUserName\theDBName_data.dump

Then I uninstalled MySQL and replaced it by MariaDB. After that I created the database and imported the new data structure (a few columns have been added with default values).

The issue occurs when restoring the old data:

mysql -uroot -p --default-character-set=utf8 theDBName
Enter password: ****************

MariaDB [theDBName]> SET names 'utf8';
MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump
ERROR: Unknown command '\U'.
ERROR: Unknown command '\a'
ERROR: Unknown command '\a'
ERROR: Failed to open file 'C:\Users\myUserName\theDBName_data.dump', error: 2

Restoring the exact same dump with that procedure works well on Linux, but doesn't on Windows.

Thanks for your help.

MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump

Either of the following should be correct:

MariaDB [theDBName]> SOURCE C:\\Users\\myUserName\\theDBName_data.dump

MariaDB [theDBName]> SOURCE C:/Users/myUserName/theDBName_data.dump


Oracle DBLink with username and password I am trying to connect to a second database via a dblink, but the password has not been included in the dblink.
Is there a way you can provide the password dynamically in the dblink when you call it

e.g.

SELECT * FROM TABLE@USERNAME/PASSWORD.DBLINK;

Thanks in advance.

K

No, there isn't.

Database links created without username and password use authentication data of your current session.

Just create another database link with username + password included.