Possible to get equivalent MySQL bin log position on another server?

I managed to do this by examining the log files using mysqlbinlog.

Given the following master log position on mysql2:

Master log file = mysql-bin.003151
Master log pos  = 46678888

I opened the log file:

mysql2> mysqlbinlog mysql-bin.003151 | less

And searched for the position (46678888).

# at 46678861
#170301 12:15:33 server id 2  end_log_pos 46678888
  Xid = 12652204449 COMMIT/*!*/;
# at 46678888
#170301 12:15:36 server id 2  end_log_pos 46678967
  Query   thread_id=414919 69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46678967

The nearby line of SET TIMESTAMP=1488370536 looked like a good candidate to search for in the corresponding log on mysql1. So I opened up the master log file on mysql1 (there were multiple to choose from, but I went with the one with the closest timestamp to that on the slave, you might have to try a few files):

mysql1> mysqlbinlog mysql-bin.002966 | less

And searched for SET TIMESTAMP=1488370536. This is what I found:

# at 46236234
#170301 12:15:33 server id 2  end_log_pos 46236261
  Xid = 88972150 COMMIT/*!*/;
# at 46236261
#170301 12:15:36 server id 2  end_log_pos 46236325
  Query   thread_id=414919    69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46236325

I checked surrounding lines to ensure I'd got the right place and then used the corresponding end_log_pos (in this case 46236261) as the new log position. Therefore my master log position on mysql1 is:

Master log file = mysql-bin.002966
Master log pos  = 46236261

It's probably possible to automate this process somewhat, but to be honest it was very quick and easy to do it manually (and I don't expect to have to do again much if at all) - it's taken longer to write up the process than actually do it.


Install MariaDB 10 on Ubuntu without prompt and no root password

In the link that Dimitar provided, the question itself hints at a solution - if you're putting this in a script, you could add the SET PASSWORD line with an empty password.

export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password password PASS'
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password_again password PASS'
sudo apt-get install -y mariadb-server
mysql -uroot -pPASS -e "SET PASSWORD = PASSWORD('');"

That said, I recommend that you keep using passwords, even for dev environments. As suggested on a related serverfault question, you could add lines to your my.cnf which contain the password, meaning that you can still simply fire up mysql by typing 'mysql':

[client]
user = root
password = s3kr1t


How to open a sharded MongoDB (without replica set) environment on my dev machine?

You can have one member replica set as a minimum. You will also need config server to keep track of shards.

Reference: https://docs.mongodb.com/manual/core/sharded-cluster-components/#development-configuration

  For testing and development, you can deploy a sharded cluster with a
  minimum number of components. These non-production clusters have the
  following components:

  A replica set config server with one member. At least one shard as a
  single-member replica set. One mongos instance.

WARNING:
Use the test cluster architecture for testing and development only.


In SQL Server Mgmt Studio, why don't triggers automatically get updated when tables get renamed?

Trying the following in SSMS..

CREATE TABLE [dbo].[T]
  (
     X INT
  );

GO

CREATE TRIGGER [dbo].[TR]
ON [dbo].[T]
FOR INSERT
AS
    SELECT *
    FROM   [dbo].[T]

Then renaming the table as T2 and scripting out the trigger returns the following so I assume you are referring to the second table reference.

CREATE TRIGGER [dbo].[TR]
ON [dbo].[T2]
FOR INSERT
AS
    SELECT *
    FROM   [dbo].[T]

If you use SQL Server Data Tools to manage your database schema instead of making schema changes directly in SSMS then this does have a rename refactoring. It isn't 100% comprehensive as you would still need to review for any possible usages in dynamic SQL or references to the table in external scripts but it would fix the issue above.


One way of doing this on a low level basis is by reading the USER_SOURCE for the corresponding package. In PL/SQL you could loop over the lines and add each line to an array (/table) of type DBMS_SQL.VARCHAR2A, lets call this variable 'sqlTable'.

The first line of your sqlTable contains the package name, with a little String manipulation you should be able to change the name from 'PACKAGE_NAME' to 'SCHEMA'.'PACKAGE_NAME'.

Then using DBMS_SQL package perform the following steps:

cur = DBMS_SQL.OPEN_CURSOR; -- cur is of type pls_integer
DBMS_SQL.PARSE(cur, sqlTable, sqlTable.first, sqlTable.last, false, DBMS_SQL.NATIVE);
DBMS_SQL.execute(cur)

Another way is to spool the output of the USER_SOURCE to a file, connect to the other schema and load / execute the file there. See this.


Improving MySQL query performance for a large database

count(i.BrandId) checks BrandId for being not null before counting.  If you don't need that check, change to COUNT(*) to count the number of rows in the JOIN.  Or perhaps you wanted COUNT(DISTINCT i.BrandId) to count the number of different brands?

Then these indexes will help:

project:  INDEX(Date)
projectitem:  INDEX(projectid)           -- if using COUNT(*)
projectitem:  INDEX(projectid, BrandId)  -- if explicitly COUNTing on BrandId

When you dataset gets bigger than RAM, the GUIDs will kill performance due to the random accesses blowing out the cache.

Be aware that any change, even a minor change, to those queries may necessitate reevaluation of the indexes.


What is the best way to add optional update in procedure in transaction?

The optimizer uses the sniffed parameter values regardless of your procedural logic. Imagine your proc code with only the DML statements and the parameters in it.
So there is a risk that when the plan is generated, the values to be used for the UPDATE are nonsense values when @IsUpdateA = 0. From a performance perspective, that is. Think of the WHERE clause and selectivity. I.e., the plan is created for that UPDATE (which isn't executed) is based on values that give you incorrect selectivity calculation and as a result a bad plan.
Or the other way around, of course.
I.e., having two procs can give you better estimates and in the end better plans.


Mongodb: how to move a member to a backup site?

Where you found that information that replica should have even number of members, because it is ODD number. With even number of members (votes) you could end up situation that you don't have majority. IF you have even number of data nodes, you need arbiter as fifth.

OK, to the question how to move one member. Let's assume that you have three nodes (primary + 2x secondary). If you want to be sure that your replica set stays up even you lost one member during that time that when one secondary is "on-the-move" and not up'n'running, bring one Arbiter to replica set. You can install arbiter to "any" other server, because it will not take resources (basically). You give command rs.addArb("address:port") just before you shutdown that secondary. After you start again that secondary at new location, you give command rs.remove("address:port"). That way you always have three votes online.


Any Issues, concerns on having a Stored Procedure in Subscriber Database

Subscriber can have different set of objects than publisher. Its perfectly fine to have a stored procedure on subscriber ONLY.

@Tara is correct, it wont drop the proc at the subscriber when you reinitialize the publication.

The only time that I think you would have to remember to recreate the SP is when you have to Initialize a Transactional Subscription from a Backup of publisher .


MSSQL efficient paging including a join on big tables

You didn't describe the problem very precisely so I made some guesses about table structures and data volume. I put 3 million rows in both tables:

CREATE TABLE dbo.ASSORTMENT (
    ID_ROW BIGINT NOT NULL,
    clientID INT NOT NULL,
    productID BIGINT NOT NULL,
    FILLER VARCHAR(2000) NOT NULL,
    PRIMARY KEY (ID_ROW)
);

INSERT INTO dbo.ASSORTMENT WITH (TABLOCK)
SELECT t.RN, 30 + t.RN % 15, t.RN, REPLICATE('Z', 2000)
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t (RN)
OPTION (MAXDOP 1);

CREATE TABLE dbo.PRODUCTS (
    productID BIGINT NOT NULL,
    FILLER VARCHAR(2000) NOT NULL,
    PRIMARY KEY (productID)
);

INSERT INTO dbo.PRODUCTS WITH (TABLOCK)
SELECT t.RN, REPLICATE('Z', 2000)
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t (RN)
OPTION (MAXDOP 1);

Getting the first thousand rows is fast. It only requires 15 milliseconds of cpu time on my machine:

SELECT *
FROM dbo.ASSORTMENT a
INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
OPTION (MAXDOP 1);

If I change the OFFSET to 50000 rows the query plan looks the same but the query now takes 1700 ms of CPU time on my machine:

Why is the query so much slower? ORDER BY with an OFFSET requires SQL Server to scan past the unwanted data. For my data set SQL Server SQL server reads 764993 rows from ASSORTMENT before finding the 51k rows that it needs. It also does 51k index seeks to PRODUCTS before returning the 1000 rows that you requested.

Let's take a step back. Do you really need to select all columns from both tables? Your question said that the join to PRODUCTS is only there for data integrity, so why do you need all columns from that table? Unnecessary SELECT * is a bad habit which is best avoided. It would make a great New Year's resolution!

One standard solution would be to only select the columns that you need and to make covering indexes on the two tables. I'm going to assume that you absolutely need all columns from both tables. In that case you want the query optimizer to do the scan over as little data as possible, but in terms of number of rows and number of columns. To make that possible I'll create the following indexes:

CREATE INDEX IX_ASSORTMENT ON dbo.ASSORTMENT (clientID, ID_ROW) INCLUDE (productID);

CREATE INDEX IX_PRODUCTS ON dbo.PRODUCTS (productID);

A query to get just the clustered indexes of both tables now just takes 63 ms of CPU time on my machine:

SELECT a.ID_ROW, p.productID
FROM dbo.ASSORTMENT a
INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
ORDER BY a.ID_ROW OFFSET 50000 ROWS FETCH NEXT 1000 ROWS ONLY
OPTION (MAXDOP 1);

The big differences are that SQL Server doesn't have to read rows for which ProductId is not 38 and it doesn't need to read data pages that contain unnecessary columns. Adding all of the columns back into the query bumps up the CPU time requirement to 80 ms but that's still better than a 20X improvement than before:

SELECT a.*, p.*
FROM
(
    SELECT a.ID_ROW, p.productID
    FROM dbo.ASSORTMENT a
    INNER JOIN dbo.PRODUCTS p on a.clientID = 38 and a.productID = p.productID
    ORDER BY a.ID_ROW OFFSET 50000 ROWS FETCH NEXT 1000 ROWS ONLY
) t
LEFT OUTER JOIN dbo.ASSORTMENT a ON t.ID_ROW = a.ID_ROW
LEFT OUTER JOIN dbo.PRODUCTS p ON t.productID = p.productID
OPTION (MAXDOP 1);

If you need the query to be faster than that you can consider using an anchored solution as described here.


Can't see Create Database in Analysis Services SQL Server Management Studio

For what it's worth, this might have to do with the mode, in which SQL Server Analysis Services is installed. If it is installed in the Tabular mode (the default), then the New Database... menu item is not available. If Multidimensional and Data Mining mode is selected during the installation, then the New Database... menu item becomes available.
A much more experienced co-worker helped me to figure this out when I was struggling with the same problem.


Group records with same value in one row informix

You are actually looking for PIVOT TABLE which seems to not be possible in Informix (I think, just by googling it, I don't know Informix). The CASE workaround, presentet e.g. here would mean that you would need a special CASE for each date... which seems pretty ugly (but you can do it).

What I would recommend, is to query the table in the "tall format" (also called "wide") and do the pivoting (wide format) transformation in the programming language, from which you call the database from. For example, I use R programmiing language a lot, and in R, I use the dcast or acast or cast function from reshape2 library to reshape the data. Just google it for your language:

  how to make pivot table in [[whatever-programming-language-you-use]]

or

  convert long table to wide table in [[whatever-programming-language-you-use]]


How should I configure these disks on a SQL Server for a BI configuration?

2 * 10k RAID1 for OS, 6 * 15k RAID10 for everything else. Honestly, with this many disks 1 array is the safest and usually fastest bet.

If you've got time to test and have a real world, repeatable, measurable workload then by all means do a test run with your tempdb on the OS drive (caveat: limit tempdb file growth to ensure you don't splat the OS). Flip side, you may see moderate improvements to your data load and maintenance with the log there instead so worth a test run or two if time permits.


Seems you don't have to use IIF or CASE

(
    (
    Production.Product_Title IS NULL
    OR Production.Product_Title Like 'Process%'
    )
AND Person.Type IS NOT NULL
)

FYI, Transact-SQL has IIF function starting with 2012.

UPD

If you had to return some value:

SELECT
    CASE WHEN
        Production.Product_Title IS NULL
        OR Production.Product_Title Like 'Process%'
    THEN 'Not Started'
    ELSE 'Started'
    END AS [ProductState]
FROM YourTable


moving binary database folder is causing issues with innodb tables

You cannot copy a database folder that has InnoDB tables because InnoDB storage engine maintains a symbiotic relationship between the InnoDB tables' physical files (frm and .ibd) and the data dictionary.

The data dictionary is inside the system tablespace file known as ibdata1.

You cannot copy a data folder and expect it to be independent of ibdata1.

SHOW TABLES; works because it only scans for .frm files and does not access metadata.

You could do one of two things

mysqldump the database and load the mysqldump into the target server
Run FLUSH TABLES;, disconnect the .ibd files from ibdata1 before copying to the other server. Then, reattach the .ibd files on the target server

Recover MySQL database from data folder without ibdata1 from ibd files
Other Posts

GIVE IT A TRY !!!


The restore from full/diff nightly is actually a good option, because it kills two birds with one stone: you get the Data Warehouse and you're also regularly testing your backups.

The typical method to populate a data warehouse from production is to use an ETL (Extract-Translate-Load) technology, like SQL Server Integration Services (SSIS). SSIS packages can be created in Visual Studio or simple ones can be created with the Import and Export Wizard. Now with 20 TB you obviously can't copy all the data every night - even a 10G connection probably couldn't keep up with that. But if you can copy most of the data over once and then incrementally load the changed/updated data every night, that should work. (The Generate Scripts wizard could help you replicate the table structures if necessary.)

Because you're running Enterprise edition on the source instance, you have several options for finding what's changed on a daily/nightly basis. Your best option would be Change Data Capture (aka CDC). The little brother of CDC is Change Tracking, which is less powerful but also a little easier to work with. If you're lucky enough that most or all of the tables have reliable UpdateDate columns, then that will work as well.

There would be challenges in keeping the structure of your source and Data Warehouse tables in sync, but you should be able to mitigate that by making sure you push changes to the Data Warehouse databases when you push them to the production database. If the Data Warehouse is just a copy of the production stuff, then you should be able to just run the same release package/scripts.

I realize this may seem daunting, but once you've got it set up, it should mostly run itself.


selecting records that are 3 days from expiring datetime

If date_created is a DATE, do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` = DATE(NOW() - INTERVAL 27 DAY);

If date_created is DATETIME or TIMESTAMP and the time part is '00:00:00', do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` >= DATE(NOW() - INTERVAL 27 DAY) + INTERVAL 0 SECOND;

These queries are better in terms of performance than the one in the question because the WHERE clauses of my queries will induce an index range scan, whereas the WHERE clause in the question will induce a full table scan (even if date_created is indexed).

If the jobs table does not have an index on date_created, then make one

ALTER TABLE jobs ADD INDEX (date_created);

Give it a Try !!


MySQL: How to decrease sleep process's time out?

to check your idle process timeout you can connect to MySQL then execute the following command:

show variables like "%timeout%";

you need to check wait_timeout and interactive_timeout from the results based on those values you can execute the following commands from MySQL to set them:

set global wait_timeout=XXX; -- where XXX equal value less than the original one
set global interactive_timeout=XXX;  -- where XXX equal value less than the original one


Create a hierarchy chain (reverse path) for each node in a tree

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg

CREATE TABLE #heredity
(
    parent  INT NOT NULL,
    child   INT NOT NULL,
    depth   INT NOT NULL,

CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child )
);
GO

INSERT INTO #heredity ( parent, child, depth )
VALUES
    ( 1, 1, 0 ),
    ( 2, 2, 0 ),
    ( 3, 3, 0 ),
    ( 1, 2, 1 ),
    ( 2, 3, 1 ),
    ( 1, 3, 2 );
GO

SELECT
    child,
    STUFF(
    (
    SELECT ',' + CAST( parent AS VARCHAR(20) )
    FROM #heredity p
    WHERE c.parent = p.child
    ORDER BY parent DESC
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(100)'),
    1, 1,'') AS [path]
FROM #heredity c
WHERE depth = 0;

My results:


Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

I modified example 1 so that the syntax is accepted by all vendors that I tried. It turns out that the only DBMS of the tested ones that reject the scenario is Db2 DB<>Fiddle:

MariaDB 10.2, 10.3 Yes
MySQL 5.6, 5.7, 8.0 Yes
Postgres 11 Yes

Oracle 11g release 2, 18 Yes
SQLServer 2017 Yes
Db2 V11 No

Note that the foreign keys have to be slightly modified for Oracle and SQLServer. See links provided by Dinesh Kumar

Oracle
SQLServer

Db2 throws an exception like:

SQL20255N  FOREIGN KEY .. is not valid because it would cause a descendent table ... to be delete-connected to its ancestor table ... through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints ... and ... on the descendent table. Reason code = "3".  SQLSTATE=42915 SQLCODE=-20255

I skimmed through 7IWD2-02-Foundation-2011-12.pdf which can be download from:

http://www.wiscorp.com/sql20nn.zip

but I did not find anything mentioned regarding this.

To me, it seems as if Db2 behave sanely in this regard, but that's just my opinion.


Index for ORDER BY on aggregate in MySQL

I have one suggestion, and if that doesn't work, then you're probably going to have to denormalize it.  There's no way to optimize a sort of the result of an aggregate function, because the entire set has to be calculated and sorted by the ORDER BY before a LIMIT ... OFFSET can be applied.

The database is theoretically free to return any of the possible valid rows to satisfy the LIMIT ... OFFSET when there's no explicit ordering in the query.  Some would argue that the existence of OFFSET isn't even a legitimate concept in a purist sense of the relational model, since tuples aren't ordered in a relation... but I digress.

You're limited on how an index can help you, primarily because you're doing GROUP BY one thing followed by ORDER BY something different -- you're already twisting the data in two directions, so even if the query were less complicated but had these two conflicting requirements, you'd have this same problem.

To explain your EXPLAIN (you might want to add the headers from the explain, to your question).

type index this is the first potentially-confusing piece of info; this is called the join type, and index here doesn't mean the query is using an index in the way you might expect; in fact, it has two different meanings, both of which are essentially full table scans with more or less help from an index, but not for lookups.  In this case, since Extra doesn't say Using index, the optimizer has chosen 'index_leads_on_user_id' (as shown in key) so that it can do the full table scan in index order -- reading the rows from the table in the order that the rows appear in that index... so it's reading from your table in "group by" order with the help of that index (on user_id).

possible_keys are the keys the optimizer found potentially viable for finding the records.  It notices both "index_leads_on_promo_id_and_user_id" and "index_leads_on_promo_id" because "promo_id" is the leftmost column in each of those indexes, so they could have been used to filter out only the 3 values for promo_id that you needed.  The optimizer, though, has abandoned this route, having apparently determined that the ability to read the rows in GROUP BY order would be more efficient than plucking out the matching rows and then having to sort the result for the purpose of grouping.

key is the index MySQL actually selected, for reasons I mentioned above.  Since the selected one couldn't be used to filter rows, it's not listed among possible_keys.

key_len 4 is number of bytes from the left side of the selected index that the optimizer will actually consider.  With a multi-column index, the first non-useful column counting from the left elimiates the use of any columns to the right; for a single column index on an INT, here, all 4 bytes are used.

ref NULL indicates that no column or value is being compared to the selected key.

extra: Using where because the WHERE clause will have to be used to remove all of the rows because we're doing a full table scan that will return more rows than are needed; Using temporary because a temporary table is needed to hold the GROUP BY result so that it can be sorted into a different order; Using filesort is how the server will store all of the useful records not filtered by the where clause.

The bottom line here is the that query seems about as well-optimized as it can be.

Also, the query itself is also not entirely logically valid.  It's syntactically correct enough that MySQL will run it, but it's ambiguous and may be causing extra work for the server and it could be distracting the optimizer.

SELECT *, SUM(entries_count) AS entries_count FROM leads
...
ORDER BY entries_count DESC

You can't SELECT * ... which includes a column called entries_count ... and then another column that you're aliasing AS entries_count... and then ORDER BY the ambiguous name entries_count and expect to get an unambiguous result.

Tested on MySQL 5.1 and 5.5, I find that this query sorts by a random value from the physical column and not by the SUM() result, and returns two different columns, both called "entries_count" in the result set, which means, at minimum, the server is having to manipulate extra data that you can't use.  This is not valid and you should definitely try explicitly listing the columns you want, excluding the physical column entries_count, which is going to be a meaningless value in light of the GROUP BY, and then, keep it unambiguous at the end:

ORDER BY SUM(entries_count);


How big can innodb_doublewrite_file grow?

Please look at the following view of the InnoDB/XtraDB Architecture

Please notice the Percona Server Documentation on This

  Performance Impact of the Doublewrite Buffer

  In usual workloads the performance impact is low-5% or so. As a
  consequence, you should always enable the doublewrite buffer because
  the strong guarantee against data corruption is worth the small
  performance drop.

  But if you experience a heavy workload, especially if your data does
  not fit in the buffer pool, the writes in the doublewrite buffer will
  compete against the random reads to access the disk. In this case, you
  can see a sharp performance drop compared to the same workload without
  the doublewrite buffer-a 30% performance degradation is not uncommon.

  Another case when you can see a big performance impact is when the
  doublewrite buffer is full. Then new writes must wait until entries in
  the doublewrite buffer are freed.

  How to Choose a Good Location for the Doublewrite Buffer

  Basically if you want to improve the I/O activity, you will put the
  doublewrite buffer on a different disk. But is it better on an SSD or
  a more traditional HDD? First you should note that pages are written
  in a circular fashion in the doublewrite buffer and only read on
  recovery. So the doublewrite buffer performs mostly sequential writes
  and a few sequential reads. Second HDDs are very good at sequential
  write if a write cache is enabled, which is not the case of SSDs.
  Therefore you should choose a fast HDD if you want to see performance
  benefits from this option. For instance, you could place the redo logs
  (also written in a sequential way) and the doublewrite buffer on the
  same disk.

What can we learn from this ?

The doublewrite buffer is a circular buffer. It is subject to pending writes when it is full.
The fact that the doublewrite buffer can get full proves that growth is never an issue.

CAVEAT

Looking at the InnoDB/XtraDB Architecture, it says that the Undo Logs can grow unllimitedly. That is the source of uncontrolled growth in the system tablespace.

Your Actual Question

  How big can grow innodb_doublewrite_file? How to calculate optimal disk size, if it would be used only for this file?

Nothing in the Documentation states a way to set a size or know its limitations. It only recommends placing the doublewrite file on a fast HDD.

CONJECTURE (OK I am guessing at this point)

If there is no limit on the doublewrite buffer size, then the number of blocks that make up all the data and index pages would be the maximum size. You would run this query:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT SUM(data_length+index_length) ibbytes
FROM information_schema.tables WHERE engine = 'InnoDB') A;

These numbers assume that every block of data and index for InnoDB has a place in the doublewrite buffer to go to.

A more realistic number may be this:

SELECT
    ibbytes InnoDBSpace,
    ibbytes/POWER(1024,1) InnoDBSpaceKB,
    ibbytes/POWER(1024,2) InnoDBSpaceMB,
    ibbytes/POWER(1024,3) InnoDBSpaceGB,
    ibbytes/POWER(2,14)   InnoDBBlocks
FROM (SELECT (variable_value * POWER(2,14)) ibbytes
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data');

These numbers are going by the actual working set in the InnoDB Buffer Pool

I hope this little explanation helped you !!!


PostGIS trigger is fired without error, but the action seems not executed

In a BEFORE INSERT trigger, the new row has not yet been inserted into the table. So when the query searches for points.id with the new value, it will not find it.

And there is no need to read the points table because you want to access only the new row, and all those values are available in NEW:

      SELECT
         NEW.id AS point_id,
         schema.lines.id AS line_id,
         schema.lines.geom <-> NEW.geom AS dist,
         ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
      FROM schema.line
      ORDER BY dist
      LIMIT 1

You need to use a BEFORE trigger to be able to modify the row before it is written into the table.

For a nearest-neighbour search, better use the <-> operator, which is able to use a geometric index on lines.geom.


Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.

In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).

You could also define your MV with a NEXT clause, for example this will refresh the MV every hour:

CREATE MATERIALIZED VIEW  C_INFO
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid
on demand
NEXT sysdate + 1/24


Are there any downsides to referencing tables from another schema using synonyms?

There is no performance issue in having an app access tables from multiple schemas.  You are correct in that you do not want to duplicate data across multiple schemas.  As for managing the privileges, grant the privs to a role, not the individual user.  Then grant the role to the user(s).  When new objects are created, grant the appropriate privs to the role, and any user to which the role was previously granted will pick up the new privs in any future sessions they create.


No, they will not do the same thing.

The amount of effort required by the SQL engine is completely different.  In the first query, the engine must go through every row and perform a string replace operation on the Name column.  In the second query, it is searching through the table where the name is "Jeff" and just updating the Name column to be Joe.
String replacement is wildcard.  So in the first query, the Name "Jeffrey" would become "Joerey".


get number of events per day from profiler trace

Try:

GROUP BY cast(StartTime as DATE)
ORDER BY cast(StartTime as DATE);

Grouping by the raw, unconverted value means you are guaranteed to have a row for every unique StartTime. (And it probably makes sense to sort that way, otherwise your rows could come back in an arbitrary and unintuitive order.)


Joining tables with one to many relationship

You can use a cte to get the latest change and then join it with the ticket table.

DECLARE @Table1 TABLE (Ticket_Number int NOT NULL, created Datetime)
DECLARE @Table2 TABLE (Ticket_Number int NOT NULL, statuschange Datetime)

INSERT INTO @Table1 (Ticket_Number, created)
  VALUES (1234, '2014-12-06 8:00:00'),
         (1235, '2014-12-08 8:00:00'),
         (1236, '2014-12-08 8:00:00');

INSERT INTO @Table2 (Ticket_Number, statuschange)
  VALUES (1234, '2014-12-06 8:30:00'),
         (1234, '2014-12-06 8:45:00'),
         (1234, '2014-12-06 10:00:00'),
         (1235, '2014-12-08 9:30:00'),
         (1235, '2014-12-08 8:45:00'),
         (1235, '2014-12-08 8:22:00');

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange,
          ROW_NUMBER() OVER (PARTITION BY Ticket_Number ORDER BY statuschange desc) AS RowNum
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE, t1.created, t2.statuschange) AS differnece_minutes
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
WHERE t2.RowNum = 1

This results in:

Ticket_Number ticket_started          last_change             differnece_minutes
------------- ----------------------- ----------------------- ------------------
1234          2014-12-06 08:00:00.000 2014-12-06 10:00:00.000 120
1235          2014-12-08 08:00:00.000 2014-12-08 09:30:00.000 90

If you need the time between changes you can extend the cte in this way:

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE,
                 t1.created,
                 t2.statuschange) AS minutes_since_create,
        DATEDIFF(MINUTE,
                 LEAD(t2.statuschange, 1, t1.created) OVER(PARTITION BY t2.Ticket_Number ORDER BY t2.statuschange desc),
                 t2.statuschange) as minutes_since_last_step
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
ORDER BY t1.Ticket_Number, last_change

That gives you the following output:

Ticket_Number ticket_started      last_change         minutes_create minutes_last_step
------------- ------------------- ------------------- -------------- -----------------
1234          2014-12-06 08:00:00 2014-12-06 08:30:00 30             30
1234          2014-12-06 08:00:00 2014-12-06 08:45:00 45             15
1234          2014-12-06 08:00:00 2014-12-06 10:00:00 120            75
1235          2014-12-08 08:00:00 2014-12-08 08:22:00 22             22
1235          2014-12-08 08:00:00 2014-12-08 08:45:00 45             23
1235          2014-12-08 08:00:00 2014-12-08 09:30:00 90             45

Please note that the LEAD() function is not available for the SQL Server 2008. This function is included from SQL Server 2012 onwards.

Lothar


MySQL stops in windows VM and shows innodb os error in logs(error no. 5)

Akina: Log tells that mysqld does not have the access rights to the directory. Adjust the access rights in server's filesystem for the account which is used for mysqld starting.


Would the query be right?

No. It would find any combination of given cities and postcodes (like  ('Smithfield', '2163'). This Cartesian product would become increasingly expensive and nonsensical for more combinations in one query.

And the query itself is needlessly expensive and confusing on top of that. LEFT JOIN is misplaced and joins would not be needed.

The shortest, cleanest and fastest way to match on multiple rows (combination of multiple column values) is to join to a VALUES expression (which is an ad-hoc table expression):

SELECT id, city, company, postcode
FROM   clients
JOIN  (
   VALUES
   ('Lansdowne' , '2163')
 , ('Villawood' , '2163')
 , ('Smithfield', '2164')
   ) v(city, postcode) USING (city, postcode);

For a big table, to make this fast, you should have an index on (city, postcode) or vice versa or at least an index with these two as leading columns.


MySQL - Migrating from Master-Slave replication to Master-Master

We had wanted to make this change in order to minimize the necessary changes to infrastructure, keeping us from needing to raise a galera cluster and attempting an online switch to the new infrastructure.

However, we have decided to implement the necessary resources to raise a mysql cluster and leave the old replication structure. It seems like there is no real alternative.

Thank you for your comments.


Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application?

I believe you're problem is similar to one I've faced a few years ago, while trying to design a multi-tenant web application with a single underlying database schema.
There's an interesting article by AWS that can point you in the right direction.
The core of it is:
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id::TEXT = current_user);

There are several different approaches, but the one I ended following is this:

Use a different database user for each connection coming from your desktop PCs. This should match your RLGroupId, if I understood correctly.
Apply a Policy to every table where data segregation by RLGroupId should be enforced

Hope this helps.


Rewrite query to not use CONCAT function in SQL Server 2008 R2?

You can concatenate strings in SQL Server just by using the + operator. So to avoid using CONCAT, your code can be rewritten as:

SELECT  dbname = DB_NAME(),
                 file_name = name,
                 TotalMB = CONVERT(decimal(12,1),size/128.0),
                 UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                 FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                 Command = 'USE MASTER; ALTER DATABASE ' + DB_NAME() + '; MODIFY FILE (name = ' +
                    [name] + ', size = ' + convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)) + 'MB)'
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc = 'ROWS'
ORDER BY file_id;


RIGHT() works in a SELECT but not in an UPDATE?

why is it failing in the UPDATE but not the SELECT...FROM?

Because in your SELECT you are filtering out the rows via WHERE #ReleaseTemp.ReleaseNotes <> ''. You do not have that filter on your UPDATE statement and you probably have at least one row that has a length of 0 or 1. Try making it:

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2))
WHERE LEN(#ReleaseTemp.ReleaseNotes) > 1;

Rather than checking for <> '', I changed the filter to ensure that the length of the string is at least 2 characters. This avoids the situation in which the string has only one character which does not equate to an empty string '' but would still get that error when passing in a -1 (from length of 1 minus 2) to the RIGHT function.

Please note that I included the LTRIM() which you currently have as a second step in order to avoid that second DML operation.


You can't make a single index that supports all of those optional parameters. You can make multiple indexes that do their best to support the most common combinations of parameters, but to support all possible combinations, the overhead is probably not worth it (and you'd have to measure the impact to the write portion of your workload - indexes aren't free).

My solution to the "customer can search in any way they please" problem is to create the kitchen sink procedure, which uses dynamic SQL to construct the where clause depending on the parameters provided. What this does is allow SQL Server to compile a separate plan for each combination of parameters, so that you don't have one massive query that tries to optimize for all scenarios but really can only optimize for very few. The basic approach would be:

CREATE PROCEDURE dbo.KitchenSink
  @param1 datatype = NULL,
  @param2 datatype = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table
    WHERE 1 = 1'

    + CASE WHEN @param1 IS NULL THEN N'' ELSE
        N' AND column1 = @param1' END

    + CASE WHEN @param2 IS NULL THEN N'' ELSE
        N' AND column2 = @param2' END

    ...
  ;

  EXEC sys.sp_executesql @sql, N'@param1 datatype, @param2 datatype, ...',
         @param1, @param2, ...;
END
GO

This will provide you with the opportunity to make the best use of any indexes that have key columns that align with combinations of supplied parameters. You will have to concede that some queries will just not perform well, but this should help minimize those cases.

Additional caveats:

You can use OPTION (RECOMPILE) inside the dynamic SQL to thwart parameter sniffing (for example, even when only lastname is supplied, the dynamic SQL alone won't help you generate the best plan every time, since a search for LIKE '%x%' will require a different plan than LIKE '[a-m]%').
You should use the optimize for ad hoc workloads setting (see here and here), so that SQL Server doesn't waste valuable plan cache space on potentially complex and large plans that are only used once.


contrib is correct, but you must have installed it for a different PostgreSQL than the one you are using.

Find out which packages are installed:

rpm -qa postgresql\*

Odds are that you need to run

dnf install postgresql11-contrib


As per the help section on their website, they describe Connection Time as the following:

  Connection Time: Displays the time taken to get connected to the SQL database server.

From that brief description, I'd be willing to say that it is the time duration between initiating the connection to the SQL Server instance.

If this is the case, then the impacts here would be the physical network latency and the server capabilities for a client response.  For further investigation into this metric, I'd consult the actual vendor for more details and acceptable statistics.


The documentation you referenced says that:

  A global trace flag must be enabled globally. Otherwise, the trace
  flag has no effect. We recommend that you enable global trace flags at
  startup, by using the -T command line option. This ensures the trace
  flag remains active after a server restart. Restart SQL Server for the
  trace flag to take effect.

and

  Using the DBCC TRACEON and DBCC TRACEOFF commands. For example, to
  enable the 2528 trace flag globally, use DBCC TRACEON with the -1
  argument: DBCC TRACEON (2528, -1). The effect of enabling a global
  trace flag with DBCC TRACEON is lost on server restart. To turn off a
  global trace flag, use DBCC TRACEOFF with the -1 argument.

And on the definition of flag 1800 it says:

  Enables SQL Server optimization when disks of different sector sizes
  are used for primary and secondary replica log files, in SQL Server
  Always On and Log Shipping environments. This trace flag is only
  required to be enabled on SQL Server instances with transaction log
  file residing on disk with sector size of 512 bytes. It is not
  required to be enabled on disk with 4k sector sizes. For more
  information, see this Microsoft Support article.

  Note: This trace flag applies to SQL Server 2012 (11.x) SP1 CU13, SQL
  Server 2012 (11.x) SP2 CU3, SQL Server 2014 (12.x) RTM CU5, and higher
  builds.

  Scope: global only

That means a restart is required depending on the configuration method you used: if by DBCC TRACEON, no restart required. If by -T, a restart is required.

Since you don't have a maintenance window to restart the server now by using the -T recommended option, you could use DBCC TRACEON to activate it emediately and also configure the -T on the service properties without rebooting SQL Server. That way when the server reboots next time the flag configured by DBCC TRACEON will be lost, but the option -T will take effect.


SQL server 2016/2017 in terms of better performance

For version, 2017.  Later versions have additional features and improvements, so for performance always prefer the latest version.

Enterprise Edition can scale up to larger machines:

and has more sophisticated High Availability and Disaster Recovery features.  But they have the same query optimizer and support the same programability features.

In general large databases, and databases with very demanding throughput requirements may perform better on Enterprise Edition, but smaller databases on smaller servers will perform the same.

See generally Editions and supported features for all of the differences.


Extracting Data from multiple archive tables

This is a pretty good case for Distributed Partitioned Views.

It would be fairly easy to implement, especially if the archives have the same schema.


Why select holds exclusive lock on table?

There are data modification statements within the same transaction.

indexname = "IX_Batch_BatchGroupId_Status"
id = "lock2aa71e32d80"
mode = "X"
associatedObjectId = "72057594166968320"

Exclusive locks associated with those data modifications will be held until the transaction is complete.

See the Deadlock Troubleshooting posts by Bart Duncan.


Unable to understand correlation between MaxWorkers created and CPU utilization

Correlation between MaxWorkers created and CPU utilization
I strongly recommend you to read the Thread and Task Architecture Guide doc. I'll quote some relevant parts:
About the higher number of workers and the relation between maxworkers and CPU:

On a very busy SQL Server Database Engine, it's possible to see a
number of active tasks that's over the limit set by reserved threads.
These tasks can belong to a branch that is not being used anymore and
are in a transient state, waiting for cleanup.

About the blocks you're experiencing:

If you suspect that there is a performance problem, it is probably not
the availability of worker threads. The cause is more likely something
like I/O that is causing the worker threads to wait.

About the CPU consumption not going beyond 30% while having requests blocking each other:

When a worker's quantum expires and is no longer active, the
respective task is placed in a FIFO queue in a RUNNABLE state, until
it moves to a RUNNING state again, assuming the task won't require
access to resources that are not available at the moment, such as a
latch or lock, in which case the task would be placed in a SUSPENDED
state instead of RUNNABLE, until such time those resources are
available.

And here's the list of states a worker can be (from sys.dm_os_workers). Unless it's on the RUNNING state, it's not using the CPU, therefore, you could have way more requests existing at the same time than the 1472 you expected to see if they're not in fact using the CPU.

Worker state. Can be one of the following values:
INIT = Worker is currently being initialized.
RUNNING = Worker is currently running either nonpreemptively or
preemptively.
RUNNABLE = The worker is ready to run on the scheduler.
SUSPENDED = The worker is currently suspended, waiting for an event to
send it a signal.

How to diagnostic your situation
With that in mind, you should check if you don't have a bottleneck caused by other resources (e.g., disk, network) required by the requests that are blocking the others. Paul Randal's article SQL Server Wait Statistics (or please tell me where it hurts…) has a terrific query to retrieve info about what could be causing trouble on your server. Before you assume the problem is the number of workers, I advise you to run that diagnostic on your server.


Purpose of Nonclustered indexes in SQL Server 2012

You've got two questions in here:

1. Does the nonclustered index have a sorted B-tree? Yes, because you have to get to the right page of the index to find the data you're looking for.

2. Why do you want a nonclustered index when you also have a clustered index? Think of the white pages of the phone book as your clustered index. If you wanted to find all of the people in your city with a first name of "Brent", then the clustered index isn't going to do you much good - the clustered index is based on last name, first name, middle initial. Wouldn't it be helpful to have a separate phone book sorted by first name?

Just like in real life, the answer to that might be yes or no - but it depends on how often you search for people by first name, and how often people move in/out of your city, or how often people change names.


Is there a JDBC Driver for Microsoft SSAS Tabular Databases?

There used to be a OLAP JDBC driver that supported SSAS called olap4j but it has not been updated in a while and I am not sure of its support for anything above 2012. It is always worth a try though.

Communication with an SSAS instance is done using XMLA , which itself is based upon the SOAP protocol. Technically you should be able to send queries using a standard SOAP client with embedded XMLA statements.

That being said, I know that Microsoft uses a proprietary implementation for their SOAP over TCP/IP so you may need to tinker with your client to get it to work.

Some additional detail is given in FrankPI's answer on StackOverflow where somebody asked a similar question and there is even a suggestion open for Power BI that requests interoperability between Java and SSAS.

If none of the above solutions work for you I am afraid you will have to roll your own or use the supported ADOMD.NET components.


The following SQL will show you which file groups your tables and indexes are in which will make it easy to see if there is data in any file group that shouldn't have data.

SELECT      f.[name] AS FileGroupName
            , o.[name] AS ObjectName
            , o.[type] AS [Type]
            , i.[name] AS IndexNAme
            , i.[index_id] AS IndexId

FROM        sys.indexes i
INNER JOIN  sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN  sys.all_objects o ON i.[object_id] = o.[object_id]

WHERE       i.data_space_id = f.data_space_id
            AND o.type = 'U' -- User Created Tables

ORDER BY    f.[name]
            , o.[name]
            , o.[type]
            , i.[name]

full details of the script can be seen here


You cannot switch roles in the middle of an update between major versions. MySQL-5.6 will generate binary log messages that MySQL-5.5 won't understand. So if 5.6 is every a master of a 5.5 slave you can expect replication to break.

At all stages slave version must be >= master version.

So update the slave by one major version 5.5 -> 5.6. Ensure everything is running correctly. Then update the master by one major version. The binary log messages generated with the update will have no effect on the slave already updated.

While it might be tempting to make a new node C and go through convoluted update process your are better of using a test environment, doing a slave update, a master upgrade, start slave (and repeat until at the latest major version you want). Practices a few times. Test your applications against the final version. Schedule a small outage and run through the steps prepared and tested.

Have a fallback plan, i.e package/config/datadir of original saved and ready to put into place). Test your fallback plan.


Are you using the compression function in SQL 2k8? If so then it should work.

I would personally restore this database onto a Dev/Test server and just carry out the scenario you have stated and see what size a full backup is.


Is it possible to make simple postgres dump using replication server?

No, you can only run pg_dump against a running PostgreSQL server.

You can avoid the necessity to recover the database whenever you need a pg_dump by setting up a streaming replication hot standby server with max_standby_streaming_delay = -1 and run your pg_dump against that.


The JSON1 extension makes it pretty easy.

Given the following:

CREATE TABLE user(name text, surname text, date text, value integer);
INSERT INTO user VALUES('UserName1','Surname1','2018-04-01',1231);
INSERT INTO user VALUES('UserName2','Surname2','2018-03-01',131);
INSERT INTO user VALUES('UserName3','Surname3','2018-02-01',121);
INSERT INTO user VALUES('UserName4','Surname4','2019-02-01',1231);
INSERT INTO user VALUES('UserName5','Surname5','2019-03-01',131);
CREATE INDEX user_year_idx ON user(strftime('%Y', date));

this query:

WITH json_by_year AS
 (SELECT json_object('year', cast(strftime('%Y', date) AS INTEGER)
                   , 'data', json_group_array(json_object('name', name
                                                        , 'surname', surname
                                                        , 'value', value))) AS year_obj
  FROM user
  GROUP BY strftime('%Y', date)
  ORDER BY strftime('%Y', date))
SELECT json_object('App', json_group_array(year_obj))
FROM json_by_year;

Produces this (After being run through a JSON pretty-printer):

{
  "App": [
    {
      "year": 2018,
      "data": [
        {
          "name": "UserName1",
          "surname": "Surname1",
          "value": 1231
        },
        {
          "name": "UserName2",
          "surname": "Surname2",
          "value": 131
        },
        {
          "name": "UserName3",
          "surname": "Surname3",
          "value": 121
        }
      ]
    },
    {
      "year": 2019,
      "data": [
        {
          "name": "UserName4",
          "surname": "Surname4",
          "value": 1231
        },
        {
          "name": "UserName5",
          "surname": "Surname5",
          "value": 131
        }
      ]
    }
  ]
}

Basically, it uses a CTE to produce rows of users grouped by year, and then the outer query wraps those in your final data format. The index on the expression speeds up the grouping; handy if you're going to be doing this a lot, but not really needed if it's a once in a while thing.


Randomly failing SQL Server 2008R2 used by Access 2010 front-end via ODBC

As Max says it sounds similar to a problem we had under a similar situation, especially for those working remote to the main company network.

Ensure that under the Access database Options Advanced tab that the OLE/DDE timeout is set to something like 300 and not the default of 30.

Also in any VBA requests to the database adjust the ADODB command.CommandTimeout value to a high value of at least the same 300, this will stop early timeouts and lost connections. Since we did this we have had no dropouts, it only uses the extra time as needed so do not worry about system locks from this.


How to find what prevented in getting a lock?

I recommend zespri's approach. Use profiler to find the query that is taking an inordinate amount of time.

If you've somehow had no joy with that, you can try the following query which will show a process with a particularly large waittime value.

It filters out any system processes and SQL which has contains WAITFOR DELAY '00:00:05' ... etc

SELECT *
FROM master.dbo.sysprocesses
WHERE spid > 49
AND (blocked <> 0 OR (waittype <> 0x0000 AND lastwaittype <> 'WAITFOR'))

You can then dig a little deeper with the spid against the row  that looks like it is causing the problem and inspect the SQL code that is running/locking:

DECLARE @Handle varbinary(50), @Spid int = <your spid value/>
SELECT @Handle = sql_handle from master.dbo.sysprocesses where spid = @spid
DBCC INPUTBUFFER (@spid)
SELECT * FROM master.sys.fn_get_sql(@Handle)

And the resulting output will look like this:

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------------------
Language Event 0          SELECT GETDATE()

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbid   objectid    number encrypted text
------ ----------- ------ --------- ---------------------------------------------------
13     NULL        NULL   0         SELECT GETDATE()

This should help you narrow down the problem query.


Importing Data in Parallel in SQL Server

Borrowing from my old answer

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 and up then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
Real World Parallel INSERT…SELECT: What else you need to know!


SQLserver WaitType : EXECUTION_PIPE_EVENT_INTERNAL

According to MSDN, EXECUTION_PIPE_EVENT_INTERNAL :

  Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.

The inference here is this is an internal wait type used to denote actions occurring inside the batch execution engine.  Since this is an internal engine wait, the only way you'll be able to accurately diagnose if this is an issue would be to raise it directly with Microsoft.  Alternately, if you are @BobWard or @PaulWhite, you could dig up a Windows Debugger, recreate the issue in a non-production environment, an stop the code in the middle of that wait type.  For the rest of us mere mortals, this deserves a call to Microsoft if you are truly worried about it.


How to calculate cache misses for PostgreSQL

I use this query to show disk x cache hits:

-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name,
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as
(
SELECT  *
FROM    (
    SELECT  relname as table_name,
        ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
        ( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache
    FROM    pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT  table_name as "table name",
    from_disk as "disk hits",
    round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
    round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
    (from_disk + from_cache) as "total hits"
FROM    (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER   BY (case when table_name = 'all' then 0 else 1 end), from_disk desc


Do I have start Log Reader Agent manually every time?

That depends - what schedule is attached to the log reader agent job? The log reader agent runs as a SQL Server Agent job and typically is assigned a schedule to start when SQL Server Agent starts. This schedule might be disabled or deleted hence why you're having to start it manually.

  How can I start Log Reader Agent via T-SQL?

You need to start the SQL Agent job. First, get the name of the job, then add it to this script and execute it:

EXEC msdb..sp_start_job @job_name = '<lo reader agent job name goes here>';

This shouldn't be necessary though, you just need to find and fix the problem with the schedule for this agent.

NOTE: To get the name of the job, the simplest way is to open Job Activity Monitor and look in the Category column for Repl-Log Reader.


Availability Group Endpoints Windows Authentication Error

So i am assuming its not causing any issues yet, but regardless i would like to know if its something i can ignore in my scenario, or fix.

Looks like since everything seems to be configured properly (I can't see the domain account information [which is redacted as it should be :) ]) so I wouldn't worry about it. If this happens on multiple servers in the environment then it may require further investigation but overall everything you posted seems in line with a normal configured system.

I traced this down to a call IsValidDomainUserForWinAuthentication used in SSMS which, for whatever reason, is returning false. Looking at the logic, it's checking a few various items, but the one that stands out is the assumption on how the service account name is configured (allowable characters) and that calls to LookupAccountName complete successfully. It would take a time travel trace or full memory dump of SSMS (when the message box is shown) to really investigate further which I don't believe is warranted at this time.


is it possible to run postgres with no WAL files being produced?

Short version: no. PostgreSQL doesn't support generating no WAL whatsoever, because changes to system catalog tables in the pg_catalog schema are always logged. There's also transaction ID logging (pg_clog), the multixact tracking data (pg_multixact), etc, in addition to the xlog, but they tend to be very small.

As Daniel says, you can run with minimal WAL generation by:

Using wal_level = minimal
Creating all tables as UNLOGGED or TEMPORARY tables

In this case, you will also want to set fsync = off, full_page_writes = off and synchronous_commit = off, since you have no requirement for durability and crash recovery.

Of course, if anything goes wrong (like power loss, unplanned restart, DB server crash, pg_ctl -m immediate stop, etc) your data will be totally unrecoverable, but that seems to be what you want.

The rate of WAL creation and rotation with this configuration will be negligible for most applications.

If you don't mind having to re-initdb after any restart (even a clean one) you could put pg_xlog on a tempfs, but I doubt it's worth the hassle when Pg will generate only a tiny bit of WAL.

You should also check and make sure you don't have WAL archiving (archive_mode = on) enabled, that wal_keep_segments isn't set. Both of those are only useful if you're doing WAL-based backup or replication. If you don't, then WAL should not accumulate, it should be rapidly recycled. Maybe your checkpoints are set to be much too infrequent, so much so that you run out of disk? Check to see if the checkpoint_segments parameter is really really high.

I think you're trying to solve the wrong problem here. The issue isn't stopping PostgreSQL from producing WAL, it's figuring out why it piled up in your batch jobs.

See also: Optimizing PostgreSQL for fast testing.


MySQL joining two tables and counting based on conditions

I just found the answer and wanted to share it:

SELECT
  dir_cats.*,
  SUM(CASE WHEN dir_sites.site_lang = "ar" THEN 1 ELSE 0 END) AS ar_count,
  SUM(CASE WHEN dir_sites.site_lang = "en" THEN 1 ELSE 0 END) AS en_count,
  SUM(CASE WHEN dir_sites.site_lang = "fr" THEN 1 ELSE 0 END) AS fr_count
FROM (dir_cats)
  LEFT JOIN dir_sites
    ON site_cat_id = cat_id
GROUP BY dir_sites.site_cat_id


Get the last 5 distinct values for each ID

There are probably many ways to do this. The first that comes to mind is to use window functions:

SELECT
    id, postcode
FROM
  ( SELECT id, postcode,
           ROW_NUMBER() OVER (PARTITION BY id
                              ORDER BY MAX(date_created) DESC
                             ) AS rn
    FROM tablename
    GROUP BY id, postcode
  ) AS t
WHERE
    rn <= 5
ORDER BY
    id, rn ;

Test at SQLfiddle.

If there are ties, say the 5th, 6th and 7th postcode for an id have the same date_created, only one of them (choice will be arbitrary) will be in the results. If you want all the tied postcodes in those cases, use RANK() instead of ROW_NUMBER().

Another option is to use the LATERAL syntax. I'm not sure which will be more efficient, it wil probably depend on the values distribution of the two columns (id and postcode), i.e. how many distinct ids in the whole table, how many distinct postcodes per id and how many rows per (id, postcode) combinations.

SELECT
    t.id, ti.postcode
FROM
    ( SELECT DISTINCT id
      FROM tablename
    ) AS t
  CROSS JOIN LATERAL
    ( SELECT tt.postcode,
             MAX(tt.date_created) AS date_created
      FROM tablename AS tt
      WHERE tt.id = t.id
      GROUP BY tt.postcode
      ORDER BY date_created DESC
      LIMIT 5
    ) AS ti
ORDER BY
    t.id, ti.date_created DESC;

Adding an index on (id, postcode, date_created) would be a good idea, too - or on (id, postcode, date_created DESC).


Allow user to GRANT certain permissions to others

you can use GRANT ... WITH GRANT OPTION.
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15
this option specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.


Should arrays of IDs stored in Postgres use varchar, int, or another type?

The "one true way" is to create a table and use foreign keys:

CREATE TABLE owners(
    user_id int references users
  , account_id int references accounts
  , primary key(user_id, account_id)
);

That way you can verify membership using a single and simple query:

SELECT user_id, account_id FROM owners WHERE user_id = X AND account_id = Y

And you can get a list of owners for an account using:

SELECT user_id FROM owners WHERE account_id = X

Alternatively, you can find all accounts a user owns using the reverse query:

SELECT account_id FROM owners WHERE user_id = X


Renaming a column in a materialized view with an unknown name

I just remembered you can quote table names, and I guess this works to escape the question marks.

ALTER MATERIALIZED VIEW temp_name_relationships RENAME COLUMN "?column?" TO score;


PostgreSQL: duplicates despite having a unique constraint

It seems that the problem is caused by the glibc update issue that @a_horse_with_no_name highlighted in the comments: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html

I suppose it would be enough to get rid of the duplicates and reindex that table, there will be no new duplicates after that.


Because the size of it is not big enough to handle the size of the transactions every hour.

or

Something is preventing the log from getting cleared after the log backup completes. Check the log_reuse_wait_desc column in master.sys.databases to see why it isn't clearing out.

Consider backing up the transaction logs more frequently than hourly. I recommend every 1-5 minutes.


Relational calculus is a notation for expressing relation values in terms of given named relations and sometimes constants. So is relational algebra. A constraint is an expression making a statement about one or more relation values. But such a statement is not relation-valued, so you can't express it in relation-valued notation alone. (So you need to ask what exactly is wanted.)

You have to have operators returning equality of relations, or whether a relation is empty, etc. (Either of those two is sufficient.)

-- every teacher is a person
empty({<id>|(exists class Teaches(id, class)) and not Person(id)})

A query is typically like {tuple|formula}. You could use an expression like the formula part of a query if you consider any free variables/attributes to be implicitly universally quantified. (This is also sufficient.)

-- id is a superkey of Teaches
-- forall id, c1, c2
    Teaches(id, c1) and Teaches(id, c2) implies c1=c2

(empty({<...>|formula}) is the same as forall ... notformula.)


Partitioning is only at the table level and is for managing partitions of that table.  Typically the advantage of partitioning is in swapping in and out data and for getting additional control of the granularity of storage and backup.  To some extent it can also help with performance if there are shared partition columns in a join, but that probably shouldn't be a reason to partition.

I understand your thinking about the child tables, but if you want that data also partitioned by country (for some of the above reasons), you would have to add a column because the partition function is restricted.

What is your thinking about why you want to partition in the first place?

http://msdn.microsoft.com/en-us/library/ms190787


Is it possible to disable `execute` in lieu of `exec`?

Community wiki answer:

No, this is not possible. I'd add this as a code quality item to their code reviews.

That said, the root cause of the multiple plans is not the different call syntax. That could also happen with different SET options, etc. Instead look at query plans and the parameterization behavior, and the Query Store (if on 2016+) to see why you are getting different plans.

See also: Slow in the Application, Fast in SSMS? by Erland Sommarskog.


Tickets created and resolved (or merged) per year

SELECT *
  FROM ( SELECT DISTINCT year(Created) AS Created
           FROM rt4.Tickets
       ) AS w

  LEFT JOIN ( SELECT year(Resolved) AS Resolved
                   , count(*) AS resolved_cnt
                FROM rt4.Tickets
               WHERE Resolved IS NOT NULL
               GROUP BY year(Resolved)
            ) AS z
         ON z.Resolved = w.Created

  LEFT JOIN ( SELECT year(Created) AS Created
                   , count(*) AS merged_cnt
                FROM rt4.Tickets
               WHERE Merged IS NOT NULL
               GROUP BY year(Created)
            ) AS q
         ON q.Created = w.Created
;

Sure you have to remember that functional restrictions like year() in the WHERE, JOIN...ON or GROUP BY clauses cannot be speeded up by indexes and can be VERY slow.

I hope that query wouldn't be runned on the regular basis.


How to reduce network latency during mirroring?

For a weak network I wouldn't personally use Mirroring. But if you really want mirroring for the specific case when there might be a slow connection an option would be to use the Asynchronous mode (or High Performance mode). This would imply some data loss would be possible, but you will be able to finish transactions faster.

Details on MSDN: Async mirroring. Some details about it:

  This mode enhances performance at the expense of high availability.
  High-performance mode uses just the principal server and the mirror
  server. Problems on the mirror server never impact the principal
  server. On the loss of the principal server, the mirror database is
  marked DISCONNECTED but is available as a warm standby.

Now this comes with a caveat, the transaction on the Principal is just put in queue, it's not committed immediately on the Mirror, so in case of problems with the Principal, you may have data loss. But at least you won't wait for the transaction to finish on both servers. Check if it's an option.

Regarding specifying different NICs for the connections, please see these questions:

Using SQL Server on separate networks and
Running different instances of SQL Server from different networks on one box.

In theory it should be simple enough as to configure both NICs with different IP and use the IP to connect to the SQL Server (not the server name). Also I'd think @Nathan's answer might lead you to a fix. No idea if it can be done with the existing mirroring, my guess is that you need to redo it.

The first sentence is more important if it's an unreliable network. In that case I'd totally advice to use a different technology for HA (log shipping or maybe replication).

PS: Following @Nathan's mention about reading the real requirements (ty, @NathanJolly), then your option remains to configure the NICs so you can connect via the direct link cable. Follow the other answers and questions and see if it's good enough for your environment. If it's still too slow, then I'd suggest you move over to log shipping.


PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO?

CREATE TABLE AS has some advantages over the other form, namely in the reduction or elimination of WAL.. Certainly optiimizations can be applied to a few commands (viz. CREATE TABLE AS, CREATE INDEX, CLUSTER, COPY into tables that were created or truncated in the same transaction) in certain wal-reduction modes (minimal).

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7).

  But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so replica or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

You can read more information about this here

  14.4.7. Disable WAL Archival and Streaming Replication

  When loading large amounts of data into an installation that uses WAL archiving or streaming replication, it might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. To prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart.

  Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they are designed not to write WAL at all if wal_level is minimal. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.)

As a special note, if you go back to your

CREATE TABLE t1 (like t2);

And instead do

CREATE TABLE UNLOGGED t1 (like t2);

It'll be even faster. But, don't do this as you'll be missing WAL. For more information on this read

UNLOGGED docs
PostgreSQL Non-durability features


In order to preserve the "left" part of the join (= your current rows), you need to join with a left outer join. You can learn about outer joins in the documentation.
By the way, if you only need the field you are joining on, then joining is not required...
I took the liberty of:

reformatting the query,
rewriting parts of the WHERE clause to use the IN keyword,
removing the keyword outer (it is optional in T-SQL),
removing the WITH (nolock) options, put them back if you really need them.

Updated query:
SELECT  RIGHT(dbo.SorMaster.SalesOrder, 6) AS 'SalesOrder',
        dbo.SorMaster.CustomerPoNumber,
        dbo.SorDetail.SalesOrderLine,
        dbo.SorDetail.MStockCode,
        dbo.SorDetail.MStockDes,
        dbo.SorDetail.MOrderQty,
        dbo.SorDetail.MCusSupStkCode,
        dbo.SorDetail.LineType,
        dbo.SorMaster.OrderStatus,
        dbo.[CusSorDetailMerch+].DuparJobNumber,
        dbo.SorMaster.Customer,
        dbo.SorDetail.MShipQty,
        dbo.SorDetail.MBackOrderQty,
        CASE
            WHEN RIGHT(dbo.SorMaster.SalesOrder, 6)= '211125'
            THEN dbo.SorDetail.MOrderQty
            ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
        END AS 'ShipPlusBackorderQty',
        dbo.InvAltStock.AltStockCode,
        RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) AS 'YearWeekNo',
        'B00ABD' + RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) + '' AS 'SerialPrefix',
        dbo.[CusSorDetailMerch+].InvoiceNumber,
        dbo.SorMaster.SalesOrder,       -- new selected field (no join required)
        dbo.[CusSorMaster+].SalesOrder, -- new selected field
        dbo.[CusSorMaster+].*           -- new selected fields: all fields from [CusSorMaster+]
FROM dbo.SorMaster
JOIN dbo.SorDetail
    ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
LEFT JOIN dbo.InvAltStock
    ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
LEFT JOIN dbo.[CusSorDetailMerch+]
    ON  dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
    AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
LEFT JOIN dbo.[CusSorMaster+]                                               -- new outer join
    ON dbo.SorMaster.SalesOrder = dbo.[CusSorMaster+].SalesOrder
WHERE dbo.SorDetail.LineType in ('1', '7')                                  -- rewrote OR
  AND dbo.SorMaster.OrderStatus not in ('9', '\', '*')                      -- rewrote OR
  AND (dbo.[CusSorDetailMerch+].InvoiceNumber in ('', '000000000000000')    -- partially rewrote OR
    OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);


How can i insert the multiple data in table using the existance condition?

INSERT dbo.mytest(first_name, table_id)
SELECT fn, ti FROM
(
  VALUES('f1', @id_for_test),
        ('f2', @id_for_test),
        ('f3', @id_for_test),
        ('f4', @id_for_test),
        ('f5', @id_for_test),
        ('f6', @id_for_test)
) AS x(fn, ti)
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.mytest
    WHERE first_name = x.fn
      AND table_id   = @id_for_test -- or = x.ti
);


Questions about handling NULLs and empty strings in nvarchar and numeric fields

Q1

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.

Why would you expect 100 rows? You have 20 rows where the column is NULL. Your expression evaluates to:

SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)

Since NULL means unknown, an equality or inequality comparison will yield unknown (and in this case false or, more pedantically accurate, not true). When column1 is null, SQL Server can't tell you if it is equal to 'foo' or not equal to 'foo'.

Q2

The error is due to implicit conversion and the order of the expressions. In the first query, you are comparing to a string first, then to NULL. The NULL becomes a string, because it was referenced later, and so the underlying column (as you should see in the execution plan) was implicitly converted to a string. In the second query, you are comparing to NULL first, therefore to determine the data type of the expression, it must go check the table. The table contains a numeric, so the first argument is the same as CONVERT(NUMERIC(18,2), NULL), and then it tries to convert the empty string to numeric. Try this to see why it doesn't work:

SELECT CONVERT(DECIMAL(10,2), '');

Q3

In order to use the same expression on all data types, you must be able to convert them all to the same data type. So let's say I have a table:

CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2));

INSERT #foo SELECT '1', NULL;
INSERT #foo SELECT NULL, 4.5;
INSERT #foo SELECT '', 5.5;

Now compare the results of these four expressions:

SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') <> '';
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = '';

SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') <> '';
SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';


mysql replication deadlock slave stopped

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;


Restoring and upgrading multiple databases

First thing i would like to highlight is you should have the system backups in addition to user databases handy when you said you need to migrate from SQL2k5 to SQL2k8R2.

You can copy all the latest backups from the current server onto the Windows2K8 box you mentioned.

Once copied, the first thing you should be looking is restoring the system database backups as mentioned here Backup and Restore system databases

Doing the above will make sure all the logins, SSIS packages, jobs etc would be attached as well when migrating to new version.

Now, you should start restoring the user database backups copied from old server to this new box as guided in Restore database from existing full backups

In addition to above you can use Powershell to completely migrate all the databases in go as you mentioned , from here.

Also, you can refer Migrate SQL server from one machine to another


I want to find out how data is getting inserted to a particular table?

Copied from a Stack Overflow answer by Damian Leszczyński - Vash, could not find a duplicate here:

You can't set filter to trace specific table directly.
What you can do is specify a filter for Text Data:

File -> Properties -> Event Selection -> Column Filters -> TextData -> Like -> [Table Name]

Your other option is to use Extended Events, but it has a much higher learning curve.  Be careful using Profiler in production. The way it reads the data has a very high overhead and it could also cause the client you're using to time out.
You could use a 'server side trace' but that also has a higher learning overhead.  Use Profiler in prod at your own risk, and make sure you don't run the client on an important server.


Measuring how long a query is IDLE IN TRANSACTION (postgresql + Django app)

A session that is "idle in transaction" can't have waiting = true because otherwise it wouldn't be "idle".

The difference between current_timestamp and  the timestamp in state_change should give you the time that the session was in "idle in transaction".

In general, a session that is just "idle" is not a problem. A session in "idle in transaction" might very well be a problem, as it prevents vacuum of cleaning up the rows that the transaction was using.

A short (a few seconds) "idle in transaction" is not a problem, only very long running  (minutes, hours) transactions that are not ended properly (and thus stick in "idle in transaction") are a problem.

With 9.6 you can force the server to disconnect long running "idle in transaction" sessions using the property idle_in_transaction_session_timeout


How can I get a list of column names and types from a resultset?

Way late to the party, but just thought I'd mention a metadata enhancement in SQL Server Denali that will make it much easier - not only to inspect the output of a query without running it (not quite the same behavior as SET FMTONLY ON, which many apps use today), but also to build target tables dynamically (without all the parsing and case work involved with pulling metadata from sys.columns). Here is a quick example - notice you never have to actually run the query to figure out the shape of its resultset:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM sys.database_files';

SELECT name, system_type_name, collation_name
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    ORDER BY column_ordinal;

There are some limitations, of course. If you have a query with multiple statements, the phrase "first_result_set" in the name should give a clue that you will only receive information about the first statement that returns data - it will not stop at a leading SET NOCOUNT ON; for example. There are also going to be issues if you are referencing > 3-part names either directly or by trying to follow a view or synonym. But for the majority of use cases this will be a welcome simplification.

I blogged a lot more details back in December, also describing some of the other metadata enhancements:

https://sqlblog.org/2010/12/20/sql-server-v-next-denali-metadata-enhancements


MS SQL Server 2008R2, "Login failed for user" from time to time, IIS 7 , NET 4.0

The clue is this

  Cannot open database "TheDatabase" requested by the login

This means that some connection string has either a non-existent database mentioned, or the login used does not have access. It is also possible that no database is mentioned, and the default database for the login is missing/no permission.

One of these 3 options is usually the answer...

Edit: you don't have auto-close on the database, do you?


Can bdr only replicate one database per server?

BDR can replicate as many databases per server as you want. You must configure each database separately, that's all.

There is not (currently) any wildcard configuration to say "all databases on the current DB".

A typical configuration looks like:

Server1       Server2
DB_A     <=>  DB_A
DB_B     <=>  DB_B


Inserting result of stored procedure into a table with additional column

A possible option is to use the technique from this post.

Basically, you declare a table variable to hold the results of the stored procedure and then use an additional insert statement to add the extra column.

In the following example, 3 columns are returned from the sp_readerrorlog stored procedure and the additional insert adds servername.

Just make sure you truncate the table variable before each invocation of the stored procedure within your loop.

DECLARE @readerrorlog_results TABLE (
  LogDate date,
  ProcessInfo varchar(max),
  Text varchar(max)
)

INSERT INTO @readerrorlog_results
      (LogDate,ProcessInfo,Text)
EXEC sp_readerrorlog 0, 1, 'Login failed'

INSERT INTO FailedLogins
      (LogDate,  ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results


Login failed for user - Error 18456 - Severity 14, State 38

State codes and their meaning.
1      'Account is locked out'
2      'User id is not valid'
3-4    'Undocumented'
5      'User id is not valid'
6      'Undocumented'
7      'The login being used is disabled'
8      'Incorrect password'
9      'Invalid password'
10     'Related to a SQL login being bound to Windows domain password policy enforcement.
        See KB925744.'
11-12  'Login valid but server access failed'
16     'Login valid, but not permissioned to use the target database'
18     'Password expired'
27     'Initial database could not be found'
38     'Login valid but database unavailable (or login not permissioned)'

More detailed information is available in Aaron Bertrand's blog.
To find the status code, you need to inspect the SQL Server Error Log after ensuring that failed logins are recorded in the error log.


SQL Server Query Plans & Offline databases

Did you try:

get-sqldatabase -ServerInstance yourserver | Where-Object status -eq 'Normal'


Splitting up tables with similar structures since they contain unrelated data? (huge numbers)

Your issues are probably less about performance, assuming reasonable equipment and a good indexing structure, and more about other impacts that you face.

Approach 1 means some additional programming complexity to deal with filtering rows in each table to restrict access to a single gameworld. It also means that if you need to restore a gameworld, you would have to (a) restore all gameworlds (since they are in the same database) or (b) restore the one database to a recovery database and then script the appropriate gameworld data from Recovery to your Gameworlds database.

Approach 2 means that you need to be able to manage schema changes across many databases.  This can be done by scripting as needed.  It is also easier to choose to have different schemas for some gameworlds, but that adds management overhead.  However, if a a single gameworld crashes, you can restore the latest good backup(s) and get it going again.

Some years ago some Microsoft folks wrote up their take on Multi-Tenanted databases.  See: http://msdn.microsoft.com/en-us/library/aa479086.aspx

Having more tables should not, in itself, cause performance problems within the size range you describe.

Choosing your approach should take into account leveraging your strengths so that you have a system you (and your project members) can readily manage.


Minimum/Maximum Server Memory in SQL Server 2005 with /3GB and /Userva switches

I suppose the real question here is "what are you trying to solve". At best you are only going to get a few hundred extra MB into your buffer pool. If you are doing this to solve a performance problem then you should make certain your problem is related to memory and not something else, like network.

Are you running Win2003 Enterprise, or Standard?

If Enterprise, you should look at using /PAE along with AWE to squeeze out some extra RAM.

If you are not running Enterprise, then I believe the /3GB and /USERVA switches are recommended by MS, but like I said before this is only going to gain you a few hundred extra MB at best.

The /3GB switch reduces the number of Page Table Entries (PTEs) for your O/S. So, if you are already seeing paging on your server, if you use /3GB you are likely to see more as a result.

Lastly, MS does not recommend using the /USERVA switch for values under 2900, so you may find yourself with a server in an unsupported state. Then again, it is Win2003, so it is just about out of support anyway, so that may not be an issue for you.


Your table is like this:

CREATE TABLE billables
(
  billable_id "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
  account_id bigint NOT NULL,           <<<<---- Can't be NULL...
  code character varying(64) NOT NULL,
  info "text",
  m_unit "measurement_unit",
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  <rest snipped...>

The fields you want to insert are

(code, info, unit_cost, unit_price)

For starters, you don't provide values for the field account_id which is NOT NULL

Your insert is like this:

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95,Alisha Davidson

I ran this SQL (on a slightly modified CREATE TABLE - don't have your other tables for FOREIGN KEYs - don't know what "measurement_unit" is (user-defined data type?)). New CREATE TABLE below.

INSERT INTO billables (account_id, code, info, unit_cost, unit_price)
VALUES (45, 'Interpreting Normal/AH','DO NOT CHANGE [OTH-INTERPSERV (2-5)
non-SLIAN,41,Alisha Davidson]',46.5,95);

And it worked - notice the single-quotes (').

Hopefully this will help to get you started.

New CREATE TABLE.

CREATE TABLE billables
(
  billable_id serial,  <<-- why not user serial here?
  account_id bigint NOT NULL,
  code character varying(64) NOT NULL,
  info "text",
  m_unit bigint,
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  tax_enabled_price boolean DEFAULT true,
  tax_aggregate_id_price bigint,
  ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_last_used timestamp with time zone,
  is_demo boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
  CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
  CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
);

[EDIT in response to OP's comments]

Start simple and then bring in complexity. Post your new .csv with the account_id.

Or, write an SQL statement like I did. See what errors occur (you should get none).

Start with no CONSTRAINTs - then add them gradually and see where your COPY starts to fail - it's debugging database style :-)


How do I go about type iterations in a SQL Query?

I would just join the tables directly
Coalesce is fine here I think

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
LEFT JOIN dbo.containers as con
  on d.ParentNetObjectID = con.ContainerID
 AND d.ParentEntityType = 'Orion.Groups'
LEFT JOIN  ...
ORDER BY ParentName

You could put the 3 tables in a view or cte

select "orion" as type,
        c.ContainerID
       ,c.Status
       ,c.Name
 FROM dbo.containers as c
union all
...


Creating a table with CHECK constraints in MySql workbench

This can be done very elegantly using CHECK constraints in MySQL >=  8.0.16 as follows (fiddle available here):
Your table (changed slightly)
CREATE TABLE IF NOT EXISTS orders
(
  order_no INT,
  order_type CHAR(8) CHECK (order_type IN ('e', 'i')) ,
  engine_description VARCHAR(45) DEFAULT 'The default',  -- NOT NULL constraint removed
  CONSTRAINT ot_ed_ck
    CHECK ((order_type = 'i') OR (order_type = 'e' AND engine_description IS NULL))
);

Populate it:
INSERT INTO orders
VALUES
(1, 'i', 'safadf'), (2, 'i', 'xcvzxv'), (3, 'e', NULL);

Then
SELECT * FROM orders;

Result:
order_no    order_type  engine_description
       1             i              safadf
       2             i              xcvzxv
       3             e                null

So far, so good.
Then we try to do this:
INSERT INTO orders VALUES ('4', 'e', 'xxxxx');

and we get the error message:
Check constraint 'ot_ed_ck' is violated.

Which is exactly what we want!
To cover the case of or else a value will be added? in the question, by which I presume you mean a DEFAULT - if we specifically exclude engine_description from the list of values to be INSERTed as follows:
INSERT INTO orders (order_no, order_type) VALUES (5, 'i');

and we SELECT:
SELECT * FROM orders

We obtain:
order_no    order_type  engine_description
      1              i              safadf
      2              i              xcvzxv
      3              e                null
      5              i         The default   <<----- Default!

I would strongly urge you to upgrade to MySQL 8 - it's now at version 8.0.22, so any major version change wrinkles will have been ironed out by now - you get all sorts of goodies - like CHECK constraints that I just outlined, COMMON TABLE EXPRESSIONs (available from 8.0.1, aka the WITH clause) and also GENERATED columns (5.7.5).
If you cannot upgrade to version 8, then you'll have to use TRIGGERs (as per @nbk's answer), but you asked for constraints so that's what I supplied.
Also, what exactly do you mean by:

Also, if I want to do the same for two different entities?

If you mean constraints between two fields in the same table (intra-table), you have the example above, if you mean between two tables (inter-table), then on any server (bar Firebird), you'll have to use TRIGGERs!
Finally, a word of advice - do NOT use SQL keywords as table or column names (in this case Order)- it's confusing, makes debugging more difficult and is not good practice.  Normally, I use singular table names, but I make an exception (obviously) for ORDERs - a table is a set and should be singular (IMHO).
p.s. welcome to the forum!


The research I did made it seem like I need to either write my own aggregation or upgrade Oracle. I ended up doing neither, and exported the query through Tableau, formatting it in a sheet appropriately, then exporting that sheet to excel and making an aggregation column.


Do you need SQL Server AAG when using SQL Server FCI/RAID10?

If I have a 2-Node AlwaysOn Automatic Failover Cluster Instance with a shared disk and RAID-5 or RAID-10 to account for a SAN failure with the same setup in a second datacenter with data propagation via SAN Replication, is there a way to benefit from using AAG in this scenario?

That depends, are you worries about any of the following scenarios?

Strict RPO/RTO
Does your SAN support write ordering across multiple LUNs?
OS/SQL Patching issues
Controller level IO corruption
Downtime for Maintenance

If your answer to the above is, "Not worried" then I don't see any reason to use any AlwaysOn technology (FCIs or AGs). If your answer is, "Yes, one or more worry me" then I would consider looking into a set of technologies to meet your specific concerns and scenarios.


Migrate Data and/or Schema from DBv1 to DBv2

It sounds like you've got this entirely back to front and upside down. Typically changes are tracked using scripts, which you would then apply to the production database. I've never encountered a case where it made sense to import production data to the next version created from development.

If your database isn't currently in source control, the articles linked to from Jeff Atwood's article Get Your Database Under Version Control are a good introduction. Also, the free Redgate ebook SQL Server Team Based Development includes a chapter on source control for databases.

If you've been making changes via GUI tools and don't have any record of what's changed your best bet is a database comparison tool, like Redgate SQLCompare or Apex SQLDiff. These tools will generate the scripts to upgrade your production database to match the schema of development.

If the boss won't part with the cash for a comparison tool, you could reconcile the changes manually using a diff tool like WinMerge or DiffMerge. Use SSMS Generate Script tools to script objects to individual files, then use the diff tool to identify differences. Finally, hand craft the necessary ALTER statements for any changes to tables.


You could add one more field in your table, something like parent_id where you could store the primary key from which the new record is derived. And primary key will continue to auto-increment. I hope this will fix your confusion.


How can a database be normalized when an optional field causes denormalization?

You could consider a cart to be a storage location.

Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so:

If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table.

This is not a perfect solution - it may make it difficult to enforce restrictions that a cart must exist in a storage location (like a bay or aisle).

However, in most database engines it's straightforward to enforce some business logic here using CHECK constraints.