MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

I'm going out on a limb here, but did you do an 'EXPLAIN' on this query?

If not, run "EXPLAIN" to see what the execution plan is. This will give you hints on optimization or indexing.

E.g. SOMETHING LIKE

EXPLAIN SELECT a.field1,b.field1 FROM tableA a LEFT JOIN tableB b ON b.a_id = a.id;

The result will be a nicely formatted report.

You may find there's unnecessary complexity in the query that can be pushed to the application or just factored out.


Need help in mysql database backup on Windows except mysqldump?

You may give it a try using percona backup alpha version for Windows - Download_Link
.

Below are the steps after installation in Windows Bash.

C:\Users\lorainne>bash

C:\Users\lorainne$ xtrabackup –user=root –password=secret -host=127.0.0.1 –port=3306 –backup –target-dir=\test

I haven't tested this but this seems to be a working command, ensure that you have https://msdn.microsoft.com/en-us/commandline/wsl/release_notes - Build 14332 fixed to have prctl system call supported on Windows.


Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem?

You really need to generate an explain plan. Since your query has an OR there may be situations where the query is being short circuited based on data not existing, hence the performance is not consistent. Also, don't join everything together, subqueries with low cardinality put at the top in a WITH subquery_name clause, and join similar tables together, then join them with other tables.


How to recover deleted records with minimal downtime?

I believe DB2 Recovery Expert is the best option.

In the case of a dropped object, or recovery to a specific point in time, you should use the "Recovery" option.  The "undo SQL" option performs Log Analysis, which only affects data, not structure.

Please check IBM's official webpage : DB2 Recovery Expert


I'd think twice about carte-blanche modifying data the way you are proposing unless you are doing this one-time as part of a data-purity effort.

I'd write code to do the modification using non-dynamic T-SQL first, then convert that into dynamic T-SQL, and finally add it to a stored procedure for easy re-use.

For instance, we'll create a table in tempdb, then update a column that has null values:

USE tempdb;

DROP TABLE dbo.test_table;
CREATE TABLE dbo.test_table (i varchar(3) null);
INSERT INTO dbo.test_table(i) VALUES (null)

UPDATE [dbo].[test_table]
SET [i] = ''
WHERE [i] IS NULL;

Next, we'll add this code to a framework that uses dynamic T-SQL so we don't need to write individual statements for every column we want to update:

DECLARE @cmd nvarchar(max);
DECLARE @schema sysname;
DECLARE @table sysname;
DECLARE @column sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
    , t.name
    , c.name
FROM sys.schemas s
    INNER JOIN sys.tables t ON s.schema_id = t.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.is_nullable = 1
    AND ty.name IN (
          'varchar'
        , 'char'
        , 'nvarchar'
        , 'nchar'
    )
    AND s.name = 'dbo'
    AND t.name = 'test_table'
ORDER BY s.name
    , t.name
    , c.name;

OPEN cur;
FETCH NEXT FROM cur INTO @schema, @table, @column;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = ''''
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
END
CLOSE cur;
DEALLOCATE cur;

It uses a cursor to iterate over all the varchar, nvarchar, char, and nchar columns in the given table where the column value is null.

Next, we'll create a stored procedure to run the code.  The stored proc has a paramter, @debug, which allows us to see the UPDATE statement, or to actually run the UPDATE statement.

IF OBJECT_ID(N'dbo.RemoveNulls', N'P') IS NOT NULL
DROP PROCEDURE dbo.RemoveNulls;
GO
CREATE PROCEDURE dbo.RemoveNulls
(
    @schema_to_change sysname
    , @table_to_change sysname
    , @debug bit = 1
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @schema sysname;
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT s.name
        , t.name
        , c.name
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE c.is_nullable = 1
        AND ty.name IN (
              'varchar'
            , 'char'
            , 'nvarchar'
            , 'nchar'
        )
        AND s.name = @schema_to_change
        AND t.name = @table_to_change
    ORDER BY s.name
        , t.name
        , c.name;

    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = ''''
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
        PRINT @cmd;
        IF @debug = 0
        BEGIN
            EXEC sys.sp_executesql @cmd;
        END
        FETCH NEXT FROM cur INTO @schema, @table, @column;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

Run the stored procedure like this, which won't actually modify data since we've left the @debug parameter set to its default value.:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table';

If we want to actually modify the data, run the stored procedure like this:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table'
    , @debug = 0;


Query to get counts of values per column

You can get the first part of that done easily like this,

SELECT FORMAT(
        E'SELECT %s\nFROM %I.%I.%I;' -- query template
        , string_agg(  -- generate the select list for query template
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog, -- not strictly required, but future safe
        table_schema,
        table_name
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name;

This will return a query like this,

SELECT count(DISTINCT column1) AS column1,
        count(DISTINCT column2) AS column2,
        count(DISTINCT column3) AS column3,
        count(DISTINCT column4) AS column4
FROM ecarroll.public.foo;

Which is pretty much what you want, except you need to pivot it.

 column1 | column2 | column3 | column4
---------+---------+---------+---------
       2 |       3 |       3 |       0

To do that pivot, we can use unnest(ARRAY[cols]) AS col_name, so we essentially have to generate

dynamic SQL to do the count()
wrap that with more dynamic sql to do the pivot.

Like this,

SELECT FORMAT(
        $$
        SELECT ordinality AS column_number, distinct_values -- the col#, and count
        FROM (
                SELECT %s      -- This was the query we
                FROM %I.%I.%I  -- used previously
        ) AS t
        CROSS JOIN unnest(ARRAY[%s]) WITH ORDINALITY -- Here we use unnest(array)
                AS distinct_values;                  -- to pivot the table
        $$,
        string_agg(
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog,
        table_schema,
        table_name,
        string_agg(column_name, ', ')
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name;

That returns a query like this..

    SELECT ordinality AS column_number, distinct_values
    FROM (
            SELECT count(DISTINCT column1) AS column1,
    count(DISTINCT column2) AS column2,
    count(DISTINCT column3) AS column3,
    count(DISTINCT column4) AS column4
            FROM ecarroll.public.foo
    ) AS t
    CROSS JOIN unnest(ARRAY[column1, column2, column3, column4]) WITH ORDINALITY
            AS distinct_values;

And you can just run \gexec and you'll get,

 column_number | distinct_values
---------------+-----------------
             1 |               2
             2 |               3
             3 |               3
             4 |               0


Extending the original answer by Andriy M we can uniqify the strings at the insert phase with a dummy character (in my case I used ~), and remove that character later.

In this case I chose to use REPLICATE passing in the dummy character '~' and the ROW_NUMBER function for the number of instances to replicate. This should ensure that all strings are unique.

dbFiddle

Full code

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');
GO

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata
AS
(
    SELECT id,splitname
    FROM
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter
        FROM   @Inter F
    )F1
    CROSS apply
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
)
INSERT INTO dbo.NameParts
SELECT id,[splitdata].[splitName] + REPLICATE('~',(ROW_NUMBER() OVER (PARTITION BY [splitdata].[id] ORDER BY [splitdata].[splitName] ASC))
FROM splitdata;
GO

;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  REPLACE(FullNameVariation,'~','')
FROM
  permutations
WHERE 1=1 AND
  Level = 1
ORDER BY
  ID,
  FullNameVariation
OPTION (MAXRECURSION 5100);

DROP TABLE [dbo].[tbltest]
DROP TABLE [dbo].[NameParts]


Linked Server - Map Windows Group to remote login

Further reading here (no affiliation): https://searchsqlserver.techtarget.com/tip/Managing-linked-server-security-in-SQL-Server

A linked server offers a few methods for connecting to the remote system.  Since MySQL as a remote system does not understand windows authentication or participate in Kerberos you are limited to the following:

Login mapping - as you said, each user in the windows group would need to be added to the linked server security tab, with a login on the MySQL side.  There is no reason that they cannot "share" the same login on the MySQL side, but each user would need to be added to the linked server on the SQL Side.
Be Made Using this security context - You can provide a single username and password here, such that every login on SQL Server would use it when using the Linked Server.

The only one that offers fine grained security control is the Login Mapping method.   Otherwise, any user who has access to SQL would have the access to MySQL that is granted by the Linked Server.


In the case of backup software, you can't use anybody's bak file interchangeably. SQL Server's backup file format is proprietary to SQL Server. It doesn't work for other apps.

You can also name your backups anything you want during the backup process - you could use the .docx extension, for example, but that doesn't mean they're readable in Microsoft Word.

(Some folks default their differential backups to .diff, and log backups to .log - but other folks just name 'em all .bak. There's no real standard out there, and changing the extension doesn't affect the contents.)


SQL Server - Do automatically created column statistics get dropped automatically after time?

SQL Server, under no version or circumstance, will automatically remove statistics objects, unless the underlying object (table/view/index) is removed.

Use DROP STATISTICS ... T-SQL or use the SSMS GUI to drop unnecessary statistics, but only do that if you are certain they are not needed.


Dynamically change schema to accommodate custom fields!

I did a presentation about the various solutions to support custom fields:

Extensible Data Modeling with MySQL.

The most straightforward option for greatest compatibility with SQL, including constraints and data types, is just to add columns as needed. But I would caution to not let users do this, instead let them submit a request and the DBA can perform the operation. A good tool to reduce impact is pt-online-schema-change.

Other solutions include:

EAV (but before you try it please read the article Bad CaRMa, and my blog post EAV FAIL)
Class table inheritance
Serialized LOB with Inverted Indexes
NoSQL document-store solutions


a query that shows the load request received through GPRS by salesman

Unless I am missing something in your explanation, it sounds like you just want to do something like the following.

If your sample data is:

CREATE TABLE WarehouseTransaction([WarehouseID] int, [TransactionID] int, [TransactionDate] datetime, [RequestedBy] int, [TransactionTypeID] int);

INSERT INTO WarehouseTransaction ([WarehouseID], [TransactionID], [TransactionDate], [RequestedBy], [TransactionTypeID])
VALUES (27, 36101398, '2012-04-18 14:49:34', 3, 1),
    (29, 1234578, getdate(), 3, 1);

CREATE TABLE Warehousedetail ([WarehouseID] int, [PackID] int, [Quantity] numeric(20, 5)) ;

INSERT INTO Warehousedetail ([WarehouseID], [PackID], [Quantity])
VALUES
    (27, 4, 25.000000000),
    (27, 26, 175.000000000),
    (27, 30, 25.000000000),
    (29, 4, 25.000000000),
    (29, 26, 175.000000000),
    (29, 30, 25.000000000) ;

Then the query will be similar to this:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= Cast(getdate() as date)
  and t.TransactionDate <= DateAdd(d, 1, Cast(getdate() as date))

See SQL Fiddle with Demo

Which generates the result:

| WAREHOUSEID | TRANSACTIONID |                 TRANSACTIONDATE | REQUESTEDBY | TRANSACTIONTYPEID | PACKID | QUANTITY |
-----------------------------------------------------------------------------------------------------------------------
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |      4 |       25 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     26 |      175 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     30 |       25 |

You did not specify what version of SQL Server but the Cast(getdate() as date) will work in SQL Server 2008+, if you are not in SQL Server 2008, then you can use the following:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), 0)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

Edit, if you want your transactions to contain yesterday and today in SQL Server 2005 you can use:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), -1)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

See SQL Fiddle with Demo


My availability group doesn't work correctly

Can you confirm you have the correct permissions on the endpoint?

USE master;
GO
SELECT SUSER_NAME(principal_id) AS endpoint_owner
    ,NAME AS endpoint_name
FROM sys.database_mirroring_endpoints;
GO

-- change the endpoint owner
USE master;
GO
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;
GO

-- grant connect
GRANT CONNECT
    ON ENDPOINT::Hadr_endpoint
    TO [yourdomain\username]; -- account used for the SQL Service


Innodb mysql 5.7 cluster wont join after reboot

Fixed it by adding "group_replication_local_address" value in my.cnf cluster config file.


You need to query for nodetype 3 to get the element text. I have changed your xpath expression slightly so it just returns the node text

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlDocument
Select   [text]
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag/text()',3)


Overhead associated with DBCC CHECKTABLE?

The code behind CHECKTABLE is indeed likely the vast amount of time spent by CHECDB, as mentioned by @Mo64. I.e., the "sum of CHECKTABLE" should roughly be the same as a CHECKDB, one can imagine.
However, by default CHECKTABLE uses a database snapshot. Not a "table snapshot", since there is no such concept. I.e., you can check one table and if modification occurs on some other table at the same time, the original for that data need to be saved in the snapshot file first. Here is one aspect where the "sum of CHECKTABLE" can exceed one CHECKDB.
Anyhow, your question is about overhead. Yes, pretty much the same (or rather, it won't be (much) less for CHECKTABLE. You have the database snapshot and copy-on-write for the whole database (although the snapshot would live for a shorted time when you check only one table). And SQL server should need space in tempdb as well.
Above is from theoretical reasoning, I should add.


Copy data from one table into other on some condition

ALTER TABLE locality ADD COLUMN city_id ...;  -- add new column
UPDATE locality
  JOIN city  ON city.city_name = locality.city
   SET locality.city_id = city.city_id;  -- set new column
ALTER TABLE locality DROP COLUMN city;   -- cleanup by removing old column


Do an inner join between sys.columns and sys.tables.  The latter table is the catalog view for user tables, so joining on object_id should give you the corresponding columns:

select c.*
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id;

Taking this a step further on what seems to be your end result, if you want to get all nullable columns in user tables, this quick query should work:

select
    object_name =
        quotename(s.name) +
        '.' +
        quotename(t.name),
    column_name = quotename(c.name),
    c.is_nullable
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
where c.is_nullable = 1;


How can I use a 1-argument function in an "ANY"/"ALL" query?

You can use the first function with ANY but you have to have a valid syntax as you found out (<expression> <**operator**> ANY ...). So the following would work (the "trick" piece is to use unnest() whild unfolds the array into rows):

SELECT true = ANY (select myfun(x) from unnest(ARRAY['test', 'world']) as x) ;

or the more compact:

SELECT true = ANY (select myfun(unnest(ARRAY['tt', 'foo']))) ;


Identify the type of a SQL Server backup

Restore Headeronly From Disk = '...'

There's only one SQL Server backup format. If this doesn't return anything useful then it's not a SQL Server backup file and may be something else (I can only imagine what; perhaps you'd have to open it in a text editor and see what the first few characters are).

It will return a BackupType column:

1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial

That would help you determine the order in which you need to restore (usually database with norecovery, then differential database with norecovery, then transaction log, then with recovery).

There are other methods to identify the file using PowerShell and SMO but this is the quickest.


Obvious reason Postgres Users can't read a table?

Connect to the respective database by \c DATABASE_NAME
It should have the Connect permission: GRANT CONNECT ON DATABASE DATABASE_NAME TO USER_NAME;
Grant the permission to use the schema GRANT USAGE ON SCHEMA public TO USER_NAME;
Grant permission to select on all the tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER_NAME;


Get list of databases accessed by a particular SQL Server login

You coud use Extended Events for this and stream the events in memory first and then to a database table, only when needed. I have a blog post on that topic that you can find here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/ Skip to the "Extended Events to the rescue" section.

Basically, what you do is you set up a trace for locks acquired and then count the occurrences of the locks, first in memory and then dump to a table. The example in that blog post is for table objects, but you could narrow it down for databases:

CREATE EVENT SESSION [audit_database_usage] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    ACTION(sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
            [package0].[greater_than_equal_uint64]([database_id], (5)) -- user databases only
        AND [package0].[equal_uint64]([associated_object_id], (0))     -- filter out objects
        AND [package0].[not_equal_uint64]([database_id], (32767))      -- filter out resourcedb
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))   -- only user sessions
        AND [resource_type] = (2)                                      -- DATABASE resources
        )
    )
    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 = OFF
            )
GO

From this session it's easy to capture database access, without inspecting specifically what is being accessed inside the database, which reduces enormously the number of events captured. If you want to dig deeper and go to the object level, you can use the session in my blog post, which will produce a far greater number of events.


I've just uncovered some interesting information from the RocksDB FAQ. (RocksDB is a K-V store.)

Here are some relevant extracts.

  Q: What are column families used for?

  A: The most common reasons of using column families: (1) use different
  compaction setting, comparators, compression types, merge operators,
  or compaction filters in different parts of data; (2) drop a column
  family to delete its data; (3) one column family to store metadata and
  another one to store the data.

  Q: What's the difference between storing data in multiple column
  family and in multiple rocksdb database?

  A: The main differences will be backup, atomic writes and performance
  of writes. The advantage of using multiple databases: database is the
  unit of backup or checkpoint. It's easier to copy a database to
  another host than a column family. Advantages of using multiple column
  families: (1) write batches are atomic across multiple column families
  on one database. You can't achieve this using multiple RocksDB
  databases. (2) If you issue sync writes to WAL, too many databases may
  hurt the performance.

  Q: I have different key spaces. Should I separate them by prefixes, or
  use different column families?

  A: If each key space is reasonably large, it's a good idea to put them
  in different column families. If it can be small, then you should
  consider to pack multiple key spaces into one column family, to avoid
  the trouble of maintaining too many column families.


One part of your problem is that execute() tries to interpret the LIKE expressions ('%PE%') as a placeholder.  You have to double all the percent signs, so that the following query in pure SQL (with a pseudocode placeholder)

SELECT ? WHERE 'abc' LIKE '%bc'

turns into

cur.execute("SELECT %s WHERE 'abc' LIKE '%%bc'", ['%s'])
cur.fetchall()
==> [('%s',)]

Since the column code is also from the source table, it needs to be "fed" from the parameters, too.

for row in curr_msql:
    curr_psql.execute('''
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %s, %s,
                CASE
                    WHEN %s LIKE %s OR %s LIKE %s
                    THEN TRUE
                    ELSE FALSE
                END)
    ''', (row['code'], row['subjectname'],
          row['code'], '%PE%',
          row['code'], '%NSTP%'))

and much cleaner, using named parameters:

for row in curr_msql:
    param = dict(code = row['code'],
                 subjectname = row['subjectname'],
                 str1 = '%PE%',
                 str2 = '%NSTP%')
    curr_psql.execute('''
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %(code)s, %(subjectname)s,
                CASE
                    WHEN %(code)s LIKE %(str1)s OR %(code)s LIKE %(str2)s
                    THEN TRUE
                    ELSE FALSE
                END)
    ''', param)

Link to psycopg2: Basic module usage for further reading.


Fool that I am... the server was restarted this morning. I realized this as I was finishing the question. The buffer pool won't fill up until the database actually needs to use that much data. Indeed, if I re-run the buffer pool query I can see the "Free buffers" number slowly decrease.

I could run some queries to force tables into memory, but given I know I don't have enough memory to go around (hope to address this soon) I should just wait and let my users tell the DB what data is more important.


Proper name for reference tables in OLTP

A reference table generally loosely fits the definition of a dimension table or sometimes you'll hear the less formal term enum table for reference tables with a unique set of values. I think it's generally fair to use all three terms interchangeably.
Generally when your schema follows the Star Schema or Snowflake Schema, you reference the tables by either the terms fact tables for your measured quantitative values and dimension tables for your qualitative values.


Due to the fact that both stored procedures issue

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

before attempting to execute any other code, the first stored procedure to successfully run that update will get an exclusive lock on dbo.DeadlockTestMutex and the other process will wait until that lock is released.  This allows each stored procedure to run to full completion without competition from the other stored procedure.


How to insert values to one table from the table stored in another database in postgresql?

Using a Foreign Data Wrapper is generally preferable to using dblink.  Once you set up the link and import the table definition, then you can write the query just as if the table were local.

Your English description of the problem doesn't make any sense to me, so I can't tell you if the specific query you wrote does what you want.


Processing occasionally fails with "Unexpected end of file has occurred."

I eventually managed to track this down to low memory. I was under the mistaken impression that 32-bit Analysis Services could use AWE memory, but alas it does not. I changed the processing strategy from full processing, to incremental processing of only the appropriate partitions. That seems to have reduced the memory requirements, and resolved the issue (hasn't failed in quite some time now).


BCNF decomposition does not prevent insert anomaly?

Edited

I think that the relation is in BCNF, but it is known that this fact does not always solve all the anomalies. For this reason other normal forms, like 4NF, 5NF, etc., for instance those based on elementary keys (Elementary Key Normal Form, Key-Complete Normal Form, etc.) have been defined.

I am not sure of what other kind of dependencies are present in your schema, but I want to show a decomposition, as pointed in a comment below by @philipxy (correcting my previous version), to eliminate redundancies and anomalies from the schema.

polls (poll_id, question)
   poll_id → question
options_of_polls (poll_id, option)
   no non-trivial functional dependencies, so the key is (poll_id, option)
votes (partecipant_id, poll_id, option, stars)
   partecipant_id, poll_id, option → stars


How to query efficiently from Postgres to select special words?

An easy approach would be to consider the array of letters corresponding to each word, and search inside that with the @> (contains) array operator. This works independantly of the letters positions as shown in the example from the manual, i.e. ARRAY[1,4,3] @> ARRAY[3,1] is true.

This array can be easily obtained with regexp_split_to_array(name, '').
[EDIT: per @Erwin's answer, string_to_array(name, NULL) is faster so  better use that. It's a drop-in replacement in the rest of the answer]

Here's a demo that first materializes the array as a column in a test table containing a mix of english and french words (~511000 rows, average length=13 characters), and then a second test table without adding the array as a column.

=> CREATE TABLE tstword AS
    SELECT word_id as id,
    wordtext as name,
    regexp_split_to_array(wordtext, '') as arr FROM words;

To find a relatively large number of words:

=> select count(*) from tstword where arr @> array['s','m','e'];
 count
-------
 42268
(1 row)

Time: 268.809 ms

This does a sequential scan as shown by EXPLAIN ANALYZE:

 explain analyze select name from tstword where arr @> array['s','m','e'];
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on tstword  (cost=0.00..17554.46 rows=21256 width=14) (actual time=0.020..268.525 rows=42268 loops=1)
   Filter: (arr @> '{s,m,e}'::text[])
   Rows Removed by Filter: 468729
 Total runtime: 269.927 ms
(4 rows)

Time: 270.414 ms

But we can index the array with a GIN index:

CREATE INDEX idx_tst on tstword using gin(arr);

And then it's quite faster:

explain analyze select name from tstword where arr @> array['s','m','e'];
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword  (cost=252.74..11815.73 rows=21256 width=14) (actual time=46.378..60.203 rows=42268 loops=1)
   Recheck Cond: (arr @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst  (cost=0.00..247.42 rows=21256 width=0) (actual time=45.202..45.202 rows=42268 loops=1)
         Index Cond: (arr @> '{s,m,e}'::text[])
 Total runtime: 61.677 ms
(5 rows)

Time: 70.185 ms

We might even avoid materializing the array as a column by directly indexing the expression, since postgres supports functional indexes.

create table tstword2 as select word_id as id,wordtext as name from words;
create index idx_tst2 on tstword2  using gin(regexp_split_to_array(name, ''));

Then the search has to be made with the exact same expression, and the index gets used:

 explain analyze select name from tstword2 where regexp_split_to_array(name, '') @> array['s','m','e'];
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword2  (cost=40.00..44.02 rows=1 width=14) (actual time=39.390..48.435 rows=42268 loops=1)
   Recheck Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst2  (cost=0.00..40.00 rows=1 width=0) (actual time=39.053..39.053 rows=42268 loops=1)
         Index Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
 Total runtime: 49.748 ms
(5 rows)

Time: 50.193 ms

See GiST and GIN Index Types in the manual for caveats on these types of indexes.


MySQL - Set trigger BEFORE INSERT for new ID is safe?

Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.


Problem compiling view when it is referencing a table in an other view: insufficient privileges

Even though you are using SYS (which you really shouldn't be), the view is stored in a SCHEMA1, as I will refer to it.  SCHEMA1 is trying to select from a table, via the view, in SCHEMA2.  Therefore, you need to grant SELECT access to SCHEMA2.TABLE to SCHEMA1.

And don't run things as SYS. :)


Accessing impact of deleting ibtmp1 file in MYSQL

That file comes from MySQL 5.7, not 5.6. You can check by connecting to MySQL and running:

mysql> SELECT VERSION();

Shrinking ibtmp1 would require shutting down MySQL. You cannot just the delete the file because there will be a open file handle on that file coming from mysqld.exe.

First, log into MySQL and run:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, shutdown MySQL, delet the file and start mysql back up:

C:\> net stop mysql
C:\> del ibtmp1
C:\> net start mysql

If your ibtmp1 grew that much, then you have been running some bad queries that create large temp tables.


Errors starting mysql after moving to a new directory

After a bit of trial and error, was able to get it to work using this approach plus modifying the log_error variable within the mysqld.cnf file to point to the new location as well.

Adding a line to deal with AppArmor was required (it's included in the linked tutorial), but otherwise works without issue.


Find all items where the 2 latest statuses meet condition

CTE add a row number in descending order, that means that row (1) must be PENDING and row (2) must be EXPIRED.

Then you can use 2 EXISTS subqueries in the WHERE clause to ensure both conditions are true.

with ct as
(
select
    id, itemfk, dt, status,
    row_number() over (partition by itemfk order by itemfk, dt desc) rn
from
    t
order by
    itemfk, dt
)
select
    t1.itemfk
from
    ct t1
where exists (select 1
              from ct
              where itemfk = t1.itemfk
                    and rn = 1 and status = 'pending')
      and
      exists (select 1
              from ct
              where itemfk = t1.itemfk
                    and rn = 2 and status = 'expired')
group by t1.itemfk;

db<>fiddle here


You should use DECIMAL (3,1) for temperature in (Celsius)


A single SQL Server instance knows how to manage its memory, CPU and IO in the most optimal way. Two (or more) SQL Server instances cannot cooperate to coordinate usage of shared resources. On this grounds, a single instance is more performant than more instances. If you split into 2 (or more) instances, then it would be required to partition the resources between them (ideally via virtualization) so the instances do not start stepping on each other toes.

To give a concrete example, consider a query memory grant. This is a reservation against the buffer pool. Multiple queries can launch into execution, as long as their total grant does not exceed the allowed memory. A memory grant is not allocated immediately, so the BP is not evicted, but instead used incrementally, and most often not all the reservation is used (reservation is computed for worst case). On many instances, a single instance can hit its cap of the total memory grant and queue queries although the total system has the capability to run more. On a single instance, the total capability has to be hit, so a tenant can survive a spike better.

The biggest drawback and danger of instance consolidation is the noisy neighbor syndrome. If one Tennant takes on a constant basis more resources from the common pool, it will create a poor experience for other tenants as their workload can be starved of required resources and the system appear slow for everybody. On single instances you would use Resource Governor to control fairness between tenants, but the control is not complete. Using virtualization for isolation gives much better control. Using multiple instances on same physical OS is not ideal, imho it gives worse control than single instance Resource Governance and does not provide anywhere near the isolation offered by virtualization. My opinion.

My advice is therefore: either split at VM level, or consolidate to single instance (per physical server, obviously). Also, consider Azure SQL DBs instead, depending on some factors it may be better suited for your scenario.


How To Fix High DTU Usage on Specific Query

Not having the full table definition, I'm going to take a stab at what it might be. Based on the screenshot in your question, the table could look something like this:

CREATE TABLE dbo.TaskSchedulerItem (
    TaskSchedulerItemID int IDENTITY(1,1),
    TaskSchedulerID int,
    ItemStatus varchar(10),
    DateLastUpdated datetime2(0),
    CONSTRAINT PK_TaskSchedulerItem PRIMARY KEY CLUSTERED (TaskSchedulerItemID)
);

And just for funsies, lets throw some random sample data in there:

INSERT INTO dbo.TaskSchedulerItem (ItemStatus, TaskSchedulerID, DateLastUpdated)
SELECT TOP 10 'Pending', column_id, GETDATE()
FROM sys.columns
UNION ALL
SELECT TOP 10000 'Complete', column_id, GETDATE()-1
FROM sys.columns;

And now we can run our query against that data:

DECLARE @CurrentDateTime datetime2(0)   = GETDATE(),
        @TaskSchedulerID int            = 4,
        @TaskSchedulerItemID int;

SELECT TOP 1 @TaskSchedulerItemID = TaskSchedulerItemID
FROM dbo.TaskSchedulerItem
WHERE (
    ItemStatus IN ('Pending','Failed')
    OR
    ItemStatus = 'IN PROGRESS' AND DateLastUpdated < DATEADD(MINUTE, -1, @CurrentDateTime)
)
AND TaskSchedulerID = @TaskSchedulerID
ORDER BY DateLastUpdated;

SELECT @TaskSchedulerItemID;

And we can see that this query does a Clustered Index Scan:

I don't know for sure that your query is doing a Clustered Index scan, but that is going to be my guess. We're only looking for one row--so ideally, we would be able to do a seek to that one row (or, at least fewer rows). The ORed criteria makes that a little bit trickier, so having a single index that always gets us the exact row is a bit challenging. Instead, we can just try to get close.

We can create an index on TaskSchedulerID (we always filter on that!), and DateLastUpdated (we always sort on that!), and ItemStatus (we filter on that, but in the OR, so it's a little trickier for SQL to search on that):

CREATE INDEX ix_TaskSchedulerId_ItemStatus
    ON dbo.TaskSchedulerItem(TaskSchedulerID, DateLastUpdated, ItemStatus);

Huzzah! It's doing a seek!

That should make the query pretty darned fast... but what's that little warning symbol?

That's the "get close" part I was talking about. In my random sampling, the index seek was able to seek to 280 rows, then did some residual I/O to find the one row it needed that fit the critera. Based on the index I created, it is going to seek to the supplied @TaskSchedulerID, then start running along the DateLastUpdated column (remember, that's the ORDER BY on the query, then do "residual I/O" to figure out that ItemStatus criteria.

Depending on your data, that might be "good enough" to fix your performance issues. Or you might need to be a bit more clever. Since I don't know your actual schema, indexing, or data distribution, I can't hazard a better guess.

But really, you just need an index that will make your query go a lot faster, so that you use fewer DTUs.


getting MySQL error 1064 but very (absurdly) simple query in MySQLWorkbench

\G is a delimiter exclusive to the mysql client

I personally do not use MySQL Workbench, but many others have done the following

Mar 12, 2010 : How to get a table creation script in MySQL Workbench?
Mar 28, 2012 : \G like vertical output in MySQL Workbench for show engine innodb status

But here is the Blog for MySQL Workbench Team ( MySQL Workbench: Vertical Query Output )

Give Them a Try !!!


I'm not understanding the concept of login/user on Azure

Simple explanation :

A login is an access to a server. It does not necessarily mean access to a database.

When you create a user from a login in a database and grant access (explicit or using a role), the login is able to access a given database/s.

What you are doing is incorrect since you are inside a database and then trying to change the password for a login.

YOu should be in master database to change a login's password - provided you have rights to do it.


The code that you have now is guaranteed to do more work on each successive loop. I'll assume that the target table is empty at the start, so the SELECT part of the query only needs to return 10k rows in order to insert 10k rows into plan2. For the next loop the first 10k rows returned by the SELECT query are already in the target table so it'll need to return 20k rows to insert 10k additional rows into the target table. You expect to insert a total of 20 million rows. With a batch size of 10k rows you'll need to do 2000 batches. Each batch will on average need to read 10 million rows from the SELECT query, so for your process to complete you'll need the SELECT part of the INSERT to generate 20 billion rows. That's a lot of work and that work is unavoidable with the looping method that you're using here.

The other thing that immediately stands out is the use of the local variable @BatchSize without a RECOMPILE hint. You used that in a TOP statement and by default SQL server will assume that you want just 100 rows returned by the query. The query optimizer doesn't know what the value of the local variable is without a RECOMPILE or some other hint. The query optimizer can pick different plans based on the presence of a TOP operator, and with an estimated value of 100 it might pick a plan that's very inefficient for what you're trying to do. This is where examining and posting the estimated or actual query plan can really help.

Let's suppose that the query optimizer chooses to do a nested loop join against the plan2 table in the NOT EXISTS. That part of the query will do more work on each successive loop, but it's even worse than before. For the Nth loop you'll need to scan 10000 * (N - 1) rows 10000 * N times. If you have 270k rows already inserted into the table that means that N has a value of 28, so you could be scanning 10000 * 27 * 10000 * 28 = 75600000000 total rows just to insert the next 10k rows. That could easily take 7 hours to complete. You may be hitting this issue or you may be running into a completely different one. Getting rid of the @BatchSize variable or using a hint may help.

My approach to batched inserts is to avoid looping on the SELECT part if possible. Are you able to drop all nonclustered indexes on the target table before the insert? Is there a database with a recovery model of SIMPLE that you can write a temporary copy of the data to? Tempdb can work if there's enough space there. You may be able to take advantage of minimal logging to skip the looping. Probably all that's needed is the addition of a TABLOCK hint. If your current database has the right recovery model you may just be able to do one insert of 20 million rows. If not, you may be able to insert into another database and to loop over a temporary copy of that data to insert into your target table. The important thing is that if you need to loop make it so that each loop takes appropriately the same amount of time. You don't want the amount of work per loop to increase linearly or quadratically like it currently is.

If doing a single insert isn't an option you should change your style of looping. The goal is to avoid processing the same row more than once from the source tables. You also want to insert in clustered order of your target table if possible. I don't know your data model so I can't give very specific advice, but does the plan1 table have a primary key? Is there some kind of relationship between the number of rows in the plan1 table and the number of rows that you need to insert into plan2? As an example, you can try writing code that processes the plan1 table in 10k row chunks. With the right indexes that can be a relatively efficient query. I have a blog post that goes over a bunch of different methods of looping. The examples in the post just read data from a single table but you could adapt them to the SELECT query that you have. Just to stress this point again, you're looking for a strategy that does a constant amount of work per loop.

if you do need to keep the NOT EXISTS for some reason the following code is much more typical and I think that it would accomplish what you need:

WHERE NOT EXISTS (
    SELECT 1
    FROM plan2
    WHERE dr1.link = plan2.l_dr_plan1
    AND ad1.link = plan2.l_address
    AND dr.link = plan2.l_provider
)

It's not clear to me that the problem resides with the ltrim or rtrim, but it's always good to remove functions like that when you can.


"ORA-12899: value too large for column "V_UUID" (actual: 36, maximum: 4000)" when used to generate a Virtual column

After some experimenting I was able to get it to work by casting the result of the function call:

CREATE TABLE example_table
(
  uuid RAW(16)    NOT NULL PRIMARY KEY,
  v_uuid CHAR(36) GENERATED ALWAYS AS (CAST(UUID_AS_HEX(uuid) AS CHAR(36))) VIRTUAL
);


getting error while installing memcached_function

i found the solution for the previous case

the DEPS ERROR mainly because of not setting the path

while installing the libevent,memcached,libmemcached i gave the prefix with /usr/local path....in that path all the library files are created
and while installing memcached function we need to set the environment variable to the path as shown below

export PKG_CONFIG_PATH=/usr/local
export DEPS_CFLAGS=/usr/local
export DEPS_LIBS=/usr/local/lib

later its succesfully configured and working

regards
Deepak M


SQL Server: Mixed mode security: Can a non AD user (a local user, remote client) have a trusted connection to SQL?

I now have a situation of a web app that can no longer run on as a domain user (long story).

The web server can't run under the account or the application context can't run under the account? For example, the web server is sitting in a DMZ?

This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

This is a problem with how the password is being stored, not a problem with SQL Server TBH. However, you can overcome this by encrypting the password at rest and decrypting on demand then freeing the memory - for example.
The password is not sent over the wire in plain text, this encryption when connecting to SQL Server through SQL Authentication is completed for you automatically.

Is it possible to ?

It is not possible to connect to SQL Server with a username/password combo and setting the trusted connection setting. This is because trusted connection doesn't mean "encrypted and secure connection" it actually means "use windows tokens instead of a username and password". It doesn't mean the connection isn't trusted it just means use my token which will either end up being protected through ntlm or Kerberos.

Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?

There is no such thing as a "trusted user or login". Local windows accounts are only known to the local server, thus you could not use webserver\localuser unless the database server was also on the webserver. Since it is not, this is not possible. Since the local user is not in active directory, this will also not work. Thus, no, you cannot use that method.
Your best possible scenario for this is to use encryption of the connection string at rest and potentially even SSL connections (though that might be overboard and doesn't make the password kept in the registry and more or less secure, it just encrypts the traffic on the line).


That's called a Major Whoopsie.

Assuming you didn't use transactions around the delete you are stuck with scanning the log files and redoing all the INSERT and UPDATE queries. If you don't have a query log then it's manual entry time.

If it's any consolation the Government of Alaska had a similar issue a while ago, further compounded by the backup tapes not being readable. You've probably lost a morning, they lost the whole thing.


Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work:

  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.

With this correction, the rest of the question is moot. Row-versioning does not create a copy of the data until data is modified, and then the copy resides in tempdb. In other words, as you continue to modify the original index while OIB is running the row-versioning will have to preserve the pre-update image of the updated rows in tempdb.

@Shanky is right about SORT_IN_TEMPDB, but that refers to the index builder, a different stage, unrelated to the original 'snapshot', and an option that is independent of the 'online' nature of the OIB.

Obviously during the OIB you will slowly build up a copy of the data (the new index). This must be in the same location as the original index (including filegroups for partitions etc) as it has to be a valid replacement of the original index when the OIB is done.


This is an educated guess, so if anyone has something more authoritative I am happy to rescind it.

I've never used the SSMS UI to manage plan guides before, so I immediately checked a few servers where I know I have viable plan guides setup:

The red Xs indicate the plans are disabled, as one might expect.

When tracing SSMS as it populates this part of the UI, we can see it runs this query:

SELECT pg.name AS [Name]
, 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/PlanGuide[@Name=' + quotename(pg.name,'''') + ']' AS [Urn]
, pg.is_disabled AS [IsDisabled]
FROM sys.plan_guides AS pg
ORDER BY [Name] ASC

Which only includes basic data and the disabled flag - nothing else. My hunch is that the yellow exclamations are actually the default icons for plan guides.

This doesn't seem too far fetched since you're modifying execution plans and a similar icon is used whenever an execution plan might be impacted by something like a conversion or a plan guide:


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

An example, separation of duties

On a web server, the password for the service account is only known by one team. It isn't required to lie around in web.config or source control.

One team sets up the IIS App Pool (password is known here)
One team deploys code
One team manages the SQL Server Instance

If you want to pass an audit where PCI or SoX is involved, then you need to have this


Upgrade BI server to 2014 and stay on 2008r2 with the database engine server

Edit

If your setup involves your BI server being standalone and the report server database is being hosted on the BI server and not the production database then yes upgrade to 2014. You should consider looking at the new deployment methods with SSIS, very cool stuff.

If your setup involves:

your BI server only houses the Integration Services and Report Server binaries
your SSRS report database is being hosted on the production server
your IS packages are file system

Then the below would apply.

With SSRS that would be a supported setup if you installed SQL Server 2014 SSRS on your BI server. You can view the documentation here that shows the supported database instances to house the SSRS databases.

  SQL Server is used to host the report server databases. The SQL Server Database Engine instance can be a local or remote instance. The following are the supported versions of SQL Server Database Engine that can be used to host the report server databases:

SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005

SSIS 2014 is another beast though if you want to actually use the new features that come with it for management: SSISDB Catalog or Project Deployment (which requires the SSIDB Catalog). You would have to have a SQL Server 2014 database instance in order to configure all this. The only thing you would be able to do with this type of setup would be a file system deployment, anything else I think is going to require the database engine to be at the same major version.


Could not continue scan with NOLOCK due to data movement SQL Server 2008 R2

There's at least three possibilities:

SQL Server Management Studio was running a query that it shouldn't have
Something else was running a query it shouldn't have (like an SSMS add-in, which isn't likely based on the sounds of the question)
The engine was experiencing difficulties executing a normal SSMS query (that it shouldn't have)

I'd troubleshoot #1 by running SSMS from another machine, pointed at this particular laptop. If it works okay, then the laptop's SSMS is the problem - reinstall that.

If the other machine's SSMS gets the same error when connecting to the laptop's engine, then uninstall and reinstall the SQL Server engine on the affected laptop. (It's some type of corruption issue, and not worth the $500 MS support call to troubleshoot - if it was a production server, I'd answer otherwise.)


Field = Parameter OR Parameter IS NULL Pattern

They’re all excellent. Really. They all have the same impact of having two plans in the cache, which is what you want.

As you get more and more parameters, you will find the Dynamic SQL option is clearest, even though it looks scarier to beginners.

If this were a function I’d suggest avoiding multi-statement options, so that the QO could do its stuff more nicely.


If you enable the force encryption option and restart the SQL Server instance then all connections between the instance and the SQL Server will be encrypted via SSL. There is no need for a certificate or change to the client. The server tells the driver that it needs to switch to an encrypted session. Thus is kind of like when you connect to a website using http:// and the Web server tells your browser to switch and reconnect using https://. It all happens without the user needing to do anything.

There's no need for an appliance to do this. SQL Server has been doing this internally just fine since SQL 2000.

What SQL does isn't to block unencrypted sessions, it converts unencrypted sessions to encrypted then allows them to continue.


How to move data from Primary Data File to other data file without destroying replication

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
    ON dbo.Mytable(PKColumn)
    WITH (DROP_EXISTING = ON)
    ON NewFileGroup


Allocating 8GB memory to MySQL on a 64bit system

memlock was the issue.
turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql.

changed memlock settings in '/etc/security/limits.conf', and limit to 8GB.
then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log.

a few things to note here -

from what i have read online, the amount of memory allocated to
mysql is slightly more than the setting for buffer pool. so lets say
you assign 7GB to pool, actual memory assigned to mysql would be
closer to 8gb (this is an example, not real numbers).
turning the mysql service on/off has to be done very carefully. i
    have noticed that when i do a mysqld-start followed by quickly by a
    mysqld-stop, mysql responds with [FAILED]. logs says allocation of
    7GB failed.


How to select more than 20 rows in SPHINX engine?

In docs you can include limit=40. So:
SELECT * FROM t1 WHERE query='test;mode=any;limit=40';

Not a usual SQL way, but SphinxSE isn't quite normal.


The best thing is to normalize the tables. Create a contact_type table with a record for each contact type. Then create a contact_type_xref junction table that contains the identifier from the contact table and the identifier from the contact_type table. Then load the contact types associated to each contact into the contact_type_xref, and remove the duplicate contact records from contact. Here's an example:. Once you fix the database design, you can tackle the entity-framework problem (which will probably disappear).


Sounds like the data in the log backup might have been corrupted. Can you restore the next log ok, or do you get an error at that point as well?


How can I investigate why Sql Server is chosing the "wrong" Index?

It's could be the lack of column-level statistics on the table variable.  Try the same approach, but instead using a real temp table instead, like this:
CREATE TABLE #Query
(
    Id int NOT NULL,
    Time datetime NOT NULL
)

INSERT INTO #Query VALUES
(1, '2020-12-31');

ALTER TABLE #Query
ADD CONSTRAINT PK_Query PRIMARY KEY (Id);

SELECT  t.*
FROM    [Transaction] t
INNER JOIN #Query q
    ON t.SiloId = q.SiloId
WHERE
    t.Time >= q.Time

However, looking at the actual execution plan provided, the problem is definitely due to a bad estimate.  Check this out:

For some reason, the optimizer thinks there will be 25,000,000 rows coming out of that join, when in reality there are only 4,155 rows.  The optimizer is not going to choose to do 25,000,000 key lookups (since the index doesn't include all the columns), so you end up with a scan of the clustered index.  This is referred to as "the tipping point" on various blogs and such.
The "simplest" option would be to make the index covering (include all the columns in the table), or only select columns that are in the index.  This avoids the key lookup, and you should get this plan naturally.  However, that might not be practical (not sure what the data type of the other columns in your table are, etc).
The better option would be to somehow fix the estimate.  You could try adding an index on Time to the temp table, in case that gives you better stats (after loading the table):
CREATE NONCLUSTERED INDEX IX_Time
ON #Query (Time);

You could also try rewriting the query in different forms to see if you get different results / better estimates, like this:
SELECT  t.*
FROM    [Transaction] t
WHERE EXISTS
(
    SELECT null
    FROM #Query q
    WHERE
        q.SiloId = t.SiloId
        AND t.Time >= q.Time
)

Or see how the legacy cardinality estimator does with this query by adding this hint to the end of it:
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

So the question is: what can I do to try to figure out why SQL Server prefers the "wrong" index even though a much better one exists and I just run full statistics update?

In general, it would be useful to use index hints to "force" the index you want, and then compare the difference in costs or estimates between that plan and the natural one.  That can provide clues about why the desired index isn't getting chosen.

FORCE_LEGACY_CARDINALITY_ESTIMATION though chose the correct index right away! Don't really know what that means though... There is some bug in a new one?

I wouldn't say it's a bug - they just have very different approaches to producing estimates. The legacy CE still works better in certain situations compared to the new CE.
If you want a deep dive into one specific difference, which also provides an approach for looking at the stats and how estimates are produced, check out Paul White's blog post: SQL Server Join Estimation using Histogram Coarse Alignment


Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking


Increased query_cache_size, Queries slowed drastically on increased traffic

That happens because all requests to the QC are serialized. The larger QC is the more time it takes to find and invalidate entries.


You have a lot of results that's why your index is not used. Make your query more restrictive.

It seems that you are using pagination:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page


The operating system returned error 21 (The device is not ready.)

Every time I reboot windows, for some databases comes out this error. (OS Error 21 - Device not ready)

This is due to a disk being offline or not being online either at the time SQL Server started, or had transitioned states after SQL Server has been online.

  3.If I restart SQL Server the errors disappears

Yes, because the databases have been remounted inside of SQL Server. You could also offline->online the database as well and it would work, assuming the disk device has been fixed.

This can easily be reproduced in a test environment by putting a database on a disk, disabling the disk, running a select query (to get the error), bringing the disk back online and noticing that the select still fails with the same error. The database will need to be remounted in order to work again and not get OS Error 21.

What should you do?

Have someone do some windows tracing to figure out why it isn't coming online initially or why it is going offline (any state transition) or why it is showing ready to windows but really isn't (maybe other drivers need to be loaded for it).

Additionally check any disk filter drivers are up to date for things like anti-virus, host intrusion protections, etc., as those may also be blocking the service/startup/state.


mysqld is the server.  You don't log into it as a user.

mysql is the client.  You do log into it as a user; it then connects to mysqld for you.


I know this is an older post, but to save anyone else a headache...

You will only be able to remove SP3, if you have already removed all other patches that have been applied since the SP3 install, you can do this via Windows Programs and features -> View Installed Updates, and checking all patch versions greater than the one for SP3.


No, the second query won't work.  It will always return 0 rows if :MIN_ROW_TO_FETCH is greater than 1.

Conceptually, if you have a query like

select * /*+ FIRST_ROWS(n) */
  from ( your_query_goes_here with filter and order by )
  where ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

where :MIN_ROW_TO_FETCH is 11 and :MAX_ROW_TO_FETCH is 20, Oracle would fetch the first row, give it a rownum of 1, then discard it because it didn't satisfy the predicate.  It would then fetch the second row from the inner query, give it a rownum of 1 (since no rows have been successfully returned yet), then discard it because it didn't satisfy the predicate.  That would repeat until every row had been fetched, assigned a rownum of 1, and discarded.  So the query would return 0 rows.

The extra layer of nesting ensures that the outer query with rnum  >= :MIN_ROW_TO_FETCH sees rows with rnum values between 1 and :MAX_ROW_TO_FETCH and that the full query returns the expected set of rows.

If you're using 12.1 or later, you can use the OFFSET ... FETCH syntax as well

SELECT *
  FROM table_name
 ORDER BY some_column
 OFFSET n ROWS
  FETCH NEXT m ROWS ONLY;


If your interest is explicitly to keep track of the direct manager of the employee, then the simplest solution may be to add manager_id to the project table, and populate that from employee.report_to when a project row is created. It's not worth setting up a full-fledged history mechanism just to track this specific relation.

If, on the other hand, this is intended to allow anyone to track down the projects they or their subordinates (at any level) started while they were their subordinates, then you'll need a full history mechanism. Check out the link mustaccio noted in the comments, to the question Best way to capture historical changes between levels in a dimensional hierarchy.


Whats the right table relationship for these entities?

I would simply have:
USER - ROLE - PROJECT
I presume a user cannot be part of a project without having a role? The ROLE table facilitates your many-to-many mapping between users and projects: one user can map to many projects (with each mapping requiring a role record) and vice versa: one project can map to many users.
Simply add a UNIQUE constraint on the (user_id, project_id) columns on the ROLE table to ensure the user exists only once per project in the ROLE table.
If you need to standardise a list of options to describe roles, then your second diagram suits best - which is the same as mine, with an additional table to describe roles.


Insert the individual values in bulk. In other words, each row of the csv file should become a row in the database with the same number of columns.

Using Notepad++ you can use a regex find and replace to easily convert your CSV into a very large insert statement. Replace \r\n with '),(' and  , with ','. Tidy up the first and last line of the file, then add the actual insert statement to the front. insert into table (col1, col2, col3) values .....


I highly doubt no one is interested! :-)

Here's my link collection that I used to get into the topic:

http://www.toadworld.com/platforms/sql-server/w/wiki/10400.the-server-side-trace-what-why-and-how.aspx
http://www.mssqltips.com/sqlservertip/1035/sql-server-performance-statistics-using-a-server-side-trace/
http://consultingblogs.emc.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx

Hope those help you as well !

Update:

Ok, to clear things up:

in order to create a server-side trace, you need a trace definition script
to create that trace definition script, the easiest approach is to use SQL Server Profiler to define what you want to trace - which events, what properties
you then use SQL Server Profiler to create that server-side trace definition script for you (doing it manually, from scratch, is a major hassle)
but then, to execute - you don't use SQL Server Profiler (that's putting too much load on your server), but you just create and run the trace on the SQL Server


A plain VACUUM will make the free space available for new data (i.e., other TOASTed JSON data).

If you really want to give the free space back to the OS, and you cannot afford the exclusive lock, try pg_repack:

  To perform a full-table repack, pg_repack will:

  create a log table to record changes made to the original table
  add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table
  create a new table containing all the rows in the old table
  build indexes on this new table
  apply all changes which have accrued in the log table to the new table
  swap the tables, including indexes and toast tables, using the system catalogs
  drop the original table

It still needs enough space for the original table and the new copy.


SQL admin Scripts - Enabling read only access to all databases

If you have a list of known usernames, you can store them in a table somewhere:

CREATE TABLE dbo.WebDevelopers(name SYSNAME PRIMARY KEY);

INSERT dbo.WebDevelopers(name)
  SELECT N'YourDomain\User1'
  UNION ALL
  SELECT N'YourDomain\User2';

Now you can create a variety of scripts to ensure that:

they exist as a login at the server level
for each relevant database:

they exist as a user
they are in the db_datareader role
they have SELECT permission on the dbo schema

Here is a start to solve 1. (I'll come back and address the others as I have time later today):

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'';

SELECT @sql = @sql + @cr + 'CREATE LOGIN '
 + QUOTENAME(d.name) + ' FROM WINDOWS;'
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN sys.server_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;

PRINT @sql;
-- EXEC sp_executesql @sql;

For the rest, this makes some basic assumptions: you always elevate privileges using database-level roles, rather than explicit GRANT/DENY, server role membership or AD group membership. Like the table of developers, you also want to have somewhere a list of the databases you want to affect:

CREATE TABLE dbo.DeveloperDatabases(name SYSNAME PRIMARY KEY);

INSERT dbo.DeveloperDatabases(name)
  SELECT N'some_database'
  -- UNION ALL N'...';

Now you can build a script that will drop the user from each database, create the entire list from scratch, and add each user only to the db_datareader role. You may want to customize the script if you want to grant explicit rights on a certain schema or set of objects.

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'SELECT @sql2 = N'''';';

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + ';
  DROP USER '' + QUOTENAME(d.name) + '';''
FROM dbo.WebDevelopers AS d
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name;'
FROM dbo.DeveloperDatabases;

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + ';
  CREATE USER '' + QUOTENAME(d.name) + '' FROM LOGIN '' + QUOTENAME(d.name) + '';
  EXEC sp_addrolemember ''''db_datareader'''', '''''' + d.name + '''''';''
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;'
FROM dbo.DeveloperDatabases;

DECLARE @sql2 NVARCHAR(MAX);

EXEC sp_executesql @sql, N'@sql2 NVARCHAR(MAX) OUTPUT', @sql2 OUTPUT;

PRINT @sql2;
-- EXEC sp_executesql @sql2;

This is very rough, and lots of things can make it break. I could have combined the two selects into a single query but I think double-nested dynamic SQL was complex enough without adding conditional string concatenation in there as well.


What is the difference between these 2 queries?

The difference is that the first query will include a job like this:

jobId    Audit_CreatedDate
-------  -----------------
1        2015-04-01
1        2015-06-01

The first row would be included in the query, but the second row won't be included. This is because at least one row from that job met the where clause of < May 9th (or is that September 5th? Please use non-regional, unambiguous date literals).

This same job would be excluded from the second query altogether, since the filter is effectively applied on the aggregate, not on individual rows. MAX(date) = June 1st in that case, and so the job is thrown out as a whole, rather than just the rows > May 9.

Which query is right? Who knows? You'll need to give more details about your actual requirements - your current word problem is not explicit enough for me to understand. Not that any user or client could consume 4.3 or 5.3 million rows anyway.


Equality query on NVARCHAR column yields multiple results in SQL Server 2012

The collation determines the comparison semantics.

If I try

CREATE TABLE [word](
    [id] [int] IDENTITY(0,1) NOT NULL,
    [value] [nvarchar](255) COLLATE Latin1_General_100_CI_AS NULL
 );

It only returns ἀπὸ.

Changing the suffix to AI for accent insensitive returns ἀπό also.

On my install I have tried every collation and 1526 return 1 (presumably AS and BIN collations), 1264 return 2 rows (presumably AI) and 1095 return 8.

From a quick look through this last group looks to include all the SQL collations and 90 collations whereas all the 100 ones are in the first 2 groups so I presume this is some issue that has been fixed in the 2008 batch of collations. (See What's New in SQL Server 2008 Collations)

Script to try this yourself

DECLARE @Results TABLE
(
Count INT,
Collation SYSNAME
)

SET NOCOUNT ON;
DECLARE @N SYSNAME;
DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.fn_helpcollations();
OPEN @C1;
FETCH NEXT FROM @C1 INTO @N ;
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT @Results
  EXEC('SELECT COUNT(*), ''' + @N + ''' from word where value = N''ἀπὸ'' COLLATE ' + @N)
  FETCH NEXT FROM @C1 INTO @N ;
END

SELECT *
FROM @Results
ORDER BY Count DESC


How to INSERT row in another database/schema in MySQL

Fully qualify the table name:

insert into SCHEMA_2.TABLE_2 values( ... );


Solved it! All, I had to do was enable a shell extension "Topicons plus" from gnome tweak.


How long will an already started online Backup with RMAN take?

RMAN jobs show up in V$SESSION_LONGOPS with OPNAME like 'RMAN%', there you can query the value of TIME REMAINING. Beware, this is just an estimate.


mysql innodb buffer pool instances always 1

The default values for innodb_buffer_pool_instances is explained in the official MySQL Documentation:

  Default Value (Other) :   8 (or 1 if innodb_buffer_pool_size < 1GB)

  Default Value (Windows, 32-bit platforms) : (autosized)

So, if your innodb_buffer_pool_size is less than 1 GB, innodb_buffer_pool_instance will always be reset to 1.

Moreover, since you mentioned my.ini file, it indicates that you are using Windows. If your Windows is 32-bit, then MySQL auto-determines this value, and thus changing it in my.ini won't affect anything. The rules are:

  If innodb_buffer_pool_size is greater than 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB, with
  individual memory allocation requests for each chunk. 1.3GB was chosen
  as the boundary at which there is significant risk for 32-bit Windows
  to be unable to allocate the contiguous address space needed for a
  single buffer pool.
  Otherwise, the default is 1.


Error 150 is a FOREIGN KEY related error so I bet that you didn't create the table mealPlannerApp.photos before mealPlannerApp.ingredients (which is mandatory).

Otherwise, you have some bad practices in your query:

PK on ingredientId and unique index id_UNIQUE are duplicates (useless and will generate extra-load)
The ASC option in your indexes definition is understand by MySQL but not implemented (useless), maybe it's Workbench which has generated this.


Of the two options mentioned, without any doubt, the first is better.  MySQL is fine at handling large tables.  There is no reason whatsoever to break the data into separate "equivalent" tables.  In fact, having multiple tables with the same layout is usually an indication of a poor database design.

For performance, you can then add indexes on the system and date (or both in one index).  You can also learn about performance.

There are some reasons why you would split the data into separate tables per system.  Here are two:

If the statistics maintained for each system were very different from other systems, then it would be more efficient to store only the appropriate columns.
If system requirements mandated data separation or permissioning.  The latter is easier to do on the table level.

You might also consider an entity-attribute-value (EAV) approach.  This would be a table where you basically have four columns:

date
system name
statistic name
value

You then repeat rows for each statistic.  This is usually less efficient than the single-table approach.  But it can be beneficial, particularly when you have lots of statistics, and most systems have different sets of statistics.


AlwaysOn read only replica slower then prime

To fix our problem we had to enable Trace Flag 3459.

We are currently having this issue on SQL Server 2017 CU6 (14.0.3025.34). Not much documentation on this Flag, but from what I gather, enabling this flag will slow down our failover process. I'm going to test some more, and update if i run into anything show stoppers.


Why does 3NF Normal form allow transitive dependency of prime attributes?

Normal forms are used to eliminate or at least reduce redundancy in data, as well as to eliminate the so called insertion/deletion anomalies.

The BCNF eliminates redundancies and anomalies, but decomposing a relation in BCNF sometimes has the unpleasant effect of causing the loss of one or more functional dependencies during the process.

For this reason the 3NF is used instead of the BCNF in practice, since a decomposition of a relation in this form always mantains data and dependencies, and reduces anomalies and redundancy (even if to a lesser extent that the BCNF).

Another reason is that the decomposition in 3NF can be obtained with a polynomial-time algorithm, while the decomposition in BCNF requires an exponential algorithm.


How to pull a list of applied service packs and cumulative updates from SQL Server 2008 R2?

Cumulative updates are exactly that, cumulative. CU2 for example contains all of the fixes included in CU1. As such, the only SP or CU that matters is the last one to be applied which you can determine with:

SELECT @@VERSION

and Google or a reference list of SQL Server versions. SQLServerBuilds is the popular unofficial list, KB321185 is the official list.

I'm not aware of any way to obtain a complete list of all patches that have ever been applied to an instance. It's likely buried in the registry somewhere but I haven't fished for it.


mariabackup of database running in docker

Create a symlink on your host so that Mariabackup thinks /var/lib/mysql exists. Then delete the symlink once you're done.

$ sudo ln -s /mnt/data /var/lib/mysql


Is 'internallength' always useful in custom type definition in Postgres?

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc.

What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the manual once more.

To optimize disk space and performance I would rather avoid using composite types due to big row overhead. Details depend on the details of your requirements.


I was able to reproduce your issue and this might be one of the reason for no result.

Cause:

When you create a table and when no record is present in the table and you run dbcc ind on such table then you get the "message like below but NO tabular result".

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If table on other hand has records you would get tabular output.

For more understanding I suggest you read following blogs from Paul Randal

More about undocumented Command DBCC IND
How to use DBCC IND command to find out various internal SQL Server information

These blogs would help you in understanding how to use DBCC IND to get various information from database and how to interpret the result.


~15 data sets w/ 25k rows & ~20 columns + quick  one time reports = Access VS SQL?

Access is a perfectly fine database system for small scale individual-user apps. Here are some criteria for shifting:

Multi-user applications. Access doesn't do concurrency very well, so a multi-user database - particularly one where multiple users are making modifications to data - will bring Access to it's knees.
Requirement to support advanced SQL functions (windowing, partitioning, MERGE, triggers etc).
Large datasets. Access used to have hard limits on .mdb files at 2GB (IIRC), and I found the likelihood of corruption increases rapidly when the total DB size gets over a gigabyte. (Note that I haven't used Access 2010, so I don't know if things are better now in this regard.)

Note that if you have a front-end built in Access and just want to shift the database away from JET (Access's internal DB engine) onto a "proper" RDBMS, you can do so by migrating the data across and setting up linked tables inside Access to the new data source.


Storage pool configuration for SQL Server

Is [using a single pool] applicable to Always-On or just applicable to shared storage FCI.

FCI's in Azure use storage spaces direct for disk replication, which I imagine is the source of this requirement.  For AGs you can use multiple separate disks or pools.  You should ensure that the same logical volumes and paths are available on each node, otherwise AG replication will stop if you perform file and filegroup operations on the primary.  EG if you ad a file 'g:\sql\data\foo.ndf' to a database, that path needs to exist on all the other cluster nodes for that change to be replicated.  See Troubleshoot a Failed Add-File Operation (Always On Availability Groups).

Also, wherever you put Tempdb data files, put Tempdb log there too.  Splitting out tempdb's log is overkill as it's likely to be underutilized, and its IO access patterns are more like tembdb data files than user database log files.  In particular transaction commit doesn't flush the tempdb logs.


How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

majority OLTP type workload with some OLAP mixed in

This is the problem, not the CXPACKET. Parallelism is a symptom, not a cause. Your 'some' OLAP workload is doing scans which trigger parallelism, which cascades into exchange wait times, likely buffer pool pollution and possible blocking (OLTP workload blocks behind OLAP scans).

If the OLAP workload is well understood and absolutely critical, then you can consider adding appropriator covering indices for it. But is an ever uphill battle. I would much rather see the OLAP workload, with its damaging scans, move onto a dedicated box. Newer versions (SQL Server 2014) have readable secondaries and columnstores, both exquisite at serving analytical/ad-hoc/OLAP workloads.

For SQL Server 2008 R2 I would consider log shipping or replication (although I reckon none is 'perfect').

Short term: you have a performance problem and you need to analyse it appropriately. Read How to analyse SQL Server performance. Identify the query or queries that cause most damage (see identifying problem queries. Only then, after you identified the actual problem, can solutions be recommended.

NB: LATCH_EX on ACCESS_METHODS_DATASET_PARENT is not about IO at all. It is strictly related to parallelism, as is the latch the parallel scans 'child' threads must acquire on the parent thread to allocate the scan range for that child. Contention on it indicates the parallelism is inefficient (is doing more 'homework' than actual useful work). Partitioning would exacerbate this symptom, specially unaligned partitioning (as parent/child datasets are set per partition). Bad cardinality estimates (outdated stats?) may also be to blame, conducting to parallelism when not necessary. On and all my advice is the same: identify the actual problem query(s).


If I've understood correctly you need to set a group every time CodeID changes. You can try by using LAG() function.

WITH ct1 AS
(
  SELECT
      ResourceID, ReferenceDate, CodeID,
      IIF(COALESCE(LAG(CodeID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> CodeID
          AND
          COALESCE(LAG(ResourceID) OVER (ORDER BY ResourceID, ReferenceDate), 0) <> ResourceID,
         1, NULL) Rst
  FROM
      Resources
)
, ct2 AS
  (
    SELECT
        ResourceID, ReferenceDate, CodeID,
        SUM(Rst) OVER (ORDER BY ResourceID, ReferenceDate) as Grp
    FROM
        ct1
   )
   SELECT
       ResourceID, CodeID, COUNT(*) CntCodeID
   FROM
       ct2
   GROUP BY
       ResourceID, CodeID, Grp;

GO

ResourceID | CodeID | CntCodeID
---------: | -----: | --------:
        55 |      1 |         1
       191 |      5 |         3
       224 |      1 |         1
       353 |      1 |         1
       382 |      1 |         1
       530 |      1 |         1
       541 |      1 |         1
       655 |      6 |         2
       656 |      1 |         2
       755 |      1 |         1
       892 |      6 |         1
       893 |      1 |         1
      1115 |      1 |         1

db<>fiddle here


Increment each row according to condition in SQL C#

You can use a window function to calculate the rolling sum (of stock_in_hand) in a derived table or a CTE and then use that to update the table accordingly:

WITH upd AS
  ( SELECT
        [Bill No],
        Item_Name,
        Stock_in,
        Stock_out,
        stock_in_hand,
        sum_stock = COALESCE(
            SUM(stock_in_hand) OVER (ORDER BY Item_Name, [Bill No]
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                              AND 1 PRECEDING)
            , 0)
    FROM
        stock_recieve
  )
UPDATE upd
SET Stock_out = Stock_out
        + CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END,
    stock_in_hand = stock_in_hand
        - CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END
WHERE sum_stock < 100 ;


Two relations to same table in SQL Server

It is not possible with a constraint. Have a look at Multiple Cascading Actions.

  No table can appear more than one time in the list of all cascading
  referential actions that result from the DELETE or UPDATE.

You can use an instead of trigger instead where you do the update in Table2 before the delete in Table1.

The trigger could look something like this

create trigger Table1_Delete on Table1 instead of delete
as

update Table2
set Table1ID1 = null
where Table2.Table1ID1 in (select D.Table1ID from deleted as D);

update Table2
set Table1ID2 = null
where Table2.Table1ID2 in (select D.Table1ID from deleted as D);

delete from Table1
where Table1.Table1ID in (select D.Table1ID from deleted as D);


Set column to randomly chosen value from another table

The subquery is evaluated only once because it has no dependency on the row to update.

You may add one, just to force the engine to evaluate the subquery for each row. For instance if dest_col is a non-null number:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random()+dest_col LIMIT 1)

Or if it's not a number, any other column that is a number in dest will do, or any other expression involving column(s) of the table to update that will not affect the result but create a dependency.


checksum(newid()) as primary key - what happens if a collision occurs?

I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs.

If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working just fine. I think you should only file a bug report if you can demonstrate that the application does something wrong when a collision occurs - not easy given the likely failure rate. An easier way, if you have source code access, would be to review the portion of the code where they perform the insert (or find out if it calls a stored procedure to do so).

That in mind, there is a much easier and fault tolerant way to do this. Build a table up front, with a slew of numbers ordered randomly, then just pluck a number off the top whenever you need one.

CREATE TABLE dbo.Destination(ID INT /*, other columns */);

CREATE TABLE dbo.Source
(
  RowNum INT PRIMARY KEY,
  ID INT, -- UNIQUE
  Used BIT NOT NULL DEFAULT 0
);

INSERT dbo.Source(RowNum, ID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), ID
  FROM
  (
    SELECT ID = CHECKSUM(NEWID())
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
      -- should produce about 4 million rows,
      -- on my crappy VM this took two minutes,
      -- add another cross join if you need more
  )
  AS x GROUP BY ID;

Then when you need to add a row, you can just say:

UPDATE TOP (1) dbo.Source SET Used = 1
  OUTPUT inserted.ID /*, @params */
  INTO dbo.Destination
WHERE Used = 0;

(You could also DELETE TOP (1) however I find keeping them useful for auditing etc.)


In the example you show, the expression would use the index on criteria to resolve which rows to retrieve, and the leaf nodes in the index contain references to the corresponding rows, either by physical location in the case of MyISAM or by primary key value in the case of InnoDB.  That way it avoids a full table scan.
Note that if the query optimizer thinks your query matches too much of the table, it will ignore the index, because it's just as fast to do the table-scan.  By analogy, this is why no one bothers to put the word "the" in the index at the back of a book.

No, MySQL requires that an index exists for the foreign key.  If an index already exists, InnoDB will use it, but it will create one if necessary.  I have found some corner cases where you can trick InnoDB by dropping the index after defining the foreign key constraint, but this would not be advisable, because the index is important for checking referential integrity quickly.

It's true that insert/update/delete needs to update indexes to reflect values added or removed.  But it's not as bad as you might fear.  Also, InnoDB implements a "change buffer" which records how your update affects secondary indexes, and reconciles it with the whole index later.

InnoDB and MyISAM support only BTREE indexes.  InnoDB also has an internal feature called the adaptive hash which does do a hash lookup for frequently-requested index entries.  But this is completely automatic and transparent; you don't have to define any index or tune any config parameters.


How to summate weekly totals from a postgres date range type per user

Build week borders table (week_start, week_end) in the range from MIN(date_start) to MAX(date_end) in CTE. Then cross-join it with your table, select only overlapped pairs and calculate values you need. - akina


Does Sql Server caches parameterized anonymous blocks?

From my brief reading on Anonymous Blocks in Oracle, this is essentially just an ad-hoc query in SQL Server. Yes, ad-hoc queries are cached in the query plan cache of SQL Server, and therefore on subsequent runs won't be recompiled, for as long as they stay in the cache. (The query plan cache has mechanisms to remove old and/or rarely used plans from the cache based on the busyness of the server.)


Allowing non-privileged users to cause privileged code to run

perhaps a signed stored procedure would work? I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Yes. A signed stored procedure absolutely would work, and would work well. As individual requirements go, "simplest" is not always overlapping with "sanest"; all too often people choose the simplest approach, giving up far too much sanity (which is understandable given that the simplest approach is, by definition, the easiest to understand, and the "saner" approach isn't obviously saner (at least not at first)). This is where Module Signing comes in. While not being the simplest approach, it is by far the sanest, all things considered. Unfortunately, it is also not well understood, and so appears to be more confusing / difficult than it really is.

The general concept is:

Create the stored procedure (in the "management" DB) that creates the student's database:

Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
Create the database with the desired naming convention (including project name). You can get their logon name from a variety of system variables and built-in functions, though ORIGINAL_LOGIN() might be the best option. You might need to do simple string manipulation as the name will be prefixed with the machine/domain name.
USE the new DB
Create a user for that student's account:

DECLARE @User NVARCHAR(258) = ORIGINAL_LOGIN();
SET @User = QUOTENAME(manipulated_@User);
EXEC (N'CREATE USER ' + @User);

Add permissions / roles for student's account:

EXEC (N'ALTER ROLE [db_datareader] ADD MEMBER ' + @User);
...

Create the stored procedure (in the "management" DB) that drops the student's database:

Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
Drop the database having the desired naming convention (including project name). 1. Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions (could be "student" AD group, or [public] as noted in the chat room

Create a certificate (in the "management" DB)
Sign the stored procedures using that certificate (using ADD SIGNATURE)
Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the stored procedures).
Create a login from the certificate copied to [master]
Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).

For some examples, please see:

How to maintain database ownership restoring across domains?
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level


Preventing duplicate data when usual normalization only makes sense for some of the records

You may want to normalize out the comments field to allow for a comments chain.  This may have additional fields such as userid and date.  This might result in a table tracking actions on issues.  If this is not part of your design, I wouldn't normalize the comments field out.

It appears you have an optional 1-1 relationship between the status checks and issues.  Either table can carry this relationship as a nullable foreign key.  If the relationship might not be 1-1 that may drive the table carrying the foreign key, or require a relationship table.


Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE'

That is the expected behaviour.

If the data in the base table changes and the view isn't up to date anymore its status is not valid anymore. If you recompile or refresh it will become up to date and is valid again. So you don't have to bother about the invalid state of your view.

This question was already posed on stackoverfow.com and answered by Andrew Brennan:

  See MOS Doc ID 264036.1:

    Dependencies related to MVs are automatically maintained to ensure
    correct operation. When an MV is created, the materialized view
    depends on the master tables referenced in its definition. Any DML
    operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on
    any dependency in the materialized view will cause it to become
    invalid.


MySQL Performance comparison between loading comma separted vs fixed-width large file

First of all, don't use myisam.
Csv or fixed width, performance will be same. But if you load the dump in chunks it will be faster. Check out pt-fifo tool from Percona toolkit. It doesn't work well though if the dump contains newlines.


If you use mysqldump to export your data it will take care of this for you. For example:

mysql> select * from table1;
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | \frac{1}{2} |
+------+-------------+

mysqldump -uroot -p database1 table1 > table1.sql

cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\\frac{1}{2}');

notice the escaped backslash? and:

mysql -uroot -p database1 < table1.sql

mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | \frac{1}{2} |
+------+-------------+


Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

First shutting down Windows without stopping SQL Server first didn't cause this.  That won't do anything bad to the database as all services are cleanly shutdown when Windows is told to turn off.

I assume that the principle is offline and can't be brought online?

Try forcing the database to come online.

ALTER DATABASE X SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

That should bring the database online which should then allow you to remove mirroring and drop the database (or do whatever else you need to do with it).


It may not be the most efficient method but I can't see a problem with your SQL.

Recommend you check your data..


How to create a relatively complex MySQL query

If I understand then this is what you want:

SELECT   m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name
FROM     macs m, network_devices n, vlans v
WHERE    n.nd_ip_address = m.ipAddr
AND      m.vlanID        = v.vlan_id
GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name
ORDER BY m.vlanID


Converting character sets and foreign key constaints

Sort the ALTERs so that the FK checks will stop complaining.  Do a child before a parent (or is it the other way??).

If you have any circular references, then, yeah, remove and re-apply the FKs.

(You have pointed out yet another reason to eschew FKs.)

While you are at it, consider utf8mb4_unicode_520_ci; it comes from a newer Unicode standard.