Can I restore a database to a point of time without backup?

I had a database using the simple recovery model, that had never been backed up.

I deleted some things that I shouldn't have and came to this thread and after reading it my heart was in my throat thinking of #1 telling the client and #2 all the work I'd be putting in to replace the data.

I continued to have a look on google and found ApexSQL recover. Link

I detached the database, took a copy of the detached data and log files then reattached the original and then ran the program. I told it the database and pointed it at the copy of the detached log file. It found all the deletions and scripted the recovery.

All in all, I'm very happy with the product and would recommend it to anyone. There is a 14 day trial, but it only recovers 1 in 10 rows.


Can partitioning the table help reduce the completion time?

No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.

If you want to improve performance you need to identify the bottleneck and address them appropriately. Is it a bad plan? Missing index? Slow IO? Slow CPU? Not enough RAM? Poor code? USe a methodology like Waits and Queues to identify the bottleneck.


Scalable DBaaS that can scale to hundreds of terabytes with easy backup and restore

Barry Morris here from NuoDB.  Answers below:

  1) DBaaS (can be self hosted too) the can easily scale to terabytes of
  data (ie. big data)

NuoDB is a downloadable product that runs anywhere (laptop, rack, public cloud).  We have not announced DBAAS at this point.  NuoDB uses Key/Value stores at the storage layer so supported DB sizes will be related to whatever the particular KV store can handle.

  2) Capable of very high throughput for real-time application that does
  many read/writes (although I will use caching too to reduce overload)

NuoDB is a general purpose database system designed for high-speed scaled-out transaction processing.  There is no need for a separate cache tier because the NuoDB in-memory tier performs a similar function.

  3) Being able to query the data using SQL, a RDBMS DB. Although if a
  NewSQL solution isn't available, I will go with NoSQL

Check. NuoDB is a SQL database.

  4) Easy backup and restore big amount of data in case of data
  corruption. I know that many solutions are safe against server
  failure, but I worry about data corruption and I want to be able to
  restore the data back if that happens - IMPORTANT!

NuoDB supports JDBC, ODBC and other standards APIs, so backups can be done at the SQL level. Other alternatives include simply taking one of your storage managers offline and putting it somewhere safe.

  5) I will be developing my web app / mobile app in ASP.NET, so I
  prefer a solution that have a .NET connector

NuoDB has an ODBC API and there is a community developed ASP.NET connector.

  6) If hosted, I prefer hosting my DB on Amazon AWs.

NuoDB supports Amazon AWS

  7) I don't want a self-managed solution and handle sharding and all
  the other complicated stuff

No sharding or partitioning (or caching, M/S replication etc) needed with NuoDB.

  8) I want an affordable solution, not like Clustrix that costs
  thousands of dollars per node. I want a cost-effective solution where
  I can start small and pay more when my database grows (all idea behind
  cloud architecture, right?)

NuoDB is free forever for a baseline system.


What causes 'Recovery completed for database ...' log messages?

Check the database options for AutoClose. It looks like your database is having to start up every time someone connects to it after there have been no connections. To start a database, it also involves looking for any non-checkpointed transactions in the log, which then does a recovery to get the database ready for action.

AutoClose should be OFF.


You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this.

A quick stored procedure should do the job:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val;
  END LOOP;
END;


Track my own custom metadata on Postgres tables and columns

Usually the most portable way of doing this is to have your own metadata table, something like:

create table meta(
  table_name text not null,
  column_name text not null,
  attribute_name text not null,
  attribute_value text not null,
  primary key (table_name, column_name, attribute_name)
);

This approach works with any database
Access to metadata is done by standard SQL
Migration and backup is very easy
The attribute_value can be anything, you can declare it as byte[], text, json, jsonb, whatever you want...


I think you want something like this,

SELECT pid, count(DISTINCT cid) AS "number_of_customers"
FROM table
WHERE PTIME >= DATE '2017-10-01'
  AND PTIME < DATE '2017-11-01'
GROUP BY pid
HAVING count(DISTINCT cid) > 0;


Probably better to work with a metadata table. This, because the combination between different rooms and facilities can be quite hefty.

Have a look at this approach. I created an entity called Establishment so you can have Hotels, Hostels, B&B, etc under the same table, the difference comed from the type defined on your EstablishmentType. Specific details of addressing, for example, can be stored on another table that can be shared with the Individuals Entity (not represented in this example).

Anyway, test schema:

Note that I choose to store the datatype on the Facilities table, this is probably discouraged because its associated with the concept of the God Table, if all you want to do is present a table with the room facilities (frontend-wise) just forget about it.

This approach will allow you to have any number of facilities per room without crowding a single table with NULL values. The model allows, also, any number of Establishments and Rooms types. You could also add another table in between and give the option of separating rooms between different buildings, branches, etc.

Alternatively you could choose to store XML or JSON on the RoomFacilities and let the frontend do the magic and deserialize the content of that table.

{
    "facilities" : {
        "number" : "1"
        "beds" : "2",
        "tv" : "22inch",
        "size" : "20m^2"
        }
}

About the images: You should probably keep them stored on a file server and reference them from within and additional media table.


SQL Server managed backup missing in ssms

It seems it was added in 2014, but then removed in SQL Server 2016 ... that is what i have personally discovered; but i have not found an authoritive document anywhere saying it was done, all i can find is the CMD line options to enable it.


SELECT query to find rows for specific entry

I think you are looking for a simple GROUP BY

SELECT username, COUNT(*)
 FROM location
group by username;


Avoiding a many-to-many relationship cycle between 3 tables

A way to solve your problem is to think of registrations as entities: a registration has a donor, an event, and a set of devices.

The relationship between donors and registrations has cardinality 1:N (a donor can have more registrations, a registration concerns a unique donor), the relationship between events and registrations has cardinality 1:N as well, while the relationship between registration and devices is M:N (in a registration, a donor can offer various devices, while a device can be registered multiple times for different events).

So, this situation can be represented with 5 tables:

Donors(DonorID, other attributes)
Devices(DeviceID, other attributes, OwnerID)
Events(EventID, other attributes)
Registrations(RegID, DonorID, EventID, maybe other attributes, like Status, Date, etc.)
RegistrationsDevices(RegID, DeviceID, maybe other attributes as TimeDonated, etc.)

with DonorID primary key for Donors, DeviceID primary key for Devices, OwnerIDforeign key referring to a Donor, EventID primary key for Events, RegID primary key for Registrations (that has DonorID and EventID as foreign keys), and (RegID, DeviceID) primary key for RegistrationsDevices (and also foreign keys).


There is the possibility that you are getting lock escalations onto the table level, or page locks on the clustered index page. A deadlock graph will show that, try starting a SQL Server trace grabbing just the Deadlock graphs from the server and check those

You can also delete in batches which will improve concurrency and if the delete is handling large number of rows might run faster and allow you to backup and truncate the log and lower the changes of deadlocking.

DECLARE @ROWCOUNT int = 1
WHILE @ROWCOUNT > 0
 BEGIN delete top (10000) from xx where RowNumber >1
 set @rowcount = @@rowcount
END


SQL Server 2014 SP2, backup and restore with powershell

You can use dbatools which is powershell based.
For your case you need to have a 2 step approach.

use the cmdlets in Backup and Restore – Certificates

Backup-DbaDbCertificate
Backup-DbaDbMasterKey
Restore-DbaDbCertificate

then use Copy-DbaDatabase with -BackupRestore switch. Alternately, you can use Backup-DbaDatabase and then Restore-DbaDatabase as per your needs.


Altering column from INT to BIG INT and fill factor trick

I have to tell you the only answer I can come up with is "This makes no sense what-so-ever".

From what I can tell they were expanding the table out so there was room for the additional space for the BIGINT.  They then dropped the primary key (presumably the clustered index as well) to avoid some of the time spent increasing the column width then re-created the primary key.

I've never tried this so I can't tell you how long it would take but I can tell you after a great deal of testing which way I went on a similar task.

Script and drop all indexes except the clustered index.
Create a temp table with the correct structure
Copy the data from the existing table to the temp table
Rename swap
Re-create required indexes
Drop original table

I wrote about the logic of it in more detail here.


Trace flag 2861 and what a 'zero-cost' plan actually means

Cost is an overloaded term, as is trivial.

When talking about execution plans, the estimated cost is computed by the query optimizer, as a way to choose one plan, or one small part of a plan, over another. The final plan has an associated cost computed by summing all the subtree estimated operator costs. A trivial plan is one obtained without going through cost-based optimization.

When talking about the plan cache, a separate cache cost is computed that is an input into how long the plan will survive in cache (normally as a response to memory pressure). The details of this computation, and what is considered a "zero cache cost" (or "trivial") plan has changed many times over the years. There are many exceptions, caveats, and restrictions, which again have changed quite often.

I have no idea if trace flag 2861 is still effective in modern builds.

You can find some of the details (that were correct at the time they were written, to the best of my knowledge) in resources like:

3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 by the SQL Programmability & API Development Team
Plan Caching and Recompilation in SQL Server 2012 by Greg Low


SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with "ReportWrongProviderType" error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2,  while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.


Large dataset of 400+ million rows to compare

Isn't contact unique in both tables?  If so, get rid if id and have PRIMARY KEY(contact).  There are multiple reasons why this is likely to speed things up.

active_call and call_status sound like things that are changed often?  That interferes with the setting of the dnc_contact, especially when done in large chunks.

I can think of no good reason to even have dnc_contact in contacts.  It is redundant information.  That is, when you are about to make a call, do the JOIN to verify that the phone number is not in dnc_contact.

If you really need to do the UPDATE, here are tips on how to do it is a relatively non-invasive way -- bu chunking.


Why would the user matter when running a query?

There could be different reasons - I'll speculate some of them:

You could have row level security and have different rows returned for different callers
You could have resource governor enabled with different resource sizings for each user based on a login trigger
Perhaps the different users are on different machines and there are subtle differences in how the tests are run - for example, one user is always first and gets to compile the query not in the cache or there is a parallel plan that is parallel for one user and not the other due to load on the machine at the time
Maybe the queries are slightly different in text on the two machines and you are actually getting different query plans somehow for the two cases.

It usually helps to either do a profiler or XEvent trace to look at the exact runtimes and showplans at runtime to get insight into why one is slower.  On SQL 2016+, Query Store makes it easier (as is the case on SQL Azure), so if you have the option to try the repro on a later version it might help you find the answer.  Good luck!


SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates?

You could use transaction level advisory locks to serialize your operations:
BEGIN;
-- take an advisory lock for the duration of the session
SELECT pg_advisory_xact_lock(4711);
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');
COMMIT;

The number 4711 is an arbitrary constant.
Then the second transaction using the same code will block until the first one has committed.


Why can't I create temporary tablespace?

To create a temp tablespace, the syntax is CREATE TEMPORARY TABLESPACE, not CREATE TABLESPACE.

CREATE TEMPORARY TABLESPACE myTempTableSpace
  TEMPFILE '/home/folder/tempfile.dbf' SIZE 1M
;

I wouldn't set any storage clauses, unless you have a good reason to do so.

Documentation link here.


It seems like you've been reading about Snapshot Publications and replication. There are three items in SQL Server with the name snapshot. One is the database snapshot. When a database snapshot is created, one new file per existing data file is created and the database engine will save the original page for any changed page in the database into those files. Database snapshot files are just a collection of pages since the snapshot creation and won't be useful on any other server. Another type of snapshot is a Snapshot Publication, which is part of the replication system. When you create a Snapshot Publication, you specify which articles, which can be tables, views procedures,ect, to include in the publication, specify a subscriber and schedule it for delivery. You can configure it to drop the existing articles at the subscriber and recreate them so they will be replaced every time the snapshot is pushed. It will synchronize all articles between the publisher and subscriber at the time it is run. Which brings be to the third instance of an object named snapshot. Within the replication system there is an agent or executable named the snapshot agent, whose job is to take a snapshot of the articles in the publication at the time it is executed.

If you really need to transfer the entire database from one server to another on a regular basis, you can design a backup system that takes full backups once per week, for example, with differential backups daily and transaction log backups hourly for production. Then transfer the full backup once per week to the test environment and leave it there. After that you would only need to transfer the differential backup daily to restore using the full followed by the differential. I would also advise turning on backup compression to minimize the sizes of the files involved.


A linked server is referenced just like you would reference a database, with multi-part naming - LinkedSvrName.DBName.SchemaName.ObjectName.  There isn't really anything else to "accessing" a linked server.

There are some settings you will need to be cognizant of to ensure you can return data; RPC and RPC Out for instance.  Also you will want to use linked servers sparingly as performance will suffer.  This should get you well on your way to Googling the rest of what you need to know.  This topic is really too broad to be covered in this format.


Add column for subsequent values for a record

To get the primary value first, then the values by indiv_id, assign each row a number starting at 1 for each hhid:

row_number() over (
  partition by hhid order by prim desc, indiv_id
) rn

Once you've got that, you pivot by the values 1, 2, 3, ..., N.

with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot (
    min(indiv_id) for rn in (
      1, 2, 3, 4
    )
  );

HHID   1    2    3        4
A        10   11       12       13
B        15   14   <null>   <null>
C        16   17   <null>   <null>

If you know (or can make a reasonable guess...) as to the max number of rows/hhid you're done. If you want the columns in the output to change, there's no simple solution.

You could XML pivot:

with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot xml (
    min(indiv_id) for rn in ( any )
  );

H
-
RN_XML
------------------------------------------------------------------------------------------------------------------------
A
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">10</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">11</column></item><item><column name = "RN">3</column><column name = "MIN(INDI
V_ID)">12</column></item><item><column name = "RN">4</column><column name = "MIN(INDIV_ID)">13</column></item></PivotSet
>

B
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">15</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">14</column></item></PivotSet>

C
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">16</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">17</column></item></PivotSet>

But that's kinda cheating as you still have one column. And you need to parse the XML...

Or you can fall back on dynamic SQL:

var cur refcursor;
declare
  in_list varchar2(10);
begin
  with rws as (
    select distinct row_number() over (
             partition by hhid order by prim desc, indiv_id
           ) rn
    from   t
  )
    select listagg(rn, ',') within group (order by rn)
    into   in_list
    from   rws;

  open :cur for 'with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot (
    min(indiv_id) for rn in ( ' || in_list || ' )
  )';

end;
/

print :cur;

H          1          2          3          4
- ---------- ---------- ---------- ----------
A         10         11         12         13
B         15         14 <null>     <null>
C         16         17 <null>     <null>


If your error is

ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

you can run a FLUSH HOSTS on master, it will flush the list of blocked host which exceed the number or fail connections attempts to your mysql master

It won't affect any running connection, it will only clean the list of blocked hosts.


The specified network name is no longer available

Sounds like spotty DNS resolution issues or long running queries.
For DNS trouble-shooting
As a Band-Aid and trouble-shooting aid add and entry to the hosts file on your app servers (not the SQL Server) at c:\windows\system32\drivers\etc
SQLServerIPAddress SQLServerName

Example:
172.16.0.5 ProductionSQLBox

That way, the SQL Server name will be resolved by the hosts file until you can find the real problem for what's going on with name resolution.
Be sure and test the hosts file by pinging the SQL box from the app server via a command line. Or, alternately create a DSN with the ODBC Administrator within Control Panel and test the connection there.
For long running queries
Implement a longer command timeout value for the application connection strings connecting to the SQL Server.
Example
<connectionStrings>
    <add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=userName;password=ABC123;Connect Timeout=120" />
</connectionStrings>


Postgres SQL on AWS RDS how to grant permission for schema, select on tables to user

Try grant with double quotes around schema name:

grant usage on schema "autorsid" to readonly ;


Merge two records in the same table, keeping foreign key relationships intact for both

This is a very straight-forward data cleanup task. Your solution is good (I'd recommend using a transaction construct as below, just to be safe) -- I've taken the same approach except I used set-based operations so it can be scaled up and automated once you come up with a better duplicate detection algorithm than the rudimentary one I provided.

/* Test setup */
CREATE TABLE [dbo].[Sites]
(
    Id int NOT NULL PRIMARY KEY,
    Name nvarchar(50) NULL,
    Path nvarchar(100) NULL
);

CREATE TABLE [dbo].[Logs]
(
    Id int NOT NULL PRIMARY KEY,
    SiteId int NOT NULL FOREIGN KEY REFERENCES [dbo].[Sites](Id),
    LogData nvarchar(MAX) NULL
);

INSERT INTO [dbo].[Sites](Id, Name, Path)
    VALUES
        (1, N'Google', N'http://www.google.com'),
        (2, N'Microsoft', N'http://www.microsoft.com'),
        (3, N'Yahoo', NULL),
        (4, NULL, N'http://www.yahoo.com');

INSERT INTO [dbo].[Logs](Id, SiteId)
    VALUES (1, 1), (2, 1), (3, 2), (4, 3), (5, 3), (6, 4);

/* Identify potential duplicates (note: very rough) */
SELECT
    s1.Id AS SourceId, s1.Name AS SourceName, s1.Path AS SourcePath,
    s2.Id AS DuplId, s2.Name AS DuplName, s2.Path AS DuplPath
    FROM [dbo].[Sites] s1
    INNER JOIN [dbo].[Sites] s2 ON
        (s2.Path LIKE (N'%' + s1.Name + N'%')) AND
        (s2.Id > s1.Id);

/* Merge duplicates */
DECLARE @duplicates table
(
    SourceId int NOT NULL,
    TargetId int NOT NULL,
    PRIMARY KEY (SourceId, TargetId),
    CHECK (SourceId != TargetId)
);

INSERT INTO @duplicates(SourceId, TargetId)
    VALUES ; /* Edit me! */

SET XACT_ABORT ON;

BEGIN TRANSACTION;

    UPDATE l
        SET SiteId = d.TargetId
        FROM @duplicates d
        INNER JOIN [dbo].[Logs] l ON l.SiteId = d.SourceId;

    UPDATE st
        SET
            Name = COALESCE(ss.Name, st.Name),
            Path = COALESCE(ss.Path, st.Path)
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] ss ON ss.Id = d.SourceId
        INNER JOIN [dbo].[Sites] st ON st.Id = d.TargetId;

    DELETE s
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] s ON s.Id = d.SourceId;

COMMIT TRANSACTION;

/* Validate the site attributes were merged correctly */
SELECT
    s.*
    FROM @duplicates d
    INNER JOIN [dbo].[Sites] s ON s.Id = d.TargetId;


mysql triggers to multiple table and insert new id to table

CREATE TRIGGER tr_b
AFTER INSERT
ON db1.table_b
FOR EACH ROW
INSERT IGNORE INTO db2.table_b (column)
VALUES (NEW.column);

The same for table_c and table_d.

CREATE TRIGGER tr_a
AFTER INSERT
ON db1.table_a
FOR EACH ROW
INSERT INTO db2.table_a (id_b, id_c, id_d, column)
SELECT b.id, c.id, d.id, vals.column_a
FROM ( SELECT b.column column_b, c.column column_c, d.column column_d, NEW.column column_a
       FROM db1.table_b b
       JOIN db1.table_c c
       JOIN db1.table_d d
       WHERE b.id = NEW.id_b
         AND c.id = NEW.id_c
         AND d.id = NEW.id_d ) vals
JOIN db2.table_b b ON b.column = vals.column_b
JOIN db2.table_c c ON c.column = vals.column_c
JOIN db2.table_d d ON c.column = vals.column_d;

PS. Not tested, minor errors possible, check and debug.

PPS. The structure assumes that column in tables b, c and d is defined as unique.


Optimizing and maintaining test databases for a web development team

As a specific answer to "speed of reloading the test environment's database": you probably do not need to rebuild the test database (copy producion then randomise) every time it needs to be reset. Instead at key times take a copy, randomise it, and store that copy - then each time you reset the test environment all you have to do is restore a new copy of that database. You could speed things up further by having more than one restored at any given time: to reset drop the current DB, rename one of the other copies to take its place (testing work can now start immediately), restore another copy to replace the one you are now actively using ready for the next reset.

More generally:

Wanting to use production data as it is a good representation of the real world but not wanting to give people access to real data are, if the data is truely sensitive, completeley mutually exclusive. If you randomise the data enough that there is absolutely no way a sufficiently determined data miner couldn't infer useful real information from it then you've randomised it to the point that it no longer represents reality any more than made up test data would anyway.

An alternative is to create your own test data in a scripted manner which has a number of beneifts:

You don't have to worry about disguising sensitive data taken from production environments at all. From a regulatory and legal standpoint this can be a big win (if you work with banking information or personal data for instance).
You can control the size of the data-set. Just want a small set of data to develope a new feature against? Then generate a few tens or hundreds of people with relevant records going back a few months. Testing the performance of a new feature over large data? Generate a many Gb database with thousands upon thousands of people with data spanning many years.
Controlling the size of the data-set means you can generate small test sets very quickly. Larger ones may take more time but are still possible (or for larger tests you can fall back to using a munged copy of produciton data, with extra precautions to make sure no devs can touch the DB until the munging is complete).
You can control the inclusion of difficult edge cases. Keeping them out completly for initial delevopment of new ideas, and including every single problem case you have ever seen in production or otherwise can think of if you are about to preform regression testing on new code changes. This can then become a useful part of your QA cycle.
Your devs can have the flexibility to create their own local test databases easily, as and when they want without needing to coordinate with the shared testing environmemnts until they have something ready to deliver that far up the chain.

For "people" in the above substitude what-ever units your system's size is dependent on. There may be more than one key unit of course: for a training and competence tracking system with its own test managment and training material the key size determinants are the size of the question bank and the number of people, so the request to the sample data generating scripts would be something like:

Create a new DB
Make up a question bank of 10,000 questions spread over 20 subjects and 200 topics
Add 1,000 people in teams of about 8 with a supervisory heirarchy to match
Add 6 months data for each person having:
A realistic number of test records inclduing passses, fails and resits
A realistic number of observitions, 1:1 meetings and other interventions for that user type
and so on.

Of course there is work involved here, which is why the "take real data and try to disguise it" approach is a common short-cut (I do it myself often enough!):

You have to write the scripts in the first place
As new features are added to your product, or features change, the scripts need to be enhanced or otherwise altered to reflect these changes
Generating truely realistic test data takes some domain knowledge. Generating a random question bank is pretty easy (many instances of "This is a multiple choice question, the answer is B? A: The answer is A; B: The answer is B; C: The answer is C" is a place to start). For more complex/variable data you can use some analysis of your exsiting real-but-randomised data to guide the creation of your model.
You need to keep an eye on how your users are actually behaving, as changes to their use of your service need to be reflected in your test data model (and if you have widely differening clients, you may need to branch the model).

There are products out there that are designed to help create and maintain this sort of test data, though for small systems or particularly complex ones hand-rolling by a good local dev may produce better results cheaper.


dev.mysql.com has 2 different installers to download, which do I need?

Look up.

"Choosing the right file:

If you have an online connection while running the MySQL Installer, choose the mysql-installer-web-community file.
If you do NOT have an online connection while running the MySQL Installer, choose the mysql-installer-community file."


Understanding the impact/risk of turning off "verify backup integrity" on SQL backup

Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something?

No, you are right :-)
RESTORE VERIFYONLY only will not ensure that you will be able to recover your database in the event of corruption. By nature, it will not perform any integrity checks.
A better way will be to periodically take your backups and do a valid restore on a different server and perform DBCC CHECKDB on it.
This is one reason, why I am not a big fan of maintenance plans since the GUI does not expose a lot of options like backup .. with CHECKSUM that can be achieved by T-SQL.
From Paul Randal's Myth blog

24p) using RESTORE … WITH VERIFYONLY validates the entire backup
No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY  and using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.
Do you run "verify" on every backup in your environment? Do you spot-check them?

I dont run the VERIFYONLY. Instead I take backup with CHECKSUM and then have them restored + CHECKDB'ed on a different server. You can follow
Statistical Sampling for Verifying Database Backups approach if you want to be creative.

This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).

You can enable Trace Flag 3023 so that the CHECKSUM option is automatically enabled for the BACKUP command. As always, test the behavior of any trace flags in your environment !
The bottom line is - ditch maintenance plans and use a more sensible backup solution (hint: Ola's backup solution) that will allow you to customize it based on your needs.

(We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)

Backup up locally to the disk and then have a PowerShell transfer job that will copy the backup locally from the server to a network share (backup server). That will be more faster than to directly copy to a network share.
Also, enable instant file initialization, that will help with auto-growths on data files as well as will help cutdown the restore time (incase if you have to restore your databases). Its always good to have options handy.
A good read will be : Backups: Planning a Recovery Strategy


InnoDB slow query (few derived and dependent queries)

CREATE TEMPORARY TABLE pro ( PRIMARY KEY (product_id, station_id) ) ENGINE=InnoDB
    SELECT DISTINCT product_id, station_id FROM productie;
CREATE TEMPORARY TABLE s ( PRIMARY KEY (station_id)  ) ENGINE=InnoDB
    SELECT DISTINCT station_id, station_omschrijving FROM stations;

SELECT
        ....
    FROM producten p
    JOIN pro  ON pro.product_id = p.product_id
    JOIN s    ON s.station_id = pro.station_id
HAVING p.afschrijvingenbedrag = 0
    OR p.... = 0
    ....;

Notes:

By creating tmp tables with useful indexes, those JOINs will run much faster.
I got rid of inner_query and changed WHERE inner_query. into HAVING p.  This may not improve speed, but it simplifies the code.

Another thought...  Did you really need the DISTINCTs?  Keep in mind that SELECT DISTINCT a,b is like GROUP BY a,b, not like GROUP BY a.

Another thought...

s.station_omschrijving
...and...
INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
        ON s.station_id = pro.station_id

could perhaps be replaced by

( SELECT DISTINCT station_omschrijving FROM stations
      WHERE station_id = pro.station_id ) AS station_omschrijving
...and no join...

It is, however, unclear whether the JOIN is restricting the overall query and whether the DISTINCT is correct.  I simply don't know what the data looks like in that table.  Maybe DISTINCT station_omschrijving needs to be MAX(station_omschrijving) to avoid multiple rows?


How to know exactly why SQL SERVER 2014 Express installation failed?

If you can open the SQL Server Installation Center with this installer it will give you a lot of useful info.

In the Planning section you can find the Hardware & Software requirements. Or: https://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx


MongoDB insertion in two collections or one to improve insertion speed

As I have two types of reports. Lets call them report_type_1 and report_type_2. Currently I insert both these reports in same collection. Will it make insertion any faster if I use different collections for these two different types of reports?

The outcome will depend on the storage engine you are using and whether concurrency control is the limiting factor for your insertion rate. The MMAPv1 storage engine has collection-level locking, while the WiredTiger storage engine (default in MongoDB 3.2+) has document-level concurrency control.

If you are pushing the limits of MMAPv1 locking for your deployment, splitting the reports into different collections may improve your insertion rate unless the underlying issue is a resource bottleneck (slow disk, insufficient RAM, ...).

The WiredTiger storage engine has more granular concurrency control as well as data compression (which reduces I/O but adds CPU overhead). With WiredTiger you should be able to increase insertion throughput to a single collection by adding more insertion threads in your application (at least until your server resources are saturated).

Rather than trying to design around possible MMAPv1 limitations with multiple collections, I would encourage you to test a single collection with the WiredTiger storage engine and multiple insertion threads.

Outside of storage engines, a more general question is whether the different report types should logically be in the same collection. If they have different fields or indexes and you don't query across report types, you may find it more efficient to use separate collections.


Splitting or Distributing very large data on PostgresQL 9.6

As @a_vlad stated, you should do have a plan. Also, for later use I would like to recommend you guys two things.

Tablespace
Citus Data (distribute data across workers)

Citus is a distributed database that combines the low-latency requests that power real-time applications with high throughput, interactive analytics on billions of events.
Citus does this by extending PostgreSQL to distribute your workload across multiple hosts, leveraging the memory, storage, and processing power of multiple machines.

Read more


Restoring encrypted database on another server (using Backup Encryption)

Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need master key and only certificate is required for restore purposes. Follow the below steps:

Step1: Create Master Key

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';

Step2: Verify permissions on cert and pvt key

Make sure SQL Server service account of second instance has FULL permissions on cert and pvt key that you created.

Step3: Create cert from backup

CREATE CERTIFICATE BackupEncryptTestCert
    FROM FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
     WITH PRIVATE KEY
      (
        FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key' ,
        DECRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
      )

Step4: Restore the DB


A much better way to purge a table by date is by using DROP PARITION.  See http://mysql.rjweb.org/doc.php/partitionmaint  Unfortunately, it requires adding partitioning to the table and changing some indexes.  This will be a very long (one-time) task for 1.2TB.
Meanwhile...  If 10000 is tunable, lower the number to, say 1000.  I suspect that other activities on the table are conflicting with the archiving scan.  By decreasing that number, the will be less chance of conflict, at the expense of the archive taking longer.  (No, not 10 times as long.)
("Sending data" is useless information.  Alas, that is probably the only clue you can get.)
(id < '3873782070'):  Is id INT UNSIGNED?  If so, might it overflow soon?


scaling options for mysql on linux vm (ubuntu) : how to scale when the RAID 0 is out of space

You have to ask yourself the question "How much is my data worth to me/the company?".

Cheap solution:

You could buy 2 2TB disks to replace the 2 1TB ones you already have. That would be a stop gap IMHO. RAID0 really doesn't cut the mustard these days with bit-rot and 24x7 operation. If one of your disks goes down, you'll place a lot of extra strain on the other disk (possibly provoking its failure - then you're bunched :-( )

More expensive, but worth it (IMHO):

Personally, I'd use a FreeNAS box with RAID-Z also striped and mirrored - or possibly Z-3. If you have the expertise in-house, great, but if not, I'd recommend iXsystems (I am not associated with this company, just have heard good reports from people who work in this area - plus they develop FreeNAS).


Best way to copy data to other location without blocking source

This depends a lot on your goals / requirements for the data in the destination.  Specifically you need to weigh the pros and cons between blocking and data correctness.
Avoid Blocking — READ COMMITTED with SELECT...INTO
For instance, the most naive solution under the default READ COMMITTED isolation level would solve your problem with very little blocking on the source server:

[Optional, if the database is on another server] Create a linked server on the destination (SQL Server 2008) instance that connects to the source (SQL Server 2016) instance
Run a SELECT...INTO statement to copy the desired 60mm rows

SELECT /*column list*/
INTO [dbo].[BigTable]
FROM [SourceDatabase].[dbo].[BigTable]
WHERE /*predicates to get the 60mm rows you need*/

Note: you'll need to specify the linked server name in the FROM clause if the linked server approach is used
Shared locks will be taken and released as pages are scanned in the clustered index (or heap, as the case may be), so there will be brief moments of blocking specific pages, but nothing that should be incredibly noticeable for your production system.  Of course, there might be other points of resource contention (storage throughput, for instance).
The main problems with this approach is that it makes basically no guarantees about the correctness of the data that will arrive at the destination.  There's a Great Post™ by Paul White about The Read Committed Isolation Level that you can check out for the details, but the main problem is that this doesn't guarantee a point-in-time view of the data.
In other words, DML statements (INSERTs, UPDATEs, and DELETEs) can successfully execute during the scan, which could result in rows being read more than once, rows being missed entirely, etc.
A potential problem for the destination database would be log file growth.  If the size (not just number of rows) of the data being moved is very large, you may need to consider batching the inserts rather than doing them all in one go.
Favor Correctness - More Restrictive Isolation Levels
If you want the data that arrives in the destination to provide data that is consistent with a specific point in time, you can use the SNAPSHOT or SERIALIZABLE isolation levels during the entire insert process (the actual insert process could be the same as or similar to the one described above).
The SNAPSHOT isolation level is a viable option.  It will cause an increase in tempdb usage as row versions are maintained to provide a consistent view of the database, so you need to make sure you system can handle that workload.  It will also cause some types of blocking (more than READ COMMITTED, but less than the SERIALIZABLE approach).  How much this will affect your production workload would be dependent on you to test.
The SERIALIZABLE isolation level will be the most disruptive to your production workload, as it will block all access to the table until the transaction is complete.  This doesn't meet your requirements, so it's not really a viable option.
Correctness AND No Blocking - Replication / Backups
If you're able to restore a full database backup somewhere, that's the ideal solution here.  You have a point-in-time view of the database, without contention, from which you could then copy the needed rows to the destination.  Since you mentioned database size / space is an issue, I imagine this isn't a viable option.
Due to the special requirements for your situation, you might want to consider buying a third-party tool that supports "object level restores."  This would allow you to restore just that needed to table to the destination directly from a backup.  I think that Quest Litespeed supports this, although it's hard to tell behind the paywalls on their site.
Transactional Replication might also be a viable option if you need the data to be continuously up to date, although the version restrictions might prevent you from being able to leverage this option.  Additionally, the maintenance and setup overhead might be impractical.  I haven't used transaction replication in practice, so I'm just mentioning it as a possibility.
About NOLOCK
Don't use that.


Make sure you have right UNIX ulimit Settings.You can see also: https://docs.mongodb.org/manual/reference/ulimit/
Use top command to check if the performance bottleneck is CPU overload.Check the mongodb log file if there is any slow query,and you can use MTools to analyse it.Then you need optimize the slow query.
Use free command to check if not enough physical memory is available.If MongoDB has too many connections,you need to check Stack settings.every connection is a thread,need a Stack,Stack settings usually is too large by default in Linux:
shell> ulimit -a | grep stack
You can see the real used Stack size,like this:
shell> cat /proc/$(pidof mongod)/limits | grep stack | awk -F 'size' '{print int($NF)/1024}'
The total mem consumed by connections is so large,I suggest you set it smaller,such as 1024:
shell> ulimit -s 1024


If your queries know beforehand which type of game you're refering to, and you are only having one game per query, the cleanest way is most probably to have different surrogate-tables for different games, and always JOIN the main table with the one specific for your type of game.

For instance, you would have a table for all the different types of games:

 CREATE TABLE games
 (
      game_id integer PRIMARY KEY,
      game_name varchar(100) NOT NULL
 ) ;

 INSERT INTO games
     (game_id, game_name)
 VALUES
    (1, 'straight pool'),
    (2, 'one pocket'),
    (3, '9 Ball'),
    (4, '8 Ball') ;

A table for players (and probably for leagues, seaons, etc.)

 CREATE TABLE players
 (
     player_id integer PRIMARY KEY, -- probably autoincrement
     player_name varchar(100)
 ) ;

 INSERT INTO players
    (player_id, player_name)
 VALUES
    (1, 'Player 1'),
    (2, 'Player 2'),
    (3, 'Player 3'),
    (4, 'Player 4') ;

Your matches table:

 CREATE TABLE matches
 (
      match_id integer PRIMARY KEY, -- probably autoincrement

      game_id integer NOT NULL REFERENCES games (game_id),
      player_1_id integer NOT NULL REFERENCES players (player_id),
      player_2_id integer NOT NULL REFERENCES players (player_id),
      match_date date NOT NULL,
      -- probably a league_id
      -- probably a season_id
      -- some other data common to all matches

      UNIQUE (game_id, player_1_id, player_2_id, match_date)  -- Alternate key
 ) ;

And then, for each specific game type, a table with all the columns that makes sense:

 CREATE TABLE straight_pool_matches
 (
    match_id integer REFERENCES matches(match_id),
    -- Don't know enough to know what to put here
    info_about_straight_pool_matches varchar(255)
 ) ;

So, let's image there is a match of "straight pool" on 1st Jan 2017, between players 1 and 2. You would add data to two tables:

 INSERT INTO matches
     (match_id, game_id, player_1_id, player_2_id, match_date)
 VALUES
     (1, 1, 1, 2, '2017-01-01') ;

 INSERT INTO straight_pool_matches
     (match_id, info_about_straight_pool_matches)
 VALUES
     (1, 'Something that makes sense about a straing pool match') ;

And the way you would perform your queries would most probably be:

 SELECT
     m.match_id, p1.player_name AS player_1, p2.player_name AS player_2, m.match_date,
     s.info_about_straight_pool_matches
 FROM
     matches m
     JOIN players p1 ON p1.player_id = m.player_1_id
     JOIN players p2 ON p2.player_id = m.player_2_id
     JOIN straight_pool_matches s ON s.match_id = m.match_id
 WHERE
     m.game_id = 1
 ORDER BY
     m.match_date, m.match_id ;

 match_id | player_1 | player_2 | match_date | info_about_straight_pool_matches
 -------: | :------- | :------- | :--------- | :----------------------------------------------------
        1 | Player 1 | Player 2 | 2017-01-01 | Something that makes sense about a straing pool match

dbfiddle here


Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date?

No.  You cannot manually switch an ONLINE database to RESTORING and apply log backups, and you cannot attach a database and leave it in the RESTORING state.

This would be a useful feature, and it's been requested before.  See eg here.

  Which kinds of database backups would I need available to be able to do so?

Any full backup, optionally a differential backup, and then the log backups from that point forward.  Note that it doesn't have to be the latest full backup, as long as you have all the log backups going back to that full.  Full and  differential backups never break the log chain.


I need help creating an index for timestamp

First, you need to make your query SARGable. Fortunately, in your case, that is simple.

SELECT date_provided as d,available as a
FROM parking_data
WHERE code = 'E03'
AND date_provided > NOW() - INTERVAL '20' HOUR

Your version of the query required the optimizer to add 20 hours to every record's date_provided, and then compare each to the value provided by NOW(). This new version of your query modifies NOW() by 20 hours, and so can use an index to find exactly where the records start that have that timestamp.

If your query is still too slow, you can add a composite index.

ALTER TABLE parking_data
ADD INDEX (`code`,`date_provided`)

This should yield extremely quick results.

You can also drop the single column index on code if you add this index, as that single column index will be redundant at that point.


Sql server snapshot isolation overhead when read_committed_snapshot is enabled

The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.


mysql 8 high availability with drbd, corosync and pacemaker

Those days I won't recommend you to use these technique to MySQL HA. I love pacemaker but it's not suitable for datbases are those are much more complex.

I would recommend you to use MySQL InnoDB Cluster that handles everything, from orchestration to routing. It provides you everything you need built-in, split-brain prevention, quorum, data consistency, automatic data provisioning, ...

Take a look at :

https://www.slideshare.net/Grypyrg/mysql-innodb-cluster-new-features-in-80-releases-best-practices
https://www.slideshare.net/Grypyrg/mysql-database-architectures-innodb-replicaset-cluster-220070786
https://www.youtube.com/watch?v=m7pFwxiglHc

Regards,


REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

If your data is not sorted (by the indexed columns), the maintenance of indexes during the load of large amount of data may have some serious overhead because of the leaf node splits (not 90-10 splits), so:

truncate the table
alter index ... unusable on all indexes
load data
alter index ... rebuild on all indexes

This will work if SKIP_UNUSABLE_INDEXES is set to true, which is the default value, but keep this in mind:

  If an index is used to enforce a UNIQUE constraint on a table, then
  allowing insert and update operations on the table might violate the
  constraint. Therefore, this setting does not disable error reporting
  for unusable indexes that are unique.

Alternatively, yes, you can drop the indexes before the load, and recreate them after.


In postgres, is using a lock better than transaction when reading data?

I think that you can prevent this problem by putting FOR UPDATE at the end of your SELECT statements. I am not sure because your description is somewhat confusing.

To avoid deadlocks, add an ORDER BY id to the queries as well.


Backup transaction log from secondary database in alwayson AG

You can take log backup on any nodes (Primary/Secondary) irrespective of its availability mode. This is what MSDN has to say about this in Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups):

  BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
  Differential backups are not supported on secondary replicas.
  BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
  A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
  To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

The link below begins a good series on backups, which also dives into different scenarios like information stored in MSDB tables for backups across several nodes:

SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica by Lisa Gardner


I was wondering how SQL Server proceeds while rebuilding the fulltext catalog.

From BOL :

When you REBUILD a fulltext catalog, SQL Server rebuilds the entire catalog. When a catalog is rebuilt, the existing catalog is deleted and a new catalog is created in its place. All the tables that have full-text indexing references are associated with the new catalog. Rebuilding resets the full-text metadata in the database system tables.

I would suggest you to look into REORGANIZE depending on the fragment counts as described in my answer for Reorganize full text catalog is offline or online?


BACKUP MASTER KEY failing with cannot find symmetric master key because it does not exist

The certificate used by TDE to encrypt the database encryption key is actually stored in the master database, which is in turn encrypted by the database master key stored in the master database.

The query to see which certificate is used to decrypt the my_db TDE-encrypted database should be:

SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN master.sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

Note, the only change is to reference sys.certificates in the master database.

The results of that query shows:

╔══════════════╦════════════════╦═════════════════════════╦════════════════════╦════════════════════╦══════════════════════════════╦═══════════════════════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ CertName           ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ Certsubject               ║
╠══════════════╬════════════════╬═════════════════════════╬════════════════════╬════════════════════╬══════════════════════════════╬═══════════════════════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ db_encryption_cert ║ <redacted>         ║ ENCRYPTED_BY_MASTER_KEY      ║ DB Encryption Certificate ║
╚══════════════╩════════════════╩═════════════════════════╩════════════════════╩════════════════════╩══════════════════════════════╩═══════════════════════════╝

Note the query shows the certificate is ENCRYPTED_BY_MASTER_KEY - the master key referenced here is the master key for the master database.

In order to restore my_db onto another server, you'll need to backup the the certificate (with its private key) used to encrypt the database, then restore it onto the target SQL Server.

This should happen on the source SQL Server:

BACKUP CERTIFICATE db_encryption_cert
TO FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , ENCRYPTION BY PASSWORD = 'new private key password'
    );

Store the resulting certificate file and private key and it's password on a secure file system offsite.

This should happen on the target SQL Server:

CREATE CERTIFICATE db_encryption_cert
FROM FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , DECRYPTION BY PASSWORD = 'new private key password'
    );

Once you've created the certificate on the target server, you should be able to restore the database without issue.

If you are preparing for disaster recovery, and intend on being able to restore the source SQL Server's master database, etc, you should also backup the service master key and the master database master key:

USE master;
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\service_master_key'
ENCRYPTION BY PASSWORD = 'new service master key password';

BACKUP MASTER KEY
TO FILE = 'C:\master_database_master_key'
ENCRYPTION BY PASSWORD = 'new master database master key password';

These keys, and their associated encryption passwords, should be stored in a secure location off-site.


Include all possible values of sub-categories under each category

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

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

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

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

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


How to Compare Strings In Two Different VARCHAR Columns?

First I've used an split string function that I've borrowed (again) from this answer. And I've added an ID column (I suppose your table has some PK field to identify every record.)

CREATE TABLE MyTable
(
    Id int IDENTITY,
    FirstName varchar(500),
    LastName varchar(500)
)

INSERT INTO MyTable (FirstName, LastName)
VALUES ('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West'),('Bob Marly', 'Junior');
GO

CREATE FUNCTION dbo.fnSplit(@Input Varchar(1000), @Splitter VarChar(10))
RETURNS TABLE AS
RETURN
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS Data
    FROM (SELECT CAST ('<M>' + REPLACE(@Input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes ('/M') AS Split(a);
GO

Now, using a CROSS APPLY with your data:

    SELECT *
    FROM   MyTable t1
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) > 0;
GO

You can identify the records where LastName contains any word of FirstName:

Id | FirstName     | LastName   | Data
-: | :------------ | :--------- | :----
 1 | Bridget Jones | Jones, III | Jones
 3 | Key West      | West       | West

Using ID's returned from previous query you can update your table:

WITH found AS
(
    SELECT Id, FirstName, LastName, Data
    FROM   MyTable t1
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) > 0
)
UPDATE     T1
SET        T1.LastName = RTRIM(LTRIM(REPLACE(T1.LastName, Data, '')))
FROM       MyTable t1
INNER JOIN found t2
ON         t1.Id = t2.Id;
GO

2 rows affected

And this is the final result:

SELECT * FROM MyTable;
GO

Id | FirstName     | LastName
-: | :------------ | :-------
 1 | Bridget Jones | , III
 2 | Butch         | Jones
 3 | Key West      |
 4 | Bob Marly     | Junior

dbfiddle here


Common term for a table (heap) without any supporting structures

We refer to these as heaps in SQL Server. I understand that Non-Clustered Indexes complicate things, but that's their name.

For what it's worth, on many RDBMS platforms, with auto statistics enabled, there's very little chance a table will not have statistics on it.

I'd be comfortable calling these data structures unindexed heaps.


Matching separate unicode characters as one

Unicode is a complex beast and database engines tend to be at best superficially aware of it. They certainly don't tend to know about sequence equivalences.

I would suggest making sure that you store data in a consistent normalised form, and normalise search parameters in the same way before searching. I'm not really a c# person, but https://msdn.microsoft.com/en-us/library/ebza6ck1(v=vs.110).aspx looks like a decent place to start for how to go about that.

Obviously you will have existing data that is not normalised, so if you go with this route you'll need to write something to scan the data and replace string values with normalised versions as needed.


On a re:Invent conference I watched once, an AWS engineer suggested when we are going to use high IOPS processes, to increase the instance type. My suggestion would be to change your instance type to db.m5.4xlarge, wait 20 minutes and then try again the dump.

Chances are that it will work like a charm!


Below code will give all the Logins with Database Mapping. It gives all the available logins. You can remove the mapping part.

--Logins and Permissions
DECLARE @dbname VARCHAR(50)-- database name
DECLARE @SQL VARCHAR(MAX)

CREATE TABLE #LoginPermissions
(
ServerName Varchar(50),
DBName Varchar(50),
LoginType Varchar(10),
srvLogin Varchar(100),
srvRole Varchar(100),
dbUser Varchar(100),
dbRole Varchar(100)
)

DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
Insert Into #LoginPermissions
select
@@SERVERNAME AS ServerName,'''
+ @dbname + ''' AS DBName,
[Login Type]=
case sp.type
when ''u'' then ''WINDOWS''
when ''s'' then ''SQL LOGIN''
when ''g'' then ''GROUP''
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
case when convert(char(25),dbp2.name) IS NULL then ''Public'' ELSE convert(char(25),dbp2.name) end as dbRole

from ' +
@dbname + '.sys.server_principals as sp join '
+ @dbname + '.sys.database_principals as dbp on sp.sid=dbp.sid left join '
+ @dbname + '.sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id left join '
+ @dbname + '.sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join '
+ @dbname + '.sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join '
+ @dbname + '.sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'

EXECUTE (@SQL)
       FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM
(
Select distinct srvLogin, LoginType,
case when srvRole = 'sysadmin' then '' ELSE DBName END as DBName, ServerName, ISNULL(srvRole, '') as srvRole,
case when srvRole = 'sysadmin' then '' ELSE dbRole END as DBRole from #LoginPermissions

UNION ALL

Select name,
[Login Type]=
case [type]
when 'u' then 'WINDOWS'
when 's' then 'SQL LOGIN'
when 'g' then 'GROUP'
end, '', @@SERVERNAME,
CASE WHEN IS_SRVROLEMEMBER ('sysadmin',name) = 1 THEN 'sysadmin'
     WHEN IS_SRVROLEMEMBER ('serveradmin',name) = 1 THEN 'serveradmin'
     ELSE ''
END AS SrvRole, ''
from sys.server_principals where convert(char(45),name) not in (select distinct srvLogin from #LoginPermissions)
) x
Where LoginType IS NOT NULL
order by ISNULL(srvRole, '') desc, srvLogin

DROP TABLE #LoginPermissions


Understand PostgreSQL's statistics about rows and times

The "rows" field only reports rows returned by the query in question.  The pg_stat_database fields also count rows that had to be fetched from system catalogs in order to plan and execute your queries.  pg_stat_statements doesn't track these, not even with pg_stat_statements.track=all.  Since the catalog data is generally cached within a session, this can give you massive expansion in the number in pg_stat_database relative to pg_stat_statements if you only execute one small query in each session and then disconnect.  These catalog reads can also explain the difference in read times.

There is more to the story than that, like whether they count input rows or output rows for aggregations and joins.  Frankly I find tup_returned and tup_fetched to be useless.

What problem are you trying to solve?


Memory-optimized table index size 10X bigger than data

The size difference is due to how row versioning in indexes and the garbage collector of in memory tables work.

Looking at the garbage collection process used for memory optimized tables:

  If there is no transactional activity after (for example) deleting a
  large number of rows and there is no memory pressure, the deleted rows
  will not be garbage collected until the transactional activity resumes
  or there is memory pressure

Okay, that makes sense for the indexes, but why is the data cleared while the indexes are not cleared?

My educated guess is that removing the row versioning from indexes is a different process than removing the overwritten data from the table. More on that here.

An example for this, with a query that inserts 1M rows and executes a full delete on a table that consists of the three columns you have in your question.

-- insert data into the tables
SET NOCOUNT ON;
DECLARE @I int =0;
WHILE @I < 1000000
BEGIN
    INSERT INTO dbo.MemOptimizedTable([organization_id], [computer_id], [process_id])
    VALUES (cast(@I as bigint) + 2147483647, NEWID(),@I) ;
    SET @I +=1;
END
-- Delete everything
 DELETE FROM  dbo.MemOptimizedTable;

SELECT object_id, memory_used_by_table_kb, memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats;

After running the query the first time

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
1045578763          62501           58852

Running the query 3 times gives us this starting point where the index size has grown beyond the table size

object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62501           78464

While the insert of 1M records is running again, the mem used by table grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           86715            88471

And grows

--Insert running
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           123628           104662

Until the insert is finished

--Insert done
object_id   memory_used_by_table_kb memory_used_by_indexes_kb
965578478           62500            79543

The index grew, the memory used by the table was removed after the insert and remained the same. After this insert finishes, a process is triggered to remove the excess memory that was used by the table.

This is not by any means the complete explanation but could be a starting point.

What can we do?

According to this post there’s also no way to force garbage collection to occur.

You could op to

Do nothing and let SQL Server handle it
Drop and create the index to reset the memory usage (if you have another index on the table)
...

Commands to drop and create the index example

ALTER TABLE  dbo.table
       DROP INDEX [ix_process_aggregation_memory_computer_id_process_id];

ALTER TABLE  dbo.table
       ADD INDEX [ix_process_aggregation_memory_computer_id_process_id] ([organization_id] ASC,[computer_id] ASC, [process_id] ASC);

Create table statement of the table used in the example

 CREATE TABLE dbo.MemOptimizedTable(
  [organization_id] [bigint] PRIMARY KEY NONCLUSTERED NOT NULL,
    [computer_id] [uniqueidentifier] NOT NULL,
    [process_id] [int] NOT NULL,
INDEX [ix_process_aggregation_memory_computer_id_process_id] NONCLUSTERED
(
    [organization_id] ASC,
    [computer_id] ASC,
    [process_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)
GO


How to improve performance of a query with an IN subquery

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery):

SELECT   COUNT(order_id), user_id
FROM     MyCustomTable a
WHERE    type='x'
AND      EXISTS (SELECT 1
                      FROM   MyCustomTable b
                      WHERE  type   ='y'
                      AND    user_id='56'
                      AND    a.order_id = b.order_id)
AND      user_id <> '56'
GROUP BY user_id

Another one is to get rid of the subquery:

SELECT   COUNT(distinct a.order_id), a.user_id
FROM     MyCustomTable a
JOIN     MyCustomTable b
             ON a.order_id = b.order_id
WHERE    a.type='x'
AND      b.type='y'
AND      a.user_id <> '56'
AND      b.user_id = '56'
GROUP BY a.user_id

Test them and use EXPLAIN to check the plans used to execute them. Your question does not show indexes in that table, for fast execution some indexes will be needed - a multicolumn index on (type, user_id, order_id) covers both the main query and the subquery for the first case (and the same for your original query with IN) and works for the second case too.


How many database licenses do we need for setting 2 servers (nodes) into a cluster?

Oracle database licenses are quoted per processor, not per machine. They don't care whether you have two machines with two cores each or four machines with one core each. Both equate to 4 licenses you need to purchase.

For clustered installations, you need to purchase the appropriate database licenses and the RAC licenses.


Does using Retry attempt option SQL Agent job create performance issue

The retry process itself does not use resources: it basically idles for the specified amount of time, then restarts the step. The step itself uses resources.


Why are you using the condition?

CONVERT (DATE,e.CompletedDate) >= '2018-11-01'
AND CONVERT (DATE,e.CompletedDate) < '2018-11-30'

This query is not SARGable, just rewrite your query as

e.CompletedDate >= '2018-11-01'
AND e.CompletedDate < '2018-11-30'

And ensure you have an index with CompletedData field as the first field. As far as I see the condition, it should work in the same way but much faster and will use minimal resources.

As per your comment, apart from the proposed change in your WHERE condition you need to create a new index as:

CREATE INDEX IX_Examinations_CompletedDate ON Examinations(CompletedDate)


Restore Postgresql database from table files

This doesn't sound promising.  Do you know how you got into this predicament?

What specific system tables are empty?  A good chunk of the system catalogs are located inside $PGDATA/base/<db_oid>

Do you have any recent backups that could be used to splice together whatever it is that you are missing?

Of course the first step it to make a backup of the current state of things, so if you somehow make things even worse you can go back and try again.


I have exactly the same problem with 4.5 & 4.6. Postgres 10 server on Ubuntu, PG Admin on Windows 7. I downgraded PG Admin to 4.3 and it works.


Incremental level 1 backup with no Incremental level 0?

Yes, if you want to prevent a level 1 from being created without a corresponding level 0 being available, then you need to do a crosscheck in the script so RMAN knows that the level 0 is not available.

Prior to 10g (or with compatibility < 10.0) Oracle would do a level 0 when a level 1 was done without a level 0.  Since you are on 11g, I would expect your compatibility to be >=10.0, so RMAN "copies all blocks that have been changed since the file was created".  This behavior seems to have changed between versions and there is some confusion as to what the current behavior is, but your findings are consistent with the documentation.


There is no need to copy the files - there is an ASM "trick" that does exactly what you want to do with no interruption in service. When ASM rebalances a disk, it maintains a list of viable LUNS for each block independently of the LUN on which it presently lives. It also won't drop a disk on which there are active blocks without rebalancing elsewhere - but it will mark it as not a viable home for rebalancing!

Once you have used oracleasm to make the new LUNs visible to ASM, you just issue the single command (forgive syntax, off the top of my head)

alter diskgroup my_dg add disk 'new1', 'new2', 'new3' drop disk old1, old2, old3;

Then sit back and wait. ASM will hot-relocate each block from the old storage to the new, laying them out nicely on the way, and drop the old disks when the operation completes. I/O will be a bit higher, but your users probably won't notice a thing.

As for your ORACLE_HOME, I'm afraid switching that will require a (brief) outage.


Hash Match inner join in simple query with in statement

For the 1st query, an index that uses all three columns from the WHERE clause and includes the column from the SELECT list would be much more useful:

-- index suggestion A
(clientid, empuid, newvalue) INCLUDE (m_uid)

or an index targeted specifically for this query:

-- index suggestion B
(clientid, empuid, m_uid)
WHERE newvalue in ('Deleted', 'DB-Deleted', 'Added')

Regarding the trigger, some comments:

The first query you show does not appear in the trigger. What appears is a join from that table to the inserted rows to another table (which has the trigger).
My suggestion B above seems better suited to be used by the trigger.
The trigger has 2 almost identical insert statements. Why? I think they could be combined in one - and simpler - insert and using NOT EXISTS instead of NOT IN:

insert EmpTaxAudit
  ( Clientid, empuid, m_uid, m_eff_start_date, ColumnName,
    ReplacedOn, ReplacedBy, OldValue, dblogin,
    newvalue
  )
select
    Clientid, empuid, m_uid, m_eff_start_date, 'taxcode',
    getdate(), IsNull(userid,@user), '', Left(@user,15),
    case when m_eff_end_date is null
        then 'Added' else 'Deleted'
    end
from inserted i
where not exists
      ( select 1
        from EmpTaxAudit
        where m_uid = i.m_uid
          and clientid = i.clientid
          and empuid = i.empuid
          and newvalue in ('Deleted', 'DB-Deleted', 'Added')
      ) ;


This is a bit tricky as you have to generate ROW_NUMBERs for both tables to join them.
IF you want only to include certain customers you need further criteria than CUSTOMER CODE IS NULL.
And a word of advise, don't use spaces in your column names, you need to encapsulate them always with backticks, that makes much more work, than having column names that lack spaces.

CREATE TABLE CUSTOMER_DATABASE (
  `CUSTOMER ID` INTEGER,
  `CUSTOMER CODE` VARCHAR(6)
);

INSERT INTO CUSTOMER_DATABASE
  (`CUSTOMER ID`, `CUSTOMER CODE`)
VALUES
  ('1', '6f73js'),
  ('2', NULL),

  ('3', NULL);

CREATE TABLE DATA_DISCOUNT_CODES (
  `ID` INTEGER,
  `CODE` VARCHAR(6),
  `STATUS` VARCHAR(4)
);

INSERT INTO DATA_DISCOUNT_CODES
  (`ID`, `CODE`, `STATUS`)
VALUES
  ('1', '6f73js', 'USED'),
  ('2', 'kdw45s', NULL),
  ('3', 'and43d', NULL);

UPDATE CUSTOMER_DATABASE T1
INNER JOIN (SELECT `CUSTOMER ID`
, ROW_NUMBER() OVER ()  rown
FROM CUSTOMER_DATABASE WHERE `CUSTOMER CODE` IS NULL) T2 ON T1.`CUSTOMER ID` = T2.`CUSTOMER ID`
INNER JOIN (SELECT `CODE`
, ROW_NUMBER() OVER ()  rown
FROM DATA_DISCOUNT_CODES
WHERE `STATUS` IS NULL) T3 ON T2.rown = T3.rown
INNER JOIN DATA_DISCOUNT_CODES T4 ON T3.`CODE` =  T4.`CODE`
SET T1.`CUSTOMER CODE` = T4.`CODE`,
      T4.`STATUS` = 'USED'

✓

SELECT * FROM CUSTOMER_DATABASE;

CUSTOMER ID | CUSTOMER CODE
----------: | :------------
          1 | 6f73js
          2 | kdw45s
          3 | and43d

SELECT * FROM DATA_DISCOUNT_CODES

ID | CODE   | STATUS
-: | :----- | :-----
 1 | 6f73js | USED
 2 | kdw45s | USED
 3 | and43d | USED

db<>fiddle here


Why are Oracle processes only using half the SGA set?

set SGA_max_size=30g (size is greater then sga_target value)..
you must set SGA_target=16777216k or 16384m or 16g

Now instance will allocate up to 16g
you can alter sga_target upto 30g
with out shut db..


Shrinking is bad for performance and is a waste of time since it's just going to grow again. You need to add more disk space to accommodate the backups that you need. If you don't care about point-in-time recovery for the database, which it sounds like may be the case since there isn't enough storage even for a LOG backup, switch your recovery model to SIMPLE. With SIMPLE, you won't need LOG backups. But which recovery model you need is dependent upon your business needs of the database.


The limit is only going to help with exactly 100 so it is probably not worth the overhead.

If you put an index on the hash then it should know to stop (and where to start).


Find Underlying Data Type (int - integer) of Oracle Table instead of number

According the ANSI, DB2, and SQL/DS Data Types:

  SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.

INT, INTEGER and SMALLINT are all mapped to the Oracle datatype NUMBER(38). Since that's what is stored in the dictionary, there's no way to recover the type as specified in the original query.

If you're not using comments on your columns for other purposes, you could store that information there when you create your tables, and retrieve it via the ALL_COL_COMMENTS (or similar) view.


Pattern matching and substring replacement in PostgreSQL

You haven't disclosed what to do with whitespaces or what to do with overlapping substrings.  Anyhow, something like this could help:

UPDATE tableA
SET field1 = replace(replace(field1, field2, ''), field3, '')
WHERE replace(replace(field1, field2, ''), field3, '') <> '';

There is no pattern to match, simply replace a string (in an other column) with nothing.  You don't have to care about substrings as if the value of field2 does not occur in field1 then it won't be replaced.

See this on SQLFiddle.


Should you create separate tables for fixed options for a column?

If I interpret your question correct. I think you have at least 4 alternatives.

ENUM https://www.postgresql.org/docs/current/datatype-enum.html
Table lookup
Local lookup
Plain Text

Personally I prefer table lookup with id and name. You store only the ID. So you have to join tables when fetching. It is easier to add or delete options. And it is easier to search for status 1. And the footprint will be smaller.


Match two distinct values from a single column in a joined table

Select c.class_id, c.class_name
  From class c, class_schedule cs
  Where c.class_id = cs.class_id
    And cs.student_id in (
          select student_id from student
            where student_name in ('studentA','studentB'))
  Group by c.class_id, c.class_name
  Having count(*) = 2

Check it out: DB Fiddle


How to create a Table in Oracle which has a variable of type number?

You could use a "code", whereby the first two digits represent your letter 'D' = X or 'S' = Y (X and Y are integers) and the rest of your number could be your numerical part - that would mean a lot of parsing though and my scheme won't work for more than 1 letter (without complex coding).

Why not put the letter in a separate column? What is your actual business requirement? 0-padding is out because 0-padded numbers appear to have to be VARCHARs - which is not what you want as I understand it.

In fact, you'll have to start with 'K' = 10 (then 'Z' = 36, A = 37) &c., because if you start with 'A' = 10, then what's 'K' going to be? Again, why do you want to do this? You could also have 'A' = 10, 'B' = 11... 'Z' = 36. If you only have a couple of letters in your system, I would use a small lookup table for the code and have a pure number in a INTEGER column.

What's wrong with parsing VARCHARs? You'll be parsing your numbers anyway. Personally,  I'd go with a lookup table and separate field or a VARCHAR. But, I may have given you a couple of ideas? P.s. welcome to the forum!


SQL Server AG - Is there any case for un-clustering prior to certain operations?

Yes they are correct it will increase the execution time. As the comments state though there is no measurable way to determine this unless you have a matched test/stage environment and try it both ways (which would also be a best guess since you cannot know latency and bandwidth of your network and the load on the nodes).

I see this as the company asking you to trade HA and your time (adding the DB back to the AG) to save some indeterminate, likely small, overhead for their scripts to run faster. They better have a pretty good reason to trade that.


How to convert the column sum-function-total to dollars in Oracle SQL?

This was a simple-syntax issue, which I unable to see due to my growing frustration-thank you.

SELECT TO_CHAR(sum(invoice_total),'$999,999.99') AS "Total Invoice Amount"
FROM company.invoices;


I'll answer in two parts: first "why the traditional answer about separating sequential and random often doesn't apply."

Then I'll discuss the potential benefits of separating files at the Windows physicaldisk, and to adding additional vHBAs and distributing the physicaldisks among them.

Expecting benefit from separating random and sequential disk IO at the Windows physicaldisk level typically assumes HDD devices for the data storage.  It also typically assumes that separate Windows physicaldisks means separate HDD devices.  The idea is that some set of HDDs are handling mainly sequential disk IO and have very limited disk head movement (eg the HDDs hosting a single busy txlog*) while a separate set of HDDs are handling random disk IO.

Those assumptions rarely hold today - especially in a VM.  First of all, unless the VMs Windows physicaldisks are RDMs, multiple of the could be in a single datastore - or maybe multiple datastores are on a single ESXi host LUN.  So what is separated in the guest can be commingled at the ESXi host level.

But let's say that RDMs are used, or that each guest physicaldisk is on its own datastore, on its own ESXi LUN.  Even then, separate sequential from random io in the guest is often commingled at the array, because the LUNs presented to the ESXi host may be from the same single pool of disk devices.  Almost every storage array does this now - either exclusively or as an option to ease management and increase array efficiency/resource utilization.

Finally, so much storage today is either all flash or hybrid flash+HDD.  With no head movement to worry about, flash doesn't care about separation of sequential for random… doesn't even care about IO weaving.

So… those are all the reasons separating sequential from random may not be all that beneficial. Next why spreading files across physicaldisks and spreading physicaldisks across vHBAs can
still boost performance anyway.

*I purposefully mentioned a single transaction log in this HDD example.  When several separate sequential disk IO streams (eg 8 busy transaction logs) are taking place on the same HDDs - unless somehow almost all of the activity is within SAN cache - constant head movement among the sequential IO tracks leads to IO weaving.  That's a specific kind of disk head thrashing which leads to disk latency that is "worse than random".  Happens on RAID5 and RAID10, although RAID10 can tolerate just a little bit more variation in this regard than RAID5 before significant degradation.

Now - given that longwinded talk about how separating sequential from random might not help - how can spreading files across physicaldisks still help? How can spreading physicaldisks among vHBAs help?

It's all about disk IO queues.

Any Windows physicaldisk or LogicalDisk can have up to 255 outstanding disk IOs at a time in what is reported by perfmon as "Current Disk Queue".  From the outstanding disk IOs in the physicaldisk queue, storport can pass up to 254 to the minidriver.  But the minidriver may also have both a service queue (passed down to the next lower level) and a wait queue.  And storport can be told to lower the number it passes on from 254.

In a VMware Windows guest, the pvscsi driver has a default "device" queue depth of 64, where the device is a physicaldisk.  So although perfmon could show up to 255 disk IOs in "current disk queue length" for a single physicaldisk, only up to 64 of them would be passed to the next level at a time (unless defaults are changed).

How many disk IOs can be outstanding to one busy transaction log at a time?  Well, transaction log writes can be up to 60kb in size.  During a high scale ETL, I'll often see every write to the txlog at 60kb.  The txlog writer can have up to 32 writes of 60kb outstanding to one txlog at a time.  So what if I've got a busy staging txlog and a busy dw txlog on the same physicaldisk, with default VMware settings?  If both txlogs are maxing out at 32 outstanding 60kb writes each, that physicaldisk is at its queue depth of 64.  Now… what if there are also flatfiles as an ETL source on the physicaldisk?  Well… between reads to the flatfiles and txlog writes, they'd have to use the wait queue in, because only 64 can get out at a time.  For databases with busy txlogs like that, whether physical server or virtual, I recommend the txlog on its own physicaldisk, with nothing else on the physicaldisk.  That prevents queueing at that level and also eliminates any concern with contents of multiple files interleaving(which is a much, much lesser concern these days).

How many disk IOs can be outstanding to a rowfile at a time(from SQL Server's perspective, not necessarily submitted to lower levels)? There isn't really a limit in SQL Server itself(that I've found, anyway).  But assuming the file is on a single Windows physicaldisk (I do not recommend using striped dynamic disks for SQL Server, that's a topic for another time), there is a limit.  It's the 255 I mentioned before.

With the magic of SQL Server readahead and asynchronous IO, I've seen 4 concurrent queries each running in serial drive a total "current disk queue length" of over 1200!  Because of the 255 limit, that isn't even possible with all rowfile contents on a single physicaldisk.  It was against a primary filegroup with 8 files, each on own physicaldisk.

So readahead reads can be very aggressive, and can stress IO queues.  They can be so aggressive that other rowfile reads and writes end up waiting.  If transaction logs are on the same physicaldisk as rowfiles, during simultaneous readahead reads and txlog writes it's very easy for waiting to take place.  Even if that waiting isn't at the "current disk queue length" level, it may be waiting at the device queue (64 by default with pvscsi).

Backup reads against rowfiles can also be aggressive, especially if buffercount has been tuned in order to maximize backup throughput.

There's one more SQL Server io type to be aware of when considering isolating txlogs: query spill to tempdb.  When query spill takes place, each spilling working writes to tempdb.  Got a lot of parallel workers all spilling at the same time?  That can be quite a write load.  Keeping a busy txlog and important rowfiles away from that can be really helpful :-)

Now, it is possible to change the default device queue depth for the pvscsi driver.  It defaults to 64, and can be set as high as 254 which is the most storport will pass on.  But be careful changing this.  I always recommend aligning the guest device queue depth with the underlying ESXi host LUN queue depth.  And setting ESXi host LUN queue depth per array best practices.
Using an EMC VNX? Host LUN queue depth should be 32.  Guest uses RDMs? Great.  Set guest pvscsi device queue depth to 32 so it's aligned with the ESXi host LUN queue depth.  EMC VMAX? Typically 64 at ESXi host level, 64 in guest.
Pure/Xtremio/IBM FlashSystem?  Sometimes host LUN queue depth will be set as high as 256!  Go ahead and set pvscsi device queue depth to 254 (Max possible) then.

Here's a link with instructions.
https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2053145

The link also talks about requestringpages - WhatAreThose??  They determine queue depth for the pvscsi adapter itself. Each page gives 32 slots in adapter queue depth.  By default, requestringpages is 8 for an adapter queue depth of 256.  It can be set as high as 32 for 1024 adapter queue depth slots.

Let's say everything is at default.  I've got 8 physicaldisks with rowfiles on them, and SQL Server is lightly busy.  There's an average of 32 "current disk queue length" across the 8, and none are higher than 64(everything fits in the various device service queues). Great - that gives 256 OIO. It fits in the device service queues, it fits in the adapter service queue so all 256 make it out of the guest to queues at the ESX host level.

But… if things get a little busier, so an average of 64 with some physical disks' queue as high as 128.  For those devices with more than 64 outstanding, the overage is in a wait queue.  If more than 256 is in the devices' service queue across the 8 physicaldisks, the overage there is in a wait queue until slots in the adapter service queue open up.

In that case, adding another pvscsi vHBA and spreading the physicaldisks between them doubles the total adapter queue depth to 512.  More io can be passed from guest to host at the same time.

Something similar could be achieved by staying at one pvscsi adapter and increasing requestringpages.  Going to 16 would yield 512 slots, and 32 yields 1024 slots.

When possible, I recommend going wide (adding adapters) before going deep (increasing adapter queue depth).  But… on many of the busiest systems, gotta do both: put 4 vHBAs on the guest, and increase requestringpages to 32.

There are lots of other considerations, too. Things like sioc and adaptive queue depth throttling if vmdks are used, configuration of multipathing, configuration of the ESXi adapter beyond LUN queue depth, etc.

But I don't want to overstay my welcome :-)

Lonny Niederstadt
@sqL_handLe


ODBC Driver for Salesforce: Access Linked Server Object in SSMS

I'm going to take a chance and suggest an answer but in this case without the specific product and setup, of course I'm unable to verify.  I believe what you will need to do is go into the Security settings of the Linked Server and specify a particular account to use to make the connection.  That is, create an account on the server you want to connect to, like SalesForceReader, with the corresponding privileges needed, and use that account and password in your Linked Server.


If you know which workstation belongs to which human, you can create a logon trigger that logs all instances of the specific shared user(s) over a period of time, and you can review the table to see which workstations are logging in. I would put the logging table in a stable place like msdb or a utility database if you have one.

USE msdb;
GO
CREATE TABLE dbo.ThatLoginLog
(
  EventDate datetime2 NOT NULL DEFAULT sysdatetime(),
  HostName sysname,
  IPAddress varchar(15)
);

Now, the logon trigger:

USE master;
GO
CREATE TRIGGER LogTheLogin
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
  IF ORIGINAL_LOGIN() = N'that login name'
  BEGIN
    INSERT msdb.dbo.ThatLoginLog(HostName, IPAddress)
      SELECT HOST_NAME(), client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;
  END
GO

Then, periodically, or as soon as you think you've given enough time in your business cycle for everyone to have logged in at least once:

SELECT HostName, IPAddress, COUNT(*), MAX(EventDate)
  FROM msdb.dbo.ThatLoginLog
  GROUP BY HostName, IPAddress;


Create table if not exist each time API called: bad practice?

So let's think forward a little. The application goes live, the tables are created and everything's lovely. Then, you have a new requirement and the tables must change. How do you code that? There's the CREATEs for environments where the code's never run, plus a bunch of ALTERs for existing environments. Now another requirement comes along. This time you have to migrate data for some reason or other. Now there are the CREATEs (for new environments), the ALTERs (for existing environments) and a whole bunch of DDL and migration logic. But wait - there's more. For really good business reasons you want the column holding the migrated data to have the same name as an existing column. How do you now tell if the database has been migrated or not? No longer do you have a metadata-only lookup. No, now you need a flag or version table to show what DDL has to run at each and every execution.

Yeah! You're a great success. Zillions of people want to use your service. Venture capitalists wheeling barrows of money to your door. Third-party organisations are begging to partner with you. The dev team's doubled (nay, trebled!) in size to handle the business opportunities and customisations. Oops. These all have to be coded in the API start-up code. Each. And. Every. One. You no longer have an API. You have the mother of all migration scripts with a fragment of business code tacked on. :sad-face:


Sql Server trace? Who is running it and how to stop it?

Run the SQL command below:

   select
          [Status] =
          case tr.[status]
                when 1 THEN 'Running'
                when 0 THEN 'Stopped'
          end
          ,[Default] =
                case tr.is_default
                      when 1 THEN 'System TRACE'
                      when 0 THEN 'User TRACE'
                end
           ,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')
          ,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')
          from sys.traces tr
                left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

Source:- https://mssqlwiki.com/2010/04/26/how-to-find-all-the-profiler-traces-running-on-my-sql-server/


This can happen when the server is under heavy load, or some part of Blitz is getting blocked.

Rather than jump to Activity Monitor, run sp_BlitzWho, which you should have also gotten with the First Responder Kit. Go ahead and update your question with the exact command you ran "and also selected the common settings in the script at the bottom" doesn't tell me exactly what you ran. - Erik Darling

Try using @CheckUserDatabaseObjects = 0 to skip what's inside the user databases altogether. Then, when it's taking over, say, 90 seconds, run sp_BlitzWho in another window to see what query is currently running. Then you can copy/paste that into your question to help track it down.

Also, try sp_BlitzFirst SinceStartup = 1, and copy/paste the top result sets into a spreadsheet (or take a screenshot) - that'll help us use the top wait stats to understand what's happening on the server. - Brent Ozar


How to design tables such that a person can work for many companies?

You'd need a many-to-many table that maps people to companies.  Something like (I'm guessing at your table definitions)

CREATE TABLE employment (
  person_id INTEGER REFERENCES person ( person_id ),
  company_id INTEGER REFERENCES company ( company_id ),
  CONSTRAINT pk_employment PRIMARY KEY ( person_id, company_id )
);


SQL Server 2014 Transactional Replication Partition Switching

We ended up coming up with a solution for this.  Basically, we had to manage the partition creation for both the publisher and subscriber databases/tables when we made a change.  We were loading data using SSIS and there were mechanics that were built in to create new partitions on the publisher, so we implemented the same logic to do the work on the subscriber too.

Of course, this can be difficult if you get into having multiple subscribers.  I suppose one solution would be to query the subscription details and dynamically perform the same ALTER PARTITION statement to any subscriber database.


Extracting data from extremely large csv files

Powershell to the rescue!

A CSV file is just a text file structured in a certain way. It doesn't have to open up in Excel, that's just what your default settings are configured to do at the moment. Use Powershell to protect yourself from nasty conversions that Excel does under the hood.

Assuming my.csv is saved in your c:\temp folder and looks like this when you open it in Notepad...

uuid,foo,bar
00000000-0000-0000-0000-000000000001,bing,bong
00000000-0000-0000-0000-000000000002,bork,björk
... lots more rows
00000000-0000-0000-0000-000000000001,blat,splunge

...then you can extract the duplicate values with the following script.

Set-Location C:\temp
$myCsv = Import-Csv ./my.csv
$dupes = ($myCsv | Group-Object uuid | Where-Object Count -gt 1).Name

$myDupes = $myCsv | Where-Object uuid -in $dupes
$myOkays = $myCsv | Where-Object uuid -notin $dupes

$myDupes | Export-Csv myDupes.csv
$myOkays | Export-Csv myOkays.csv

Hey presto! You've now got one

Your original file for comparison
A file containing all the duplicates
A file containing just the uniques records

You may want to do some more work to determine which of the duplicate records to keep and possibly append back into your "good" file, but the exact details of that are probably best left for another question. Possibly over on StackOverflow if you're gonna keep flexing those Powershell skillz.


INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56

You need to enable the PERFORMANCE_SCHEMA.

See the documentation for details:

  The Performance Schema is disabled by default. To enable it, start the
  server with the performance_schema variable enabled. For example, use
  these lines in your my.cnf file:

[mysqld]
performance_schema=ON

*Please check updated syntax from mysql website

Migrating to Performance Schema System and Status Variable Table has all the information you'd ever need on the matter.


Using different servers for front-end and back-end?

Depending on your server config it might.  It is usually recommended that you don't have anything else running on the SQL Server other than SQL Server so that you can dedicate all the memory to the SQL Server instance.  You may also need to do some index tuning to help solve the performance problem.