Best way to store information about a list of materials and their quantity I want to store some information about a game. Specifically information about certain buildings that can be built in it.
Said buildings require materials to be built. I am trying to come up with a good DB model for storing that information in my database.
For example, one building needs 10 wood to be built. I thought about having a "Buildings" table with columns for ID, name, icon and material cost. However I'm usually used to referencing common values from another table. This is to say I would have a separate table named "Materials" with columns ID, name and icon and the "Buildings" table would have a column "MaterialsRequired" which would hold a reference to the ID of the needed material in the "Materials" table. But I have no idea how to do that (which is a best practice, afaik) AND store the needed amount of said material.

No you would have a intermediate table, so you can have a many to many realtionship.
And a material can be also a product of other materials
Building            Building_Material         Material
---------------------------------------------------------
Building_id         Building_id               Material_id
Name                Material_id               Name
                    Quantity                  Price


Are there any risks in running 'flush hosts'? Due to an error in my code I got my PC blocked from an active production DB (too many connection errors).
The error calls for me to use 'flush hosts' in order to reset the host cache.

Are there any risks involved in doing so? Will active connections be terminated? Does it do anything beyond just flushing the cache?

No harm. No connection termination. The cache is used only when clients are trying to connect; apparently something is out-of-date in the cache.

(Follup:  The OP tried it and found no side effects.)


Poor Performance from ABS in WHERE I have a sqlite database with around 12,000 rows, the relevant columns are width1, height1, width2 and height2

My dynamic input is an arbitrary width and height, and I want to find all rows where the difference between the width and height are smaller than a x

To make it more compleicated, I'm never sure if the width and height of the input are reversed, so I also have to flip the values and test input width against stored height, etc.

Here's the ugly query:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE ((ABS(width - width1) < 10 AND ABS(height - height1) < 10) OR
       (ABS(height - width1) < 10 AND ABS(width - height1) < 10)) OR
      ((ABS(width - width2) < 10 AND ABS(height - height2) < 10) OR
       (ABS(height - width2) < 10 AND ABS(width - height2) < 10));

I have created combined indexes for width1, width2, height1, height2 as well as each column individually, but the query is missing the indexes.

You indexed the value of width1 but then you added another number to it and did the absolute value of that.  The value has changed so much that the system no longer knows how to associate that to the index.

The answer here is to not do any math on width1, height1, width2, height2 but instead to do it on width and height only since those are passed in (not indexed values).  This query isn't any prettier but it should turn to your indexes at least.

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE
    (
        (width1  between (width-10) and (width+10)
            AND height1 between (height-10) and (height+10)) OR
        (width1  between (height-10) and (height+10)
            AND height1 between (width-10) and (width+10)) OR
        (width2  between (width-10) and (width+10)
            AND height2 between (height-10) and (height+10)) OR
        (width2  between (height-10) and (height+10)
            AND height2 between (width-10) and (width+10))
    )

Even better would be to pass in height_min, height_max, width_min, width_max (instead of just height and width) and then you don't have to do the math on every row:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE
    (
        (width1  between (width_min) and (width_max)
            AND height1 between (height_min) and (height_max)) OR
        (width1  between (height_min) and (height_max)
            AND height1 between (width_min) and (width_max)) OR
        (width2  between (width_min) and (width_max)
            AND height2 between (height_min) and (height_max)) OR
        (width2  between (height_min) and (height_max)
            AND height2 between (width_min) and (width_max))
    )


How space is compared in where condition I've come across below query where it is column being compared the space character.

SELECT  *
FROM    company
WHERE   emp_name > ' ' -- space char
AND     emp_id = 123
AND     active_flag = 'Y';

emp_name is VARCHAR2

Here WHERE emp_name > ' ', does it consider ASCII value of the space while comparing? Could someone please explain this.

I have Oracle on local. But you can see example on SQLFiddle

create table a1 ( i varchar(10));

insert into a1 values('a');
insert into a1 values(' ');
insert into a1 values('v ');

select * from a1 where i <> ' ';
select * from a1 where i = ' ';
select * from a1 where i like '% %';


Delete All Tables With Dates By Date Refer to this question on SO because it's similar.

I'm working on a way to delete a range of dates from all tables with date values in a database (purpose for building mini-devs).  I built a script that allows the user to pass in begin, end and database values, but am running into issues with dynamic sql.  For instance, when removing records between certain dates, I get an operand clash.  The below shows an over simplified example of this:

DECLARE @b DATE, @e DATE, @cmd NVARCHAR(MAX)
SET @b = '2012-07-01'
SET @e = '2014-01-25'

SET @cmd = 'DELETE FROM DateTable
WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

EXEC sp_executesql @cmd

The error is Operand type clash: date is incompatible with int.  I tried doing an inner CAST within the dynamic sql since the error indicates that the ValueDate becomes and INT:

-- Only the WHERE clause changed:
WHERE ValueDate BETWEEN CAST(' + CONVERT(VARCHAR,@b,121) + ' AS DATE) AND CAST(' + CONVERT(VARCHAR,@E,121) + ' AS DATE)'

The error this time is Explicit conversion from data type int to date is not allowed.  Finally I tried playing with some other formats (from MSDN).  In a nutshell, I want to do exactly what the SO user was trying to accomplish, but be able to do it across multiple tables (I even searched for a script as I doubt I'm the first person to want this).

You should try printing your commands when they yield errors. If you issued this instead of executing it:

PRINT @cmd;

You would see that this:

WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

Yields this:

WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25

Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:

WHERE ValueDate BETWEEN 2004 AND 1988

Which isn't even a valid BETWEEN operation, even if it could translate those to dates.

What you should be doing is passing these as proper parameters, e.g.

SET @cmd = 'DELETE FROM dbo.DateTable -- always use SCHEMA prefix
 WHERE ValueDate BETWEEN @b AND @e;';

EXEC sp_executesql @cmd, N'@b DATE, @e DATE', @b, @e;

If any of these columns are DATETIME, not DATE, you really should not be using BETWEEN at all. Instead:

WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);

Please read:

https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length
https://sqlblog.org/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix
https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common
https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons

As for the overall requirement of repeating this query against all date columns in the database, here is how I would do it (I've looked at the SO question you reference, and it just shows how to do this for one table):

DECLARE @b DATE = '2012-07-01', @e DATE = '2014-01-25',
        @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DELETE ' + QUOTENAME(s.name)
 + '.' + QUOTENAME(t.name) + '
  WHERE ' + QUOTENAME(c.name) + ' >= @b
  AND ' + QUOTENAME(c.name) + ' < DATEADD(DAY, 1, @e);'
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (40,42,43,58,61);
       -- or just = 40 if you're only interested in DATE columns

PRINT @sql;
-- EXEC sp_executesql @sql, N'@b DATE, @e DATE', @b, @e;


Eliminate duplicates in ListAgg (Oracle) Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row.  11.2 Added the LISTAGG function, so I am trying to use that instead.  My problem is that I need to eliminate duplicates in the results and don't seem to be able to do that.

Here is an example.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

What I want to see is this:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Here is a listagg version that is close, but doesn't eliminate duplicates.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s
FROM ListAggTest;

I have a solution, but it's worse than continuing to use the custom aggregate function.

You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1,
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
           '([^-]*)(-\1)+($|-)',
           '\1\3'),
         '-') Num2s
FROM ListAggTest;

This could be tidier if Oracle's regex flavour supported lookahead or non-capturing groups, but it doesn't.

However this solution does avoid scanning the source more than once.

DBFiddle here


How to manipulate join order of PostgreSQL optimizer? I am working on table join order optimization.

While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL?

How can I update table join order while query processing?

You can do that but it needs some trickery.

Imagine you got this:

select ... from a, b, c, d, e ...

...this type of query is always reordered.

But if you do:

select ... from a JOIN b ... JOIN c JOIN d JOIN c ...

...then PostgreSQL will only reorder join_collapse_limit tables. You can reduce this variable to a low value to force PostgreSQL into your order; however, I would try to avoid that.

This should only be locally using SET LOCAL, and you should first think, why you want the join order to be changed at all.

Related: Slow fulltext search due to wildly inaccurate row estimates


Replication Not Replicating I'm trying to set up transactional replication between two SQL Server 2008 R2 databases and, no matter what I try, it just doesn't seem to work, and I'm not sure where to look for further information about what may be going wrong.

I've created the publication and subscription by running through the wizards on both servers, and everything appears to be up and running - I get successful messages on the Publisher that it's created the publication and added the articles to the publication etc. I also subscribe the subscription database successfully to this publication. The replication monitor shows everything OK - but the synchronisation status of both the publication and the subscription always show as "No replicated transactions available" even when I manually add / modify data in the publication database.

I've tried deleting the replication data from both servers, dropping and recreating the databases from backups, reinitializing the subscription database etc all with no joy.

Any advice on where to look for some more info on what may be going on would be gratefully appreciated.

SQL Server Replication can in some situations hide errors from you. The behavior you are seeing could for example be caused by the snapshot agent not finishing, likely because of an incorrectly configured permission set.

Check out my Stairway to Replication here: http://www.sqlservercentral.com/stairway/72401/

Levels 1 - 5 give a solid foundation to the security requirements when setting up replication.
Level 10 describes how to troubleshoot a replication setup. This includes information about where to find error messages and how to interpret them.


Microsoft Distributed Transactions from Informix with Linux I need to create an extraction job from an Informix database, the requirement is that I need to enable transactions in SSIS. All of the MSDTC settings of the target machine and the SQL Server database are working, and the RPC service is listening on port 135, both firewall are disabled and no antivirus is installed.

The Informix server is on a Linux Red Hat 6.5, where the SELinux is permissive, the IpTables disabled. I also ran the cdotnet.sql and coledbp.sql scripts as directed by an IBM article to configure linked servers in SQL Server - and it worked.

The connection between the development machine and Informix is through an ODBC connection, which is also successful in querying the tables.

But when the job is started the following error occurs in SSIS:

Error: 0xC001402C in 1-Load CTLANCTO: SSIS Runtime Failed to Enroll OLE DB Connection in a Distributed Transaction with Error 0x8004D01B "The transaction manager is not available."
Error: 0xC0202009 in 1-Load CTLANCT: SSIS Error Code DTS_E_OLEDBERROR. OLE DB error. Error code: 0x8004D01B.
Error: 0xC020801C in Load CTLANCTO On Stage, OLE DB Source [16]: SIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The call of the AcquireConnection method for the "OLEDB informix" connection manager with error code 0xC0202009 failed. Error messages may have been posted earlier with more information about why the call failed for the AcquireConnection method.
Error: 0xC004701A in Load CTLANCTO On Stage, SSIS.Pipeline: OLE DB Source Fails in Pre-Run Phase with Error Code 0xC020801C.
Information: 0x4004300B in Load CTLANCTO On Stage, SSIS.Pipeline: "Stage" recorded 0 lines.
Information: 0x40043009 in Load CTLANCTO On Stage, SSIS.Pipeline: The cleaning phase is starting.
Task failed: Load CTLANCTO On Stage
Information: 0x4001100C in Load CTLANCTO On Stage: Overriding the current distributed transaction.
Information: 0x4001100C in 1-Load CTLANCT: Overriding the current distributed transaction.
SSIS Package "C: \ Projects \ ETL \ Daily LoadDRE \ Daily LoadDRE \ 1-Load CTLANCTO.dtsx" Finished: Failed.
The program '[796] DtsDebugHost.exe: DTS' was closed with the code 0 (0x0).

Although I have little knowledge on Linux, I tried to connect the development machine with the Informix server on port 135 via telnet, and the result is::

Connecting to x.x.x.x ... Could not open host connection in port
to 135: Connection failed

Then by running the rcpinfo command, it has the following:

program version netid     address                service    owner
    100000    4    tcp6      ::.0.111               portmapper superuser
    100000    3    tcp6      ::.0.111               portmapper superuser
    100000    4    udp6      ::.0.111               portmapper superuser
    100000    3    udp6      ::.0.111               portmapper superuser
    100000    4    tcp       0.0.0.0.0.111          portmapper superuser
    100000    3    tcp       0.0.0.0.0.111          portmapper superuser
    100000    2    tcp       0.0.0.0.0.111          portmapper superuser
    100000    4    udp       0.0.0.0.0.111          portmapper superuser
    100000    3    udp       0.0.0.0.0.111          portmapper superuser
    100000    2    udp       0.0.0.0.0.111          portmapper superuser
    100000    4    local     /var/run/rpcbind.sock  portmapper superuser
    100000    3    local     /var/run/rpcbind.sock  portmapper superuser
    100024    1    udp       0.0.0.0.173.232        status     29
    100024    1    tcp       0.0.0.0.132.42         status     29
    100024    1    udp6      ::.223.153             status     29
    100024    1    tcp6      ::.169.187             status     29

Does this mean that the RPC service in Informix is running on port 111? Is it necessary to change to 135?

The important detail is that when I disable transactions option in SSIS, extraction successfully occurs.

Test thousands of solutions over the internet and nothing worked, my last hope is here.

From the comments, it sounds like you're trying to:

Load from Informix into SQL Server
Use a distributed transaction to wrap the entire load
Roll back and retry the loads if they fail

In that case, the answer isn't a distributed transaction - it's a staging database and a 2-step extract/transform/load (ETL) process:

Select the data out of Informix into a temporary staging database (this could be in SQL Server - but don't bother with a transaction)
Move the data from your staging database into your final destination - and if you do want to use a transaction to accomplish this, that's a separate debate, but at least it'll work

Benefits of this approach include:

If the Informix read portion fails for some reason and you have to retry it, the production data warehouse users still aren't affected
If the production data warehouse (SQL Server) write portion fails for some reason and you have to retry it, the Informix users aren't affected either

I know that you're going to say, "But I need to write it this way." Thing is, the reason you're not finding any results for your question out there is that people don't write it that way. As your data grows, your server grows, and your career grows, you're going to need to extract data from multiple sources and you won't be able to take locks across all of them at once to accomplish the load. That's why the ETL (extract/transform/load) design is so popular.


A tail-log backup of the source database will be taken ? Why I am seeing this warning I took a backup of a database from 1st server (which runs SQL Server 2014).

When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message:

  Warning message - a tail-log backup of the source database will be
  taken. View setting on the Options page.

If I ignore this message and try to restore, it fails.

Any idea ? What should I do ?


SQL Native Client and network disconnects Does the SQL Native Client provide support for maintaining a persistent connection over the network at the driver level if there is a momentary network disconnect like a 999 error? Our applications are written in PowerBuilder (versions 12.5 and 6.5) and support SQL Server 2005 - 2014 and Express Edition - Enterprise Edition.

I read about the KeepAlive property at http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx , https://technet.microsoft.com/en-us/library/ms190771(v=sql.120).aspx , and http://www.databaseskill.com/2357386/ . Would this help with this situation?

I also looked at the Microsoft site https://msdn.microsoft.com/en-us/library/ms130822(v=sql.120).aspx which lists all the SQL Native Client connection string properties. I found Failover_Partner, but this is for database mirroring, and I found MultiSubnetFailover, but this is for Always On Availability Groups (AG).

I also looked at http://www.connectionstrings.com/sql-server/.

Thank you!
Mike Petrak

If you have connection pooling enabled in the connection string for the SQL Server Native Client, then connections are maintained to the SQL Server.

Check this MSDN blog for some technical details about how you can confirm if connections are being maintained.

Also, take a look at this MSDN page for Connection String properties, and how to ensure connection pooling is enabled.


How to create Buffer Pool in Database dedicated only for ONE BIG table? I have table TICKET with 400K records in database (DB2).

I wish to create one huge buffer pool which will be dedicated only to this one big table for faster response. What are the steps to do it?

Also at the moment I have one Buffer Pool which coovers whole Table space with all the tables (about 200) in database! what will happen then with that my specific table in that old firstly created buffer pool? should that table stay in first buffer pool or how to remove from that buffer pool?? Also are there some risks for this action???

Thank you

Create the new bufferpool.
Create a new tablespace that is associated with that bufferpool.
Create a copy of the table you want and place it in the new tablespace.
Copy the data from the original table to the new table.
Drop the old table.

While you can associate tablespaces with different bufferpools using the ALTER TABLESPACE statement, tables can only be associated with one tablespace. You can't issue ALTER TABLE and switch the tablespace. So you unfortunately have to create a new table and move the data from one to the other.

EDIT: Also note that any indexes you have associated with the old table will also get dropped and have to be re-created.

EDIT: Code Example, note for this I am assuming a 4K tablespace. Please substitute your correct size if need be (ie, 4, 8, 16, or 32 K).

db2 create bufferpool TICKET_BP immediate size 250 automatic pagesize 4k;
--note I list automatic storage here. We prefer to use that. Plus, IBM is pushing to move
-- in that direction. This TS is for the table.
db2 create large tablespace TICKETTS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
--this TS is for indexes.
db2 create large tablespace TICKET_IND_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
db2 create large tablespace TICKET_LOB_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;

-- now you can run the stored proc to move the data. It essentially does the same thing as what I specified above, except that it can move the data during normal work loads, so it should have less impact on locking and such, but it may take longer I'm guessing.
db2 "call syscproc.admin_move_table('MAX','TICKET','TICKETTS','TICKET_IND_TS','TICKET_LOB_TS','','','','','','MOVE');

And yes, most definitely take backups before and after. Also note that if you allow any transactions during the data movement period you may lose those transactions if you have to rollback.


In SSIS 2012, what is the purpose of flagging a package as an "Entry Point" package In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package"

Doing a search I found this page on MSDN which states:

  The value of 1 signifies that the package is meant to be started
  directly. The value of 0 signifies that the package is meant to be
  started by another package with the Execute Package task. The default
  value is 1.

With this flag enabled and disabled I have been able to execute a package directly.

What is the purpose of enabling or disabling this flag?  Is it merely to document the intentions of your own SSIS packages or does SQL Server/SSIS behave differently when it enabled or disabled?

There's one behaviour I can think of:

Only packages with "Entry Point Package" set to 1 can be executed by catalog.create_execution

(see http://msdn.microsoft.com/en-us/library/ff878034.aspx)


Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist) I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.

  with checked_adgroup AS (
     SELECT
        ua.new_adgroup,
        ua.account,
        ua.campaign,
        ua.ad_group,
        ua."position",
        cp.category,
        pt.full_value,

       FROM unnest_adgroup ua
         LEFT JOIN taxonomy_category cp ON ua."position" = cp."position"
         LEFT JOIN taxonomy pt ON ua.short_val = pt.short_value AND cp.category = pt.category AND (pt.lob IS NULL OR pt.lob = ua.lob)
    )

SELECT *
from crosstab(
'select
cad.account,
cad.campaign,
cad.ad_group,
cad.category,
cad.full_value
FROM checked_adgroup cad
WHERE  cad.all_correct AND cad.category IS NOT NULL
ORDER BY 1,2,3')
AS final_result(
 account text, campaign text, ad_group text,
 division text, lob text, match_type text  )
;

Error message:

ERROR:  relation "checked_adgroup" does not exist
LINE 7:     FROM checked_adgroup cad

Output of checked_adgroup cte looks like below

Desired output of the final statement is:

The CTE is not visible inside the crosstab() function. You have to pass the complete query string including the CTE.

Possible alternative: create a temporary table instead of the CTE.

However, there are more problems in your query. all_correct seems undefined. Etc.

Basic instructions:

PostgreSQL Crosstab Query


Database ID of a non-existent database - time-out waiting for buffer latch on page appearing in error log Background:
I've recently started at a new shop with lots of databases in various states. I arrived this morning (having just been added to the SQL DBA mailing group) to find an email about the CHECKDB job failing last night on one of the servers (one I've been warned has been problematic).

I won't dwell on the details of this here, it appears to be down to a number of time-outs waiting for buffer latches - by itself it's hard to tell why this is, as there should've been (and appears to have been) very little system usage last night when this was run.

There are ongoing problems with I/O requests longer than 15 seconds in the logs, and the vmware guys say that there have been communication problems with storage (on-going network issues), so this goes someway to explain the problem I think.

Important Note: I reran CHECKDB this morning and it completely quickly, finding 0 errors and as far as I'm aware nothing has happened since the failure last night, so (correct me if I'm wrong), I'm pretty confident there is no database problems, and last night was an anomaly, possibly triggered by network or storage problems (I'll know more about this when the network guys get back to me... if they do).

The actual point of this:
In the logs, right at the point where last night's CHECKDB job failed, there is a time-out waiting for a buffer latch - the strange thing to me is that the database id of the page it's supposedly trying to latch is 11, and there is no database with that id on the instance - ids only go up to 10.

This is followed up by the CHECKDB line with 1 errors found, 0 repaired, pointing to an Internal Database snapshot having a split point (see below for error log)

Specific Questions:

Why would there be a reference to a database ID that doesn't exist (do Internal database snapshots get assigned a database ID at some point and that's what it was referring to)?
Is there anyway of finding out what the database snapshot in question is (is it something to do with the way CHECKDB works, or is it something else)?
Is there a way of finding what happened to the snapshot in question (as it obviously wasn't a problem when I ran CHECKDB again this morning).

Error log:

01/04/2017 23:50    spid101
Unknown DBCC CHECKDB (zenworks_UAL) WITH no_infomsgs executed by ARTSLOCAL\svc_zcmsql found 1 errors and repaired 0 errors. Elapsed time: 0 hours 28 minutes 51 seconds.  Internal database snapshot has split point LSN = 00adf51f:00019bf4:0001 and first LSN = 00adf51f:00019bf3:0001.

01/04/2017 23:50    spid101
Unknown Unable to read and latch page (1:2067184) with latch type SH. Latch failed.

01/04/2017 23:50    spid101
Unknown Error: 8966 Severity: 16 State: 2.

01/04/2017 23:50    spid101
Unknown A time-out occurred while waiting for buffer latch -- type 2 bp 00000004FFFF4A80 page 1:2067184 stat 0xc2040d database id: 11 allocation unit Id: 0/281474976710656 task 0x000000015BA6A088 : 0 waittime 300 flags 0x100000001a owning task 0x000000015BA6A088. Not continuing to wait.

Note:
As far as a know, no one else was working the database or running anything significant between the failure last night and me running CHECKDB this morning. The system was in use, but not significantly. Nothing is showing in the logs.

To confirm, there is no DB of ID 11, and I don't think there ever was (and as certain as I can be not between last night and this morning

Query:

SELECT db_name(11)

Results:

NULL

Query:

SELECT * FROM sys.databases

Results:

╔════════════════════╦═════════════╗
║        name        ║ database_id ║
╠════════════════════╬═════════════╣
║ master             ║           1 ║
║ tempdb             ║           2 ║
║ model              ║           3 ║
║ msdb               ║           4 ║
║ ReportServer       ║           5 ║
║ ReportServerTempDB ║           6 ║
║ z_xxxxxx_L         ║           7 ║
║ z_xxxxxx_p         ║           8 ║
║ S_xxxxxx_t         ║           9 ║
║ z_xxxxxx_t         ║          10 ║
╚════════════════════╩═════════════╝

This issue is a characteristic symptom of heavy I/O on the host.  Perhaps there are multiple backups, DBCCs, and other "maintenance" activities happening on the SAN at that time.

Work with your VMware guy to ensure the SQL Server VMs are using the PVSCSI adapter for the LUNs present in the VM.  VMware's SQL Server Best Practices documentation says this about SCSI adapters:

  Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI Controller for data and log VMDKs.

  The PVSCSI Controller is the optimal SCSI controller for an I/O-intensive application on vSphere. This controller has a queue depth of 64 (Per Device) and 254 (Per Controller) by default (double the size of an LSI Logic SAS controller). The PVSCSI controller’s per-device and per-controller queue
  depths can also be increased to 254 and 1024 respectively, providing even more increased I/O bandwidth for the virtualized workload. See Configuring disks to use VMware Paravirtual SCSI (PVSCSI) adapters (1010298).

  Also, see how to increase queue depths in a VMware vSphere environment.

  Note:
  While increasing the default queue depth of a virtual SCSI controller can be beneficial to an SQL Server-based VM, the configuration can also introduce unintended adverse effects in overall performance if not done properly.

  VMware highly recommends that customers consult and work with the appropriate storage vendor’s support personnel to evaluate the impact of such changes and obtain recommendations or other adjustments that may be required to support the increase in queue depth of a virtual SCSI controller.

Use multiple vSCSI adapters. Placing OS, data, and transaction logs onto a separate vSCSI adapter optimizes I/O by distributing load across multiple target devices and allowing for more queues on the operating system level.
Spread the I/O load across all PVSCSI adapters to help optimize the I/O from the guest. In cases where there is a requirement for many data and
transaction log disks, it will be beneficial to set the OS boot disk
to use PVSCSI as well.

I would check the SQL Server default trace to see if DatabaseID 11 shows up.

This reads the default trace into a temporary table:

DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt;

This will show any trace events where DatabaseID = 11.

SELECT tt.DatabaseID
    , tt.DatabaseName
    , tt.StartTime
    , tt.HostName
    , tt.LoginName
    , tt.ApplicationName
    , te.name
FROM #trctemp tt
    LEFT JOIN sys.trace_events te ON tt.EventClass = te.trace_event_id
WHERE tt.DatabaseID = 11 --missing database ID
ORDER BY tt.StartTime DESC;

DBCC CHECKDB (db_name) will create an internal snapshot of the database.  You can see the snapshot files as an NTFS alternate data stream attached to the actual database files.  So, if you run dir C:\some\path /r where C:\some\path is the location of your database files, you'll see the internal database snapshot files listed like this:

2018-03-29  03:06 PM       402,653,184 Test_DB.mdf
                           402,653,184 Test_DB.mdf:MSSQL_DBCC25:$DATA

In the sample above, you can see I have a database with a file named Test_DB.mdf.  There is also an alternate data stream named Test_DB.mdf:MSSQL_DBCC25:$DATA.  By correlating the existing database IDs in sys.databases, I am able to deduce that 25 in the DBCC25 part of the name is the database id for the snapshot.


Getting Batch Requests Per Second On a Particular Database Does anyone know how to find Batch Requests/Sec on particular database (database specific, not server level)?

This is how to find Transactions/Sec on a particular database.

DECLARE @Counter bigint
SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND object_name='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name

-- Wait for 1 second

WAITFOR DELAY '00:00:01'

SELECT cntr_value = @Counter FROM sys.dm_os_performance_counters
WHERE counter_name ='Transactions/sec'
AND object_name ='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name

'Batch Requests/Sec' is a server-level counter, not available for individual
database instances. Keep in mind that single batch can access multiple databases.

You can get an aggregated count of batches and RPCs executed per database with an XE trace with a histogram target like the below example. This will count only the context database where the batch was executed, not all databases touched by the batch.

CREATE EVENT SESSION [sql_batch_completed_by_database] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.sql_batch_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

CREATE EVENT SESSION [rpc_completed_by_database] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.rpc_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

--get snapshot of aggregated counts
WITH
      sql_batch_completed_by_database AS (
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'sql_batch_completed_by_database'
    )
    , rpc_completed_by_database AS (
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'rpc_completed_by_database'
    )
    , requests_by_database AS (
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM sql_batch_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
        UNION ALL
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM rpc_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
    )
SELECT DatabaseName, SUM(RequestCount) AS RequestCount
FROM requests_by_database
GROUP BY DatabaseName;
GO


Foreign key constraints with MySQL/InnoDB I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL.

I assume that they effect inserted/updates to a degree however I am not to worried about that in my testing.  I was wondering if they effect SELECT statements and if they effect data/index size?

"integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Integers are very good when using as an index column." - HottestGuide


Receiving several deadlocks with simple queries when ran concurrently from different nodes We have a web application with three nodes that sit behind a load balancer that handles traffic in a round robin fashion (no sticky sessions). When we receive several requests coming in at the same time we will start to see several deadlocks occurring on one of our queries that obtains a session id. We have a table called uniqueids that we populate with around 250,000 rows that contains sequential ids specific to our application. We use the stored procedure getnextsessionid to select the next unused id and then update the used column in that row to show it has been used. Is there a different locking method we can use to ensure no two people receive the same id and prevent deadlocks?

ALTER    PROCEDURE [dbo].[getnextsessionID]

    (
    @id varchar(15) OUT
    )

AS

    SET NOCOUNT ON
    SET LOCK_TIMEOUT 8000

    BEGIN TRAN
        SELECT TOP 1 @id = RTRIM(LTRIM(sessionID))
        FROM uniqueids WITH (HOLDLOCK,ROWLOCK,INDEX = session_used)
        WHERE used = 0

        UPDATE uniqueids
        SET used = 1,dt = GetDate()
        WHERE sessionID = @id

    COMMIT TRAN

--uniqueids table

    CREATE TABLE [dbo].[uniqueids](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [sessionID] [varchar](15) NOT NULL,
        [used] [bit] NOT NULL,
        [dt] [smalldatetime] NULL,
     CONSTRAINT [PK_uniqueids_id] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY],
     CONSTRAINT [U_sessionID] UNIQUE NONCLUSTERED
    (
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [session_used] ON [dbo].[uniqueids]
    (
        [used] ASC,
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[uniqueids] ADD  CONSTRAINT [DF_uniqueids_used1]  DEFAULT ((0)) FOR [used]

I would use an UPDATE with an OUTPUT instead of two completely separate statements.

ALTER PROCEDURE dbo.getnextsessionID
  @id varchar(15) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @t TABLE(id varchar(15));

  ;WITH NextID AS
  (
    SELECT TOP (1) sessionID,used
      FROM dbo.uniqueids
      WHERE used = 0
      ORDER BY ID
  )
  UPDATE NextID SET used = 1
    OUTPUT deleted.sessionID INTO @t;

  SELECT @id = RTRIM(LTRIM(id)) FROM @t;
END
GO

Also if the table is large (and as the set where used = 1 grows), I might suggest a filtered index instead, simply because it's much cheaper to maintain:

CREATE INDEX session_used
  ON dbo.uniqueids(ID, sessionID, used)
  WHERE used = 0
  WITH (DROP_EXISTING = ON);


Count rows in a table based on a DATE field I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:

order_id | client_id  | date_added
-----------------------------------
14152    | NA4156     | 2019-03-01
14153    | EA4656     | 2019-03-02
14154    | EA4656     | 2019-03-02
14155    | CA4456     | 2019-03-03
14156    | DA4556     | 2019-03-03
14157    | EA4656     | 2019-03-03
14158    | FA4756     | 2019-03-06
14159    | GA4856     | 2019-03-06

and so on.
As you can see on a certain day there might be no entries.

I am trying to obtain the following result:

date       | no_of_rows
-----------------------------
2019-03-01 | 1
2019-03-02 | 2
2019-03-03 | 4
2019-03-04 | 4
2019-03-05 | 4
2019-03-06 | 6

I understood from here how to generate all dates that I am looking for, but I am not sure now how to count the unique clients based on client_id for each date.

At the moment I am doing this step by step and moving the data into an Excel and processing it from there using the query below:

get unique number of clients registered until and including 2019-03-01

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-02' GROUP BY client_id

get unique number of clients registered until and including 2019-03-02

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-03' GROUP BY client_id

and so on.

But this method seems to be a little bit exhaustive and I am pretty sure there is a way to do it in a single query, but not sure where to start from.

You have to utilise the COUNT function along with the GROUP BY function along the lines of

SELECT
 date_added AS Date,
 COUNT(Client_ID ) AS no_of_rows
FROM CLIENTS
GROUP BY date_added
ORDER BY date_added;

Using the above produces output in the form of

Date        no_of_rows
2019-03-01  1
2019-03-02  2
2019-03-03  3
2019-03-06  2

As an aside it is generally a good idea not to name a column with the name of a datatype i.e I would probably call the Data column in the result ClientDate.


Detached database file permissions I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by this I meant detaching databases in general) removes all other ownership and makes ownership exclusive to the local Administrator account. Here's where it gets weird:

I can't seem to change permissions back except via two methods:

Windows GUI properties/Security etc.
Copying the files to a sub directory that has inherited different permissions.

What I can't get to work:

PowerShell Set-Acl
PowerShell Move-Item
Moving/cutting the files in Windows GUI
icacls

I understand why ownership changes, but not why it's so difficult to edit the permissions afterwards, and why the files seem to essentially be hidden from command line tools.

Reason for the change is that sometimes data needs to be put back online before it goes to tape for whatever reason, and I can't attach the database(s) without changing permissions first.

Thanks

You should enable trace flag 1802 on your database servers.  This will allow SQL Server to retain inheritable permissions when you detach a database so you won't experience this issue going forward:

http://support2.microsoft.com/kb/922804

DBCC TRACEON(1802, -1)

Add it to the startup parameters of the SQL Server service
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.110).aspx


How to replace multiple parts of a string with data from multiple rows? Here's a fiddle to show what I'm after.

Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in curly braces, mixed in with any other valid text characters.

Example: '{1} / {9} ... {12}'

With a single SELECT statement, I want to return a string whereby all the integers (and their wrapping braces) have been replaced with a value derived from my table; specifically, the text value for the row having an ID that matches the number found in the source string.... and any characters outside the curly braces remain untouched.

Here is an example that fails to complete the task:

select
  replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
from testing;

This would return 1 row per row in the testing table. For the row with id value = 13, the '{13}' portion of the string is successfully replaced, but the '{15}' portion is not (and vice versa on row 15).

I imagine creating a function that loops through all testing rows and repeatedly attempts replacements would solve the problem. Be that as it may, a straight-up SQL statement would be preferable to looping.

Example Data

+----+-------------------+
| id |  display          |
+----+-------------------+
|  1 |  Apple            |
|  2 |  Banana           |
|  3 |  Celery           |
|  4 |  Dragonfruit      |
|  5 |  Eggplant         |
|  6 |  Fenugreek        |
|  7 |  Gourd            |
|  8 |  Honeydew         |
|  9 |  Iceberg Lettuce  |
| 10 |  Jackfruit        |
| 11 |  Kale             |
| 12 |  Lemon            |
| 13 |  Mandarin         |
| 14 |  Nectarine        |
| 15 |  Olive            |
+----+-------------------+

Example use cases

select replace('{1} {3}',null,null)
-- Returns 'Apple Celery'

select replace('{3},{4},{5}',null,null);
-- Returns 'Celery,Dragonfruit,Eggplant'

select replace('{1} / {9} ... {12}',null,null);
-- Returns 'Apple / Iceberg Lettuce ... Lemon'

Clearly, the replace keyword does not do the job.

PS. If a solution required the format of the string to change in order to facilitate this, that is an option.

For example: '#1 / #9 ... #12' (to correlate with the earlier example)

In this format, perhaps we could break the string up into a rowset, based on #, take the left characters until we find a non-numeric, join to the testing table based on the numbers taken, replace the # and numbers with the testing table's display value then stuff all those individually modified tokens back into a single string for xml path?

I am using SQL Server 2016 which does not support string_agg. That said, if there is a solution using string_agg, I am still interested in reviewing it.

Here is an example of using a recursive cte to translate the variables

drop table if exists testing;
go
create table testing (id int, display varchar(16));
insert into testing values (1, 'Apple');
insert into testing values (2, 'Banana');
insert into testing values (3, 'Celery');
insert into testing values (4, 'Dragonfruit');
insert into testing values (5, 'Eggplant');

DROP FUNCTION IF EXISTS dbo.TranslateVariables
go
CREATE FUNCTION dbo.TranslateVariables
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT FindValue = '{' + convert(varchar(5),id) + '}' ,ReplaceValue = display,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM testing
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.TranslateVariables('{1} {3}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

 StrValue     | cnt |
|--------------|-----|
| Apple Celery | 0   |

SELECT *
FROM dbo.TranslateVariables('{3},{4},{5}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| StrValue                    | cnt |
|-----------------------------|-----|
| Celery,Dragonfruit,Eggplant | 0   |


Database master key and certificate for backup encryption - SQL Server I'm trying to understand backup encryption and I have created a master key (in the master DB) and used a password as follows:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test123!';
GO

Then I create a certificate as follows:
CREATE CERTIFICATE CertName
WITH SUBJECT = 'DB Backup Certificate';
GO

I backup this certificate as follows:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk',
ENCRYPTION BY PASSWORD = 'bkPofcert!')

Then I take a DB backup using this certificate, delete the DB, certificate and the master key and try to restore the DB which is not possible (I do understand this).
So I create the master key, restore the certificate from the certificate backup that was taken above and then it lets me restore the DB successfully.
My issue is that even if you provide a different password than what you originally used to create the master key (e.g. 'newpassword123!' instead of 'test123!', it will still let you restore the certificate and the DB. Isn't the certificate encrypted by the master key? If so, how does it let you proceed even with a different password for the master key? Also if this is the case, why do we need to backup the master key? When we can just create a new one with a different password and all works well.
I also tried to restore this DB on another instance (after creating a new master key with a different password and after restoring the certificate as per above) and it still allowed me to restore this DB. So this also eliminates the possibility that it lets me restore the encrypted DB (even with a different master key) due to the master key being encrypted (and hence "recognised") by the service master key - since on the other instance the service master key should be different.

Isn't the certificate encrypted by the master key?
Not exactly. To be more precise, the certificate private key is the one encrypted by the master key and you can see that under the Remarks section of the CREATE MASTER KEY doc:

The database master key is a symmetric key used to protect the private
keys of certificates and asymmetric keys that are present in the
database.

You can check sys.certificates to see that information as well:
SELECT name, pvt_key_encryption_type_desc, pvt_key_last_backup_date
FROM sys.certificates;

how does it let you proceed even with a different password for the master key?
When you backed up the certificate using the option WITH PRIVATE KEY you added to the backup the actual key used to encrypt things referenced by that certificate. When the certificate was within SQL Server, its master key secured the certificate private key, but when you back it up the only protection seems to be that password for the backup ENCRYPTION BY PASSWORD = 'bkPofcert!'. If you try to backup the certificate and private key without a password like this:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\certbk.cert'
WITH PRIVATE KEY (
FILE = 'C:\SQL2019\certkey.pvk')

SQL Server throws the following error:

An encryption password must be provided to encrypt the private key of
this certificate.

That means while the private key is being moved (by backing it up to restore it later) the only protection you get is that password from the backup, not the one from the master key. The password from the CREATE MASTER KEY command is in fact used when you need to use the command OPEN MASTER KEY.
As you have noticed by now, You shouldn't keep the certificate backup and the database backup at the same place for security reasons.
why do we need to backup the master key?
Because it encrypts objects (like the private keys used by the certificates) while they're still on the instance and if it was to be lost, things on the instance would not be decrypted.
The following backup of the certificate without its private key works, but it would not be enough to restore the database backup later as you did:
BACKUP CERTIFICATE CertName TO FILE = 'C:\SQL2019\CertName.cer';

The reason it does not restore the database properly is because the certificate misses the private key it used before.

Relevant material:
SQL Server Encryption - Youtube video by MVP Ben Miller
Encryption Hierarchy

Disclaimer: I provided this answer based on research and I'd be really glad if someone with more experience could confirm that my answer is appropriate because I understand encryption is a delicate subject and could cause someone to be locked out of its data if used without accurate knowledge.


(mysql) Database replication between 2 DB while 3rd DB as backup I am not an advanced DB admin or designer. I am sure there are articles on this kind of scenario, probably too many. So I got confused and not sure which solution is appropriate/best for me.

I am setting up two DB on different region. Users will access/write to the nearest one. So these 2 DB should be 100% identical. Any insert, update, delete on a DB replicate same on the other instantly.
(better if table structure on a DB can also alter other DB table)

See the attached diagram. Clients can use any of these DBs.

There is the 3rd DB. Clients won't ever access it or write to it.
It's for backup purposes only.
Any changes but "delete" from other DB will be replicate to this one.

Any suggestion?

Dual-Master, where you write to both masters, is full of problems.  You need to carefully set the two auto_increment_* "variables".  You need to make sure users cannot insert the same PRIMARY or UNIQUE values into different masters.  If 'East' goes down, the Slave is hard to reconnect.  If a hurricane hits the East, you lose both the master and slave there.  Etc.

A slight improvement is "Dual-Master, Single writer".  That is, you have the configuration you described, but clients never write to more than one of the Masters.  The configuration is more stable, but the failover process is perhaps more complex.

Instead, consider Galera (in MariaDB or PXC), which solves many of the problems.


Oracle file permissions expdp vs dbms_datapump We have written our own export function using calls to dbms_datapump. In some systems the export fails due to insufficient file permissions on the chosen directory.
However, using expdp to perform the export to the same directory works.

I'm looking for an explanation for this different behaviour. Does the expdp session run under a different OS user than the database server? And what user would that be?

Edit: We have seen the same kind of difference between file writes using utl_file running in a user session and running in a scheduled job.

I assume that the database runs on a Linux OS.
I assume that you connect with a tool like sqldeveloper to the database using a tns-connection string (maybe from a machine different)
I assume that you start expdp from the database server using not a tns-connection string but set ORACLE_SID and create a local connection.

Then, yes, it is possible that such strange things happen.

In one of your comments you wrote "both run as a job in the database". That is not the correct terminology. Both are sessions in the database And both sessions have a Linux process that corresponds to the session: the so called shadow processes. There are more complex configuration but I assume this one.

So what is the difference between your two sessiosn? The way you connect to the database and the shadow processes are created.

In the case of the sqldeveloper that uses a tns-connectstring the tool contacts the listener and the listener-process runs the oracle binary to create a process (the shadow process). If you  run the expdp tool directly on the Server using a local connection then the expdp tool runs the Oracle binary to create a process.

There are other situations were you can have such a result, e.g. with a RAC-Database. The database Directory Points on both nodes to a loacal Directory with different permissions.

But without more detailed Information it makes not sense to investigate this further.

Use a tns-connection in both Situation so that your shadow process is created in the same way.


Is searching faster if indexed values are the same? Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123).
Because my users search a lot by date, I added an index on this attribute. There are probably hundred of thousands items in my collection and most of these events have a different createdAt date (which means a different index value).

My users are complaining that it takes too much time to retrieve some data, so I'm looking for a way to improve that.

As my users only search by date (never with time), I was wondering if creating another attribute 'createdAtDateOnly' (keep only the date and set the time to 00:00, for example: 2019/09/09 00:00:00) would be faster to search because many 'events' in my collection would have the same index.

Indexes perform better when they have unique values like customer ID, acc number etc. The more unique your indexed field is the less number of records gets picked improving the performance. You can build index on the date field, but would that be unique enough to filter the record you need?


CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database.

Here is some information about the index:
Rows: 112 Millions
Disk space: 30 GB

I'm using SQL Server 2008 R2 Enterprise Edition.

I'm running CREATE INDEX ... WITH DROP EXISTING = ON, ONLINE = ON

In my QA environment I'm currently running the CREATE INDEX statement. It has been running 1 1/2 hours from now and I run on purpose an INSERT statement in another SSMS window, this statement is blocked since the CREATE INDEX operation is running.

In production this database is being accessed 24x7x365, so I can not accept so much time of blocking, but there's a time when the database has low access (3AM - 6AM).

I was expecting that the WITH ONLINE = ON behaves different. There's a way to prevent so much blocking? Or should I ask for a long maintenance window for the CREATE INDEX operation?

Thanks in advance for any response.

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The culprit is the user query blocking the final phase. Look at sys.dm_exec_request.blocking_session_id but make sure to walk up the chain until you find the one spid that is blocking w/o being blocked in turn.


COPY TO statement in postgres outputting double backslashes I would like to output a string with backslashes that is enclosed in dollar quotes to a file in postgres.  For example, with the following query:

COPY (select $$a\x5cb\x5cc$$ ) to $$c:\test.txt$$;

I would expect the contents of the file to be

a\b\c

consistent with the documentation from Postgres suggesting that these string literals should be, well, literals:

  Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.

However, to my surprise, the contents of this file are instead

a\\b\\c

Why is this the case?  How can I output a literal string to a file without using single

It doesn't matter how you got the string into COPY TO.  The output of COPY TO is defined on its own terms.

Read the "Text Format" section of the docs for the command you are using.

The output of COPY TO is not inside the dollar quotes strings, so the rules of those strings does not matter.

You can use with (format csv); option to COPY.  It will consider a different set of characters to be special, and will escape those special characters in a different way.  It will not double the backslashes, but might do something else you don't like instead.


SSMS works, Linked Server does not Linked Server SS 2012(SP4) to SS 2016(SP1) just stopped working. It times out with the error "Server is not found or not accessible". However I can connect and query the target server successfully with SSMS. How can SSMS work yet Linked Server cannot even "see" the target server?

There is an connection issue between Server A and B, NOT local machine and Server B.


Remove invisible null characters a string's ending For an unknown reason, many strings in one of my VARCHAR(1000) columns have been terminated with invisible characters.

declare @BrokenString varbinary(max)=0x6D0079002000620075006700670065006400200073007400720069006E00670000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003F003F00;
select cast(@BrokenString as nvarchar(max)) -- returns 'my bugged string'
select cast(@BrokenString as nvarchar(max)) + ' is bugged' -- still returns 'my bugged string' !

declare @BrokenStringTable table (Brokey nvarchar(max));
insert into @BrokenStringTable
select cast(@BrokenString as nvarchar(max));
select * from @BrokenStringTable for json auto;

The output from the select * from @BrokenStringTable for json auto; statement looks like this :

[{"Brokey":"my bugged string\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000??"}]

How can I detect which records in my table contains these characters ? It seems using charindex, where+like and any other normal solutions just don't work with these.

One of the reasons I vastly prefer convert() over cast() is that convert() is much more extensible. For example, you can use a style number to convert a binary value to a string as is. So if 3F00 is always the problematic character:

SELECT CASE
  WHEN CONVERT(nvarchar(max), @BrokenString, 1) LIKE N'%3F00'
  THEN 'borked' END;

Result:

borked

So you can find all the offending rows (this will not set any speed records) using:

SELECT ... FROM dbo.table
WHERE CONVERT(nvarchar(max), column) LIKE N'%3F00';


having 'Measure' > 0 syntax does not work in mdx query Hope you're doing well
I've written an MDX query as you can see below :

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}
   having [Measures].[IndentCount] > 0 on 0 ,
   non empty [DimTime].[Year].[Year] on 1
from [Procurement]

The problem is despite using having [Measures].[IndentCount] > 0 I still see the null value for my measure as you can see below :

 .
I want to know what the problem would be???
I have this problem even when I use Filter function

Thanks in advance

You put the HAVING clause in the ON COLUMNS axis (or ON 0) which attempts to filter out any column which has a null or zero IndentCount. Instead you need to move it to the ON ROWS axis (or ON 1) if you want it eliminate some rows.

I would also suggest you may optimize the performance by using the NonEmpty function instead of HAVING. Since you can’t have a zero or negative IndentCount the following will do what you want and will perform better since it will only calculate the TOTAL measure for rows you will end up keeping.

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}
   on 0 ,
   NonEmpty([DimTime].[Year].[Year], [Measures].[IndentCount]) on 1
from [Procurement]


Postgres 10 Log Shipped Replica with cascading replication I'm planning for a tricky (at least for me) migration whereby I have master postgres 10 db from a cloud provider that will not allow for replication user connection. I do have a base backup and WAL getting shipped to AWS S3. I've easily set up a log shipped replica as a hot standby to promote and officially migrate off initial cloud platform.
However, I'd also like to have another warm stand by ready and available immediately for HA after the initial log shipped replica is promoted in the event of a failure I have something immediately available.  There are several TB of data so it takes a few hours to generate a replica representing significant risk after database promotion.

PostgreSQL supports cascading replication, so you can configure your warm standby as a normal streaming replication standby server to your WAL shipping standby.
If you have recovery_target_timeline = 'latest' on that standby server (default in later PostgreSQL versions), that second standby server will keep replicating when you promote your WAL shipping standby.


Wingdings font in SSMS's column I do want to create a column which should has check marks and cross marks.

Here is the part of my query:

iif(order_details.order_status = 'Y',char(252),char(251)) as [completed?]

After I execute the query in SSMS, I'm getting outputs like this - 'ü', 'û'

But I'm assuming, if change from current font to Wingdings in SSMS I'll getting check and cross marks and every outputs will be changed in SSMS. But I only want to change the column  completed? .

Here is my result:

Can any one help me to get check marks and a cross marks?

This is not possible: the display settings can not be specified per column.

If you set the font to one which definitely supports relevant Unicode code points, you can use these characters instead of relying on a legacy symbols font, while still having normal characters available.

For example "Arial Unicode MS" supports CHECK MARK (U+2713) and HEAVY BALLOT X (U+2718).


Your MySQL Instance is using binlog_format as ROW and not STATEMENT.

You can check with one of the following:

SELECT @global.binlog_format;
#
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
#
SELECT variable_value FROM information_schema.global variables
WHERE variable_name = 'binlgo_format';

This should be OK for you because it keeps the table layout a secret, known only to your developers, DBAs, and Project Managers. You will now be able to see the SQL with your current binary logs. I just mentioned this about 3 weeks ago : Show sql statements from mysql binlog (GTID)

You could switch binlog_format to MIXED and see if it exposes the actual SQL.


Postgresql numeric and decimal is automatically rounding off CREATE TABLE IF NOT EXISTS ttable (
    tcol decimal(9,7)
);

insert into ttable(tcol) values(17.4604786);

the value is getting stored as 17.46

Happens the same if I use decimal/numeric type

I am using

PostgreSQL 11.2 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit.

Tool
SQL Workbench/J Build 124 (2018-08-20 22:43)
Java version: 1.8.0_211 (64 bit).

Connection info:
Product Name: PostgreSQL
Product Version: 11.2
Product Info: 11.2
Driver Name: PostgreSQL JDBC Driver
Driver Class: org.postgresql.Driver
Driver Version: 42.2.6
Isolation Level: READ COMMITTED
Workbench DBID: postgresql

It seems to be the default behaviour of SQL Workbench, have a look at this article:

How do I change the resolution or scale of decimal data type on SQL Workbench.

Quoted from the article:

  Normally, SQL Workbench doesn't display the decimal data with the full
  scale. By default the scale is 2. We can change the scale by the
  setting.

  Solution

  Open Data formatting settings. SQLWorkBench -> Preferences ->Data formatting
  Change Decimal digits The default is 2. In this case, it should be at least 11.

And according to SQL Workbench manual about Data formating:

  Decimal digits

  Define the maximum number of digits which will be displayed for
  numeric columns. This only affects the display of the number, not the
  storage or retrieval. Internally they are still stored as the DBMS
  returned them. To see the internal value, leave the mouse cursor over
  the cell. The tool tip which is displayed will contain the number as
  it was returned by the JDBC driver. When exporting data or copying it
  to the clipboard, the real value will be used.

  If this value is set to 0 (zero) values will be display with as many digits as available.

(Bold is mine)


How can I simplify this UNION? Here's the case - I have the following query:

SELECT DISTINCT post_id FROM
(
    (
        SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director' AND
            pm.meta_key = 'htk_premiere_date'
        )
    UNION
    (
        SELECT DISTINCT rp.post_id, '' AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director'
    )
    ORDER BY (premiere_date IS NULL), premiere_date DESC
) AS element_ids

The above query works as intended but, as those two inner SELECTs are fairly similar, I was wondering if there is a way to merge them into a single query?

Here's the background:
This is related to a WordPress database, expanded with custom tables. I need to get IDs of elements, sorted by htk_premiere_date meta key value. This key & its value is stored in wp_postmeta - table that has the following columns: meta_id (not important here), post_id, meta_key & meta_value. The point is, I need to sort all IDs in the following pattern:

First, IDs that have this htk_premiere_date key set and it's assigned a date value (so sorted by date).
Second, IDs that does not have the htk_premiere_date meta value set, but the key has its row in the wp_postmeta table.
Third - and this is basically the source of the whole problem - if a post ID does not have a row in wp_postmeta table for htk_premiere_date key, it should be treated as in point 2. - so as if it has the key but no value assigned to it.

Thanks a lot for any help!

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates:

test=# SELECT 1 UNION SELECT 1;
 ?column?
----------
        1
(1 row)

There is a sharp distinction between UNION and UNION ALL:

test=# SELECT 1 UNION ALL SELECT 1;
 ?column?
----------
        1
        1
(2 rows)

in your case the second subselect can never have the same rows as the first one (due to the empty column) so UNION ALL is already definitely fine here (saving a pointless duplication filter).


Include all possible values of sub-categories under each category How can it be achieved in MS SQL 2016, when I need to always see every state under all existing categories even if there are no records, which satisfy the state formula?

Under a and b below I always want to see all three of later, earlier, and same with null counts whenever there is no corresponding record.

I guess it should be achievable with some sort of cross join?..

declare @t table (tid int not null identity(1,1) primary key clustered
    ,tcat char(1)
    ,tdat1 date
    ,tdat2 date
)

insert into @t select * from (values
     ('a', '2019-01-01', '2019-02-01')
    ,('a', '2019-02-01', '2019-01-01')
    ,('b', '2019-02-01', '2019-01-01')
    ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

select
    tcat as category
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end         as state
    ,count(1)   as howmany
from @t
group by
    tcat
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end

If I understand your question correctly, here is one way to achieve your goal

--demo setup
declare @t table (tid int not null identity(1,1) primary key clustered
    ,tcat char(1)
    ,tdat1 date
    ,tdat2 date
)

insert into @t select * from (values
     ('a', '2019-01-01', '2019-02-01')
    ,('a', '2019-02-01', '2019-01-01')
    ,('b', '2019-02-01', '2019-01-01')
    ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

--the solution
;with BaseData as
(
select
    tcat as category
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end         as state
    ,count(1)   as howmany
from @t
group by
    tcat
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end
)
select * from BaseData
union
select tcat,'later' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'later')
union
select tcat,'earlier' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'earlier')
union
select tcat,'same' as state, 0 as howmany from @t t
where not exists(select * from basedata where category = t.tcat and state = 'same')

| category | state   | howmany |
|----------|---------|---------|
| a        | earlier | 1       |
| a        | later   | 1       |
| a        | same    | 0       |
| b        | earlier | 0       |
| b        | later   | 1       |
| b        | same    | 1       |


MySQL Index for searching for NULL values in text fields I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL.

blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for this query?

Yes, a prefix length of one will allow you to have a working index for looking up NULLs.

Here's the SQLFiddle: http://sqlfiddle.com/#!2/12bf3/1/0

The EXPLAIN says that it is using the desired index.

Of course you might want to try a larger test to make sure it performs as well in reality as you hope.


How to substitute substring in column A with matching string in map table I want to process foo's Column A to become B, using the replacement patterns found on a separate table bar.

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1, two, 2, three, 3' , 'one apple, two bananas, three strawberries' ),
    ( 'one, 1, four, 4, three, 3', 'one apple, four oranges, three strawberries' ),
    ( 'two, 2, four, 4, three, 3', 'two bananas, four oranges, three strawberries' )
  ) AS t(a,b);

CREATE TABLE bar
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1'    , 'one apple' ),
    ( 'two, 2'    , 'two bananas' ),
    ( 'three, 3'  , 'three strawberries' ),
    ( 'four, 4'   , 'four oranges' )
  ) AS t(pattern,repl);

I wouldn't do this. This is called "lexicon replacement". It's done using FTS's Thesaurus Dictionary. You can ask another question if you have a problem getting that working and you decide to go down that route.

That said, if you want to do this recursively, you can. It's certainly doable.

WITH RECURSIVE f AS (
  SELECT row_number() OVER (), *
  FROM bar
),
t(orig,working,row) AS (
  SELECT a , a , 0
  FROM foo
  UNION ALL
    SELECT orig, regexp_replace(working,pattern,repl), row+1
    FROM t
    INNER JOIN f
      ON row_number = row+1
)
SELECT orig, working
FROM t
WHERE row = (SELECT count(*) FROM f)
ORDER BY orig;

Output

           orig            |                    working
---------------------------+-----------------------------------------------
 one, 1, four, 4, three, 3 | one apple, four oranges, three strawberries
 one, 1, two, 2, three, 3  | one apple, two bananas, three strawberries
 two, 2, four, 4, three, 3 | two bananas, four oranges, three strawberries
(3 rows)

Note if you have a serial on bar then remove CTE f.


SQL server DB status runnable, sleeping, suspended, running and background meanings I am not able to get the meanings of DB Session  status. I searched it for some references, but not getting any help.
Can anyone explain these status (runnable, sleeping, suspended, running and background) what actually means, that would be help me a lot.
Thanks in advance.

The status values are documented with sp_who:

  The possible values are:

  Dormant - SQL Server is resetting the session.

  Running - The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run
  multiple batches. For more information, see Using Multiple Active
  Result Sets (MARS).

  Background - The session is running a background task, such as deadlock detection.

  Rollback - The session has a transaction rollback in the process.

  Pending - The session is waiting for a worker thread to become available.

  Runnable - The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

  Spinloop - The session's task is waiting for a spinlock to become free.

  Suspended - The session is waiting for an event, such as I/O, to complete.


Postgresql two tablespaces one on SSD and one on SATA III HD Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two separate tablespaces, the first one is already on a SATA III Seagate Baracuda and one that I will set on the SSD (once the SSD arrives from the store). In that case, postgresql.conf should have two separate configurations (one for the SSD and one for the HDD). Is there any way to make settings separate per tablespace or per database, without having to restart the server and reload configurations? I am benchmarking for scientific research and basically I want to set two identical databases on the two tablespaces and compare query read performance between the SSD and HDD for my methods.

You can set seq_page_cost and random_page_cost per tablespace via ALTER TABLESPACE without restarting Postgres.


Insert Data in seconds between 30 minutes time interval I want to divide my time interval from the start time to end time.

The elapsed time should be divided across 30 minute time intervals so that for this input

08:05:13   12:09:49

I should get following output.

One way to achieve this involves creating a table of 48 daily half-hour period start times:

CREATE TABLE #HalfHours
(
    IntervalFrom time(3) PRIMARY KEY
);

INSERT #HalfHours
    (IntervalFrom)
SELECT
    DATEADD
    (
        MINUTE,
        30 * (SV.number - 1),
        CONVERT(time(3), '00:00:00.000')
    )
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 48;

We can then use a query like the following:

-- The specified times
DECLARE
    @Start time(3) = '08:05:13',
    @End time(3) = '12:09:49';

SELECT
    HH.IntervalFrom,
    IntervalTo = CA.IntervalEnd,
    Elapsed =
        CASE
            -- Current period fully covered
            WHEN @Start <= HH.IntervalFrom AND @End >= CA.IntervalEnd
                THEN 1800
            -- @Start within current period, @End not
            WHEN @Start > HH.IntervalFrom AND @End >= CA.IntervalEnd
                THEN 1800 - DATEDIFF(SECOND, HH.IntervalFrom, @Start)
            -- @End within current period, @Start not
            WHEN @Start <= HH.IntervalFrom AND @End < CA.IntervalEnd
                THEN DATEDIFF(SECOND, HH.IntervalFrom, @End)
            -- @Start and @End both within current period
            ELSE DATEDIFF(SECOND, @Start, @End)
        END
FROM #HalfHours AS HH
CROSS APPLY (VALUES(DATEADD(MINUTE, 30, HH.IntervalFrom))) AS CA (IntervalEnd)
WHERE
    @Start < @End
    AND HH.IntervalFrom < @End
    AND HH.IntervalFrom >=
        CASE
            WHEN @Start < CONVERT(time(3), '00:00:30.000')
                THEN CONVERT(time(3), '00:00:00.000')
            ELSE DATEADD(MINUTE, -30, @Start)
        END;

This can be generalized quite easily in case the input is more than a single pair of start/end times.


Understanding the usage of version store in tempdb We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4.

I know tempdb would need the space depending upon the transactions, and how queries are designed to use it.

We had an issue for disk space getting full , where we analyzed how tempdb grew that big over a week.

After a reboot, via monitoring we came across 4-5 queries, which were holding up version store for more than 20 minutes, grew tempdb same day from its initial size of 2 GB ( 4 data files) to over 100 GB within a spam of 6-8 hours.

For example

query A holds the version store for 10 minutes where current version generation rate was 23.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 17 MB and tempdb file size was 10332 MB.

Later after an hour the query B ran, the version store for 16 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 45 MB and tempdb file size was 45332MB.

Similarly a query C ran after 3-4 hours, the version store for 15 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 19 MB and tempdb file size was 85332MB.

and so on on. during our monitoring for that day.

Now same queries run next day and day after,

We see that tempdb allocations and dellocations value running to somewhat similar during the very first day, but tempdb did not grew that fast.

same happens for upcoming 7-8 days where growth is approx 8-10 GB compared to first day where it grew to 100 GB for the similar queries running each day.

Also , the current output of select  count(*) from sys.dm_tran_version_store

is 196470 and its been 2 days since a reboot.

May be i am unaware of some process here, but please help me understand what could be the reason apart from those identified queries as we are trying to improve if they are the one causing tempdb to grow that much.

Note* tempdb size is 110 GB and Free space it shows is almost 109 GB.

There can be many reasons for that.

You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed.
Make sure that all information is stored on the disc. It may occur that your information is still stored in your memory. This could cause the hold of a version in your tempdb. You can force the latch process by using CHECKPOINT after your commit.

Hopefully this will help you.


T-SQL: How to switch all partitions from one table to another? Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?

For anyone wanting a solution to this, here's a stored procedure I cooked up:

------------------------------------------------------
-- Switches all non-empty partitions from a switch_in
-- table into a target table, and switches all replaced
-- partitions from the target table into a switch_out
-- table.
------------------------------------------------------
CREATE PROC [dbo].[SwitchAllPopulatedPartitions]
        @table     varchar(1000),
        @table_in  varchar(1000) = null,
        @table_out varchar(1000) = null
AS
    SET @table_in = ISNULL(@table_in, @table + '_SwitchIn');
    SET @table_out = ISNULL(@table_out, @table + '_SwitchOut');

    DECLARE @object_id     int = OBJECT_ID(@table);
    DECLARE @object_id_in  int = OBJECT_ID(@table_in );
    DECLARE @object_id_out int = OBJECT_ID(@table_out );

    DECLARE @SQL varchar(max) = 'TRUNCATE TABLE ' + @table_out + ';' + CHAR(13);

    SELECT @SQL = @SQL
                + 'ALTER TABLE ' + @table + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table_out + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
                + 'ALTER TABLE ' + @table_in + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
    from sys.partitions
    where OBJECT_ID = @object_id_in and index_id = 1 and rows > 0

    SET @SQL = @SQL + 'TRUNCATE TABLE ' + @table_in + ';' + CHAR(13);

    EXEC (@SQL);
GO


Difference between database dump (export) and database backup? What is the drawback using only EXP or EXPDP command for a database dump (and considering it as a backup) instead of taking backups using BACKUP DATABASE command (via RMAN)? What is a difference between database dump (export) and database backup?

I'll answer this at a high level for you. The two backup methods work at different levels. An RMAN backup is a physical backup and a Data Pump backup is a logical backup.

A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data.

An RMAN backup is a point-in-time backup of an entire database (for the purposes of this question). It backs up the physical blocks that make up the database (data files, control file, archive logs etc) and, in combination with the database archive logs, allows point in time recovery options.

In the event of a complete database loss an RMAN backup can be used to restore the complete database. However, a data dump taken using expdp would need a new database creating before the data could be imported using impdp.

For a hobbyist Oracle XE database (that may not be in archivelog mode), backups using expdp will probably suffice.

The Oracle Documentation covers this far better than I could ever explain. Oracle® Database Concepts - Backup and Recovery


information_schema.columns slow performance we have system that quite a lot and often asks database about:
SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE, DATA_TYPE, IS_NULLABLE
FROM "information_schema"."columns"
WHERE TABLE_NAME = 'some_instances'
 AND TABLE_SCHEMA = 'public'
ORDER BY ORDINAL_POSITION

Time to execute this query is 10s+, and it ruins overall performance
"information_schema"."columns" contains more than 5m records
Postgres version is 9.6
How to speed up such query?
UPD. cant paste here execution plan as it's off stackexchange limitation.
here is the link: https://github.com/mkdel/code_pile/blob/main/EXPLAIN_ANALYZE_BUFFERS

I think you have a very strange row estimation here (2064 vs 1)
->  Seq Scan on pg_class c  (cost=0.00..96893.03 rows=2064 width=76) (actual time=0.073..386.865 rows=1 loops=1)
                                                              Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND (((relname)::information_schema.sql_identifier)::text = 'some_instances'::text))
                                                              Rows Removed by Filter: 962568
                                                              Buffers: shared hit=69844

I think maybe this is affecting you PG 12 release notes:

Treat object-name columns in the information_schema views as being of type name, not varchar (Tom Lane)

Per the SQL standard, object-name columns in the information_schema views are declared as being of domain type sql_identifier. In PostgreSQL, the underlying catalog columns are really of type name. This change makes sql_identifier be a domain over name, rather than varchar as before. This eliminates a semantic mismatch in comparison and sorting behavior, which can greatly improve the performance of queries on information_schema views that restrict an object-name column. Note however that inequality restrictions, for example

SELECT ... FROM information_schema.tables WHERE table_name < 'foo';
will now use calculation“C”-locale comparison semantics by default, rather than the database's default collation as before. Sorting on these columns will also follow “C” ordering rules. The previous behavior (and inefficiency) can be enforced by adding a COLLATE "default" clause.

Edit, in pg 12 I get for your example query:
->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.30 rows=1 width=76) (actual time=0.018..0.018 rows=0 loops=1)
                                                         Index Cond: (relname = 'some_instances'::name)
                                                         Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                   ->  Seq Scan on pg_namespace nc  (cost=0.00..1.09 rows=1 width=68) (never executed)
                                                         Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))


Postgresql can I access inline array items returned from function? Say I have a function which returns an array. Can I access its items inline inside a select query? I can not use sub-querying since function relies on query parameters. And I can not declare outside variable for the same reason.

I expect somewhat like this:

select foo,bar, function(foo,bar)[1] as array_item_1, function (foo,bar)[2]  as array_item_2 from table...

Currently I return function(foo,bar) as array_items and iterate it in application which is really cumbersome. I want db to return prepared outputs.

Thanks.

Can I access its items inline inside a select query?

Yes, but you need an extra pair of parentheses (as documented in the manual)

select foo, bar,
       (function(foo,bar))[1] as array_item_1,
       (function(foo,bar))[2] as array_item_2
from table...

However, unless the function is defined as stable or immutable, each expression will call the function. So in the above, if the function is defined as volatile (the default!) then it would be called twice. In that case using a derived table is a better approach:

select foo, bar,
       result[1] as array_item_1,
       result[2] as array_item_2
from (
  select foo, bar, function(foo,bar) as result
  from table...
) as t


Aggregate interval data to hourly I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?

You can do a simple GROUP BY with DATEPART applied to the time column. I'm assuming that you'll always get a row every 15 minutes and you don't need to worry about gaps in the data.

SELECT
  code
, date_column AS [date]
, DATEPART(HOUR, time_column) AS hourly
, SUM(value) AS value
FROM test_table
GROUP BY
  code
, date_column
, DATEPART(HOUR, time_column);

The following references may be useful to you:

DATEPART (Transact-SQL)
GROUP BY (Transact-SQL)
SUM (Transact-SQL)


Matching a value with every element of a set of values - Postgresql I'm new in Postgres and I can't achieve what I'm trying to do.
I have a list of films in a Films table

ID_FILM    FILM NAME
1          Terminator
2          Forrest Gump
3          Nueve reinas

A Country table

ID_COUNTRY  COUNTRY
1           ARGENTINA
2           ENGLAND
3           USA
4           FRANCE

Finally, I have a table that contains in which countries the film was projected

ID_FILM     ID_COUNTRY
1           2
2           4
3           1
3           2
3           3
3           4

As you can see, the film Nueve Reinas (3) was projected in all countries (1, 2, 3, 4).
Well, that's what I'm trying to show. I'm trying to show the films projected in every country.
What I got was:

SELECT a.name FROM film a
JOIN film_country b ON a.id_film = b.id_film
WHERE b.id_country = ALL (SELECT id_country FROM country)

But this isn't quite working.

This kind of query is called a relational division, where you "divide" film_country by country to obtain a "quotient" that represents a number of films, and then filter out on this number.

The canonical way to write it would be:

SELECT name FROM film JOIN
  (SELECT id_film FROM film_country
   GROUP BY id_film
   HAVING count(*)=(select count(*) from country)
  ) s
ON (film.id_film=s.id_film);


Error in loading data using DB2 load command in DB2 10.5 I have a IBM DB2 database in our application. We have a monthly Java batch process which loads data in the tables using

"load Replace"
sql command. Around millions of record will be updated every month in this batch process.
The batch was running fine without any issues in the DB2 version 9.5 . Recently the database was updated to DB2 Version V10.5. After the upgrade , we face the following issues in the batch process. (No change in the code)

There is an Interrupted Exception occurring in the middle of the process.
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=3.69.24

The data in which the data gets loaded is also getting slow after some time. At first the data ets loaded in around 50,000 rows /sec. After some time it is getting loaded at the rate of 1000 rows/second.

TheCPU utilization is also getting increased gradually as the load starts.

We select data from the input tables using Java JDBC connection.Then we process the data and store the data in temporary files. From the files we insert data in the utput tables using the load command. Is there any upgrade issue or enhancement in DB2 10.5, that does not support this. Will using cursor solve this issue. I am also not sure if there is any connection problems in the new version of Db2.
Any help is highly appreciated. Please comment for any additional information required.

Have you run runstats recently for at least the relevant tables and indexes?
Are you sure you have index(es) to support your monthly batch?
Have you checked the bufferpool hit ratio when the batch is running?
db2top and db2advis may help you answering these questions.

The secret optimizer was surely changed and it may see tiny details differently than the previous version, that can cause dramatic differences. It's always a good idea to pamper it with runstats when unexplainable slowdown is seen and check the rest when there is no benefit.


How to know max specified memory in Oracle XE11g release 2? What is the max specified memory in Oracle XE11g release 2? I have read in Oracle documentation that the allowed max memory for database is 1GB RAM. Is that per database or per session? And if it is per database, then how can I modify it per session?

The documentation is clear about this. See Oracle Database Licensing Information

  Oracle Database Express Edition  Oracle Database Express Edition
  (Oracle Database XE) is an entry-level edition of Oracle Database that
  is quick to download, simple to install and manage, and is free to
  develop, deploy, and distribute. Oracle Database XE makes it easy to
  upgrade to the other editions of Oracle without costly and complex
  migrations. Oracle Database XE can be installed on any size machine
  with any number of CPUs, stores up to 11 GB of user data, using up to
  1 GB of memory, and using only one CPU on the host machine. Support is
  provided by an online forum.

The 10g documentations shows explicitly: 1 installation, 1 instance on a machine. Compared to 10g in 11g you can use more userdata on disk.


How to reclaim the storage space used by a heap? I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:

How do I reclaim unused space from MyTable?

Said table is declared as follows:

CREATE TABLE [dbo].[MyTable](
    [RecordID] [varchar](50) NULL,
    [DocumentID] [nvarchar](100) NULL,
    [DocumentName] [varchar](100) NULL,
    [DocumentOwner] [varchar](50) NULL,
    [DocumentTemplate] [varchar](50) NULL,
    [DocumentData] [ntext] NULL,
    [DocumentDate] [char](10) NULL,
    [DocumentTime] [char](10) NULL,
    [DocumentSize] [int] NULL,
    [DocumentUpdateVersion] [int] NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL
)

What steps should I follow to free unused space?

If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.

Your options are:

ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard Edition, building a new copy of it with everything packed in nicely like sardines
Do your deletes with the TABLOCK hint - which can prove problematic for concurrency, since as it indicates, will take out a table lock to do the deletes
Truncate the table - which will deallocate all of the pages, not just the empty ones, so it has the unfortunate drawback of erasing all your data.
Put a clustered index on it - if you frequently update & delete your data, then you should do as Beyonce says: put a clustered index on it. Otherwise, you end up with the empty-space problem that you're having now, plus the forwarded-fetches problem.


Why is a GiST index used for filtering on non-leading column? I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query. Why is that? Is this a special feature of GiST indexes?
CREATE TABLE t1 (
    i INT,
    j INT,
    k INT
);

INSERT INTO t1
SELECT i, j, k
FROM   GENERATE_SERIES(1, 100) AS i,
       GENERATE_SERIES(1, 100) AS j,
       GENERATE_SERIES(1, 100) AS k;

CREATE INDEX ON t1 USING GiST(i, j, k);

EXPLAIN SELECT * FROM t1 WHERE k = 54;

QUERY PLAN
Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)
  Recheck Cond: (k = 54)
  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)
        Index Cond: (k = 54)

db<>fiddle here

an index can only be used when we have predicates for the leading (or all) columns.

In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:

Working of indexes in PostgreSQL

But mostly wrong for GiST indexes. The manual:

A multicolumn GiST index can be used with query conditions that
involve any subset of the index's columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned. A GiST index will be relatively
ineffective if its first column has only a few distinct values, even
if there are many distinct values in additional columns.

So, generally, you put columns (or expressions) with the most distinct values first in a GiST index.
Related:

PostgreSQL exclusion constraints in a bitemporal setting?

It's different for other index types. For (also very common) GIN indexes:

A multicolumn GIN index can be used with query conditions that involve
any subset of the index's columns. Unlike B-tree or GiST, index search
effectiveness is the same regardless of which index column(s) the
query conditions use.

The whole chapter of the manual is recommended reading.