Oneway sync of two SQL Servers via FTP I need to replicate data from a ‘master’ SQL Server out to a number of replicas. Luckily the data only flows from the ‘master’ to the replicas.

I need to replicate both schema and data. Data size approx. 1 TB. I have full control of ‘master’ and replica servers, but they cannot communicate directly – only via a file share.

I’ve looked at Merge Replication and MS Sync Framework.

Merge Replication requires direct communication between the SQL Servers.
MS Sync Framework does not handle schema changes, but that can be solved with SQL Server Data Tool and DACPAC. Unfortunately I have to create a SQL Server to file share as partial participant and then another the other way from file share to SQL Server. Is there any way to get around this?

I would like to support SQL Server version agnostic replication e.g. ‘master’ running SQL Server 2008 R2 and replicas running SQL Server 2008 R2+ (E.g. SQL Server 2012).

Any other implementation suggestions?

We are using a merge replication topology between our servers where:

Initial snapshot is made available to subscribers through ftp
further data exchange (in our case, merge sync between servers) is done through web replication

Here is a script I once proposed to launch such a replication. You will notice that one of the sp_addmergepullsubscription_agent parameters, @use_ftp, is set to True. The @internet_url parameter sets the https adress used for replication. There are other options allowing the use of an alternate snapshot location and/or its compaction (which could make sense when you are talking about a 1To database ...)

Hope it helps


Optimize slow COUNT in MySQL subquery I have the following query that takes minutes to complete:

SELECT DISTINCT Designation, Model, (
    SELECT COUNT( Equipment.EquipmentID )
    FROM Equipment
    INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID
    LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial
    WHERE Equipment.ItemID = i.ItemID
    AND EquipmentDesignation_Vw.Designation = ed.Designation
        AND EquipmentDesignation_Vw.ArmoryID IN 2
    AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))
) AS Total
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
    AND ed.ArmoryID IN 2
    AND i.ItemCategoryID = 7

This is because of the nested SELECT COUNT subquery. Without it, it runs blazing fast.

I made sure to add indexes on all the columns used to perform the JOINS.

If I extract the COUNT subquery to its own query, it is also really fast:

SELECT i.ItemID, COUNT( Equipment.EquipmentID )
FROM Equipment
INNER JOIN Item i on i.ItemID = Equipment.ItemID
INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID
LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial
WHERE Equipment.ItemID = i.ItemID AND EquipmentDesignation_Vw.Designation = 'Issue'
AND EquipmentDesignation_Vw.ArmoryID IN 2
AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))
GROUP BY i.ItemID

I tried to remove the subquery and calling COUNT directly in the SELECT, but to get the same results as the original query, I need to add the AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL )) condition to the main query. By doing so, I am not sure that I will not affect the original results:

SELECT DISTINCT Designation, Model, COUNT( e.EquipmentID )
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation =  'Issue'
AND ed.ArmoryID  IN 2
AND ((ei.IssueLocationID is NULL) OR (ei.IssueLocationID is not NULL AND ei.ReturnLocationID is not NULL ))
GROUP BY i.ItemID

This is quite fast as well, and seems to return the same results as the original query but I am not certain about this.

So is there a way to get rid of this subquery, or at least to make it more performant?

This is the output of EXPLAIN EXTENDED for the original query

Thank you. I am not a DBA, I spent hours on this and feel a bit stuck here.

Thanks to @Akine I did this:

SELECT DISTINCT Designation, Model,
COUNT(CASE WHEN ((ei.IssueLocationID IS NULL) OR (ei.IssueLocationID IS NOT NULL AND ei.ReturnLocationID IS NOT NULL )) THEN e.EquipmentID ELSE NULL END) AS Total
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
INNER JOIN Equipment e ON e.ItemID = i.ItemID
INNER JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
AND ed.ArmoryID  IN 2
GROUP BY i.ItemID


Dataguard: preventing logical standby to loose track after flashback database on primary We have 2 Oracle databases; a primary and a logical standby, configured with Dataguard. Whenever we have an application rollout on the primary, we create a restore point in case anything goes wrong.

Last Tuesday something went wrong and we had to restore the primary database to its restore point.

That caused the logical standby database to stop applying archive logs (which makes sense, because the primary database was basically reverted in time, leaving the logical standby in an undefined state).

How could we have prevented this situation?

I imagine this scenario:

Stop SQL Apply on logical standby
Create restore point on primary
Do stuff on primary, error occurs
Flashback primary database to restore point
Open primary database
Start SQL Apply on logical standby

Would this work? Would it allow the logical standby to continue applying without getting diverged from the primary database?

I would change the whole process of deploying.

Stop SQL Apply.
Create restore point on standby.
Deploy to standby
When testing is complete, flashback standby.
Start SQL Apply.
Safe to deploy on primary.


How to convert a Microsoft SQL Server CE file (.sdf) to Postgres 9.6 /pgAdmin 4? I have a database file data.sdf that I want to import into PgAdmin 4. I've considered using pgloader but the file format does not seem to be accepted. Does anybody have any hints on how I might go about making this work?

You can use my tools to script data and schema as SQL Server compatible scripts, and if required tweak those to run with Postgress: http://erikej.github.io/SqlCeToolbox/


PostgreSQL relation size does not sum up I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:

=> SELECT pg_size_pretty(pg_total_relation_size('data'));
157 GB

This seemed a bit large, so I tried to sum up the data:

=> SELECT pg_size_pretty(pg_relation_size('data'));
19 GB

With the indices:

SELECT pg_size_pretty(pg_relation_size('data_pkey'));
757 MB
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_merged_idx'));
854 MB
SELECT pg_size_pretty(pg_relation_size('data_owner_idx'));
794 MB
SELECT pg_size_pretty(pg_relation_size('data_session_format_idx'));
779 MB

The summation of the data and indices size is around 26 GB, but the total relation size is near 160 GB. The table was restored from dump just a while ago, and no writes were made since.

What's the explanation for this difference?
Any way to reduce wasted disk space? Will clustering help?

Does table have any variable-width columns?
If the answer is yes, you are probably missed toast tables (pg_toast_xxx). You need to find their names (not sure how to do it, need to google it) and calculate their size as well.

To reduce space (for example after deleting bunch of rows) execlute VACUUM FULL  and reindex table after it (to prevent index fragmentation).


How much do foreign keys affect performance? I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign keys specified. Either the application takes care of all the business logic by itself, or the installation is wrong.

How much do foreign keys affect performance? Or do they exist only for business logic? Are there any cases when not having foreign keys are an advantage? (e.g. this system supports SQL Server, Oracle and DB2.)

The link provided in bummi's comment has useful information.  Depending on what you're doing, FKs can improve performance because SQL Server's query processor can rely on there being matching data in a table.

In what context are you asking your question?  Do you have a performance problem already, or are you being cautious?

It's better to add in the FKs and then disable them later (don't remove them, just disable) then to never have them at all.


Why am I getting an error if I am trying to modify data type? I am facing an error when I passed the following syntax in MySQL:
ALTER TABLE store MODIFY (DOB char(40)) ;

I am attaching a screenshot too because then you can see that the column DOB is having all rows NULL in it. Even after that Data type isn't changing.

See in yellow is my syntax. Earlier, DOB was an integer. See all the rows are NULL. Then why the error!
Please, help even if you feel it is a silly question. I am just a school going kid who has this MySQL as my subject and I don't have any tutor too.

In your image you have far to many parenthesis
ALTER TABLE store MODIFY DOB char(40);

The parenthesis you put around column name and type is not valid.


How to migrate data from SQL database to MySQL including flat files? One of my friends asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now, my friend asked me to do everything in PHP and he want to migrate all the data which he has in SQL database to MySQL database. I can do it. text can be migrated easily but the files which are saved in SQL database can be migrated to MySQL as flat files. Is it possible?

According to this you should be able to store files in a blob or text field.  I don't actually work with mysql so I googled "mysql datatypes".


Adding checkboxes in SSRS reports to filter values on a table I am trying to create a report that would list records but I need some way to include or exclude some values probably by using some form of option like a checkbox or a custom element in SSRS.

For example I'd have a table with the following data:

|---------------------|------------------|
|         Name        |     Category     |
|---------------------|------------------|
|         John        |     Project      |
|---------------------|------------------|
|          Bob        |      Support     |
|---------------------|------------------|

Is it possible to have checkboxes on the side that can filter out let's say just rows with "Project" as the category?

No. We do not have Check Box with Event Trigger and etc but you can use Hacks.

First Option is the most straight forward way of doing things. but for you to implement something like Check Box Routine you would need to hide and show check boxes and tables that have data.

You can Use parameter in you report, where you would bind your
category to the parameter and when user choose it then you would use
the parameter on the Filter option of the table.

Use with Avalable Values (default: Optional)

Then
Use The parameter on Table Filter

So user can Filter the Table Category.

You can use Hidden and Initial Toggle State properties with some Expression to hide and Show base on some sort of Toggle. (Not preferred)

I used Two Table, inside Another Table. set ToggleItem of Each ,to the Cell that would do the Toggle.

Result:

Design:

Row option:


Privilege to select only user's own tuple only in mysql I create a table named person which columns such as name, sex, occupation and so on.
I want to give each user select grant over that user’s own tuple only. I assume that each users use their own name as their user ID.
So I created view as

  create view mine as select * from person where name=current_user;

And I give privilege all user

  grant select on mine to public;

But it does not work.
How to grant privilege to all user in mysql?

And, in the person table, name column  store only just name. But current_user replies name@localhost. So it does not match. How to solve that?

You may probably make use of LEFT and INSTR built in functions to strip out the host name from the user@host.

CREATE VIEW mine
AS
SELECT * FROM person
 WHERE `name`=LEFT(CURRENT_USER(),INSTR(current_user(), '@')-1);


How to SELECT most common (viral) from a table and LIMIT results for pagination? Sorry if it's a dubplicate but I can't find an answer to the question.

Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is also a timestamp column and can be liked by other users where a like is saved in likes table with the user id and secret id.

articles table:

+----+---------+--------------------+
| id | article |       timestamp    |
+----+---------+--------------------+
| 1  | A       |2019-02-10 22:19:02 |
| 2  | B       |2019-02-9 22:20:28  |
| 3  | C       |2019-02-9 22:21:10  |
| 4  | D       |2019-02-8 18:20:10  |
| 5  | E       |2019-02-8 13:25:04  |
+----+---------+--------------------+

likes table:

+-----------+--------+
| articleid | userid |
+-----------+--------+
| 1         | 1      |
| 1         | 3      |
| 1         | 5      |
| 1         | 3      |
| 2         | 1      |
| 3         | 2      |
| 3         | 3      |
| 3         | 4      |
| 3         | 5      |
| 3         | 2      |
| 4         | 4      |
+-----------+--------+

I'm trying to do the pagination of the most liked articles in the shortest time, as for example in this case article A is the top of the order because it got more likes in less time,   How would I do that and at the same time LIMIT the result to do the pagination?
My try was:

SELECT * FROM articles, (SELECT count(*) FROM likes WHERE articleid = id) as likes
GROUP BY articles
OREDER BY likes
LIMIT 10

I would do it in php without LIMIT but at the same time I want to repeat the query to get the next articles for the second page in the same way and I have no idea how to do that.

Thanks in advance!

ORDER BY likes DESC should be used to get the highest likes first.

The second page is going to have LIMIT 10,10.

With a likes table as it is, its going to be hard to get quick performance out of this. Getting the number of likes into the main articles table would help get a quicker results of this if they are used often. If you need to use both perhaps an INSERT trigger on the likes table.

Once you have a likes column in articles, it will need an index on it.

Then your query becomes simply:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10

The second page way is:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10,10

If you got too many pages, you'll eventually start to be slow down as getting 10 pages at an offset needs to walk down to that offset first.


Full text search on larger text rows I am wondering what is the best practice to use the right query in my case.

The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they often run in a timeout or throw an error:

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.

So I am not sure, if I did it right and my table looks like this and has over 10.000 entries:

  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 doc       | bytea         | not null
 textdoc   | text          | not null

So I have the document (PDF or DOCX) and I give it to the converter which makes a huge string out of the documents and put it into the textdoc. A document can have 150 pages or 40.000 words(strings). So in each textdoc can be a huge amount of words. And when I have over 10.000 entries in the database and each of this entry has a huge amount of words in textdoc, how can I speed this up.

So my actual query looks (simplified) like this:

SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'

When I am searching the web, the standard message is don't use ILIKE. I used pg_trgmand it wasn't really faster. It was all about three minutes for a query.

Required information:

Database: "PostgreSQL 10.4 on x86_64-pc-linux-gnu, (Red Hat), 64-bit"

Analyze (hope I did it right).. I run:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'

Here is the output: https://explain.depesz.com/s/wqLh

Another question is, the notice of the 2047 characters: Does this mean, that a the text in the column textdoc is over 2047 or a string in the textdoc is over 2047 characters?

The NOTICE means that textdoc contains at least one word within it that is more than 2047 characters.  The definition of a word depends on what configuration you use for your ts_config.  Words are generally strings of characters separated by spaces or certain punctuation. Hyphenated words will generally be emitted as each separate word, plus the entire hyphenated phrase as an extra "word", at least with the ts_configs that I am familiar with. You can use the ts_debug function to find out how it actually breaks up the string emitted by your specific converter using your chosen ts_config.

select alias, description, token from mytable cross join ts_debug('english',textdoc)
    where length(token)>2047;

(On second thought, ts_lexize may be better than ts_debug, but I am not as familiar with it.)

Regarding the ILIKE query, your query is returning 2/3 of the rows in the table.  Any index, whether pg_trgm-based or tsvector-based, is unlikely to help you in that situation.


how to select * from XML where id=1? declare @xml xml,
declare @num int
set @num = 1
set @xml = '
<row>
    <Id>1</Id>
    <name>reza</name>
</row>
<row>
    <Id>2</Id>
    <name>Masoud</name>
</row>
'

how to select ID = @num ??

Try this:

select
    XRows.query('.')
FROM
    @xml.nodes('/row') AS XTbl(XRows)
WHERE
    XRows.value('(Id)[1]', 'int') = 1

This will return the entire <row> XML element as XML


SQL Server indexes and record deletion I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day.  These rows have built up over 3 years and they are currently growing at approx 2 million every 4 weeks.

There are 7 indexes (3 heavily used and least fragmented/ 4 mostly unused but heavily fragmented at about 75-85% and the columns are heavily updated).  There was an SP that was scheduled to archive record stubs to another table but we have had to turn this off as it was interfering with the backups and causing a failover and outages on another system.

Ideally we only want 1 years worth of data in this table (approx 25-30m rows) and i'd like to disable the 4 unused indexes.  I've read that before i disable them they should be rebuilt but i'm not sure whether to do this before or after i've archived the previous 2 years worth of data.

A redesign has been on the cards for a few years but i'm now a team of 1 with limited knowledge of indexes/SQL tuning etc and trying to do my best with very limited resources.

The table is heavily used between 7am and 1am so most work needs to be a site outage or done in the early hours of the morning to limit downtime to users.

Any suggestions would be welcome.  Thanks.

Here some thoughts on your situation:

1) if 4 indexes are not used at all, and you are not planning using them in future, consider dropping them completely, instead of just disabling it

2) to disable an index, no need to rebuild it before disabling it. To enable it back, you have to "rebuild"
https://blog.sqlauthority.com/2007/05/17/sql-server-disable-index-enable-index-alter-index/

3) if you are willing to archive/delete previous 2 years of data, better drop unused indexes before archiving data -> this way archiving data will be less resource-intensive as those indexes will not need to be maintained during archive/delete

4) as already mentioned in comments, archive/delete data in small batches (1000,500,100 etc.) -

"delete top (n) from [YourTable] where [Date] < 'some date' "

to prevent escalating row/page level locks to table-level locks
There must be 1 to few seconds break between each batch deletion
You can write a t-sql code that does all this for you

5) You can also setup Extended Events session and monitor for "lock_escalation" events, by doing this you can find out what batch size is not causing escalation of locks to table-level when you run your archive/delete process,
and use this batch size going forward - this will make archiving/deleting painless - there will be no need for outage/downtime for your applications/users


Supporting non-ascii characters in Postgres full text (english) We're implementing Postgres full-text search. While the bulk of the text is normal English, there are a number of place names which include diacriticals (ñ,á,ó, etc)

More specifically, there are words like cañon -- I want to make sure that users who type canon also get matches for cañon.

Is there specific support for this? Or is the solution simply to manually manage the specific special cases using a thesaurus?

You can use unaccent extension, either by pre-processing the text with unaccent function, or creating your own text search configuration. For instance (based on the example on unaccent's doc):

CREATE TEXT SEARCH CONFIGURATION my_conf ( COPY = english );
ALTER TEXT SEARCH CONFIGURATION my_conf
    ALTER MAPPING FOR hword, hword_part, word
    WITH unaccent, english_stem;

So you can match it:

mydb=> SELECT to_tsvector('english','cañon'), to_tsvector('my_conf', 'cañon');
 to_tsvector | to_tsvector
-------------+-------------
 'cañon':1   | 'canon':1
(1 row)

mydb=> SELECT
mydb->     to_tsvector('english','cañon') @@ to_tsquery('english', 'cañon'),
mydb->     to_tsvector('english','canon') @@ to_tsquery('english', 'canon'),
mydb->     to_tsvector('english','cañon') @@ to_tsquery('english', 'canon'),
mydb->     to_tsvector('english','canon') @@ to_tsquery('english', 'cañon'),
mydb->     to_tsvector('my_conf','cañon') @@ to_tsquery('my_conf', 'cañon'),
mydb->     to_tsvector('my_conf','canon') @@ to_tsquery('my_conf', 'canon'),
mydb->     to_tsvector('my_conf', 'cañon') @@ to_tsquery('my_conf', 'canon'),
mydb->     to_tsvector('my_conf', 'canon') @@ to_tsquery('my_conf', 'cañon');
 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------+----------+----------
 t        | t        | f        | f        | t        | t        | t        | t
(1 row)

See the last query, I'm matching combinations of cañon and canon, when using english configuration (first 4 columns) will only match if both are the same (first 2 ones), but my_conf configuration (last 4 columns) will match in all of them.


MySQL server 5.1 trigger problem I have implemented a trigger. But once I try to update the table it is giving me the error:

  can't update table 'booking' in stored function/trigger because it is already used by the statement which invoked this stored function/trigger

This is the trigger

DELIMITER //
CREATE TRIGGER CANCEL_BOOK AFTER UPDATE on BOOKING
FOR EACH ROW
BEGIN
DELETE FROM BOOKING
    WHERE NEW.F_ID=NULL AND NEW.H_ID=NULL;
END //
DELIMITER ;

please help

Prevent record creation with F_ID and H_ID having NULL values (needs 5.5 or newer server version):

DELIMITER @@;

CREATE TRIGGER cancel_insert_book BEFORE INSERT ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time',
        MYSQL_ERRNO = 1048;
    END IF;
END;
@@;

CREATE TRIGGER cancel_update_book BEFORE UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time',
        MYSQL_ERRNO = 1048;
    END IF;
END;
@@;

DELIMITER ;

Remove record which have F_ID and H_ID having NULL values:

DELIMITER @@;

CREATE TRIGGER cancel_book AFTER UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        CREATE EVENT remove_record
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
        DO
          DELETE FROM booking
          WHERE ((f_id IS NULL) AND (h_id IS NULL));
    END IF;
END;
@@;

DELIMITER ;


ProxySQL equivalent of MaxScale schemarouter background

My employer developed a web application we provide on software-as-a-service terms to our customers. To allow for multiple customers with a huge mass of data to be stored in a database, we chose to let the application create a schema per tenant. So if we had 5 customers we had something along the lines of

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tenant_1a          |
| tenant_d2          |
| tenant_yf          |
| tenant_ok          |
| tenant_8n          |
+--------------------+

Edit:
Numbers and letters represent a hash around 32 chars long.

Tenant schema names are in fact not numeric but hashes of certain facts during creation. So not really predictable until creation. This prevents us from preparing query rules in advance.

what we have/do now

For now we are good with this and only run a mariadb galera cluster of three nodes behind a maxscale readconnroute balancer. But we will eventually hit a barrier, where adding nodes to this cluster won't do, because the overall data size won't fit on disk and/or the amount of tables will kill performance.

To keep the complexity of the applications database layer low, our devs would like us to handle the routing transparent from the viewpoint of the application: they want the application to just to talk to one "server" and not care about where which tenant is located physically.

To expand our application cluster to multiple mariadb galera clusters we could use maxscales schemarouter which exposes all schemas on all connected sub-clusters as if there was only one server. This fits perfectly into our devs expectations.

Now, a few months ago ProxySQL entered the scenery of database proxies and claims better performance paired with greater flexibility among other stuff.

actual question

We can route queries based on hard-coded schema names, but would refrain from doing so as this would mean to create/update them each time a tenant is created/deleted.

How could we replicate the dynamic behaviour of maxscales schemarouter with proxysql query rules, if at all?

You can find the answer here: Can proxysql have multiple listener? (Google Groups).

In short: ProxySQL's query rules support routing by schemaname.

For simplicity, let's assume you have 3 distinct clusters and we call the 3 clusters HG11, HG21 and HG31. The servers are 10.10.X.Y. To add some complexity, we will also enable read/write split, where the readers are HG12, HG22 , HG32.

INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES
(11,"10.10.10.1"),
(12,"10.10.10.1"), (12,"10.10.10.2"), (12,"10.10.10.3"),
(21,"10.10.20.1"),
(22,"10.10.20.1"), (22,"10.10.20.2"), (22,"10.10.20.3"),
(31,"10.10.30.1"),
(32,"10.10.30.1"), (32,"10.10.30.2"), (32,"10.10.30.3");

Enable replication hostgroups

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) VALUES
(11,12),(21,22),(31,32);

Create rules for read/write split

INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT) VALUES
(1,1,'^SELECT.*FOR UPDATE',100),
(2,1,'^SELECT',200),
(3,1,'.*',100);

Sharding, sending traffic to masters

INSERT INTO mysql_query_rules (active, flagIN, schemaname,     destination_hostgroup, apply) VALUES
(1,100, "shard001", 11, 1),
(1,100, "shard002", 11, 1),
(1,100, "shard003", 11, 1),
(1,100, "shard004", 11, 1),
...
(1,100, "shard050", 21, 1),
(1,100, "shard051", 21, 1),
(1,100, "shard052", 21, 1),
(1,100, "shard053", 21, 1),
(1,100, "shard054", 21, 1),
...
(1,100, "shard100", 21, 1),
(1,100, "shard101", 31, 1),
...
(1,100, "shard150", 31, 1);

Sharding, sending traffic to slaves

INSERT INTO mysql_query_rules (active, flagIN, schemaname, destination_hostgroup, apply)
SELECT 1, 200, schemaname, destination_hostgroup+1 , 1 FROM mysql_query_rules WHERE flagIN=100;

Load everything to runtime:

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

Eventually, save everything to disk:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

About "There is no way to have proxysql deal with the question of which schema is on which hg by itself?" : the answer is no, and this is intentional.
Each HG may have identical schemas: beside the classic "mysql", "information_schema", "performance_schema", you can have other schemas that are used for other purposes (anything really). We can't ask ProxySQL to understand what are these schemas and automatically create rules.

Also, it is possible that you have created a tenant_1 schema in two different servers, but one has production data while the other has testing data: you don't want ProxySQL to automatically add both of them.

Finally, because ProxySQL is easy reconfigurable using simple SQL queries executed on the admin interface, if you want to automate the loading of new schema you can simple create a script that connects to each HG, lists the schemas, and connected to ProxySQL creating the rules if missing. The script can be very simple, but should also have the logic to exclude schemas that shouldn't be included.

Or you can create configurations that are pushed to ProxySQL using configuration management tools like Chef, Ansible, Puppet, Consul, etc.


Is it possible to setup multiple RW Replicas for a database in an AlwaysOn Availability Group? Can I have multiple RW replicas of an AlwaysOn Availability Group database across different servers? (I.e. two-way replication so that multiple servers can write to the same DB.)

No, it is not possible to have multiple read/write replicas in an Availability Group. For your requirement, you might consider Merge Replication which allows writes at multiple databases and merges the data across all databases involved in the replication.

This doesn't allow for writing to that same DB, but the replication architecture works to keep both databases synced.


Postgresql cannot restore partial pg_dump "relation "public.my_table_seq" does not exist" In my database I dumped some tables via the following psql command:

pg_dump -t my_table1 -t mytable2 > my_dump.sql

And I try to import it into a postgresql docker image as a standalone database using docker-compose.yml:

version: '3.1'
services:
    postgresql:
        image: postgres:9.6
        volumes:
        - './my_dump.sql:/docker-entrypoint-initdb.d/my_dump.sql'
        environment:
        POSTGRES_USER: $MY_DB_USER
        POSTGRES_PASSWORD: $MY_DB_PASSWD
        POSTGRES_DB: $MY_DB_NAME

But I get the following error:

postgresql_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/my_dump.sql
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  |  set_config
postgresql_1  | ------------
postgresql_1  |
postgresql_1  | (1 row)
postgresql_1  |
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | ERROR:  relation "public.my_table_seq" does not exist
postgresql_1  | STATEMENT:  CREATE TABLE public.my_table (
postgresql_1  |         table_id integer DEFAULT nextval('public.my_table'::regclass) NOT NULL,
postgresql_1  |         name character varying(50) NOT NULL,
postgresql_1  |         mode character varying(50) NOT NULL,
postgresql_1  |         workers integer DEFAULT 1
postgresql_1  |     );
postgresql_1  | psql:/docker-entrypoint-initdb.d/cue.sql:32: ERROR:  relation "public.my_table_seq" does not exist
cue_docker_postgresql_1 exited with code 3

Meaning that for some reason I fail to dump the sequence as well via pg_dump. So I want to know how I can pg_dump specific tables alongside with their respective sequences used.

What try to do with that is to split an existing database schema into seperate databases due to its sheer size and also for scalability reasons as well.

I had also a similar case and I solved it via doing theese steps:

First of all I made an initial psql_dump with the tables I wanted to dump:

 pg_dump -t my_table1 -t mytable2 > my_dump.sql

Then I listed any sequence existing by filtering them, from the existing dump I made. In case of larger dumps use tools offered in bash or in powershell in order to filter them out. In other words you need to search any lines having DEFAULT nextval( and keep only the sequence name.
Once found the all the sequences do an another pg_dump as the one follows:

     pg_dump -O -x -t my_table_seq -t another_table_seq -t my_table1 -t mytable2 > my_dump.sql

In the command above the my_table_seq and the another_table_seq are sequences used. Also with the -x and -O parameters I ewxclude dumping any permission/role related sql so in the new database will contain only the tables and their respective data.


Creating an incremental database copy in PostgreSQL Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone.

I am searching for the same option for a Postgres DBMS.

How can I setup an incremental database copy, where entries are read from the original database, and touched/modified rows from the local copy?

If not on the DBMS level, how can I emulate such behavior at the file-system/storage level using a separate DBMS instance?

Background: The idea is to utilize the powerful database-server yet without incurring much resource overhead for a staged/developer database-copy.

There isn't really anything like this.

You can use pg_basebackup or pg_dump to copy the original DB, but there's no copy-on-write facility. The built-in replication requires a shared-nothing copy and the replicas are read-only.

You can use 3rd party replication options like Londiste or Slony-I to replicate the state of the master to the dev machines, but these systems are a bit fragile, don't deal with DDL replication, and can have a significant impact the master server's performance.

Future PostgreSQL versions will hopefully have log-streaming logical replication which would be closer to what you want. It exists as a patch to the source code now, but at time of writing (Pg 9.3 release) it's in alpha stage development where it's validated for a couple of uses but not for general use.

I'd probably go a layer or two down. I'd use file-system copy-on-write snapshots, or even copy-on-write virtual machine images, to achieve what you want.


Should PostgreSQL be Installed on a Server Machine for Multi-User Access? I am hoping to set up a database using PostgreSQL which multiple internal users should be able to access.
I am relatively new to administrating databases so I am unsure where the best place is for me to install the software.
Would it be best for me to check with my IT department to see if we have a server machine that I could install the database on?

It can be installed to a server or you can buy it as a cloud service.

As your organization has an IT department, you should ask their opinion first.


What is the sense and benefit of using SqlCommand.Prepare()? I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this?

Sample:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

I have played around a little bit and traced. The execution of the Command after calling the Prepare() method makes Sql Server execute the following statement:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

After that when the Parameter gets it's value and SqlCommand.ExecuteNonQuery() is called, the following gets executed on Sql-Server:

exec sp_execute 1,@id=20

To me this looks like the statement gets kind of compiled as soon Prepare() is executed. I wonder what is the benefit of this?
Does this mean that it is put into the plan cache and can be re-used as soon the final query is executed with the desired parameter values?

I figured out (and documented it in another question) that SqlCommands that are executed with SqlParameters always are wrapped in sp_executesql procedure calls. This makes Sql Server able to store and reuse the plans independant of the parameter values.

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here?

Preparing a SQL batch separately from executing the prepared SQL batch is a construct that is effectively** useless for SQL Server given how execution plans are cached. Separating out the steps of preparation (parsing, binding any parameters, and compiling) and execution only makes sense when there is no caching. The purpose is to save the time spent on parsing and compiling by reusing an existing plan, and this is what the internal plan cache does. But not all RDBMS's do this level of caching (clearly from the existence of these functions) and so it is left up to the client code to request that a plan be "cached", and so save that cache ID, and then re-use it. This is additional burden on the client code (and programmer to remember to do it and get it right) that is unnecessary. In fact, the MSDN page for the IDbCommand.Prepare() method states:

  The server automatically caches plans for reuse as necessary; therefore, there is no need to call this method directly in your client application.

It should be noted that, as far as my testing shows (which matches what you show in the Question), calling SqlCommand.Prepare(), does not do a "prepare"-only operation: it calls sp_prepexec which prepares and executes the SQL; it does not call sp_prepare which is parse and compile only (and does not take in any parameter values, only their names and datatypes). Hence, there cannot be any "pre-compile" benefit of calling SqlCommand.Prepare since it does an immediate execution (assuming the goal is to defer execution). HOWEVER, there's an interesting potential minor benefit of calling SqlCommand.Prepare(), even if it does call sp_prepexec instead of sp_prepare. Please see the note (**) at the bottom for details.

My testing shows that even when SqlCommand.Prepare() is called (and please note that this call does not issue any commands on SQL Server), the ExecuteNonQuery or ExecuteReader that follows is fully executed, and if it is ExecuteReader, it returns rows. Still, SQL Server Profiler does show that the first SqlCommand.Execute______() call after the SqlCommand.Prepare() call registers as a "Prepare SQL" event, and subsequent .Execute___() calls register as "Exec Prepared SQL" events.

Test Code

It has been uploaded to PasteBin at: http://pastebin.com/Yc2Tfvup.
The code creates a .NET / C# Console App that is intended to run while a SQL Server Profiler trace is running (or an Extended Events session). It pauses after each step so it will be clear which statements have a particular effect.

UPDATE

Found more info, and a slight potential reason to avoid calling SqlCommand.Prepare(). One thing I noticed in my testing, and what should be noted for anyone running that test Console App: there is never an explicit call made sp_unprepare. I did some digging and found the following post in the MSDN forums:

SqlCommand - Prepare or not to prepare?

The accepted answer contains a bunch of info, but the salient points are:

** What prepare actually saves you is primarily the time it takes to transmit the query string over the wire.
A prepared query has no guarantee of a cached plan being saved, and is no faster than an ad-hoc query once it reaches the server.
RPC's (CommandType.StoredProcedure) gain nothing from preparing.
On the server, a prepared handle is basically an index into an in-memory map containing TSQL to execute.
The map is stored on a per-connection basis, no cross-connection use is possible.
The reset sent when the client re-uses the connection from the pool clears the map.

I also found the following post from the SQLCAT team, which appears to be related, but could simply be an issue specific to ODBC whereas SqlClient does clean up correctly upon disposing of the SqlConnection. It is hard to say since the SQLCAT post does not mention any additional tests that would help prove this as a cause, such as clearing the connection pool, etc.

Watch out those prepared SQL statements

CONCLUSION

Given that:

the only real benefit of calling SqlCommand.Prepare() seems to be that you don't need to submit the query text again over the network,
calling sp_prepare and sp_prepexec store the query text as part of the connection's memory (i.e. SQL Server memory)

I would recommend against calling SqlCommand.Prepare() because the only potential benefit is saving network packets while the downside is taking up more server memory. While the amount of memory consumed is probably very small in the majority of cases, network bandwidth is rarely ever an issue since most DB servers are directly connected to the app servers over 10 Megabit minimum (more likely 100 Megabit or Gigabit these days) connections (and some are even on the same box ;-). That and memory is nearly always a more scarce resource than network bandwidth.

I suppose, for anyone writing software that can connect to multiple databases, then the convenience of a standard interface might change this cost/benefit analysis. But even in that case, I have to believe that it is still easy enough to abstract a "standard" DB interface that allows for different providers (and hence differences between them, such as not needing to call Prepare() ) given that doing so is basic Object Oriented programming that you are likely already doing in your app code ;-).


how to import mysql database structure? I have a database on Machine A. What I want is to dump my database in such a way that when I import it on another machine (Machine B) only the structure of the tables and Database are created.

I want no data.

Can mysqldump be used here?

Yes.

If you want to import the structure of the Database on some other machine there are some steps as follows

1.on Machine A(generate the dump without the data)

mysqldump -uuser -ppass --no-data --databases db1 db2 db3 > database_structure.sql

using mysqldump with --no-data will generate the dump without the data.

if you want only data dump do as(It will not contain the create statements)

mysqldump -uuser -ppass --no-create-info --databases db1 db2 db3 > database_data.sql

2.copy the  database_structure.sql on Machine B

Then you want to restore its structure on Machine B do

3.mysql -uuser -ppass < database_structure.sql

Then you have database structure available on the Machine B.

try it..


SQL Server WHERE clause on CLR method (spatial) peformance I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run without this WHERE clause but over 40 seconds to run with the WHERE clause.

The execution plan seems to indicate that the filter on STGeometryType() is being run over the geometry column of the whole locations table and there's an accompanying warning that there are no statistics for the geometry column.

If I first select into a temporary table without the filter and then filter on the temporary table, I get the results in under a second. This is because it is only testing for geometry type over a result set of 25000 records. So one solution would be to do this (though it would require re-factoring the application).

I've also tried using a CTE and derived table to do all of the joins 'first' and then filter but that doesn't improve performance.

Is there a way I can structure the query so that this relatively expensive filter is applied last without the use of intermediate tables?

   select o.code as Code, ro.ReportTaxonCode as TaxonCode, t1.TaxonName as TaxonName,
   case
       when o.NumberObserved IS NOT NULL THEN o.NumberObserved
       when o.NumberObserved IS NULL then su.Mean
   end as Scale,
   ro.OriginalTaxonCode as RecordedCode, t2.TaxonName as RecordedName, s.code as SetCode, CAST(s.StartDate AS nvarchar), CAST(s.EndDate AS nvarchar), l.code as Location, l.geom
   from obsveg.obs_observations o
   inner join obsveg.obs_sets s
   on o.obs_setsguid = s.guid
   inner join obsveg.obs_locations l
   on o.obs_locationsguid = l.guid
   inner join obsveg.obs_rpt_reportobservations ro
   on o.guid = ro.obs_observationsguid
   inner join obsveg.obs_taxon t1
   on ro.ReportTaxonCode = t1.code
   inner join obsveg.obs_taxon t2
   on ro.OriginalTaxonCode = t2.code
   left join obsveg.OBS_MeasurementMethodScaleUnits mmsu
   on o.OBS_MeasurementMethodScaleUnitsGUID = mmsu.GUID
   left join obsveg.obs_ScaleUnits su
   on mmsu.OBS_ScaleUnitsGUID = su.GUID
   where ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
   and l.geom.STGeometryType() = 'Point'

EDIT Warning in execution plan added

EDIT Execution plan posted to pastebin
Execution plan

The first thing to look at is your indexing strategy. Bad execution plans are often caused by insufficient indexes or stale statistics. Your statistics warning might hint at that.

If that does not resolve your problem there are a few hacks that you can try:

A top operation requires SQL Server to separate query sections in the execution plan.

If you know you are always dealing with less then 2 billion rows you could write your query like this:

SELECT * FROM(
  SELECT TOP(2000000000) *
    FROM <complex join>
    WHERE ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
  )X
WHERE l.geom.STGeometryType() = 'Point'

An alternative is to write a scalar valued function that takes in the l.geom column and a few other columns from the other tables and returns the STGeometryType() value while ignoring all the other values. Because SQL Server does not consider the function logic at optimization time, it is forced to execute the function after the join. That does not guarantee that the other filter is executed first but often it works out that way.

The third option is to play around with join hints and join order. They often lead to a change in where filters are applied.

All three options I would consider a last resort because they make the code ugly and you run the risk that someone removes that ugliness later trying to make the code better.


User system database design I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging system.

Certain users can create events which others can view, this is determined from the userType table. I'm unsure if I should be storing the lost password columns in the users table, would there any benefit for using a separate table for this?

(I'm not to sure if my relationships are setup correctly)

Full size

Appreciate any feedback given =)

Thanks !

Having the users and lost passwords in the same table implies a 1-1 (or 1-0/1) relationship; 1 user has 1 lost password (or none, if the column is nullable).

If that's the cardinality of the relationship, having them on the same table is correct. The school of thought that refuses nulls (which depending on the day, I adhere to), says that if the relationship is 1-0/1, you should:

users
  usersID primary key

lostPasswords
  usersID primary key references users(users_id)
  -- lost password columns here, non-nullable

The relationships look fine, however, I would suggest you use a diagramming tool which shows which are the columns referenced. Surprisingly, I've only found SchemaSpy doing this: http://schemaspy.sourceforge.net/sample/diagrams/summary/relationships.real.compact.png


Secondary Tempdb filling up on AG Setup I'm currently facing this problem in one of my Production environments.

I have an AG with around 130 DBs with readable secondaries enabled. However, there are not routing lists defined and applications are not using it. From time to time, tempdb on my secondary fills up completely.

Doing an evaluation what I was able to get was.

tempdb size: 255GB

free pages  free space in MB
27571456    215402.000000

version store pages used    version store space in MB
5130624                      40083.000000

To see what is using row versioning I am using the following query.

select
    hostname,
    elapsed_time_seconds,
    session_id,
    is_snapshot,
    blocked,
    lastwaittype,
    cpu,
    physical_io,
    open_tran, cmd,
    DB_NAME(b.dbid)
from
    sys.dm_tran_active_snapshot_database_transactions a
join master..sysprocesses b
    on a.session_id=b.spid
order by
    a.elapsed_time_seconds desc

From this point I am stuck with ideas on where to look the problem. I also didn't want to restart the secondary as this is not going to fix the issue.

Any ideas that someone could share with me?

Regards,
Marcos Freccia

I recently ran into the same issue. We have an Availability group where the secondary was having TempDB blow up and the primary was fine.

I created three tables and ran three stored procedures every 1-5 minutes on the secondary node. They are pretty light loads, obviously you should test them beforehand.

Here are the tables:

USE [master]
GO

/****** Object:  Table [dbo].[TempDBDataGrowth]    Script Date: 4/12/2018 1:13:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempDBDataGrowth](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TimeStart] [datetime2](7) NOT NULL,
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [Size] [decimal](38, 2) NULL,
    [UsedSpace] [decimal](38, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [master]
GO

/****** Object:  Table [dbo].[TempDBDataGrowth]    Script Date: 4/12/2018 1:13:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TempDBDataGrowth](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TimeStart] [datetime2](7) NOT NULL,
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [Size] [decimal](38, 2) NULL,
    [UsedSpace] [decimal](38, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [master]
GO

/****** Object:  Table [dbo].[WhoIsActive]    Script Date: 4/12/2018 1:15:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WhoIsActive](
    [dd hh:mm:ss.mss] [varchar](8000) NULL,
    [dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
    [session_id] [smallint] NOT NULL,
    [sql_text] [xml] NULL,
    [sql_command] [xml] NULL,
    [login_name] [nvarchar](128) NOT NULL,
    [wait_info] [nvarchar](4000) NULL,
    [tran_log_writes] [nvarchar](4000) NULL,
    [CPU] [varchar](30) NULL,
    [CPU_delta] [varchar](30) NULL,
    [tempdb_allocations] [varchar](30) NULL,
    [tempdb_current] [varchar](30) NULL,
    [tempdb_allocations_delta] [varchar](30) NULL,
    [tempdb_current_delta] [varchar](30) NULL,
    [blocking_session_id] [smallint] NULL,
    [blocked_session_count] [varchar](30) NULL,
    [reads] [varchar](30) NULL,
    [reads_delta] [varchar](30) NULL,
    [writes] [varchar](30) NULL,
    [writes_delta] [varchar](30) NULL,
    [physical_reads] [varchar](30) NULL,
    [physical_reads_delta] [varchar](30) NULL,
    [query_plan] [xml] NULL,
    [locks] [xml] NULL,
    [used_memory] [varchar](30) NULL,
    [used_memory_delta] [varchar](30) NULL,
    [status] [varchar](30) NOT NULL,
    [tran_start_time] [datetime] NULL,
    [open_tran_count] [varchar](30) NULL,
    [percent_complete] [varchar](30) NULL,
    [host_name] [nvarchar](128) NULL,
    [database_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [additional_info] [xml] NULL,
    [start_time] [datetime] NOT NULL,
    [login_time] [datetime] NULL,
    [request_id] [int] NULL,
    [collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Here are the three scripts I'm using to collect information: (For sp_whoisactive, you will need to download that procedure online.)

http://whoisactive.com/

USE tempdb
insert into master.dbo.TempDBDataGrowth
(TimeStart, name, database_id, size,UsedSpace)
   SELECT
        getdate() as timestart,
         SD.name,
         MF.database_id,
         SUM( CONVERT(decimal(10,2),(DF.size/128.0)) ) as Size,
         SUM( CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS INT)/128.0 ) ) ) AS UsedSpace
   FROM sys.master_files MF JOIN sys.databases SD
      ON SD.database_id = MF.database_id
   JOIN sys.database_files DF
      ON DF.physical_name collate DATABASE_DEFAULT = MF.physical_name collate DATABASE_DEFAULT
   WHERE MF.type = 0
   GROUP BY SD.name, MF.database_id;

insert into master.dbo.TempDBDataUsage
(
TimeStart, QueryExecutionContextDBID, QueryExecContextDBNAME, ModuleObjectId,
Query_Text, session_id, request_id, exec_context_id, OutStanding_user_objects_page_counts,
OutStanding_internal_objects_page_counts, start_time, command, open_transaction_count,
percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, reads,
writes, logical_reads, granted_query_memory, HOST_NAME, login_name, program_name
)
   SELECT
   getdate() as Timestart,
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC;

USE [master]
GO

DECLARE @return_value int,
        @schema varchar(max)

EXEC    @return_value = [dbo].[sp_WhoIsActive]
        @get_full_inner_text = 1,
        @get_plans = 1,
        @get_outer_command = 1,
        @get_transaction_info = 1,
        @get_task_info = 1,
        @get_locks = 1,
        @get_avg_time = 1,
        @get_additional_info = 1,
        @find_block_leaders = 1,
        @delta_interval = 1,
                @destination_table = 'WhoIsActive',
        @schema = @schema OUTPUT

SELECT  'Return Value' = @return_value

GO

This takes 1-2 seconds to run in my environment, I scheduled it for every 5 minutes. You should adjust as necessary, I left this as a job running for a week at 5 minute intervals and then I looked at the DB growth portions to see when my TempDB was filling. Then I looked at sp_whoisactive to see what query was running at that time.

Here are some queries I ran afterwards to help me find my culprits:

SELECT TOP 1000 *
  FROM [master].[dbo].[TempDBDataGrowth]
  order by UsedSpace desc

SELECT TOP 1000  *
  FROM [master].[dbo].[TempDBDataUsage]
  order by writes desc

SELECT TOP 1000 *
  FROM [master].[dbo].[WhoIsActive]
  order by tempdb_allocations desc


Clear missing index DMVs How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details?

I don't have the option to restart the server or taking it offline first and put online back again as it is a production environment.

No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter.

The only DMVs you can clear manually, using DBCC SQLPERF, are sys.dm_os_wait_stats and sys.dm_os_latch_stats:

DBCC SQLPERF
(
     [ LOGSPACE ]
     |
          [ "sys.dm_os_latch_stats" , CLEAR ]
     |
     [ "sys.dm_os_wait_stats" , CLEAR ]
)
     [WITH NO_INFOMSGS ]


Percona Server - binlog not automatically purge with mysqladmin flush-logs I have a little problem and I need your help please. Here is the story : I have Percona Server in version 8.0.13 with one slave and the old binlogs on master are not deleted automatically when flush-logs appending.
Variables are set as :

# mysql -e"select @@expire_logs_days, @@binlog_expire_logs_seconds"
+--------------------+------------------------------+
| @@expire_logs_days | @@binlog_expire_logs_seconds |
+--------------------+------------------------------+
|                  0 |                        86400 |
+--------------------+------------------------------+

I have logrotate which execute mysqladmin flush-logs every night and binlogs are correctly rotated but old ones are not removed. When I execute this command manually the behaviour is the same.

In MySQL CLI the command PURGE BINARY LOGS BEFORE datetime works fine.

The slave replication is up to date or a little bit late but not more than a couple hour. So it can't be explain why old binlogs are still present on disks (some are 7 days old by example)

I also execute lsof to check if MySQL retain descriptor on old binlogs but no. I have no more idea to explain the problem.

Do you have any ideas to explain and fix this please ?

Thanks,

Please note Paragraphs 2,3 in the MySQL Documentation for binlog_expire_logs_seconds:

  The default binary log expiration period is 2592000 seconds, which equals 30 days (30*24*60*60 seconds). The default applies if neither binlog_expire_logs_seconds nor the deprecated system variable expire_logs_days has a value set at startup. If a non-zero value for one of the variables binlog_expire_logs_seconds or expire_logs_days is set at startup, this value is used as the binary log expiration period. If a non-zero value for both of those variables is set at startup, the value for binlog_expire_logs_seconds is used as the binary log expiration period, and the value for expire_logs_days is ignored with a warning message.

  To disable automatic purging of the binary log, specify a value of 0 explicitly for binlog_expire_logs_seconds, and do not specify a value for expire_logs_days. For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly for expire_logs_days and do not specify a value for binlog_expire_logs_seconds. In that case, the default for binlog_expire_logs_seconds is not applied.

Please review your my.cnf

If you have this line in the my.cnf

expire_logs_days = 0

change it to

expire_logs_days = 1

Then go execute

mysql> SET GLOBAL expire_logs_days = 1;
mysql> FLUSH BINARY LOGS;

This will cause a new thread to be opened with the new setting.

It's OK to have

expire_logs_days = 1
binlog_expire_logs_seconds = 86400

because the documentation says when both are set, binlog_expire_logs_seconds will take precedence and a simple warning is posted for expire_logs_days.

As a last resort, please restart mysql

service mysql restart

If you still have a problem after doing this, then I would suspect a bug.


Why does MySQL choose this execution plan? I have two queries,

select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;

and

select some_other_column
from `table`
order by secondary_index_column asc
limit 4000000, 10;

Both return 10 rows; the first takes 2.74 seconds, and the second takes 7.07 seconds.  some_other_column is not a part of any index.  primary_index_column is the primary key column; secondary_index_column has a b-tree index and a cardinality of 200 (according to MySQL).

Here are the explain results:

mysql> explain select some_other_column from `table` order by primary_index_column limit 4000000, 10;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | table   | index | NULL          | PRIMARY | 4       | NULL | 4000010 |       |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+

mysql> explain select some_other_column from `table` order by secondary_index_column limit 4000000, 10;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | table   | ALL  | NULL          | NULL | NULL    | NULL | 4642945 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

Why does MySQL choose that specific execution plan for the second query?   I don't understand why it can use the index for the first query but not for the second query.

An indexed column in InnoDB always has an additional key to the gen_clust_index (aka Clustered Index) attached to it. This would be traversed by the first query to get to row 4000000 in the order of the index. Since it is the only column being requested, access to the table is unnecessary.
The second query has to collect the non-indexed column from the table along with the indexed column into a temp table. The temp table is then sorted before presenting the non-indexed column as SELECT output.
Notice another contrast

The table count is 4636881
The EXPLAIN plan for the first query traversed 4000010 indexed_column keys. No need to read the last 636871 keys.
The EXPLAIN plan for the second query traversed 4636881 rows ordered by indexed_column. For every row picking up the non-indexed column out of the table, the indexed column (already sorted by the index) is looked up and comes along for the ride. The tmp table gets ordered by the indexed column, and mysqld then dismisses the first 4000000 rows, leaving 10 rows. All that interaction between table and index just for 10 rows is the bottleneck.

COMMON THINGS
In both instances, the query specfies the number of rows to traverse. Since the number of rows in the table is 4636881, we should readily expect a full scan. The contrast becomes apparent when the MySQL Query Optimizer decides where to perform the full scan.

The first query is referencing an indexed column only in the SELECT list and the WHERE clause. The MySQL Query Optimizer chooses to perform a full index scan with no need to contact the table since everything needed is in the index.
The second query is referencing an indexed column in the WHERE clause. However, it must reach out the table to retrieve the corresponding non-indexed column. The MySQL Query Optmizer was tipped off by the query that it must not use the index becasue of the number of rows it was expected to read. As a rule of thumb for any RDBMS, if more than 5% of a table has to be read to fulfill a query, the MySQL Query Optimizer will just throw the index 'under the bus' and do a full table scan.

Doing the math, here is what MySQL Query optimizer computes:

5% of 4636881 is 231844
The second query is commanded to read 4000000 rows, which is way higher than 231844
MySQL Query Optimizer realizes there would be too much interaction between table (because of the non-indexed column) and index (because of the indexed column) to get the needed data. It decides to read the table only (because both the indexed and non-indexed columns are residing together in the table) rather than bounce back and forth between them.

In my honest opinion, with the row count of the table, the present indexes the table has, and the number of rows prescribed by the query, the MySQL Query Optimizer made the correct decision.
RECOMMENDATION
Create this index
ALTER TABLE `table` ADD INDEX mynewndx (indexed_column,some_other_column);

and your second query will never touch the table again going forward. The MySQL Query Optimizer will behave quite differently when it sees this new index.


Does Grant on MySQL overwrite previous permissions? I ran the following command per AWS Aurora's documentation to give my user permissions to write to S3: GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'. Unfortunately, it revoked permissions to do anything, so I couldn't even run a basic select statement on any table.

So what would be the proper statement to append or add the additional permission to run select into s3 for a user?

Suppose S3 is a table of database DB2, you would

grant insert on DB2.s3 to 'user'@'domain-or-ip-address'

But often you would

grant all on DB2.s3 to 'user'@'domain-or-ip-address'

There is no overwriting, grant modifies existing privileges.


How to select all columns plus one alias column in MySQL It is a simple question, but i could not find the answer.

I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ID.

I need something like this:

SELECT BIN_TO_UUID(ID) AS ID, * FROM TABLE_NAME

Also would be nice to remove the original column that was aliased, to do not have the same column twice with different names, but just if this do not add more text to the query, because i will have to repeat this for all my 16 tables.

I figured out the solution. I simple had do repeat the table name, as follows:

SELECT bin_to_uuid(id) as uuid, table_name.* FROM table_name

But if you have a solution that does not require to repeat the table name i will upvote your answer, since repeating all the table names in all the queries will bloat my the code a little.

EDIT:

As suggested in the comments by @ypercubeᵀᴹ and @Akina, looks like the * selector position is limited in MySQL. If you move the * to the first position everything works fine:

SELECT *, bin_to_uuid(id) as uuid FROM table_name


How does NHibernate handle execution plan? I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.

How does NHibernate handle execution plan?
Are execution plan reused in NHibernate?

To answer your first question, NHibernate does not handle execution plans.  SQL Server handles execution plans.  If the dynamic SQL produced by NHibernate is parameterized, the plans will be classified as "Prepared" and will be reused assuming the parameters provided in each subsequent execution could produce the same optimized query plan.  If the dynamic SQL is not parameterized, the execution plan will be classified as "Adhoc" and could still be reused.

I use this bit of T-SQL to monitor cache sizes for various query plans.  I believe I copied this from Paul Randal's site (http://www.sqlskills.com/BLOGS/PAUL/), but it has been so long that I can no longer know for sure.

SELECT
    objtype AS [CacheType],
    count_big(*) AS [Total Plans],
    sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs],
    avg(usecounts) AS [Avg Use Count],
    sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1],
    sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC;
GO


SQL PRINT vs SQL EXEC When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error:

Is there a way of simplifying what I am doing? Why do SQL PRINT and SQL EXEC deliver these two very different result sets?

DECLARE @TableName as NVARCHAR(250), @SQL as VARCHAR(MAX);

DECLARE @TableCursor as CURSOR;
SET @TableCursor = CURSOR FOR
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
AND name like 'HISTORY_MasterList_%'
ORDER BY sobjects.name

OPEN @TableCursor;
FETCH NEXT FROM @TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='select '''+ @TableName +''', 0
 Union All
select All ''Server Count'',count(1) from ['+ @TableName +']
 Union All
select All ''Server Cores'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 Union All
select ''Production Servers'',count(1) from ['+ @TableName +'] where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Cores'', sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
 Union All
select ''Production Server Count after filtering out passive/failover servers'', count(1) from
(select  distinct m.ServerName
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Production Server Cores after filtering out passive/failover servers'', sum(convert(decimal(18,0),cores)) from(
       select  distinct m.ServerName, m.Cores
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Non-Prod SQL Instances downgraded to Developer Edition'',count(1) from ['+ @TableName +'] where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod SQL Instance Core Count downgraded to Developer Edition'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']  where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod VMs moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''VM Moved/right sized from Prod Env to NonProd''
 Union All
select ''Non-Prod VMs TO BE moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''Sent for V2V - non prod split''
'

EXEC @SQL

FETCH NEXT FROM @TableCursor INTO @TableName;
END
CLOSE @TableCursor;
DEALLOCATE @TableCursor;

The error is saying that ...it is not a valid identifier.
When you pass EXEC @SQL , it is expecting that @sql to represent the name of a stored procedure (or scalar function). Hence the error message: ...it is not a valid identifier.

If you want to execute a text, to pass dynamically , you should encapsulate that text inside ( ), like EXEC (@SQL).


Counting occurrence of words in table is slow Consider these simplified tables:

CREATE TABLE dbo.words
(
    id bigint NOT NULL IDENTITY (1, 1),
    word varchar(32) NOT NULL,
    hits int NULL
)

CREATE TABLE dbo.items
(
    id bigint NOT NULL IDENTITY (1, 1),
    body varchar(256) NOT NULL,
)

The words table holds about 9000 records, each holding a single word ('phone','sofa','house','dog', ...)
The items table holds around 12000 records, each with a body text of no more then 256 characters.

Now, I need to update the words table, counting how many records there are in the items table that hold (at least once) the text in the word field. I need to account for partial words, so all these 4 records should be counted for the word dog:

'This is my dog'
'I really like the movie dogma'
'my cousin has sheepdogs'
'dog dog dog doggerdy dog dog'

That last example should count as just one record (contains the term 'dog' at least once).

I can use this query:

UPDATE dbo.words
SET hits = (SELECT COUNT(*) FROM dbo.items WHERE body like '%' + word + '%')

But, this is extremely slow, this will take over 10 minutes to complete on the not to heavy server I have for it.

AFAIK indexes won't help, I'm doing LIKE searches.
I also think full-text won't help me, since I'm looking for words starting, ending or containing my search term. I could be wrong here.

Any advise on how to speed this up?

The best way I have found to speed up leading wildcard LIKE searches is to use n-grams. I describe the technique and provide a sample implementation in Trigram Wildcard String Search in SQL Server.

  The basic idea of a trigram search is quite simple:

  Persist three-character substrings (trigrams) of the target data.
  Split the search term(s) into trigrams.
  Match search trigrams against the stored trigrams (equality search).
  Intersect the qualified rows to find strings that match all trigrams.
  Apply the original search filter to the much-reduced intersection.

It may be suitable for your needs, but be aware:

  Trigram search is no panacea. The extra storage requirements, implementation complexity, and impact on update performance all weigh heavily against it.

Test

I ran a quick test using the Complete Works of Shakespeare to populate the body column of the items table with 15,838 rows. I loaded the words table with 7,669 unique words from the same text.

The trigram structures built in around 2 seconds and the following update statement completed in 5 seconds on my mid-range laptop:

UPDATE dbo.words WITH (TABLOCK)
SET hits =
(
    SELECT COUNT_BIG(*)
    FROM dbo.Items_TrigramSearch
        ('%' + word +'%') AS ITS
);

A selection of the updated words table:

The modified trigram scripts from my article are below:

CREATE FUNCTION dbo.GenerateTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING
AS RETURN
    WITH
        N16 AS
        (
            SELECT V.v
            FROM
            (
                VALUES
                    (0),(0),(0),(0),(0),(0),(0),(0),
                    (0),(0),(0),(0),(0),(0),(0),(0)
            ) AS V (v)),
        -- Numbers table (256)
        Nums AS
        (
            SELECT n = ROW_NUMBER() OVER (ORDER BY A.v)
            FROM N16 AS A
            CROSS JOIN N16 AS B
        ),
        Trigrams AS
        (
            -- Every 3-character substring
            SELECT TOP (CASE WHEN LEN(@string) > 2 THEN LEN(@string) - 2 ELSE 0 END)
                trigram = SUBSTRING(@string, N.n, 3)
            FROM Nums AS N
            ORDER BY N.n
        )
    -- Remove duplicates and ensure all three characters are alphanumeric
    SELECT DISTINCT
        T.trigram
    FROM Trigrams AS T
    WHERE
        -- Binary collation comparison so ranges work as expected
        T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%[^A-Z0-9a-z]%';
GO
-- Trigrams for items table
CREATE TABLE dbo.ItemsTrigrams
(
    id integer NOT NULL,
    trigram char(3) NOT NULL
);
GO
-- Generate trigrams
INSERT dbo.ItemsTrigrams WITH (TABLOCKX)
    (id, trigram)
SELECT
    E.id,
    GT.trigram
FROM dbo.items AS E
CROSS APPLY dbo.GenerateTrigrams(E.body) AS GT;
GO
-- Trigram search index
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigrams (trigram, id)]
ON dbo.ItemsTrigrams (trigram, id)
WITH (DATA_COMPRESSION = ROW);
GO
-- Selectivity of each trigram (performance optimization)
CREATE OR ALTER VIEW dbo.ItemsTrigramCounts
WITH SCHEMABINDING
AS
SELECT ET.trigram, cnt = COUNT_BIG(*)
FROM dbo.ItemsTrigrams AS ET
GROUP BY ET.trigram;
GO
-- Materialize the view
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.ItemsTrigramCounts (trigram)]
ON dbo.ItemsTrigramCounts (trigram);
GO
-- Most selective trigrams for a search string
-- Always returns a row (NULLs if no trigrams found)
CREATE FUNCTION dbo.Items_GetBestTrigrams (@string varchar(255))
RETURNS table
WITH SCHEMABINDING AS
RETURN
    SELECT
        -- Pivot
        trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END),
        trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END),
        trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END)
    FROM
    (
        -- Generate trigrams for the search string
        -- and choose the most selective three
        SELECT TOP (3)
            rn = ROW_NUMBER() OVER (
                ORDER BY ETC.cnt ASC),
            GT.trigram
        FROM dbo.GenerateTrigrams(@string) AS GT
        JOIN dbo.ItemsTrigramCounts AS ETC
            WITH (NOEXPAND)
            ON ETC.trigram = GT.trigram
        ORDER BY
            ETC.cnt ASC
    ) AS BT;
GO
-- Returns Example ids matching all provided (non-null) trigrams
CREATE FUNCTION dbo.Items_GetTrigramMatchIDs
(
    @Trigram1 char(3),
    @Trigram2 char(3),
    @Trigram3 char(3)
)
RETURNS @IDs table (id integer PRIMARY KEY)
WITH SCHEMABINDING AS
BEGIN
    IF  @Trigram1 IS NOT NULL
    BEGIN
        IF @Trigram2 IS NOT NULL
        BEGIN
            IF @Trigram3 IS NOT NULL
            BEGIN
                -- 3 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                INTERSECT
                SELECT ET3.id
                FROM dbo.ItemsTrigrams AS ET3
                WHERE ET3.trigram = @Trigram3
                OPTION (MERGE JOIN);
            END;
            ELSE
            BEGIN
                -- 2 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.ItemsTrigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                INTERSECT
                SELECT ET2.id
                FROM dbo.ItemsTrigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                OPTION (MERGE JOIN);
            END;
        END;
        ELSE
        BEGIN
            -- 1 trigram available
            INSERT @IDs (id)
            SELECT ET1.id
            FROM dbo.ItemsTrigrams AS ET1
            WHERE ET1.trigram = @Trigram1;
        END;
    END;

    RETURN;
END;
GO
-- Search implementation
CREATE FUNCTION dbo.Items_TrigramSearch
(
    @Search varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Result.body
    FROM dbo.Items_GetBestTrigrams(@Search) AS GBT
    CROSS APPLY
    (
        -- Trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items_GetTrigramMatchIDs
            (GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID
        JOIN dbo.Items AS E
            ON E.id = MID.id
        WHERE
            -- At least one trigram found
            GBT.trigram1 IS NOT NULL
            AND E.body LIKE @Search

        UNION ALL

        -- Non-trigram search
        SELECT
            E.id,
            E.body
        FROM dbo.Items AS E
        WHERE
            -- No trigram found
            GBT.trigram1 IS NULL
            AND E.body LIKE @Search
    ) AS Result;

The only other change was to add a clustered index to the items table:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.items (id);


Performance improvement by placing tables on a virtual RAM disk I am trying to improve performance of queries by placing the tables in a virtual RAM DISK. I am using in Oracle 11.2 Standard edition with 4G of SGA and 2G of PGA.

I created a Virtual RAM DISK of size 140GB using imdisk. Then I moved only one table, which has 350 million rows and 32gb of size, to this RAM DISK.

Now I am accessing this table with simple straight forward query.

SELECT PRICE, QUANTITY
FROM TEST_TABLE;

It is taking around 5-6 minutes to read the entire table.

There is no difference in performance whether the table is in hard disk (SSD) or RAM disk. When RAM read speed is many times faster than hard disk, why is this not getting reflected in the query performance?

Is something is missed here?

Any help would be appreciated.

Explain Plan
Plan hash value: 2982911821

-------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   348M|    11G|  1311K  (2)| 04:22:14 |
|   1 |  TABLE ACCESS FULL| TEST_TABLE |   348M|    11G|  1311K  (2)| 04:22:14 |
--------------------------------------------------------------------------------

This is the output of stats:

| Id | Operation| Name| Starts | E-Rows | A-Rows |A-Time|Buffers|
|0   | SELECT STATEMENT| |1 | | 13 |00:00:00.04 | 4 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |      1 |    136 |     13 |00:00:00.04 |  4 |

I am trying here to see if RAM disk can really improve the performance, as this is full table scan and it has fetch so much of rows, I thought this would be appropriate use case to effects of RAM disk with Oracle.

If moving tables in RAM is going to work then, We are planning to keep our entire database in RAM (our database size is 500GB), so that we can avoid the I/O bottleneck or I/O bound and we also make sure that we have the best hardware in place that cares of I/O bound, there is nothing more can be done for the I/O. Right now I am focusing only on I/O cost and thought moving tables to RAM is going to solve those problems.

More over we already have a mechanism in place to handle the volatility of RAM, so that's not a problem for me right now. Also, the entire database is in SSD (still we are not satisfied with the performance). The data must be fetched far better from RAM than SSD. Only question that I am not able to get answer is why that is not reflected here (even though the sample query is a I/O bound). This is an exercise that I am doing to take some vital decision on using RAM and fine tuning other queries.

Why bother with a RAM disk, when you can just increase SGA, increase buffer cache, define KEEP pool, use supported, built-in tools?

Why lie to the database, and make it think it uses disk and perform all the related extra unnecessary operations, while it uses memory indirectly ineffectively?

Why risk database corruption and losing the data by using memory as "persistent" storage?

What makes you think the database worked for 5-6 minutes? Did you check what the database really did?

Even if the database read the whole table in let's say, 20 seconds, fetching and displaying 350 million rows takes a lot more. People often think wall clock time = execution time, but that is not true.

Forget EXPLAIN PLAN, that is just an assumption. At least, check the following:

alter session set statistics_level=all;
SELECT PRICE, QUANTITY
FROM TEST_TABLE;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

Run the above for both tests, and compare the results.

Or enable SQL tracing. Check at least the "disk I/O" statistics between the test. Measure your tests.


How to grant all privileges on a database? There are many almost-duplicates to this question and all I found so far were wrong and/or incomplete, so I feel we need a canonical answer.

In PostgreSQL, how can I grant all permissions on a database to a user, without giving them SUPERUSER permissions?

This includes all tables, sequences, types, triggers, indexes, procedures and whatever other objects are currently in the database and any objects that are created in the database at a later time, by this user or others. Of course, it also includes to ability to create objects in the database.

What you ask is hardly practical - you would typically use a superuser for this. Because, quoting the manual:

  The right to modify or destroy an object is always the privilege of the owner only.

And:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.

So, for a non-superuser to have these privileges, too, it would have to be granted membership (directly or indirectly) in all roles that are allowed to create objects.

GRANT any_role TO admin_role;

Granting membership in a superuser role does not make the member a superuser.

  The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be
  thought of as special privileges, but they are never inherited as
  ordinary privileges on database objects are.

But the member role can now SET ROLE and become superuser. May or may not be what you want.

Then, as owner, admin_role can also GRANT any additional privileges (that may have been revoked) himself.

Or you set up a regime that assigns ownership of all existing and future objects to a role and grant membership in that role only. The quick way to do that for all objects of given roles is REASSIGN OWNED. Related:

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

But would you even want to grant ownership on objects in the system catalog, too? Those are owned by the superuser postgres in default setup.

Instead, I would use a superuser where "all" privileges are needed. And grant only required privileges ( incl. CREATEDB and CREATEROLE) to an admin role. A recipe for a powerful admin role (without all privileges):

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


Does MSSQL log shipping automatically remove the backup files from both servers? Does Microsoft SQL Server's log shipping feature remove the backup files automatically? I am concerned that the secondary server's hard drive will reach capacity unless the previously restored files get removed.

I have not been able to find a reliable answer to this anywhere.

Log Shipping utilises an executable called SqlLogShip.exe. As per the documentation, when it runs as part of the LSBackup job, it removes old backup files outside the defined retention period on the primary:

  The backup operation creates the log backup in the backup directory.
  The sqllogship application then cleans out any old backup files, based
  on the file retention period. Next, the application logs history for
  the backup operation on the primary server and the monitor server.
  Finally, the application runs sp_cleanup_log_shipping_history, which
  cleans out old history information, based on the retention period.

When it runs as part of the LSRestore job, it removes old backup files outside the defined retention period on the secondary server:

  Any backup files in the destination directory that were created after
  the most recent restore point are restored to the secondary database,
  or databases. The sqllogship application then cleans out any old
  backup files, based on the file retention period. Next, the
  application logs history for the restore operation on the secondary
  server and the monitor server. Finally, the application runs
  sp_cleanup_log_shipping_history, which cleans out old history
  information, based on the retention period.

Provided your retention periods are set and SqlLogShip.exe is executing correctly, it should maintain the log files on both the primary and secondary locations.


SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this works.

Essentially, I have a series of forms in a project, all of which have a status assigned to them. The statuses have an assigned order of precedence, which will trickle up to the higher level visits. The way this is currently working uses the following sql statement:

select state_code
 from (select ' || v_priorstr || '
    from m_object mo, m_form mf
      where mo.id = mf.id
        and mf.formlayout_id = :1
        and mo.v = :2)
 unpivot
   (
    state_counts
    for state_code in (' || v_priorstr || ')
   ) where state_counts = 1 and rownum = 1

where v_priorstr is a comma separated string of the statuses in order of their precedence. As an example, consider the following string: "Blank,Error,InProgress,Completed". The m_object table contains each of these columns, with either a 0 or 1 to show which statuses apply. (Forms can have more than one status) This query selects the highest priority status that it will then use to display a status icon for that form.

The problem arises when dealing with repeating forms (which can have multiple iterations of themselves under one link). They all share a single status icon in the navigation system.

While normally this works fine, since SQL will naturally display columns in the order you request them, and they maintain that order when unpivoted. However, with the multiple forms, and thus multiple rows, all that statuses of the first form/row take absolute precedence over the statuses of all the forms.

For example, if I have two of these repeating forms, the first of which is 'Completed' the second is 'Error', the above query (sans the rownum = 1) will return the following two rows:

Completed
Error

I have thus far attempted the following solutions:

Group By without an Order By throws the order off.
I can't use a case-based Order By because the status names can change.

In short, I need it to either sort by or group by the status names, in the order that is passed in via that string.

This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank'
              WHEN Error=1 THEN '2 Error'
              WHEN InProgress=1 THEN '3 InProgress'
              WHEN Completed=1 THEN '4 Completed'
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;

I assume the data looks something like this:

drop table m_object;
create table m_object as
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL.  It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;
         Else
            vWord := vWord || vChar;
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN '''
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/


Problem Configuring Log Shipping for DR Server Problem Statement - I want to configure DR site in SQL Server 2012 with the existing backups.

The backup scenario is as below.
FULL – on every Sunday,
DIFF – daily except Sunday,
LOG – every 15 minutes

Now, I want DR site for my production for which I am interested in using Log Shipping. But, the problem in Log Shipping is that it will initially restore full backup then regularly ships the transaction logs to the secondary server.

But, as in my present scenario if I use this then after every FULL or DIFF backup, my Log Shipping will break as the LSN will get change with respect to my current backup at secondary server.

So, is there any way to configure Log Shipping with using existing backups or do any one have any custom procs or scripts which will help us out?

I don't understand your issue.  You can definitely log ship while performing Full and Diff backups.  Are you trying to have some manually performed version of log shipping?
Performing a Full or Diff backup will cause no issues, log shipping just uses log backups so the chain will not be broken.

Have you actually tried this out, from your question it appears that you just think it will break.


mongodb “WriteConcernFailed” on a replica set I have a replica set of 4 members. 1 primary, 1 arbiter, 1 secondary, and another one that should be secondary, but have issues and is not connected. (in a "connecting..." state).

When the unconnected member is part of the replica set, i get errors in the other members:

from the server mongo log:

[LogicalSessionCacheRefresh] Failed to refresh session cache: WriteConcernFailed: waiting for replication timed out; Error details: { wtimeout: true }

command config.$cmd command: update { update: "system.sessions", ordered: false, allowImplicitCollectionCreation: false, writeConcern: { w: "majority", wtimeout: 15000 }, $db: "config" } numYi elds:0 reslen:782 locks:{ Global: { acquireCount: { r: 208, w: 208 } }, Database: { acquireCount: { w: 208 } }, Collection: { acquireCount: { w: 208 } } } protocol:op_msg 15030ms

the write concern settings are: w : 1.0, and timeout 0.
but, "writeConcernMajorityJournalDefault" is set to true

When i remove that member from the replica, the errors disappear.

I dont understand, the write concern is set to w:1, so how do i get a timeout?
Even if it was "majority", my secondary and primary are up, so i have 2/3 servers up, why would i get a timeout?

I understand that i get a timeout error because one of the members is down, i just not really understand why.

thanks!

Just to clarify in case someone stumbles upon this in the future.

MongoDB Replicasets need a majority vote to elect a primary. So uneven amount of nodes are advised because a 4 member replica set requires 3 votes as well as a 5 member replica set.
But with a 5 member replica set 2 members can go down while with 4 only 1 can go down before the replica set enters a read only state.

The issue of OP is a different one though, it is about write concern majority. Write concern majority requires a majority vote of all the databearing nodes in a replicaset.
An arbiter is not a databearing node and thous can't vote on it but will still be counted for the total amount of votes required.

Example: In OPs deployment there are currently 2 Databearing nodes and an arbiter.
If he adds another databearing node and does a transaction that requires a write concern majority vote to pass he can't get a majority vote because the total amount of members is 4 and thous the majority is 3.
Since the third databearing node is still syncing it can't pass an OK on the write concern and that forces it to time out.
If he were to remove the arbiter and add the databearing node after it should finish syncing and be able to vote on the write concern after.
In the meantime he should not recieve any more errors because the total amount of votes required in a PSS deployment (Primary Secondary Secondary) is 2 and since there are 2 healthy data bearing nodes the write concern majority query will pass.