Inner Join or direct data save in SQL Server I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its related information like

Location

Country
State
City
Location

Education

School
College
University
etc

So finally user table will join with these and will fetch information through views. I am maintaining index properly. However thinking for future and want to make best solution for Select query.

My question is what is best process for faster select query, shall I do inner join among thees tables or should I  store direct location type information in user table as these changes very rare. All location fields will be nvarchar type and length around 100-250 char.

I believe you are going to want to have things split out into multiple tables as you've described. Everything in the user table, and don't name it user lest you want to spend your application's lifetime writing [user], is going to result in every access to the database to hit one table. Imagine it's the lunch hour and everyone logs in to check their SumanSpace social network site. .5M users all reading that user table at once and then you have people creating new accounts as well as updating their existing data. The less wide you can make that base table, the better as you're going to be able to stuff more entries in an 8k page.

Plus, things like updates to Education or Location won't need to lock the main user table which could cause contention. Those locks would be on, what I assume, are less frequently accessed tables.

Specific to your tables. Education - I would look to design it as 2 - 3 tables. EducationType would be a lookup table describing the type of institution (grade school, middle school, junior high, high school, community college, military college, college, university, etc). EducationLocation to associate an institute of learning with a physical space. Something to consider here are universities with multiple locations like DeVry-is there business value in building out a location hierarchy with the parent in Downer's Grove and then all the satellite campuses listed? EducationObtained could be another lookup table, indicating the type of education obtained. For things less than the collegiate level, it'd have a value of graduated but the university level could have Bachelors, Masters, PhD, JD, Divinity (however abreviated) in Sciences, Arts, Fine Arts, etc. And of course, a cross reference table tying a user to their tenure (start and stop dates) at an institute.

Finally, you won't want an INNER JOIN as people may not have or may not choose to record their education or location information.

Those are my top thoughts before I go and get cleaned up. I'm sure @DennyCherry has some insight into the matter based on his time at MySpace.


Generate data delta between two databases PostgreSQL I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6.  This database is copied and sits in number of client sites without an Internet connection.  Changes that are made to this database should be delivered to the client sites.

I'm looking for a way to generate a delta script between the current version of the database to the previous and execute this script in each client's site (open source/ freeware solution - RedGate is not an option).

The main assumption is that the schema is the same - so the delta is only in data (insert / update / remove rows)

I have checked a number of proposed tools (here for example) and the one that worked nice was SQLWorkbench-J using its WbDataDiff command. But its very slow - even if the databases resides on the same machine. I assume that its because the delta is calculated externally to support different databases sources.

Does anyone have a suggested way to do that? Is there a way to make it run internally on the PostgreSQL instance to increase the performance (I understand that joins cannot be calculated using different databases according to here - is using the "foreign data wrapper" may help in this situation)?

The traditional mechanism if you can

Dump the tables at point-in-time into a CSV (\COPY with ORDER BY)
Modify the tables, or wait for modiications
Dump the tables again at a point-in-time (\COPY with ORDER BY)
Diff the CSVs
Thennn

If the rows are not present in the second versions, write a DELETE (or batch for a larger delete)
Otherwise write a INSERT ON CONFLICT .. DO UPDATE (or batch for a larger INSERT)

This handles INSERTS, UPDATES and DELETES and only requires you to check for the presence of the surrogate key. If you don't have a surrogate key, things obviously get a lot more complex. And, even more complex if you have no UNIQUE keys at all.

Alternatively, dump the table as a 50gB CSV as an .xz (or other compressed format) and just be done with it. Probably compresses to a few GB, in a few hours.


UPDATE the records in oracle SQL with joinings and where exists query I have the tables below and

tempaddresstable
account
premise
address

Here is the link of details(id of account = account_fk of premise)
(address_fk of premise = id of address)

So only goal is to update all the records  info from tempaddresstable to address table

I have tried the below query but I am getting error

UPDATE ADDRESS AD
SET AD.ADDRESS1 =
  (SELECT (B.TEMPADDRESS1)
   FROM tempAddressTable B,
        ADDRESS AD
   WHERE AD.ID =
       (SELECT (P.ADDRESS_FK)
        FROM ACCOUNT A
        LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
        LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
        LEFT JOIN ADDRESS AD ON AD.ID = P.ADDRESS_FK
        WHERE A.EXT_REF = B.TEMPEXTREF ))
WHERE EXISTS
    (SELECT 1
     FROM tempAddressTable B
     WHERE AD.ID =
         (SELECT DISTINCT max(P.ADDRESS_FK)
          FROM ACCOUNT A
          LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
          LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
          WHERE A.EXT_REF = B.TEMPEXTREF ));

Error :

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 -  "single-row subquery returns more than one row"
  *Cause:
  *Action:

Details of the table

tempaddresstable
account
premise
address

tempaddresstable

+------------+----------------------------+
| TEMPEXTREF | TEMPADDRESS1               |
+------------+----------------------------+
| 34         | 101 DRCSG117432RES RD TEST |
| 35         | 102 DRCSG117               |
| 36         | 100 DRCSG117432RES RD      |
+------------+----------------------------+

account

+---------+---------+
| ID      | EXT_REF |
+---------+---------+
| 1041261 | 34      |
| 1041262 | 35      |
| 1041263 | 36      |
+---------+---------+

premise

+---------+------------+------------+
| ID      | ACCOUNT_FK | ADDRESS_FK |
+---------+------------+------------+
| 1044610 | 1041261    | 1041502    |
| 1044611 | 1041262    | 1041503    |
| 1044612 | 1041263    | 1041504    |
+---------+------------+------------+

address

+---------+----------+
| ID      | ADDRESS1 |
+---------+----------+
| 1041502 | test     |
| 1041503 | test     |
| 1041504 | test     |
+---------+----------+

Usually, when you need to join multiple tables in an UPDATE, MERGE is the solution.

merge into address a1 using
 (
   select
    p.address_fk,
    ta2.tempaddress1
  from
    premise p
    join account ac on (ac.id = p.account_fk)
    join tempaddresstable ta2  on (ac.ext_ref = ta2.tempextref)
 ) ta1
 on (a1.id = ta1.address_fk)
when matched then update set a1.address1 = ta1.tempaddress1;


MySQL TRUNCATE TABLE blocks queries in other databases When I run automated tests against my test_development database, I often observe degraded performance for my production users, even though those users aren't accessing the same database as the test suite. In viewing the process list, it seems that the degradation corresponds to frequent TRUNCATE TABLE statements which run on the test db (to reset the db state for the next test). I see a system lock for the TRUNCATE TABLE statement, and I see "Waiting for query cache lock" for the production users. Here is part of my process list:

mysql> show full processlist\G
*************************** 8. row ***************************
 Id: 67503075
User: delivery
Host: xxxxxxxxxxx:34734
db: test_development
Command: Query
Time: 0
State: System lock
Info: TRUNCATE `change_version`
*************************** 12. row ***************************
Id: 67503141
User: delivery
Host: xxxxxxxxxxx:37959
db: delivery
Command: Query
Time: 0
State: Waiting for query cache lock
Info: SELECT COUNT(*) FROM `address_single_4` WHERE `address_single_4`.`reportId` = 75483054
12 rows in set (0.04 sec)

Why is the TRUNCATE TABLE statement (apparently) blocking queries that are not in the same database, and how can I fix it?

I am on MySQL 5.6.21, running in Amazon RDS. All tables are InnoDB.

TRUNCATE would have to invalidate all dependent queries in the query cache, so that's probably why it's taking a lock on it. If your query cache is sufficiently large, invalidation might take some time. Since MySQL "databases" are more like schemas, a single query cache is maintained for all databases in the MySQL instance, so there really is no isolation between them.

You may want to consider disabling query cache or, better yet, using different MySQL instances for testing and production.


Consequences of tnsnames.ora and sqlnet.ora being unavailable Our database servers are configured with the tnsnames.ora and sqlnet.ora files centrally located on a shared network drive. In every ORACLE_HOME/network/admin directory we have a symbolic link pointing to the centrally-located tnsnames.ora and sqlnet.ora files.  I want to understand the consequences of these files becoming unavailable in the case of a loss of connectivity to the network share.

Are these files used by any database processes, or only by client programs like SQL*Plus?
If these files became unavailable, would existing client connections be affected, or only new connections?
Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?

Are these files used by any database processes, or only by client programs like SQL*Plus?

Yes, these files are used by Oracle processes while connecting to the database, once the connection is established they don't need these files.

For example,

If you have specified the following parameter in sqlnet.ora file on server side.

tcp.validnode_checking = yes
tcp.invited_nodes =<list the valid nodes here>

The Oracle server process will check whether the incoming connection request is valid or not. Once it finds the node is valid it will proceed ahead and it no longer needs the sqlnet.ora file.

Similarly, Oracle client process will look up TNS Name on tnsnames.ora file for the connection string. Once it finds the connection details to make connection it also no longer needs the tnsnames.ora file.

  If these files became unavailable, would existing client connections be affected, or only new connections?

Only the new connection will be impacted because existing connections have already used these files during the connection initiation.

  Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?

If you are middle of the transaction using that database link and the tnsnames.ora is missing then it will not impact the on going transaction. However, next session which uses the TNS Name in database link will fail.

ORA-12154: TNS:could not resolve the connect identifier specified


Searching between dates on a CHAR type field I have a table that has a field in which dates are stored, the problem is that the field type is CHAR(10) and I'm being asked to query for data between certain dates.

I don't want to change the field type because I'm not sure how's that gonna affect the frontend application.

Is there a way of querying for results between dates without making changes to the data type?

Regards.

You can cast your date to perform where clause on it just like this.

WHERE CONVERT(DATETIME, MyStringDateField, 120) BETWEEN @DateParam1 AND @DateParam2

The last parameter in CONVERT is the date format. You must choose the one that match your text data of your date. You can find the list here

You must be aware that if a string does not match a date for a particular record, your query will fail with a message saying that the data XYZ cannot be converted to DATETIME type.


Mirrored local accounts to connect to database on different server and domain My database client and database server are on different machines and are not connected to the same domain (this is not supported with my web host). I'm not keen to send username/password details via a connection string and want to use windows authentication to connect to the database.

In this article's overview, it mentions this (emphasis mine):

  When you use Windows authentication to connect to SQL Server, you use
  either Kerberos or NTLM authentication depending on the configuration
  of your servers and domain. You might not be able to use Kerberos
  authentication if:

  Your database client and database server are separated by a firewall
  which prevents Kerberos authentication.
  Your application server and database server are in separate domains
  with no trust.

  In these situations, you can use either mirrored local accounts or SQL
  authentication. With mirrored local accounts, you configure two
  accounts on each server with identical user names and passwords. You
  must ensure that the passwords remain the same.

I'm assuming this just means creating a windows user with the same username and password on both machines, however I'm skeptical this would work. When I'm adding permissions for this user on the database client, it prefixes the username with the servername, and the same thing happens when creating a database login on the database server.

Is this what it means to create a mirrored local account? If so, how does this work considering the users are in different domains?

That is not a sql server question but a windows question.
If you have multiple computers, login on one with a local account and try to connect to another computer it will try to use the username and check it against its own local account database. If the passwords match the challenge/respone authentication will succeed.
As far as I remember the users have to be local accounts - on both sides.


If I update a column record in a table, will indexes that do NOT have this column in it be affected? In terms of performance if I have a table like so:

CREATE TABLE [TESTDATA].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And then create a non-clustered index like this:

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [TESTDATA].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

If I perform an update to the table, only changing Col3 does the DB need to touch the index _idx_TableA?

Just curious how far performance touches all indexes?

No, _idx_TableA will not be affected for this operation.  I have modified your example and added another index (NCI) that actually includes the key column Col3.  Here's my example code:

use testdb;
go

CREATE TABLE [DBO].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [int] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [DBO].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

create nonclustered index IX_Col3
on dbo.TableA(Col3);
go

insert into dbo.TableA
values
    ('a', 'b', 10),
    ('b', 'c', 11),
    ('c', 'd', 12);
go

update dbo.TableA
set Col3 = 13;

If I capture the post execution plan of the UPDATE command, you will see something similar here:

As you see from the above screenshot, the indexes that are updated are the clustered index, and my nonclustered index IX_Col3.  The index _idx_TableA does not get updated.  Therefore, only the indexes that contain Col3 will be affected, and therefore updated by your particular example.


How to Create Indexes Or Manage Performance For Complex Queries Before a Release? Last night we had a major release that involved a massive data dump and some complex queries.   We semi-anticipated the slowness and were able to react quickly to creating some new indexes in SQL Server 2012.   However, the site was lagging for quite a long time post-release.

Is there a way to manage and prevent this ahead of time?   You can't create indexes before the data is on the production database, but there must be some way to prepare the database.

Part of the problem is not just indexes, but giving the database enough time to get statistics to make the indexes more efficient.

One thought I had was it might have something to do with sharding or replication, or perhaps some sort of db merge.

It's always a tough thing to performance test production systems, companies typically have far lesser systems/hardware for QA/Staging/Dev environments. One method I use is to baseline performance of our QA system before implementing SQL changes, and then check performance after. I am not concerned with the actual times, but the delta of before and after. Unless you have exact hardware/systems between environments, you will be somewhat limited.


Database Size is Different after pg_restore (389 GB vs 229 GB) I created a backup of my database in a PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_dump:

PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development

and restored it to a new server running the same versions of PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_restore. For the restore, executed the following commands in psql as user postgres:

CREATE DATABASE database_development;
\c database_development
CREATE EXTENSION timescaledb;
SELECT timescaledb_pre_restore();

\! time pg_restore -Fc -d database_development /var/lib/postgresql/backups/database_development_2020-02-29

SELECT timescaledb_post_restore();

The database size of the original database was 389 GB but the restored database was 229 GB. These sizes were obtained by running

select pg_size_pretty(pg_database_size('database_development'))

Some differences:

The old database is stored on a ext4 partition, while the new database is stored on a ZFS filesystem with compression disabled. Both database instances are running inside a Docker container with an Ubuntu 18.04 host.

Question: How can we explain the differences in the database sizes? There were no errors encountered during both the pg_dump and pg_restore.

Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference.

The reason is dump being a logical one it will only create statements to insert your data and the dead rows would anyways be invisible to it. If you have lots of updates and deletes happening or in other words your db is highly transactional, it will create more dead row versions and needs an aggressive vacuum to deal with bloating as well. If and when you compare the dead vs live row counts, before and after the restore you will see the difference.

Also just to be on a safer side do perform a manual vacuum analyse on the db post a dump restore, i have seen in the past that due to wrong estimation the planner changes the optimum plan to be used for the queries.


MySQL database datafiles in windows SystemData directory When I create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\.

Each table has three files with these suffixes:

TableName.frm
TableName.MYD
TableName.MYI

I don't know what those exactly are.

Please explain to me what those are and what is usages?

Thanks in advance.

Those three files represent one MyISAM table

For the MyISAM table, mydb.TableName, here is what they are

TableName.frm : Table format file (all Storage Engines have this one)
TableName.MYD : MyISAM data for the table
TableName.MYI : MyISAM Indexes for the Table

Click to read MySQL Documentation for MyISAM Storage Engine. In part, it says:

  Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.


What contributes to the transaction size on a MySQL UPDATE? Background
We're running a MySQL 8 INNODB cluster behind some Java services.  We received an error from the application because we'd exceeded the group replication transaction size limit (“Error on observer while running replication hook 'before_commit'”).  Our limit was set to 150Mb at the time.
Problem
Looking at the transaction involved I don't understand how it might have involved anything like 150Mb.
It involved an update to two tables
update my_table mt
inner join my_table_aud mta on mt.id = mta.id
  set mt.boolean_column_1 = TRUE,
      mt.boolean_column_2 = TRUE,
      mt.varchar_column = coalesce(mt.varchar_column, ?2),
      mta.varchar_column = coalesce(mta.varchar_column, ?2)
where mt.boolean_column_1 = FALSE
AND mta.rev <= ?1

which involved approximately 100 rows in my_table and maybe 200 rows in my_table_aud.  Plus one other simple insert to a different table.  The varchar columns were updated with around 10 bytes of data.
However the two tables involved in the UPDATE do both have a different longtext column, which wasn't updated. There would have been on average maybe 1MB in text per row updated in those columns.
The only explanation I can think of for us exceeding the transaction limit would be that the text in longtext columns contributed to the transaction size, even though they were not referenced in the update.
I searched for documentation on what contributes to the transaction size of a transaction in MySQL and haven't been able to find anything useful.
Please can someone help my understanding of how the transaction size limit might have been exceeded in this scenario?

The transaction size is not just based on the amount of data you're actually change, rather the whole state of the row before you made the change is preserved in case of a rollback and therefore the entire row is essentially copied. From further research it sounds like as much as even the entire page of rows is copied to the rollback segment, so depending on how many pages contain the rows you're updating, it could easily be a lot more data generated in the transaction then what you're actually updating.


Updating multiple columns using Case I have a query that looks like below:

UPDATE some_table
SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END,
    column_y = CASE WHEN y_specific_condition THEN new_value_for_y ELSE column_y END,
WHERE some_more_conditions

Problem with above is, each column (x, y) still gets updated with their own value if some_more_conditions return true irrespective of their specific conditions returning true. I tried removing ELSE from above, but no luck

I am combining as some_more_conditions are same for both cases, I think its better to perform all in 1 update (suggestions welcome)

Do you know if there is a way that can I can perform the above update in 1 query by skipping the individual columns where the specific conditions do not match (basically avoid overwriting of same values)

Both columns specified in the SET clause of your UPDATE are going to be touched (written to) for all the rows matching the some_more_conditions, no matter what x_specific_condition or y_specific_condition may evaluate to. There is no syntax to work around that while sticking to a single statement.

So, either live with that or go for two statements:

UPDATE some_table
SET column_x = new_value_for_x
WHERE some_more_conditions
  AND x_specific_condition;

UPDATE some_table
SET column_y = new_value_for_y
WHERE some_more_conditions
  AND y_specific_condition;


Can auto failover cause split-brain? (SQL AlwaysOn) I have been studying Cluster voting and AlwaysOn failover.  The topic of split-brain comes up and sounds terrible.  This following from Microsoft seems to indicate that only a person with SA, doing something stupid can cause it.

When more than one subset of voting nodes is able to establish a quorum on its own, that is known as a split-brain scenario. In such a scenario, the nodes in the separate quorums may behave differently, and in conflict with one another.
The split-brain scenario is only possible when a system administrator manually performs a forced quorum operation, or in very rare circumstances, a forced failover; explicitly subdividing the quorum node set. Source

There are a lot of variables, and I wonder if something like changing the Dynamic quorum (is on by default) could create a scenario where a split-brain scenario exist.
Can auto failover be configured so a split-brain scenario is possible?
I want to know, so that if it is possible I won't configure that way.

This following from Microsoft seems to indicate that only a person with SA, doing something stupid can cause it.

Yes, or doing completely unsupported things like using DSFR on a fileshare witness. If you're staying in supported land and aren't going around forcing stuff up, the worst case is the entire cluster will be offline until an admin can intervene.

  There are a lot of variables, and I wonder if something like changing the Dynamic quorum (is on by default) could create a scenario where a split-brain scenario exist.

I wouldn't turn this off, it's actually your friend. This stops the need from having to change the node weight per node every time something happens in the cluster. BTW, you can't turn dynamic witness off.

There are extremely rare times that I would even consider turning dynamic quorum off and in those times it's because we've looked at the way we want the cluster to work and we specifically want it to fail if certain things happen. It's not something that you'd want to do without great testing and consideration.

  Can auto failover be configured so a split-brain scenario is possible?

Absolutely not.


Fastest way to move Collection from Sharded MongoDB Cluster to a another Sharded MongoDB Cluster I have to move a MongoDB Collection (Size: 1TB+) from Sharded MongoDB Cluster to another[5 shards on both clusters]. During this operation, it is assumed that the accompanying program will be offline, so no need to worry about inconsistent data, but I need to minimize the downtime.

I've tried testing it with mongodump and mongorestore, but looks like that is taking ages to finish.

Please share your experiences for such kind of scenarios.

Note: Cluster has only one collection, so I am open at cluster level sync as well.

As per MongoDB documentation  Create Chunks in a Sharded Cluster

  If you want to ingest a large volume of data into a cluster that is
  unbalanced, or where the ingestion of data will lead to data
  imbalance, such as with monotonically increasing or decreasing shard
  keys. Pre-splitting the chunks of an empty sharded collection can help
  with the throughput in these cases.

EXAMPLE

To create chunks for documents in the myapp.users collection using the email field as the shard key, use the following operation in the mongo shell:

for ( var x=97; x<97+26; x++ ){
    for ( var y=97; y<97+26; y+=6 ) {
        var prefix = String.fromCharCode(x) + String.fromCharCode(y);
        db.adminCommand( { split: "myapp.users", middle: { email : prefix } } );
    }
}

This assumes a collection size of 100 million documents.


Share mongo database between two OS on dual boot Now I have two OS on my computer: Windows 10 and Linux (arch). Sometimes I use Linux but I also use windows because of gaming, and I want to create a small application that is going to work on both OS. I want to know if it is possible to share a Mongo database with all its collections and indexes between the two OS.

This is something I want on my computer for personal usage, it is just a small application, but it creates a lot of documents and some indexes. Also, I will be adding Indexes frequently.

I can not use any external server or machines to store the DB, because the application should work offline. I don't want to migrate the DB from one system to another every time I boot my computer using mongoexport because that takes time and recreates the indexes.

In theory, YES... If your mongod dbpath is at partition what both sides can read and write. So, you cannot use linux partitions (ext2,ext3,ext4,...) because your windows don't know how to handle. It's better not to use ntfs at windows side, because linux don't always handle ntfs right. So, solution is use older vfat32 partition type. Both sides can handle that well.


Any problem will all InnoDB and one MyISAM table? I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM table just because this one table doesn't need transactions.

Is there a maintenance or tuning headache here?
Any benefit to it?

Thanks

Is there a maintenance or tuning headache here? ABSOLUTELY !!!

Any benefit to it? I'll pretend I didn't read that.

The MyISAM storage engine always performs a full table lock with each DML (INSERT, UPDATE, DELETE). SELECTs block the daylights out of DML Statements.

Any transactions that mix MyISAM and InnoDB tables will cause even InnoDB tables to degenerate to full table locks because of the presence of even just one MyISAM table. I wrote about that in StackOverflow back in March 2011.

If the only MyISAM table is just the log table you mentioned, that is still very bad. The reason? If 50 DB Connections want to write a single row into the log table, there will 50 full tables locks, 50 inserts. Here is what is worse:

50 DB Connections, 1 performs table lock, does INSERT, 49 DB Connections have to wait
49 DB Connections, 1 performs table lock, does INSERT, 48 DB Connections have to wait
48 DB Connections, 1 performs table lock, does INSERT, 47 DB Connections have to wait
...
1 DB Connection, 1 performs table lock, does INSERT

Get the idea ??? This is what awaits you by making the log table MyISAM !!!

If the same log table was InnoDB, 50 DB Connections would perform 50 one line transactions that do not block each other.

Please read these questions for your own benefit on MyISAM and InnoDB

What are the main differences between InnoDB and MyISAM?
Benefits of table level locking
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Is it common practice to mix InnoDB and MyISAM tables on same server?

CAVEAT : To be fair, if a MyISAM has a FULLTEXT, then you have no choice but to live with it. MySQL 5.6 will change that problem as InnoDB will support FULLTEXT indexes.


Viewing Type Of Lock Being Used T-SQL - Update Lock Query Issue I am looking for a way to determine whether SQL will take a shared lock or an update lock.

Take these three queries. I believe in that in theory:

Query one will take a shared lock on the pk, due to using SELECT TOP 1
Query two will use an update lock due to using MIN
Query three... not sure whether the use of group by and order by will make it do a shared lock?

However how do I prove this theory? Also if anyone can tell me how to format sql in these posts that would be great :)

Query one:

/* Uses Shared Lock? */

CREATE TABLE #TABLE1
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE1
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE1
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE1
SET Name = 'Jeff'
WHERE ID = (SELECT TOP 1 ID FROM #TABLE1 ORDER BY DateModified ASC)

Query two:

/* Uses Update Lock? */

CREATE TABLE #TABLE2
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE2
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE2
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE2
SET Name = 'Jeff'
WHERE ID = (SELECT MIN(ID) FROM #TABLE2)

Query three:

CREATE TABLE #TABLE3
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

CREATE TABLE #StagingTable
(ID int,
DateModified datetime)

INSERT INTO #TABLE3
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE3
VALUES (2,'Tim', '2013-01-01')

INSERT INTO #StagingTable

SELECT MIN(ID), DateModified FROM #Table3 GROUP BY DateModified ORDER BY DateModified

UPDATE #TABLE3
SET Name = 'Jeff'
WHERE ID = (SELECT MAX(ID) FROM #StagingTable)

You may be wondering what the idea with query three is. However if you take a look at the data the date is not in line with the ID column. So the oldest date may not be the lowest ID number, which means the MIN logic in query two is invalid.

Thanks

I you try to find out which lock is currently been taking. Just take a short look in the tran_locks. There you find all current locks.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID() and request_session_id = [YOURSESSION]

Hope this helps you.

Another way could be the usage of WITH(HOLDLOCK) on your statement and append this code to the end of your transaction.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID() and request_session_id = @@SPID


Any way to represent simple AND/OR business logic in a database? I'm designing an application to provide students access to modules once they have completed certain online courses.

I have tables for: Students, Modules, Courses, and StudentCourseResults. Course requirements and modules will change over time and I believe it would be easier to track their relationships in the database than the application. Unfortunately this is not a simple 1-1 mapping. For example, for ModuleA you may need to complete Course 1 OR 2, but for ModuleB you may need to complete Course (1 OR 2) AND 3. Requirements may also be somewhat different depending on the type of student.

Has anyone tackled this kind of requirement before? I can't think of a solution other than creating a ModuleCourseRequirements table and putting the logic in a field (e.g. (1|2)&3) then parsing this logic in the application and performing subsequent queries to check the requirements are met.

Let me know what you think, thanks!

If you had billions of students and the rules were fixed, I'd recommend a stored procedure which performs the logic on rolling subsets, saving the results in another column or table.  Since you likely have fewer students, a view could handle the logic and then you'd never need to store the result.

If the rules are extremely complicated, then you'll want to pull the data into code and perform the logic there, where you have better control.


Postresql dynamic index creation for many tables How can I create an index in Postgres dynamically over many tables but always with different names.

Lets assume table x has a column y with all table names of hundreds tables which need to get an index on their z column type numeric. How can I run a loop which dynamically creates with the table names of column y of table x indexes on every table?

You can use dynamic SQL with a do statement that iterates over the tables in a schema:

do
$$
declare
 l_statement text;
 l_rec record;
begin
  for l_rec in select y
               from x
  loop
    l_statement := format('create index on %I (z)', l_rec.y);
    execute l_statement;
  end loop;
end;
$$

Postgres will automatically generate a name for the new index.


Designating databases to certain CPUs SQL Server 2012.
AlwaysOn
Cluster with two nodes, each has 4 cores

We are consolidating SQL server instances and moving bunch of databases from various locations to a single cluster which has two instances, 4 cores each.
There are "more" and "less" important databases which are going to be mixed at the same location. Is there a way to make queries from a database to use only certain core on the host?

Example:

We have 10 databases named A1-A10.
Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10  to use rest of the cores (in this case 1-3).

Thanks.

Is there a way to make queries from a database to use only certain core on the host?

One way is to use query hint OPTION (MAXDOP 1).

The problem with above apporach is that you have to hint everything that you don’t want to be limited by the server-wide setting, or use 0 (i.e. unlimited) for the server-wide setting and hint everything that you do want to be limited.

  Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10 to use rest of the cores (in this case 1-3).

A better way is to use Resoruce Governor. It is available only in Enterprise Edition (Dev edition as well).

Note: Make sure you do proper testing before implementing directly in PROD as I have seen really bad cases where without understanding, Resource Governor is implemented and things go horribly wrong.

If you are running on Enterprise Edition then you can create

CREATE RESOURCE POOL -- here you can specify min and max CPU allowance. Also by using AFFINITY SCHEDULER, you can bind specific schedulers to your Pool.
CREATE WORKLOAD GROUP USING RESOURCE POOL. -- Here you can specify max_dop=1.
This MAXDOP setting overrides the server-wide setting and any query hints.

Refer to : Resource Governor in SQL Server 2012 whitepaper for more details.

Aaron Bertrand has explained very nicely at : SQL Server 2012 Resource Governor Enhancement


SQL Server considerations when backing up / restoring a database that uses replication In the event that I need to restore a database involved in replication, what considerations do I need to keep in mind?

Do I need to keep backups of both the publisher and the subscriber?
Can I restore only the publisher and have the subscriber automatically sync up?
If the subscriber crashes, is it possible to restore from its back up and have it automatically sync up?

The answer depends on your type of replication. For example, merge replication allows you to update in more than one source DB, and then distribute those updates. With this topology, you would of course need backups everywhere data originates.

As for automatic syncing, I've seen that most of the time if there is any issue with repl on either end of publisher / subscriber, you will usually have to reinitialize the repl. An exception to this might be when you have a subscriber problem such that it is offline to the distributor. In that case the distributor would "catch up" the subscriber when it came back up IF there was no corruption (bad sequence) at the subscriber.

As a rule, back everything up, but don't count on being able to "jump-start" repl back to a functional state. Re-initializing it is common, and painful with large DBs.


Why is MySQL not connecting when reading from mylogin.cnf? On Ubuntu 20.04, I can log into a server by specifying the log credentials on the CLI:
$ mysql -h172.30.0.2 -uroot -p bar -e "SELECT id FROM users LIMIT 1;"
Enter password:
+----+
| id |
+----+
|  1 |
+----+

However, I cannot log in when using ~/.mylogin.cnf:
$ cat ~/.mylogin.cnf
[foo]
user=root
password="notTheRealPassword"
host=172.30.0.2

$ mysql --login-path=foo bar -e "SELECT id FROM users LIMIT 1;"
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I have confirmed that the file is being read by changing the permissions of the file and noticing MySQL complaining:
$ chmod 660 ~/.mylogin.cnf

$ mysql --login-path=foo bar -e "SELECT id FROM users LIMIT 1;"
mysql: [Warning] /home/dotancohen/.mylogin.cnf should be readable/writable only by current user.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Why might MySQL not connect when using ~/.mylogin.cnf? How should I debug why MySQL is not connecting?

There is a bug here.
You can check your .conf file permissions.
The root cause of crashes or other unexpected behavior will be in the error logs. The default path of the MySQL log file is /var/log/mysql/error.log on Ubuntu.
Also for debugging check this out.


How to apply query to select of another query, PostgreSQL 9.6 I have a query, which is working well:

    WITH geom_1 AS
    (
      SELECT ST_Transform(ST_SetSRID(ST_MakePoint(VAL_1, VAL_2), 4326),32652)
    ), point_3d AS
       (
         SELECT  ST_MakePoint(ST_X(st_transform),ST_Y(st_transform), VAL_3) FROM geom_1
        ), static_points AS
           (
             SELECT geom FROM eco.static_points WHERE name='Korea'
           )
   SELECT ST_3DDistance(st_makepoint, geom) FROM point_3d, static_points;

It's just needed to provide VAL_1,VAL_2,VAL_3.
I want take them from another query:

WITH param AS (
    SELECT longitude, latitude, altitude
    FROM eco.tracks
    WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND altitude IS NOT NULL
    )

So, input is 3 column, and output is column with distance.
How can I do it?

UPD: Looks like I did

   WITH
    coordinates AS (
    SELECT id, longitude as lon, latitude as lat, altitude as alt FROM eco.tracks WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND altitude IS NOT NULL
    ),
    point_3d AS (
    SELECT  ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt)  FROM coordinates
    ),
    static_points AS (
    SELECT geom FROM eco.static_points WHERE name='Korea'
    ),
    distance_table AS(
    SELECT  id, ST_3DDistance(ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt), geom) as distance FROM coordinates, static_points
    )

And updating third one

UPDATE eco.tracks tr
SET distance_1 = dt.distance
FROM distance_table as dt
WHERE tr.id = dt.id

I'm sorry, maybe I'm misreading something but I think you can get it using a single UPDATE.

UPDATE eco_track
SET    distance_1 = ST_3DDistance(ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326),32652)),
                                               ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326),32652)), altitude),
                                               (SELECT geom FROM eco.static_points WHERE name='Korea'))
WHERE latitude IS NOT NULL
AND   longitude IS NOT NULL
AND   altitude IS NOT NULL;

point_3d isn't used anywhere

point_3d AS (
    SELECT  ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt)  FROM coordinates
    ),

static_points is constant, you can replace it in the UPDATE sentence

SELECT geom FROM eco.static_points WHERE name='Korea'

All the information is on the eco_track table


Postgres Logical Replication - "replication slot "mysub" was not created in this database" I have two database servers 192.168.150.210 and 192.168.150.220
I created publication and subscription successfully for logical replication.

postgresql.conf file is modified on both servers.

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

On both servers wal level is set to logical.

testha=# show wal_level;
 wal_level
-----------
 logical
(1 row

pg_hba.conf file is modified on both servers:

on server 192.168.150.210

host    all             all             192.168.150.220/32      md5

on server 192.168.150.220

host    all             all             192.168.150.210/32      md5

with user postgres I created table article on database testha

\c testha
create table article(idart int primary key,name varchar(10), quantity int);

just in case I also granted permission to that table for user postgres/postgres

grant all on article to postgres;

I created publication on server 192.168.150.210

CREATE PUBLICATION mypub FOR TABLE article;

I can see that everything is OK:

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

I tested connection and communication between servers is ok:

psql -h 192.168.150.220 -U postgres -W
psql -h 192.168.150.210 -U postgres -W

I restarted servers but in the logs I see errors:

ERROR:  replication slot "mysub" was not created in this database

ERROR:  could not start WAL streaming: ERROR:  replication slot "mysub" was not created in this database
LOG:  background worker "logical replication worker" (PID 30667) exited with exit code 1

What am I doing wrong. I followed instructions from multiple sites and did not find any additional instructions.
https://blog.dbi-services.com/postgresql-10-beta-1-logical-replication/

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

What was the output of this command?

You should have gotten something like either:

NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION

Or

ERROR:  could not create replication slot "mysub": ERROR:  replication slot "mysub" already exists

It sounds like what happened here is that at the time you created the subscription, the publisher was running as expected.  But then you did something weird, like disconnect the subscriber, rename the database "testha" on the publisher to something else, make a new "testha" and try to reconnect the subscriber to that.


How to restrict access to SQL Server user in some period of time I have two SQL Server users 'A' and 'B'.

I use user 'A'  for ETL and 'B' for reporting.

My requirements is to restrict user 'B' access while user 'A' performing ETL (Extraction, Transforming and loading)

You can :

Method1

1) Create a trigger, in case if its the concern for a single table that user A should be not be conflicting. Therefore you can create the trigger which should restrict user A from the current permission it have , that you would like to restrict.

2) Once done with creating trigger, you will need to create an SQLAgent job which enables the trigger during the time when user B starts its process and second step of that job will be to disable the trigger when user B finishes it tasks.

Method 2

You can also, in case the whole database is in question where user B needs no conflict with user A you can script out the permission for user A and schedule a jobs which (during you're required time frame)will remove the permissions of user A and second job which add backs the permission of user A after a defined time as per the need.

There can be various other methods for sure, but may be i am not able to recall as of now. Hope if above 2 helps.


Finding Records that Have Same Options Trying to figure out how find out which user records have at least 1 similar option enabled and then display these.

Tables:

user (id, username)
thing (id,title)
uthing (id, uid, tid)

Uthing table has a record of what options each user have selected.

uid = user.id
tid = thing.id

I need to compare the options (uthing) from user id 1 with all other users.

How can find users that have 1 or more options(uthing) of USER ID 1 and return these users with the options found?

This query will give me all options for user id 1.

select thing.title,thing.id as thingid
from thing join uthing on uthing.tid = thing.id where uthing.uid = 1;

You achieve this by following this query

select uthing.id, uthing.uid, thing.title
from uthing left join thing on uthing.tid = thing.id
where uthing.tid in (
        select thing.id
        from thing join uthing on uthing.tid = thing.id
        where uthing.uid = 1
     );


Using SUBSTRING and RTRIM Together I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field.  I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

Try this

select right(rtrim(alt.Address),2),
   rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):

select right(rtrim(alt.Address),2),
   ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.


Transaction Log maintenance on Mirror database SQL Server Version: 2008 R2 Enterprise SP2

I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect.  We have a single production instance with 3 databases each being mirrored offsite to a DR instance.

While looking at the DR instance I noticed that the LDF files were huge, over 35GB for the heavily used databases.

I understand that this is probably due to the mirror databases being in Full recovery mode and that the logs have never been backed up, they will just continue to grow until running out of drive space.

We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?

At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?

Again this is transaction log maintenance recommendations on the MIRROR database.

Thanks for any input

We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?

You can't do a log backup on the mirror database.

  At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?

You can't do a full database backup on the mirror database either.

Take this for instance:  I have Server1 which houses the principal database AdventureWorks2012, and I have Server2 which contains the mirror.  Here's what happens when I try to run backups on the mirror database (on Server2):

use master;
go

backup database AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.bak';
go

  Msg 954, Level 14, State 1, Line 2
  The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
  Msg 3013, Level 16, State 1, Line 2
  BACKUP DATABASE is terminating abnormally.

backup log AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.trn';
go

  Msg 954, Level 14, State 1, Line 1
  The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
  Msg 3013, Level 16, State 1, Line 1
  BACKUP LOG is terminating abnormally.

Take a look at this Database Mirroring FAQ by Robert Davis.  I will quote him regarding this operation and the mirror database transaction log maintenance:

  When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

So there you have it.  If you have transaction log backups on the principal then similar log reuse behavior will be mirrored to its partner database.


Linked Server to Azure Error: "Access to the remote server is denied because no login-mapping exists." I'm setting up a linked server from an on-prem SQL Server to a database on MS Azure (see below).  When non-admin users query the Azure database via the linked server they get:

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is denied because no login-mapping exists.

Any thoughts on how to get this working?

EXEC sp_addlinkedserver
@server='LincServer',
@srvproduct='',
@provider='sqlncli',
@datasrc='myAzureDB.database.windows.net',
@location='',
@provstr='Encrypt=Yes;TrustServerCertificate=No;User ID=myAzureID',
@catalog='MyDB'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LincServer',
@useself = 'false',
@rmtuser = 'myAzureID',
@rmtpassword = '########'
GO

The other answer is wrong and I want to disprove it with repro code and pictures.

The problem here is the parameter @provstr, it's a known issue and how to solve it is described here: Access to the remote server is denied because no login-mapping exists:

  Problem: Unable to run a query through a linked server SQL Server
  2005. This problem only happens with a non-sysadmin account.

  You got the message below:

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is
  denied because no login-mapping exists..

  Cause: When creating a linked server with the parameter @provstr and
  you use a local SQL Server non-admin or non-Windows account, you have
  to add the parameter "User Name"   into the @provstr

  Resolution : Add "User ID=Username" into the provider string on your
  linked server

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
  @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server',
  @provstr=N'SERVER=serverName\InstanceName;User ID=myUser'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
  @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser',
  @rmtpassword = N'*****'

The OP has already done it, he included remote login into provider string in the linked server definition, so I really have no idea what else can cause the error.

But now I want to prove that following the cited guide you get rid of the error, while following the other answer we solve nothing.

Here is my repro code.

I have 2 servers, the default and named instance (hp2\sql_2012_dev), I'm going to link hp2\sql_2012_dev server to the default using @provstr without passing in @provstr remote login rmt that is not admin, this will cause the OP's error for ALL non-admin users.

I will map all the local logins to 1 remote login using OP's code.
Then I'll try to apply the suggestion to map the problematic login individually but this will not solve the problem.

Then I'll recreate my linked server passing the remote login with the provider string and this will fix the issue WITHOUT ANY ADDITIONAL INDIVIDUAL MAPPING.

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkServer2012_rmt',
@provider=N'SQLNCLI',
@srvproduct = '',
@provstr=N'SERVER=hp2\sql_2012_dev';--don't pass User ID=rmt' now in order to cause the error

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

At this point non-admin login l tries to select:

select *
from linkserver2012_rmt.master.dbo.spt_values;

And got the error

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is
  denied because no login-mapping exists.

Ok, we are trying now to map it individually:

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = N'l',
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

And login l re-tries to select from linked server but he's got the same error

At this point I drop and re-create linked server, this time passing in rmt login in linked server definition, and map all the local logins to rmt, without doing any individual mapping:

exec master.dbo.sp_dropserver @server=N'LinkServer2012_rmt', @droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkServer2012_rmt',
@provider=N'SQLNCLI',
@srvproduct = '',
@provstr=N'SERVER=hp2\sql_2012_dev;User ID=rmt'; --now pass in rmt login

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

This time non-admin login l has no problem at all:

I cannot consider this my post a real answer to OP's problem, maybe the cause is Azure and I cannot test it because I have no access to Azure server. But at least it solves the same problem when linking to on-prem instance.

UPDATE

Try to not to use @provstr at all when creating linked server like this:

EXEC sp_addlinkedserver
   @server=N'LincServer',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'myAzureDB.database.windows.net',
   @catalog='MyDB';


is there a way to configure Azure SQL databases for high-insert scenarios? I'm working on a legacy application that uses SQL Server as a backing store. There's a portion of the app that does a ridiculous amount of inserts to a single table. Re-architecting this portion of the app represents a large time investment, probably 2 weeks for one developer. It works as is so we're, well happy isn't the right word, I guess we grudgingly accept it.

However, that portion of the app performs terribly when connected to an Azure database. I'm talking a sudden 4x increase in time that it takes to insert records. Each cycle inserts about 4000 rows into this table via a call to a stored proc (4000 calls; it's awful, I know). On a local SQL Server it will take about 5 minutes but on Azure that spikes up to >15 minutes and our users find that unacceptable.

The table has about 20 columns, mostly bits and ints. It has one primary key (int) and no other indexes. It has 2 foreign keys. Including the full CREATE script here seems excessive, but I can if needed.

What I'm wondering is if there's any way to "tune" an Azure database to handle large numbers of inserts better. Altering the code isn't a great option right now, but it is the right choice down the road.

Azure v12. We use S2 or S3 instances and don't see any high CPU or high DTU usage other than spikes for a few seconds here and there. Databases are all under 1GB.

Table compression is now supported in V12. You can compress the table to reduce the IO (as you mentioned CPU is not a problem). Try to batch the requests as each commit has overhead. You are just taling about 20 * 4 = 80 bytes per row and 80 * 4k = 320k data which is 0.5MB. This should be doable if tune the app in both S2 & S3. As you mentioned you are not hitting DTU limits, concurrent connections help too.


Despite STATISTICS is updated, Estimated and Actual Row count is not same I am executing a SELECT statement to retrieve 2 columns with filter on datetime column from a table having 27451373 rows.
declare @LastProcessedDate datetime='08-15-2016'

select Bill_Code,CLIENT_ID_6,u2_id
from [ODS_HRP].dbo.EMPLOYEE_BILL_VCHR_CODE_ALLOCATIONS
where time_stamp >= @LastProcessedDate

In execution plan I found big gap between Estimated and Actual Number of rows.
Hence I update statistics 2 time in a week, I updated all statistics of the table again and executed same SELECT statement.
But, it is still same.
Here is the execution plan.

And last stats update date.

Thanks

As others have pointed out the most immediate problem is your use of the local variable. SQL Server creates the query plan for that query before it assigns a value to the local variable. You have an unknown input filtered using the >= operator. The legacy cardinality estimator in SQL Server has a rule that treats this as a special case. The cardinality estimate will always be 30% of the number of rows in the table with a non-null value for the filter column.

I don't know many NULL values you have but if you do the math it almost works out exactly:

  27451373 * 0.3 = 8235411.9

There are a few ways around this problem. You can use an OPTION (RECOMPILE) hint. That will force SQL Server to never use a cached query plan and it will create the query plan after the variable has been assigned. Most of the time you will see the actual value for the variable in the plan. The unknown estimate of 30% will no longer be used. One exception that I know of is SQL Server may parameterize trivial queries, even those that use a RECOMPILE hint. In that case you may not see the variable value in the plan. I assume that this table is part of a larger query. If so you should not have that problem.

Other options are to hardcode your filter values into the query, use dynamic SQL, or to use a parameterized query. My preference is the RECOMPILE hint unless your Unless your query is executed so many times per day that you cannot afford to recompile the plan.

I also want to point out that it's relatively uncommon for the estimated number of rows to exactly match the actual number of rows. To get an exact match you typically need to work with a simple query and/or you need to get lucky with statistics.


Never changing the password for distributor_admin for local instance only? Per Does distributor_admin need sysadmin? distributor_admin needs SA and per Can the account name of 'distributor_admin' be changed? the name can not be changed.

The password is changed with sp_changedistributor_password See sp_changedistributor_password (Transact-SQL) for details.

  When the distribution database is contained on a local instance, a random password is generated and configured automatically. Source

What this all means is that when you have replication on a local instance (i.e. reporting database) no one knows what the password is.  It would seem to be impossible to compromise the password. Assuming you are watching for and reporting on failed login attempts, you would know if anyone was trying to brute force the password.  This leads to an argument where there is no good reason to actually change the password on a regular bases.  You might want to change it on day one, if you don't trust the random password generation, but even that could add more risk then it removes, as now the password has been seen and processed with human contact.

Is there a good reason to change the password for distributor_admin for local instance only?

I posted a general question at security.stackexchange If no one knows a password, is there a reason to change it?  as of this writing it has 3 answers.

The consensus is, two part

If you know that the password can not have been compromised there is no reason to change it.
But what if it was compromised and you don't know it.  By not changing you leave your system open forever.

Examples:

someone who was able to access the machine from an unknown vulnerability will have unlimited access to the system, forever.
If there is evidence, or at least a reasonable assumpion, that the secret has been compromised. In that case, change it immediately
If an admin who could recover (or change) the password either moves to another role or leaves the company, the password should be changed immediately thereafter.

  Is there a good reason to change the password for distributor_admin for local instance only?

Yes, if you can change it, someone else can change it and have eternal access. If you have a password changing policy 'distributor_admin' should be included in that policy.


Error when restoring up a database backup: Cannot find file ID 1 on device Below is the query that I am using to restore my database.

However, whenever I run it, i always get this error message:

  Msg 4038, Level 16, State 1, Line 1
  Cannot find file ID 1 on device 'D:\DB_Backup\MMS.BAK'.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

This is my query:

RESTORE DATABASE METERIAL_MANAGEMENT_2
FROM DISK = 'D:\DB_Backup\MMS.BAK'
WITH
    MOVE 'METERIAL_MANAGEMENT' TO 'D:\DATA\METERIAL_MANAGEMENT_2.mdf',
    MOVE 'METERIAL_MANAGEMENT_log' TO 'D:\DATA\METERIAL_MANAGEMENT_2_Log.ldf'

Your code is for Restoring a database not to create a backup file. you have to use the following code:

BACKUP DATABASE METERIAL_MANAGEMENT_2
TO DISK = 'D:\DB_Backup\MMS.BAK'

Read more about Creating a full backup using T-SQL in the following TechNet article


MySQL Error1822 failed to add the foreign key constraint Im trying to have the City and Postal Code depend on eachother, but i get this error time and time again.
MySQL Error1822 failed to add the foreign key constraint. Missing index for constraint 'Student_address_ibfk_1' in the referenced table 'Student_Information'
################
CREATE TABLE IF NOT EXISTS Student_Information(

    Attendance_ID INT NOT NULL AUTO_INCREMENT,
    Student_ID INT NOT NULL,
    Student_name VARCHAR(99) NOT NULL,
    Phone_number INT NOT NULL,
    Email VARCHAR(99) NOT NULL,
    City VARCHAR(99) NOT NULL,

    PRIMARY KEY(Attendance_ID, Student_ID, City)

);

CREATE TABLE IF NOT EXISTS Post_Address (

    Student_ID INT NOT NULL,
    Street_Address VARCHAR(99) NOT NULL,
    Postal_Code INT NOT NULL,
    City VARCHAR(99) NOT NULL,

    PRIMARY KEY(Student_ID, City)

);

CREATE TABLE IF NOT EXISTS Attendance(

    Attendance_ID INT NOT NULL AUTO_INCREMENT,
    4_jan INT NOT NULL,
    6_feb INT NOT NULL,
    7_mar INT NOT NULL,
    18_apr INT NOT NULL,
    20_may INT NOT NULL,
    21_jun INT NOT NULL,
    10_jul INT NOT NULL,
    30_aug INT NOT NULL,
    16_oct INT NOT NULL,
    12_nov INT NOT NULL,
    15_des INT NOT NULL,

    PRIMARY KEY(Attendance_ID)
);

I made tables and I used index to be able to make a relationship between my tables, then I added a foreign key (the relationship is one-to-many).
FYI, you don't need to repeat the column in a different table(city). You can save the data in one table and you get the data by joining them. I kept the city on both tables, but you can remove it from one of your tables.

CREATE TABLE `Attendance` (
  `id` int(11) NOT NULL PRIMARY KEY  AUTO_INCREMENT,
  `Attendance_ID` int(11) NOT NULL,
  `4_jan` int(11) NOT NULL,
  `6_feb` int(11) NOT NULL,
  `7_mar` int(11) NOT NULL,
  `18_apr` int(11) NOT NULL,
  `20_may` int(11) NOT NULL,
  `21_jun` int(11) NOT NULL,
  `10_jul` int(11) NOT NULL,
  `30_aug` int(11) NOT NULL,
  `16_oct` int(11) NOT NULL,
  `12_nov` int(11) NOT NULL,
  `15_des` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Post_Address` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Student_ID` int(11) NOT NULL,
  `Street_Address` varchar(99) NOT NULL,
  `Postal_Code` int(11) NOT NULL,
  `City` varchar(99) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Student_Information` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  `Attendance_ID` int(11) NOT NULL,
  `Student_ID` int(11) NOT NULL,
  `Student_name` varchar(99) NOT NULL,
  `Phone_number` int(11) NOT NULL,
  `Email` varchar(99) NOT NULL,
  `City` varchar(99) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Attendance`
  ADD KEY `Attendance_ID` (`Attendance_ID`);

ALTER TABLE `Post_Address`
  ADD KEY `Student_ID` (`Student_ID`);

ALTER TABLE `Student_Information`
  ADD KEY `Attendance_ID` (`Attendance_ID`),
  ADD KEY `Student_ID` (`Student_ID`);

ALTER TABLE `Attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`Attendance_ID`) REFERENCES `Student_Information` (`Attendance_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `Post_Address`
  ADD CONSTRAINT `post_address_ibfk_1` FOREIGN KEY (`Student_ID`) REFERENCES `Student_Information` (`Student_ID`) ON DELETE CASCADE ON UPDATE CASCADE;


Managing concurrency when using SELECT-UPDATE pattern Let's say you have the following code (please ignore that it's awful):

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else

To my eye, this is NOT managing concurrency properly. Just because you have a transaction doesn't mean someone else won't read the same value that you did before you get to your update statement.

Now, leaving the code as is (I realize this is better handled as a single statement or even better using an autoincrement/identity column) what are sure ways to make it handle concurrency properly and prevent race conditions that allow two clients to get the same id value?

I'm pretty sure that adding a WITH (UPDLOCK, HOLDLOCK) to the SELECT will do the trick. The SERIALIZABLE transaction isolation level would seems to work as well since it denies anyone else to read what you did until the tran is over (UPDATE: this is false. See Martin's answer). Is that true? Will they both work equally well? Is one preferred over the other?

Imagine doing something more legitimate than an ID update--some calculation based on a read that you need to update. There could be many tables involved, some of which you'll write to and others you won't. What is the best practice here?

Having written this question, I think the lock hints are better because then you are only locking the tables you need, but I'd appreciate anyone's input.

P.S. And no, I don't know the best answer and really do want to get a better understanding! :)

Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.

Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.

The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.


Restore data from remote server using ".sql" file I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file (6.7GB) it's taking forever.

Is there any way I could execute the restore command on remote server and tunnel the output to my local machine DB ?

Thanks for help !

You should gzip the file.

gzip DataFile.sql

Then, download it to the local machine.

Once it is on the local machine, run the following:

gzip -d < DataFile.sql.gz | mysql -uroot -p... ...

If you would rather import through the tunnel, try this on the remote server:

mysql -h<IP of your machine> -uroot -p... < DataFile.sql

If you local Linux machine has a firewall, run this

service iptables stop

then run the import. Finally, run this on your local box and put up the firewall

service iptables start


Is there any database modeling software which provides a real-time (and editable) ERD, which I can manipulate alongside a traditional GUI? In the web development world, the designer can write his code and watch the webpage materialize in real time. Why is this such a convoluted process when it comes to the database world? In MySQL Workbench, synchronizing your model with the the source is such a pain. Trying the same thing within Oracle SQL Developer is also not any better. Why is this so? I understand that this may have something to do with the delineation between the logical model, and the physical model, but if someone can please shed some light on this.

The two worlds differ because of "state". A web page has none; a database is nothing but.

A web page is rendered and it is over with. Yes, a user can click here and there, type values and widgets may appear and go again, but nothing permanent has changed as far as the enterprise is concerned. Should the rendering computer fail no inconsistency can be introduced. The page can be completely re-created as it was with a single call to the server. A change to, say, some CSS does not have to account for every other piece of styling that has been applied to this page throughout history.

A database, however, must maintain and protect state. If the DB server fails it must (should!) guarantee the integrity of the data it has acknowleded as successfully written. Rebuilding the current data from all past input would be time-consuming, assuming the previous files (key strokes?) exist and can be processed in the identical sequence. A change to a table's schema must take account of existing data's structure and meaning. Declared referential integrity (DRI) constraints, which further guarantee's data quality, must be honoured.

NoSQL tries to side step this issue by allowing dynamic schema i.e. no to rows need have the same columns, data types or meaning. This allows very rapid evolution of the applicaiton without hinderance from database upgrade tasks. The responsibility for ensuring the completeness, correctness and consistency of the data is now in the hands of the application developer with little or no help from the persistence tool. Being able to Deal with all the existing "state" in the previous format(s) is now her responsibility, too.

The difference between logical and physical models is a valid one, too. In my experience, however, they tend to be very similar. The mapping from logical to physical must be explicit within the modelling tools (if it is to generate DDL) so could be made transpartent to code written against the logical model. I don't think this is a great impediment to the world you hope for.

I have used CASE tools where the logical data model can be entered into the tool, procedural logic written against that model immediately, and a functioning system generated at the touch of a button. It was a great tool to work with. It was, however, expensive and seems to have declined.

There are tools which will take a "desired state" model, compare it to an "existing state" database and generate the delta DDL. This can be integrated into a continuous build & deploy workflow. Having seen a generated change script which was so awful it would take in excess of three days to complete, however, I'd recommend reviewing manually before submitting.


Adding Last 12 Months to a row in a table (SQL Server) I want to add the last 12 months to a SQL Server table, something like the following:

FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15

Please can someone help with with this?

Thanks

EDIT: Table schema is as follows:

Department   Month12   Month11   Month10   Month9   Month8   Month7   Month6   Month5   Month4   Month3   Month2   Month1
----------- --------- --------- --------- -------- -------- -------- -------- -------- -------- -------- -------- -------

Perhaps you want something like this?

INSERT INTO dbo.Test (
    Department
    , Month12
    , Month11
    , Month10
    , Month9
    , Month8
    , Month7
    , Month6
    , Month5
    , Month4
    , Month3
    , Month2
    , Month1
    )
VALUES ('Test'
    , DATEADD(MONTH, -12, GETDATE())
    , DATEADD(MONTH, -11, GETDATE())
    , DATEADD(MONTH, -10, GETDATE())
    , DATEADD(MONTH, -9, GETDATE())
    , DATEADD(MONTH, -8, GETDATE())
    , DATEADD(MONTH, -7, GETDATE())
    , DATEADD(MONTH, -6, GETDATE())
    , DATEADD(MONTH, -5, GETDATE())
    , DATEADD(MONTH, -4, GETDATE())
    , DATEADD(MONTH, -3, GETDATE())
    , DATEADD(MONTH, -2, GETDATE())
    , DATEADD(MONTH, -1, GETDATE())
);

This assumes several things.

You have DATE (or perhaps DATETIME, etc) as the data type for the 12 columns for each month.
You want each month to reflect the current day.  For instance, since it is currently Jan 2nd, this code would insert 2014-01-02, 2014-02-02, ... 2014-12-02 into the table.

If you are using SQL Server 2012 and above, you can modify this:

DATEADD(MONTH, -1, GETDATE())

to this:

FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MMM-yyyy')

To get the date as Jan-2014.  There are also a lot of other ways that are probably more efficient.


Enabling TDE without breaking existing mirror I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.

There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.

I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.

Is enabling TDE across an existing mirrored SQL Server setup possible?

From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.


use an Index correctly to optimize queries I have an SQL database with millions of records and when I'm querying the data like

select * from ActCosts where ScenarioID= 456

The tables has 1,323,718 rows  and it gives me like 50,000+  rows which is surprisingly taking more than 3 minutes. So I am now thinking of how I could improve this performance. I find one way is to create an index on the column "SomeID". I have created this index but the query takes the same time

Execution plan

Index Script

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost]
(
    [ScenarioID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed.

As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to return from the query would need to be retrieved from the Clustered Index. On 50,000+ rows, this simply wouldn't be efficient, hence the index is ignored.

Firstly, you need to ask yourself if you really need ALL columns to be returned. I somehow doubt you do (the ID columns for example), but I may be wrong.  In any case, you should always define the column list rather than using *.

SELECT
 ActID,
 ActCostTypeID,
 Description,
 Cost,
 <rest_of_columns...>
FROM dbo.ActCosts
WHERE ScenarioID = 456;

Notes:
1. Don't avoid the schema prefix
2. Avoid using SELECT *

You then need to include the extra columns returned in IX_ActCost_ScenarioID:

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO


How to use a relational database to store all users who belong to a group, and all groups which a user belongs to? This is a beginner question:

A user can belong to many groups, and a group can contain many users.

Let's say the two tables look something like this:

user_id
user_name
user_email

group_id
group_title
group_description

Where and how do I store all of the users which belong to a group and all the groups a user belongs to?

I can't imagine the groups table having a users field which contains comma separated user IDs being an acceptable solution. I think I get the basic concept of relational databases, but I'm not sure of the implementation. I get the one-to-one relationship, but I'm struggling with one-to-many. Is what I'm dealing with in this example two separate one-to-many relationships?

You want three tables for this:

Users with a row per user and whatever details you need
Groups with a row per group and whatever details you need
UsersGroups with a unique combination of UserId, GroupId that only keeps track of relationships

This will let you have as many combinations as you like.


SSRS: how to handle unavailability of service when datawarehouse is being rebuilt We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly.

During this time, we restrict access to the application, this way the database is not altered during integration.

1/ can integration be done on Snapshot of the database, so that we can keep the app up and running?

2/ general question: how to handle the period when datawarehouse is rebuilt? Access to report should be forbidden until all tables are refilled with fresh data. Does exist a mecanism of balance between 2 datawarehouses ? It could be done with parameterised datasources in SSRS instead of shared datasource, but it seems to me like gasworks.

Environment: SQL Server 2005 and we plan to migrate to 2012.

Database Snapshots are read-only, so you couldn't run read/write ETL processes against the snapshot. However, you could point reports, end-user adhoc queries, etc. at the snapshot. This would allow you to run the ETL processes against the live database without interfering with user reporting. A new snapshot would need to be created after ETL processes complete. Depending on available storage, you could store multiple snapshot copies.
Regarding the data warehouse (DW) refresh, you may want to implement incremental load processes. Instead of mass truncate/insert operations, compare the staged source data with what already exists in the DW; Only insert/update the data that needs to change. This will give you a huge benefit in terms of resources used and time required for the ETL processes to complete.


SQLserver WaitType : EXECUTION_PIPE_EVENT_INTERNAL One of our production Microsoft SQL Server 2014 (SP1-CU5)
 experiencing performance issue so I ran the ShortPeriodWaitStats.sql script from Paul S. Randal, SQLskills.com
and the top WaitType is EXECUTION_PIPE_EVENT_INTERNAL it's 50% of all the WaitTypes !
Someone knows something about it ? any tips to track the core pb ?

According to MSDN, EXECUTION_PIPE_EVENT_INTERNAL :

  Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.

The inference here is this is an internal wait type used to denote actions occurring inside the batch execution engine.  Since this is an internal engine wait, the only way you'll be able to accurately diagnose if this is an issue would be to raise it directly with Microsoft.  Alternately, if you are @BobWard or @PaulWhite, you could dig up a Windows Debugger, recreate the issue in a non-production environment, an stop the code in the middle of that wait type.  For the rest of us mere mortals, this deserves a call to Microsoft if you are truly worried about it.


How to three way inner join to three way outer join in MySQL? We are currently joining three MySQL tables based on the summation of a common key within each table:

select a.common_key, a.val, b.val, c.val from
  (select common_key, sum(val) val from table_a group by common_key) as a,
  (select common_key, sum(val) val from table_b group by common_key) as b,
  (select common_key, sum(val) val from table_c group by common_key) as c
where a.common_key=b.common_key and a.common_key=c.common_key;

This works fine, but now we want to include in our results values from a, b or c for which there is no matching values in the other tables.

In other words, we want to go from a three way inner join to a three way outer join, but we are having a hard time figuring out both the syntax and
also how much harder a processing task it would be for our database.

As requested, here is what we get from SHOW CREATE TABLE table_a\G:

+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| table_a | CREATE TABLE `table_a` (
  `common_key` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------------------------------------------------

If you want to include in the results values from any of a, b or c for which there is no matching values in the other tables, you can first find all distinct values for the three tables and then LEFT JOIN to the derived tables you already have:

select
    d.common_key,
    a.val, b.val, c.val
from
    ( select common_key from table_a
      union distinct
      select common_key from table_b
      union distinct
      select common_key from table_c
    ) as d
  left join
      (select common_key, sum(val) val from table_a group by common_key) as a
    on d.common_key = a.common_key
  left join
      (select common_key, sum(val) val from table_b group by common_key) as b
    on d.common_key = b.common_key
  left join
      (select common_key, sum(val) val from table_c group by common_key) as c
    on d.common_key = c.common_key
 ;


Type conversion with default fallback value In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty).

In "pseudo-SQL", this would be something like this:

SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;

I've tried to simplify the problem to detect an empty string using these two queries:

SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ?  END);

For example, if the parameter is '', this is equivalent to this:

SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE ''  END);

Both fail with ERROR: invalid input syntax for type timestamp with time zone: ""
It makes sense, but it implies that the ELSE block is evaluated (or at least that its types are resolved) whether or not the CASE condition is true. The following works, but what I'd like the CASE (or similar) condition to handle, is precisely the case when it's not a valid date.

SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;

The closest I got to a working solution is this:

SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));

In this case, if the parameter is '', it returns the current date, otherwise, it returns the date passed in the string parameter (provided it can be turned into a valid date).

What I would like is to go a step further and make anything that cannot be turned into a DATE use the current date. I guess this could be done using a custom PL/pgSQL function that would trap this error, but can this be done without such a function, in "plain" SQL (or at least using the existing PostgreSQL functions)?

You can't handle SQL exceptions within an SQL statement like you can from within PL/pgSQL.

You must write a custom function as you suggest, for example:

create function is_valid_date(text) returns boolean language plpgsql immutable as $$
begin
  return case when $1::date is null then false else true end;
exception when others then
  return false;
end;$$;

test:

with w as (select 'asdsad'::text dt1, '2011-12-01'::text dt2, null::text dt3)
select case when is_valid_date(dt1) then dt1::date else current_date end d1,
       case when is_valid_date(dt2) then dt2::date else current_date end d2,
       case when is_valid_date(dt3) then dt3::date else current_date end d3
from w;

     d1     |     d2     |     d3
------------+------------+------------
 2011-12-06 | 2011-12-01 | 2011-12-06


Will FILLFACTOR = 99 slow down my SQL Server? I have read this today:

  Closely related to index rebuilding is the fillfactor. When you create
  a new index, or rebuild an existing index, you can specify a
  fillfactor, which is the amount the data pages in the index are filled
  when they are created. A fillfactor of 100 means that each index page
  is 100% full, a fillfactor of 50% means each index page is 50% full.

  If you create a clustered index (on a non-monotonically ascending
  column) that has a fillfactor of 100, that means that each time a
  record is inserted (or perhaps updated), page splits will occur
  because there is no room for the data in the existing pages. Numerous
  page splits can slow down SQL Server’s performance.

I have noticed that we have a database in our company with a table Department with departmentName as varchar(50) not null. We can have around 100 departments in this table.

We also have a non clustered index as follows:

CREATE NONCLUSTERED INDEX [DEPARTMENT_NAME]
    ON [dbo].[Department]([departmentName] ASC)
    WITH (FILLFACTOR = 99, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON,
          PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
          IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
          ONLINE = OFF, MAXDOP = 0);

So, I am wandering if this FILLFACTOR = 99 would slow down our database. What does the author mean with "page splits occur when there is no room for data"?

Fill factor is critical in fragmentation

  What does the author mean with "page splits occur when there is no room for data"?

There are basically two types of splits good page split or sequential page split which occurs when leaf level of index page is full and when new record is inserted page split occurs which results in allocation of new page and data is written in sequential order because new page is added at last of all pages.

The other one is bad page split or non sequential page split which occurs due to insert or update operation on a page resulting in page split in between. What happens is when record is updated a space for new record is created and this might splits page and move some of record on new page now this new page would be just after page that was updated hence severely creating a mismatch in order by which Logical clustering keys are arranged and how data pages are arranged. In fact depending on how fast new page can be allocated for page split it can also cause performance issue. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. This article aptly demonstrates how page split can occur. You can also read this article by Paul about how to see bad page splits. Both of these use undocumented commands if you have SQL Server 2012 and above you can use Extended events trace to track nasty page splits

One more drawback associated is when huge number of page splits occur they could saturate I/O by utilizing it. Consider a scenario where update causes page split now one I/O would be required for update to index key one for update to page(normally) it would also require additional I/O for new page addition and updating index after new page is created, on a busy system with not so upto date hardware this could cause performance issue

There is other way using DMV to find number of page splits happening in database but it wont tell you which one is good and which one is bad

--Script to check page split for index
SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Please let me know if you need further explanation


Blocking process inputbuf is empty I was trying to capture blocking query using blocked process report past 1 hour as this link
https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/

I can get the blocked process query using sqlhandle but for blocking process query i cannot get it because no sqlhandle and inputbuf doesnt have any SQL statement.

Any idea? This is first time i m using this blocked process report.

I use this query to monitor all running SPID's and the column Blocked shows you the blocking session ID. Try it

SELECT s.STATUS
    ,db_name(r.database_id) AS DB
    ,s.session_id AS SPID
    ,r.blocking_session_id AS blocked
    ,s.login_name AS userN
    ,s.host_name AS pc
    ,s.program_name AS app
    ,t.TEXT
    ,spu.internal_objects_alloc_page_count AS Pages_Allocated
    ,s.cpu_time AS CPU_T
    ,s.memory_usage AS Memory
    ,s.total_scheduled_time AS Tot_Sch_T
    ,s.total_elapsed_time AS Tot_Ela_T
    ,s.reads
    ,s.writes
    ,s.logical_reads AS Log_Read
    ,s.row_count
    ,s.last_request_start_time AS Last_Strt_T
    ,s.last_request_end_time AS End_T
    ,r.open_transaction_count AS OPENTRAN
    ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, s.last_request_end_time), 0), 108) AS TOTAL_TIME
    ,r.percent_complete
    ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage spu ON s.session_id = spu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
    AND s.STATUS LIKE 'RUNNING'
ORDER BY s.total_elapsed_time DESC


Postgresql cluster over nfs Can I create a HA postreges cluster using two different nodes via NFS?

According to the postgresql 9.4 official doc this isn't a good idea but I don't find any other info on the web.

Short answer: NO!.

Longer answer: If you use synchronous NFS and you have robust STONITH / fencing, then with great caution you could run a shared-storage failover cluster over NFS without horribly corrupting your data. However, it won't do you any good for failure modes where the data is affected by the problem.

Shared-access SAS/SCSI, DRBD, shared-access SAN storage (iSCSI etc), or similar block-level sharing is slightly saner than using NFS, but still not safe.

If you don't already know what STONITH or fencing means, then see "short answer" above.

Use replication. Really.


mysqltuner: "MySQL started within last 24 hours - recommendations may be inaccurate." Why? I'm new to db management and db tweaking. I'm using mysqltuner to get recommendations for how to improve db performance.

Mysql issues a warning message along with its recommendations:
"MySQL started within last 24 hours - recommendations may be inaccurate."
It is true that I started MySQL within the last 24 hours, but why does this matter? What does the database do over a 24 hour period that will change the results of mysqltuner?

It is a basic caution that a MySQL instance running under 24 hours (1 day) may not have seen the entire query workload and thus the recommendations might be affected.

Of course, 24 hours doesn't guarantee the entire workload either, as you might have scripts that run weekly or monthly.

On a separate note, I would caution you on taking the advice of mysqltuner too strictly. Specifically don't make any changes that you don't fully understand just because the script says to do so.


View crossing multiple databases, but a query only using one is slow Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.

Please note that each database can be split using the INSTANCE_NO
Also, each table has only about 100 rows.

ALTER VIEW [dbo].[VI_MULTI_DUMMY]  AS
SELECT 1 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM [HUB_1].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 2 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_2].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 4 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_4].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

I have a huge performance problem for a query that only accesses one database:

--> 0 sec
SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] , ... [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

--> 5-7 sec
select T.* from VI_MULTI_DUMMY AS T  WITH(NOLOCK) where  INSTANCE_NO = 3 OPTION(RECOMPILE)

I've used RECOMPILE, NOLOCK, ... and I'm out of idea's, who can help me?

EDIT : SOLUTION
A solution as stupid as the problem :
Go to each database it's properties--> Options--> Automatic.
Set 'Auto Close' to false

Never mind,

I just went with sp_executesql

Performance issue are gone, readability gone

SET @myInst = '[db2Name]'
SET @sqlReplaced = REPLACE('myQry','{@myInst}',@myInst)

EXECUTE sp_executesql
@statement                  = @sqlReplaced,
@ParmDefinition             = @ParmDefinition,

@clientNo                   = @clientNo,
@lastTransactionDate                = @lastTransactionDate,
@msInstanceNo               = @msInstanceNo,
@dateFrom                   = @dateFrom ,
@dateUntil                  = @dateUntil


Impact of partition scan on large and small data I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime.

While querying from this table i.e.

Select * from SampleTable where Convert(Date,Date) >= Convert(Date,GETDATE())-1

... I convert Date column to Date datatype. This makes my search predicate seek on all partitions.

However, changing the query to:

Select * from SampleTable where Date >= Convert(Date,GETDATE())-1

... causes the search predicated to seek only on partitions which qualify.

I wanted to understand the impact of the seek operations. Will the impact be linear or exponential when my data/date range increases?

The index is always being used.

Think of it as different queries. How much it cost to navigate a B-Tree to find zero rows? That is what happens for those "extra partitions". Those that don't contain any data for your range and which are skipped with your second query alternative. If you don't have too many partitions, then this extra cost will be very low. But as number of partitions increase, so will the overhead (especially relative overhead if the selectivity for the range is high).