pt-table-checksum - Diffs cannot be detected because no slaves were found (1)

According to the Documentation on --recursion-method

  Preferred recursion method for discovering replicas. Possible methods
  are:

METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table

  The processlist method is the default, because SHOW SLAVE HOSTS is not
  reliable. However, the hosts method can work better if the server uses
  a non-standard port (not 3306). The tool usually does the right thing
  and finds all replicas, but you may give a preferred method and it
  will be used first.

  The hosts method requires replicas to be configured with report_host,
  report_port, etc.

  The dsn method is special: it specifies a table from which other DSN
  strings are read. The specified DSN must specify a D and t, or a
  database-qualified t. The DSN table should have the following
  structure:

CREATE TABLE `dsns`
(
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `parent_id` int(11) DEFAULT NULL,
   `dsn` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
);

  To make the tool monitor only the hosts 10.10.1.16 and 10.10.1.17 for
  replication lag and checksum differences, insert the values
  h=10.10.1.16 and h=10.10.1.17 into the table. Currently, the DSNs are
  ordered by id, but id and parent_id are otherwise ignored.

You could do this setup for DSN if you want, but you do not have to go that far yet. Why?

The default --recursion-method is the processlist. This simply means that you did not run START SLAVE; on the port 3307 instance. You cannot use --recursion-method processlist on a Master where all the Slaves are stopped. When you run START SLAVE; on port 3307, a DB Connection should appear in the processlist of the Master (3306 instance) from the Slave (3307 instance). In light of this, you should just run START SLAVE; on port 3307 and then run the pt-table-checksum.

Give it a Try !!!


In SQL Server, you need a two step process to do this. The check constraint can't have complex logic in it, but it can reference a function which can.

This constraint is a pretty big red flag for how the schema is designed. Looking at your other questions, it looks like you're aware of this and have limited options to work with.

CREATE TABLE TestTable (TestTableId INT IDENTITY(1,1), Jobid INT NOT NULL, GxpId INT NOT NULL)

GO
CREATE FUNCTION dbo.TestTable_GxpCountForJobId_fn(@JobId AS INT)
RETURNS INT
BEGIN
    DECLARE @GxpCount INT
    SELECT @GxpCount = COUNT(*)
    FROM (
        SELECT DISTINCT
            GxpId
        FROM TestTable
        WHERE JobId = @JobId
    ) AS DistinctGpx

    RETURN @GxpCount
END
GO

ALTER TABLE TestTable
ADD CONSTRAINT TestTable_SingleRelation_ck
CHECK (dbo.TestTable_GxpCountForJobId_fn(JobId) <= 1)
GO

INSERT INTO TestTable VALUES (1,1)
INSERT INTO TestTable VALUES (2,3)
INSERT INTO TestTable VALUES (1,1)

-- This will fail
INSERT INTO TestTable VALUES (1,2)


Trying to better understand how SELECT rights are managed in a/my SQL Server db

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html


Autopostgresqlbackup - DBEXCLUDE doesn't works

Other values and comments like DBNAMES suggest the syntax is a single pair of double quotes around a space separated list of DB names:

DBEXCLUDES="DB1 DB2 DB3"

Which is a syntax easy to handle for shell scripts.


There are basically 3 options:

An app or query is changing the data
Storage is becoming corrupt (which can be due to the OS, HW, storage, etc.)
You've hit a bug in SQL Server

It sounds like you've ruled out #1 (although I'm going to come back to that in a minute - but let's rule out the other stuff first.)

To start ruling out #2, run DBCC CHECKDB on all your databases. I know, you're worried about the filestream data, but it's possible that you're starting to experience corruption in other parts of the database, too. Check all of the databases for corruption, and if anything pops up, stop troubleshooting filestream and start troubleshooting the bigger issue instead. Here's my corruption troubleshooting checklist.

To start ruling out #3, open a Microsoft support case. It's $500, and they work the problem with you until it's done. (Normally I'd say to patch SQL Server to the latest cumulative update (CU) first, but I don't think I've seen any filestream corruption issues in there.) The MS support case may not be a fast fix, but I'd wanna get that process started sooner rather than later. Because filestream is a bit of an edge case feature, it's entirely possible that you've hit a bug that other folks haven't hit yet.

Those two are pretty quick - so if they don't turn up anything, then it's time to refocus on #1 (which would still be my lead suspect.)


If you put everything in one table, you will have a bigger, redundant table.

If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.


SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available)

I had the same problem, and solved it.

You need to add 2 strings in the Windows Registry via regedit.exe at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer:

DefaultData
DefaultLog

For me, their values are both C:\Users\neil\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0.


Truncate parent table in Oracle when child table is empty

No, there is no such hint.
What you can do is

alter table {child_table} disable constraint {fk_constraint_name};
truncate table {parent_table};
alter table {child_table} enable constraint {fk_constraint_name};


Which terms are used to refer a row in database?

It's right to use Entity name for prefix/suffix to represent a table schema as your class Entity is a mapping to table schema not the data of the table.

And in terms of data mapping a single instance of the Entity will always represent the row as the row will have all those columns which are attributes in entity and a List of the Entity will represent a table.


how to restore differential back daily basis on sql server DB

This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored.

Please have a look on the below blog.

http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/


How to iterate through table and modify entrys in an "if" statement with MySQL

What version of MySQL are you using? MySQL version 8 has Window Functions that support last_value().

Shy of using MySQL 8, you have two options,

Write a PROCEDURE that uses cursors in the database
Write a function that uses a cursor in a client side language.

My own preference, if I was to do this task in MySQL would be to write it outside of the server if that was reasonable (the data could be moved off-server), if not (we're talking about millions of rows) I would write a PROCEDURE


What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

If you're making regular backups of the log file or are running in SIMPLE recovery mode, the log file should mostly consist of free space; shrinking it will just truncate the free space from the log file.  This is usually safe to do, but you need to realize that the log file will likely just grow back to the previous size....when this happens the log will auto-grow, which can cause a performance hit every time it happens.

The only real reason for shrinking it is if you're low on disk space, if the log is filling up the drive you should consider adding additional disk space as a long term solution.


Using results from one query as the parameters for another

Consider using Common Table Expressions (CTEs).

I don't have your table structures nor your queries, but what I would envisage is something like this:

WITH cte1 AS
(
  Perform your 1.SELECT id of rows that match a certain criteria (we'll call this result set "X")`
),
cte2 AS
(
  Perform you 2.SELECT certain fields from rows that are titled "Cancelled-[X]"
)
UPDATE rows from [X] with information from "Cancelled-[X]";

There are some restrictions on CTEs. You can't perform UPDATEs from them for example. I'm not really a MS SQL Server person, but here are a couple of references which should help (1, 2).

For an example of them in action, see my post here. It's a PostgreSQL solution, but the MS SQL Server syntax is virtually identical.


Can Indexes mitigate untrusted Constraint

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow.  As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance).

I think you should figure out if the Dev team has valid reasons why they don't want FK's or if they are just being lazy.  Based on the information you've provided it doesn't seem like they have a well articulated reason why they don't care about the bad data.

As @AaronBertrand hinted if the Dev team is adamant that FK's shouldn't be enforced, AND business doesn't care about dirty data then you should probably remove the FK constraint.  If the business cares about dirty data and the dev team doesn't then that is a whole 'nother ball of wax.  Keep in mind, you can still use indexes to make queries faster regardless of whether you have FK relationships  between your tables.  You just won't get the performance benefits of a FK.  In general terms this is like how a non-covering nonclustered index scan is faster than a table scan, but a covering nonclustered covering index scan is even faster.

In summary here is your plan of attack:

Get a real answer why the Dev team doesn't want FK's to be enforced.
.....
Make your queries faster.


Is REPEATABLE READ the right choice for validation before modifying data?

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

REPEATABLE READ is probably the right isolation level to use in this case, assuming the data you are checking does not change very often. This isolation level guarantees that rows actually read earlier in the transaction will continue to exist, unchanged, until the transaction completes by taking and holding shared locks.

A transaction running at this isolation level may miss rows that existed at the start of the transaction, but which moved during the transaction in such a way that they moved from in front to behind the seek or scan trying to locate them. It might also encounter new rows on a second run of the same query that did not exist at the beginning (phantoms).

So, in rare cases, your transaction might fail to approve an insert that would succeed if tried. It would not result in a validated insert subsequently failing, which is probably your primary concern.

If you need a more complete guarantees of isolation, consider SERIALIZABLE - but ensure the proper indexes exist to support minimal key-range locks.

Using SNAPSHOT isolation (as I mentioned on your previous question) would provide a consistent view of the data (as it appeared at the time the transaction started), but you might encounter an update conflict when trying to insert the 'validated' entry, if any concurrent modifications result in the foreign key checks failing. This would not provide the level of detail you need for a meaningful error message, so SNAPSHOT is not the right choice here.

For more information see:

The Repeatable Read Isolation Level
The Serializable Isolation Level
The ACID properties of statements and transactions


I found this at http://zarez.net/?p=2192 (author not immediately evident) - how to cheat around this.  RAND() may count as "a side-effecting operator" because it updates the seed-number that the RAND() function uses, ready to produce the next "random" number.  Here's the cheat:

CREATE VIEW Get_RAND
AS
SELECT RAND() AS MyRAND
GO

You can read a value from a view like that in a function, but maybe there's some deep reason not to... or on the other hand, maybe it was forbidden in the first place without a good reason?


How do I add minutes to a time data type?

You can't use lazy shorthand arithmetic with the new types. Try:

DATEADD(MINUTE, @MinutesToAdd, @StartTime)

Note that even though you have protected your @MinutesToAdd from overflow, you haven't protected the result from overflow. This doesn't yield an error, however, just might not be the result you're expecting.

DECLARE @StartTime TIME(0) = '23:59';
DECLARE @MinutesToAdd INT = 20;

SELECT DATEADD(MINUTE, @MinutesToAdd, @StartTime);

Result:

00:19:00

I assume this must go through some type of internal conversion, because you couldn't get that result by saying:

DECLARE @StartTime TIME(0) = '24:19';

Result:

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

You need to consider how you want to handle calculations that lead to either @EndTime or both @StartTime and @EndTime to be in the next day.

Also - to address another new requirement in your "ideal answer" - there is no loss of precision. As per the documentation, the return type of DATEADD is the same as the input:

  The return data type is the data type of the date argument, except for string literals.

Therefore, TIME in, TIME out.


Design Pattern to Reduce Number Of Columns

Look at your table from the feature usage side:

If you always access a single record by id, or work with data sequentially (example - answer for all questions) - continue to use a single table
If you plan to make any group by, filter or search operation over a range of records - main table PK + columns for these operations, second (and more) - other tables. Example - select query for specific topic.
Have TEXT/BLOB - move to second table
If number of columns unpredictable - look for JSON type of column

Real practice shows that 3-5, max 10 parameters are used for search and group by.
These columns have indexes, and if indexes are created properly (including for foreign keys) - all operations will be fast.


Find the most recent row where no exact match on date is found

How about the following query?  This query will take the exchange on the query date if it exists.  If not, it will find the most recent exchange date prior to the query date and use that date's exchange rate.

DECLARE @Query_currency VARCHAR(3) = 'SGD',
        @Query_date DATETIME = '2015-09-05 00:00:00'
SELECT TOP(1) Currency, Exch_date AS Observed_exch_date,
    @Query_date AS Exch_date, Exch_value
FROM ExchRate
-- Find the exchange rate on the query date for this currency
-- If there isn't one, find the most recent exchange rate
WHERE Currency = @Query_currency
    AND Exch_date <= @Query_date
ORDER BY Observed_exch_date DESC

Looking up exchange rates in batch

If you need to find the exchange rate for multiple rows at a time, you can use CROSS APPLY to find the most recent exchange rate for each row. If you make sure that you have an appropriate index on (Currency, Exch_date) that includes the Exch_value column, looking up the exchange rate for each row will perform a single seek with no additional sort needed.  You can see a full demonstration in this SQL Fiddle.

SELECT p.PO_Num, p.PO_Cur, p.PO_Date, e.Exch_date, e.Exch_value
FROM PO p
CROSS APPLY (
    -- For each PO, seek to the current or most recent exchange rate
    SELECT TOP 1 ex.Exch_date, ex.Exch_value
    FROM ExchRate ex
    WHERE ex.Currency = p.PO_Cur
      AND ex.Exch_date <= p.PO_Date
    ORDER BY ex.Exch_date DESC
) e


Error: 9245, Severity: 16, State: 1. / "During the last time interval X query notification errors were suppressed"

It's the simple built-in alerts that you can configure in SQL Server i.e. (EXEC msdb.dbo.sp_add_alert @params) - it emails the DBA team when that error is captured.

Since this particular "cause"  is not exactly threatening the health of the server I was wondering "other than a mailbox rule" if there was a way to "omit" these particular ones containing "notification errors were suppressed" - so basically that they're not sent at all.


Can I deactivate log shipping jobs without raising errors?

This normally occurs when the log-shipping is not removed cleanly. To remove the orphan entries you have to delete them from msdb logshipping tables.

On Primary :
delete from msdb..log_shipping_monitor_secondary
where secondary_database = '' -- secondary db name goes here
delete from dbo.log_shipping_secondary
where primary_database='' -- primary db name goes here

On Secondary:

delete from msdb..log_shipping_monitor_primary
where primary_database='' -- primary db name goes here

delete from dbo.log_shipping_primary_secondaries
where secondary_database='' -- secondary db name goes here


Table/Column Character Encoding and Indexes

I found my answer here.  Quick answer is - they need to be the same collation/encoding to use an index.
https://stackoverflow.com/questions/2266241/utf-8-vs-latin1-mysql-indexes-not-used-on-utf-8


SQL Server cannot obtain a LOCK resource at this time

the machine has 48GB memory and only ~14GB is used. SQL Server is configured to use a max of 2,147,483,647MB of memory.

This does not tell wether the locks memory is exhausted or not. SQL Server will use for the lock manager either the configured sp_configure 'locks' value or, by default, some percent of the available total memory. The lock manager can well exhaust the available locks memory w/o the total memory exceeding the 14GB you see. you need to check SQL Server, Memory Manager Object:

Lock Blocks   Specifies the current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
Lock Blocks Allocated Specifies the current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
Lock Memory (KB)  Specifies the total amount of dynamic memory the server is using for locks.
Lock Owner Blocks Specifies the number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
Lock Owner Blocks Allocated   Specifies the current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.

However, in your case, I do not believe the problem to be lock manager memory exhaustion. It could be a lock held by an orphaned transaction, although is very unclear from your explanation. To begin with, post the exact error message your application is seeing. Normally applications either get the lock, or they wait. An infinity. A 'could not get the lock' message would imply some very specific context (eg. attempt to obtain a lock for ALTER DB). So is very important to know what message you get, and the exact statement being executed when you get the message.
Next step, depending on the result of the previous steps, is to look into sys.dm_tran_locks.


Select rows from my table where a character column matches a pattern

Use a regular expression:

SELECT *
FROM   tbl
WHERE  name ~ '^\d+ .+';

The regular expression explained:

^ .. start of string
\d  .. class shorthand for digits
+ .. 1 or more of the preceding atom
 .. plain space
.+ .. any character, again 1 or more of those

There are many different characters that look like a space. ' ' is just the plain space character. You might want to use the class shorthand \s instead to cover all of them.


The length of @P1 is different in all of those.
You're getting different plans because you're not explicitly setting parameter lengths in your code.
Any difference in strings, no matter how minor, will generate a new plan. Data type sizes, spaces, comments, all of them will cause new entries in the plan cache. Here's a demo video illustrating the problem.
The solution is to declare your variable size as the same length as the data type of the column it's being inserted into. This will give you predictable plan reuse.
The way you do this will vary based on how the insert is being performed. If you're using stored procedures, you can update the parameters to use the same length as the columns. If you're using .NET's AddWithValue() method (which is evil), use one of the APIs that allows you to set the parameter length.


Calculation Not Displaying Proper Answer

In addition to correcting the parentheses, if you want your output to be .055172 then you should cast (val7*.22) as an integer in your equation.

http://rextester.com/SQP94408

Declare @Hold Table (val1 float,val2 float,val3 float,val4 float,val5 float,val6 float,val7 float)
Insert Into @Hold values (145, 10, 20, 30, 40, 50, 60)
Select
 (
   (val1)
  -(val2+val3+val4+val5+val6)
  + (cast((val7*.22) as int)) -- this results in 13
  )  /val1
FROM @Hold
/* results in 0.0551724137931034 */

Select
 (
   (val1)
  -(val2+val3+val4+val5+val6)
  +(val7*.22)  -- this results in 13.20 not 13
  )  /val1
FROM @Hold

/* results in 0.056551724137931 */


::, as I understand IPv6 indicates as many chunks of all zero as are needed to fill out the full length.  The question comes as to whether MySQL treats % as a single chunk and treat fdd7:03d7:6247::% as fdd7:03d7:6247:0000:0000:0000:0000:% or interpret it some other way.  (Sorry, I don't know the answer.)

A second problem is that /56 splits the middle of some chunk.  I don't know if that can be represented with %.

Anyway, here's my best guess:

'fdd7:03d7:6247:00%'

This confirms PacoHope's contention that leading zeros in each segment are optional:

SELECT HEX(INET6_ATON('1111:698:2222::'));
         -->           11110698222200000000000000000000


A low cardinality column index and SELECT

The answer is "It Depends, you didn't provide enough information".

Put yourself in the shoes of the optimizer.
You see this query that does a SELECT *. C is most likely not the only column in the table, let's say you have both columns A and B as well.
That means that an index seek on C will require a lookup operation for each row to bring back A and B from the table.
Now it boils down to the cost - If you have 1M rows, and IF the optimizer can realize that your query will return 1% = 10,000 rows. That means the cost of using the index is performing the index seek, getting 10K rows, and then performing 10K lookups to bring back columns A and B.

Unfortunately, MySQL does not maintain histograms like some other engines, but only the density vector... So it may estimate that ~10% of the rows will be returned, and with that info the estimated cost will be much higher.

The alternative, is to scan 1M rows and filter 'on the fly' without using the index.
Which is cheaper? I don't know - that depends on the size of the table. If A and B are both BOOLEAN columns which take up very little space, it might think that it will be cheaper to scan the table. If A and B are huge BLOBS, it most likely will increase the estimated cost.

BTW.. if instead of using * (which I assume you only gave as an example), you would list only the minimal set of columns that you need, let's say A and C, then a composite index on (C,A) will always be the cheapest choice as you just saved yourself the lookups..

HTH


Unable to Upgrade SQL Server from 2012 to 2016 SP2 in Cluster environment

Ideally in-place upgrade is not very much recommended as this poses multiple risk and has potential to bring your whole database down. You may perform the same on a different server using existing set-up and replace the old servers with new server(set-up) in your go-live activity.

Having said above, few customers go for this upgrade in order to save licensing and servers. From the error message, its very clear that MSSQL service is having dependency on SSAS. Please refer the below screenshot of dependency tab:

No Dependency:

With Dependency:

Service depends on other service:

[

Other service depends on this service:

Based on terminology, I hope its clear that what service depends on other service.

This error would have been logged in either summary or detail log as described here at Mr. Pinal Dave's article:

https://blog.sqlauthority.com/2017/06/20/sql-server-upgrade-failure-cluster-resource-not-online-bring-analysis-services-server-online-starting-upgrade-process/

This also suggests that there was something wrong with the installation, meaning same set of SQL components are not available on active and passive nodes.

As you have already confirmed from the comments that it has worked, you may find details about dependencies above.

I hope above helps.


How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

I did

select *
from posts p
join tag_map tm on tm.post_id = p.post_id
join tags t on tm.tag_id = t.tag_id
join
  (select post_id, count(tag_id) as tag_count
   from tag_map
   group by post_id) counts
  on counts.post_id = p.post_id
where t.tag in('tag1', 'tag2', 'tag3')
order by tag_count desc, tag asc

and it seems to work OK according to your specs:

Order by tag count - I joined with a sub-select that retrieves the tag count per post_id and ordered primarily by that count (descending)
Order by tag name - after ordering by tagcount/post, the query orders by tag name

Here's the SQLFiddle if you want to experiment more: http://sqlfiddle.com/#!2/8fcd7/9

Update: version of the SQLFiddle with index on (tag_map.post_id): http://sqlfiddle.com/#!2/02b5e/1

PS: this is my first MySQL query, so if there are any criticisms regarding performance/style/anything else, I'd be glad to hear them and learn from them.


In short, you can't do this unless you configure some logon auditing in advance. Your options to do that are:

Configure Login Auditing. This will log all successful logins to the SQL Server event log. I usually advise against this due to the volume of logins written
SQL Server Audit: create an audit to write all logins to a file or the event log.
Extended Events: create an extended events session to write all logins to a file. There is a pre-defined template to achieve this, create a session via the wizard and select the "connection tracking" template
Logon Triggers: create a logon trigger and write logins to a table adding any further bespoke logic you choose. For example you can just update a logon count for each login so you don't insert massive volumes of data.


How to configure so the Oracle wallet password can be authenticated with a web based Oracle application?

ORA-24247 is something you would get after you already have a connection and are trying to use a network resource (such as UTL_HTTP).  It sounds like you need to create an ACL (Access Control List).  See http://oraclepoint.com/oralife/2010/10/08/configuring-wallet-manager-to-enable-https-connect-with-oracle-11g-database/.


SQL Server Not Consuming all Memory - Insufficient System Memory in Resource Pool 'internal'

Essentially, the error is saying that there is not enough memory available to Sql Server for it to grow its internal buffers - this generally happens if Windows is having to page a lot or other processes are consuming the memory.

You should also remember that just because you tell Sql Server to use a minimum amount of RAM, it can use less if the host OS needs it more - the host always takes priority and the buffer pool will get trimmed if the host starts to detect memory pressure. For instance, in the documentation for Server Memory Configuration Options it says:

  SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

I would check to see what other processes are running (for instance Service Broker), and how much strain they are putting on the server. If possible, increase the size of the VM or move some of the other processes onto other boxes.

Lock Pages In Memory Option

It is a common misconception that when you use Lock Pages In Memory that it can never be released. Using the LPIM setting prevents Windows moving pages in memory to virtual memory on disk (the page file). It does not however, prevent Sql Server from reducing its memory footprint if it detects that the host OS is low on memory. The snippet below is taken from the same page the previous snippet is taken from:

  Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks.

Sql Server uses the Memory Notification API's within Microsoft Windows to detect when the host is running low on system resources. If the host is running low, Sql Server will begin to trim the buffer pool in response. This is an important fact - it is Sql Server that is releasing the memory in response to an external event - Windows is not taking it from Sql Server. The below is taken from the Memory Management Architecture Guide:

  When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS.


Put a GO (i.e. batch separator) between the two sections. SQLCMD commands and variables are processed per each batch. I tested this and without the GO (i.e. how it is set up in the Question), I get the same behavior that you are seeing: first file exists but is empty, while the second file has the results for both stored procedure executions. This is due to both :out commands being in the same batch, hence the 2nd :out replaces the first one.

But, if I put a GO between them, each output file has just the results for that one execution:

:OUT  c:\test\tab0.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab0', @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;
GO
:OUT  c:\test\tab1.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab1' , @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;

P.S. Unrelated to this issue, you shouldn't prefix your Stored Procedure names with sp_ as that is reserved for special use by SQL Server and searches the master Database first, which could lead to a performance issue.


"Cannot continue the execution because the session is in the kill state" when querying system tables

It's possible there is corruption in those system tables.  Try running:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

On all the databases on this instance to check for corruption.
It's also possible you're encountering a SQL Server bug. Try checking the SQL Server error log for any error messages around the time you get that "a severe error occurred" error message.  You might get more details about the error, or maybe even stack dumps that are happening at that time.


Is there a reason to use extremely abbreviated table names?

Oracle has had a long standing limit on table names of 30 characters.  I suspect this is a legacy issue based off an original 16 bit environment.
The length of a table name could have some minuscule effect on performance as all the names have to be stored in a data dictionary and also parsed for queries but I don't think you could measure the hit.

A more important effect of short table names is that it's hard to work with.  I too have to maintain an enterprise database schema with short names.  There is no good reason to have short table names.  Ease of maintenance trumps obfuscation or old DOS habits every time.


How much could sequential scan gain by GPU?

You'd better ask this question on the hackers mailing list, there has been some debate about this subject in the past. The PostgreSQL developers can tell you much more about it.

http://archives.postgresql.org/pgsql-hackers/

To get good performance using a GPU for PostgreSQL, you need some form of parallel processing in PostgreSQL. This is something PostgreSQL doesn't have at the moment (for CPU nor GPU), but Greenplum already implemented this in their PostgreSQL-product for CPU's. Parallel processing is on the ToDo list, but it looks like nobody is working on it at the moment. Ask the PostgreSQL hackers about the current status.


How should I delete branch from adjacency list in SQL Server?

No need for a procedure, just combine the CTE with a DELETE

with doomed as (
  select id, ParentNodeId
  from treenode
  where id = ... --<< start node of the branch to be deleted
  union all
  select c.id, c.ParentNodeId
  from treenode c
    join doomed p on c.parentnodeid = p.id
)
delete from treenode
where id in (select id from doomed);


How to optimize information schema table in mysql?

Information Schema is not like other databases:

  They are actually views, not base tables, so there are no files
  associated with them, and you cannot set triggers on them. Also, there
  is no database directory with that name.

source - https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

and not a lot of ways for optimise queries over it
there are few recommendations (very standard for any queries)

https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

recommendations is really standard and in Your case - with count(*) not help.


Proper remote access control management in PostgreSQL

Just change pg_hba.conf from

host all all 0.0.0.0/0 md5

to rules you need. Columns are :
type (leave host),
database,
username,
IP,
access method.
For example, if you need to allow only 1.2.3.4 and 5.6.7.8 to connect any database using any username:

host all all 1.2.3.4/32 md5
host all all 5.6.7.8/32 md5
host all all 0.0.0.0/0 reject

Use
    service postgresql reload
(or smt like that, depending on your system) to apply changes.


Most efficient way to get stats on an entire SQL Server database

For checking fragmentation in 2005/2008 you can use the following script.  You need to set the @DB and @Table values.  If you define those as NULL then it will run on all databases and/or all tables.  If you do a single db make sure you execute in that DB's context (USE MyDB).

SELECT
    object_name(IPS.object_id) AS [Table Name],
    SI.name AS [Index Name],
        CASE IPS.Index_type_desc
            WHEN 'CLUSTERED INDEX' THEN 'Clustered'
            ELSE 'Non-Clustered'
        END AS 'Index Type',
    IPS.avg_fragmentation_in_percent as 'Avg Fragmentation (%)',
    IPS.avg_fragment_size_in_pages as 'Avg Frag Size (pages)',
    IPS.page_count as 'Page Count',
    IPS.forwarded_record_count as 'Forwarded Records',
    --IPS.avg_page_space_used_in_percent as 'Avg Page Space Used (%)',
    --IPS.record_count as 'Record Count',
    --IPS.ghost_record_count as 'Ghost Record Count',
    IPS.fragment_count as 'Fragment Count'
FROM sys.dm_db_index_physical_stats
    (
        db_id(@DB),
        OBJECT_ID(@Table),
        NULL,
        NULL ,
        'LIMITED'
    ) as IPS
JOIN sys.indexes as SI WITH (nolock)
    ON IPS.object_id = SI.object_id
    AND IPS.index_id = SI.index_id
ORDER BY 1,3,5

For autoshrink you can just check master.sys.databases:

select * from master.sys.databases
where is_auto_shrink_on = 1


Encounter installation problems on postgres 12

Your log lines are truncated (use --no-pager with journalctl when you want the full output) but the full error message that is the most relevant is probably:

2020-10-25 12:56:02.995 CST [7586] LOG: could not bind IPv4 address "172.18.56.1": Cannot assign [truncated here] requested
address

You did not mention what you set listen_addresses to, but a plausible explanation for this error is that it's set to 172.18.56.1, and this address is not assigned to a network interface of the host, so it's a mistake to use it in   listen_addresses.
Use ifconfig or ip a list to see the network addresses on the host.


Handling newsletter mail subscribers data

For your first problem, you may have to do it in stages. Here's one approach:

Create the new INT column - BUILDING_ID_INT
Create a new table that maps the building name text to Integer values
Write a program to populate the new BUILDING_ID_INT column based on the data in the current Building column and the values in the table created in #2.
Re-write all of your existing code to use BUILDING_ID_INT.
Delete the old Building column.

For the second problem, where the database structure changes between the time a user makes a request, and the time they confirm their request, it will vary on the kind of change. If an old column is being removed, then there shouldn't be any problem because your current insert statement should not be inserting data that's not insertable. For new columns that are added, default values can be used of it's non-nullable, and process will have to be introduced to handle these. For small volumes, it can be manually asking these users to send the additional new information. For large volumes, you'll want to write code.


There are a couple things wrong:

You should always use QUOTENAME() when dealing with concatenated values, both from the perspective that it can prevent SQL injection attacks, and because it prevents errors when invalid characters get used.
Whenever you're concatenating values, you should use NVARCHAR literals and explicit converts to your desired data type and don't rely on implicit conversions.
Use statement terminators.

Code:

DECLARE @AlterCmd NVARCHAR(MAX);

SELECT TOP 1 @AlterCmd = N'ALTER TABLE [dbo].[table] ALTER COLUMN '
                         + QUOTENAME(COLUMN_NAME)
                         + N' NVARCHAR('
                         + CONVERT(NVARCHAR(10), CHARACTER_MAXIMUM_LENGTH)
                         + N');'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = N'table_name'
       AND DATA_TYPE LIKE N'%char%';

PRINT @AlterCmd;


INSERT from a SELECT combined with a single value

Basic INSERT
INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (SELECT item_id FROM properties WHERE name LIKE 'body') p
    , (SELECT id FROM tags WHERE name ILIKE '%hoax%') t

Where the comma (,) in the FROM clause is short notation for CROSS JOIN.
That's building on your assertion that the second query on tags ...

returns 1 row of tag_id

You get a Cartesian Product from a CROSS JOIN, that's n*m combinations.
If both sides of the join return more than one row, they multiply each other.
If either side does not return a row, the overall result is no row and nothing happens.
If you want neither of those effects, one side must return at least one row, and the other side exactly one row.
For your specific combination you can also use a nested subselect:
INSERT INTO item_tags (item_id, tag_id)
SELECT item_id
     , (SELECT id FROM tags WHERE name ILIKE '%hoax%') AS tag_id  -- single result!
FROM   properties
WHERE  name LIKE 'body';

The subtle difference: This inserts all rows returned from the outer SELECT, even if the subselect on tags returns no row, which would be converted to NULL for tag_id.
Bulk INSERT
To deal with lots of such inserts at once, you would use a largely modified query. But the same rules as for the first query above apply for cardinalities in both joined tables.
INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (
   VALUES
      ('body',  'hoax')
    , ('body2', 'fun')
    , ('body3', 'love')
  ) AS it(item, tag)
JOIN   properties p ON p.name LIKE it.item
JOIN   tags t ON t.name ILIKE ('%' || it.tag ||'%');

You see the logic, right? Personally, I call this a "CROSS JOIN by proxy".


You do not need PL/SQL for that, you can use plain SQL after initializing a bind variable, for example.

variable v_pNBr number;
exec :v_pNBr := 100150;

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

Or you can simply use:

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

And SQL Developer will prompt for the value of the variable.

PL/SQL code is executed on the database server and it does not display a resultset on the client. Yes, PL/SQL code can store results in an intermediate memory area on the server, then the client is responsible for getting and displaying that content. Another way is opening a cursor in PL/SQL and returning that to the client. Or you can create a PL/SQL table function.


Select statement to retrieve xml output as below

Give this a try:

SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    t2.Phone AS "Personal/Phone",
    t2.City AS "Personal/City"
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

I have added a second code sample in response to your comment.  I have no idea if this will work, or if it is even the best way to accomplish what you want it to.  Please let me know.

SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    (
        SELECT
            t2.Phone AS "Personal/Phone",
            t2.City AS "Personal/City"
        FROM table1 t2

            INNER JOIN table2 t3
                ON t2.rid = t3.rid

        WHERE t2.rid = t1.rid

        FOR XML PATH('Personals')
    )
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');


In SQL Server, what are the typical symptoms of parameter sniffing?

It's commonly a potential cause when a stored procedure takes wildly different times to complete with different parameters.

Or when a stored procedure is seen taking a less than brilliant approach to getting data with certain parameters.

These would indicate to me that SQL Server had based it's query plan on the first parameters it had seen, but that these parameters weren't really indicative for all the possible parameter values.


postgresql cumulative counts in date range


SET NOCOUNT Error in handling SQL call after upgrade

SQL Server Configuration has an option called, appropriately, user options, which can be set using the sp_configure system stored procedure.  I wrote a blog post on SQL Server Science showing how to inspect and set the user options.

In brief, you can get the "config value" from the old server, using this:

EXEC sys.sp_configure 'user options';

Then, set the new server to use the same options via this:

EXEC sys.sp_configure 'user options', <config value>;
RECONFIGURE

(replace the <config value> with the value from the old server).


MS SQL "migration" to older version - problem with Attach

Question 1 - NO
You cannot restore or attach a database from a newer version of SQL to an older version.  The only way to go backwards is to use a bacpac to export the schema and data and then import again.
Question 2 - Compatibility Level
This mainly controls whether certain features are enabled on the database (such as JSON), and it also controls which cardinality engine is used for estimating plans and their various costs.
Question 3 - How to go backwards
I haven't used the tools you referenced so I don't know about them.  But I have used bacpac (included with SQL Server) to move databases around before.  It works better with smaller databases.
You can also use SSIS to migrate the data although that may be a lot of work compared to just using a bacpac.
https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/


download mysql slow query log on google cloud sql

After a lot of digging I found an answer.  It is not perfect as the quality of data may need some massaging, but it works.
gcloud logging read projects/yourproject/logs/cloudsql.googleapis.com%2Fmysql-slow.log --format json| jq -rnc --stream 'fromstream(1|truncate_stream(inputs)) | .textPayload' > slow-log-timestamp.log


Best is to refer : Stairway to SQL Server Indexes

You leave your house to run a few errands.  When you return, you find a message from your daughter’s softball coach waiting for you.  Three of the girls, Tracy, Rebecca, and Amy have lost their team caps.  Could you please swing by the Athletic Products Store and buy caps for the girls.  Their parents will reimburse you at the next game.
You know the girls and you know their parents.  But you do not know their hat sizes.  Somewhere in your town are three residences, each containing a piece of information that you need.  No problem, you’ll just call the parents and get the hat sizes.  You reach for your phone, and you reach for an index – the white pages of your telephone directory.
The first residence that you need to reach is that of Helen Meyer.  Estimating that “Meyer” will be located near the middle of the population, you jump to the middle of the white pages; only to discover that you are at the page whose heading says “Kline-Koerber”.  You make a smaller jump forward and reach the “Nagle-Nyeong” page. One even smaller jump backwards puts you at the “Maldonado-Nagle” page.  Realizing that you are now at the correct page, you scan down the page till you reach the “Meyer, Helen” line and obtain the telephone number.  Using the phone number, you reach the Meyer residence and obtain the information you need.
You repeat the process two more times, reach two other residences, and obtain two more hat sizes.
You have just used an index, and you have used it in much the same way that SQL Server uses an index.

For more reading, you can refer to

SQL Server Index Basics
Kimberly's posts on Indexing
Expert Performance Indexing for SQL Server 2012.


Best way to delete large set of rows knowing field to NOT delete

In relation to the first table, appointments, make sure that you have an index on company_id column.

In relation to the mappings table, using EXISTS rather than IN may yield better performance. You can re-write your query as following:

DELETE FROM mappings AS m
WHERE EXISTS (  SELECT 1
                FROM code_mappings AS cm
                  INNER JOIN codes AS c
                    ON c.remote_id = cm.code_remote_id
                WHERE
                (
                c.company_id NOT IN (6, 753, 785, 1611)
                AND cm.id = m.id
                )
)

In the above query, you will also benefit from indexes on the mappings and code tables.

Documentation for creating indexes is @ https://www.postgresql.org/docs/current/static/sql-createindex.html. In your case, you can create indexes on the relevant tables as following:

CREATE INDEX company_id_idx ON appointments (company_id);

CREATE INDEX remote_id_company_id_idx ON codes (remote_id, company_id);

CREATE INDEX code_remote_id_id_idx ON code_mappings (code_remote_id, id);

-- If you don't already have a primary key OR index on `id` column in the `mappings` table, then create one:

ALTER TABLE mappings ADD PRIMARY KEY (id);
-- Choose primary key, or index: CREATE INDEX id_idx ON mappings (id);


Insert into table where boolean value in another table is true

One possible solution ...

Setup:

create table sensors(pk int, enabled bit);

insert into sensors values (10,1);

create table sensordata(sensorkey int, sensorvalue float);

Insert if enabled is true:

insert into sensordata (sensorkey,sensorvalue)
select 10,25.4 from dual
where exists(select 1 from sensors where pk = 10 and enabled);

NOTE: from dual required as a dummy table for select to function properly.

Results:

select * from sensors;

 pk      enabled
 ------- -------
      10 true

select * from sensordata;

 sensorkey sensorvalue
 --------- -----------
        10        25.4

sqlfiddle


If it's the first UPDATE that times-out then I'd look closer at your physical reads.

If [staffid] isn't indexed then you'll be performing a full table scan and pulling the entire table from the IO subsystem through to the buffer.

After the first UPDATE most of the table's pages will be in the buffer and subsequent UPDATES will be able to perform logical reads for the EXISTS part of the SP.


It doesn't really matter what naming convention you use, however it is really important to have one and stick to it. As long as you're strict about using it, you'll have a clean/readable database.

A couple of useful tips:

Using tbl_xx or v_ for tables/views might be a bad idea, cause tomorrow, you might change the view to a table or visa versa. Which could end up being really confusing a couple of years from now.
Try to give meaningful names to columns, for example an email field should not be named user_e, but user_email, or just email.
Correct use of underscores or capitalization, bigfieldbuthardtoread vs BigFieldButEasyToRead vs Big_Field_But_Easy_To_Read
Don't prefix procedures with sp_, can cause some performance issues (it will first look in the master for that sp)

If you don't want to make up your own system, you can always try to find one online and make that your own, for example this one


I took the liberty of assuming that you want the XML to look like this:

<row>
...
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I think this is the solution you're looking for (see my comments for where I've changed your code).

SELECT Item.foldername AS "foldername",
Item.status,
Item.vhrid,
Item.firstname,
Item.middleinitial,
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id AS [practice/ID],   --- here..
           id.name AS [practice/Name] --- here..
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices
    on aprac.PracticeGroupID = practices.ID
    where Item.vhrid = aprac.vhrid
    FOR XML path('/practices'), type  --- and here.
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

For obvious reasons, I haven't tried it myself, please let me know if it doesn't work.


Relational Algebra Queries - How to Approach Problems?

I think you're on the right track, but I'm not seeing a provision for restricting the results to employees who are actually working on two projects and the rename notation is confusing to me.  You'll probably want to leverage a theta-join on a self join to accomplish this.

To start out, I would rename the relevant attributes in the work table in preparation for a self-join:

  ρsin/emp_sin,project_number1/project_number works_on

Then project just those attributes for simplicity's sake:

  πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on )

For visualization purposes, the equivalent SQL here would be something along the lines of:

SELECT  emp_sin
            AS sin,
        project_number
            AS project_number1
FROM    works_on;

I'd then prepare for the self join by creating another relationship, renaming the project_number differently:

  πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on )

At this point, a theta-join can be applied, exploiting the maximum of 2 carnality as defined in the assumption.  This theta-join creates a relationship with both project_numbers by emp_sin:

  ( πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on ) ) ⋈sin, project_number1 < project_number2 ( πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on ) )

In effect, this is similar to SQL as follows:

SELECT  *
FROM (  SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number1
        FROM    works_on ) AS w1
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number2
        FROM    works_on ) AS w2
    ON  w1.sin = w2.sin
    AND w1.project_number1 < w2.project_number2;

The little exploit in the requirements here lets us do a natural join and a final projection to finish up ( since I've already renamed the emp_sin attribute to sin ):

  πemp_name, project_number1, project_number2 ( employee ⋈ ( πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on ) ) ⋈sin, project_number1 < project_number2 ( πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on ) ) )

Again, as visual proof, this is approximately the equivalent of the following SQL, shown in action in this SQL Fiddle:

SELECT  e.emp_name, w1.project_number1, w2.project_number2
FROM    dbo.employee e
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number1
        FROM    dbo.works_on ) AS w1
    ON  e.sin = w1.sin
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number2
        FROM    dbo.works_on ) AS w2
    ON  w1.sin = w2.sin
    AND w1.project_number1 < w2.project_number2;


If you're using MySQL with InnoDB tables, then you could take incremental backups with Percona's xtrabackup, or talk to Oracle about enterprise licensing, which would net you MySQL Enterprise Backup.

Xtrabackup is a brilliant piece of software, but it behaves very differently from traditional MySQL backups using mysqldump, and it's worth spending some time studying and testing it before you look at rolling it into production.

If you're using MySQL with MyISAM, you're pretty much out of luck. You could perhaps do something logically, like maintain "last modified" timestamps on your tables that are maintained by triggers, but there's manual maintenance and several caveats that might make that unwise.

Recent versions of Xtrabackup should also work with MariaDB, though again you'll probably want to test this yourself.


I'd consider many other options before I played with these

Some things to check here on SO: https://stackoverflow.com/a/2009693/27535

Additionally...

indexes: are your writes scanning not seeking?
key: do you have GUIDs as clustered indexes (bad idea!)
triggers: no emails being sent on INSERT or CURSORs?
disk layout: separate fast RAIDs for data and logs, per database?
autoshrink/autoclose + associated file growth
unmaintained statistics and indexes
..

Basically, your problem will be poor design poor indexing or poor hardware...


View column has an 8000 character limit?

I suspect you are simply not checking the length of the column accurately (like inspecting PRINT or SELECT output in Management Studio, which is truncated by the tool, not accurately reflecting what's actually in the database). There is no limitation on the number of characters that can be exposed or updated by a view, unless you introduce them, perhaps you are using an INSTEAD OF trigger, or there is implicit conversion going on somewhere, or your data is being truncated before the update. Hard to tell without more details, which I hope you can furnish.

In the meantime, try this:

USE tempdb;
GO

CREATE TABLE dbo.foobar(x VARCHAR(MAX));
GO

CREATE VIEW dbo.v_foobar
WITH SCHEMABINDING
AS
  SELECT x FROM dbo.foobar;
GO

INSERT dbo.v_foobar(x) VALUES('c');
GO

UPDATE dbo.v_foobar
  SET x = REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);
GO

SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;
GO

Results:

-----    -----
16000    16000

I'm also not convinced that CHAR(34) is the culprit.

UPDATE dbo.v_foobar
  SET x = CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x' + CHAR(34)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);

GO
SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;

Results:

-----    -----
17226    17226

I suspect that something else is happening to this string as it's being generated or before the command is passed to SQL Server.


The code should read:

create or replace trigger complaint_advertise
BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
DECLARE
BEGIN
    UPDATE ADVERTISEMENT SET AD_APPEAR='INVALID' WHERE AD_ID=:NEW.AD_ID;
END;
/


Error when trying to deploy SSIS package

I experienced this problem, and was able to resolve it by disabling a DDL trigger on the SSISDB database for DDL_DATABASE_LEVEL_EVENTS.  The trigger was trying to write info into another Database.


How to prevent scheduler jobs from running at instance startup

Set the job_queue_processes parameter to 0 before opening the database.


MongoDB replica set quorum config (network partitioning behaviour)

I want to set a quorum rule to 3. It`s a split-brain protection measure. It means that if no 3 alive nodes in the cluster there is no able to write, only to read.

Split brain usually refers to a scenario where you have inconsistent data on both sides of a partition, for example with different writes accepted. The quorum requirement in MongoDB replication is designed to avoid that: a primary can only be elected (or sustained) in a partition that has a majority of configured voting members available. Any minority partitions will be increasingly stale, but will still have consistent history with a possibility of resuming sync when the network partition is resolved.

  I also want to configure such primary node behaviour: if any secondary nodes are not available in the cluster - primary change status to secondary till all cluster members will be available again.

This is an atypical configuration since it does not allow for any fault tolerance for writes (which is one of the key benefits of replica sets). A recommended approach would be configure all of your nodes as voting, use w:majority to require majority writes, and use read concern majority to ensure documents read are guaranteed not to roll back. The maxStalenessSeconds read preference can also be useful if you are concerned about reading from a secondary that has fallen too far behind.

However, if you want to ensure writes only succeed with all members available, you could consider one of the following approaches:

Use a w:3 write concern to require acknowledgement from all 3 members of your replica set.
Configure your replica set with 2 replica set members (instead of 3) which are both voting. This will require both members online in order to elect or sustain a primary.


Am I likely to experience thread starvation?  Assume a low cpu count.

Thread starvation doesn't actually have to do with the physical CPU count.  If you have lots of worker threads, and a low CPU count, then it will just be slower to schedule all of those threads on the limited number of CPUs.  See Erik Darling's blog post Max Worker Threads: Don’t Touch That:

The thing is, all changing that setting does is help you not run out of worker threads. It doesn’t make queries run any better or faster. In fact, under load, performance will most likely be downgraded to Absolute Rubbish© either way.
What’s worse? Running out of worker threads and queries having to wait for them to free up, or having way more queries trying to run on the same CPUs? Six of one, half dozen of punching yourself squarely in the nose.

This becomes even more complex in your scenario with multiple instances on the same box ("instance stacking").  Within a single instance, SQL Server uses cooperative scheduling to (try to) make sure that queries share CPU resources in an optimal fashion.
Unfortunately, one instance doesn't know anything about what the other instances are doing.  So each instance will compete for processor time "un-cooperatively" (generally called preemptive scheduling) due to queries running on the other instances.
Is this likely to be a problem for you?  It really depends on how busy the servers are.  But hopefully this answer helps to clarify the situation with regards to worker threads.


Can we downgrade from 19c to 12.1.0 if the compatibility is set to 12.2.0

Check for Incompatibilities When Downgrading Oracle Database

If the compatibility level of your Oracle Database 12c database is set
to 12.2.0, then you are not able to downgrade.

You can downgrade to 12.2, but not to 12.1.


Why isn't to_char IMMUTABLE, and how can I work around it?

The formats accepted by to_char(timestamp, text) include localized patterns that make it not immutable.

Example of different results with the same input:

test=> BEGIN;
test=> set lc_time='en_US.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char
---------
 Monday

test=> set lc_time TO 'fr_FR.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char
---------
 Lundi

test=> END;

If not using this kind of format, the solution is to create your own immutable wrapper function,

CREATE FUNCTION custom_to_char(timestamp) RETURNS text AS
$$ select to_char($1, 'YYYY-MM-DD HH24:00'); $$
LANGUAGE sql immutable;

and then create the index on that function.


Remind customers of an event 7 days before the event based on a date

A database is a place to store data.  An application is where that data is manipulated.

MySQL cannot "send email" or otherwise reach outside of itself.

So...  I would have a daily 'cron' job in the OS open the "events" table, read all the rows, compute who needs a reminder, send out the emails, and remove any expired rows from the table.

If you have only thousands of entries in the table, this is quite manageable; if you have millions, then there needs to be some way for the SELECT to filter out most of the table, thereby lightening the load on the app.

To accommodate February, you might want to say 'last' instead of '30th'.  That can be computed as "go forward 1 month, then back up 1 day".  Or, in MySQL, date + INTERVAL 1 MONTH - INTERVAL 1 DAY.

Your definition of Event Reminders table is confusing -- is it a list of future reminders to issue?  Or a list of past reminders sent out.  Pick one.  You seem to need to remember that you have already sent out a reminder; so I prefer that.  Pre-computing the 'future' is not worth the effort (as I already pointed out).


Does MySQL 5.6 support fan-in replication?

At present, MySQL does not support a native Multiple Master, Single Slave topology.

The phrase "Arbitrary Topology" is exact what it says. It is a topology of your making.

If you really want to create a Multiple Master, Single Slave topology, you came to the right place.

You will have to construct the topology based on the book

Pages 373-375 has two diagrams of three DB Servers (M1, M2, S1)

DIAGRAM #1

M1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 all tables use BLACKHOLE Storage Engine

M2 has the following characteristics

Slave of M1
Database db1 all tables use BLACKHOLE Storage Engine
Database db2 has real data

S1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 has real data

DIAGRAM #2

M1 has the Database db1
M2 has the following characteristics

Slave of M1
Database db1 all tables use BLACKHOLE Storage Engine
Database db2 has real data

S1 has the following characteristics

Slave of M2
Database db1 has real data
Database db2 has real data

WARNING : As long as you do not run ALTER TABLE statements against a database that is made up of all BLACKHOLE tables, this approach is the closest you can get to Single Slave/Multiple Master Replication. You will have to live with possible latency issues.

Give it a Try !!!

I have written about such topologies before

Apr 18, 2013 : Single slave - multiple master MySQL replication
Feb 03, 2012 : One slave, multiple masters MySql
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?
Mar 11, 2011 : MySQL in star topology


Does a read-only MySQL slave need log-bin in addition to relay-bin logs?

You are right. Traditionally, replication slaves need binary logs enabled unless you plan to failover to it someday.

The only reasons for using binary logs on a slave would be

Setting you circular replication (Makes a Slave a Master at the same time)
You want to promote the Slave to a Master
Using it as a Distribution Master (All Binary Logs,No Data, See my old post Is it Possible to Recover Data from a BLACKHOLE table? that describes why one would use such a setup)

As for your actual situation, here are two suggestions (pick one)

SUGGESTION #1

First, login to mysql as root@localhost on that slave and run

mysql> STOP SLAVE;
mysql> RESET MASTER;

Next, comment out this line from my.cnf

#log_bin                         = /var/log/mysql/mysql-bin.log
#log_slave_updates               = 1

Next, restart mysql

service mysql restart

Finally, manually delete those logs

cd /var/log/mysql
rm -f mysql-bin.*

SUGGESTION #2

If you do not want to restart mysql, do this

First, login to mysql as `root@localhost on that slave and run

mysql> SET GLOBAL expire_logs_days = 1;
mysql> RESET MASTER;

Then, add this line to my.cnf

expire_logs_days = 1

That way, the slave would still pile up binary logs, but would auto delete binary logs older than 24 hours.

GIVE IT A TRY !!!


export SQL statements from Oracle 11g database via command line

Depends what you mean by 'from the command line', but you can generate the DDL via the dbms_metadata package (see here for example).

If you want the data as well then you'd then have to roll your own, unless you find a generic script somewhere on the web. Automating the script with dynamic SQL and the data disctionary is certainly possible - it's what SQL*Developer must do ultimtately - it will just need some work to get right, and to handle things like embedded quotes. No quick fix I'm afraid.


Why can't we access SQL Server when a long query is running in Management Studio?

It would be impossible to say for sure without examining your web application and the SSMS query.  My guess, however, is that the long query is locking tables which the web page wants to read, so the page has to wait for the query to finish.  The page's timeout occurs before this happens and the page fails to load.

The concepts you are dealing with are locking and concurrency.

There are several ways to get the number of rows in a table.  Some are more accurate than others; some are faster.  This post on Stack Overflow may give you some ideas to work with.

Response to comment: a select count(*).. query will read every page of the table.  Since the SSMS statements are performing updates they will have exclusive locks on some pages and prevent the count(*) from proceeding.  This is exactly what I guessed was happening.

Some options are:

Read the post from Stack Overflow and decide if any of those options would be appropriate for you.  There are ways of getting the row count without touching the data tables.
Batch the updates so you only update, say, 1000 rows before you commit the transaction.  The commit will release locks and allow the counts to proceed.
Add WITH (NOLOCK) to the counting queries, described here.
Explicitly declare an appropriate isolation level for the web page's  queries.
Change the default isolation level for the SQL Server instance.  NOTE WELL - this will have MAJOR implications for ALL activity on the instance and should only be done after CAREFUL, DETAILED discussion with an EXPERIENCED SQL Server DBA.


Is there a way to query into the table and grab only those that have
  unique emails, while ignoring those that share emails?

I can think of a couple ways to do this:

1) If I restate this, I might say "Get all of the email addresses that have only 1 entry in the table, and exclude email addresses having more than one entry in the table.
Use GROUP BY...HAVING to identify the non-duplicated email addresses, grouping by the address, and literally looking for a COUNT of 1 (or > 1):

--UNIQUE EMAILS
SELECT EmailAddress
FROM TableName
GROUP BY EmailAddress
HAVING COUNT(*) = 1;

---NON-UNIQUE EMAILS
SELECT EmailAddress
FROM TableName
GROUP BY EmailAddress
HAVING COUNT(*) > 1;

This would literally give you the list of email addresses, which you would then need to do stuff with.

2) If I restate your question another way, "Get all of the emails where there doesn't exist another row with the same email address. Use 'EXISTS' and 'NOT EXISTS' and a correlated subquery to identify rows with or without duplicates.

--UNIQUE EMAILS
SELECT t1.EmailId, t1.EmailAddress
FROM TableName t1
WHERE NOT EXISTS (SELECT 1 FROM TableName t2
                 WHERE t1.EMailId <> t2.EmailId AND t1.EmailAddress = t2.EmailAddress);

--NON-UNIQUE EMAILS
SELECT t1.EmailId, t1.EmailAddress
FROM TableName t1
WHERE EXISTS (SELECT 1 FROM TableName t2
                 WHERE t1.EMailId <> t2.EmailId AND t1.EmailAddress = t2.EmailAddress);

Personally, I'd go with the second approach, as I think the logic is easier to follow, and I suspect it would perform better (though, that's just a guess).


Use value() instead.

The value() function returns a scalar value and takes two parameters. The first is the xpath expression and the second is the data type you want.

@ExpensiveXML.value('(/EventFileTarget/File/@name)[1]', 'nvarchar(260)');


Try this ...

  create table taba ( cli_id  number, fb_flag varchar2(10) );
  insert into taba values ( 1, null );
  insert into taba values ( 2, null );
  insert into taba values ( 3, null );
  insert into taba values ( 4, null );

  create table tab_freebook ( cli_id  number, free_book_b varchar2(10) );
  insert into tab_freebook values ( 23, 'true' );
  insert into tab_freebook values ( 2, 'true' );
  insert into tab_freebook values ( 3, 'true' );
  insert into tab_freebook values ( 6, 'true' );

  commit;

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1
           2
           3
           4

  select * from tab_freebook;

      CLI_ID FREE_BOOK_
  ---------- ----------
           2 true
           3 true
           6 true
          23 true

  select a.cli_id, a.fb_flag  old_val,
         nvl(tf.free_book_b, 'FALSE') new_val
    from taba  a
        LEFT OUTER JOIN tab_freebook tf
           ON a.cli_id = tf.cli_id
  /

      CLI_ID OLD_VAL    NEW_VAL
  ---------- ---------- ----------
           2            true
           3            true
           4            FALSE
           1            FALSE

  SQL>

  MERGE INTO taba  base
     using (
              select a.cli_id, a.fb_flag  old_val,
               nvl(tf.free_book_b, 'FALSE') new_val
          from taba  a
              LEFT OUTER JOIN tab_freebook tf
                 ON a.cli_id = tf.cli_id
           )  new
     ON ( new.cli_id = base.cli_id )
     WHEN MATCHED THEN UPDATE
           SET base.fb_flag = new.new_val
  /

  4 rows merged.

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1 FALSE
           2 true
           3 true
           4 FALSE

  SQL>

[edit] fixed typo - somehow my initial test missed your test record "23" .. fixed, same result ;) [/edit]

Basically, start by writing a SELECT query ONLY that returns the end result set you want.
Then use that in your USING clause .. and viola ... "magic" ;)

although that said, you could probably avoid the MERGE altogether and just use an UPDATE  ;)  (that said, I actually use MERGE now more often than a straight UPDATE when the logic starts to get a little complex ... that is, for simple UPDATES, I use UPDATE, for complex UPDATEs, I use MERGE ;)  I just find the syntax easier ... shrug )


Since you mention hundreds of columns I would consider an EAV design.  While Joe Celko warns against this, I think it may be applicable in your use case.  It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string.  It will work even better if all the amounts are whole numbers, but can work also if some are decimal.  Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns:  Conventions of Thought.  This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that.  One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure.  Here is an example:

UOM Conversion

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2.  There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps.  So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32.  The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth.  I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.


Find the Foreign Keys Associated with a Given Primary Key

Here's a simple query to match up foreign keys to their referenced tables/columns:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).

(Edited to add some more output columns.)

EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.

Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.

SELECT
    fk.is_disabled,
    fk.is_not_trusted,
    OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
    o1.name AS FK_table,
    --Generate list of columns in referring side of foreign key
    STUFF(
        (
            SELECT ', ' + c1.name AS [text()]
            FROM sys.columns c1 INNER
                JOIN sys.foreign_key_columns fkc
                    ON c1.object_id = fkc.parent_object_id
                    AND c1.column_id = fkc.parent_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS FK_columns,
    --Look for any indexes that will fully satisfy the foreign key columns
    STUFF(
        (
            SELECT ', ' + i.name AS [text()]
            FROM sys.indexes i
            WHERE i.object_id = o1.object_id
                AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                    SELECT fkc.constraint_column_id, fkc.parent_column_id
                    FROM sys.foreign_key_columns fkc
                    WHERE fkc.constraint_object_id = fk.object_id
                    EXCEPT
                    SELECT ic.key_ordinal, ic.column_id
                    FROM sys.index_columns ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                )
            FOR XML PATH('')
        ), 1, 2, '') AS FK_indexes,
    fk.name AS FK_name,
    OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
    o2.name AS PK_table,
    --Generate list of columns in referenced (i.e. PK) side of foreign key
    STUFF(
        (
            SELECT ', ' + c2.name AS [text()]
            FROM sys.columns c2
                INNER JOIN sys.foreign_key_columns fkc
                    ON c2.object_id = fkc.referenced_object_id
                    AND c2.column_id = fkc.referenced_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS PK_columns,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name


When you DECLARE and SELECT @role= at the beginning of your code, you assume that the trigger will only be applied to single-row inserts. Effectively, the value of @role is not guaranteed, as it is assigned multiple times, and you cannot control the order.

To fix this, you could instead join the users table into your update:

UPDATE a
SET a.updated_date=i.created_date

FROM TableA AS a
INNER JOIN inserted AS i ON a.id=i.trans_id
INNER JOIN users AS u ON u.id=i.creator_id

WHERE u.[role]='client' OR
      u.[role]='tech' AND i.public='true';

In this example, the update is performed if a) the role is "client", or b) the role is "tech" and public is "true".


what is the correct name for "rich" views?

Materialized view.

Overview of Materialized Views

Materialized Views


How to get super parent from table with self relationship

Something like this

/*
create table #table1 (id int, parentId int)

INSERT INTO #table1
VALUES (1,null),
(2,1)
,(3,2)
,(4,3)
,(5,4)
,(10, null)
,(11,10)
,(8,11)
*/
declare @child int;
set @child=4;
with prt as
(select 1 rn,id, parentid from #table1 where id=@child
union all
select rn+1 rn, t.id,t.parentid from #table1 t join prt on prt.parentid=t.id
where t.parentid is not null
)
select top 1 parentid from prt order by rn desc

Recursive CTE


What are the disadvantages if indexes are added on all columns?

Insert/Updates/Delete speed will degrade. The index has to be updated on data modification.
I don't know mysql in fine details but it also means more time spent by the query planner to determine the indexes to use.
More disk space.

These are the key disadvantages.


mysql - 35GB binlog files are cached in OS cache

Yes its expected behaviour. MySQL doesn't control what goes in the buff/cache, that's purely down to the OS. It gets reclaimed quite quickly on any new file access or process memory requirement.

Linux can't really tell its not going to be used again soon, but its not doing anything else with the memory so it just uses it (or more correctly, doesn't immediately purge it).


Yes it is a good idea. Temp tables will benefit from indexes in the same way that permanent tables will. Index creation is the same, too, with a few minor exclusions.

The indexes function in the same way as they do on permanent tables, with the exception that temp tables are cached, which can hurt if the workloads are skewed significantly.

The same caveats apply too. Just because an index exists does not mean the optimizer will use it. Writes will be costlier with many indexes in place. Stale statistics can produce poor plans.

Recently I was able to reduce a large batch procedure's run time from over 20 minutes to under three by adding indexes to temp tables. This is a worthwhile task.

See also Indexing Temp Tables by Erik Darling.


I finally found a solution. The problem might be that I was setting utf8mb4 just for a database, not for the mysqld daemon.

Anyway I put here what I made. I first created a Dockerfile:

FROM mysql:latest

RUN apt-get update && apt-get install -y locales && rm -rf /var/lib/apt/lists/* \
  && localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8
ENV LANG en_US.utf8

COPY conf.d/ /etc/mysql/conf.d/

ENV MYSQL_ROOT_PASSWORD pass
ENV MYSQL_DATABASE gastos

As you can see, I copy a conf.d directory I just made. Inside this directory there is a character_set.cnf file:

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci


That one example needs

INDEX(c, f, h,  -- these can be in any order
      a,        -- this must be after the first 3
      ... )     -- others can follow

Your suggestions with (c, d, ... are not very useful.

If you are expecting to have lots of different queries, I recommend you

Study what is actually used by users.  You will find that there are a few common columns or pairs of columns, and some columns that are rarely used.
Then add a few indexes, no more than, say, a dozen.
Generally don't make indexes with more than 3 columns.
Have the indexes scrambled around.  The two you suggested may as well be replaced by simply (c,d,e,f,g,h,i) since this will be quite useful for both.
Study this for how to generate the optimal index for a given select.


Unique key based on many-to-many pivot table

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key.

There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns:

ALTER TABLE album_artist
ADD CONSTRAINT uq_album_artist
  UNIQUE (album_id, artist_id);

There are more options to adding a UNIQUE constraint, which can be found in the linked manual.

The second variation is to get rid of the id column and declare (album_id, artist_id) to be the primary key of the table, as Rick James has suggested in a comment:

ALTER TABLE album_artist
DROP PRIMARY KEY;  /* assuming id has actually been declared
                      as the PK; if not, omit this step */

ALTER TABLE album_artist
DROP id;

ALTER TABLE album_artist
ADD CONSTRAINT pk_album_artist
  PRIMARY KEY (album_id, artist_id);

A junction table like your album_artist table, which stores no additional information beyond the table references, usually does not need to be referenced by other tables – at least, not often enough to justify the need for a dedicated ID column. The second variation, therefore, may be more appropriate. Rick's other useful thoughts on implementation of a junction table can be found in his blog.

If another table needs to reference an album and an artist as a valid combination (i.e. one that exists in the junction table), it can simply use a composite foreign key to reference album_artist:

FOREIGN KEY (album_id, artist_id) REFERENCES album_artist (album_id, artist_id)

It is only when you need to reference the junction table often (that is, from many tables) that I would argue the first approach, which allows you to keep a dedicated PK column in the junction table, would be more useful.


Bad execution plan after stats update due to temp table

The bad plan has a warning on the temp table that the join column has no stats. What gives?

There may be a more esoteric reason for this, but it is more likely a simple statistics creation failure. This might, for example, occur when the task fails to get the memory resources it needs, or when statistics creation is being throttled (too many concurrent compilations). See the Microsoft White Paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008. You may be able to debug this further by looking at the auto stats Profiler or Extended Events and other events around the same time.

That said, a great deal more information and investigation would be needed to place the blame for the plan selection at the door of the missing temporary table statistics. Even without detailed statistics, the optimizer can still see the total cardinality of the temporary table, and this would seem to be an important factor here.

  ...but can be recompiled to the good plan right after. Same compiled parameters.

The @Months parameter might be the same, but the number of rows in the temporary table (from the unknown view View_Feeder) is different, and the plans provided do not show the value of @MyId.

Going from the information available: The 'good' plan (estimates only, no performance data presented) is based on a temporary table containing 4 rows. The 'bad plan' is based on a temporary table with 114 rows. Certainly a lack of density and histogram information may not be helpful, but it is easy to see how the optimizer might choose a different plan for 4 vs. 114 rows, albeit ones with unknown density and distribution.

If estimates on plan operators not dependent on the temporary table are wildly off, this is a strong signal that the current main-table statistics are not representative of the underlying data. The lack of information in the question makes this impossible to assess.

Nevertheless, it is possible to see that the optimizer is being asked to choose between sub-optimal alternatives here. Neither plan presented represents an 'obviously good' choice, since both involve lookups (lack of a 'covering' index) and late filtering (see next). Lookups in particular have a high cost associated with them, which depends sensitively on cardinality estimates.

Using a view restricts the optimizer and hinting choices:

The view contains a GROUP BY that prevents the predicate vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE()) from being pushed down, even though the transformation would be valid in this very specific case.

In-lining the view to the query would provide a natural way to filter the date/time column earlier (though the question does not state whether refactoring the query is an option).

It is not possible to hint an index on a view, and FORCESEEK simply asks the optimizer to find any index-seeking plan (not necessarily using the index you would prefer). Removing the view would likewise remove this restriction.

Allowing the predicate to push down should open up indexing opportunities on the large table as well. For example:

CREATE INDEX give_me_a_good_name
ON dbo.LargeTable (Col1, [Status], Col4)
INCLUDE (P_Id);

...provides a good access path for the rewritten query:

DECLARE @Date datetime = DATEADD(MONTH, @Months * -1, GETDATE());

SELECT
    MT.Col1,
    ST.Col2,
    MAX(LT.Col4)
FROM #MyTemp AS MT
JOIN dbo.LargeTable AS LT
    ON LT.Col1 = MT.Col1
JOIN dbo.SmallTable AS ST
    ON ST.P_id = LT.P_Id
WHERE
    LT.[Status] IN (3, 4)
    AND LT.Col4 > @Date
GROUP BY
    MT.Col1,
    ST.Col2
OPTION (RECOMPILE);

Another consideration is the effect of temporary table and statistics caching as described in my articles Temporary Tables in Stored Procedures and Temporary Table Caching Explained. If a good plan depends on the current content of the temporary object, an explicit UPDATE STATISTICS #MyTemp; before the main query, and adding OPTION (RECOMPILE) to the main query may be a good solution.

Alternatively, if one particular plan shape is always optimal for this query, you have many options available, including a variety of hints, plan guides, and query store plan forcing. You might find that using a table variable instead of a temporary table is the better choice, since it favours the low cardinality case, and does not provide (or rely on) statistics.

In summary, there are several general improvements that should be undertaken before worrying about the reasons for (of effect of) occasional missing statistics on the temporary table:

Ensure statistics are representative and useful to the optimizer
Check actuals vs. estimates for a range of parameter values
Provide good data access path(s) for the query by improving existing indexes
Remove the view if possible; or consider a 'parameterized view' (inline table-valued function) with an explicit predicate for the date/time parameter.
Ensure that automatic statistics creation is not being throttled unnecessarily
Use the right sort of temporary object for the task (table vs. variable)
Consider RECOMPILE if plan choice is very sensitive to parameter values
Add UPDATE STATISTICS and RECOMPILE if cached statistics are a problem
Consider a temporary table with a primary key instead of SELECT INTO if that provides useful information to the optimizer
Review the schema to ensure the optimizer has the most information possible (e.g. foreign keys, other constraints)
Consider the suitability of filtered indexes/statistics based on your knowledge of the data
Don't sprinkle NOLOCK hints to increase performance

Repro

The following was built from the limited information available in the redacted execution plans provided:

DROP VIEW IF EXISTS dbo.View_VP;
DROP TABLE IF EXISTS dbo.SmallTable, dbo.LargeTable, #MyTemp;
GO
CREATE TABLE LargeTable (P_Id integer NOT NULL, Status integer NOT NULL, Col1 integer NOT NULL, Col4 datetime NOT NULL);
CREATE TABLE SmallTable (P_id integer NOT NULL, Col2 integer NOT NULL)
CREATE TABLE #MyTemp (Col1 integer NOT NULL);
GO
CREATE VIEW dbo.View_VP
AS
    SELECT
        pp.Col1,
        pd.Col2 AS Col2,
        MAX(pp.Col4) AS Col3
    FROM LargeTable pp
    JOIN SmallTable pd
        ON pd.P_id = pp.P_Id
    WHERE
        pp.[Status] IN (3, 4)
    GROUP BY
        pp.Col1, pd.Col2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_SmallTable ON dbo.SmallTable (P_id)
CREATE CLUSTERED INDEX ix_P_id ON dbo.LargeTable (P_Id)
CREATE INDEX ix_Col1 ON dbo.LargeTable (Col1)
CREATE INDEX ix_Status ON dbo.LargeTable ([Status])
GO
UPDATE STATISTICS dbo.LargeTable WITH ROWCOUNT = 32268200, PAGECOUNT = 322682;
UPDATE STATISTICS dbo.SmallTable WITH ROWCOUNT = 6349, PAGECOUNT = 63;
UPDATE STATISTICS #MyTemp WITH ROWCOUNT = 4;

The query is:

DECLARE @Months integer = 6;

SELECT wd.Col1
         , vp.Col2
         , vp.Col3
    FROM dbo.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

Without real statistics on the base tables, this favours plans close to the 'bad plan' example (using ix_Status):

This suggests that information about the selectivity of Col1 is an important factor in the optimizer's choice.


Delete rows from a table with inner join conditions

If your aim is only delete rows from table1, you can re-write your query as follow:

DELETE FROM table1  p
WHERE EXISTS(
    SELECT 'MYROW'
    FROM table2  e
    JOIN table3  d
        ON  d.col4 = 'IO'
        AND d.col5 = -1
        AND e.col3 = d.col6
    WHERE e.col1 = 'YU'
    AND e.username = p.username
    AND p.col2 = d.col3
);

You convert your INNER JOIN between main table (table1) and the others with using of WHERE EXISTS condition.

Note: string literals (like 'IO') need to be quoted with single quotes and not double quotes.


How mature is the Firebird database when compared with PostgreSQL and MySQL?

FirebirdSQL is quite mature and reliable.

Its biggest weakness however (in my opinion) is the lack of a proper manual that covers all features of the current release. Currently if you want to know if a specific feature is available you first need to go through the Interbase manuals, than through the Firebird 1.0 release notes than the Firebird 1.5 release notes, than the Firebird 2.0 release notes and finally the Firebird 2.5 release notes (and 3.0 is more or less on the doorstep)

Btw: using a different DBMS in development than in production is usually a very bad idea. Especially if the development DB - as in your case - offers a lot more SQL features than the production DBMS.


Adding a bool column; tracking untouched vs. false

In most DBMSs, or at least the ones I have dealt with, a Bool is really a trinary.  You have 1, 0 and NULL.  NULL is specifically there for "I haven't entered any data yet."  Depending on how you code it your checkbox should default to unchecked for 0 or NULL.  Then 1 for checked of course.  However in your reporting you can actually differentiate between rows where someone has looked at it and entered a value (1 or 0) and rows where no one has updated it yet (rows where the value is NULL).


I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed:

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT 1
    FROM eventlog.rules r
    where l1.message like r.content
      and r.type = 'DROP'
)
and criticality in ('High', 'Medium')
and record_date > sysdate() - Interval 2 Day
order by l1.message;

I also replaced the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN.


SQL Server falling below 5000 free pages

So what can happen that the free lists shrinks? For most purposes you can consider the formula free = total - (max(database, reserved)+stolen). So simply collect all the counters in the SQL Server, Buffer Manager Object and then see what the pattern is:

total stay the same, but stolen has grown. This indicate some memory consumption from code and caches. and usually does not heal itslef. You would investigate this by checking the memory clerks, see How to use the DBCC MEMORYSTATUS command to monitor memory usage. A possible explanation for a sudden spike in stolen that disapears quickly is a complex query compilation. Collect SQL Server, Memory Manager Object for additional info, look at Optimizer Memory.
database, reserved and stolen stay the same, but total shrinks. Lets just consider this as it doesn't happen. If you see this, post here and we can further digest.
total stays the same, database grows. Probably a scans on a large table. Look for a query with large logical_reads in sys.dm_exec_query_stats
total stays the same, reserved grows. Indicates a query that has a large memory grant request. Collect SQL Server, Memory Manager Object for additional info, look at Memory Grants Outstanding.


After Oracle 11gR2 restore & recover database, "ghost" NOLOGGING corrupt blocks

Presumably there was data in them at one point, which has since been deleted.
If the corrupted blocks are in free space, there is no need to take any action. Oracle will reformat the block when it attempts to use the space. So you can ignore the alerts.
If you want to clear the corrupt blocks to prevent more alerts, create a nologging table in the relevant tablespace. Insert a lot of rows until the corrupt blocks disappear, then drop the table.


Database Mail on Amazon RDS / SQL Server

AWS has posted a detailed blog post based on the way that UnderArmor solves this limitation. It involves a linked SQL Server that you run in EC2 and that EC2 instance is configured to SES to relay the messages through.

AWS RDS Database Mail