How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

Pages are read into memory as required, if there is no free memory available, the oldest unmodified page is replaced with the incoming page.

This means if you execute a query that requires more data than can fit in memory, many pages will live a very short life in memory, resulting in a lot of I/O.

You can see this effect by looking at the "Page Life Expectancy" counter in Windows Performance Monitor.  Look at https://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy for some great details about that counter.

In the comments, you asked specifically what happens when the results of the query is larger than available buffer space.  Take the simplest example, select * from some_very_big_table; - assume the table is 32GB, and max server memory (MB) is configured at 24GB.  All 32GB of table data will be read into pages in the page buffer one at a time, latched, formatted into network packets, and sent across the wire.  This happens page-by-page; you could have 300 such queries running at the same time, and assuming there was no blocking happening, the data for each query would be read into page buffer space, a page at a time, and put onto the wire as fast as the client can request and consume the data.  Once all the data from each page has been sent onto the wire, the page becomes unlatched, and will very quickly be replaced by some other page from disk.

In the case of a more complex query, say for instance aggregating results from several tables, pages will be pulled into memory exactly as above as they are required by the query processor.  If the query processor needs temporary work space to calculate results, it will know that upfront when it compiles a plan for the query, and will request work space (memory) from SQLOS.  SQLOS will at some point (assuming it doesn't time out), grant that memory to the query processor, at which point query processing will resume.  If the query processor makes a mistake in its estimate of how much memory to ask for from SQLOS, it may need to perform a "spill to disk" operation, where data is temporarily written into tempdb in an intermediate form.  The pages that have been written to tempdb will be unlatched once they are written to tempdb to make room for other pages to be read into memory.  Eventually the query process will return to the data stored in tempdb, paging that in using latching, into pages in the buffer that are marked free.

I'm undoubtedly missing a load of very technical details in the above summary, but I think that captures the essence of how SQL Server can process more data than can fit in memory.


Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

Indexes remain compressed when rebuilding / reorganizing them.

Create table and compressed index

 CREATE TABLE DBO.TEST_INDX(id int, bla varchar(255));
 CREATE INDEX IX1 ON dbo.TEST_INDX(id)  WITH (DATA_COMPRESSION = PAGE);

Check Compression

 SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1';

Result

name    data_compression_desc
IX1     PAGE

Rebuild the index

ALTER INDEX IX1 on  DBO.TEST_INDX rebuild

Check Compression

 SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1'

Result

name    data_compression_desc
IX1     PAGE

Disabling them and then rebuilding has a different result, since disabling removes the index, while keeping the index definition.

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD ;

Result

name    data_compression_desc

Compression was lost, compression definition would also be lost when dropping and creating the index via SSMS without adapting the Index create script.

Why?

Because the data_compression option is not retained when scripting out the Index create statement.

however, if we disable the index , rebuild with compression and then rebuild again:

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD  WITH (DATA_COMPRESSION = PAGE);
alter index IX1 on  DBO.TEST_INDX REBUILD;

Result

name    data_compression_desc
IX1 PAGE

Testing a rebuild with Ola hallengren's maintenance solution

The parameters are modified for testing purposes.

Add some data to get to one page, as it is needed for the MinNumberOfPages parameter.

INSERT INTO dbo.TEST_INDX(id,bla)
VALUES(5,'test');
go 10

Execute the index optimize proc to print out the statement.

EXECUTE dbo.IndexOptimize
@Databases = 'TestDB',
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'TestDB.DBO.TEST_INDX',
@Execute = 'N',
@MinNumberOfPages = 1;

Result:

Command: ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Comment: ObjectType: Table, IndexType: NonClustered, ImageTex
t: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1, Fragmentation: 0
Outcome: Not Executed
Duration: 00:00:00
Date and time: 2019-01-09 14:48:12

Executing the generated command

ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Compression is retained

name    data_compression_desc
IX1 PAGE

Testing a rebuild with a maintenance plan (I would strongly argue for ola's solution)

Rebuild indexes

Choose the test table

Add some test fragmentation levels.

Insert some values to get the fragmentation going

INSERT INTO dbo.TEST_INDX(id)
SELECT id from TEST_INDX
go 4

Check the fragmentation percentage

SELECT
I.[name] AS  INDX ,
IPS.avg_fragmentation_in_percent,
IPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[TEST_INDX]'), NULL, NULL, NULL) AS IPS
INNER JOIN sys.indexes AS I ON I.[object_id] = IPS.[object_id]
AND IPS.index_id = I.index_id
WHERE IPS.database_id = DB_ID()
and I.name = 'IX1'

Result

INDX    avg_fragmentation_in_percent    page_count
IX1 66,6666666666667    3

Run the plan

The interesting part here, when looking at the plan report, is that the DATA_COMPRESSION = PAGE option is added to the generated REBUILD command!

Command:USE [TestDB]
GO
ALTER INDEX [IX1] ON [dbo].[TEST_INDX] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, DATA_COMPRESSION = PAGE)

Fragmentation:

INDX    avg_fragmentation_in_percent    page_count
IX1 0   2

Compression:

name    data_compression_desc
IX1 PAGE


Filtered indices: Why include the filtered-on field?

This has been previously raised on Connect and Closed as "Won't Fix".

  We'll close this because it seems a narrow scenario and we don't see
  doing anything about it in the forseeable future.

That's quite disappointing IMO. Perhaps upvote and comment that item.

Edit: BOL has an example of using WHERE EndDate IS NOT NULL in a filtered index and that does not do a key lookup but replacing IS NOT NULL with IS NULL does lead to a lookup so it appears as though sometimes it is avoided...


If I stop the network interface (eth0) of the primary server to activate ReplicaSet, should the myState attribute of the secondary server change to 1?

Since you only have two voting members in your replica set, the strict majority of voting members required to elect or maintain a primary (n/2 + 1)  is 2.

You should expect that if either member is unavailable, there will be no primary: a two member replica set does not provide automatic fault tolerance.

  If the status is to change, I have already tested it and it is not changing. How do I know that the secondary has become primary?

The replica set member state changes to reflect the current role of the replica set member. Your surviving secondary cannot become a primary without manual intervention to forcibly reconfigure the replica set. The recommended solution for production deployments is to add a third member to your replica set (ideally another data-bearing member hosted on a third machine) so your deployment can automatically recover from any single member being unavailable.

There are several ways to confirm member state via the mongo shell:

The default mongo shell prompt is prefixed with the replica set name and member state of the MongoDB process you are connected to, for example: replset:PRIMARY>. If the member state has changed, hitting <enter> should re-establish a connection and refresh the shell prompt.
Check via db.isMaster().primary. This status value should be set to the current host (equivalent to db.isMaster().me) if it is primary.
Check the member state in rs.status().myState. A value of 1 indicates the current member has the state PRIMARY.

You can find out more information about the election process by searching for MongoDB log lines with the  REPL log component. When you shutdown the primary on your two member replica set, you should see log messages similar to the following on your remaining secondary:

# Checking from a bash shell (note the intentional space after "REPL ")
grep "REPL " mongod.log | tail -1

2018-12-24T12:40:01.894+1100 I REPL     [replexec-2] Not starting an election, since we are not electable due to: Not standing for election because I cannot see a majority


Sql server 2008 filestream maximum number of rows

Actually you can put as many files as you want in a file group.

  Basically file group feature included in sql server so that you can
  archive your organization data properly and can query the data
  efficiently. Its also depend on the hard disk space on server and
  configuration that which raid model you use for data storage.


Multiply by value from related row with latest date

A LATERAL subquery would do the job:

SELECT t.staff_id, t.entry_date, t.hours * r.rate AS total_rate
FROM   time_table t
LEFT   JOIN LATERAL (
   SELECT r.rate
   FROM   rates_table r
   WHERE  r.staff_id = t.staff_id
   AND    r.start_date <= t.entry_date -- "on or before that time entry"
   ORDER  BY r.start_date DESC NULLS LAST
   LIMIT  1
   ) r ON true;

db<>fiddle here

Details depend on more information. The LEFT JOIN keeps all rows from time_table in the result, even if no rate is found. (total_rate is NULL in that case.)

This is typically efficient for many rate entries per staff_id - if you have an index on rates_table(staff_id, start_date DESC NULLS LAST) or similar.

If you can get index-only scans out of it, a covering index would be better, yet:

CREATE INDEX ON rates_table (staff_id, start_date DESC NULLS LAST) INCLUDE (rate);

This form of the index requires Postgres 11 or later. See:

Can Postgres use an index-only scan for this query with joined tables?

Depending on table definition, indexes, data distribution etc. other query styles may be preferable. For querying the whole table and only few rows per staff_id, DISTINCT ON might be faster ...

Related:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest record per user
Slow left join lateral in subquery
What is the difference between LATERAL and a subquery in PostgreSQL?


Cannot run 'uuid-ossp' functions even when it's activated

There are several possibilities:

you ran CREATE EXTENSION in a different database (postgres?)
you installed the extension in a schema that is not on the user's search_path.

Use

\dx "uuid-ossp"

in psql to see where the extension is installed.


What are definer and invoker rights in database, Oracle and MySQL?

Let's take a simple example:
You have this procedure using "definer rights" - which is the default in Oracle.
CREATE PROCEDURE DEL_EMP AS
BEGIN
   DELETE FROM EMP;
END;

Another user who calls this procedure only needs EXECUTE privilege for this procedure, it is not required that such user has DELETE privilege on table EMP.
Procedure runs under permission of the procedure owner (or user who defined it, thus it is called "definer" rights).
"Inovker Rights" is the opposite. A user who likes to runs this procedure successfully must have EXECUTE privilege for this procedure and DELETE privilege for table EMP.
There are some more points regarding definer and invoker rights but for a general understanding this should be enough.


Should a database 'administrator' have the ability to deep dive into runtime query performance issues?

Titles are just that, nothing more than a set of words, and the relevancy of their meaning are dependent on how you define that meaning within the context of your domain. In this case that domain is your company, so you guys should define the DBA role to fit the needs of the business (realistically under the umbrella of what a DBA can do).
DBA is one of those kinds of roles that I've found to evolve as a many hats or crossover type of position. The reasoning being is it's a career heavily populated by a mix of people who either were Software Developers, System Administrators, or neither, in a past life.
As someone who went from Software Developer to resident DBA to actual DBA at another company and back to a quasi-Software Developer / DBA now, I got to experience most of the database development and performance tuning side of being a DBA as opposed to server and database management - though I have and still do manage some of those aspects as well. This was because of my prior experience as a developer and because the companies I worked for needed someone who could write code but also help optimize performance in the database layer both from an architectural standpoint and a query tuning one.
So in summary, there is a rainbow of DBA roles out there between ones who essentially just administrate database systems and get their hands dirty under the hood of the servers themselves, others who purely do database design and performance tuning, and others who have a mix of actual software development (even outside the database layer) responsibilities and do performance tuning and database management.


Why can't I make this insert statement a procedure?

I've raised a bug for this. In the meantime, you can work around the issue by using dynamic SQL:
create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test.csv', 'w');
  utl_file.put_line(f, '1,1');
  utl_file.put_line(f, '2,2');
  utl_file.fclose(f);
end;
/

create table t (
  c1 int, c2 int
);

create or replace procedure p as
begin
  execute immediate q'!insert into t
    select * from external (
    ( c1 int, c2 int )
    default directory tmp
    location ( 'test.csv' )
  )!';
end p;
/

exec p();

select * from t;

C1     C2
    1     1
    2     2


Some guesses.  Some things come from SHOW GLOBAL STATUS (highlighted and with initial caps, below); some from elsewhere.

Questions:  Status Questions or Queries.  The latter seems to include statements inside Stored routines.

Connections:  Not Connections since that continues to grow.  Probably not Threads_running since that should be very small.

Processes:  Perhaps from the OS.  Are you using Windows?

Traffic:  Bytes_sent/received.  (Not very useful)

CPU Usage:  The OS.  That is high.  You have some slow queries and/or poor indexing.  See the slowlog (more below).

System Memory:  The OS.  See top or whatever Windows has.  Sounds like there is room to increase some settings.  Do you have 6MB of RAM?

Swap:  From the OS.  No swapping is Good.

Slow queries:  Slow_queries.  0 is good ...IF... you have a reasonable value for long_query_time, which defaults to 10 seconds.  Change it to 1.

Key reads:  Maybe Key_reads, which applies only to MyISAM.  Are you using that engine?  Maybe Handler_read_key, which applies to all engines, but is not useful by itself.

The graphs are pretty, but mostly useless.

I may be able to give you about 20 tips from the full set of VARIABLES and GLOBAL STATUS, plus tips from the slowlog, once you make use of it:  http://mysql.rjweb.org/doc.php/mysql_analysis


Cannot run Sql Server Agent jobs with PowerShell script

It sounds like you have a good way to reproduce the problem:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

The next step I'd take is to do some tracing to determine where the error is happening. Since you aren't getting a stack trace in your repro, I'd start by running Process Monitor.

I'd want to start by filtering on processes named Powershell.exe, and then adjust the filters based on the data captured. My suspicion is that this will turn out to be a problem with the PowerShell profile for the Windows service account, but hopefully with Process Monitor you'll get an idea of what error is leading up to the generic -1073741819, which is defined as being a default or unknown error in various Windows libraries.

Significant edit: According to the Visual Studio ERRLOOK feature, the error message of -1073741819 shown in the lower-right corner of the stack trace in your post translates to 0xC0000005. This is defined as STATUS_ACCESS_VIOLATION in ntstatus.h. It could be that some component is not correctly installed, or something is interfering with it (e.g. antivirus). If an access violation is occurring, there might also be further details in Windows' application log.

I'd suggest testing running Process Monitor in a non-production environment first until you feel familiar with it, and selecting the 'Drop Filtered Events' option to avoid consuming large amounts of memory from buffering all events.


can I script all the database jobs one by one and run them against a new server to create these jobs?

Yes

But anything dependent will need to exist, i.e. users, proxies, if there is a path in the task it needs to work against the new server.

Normally there is some kind of migration process, i.e. "I'll need to change all D drives to E, I'll need to change the proxy name" etc.

Why don't you try one and see.


ERROR: loop variable of FOREACH must be a known variable or list of variables

Without seeing complete code, or a cut-down test case, it's hard to be sure but the error strongly suggests that:

FOREACH _mat
        ^^^^
        this variable is undeclared


breaking up complex queries into simple ones

Poppycock.  Why do extra effort when MySQL is quite willing to do it for you?  As for performance -- there is probably no difference except that the broken up queries require more round trips to the server.

OTOH, there are cases where you can outsmart the optimizer.  But your example was not one of those.

IN (thousands-of-ids) is possible, but painful, for the server.  It will sort and de-dup them, then leave them in some kind of structure for repeated binary searching.  I have seen lots of such queries, but only those over, say, 50K items raised any eyebrows.

There are times when this rewrite helps:

SELECT ... ORDER BY ... LIMIT ...

-->

SELECT b...
FROM tbl b
   JOIN ( SELECT id FROM TBL WHERE ... ORDER BY ... LIMIT ... ) a
   ON a.id = b.id

But that is to avoid hauling around extra junk that will be thrown away by the LIMIT.


Oracle Query to Compare dates from Two Different tables

There are other ways to do this but the simplest is with a subquery:

select assetid
     , data_received_on
     , (select min(last_contacted_on)
        from customer_contacted_logs c
        where c.assetid=a.assetid and last_contacted_on>=data_received_on)
       last_contacted_on
from asset_data_historical a;

ASSETID | DATA_RECEIVED_ON  | LAST_CONTACTED_ON
------: | :---------------- | :----------------
  17085 | 23-AUG-2017 22:50 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 20:54 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 21:08 | 28-SEP-2017 21:57
  17085 | 31-AUG-2017 18:49 | 28-SEP-2017 21:57
  17085 | 29-SEP-2017 18:51 | 16-OCT-2017 18:51

If you need the ticket ID as well it becomes a more interesting problem — and the idiomatic Oracle way is with KEEP…FIRST:

select assetid,data_received_on,ticketid,last_contacted_on
from (select assetid
           , data_received_on
           , (select min(ticketid) keep (dense_rank first order by last_contacted_on)
              from customer_contacted_logs c
              where c.assetid=a.assetid and last_contacted_on>=data_received_on)
             ticketid
      from asset_data_historical a)
     left outer join customer_contacted_logs using(ticketid,assetid);

ASSETID | DATA_RECEIVED_ON  | TICKETID | LAST_CONTACTED_ON
------: | :---------------- | -------: | :----------------
  17085 | 31-AUG-2017 18:49 |  1785487 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 21:08 |  1785487 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 20:54 |  1785487 | 28-SEP-2017 21:57
  17085 | 23-AUG-2017 22:50 |  1785487 | 28-SEP-2017 21:57
  17085 | 29-SEP-2017 18:51 |  1785491 | 16-OCT-2017 18:51

db<>fiddle here


Yes, it's fairly simple. You need to copy the database from the old disk image to a directory in the new host then start it using the same major version of PostgreSQL compiled with the same options (integer datetimes on/off, etc).

You can start the DB with a command like PGPORT=5433 pg_ctl -D /path/to/copy/of/old/database start. You must use the same PostgreSQL major version that the DB was created with before. If you're not sure what that was, check the PG_VERSION file in old data directory.

Once you've started a server with the old database you can then pg_dump the databases(s) and pg_dumpall --globals-only the users, etc, and load them into the new database.

Do not try to start the database directly on the old disk image, make a copy and start the copy.

You haven't provided details about versions, operating systems, how you installed Pg on the old and new machines, etc, so it's hard to provide more detail.


How to sync logins and jobs when using an availability group?

I have to credit David Browne for this one (I learned from one of his recent answers), but one thing you can do is setup a Linked Server between the your Primary and Secondary Replicas. Then you can create a SQL Agent Job on the Primary Replica that generates the scripts for the Logins and other Jobs and executes them on the Secondary via the Linked Server. You can even have the Job synchronize itself to the other servers as well. It of course should first check if the objects you're synchronizing already exist and take the appropriate action when they do.
You might find this article Synchronize logins between Availability replicas in SQL Server Always On Availability Groups helpful, especially the Use stored procedure sp_help_revlogin and sp_hexadecimal (which references this Microsoft BOL) section which discusses a Microsoft stored procedure you can utilize to transfer Logins between servers as well. You probably can simplify the job above by having it leverage this procedure.
As far as synchronizing the other Jobs, you might be able to do so with the meta-data stored in the the msdb Jobs tables, e.g. sysjobs, or this StackOverflow answer seems to have found a way via T-SQL. You might also find the Microsoft article Sync Logins and Jobs helpful as well.
Finally, depending on how many Secondary Replicas you are planning to setup, or if you have an affinity to SSIS, you might find it cleaner and more manageable to create an SSIS Package that does all of this (which is also discussed in the first article above) as opposed to using Linked Servers.
Either way, this is a moderate difficulty task to setup, but definitely accomplishable once you start diving into it.


It looks like you need SELECT ... FOR UPDATE

Pseudo code:

START TRANSACTION;
SELECT some_column INTO @value FROM table_name WHERE ... FOR UPDATE;
IF @value = 'some value' THEN
  INSERT INTO table_name VALUES(...);
END IF;
INSERT INTO referenced_table VALUES(...);
COMMIT;


statement to find at least a year with register users > 1

You need to GROUP BY year, and use a HAVING clause to further limit only the grups where the count or rows (count(*)) is greater than 1:

SELECT
    year
FROM
    users
GROUP BY
    year
HAVING
    count(*) > 1
ORDER BY
    year ;

You get:

| year |
|------|
| 2016 |

You can check it at SQLFiddle


Replicate tables to different database name

Yes, there is. Put this in your my.cnf file:

replicate-rewrite-db="dbname1->dbname1test"

See Replication Slave Options and Variables: replicate-rewrite-db


Inserting date from string: CAST vs CONVERT

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done with convert(date, GETDATE()+num,20) -- a value to convert that changes per row -- while the CAST operation is being done with a simple cast(GETDATE() as date) -- a value to convert that is consistent across all rows and is replaced in the execution plan as a constant. And in fact, looking at the XML execution plan even shows the actual operation performed as being CONVERT(date,getdate(),0) !!

Insofar as my testing shows (after making them equal via using cast(GETDATE()+num as date)), the times varry with them being mostly the same (which makes sense if they are both reduced to being CONVERT anyway) or the CONVERT winning:

SET STATISTICS IO, TIME ON;
    ;with t as (
               select convert(date, GETDATE(),20) as fecha , 0 as num
             union all
             select convert(date, GETDATE()+num,20) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9031 ms,  elapsed time = 9377 ms.

-- VS

SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE() as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

--2016-08-26
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 8969 ms,  elapsed time = 9302 ms.

SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE()+num as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9438 ms,  elapsed time = 9878 ms.

The main difference between CAST and CONVERT is that CONVERT allows for the "style" to be specified. The "style" not only allows for tailoring the output when converting a non-string to a string, but also allows for specifying the input format when converting a string to a non-string:

SELECT CONVERT(DATE, '5/10/2016', 101); -- 101 = mm/dd/yyyy
-- 2016-05-10

SELECT CONVERT(DATE, '5/10/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-10-05

Now compare that functionally with CAST:

SELECT CAST('13/5/2016' AS DATE);
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.

SELECT CONVERT(DATE, '13/5/2016', 101); -- 101 = mm/dd/yyyy
-- Msg 241, Level 16, State 1, Line 76
-- Conversion failed when converting date and/or time from character string.

SELECT CONVERT(DATE, '13/5/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-05-13

One additional thing to mention about CAST: because it does not have the "style" parameter, the format of the date string passed in is assumed to be that of the current culture (a session property). The current culture is denoted by the @@LANGID and @@LANGUAGE system variables. This means that the CAST statement that failed in the test directly above could succeed for a different culture / language. The following tests shows this behavior and how that same date string does work with CAST when the current language is "French" (and would work with several others, based on the values in the dateformat column in sys.syslanguages):

IF (@@LANGID <> 0) -- us_english
BEGIN
    PRINT 'Changing LANGUAGE to English...';
    SET LANGUAGE ENGLISH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 1];
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 2]; -- 103 = dd/mm/yyyy
-- us_english   2016-05-13
GO

IF (@@LANGID <> 2) -- Français
BEGIN
    PRINT 'Changing LANGUAGE to French...';
    SET LANGUAGE FRENCH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 3];
-- 2016-05-13
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 4]; -- 103 = dd/mm/yyyy
-- Français 2016-05-13
GO

-- Reset current language, if necessary.
IF (@@LANGID <> @@DEFAULT_LANGID)
BEGIN
    DECLARE @Language sysname;

    SELECT @Language = sl.[alias]
    FROM   sys.syslanguages sl
    WHERE  sl.[langid] = @@DEFAULT_LANGID;

    PRINT N'Changing LANGUAGE back to default: ' + @Language + N'...';

    SET LANGUAGE @Language;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;


How can I optimize this query over this logging table?

You could try a different query:

SELECT ItemID
  FROM (SELECT ItemID
              ,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY TimeStamp DESC) rn
              ,LocationId
          FROM events
         WHERE TimeStamp < '2017-01-01 11:00'
       ) e1
  WHERE LocationId = 1
    AND rn = 1
;

No promises that this will do any better (it could actually be worse); it's just a different approach.

Also - if it makes sense, you may want to put a lower bound on the possible TimeStamp values; if you can ignore everything more than 12 hours prior to the time you're looking for, that could eliminate a large number of rows.


Cannot get SQLCMD to work regardless of what I try

SQLCMD, when invoked without specifying a server name, tries to connect to a default instance on the local machine. If you don't have one, it displays the first error you are getting. Use the -S parameter to specify a server name.

To use Windows authentication, use the -E flag and you don't need to provide username or password. Example: SQLCMD -Smyserver\myinstance -E

To use SQL Server authentication, use the -U and -P parameters to supply credentials. Example: SQLCMD -Smyserver\myinstance -UsomeUser -PhisPassword

If you specify -U you cannot use a windows user name: this is for SQL Server logins. If you want to impersonate a different windows user, start a command prompt impersonating that user first.

Example:

Open command prompt
Type runas /USER:domain\username cmd
Type the password for your user
Run SQLCMD

You can achieve the same result right clicking the link for cmd and selecting "Run as different user..."


I see "-2" for two of the rows. What does this mean?

It means you have an open DTC transaction managed by an external ITransaction interface but no Sessions currently using it.

you have to use KILL UOW where UOW = UnitOfWork associated with that transaction instead of the session id.

---- in text mode run below query

select 'kill ' , req_transactionUOW
from master..syslockinfo
where req_spid = -2


Update same field from multiple transaction

Databases are designed to support multiple concurrent users.  "Waiting for the other transaction to finish" is mutually exclusive with that requirement.  The ability to support multiple concurrent users is why you use a Database instead of flat files.

You must design your algorithm accordingly.

In your example, you want to serialize this process.  (note:  Serialization is the same as setting the configuration parameter SLOW=TRUE)

You can serialize this process by putting a Row Lock on Table_2 first.  In MySQL (and other RDBMS), you do that by using the SELECT ... FOR UPDATE syntax at the beginning of the transaction.

The process should look like this:

START TRANSACTION;
select count from test_2 where id=1 for update;
INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1;
commit;


Ensure Mongo sharding rebalance latest chuck

Yes, it is balancing "old" data because your choose bad shard key. Shard key should not start with linearly increasing key like time/date/number. Because (of the key) you add always data to the "end" of chunk, chunk is splitted at middle and lower (older) part is moved away.

If you want to distribute your query/insert load evenly to whole shard you could use {pdm:"hashed"} as shard key. Of course changing order of those two keys {hdocid:1, pdm:1} would distribute load too.


Local testing on toy test instance:
show global status like 'memory_used' 276360624
show session status like 'memory_used'; 69608
Looking the code the "global memory used" is the total of all the per connection memory allocations, but exclude actual global allocations like key_buffer, innodb_buffer_pool etc.


not really big choice - it will be normal B-Tree index

with autocomplete You can not use FullText because it work only with full words

at the same time queries like:

SELECT text FROM tag_table WHERE text LIKE 'part_text%';

will use indexes

add:

in case of big column -

You of course could try to realise auto-complete by whole words - there You can create full text index, but not sure - how good it will work, because with fulltext always a lot of variants at the begin of search phrase, and variants how suggested text will be relevant to expected result


Simple question about schema (is this supposed to happen?)

I would confirm that you are connecting using the correct login, and that you have not incorrectly assigned some other permission.

I executed the script that you provided above, and then used execute as to test the select permissions and received SELECT permission denied errors for both (as I would expect).

Adjusted comments slightly, but the following I received what I expected (SQL Server 2012)

/* Create new database */
CREATE database dba_schema
GO

use dba_schema
GO

/* Create new table in dbo schema */
CREATE TABLE dbo.tabela1 ( id int )
GO

/* Add new login */
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user',
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

/* Add new user */
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO

/* Set default schema for user */
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

/* Create new schema */
CREATE SCHEMA schema1
GO

/* Add new table to new schema */
CREATE TABLE schema1.tabela1 ( id int )
GO

/* Impersonate the new login of schema_user and query the tables */
EXECUTE AS LOGIN = 'schema_user';
/* Confirm we've impersonated correctly */
SELECT SUSER_NAME();
/* Run the selects */
SELECT * FROM dbo.tabela1;
SELECT * FROM schema1.tabela1;
REVERT;


Optimization causes tmpdir to fill up with 'deleted' file handle

Short answer:  Can't be done.

Long answer:  There is a glimmer of hope...

Assuming that the optimized table will still be more than 200GB, you cannot successfully run OPTIMIZE TABLE in the small space you allotted.  Are there other things you can delete?  Can you OPTIMIZE the smaller tables first - that might free enough room.  I assume you have innodb_file_per_table=ON?  How big is ibdata1?

Please provide SHOW TABLE STATUS LIKE 'tablename'.

Please explain why you want to run OPTIMIZE.  It is rarely worth doing for InnoDB tables.


Postgres shared_buffers and pooled processes

No, it's still only 1GB of total memory.

The buffer is allocated as shared memory and it only allocated once, but shared between all server processes.

It's basically an "error" in the memory display of the top command, which simply reports the shared memory (that only exists once) for each process.


Best strategies to have a backup of an RDS MySQL database

You may not be aware of this but MySQL RDS allows you to create two things

Read Replicas
Snapshots

What is the difference between them?

A read replica is simply a MySQL Replication Slave that you create from the AWS console. It's just a point and click away. It maintains a continuous backup.
A snapshot is simply an instance copy of an AWS MySQL RDS Instance. This is more of a point-in-time copy of the data. This can serve as a physical backup of the instance.
CAVEAT #1 : You could combine both methods by taking snapshots of read replicas so as not to disturb the production RDS instance. You can take snapshots of a production instance but it might be a little slower than taking the snapshot of the read replica.
CAVEAT #2 : You don't want mysqldumps from RDS because there may be applicable charges for doing so. If you really need access to mysqldumps, switch from RDS to EC2. You just have to be more hands on managing MySQL (Should I increase max_connections in AWS RDS t1-micro for MySQL?) as well as managing the EC2 instance.

Here are some of my past posts on this subject

Dec 11, 2012 : MySQL on RDS, data transfer from one instance to another as a production job
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

UPDATE 2013-05-22 16:21 EDT
Replication lag is a part of MySQL Replication because it is asynchronous. Even though you are using MySQL 5.5, you cannot take advantage of semisynchronous replication features (evidently AWS policy).
If you would like virtual synchronous replication, you may want Percona XtraDB Cluster (PXC), an all-InnoDB virtually synchronous ACID-compliant DB Cluster, since replication lag is forbidden by design (Codership's Galera libraries are embedded in the Server Binaries). You will have to stop using RDS and go with EC2.
Check out PXC's Documentation on how to setup PXC in the EC2 environment.


Can a sublass have two parents (two entity types that are connected with it?)

"Can a subclass have 2 parents(2 entities that connect to it?)".  Yes.  However: Avoid redundant relationships in your ERDs.  For your particular scenario: if the BOOKING entity is connected to STANDARD/EXECUTIVE via ROOM, then you will later be able to write a query whose output tells the user what type of room has been booked.

Regarding your questions:
"For each entity connecting to this sublass, does it have its own instance of it?  So: Will my room entity record if its a standard or executive room, and will the booking entity be able to record if that specific booking was to have decorations or not?"
ANSWER: Yes, a ROOM can have attributes that determine whether it is a "standard" or "executive" (see the model below).  A booking does not change a room's "decorations".

It would be sufficient to model ROOM -< BOOKING (each room may have one or more bookings, same as you have done).  As for the "room types", I would use an ARC ie an exclusive OR (XOR).  The reasoning behind it: each room is either equipped as STANDARD room (with all the necessary attributes) or it has the EXECUTIVE "package" (including all attributes that you need).

There is NO need to draw (a) relationship(s) between STANDARD/EXECUTIVE and BOOKING.
A ROOM gets booked (and each room is either a STANDARD or an EXECUTIVE, but this gets recorded in the respective attribute/column -> see the diagrams (ERD above, relational model below).

If it is possible that a single (unique) booking ID can be used to book several rooms, then you'd have a M:M relationship between ROOM and BOOKING.  (Which would require an intersection.)

(As for the implementation of the XOR - which comes much much later: you can use a CHECK constraint in the ROOMS table in order to force the room to be either "standard" or "executive".)


You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother.

The issue is how will their update scripts react to your table being added/modified?  Usually it's not a big deal for me but sometimes it could cause massive problems, up to silently causing issues which no one finds out until they realize their billing data is wrong or something of the sort.  If you include FKs which you probably will, note that changes to one table might also affect updates.

Problem is you need to actually make changes like this.  My suggestion would be to be open with the vendor, tell them you need this functionality so they add it to their requested features and in the meantime try to get their support on making your own tables in their app's db.  Their answer will most likely be 'we don't recommend it and we don't support it'.  Thus you should always have steps on handling this data before and after upgrades.  You could have processes based on how/when the data is accessed where you could remove the table or FKs and recreate after the update.  You'd need to track the changes diligently.

Edit:

*and hire a QA contractor 1 time or utilize a good one to have tests written before/after upgrades that cover many if not all edge cases if it's really important.


My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

That is not the problem.

The problem is, mysqldump only takes care of this, automatically, if you use it as intended:

mysqldump [options] --databases db1 db2 db3

Otherwise, it essentially assumes any dependencies will already have been met on the server when you restore a dump file, so it does nothing to resolve them except within the individual db being restored.

If you create a single dump file, like this, specifying all the databases you need but using the --no-data option, you'll get a small file with all your tables and views, but no data.  Restore that first, and then your 3 individual files.


Stop using things like varchar without specifying the length. Watch what happens here:

CREATE PROCEDURE [dbo].[Reconciliation_broken]
    @Param1 varchar
AS
BEGIN
    SET NOCOUNT ON;
    PRINT @Param1;
END
GO
EXEC dbo.Reconciliation_broken @Param1 = 'what happened?';

Results:

w

When you don't specify the length, you get silent truncation - in some cases it is 30 characters; in this case, it is 1 character. There is no error message or warning - in fact if you were inserting data here you would be inserting 1 character and who knows how long you would be doing that before you realized. That data would be lost with no hope of recovery.

So, change those parameters to match the data type and length of the underlying columns. For more information, see:

Bad habits to kick : declaring VARCHAR without (length)


How do I rule out SQL Server as the reason for a slow website

I'd suggest you need a more definitive problem statement - is it all web pages? If it's just some, what are those web pages doing? What is the acceptable load time, and what is the current load time? Is it all users, or just some?

I used to be a web admin, and my first check would have been the webserver - is memory and CPU ok? Do the OS and web logs show anything? Can the scripts output a log to show what's going on? eg I used to put the database stuff in one section, and write times/durations for each database activity to a log, when activated.

If you need to check the SQL Server, check the SQL & NT logs, and again check the memory, disc IO, and CPU - task manager will give you an ideal of two of these (not disc IO).  Ideally, SQL Server should be on a dedicated box rather than shared with anything.

Whilst someone is using the website, look at either Activity Monitor or sp_who2 - are you able to see the query dawdling? Is there any blocking? Keep refreshing... if the query is runnable/suspended/running for a while, then you need to find why it is slow. If you can't see a query in those states, it may not be SQL Server.


Misread your post and wrote up a post telling you how to optimize the inserts, which seemed like an odd activity to do. The real query to optimize is this:

select *
from
Person p
where p.Name like 'a%';

That query gets back around 6100 rows out of 100000 rows for me. Even if you create a simple index on the name column it is unlikely that the query optimizer will use it.

create index Person__name ON Person (name);

select *
from
Person p  WITH (INDEX (Person__name))
where p.Name like 'a%';

Disclaimer that I'm only using that hint for testing purposes. You don't want to use something like that in production unless you really know what you're doing. That hint forces the optimizer to use the index but I get worse results than not using it. If you truly need that query to be faster my only ideas are that you'll need to create a covering index, you'll need to change the structure of the table, or maybe there are some small gains to be had by writing the filter condition in a different way.

Here is what I mean by a covering index:

create index Person__name ON Person (name)
INCLUDE (StreetAddress, Height, CompanyId)

That index avoids the key lookups that you get with a simple index because every necessary column is included in it. However, the size of that index will be similar in size to the table. Any DML on the table will be slower because the index needs to be maintained.

You could change the clustering index from ID to name. That would have significant impacts on DML performance and on other queries, but it would make your query faster.

Perhaps instead of LIKE there is another string operator that you can use. I doubt that such changes would have a significant impact on your query's runtime.


PostgreSQL, PostGIS: Copying a schema with a geometry column

Adam,

First that is old document -- new one is here: http://postgis.net/docs/AddGeometryColumn.html

Though what they say in both is the same.

The using that function is only need for 1.5 and below.  2.0 we encourage you to just create the table normally.

something like

CREATE TABLE test(gid serial primary key, geom geometry(POLYGON, 4326) );

pg_dump works fine either way since in old version it would restore the constraints and new version the create table would be built with typmodifiers.


The advantage of using an auto increment field is that new records are automatically given an unique primary key.

  Suppose the data of a student has entered in a student table. If I enter that student data again, it will be ok since it create another id automatically. But I have storing the same data unnecessarily.

It's your duty to take care that the same data is not entered again i.e. verify that the student exists and, if yes, edit his record instead of creating a new one.

  If I can identify a primary key, I do not need to use that?

Yes, if you can identify a natural primary key (e.g. the student ID in an University, the ISBN for a book, the S/N for a piece of hardware) it is much better to use that one.

  For example, For a Hotel table, I can use the combination of Hotel name and address as a primary key.

It is a bad idea to use primary keys which contain meaningful data. What happens if the hotel changes its name? In this example, it is better to use a surrogate primary key.


Is normalizing further to index on INT FK rather than VARCHAR worth it?

integer is typically a bit smaller and faster than character types in every respect. The difference mainly depends on the length of the strings.

You asserted that the text data is unchanging, else this would be a strong case for using a surrogate key instead (integer or whatever).

Circumstances that would still make me lean towards an added integer surrogate key:

Strings are much larger than the 4-byte integer. Little to nothing to gain with short strings. The benefit grows with the avg. string length.
The key is used in multiple indexes and/or FK columns, so benefits add up.
You work with a locale other than C (the typical case) and you need rows to sort in immutable (obvious) fashion. The sort order of character types depends on the current locale settings (COLLATION).
All other tables in your DB work with integer keys and you'd rather not break the pattern.

On the other hand, with Postgres 9.5 or later you can benefit from the "abbreviated keys" feature. But only if you work with locale C on a *nix OS. That would tilt the scales towards using original strings.

Related:

Indexes: integer vs string performance if the number of nodes is the same
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
Slow query ordering by a column in a joined table
What is the overhead for varchar(n)?


Prevent postgres function of running more than 1 instance at the same time

Do the exclusion in the cron job definition. The handy flock shell command is intended for exactly this.

To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE, do a SELECT ... FOR UPDATE of a particular row, or use pg_advisory_xact_lock. See the explicit locking chapter. The main advantage of using an advisory lock is that you don't need to create some table/row just to lock it in the case where there's no "natural" candidate for a lock target.

You can use the try forms of the advisory lock functions, or NOWAIT locks, if you want to abort a run instead of queueing it up. If you're using psql remember that by default it continues on error instead of aborting, so use psql -v ON_ERROR_STOP=1 in combination with NOWAIT.


script task sssis file extension is missing?

Have you tried just adding the extension to the variable like this?

sFlatFileConnection = sFlatFileConnection + dtRunDate.ToString("yyyy_MM_dd") + '.txt';


PostgreSQL Database Data File Integrity Check

PostgreSQL prior to 9.3 did not have block checksums. The feature was added in 9.3 (long after this question was posted).

To answer your needs, I would probably develop my own checksumming (triggers?) - working on attribute values, not data pages.


What can be represented in a relational database that can't be represented in a graph database?

Disclaimer: I am not a computer science academic. There are undoubtedly nuances and implied knowledge in that paper of which I am ignorant. I do, however, have some small practical exposure to these technologies.

To say that graph is a subset of relational would mean there is something that can be done in relational that cannot be done in graph. I would disagree with this. Data can be stored equally in either of these systems. Any graph can be represented relationally by a Node table and an Edge table (perhaps with additional NodeAttribute and EdgeAttribute tables) 1. Any relational database can be stored in graph form with one node per row, one attribute per column and one edge per foreign key. Non-binary relationships can be represented by inserting a junction node to which the multiple edges connect.

I know little of Logic stores (which I believe are also known as triple stores) but they seem to have a very direct translation to graphs and, hence, to a relational model.

As for standards, they only exist because some people decided to sit down and write them. There is nothing inherent in the structure of the universe that caused SQL to come into being. An equal effort could produce standards for graph processing. Languages like Gremlin and Cypher seems to be gaining broad acceptance and may, eventually, lead to a ISO standard graph language. The maths that underlies graph is just as valid as set theory.

The relational model has concepts like schema, primary key and referential integrity which are not part of the graph model. There is no reason why an actual graph DBMS could not implement these features, however. Indeed there are many databases hosted in relational software which are unnormalised, lack primary keys and have no DRI!

Graphs allow traversals of indeterminate length. Some RDBMs allow recursive queries which, although more cumbersome, can accomplish the same effect.

It seems to me these systems differ not so much in the data they can store, but in the ease with which their underlying structures can answer different classes of questions. Relational database engines are optimised to process sets of similarly-structured items (tables). Graphs are optimised for traversing links from node to node.

1 SQL Server 2017 has implemented graph processing with precisely this form. It has added extensions to identify a table as containing nodes or edges.


I have seen setups that run 2 AG's in a 3 node cluster. Usually all the databases are running on the primary server, but they can (and do) failover each AG separately. The setup will work, the question now is, do you want to do it?

Some problems you will have to consider:

Extra admin of running and debugging 2 AG's (and confusion of what runs where)
Extra overhead on SQL of running a 2nd AG (minimal impact for the small number of databases)
Extra licensing costs. SQL isn't cheap, if you have Software Assurance you can run a non-readable secondary for free. If you want to split the load you need to fully license both servers. (Note: I'm not a licensing expert)
The big one: Can both servers run a full load on all of the databases should 1 server fail?

The last point is the important one, and the one that makes this setup less desirable. If you are splitting the 2 AG's across 2 servers because 1 server doesn't have the resources, then what are you going to do when 1 server crashes and the AG fails over? You now have one server running all of the databases. Can that server cope with the load? If so, keep it simple and use it all the time. If not, your AG is not going to do the HA/DR that you'd hoped it would.

One scenario I did see where this setup worked was a database running on the secondary that consumed a lot of CPU due to batch processing. The business decided that should either node fail, they wanted to still be able to query the data (very small load on SQL) but were happy to stop the batch processing of new data. They chose to go with 2 AG's and added a whole lot of code to their batch processing so it could turn itself off when it detected both AG's were being served from the same server. In normal operations this took a heavy CPU load off their primary OLTP server, but at the cost of having to stop a fairly important process should 1 node fail.


The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but

SELECT ... FROM person;

is effectively the same as

SELECT ... FROM ONLY person
UNION ALL
SELECT ... FROM praktikant;

Look at the EXPLAIN output for both queries to confirm that.

Each of these tables has its own constraints and indexes, which don't affect the other table.

So the answers to your questions would be:

The tables are completely independent, except that

praktikant must have all columns of person
querying person will include rows from praktikant

That question is not quite clear, but I assume you can answer it yourself from the above. Locks are taken on the rows of each table independently.
Not at all. Also, a constraint on one table does not affect the other table. Specifically, you cannot guarantee uniqueness of entries across the two tables.
There are few real-world uses. Before the advent of declarative partitioning in v10, it was used to implement partitioning in PostgreSQL.


Deploying SSRS reports on an "Air Gapped" report server without SSDT?

You could use a RS.exe script. This would allow you to create a script, a batch file and a set of .rdl files.
Then you just call rs.exe with the script you created to upload the files to the report server.

You can find an example here

There is also a tool called rsscripter.exe created Jasper Smith, a SQL Server MVP.
Apparently it is now included in finebuild and the zip is still archived. See this link for the original article.


Initial Full Backup required for Filegroup Backups?

I think what you're looking for is PARTIAL backups, which backup read/write filegroups (including PRIMARY) and any specified read-only filegroups. For your requirement, you could perform a PARTIAL backup of the 4 read/write filegroups plus PRIMARY and a File Backup of the 8 read-only filegroups.

Your file backups would not need to be performed again unless A) you change them to read/write, or B) your file backups become damaged. Beyond that, the file backups can be used for any restore going forward as needed.

Your PARTIAL backup of the PRIMARY and read-write filegroups can then be used as the source for PARTIAL restores or as a differential base for DIFFERENTIAL PARTIAL backups.

NOTE: The PRIMARY filegroup contains system tables and database metadata, including metadata about the state of other database files/filegroups, so you should always include this in your regular backup processes, it can't be backed up once and retained for future reference like the read-only filegroups. If you've designed your database correctly, you would ideally have little to no user data in the PRIMARY filegroup anyway, so its includion in PARTIAL backups shouldn't make much difference to performance or space consumption.

EDIT:

As per Peter's comment, I wanted to include a quote from the documentation:

  Partial backups are useful whenever you want to exclude read-only
  filegroups. A partial backup resembles a full database backup, but a
  partial backup does not contain all the filegroups. Instead, for a
  read-write database, a partial backup contains the data in the primary
  filegroup, every read-write filegroup, and, optionally, one or more
  read-only files. A partial backup of a read-only database contains
  only the primary filegroup.

So with a PARTIAL backup, you're backing up all read/write filegroups, including PRIMARY, without exception. It is possible to include read-only files or filegroups as well, but all read/write are included in a PARTIAL backup automatically.


MongoDB convert standalone mongod to sharded replica set cluster

You have config server replica set installed?
You are running at least one mongoS -service
You are connecting (with mongo) to mongoS when you are working with cluster
You have added your replica set(s) to the cluster with sh.addShard() -command
You have added (at least one) your databases to cluster with command sh.enableSharding("yourDBname")

This point your collection is still there, sitting on original location with all data. You still need to shard your collections, which data is needed to "spread" to other shards (nodes).


MySQL failover: how application can be switched to a new master in master-slave configuration?

We had faced a similar kind of problem. A Multi-AZ MySQL-RDS provides an automatic failover feature. Everytime a failover occurred, although the URL of the new master would remain the same but still JDBC was not able to connect to the new master.

By default, once a connection to database is made some setups will never refresh DNS entries until the JVM is restarted. This can be solved by setting the networkaddress.cache.ttl parameter of JVM. Setting this value ensures that the DNS entries are refreshed after some interval. In case you are not using Java, try to find an analogous parameter for your connection.

In other words, first ensure that the URL of the new master remains the same(you need to talk to your operations team to understand how it can be done). Once that is done, post failover discovering the new master becomes the responsibility of the application itself which can be achieved by refreshing the underlying DNS entries periodically.


SQL Availability group IO prioritisation

Will IO for the primary workload be prioritised over the replicated data, assuming all disks are shared?

No, I have not heard any such thing that SQL Server will prioritize certain IO over other. You have an option to set High Priority for SQL Server by using sp_configure but it is not recommended and mostly counter productive.


PostgreSQL Size Quota on Table or Schema

You can create a trigger that checks the number of records in your destination table when an INSERT is used.

The trigger function would look like this:

CREATE OR REPLACE FUNCTION check_number_of_row()
RETURNS TRIGGER AS
$body$
BEGIN
    -- replace 100 by the number of rows you want
    IF (SELECT count(*) FROM your_table) > 100
    THEN
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table'
    END IF;
END;
$body$
LANGUAGE plpgsql;

And trigger will be like this : -

CREATE TRIGGER tr_check_number_of_row
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();

There is no other way i think to set table size in postgresql but u can restrict row limit on table


Index optimization for IN, BETWEEN, ORDER BY and LIMIT query

Shrinking the table size will help performance:

Don't use BIGINT unless you expect to need more than 4 billion ids; INT UNSIGNED takes half the space.
Don't use INT (4 bytes) for low-cardinality flags/types/levels(?)/etc.  ENUM or TINYINT UNSIGNED is only 1 byte.

Write a little more code to avoid useless parts to the WHERE, such as AND filter.metrcs_1 BETWEEN 0 AND 9999999999.

Pagination via OFFSET (LIMIT 5000, 50) is problematic.  See my blog

Don't USE INDEX or FORCE INDEX; it may help today, but it is likely to hurt tomorrow.

It is rarely useful to start with a column that will be used in a 'range':  KEY indx_posted_on (posted_on,source_id)

For that query: INDEX(type, source_id, posted_on),INDEX(type, posted_on).  The first of those covers the case where you have only onesource_id.  (Does such happen?).  The second one is better thanINDEX(posted_on)`.

More on optimal indexes  To summarize:  =s first, optionally INs, and one range last.  But the IN keeps the ORDER BY from being consumed.

Also, be sure that innodb_buffer_pool_size is about 70% of available RAM; this may help cut back on I/O.

UPDATE (based on OP's UPDATE):

INDEX(source_id, posted_on) -- IN + range -- should help with WHERE
INDEX(posted_on) -- part of WHERE, plus may consume ORDER BY

The Optimizer will pick between the two.

If AND metrics_1 BETWEEN... is added in, the performance will be worse.  So, keep it out of the query if the user does not need it.

Since you have a large set of possible queries, I recommend building a few indexes:

INDEX(posted_on) -- to handle the ORDER BY, in case nothing else works well
INDEX(const, posted_on) -- to partially handle WHERE, plus ORDER BY
INDEX(const, range) -- a few 2-column indexes;
          first is something used with '=', second is BETWEEN (or other range)
INDEX(in, range) -- a few more 2-column indexes;
          first is something used with 'IN', second is a range

Aim for no more than 10 total.  Watch what users usually ask about as a guide to which 'const'/'in'/'range' columns to index.  And/or look at what queries are "too" slow.

Don't say filter.* unless you really need all the columns.  In particular, if all TEXT columns can be omitted, there may be a performance gain in how temp tables are handled.

For large tables with text columns, this "lazy eval" may help performance:

SELECT filter.*
    FROM (
        SELECT primary-key-fields-of-filter
                  rest-of-existing-SELECT
                  order-by-limit
         ) x
    JOIN filter USING(primary-key-fields-of-filter)
    ORDER BY repeat-the-order-by-but-not-limit


Change table from InnoDB to ARCHIVE in MySQL

Since the ARCHIVE Storage Engine does not support indexes of any kind, your main problem is the presence of the  PRIMARY KEY. Simply drop the PRIMARY KEY. Then, convert the Storage Engine.

There are two approaches to this

APPROACH #1

ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;

APPROACH #2

CREATE TABLE date_obook_new LIKE date_obook;
ALTER TABLE date_obook_new DROP PRIMARY KEY;
ALTER TABLE date_obook_new ENGINE=ARCHIVE;
INSERT INTO date_obook_new SELECT * FROM date_obook;
ALTER TABLE date_obook RENAME date_obook_old;
ALTER TABLE date_obook_new RENAME date_obook;
DROP TABLE date_obook_old;

GIVE IT A TRY !!!

SPECIAL NOTE

If anyone has a PRIMARY KEY with the AUTO_INCREMENT attribute, you must remove the AUTO_INCREMENT attribute first. Then, remove the PRIMARY KEY, Then, convert the Storage Engine. For example, if market_id was defined as int(6) NOT NULL AUTO_INCREMENT, do the following:

ALTER TABLE date_obook MODIFY market_id INT NOT NULL;
ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;


SQL Server Threshold of Write per X when considering Indexes?

To add to it, I just assumed that there was some sort of "sweet spot"
  that could be evaluated by looking some numbers. How likely/difficult
  is it to generate that information?

I do not think that there is a golden ticket measure to get the index writes <> index reads.

One of the reasons for this is that the sys.dm_db_index_usage_stats dmv explained below might show:

  10 USER_SEEKS vs. 200000 USER_UPDATES.

When looking at this difference the knee jerk reaction would be to say remove this index as it is not used that much.

But these 10 index seeks might be making a very important query go from 5 minutes to 5 seconds. While these 200000 user inserts might be single insert statements on a very big table.

These inserts might even have happened during non production hours for all we know.

Things to look at

This is not a complete list, simply listing a few things you could look at

Index Usage stats

The index usage stats from the sys.dm_db_index_usage_stats dmv Query source (Greg Robidoux) to get a basic idea of the SEEKS / SCANS / LOOKUPS vs. USER_UPDATES.

I would use this one to check if the index is even used and to get a basic idea of the  READS <> WRITES

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1;

Note that these counters reset each time you restart SQL Server.

Datatypes & Table Size (+ Future growth)

After this, I would look at the datatypes of the key columns / included columns you want to create the index on + the total Data size / Index size to get a general idea of the amount of data you are adding to your database.

An example:

The total data space of my table = 6MB, and I only have 0,1MB index space.

In most cases, indexing this table to remove a query that gets executed a lot and uses too much resources would be a no brainer.

Most of the time, a query you need indexed would be on bigger tables.

In these cases it comes down to:

The data you are indexing (INT?, VARCHAR(100)?, datetime?,...)
The size of your table
The queries / access ratios on the clustered index / heap table.

2 thirds of these you can easily find, the other one you can find with the DMV and by monitoring the queries executing on this table.

After validating this, and proceeding

These two points are not the only ones to look at, there is much more than this.

Another important part is to test & monitor

Most of the time, you will not be 100% sure when applying indexes, it comes down to using common sense and looking at the most obvious parts. The unexpected can always happen.


Settle debate between Professor and Myself on interpreting Crow's Foot Notation in an ERD

Your professor is wrong.  The cardinality in almost all versions of crows foot notation is read such that the cardinality next to the entity is the cardinality of that entity.  You can also see by the fact that common sense tells you that whoever drew this ERD did not intend what your professor is reading.

I will say that I have (very rarely) seen people read cardinality the way your professor is doing it.  I think of it as "looking down the relationship" at the other side.  The fact is that this approach doesn't make sense for readability in my opinion.  It's also just not the way the vast majority of people do it.

You're better off to stick with the conventional wisdom on this.  If you can't convince your professor, then go with the flow until the end of the semester, but then forget this inappropriate convention and do it the way almost everyone else does from then on.

As an aside/pro-tip: when you see many/optional cardinality, I'd suggest you get in the habit of reading it "zero or more", not "zero or many".  I think "zero or many" kind of implies "not 1", which isn't the nature of such cardinality.


select floor(days / 20) as daygroup, count(*)
from mytable
group by daygroup


Schema for storing historical data on shared identifiers

Looking at the data you posted, what would be the expected result from your point of view?
The fact that users share the same desk/IP shouldn't be a problem as long as both users are reported while they're logged in.
You could either use your current table design (a single table with Row_Id, update_time, IP and User) or an aggregated table with Row_Id, IP, User, login_tim, and logout_time.

I'd decide based on the expected number of unchanged logins. For example, if users usually login in the morning and logout in the afternoon, then I'd go for the aggregated version. But if the scenario is more like a user to log in and log out within a short period of time and a high frequeny, then I'd go for the current design since aggragation wouldn't reduce the number of rows significantly.

How many rows do you expect per day (given your current design)?


PostgreSQL not using the index on queries that return large number of records

That is just as it should be.

If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway.

So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.


If that happens only on one database, and the databases are comparable in size, then the problem is probably locking.

There must be other (long running) transactions that modify rows in the table. If your statement tries to modify the same row, it has to wait until these concurrent transactions are finished.

Examine the pg_locks view for entries that are not granted: these correspond to blocked database sessions.


How calculate the time difference between two consecutive rows

This is a lot of code with tables selecting on tables and a dynamic pivot statement at the end. Let me know if you have any questions or any things you want me to change.

SELECT  ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY ownerdate) Ticket_OrderID,
        ticketID,
        [owner],
        ownerdate INTO #tempTable
FROM yourTable
--WHERE [owner] IS NOT NULL

SELECT  A.ticketid,
        'timediff' + CAST(ROW_NUMBER() OVER (PARTITION BY A.ticketID ORDER BY A.ticketID,A.ownerdate) AS VARCHAR(100)) time_diff_owner,
        CAST(DATEDIFF(MINUTE,A.ownerdate,B.ownerdate) AS VARCHAR(100)) + 'min' +
        CAST(DATEDIFF(SECOND,A.ownerdate,B.ownerdate)%60 AS VARCHAR(100)) + 'sec' time_diff_sec
        INTO #tempTable2
FROM    #tempTable A
INNER JOIN #tempTable B
ON  A.Ticket_OrderID = B.Ticket_OrderID - 1
    AND A.ticketID = B.ticketID

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(time_diff_owner)
            FROM #tempTable2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query =
'SELECT ticketid,' + @cols + ' FROM #tempTable2 A
PIVOT (
     MAX(time_diff_sec) FOR time_diff_owner IN (' + @cols + ')
) pvt'

EXEC (@query)

DROP TABLE #tempTable
DROP TABLE #tempTable2

Results:

ticketid timediff1      timediff2       timediff3       timediff4       timediff5
---------------------------------------------------------------------------------
1001     1min43sec      5min18sec       107735min59sec  221682min17sec  16min34sec
1002     106599min59sec 34251min23sec   188557min57sec  5234798min59sec 1min48sec
1003     14min5sec      2865min38sec    31394min56sec   NULL            NULL
1004     34196min44sec  NULL            NULL            NULL            NULL
1005     153min14sec    3min9sec        2826min1sec     18245min51sec   11min53sec


Why does TSQL return the wrong value for POWER(2.,64.)?

From the online documentation:

POWER ( float_expression , y )

  Arguments

  float_expression
  Is an expression of type float or of a type that can be implicitly converted to float

The implication is that whatever you pass as the first parameter is going to be implicitly cast to a float(53) before the function is executed. However, this is not (always?) the case.

If it were the case, it would explain the loss of precision:

  Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

On the other hand, the literal 2. is type numeric…:

DECLARE @foo sql_variant;
SELECT @foo = 2.;
SELECT SQL_VARIANT_PROPERTY(@foo, 'BaseType');
GO

| (No column name) |
| :--------------- |
| numeric          |

dbfiddle here

…and the multiply operator returns the data type of the argument with the higher precedence.

It appears that on 2016 (SP1), all the precision is retained:

SELECT @@version;
GO

| (No column name)                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
        Oct 28 2016 18:17:30
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
 |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709551616 |

dbfiddle here

…but on 2014 (SP2), they are not:

SELECT @@version;
GO

| (No column name)                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
        Jun 17 2016 19:14:09
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
 |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709552000 |

dbfiddle here


What's the best database if i want save data temporally?

I'd look at SQLite if I were you but if you are you going to perform many analyses on this data - i.e. windowing functions -or CTEs (common table expressions), in that case, I'd go with PostgreSQL.


Yes, this is possible:

First install the package. On Redhat and friends, run this command:

sudo yum install MariaDB-connect-engine

Or on Debian/Ubuntu and friends:

sudo apt-get install mariadb-plugin-connect-engine

(Or should that say 'mariadb-plugin-connect'?)

Then log in to MariaDB and install the plugin:

INSTALL SONAME 'ha_connect';

Now create the CONNECT table locally:

CREATE TABLE my_connect_table (
   col1 type1 -- same columns as in your remote table
   ...
)
ENGINE=CONNECT TABLE_TYPE=MYSQL dbname=the_db_name tabname=the_table_name
CONNECTION='mysql://the_username:the_password@the_hostname';

Now you can SELECT, INSERT, UPDATE, DELETE etc. with that table as you wish. If you DROP this table, then you only DROP the local CONNECT table, not your remote table.

For reference, see:
CONNECT MYSQL Table Type: Accessing MySQL/MariaDB Tables

You might also want to consider the MariaDB Spider storage engine. The Connect engine can use any remote database system (Oracle, MSSQL, PostgreSQL, ...) if you install the ODBC driver, or it can even be e.g. a CSV file instead of an actual database system. The Spider engine is only for remote MariaDB databases. However, the advantage of Spider is that it has sharding features (i.e. allows distributing the data in a table across multiple servers) and supports XA transactions.


SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me

Microsoft has an upgrade strategy for changing the compatibility mode on SQL Server 2016. Quoting the linked article:

The recommended workflow for upgrading the query processor to the latest version of the code is:

Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)

Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.

Wait sufficient time to collect representative data of the workload.

Change the compatibility level of the database to 130

Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change

For cases where there are regressions, force the prior plan in the query store.

If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.

You could try a version of that for your situation. Change the compatibility mode back to 100, enable the query store, go through a full business cycle and get a good baseline, then change compatibility mode and use the query store to analyze poorly running queries and take further action on them.


Return rows that meet a condition in two different columns

WITH cte AS ( SELECT *, COUNT(*) OVER (PARTITION BY color, smell)  cnt
              FROM animals )
SELECT name, color, smell
FROM cte
WHERE cnt > 1

PS. color and smell must be NOT NULL.


Complete list of possible database "objects"?

Some more:

domains
synonyms
sequences
rules
constraints
assertions (though I don't know of any DBMS supporting them)
indexes
roles
user defined (object) types


File / filegroup restore vs database / log restore in SQL Server

Well, here is a simple example.  You have a large database, but most of the data is history.  You go ahead and partition out the historical data onto read only filegroups.  This information is used on a regular basis for reporting but not for the actual application.  It is, lets say, 90% of your database.

If there is a problem you can restore the read/write portion much more quickly to get the application up and running.  Then once your application is functioning you can restore the read/only portion (which of course will take much longer).


How do I know if my PostgreSQL is up do date with DST rules?

Yes, there is a schedule. It's documented here: https://www.postgresql.org/support/versioning/

  if need be, security fixes that are released at least once every three months in what we call a "minor release". For more information on the minor release schedule, you can view the minor release roadmap

And the minor release roadmap currently (as of 2019-07-08) shows:

  The target date for these releases are, unless otherwise stated, the second Thursday of February, May, August, and November. The current schedule for upcoming releases is:

     * August 8th, 2019
     * November 7th, 2019
     * February 13th, 2020
     * May 14th, 2020

Every minor release will include updated DST rules if necessary.

The last minor release for Postgres 11 was 11.4 on 2019-06-20. I would have assumed that it included the new rules for Brazilian DST, but I can't find any mentioning of that in the release notes as there was e.g. for the 11.3 and 11.2 releases.

So most probably, 11.5 (scheduled for August 8th) will include that.

If you want an authoritative answer from the devs, you should ask that on the Postgres mailing list

Postgres can be compiled in a way that it uses the operating system's time zone data. You can check if your Postgres version was compiled with the --with-system-tzdata option by running pg_config


Create a view containing multiple rows per customer

You can use a LATERAL join to rewrite the view:

CREATE VIEW tasks AS
SELECT c.customer_id, t.*
FROM customers AS c,
     LATERAL
     (   SELECT  1::int, c.created, c.confirmed
       UNION ALL
         SELECT  2, ---
         WHERE ---
       ...
       UNION ALL
         SELECT 12, c.invoice_paid, c.invoice_registered
         WHERE c.has_invoice
     ) AS t (task_id, since, done)
WHERE c.cancelled IS NULL ;

Adding a filtered index on (customer_id) WHERE (cancelled IS NULL) should  improve efficiency (perhaps of the existing view as well, although I'm not sure if the condition will be pushed down through the UNION) for the query you use:

SELECT * FROM tasks WHERE customer_id = 12 ;


SQL Agent Jobs - What Permissions Required

SQLAgentUserRole allows users to create jobs and to manage jobs that they create.

The SQLAgentOperatorRole allows users all the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

Member of SQLAgentReaderRole role allow the user to see any SQL Agent job located on the server instance, even the jobs in which that user does not own. The user can see the job, along with any history saved to the job. However, the group is not allowed to execute the jobs


Index seek much slower with OR condition compared with separate SELECTs

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), whereas the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

That said, it seems you have a table with IDUkazatel values rather than an explicit OR clause. The code below will work with that arrangement, simply replace the table name @T with the name of the table containing IDUkazatel values:

SELECT
    MinCas = MIN(CA.PartialMinimum)
FROM @T AS T
CROSS APPLY
(
    SELECT
        PartialMinimum = MIN(PD.Cas)
    FROM dbo.PenData AS PD
    WHERE
        PD.IDUkazatel = T.IDUkazatel
) AS CA;

In an ideal world, the SQL Server query optimizer would perform this rewrite for you, but it does not always consider this option today.


Identity column value falling behind randomly

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Inserts are probably stopping because of an attempt to reuse an already existing unique value in the PRIMARY KEY, thus triggering the error like:

Msg 2627, Level 14, State 1, Line 27
Violation of PRIMARY KEY constraint 'PK__ID__1234'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (8).
The statement has been terminated.

Note that if your IDENTITY column does not have a UNIQUE index or constraint, it is possible to reseed repeatedly and have many identical ID values. You do not want to do that, of course.

I have not personally found an error that, in itself, would reseed the IDENTITY value.  Of course, it is possible to reset the SEED to a range where there will soon be a conflict by running a reseed that is lower than the current seed:

DBCC CHECKIDENT( MyTable,RESEED, 7) WITH NO_INFOMSGS

It could be that code somewhere in one of the processes actually does a RESEED on the table under some unusual circumstances.

(For example, this could be from a merge of two data sets, where the code reads the high value from one data set and after the import RESEEDs to the lower of the two high values that were merged.)

You should also read Martin Smith's post at: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database


How to use case and filter with a group by statement and having an aggregate calculation?

FWIW, I think you will be way better of separating the 2 cases for PO.col3, and combine the results with a UNION (which I assume is what you had before). Meanwhile, I tried to fix your query so that it compiles in your fiddle:

SELECT * FROM (
    SELECT
        PO.col1,
        PO.col2,
        CASE WHEN PO.col3 <> 8 THEN PO.col4::float
             WHEN PO.col3 = 8
             THEN CASE WHEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float))) > 0
                        AND SUM(CAST(PO.col5 AS float)) > 0
                       THEN (CAST(PO.col4 AS float) - SUM(CAST(PO.col5 AS float)))
                       END
             END AS Quantity
    FROM mytable AS PO
    GROUP BY PO.col1, PO.col2, PO.col3, PO.col4
) AS T
WHERE Quantity > 0

I have no idea whether it is correct or not, for that I would need a small sample and an expected result.


MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

Query 1:  Since id is the PRIMARY KEY, it is unique.  Hence the GROUP BY id does nothing.  Remove it.  This may make it run faster.
Query 2 does not use any columns other than id from car_makes.  The only thing that the JOIN does is to verify that there is a row in car_makes for the make_id.  You probably don't need that check, so get rid of car_makesin that query.  That will simplify things.  Note that currently there is a "filesort".  Withoutcar_makes`, that step will probably go away.
As for "why can't it use the same index" -- The STRAIGHT_JOIN forces it to look at the other table first.  This effectively turns the second table into
WHERE     make_id = ...
GROUP BY  id
ORDER BY  horsepower_peak DESC

To optimize such, it must first filter on make_id.


Should I join all of my polygons to make a single polygon for point-in-polygon search?

How about ST_Contains() or ST_Within()?

https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html

Perhaps you need AND ST_Distance(...) > 0?

More likely, you want AND NOT ST_Touches(...).


Help to create indexes to improve query performance and reduce report generation time

As you are running a lot of (MEM_Name IS NOT NULL) against the memory table I would look to create a filtered index on this table to begin with.

CREATE NONCLUSTERED INDEX Memory_MEM_Name
ON MEMORY (Index Fields here)
WHERE MEM_Name IS NOT NULL

in most occations every table should have a least 1 Index.

You should capture a work load trace via Profiler and then run it through the database tuning advisor this will tell you where you should see gains with indexs (and even create them for you)

http://msdn.microsoft.com/en-us/library/ms181091.aspx

When running the DTA it might be a good idea to run it against a test system if you have one rather than an online system. Make the changes it sugests and then run it again to see if the improvements appear.

Note - I would not have called a table master. Master should be reserverd for the important master database.


SQL Server: Transaction log still very large after log backup

The transaction log does not automatically shrink because you did a backup.  This is actually a good thing because processes actually stop when the log grows.  Your log has grown to the current size because you had processes that required the log to grow to that size.  These could have been long running units of work that generated a lot of log records (like index maintenance).  The log 'truncation' you're referring to means that, after a log backup, log space is available for 'reuse'.  Again, this process does not automatically reduce the size of the log.  To reduce the log size,, you'd need to 'shrink' the log file.  Repeated shrinking of the log is discouraged because it typically just re-grows, which affects performance.  If you do 'shrink' the log, you should monitor the growth rate.  Here is a helpful article.  I also found this article that provides additional information.


Will this Database design logic work and what should be used?

I really have to encourage you to look at the way DB schemas are handled by existing CRM systems and consider whether (1) you wouldn't be further off customizing one of these systems and (2) you're not biting off more than you can chew.

Most CRM systems I've seen have extensive customization capabilities built into them.  Typically, this includes the ability to extend and/or modify the definitions of the built-in entities and the ability to add new entity types and relationships.  Many of these CRM systems also have the ability to set up some pretty funky security setups, and some might be able to do something similar to what you're suggesting.

I'd also encourage you to try to pry a business scenario out of this client, as well.  What's got him convinced that sales reps are going to share information?  If they're working together on a team, then existing CRM systems have this covered already.  If not, then I'd be a little suspicious about the premise of information sharing.  Understand that sales reps are going to follow the money with respect to their behavior.  If they're paid to share information, then it'll happen; otherwise, you're going to have to beat them with a stick to share anything.


OBJECTPROPERTY is local to the database the query is run it. So the object_id passed in is resolved against master.sys.objects: but the object_id comes from msdb

So here you have case 2.

On my server, I have 37 matching object_id values between msdb and SomeDBOnMyServer. But the names are different.

USE SomeDBOnMyServer
GO
SELECT
  object_id,
  name,
  OBJECT_NAME(object_id)
FROM msdb.sys.objects
WHERE OBJECT_NAME(object_id) <> name

Obviously, I have a lot of rows where OBJECT_NAME(object_id) is NULL that are filtered here


What is the right tool to process big .xel files (SQL Server Extended Events logs)?

For large XE trace files, I use custom tooling with QueryableXEventData. I've found this to be much faster than XML parsing in T-SQL.

Below is a basic PowerShell that imports selected fields and actions from an rpc_completed event trace into a table in 10K batches. You'll need to include an Add-Type command for the Microsoft.SqlServer.XE.Core.dll and Microsoft.SqlServer.XEvent.Linq.dll assemblies, which will be in your SQL Server installation folder with the exact location will varying depending on SQL version and chosen install location.

$SharedPath = "C:\Program Files\Microsoft SQL Server\140\Shared";
$SqlInstanceName = "";

$xeCore = [System.IO.Path]::Combine($SharedPath, "Microsoft.SqlServer.XE.Core.dll");
$xeLinq = [System.IO.Path]::Combine($SharedPath, "Microsoft.SqlServer.XEvent.Linq.dll");
Add-Type -Path $xeLinq;

if( [System.IO.File]::Exists($xeCore) )
{
    Add-Type -Path $xeCore;
}

Note that there are separate Fields and Actions collections in the PublishedEvent class so you'll need to extract values from the appropriate connection.

# create target table
$connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand(@"
CREATE TABLE dbo.rpc_completed(
      event_name sysname
    , timestamp datetimeoffset
    , statement nvarchar(MAX)
    , username nvarchar(256)
);
"@, $connection)
$connection.Open()
[void]$command.ExecuteNonQuery()
$connection.Close()

# data table for SqlBulkCopy
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add("event_name", [System.Type]::GetType("System.String"))
$dt.Columns["event_name"].MaxLength = 256
[void]$dt.Columns.Add("timestamp", [System.Type]::GetType("System.DateTimeOffset"))
[void]$dt.Columns.Add("statement", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("username", [System.Type]::GetType("System.String"))
$dt.Columns["username"].MaxLength = 128
$dt.Columns["statement"].MaxLength = -1

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("D:\TraceFiles\Log\rpc_completed*.xel")

# import XE events from file(s)
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.rpc_completed"
$eventCount = 0
foreach($event in $events) {
    $eventCount += 1
    $row = $dt.NewRow()
    $dt.Rows.Add($row)
    $row["event_name"] = $event.Name
    $row["timestamp"] = $event.Timestamp
    $row["statement"] = $event.Fields["statement"].Value
    # username is a collected action
    $row["username"] = $event.Actions["username"].Value
    if($eventCount % 10000 -eq 0) {
        $bcp.WriteToServer($dt)
        $dt.Rows.Clear()
    }
}
$bcp.WriteToServer($dt) # write last batch
Write-Host "$eventCount records imported"


SELECT @a := EXISTS ((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1));
IF @a = 1 THEN

       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Trigger");

END IF;

or

IF EXISTS ((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Dark Red");
END;

ELSEIF EXISTS((SELECT 1 FROM load_data WHERE diIndex=1 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","Yellow");
END;

ELSEIF EXISTS((SELECT 1 FROM load_data WHERE diIndex=3 and diMode=0 and diStatus=1)) THEN
BEGIN
       INSERT INTO logs(user_id,button_name,flag,box_name)
       VALUES("83","Plant","1","RED");
END;

END IF;


How to prevent SQL files opening new instances of SSMS 17.9.1?

I have encountered this issue after installing SSMS 18 instead of 17.9. Then I have tried solutions from the list above but none worked. Uninstalling SSMS 18 and installing 17.9 again did not work either. I was finally able to resolve this issue with SSMS 18, but I hope it will fix incorrect behavior of SSMS 17 as well. First of all I've uninstalled SSMS, then I've searched through registry to find and remove all meaningful nodes containing info about SSMS or sql_auto_file. I removed the following nodes (don't forget to backup each of them prior to deleting!):

HKEY_CLASSES_ROOT.sql
HKEY_CLASSES_ROOT\Applications\Ssms.exe
HKEY_CLASSES_ROOT\sql_auto_file
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio
Removed all records for ssms in HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\ApplicationAssociationToasts
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts.sql
HKEY_USERS.DEFAULT\Software\Microsoft\SQL Server Management Studio

Then I've restarted my PC, then installed SSMS 18, then restarted again. After that SSMS opens sql files in existing SSMS instance and explorer has correct file icon assigned (document with small database icon)


Postgres table contains 1 column, and that column is PK. How to insert new record?

Doing a insert .. select max() ... is a bad idea to begin with as it doesn't work correctly in a multi user environment. Plus it would get the sequence behind the serial column out of sync with the already generated values.

What you are looking for is:

insert into bar (bar_id) values (default);

Another option would be explicitly call nextval() when inserting.

insert into bar (bar_id) values (nextval('bar_bar_id_seq'));


increasing disk space for mysql data volume

You say you have bin logging enabled.  This leads me to believe you have replication running.

To minimize down time you could promote your slave to master.  The finer details on how to do this really depends on your setup.  Maybe it will involve updating application configurations, maybe it will involve changing the DNS to your "master server".

Prior to the promotion ensure you slave is configured they way you would like, e.g. not writing binlogs to the data directory.  Make sure all the grants present on the master are on the slave.  Make sure the slave is not running in read only mode when you are ready to bounce.

Prior to promotion you'll want to ensure both the master and slave are in read only mode.   Run show master status; on the slave to see what log file and position you'll use for change master to after you reconfigure and restart the old master to run as a slave.

Once the old slave is promoted to the new master you can take your time to move the old binlogs to a new file system and reconfigure to keep new logs out of the data dir.


How to view full blocking postgres queries?

The length of the query tracked can be found in the parameter track_activity_query_size.

18.8.1. Query and Index Statistics Collector

track_activity_query_size (integer)

  Specifies the number of bytes reserved to track the currently executing command for each active session, for the
  pg_stat_activity.current_query field. The default value is 1024. This
  parameter can only be set at server start.

e.g. if you had track_activity_query_size=16384 in your config, then this would mean that query text up to 16k would be collected.