TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing Query

I know of three main ways of addressing a query performance issue caused by a cardinality mis-estimate:

1. Giving the optimizer more information

The query optimizer generally works better if it has higher quality information to inform the model. Steps here can include updating statistics, creating new statistics, using the RECOMPILE hint to pass along the literal values or variables, or materializing key intermediate result sets to provide better cardinality estimates or indexing.

Rewriting your query to be more clear to the optimizer

This can include simplifying code to remove redundant filters or refactoring it to be more clear to the optimizer. The query looks complex and we don't have the view code so it's hard to say more. There are a few filters in the query that appear to be extremely complex. It wouldn't surprise me at all that the optimizer cannot do a good job with guessing how those filters will affect the results.

3. Taking advantage of SQL Server enhancements

Sometimes there are features that you can turn that will make SQL Server do a better job with your workload. If you aren't using trace flag 4199 you could test this query with it. Trace flag 4199 is a collection of query optimization fixes that Microsoft has done over the years. It is on my default in SQL Server 2016. Trace flag 2301 is a bit less straightforward. It makes some changes to the optimizer around join cardinality estimate and in a rough sense you can say that the optimizer works harder to find a better plan. It is riskier and not nearly as common as trace flag 4199. Might not be practical but worth mentioning that each new version of SQL Server makes changes to improve query performance. In SQL Server 2014 there is a new cardinality estimator model which works better for some workloads.

For your particular query, I also want to note that it's easy to misread the single row estimate that you're seeing. The estimated number of rows that you see on the inner side of the nested loop is the number of rows returned per iteration of the loop. Seeing one row estimated from a nested loop seek is common and often not a sign of a problem.

However, the cardinality estimate for the outer part of the query is a bit off (36269 actual rows versus 6976 rows). It's perfectly natural to see a high number of logical reads with a nested loop and to suspect that part of the query is slow and needs to be improved. I find it useful to try to think about what the query optimizer should do instead to get the data that it needs. Would a hash join be better? Merge join? A nested loop with a different index?

I don't have the full picture but the nested loop joins that you called out don't look that bad to me. I don't see any key lookups and one of the indexes is covering. One way to move forward is to materialize all of the results of the query up until that point. Gather statistics on the temp table. Then look at a query plan for the adjusted query and see how long it takes to run. If the query plan changes for the better then you have a useful clue on how to make it run faster. If it doesn't change then you can at least get a more precise measurement of what you think the slow part is. Good luck!


Teradata 15 : Failure 5619 Role nesting exceeded single-level

Teradata only allows one level of role nesting, there's no workaround.

Either role_2 or is already granted to another role or role_1 has been granted another role.

role_a -- top level
   |
role_b -- GRANT role_b TO role_a; this is ok, 1st nested level
   |
role_c -- GRANT role_c T1O role_b; this will fail, 2nd nested level


MySQL - What exactly does smallint(2) permit?

As explained in the MySQL documentation here, the (n) suffix for numeric types is the (default) display width for numbers in that column. In your case the (2) sets the default display width to 2 digits. It does not limit numbers in that column to two digits (as you have discovered), it's just a formatting hint for display purposes.


Too many TEXT fields leads to record too big

You are near or at a limit of row size.  There is a limit of about 8000 bytes per record.  TEXT fields, by default, store only 767 bytes toward that 8000, and store the rest elsewhere.  You have enough TEXT fields to threaten the 8000.

Plan A:  Have one JSON TEXT field that is a structure containing the 9 structures you now have.  This will easily avoid the 8000.  And, since you seem to update all of them all the time, it is probably more efficient.  (If it could exceed 65KB, make it MEDIUMTEXT instead.)

Plan B:  ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED would drop that 767 to 20, thereby no longer threatening to hit 8000.

Plan C:  "Vertical partitioning" -- That is, put some columns in a 'parallel' table.

I prefer Plan A, but it that does not fit with your access patterns, then consider the other options.

Actually, I would go with a variant of Plan A:  I would compress the JSON in the client, then store into a BLOB (TEXT won't work with compressed data).  That shrinks the disk footprint, cuts back on the network traffic, etc.

Yes, a single UPDATE (setting many fields of one row) is better than multiple UPDATEs (each setting one field of the same row).

Reference:  http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Another note:  If all 9 text fields (as you currently have it) are all longer than 767 bytes, then they are stored in 9 different places outside the record.  Plan A would lead to only 1 'overflow' place for the text.


Constraints between tables on a variable

I think that I would do something like the following.

Have a (sort of) joining table (or Associative Entity in the technical jargon) called slot.

If it's a manual system and you don't actually have a physical slot for individual bicycles (i.e. bicycles are stored behind a counter and allocated on an ad-hoc basis - but I'm also assuming that you keep track of individual bikes by bicycle_id), that's OK. Just "pretend" that you have individual slots and fill them virtually in sequence until there are no more either for filling or emptying. This solution also has the advantage of being ready to go in the event of slots being unsupervised as they are in my city - just out on the street!

So, do the following:

CREATE TABLE slot
(
  slot_id SERIAL PRIMARY KEY,
  terminal_id INTEGER NOT NULL,
  bicycle_id  INTEGER NOT NULL,  -- could have 0 here for unoccupied slots if you dont' want to deal with NULLs!
  CONSTRAINT slot_terminal_bike_uq UNIQUE (terminal_id, bicycle_id), -- assumes that a given bike has a "home" terminal - YMMV
  CONSTRAINT slot_terminal_fk FOREIGN KEY (terminal_id) REFERENCES terminal (terminal_id),
  CONSTRAINT slot_bicycle_fk  FOREIGN KEY (bicycle_id)  REFERENCES bicycle  (bicycle_id)
);

Then, have the same number of slots per terminal as there are total bikes at that terminal. All you have to do to see the number of available bikes is:

SELECT COUNT(*) FROM slot WHERE bicycle_id != 0

No three-value logic to worry about.

I think that experimenting with this idea might be helpful. Obviously, I don't know your exact circumstances, but hopefully it will get you going and/or thinking! Schema design is an iterative process (see the links here). Design a first pass of your system, run some code, fix the bugs (and there will be bugs), rinse and repeat.


The target table user of the INSERT is not insertable-into MariaDB 10.4.11

I finally found my solution.

This is a big thanks to @RolandoMySQLDBA on this thread here

The only problem I had with his answer was that in the /var/lib/mysql/InitFile.sql file the missing WITH GRANT OPTION;

example:

GRANT ALL PRIVILEGES ON *.* TO root@localhost
IDENTIFIED BY 'Gstlouis78' WITH GRANT OPTION;

Do not forget to remove the file when you are done, update to remove the init-file string you added the /etc/my.cnf

I am still not sure why mariadb was giving me the error "INSERT is not insertable-into"
and I cold not find any documentation that explains this was a mariaDB problem.  If anyone has more information about this please share


Do not build arrays as columns.  Instead, use extra tables.  If the different content_* are differeng languages, have another table with page_id (for JOINing), content, and lang.  Probably the PRIMARY KEY would be (page_id, lang).


MySQL backing up MyISAM tables by copying the physical files - and automating the process

Basically, you need the following paradigm

Session #1 (MySQL)

Lock all tables
Read Lock
Sleep for a Long Time

Session #2 (Windows) : This you are already doing

Copy the .frm, .MYD, .MYI files for every MyISAM table

Session #3 (After Session #2 is Complete)

Look for Sleeping Connection of Session #1
Kill the SLEEP Query (Session #1 Will Terminate)

Create a File Called C:\KillLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
mysql %MYSQL_CONN% < %KILL_SCRIPT%

Create a File Called C:\FlushTablesWithReadLock.bat with these lines

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set KILL_SCRIPT=C:\KillSleepLock.sql
start mysql %MYSQL_CONN% -ANe"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)"
mysql %MYSQL_CONN% -ANe"SET @x=SLEEP(180); SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE state='User sleep' AND info='SELECT SLEEP(86400)'" > %KILL_SCRIPT%

This will lock all tables, pause for 3 minutes, then create the file C:\KillSleepLock.sql

Backup Process

Here are your steps to automate

STEP 01 : Run C:\FlushTablesWithReadLock.bat
STEP 02 : Run your process to backup the .frm, .MYD, .MYI files
STEP 03 : Run C:\KillLock.bat

I will leave it to you to implement this paradigm

Give it a Try !!!


How expensive is it to rename column name on a big table?

Yes, it is just a metadata update.

SQL> create table t1 as
     with g as (select * from dual connect by level <= 1000)
     select 'HELLO' as c1 from g,g,g where rownum <= 1e7;

Table created.

SQL> select bytes/1024/1024 from user_segments where segment_name = 'T1';

BYTES/1024/1024
---------------
            128

SQL> select
       s.value/1024/1024
     from v$mystat s
     natural join v$statname sn
     where sn.name = 'redo size';

S.VALUE/1024/1024
-----------------
       131.848621

SQL> alter table t1 rename column c1 to c2;

Table altered.

SQL> select
       s.value/1024/1024
     from v$mystat s
     natural join v$statname sn
     where sn.name = 'redo size';

S.VALUE/1024/1024
-----------------
       131.851715

Above statement produced just a tiny amount of redo.

But there are other aspects: the required locks and invalidation of related PL/SQL objects.


Possible Pitfalls of Setting Up Kerberos to Permit Cross-SQL Database Access Via AD Trusted Connection

I did this as well to make linked server permissions sooo much easier to manage. You get to choose "Be made using the login's current security context" when setting up linked server security.

I rarely but occasionally see some inconsistent behavior with the registering / deregistering of SPN's (Kerberos authentication will fail if the SPN becomes deregistered). It very well could be something with our setup, I occasionally experience other network vagueries as well. I mention it because it's always the first thing I check when I get the dreaded "Login failed for NT AUTHORITY\ANONYMOUS LOGON". That is the error you'll get if you've configured security as mentioned above, but Kerberos fails and authentication drops down to NTLM.


Return Output from SQL Function using command line SQLCMD

You are looking for the headers option of sqlcmd. Adding -h -1 will suppress header information.

sqlcmd -S MyServer\Instance -d SQLAdmin -Q "SELECT dbo.UDF_INT_Get_Status ('INT_02')" -h -1


PostgreSQL 10 Identity Column gets "null value" when inserting multiple rows with default keyword

This is in fact a bug. I verified it. I went to go see if it was filed and it seems it already is. It's not just filed, the commit is there.

You can see their test, exactly like yours

+-- VALUES RTEs
+INSERT INTO itest3 VALUES (DEFAULT, 'a');
+INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
+SELECT * FROM itest3;

So just wait, it's there for PostgreSQL 10.2.

Possible work around for PostgreSQL < 10.2

If you absolutely must have this, and using the implicit column isn't acceptable. One easy solution would be to retrieve the sequence with the catalog info function

pg_get_serial_sequence(table_name, column_name)

Which I believe should work, and to set that as a default.

ALTER TABLE ONLY test
  ALTER COLUMN id
  DEFAULT nextval('seqname');


If you need to shrink your log file often, it means that the size it has after a shrink is not sufficient for your everyday activity, so it grows again and again.

This means that you do unnecessary work when shrink it, and, that is worse, every time your log needs to expand, it wastes time zeroing out every new piece of file it adds (and your users WAIT for the completion of this zeroing out).

You should stop this double waste of time and eliminate log shrinking from your maintenance plan. If you are not satisfied with the size of your log, shrink it once and then make your log backups more frequently.


update employee as a
      set a.suprole=(
                    select b.jobroll from employee as b
                           where b.supervisorid=a.employeeid
                           group by b.jobroll
                           order by count(b.jobroll) desc
                           limit 1
                     )
       where a.jobroll='generic manager';


How to get value of last updated row from a table?

From http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html:

  Within the trigger body, the OLD and NEW keywords enable you to access
  columns in the rows affected by a trigger. OLD and NEW are MySQL
  extensions to triggers; they are not case sensitive.

So if you want the new rows being inserted on another table, you should use the syntax:

INSERT INTO tbl2 (c1, c2, c3) VALUES (NEW.c1, NEW.c2, NEW.c3);


How badly does innodb fragment in the face of somewhat out-of-order insertions?

In the case of an index, fragmentation can vary greatly. Yet, nothing can be worse that entering data into an index already sorted.
To illustrate, let's take the worse case BTREE, a balanced binary tree. A balanced binary tree is a binary tree that monitors the height of its child nodes and will only allow for a difference in heights of :

-1 (Left TreeNode Taller Than the Right TreeNode)
 0 (Left TreeNode and Right TreeNode Same Height)
+1 (Left TreeNode Shorter Than the Right TreeNode)
See Definition of AVLTrees

Over 25 years ago, I learned that entering already ordered data into a balanced binary tree results in 45% of the tree nodes requiring rotation and rebalancing.
Common sense would have it that if I enter the numbers 1-15 ordered as follows:

8,4,12,2,6,10,14,1,3,5,7,9,11,13,15
8,12,4,14,10,6,2,1,3,5,7,9,11,13,15
8,4,12,2,6,10,14,15,13,11,9,7,5,3,1
8,12,4,14,10,6,2,15,13,11,9,7,5,3,1

into a balanced binary tree, absolutely no tree rebalancing would ever occur.
This being the case, entering random data into a balanced binary tree could never be as bad as entering already sort data. On average, random ordering would require less tree rebalancing than sequential ordering.
Now, let's get away from balanced binary trees into something more real-world, BTREEs. Treenodes in BTREE indexes usually hold some power of two index page entries + pointers to a lower BTREE node. Page splitting would occur in the event pages become full. Consequently,  many early BTREE pages would end up half full due to page splitting (The execption to this would be with bulk inserts and in-memory sorting of keys and writing out more compact index pages with as little fragmentation as possible). Other than that exception, insertion of ordered data in a BTREE page would cause page splits with a very small degree of regularity. Random inserts would at the very least wreak less havoc than loading ordered data.
InnoDB
In terms of InnoDB, you have the gen_clust_index. It has hooks to another type of index used internally for tracking row IDs. Never think about the gen_clust_index as just any other BTREE. It is what the Oracle world would call an index-organized table. Column ordering for InnoDB is handled so differently from MyISAM that can you detect a notable difference if you attempt to reorder row data in a specific key order.
According to pages 148,149 of MySQL Database Design and Tuning

it shows that doing ALTER TABLE tblname ORDER BY column-list; would greatly improve retrieval for MyISAM tables by physically ordering rows. In contrast, doing ALTER TABLE tblname ORDER BY column-list; would have no effect on InnoDB tables because data is always ordered by RowID within the clustered key.
MORAL OF THE STORY
When it comes to InnoDB, do not worry about randomness of data. No matter what machinations you perform on tables indexes, the clustered key has the final say on access. Whatever order you load the data, gen_clust_index will abide by it. No questions asked.


Passing Variable in json_value in sql server 2016

As answered for JSON_QUERY in this question the same is true for JSON_VALUE as well.

Docs on JSON_VALUE

  In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a
  variable as the value of path.

Before SQL Server 2017 you would have to build the query dynamically.

Examples

Printing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL VARCHAR(MAX);

SET @SQL ='
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"'+cast(@id as varchar(4))+'"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';
PRINT (@SQL);

Executing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Parameterdefinition nvarchar(20) ='@id int';
SET @SQL ='
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"+cast(@id as varchar(4))"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';

EXEC SP_EXECUTESQL
@SQL,@Parameterdefinition,@id;

DB<>Fiddle


What does "Latest Restore Time" says in AWS RDS(Mysql) instances page

Given the RDS Console tooltip, the Latest Restore Time defines The latest time to which a database can be restored with point-in-time restore. and not the last time the database was restored.

The database can be restored from Amazon RDS Automated Backup tool that can be configured in the Amazon RDS Modify DB Instance page. The default window backup is 7 days, every 30 minutes.


Set TERMOUT OFF is not working when I am using sqlplus in command prompt?

12.41.61 SET TERM[OUT] {ON | OFF}

  Controls the display of output generated by commands in a script that
  is executed with @, @@ or START. OFF suppresses the display so that
  you can spool output to a file without displaying the output on
  screen. ON displays the output on screen. TERMOUT OFF does not affect
  output from commands you enter interactively or redirect to SQL*Plus
  from the operating system.

Put the above commands in a script, then call that script.

script.sql:

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT
    DATA_OUT_DATA
FROM
    STAGE.DATA_TABLE
ORDER BY
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON

Then:

SQL> @script.sql


A LOP is a "Logged Operation" they have identifiers and contexts internally.

  LOP  LopDesc (Operation), LopCtxt (LOP Context)
    1,     LOP_FORMAT_PAGE,        LCX_TEXT_MIX
    1,     LOP_FORMAT_PAGE,       LCX_CLUSTERED
    1,     LOP_FORMAT_PAGE,      LCX_INDEX_LEAF
    1,     LOP_FORMAT_PAGE,  LCX_INDEX_INTERIOR
    2,     LOP_INSERT_ROWS,        LCX_TEXT_MIX
    2,     LOP_INSERT_ROWS,       LCX_CLUSTERED
    2,     LOP_INSERT_ROWS,      LCX_INDEX_LEAF
    2,     LOP_INSERT_ROWS,  LCX_INDEX_INTERIOR
    3,     LOP_DELETE_ROWS,   LCX_MARK_AS_GHOST
    3,     LOP_DELETE_ROWS,  LCX_INDEX_INTERIOR
    4,      LOP_MODIFY_ROW,             LCX_IAM
    4,      LOP_MODIFY_ROW,             LCX_PFS
    4,      LOP_MODIFY_ROW,            LCX_HEAP
    4,      LOP_MODIFY_ROW,        LCX_TEXT_MIX
    4,      LOP_MODIFY_ROW,       LCX_BOOT_PAGE
    4,      LOP_MODIFY_ROW,       LCX_CLUSTERED
    4,      LOP_MODIFY_ROW,       LCX_OBJECT_ID
    5,   LOP_MODIFY_HEADER,            LCX_HEAP
    5,   LOP_MODIFY_HEADER,      LCX_INDEX_LEAF
    6,  LOP_MODIFY_COLUMNS,       LCX_CLUSTERED
    7,        LOP_SET_BITS,             LCX_PFS
    7,        LOP_SET_BITS,            LCX_SGAM
   10,  LOP_SET_FREE_SPACE,             LCX_PFS
   11,    LOP_DELETE_SPLIT,       LCX_CLUSTERED
   11,    LOP_DELETE_SPLIT,      LCX_INDEX_LEAF
   13,    LOP_EXPUNGE_ROWS,       LCX_CLUSTERED
   16, LOP_FILE_HDR_MODIFY,     LCX_FILE_HEADER
   18,     LOP_COUNT_DELTA,       LCX_CLUSTERED
   19,     LOP_ROOT_CHANGE,       LCX_CLUSTERED
   22,       LOP_INSYSXACT,       LCX_CLUSTERED
   22,       LOP_INSYSXACT,      LCX_INDEX_LEAF
   22,       LOP_INSYSXACT,  LCX_INDEX_INTERIOR
  128,      LOP_BEGIN_XACT,            LCX_NULL
  129,     LOP_COMMIT_XACT,            LCX_NULL
  131,       LOP_PREP_XACT,            LCX_NULL
  137,        LOP_HOBT_DDL,            LCX_NULL
  140,      LOP_HOBT_DELTA,            LCX_NULL
  141,       LOP_LOCK_XACT,            LCX_NULL
  148,        LOP_BP_DBVER,            LCX_NULL
  150,      LOP_BEGIN_CKPT,            LCX_NULL
  152,       LOP_XACT_CKPT,  LCX_BOOT_PAGE_CKPT
  153,        LOP_END_CKPT,            LCX_NULL
  211,     LOP_SHRINK_NOOP,            LCX_NULL

It looks like the LopCtxt is a kind of breakdown of the logged operation into atoms.

@sp_BlitzErik provided this blog post find which adds a lot detail.


How to select bytea data as binary data, inserted as hex-encoded from binary

You're not going to get binary data out of psql and SELECT. psql will format it for readability.  You could try something with lo_export similar to the answer in How to insert (file) data into a PostgreSQL bytea column to get the data written to a file.

However, most PostgreSQL client adapters (perl DBI, Ruby 'pg', etc) can give you the data in binary when you SELECT bytea columns.  Just write a little program using an appropriate adapter in the language of your choice.


Why Snapshots of the model, master, and tempdb databases are prohibited

taking snapshot on hourly manner is very useful for active databases and based on RTO (Recovery Time Objective) and RPO (Recovery Point Objective) which is less than 1 hour.

Model, master, and tempdb is passive default MSSQL databases.These database doesn't required to snapshot hourly as there wont be much changes occur every hour.One time daily backup is sufficient enough to rescue the database in the event of disaster.

Moreover, snapshopt will use up spaces and available resource (RAM and CPU).


In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error?

You can use the following statement to synchronize the sequence with the current max value of the ID column:

select setval(pg_get_serial_sequence('dummy', 'id'), (select max(id) from dummy));

Online example: https://rextester.com/GMK2630

You can run this manually after you have inserted rows with explicitly supplied values.

Note that this can still result in a wrong sequence value if this is done concurrently from multiple transactions because a transaction does not see the values inserted (but not committed) by other transactions.

But I would strong recommend to simply let the default do its job, adjusting the sequence like that is more a hack, rather than good coding practice.


I guess you have forgotten a ')'.

try this:

stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1),?,?,?,?,?");


Whats SQL Server NVARCHAR(max) equivalent in MySQL?

According to this MySQL document the LONGTEXT datatype in MySQL is 4 Gigabytes.

http://wiki.ispirer.com/sqlways/mysql/data-types/longtext

Note that the number of characters depends on the encoding.


How to join fixed row table against mapping table and reveal unmapped records

You need to create a multiplication first - every combination of Property + RoomType. In SQL Server we use a CROSS JOIN for that. Then you need to outer join to the junction table, and conditionally display Yes/No based on whether the junction table had a matching row. This should provide the output you want:

SELECT
  p.Property,
  RoomTypeID = t.Id, -- why isn't this just called RoomTypeID?
  PropertyHasRoom = CASE
    WHEN pr.Property IS NOT NULL THEN 'Yes'
    ELSE 'No' END -- or just END if you want NULL
FROM dbo.Properties AS p
CROSS JOIN dbo.RoomTypes AS t
LEFT OUTER JOIN dbo.PropertyRooms AS pr
ON p.Property = pr.Property
AND t.Id = pr.RoomTypeID
ORDER BY p.Property, t.Id;


Different results rebuilding an index online and offline

This is by no means a full answer but may move things along a bit if you were to try something similar and report your results.

I couldn't reproduce them. With the following test table

CREATE TABLE [dbo].[Table]
(
Col BIGINT
)

CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)

INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

And multiple runs of the following script

USE FragTest;

DECLARE @DbccPage TABLE (
  ParentObject VARCHAR(255),
  Object       VARCHAR(255),
  Field        VARCHAR(255),
  VALUE        VARCHAR(255))

DECLARE @sp_index_info TABLE (
  PageFID         TINYINT,
  PagePID         INT,
  IAMFID          TINYINT,
  IAMPID          INT,
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),
  PageType        TINYINT,
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT,
  PRIMARY KEY (PageFID, PagePID));

DECLARE @I INT = 0

WHILE @I < 2
  BEGIN
      DECLARE @Online VARCHAR(3) = CASE
          WHEN @I = 0 THEN 'OFF'
          ELSE 'ON'
        END

      EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ' + @Online + ',
    SORT_IN_TEMPDB = ON
);')

      INSERT INTO @sp_index_info
      EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );

      ; WITH T
           AS (SELECT *,
                      PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
               FROM   @sp_index_info)
      SELECT PageType,
             MIN(PagePID) AS StartPID,
             MAX(PagePID) AS EndPID,
             COUNT(*)     AS [count],
             IndexLevel
      FROM   T
      GROUP  BY Grp,
                PageType,
                IndexLevel
      ORDER  BY PageType DESC,
                StartPID

      DECLARE @DynSQL NVARCHAR(4000)

      SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
      FROM   @sp_index_info
      WHERE  PageType = 10

      INSERT INTO @DbccPage
      EXEC(@DynSQL)

      SELECT VALUE AS SinglePageAllocations
      FROM   @DbccPage
      WHERE  VALUE <> '(0:0)'
             AND Object LIKE '%IAM: Single Page Allocations%'

      SELECT avg_page_space_used_in_percent,
             avg_fragmentation_in_percent,
             fragment_count,
             page_count,
             @Online                                                   AS [Online],
             (SELECT COUNT(*)
              FROM   @DbccPage
              WHERE  VALUE <> '(0:0)'
                     AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
      FROM   sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
      WHERE  index_level = 0

      DELETE FROM @sp_index_info

      DELETE FROM @DbccPage

      SET @I = @I + 1
  END

I consistently got results like

Online = OFF

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       119         119         1           NULL
2        2328        2351        24          0
2        2352        2352        1           1
2        2384        2392        9           0

SinglePageAllocations
----------------------

(0 row(s) affected)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734               0                            2                    33                   OFF    0

Online = ON

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       115         115         1           NULL
2        114         114         1           0
2        118         118         1           1
2        2416        2449        34          0

SinglePageAllocations
-----------------------
(1:114)
(1:118)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875               2.85714285714286             2                    35                   ON     2

At least in the test I did the differences between the two balanced out fragmentation wise (though similarly to your test I did find that rebuilding the index online led to a higher page count.).

I found that the Online = OFF version always used uniform extents and had zero single page allocations whereas the Online = ON always seemed to put the index root page and first index leaf page in mixed extents.

Putting the first index leaf page in a mixed extent and the rest in contiguous uniform extents causes a fragment count of 2.

The Online = OFF version avoids the fragment caused by the lone index leaf page but the contiguity of the leaf pages is broken by the index root page that shares the same extents and this too has a fragment count of 2.

I was running my test on a newly created database with 1 GB of free space and no concurrent activity. Perhaps the Online = OFF version is more vulnerable to concurrent allocations causing it to be given non contiguous uniform extents.


How long can this ALTER TABLE statement take?

Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a quick test to compare changing a table from int -> bigint and from varchar(8000) -> varchar(max). First, two simple tables:

CREATE TABLE dbo.t0(a int primary key, b int);
GO
CREATE TABLE dbo.t1(a int primary key, b varchar(8000));
GO

Now, insert at least one non-NULL value, and then over 5 million rows (on my system; YMMV):

INSERT dbo.t0 VALUES(0,1);
INSERT dbo.t0 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

INSERT dbo.t1 VALUES(0,'what');
INSERT dbo.t1 SELECT rn, NULL FROM
( SELECT rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS x;
GO -- 5,299,204 rows for me

Then I tested the ALTER change, with stats I/O enabled:

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t0 ALTER COLUMN b bigint NULL;
GO -- 9 seconds, 122,506 reads
SET STATISTICS IO OFF;

This finished in 9 seconds and required 122,506 reads.

SET STATISTICS IO ON;
GO
ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL;
GO -- 5 seconds, 8,562 logical reads
SET STATISTICS IO OFF;

This finished in 5 seconds and only required 8,562 reads.

So while neither operation happened online, and while your actual results may differ based on hardware and your table structure, this was on a lowly Windows VM on a MacBook, so I would expect your time to be better than this.

I also tested with a lot more data populated in the nullable column. Dropped the tables, re-created and re-populated with the above scripts, then ran this to put something in 100,000 rows:

;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = a;
GO
;WITH x AS (SELECT TOP (100000) a,b FROM dbo.t1 ORDER BY NEWID())
UPDATE x SET b = REPLICATE(RTRIM(a), 1000);
GO

This took way longer than the subsequent ALTERs (over 3 minutes). The first ALTER still took 9 seconds, but the second one took 17, simply because the number of reads shot up (to 189,854). This is similar to what would happen if you had, say, 30 other columns in this table. I still think 17 seconds is pretty good for what is actually happening here, and again, this is far from an enterprise-level environment.

As Paul points out, starting with SQL Server 2016, you can get this to happen online, but there are restrictions and limitations. For more information see the docs.

ALTER TABLE dbo.t1 ALTER COLUMN b varchar(max) NULL WITH (ONLINE = ON);

This option is not available in SQL Server 2014, though.

One way you could get this to happen more online is to simply move that column to a separate, related table. There are some intricacies here with how you cut over, and it might impact a bunch of code, but you can solve most issues with a view (at least temporarily).


How to enable/disable SQL agent jobs during fail over (AlwaysOn)

There are several ways to automate this and you are heading down the right path...you can rely on your DBA to manually disable and enable (not very nice ;), you can keep track of job state in a table and enable and disable using a periodic job that runs, or you can categorize your jobs and respond to a role change alert...this is what I do.

A rough outline is:

Pick a category for all the jobs that you want to only run on the current primary replica, I use the ag name
Set up a SQL Agent alert for error number 1480 (AG Role Change) that will call a stored procedure in master via a job passing in the category name
Build the sp to check to see if it is the primary, if it is enable all the jobs categorized with the parameter. If it is not the primary, disable all the jobs categorized with the parameter
Build some functions to help recognize if the AG is Primary or Secondary

There is a very detailed SQL Magazine series of articles by Michael K. Campbell that give code examples for how to structure either solution, I started with that article and only had to make minor modifications.


Sql Server Agent fails to run batch from desktop

Any idea why the access is denied if the batch file is in desktop?

Yes. Because it is a rotten idea. The service agent account has no rights on your personal folders and your personal folders include the desktop. And you should keep it that way.

Your personal fodlers are btw., locked also for the admin accounts etc.

If you need sql agent to do something, I strongly suggest you agree on a location for that to happen and move the batch there.


Is it possible to rewrite this Function for optimization?

You are running one query for each column and table. One way to optimize this, is to only run a single query for each table that retrieves the distinct values for all columns in one go. This result can be put into a JSONB which in turn can be returned as individual rows using jsonb_each:

CREATE OR REPLACE FUNCTION get_pubcol(
    included_tables name[] default '{}',
    included_schema name[] default '{}'
)
RETURNS table(schema_name text, table_name text, column_name text, column_value text)
AS
$$
declare
  l_value jsonb;
  l_sql   text;
begin
  FOR schema_name, table_name, l_sql IN
    SELECT c.table_schema, c.table_name,
           concat('select to_jsonb(t) from ( select ',
                  string_agg(format('array_agg(distinct %I) as %I', c.column_name, c.column_name), ', '),
                  format(' from %I.%I) as t', c.table_schema, c.table_name)) as sql
    FROM information_schema.columns c
      JOIN information_schema.tables t
        ON t.table_name = c.table_name AND t.table_schema=c.table_schema
    WHERE t.table_type = 'BASE TABLE'
      AND (c.table_name = ANY(included_tables) OR included_tables='{}')
      AND (c.table_schema = ANY(included_schema) OR included_schema='{}')
    group by c.table_schema, c.table_name
  LOOP
    EXECUTE l_sql into l_value;
    IF l_value is not null THEN
      return query
        select schema_name, table_name, x.*
        from jsonb_each_text(l_value) as x(column_name, column_value);
    END IF;
 END LOOP;
END;
$$ language plpgsql;

The outer query assembles a single query for each table. E.g. if you have a table person with the columns id, firstname, lastname the expression in the query for the loop would generate something like:

select to_jsonb(t)
from (
   select array_agg(distinct lastname) as lastname, array_agg(distinct id) as id, array_agg(distinct firstname) as firstname
from public.person
) as t

That query returns a single jsonb value that contains the information for all columns. This is stored in the local variable l_value, something like:

{
    "id": [42,43,44,45],
    "lastname": ["Beeblebrox","Dent","McMillan","Prefect"],
    "firstname": ["Arthur","Ford","Tricia","Zaphod"]
}

This is then turned back into rows using jsonb_each_text.

The display will be different though.

Your example

{BL1,GL1,GP1,KL1,KP1,NaN,SL1,SP1,WL1,WL2,WP1}

will be displayed as

["BL1","GL1","GP1","KL1","KP1","NaN","SL1","SP1","WL1","WL2","WP1"]


It would be helpful to see your expected output, but at a guess, you need this:

SELECT
    studID,
    [Remaining Metrics (14th Century)],
    [History Of The World (Part I)],
    [Singing Socks In The Rain (Part III)]
FROM
    (SELECT StudID, Course, SUM(lv) AS Lv FROM @House GROUP BY StudID, Course) AS X
PIVOT
    (
    SUM(lv) FOR course IN ([Remaining Metrics (14th Century)], [History Of The World (Part I)], [Singing Socks In The Rain (Part III)])
    ) As Drafts

...if you want to see the total of each students' "Lv" values, in a column for each course.


MySQL any way to import a huge (32 GB) sql dump faster?

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit

In the event of a crash, both 0 and 2 can lose once second of data.
The tradeoff is that both 0 and 2 increase write performance.
I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor.

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.


Determine Oracle package body memory size

Have a look at PL/SQL Program Limits

  In the shared memory pool, a package specification, ADT specification,
  standalone subprogram, or anonymous block is limited to 67108864
  (2^26) DIANA nodes which correspond to tokens such as identifiers,
  keywords, operators, and so on. This allows for ~6,000,000 lines of
  code unless you exceed limits imposed by the PL/SQL compiler, some of
  which are given in Table C-1.

I remember long time ago with Oracle 8i I "managed" once to get an PLS-00123: program too large error. At this time the limit was 64K DIANA nodes.

I think nowadays it is hardly possible to exceed the limit.


Importance of uniformity of hardware for Galera cluster

You basically answered your question yourself: the main problem is uniformity of response time- you cluster, as a whole, will be as fast as the slowest of your nodes (due to the roundtrip). This may affect not only your hardware choice (for example, all nodes having SSDs except one will be a bad choice) your network topology too- a remote server will slow down nodes on the same data center. Also, you do not want one of the nodes to run out of space, which may take some time to be desynced.

  the nodes use the same disks, the same amount of RAM and similar CPUs, shouldn't that be good enough?

Yes, "it should be enough", now the question is if it actually is in practice. Makes some tests on latency, specially under load (mysqlslap/sysbench). You never know when a small detail can be a big difference. It is also easier to manage uniform hardware and software (for replacements/fixes and for comparing performance). But you are mostly right.


Session close unexpectedly destroying temporary table

As noted in the comments, if your session disconnects for any reason, you're going to have problems with even a global temp table disappearing.

Here's a neat workaround: create real user tables in TempDB. This works perfectly fine:

USE tempdb;
GO
CREATE TABLE dbo.persistent(ID INT);
GO

And then you can access that table from any session (via tempdb.dbo.persistent.)

I'm not saying this is a good idea - it's a bad one in the same way that global temp tables are a bad idea, like contention across multiple sessions, having problems when you have an AG failover or a service restart, or filling up your TempDB drives - but as long as you're trying to use global temp tables, this would be a workaround.


Percona's xtrabackup --apply-log fails with "all log files must be created at the same time"

Check the log, a bit above the error:

InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!

It is trying to create new innodb files because they are not found.

You need to decompress the files before applying the log:

innobackupex --decompress /...

Then, apply the log.

BTW, you do not need to specify the user and password once the first backup process was done.


How can I make "phraseto_tsquery" as fast as "plainto_tsquery" in PostgreSQL

The phrase search has to inspect every document which contains both 'awesom' & 'websit' to make sure the tokens occur in the correct order and proximity, because the order and proximity data is not present in the index.  Reparsing, tokenizing, stemming, etc. those documents is slow.  Also, since a large document is stored out-of-band, it has to read that large data in from a separate TOAST table before it can do any of that.  Without the the word order and proximity constraint, it doesn't have to do any recheck at all, as in this case the index only returns exact results, with no possible false positives (as long as work_mem is large enough).

Rather than using a functional index, you can store the tsvector in the table and keep that column populated (for example, a trigger on row insert and on update of body which sets tsv = to_tsvector('english', body)). Then you would build the index directly on "tsv" column, and write the query directly against that column:

ALTER TABLE documents add column tsv tsvector;
update documents set tsv = to_tsvector('english', body);
CREATE INDEX documents_tsv_tsvector_index ON documents USING GIN (tsv);

SELECT...WHERE tsv @@ phraseto_tsquery('english', 'awesome website')

Here, it still needs to recheck the "tsv" for order and proximity, which means it needs to read that large column out of disk and back into memory, but it doesn't need to reparse it as it is already stored in tokenized form designed for efficiency.  How much of an improvement this makes will depend on whether your bottleneck is the IO for reading the documents back into memory, or the CPU for doing the parsing.

Of course that does mean you will be storing both "body" and "tsv" in the table, which will expand its size considerably, but that might be a low cost to pay for the improved performance.  If all you want to do is COUNT(*), then you could even store only the tsvector, and don't store the raw "body" at all.


Master database is corrupt, instance won't start - what are my options?

Here are a few avenues I would investigate. Don't do all of these (some of them are different techniques to accomplish the same purpose), but are worth considering:

1. Examine the SQL Error Log directly

Browse directly to the folder containing the SQL error logs and load the most recent ERRORLOG into notepad to get more detail on why the SQL instance will not start. Perhaps you will find the problem isn't with the master database at all.

2. Try to start the instance in single-user mode

Here is a full list of startup options for SQL server, including -m (single user mode) and -f (minimal configuration mode). Other options allow you to specify the path for the master database, if that is the problem.

If you are able to get the instance started, follow the steps in the MSDN article you linked for restoring the master database, or this detailed walkthrough by Thomas LaRock.

If another application always grabs the single user connection before you can, first disable the SQL Agent so it isn't starting. Second, see the ideas on this question for using the -m"Application Name" parameter to specify the application name.

3. Restore master to another instance and copy its files

I've only found one other mention of this undocumented technique, but I used it successfully this past weekend, so it might be worth a try.

If you can't start the instance in single-user mode, but you have another SQL instance running the exact same release and build, try restoring the last known good master database backup from your dead server to the other instance:

Restore as a different name, of course (master_please_god_let_this_work), WITH MOVE so you don't overwrite master on your good server
Restore WITH NORECOVERY. Not sure this is necessary, but made me feel better that I knew the other server wasn't going to alter anything in the restored master
Set it to offline: ALTER DATABASE [master_please_god_let_this_work] SET OFFLINE
Copy the restored MDF and LDF files from the good server to the dead server
Rename the master.mdf and mastlog.ldf files as necessary to replace the bad master files with your restored versions
Cross your fingers and start the instance
Optional: do a fresh restore of master on the revived server. Not sure this is necessary, since we were pretty careful not to change master.

4. Rebuild the system databases

If you don't have another instance running the same version, or if you're not comfortable with using the undocumented procedure listed in #3, or if you don't have backups of master (why do you not have backups??), you can rebuild the SQL system databases from the original install disk:

Setup.exe /ACTION=REBUILDDATABASE /...

When this is complete, you can follow the steps linked previously to restore master from your last good backup. You will also need to restore a recent backup of msdb to keep all your jobs, job schedule, and job history.

5. Restore all USER databases to a new (or existing) SQL instance

If you've got another existing instance already running (proper SQL version, enough disk space), I would probably start database restores from the most recent backups while I'm working on the other troubleshooting steps above, just in case I need them.

If your new (or reinstalled) instance has access to the same disk, it is much faster to simply attach them as new databases:

CREATE DATABASE foo
ON (FILENAME = 'D:\data\foo.mdf'),
   (FILENAME = 'D:\data\foo_log.ldf')
FOR ATTACH;

6. Re-do any changes to master

Once you successfully restore master (via any of the above techniques), you need to investigate any changes that might have been lost, if they were made after the backup you just restored:

Security changes
New databases (the files will still be on disk, just attach them)
Server-wide settings

There is no magic way to find these, you'll have to go back to your own company's documentation trail for these kinds of changes, if you have one.


Please RUN the following:

select user();
select current_user();

The former is who you are attempting to login as; the second is what the database sees as connected. You will need to run grant_priv to user.

Here is how to verify:

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user

If the grant_priv is set to 'N' for your user, this needs to be 'Y'.

To grant access you can run:

UPDATE mysql.user
SET Grant_priv='Y', Super_priv='Y'
WHERE User='me'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'me'@'localhost'


11gR2(11.2) RAC TAF Configuration for Admin and Policy Managed Databases (Doc ID 1312749.1)

  Create Service

As you have already written.

  Start the service

No TNS entry needed on the server.

  Make a connection.

  The client connect string does not require any special TAF parameters.
  A simple tnsnames.ora entry as shown below will do.

mysrvb =
  (description =
   (address = (protocol=tcp)(host=secrac-scan)(port=1521))
   (connect_data=
    (service_name=mysrvb)
   )
  )

Of course, use the service that was defined with TAF.


What is the cause of this problem with CONVERT()?

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try:

SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0);

As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


Can I find out current disk response time in MS SQL Server using DMOs?

What is Activity Monitor Doing

You're right, those values from sys.dm_io_virtual_file_stats accumulate the entire time the SQL Server service is running:

  The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Activity Monitor is just taking a snapshot of the results, waiting 10 seconds, taking another snapshot, and then showing you the difference.  This is why the numbers are small.

It can be useful to view these diffs, or log them to a table on a schedule, in order to help you know what's "normal" and what's "slow" for your server.  Some tools that can help you do this:

Paul Randal's script: Capturing IO latencies for a period of time
sp_BlitzFirst (which conveniently has parameters that will log the results to a table for your automatically): Logging sp_BlitzFirst to Tables

If your goal is to see the same numbers from Activity Monitor via T-SQL, I would go with one of these options.

Disk Response Time vs File Response Time

The sys.dm_io_virtual_file_stats dynamic management function returns these stalls (AKA latency or disk response time) on a per-file basis.  If you're looking for a pattern of slow response times, or a way to get latency, at the disk / LUN level, you'll have to be careful to consider which disk each of these files is on.

To the direct point of your question, SQL Server is not a great source of information about disk-level I/O issues.

What is Slow?

You'll see varying rules of thumb online, here's a common one (from SQLPerformance.com):

  If you ask SQLskills, we will tell you something along the lines of:

  Excellent: < 1ms
  Very good: < 5ms
  Good: 5 – 10ms
  Poor: 10 – 20ms
  Bad: 20 – 100ms
  Really bad: 100 – 500ms
  OMG!: > 500ms

  If you do a Bing search, you will find articles from Microsoft making the recommendations similar to:

  Good: < 10ms
  Okay: 10 – 20ms
  Bad: 20 – 50ms

Take those values with a grain of salt though.  What matters is that performance is good enough, not whether a number has a certain value.  Make sure to treat the patient, not the monitor.

Outside of SQL Server

To get truly useful information about about disk I/O, you'll really want to go outside of SQL Server.  Windows Performance Monitor (PerfMon) is a good way to see some of these stats in "real time."  You can see them aggregated across the whole machine, or by disk.  The counters you want are Avg. Disk sec/Read and Avg. Disk sec/Write, which are both part of the Physical disk object.

I've heard that xperf is also a good tool for investigating this.  I haven't used it, but I'll refer you to an excellent Microsoft blog post that provides a lot of details on usage and analysis: Analyzing Storage Performance using the Windows Performance Analysis ToolKit (WPT)

Maybe It's Not the Disk

It's important to note that high disk response times are not necessarily due to a "slow disk."  As the MS blog post above mentions, It could be SAN issues (you might have unplugged or low throughput cabling, downgraded to slower disks than expected on the SAN, the network might be overloaded - even by traffic from other servers and sources - causing slow response times, etc).

It could be that the workload is doing too much I/O, and the disk just can't keep up.  This type of problem can potentially be solved with query and index tuning, rather than trying to improve or upgrade a disk.

It could even be high CPU or oversubscribed VMs, or many other things.


Which technology to synchronise SQL Server and MySQL data?

Given the above comments, I'd recommend going with an SSIS package.
If you go with the linked server option you are injecting database dependency between the replication and the MSSQL server objects.
Linked servers are also a security liability, anyone with sufficient permissions on the MSSQL database can access the target database on the linked server.
Choosing SSIS gives complete control over the datasources, execution, scheduling and effectively decouples the data transfer from the actual databases.


Use another disk for "optimize table" temporary file

If you have deleted most of the rows from a table, OPTIMIZE table needs only enough room to build a new, smaller, copy.  After the copy is finished, it will rename, then drop the old table.

If the table had its own tablespace (built with innodb_file_per_table=ON), then the freed space will be returned to the OS.  But...  The 17GB free in the .ibd cannot be used for the table copy.  The copy will be in a separate file.  So it could fail if the resulting table is more than 8GB (data+index).

If =OFF, the OPTIMIZE will try to use free space in ibdata1 before increasing the size of that file.

There is 17GB free somewhere; I can't tell where.

Also, check TABLE STATUS for other tables.  The Data_free for all tables in ibdata1 will be the same value.  What is that value?

There are tricks to work around the cramped space, but I need answers to the questions posed.


Display Top X results for each unique value

Incoming schema and test data

CREATE TABLE `db_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ComputerID` int(10) unsigned NOT NULL,
  `BackupImageName` varchar(32) NOT NULL,
  `WindowsVolumeGUID` varchar(32) NOT NULL,
  `FileSize` int(10) unsigned NOT NULL,
  `TimeCreated` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `ComputerID` (`ComputerID`),
  KEY `WindowsVolumeGUID` (`WindowsVolumeGUID`),
  KEY `FileSize` (`FileSize`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `db_info` (`ComputerID`, `BackupImageName`, `WindowsVolumeGUID`, `FileSize`, `TimeCreated`) VALUES
(1234, 'PCBackup.spi ', 'kjsdfai-dadad-ddd', 10400, '2020-05-22'),
(1234, 'PCBackup1.spi', 'kjsdfai-dadad-ddd', 10500, '2020-05-23'),
(1234, 'PCBackup2.spi', 'kjsdfai-dadad-ddd', 10300, '2020-05-24'),
(1234, 'PCBackup3.spi', 'kjsdfai-dadad-ddd', 10900, '2020-05-25'),
(1225, 'PCBackup4.spi', 'kjsdfa5-dadad-ddd', 10600, '2020-05-22'),
(1225, 'PCBackup5.spi', 'kjsdfa5-dadad-ddd', 5000, '2020-05-23'),
(1225, 'PCBackup6.spi', 'kjsdfa5-dadad-ddd', 10000, '2020-05-24'),
(1225, 'PCBackup7.spi', 'kjsdfa5-dadad-ddd', 4588, '2020-05-25');

Requested Query

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(IF(FileSize >= 10000, 1, 0)) AS large_backups_count
FROM (
    SELECT ComputerId, FileSize
    FROM `db_info`
    WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
    ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING large_backups_count >= 3;

Result:

+------------+---------------------+
| ComputerId | large_backups_count |
+------------+---------------------+
|       1234 |                   3 |
+------------+---------------------+

Alternative query based on total size of recent three backups :

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(FileSize) AS sum_of_recent_three_backups
FROM (
    SELECT ComputerId, FileSize
    FROM `db_info`
    WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
    ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING sum_of_recent_three_backups >= 30000;

Result:

+------------+-----------------------------+
| ComputerId | sum_of_recent_three_backups |
+------------+-----------------------------+
|       1234 |                       31200 |
+------------+-----------------------------+


You can only have one statement after the CTE. You can, however, define subsequent CTEs based on a previous one:

WITH t1 AS (
   SELECT a, b, c
   FROM table1
)
, t2 AS (
   SELECT b
   FROM t1
   WHERE a = 5
)
SELECT *
FROM t2;

Given that you are trying to count the rows and populate a ref cursor from the same result set, it may be more appropriate to do one of the following:

create a view
stage temporary results in a temp table

Finally, if the query is simple enough, just write it once for the count and again for the cursor. Simplicity and readability trump the DRY principle in this case.


I was just about to suggest using a multi-character separator or a character from a foreign language (e.g. 日) but .separator does not allow multi-character strings or even a multi-byte characters.

I would use a multi-character separator with very low probability to be in the text to import and then parse the document using a custom script. Here is an example in Python3 using $$$$$$ as a separator, but consider strings like [ŠĐć~^˘°˛˙€] if needed.

import.csv

hello $$$$$$ world $$$$$$ 1
foo   $$$$$$ bar   $$$$$$ 2

sqliteimport.py

import sqlite3

import_file_name = "import.csv"
cell_separator = "$$$$$$"
lines = []
insert_query = "INSERT INTO imported (a, b, c) VALUES (?, ?, ?);"
db_connection = sqlite3.connect("database.sqlite")
db_cursor = db_connection.cursor()
db_cursor.execute("CREATE TABLE imported (a TEXT, b TEXT, c INTEGER);")
db_cursor.execute("BEGIN TRANSACTION")
with open(import_file_name, 'r') as import_file:
    for line in import_file:
        # Split each line at each cell_separator into a list of strings.
        # Strip each one of the columns to remove whitespaces.
        cleaned_columns = [column.strip()
                           for column in line.split(cell_separator)]
        db_cursor.execute(insert_query, tuple(cleaned_columns))
db_connection.commit()
db_cursor.execute("VACUUM;")
db_cursor.close()
db_connection.close()


1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.

There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small.  One big problem that can become an issue is if you have a local distributor, especially if you are dealing with a large amount of publications being replicated to multiple subscribers in a push configuration.  If that is the case, one way to alleviate the server impact is to have a remote distributor.

Also, by design with transactional replication the Log Reader Agent is doing just that:  reading the transaction log of the publisher.  There are many strategies to reduce this impact, such as replicating stored procedure executions instead of the resulting data.

  2.If the subscriber server goes down, will the primary still function

Yes, the publisher will still function.  Commands to get replicated will be queuing up, and depending on how long your retention is setup for and how long the subscriber is disconnected you may need to reinitialize the subscription.

I recommend this TechNet reference: Enhance Transaction Replication Performance.  There are a handful of great strategies in that document that should be considered.


Mostly no, it isn't dangerous, but it depends on which version of SQL Server you are talking about, what parts you are using (just the DB engine?, SSIS?, SSRS?, ...) and your level of aversion to the small risk.

Developer edition is essentially Enterprise edition under a different licence. The problems you could hit are that some features might not be enabled in your production, standard edition, installation that are present in your development environment.

If you are using SQL Server 2016sp1 or above then this is unlikely to be a problem at all: almost everything is available to all editions. If you are using an older version then there might be differences to watch out for. For instance table level data compression was not supported outside enterprise (and developer) editions before 2016sp1 (including the original release of SQL Server 2016). The risk here being that one of your devs may make use of a feature that is not enabled in production. To see which features this affects, check MS's editions documentation: 2012, 2014, 2016, 2017. The risk here is small, and can be mitigated with care.

You could also go the other way: if you don't specifically need greater feature set and your dev environments don't have the data size to need more RAM than it will make use of, make your devs use Express instances then you know they won't use anything that won't be present when you deploy to Standard!

The differences beyond features between the editions and licensing options are how much RAM and how many CPUs/cores are supported. Unlike feature differences this will not cause your code to fall over because things are missing in production that are present in development, but it might cause performance differences. So again, the risk here is small. The same editions comparison pages listed above include this information also.

In any case, even if you use Developer edition for general dev, I would recommend using Standard edition in your Test environment because you want Test to be as close to Production as possible. Furthermore, matching versions and service pack levels, even if you don't match editions, is strongly recommended where possible.


Merge replication Identity columns for already existing databases

Vikram,

If your remote sites are named unique, then you can utilize HOST_NAME() as a FILTER  while creating a Merge Publication, we are doing the same at our place.

We have 2000 stores across USA, which we replicate (via Merge Replication) to our home office. All these stores have same schema across the board, so table ID is going to be duplicated across the board. To overcome the duplication issue, we utilized followings columns in our Replicated Articles:

Table ID
RowGUID (It will be unique per server at your remote location)
SourceStore (this is filter for HOST_NAME(), which will make record unique at the home office (i.e. Central Location)

Example:

-Subscriber A Table X has ID column 25 with row data (a,b,c,d,RowGUID,Host_Name())
 -Subscriber B Table X has ID column 25 with row data (e,f,g,h,RowGUID,Host_Name())

Hope this helps with your issue.

Thank you,
HP


How to select only members who have not made any deposits this month in mysql

You need to do it in a diffent way: choose all members that are not IN the set of members who paid something this month:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    memberid NOT IN
    (
    /* members who paid something this month */
    SELECT
        memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

You actual query specified members who paid something in the years previous to the current one, and month previous to the current one.

As a second alternative, you can use NOT EXISTS:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    NOT EXISTS
    (
    /* any payment from this specific member this month */
    SELECT
        1
    FROM
        fees
    WHERE
        fees.memberid = members.memberid   AND
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

And yet another (not so clear, in my opinion) alternative, do a LEFT JOIN and set a WHERE condition that will filter out the rows actually retrieving something from the right side of the JOIN (i.e.: using only the rows WHERE all columns, and specifically memberid are NULL on the right side):

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
    (
    /* members who paid this month */
    SELECT
        fees.memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) AS s0
    ON s0.memberid = members.memberid
WHERE
    s0.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */

Check Best practice between using LEFT JOIN or NOT EXISTS

And check all dbfiddle here

This is called, in relational algebra, an antijoin

UPDATE

Taking a step further from Kondybas's solution, and as pointed out in the comments to Kondibas answer by ypercube, this can be further simplified to:

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees f1 ON fees.memberid = members.memberid */
    LEFT JOIN fees f2 ON
        f2.memberid = members.memberid AND
        f2.paiddate BETWEEN date_format(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    f2.memberid IS NULL ;

Writing the condition like this (i.e.: not having a function applied to paiddate), it will use an index such as:

CREATE INDEX idx_member_date ON fees (memberid, paiddate);

... as can be seen on the execution plan:

id | select_type | table   | type | possible_keys   | key             | key_len | ref                                          | rows | Extra
-: | :---------- | :------ | :--- | :-------------- | :-------------- | :------ | :------------------------------------------- | ---: | :-----------------------
 1 | SIMPLE      | members | ALL  | null            | null            | null    | null                                         |    2 |
 1 | SIMPLE      | fees    | ref  | idx_member_date | idx_member_date | 5       | fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid |    1 | Using where; Using index

dbfiddle here


Having troubles creating a specific TRIGGER in MySQL

You need to put your code in a CREATE TRIGGER statement.

CREATE TRIGGER AddNewUserToHighscoresTable
    AFTER INSERT ON USER_ACCOUNTS
    BEGIN
         <insert code to run after inserts on the USER_ACCOUNTS table here...>
    END


Need help to optimize this slow performance Query

we need to know more information as Justin mentioned in the comment to answer you better, however meanwhile try this method, it improve performance a little bit better:
with cte_ as (
select ResourceId
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Subfields else null end) as sub1
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Normalized else null end)  as Norm1
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Subfields else null end) as sub2
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Normalized else null end)  as Norm2
FROM
    ResourceField rfAUT
    INNER JOIN ResourceFieldValue rfvAUT
        ON rfAUT.ResourceFieldValueId = rfvAUT.Id
    group by
        rfvAUT.ResourceId
)

SELECT DISTINCT
    h.ResourceId,
    h.Id AS HoldingId,
    h.Barcode AS [BAR],
    h.Call AS [CAL],
    h.ResourceCategoryId as CategoryId,
    cte_.norm1 fAUT,
    cte_.sub1 sf_AUT,
    cte_.sub2 sf_TIT
FROM ResourceHolding h
left join cte_
 on cte_.ResourceId = h.ResourceId
WHERE h.ResourceStatusId != 7
ORDER BY fAUT asc
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY


Remap array columns to single column with value

UNION ALL, not UNION:
SELECT id, unnest(critical) AS element, 1 AS prio FROM tbl
UNION ALL SELECT id, unnest(high)     , 2         FROM tbl
UNION ALL SELECT id, unnest(low)      , 3         FROM tbl;

Optionally drop the id (like in your displayed result), but I suspect you need it.
For big tables, a LATERAL subquery may be faster as it only uses a single sequential scan:
SELECT sub.*
FROM   tbl t
CROSS  JOIN LATERAL (
      SELECT id, unnest(t.critical) AS element, 1 AS prio
   UNION ALL SELECT id, unnest(t.high)        , 2
   UNION ALL SELECT id, unnest(t.low)         , 3
   ) sub;

db<>fiddle here
Otherwise, this question seems like a subset of your later more complex question:

Unnest ids from arrays based on priority with no duplication


Is there an underling difference between TIMESTAMP and ROWVERSION when creating columns with each?

I can't reproduce this on SQL Server 2017 or 2019 with Visual Studio 2019, so it would be interesting to know:

what version of sqlpackage.exe are you using?
what version of Visual Studio and SSDT are you using?
what version of SQL Server are you targeting?
what is the actual table definition in SSDT?
what options are you specifying when publishing?

Despite not being able to repro the issue, I do have some suggestions.

Whenever SSDT is doing some repetitive or redundant like this, I try to make sure that my definition in the model (SSDT project) matches the target, as sometimes things get transformed prior to being stored in SQL Server's metadata.

As you noted, the metadata is out of sync in this case.  The column is stored as timestamp regardless of whether you specify rowversion or timestamp in the CREATE TABLE statement.

  However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

This behavior is related to these types being data type synonyms.  Synonyms don't leave any trace that one was used in a DDL statement:

  However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There's no record that the synonym was specified in the statement that created the object.

What you're reporting is backwards from what I would expect, but it's possible that SSDT is trying to enforce this deprecation warning from the rowversion docs page:

  The timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

I'd suggest you update the SSDT project to use rowversion to avoid this problem.


SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow

I would look at the event handlers (Package explorer tab shows this nicely). Something in there might be tamping down the failure or the DWH_STAGE container might have an explicit return status.
Something is off because a minimal reproduction works as expected


Error while importing a ".sql" file in database

Since you have an ASCII file, the script could be giving problems because it is came from a Windows machine and you are attempting to load it into mysql on a Linux machine.

You may want to try one of two things:

Using the mysql client in Windows, load the data remotely.
Use sql client in raw mode in one of two ways

mysql -hIP -r -uroot -p DN_name < filename.sql
mysql -hIP -B -uroot -p DN_name < filename.sql


How to copy tablespace from one database to another database in Oracle 11g

While copying a tablespace, it automatically includes data files and indexes associated with it. You need to make sure that tablespace is self contained.

Confirm that tablespace is self contained
execute DBMS_TTS.TRANSPORT_SET_CHECK('<TABLESPACENAME>', true);
Use the TRANSPORT_SET_VIOLATIONS view to check if any violations condition  exists
select * from TRANSPORT_SET_VIOLATIONS;
if any exist then fix them.
Make the tablespace read only
alter tablespace <TABLESPACENAME> read only;
Use EXPDP COMMAND to export the tablespace:

EXPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILE.DMP
LOGFILE=ERROR.LOG TRANSPORT_TABLESPACE=TABLESPACENAME

(You should have the rights necessary to execute IMPDP/EXPDP command and access rights to directory objects which map to the OS folder.)
Copy the datafiles to the target database using ftp or scp or somt other filesystem utility.
Make the tablespace READ WRITE again in the source database
Use IMPDP command to import it into target database:

IMPDP USERNAME/PASSWORD DIRECTORY=DIRECTORYNAME DUMPFILE=DUMPFILENAME.DMP
LOGFILE=ERROR.LOG

Check logfile for errors, if any
Make the tablespace read write in the target database:

ALTER TABLESPACE <TABLESPACENAME> READ WRITE


There might be a way to rearrange parts of the correlated subquery so that it can reference the outer level's products_id directly, rather than in a subquery/derived table of its own, but in the end I had too little patience to find it.
Instead, I can offer you a different approach to calculating the same results, which uses variables:
SELECT
  p.products_id
, (
    SELECT
      SUM(
        DATEDIFF(to_date, GREATEST(from_date, CURDATE() - INTERVAL 400 DAY)) * factor
      )
    FROM
      (
        SELECT
          IF(h.products_id = @p, @d, CURDATE() + INTERVAL 1 DAY) AS to_date
        , @d := h.inventory_date                                 AS from_date
        , (quantity > 0)                                         AS factor
        , @p := h.products_id                                    AS products_id
        FROM
          inventory_history AS h
        ORDER BY
          h.products_id ASC
        , h.inventory_date DESC
      ) AS v1
    WHERE
      v1.products_id = p.products_id
      AND v1.to_date >= CURDATE() - INTERVAL 400 DAY
  ) AS count
FROM
  (SELECT @p := null, @d := null) AS init
, products AS p
WHERE
  p.master_categories_id=264
;

With the help of two variables – @p to keep track of the current products_id, and @d to carry over inventory_date to the preceding record of the same product – the v1 derived table turns inventory_history into a set of date ranges, with a flag (factor) to indicate whether the range qualifies for the count result or not.
The derived table's definition is two levels deep, so it cannot reference the main query's products_id.  It simply returns the entire set.  However, the SELECT that is using v1 is where p.products_id is valid, and that is where filtering on products_id happens.
The count itself is then simply a matter of adding up the date ranges' lengths, using the DATEDIFF(end_date, start_date) function.  The starting day expression (GREATEST(...)) simply makes sure that the earliest date range's starting point, for the purposes of the query, is no earlier than 400 days from now.
Here is a link to a live demo of this solution at dbfiddle.uk using your test setup:

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=dc5baefec8902a99bf807115aaa235ba


Enabling TLSv1 in postgres12 / openssl1.1.1f

As Laurenz Albe pointed out, it is not possible to configure postgres to use a protocol version older than the MinProtocol specified in openssl.cnf.
It is however possible to make postgres use its own version of openssl.cfg. This way postgres can use TLSv1 without affecting the system default.
To achieve this I added the following line to the environment file in pg's config directory:
OPENSSL_CONF = '/etc/postgresql/12/main/openssl.cnf'

I then copied /etc/ssl/openssl.cnf to this directory and made the changes there i.e.:
# beginning of openssl.cnf
openssl_conf = default_conf

# end of openssl.cnf
[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT:@SECLEVEL=1

Now ssl_min_protocol_version = 'TLSv1' in postgresql.conf works and the rest of the system still uses the openssl default of TLSv1.2


How can I get SSMS to persist my custom keyboard shortcut?

On further investigation, this seems to be a bug in SSMSBoost, at least on the current version (3.0.6397).  Uninstalling it makes the keyboard shortcuts work normally.  :(


Get Primary records where N most recent Secondary records match condition

SET @@group_concat_max_len = 9;

SELECT  a.author_name
    FROM
    (
        SELECT  author_id,
                GROUP_CONCAT(best_seller ORDER BY  published_date DESC) AS bs
            FROM  Books
            GROUP BY  author_id
            HAVING  bs = 'true,true'
    ) AS x
    JOIN  Authors AS a USING(author_id) ;

Notes:

9 is based on true,true being 9 characters, and that is enough to catch the first two.
I'm assuming best_seller is a VARCHAR.
ORDER BY  published_date DESC -- the "latest"
HAVING -- can't use WHERE in this situation


Count and Group data based on consecutive occurrence

Problems of this kind are a variation of what is normally referred to as Gaps and Islands. You can see a good overview at The SQL of Gaps and Islands in Sequences

Step by step approach

This can be done with several intermediate steps. First, let's make a (derived) table, that we will store as t1, finding out at which points you start a new series of TaskPK values.

CREATE TEMPORARY TABLE t1 AS
SELECT
    PK, TaskPK,
    case when
        (SELECT TaskPK
         FROM tbl t_prv
         WHERE t_prv.PK < tbl.PK
         ORDER BY PK DESC
         LIMIT 1
        ) <=> TaskPK
    then 0
    else 1
    end AS start_of_series
FROM
    tbl ;

t1 contains:

PK | TaskPK | start_of_series
-: | -----: | --------------:
 1 |      1 |               1
 2 |      1 |               0
 3 |      2 |               1
 4 |      2 |               0
 5 |      5 |               1
 6 |      1 |               1
 7 |      1 |               0
 8 |      2 |               1
 9 |      2 |               0
10 |      5 |               1
11 |      5 |               0

NOTE: the <=> operator in MySQL is equivalent to NOT DISTINCT FROM in standard SQL. It allows for equality comparison with NULL values as well as NON-NULL ones.

Now that we know where every series starts, we can give each PK a series_number by adding together all the current and previous values of the column start_of_series (that's a cumulative sum). Without window functions, this must be done with an (awful, inefficient) subquery.

We store the result in a table t2:

CREATE TEMPORARY TABLE t2 AS
SELECT
    PK, TaskPK,
    (SELECT
        sum(start_of_series)
    FROM
        t1
    WHERE
        t1.PK <= tbl.PK
    ) AS series_number
FROM
    tbl
;

The content of t2 is:

PK | TaskPK | series_number
-: | -----: | ------------:
 1 |      1 |             1
 2 |      1 |             1
 3 |      2 |             2
 4 |      2 |             2
 5 |      5 |             3
 6 |      1 |             4
 7 |      1 |             4
 8 |      2 |             5
 9 |      2 |             5
10 |      5 |             6
11 |      5 |             6

At this point, what you want to do is just GROUP BY series_number, and take the value of TaskPK (just min(TaskPK) will do this job), together with a count(*):

SELECT
    min(TaskPK) AS TaskPK, count(*) AS count
FROM
    t2
GROUP BY
    series_number
ORDER BY
    series_number

This is the result you actually wanted:

TaskPK | count
-----: | ----:
     1 |     2
     2 |     2
     5 |     1
     1 |     2
     2 |     2
     5 |     2

Putting everything together

By substituting t2 and t1 by their definitions, you can put everything together in a single query:

SELECT
    min(TaskPK) AS TaskPK, count(*) AS count
FROM
    (SELECT
        PK, TaskPK,
        (SELECT
            sum(start_of_series)
        FROM
            (SELECT
                PK, TaskPK,
                case when
                    (SELECT TaskPK FROM tbl t_prv WHERE t_prv.PK < tbl.PK ORDER BY PK DESC LIMIT 1) <=> TaskPK
                then 0
                else 1
                end AS start_of_series
            FROM
                tbl
            ) AS t1
        WHERE
            t1.PK <= tbl.PK
        ) AS series_number
    FROM
        tbl
    ) AS t2
GROUP BY
    series_number
ORDER BY
    series_number
;

TaskPK | count
-----: | ----:
     1 |     2
     2 |     2
     5 |     1
     1 |     2
     2 |     2
     5 |     2

This query is quite complicated (there are five SELECT within it), and can probably be still simplified some.

You can find the whole code and data at dbfiddle here

This can be very hihgly simplified if you can use MariaDB's WINDOW functions, or MySQL 8.0 Window functions (not yet released for production as of now).


Is TEXT to VARCHAR(MAX) an implicit conversion?

As detailed in the excellent Microsoft Documentation, conversions from text to varchar can indeed be implicit.


Will one SQL instance work whilst the other 2 are patched?

Firstly, it always better to test patches in DEV environment before applying on Production.

To answer, there won't be downtime/interruption for Instance 3 while patching/removing path on Instance 1, all you need select particular instance during the wizard or command line as follows:

KBXXXX.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /InstanceName=Intance1

There might be restart required (manual restart) to proceed with Instance 2 patching after completing instance 1.

Shared features are updated (patched) once. In your case during the Instance 1 or which ever starts first, and not applied while patching other instances on the same server. We never experienced any interruption with shared features (considered following): But if you got Integration Services and it's heavily used you must find maintenance windows where it's got less overhead.

Client Tools Connectivity
Client Tools Backward Compatibility

My environment was 3 instances on 1 server, all of them are AG replicas. One primary other two secondary, and there are 3 servers total with same configuration.


How to remove specific characters from strings in sql

SELECT (
        REPLACE
                (CASE
                    WHEN RIGHT (COL, 1) = '-'
                        THEN LEFT(COL, LENGTH(COL) - 1)
                    ELSE COL
                END, '6|', ''
                )
        )
FROM Table


You can back up individual files and filegroups only when your database is using the full or bulk_logged recovery model, because you must apply log backups after you restore a file or filegroup; you can't make log backups of a database that's using the simple model.
Unlike full database and differential backups, a backup of a file or filegroup doesn't back up any portion of the transaction log. So, none of the transaction log is in a file or filegroup restore, and you still need to restore the log.
You can restore individual file or filegroup backups from a full database backup.
Immediately before restoring an individual file or filegroup, you must back up the transaction log. You must have an unbroken chain of log backups from the time the file or filegroup backup was made until the time the files or filegroups are restored.
After restoring a file or filegroup backup, you must restore all the transaction logs made between the time you backed up the file or filegroup and the time you restored it. Restoring these interim transaction logs guarantees that the restored files are synchronized with the rest of the database.


Who changed the size of the transaction log?

Most likely you did. Most cases database files, including transaction log, are configured for auto-growth. Auth-growth enables the file to increase its size as needed, any time is needed. The trigger for growth can be any activity that requires log space. The activity that triggered the growth is most cases ordinary, normal activity. The true problem is why is the log growing. Most times is simply lack of log truncation, see Factors That Can Delay Log Truncation. Other time it can be a single event that generates a lot of log and requires growth. But most often is just bad maintenance (lack of truncation).

To shrink the log now, see How to Shrink the SQL Server log.

If the log was explicitly increased in size, the event is likely captured in the default administrative trace.


Is automatic disabling of DML triggers possible during SSDT publish?

Based on my first-hand experience, SSDT refreshes table definitions before it refreshes triggers.

I had to manually disable all triggers before publishing for it to work (sample reasoning below). However, it is foolish to assume all table triggers were enabled before, so turning them all off then back on is prone to causing issues.

My solution was to create a script to generate the necessary SQL to turn off all currently-enabled triggers, and then to enable them again (make sure to set current database connection to your target database).

Warning: Single user mode will close all open connections. Make sure to re-enable multi-user mode after you're done. You can skip changing user mode altogether if nobody is holding any locks on your system.

SET NOCOUNT ON
DECLARE @DatabaseName NVARCHAR(255) = 'YOURDATABASE'
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
DECLARE @TableTriggersOnly BIT = 1
CREATE TABLE #Trigger
(
     SchemaName NVARCHAR(255)
    ,TableName NVARCHAR(255)
    ,TriggerName NVARCHAR(255)
    ,WasDisabled BIT
)
INSERT INTO #Trigger
(
     SchemaName
    ,TableName
    ,TriggerName
    ,WasDisabled
)
SELECT
     SchemaName = S.name
    ,TableName = T.name
    ,TriggerName = Tr.name
    ,WasDisabled = Tr.is_disabled
FROM
    sys.triggers AS Tr
    INNER JOIN sys.tables AS T ON
        T.object_id = Tr.parent_id
    INNER JOIN sys.schemas AS S ON
        S.schema_id = T.schema_id
WHERE
    Tr.is_disabled = 0
    AND (@TableTriggersOnly = 0 OR Tr.parent_class = 1)
DECLARE
     @SchemaName NVARCHAR(255)
    ,@TableName NVARCHAR(255)
    ,@TriggerName NVARCHAR(255)
    ,@WasDisabled BIT
DECLARE TriggerCursor CURSOR STATIC FOR
    SELECT
         SchemaName
        ,TableName
        ,TriggerName
        ,WasDisabled
    FROM #Trigger
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'DISABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
/*
 * YOUR STATEMENTS HERE
 */
PRINT '/*******************'
PRINT 'YOUR STATEMENTS HERE'
PRINT '*******************/'
/*
 * YOUR STATEMENTS HERE
 */
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'ENABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
DEALLOCATE TriggerCursor
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
DROP TABLE #Trigger

A particular table of mine has a trigger that was poorly written, encountering errors when handling multiple affected rows at a time, but the correction to the trigger to handle the case was never getting applied. So a column alteration in the table meant that SSDT was shifting data to a temp table then back, causing a multi-row invocation of the table trigger.


Scalar UDF causing performance issues in SQL Server

Scalar UDFs always affect performance. What you might have read is that you probably don't want to use them in your WHERE or JOIN ON clauses because that test is happening as part of the filtering stage and thus may force every single row to go through the UDF to determine the output.

This is compared to the less worse option of just using it in the part of the SELECT statement that specifies the columns/data you want to return; in which case you've already filtered everything and so there's presumably less to work on. i.e. Doing a test of the filtered data to return a bit field as to whether there's a match or not.

Apart from this, about making searches more performance, in your case you are searching for a string anywhere within another string, and that's bad. To make things faster you would need to make it so that you are only searching for a string at the beginning of the string (x like '%...') or end of the string (reverse(x) like ('%' + reverse('...') as a persisted computed column and then stored in an index).

Either way you would then go with dynamic SQL; iterate the temp table and build up a string with the SQL statement you want that codes in the specific search terms you are looking for in the WHERE clause; then run with sp_executesql. But it's terrible practice and sometimes a security nightmare.

But it would only go significantly faster if it's on those '%...' searches. Not '%...%'.

The other alternative is to investigate full-text indexing to see if your specific search terms are going to meet that criteria, but it's much more complicated, and you're going to have to crack open a book to learn it.


Connect to a Windows Authentication SQL server in SQL Server Authentication mode?

No. You'd need to be in "mixed mode" which is both SQL and Windows Authentication


Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):

CREATE PROCEDURE dbo.OptimizeInstanceMemory
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
     @thisNode      NVARCHAR(255) = CONVERT(NVARCHAR(255),
                                  SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
     @otherNode     NVARCHAR(255),
     @optimalMemory INT = 12288, -- 12 GB
     @sql           NVARCHAR(MAX);

  SET @sql = N'SELECT @OtherNode = CONVERT(NVARCHAR(255),
                        SERVERPROPERTY(N''ComputerNamePhysicalNetBIOS''));';

  EXEC [SERVER\INSTANCE].master..sp_executesql @sql,
    N'@OtherNode NVARCHAR(255) OUTPUT', @OtherNode OUTPUT;

  IF @thisNode = @otherNode
  BEGIN -- we're on the same node, let's make everyone happy
    SET @optimalMemory = 6144;
  END

  SET @sql = N'EXEC sp_configure N''max server memory'', @om;
    RECONFIGURE WITH OVERRIDE;';

  EXEC                   master..sp_executesql @sql, N'@om INT', @optimalMemory;
  EXEC [SERVER\INSTANCE].master..sp_executesql @sql, N'@om INT', @optimalMemory;
END
GO

EXEC [master].dbo.sp_procoption
  N'dbo.OptimizeInstanceMemory', 'startup', 'true';

Of course create it again on the other instance, swapping the linked server name used.

This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.

Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.

You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.


How to get the return value from a stored procedure executed with sp_executesql

You need to use the OUTPUT parameter, as it is described in the MSDN article:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS
RETURN 2

This code will give you the result:

declare @ret int

exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT
select @ret as result

result
-----------
2

(1 row(s) affected)


MySQL: can not get rid of “Using filesort” in a simple query

Here is your original query

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture
FROM online i join usertable u on i.uid = u.id
order by i.expiration DESC limit 0,12;

Try refactoring the query to coerce the retrieval of 12 rows before doing the JOIN

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT * FROM online ORDER BY expiration DESC limit 0,12) i
LEFT JOIN usertable u on i.uid = u.id;

The subquery may be forced to use the expiration index now.

You can also try this one:

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT id FROM online ORDER BY expiration DESC limit 0,12) ik
LEFT JOIN online i on ik.uid = u.id;
LEFT JOIN usertable u on ik.uid = u.id;

but you will need redo the expiration index

ALTER TABLE online DROP INDEX expiration;
ALTER TABLE online ADD INDEX expiration (expiration,id);

Give it a Try !!!


MS Access fail to recognize correct field width

Looking at your image the data looks like it is fixed width. You have a few options. You could:

Manually edit the columns in access. (fixed width).
Import the data into excel and use the "text to columns" feature to split the fields. Then import to sql.
Edit the file with a text editor like notepad++ and insert a delimiter between columns.
Import the data into SQL into a single column and then use TSQL to parse/substring the data into different fields. It's not a nice job but its doable.

If the data is a mix of fixed width and and delimited you can use a combination of the steps above.

If you have to repeat or automate this process, I recommend you go back to the source of this data and ask them to re-export with a delimiter, grant query access so you can import data directly, or get them to export directly to a database table for you.


Using Windows authentication only is typically considered the preferred configuration for SQL Server.  Windows authentication uses a series of encrypted messages to authenticate users, whereas SQL Server logins and passwords are passed across the network, which makes them less secure.

Windows Authentication confers an advantage in that there is a single centralized location for controlling access.

If you have a user who no longer should have access to anything on your network (for instance, an employee leaves the job), you simply disable their account in Active Directory, and voila, the user no longer has access across the board, including any SQL Servers where they have a Windows Authentication account.

If you don't have Active Directory for authentication you would need to create an account on the machine where SQL Server is installed for each user who wants access to SQL Server.

See the following links for further details:

Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

Choose an Authentication Mode

This question shows how you can still use Windows authentication for an application/user from an untrusted domain.

This MSDN topic on Authentication in SQL Server via .Net has some more interesting details.


You will find listener.ora in here:

  %ORACLE_HOME%/network/admin

The usual problem is that %ORACLE_HOME% isn't set as a system variable and not exposed to a command shell.

My quickest way to locate ORACLE_HOME from the command shell is:

C:\>where sqlplus
The full path to the executable will show. Go there.
Go one up, and add /network/admin

Another approach is to look in the registry.

Watch out for variable TNS_ADMIN which overrides Oracle default network directory.


How to make PostgreSQL default_tablespace work properly?

Per the PostgreSQL tablespaces documentation:

  If a database is created without specifying a tablespace for it, it
  uses the same tablespace as the template database it is copied from.

Per the CREATE DATABASE documentation:

  By default, the new database will be created by cloning the standard
  system database template1.

You could either alter the template1 database's tablespace to screwy, or create a new template database that has a tablespace of screwy, then specify that new database as a template when creating your screwy database.


pg_get_viewdef error while running pg_dump in PostgreSQL

Thanks guys for the suggestions, I think I found the problem.

Each night, several bash scripts drop and recreate some tables, which each time get a new OID. This would explain why a different OID was appearing in each successive error message, as suggested by Josh.

I believe that on some occasions, pg_dump started while some of these tables were still being dropped/recreated, hence the error message.