How to use two actions and a function in an Extended Events Histogram for deadlocks?

A target is a place where events captured by an Extended Events (XE) session go to live.  A histogram (previously synchronous_bucketizer or asynchronous_bucketizer) is a target only visible in the sys.dm_xe_session_targets DMV whilst the XE session is running.  An .xel file is also a target persisted to a file.  Although you can have multiple targets per session (eg ring buffer, histogram, event file, event counter) you cannot place a histogram in an event file, a target within a target.  Nor can you use custom functions within the XE session to modify the attributes of actions and events you capture.

However what you could do, is create an XE session using the histogram target and the sqlserver.database_xml_deadlock_report event and poll it on a regular basis, eg 5 minutes to the hour in my example below.  Run the code in the 'Setup' section first, then place the second bit of code in a SQL Agent task schedule to run each hour.  This code groups the information from the XE session up into date, hour and database format and persists it to a table:

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Setup
--IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
--  DROP EVENT SESSION XE_DeadlockHistogram ON SERVER;
--GO

IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
CREATE EVENT SESSION XE_DeadlockHistogram ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.histogram
(
SET slots = 32                      --< this should be greater than number of databases on your server; gets rounded up nearest power of 2 anyway
  , filtering_event_name = 'sqlserver.database_xml_deadlock_report' --,
  , source_type = 0                 -- < 0|1 = event|action
  , source = 'database_name'
)
WITH (
    MAX_MEMORY = 4096KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 10 SECONDS,
      MAX_EVENT_SIZE = 0KB,
      MEMORY_PARTITION_MODE = NONE,
      TRACK_CAUSALITY = OFF,
      STARTUP_STATE = OFF
    )
GO

-- Tracking table
IF OBJECT_ID('dbo.deadlockHistogram') IS NULL
    CREATE TABLE dbo.deadlockHistogram (
        deadlockDate        DATE NOT NULL,
        deadlockHour        TINYINT NOT NULL,
        dbName              SYSNAME NOT NULL,
        sessionStartTime    DATETIME NOT NULL,
        deadlockCount       SMALLINT NOT NULL,

        CONSTRAINT PK_deadlockHistogram PRIMARY KEY ( deadlockDate, deadlockHour, dbName )
        )
GO

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- SQL Agent Job Code START
-- Place this code in a SQL Agent job and schedule to be run every hour at 5 minutes to the
-- hour, eg 6.55, 7.55 etc
-- !!TODO Wrap in a stored proc; add to Utilities database
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, s.create_time AS sessionStartTime, SYSDATETIME() AS dateAdded, CAST( SYSDATETIME() AS DATE ) AS deadlockDate, DATEPART( hour, SYSDATETIME() ) AS deadlockHour
INTO #tmp
FROM sys.dm_xe_session_targets st
    INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address
WHERE s.name = 'XE_DeadlockHistogram'
  AND st.target_name = 'histogram'
GO

-- Collect current deadlock info from dm_xe_session_targets and tag with current date/hour
;WITH cte AS
(
SELECT
    CAST( dateAdded AS DATE ) AS deadlockDate,
    DATEPART( hour, dateAdded ) AS deadlockHour,
    v.c.value( '.', 'SYSNAME' ) dbName,
    sessionStartTime,
    s.c.value( '@count', 'INT' ) deadlockCount
FROM #tmp t
    CROSS APPLY t.target_data.nodes('HistogramTarget') td(c)
        CROSS APPLY td.c.nodes('Slot') s(c)
            CROSS APPLY s.c.nodes('value') v(c)
)
INSERT INTO dbo.deadlockHistogram ( deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount )
SELECT deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount
FROM cte t
WHERE NOT EXISTS
    (
    SELECT *
    FROM dbo.deadlockHistogram h
    WHERE t.deadlockDate = h.deadlockDate
      AND t.deadlockHour = h.deadlockHour
      AND t.dbName = h.dbName
    )
GO

-- Stop and restart the session to reset the historgram counts
IF EXISTS( SELECT * FROM sys.dm_xe_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = STOP
    RAISERROR( 'XE session %s stopped successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = START
    RAISERROR( 'XE session %s started successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
RAISERROR( 'XE session %s does not exist.', 16, 1, 'XE_DeadlockHistogram' )
GO

-- SQL Agent Job Code END
------------------------------------------------------------------------------------------------

Stopping and restarting the session during the Agent job is important to reset the histogram.  When deadlocks occur, the session will record XML that looks like this:

<HistogramTarget truncated="0" buckets="32">
  <Slot count="2">
    <value>tempdb</value>
  </Slot>
  <Slot count="1">
    <value>testCS</value>
  </Slot>
</HistogramTarget>

The code above shreds it and over time it will build up into data that looks like this:

This is a simple example, your business requirements may be more complex, but have a look through the code and let me know what you think.  You could probably also achieve something similar polling the system_health session (as mentioned by @Kin) but you would require some more heavy-weight XQuery which I can help you with if required.  The system_health session also targets to ring buffer which may wrap around / cycle, so this tightly scoped histogram session isn't a bad idea.

NB I am not using the sqlserver.xml_deadlock_report event as database name was always master, I guess where deadlock monitor runs.


The comma is the implicit join operator, ANSI SQL-89 syntax. No different than the comma in:

select ...
from a , b             -- <-- this comma
where a.aid = b.aid;

I think you can replace it with CROSS JOIN*, assuming a recent enough Oracle version.

The i, it and definition_xml are the aliases of the 3 joined tables (the 2 tables are base tables or views and the 3rd is something like a derived table, Oracle calls them "table collection expressions") but they are no different regarding how they can be joined or referenced.

The parentheses around the joins and the last table are completely optional. I added them just to show how the query is parsed:

SELECT
    EXTRACTVALUE(definition_xml.COLUMN_VALUE, ...) AS domain_name
FROM
      (   SDE.GDB_ITEMS_VW i
        JOIN
          SDE.GDB_ITEMTYPES it
        ON
          i.Type = it.UUID
      )
    ,                           -- or: CROSS JOIN
      (  TABLE(...) definition_xml
      )
;

* CROSS JOIN and the comma join operator have different precedence but this doesn't matter for the specific query. As a good practice though, it's best not to mix the comma join with explicit JOIN.


How to concurrency will be handled in this case?

The processes can theoretically work concurrently on different records, row locks are obtained as needed not in advance. However, in this case they are bound to run into each other sooner or later. If both have locked at least one record when that happens they will deadlock (both wait for the other to release a lock that it needs) and the database will kill one. Alternatively one will get the locks first and the other will wait until it commits; then it will update the records again.

In other words you can never end up with a scenario where A has updated some records and B some records. A will either update all or fail (due to deadlock/timeout) and B will also either update all or fail.

EDIT: if you do want to share load in this way you can get that effect using a select ... for update skip locked. Then locked records are simply ignored and the sessions can split the work, often quite useful in polling scenarios. Or use a parallel hint for the update and make a single session run in parallel.


Column types analysis of columns used in joins

Is it possible to do a deep type analysis on the source code base, views and stored procedures in order to identify joins of columns that don't have compatible types?

It's likely that is possible, but I'd like to recommend a different approach.

  ...and any discrepancies in the joined types can have huge performance impacts.

Rather than trying to comprehensively fix all of these mismatches, I would try and track down the ones that will give you the "biggest bang for your buck."  Use the sp_BlitzCache stored procedure from the SQL Server First Responder Kit.  You can run it like this:

EXEC sp_BlitzCache @DatabaseName = N'YourDatabaseName';

By default, this will give you the 10 most CPU-intensive queries from that database.

In the output, there is a "warnings" column, and you should be able to find queries that have these mismatched predicates and join columns by looking for "implicit conversion:"

For rows that have that warning, you can scroll over to the "Implicit Conversion Info" column for details, grab the execution plan from the "Query Plan" column, or just use the "Query Text" column to guide you to the right spot.

My main point here is that some of the mismatched data types might not be causing any real problems.  You don't want to waste time fixing those if the server is on fire because of a handful of specific columns in specific tables.


SQL Replication, transactions Replication

An alter procedure would not normally be replicated and so would not cause any problems. More than likely you're doing DDL operations and changing the tables and this is breaking the replication.

Michael Keleher has suggested your replication may not be set up to replicate schema changes:

  Open the properties of the publication, go to the Subscription Options section and change the Replicate schema changes property to true. Then all DDL changes applied to articles in the publication will be applied to the subscriber without the need to re-initialize.

If that works for you then great.

In my experience replication can cause a lot of normally benign deployments to fail, and so I prefer that replication be removed as part of a deployment and reapplied afterwards.

For a "lighter" touch, in your specific example the end of your deployment you can mark the subscribers as needing a snapshot (using sp_reinitpullsubscription) then trigger the snapshot agent (with sp_start_job).

It's roughly documented in BOL https://msdn.microsoft.com/en-AU/library/ms152466.aspx?f=255&MSPPError=-2147217396 though the instructions differ slightly depending on whether you're doing push or pull subscriptions.

However you probably need to dig a little more into what's actually going on to cause the problem because doing this kind of thing will make every single deployment go slowly for little reason.


Queries hang when accessing Oracle via ODBC and Python

I had a very similar problem where I had a query that would run, return 541 rows (every time) and then just hang. Wireshark looked like I was sending a packet and just not getting any response.

While troubleshooting the issue I added an order by clause to the query trying to see if I could find a potentially problematic record, but then the query executed just fine.

I don't know why the order by clause fixed it and I don't care too much to find out. Hopefully this simple solution will work for others too!


SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component

You probably cannot open the file due to lack of memory.  A 200GB 200MB .sql file is very, very big.  You could process the file using SQLCMD.exe instead of SQL Server Management Studio.


Oracle 'takes care' of the conversion between different NLS settings. As long as both NLS settings can store the same characters the there should be no problem. It is the same as the difference between the database and a client.

If for example the database of the customer is in UTF-8 and you create a database in ISO8859-P1 then you will loose (some) characters with accents and from non-western alphabets. The other way around gives no problem.


First of all, make sure that SYSTEM tablespace is not assigned as default or temporary tablespace for normal database users.

Second of all, one of the space consuming table in SYSTEM tablespace is AUD$. Next, as you have said you are just performing export/import, datapump creates master tables in the default tablespace of the user performing export job.

The master table is either retained or dropped, depending on the circumstances, as given here. You might have done the export using SYS user. If the job is failed master table will not be dropped.

Now, you can use the following query to see which object is consuming most of the space on this tablespace.

SELECT owner, segment_name, segment_type, extents, blocks, bytes
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;

Update:

If you need the audit trail then copy its records to another table, another tablespace if needed, and/or export the audit table, otherwise you can truncate it.

Login as sqlplus sys as sysdba

SQL> CREATE TABLE backup_aud$ AS SELECT * from sys.aud$;
SQL> truncate table aud$;

OR

You can moveaudit trail table, SYS.AUD$, to another tablespace using DBMS_AUDIT_MGMT
 Create new tablespace
 Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'NEW TABLESPACE');
END;
/


I think that you data model makes sense. I would add type to the "subclass" tables, create a unique constraint on (system_user_id, type) and create foreign key relationships pointing to system_user. Then add a check constraint to the subclass tables that makes sure that type has the correct value.
This avoids using a trigger.


How to set mariadb read_write for some specific tables while starting as read_only=1

Plan A:  Turn off read_only and hope that no one abuses it (accidentally or deliberately).

Plan B:  Run the 'cache' stuff as some user with SUPER so that it can bypass read_only.  This seems more secure than Plan A, but opens up other risks.


Behavior of Varchar with spaces at the end

Everything is just according the ANSI standard:

Trailing blanks explained:

  SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
  , General rules #3) on how to compare strings
  with spaces. The ANSI standard requires padding for the character
  strings used in comparisons so that their lengths match before
  comparing them. The padding directly affects the semantics of WHERE
  and HAVING clause predicates and other Transact-SQL string
  comparisons. For example, Transact-SQL considers the strings 'abc' and
  'abc ' to be equivalent for most comparison operations.

  The only exception to this rule is the LIKE predicate. When the right
  side of a LIKE predicate expression features a value with a trailing
  space, SQL Server does not pad the two values to the same length
  before the comparison occurs. Because the purpose of the LIKE
  predicate, by definition, is to facilitate pattern searches rather
  than simple string equality tests, this does not violate the section
  of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE
    @a = @b
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.

BUT if you want to differ them - you may decide to use DATALENGTH function instead of LEN, because

SELECT 1 WHERE LEN('John ') = LEN('John')

will put you 1 instead of

SELECT 1 WHERE DATALENGTH('John ') = DATALENGTH('John')

The solution is

to use DATALENGTH function to differ between strings
to cast the string into NVARCHAR type - may be better declare this type to parameter of SP


Is it possible to virtually vertical partition and oracle database table?

I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

Your whole premise behind the question is faulty. You do not need to split your data between tablespaces to spread the i/o across more disks, you need to increase the number of disks in your RAID10 or (better still) ASM array. You will get less performance gain, less space efficiency and far more maintenance trying to manually tune the i/o like you are suggesting.

ASM beats RAID10 primarily because it understands the data being written to it - so for example it can vary the stripe size between data blocks and logs.


Of course.

col_part as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_part_key_columns WHERE NAME in
  (
    select *
    from nom_tabla
  )
  GROUP BY name
  ORDER BY name
)

If nom_table has more than 1 row, the subquery returns more than 1 row as well.

This works:

   select
      t.owner as propietario,
      t.table_name as tabla,
      ' - ' as particion,
      nvl(pt.partitioning_type, ' - ') as tipo_part,
      ' - ' as subparticion,
      nvl(pt.subpartitioning_type, ' - ') as tipo_subpart,
      nvl(t.tablespace_name, ' - ') as ts,
      nvl(col_part.part_por, '-'),
      nvl(col_subpart.part_por, '-')
    from
      dba_tables t
      left join dba_part_tables pt on (t.owner = pt.owner and t.table_name = pt.table_name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_part_key_columns group by owner, name order by owner, name) col_part on (t.owner = col_part.owner and t.table_name = col_part.name)
      left join (select owner, name, listagg(column_name, ', ') within group (order by column_position) part_por from dba_subpart_key_columns group by owner, name order by owner, name) col_subpart on (t.owner = col_subpart.owner and t.table_name = col_subpart.name)
    where
      t.owner not in ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
      and t.table_name like 'TE%'
    ;


If you use the ROW_NUMBER function partitioning over "Code" and ordering by "Date" descending like the following query

SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
FROM   A
WHERE  CODE <> 'FOL'

you will get.

    RNO ID           CODE       FDATE
    1   customer001  INQ        09/30/2013
    2   customer001  INQ        01/01/2013
    1   customer001  REF        10/15/2013

Then if you use a left outer join using that same select statement on RNO where the code of the right table is 'REF' like below

SELECT a1.ID,
   a1.CODE AS Code1,
   a2.CODE AS Code2,
   a1.fdate AS fdate1,
   a2.fdate AS fdate2
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM A
  WHERE CODE <> 'FOL'
) AS a1
LEFT OUTER JOIN
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM   A
  WHERE  CODE <> 'FOL'
  ) AS a2
  ON a1.rno = a2.rno
  AND a2.CODE = 'REF'
  WHERE  a1.CODE <> 'REF'
  ORDER BY FDATE1

and finally use a where clause filter on the left table where code is not equal to 'REF' you will get the following.

    ID          CODE1   CODE2   FDATE1      FDATE2
    customer001 INQ     (null)  01/01/2013  (null)
    customer001 INQ     REF     09/30/2013  10/15/2013

Hope this helps or is what you are looking for.


Inner join in select statement within stored procedure

There are actually two problems with your first query.  The first is that you have an extra semi-colon for the subquery where you are getting the category name.  The second is that if that query returns more than one row then you will receive an error.  I've corrected it for you to eliminate both of those issues.

CREATE PROCEDURE sp_Select
AS
BEGIN
    SELECT [ProductID]
        ,[Name]
        , CategoryName = (SELECT TOP 1 tblCategories.CategoryName
                            FROM tblCategories
                            WHERE tblCategories.CategoryId = tblProducts.CategoryID)
        ,[Model]
        , Cast([RAM] AS NVARCHAR(10)) + ' ' + [RAMMemUnit] AS RAM
        ,Cast([SecondryMem] AS NVARCHAR(10)) + ' ' + [SecondMemUnit] AS Memory
        ,[OSVersion]
        ,[Processor]
        ,CAST([Battery] AS NVARCHAR(10)) + ' ' + [BatteryType] AS Battery
        ,UnitsInStock AS 'Quantity In Stock'
    FROM [tblProducts];
END

However, queries that have sub-queries like the above generally perform worse than ones that can achieve the same results with a sub-query.  It lets you access other fields from the tblCategories table without incurring additional sub-query costs, and you can more easily search for products that are in particular categories.

Some of the comments have mentioned changing the join from using a LEFT OUTER to be an INNER join.  I chose LEFT OUTER initially because that more closely matched the result set you would have received in your first query.  Essentially, it would have NULL's in place where the CategoryName would be assuming you had Products without Categories.  An INNER JOIN would eliminate products that had no matching category.

CREATE PROCEDURE sp_Select
AS
BEGIN
    SELECT P.[ProductID]
        ,P.[Name]
        , C.CategoryName
        ,P.[Model]
        , Cast(P.[RAM] AS NVARCHAR(10)) + ' ' + P.[RAMMemUnit] AS RAM
        ,Cast(P.[SecondryMem] AS NVARCHAR(10)) + ' ' + P.[SecondMemUnit] AS Memory
        ,P.[OSVersion]
        ,P.[Processor]
        ,CAST(P.[Battery] AS NVARCHAR(10)) + ' ' + P.[BatteryType] AS Battery
        ,P.UnitsInStock AS 'Quantity In Stock'
    FROM [tblProducts] AS P
        LEFT OUTER JOIN tblCategories AS C ON C.CategoryId = P.CategoryID;
END


How to identify columns order in a table

You should be using the sys.columns catalog view.  syscolumns is included only for backwards compatibility.  It's really a SQL Server 2000 system table that shouldn't be used in SQL Server 2008 R2.

select *
from sys.columns
where object_id = object_id('MyTable')
order by column_id

That should return the order of your columns.  Note, though, these column id's might not be sequential.


Is it possible to use Oracle XMLTABLE and %type column type selector?

You can't use the %TYPE construct in the COLUMNS clause.

However, depending on what you do with your data, it may be a good idea to not be too strict with it in this query. For instance, if we take the data from your previous question, note that the following doesn't result in an error:

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                   passing XMLTYPE(:xml)
  4                   columns
  5                      name   varchar2(1) path 'name',
  6                      family varchar2(1) path 'family'
  7                 ) x;

NAME FAMILY
---- ------
t    m
m    s

Whereas in most case if you have an XML file as input and a column is larger than specified in the table, this would be considered an anomaly.

This is why you could, if you intend to perform validation on the input file, load all columns as text (VARCHAR2(4000)) and then insert into your staging table. This will result into an appropriate error.


Import data to existing schema from dmp file

Check the documentation of the current database release

Oracle Database Error Messages
12c Release 2 (12.2)
E49883-13

In the documentation of the current release you will find only

  ORA-01775: looping chain of synonyms

This is not helpfull (The 11.2 documentation does not contain the message at all). In this case check the
documentation of the 9i release.

Oracle9i Database Error Messages
Release 2 (9.2)
Part Number A96525-01

It  may contain outdated but nevertheless useful information:

  ORA-01775 looping chain of synonyms

  Cause: Through a series of CREATE synonym statements, a synonym was defined
      that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2
CREATE SYNONYM s2 for s3
CREATE SYNONYM s3 for s1

  Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

So check if the package specification of XZB.P_IATA contains such a synonym.


Here is your original query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

Two Suggestions

Perform LIMIT 0, 25 on school table before the INNER JOINs
INNER JOIN school.scid to the other three(3) tables

Here is my propsed query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `school`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `school`.`scid` = `accredit`.`scid`
;

Give it a Try !!!

UPDATE 2012-06-25 12:13 EDT

To speed up the subquery, make sure you have this index:

ALTER TABLE school ADD INDEX ARCO_name_scid_ndx (ARCO_name,scid);

UPDATE 2012-06-25 12:18 EDT

AS pointed out by @yercube in his comments to my answer, you may want to attach the student-id from to degree_o and degree_id over to accedit. Perhaps something like this:

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`studend_id` = `degree_o`.`studend_id`
    INNER JOIN `accredit` ON `degree_o`.`degree_id` = `accredit`.`degree_id`
;


MongoDB UserNotFound when starting mongod

Ended up working by using a configuration file when running mongod. Don't know why.


Exception while inserting records in SQL Server (Error message id : 586)

It seems that this server has been configured in a different way than the others.

Using SSMS, right click over server instance in some of the servers where the job is running and check that ANSI NULLS and quoted identifier have the same value on this server.

If you prefer to use an script, have a look at:

SET ANSI NULLS
SET QUOTED_IDENTIFIER


As per MongoDB BOL Here To Reconfigures an existing replica set, overwriting the existing replica set configuration. To run the method, you must connect to the primary of the replica set.

rs.reconfig(configuration, force)

To reconfigure an existing replica set, first retrieve the current configuration with rs.conf(), modify the configuration document as needed, and then pass the modified document to rs.reconfig().

rs.reconfig() provides a wrapper around the  replSetReconfig command.

The force parameter allows a reconfiguration command to be issued to a non-primary node.

  WARNING: Avoid reconfiguring replica sets that contain members of different MongoDB versions as validation rules may differ across
  MongoDB versions.

The rs.reconfig() shell method can trigger the current primary to step down in some situations. When the primary steps down, it forcibly closes all client connections. This is by design. Since it may take a period of time to elect a new primary, schedule reconfiguration changes during maintenance periods to minimize loss of write availability.

{ force: true }

  WARNING:
  Using rs.reconfig() with { force: true } can lead to rollback
  of committed writes. Exercise caution when using this option.

Member Priority and Votes
Changed in MongoDB version 3.2.

Members with priority greater than 0 cannot have 0 votes.

Non-voting members must have priority of 0.

For example A replica set named rs0 has the following configuration:

{
   "_id" : "rs0",
   "version" : 1,
   "protocolVersion" : NumberLong(1),
   "members" : [
      {
         "_id" : 0,
         "host" : "mongodb0.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      },
      {
         "_id" : 1,
         "host" : "mongodb1.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      },
      {
         "_id" : 2,
         "host" : "mongodb2.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      }
   ],
   "settings" : {
      "chainingAllowed" : true,
      "heartbeatIntervalMillis" : 2000,
      "heartbeatTimeoutSecs" : 10,
      "electionTimeoutMillis" : 10000,
      "catchUpTimeoutMillis" : 2000,
      "getLastErrorModes" : {

      },
      "getLastErrorDefaults" : {
         "w" : 1,
         "wtimeout" : 0
      },
      "replicaSetId" : ObjectId("58858acc1f5609ed986b641b")
   }
}

The following sequence of operations updates the members[n].priority of the second member. The operations are issued through a mongo shell connected to the primary.

cfg = rs.conf();
cfg.members[1].priority = 2;
rs.reconfig(cfg);

As the above statement working in such as

The first statement uses the rs.conf() method to retrieve a document
containing the current configuration for the replica set and sets
the document to the local variable cfg.
The second statement sets a members[n].priority value to the second
    document in the members array. For additional settings, see replica
    set configuration settings.

To access the member configuration document in the array, the
statement uses the array index and not the replica set member’s
`members[n]._id` field.

The last statement calls the rs.reconfig() method with the modified
    cfg to initialize this new configuration. Upon successful
    reconfiguration, the replica set configuration will resemble the
    following:

{
   "_id" : "rs0",
   "version" : 2,
   "protocolVersion" : NumberLong(1),
   "members" : [
      {
         "_id" : 0,
         "host" : "mongodb0.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  },
  {
     "_id" : 1,
     "host" : "mongodb1.example.net:27017",
     "arbiterOnly" : false,
     "buildIndexes" : true,
     "hidden" : false,
     "priority" : 2,
     "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  },
  {
     "_id" : 2,
     "host" : "mongodb2.example.net:27017",
     "arbiterOnly" : false,
     "buildIndexes" : true,
     "hidden" : false,
     "priority" : 1,
     "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  }

],
   "settings" : {
      "chainingAllowed" : true,
      "heartbeatIntervalMillis" : 2000,
      "heartbeatTimeoutSecs" : 10,
      "electionTimeoutMillis" : 10000,
      "catchUpTimeoutMillis" : 2000,
      "getLastErrorModes" : {

  },
  "getLastErrorDefaults" : {
     "w" : 1,
     "wtimeout" : 0
  },
  "replicaSetId" : ObjectId("58858acc1f5609ed986b641b")

}
}

  Note : When updating the replica configuration object, access the replica set members in the members array with the array index. The
  array index begins with 0. Do not confuse this index value with the
  value of the members[n]._id field in each document in the members
  array.

For help you can type in mongo shell rs.help().

> rs.help()
        rs.status()                                { replSetGetStatus : 1 } checks repl set status
        rs.initiate()                              { replSetInitiate : null } initiates set with default settings
        rs.initiate(cfg)                           { replSetInitiate : cfg } initiates set with configuration cfg
        rs.conf()                                  get the current configuration object from local.system.replset
        rs.reconfig(cfg)                           updates the configuration of a running replica set with cfg (disconnects)
        rs.add(hostportstr)                        add a new member to the set with default attributes (disconnects)
        rs.add(membercfgobj)                       add a new member to the set with extra attributes (disconnects)
        rs.addArb(hostportstr)                     add a new member which is arbiterOnly:true (disconnects)
        rs.stepDown([stepdownSecs, catchUpSecs])   step down as primary (disconnects)
        rs.syncFrom(hostportstr)                   make a secondary sync from the given member
        rs.freeze(secs)                            make a node ineligible to become primary for the time specified
        rs.remove(hostportstr)                     remove a host from the replica set (disconnects)
        rs.slaveOk()                               allow queries on secondary nodes

        rs.printReplicationInfo()                  check oplog size and time range
        rs.printSlaveReplicationInfo()             check replica set members and replication lag
        db.isMaster()                              check who is primary

        reconfiguration helpers disconnect from the database so the shell will display
        an error, even if the command succeeds.

For your further ref Here and Here


Find who and when changed a specific value in the database

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled.

You can use various types of auditing for these tasks:

Standard auditing
Fine-Grained Auditing (FGA)
Triggers

With standard auditing, you can audit object and system privileges, such as SELECT * FROM HR.EMLOYEES and CREATE ROLE. Audit records can be written in database (the default) or in the files outside the database. You enable auditing with AUDIT statement, for example, AUDIT SELECT ON HR.EMPLOYEES, and disable it with NOAUDIT statement, for example, NOAUDIT CREATE ROLE. Standard auditing allows you to capture the user name which invokes the statement, the timestamp, the text of the executed SQL, and bind variables. The standard auditing records can be queried from the DBA_AUDIT_TRAIL view if auditing is configured to store them in database (which is default).

With FGA, you can audit specific columns and rows of the table based on audit condition, you can audit actions in specific time intervals (say, from 5 PM to 8 AM), you can even audit actions from specific IP addresses. You implement FGA with DBMS_FGA PL/SQL package. For example, you can audit queries and updates on the column SALARY of the table HR.EMPLOYEES with this FGA policy:

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees_salary',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'SELECT, UPDATE',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

The FGA audit records can then be queried from the DBA_FGA_AUDIT_TRAIL view if policy is configured to store them in database (audit_trail parameter in the code above).

With triggers, you can capture the previous and new values for the interesting tables. However auditing implemented with triggers is superseded by FGA. The audit records are inserted in the tables specifically created by you. As far as table auditing is concerned, you can use BEFORE STATEMENT and AFTER STATEMENT triggers to audit statements executing on the tables, or BEFORE ROW and AFTER ROW to audit the previous and new values for the columns.

The other facility I would give a shot is LogMiner. It allows you to analyze the online and archived redo logs and reconstruct the exact DML and DDL that were executed in the database. In Database Control (the web interface to control the database), take to the Availability tab, then click View and Manage Transactions). Here, you can specify the time range and objects you're interested in, and LogMiner will provide you the transactions list based on the parameters you specified. If you cannot determine the actual user, I would try to correlate the time of transaction with AUDIT SESSION records which are usually collected as part of mandatory auditing and can be retrieved from DBA_AUDIT_TRAIL view.


The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause.  So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE.  Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run.  I'm guessing based on the column names that that thing is setting "completed_date" to something which no longer NULL?  If so, you might want to create in index on (completed_date, created_date).  Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted.  If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.


Cassandra backup from all nodes or just one?

the short answer -> you have to take snapshots on all nodes.

As you pointed out, Cassandra is a distributed database. As an example, suppose you have 3 nodes with a replication factor (RF) of 2. Each node has primary responsibility for 1/3 of all the tokens in the ring. In addition, each node has a replica from the another node, and nodetool status will show "Owns 66.6%" (2 replicas / 3 nodes).

If you only backup one node, you only get the data on that node plus whatever replicas are being stored on that node. Since the data is distributed, you will end up missing some data unless you take snapshot on all nodes.


SQL Server performance sudden degradation

From MSDN:

"Insert Operations Occur on Ascending or Descending Key Columns
Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations
Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

You might use "EXEC sp_updatestats" from time to time on your system (scheduled some time) or use the function STATS_DATE on all the objects and see when their statistics were actually updated last time and if there was too much time since then, use UPDATE STATISTICS for that particular object. In my experience, even with Automatic statistics enabled we're still forced to update stats from time to time, because of insert operations that didn't trigger automatic update.

To add my personal code (used in a weekly job that builds dynamic statements for stats update):

select distinct
        'update statistics [' + stats.SchemaName + '].[' + stats.TableName + ']'
            + case when stats.RowCnt > 50000 then ' with sample 30 percent;'
            else
                ';' end
        as UpdateStatement
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName,
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from sys.stats st
            join sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join sysobjects so on so.id = si.id and so.xtype = 'U' --user table
            join sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 10 --more than 10% of the rows have changed
    or ( --update statistics that were not updated for more than 3 months (and rows no > 0)
        datediff(month, stats.LastStatisticsUpdateDate, getdate()) >= 3
        and stats.RowCnt > 0
    )

Here I get all objects where didn't have stats updated for more then 3 months or since the last stats update it had more than 10% of the rows changed.


Retrieve user id for users that have 5 specific rows

If your both tables have referential relationship with entry_id then you can fetch your result data through with 'IN' clause.
No need to write down as many AND operator in the query.

select t.author_id,t.entry_id,d.entry_id

from yourdatabsename.titles as t left outer join yourdatabasename.data  as d

on t.entry_id=d.entry_id

where d.skills IN ('Skill A','Skill B','Skill C','Skill D','Skill E')

Group By t.author_id

Order By t.author_id
;


What does the bit data type map to in a Java Domain Object

Incorrect syntax near '-'.

This tells me that you've named a database, table or column with a dash in it, not that POJO is having an issue mapping your columns.

As an example, if you've named your entity bar-none and POJO issues the following CREATE TABLE statement:

CREATE TABLE dbo.foo(bar-none BIT);

Or this one:

CREATE TABLE dbo.foo-bar(none BIT);

You will get this error:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near '-'.

And in fact even if you declare a data type that does not exist in SQL Server, such as Boolean, Frank or WhatTheHeckDataTypeIsThis, the parser will still barf with the above error due to the hyphen, before it even bothers validating the data type(s).

So, please check your table name and column names and be sure that your names don't use dashes, spaces or other special characters, that they aren't reserved words, and that each name you provide obeys the well-documented rules for identifiers. While you can get around this error if you can coerce POJO to properly delimit bad names with [square brackets], this is not a best practice.


is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

No, It is not possible.

when the table is involved in replication it does not allow you to drop the primary key.

below is an example as how I have changed the primary key of a replicated table:

table is dbo.CategoryImportMap, database is Product_Staging.

---------------------------------------------------------------------------------------------------
-- remove the table from the replication
---------------------------------------------------------------------------------------------------

USE [Product_Staging]
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

exec sp_dropsubscription @publication = N'Product_Staging', @article = N'CategoryImportMap', @subscriber = N'all', @destination_db = N'all'
GO
exec sp_droparticle @publication = N'Product_Staging',      @article = N'CategoryImportMap', @force_invalidate_snapshot = 0
GO

---------------------------------------------------------------------------------------------------
-- do the schema changes
---------------------------------------------------------------------------------------------------

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

SELECT @@TRANCOUNT

ALTER TABLE [dbo].[CategoryImportMap]
 DROP CONSTRAINT [PK_CategoryImportMap]

DROP index [UC_SegmentCategory]
on  [dbo].[CategoryImportMap]

ALTER TABLE [dbo].[CategoryImportMap]
ADD CONSTRAINT   [PK_CategoryImportMap]  PRIMARY KEY CLUSTERED   (  [MPlanSegmentCode] ASC  , [MPlanCategoryCode] ASC  )

SELECT @@TRANCOUNT

COMMIT TRANSACTION

---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging',
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go

-- test number of rows (source and destination)
sp_count 'CategoryImportMap'


What does changing Max Server Memory do besides clear the plan cache and (obviously) alter the memory setting)?

Ref: Server Memory Server Configuration Options

max server memory controls the SQL Server memory allocation, including (essentially any memory clerk found in sys.dm_os_memory_clerks):

the buffer pool
compile memory
all caches
qe memory grants
lock manager memory
clr memory

So all of these components will be effected by max server memory setting change.

On a side note this will be a very difficult path to find what is causing your CPU spike. I suggest you use following articles for your troubleshooting.

Troubleshooting SQL Server CPU Performance Issues by Joe Sack
How do I find out what is hammering my SQL Server?


MySQL: AVG of defined variable per month

Rewritten to only count logins after the last previous order:

-- Calculate the previous order date for each order
CREATE TEMPORARY TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
UserID  INT NOT NULL,
OrderDate   DATETIME NOT NULL,
Year    YEAR NOT NULL,
Month   TINYINT NOT NULL,
PreviousOrderDate   DATETIME
)
INSERT INTO Orders (OrderID, UserID, OrderDate, Year, Month, PreviousOrderDate)
    SELECT
        O2.orderid,
        O2.userid,
        FROM_UNIXTIME(O2.date) AS OrderDate,
        YEAR(FROM_UNIXTIME(O2.date)) AS Year,
        MONTH(FROM_UNIXTIME(O2.date)) AS Month,
        MAX(O1.date_time) AS PreviousOrderDate
    FROM
        xcart_orders AS O2
        LEFT JOIN xcart_orders AS O1 ON O2.userid = O1.userid AND O1.date_time < O2.date_time
    GROUP BY
        O2.orderid,
        O2.userid,
        O2.date

-- Calculate the average for each year and month
SELECT
    Year, Month, COUNT(*) AS Orders, SUM(PreviousLogins) / COUNT(*) AS AvgPrevLogins
FROM
    (
    -- Get the number of previous logins for each order
    SELECT
        O.OrderID, O.Year, O.Month, COUNT(L.userid) AS PreviousLogins
    FROM
        Orders AS O
        LEFT JOIN xcart_login_history AS L
            ON  O.UserID = L.userid
            -- Filter logins here in the join rather than in a WHERE clause, or you exclude orders that HAVE no previous logins (in practice, such may not exist)
            AND L.date < O.OrderDate
            AND (L.date > O.PreviousOrderDate OR O.PreviousOrderDate IS NULL)  -- Only count logins from after the last previous order (if one exists)
            AND L.status = 'success'
            AND L.action IN ('login', 'autologin')
    GROUP BY
        O.OrderID, O.Year, O.Month
    ) AS X
GROUP BY
    Year, Month
ORDER BY
    Year, Month


Show the number of all tables, views and base tables

information_schema.tables includes tables and views, so you only need:

select count(*)
from information_schema.tables
where table_type in ('VIEW', 'BASE TABLE')

If you want the numbers separately for each type, use a group by

select table_type, count(*)
from information_schema.tables
where table_type in ('VIEW', 'BASE TABLE')
group by table_type

The reason your statement isn't working is because you are creating a cross join (cartesian product) between the two system view.


Is it safe to use mysqldump to backup and restore a master-master cluster

I would strongly urge you to use Percona's XtraBackup. You're already using their storage engine - take the time to get to know this tool! Good reasons not to use mysqldump can be found here. If you really have to get text file SQL dumps of your data, check out mydumper, see here and here for why (disclaimer - haven't used).


I did not manage to delete my post from the phone. It's probably easiest to do each count as a subselect.

SELECT c.name
     , (select COUNT(i.character_id)
         From wiki_items i
         Where   c.character_id=i.character_id

       ) as  wiki_unlocks
     , (select COUNT(a.character_id)
         From wiki_armors a
         Where   c.character_id=a.character_id

       ) as  wiki_armors
     , (select COUNT(w.character_id)
         From wiki_weapons w
         Where   c.character_id=w.character_id

       ) as  wiki_weapons

FROM characters c


Why is setting open_files_limit making MySQL slow?

Looking back at my answer to your original post, I recommended a value for open_files_limit. Apparently, mysqld saw that 3145728 was the default value deemed safe enough to use. Hopefully, you changed ULIMIT in the OS to match. Otherwise, you will get lots  of table cache issues.

NOTE: mysqld will try to choose a fair default value for open_files_limit based on the amount of memory available at the time mysqld starts. There is a warning from the MySQL Documentation:

  mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed.

Even if you raise innodb_open_files, there is the possibility of caching file handles against the open .ibd files. See Percona's take on it : http://www.mysqlperformanceblog.com/2009/11/18/how-innodb_open_files-affects-performance/. That may also attribute somewhat to the slowness you are experiencing.

In your accepted answer, you stated you set open_files_limit to 1048570, a value mysqld felt was too low. Removing open_files_limit=1048570 from my.ini simply allowed open_files_limit to be defaulted to 3145728. You could increase it from that value, not lower it.


I'd say the difference is that your PL/PgSQL procedure runs in a single transaction.

If you run line by line in psql, unless you explicitly BEGIN and COMMIT, you're running in individual transactions. This can be quite a lot slower, but it also means that autoVACUUM can come along to free and reuse deleted rows, so subsequent UPDATEs can write new row versions into those freed spaces.

If you're in a single transaction, the system has to keep the old row versions around because it needs them in case you ROLLBACK the transaction or hit an error.

So you can't really do what you want in PL/pgSQL alone, unless you're willing to use hacks like dblink. You need to batch your work into a series of smaller transactions and since PostgreSQL doesn't yet support autonomous commit from within a PL/PgSQL function, that means dblink or an external client.


SQL 2005- stored procedure performance troubleshooting

Things like select count(*) are just fine as long as the where clause is indexed correctly.

DTA may or may not be of any use.

The actual and estimated plan are what you need to look at.  It'll tell you if there are any indexes which are missing.  The indexes which are used will be in the execution plan.


First, just to make sure that a collation is available or not, you can test in two ways:

Check the list of available collations (i.e. sys.fn_helpcollations()). Running the following query in SQL Server 2012 and 2014 should return 52 rows:

SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'ukrain%';

Try using the collation:

SELECT N'a' COLLATE Ukrainian_100_CI_AS;

If a collation is not available, it will let you know ;-)

Next, we should see if the collations work as expected (best not to fully trust documentation, right ;-) ?).

To test these two collations, run the following:

DECLARE @CollationTest TABLE (Col1 NVARCHAR(5));
INSERT INTO @CollationTest (Col1) VALUES (N'Ы'), (N'Ю'), (N'Ь'), (N'Ъ'), (N'Я'), (N'Э');

SELECT Col1 AS [Ukrainian_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Ukrainian_100_CS_AS;

SELECT Col1 AS [Cyrillic_General_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Cyrillic_General_100_CS_AS;

Results:

Ukrainian_100_CS_AS
----------------------------
Ъ
Ы
Э
Ю
Я
Ь

Cyrillic_General_100_CS_AS
----------------------------
Ъ
Ы
Ь
Э
Ю
Я

Those two orderings at least match the collations linked in the question (which makes sense given that those collation charts were done in 2009 and the _100 series of collations came out in SQL Server 2008).

The only difference seems to be in the initial sort order of one character: Ь (Capital Letter Soft Sign). Is the placement of one character a determining factor? You do need to mirror, as much as possible, the expectations of your customers. But, keep in mind that sort rules and initial ordering changes over the years.

Here are some examples of the Unicode spec for the Ukrainian locale. As you can see, both placements have been correct, though the "current" placement matches the Cyrillic collation, not the Ukrainian collation. The main release history of the locale-specific data starts here: CLDR Releases/Downloads

http://www.unicode.org/repos/cldr/tags/release-1-4/posix/uk_UA.UTF-8.src  (2006)
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5EF0>;<X0005>;<X008F>;IGNORE -- Ь
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5EFC>;<X0005>;<X008F>;IGNORE -- Я

http://www.unicode.org/repos/cldr/tags/release-1-9/posix/uk_UA.UTF-8.src  (2010)
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5D45>;<X05>;<X8F>;IGNORE -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5D46>;<X05>;<X86>;IGNORE -- Ь

http://www.unicode.org/repos/cldr/tags/release-21/posix/uk_UA.UTF-8.src   (2011)
<CYRILLIC_CAPITAL_LETTER_YA>
                           <X5D45>;<X05>;<X8F>;<CYRILLIC_CAPITAL_LETTER_YA>        -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>
                           <X5D46>;<X05>;<X86>;<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> -- Ь

http://www.unicode.org/repos/cldr/tags/release-24/common/uca/allkeys_CLDR.txt  (2013)
042C  ; [.1B43.0020.0008.042C] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1B5E.0020.0008.042F] # CYRILLIC CAPITAL LETTER YA        -- Я

http://www.unicode.org/repos/cldr/tags/release-27/common/uca/allkeys_CLDR.txt  (2015)
042C  ; [.1E56.0020.0008] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1E6D.0020.0008] # CYRILLIC CAPITAL LETTER YA        -- Я

And to be fair, we should keep in mind that collations handle more than sorting. They handle casing, transformations, etc.

So what should one do? It is probably best, regardless of the rest of this, to choose the locale that is most common for your customers to be using as their main Windows OS locale. This will at least give consistent behavior to how everything else should be running for them. Run the following to see on your system:

SELECT os.os_language_version AS [WindowsLocale],
       COLLATIONPROPERTY(N'Ukrainian_100_CS_AS', 'lcid') AS [SQLServerLocale-Ukrainian],
    COLLATIONPROPERTY(N'Cyrillic_General_100_CS_AS', 'lcid') AS [SQLLocale-CyrillicGeneral]
FROM   sys.dm_os_windows_info os;

Ask as many of your customers / potential customers as possible to check their Windows setup so that you can plan accordingly.


Is the system column "ctid" legitimate for identifying rows to delete?

The ROW SHARE lock taken by FOR UPDATE prevents concurrent write access that would change the physical location of the row. The manual:

  This prevents them from being locked, modified or deleted by other
  transactions until the current transaction ends. That is, other
  transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these
  rows will be blocked until the current transaction ends;

So the ctid should be stable for the duration of the command (or the transaction, even) unless you alter the row within the same transaction yourself. ctid is still a system column for internal use and the project will not offer any guarantees. If you have any unique (combination of) column(s) (including the PK) use that instead of the ctid.

However, I would use a CTE to materialize the selection and avoid unexpected results.

And without ORDER BY you select arbitrary rows for deletion. You might as well add SKIP LOCKED to minimize lock contention with concurrent transactions:

WITH cte AS (
   SELECT ctid
   FROM   calendar_event
   WHERE  user_id = 5
   LIMIT  100
   FOR    UPDATE SKIP LOCKED
   )
DELETE FROM calendar_event WHERE ctid IN (TABLE cte);

Related, with explanation for both considerations:

Postgres UPDATE ... LIMIT 1

About ctid:

How do I decompose ctid into page and row numbers?


Transactional replication subscriber vs AG secondary replica for reporting

The secondary replica can't be changed inside; and no you can't add indexes.

You can do subsets and add indexes on the transactional subscriber of something but you will likely encounter issues if you ever need to modify your source tables (such as upgrading the application) or reload data for any other reason; it's good practice to remove it before any change then reapply after and then apply your own changes.

Also replication often fails for any numbers of reasons. Basically just a warning that it's likely ongoing work and monitoring rather than set and forget.

For the latter part of your question, you don't have to add it to the AG if you don't want to. I'm not sure what you're getting at.


Inserts through Stored Procedure 6 time slower than direct insert

Make your code simple. Do not create dynamic query for simple one table insertion. for example:

create proc [dbo].usp_Add<Your table> List of Parameters--, datatype similar to table columns
AS
BEGIN
SET NOCOUNT ON
INSERT INTO <MY TABLE> (LIST OF COLUMNS) SELECT @Parameter1, @Parameter2
--OR
INSERT INTO <MY TABLE> (LIST OF COLUMNS) values(@Parameter1, @Parameter2)

END


Don't see the Microsoft.Jet provider in my new SQL Server 2008 R2 instance?

I believe that starting in 2008 R2 Jet was no longer included with the SQL Server install.  For R2 64 bit I believe you will need to download and install the Microsoft Access Database Engine Redistributable package found here.

This should install the necessary drivers for Office doc access from 64 bit SQL Server.


I have seen this done.  And while it's not something I would ever recommend, it does have some advantages from a process and skillset point-of-view.  The main reason you would try this is to have a consistent drag-and-drop process for building, and importantly, changing ETL processes.

Issues:

1) Performance.  Performing transformations in SSIS data flows is just slower than using TSQL.

2) Productivity.  Using TSQL is, perhaps, a more difficult skill, but for someone who can do both, it's massively more productive to build and test using TSQL than SSIS data flows.


Consider

Load the data into a different database, or even a different MySQL instance.
Locate the deleted rows.
Export them in some manner -- INSERT .. SELECT .. into another table; SELECT INTO OUTFILE ..., mysqldump, etc.
Load them into your real database.

Cost:  A bunch of your time.


Strange Sybase / SQL Server linked server issue

try

select * from OPENQUERY(sybase, 'select left(cast(note as varchar(1000)), 257) note from table')


Estimating resource utilization for one database

"SQLServer:Databases" perfmon counter will tell you Log activity.

If you can classify your connections (sql login user_a goes to database app_a, or connections from server B goes to database app_b, etc) then you can use a resource governor classifier function to assign connections to different workload groups.  After that, you can use the "SQLServer:Workload Group Stats" perfmon counter to see CPU usage.

Glenn Berry has DMV queries for CPU USage, IO Usage and Total Buffer Usage By Database.  https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-january-2018/


Postgres pg_read_all_stats default role cannot read from my schema

Looking at the definition of pg_stats:

SELECT ...
FROM pg_statistic s
  JOIN pg_class c ON c.oid = s.starelid
  JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text)
  AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));

you can see that you need to have SELECT rights on the column in question to see the statistics.

The underlying table pg_statistic can only be examined by the bootstrap superuser.

One way to work around this is to create a function owned by a superuser with SECURITY DEFINER that selects from pg_stats and returns the results.

Make sure to

REVOKE EXECUTE on this function FROM PUBLIC.
ALTER FUNCTION ... SET search_path = pg_catalog, pg_temp;


you can do it this way:
;WITH FirstQuery  AS (
    SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
    FROM     ConIssuance
    where EntryDate between @FirstDOM and @LastDOM
    GROUP BY EntryDate
    order by EntryDate asc
)
,SecondQuery AS (
    SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
    FROM     Probale AS s INNER JOIN
                      ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                      where S.EntryDate between @FirstDOM and @LastDOM
    GROUP BY s.EntryDate
)
,ThirdQuery AS (
    SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
    FROM     Bigbalprd
     where EntryDate between @FirstDOM and @LastDOM
    GROUP BY EntryDate
)
SELECT *
FROM FirstQuery
JOIN SecondQuery ON SecondQuery.EntryDate = FirstQuery.EntryDate
JOIN ThirdQuery ON ThirdQuery.EntryDate = FirstQuery.EntryDate;

If you have gaps (no records for some dates in some of three tables) you may need to use FULL JOIN with a bit more complex join conditions or even generate a list of dates (in case if you need a row for each date even when data for this date is not presented in any of your tables)


Despite Workbench freezing on me, I was able to connect to the MySQL instance via the command line and run CALL mysql.rds_kill(_each_and_every_process_id_one_at_a_time) successfully.


When you setup a data source, you can specify components of the connection string directly.

Simply add the following to the connection string:

Encrypt=True;TrustServerCertificate=True

That will force encryption between the SQL Server and the SSRS reporting server for the given data source.  Any report that uses that data source will obtain its data via an encrypted channel.  The TrustServerCertificate=True tells the client (SSRS) to trust the server certificate, even if it's a self-generated, or untrusted, certificate.


You should look at using a 3rd party schema change tool to avoid causing issues. You can start with pt-online-schema-change from the Percona Toolkit and if that doesn't meet your needs there's a schema change tool written by github named Ghost.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

https://github.com/github/gh-ost


Do I need to reindex my table in mysql after delete?

When there is a lot on insert and delete operations on a table, the table will be "fragmented", and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen)
hth


ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead.

This works:

INSERT INTO journals (ext_ids, title)
VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ((ext_ids ->> 'nlmid'::text))
DO NOTHING;


MySQL | Complex (for me) SQL Statement, how to?

Assuming that (post_id, meta_key) has a UNIQUE constraint, you can join twice to the Meta table:

SELECT
    p.*,                                          -- replace * with the columns you need
    CAST(m1.meta_value AS SIGNED) AS views,
    CAST(m2.meta_value AS SIGNED) AS maxViews
FROM
    posts AS p
  JOIN
    meta AS m1
      ON  m1.meta_key = 'views'
      AND m1.post_id = p.id
  JOIN
    meta AS m2
      ON  m2.meta_key = 'maxViews'
      AND m2.post_id = p.id
WHERE
    CAST(m1.meta_value AS SIGNED) < CAST(m2.meta_value AS SIGNED) ;

And here is the SQL-Fiddle (thnx to @bluefeet)


Query against history table not using index (Oracle 12c)


Fetch partitions where values are present

SELECT DISTINCT
       format(
          'calendars_y%s_m%s',
          lpad(CAST(extract (year FROM q) AS text), 4, '0'),
          lpad(CAST(extract (month FROM q) AS text), 2, '0'))
FROM (VALUES (DATE '2021-01-01'),
             (DATE '2021-02-01'),
             (DATE '2021-02-02')) AS q(q);

       format
---------------------
 calendars_y2021_m01
 calendars_y2021_m02
(2 rows)

But I seriously doubt that it is a good idea to do that on foot. You will be better off letting the PostgreSQL optimizer figure out which partitions to scan.


Some notes first:

Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2.
Adding account_type to Payments (and modifying accordingly the foreign key to Payment_types) seems like the most sensible approach and it will solve issue #3.
The two changes together will solve issue #1.
Oracle does not have ON UPDATE CASCADE option, so this issue (updating the account_type of a Payment_type to update the related Payments) needs to be addressed by either an update trigger or by a stored procedure (that will also check that related Accounts also exist (or are added) for all Stores that have Payments of the (updated) account_type.

The suggested design, assuming you also have an Account_types table (I renamed a few columns but that is up to you and your preferences / conventions):

Account_types
---------------
account_type_id  PK

Payment_types
---------------
account_type_id  PK, FK -> Account_types
payment_type_id  PK

Stores
--------
store_id         PK

Accounts
---------------
account_type_id  PK, FK1 -> Account_types
account_id       PK
store_id             FK2 -> Stores

Payments
---------------
payment_id       PK
amount
account_type_id      FK1, FK2
payment_type_id      FK1 -> Payment_types
account_id           FK2 -> Accounts

Note how both foreign keys from Payments use composite keys.


MySql Switching Masters During Failover with log-slave-updates

The warning about --log-slave-updates will only apply if you had multiple intermediary 'Master/Slave' servers.

The warning is this (my emphasis):

  Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master

But in your scenario, Slave 2 is not changing masters, it will still point to the same Master 2 server it always was at.

So now, in case of Master 1 failing, you will need to do two things:

Make sure your applications point to Master 2
Follow the instructions on promoting one of the three slaves to be new Master 2
make sure to enable --log-slave-updates on the new Master 2


There is no "ownership", only permission to read/write/drop/etc.  However, it is possible to have conventions of how permissions are GRANTed that sells a lot like "ownership".

Admin (typically 'root'):

GRANT ALL PRIVILEGES ON *.* TO root@...

"Owners":

GRANT ALL PRIVILEGES ON Leslie_database.* TO Leslie@...
GRANT ALL PRIVILEGES ON Dana_database.* TO Dana@...

That lets Leslie do everything with Leslie_database, but he/she cannot touch Data_database.

Note that the syntax schema.DBO.tablename does not exist in MySQL, only dbname.tablename.

Close enough to "ownership"?

GRANTs can also be done at the "table" level, and even the "column".

You could let Leslie "see" Dana_database:

GRANT SELECT ON Dana_database.* TO Leslie@...


How to monitor general performance and wellbeing of a database?

In general I agree somewhat: if the customers are happy, don't start tuning.
But there are certainly things that can go wrong without you noticing before it is too late. Here is a random list of things that should cause an alert, without a claim to completeness:

Is there enough free disk space?
Is the CPU overloaded?
Is the I/O subsystem overloaded?
Does the database grow unreasonably?
Are there problems with WAL archiving?
Are there too many client connections?
Are there long running queries?
Are there transactions that remain open for a long time?
Are there databases that are in danger of suffering data loss from transaction wraparound?
Can you connect to the database and run a simple query?
Is replication delayed too much?
Are there any tables with too many dead tuples?
Did you get errors from data corruption?
Are there any stale prepared transactions?

pgbench is more useful than you think: you can use it with custom scripts that even support a simple scripting language to simulate different workloads.
But of course it is essentially a tool to generate artificial load to test the performance impact of certain changes and to make sure you didn't misconfigure PostgreSQL or the operating system.


when do lock actually happen in a transaction?

You can see for yourself using DMVs. In SSMS open two query tabs. Paste this into one:

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_locks

Paste your statements into the other.

Run my queries. This will show background activity on the server which can be ignored.

Run just your BEGIN TRAN A; statement and re-run the DMV queries. You'll see there's a new transaction with name=A but no further locks.

Now hightlight just your select statement and run it and also the DMV queries again. Nothing has changed! What happened to the S locks? Well, running under the default READ COMMITTED isolation level the locks are release as soon as the DB engine has finished processing the particular row. See section "SQL Server Locking Read Committed" here. So the S locks were taken while the query was execution but had been released by the time the DMVs were examined again. (You can see the locks and releases happening using extended events. There are queries in this answer which you can modify to see this for yourself.)

We can make the locks remain to the end of the transaction using the HOLDLOCK table hint. Change your SELECT to ..FROM Production.Products WITH(HOLDLOCK).. and re-run the SELECT and the DMV queries. You will see shared locks on the table (and other objects).

Run just the UPDATE statement. The shared (S) locks have become exclusive (X) locks.  Because these locks are protecting altered data they are always held until the end of the transaction - no additional hints are required.

Running your COMMIT will release the locks and remove the transaction.

For your original question - when was the exclusive lock taken on row with productid 3 - the answer is, when the query processor came to that particular row as it was working through the query plan generated for that update statement.


How can I implement full-text search on a single value in a text[] column?

There are a lot of problems with using text[] like this, but fundamentally I agree @jjanes when he says,

  How will you know which member of the array gets what kind of formatting? If that logic is hard-coded into the position of the member in the array, then why not just have multiple columns and key the formatting to the column names?

You can keep this more direct and simpler by following a golden rule,

If the elements in the array can not be shuffled, then the order matters and the elements are ordinal
If the order matters, you should not be using an ARRAY type, regardless of Full Text Search.

Moreover, the to_tsvector call is a pretty bad idea, this design is problematic too. ARRAY is not for schema-less design, and the position in the array should not itself be information need to render the data in the app.

As a side note, in a normalized display you can do weighing/ranking with FTS. In order to that with your schema, you'd have to move from the schemaless abuse of text[] to an actual schema'd design anyway.

Abomination

If you decide to go down this route, this should be substantially faster and cleaner. But, this is a horrible idea.

CREATE AGGREGATE tsvector_agg (tsvector) (
  SFUNC = tsvector_concat,
  STYPE = tsvector
);

CREATE OR REPLACE FUNCTION text_array_to_tsvector( mytext text[], out tsv tsvector )
AS $func$
  BEGIN
    SELECT INTO tsv
      tsvector_agg(to_tsvector(t))
    FROM unnest(mytext) AS t;
    RETURN;
  END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

Compare the two,

EXPLAIN ANALYZE SELECT to_tsvector(array_to_string(ARRAY['foo', 'bar', 'baz'],' '::text));
EXPLAIN ANALYZE SELECT text_array_to_tsvector(ARRAY['foo', 'bar', 'baz']);

See also

How can I implement full-text search on a value in a JSONB column?


While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1

Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193)

Using an index in this case is beneficial as there are hardly any rows matching your WHERE condition.

In the second case, there are around 1080 rows that meet your condition. Going through the index for each of these would be expensive when compared to a sequential scan. (More disk movements for reading index entries and then touching the actual table rows.)

Seq Scan on mytable (cost=0.00..57.80 **rows=1080** width=193)

You can still try forcing the planner to use the index for the second case by temporarily disabling a sequential scan. You can achieve that with:

BEGIN;
set enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
COMMIT;

The actual time taken by the above statement is likely to be more as compared to a sequential scan selected by the planner.

Note: Sequential scan is not always bad. If you are selecting a major chunk of your table it is faster to read the entire table and filter the rows rather than jumping between the pages of the index and the table itself.


Can't deploy SSAS Tabular Model in Visual Studio

I just found out why I could not deploy my Tabular model to the server:

I tried to deploy it on the SQL Server (where my source DW is located), but obviously the Tabular Model needs to be deployed on the Analysis Server


Cannot restore azure database to local due to missing master key

Restoring a BACPAC isn't the same as restoring a SQL Server native backup (created using the BACKUP DATABASE syntax).

A native backup using BACKUP DATABASE is created by exporting all schema & data by backing up at the page level into backup files. By convention, these generally have a .bak file extension. A RESTORE DATABASE does the inverse to create a database by copying the data pages out of the .bak and into the database. (It's a little more involved than this, but that's the big picture.) If the source database is encrypted with TDE, the data pages in the backup are also encrypted.

A BACPAC is created by generating CREATE statements for all of the schema, then doing an export of data. The .bacpac file itself is actually just a zip file of the CREATE scripts & data export. When doing a "restore" of the BACPAC, it simply executes the CREATE scripts, then does an import of the data. If the source database is encrypted with TDE, the exported data within the .bacpac file is not encrypted.

If you want to create a TDE-encrypted database using a BACPAC, simply pre-create an empty database and enable TDE on that database. Then, import the BACPAC into that empty database, and Bob's your uncle.


SSIS File System Task Editor closes unexpectedly

I've been having the same issue. I have managed to get around it by manually entering the variable I'm using in the "Destination" property on the Properties window for the File System Task.  For instance, "User::DestFileName".


Find parent rows that have identical sets of child rows

This is a generalization of the relational division problem. No idea how efficient this will be:

; WITH cte AS
( SELECT RecipeID_1 = r1.RecipeID, Name_1 = r1.Name,
         RecipeID_2 = r2.RecipeID, Name_2 = r2.Name
  FROM Recipes AS r1
    JOIN Recipes AS r2
      ON r1.RecipeID <> r2.RecipeID
  WHERE NOT EXISTS
        ( SELECT 1
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
            AND NOT EXISTS
                ( SELECT 1
                  FROM RecipeIngredients AS ri2
                  WHERE ri2.RecipeID = r2.RecipeID
                    AND ri1.IngredientID = ri2.IngredientID
                    AND ri1.Quantity = ri2.Quantity
                    AND ri1.UOM = ri2.UOM
                )
         )
)
SELECT c1.*
FROM cte AS c1
  JOIN cte AS c2
    ON  c1.RecipeID_1 = c2.RecipeID_2
    AND c1.RecipeID_2 = c2.RecipeID_1
    AND c1.RecipeID_1 < c1.RecipeID_2;

Another (similar) approach:

SELECT RecipeID_1 = r1.RecipeID, Name_1 = r1.Name,
       RecipeID_2 = r2.RecipeID, Name_2 = r2.Name
FROM Recipes AS r1
  JOIN Recipes AS r2
    ON  r1.RecipeID < r2.RecipeID
    AND NOT EXISTS
        ( SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
        EXCEPT
          SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri2
          WHERE ri2.RecipeID = r2.RecipeID
        )
    AND NOT EXISTS
        ( SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri2
          WHERE ri2.RecipeID = r2.RecipeID
        EXCEPT
          SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
        ) ;

And another, different one:

; WITH cte AS
( SELECT RecipeID_1 = r.RecipeID, RecipeID_2 = ri.RecipeID,
          ri.IngredientID, ri.Quantity, ri.UOM
  FROM Recipes AS r
    CROSS JOIN RecipeIngredients AS ri
)
, cte2 AS
( SELECT RecipeID_1, RecipeID_2,
         IngredientID, Quantity, UOM
  FROM cte
EXCEPT
  SELECT RecipeID_2, RecipeID_1,
         IngredientID, Quantity, UOM
  FROM cte
)

  SELECT RecipeID_1 = r1.RecipeID, RecipeID_2 = r2.RecipeID
  FROM Recipes AS r1
    JOIN Recipes AS r2
      ON r1.RecipeID < r2.RecipeID
EXCEPT
  SELECT RecipeID_1, RecipeID_2
  FROM cte2
EXCEPT
  SELECT RecipeID_2, RecipeID_1
  FROM cte2 ;

Tested at SQL-Fiddle

Using the CHECKSUM() and CHECKSUM_AGG() functions, test at SQL-Fiddle-2:
(ignore this as it may give false positives)

ALTER TABLE RecipeIngredients
  ADD ck AS CHECKSUM( IngredientID, Quantity, UOM )
    PERSISTED ;

CREATE INDEX ckecksum_IX
  ON RecipeIngredients
    ( RecipeID, ck ) ;

; WITH cte AS
( SELECT RecipeID,
         cka = CHECKSUM_AGG(ck)
  FROM RecipeIngredients AS ri
  GROUP BY RecipeID
)
SELECT RecipeID_1 = c1.RecipeID, RecipeID_2 = c2.RecipeID
FROM cte AS c1
  JOIN cte AS c2
    ON  c1.cka = c2.cka
    AND c1.RecipeID < c2.RecipeID  ;


Why SQL Server has 900 byte index size limit

From Microsoft SQL Server 2008 Internals:

  In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first.

At 900 bytes per key, you can have a maximum of 8 index entries per page.  As you decrease the maximum number of index entries per page, you vastly increase the number of levels required to store any given index.  The smaller the key size, the more efficient the index, since it requires fewer pages of data.

As of SQL Server 2016, the maximum byte size for non-clustered indexes has been increased to 1,700 bytes, and 32 columns.


Alternatives for SSMS solutions & projects?

Reading between the lines, I guess the expectation/plan was for the SQL Server Data Tools (SSDT) project type to be extended to SSMS for the release of SQL 2012. Evidently that didn't happen, so we're stuck with the (frankly dreadful) SSMS interpretation of a project.

If you want something to manage your database source, you should be diving deep into SSDT. I'm currently using it for one greenfield project and also moving a large brownfield database to it. Minor niggles and pain points but no show stoppers and we're getting payback on the time invested already.

Jamie Thomson, who blogged extensively on VS2010 Database Projects (the predecessor), has started to publish articles on SSDT and appears to be as big a fan as I am. Bob Beauchemin is another early adopter worth following.

If you want to organise a collection of random scripts (rather than a full database build) you can but support isn't quite "first-class". To do so, create a standard SSDT project type and change your scripts to compilation type "None" in the file properties.


Anemometer - query on analysing the slow logs

From the documentation:

Anemometer is a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a weather station that measures wind speed. SQL queries are like the wind -- ephemeral and hard to get a hold of. Anemometer helps you get a handle on your queries; it makes it easier to figure out what to optimize and how to track performance over time.
Anemometer relies on the Percona Toolkit to do the slow query log collection. Specifically you can run pt-query-digest. To parse your slow logs and insert them into a database for reporting.

From the documentation of pt-query-digest

Usage
pt-query-digest [OPTIONS] [FILES] [DSN]
pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no FILES are given, the tool reads STDIN. The optional DSN is used for certain options like --since and --until.
[...]
Capture MySQL protocol data with tcppdump, then report the slowest queries:
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

tcpdump monitors real-time data... that is, in principle, the answer is yes, although not by looking at the slow log. However the actual setup doesn't look easy, and requires running from the server machine (as tcpdump works locally), not by remotely querying the database.


Data type for storing an array of flags (a bitmap/bit array)

I can't advocate strongly enough not to use a single field for this.

I'm currently dealing with maintaining a very large dataset with a bigint bitmask field and it's a bit of a performance nightmare.

If you check a single bit it's fine.  If you check more than one bit performance degrades very quickly.

Due to the nature of bitmask integers, the data distribution will be very imbalanced and you'll get suboptimal plans.

Multiple bit checks result in range or index scans with a function running against every row.  It's a mess.

My workaround was simple - I made a table to store the PK for each of the conditions to be checked.  This is counter-intuitive initially but the space needed is low (you only store the PK) and lookups are lightning fast, especially if you use a UNIQUE CLUSTERED INDEX.

You can add as many conditions as you want without affecting your main table, and updates also don't affect your main table.

Indexing is simple since you just index all the lookup tables individually, and since your clustered key is the same on your main table and the lookups all your evaluations are merge joins which are very efficient.


Sort table rows and save the row numbers using UPDATE

Join to a subquery that computes numero with the window function row_number():

UPDATE movimientos m
SET    numero = sub.rn
FROM  (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub
WHERE  m.id = sub.id;

Details for UPDATE syntax in the manual.

If you have concurrent write access you need to lock the table to avoid race conditions.

Note that updating every row in a table is expensive either way. The table typically grows to twice its size and VACUUM or VACUUM FULL may be in order.

Depending on your complete situation it may be more efficient to write a new table to begin with. Related answers with instructions:

Best way to populate a new column in a large table?
Optimizing bulk update performance in PostgreSQL

I am not convinced, though, that you need the column numero in your table at all. Maybe you are looking for a MATERIALIZED VIEW. Recent related answer on SO:

Global row numbers in chunked query


How to copy selected table data from sql server to mysql on a daily basis?

You have a few options:

Set up a linked servers on SQL Server against the MySQL db, and then join the data as needed on the SQL Server side
Having set up the linked server, you can also copy the table over to a new location using standard SQL (or create a table in MySQL and copy data in).
You can also use Data Transformation Services in SQL Server to export into a csv or similar, to import in MySQL using a csv import tool.

Which one is best depends to some extent on size of the record set and how much of it you need. My preference would be to use DTS and go from there.

Source


GIST exclude constraint with a specific value

It sounds like you need a partial constraint.
alter table a_table add constraint foobar exclude using gist
    (tstzrange("start_time", "end_time") WITH &&) where (active);


What does this margin symbol in the SSMS text editor mean?

I found the answer on Stack Overflow, as soon as I remembered that SSMS is a 'special flavour' of the Visual Studio Shell.

In short, it's a Task List item, which is toggled on/off with Ctrl-K, Ctrl-H. Items can be displayed with the menu View/Task List.


Determine record count grouped as year-month for each user including 0 counts

I don't have a MySQL instance handy to test and confirm that this works for you, but this should work.  It assumes that you have at least one entry for every month in the table.

SELECT DateTable.MonthDay
    , RecordCount.Records
    , RecordCount.created_by
FROM (
    SELECT DISTINCT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
    FROM article
    ) AS DateTable
    LEFT OUTER JOIN (SELECT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
                        , created_by
                        , COUNT(id) AS Records
                    FROM article
                    ) AS RecordCount ON RecordCount.MonthDay = DateTable.MonthDay
WHERE DateTable.MonthDay >= '4/1/2017' AND DateTable.MonthDay <= '10/1/2017'


Primary Key Index on a non-filtered column has a very high total lock wait time

Notice that you're talking about a PRIMARY KEY CLUSTERED (which means it's the table itself) so you don't need a query like WHERE TokenID = 1 to hit that index. From your scenario most of the reads are from lookups (do you understand what is a lookup read method?) what means that most of the queries are something like SELECT TokenID, Column2, Column3 WHERE Column2 = 1 and you have an index for Column2 that doesn't include TokenID, Column2 and Column3.

Here's how I would interpret that output you got:

Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup)

2,259,000 seek: It's a considerable value, but a seek is usually the best method of accessing an index and there's little room for
improvement here.
934,476 scan: It's usually the worst access method, but this value compared to the others might not have the biggest impact to be
the first fixed.
6,873,373 lookup: By far the biggest number and deserves some attention as this could be caused by a few queries repeatedly hitting nonclustered indexes that could be improved to do seeks instead.

Writes: 1,399,277, 314 rows;

These can cause blocks on the reads or get blocked because of them,
so if I can make those reads finish their job faster (like tuning the
lookups to become seeks by improving either the indexes or the
queries) the time a read would have to wait for the write to
finish (or vice versa) would be reduced.

With that information I would search among the queries hitting the table [Authentication].[Tokens] what are doing the lookup read. Once they are improved I would run the sp_BlitzIndex again to measure the results of the tuning.

Further reading:

Brent Ozar explains the sp_BlitzIndex Aggressive Indexes and How to Fix sp_BlitzIndex Aggressive Indexes Warnings.

Pinal Dave has an article on SQL SERVER – Removing Key Lookup


Help Outputing MySQL General Log to Named Pipe

Every step in /var/lib/ needs to have at least 755 permissions.  That is, each directory needs to be readable and x (searchable) by user mysql.  (Technically, none of those steps should be owned by mysql, so 005 is sufficient.  However, you may find 750, which won't work.)

/var/lib/mysql/, itself needs to be owned and writable by mysql (700 or better).


How to combine two select statements result column wise?

Selecting UNION will append the results below the existing result set.
If you want the second query's results as an additional column you will need to write it as a single query and JOIN the additional column.

You can do this by including the 'second' query as a subquery and joining the results together.
Without changing your existing query structure:

SELECT      count1.DESCRIPTION,
            count1.[COUNT],
            count2.COUNT2
FROM
(
    SELECT TC.DESCRIPTION,
                count(TE.CategoryID) AS COUNT
    FROM tblCategory TC
     LEFT OUTER JOIN tblEvent TE
            on TE.CategoryID=TC.NO
            AND TE.AssetID IN (
                            SELECT ASSET_NO
                            FROM tblAsset
                            WHERE EQUIPMENT_ID=1
                                )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION
) count1
LEFT OUTER JOIN
(
    SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
    FROM tblCategory TC
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=2
                        )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION
) count2 ON count2.desc2 = count1.DESCRIPTION

Of course, this assumes your value for TC.DESCRIPTION is unique, otherwise you will potentially generate duplicate rows. If you have a primary key to join in instead this is preferable, however the underlying table structure was not given in the question.


How to find MySQL users causing high load?

The performance schema provides statistics aggregated by account (user + host), by user, and by host.

See for example table:

performance_schema.events_statements_summary_by_account_by_event_name

Reference manual
https://dev.mysql.com/doc/refman/5.7/en/statement-summary-tables.html

This table will show statement statistics (aka, queries) broken down per account ... anyone causing heavy traffic (high number of queries) or expensive computation (a few very expensive and long latencies queries) should be very visible in these statistics.

[Edit] Example of query:

select `USER`, `HOST`, SUM(COUNT_STAR) AS TOTAL
  from events_statements_summary_by_account_by_event_name
  group by `USER`, `HOST`
  order by TOTAL DESC;

will show how many queries each account do, ordered by count descending.


how to identify the transactions that are causing the transaction log to grow?

Try this extended event (change the filename path accordingly).  I've used it in the past to help me track down what was causing unexpected growth in my data and log files.

CREATE EVENT SESSION [DB Size Tracking] ON SERVER
ADD EVENT sqlserver.database_file_size_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\DB Size Tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

You'll need to shrink your log on the TEST server, then let the jobs run so that it grows again.  This extended event will then log the SQL text of what causes the autogrowth event.


Generate `insert` statements for each entry in a table

In SQL Developer, use the /*insert*/ "hint".

select /*insert*/ * from t1;

REM INSERTING into T1
SET DEFINE OFF;
Insert into T1 (C1,C2) values (1,2);
Insert into T1 (C1,C2) values (2,3);
Insert into T1 (C1,C2) values (3,4);

Another useful "hint":

select /*csv*/ * from t1;

"C1","C2"
1,2
2,3
3,4

Starting with version 4.1, the "hint" the below also works:

set sqlformat insert
select * from t1;

The output can be spooled as well:

set sqlformat insert
spool C:\Users\balaz\Desktop\insert.sql
select * from t1;
spool off


How to rank an aggregation in each newly formed group?

Use grouping to get the count per country, and a window function to get the rank:

WITH t1 AS (
  SELECT CASE
         WHEN Country IN ('France', 'Germany')     THEN 'Europe'
         WHEN Country IN ('China', 'Japan')        THEN 'Asia'
         WHEN Country IN ('Egypt', 'South Africa') THEN 'Africa'
         END AS Region,
         GenreName,
         COUNT(*) AS Views
  FROM Customer
  JOIN Invoice USING (CustomerID)
  JOIN Movie USING (MovieID)
  JOIN Genre USING (GenreID)
  GROUP BY Region, GenreName
),
t2 AS (
  SELECT Region,
         GenreName,
         Views,
         row_number() OVER (PARTITION BY Region ORDER BY Views DESC) AS Rank
  FROM t1
)
SELECT Region, GenreName
FROM t2
WHERE Rank <= 2
ORDER BY Region, Rank;


Encrypted stored procs and views can be decrypted pretty easily:

http://www.mssqltips.com/tip.asp?tip=1046

That'll give you the unencrypted text of the stored procedures and views.  Just change the CREATE statement to be an ALTER, and it'll overwrite the encrypted versions with decrypted ones.

Don't do this in production - do it in a restored backup of the database.  That way if it's a third party vendor app, you'll still be supported in production.


MySQL Collation utf8_unicode differences

Unicode collation names may include a version number to indicate the version of the Unicode Collation Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the name use the version-4.0.0 UCA weight keys. A collation name such as utf8_unicode_520_ci is based on UCA 5.2.0 weight keys.

See https://dev.mysql.com/doc/refman/5.6/en/charset-collation-names.html.