Connection Issues with Informix OLEDB Provider

So, after giving up searching for answers, I started trying every imaginable combination of properties with different registries set and not set and I managed to get it working.

The Connection String was missing UNICODE=TRUE.

I can't say for sure, if I already tried this before and the error originally came from incorrectly set registry entries or if that simple solution slipped past my testing.

Anyways here is what I did, in addition to what was mentioned in my original post (no idea if that made any difference):

registered the ifxoledbc.dll (32Bit and 64Bit) with the respective regsvr32.exe (Originally I used the 64-Bit regsvr32.exe for both)
Added registry entries in Computer\HKEY_CLASSES_ROOT\Ifxoledbc\CLSID (64Bit) and Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Ifxoledbc\CLSID (32Bit) with name: OLEDB_SERVICES type: REG_DWORD and value: 0xffffffff

Concluding:

I'm still surprised, that the RowsetViewer somehow bypassed the need for the UNICODE property. Maybe it automatically sets it to true?

The interface:unknown error I mentioned with the viewer seems to have been fixed in a newer version of the Informix Client SDK (Only happened with 64-Bit Operating Systems using 32Bit Tools).

During my successful testing I also noticed, that Excel seemingly didn't need the %INFORMIXDIR% environment variable. FYI, when I was testing the ODBC datasources i had to edit the %INFORMIXDIR" to either point to my 32Bit or 64Bit folder (Not doing so resulted in a unable to load shared library error). The 64Bit datasource was also incompatible with my 32Bit Excel. With the OLEDB Provider however, it connects just fine with %INFORMIXDIR% still pointing to the 64Bit folder.


MySQL not using PRIMARY KEY to sort by default

MySQL like any other RDBMS do not sort results by default. You have to add ORDER BY clause to get an ordered result. Otherwise the order rows are returned is undefined.

In some degenerate cases you have a chance to get an ordered result without ORDER BY - e.g. when you have bulk-inserted preordered rows into the newly created table, and if you do not DELETE or UPDATE any row before SELECT. But when the table is heavily DELETEd or UPDATEd, mysql performs so-called "index tree rebalancing". Traversing of the rebalanced index tree returns rows in the unpredictable order. Therefore you should never rely on assumption that query without ORDER BY returns an ordered result.


Exporting SQL Server DB from my laptop to AWS RDS

The paths in the generated script are the  locations where the data and log files should be on your laptop.

If you copy paste the script to amazon AWS that will be the location where the files are created when you create the database.

If that path doesn't exist on the Amazon instance you will get errors.

You could comment out the lines and then the files will be created in the default locations for the instance, with default sizes and a filename generated from the database name.

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 /* ON  PRIMARY
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)*/
GO

As you indicate some of the other errors are related to things like roles and permissions there is an advanced settings dialog where you can change some options, and you can also select which objects to script (such as users)

See this link How to: Generate a Script (SQL Server Management Studio) for an overview of the wizard.

For example if you select "select specific options" in this page you can deselect "Users"

On the next page you can click an advanced button

Which opens a dialog with a lot more options, allowing to script things like permissions and triggers and stuff like that.


Millions of reads for a single table used for reports

[FinPeriod] [FinPeriod] : 180 rows

[GLHistory] [GLHistory] :75334 rows after applying those filter at the end of index spool

At Nested loop :Estimated rows 23483000 which is 75334*180 rows.

Final output Estimated rows 2987930 which is way above 251114.
This prove your query is wrong.you have to improve cardianility estimate.

In Findperiod table finperiodid is char(6) and in GLHistory table it is int.
Which is one of reason for poor cardianility estimate.

Make FinPeriodID to INT in both the table SAFELY.In million of record it does matter.

Alter table [FinPeriod]
Alter column [FinPeriodID] int not null

Experiment,

Declare @i int=201706
Declare @j char(6)='201706'
select DATALENGTH(@i),DATALENGTH(@j)

In FinPeriod table change the order of Clustered Index like this CompanyID (ASC), FinPeriodID (ASC)

Now in GLHistory your choice of Clustered index is wrong.

Firstly It will cause lot of index fragmentation.
Secondly  your index  is not being use in sample record of 700.

So i hv change index in GLHistory table in this manner,

CREATE  CLUSTERED INDEX RIIX ON GLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX RIIIX ON GLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

I  use your query without changing any logic in old index way .I got all index scan .

No need of any covering index.WHY ?Because CLUSTERED Index use in join is able to select those columns.

Now I  drop the index and created index like above.

In your query you will notice I have only change the order of column in join so that it utilise the index.
After Creating index don't forgot to update statistics.

UPDATE STATISTICS [GLHistory] ;
GO
UPDATE STATISTICS [FinPeriod] ;
GO

SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].[FinPtdDebit]
    ,[GLHistory].[CuryFinPtdCredit]
    ,[GLHistory].[CuryFinPtdDebit]
    ,[GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    ,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    ,[GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    ,[GLHistoryLast].[CuryFinPtdDebit]
    ,[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
    ,[GLHistoryLast].[YearClosed]
FROM (
    SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM [GLHistory] [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [GLHistory].CompanyID = 7
        AND ([GLHistory].BranchID = 13)
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN [GLHistory] [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN [GLHistory] [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

Result : I am getting all index Seek,minor Nested loop .Thats it.

Don't know how it will perform in million of records.

Alternative Solution,

Put [GLHistory]  filtered records in temp table because you are joining same table 3 times.Add all require column in temp table.I have added few only.

    CREATE TABLE #tmpGLHistory (
    id INT identity(1, 1)
    ,BranchID INT
    ,LedgerID INT
    ,AccountID INT
    ,SubID INT
    ,FinPeriodID INT
    ,CompanyID INT
    ,CuryID NVARCHAR(10)
    ,FinPtdCredit DECIMAL(19, 4)
    ,CuryFinPtdCredit DECIMAL(19, 4)
    ,FinBegBalance DECIMAL(19, 4)
    ,CuryFinYtdBalance DECIMAL(19, 4)
    ,CuryFinBegBalance DECIMAL(19, 4)
    )

CREATE  CLUSTERED INDEX TIIX ON #tmpGLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX TIIIX ON #tmpGLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

INSERT INTO #tmpGLHistory
SELECT [BranchID]
    ,LedgerID
    ,AccountID
    ,SubID
    ,FinPeriodID
    ,CompanyID
    ,CuryID
    ,FinPtdCredit
    ,CuryFinPtdCredit
    ,FinBegBalance
    ,CuryFinYtdBalance
    ,CuryFinBegBalance
FROM [GLHistory] [GLHistory] WITH(NOLOCK)
WHERE [GLHistory].CompanyID = 7
    AND (
        [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13
        )

    SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].FinPtdCredit
    ,[GLHistory].[CuryFinPtdCredit]
    --,[GLHistory].[CuryFinPtdDebit]
    --, [GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    --,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    --, [GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    --, [GLHistoryLast].[CuryFinPtdDebit],
    --[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
--, [GLHistoryLast].[YearClosed]
FROM (
   SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM #tmpGLHistory [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [FinPeriod].CompanyID = 7
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN #tmpGLHistory [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN #tmpGLHistory [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

--ORDER BY [GLHistoryByPeriod].AccountID asc oFFSET 0 ROWS FETCH NEXT 10000000 ROWS ONLY

DROP TABLE #tmpGLHistory

  Introduce Filter inside Inner sub query

when you can write WHERE [GLHistoryByPeriod].[finPeriodID] = 201706
Outside inner query then why not introduce same filter inside inner subquery so that inner subquery already give you less resultset imprving the query dramatically.

Like , [FinPeriod].[FinPeriodID] = 201706


Turn on those flags only for initial development.  They are too noisy with false alarms otherwise.

There are valid cases for queries that do not use indexes.

There are cases (such as EAV) where it is impractical to have indexes for every possible query.

I prefer to turn on the slowlog with a low value for long_query_time, and later gather what queries are the "worst".  This is a much better list of queries to work on improving.  It may include some slow queries with the best possible index, and exclude some very fast queries that don't involve any index.

Do not blindly index every column.  Learn about "composite" indexes.

See my Cookbook on creating optimal indexes for basic queries.


Duplicate Oracle Database from Windows to Linux

An Oracle employee told me that redo logs are not platform independent. They told me I'd either have to shut down the Windows database and take a cold backup of it without any redo logs, or I'd have to restore the files I already have to another Oracle database running on Windows.

We ended up setting up a new Windows machine to restore the database to.


The optional WITH ROLLUP clause can be added to your GROUP BY in order to get the grand total row.
GROUP BY employeeName WITH ROLLUP

To get "TOTAL" as the label, you can use the GROUPING function.
Select
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END

The final query looks like this:
Select
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT (
    SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName WITH ROLLUP
Order By GROUPING(employeename), employeeName

Notice that I tweaked the ORDER BY so that the total row comes last in the resultset.


Howto transfer tracefile into a trace table without loosing the sort order?

During creation of the question process I figured out a way:

SELECT IDENTITY(int, 1, 1) AS RowNumber, EventClass, TextData, Duration, SPID, BinaryData, ServerName, DatabaseName INTO trace_table3
FROM fn_trace_gettable('c:\traces\myTraceFile.trc', default);

does the trick. The results are kept "together" same way as in the trace table.

http://zarez.net/?p=228

https://msdn.microsoft.com/de-de/library/ms188425(v=sql.120).aspx

I tried to trace the execution of the function fn_trace_gettable to get an idea how it orders the records to get them lined up again, but of course nothing showed up but the execution itself.

However, this answers question 1, I have the desired result.

EDIT:
As AaronBertrand suggested in the comments, the eventsequence delivers the events in an order that very reliably contains the PLANS (column/type 51) in advance of the SQL Statements. That's a better approach than identities.


Migrating Large Database from SQL Server 2008 to 2014

I'd suggest setting up database mirroring.

Assuming you setup mirroring in synchronous, high-safery, mode, the 2008 server will send data over to the 2014 server, as it is modified in the source database.

Once you decide to cut over to the new machine, you simply initiate a failover to the mirror database, "et voila", after a minute or two required for the 2014 machine to "upgrade" the database, it will come online and be ready to use.


This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1.

Easily reproduced with the following:

create view v1 as ( select 'view' as vvv from dual );

create view v2 as ( select * from v1 );

with v1 as ( select 'cte' as vvv from dual) select * from v2;


Order the result of a comma separated string generated with STUFF

As Aaron Bertrand pointed out in comments, the error is produced because I'm using SELECT DISTINCT; and in this example it is not necessary. Also, ORDER BY position is fully valid if the distinct is removed.

If DISTINCT is removed the query returns the desired value:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;
GO

| (No column name)                                                                  |
| :-------------------------------------------------------------------------------- |
|  M3 AS [Title1], M5 AS [Title2], M1 AS [Title3], M2 AS [Title10], M4 AS [Title12] |

dbfiddle here

I recommend having a look at this article, recommended by sp_BlitzErik:

Grouped Concatenation : Ordering and Removing Duplicates


Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

Yes, you can do it for multiple tables. You can't do it for multiple indexes on the same table - I invented a new index lock subresource to prevent it. Same behavior in ALTER INDEX ... REORGANIZE that I replaced it with in 2005.

Thanks


Alternatives to joins using Linked Server and OPENQUERY

You can perhaps play with EXECUTE AT vs. OPENQUERY, but what I see as critical is:

  If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned.

If this is both true now, and will be true after a few years of growth, and you've already restricted the amount of data returned as much as possible by doing as many joins and WHERE clause restrictions and even RTRIM if you have CHAR columns with trailing spaces in your OPENQUERY statement as you can, then at the beginning of your stored procedure/query batch, create a #temp table for the results you need and fill it with the OPENQUERY results.  Then use it in your actual queries and joins!

Use Profiler (reads, writes, cpu, duration columns) to check on overall performance with SQL:BatchCompleted and RPC:Completed events; in particular, see if various ways of indexing the #temp table yield a net gain.

Alternately, consider that instead of an agent job refreshing a permanent table willy-nilly, is there a guaranteed way of telling if the remote server's table has been changed, perhaps a rowcount to find deletes combined with a MAX(updatedate) or a MAX(column that's  INT IDENTITY and always autopopulates) that'll catch changes, inserts, and inserts equal in quantity to the deletes, or another way?

If so, then in your stored procedure/batch, instead of fetching all the data, just check to see if it's changed.  If it's changed, update your permanent local table.  If not, use your permanent local table as-is.  WARNING: be very careful not to deadlock yourself with this code if it's run by several people at close to the same time (i.e. "run at the same time" in layman's terms).

Also alternatively, put a trigger on the remote table that simply sets a flag table with a single row and BIT column (call it YourDataIsOutOfDate) to 1 on every DML statement on that remote table.  Then do the above "check for updates" technique, except that you need to lock the remote table, update your local table, update the YourDataIsOutOfDate remote table to be 0 again, and then unlock  (finish your transaction) the remote table.  Again, beware of deadlocking yourself, and this may run into remote transaction intricacies that could be a headache.  I don't recommend this, but include it for completeness.


The answer can be platform-specific. Although the results are the same, your performance might be very different.

I would suggest another approach, because it seems to clearly document the following intent: calculate some aggregates, then decorate them with another field(s). Here is an example:

WITH CustomerTotals AS(
SELECT CustomerID,
SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
)
SELECT Customers.Name, CustomerTotals.TotalAmount
FROM  CustomerTotals JOIN Customers
  ON CustomerTotals.CustomerID = Customers.CustomerID


Disable database tables for writes but report writes as successful

MySQL, and friends, i.e. MariaDB and Percona, have a rather unusual storage mechanism called the Black Hole engine - it might help with your particular request (see the fiddle here).

For example, if you do this:

CREATE TABLE foo
(
  bar INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  baz VARCHAR (30) NOT NULL
) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)

The default Engine is InnoDB anyway, but I'm just being explicit for the sake of clarity here.

Then, we input a few records:

mysql> INSERT INTO foo (baz) VALUES ('afadfa'), ('sfasdfa'), ('asdfadf');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

No problems there! Then, to check we:

mysql> SELECT * FROM foo;
+-----+---------+
| bar | baz     |
+-----+---------+
|   1 | afadfa  |
|   2 | sfasdfa |
|   3 | asdfadf |
+-----+---------+
3 rows in set (0.00 sec)

All's rosy in the garden!

Now, for the interesting part!

mysql> ALTER TABLE foo ENGINE = BLACKHOLE;
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

OK. We continue merrily INSERTing data as follows:

mysql> INSERT INTO foo (baz) VALUES ('xxxxx'), ('yyyyy'), ('zzzzzzz');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Again, all is fine and dandy. Database is happy, we're happy and, most importantly, third party software is blissfully happy and ignorant of any change in the environment!

BUT!!! Now, when we do this:

mysql> SELECT * FROM foo;
Empty set (0.00 sec)

Nada! Zilch! Rien! Nought...

Our data has met the Black Hole's event horizon from which nothing can escape! You can put as much data in as you like - it's like sending a file to /dev/null in *nix!

You asked:

  Is there a way to lock this log table so it's never written but at
  same time it will report the write as successful so it doesn't cause
  issues with the software?

and now you have (see above) outwardly normal behaviour on the INSERT:

Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

So, we're golden! To all intents and purposes, the data being sent to the server is being faithfully stored, except it's not!

To the software writing these logs, it appears that all is hunky-dory but nothing gets written - it's really quite nifty.

This looks as if it was made for your requirement. In MariaDB, you need to load a plugin as explained here, but it seems pretty straightforward. From here:

  the application will continue to function with no errors. But since the
  data is never actually written, all the extra I/O is gone.

Just a word of warning! I don't know what your software is that is writing all this log data to the server, but somebody, somewhere, sometime thought it was worthy of saving. Just make sure you're not throwing the baby out with the bathwater!

Also, if you have a support contract for this pesky software, you might want to check to see if you're potentially invalidating that contact by doing this?

As a final note, if you try something like this on the Black Hole table:

INSERT INTO foo (baz) VALUES (23, 'asdfds'), (33), (43, 'adsfd');

Result:

Column count doesn't match value count at row 1

So, even the errors are authentic, and your software will behave as expected if there's an error and will continue to (all outward appearances) be functioning normally if something like this happens.

You will notice that the fiddle is for MySQL 8 and not MariaDB. With MariaDB (on this fiddle) I get a permission denied error if I try to run

ALTER TABLE foo ENGINE = BLACKHOLE;

I get message:

Unknown storage engine 'BLACKHOLE'

This is to be expected - from the MariaDB link:

  The BLACKHOLE storage engine was installed by default until MariaDB
  10.0. In MariaDB 10.1 and later, the storage engine's plugin will have to be installed.

Attempts to change the engine type and to install the plugin both failed (hardly suprising on a public-facing server).

INSTALL SONAME 'ha_blackhole';

gives:

INSERT command denied to user
'fiddle_PIIYJABQHVJRTTYOTJLC'@'localhost' for table 'plugin'


Unknown database corruption: Data error in cyclic redundancy check

If you are experiencing corruption, the first thing I would do is make sure you have a valid backup from a previous run on standby.  Restore that somewhere (if possible) so you have access to the data.

You will need to run a DBCC CHECKDB to see what exactly is going on.  If DBCC CHECKDB comes back with no problems, delete your backup file and try re-creating it again from scratch (it may be a corrupted backup file).

If DBCC CHECKDB shows errors, you can try running a repair_fast or a repair_rebuild on the database, but more than likely you will need to run a repair with the repair_allow_data_loss option.  As the option says, you may lose data.  If the problem lies with an index, just drop / recreate the index.  If the problem lies with a data page, then you will need to correct it with the repair_allow_data_loss option and deal with the loss.

After you correct the error, ensure the database page_verify option is set to CHECKSUM.

ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM

To find the lost data, you could try to take a new full backup and try using a data comparison tool against the old backup and the new backup to identify records that may have been lost.  This will be difficult and time consuming.  There may be a better way using the pages displayed in DBCC CHECKDB when the repair is run but someone else would have to fill in that part as I'm not too familiar with this.


Sqlcmd command strips $ out of query variable

Have you tried defining $InstanceName as a string literal with single quotations?

PS C:\> $GoodInstanceName = '$myInstanceName'
PS C:\> "Hostname$GoodInstanceName"
Hostname$myInstanceName

PS C:\> $BadInstanceName = "$myInstanceName"
PS C:\> "Hostname$BadInstanceName"
Hostname

In Powershell, string literals that are defined with double-quotes will resolve variables.  However, if they are defined with single-quotes, they will not.  Since $myInstanceName resolved in the $BadInstanceName example but has never been defined, it will simply resolve to an empty string.

I suspect this (or something similar) is the problem since the error seems to do with your completed path string.


How to get row number without windows functions in SQLite?

To sort group_concat() values, the original rows must come from an ordered subquery:

SELECT id,
       name,
       group_concat(cname) AS children_names
FROM (SELECT p.id,
             p.name,
             c.name AS cname
      FROM      tbl AS p
      LEFT JOIN tbl AS c ON p.id = c.pid
      WHERE p.pid = 0
      ORDER BY c.name = 'b',
               c.name)
GROUP BY id
ORDER BY name = 'b',
         name;


Will I have to restore controlfile in RMAN level 1 backupset before recover database?

The error might be a bit misleading.
It is correct to restore the control file from the latest backups assuming you want to recover to as late in time as possible. If your control_file_record_keep_time is long enough to also contain the backup pieces of the level 0 backup, rman will start restoring the level 0 backup and continue with the level 1 backup. If your level 0 backup is not visible in the restored control file you need to do the catalog so the info gets loaded into the control file.
The problem is with the ending of the recovery because the database keeps requesting archives until it sees the end of redo marker. This means that there was a clean shutdown. If everything is OK, you also have the archive log files, in backup or in the archive destination.
To get this database open after restoring the level 0 backup, you have to prepare the restore by setting until what moment the recovery has to be done. Without this setting the recovery will try to recover almost forever.
set until can be done until time or until sequence or until scn. It is my guess that "

Apr 13 21:06 inr01_0413_1_dbf_9fr2v0t0_1_1_RAC_20160413_42287

" is a backup of log sequence 42287 so your recovery would succeed if you specify
set until sequence 42287 {
restore database;
recover database;
}


Conditional relationships? One-to-many relationship using a pivot table for only a type of entity

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this way:

CREATE TABLE users (
  id serial NOT NULL PRIMARY KEY,
  name varchar(100) NOT NULL,
  user_type_id int NOT NULL REFERENCES user_types,
  home_id int NULL REFERENCES homes,
  -- a resident must have a home, a non-resident cannot have a home
  CHECK ((user_type_id = 1 AND home_id IS NOT NULL) OR
         (user_type_id <> 1 AND home_id IS NULL))
);


I have a few suggestions
SUGGESTION #1
You should use innodb_flush_log_at_trx_commit=2 instead. Why ?

With innodb_flush_log_at_trx_commit set to 0, a mysqld crash can lose up to 1 second of data.
With innodb_flush_log_at_trx_commit set to 2, an OS crash can lose up to 1 second of data. A mysqld crash can still keep that last second lingering in the OS until the tables and other moving parts of InnoDB are flushed to disk PROVIDED THE OS IS STILL UP.

See my post Is it safe to use innodb_flush_log_at_trx_commit = 2
SUGGESTION #2
You should use sync_relay_log=1 instead. Why ?
MySQL Documentation on sync_relay_log says:

Setting sync_relay_log to 0 causes no synchronization to be done to disk; in this case, the server relies on the operating system to flush the relay log's contents from time to time as for any other file.
Prior to MySQL 5.6.6, 0 was the default for this variable. In MySQL 5.6. and later, the default is 10000.
A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Since you did not set sync_relay_log, relay logs would flush to disk every 10000 binary log events (the default setting). In that case, you may as well set sync_relay_log_info to 100.
If setting sync_relay_log and sync_relay_log_info produces too much disk I/O you can adjust them to your liking. It seems inappropriate to me to use 10000 as the setting.
SUGGESTION #3
On the Master, make sure sync_binlog is set to 1 if the application is low-to-medium write. If it is heavy write, use sync_binlog is set to 100. Same thing with setting sync_master_info
SUGGESTION #4
With more than one slave, it would do you some good to setup of Semisynchronous Replication. That will give you added assurance that at least one Slave is sync'd and can be promoted to Master.
Please read my Aug 05, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?. It includes instructions on how to set it up as well as how it differs from regular MySQL Replication (asynchronous).
SUGGESTION #5
If any Slave has log-bin and log-slave-updates enabled, disable them if the Slave is not a Master. Don't be surprised by this suggestion. I have a client who would do midday bulk loads and Seconds_Behind_Master increase 1 second every 2 seconds for 6 hours. Disabling the binary log made all that replication lag go away quickly.
SUGGESTION #6
For slave0, check the following

battery on the disk controller
network traceroute
switches

GIVE IT A TRY !!!


OFFSET ... FETCH overlapping results on second page

This is not unexpected.  Your ORDER BY does not determine a complete (unique) order. As described in MSDN documentation:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

The underlying data that is used by the query must not change. ...

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

You have order by date but all the rows have the same value in date. To have determinate results with TOP / OFFSET ... FETCH, you have to use an  ORDER BY that completely determines an ordering.
You could use for example:
order by date asc,
         name asc

or (if (date, name) is not unique either:
order by date asc,
         eventID asc

or:
order by date asc,
         name asc,
         eventID asc

If the query is produced by some form and can be customized by the user, you could - behind the scenes - add the primary key column(s) (I guesseventID here) in the end of the order by list, so you have an order that is determinate and stays the same across different executions of the query.


What is the best way to store a lot of user-encrypted data?

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features.

Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing with this for over 20 years now - and it is worth making use of them just for that. In addition to this, relational databases get you the benefits of a long history of performance tuning. They run highly optimised native code so you don't have to struggle with poor Java implementations, bad network stacks, overuse of memcpy, garbage collection, coarse locking and all the other pathologies new products (especially the noSQL stuff) tends to have.

To store encrypted data, use the BINARY data types. MSSQL, MySQL and Postgres all support these types. You may not want to do operations directly on these types (though you CAN do binary manipulation if you want to). Also note that it is relatively easy to implement the encryption/decryption in the database itself  as all of the above support crypto libraries. You will also benefit from indexing on the key/foreign columns so you can find your data fast. A relational database is an excellent key/value store for small value types - SQL Server will easily get you over 1-10M key/value lookups/sec even on a small box - I expect MySQL and PostGres would deliver results in the same ballpark.

It is also easy to find programmers who can query a relational database efficiently. SQL has been around for a very long time and it is an extraordinarily powerful language. MSSQL will even give you automated parallel execution. Some programmers wont "get" it - but if they don't, chances are they also won't grok parallelism or lambda expressions (two crucial skills of a modern coder).

On top of all of this goodness, you also get powerful backup and monitoring tools for all of the standard relational databases.

So basically, unless you have a REALLY good reason to use NoSQL - just use relational databases.


Create a User table with a UserId PK and any other user related fields.
Create a Language with a LanguageId PK and the name of the language.
Create a Group table with a GroupId PK, UserId FK, LanguageId FK, Hook? and any group specific fields.
Create a BannerBox table with a BannerBoxId PK, GroupId FK, and any other Banner Box specific fields

This will let you get all the information you need assuming that languages are consistent for a group.

I don't know what your Hook field is so answering @ypercube's comment would be helpful.  In this answer I assumed it was some sort of business logic that was relevant at the group level.


How to best manage Index Builds with Stats Updates?

I use Ola Hallengren's indexing script and have the settings specified as per the below. I run this once a week through an Agent job.

Let's break down the command you're running:
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

Even once a week, this is absurdly aggressive. I know the 5/30% comes from ancient Microsoft advice, but it's just that --ancient. It's time to move on.
You're taking the time to defragment indexes that are 500 8KB pages. That's a 4MB table. If your hardware has issues reading 4MB into memory, or keeping a 4MB table in memory, the answer isn't index maintenance. That's why I crank this up to at least 5000.
Modern hardware like SANs, SSDs, and non-32-bit servers that can house amounts of RAM > 4GB simply don't have the same data access issues that spinning platter drives from the early 2000s had.
We're talking record players vs. CDs.
Index rebuilds should happen extremely infrequently, and only to either correct a problem with an index, or change a setting. Here's the thing: 30% fragmentation isn't a real problem. It's just something DBAs do because they've been told to, and it's something they can measure.
So here's my question to you: how much time and resources does your server expend on index maintenance, and how much does that reduce time and resources consumed by queries?
If you can measure that, you can rebuild and reorg as much as you want.

... but I have the following database properties set against all my databases which I believe should take care of updating stats...

Kinda sorta. Auto-update stats (even async) happens when 20% of the table data + 500 rows (assuming the table is > 500 rows) changes. If you have a table with a million rows in it, that's 200k rows. You can dynamically decrease that threshold by using Trace Flag 2371, but auto update stats uses the default sampling algorithm, which may not be enough for tables with heavily skewed data.

but update stats regularly is very general.

Well, yeah, I'm answering questions about a server I've never looked at. I prefer nightly stats updates, but I've seen servers that needed them more regularly than that.
So what should you do?

Start by dialing back index maintenance to the commands I posted in the question you're referencing
If you run into problems, decrease fragmentation thresholds until they stop. If no one says anything, run index maintenance less frequently, until maybe, just maybe, you never run it at all.

Update stats nightly
Start with the default threshold. If you find that the full stats update that occurs with a rebuild is necessary, use the CommandLog table to find which tables and indexes were regularly being rebuilt, and start focusing on those. They'll typically be indexes for "big" tables, with tens of millions of rows.

This is about as specific as I can get about a server I've never seen. You're going to have to take the experimentation from here.
See also my post Why most of you should leave Auto-Update Statistics on


In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow?

No it will not follow, you will be able to utilize the migrated database up until the 10GB limit.

I've written an article on maximizing space savings with regard to SQL Server Express, which may also be of interest to you to help reduce the your periodic data pruning further.

You may also want to consider upgrading again after 2014 to 2017 or 2019 to keep up with the latest advances in SQL Server features, stability, etc.


Is moving large columns to separate table likely to have significant performance improvement?

It is possible that these large string types are not held beside the other columns currently. See here. A forwarding pointer is held instead and dereferenced as required.

It may still be worth splitting out the columns, however, if

the key is smaller than the forwarding pointer; overall space saving, removes the pointer bytes from the row data.
you ever do select * 1; the large fields will not be pulled off disk
the columns are sparsely populated, written to on a different cadence than the other columns, or would benefit from a different indexing strategy.

Conversely  if the values are not too long (say a few hundred bytes) and accessed in most queries you may see a performance improvement by storing them in-row and avoiding the pointer lookup and secondary IO. This can be controlled through sp_tableoption.

1 Not that you ever would, of course.


How to get the current date without the time part

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.


Schema qualification necessary for query plan reuse?

For a plan to be reused all of the attributes in sys.dm_exec_plan_attributes where is_cache_key=1 must be the same. A list of these is below.

acceptable_cursor_options
compat_level
date_first
date_format
dbid
dbid_execute
is_replication_specific
language_id
merge_action_type
objectid
optional_clr_trigger_dbid
optional_clr_trigger_objid
optional_spid
required_cursor_options
set_options
status
user_id

The one affected by using two part names is user_id

If you try the following under the credentials of a user with default schema dbo

DBCC FREEPROCCACHE;

CREATE TABLE dbo.FooBar(X int);

EXEC('SELECT * FROM FooBar');
EXEC('SELECT * FROM FooBar');

EXEC('SELECT * FROM dbo.FooBar');
EXEC('SELECT * FROM dbo.FooBar');

And then execute the following query

SELECT usecounts,
       text,
       value AS [user_id]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE text LIKE 'SELECT * FROM %FooBar'  and attribute='user_id'

You will see the following results

usecounts   text                                user_id
----------- ----------------------------------- -------
2           SELECT * FROM dbo.FooBar            -2
2           SELECT * FROM FooBar                1

This shows that both plans got re-used when the identical statement got run for the second time. The docs for sys.dm_exec_plan_attributes explain for the user_id

  Value of -2 indicates that the batch submitted does not depend on
  implicit name resolution and can be shared among different users. This
  is the preferred method. Any other value represents the user ID of the
  user submitting the query in the database.

This appears to be incorrect! It seems from my testing that the value it actually uses for the user_id in the second case is the schema_id of the default schema for the executing user rather than an identifier for that specific user. Running the four EXEC statements again under a different login with default schema "dbo" gives.

usecounts   text                                user_id
----------- ----------------------------------- -------
4           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1

Showing the plans for both versions of the query were able to be re-used between users. Finally running the four EXEC statements again under a third login with default schema "guest" gives.

usecounts   text                                user_id
----------- ----------------------------------- -------
6           SELECT * FROM dbo.FooBar            -2
4           SELECT * FROM FooBar                1
2           SELECT * FROM FooBar                2

Showing that the plan for the dbo qualified query was successfully shared between the users with different default schemas but the non schema qualified query needed a new plan compiled.

If you don't see this sharing happening ensure that all logins you are testing have the same set_options,language_id, date_first,date_format as these are among the cache keys listed at the beginning and any differences in those  will prevent the plans being reused between sessions.


Significant problem 1 - table_open_cache
Table cache hit rate: 0% is significantly bad. Identify how many tables are in Magento and increase  the table_open_cache to higher than that.
open_files_limit needs to be higher than this number however (as it includes connections also). High number like 64k are ok.
After that enable your slow_query_log, long_query_time and start identifying slow queries.
As you do have 16G of RAM setting innodb buffer pool size to 4G would be prudent as notably your data size is only 1.2G.
Examine performance again after these changes.


SQL Server Query Store - What is considered an 'ad-hoc' query?

After a little bit of searching, I wasn't able to find a concrete and satisfying source from Microsoft's documentation to answer this question. There are many good third-party descriptions and definitions of adhoc/ad-hoc/ad hoc out there, but for the specificity of this question I think one close to the source is ideal.

Moving past generic (but still accurate) definitions like this SO post (thanks SqlWorldWide), if we look at what the documentation does say on the topic, it agrees with what you've mentioned about the definition being based off of the number of executions, I think to the point that we can take it as fact.

The article on optimizing for ad hoc workflows says,

  When this option is set to 1, the Database Engine stores a small
  compiled plan stub in the plan cache when a batch is compiled for the
  first time, instead of the full compiled plan. This helps to relieve
  memory pressure by not allowing the plan cache to become filled with
  compiled plans that are not reused. The compiled plan stub allows the
  Database Engine to recognize that this ad hoc batch has been compiled
  before but has only stored a compiled plan stub, so when this batch is
  invoked (compiled or executed) again, the Database Engine compiles the
  batch ... and adds
  the full compiled plan to the plan cache.

So it would appear the the ad hoc query server configuration option also uses the definition of a single execution as the definition of ad hoc. If the query continues to get executed and generates the same plan it would no longer be treated as such.

The article on Best Practices for Query Store also  aligns with this,

  Compare the number of distinct query_hash values with the total number
  of entries in sys.query_store_query. If the ratio is close to 1 your
  ad-hoc workload generates different queries.

This, of course, is for queries that are not already used as stored procedures, parameterized, etc. because they can be recognized and treated appropriately right off the bat.

So based on all this we can say that a query is treated as ad hoc if:

It is not parameterized
It is not programmatically stored in the database (stored proc, function, trigger, etc.)
The same query is only executed one time
OR The same query is executed multiple times but generates a different query plan for each subsequent execution.


GRANT IMPERSONATE ON LOGIN::admin to notadmin is telling SQL Server that you want to give the IMPERSONATE permission to the notadmin account so that it can IMPERSONATE other users. It sounds like your goal is the opposite, that you want to impersonate the notadmin account from the admin account. You already are accomplishing this when you run the SQL code EXECUTE AS LOGIN = 'notadmin' (when you're logged in as your admin account).


First, a query or (update) statement with a condition like WHERE posts < comments that compares 2 columns cannot effectively use your indexes so it will probably have to do a full table scan. It might be better if you had a composite index, (posts, comments) or the other way around, but it would still need to do a full index scan.

If the rows to be updated are very few, say 100, it's not very efficient to scan 1 million rows to update only 100.

So, the easiest thing would probably be to add such a composite index and test. If the resulting efficiency from the full index scan is acceptable, you can keep using this query.

Another thing you could do - since you are using MariaDB - is to add a VIRTUAL (computed) column and index it:

ALTER TABLE posts
  ADD COLUMN comment_posts_diff INT AS (columns - posts) PERSISTENT,
  ADD INDEX more_comments_ix (comment_posts_diff) ;

Then you only need to change the condition of your queries to use:

WHERE comments - posts > 0

or:

WHERE comment_posts_diff > 0

and the index will be effectively used.

Note that there are 2 variants of computed columns, the VIRTUAL and the PERSISTENT ones. As it's obvious from the names, the persistent columns
actually take space on disks and the values are automatically modified when any of the columns in their definition is modified. Their advantage is that they can be indexed.

If the 'more comments' though is just a constant string that applies to rows with comments > posts and there is another string ('less comment'?) that applies to the rest of the rows or a similar need, you don't really need the column notes and any of the above. Your queries could just compute the respective value (more comments, less comment, no comments) at run time.

Or you could have a computed column to do the work for you (vitual this time, not persistent):

ALTER TABLE posts
  DROP COLUMN notes,
  ADD COLUMN notes VARCHAR(20) AS
      CASE WHEN columns > posts THEN 'more comments'
           WHEN columns = 0     THEN 'no comments'
           WHEN columns = posts THEN 'comments = posts'
           ELSE 'less comments'
      END
    VIRTUAL ;


SQL Server Autonomous transaction logging

I am also an Oracle developer. Savepoints are for partial rollback. Oracle autonomous transactions are for partial commits.

Oracle autonomous transactions work like below.

Start Transaction1
/*DMLs*/
    Start Transaction2
    /*DMLs*/
    COMMIT only Transaction2 and end Transaction2
ROLLBACK only Transaction1 retaining changes committed by Transaction2 and end Transaction1

It seems that SQL server does not allow partial commits. Please check NestedTransactions. When an outer transaction is rolled back, the committed inner / nested transactions are also rolled back.


You need to separate the 3 ordering cases:

whether the column starts with 'zx' or not
order by name for the first group
order by id for the second group

Then because the first one needs a case expression, you need to use the same case expression in the following two, with the different orders and null accordingly:

order by
    case when name like 'zx%' then 0 else 1 end,        -- 1st ordering column
    case when name like 'zx%' then name else null end,  -- 2nd
    case when name like 'zx%' then null else id end     -- 3rd

The 1st ensures that all rows that match the like 'zx%' condition will be first and the rows that don't match afterwards.

The 2nd column will be used for ordering by name the first group (rows that match) and ignored for the second group (rows that don't match) because it will be null for all of them.

The 3rd column will be ignored for the first group (rows that match) as it will be null and the rows will already have been ordered anyway by the 1st and 2nd column. But ti will be used for ordering by id the second group (rows that don't match).


Is the mapping between PostgreSQL system tables and information_schema views documented?

The system tables are the implementation of database objects.  So if you, say, call a function foo(), the DBMS looks in pg_proc to see if there is a function foo and what the arguments and the source code and so on are.  The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs.  In many cases, there is a simple mapping, say, between a function and pg_proc.  But in other cases, such as for an index, it's a bit more complicated.  You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.

The information schema is specified by the SQL standard.  The principle there is, if you enter these DDL commands, then a query of the information schema should give these results.  In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases.  So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless.  The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.

Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables.  The reality is too complicated for this to be simpler.  (I think.  Send a patch if you have a better idea.)


In general, this will take a time, anyway this time can be reduced by some ways, for example you can lock the table and unlock it when to finish..

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)
Closing: (1)

Please read full article form the following Link by MySql dev Team:
https://dev.mysql.com/doc/refman/5.0/en/insert-speed.html


Joining Three Tables Using Composite Key

If we were dealing with 0-1 income_rec rows and 0-1 advance_billing rows for any given acct_rec row, and if AR_INV_NBR, AR_INV_SEQ, and AR_TYPE uniquely identify a single acct_rec row, then this is trivial:

SELECT ...
  FROM acct_rec ar
         LEFT OUTER JOIN income_rec inc ON (    ar.AR_INV_NBR        = inc.AR_INV_NBR
                                            AND ar.AR_INV_SEQ        = inc.AR_INV_SEQ
                                            AND ar.AR_TYPE           = inc.AR_TYPE
                                            AND ar.JOB_NUMBER        = inc.JOB_NUMBER
                                            AND ar.JOB_COMPONENT_NBR = inc.JOB_COMPONENT_NBR
                                           )
         LEFT OUTER JOIN advance_billing bill ON (    ar.AR_INV_NBR = bill.AR_INV_NBR
                                                  AND ar.AR_INV_SEQ = bill.AR_INV_SEQ
                                                  AND ar.AR_TYPE    = bill.AR_TYPE
                                                 )

However, we apparently have to deal with the possibility that we have multiple rows in each of the three tables with the same three key values (AR_INV_NBR, 'AR_INV_SEQ, andAR_TYPE`). This would allow cross-products amongst the various tables to come in, which is probably the issue you've had.

Taking a relatively simple example, where there is 1 row in acct_recwith our three key values, 3 rows in income_rec, and 2 rows in advance_billing. The LEFT JOIN approach above would give us six rows:

ar + inc(1) + bill(1)
ar + inc(1) + bill(2)
ar + inc(2) + bill(1)
ar + inc(2) + bill(2)
ar + inc(3) + bill(1)
ar + inc(3) + bill(2)

Aggregates would result in twice the expected values for the income_rec columns, and 3 times the expected values for the advance_billing columns.

So, we need a solution where only one of the JOINed tables is allowed to have multiple rows.

The script below:

creates a temp table with one unique row for each of the AR_INV_NBR-AR_INV_SEQ-AR_TYPE combinations seen in acct_rec;
generates the actual results by:

creating a sub-query to match our unique account records with all matching records from advance_billing, aggregating the results so we have one row for each acct_rec row;
take our unique account temp table, and LEFT JOIN it with income_rec, and with our aggregated advance_billing sub-query;
and aggregate the income_rec values, to handle multiple rows from that table (by this point, we've combined any multiple rows from acct_rec and advance_billing into a single row).

Here's the query:

CREATE TABLE #ar
     (
       [AR_INV_NBR] [int] NOT NULL
                ,[AR_INV_SEQ] [smallint] NOT NULL
                ,[AR_TYPE] [varchar](3) NOT NULL
                ,[AR_INV_DATE] [datetime] NULL
                ,[AR_INV_AMOUNT] [decimal](15, 2) NULL
                ,[AR_POST_PERIOD] [varchar](6) NULL
                ,[AR_ADVANCE_AMT] [decimal](15, 2) NULL
     )
;

-- populate a temp table with 1 unique row
--   for each AR_INV_NBR, AR_INV_SEQ, AR_TYPE in acct_rec
INSERT INTO #ar (AR_INV_NBR, AR_INV_SEQ, AR_TYPE, AR_INV_DATE, AR_INV_AMOUNT, AR_ADVANCE_AMT)
SELECT AR_INV_NBR, AR_INV_SEQ, AR_TYPE
      ,MAX(AR_INV_DATE)
      ,SUM(AR_INV_AMOUNT)
      ,SUM(AR_ADVANCE_AMT)
  FROM acct_rec
 GROUP BY AR_INV_NBR, AR_INV_SEQ, AR_TYPE
;

-- Update temp table with the maximum post period from the acct_rec row(s)
--   with the maximum date for each AR_INV_NBR, AR_INV_SEQ, AR_TYPE
-- Don't want to jsut take MAX of all post periods, in case we've got rows
--   that fall before and after the restart of the post period count
--   (something that could possibly happen at year-end, quarter-end, daily, or never).
UPDATE ar
   SET AR_POST_PERIOD = pp.AR_POST_PERIOD
  FROM #ar ar
         INNER JOIN (SELECT t.AR_INV_NBR, t.AR_INV_SEQ, t.AR_TYPE, t.AR_INV_DATE
                           ,MAX(act.AR_POST_PERIOD) as AR_POST_PERIOD
                       FROM #ar t
                              INNER JOIN acct_rec act ON (    t.AR_INV_NBR  = act.AR_INV_NBR
                                                          AND t.AR_INV_SEQ  = act.AR_INV_SEQ
                                                          AND t.AR_TYPE     = act.AR_TYPE
                                                          AND t.AR_INV_DATE = act.AR_INV_DATE
                                                         )
                    ) pp ON (    ar.AR_INV_NBR  = pp.AR_INV_NBR
                             AND ar.AR_INV_SEQ  = pp.AR_INV_SEQ
                             AND ar.AR_TYPE     = pp.AR_TYPE
                            )
;

-- Now that we know we have one unique row for each set of values in acct_rec,
--   we can bring in the values from the other tables.

SELECT ar.AR_INV_NBR, ar.AR_INV_SEQ, ar.AR_TYPE
      ,ar.AR_INV_AMOUNT, ar.AR_ADVANCE_AMT, ar.AR_INV_DATE, ar.AR_POST_PERIOD
      ,SUM(inc.REC_AMT) as REC_AMT
      ,bill.HOURS, bill.QUANTITY, bill.LINE_TOTAL
  FROM #ar ar
         LEFT  JOIN INCOME_REC ir ON (    ar.AR_INV_NBR = inc.AR_INV_NBR
                                      AND ar.AR_INV_SEQ = inc.AR_INV_SEQ
                                      AND ar.AR_TYPE    = inc.AR_TYPE
                                      AND EXISTS (SELECT 1 FROM acct_rec
                                                   WHERE AR_INV_NBR = inc.AR_INV_NBR
                                                     AND AR_INV_SEQ = inc.AR_INV_SEQ
                                                     AND AR_TYPE = inc.AR_TYPE
                                                     -- Drop the following two lines if they won't actually eliminate anything
                                                     AND JOB_NUMBER = inc.JOB_NUMBER
                                                     AND JOB_COMPONENT_NBR = inc.JOB_COMPONENT_NBR
                                                 )
                                     )
         LEFT  JOIN (SELECT ar1.AR_INV_NBR, ar1.AR_INV_SEQ, ar1.AR_TYPE
                           ,SUM(bill_1.HOURS) as HOURS
                           ,SUM(bill_1.QUANTITY) as QUANTITY
                           ,SUM(bill_1.LINE_TOTAL) as LINE_TOTAL
                       FROM #ar ar1
                              INNER JOIN ADVANCE_BILLING bill_1 ON (    ar1.AR_INV_NBR = bill_1.AR_INV_NBR
                                                                    AND ar1.AR_INV_NBR = bill_1.AR_INV_NBR
                                                                    AND ar1.AR_TYPE    = bill_1.AR_TYPE
                                                                   )
                      GROUP BY ar1.AR_INV_NBR, ar1.AR_INV_SEQ, ar1.AR_TYPE
                    ) bill ON (    ar.AR_INV_NBR = bill.AR_INV_NBR
                               AND ar.AR_INV_NBR = bill.AR_INV_NBR
                               AND ar.AR_TYPE    = bill1.AR_TYPE
                              )
 GROUP BY
       ar.AR_INV_NBR, ar.AR_INV_SEQ, ar.AR_TYPE
      ,ar.AR_INV_AMOUNT, ar.AR_ADVANCE_AMT, ar.AR_INV_DATE, ar.AR_POST_PERIOD
      ,bill.HOURS, bill.QUANTITY, bill.LINE_TOTAL
;

NOTE: This query may be overly complicated, because I've made assumptions to try to make it cover the worst-case scenarios. If AR_INV_NBR, AR_INV_SEQ, AR_TYPE act as a unique key on the acct_rec table, then the #ar temp table can be dropped. If the stated JOIN using JOB_NUMBER and JOB_COMPONENT_NBR between acct_rec and income_rec is not needed (for example, if there's no chance that we'd find an income_rec row where the two values don't match up to any rows in acct_rec), then the EXISTS clause in the income_rec JOIN can be dropped.

ALSO NOTE: by the same token, the code does assume that JOB_NUMBER and JOB_COMPONENT_NBR are actually in acct_rec (the  DDL posted doesn't include them), and those two columns in advance_billing should be ignored. If either assumption is incorrect, the query could change significantly.


HADR wait on servers without HA configured

Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?

According to Paul Randal's wait stats library entry for this, waits are to be expected whether AGs are configured or not:

  This wait occurs every 0.5 seconds for 0.5 seconds, regardless of whether any availability groups are configured.

Note however that this wait type was introduced in SQL Server 2012, so if your "live server" is an older version, you won't see this wait type on it.

  Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

It's also not surprising that the wait duration is similar for these three waits, as they each occur on a routine schedule.

  Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)

Unfortunately, these three specific waits are very unlikely to be the culprit for your slower-than-expected data load.  I would filter them out, and check wait stats again.  Consider filtering out all "benign" wait stats (example here: Wait statistics, or please tell me where it hurts) and reviewing what's high.


SSIS column cannot convert between unicode and non-unicode string data types

Double click the line between the source and destination and select the Metadata tab. That will list all of your columns and the data types SSIS has inferred.

I would compare that to your source and 100% ensure they align.

What can happen is that the first time you built your data flow, SSIS will set the metadata based on what the types are at that point. col1 is interpreted to be nvarchar so it'll set it as DT_WSTR of some length.

Time passes and you update the query and the source is now varchar, but still called col1. A varchar will always fit into an nvarchar so the shape of that column hasn't "changed" so SSIS doesn't need to redo it's internals.

If it is the case that SSIS is clinging to old metadata, my click path is to

Copy the existing query
Change my query to SELECT 1 as TotallyNewAndUniqueColumn
SSIS will then report that all my metadata is out of whack and would I like to autofix it, and I click Yes
Repaste the original query and lo and behold, the current metadata is captured.


Don't remove ibdata1.  That may be fine for one table, but once you have two tables, you will be in trouble.  Also don't remove an .ibd file if you have innodb_file_per_table.

What is the size of innodb_buffer_pool_size?  Unless you have a small RAM, it should be set to about 70% of RAM.  This is a very important speed setting for InnoDB.

A better way to empty out a table:  TRUNCATE TABLE.  If you will frequently be reloading this table, the have innodb_file_per_table = ON when you create it.

14KB per row, including a BLOB?  That is past one critical threshold.  Please show us SHOW CREATE TABLE and SHOW TABLE STATUS.  There are some important factors to note before discussing this further.

If you ultimate goal is to completely replace the table periodically, then do it this way:

CREATE TABLE new_tbl   (and use either `LIKE real_tbl` or spell out the schema)
Load the data into new_tbl
RENAME TABLE real_tbl TO old_tbl, new_tbl TO real_tbl;
DROP old_tbl;

The RENAME is atomic and instantaneous.  With innodb_file_per_table = ON, you will recoup any free space from the old copy.


psql command output to print error via -L output.log

The file specified to -L cannot be made to include errors.
To have errors in the same log file than queries and results, both outputs should be redirected to the same file. For example:
$ cat >script.sql
select 1/0;

select count(*) from pg_class;

$ psql -e -f script.sql &> output.log

$ cat output.log
select 1/0;
psql:script.sql:1: ERROR:  division by zero
select count(*) from pg_class;
 count
-------
   352
(1 row)

See psql --help for more options:

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)


structure of query does not match function result type

It seems that question_type is defined as varchar(80) but you as defined question as text.

Simply cast it as text:

select question_type::text


Depending on what version of SQL server you are running you can initialize via a backup. However, Microsoft has recommendations as to when it's appropriate to use this processes. Also need to make sure your security is setup properly as well.


Building a custom SQL Server 2017/2019 container on a windows server without internet

SQL Server 2019 Windows containers are still in preview as of this writing. If you don't join the Early Adopter program, you can create a custom image by starting with a Windows Server Core base image and then install SQL Server 2019 (and optionally CUs) to create a custom image.
The build is most easily done on a machine with internet connectivity to pull the base OS image from the public repro. You can then copy the image to the machine without an internet connection (or push it to a local private repro) and run it from there. Alternatively, pull, save, and load the OS images (steps 6-8) and run the build on the server.
Below is an example of the general steps to do this. My appologies for the formatting. I'm mardown-challenged.

create an empty directory on your Windows machine (e.g. C:\dockerfiles)
 MKDIR C:\dockerfiles

create a dockerfile in that directory with the following contents
 #Start with a Windows Server Core base image.
 #Note that the image with tag 'latest' is pulled by default, which could be older than the actual host OS version.
 #In this case, specify the version-specific to pull a later image compatible with the host OS version.

 FROM mcr.microsoft.com/windows/servercore:2004

 #Parameters for start.ps1:
 ENV sa_password="_" \
     attach_dbs="[]" \
     ACCEPT_EULA="_" \
     sa_password_path="C:\ProgramData\Docker\secrets\sa-password"

 #set default shell to PowerShell and set preferences:
 SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

 #set current directory to root (C:\)
 WORKDIR /

 #copy SQL Server setup folder into build image for installation
 COPY setup setup

 #run setup to install
 RUN  \setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS

 #remove setup files
 RUN  Remove-Item -Force -Recurse -Path 'setup'

 #create empty directory (C:\SqlFiles) for user database files
 RUN "mkdir SqlFiles | Out-Null"

 #stop SQL Server service, configure TCP/IP, allow SQL authentication, and set default file locations
 RUN stop-service MSSQLSERVER ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value '' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433 ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2 ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name BackupDirectory -value 'C:\SqlFiles' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultData -value 'C:\SqlFiles' ; \
         set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultLog -value 'C:\SqlFiles' ;

 #set command used for container health check status
 HEALTHCHECK CMD [ "sqlcmd", "-Q", "SELECT 1;" ]

 &#35;copy startup script to image
 COPY start.ps1 /

 #specify command invoked when container is run from this image
 CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

create start.ps1 file in build directory (e.g. C:\dockerfiles\start.ps1). Below is an example script similar to the one I use.
 # The script sets the sa password and start the SQL Service
 # Also it attaches additional database from the disk
 # The format for attach_dbs

 param(
 [Parameter(Mandatory=$false)]
 [string]$sa_password,

 [Parameter(Mandatory=$false)]
 [string]$ACCEPT_EULA,

 [Parameter(Mandatory=$false)]
 [string]$attach_dbs
 )

 if($ACCEPT_EULA -ne "Y")
 {
     Write-Verbose "ERROR: You must accept the End User License Agreement before this container can start."
     Write-Verbose "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

     exit 1
 }

 #start the service
 Write-Verbose "Starting SQL Server"
 Start-Service MSSQLSERVER

 if($sa_password -eq "_") {
     if (Test-Path $env:sa_password_path) {
         $sa_password = Get-Content -Raw $secretPath
     }
     else {
         Write-Verbose "WARN: Using default SA password, secret file not found at: $secretPath"
     }
 }

 if($sa_password -ne "_")
 {
     Write-Verbose "Changing SA login credentials"
     $sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
     & sqlcmd -Q $sqlcmd
 }

 $attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

 $dbs = $attach_dbs_cleaned | ConvertFrom-Json

 if ($null -ne $dbs -And $dbs.Length -gt 0)
 {
     Write-Verbose "Attaching $($dbs.Length) database(s)"

     Foreach($db in $dbs)
     {
         $files = @();
         Foreach($file in $db.dbFiles)
         {
             $files += "(FILENAME = N'$($file)')";
         }

         $files = $files -join ","
         $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"

         Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
         & sqlcmd -Q $sqlcmd
     }
 }

 Write-Verbose "Started SQL Server."

 Write-Verbose "Changing SQL Server server name to $($env:COMPUTERNAME)"
 $sqlcmd = "EXEC sp_dropserver @@SERVERNAME;EXEC sp_addserver N'$env:COMPUTERNAME', 'local';"
 & sqlcmd -Q $sqlcmd

 Write-Verbose "Restarting SQL Server."
 Stop-Service MSSQLSERVER
 Start-Service MSSQLSERVER
 Write-Verbose "Restarted SQL Server."

 Write-Verbose "Tailing log..."
 Get-Content -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Wait

copy files from SQL 2019 installation media to a setup subdirectory (e.g. C:\dockerfiles\setup)

run docker build in the contest of the build directory (e.g. C:\dockerfiles) to build and tag the custom image
 docker build -t mssql-2019-windows-dev-rtm-example .

save the image to a tar file
 docker save --output "C:\temp\mssql-2019-windows-dev-rtm-example.tar" mssql-2019-windows-dev-rtm-example

copy the image to the Windows server
 copy "C:\temp\mssql-2019-windows-dev-rtm-example.tar" "\\YourServer\c$\temp\mssql-2019-windows-dev-rtm-example.tar"

load the image from the tar file on the Windows server machine:
 docker load --input "C:\temp\mssql-2019-windows-dev-rtm-example.tar"

example to run the image
 docker run -d -p 14331:1433 -h sql2019 --name sql2019 -e "sa_password=y0ourS@pAssword" -e "ACCEPT_EULA=Y" -e attach_dbs="" --rm mssql-2019-windows-dev-rtm-example:latest

Be aware Windows containers are not yet supported on production and you are on your own with SQL 2019 images at this time unless you join the EA preview.


MongoDB: Is it possible to search for two fields whose array sizes don't match?

Is this possible with the $size operator? ... so my Plan B will be to
  add two separate fields called numAccepted and numInvited ...

The $size operator matches any array with the number of elements specified by the argument. In this case you have to compare the size of two array fields within the same document. You cannot use MongoDB Query Language (MQL) operators for this purpose.

The aggregation $size array operator returns the length of an array. To use aggregation operators within a find query, you have to use the $expr operator. Here is how it is done:

db.collection.find( { $expr: { $ne: [ { $size: "$customersInvited" }, { $size: "$customersAccepted" } ]  } } )

Note the $size and the $ne used in the above query are aggregation operators.


You need to use SQL baselines to force the execution plan.

This blog describes the steps involved. You'll need the sql_id for the statement and the plan_hash_value of the old plan.


What providers are available on your SQL Server?In SSMS --> Server Objects --> Linked Servers --> Providers

Do you see an Oracle provider? If not install the provider which I believe you can find here:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

If that doesn't work you can try this:
Create an ODBC connection on the server, and then create a linked Server with Microsoft OLE DB for ODBC drivers. Kind of a dirty way to do it, but it should work if you don't have the ability to install the oracle provider.


Inconsistent information from different DMVs

There is no inconsistency:

  1048576 * 8 = 8388608 KB => 8 GB;

Both Perfmon and sys.dm_os_sys_info show 8 GB;

[bpool_visible], [bpool_commit_target] and [bpool_committed] - in SQL 2005 all these are defined as "Number of 8-KB buffers ..." (http://msdn.microsoft.com/en-us/library/ms175048(v=sql.90).aspx), so you have to multiply the result by 8 to get KB; Starting with SQL 2008 R2 you no longer have to do the multiplication.

If you want to use a multiplication within the query try:

SELECT CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB
    , CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB
    , CAST((bpool_visible * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_visible_memory_in_GB
FROM sys.dm_os_sys_info;


SQL to combine records only if data is available

SELECT id,
description1 = MAX(description1),
description2 = MAX(description2)
FROM dbo.MyTable
GROUP BY id
ORDER BY id;


global foreign_key_checks = 0 on RBR slave

To answer my own question, I tried 'set global foreign_key_checks=0' on the slave. This didn't work, as the statements generated for my RBR binlog explicitly set foreign_key_checks=1.

So, I may try option 3.


Check the Creating an Access Control List for External Network Services documentation (look at step 2, after the parameter descriptions):

  Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range.

Your second assignment simply replaces the first. You can only have one ACL file per host/domain.


Direct database interaction with memory - how it works?

Let's talk about MySQL/MariaDB with ENGINE=InnoDB only.  (That is virtually the only Engine in use today.)

All InnoDB activity happens in the buffer_pool.
All data and each secondary index is composed of 16KB blocks.
Those blocks are brought into the buffer_pool as needed, and flushed out as needed.
It uses a LRU (Least Recently Used) algorithm for controlling which blocks to bump out.  (There are minor tweaks to LRU, so it is not exactly LRU.)

Hence, it is very important go give InnoDB most of RAM, then let it do its thing.  A simple rule of thumb:  innodb_buffer_pool_size should be set to about 70% of RAM.  For most applications, that is the most important setting and the only one that needs to be changed from the default.

Setting the buffer_pool too large could lead to swapping.  But, since InnoDB treats it as a big array, that could cause a terrible amount of swapping, hence slowing down MySQL/MariaDB terribly.

InnoDB has threads for reading writing blocks.  In particular, flushing to disk is somewhat done "in the background".  This allows an INSERT to appear to be faster than it really is.

I discuss memory allocation further in here

Using "virtual memory" for accessing a file is a lazy way to do things.  It works OK for simple cases.  However, a database engine needs to be efficient and fast.  When the code knows the access pattern, it is best to use an access method that is tuned for such.

For example, if you are going to read sequentially through a file, it is best (for memory management) to ping-ping between two 'small' buffers; one being read into and one being parsed.  (A single "circular" buffer is a good alternative.)

  How exactly does this direct communication takes place?

Because everything is in 16KB blocks, there is a number that uniquely identifies such.  It is rather large (20? bytes).  It includes at least the "tablespace" number, and block number within the table space.  This is taken modulo the number of buffer_pool_instances to decide which instance to look in.

Within a block, there are rows of the table or rows of a secondary index, or rows of a node in the B+Tree (see Wikipedia) that it represents.

If the block is not in the buffer_pool, it must wait while a read occurs.  But, before the read can occur, there might need to be a write to flush out some block.  Normally, a background thread(s) flushes out "dirty" blocks so that there is always some free blocks in the buffer_pool.  ("Free" or "easily removed" because they are not dirty.)


Generally, an index should be on

(equalitycol1, equalitycol2, ..., nonequalitycol1, , nonequalitycol2, ...)
INCLUDE
(outputonlycol1, outputonlycol2, ...)

Simply (ignoring ranges) this breaks down into

equalitycol: WHERE equalitycol = something. Most selective first. SARGable. Seeks.
nonequalitycol: WHERE nonequalitycol <> something. Residual filters
outputonlycol: SELECT outputonlycol. No filtering or ordering

ORDER BY and GROUP BY columns will be equality or non-equality columns.

In this case...

Query 1 doesn't match the index at all, really, so most likely won't use the index. However, the optimiser may decide it's cheaper to use the index then use other table access such as a table scan or a RID/bookmark lookup.

For query 2, the optimiser may decide to use the index because col1 would be an equality match. And then use the unsorted col5 from the INCLUDE. YMMV, but there is a higher chance.

For the WHERE clause order, no: the optimiser works it out. Two indexes (col1, col2) and (col2, col1) are different though. Note (col1, col2) will be contained by (col1, col2, col3, col4) though so the first isn't needed.


Storing not very volatile information in database with cache or mongodb

The answer as always is: it depends ;)

With a bit of simplification: Indexes and recently read (and written) documents are kept in RAM until that RAM is needed for something else. So wether your data is read from RAM or from file pretty much depends wether RAM on the MongoDb node(s) is sufficient to keep the index and at least part of your data (called the working set) in RAM. It is safe to say that the only thing that can replace having sufficient RAM in a MongoDB installation is to have more RAM.

So the question is: how much is sufficient RAM? And the answer again is: it depends. On the size of your database, the number of documents in it's collections, the number of indices you need. It is by no means easy to figure out the right values and that is very dependent on the situation, but I will try to give you some rules of thumb.

Whenever possible, use SSDs. Rather take half the amount of RAM and SSDs than double the amount of RAM and HDDs.
Generate a reasonable fraction of your expected data size (or use a reasonable fraction of it if you already have data). The values can be completely random, though their size should be somewhat realistic (you rarely have company names 255 characters long, for example)
Create the indices as you expect to need them. Rule of thumb: generate an index for every field or combination of fields you expect to query for. Keep in mind that a field in an compound index will be used if you query for that field alone. You really need to put some effort in this step.
Sum up the index sizes.
If your combined index size for the test data exceeds your physical RAM, put some more into the machine, at least 1.5 times the value of your combined index sizes.
Run your performance tests.

While this sounds a bit complicated, it may save you from developing (and maintaining!) a whole layer in your application. This easily sums up over time.

Note for general scaling: Calculate the index sizes for your expected data set. Multiply that value by 1.5 at least. I'd rather suggest multiplying by >3. Take this as your memory size. The factor is actually determined by your growth factor and limited by the sweet spot where your hardware delivers the most bang for the buck. If your RAM requirements are exceeding the RAM of your hardware sweet spot, you should shard your cluster right away.


Is it a good idea to use Synonyms to avoid creating a duplicate table?

Why do you have 3 databases with the same users?

What happens if one database goes down now?

I'm asking these questions because the Con of the user-source database going down preventing use of the other two databases may not be as big a problem as it sounds.

Additionally, if a database goes down now you will already have consistency problems if users are modified in the other two DBs during that period. I don't think we can give the best advice without more context.

Right now I would create a fourth database for users, make changes only there, and sync across the other databases. You're already distributed-denormalized by changing essentially the same data in three places, and probably already have consistency problems. If fault tolerance is important then this scheme still gives that while solving the multiple entry problem.

I think that having this fourth user/settings-only database rather than using one of your existing ones is a good strategy as it becomes less likely to go down since it isn't tied to other resources or heavily used. I see now that you said you can't do this, but I'm not clear on why. Do the applications on the main database support user editing directly, or is user editing a completely separate function that can be pointed elsewhere?

True, with this "canonical user table" idea--whether you use synonyms or synchronize the data--you can't modify users during a down user DB, but that seems ok to me. Fix the problem and bring it up! One source, one place to edit, one thing to fix if broken. With synchronization, all other databases have temporary copies they can work from, but no editing. Minimizing data duplication across systems is a great and useful goal. It is a serious problem to enter the same data in three places, so do whatever you can to eliminate that.

To address more of your comments, if your user IDs are different across all your applications, then you should seriously consider a planned down time for your two new databases to bring them in sync with the main one (ask a separate question if you need ideas on how to accomplish this, which is tricky but not really THAT hard).

If you analyze your business needs and find that the main database must have the user data that's okay, you still use it as canonical, then choose between synonyms or synchronizing to address how unplanned down times affect all the databases.

One additional Con to using synonyms is that you would no longer be able to have proper FKs to Users in each database.


Configure SQL Server Database for High Availability & Warehousing

Quick points:

-Don't setup a separate 'transaction log backup' job outside of the 'log shipping' architecture.  Doing so will break log shipping.  Let log shipping handle all of your transaction log backups.

-You can use log shipping in 'STANDBY' mode but note that the instance will not be accessible when the server is doing a log restore.  You can set the frequency in which to do log restores (for example, take a transaction log file backup every 5 minutes, restore to the reporting server every 4 hours) which should minimize connectivity issues.

-Paul Randall will probably kill me for this, but I have a 'mirrored' instance with a snapshot taken on it every 30 minutes.  All queries are ran on the snapshot.  I have not had a single complaint in 4 months of keeping this up.  This was a 'intermediary' step before we could setup a replicated copy but it's working very well.  Again, Paul Randall does not like this solution but it works for me thus far in this limited scenario (whilst being aware that often a limited scenario will turn out to be permanent solution).

-Have you considered a daily 'snapshot replication' job which refreshes the reporting instance?  How far behind can your reporting server be?


Timeout expired error while using copy database wizard

A much more efficient path than messing with that wizard would be to:

Take a full backup of your 2000 database.
Restore it to a SQL 2005, 2008 or 2008 R2 instance, all of which support 2000 databases. You can still get the evaluation edition of 2008 R2 here, if you don't have any applicable instances in place. Note that Express won't work as it has a 4GB / 10GB data file limitation depending on version.
Change the compatibility level to something above 80 (90 or 100, depending on whether you used 2005 or 2008/2008 R2) using ALTER DATABASE.
Take a full backup of this database after it is no longer in 80 compatibility mode.
Restore this new backup to your SQL Server 2012 instance.
Change the compatibility level to 110 and update statistics.
TEST.


Speed-up lookup of a row with prefix pattern

This is how indexing essentially works. If you create an index on that column, the data will be sorted by the value, which effectively is on the prefix first and then the suffix.
The other thing you can do is either store only the suffix part (the XXXXX) of the value in it's own column too, either from the application that loads the data or using a Stored Generated Column and then create the index on that field. Doing that allows you to do an exact match search on a more unique value.


Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF

I noticed that when enabling this, many queries got a lot faster. I'm still using the default READ COMMITTED isolation level to connect to the database.

Dan noted this:

  The options are independent and both do not need to be turned on, although many mistakenly do so. Turning on either incurs the row-versioning overhead. READ_COMMITTED_SNAPSHOT ON causes all READ_COMMITTED sessions to use row-versioning instead of locking so that will avoid blocking and may explain your perceived performance improvement.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will only give you some properties of optimistic concurrency. RCSI and Snapshot (SI) differ in three main ways:

RCSI will be used immediately by all RC isolation queries.
RCSI provides a point-in-time (snapshot) view of the database for a single statement.
SI provides a point-in-time view for an entire transaction.
SI can have write conflicts.

  What is actually happening here?

RCSI is turned on, thus all RC isolation queries are being promoted to RCSI where readers don't block writers and writers don't block readers. The "speed up" you're seeing is most likely due to the reduced waits on blocking for those queries as it is now using the optimistic concurrency's version store instead of blocking and waiting. This may or may not be giving you the desired results in your queries.

  I would think that when ALLOW_SNAPSHOT_ISOLATION is OFF, setting READ_COMMITTED_SNAPSHOT to ON wouldn't have any effect... I'm still not actually using the snapshot isolation, or am I?

See above. You are using it when RCSI is turned on (which is the one you did in fact turn on) but SI is not automatically used when turned on: You need to explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT (or equivalent) to use SI.

For more information, see Row Versioning-based Isolation Levels in the SQL Server Database Engine in the product documentation.

Other useful details relating some gotchas and caveats when using RCSI/SI can be found in these articles:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)


how to get the information about sleeping processes that are causing blocking?

This is what I use to monitor sleeping SPIDs causing blocking issues:
SELECT
     s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1;

Hope this helps.
To answer your question specifically, sys.dm_tran_database_transactions holds the information you're looking for.


Trying to drop database only if it does not exist by using the db_id function fails

This is a parsing error, not a runtime error. You can avoid this by removing the GO commands (these can't exist inside an IF block anyway) and using dynamic SQL.

Why IF DB_ID() instead of just putting the DROP inside TRY/CATCH (or letting it error)?

You may want WITH ROLLBACK IMMEDIATE on the ALTER.

BEGIN TRY
  ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;
  DROP DATABASE foo;
END TRY
BEGIN CATCH
  PRINT 'Failure or database does not exist.';
END CATCH

Or

IF DB_ID(N'foo') IS NOT NULL
BEGIN
  EXEC sys.sp_executesql N'ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;
    DROP DATABASE foo;';
END

Of course this approach (OFFLINE first) will leave the data and log files intact, which means that if they were created in the default location, if you try to create the same database again, you'll first need to move or rename those files, or manually specify different names in the new file specification.


Use of MySQL trigger to insert row's week number

You tagged MySQL, so here is what should work for a MySQL trigger on your Before Insert trigger.

 DELIMITER $$
 CREATE DEFINER=`USERNAMEHERE`@`%` TRIGGER `SCHEMANAMEHERE`.`TABLENAMEHERE_BEFORE_INSERT` AFTER INSERT
 ON `TABLENAMEHERE`
 FOR EACH ROW
 BEGIN
    SET NEW.WeekNumber = WEEKOFYEAR(NEW.InsertedDateTime);
 END$$
 DELIMITER ;


TokuDB database size unknown in phpmyadmin

Once the TokuDB storage engine was defined, it becomes responsible for updating the INFORMATION_SCHEMA database.

The first thing you should do it test TokuDB's INFORMATION_SCHEMA functionality.

First, run this query at a mysql client prompt:

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

This will group and summarize MySQL data by engine usage per database.

If TokuDB stats do appear, then upgrade the MySQL drivers for phpMyAdmin.
If nothing comes up for TokuDB, try getting the latest version of the TokuDB storage engine. If you already have the latest version of TokuDB, contact Tokutek.
If you already have the latest version of the TokuDB storage and the latest MySQL drivers for phpMyAdmin and still get unknown, file a bug report with phpMyAdmin.


MYSQL showing access denied for use '"@ localhost' when trying to create a new database

1. Try to login Login with any valid mysql user credentials.

If the root user of MySQL doesn't have a password (which is a bad pratice) ensure to set below in config.inc.php file (for eg path: /etc/phpMyAdmin/config.inc.php)

$cfg['Servers'][$i]['AllowNoPassword'] = True;

2.You can also create a new mysql user using below.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

GRANT ALL privileges ON my_db.* TO 'new_user'@'localhost'; IDENTIFIED BY 'new_password';

flush privileges;


You could try to use top (1) with ties.

SELECT TOP (1) WITH TIES
  SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice,
  Orders.Id,
  Customer.Lastname AS CustomerLastName
FROM OrderLine, Product, Orders, Customer
WHERE OrderLine.ProductId = Product.Id
  AND Orders.Id = OrderLine.OrderId
  AND Customer.Id = Orders.CustomerId
GROUP BY Orders.Id, Customer.Lastname
ORDER BY OrderLinePrice DESC