Data center from scratch which way to go My budget for a data center (hardware,OS licences, SQL Licences WithOut CALS ) is around $50K~$60K.

I have needs for around 200 users and my apps works only works in Microsoft environment (Window Server and SQL Server).

I've talked to a lot of "so called" experts and every one had different vision for my data center, as much I talking with vendors I am more puzzled how I should design my data center, perhaps this is because I did not meet yet any SQL Server expert who knows what to do.

My living area is so poor whit IT Specialist.

My app is not mission critical and We can afford down time up to 1 working day (8 hours).
I now have 1 main database (80GB-DATA and 20GB of SQL Server log) for writing transaction
That database is transactional replicated by SQL Server to second physical SQL Server web servers from there reading large reports.

I have around 200 users which over WEB server communicating with my SQLs

Here is what I considered so far to do.

First scenario

3X Physical servers (no storage, no SAN, no Virtualization) one server will act as Web Server other two is going to be SQL server whit 2012 standard edition each server will have 96GB ram and Intel Xeon 6C Processor 2Hz. In this scenario I can afford  IBM enterprise SSD discs I can have at TWO SQL servers 8 X 250 GB ssd SATA disc. First server will replicate database to other.
Second scenario

2X physical servers one IBM or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage. All of that virtualized by vmware and running on one SQL Standard Server. without SQL replication ,but whit 200GB of ram in SQL-os.

Personally I do not have experience with virtualization and storage systems. I more like SQL Server replication.

Here is some questions which I seek to have direction which way to go.

Should I avoid virtualization for machines which is dedicated to running SQL Server ?

Is SQL Server 2012 ready for production? I know that this is perhaps silly question but one of IT guys with plenty of certificates say that they always running production instances on second to last Microsoft platform, I am really confused how is He strong in claiming that they not going to instal and configure MS Servers (OS, SQL Server) on 2012 generation.

Do I need SSDs beside plenty of RAM. Unfortunately I can't do much in redesigns queries!

Here is what I have captured during 3 hour monday peek time.

Reporting Database (replication subscriber)

Replication

This is transactional DB (replication publisher)
Trasaction DB

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all

As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your costings and a really good backup solution for your business critical data is not something you should try buy/create cheaply).

If you are happy with this arrangement in terms of resilience and general performance, and you are just upgrading the servers mainly to replace old hardware, then go with option 1 and upgrade the current design. Remember that if you build a different infrastructure you may run into problems you've never seen before and if you do they may turn up at the least convenient time for your business. If you don't have the knowledge/experience to fix things fast (and it isn't available in anyone else in the company you can call on) this could be very expensive in terms of business lost due to downtime and paying an expert to fly in and help.

  Do I need SSDs beside plenty of RAM

That very much depends on your applications write patterns. Given you have ~80Gb of data you might find all your common working set could be held in RAM most of the time so for read heavy loads you will see little or no difference between the SSDs and spinning metal for day-to-day operations as the drives won't be touched for most reads. That data held in memory will be lost in some circumstances (such as when SQL server is shut down for what-ever reason) and it will take a while for the working set to get loaded and at this point the SSDs will help quite noticeably. For writes data will still of course need to hit the disk, so for write heavy loads the SSDs will help.

Do remember that Standard Edition will only use up to 64Gb of memory per instance (see http://msdn.microsoft.com/en-us/library/cc645993.aspx#CrossBoxScale) so you may not see all that 96Gb get used in either box.

  I now have 1 main database (80GB-DATA and 20GB of SQL Server log)
  I can have at TWO SQL servers 8 X 250 GB ssd SATA disc
  or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage

Am I reading this wrong, or are you expecting your data size to increase a fair bit over the active service life of the new hardware? In RAID10 those SSDs will give you ~1TB of space which is considerably bigger than the ~100Gb you are using now, and that large collection of spinning metal even more so (3Tb+ unless you configure a redundancy level over 2:1), so you might be over-spending here for your near-/mid-term needs.


MySQL metadata search? I'm coming from SQL Server background and getting into a project that uses MySQL. Is there any way to search the metadata of a MySQL database like there is with the Redgate SQL Search tool for SQL Server?

What are you looking for? Most things will be in the INFORMATION_SCHEMA like discussed here:

https://dev.mysql.com/doc/refman/5.7/en/information-schema.html


Two queries, one JSON result I have got two queries, but I just want one result:
1 Query, give me all the data of the column
 SELECT * FROM dbo.Notificacion
 FOR JSON PATH, ROOT('notification')

2 Query give me all the column names
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'notificacion' ORDER BY ORDINAL_POSITION
 FOR JSON PATH, ROOT('COLUMN_NAME')

This way, I can make the view of the application more dynamic.
I want JSON something like this:
{
  "COLUMN_NAME": [
    {
      "COLUMN_NAME": "ID"
    },
    {
      "COLUMN_NAME": "Active"
    },
        {
      "COLUMN_NAME": "date"
    },
        {
      "COLUMN_NAME": "dateUpdate"
    }
  ],
    "notification": [
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
        {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    }
  ]

}

This may not be the most optimal way to skin the cat but when I need to combine different JSON results in a single output, I typically use the JSON_QUERY() function.
Here is one way to apply it in your case:
SELECT
  COLUMN_NAMES = JSON_QUERY(inf_sch.json_result)
, Notification = JSON_QUERY(notif  .json_result)
FROM
  (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'notificacion'
    ORDER BY ORDINAL_POSITION
    FOR JSON PATH
  ) AS inf_sch (json_result)
, (
    SELECT *
    FROM dbo.Notificacion
    FOR JSON PATH
  ) AS notif (json_result)
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER

The two derived tables are your two original queries with the ROOT() specifier stripped off.  They each produce a JSON output.  Each output is essentially injected into the outer level output using the JSON_QUERY function.  The function makes sure that its argument is injected as a JSON object rather than an nvarchar string representing one.
The WITHOUT_ARRAY_WRAPPER option at the end of the query makes sure the top-level JSON is returned as a single object rather than an array.
Again, I understand that this may not be the best method.  Perhaps a more concise and elegant way could be achieved using the AUTO option of the FOR JSON clause.  I admit I am not very familiar with it.  Hopefully someone can come along and show both of us, as well as the rest of the audience, how to successfully use it in your scenario.


Query finds no values when sharppend I am trying to join two datasets and find the most recent certification date
I the following 2 datasets
Certifications

(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, 'Judge Level 3', 'judge', 30, 2),
(4, 'Olympic Judge', 'olympic judge', 30, 2),
(5, 'Junior Leader', 'leadership', 10, 1),
(6, 'Trainee', 'leadership', 10, 1),
(7, 'Instructor', 'leadership', 10, 1);

members_certifications

(1000052, 7, '2016-07-01'),
(1000053, 1, '2016-07-01'),
(1000053, 3, '2018-07-01'),
(1000053, 4, '2019-07-01'),
(1000053, 5, '2020-07-01'),
(1000059, 1, '2016-07-01'),
(1000059, 2, '2017-07-01'),
(1000059, 5, '2016-07-04'),
(1000059, 6, '2016-07-01'),
(1000059, 6, '2016-07-02');

I would like the result set to be

1000059, Junior Leader , leadership, 10, 2016-09-04
1000059, Judge,Judge Level 2, 20, 2016-09-04

I created a exact example http://sqlfiddle.com/#!9/20c30b1/3/1  MySql version 5.6
The first query produces a large data set when I reduce the query by setting a specific id (in this case 100059) I get 0 results.
Also is there a more query efficient or less bulky way create this query.
CREATE TABLE `certifications` (
  `id` int(11) NOT NULL,
  `title` varchar(254) NOT NULL,
  `type` varchar(254) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `expiration_interval` tinyint(4) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `members_certifications` (
  `member_id` int(11) NOT NULL,
  `certification_id` int(11) NOT NULL,
  `effective_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query 1
SELECT members_certifications.member_id AS member_id,certifications.title AS title,
certifications.type AS type,certifications.sort_order AS sort_order,
MaxTable.effective_date AS effective_date FROM members_certifications, certifications,
(SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,
 MAX(members_certifications.effective_date) AS effective_date
 FROM     members_certifications, certifications
 WHERE     members_certifications.certification_id = certifications.id
 GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable
 WHERE  members_certifications.certification_id = certifications.id
 AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date
ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title;

query II (0 results)
SELECT members_certifications.member_id AS member_id,certifications.title AS title,certifications.type AS type,certifications.sort_order AS sort_order,MaxTable.effective_date AS effective_date FROM members_certifications, certifications,  (SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,     MAX(members_certifications.effective_date) AS effective_date   FROM     members_certifications, certifications   WHERE     members_certifications.certification_id = certifications.id   GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable WHERE  members_certifications.certification_id = certifications.id  AND members_certifications.member_id = 100059  AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title ;

I'm not exactly sure what you are asking, but I would suggest that you use aliases and ANSI joins for your queries:
SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date
      FROM members_certifications mc2
         , certifications c2
      WHERE mc2.certification_id = c2.id
      GROUP BY c2.type, mc2.member_id
     ) MaxTable
    ON c1.type = MaxTable.type
   AND mc1.member_id = MaxTable.member_id
   AND mc1.effective_date = MaxTable.effective_date
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

Regarding your second query in the fiddle, there is a slight difference between:
1000059 and 100059

So if you change that to:
SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date
      FROM members_certifications mc2
         , certifications c2
      WHERE mc2.certification_id = c2.id
      GROUP BY c2.type, mc2.member_id
     ) MaxTable
    ON c1.type = MaxTable.type
   AND mc1.member_id = MaxTable.member_id
   AND mc1.effective_date = MaxTable.effective_date
WHERE mc1.member_id = 1000059
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

does that give you the expected answer?
MySQL 8+ supports window functions which are far more efficient. Instead of doing a join against a sub-select you can use a window function to determine max_effective_date, and then filter with that:
SELECT member_id, title, type, sort_order, effective_date
FROM (
    SELECT mc1.member_id AS member_id
          ,c1.title AS title
          ,c1.type AS type
          ,c1.sort_order AS sort_order
          ,mc1.effective_date
          ,Max(effective_date) over (partition by member_id, c2.type) AS max_effective_date
   FROM members_certifications mc1
   JOIN certifications c1
        ON mc1.certification_id = c1.id
) AS T
WHERE max_effective_date = effective_date
ORDER BY member_id, sort_order, type, title;


Oracle, how to manually re-instantiate a PL/SQL package? I have a web application which calls a procedure from an Oracle PL/SQL package. Each time the package is replaced in turn of a new release the application runs into an exception ORA-06508

java.sql.SQLException: ORA-04068: Der bestehende Status der Packages  wurde  aufgehoben
ORA-04061: Bestehender Status von package body "OPS$XY.PACK_XY" wurde annulliert
ORA-04065: Ausführung nicht erfolgreich, package body "OPS$XY.PACK_XY" wurde geändert oder gelöscht
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "OPS$XY.PACK_XY" konnte nicht gefunden werden
ORA-06512: in Zeile 1

From the Oracle-Docs I got the info that the package needs to be re-instantiated, which is done by the next PL/SQL call after the ORA-06508- error.

Is there a way to re-instantiate this package manually right after CREATE OR REPLACE PACKAGE ?

The answer is NO.

Now you have two options IMHO.

You can make the package stateless by removing package variables.
You can catch this ORA-ORA-04061 in the application code and re-execute the command.

Useful links:
AskTom
ORA-04061: existing state of  has been invalidated


"Zero" downtime and minimal space requirements for upgrade to PostgreSQL 9.2 Need to migrate from PostgreSQL 8.3.x to 9.2

Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is rebuilt with --disable_integer_datetime. This implies all future migrations would also have to retain this setting. This isn't acceptable in our case.

Constraint 2: The application using the 8.3.x database can't go down (there isn't any allowed downtime for at least a portion of its function). We could, however, hold off all the db updates for a short while. This gives us a window of about an hour at most.

Proposed solution (or actually - can the experts out there validate if this idea will work or if it makes any sense):
The idea is to move tables over in parallel (processes or threads) using the copy command from the old to the new version. Assuming that both old and new servers are running (on different ports) ...
Question is whether the copy command on the old version can be sent "directly" into the new version? Optimally, if it can be done without an intermediate file.
Has anyone tried anything like this before? Is it practical? Does it even make sense?

Thanks in advance for your replies.

The two constraints that you have specified:

Very low downtime
Limited disk space use

conflict rather seriously.

You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.

I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.

At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.

Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.

This might be a good opportunity to see professional support from people who have experience dealing with these issues.


Is there a more efficient way to search for a record but only if it has associations? I'm using Postgres 9.5.  I want to select some records, but only if they have at least one association in another table.  I came up with this query:

select distinct m.name, m.id
from machines m, parts rt
where m.id = rt.machine_id
  and m.name like '%Name%';

but I don't think this is particularly efficient, since if there are 50000 associations, I think they're all loaded and then "distinct"ed through, which seems a little inefficient.  Is there a more efficient way to check load a record but only if it has one or more associations?

The way your query is structured, you can just write it using an EXISTS expression:

SELECT
    m.name, m.id
FROM
    machines m
WHERE
    m.name LIKE '%Name%'
    AND EXISTS (SELECT *
                  FROM parts rt
                 WHERE rt.machine_id = m.id
               ) ;

If you want the query to be efficient, take into account that the most difficult part will probably be checking the machine name. Consider indexing machines.name using a trigram index. Trigram indices allow for fast finding LIKE '%something%' conditions.

CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx
    ON machines
    USING GIST (name gist_trgm_ops);

Most probably, the database planner will make an execution plan where it will first find the machines whose name is LIKE '%Name%' (using the name_like_idx index), then, for every machine in this subset, it will check if there are parts associated with them; if so, they will be part of the result.

For efficiency, it is also assumed that there is also an index on parts.machine_id, because this will then be how PostgreSQL will efficiently check for the existence of the part. If machine_id is a foreign key, it is always advised to index it.

This query doesn't need DISTINCT (assuming that the ids are already unique).


SQL Server - Data and Log Separation We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is our usual practice. However, a DBA has advised that this may not be the ideal configuration and that we may see better performance storing both the data and log files on the 12K HDD.

Not being a DBA, I'm quite happy to defer to an expert, but I'm not fully convinced that this configuration is the best approach. I would have thought that there would have been advantages in separating the data from the logs in terms of parallelism regardless of the specs of the HDD's.

What are your thoughts?

The log vs. data separation is sound advice, but the devil is always in the details. A write intensive workload that commits frequently requires the log stream to flush to disk as fast as possible. This in turn requires a sequential write pattern, as the log does, undisturbed by any other operation. Hence, isolate the data writes (and reads!) from the log writes.

But the question is: is your workload write sensitive? Is the log commit flush wait the most important performance bottleneck? You may have a tempdb sensitive workload. Or you may have a workload that is not even touching the disk. Lock contention, memory restrictions, latch contention all could be your nemesis, none would be solved by how you separate LDF and MDF. And lets not forget that the modern SQL Server instance usually hosts several databases, implying several separate log streams.

In lack of further evidence, both you and your admin are equally right/wrong. You need to measure, identify your problems, and address them accordingly. Read How to analyse SQL Server performance for general directions how to identify (and address) your bottleneck(s).

Before I go, I recommend you read:

Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 2: sequential inserts)
Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)

Not saying that, w/o proper consideration of the workload and value, you should use SSDs (yes, you should, always...), but just food for thought.


Avoid foreign keys on an 800-GB database? This question is for the experienced developers out there.

My friend recently told me that his employer has a legacy code base whose MySQL database has now grown to around 800 GB in size. What's interesting is that there are very few foreign key constraints, resulting in some duplicated bad data, but since that doesn't harm anything (apparently), nobody is overly concerned.

Now I can't imagine life without foreign keys, so when I asked him why there were no such checks, he replied by saying that a foreign key is another index that the database has to maintain, and takes more space.

Yes, space. So here's my question: Just how much more space can an index take? 10% of the DB? 20%? Even if his reasoning was wrong, there's no denying that foreign keys entail a performance penalty. Would you, as a developer, let go of foreign keys and instead rely on code to do proper housekeeping once the DB approached 1 TB in size?

...a foreign key is another index that the database has to maintain

I think your colleague may be laboring under the idea that columns containing foreign keys must be indexed, which isn't always the case.  (See ypercube's comment below.)  Foreign keys aren't anything other than values that the schema gives a special meaning.  The decision to index such a column is the same as for any other: if it will frequently be the subject of selection criteria (i.e., part of a WHERE clause), index it according your DBMS's preferred practices.

Indexes come into play in the parent table's primary key column because those columns meet the criteria described above.  More on that in a minute.

  ...and takes more space.

Almost everything in this field is a space/time trade-off, and indexes are one of them: you consume more storage during writing to consume less time during reading.  It would be very hard to argue that adding an index to a table doesn't consume additional space, because it does.  Making those kinds of decisions without the context of the whole schema is failing to see the forest for the trees.

As Codd described it, normalization was a very early application of what would become the DRY principle.  Non-normalized data is inherently repetitive; normalized data is not.  References in child tables are (necessarily) repetitive but have the benefit of being smaller than the original.  (Normalization is more for preserving integrity rather than space, but that's another discussion.)

For example, a million copies of the string 1600 Pennsylvania Avenue stored in a million rows of a non-normalized table occupy 25 MB.  Move that string to a separate table of addresses (where it's stored exactly once), refer to it a million times using an eight-byte, numeric foreign key and the whole mess occupies 8 MB plus 25 bytes for the actual string.  That's space savings of about 67%.

This foreign-key relationship may work better performance-wise if the table of addresses (parent) is indexed by its primary key.  That index is a lot smaller because the N:1 nature of normalization means that, on average, there will be fewer rows to index and the index won't need updating as often.

It's hard to say without seeing the schema, but a lack of normalization may be the reason the database grew to 800 GB in the first place.

  Just how much more space can an index take?

That's a hard question to answer in general terms.  The idea behind most forms of indexing is to allow the traversal of a small amount of data to locate something in a large corpus.  A side effect is that the data structures used to represent it tend to end up much smaller.  That said, a poorly-designed or poorly-selected mechanism combined with pathologically-bad data can lead to indexes that are much larger than the original data.  The opposite, more-usual case is that the index is a small fraction.  I've done a lot of work with full-text indexers and find they tend to chew up less than 5% of the original data.  But that's just for my use cases and may not apply to anyone else's.


Different services using different port pointing to same datadir folder Let say I have already installed service MySQLa with mysqla.ini (port number=3306) setting and datadir folder in ...\data

Is it possible then, I install another service MySQLb with mysqlb.ini (port number=3307) setting and sharing datadir folder in ...\data
?

Would there be any conflict with the system schema (mysql, information_schema and performance_schema)?

I have tried locating the datadir into different folder (...\dataa and ...\datab) and it is working well.

NO!  Do not attempt to have the same files accessed by different instances of MySQL.  The code has now way of coordinating access between them.  (If it did, it would slow MySQL down to a crawl.)


Deadlock from select statements I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue?

Deadlocked processes:   11278
Victim process: 5185
Lock type:  Index/(Key)

Both queries are the same

  object: unicas_ux.dbo.Course, lock mode: Exclusive (X), index: NCIDX_Course_TermId

  (@P0 bigint)select courses0_.TermId as TermId17_130_1_, courses0_.Id
  as Id1_45_1_, courses0_.Id as Id1_45_0_, courses0_.ConvertedGrade as
  Converte2_45_0_......courses0_.VerifiedCredits as Verifie14_45_0_,
  courses0_.VerifiedDate as Verifie15_45_0_, courses0_.VerifiedGrade as
  Verifie16_45_0_ from dbo.Course courses0_ where courses0_.TermId=@P0
  order by courses0_.CreatedDate asc

Deadlock happens when one query acquires a lock on a object (rows, data pages, extent, tables etc) and other resource tries to access it. Smallest unit in SQL Server is data pages and SQL holds a lock on page while working on it. So, yes it is possible that two select statement can create deadlock.

Solution:

WITH(NOLOCK) - If your data is fine to be read dirty
Use Read committed snapshot or Snapshot isolation
Try to keep transaction small and in batches
Use bound connection - it will allow two connection to use same transaction & lock

This answer is based on your question, as your screenshot doesn't contain any information.


Get ID which has two rows with different values for one column Basically, I have a dataset that is a list of internal location identifiers attached to an ID (so each 'set' of geographical points has its own ID). So an 'id' column and a 'location' column exist in the table.

I need a way to find an ID which contains two different locations, that is:

ID | location
--------------
1  | a
1  | b
1  | c
2  | a
2  | c
2  | d

If I looked for 'a' and 'd', I would get 2.If I looked for 'a' and 'b', I would get 1. If I looked for 'a' and 'c', I would get either 1 or 2.

I am using PostgreSQL 9.4.

select id
from the_table
where location in ('a', 'd')
group by id
having count(distinct location) = 2;


I want to create a mirror in SQL Server 2008, but the database is in the status "restoring ..." I'm trying to create a mirror in SQL Server 2008, and did the following:

On the principal server

USE master;
GO
ALTER DATABASE Test
SET RECOVERY FULL;
GO;

BACKUP DATABASE Test
TO DISK = N'D:\Test.Bak' WITH FORMAT
GO
RESTORE VERIFYONLY
FROM DISK = N'D:\Test.Bak'
GO

BACKUP LOG Banco
TO DISK = N'D:\TestLog.Bak' WITH FORMAT
GO
RESTORE VERIFYONLY
FROM DISK = N'D:\TestLog.Bak'
GO

On the mirror server:

USE master;
GO;
RESTORE DATABASE Teste
FROM DISK = N'D:\Test.bak'
WITH NORECOVERY,
MOVE 'Test_Data' TO
N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Data.mdf',
MOVE 'Test_Log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Data.ldf'
GO

USE master
GO
RESTORE LOG Test
FROM DISK = N'D:\TestLog.bak'
WITH NORECOVERY
GO

I can not select, users can not change, change nothing!

how do I fix this?

You appear to be attempting to access the database on the mirror machine.  You can only access it from the Principal.

If the Principal has a problem or shuts down or whatever, the mirror will become the Principal and you then use that machine to access the database.


Redshift: Return rows if value exists in cell array How do I return the rows that include a particular value in a column's cell array?

Imagine we have a table like so:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
2     John    yyy.yyy.yyyy    [1,5,2]
3     Peter   zzz.zzz.zzzz    [6,2,6]

I need to create an SQL query that will return the rows where the value '6' exists in the values array.  So the expected output would be:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
3     Peter   zzz.zzz.zzzz    [6,2,6]

We are working in Redshift.  So, if it is easier, the possibility of using the json_extract_array_element_text('json_string', pos) function could be used.  Please note that the length of the values arrays could be different from each other.

I will give it a try :

First use this query to find the maximum length of longest array for values field in your SampleTable:

SELECT MAX(JSON_ARRAY_LENGTH(values)) FROM SampleTable;

Then create a view according to the result you get from above query using the below format( I am supposing the max size output as 2 with index starting from 0 as per the sample table data you have provided ):

CREATE VIEW seq_0_to_2 AS (
    SELECT 0 AS i UNION ALL
    SELECT 1 UNION ALL
    SELECT 2
);

Then use the below query to get the desired results:

WITH exploded_array AS (
    SELECT id,name,phone, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(values, seq.i) AS NewExtractedValues
    FROM SampleTable, seq_0_to_2 AS seq
    WHERE seq.i < JSON_ARRAY_LENGTH(values)
  )
SELECT *
FROM exploded_array
where NewExtractedValues = 6;

Hope this helps.

Inspired from https://torsten.io/stdout/expanding-json-arrays-to-rows/


How to model data which requires index on all columns? I have following use case where there are bunch of IDs (GUID) which are mapped to each other. I need to lookup all the IDs given any single ID. For example, let's say my data has following columns:

uid, id1, id2, id3, id4, id5

How to support the following queries:

1. SELECT uid FROM table WHERE id1=x;
2. SELECT id1, id2, id3, id4 FROM table WHERE uid=xyz;

The number of IDs can change (I have to add new columns).

I can create indexes on all columns but that would not be very effective. Should I model this data differently?
Is there any NoSQL database which can help model this use-case?

To give more business context for clarity, we have data coming from multiple data sources which have their own custom_id. But they all identify the same person (UID). Thats why we need to map all the data source ids to our own internal UID. Hence the question.

The number of columns have max bound of 20-25.
They are nullable.
The number of id's can be different per row (thats why nullable).
Datatypes are STRING for all.
UID will be primary key.

You can model your table as a dictionary with two columns:

UID
ID_VALUE

Both would be indexed with dedicated index.

You can then add as many ID_VALUE rows for any UID and search for an UID given ID_VALUE.

Queries would then look like

SELECT UID FROM table WHERE ID_VALUE=x;
SELECT ID_VALUE FROM table WHERE UID=xyz;

Second query would return more than one row.

EDIT

  I need to lookup all the id's given any single id

As long as you have all IDs bound by a common column, in this case UID, this should be possible. Consider following query:

SELECT t2.id_value
  FROM table t1
      ,table t2
 WHERE t1.uid = t2.uid
   AND t1.id_value = x

This will return all id's given a single id X.

It really doesn't matter what are the values of ID_VALUE. They may be other UIDs pointing to this one or any other arbitrary number. If you want to search of any ID_VALUE including the UID value, you can include this UID value into the set too so you will have one row where UID = ID_VALUE.

EXAMPLE

Let's say you have following data in your model:

UID   ID1   ID2  ID3
--------------------
  1     2     3    4
  2     1     4
  3     4
  4     2     3

You can model this data in a dictionary table like so:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  2          1
  2          4
  3          4
  4          2
  4          3

If you want to include the UID in the list so the last query in my example would return ALL of IDs including the UID, you would then have:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  1          1 <---
  2          1
  2          4
  2          2 <---
  3          4
  3          3 <---
  4          2
  4          3
  4          4 <---

I've marked the rows added to satisfy this requirement.
Executing the query:

SELECT t2.uid
      ,t2.id_value
  FROM table t1
      ,table t2
 WHERE t1.uid = t2.uid
   AND t1.id_value = 1

Will result in:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  1          1
  2          1
  2          4
  2          2

This will return all IDs given any single id as per requirement. Note that ID_VALUE of 1 can be found in both sets with UID = 1 and UID = 2, both are returned.

This would be equivalent to executing this query on your flat design:

SELECT * FROM table WHERE UID=1 OR ID1=1 OR ID2=1 OR ID3=1


Why can't SQL Server find a seek plan? Is this a bug? create table Test1 (Id int not null, H char, primary key (Id), index i1 unique (H))
create table Test2 (Id int not null, H char, primary key (Id), index i2 unique (H))
insert into Test1 values (1, 'A'), (2, 'B')
insert into Test2 values (1, 'A'), (2, 'C')

this query fails Query processor could not produce a query plan because of the hints defined in this query. (if I remove the forceseek hints then it runs but it will scan one of the two tables - even if it's extremely large)

select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where a.H = 'A' or b.H = 'C'

this equivalent query runs fine:

select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where a.H = 'A'
union
select * from Test1 a with (forceseek)
join Test2 b with (forceseek) on a.Id = b.Id
where b.H = 'C'

and gives plan

..I don't understand why SQL Server doesn't run the first query optimally. Is this a known issue? Does it have a name?

For the original query in question optimizer considers(*) two main alternatives normally (when FORCESEEK hints aren't used on any of the tables).

First one is simple join

when indexes from both tables are scanned entirely (without predicates), and predicate a.Id = b.Id AND (a.H = 'A' OR b.H = 'C') is tested at join node.

Second one is apply form (more about it here)

when index on one of the tables is scanned on the outer side of the Nested Loops join, and then index data is used on the inner side to seek into clustered index on the other table using b.Id = a.Id seek predicate and additional a.H = 'A' OR b.H = 'C' predicate. It can be expressed in T-SQL as

SELECT *
FROM Test1 a
    CROSS APPLY (
        SELECT *
        FROM Test2 b
        WHERE b.Id = a.Id AND (a.H = 'A' OR b.H = 'C')
    ) appl

When FORCESEEK is used on one of the tables, simple join alternative falls out from consideration, but optimizer additionally considers modified form of apply

which unions two seeks into clustered index on the other table. One with b.Id = a.Id seek predicate and additional b.H = 'C' predicate. And the other with b.Id = a.Id seek predicate beyond Filter with a.H = 'A' startup predicate. It can be expressed in T-SQL as

SELECT *
FROM Test1 a
    CROSS APPLY (
        SELECT DISTINCT u.Id, u.H
        FROM (
            SELECT b.Id, b.H
            FROM Test2 b
            WHERE b.Id = a.Id AND a.H = 'A'
            UNION ALL
            SELECT b.Id, b.H
            FROM Test2 b
            WHERE b.Id = a.Id AND b.H = 'C'
        ) u
    ) appl

There are more alternatives actually (that use spooling on the inner side of the apply, for example, or different physical join implementation for simple join, or non-clustered index scan instead of clustered index scan, or vice versa, etc.), but above execution plans shapes are quite representative.

When FORCESEEK is used on both tables, no new alternatives appear. Moreover, apply alternatives become rejected after consideration due to seek requirement on both tables.

So, I think we can say, that possible implementations of the original written form of the query require FORCESEEK requirement to be relaxed for at least one of the tables.

You have another equivalent query, but discovering such alternative is not implemented in the current version of query optimizer unfortunately. This is not a bug though, just imperfection.

Notice also that you add FORCESEEK to convince optimizer to seek in the non-clustered index, but in the above cases optimizer understands it in its own way and performs seek over clustered index instead. Rewriting query, when its performance is not satisfactory, is one of the first (and right) things to try often.

(*) One may find it out by analyzing final memo structure and applied transformations (using undocumented trace flags 8615, 8619 and 8621).


Managing Sql Server 2012 Standard Edition I have some questions about Sql Server 2012 Standard Edition.

Does anyone know of 3rd party software to manage Sql Server 2012 Standard Edition with?
Is it advisable to use SSMS 2005/2008 Express to manage a SQL Server 2012?

The reason I ask is that we only have XP workstations and SSMS 2012 requires Vista/Windows 7 or above.

Thank you, Pieter

You could use remote desktop to connect to the server where 2012 is installed, then run the tools from there.


Does innodb_data_reads include reads from the buffer pool? We have set up some monitoring on many of our databases that serve many different applications.  It seems that they all report back more writes than reads when in reality that is very unlikely.  I know much of the code inside and out and we do far more reads than we do writes.  It has me wondering if a read is counted if it comes out of the cache (InnoDB buffer pool)?

Most of the stats seem to be based off of the innodb_data_reads system variable.  Our buffer pools are large enough to contain all of our InnoDB tables.

One server I checked says it is like 10/1 writes to reads.  However, when I view these two status variables:

Innodb_buffer_pool_read_requests: 1458637157995

Innodb_buffer_pool_reads: 516322

Innodb_data_reads: 643489

Innodb_data_writes: 17209580

Something doesn't work here..

Thanks for any help you can give!

Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests is very small, so the caching for reads is very effective.  The former is actual I/O; the latter includes cached reads.

Innodb_pages_read + Innodb_pages_written may be the number of 16KB blocks read + written.  In looking around on various machines, it seems that either number can be larger.

Indexes are included.  Non-leaf-node pages of BTrees are included.

I don't know what Innodb_data_* are.

Although many operations are "read-modify-write", the "read" is often cached, but the "write" needs to be flushed for persistence and syncing.

Bug #65030: "The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be. Its value should never exceed the value of Innodb_pages_written." (fixed in 5.5.25)

Do you want to monitor physical reads/writes?  Or logical reads/writes?


How do I import the _id index from a MongoDB collection as a hash index for a PostgreSQL table (from a CSV export from MongoDB)? I want to be able to keep track of the _id index values that get the MongoDB collection uses when I export the MongoDB collection (via CSV) and import the data into a PostgreSQL DB table (basically for archiving older data not frequently reported on from a MongoDB collection to a PostgreSQL DB table).
That said, if I ever wanted to add the records back into MongoDB for any reason, I want to ensure that the _id MongoDB collection values stay aligned with other collections in MongoDB and tables in PostgreSQL.
MongoDB uses a BSON object 24 character hash for its index (_id) and I want to be able to archive the data from MongoDBto a PostgreSQL DB using that _id value. It looks like it's possible to set a PostgreSQL table column datatype to character(24) and set it to the primary key as a hash using CREATE INDEX idx_hash ON 'pg_table_name_goes_here' USING HASH (_id); rather than requiring it to be a BSON like I originally though I would have to do, but will that work in a typical PostgreSQL table JOIN? and is there a better way to do this?
To put it another way, I'm taking a fair amount of data (about 5 to 8+ Million records a month) that is originating from a MongoDB and trying to archive previous/older data into PostgreSQL. So I want to keep that archived data in PostgreSQL exactly (or as close as possible) the same, in the event I might want to move the archived PostgreSQL back into some kind of NoSQL datastore in the future. In the meantime, I want to make querying the data in PostgreSQL as fast as possible. I want identical index columns / values.
Is this possible (using PostgreSQL v12 and MongoDB v4.4)?
I've been going down some pretty deep rabbit holes and finding a lot of weird and bizarre things about PostgreSQL.  I've traditionally used the MySQL family of Relational DBs and PostgreSQL has some great features but it's been a little more awkward to get the hang of than I expected.  I stumbled across this video series, and it makes sense: idx and seems to fit my use case as well as line up with your above comment. FWIW I did find this: pgbson but the complexity for deployments doesn't seem worth it.

MongoDB uses a BSON object 24 character hash for its index (_id)

Values are stored in columns in a relational database, not "in an index". To store such a character (aka "string") value, create a column of type varchar(24) to hold the _id value from MongoDB.
If you want to ensure uniqueness, then create a unique B-Tree index on the column or defined it as the primary key.
Something along the lines:
create table mongo_data
(
  id varchar(24) primary key,
  ... other columns ...
);

The fact that the value is computed by a "hash algorithm" in MongoDB has nothing to do with a "hash index" in Postgres.


Why doesn't DELETE + REORG free diskspace (DB2)? In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?

The table resides in its own tablespace which I created as follows:

CREATE BUFFERPOOL "MY_BP" SIZE 250 AUTOMATIC PAGESIZE 4096;
CREATE LARGE TABLESPACE MY_TBS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL MY_BP OVERHEAD 10.500000 TRANSFERRATE 0.140000 FILE SYSTEM CACHING;

I deleted/reorged as follows:

DELETE FROM MY_TBL
RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL
REORG TABLE MY_TBL
RUNSTATS ON TABLE MY_TABLE WITH DISTRIBUTION AND DETAILED INDEXES ALL
ALTER TABLESPACE MY_TBS REDUCE

The table MY_TBL took up 2.5GB before all that and after deleting/reorging it uses only 3 MB less.

FWIW: I'm running DB2/NT v9.5.2.

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.)

The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water Mark for the tablespace.

Do a the following

db2 list tablespaces show detail

This will show you each tablespace and what it is using on disk. Used pages is how many pages of disk the database is using. Comparing that against total pages (the total claimed on disk) and the High water mark (pages) will show you if you are "claiming" more than you actually need. (ie, low used pages, very high total pages and a High Water Mark close to the total pages).

To get rid of this unused space and return it to the operating system you would issue the following (under automatic storage): db2 alter tablespace <tablespace name> reduce max.
example

db2 alter tablespace ts1 reduce max;

That will cause DB2 to lower the high water mark and release the unused disk back to the operating system. (Note you can only do this for regular and large tablespaces, not for system temporary, or user temporary tablespaces).

If you are using DMS without automatic storage you need to use a slightly different set of commands:

db2 alter tablespace <tablespace name> lower high water mark;
db2 alter tablespace reduce (<containter name> or [all containers] integer K|M|G or integer PERCENT);

example

db2 alter tablespace ts1 lower high water mark;
db2 alter tablespace reduce (all containers 500 M);

Where we work, we put this into some of our maintenance scripts so that we automatically run this after we do reorgs to make sure we reclaim disk space. In our case we use DB2 LUW 9.7 FP 4, so it doesn't hurt to double check Information Center for 9.5 to make sure you have access to the right information for your version.

  EDIT: If your tablespaces came from a database upgraded to DB2 9.7, you probably will not have the reclaimable storage attribute set. This is true even if you upgrade from DMS to automatic storage. Either
  way bites as you cannot actually lower the high water mark. You have
  to dump the table and data out, drop the tablespaces. Then re-create
  the tablespace using automatic storage and import the data for your
  tables.


How to get a list of locked accounts / check that account is locked? I can use following statement to unlock an account:

ALTER USER username ACCOUNT UNLOCK

But which statement can I use to verify that account is currently locked out?

As Dba's answer already shows, account status information is accessible via the dba_users view. Connected with a user having the appropriate grants, this can also be used to identify "inactive users":

SELECT username, account_status, created, lock_date, expiry_date
  FROM dba_users
 WHERE account_status != 'OPEN';

tells you for each locked/expired account when it was created, which state it is in (locked, expired, expired and locked, expired(grace)), and what time it was locked or expired. Useful for a "cleanup"; but you should consider that some accounts might just be "data holders" which are never connected to for security reasons, but required either by Oracle itself or your application(s). A good example for those is the system account OUTLN. So take care to only "clean out" accounts you know are not used/needed :)


Postgresql - Multiple counts within a join I am modeling tokens extracted from tweets and have the below table structure:

CREATE TABLE process (
        id SERIAL PRIMARY KEY,
        ...
);

CREATE TABLE tweet (
        id SERIAL PRIMARY KEY,
        process_id INTEGER NOT NULL REFERENCES process(id) ON DELETE CASCADE ON UPDATE CASCADE,
        cleaned_text TEXT,
        process_tweet_id INTEGER NOT NULL,
        constraint tbl_statusid_processid unique(status_id, process_id)
        ...
);

CREATE TABLE token (
    id SERIAL PRIMARY KEY,
    tweet_id INTEGER NOT NULL REFERENCES tweet(id) ON DELETE CASCADE ON UPDATE CASCADE,
    type TEXT,
    value TEXT,
    ...
);

The token table has a foreign key for the tweet table and the tweet table has a foreign key for the process table.

The 'type' field in the token table can be 1 of 7 or 8 possible values (e.g. 'LOCATION', 'PERSON', etc.).  The possible types are static and enforced elsewhere within the application, so there is no chance of any diversion from those set types.

I am trying to create a query that will return all of the tweets referencing a given process_id, the total number of tokens referencing each tweet as well as the number of each TYPE of token referencing each tweet (each type having its own column in the result set).

  id   |     status_id      | tokencount | loctoken | persontoken
-------+--------------------+------------+----------+-------------
  6158 | 672636476602122241 |          3 |        2 |           1
 10295 | 674183390430552064 |          4 |        2 |           2

The above result set is basically what I am hoping to get back.  So, tweet ID# 6158 has a total of 3 tokens, 2 of which are location tokens and 1 is a person token.  Tweet ID# 10295 has a total of 4 tokens, 2 locations and 2 persons.

What I have so far is a simple JOIN, but I am unsure how to include the count for each type of token per tweet...

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;

I thought I could simply add count(token.type = 'LOCATION') like the below, but that simply repeated the count(token.id) column.

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount, count(token.type = 'LOCATION) AS locToken, count(token.type = 'PERSON') AS personToken
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;

Can anyone provide the query that would return what I am looking for?  Hopefully with a good explanation of the solution?

I would also really like to know why count(token.type = 'LOCATION') did not work and simply returned the same number as count(token.id).  I tried it on a whim and really thought I was just going to get an error.

You were nearly there, you can do this with case when ... end

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount,
sum(case when token.type = 'LOCATION' then 1 else 0 end) AS locToken,
sum(case when token.type = 'PERSON' then 1 else 0 end) AS personToken
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;


Help to understand 'log space used' metric The chart below comes from a monitoring tool. The metric shown is "log space used" for SQL Server 2016 for the last 7 days.

I'm looking for help to understand what's going on here.

What I do know is that the IT department is using a 3rd party tool (Veeam) to "back up" regularly. I'm advised full backups are performed nightly and log backups are performed every 15 minutes (but I am not sure if these are disk image backups of the database files or commands issued to SQLServer by Veeam to perform a backup). One of the steps includes "Process logs" for SQL Server and I believe one of the steps there is to truncate the logs.

I understand truncating the log means to mark as inactive those portions of the log file that may be re-used. At the moment, I understand (rightly or wrongly) that a log file portion may be re-used if SQL Server knows it has been written to disk - ie via backup.

What I see in this chart is

An abnormally high usage of the log file for one DB at far left - ignore that.
Daily growth and then daily clearing of each log file (3 humps).
Growth over 3 days (Fri, Sat, Sun), then truncation.

My interpretation here is that log backups must be occurring, otherwise the usage would not drop back near to zero.

I suspect the log backups are occurring only once per day - although I don't think this chart proves it. For example, there is the possibility that there are more frequent log backups during business hours, but truncation does not occur at that time due to a long-running transaction. However at the nightly backup by Veeam, the truncation command is issued and succeeds. Despite this possibility, I still think it more likely that log backups are being carried out only once per day by Veeam.

Part of the issue here is that Veeam is a bit of a black box to me - I am not sure how IT has this configured. I am also not clear on whether it is taking snapshot images of the hard disks (ie database files), or instructing SQLServer to execute a backup.

My goal is to ensure we are taking frequent log file backups (and to work out a good frequency) in order to support a full recovery model across essentially all of these databases. If I determine that currently we are taking log backups no more frequently than nightly, then I also need to ensure that if I put measures in place for frequent log backups, that I'm not competing with Veeam such that Veeam and any scheduled task within SQLServer are not breaking log chains for each other - any suggestions on how to check and plan these things would be most appreciated.

You can check your backup history by using a standard report:

... by using some scripts:
Script to retrieve SQL Server database backup history and no backups

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

... or having a look to your server's ERRORLOG file and searching for Source = Backup


SQL Server Backup Encryption on AAG We are using SQL Server 2016 Enterprise Ed with AlwaysOn Availability Group.  If we want to encrypt our database backups, how do we go about creating the encyrption keys?  For example, do I create the same Certificate used on both primary and secondary?  I am thinking it would be the same cert since the backups could be happening on any of the two replicas.  Anyone have any guidance on this?

I am new to encryption so this is all new to me.  Thanks for any help!

Yes, use the same cert on each instance. Your restore process would be clumsy if you had different certs on different instances.

I would suggest you read the documentation (including the relevant links) as encryption and key hierarchy in SQL Server is a topic too large for a single answer on a Q&A site, but essentially you need to:

Create a database master key
Create a certificate
Backup your certificate and keys
Import the certificate to each node of the AG and any other instance you plan on restoring to
Use the ENCRYPTION option in your backup statements


How to choose a column of data from 2 days ago? Currently, I am running the following SQL (in MySQL) query to return the column for records recorded at 12am on the current day, up to the current time.  So, if today is 2/3, and I run the query at 8pm, I will return all data recorded from 12:01am up to 8pm.

My question is; how do I retrieve yesterday's data?  I want the same column, except instead of today's date, I want yesterday.

SELECT column1
FROM db.table
WHERE TS >= CURDATE() AND TS < CURDATE() + INTERVAL 1 DAY;

My second question is, how would I take the same column, and find the records for the entire month, up to the CURDATE()?

If the current WHERE condition specifies today and you want yesterday, then just subtract one day from both expressions. That will make the first expression CURDATE() - INTERVAL 1 DAY and the second simply CURDATE():

SELECT column1
FROM db.table
WHERE TS >= CURDATE() - INTERVAL 1 DAY AND TS < CURDATE();


Postgres ClientWrite Event I have a Postgres 10.9 table that has ~200 million rows. This table has an index on one of the fields my_date which is obviously a Date. I'm executing the following query: select my_date from my_table.

The explain call yields the following:

Index Only Scan using client_purchaseorder_facility_alias_id_po_date_idx
on client_purchaseorder  (cost=0.57..5981683.41 rows=196100256 width=4)

An extraordinary amount of time is spend (60%) in the ClientWrite event which I see whenever I perform the following query:

select pid, wait_event_type, wait_event
from pg_stat_activity
where wait_event is NOT NULL;

30% is in the DataFileRead.

Postgres documentation describes ClientWrite as "Waiting to write data to the client." Is there a way to tell why it is waiting? I'm performing the query from PGAdmin on a machine where not much else is going on. Total time to complete is at least 5 to 10 minutes. I feel that this should be going much faster.

I checked to see when the table was last vacuumed and it says two days ago.

pgAdmin is known to be slow and inefficient with large result sets, so this is not surprising. It is not built for mass data display.

If you want to visualize a sample, add a LIMIT clause to the query.

If you want to export the data, use the COPY statement.


Insert all dates from a month into a table I'm confused. How to get all dates from a month and insert them into a table in a database?

I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table attendance it will generate one row for each date.

I have table period

period | start_period | end_period
April  |   2016-04-01 | 2016-04-30

and I have table attendance

attendance_date  | check_in | check out
2016-04-01       |     null |      null
..........
2016-04-30       |     null |      null

How to get all dates between 01-04-2016 and 30-04-2016 and insert them into table attendance?

You can use variables to mimic a recursive CTE:

SELECT @d:=(@d + interval 1 day)
FROM (SELECT @d:=date('2016-03-31')) r
CROSS JOIN information_schema.tables t
WHERE @d < '2016-04-30';

You need to make sure that you have a table to join against that have a sufficient number of rows for your need.

If you do this on a regular basis you might want to generate a calendar table that you can use to join against.

I heard rumors that recursive CTE:s as well as window functions will be available in a nearby future for MySQL. I have not investigated it very closely though, so it might just be rumours.


Why is a .bak so much smaller than the database it's a backup of? I just took a backup of a SQL Server database.  The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller.

My first guess when one version of a set of data is smaller than another version containing the same data would be data compression, but compression usually yields a much better compression ratio than 20%, especially for highly-ordered data (such as database tables.) Also, compressed data can't easily be compressed further, but I know that .bak files can be compressed.

So if the data isn't being compressed, and nothing's being discarded, (because the whole point of making a backup is to be able to restore it to an identical state afterwards,) then what's that 20% that's unaccounted for?

The space was allocated to the database files, but not used.

You can create a new database, make it 10GB in size, and see the files allocate that amount of space on disk. However, until you put data in the database, the file is essentially empty, and your backup file size will be minimal.

HTH


Error using sp_send_dbmail with a @query parameter This has been asked many times, and every time I've seen, the solution is to add the @execute_query_database parameter. Well, as you can see, I have that parameter and this code:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'GMail',
@recipients = 'brady@bradykelly.net',
@subject = 'Snappy Referrals', -- add date
@body = 'Hello, DB mail!',
@execute_query_database = 'SeniorExecs',
@query = N'SELECT * from Language'

Still gives the error below as long as the @query parameter is present. When I comment out that one line, the proc call works and the mail is sent.

Failed to initialize sqlcmd library with error number -2147467259.

When I examine the DB Mail logs, in the Sql Server section, everytime I try and execute the proc, an entry like this gets added:

  Unknown,Disallowing page allocations for database 'WideWorldImporters'
  due to insufficient memory in the resource pool 'default'.

At this point, the only thing to say about database WideWorldImporters is that it is on the same SQL server. I encountered it nowhere setting up a mail profile and user account.

Going an entirely different direction than what I was thinking of in the comments, if you're running this from within a job, this blog post suggests adding the SQL Agent account to the sysadmin fixed server role.

I doubt you have to go that far to fix this, and I suspect you only really need to grant the SQL Agent user (or whatever user owns the job in question) a minimum set of permissions, which are execute permissions on the sp_send_dbmail procedure, ensuring it's a member of the DatabaseMailUser database role in the msdb database, and finally, making sure it can access the data being returned by the query you're passing to the sp_send_dbmail routine itself.


MySQL Shell dump utility - maximum possible chunk size? The documentation https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html mentions that the min chunk size is 128kB. What is the maximum possible chunk size?

The max value is innodb_buffer_pool_size / innodb_buffer_pool_instances or some integral fraction of that.
But why bother changing the setting?  It should not make much difference.
Reference, and more discussion:  https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size


How to recover from Backup Pending SQLSTATE=57019 in DB2? I updated the DB2 parameters in LOGARCHMETH1 to OFF.  It worked fine after bouncing the database.

When I tried to turn on again the LOGARCHMETH1 and specify a DISK for the log archive of the database, used the command:

update db cfg for "DES" using LOGARCHMETH1 DISK:G:\db2\DES\log_archive\

It prompts that the parameters submitted for immediate modification were not changed dynamically. Database must be shutdown and reactivated before the config parameter changes become effective. So I restart/bounce the database with:

db2stop
db2start

Tried to check the database config again if the changes was made, but now
I cannot anymore connect to the database and I was being prompt with error:

  SQL1116N A connection to or activation of database "DES" cannot be
  made because of BACKUP PENDING. SQLSTATE=57019

  SQL1116N A connection to or activation of database "DES" cannot be
  made because of BACKUP PENDING.

Any suggestions on how to activate back the database? Since now I cannot turn on the application for this one.

Backup pending means you need to take an offline backup. Since you went from circular to archive logging DB2 needs a point in time from where it can apply the logs.


Maximum number of fields/columns in MongoDB I'm considering learning and using MongoDB for a project, but I'm going to need to be able to store at least 1024 columns for the first part, and as many as 1048577 for the second. Does MongoDB have a limit on the number of columns?

This depends of name space or the storage for each name of each field, MongoDB by default only available 16mb for each documents, including name space (field in documents, database name, collection name)

Read this: limits namespaces


How can I measure the number of IOPs for a certain query I need to estimate the max IOPs for a certain MySQL DB (one that does not exist yet).

The OS is Linux and the DB is MySQL 5.1.x (though I don't think the DB brand is important for the asked topic)

The questioned table is extremely simple. user table with some attributes. and the dominant query is:

SELECT * FROM users WHERE username='';

I think about populating a table with huge amount of records and use iostat to calculate the IOPs average per query. Later I can use the same method to test SELECT.

Is this a reasonable test for measuring IOPs?
Are there other better methods to accomplish this?

Thanks

This is trickier than it looks. I/O per second is a difficult thing to pin down. Just because the file system sees so many I/Os per second, doesn't mean that MySQL sees the same thing - there is such a thing as I/O buffering. I would look into MySQL 5.6 - in particular the performance schema (P_S)(Marc Alff wrote the P_S (*)). One SQL SELECT could read from multiple tables. One transaction could read and write to many tables. Are you using MyISAM? If you are using InnoDB, are you using one file per table (advisable IMHO) - otherwise iostat will just give a database wide figure. I would suggest that you read around this topic in some detail and be clear about what, exactly, it is you're measuring.

(*) this comment obviously depends on whether you have the choice to upgrade, but the server version is always important on this forum - the more those who are trying to help know about your system, the more help you can receive.


T-SQL to Exclude the primary from List of AOAG Replicas I am working on a task for which I need to exclude primary from the list .Can someone please help me to fix it.

select primary_replica  from sys.dm_hadr_availability_group_states

The above command gives Primary replica of AOAG.

select replica_server_name from sys.dm_hadr_availability_replica_cluster_states

The above command gives list of replicas of complete AOAG.

Now,I want to exclude primary_replica from the replica_server_name and I Want to see only secondary replicas.

So far I tried this but no luck.

select replica_server_name AS SecondaryReplicas from sys.dm_hadr_availability_replica_cluster_states INNER JOIN
sys.dm_hadr_availability_group_states ON sys.dm_hadr_availability_group_states.group_id= sys.dm_hadr_availability_group_states.group_id

Thanks in advance

You could adapt the query from this link (which is looking for PRIMARY replicas) to look for secondary replicas.  Example below:

IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
    SELECT AGC.NAME -- Availability Group
        ,RCS.replica_server_name -- SQL cluster node name
        ,ARS.role_desc -- Replica Role
        ,AGL.dns_name -- Listener Name
    FROM sys.availability_groups_cluster AS AGC
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
    INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
    WHERE ARS.role_desc = 'SECONDARY'
END


How do I save functions to individual files in PostgreSQL? I maintain a legacy application that uses a PostgreSQL database.

The application is heavily dependent on stored procedures (aka functions).

I want to save these functions to files named after the function name so I can then use a VCS (version control system).

I know that I can save the code with the ALTER FUNCTION using PgAdmin but this only allows me to save one function at a time.

I am looking for a way to save all the functions automatically. Is there any way to script this task?

I wrote a simple PHP script using pg_get_functiondef to get the source code and then save it to a file.

$connection = pg_connect("connection string details");
$result = pg_query($connection, "select proname, pg_get_functiondef(oid) from pg_proc where pronamespace=<namespace_oid>");

while ($row = pg_fetch_row($result)) {
    $fp = fopen("{$row[0]}.sql", 'w');
    fwrite($fp, $row[1]);
}


Multi-Column PK or Auto-Increment PK? In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those columns.

The table will ultimately have as many as hundreds of millions of rows, so it was pointed out that an auto-increment PK might be better and my multi-column PK would be less performant. Inserts can be several per second.

E.g. please ignore questions about columns, this is just an example.

CREATE TABLE records(
  record_code VARCHAR(20) NOT NULL,
  record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user VARCHAR(30) NOT NULL,
  other_data VARCHAR(100),
  other_data VARCHAR(100),
  PRIMARY KEY (record_code, record_date, user)
) ENGINE=InnoDB;

Or:

CREATE TABLE records(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  record_code VARCHAR(20) NOT NULL,
  record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user VARCHAR(30) NOT NULL,
  other_data VARCHAR(100),
  other_data VARCHAR(100),
  PRIMARY KEY (id),
  KEY record_code (record_code)
) ENGINE=InnoDB;

It made sense to me that an AUTO_INCREMENT surrogate PK would save disk/memory space and allow faster inserts, but another commenter disagreed, so I am wondering if it's OK to use the multi-column PK, is it just as fast? Is there not much difference?

NOTE it is possible at some point that may need to start doing lookups by both the first AND second columns, so in the second example above the second index would be KEY record_code_date (record_code, record_date) so inserts are doing almost as much work as with just using the multi-column PK(?). Does this change the advisable approach?

It depends.

One must weigh the alternatives.  Here is a partial list:

Rows are INSERTed in an ordered list (the BTree that contains the data, and is ordered by the PK) according to the PK.  For a huge table and random PK (eg UUID), this means jumping around a lot, which leads to poor caching.
New rows with an AUTO_INCREMENT PK are inserted at the "end" of the table, thereby avoiding the above point.
Every secondary key has a copy of the PK.  So, if the PK is bulky, you are taking more space for the secondary keys.
Ditto for other tables that need to JOIN to this table (with or without FOREIGN KEYs).
A PK is necessarily UNIQUE.  Hence, DATETIME and TIMESTAMP make risky fields to put into a PK.  (What if two things happened in the same second?)
Will you be fetching records based on the column(s) of a multi-column key that could be the PK?  If so, you get to them faster because you don't have to go through a secondary key faster.
Consecutive records (according to all columns of the PK) are "clustered" next to each other.  This means that fetching multiple rows will need fewer blocks from disk.
Etc, etc.


Is it possible to list all user that accessed to specific table Is there a way in Oracle (11) to make a list of users when they were last accessed (select / update / insert or delete) a table or view ?

Thanks

You can do this with auditing, but it incurs a performance overhead and does not work retrospectively.

Read the section Verifying Security Access with Auditing in the Oracle® Database Security Guide 11g Release 2 (11.2).

I'll add that in the majority of cases, somebody in the organisation thinks that this type of auditing is required, but more often than not it is just a misguided requirement.


array_length if array is empty behaviour Please explain to me this behavior.

This SQL finds elements where ids (type is array::bigint) is empty.

SELECT * FROM rises WHERE ids = '{}'
-- finds, e.g., 9 rows

This SQL finds no rows:

SELECT * FROM rises WHERE array_length(ids, 1) = 0
--finds always 0 rows

But this SQL can find non-empty arrays

SELECT * FROM rises WHERE array_length(ids, 1) > 0
--finds, e.g., 15 rows

Initialization:

CREATE TABLE rises(
    id bigserial,
    d1 bigint DEFAULT 0,
    ids bigint[] DEFAULT '{}',
    PRIMARY KEY (id));

Why array_length can find non-empty arrays but it doesn't work for empty arrays?

It looks like PostgreSQL returns NULL for empty arrays. Try:

SELECT
    array_length(ARRAY[]::bigint[], 1),
    array_length(ARRAY[]::bigint[], 1) IS NULL,
    array_lower(ARRAY[]::bigint[], 1),
    array_upper(ARRAY[]::bigint[], 1)

You get:
null|true|null|null

Looks weird, but that's just the way it is. The workaround is to use COALESCE:

SELECT
    COALESCE(array_length(ARRAY[]::bigint[], 1), 0)

returns 0.

Tried this on PostgreSQL 9.4.


SQL server backup message Could you please give little clarification about SQL server backup message

For example :

  BACKUP DATABASE successfully processed 19696388 pages in 1945.648
  seconds (79.088 MB/sec).

For example we have disk with database and we have separate disk for backup , does it mean that 79 MB/sec is that one disk reads data for backup with speed of 40 mb/sec and another disk writes data with speed 40 mb and as sum it gives us speed of 79 mb/sec , is it correct ?

Lets first get the concept of reader / writer threads out of the way

  During a backup, SQL Server creates one reader thread for each volume
  that the database files reside on.  The reader thread simply reads the
  contents of the files.  Each time that it reads a portion of the file,
  it stores them in a buffer.  There are multiple buffers in use, so the
  reader thread will keep on reading as long as there are free buffers
  to write to.  SQL Server also creates one writer thread for each
  backup device to write the contents of the buffers out to disk or
  tape.  The writer thread writes the data from the buffer to the disk
  or tape.  Once the data has been written, the buffer can be reused by
  the reader thread.

Source

Answer

So yes, while the reader threads are reading the pages and the writer threads are writing the pages to the separate disk, the time to process the pages will be impacted by both.

But, this does not mean that the79.088 MB/sec will be divisible by 2.

It means that the read operation was running at ~79.088MB/sec and the write operation was also running at ~79.088MB/sec. The speed is determined by the slowest of these two operations since both operations are reading and writing from multiple buffers (as long as BUFFERCOUNT is set correctly / not added to the command).

As a result, these buffers filling up too slow / not being cleared fast enough is the bottlenecking in action.

Another way to validate this is by calculating the MB/sec * backup duration. This will match your database size minus the space available.

Testing how fast you can read

If you want to know the amount of data & how fast it can be read from disk, you can take a backup to 'NUL'

BACKUP DATABASE DBName TO DISK = 'NUL' WITH COPY_ONLY;

In my case I ended up with 122.406 MB/sec

Testing how fast you can write

To know how fast you can write, you could use Crystaldiskmark.

Use Seq Q32T1 to mimic the backup operation writes.

Source

This is what pops up on my slow target drive, where I want to back up to.

Putting two and two together

If I then run a backup command of my database that resides on the D  (data) &  L  (log) drive to the disk I previously checked with CrystalDiskMark (E).

BACKUP DATABASE DBName TO DISK = 'E:\Folder\DbName.BAK';

Processed 703088 pages for database 'DbName', file 'Database' on file 1.
Processed 2 pages for database 'DbName', file 'Database' on file 1.
BACKUP DATABASE successfully processed 703090 pages in 50.198 seconds (109.424 MB/sec).

We see that the read part of the backup operation is the bottleneck on my system.


Pairwise intersections I have a table with two columns, let's say FirstName and LastName.
I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's.

Is this even feasible to do in SQL?

There are much more unique LastName's than FirstName's, if this affects efficiency of the query.

A toy example, input:

FirstName, LastName
John, Smith
John, Doe
Jane, Doe

Output:

FirstName1, FirstName2, CommonLastNames
John, John, 2
John, Jane, 1
Jane, Jane, 1
Jane, John, 1

As this relation is reflexive and symmetrical, it's OK if the result is just one of the triangles (e.g, the one above the diagonal).

I'm going to use MS SQL Server to do this since I have a copy to hand.  I believe most any of the majors would do it similarly.

First a sample table, with data.  I use a table variable but it's the same for any flavour of table.

declare @t table (FirstName char(10), LastName char(10));

insert @t(FirstName,LastName)
values ('John','Smith'),('John','Doe'),('Jane','Doe');

You can get all pairs by doing a self-join:

select
    a.FirstName, a.LastName, b.FirstName, b.LastName
from @t as a
cross apply @t as b;

Using CROSS APPLY avoids having to jump through hoops to find a join condition for an ON clause.

Next you need something to count.  This is where the CASE statement comes in.  The case returns an integer value per pair of first names, which is what gets counted. (If I'm reading your question correctly you want where the LastNames match so that's the comparison I have.  Hopefully it's obvious how to modify this if I'm wrong.)

select
    ...
    case
        when a.LastName = b.LastName then 1
        else 0
    end
...etc.

Add in a SUM() and GROUP BY and you get your answer:

select
    a.FirstName,
    b.FirstName,
    sum(
    case
        when a.LastName = b.LastName then 1
        else 0
    end
    ) as CommonLastNames
from @t as a
cross apply @t as b
group by a.FirstName, b.FirstName;


Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain.

I've created a domain account for my sql server agent user on each server including the master. I was able to create the Master and set Targets to two other servers. The targets are added via the registered servers (FQDN) on the CMS which is also the master.

When i go to the master, right click > Multi-Server Administration > Manage Target Servers, i see the servers are listed using the short sever name not FQDN. When I click Force Poll, I get an error message:

This operation requires SQL3 server to be a registered server. (SqlManagerUI)

In order to work around this, i have to setup a LOCAL server registration and use the short name SQL3 instead of a FQDN.

This seems rather inconvenient to say the least - why do i have to register each server locally to use this feature? Why is it relying on the short name and not FQDN. These servers are already registered in my CMS which is also the master server. I could see this being a big problem when managing many servers - would i have to locally register a 100 servers??

I know it's been a while, but I have the same issue trying to manage multiple domains. (Avoid using the same server name in more than one domain.)  The Multi-server feature seems to be very limited, but it could be so much better with a little effort.  I figure it will be removed from the product one of these years, fixed, and then added back as a great "new" feature in the ENT edition.

I think the limitation is in the extended procs such as xp_msx_enlist code.  They determine the name of the local instance.  If it was T-SQL code, it might have been easier to fix.  As far as I can tell, the only place the target name is stored is in msdb.dbo.systargetservers table on the master server.  If the targets could be tricked into requesting a sync for the FSDN, then the table could have be updated (hacked) directly to use the FQDN.

If it was open source, it would have been fixed years ago.  It would have had more features by now too.


Database Schema design of server running status log I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running PUMA server and another one is running NGINX and so on. I want to store the running status of the instances and their application servers in a database.

Like, If Instance A is running or not, that I can get by continuously hitting into the exposed port and store it in the database. I am scanning it in each minute. There will be 3 columns I can think of right now,

Instance A :-

Instance ID    Running Status   Time

Instance A        Running        10:10:04
Instance A        Running        10:11:04
Instance A        Running        10:12:04

Next, I want to store the status if Application Servers within Instance A running or not. There will be 3 columns I can think of right now,

Let's say PUMA and NGINX are running in Instance A

Instance A :- (Here also I am scanning it in each minute)

Application Name   Running Status   Time

PUMA                  Running       12:10:20
NGINX                 Running       12:10:20
PUMA                  Running       12:11:20
NGINX                 Running       12:11:20

I am using Postgresql. What will be the recommended schema design here ? Should I create a table for each instance and how will I map the applications with that particular Instance ? If I take Instance ID as a primary key then duplicate values in that column are not possible.

Time without date and timezone is pretty useless.

CREATE TABLE logz (
  server_inet inet,
  server_name text,
  app_name    text,
  is_running  bool,
  ts          timestamp with time zone
);

CREATE INDEX ON logz(server_name,ts);
CREATE INDEX ON logz(server_inet,ts);

Then to find the status of the server for each app.

CREATE VIEW running_status_by_name
AS
  SELECT DISTINCT ON (server_name,app_name) server_name, server_inet, app_name, is_running, ts
  FROM logz
  ORDER BY server_name, app_name, ts DESC;

CREATE VIEW running_status_by_inet
AS
  SELECT DISTINCT ON (server_inet,app_name) server_inet, server_name, app_name, is_running, ts
  FROM logz
  ORDER BY server_inet, app_name, ts DESC;

You could also use a pkid and easily do a CROSS JOIN LATERAL (OFFSET 0 LIMIT 1).


Scrambling PII data in CLOB columns I am working on scrambling PII tags in CLOB columns such as <FirstName> or <LastName> or <SSN_Number>.

so I am using the below query to achieve the same.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();

The above query runs for really a long time(more than 6 hours) and at last fails with below error.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();
               xmltype (SUED_DATA),
               *
ERROR at line 4:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 3 (U+0003)
Error at line 181
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

I am not aware of what could be wrong with it. Is there anyway to optimize the same? What is that I am missing?

Looks like there's an error in your XML file (the character U+0003 is "ETX" - end of text, which is nonprinting and invalid in XML).  Run the XML file through a validator.

Also (and you probably know this), your atbash "scramble" is not particular secure.


Database Mail on Amazon RDS / SQL Server Since Database Mail is not supported on Amazon RDS with SQL Server, can anyone offer any suggestions on how to send e-mail notifications for failed SQL Server jobs in Amazon RDS?

Thanks in advance for any suggestions or workarounds.

AWS has posted a detailed blog post based on the way that UnderArmor solves this limitation. It involves a linked SQL Server that you run in EC2 and that EC2 instance is configured to SES to relay the messages through.

AWS RDS Database Mail


Set TRUSTWORTHY On smacks down service broker from working I've got a database running on a SQL-Server 2012 DBMS. Consider that the server running SQL Server is not part of the network domain.
When I execute this sql command

ALTER DATABASE myDatabase SET TRUSTWORTHY ON;

a service broker queue (early setted and working) receives messages but the activeted stored procedure associated to the queue doesn't start. Messages continue to flow in to the queue, but nothing else happens.
In the SQL SERVER log I've found this message

  The activated proc 'proofSchema.mySP' running on queue 'proofSCHEMA.myQueue' output the following:  'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'myDatabase'. You should correct this situation by resetting the owner of database 'myDatabase' using the ALTER AUTHORIZATION statement.'

If I run this same configuration on a machine inside a network domain, everything works.

I can't understand what is happening and why trustworthy crush with servi broker queue.

Within each database there exists a dbo User. This User (Database-level) always exists, but the SID (Security IDentifier) that it maps to is not always the same; it will map to whatever Login (Instance-level) is specified either when creating the Database of when altered to have a new "Database Owner". The dbo User is one of the entries in sys.database_principals.

When initially setting the Database Owner during CREATE DATABASE, or when changing it later, the SID of the "owner" is not only placed into sys.database_principals, but is also recorded in master.sys.databases. If the Database never leaves the Instance in which it was created, there should never be a mismatch between the SID values in sys.database_principals and master.sys.databases. But, if the Database is ever restored or attached to (or from) another Instance, then it is possible that the SID values won't match. You can check the values in both places using the following queries:

USE [tempdb]; -- Change to whatever DB you want to check

SELECT      msd.owner_sid,
            msp.[name]
FROM        [master].[sys].[databases] msd
INNER JOIN  [master].[sys].[server_principals] msp
        ON  msp.[sid] = msd.[owner_sid]
WHERE       msd.[database_id] = DB_ID();

SELECT sdp.[sid]
FROM   [sys].[database_principals] sdp
WHERE  sdp.[name] = N'dbo';

Now, by default, TRUSTWORTHY is set to OFF and permissions on operations involving Impersonation (i.e. EXECUTE AS) are confined to the database where the operation was executed from. While impersonating a DB User, attempting to access another DB (or even Server/Instance-level resources, I believe), SQL Server will assume that the current DB User's SID (i.e. the one being impersonated) has a matching Login so it can take on those permissions. This is blocked when TRUSTWORTHY is OFF, but setting it to ON lifts the Database-level quarantine and allows Impersonation to extend beyond the initial Database. The SID of the dbo User might certainly exist as a Login, but if it is not the same SID that is mapped as the database owner in sys.databases, then that is a clear indication that something is not right (and most likely that that DB came from another Instance), and that there might possibly be malicious intent in the requested operation.


Redshift table not showing up in tables for schema? On Redshift, why doesn't my table show up in the following query? It definitely exists, as shown by the next query I run. I want a way to list all tables for a schema:

mydb=# select distinct(tablename) from pg_table_def where schemaname = 'db';
 tablename
-----------
(0 rows)

mydb=# \d db.some_table
                    Table "db.some_table"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
...correct info shows up here...
...but nothing showed up above?

PG_TABLE_DEF in Redshift only returns information about tables that are visible to the user, in other words, it will only show you the tables which are in the schema(s) which are defined in variable search_path. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schema(s).

Try this -

mydb=# set search_path="$user",db;

Then run your query -

mydb=# select tablename from pg_table_def where schemaname = 'db';


Subselect vs join: Are both SQL queries equal? I have this db:

DROP TABLE auth_user_groups;
DROP TABLE auth_user;
DROP TABLE auth_group;

CREATE TABLE auth_user (
    id integer PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

SQL statement using join:

select username from auth_user, auth_user_groups, auth_group where
  auth_group.name='group1' and
  auth_user_groups.group_id=auth_group.id and
  auth_user.id=auth_user_groups.user_id;

SQL statement using subselect:

select username from auth_user where id in (
  select user_id from auth_user_groups where group_id in (
    select id from auth_group where name ='group1'));

Is the result of both SQL statements equal for all possible data sets?

I use PostgreSQL, but AFAIK this should not matter for this question.

Both query will return same possible data sets as if you use in as sub query, it behaves like inner join in SQL query and where clause in both SQL statements should be same as in your where condition.


Small databases, large backup files We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point.

My database was 900MB, then I tried clearing it by deleting old history which is not necessary anymore and after that ran VACUUM FULL. The statistics in pgAdmin say that the database is now only 30MB.

When I run the pg_dump command manually through a Command prompt, it creates a file of 22MB. When I run my daily task (windows task scheduler) it still creates a backup file of 1GB.

What am I missing at this point?

Check if your pg_dump does not add contents to the existing file instead of replacing it with a new one. That was my mistake.


Limiting the number of CPUs used in SQL Server 2012 Enterprise The platform under consideration is SQL Server 2012 Ent. SP3.

We are in the process of migrating a SQL Server environment to VM but highly feel the server was over spec'd to start.

We want the customer to feel good about going to a VM with less resources as they don't appear to be needed.

To start we want limit the number of CPUs that are currently being allocated.  Is setting the Processor affinity a safe approach? Any gotchas?

The server was a dedicated physical SQL Server with 32 cores moving to VMware. The thought was to step down the number of CPUs on the old server prior to going to VM to show the customer.  According to the VM team, having a server with that many CPUs is not necessary due to VM.   Also, the server is basically using less than 10% of the available CPUs.

The question is a little hard to understand.

  We are in the process of migrating a SQL Server environment to VM but
  highly feel the server was over spec'd to start.

  We want the customer to feel good about going to a VM with less
  resources as they don't appear to be needed.

  To start we want limit the number of CPUs that are currently being
  allocated. Is setting the Processor affinity a safe approach? Any
  gotchas?

Is the current server a physical and you want to limit the CPU resources it has access to in order to highlight to the customer that it will run fine on a VM with fewer CPU resources?

Assuming yes, processor affinity may work for this, however, it can cause SQL Server to adopt a 1:1 ratio of Schedulers on SQLOS to Cores on the server, which can cause poor utilisation of even the now reduced CPU resources. See this blog post for more information.

A better solution might be to use Resource Governor to limit the amount of CPU for the default pool to control the amount of CPU resources it can use without introducing the 1:1 affinity issue. See here for more information.

Basically, if you have, say, 24 cores at the moment and want to reduce to 12, then you could set the default pool MAX_CPU_PERCENT to 50. It is not an exact analog for reducing the available cores, but it will give you some confirmation that your workload will be fine with reduced CPU resources.

NOTE: If you enable trace flag 8002 and use processor affinity you can avoid the 1:1 scheduler to core issue. See here for more info. Enabling this trace flag requires a restart.

NOTE 2: If your current server is not physical, then just lower the vCPUs for that server to validate (if you're confident the workload can be sustained on fewer cores).


Maximum number of columns per index key in Ms SQL Server 2014 I have a large table with about 200 million records. I want to define a composite nonclustered key on it.
What is the maximum number of columns per index key in Ms SQL Server 2014?

There are two limitations which you would probably have to consider.

The maximum number of columns in an index key is 16, unless you have an XML column

  Columns per index key

  Note: If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Create Indexes with Included Columns.

The maximum number of bytes in an index key is 900.

  Bytes per index key

  Note: The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

Maximum Capacity Specifications for SQL Server (Microsoft)


Is it possible to set the prompt to a variable - MySql - MariaDB I would like to change the prompt to my mariadb or mysql client.

I would like the prompt to be the contain of a variable like report_host or more, to something that can change like default_master_connection.

Does someone know how to do it?

AFAIK MySQL prompt cannot be set to MySQL variables (Global or Session). The only options that can be used are documented at https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html (look towards the end of the documentation page for a full list).


Track changes on SQL Server I have a production server and a development server. The schema is equal on both. I need a tool that, if I do any schema change in the development server, it will create a sync script that I can run on production. The servers are not connected, and I can't get from one server to another.

I need a tool that if i do any schema change in the develop server it will create a sync script that i can run on the production.

I would suggest you to take a look at Redgate's schema compare. Its worth the investment.

Note: I have used Redgate's schema and data compare and they are life saver.

Alternatively, you can use SQL Server Data Tools (SSDT)

  SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.