How to get the DDL for Oracle object without extra characters When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script.
For example, if I create this sample procedure:

CREATE OR REPLACE PROCEDURE TEST
IS
BEGIN
 NULL;
END;
/

then dbms_metadata.get_ddl for this procedure returns extra newline and two space characters at very beginning:

select dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 ) text
, dump(dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 )) dump_text
FROM dual;

Result is:
  CREATE OR REPLACE PROCEDURE "TEST"
Typ=1 Len=37: 10,32,32,67,82,69,65,84,69,32,79,82,32,82,69,80,76,65,67,69,32,80,
82,79,67,69,68,85,82,69,32,34,84,69,83,84,34

As you can see there are 10,32,32,, just before CREATE OR REPLACE.
Any idea how to get rid of these whitespaces?
Also there are extra " around procedure name.

The following will get rid of the LF and 2 spaces using a function though it would probably be better/easier to do it in your text editor.

select
   REPLACE(dbms_lob.substr(
        dbms_metadata.get_ddl('FUNCTION','REMAP_SCHEMA', 'ETL'),
      37, 1), CHR(10)||CHR(32)||CHR(32)||'CREATE','CREATE') text
   from dual

Obviously the first word that you are interested in keeping would need to be 'CREATE' for this to work. You could also use one of the REGEXP functions but I don't see that being any better/different to this method.
I'm also not sure why you particularly need to get rid of them.


Dependant T-SQL comparison collation Is there any way to set the collation for a string comparison based on an independent variable or column value?  Something akin to

SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
    COLLATE CASE CASE_SENSITIVE
                WHEN 1 THEN SQL_Latin1_General_CP1_CS_AS
                ELSE SQL_Latin1_General_CP1_CI_AS
            END

or

DECLARE @collation AS VARCHAR(50)
SET @collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
    COLLATE @collation

Along the lines of this with 2 where conditions.

One will be become '%string%' LIKE '%string%' which is always true

WHERE
    CASE WHEN @casesenstive = 1 THEN COL3 ELSE '%string%' END
           COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%string%'
    AND
    CASE WHEN @casesenstive = 0 THEN COL3 ELSE '%string%' END
           COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%string%'


Can data be replicated from a MySQL slave back to a master without the slave containing all the master data? We have a PHP/Symfony 2 application using MySQL as the database. The database contains a large amount of data.

The application is deployed to a separate server(s) and an empty/clean version of the database deployed. The application is then run on a local network as the application is used where internet access is not always available (hospitals, banks etc).

Once the application has collated all the information are there any solutions for replaying the data back to the original database?

We are not using Master/Slave as we do not want the Slaves to contain all data from the original database.

When using master/slave you do not need to replicate the all instance.
You can go up the the table with the replicate-wild-do-table parameter.

May be this could help you in what you are trying to do


Cannot access MySQL database after moving files in data directory I am using Xampp locally and I recently did a computer upgrade where I went from XP to Windows 7 and also got a new drive to install Windows on.

I have all my old Xampp files in: G:\xampp
I installed the new Xampp in: C:\xampp (which is the directory location they were previously in)

I then copied over everything in  G:\xampp\mysql\data to C:\xampp\mysql\data.

When I view the specific database in SQLYog I can see everything fine apart from one table which says:

  Table 'qotm.wp_36rwq4kcgt_options' doesn't exist

...but when I try and connect to the database via a script I get:

  InnoDB: Cannot open table qotm/wp_36rwq4kcgt_options from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

I visited that link and the closest thing I could find relevant was the part under Problem Opening Table; however I do not have any  #sql-.ibd* files in that directory, nor any .ibd files whatsoever in there.

I then found this post but it only mentions .ibd files which I do not have any of.

Ok, the problem was because I didn't copy the ibdata* file. You may expect this file to be located within the respective database folders just like the other related files, but you would be wrong.

My ibdata file was located at mysql/data/ibdata1; as soon as I copied that to the new location everything was good again.

Note however I have read of this file being located in different locations, so your mileage may vary.


How to set AUTHORIZATION on CREATE SCHEMA I'm looking to create a new schema for an existing database, which currently only uses the dbo schema. I'm after advice on how to set the AUTHORIZATION (owner name) and what affect this will have.

1. Should I create a new user for this or can I use the dbo user?

I notice the existing Database Roles in the database are owned by the dbo user.

2. If I set the AUTHORIZATION to a new user will I need to create new Database Roles owned by the new user?

I've also noticed the Default schema for existing Users is the dbo schema.

3. Will I need to create new Users with a default schema set to the new schema?

My main reason for creating the new schema is to separate the database objects by name rather than for permissions. I intend on granting certain existing Users access to specific database procedures on the new schema as we do for database procedures on the dbo schema.

You do not need to create a new user to be owner of the schema.

The default dbo owner is fine, especially if your adding the schema for organization reasons rather then security.

You do not need to create new users to access the new schema.

The only thing I would add is to make sure if you do change the owner from dbo, you change it to a domain service account or other permanent sql account. If the user gets deleted it can cause issues accessing the schema.


Transactional Replication breaks when schema is changed in Publisher We have set up Transactional replication where two Publishers feed into one Subscriber. Now if try to change the schema of any one Publisher or both publishers, replication breaks.
We have tried to pause the replication and change the schema in publisher as well as subscriber, but as soon as we re-start the replication it breaks.
The only option that we found is, we have to drop the replication, sync the schema and the start the snapshot replication.
Is there a better way of doing this?

From Make Schema Changes on Publication Databases:

Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table. You cannot drop published objects, therefore the schema change fails.


Postgresql querying trends Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches

So I have a database which records motor races, with the following simplified schema

race_table
==========
race_id PK
race_date timestamp
average_speed Decimal
max_speed Decimal

drivers
=======
driver_id PK
driver_name text
date_started timestamp

driver_races
============
driver_id FK
race_id FK

If each driver has ~1000 races spread over 2/3 years

How would I go about querying the overall % change (positive of negative) in their average race speed for a given date range, for example

% Change in first 6 months

Joe Smith - 5% increase
Andy James - 4% increase

% Change in first 12 months

Joe Smith - 8% increase
Lewis May - 6% increase

UPDATE: More detail on % Change

The % Change could be calculated using linear regression, (least-squares-fit would be suitable), the average change is effectivly the y-difference on a line-of-best-fit, where each point is a race, x is the race_date and y is the average_speed for that race.

Postgres's regr_slope will give the gradient of the line which is effectivly the same as the %change

SELECT regr_slope(r.average_speed, EXTRACT(EPOCH FROM r.race_date)) as slope
    FROM race_table as r, driver_races as dr
    WHERE dr.race_id = r.race_id
    AND d.driver_id = 1

This gives the figure I want, but I now need to apply is against all users, sorted by 'slope'

Data model
You are saving avg. and max. speed per race, not per driver? Normally this would be something like:

CREATE TABLE race (
  race_id   serial PRIMARY KEY
 ,race_date timestamp
);

CREATE TABLE driver (
  driver_id    serial PRIMARY KEY
 ,driver_name  text
 ,date_started timestamp
);

CREATE TABLE driver_race (
  driver_id int REFERENCES driver(driver_id)
 ,race_id   int REFERENCES race(race_id)
 ,average_speed numeric
 ,max_speed numeric
);

This is also how you should provide your data model: as CREATE scripts ready to use for testing.
numeric and decimal are the same in Postgres.
Query
Your query extended to all drivers:
SELECT driver_id, d.driver_name, x.slope
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,regr_slope(dr.average_speed, EXTRACT(EPOCH FROM r.race_date)) AS slope
   FROM   race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY slope DESC;

Simple alternative
A simple alternative would be to match the average speed of the first half of a time period to the average of the second half:
SELECT driver_id, d.driver_name
      ,round(x.avg1::numeric, 2) AS avg1
      ,round(x.avg2::numeric, 2) AS avg2
      ,round((x.avg2 / x.avg1 - 1) * 100, 2) AS pct_change
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-01'::date AND '2013-3-15'::date
              THEN dr.average_speed END) AS avg1
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-16'::date AND '2013-3-31'::date
              THEN dr.average_speed END) AS avg2
   FROM    race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY pct_change DESC;

The CASE expression has no ELSE branch, because the default NULL serves just fine: avg() ignores NULL values.
-> SQLfiddle demo.


Cannot find sqlserver service I installed Sql Server 2019 in Windows PC.
In Sql Server Configuration Manager into SQL Server Service I stopped all the instances.
But still I can loggin to Sql Server via SSMS.why is that? Where is the Sql Server service?
In Windows Services I can see SQL Server (MSSQLSERVER01) that running and when I stopped it,I can't login to sqlserver. But why I can't see that service running in Sql Server Configuration Manager into SQL Server Service.
I login with server name:DESKTOP-IJ7544\MSSQLSERVER01

There are 2 types of SQL Server installed on a PC:

Server based - named instances such as \sqlexpress, \instance name or unnamed instances such as . All of these instances can be stopped by going to services.msc and stopping the service. By default, most people install unnamed instances on PCs unless they fully intend to install a named instance.
File-based - this is your localDB that may come with an installation of Visual Studio

From all indications, you may have more than one server-based installation - a unnamed instance for which you have stopped the service in services.msc and a named instance which you can still connect to.
I would go to services.msc and check to see if any of the sql instances services have a status of running. If they do, that’s why. While in services, typing “s” or “sq” fast enough should get you there faster.
Sql configuration is great for particular versions but may not reveal other instances beyond the version for which it was installed.


Update table from calculated temporary table I have two tables, one containing a set of user ID's, vote direction (-1 or 1) and ID of the object they voted on (casted votes table). In the other table, the object id, upvotes, downvotes and totalvotes.

I'm trying to write a command to update the values in the second table by tallying the votes in the table of votes cast. I'm trying to think of the best way to do this, currently I am thinking of generating a temporary table that contains Object Id, upvotes, downvotes and totalvotes from the votes cast table (using a few SUM functions and some other stuff to generate the columns), then using an update query to go through and change the values in the second table by referencing the temporary table.

Is this the best way to do it or am I massively overlooking a feature that does this already without all the messing around in MySQL?

This might be a good case for using an update trigger.

Look at some of the examples here: http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

What you'll basically do is create a trigger on the first table that iterates a matching row in the second table when a row is added to the first table (on insert).


Oracle Creating ER Diagrams and Data Dictionaries I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?

Oracle's Data Modeller can reverse-engineer from an existing database. They were charging for it at one time, but it is now free to use.


MySQL: not quoting WHERE conditions slows query execution I just ran into a curious issue with MySQL. I had a very slow query, even if properly indexed, which was like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = 10

As said both parm1 and parm2 had indexes. The query was taking from 0.2s up to 120s, depending on the searched values.

Then I quoted the search terms like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = '10'

and now the execution takes 0.02s! Why that?

First query should give you an error as you're filtering items with string,
the datatype would be CHAR or VARCHAR.

If you you integer, in same columns, the query will rub without quotes but it will be slow because of string datatype.

Second Query is proper written,

However for more performence,
you can also create combine index on (parm1, parm2) with

CREATE INDEX tbl11 ON tbl1 (parm1, parm2))


MySQL Slave Relay Logging but not logging Binary Log I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of understanding on the matter but my ultimate goal is to setup another slave feeding off the current slave to then point the two together for a master-master relationship.

My problem is that although on the slave I can insert a record and the master replication position (on the slave) will change, it will not change as a result of slave activity on the same database. Will the slave not dump activity into both logs? If i point the secondary slave to relay logs what are my options down the line?

Hope that makes sense.

my my.ini setup on the slave is as such, a unique server id and hostname are also set:

binlog-do-db=our_database
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=our_database

You forgot one thing
binlog-do-db=our_database
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=our_database
log-slave-updates

The option log-slave-updates takes SQL that was read from relay logs and records them in the Slave Binary Logs when the SQL is completed.
Please be very careful. You should never use binlog-do-db and binlog-ignore-db simultaneously. I wrote about this back on Jun 14, 2012 ( Configured MySQL replication but its not working ) which points to an old Percona article on binlog-do-db being dangerous.
SUGGESTION
Of the things you are using, you only need these lines
replicate-do-db=our_database
log-slave-updates

You really don't need binlog-do-db=our_database once you have log-slave-updates working properly.


Migrating mysql.user table from MySQL 5.5 to MySQL 5.6 I have a tricky question.

I am migrating from MySQL 5.5 to MySQL 5.6.
I want to migrate 45 users in MySQL 5.5 to MySQL 5.6

Questions

Should I just take a dump of the MySQL 5.5 user table and restore on MySQL 5.6 ?
MySQL 5.6 user table has more columns then MySQL 5.5 so was wondering if that was doable.
Any other advise ?

You could load the mysql schema into the 5.6 instance and run mysql_upgrade. I have a much cleaner method in mind: You could dump the grants as pure SQL from the MySQL 5.5. instance and then run it in the MySQL 5.6 instance.
You can do one of the following:

Extract the grants as SQL using pt-show-grants.
Run my personal emulation of pt-show-grants:

mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql

Once you create the script from the 5.5. instance, run the script in the 5.6 instance.
GIVE IT A TRY !!!
Note : I have a post where I helped someone manually fix mysql.user (MySQL service stops after trying to grant privileges to a user). Try this only when everything else fails ...


Always on without clustering Is it possible to setup always on QL Server 2016/2017 without windows clustering and domain?

My intention of implementing always-on and secondary replica is offloading the select query from the primary database server. I'm not expert at windows clustering, so I'm looking for an easy solution to implement always-on as I don't need automatic failover.

Is it possible to setup always on QL Server 2016/2017 without windows clustering and domain? My intention of implementing always-on and secondary replica is offloading the select query from the primary database server.

Yes, it is possible but will require SQL Server 2017 (I'd also apply the latest CU, which at time of posting is CU4).

Please note that without a domain, SQL Authentication is the only available authentication type and there is also the additional endpoint setup of using certificates.

If you'd like to read more about read-scale availability groups (Not for HA or DR) the Docs articles are a good start. I've also blogged about it when it comes to things like read only routing.

  I'm not expert at windows clustering [...]

You really don't have to be since you're not looking for HA or DR. That's my opinion, though, and won't translate well everywhere depending on the resources and familiarity with Windows Server.


#Deleted as values when using MS Access to query a db2 database through ODBC When trying to query certain db2 tables I get #Deleted for all values in the table. But for other tables it works fine.
The row count of this query is equal to the row count when using the data studio client so it is actually querying the same table, just the fields don't display correctly.
This data studio result looks like this

and the ms access result looks like this

Update
After checking other tables I have found a similarity. Tables with a lot of records give me the results (but the values are '#Deleted'). The other tables display the results correctly.

The cause of this problem most likely is the primary key of the table in DB2.

Access can not handle some data types as primary keys well.

Notoriously bad for PKs are all floating point numeric types. They may be rounded differently on the server and in Access. If Access uses a floating point value to query a single record from the server, there will be no results because the actual value on the server is different.
This is usually the cause for the #Deleted displayed in table cells.

Good data types for Primary keys are integer types (max int32) and string/varchar types (max length 255 characters).

Dates and fixed-decimal-point data types may be causing problems as well. That is mainly dependent on the actual backend database system and the used ODBC Driver. - I have no experience in that regard with DB2.

Here are some options to solve or work around this issue

If the problem is caused by date or decimal data types in the primary key column, you should check if there are settings in you ODBC-Driver that change its behaviour for those types. If not, you could research if there is another ODBC-Driver available that is more suitable for the use with Access.
Be aware that there will be data types that Access simply cannot support properly.
Lacking experience with DB2 I cannot give more detailed advice on this.
The best way to solve this issue is obviously changing the structure of the base table in the server database. If you only use unproblematic data types in all the primary key columns will make the problem disappear.
If Access is the primary frontend application for the database, I would strongly recommend going this way. Unfortunately there will be many scenarios where this is just not feasible.
If option 1 and 2 fail or cannot be used in your scenario, the workaround would be to create views on the server that wrap the problematic tables. You then link the views to the Access application instead of the base tables.

If you only need read only access to the data, this is a simple solution. When linking views you can (but don’t have to!) select the unique key of the view. If you don’t select any, the view will be read-only, but the data should be displayed properly.

If you do need direct write access to the data it becomes more complicated. You will need to create an artificial unique column in the view, by converting the data in the primary key column of the table to a varchar representation of the keys data. When linking the view to Access you select your artificial column as primary key. Now the data in the table should be displayed properly and should be editable (except for the key column of course).
This approach will incur performance penalties for the calculated PK-column is not indexed itself. You should investigate if the database supports persisting and indexing the artificial key column in the view.
If option 1-3 cannot be used, there is another option. Do not use a linked the table (or view) at all, but create a Pass-Through-Query in Access to query the data from DB2. PT-Queries are read-only though.

If you need to edit data returned by the PT-Query, you need to send custom SQL statements to the database to update the data. So in this case this will take considerably more effort than the other options.


Copy mysql_native_password to new server I did a mysqldump of my entire database mysqldump --defaults-file="/home/ubuntu/.my.conf" -u api --all-databases > full-dump.sql . I setup a new mysql and phpmyadmin and loaded the mysqldump in mysql -u root -p < full-dump.sql
Then my root password stopped working (it's the same on both servers). I was able to reset the password and regain access. I am now comparing authentication_strings on my new server to my old server. I noticed the strings changed on the new server when I reset the passwords. My presumption is that the new and old server use different hashes for the same password and hence aren't compatible with each other.
Is there a way to copy the mysql users over and have the existing passwords work? Or will I always have to manually reset them like I did in this case?

Each version of MySQL features a specific number of columns for the mysql.users table
I mentioned this back on April 12, 2012 : Cannot GRANT privileges as root
You cannot mysqldump the mysql schema from one major version and load it into a different major version.
What you need is to generate the SQL to create the users
I wrote a version for MySQL 5.6 and back on Mar 22, 2015 : Export all MySQL users
I had suggested using pt-show-grants. I also suggested an emulation of it.
Here is the  emulation code from that post
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' > ${GRANT_FILE}

WHAT ABOUT MySQL 5.7+ ???
pt-show-grants will not generate the SQL for the password to be visible. (PCI auditors will be crawling the walls looking for password exposures)
Here is a script to to dump create user commands with their password hashes
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER IF NOT EXISTS ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED WITH mysql_native_password AS ',QUOTE(authentication_string),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' >> ${GRANT_FILE}

If you want this to works for MySQL 5.5 and 5.6, change the word authentication_string to password, and adjust the CREATE USER syntax as needed, perhaps something like this
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED AS ',QUOTE(password),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' >> ${GRANT_FILE}

I did not test these. So, please test them and see if it produces it for you.
WHAT NEXT ???
You then load the MyDatabaseUSers.sql into the new MySQL instance
Please avoid using --all-databases when dumping everything. You shouuld dump everything except the mysql schema (See my old post How do you mysqldump specific table(s)? on how to do that)


SQL Server DB becomes unusable overnight Yesterday, my SQL Server database was fine. Today it is almost unusable - it's slowed down by a factor of between five to twenty, depending on when I hit it.

Some data was added to the server in an overnight load process, but nothing like a volume that should impact a database that much. About 50,000 plain text records (no XML or other frippery).

The server was patched this morning before we rebooted it. However, none of our other database servers which also got patched are behaving differently.

Resource Monitor would seem to suggest that its disk IO that's at fault. It's running at close 100% of capacity on the .mdf file the whole time, even when there's not much actually happening in the database. Access to Templog.ldf is also running quite high.

No-one here is an expert DBA (we're all developers with varying amount of SQL skills) and we're all baffled by what's happened. We've tried running sp_updatestats and moving some of the big indexes to different discs, to no avail.

I think this must have something to do with the patch - it seems too much of a co-incidence. A colleague is convinced that it's the data load having caused the size of the mdf to increase to a point where it's caused execution plans to become inefficient.

What on earth has caused this? How can we find out, and what can we do to fix it?

EDIT:

Using sp_WhoIsActive reveals nothing out of the ordinary. It registers my own use of the sproc and some commands from a colleague who is currently trying to move another index. That's probably holding up the DB right now but it was running just as badly before.

It's the Standard version of SQL Server 2008 R2. SELECT @@VERSION gives:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
  Jul  9 2014 16:04:25
  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

The server has 72GB of RAM and three quad-core 2GHz processors.

The patching was only applied to Windows. There were no changes other than the patch.

Selected settings:

_id     name                        value   minimum     maximum     value_in_use    description                                 is_dynamic  is_advanced
1540    min memory per query (KB)   1024    512         2147483647  1024            minimum memory per query (kBytes)           1           1
1541    query wait (s)              -1      -1          2147483647  -1              maximum time to wait for query memory (s)   1           1
1543    min server memory (MB)      0       0           2147483647  16              Minimum size of server memory (MB)          1           1
1544    max server memory (MB)      65536   16          2147483647  65536           Maximum size of server memory (MB)          1           1

UPDATE: Shifting indexes and tables to different disk partitions seems to be improving things. I'm still confused over how we could have reached a tipping point so suddenly with such drastic results.

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion.
There are 2 possible base reasons for your slow down.

You upgraded your system and rebooted it
You load a bunch of data in it

Let's take a look at part No. 1
It might be that your SQL Server configuration might be broken. This can cause serious problems regarding your Server speed and the disc usage.
Please check in the first instance your basic server settings. Those basic settings are max server memory, affinity I/O mask, affinity mask and max degree of parallelism. You may need to enable the advanced options using show advanced options.
Here is a complete script:
-- enable advanced options
EXEC sp_configure 'show advanced options',1
-- apply configuration
RECONFIGURE
-- how much memory can the sql server allocate?
EXEC sp_configure 'max server memory'
-- which cpu is used to run I/O operations
EXEC sp_configure 'affinity I/O mask'
-- which cpus can run processes?
EXEC sp_configure 'affinity mask'
-- how many threads can work on one query part?
EXEC sp_configure 'max degree of parallelism'

Compare the result with your documented values in your installation steps. Are they still the same?
It may have many reasons why your server behaves so strange. I would normally bet, that your max server memory is just wrong. This will cause your SQL Server permanently swapping data pages. He can't hold everything in his memory. This means he need to read the pages from the disc, update it, write it instantly back. If another update comes along and uses the same page for an update, it can't be read from the memory. Instead the server needs to read it again from the disc. Just swapping...
Another problem can be a to high affinity on disc or processes. If you used a shared Server (SQL Server + other services) with a dedicated disc for SQL Server (which may be a rare case, but it could be), this could be your problem. Your server normally used to have for example 3 cpus for processes and one for I/O. The other 12 cpus are used for other services. In this case your affinity mask is wrong and uses for example an automatic configuration. This means your Server uses all the 16 cores for processes and I/O dynamically. If you have huge processes running, they can put a huge load on the disc, which it may not handle. But in fact, I don't believe that this is your case. It would be faster (even if just a bit) if this would apply, but your case is a slow down.
Another problem may be a too high degree of parallelism. Which means you have too many threads idling on one partial of a query. This could also cause a huge slow down if the parallelism don't work as expected. But this won't describe your high I/O in total.
Now let's take a look at part No. 2 too
You load a bunch of rows into your system. Even if this is a regular job, it could raised a limit in which your query plans escalate. It could be even the case that your insert in combination with SQL Server produces this behavior.
Yοu mentioned that you already tried to migrate your indices to another disc, which seems to help. This can be the happened just to the fact that you split the load on two different discs.
It may be that your indices were fractured, that your plans were fractured or that your statistics are just outdated.
1. lets check the statistics last update
You can do this manually over the interface for each single statistic element. Which would be a pain. Or you can try this code:
SELECT name AS indexname,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes

This will give you a complete information over each index (and heap) and the statistics behind them. Even if you run sp_updatestats it doesn't mean that the statistics were updated. The part when an update is quite tricky, even if you run sp_updatestats or even if auto update statistics is enabled, the statistics won't be updated just in time. Here are some edge points, when an update is needed/generated:

An empty table gets one or more rows
A table with more than 500 rows updates 20% + 500 additional rows and an insert happened afterwards
When 500 rows were changed in a table that holds less than 500 rows

This means, your statistics may be outdated even if you run the update.
You can take a look at the query above. If you find some pretty old statistics in some tables, you may want to run an manual statistic update for this table:
UPDATE STATISTICS dbo.YourBadTable WITH FULLSCAN

After that, you may want to give your server a kick in the ass to throw away all old plans.
DBCC FREEPROCCACHE

If you just want to clean all caches, you might want to run this instead:
DBCC FREESYSTEMCACHE ('ALL')

This will clean up all caches, not just the plan cache. I would normally warn, to use this on a production server in production phase. But as your server don't work currently, you can't harm them too much. It might slow down for some seconds maybe 1-2 minutes as he needs to rebuild all caches, but after that he should run with the correct plans.
Another reason can be totally fragmented indices. This can be checked on the whole server using this statement:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

If the fragmentation is very high, you might need to reorganize (fragmentation < 20%) or totally rebuild (>20%) it. This may take more pressure on your disc and cause trouble. On the other hand, if the indices are that bad, it probably would help in the end more than it harms.
Beside those two reasons, there still may be a third problem
It maybe that your server is configured probably, you haven't changed any code in this time, just added a few rows. All statistics are updated and all caches are rebuild. All your indices are reorganized in the way you need them, but still - nothing works. It just might be that you reached the limit of available memory in your processes. Maybe you need more. You can simply check if there is any process which tries to get more memory than you have.
You can check this using this command:
SELECT * FROM sys.dm_exec_query_memory_grants

It will provide you a list of all sessions which consuming memory. There might be some query which is still waiting to get memory. Those queries can be easily filtered. All sessions where granted_memory_kb IS NULL. These are sessions which requested memory but don't get it. Another thing can be an granted memory which may be to low. You can compare the columns requested_memory_kb with granted_memory_kb. Requested shows how much memory the process needs to run optimal while granted shows up the memory which is enable for the process. If a process needs 2GB to run but only gets 2MB... you might get it on your own. ;-)
Another way is to check the RESSOURCE_SEMAPHORE:
SELECT * FROM sys.dm_exec_query_resource_semaphore

You can take a look at the waiter_count and the grantee_count. If the waiter is above 0, you have pressure on your memory, which may cause swapping and may cause the disc pressure seen by you in the perfmon.


How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL I am using MySQL v.5.7.x.
In a database with 161 tables, one of the them has the following structure:

CREATE TABLE `prefix_coupon` (
`coupon_id` int (11) NOT NULL AUTO_INCREMENT,
`name` varchar (128) NOT NULL,
`code` varchar (10) NOT NULL,
`type` char (1) NOT NULL,
`discount 'decimal (15,4) NOT NULL,
`logged` tinyint (1) NOT NULL,
`shipping` tinyint (1) NOT NULL,
`total` decimal (15,4) NOT NULL,
`date_start` date NOT NULL DEFAULT '0000-00-00',
`date_end` date NOT NULL DEFAULT '0000-00-00',
`uses_total` int (11) NOT NULL,
`uses_customer` varchar (11) NOT NULL,
`status` tinyint (1) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`coupon_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8

See the columns date_start, date_end and their structure date NOT NULL DEFAULT '0000-00-00'?

Is there a way to query database tables for

date NOT NULL DEFAULT '0000-00-00'

and ALTER them

ALTER TABLE prefix_coupon CHANGE date_start date_start DATE NULL DEFAULT NULL, CHANGE date_end date_end DATE NULL DEFAULT NULL;

You can find that information in the INFORMATION_SCHEMA:

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
from information_schema.columns
where column_default = '''0000-00-00 00:00:00''';

Since MySQL lacks the concept of schema, TABLE_SCHEMA is the same as the database

Note, that the default is '0000-00-00 00:00:00', so ' is part of the value. You, therefore, need to quote with an additional ' to get it right.

When you have fixed the default and whatever data there is, I suggest you set 'NO_ZERO_DATE' in sql_mode to prevent this in the future.

'0000-00-00 00:00:00' have some counterintuitive properties, for example:

create table tt (x int, y datetime not null default '0000-00-00 00:00:00');
insert into tt (x) values (1);
select * from tt where y is null and y is not null;
+------+---------------------+
| x    | y                   |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+

You can change the default value like:

alter table tt alter column y set default '1900-01-01 00:00:00';

So you can use the previous query to generate alter statements like:

select 'ALTER ' || TABLE_SCHEMA || '.' || TABLE_NAME
    || ' ALTER COLUMN ' || COLUMN_NAME || ' SET DEFAULT ''1900-01-01 00:00:00'';'
from information_schema.columns
where column_default = '''0000-00-00 00:00:00''';

Note that you need 'PIPES_AS_CONCAT' in sql_mode for || to work


MySQL: list users which have been granted EXECUTE permission on a given procedure I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure.

I would like to use this information to be able to save GRANTs when changing (DROP+CREATE) the definition of some procedure in order to restore them after.

How can I query the INFORMATION_SCHEMA or MYSQL schemas to retrieve that information?

Update I'm interested in a solution working on MySQL 5.6+.

For granular security, users of our datase are not granted an EXECUTE permission on the whole schema but on specific stored procedures. Answers on schema-level permissions are irrelevant.

This script executes and shows all privileges associated for account.

SHOW GRANTS FOR 'antonio'@'antonio@gmail.com';


To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to regain it.
I've heard lots of times that using DBCC SHRINKDATABASE reduces the performance of your DBs -As far as I know, because "...A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree" [MSDN]
So I'm planning to use DBCC SHRINKDATABASE and then rebuild our indexes.
There is another performance reason to not use DBCC SHRINKDATABASE to regain disk space?

From Tibor Karaszi's Blog:

So what is the problem? Why shouldn't I shrink database files on a regular basis?
Have a look at below list and then you can determine for yourself whether or not you want to shrink database files regularly:

Each page moved will be logged to the transaction log.
Say you have a database with 50GB used space (data and indexes pages), and the shrink will shuffle 40 GB towards the beginning of the file. The log file will for this shrink operation need 40GB, likely auto-grow to that size (if you don't have 40GB free space in the log file already). The following log backup wll be 40GB in size, plus the "regular" log records. This doesn't seem to happen if the database is in simple recovery mode, possibly beacuse CHECKPOINT will prune the log regurarly during the shrink.
  (Applies to shrinking of data files.)
After the shrink, as users add rows etc in the database, the file has to grow again.
  Growing a database file is an expensive operation, it takes time and it also hurts performance (lots of resource usage). Also, some modifications will be blocked until the grow operation has finished.
  (Applies to shrinking of both data and log files.)
SQL Server 2005 and later:
As of SQL Server 2005, we have "instant file initialization" which means that database files can be created and also grow very fast since Windows doesn't "zero out" the data in the database file. Instant file initialization is only available for data files, not log files. Also, instance file initialization requires that the service account for the SQL Server service has the SE_MANAGE_VOLUME_NAME windows privilige, which can be assigned using the Perform Volume Maintenance Tasks security policy. This is by default only granted to Administrators.
There are situations where autogrow doesn't "catch up" with the space usage requirements.
This will result in an error message from SQL Server when the modification is performed, returned to the client application: error 1105 if data is full and 9002 if log is full.
  (Applies to shrinking of both data and log files.)
Moving datapages around will fragment your database.
  Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.
What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB.
  (Applies to shrinking of data files.)
Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance.
  (Applies to shrinking of both data and log files.)
Repeatedly srinking and subsequent growing of transaction log files will typically result in many virtual log files which can cause long startup time for the database. This can manifest itself as long startup time for a database, long restore time, delays for transaction replication etc. Check out this blog post for more information.


UFT to SQL Server conecction which is located at different domain I need to connect to a SQL Server through UFT (Unified Functional Testing) but the problem is SQL Server is present at different domain whereas QTP (HP QuickTest Professional) is present in a different domain.

I am able to connect to SQL Server manually from my domain using

runas /netonly /user:mydomain\TestUser
    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe

Where mydomain is the domain in which the SQL Server is present. This is working fine when I connect it manually, but it failed to establish a connection when I tried to connect it through QTP.

Connection string that I'm using:

provider=sqloledb.1;server=10.130.15.11,1421;uid=testuser;password=mypassword"

The password in the connection string is different from the password that I provide after executing above runas command. I also tried with passing both the domains in UID in connection string in QTP but still not able to connect.

The uid & password attributes are used for SQL Server Authentication. You cannot use those connection string values with Windows Authentication. I suspect you are getting login failures with error 18456, state of either 2 or 6. The state portion of the failure message will give you the detailed reason for failure. This post by Aaron Bertand gives a detailed rundown of most/all possible failure reasons.

If you want to use Windows Authentication to connect to SQL Server from a different domain, you would need to have a trust established between the two domains. With the trust established, you would replace the uid & password attributes with Trusted_Connection=True;

If your second domain is not trusted, and you cannot establish a trust, then you would need to use SQL Server Authentication instead. You would need to create the SQL Server login, and then your existing connection string format would work.


general memory requirements for sql server 2008 r2 I'm not experienced with DBA work, but I'm trying to make a case for requesting additional resources for our sql server and was hoping I could get some smart folks to provide a rough rough estimate of what we should be running.  I'm suspecting that the allocation of resources IT has given to our production sql server is low.

Hardware & Software:

Database: sql server 2008 r2 enterprise database

Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on VMware.

Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz  2.26 GHz (2 processors)

Installed memory: 4GB

Hard drive for Database files: 300GB

Hard drive for backups: 150GB

Hard drive for logs: 100GB

Application:

We have 3 main databases that add up to about 170GB in data, a Reporting Services database (SSRS) on the same server that houses maybe 10 different reports (comprising average of 700k records each) that get generated daily.  Our user base is about 20 simultaneous users, maybe 5 of those could be considered "resource intensive" with generating data-crunching large reports.  Majority of users interact with database through asp.net website and Report server website.  Additionally, our developers use SSIS in BIDS extensively by remoting directly onto the server (2 remote connections max).  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Current Problems:

We have chronic sever server timeouts and the response time to the website is pretty bad.  I suspect the amount of memory we have (4GB) is probably a large bottleneck.  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.  While we aren't sql pros or (as I'm sure you can tell by our setup) db admin pros, I want to make sure I'm not expending all my time trying to squeeze out little potential performance if the hardware is the bottleneck.

Thanks all for the tl;dr avoidance!

... was hoping I could get ... a rough rough estimate of what we should be running.

Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate.

  Database: sql server 2008 r2 enterprise database

  Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on
  VMware.

  Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz 2.26 GHz (2
  processors)

  Installed memory: 4GB

Two processors (I'm assuming this is exposed in the VM as 2 cores) may or may not be under-provisioned. The cores assigned to a VM aren't necessarily mapped directly to physical cores (or even allowed to use 100% of a single core when it's needed!), so you may find this is a more flexible resource than memory. Without any more information about your workload or hardware/virtualization configuration, I would say increasing this to 4 would be nice-to-have.

Memory allocation. Oh boy. This is grossly under-provisioned for the workload. Windows itself needs a bare minimum of 2-3 GB to stay happy, and each of the 2 users running BIDS on the box will require at least 500 MB each. And with that, the box is maxed out already, and I didn't even start figuring out how much the database is going to need.

  Majority of users interact with database through asp.net website and Report server website.

You didn't say, but if these are running on the same box, memory requirements for them need to be taken into account as well.

  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Assuming this runs at night when there are no live users on the system, I don't see this as a problem unless it's taking too long to run. This part of things is the least of your worries; live users are more important.

  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.

As I demonstrated above, the current amount of memory that's been provisioned is completely inadequate. At the same time, though, at the other end of the spectrum, it's exceedingly unlikely you'll be able to get enough memory provisioned to be able to keep the entire database in memory at once.

Even though you got a blanket response like that (which, by the way, probably had more to do with how persuasive your justification for additional resources was, and not the actual resource usage itself), it's highly likely the efficiency of the database could be improved. Yet there's no amount of tuning alone that can fix the issues you're experiencing now; the suggestion of that is a complete non-starter to me.

I would take the overall approach that the amount of memory currently provisioned is below the minimum required (which should be corrected ASAP), and additional resources may be required to improve the user experience to a usable level while improvements are made to increase the efficiency of the systems.

Here are a few thoughts (in order of attack):

You will win if you can prove how much performance improves every time you get more resources provisioned. Keep track of performance metrics using Performance Monitor logging (note: the logging part is very important), including website response times if you can. Start doing this now, before doing anything else. When you do finally get to the minimum amount of memory (you aren't going to get 32 GB right away), suddenly you now have evidence that the added memory improved things... which means adding even more would probably help, too! If you don't collect a baseline on the current configuration, you're going to miss the boat when things are bumped up to the minimum recommended level.
Analyze your server's wait statistics. This will tell you what the biggest bottleneck in the system is. You'll probably have PAGEIOLATCH_XX as the most common/highest wait time, which indicates too much I/O is being done to fetch pages from disk. This can be alleviated by adding memory, so the physical I/O's become less frequent as the needed data is already in memory. While this analysis is pretty much a foregone conclusion, the fact you've gathered these stats at all gives you more ammo when justifying the need for resources.
As I mentioned above, the bare minimum requirement for memory is not being met. Collect the set of recommended hardware requirements for all the software you're running, and maybe also grab screenshots of Task Manager. This alone should be enough to justify at least 4-8 GB more, on the spot. If they still refuse, try to convince them to allow you to try it out for a week, and give it back after that (you're collecting performance stats, so you won't need to give it back because mid-week you'll be able to prove how much it's improved the situation). If they still refuse, you're being set up to fail; URLT.
If you can offload some of the workload (in particular, avoid remoting in if at all possible), this will increase the amount of memory available for the database, which is more critical.
You won't be able to fit the entire database in memory at once, which means you need to set SQL Server's max memory setting very carefully to prevent memory over-commit, which kills performance like nothing else. Over-commit is actually even worse than simply not being able to fit all the data in memory. It's highly likely you're in this scenario right now simply because there's just no memory available at all, and it's probable that the max memory setting is set to the default (unlimited).
Since you're running SQL Server Enterprise Edition, and memory is at a premium, I would strongly consider implementing data compression. This will trade off an increase in CPU usage for space-savings of memory (and hence reduced disk accesses, which are comparatively very slow).
Tune the database. It's likely the structures and queries could use improvements as far as indexing and access patterns go. Also, if a lot of data is being frequently scanned and aggregated, creating indexed views, summary tables, or precomputed reports may be very helpful.
This might be a longshot because it probably means more hardware provisioning, but implement a caching solution. The fastest query is the one you never make.

Those are just a few ideas. The bottom line is that tuning alone will not solve the problems here, nor will hardware alone, even though the latter probably will alleviate the majority of the immediate issues. That's really how it goes: throw hardware at the problem in the short-term to put out the fire, and throw tuning at the problem in the long-term to fix the root cause as best you can.


Execution plan - Hash Match SELECT        dbo.Contracts.ContractID AS [Service Unit ID], dbo.Jobs.JobName, dbo.Customers.CustomerName, dbo.Contracts.ContractAutoID, dbo.Contracts.ContractType, dbo.Contracts.ContractStatus AS [Unit Status],
                         dbo.Contracts.Area
FROM            dbo.Customers WITH (READPAST) INNER JOIN
                         dbo.Jobs WITH (READPAST) ON dbo.Customers.CustomerID = dbo.Jobs.CustomerID INNER JOIN
                         dbo.Contracts WITH (READPAST) ON dbo.Jobs.JobID = dbo.Contracts.JobID
WHERE        (dbo.Contracts.ContractType = 'service')

I understand Hash Match indicates the indexing is not appropriate, which is why it takes up so much space. I am wondering what i need to different with my indexing to get these Hash Matches to become nested loops. Below is a picture of the Execution Plans.

  Customer Index

  Index Key Column: CustomerID

  Included Columns: City, CustomerStatus, CustomerName

  Contracts Index

  Index Key Column: ContractType

  Included Columns: ContractAutoID, JobID, Area, ContractStatus,
  ContractID

  Jobs Index

  Index Key Column: JobID

  Included Columns: CustomerID, JobName, Area

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or  use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


How to set order for constraints to be checked in Postgres I have a table with 2 constrains like below.

ALTER TABLE entity_instance ADD CONSTRAINT instance_name_uniq UNIQUE (tenant_id, name)

ALTER TABLE entity_instance ADD CONSTRAINT instance_source_uniq UNIQUE (tenant_id,source_key)

How can I make it mandatory that instance_source_uniq is checked first?

It's hard to imagine a use case where it matters. Maybe a case where one constraint is much more likely to fail and/or the other one(s) are expensive (huge tables?) and you'd rather check the one first to optimize performance? Be that as it may, to answer the question asked:
CHECK constraints are applied alphabetically. The manual:

When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints.

But there is no such rule for UNIQUE constraints.
Slow and sure
A slow and sure way: define competing constraints as DEFERRABLE. The manual:

A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction ...

But if the purpose of this is to optimize performance, this may defy the purpose. The manual warns:

Be aware that this can be significantly slower than immediate uniqueness checking.

There are also a couple of side effects. Like: FK constraints cannot currently point columns with only a deferrable UNIQUE constraint. Read the manual carefully. Related:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Quick and dirty
Some quick tests indicate that UNIQUE constraints are checked in the order of their creation. Could be bound to the oid in pg_constraint or the oid of the underlying unique index ... But neither can I give any guarantees that this is true, nor does the Postgres project guarantee anything in that direction. I found nothing in the manual or anywhere. Looks like an implementation detail that's not reliable. But for the sole purpose of performance optimization "not reliable" may still be good enough.
To take advantage of this observation create instance_source_uniq before you create instance_name_uniq. (DROP and recreate other UNIQUE constraints that were created earlier.) Check success with a forged INSERT that violates all competing UNIQUE constraints at once. You'll see an error message for the one that's checked first.
To further keep that order intact across dump / restore cycles, rename instance_source_uniq to something like instance_1st_source_uniq, which sorts before all other competing constraint names. pg_dump dumps objects in alphabetical order, so this should result in your favored constraint being created first on restore.

Postgres pg_dump dumps database in a different order every time (Peter Eisentraut is a core developer.)

Again, no guarantees. Whenever instance_1st_source_uniq is dropped and recreated, you may lose the effect. Or if Postgres decides to do things differently. Or if there are exception to the observed behavior.


Production database substantially bigger than Dev I have a Production database and a Development database on two different servers. Both databases are being fed from the same data warehouse on a staging server. There are a series of automated SQL jobs that run and populate tables in a database. The jobs were copied from Dev into Prod so they should also be the same. However, Prod is growing much faster than Dev.

There are approximately 1 billion rows of data in the database. Total data files in Prod are about 123GB larger. Total index files are about 31GB larger. I'm new to this, but I would expect the two databases to be fairly similar in size. If anything I was expect Dev to have some extra 'junk' and potentially be the bigger database.

Any ideas how to find the source of this size difference? I can increase the disk space on Prod if needed, but indicates to me that there may be an issue that needs to be addressed. I'd like to recoup the 153GB if possible.

I'm admittedly a rookie when it comes to this, but I checked the Index folder in each environment and they both appear to have one Index. I checked properties and they look the same as well. Would more Index also cause an increase in size in the data files?

I'm running the IndexOptimize script from Ola Hallengren in both Prod and Dev and hoping that is adequately handling any significant fragmenting. I haven't actually migrated any data from Dev to Prod. We have a staging server that hosts the data. A set of SSIS and stored procedures moves the data from staging to databases on Dev. More SSIS jobs and stores procedures populated the tables in Dev. The SSIS and stored procedures are promoted from Dev to Prod and they run on Prod independently. The Prod jobs access the same staging server as Dev.

Compression can cause you to see different table and index sizes for the same data across two tables.

You asked in a comment if there's any reason not to apply compression. The general trade-off for page compression is that your tables take up less space and you can fit more data into memory at a cost of CPU overhead. As a very general rule, if your server has CPU to spare you might as well test your workload before and after applying compression to see what happens. There are even some workloads which will get more efficient from a CPU perspective after applying compression. In short, "it depends".

Another reason not to use compression is if you aren't licensed for it. Your development server could be using developer edition which allows data compression but your production server could be using standard edition, which doesn't allow data compression until SQL Server 2016 SP1.


Is it possible to run Oracle 12c and AD-Server on Windows Server 2012 R2? I have tried to install Oracle 12c Enterprise Edition on a computer also running as AD Server. Including the October 2015 patch. Operation system is Windows Server 2012 R2

A managed service account is used as user when installing the server. (and 32 bit client)

Everything looks perfect, until I reboot the computer. Then the computer starts to act as a zombie.
Usually, the oradim command does not respond at all, it just hangs.

This configuration, running both Oracle and AD server on the same computer is to save virtual machines in our vCloud test configuration.

When Oracle and AD is not installed on the same computer, it works like a charm.

Does anyone have a clue what's wrong, or if it is a known limitation?

Once you run dcpromo to create an AD domain controller, this server differs significant from a common Windows Server as you are used to work with. In your case the most critical point is that a domain controller does not know local users and groups because it does not have a local context. But what Oracle requires, when you create a database via the Oracle Installer or oradim on Windows, is to create several local groups like ORA_DBA, ORA_OPER, etc. and also adding users to these groups. Otherwise you can not do stuff like e.g. "sqlplus / as sysdba". Knowing about what is going on in the background during Oracle database installation makes it easy to understand why you can not run Oracle on an AD domain controller because the installer can not create the local groups resulting from the fact that an AD server does not have a local context. Please find a list of the required local groups here under section 4.1.4: https://docs.oracle.com/database/121/NTDBI/user_grps.htm#NTDBI260

In case your oradim command created and started a database successfully, you resulted in a database using the LocalSystem Account (NT AUTHORITY\System) to start the database which Oracle does not recommend since Oracle 12c. It was the default until Oracle 11g but Oracle introduced the new Oracle Homer User for security reasons as requested from Oracle customers. You can still use the LocalSystem Account and in your case this has been done. In addition you have no local Oracle groups as listed under section 4.1.4 and in case you request support I have serious doubt that Oracle Support will accept your installation.

The above topics are the basic reasons why all customers who asked me in the past how to install Oracle on a domain controller finally have decided not to do so. I must add that I can not predict what kind of errors and failures might result from an installation which actually misses the required local Oracle groups.

Hope that helps.


Postgresql: compare record against sibling, but limit search only to active records I'm trying to find a way to compare records against it's sibling record while limiting the comparison only to the most recent entry for each set.

I know it does not make to much sense so I'll go with an example.

Table 1: A list of category price-sheet info.

WITH category_price_sheets(category_id, reg_price_sheet_num, bulk_price_sheet_num) AS (
      VALUES
      (1, 1600, 1601),
      (2, 1602, 1603)
),

Table 2: Item cost entries for both regular cost and bulk cost.

prices(id, price_sheet_num, item_id, cost, created_at) AS (
  VALUES
  (1, 1600, 88, 2.00, '2016-05-06 00:00:00'::timestamp),
  (2, 1601, 88, 2.10, '2016-05-06 00:00:00'),
  (3, 1600, 88, 2.05, '2016-05-07 00:00:00'),
  (4, 1600, 88, 2.00, '2016-05-08 00:00:00'),
  (5, 1601, 88, 1.88, '2016-05-08 00:00:00'),
  (6, 1600, 88, 2.05, '2016-05-09 00:00:00'),

  (7, 1602, 88, 2.00, '2016-05-06 00:00:00'),
  (8, 1603, 88, 2.10, '2016-05-06 00:00:00'),
  (9, 1602, 88, 2.05, '2016-05-07 00:00:00'),
  (10, 1602, 88, 2.00, '2016-05-08 00:00:00'),
  (11, 1603, 88, 2.07, '2016-05-08 00:00:00'),
  (12, 1602, 88, 2.05, '2016-05-09 00:00:00')
),

How can I identify bulk pricing that is more expensive than it's regular counterpart, but only in case they're both the latest entry for same item.

So in the example above the bulk cost for item_id 88 in price-sheet 1601 is indeed a better price than it's counterpart in price-sheet 1600 but the same is not true when comparing 1603 against 1602.

I tried the following on a small sample set, and the results were correct. But when I tried it on the whole table, I pulled the plug after two hours with no results. (I'm dealing with more than 50,000,000 records)

bulk AS (
  SELECT v.* FROM (SELECT price.*, sheet.reg_price_sheet_num, sheet.bulk_price_sheet_num,
      lead(price.id) OVER (PARTITION BY price_sheet_num, item_id) IS NULL AS latesetrecord
    FROM prices price
    JOIN category_price_sheets sheet ON sheet.bulk_price_sheet_num = price.price_sheet_num
  ) AS v
WHERE v.latesetrecord
)

SELECT v.* FROM (SELECT price.*, sheet.reg_price_sheet_num, sheet.bulk_price_sheet_num,
      lead(price.id) OVER (PARTITION BY price_sheet_num, item_id) IS NULL AS latesetrecord
    FROM prices price
    JOIN category_price_sheets sheet ON sheet.reg_price_sheet_num = price.price_sheet_num
      WHERE price.cost < (
        SELECT cost FROM bulk blk WHERE blk.price_sheet_num = sheet.bulk_price_sheet_num
          AND blk.item_id = price.item_id
      )
  ) AS v
WHERE v.latesetrecord

I think this can be done with some WINDOW functions but I couldn't get it to go.

Thank you all!

Your best (or simplest) choice may be using DISTINCT ON:

WITH latest_prices AS (
  SELECT DISTINCT ON (price_sheet_num, item_id) *
  FROM prices
  ORDER BY price_sheet_num, item_id, created_at DESC
  )
SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost > lpr.cost;

Ensure first there's an index for prices ON (price_sheet_num, item_id, created_at), or better still, prices ON (price_sheet_num, item_id, created_at DESC);

If this doesn't preform well enough, you may benefit from creating a temporary table:

CREATE TEMP TABLE latest_prices (LIKE prices) ON COMMIT DROP;
INSERT INTO latest_prices
SELECT DISTINCT ON (price_sheet_num, item_id) *
FROM prices
ORDER BY price_sheet_num, item_id, created_at DESC;
CREATE UNIQUE INDEX ON latest_prices (price_sheet_num, item_id);

SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost > lpr.cost;


SQL Server Integration Services (SSIS) Package Failing with no error I have some SSIS packages that are executed as steps in a Server Agent Job. The Steps often fail with no error and I have had to add retries. Now the jobs complete but I get the warning exclamation mark telling me there has been a failure and its these steps that nearly always require a retry.

When looking at the failed step in the Job History I can see the package execution log and then it just randomly cuts off and the step beings a retry. There is no consistency with where it stops and I know there isn't a problem with the package as it eventually succeeds.

Does this sound like a network related issue? Is there anyway to find out what is causing these failures to happen?

Working backwards from the job, have you been able to run the package straight from the server? In older versions of SQL Server it might be in the Integration Services server, that you can connect to through Management Studio, or in newer versions of SQL Server it could be in the Integration Services Catalog on the database server. If you can run the package directly through there, then I would take a look the job credentials, or any configuration settings that are being set in the job. If the package fails when running it directly, then it may be the package itself that's the problem, which then I would go back to running and troubleshooting it from Visual Studio\BIDS to see where the problem is occurring.

On a more unusual path, if the package is doing a lot over an unsteady network, then you might be getting network related problems, or timeouts that aren't being reported as such to SQL. This might need you to get creative in staging data or breaking up the tasks into smaller packages and then calling those packages as additional steps.


UPDATE statement behavior I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at the same time:
 |    Session 1 - Statement 1 | Session 2 - Statement 2
 |   -------------------------+-------------------------
 |    update dbo.Table1       |  update dbo.Table1
 |    set Value = 10          |  set Value = 20
 |    where ID = 1            |  where ID = 1
 ▼    and Value = 0;          |  and Value = 0;
(t)

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row. It then asks for an exclusive lock for updating the column values. So, it seems that the result would the Value being set to 20.
Am I missing something or is my understanding correct?
The isolation level at the connection is set to READ UNCOMMITTED.

I have a question regarding inner workings of UPDATE statements with regards to SQL Server.

The statement describes a desired logical change to the database. What happens physically at runtime depends on the execution plan, the state of the database, and what other concurrently running statements/queries are doing.
I mention this because your question relates very much to implementation details.

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row.

This is generally not what happens.
Under most isolation levels (including READ UNCOMMITTED as specified in the question) SQL Server will take update (U) locks when locating rows to update. This is an implementation detail added to offer some protection against a common form of conversion deadlock (it is not always sufficient, but it does help).
Note: SQL Server may choose any convenient access method to locate rows to update. For example, it might choose to use a nonclustered index. The update will still ultimately update the base table and any applicable secondary indexes, but the order of operations may differ.
This means U locks do not necessarily serialize as one might expect. For example, one update statement in the question might choose to locate records via a secondary index, in which case an entry in that index has a U lock applied. There is nothing to stop the second update statement choosing a plan that locates records using a scan of the base table (heap or clustered).
In that case, the second update could acquire a U lock on a row in the base table while the first update holds a U lock on a row in a secondary index that links to the same base table row.
In yet another scenario, SQL Server might be able to choose a single operator update plan (for example if a clustered index exists on (ID, Value)). In this case, an exclusive X lock is taken on the clustered index immediately - there is no prior U lock.

It then asks for an exclusive lock for updating the column values.

Data-changing operations always take an X lock before the change is made. This lock is held to the end of the transaction. It may or may not involve converting a currently-held U lock to X.
For example, if the row to update was located using the base table, a U will be held and converted to X at the base table update operator. If the row was located using a secondary index, U is held on that index and a new X lock is taken on the base table. Both U and X are held at the same time, on different resources.

So, it seems that the result would the Value being set to 20.

There are a number of possible outcomes depending on timing and the execution plans chosen by each of the two update statements. Choosing just a few of the more interesting ones:
Scenario 1:

Session 1 obtains a U lock on the base table row while reading.
Session 2 blocks waiting to acquire U on the same row in the base table.
Session 1 sets Value to 10 and commits.
Session 2 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 10.
Scenario 2:

Session 2 obtains a U lock on the base table row while reading.
Session 1 blocks waiting to acquire U on the same row in the base table.
Session 2 sets Value to 20 and commits.
Session 1 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 20.
Scenario 3:

Session 1 obtains a U lock on a secondary index (locating a row to update).
Session 2 obtains a U lock on the base table (locating a row to update).
Session 1 needs to acquire X on the base table row to perform the update, but is blocked by the U lock held by session 2.
Session 2 converts its U lock on the base table to X and sets Value to 20.
Session 2 now needs to maintain the secondary index (assuming that index contains the Value column).
Session 2 needs to acquire X on the secondary index row to update it, but is blocked by the U lock held by session 1.
Deadlock. One of the sessions rolls back with an error; the other completes successfully.

Outcome: Indeterminate. Value may end up as either 10 or 20.

These are just some of the possibilities. It is generally a mistake (in my opinion) to try to predict detailed engine locking behaviours. Focus instead on writing a correct logical specification of the change you want to make, and use the isolation level that provides the guarantees you need.
Something about the way the question is written makes me think you might really be asking about "lost updates". If that is case, please review existing Q & A like the following before asking a more specific new question:

Lost Update Understanding
Are lost updates completely handled in SQL Server or is manual intervention still required?
Why are U locks required when using Read Committed Snapshot Isolation
Where is Read Committed insufficient?


Insert performance increases under load: Why? I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+. This is SQL Server 2008 R2, running on Windows Server 2008.

Each insert consists of inserting to a number of tables under the same transaction. Some inserts are batched by NHibernate, but some cannot be, but they are all under the same transaction nonetheless.

When I perform inserts for say 500 times by repeatedly calling a piece of code that performs the insert, I get an average of ~360 ms.

The weird bit is, when I run the test code simultaneously using 4 processes (the same exe run from 4 different command prompts under windows server 2008), the insertion performance per call gets much better. I see bursts that go as fast as 90 ms (almost X4 faster). I'm measuring the insertion time from the code.

Since the 4 processes know nothing about each other, I'm assuming that this has something to do with SQL Server, but I have absolutely no clue why. I'd like to know why this is happening and if there is any configuration that would allow me to get the same performance when the inserts are not that frequent.

Suggestions regarding SQL Server monitoring methods to understand what is going on at the db level are equally welcome.

One possible reason is that four concurrent processes generate a more favourable pattern of log flushes - typically meaning that each log flush writes more data than is the case with a single executing process.

To determine if transaction log throughput/flush size is a factor, monitor:

sys.dm_os_wait_stats for WRITELOG and LOGBUFFER waits
sys.dm_io_pending_io_requests for IO performance
Performance Monitor counters (or sys.dm_os_performance_counters) for:

Log Bytes Flushed/sec
Log Flushes/sec
Log Flush Wait Time

Look for internal limits being reached. In SQL Server 2008 R2, there can be a maximum of 32 outstanding (asynchronous) log flush I/Os per database on 64-bit versions (only 8 on 32-bit). There is also a total size limit on the outstanding IOs of 3840KB.

More information and further reading:

Transaction Log Monitoring
Trimming Transaction Log Fat
Diagnosing Transaction Log Performance Issues and Limits of the Log Manager
Optimizing Transaction Log Throughout


My mysql database crashed, how to recover I have a mysqldump of all databases, including mysql.

I'm using mysql 5.6 on Windows 2008 R2. How can I restore my backup ?

The problem is that I cannot start the server when the database mysql is missing.

Edit:
This is what happened exactly:

I got this error while running some query:

  [ERROR] Out of memory; check if mysqld or some other process uses all
  available memory; if not, you may have to use 'ulimit' to allow mysqld
  to use more memory or you can add more swap space

I tried to restart mysqld, but it did not restart because of this error in the log:

  InnoDB: Database page corruption on disk or a failed
  InnoDB: file read of page 2568.
  InnoDB: You may have to recover from a backup.

I included this line in my.ini according to the doc http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html: innodb_force_recovery = 1
The server started
I created a dump of each database using mysqldump
According to http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/, I ran a database check with mysqlcheck --all-databases and found the offending table.
I tried to delete that table, but it was linked with primary keys (mysql -u ... -p... -e "drop database ..."). I deleted the depending tables, but with no luck, mysqld still refused to start with innodb_force_recovery = 0

I decided to start off from scratch to be sure to get a clean database back.

I deleted everything in the data directory (keeping a backup copy).
I initialized the innodb datafiles using mysqld --initialize, according to https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
I copied the two system database files from the backup (directories mysql and performance_schema)
Now the server started again with these warnings in the log:

  [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
  [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.

the same for mysql/slave_relay_log_info and mysql/slave_worker_info
According to https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html, I deleted the three files mysql/slave_master_info.frm, mysql/slave_relay_log_info.frm and mysql/slave_worker_info.frm
I imported the three tables from the dump of the mysql database.

At this point I got a working mysql server with a clean database. The olny thing that remained was to import the dumps that I made previously, using mysql -u root -p... mydb < backup-mysql\mydb.sql

And thank God, after one hour of interruption, everything was working again.


Revoke grant option from table owner I have a role engineer1 | No inheritance, Cannot login | member of {priv_app}. The priv_app user ownes the tables in the schema. Users are members of engineer1 by default which only grants them select access. However, there are reasons the user may need to alter tables so they need to elevate their privileges to the priv_app role using set role priv_app. However this gives them the rights of the table's owner. Which is acceptable except that a user can then grant another user DML privileges on any tables.

Is there a away to prevent the table owner from granting privileges? If not what would be the correct way to architect security groups so that users can alter tables but not grant privileges.

Answered by @Ziggy Crueltyfree Zeitgeister 's comment.

  A table owner can do with the tables as they please: drop, grant, revoke, etc. What you could do for altering tables is doing so via executing any required ALTER TABLE command from within a function with SECURITY DEFINER.


MongoDB: Can I have a delayed secondary that doesn't vote? I have a replica set setup with 1 Primary, 1 Secondary and 1 Arbiter; each on a different data center.

I want to add a delayed secondary to one of the existing data centers, but I don't want it to participate in any election decisions.

I still want to have a total of 3 votes, so if the data center that hosts the delayed secondary (plus another member) goes down, my replica set would still elect a primary. Is this possible?

A configuration with three voting nodes in different data centres plus an additional non-voting delayed secondary is definitely supported. This is actually a reasonable configuration in terms of satisying core availability requirements using the distributed voting nodes with additional hidden/non-voting nodes for special use cases. A delayed secondary should be always configured as hidden and priority 0, and may have 0 or 1 vote.

However, there is one important caveat with your deployment: you have three data-bearing nodes but one of those is delayed. This will be problematic if you want to use any write concern higher than the usual default (w:1 aka acknowledgement by the primary). In the event one of your non-delayed secondaries is unavailable, a higher write concern (eg w:2 or w:majority) could still be satisfied but the writes would have to wait for acknowledgement from a second data-bearing node which would have to be the delayed secondary.

A more robust configuration would be to replace the arbiter with a data-bearing secondary so that unavailability of any of the three voting nodes would still allow for acknowledgement of majority writes.


Apply in a CSV file to multiple databases Recently, I have been working with a client whom has asked us to provide their dataset for use in a mobile application we are making for them. The requirements for this project include the ability to update the local dataset on the mobile devices with any changes that our client delivers to us. The local databases are to be stored in sqlite according to the mobile device standard.

The Problems:

The Dataset: Our clients can only deliver to us an entire dump of their database in CSV format. We must be able to apply only the changes between the new CSV and our existing databases without causing duplicate entries or stale data.
The Delivery: Only the changes to the dataset may be sent to the mobile databases. Resending of the entire database is too expensive.

Approach:

Currently, the solution we are looking into involves using one of our backend MySQL servers to maintain a backup of database stored on the mobile sqlite databases. When a new dataset arrives, we were planning to make a new database with the new data, and then to be able to use diff and patch linux commands to update the server side data. However, with this approach, knowing what or how to update in the mobile phone's local databases gives us some issues. I am currently unaware of any way to use the patching or the diff file from MySQL in either the Android or iOS environments.

Ideal Approach:

Preferably, when we receive the entirely new dataset, we would like to be able to detect just the changes in the new CSV against the current state of the database. Then, with this list of changes, we would generate INSERT/UPDATE/DELETE statements that would be able to apply each of the changes to both the MySQL and sqlite databases. We would store a list of each of the INSERT/UPDATE/DELETE statements along with a the date updated so that upon request each of the phones can be updated accordingly to the newest data.

However, I am also currently unaware of a way to detect changes from a new CSV to an existing database which makes constructing INSERT/UPDATE/DELETE statements difficult.

The Questions:

What is the best way to get the changes between the CSV file and our current database implementation in a portable and meaningful format?
What is the preferred method for cascading such changes from our server side database to the phone's local databases?

I am currently seeking any advice for either improvements to our design or proposed implementation. I feel like I can't be the first person to try and reflect changes to a CSV in a database or synchronize changes in a server side database to a local offline database.

Thank you everyone in advance for your help. I look forward to hearing alternative approaches to this problem.

Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one

Something along the lines:

update real_table
  set ...
where exists (select 1 from staging_table where ...)

insert into ...
select
from stage_table
  left join real_table
where real_table.some_col is NULL

delete real_table
where not exists (select 1 from staging_table)

Then delete the data from the staging table.

Probably the fastest way to do it. The only drawback is that you have the data for one table twice in the database (until it is deleted from the staging table).


Comparing Query Accomplishment Speed By Using JOIN or not I have been searching about this, and still didn't get satisfying answer. My problem is, I have 2 tables, for example table A and table B.

Table A consists of: id (pk), name, address
Table B consists of: b_id (pk), id(fk - from table A), tools

The relationship between these 2 tables is one to many. So each row of A could have more than one items in table B.

Commonly, I always use this way:

I select one row from table A

SELECT * FROM A WHERE id = 1

Then I select more data from table B

SELECT * FROM B WHERE b.id = 1

Is this way effective? Is that better than using JOIN, like

SELECT A.id, A.name, A.address, B.b_id, B.tools
FROM a
  LEFT JOIN b ON a.id = b.id
WHERE a.id = 1

should I better use JOIN instead?

I would recommend to use JOIN for the following reasons:

It is optimised especially if Index properly designed and also if query get complicated later on.
Easy to read & understand, tidier code and one result-set
It is way the professionals do
select the only columns which are required not everything which could increase network traffic


ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube I have Account and Customer dimensions in the cube that are connected to the same measure groups (there are about 15 - 20 measure groups in the cube).

When I run XMLA command to process update these two dimensions, like this:

<Batch>
    <Parallel>
        <Process>
            <Object>
                <DatabaseID>My Database</DatabaseID>
                <DimensionID>Dim Customer</DimensionID>
            </Object>
            <Type>ProcessUpdate</Type>
            <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
        </Process>
    </Parallel>
</Batch>

in the case of Account dimension it finishes in a couple of minutes because it doesn't trigger processing of all partitions of all measure groups. But in the case of Customer dimension it triggers processing of all partitions of all measure groups, so process update of this dimension lasts longer then full processing of entire cube.

I am not sure what can be the reason from which the dimension will trigger all this processing in the case of one dimension and not in the case of the other. For both dimensions Process affected objects is set to Do not process. Where should I look, what to check, can I somehow prevent this reprocessing happen?

Thanks!

I'm somewhat surprised that your partitions are processed without you specifying "process affected objects" anywhere.
Maybe you are confusing the output "partition processing operations" with the actual processing of the partitions.

The logic to process partitions when updating dimensions is this.

if you process update a dimension and only new members were added, the partition is not affected
if you process update a dimension and changes were made (deletes, changes in dimension relations such as a customer changed customer group/zip code whatever) some of the aggregation data and bitmap indexes on the partitions are dropped.

The drop of the aggregation data and bitmap index shows up in profiler as "partition processing operations".

This however will not render the partition inaccessible, so the measures are still available for querying, albeit slower.

If ProcessAffectedObjects were set to true, the partitions for which the aggregations/indexes were dropped would have their aggregations and indexes rebuilt (but not the entire partition reprocessed).

So I think you are confusing the message "partition processing operations" with the actual reprocessing of the partition, and the customer dimension just takes longer to process than the account partition (possibly because it has more members/hierarchies/relations/...).

For reference you could visit this link which offers you a script on the $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT dmv to validate this is what happens: Different Kinds of SSAS Processing in simple words.

The "impact analysis" button only uses the cube definition to see which partitions "may" be affected, it isn't until after actual processing of the dimension that SSAS knows which partitions "are" affected.


How to change directory of binlogs on Ubuntu 18.04 Current config:
[mysqld]
datadir=/data/mysql
log-bin=test

Updated config:
[mysqld]
datadir=/data/mysql
log-bin=/binlogdata/test
log-bin-index=/binlogdata/test.index

What I did was:

Stop mysql service
Update my.cnf
Move the current binlogs to destination path using:

mv /data/dir/test.* /binlogdata/

Update /etc/apparmor.d/usr.sbin.mysqld I added /binlogdata/** rw
Reload apparmor
I excecuted chown on binlogdata

chown -R mysql:mysql /binlogdata

Start mysql service

Error on /var/log/mysql/error.log is
mysqld got signal 11 ;

I already got it to work after I added the 3rd line.


Increment fixed value in select clause How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention:

SELECT
    name, ++x as ordinal
FROM
    foo;

name   | ordinal
----------------
john   | 0
paul   | 1
george | 2
ringo  | 3

The ultimate intention is to select users from a database to win first, second, and third prizes. I would have one user win first prize, five second prize winners, and twenty third prize winners:

SELECT
    name, IF(++x<2, 'first',  IF(x<7, 'second', 'third') ) as prize
FROM
    foo LIMIT 26;

name     | prize
------------------
alice    | first
bob      | second
charlie  | second
dotan    | second
erik     | second
frank    | second
george   | third
henry    | third
:        | :
:        | :

This is running on MySQL v5.5. Thank you.

You can use user-defined variables for this.

In your first example this would be as simple as this:

SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY whatever_column;

Here we initialize the variable in another query. This is often more comfortable than doing this:

SET @rn := -1;
SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
ORDER BY whatever_column;

when you're not allowed to execute multiple statements in PHP or whatever.
The variables are session bound!
Further we initialize it here with -1 so that the first row will have value 0, because @rn + 1 is evaluated first.
Also note, that I added an ORDER BY clause. If you don't add it, your result is kind of meaningless, cause there's no order in a relational database unless you specify it. And the calculations done with any variables in the SELECT clause rely on the order of the rows.

Regarding your second example, here's just important, to take care of the order in the SELECT clause.

SELECT
name, @rn := @rn + 1 AS ordinal,
IF(@rn < 2, 'first', IF(@rn < 7, 'second', 'third')) AS prize
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY points DESC; /*or whatever_column*/


Simple recursive CTE stuck in infinite loop I have a need for a recursive query to find daisy chained related data. I have the following query

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)

INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),
('05147762', '05395782', '2016-12-31'),
('04564728', '05147762', '2016-09-30'),
('04299824', '04564728', '2013-07-31'),
('05224920', '04999841', '2017-03-31'),
('04999841', '05224920', '2016-10-31'),
('04824720', '04999841', '2015-12-31'),
('02917870', '04824720', '2015-06-30')

;with
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.parent
)
select *
from descendants

--order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source

As you can see from the test data, the parent and child fields daisy-chain back and forth. Unfortunately, it's getting stuck in an infinite loop when I run the above query and I'm scratching my head as to why.

The final goal is to get the data to look like this:

Parent      Child       Date          Rank
05529744    05529744    9999-12-31    1
05529744    05395782    2017-12-31    2
05529744    05147762    2016-12-31    3
05529744    04564728    2016-09-30    4
05529744    04299824    2013-07-31    5
05224920    05224920    2017-03-31    1
05224920    04999841    2016-10-31    2
05224920    04824720    2015-12-31    3
05224920    02917870    2015-06-30    4

I think you are missing the anchor WHERE...

select parent, child as descendant, Date, 1 as level
    from #source where <<SOMETHING TO SELECT JUST FEW RECORDS>>

Otherwise, every time you will be selecting all the records.

Moreover, check the first 2 records...

('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),

They are cyclical... This will never finish...
Fix the data, give it another try and let me know

Other thing that will throw error is the date convertion...
Your date fields should look like this:

convert(date,'9999-12-31',120)

Fixing some of the data. The following CTE will work:

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)

INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05529744', convert(date,'9999-12-31',120)),
('05395782', '05395782', convert(date,'2017-12-31',120)),
('05395782', '05147762', convert(date,'2016-12-31',120)),
('05147762', '04564728', convert(date,'2016-09-30',120)),
('04564728', '04299824', convert(date,'2013-07-31',120)),
('05224920', '05224920', convert(date,'2017-03-31',120)),
('04999841', '04999841', convert(date,'2016-10-31',120)),
('04999841', '04824720', convert(date,'2015-12-31',120)),
('04824720', '02917870', convert(date,'2015-06-30',120))

select * from #source order by 1

;with
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source where Parent = Child
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.Parent
    where s.Child <> s.Parent
)
select *
from descendants

order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source;


Modeling global and private parameters I'm trying to model a database that will be used for management and execution of SSIS packages. The entities are Client, Job and Package. A Job is the managed unit that references Client and Package.

I need to model parameters for the Package. Some parameters are Global, common to all clients. Others pertain to a particular client. The source server, is a good example of a client parameter and target server is global to all clients. Here's the model I have so far:

Here is a scenario:

Global Parameters:

SSIS_ServerName
StagingServerName
StagingUserName
StagingPassword
LogServerName
LogUserName
LogPassword

Client Parameters:

SourceServerName
SourceUserName
SourcePassword
SourceDatabase
StagingDatabase
BlockSize
AllotedRunTime

It doesn't seem very clean to me. Anyone have a suggestion how to better model the parameters?

It's the relationship of PackageParameter to ClientParameter and GlobalParameter I don't quite know what to do with. PackageParameter lists the names of all parameters for the package and whether they are client or global. The values for those parameters would come from either the ClientParameter or the GlobalParameter, depending on the type.

Decided to do away with GlobalParameter and use a special PackageID (0) for global parameters. Simplifies matters a great deal in both the model and the code.