Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme? The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very common?

One would assume that the optimizer recognizes both and optimizes them (trivially) to the fullest.

Here is a very simple case where OFFSET ... FETCH is ~2x faster according to the cost estimate.

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
    ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

One can vary this test case by creating a CI on object_id or adding filters but it is impossible to remove all plan differences. OFFSET ... FETCH is always faster because it does less work at execution time.

The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error).  The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general:

DECLARE
    @PageSize bigint = 10,
    @PageNumber integer = 3;

WITH Numbered AS
(
    SELECT TOP ((@PageNumber + 1) * @PageSize)
        o.*,
        rn = ROW_NUMBER() OVER (
            ORDER BY o.[object_id])
    FROM #objects AS o
    ORDER BY
        o.[object_id]
)
SELECT
    x.name,
    x.[object_id],
    x.principal_id,
    x.[schema_id],
    x.parent_object_id,
    x.[type],
    x.type_desc,
    x.create_date,
    x.modify_date,
    x.is_ms_shipped,
    x.is_published,
    x.is_schema_published
FROM Numbered AS x
WHERE
    x.rn >= @PageNumber * @PageSize
    AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
    x.[object_id];

SELECT
    o.name,
    o.[object_id],
    o.principal_id,
    o.[schema_id],
    o.parent_object_id,
    o.[type],
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published
FROM #objects AS o
ORDER BY
    o.[object_id]
    OFFSET @PageNumber * @PageSize - 1 ROWS
    FETCH NEXT @PageSize ROWS ONLY;

The ROW_NUMBER plan has an estimated cost of 0.0197935:

The OFFSET plan has an estimated cost of 0.0196955:

That is a saving of 0.000098 estimated cost units (though the OFFSET plan would require extra operators if you want to return a row number for each row).  The OFFSET plan will still be slightly cheaper, generally speaking, but do remember that estimated costs are exactly that - real testing is still required.  The bulk of the cost in both plans is the cost of the full sort of the input set, so helpful indexes would benefit both solutions.

Where constant literal values are used (e.g. OFFSET 30 in the original example) the optimizer can use a TopN Sort instead of a full sort followed by a Top.  When the rows needed from the TopN Sort is a constant literal and <= 100 (the sum of OFFSET and FETCH) the execution engine can use a different sort algorithm which can perform faster than generalized TopN sort.  All three cases have different performance characteristics overall.

As to why the optimizer does not automatically transform the ROW_NUMBER syntax pattern to use OFFSET, there are a number of reasons:

It's almost impossible to write a transform that would match all existing uses
Having some paging queries automatically transformed and not others could be confusing
The OFFSET plan is not guaranteed to be better in all cases

One example for the third point above occurs where the paging set is quite wide.  It can be much more efficient to seek the keys needed using a nonclustered index and manually lookup against the clustered index compared with scanning the index with OFFSET or ROW_NUMBER.  There are additional issues to consider if the paging application needs to know how many rows or pages there are in total.  There is another good discussion of the relative merits of the 'key seek' and 'offset' methods here.

Overall, it is probably better that people make an informed decision to change their paging queries to use OFFSET, if appropriate, after thorough testing.


pgBouncer does not use all available CPUs I have a server with multiple cores that is supposed to run pgBouncer (1.7).

It's working pretty well, but it utilises only one CPU core. I get pretty high latency values when I do a load test with pgbench, emulating several clients (27 ms without pgBouncer, and 62 ms with pgBouncer).

How can I make pgBouncer use all available CPU cores?

Pgbouncer implementation is based on epoll system call and runs one thread only.
You pretty much have to run several pgbouncer processes to utilize more cores(one process per core).
But it is what you want to do only when pgbouncer is a bottleneck.


How to prevent a full database backup We are in the middle of migration, a full backup has been taken and copied to the destination. On Friday, we plan to do a differential backup, but someone has by mistake taken another full backup (did not use copy_only).

We want to prevent this in the future. We can think of DENYing (as follows), but we don't know the specific login. So, how do we make sure no-one can do a full backup?

DENY BACKUP DATABASE TO [LoginName]

The most "normal" way to prevent database backup - simply ask persons who have permissions to backup databases, do NOT do any full backups (that are not copy_only) until migration is complete...


Establishing referential integrity in a generic table? Current Schema:

 - DropDown:
     DropDownID PK,
     Name nvarchar(25)

 - DropDownOption:
     DropDownOptionID PK,
     DropDownID FK,
     Name nvarchar(25),
     Description nvarchar(155)

 - Estimate:
     EstimateID PK,
     JobDifficultyID FK(DropDownOption),
     JobTypeID FK(DropDownOption),
     etc.

 - Note there are 5+ Tables With links to DropDownOption similar to Estimate.

How can I establish referential intregity (i.e. JobDifficultyID can only point to DropDownOptions that are apart of JobDifficulty DropDown) without creating separate tables (i.e. Instead of DropDown & DropDownOption having JobDifficultyTypes, JobTypeTypes, etc)

Alternative Schema:

- JobDifficultyType:
    JobDifficultyTypeID PK,
    Name nvarchar(25)

- JobTypeType:
    JobTypeTypeID PK,
    Name nvarchar(25)

- Estimate:
    EstimateID PK,
    JobDifficultyTypeID FK,
    JobTypeTypeID FK,
    etc.

This would result in at least 13 new tables. Is this the appropriate way or is there a robust way to combine all the miscellaneous types into a single table while maintaining referential integrity?

You can use this design, which requires one more table:

 - DropDown:
     DropDownID  PK,
     Name nvarchar(25)

 - DropDownOption:
     DropDownID  FK (DropDown)  UQ1,
     DropDownOptionID  PK       UQ1,
     Name nvarchar(25),
     Description nvarchar(155)

 - Estimate:
     EstimateID  PK,
     etc.

 - EstimateOption:
     EstimateID  PK,   FK1 (Estimate)
     DropDownID  PK    FK2 (DropDownOption),
     DropDownOptionID  FK2 (DropDownOption)

For every row in Estimate, you'll have to add (up to) 13 rows in the EstimateOption table (one for each dropdown menu).

The additional Unique constraint in the DropDownOption table is needed for the foreign key (FK2) from the EstimateOption table.

What you have in your current design as Estimate, can then be constructed with a pivot query from the two tables.


query optimization for update nested query IN(SELECT Can we optimize this?

UPDATE TABLE_NOTE
SET COL_NOTE_DISPLAY='BLOCK'
WHERE COL_NOTE_BIBID = $projectID
  AND $COL_NOTE_ID IN
      ( SELECT COL_PILENOTEREL_NOTE_ID
        FROM TABLE_PILENOTEREL
        WHERE $COL_PILENOTEREL_PILE_ID = $pileId
      ) ;

How about:

UPDATE
(select * from TABLE_NOTE
where COL_NOTE_BIBID = $projectID
join TABLE_PINENOTEREL on $COL_PILENOTEREL_NOTE_ID = $COL_NOTE_ID
WHERE $COL_PILENOTEREL_PILE_ID = $pileId)
SET COL_NOTE_DISPLAY='BLOCK'


Is creating a joining/bridging table the most practical and efficient way of normalizing numerous M:M relationships in a database? Let me start with an example:

Table users:

ID | Name
---------
1, Kirk
2, John

Table class:

ID | Class
----------
1, MATH
2, FIN

Now, based on what I studied so far, in order to properly normalize this database, I'd create another table, a joining/bridging table:

Table class_enrollment:

UID | CID
1     1
1     2
2     1
2     2

Well, it works fine in these kinds of examples.

But, what if my database has 35 or 50 M:M relationships? Is it really best to create yet another 35/50 joining tables?

The real best differs case to case. May be after checking all of your 50 / 60 tables this could be arrived at. Though there is clear difference between 3NF and 6NF for sure, do not limit yourself on this.

For what you had explained, if and only if you wanted to have 6NF satisfied, the third table is essential. And you can have very simplest queries starting at class or starting at user.

But, going that way, for 50 / 60 tables, you will land up with too many cross reference tables which may be worth of having or not (depending upon your other tables by design). Remember that you are making all your queries bigger too. Also, being in 6NF sometimes affects scalability.

It is well normalized even if you just have CID within users table. I do not say - stick to just two tables and not three.

There are cases when you had worked with 50 / 60 tables with all cross reference tables in place for a while, you yourself need to denormalize to improve the performance. Though normalization is essential while designing the databases, it will not always lead to best performance.

Refer
https://en.wikipedia.org/wiki/Denormalization


Saving data for a colossal maze I want to build a labyrinth in which players can interact with others through leaving messages for one another, so in its most basic form each "tile" of the map is a forum thread or chat room.

However, I intend to have the maze continue to be generated for a very long time (the process is deterministic and will eventually terminate, but only if the 50% chance of each branch continuing fails for every single branch simultaneously. Interestingly a seed value of 42 results in a maze of size "3 tiles", so clearly 42 is not the answer here :D) and each room will have some data, such as which directions are open and which one points to the origin (can be useful for pathfinding later - find the path to the origin and they'll meet somewhere, thus you find the path between the two; this works because there are no loops, only branches).

Anyway, here's the table structure I have in mind for saving rooms:

CREATE TABLE `rooms` (
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    PRIMARY KEY (`x`,`y`),
    `open_left` TINYINT(1) UNSIGNED NOT NULL,
    `open_right` TINYINT(1) UNSIGNED NOT NULL,
    `open_up` TINYINT(1) UNSIGNED NOT NULL,
    `open_down` TINYINT(1) UNSIGNED NOT NULL,
    `origin_direction` ENUM('isorigin','left','right','up','down') NOT NULL
) ENGINE=InnoDB;

Then, the chat messages can be saved like so:

CREATE TABLE `messages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `x` INT NOT NULL,
    `y` INT NOT NULL,
    FOREIGN KEY (`x`,`y`) REFERENCES `rooms` (`x`,`y`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...
) ENGINE=InnoDB;

This seems fine, but there's one problem I can forsee:

The maze will be absolutely massive if given enough time to be generated. Based on how it is generated, its size will be exponential and could easily reach millions, perhaps billions of rooms. And past experience has taught me, tables with millions of rows are trouble.

I imagine I will probably need to break the world down into chunks. Say, each chunk can be 1000x1000 tiles in size, giving a maximum capacity of 1,000,000 rooms (assuming somehow the maze generation fills every tile in the area). This seems simple enough, and I can procedurally create tables or even whole databases of the form zone_X_Y with X and Y being the respective coordinates of that sector.

I guess mostly this question is to voice my thought process and see if anyone has any suggestions to make for better alternatives. My DB experience is pretty much just picking up what I can as I go along and learning from past mistakes.

Use UNSIGNED to squeeze out an extra bit.  A 3-byte MEDIUMINT UNSIGNED gives you 0..16M; a 4-byte INT UNSIGNED gives you 0..4 billion.  Since we are talking about X and Y, what is the max width/height of the maze?  16M or 64K seems more realistic than 4B.

Consider using a TINYINT UNSIGNED or SET to handle the 4 "open" flags.  That would take 1 byte instead of 4.  Don't you want to go for 3 dimensions?  Why not 4?  Or string-theory with 10 or 11.  (That would take 2 bytes for all the possible openings.)

Smaller --> faster, especially since this may become I/O bound.

Do you really need FOREIGN KEY and cascading delete?

I don't think MySQL needs for you to break it into 'chunks', but if you do, use suitably sized ints, such as the 2-byte SMALLINT UNSIGNED (0..65535).

Perhaps you are optimistic to think there will be more than 4 billion "messages"?  Do you have a terabyte-sized disk?

I can't really advise on the structure, nor the indexes, without seeing the queries.  I would "guess" that you always say WHERE x=... AND y=...; that is, specifying both?

Is someone (a human, that is) going to wander through this maze?  Even at one 'step' per second, a human cannot realistically touch more than 10M rooms in a year.  And such a person would probably need a padded cell after that.


How many queries per second is my Postgres executing? How can I tell how many queries per second my Postgres database is executing?

Use this query to read total number of transactions executed in all databases:

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:

SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

To calculate TPS (transactions per second), run the query several times and calculate difference over time interval.

There are ready made tools for that, one of them is http://bucardo.org/wiki/Check_postgres

More info: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

Update: Konrad corrected my misunderstanding of his question. The goal was to count queries, not transactions.

How to count queries?

Method 1

Use pg_stat_statements contrib.

Method 2

Enable full logging of queries for a representative period of time.

To enable full logging, for PostgreSQL 9.0 - 9.3, change following settings in postgresql.conf

logging_collector = on
log_line_prefix = '%t '
log_rotation_size = 1GB
log_statement = all

If you want to see also query duration, you can set log_min_duration_statement = 0 instead of log_statement = all. This is very useful for query tuning.

Then reload config (restart or HUP) and collect enough log to estimate traffic.

Note: neither method will include queries embedded in user-defined functions.


SELECT other rows based on one matching row I am trying to get related rows in a tag table based on one tag, where "related" means "having the same area_id". Example:

tag       area_id
--------  -------
turkey    1
stuffing  1
carrots   1
chicken   2

I want to get rows 'stuffing' and 'carrots' based on knowing 'turkey'. How might I accomplish this with one query (and/or subqueries) please?

if i'm reading your question properly, you know there is a 'turkey' tag and want your query to return every row that shares the same area_id but with a tag that is not 'turkey'. Right?
Try...

SELECT a.* FROM tagtable a,
(
SELECT * FROM tagtable WHERE tag = 'turkey'
) As z
WHERE a.area_id = z.area_id
AND a.tag <> z.tag;

hope that helps...


Mapping an MSSQL Login to the secondary replica in a High Availability Group I'm trying to map a login to a user in a database on the secondary replica of a two node High Availability group.  Obviously this fails since the replica is offline.  Is there a way for me to create the login mappings to users to the offline database?  I'm using Windows domain accounts BTW, so no SID issues, just the mapping problem.

When connecting to the secondary, after a failover, the error is that "the users' default database could not be accessed." The default database is the one where on the primary I set the login to have the correct database mappings, and the correct default database. When the failover happens, the default database is correct, but the user is not mapped to the database. At all. I know these mappings are at the database level which is why this is throwing me for a loop.

If the login is a Windows login, and is present on both the Primary and Secondary servers, you should only need to add the database user to the Primary Replica.

This is because database users are at the database level and the creation of the user itself will be replicated to the secondary replica. As this is a Windows login, it then follows that the SID will already match.


MySQL quick database transfer between two servers I am migrating a reasonably large (18GB) database used for a website from one server to another. There are two tables which make up 95% of that size and they do not have to be fully transferred for the site to function but eventually they'll have to be transferred. All tables are InnoDB.

I've examined the following options but I'm still unsure which would be most reliable and with least downtime.

Option A: mysqldump + import

Stop server A, mysqldump, transfer to server B and import. Definitely the safest method but downtime could be over 2 hours.

Option B: mysqldump w/out large tables + import later

Would be very quick but I'm not sure how the server would function importing the large tables while under load  and whether this would cause it to lock.

Option C: rsync datadir

I've seen this option mentioned in other question answers, and I wasn't able to make it work on a running server, understandably, but I can see it going wrong again even if server A is stopped fully before syncing. Also server A is on 5.6.25 while server B is on 5.6.28.

Option D: early import + incremental backup

I could make a --single-transaction dump on running server A, import it in advance and then make a further incremental backup whether through the binary log or other incremental backup systems.

Option A
Definitely the safest, but long downtime not necessary if Option D is applied correctly. Your downtime would be based on collecting the incremental, not the initial load of the target server. This would be very short if the loading of incrementals are being done frequently.
Option B
This is good, provided the large tables are archive data only.
If the large tables are active logs, go to Option D
Option C
To ensure an rsync, mysqld should be down. To do so, run this
mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, run this
service mysql stop

Then, issue the rsync of the data directory. This will ensure the mysqld commits everything is has on hand when performing MySQL shutdown. Thus, when you run
service mysql start

the MySQL will not do any crash recovery during mysqld's startup phase.
If you decide to rsync a live server, you are not taking into account the InnoDB Buffer Pool that has dirty pages (changes needed to be flushed to disk). Starting mysqld from a live rsync would impose crash recovery using ibdata1's double write buffer and a scan for any changes (deltas) in the redo logs (ib_logfile0,ib_logfile1). This is risky in a write-intensive live database.
Option D
Doing --single-transaction mysqldump creates a convenient point-in-time backup. Doing the incremental using binary logs will require four(4) things:

Enable binary loggging on DB Server
Ensure that binary logs stay around by increasing expire_logs_days in case you delay doing the incremental load of changes a day or two
Use mysqlbinlog to dump events after the replicaton coordinates of the dump to create incremental data
Use --master-data=2 to record binary log and position of the dump (will appear on line 23)


SET ROLE via parameterized query In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I would like to prevent SQL injection attacks by parameterizing the SET ROLE statement. My approach in Groovy (which uses JDBC) was:

def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver')
sql.execute 'SET ROLE ?', user

but this generates a syntax error. The documentation says SET ROLE can take a string literal, but I'm just not clear how I can validly pass it one. Any suggestions?

Based on a simple test case I just wrote:

@Test
public void test() throws SQLException {
    PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
    ps.setString(1, "someuser");
    ps.executeUpdate();
}

I think the error you refer to is probably:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 10
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2245)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1974)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:254)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:565)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    ....

The reason for this is that PostgreSQL's protocol can only bind placement parameters for "plannable" statements, which are SELECT, INSERT, UPDATE, DELETE and a few others. For these statement types it only supports placement parameters for literal values, not for identifiers or other syntax elements.

For other statement types, literals must be substituted in by the client. Some drivers support client-side emulation of prepare for these statements, so they seem to work transparently, but PgJDBC does not currently support this.

PgJDBC actually supports client-side parameter binding if you're using the legacy v2 protocol, but it doesn't expose this functionality for connections on the v3 protocol. Even if you're not using server-side prepared statements it'll still use server-side parameter binding. Forcing the v2 protocol will work, but is a poor workaround with lots of other consequences elsewhere - plus at some point PostgreSQL will drop support for the v2 protocol entirely.

I think this is an issue with the driver and/or PostgreSQL's wire protocol. The user shouldn't have to care about whether the server supports parameter binding for some statements and not others, the driver should be taking care of that - or better, the server should just support it for all statement types.

Unfortunately, PgJDBC also doesn't expose its internal implementations of safe identifier and literal escaping for client application use. If standard_conforming_strings is on, though, the rule is very simple:

Replace every ' with ''; and
Wrap the whole thing in single quotes

The same applies to identifiers, with double quotes.


Multiple AGs on same Windows Failover cluster the Listeners don't work properly Here is my case. I have Server1 and Server2, which were installed 4 SQL instances as follows:

Server1

Server1\SQL1
Server1\SQL2

Server2

Server2\SQL1
Server2\SQL2

The Server1\SQL1  and Server2\SQL1 are replicas in SQLAG1 which is listening on the listener xxx.xxx.xxx.54

The Server1\SQL2 and Server2\SQL2 are replicas in SQLAG2 which is listening on the listener xxx.xxx.xxx.55.

But when I use SSMS to connect to xxx.xxx.xxx.54 or xxx.xxx.xxx.55 It always connects to the Server1\SQL1 which is the primary replica of SQLAG1.

Any thoughts? Do I need to install more AGs on the same servers? How can I separate them by listeners?

It sounds like you have set the listeners to use the same port, probably the default 1433.

When you create an AG listener you specify a port for it to listen on. If you have multiple listeners on multiple AGs, they will each need to listen on a different port otherwise you will only be able to connect to the listener that started first and gained control of the port.

You should also ensure configure your SQL Server instances to listen on a specific port, as named instances will use a dynamic port by default. Specifying the port helps you document and control your environment better.

Info on configuring SQL Server to use a specific port

For your listener, run the below query against the primary of each AG to get the current port configuration:

SELECT ag.name AS [Availability Group], agl.dns_name AS [Listener DNS Name],
agl.port AS [Port]
FROM sys.availability_group_listeners agl
INNER JOIN sys.availability_groups ag ON agl.group_id = ag.group_id
ORDER BY ag.name, agl.dns_name

And use this code to update your listeners to use a specific port

USE [master]
GO
ALTER AVAILABILITY GROUP [** AG name here **]
MODIFY LISTENER N'** Listener name here **' (PORT=1433); -- or change to whatever port you are wanting to change to.
GO

Info on changing listener ports here


Will increased network latency cause table locks in MS SQL Server? If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that data over a slow network - will there be a read lock on table A while the server sends the response over the network, or does SQL Server release the lock before sending the response?

Also, would the answer vary based on the size of the response? If it just has to return a few KB vs several hundred MB, would that make a difference?

Creating an explicit transaction, running queries, and closing the transaction would obviously cause the tables to lock, since the duration of the transaction is correlated with my latency.

If client takes long time to receive data and in turn send
  acknowledgement to SQL Server that it has received the data SQL Server
  has to wait, due to this wait SQL Server will not release the locks
  held by the query unless acknowledgement is received from client.

This is not accurate, it is dependent on the isolation level.

At the default READ COMMITTED locks are not held for the duration of the statements execution. READ COMMITTED does not provide statement level read consistency, the only guarentee is that you cannot read uncommitted data. A shared lock is acquired and held to read the row and then released.

Unless you have LOB types.

LOB types, being potentially very large, cannot be buffered. A shared lock must be acquired and held until the statement completes, essentially giving you REPEATABLE READ behavior at READ COMMITTED.

  If I'm making a single call to an MSSQL database over a high-latency
  network, will table locks occur due to that latency?

The latency isn't causing the table lock, no. However, if a table lock has been acquired the latency is going to prolong it.

To quote someone that knows the mechanics of this better than I (@RemusRusanu):

  Results are returned back to the client program as the execution
  proceeds. As rows ‘bubble’ up the execution tree, the top operator is
  usually tasked with writing these rows into network buffers and
  sending them to back to the client. The result is not created first
  into some intermediate storage (memory or disk) and then sent back to
  the client, instead it is sent back as is being created (as the query
  executes). Sending the result back to the client is, of course,
  subject to the network flow control protocol. If the client is not
  actively consuming the result (eg. by calling SqlDataReader.Read())
  then eventually the flow control will have to block the sending side
  (the query that is being executed) and this in turn will suspend the
  execution of the query. The query resumes and produces more results
  (continue iterating the execution plan) as soon as the network flow
  control relieves the required network resources. [source]

Where results aren't consumed as quickly as SQL Server can deliver them, be it due to the client or the network, we see ASYNC_NETWORK_IO waits accumulating. To reiterate, this will not influence the locks that are acquired, just the duration they are held.


PostgreSQL return second 'Group' as an array I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this...

websites
-------
website_id (pk)
url
description

visitors
-------
visitor_id (pk)
ip_address

website_visits
--------
visit_id (pk)
website_id (fk)
visitor_id (fk)
visit_date
visit_duration

I know that I can use two GROUP BY 's to produce a single list ordered by website using the query below...

SELECT w.website_id, v.visitor_id, count(wv.visit_id)
FROM website_visits ws, websites w, visitors v
WHERE wv.website_id = w.website_id and wv.visitor_id = v.visitor_id
GROUP BY w.website_id, v.visitor_id
ORDER BY w.website_id ASC, count(wv.visit_id) DESC

But I would like to improve this by a) only showing the top ten for each site, and b) grouping each top 10 result into an array, so that the results look something like this:

website_id    top_ten
432           [{visitor_id:354,visits:958},{visitor_id:987,visits:971}...etc]
325           [{visitor_id:456,visits:955},{visitor_id:1056,visits:465}...etc]

(I have used JSON here but it could be in any format)

I have experimented with using the SELECT ARRAY(...) function in a subquery but I cannot seem to get it working and I am not sure if thats the correct approach?

Can someone advise? Thanks

Using PostgreSQL 9.0 by the way

Maybe something like this:

with ranked_visits as (
  SELECT w.website_id,
         v.visitor_id,
         count(wv.visit_id) as visits,
         row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk
  FROM website_visits wv
    JOIN websites w ON wv.website_id = w.website_id
    JOIN visitors v ON wv.visitor_id = v.visitor_id
  GROUP BY w.website_id, v.visitor_id
  ORDER BY w.website_id ASC, count(wv.visit_id) DESC
)
select website_id, string_agg('visitor_id: '||visitor_id||',visits:'||visits, ', ')
from ranked_visits
where rnk <= 10
group by website_id;


SQL Server Agent not observing "execute as" permissions I have a curious permissions problem.  Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working.  They previously worked; they are now throwing basic permissions errors (e.g., SELECT permission denied).

Here's a specific test case.  The user johndoe has the server-level sysadmin role. The following query works fine when run by johndoe in SQL Server Management Studio:

select * from TableA into TableB

But when I put that exact same query into a single-step SQL Server Agent Job, with that step configured to run as user johndoe, I get the following error.

  Executed as user: johndoe. The SELECT permission was denied on the
  object 'TableA', database 'MyDatabase', schema 'dbo'. [SQLSTATE 42000]
  (Error 229).  The step failed.

Any suggestions on what might have caused this seemingly spontaneous change?

Have you checked which SQL user is actually running the query?  Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data.  If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.


Why does MyISAM support FULLTEXT Searching and InnoDB does not? I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess is because table storage format or the way that data is indexed. Sorry for my ignorance.

Back in the day, people have been clamoring for FTS for InnoDB and feverishly looking for workarounds. Sometime around January 2005, InnoDB found a sponsor to look for a developer.

I am sure one of the biggest gotchas that developers worried about was making FULLTEXT Searching (FTS) work in harmony with Transactions. This is especially needed since the tokenization for inserted strings is performed only at commit time, so a full-text search does not see the uncommitted data.

Please keep in mind that although there are tutorials on FTS with InnoDB, MySQL 5.6 still has a bug or two out there for InnoDB/FTS.


System Monitor and Memory in SQL Server Which performance counter (System Monitor) should I use to answer the questions:

total amount of memory currently used by SQL Server
total amount of memory used by a process
total amount of memory required by SQL Server for running processers efficiently.

I think that learning about the answers to these questions is the beginning of the evaluation of memory (global).

I like the way you are thinking. Too many people don't think about these things until they've been burned by them.

You've asked about three things. Some are more "1 answer works" kind of questions. Some are more "Lots of opinions exist.

I'll take a first stab at least. One good rule of thumb, though - don't believe what Task Manager thinks. There are a few reasons that is not so reliable. Lots of memory from different places and various versions do things a slight bit different there.

There are actually a few places to see "total amount of memory currently used by SQL Server" - In perfmon/sysmon you can see two counters - "Total Server Memory" and "Target Server Memory". Simply put, the Total Server Memory is how much SQL Server is using. The Target Server Memory is how much SQL Server - at that moment in time- would like to have based on its current load. You can find this counter Perfmon in SQLServer:Memory Manager perfmon object. If you are on a Default instance it should just be SQLServer at the parent object. If a named instance, it would be something like MSSQL$InstanceName. But Memory Manager is where you can be.

For the second question - you can see a few memory counters under the Process object - and you'll see various instances for the various processes. There are a few counters here - I'd normally stick with Private Bytes to just get a generic "how much is that process actively/actually using"

For the last question - "Total amount of memory required by SQL Server for running processes efficiently" - that is a tougher nut to crack. While mileage certainly varies, I tend to look for a few adages here when I go down this road:

Memory is cheaper than licenses and just about everything else. You can get storage a little wrong. You can even be a bit under on procs. Memory under allocation is a sin you'll pay for for a long time - so I tend to be a bit aggressive in memory allocation where I can. (Generally).
If target memory is normally much higher than total memory in the point I made above - that can be a rather clear sign (do note that SQL memory takes some time to "stabilize" after a service restart - when you first bring SQL up - target and total play a game - you'll see them each grow. Wait for some stability.
How is Page Life Expectancy? This counter is in the SQLServer:Buffer Manager (really you should look at Buffer Nodes in case you have multiple NUMA nodes and see what all of your PLEs are for the various memory allocations in each NUMA node). Is it constantly up and down like it can't sit still? While workload and so many variables come into play - it's better to have a PLE that tends to go up or at least say stable. If it looks like a heart monitor (with a normal or fast heart rate), it could be a sign that you are having some memory pressure.

But I don't like tuning monitors only - you also have to ask "is this impacting our customers?" what's the experience like? Are folks complaining? Are queries going longer than baseline? Front end response times waiting too much on data calls? Do some SQL Server waits based monitoring - what are you watiing for mostly? Are they counters related to memory and potentially to IO?

(Of course each of those opens a whole new rabit hole.. What comes first "insufficient memory" or "bad queries?" - so take a holistic appraoch. Look at your queries. Look at overall tuning. But those counters will at least be a good start for you.


Less restrictive query return less result due to simple removing one additional constraint Look at this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

that's basically search all bizs whose TA.LatLong is in the box of 'MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)' and after that the box must contain kucing

This returns 22 rows.

Now compared that to this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
ORDER BY
  Distance
LIMIT
  0, 20

It's the same query but LESS restrictive. Now I only want to check whether the business is in the box or not. That is it. Yes there are businesses in those box. Most of which do not contain the word "kucing*"

There should be more queries. Yet there are less. The second query return null. The LESS restrictive query return LESS result.

Rolando answer is good with one catch.

The one with
    MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) is the one that works properly.

I am asking why the one without is problematic. Rolando seems to try to correct the correct ones.

Note: (I wonder whether this should be another question). After listening to others I tried:
After removing distinct

SELECT
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong)
AND TA.Prominent >15
ORDER BY
  Distance
LIMIT
  0, 20

returning 0 rows

Then I tried removing the order by and limit

SELECT
  TB.ID,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
            ),
            TA.Latlong)
AND TA.Prominent >15

Which also result in 0 rows finally I use the original query and make sure I only remove

    AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)

to turn results from some results to no results

Finally I make the query really simple

SELECT BusinessID, Prominent
FROM
    tableauxiliary as TA
WHERE
  MBRContains(GeomFromText ('MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)'),TA.Latlong)

That's it the most basic query ever.

Still return no result

Now here's a miracle. I add 2 characters and it works again. The characters I add is =1

SELECT BusinessID
FROM tableauxiliary AS TA
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)' ) , TA.Latlong )
 =1

I got result

I guess it's time to report bugs.

The FULLTEXT index acts very funny with regard to the MySQL Query Optimizer. I have written about this before:

https://stackoverflow.com/a/6092216/491757 (May 23, 2011)
FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)
Mysql fulltext search my.cnf optimization (Jan 26, 2012)
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

Try refactoring your first query so that the FULLTEXT clause and the Spatial clause are not performed in the same level of the EXPLAiN plan.

SELECT * FROM
(
    SELECT DISTINCT
        TB.ID,
        TB.Latitude,
        TB.Longitude,
        111151.29341326*SQRT(pow(-6.185-TB.Latitude,2) +
        pow(106.773 - TB.Longitude,2)*0.98839228980165) AS Distance
    FROM
    (
        SELECT * FROM `tablebusiness`
        WHERE MATCH (FullTextSearch) AGAINST
        ('kucing*' IN BOOLEAN MODE)
    ) AS TB
    join
    (
        SELECT ID FROM tableauxiliary
        WHERE MBRContains(GeomFromText ('MULTIPOINT
        (-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),TA.Latlong)
    ) as TA
    on TA.BusinessID=TB.ID
    ORDER BY
        Distance
) AA LIMIT 0, 20;


Grouping multiple records into one row I need to get daily reports for an app.

A report should tell me how many times a event page was visited and how many tickets were sold for each category. All that should be grouped by day in a single row, like so:

Date        | Category A | Category B | Category C | Visits
'2017-03-10'|         10 |          2 |          8 |     30

Using a sequence table I was able to group results by day, but the best I've been able to do is to have a row for each category for each day, ie, if I have 3 categories, I get 3 rows for each day.

Fiddle

If the number of categories is fixed an known in advance, this is just some conditional summing over your existing solution... and add whatever logic is needed for the visits via a subquery:

SELECT
    date,
    sum(CASE WHEN `categoryId`=1 THEN `ticketCount` END) AS `Category A`,
    sum(CASE WHEN `categoryId`=2 THEN `ticketCount` END) AS `Category B`,
    sum(CASE WHEN `categoryId`=3 THEN `ticketCount` END) AS `Category C`,
    (SELECT  count(id) FROM event_page_visits
      WHERE event_id = 1 and date(created_at) = date) AS `Visits`
FROM
(
    select
      `list_of_dates`.`date` as `date`, `ticket_categories`.`id` as `categoryId`, `ticket_categories`.`name` as `categoryName`, count(tickets.id) as ticketCount
    from (
      SELECT DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) AS DATE FROM seq_0_to_999 AS seq WHERE DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) <= '2017-03-14'
    ) as list_of_dates
    cross join
      `ticket_categories`
    left join
      `tickets` on
        `tickets`.`ticket_category_id` = `ticket_categories`.`id` and
        `tickets`.`created_at` >= `list_of_dates`.`date` and
        `tickets`.`created_at` < list_of_dates.date + INTERVAL 1 DAY
    where
      `ticket_categories`.`event_id` = 1
    group by
      `date`, `ticket_categories`.`id`
) AS q
GROUP BY
   `date`
order by
  `date` asc ;

And you just get:

|       date | Category A | Category B | Category C | Visits |
|------------|------------|------------|------------|--------|
| 2017-03-10 |          4 |          3 |          2 |     10 |
| 2017-03-11 |          0 |          0 |          0 |      2 |
| 2017-03-12 |          1 |          1 |          0 |      3 |
| 2017-03-13 |          0 |          0 |          3 |      6 |
| 2017-03-14 |          0 |          0 |          0 |      0 |

Fiddle.


Does it make sense to limit Mongo RAM? I have a Windows server running MongoDB. My database has big indexes and all RAM is usually busy. As I know, it can be a cause of Windows swapping and performance issues. Does it make sense to limit Mongo service RAM (e.g. use only 80%)?

No. If it starts to swap, it means that MongoDB needs more RAM than the system has available. Limiting the RAM used would make the situation worse.

MongoDB uses only up to a certain limit (95% of physical RAM, iirc), which might lead to the wrong assumption that there is free RAM available and it must be a problem caused by MongoDB. Quite the contrary: MongoDB keeps the system usable for administrative tasks.

Solution: increase the physical RAM (scale up) or start sharding (scale out).


Mongodb automatic Replica Set creation with JS driver The MongoDB docs state that one can use the mongo client console to initialize replica sets by running rs.initiate([config]). However, I don't want to have to manually use the command line for this. There is a driver method called Admin.command in the docs but I don't know how that would work here. How do I automate configuring replica sets using the MongoDB JS driver?

I have a js script that initializes my database installation by creating the data folder and automatically setting up users for access control. I want it to also configure the replica set so the database is completely ready to use after running it. I'm using MongoDB 4.2.1 and driver version 3.3.

The method rs.initiate can only be used in the MongoDB shell, for drivers you have to use Database Commands.

For example:

myMongoClientVariable.db("myDB").admin().command( {
    replSetInitiate : {
        _id : "myReplicaSet",
        members : [
            {_id : 0, host : "host0"},
            {_id : 1, host : "host1"},
            {_id : 2, host : "host2"}
        ]
    }
} );


Scaling Azure SQL database will make my website goes down? I have a website using Azure SQL databases, and I have some questions:

How does the scaling process work? Is there a flow, diagram that illustrates it?
Will the scaling cause any downtime in my website?
Am I able to write things to the database during the scaling process?

How does the scaling process work? Is there a flow, diagram that
illustrates it?
See this link:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers
Will the scaling cause any downtime in my website?

No new connections for a brief period and existing connections might rollback.

  Changing the service tier and/or performance level of a database
  creates a replica of the original database at the new performance
  level, and then switches connections over to the replica. No data is
  lost during this process but during the brief moment when we switch
  over to the replica, connections to the database are disabled, so some
  transactions in flight may be rolled back. This window varies, but is
  on average under 4 seconds, and in more than 99% of cases is less than
  30 seconds. If there are large numbers of transactions in flight at
  the moment connections are disabled, this window may be longer.

Am I able to write things to the database during the scaling
process?

Yes but you might see your transaction is rolled back.


sp_BlitzCache gives a "divide by zero error encountered." After updating to First Responders Kit 20180901, I have a job that runs every 15 minutes and calls sp_BlitzFirst:

EXEC [DBA Maintenance].[dbo].[sp_BlitzFirst]
    @OutputDatabaseName = 'DBA Maintenance',
    @OutputSchemaName = 'dbo',
    @OutputTableName = 'BlitzFirst',
    @OutputTableNameFileStats = 'BlitzFirst_FileStats',
    @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
    @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
    @OutputTableNameBlitzCache = 'BlitzCache',
    @OutputTableRetentionDays = 10;

After updating the SQL Scripts to First Responders Kit 20180901, the Job keeps failing and when I run the Step SQL manually I get below messages:

. . .
Populating Warnings column
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 3972
Divide by zero error encountered.
The statement has been terminated.
Populating Warnings column for stored procedures
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 4047
Divide by zero error encountered.
. . .
Writing results to table.
sp_BlitzCache Finished

I think the error is happening for the sp_BlitzCache CASE lines

CASE
    WHEN   CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10.
    THEN    ', High Compile Memory'
    ELSE    ''
END

When I SELECT the values in the ##bou_BlitzCacheProcs table, MaxCompileMemory is either NULL or 0 (Zero).

Anybody else having this issue?

Is there a SQL Setting I need to look at?

Clearly, without a doubt, that's a bug. You'll want to post it there.

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues

I'm guessing MaxCompileMemory is 0 for you? This is the only division I see,

CASE WHEN CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. THEN ', High Compile Memory' ELSE '' END, 3, 200000)

Update

Filed here and fixed with this patch.


(SSMS) How do you prevent an open tab from querying a DB after disconnecting the server In SSMS if you have a tab open with a query and then you disconnect the database you can still run the query after disconnecting.

is there a way to prevent SSMS from running a query after server is disconnected?

The Object Explorer pane and the Query pane are independent. You can have the Object Explorer connected without having a Query connected, and vice versa.

The menu option you probably want is Query -> Connection -> Disconnect.

HTH


Which of tables key to reference? MySQL.

There is a table users with autoincrement primary key id.

There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not autoincrement.

employees.userid is a foreign key for users.id.

Thus every employee's primary key value is also users primary key value.

Now consider a third table rows of which should refer to an employee.

The question: Should the third table refer to users.id or to employees.userid?

If the third table is related to employees and not to every user, you should reference employees.userid.

It makes sense for, say, a table of employee identity card numbers to reference "employees"."userid". But it makes sense for a table of email addresses to reference "users"."userid", because every user has an email address.


What kind of MySQL replication? I'm working on an ERP project for an educational institution. They have two branch offices and we need to keep records not only for customers, but also class calendars, class reservations for teachers.

At the beginning I'll start with a cloud based web application. However, in the future I will need to replicate database to two additional physical addresses. Power outage and loss of internet connection are not rare in the city.

What replication model do you recommend? Master-Master, Master-Slave, or Mysql Group Replication? What about MySQL Cluster?

Master-Slave is pretty good, but you must write only to the Master.  Inability to write to the Master means inability to do the various tasks.  A network outage between M and S leads to delayed updating of the Slave.

Master-Master, and writing to both, has several ways to get into trouble, so I recommend against it.

Master-Master, but writing to only one solves some of the M-M problems, and makes failover easier.  That is, if the Master fails, the configuration is ready to promote the Slave in an otherwise M-S topology to be the Master.

Group Replication has a lot of promise, and is likely to be the best in the long run.  However, I cannot say much at the moment.

MySQL NDB Cluster is complex to set up, has many moving parts, uses "eventual consistency" model of replication, etc.  Some applications can use it; hard to say if it fits your situation.  Study its implementation of "eventual consistency"; if that model works for you, then consider putting an instance of the three nodes (data, sql, mgt) on a single server at each of your two sites.  (I assume scaling is not an issue.)

Galera (see MariaDB, Percona's PXC, or Codership), which you did not list, is robust, write-anywhere, self-healing, etc.  The optimal setup would be to have 3, not 2, "nodes" in 3 separate locations with separate network connections.  As long as any 2 nodes can talk to each other, those 2 declare themselves to be online.  If the third is alive, but isolated (because of network outages), it understands that is should be "offline".  When an offline node comes back online (from network outage or servers crash), the data is automatically repaired with the help of the online nodes.


Integration Services Catalogs - Version Description In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no way yet to put a description into this field. How can it be edited/filled ?

I assume you're describing this window

What the answer wasn't

Folder's have descriptions, you can set them via the catalog.set_folder_description stored procedure. In my example, my folder is called SSISHackAndSlash and my project is SSISHackAndSlash2012.

I called the above method and looked at the properties of the folder and they match.

I then tried to use the same call but using the Project name but no dice

EXECUTE catalog.set_folder_description
    @project_name
,   @folder_description = 'called from set_folder_description'

  Msg 27104, Level 16, State 1, Procedure set_folder_description, Line
  73 Cannot find the folder 'SSISHackAndSlash2012' because it does not
  exist or you do not have sufficient permissions.

That's the only public proc in the list of all the SSISDB Stored Procedures that mentions description.

The actual deploy proc, catalog.deploy_project doesn't have a parameter for description.

What the answer is

Since the answer wasn't in the public catalog and it lead me to believe it either wasn't an exposed property or it's something in the actual project itself. Occam's razor wins.

Win

If you like futzing with the .ispac directly, in the @Project.manifest, the property is serialized under the <SSIS:Properties> node as <SSIS:Property SSIS:Name="Description"></SSIS:Property>


How to best store Google ngrams in a database? I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database.

I am wondering how one should store Google ngrams in a database best. I mean, if you are not using onegrams, but e.g. twograms or threegrams the amount will be much larger. Can I store 500 million records in one database and work with it or should I split it to different tables?

After how many records should one split it and how should one split it best (considering that twograms has 100 files and thus probably about 5 billion records)? Is it recommended to use MySQL horizontal partitioning or rather build one’s own partitioning (e.g. via first character of word => twograms_a).

There were so many changes I would have to make to my first answer I start this one !!!

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;

    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

Much smaller tables for year info but much bigger keys to preserve the original ngram. I also increased the amount of test data. You can cut and paste this directly into MySQL.

CAVEAT

Simply remove ROW_FORMAT and it becomes dymanic and compress the ngram_key tables a lot smaller.

DiskSpace Metrics

nrgram_rec has 17 bytes per row
8 bytes for ngram_id (max unsigned value 18446744073709551615 [2^64 - 1])
8 bytes for 4 smallints (2 bytes each)
1 byte MyISAM internal delete flag

Index Entry for ngram_rec = 10 bytes (8 (ngram_id) + 2 (yr))

47 million rows X 17 bytes per row = 0799 million bytes = 761.98577 MB
47 million rows X 12 bytes per row = 0564 million bytes = 537.85231 MB
47 million rows X 29 bytes per row = 1363 million bytes = 1.269393 GB

5 billion rows X 17 bytes per row = 085 billion bytes = 079.1624 GB
5 billion rows X 12 bytes per row = 060 billion bytes = 055.8793 GB
5 billion rows X 29 bytes per row = 145 billion bytes = 135.0417 GB

ngram_key has 73 bytes
64 bytes for ngram (ROW_FORMAT=FIXED set varchar to char)
8 bytes for ngram_id
1 byte MyISAM internal delete flag

2 Index Entries for ngram_key = 64 bytes + 8 bytes = 72 bytes

47 million rows X 073 bytes per row = 3431 million bytes = 3.1954 GB
47 million rows X 072 bytes per row = 3384 million bytes = 3.1515 GB
47 million rows X 145 bytes per row = 6815 million bytes = 6.3469 GB

5 billion rows X 073 bytes per row = 365 billion bytes = 339.9327 GB
5 billion rows X 072 bytes per row = 360 billion bytes = 335.2761 GB
5 billion rows X 145 bytes per row = 725 billion bytes = 675.2088 GB


RHEL 7.4, heavy workload(s) and SWAP usage This might not be the correct board for this, but since I'm a DBA and I'll ask here.

I have several RHEL 7.4 servers, running a mix of MariaDB (10.1, 10.2) and MongoDB (3.4).  The problem was happening with RHEL 7.3 as well.

All of these servers have 256Gb of memory with local SSD array storage, and even given heavy workloads the highest active memory-in-use footprint is less than 100Gb at any one time.  I've been profiling this for quite some time:

On each of these servers, even though there is always plenty of free memory available, the systems are incrementally going into swap.  I've tried setting the vm.swappiness value to 1, but incremental jumps of swap are still happening.

Is this happening to anyone else?  Does anyone know, with a large amount of memory available, if setting swappiness to 0 has ill effects?

Thanks

If you have 8GB swap, 256 GB physical memory and swappiness=1, with memory usage nearing 72Gb, it's acceptable to swap operations almost 700MB (72GB * 1%), then the 100MB swap usage that you had seen is pretty normal.

Be sure that your 1TB daily data load doesn't exhaust your physical memory and you can change swappiness to 0 without side effects.

But do you have a quite low swap rate in your setup. Do you really need to avoid swap?


How to FETCH from a cursor into a table variable? I have a cursor defined as:

DECLARE idCursor CURSOR
    FOR SELECT DISTINCT [id], [data]
        FROM #tempTable

And I have a variable defined as:

DECLARE @currentId TABLE (
                            [id] int,
                            [data] char(1)
                         );

But when I try to fetch the next result into @currentId

FETCH NEXT FROM idCursor INTO @currentId

I get this error:

Must declare the scalar variable "@currentId".

How can I get the result of a cursor fetch in a table? I understand that I can declare two variables and store the results individually. But for a table with many columns that would be time consuming.

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert:

FETCH NEXT FROM idCursor INTO @id, @data;
INSERT @currentId([id],[data]) SELECT @id, @data;

However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table variable with whatever query originally populated #tempRemaining?

INSERT @currentId([id], [data])
SELECT DISTINCT [id], [data]
    FROM #tempTable;

Or even just using #tempTable by itself and skipping the table variable altogether? Maybe even skipping the #temp table? A cursor combined with a table variable and a #temp table just sounds like a nightmare.

At the risk of sounding like Celko, this seems very much like flat file processing from the 1970s... and it's even worse that there are so many columns that declaring those variables would be prohibitive.


Bad execution plan when having partial index and big In-clause in Postgres Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements.

Given the following table:

create table foo(id bigint primary key, bar bigint);

insert into foo (id, bar)
select g.id, case when id % 1000 = 0 then id else null end
from generate_series(1, 10000000) AS g (id) ;

--Create partial index
create unique index ix_foo_bar on foo(bar) where bar is not null;

analyze foo;

And the given the following query with a large in-statement:

explain analyze select count(*) from foo where bar in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101);

The query plan shows a sequential scan. It is slow and and have a high cost:

QUERY PLAN
------------------------------------
 Finalize Aggregate  (cost=612955.35..612955.36 rows=1 width=8) (actual time=254.605..254.605 rows=1 loops=1)
   ->  Gather  (cost=612955.13..612955.34 rows=2 width=8) (actual time=254.474..258.242 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=611955.13..611955.14 rows=1 width=8) (actual time=247.743..247.744 rows=1 loops=3)
               ->  Parallel Seq Scan on foo  (cost=0.00..611955.03 rows=42 width=0) (actual time=247.740..247.740 rows=0 loops=3)
                     Filter: (bar = ANY ('{1,2,3,4,5(...)
                     Rows Removed by Filter: 3333333
 Planning Time: 0.867 ms
 Execution Time: 258.323 ms

set enable_seqscan has no effect - it still does a sequential scan.

If I add a 'not null' to the query, it uses the index:

explain analyze select count(*) from foo where bar is not null and bar in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101);

Index only scan:

    QUERY PLAN
----------------------------------------------------------------------
 Aggregate  (cost=153.55..153.56 rows=1 width=8) (actual time=0.267..0.267 rows=1 loops=1)
   ->  Index Only Scan using ix_foo_bar on foo  (cost=0.29..153.55 rows=1 width=0) (actual time=0.262..0.262 rows=0 loops=1)
         Index Cond: (bar = ANY ('{1,2,3,4,5,6 (...), 101}'::bigint[]))
         Heap Fetches: 0
 Planning Time: 0.531 ms
 Execution Time: 0.319 ms
(6 rows)

It also uses the index if I only have fewer elements in the in-clause (the cut-off is at 100 vs 101), or if I have a complete index instead of a partial one.

Why doesn't Postgres use the partial index when I have an in-clause with more than 100 elements? Is this a known limitation of the query planner, or  is it a bug?

This will be fixed in version 12, which will be released soon.

I think the summary here is that we are only willing to do so much work to try to prove a partial index can be used, because all queries have to go through that work even if they don't end up using the partial index.  In this change, they just found a more efficient way to do that work in this particular case, and so no longer impose the 100-element limit to it.


PostgreSQL vs MySQL benchmarks when a 2nd level cache is included Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by Memcache, EhCache, etc) is enabled?

I've seen a lot of benchmarks comparing and MySQL performance (most of which are outdated but that's besides the point) but none of them ever seem to factor in a 2nd level cache.  That makes the benchmark entirely irrelevant to me because any highly scalable system that relies on a relational database is going to need a 2nd level cache.

PS Let me know if this is not the site to ask this question and it belongs on one of the many other StackExchange sites instead.  Please migrate the question as usual if that's the case, thanks.

Left unconfigured, PostgreSQL will outperform MySQL. In fact, left unconfigured, InnoDB in some older versions of MySQL are better than newer versions of MySQL.

Tuning PostgreSQL and MySQL and then Benchmarking for occassional usage in the presence of memcached is a lot like building two cars, drag racing them, and then parking them forever. That only leaves the open road and whatever happens to be in the middle of highway.

If you have a large working set and high turn around of data, then perhaps memcached and other second-tier caches woiuld be unnecessary. From that point forward, choice of database and benchmarking would be worthwhile and intriguing.


What rights do you need for Ola's integrity check? I'm configuring a new server and Ola's integrity check is working for user databases (I have restored a database of around 50 GB for tests) and is failing for system databases. If I look in the logs it works for model and fails for master and msdb with The database could not be checked as a database snapshot could not be created...

The SQL Agent account is sysadmin. It works if I add the SQL Server and SQL Agent account as administrators of the Windows Server, so it's some rights missing somewhere. It's a VM running Windows Server 2016 version 1607, I don't have access to the Hyper-V host but it's probably the same version.

Same result with SQL Server 2017 and SQL Server 2014. Seems to be caused by Windows Server 2016, maybe some group policies settings.

This sounds like more of an issue with DBCC CHECKDB being run than anything specific to Ola's.

Since you're probably creating snapshots, the service account will need to have access to the database directory folders in order to create snapshots there. You could work around this temporarily by peforming a physical only CHECKDB as well.


How to get SQL Server Profiler to show amended tables The ASP.Net application I'm maintaining at the moment has a lot of activity in the database when ever a postback occurs. For the example I'm currently looking at, Profiler shows a mass of activity just to refresh the UI but I know its also updating a couple of tables.

What would be the best trace filter to only show the sprocs/statements that are either inserting, updating, deleting?

Is there a way to just show the table that are I/U/D (but I doubt this is possible)?

Thanks for your help on this.

Try a trace on SP:Completed and RPC:Completed with column filters on TextData for:

% insert %
% delete %
% update %

Might get the odd spurious result but it'll narrow the field. Add SQL:BatchCompleted if you have a mix of procs and statements.


Delete duplicates with specific columns as identifier I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512))..

My amateur-ish SQLfoo lead me to this non-functioning query (for testing reasons I just selected the duplicates)

SELECT num, com FROM TableA
WHERE num, com IN
    (
        SELECT count(*) AS co FROM TableA WHERE co > 1 GROUP BY num, com
    )

How can I delete duplicates from TableA identified by num and com?

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

edit

from the suggestions I've tried the following two queries. TableA features ~220 entries, where ~80 are duplicates.
first @dimitars suggestion

select ROW_NUMBER() OVER (PARTITION BY [num], com ORDER BY [num], com) as 'RowNumber', t1.*
    into ##TableA
    from TableA t1

delete t1
--select t1.*
from TableA  t1
join ##TableC t2
    on t2.[num]=t1.[num] and t2.com=t2.com
where t2.RowNumber > 1

resulting in every entry being deleted. And @marcelo miorellis suggestion

select * from TableA;
with cte as (
    select *, row_number() over (partition by [num], com order by [num], com ) as Picker
    from TableA
    )
select cte
where Picker > 1

which gives me

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Picker'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'cte'.

--=======================================================
-- delete the duplicate records from table @t
-- keeping a single unit of each
-- marcelo miorelli 24-nov-2014
--=======================================================

--=======================================================
--create a table variable and insert records in it
-- just for this example
--=======================================================
declare @t table ([num] int, com varbinary(512) )

insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 3, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )

--=======================================================
-- this is going to show the table before the deletes
--=======================================================
select * from @t

--=======================================================
-- this deletes the duplicates - leaving only 1 of each
--=======================================================
; with cte as (
    select *
        , row_number() over (partition by [num],com  order by [num],com ) as Picker
    from @t
    )
delete cte
where Picker > 1

--=======================================================
-- this shows the table after the deletion - without the deletes
--=======================================================
select * from @t


Set columns dinamically in UTL_FILE I've been stuck with this problem, so I'm looking for help now.

I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to pass all the column names to UTL_FILE.PUT_LINE(), and there are some columns that are set dynamically, so I don't know how to pass those columns to UTL_FILE.

I tried to build a string with those columns, but it doesn't work.
Example:

    FOR pointer IN (SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1)

    LOOP
      UTL_FILE.PUT_LINE(v_file, pointer.column1|| ',' ||pointer.column2|| ',' ||**string_with_dynamic_columns**|| ',' ||pointer.column3);
    END LOOP;
 UTL_FILE.FCLOSE(v_file);

The query works well and, except for those dynamic columns, I can get the .cvs file.

Any suggestions?

I hope i explained myself well

It would be something like this:

DECLARE
    cur SYS_FERCURSOR;
    curid NUMBER;
    desctab DBMS_SQL.DESC_TAB;
    colcnt NUMBER; -- total number of columns
    res NUMBER;

    namevar VARCHAR2(4000);
    numvar NUMBER;
    datevar DATE;
    ... more IF NEEDED
BEGIN

    OPEN cur FOR 'SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1';
    curid := DBMS_SQL.TO_CURSOR_NUMBER (cur);

    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
    FOR i IN 1..colcnt LOOP -- loop over all columns
        IF desctab(i).col_type = 1 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar);
        ELSIF desctab(i).col_type = 2 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
        ELSIF desctab(i).col_type = ... THEN
            .......
        END IF;
    END LOOP;
    -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
           IF (desctab(i).col_type = 1) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
              UTL_FILE.PUT(v_file, namevar|| ',');
           ELSIF (desctab(i).col_type = 2) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
              UTL_FILE.PUT(v_file, numvar|| ',');
           ELSIF (desctab(i).col_type = 12) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
              UTL_FILE.PUT(v_file, datevar|| ',');
           ELSIF ....
              ...
           END IF;
       END LOOP;
       UTL_FILE.NEW_LINE;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);

END;

In order to trim the last comma you may use

IF i < colcnt THEN
    UTL_FILE.PUT(v_file, namevar|| ',');
ELSE
    UTL_FILE.PUT(v_file, namevar);
END IF;


Schema to generalise potentially parametrised status Let’s say I have some entity:
create table entities (
  id serial primary key,
  foo text
);

...and I want to assign it some state, which has N possible values. Some of said states also come with a parameter. For example:

foo
bar
quux, 10
quux, 20
quux, 100

I original set this up as a separate table, with the states and parameters populated by the application at runtime. Then this is just simply referenced in the status table:
create table status (
  entity integer references entities(id),
  state integer references states(id),
  primary key (entity, state)
);

The problem with this is that the semantics of the states are now lost, so it’s up to the application to understand things.
As an alternative, I created an enumeration type of the states and an optional parameter key:
create table status (
  id serial primary key,
  entity integer references entities(id),
  state state_enum not null,
  param integer default null,
  unique (entity, state),
  unique (entity, state, param)
);

I haven’t written it out here, but I also put an elaborate check constraint on param to ensure it’s only non-null when the state is “quux”.
(Rather than using an enum, I could have a lookup table with the states as hard-codes records; not the parameters as they have application-specific meaning. Either way, it’s equivalent.)
Now the states are explicitly enumerated by the schema, one doesn’t have to rely on the application to define semantics. However, it’s really messy in my opinion.
Is there a more elegant way to design this?
(If it makes a difference, in terms of features, I’m using PostgreSQL.)

Define a StatusType that will migrate through to a StatusLevel and provide a mechanism for a CHECK constraint.

DML (I'm doing this freehand, there may be syntax errors lurking):
CREATE TABLE StatusType
(
  StatusTypeCd  TEXT     NOT NULL
 ,Description   TEXT     NOT NULL
 ,CONSTRAINT PK_StatusType PRIMARY KEY (StatusTypeCd)
)
;
INSERT INTO StatusType VALUES ('N','Non-parameterized'),('P','Parameterized');

CREATE TABLE Status
(
  StatusCd      TEXT  NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,Description   TEXT  NOT NULL
 ,CONSTRAINT FK_Status_Classified_By_StatusType FOREIGN KEY (StatusTypeCd) REFERENCES StatusType (StatusTypeCd)
 ,CONSTRAINT PK_Status PRIMARY KEY (StatusCd,StatusTypeCd)
 ,CONSTRAINT AK_Status UNIQUE (StatusCd)
)
;
INSERT INTO Status VALUES ('Foo','N','Foo description'),('Bar','N','Bar description'),('Quux','P','Quux description');

CREATE TABLE StatusLevel
(
  StatusCd      TEXT  NOT NULL
 ,Level         INT   NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,CONSTRAINT FK_StatusLevel_Level_Of_Status FOREIGN KEY (StatusCd, StatusTypeCd) REFERENCES Status (StatusCd, StatusTypeCd)
 ,CONSTRAINT PR_StatusLevel PRIMARY KEY (StatusCd,Level)
 ,CONSTRAINT CK_StatusLevel_Is_Valid CHECK (StatusTypeCd = 'P' OR Level = 0)
)
;
INSERT INTO StatusLevel VALUES ('Foo','0','N'),('Bar','0','N'),('Quux',10,'P'),('Quux',20,'P'),('Quux',30,'P');

From there you would migrate StatusCd,Level directly to entity or to an optional attribute entity_status:

Notes:

There should be unique constraints on names/descriptions.
Three tables? Yes.  But it keeps you from doing something dumb and forces you to think about what you're doing.
There are no Id columns for StatusType/Status/StatusLevel as they are unnecessary.


Log shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning I wish to implement log shipping from my current primary which is a SQL Server 2014 Enterprise database to a SQL Server 2017 Standard edition database. I also wish to implement table partitioning on my primary (2014 Enterprise) database. Table partitioning only became available at Standard edition level from SQL Server 2016 Standard SP1 onward.

I believe this will work I need to test it first. My problem is that SQL Server Evaluation edition seems to only correspond to Enterprise/Developer edition or at least that is my understanding. I do not have access to 2017 Standard edition without purchasing but do not want to purchase it without knowing if my proposed log shipping implementation would work for sure.

Has anyone else implemented similar, or does anyone know how to restrict an evaluation edition so that it corresponds to Standard?

I believe this will work I need to test it first. My problem is that SQL Server Evaluation edition seems to only correspond to Enterprise/Developer edition or at least that is my understanding. I do not have access to 2017 Standard edition without purchasing but do not want to purchase it without knowing if my proposed log shipping implementation would work for sure.

According to the Docs, Express Edition can also handle table partitioning in 2016 SP1+. Thus you could setup an extremely small database, create a new partitioned table, and log ship it from 2014 Enterprise Edition to 2017 Express Edition and see if it works. Express edition is free and can be easily downloaded and installed.

If it works, then it'll work in Standard. If it doesn't, then it won't work in standard and it has some but not all feature parity across editions in 2016 SP1+ with partitioning being one of those that is available across all editions.


IO_STALL question and understanding I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO.

In one 5min period I get a delta of 5826331 ms which is 97minutes.

I am a little confused by this, is this saying that an operation started 97mins ago only just finished at that point and hence recorded that wait time?

Thanks

Added code as requested:

/*

USE [SysDBA]
GO
*/
/****** Object:  Table [dbo].[DISKIOPS]    Script Date: 04/07/2013 11:40:15 ******/
/*
DROP TABLE [dbo].[DISKIOPS]
GO
*/
--Create the table
/****** Object:  Table [dbo].[DISKIOPS]    Script Date: 04/07/2013 11:40:15 ******/
/*
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DISKIOPS](
    [IO_STALL] [bigint] NULL,
    [IO_STALL_READ_MS] [bigint] NULL,
    [IO_STALL_WRITE_MS] [bigint] NULL,
    [NUM_OF_READS] [bigint] NULL,
    [NUM_OF_WRITES] [bigint] NULL,
    [SIZE_ON_DISK_MB] [bigint] NULL,
    [DBNAME] [varchar](max) NULL,
    [NAME] [varchar](max) NULL,
    [FILE_ID] [int] NULL,
    [DB_FILE_TYPE] [varchar](max) NULL,
    [DISK] [varchar](max) NULL,
    [FILE_LOCATION] [varchar](max) NULL,
    [TIMESTAMP] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

*/

--Capture IO information from DMV and query to find deltas over time.
/*
USE [SysDBA]
GO

INSERT INTO [dbo].[DISKIOPS]
           ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP])
SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location,
b.physical_name AS File_location,
GETDATE() AS Timestamp
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
GO
*/
DECLARE @File_Name VARCHAR(8000),
        @Disk VARCHAR(5)
SET @File_Name = 'DBTEST'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP]
           ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)

SELECT MAX([IO2].[IO_STALL] - [IO1].[IO_STALL])
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
AND IO1.Disk = @Disk

Question comment pasted below:

  io_stall by itself doesn't mean too much. If in 10 seconds you had 1000 operations stalling for 1 second each, you'll have 1000 seconds of stalls. That would be 16+ minutes of stalls in 10 seconds. You need to correlate this with IO operations...

The above is a pretty good example of how you can see monumental and seemingly exaggerated numbers.  By itself, io_stall doesn't really mean anything.  You need to know the scale of I/O operations for that cumulative stall.

Instead of having this:

SELECT MAX([IO2].[IO_STALL] - [IO1].[IO_STALL])
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
...

You need to divide the stall by I/O operations to get the average stall per I/O (or per read, or write, or whatever granularity you're looking for).  In other words, my recommendation would be to modify your query to look something like this:

SELECT
    MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)

And then you need to have an extra predicate clause to ensure you're not dividing by zero:

...
WHERE IO1.NAME = IO2.NAME
and (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk

What this basically does is calculate the average io_stall per I/O operation.  By itself, a high io_stall could simply mean a higher workload and not necessarily the sign of a problem.


Why does sqlcmd truncate varbinary(max)? Inside of the sys.assembly_files catalog, I can see a field named content

1> SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
2> GO
assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x07585

(1 rows affected)

However, even with sqlcmd -Y 8000 -y 5000 I can't get more than 0x4D5A90x075850x167D00x043CB

assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x075850x167D00x043CB

Wondering what was going on and suspecting the client wasn't returning everything to me, I tried mssql-cli, the port of pg-cli to SQL Server. Using mssql-cli, I saw the entirety of the content field, which does in fact look to be the right size to be the bit contents. That leaves me to believe this is a problem in sqlcmd.exe truncating, or other mutilating and adulterating the bytecode representation of the assembly. Using mssql-cli, I get this

master>SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
......
-[ RECORD 1 ]-------------------------
assembly_id      | 1
(No column name) | microsoft.sqlserver.types.dll
file_id          | 1
content          | 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000D00000000E1FBA0E00B409CD21B........ and on and on
(1 row affected)

Why does sqlcmd truncate varbinary(max)?

Because it's it might be GBs of data, so the query tools like SQLCMD and SSMS truncate the results for display.

You can dump to a file with bcp.exe, setting the "prefix-length" to 0.

bcp "select content from sys.assembly_files where name = 'microsoft.sqlserver.types.dll'" queryout microsoft.sqlserver.types.dll -T

Enter the file storage type of field content [varbinary(max)]:
Enter prefix-length of field content [8]: 0
Enter length of field content [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (16.13 rows per sec.)


MySQL: How to perform aggregate functions on rows where a column is duplicate, for all values of said column I have a table where one of the columns may have duplicate entries, and I want to condense each set of duplicates into a single row, with an aggregate function applied to the other columns. I cannot think of a sensible explanation of this, so here is an example data table and result table

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1                  |
| 1                      | 2                  |
| 2                      | 3                  |
| 3                      | 4                  |
| 3                      | 5                  |
| 3                      | 6                  |
+---------------------------------------------+
output

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1.5                |
| 2                      | 2                  |
| 3                      | 5                  |
+---------------------------------------------+

All of the rows where column_with_duplicates is 1 had column_to_aggregate averaged together into a result row. The same happened for all values of column_with_duplicates

Since I had so much difficulty describing this, I couldn't effectively research the issue.

It seems like your query needs to be something like:

SELECT
  column_with_duplicates
  , AVG(column_to_aggregate) AS column_to_aggregate -- AVG does the math, AS gives the name
FROM <table name>
GROUP BY
  column_with_duplicates;

More information on creating aliases (the AS keyword).

More information on GROUP BY

More information on the AVG function with a handy example that would have served you well.

Since you had a question about the GROUP BY clause in the comments below...

As I understand it MySQL has some quirks with it's GROUP BY rules. Those quirks not withstanding, I think of a simple GROUP BY clause, like the one in the query above, as sorting the result set into buckets. So in our query above there would be one bucket for each unique value in the column_with_duplicates column. So each value in the column_to_aggregate would get dropped into the proper bucket. Once all the rows/values are sorted into their buckets then an average is taken of all the values in each bucket individually.

Of course this is just a conceptual model, but it should give you a basic understanding of the role of a GROUP BY clause.


Precision for average_disk_seconds_per_read? In SQL Server Extended Events, event = perfobject_logicaldisk, what is the actual unit of time for average_disk_seconds_per_read and average_disk_seconds_per_write?

All of the documentation I've found says "seconds", but my sample data includes values like these:  87708875, 1573358215, etc.

It's probably microseconds. I exported the target data to Excel and divided it by 1,000,000,000,000, which gives numbers that seem to be correct for microseconds, but I want confirmation before use such an xEvent query.

Extended Events are notorious for being poorly documented.  Duration / units is especially so, see:

Extended event duration Millisecond or Microsecond?

Despite the fact that "seconds" is in the event field name, you're correct that the value being reported is nowhere near seconds.  You mentioned that you have to divide the number by a trillion (a 1 with 12 zeroes after it).  Rather than microseconds, that actually means the number is in picoseconds.  Which sounds...crazy.  But I think it's right.

To confirm this, I ran SELECT * from dbo.Posts; on the StackOverflow2010 database while running three different trace tools simultaneously and compared the results.

Extended Events: perfobject_logicaldisk

This event gets logged every 15 seconds.

For the "C:" instance_name at timestamp 09:44:43 (thus a span starting at 09:44:28), I got an average_disk_seconds_per_read of 3,839,009,107 during that 15 second interval.

For the "HarddiskVolume1" instance_name at that same timestamp, I got average_disk_seconds_per_read of 2,239,089.

There's also an entry for instance_name "HarddiskVolume3" with average_disk_seconds_per_read of 1,046,233.

I think those two volumes are system reservations and restore partitions respectively.  For reference, here's the output from DiskPart on my machine

C:\WINDOWS\system32>diskpart

Microsoft DiskPart version 10.0.17134.1

Copyright (C) Microsoft Corporation.
On computer: *****************

DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D                       DVD-ROM         0 B  No Media
  Volume 1         System Rese  NTFS   Partition    500 MB  Healthy    System
  Volume 2     C                NTFS   Partition    977 GB  Healthy    Boot

PerfMon.exe: LogicalDisk -> Avg. Disk sec/Read

This Windows performance counter object is documented as being reported in seconds.  I only had it log the "C:" instance name.  I see values around .002 seconds (2 milliseconds) on average during the period mentioned above.

PerfView: Windows Kernel/DiskIO/Read ETW event

PerfView samples much more aggressively than the other methods here (see screenshot with a snippet of the details below).  Dumping that output (which is filtered to only include I/O done by the SQL Server process) into Excel indicates that the average seconds per read was 2.7 milliseconds over the entire sample.

What does it all mean?

ETW Tracing and Performance Counters, which are well known Windows components for measuring various performance metrics, indicate that reads were taking on average somewhere between .002 and .003 seconds (2-3 milliseconds).

Since the value we got from extended events was 3,839,009,107 I think we can assume it's trying to tell us .003 seconds (closer to .004).  Backing in to that number, we'd have to divide by a trillion to get there.

I might be bad at math, but it looks like the number reported by this EE field is in picoseconds.

Terrible.  Just...terrible.


Boolean field in OLAP cube What is the best practice for presenting boolean data in an OLAP cube? Should I create a boolean dimension containing two rows, true and false?

Go ahead and create a dimension.  It may sound strange at first, but think of the end user who ends up selecting a radio button for "male/female", or "sold/unsold", etc.


How to change the mount point for a column in postgresql table? This is my scenario

I have a table t with following columns

id  int
link  varchar(500)
name  varchar(20)
sal   text

In the link column we will store the location for the pdf files(mount point)

Ex: link  /ABC/ABC_DATA/Mydata/Test/a1.pdf

there will be one link for every id

Application Users have changed their pdf file location from "/ABC/ABC_DATA/Test/a1.pdf" to "/CBF/CBF_DATA/Documents/Test_DATA/a1.pdf"

Now how can i update the table with new location for all the rows. Table contains nearly 10,000 rows

Note:id and link are related every id contains unique link.

Maybe I'm missing something, but it sounds as if that is a simple case of replacing the value:

update the_table
   set link = replace(link, '/ABC/ABC_DATA/Test/', '/CBF/CBF_DATA/Documents/Test_DATA/');


Fetch the newest row grouped by a column in MySQL My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set:

log_table

+--------+-----------+------------------+---------+
| log_id | entity_id |       date       | comment |
+--------+-----------+------------------+---------+
|      1 | A         | 2012-10-23 07:50 | foo     |
|      2 | B         | 2012-10-23 07:59 | bar     |
|      3 | B         | 2012-10-23 08:11 | baz     |
|      4 | A         | 2012-10-23 08:23 | bat     |
+--------+-----------+------------------+---------+

Say I wanted to get the latest date of log entries for each entity so that the result looked like:

Results:
+-----------+------------------+--------------+
| entity_id |  last_log_date   | last_comment |
+-----------+------------------+--------------+
| B         | 2012-10-23 08:11 | baz          |
| A         | 2012-10-23 08:23 | bat          |
+-----------+------------------+--------------+

I'm currently using MySQL that looks something like:

SELECT
  `entity_id`,
  `date` AS last_log_date,
  `comment` AS last_comment
FROM (
  SELECT *
  FROM `log_table`
  ORDER BY `date` DESC, log_id ASC
) AS `ordered_log`
GROUP BY `entity_id`

This works fine but it doesn't seem very efficient to me, there has to be a better way of doing this, surely?

Create a Subquery that Gathers Keys from the the log_table with Maximum Date Per Entity. Then, perform an INNER JOIN of that Subquery back to the log_table.

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A INNER JOIN B USING (entity_id,last_log_date);

Give it a Try !!!

You can really speed this up if you have a compound index like this

ALTER TABLE log_table ADD INDEX entity_date_ndx (entity_id,last_log_date);

Indexes on each separate column may yield index merges. This compound index will bypass that.

Please try LEFT JOIN instead of INNER JOIN

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A LEFT JOIN B USING (entity_id,last_log_date);