Creating and dropping partition in PostgreSQL "on the fly"? In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative partitioning for largest tables. Tables partitioned by forecast identifier. Partition creating and dropping is made "on the fly" when users create or delete forecasts. However, I doubt if creating partitions in this way is good idea.
EDITED. We do not use creating partition by BEFORE INSERT trigger. We create or drop section on backend after user start creating or deleting forecast on frontend of our web-application.
EDITED 2. Backend in our web-application is web-server, that works with PostgreSQL 12 database.

Usually it is the best thing to create partitions for the data you expect ahead of time and handle the error when unexpected data arrive.
There is also the option of a default partition that receives data that belongs into no other partition, but that is usually only a good idea if you have a fixed number of partitions, because conflicting data in the default partition prevents creation of a new partition.


SQL-Server database health-check I'm new one with SQL Server, and I have the following question:

My database is not big, about 1 GB, and I make regular backups with the help of third party tool. How can I be sure that if a failure occurs I will be able to restore my database with the minimal loss?

The only way to be 100% certain is to restore the backups. If you are restoring to the same SQL Server instance then you will want to restore with a new database name and make use of the MOVE restore syntax to restore the .mdf and .ldf files to a different directory.

Alternatively, you can restore to a separate SQL server instance with the same database name. Again you will need to consider if the MOVE syntax is relevant.

See Retore SQL Server database to different filenames and locations for help.

For added piece of mind, you should create all your backups with checksum and after validate the metadata consistency with a RESTORE VERIFYONLY check. The restore verify can be automated by using using Ola Hallengrens excellent backup solution SQL Server Maintenance Solution


pg_restore error from pgadmin3 - Postgresql i have taken a backup of a postgresql db from a system1 using pgadmin3 tool.  The backup file created is of extension  ".backup"

I am trying to restore it on an another system2 installed with postgresql 9.0 and pgadmin3 1.8
but iam getting the error

  could not execute query: ERROR: unrecognized configuration parameter
  "lock_timeout" Command was: SET lock_timeout = 0;

configuration of both the systems

system1
win7-64bit
postgresql 9.0.13-1 - 64bit
pgadmin 1.8

system2
win7-32bit
postgresql 9.0.13-1 - 32bit
pgadmin 1.8

lock_timeout is a new parameter that appeared with PostgreSQL 9.3

Per the release notes:

  Add configuration variable lock_timeout to allow limiting how long a
  session will wait to acquire any one lock (Zoltán Böszörményi)

That would indicate that pgadmin is using a PostgreSQL 9.3 pg_dump command to take the backup, instead of 9.0.

This is recommended only when migrating to 9.3. Since the target server is 9.0, better use the pg_dump binary that has been installed with the 9.0 server.

The path of external binaries can be set in pgAdmin preferences. To fix the problem, you may set it to the bin directory of your 9.0 installation and try to backup again.

Other than that, as a one-time fix you may edit the dump file if it's in plain text, remove the offending line SET lock_timeout = 0; in the hope that it's the only problem caused by the version mismatch.


Performance gain by moving a large table from a database and referencing with a synonym We have SQL Server 2008 R2 database which is currently about 80Gb.

About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own database, and created a referencing synonym so that existing queries and SPs remained functional.

This database is ripe for an overhaul anyway, so that may be the obvious answer but I wondered if the synonym idea would be a quick win.

You don't have to move it to it's own database. You may be able to get a performance improvement by simply moving it to it's own filegroup within the existing database.  The filegroup could then contain one or more files that are on a separate disk(s) from the ones the rest of the database is on.


Help with MySQL my.cnf config So I'm running MySQL on a high availability cluster with the MySQL data directory on a GlusterFS.

I want to:

- Disable the query cache (--query-cache-size=0)

- Switch on external locking (--external-locking=TRUE)

- Disable delays to writing (--delay-key-write=OFF)

How would I express these settings in a my.cnf file?

I would like to edit /etc/mysql/my.cnf to my taste and then do service mysql start

to disable query cahche you need add the following prameters to cnf file:

query_cache_size=0
query_cache_type=0

to skip external lock you need to comment or remove skip-external-locking

and to disable delay key write you need to add the following parameter

delay-key-write= OFF


how to filter the resulted table in dbeaver? I have big sqlite table that I filtered one column with IN statement from the GUI. It worked will but I want to filter another second column also based on the first selected. eg full table looks like.

A    v
A    c
A    n
B    o
B    i
B    k
C    l
C    a
C    z

after first query

A    v
A    c
A    n
C    l
C    a
C    z

I want to filter this second column only (without the o, i, k available in selection menu).
I found the  best solution is the CREATE TABLE, name it, then use it.

However, I don't know where this table is saved. I can't find it in the explorer however it is there when I SELECT it.

What would you do if you have to do that?

You can filter on multiple columns with DBeaver, either on tables or query results.

The menu is called "Custom filter" and is located at the top right of the query result:

From there you can specify custom filters as you need:


Update Using a cursor I know that instead of cursor we can have the tables updated with different queries but I wanted to try using cursor so as to have a knowledge on cursor
This was what I was trying I have inserted a new column which has null values instead of null I wanted to update that column based on the title column here what I used the query but I am unable to update the table can someone suggest a solution for this.

Declare @Title varchar(max)
Declare @Department varchar(max)

Declare Department_Cursor Cursor
For
    Select Title, Department
    from Sample_table

Open Department_Cursor
Fetch Next From Department_Cursor Into @Title, @Department

While @@fetch_status = 0
Begin
     Update sample_table
     Set @Department = 'Production'
     where @Title = '%Production%'

     Fetch Next From Department_Cursor Into @Title, @Department
End

Close Department_Cursor
Deallocate Department_Cursor

I tried running what you have and noticed one small error:

where @Title = '%Production%'

So if @Title ever actually = '%Production%' it will update the entire table.  Until then it's going to update 0 rows.  My guess is what you are trying to do is something like this:

WHERE @Title LIKE '%Production%'
  AND Title = @Title

And on further inspection I don't think you want this either

SET @Department = 'Production'

You probably mean

SET Department = 'Production'

Which also means you don't need to be pulling Department at all in your cursor.  Last but not least, since you are updating the table that the cursor is pulling from I would use a READ ONLY cursor.  This does however write the cursor results to tempdb so it may not be such a good idea if you have a large table.

Last comment: Cursors are not always bad.  But they should only be used where batch processing is not possible.  Say executing a stored procedure with multiple inputs.  I think it's great that you are being proactive and practicing them however.


Queue Implementation with Custom Consistent Absolute Ordering I need to create a schema for a queue. Elements in this queue will be processed in an order based on a column. However, this is not always the e.g. date_added column.
Users should be able to freely re-order items in the queue, thus setting a custom position - let's call that field position. Therefore, queue items that have a position >= to the new position I set for a specific item need to be re-ordered on each update.
I really want to avoid using a server-side script to manually go through each of the now outdated elements and updating their position.
Is there a way to do this automatically, and with relatively low resource usage? I'm sure people have implemented such a queue in MySQL before, but there don't seem to be relevant answers anywhere, although the task sounds trivial. Any ideas?
I know I shouldn't rely on the order the records are stored in. But I am maybe considering using ALTER TABLE ORDER BY. How bad is this really? And how useful is it in achieving my goal?
There's some PK, likely an auto-increment, or maybe the date_added with more precision. The version is InnoDB, MySQL >= 5.6.
The queue position can be changed an unlimited number of times. It should work without failure. The UI with HTML and JS is trivial: On any change of immediate hierarchy within a container, you just read the order of elements with a specific attribute. It's also pretty common. Possibly hundreds of items in the list.
By "outdated elements" I mean those that no longer have the position field set correctly, and need to be "reindexed". Naturally, something in MySQL would deal with that.
As for finished, I need those to be retrievable too, so they stay in the table, and are marked as done with a boolean field. There is another table that takes care of more complicated status transition, as it is not a part of what makes a queue.

(I'll compete with myself.  Here's an Answer for a "large" number of items.)
Updating 1K rows is not very fast, due to saving rows in case of rollback.  Incrementing/decrementing the sequence numbers (column seq) for a range of rows, then changing one the sequence in the one row that was moved.  That's 3 UPDATEs -- one slow, one fast.
The UI would need to provide some way for the user to specify which item to move from some seq to a new seq.
A column in the table would contain the sequence number.  seq would effectively be UNIQUE, but saying so might slow it down.
Suppose the UI tells the db that "Seq=222 needs to move to after seq=567.  (after "0" for inserting at front.)
BEGIN;   -- need atomicity
-- Move item out of the way (assuming "0" is not otherwise used):
UPDATE t SET seq = 0 WHERE seq = 222;
-- Shift the items that need to move:
UPDATE t SET seq = seq - 1   -- "-" or "+" depending
    WHERE seq BETWEEN 222+1 AND 567;
-- move the item in question:
UPDATE t SET seq = 567 WHERE seq = 0;
COMMIT;

Moving the other direction needs a couple of changes.


Unable to perform queries through mongos I have setup a minimal replicated configuration with mongodb 3.6.3,it seems to be everything fine but when I connect to the mongos (which as far as I understand acts like a kind of load balancer in front of sharded clusters) and try to perform a query it raises this error: Cannot accept sharding commands if not started with --shardsvr (code: 193, codename: NoShardingEnabled)

This is how I've setup the servers:

3 servers: mongoserver1, mongoserver2 and mongoserver3

Launch the config server:

mongod --configsvr --replSet data --bind_ip localhost,${HOSTNAME}\
    --port 25000 --logpath /var/lib/mongodb/shardedcluster/log.cfg\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/cfg --fork

Log in the config server and initiate the replica:

rs.initiate(
  {
    _id: "data",
    configsvr: true,
    members: [
      { _id : 0, host : "mongoserver1:25000" },
      { _id : 1, host : "mongoserver2:25000" },
      { _id : 2, host : "mongoserver3:25000" }
    ]
  }
)

Launch the sharded replica:

mongod --shardsvr  --replSet data --bind_ip localhost,${HOSTNAME}\
    --port 26000 --logpath /var/lib/mongodb/shardedcluster/data.log\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/data --fork

Log in the sharde replica and initiate the replica:

rs.initiate(
  {
    _id : "data",
    members: [
      { _id : 0, host : "mongoserver1:26000" },
      { _id : 1, host : "mongoserver2:26000" },
      { _id : 2, host : "mongoserver3:26000" }
    ]
  }
)

And finally launched the mongos:

mongos --configdb data/mongoserver1:25000,mongoserver2:25000,mongoserver3:25000\
   --bind_ip localhost,${HOSTNAME}\
   --logpath /var/lib/mongodb/shardedcluster/mongos.log --fork

And inside the mongos:

sh.addShard("data/mongoserver1:26000,mongoserver2:26000,mongoserver3:26000")
sh.enableSharding("cmdb")

After adding some data on the cmdb database through the mongod running or port 26000 I've checked after stopping primary node I can continue working with date on the new primary node but I'm not able to do it connecting to mongos even it seems the cmdb database is replicated as shown by sh.status():

databases:
    {  "_id" : "cmdb",  "primary" : "data",  "partitioned" : true }

Well, finally I've solved the problem. It was my fault when configuring everything to give the same name data to the replicas of the config server and the sharded server.

I replaced data by rs0 when launching the config servers:

mongod --configsvr --replSet rs0 --bind_ip localhost,${HOSTNAME}\
    --port 25000 --logpath /var/lib/mongodb/shardedcluster/log.cfg\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/cfg --fork

Of course in the first rs.initiate changed _id to rs0 and finally when launching the mongos replaced data with rs0:

mongos --configdb data/mongoserver1:25000,mongoserver2:25000,mongoserver3:25000 \
    --bind_ip localhost,${HOSTNAME} \
    --logpath /var/lib/mongodb/shardedcluster/mongos.log --fork

Now it works like charm :)


SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum) Three separate processes have failed in the last two days with very similar errors:

Time-out occurred while waiting for buffer latch type 4 for page (1:1189832),
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827),
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827),
database ID 2.

Two of them are exactly the same, the other is accessing a slightly different pagenum.  Database #2 is tempdb. I tried to run DBCC PAGE(tempdb, 1, 1189827, 1) but the output is just gibberish to me. I'm fairly lost.

Does anyone know what the root cause of these timeouts might be? (I've seen dozens of suggestions on other forums, among which was memory corruption)

Any help would be greatly appreciated. If you would like to read the DBCC PAGE output, ask and I will post it.

By the time that you whip out dbcc page and try to look at the page, it's possibly/probably being used for something else anyway. It is tempdb, after all.

I've been at this since SQL 6.5 and I have only ever seen these sorts of page timeouts caused by slow or misconfigured storage. Every incident I can remember offhand, it's been a SAN. If you have a SAN, you should definitely talk to your SAN guy/gal and find out if they have been seeing unusual load on the system or if they have changed anything lately. I've seen problems with flaky fiber switches and cables, for example, or maybe they moved your data onto slower disks lately.

Beyond that, Have a hard look at your drives, SAN or otherwise. Look at the "Avg. Disk Sec/Read" and  "Avg. Disk Sec/Write" numbers for the drive that the tempdb MDF file is stored on while stuff is happening, preferably while you are getting errors. It's helpful to look at the bytes/second numbers as well, to get a feel for what the drives are being asked to do (a drive with 1000 ms latency numbers that is being asked to push only 1 MB/sec is one thing, a drive with 50 ms latency when pushing at 10 MB/s is another.)

If the drive is overwhelmed, you may need to reduce the I/O going to the drive. Tactics would include moving tempdb files to another device, creating additional tempdb data files on other devices, tuning queries (any unnecessary GROUP BY or DISTINCT?), moving any data files that are also on that storage device to somewhere else, etc.


Dump only the Stored Procedures in MySQL I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?

This should do it for you:

mysqldump -h... -u... -p... -n -d -t --routines --triggers --all-databases > MySQLStoredProc.sql

  -n, --no-create-db     Suppress the CREATE DATABASE ... IF EXISTS statement
                         that normally is output for each dumped database if
                         --all-databases or --databases is given.
  -d, --no-data          No row information.
  --triggers             Dump triggers for each dumped table.
                         (Defaults to on; use --skip-triggers to disable.)
  -R, --routines         Dump stored routines (functions and procedures).
  -t, --no-create-info   Do not write CREATE TABLE statements that create each
                         dumped table.

CAVEAT

It would be much better not to separate the stored procedures from the database so that specific stored procedures will be created in the database it was meant for. The same goes for triggers. This would be preferrable:

mysqldump -h... -u... -p... -d --routines --triggers --all-databases > MySQLStoredProc.sql


Mongo db custom role with different privilages on differenct collections I want to create a custom role which can just read one collection and readWrite all other collections. Don't have any delete permission on any collections.

Why this code won't work?
Should I add something like * in : collection:"" ?

db.createRole(
{
 role: "role1",
 privileges: [
   { resource: { db: "db1" ,collection:"" }, actions: [ "find","insert","update" ] },
   { resource: { db: "db1" ,collection:"col1" }, actions: [ "find"] }
 ],
 roles:[]
},
{ wtimeout: 5000 }
)

The only solution is to move that col1 to different DB, but then your application must, of course, read two different databases.


Why does this RX-X lock not appear in Extended Events? The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it is only released. Where does it come from?
The Repro
Here's my table:
CREATE TABLE dbo.LockTest (
ID int identity,
Junk char(4)
)

CREATE CLUSTERED INDEX CX_LockTest --not unique!
ON dbo.LockTest(ID)

--preload some rows
INSERT dbo.LockTest
VALUES ('data'),('data'),('data')

Here's my problem batch:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

INSERT dbo.LockTest
VALUES ('bleh')

SELECT *
FROM dbo.LockTest
WHERE ID = SCOPE_IDENTITY()

--ROLLBACK

I check locks held by this session, and see RX-X:
SELECT resource_type, request_mode, request_status, resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = 72 --change SPID!

But I also have an Extended Event on lock_acquired and lock_released. I filter it on the appropriate associated_object_id...there's no RX-X.

After executing the rollback, I see RX-X (LAST_MODE) released, even though it was never acquired.

What I've Tried

I looked at all locks in Extended Events - no filtering. No RX-X locks acquired.

I also tried Profiler: same results (except of course it gets the name right...no "LAST_MODE").

I ran the XE for lock escalations - it's not there.

There's no XE specifically for conversions, but I was able to confirm that at least the U to X lock conversion is captured by lock_acquired

Also of note is the RI-N that gets acquired but never released. My current hypothesis is that the RX-X is a conversion lock, as described here. There are overlapping key-range locks in my batch that look like they should qualify for conversion, but the RX-X lock isn't in the conversion table.
Where is this lock coming from, and why isn't it picked up by Extended Events?

The single row insert acquires an X (exclusive) lock on the new row.

The SELECT attempts to acquire a range-shared, key shared (RangeS-S) lock.

This request is reported by the lock_acquired Extended Event as mode = RS_S.

It is reported by the Profiler event class Lock:Acquired as mode 13 (LCK_M_RS_S).

The requested mode is combined with the existing exclusive lock mode in Lock::CalculateGrantMode in sqlmin.dll. There is no combined mode of range-shared, key exclusive (RangeS-X) so the outcome of the calculation is range-exclusive, key exclusive (RangeX-X), which happens to be mode 15.

The grant mode calculation above is performed just before the extended event is generated by lck_ProduceExtendedEvent<XeSqlPkg::lock_acquired>. Nevertheless, both Profiler and Extended Events log the requested RangeS-S mode, not the resulting lock mode RangeX-X. This is counter to the limited documentation, which says:

  Mode | int | Resulting mode after the lock was acquired.

The mode column of the extended event has no documentation at all, and the description in the meta data is blank. Perhaps Microsoft themselves weren't even sure of the behaviour.

I have often thought it would be more useful if lock events reported both the requested and resulting modes, but that is not what we have. The current arrangement makes it pretty much impossible to track and match up lock acquisition and release.

There might be a good reason for reporting locks this way. If it doesn't meet your needs, you could open a support case with Microsoft, or create an Azure Feedback item.

LAST_MODE

The mysterious LAST_MODE is something Erik Darling has remarked on before. It is the highest map_key value in the list of lock modes exposed by sys.dm_xe_map_values:

SELECT
    DXMV.map_key,
    DXMV.map_value
FROM sys.dm_xe_map_values AS DXMV
WHERE
    DXMV.[name] = N'lock_mode'
ORDER BY
    DXMV.map_key;

╔═════════╦═══════════╗
║ map_key ║ map_value ║
╠═════════╬═══════════╣
║       0 ║ NL        ║
║       1 ║ SCH_S     ║
║       2 ║ SCH_M     ║
║       3 ║ S         ║
║       4 ║ U         ║
║       5 ║ X         ║
║       6 ║ IS        ║
║       7 ║ IU        ║
║       8 ║ IX        ║
║       9 ║ SIU       ║
║      10 ║ SIX       ║
║      11 ║ UIX       ║
║      12 ║ BU        ║
║      13 ║ RS_S      ║
║      14 ║ RS_U      ║
║      15 ║ RI_NL     ║
║      16 ║ RI_S      ║
║      17 ║ RI_U      ║
║      18 ║ RI_X      ║
║      19 ║ RX_S      ║
║      20 ║ RX_U      ║
║      21 ║ LAST_MODE ║
╚═════════╩═══════════╝

The memory structure accessed via the DMV (using sqlmin!CMapValuesTable) is stored starting at the address sqlmin!XeSqlPkg::g_lock_mode. Each 16-byte entry in the structure contains the map_key and a pointer to the string returned as map_value by the streaming TVF.

The strings are stored exactly as shown in the table above (though not in that order). It seems to be an error that entry 21 has a map_value of "LAST_MODE" instead of the expected "RX_X". Erik Darling has reported the issue on Azure Feedback.


Why are my queries taking so long? In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python and SQLalchemy. I have 76,807,696 rows in my table. However a single query is taking upwards of 300 seconds.

My Database is defined as:

class LocationsLookupTable(db.Model):
    """Use this class to create the database Model for the locations lookup
table.
This class is used for creating the database table Model for the locations
lookup table. It will store the ID pair and their distance value. There
will also be a unique ID for each value in the database table.
"""

id = db.Column(db.Integer, primary_key=True, unique=True)
firstId = db.Column(db.Integer, nullable=False)
secondId = db.Column(db.Integer, nullable=False)
firstLocationGeoPointX = db.Column(db.Float)
firstLocationGeoPointY = db.Column(db.Float)
secondLocationGeoPointX = db.Column(db.Float)
secondLocationGeoPointY = db.Column(db.Float)
distanceValue = db.Column(db.Integer)

And I am querying it using the following query:

result = models.LocationsLookupTable.query.filter_by(
            firstId=firstId, secondId=secondId).first()

I have some theories as to why the performance is so bad.
The first is that I simply have too many rows.
The second is that a database is not suited for this task.
The third is that my query is not well formulated.
This is however my first attempt at using a Database of any kind so I would like to ask how I can speed up my query before I would make any large changes to my project.

Edit:
I solved my problem. @ypercubeᵀᴹ was indeed correct in that the solution was to index my columns that I was querying. SELECT statements are incredibly fast now by a huge margin, taking roughly 0.03 seconds. Thankyou.

I have some theories as to why the performance is so bad.

Lets see.

  The first is that I simply have too many rows.

Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.

  The second is that a database is not suited for this task.

Unlikely. We don't know which DBMS you use (Postgres? MySQL? SQLite? SQL Server? There are hundreds of them) but I think any of them could handle what seems to be a simple query.

  The third is that my query is not well formulated.

No, it seems good. The query produced will look like:

SELECT id, firstid, secondid, ..., distanceValue
FROM LocationsLookupTable
WHERE firstId = ?
  AND secondId = ? ;

  So, what is causing this?

If that is all the code that is creating the table, then what is missing is indexes. Lacking them, the database optimizer/planner has to create a query plan that scans the entire table. While the table is not big, it is not very small either.

Adding an index on (firstid, secondid) will improve performance a lot.

However 300 seconds looks too long, even for a table scan, so the hardware or the DBMS settings should be examined, too.


Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure? If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored procedure getting it's own execution plan?

If so, could this be potentially more globally efficient on my server if there are other ad-hoc queries that exactly match the dynamic SQL queries in my procedure (or even if there are other procedures that use dynamic SQL to call the same exact queries as well)?

Yes, the dynamic SQL you submit will be treated as ad-hoc. I.e., a hash is generated from the text, making it sensitive to everything (space, upper/lower, search arguments, etc).

If you happen to submit the exact same string as a regular ad-hoc (not dynamic sql from a proc), or from other procedures as dynamic SQL, then the plans can be re-used.

What is best for you, we can't say. An option for you is to use sp_executesql and parameterize your queries to allow for plan re-use. OTOH, you will now have parameter sniffing. Either "pay" for plan generation for every execution or re-use plans. Your choice.


Bitmask Flags with Lookup Tables Clarification I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic.

Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records.

Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results.

I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented.

Is this as simple as creating a lookup table that has all possible search conditions?

Example for 3 bits using (a & b) (Edit: Wrong bitwise op)

001,001
001,011
001,101
001,111
010,010
010,011
010,110
011,011
011,111
etc

The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows.

Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated.

Edit: A more concrete example using small data.

  Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.

Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &).

Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.

I partially agree with Aaron's comment - in the most general case for storing 21 unrelated pieces of information, you'd probably use 21 bit columns.  As a general solution, it may well be your best solution.  If you had multiple bitmask-ed varchar columns, that would translate to a row with possibly over a hundred bit flags. FYI, 21 bits get stored as 3 bytes when you don't define them as NULLable, removing the necessity for space in the NULL bitmap. Since you have multiple bitmask columns, you'd end up with every 8 bits mashed into a byte.

What SQL Server ends up doing with your multi-column queries is eventually a bunch of bitmasking routines (yes! SQL Server uses bitmasks, so they the concept per se can't be all bad!) but for average use cases, it makes life easier for you.

If we had more information about what types of queries you run, we may be able to better advise, because ultimately the use cases dictate the design.

If you persist with the COMPUTED column, I would persist and index it if you haven't already.  It helps some queries, such as

exact matches

WHERE computedInt = POWER(2, 6) -- bit position 7
AND matching on 15th bit and OR matching on 2 other bits (10th and 7th)

WHERE computedInt >= Power(2,14) AND computedInt < Power(2,15)
  AND computedInt & (Power(2,9) + Power(2,6)) > 0

But these are probably exotic samples and yet also real live in some cases.  It's certainly not too much worse than 21 individual bit columns, for which yes your statements could be easier to write, but remember that SQL Server has mashed them for storage into 3 bytes and will be doing the bit-unmasking anyway!  You would have thought if bit-masking were all bad (without exception) then SQL Server wouldn't be doing it, right?

EDIT

Re the scenario of

  Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.

it is more efficient and logically expedient to test all 4 bits at once and do a single integer based operation, e.g.

WHERE computedInt & (POWER(2,10)+POWER(2,5)+POWER(2,3)+POWER(2,1)) = 0 -- has none
WHERE computedInt & (POWER(2,10)+POWER(2,5)+POWER(2,3)+POWER(2,1)) > 0 -- has one or more

Hypothetically, if this were your most exercised query on the table, you might even group the four columns into another computed column and index it separately, making the bitmask unnecessary (just test the resultant int with =0 and >0).  You might even go further and just precompute the answer... horses for courses.


Generate the statistics of a table after truncating it and then repopulate it? Is it necessary to generate the statistics of a table after truncating it and then repopulate it?
I have a table with several million rows in Oracle.Every week new data is recharged, truncating the table and then loading the data.

Each time a new data load is made, I must generate the statistics again. Should I also rebuild the indexes?
Some columns are encrypted with TDE.
Thank you

As a general guidelines, it is usually recommended to do a gather stats on table whenever more than 10% of the rows are modified. So in your case, you will need to do a stats gather post data load.
The index stats gather are taken care of if we do table stats gather using cascade option.


Select sales from different stores and group by product I have three tables (SalesTable, StoresTable and ProductsTable) and the structure is the following:

--StoresTable
StoreId Name
1       Store 1
2       Store 2

--ProductsTable
Id    Description
1     Product 1
2     Product 1

--SalesTable
Store    Product    Qty
Store 1  Product 1  20
Store 1  Product 1  30
Store 1  Product 2  10
Store 2  Product 1  30
Store 2  Product 2  10
Store 1  Product 2  5

What I want is to display a report to show the sum of qty sales from each product and group by store. Something like this:

--Expected Result according above table data
Store     Product     Sales
Store 1   Product 1   50
Store 1   Product 2   15
Store 2   Product 1   30
Store 2   Product 2   10

At the moment I have the following query:

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, Sales, P.Description

This query returns something like this (according with my real data info):

Store    Sales  PRODUCT
STORE 1  12     PRODUCT 1
STORE 1  3      PRODUCT 1
STORE 1  24     PRODUCT 2
STORE 1  48     PRODUCT 2
STORE 1  132    PRODUCT 2

As you can see the problem is that is duplicating products, I think that maybe is a problem with my joins. What I'm doing wrong?

Take sales out of the group by

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, P.Description


IF IS NULL then SINGLE else keep the value of the field I am working with a DB in Access and I'd like to be able to fill the field in this row with a result of SINGLE if it is not already filled with another value.

I wrote Iif(MaritalStatus) is null, "SINGLE") but Access returned a result in which all of the fields were filled with SINGLE except those that had had a previous value of Married, Divorced, etc.

Try:

NZ(MaritalStatus,"SINGLE")

  From Nz Function in the documentation:

  You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Or you can use:

IIF(IsNull(MaritalStatus),"SINGLE",MaritalStatus)


Release SQL Server Memory usage How can release memory usage of sql server by command. my system have 16GB RAM, but when get huge query by sql server, memory usage become full. if I restart SQL Server service memory usage become free. but I want to release memory usage of sql server after each huge query execute.

SQL Server is supposed to use all the memory.  If you want to reserve memory for other things set the max server memory setting in SQL Server so that it'll stop eating up all the memory.


Optimize MariaDB query with table that self-joins I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why.

The query (which is generated by an application that would be difficult but not impossible to change) is:

SELECT t1.id id1, t2.id id2, 5 weight
FROM ar1 t1
INNER JOIN ar1 t2 ON (t2.contact_type = 'Individual' and t1.first_name = t2.first_name)
WHERE t1.first_name IS NOT NULL
 AND t1.first_name <> ''
 AND t1.contact_type = 'Individual'
 AND t1.id < t2.id GROUP BY id1, id2, weight

And a table with 100k rows.

[EDIT: added:] The puropse of the query is to provide a list of unique pairs of records that could be duplicates. Could be because the table has other fields, too, but this query is only looking at the first name field.

CREATE TABLE `ar1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_type` varchar(64) DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`first_name`,`contact_type`),
  KEY `k2` (`contact_type`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Table status:

Name            | ar1
Engine          | InnoDB
Version         | 10
Row_format      | Dynamic
Rows            | 102463
Avg_row_length  | 46
Data_length     | 4734976
Max_data_length | 0
Index_length    | 5275648
Data_free       | 0
Auto_increment  | <null>
Create_time     | 2020-04-21 09:22:1
Update_time     | <null>
Check_time      | <null>
Collation       | utf8mb4_unicode_ci
Checksum        | <null>
Create_options  |

EXPLAIN says this:

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t1
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k2
key_len       | 259
ref           | const
rows          | 51232
Extra         | Using where; Using index; Using temporary; Using filesort
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t2
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k1
key_len       | 518
ref           | od_civicrm.t1.first_name,const
rows          | 5
Extra         | Using where; Using index
2 rows in set

Notes:

There are only 8647 distinct first names in the table.
contact_type is Individual for 99.9% of the rows.
There's no need to group on a constant (weight), either. But ommitting that does not magically fix it either.
I've tried various index combinations.
I'm on MariaDB 10.1
The servers I've tried it on are not massive - but to my mind this is not a huge recordset?

The ANALYZE result can be downloaded

What the heck is the intent?  It seems to deliver the ids of all pairs of individuals with the same first_name.

It looks like a "group-wise" max, but not quite.

weight seems to be irrelevant since it is the constant "5".

INDEX(contact_type, first_name, id)

may speed it up, even with 99.9% of contact_types being the desired value.  The speedup is due to the index being "covering".  The order of the columns is important.

It is likely to return a million rows.  That, in itself, will take time.  And possibly choke the client.

Just looking for dups?

Perhaps you want

SELECT GROUP_CONCAT(id), first_name
    FROM t
    WHERE first_name != ''
      AND contact_type = 'Individual'
    GROUP BY first_name
    HAVING COUNT(*) > 1;

Alternatively, SELECT MIN(id), MAX(id), first_name ..., except that it does not give you all the ids when there are more than 2.

Optimal:

INDEX(contact_type, first_name, id)


Oracle procedure with input parameter that performs multiple SUBSTR I have a table Employee with the columns employee_id as type number, and  employee_name as type varchar.

I need a procedure that takes an input string to insert employees into the table. The procedure should read and analyze the string which will include multiple names separated by commas.

Example:

'adam,alex,hales,jordan,jackob'

Each name should be extracted up until the comma and inserted into the employees table. So basically using the input string above I should have the names inserted as in the following result:

 employee_id | employee_name
-------------+--------------
 1           | adam
 2           | alex
 3           | hales
 4           | jordan
 5           | jackob

Any solution is appreciated.

You don't need a procedure, you can do this in a single SQL statement (though you could wrap that statement in a procedure if necessary):

insert into employee(employee_name)
with w as (select 'adam,alex,hales,jordan,jackob' names from dual)
select regexp_substr(names,'[^,]+', 1, level) from w
connect by regexp_substr(names, '[^,]+', 1, level) is not null;

select * from employee;

EMPLOYEE_ID | EMPLOYEE_NAME
----------: | :------------
          1 | adam
          2 | alex
          3 | hales
          4 | jordan
          5 | jackob

dbfiddle here


Purging audit records - Oracle At the end of each financial year, audit team asks for audit records for samples dates in a year as per their methodology and ideally we are supposed to provide them. (Say, give them list of schemas dropped in a particular month or modifications made for a schema in a month)

Now, im in a situation I need to save the audit data for one complete year, is there anyway to purge the audit data on a monthly basis and at the same time provide the necessary info by the end of the year. The reason I need to purge is because of the audit file size which grows to a huge size.

What choices do I have? I do not have license to Oracle Audit Vault as well.

Archiving the Database Audit Trail

Scheduling an Automatic Purge Job for the Audit Trail

Manually Purging the Audit Trail

Basically you copy the audit trail to another table, e.g:

INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...;

You can just export and drop these tables, then import them when you need to access audit records.

Then you set the last archive timestamp, e.g:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =>  '2009-05-28 06:30:00.00'
   RAC_INSTANCE_NUMBER  =>  0 );
END;
/

Finally you initialize cleanup and schedule a purge or run it manually:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    => 12 );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

Manual:

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
END;
/


PostgreSQL xmloption setting How long does changing PostgreSQL's xmloption setting to DOCUMENT from its default CONTENT last? It seems to be DB wide, not Cluster wide (any way to set it on a column by column basis?)

It seems to be connection specific, which is annoying cause I have a SQL script that needs to have it set to DOCUMENT to pass validation. The script is 24GB uncompressed, so I don't feel like modifying it manually.

it is connection specific,

All plain set commands are. (see the set command in sql refernce)

You can make the setting persistent using.

ALTER[user|database] name SET xmloption TO DOCUMENT ;

see the ALTER command in sql reference

Alternatively you can prepare the session or prepend to the stream being sent to psql, you don't need to edit the actual disk file.

In the following examples everything upto the $ represents the prompt,

Using the psql command-line:

  database-$ SET xmloption TO DOCUMENT ;
  database-$ \i filename

Or the system commmand-line

~$ psql "conmnection-parameters" -c "SET xmloption TO DOCUMENT" -f "filename"


Why are there multiple plans for query under forced parameterization? I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:

delete from [CMS_WebFarmTask]
where [TaskIsAnonymous] = @0
and [TaskID] not in ( select [TaskID] from [CMS_WebFarmServerTask] )
and [TaskCreated] < dateadd ( minute , @1 , getdate ( ) )

It has a large number of plans (17 in the last hour), despite both parameters being integers with identical values in each plan:

        <ParameterList>
          <ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(-3)" />
          <ColumnReference Column="@0" ParameterDataType="int" ParameterCompiledValue="(0)" />
        </ParameterList>

Why isn't only one plan being used?

Forced parameterization <> forced single plan.

However, the Query Store does have a feature to force a single plan if that's what you're after. You can see the button for it right in your screenshot.

But, if your concern is more general as to why you're getting multiple plans in the first place, you should start by making sure the same query is being executed each time. In your case, since you're using GETDATE(), the query is actually different every time it runs, so I wouldn't expect it to yield the same plan necessarily based on that alone.

You also need to be aware of the fact that the statistics of the underlying tables could be changing over time as well, which can heavily impact plan estimations. There are lots of other factors that play into it - this article digs deeper into what goes into an execution plan and what can affect them. The database is a living thing and plan changing shouldn't be inherently viewed as a bad thing, unless it is causing obvious performance issues.


SSMS Intellisense - Data Dictionary Incorporation - Show Object Descriptions in Popup? Can anyone point me in the right direction for an SSMS intellisense replacement that will show the object descriptions in the popup?

This will help speed up onboarding as well as making myself familiar when dev hands me a new beast to manage.  Object names should always be fairly descriptive, but sometimes what made sense 10 years ago makes little now, and cross-referencing a dictionary doc is a productivity barrier.

Mockup example using AdventureWorks:

Using native tools there's no way to do this.  You could try checking with RedGate or SSMS Tool Pack and see if either team would be able to assist you.


SQL Visual Studio Find and Replace in Publish Profile We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References.

We need Project references, since Otherdatabase, can choose between two options at our company. Most of the time, its 90% Database A, and 10% Database B.

This is a headache for developers, to write code with $ and brackets []. Is there any work around for this?
Eg, Say I write my code in DatabaseA default,

from [$(OtherDatabase)].dbo.PurchaseOrder
from DatabaseA.dbo.PurchaseOrder

Is there a method to have Multiple publish profile scripts, Can I write a publish profile script to find all references to Database A, and replace with Database B? Maybe like a simple Ctrl-Find and Replace? How do I incorporate this kind of command, batchfile, stored procedure or Powershell in publish profiles?

Searching for an efficient way; I may use this answer below, however DatabaseA needs to reside in same server, and then I would drop and recreate which may not be optimal.

Find and replace database name in views and stored procedures

Aware of synonyms trying to avoid this route, and conduct find/replace

When writing cross-database code, I prefer to avoid three-part name references. The variability of the $(OtherDatabase) that you describe is the precise reason why I avoid three-part names. Someday, DatabaseA will be renamed to DatabaseC and have to update a bunch of references in code. In your case, you can just do a new build, but it's still a pain.

Instead, create synonyms in the project for each cross-database reference. In the synonym, you will use the SSDT project reference variable once per object:

CREATE SYNONYM dbo.PurchaseOrder FOR [$(OtherDatabase)].dbo.PurchaseOrder;

Then, every time you reference the cross-database object, you only need to use the two-part name. Developers should use neither the [$(OtherDatabase)] nor  a placeholder DatabaseA reference for find-and-replace.

FROM dbo.PurchaseOrder

This has the added benefit that all of the code will always be identical across different iterations of the database. The only object types that will not match will be synonyms. Rather than inspecting code, you can simply query the sys.synonyms DMV to determine where dbo.PurchaseOrder points.

In this query, name would return the two-part names (ex PurchaseOrder), and base_object_name would return the three-part names (ex DatabaseA.dbo.PurchaseOrder):

SELECT name, base_object_name FROM sys.synonyms;

If you ever have a database that needs to be repointed from DatabaseA to DatabaseB, you can simply re-point the synonyms via a series of DROP SYNONYM & CREATE SYNONYM statements, without having to perforem ALTER statements for procedures/views/functions/triggers.


SQL2542N - No match for a database image file was found based on the source database alias I'm trying to restore a DB from a Linux system to a Windows. I'm working with DB2 9.7.7. I want to create a new DB with the restored DB.
The DB file has this name: DBBACKUP.0.db2inst1.DBPART000.20140614125950.001 and I've the file under /home/db2inst1 folder. So, logged as DB2 administrator (db2inst1), I'm trying with this command:

  db2inst1@machine:/data/home/db2inst1> db2 restore db DBBACKUP

or

  db2inst1@machine:/data/home/db2inst1> db2 restore db DBBACKUP taken at 20140614125950

and it returns me:

  SQL2542N  No match for a database image file was found based on the source database alias "DBBACKUP" and timestamp "" provided.

It can not find the database, but I don't know if the error is the name of the DB I put in the command, or the path is wrong.

Thanks.

Thanks all for your answers.

Finally I asked the guy who had give me the copy of the database. It came from a Linux, and I was trying to restore in a Windows, so I think this was the main problem. I tried to do the restore on a Linux-Suse, and it worked.

Once more, it is good to start with the most obvious... sorry, and thanks!


Row level security returning no error when blocking all effects of a query With row level security enabled on tbl, but no policy set, as a user the UPDATE throws no error but does not modify the value:

UPDATE tbl SET col = 1;
=> Query returned successfully: 0 rows affected
SELECT col FROM tbl;
=> NULL

If row level security is disabled on tbl, as a user:

UPDATE tbl SET col = 1;
=> Query returned successfully: 1 row affected
SELECT col FROM tbl;
=> 1

I would expect to get something like: ERROR: permission denied for relation tbl instead of a kinda silent 0 rows affected.

Can I get something more explicit?

Using PostgreSQL 9.6.3 on CentOS 6.

Edit: Row level security is the cause, but I'm not clear why there is no error.

If you only enable RLS for a table, but do not specify rules, then a "deny all" rule is implicitly assumed.

Quote from the manual

  When row security is enabled on a table [...] all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. [...] If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

(emphasis mine)

So since the policy makes no rows visible, this is why there is no error when UPDATEing and you get 0 rows affected.


MySQL compute percent change I am new in MySQL.. I want to compute for the percent change of this table using MySQL. Can you help me with the script? I am using 10.3.8-MariaDB MariaDB Server.. Thanks..

CREATE TABLE `MyTable` (
  `Column` VARCHAR(14),
  `Amount` VARCHAR(6);

INSERT INTO `MyTable` (`Column`, `Amount`)
VALUES (`Column 1`, `500`),(`Column 2`, `100`),(`Column 3`, `1100`);

I want to get an output table sorted by Column:

Column   | Amount | Previous Amount         | Percent Change
Column 1 | 500    | 0                       | ((500-0)/0)*100
Column 2 | 100    | 500 (amount in column1) | ((100-500)/500)*100
Column 3 | 1100   | 100 (amount in column2) | ((1100-100)/100)*100

SELECT `Column`,
       Amount,
       COALESCE(LAG(Amount) OVER (ORDER BY `Column` ASC), 0) `Previous Amount`,
       COALESCE(100*(Amount -
                 LAG(Amount) OVER (ORDER BY `Column` ASC)) /
                 LAG(Amount) OVER (ORDER BY `Column` ASC), 0) `Percent Change`
FROM MyTable ;

fiddle

PS. It's extremely bad practice to store number data in VARCHAR field.

PPS. While using any ticks always look for a proper tick type.


How can I import CSV data stored in a bytea? I have a CSV file that can be loaded into a table easily with COPY FROM, but the catch is that the file is stored in the database in a bytea field.

Is the only way to use COPY on this data to write it out to a temporary file, or is there some clever trick, perhaps involving COPY FROM STDIN?

Precaution

The only reason I can see storing a CSV as a bytea is if you created it with COPY TO BINARY. You'll want to check to make sure this isn't binary data. If so the whole question is a off the mark as you don't have to do this and can just feed the bytea to COPY FROM STDIN

Using convert_from

The question is mostly how do you convert a bytea to text. To do that you'll use convert_from. See my answer here for more information on that process.

After you have a method to get the bytea to text you can either,

Wrap a psql -d myDb -c "SELECT convert_from(col ...) FROM tbl" in a call to COPY FROM STDIN and make use of it.
Parse it from within the INSERT/UPDATE using something that can process a CSV-row (suggest Text_CSV::XS). Doing it this way will save you from having to call a second a session to read and output in a format that COPY can accept.


Function pg_indexes_size(oid) does not exist I'm attempting an operation on a pg_class c, but getting this:

ERROR:  function pg_indexes_size(oid) does not exist
LINE 10:               , pg_indexes_size(c.oid) AS index_bytes
                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Version of Postgres

$ psql --version
psql (PostgreSQL) 8.4.20
contains support for command-line editing

I tried typecasting to a few different types, but with no success.

8.4 is a pretty ancient version now.  The function you are after is not mentioned in the docs, therefore it is sane to think it simply did not exist back then.  9.0 introduced a number of administration functions, and pg_indexes_size() is one of those.

In any case, it would be prudent to upgrade to a supported version (9.2+ at the time of writing.


Sql server cpu usage is 140% I run Sql server instance and a dot.net core app on the same server for a while and today is the first time I came across with this issue, my website doesn't get data from the server and it gets this error:

There is insufficient system memory in resource pool 'internal' to run
this query. Error: 596, Severity: 21, State: 1. (Params:). The error
is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.   A severe error occurred
on the current command.  The results, if any, should be discarded.

and this error as well:

Timeout expired.  The timeout period elapsed prior to completion of
the operation or the server is not responding.

The sql server version:

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)
Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)

I typed the top command and I got this:

It used to work fine for more than 3 months without any error.
How can I find out what is causing Sql server to use all this cpu all of a sudden?

You should familiarize yourself more with top - it shows > 100% because it displays this data as a percentage of a single CPU and presumably you have more than one of those.
I'd recommend a more robust monitoring setup for your SQL Server. The MS CAT team has a good article on how to do this for SQL Server on Linux.
Finally, stop running your application and SQL Server on the same host. Your error is actually concerning a lack of memory, of which you are probably capping SQL Server from using and/or it is competing with the application for. More resource contention issues like this are probable in the future with keeping both on the same host, as well as security concerns, patching difficulties, and more.


Primary Key choice on table with unique identifier I'm reconsidering my choice of Primary Key on a table, and would like some input.

Some context:  We are building a REST API, where clients access "Assets" identified by a GUID.  So, the table for Asset in our database resembles this:

CREATE TABLE [dbo].[Asset](
    [Id] [uniqueidentifier] NOT NULL,
    [TypeId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Title] [varchar](1000) NULL,
    [Description] [varchar](max) NULL,
    [Created] [datetime] NOT NULL,
    [Modified] [datetime] NOT NULL,
    [PublisherFields] [xml] NULL,
    [StatusId] [int] NOT NULL,
 CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

We are experiencing a few problems with this structure, notably that the clustered index on the table becomes fragmented very quickly, and we have now reached the point where we are no longer able to rebuild it (because of Azure limitations on query time limits).

Further research suggests that using a uniqueidentifier as a clustered index is not always a good choice, because of the way that newid() doesn't generate IDs in sequence (unless you use newsequentialid(), but Azure doesn't allow this either).

The simple solution seems to be to introduce a dummy column and add the clustered index to that.  Design guidelines say that good clustered indexes should be sequential and non-changing, so the simplest answer to that seems to be an Identity column.  This would make my table into this:

CREATE TABLE [dbo].[Asset](
        [Id] [bigint] IDENTITY(1,1), <---- NEW
        [AssetId] [uniqueidentifier] NOT NULL, <---- Renamed
        [TypeId] [int] NOT NULL,
        [Date] [datetime] NOT NULL,
        [Title] [varchar](1000) NULL,
        [Description] [varchar](max) NULL,
        [Created] [datetime] NOT NULL,
        [Modified] [datetime] NOT NULL,
        [PublisherFields] [xml] NULL,
        [StatusId] [int] NOT NULL,
     CONSTRAINT [PK_Asset] PRIMARY KEY NONCLUSTERED <--- Now NonClustered
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )

The clustered index would also be added to the ID column.  Does this seems a sensibible alternative?  I might question the sense in having an IDENTITY column on the table that is not a primary key, but my reasoning is that an IDENTITY column is used to obtain the auto-incrementing nature, which suits the clustered index.  It might also be confusing...when someone references an "AssetID", which column are they talking about?  The [AssetId] column, or the Asset table's [ID] column?

Suggestions and alternatives welcomed.

There are a few things to consider here:

How is data most often looked up in this table?
How is data most often sorted in this table?
Does this table relate to others as the parent record (i.e. will other tables FK to the PK of this table)?

Also, please keep in mind:

The key fields of a Clustered Index are copied into the non-clustered indexes on the same table
For Clustered Indexes that are not a Primary Key (implied unique) or at least declared as UNIQUE, a hidden "uniqueifier" field is added to rows that would otherwise be duplicates.
NEWSEQUENTIALID() is sequential per each restart of the SQL Server service. It is possible that the starting value after a restart is less than the previous lowest value.

Hence:

If the PK field of this table shows up as a FK field in other tables, there is a definite performance implication of choosing to use a UNIQUEIDENTIFIER instead of an INT as the FKed tables would then have a larger FK field in them.
How many rows do you really expect to have in this table? INT is 4 bytes (compared to the 8 bytes of a BIGINT) and has a max value of 2,147,483,647. If you might have slightly over 2.14 billion items, you can also start the IDENTITY range at the min value of each datatype, which for INT is -2,147,483,648. Starting at the low-end gives you the full 4.294 billion values to use. Compared to the 8 bytes of the DATETIME field if going with the Created field, plus add in the size of another field to make it unique, or the uniqueifier for any duplicate rows.
Since the key field(s) of the Clustered Index are included in Nonclustered indexes, that increases the chances of having a covering index without needing to INCLUDE other columns. Meaning, if you have the Clustered Index on the INT PK and a Nonclustered index on the UNIQUEIDENTIFIER, then JOINing to another table on that INT PK field while specifying the GUID value in a WHERE clause (assuming no other fields from this table are in the query) won't have to go back to the table since the Nonclustered Index will have both of the requiered fields in it. Does the Created field give the same benefit? Likely not.

IF no other tables ever JOIN to this table:

THEN it might be ok to use the Created field as the non-unique Clustered Index and the Id field as the Nonclustered PK.
ELSE it is typically best to add an INT (unless you need more than 4.294 billion values) IDENTITY field, AssetId, as the Clustered PK, and the Id UNIQUEIDENTIFIER field as a Nonclustered Index. Since you likely already have code referencing the UNIQUEIDENTIFIER field as Id, I wouldn't change that name.


Is there any way to do a SELECT query without any optimization in SQL Server? I am trying to test the speed of SQL server queries.
However, once I do the query once, it becomes a lot faster.
The database is live so I can not make any changes to it, only to the SELECT query being performed.

Is there any way to do a SELECT query without SQL server optimizing it?

For the query plan itself, you can force compilation every time using the following option on your query:

OPTION (RECOMPILE);

However I suspect what is happening is that the second and subsequent executions of the query are pulling the data from memory instead of disk, and of course memory is faster than disk.

If you are trying to test the scenario where this data always comes from disk, there's not really a good way to do that on your production instance without affecting anything else, because you can only drop clean buffers for the entire instance at a time, not for a single database, never mind table. So what you could do perhaps is set up an instance on the same hardware, with just this table, and run the following before every run of the query:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

However, this does not make a lot of sense, as ideally you will be querying data that is in memory, and you should be optimizing for the best case scenario. You should have some idea of what the worst case scenario is like, but again, this should not be the normal situation, unless you have a very big database and a very small amount of memory (in which case, open the wallet and buy more memory - it's much cheaper than optimizing for the lack of it).


Removing secondary replica from SQL Availability Group causes large log file Due to an unforeseen hardware error, I had to take our secondary replicas down for maintenance. This was really due to a storage issue and, it was decided at a higher level, to purchase a new storage system for the secondary instance. This is several weeks out from delivery and installation at this point.

This was on a system that also uses FCI with 2 active instances.

I was able to successfully remove the secondary replicas from both Availability Groups and I thought I had done this correctly (they no longer show up in the UI or when I look at the AG Dashboard. However, one one of the AG's, all of my log files are growing. When I query the sys.databases and look at the log_reuse_wait_desc for that instance it says 'AVAILABILITY_REPLICA'

I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

Why, on one AG does it not appear to be growing or have the log_reuse_wait_desc of AVAILABILITY_REPLICA but on the other it does?

I think the question has a wrong assumption.

  So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'?

...

  I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

If you have an AG running on a single node, you can remove the database from the AG completely AND still connect to the database via the listener (as long as the clustering part is online either forced or using Windows 2012+ with dynamic quorum).

For a normal connection without any application intent the listener just functions as a dumb alias for the server. It doesn't check something like whether the database you're connecting to is part of its AG or not.


Need help on my first ER diagram I just started my first Database course online, and I have a homework assignment to create an entity-relationship diagram (ERD) from a list of specifications.

The specs are as follows:

The company is organized into departments. Each department has a name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have several locations.
A dept controls a number of projects, each of which has a name, unique number, and single location.
We store each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee.
We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name sex, DOB, and relationship to the employee.

For your first database class and first attempt at an ER Diagram (ERD) I think you have done a great job!  I'd like to give you some feedback in the context of the process I use to break down a set of requirements like you were given and create a draft diagram.  Hopefully by taking this approach I'll help you in your development of the skill of ER modeling and database design and not just give you an answer to a homework assignment.
Find the Entities and Relationships
The idea behind an ERD is to identify the entities - things of fundamental significance to the business - and how they are related.  Thus the name Entity-Relationship Diagram.  The goal at this point is not to design or implement a database but instead to develop a more structured understanding of the domain of interest on which a database will ultimately be based.  A good way to get a start on finding the entities is to look for the nouns in the requirements and then pick out those which are persons, places, things, concepts, or events.  Then, you can find most of the relationships by looking for the verbs that connect those nouns.  Here is my first pass at finding entities and relationships in the requirements provided:

I highlighted the nouns in a bright green, and the verbs in a bright pink.  Now notice there is one noun - supervisor - that is highlighted in a darker green.  We'll ignore that for now.  Comparing what I found to what you placed on your ERD we are on the same page!  The only difference is that I also highlighted location.  I can see why this was left off of your ERD as its not clearly an entity.  You could just as easily think of it as an attribute - which is a common property or characteristic that all entities in an entity type share - of the department and the project and that is how you modeled it.  The reason I elevated it to an entity type is this:

A dept may have several locations

This indicates that each department will be associated to one or more locations, and thus necessitates breaking this out to its own entity type.  A second reason to elevate it is that we can think of a location as a place and as such would have its own attributes such as name, address, city, state, zip, and so on.  A third reason to elevate it is that the location is referenced by more than one entity type - the department and the project.  If you find the element in question is referenced by more than one already identified entity, you should consider it an entity in its own right that is related to the others, and not simply an attribute of the others.  There is no science to this however - its purely a judgment call.  This is why ER diagramming is at the conceptual level as its subjective to individual perspectives.  One person's entity is another person's attribute depending on their perspective into the domain of interest.
Regarding the relationships, here is a list of what I highlighted in pink:

Employee manages Department
Department controls Project
Employee assigned to Department
Employee works on Project
Employee has Dependent

Of these, you identified all but the first one.  This, along with the supervisor I mentioned earlier, gets into a discussion of roles which we'll postpone to later.  Right now we are just trying to identify the fundamentals and I'd say you were right on.
Associative Entities
Looking at the relationships we have a few that are many to many.  The requirements state that an employee can work on many projects, which are not necessarily controlled by the same department.  Given that most projects have more than one member, we can safely assume this is a many to many relationship.  When you have such a relationship on an ERD, it is perfectly acceptable to show a many to many relationship line.  I typically only use the many to many relationship though if I'm not showing attributes at all.  Since we are showing them here, I prefer to resolve the many to many relationship with an associative entity.  Even if there are no attributes for the entity now, we may uncover some as analysis proceeds.  However, in the case of employees and projects, we have attributes - the hours worked - that must be added for that relationship and so it must be resolved by creating the associative entity to show them.  Thus we will create an associate entity called Project Assignment.  But we are still not done.  The requirement calls for us to know the hours worked per week.  When you think about it, you realize there would be many weeks in the life of the project, and we need to record the hours worked by that employee on that project each week.  Thus another entity type is required which I called Hours Worked of which there will be many occurrences per project assignment.  This entity type will hold the week ending date and the hours worked.
Next, by creating a location entity type, the requirement that states a department may have several locations means we now have a relationship where one department can have many locations.  It is safe to assume as well that one location will have many departments operating there.  Therefore I added another associative entity called Operating Site to show this.
Find the Attributes
The attributes were listed pretty clearly in the requirements with statements like:

Each department has a name, an unique number,
keep track of the start date when the employee began managing the dept
projects, each of which has a name, unique number,
store each employee’s name, SSN, address, salary, sex, and DOB.
keep track of the number of hours per week that an employee works for each project.
keep each dependent’s first name sex, DOB, and relationship to the employee.

What I do at this point is list out the entities and relationships identified and place the attributes identified with the entity type it belongs to:

Department - Number, Name
Employee - Name, SSN, Address, Salary, Sex, DOB
Location - ???
Project - Number, Name
Dependent - First Name, Sex, DOB, Relation

Note I can't find any attributes for location.  This would be a red flag to go back to the business to get some additional information about locations!
Then list the relationships and their attributes:

Employee manages Department - Start Date
Department controls Project -
Employee assigned to Department -
Employee works on Project - Hours Per Week
Employee has Dependent -
Department has Location -

For those last two relationships the verb has isn't very descriptive, but that was how the requirements were phrased.  The entity has an attribute, so why wouldn't these be attributes?  I discussed earlier why I would choose to make location an entity type.  For dependent, the choice is more clear as its clear each employee has many dependents and so it must be broken out into its own entity.  Once this is done we could make the leap to improve the verb.  Perhaps something like employee cares for dependent and department operates at location.
Now we are in a position to create the diagram.  Here is what I have:

This was created using Oracle SQL Developer Data Modeler - a free download - which is a great tool if you want to continue the process into database design and creation.
Primary Keys
You have identified primary keys.  Technically, an ERD does not require you even consider keys.  If you think of the ERD as a tool to model the business, its not important to determine at this point exactly how you will uniquely identify an occurrence of each entity.  Instead, you can assume that at a later point you will figure this out, and instead focus only on the entities, their relationships, and their attributes.  It is a good idea to note which attributes are unique for each entity as this will help choose keys.  Once you complete the diagram and iterate upon it with the business, you can go back and complete it by choosing the right keys.  In my diagram I only noted that the two numbers mentioned as unique in the requirements were unique on the diagram using a U beside the attribute name.
Entity Roles
Now to address the dark green highlighted supervisor I mentioned earlier.  The requirements stated that "a department has an employee who manages the department."  They go on to state "We also keep track of the direct supervisor of each employee."  So what we have here is an entity - a person - who is playing different roles.  The employee might be a manager.  The employee might be a supervisor.  So how do we resolve this?  First, we need to decide if the manager and the supervisor are the same thing!  Is it the case that the manager of a department is also the supervisor of the employees assigned to it?  If yes, the solution is simple.  A new entity type called Assigned Manager can be created with a one to many relationship from department to it and from employee to it, with a single attribute of the start date when that employee started managing that department.  Then, each employee's supervisor is assumed to the the manager of the department they are assigned to.  If no, then we can add a recursive relationship from employee to employee to represent the the supervisor and the employee.  This is the simplest approach but does introduce a mixing of roles in the same entity type.  A better approach is to add a new entity type called Assigned Supervisor with two one to many relationships from employee to it - one to represent the supervisor and the other to represent the supervised. If there is a date when the assignment began and ended this could be added as well.
Conclusion
Hopefully this, coupled with the description of the process used to develop it, will be a good learning tool for you to understand how you get from a description of a business process to a draft ERD.  I say draft because it is just a starting point.  Once completed, it serves as a communication vehicle to validate the requirements, find holes in them, uncover new ones, and so on, before any programming or even system design begins.  Remember that doing a great job building a system that implements requirements that were mis-understood will be a failure and it will be much cheaper to modify an ERD than a working system!
References
Two really good reference for ER diagramming are Steve Hoberman's Data Modeling Made Simple, which is a great overview, and David Hay's Enterprise Model Patterns, which gives a great in depth look at common patterns you find when analyzing organizations.  Both of these references give much more detail on describing relationships using verb and prepositional phrases, identifying and non identifying relationships, and strong and weak entities - concepts I didn't address.  Fabian Pascal's Practical Database Foundation Series is also excellent, and has a great first paper that is the perfect compliment to the books as Fabian describes the entire process of determining data requirements.  Remember that ER diagrams can only show keys and references, whereas in fact there are many more kinds of business rules that can be explored and implemented in the ultimate database.


MySQL InnoDB cluster pricing On https://www.mysql.com/products/community/, the InnoDB Cluster is listed as part of the MySQL community edition, but when clicking the "Download MySQL Community Edition" link and further, I end up on the NDB cluster documentation. So I'm confused about whether the InnoDB cluster is only availabla via the $5000 enterprise package, or if it's available for free too.

Have anyone looked into this, and know if the InnoDB Cluster is available for free?

Quote from comments on Blog Introducing MySQL InnoDB Cluster – MySQL HA: Out-of-Box, Easy to use High Availability

  Is this MySQL InnoDB Cluster – MySQL HA for Community Edition i.e
  under GPL /OpenSource?

answer from Mike Zinner:

  (...)
  yes, MySQL InnoDB Cluster is available in the Community
  Edition and it is licensed under GPL. You can download the individual
  components from https://dev.mysql.com/downloads/ You will need MySQL
  Server, MySQL Shell and MySQL Router to build your MySQL InnoDB
  Cluster (...)


Cast string to decimal without rounding the decimal part I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don't want that, I just want that it keeps all the decimals that the original table had.

Here is the query I'm using to populate this new table:

UPDATE "Products" p
SET "conversionFactor" = (SELECT cast(a.factor_conversion as decimal)
                            FROM db2.articulo a
                            WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)
 WHERE EXISTS(SELECT 1
                FROM db2.articulo a
                WHERE cast(a.codigo_articulo as int4) = p.id and p.id = 101)

In Products if I select that id I get: 327.58 but in the original table it is 327.580134

How can I fix that? Also other rows can have the same amount of decimals, others less, others more.

The datatypes of "conversionFactor" and factor_conversion are both strings in table articulo. I want them to be decimals in the Products table without rounding any decimals.

You don't show the column definition, but it must be similar to

"conversionFactor" decimal(10,2)

At any rate, the scale of the column must be 2, so PostgreSQL has to round the number to be able to sore it in that column.

You will have to use ALTER TABLE to change the colum definition to be able to store more fractional digits in the column.


The transaction log for database is full due to 'OLDEST_PAGE' We have a database hosted on azure. It is part of an azure elastic pool.
From yesterday all our database operation are consistently failing with following error.

The transaction log for database is full due to 'OLDEST_PAGE'

We checked for all active transactions on database but there are no active transactions currently but still log file is taking 100% of space.

I have gone through following documentation pages, but I was not able to understand the issue completely:

The transacion log
Database checkpoints

Can anyone please help me understand this issue?

Update

When we moved this DB to new pool then log size reduced automatically.
We are not experiencing any issue after move.

This is not something you should ever see on an Azure SQL Database.  You should engage support on this.

There are a couple things you could do yourself, if you want.  First just try to CHECKPOINT the database.  Second if you are sure you have no active transactions, change the database SLO.  Taking it out of the Elastic Pool, then putting it back in.


Reuse results from correlated subqueries in same query I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).

distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
distr_catalog_brands: belongs to distr_catalogs
distr_catalog_system_types: belongs to distr_catalogs
brand_catalog_sections: belongs to distr_catalog_brands, distr_catalog_system_types

I am wanting to create a materialized view whose columns are:

catalog_id | catalog_name | brand_ids | system_type_ids | catalog_sections

catalog_id & catalog_name come from the distr_catalog table
brand_ids holds an array of the brand ids related to the catalog
system_type_ids holds an array the system type ids related to the catalog
catalog_sections holds an array of brand catalog section ids that are related to the brand_ids and system_type_ids

I can do all but the last just fine:

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name,
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
from distr_catalogs dc

This gets me a great table that has all the right data. However the only way I can figure out to get the last column I need ... is to completely re-write the subqueries in the final array statement. I get an error every time I try to use the brands or system_type_ids aliases I defined in the two above arrays.

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name,
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
ARRAY(SELECT id FROM brand_catalog_sections
      WHERE brand_id = ANY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id)
      AND system_type_id = ANY(SELECT system_type_id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id)) as section_ids
from distr_catalogs dc

How would I re-write the create statement to not have to re-run the subqueries?

Note: This is the first instance of this. However, the next table I bring into this view will need to use the results of the section_ids column to determine which systems to display. So this will get out of hand quickly if I am not able to reuse the results from a previous query.

Use LATERAL joins instead of correlated subqueries. This way you can reuse results to join to more tables:

SELECT dc.id AS catalog_id
     , dc.catalog_name
     , db.brands
     , ds.system_type_ids
     , bs.section_ids
FROM   distr_catalogs dc
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT brand_id
                FROM   distr_catalog_brands
                WHERE  distr_catalog_id = dc.id) AS brands
   ) db
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT id
      FROM   distr_catalog_system_types
      WHERE  display_status = true
      AND    distr_catalog_id = dc.id
      ) AS system_type_ids
   ) ds
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT id
      FROM   brand_catalog_sections
      WHERE  brand_id       = ANY(db.brands)
      AND    system_type_id = ANY(ds.system_type_ids)
      ) AS section_ids
   ) bs;

It's safe to use CROSS JOIN LATERAL (or just , LATERAL) here, because the array constructor always returns a row. Else, if a lateral subquery could come up empty (no row), you would use LEFT JOIN LATERAL (...) ON true to avoid losing rows.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
Postgres datetime sort slow and not using index
Build JSON object from one-to-many relationship data in a single query?


DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows) I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.

Background

I had a large table (~400,000,000 rows) that needed to have rows archived and removed.  After removing about half of the rows, my archive process started to hang on the DELETE statement.  I took the time to run the (painfully slow) 11-hour OPTIMIZE (ALTER for InnoDB) query to reclaim disk space and hopefully unblock the DELETE process.  This worked until I deleted about half of the remaining rows (down to about 106m out of 200m rows).  I figure I could keep running OPTIMIZE on my giant table to let me remove another 1/2 of the rows, but there must be a better way...

Note that the table does get a lot of INSERT/UPDATE traffic, 1000s of changes per minute.

Archive Process

To simplify the archive script, I put the whole process into a stored procedure.  The steps in that procedure are:

Flatten and copy up to N rows from the live DB to the archive DB, where the rows are older than 1 month
Make a list of archived IDs that are probably in the live DB.
Store these in a temporary table A
Filter temp table A to only include row IDs that are actually in the live DB.
Store these in a temporary table B
DELETE FROM live_db.test_table WHERE test_id IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

Now that the table is half empty, step 4 is hanging, even for N = 10
Before the table was half-empty, it worked fine and N = 1000 took ~ 1 sec

Diagnostics

Adding an EXPLAIN in the stored procedure lets me know MySQL is planning on using the PRIMARY index for the DELETE, as I hoped it would:

id select_type         table          type   possible_keys  key      key_len  ref   rows     Extra
1  PRIMARY             test_runs      index  NULL           PRIMARY  4        NULL  10       Using where
8  DEPENDENT SUBQUERY  tests_to_drop  ALL    NULL           NULL     NULL     NULL  1        Using where

Output selected from SHOW ENGINE INNODB STATUS (some things redacted):

---TRANSACTION 6457899980, ACTIVE 14 sec starting index read, thread declared inside InnoDB 3008
mysql tables in use 2, locked 2
242717 lock struct(s), heap size 25507368, 5532268 row lock(s)
MySQL thread id 1834147, OS thread handle 0x7f0f68c4a700, query id 204328569 1.2.3.4 user123 Sending data
DELETE FROM live_db.test_table
WHERE test_id IN (SELECT * FROM temp_table_B)
ORDER BY test_id ASC
LIMIT 10
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

(From Comment)

CREATE  TEMPORARY TABLE temp_table_B (
        test_id int(10) unsigned NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

I found out what's going on here:

Because of how I was choosing test_id values in steps 1 and 2, temp_table B would sometimes be empty. (see question description for step/table context)

Then, with an empty temp_table_B, I was running step 4:

DELETE FROM live_db.test_table WHERE test_id
    IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

which gave

DELETE FROM live_db.test_table WHERE test_id
    IN ( <empty> ) ORDER BY test_id ASC LIMIT N;

which would hang for quite a while.

Based on this answer: https://dba.stackexchange.com/a/86103/144766 , it seems like that empty table was probably compared against each of my 106 million rows, making the do-nothing DELETE take 25 minutes.

Now that I have modified my process to detect the empty table and abort before calling DELETE, I can manually watch for the (data-specific) condition that's causing my list-of-rows-to-delete to occasionally become empty.

EDIT: Even after fixing the empty-in-list problem, my query was greatly sped up by switching from the " IN (SELECT * temp_table) " version to a JOIN version, similar to what is suggested in comments by ypercube above.  This modification eliminates the 'SUBQUERY' from the execution plan and the query becomes a 'SIMPLE' query:

'IN' version:
id select_type         table           type  key     key_len rows Extra
1  PRIMARY             test_runs       index PRIMARY 4       10   Using where
8  DEPENDENT SUBQUERY  temp_table_B    ALL   NULL    NULL    10   Using where

'JOIN' version:
id  select_type  table        type   key     key_len ref                  rows Extra
1   SIMPLE       temp_table_B ALL    NULL    NULL    NULL                 10   Using where
1   SIMPLE       test_runs    eq_ref PRIMARY 4       temp_table_B.test_id 1


SQL Server Collation for Arabic, Hebrew, English and French I have new SQL Server database which need to have support in 3 different languages.

What character sets and collations do I need to choose if I want my database to support the following:

Hebrew
Arabic
English
French

Language support comes from datatype: use nvarchar/nchar (which stored unicode basically)
Collation is sorting + comparing + non-unicode code page

So, one column can store all languages, but it can have only one collation. You can "add" extra collations using computed columns, or coerce it during a sort/compare using the COLLATE clause:

Example

ORDER BY
    LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';


Don't use a transaction for Stored Procedure I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to stay and not rollback.

Is it possible to write the stored procedure in such a way that it doesn't all execute as one big transaction?

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit.  All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated).  You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together.  A stored procedure is an example of a batch.  In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it.  If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.


Why a backup created on SQL Server 2016 is significantly larger than one created on SQL Server 2012? I've backed up a database on SQL Server 2012 that resulted in a file of 33MB in size. Then, I restored that backup on SQL Server 2016 and created another backup, but this time on SQL Server 2016.

The size of the backup changed from 33MB (SQL Server 2012) to 315MB (SQL Server 2016).

Why such big increase to the size of the backup file on the SQL Server 2016?

Appreciate any help.
Wesley

Sometimes your backup device can be bigger in comparison to the previous version for reasons like:

Your transaction log increased, check your log to validate if you have not grown it.
You could have extra features enabled in your new instance that are not in the previous instance. i.e: if you have used Full Text-search in your SQL 2016. One time a customer database backup got exponential growth for this.
Your backup device on the new SQL Server version is not initialized {NON Init | Init} in other words your backup file appends the backup into the same file each time that you performed a backup.

--Media Set Options
  { NOINIT | INIT }
| { NOSKIP | SKIP }
| { NOFORMAT | FORMAT }

More details here.


Transactional versus Dimensional model for file upload web application -- preferred approach? For a web application whose sole purpose is to allow users to upload raw file data at any time (to database tables), does a transactional or dimensional model make more sense for such an application?
Some reporting is done off the data at this time but it is very minimal.  If that changes in the future (such that reporting becomes more pervasive), does that change the model you suggest?
Total space for the database 100GB.

It depends on your data, and how you want to store it and access it later to report off it. Your database itself doesn't need to be geared specifically to one or the other, rather you can have tables that are transactional, dimensional, or simultaneously both depending on your use case.
In general, if you're doing some sort of reporting off the data, then normalizing your data with dimensions is helpful for performance, flexibility, and maintenance. But a transactional table can still store references to dimensions to represent the "facts" / qualitative portion of the data to maintain normalization. Or they can even directly store the dimensions inside them themselves, albeit being a denormalized representation of the data from the former.
See the following for two different schemas representing a transactional table that stores dimensions vs being normalized and storing references to those dimensions (T-SQL syntax):
Denormalized
CREATE TABLE DenormalizedTransactions AS
(
    TransactionId BIGINT,
    ItemName VARCHAR(100),
    ItemType VARCHAR(25)
    Quantity INT
);

Normalized
CREATE TABLE NormalizedTransactions AS
(
    TransactionId BIGINT,
    ItemId INT,
    ItemTypeId INT
    Quantity INT
);

CREATE TABLE Items AS
(
    ItemId INT,
    ItemName VARCHAR(100),
    Description VARCHAR(1000)
);

CREATE TABLE ItemTypes AS
(
    ItemTypeId INT,
    ItemType VARCHAR(25)
);


QGIS PostGIS Authentication Failure with "trust" option I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, and set my  pg_hba.conf for local to:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

I can can connect to the database using the pgAdminIII, requiring no password to connect, but connection fails when I try to connect via PostGIS or with QuantumGIS. Here's a screencap of the connection window in QuantumGIS:

Looking into the problem a bit, I came across this post which led me to try connecting via the terminal using psql -U jay -h localhost gis. psql prompted me for a password, I hit enter because I had not set a password, and it returned psql: fe_sendauth: no password supplied. I tried again, this time supplying my Ubuntu user password, which returned

psql: FATAL:  password authentication failed for user "jay"
FATAL:  password authentication failed for user "jay"

I then tried setting a password for jay using ALTER USER, and entering this password in the prompt, and this failed as well. Clearly, I am unable to connect. However, I'm having trouble figuring out what troubleshooting steps to take next.

Any ideas?

Just don't leave the Database field empty in the connection dialog.

When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case.

The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question.

When you put localhost in this field, it attempts a TCP connection which is not what's called local in the pg_hba.conf terminology, so that the entry local all all trust will not match with such a connection.


Migrating SQL Server tables data from one application to another totally different application tables We are migrating a few tables' data from one application to another application. These two applications are totally different tables and schema.

We have created general T-sql scripts and temporary tables to store the required data to transform the data in an acceptable format of destination application tables.

Checking here, is there any different approach that we can adapt to migrate the data between the different applications?

Thanks in advance,
Venkat

For a one-off migration that's a good way. For continuing movement of data from a source to destination system e.g. OLTP system to a data warehouse, a proper ETL tool (SSIS/ BIML) would be better.