Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

The query cache is a very nice feature, but don't be tempted to pay too much attention to it and don't be tempted to make it too large.  Understanding some of its internals will probably help in that regard.

The query cache starts out as one big contiguous chunk of available memory.  Then "blocks" are carved out of this big block:

each cached query takes a block
its companion resultset takes a block
each table referenced by any cached query (no matter how many queries referencing that table are in the cache) also takes a block, one per table.

Block size is dynamic, but the server allocates a minimum of query_cache_min_res_unit bytes per block, with a typical default of 4096 bytes.

Any time queries, their accompanying results, and table references are removed from the cache, either by becoming invalidated by the underlying tables changing or by pruning to make room for newer queries, this leaves new holes the size of however large those blocks were, and the number of "free blocks" usually increases... although if two or more contiguous blocks are freed, the number of "free blocks" only increases by 1, and the "free blocks" won't increase at all if the newly-freed blocks are contiguous with an already free block -- the size of that free block just becomes larger.  Any open block of free memory in the query cache is counted as 1 free block.

Of course, a free block smaller than query_cache_min_res_unit won't be used at all.

So, the query cache fragments.  If the server wants to cache a new query and no free blocks of sufficient size can be arranged (that description is deceptively simple, because the underlying algorithm is complicated), something else has to be pruned... that's your Qcache_lowmem_prunes.  There's a "least-recently-used" (LRU) algorithm that decides what gets pruned.

It would be sensible to ask why the server doesn't defragment the memory... but that wouldn't make sense.  The query cache helps when it can but it's not at all strategic.  You don't want to invest processing time (especially time spent in a global lock) with unnecessary maintenance tasks.

It would be counter-productive for the server to spend time rearranging -- defragmenting -- the memory in the query cache, since the cached results are constantly changing and the whole point of the cache is to improve performance.

The global lock is a very good reason why you don't want to use an excessively large query cache... the server will spend too much time there as queries wait their turn to see if they happen to be cached and your performance will suffer.

But the qcache_free_blocks is essentially an indicator of free-space fragmentation.  That's now many noncontiguous blocks of available memory exist in the query cache.  For a new query to be inserted into the cache, there has to be a a large enough chunk of free space to contain the query, its results, and (sometimes) its table references.  If there isn't, then something else has to go... which is what you're seeing.  Note, again, that the available space doesn't always necessarily have to be contiguous (from what I can tell by reading the source code), but not every hole will be filled when there's fragmentation.

But the fragmentation tends to level out over time, for a given workload, since nothing usually stays in the query cache for as long as you might expect.

This is because, in some ways, the query cache is brilliant in its simplicity.

Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results.  This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back.  The query cache entries referencing that table were already purged.

Also, the query cache is checked for each incoming query before the server actually parses the query.  The only thing that will match is another query that was precisely the same, byte-for-byte.  SELECT * FROM my_table and select * from my_table are not byte-for-byte identical, so the query cache doesn't realize they're the same query.

FLUSH QUERY CACHE does not empty the query cache.  It defragments the query cache, which is why Qcache_free_blocks becomes "1."  All of the free space is consolidated.

RESET QUERY CACHE actually flushes (clears out all of the contents of) the query cache.

FLUSH STATUS clears the counters, but this isn't something you want to do routinely because this zeroes out most of the status variables in SHOW STATUS.

Here are some quick demonstrations.

Baseline:

mysql> show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Run a query...

mysql> select * from junk where id = 2;

The total blocks has increased by 3, inserts by 1 and queries in cache is 1.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67089584 |
| Qcache_inserts          | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+

Run the same query, but with different capitalization...

mysql> SELECT * FROM junk where id = 2;

This query was cached separately.  Total blocks only increased by 2 because we already had a block allocated for the table.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67088560 |
| Qcache_inserts          | 2        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+

Now, we change a different row in the table.

mysql> update junk set things = 'items' where id = 1;

Both queries and the table reference are invalidated from the cache, leaving us with 1 contiguous free block, all of the cache memory freed, and all of the free space consolidated in one block.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

MySQL will not store a query in the cache that isn't deterministic -- such as SELECT NOW(); or any query that you tell it specifically not to cache.  SELECT SQL_NO_CACHE ... is the directive to tell the server not to store the results in the cache.  It's useful for benchmarking the true execution time of a query when the cache is giving you a deceptively fast response on subsequent executions.


Monitor database every hour to prevent data loss

First and foremost this is an issue of access control. Eliminate all redundant logins, and reduce permissions on the required logins to the strictly necessary minimum. Do not use 'well known' logins shared by every person with DB access, make sure every user uses its own login (eg. use Integrated Auth).

It is not clear from your post whether this server is accessed directly from the internet or not. Ideally you should set up a VPN between your app(s) and the server and avoid exposing the server to the internet. If not possible, there are some defenses you need to set up. The default SQL Server ports are subject to constant scans and brute force attacks. You should use a non-default port to, at least, avoid the overhead of rejecting these constant brute force scan attacks. Next, you should restrict the internet client addresses that can connect. This can be done, ideally, at IP level using Firewall rules. SQL Server also can enforce this, using logon triggers, as Alex suggested. If possible, deploy both firewall rules and a logon trigger.

Enable SQL Server logon audit. Advertise it that is enabled, so that employees are deterred from trying to hack into the system.

Establish backup policies, with frequent log backups, and keep backups off-site. Have restore plans, and do restore drills to validate the backups and the restore procedures. If someone penetrates your defenses and manages to erase data, you should be able to quickly restore it.

And lets not forget the app itself. You may well be barking up the wrong tree and the erase may had been done via the app itself, via the usual SQL injection exploits. You must audit the app and ensure this is not the case, fix any issue you find.

Right now, you are in an impossible situation. You have lost data, and don't even know how. Contact professional help to help you find how the data was erased. If you don't know where the ship is leaking, is very hard to patch the hole...

My 2c on the idea of 'monitor the database see when someone deleted my data' is ... a waste of time right now. W/o understanding how the data was erased, it will just turn into an amusing game for somebody that will periodically erase your data, just to see how fast you are reacting today... Really, focus on mitigation and deterrence. Detection is nice to have, but it cannot be your first line of defense.

For structural database changes (eg. modify a table, alter a procedure etc) my company DBHistory.com offers a service that audits all server changes and keeps an off-site record. Unfortunately, at this moment I cannot offer data changes audit.


DDL queries, including CREATE statements, cannot be prepared.

Moreover, even in DML queries that can be prepared, parameters cannot be used for identifiers. They're allowed at places in the query where a literal would be allowed.

As an alternative, you can safely inject column names into a query with the dedicated function that PL/Python provides for dynamic SQL: plpy.quote_ident(string):

https://www.postgresql.org/docs/current/static/plpython-util.html


SELECT
  GroupValue = Val,
  [Count] = COUNT(DISTINCT MyGroup)
FROM
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue)
   FROM dbo.MyTable
   WHERE MyGroup = t.MyGroup
  FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)', 1, 2, '')
  FROM dbo.MyTable AS t
) AS x
GROUP BY Val;


How to import Sybase .db file into SQL Server database?

Since both Sybase and SQLServer share the same SQL dialect (T-SQL), I'd generate the DDL statements from the existing database (it will require a Sybase server) to build the SQL Server database and the an SSIS task to copy data between databases.

I don't know if it is possible to SQLServer to just open a Sybase file, but, albeit the same origins, I don't believe there's any kind of binary compatibility between inner formats of this two DBMS.


Is there any way to prevent a PostgreSQL user to change his/her own password?

AFAIK, there is no way to prevent a user from changing his/her password. It's been discussed (quite long ago) on ocassion on PostgreSQL mailing list, and all the answers I remember were basically "no, you can't".
However, there's a contrib module, called passwordcheck, whose objective is to guarantee the strength of passwords, if that's what you wish (as already commented by @RDFozz).
From the docs:

The passwordcheck module checks users' passwords whenever they are set with CREATE ROLE or ALTER ROLE. If a password is considered too weak, it will be rejected and the command will terminate with an error.
To enable this module, add '$libdir/passwordcheck' to shared_preload_libraries in postgresql.conf, then restart the server.

I've never used the module myself.


How can I see what called a stored procedure?

Internally within a stored procedure there is no way (that I'm aware of) to determine the calling proc. There are two workarounds that I use

1.
This one wont actually be identifiable for a profiler run but can be useful in auditing.

When calling any SP call it with isnull(@procID,@@procID) (into a value called @procID), this will mean that it will hold the stored procedure ID (which can get you the schema and name) of whatever stored procedure was first called, by anything (be it a person or automated call (note this can technically be spoofed by someone manually adding in the  @procID to the initial call)

This is quite a bit of work depending on how many things you want to audit. and generates overheads in your db but will get you exactly what your after just in.

2.
The easiest way is each of the different routes into your system has its own login, so you have a login for updating users credentials, a different login for another action. This once again can be a lot of work, but will get you the information you want directly from the profiler, it also will make your system a lot more secure if the logins are stored securely as if one login is compromised then there is only that very specific are is effected someone doesn't have access to your entire system

Aditionally depending on how it is being called there is the potential (if you're using php this isnt possible if you have a more complex back end with iss / .net in the background you may be able to set the program_name field for the login, once again this will show up in the profiler and if you have the proc name your calling you can amend that into the program name (WebLogin-schema.usp_procname for example)

Hope this somewhat helps
Ste


COUNTING results per row in self referenced table

Your answer is on the right track but needs several adjustments to be correct and efficient:

The INNER JOIN will only give you clients with 1 or more children, not the ones with 0 children. You need an outer join for that: client c LEFT JOIN client p ...
If there is a UNIQUE constraint on client (user), then the group by is ok. Otherwise you may have 2 users with same name (but different id). To differentiate them, GROUP BY c.id, c.user is needed, which is not bad to have anyway for efficiency.
The date math and conditions are not entirely correct, will fail on January dates. They need correction. Even better to make the conditions sargable so indexes on createdAt may be used.
If the date conditoins are to be checking the children createdAt timestamps, then they need to be move to the ON clause.

The query improved with all the above points:

SELECT
    COUNT(p.idFather) AS totalInvited,
    c.user AS clientName
FROM client c
  LEFT JOIN client p
    ON  p.idFather = c.id
    AND p.createdAt >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 2 MONTH
    AND p.createdAt  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 1 MONTH
GROUP BY c.id, c.user
ORDER BY totalInvited DESC ;


What happens if one DC goes down in Cassandra multi DC setup?

All writes are always happens to all DCs (see the diagram in DataStax Architecture guide).

But if the one DC is down, the nodes in the other DC will detect this, and will start to collect hints during the configured hints time window (3 hours by default, and cross-DC hints aren't disabled) - if servers in first DC comes back during this window, hints will be replayed


Why does this query return users not born on the date provided?

The OR clause is short circuiting your selection. OR binds lower than AND.  Use parenthesis to group that criteria as follows:

Select *
From Client
Inner Join Address
On Client.num = Address.cl_num
Where
(
Client.fname LIKE ‘%john%’ and Client.sname LIKE ‘%doe%’
Or
Client.fname LIKE ‘%doe%’ and Client.sname LIKE ‘%john%’
)
And Client.year = 1973
And Client.month = 06
And Client.day = 05

Precedence rules for operators exist in all languages.  For example, 3 + 5 * 2 will give you 13 and not 16 in most programming languages.   * has higher precedence that +, the same way AND has higher precedence than OR. If you want the addition, 3+5, to happen before the multiplication, you'll use parentheses: (3 + 5) * 2


How do I prevent my Windows Server from being automatically rebooted by SQL Server updates?

To prevent SQL Server updates to be installed automatically, you need to:

open up PowerShell window (Command prompt will also work)
type "sconfig.cmd" and run it
press 5 to select "Windows Update Settings"
set it to "M" which is "Manual"

Important: choose "Manual", NOT "DownloadOnly", because even with "DownloadOnly" setting, some Windows Updates that Microsoft considers very important, may be still installed and machine will be rebooted (I've experienced it myself)
This way you have total control over when and how to install Windows and SQL Server updates


ORACLE 11g Know Insert Record Details which Failed to insert

Use SQL Loader Utility with following control file format.

options(skip=1,rows=65534,errors=65534,readsize=16777216,bindsize=16777216)
load data
infile 'c:\users\shena\desktop\1.txt'
badfile 'C:\Users\shena\Desktop\test.bad'
discardfile 'C:\Users\shena\Desktop\test.dsc'
log 'C:\Users\shena\Desktop\test.log'
append
into table ma_basic_bd
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(fs_perm_sec_id,
"DATE" "to_date(:DATE,'YYYY-MM-DD')",
adjdate "to_date(:adjdate,'YYYY-MM-DD')",
currency,
p_price,
p_price_open,
p_price_high,
p_price_low,
p_volume)

You are requested to use the conventional path loading so that we can get the rejected(rejected because of datatype mismatch and business rule violation) records in .bad file. Conventional path loading is a default option.

Following URL can be used for the detailed knowledge.

https://youtu.be/eovTBGAc2RI

Total 4 videos are there. Very helpful.


Recommendations for building a big search engine

you might consider to use an indexing framework like Apache Lucene as well. If not, I would recommend to model an inverted index like database. An inverted index uses keywords as key identifier for a set of images.

Inverted Index Table:

keyword image_ids
"sun" : [1,5,6]

Image Table

image_id  size    ...
   1     200x300  ...


How do you turn off the croak sound for Toad?

Issue: You are experiencing a noise / croak sound when TOAD starts up.

Solution: http://www.odipundits.com/2017/01/how-to-get-rid-of-croak-sound-at-toad.html

View in the top toolbar -> Toad Options from the list.
Click on Startup in the left pane after scrolling down a ways.
Uncheck the box Play Toad wave file and then click ok. Click apply instead if you wish to continue navigating through the pane.

The next time it starts up, it should not play that sound.


"Incorrect string value" while migrating from MSSQL to MySQL with Workbench

I had to do the following:

In the source configuration (ODBC FreeTDS), tab "Advanced", I set:

TDS_VERSION=7.2;ClientCharset=UTF-8

and check "Driver sends Unicode data as UTF-8".
(for more info in http://www.freetds.org/userguide/odbcconnattr.htm )

Then, in the target configuration, tab "Advanced", in textbox "Others" I added the following new line: (change it as you whish, always utf8* )

preInit=SET default_storage_engine=MYISAM,character_set_connection=utf8mb4,collation_connection=utf8mb4_spanish_ci,collation_server=utf8mb4_spanish_ci,character_set_server=utf8mb4

(for more info see:
https://dev.mysql.com/doc/refman/5.7/en/server-options.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/connector-cpp/en/connector-cpp-connect-options.html )

Finally in the Data Copy step, in Options group check "Driver sends data already encoded as UTF-8".


First there's no way using the native tools to mount a backup file and read the data in it.  That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword.  Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.


LOAD DATA statement returns a syntax error a fraction of the time

See if this works as a workaround:

field_name = NULLIF(@field_name, 'None');

Suggest upgrading to 5.6, though I doubt if something this obscure is fixed.

Also, you should change from MyISAM to InnoDB.


How to make SSMS Properties Window coincide with object selected in Object Explorer

There's nothing out of the box that will let you do this.

This is kind of confusing... because the Properties window never shows properties about anything in Object Explorer. If you right-click | Properties on anything in Object Explorer, you always get a pop-up dialog box. The Properties window only shows properties for objects that appear in select MDI-style windows, such as query windows.

Having said that, even though there's no way to do this, I'm not even sure it's such a good idea.

In order to return that kind of information to the UI, SSMS has to hit the server with a metadata query. SSMS is a data-driven application; it's just special in that it operates on SQL Server itself. IntelliSense works the same way. If you're on a production server, you probably don't want this kind of thing enabled because every time you touch a different node in Object Explorer (which is very frequently), it will have to hit the server to retrieve the data. And then you get into a debate about how to turn it off, and ......


When normalising, if you have entities with the same primary key, does it mean it should be the same entity?

Place total_extra_item_charges into the same table as the activities. You are unnecessarily duplicating customer_id and activity_d&t for a single column that you indicate will not have more than one row. Try to avoid over-normalizing.

I would also recommend a primary key on a unique identity column (e.g. activity_schedule_id). Based on your bolding of customer_id and activity_d&t, it appears you're using a composite primary key. A datetime column is not typically suitable as a primary key and is going to add significant physical overhead. I would instead add a nonclustered index and foreign key on customer_id with an include on activity_d&t.

Depending on the transactional volume of this database, you may also want to split the date and time portions of activity_d&t for quicker join lookups. E.g. activity_date and activity_time. Be mindful of the precision level you use for your time - do you need seconds and milliseconds? If you just need to be precise to the minute, be sure you aren't storing this data as you can save a significant amount of space.


One way to achieve is to log information from your default trace into a physical persistent table.

A default trace is like a black box recorder for sql server and is ON by default. It records 116  (EventID )    Audit DBCC Event (Event Description).

Only relying on dbi_dbccLastKnownGood for determining of checkdb ran is misleading as DBCC CHECKDB ('your_db_name') WITH PHYSICAL_ONLY will update the dbi_dbccLastKnownGood along with DBCC CHECKFILEGROUP.

The only way to determine of checkdb ran successfully is to actually run it and record all the error messages (if any).

Also, refer to : What event information can I get by default from SQL Server? from Aaron Bertrand.


This will work I think:

WITH x AS
(
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY category_sort, id) - 1,
              halfsize = (1 + COUNT(*) OVER ()) / 2
    FROM @yourtable
)
SELECT id, name, category_sort
FROM x
ORDER BY rn % halfsize, rn ;

Test at dbfiddle.uk:

id | name      | category_sort
-: | :-------- | :------------
 6 | Campaign6 | Advertizing
 3 | Campaign3 | Food
 1 | Campaign1 | Beverage
 5 | Campaign5 | Hot Drinks
 2 | Campaign2 | Beverage
 7 | Campaign7 | Marketing
 4 | Campaign4 | Beverage


DB Normalization ends up with 2 similar tables

The purpose of normalization is to remove insert, update and delete anomalies from the data model.  For instance, if you have a spelling error for the name of a location or say the lat-long is incorrect, you would want a single place to go to change that information.  Your solution with two tables seems correct to me.  Each column is dependent on the key, the whole key, and nothing but the key of the table.


I think that approach will just complicate your task.

What I'm thinking is a validation in a table, when your package starts insert something and then when it's completed insert something else, and then your "part2" when executed will look to the table if it's over execute this other wise wait for 5 minutes and execute this same check again.


Is using innodb_file_per_table improve speed of alter table command?

innodb_file_per_table is convenient option to manage disk space used by innodb, but it won't improve ALTER TABLE time.

As of 5.6 InnoDB supports online DDL for some kind of ALTERs. Check Online DDL page to see what's currently supported.

If not, you may consider using pt-online-schema-change or similar tool(https://github.com/github/gh-ost, https://github.com/facebookincubator/OnlineSchemaChange). It will run long time, but won't block the table.


When using class inheritance, can all PKs of the derived tables exist as PKs of the base table?

Your data model looks reasonable and normalized. If you are using the same paint_type repeatedly, you can break it out into a lookup table.
I don't understand the concern in your edit, so maybe you should explain that with more detail in a different question.


How can I programmatically grant (and revoke) temporary database access in MySQL?

It depends upon how sophisticated you want to be with your user management.

In MySQL I would create a small selection of "temporary" users with no privileges, not even to log in. When a user requests access to some part of the database, I would have a script run GRANT statements to allow them access to the resources they need, and have a cron (or other scheduled) script to automatically run the corresponding REVOKE statements.

In a more sophisticated database (like Oracle), the user can be set to expire automatically after a set period of time. Once their time has expired, they are no longer allowed to log in until you (the DBA) re-enables their account.


MS Access: adding column if not exist (or try/catch it)

Access doesn't support the IF EXISTS syntax in queries; I think your error is coming from that. It's also very difficult to do any sort of procedural logic using Access SQL. Your alternatives are to run the ALTER TABLE statement and handle the error elsewhere or use VBA to conditionally run the statement.


Replacing NULL values with one string from provided list

You can use something like

SELECT Id,
       ISNULL(Date, (SELECT TOP 1 *
                     FROM   (VALUES(2015),
                                   (2012)) R(Ryear)
                     ORDER  BY CRYPT_GEN_RANDOM(DATALENGTH(Id))))
FROM   YourTable

The reference to Id in the ORDER BY is just to make it correlated so that SQL Server is less likely to spool the result and replay it for multiple rows.
Select a column that is unique for this.

BTW: There is a CHOOSE function that looks tempting but when this gets expanded out to CASE the random number function gets copied out too and so this is not suitable for the task.

Don't use this

SELECT Id,
       CHOOSE(1 + ABS(CRYPT_GEN_RANDOM(4) % 2),2015,2012)
FROM   YourTable

Because it is evaluated as

CASE
  WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 1
    THEN 2015
  ELSE
    CASE
      WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 2
        THEN 2012
      ELSE NULL
    END
END

(And CASE ABS(CRYPT_GEN_RANDOM(4) % 2) WHEN 0 THEN 2012 WHEN 1 THEN 2015 END would have the same problem)


No shortcut at all, there is no elegant string concatention in SQL Server

You are mixing varchar and nvarchar though: datatype precedence means the entire expression will be nvarchar.


STATISTICS IO difference for heap and clustered index

Accessing either a Clustered Index or Non-Clustered Index requires traversing that b-tree structure. For some reason, on scan operations there seems to be one extra logical read. In your case you have a single page, which is the 2 logical reads. If you had enough rows to fill up enough data pages that would in turn require another level within the b-tree index structure, then you would see an additional logical read.

Heaps, by definition, have no index (b-tree) structure. Since you have one data page, the operations only need that one logical read. In such a simplistic example it would appear to be less work than the Clustered Index approach, but as soon as you get a few more data pages then you will start to see a difference since the b-tree structure will allow for going directly to appropriate data pages while the Heap still has to check all of the pages.

For example, I have a test table with a structure of:

CREATE TABLE [dbo].[GuidPkAsUI](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY CLUSTERED,
    [InsertTime] [datetime] NOT NULL
                            CONSTRAINT [DF_GuidPkAsUI_InsertTime]  DEFAULT (getdate()),
);

It has 767,968 rows in it via:

INSERT INTO [dbo].[GuidPkAsUI] ([ID])
  SELECT TOP (767968) NEWID()
  FROM   master.sys.all_columns ac1
  CROSS JOIN master.sys.objects so1;

I copied it to a new table that is the same structure and data, but missing the two constraints (i.e. no Clustered Index) using the following:

SELECT *
INTO dbo.GuidPkAsUIheap
FROM dbo.GuidPkAsUI;

The following queries:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUIheap'),
                                             0, NULL, 'DETAILED');

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUI'),
                                             1, NULL, 'DETAILED');

show that GuidPkAsUIheap has 1 level (with 3135 data pages) and GuidPkAsUI has 3 levels (with 3135 data pages, 20 index pages on one level -- intermediate, and 1 index page on another level -- root, totaling 3156 pages).

The following queries:

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUIheap;
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUI;
SET STATISTICS IO OFF;
-- 3157

shows that GuidPkAsUIheap requires 3135 logical reads (the number of data pages) while GuidPkAsUI requires 3157 logical reads (the number of data and index pages plus one). So here the logical reads for the Clustered Index are still higher than for the Heap.

I then rebuilt the tables via:

ALTER TABLE [dbo].[GuidPkAsUIheap] REBUILD;
ALTER TABLE [dbo].[GuidPkAsUI] REBUILD WITH (FILLFACTOR = 100);

Running the SELECT * FROM sys.dm_db_index_physical_stats queries above again shows the Heap to be the same but the Clustered Index now has only 10 intermediate index pages instead of 20.

Running the SELECT COUNT(1) queries above again shows the same 3135 logical reads for the Heap and 3147 logical reads for the Clustered Index (all data and index pages plus one).

Now, let's find one specific row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3

The Heap still takes 3135 logical reads. But the Clustered Index takes a mere 3 logical reads: 1 for the root index page, 1 for the next level index page, and 1 for the leaf level / data page.

Now let's force a scan as we look for a single row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3147

Here we get the same logical reads that the COUNT(1) queries get.


Does SQL Server require more RAM to hold columnstore indexed tables than traditional rowstore?

More RAM as opposed to what?  Compared to a normal clustered index, the data is highly compressed, so should take less memory during querying.  However, the process of building the CCI can be very memory intensive.  See the product documentation

  Plan for enough memory to create columnstore indexes in parallel
  Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

  The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

  If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In some cases, DOP must be decreased to one in order to build the index under constrained memory.


What is better in IN()? Sub-query or list of values

If you have the values in a table and the column is indexed, I would expect it to be more efficient than using a list of values. Note that there are several ways to so this - especially because this type of queries (WHERE c IN (SELECT c FROM x)) is not fully optimized in some, not so old, MySQL versions:

SELECT a FROM b WHERE c IN ( SELECT c FROM x ) ;

You could rewrite (if x (c) has no NULL values) as:

SELECT a FROM b WHERE EXISTS ( SELECT 1 FROM x WHERE x.c = b.c ) ;

or (if x (c) has unique values) as:

SELECT b.a FROM b JOIN x ON x.c = b.c ;

So, now you have 4 versions to test. Actual behaviour of course depends on several more factors (besides query, indexing, versions) like table sizes, values distributions, mysql settings, memory available, other queries running, moon phase, disk efficiency, etc.

In all, the best thing to do is test in your environment, all the available options and choose what works best for you.


Avamar SQL Agent running against live SQL Server databases

Please ask them and also, as max pointed out, test whether such tool can provide point in time recovery. Test various scenarios and see if it matches your RPO and RTO. I am sure its not free of cost only go if it REALLY provides some thing more than what native SQL Server Tsql backup provides. I have not found any tool more reliable than TSQL built in code for backup.

Ask them,if possible, to do a POC (proof of concept) and show you how it would be better than native TSQL backup. You have to let them know that there is no point in choosing any thing blindly if it does same work as a tool which you have free( inbuilt). You can professionally ask them above questions. Plus if you use such tool you might need to hire a person who is expert in this tool so might be some more extra cost.

Think all pros an cons and then move


MySQL - GROUP BY not working with multiple CASE WHEN statements

A syntactical version that should work (untested due to lack of ddl and sample data) is:
SELECT
CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY CASE
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign'
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END, currency.name;

I agree with @Akina that the result will be confusing when several currencies are involved. The result may end up like:
Long Campaign   42
Long Campaign   36
...

and you basically have no idea what row that represents which currency


Set in config file this line

max_sort_length=8M

And happy day!


How can I get the last inserted id from an specific table from a given stored procedure?

From here: (My emphasis below)

  The ID that was generated is maintained in the server on a
  per-connection basis. This means that the value returned by the
  function to a given client is the first AUTO_INCREMENT value generated
  for most recent statement affecting an AUTO_INCREMENT column by that
  client.

  This value cannot be affected by other clients, even if they
  generate AUTO_INCREMENT values of their own. This behavior ensures
  that each client can retrieve its own ID without concern for the
  activity of other clients, and without the need for locks or
  transactions.

A fundamental point about LAST_INSERT_ID() is that bit about per_connection - if it wasn't on that basis, then the function would be useless in a multi-user environment.

Unless there are further complications, you have absolutely no need to resort to the MAX() function - and you have no guarantee anyway that (in a multi-user scenario), your MAX(xxx) value won't be incremented by another user in the instant after you call it.


inmemory_size can not be modified online. Modify it in the spfile:
alter system set inmemory_size=200M scope=spfile;
Then restart the database.


Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift

The problem with how I used this pattern is how I wrongly tried to add characters after the - character. Basically, Redshift does support the use of carets for pattern list matching negation. So this
[^A-Za-z0-9\\.\\,-]+

should not cause any problems, but
[^A-Za-z0-9\\.\\,-\\s]+

might cause problems because it might assume a range lookup instead for looking for both - and whitespace characters.


PostgreSQL translate special characters?

Use unaccent() provided by the additional module unaccent.
Details:

'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX?
Does PostgreSQL support “accent insensitive” collations?
Postgres accent insensitive LIKE search in Rails 3.1 on Heroku

Substantial improvements went into Postgres 9.5:

  Allow empty replacement strings in unaccent (Mohammad Alhashash)

  This is useful in languages where diacritic signs are represented as
  separate characters.
  Allow multicharacter source strings in unaccent (Tom Lane)

  This could be useful in languages where diacritic signs are
  represented as separate characters. It also allows more complex
  unaccent dictionaries.

Custom rules

If you edit the default rules (like you commented) any updates (or new compilations if you compile yourself) will overwrite your manual changes. Be sure to make a backup of both the original and your edited ruleset immediately.

The unaccent() function is overloaded. While the simple form uses the default dictionary 'unaccent':

SELECT unaccent('ì ằ ú ề');

You can chose a separate dictionary with the 2-parameter form:

SELECT unaccent('my_dictionary', 'ì ằ ú ề');

I would consider not to alter the default ruleset (like instructed in the link in your comment), but install your ruleset additionally under a custom name. This way you keep the default behavior, which receives all updates automatically, and you can clearly see in your code where you apply your alternative / extended ruleset. Follow instructions in the manual or consider this blog.

And consider my link above for use in indexes or other contexts where you need the function to be immutable.


Connecting SQL Server Developer Edition to Visual Studio Windows Forms Application

1) Create an application user for your app on the SQL Server instance.  Never use an administrative user or SA.

2) Create a DSN (Data source Name) on the machine that the app will connect to the SQL Server database.  This applies even if the two are on the same machine (which would be poor architecture, but possible in a solitary dev environment).

2a) The login credentials for this DSN should be the app's user in SQL Server

3) Go to ConnectionStrings.com and generate a SQL connection string based on the DSN you just made. (https://www.connectionstrings.com/dsn/)

4) Use the connection string in your app/web config and reference it in the SQLConnection call.


mysql replication: replicate all but one table to different database on different server

So I had some lunch and figured it out !

  replicate-do-table      = database2.table
  replicate-rewrite-db    = "database1->database2"


Delete from table using FK columns, JOIN and LIMIT

This workaround is OK for my case but isn't great, since it needs to scan the entire index of the User table, so wouldn't be very portable. The user:product ratio can be up to 1:1000, and it's feasible to scan the User table first using a covering index before doing the JOIN.

DELETE
FROM Product f
JOIN (
    SELECT t.* FROM (
        SELECT DISTINCT user_type, user_id
        FROM UserTable
        WHERE MOD(shard_key,20) != 7
        ) p
        JOIN Product t ON (
            t.user_type = p.user_type
            AND
            t.user_id = p.user_id
        ) LIMIT 10
    ) x
ON
x.user_type = f.user_type
AND
x.user = f.user_id;

My takeaway is: If you can reasonably scan the entire primary key table quickly using indexes, and join with the foreign key table afterwards (ideally also using indexes), that is a solution that ensures you will be able to find ALL foreign key references if you want to batch DELETE in situations where many foreign keys reference each primary key.In this case, the User table was ~1GB and the Product table ~350GB


Change MySQL 4.1 Windows to MySQL 4.1 Linux- Problem with the key Sensitive in query

There is a system variable that needs to be set in your my.cnf file.  You will need to add:
lower_case_table_names=1 in the [mysqld] section of your cnf file.

Kevin


Can we pass a varchar/text datatype in to a variable in pgbench?

According to the documentation, pgbench variables are supposed to be boolean, integer, float, or variable-references.

However it appears to be possible to pass arbitrary text to --define so long as you don't try to use pgbench operators that will treat it as a numeric value.

pgbench doesn't support psql-style :'quoted_variable' syntax for literals or :"quoted_variable" for identifiers. So you have to quote the value you pass.

e.g. if you invoke pgbench with --define "v_username='fred'" then the variable's string value will be 'fred', including the quotes. You can then write:

\setrandom cnt 1 100000

begin;
select get_user_email('user_' || :v_username);
end;

Note that the concatenation is done in SQL, as pgbench expression language doesn't have string operators.


Why aren't unsigned integer types available in the top database platforms?

Microsoft's Jim Hogg has responded to this issue with the following:

There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.
On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.
I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.

Source: Microsoft Connect
I would add significantly to the pro list, and reiterate that their SQL engine is already doing FAR more complex things than this, and so their team can handle the added complexity. While I don't agree with their summation, this is Why SQL Server doesn't support unsigned types.
The Connect link was originally posted by Martin Smith in the question comments.


Method (Policy Based Management, DDL Trigger, whatever) to prevent logins with sysadmin from granting sysadmin to other logins

For whodunnit, there's a profiler trace event "Audit Add Login to Server Role" under security. Presumably it catches ALTER and not just the SPs specified, but that would need to be tested.

I'd suspect the guilty once caught would start assigning specific permissions instead so would trace that too.


This table is very small!

It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints).

CREATE TABLE Person.ContactType(
    ContactTypeID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NOT NULL,
    ModifiedDate datetime NOT NULL,
    CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
    CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
)

Running

SELECT index_type_desc,
       index_depth,
       page_count,
       avg_page_space_used_in_percent,
       avg_record_size_in_bytes
FROM   sys.dm_db_index_physical_stats(db_id(),
                                      object_id('Person.ContactType'),
                                      NULL,
                                      NULL,
                                      'DETAILED')

Shows both indexes only consist of a single leaf page with no upper level pages.

+--------------------+-------------+------------+--------------------------------+--------------------------+
|  index_type_desc   | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX    |           1 |          1 | 15.9130219915987               | 62.5                     |
| NONCLUSTERED INDEX |           1 |          1 | 13.1949592290586               | 51.5                     |
+--------------------+-------------+------------+--------------------------------+--------------------------+

Rows on each index page aren't necessarily in index key order but each page has a slot array with the offset of each row on the page. This is maintained in index order.

The nonclustered index covers two out of the three columns (Name as a key column and ContactTypeID as a row locator back to the base table) but is missing ModifiedDate.

You can use index hints to force the NCI seek as below

SELECT ct.*
FROM   Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE  ct.Name LIKE 'Own%';

But you can see that under SQL Server's cost model this plan is given a higher estimated cost than the competing CI scan (roughly double).

The single page clustered index scan would just need to read all the 20 rows on the page, evaluate the predicate against them and return them.

The single page nonclustered index range seek might potentially be able to perform a binary search on the slot array to reduce the number of rows evaluated however the index does not cover the query so it would also need a potential IO to retrieve the CI page and then it would still need to locate the row with the missing column values on there (for each row returned by the NCI seek).

On my machine running 1 million iterations of the non clustered index plan took 15.245 seconds compared to 11.113 seconds for the clustered index plan. Whilst this is far from double the plan without the hint was measurably faster.

Even if the table was orders of magnitude larger however you may well still not get your expected plan with lookups.

SQL Server's costing model prefers sequential scans to random IO lookups and the "tipping point" between it choosing a scan of a covering index or a seek and lookups of a non covering one is often surprisingly low as discussed in Kimberley Tripp's blog post here.

It is certainly not out of the question that it would choose such a plan for a 10% selective predicate but the clustered index would likely need to be quite a lot wider than the NCI for it to do so.


You cannot set your database to single-user mode or offline because your database is already in a RESTORING state.

Second, your script tries to use the stored procedure sp_fixusers. This is a custom stored procedure to fix orphaned users. If you need to use this stored procedure, you need to re-create it first, using the script in the link above. But it is possible that your databases uses a different version and it will be better if you use the sp_fixusers from your server/database.


How is TINYINT(1) converted to BOOL/BOOLEAN?

A tinyint column can hold values from 0 to 255 (if it is defined as unsigned) or -128 to +127 (if it is signed). The (1) in tinyint(1) is only for some formatting options and generally ignored. You could create it as tinyint(100) and it wouldn't make a difference.
Regarding the TRUE or FALSE, any int (int, tinyint, smallint, bigint) value can be used as (or converted to) a boolean value. It is considered FALSE if it is 0 and TRUE otherwise. So, 2 would count as TRUE.
To be entirely clear, MySQL does not have a true BOOLEAN type. BOOLEAN is a synonym of TINYINT(1), as the docs explain in Numeric Type Overview:

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
...
However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:
...


How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?

A simpler way to write the MERGE is as follows:

MERGE #target /* WITH (SERIALIZABLE) */ AS T
USING #source AS S
    ON S.Id = T.Id
WHEN MATCHED
    AND T.IsDeleted = 0
    AND S.Modified > T.Modified
    THEN UPDATE SET
        Modified = S.Modified,
        NoteText = S.NoteText
WHEN NOT MATCHED THEN
    INSERT (Id, Modified, IsDeleted, NoteText)
    VALUES (S.Id, S.Modified, 0, S.NoteText);

The SERIALIZABLE hint would be necessary to avoid a potential race condition. This consideration obviously does not apply to a local temporary table target, but it would be remiss of me not to mention.

In broad terms, it is almost always wrong to perform any sort of filtering in the ON clause (or equivalently, in a CTE). The ON clause should only be used to specify how rows between the source and target are related.

See Inserting, Updating, and Deleting Data by Using Merge for more details.

The source and target tables should also have a unique index or key. Moreover, for best performance SQL Server should be able to guarantee that the MERGE statement will affect each target row a maximum of once. Without that, the execution plan will have to detect multiple DML operations on the same row in order to raise a runtime error if it happens. This often requires a Sort, and always requires a Segment, Sequence Project, and Assert. None of these will make your command execute any faster :)


Do you feel like DBA/DBD Is a "secure Field" still?

Yes, absolutely. Whether you use NoSQL (which means "not only SQL" not "no SQL") or an enterprise RDBMS, there is still quite a large need for architecture, data modeling, design, tuning, data security, etc. If you think throwing your databases on SQL Azure means you no longer need a DBA and it just runs itself, you might be in for a shock. Every company uses data in one way or another, and the trend I see developing is that more and more companies are moving to taking their data to big solutions. I don't anticipate the need for the DBA/DBD folks around that to dwindle, though I do sense that the nitty-gritty specifics involved in any such role are constantly evolving.


MySQL - Arrange a table so that it shrinks as much as possible?

Here's general recommendations for keeping table size small

Try ROW_FORMAT=COMPRESSED when creating InnoDB tables. There will be impact on writes.
Use index with prefix
Use OPTIMIZE TABLE often if it gets many DELETE/UPDATE's

Normalizing tables would actually result in bigger overall size, but less. It MAY help with caching, though.


How to create a cumulative sum on update in mysql-python

Try this:

add_lang = ("INSERT INTO Languages (lang, length, counter)\
VALUES (%s, %s, 1)\
ON DUPLICATE KEY UPDATE\
counter = counter + 1,\
length = length + VALUES(length);")

NB: edited to apply parameter replacement to the VALUES


Normalising Customers and Employers with a people table

The design of your tables is not, as you noted, an OOP inheritance.  The underlying model is simpler:

Do not duplicate your data!

The more data you have, the more important this is because:

Nobody will type the duplicate data exactly the same in all cases.  (Names, Addresses, etc.)
If you have entered similar data in two different tables, you will regularly need to do some Sherlocking to figure out who is whom.

Having said that, people identification (particularly customers) is difficult to get correct and consistent no matter how diligent you try to be.  Likely you will unwittingly enter some people a second or third time.  But a People table gives you one place to examine, which can be a bit of a help.

Creating a Persons table would be 2nd Normal Form.

I do not really understand what issue you are struggling with when you ask: when it "becomes an FD to justify creating the extra table". You are basically making a choice between:

Choosing to just cope with variant and duplicated data.
Set up your data to avoid duplicated data as much as possible.

I avoid duplicating data as much as possible, because it gives me fewer headaches over time.


Where clause for multiple rows and columns

Some DBMSs allow tuples to be used in IN.

WHERE  (ID, Quality ) IN (SELECT ID, Quality FROM @ItemList)

SQL Server isn't one of then and just allows a single column.

You can use WHERE  EXISTS

SELECT *
FROM   tbl_Item tI
WHERE  EXISTS (SELECT *
               FROM   @ItemList I
               WHERE  I.ID = tI.ID
                      AND I.Quality = tI.Quality)


Database schema initially not designed for scaling, how to optimize it?

I find your design a bit backword, can a delivery_note_partner_A exist without a corresponding delivery_note?
Assuming that is not the case, I would suggest:
CREATE TABLE delivery_note
( delivery_note_id ... NOT NULL PRIMARY KEY
, ...
);

Depending on how much processing of the notes for each partner you are going to do, you may consider something like:
CREATE TABLE delivery_note_partner
( delivery_note_partner_id ... NOT NULL PRIMARY KEY
, delivery_note_id ... NOT NULL REFERENCES delivery_note (delivery_note_id)
, delivery_note JSON NOT NULL
);

Whether the partner should reside in the JSON doc or as an attribute is a decision to make
EDIT:
After some discussion in a separate thread, this is the suggestion I proposed:
-- removed attributes delivery_not_parter_X
CREATE TABLE delivery_note (
 delivery_note_id int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
...
PRIMARY KEY (delivery_note_id),
UNIQUE KEY `UNIQ_1E21328E551F0F81` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- sample data with above attributes removed
INSERT INTO `delivery_note` (`delivery_note_id`, `status`, `seller_name`, `first_name`, `last_name`, `email`, `street`, `street_extra`, `post_code`, `city`, `lat`, `lng`, `phone_number`, `phone_number2`, `residential_type`, `floor`, `elevator`, `service_type`, `order_number`, `order_picker_name`, `order_flow_origin`, `delivery_day`, `delivery_hour_start`, `delivery_hour_end`, `observation`, `internal_observation`, `created_at`, `updated_at`, `delivered_at`, `route_plan_order_id`, `route_customer_id`, `route_route_id`, `delivery_dday`) VALUES
(1, 'delivered',    'Sauvage',  'Adrien',   'Thibault', 'spotier@wanadoo.fr',   '16 rue des violettes', NULL,   '77500',    'Chelles',  48.89320500,    2.59495500, '0476134831',   NULL,   'house',    'fourth',   0,  'comfort',  '32027253', NULL,   'store_order',  '2020-01-02 00:46:52',  '2019-12-30 15:23:47',  '2019-12-30 16:45:03',  NULL,   NULL,   '2019-12-30 12:11:31',  '2019-12-30 12:11:31',  NULL,   93004,  8782,   NULL,   0),
...);
-- new table
CREATE TABLE delivery_note_partner
( delivery_note_partner_id int(11) NOT NULL AUTO_INCREMENT
, delivery_note_id int(11) NOT NULL
, delivery_note JSON NOT NULL
  -- example of generated column, for indexing purposes
, payment_method varchar(20) generated always as (delivery_note ->> '$.payment_method')
,     CONSTRAINT PK_DELIVERY_NOTE_PARTNER
          PRIMARY KEY (delivery_note_partner_id)
,     CONSTRAINT AK1_DELIVERY_NOTE_PARTNER
          UNIQUE (delivery_note_id)
,     CONSTRAINT FK_DELIVERY_NOTE
          FOREIGN KEY (delivery_note_id)
          REFERENCES delivery_note (delivery_note_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Since MySQL does not support indexes on JSON, this is an example of an index for attributes that are used in predicates

CREATE INDEX X01_DELIVERY_NOTE_PARTNER ON delivery_note_partner (payment_method);

-- some migrated sample data for new table
INSERT INTO delivery_note_partner (delivery_note_id, delivery_note)
VALUES (1, '{ "payment_method":"credit_card", "total_price_amount": 6500, "total_price_currency":"EUR" }')
    ,  (12, '{ "client_account_number":"ACC605739261" }')
    ,  (17, '{ "sum_owed_amount":7500, "sum_owed_currency":"EUR", "delivery_price_amount":2500, "delivery_price_currency":"EUR" }');


LSN mentioned in Backup is missing in Backupset

But i couldn't find that LSN in the results.

The tran log backup taken at 09:00 contains the lsn 38000000116100001.

  Full backup and Tlog backup occurs at the same time every 15 minutes.Does that create any issue while restoring.?

No. Full backups never affect the log chain.

  I start with a full backup restore,then i restore all tlog backups and i skip full backups which occurred in between.?

Yes that is correct.


Log all accessed objects during a certain time period

Updating answer

There are two parts to your question:

  In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great
  if that would be possible in an automated way.

This is to do with data inside tables, meaning we have to get to the row level in order to capture what has been updated/deleted(DML). This can be achieved by enabling tracking. Copied from Microsoft site:

  SQL Server 2017 provides two features that track changes to data in a
  database: change data capture and change tracking. These features
  enable applications to determine the DML changes (insert, update, and
  delete operations) that were made to user tables in a database. Change
  data capture and change tracking can be enabled on the same database;
  no special considerations are required. For the editions of SQL Server
  that support change data capture and change tracking, see Features
  Supported by the Editions of SQL Server 2016. Change tracking is
  supported by SQL Database. Change data capture is only supported in
  SQL Server and Azure SQL Database Managed Instance.

You may refer this link from Microsoft to understand details. Alternatively you can enable auditing on tables for any DML operations and you may also club these two as mentioned here.

For more details on Change tracking feature in SQL server database, you may refer another link from Mr. Arshad Ali.

Above will help you for tracking DML operation only however for tracking select, its really difficult and can be traced only by enabling auditing as mentioned here.

  Here I'm thinking of something like "Run the tests and log which
  objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)".
  Then afterwards we could remove all which haven't been accessed. Is
  there a way to collect this information in SQL Server 2016?

As data level is tracked, we can go a level higher i.e. object level. In order to handle the usage/non-usage of any object, you may refer below.

There is a wonderful article from Mr. Ahmad Yaseen on finding unused objects in any given databases. You may Below is the query which can be used:

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )
AS (
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U'
     AND NOT EXISTS (SELECT OBJECT_ID
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate
FROM UnUsedTables
ORDER BY TotalRowCount ASC

Kindly note that, this will work from the last restart time of SQL service/server as mentioned:

  We will use the sys.dm_db_index_usage_stats system DMV that stores the
  number of scans, seeks and updates performed on the database tables
  since the last time the SQL Service was restarted.

You may read more details here.

There are other articles also, which could be referred for this purpose. For instance, you may use sp_blitzindex for finding similar info from Mr. Brent Ozar.

For checking access date/time for views/function/SPs, you may refer below question:

Query last time a view, funcion or sp was last used/read

There is another article from Mr. Bart Gawrych on the same topic:

  select [schema_name],
           table_name,
           max(last_access) as last_access
    from(
        select schema_name(schema_id) as schema_name,
               name as table_name,
               (select max(last_access)
                from (values(last_user_seek),
                            (last_user_scan),
                            (last_user_lookup),
                            (last_user_update)) as tmp(last_access))
                    as last_access
    from sys.dm_db_index_usage_stats sta
    join sys.objects obj
         on obj.object_id = sta.object_id
         and obj.type = 'U'
         and sta.database_id = DB_ID()
    ) usage
    where last_access < dateadd(month, -3, current_timestamp)
    group by schema_name,
             table_name
    order by last_access desc;

This again depends on system DMV, so be wary of last reboot of service/server.

Note: Your question is on how to find list of objects which are getting used however there are tonnes of article on finding unused objects, you may tweak them a bit to get list of used objects or else if you do manage to find unused objects, that should also serve your purpose.

Please let us know if this helped you in achieving your objective.


Dynamic Ports are being set to different ports

The point of dynamic ports is that you don't care what port the server runs on.  SQL Server Browser directs clients to the dynamically chosen port.

If you don't want dynamic ports, just assign a port at the outset, then it will never change.


Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now)

By default, PMON process registers with one listener and considers that "good enough."

This article describes how to set up two listeners and then how to manually register your database with the second listener.

You need to register the listener with the database:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=<IP address or hostname>)(PORT=1523))' scope=both;

You may need to issue this statement to list BOTH listeners. Otherwise, it may default to only the new, manually configured, one upon restart.

alter system set local_listener='Listener 1' scope=both;

See the Oracle Docs, where it shows how to add two listeners via comma-separation.

ALTER SYSTEM SET LOCAL LISTENER=["]listener_address["][,...];

Note that this link lists several other methods depending on the environment and what you're trying to achieve, so you may want to read that link if this first method fails to get what you want.


Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

I think, in general, I'd say probably not.  But it depends on a lot of factors.

Bulk Loading (minimal logging)

SELECT...INTO will benefit from bulk load optimizations.  The INSERT portion of the MERGE query might benefit from these optimizations, but the UPDATE portion won't.  So the MERGE statement will likely result in more transaction log writes, making it slower.

DROP vs DELETE

SELECT...INTO will also benefit from the fact that DROP TABLE is a metadata operation, and thus will "remove" all the rows very quickly.  The DELETE portion of the MERGE query will be logged normally, resulting in more transaction log writes, making it slower.

Concurrency

SELECT...INTO will benefit from reduced locking / blocking / concurrency issues, because it will have exclusive access to the target table.  The MERGE statement will potentially have to deal with lock escalation, blocking by other processes, etc, unless you provide TABLOCK hints.

Index Maintenance

The MERGE statement also has to deal with index maintenance (you mentioned there is a clustered index and a nonclustered index on the target table in this scenario), whereas the SELECT...INTO does not.  To mitigate this, you could potentially drop the indexes and recreate them after the MERGE has run.

Additional thoughts

All of this somewhat depends on what the breakdown (in terms of # of rows) is between INSERTs, UPDATEs, and DELETEs in the MERGE statement, but in general I would expect the SELECT...INTO case to be quicker unless the MERGE statement has been designed very carefully.

Some related information that might be interesting to you and this situation:

Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable
The Data Loading Performance Guide

Sidebar: in a comment, you mentioned:

  I'm asking because in my case I have to synchronize about 300 million records, and with my testing of only 5 million records, the SELECT INTO took about 30 seconds, the MERGE took about 4 minutes. :(

To compare "apples to apples" make sure to include the time it takes to create the two indexes you mentioned for the SELECT...INTO case:

  In my specific case, there's actually no indexes on TableB at the time of the SELECT INTO, but a single unique clustered (PK) and a single non-clustered index is created on it on after the data is inserted. If a MERGE query was used instead, then both tables would have unique clustered indexes and non-clustered indexes on them.


Prometheus High Memory and CPU Usage in PMM

100% RAM -- You are probably swapping, which is terrible for performance.  Lower innodb_buffer_pool_size a little to avoid swapping.

200% CPU -- Poor indexes and/or poor formulation of queries.  Please provide some of the queries and SHOW CREATE TABLE; there may be a quick fix.

"out of order" and "different value" -- Either a bug with the collection mechanism or a bug in Percona.


The syntax error comes from the INSERT statement. The statement INSERT...VALUES does not support WHERE clause.

Possible you want this -

INSERT INTO WORK_LOG (
  ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE
)
SELECT
  ENTRY_NUMBER_FK,
  EMPLOYEE_ID_FK,
  WORK_ORDER_NUMBER_FK,
  ENTRY_TIME,
  WORK_DONE
FROM
  WORK_LOG_TRACK
WHERE
    WORK_LOG_EDIT_NUMBER = ID;


It's not easy to say anything for certain, but one thing I noticed is that you're also using an old version of the galera RPM (25.3.12). Version 25.3.20 is the one distributed with the version of MariaDB you're using (10.2.10).


How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified statistics, and even then you only need to do this when you notice performance degradation (i.e. check what your monitoring system tells you).

Per Ola's site, you can do something like this:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

The other thing you can do, for when you do need to defragment indexes (if for example you have deleted a large amount of data), is using SQL Server's Service Broker to set up the maintenance plan for multiple tables in a batch.

That article is in-depth and difficult to summarise in this reply, but it comes down to creating an asynchronous queue, that fires Agent jobs in parallel.


You can't disable a particular job step.

You have two options, the way I see it:

Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)
Create a separate job with the step's logic and then only use the new job that does the work


Schema design for storing item combination with order

The design doesn't seem awful, but I think you could solve your update issue by changing how you are assigning a priority number. Instead of using 1, 2, 3, 4, ..., you could use a higher range between the values, such as 10, 20, 30, 40, .... You might find you need an even larger set like 50, 100, 150, 200, ....

By increasing the range, you have some flexibility when a user changes the ordering of a given item. Instead of having to update all of the records, you could now use an available number between the existing two and save yourself the headache. For example:

AAA -> 10
BBB -> 20
CCC -> 30

We want to move CCC to be the second item (in between AAA and BBB), so we change 30 to 15. So now:

AAA -> 10
CCC -> 15
BBB -> 20

Of course, you'll still have to re-assign all the priorities if the user changes too many of the items, as you'll eventually run out of empty numbers. When you're doing your selection, just remember to order by the priority.


Compact Large Objects in Reorganize Index task

Large objects (LOBs) are stored in different "pages".  A page is a small section of a database, typically an 8kb chunk.  For rows that contain LOB columns, the normal page will contain a reference to the LOB page.
From the alter index manual page:

WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that
contain large object (LOB) data are compacted. The LOB data types are
image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and
xml. Compacting this data can improve disk space use. The default is
ON.

The default is "on" and it's hard to think of a case where you'd want to turn it off.  Perhaps if you had a strict time window for reindexing, and turning LOB compaction off would allow you to meet the window.


is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?

Yes. And if your concern is limited to just read-only access to those tables then you don't even need to create separate views, you can simply create new schema and grant select on those tables.

  is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?

Yes. If you just create views on this schema then you even don't need to worry to manage separate tablespace because  views(is a named and validated SQL query) are stored in the Oracle data dictionary.

  if both are possible than which one should be used and why?

It's up to you. In my opinion, if you place schema in to the separate tablespace then you can transport it to another database with ease. Also it makes possible to perform point-in time recovery. Additionally, we can place such tablespace to different disks as per the IO performance requirements.

Update

You have to have granted select on FOO with grant option to user B in order for user 'B' to grant select on view to user 'C' as demonstrated below.

SQL> conn a/a
Connected.

SQL> grant select on t1 to b with grant option;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> create or replace view v_t1 as select * from a.t1;

View created.

SQL> grant select on v_t1 to c;

Grant succeeded.

SQL> conn c/c
Connected.
SQL> select * from b.v_t1;

    ID
----------
     1


filter out the records with same id having different values in a column

You could for example do this:

select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
where
not exists (select 1 from order1 o1 where o1.CartTransaction_Id = A.CartTransaction_Id and o1.Status_Id != 1)
order by A.CartTransaction_Id

If you have a lot of data, then you might to want to check other alternatives for this, like max + over


Secondary Database Replica in Reverting/InRecovery (Long time)

I waited it out for about two days and the replication is back up.

I guess it's best to let SQL Server do it and to monitor the counters pertaining to the undo (SQLSERVER:Database Replica -> Log Remaining For Undo and Total Log Requiring Undo).


Error on changing ownership of database from Files Page in database properties dialog box

The database owner is the login in sys.server_principals that owns the database, as defined by its SID.  After a restore it may easily be the case that the login that was used to restore the database is not the login that was the previous owner of the database.  (This is even more likely to happen when the database if moved between servers.)

So, there could be three settings that you are having trouble with

The database owner_sid is not equal to the database's db_owner sid. You can compare this for database ABC by:

SELECT owner_sid FROM sys.databases where name = 'ABC';

SELECT sid from ABC.sys.database_principals WHERE name = 'dbo';
Next you are getting a message saying that your proposed database owner_sid also exists as a user in sys.database_principals.  We know this from the error message about mapping the proposed owner.

So your steps are:

 USE ABC;
 DROP USER James;
 ALTER AUTHORIZATION ON DATABASE::ABC TO [James-PC\James];

You dropped the user which is not needed, since you are making it the dbo.  Once that is out of the way, then your login can be made the owner of the database.

EDIT: You can also use SSMS to delete the James user from ABC database.  Then you could return to the Database properties File tab to set the database owner. (Sorry, I tend to think scripts.)


You may have corruption in your database.
Add the following to your my.cnf  under [mysqld] file and see if mysql will start

innodb_force_recovery = 1

Increment the value as needed.
See http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html


You have to take into consideration the Full-Text Search Functions

  Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

The initial setup and configuration is very simple


MSSQL how do I query sql for a latest state record after latest date

Solved it myself.. was actually very easy..

SELECT *
FROM myTable
WHERE day >= ( SELECT max(day)
            FROM [dbo].[myTable]
            WHERE state = 'D'
            AND location = 'ams'
)
ORDER BY [location] DESC, [actual_date] DESC

Thanks anyway for looking up to my issue.


When converting a table valued function to inline, why do I get a lazy spool?

A "lazy" spool, is not really lazy in the "bad" sense of the word.  It's lazy in that it may remove the need to re-scan the input data for the given operation, resulting most of the time, in improved performance.

From this MSDN page:

  The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

  The Lazy Spool operator builds its spool file in a "lazy" manner, that is, each time the spool's parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.

You mention in your question that performance has increased by converting the function into an inline variant; I'd say that's winning.  Looking at the largest cost operator is not always the best way to optimize your code; for instance that would never inform you that moving from a multi-statement-TVF to an inline-TVF would be so performance enhancing.  If you want more specific performance improvements for the code in question, adding that code to your question will help us help you.


I suggest you read following documentation about MongoDB first. This is a very broad question for a forum like this. Unless somebody analyze your application requirements in details it is not possible to give a precise answer.

Broadly in MongoDB ACID property is respected for a single document and NOT for multi document.

Does MongoDB support ACID transactions?

Yes, but in a limited sense. MongoDB supports ACID transactions at the document level; today MongoDB does not support multi-document transactions. For many but not all applications, this is sufficient because data for a record tends to be managed as a single document. Like most databases, MongoDB uses write-ahead logging to an on-disk journal to guarantee write operation durability and to provide crash resiliency.

The distributed nature of MongoDB necessitates additional consideration beyond ACID regarding consistency and availability. MongoDB automatically maintains replica sets, multiple copies of data that are distributed across servers, racks and data centers for high availability ( see How does MongoDB ensure high availability? ) MongoDB is strongly consistent: all reads and writes are applied to the primary member by default. ( See How does MongoDB ensure consistency? ) Write operations are automatically applied to all replica set members. You can configure each write to return after success on the primary, on multiple set members, a majority of set members, or all members. Reads can be applied to the primary member, to secondary members if the primary is unavailable, to specific members exclusively (for workload isolation), or to the nearest secondary based on ping distance. You can configure your reads and writes to achieve the consistency and availability required for your application.

Read more about concurrency here.


Does pt-table-checksum point out rows that are different between Master and Slave?

By default, pt-table-checksum displays as result all tables even if there is no error or diffs.

If you want to display just errors and diffs you can add the --quiet to the invocation of pt-table-checksum

  Print only the most important information (disables --progress). Specifying this option once causes the tool to print only errors, warnings, and tables that have checksum differences.
  Specifying this option twice causes the tool to print only errors. In this case, you can use the tool’s exit status to determine if there were any warnings or checksum differences.


This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.


Why is this Full Outer Join not working?

You should do an isnull on both policynumbers so that you can group properly.

Since it's an outer-join, there's the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)


How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

The other option to Aaron's excellent advice is to just remove @schedule_uid, the procedure will create a new UID for the schedule.  That is how I moved all the jobs from our old server to our new one with out issue.


Is it possible to use txid_current value for sync

I would tag each inserted row with the current transaction ID (txid_current() or, from v13 on, pg_current_xact_id()).
The “latest safe change” is the right before the xmin of the “current snapshot”, which can be found with txid_snapshot_xmin(txid_current_snapshot()) (or pg_snapshot_xmin(pg_current_snapshot()) from v13 on). That xmin is the transaction ID of the oldest still active concurrent transaction, that is, everything older than that is done and visible.
This should make it fairly easy to build a replication system.


Your answer is correct, and should show as many tags as there are, in different rows. You can check it at Rextester.

If you want all your tags to be converted into a single column (all tags concatened), and have just one row per post, you need to aggregate (GROUP BY) and use then GROUP_CONCAT function.

Your query will look like (I've slightly changed the naming of your columns, for legibility):

-- All tags concat
SELECT
     p.post_id, p.post, group_concat(t.tag ORDER BY t.tag SEPARATOR ', ') AS all_tags
FROM
     posts p
     inner join posts_tags pt on p.post_id = pt.post_id
     inner join tags t on pt.tag_id = t.tag_id
WHERE
     p.post_id = 1
GROUP BY
     p.post_id, p.post ;

Check it here

Addendum

If you want not only tags, but something else, you can aggregate an expressions, and not just a column:

-- All tags, with id concat
SELECT
     p.post_id, p.post,
     group_concat( concat(t.tag, ' [', t.tag_id, ']') ORDER BY t.tag SEPARATOR ', ')
        AS all_tags_with_ids
FROM
     posts p
     inner join posts_tags pt on p.post_id = pt.post_id
     inner join tags t on pt.tag_id = t.tag_id
WHERE
     p.post_id = 1
GROUP BY
     p.post_id, p.post ;

Check it here.


Can I hide all databases and show only the one database belonging to a listener?

Yes.  Have each AG's databases owned by a separate login created specifically for that purpose.

  I also do not want to create a separate user for each listener

Then no. There's no way to trim the list of databases a user can see based on the endpoint used to connect to SQL Server.


PostgreSQL 10: deleting foreign key locks selects

Foreign key constraints are implemented as system triggers in PostgreSQL.
This query shows you the triggers behind your foreign key constraint:
SELECT t.tgrelid::regclass AS table_name,
       t.tgname AS trigger_name,
       concat(
          CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE ' WHEN 64 THEN 'INSTEAD OF ' ELSE 'AFTER ' END,
          CASE WHEN t.tgtype & 4 > 0 THEN 'INSERT' END,
          CASE WHEN t.tgtype & 8 > 0 THEN 'DELETE' END,
          CASE WHEN t.tgtype & 16 > 0 THEN 'UPDATE' END
       ) AS fires,
       f.proname AS trigger_function
FROM pg_trigger AS t
   JOIN pg_proc AS f ON t.tgfoid = f.oid
   JOIN pg_constraint AS c ON t.tgconstraint = c.oid
WHERE c.conname = 'incoming_account_id_fkey';

 table_name |         trigger_name         |     fires     |   trigger_function
------------+------------------------------+---------------+----------------------
 account    | RI_ConstraintTrigger_a_62074 | AFTER DELETE  | RI_FKey_noaction_del
 account    | RI_ConstraintTrigger_a_62075 | AFTER UPDATE  | RI_FKey_noaction_upd
 incoming   | RI_ConstraintTrigger_c_62076 | AFTER INSERT  | RI_FKey_check_ins
 incoming   | RI_ConstraintTrigger_c_62077 | AFTER UPDATE  | RI_FKey_check_upd
(4 rows)

You see that two of these triggers are defined on the target table, account.
If you drop table incoming, that will also drop the foreign key constraint and consequently drop the triggers on account, which requires a short ACCESS EXCLUSIVE lock on account.
That should not be disruptive, but it seems that you are running long transactions that involve account. Now the DROP TRIGGER on account has to queue behind the currently active transactions that hold locks on account, and all later transactions have to queue behind the DROP TRIGGER, which is what you observe.
You will have to find a time when there are no long running transactions involving account, then your DROP TABLE will finish quickly without blocking other sessions.
Side remark: choose bigint rather than integer for artificial primary key columns.


Absolutely.  We definitely have FOR loops.

https://www.techonthenet.com/sql_server/loops/for_loop.php

From the site:

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

There is also a "FOREACHDB" functionality and even foreachTable

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.sqlservercentral.com/Forums/Topic271576-5-1.aspx
https://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables

But quite a few articles on why it (foreachdb) may be unreliable.

Here's an Aaron Bertrand version, which is much better:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/448

This is the direct link.
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/


How to best store a timestamp in PostgreSQL?

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around:

Time zone storage in PostgreSQL timestamps
Ignoring timezones altogether in Rails and PostgreSQL

To address your concern:

passing a correctly formatted timestamp is more complex than a simple number

You can pass and retrieve a UNIX epoch either way if you prefer:
SELECT to_timestamp(1437346800)
     , extract(epoch FROM timestamptz '2015-07-20 01:00+02');

Related:

Truncate timestamp to arbitrary intervals
Aggregating (x,y) coordinate point clouds in PostgreSQL

If you want to store the current timestamp with writes to the DB, use a timestamptz column with default value now(). The system time on the DB server is typically much more reliable and consistent than multiple clients handing in their respective notion of what time it is.
For INSERT it can be as simple as:
CREATE TABLE foo (
  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()
);

And just don't write to that column. It's filled in automatically.


pg_dump custom format between postgres versions

In my experience, you can use pg_dump without formatting to backup and restore databases between versions without any issues. This is when using pg_dump to simply generate a SQL file.

example

pg_dump mydb > db.sql

However, when using -Fc as an option

$ pg_dump -Fc mydb > db.dump

You can only restore the dump file with a version of PostgreSQL that's higher i.e. more recent. So if you create the dump file with PostgreSQL 8.1, you can only restore that same file with pg_dump from a 8.1 or more recent version of PostgreSQL.

There may be other elements that must exist on the target server such as plpgsql or postgis, if those same elements exist on the source server.

Here is the documentation from Postgres on pg_dump.

Here is the relative paragraph

  Because pg_dump is used to transfer data to newer versions of
  PostgreSQL, the output of pg_dump can be expected to load into
  PostgreSQL server versions newer than pg_dump's version. pg_dump can
  also dump from PostgreSQL servers older than its own version.
  (Currently, servers back to version 7.0 are supported.) However,
  pg_dump cannot dump from PostgreSQL servers newer than its own major
  version; it will refuse to even try, rather than risk making an
  invalid dump. Also, it is not guaranteed that pg_dump's output can be
  loaded into a server of an older major version — not even if the dump
  was taken from a server of that version. Loading a dump file into an
  older server may require manual editing of the dump file to remove
  syntax not understood by the older server.


how to change the driver of a ole_dbconnection from oracle_32bit to oracle_64bit

Even in this day and age, Windows is still reliant on the good, old-fashioned DOS PATH environment variable for finding dynamically linked libraries (DLLs).

It is possible that the 64-bit installation has altered the PATH variable (usually to insert its own location at the start of the "list").  This means that the 32-bit application, while searching through the DOS PATH, will find correctly-named 64-bit libraries before the 32-bit ones that it requires.


Unable to telnet to port 1433 in SQL Server clustered instance?

Does the SQL Server instance installed on the cluster resource named SQLclustername-instance2 listen on port 1433? If you installed a named instance then is very likely using dynamic ports. See Configure a Server to Listen on a Specific TCP Port.


Everything I've read suggests that DBCC SHRINKFILE should take pages
  from the end of the file and move them to free space near the
  beginning, What is DBCC SHRINKFILE actually doing?

The steps that DBCC SHRINKFILE goes through:

DbccSpaceReclaim Reclaim free  space. E.G. by purging empty extents.
DbccFilesCompact move the non-LOB pages to before the point specified as the second parameter, as close to the beginning of the data file as possible
DbccLOBCompact move the LOB pages to before the point specified as the second parameter, as close to the beginning of the data file as possible

During the DbccFilesCompact & DbccLOBCompact pages are moved from after the parameter passed to the DBCC SHRINKFILE statement to before the parameter, as close to the data pages as possible.
 if it is possible to do so

AFTER all these page movements, the actual truncation happens and you see the size change.

Source

Monitoring the DBCC SHRINKFILE process can give you more information on which of these three takes the longest / uses the most resources (but I have noticed DbccFilesCompact running while Lob Pages where being moved when testing, will have to do some investigating there).

According to this blogpost, Shrinking files is done in batches of  ~32 pages per transaction. As to get the ability to resume the shrink when stopping the process.

The statement that when shrinking your database files, you can resume approx. where you left off is also confirmed in this answer.

It seems like nothing happened when cancelling the command, but pages would have been moved in these smaller batches. The size difference is only noticed at the end due to the truncation happening at the end of the DbccCompact events.

Fully logged

When looking at the log file while a shrink is happening, this is also confirmed due to many transaction being logged:

LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_MODIFY_ROW
... --many more LOP_MODIFY_ROW records
LOP_COMMIT_XACT
LOP_BEGIN_XACT
...

We are seeing all these LOP_MODIFY_ROW records because of the fact that shrinking is fully logged in the transaction log. Which has additional overhead.

If you don't want all the logging happening at once or you need more time in between shrink operations you could run the shrink in batches, with or without WAITFOR delays.

Another side effect of the multiple transactions is that blocking can occur on multiple occasions while shrinking or trying to shrink your file.

Again, to mitigate blocking you can try shrinking in smaller batches, with time in between shrink.

The first shrink you should try is with: TRUNCATEONLY as to not move the pages and simply try and release space to the OS.

DBCC SHRINKFILE (N'FILENAME' , TRUNCATEONLY)

  The database contains lots of binary files and XML data in the MBs how
  will that impact the shrink?

Shrinking Lob data can take a very long time due to additional index / table scan's to update the pointers. Take a look at this post by Paul Randal

In short, you could see the shrink taking a long time processing DbccLobCompact.

This is due to a pointer in the pages on the table pointing to the 'OFF ROW' values (Lob data) but not the other way around. When moving Lob Data due to the shrink, we have to update the pointer in the on row pages resulting in a lot of extra overhead.

The overhead is similar for nonclustered indexes.

@TiborKaraszi also mentioned:

  A couple of more considerations (apart from the LOB aspect). Shrink
  need an X lock, on which it will wait on forever. Moving a heap page
  means updating all NC indexes that points to all rows that were
  affected by this heap-page-movement.

Meaning that the reason for your shrink operation taking so long is that your shrink operation might be blocked by queries & that it can block other queries while it is running.

Additionally, shrinking heaps can be expensive due to the NC index updates.

TL;DR

All in all shrinking larger files with larger objects will take longer due to scanning and updating the pointers in the underlying clustered / nonclustered indexes.

Even if 1mb of lob data is moved, the objects would have to be read and updated accordingly. The speed of this will also be based on your IO subsystem.

Another valid reason could be that your query is blocked due to the exclusive locking behavior.

While the shrink is running you could check for blocking with this query:

SELECT
est.TEXT,
er.blocking_session_id,
er.last_wait_type,
er.reads,
er.cpu_time,
er.total_elapsed_time
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est
WHERE blocking_session_id != 0;

Is shrinking a single big lob record an all or nothing operation?

Another reason where I was not entirely sure of is that an entire Lob object and it pages will be moved as far to the front of the data file as possible, even if not all pages are over the threshold defined in the DBCC SHRINKFILE.

EDIT: Tested this and this statement is false, only the Lob pages above the threshold are moved with DBCCLobCompact even if not all the pages of one record are above this threshold.

When testing with DBCC IND, we only see a few lob pages at the end of the file being moved, even if they belong to one record.

Extra notes

Shrinking files is single threaded
The better alternative for shrinking that Paul Randal points out in
one of his blogposts:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the    CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or    shrink it way down if its the primary filegroup)

Shrinking will cause heavy fragmentation