How to group many information une one select

What you are trying to do is called pivoting. You essentially want the results grouped by code and action but re-arranged so that instead of one row per code and action you get one row per code only, all the distinct action values (well, just two in your case) forming separate columns.

In other words, instead of

action   code  value
-------  ----  ------
action1  IN    value1
action1  OUT   value2
action2  IN    value3
...      ...   ...

you are trying to get

action   IN      OUT
-------  ------  ------
action1  value1  value2
action2  value3  ...
...      ...     ...

Some SQL products support a dedicated PIVOT syntax. MySQL is not one of them, but you can use other ways.

One common way is conditional aggregation. You will group the rows by code only, because you want one row per code in the output. The aggregation by action will be achieved by introducing a conditional as the aggregate function's argument. This is how you code that in SQL:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  SUM(CASE act.action WHEN 'IN'  THEN act.qte END) AS `IN`,
  SUM(CASE act.action WHEN 'OUT' THEN act.qte END) AS `OUT`
FROM
  article AS art
  LEFT JOIN action AS act ON art.art_code = act.code
GROUP BY
  art.id,
  art.art_code,
  art.art_name
ORDER BY
  art.id ASC
;

Each SUM expression aggregates qte just for one of the action values, either 'IN' or 'OUT', using a CASE expression. As shown in the query, you need two SUM expressions, one per each value of action. If you had more, you would need to add more SUMs accordingly.

However, as there only two values here, it may make sense to try alternative methods. One such alternative is in Akina's answer. Here is another option:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  act_in.`IN`,
  act_out.`OUT`
FROM
  article AS art

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `IN`  FROM action WHERE action = 'IN'  GROUP BY code
  ) AS act_in
  ON art.art_code = act_in.code

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `OUT` FROM action WHERE action = 'OUT' GROUP BY code
  ) AS act_out
  ON art.art_code = act_out.code

ORDER BY
  art.id ASC
;

As you can see, getting each of the two columns (IN and OUT) requires one outer join to a derived table containing the aggregated results for the corresponding action value. It is easy to see how this approach would not be a good idea if you needed to produce ten such columns. Not only would the query itself become cumbersome much more quickly than the conditional aggregation method, but the performance would deteriorate very soon too. However, for just two columns this option might be worth a shot.

Both suggestions presented in this answer can be found and played with at dbfiddle.uk (the setup was borrowed from Akina's fiddle):


How to re-create the ##MS_PolicyEventProcessingLogin## principal

Any account enclosed in "##" are certificate based accounts and you will not be able to simply create them as you would other logins/users. If you have a backup of the master database prior to the accounts disappearing you could might try restoring the database to get them back. Other than that I suspect you will need to reinstall or try rebuilding the master database.

Read through these two articles (here, here) for rebuilding the system databases. I would suggest taking a backup of the current state of the system databases. Script out all the logins you have in master as you will loose these.


You should be able to run it via the HOST command:

SQL> host /path/to/ora/bin/sqlldr parfile=...


Restoring Postgres from only base backup taken with wal-e

I have a postgres base backup taken via wal-e.

So I would suggest that your question would be best addressed to the people who created wal-e.  I've never used it myself, but ...

  Is it possible to start the DB and bring it to consistent state with only the base backup?

Short answer: No.

The base backup takes a "snapshot" of the database.
That snapshot, taken from a running database, it unlikely to be consistent in itself.

Anything and everything that happens in the database after that snapshot is taken is written to the WAL files so, in order to do any meaningful database recovery, you need those as well.


TNSNAMES.ORA is located in Windows 2012 Server

it's in your $ORACLE_HOME\network\admin

for example:
C:\app\myusername\product\11.2.0\client_1\network\admin\tnsnames.ora


Can SQL Server system tables be defragmented?

Are you sure you have positively and accurately identified this system table as the sole source of "unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database"? Are you sure this system table isn't self-managed in such a way that (a) fragmentation is minimized or kept in check secretly or just (b) managed efficiently in memory so that defragmentation levels really don't affect anything much?

You can see how many pages are in use, and you can see how much free space is on the pages that are in memory (page_free_space_percent is always NULL in the allocations DMF, but this is available from the buffer DMV) - this should give you some idea if what you're worrying about is really something you should be worrying about:

SELECT
  Number_of_Pages = COUNT(*),
  Number_of_Pages_In_Memory = COUNT(b.page_id),
  Avg_Free_Space = AVG(b.free_space_in_bytes/8192.0)
FROM sys.dm_db_database_page_allocations
(
  DB_ID(),
  OBJECT_ID(N'sys.syscolpars'),
  NULL,NULL,'DETAILED'
) AS p
LEFT OUTER JOIN sys.dm_os_buffer_descriptors AS b
ON b.database_id = DB_ID()
AND b.page_id = p.allocated_page_page_id
AND b.file_id = p.allocated_page_file_id;

If your number of pages is small (like probably < 10000 for system tables) or if the free space is "low" (not sure what your typical thresholds are for reorg/rebuild), focus on other, more interesting, low-hanging fruit.

If your number of pages is large and the free space is "high", ok, then maybe I'm giving SQL Server too much credit for its own self-maintenance. As you showed from the other question, this works...

ALTER INDEX ALL ON sys.syscolpars REORGANIZE;

...and does reduce fragmentation. Though it may require elevated permissions (I did not try as a peon).

Maybe you can just do this periodically as part of your own maintenance, if it makes you feel good and/or you have any evidence that it has any positive impact on your system at all.


MySQL Replication - Introduce new Slave to replication

Is this an issue with the fact now()
  is being in a query, which common
  sense tells me would cause issues if
  the inserts on that table were
  frequent enough??!?!

Yes, I believe this is the cause of your errors. Your method for introducing the new slave seems to be correct. It is quite strange in my opinion to define a table with a DATETIME field as the Primary Key. As you've quite rightly pointed out, the slave gets the replicated queries from the master and they will use the now() keyword in the queries, which will grab the timestamp from the local server.

Really, the table should be defined with some other data type for the PK (such as INT or BIGINT) which can be guaranteed to be unique, unlike a timestamp inserted with now().


First off, intra-query parallelism deadlocks are always a SQL Server bug.  There's not much you can do to fix them, other than updating SQL Server, opening a support case, suppressing parallelism (eg adding a MAXDOP 1 hint), or trying to get a query that uses a simpler plan.
In this case in EF, if you can, set UseDatabaseNullSemantics to true.  Otherwise the EF query translator generates TSQL queries that emulate the C# null comparison semantics.  That will clean up the NULL handling in the query.
Other than that, this query appears to be a view, joined with another table, and then a paging query on top of that.  So possibly can be simplified if you can figure out what the intent is.


When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

Attribute names in XML are not allowed to start with a number, see NameStartChar.

You have to come up with alternative names for your attributes and encode that in a separate @cols variable specifying column aliases for your dynamic pivot query.

SELECT @cols2 = STUFF((SELECT distinct ',' +
                       quotename(convert(char(10), [StayDate] , 120)) +
                       ' as '+ QUOTENAME('z'+convert(char(10), [StayDate] , 120))
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');

Result;

[2016-12-20] as [z2016-12-20],[2016-12-21] as [z2016-12-21]

When you are using for xml auto SQL Server does that for you.


PowerShell script to access Oracle system views

What powershell syntax are you using?

If you do not enclose the select between single quotes, Powershell will treat it like a variable. If that's the problem, please try:

$command.CommandText = 'SELECT COUNT (*) AS COUNT FROM v$session'


Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle?

In SQL Server all the sequence numbers are generated on a single instance so they are already inherently ordered. And if you’re just using them as unique numbers it shouldn’t really matter anyway.

The Oracle implementation is different because in a RAC environment (not all Oracle implementations), two sequence numbers could be generated by two different “instances” (that’s the wrong term for Oracle but just drawing equivalency in SQL Server). Normally you wouldn’t care if the one requested first was slower and got a higher number than the one requested later - unless, as the Oracle docs say, you were using the sequences as timestamps to truly reflect the order of the original request.


Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

John is right that the updated syntax error message you get:

  Msg 1034, Level 15, State 1, Procedure trigger
  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones.

In fact, I have tested this and the syntax was allowed in 80 compatibility mode (SQL Server 2000), but not in 90+. I've updated my canonical answer about compat level 80.

(As for why it's not a documented breaking change, well, shrug. You'll need to approach Microsoft directly if you want a meaningful answer to that one.)

As for the trigger itself, here's how I would write this:

UPDATE t
  SET t.datetime = GETDATE()
FROM dbo.table1 AS t
INNER JOIN dbo.table2 AS t2
ON t.id = t.id;

The syntax error might be a more strict parsing of the word table, not sure. If that really is the name of your table, you should change it, or [put it in square brackets].


How can I get rid of this Lazy Spool, or otherwise improve this query's performance?

The use of a scalar user-defined function is inhibiting parallelism in this query, which is hinted at in the execution plan XML: NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

One option would be to manually inline this function's implementation.  So this:

Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1

Becomes this:

CASE WHEN (POWER(2,3) & Txn.Account_Subscription)<> 0 THEN 1 ELSE 0 END = 1

Note: you would need to do this for both references to the function

There are some large index scans in the execution plan (22 million rows are read from the ACCOUNT table).  If the query really needs to process this many rows, parallelism can help a lot.

If the DISTINCT is not required for correct results, removing that could help with performance as well (preventing the potentially costly Sort (Distinct Sort) in the plan.

As far as the spool is concerned, it might actually be helping in this case.  You can try suppressing the spool by adding OPTION (QUERYTRACEON 8690) to the end of the query and seeing how the plan / query execution time is different. On SQL Server 2016 onward, the query hint NO_PEROFRMANCE_SPOOL is available.


You can use pg_column_size() to get the size of a complete row, not only for a single column.

So to find the size of all rows for a specific customer, you can use something like this:

select sum(pg_column_size(t) + 24)
from the_table t
where customer_id = 42;

(The 24 is the storage overhead per row in a table)

You would need to do that for all tables you are interested in.

This does however not include the size of those rows in the indexes.


Use this one:

DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT";

Note, when you use quotes, then names are case-sensitive in Oracle.


It probably means that you ran a lot of commands that start with "SET something = somethingelse;"

I would say to set your general_log on for a short while on your dev environment and search for SET commands. It would probably be something to do with setting the server time.

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Com_xxx


From the docs

  sdo_num_res Specifies the number of results (nearest neighbors) to be returned. If neither sdo_batch_size nor sdo_num_res is specified, this is equivalent to specifying sdo_batch_size=0. See the Usage Notes and Examples for more information. Data type is NUMBER. For example: 'sdo_num_res=5'

So just set sdo_num_res to whatever number of results you want returned.


Here is at least a part of the problem: If you have two rows in HMI_Temp with the same value for sn but different values for cb (or any other column), which one should SQL Server pick?

Here is a better pattern than MERGE, IMHO, which has all kinds of issues:

INSERT dbo.HMI(stn,sn,cn,lp,cb)
SELECT stn,sn,cn,lp,cb
FROM
(
  SELECT stn,sn,cn,lp,cb,rn = ROW_NUMBER() OVER
    (
      PARTITION BY sn,cn -- makes sn + cn distinct
      ORDER BY sID       -- picks which row to keep
    )
  FROM dbo.HMI_Temp
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.HMI WHERE sn = hmi.sn
  )
) AS x
WHERE rn = 1;

This is taking the row with the lowest sID but you may wish to rank the rows a different way or use MIN/MAX on each of the other four columns to determine which row you want to keep. Hard to tell from your question.


Transferring concepts - users/groups/permissions of db objects from mysql to postgresql

Granting permissions to users works exactly the same way in PostgreSQL.  There are some differences in the advanced features, but what you wrote above works fine.


Yes it is possible.

Cascaded Redo Transport Destinations

  A cascaded redo transport destination (also known as a terminal
  destination) receives primary database redo indirectly from a standby
  database rather than directly from a primary database.

  A physical standby database that cascades primary database redo to one
  or more terminal destinations at the same time it is applying changes
  to its local database files is known as a cascading standby database.

More details at the above URL.


A quick one using Itzik ben gan tally table.

DECLARE @date datetime='20180327 00:00'

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT TOP (1440) dateadd(minute,n,@date) n
FROM Tally

You can left join this with you meter reads table.


I had a blonde moment! Just using SUM instead of AGGREGATE and everything is ok as expected :(


Create Oracle ASM Disks Without Installing Oracle

Mat is right. ASMlib is only one way to created candidate disks for ASM. I would recommend you use udev as detailed here.


Carriage return/line feed stopped working in SQL Server

Since you're outputting to Grid View, I suspect that the option to Retain CR/LF on copy or save is disabled.  Enable this option, open a new query window, run the same query, and your newline characters should now come across after you paste results out of a Grid View.  In SSMS 2017, the Setting can be found under Tools → Options → Query Results → SQL Server → Results to Grid.  The path is in a similar location in other versions of SSMS.

Alternatively, if you want to apply this setting to the immediate query window only, enable the Retain CR/LF on copy or save option which can be found under Query → Query Options... → Results → Grid.  These settings won't be saved across all query windows, rather they are only applied to the current one.


what replication option should I choose?

As some of your tables don't have a primary key, and I assume they don't have a unique index on them either, you are pretty much hosed.  Anything which will be real time (transactional, merge, or peer to peer) will require either a primary key or that replication put it's own column in place called a rowguid (basically it's own guid column).

Since your schema can't be changed there aren't going to be any replication options which satisfy your requirements.


First hit from Google gives an MS KB article

"How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring"

Another viewpoint "How To Apply Service Packs To SQL Servers Involved In Mirroring"


Is capitalization of SQL commands/keywords required, expected or completely optional?

The SQL language is not case sensitive.  It is not important whether you type select or SELECT.

Often rules about capitalisation are included in a company's or project's coding standards. These often dictate where indentation, alignment and white space should be used, too.  This is important because it adds uniformity across all the code.  After a short while your eyes get used to the layout and can scan the code much more quickly, identifying important clauses and missing constructs.

Exactly where white space, alignment and capitalisation should be applied is a subject of active debate. Every programmer I've ever worked with had their own idea. There is no universally correct answer.  The value lies in uniformity, not the individual choices.

Some years ago colleagues were working on a project where the database was declared with a case sensitive collation.  Then they did have to be careful about capitalisation as key words or variables were considered different, much like Java.


What are the adventages and disadventages of both solutions?

The second solution can not perform range scans.  It is great for selecting a single ID.  But what if you want ids 3 through 8?  It has to grab all the records individual which in the real world isn't just O(1) * 6 records to retrieve.  In a large, production database with a HashMap index you would get records on different pages, requiring you to hit the disk and read six different pages into memory.

In a B-Tree structure, like how your first situation would actually be implemented, the ids would be sequential on disk and a single page would likely hold ids 3 - 8 increasing the speed of range scans will making individual access O(log n).

Which one is more popular in current database implementations? Maybe different dbs use different approaches?

I don't have a huge experience in lots of different databases.  I know Sql Server uses B-Trees mostly, but SQl 2014 has some new Hash Indexes you can use on certain table.  I hear a lot of No Sql databases and caching databases built on retrieving individual records use hash indexes as well.  This makes sense for caches since you want the record for user A Page 11 and don't need range scans.

Does it exist in nonSQL dbs?
Yes.  Taking a quick look at the create index documentation for postgressql I see it supports both Hash and B-Tree indexes as well as a few others.


How to check sybase command line history?

Unless you have enabled history in isql, then you are out of luck.
Moving forward, you can enable it by following these directions:
Check http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html for details of --history parameter:

--history [p]history_length [--history_file history_filename] – Loads the contents of the command history log file, if it exists, when isql starts. By default, the command history feature is off. Use the --history command line option to activate it.

p – indicates command history persistence; in-memory command history is saved to disk when isql shuts down. If you do not use the p option, the command history log is deleted after its contents are loaded into memory.

history_length – this parameter, which is required if you use --history, is the number of commands that isql can store in the command history log. The maximum value of history_length is 1024; if a larger value is specified, isql silently truncates it to 1024.

history_file history_filename – indicates that isql must retrieve the command history log from history_filename. If p is specified, isql also uses history_filename to store the current session’s command history. history_filename can include an absolute or a relative path to the log file. A relative path is based on the current directory. If you do not indicate a path, the history log is saved in the current directory. When --history_file is not specified, isql uses the default log file in $HOME/.sybase/isql/isqlCmdHistory.log.


Why does my WHERE clause benefit from an "included" column?

Your Predicate is different to your Seek Predicate.

A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done.

But before the data is returned, it checks every row using the Predicate, which I refer to as the Residual Predicate. It's done on the results of the Seek Predicate.

Any included column is not part of the ordered data, but can be used to satisfy the Residual Predicate, without having to do the extra Lookup.

You can see material I've written on this around Sargability. Check for a session at SQLBits in particular, at http://bit.ly/Sargability

Edit: To show the impact of Residuals better, run the query using the undocumented OPTION (QUERYTRACEON 9130), which will separate out the Residual into a separate Filter operator (which is actually an earlier version of the plan before the residual gets moved into the Seek operator). It clearly shows the impact of an ineffective Seek, by the number of rows being passed left to the Filter.

It's also worth noting that because of the IN clause on ItemState, the data being passed left is actually in ItemState order, not in ItemPriority order. A composite index on ItemState followed by one of the dates (eg (ItemState, LastAccessTime)) could be used to have three Seeks (notice the Seek Predicate shows three seeks within the one Seek operator), each against two levels, producing data that is still in ItemState order (eg, ItemState=3 and LastAccessTime less than something, then ItemState=9 and LastAccessTime less than something, and then ItemState=10 and LastAccessTime less than something).

An index on (ItemState, LastAccesTime, CreationTime) would be no more useful than one on (ItemState, LastAccessTime) because the CreationTime level is only useful if your Seek is for a particular ItemState and LastAccessTime combination, not a range. Like how the phone book isn't in FirstName order if you are interested in Surnames beginning in F.

If you want a composite index but you are never going to be able to use the later columns in Seek Predicates because of the way you use the earlier columns, then you may as well have them as included columns, where they take less space in the index (because they're only stored at the leaf level of the index, not the higher levels) but can still avoid lookups and get used in Residual predicates.

As per the term Residual Predicate - that's my own term for this property of a Seek. A Merge Join explicitly calls it its equivalent a Residual Predicate, and the Hash Match calls its one a Probe Residual (which you might get from TSA if you match for hash). But in a Seek they just call it Predicate which makes it seem less bad than it is.


connect to sqlplus without writing as sysdba

$ alias sqs='sqlplus / as sysdba'
$ sqs

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 20:33:31 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>


Start->Run (Windows R if you prefer) SQLServerManager11.msc

That is the snapin for managing your SQL Server Instances.

Unless of course you mean SQL Server Management Studio which by default would be installed at "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"


MySQL: Merge Same Database on Several Location into Single Location

Let me first suggest an approach that may not work for you, but it seems the ideal approach for saving bandwidth and resources:

Multi-source replication is a relatively new feature, available on MariaDB 10 and MySQL 5.7 (not yet released). It is used precisely for the case you are talking about: merging data from different servers into one, typically for analytical purposes.

Here it is an overview of how it works for MariaDB 10 and for MySQL 5.7. If the tables have the same name, and you set the proper filters, you could have the table almost up-to-date in real time without having to do imports and exports every single time (which will become more and more inefficient each time). Even if you do not want that, it would be just easier to start and stop the replication each time, unless the tables are completely rewritten between synchronisations.

As writing to the same table could become insecure (for the data integrity)- depending on the kind of DML queries that you execute at each location-, you could alternatively write to separate tables and use a VIEW to see it as a single table. This may or may not be better for performance depending on the queries executed on the central location.

If this does not work for you (for example, because you cannot use those versions of MySQL- after all, this is a relatively new features), I can suggest alternative methods.

EDIT:

As you seem to not be able to use a stable internet connection (although that wouldn't be a problem, replication is asynchronous and it will work even if it stops very frequently), I will assume you want to do the transfer via a USB drive.

My second recommendation would be to backup the table or tables using CSV if the data is not too big, and in binary format if they are relatively big.

For CSV format, you can use mysqldump --tab or mydumper and then import them back with mysqlimport/myloader.

If CSV format is too slow, I would recommend you to use something like Percona XtraBackup that, combined with InnoDB, allows you to export tables in binary format and then reimport them separately (it requires Percona Server or MySQL 5.6 on the importing node and innodb_file_per_table). You can first import each table separately and then merge it into a single table as partitions with EXCHANGE PARTITION. That would allow you to do the export/import at nearly disk speed.


Can mutli-threading java client cause high CPU utilization of SQL Server 2008?

Potentially - yes. Especially on a totally underscaled database server. I would expect problems with 1000 connections, not with something as low as 50.

I would start investigating the SQL sent and see for optimizations there (using precompiled statements) to take load off. Also certain kinds of sub-optimal SQL can lead to significant CPU usage (due to table scans doing tons of processing instead of using an index). For that you need to find the resource intensive queries and analze them (or ask for help here).

Generally: CPU is normally not the limiting factor of a database server, IO etc. come first. But it is possible, to answer your question. Start analyzing.

Last time I had this issue on a large oracle we used 24 cores at nearly 100%, hitting the oracle server with close to 200 threads - and the CPU limitation came out to be an oracle configuration error limiting the database server to ONE CORE. That is how efficient databases are.

And obviously the java app may not even be taxed - if all it does is sending inefficient SQL to the sql server it has not a lot to do except to wait.


barman + postgresql not start after barman retore

It looks to me like you are trying to restore to a point in time that is earlier than what is recorded in the backups.

Make sure you have not specified a recovery-time that is earlier than the backup you are trying to restore.

Check the files recovery.conf and backup.label in the postgres data directory.
If you want to restore to the latest possible, then you should make sure you don't have a property named recovery_target_time in the recovery.conf.


How can I manipulate database in stack n-up order?

I'm not sure what the modulo function in Access is, but if I were to write an SQL query I'd use this to order the records:

...order by case mod(id,615) when 0 then 615 else mod(id, 615) end, id


Why is SQL Server 2008 R2 Management Studio making me login again?

Possible simple reasons:

Connection is broken between your PC and SQL Server (for example some Switch can be powered-down, re-started etc)
Connection can be closed from SQL-Server side if your admins make some kind of maintenance and simply kill connections, or if your Server is running in Virtualized envronment, it's instance could be "paused" to make a snapshot.

list of reasons can be really big

you can start analysing using simple tool: when you go home, just run:

ping -t NAMEOFYOURSERVER >c:\temp\log.txt

this will start permanent ping and when you will come next day - just Ctrl-Break and look into the file if there are Time-Outs. This way you will know if at least IP connection was alive.


Why would I need to update stats manually?

As the data in a table changes the statistics objects that represent the distribution of data become outdated. This is a problem because it's the statistics objects that the optimiser uses to make it's estimates when building execution plans. If these estimates are inaccurate your performance will suffer.

SQL Server does automatically update statistics objects when the amount of data in the table that has been changed hits certain thresholds, but if you have data that is changing frequently or tables with millions of rows you will better off performing regular index maintenance.

This can be done with maintenance plans or scripts. Ola Hallengren's suite of scripts is very good for this and something that I use in all of my environments. There is also the Minionware suite that I have not used personally but I hear good things.


Multi server administration: Job that runs both on master and targets servers?

You cannot do this unless you create another copy of the job under Local in the agent.

Details about doing this is given in this post.


sp_databaserestore: "stopat" gives error "no database backup found"

I don't think the problem is with sp_DatabaseRestore but there are alternatives as you can see on this question (includes the script):

How do I create an automated SQL Server restore script for multiple backups?

Before you run your restore:

1) you must check that all your backups exist in the server

2) you must check the total disk space required by the restored-to-be-database is available on your drives

3) who is currently using the database - you will need exclusive access to it, if it already exists

4) I also save synonyms, triggers, indexes and permissions before I do the restore, so that I can re-apply these things later on if I need to.

5) questions for you:
Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?

Also, look into the use of WITH RESTART for your restores...

The backup or restore was aborted - what could it be?


Pipelinedb: How to group stream data by each N minutes in continuous view


Mysql database offline-online synchronization

As per your requirement you're suggesting leads to this solution:

mysqlbinlog --start-datetime=<START_DATETIME> --stop-datetime=<STOP_DATETIME> binlog.xxx > binlog.xxx.sql

Though this is very much prone to corrupt your data and/or introduce inconsistency! Not recommended!

There can be multiple solutions to one problem, if you could share the scenario you would get a better solution.

For eg:

If you're planning to make application specific changes on development (local) server and want to push that to live, use version control to manage the SQLs and exec to live.
If it is only specific database, you can setup replication and only move replicate changes to that db (or even tables).


Outer Apply
You're using OUTER APPLY, but with a where clause that would reject NULL values.
It's converted to an inner join without the TOP (1):
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

I've formatted your code a little bit, and added an ORDER BY to validate results across queries. No offense.
Outer Apply + TOP (1)
When you use the TOP (1), the join is of the LEFT OUTER variety:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

The TOP (1) inside the OUTER APPLY apparently makes the optimizer unable to apply the same transformation to an inner join, even with a redundant predicate:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
    AND   v.DisplayName LIKE 'J%'
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Note the residual predicates to evaluate if the Id and DisplayName columns are NULL.
This isn't just a TOP (1) issue either -- you can substitute any values up to the big int max (9223372036854775807) and see the same plan.
It will also happen if you skip the view entirely.
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.Id,
        v.DisplayName,
        v.Reputation
    FROM dbo.Users AS v
    WHERE v.Reputation > 10000
    AND   v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id
OPTION(EXPAND VIEWS);

A Rewrite
One way to get the same effect as TOP (1) without the various optimizer side effects of TOP is to use ROW_NUMBER
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM
    (
        SELECT
            v.*,
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    v.Id
                ORDER BY
                    v.Id
            ) AS n
        FROM dbo.vwHighReputation AS v
    ) AS v
    WHERE v.Id = U.Id
    AND   v.n = 1
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Which will get you the original plan:


How do I get a list of all the partitioned tables and partitioning details in my Teradata?

dbc.PartitioningConstraintsV contains info about all partitioned tables.

SHOW TABLE mytable returns the CREATE TABLE statement for this particular table.


Oracle Data Guard - redo logs not in chronological order?

I simply do not believe. It's impossible. There is process called FAL (Fetch Archive Log), it can happen that redo-logs are not TRANSFERRED in chronological order, but anyway they must be APPLIED in chronological order.

Also logical standby has can have some gotchas but those are not related to network link.
(For example for some reason LOCK TABLE statement is not propagated to slave)


MySQL server parameters default empty string to null

There is no way to do that by changing a server parameter. NULL is different from an empty string so there is no reason to have that.

You could change your application to send NULL to your SQL statement or add a condition to your query.

INSERT INTO table (column1) values (IF(LENGTH('$variable')=0,NULL,'$variable'));


Postgis building with `make docs`, "cannot parse /html/docbook.xsl"

This is because you don't have docbook.xsl. On Ubuntu this is found in the docbook-xsl package, run

sudo apt-get install docbook-xsl
./configure
make docs


Backup weekly current production db to Historical db server

You can look into replication as well (not sure if it works on AWS though)

[Prod SQL] --> [Archive SQL] constantly

periodically delete on Prod: anything > 90 days
periodically delete on Archive: anything > 365 days maybe?

If not replication, does "linked server" work on AWS?

Not sure what you mean by "register"


I would avoid the replicate-do-db option due to the restriction on cross-database replication and users not fully qualifying a database name or not specifying "use dbname;" (it will execute on master but not replicate to slave).

Use this option instead (put it in my.cnf):

replicate-wild-do-table=dbname1.%

replicate-wild-do-table=dbname2.%

Or you can specify the tables, replace % with table name, one on each line.
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-wild-do-table

This link should help get you started:
http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-partitioning.html


If you want to have a computer be assigned to more than one user, you're going to need a junction table between the two to store the relationships. This was not stated in the requirements but it seems like a logical approach given the project scope.  1. Account 2. Computers 3. Account_Computer_Assignments.  The third one is your junction table that stores the relationship between the other two.  It's primary key would consist of IDAccounts and IDComputers using your naming convention.  A foreign key relating back to their parent tables would also be advisable.
This structure will also allow for multiple users to link to the same computer.  I don't know if that is the intended behavior but it will allow for that with this design.
If that is not intended, and a single computer can only be monitored by one and only one user, then a single field on the Computer table for the AccountID is all that is needed.  No junction table.


Presumably the Disp_id is a non-unique field as you'll have an order table which contains the customer name / delivery address etc

so you'll have an order has 1-+ dispatches
The product table will cant be a 1-1 relationship with dispatch otherwise only one person would ever be able to order that product regardless of how many they order so the product -> dispatch needs to be 1-+

looking at the information here this is less a table you want to create and more a View since all that information will be stored in other locations you dont want to duplicate things, you just want to derive the locations

A view based on the

[Orders]-> OrderID (Is the dispatch ID)
[Orders]-> Product ID -join-> [Products] -> ProductName
[Orders]-> Product ID -join-> [Products] -> Shelf Location
[Orders]-> ProductQuantity

(I used to work in a warehouse and thats how it used to work there and makes sense, and fuflls all the needs that I can see you wanting)

There's no reason to load all of the products, you want to just load the products of an order

Think of the Dispatch as part of the Full order, the warehouse staff only needs to know that this gets picked up from there and gets placed with other items of this dispatch ready to actually be dispatched

Hope that helps (it is based on my understanding of what I've read)
Ste


This is one of those exceptions where it's probably okay to execute a DBCC SHRINKFILE. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.

You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:

DBCC SHRINKFILE(yourdb_log, 4000);

This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...

If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using:

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'yourdb';


Restoring to specific point in time in SQL Server

The quick answer here is: What you are describing/insinuating is kind of a big deal, and I've not experienced this and this is not the default or expected behavior of SQL Server.

The ability to restore is a fundamental function of an RDBMS. The ability to restore to a point in time is a fundamental part of that fundamental function. And the underpinnings of this ability is the logging mechanism of a Relational Database Management System.

This is not only what gives us the ability to restore to a point in time, but the ability to recover to a consistent state during a crash or restart. It is the same transaction log that is used there.

In a nutshell (there are lot of resources that describe the process. This is a good one to start) - SQL Server uses a write-ahead-logging mechanism. This means everything is written to your transaction log and confirmed to be written there before that transaction is considered durable and your transaction can be considered done. If this didn't work, we could never trust the reliability of SQL Server and the data in your app, because data could become inconsistent with IO issues, memory issues, a restart, etc. We'd have no guarantee of maintaining the ACID properties of the database anymore.

So with that caveat in mind there is one of three things going on here:

An Error/Fault during the restore - I am guessing that is not the case here. Why? Because you were able to restore and query the database after your restore and you are not dealing with a corrupt database or restore failures.
A Bug - I am not saying this isn't possible, but I am skeptical that this is the case. Especially with your comment update indicating some uncertainty about what should be in the application log you are looking at.
A Misunderstanding - This is the most likely guess of what you are experiencing. You can confirm this a bit by doing the same process to a dev server or test server. Either the wrong database could be being restored, the table you are querying is not showing you the data you are expecting to see and not logging the data correctly or there weren't the transactions you were expecting to see.

You actually aren't doing transaction log backups/restores - I suppose there could also be some confusion in what you are doing. To be clear - to restore to a point in time you have to restore a Full backup with NORECOVERY, then apply the necessary transaction logs between your last full backup and the time you want to restore to, specifying a WITH STOPAT on the final transaction log restore and specifying WITH RECOVERY on that final one.. Also, if the time you want to go back to is after your last transaction log backup, you need to first backup the tail of the log one last time to be able to restore to that point in time. (This article talks about how to restore transaction log backups. This one talks about backing up the tail of the log.)

(**note:**I would also confirm time zone settings, the time zone on the server where the backups were taken is the time zone used in your restore as this question got into. So if you are restoring from one server to the other - check your time zones and all. )

One simple way you can prove this out?

Query the table you are looking at for logging after normal activity for a bit. Do you see what you are expecting to see based on your understanding for the activity today? Okay good, go through your normal restore process or the one indicated in the documentation linked to above if you've done something different. Do this restore to another machine/dev machine so you aren't affecting your primary server! Restore to the time you ran your query in this table. Look at your table - if you did the steps above correctly you should see that the data looks as it should. If it doesn't, I would first look at your process, re-read the instructions, confirm the database, confirm the time zones and times on the two servers, and if something is still off and you've totally ruled out something missing in the process? I would open a call immediately with Microsoft, but I'd honestly be surprised if you were experiencing a bug or issue.


ERROR: "4.9E-324" is out of range for type double precision

You're asking for software that was EOL in July 2014. It's far from supported. I'm not sure I trust git on those releases. The work transferring over the repo may have been sloppy. You may have to go back to SVN to get an authoritative answer but this seems to be responsible...

  On some platforms, strtod() reports ERANGE for a denormalized value (ie,
  one that can be represented as distinct from zero, but is too small to have
  full precision).  On others, it doesn't.  It seems better to try to accept
  these values consistently, so add a test to see if the result value
  indicates a true out-of-range condition.  This should be okay per Single
  Unix Spec.  On machines where the underlying math isn't IEEE standard, the
  behavior for such small numbers may not be very consistent, but then it
  wouldn't be anyway.

  Marti Raudsepp, after a proposal by Jeroen Vermeulen

You can see the thread about this here.


FullText Index on VARBINARY column: how to find string with dot

Sorry this might not be an answer but I cannot put this in comment.  Based on the comment by OP @Jefferson (as an answer to my comment) I could not reproduce the behavior.  I can get a record back with where clause exactly matching the value.

I tested this with following code (copied and modified from here)

CREATE TABLE Department
   (DepartmentID INT IDENTITY CONSTRAINT DepartmentPK PRIMARY KEY,
    NAME NVARCHAR(50),
    File_txt NVARCHAR(50));
GO
INSERT INTO Department (NAME, File_txt) VALUES ('OP1', 'CORP-MMM-456.245');
INSERT INTO Department (NAME, File_txt) VALUES ('OP2', 'CORP-MMM-456.246');
INSERT INTO Department (NAME, File_txt) VALUES ('OP3', 'CORP-MMM-456.247');
INSERT INTO Department (NAME, File_txt) VALUES ('OP4', 'CORP-MMM-456.245.246');

Go
CREATE FULLTEXT CATALOG ComplexFTS AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON Department(File_txt LANGUAGE 1033 /* 0 = Neutral, 1033 = American English  */)
KEY INDEX DepartmentPK WITH (STOPLIST = OFF); /* Or use (stoplist = Off) for no stoplist */
GO
WAITFOR DELAY '00:00:05';
GO
SELECT * FROM Department WHERE CONTAINS (File_txt,'"CORP-MMM-456.*"');
SELECT * FROM Department  WHERE CONTAINS(File_txt,'CORP-MMM-456.245')
GO

This is what I get as my result set.

Clean up code.

DROP FULLTEXT INDEX ON Department;
GO
DROP FULLTEXT CATALOG ComplexFTS;
GO
DROP TABLE Department;
GO


Combine - Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH


sp_who returns more rows than the value of "max degree of parallelism"

Shouldn't it be at most 8 rows in this case?

No, because a single execution plan can use more threads than specified by the effective degree of parallelism (DOP).

Only a maximum of DOP threads will be active on a scheduler at a given moment in time. SQL Server (2005 onward) limits a parallel query to DOP schedulers, not DOP threads.

Since a scheduler abstracts a logical processor, this effectively limits the parallelism as desired.

DOP threads are assigned to each parallel branch, where a branch is delimited by Parallelism (exchange) operators.

For more information, see my Simple Talk article:

Understanding and Using Parallelism in SQL Server

and/or my PASS Summit 2013 session recording:

Parallel Query Execution


Copy Database wizard fails but leaves no text file log

Please use backup / restore. The copy database wizard is a disappointing sack of, well, let's leave it at that.


MySQL Replication - replication issues; procedures registered on master not giving same result on slave in MySQL 5.5.20

You have to pay attention on you replica configuration, first of all it is a statement-based o row-based replica?

Probably you run an "unsafe" statement in you master server so the slave can't apply same statement in deterministic mode.

Check the master error.log for warnings like:

[Warning] Unsafe statement written to the binary
log using statement format since BINLOG_FORMAT = STATEMENT.

For more information: Determination of Safe and Unsafe Statements in Binary Logging


Filter mysql group by negative having contains

This is an anti-semijoin. Can be written with NOT IN, LEFT JOIN / WHERE IS NULL or NOT EXISTS:

select distinct ColA       -- if there is another table where ColA is unique
from test as a             -- use it here, instead of table test
                           -- and remove the distinct
where not exists
      ( select 1
        from test as b
        where b.ColB = 'zzz2'
          and b.ColA = a.ColA
      ) ;

You could also use GROUP BY with a CASE expression:

select ColA
from test
group by ColA
having count(case when ColB = 'zzz2' then 1 end) = 0 ;


Database Link from Oracle 10g on Windows over to MySQL database

This can be done by conneting to an ODBC data source through a dg4odbc listener.

There is a reasonable explanation available here:

http://hs2n.wordpress.com/2012/04/03/oracle-create-database-link-to-mysql-database/


Add shared folder to AlwaysOn AG Failover

The recommended way to do this is the way you don't want to do it--by creating a shared disk and the necessary cluster resources for a file share.

Other cluster options would require some sort of replication functionality.  Carbonite  Availability GeoCluster will allow you to create software replicated disk resource to accomplish this, and there are some other products that will do the same thing.  However, using third-party products introduces some risk because if there is a defect your cluster group may fail over or even fail to come online.  Accordingly, I personally would not opt for one of these solutions.


save/export PGAdmin server list configuration settings

Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here:

%your-user-home%\AppData\Roaming\pgAdmin\pgadmin4.db


Difference between star schema and data cube?

After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this answer satisfies me and help me to start the task. On the process, I hope I get better understanding (if exists) of these techniques. Here is my findings:

Difference between Star Schema and data cubes:

  Star schema is a dimensional modeling technique. It contains,
  Dimensions and Facts (business measurements). Mostly used in Data
  warehouse technology.

  Data cube is a multi-dimensional table. It means, combination of
  dimension and fact tables. Mostly used in OLAP analysis tools.

  Data cubes are built on the star schema to improve the query
  performance - performing aggregate and summarizing measurements.

  It will pre-calculate the values, instead of calculating on the fly,
  so it boosts performance.

  Example: Total item, Sales Amount, etc

Where I have to start?

I realized I have to start from, star schema and build data cubes on it. Data cubes are not built on the DBMS system, but outside the DBMS system as aggregates and other operations.

I hope this answer will help the people who are new to this technologies. If I am missing something or understanding wrongly, please correct it. Thank you.


Backup detects corruption, but CHECKDB does not

This answer is taken from an issue of the SQLskills.com newsletter written by Paul Randal, about "a database which would fail a backup with page checksum errors, but passed a DBCC CHECKDB".

  The only time this can happen is when an extent is a mixed extent
  (where the 8 pages in the extent can be allocated to potentially 8
  different allocations units – see here) and some pages are
  erroneously marked as allocated by the relevant PFS page.

  When that happens, DBCC CHECKDB will not attempt to read those pages,
  as it derives what pages to read from an allocation unit's IAM pages
  (the first of which lists the pages allocated from a mixed extent).
  This case is a gap in DBCC CHECKDB's corruption-detection logic.

  [Because] DBCC CHECKDB couldn't detect the corruption, it wasn't
  possible for it to make the repairs needed to fix them.
  So using DBCC WRITEPAGE, I worked out the changes needed in the allocation status
  for the erroneously-allocated pages, directly in the PFS page, and it
  worked!

  This was an extremely rare case – it's much more common that a DBCC CHECKDB
  fails but a backup would succeed.

In my opinion, Paul's resolution is way above and beyond exporting and importing the data like you did, so I think you did the right thing.


How to loop through tables dynamically and INSERT INTO a temporary table in MSSQL

If you want to avoid a union, you can loop using a cursor.

So, first you define your cursor that will list all the tables you want to query (let's assume you have all your data tables listed somewhere (system tables or config)).

And then loop doing :

create a dynamic SQL statement with the name of the table you retrieved
execute this statement

So something like:

declare @sql varchar(max)
declare @data_table varchar(50)
declare data  cursor for
    select data_table   -- name of the table you have to query
    from somewhere      -- where these table names are stored

open data
fetch next from data into @data_table

while (@@fetch_status = 0)
begin
    -- result_table = name of the table you want the results to be stored
    -- control_table = name of the control table you mentioned
    set @sql = 'insert into result_table
                select *
                from '+@data_table+'
                where AccountId in (select AccountId
                                    from control_table
                                    where Appear_In_View = 1)
                and Most_Recent_Data = 1'
    exec(@sql)
    fetch next from data into @data_table
end

close data
deallocate data

EDIT : with new inputs from Ariel Hayoun
Try this kind of where clause if (and only if) there is one line te retrieve:

set @sql = 'insert into result_table
            select *
            from '+@data_table+'
            where AccountId in (select AccountId
                                from control_table
                                where Appear_In_View = 1)
            and Retrieved_At = (select max(Retrieved At) from '+@data_table +')'

If you have multiple lines per tables (one line per account id for instance), you may be able to achieve what you want using row_number() function


SELECT COUNT of multiple column under the same table

The use of COALESCE() will do what you ask for very nicely, but I would also question if you might want to reconsider your schema. It looks like what you're doing is tracking individuals' membership in various groups (column_1 ... column_X).

You would probably fare far better to simply make a membership table with two columns: member_id and group_id. You could then more easily add/remove groups from the groups table and counting memberships would have been as simple as a COUNT() on rows matching the member_id in the membership table. This would also set you up better for varying types of members (additional field on membership). Just a thought on a better way to plan for future needs here.


how do i print a record coming from joinig query as follows in mysql

Use group_concat to merge multiple field rows into one field

create table A(a_id,a_name);
create table B(a_id,b_name varchar(5));
insert into A values(1,'john'),(2,'paul'),(3,'joy');
insert into B values(1,'x'),(1,'y'),(1,'z');

set group_concat_max_len = 4096; -- default: 1024

-- the query
select A.a_name, group_concat(B.b_name separator ',')
from A, B
where A.a_id = B.a_id
group by A.a_name;

-- result
+--------+--------------------------------------+
| a_name | group_concat(B.b_name separator ',') |
+--------+--------------------------------------+
| john   | x,y,z                                |
+--------+--------------------------------------+
1 row in set (0.00 sec)


Adding specific information to a local file

For a SQL Server solution, FILESTREAM (or FILETABLE if you can use SQL Server 2012) was built to solve this type of problem.

This solution will allow you to store the files in a central network location (most importantly, not on a web server box) outside the database, and manipulate metadata information within the database.

As mentioned in my comment, other RDBMS systems may also have similar capabilities (I'm not familiar enough with them to say, though).


Indexes over XML data (a.k.a. "XML Index") differ from relational indexes in several ways. They are used to identify documents and the position of the nodes within for the indexed pattern. Several nodes within a single XML document may match that pattern. For a relational index each row is indexed, for an XML index there might be zero, one or more matches per document (which is stored as part of a relational row). Some more information can be found in the DB2 documentation on Indexing XML Data.

By SQL Standard the XML data type is kind of a black box. It does not allow comparison and hence sorting. What you are trying to do is sort an XML fragment by a specific node. The fragment is already derived from the stored XML value/document. At the time of the processing there could be no connection between the XML index and the fragment. A good reference on XML indexes and their usage was written by Matthias Nicola.

With that background information we can now focus on your specific question. How would DB2 know that a timestamp/date value is present for each row and the related "Content"? How should the sorting be organized? Because of the index entry/document/row relationship outlined above the sorting in the SQL statement won't work.

For your XQuery I assume that the data is untyped, no schema is used. How would DB2 know that dateCreated is of timestamp and that the index should be used? Have you tried a cast around the dateCreated to let DB2 know that timestamp data is expected?

BTW: You can specify multiple XML indexes for the same pattern, but with different data types.


Calculating a running balance to store with transactions

Attemping an answer based on comments

The easiest and fastest way to do this is to take a set based approach. Like this:

1) Read NOW() into a variable @N

2) Load new data into Transactions

3) Update the Balance like this:

UPDATE Balance B
INNER JOIN (
  SELECT SUM(value) as value, member_id, product_id
  FROM Transactions
  WHERE date >= @N) AS T
ON B.member_id = T.member_id
AND B.product_id = T.product_id
SET B.balance = B.balance + T.value

4) Profit


Best configuration for MySQL? InnoDB and MyISAM

MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read and write simultaneously. this page has a lot on optimizing disk I/O of innoDB


Is is possible to combine local variables in a case statement?

Is there any reason you aren't just using a group by?

SELECT T0.[Project], T3.[Name] AS [Season],
     sum(T0.[LineTotal]) AS [Season Total]
FROM RDR1 T0
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
GROUP BY T0.[Project], T3.[Season];


Adding index - There is insufficient system memory in resource pool 'default' to run this query

The problem is that the amount of memory available for memory-optimized data is not a fixed size. It depends on system needs.

Please see my post here:
http://nedotter.com/archive/2018/01/in-memory-oltp-resources-part-4-oom-the-most-feared-acronym-in-all-of-in-memory-oltp/

Bottom line is that you need more memory.


SUM of DATALENGTHs not matching table size from sys.allocation_units

                          Please note that the following info is not intended to be a comprehensive description of how data pages are laid out, such that one can calculate the number of bytes used per any set of rows, as that is very complicated.

Data is not the only thing taking up space on an 8k data page:

There is reserved space. You are only allowed to use 8060 of the 8192 bytes (that's 132 bytes that were never yours in the first place):

Page header: This is exactly 96 bytes.
Slot array: this is 2 bytes per row and indicates the offset of where each row starts on the page. The size of this array is not limited to the remaining 36 bytes (132 - 96 = 36), else you would be effectively limited to only putting 18 rows max on a data page. This means that each row is 2 bytes larger than you think it is. This value is not included in the "record size" as reported by DBCC PAGE, which is why it is kept separate here instead of being included in the per-row info below.
Per-Row meta-data (including, but not limited to):

The size varies depending on the table definition (i.e. number of columns, variable-length or fixed-length, etc). Info taken from @PaulWhite's and @Aaron's comments that can be found in the discussion related to this answer and testing.
Row-header: 4 bytes, 2 of them denoting the record type, and the other two being an offset to the NULL Bitmap
Number of columns: 2 bytes
NULL Bitmap: which columns are currently NULL. 1 byte per each set of 8 columns. And for all columns, even the NOT NULL ones. Hence, minimum 1 byte.
Variable-length column offset array: 4 bytes minimum. 2 bytes to hold the number of variable-length columns, and then 2 bytes per each variable-length column to hold the offset to where it starts.
Versioning Info: 14 bytes (this will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON).

Please see the following Question and Answer for more details on this: Slot Array and Total Page Size
Please see the following blog post from Paul Randall which has several interesting details on how the data pages are laid out: Poking about with DBCC PAGE (Part 1 of ?)

LOB pointers for data that is not stored in row. So that would account for DATALENGTH + pointer_size. But these are not of a standard size. Please see the following blog post for details on this complex topic: What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc?. Between that linked post and some additional testing I have done, the (default) rules should be as follows:

Legacy / deprecated LOB types that nobody should be using anymore as of SQL Server 2005 (TEXT, NTEXT, and IMAGE):

By default, always store their data on LOB pages and always use a 16-byte pointer to LOB storage.
IF sp_tableoption was used to set the text in row option, then:

if there is space on the page to store the value, and value is not greater than the max in-row size (configurable range of 24 - 7000 bytes with a default of 256), then it will be stored in-row,
else it will be a 16-byte pointer.

For the newer LOB types introduced in SQL Server 2005 (VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)):

By default:

If the value is not greater than 8000 bytes, and there is room on the page, then it will be stored in-row.
Inline Root — for data between 8001 and 40,000 (really 42,000) bytes, space permitting, there will be 1 to 5 pointers (24 - 72 bytes) IN ROW that point directly to the LOB page(s). 24 bytes for the initial 8k LOB page, and 12 bytes per each additional 8k page for up to four more 8k pages.
TEXT_TREE — for data over 42,000 bytes, or if the 1 to 5 pointers can't fit in-row, then there will be just a 24 byte pointer to the starting page of a list of pointers to the LOB pages (i.e. the "text_tree" page).

IF sp_tableoption was used to set the large value types out of row option, then always use a 16-byte pointer to LOB storage.

I said "default" rules because I did not test in-row values against the impact of certain features such as Data Compression, Column-level Encryption, Transparent Data Encryption, Always Encrypted, etc.

LOB overflow pages: If a value is 10k, then that will require 1 full 8k page of overflow, and then part of a 2nd page. If no other data can take up the remaining space (or is even allowed to, I am unsure of that rule), then you have approx 6kb of "wasted" space on that 2nd LOB overflow datapage.
Unused space: An 8k data page is just that: 8192 bytes. It does not vary in size. The data and meta-data placed on it, however, does not always fit nicely into all 8192 bytes. And rows cannot be split onto multiple data pages. So if you have 100 bytes remaining but no row (or no row that would fit in that location, depending on several factors) can fit there, the data page is still taking up 8192 bytes, and your 2nd query is only counting the number of data pages. You can find this value in two places (just keep in mind that some portion of this value is some amount of that reserved space):

DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS; Look for ParentObject = "PAGE HEADER:" and Field = "m_freeCnt". The Value field is the number of unused bytes.
SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id; This is the same value as reported by "m_freeCnt". This is easier than DBCC since it can get many pages, but also requires that the pages have been read into the buffer pool in the first place.

Space reserved by FILLFACTOR < 100. Newly created pages do not respect the FILLFACTOR setting, but doing a REBUILD will reserve that space on each data page. The idea behind the reserved space is that it will be used by non-sequential inserts and/or updates that expand the size of rows on the page already, due to variable length columns being updated with slightly more data (but not enough to cause a page-split). But you could easily reserve space on data pages that would naturally never get new rows and never have the existing rows updated, or at least not updated in a way that would increase the size of the row.
Page-Splits (fragmentation): Needing to add a row to a location that has no room for the row will cause a page split. In this case, approx 50% of the existing data is moved to a new page and the new row is added to one of the 2 pages. But you now have a bit more free space that is not accounted for by DATALENGTH calculations.
Rows marked for deletion. When you delete rows, they are not always immediately removed from the data page. If they cannot be removed immediately, they are "marked for death" (Steven Segal reference) and will be physically removed later by the ghost cleanup process (I believe that is the name). However, these might not be relevant to this particular Question.
Ghost pages? Not sure if that is the proper term, but sometimes data pages do not get removed until a REBUILD of the Clustered Index is done. That would also account for more pages than DATALENGTH would add up to. This generally should not happen, but I have run into it once, several years ago.
SPARSE columns: Sparse columns save space (mostly for fixed-length datatypes) in tables where a large % of the rows are NULL for one or more columns. The SPARSE option makes the NULL value type up 0 bytes (instead of the normal fixed-length amount, such as 4 bytes for an INT), but, non-NULL values each take up an additional 4 bytes for fixed-length types and a variable amount for variable-length types. The issue here is that DATALENGTH does not include the extra 4 bytes for non-NULL values in a SPARSE column, so those 4 bytes need to be added back in. You can check to see if there are any SPARSE columns via:

SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName],
       OBJECT_NAME(sc.[object_id]) AS [TableName],
       sc.name AS [ColumnName]
FROM   sys.columns sc
WHERE  sc.is_sparse = 1;

And then for each SPARSE column, update the original query to use:

SUM(DATALENGTH(FieldN) + 4)

Please note that the calculation above to add in a standard 4 bytes is a bit simplistic as it only works for fixed-length types. AND, there is additional meta-data per row (from what I can tell so far) that reduces the space available for data, simply by having at least one SPARSE column. For more details, please see the MSDN page for Use Sparse Columns.
Index and other (e.g. IAM, PFS, GAM, SGAM, etc) pages: these are not "data" pages in terms of user data. These will inflate the total size of the table. If using SQL Server 2012 or newer, you can use the sys.dm_db_database_page_allocations Dynamic Management Function (DMF) to see the page types (earlier versions of SQL Server can use DBCC IND(0, N'dbo.table_name', 0);):

SELECT *
FROM   sys.dm_db_database_page_allocations(
               DB_ID(),
               OBJECT_ID(N'dbo.table_name'),
               1,
               NULL,
               N'DETAILED'
              )
WHERE  page_type = 1; -- DATA_PAGE

Neither the DBCC IND nor sys.dm_db_database_page_allocations (with that WHERE clause) will report any Index pages, and only the DBCC IND will report at least one IAM page.
DATA_COMPRESSION: If you have ROW or PAGE Compression enabled on the Clustered Index or Heap, then you can forget about most of what has been mentioned so far. The 96 byte Page Header, 2 bytes-per-row Slot Array, and 14 bytes-per-row Versioning Info are still there, but the physical representation of the data becomes highly complex (much more so than what has already been mentioned when Compression is not being used). For example, with Row Compression, SQL Server attempts to use the smallest possible container to fit each column, per each row. So if you have a BIGINT column which would otherwise (assuming SPARSE is also not enabled) always take up 8 bytes, if the value is between -128 and 127 (i.e. signed 8-bit integer) then it will use just 1 byte, and if the value could fit into a SMALLINT, it will only take up 2 bytes. Integer types that are either NULL or 0 take up no space and are simply indicated as being NULL or "empty" (i.e. 0) in an array mapping out the columns. And there are many, many other rules. Have Unicode data (NCHAR, NVARCHAR(1 - 4000), but not NVARCHAR(MAX), even if stored in-row)? Unicode Compression was added in SQL Server 2008 R2, but there is no way to predict the outcome of the "compressed" value in all situations without doing the actual compression given the complexity of the rules.

So really, your second query, while more accurate in terms of total physical space taken up on disk, is only really accurate upon doing a REBUILD of the Clustered Index. And after that, you still need to account for any FILLFACTOR setting below 100. And even then there are always page headers, and often enough some amount of "wasted" space that is simply not fillable due to being too small to fit any row in this table, or at least the row that logically should go in that slot.

Regarding the accuracy of the 2nd query in determining "data usage", it seems most fair to back-out the Page Header bytes since they are not data usage: they are cost-of-business overhead. If there is 1 row on a data page and that row is just a TINYINT, then that 1 byte still required that the data page existed and hence the 96 bytes of the header. Should that 1 department get charged for the entire data page? If that data page is then filled up by Department #2, would they evenly split that "overhead" cost or pay proportionally? Seems easiest to just back it out. In which case, using a value of 8 to multiply against number of pages is too high. How about:

-- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes.
SELECT 8060.0 / 1024 -- 7.906250

Hence, use something like:

(SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]

for all calculations against "number_of_pages" columns.

AND, considering that using DATALENGTH per each field cannot return the per-row meta-data, that should be added to your per-table query where you get the DATALENGTH per each field, filtering on each "department":

Record Type and offset to NULL Bitmap: 4 bytes
Column Count: 2 bytes
Slot Array: 2 bytes (not included in "record size" but still need to account for)
NULL Bitmap: 1 byte per every 8 columns (for all columns)
Row Versioning: 14 bytes (if database has either ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT set to ON)
Variable-length column Offset Array: 0 bytes if all columns are fixed-length. If any columns are variable-length, then 2 bytes, plus 2 bytes per each of only the variable-length columns.
LOB pointers: this part is very imprecise since there won't be a pointer if the value is NULL, and if the value fits on the row then it can be much smaller or much larger than the pointer, and if the value is stored off-row, then the size of the pointer might depend on how much data there is. However, since we just want an estimate (i.e. "swag"), it seems like 24 bytes is a good value to use (well, as good as any other ;-). This is per-each MAX field.

Hence, use something like:

In general (row header + number of columns + slot array + NULL bitmap):

([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))

In general (auto-detect if "version info" is present):

+ (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1
                 THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())

IF there are any variable-length columns, then add:

+ 2 + (2 * {NumVariableLengthColumns})

IF there are any MAX / LOB columns, then add:

+ (24 * {NumLobColumns})

In general:

)) AS [MetaDataBytes]

This isn't exact, and again will not work if you have Row or Page Compression enabled on the Heap or Clustered Index, but should definitely get you closer.

UPDATE Regarding the 15% Difference Mystery

We (myself included) were so focused on thinking about how data pages are laid out and how DATALENGTH might account for things that we didn't spend a lot of time reviewing the 2nd query. I ran that query against a single table and then compared those values to what was being reported by sys.dm_db_database_page_allocations and they were not the same values for the number of pages. On a hunch, I removed the aggregate functions and GROUP BY, and replaced the SELECT list with a.*, '---' AS [---], p.*. And then it became clear: people must be careful where on these murky interwebs they get their info and scripts from ;-). The 2nd query posted in the Question is not exactly correct, especially for this particular Question.

Minor problem: outside of it not making much sense to GROUP BY rows (and not have that column in an aggregate function), the JOIN between sys.allocation_units and sys.partitions isn't technically correct. There are 3 types of Allocation Units, and one of them should JOIN to a different field. Quite often partition_id and hobt_id are the same, so there might never be a problem, but sometimes those two fields do have different values.
Major problem: the query uses the used_pages field. That field covers all types of pages: Data, Index, IAM, etc, tc. There is another, more appropriate field to use when concerned with only the actual data: data_pages.

I adapted the 2nd query in the Question with the above items in mind, and using the data page size that backs out the page header. I also removed two JOINs that were unnecessary: sys.schemas (replaced with call to SCHEMA_NAME()), and sys.indexes (the Clustered Index is always index_id = 1 and we have index_id in sys.partitions).

SELECT  SCHEMA_NAME(st.[schema_id]) AS [SchemaName],
        st.[name] AS [TableName],
        SUM(sp.[rows]) AS [RowCount],
        (SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB],
        (SUM(CASE sau.[type]
           WHEN 1 THEN sau.[data_pages]
           ELSE (sau.[used_pages] - 1) -- back out the IAM page
         END) * 7.91) / 1024 AS [TotalActualDataMB]
FROM        sys.tables st
INNER JOIN  sys.partitions sp
        ON  sp.[object_id] = st.[object_id]
INNER JOIN  sys.allocation_units sau
        ON  (   sau.[type] = 1
            AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
        OR  (   sau.[type] = 2
            AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
        OR  (   sau.[type] = 3
            AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE       st.is_ms_shipped = 0
--AND         sp.[object_id] = OBJECT_ID(N'dbo.table_name')
AND         sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap
GROUP BY    SCHEMA_NAME(st.[schema_id]), st.[name]
ORDER BY    [TotalSpaceMB] DESC;


Script file execution through Power shell

Assuming your or a user for which you have credentials has applicable permissions, it is very easy to run scripts into SQL Server.  The simplest method is to use Invoke-Sqlcmd.  You can set the -Query parameter to a hard coded value like

Invoke-Sqlcmd -Query "SELECT name FROM sys.databases" -ServerInstance "YourSQLInstance"

Or you can set a variable equal to the value of a .txt or .sql script, using the Get-Content cmdlet.

$sqlQuery= Get-Content -Path "c:\Scripts\MySQLScript.sql"

Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Using this method would allow you to create more complicated scripts.  If you tokenized your USE statement, you could do a simple replacement of the value, after you've captured it.

$sqlQuery.replace("xxxDBNamexxx", "YourDBName")

Putting all this together, you could query your instance and capture the required databases, loop through the databases - for each DB read in the required sql script and replace the DB token with the current DB, execute the script.

Logging is another matter, but not too difficult, depending on the method you use. The easiest, might be to just capture the results of the Invoke-Sqlcmd to another variable and write the variable.

$sqlResults=Invoke-Sqlcmd -Query $sqlQuery -ServerInstance "YourSQLInstance"

Add-Content -Path "c:\Logs\sqlLog.txt" -Value $sqlResults

These are overly simplistic examples intended to point you in the right direction.  Someone with more experience might use more advanced methods.  It might be worth your while to spend a few hours with the Microsoft Virtual Academy or some other online resource to pick up some basic Power Shell.  It's all I've had time for, but even the basics can get you a long way.


Does MongoDB produce a single file like sqlite does for an individual database? Can it?

As at MongoDB 3.4, all of the standard storage engines (WiredTiger, MMAPv1, ..) create multiple files in the dbPath. For example, in WiredTiger there are a number of metadata files, journal files, and a file per collection and per index (see: Why is an empty MongoDB database so big?). Multiple files generally allow more granular concurrency and storage management; single-file databases are more common for embedded use cases.

The file and directory layout is dependent on the storage engine and server configuration options, so it is conceivable that someone could develop a new MongoDB storage engine which has a more concise file layout (but I'm not aware of anyone having done so).

Irrespective of the layout on disk, you could use the standard mongodump utility to create a single compressed & archived backup of a database to copy and restore on another system using mongorestore. The archive feature is available in MongoDB 3.2 or newer. For more information, see Archiving and Compression in MongoDB Tools.


ERROR: collation "database_default" for encoding "UTF8" does not exist

database_default is a SQL Server-specific collation that explicitly tells SQL Server to use the database's default collation, overriding the default collation precedence behavior. This is used in a COLLATE clause most typically to avoid collation conflict errors that arise when using temp tables in scenarios where tempdb's default collation differs from the current database's default collation and string columns are being joined or concatenated.
The Java code would need to be edited to remove the COLLATE DATABASE_DEFAULT from the query. Though if this app is coded for Microsoft SQL Server, will it work in PostgreSQL even if this one part is fixed? There are other SQL syntax and datatype differences between the RDBMSs.
For more information on migrating from SQL Server to PostgreSQL, please see:
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server
However, if you are dealing with a 3rd party app that has embedded T-SQL, then you might be forced to contact the software vendor to ask about the potential for PostgreSQL support.


I have managed to find one solution to this, although it's not as neat as I would have preferred. I've created a second job that checks the last run date on my target job. This is scheduled to run daily, a couple of hours after the target job should have run.

If not exists
(
    SELECT 'x'
    FROM msdb.dbo.SysJobServers S
        INNER JOIN msdb.dbo.SysJobs J ON S.job_id = J.job_id
    WHERE J.name = 'JobName'
        AND CONVERT(datetime2, CONVERT(VARCHAR(20), last_run_date)) > GETDATE() -1
)
BEGIN
    RAISERROR ('The Job has not been executed today.', 16, 0)
END


Trying to import a large sql file using BigDump. Partial fail occuring

I finally found the actual problem! It had nothing to do with how I was importing it, it was my local server's export that failed halfway through!

After opening up VIM and going to the end of the file, I found: Fatal error: Maximum execution time of 300 seconds exceeded...

Ahhhh, the problem was with my local server's timeout, so it only exported a partial amount of the data.


A simple test with a little over 1M values

Data

CREATE TABLE dbo.FOO([A] int , [B] int,[C] int, [D] int, [E] int);

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
VALUES(1,1,1,1,1);
GO 1000

INSERT INTO dbo.FOO
SELECT * FROM dbo.FOO;
GO 10

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
VALUES(1,2,1,1,1); --one matching record

This dataset is not a real world dataset, as aside from one record, all the other records are the same.

Amount of rows

select COUNT(*) from dbo.FOO
   (No column name)
1024001

Query

Select A, B, C, D, E from dbo.FOO where A = 1 and B = 2;

No key lookup

Which is normal as this index is perfect considering the query and the dataset.

key column <> included column

The main difference between the key columns and included columns is that
these key columns are ordered [A] --> [B] (B-Tree) and the other columns [C], [D], [E] are not ordered (leaf level).

As a result seeking only on [A] is possible, seeking on [AB] is possible, but seeking only on [B] will create a residual predicate if the index is used.

Additionally

You cannot add a column as both the key column and the included
column
Some datatypes cannot be added to the key column but could be included (all datatypes except text, ntext, and image)

More info

Example of a key lookup happening as a result of a different query

create Nonclustered index IX_Foo_2 on dbo.FOO ([B]) include ([C], [D], [E]);

Select A, B, C, D, E from dbo.FOO where  B = 2;

A key lookup to get [A] happens, but it is not very expensive (one row).


How do I change the MySQL data folder path in a Mac?

First shutdown mysql

mysqladmin -uroot -p shutdown

Then, launch mysqld by hand

mysqld --defaults-file=...

or if MySQL is running with all defaults and you are just changing datadir, do this

mysqld --datadir=/path/to/mysql/data

Here is a StackOverflow question (MySQL 'my.cnf' location?) with multiple suggestions on where my.cnf might be for Mac OS when installed .


Index on column with data type citext not used

Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ...

Like you commented, the actual reason is burried in collation support.

Either way, citext or text, you can make it work with an expression index:

CREATE INDEX people_name_idx ON people (lower(name) text_pattern_ops);

And a corresponding query:

SELECT id, name FROM people WHERE lower(name) LIKE 'abc%';

Note that lower(name) returns data type text, even when feeding it citext.

Alternatively, you could use a trigram index, which is more expensive to maintain, but also offers more capabilities:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Aside: your test case is suboptimal because your dummy values are all lower case to begin with and the pattern 'a%' is often not selective enough to use an index at all. And char(1000) doesn't make sense (even though irrelevant to the test).


How can I make this nested query more efficient?

I mocked up data by looking at the row counts in your tables, giving them an even data distribution, and making guesses about the schema:

DROP TABLE IF EXISTS [Room];

CREATE TABLE [Room] (
    [Id] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Room] WITH (TABLOCK)
SELECT TOP (3088) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 200)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS [Conference];

CREATE TABLE [Conference] (
    [Id] BIGINT NOT NULL,
    [Name] VARCHAR(30) NOT NULL,
    [RoomId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Conference] WITH (TABLOCK)
SELECT RN
, 'MY FAVORITE MEETING ROOM'
, 1 + RN % 3088
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (97413) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS [Participant];

CREATE TABLE [Participant] (
    [Id] BIGINT NOT NULL,
    [ConferenceId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Participant] WITH (TABLOCK)
SELECT RN
, 1 + RN % 97413
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (235323) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CREATE INDEX NCI_Part ON [Participant] ([ConferenceId]) INCLUDE (Id);

The most important assumption that I made about the schema is that the Id column is the primary key of the [Conference] table. This seemed reasonable given the query plan and the index names involved.

On my machine I get the same query plan as you, but my starting query only takes 163 ms of CPU. I assume the differences come down to differences in hardware, data distribution, and the fact that I'm not returning data to the client.

The first thing that jumped out to me is the unnecessary GROUP BY in your confData derived table. Id is the primary key of the table so you don't need all of the aggregates. With the right indexes (which you already have for this particular case), subqueries aren't necessarily a bad thing. Rewriting what you have to remove the GROUP BY:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT COUNT(part.[Id])
        FROM [Participant] part
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

This results in the stream aggregate getting pushed down further into the plan:

The uploaded plan takes 113 ms of CPU. The same operators are present, but some of them process fewer rows which saves time. You may be able to make this query more efficient by defining a covering index on the [Conference] with Id as the index key. This may seem like an odd thing to do, but your clustered index scan takes 10% of the overall query time and likely includes columns that you don't need.

If you want to make the query faster you could also consider an indexed view. Why perform the aggregation every time when you can define a simple indexed view to do it for you?

CREATE VIEW IndexedViewOnParticipant WITH SCHEMABINDING
AS
SELECT [ConferenceId], COUNT_BIG([Id]) CntId, COUNT_BIG(*) Cnt
FROM dbo.[Participant]
GROUP BY [ConferenceId];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON IndexedViewOnParticipant ([ConferenceId]);

This will result in a little more space and a little bit of overhead when doing DML on the table. Overall I'd say that it's a good use case for an indexed view. Rewriting the query again:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT CntId
        FROM IndexedViewOnParticipant part WITH (NOEXPAND)
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

SQL Server agrees with my assessment that it's a good idea and CPU time falls to 78 ms.

On my machine I was able to make the query even faster, but this is starting to get into optimizations that are somewhat risky because it may require a LOOP JOIN hint. That hint may not be a good idea as your query or the data in the table changes. It also may not be a good fit for your hardware. The idea behind this approach is to create a suitable index on [Conference] and to take full advantage of the TOP with a plan that only does nested loops. Here is the index that I added:

CREATE INDEX NCI_Conf ON [Conference] ([RoomId]) INCLUDE ([Name]);

Running the same query as before with a LOOP JOIN hint gave me the following plan:

That query only took 58 ms of CPU time. It's worth mentioning that I noticed requesting the actual plan adds quite a bit of relative overhead at this stage. All of the other possible optimizations that come to mind to me aren't safe for production so I'll stop here.

As a final thought, do you really want to return 1000 arbitrary rows and the minimum conference name? Is that information useful to your end users?


Unfortunately there isn't any Change Data Capture, Change Tracking (both coming only from SQL 2008 +) or even enough Trace Events or Trace Event Groups for Use with Event Notifications.

You're stuck with server side traces for saving all the user queries.


Joining two tables in one has a subquery which is returning id count present in one table which is not presented on another field of a particular user

As far as I understand, a simple inner join on the foreign key/primary key pair should suffice?

Assuming that ad_analytics.ad_id refers to inventory.id (consider using foreign key constraints!) and by "name" you mean name_of_campaign I have something like

select t.id,
  t.ad_id,
  sum(t.impression) total_impression,
  sum(t.view) total_views,
  sum(t.clicks) total_clicks,
  t.publisher_id,
  i.budget,
  i.name_of_campaign
from
  (select id,
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  group by ad_id, visitor_ip) t
  inner join inventory i
             on i.id = t.ad_id
group by t.ad_id;

in mind.

Note, that I had to introduce using the aliases as a prefix in the list of selected columns, as id would have been ambiguous otherwise (it's in both tables).


Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to innodb_flush_log_at_trx_commit=0, however that may not be the right choice for you - see the previous link.

In any case, COMMITs don't usualy stall due to other activity on the server (like queries), unless that other activity locks some kind of mutex for far too long.

If I had to guess without any further evidence, I would say that your writes are so intensive, that MySQL is unable to flush fast enough using adaptive checkpointing, so it exceeds 75% of the log capacity and starts sharp checkpointing (which blocks server activity for a short period).

There are few quick ways to make MySQL handle writes better (besides one already mentioned above):

Increase innodb log files (innodb_log_file_size) - you can go up to 4GB or so for your logs (esimating based on zero information given on the amount of writes),
Increase pIOPS capacity for the data drive
Increase innodb_io_capacity (up to ~70-80% of pIOPS capacity)

Otherwise it would be really useful to see disk activity from AWS, amount of data server currently writes (you can calculate it using Log sequence number delta between two consecutive show engine innodb status\G runs)

There are other ways too, but for that, I'd need a lot more details on the actual workload.


I try to stay away from having multiple databases in one instance. Instead I have multiple instances set up on a server where each one is dedicated to a database.

MySQL Cacti templates are a good monitoring solution. If you are enterprise customer then MySQL Enterprise Monitor works. Also check out mycheckpoint from openark.

EDIT
Here are some tutorials for setting up multiple instances on one server:

Running multiple instances of MySQL on the same machine
How-to: Setup Multiple MySQL Instances

You could also use MySQL Sandbox as it automates a lot of different set ups for you.


PostgreSQL: Cannot change directory to /root

Try this: Re: could not change directory to "/root":

  Apparently you did "su postgres" from the root account, so you're
  still in root's home directory.  It'd be better to do "su - postgres"
  to ensure you've acquired all of the postgres account's environment.
  Reading "man su" might help you out here.


Why can't I create a database in a 107GB dbspace I created for my instance?

How did you create the dbspace ? I'm a bit astonished that you showed up a directory listing instead of the output from onstat -d - just creating a file does not do the job.
Run onstat -d and see if there is a dbspace called "datadbs". If not, cd to the directory where the dbspace should reside, create a suitable file (i. e. echo gaga >datadbs content does NOT matter) and create the dbspace using "onspaces". This should look similar to

onspaces -c -d datadbs -p c:\dbspaces\datadbs -o 0 -s 128000000

For more options enter onspaces -?

Hope this will help


Why my application is blocked infinitely and SQL Server never timeout and kill the deadlock

Deadlocks and blocking locks are two different concepts that you need to understand.

A deadlock is a situation where process/action 1 is waiting for process/action 2 to finish and at the same time process/action 2 is waiting for process/action 1 to finish. In other words. They would wait forever since they are waiting on each other.

In your scenario, something else is happening:

Process 1 is doing an action and has taken a lock on a resource to complete that action, Process 2 now wants to start a action that requires a lock on the same resource. Process 2 now has to wait for process 1 to complete and the lock is released. The key here is that at any given moment, none of the processes are waiting for each other (at the same time). One process is just waiting for the other process to finish an action on the same resource. They are not waiting for each other.

I hope that's clear.

On to how we fix your issue:

Can you post the Table definition, the indexes on the table and the delete select statement. We could have a look to see if there are ways to make the likelyhood of blocking locks less.