Restarting primary SQL Server instance with log shipping I have configured log shipping with 20 minutes for backup, copy and restore. If i need to restart my primary server. do I need to stop all jobs (backup, copy and restore)?

Please advise.

Regards
Arvind Mishra

It has happened in our case wherein we had to restart our secondary server or log shipping monitoring server as some patches were getting applied and we don't have clustering on them. Our log shipping job is configured to take backup at primary server every 15 minutes, copying and restore also at every 15 minutes however there is gap of 5 minutes in between each of them considering network latency. Only impact we had was one of the job which was scheduled during that window got skipped however no impact on log shipping as lsn number caught up itself on the next copy and restore.

Based on above experience, I don't expect any issue to happen in your log shipping even if you don't  disable any jobs as the backups will not be taken if primary is down , since remaining two jobs are on secondary server.  Log sequence number will catch up on next backup.

I hope this helps.


SQL Join Field and Content I have this MySQL Structure:

CREATE TABLE Fields (
ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ForUser VARCHAR(255) NOT NULL,
ForCategory VARCHAR(100) NOT NULL,
FieldName VARCHAR(100) NOT NULL
);
CREATE TABLE Content (
ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
ForUser VARCHAR(255) NOT NULL,
ForCategory VARCHAR(100) NOT NULL,
ForField VARCHAR(100) NOT NULL,
FieldContent VARCHAR(255) NOT NULL
);

Now I want to make SQL Query that list results. Query must list content from 'Fileds' and must list content from 'Content' for specific Field. That will look like this:

  FieldName > ID 1(Table:Fields)
  -Content > ID 1(Table:Content)
  -Content > ID 2(Table:Content)... FieldName > ID 2(Table:Fields)
  -Content > ID 1(Table:Content)
  -Content > ID 2(Table:Content)...

According with your last edit:

SELECT
    F.*,
    C.*
FROM db1.Fields AS F
JOIN db1.Content AS C ON (C.ID = F.ID AND C.ForUser = F.ForUser
                          AND C.ForCategory = F.ForCategory)
WHERE `the field you want to filter`=`the value you want to search`;

You can filter with Any field in Fields or Content.


Can I call SMO from pure T-SQL? I know how to script a stored procedure using PowerShell and SMO:

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
$srv.databases['MyDatabase'].StoredProcedures['MyProc'].TextBody

But since I want to use the procedure text within a query, I wonder whether I can call SMO in some way within pure T-SQL.

Must only run in development environment.

BTW: A function returning the definition of a stored procedure with a given name would solve my current problem but not answer my question.

Edit:

I want to find which procedures reference a given table when I use this query:

SELECT p.name --, definition
FROM sys.sql_modules m
join sys.objects p on m.object_id = p.object_id
where p.type = 'P'
and definition like '%SearchForThis%'

That is exactly the same information I want to get using SMO.

You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.

It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.

This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.

One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.

And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.


SQL Server - is it ok to store link table as a heap? I'm trying to understand if it's a good practice to have non-clustered PK on link table:

CREATE TABLE table1_table2(table1_id INT NOT NULL, table2_id INT NOT NULL,
CONSTRAINT PK_T1_T2 PRIMARY KEY NONCLUSTERED(table1_id, table2_id),
CONSTRAINT FK_T1_T2_T1ID FOREIGN KEY(table1_id) REFERENCES table1(id),
CONSTRAINT FK_T1_T2_T2ID FOREIGN KEY(table2_id) REFERENCES table2(id)
);
CREATE INDEX IDX_T1_T2 ON table1_table2 (table2_id);

My point here that heap is ok because

No particular order during INSERT, so in case of clustered PK I'll have many page splits.
All SELECTs will use just index anyway

In my understanding, adding identity clustered PK will add more overhead for such tables (absolutely no data except foreign keys).

Could you please clarify if that makes sense ?

Thanks.

I agree that there is no point in creating an IDENTITY column clustered index here but I would just create the composite PK as CLUSTERED rather than NONCLUSTERED.

There is no point having 2 copies of the data, One in the heap that never gets used and one in the NCI. In fact it is downright counter productive as shown below.

Your NCI will still get page splits anyway if you are not inserting into the table in order of primary key so you have not avoided the problem. You might also consider a UNIQUE constraint or index on the reversed key order as well depending upon what queries you run against that table.

In general NCIs can be slightly be more compact than the equivalent NCI as they do not have the status bits B or NULL_BITMAP (if no nullable columns) but in this case this is more than outweighed by the necessity to store the 8 byte RID pointer to the row in the heap as can be seen from the below.

Script

CREATE TABLE T_NCI
  (
     table1_id INT NOT NULL,
     table2_id INT NOT NULL,
     CONSTRAINT NCI_PK PRIMARY KEY NONCLUSTERED(table1_id, table2_id)
  );

CREATE TABLE T_CI
  (
     table1_id INT NOT NULL,
     table2_id INT NOT NULL,
     CONSTRAINT CI_PK PRIMARY KEY CLUSTERED(table1_id, table2_id)
  );

INSERT INTO T_CI
OUTPUT inserted.* INTO T_NCI
SELECT DISTINCT number, number
FROM master..spt_values

SELECT LEFT(OBJECT_NAME(object_id),5) AS [Name],
       page_count,
       record_count,
       min_record_size_in_bytes,
       max_record_size_in_bytes
FROM   (SELECT *
        FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T_CI'), NULL, NULL, 'DETAILED')
        UNION ALL
        SELECT *
        FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T_NCI'), NULL, NULL, 'DETAILED')
) T

DROP TABLE T_NCI
DROP TABLE T_CI

Results

Name  page_count           record_count         min_record_size_in_bytes max_record_size_in_bytes
----- -------------------- -------------------- ------------------------ ------------------------
T_CI  5                    2163                 15                       15
T_CI  1                    5                    15                       15
T_NCI 5                    2163                 15                       15
T_NCI 6                    2163                 17                       17
T_NCI 1                    6                    15                       15

The record length for the non leaf pages in both the CI and the NCI is 15 bytes (1 byte status bits, 8 bytes for the composite key and 6 for the down page pointer) but for the leaf page NCI the rows take up 17 bytes (1 byte status bits, 8 bytes for the composite key and 8 for the row pointer) compared to 15 for the CI (2 bytes status bits, 1 byte column count offset, 8 bytes data, 2 bytes column count, 1 byte null bitmap). And as well as this less compact index structure you also have all the additional pages for the heap itself on top.


communication followup database design I'm trying to decide on how to set up the tables to get this use working without over-complicating it or not collecting enough data.

For the sake of an example, I've got a ticketing system with two users, a manager and a worker.

A ticket comes in, and the manager assigns it to a worker.
The manager also can set when they expect the the ticket to meet any particular milestones.
The worker updates ticket status whenever the status changes.
If the worker does not update the ticket to the status that the manager defined by the set date, the manager is notified (and the worker gets a mild electric shock).

The goal is to generate notifications when a status update does not happen by the set deadline. The second part is to be able to gauge performance, so if the manager keeps updating the date expected update (for a set status), the data is there to generate such a report.

data I'm trying to track

Ticket
User
Current Status
Previous Statuses
Expected update
Previous Expected updates

the list of statuses is likely to change, so using multiple columns won't work

I could store all of these in one table, but I could lose history of status updates.

I'm looking at a setup where I have a table for a ticket, ticket_status and ticket_expected with 1:m relationships (1 being ticket).

I've also got the latest ticket and next expected update on the ticket to make lookups more straight forward.

Is there a better way to do this?

I'd say you should have DATETIME fields on the ticket_expected and ticket_status so that it's possible to see when the status of the ticket changed. This would allow you to track changes from start to end.

I would also recommend that use the same name for columns that are used to join tables. So the ticket table should have a ticket_id column that matches the column of the same name in the ticket_expected table. This is standard practice as it avoids a lot of confusion that can happen when writing and bug tracing queries.

Those two points are my only comment as the general design seems to meet your requirements.


PostgreSQL takes 99,9% I/O? Please, look at this screenshot of iotop tool output (Linux). There is long-running batch update process on table with 20+ million rows. Does it mean that postgres is limited by I/O disk performance?

To answer your question, we'd have to know your hardware and how to interpret what iotop is reporting.  Not exactly on topic in this forum.  Taking a giant guess, assuming you're using something like ordinary SATA hardware, the answer would be No.  A single SATA drive is capable of something like 100 MB/s; you're seeing (on the first line) about 5% of that.  Before I'd credit the disk, I'd have to explain the other 95%.

In approaching Database performance problems, it's always a good idea to start with the DBMS itself, especially physical table design.  Work from the top down: DDL, DML, triggers, indexes, partitioning.  Once you're sure you have the DBMS's house in order, you can look at what the DBMS demands from the OS.  You can look at the depth of the I/O queues, I/O operation/second and how memory is being used.  More RAM is hardly ever harmful; a DBMS can use RAM to avoid I/O.

The reason to work this way is technical, not political.  Errors (or suboptimal choices) at the high levels can drive orders of magnitude in performance.  One index can mean the difference between O(n^2) and O(log n) performance, a matter hours versus seconds.  The further down you go, the harder it is to measure performance, and the more expensive to rectify.  Not to mention that any new machine capacity can quickly be absorbed by new bad SQL.


App domain crashed due to not enough storage available to process this command We are running SQL server SQL 2014 12.0.2000.8 32 bit running on Windows 2008 server 32 bit.

This server is also part of T-Replication as a publisher and also distributor.

The server has 16 GB of RAM and has allocated Max Memory 2147483647 MB (Default setting) to SQL server.

Since yesterday I am getting the below error whenever start the SSMS. To solve it, I restarted SQL Server Service, then I am able to login to SQL Server, but after a few hours it happens again.

Please provide me help with any idea where to look to trouble shoot the issue.

This is usually down to memory pressure, I assume that you're running SSMS on the SQL server?  Try opening SSMS from another location and connect in, does this still produce the error?

You might want to set your max memory setting to allow some breathing space for the OS and other apps.  I usually leave the OS 4GB to start with and then tune from there if I need to.


postgresql - transaction log - versioning? Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least say  with in the last 2 days) would be very helpful. I am not a database administrator but I am developing an application that needs some fault tolerance. If it is possible can you point me in the right direction for information? If is not possible what are my options?

you should setup point in time recovery. take a look,
http://www.postgresql.org/docs/current/static/continuous-archiving.html


Postgres 10 and malformatted array literal when using EXECUTE I am trying to create a function on Postgres 10 that would check whether all the given ids exist
in the target table.

CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"(
  _ids TEXT[],
  _column_name TEXT,
  _table_name TEXT,
  _schema_name TEXT DEFAULT 'public'
)
    RETURNS BOOLEAN AS
$$
DECLARE
  result TEXT[] := '{}';
  _query TEXT := '';
BEGIN
  IF _ids IS NULL THEN
      RETURN false;
  END IF;

  _query := CONCAT('SELECT UNNEST($1) EXCEPT SELECT "', _column_name, '" FROM "', _schema_name ,'"."', _table_name ,'"');
  EXECUTE _query INTO result USING $1;
  RAISE NOTICE 'Executed';

  IF array_length(result, 1) > 0 THEN
      RETURN true;
  END IF;

  RETURN false;
END;
$$
LANGUAGE plpgsql
    STABLE
    SECURITY INVOKER;

This is my function that returns correctly true if all the ids exist, but it throws an exception if any of
the ids does not exist.

CREATE TABLE "public"."categories" ("category_id" TEXT NOT NULL PRIMARY KEY);
INSERT INTO "public"."categories" ("category_id") VALUES ('foo');
INSERT INTO "public"."categories" ("category_id") VALUES ('bar');

This works

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo'], 'category_id', 'categories', 'public');
NOTICE:  Executed
 has_mismatching_ids
---------------------
 f
(1 row)

but this fails to even

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo2'], 'category_id', 'categories', 'public');
ERROR:  malformed array literal: "foo2"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function public.has_mismatching_ids(text[],text,text,text) line 14 at EXECUTE

and strangely this works also if I write a function that uses explicit names and without EXECUTE

SELECT UNNEST(ARRAY['foo', 'foo3']) EXCEPT SELECT "category_id" FROM "public"."categories";
unnest
--------
 foo3
(1 row)

Why doesn't my dynamic function work when comparing to values that do not exist in the target table?

I think your function does not work because it does USING $1 instead of the parameter name (USING _ids). Also, the concatenation is better replaced with the format function. And you can use one query instead of array_length trick.

This should work:

CREATE OR REPLACE FUNCTION "public"."has_any_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS $$
/* Checks whether any given id exists in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT EXISTS(
            SELECT 1 FROM %I.%I t WHERE t.%I = ANY(%L::text[]))',
        _schema_name, _table_name, _column_name, _ids);
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;

CREATE OR REPLACE FUNCTION "public"."has_all_ids"(
  _ids TEXT[],
  _column_name name,
  _table_name name,
  _schema_name name DEFAULT 'public'
)
RETURNS BOOLEAN
STABLE
LANGUAGE plpgsql AS
$$
/* Checks whether all given ids exist in the target table. */
DECLARE
    _query TEXT;
    _result BOOLEAN;
BEGIN
    _query := format(
        'SELECT NOT EXISTS(
            (SELECT u FROM unnest(%L::text[]) u)
            EXCEPT
            (SELECT t.%I::text FROM %I.%I t WHERE t.%I = ANY(%L::text[]))
        )',
        _ids, _column_name, _schema_name, _table_name, _column_name, _ids
    );
    EXECUTE _query INTO _result;
    RETURN _result;
END;
$$;


How to know if a particular DW project is using Kimball methodology When I look at the data warehouse/BI/ETL processes in my group, how can I tell if we are using Kimball methodology or not?

If I use Slowly Changing Dimensions i.e expire the old record and create a new record, and are also using a Star Schema, does it automatically mean we are using Kimball?

As I'm only familiar with Kimball approach and my Inmon book gathers dusk, unopened and unloved, I'm going to cite this article which indicates the difference between a Kimball model for a slowly changed dimension and an Inmon would be the design.

  The common way to track changes in an Inmon or 3rd Normal Form data warehouse is to use the Natural/Business Key combined with a start and end date

The Kimball approach would be to use a surrogate key instead of the natural/business keys.


SELECT Parent-Child with SORT I have a simple, one level parent child relation table, with following columns:

ID_Asset| Parent_ID_Asset | ProductTitle

I need output grouped by Parent followed by children, and also sorted by Parent and Children Name. My attempts in the fiddle. The parent must be first.

See here for details: https://rextester.com/PPCHG20007

CREATE TABLE [dbo].[Test](
    [ID_Asset] [int] NOT NULL,
    [Parent_ID_Asset] [int] NULL,
    [ProductTitle] [nvarchar](150) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (2, NULL, N'Live Maps Unity')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (3, NULL, N'mShare')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (4, NULL, N'Nessus Professional')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (5, NULL, N'Enterprise Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (6, NULL, N'PhantomPDF')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (7, NULL, N'Sharegate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (8, NULL, N'ADONIS Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (9, NULL, N'Automated Intelligence AI Compliance Extender & AI Syncpoint')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (10, NULL, N'Agility BridgeChecker')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (11, NULL, N'Office Timeline')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (12, NULL, N'ThinkBuzan iMindMap 8 Ultimate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (13, NULL, N'Total Management Suite')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (14, NULL, N'Webex Business Messaging and Advanced Meetings')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (16, 8, N'ADONIS Designer')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (20, 8, N'ADONIS Portal Module “Control & Release” Package XS')
GO

SELECT
    *
FROM
    Test
ORDER BY (
    CASE WHEN Parent_ID_Asset is null then ID_Asset else Parent_ID_Asset end), ProductTitle
GO

The desired output is (the relevant part):

ID_Asset   Parent_ID_Asset    ProductTitle
--------   ---------------    ---------------------------------------------------
   8            NULL          ADONIS Server
  16             8            ADONIS Designer
  20             8            ADONIS Portal Module “Control & Release” Package XS

Parent on top, followed by children in alphabetical order. The parent elements (with null in Parent_ID_Asset) must be also ordered alphabetically.

You can get it by using CASE WHEN in ORDER BY clause:

Note: I've added COALESCE(Parent_ID_Asset, '') just to get NULL Parent_ID in first place, you could replace it by CASE WHEN Parent_ID_Asset IS NULL THEN 0 ELSE 1 END

SELECT
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM
    Test
ORDER BY
    CASE WHEN Parent_ID_Asset IS NULL THEN ID_Asset ELSE Parent_ID_Asset END,
    COALESCE(Parent_ID_Asset, ''),
    ProductTitle;
GO

| ID_Asset | Parent_ID_Asset | ProductTitle                                                 |
|----------|-----------------|--------------------------------------------------------------|
| 2        | NULL            | Live Maps Unity                                              |
| 3        | NULL            | mShare                                                       |
| 4        | NULL            | Nessus Professional                                          |
| 5        | NULL            | Enterprise Server                                            |
| 6        | NULL            | PhantomPDF                                                   |
| 7        | NULL            | Sharegate                                                    |
| 8        | NULL            | ADONIS Server                                                |
| 16       | 8               | ADONIS Designer                                              |
| 20       | 8               | ADONIS Portal Module “Control & Release” Package XS          |
| 9        | NULL            | Automated Intelligence AI Compliance Extender & AI Syncpoint |
| 10       | NULL            | Agility BridgeChecker                                        |
| 11       | NULL            | Office Timeline                                              |
| 12       | NULL            | ThinkBuzan iMindMap 8 Ultimate                               |
| 13       | NULL            | Total Management Suite                                       |
| 14       | NULL            | Webex Business Messaging and Advanced Meetings               |

Rextester here

UPDATE

It seems you need the result ordered somehow by ProductTitle, you can use this query: (Keep in mind you're adding an extra job that maybe could be done at presentation layer.)

SELECT
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM
    Test
ORDER BY
    MAX(CASE WHEN Parent_ID_Asset IS NULL THEN ProductTitle ELSE '' END)
        OVER (PARTITION BY COALESCE(Parent_ID_Asset, ID_Asset)),
    COALESCE(Parent_ID_Asset, ID_Asset),
    Parent_ID_Asset,
    ProductTitle;

ID_Asset | Parent_ID_Asset | ProductTitle
-------: | --------------: | :-----------------------------------------------------------
       8 |            null | ADONIS Server
      16 |               8 | ADONIS Designer
      20 |               8 | ADONIS Portal Module “Control &amp; Release” Package XS
      10 |            null | Agility BridgeChecker
       9 |            null | Automated Intelligence AI Compliance Extender &amp; AI Syncpoint
       5 |            null | Enterprise Server
       2 |            null | Live Maps Unity
       3 |            null | mShare
       4 |            null | Nessus Professional
      11 |            null | Office Timeline
       6 |            null | PhantomPDF
       7 |            null | Sharegate
      12 |            null | ThinkBuzan iMindMap 8 Ultimate
      13 |            null | Total Management Suite
      14 |            null | Webex Business Messaging and Advanced Meetings

db<>fiddle here


Code creates different plan when ran ad-hoc vs. in a stored procedure I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

SOLUTION: I ended up getting the plan I wanted by using the OPTIMIZE FOR UNKNOWN hint.

Usual suspects:

constants in adhoc, parameters in code
mismatch of data types in code
parameter sniffing

Point 1: the optimiser can choose the best plan for the constants.
Change the constants = change the plan. A parametrised plen is resuable

Point 2 will introduce implicit conversions because of datatype precedence
eg varchar column compared to nvarchar parameter

Point 3: use parameter masking or OPTIMISE FOR UNKNOWN
Edit: To test: run stored proc, run sp_updatestats, run again. This will invalidate cached plans which is better then clearing plan cache

Edit: after jcolebrand's comment

You can disable sniffing several ways. The main 3 are

RECOMPILE. This is silly IMO.
OPTIMIZE (sic) FOR UNKNOWN
Parameter masking

Parameter masking:

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

Masking and the OPTIMIZE hint have the same effect (maybe for different reasons). That is, the optimiser has to use statistics and data distribution (Note: still under testing by Mark Storey-Smith) evaluate the parameters on their own merits ? , rather than what they were last call. The optimiser can recompile or not. SQL Server 2005 added statement level recompilation so there was less impact

Now, why a plan with "sniffed" parameters is "sticky" compared to masked/"unknown" parameters, I'm not sure.

I've used parameter masking since SQL Server 2000 for all but the simplest code. I've noted that it is liable to happen with more complex code. And at my old job I has some report procs that I could change the plan parameter defaults. I reckon the "cargo cult" approach was easier than a support call.

Edit 2, 12 Oct 2011, after some chat

Parameter masking and OPTIMISE FOR UNKNOWN have the same effect as far as I can tell
The hint is cleaner than masking but was added with SQL Server 2008.
Parameter sniffing happens at compile time.
WITH RECOMPILE generates a new plan each execution. This means a poor choice of defaults will influence the plan. At my last job, I could demonstrate this easily with some report code: changing parameter defaults altered the plan regardless of supplied parameters.
This MS Connect article is interesting: Suboptimal index usage within stored procedure (mentioned in one of the SO answers below)
Bob Beauchemin mentions it too

Outstanding issues

Does sniffing still apply with WITH RECOMPILE? That is, if the optimiser knows to discard the plan does it aim for re-use?
Why are sniffed plans "sticky"?

Links from SO:

https://stackoverflow.com/q/6919166/27535
https://stackoverflow.com/q/5236631/27535
https://stackoverflow.com/q/1392307/27535
https://stackoverflow.com/q/2905440/27535
https://stackoverflow.com/q/467513/27535
https://stackoverflow.com/q/272726/27535
Remus Rusanu (MS SQL team) and I had a bunfight here on SO
Whitepaper on MSDN in conjunction with my SO analysis: https://stackoverflow.com/questions/414336/why-does-the-sqlserver-optimizer-get-so-confused-with-parameters/414368#414368


Is it a good idea from a performance perspective to store audio files in a mysql database? I have an application where I have audio files. I need to store them in a mysql table since we already have a mysql database available.

I saw that some people don't advise to do so.
What are the advantages and inconvenients of storing the audio files in the table?

Does it affect the operations performed on the database?

MySQL/InnoDB is a relational database management system which works best when a significant part of its data is on memory for fast retrieval. Traditionally, it has been said that you should keep only small data (integers, floats, smallish pieces of text) on MySQL and use the filesystem, or a dedicated blob/file storage for extreme cases. The reasons are several:

Metadata and small data can be effectively indexed on a MySQL a table and accessed and searched quickly, but huge amount of text or binary data cannot (even fulltext indexes are limited in capabilities, and other string search engines are recommended)
For a long time, small updates on large blobs meant that the whole field was updated and duplicated, even if a single bit was updated
Large blobs tend to be stored off-row, losing some of the advantages of in-row storage
In general, metadata tends to require very fast access (e.g. search a particular row among millions) while retrieving a large file is usually more lenient on latency
MySQL/InnoDB caching system is not optimized for large IO/operations, making it potentially less efficient than the OS caching system. You want probably to save your buffer pool (MySQL application cache) for in-memory metadata retrieval, and don't cache or cache only once on the OS large files in a more simplistic way (different access patterns)
Normally, MySQL query results are handled in-memory on the application, which is fast and nice for KB or results, but it would be highly inefficient for MB of files, which if they were stored separately of the filesystem or a dedicated blob storage, it would not affect the application's memory
MySQL/InnoDB in general is optimized for small transactions, and you may find latency problems on IO operations and replication for very large transactions. That is even more true for distributed and clustered topologies. The need for ACID properties on files is usually non-existent of very low (files are normally added to the storage and have very low consistency needs)
Backing up MySQL gets more and more difficult the more data you have due to consistency requirement. Backing up a directory of files is usually trivial.

For all those reasons and some more I may be forgetting, it is a common advice to store on the database only a reference of the files (e.g. name, path, hash) and store the actual files separately. You can break the rules, no answer is absolute (e.g. you will not hit a scalability problem with 10MB of small files), but that is the reason why filesystem/hadoop/ceph/S3/etc. are chosen and why files are cached/dealt with on a separate pipeline, to allow for future scalability.

Regarding some advantages:

probably it will be a simpler setup (less technologies, less code, and less logic to handle distributed files- as that is all handled transparently by the db)
It will be simpler to maintain consistency between data and metadata, as a single agent will be used to control both; not only for the application, also for things like backups
Even if MySQL may not be the best technology, it may be better to keep it there than on the filesystem if you are handling millions or billions of small files, e.g. on a single directory


Query to get duplicate records then update it by putting a sequence number I was trying to get all records with duplicate data and update it by putting a sequence number.

Let's say i have this data:

+-------------------------------------------+
| first_name | last_name | sequence_number  |
+-------------------------------------------+
| John       | Smith     | NULL             |
| John       | Smith     | NULL             |
| Jane       | Doe       | NULL             |
| Jane       | Doe       | NULL             |
| Jane       | Doe       | NULL             |
| Rock       | Lee       | NULL             |
+-------------------------------------------+

and my expected output is like this:

+-------------------------------------------+
| first_name | last_name | sequence_number  |
+-------------------------------------------+
| John       | Smith     | 1                |
| John       | Smith     | 2                |
| Jane       | Doe       | 1                |
| Jane       | Doe       | 2                |
| Jane       | Doe       | 3                |
| Rock       | Lee       | NULL             |
+-------------------------------------------+

create table data
(
  first_name varchar2(20 char),
  last_name varchar2(20 char),
  sequence_number number(10, 0)
);

insert into data values ('John', 'Smith', null);
insert into data values ('John', 'Smith', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Jane', 'Doe', null);
insert into data values ('Rock', 'Lee', null);
commit;

select * from data;

FIRST_NAME           LAST_NAME            SEQUENCE_NUMBER
-------------------- -------------------- ---------------
John                 Smith
John                 Smith
Jane                 Doe
Jane                 Doe
Jane                 Doe
Rock                 Lee

--

select
  first_name, last_name, rowid,
  row_number() over (partition by first_name, last_name order by rowid) as sequence_number
from data
  where (first_name, last_name) in
  (select first_name, last_name from
    data group by first_name, last_name having count(*) > 1
  )
;

FIRST_NAME           LAST_NAME            ROWID              SEQUENCE_NUMBER
-------------------- -------------------- ------------------ ---------------
Jane                 Doe                  AAAJ7hAAEAAAAFjAAC               1
Jane                 Doe                  AAAJ7hAAEAAAAFjAAD               2
Jane                 Doe                  AAAJ7hAAEAAAAFjAAE               3
John                 Smith                AAAJ7hAAEAAAAFjAAA               1
John                 Smith                AAAJ7hAAEAAAAFjAAB               2

--

merge into data using
(select
  first_name, last_name, rowid,
  row_number() over (partition by first_name, last_name order by rowid) as sequence_number
from data
  where (first_name, last_name) in
  (select first_name, last_name from
    data group by first_name, last_name having count(*) > 1
  )
) data_with_sequences
on (data.rowid = data_with_sequences.rowid)
when matched then update set data.sequence_number = data_with_sequences.sequence_number;

5 rows merged.

--

commit;
select * from data;

FIRST_NAME           LAST_NAME            SEQUENCE_NUMBER
-------------------- -------------------- ---------------
John                 Smith                              1
John                 Smith                              2
Jane                 Doe                                1
Jane                 Doe                                2
Jane                 Doe                                3
Rock                 Lee


Using pivot on datetime I have the following table:

Date,Type
2012-01-25 13:48:00.000,1
2012-01-25 13:49:00.000,2
2012-01-26 13:48:00.000,1
2012-01-26 13:49:00.000,2

I tried the pivot function to get this layout:

1,2
2012-01-25 13:48:00.000,2012-01-25 13:49:00.000
2012-01-26 13:48:00.000,2012-01-26 13:49:00.000

I'm not sure if it's even possible to use the pivot function, because I have to use an aggregate function in pivot, right? Is it possible to use pivot or should i use an self join?

Best regards
Stefan

EDIT:

This is my pivot statement:

SELECT
  [1],
  [2]

  FROM
  (
    SELECT
      [Date],
      [Type]

      FROM
        [Test]
  ) AS SourceTable
  PIVOT
  (
    SUM([Date]) FOR [Type] IN ([1],[2])
  ) AS PivotTable;

I'd go with the classic Cross-Tab approach together with ROW_NUMBER to have "something" to group by:

;
WITH cte as
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Type ORDER By Data) as pos,
    *
    FROM -- use your table name instead of the VALUES construct
    (VALUES
    ('2012-01-25 13:48:00.000' ,1),
    ('2012-01-25 13:49:00.000',2),
    ('2012-01-26 13:48:00.000',1),
    ('2012-01-26 13:49:00.000',2)) x(Data,Type)
)
SELECT
MAX(CASE WHEN Type = 1 THEN Data ELSE NULL END) AS [1],
MAX(CASE WHEN Type = 2 THEN Data ELSE NULL END) AS [2]
FROM cte
GROUP BY pos


How to PIVOT/UNPIVOT if I don't know how many columns will it have? I have this simple query:

I want to list something like this:

  Unidade1 \ PLaca1 \ Placa2 \ Placa3

  Unidade2 \ Placa1 \ Placa2

But I don't know how many placas a unidade will have.

I was trying to pivot like this:

SELECT   [undiade], [placa1],   [placa2], ..
FROM   Table1
join table2
bla bla bla
PIVOT
(
       Unidade
       FOR [unidade] IN ([placa1], [placa2], [placa3])
) AS P

if there's something I can't understand is how to pivot.

EDIT1:

I tried this with no succes:

Select distinct Unidade,
    substring(
        (
            Select ','+unidade  AS [text()]
            From unidades.Unidades u
            join t1.Viat v
            on u.unidade = v.unidade_id
                where v.parametrizado=0
            ORDER BY 1
            For XML PATH ('')
        ), 2, 1000) [Unidades]
From unid.unids.unids u

Conversion failed when converting the varchar value 'field' to data type int.

As Julien mentioned, dyanamic sql will be your answer for this. Below is an example of using pivoting with an EAV system. It really helps with reading the data.

DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
DECLARE @ClassName NVARCHAR(50)
SET @ClassName = 'customer'

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(property_name)
                                FROM Entity_Property ep
                                INNER JOIN Entity e ON ep.eid = e.eid
                                INNER JOIN Class c ON c.cid = e.cid
                                WHERE c.class_name = @ClassName
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)')
                              ,1,1,'')

SET @Query = 'SELECT *
FROM
      (SELECT ep.eid, ep.property_name, ep.property_val FROM Entity_Property ep INNER JOIN Entity e ON e.eid = ep.eid INNER JOIN Class c ON c.cid = e.CID WHERE c.class_name = ''' + @ClassName + ''') as src
PIVOT
(
      MAX(Property_val)
      FOR property_name IN (' + @Cols + ')
) AS piv;'

EXEC(@Query)

- EDIT -

Here is the code that will do what you are looking for. I used a temp table as my source of information instead of the query you gave, so feel free to use that instead of #Table. The code below can be run stand alone to see what the output would look like, so with some tweaking, you can tie it to your tables.

-- Mimicing what we have for testing purposes
CREATE TABLE #Table (
    Unidade VARCHAR(100),
    Placa CHAR(8)
)

INSERT INTO #Table (Unidade, Placa) VALUES
('Divisao Administrativa', 'DJL-2937'),
('Instituto de Crimalistica', 'DJL-2690'),
('Instituto de Crimalistica', 'CDV-7883'),
('Instituto de Crimalistica', 'DJP-2304'),
('Instituto Medico Legal', 'DJL-2925'),
('Instituto Medico Legal', 'DJL-2689'),
('Instituto Medico Legal', 'DJL-2851'),
('Instituto Medico Legal', 'CMW-6316')

-- Using a temp table to hold a copy of our data with a new column for column headers
SELECT
        Unidade,
        'Placa' + CAST(ROW_NUMBER() OVER (PARTITION BY Unidade ORDER BY Placa) AS VARCHAR(10)) AS NewColumnHeader, -- If you have an order for numbering you want to use, add it to the above query and use it here
        Placa
INTO #TableWithNewColumnHeaders -- The 'dirty' way of creating a temp table.
FROM #Table

-- Now grab what all our new columns will be (Placa1, Placa2, etc)
DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(NewColumnHeader)
                           FROM #TableWithNewColumnHeaders
                           FOR XML PATH(''), TYPE
                           ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

-- Now we pivot our results
SET @Query = 'SELECT p.*
        FROM
       (SELECT Unidade, NewColumnHeader, Placa
        FROM #TableWithNewColumnHeaders)     AS SourceQuery
PIVOT
(
      MAX(Placa)
      FOR NewColumnHeader IN (' + @Cols + ')
) AS p'

EXEC(@Query)

The idea is, we create a new column that will become our new column header. This is what lets us get the column names we want. Once we do that, it's a simple pivot statement to turn the header values into our new column names.


In MySQL, does the order of the columns in a WHERE clause affect query performance,why? I have a query that doesn't use any indexes:

SELECT 32,
       guid,
       1,
       1,
       1,
       0,
       5
FROM   test
WHERE  level >= 20
       AND ( ( fun_GetIndexValue(data, 354) >> 16 ) +
             ( fun_GetIndexValue(data, 355) >> 16 ) +
             ( fun_GetIndexValue(data, 356) >> 16 ) +
             ( fun_GetIndexValue(data, 357) >> 16 ) +
             ( fun_GetIndexValue(data, 358) >> 16 ) +
             ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1;

The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?

Maybe because the results from the first "form" of the query are already cached ?
As lyxing said, post the execution plan. It will be interesting to see how the optimizer chooses the execution plan in both cases.


Risks of pg_start_backup() I want to know the risks of the pg_start_backup() control function.

For example, what will happen if someone resets the server, or if there is a power failure during pg_start_backup's operation?

What will happen if something goes wrong with the postgres process and it just gets out?

What will happen if the hard drive goes full with this option enabled?

First and most importantly, the great majority of users should simply use pg_basebackup. For those few it is unsuitable for, there are existing tools you should use instead of rolling your own, like PgBarman, WAL-E and OmniPITR.

If the server crashes mid-backup when using pg_basebackup you might need to remove the backup.label file from the data directory manually. It can result in some increased table bloat while it's running but is otherwise very safe.

Note that PostgreSQL 9.6 addresses the issue with the leftover backup.label file by providing a new API that returns the backup label file over the SQL connection. See the release wiki and docs. You should strongly prefer to use this mode when backing up 9.6 or newer.


Compare two servers on SQL query performance I need to compare SQL query speed on two servers, with the same databases, one is an Amazon RDS SQL Server instance and the other SQL server is on IBM Softlayer. What do I need to do and how do I go about doing it. I need to generate a report for the comparisons to management. I am a SQL novice. Thank you.

One of the best ways to compare two servers is to repeat tasks that you (hopefully) normally do on both:

Run DBCC CHECKDB
Take Full backups
Run any index or statistics maintenance

And of course, one of the best ways to measure the impact they have on your server is to look at wait stats. If those pass, then move on to comparing queries. You generally don't want to test query performance in a vacuum, because a whole lot of other stuff may not run the way you expect it to

I wrote a whitepaper for Google earlier this year on how to do this. It's free, and I suggest you give it a read. It walks through how to do all the stuff you're interested in. Even though it's done using Google's Cloud, it's applicable to any server comparison.


Representing a many-to-one relationship in a database diagram I have two tables loadingDetail, and stowageDetail:

loadingDetail           | stowageDetail
[PK] loadingID (int)    | [PK] StowageID (int)
[PK] transactionID (in) | [PK] room (int)
                        | [UNIQUE] transactionID (int)

transactionID in loadingDetail can be duplicated when the loadingID value is different.
transactionID in stowageDetail is UNIQUE.
many transactionID values in loadingDetail can have only one transactionID in stowageDetail.
transactionID in loadingDetail can be deleted when there is no transactionID in stowageDetail (No Action)

How I can represent that in a database diagram?

Additional information

I'm really looking for a database-diagram possible answer. But I tried to find something about many-to-one relationships in database diagrams and it seems can't find it. The relationship is like many Containers put inside Big Box. The Containers just can removed if the Big Box is already removed. The supported database diagram "I know" is one-to-many which in my condition does not fit because the Box is removed the first, then Containers the second.

transactionID in loadingDetail can be duplicated when the loadingID
  different

This is your requirement.Table composite key already handling this.

  transactionID in stowageDetail is UNIQUE

This is your requirement

  •many transactionID in loadingDetail just can have 1 transactionID in
  stowageDetail

This is your requirement.This can Unique index. Or it can be handle via code/trigger.
This can never be FK constraint.You cannot define partial FK constraint.

  •transactionID in loadingDetail can be deleted when there is no
  transactionID in stowageDetail (No Action)

Because you cannot define partial FK constraint. You have to handle this via code.**Instead of Trigger** is ideal for this scenario.

I assume you cannot define transactionID and loadingID as FK in stowageDetail table because of your requirement.

Read this

As per your requirement, your design is already ok.Adding any other candidate key is useless. you cannot do it via PK-FK constraint.


Query for a single unique record takes about 1.5 seconds I have a simple table with a 13 byte unique key (indexed unique, non-clustered) and about 6 other small fields of no more than 10 bytes each. The table has about 8 million records. I have rebuilt the index. No fragmentation of the index or the file on disk. I am doing a query to retrieve one of these rows. I am doing a loop say 1000 of these queries for records I know exist in the database and it takes about 25 minutes.  The code is in a c# program. It has the same response time whether I do it across our 1GB network or directly on the machine with the MS SQL server.  I have tried suggestions with (index) and nolock - nothing changes.  It wont let me do a forceseek, I get an error.
If I do the same query within the sql management express (or whatever it is called) it returns immediately, no perceptible delay.

So why would my c# program take soooo long.  Seems like it is the c# program more than the query itself.  Nothing fancy or unusual.

I know someone will want to see the code, and I can get that. For now I wondered if there is something known about the mssql c# libraries that is known to cause such slowness.  I have tried doing it a number of different ways. I do not open and close the connection with each query.  Is there something I missed about how I should build the connection string?

Basically I am at a loss.

I've seen some options in .NET be different than the default SSMS options. Specifically, ARITHABORT was ON in SSMS, but in .NET it was OFF (the default value in the DB was off, where I assume .NET was pulling this from). This caused SQL Server to choose a vastly different (and terrible) plan in the .NET code.

Can you verify the options (ANSI_NULLS, QUOTE_IDENTIFIER, ARITHABORT, etc.) are consistent? My guess is that they are not.

See Slow in the Application, Fast in SSMS? by Erland Sommarskog for more information.


Does mongodb automatically move small non-partitoned databases to spread read load? I have a few hundred small databases, each between 0.2GB and 4.0GB in size. These are sitting on a sharded mongodb environment with 10 or so shards.

At any one time, only a very small subset of these databases are being (intensively) written to.

All of them are being intensively read from, all of the time (Target OTE 300,000 queries per second). I can exert enough control over the read order to spread the reads across the databases fairly evenly.

Right now, none of these small databases are partitioned.

When I look at the output of the db.printShardingStatus() command, I see that most of the databases are sitting on shard0001. Indeed, mongostat shows that most of the reads are hitting that one machine.

I have (so far) done nothing to try to influence which db goes on which machine.

My question is this: Left to it's own devices, will mongodb automatically move the primary for these small databases so that the load (eventually) ends up being more balanced, or do I have to intervene in the process myself?

(Or should I try to partition these databases over multiple machines so that the index size is smaller on each shard, then re-sequence my reads so that I hit each database one at a time?)

Left to its own devices, no, MongoDB will not move those unsharded databases to a different primary shard - the automatic balancing only applies to chunks from sharded collections.

It will round robin through your shards as the databases are created to spread them out across all the shards from that perspective.  If you had one shard originally and expanded to many, the databases may have been concentrated on that shard - the round robin aspect only applies when you create the database, not the collections inside it.

Once the databases are created, and assuming you can predict what will be used and when, you can then move them to whatever shard you wish using the movePrimary command and distribute load accordingly:

http://www.mongodb.org/display/DOCS/movePrimary+Command

Naturally, this will be a quicker process if there is no data in the databases, but should still be possible after the fact.


Grouping data in many to many relationships to improve performance I have a "classic" SQL Server three table many-to-many in the form

PC -< [PCHasSoftware] >- Software

I am having issues with data volumes and performance in a very large database scenario, so am exploring a further redesign to improve performance and cut storage, etc.

Many PCs will have the same software titles. So can we cut storage by somehow reorganizing that into groups of titles. Then instead of saying

"this PC has these 16 titles" - needing 16 rows per PC

we have instead:

"this PC has this group of 16 titles" needing 16 rows somewhere and only 1 row per PC.

So if 10,000 PCs have these 16 software titles, I'm storing 10,016 rows instead of 160,000 rows.

Two initial thoughts on that idea.

Build software to identify the 8 most commonly occurring groups, and then retain 8 bit flags on the PC table, and have a template PC which holds what those titles are in the same structure
Organize into groups

PC -< [PCHasSoftwareGroup] >- [Software Group] -< Software

or (edit)

    PC -< [PCHasSoftwareGroup] >- [Software Group] -< GroupHasSoftware >- Software

The difficulties with these models I perceive are:

How to identify which groups are most popular. Probably a chunk of SQL operations that runs in a batch job overnight to "groupize" the standard mode. I wondered if any clever "dba+maths" people have come up with any algorithms that could do this
When one of those 16 titles is moved off a PC, you suddenly have to remove the group relationships and insert the 15 rows as non-grouped detail row, so potentially a lot of traffic for one update
How to combine this with software which does not fall into any logical grouping. I'm not a fan of using union because "SQL" only seems guaranteed to generate good query plans when you keep things like that out, but that doesn't mean I won't use it if its the best solution!
There will be some data that is specific to this PC, like DateInstalled, DateLastSeen etc etc. So we'll still have to store some data at the PC-software level, but this could still reduce data volumes compared to the data model described at the top of this post

This may have other applications, and I imagine it would have been done before. e.g. in an order entry system, if its very common for a certain combination of products to appear on orders, you could represent that as a group as a single order line in the database and expand it during querying.

I'm just kicking around ideas here and would appreciate all thoughts. I wasn't even sure what to google for but I've tried for the last week to find other examples of this and failed.

It may be worth looking at things from the other direction.

I suspect that you have a large number of PCs that are set up with exactly (or almost exactly) the same set of software. You may want to consider establishing a set of PC "profiles": a set of software that's going to be standard for hundreds (or even thousands) of PCs.

This has most of the same benefits and drawbacks as your current plan, but might impact more rows more easily. And, it might make certain kinds of changes easier as well: in many cases, a new/updated piece of software will ultimately be deployed to all the machines; you can create a new profile, and move PCs to it as the software change is applied.

Functionally, this would look much the same as what you're talking about; I simply think the perspective helps nail tings down a bit.

If you are going to create application groups, I would not try to generate groups based on what's on PCs today directly. It's useful information, but should be looked at carefully, to identify meaningful groupings, and not just "accidental" ones. It's better to create four different groups where each one actually contains software that has a logical reason to all be on the same machine together, than to create just two groups, each of which contains some things that may be found together, but not on purpose.

Finally, note that this sort of thing always involves a trade-off. You may get better performance because you're dealing with smaller tables, but your queries will involve more tables and/or more joins, and be more complicated. The end result might actually be worse performance.


Split date time, with only time, into minutes and seconds I'm creating an Access database from an excel file that contains metadata about vhs tapes and DVDs. The person who created the excel file had the running times as a date/time field in minutes:seconds. I would like to be able to create reports or set up forms/queries etc. where the user can search for minutes greater than such and such. I was thinking that the best way would be to create two fields: minutes and seconds. The date/time type is really for specifying chronological times, not running times.

What is the best way to do this? How can I split the minutes:seconds to create two columns?

I only just imported the Excel table into Access. Would it be easier to do it as a formula in Excel and then import to Access?

Store the running time as an integer in total seconds.  For display purposes you can convert the number of seconds into a string in hours:minutes:seconds format or whatever display format you choose.


WinSCP extract won't connect when run by SQL Server Agent Job Situation
We use WinSCP to receive daily csv files from a vendor which we then extract and load into our data warehouse.  To achieve this, we have a SQL Agent scheduled job that executes a process task that kicks off a batch file. This batch file then runs a FTP script that downloads the folders from WinSCP to a local drive which later goes through the ETL process.

Complication
Recently our vendor updated their FTP site.  We were required to update the session in WinSCP.  I updated the session and can log into the WinSCP session manually (by double-clicking .bat file from a local box) but when SQL server agent kicks off the batch process via executing process task I receive the following error.

Network error: Connection to "ftp.hostsite.com" timed out
The server rejected the SFTP connection, but it listens for FTP connections.
Did you want to use the FTP protocol instead of SFTP? Prefer using encryption.

Batch File

"\\sharedfolder\winscp.exe" compdata /console /script=\\sharedFolder\Data\App\DownloadFlatFile.ftp

DownloadFlatFile.ftp

get /home/*.csv \\sharedfile\Data\
mv /home/*.csv  \\sharedfile\Data\
exit```

Here is WinSCP session info.
File protocol: SFTP
Hostname: ftp.site.com
Port number: 22
Username: privateusername
Password: none

Then under advanced settings >> SSH >> Authentication I have the path to the ppk file

Turns out the issue was the firewall.  I figured out that with this setup, SQL server runs remotely and the server it runs off of had a firewall that was blocking the FTP site's IP address.  The network admins were able to whitelist the IP address, now everything runs correctly. Thanks to those that helped me work through the problem.


Best Approach for Mirroring Data from SQL Server to MySQL I need help in regards to replicating data from a third party point-of-sale system with a SQL Server backend to a MySQL DB.

Specifically, can anyone suggest a best way to mirror data from SQL Server on our MySQL instance?

Current process:

Select a dataset from the distant SQL Server and write to a text file using PHP.
Import the text file using LOAD INFILE using PHP.
Some data sets must be chunked into several iterations.

Drawbacks:

It is slow and cumbersome.
Requires polling their server every x minutes for changes.
Difficult to detect changes as most tables do not have an 'updated time' on the row to indicate a change.  We therefore have to sometimes assume we will need the whole table.
Tables are up to 11 million records (and growing).
Bandwidth is being used inefficiently.
Whenever the schema changes (short notice) on the SQL Server, we have to react to the changes and change every query.

Additional Circumstances:

Our third party SQL Server host will not allow any changes to the system on their end.
Our side (MySQL) is more flexible to system changes.
We cannot change to a SQL Server platform as we are a LAMP shop.
We are very capable IT developers, but this issue seems to warrant additional advice.
Management is against an organic point-of-sale.

Does the data actually need to be replicated in the same model?

What will you be doing on the MySQL side with the data?

I would strongly consider putting either another database or instance which you control on the SQL Server side to be able to take snapshots on that side so you pull less data down (comparing the snapshot to live data should be quick) and also look at what you want to do with the data on the MySQL side, since it is possible that you actually want to transform the data during the extraction to better facilitate the load.

I think you will find that having a sandbox database or instance which you do control collocated with the vendor database will give you a tremendous amount of power, while still allowing the vendor to operate without interference.

This can also help in reducing the bandwidth (unneeded columns, denormalizing usually increases data size, but not always) and reducing the load times or the complexity of the load process.  A different model on the MySQL side which corresponds to the queries you will actually be running can also improve performance of reporting or even the load.

Having views against the source data can also help mitigate against changes - although you might not be able to use DDL triggers and schemabinding to stop changes from breaking dependencies, you could possibly catch schema changes when the extraction fails instead of after the extraction has neglected to bring down some new columns.


How can this query be improved to be faster? I am trying to make a search in a db which contains tables poems and authors. I am using replace function to ignore accent characters in db while making the search.

If the title column matches the input it gets high order in search results.
If the content matches the input it gets a lower order in search results.

The below query works correctly but I want it to be faster.

I considered an extra column that will hold the accent-ignored copies of content and title columns but this almost doubles the db size so I skipped that option. It is around 7mb now. Since it is a mobile app, I don't want its size grow to 15mb just to ignore the accents.

Select poems._id, title, name, surname, photoName
from poems,authors
where ( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
        LIKE '%input%'
      OR
        replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
        LIKE '%input%')
  AND authors._id = poems.authorId
ORDER BY (CASE WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE 'input%' THEN 1
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE '%input%' THEN 2
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','')
                    LIKE '%input%' THEN 3
               ELSE 4 END)

I considered an extra column that will hold the accent-ignored copies
  of content and title columns but this almost doubles the db size so I
  skipped that option.

Do this. Add the extra column. The cost of the additional memory is nothing compared to anything else you try.


Trigram similarity (pg_trgm) with German umlauts I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'.
CREATE TABLE public.names
  (name text COLLATE pg_catalog."default");

CREATE INDEX names_idx
    ON public.names USING gin (name COLLATE pg_catalog."default" gin_trgm_ops);

SHOW LC_COLLATE; -- de_DE.UTF-8

When I use the similarity()
function to query the similarity for 'Schoenstraße'.
SELECT
    name,
    similarity (name, 'Schoenstraße') AS similarity,
    show_trgm (name)
FROM
    names
WHERE
    name % 'Schoenstraße'
ORDER BY
    similarity DESC;

I get the following result:
Name            similarity  show_trgm

Schyrenstraße   0.588235    {0x9a07c3,0xde3801,""  s"","" sc"",chy,ens,hyr,nst,ren,sch,str,tra,0x76a40e,yre}
Schönstraße     0.5625      {0x9a07c3,0xde3801,0xf00320,0x095f29,""  s"","" sc"",0x6deea5,nst,sch,str,tra,0x76a40e}

db<>fiddle here
Is there anything I can do to improve that or do I need to replace all umlauts in the DB?

For starters: A German poet is still a Poet. 'Poet' <> 'Pöt'; 'oe' <> 'ö'. The convention to replace 'ö' with 'oe' is largely out of use nowadays. See:

https://de.wikipedia.org/wiki/Umlaut#Darstellung_von_Umlauten

I have had related problems many times. The additional module unaccent is instrumental to cover spelling variants and typos on both sides. Install it once per database:
CREATE EXTENSION unaccent;

See:

Does PostgreSQL support “accent insensitive” collations?

The tricky part is to get index support with it. Details may be hard for beginners.
Trigram similarity
Then create the wrapper function as instructed here (same link again):

Does PostgreSQL support “accent insensitive” collations?

Create a new index with it:
CREATE INDEX names_trgm_idx ON names USING gin (f_unaccent(name) gin_trgm_ops);

Query demo:
SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
     , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
        , name <-> 'Schoenstraße';  -- best match first

Sorting by name <-> 'Schoenstraße' additionally serves as tiebreaker to get best matches first (after filtering cheaply with the index).
Consider a GiST index instead of GIN for "nearest neighbor"-type queries with LIMIT. See:

Best index for similarity function

Full text search (FTS or just TS)
Consider full text search to just match complete words (after stemming), or with prefix matching / phrase search ... See:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

The unaccent module provides a dictionary for integration in a TEXT SEARCH CONFIGURATION:
CREATE TEXT SEARCH CONFIGURATION de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;

Index based on it:
CREATE INDEX names_fts_idx ON names USING GIN (to_tsvector('de', name));

Alternatively, since you are dealing with names, you might base it on the simple text search configuration instead of german. No stop words, no stemming. Names don't necessarily follow language-specific rules.
Query demo:
SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schoenstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße') AS match
     , similarity(name, 'Schoenstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße')
ORDER  BY name <-> 'Schoenstraße';  -- exact matches first

Again, sorting by name <-> 'Schoenstraße' serves as tiebreaker, maybe additionally to other sort criteria.
Note, however, that looking for 'Schoenstraße' with FTS does not find 'Schönstraße', as unaccent() does not change 'oe', and maps 'ö' to 'o'. ('oe' <> 'ö').
Related:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Finding the most commonly used non-stop words in a column

db<>fiddle here  -- looking for 'Schoenstraße'
db<>fiddle here  -- looking for 'Schönstraße'


Database size twice as large after mysql dump and re-import After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be?

This is occurring when importing the sql dump right back into exactly the same mysql database. To show what I mean:

Export mysql database: mysqldump -u mysql_user -p database1 > dump.sql
Import mysql database (into same mysql server, on same host): mysql -u mysql_user -p database 2 < dump.sql
Check the database size using:

SELECT table_schema "Database Name",
    sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
    sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Which then gives:

+--------------------+---------------------+------------------+
| Database Name      | database size in MB | free space in MB |
+--------------------+---------------------+------------------+
| information_schema |          0.01074219 |       0.00000000 |
| database2          |        978.73327637 |     120.00000000 |
| database1          |        284.76471329 |     217.00003815 |
+--------------------+---------------------+------------------+
3 rows in set (0.52 sec)

Note how the database 2 is 3.5 times bigger than database 1.
If I do a checksum test, I get the same checksum so the data seems to be valid. Just it's taking up much more space on the disk!

mysql> use database1

;Database changed
mysql> checksum table wp_site;
+--------------------+------------+
| Table              | Checksum   |
+--------------------+------------+
| database1.wp_site  | 2263483605 |
+--------------------+------------+
1 row in set (0.00 sec)

mysql> use database2

;Database changed
mysql> checksum table wp_site;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| database2.wp_site   | 2263483605 |
+---------------------+------------+
1 row in set (0.00 sec)

So my questions:

Why is this happening?
Is it really taking up more space on the disk, or is this somehow a mis-report?
Will database 2 have performance issues since it's much larger in size (even though the data seems to be the same)?

Note: in reality I'm copying the data over to a new server, not duplicating it into the same server. I'm using the example of the same server here to avoid any other unknowns.

Update

After running mysqlcheck -o -A, the database size did shrink a little, but not that much:

+--------------------+-----------------+------------------+------------------+
| Database Name      | data size in MB | index size in MB | free space in MB |
+--------------------+-----------------+------------------+------------------+
| information_schema |      0.00000000 |       0.01074219 |       0.00000000 |
| database2          |    568.22155762 |     358.74609375 |       6.00000000 |
| database1          |    174.76861954 |     109.99609375 |     215.00003815 |
+--------------------+-----------------+------------------+------------------+

Run the following command so that free space is compressed, and indexes checked and optimized:

mysqlcheck -o -A

After that, try to execute the following query:

SELECT table_schema "Database Name",
sum( data_length) / 1024 "data size in MB",
sum( index_length) / 1024 "index size in MB",
sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES GROUP BY table_schema;

Especially check if the index size in MB column is different between the 2 databases, and show us the results.


How to update column with ascending value column I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?

Real state:

id       name     position
-------  -------  -------
1        S4       null
2        S2       null
3        S3       null
4        S1       null

Expected:

id       name     position
-------  -------  -------
1        S4       4
2        S2       2
3        S3       3
4        S1       1

I tried with update:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) position
  FROM
      stage
)
UPDATE
    stage
SET
    position = x.position
FROM
    x
WHERE
    x.id = stage.id;

Stage is the name of the table.

You can use ROW_NUMBER() window function:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) ord
  FROM
      tbl
)
UPDATE
    tbl
SET
    ord = x.ord
FROM
    x
WHERE
    x.id = tbl.id;

SELECT * FROM tbl;

id | name | ord
-: | :--- | --:
 1 | S4   |   4
 2 | S2   |   2
 3 | S3   |   3
 4 | S1   |   1

db<>fiddle here

If there isn't duplicated names you can remove 'S' from name:

UPDATE
    tbl
SET
    position = right(name, length(name) - 1)::int

db<>fiddle here


Is it possible to tell if a database has AUTOMATIC_TUNING switched on? In SQL Server 2017 is it possible to tell if automatic tuning is switched on? This relates to tuning of the plan. So the database automatically monitors itself and if the plan gets worse it goes back to an old plan

Automatic query tuning requires that Query Store be enabled first, then you can enable Automatic Tuning.

To check Query Store, select * from sys.database_query_store_options. You're looking for actual_state_desc to be READ_WRITE.

Then check the tuning options with select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'. You're looking for actual_state to be set to ON.


SQL ssis delete imported rows from source table I have setup a simple SSIS package that copies data from a source table to a destination table which works.
I would like to get the package to also delete the data in the source table that has been copied to the destination, there seems to be a great deal on deleting from the destination but not the source, how can I achieve deleting the copied data from the source?
Thanks

SQL 2008 R2

Since you're working with SQL Server 2008, you can take delete first approach as I outlined on this SO answer https://stackoverflow.com/a/15508174/181965

A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause.

I created a simple control flow that populates a table for me.

IF EXISTS
(
    SELECT 1 FROM sys.tables AS T WHERE T.name = 'DeleteFirst'
)
BEGIN
    DROP TABLE dbo.DeleteFirst;
END

CREATE TABLE dbo.DeleteFirst
(
    [name] sysname
);

INSERT INTO
    dbo.DeleteFirst
SELECT
    V.name
FROM
    master.dbo.spt_values V
WHERE
    V.name IS NOT NULL;

In my OLE DB Source, instead of using a SELECT, DELETE the data you want to go down the pipeline and OUTPUT the DELETED virtual table. Somethinng like

DELETE
    DF
OUTPUT
    DELETED.*
FROM
    dbo.DeleteFirst AS DF;

It works, it works!


Why is my database still fragmented after I rebuilt and reindexed everything? I have a database which I tried to defragment all the tables at once by running this T-SQL:

SELECT
        'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
        'ALTER INDEX all ON ' + name + ' REBUILD;'
    FROM sys.tables

And then copying and pasting the output to a new query window and running that. I got no errors, but I still have fragmentation. I tried running both commands separately too and still have fragmentation. Note: I have been made aware that REORGANIZE is unnecessary by Aaron, and I'm aware I could use dynamic sql to automate this.

I ran this to determine I still have fragmentation:

SELECT * FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
WHERE avg_fragmentation_in_percent > 0

And I got:

database_id object_id   index_id    partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count  avg_page_space_used_in_percent  record_count    ghost_record_count  version_ghost_record_count  min_record_size_in_bytes    max_record_size_in_bytes    avg_record_size_in_bytes    forwarded_record_count  compressed_page_count
85  171147655   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   36.3636363636364    5   2.2 11  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  421576540   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   75  7   1.14285714285714    8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  965578478   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   14.7058823529412    6   5.66666666666667    34  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1061578820  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   40  4   1.25    5   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1109578991  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   30.7692307692308    5   2.6 13  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1205579333  2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   50  5   1.6 8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1493580359  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   50  6   1.66666666666667    10  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

I know I am missing something real basic, but I don't know what.

The tables are tiny. The page counts in your tables are:

11, 8, 6, 5, 13, 8, 10

They occupy 480kb in total. There is quite literally nothing to defrag.

Edit: This warrants a little more explanation.

A new table or index is usually allocated it's first 8 pages from a mixed, rather than uniform extent. So, it's possible for each of the first 8 pages to be allocated from different mixed extents. A table or index consuming 8 pages could therefore have 8 fragments, 1 on each of 8 different mixed extents.

The more widely used defrag scripts (a couple of examples linked below) tend to exclude small tables because of this. IIRC, <500 pages is in one or both of them. At these sizes, there is very little benefit to defragmenting and the fragmentation figures are potentially skewed by the mixed extent allocations.

Ola Hallengren - SQL Server Maintenance Solution
SQL Fool - Index Defrag Script


Oracle's GV$SQL_MONITOR - Uniquely identify execution of a SQL statement I'm playing around with the data from GV$SQL_MONITOR and I found something that I don't understand. The oracle documentation says that SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID combine to "uniquely identify one execution of the SQL statement". However, I see the below data in the table. Many records have the same SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID, but a unique SID.

Does anyone here have insight into why the documentation and my observations contradict each other?

Possibly relevant: I observed that all of these queries finish at the same time.

SQL_EXEC_START        SQL_ID        SID SQL_EXEC_ID ELAPSED_TIME STATUS              LAST_REFRESH_TIME
--------------------- ------------- --- ----------- ------------ ------------------- ---------------------
2017-09-20 14:27:56   gncss37ygprra 196    16777218      1847766 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 3148    16777218       498598 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 3329    16777218        96863 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 480    16777218      1849199 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 3727    16777218      1848618 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 498    16777218       513823 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 2578    16777218      1955707 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 3541    16777218      1848522 EXECUTING           2017-09-20 14:27:58

Let's create the below function so we can have a long running parallel query and have time to query SQL monitoring data:

create or replace function f1 return number parallel_enable as
begin
  dbms_lock.sleep(1800);
  return 1;
end;
/

Then this table:

create table t1 as select * from dual connect by level <= 4;

And:

alter session force parallel query parallel 4;

And start this query:

select f1 from t1;

If you query GV$SQL_MONITOR, with some additional columns, you can see it is a parallel query, and the sessions it's spawned, and also find the session that originally started it (QCSID):

select sql_exec_start, sql_id, sql_exec_id, sid, px_qcinst_id, px_qcsid from gv$sql_monitor
where status = 'EXECUTING';

SQL_EXEC_START      SQL_ID        SQL_EXEC_ID        SID PX_QCINST_ID   PX_QCSID
------------------- ------------- ----------- ---------- ------------ ----------
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        270            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216         20            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        400            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        134            1        262


What did I miss in EXPLAIN that hinted at server crippling loads? I reviewed EXPLAIN ANALYZE output for a query and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment.
After some research, I decided that it would be reasonable to use a lateral join on those subqueries to dramatically reduce the amount of data scanned. EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original. So we proceeded.
Within two hours of deploying the query change, our DB server was maxed out at 100% and basically unresponsive.  Reverting the query to use subqueries scanning the tables restored the CPU usage to something sane.  Our DB is running in AWS RDS for PostgreSQL using a t2.xlarge.  Performance insights showed a substantial increase in ClientWrite. See .
The query using subqueries along with the EXPLAIN output: https://explain.depesz.com/s/wES6.
select appointments.*,
    reportSnapshots.created_at as latestSnapshotTime,
    responses.created_at as latestResponseTime
from appointments
    left join (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM reportSnapshots
            ORDER BY appointmentId, created_at DESC
    ) reportSnapshots on appointments.id = reportSnapshots.appointmentId
    left join (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM responses
            ORDER BY appointmentId, created_at DESC
    ) responses on appointments.id = responses.appointmentId
where appointments.organizationId = 16 and appointments.locationId = '51'
    and appointments.cancelled = false and appointments.filteredIn = true
    and start between '2021-05-04T00:00:00-06:00' and '2021-05-04T23:59:59-06:00'
    and appointments.locationId in (61,60,140,53,138,130,133,131,55,51,100)
group by appointments.id,
    reportSnapshots.created_at,
    responses.created_at
order by start ASC, start ASC, id ASC
limit 100

The query using lateral join along with the EXPLAIN output: https://explain.depesz.com/s/B2vp.
select appointments.*,
    reportSnapshots.created_at as latestSnapshotTime,
    responses.created_at as latestResponseTime
from appointments
    left join lateral (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM reportSnapshots
        WHERE reportSnapshots.appointmentId = appointments.id
            ORDER BY appointmentId, created_at DESC
    ) reportSnapshots on appointments.id = reportSnapshots.appointmentId
    left join lateral (
        SELECT DISTINCT ON  (appointmentId) created_at, appointmentId
            FROM responses
        WHERE responses.appointmentId = appointments.id
            ORDER BY appointmentId, created_at DESC
    ) responses on appointments.id = responses.appointmentId
where appointments.organizationId = 16 and appointments.locationId = '51'
    and appointments.cancelled = false and appointments.filteredIn = true
    and start between '2021-05-04T00:00:00-06:00' and '2021-05-04T23:59:59-06:00'
    and appointments.locationId in (61,60,140,53,138,130,133,131,55,51,100)
group by appointments.id,
    reportSnapshots.created_at,
    responses.created_at
order by start ASC, start ASC, id ASC
limit 100

Obviously, I did not understand what the EXPLAIN output was telling me about the queries. What did I miss that could have told me the DB load would be higher with the lateral join query despite a lower cost?

EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original.

EXPLAIN (estimating the cost) suggests 40,089.36 vs. 189,883.92 unicorn points.
But EXPLAIN ANALYZE (measuring actual execution times) disagrees and shows 2,502.031 ms vs. 1,835.193 ms, so around 1/3 slower. There can be many reasons why the estimation is off target. Most prominently cost settings and statistics. See:

Keep PostgreSQL from sometimes choosing a bad query plan

That said, the query can probably be much faster (by orders of magnitude). We only got limited information, but I suppose you want something like this:
SELECT a.*
     , rs.created_at AS latestsnapshottime  -- maybe use COALESCE?
     , rp.created_at AS latestresponsetime
FROM   appointments a
LEFT   JOIN LATERAL (
   SELECT rs.created_at
   FROM   reportsnapshots rs
   WHERE  rs.appointmentid = a.id
   ORDER  BY rs.created_at DESC  -- NULLS LAST ?
   LIMIT  1
   ) rs ON true
LEFT   JOIN LATERAL (
   SELECT rp.created_at
   FROM   responses rp
   WHERE  rp.appointmentid = a.id
   ORDER  BY rp.created_at DESC  -- NULLS LAST ?
   LIMIT  1
   ) rp ON true
WHERE  a.organizationid = 16
AND    a.locationid = '51'
AND    a.cancelled = FALSE
AND    a.filteredin = TRUE
AND    a.start BETWEEN '2021-05-04T00:00:00-06:00' AND '2021-05-04T23:59:59-06:00'
AND    a.locationid IN (61,60,140,53,138,130,133,131,55,51,100)
-- GROUP  BY a.id, rs.created_at, rp.created_at  -- not needed, I guess
ORDER  BY a.start, a.id
LIMIT  100;

DISTINCT ON is a great tool, but for different situations. See:

Select first row in each GROUP BY group?

Retrieving a single row from each LATERAL subquery like I suggest can  use an index and is super fast. Ideally, you have theses indexes:
reportsnapshots (appointmentid, created_at DESC NULLS LAST)
responses       (appointmentid, created_at DESC NULLS LAST)

Further reading:

Optimize GROUP BY query to retrieve latest row per user

If created_at is defined NOT NULL, a simpler index on (appointmentid, created_at) is just as good (and preferable). See:

Sort by column ASC, but NULL values first?
Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Plus an index on the outer table. The one in use now (appointments_organizationId_status_start_idx) doesn't seem too bad. But I suspect more potential, depending on undisclosed information.
You probably don't have to aggregate in the outer query, since both subqueries return a single row each (even your original).
Alternatively, use plain correlated subqueries with max() for your simple case. Probably even faster, yet:
SELECT a.*
     , (SELECT max(rs.created_at)
        FROM   reportsnapshots rs
        WHERE  rs.appointmentid = a.id) AS latestsnapshottime
     , (SELECT max(rp.created_at)
        FROM   responses rp
        WHERE  rp.appointmentid = a.id) AS latestresponsetime
FROM   appointments a
WHERE  ...
LIMIT  100;

See:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Aside 1: BETWEEN is typically no good for timestamps. See:

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29
How to add a day/night indicator to a timestamp column?

Aside 2: In your first query plan I see Sort Method: external merge Disk: 3,856kB and Sort Method: external merge Disk: 17,752kB, which indicates  a lack of work_mem. The same problem does not surface in the 2nd plan, nor will it for my queries. But look into your server configuration. Related:

Optimize simple query using ORDER BY date and text


MySQL replication using a lot of IO I'm having big troubles on a database pool I've setup recently.

There is a master, and 4 slaves replicating one DB. All using MyISAM engine.

Thing is, during the replication period (when the slave is not up to date), the IO% showing in iotop is equal to 99% for mysqld.

Besides, I'm having a big latency when connecting a web application to any of these databases (master included).

All these servers are virtual machines running Ubuntu 12.04, having 4vcpu each and 12GB RAM.

If you need more information about the config (system/mysql), please tell me, I'll update my post.

Thing is, I need some hint about where to search, because for now I was not able to identify the problem.

EDIT:

I'm adding the conf files for the master and for a replica :

master configuration file

replica configuration file

2 questions:
- Where is the IO problem ? On the master or on the replicas ?
- If the IO contention is on a replica, can you create a TEST replica, replicating from the same master, but having innoDb engine ?

The thing with MyISAM storage is that an UPDATE / INSERT query will lock the whole table. And the replication thread on each replica is also a serial process, running just one query at a time (event) from the master binlog.

My suggestion would be change the storage engine for all tables, from MyISAM to InnoDb, then re-check the replication parameters.


Import in postgres json data in a csv file I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type.

One line of my csv file contains something like

1,{"a":"b"}

Suppose the table has a schema

id              | smallint          |
data            | jsonb             |

If I try just to insert the data, everything works fine

INSERT INTO table VALUES (1, '{"a":"b"}');

Trying to import directly from the file with

COPY table FROM '/path/to/file.csv' DELIMITER ',' csv;

gives me the following error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY availability, line 1, column services: "{a: b}"

I tried to quote the fields with ', with ", with \" and \', but nothing works..

Which is the correct syntax do do it?

The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing.

CREATE TEMP TABLE baz AS
  SELECT 1::int, '{"a":"b"}'::jsonb;

This is your exact sample data. Now we can test different settings..

# COPY baz TO STDOUT;
1   {"a": "b"}

COPY baz TO STDOUT DELIMITER ',';
1,{"a": "b"}

You'll see that the above generates the exact data your questioning...

COPY baz TO '/tmp/data.csv' DELIMITER ',';

There is no problem. At least not with PostgreSQL 9.5.

CSV Mode

So where is your problem, it's with CSV-mode. Observe,

# COPY baz TO STDOUT;
1   {"a": "b"}
# COPY baz TO STDOUT CSV;
1,"{""a"": ""b""}"

You can see these two are different now. Let's try to load the non-CSV file in CSV mode which assumes the format that CSV mode generated above.

TRUNCATE baz;
COPY baz FROM '/tmp/data.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY baz, line 1, column jsonb: "{a: b}"

Now we error. The reason for that comes from RFC 4180

  Each field may or may not be enclosed in double quotes (however
         some programs, such as Microsoft Excel, do not use double quotes
         at all).  If fields are not enclosed with double quotes, then
         double quotes may not appear inside the fields.

So JSON RFC 4627 specifies an object's names in name/value pairs must be strings which require double quotes.
And CSV RFC 4180 specifies that if any double quotes are inside the field, then the whole field must be quoted.

At this point you have two options..

Don't use CSV mode.
Or, Escape the inner quotes.

So these would be valid inputs under the same options in CSV mode.

#COPY baz TO STDOUT DELIMITER ',' CSV ESCAPE E'\\';
1,"{\"a\": \"b\"}"

# COPY baz TO STDOUT DELIMITER ',' CSV;
1,"{""a"": ""b""}"


SQL Server database on an SSD - any advantage to a separate file for every table? I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key column in order to maximise query efficiency in the face of heavy updates and insertions and make heavy use of clustered indexes. Two of the tables will contain variable-length textual data, with one of them containing hundreds of millions of rows but the rest will contain only numeric data.

As I really want to squeeze every last drop of performance out of the hardware I have available (about 64GB of RAM, a very fast SSD and 16 cores), I was thinking of allowing each table to have its own file so that no matter if I'm joining on 2, 3, 4, 5 or more tables, each table will always be read using a separate thread and the structure of each file will be closely aligned with the table contents, which would hopefully minimise fragmentation and make it faster for SQL Server to add to the contents of any given table.

One caveat, I'm stuck on SQL Server 2008 R2 Web Edition. Which means I can't use automatic horizontal partitioning, which rules that out as a performance enhancement.

Will using one file per table actually maximise performance, or am I overlooking built-in SQL Server engine characteristics that would make doing so redundant?

Second, if using one file per table is advantageous, why does create table only give me the option to allocate the table to a file group and not to a specific logical file? This would require me to create a separate file group for every file in my scenario, which suggests to me that perhaps SQL Server isn't envisioning the advantages I am assuming would come from doing what I'm proposing.

I was thinking of allowing each table to have its own file so that no
  matter if I'm joining on 2, 3, 4, 5 or more tables, each table will
  always be read using a separate thread and the structure of each file
  will be closely aligned with the table contents, which would hopefully
  minimise fragmentation and make it faster for SQL Server to add to the
  contents of any given table

What the heck are you talking about? Not sure where you got your information from, but you should certainly discard that source. Nothing from what you assume here is actually correct.

If you want to read a good discussion of SSD performance for SQL Server there are several blog series out there. As usually, Paul Randal's one is the top read:

Benchmarking: Introducing SSDs (Part 1b: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 2: sequential inserts)
Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)
Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)

Brent also has a nice presentation on the topic: SQL on SSDs: Hot and Crazy Love and there are more out there.

Going through all these presentations you will quickly notice that they all focus on writes since this is where SSDs performance comes into picture. Your post wording is almost entirely about reads, which is a different topic. If reads are your pain point then you should be talking about RAM, not about SSDs, and about proper indexing and querying strategies.


Random unguessable primary key which preserves correct order I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: https://gist.github.com/mikelehen/3596a30bd69384624c11. Is it a good idea to port it to psql or plv8 for example and use in postgres to generate primary keys for a table? Or maybe there is other solution out of the box in PG?

Don't do that.

If you want to make a user-unique-and-crazy-key don't use the primary key of the table, create another column with this kind of data and you can generate a unique code using md5 of the unix microtime for example.

The Human world are strange for databases, believe me or not! Let the keys to be keys, its nice and faster to table relationships.

And for any user or system search you use another column, its very like to want to relationship users by using a person one identification code, so you change the country and discovery the code repeats, and all your database is lost. You want a unique crazy code, but it does not mean nothing for your machine, how your machine will work for nothing to make your relationships working. For relationships use things that your machine can really understand, in another words: Use sequential numbers.


Logging errors with extended events excluding some users I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works.
I have some users called test1, test2 and so on that work interactively, and produce several errors. I don't want to log these errors.
I tried to change the WHERE clause like this:

WHERE
(
    [severity] >= (11) AND sqlserver.username NOT LIKE 'test%'
)

I get these errors:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'LIKE'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'TARGET'.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

How can I exclude the errors from some users from my logging?

Since you are using sql 2008R2, you cannot use like predicate comparisons as they were introduced with SQL Sever 2012.

If you are on SQL 2012, then you can use below query to find what is available :

select xp.name, xo.name, xo.description, xo.type_name, xo.type_size from
sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp on xo.package_guid=xp.guid
where object_type like 'pred_compare'

SQL 2008 R2 does not have like_i_sql_unicode_string vs SQL 2012 has it.

So, below should work on SQL 2012

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
            [severity] >= (11) AND
            (NOT sqlserver.like_i_sql_unicode_string(sqlserver.username,N'test%'))

    )

)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='C:\ErrorCapture_Extended_Event.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

Refer: Connect item Extended events predicate - NOT LIKE required