Moving `order by` Significantly Improves Execution Time

Comparing apples to oranges. The 2 queries and the results they return are not the same.

First query retrieves 999 rows from main_table + other_table, joins to rates_table and sorts the result. The result of the 3 table join can be 999 rows or 10 million rows as well, depending on the data. If it is 10 million, it has to sort 10 million rows.

Second query retrieves 999 rows from main_table + other_table and sorts them right after. Finally joins the rates_table, and does not sort the final result.

The first query sorts everything, the seconds query sorts only the result of the subquery. Sorting 10 million rows will most likely need more resources+time than sorting 999 rows. The cost of sorting is often overlooked.

The first query returns a sorted resultset, but for the second query, there is no guarantee of returning a sorted result.

The second query may return inconsistent results between different database versions because of how the optimizer works in different versions. For example if column a is indexed, the optimizer may choose to access the table using the index, and returns the first 999 rows sorted anyway, because data is already sorted in the index. Or it may choose to scan the table, get whatever 999 rows it can, then sort that. This can be easily tested with a hints such as: /*+ optimizer_features_enable('12.1.0.2')*/ and providing different versions.

The first query is guaranteed to return the same result on all versions, as long as the data is organized the same way. As there is no criteria specified for getting the first 999 rows (first, based on what?), after reorganizing the table (move, shrink, export/import, redefinition), it may also return different results for the same base data. In this aspect, both queries are inaccurate, unless you do not care about consistent results - which is a quite rare situation.


Does MariaDB support ST_Distance_Sphere?

Almost unbelievably, MariaDB lacks this ST_Distance_Sphere (MDEV-13467).

Find their support matrix here.

And it's not just that either, it also lacks ST_GeoHash that MySQL has.


Query To Show All Users Who Have Multiple Managers

You just need to use the GROUP BY and the HAVING clause to filter the Table down to only users with multiple managers like so:
SELECT userid
FROM Test
GROUP BY userid
HAVING COUNT(mngrid) > 1

Also I don't think @@Test is valid syntax (though I'm unable to test right now) so my example just uses a regular Table, but you can replace it with your actual Table name.


How can I find who has allocated a user lock via dbms_lock?

The type of locks you are referring to are stored in table (not view) SYS.DBMS_LOCK_ALLOCATED.  I believe they only have a "handle", but not a specific owner, i.e.: they all belong to SYS.  That said, if you have active blocking situation going on (someone is waiting for the lock and it's not getting released by the session who allocated the lock) then this query might show what's going on:

SELECT blocker.username blocker, blocker.sid blocker_session
     , blocked.username blocked, blocked.sid blocked_session
  FROM v$session blocked
  JOIN v$session blocker
  ON (blocked.blocking_session = blocker.sid)
 WHERE blocked.wait_class = 'Application'
   AND blocked.event='enq: UL - contention';


IN Clause causes Execution plan to change from Nested Loops to Hash Match

Well, you can force nested loops by using OPTION(LOOP JOIN) however, I would advise using OPTION(LOOP JOIN, MERGE JOIN) this is working with the optimiser rather than against it and is saying do what you want as long as its not a hash match.

But I would also dig a little deeper. With the IN clause it is possible that SQL is performing multiple index scans as opposed to a single table scan, so you may need to use an WITH(INDEX(0)) instead.

If you add SET STATISTICS IO at the top of the batch and run it with and then without the IN clause and check the number of physical and logical reads for each, as well as the read-ahead reads, this will prove or disprove my theory.


The simple answer is....

To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause.

The Oracle documentation spells it out clearly:

  Use the ORDER BY clause to order rows returned by the statement.
  Without an order_by_clause, no guarantee exists that the same query
  executed more than once will retrieve rows in the same order.


How to isolate client data on the same application using MySQL

In MySQL schema and database are synonymous: you use CREATE DATABASE or CREATE SCHEMA with equal effect. There's little physical isolation between schemas (databases), workload against one schema can potentially affect all schemas  on the same server. However, there is logical access isolation: you can allow certain user(s) access to a specific schema only using GRANT ... ON schema.* TO ....


We have so many user ID locks from the front-end users.

Good Problem Statement.
More common than you might think.

We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present.

No.  You don't.
You need to find out why the users are locking their accounts so often and resolve the Root Cause of that problem, not fiddle about trying to fix the Symptoms.

The sys password needs to be decrypted during runtime for script run

No.
Just ...   No.

... & encrypted after script executes successfully. (Ideally they should not see the password)

There should never be any possibility of Users seeing the SYS password.
EVER.
Remember: your job as DBA is to clean up the mess that other people make.  Always keep the biggest and best Tools for yourself.

We have multiple databases so the parameter has to be passed in the below manner only.
Example syntax : ./tmp/unlock_user.sh user instance_name

This may be part of your Problem.
If Users have different passwords in different databases, then they're going to get themselves muddled up and lock themselves out.
Do your users really have shell access to your database hosts?
Next you'll be telling me that they connect directly to the Database and run SQL ...
Moving forward ...
If there's an Application layer between the Users and your Database, then you should consider introducing an "Application" account, whose credentials are used exclusively by the Application and that users are never aware of.  All database connections will be made using these credentials and Users will not have their own accounts in your databases.  OK, this means that you'll have to re-implement account authentication, because the database won't be doing it for you, but that shouldn't be a huge job.
Using an Application account has other benefits.
If a User "locks themselves out" in this "shared-connection" model, the Application can still support them in resetting their password (or whatever), because the Application has its own credentials and connection to the Database.  If the User has their own credentials and connection, then they cannot get into the database to reset their password in the database, hence your current predicament.
If there is no Application layer between the Users and the Database (and I would seriously ask "why not"?), then you may have to look at something [a lot] more intrusive, like hooking Oracle into Kerberos or [indirectly] Active Directory, to provide you Users with a true, single signon experience.


Extract insertions that failed because of size limit from SQL Server Express Transaction Log

The size of the Transaction Log is just due to its normal growth pattern based on the amount of data that's been transacted between backups. It'll grow to whatever size it needs to hold transactions until a backup is taken on the log which flushes those hardened transactions BUT does not shrink the log file. Rather that space remains as reserved empty space that the Transaction Log will re-use for new transactions.
So there is no correlation between the log file's size and if your failed to insert data is in it. At best it may be in one of the previous backups of it, as a rolled back transaction, and in a non-human readable form. Unfortunately that failed data insert is likely gone, at least in the context of this question.


What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this?

A tablespace creates a layer of abstraction between physical and logical data

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower disk.


How to store user defined scoring formulas?

You can have a table of weights -- that is, an association of user with each question and how much that answer is weighted. Then you can give each user their own weight for each answer.

create table Weights(
    UserID     int not null references Users( ID ),
    AnswerID   int not null references Answers( ID ),
    Weight     int default 1, -- For Ux: Ax * Weight,
    constraint PK_Weights primary key( UserID, AnswerID )
);

I've shown the weight as an integer but you can change that if you want fractional values (A1 * 3.5 + A2 * -2.75 + A3 * 7 + A4 * 3.1415 = SCORE ).


Part of statement missing from sys.dm_exec_query_stats

It's difficult to say what might be going wrong with your script, since you didn't show us what you're running. But no matter -- you're probably better off using sp_WhoIsActive, and doing something like this:
EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_full_inner_text = 1,
    @get_outer_command = 1;


SQL Server Difference between CPU and CPU_TIME

Here's what I can tell you.

Internally, they use two different ways obtaining the data; sysprocesses uses the "legacy" method and sys.dm_exec_requests uses a newer method. Unless you have a very pedestrian workload these will generally not be the same.

Overall, I must agree with Aaron and not use sysprocesses as it is deprecated. Having said that, either method will get you "close enough" to troubleshoot issues and there shouldn't be such a difference that it hides or masks an issue from your troubleshooting efforts.

Note, this was tagged as SQL2014 and thus this answer should be considered relevant only for SQL Server 2014 specifically build 12.00.5538 (SP2 CU3).


Can partitioning help speed up inserts on a very large table?

Short answer: yes it can help, because it's theoretically instantaneous. You would insert your data into a staging table with the same definition as your main partitioned table, and then switch it into the partitioned table, which is a metadata operation (schema lock).

Long answer: it might make performance suffer over the long run to have such a large table, and unless you move to later versions of SQL Server, statistics updates are quite difficult to manage.

If this is a read-heavy table, I'd consider using view partitioning instead. Each month (for example) would get its own table, with check constraints on a date column to help the query optimizer know where the data is physically stored, and a view over the top of that:

SELECT col1, col2, col3, col4 FROM period201501
UNION ALL
SELECT col1, col2, col3, col4 FROM period201502
...
SELECT col1, col2, col3, col4 FROM period201608

(or whatever). Then your metadata operation is the update of the view, as opposed to the switching in of the partitioned table.


Select data from single table using Self join

Here's how i got it to work:

create table tableone (id int, value int)
insert into tableone values (123,     1);
insert into tableone values (123,     8);
insert into tableone values (234,     8);
insert into tableone values (456,     1);
insert into tableone values (876,     5);
insert into tableone values (876,     1);
insert into tableone values (765,     8);
insert into tableone values (765,     5);
Select a.*
from tableone a
left join tableone X on a.id = x.id and x.value = 8
Where a.value = 1
and X.id is null
drop table tableone


Setting innodb_buffer_pool_size in for Mysql

I think 80% is a little bit too much, I suggest 60%. Otherwise you'll risk swapping.

If you're data and indexes do not fit into ram (innodb_buffer_pool_size) you'll experience more disk reads. Which of course is slower than reading from RAM, especially when you have slow disks.

Monitoring disks (IOPS) can be done using iostat or pt-diskstats.


Amazingly broad question but a couple of simple answers.

Indexing like you suggested.  Make sure the appropriate indexes exist and if the need is great enough make sure they are all covering.
Get rid of the *.  Specify the columns you need.  If you are pulling across 100 columns over a million rows that's going to be a LOT of data.  If you only need 3 columns only specify 3 columns.
Change the timeout.  This sounds hokey but honestly sometimes it really is the answer.  SQL Server (for example) doesn't actually have a timeout so the problem is on the connection side.  Have them increase the timeout to 30 seconds (assuming this is an acceptable amount of time, and it frequently won't be).
Make sure it's the DBMS's fault.  It's very possible you are having a problem on the connection side that's causing it to take 5-10 seconds just to connect.  Fix that and you are well within your time.


Error 7399 when the Access file has a password

The problem was the file. I got a Windows XP virtual machine that has the MS Access 95. So I could open the file to delete the current password and change groups permissions. Now the linked server works.

It seems neither Microsoft.ACE.OLEDB.12.0 nor Microsoft.Jet.OLEDB.4.0 can deal with MS Access 95 security policy. But I really don't know why.


One table with many rows or many tables with a few rows

Assuming that you are talking about tables containing the same kinds of entities, you typically want to have one table.

You would not have any performance differences and a whole lot of management differences between the two approaches, with the single table being easier to manage.  Typically large tables do not have performance overheads compared to smaller versions of the same when properly indexed, since the index seek time grows slowly compared to the row growth.

In a normalized design, you will have different tables for different entities or relations, but as far as partitioning (either supported with a DBMS feature or manually with separate tables), that usually is necessary when you have certain requirements, like granularity of backup or data loading/unloading on a partition basis.


Compilation of T-SQL oddities and pitfalls

Trailing spaces are ignored by some functions and operators, such as LEN() and equality.  The first query returns 5, 5, 5, 5, 5, 5.  The second query returns EQUAL.  These results can be quite surprising to a new SQL programmer.

SELECT   LEN('Hello') AS [0 trailing spaces],
         LEN('Hello ') AS [1 trailing spaces],
         LEN('Hello  ') AS [2 trailing spaces],
         LEN('Hello   ') AS [3 trailing spaces],
         LEN('Hello    ') AS [4 trailing spaces],
         LEN('Hello          ') AS [10 trailing spaces]
;

SELECT   CASE
              WHEN 'Hello' = 'Hello    ' THEN 'EQUAL'
              ELSE 'NOT EQUAL'
         END
;

Application of negative and positive signs has lower operator precedence than multiplication and division.  This causes products preceded with negative signs to be evaluated first.  Thus, the first expression in the query below yields 9.0, whereas the second expression yields -1.0.

SELECT   6.0/2.0*3.0,
         6.0/-2.0*3.0
;

Casting a blank string to integer, bigint, or float results in zero, whereas casting a blank string to decimal or numeric produces an error.

SELECT   CAST('' AS INT) AS [INT],
         CAST('' AS BIGINT) AS [BIGINT],
         CAST('' AS FLOAT) AS [FLOAT]
;

SELECT   CAST('' AS DECIMAL(15,6)) AS [DECIMAL],
         CAST('' AS NUMERIC(15,6)) AS [NUMERIC]
;

SQL has one of the most strongly defined NULLs of any programming language.  It nearly constitutes a third logic value (either "missing" or "non-applicable") in a three-valued logic, rather than merely an indicator of "uninitialized variable".  A three-valued logic can produce surprising results to those who are more accustomed to two-valued logic.  The queries below will not necessarily return every row in the table.  Rows in which COL1 is NULL will not be returned.

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 = 5
         OR COL1 <> 5
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 = 5
         OR NOT(COL1 = 5)
;

The strongly-defined NULL mark and three-valued logic can cause bafflement in more subtle ways.  The first query below will not return any rows, even when COL1 contains a value not in the excluded list (e.g., 6 or 7 or 8).  The second query will return all rows, as expected, but the third query will never return any rows.

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 NOT IN (1, 2, 3, 4, 5, NULL)
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    1 = 1
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    NULL = NULL
;

You'd better be sure you understand scale and precision and how they interact.  Otherwise, the query below will make you recoil in horror.

SELECT   CAST(0.0000006 AS DECIMAL(38,22)) * CAST(1.000000 AS DECIMAL(38,22))
;

More of unary arithmetic operator irregularities. When applying multiple sign operators in a row, the standard notation prescribes that you use brackets: -(-(-2)). In Transact-SQL, however, you can omit the brackets:

SELECT  -1,  - -1,  0- - -1;

-- results: -1, 1, -1

Note that you have to put spaces between the - signs, otherwise a -- will be interpreted as a line comment symbol. However, you do not have to put spaces between multiple unary + signs:

SELECT  +1,  ++1,  0+++++++++++++++++++++1;

-- results: 1, 1, 1

Finally, you can also apply a unary + to a string! This will compile just fine:

SELECT
  +'This is a positive note',
  +++'Super+positive note',
  'I'++++++' love'+++++++++' plus(s)es'
;


Why does it take so long to view logs on a server?

Yes, reading logs takes a long time in the log file viewer. A few things to fix it:

Try using xp_readerrorlog with filtered parameters to get just the data you want:

@p1 is the log file you want to look at (0 is current, 1 is the first archive, 2 is second, etc)
@p2 is null for the error log, 2 for Agent
@p3 and @p4 are strings to search for in the output

That way you just get the rows you want.

If you find yourself doing this kind of thing frequently, run a job to cycle the error log periodically (I like weekly) so that you don't have to sift through so much stuff. Plus, make sure you're not logging successful backups or successful logins to the error log.


Use CASE to select columns in UPDATE query?

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

UPDATE some_table
SET    column_x = CASE WHEN should_update_x THEN new_value_for_x ELSE column_x END
     , column_y = CASE WHEN should_update_y THEN new_value_for_y ELSE column_y END
     , column_z = CASE WHEN should_update_z THEN new_value_for_z ELSE column_z END
FROM   ...

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.


First of all: DO NOT USE 'sa' account.

Create database roles for the application with appropriate permissions, reader, writer, executor etc, with only permissions the application requires.

Secondly, is there any reason this server is not on the Windows Domain? It is preferable to handle permissions via AD Security Groups and service accounts (and managed service accounts) rather than SQL accounts. The benefit is simplified management and increased security - Kerberos vs plain text passwords.

EDIT : as I have not answered your question. In your case the number of users is irreleveant as they transalate to number of requests and spawned workers/threads. You are asking database to serve 40 reports, no matter if they all come from 1 user, or 40 users asks for 1 report, the end result is still 40. In extreme situations it may be preferable to have less users to save resources and time on creating new connections as each takes tiny amount of memory but that is a whole different conversation.

Hope this helps.


Your understanding is correct though you have 1 too many digits for @dVal2 and @dVal4 which is why you see these being rounded up to 7 (9.58141981546547) for the last digit in your select

As for the division rounding, it's hidden in the middle of the docs.

  In multiplication and division operations we need precision - scale
  places to store the integral part of the result. The scale might be
  reduced using the following rules:

  The resulting scale is reduced to min(scale, 38 – (precision-scale))
  if the integral part is less than 32, because it cannot be greater
  than 38 – (precision-scale). Result might be rounded in this case.

  The
  scale will not be changed if it is less than 6 and if the integral
  part is greater than 32. In this case, overflow error might be raised
  if it cannot fit into decimal(38, scale)

  The scale will be set to 6 if
  it is greater than 6 and if the integral part is greater than 32. In
  this case, both integral part and scale would be reduced and resulting
  type is decimal(38,6). Result might be rounded to 6 decimal places or
  overflow error will be thrown if integral part cannot fit into 32
  digits.

So in your first case decimal(32,14), the scale is being set to 6 digits because the resulting value decimal(64,28) has a scale = 28 which is > 6 and the integral part (64-28) = 36 is > 32 as defined in the last rule above. Thus, decimal(38,6)

In your second case decimal(18,14), the first rule is being applied for your scale for the resulting value of decimal(36,28) to min(28, 38 -(36-28)) = min(28,30) = 28. Thus, decimal(38,28)


pgAgent service for PostgreSQL does not run

I'm not sure what I did, but I have the service running now. I guess it is a combination of little of everything

Open CMD as administrator
browse to pgagent.exe

cd C:\Program Files\PostgreSQL\pgAgent\bin

Remove the pgAgent service (if pgAgent service is installed, but does not work)

pgAgent REMOVE pgAgent

Then I installed the service again.

pgAgent INSTALL pgAgent -u WindowsUser -p WindowsPassword hostaddr=127.0.0.1 dbname=postgres user=postgres password=postgePassword

(Not sure if the following steps are needed but) Open Windows services
There you can confirm the windows account that runs the service.
open up pgpass.conf in %APPDATA/postgresql and add the line

127.0.0.1:5432:*:windowsUsername:windowsPassword

add pgpass.conf as system variable PGPASSFILE


Creating a Trigger to Update Matching Records in Another Table

Here is an example of joining the virtual inserted table with TABLEB.

--demo setup
Drop table if exists TableA
Drop table if exists TableB

Create table TableA (Field1 varchar(20), Field2 varchar(20), Field5 varchar(20))
Create table TableB (Field1 varchar(20), Field2 varchar(20), Field3 varchar(20))

insert into TableB(Field1,Field2,Field3) values('one','two','three')
insert into TableB(Field1,Field2,Field3) values('four','five','six')
go
--the trigger
CREATE TRIGGER I_Am_A_Trigger ON TableA
AFTER INSERT
AS
UPDATE tb
SET tb.[Field3] = 'Hello World'
FROM inserted i
JOIN TableB tb
    ON i.Field5 = 'ABC'     --make sure to only join where the inserted Field5 was 'ABC'
        AND tb.[Field1] = i.[Field1]
        AND tb.[Field2] = i.[Field2]
GO

insert into TableA(Field1,Field2,Field5) values
('one','two','abc')

select * from TableB

| Field1 | Field2 | Field3      |
|--------|--------|-------------|
| one    | two    | Hello World |
| four   | five   | six         |

Including a check on Inserted.Field5 in the UPDATE statement takes care of multiple inserts; only those inserted rows where Field5 has the correct value will be used to update tableB.


The most detailed discussion of how statistics are used is the Row Estimation Examples section of the documentation.  Ultimately all information about how a query might be executed is turned into a serious of costs via the various Cost Constants.  So if a table is 1000 pages in size, and the statistics suggest 10% of it will be touched at random by a proposed query, that's 100 pages * 4.0 (random_page_cost) = 400 cost units for pulling in the data; then other constants are used to determine things like processing costs on the data in those pages.

The query optimizer tries various ways of obtaining and combining individual components:  different join types, different ways to access the table data, etc.  It iterates through the possible plans from those combinations, then picks the one that has the cheapest total cost to execute.

I wrote just over 50 pages on this subject for my book PostgreSQL 9.0 High Performance, which has the longest discussion of query execution available right now.  There's isn't too much there on how statistics are used beyond what's shown in the documentation though.  Most of it covers all of the various query plan node elements you might run into.


We've used Change Tracking to handle this.  We've found it lightweight and easy to set up and administer.  The largest system's moving tens of thousands of rows (from a table containing millions) per day without signs of stress. Of course your configuration will dictate your throughput.

It's big cousin - Change Data Capture (CDC) - will handle the case where you need to know the "before" and "after" values for each and every change that happens.


For the IP address you can use the function inet_client_addr()

All the system information functions are documented in the manual in the chapter "System Information Functions"

http://www.postgresql.org/docs/current/static/functions-info.html


How to version control PostgreSQL schema with comments?

Why don't you COMMENT ON the various SCHEMA components, that way your comments are in the schema, and will get dumped.

COMMENT stores a comment about a database object.

To modify a comment, issue a new COMMENT command for the same object. Only one comment string is stored for each object. To remove a comment, write NULL in place of the text string. Comments are automatically dropped when the object is dropped.


High CPU Usage on certain query when changing DB Compatibility Level

On higher compatibility level SQL Server starts to use new cardinality estimator which leads to different execution plan. Sometimes this new plan may be less efficient but it doesn't mean that new cardinality estimator works worse. Usually it means you need to review your query and probably adjust your indexes or statistics because probably your original plan was far away from optimal too. You attached estimated execution plans to question description. To find out the root reason of the issue you need to compare actual plans.
You can use traceflag or hint to force SQL Server to use old cardinality estimator for your query: OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))


However, what I'm really curious about is what else might get flushed along with that.

Any log lower than the LSN currently requesting to be flushed will be flushed as well. Log records are put into the log buffer in order and all transactions are serialized to the log buffer for that database. Note that individual log records aren't flushed, they are put into log blocks and log blocks are flushed.

  Does anyone know if such an optimization has been made in SQL Server?

It's called group commit, and it stalls the normal commit for a few ms if it can group more together for better overall performance (roughly-ish speaking). I believe it was added in 2008R2.


How to Choose an SQL Database for Simple Data Preparation & Analytics

Questions to ask could include:

What fits well with your current skills? (That might make it 'easier' to use.)
Is your focus on getting something working soon, or on developing new skills?
What is the cost of your choices (money, time, effort, unfamiliarity, etc)?

With your list you do emphasize cost as a factor and the database is not expected to be huge.  So, you can use the free version of some vendor product (such as SQL Server Express), or use open source offerings (such as MySQL, PostgreSQL, etc).

But you will pay, of course, for any choice.

Once you know where you want to spend your resources (which only you can answer) you should be able to discard some of the options.


A question regarding the performance of normalization

The relationship between normalization and performance is complicated.  Sometimes the pursuit of normalization and the pursuit of high performance lead in the same direction, sometimes in opposite directions.  You have to look at each case carefully.

More importantly, there is more than one measure of "goodness" in a database.  In addition to performance, there is ease of programming, ease of understanding, adaptability in the face of changing requirements, and so on.

Learning how to normalize is just the beginning.  In addition to learning the basic normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), you should learn what benefit you get out of conformance to each of these normal forms.  In general, conformance to 1NF buys you keyed access to all data.  conformance to 2NF through 5NF buys you freedom from harmful redundancy. Once you know the benefits, you can compare them against the costs.

A database that contains harmful redundancy has the possibility that the database will  contradict itself, by storing contradictory predicates in two different places.  You can exercise discipline in avoiding self contradiction by being very careful when you write the application.  But it's often better to design the database in such a way that it can't contradict itself, and normalization helps here.

Normalization sometimes helps a database to avoid "hot spots" at update time, where the DBMS is forced to serialize update transactions, and thereby creates bottlenecks.  But extracts and reports are often slower and more cumbersome than they are on a well designed database that is not normalized.

If you do decide not to normalize, don't just design at random, composing tables in any way that seems good at first sight.  Adopt some design discipline other than normalization to guide you to a good design.  Star schema is one such alternative design discipline.


What are the advantages of going with AWS aurora DB with Mysql backend over AWS aurora DB with Postgresql backend

You got it wrong.  Aurora is its own backend, but it comes with MySQL compatibility:

  Amazon Aurora is designed to be compatible with MySQL 5.6, so that existing MySQL applications and tools can run without requiring modification.

The PostgreSQL compatibility is only available as a preview yet, in only one AWS region, after sign-up.


PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates?

With PostgreSQL logical replication, the table on the standby server (where the subscription is) is available for data modifications. This is different from physical replication, where the standby is a physical copy of the primary and cannot be modified directly. Perhaps your confusion originates there.

While you can modify the table on the standby, it is not necessarily a good idea to do that, because it can lead to replication conflicts. But it is in your own responsibility to not perform any such operations.


Car service database design (domain model)?

I think you are trying to be a bit too "elegant".  Einstein is quoted as saying "things should be made as simple as possible, but no simpler."

You are trying to have a single record for something like an oil change - but then you need two other tables that give you the flexibility to apply the approriate price for this service in different situations.

Instead, I would suggest that you flatten out your three tables and just have a SKU table which includes a default price.  You would have different SKU records for each kind of oil change, for example.

The service technician (i.e. user) needs to apply the proper SKU to each service they provide.  This way, you are relying on the user to pick the right service/price rather than trying to build a table-driven set of business rules that must be used so that you can "deduce" the proper pricing.  I would say, based on my experience, that users would much rather have a short list of SKUs to scan through and to pick from rather than having some kind of interactive expert system that asks them multiple questions about different criteria until the right answer finally pops out the bottom.

If you want to allow some drilling down (or some rolling up) for SKUs, you can create a grouping table that groups similar SKUs.  If you really feel it necessary, you could even make this grouping hierarchical.

Your model might look something like this:

This will be easier to build and maintain, and much easier to explain to your users.  I believe too, as I said before, that your users will find this to be easier to use at the service counter too.


Database User not reported as orphaned after Windows login dropped

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:

SELECT p.name
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
AND NOT EXISTS
(
  SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
);

If you need to do this for all databases, you can generate this dynamically, e.g.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT ''' + db.name + ''',p.name
  FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
  WHERE [type] IN (N''U'', N''G'')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
  );'
FROM sys.databases AS db
WHERE [state] = 0;

EXEC sp_executesql @sql;


MySQL / MyISAM: Queries sometimes very slow, no waiting for lock time

If you are looking for the latest 100, you have not quite gotten it.  You need LIMIT 100 in the inner queries, too.  (OK, that will slow down the query a little, but it might be 'correct'.)

Back to performance...

Short answer:  Switch to InnoDB; it is likely to help, especially in avoiding strange locks.  (Instead, you will other strange locks, but usually not for as long.)

Which table is date in?  Hard to analyze the query without having all columns qualified and SHOW CREATE TABLE.  EXPLAIN SELECT ... would also help.  But I will try...

Indexes needed:

table4: INDEX(userId1, userId2)  -- in this order
table1: INDEX(userId)
table3: INDEX(groupId)
table2: INDEX(userId, class)  -- in this order


Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped.

pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name


MYSQL item in select value where value is enum

I managed to find a solution on google ...

SELECT * FROM tableX
WHERE FIND_IN_SET(id, (SELECT GROUP_CONCAT(ids) FROM tableY WHERE ...))


Solving Long Running Query, number of executions problem

There appears to be a problem with the server that will prevent you from getting good performance with that query, even when @SearchWordCount = 0. Consider the details for node id 15:

This plan executes in row mode and this operator is at the end of the branch, so you can attribute all 20 ms of CPU time and 3962 ms of elapsed time to the index seek on [Visit].[IDX_Visit_ClientID]. When executing this query you spent almost four seconds waiting for something other than CPU work. Looking at the wait stats for the select operator provides a valuable clue:

Almost four seconds waiting on PAGEIOLATCH_SH. We can get more information about the IO that was actually done by looking at the RunTimeInformation in the XML:

<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="8155" ActualRowsRead="8155"
    Batches="0" ActualEndOfScans="211" ActualExecutions="211"
    ActualExecutionMode="Row" ActualElapsedms="3962" ActualCPUms="20"
    ActualScans="211" ActualLogicalReads="726" ActualPhysicalReads="42"
    ActualReadAheads="8" ActualLobLogicalReads="0"
    ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>

SQL Server only needed to do 50 physical reads for the index seek, yet it waited almost four seconds to do them. You're reading data at a rate of 100 KB per second. There could be a problem with the server configuration or perhaps the server is just overloaded. Paul Randal published detailed instructions here for determining the root cause of the problem and how to fix it. Good luck.

On the subject of the pattern matching, SQL Server took about a third of a second to do that with just a single row in the temp table. That part of the query could become a bottleneck as the number of rows increases in the temp table. But you won't get good performance by changing that part of the query without resolving the issue with PAGEIOLATCH_SH waits first.


how to use trigger to make SQL Job's step success or fail

If you are storing records in a permanent table, you can make use of a trigger below.

--to check status of inserted record

create trigger triggername
on tablename
for insert
as
begin
if exists(select 1 from inserted)
--do your logic here
end

--to check status of deleted record

create trigger triggername
on tablename
for delete
as
begin
if exists( select 1 from deleted)
--do your logic here to start another process
end

Just for your information, inserted and deleted tables can be used to know what records are inserted/deleted as the result of the trigger action, and in spite of how many rows are inserted or deleted, the trigger will fire only once per statement. Further, you can combine both of the above statements into one by just doing like below:

create trigger triggername
on tablename
for insert,delete
as
if exists(select 1 from inserted)
begin
--what you want to do when a record is inserted
end

if exists(select 1 from deleted)
begin
--what you want to do when a record is deleted
end


My SQL Server table identity is incrementing way too fast

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway.

Example:

IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL
    DROP TABLE #test_identity;

CREATE TABLE #test_identity (
    i int identity(1,1),
    name varchar(50) UNIQUE
)

INSERT INTO #test_identity (name) VALUES ('aaaa')
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 1 row inserted, ident_current = 3
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 5
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('bbbb')

-- 1 row inserted, ident_current = 6
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('aaaa') --fails
INSERT INTO #test_identity (name) VALUES ('aaaa') --fails

-- 0 row inserted, ident_current = 8
SELECT * FROM #test_identity

INSERT INTO #test_identity (name) VALUES ('cccc')

-- 1 row inserted, ident_current = 9
SELECT * FROM #test_identity

The SQL Server 2012 jump in identity columns might be part of your issue, but I guess it has a minor impact on what you're seeing.

Identity values are not guaranteed to be contiguous by design, so if you're relying in "no holes" in your identities, you may want to reconsider your assumptions.


Pros and Cons of SQL Server Database Mail

Why would you build a table to log the emails into, then have another process come through and read the emails from the table in order to send them via database mail.

Why not just modify your application to send the emails directly via database mail.

Database mail uses SQL Service Broker under the covers to handle the email sending.  When you tell it to send an email it drops the email into a queue, then a background process comes through and grabs the email and actually sends it.  In your current design:

You drop the message into a table
A process reads the email
That process sends it to database mail
Database mail puts it into a queue
Database mail reads from the queue and sends the email

Why not remove steps 1, 2 and 3 from the process.


Scalable table structure for periodically updated stats that get aggregated over time

I'm frankly always nervous about throwing away details.  For that reason I'd try to find a way to keep handy (or at least archive) the most granular data.  That way, if your summarization requirements change, you can always go back to the source data and recalculate appropriate summary statistics.

As to your approach of calculating averages over varying times at various ages, this is a fair practice that would meet the needs of many organizations, to a point.

While it may certainly be true that you don't care about per 30 second periods after a few weeks, let alone one year from now, at what point do you stop increasing the reporting period?  Do you stop at days or do you go to weeks?  (or months? or quarters?)

Once you get past days, you run into issues with doing year over year comparisons, which many organizations rely upon.  I have seen some datawarehouses where there are multiple periods precalculated.  Whether you do this or just pick one "longest period" e.g. days, is a matter of how you trade off quick access and redundant data.

Regarding "How would I organize this into a table?" the best way would be to have starting and ending date/times (down to the second) for the period being represented, along with the average count over that period.  For convenience, you might also include a partitioning attribute that describes the length of the period, e.g. 'm', 'h', 'd', 'w', 'M', 'q', 'y' or whatever makes sense for your summaries.


On-premesis SQL Server setup for MFA against Azure AD

I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition).

"Active Directory - Universal with MFA Support" is an Azure Active Directory authentication method.  SQL Server (on premises) doesn't support Azure Active Directory Authentication; it only supports SQL Auth and Windows Auth (NTLM and Kerberos).


Pick one single row each time from either table?

First of all, this is my first post on dba.stackexchange.com, so I apologize in advance if there is any etiquette that I fail to follow, and am open to your input as to how I can improve future posts.

Best database design practices would call for consolidating the two very similar tables into just one table, like sa555 described. The issue you are currently facing is the direct consequence of having these two tables separate.

However, since you are working with a live system, I would recommend asking yourself and/or the rest of your development team (if applicable) the following question:

Will the future benefits of consolidating these two tables outweigh the time and effort put into the process of consolidating the two tables? Keep in mind that any applications depending on these two tables being separate will need to be updated also.

If the benefits outweigh the costs, I would recommend consolidating the two tables, just as sa555 recommended.
If not, I have included the MySQL code below to create a stored procedure that will return the most recent row from either table. Just remember that this is a mere surface-level fix, and that your database still contains a design flaw that could impede future development.

Here are some important notes on the procedure's edge-case behavior:

If both tables are empty, the procedure will not return a result set at all.
If only one of the tables contains records, the most recent record from the non-empty table will be returned.
If the most recent records from both tables have the exact same date and time, the record from tblGAlert will be returned. You can return the tblEAlert record by changing the inequality from ">=" to ">". (comments will explain where to do this)
Since both tables have different column names, you may want to canonicalize the column names in this procedure's result set using column name aliases (e.g. SELECT eDateTime AS AlertDateTime). The comments I have inserted into the code will direct you as to where to insert the aliases.

SQL Query (creates stored procedure):

DELIMITER $$
CREATE PROCEDURE GetLatestAlert()
BEGIN
-- Select latest gAlert record
SELECT gDateTime, gAlertID INTO @gAlertDateTime, @gID FROM tblGAlert ORDER BY gDateTime DESC LIMIT 1;

-- Select latest eAlert record
SELECT eDateTime, eAlertID INTO @eAlertDateTime, @eID FROM tblEAlert ORDER BY eDateTime DESC LIMIT 1;

-- We still want to be able to return a record in the event
-- that one of the tables does not contain a record, so we make
-- the default @gAlertDateTime and @eAlertDateTime values equal to the earliest
-- MySQL date possible, instead of the default value of null (if
-- no records were returned from that respective table). This is
-- because the NULL value cannot be compared using the ">" operator
-- that appears later on in this procedure.
IF @gAlertDateTime IS NULL THEN
    SET @gAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

IF @eAlertDateTime IS NULL THEN
    SET @eAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

-- Before comparing the dates, make sure one or both of the tables contain(s) a record.
IF @gID IS NOT NULL OR @eID IS NOT NULL THEN

  -- Compare the dates and return the latest row.
  -- Note: if one of the tables does not contain a record,
  -- the default values for @gAlertDateTime and @eAlertDateTime are the earliest
  -- possible date in MySQL, which will automatically make the
  -- logic choose the table that contains the record.
  IF @gAlertDateTime >= @eAlertDateTime THEN
     -- Change the inequality from ">=" to ">" in order to return the tblEAlert record
     -- in the event that both records have the same date and time.

     -- NOTE: You may want to canonicalize the column names for the tblGAlert and
     -- tblEAlert results by adding column aliases. This way the application using
     -- the data will get consistent column names.
     SELECT * FROM tblGAlert WHERE gAlertID = @gID;
  ELSE
     -- If you canonicalize the column names, insert the aliases here as well.
     SELECT * FROM tblEAlert WHERE eAlertID = @eID;
  END IF;

END IF;

-- If no records were returned from either table, this procedure
-- will not return a resultset at all.
END; $$

Now just execute CALL GetLatestAlert(); and the most recent row from either table will be returned.


InnoDB Tablespace critical error in great need of a fix

Your free space in the current innodb file (ibdata[1] ?) is decreasing.
It doesn't necessarily mean that your website is slow.

Maybe the number of users (concurrent users) has increased in the last few days ? (also causing lots of inserts int he DB?)

There is no error in the print screen. it's just your monitoring system, reporting a value that have reached the critical threshold.


cannot modify a column which maps to a non key-preserved table

In your view you do not fetch all the key fields from both tables. Oracle needs that to be able to do the update. In your case you do an insert so I suppose that you do not provide the primary keys for both tables. Since you did not gave the structure of both tables I cannot be more specific.


Reducing size of datatype involved in index fails where as increasing size work

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length").  As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index).  The SQL engine is smart enough to know that and doesn't do anything to either, other than notate that the max size has changed.

On the other hand - if you were to try your script using fixed-length data types (i.e. CHAR of CHAR), any change up or down will change how the content is stored (since the full length is pre-allocated).  This would force the table to rewrite (and the index would need to reflect the change as well).

If you run that very script you have above but with NCHAR, the reported error would pop up during the INCREASE as well.


How Can I Make My Statement Accept Multiple Results?

I added GROUP BY label_id to make the original statement work.

SELECT `labels`.*,
       IF((SELECT `label_id`
           FROM   `customer_print_sessions`
           WHERE  `customer_id` = (SELECT `customer_id`
                                   FROM   `customer_accounts_logins`
                                   WHERE  `id` = '1069')
                  AND `customer_print_sessions`.`label_id` = `labels`.`id` GROUP BY `label_id`) IS
          NOT NULL
       , 'Y', 'N') `in_queue`
FROM   `labels`
WHERE  `id` IN (SELECT `label_id`
                FROM   `customer_groups_label_relation`
                WHERE  `group_id` IN (SELECT `group_id`
                                      FROM   `customer_accounts_group_relation`
                                      WHERE  `customer_id` =
                                             (SELECT
                                             `customer_id`
                                                              FROM
                                             `customer_accounts_logins`
                                                              WHERE  `id` =
                                             '1069'
                                             )))
ORDER  BY `labels`.`plu` ASC;


Need help understanding Postgres materialized views under the hood

All your assumptions are correct.
You need to ANALYZE the materialized view to get correct estimates – this should be done automatically by autovacuum, so I am not sure what could be wrong there.
It also looks like your materialized view is terribly bloated. You would need to run VACUUM (FULL) on it to fix that.
Both point in the direction of autovacuum not doing its job properly. You should investigate that.


Quote from the manual

Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend


Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys?

The user_lookups column of sys.dm_db_index_usage_stats is a count of clustered index key lookups (a.k.a. bookmark lookups). This indicates a data row retrieved via a non-clustered index using the clustered index key row locator. The separate user_seeks and user_scans columns for a clustered index count the number of times the CI was used directly.

These columns allow you to determine how the clustered index is being used in query plans.


what would be my storage account?

MYCOMPANYbackups

  How can I have access to the backup files?

http://portal.azure.com
or
AzCopy
or
PowerShell
or
Azure Storage Explorer

  Where are these azure permissions managed?

Permissions for accessing the blobs is controlled by the storage account keys, or Shared Access Signatures.  See Azure Storage Security


Does adherence to the DRY principle justify dynamic SQL?

Strict DRY doesn't really apply to databases

I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance.

Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL not does it justify a view.

In your specific case above, I'd consider an IF statement to capture the difference, especially if the code was issued by the same search page or form: you have similarity based on usage, not just the same columns and table being used.

Also, you have security to consider.

Dynamic SQL require EXECUTE AS (escalation of rights) or permissions on the base tables. Using a plain stored procedure does not require such permissions.

You may also have different client code: we have stored procedures per client (schemas control permissions). Do you want to GRANT select rights on all your tables to all clients?


SQL Server 2008 R2 - Error 18456 State 12 - nothing I find helps me

SQL Server operates in 2 security modes: "Windows" or "Mixed"

Ensure you are set to "Mixed" as per this MSDN article: How to: Change Server Authentication Mode

The clue is in the error:

Login failed for user 'kentico' is not the same as Login failed for user 'DOMAIN\kentico' which means a SQL login (not Windows)
Error 18456 is for Windows logins

Ergo, your mode is wrong...

Our very own Aaron Bertrand has a good article on the error 18456 states


Running members of a job subset at different intervals

If the jobs are all scheduled to run at the top of the hour, then you could simply have a single job, with a single step, and schedule the job to run every hour (or on select hours). Then the step can have logic like this:

DECLARE @h INT = DATEPART(HOUR, CURRENT_TIMESTAMP);

IF @h = 1
BEGIN
  EXEC dbo.ProcedureToRunAt1AM;
END

IF @h = 2
BEGIN
  EXEC dbo.OneProcedureToRunAt2AM;
  EXEC dbo.AnotherProcedureToRunAt2AM;
END

IF @h IN (4,16)
BEGIN
  EXEC dbo.OneProcedureToRunAt4AMand4PM;
END

If you need it to be more granular, you can check the minute, minute/10, etc.


Can not install oracle apex on GNU/Linux (db 12c)

Check the status of pluggable database.

SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PDBORCL          MIGRATE
PDBORCL2         MIGRATE
PDBORCL1         MIGRATE

SQL> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Check the status of pluggable database.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PDBORCL          MOUNTED
PDBORCL2         MOUNTED
PDBORCL1         MOUNTED

We need to start PDB$SEED pluggable database in UPGRADE state for that

SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MIGRATE
PDBORCL          MIGRATE
PDBORCL2         MIGRATE
PDBORCL1         MIGRATE

SQL> exit

try again
it will solve the issue


Where can I find documentation on SQL Server internals?

I'm looking for docs on the internal format of the WAL log

The SQL Server Log File is not publicly documented, but there is a partner program for 3rd parties that want to build solutions that use it directly.  EG Attunity, Quest, etc.

    And, moreover, is there anyway to know what exactly is documented with MSDN without first paying for it?

There is ZERO additional documentation available to MSDN subscribers.  All published documentation and support material is published to the web.

The best sources for SQL Server Internals are the work of Kalen Delaney https://www.red-gate.com/simple-talk/author/kalen-delaney/ and Paul Randal https://www.sqlskills.com/blogs/paul/.

In particular Paul has some posts on reading the SQL Log. eg
https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/


Stolen Server Memory and Database Cache Memory

"stolen" memory?  I like that.  Seriously though, I think the question may be clearer with OS vs SQL memory.  SQL will use the startup minimum set up in the config.  If you didn't set a maximum, then it will continue to steal memory as more work is demanded.

Thus the available total RAM for the OS decreases over time.  Simply set a maximum in the SQL properties, in your case 28GB, so it doesn't steal everything and starve the OS.

This is how SQL behaves normally so it's not that much of a thief.  Hope that helps.


phpMyAdmin Strange permissions #1142 error after deleting a table record with success!

This ia a bug for PMA 4.7.0.
Here is the bug tracking https://github.com/phpmyadmin/phpmyadmin/issues/13214

If anyone comes here with the same problem for PMA 4.7.0, then open the PMA/libraries/Tracker.php and alter the following lines:

Index: Tracker.php
===================================================================

// - Remove/Comment
$result = $GLOBALS['dbi']->fetchValue($sql_query);

// + Add
$row = $GLOBALS['dbi']->fetchArray(PMA_queryAsControlUser($sql_query));

// - Remove/Comment
return ($result == 1);

// + Add
return isset($row[0])
    ? $row[0]
    : -1;


Query with HAVING clause is taking too long

There may be more suggestions, but these should help performance some...

        WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  message_id = msg.id

Presumably, you have PRIMARY KEY(uid, message_id) ?

        WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  following_uid = msg.uid) AS isFollowing,

Some kind of index with both follower_uid and following_uid?

WHERE is done while gathering rows; HAVING is done after all the rows are gathered.  That is, WHERE is more efficient.  However, HAVING is required when working with aggregates; but you don't have such.

Don't include unnecessary things like foo LIKE '%'.  Instead, build the query without it.

Discourage users from providing LIKEs with leading wildcards.

message_likes -- Don't COUNT something and then just check for 0 or 1.  Instead, use EXISTS ( SELECT 1 FROM .. WHERE .. ) -- and this can be put directly in the WHERE (unless you also need to fetch the value).

"number = string" -- Avoid mixing numeric and non-numeric.  In some situations, it requires all rows to be dynamically changed from a string to a number before checking.

("voilation" - check spelling)

How much data?  What is the setting of innodb_buffer_pool_size?  How much RAM?

created_at > '0' -- Huh?  When you upgrade, beware of "zero dates".  Consider changing to NULL instead of 0.


One SQL Server RDS instance plus one Ubuntu instance cost money on my initial free trial year

Yes technically both are considered separate instances.  However you get 750 hours free for each as they are billed as separate services with the caveat that instance sizes have to be a t2 micro.  Since RDS is a managed service you won’t see the running instance in your ec2 console even though it’s an instance running in your account.

Some things to look at on your bill as they might be the cause of the charges:

Are you using more than 30gb for EBS volumes attached to your ec2 instance (You can have any combination of gp2 or hdd EBS volumes, they just need to be <= 30gb)
Did you allocate more than 20gb for your RDS instance
Have you been stopping and starting your ec2 instance multiple times an hour (Each time you stop then start your instance the clock starts over on the running costs. Restarts keep the clock running)
Multiple snapshots of the EBS/RDS volumes


Restore the DB into another DB , while restoring select "To a point in Time" and select "A specific date and time" and enter the date time before you deleted the record


Easy!
Use the Azure SQL Database DTU Calculator:

Download the PowerShell script on your server
Run powershell -ExecutionPolicy Bypass -File .\sql-perfmon.ps1 and let it run for a few minutes
On the web page enter the number of cores that your server is using
Stop the PowerShell script and upload the .CSV file that is now on the C:\ root


Taking your examples, these are normally different queries if c2 is a child of c1 (a.k.a many rows in c2 per row in c1). Which is the usual case unless you only have 1:1 relationships.

select c1 from t1 join t2 on c1 = c2

select c1 from t1 where c1 in (select c2 from t2)

The first one will give a row for each match
The second gives unique c1 values only.

To make them the same you'd need DISTINCT on the first one: which doesn't make it more readable.

Going further, the 2nd one can be expressed as

select c1 from t1
INTERSECT
select c2 from t2

select c1 from t1 where EXISTS (select * from t2 WHERE t1.c1 = t2.c2))

That is: IN, EXISTS and INTERSECT give the same results.

Now, we may as well cover the inverse case too since you asked about LEFT JOINs. Here we have NOT IN, NOT EXISTS, LEFT JOIN and EXCEPT.

Let's say we want rows from t1 with no rows in t2 where t2.c3 = 'foo'

select c1 from t1 left join t2 on t1.c1 = t2.c2 AND t2.c3 = 'foo'
WHERE t2.c2 IS NULL

select c1 from t1 where c1 NOT in (select c2 from t2 where t2.c3 = 'foo')

select c1 from t1
EXCEPT
select c2 from t2 where t2.c3 = 'foo'

select c1 from t1 where NOT EXISTS
        (select * from t2 WHERE t1.c1 = t2.c2 AND t2.c3 = 'foo')

In this case, only EXCEPT and NOT EXISTS are always correct. NOT IN will fail in t2.c2 is ever NULL. And you need DISTINCT in the LEFT JOIN.

Note also that the LEFT JOIN filter is in the ON clause. To remove this you'd need a derived table or a CTE.

I would also say that the subqueries these days don't matter too much given the sophistication of the query optimisers (unless abused of course)

Personally I almost always use EXISTS and NOT EXISTS (maybe INTERSECT or EXCEPT for clarity) so my code is consistent and works in every case. I don't have to worry about bollixing a LEFT JOIN into an INNER JOIN, or NULLs in an NOT IN.


Reason for slowness is that - your buffer pool (including other caches e.g. plan cache, etc) are nuked when sql server is restarted.

Couple of things that you can do -

After reboot, if you know what tables are getting hit by your critical jobs, just cache them (doing select * ) into the buffer pool.
Check to see if your server is configured properly  - e.g. power setting (high perf mode) , max memory setting, etc. You can use sp_Blitz and follow general best practices.

I am assuming that you are regularly updating stats and / or reorg/rebuilding indexes as and when required.


I'm not sure if this code is fully accurate for columns that have more than 1 row with the same value, but I suppose that's a different subject.  What I see is that the only value the null counter is ever initialized to is 0.  That means it will never have an effect on the addition.  Putting on my dev hat, I could see the start of an unfinished concept.

If I needed to update a column to NULL, the where clause on line 9 wouldn't work.  The test NULL = NULL is false.  That means the join logic fails for update values of null.  That being said, it appears the error handling for that was never implemented beyond having a count column to use.


You need to keep the comparison column outside of your CASE statement.

INNER JOIN Users u
ON u.id = CASE WHEN c.sender = ? then c.reciever else c.sender END


Does SQL Server execute queries in parallel?

You mean "concurrently". The answer is yes, with caveats that are too broad to discuss here.
In fact, the whole point of RDBMS is concurrency.

"Parallel" has a precise meaning in SQL Server: "a single query is distributed over more than one processor core".


Is there any way to reduce/shrink tempdb.mdf size without restarting SQL Server

You can do that this way:

-- write everything from your buffers to the disc!
CHECKPOINT;
GO
-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
-- Now shrink the file to your desired size
DBCC SHRINKFILE (TEMPDEV, 40960);
-- Make sure that there is no running transaction which uses the tempdb while shrinking!
-- This is most trickiest part of it all.
GO

The last step is the most trickiest. During the shrink process, no other action should use the tempdb, as this could cause an abort of your SHRINKFILE operation. Due to the fact that the tempdb is quite easy to shrink, it shouldn't take to long to shrink it.

Beware that this is something like a "soft restart". Everything will be removed from the buffers and written to the disc. This means an impact on your I/O subsystem (write) as it have to handle all the write operations. After that you can shrink the file (which has an impact on read and write performance) and at the end, all processes which query any table will need to retrieve the data back from the I/O subsystem into the buffers. This may hurt more than an restart.

If you're running a development system, you should just restart the machine instead of this way. But on some production systems without a failover partner, this may be useful.


Message system - delete message, or flag for deletion?

Ultimately it will depend on where you want the responsibility for deleting the emails to reside, system or user? If your main goal is performance then the fastest way to do this would be to only store a single copy of the message and then send the clients a reference to this message instead of the message itself. The client can then download the message when a user clicks on it. Once a message has been downloaded by all recipients then it can be flagged for deletion by the system regardless of whether the clients decide to delete their local copy or not.

I would look at how other messaging systems work and decide whether the method they use suits your purpose. For example Microsoft Exchange uses Single Instance Storage, a description of this is below:

  Exchange utilizes what Microsoft terms a single-instance message store. This single-instance message store works on a per database basis. What does this mean? If an e-mail message is sent to multiple mailboxes that are all in the same database, the message is stored once and each mailbox has a pointer to the message. The transaction is also logged in the transaction logs for the Storage Group that contains the database. However, if the e-mail message is sent to multiple mailboxes that are located in different databases, the message is copied to each database and written to the transaction logs for each Storage Group that contains the database with a copy of the message.

As the designer of the system it is ultimately up to you to make the call based on the requirements you are given. That being said, my personal preference is to put the ownership on the clients to tidy themselves up so that you can worry about important things instead. You could implement some type of archiving system if you wanted to so that once it is downloaded by all clients it gets archived for a set period of time before being removed - maintaining a good backup strategy is also important in these scenarios.

Remember, although speed is important you can only go as fast as your network allows you to under a given load - something a lot of companies seems to overlook.

I hope this helps you.


Maintaining referential integrity in a booking system

If you only had two tables, Session and Booking, you could do this:

Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
Add CourseSizeMax column, and BookingNumber  column to your dbo.Booking table.
Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.


According to your desired result, you should group_concat workers group by department's name.

select d.name, group_concat(w.work_id) workers_id
from   department d
join   worker w
on     w.department_id = d.id
group by d.name;

name                            | workers_id
:------------------------------ | :---------
Academic Personnel              | 6,4
Anthropology                    | 3
Arts & Lectures                 | 5,2,7
Business and Financial Services | 1
Computer Science                | 8

dbfiddle here


Resource Governor seems to increase resource usage

I have three suggestions

Look into your Data Warehouse designs.  It should be optimized to let ad-hoc queries run relatively quickly. Analysis Services can help with this.
Off load DW queries into no busy hours. I find this works really well for companies I have worked at. One should not run "big" queries when most of your users are accessing the system. Instead, they should be saved at times when the server has a low load.
Check to see if the power user is writing proper queries. You can have highly optimized indexes, great architecture, and preprocessed cubes, but if the power user is writing solutions procedurally and not set-based, that will be a problem. Maybe he is brining in too much data yet only uses a small part. Who knows, but a poorly written query can very likely be the culprit.


Way to set all queries to run through SSEM to READ UNCOMMITTED?

If by SSEM you mean SQL Server Management Studio (SSMS), there is an option you can set when logged in as this user to run as READ UNCOMMITTED by default:

But they could easily override that by unchecking the box, setting their own isolation level manually, or calling stored procedures that override. Features like Resource Governor can control a lot of things, but I don't think there's a way to hard-code isolation level for all queries a user submits...

Though, this post might be useful:

Can I force a user to use WITH NOLOCK?


You can simply use this :

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

Various other methods include:here

sys.partitions Catalog View
sys.dm_db_partition_stats Dynamic Management View (DMV)
sp_MSforeachtable System Stored Procedure
COALESCE() Function


Informix create procedure to query records and drop if value does not exist using select

I found an alternative solution using a stored proc in informix that will also work for me:

create procedure systables_growth_tblclnup()
DELETE FROM systables_growth WHERE tabname NOT IN
(SELECT unique(tabname) FROM systables WHERE systables_growth.tabname = systables.tabname);
end procedure ;


How to get last identity row Inserted when using instead of trigger

In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert.

USE tempdb;
GO

CREATE TABLE dbo.SmellThis
(
  id INT IDENTITY(1,1),
  name VARCHAR(32)
);
GO

CREATE TRIGGER dbo.SmellThis_First
ON dbo.SmellThis
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ids TABLE(id INT);

    IF NOT EXISTS
    (
      SELECT 1 FROM sys.objects AS o
        INNER JOIN inserted AS i
        ON o.name = i.name
    )
    INSERT dbo.SmellThis(name)
      OUTPUT inserted.id INTO @ids
      SELECT name
      FROM inserted;

    SELECT id FROM @ids;
END
GO

INSERT dbo.SmellThis(name) SELECT 'Remus';
GO

Results:

id
----
1

Now clean up:

DROP TABLE dbo.SmellThis;

As an aside, you should never, ever, ever be using @@IDENTITY or IDENT_CURRENT() anyway. And SCOPE_IDENTITY should be reserved for situations where you know only one row can ever be inserted. A common misconception with triggers is that they fire per row, like in other platforms, but in SQL Server they fire per operation - so a multi-row insert using VALUES(),(),() or INSERT...SELECT - which SCOPE_IDENTITY would you be setting to your variable?


Add a flag when certain conditions are met

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline:

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON,
    SD.TRANSACTDATE,
    SD.STARTDATE,
    SD.END_DATE,
    MIN(SD.IN_PUNCH) OVER (
        PARTITION BY SD.PERSON,SD.TRANSACTDATE) AS IN_PUNCH,
    MAX(SD.OUT_PUNCH) OVER (
        PARTITION BY SD.PERSON, SD.TRANSACTDATE) AS OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN
        (
            -- Number of LEAVE1 or LEAVE2 paycodes
            -- for the same person and date
            SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
            FROM SampleData AS SD2
            WHERE SD2.PERSON = SD.PERSON
            AND SD2.TRANSACTDATE = SD.TRANSACTDATE
            AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
        )
        = 2 --Both LEAVE1 and LEAVE2 are present
        THEN
            -- Decode current paycode
            CASE SD.PAYCODE
                WHEN 'LEAVE1' THEN 1
                WHEN 'LEAVE2' THEN 2
                ELSE 999 -- Impossible
            END
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD;

Output

Using APPLY

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON,
    SD.TRANSACTDATE,
    SD.STARTDATE,
    SD.END_DATE,
    CA1.IN_PUNCH,
    CA2.OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE1'
        THEN 1
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE2'
        THEN 2
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD
CROSS APPLY
(
    -- Minimum IN_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.IN_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.IN_PUNCH ASC
) AS CA1
CROSS APPLY
(
    -- Maximum OUT_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.OUT_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.OUT_PUNCH DESC
) AS CA2
CROSS APPLY
(
    -- Count distinct LEAVEx PAYCODEs
    -- for the current PERSON and TRANSACTDATE
    SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
) AS CA3 (LEAVE_PAYCODES);


Fail plan on a release to production with differential backup

You can't use differentials as stated in the other answer, but you CAN use database snapshots. They require Enterprise Edition, but they function exactly like what you're looking for. You take the snapshot, start your deployment, do user acceptance testing, and then either delete the snapshot or roll back to it.

Be aware that "snapshots" is an overloaded term - there's also snapshot replication (which wouldn't help) and SAN snapshots (which actually would help, but it requires a SAN, which I'm guessing you don't have.)


Multiple Batches in SQL Server Migration Script

The problem is really just about error handling, as SSMS has no way to handle errors across batches when you run the script in an SSMS query window (ether normal or SQLCMD mode).  So your transaction might abort, and SSMS would run the next batch.

If you run the script with SQLCMD you can start a transaction in the first batch, and use the -b switch.  Any error will abort the script and roll back your transaction.

EG

use tempdb
go
begin transaction
-- be sure to run with sqlcmd -b
-- eg c:> sqlcmd -b -i c:\deploy\thisscript.sql
go

--. . . any number of batches here

go

commit transaction

go

Of course you must ensure that your script does not commit or rollback the transaction, catch errors, or contain any statements that can't be run in a transaction (like adding files to a database).

You can similarly burst the batches yourself in .NET or PowerShell, start a transaction, and run each batch separately, commiting only if all batches ran without error.

Another good way to handle this whole scenario (if you can do the upgrade during an outage) is with a database snapshot. In case of a failure, restoring from a database snapshot allows a quick rollback of all the upgrade actions without the need of a transaction.


How do I get Postgres DO $$ to work from the bash prompt?

Using \ to escape the dollar $

echo \$PATH
$PATH

Reference to psql doc, in order to execute multi commands, they also provide 3 ways to work around as below

1) Repeats -c option

psql -c 'SELECT now()' -c 'SELECT * FROM foo;'

2) Combines echo and psql

echo 'SELECT now() ; SELECT * FROM foo;' | psql

3) Uses psql and EOF

psql <<EOF
\x
SELECT now();
SELECT * FROM foo;
EOF

Besides, considering changing rolename text to rolename RECORD to avoid ERROR:  missing FROM-clause entry for table (record type)

  Record variables are similar to row-type variables, but they have no
  predefined structure. They take on the actual row structure of the row
  they are assigned during a SELECT or FOR command

In terms of your case, please careful with DROP ROLE due to dependencies (refer: dropping role)

  If DROP ROLE is attempted while dependent objects still remain, it
  will issue messages identifying which objects need to be reassigned or
  dropped.

Last but not least, here's my example

#### Using psql and EOF
psql << EOF
DO \$$
DECLARE
  v_role record;
BEGIN
  for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
  loop
    raise notice '%', v_role.rolname;
    execute 'DROP ROLE ' || v_role.rolname;
  end loop;
END
\$$;
EOF

#### Using echo and psql
echo "
DO \$$
DECLARE
  v_role record;
BEGIN
  for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
  loop
    raise notice '%', v_role.rolname;
    execute 'DROP ROLE ' || v_role.rolname;
  end loop;
END
\$$;
" | psql


Here is my two cents in the form of an answer...

Kin pointed out the CPU and memory limitations of standard edition (16 cores/4 sockets and 128GB - though see Buffer Pool Extension), which I completely agree with being the largest obstacle. If you're server is going to be under those requirements then it's a moot point.

Aaron pointed out that if you use SQL 2016 you'll get a less flexible form of AGs. Honestly, to me, it doesn't sound like this will work for you. Cheap storage would be the death of it and if you're considering standard edition I highly doubt this database is that important. Not saying all important databases need to be enterprise edition - but the fact that you're in cheap storage and looking at SE says it all.

Additionally you're not looking at any enterprise level features which could help you. Range partitioning the key/value pairs, compression, column store indexes on the KVPs, TDE/AE, etc.

So, it seems, at the end of the day you're looking for someone to talk you INTO using enterprise edition. I don't know what the usage patterns of the database are, the load, or what it supports for the business... so unless it requires more than the CPU/Memory of standard edition I see no need with the information provided to go with enterprise edition.

The last thought is if this will be virtualized - if so, depending on licensing (enterprise of the host[s]) that's where it may make sense. This wasn't included in the discussion so I'm just throwing it out there.

See also: Features Supported by the Editions of SQL Server 2014


IMO, this type of work should be testable and repetable. I.e., scripted. I would go for backup/restore and then have a script that does your DDL changes (ALTER TABLE, ...). You script everything and test, re-test etc to know everything is fine.
Backup/restore time can be minimized by utilizing log backups.
As for the DDL changes, you will find out how long time they take. Depending on what you need to do, simple recovery model might be useful - but again, that depends on your DDL/DML in the end.


Here is an extended event session to find any DBCC calls, that have not already occured.  From your question it looks like you may have wanted to find ones in the past? Like others have alluded to there should be info in the logs on cache clears, and should read as follows (at least on SQL2014):

Date,Source,Severity,Message
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

For the future you could use this Extended Event definition.

CREATE EVENT SESSION [DBCC] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%'))), ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%')))
GO
-- Uncomment if you want to output to a target file and change the file path
--ALTER EVENT SESSION [DBCC] ON SERVER
--ADD TARGET package0.event_file(SET filename=N'K:\Backup\DBCC_XE',max_rollover_files=(2))
--GO

ALTER EVENT SESSION [DBCC] ON SERVER STATE = START
GO

-- Don't forget to turn it off
ALTER EVENT SESSION [DBCC] ON SERVER STATE = STOP
GO

Will get you some information like this (my user and server name are redacted):

If you want a trace (SQL Profiler) definition I could probably throw one of those together too.

Hope this helps.

Cheers


MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster

Did you restart the server after changing the server-id? Despite server-id being dynamic, there is the following caveat on the manual:

If the server ID is set to 0, binary logging takes place, but a master
with a server ID of 0 refuses any connections from slaves, and a slave
with a server ID of 0 refuses to connect to a master. Note that
although you can change the server ID dynamically to a nonzero value,
doing so does not enable replication to start immediately. You must
change the server ID and then restart the server to initialize the
replication slave.

I am giving a wild guess that may be your issue, because it is a very common problem that people suffer once when setting up replication.
Edit: Re "Interestingly mysql is ignoring the server_id in the my.cnf file and choosing its own."
Then you have a configuration problem in which your server id is not properly read, please use --print-defaults to debug which files and options are being applied live, and check the paths for correct permissions, file existence and avoid duplicate config definitions/unintended includes.


Managing multiple DBs and servers with central or local processes?

In the environment I managed for about a decade, backups were handled by local jobs, but we did have a couple of monitoring jobs (daily login records for audit purposes, and daily report on failed jobs) that was centralized.

So, here's what I can think of:

Centralized job "hub"

PROS:

Same approach applied to all servers.
Easy to change process for all servers in the environment.
Easy to monitor entire environment.

CONS:

One point of failure - If the hub fails, nothing gets backed up, etc.
Likely implemented with one account with admin access to all servers - if that's compromised, everything is.
"One size fits all" approach to all tasks may not be appropriate, in some cases (server-specific jobs may limit when you want to do full backups, for example; if all full backups are run from a single job, it becomes more difficult to accommodate specific time frames for particular servers).
You can presumably run certain unique jobs from that single server, but beyond a certain point it gets confusing as to which jobs hit which servers.
Harder to check status of a single server (unless that data is retained in a central table, in which case it may actually be easier).

Server maintenance jobs run locally, reported to hub

PROS:

Jobs can be configured as is best for that server.
One server can fail with no impact to the others.
Can use accounts that do not need access to all servers.
Easier to confirm activity for a single server - check the Job Activity Monitor for the server, all jobs are there.

CONS:

Failure of one server to report job status to hub server may not be noticed (harder to notice something that isn't there than something that is).
Jobs have to be set up one each server, or backups, etc. won't happen.
May be harder to spot trends affecting multiple servers.
Jobs may not be configured in a consistent way (even if the same person configures all the servers).

Many of the cons can be overcome if you've still got a central server that receives/collects data on jobs across all servers. As I said, that's the environment I worked in for several years, and it worked well for us.


webscale (https://github.com/webscalesql/webscalesql-5.6) is a fork of mysql implementing, apparently, facebook's mysql patches. These same patches are slowly making into many of the mysql forks out there.


How to create a trigger that update the date and time field and modify of the row

Create a task in SQL Server Agent that runs a stored procedure once every minute that updates the affected rows.

For instance, something like this might work:

CREATE PROCEDURE UpdateRows
AS
BEGIN
    DECLARE @Cutoff DATETIME;
    SET @Cutoff = DATEADD(mi, -30, GETDATE());
    UPDATE MyTable
    SET Status = 'ForcefullyClosed'
    WHERE CheckInDateTime < @Cutoff AND Status IS NULL;
END;


does it would hurt performance

Not in any significant way. The initial parsing may take a little longer, as might the network transfer (though for hundreds or even thousands of characters you are not going to really see that unless you are talking to the server over a cellular connection or such!), but after that stage the query planner will be given the same tokenised statements whether you used table aliases or not. Time spent in the query planner's realm will be much more significant than the parsing+tokenising time, and running the resulting plan will usually be orders of magnitude more expensive again.

tl;dr: if there is a performance difference it will be practically undetectable.


You're joining only on 'student_id', when you need to join using, at least, also result_session, but most probably all of result_term, result_session, result_level,result_class,result_arm.

Try either:

SELECT
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           /* This is what I guess you're missing */
           AND t1.result_session = t2.result_session
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2'

or

SELECT
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           /* Or most probably you're missing all of this */
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2'

As a side comment: this data structure for a database could very easily be improved...

My original trial (on a different DB than mySQL)

WITH agricultural_science(id, student_id, result_session,
    result_term, result_arm, result_class, result_level,
    "1st_test", "2nd_test", exam, result_total, result_grade, result_remark)
AS
(
VALUES
 (1, 'GCU/16/10414', '2016/2017',
    '1st', 'arm', '2', 'JSS',
    93, 92, 'exam', 'result_total', 'result_grade', 'result_remark'),
 (1, 'GCU/16/10414', '2015/2016',
    '1st', 'arm', '2', 'JSS',
     99, 23, 'exam', 'result_total', 'result_grade', 'result_remark')

)
, result_summary (id, student_id, result_score, result_average,
    result_term, result_session, result_level, result_class, result_arm
) AS
(
VALUES
 (1, 'GCU/16/10414', 99.1, 90.1,
    '1st', '2016/2017', 'JSS', '2', 'arm')
)

SELECT
    t2."1st_test" AS agric1, t2."2nd_test" AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2' ;


Compressed Backup size value is incorrect in msdb

This is just conjecture, but it sounds like LiteSpeed is compressing the backup after SQL Server has performed it's part of the work.  I doubt you can get accurate compression numbers from msdb in this scenario.

If Quest is using deduplication in their SQL Server backup product then SQL Server will certainly not be able to understand the real size of the backup, since the data stored in the backup will be opaque to SQL Server.

If you are trying to retain a history of backup sizes for growth tracking, you could use the numbers stored in MSDB as a rough guide to percentage growth over time.  If you need exact sizes of the backups saved by Quest LiteSpeed, I would recommend contacting Quest's technical support to see if there is a way to do that.


Distinct 3 columns and 4th column with sum

It's easy-peasy:

select a,b,c,sum(d)
from yourtable
group by a,b,c;

Probably too simple for this site, to be honest.


This is a strange behaviour!
Once user is created you can make sure of that by reviewing the grants for it.

show grants for 'username'@'hostname';

Privileges are persistent, you don't need any additional step to make them stay.

Check if my.cnf has something like --init-file or --skip-grant-tables. Also, how mysql is started? Does your root password get reset as well? Are you sure you're connected to the right mysql?


I have now found the source of the issue. It is because my ri_email_messages table was using the Aria engine. I had done this because InnoDB did not support full text search at the time. In MariaDB > 10.0.5, you can now do fulltext search in InnoDB, so I have switched back.

The way to tell in the EXPLAIN statement is that the PRIMARY query was using type=index. This should be type=eq_ref, since it is constrained upon the primary key, but this wasn't happening with Aria. After switching to InnoDB, this correctly shows as type=eq_ref, and the row match went from 126156 to 1.


Why decrease the wait_timeout configuration parameter in MySQL?

You should be just fine extending wait_timeout

Notice the maximum value for wait_timeout for MySQL 5.0, 5.1, 5.5, 5.6

Linux : 31536000 seconds (365 days, one year)
Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)

These maximums would not exist of mysqld could not handle them.

Connection pooling only saves on overhead in terms the alternative: destroying and reallocating memory for new DB Connections. This could cause the OS on the DB Server to engage in a lot more swapping to juggle memory.

I wrote about this over a year ago : How costly is opening and closing of a DB connection?

Lowering the wait_timeout could definitely throw Apache into confusion because there and PHP and Apache objects for DB Connections still instantiated while the DB Connection decides to terminate early and without warning. In that event, you may have to scan your netstat for any TIME_WAIT on connections over port 3306 or whatever port MySQL is using.

May 20, 2011 : Why is MySQL caching data on our dynamic PHP site?
Aug 17, 2011 : Too Many Connections
Oct 26, 2011 : What could be causing strange query timeouts between PHP and MySQL?
Feb 27, 2012 : MySQL connection count much larger than query count

If such exist and impedes Apache from opening a new connection, you would have to kill the TIME_WAITs yourself. See my Feb 01, 2012 ServerFault post MySQL lowering wait_timeout value to lower number of open connections on how to do so.


The best option is (my opinion) mongoexport data to disk and then mongoimport it to 3.6.


The server core is similar (identical?) on the GA and Standard Edition (SE)/Entreprise Edition (EE). What you're getting with SE/EE is stuff like (from here)

MySQL Partitioning
MySQL Enterprise Backup
MySQL Enterprise Monitor
MySQL Enterprise HA
MySQL Enterprise Scalability
MySQL Enterprise Security
MySQL Enterprise Audit

You also get support and some access to goodies on Oracle's MOS (My Oracle Support, used to be Metalink). You can tell your PHB - "look, if anything goes wrong, at least _you_ won't be blamed!".

The funny thing is that many of these "enterprise" solutions are available from open source contributors in various guises, and I for one am convinced that it's possible to have at least an entreprise standard setup using only Open Source components. With the advent of WebScaleSQL, that situation could move up to  carrier grade.

As for pricing - see here, it's basically $2.000 (Standard), $5.000 (Enterprise) or $10.000 (Carrier grade) per year. See the features table for a comparison.


Distinguish between unknown and non-present foreign keys

There is a mismatch between you and your DBMS regarding special values.  Special values are special in your mind, but the DBMS can't tell that a special value is different from an ordinary value.  So when you put a special value in, it expects a valid reference to a row in the lookup table.  If the reference is invalid, it flags a constraint error.

There is a way you can get around this, but it will create further problems down the road.  You can put the special value in as a key in the lookup table, with some sort of descriptive field set to "[Unknown Value]".  Then, when you do the join, this text will pop up in place of the known value.

I hesitate to recommend this solution.  In every case where I had to work with this, dummy values in lookup tables ended up causing more problems than they solved:

Wherever the foreign key and the primary key are joined, you have to include a condition to weed out the special values, if appropriate.  If you forget, that's a bug.  It you don't forget, that's a slowdown.

It depends on what the special value means.  If the special value means "this is not a value", among other things, then there is, conceptually, nothing to join to.  If the actual join produces a result, that result is likely to be meaningless or misleading.

But, if it works for you, great.


No. There is no way to do this without first restoring to MSSQL.


MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

Before MySQL 8.0, and MariaDB:
You may use DESC in an index declaration, but it is ignored.  (The PRIMARY KEY is an index.)
You must say DESC in the ORDER BY clause if you want the resultset sorted that way.
For multi-column indexes, all columns in ORDER BY should go the same way (all ASC or all DESC) in order to be able to use an INDEX.  (There are many cases where an index won't be useful for ORDER BY anyway.)
MySQL 8.0:  DESC is honored in INDEXes.